def delete_inbound_sms_older_than_retention(): current_app.logger.info( 'Deleting inbound sms for services with flexible data retention') flexible_data_retention = ServiceDataRetention.query.join( ServiceDataRetention.service, Service.inbound_number).filter( ServiceDataRetention.notification_type == SMS_TYPE).all() deleted = 0 for f in flexible_data_retention: n_days_ago = midnight_n_days_ago(f.days_of_retention) current_app.logger.info( "Deleting inbound sms for service id: {}".format(f.service_id)) deleted += _delete_inbound_sms( n_days_ago, query_filter=[InboundSms.service_id == f.service_id]) current_app.logger.info( 'Deleting inbound sms for services without flexible data retention') seven_days_ago = midnight_n_days_ago(7) deleted += _delete_inbound_sms(seven_days_ago, query_filter=[ InboundSms.service_id.notin_( x.service_id for x in flexible_data_retention), ]) current_app.logger.info('Deleted {} inbound sms'.format(deleted)) return deleted
def dao_get_uploads_by_service_id(service_id, limit_days=None, page=1, page_size=50): # Hardcoded filter to exclude cancelled or scheduled jobs # for the moment, but we may want to change this method take 'statuses' as a argument in the future jobs_query_filter = [ Job.service_id == service_id, Job.original_file_name != current_app.config['TEST_MESSAGE_FILENAME'], Job.original_file_name != current_app.config['ONE_OFF_MESSAGE_FILENAME'], Job.job_status.notin_([JOB_STATUS_CANCELLED, JOB_STATUS_SCHEDULED]) ] if limit_days is not None: jobs_query_filter.append( Job.created_at >= midnight_n_days_ago(limit_days)) jobs_query = db.session.query( Job.id, Job.original_file_name, Job.notification_count, Job.created_at.label("created_at"), Job.scheduled_for.label("scheduled_for"), Job.processing_started.label('processing_started'), Job.job_status.label("status"), literal('job').label('upload_type')).filter(*jobs_query_filter) letters_query_filter = [ Notification.service_id == service_id, Notification.notification_type == LETTER_TYPE, Notification.api_key_id == None, # noqa Notification.status != NOTIFICATION_CANCELLED, Template.hidden == True, ] if limit_days is not None: letters_query_filter.append( Notification.created_at >= midnight_n_days_ago(limit_days)) letters_query = db.session.query( Notification.id, Notification.client_reference.label('original_file_name'), literal('1').label('notification_count'), Notification.created_at.label("created_at"), literal(None).label('scheduled_for'), # letters don't have a processing_started date but we want created_at to be used for sorting Notification.created_at.label('processing_started'), Notification.status, literal('letter').label('upload_type')).join( Template, Notification.template_id == Template.id).filter( *letters_query_filter) return jobs_query.union_all(letters_query).order_by( desc("processing_started"), desc("created_at")).paginate(page=page, per_page=page_size)
def returned_letter_statistics(service_id): most_recent = fetch_most_recent_returned_letter(service_id) if not most_recent: return jsonify({ 'returned_letter_count': 0, 'most_recent_report': None, }) most_recent_reported_at = datetime.combine( most_recent.reported_at, datetime.min.time() ) if most_recent_reported_at < midnight_n_days_ago(7): return jsonify({ 'returned_letter_count': 0, 'most_recent_report': most_recent.reported_at.strftime(DATETIME_FORMAT_NO_TIMEZONE), }) count = fetch_recent_returned_letter_count(service_id) return jsonify({ 'returned_letter_count': count.returned_letter_count, 'most_recent_report': most_recent.reported_at.strftime(DATETIME_FORMAT_NO_TIMEZONE), })
def dao_get_paginated_most_recent_inbound_sms_by_user_number_for_service( service_id, page, limit_days): """ This query starts from inbound_sms and joins on to itself to find the most recent row for each user_number. Equivalent sql: SELECT t1.* FROM inbound_sms t1 LEFT OUTER JOIN inbound_sms AS t2 ON ( -- identifying t1.user_number = t2.user_number AND t1.service_id = t2.service_id AND -- ordering t1.created_at < t2.created_at ) WHERE t2.id IS NULL AND t1.service_id = :service_id ORDER BY t1.created_at DESC; LIMIT 50 OFFSET :page """ t2 = aliased(InboundSms) q = db.session.query(InboundSms).outerjoin( t2, and_( InboundSms.user_number == t2.user_number, InboundSms.service_id == t2.service_id, InboundSms.created_at < t2.created_at, )).filter( t2.id == None, # noqa InboundSms.service_id == service_id, InboundSms.created_at >= midnight_n_days_ago(limit_days)).order_by( InboundSms.created_at.desc()) return q.paginate(page=page, per_page=current_app.config['PAGE_SIZE'])
def fetch_recent_returned_letter_count(service_id): return db.session.query( func.count(ReturnedLetter.notification_id).label( 'returned_letter_count'), ).filter( ReturnedLetter.service_id == service_id, ReturnedLetter.reported_at > midnight_n_days_ago(7), ).one()
def dao_get_jobs_by_service_id( service_id, *, limit_days=None, page=1, page_size=50, statuses=None, contact_list_id=None, ): query_filter = [ Job.service_id == service_id, Job.original_file_name != current_app.config['TEST_MESSAGE_FILENAME'], Job.original_file_name != current_app.config['ONE_OFF_MESSAGE_FILENAME'], ] if limit_days is not None: query_filter.append(Job.created_at >= midnight_n_days_ago(limit_days)) if statuses is not None and statuses != ['']: query_filter.append(Job.job_status.in_(statuses)) if contact_list_id is not None: query_filter.append(Job.contact_list_id == contact_list_id) return Job.query \ .filter(*query_filter) \ .order_by(Job.processing_started.desc(), Job.created_at.desc()) \ .paginate(page=page, per_page=page_size)
def get_paginated_jobs(service_id, limit_days, statuses, page): pagination = dao_get_jobs_by_service_id( service_id, limit_days=limit_days, page=page, page_size=current_app.config['PAGE_SIZE'], statuses=statuses ) data = job_schema.dump(pagination.items, many=True).data for job_data in data: start = job_data['processing_started'] start = dateutil.parser.parse(start).replace(tzinfo=None) if start else None if start is None: statistics = [] elif start.replace(tzinfo=None) < midnight_n_days_ago(3): # ft_notification_status table statistics = fetch_notification_statuses_for_job(job_data['id']) else: # notifications table statistics = dao_get_notification_outcomes_for_job(service_id, job_data['id']) job_data['statistics'] = [{'status': statistic.status, 'count': statistic.count} for statistic in statistics] return { 'data': data, 'page_size': pagination.per_page, 'total': pagination.total, 'links': pagination_links( pagination, '.get_jobs_by_service', service_id=service_id ) }
def get_paginated_uploads(service_id, limit_days, page): pagination = dao_get_uploads_by_service_id( service_id, limit_days=limit_days, page=page, page_size=current_app.config['PAGE_SIZE']) uploads = pagination.items data = [] for upload in uploads: upload_dict = { 'id': upload.id, 'original_file_name': upload.original_file_name, 'notification_count': upload.notification_count, 'created_at': upload.scheduled_for.strftime("%Y-%m-%d %H:%M:%S") if upload.scheduled_for else upload.created_at.strftime("%Y-%m-%d %H:%M:%S"), 'upload_type': upload.upload_type, 'template_type': upload.template_type, 'recipient': upload.recipient, } if upload.upload_type == 'job': start = upload.processing_started if start is None: statistics = [] elif start.replace(tzinfo=None) < midnight_n_days_ago(3): # ft_notification_status table statistics = fetch_notification_statuses_for_job(upload.id) else: # notifications table statistics = dao_get_notification_outcomes_for_job( service_id, upload.id) upload_dict['statistics'] = [{ 'status': statistic.status, 'count': statistic.count } for statistic in statistics] else: upload_dict['statistics'] = [] data.append(upload_dict) return { 'data': data, 'page_size': pagination.per_page, 'total': pagination.total, 'links': pagination_links(pagination, '.get_uploads_by_service', service_id=service_id) }
def get_notifications_for_service( service_id, filter_dict=None, page=1, page_size=None, count_pages=True, limit_days=None, key_type=None, personalisation=False, include_jobs=False, include_from_test_key=False, older_than=None, client_reference=None, include_one_off=True ): if page_size is None: page_size = current_app.config['PAGE_SIZE'] filters = [Notification.service_id == service_id] if limit_days is not None: filters.append(Notification.created_at >= midnight_n_days_ago(limit_days)) if older_than is not None: older_than_created_at = db.session.query( Notification.created_at).filter(Notification.id == older_than).as_scalar() filters.append(Notification.created_at < older_than_created_at) if not include_jobs: filters.append(Notification.job_id == None) # noqa if not include_one_off: filters.append(Notification.created_by_id == None) # noqa if key_type is not None: filters.append(Notification.key_type == key_type) elif not include_from_test_key: filters.append(Notification.key_type != KEY_TYPE_TEST) if client_reference is not None: filters.append(Notification.client_reference == client_reference) query = Notification.query.filter(*filters) query = _filter_query(query, filter_dict) if personalisation: query = query.options( joinedload('template') ) return query.order_by(desc(Notification.created_at)).paginate( page=page, per_page=page_size, count=count_pages )
def fetch_notification_status_for_service_for_today_and_7_previous_days(service_id, by_template=False, limit_days=7): start_date = midnight_n_days_ago(limit_days) now = datetime.utcnow() stats_for_7_days = db.session.query( FactNotificationStatus.notification_type.label('notification_type'), FactNotificationStatus.notification_status.label('status'), *([FactNotificationStatus.template_id.label('template_id')] if by_template else []), FactNotificationStatus.notification_count.label('count') ).filter( FactNotificationStatus.service_id == service_id, FactNotificationStatus.bst_date >= start_date, FactNotificationStatus.key_type != KEY_TYPE_TEST ) stats_for_today = db.session.query( Notification.notification_type.cast(db.Text), Notification.status, *([Notification.template_id] if by_template else []), func.count().label('count') ).filter( Notification.created_at >= get_local_timezone_midnight_in_utc(now), Notification.service_id == service_id, Notification.key_type != KEY_TYPE_TEST ).group_by( Notification.notification_type, *([Notification.template_id] if by_template else []), Notification.status ) all_stats_table = stats_for_7_days.union_all(stats_for_today).subquery() query = db.session.query( *([ Template.name.label("template_name"), Template.is_precompiled_letter, all_stats_table.c.template_id ] if by_template else []), all_stats_table.c.notification_type, all_stats_table.c.status, func.cast(func.sum(all_stats_table.c.count), Integer).label('count'), ) if by_template: query = query.filter(all_stats_table.c.template_id == Template.id) return query.group_by( *([Template.name, Template.is_precompiled_letter, all_stats_table.c.template_id] if by_template else []), all_stats_table.c.notification_type, all_stats_table.c.status, ).all()
def dao_get_inbound_sms_for_service(service_id, user_number=None, *, limit_days=None, limit=None): q = InboundSms.query.filter( InboundSms.service_id == service_id ).order_by( InboundSms.created_at.desc() ) if limit_days is not None: start_date = midnight_n_days_ago(limit_days) q = q.filter(InboundSms.created_at >= start_date) if user_number: q = q.filter(InboundSms.user_number == user_number) if limit: q = q.limit(limit) return q.all()
def get_paginated_jobs(service_id, limit_days, statuses, page): pagination = dao_get_jobs_by_service_id( service_id, limit_days=limit_days, page=page, page_size=current_app.config["PAGE_SIZE"], statuses=statuses, ) data = job_schema.dump(pagination.items, many=True).data for job_data in data: start = job_data["processing_started"] start = dateutil.parser.parse(start).replace( tzinfo=None) if start else None if start is None: statistics = [] elif start.replace(tzinfo=None) < midnight_n_days_ago(3): # ft_notification_status table statistics = fetch_notification_statuses_for_job(job_data["id"]) else: # notifications table statistics = dao_get_notification_outcomes_for_job( service_id, job_data["id"]) job_data["statistics"] = [{ "status": statistic.status, "count": statistic.count } for statistic in statistics] return { "data": data, "page_size": pagination.per_page, "total": pagination.total, "links": pagination_links(pagination, ".get_jobs_by_service", service_id=service_id), }
def dao_fetch_stats_for_service(service_id, limit_days): # We always want between seven and eight days start_date = midnight_n_days_ago(limit_days) return _stats_for_service_query(service_id).filter( Notification.created_at >= start_date ).all()
def test_midnight_n_days_ago(current_time, arg, expected_datetime): with freeze_time(current_time): assert midnight_n_days_ago(arg) == expected_datetime
def dao_count_inbound_sms_for_service(service_id, limit_days): return InboundSms.query.filter( InboundSms.service_id == service_id, InboundSms.created_at >= midnight_n_days_ago(limit_days)).count()
def dao_get_uploads_by_service_id(service_id, limit_days=None, page=1, page_size=50): # Hardcoded filter to exclude cancelled or scheduled jobs # for the moment, but we may want to change this method take 'statuses' as a argument in the future today = datetime.utcnow().date() jobs_query_filter = [ Job.service_id == service_id, Job.original_file_name != current_app.config['TEST_MESSAGE_FILENAME'], Job.original_file_name != current_app.config['ONE_OFF_MESSAGE_FILENAME'], Job.job_status.notin_([JOB_STATUS_CANCELLED, JOB_STATUS_SCHEDULED]), func.coalesce(Job.processing_started, Job.created_at) >= today - func.coalesce(ServiceDataRetention.days_of_retention, 7), Job.contact_list_id.is_(None), ] if limit_days is not None: jobs_query_filter.append( Job.created_at >= midnight_n_days_ago(limit_days)) jobs_query = db.session.query( Job.id, Job.original_file_name, Job.notification_count, Template.template_type, func.coalesce(ServiceDataRetention.days_of_retention, 7).label('days_of_retention'), Job.created_at.label("created_at"), Job.scheduled_for.label("scheduled_for"), Job.processing_started.label('processing_started'), Job.job_status.label("status"), literal('job').label('upload_type'), literal(None).label('recipient'), ).join(Template, Job.template_id == Template.id).outerjoin( ServiceDataRetention, and_( Template.service_id == ServiceDataRetention.service_id, func.cast(Template.template_type, String) == func.cast( ServiceDataRetention.notification_type, String))).filter(*jobs_query_filter) letters_query_filter = [ Notification.service_id == service_id, Notification.notification_type == LETTER_TYPE, Notification.api_key_id == None, # noqa Notification.status != NOTIFICATION_CANCELLED, Template.hidden == True, Notification.created_at >= today - func.coalesce(ServiceDataRetention.days_of_retention, 7) ] if limit_days is not None: letters_query_filter.append( Notification.created_at >= midnight_n_days_ago(limit_days)) letters_subquery = db.session.query( func.count().label('notification_count'), _naive_gmt_to_utc(_get_printing_datetime( Notification.created_at)).label('printing_at'), ).join(Template, Notification.template_id == Template.id).outerjoin( ServiceDataRetention, and_( Template.service_id == ServiceDataRetention.service_id, func.cast(Template.template_type, String) == func.cast( ServiceDataRetention.notification_type, String))).filter( *letters_query_filter).group_by('printing_at').subquery() letters_query = db.session.query( literal(None).label('id'), literal('Uploaded letters').label('original_file_name'), letters_subquery.c.notification_count.label('notification_count'), literal('letter').label('template_type'), literal(None).label('days_of_retention'), letters_subquery.c.printing_at.label('created_at'), literal(None).label('scheduled_for'), letters_subquery.c.printing_at.label('processing_started'), literal(None).label('status'), literal('letter_day').label('upload_type'), literal(None).label('recipient'), ).group_by( letters_subquery.c.notification_count, letters_subquery.c.printing_at, ) return jobs_query.union_all(letters_query).order_by( desc("processing_started"), desc("created_at")).paginate(page=page, per_page=page_size)