def get_next_days_until(until): now = convert_utc_to_aet(datetime.utcnow()) days = int((until - now).total_seconds() / (60 * 60 * 24)) return [ get_human_day((now + timedelta(days=i)).replace(tzinfo=pytz.utc), prefix_today_with='Later t') for i in range(0, days + 1) ]
def format_payload(*, dataset, date, group_name, group_value, count, period='day'): """ :param dataset - the name of the overall graph, as referred to in the endpoint. :param date - the date we're sending stats for :param group_name - the name of the individual groups of data, eg "channel" or "status" :param group_value - the value of the group, eg "sms" or "email" for group_name=channel :param count - the actual numeric value to send :param period - the period that this data covers - "day", "week", "month", "quarter". """ payload = { '_timestamp': convert_utc_to_aet(date).isoformat(), 'service': 'govuk-notify', 'dataType': dataset, 'period': period, 'count': count, group_name: group_value, } payload['_id'] = PerformancePlatformClient.generate_payload_id( payload, group_name) return payload
def test_create_nightly_billing_letter( notify_db, notify_db_session, sample_service, sample_letter_template, mocker): yesterday = datetime.utcnow() - timedelta(days=1) mocker.patch('app.celery.reporting_tasks.get_rate', side_effect=mocker_get_rate) sample_notification( notify_db, notify_db_session, created_at=yesterday, service=sample_service, template=sample_letter_template, status='delivered', sent_by='dvla', international=False, rate_multiplier=2.0, billable_units=2, ) records = FactBilling.query.all() assert len(records) == 0 create_nightly_billing() records = FactBilling.query.order_by('rate_multiplier').all() assert len(records) == 1 record = records[0] assert record.notification_type == LETTER_TYPE assert record.aet_date == datetime.date(convert_utc_to_aet(yesterday)) assert record.rate == Decimal(2.1) assert record.billable_units == 2 assert record.rate_multiplier == 2.0
def delete_notifications_created_more_than_a_week_ago_by_type(notification_type): seven_days_ago = convert_utc_to_aet(datetime.utcnow()).date() - timedelta(days=7) deleted = db.session.query(Notification).filter( func.date(Notification.created_at) < seven_days_ago, Notification.notification_type == notification_type, ).delete(synchronize_session='fetch') return deleted
def increment_template_usage_cache(service_id, template_id, created_at): key = cache_key_for_service_template_usage_per_day(service_id, convert_utc_to_aet(created_at)) redis_store.increment_hash_value(key, template_id) # set key to expire in eight days - we don't know if we've just created the key or not, so must assume that we # have and reset the expiry. Eight days is longer than any notification is in the notifications table, so we'll # always capture the full week's numbers redis_store.expire(key, current_app.config['EXPIRE_CACHE_EIGHT_DAYS'])
def test__update_fact_notification_status_updates_row(notify_db_session): first_service = create_service(service_name='First Service') first_template = create_template(service=first_service) create_notification(template=first_template, status='delivered') process_day = convert_utc_to_aet(datetime.utcnow()) data = fetch_notification_status_for_day(process_day=process_day) update_fact_notification_status(data=data, process_day=process_day.date()) new_fact_data = FactNotificationStatus.query.order_by(FactNotificationStatus.aet_date, FactNotificationStatus.notification_type ).all() assert len(new_fact_data) == 1 assert new_fact_data[0].notification_count == 1 create_notification(template=first_template, status='delivered') data = fetch_notification_status_for_day(process_day=process_day) update_fact_notification_status(data=data, process_day=process_day.date()) updated_fact_data = FactNotificationStatus.query.order_by(FactNotificationStatus.aet_date, FactNotificationStatus.notification_type ).all() assert len(updated_fact_data) == 1 assert updated_fact_data[0].notification_count == 2
def test_create_nightly_billing_null_sent_by_sms(notify_db, notify_db_session, sample_service, sample_template, mocker): yesterday = datetime.utcnow() - timedelta(days=1) mocker.patch('app.celery.reporting_tasks.get_rate', side_effect=mocker_get_rate) sample_notification( notify_db, notify_db_session, created_at=yesterday, service=sample_service, template=sample_template, status='delivered', sent_by=None, international=False, rate_multiplier=1.0, billable_units=1, ) records = FactBilling.query.all() assert len(records) == 0 create_nightly_billing() records = FactBilling.query.all() assert len(records) == 1 record = records[0] assert record.aet_date == datetime.date(convert_utc_to_aet(yesterday)) assert record.rate == Decimal(1.33) assert record.billable_units == 1 assert record.rate_multiplier == 1 assert record.provider == 'unknown'
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 test_get_month_start_and_end_date_in_utc(month, year, expected_start, expected_end): month_year = datetime(year, month, 10, 13, 30, 00) month_year_in_aet = convert_utc_to_aet(month_year) result = get_month_start_and_end_date_in_utc(month_year_in_aet) assert result[0] == expected_start assert result[1] == expected_end
def populate_monthly_billing(): # for every service with billable units this month update billing totals for yesterday # this will overwrite the existing amount. yesterday = datetime.utcnow() - timedelta(days=1) yesterday_in_aet = convert_utc_to_aet(yesterday) start_date, end_date = get_month_start_and_end_date_in_utc(yesterday_in_aet) services = get_service_ids_that_need_billing_populated(start_date=start_date, end_date=end_date) [create_or_update_monthly_billing(service_id=s.service_id, billing_month=end_date) for s in services]
def get_human_day(aet_time, prefix_today_with='T'): now = convert_utc_to_aet(datetime.utcnow()) # Add 1 hour to get ‘midnight today’ instead of ‘midnight tomorrow’ time = (aet_time - timedelta(hours=1)).strftime('%A') if time == now.strftime('%A'): return '{}oday'.format(prefix_today_with) if time == (now + timedelta(days=1)).strftime('%A'): return 'Tomorrow' return time
def create_or_update_monthly_billing(service_id, billing_month): """ :param billing_month a UTC datetime """ billing_month_in_aet = convert_utc_to_aet(billing_month) start_date, end_date = get_month_start_and_end_date_in_utc(billing_month_in_aet) _update_monthly_billing(service_id, start_date, end_date, SMS_TYPE) _update_monthly_billing(service_id, start_date, end_date, EMAIL_TYPE) _update_monthly_billing(service_id, start_date, end_date, LETTER_TYPE)
def test_create_nightly_notification_status(notify_db_session): first_service = create_service(service_name='First Service') first_template = create_template(service=first_service) second_service = create_service(service_name='second Service') second_template = create_template(service=second_service, template_type='email') third_service = create_service(service_name='third Service') third_template = create_template(service=third_service, template_type='letter') create_notification(template=first_template, status='delivered') create_notification(template=first_template, status='delivered', created_at=datetime.utcnow() - timedelta(days=1)) create_notification(template=first_template, status='delivered', created_at=datetime.utcnow() - timedelta(days=2)) create_notification(template=first_template, status='delivered', created_at=datetime.utcnow() - timedelta(days=4)) create_notification(template=first_template, status='delivered', created_at=datetime.utcnow() - timedelta(days=5)) create_notification(template=second_template, status='temporary-failure') create_notification(template=second_template, status='temporary-failure', created_at=datetime.utcnow() - timedelta(days=1)) create_notification(template=second_template, status='temporary-failure', created_at=datetime.utcnow() - timedelta(days=2)) create_notification(template=second_template, status='temporary-failure', created_at=datetime.utcnow() - timedelta(days=4)) create_notification(template=second_template, status='temporary-failure', created_at=datetime.utcnow() - timedelta(days=5)) create_notification(template=third_template, status='created') create_notification(template=third_template, status='created', created_at=datetime.utcnow() - timedelta(days=1)) create_notification(template=third_template, status='created', created_at=datetime.utcnow() - timedelta(days=2)) create_notification(template=third_template, status='created', created_at=datetime.utcnow() - timedelta(days=4)) create_notification(template=third_template, status='created', created_at=datetime.utcnow() - timedelta(days=5)) assert len(FactNotificationStatus.query.all()) == 0 create_nightly_notification_status() new_data = FactNotificationStatus.query.order_by( FactNotificationStatus.aet_date, FactNotificationStatus.notification_type ).all() assert len(new_data) == 9 assert str(new_data[0].aet_date) == datetime.strftime(convert_utc_to_aet(datetime.utcnow() - timedelta(days=4)), "%Y-%m-%d") assert str(new_data[3].aet_date) == datetime.strftime(convert_utc_to_aet(datetime.utcnow() - timedelta(days=2)), "%Y-%m-%d") assert str(new_data[6].aet_date) == datetime.strftime(convert_utc_to_aet(datetime.utcnow() - timedelta(days=1)), "%Y-%m-%d")
def get_human_day(time): now = convert_utc_to_aet(datetime.utcnow()) # Add 1 minute to transform 00:00 into ‘midnight today’ instead of ‘midnight tomorrow’ date = (gmt_timezones(time) - timedelta(minutes=1)).date() if date == (now + timedelta(days=1)).date(): return 'tomorrow' if date == now.date(): return 'today' if date == (now - timedelta(days=1)).date(): return 'yesterday' return _format_datetime_short(date)
def test_create_nightly_billing_sms_rate_multiplier( notify_db, notify_db_session, sample_service, sample_template, mocker, second_rate, records_num, billable_units, multiplier): yesterday = datetime.utcnow() - timedelta(days=1) mocker.patch('app.celery.reporting_tasks.get_rate', side_effect=mocker_get_rate) # These are sms notifications sample_notification( notify_db, notify_db_session, created_at=yesterday, service=sample_service, template=sample_template, status='delivered', sent_by='mmg', international=False, rate_multiplier=1.0, billable_units=1, ) sample_notification( notify_db, notify_db_session, created_at=yesterday, service=sample_service, template=sample_template, status='delivered', sent_by='mmg', international=False, rate_multiplier=second_rate, billable_units=1, ) records = FactBilling.query.all() assert len(records) == 0 create_nightly_billing() records = FactBilling.query.order_by('rate_multiplier').all() assert len(records) == records_num for i, record in enumerate(records): assert record.aet_date == datetime.date(convert_utc_to_aet(yesterday)) assert record.rate == Decimal(1.33) assert record.billable_units == billable_units assert record.rate_multiplier == multiplier[i]
def _transform_billing_for_month_sms(billing_for_month): month_name = datetime.strftime(convert_utc_to_aet(billing_for_month.start_date), "%B") billing_units = rate = 0 for total in billing_for_month.monthly_totals: billing_units += (total['billing_units'] * total['rate_multiplier']) rate = total['rate'] return { "month": month_name, "billing_units": billing_units, "notification_type": billing_for_month.notification_type, "rate": rate }
def test_create_nightly_billing_different_templates( notify_db, notify_db_session, sample_service, sample_template, sample_email_template, mocker): yesterday = datetime.utcnow() - timedelta(days=1) mocker.patch('app.celery.reporting_tasks.get_rate', side_effect=mocker_get_rate) sample_notification( notify_db, notify_db_session, created_at=yesterday, service=sample_service, template=sample_template, status='delivered', sent_by='twilio', international=False, rate_multiplier=1.0, billable_units=1, ) sample_notification( notify_db, notify_db_session, created_at=yesterday, service=sample_service, template=sample_email_template, status='delivered', sent_by='ses', international=False, rate_multiplier=0, billable_units=0, ) records = FactBilling.query.all() assert len(records) == 0 create_nightly_billing() records = FactBilling.query.order_by('rate_multiplier').all() assert len(records) == 2 multiplier = [0, 1] billable_units = [0, 1] rate = [0, Decimal(1.33)] for i, record in enumerate(records): assert record.aet_date == datetime.date(convert_utc_to_aet(yesterday)) assert record.rate == rate[i] assert record.billable_units == billable_units[i] assert record.rate_multiplier == multiplier[i]
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 test_update_fact_notification_status(notify_db_session): first_service = create_service(service_name='First Service') first_template = create_template(service=first_service) second_service = create_service(service_name='second Service') second_template = create_template(service=second_service, template_type='email') third_service = create_service(service_name='third Service') third_template = create_template(service=third_service, template_type='letter') create_notification(template=first_template, status='delivered') create_notification(template=first_template, created_at=datetime.utcnow() - timedelta(days=1)) create_notification(template=second_template, status='temporary-failure') create_notification(template=second_template, created_at=datetime.utcnow() - timedelta(days=1)) create_notification(template=third_template, status='created') create_notification(template=third_template, created_at=datetime.utcnow() - timedelta(days=1)) process_day = convert_utc_to_aet(datetime.utcnow()) data = fetch_notification_status_for_day(process_day=process_day) update_fact_notification_status(data=data, process_day=process_day.date()) new_fact_data = FactNotificationStatus.query.order_by(FactNotificationStatus.aet_date, FactNotificationStatus.notification_type ).all() assert len(new_fact_data) == 3 assert new_fact_data[0].aet_date == process_day.date() assert new_fact_data[0].template_id == second_template.id assert new_fact_data[0].service_id == second_service.id assert new_fact_data[0].job_id == UUID('00000000-0000-0000-0000-000000000000') assert new_fact_data[0].notification_type == 'email' assert new_fact_data[0].notification_status == 'temporary-failure' assert new_fact_data[0].notification_count == 1 assert new_fact_data[1].aet_date == process_day.date() assert new_fact_data[1].template_id == third_template.id assert new_fact_data[1].service_id == third_service.id assert new_fact_data[1].job_id == UUID('00000000-0000-0000-0000-000000000000') assert new_fact_data[1].notification_type == 'letter' assert new_fact_data[1].notification_status == 'created' assert new_fact_data[1].notification_count == 1 assert new_fact_data[2].aet_date == process_day.date() assert new_fact_data[2].template_id == first_template.id assert new_fact_data[2].service_id == first_service.id assert new_fact_data[2].job_id == UUID('00000000-0000-0000-0000-000000000000') assert new_fact_data[2].notification_type == 'sms' assert new_fact_data[2].notification_status == 'delivered' assert new_fact_data[2].notification_count == 1
def create_nightly_notification_status(day_start=None): # day_start is a datetime.date() object. e.g. # 4 days of data counting back from day_start is consolidated if day_start is None: day_start = convert_utc_to_aet( datetime.utcnow()).date() - timedelta(days=1) else: # When calling the task its a string in the format of "YYYY-MM-DD" day_start = datetime.strptime(day_start, "%Y-%m-%d").date() for i in range(0, 4): process_day = day_start - timedelta(days=i) transit_data = fetch_notification_status_for_day( process_day=process_day) update_fact_notification_status(transit_data, process_day) current_app.logger.info( "create-nightly-notification-status task: {} rows updated for day: {}" .format(len(transit_data), process_day))
def _transform_billing_for_month_letters(billing_for_month): month_name = datetime.strftime(convert_utc_to_aet(billing_for_month.start_date), "%B") x = list() for total in billing_for_month.monthly_totals: y = { "month": month_name, "billing_units": (total['billing_units'] * total['rate_multiplier']), "notification_type": billing_for_month.notification_type, "rate": total['rate'] } x.append(y) if len(billing_for_month.monthly_totals) == 0: x.append({ "month": month_name, "billing_units": 0, "notification_type": billing_for_month.notification_type, "rate": 0 }) return x
def get_platform_stats(): if request.args: validate(request.args, platform_stats_request) include_from_test_key = request.args.get('include_from_test_key', 'True') != 'False' # If start and end date are not set, we are expecting today's stats. today = str(convert_utc_to_aet(datetime.utcnow()).date()) start_date = datetime.strptime(request.args.get('start_date', today), '%Y-%m-%d').date() end_date = datetime.strptime(request.args.get('end_date', today), '%Y-%m-%d').date() end_date = datetime.strptime(request.args.get('end_date', today), '%Y-%m-%d').date() data = fetch_aggregate_stats_by_date_range_for_all_services( start_date=start_date, end_date=end_date, include_from_test_key=include_from_test_key) stats = format_admin_stats(data) return jsonify(stats)
def create_nightly_billing(): yesterday = datetime.utcnow() - timedelta(days=1) # 3 days of data counting back from yesterday is consolidated. for i in range(0, 3): process_day = yesterday - timedelta(days=i) process_day_in_aet = convert_utc_to_aet(process_day) ds = datetime.combine(process_day, time.min) de = datetime.combine(process_day + timedelta(days=1), time.min) try: create_nightly_billing_for_day(process_day, process_day_in_aet, ds, de) except IntegrityError as e: # SQS is at-least-once message delivery which means this celery # task handler may fire more than once. If there is an # IntegrityError of the duplicate key kind it means that the task # message was delivered more than once. if not is_duplicate_key_integrity_error(e): raise e current_app.logger.info( f"Ignoring duplicate key IntegrityError for: process_day: {process_day}, process_day_in_aet: {process_day_in_aet}" )
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 create_nightly_notification_status(day_start=None): # day_start is a datetime.date() object. e.g. # 4 days of data counting back from day_start is consolidated if day_start is None: day_start = convert_utc_to_aet(datetime.utcnow()).date() - timedelta(days=1) else: # When calling the task its a string in the format of "YYYY-MM-DD" day_start = datetime.strptime(day_start, "%Y-%m-%d").date() for i in range(0, 4): process_day = day_start - timedelta(days=i) try: fetch_and_update_fact_notification_status(process_day) except IntegrityError as e: # SQS is at-least-once message delivery which means this celery # task handler may fire more than once. If there is an # IntegrityError of the duplicate key kind it means that the task # message was delivered more than once. if not is_duplicate_key_integrity_error(e): raise e current_app.logger.info( f"Ignoring duplicate key IntegrityError for process day: {process_day}" )
def get_billing_date_in_aet_from_filename(filename): datetime_string = filename.split('-')[1] datetime_obj = datetime.strptime(datetime_string, '%Y%m%d%H%M%S') return convert_utc_to_aet(datetime_obj).date()
def get_monthly_billing_by_notification_type(service_id, billing_month, notification_type): billing_month_in_aet = convert_utc_to_aet(billing_month) start_date, _ = get_month_start_and_end_date_in_utc(billing_month_in_aet) return get_monthly_billing_entry(service_id, start_date, notification_type)
def get_current_financial_year(): now = convert_utc_to_aet(datetime.utcnow()) current_month = int(now.strftime('%-m')) current_year = int(now.strftime('%Y')) year = current_year if current_month > 6 else current_year - 1 return get_financial_year(year)
def create_nightly_billing(): yesterday = datetime.utcnow() - timedelta(days=1) non_letter_rates = [ (r.notification_type, r.valid_from, r.rate) for r in Rate.query.order_by(desc(Rate.valid_from)).all() ] letter_rates = [ (r.start_date, r.crown, r.sheet_count, r.rate) for r in LetterRate.query.order_by(desc(LetterRate.start_date)).all() ] # 3 days of data counting back from yesterday is consolidated. for i in range(0, 3): process_day = yesterday - timedelta(days=i) process_day_in_aet = convert_utc_to_aet(process_day) ds = datetime.combine(process_day, time.min) de = datetime.combine(process_day + timedelta(days=1), time.min) transit_data = db.session.query( Notification.template_id, Notification.service_id, Notification.notification_type, func.coalesce( Notification.sent_by, case([(Notification.notification_type == 'letter', 'dvla'), (Notification.notification_type == 'sms', 'unknown'), (Notification.notification_type == 'email', 'ses')]), ).label('sent_by'), func.coalesce(Notification.rate_multiplier, 1).label('rate_multiplier'), func.coalesce(Notification.international, False).label('international'), func.sum(Notification.billable_units).label('billable_units'), func.count().label('notifications_sent'), Service.crown, ).filter( Notification.status != NOTIFICATION_CREATED, # at created status, provider information is not available Notification.status != NOTIFICATION_TECHNICAL_FAILURE, Notification.key_type != KEY_TYPE_TEST, Notification.created_at >= ds, Notification.created_at < de).group_by( Notification.template_id, Notification.service_id, Notification.notification_type, 'sent_by', Notification.rate_multiplier, Notification.international, Service.crown).join(Service).all() updated_records = 0 inserted_records = 0 for data in transit_data: update_count = FactBilling.query.filter( FactBilling.aet_date == datetime.date(process_day_in_aet), FactBilling.template_id == data.template_id, FactBilling.service_id == data.service_id, FactBilling.provider == data. sent_by, # This could be zero - this is a bug that needs to be fixed. FactBilling.rate_multiplier == data.rate_multiplier, FactBilling.notification_type == data.notification_type, FactBilling.international == data.international).update( { "notifications_sent": data.notifications_sent, "billable_units": data.billable_units }, synchronize_session=False) if update_count == 0: billing_record = FactBilling( aet_date=process_day_in_aet, template_id=data.template_id, service_id=data.service_id, notification_type=data.notification_type, provider=data.sent_by, rate_multiplier=data.rate_multiplier, international=data.international, billable_units=data.billable_units, notifications_sent=data.notifications_sent, rate=get_rate(non_letter_rates, letter_rates, data.notification_type, process_day, data.crown, data.rate_multiplier)) db.session.add(billing_record) inserted_records += 1 updated_records += update_count db.session.commit() current_app.logger.info( 'ft_billing {} to {}: {} rows updated, {} rows inserted'.format( ds, de, updated_records, inserted_records))
def test_get_utc_in_aet_returns_expected_date(date, expected_date): assert convert_utc_to_aet(date) == expected_date