def fetch_stats_by_date_range_for_all_services(start_date, end_date, include_from_test_key=True, only_active=True): start_date = get_sydney_midnight_in_utc(start_date) end_date = get_sydney_midnight_in_utc(end_date + timedelta(days=1)) table = NotificationHistory if start_date >= datetime.utcnow() - timedelta(days=7): table = Notification subquery = db.session.query(table.notification_type, table.status, table.service_id, func.count(table.id).label('count')).filter( table.created_at >= start_date, table.created_at < end_date).group_by( table.notification_type, table.status, table.service_id) if not include_from_test_key: subquery = subquery.filter(table.key_type != KEY_TYPE_TEST) subquery = subquery.subquery() query = db.session.query( Service.id.label('service_id'), Service.name, Service.restricted, Service.research_mode, Service.active, Service.created_at, Service.organisation_type, subquery.c.notification_type, subquery.c.status, subquery.c.count).outerjoin( subquery, subquery.c.service_id == Service.id).order_by(Service.id) if only_active: query = query.filter(Service.active) return query.all()
def populate_redis_template_usage(service_id, day): """ Recalculate and replace the stats in redis for a day. To be used if redis data is lost for some reason. """ if not current_app.config['REDIS_ENABLED']: current_app.logger.error( 'Cannot populate redis template usage - redis not enabled') sys.exit(1) # the day variable is set by click to be midnight of that day start_time = get_sydney_midnight_in_utc(day) end_time = get_sydney_midnight_in_utc(day + timedelta(days=1)) usage = { str(row.template_id): row.count for row in db.session.query(Notification.template_id, func.count().label('count')). filter(Notification.service_id == service_id, Notification.created_at >= start_time, Notification.created_at < end_time).group_by( Notification.template_id) } current_app.logger.info( 'Populating usage dict for service {} day {}: {}'.format( service_id, day, usage.items())) if usage: key = cache_key_for_service_template_usage_per_day(service_id, day) redis_store.set_hash_and_expire( key, usage, current_app.config['EXPIRE_CACHE_EIGHT_DAYS'], raise_exception=True)
def fetch_aggregate_stats_by_date_range_for_all_services(start_date, end_date, include_from_test_key=True): start_date = get_sydney_midnight_in_utc(start_date) end_date = get_sydney_midnight_in_utc(end_date + timedelta(days=1)) table = NotificationHistory if start_date >= datetime.utcnow() - timedelta(days=7): table = Notification query = db.session.query( table.notification_type, table.status, table.key_type, func.count(table.id).label('count') ).filter( table.created_at >= start_date, table.created_at < end_date ).group_by( table.notification_type, table.status, table.key_type ).order_by( table.notification_type ) if not include_from_test_key: query = query.filter(table.key_type != KEY_TYPE_TEST) return query.all()
def dao_fetch_todays_stats_for_all_services(include_from_test_key=True, only_active=True): today_in_aet = convert_utc_to_aet(datetime.utcnow()) start_date = get_sydney_midnight_in_utc(today_in_aet) end_date = get_sydney_midnight_in_utc(today_in_aet + timedelta(days=1)) subquery = db.session.query( Notification.notification_type, Notification.status, Notification.service_id, func.count(Notification.id).label('count')).filter( Notification.created_at >= start_date, Notification.created_at < end_date).group_by( Notification.notification_type, Notification.status, Notification.service_id) if not include_from_test_key: subquery = subquery.filter(Notification.key_type != KEY_TYPE_TEST) subquery = subquery.subquery() query = db.session.query( Service.id.label('service_id'), Service.name, Service.restricted, Service.research_mode, Service.active, Service.created_at, Service.organisation_type, subquery.c.notification_type, subquery.c.status, subquery.c.count).outerjoin( subquery, subquery.c.service_id == Service.id).order_by(Service.id) if only_active: query = query.filter(Service.active) return query.all()
def fetch_notification_status_for_service_for_day(aet_day, service_id): return db.session.query( # return current month as a datetime so the data has the same shape as the ft_notification_status query literal(aet_day.replace(day=1), type_=DateTime).label('month'), Notification.notification_type, Notification.status.label('notification_status'), func.count().label('count')).filter( Notification.created_at >= get_sydney_midnight_in_utc(aet_day), Notification.created_at < get_sydney_midnight_in_utc(aet_day + timedelta(days=1)), Notification.service_id == service_id, Notification.key_type != KEY_TYPE_TEST).group_by( Notification.notification_type, Notification.status).all()
def fetch_notification_status_for_service_for_today_and_7_previous_days( service_id, by_template=False, limit_days=7): start_date = convert_utc_to_aet(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.aet_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_sydney_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 fetch_notification_status_totals_for_all_services(start_date, end_date): stats = db.session.query( FactNotificationStatus.notification_type.label('notification_type'), FactNotificationStatus.notification_status.label('status'), FactNotificationStatus.key_type.label('key_type'), func.sum( FactNotificationStatus.notification_count).label('count')).filter( FactNotificationStatus.aet_date >= start_date, FactNotificationStatus.aet_date <= end_date).group_by( FactNotificationStatus.notification_type, FactNotificationStatus.notification_status, FactNotificationStatus.key_type, ) today = get_sydney_midnight_in_utc(datetime.utcnow()) if start_date <= datetime.utcnow().date() <= end_date: stats_for_today = db.session.query( Notification.notification_type.cast( db.Text).label('notification_type'), Notification.status, Notification.key_type, func.count().label('count')).filter( Notification.created_at >= today).group_by( Notification.notification_type.cast(db.Text), Notification.status, Notification.key_type, ) all_stats_table = stats.union_all(stats_for_today).subquery() query = db.session.query( all_stats_table.c.notification_type, all_stats_table.c.status, all_stats_table.c.key_type, func.cast(func.sum(all_stats_table.c.count), Integer).label('count'), ).group_by( all_stats_table.c.notification_type, all_stats_table.c.status, all_stats_table.c.key_type, ).order_by(all_stats_table.c.notification_type) else: query = stats.order_by(FactNotificationStatus.notification_type) return query.all()
def backfill_processing_time(start_date, end_date): """ Send historical processing time to Performance Platform. """ delta = end_date - start_date print('Sending notification processing-time data for all days between {} and {}'.format(start_date, end_date)) for i in range(delta.days + 1): # because the tz conversion funcs talk about midnight, and the midnight before last, # we want to pretend we're running this from the next morning, so add one. process_date = start_date + timedelta(days=i + 1) process_start_date = get_midnight_for_day_before(process_date) process_end_date = get_sydney_midnight_in_utc(process_date) print('Sending notification processing-time for {} - {}'.format( process_start_date.isoformat(), process_end_date.isoformat() )) send_processing_time_for_start_and_end(process_start_date, process_end_date)
def get_total_sent_notifications_for_day(day): """ :day a UTC datetime """ day_in_aet = convert_utc_to_aet(day) start_date = get_sydney_midnight_in_utc(day_in_aet) end_date = start_date + timedelta(days=1) email_count = get_total_sent_notifications_in_date_range(start_date, end_date, 'email') sms_count = get_total_sent_notifications_in_date_range(start_date, end_date, 'sms') letter_count = get_total_sent_notifications_in_date_range(start_date, end_date, 'letter') return { "start_date": start_date, "email": { "count": email_count }, "sms": { "count": sms_count }, "letter": { "count": letter_count }, }
def test_get_sydney_midnight_in_utc_returns_expected_date(date, expected_date): """ :param date a localized datetime :param expected_date a UTC datetime """ assert get_sydney_midnight_in_utc(date) == expected_date
def fetch_count_of_complaints(start_date, end_date): start_date = get_sydney_midnight_in_utc(start_date) end_date = get_sydney_midnight_in_utc(end_date + timedelta(days=1)) return Complaint.query.filter(Complaint.created_at >= start_date, Complaint.created_at < end_date).count()
def fetch_monthly_template_usage_for_service(start_date, end_date, service_id): # services_dao.replaces dao_fetch_monthly_historical_usage_by_template_for_service stats = db.session.query( FactNotificationStatus.template_id.label('template_id'), Template.name.label('name'), Template.template_type.label('template_type'), Template.is_precompiled_letter.label('is_precompiled_letter'), extract('month', FactNotificationStatus.aet_date).label('month'), extract('year', FactNotificationStatus.aet_date).label('year'), func.sum( FactNotificationStatus.notification_count).label('count')).join( Template, FactNotificationStatus.template_id == Template.id).filter( FactNotificationStatus.service_id == service_id, FactNotificationStatus.aet_date >= start_date, FactNotificationStatus.aet_date <= end_date, FactNotificationStatus.key_type != KEY_TYPE_TEST, FactNotificationStatus.notification_status != NOTIFICATION_CANCELLED, ).group_by( FactNotificationStatus.template_id, Template.name, Template.template_type, Template.is_precompiled_letter, extract('month', FactNotificationStatus.aet_date).label('month'), extract('year', FactNotificationStatus.aet_date).label('year'), ).order_by(extract('year', FactNotificationStatus.aet_date), extract('month', FactNotificationStatus.aet_date), Template.name) if start_date <= convert_utc_to_aet(datetime.utcnow()) <= end_date: today = get_sydney_midnight_in_utc(datetime.utcnow()) month = get_sydney_month_from_utc_column(Notification.created_at) stats_for_today = db.session.query( Notification.template_id.label('template_id'), Template.name.label('name'), Template.template_type.label('template_type'), Template.is_precompiled_letter.label('is_precompiled_letter'), extract('month', month).label('month'), extract('year', month).label('year'), func.count().label('count')).join( Template, Notification.template_id == Template.id, ).filter(Notification.created_at >= today, Notification.service_id == service_id, Notification.key_type != KEY_TYPE_TEST, Notification.status != NOTIFICATION_CANCELLED).group_by( Notification.template_id, Template.hidden, Template.name, Template.template_type, month) all_stats_table = stats.union_all(stats_for_today).subquery() query = db.session.query( all_stats_table.c.template_id, all_stats_table.c.name, all_stats_table.c.is_precompiled_letter, all_stats_table.c.template_type, func.cast(all_stats_table.c.month, Integer).label('month'), func.cast(all_stats_table.c.year, Integer).label('year'), func.cast(func.sum(all_stats_table.c.count), Integer).label('count'), ).group_by( all_stats_table.c.template_id, all_stats_table.c.name, all_stats_table.c.is_precompiled_letter, all_stats_table.c.template_type, all_stats_table.c.month, all_stats_table.c.year, ).order_by(all_stats_table.c.year, all_stats_table.c.month, all_stats_table.c.name) else: query = stats return query.all()
def fetch_stats_for_all_services_by_date_range(start_date, end_date, include_from_test_key=True): stats = db.session.query( FactNotificationStatus.service_id.label('service_id'), Service.name.label('name'), Service.restricted.label('restricted'), Service.research_mode.label('research_mode'), Service.active.label('active'), Service.created_at.label('created_at'), FactNotificationStatus.notification_type.label('notification_type'), FactNotificationStatus.notification_status.label('status'), func.sum( FactNotificationStatus.notification_count).label('count')).filter( FactNotificationStatus.aet_date >= start_date, FactNotificationStatus.aet_date <= end_date, FactNotificationStatus.service_id == Service.id, ).group_by( FactNotificationStatus.service_id.label('service_id'), Service.name, Service.restricted, Service.research_mode, Service.active, Service.created_at, FactNotificationStatus.notification_type, FactNotificationStatus.notification_status, ).order_by(FactNotificationStatus.service_id, FactNotificationStatus.notification_type) if not include_from_test_key: stats = stats.filter(FactNotificationStatus.key_type != KEY_TYPE_TEST) if start_date <= datetime.utcnow().date() <= end_date: today = get_sydney_midnight_in_utc(datetime.utcnow()) subquery = db.session.query( Notification.notification_type.cast( db.Text).label('notification_type'), Notification.status.label('status'), Notification.service_id.label('service_id'), func.count(Notification.id).label('count')).filter( Notification.created_at >= today).group_by( Notification.notification_type, Notification.status, Notification.service_id) if not include_from_test_key: subquery = subquery.filter(Notification.key_type != KEY_TYPE_TEST) subquery = subquery.subquery() stats_for_today = db.session.query( Service.id.label('service_id'), Service.name.label('name'), Service.restricted.label('restricted'), Service.research_mode.label('research_mode'), Service.active.label('active'), Service.created_at.label('created_at'), subquery.c.notification_type.label('notification_type'), subquery.c.status.label('status'), subquery.c.count.label('count')).outerjoin( subquery, subquery.c.service_id == Service.id) all_stats_table = stats.union_all(stats_for_today).subquery() query = db.session.query( all_stats_table.c.service_id, all_stats_table.c.name, all_stats_table.c.restricted, all_stats_table.c.research_mode, all_stats_table.c.active, all_stats_table.c.created_at, all_stats_table.c.notification_type, all_stats_table.c.status, func.cast(func.sum(all_stats_table.c.count), Integer).label('count'), ).group_by( all_stats_table.c.service_id, all_stats_table.c.name, all_stats_table.c.restricted, all_stats_table.c.research_mode, all_stats_table.c.active, all_stats_table.c.created_at, all_stats_table.c.notification_type, all_stats_table.c.status, ).order_by(all_stats_table.c.name, all_stats_table.c.notification_type, all_stats_table.c.status) else: query = stats return query.all()