def dao_get_template_usage(service_id, limit_days=None): query_filter = [] table = NotificationHistory if limit_days is not None and limit_days <= 7: table = Notification # only limit days if it's not seven days, as 7 days == the whole of Notifications table. if limit_days != 7: query_filter.append(table.created_at >= days_ago(limit_days)) elif limit_days is not None: # case where not under 7 days, so using NotificationsHistory so limit allowed query_filter.append(table.created_at >= days_ago(limit_days)) query_filter.append(table.service_id == service_id) query_filter.append(table.key_type != KEY_TYPE_TEST) # only limit days if it's not seven days, as 7 days == the whole of Notifications table. if limit_days is not None and limit_days != 7: query_filter.append(table.created_at >= days_ago(limit_days)) notifications_aggregate_query = db.session.query( func.count().label('count'), table.template_id ).filter( *query_filter ).group_by( table.template_id ).subquery() query = db.session.query( Template.id.label('template_id'), Template.name, Template.template_type, Template.is_precompiled_letter, notifications_aggregate_query.c.count ).join( notifications_aggregate_query, notifications_aggregate_query.c.template_id == Template.id ).order_by(Template.name) return query.all()
def dao_get_jobs(limit_days=None, statuses=None): query_filter = [ 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(cast(Job.created_at, sql_date) >= days_ago(limit_days)) if statuses is not None and statuses != ['']: query_filter.append( Job.job_status.in_(statuses) ) return Job.query \ .filter(*query_filter) \ .order_by(Job.processing_started.desc(), Job.created_at.desc())
def dao_get_jobs_by_service_id(service_id, limit_days=None, page=1, page_size=50, statuses=None): query_filter = [ Job.service_id == service_id, Job.original_file_name != current_app.config['TEST_MESSAGE_FILENAME'] ] if limit_days is not None: query_filter.append(cast(Job.created_at, sql_date) >= days_ago(limit_days)) if statuses is not None and statuses != ['']: query_filter.append( Job.job_status.in_(statuses) ) return Job.query \ .filter(*query_filter) \ .order_by(Job.processing_started.desc(), Job.created_at.desc()) \ .paginate(page=page, per_page=page_size)
def dao_get_jobs_older_than(limit_days): return Job.query.filter( cast(Job.created_at, sql_date) < days_ago(limit_days) ).order_by(desc(Job.created_at)).all()