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()
