def delete_notifications_older_than_retention_by_type(notification_type, qry_limit=10000): current_app.logger.info( 'Deleting {} notifications for services with flexible data retention'. format(notification_type)) flexible_data_retention = ServiceDataRetention.query.filter( ServiceDataRetention.notification_type == notification_type).all() deleted = 0 for f in flexible_data_retention: days_of_retention = get_local_timezone_midnight_in_utc( convert_utc_to_local_timezone( datetime.utcnow()).date()) - timedelta( days=f.days_of_retention) if notification_type == LETTER_TYPE: _delete_letters_from_s3(notification_type, f.service_id, days_of_retention, qry_limit) insert_update_notification_history(notification_type, days_of_retention, f.service_id) current_app.logger.info( "Deleting {} notifications for service id: {}".format( notification_type, f.service_id)) deleted += _delete_notifications(notification_type, days_of_retention, f.service_id, qry_limit) current_app.logger.info( 'Deleting {} notifications for services without flexible data retention' .format(notification_type)) seven_days_ago = get_local_timezone_midnight_in_utc( convert_utc_to_local_timezone( datetime.utcnow()).date()) - timedelta(days=7) services_with_data_retention = [ x.service_id for x in flexible_data_retention ] service_ids_to_purge = db.session.query(Service.id).filter( Service.id.notin_(services_with_data_retention)).all() for service_id in service_ids_to_purge: if notification_type == LETTER_TYPE: _delete_letters_from_s3(notification_type, service_id, seven_days_ago, qry_limit) insert_update_notification_history(notification_type, seven_days_ago, service_id) deleted += _delete_notifications(notification_type, seven_days_ago, service_id, qry_limit) current_app.logger.info( 'Finished deleting {} notifications'.format(notification_type)) return deleted
def letter_print_day(created_at): bst_print_datetime = convert_utc_to_local_timezone(created_at) + timedelta(hours=6, minutes=30) bst_print_date = bst_print_datetime.date() current_bst_date = convert_utc_to_local_timezone(datetime.utcnow()).date() if bst_print_date >= current_bst_date: return "today" else: print_date = bst_print_datetime.strftime("%d %B").lstrip("0") return "on {}".format(print_date)
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_local_timezone(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.bst_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.bst_date, FactNotificationStatus.notification_type ).all() assert len(updated_fact_data) == 1 assert updated_fact_data[0].notification_count == 2
def dao_get_provider_stats(): # this query does not include the current day since the task to populate ft_billing runs overnight current_local_datetime = convert_utc_to_local_timezone(datetime.utcnow()) first_day_of_the_month = current_local_datetime.date().replace(day=1) subquery = db.session.query( FactBilling.provider, func.sum( FactBilling.billable_units * FactBilling.rate_multiplier).label( 'current_month_billable_sms')).filter( FactBilling.notification_type == SMS_TYPE, FactBilling.bst_date >= first_day_of_the_month).group_by( FactBilling.provider).subquery() result = db.session.query( ProviderDetails.id, ProviderDetails.display_name, ProviderDetails.identifier, ProviderDetails.priority, ProviderDetails.notification_type, ProviderDetails.active, ProviderDetails.updated_at, ProviderDetails.supports_international, User.name.label('created_by_name'), func.coalesce( subquery.c.current_month_billable_sms, 0).label('current_month_billable_sms')).outerjoin( subquery, ProviderDetails.identifier == subquery.c.provider).outerjoin( User, ProviderDetails.created_by_id == User.id).order_by( ProviderDetails.notification_type, ProviderDetails.priority, ).all() return result
def format_payload(*, dataset, start_time, group_name, group_value, count, period="day"): """ :param dataset - the name of the overall graph, as referred to in the endpoint. :param start_time - UTC midnight of the day 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_local_timezone(start_time).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_for_day_letter(sample_service, sample_letter_template, mocker): yesterday = convert_utc_to_local_timezone( (datetime.now() - timedelta(days=1))).replace(hour=12, minute=00) mocker.patch("app.dao.fact_billing_dao.get_rate", side_effect=mocker_get_rate) create_notification( created_at=yesterday, 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 # Celery expects the arguments to be a string or primitive type. yesterday_str = datetime.strftime(yesterday, "%Y-%m-%d") create_nightly_billing_for_day(yesterday_str) records = FactBilling.query.order_by("rate_multiplier").all() assert len(records) == 1 record = records[0] assert record.notification_type == LETTER_TYPE assert record.bst_date == datetime.date(yesterday) assert record.rate == Decimal(2.1) assert record.billable_units == 2 assert record.rate_multiplier == 2.0
def test_create_nightly_billing_for_day_null_sent_by_sms( sample_service, sample_template, mocker): yesterday = convert_utc_to_local_timezone( (datetime.now() - timedelta(days=1))).replace(hour=12, minute=00) mocker.patch('app.dao.fact_billing_dao.get_rate', side_effect=mocker_get_rate) create_notification( created_at=yesterday, 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 # Celery expects the arguments to be a string or primitive type. yesterday_str = datetime.strftime(yesterday, "%Y-%m-%d") create_nightly_billing_for_day(yesterday_str) records = FactBilling.query.all() assert len(records) == 1 record = records[0] assert record.bst_date == datetime.date(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 = convert_utc_to_local_timezone(datetime.utcnow()) start_date = get_local_timezone_midnight_in_utc(today) end_date = get_local_timezone_midnight_in_utc(today + 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, 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_create_nightly_billing_for_day_different_sent_by( sample_service, sample_template, sample_email_template, mocker): yesterday = convert_utc_to_local_timezone( (datetime.now() - timedelta(days=1))).replace(hour=12, minute=00) mocker.patch("app.dao.fact_billing_dao.get_rate", side_effect=mocker_get_rate) # These are sms notifications create_notification( created_at=yesterday, template=sample_template, status="delivered", sent_by="sns", international=False, rate_multiplier=1.0, billable_units=1, ) records = FactBilling.query.all() assert len(records) == 0 # Celery expects the arguments to be a string or primitive type. yesterday_str = datetime.strftime(yesterday, "%Y-%m-%d") create_nightly_billing_for_day(yesterday_str) records = FactBilling.query.order_by("rate_multiplier").all() assert len(records) == 1 for i, record in enumerate(records): assert record.bst_date == datetime.date(yesterday) assert record.rate == Decimal(1.33) assert record.billable_units == 1 assert record.rate_multiplier == 1.0
def fetch_monthly_billing_for_year(service_id, year): year_start_date, year_end_date = get_financial_year(year) utcnow = datetime.utcnow() today = convert_utc_to_local_timezone(utcnow) # if year end date is less than today, we are calculating for data in the past and have no need for deltas. if year_end_date >= today: yesterday = today - timedelta(days=1) for day in [yesterday, today]: data = fetch_billing_data_for_day(process_day=day, service_id=service_id) for d in data: update_fact_billing(data=d, process_day=day) email_and_letters = (db.session.query( func.date_trunc("month", FactBilling.bst_date).cast(Date).label("month"), func.sum(FactBilling.notifications_sent).label("notifications_sent"), func.sum(FactBilling.notifications_sent).label("billable_units"), FactBilling.rate.label("rate"), FactBilling.notification_type.label("notification_type"), FactBilling.postage, ).filter( FactBilling.service_id == service_id, FactBilling.bst_date >= year_start_date.strftime("%Y-%m-%d"), FactBilling.bst_date <= year_end_date.strftime("%Y-%m-%d"), FactBilling.notification_type.in_([EMAIL_TYPE, LETTER_TYPE]), ).group_by( "month", FactBilling.rate, FactBilling.notification_type, FactBilling.postage, )) sms = (db.session.query( func.date_trunc("month", FactBilling.bst_date).cast(Date).label("month"), func.sum(FactBilling.notifications_sent).label("notifications_sent"), func.sum(FactBilling.billable_units * FactBilling.rate_multiplier).label("billable_units"), FactBilling.rate, FactBilling.notification_type, FactBilling.postage, ).filter( FactBilling.service_id == service_id, FactBilling.bst_date >= year_start_date.strftime("%Y-%m-%d"), FactBilling.bst_date <= year_end_date.strftime("%Y-%m-%d"), FactBilling.notification_type == SMS_TYPE, ).group_by( "month", FactBilling.rate, FactBilling.notification_type, FactBilling.postage, )) yearly_data = email_and_letters.union_all(sms).order_by( "month", "notification_type", "rate").all() return yearly_data
def create_empty_monthly_notification_status_stats_dict(year): utc_month_starts = get_months_for_financial_year(year) # nested dicts - data[month][template type][status] = count return { convert_utc_to_local_timezone(start).strftime('%Y-%m'): {template_type: defaultdict(int) for template_type in TEMPLATE_TYPES} for start in utc_month_starts }
def get_folder_name(_now, is_test_or_scan_letter=False): if is_test_or_scan_letter: folder_name = '' else: print_datetime = convert_utc_to_local_timezone(_now) if print_datetime.time() > LETTER_PROCESSING_DEADLINE: print_datetime += timedelta(days=1) folder_name = '{}/'.format(print_datetime.date()) return folder_name
def test_fetch_billing_data_for_today_includes_data_with_the_right_key_type(notify_db_session): service = create_service() template = create_template(service=service, template_type="email") for key_type in ['normal', 'test', 'team']: create_notification(template=template, status='delivered', key_type=key_type) today = convert_utc_to_local_timezone(datetime.utcnow()) results = fetch_billing_data_for_day(today) assert len(results) == 1 assert results[0].notifications_sent == 2
def test_fetch_billing_data_for_day_is_grouped_by_international(notify_db_session): service = create_service() template = create_template(service=service) create_notification(template=template, status='delivered', international=True) create_notification(template=template, status='delivered', international=False) today = convert_utc_to_local_timezone(datetime.utcnow()) results = fetch_billing_data_for_day(today) assert len(results) == 2 assert results[0].notifications_sent == 1 assert results[1].notifications_sent == 1
def test_fetch_billing_data_for_day_is_grouped_by_rate_mulitplier(notify_db_session): service = create_service() template = create_template(service=service) create_notification(template=template, status="delivered", rate_multiplier=1) create_notification(template=template, status="delivered", rate_multiplier=2) today = convert_utc_to_local_timezone(datetime.utcnow()) results = fetch_billing_data_for_day(today) assert len(results) == 2 assert results[0].notifications_sent == 1 assert results[1].notifications_sent == 1
def test_fetch_billing_data_for_day_is_grouped_by_provider(notify_db_session): service = create_service() template = create_template(service=service) create_notification(template=template, status='delivered', sent_by='mmg') create_notification(template=template, status='delivered', sent_by='firetext') today = convert_utc_to_local_timezone(datetime.utcnow()) results = fetch_billing_data_for_day(today) assert len(results) == 2 assert results[0].notifications_sent == 1 assert results[1].notifications_sent == 1
def create_nightly_billing(day_start=None): # day_start is a datetime.date() object. e.g. # up to 4 days of data counting back from day_start is consolidated if day_start is None: day_start = convert_utc_to_local_timezone(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) create_nightly_billing_for_day.apply_async(kwargs={"process_day": process_day.isoformat()}, queue=QueueNames.REPORTING)
def test_fetch_billing_data_for_day_is_grouped_by_template_and_notification_type(notify_db_session): service = create_service() email_template = create_template(service=service, template_type="email") sms_template = create_template(service=service, template_type="sms") create_notification(template=email_template, status='delivered') create_notification(template=sms_template, status='delivered') today = convert_utc_to_local_timezone(datetime.utcnow()) results = fetch_billing_data_for_day(today) assert len(results) == 2 assert results[0].notifications_sent == 1 assert results[1].notifications_sent == 1
def test_fetch_billing_data_for_day_sets_postage_for_emails_and_sms_to_none(notify_db_session): service = create_service() sms_template = create_template(service=service, template_type='sms') email_template = create_template(service=service, template_type='email') create_notification(template=sms_template, status='delivered') create_notification(template=email_template, status='delivered') today = convert_utc_to_local_timezone(datetime.utcnow()) results = fetch_billing_data_for_day(today) assert len(results) == 2 assert results[0].postage == 'none' assert results[1].postage == 'none'
def test_fetch_billing_data_for_day_returns_list_for_given_service(notify_db_session): service = create_service() service_2 = create_service(service_name='Service 2') template = create_template(service=service) template_2 = create_template(service=service_2) create_notification(template=template, status='delivered') create_notification(template=template_2, status='delivered') today = convert_utc_to_local_timezone(datetime.utcnow()) results = fetch_billing_data_for_day(process_day=today, service_id=service.id) assert len(results) == 1 assert results[0].service_id == service.id
def dao_old_letters_with_created_status(): yesterday_bst = convert_utc_to_local_timezone(datetime.utcnow()) - timedelta(days=1) last_processing_deadline = yesterday_bst.replace(hour=17, minute=30, second=0, microsecond=0) notifications = Notification.query.filter( Notification.updated_at < convert_local_timezone_to_utc(last_processing_deadline), Notification.notification_type == LETTER_TYPE, Notification.status == NOTIFICATION_CREATED ).order_by( Notification.updated_at ).all() return notifications
def test_fetch_billing_data_for_day_groups_by_postage(notify_db_session): service = create_service() letter_template = create_template(service=service, template_type='letter') email_template = create_template(service=service, template_type='email') create_notification(template=letter_template, status='delivered', postage='first') create_notification(template=letter_template, status='delivered', postage='first') create_notification(template=letter_template, status='delivered', postage='second') create_notification(template=email_template, status='delivered') today = convert_utc_to_local_timezone(datetime.utcnow()) results = fetch_billing_data_for_day(today) assert len(results) == 3
def test_fetch_billing_data_for_day_is_grouped_by_service(notify_db_session): service_1 = create_service() service_2 = create_service(service_name="Service 2") email_template = create_template(service=service_1) sms_template = create_template(service=service_2) create_notification(template=email_template, status="delivered") create_notification(template=sms_template, status="delivered") today = convert_utc_to_local_timezone(datetime.utcnow()) results = fetch_billing_data_for_day(today) assert len(results) == 2 assert results[0].notifications_sent == 1 assert results[1].notifications_sent == 1
def test_fetch_billing_data_for_today_includes_data_with_the_right_status(notify_db_session): service = create_service() template = create_template(service=service, template_type="email") for status in ['created', 'technical-failure']: create_notification(template=template, status=status) today = convert_utc_to_local_timezone(datetime.utcnow()) results = fetch_billing_data_for_day(today) assert results == [] for status in ['delivered', 'sending', 'temporary-failure']: create_notification(template=template, status=status) results = fetch_billing_data_for_day(today) assert len(results) == 1 assert results[0].notifications_sent == 3
def test_fetch_billing_data_for_today_includes_data_with_the_right_date(notify_db_session): process_day = datetime(2018, 4, 1, 13, 30, 0) service = create_service() template = create_template(service=service, template_type="email") create_notification(template=template, status='delivered', created_at=process_day) create_notification(template=template, status='delivered', created_at=datetime(2018, 4, 1, 4, 23, 23)) create_notification(template=template, status='delivered', created_at=datetime(2018, 3, 31, 20, 23, 23)) create_notification(template=template, status='sending', created_at=process_day + timedelta(days=1)) day_under_test = convert_utc_to_local_timezone(process_day) results = fetch_billing_data_for_day(day_under_test) assert len(results) == 1 assert results[0].notifications_sent == 2
def test_fetch_billing_data_for_day_uses_notification_history(notify_db_session): local_now = convert_utc_to_local_timezone(datetime.utcnow()) service = create_service() sms_template = create_template(service=service, template_type='sms') create_notification_history(template=sms_template, status='delivered', created_at=datetime.utcnow() - timedelta(days=8)) create_notification_history(template=sms_template, status='delivered', created_at=datetime.utcnow() - timedelta(days=8)) Notification.query.delete() db.session.commit() results = fetch_billing_data_for_day(process_day=local_now - timedelta(days=8), service_id=service.id) assert len(results) == 1 assert results[0].notifications_sent == 2
def test_update_fact_notification_status(notify_db_session): local_now = convert_utc_to_local_timezone(datetime.utcnow()) 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=local_now - timedelta(days=1)) # simulate a service with data retention - data has been moved to history and does not exist in notifications create_notification_history(template=second_template, status='temporary-failure') create_notification_history(template=second_template, created_at=local_now - timedelta(days=1)) create_notification(template=third_template, status='created') create_notification(template=third_template, created_at=local_now - timedelta(days=1)) process_day = local_now 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.bst_date, FactNotificationStatus.notification_type ).all() assert len(new_fact_data) == 3 assert new_fact_data[0].bst_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].bst_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].bst_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 test_fetch_billing_data_for_day_is_grouped_by_notification_type(notify_db_session): service = create_service() sms_template = create_template(service=service, template_type='sms') email_template = create_template(service=service, template_type='email') letter_template = create_template(service=service, template_type='letter') create_notification(template=sms_template, status='delivered') create_notification(template=sms_template, status='delivered') create_notification(template=sms_template, status='delivered') create_notification(template=email_template, status='delivered') create_notification(template=email_template, status='delivered') create_notification(template=letter_template, status='delivered') today = convert_utc_to_local_timezone(datetime.utcnow()) results = fetch_billing_data_for_day(today) assert len(results) == 3 notification_types = [x[2] for x in results if x[2] in ['email', 'sms', 'letter']] assert len(notification_types) == 3
def test_fetch_billing_data_for_day_bills_correctly_for_status(notify_db_session): service = create_service() sms_template = create_template(service=service, template_type='sms') email_template = create_template(service=service, template_type='email') letter_template = create_template(service=service, template_type='letter') for status in NOTIFICATION_STATUS_TYPES: create_notification(template=sms_template, status=status) create_notification(template=email_template, status=status) create_notification(template=letter_template, status=status) today = convert_utc_to_local_timezone(datetime.utcnow()) results = fetch_billing_data_for_day(process_day=today, service_id=service.id) sms_results = [x for x in results if x[2] == 'sms'] email_results = [x for x in results if x[2] == 'email'] letter_results = [x for x in results if x[2] == 'letter'] assert 7 == sms_results[0][7] assert 7 == email_results[0][7] assert 3 == letter_results[0][7]
def test_create_nightly_billing_for_day_different_letter_postage( notify_db_session, sample_letter_template, mocker): yesterday = convert_utc_to_local_timezone((datetime.now() - timedelta(days=1))).replace(hour=12, minute=00) mocker.patch('app.dao.fact_billing_dao.get_rate', side_effect=mocker_get_rate) for i in range(2): create_notification( created_at=yesterday, template=sample_letter_template, status='delivered', sent_by='dvla', billable_units=2, postage='first' ) create_notification( created_at=yesterday, template=sample_letter_template, status='delivered', sent_by='dvla', billable_units=2, postage='second' ) records = FactBilling.query.all() assert len(records) == 0 # Celery expects the arguments to be a string or primitive type. yesterday_str = datetime.strftime(yesterday, "%Y-%m-%d") create_nightly_billing_for_day(yesterday_str) records = FactBilling.query.order_by('postage').all() assert len(records) == 2 assert records[0].notification_type == LETTER_TYPE assert records[0].bst_date == datetime.date(yesterday) assert records[0].postage == 'first' assert records[0].notifications_sent == 2 assert records[0].billable_units == 4 assert records[1].notification_type == LETTER_TYPE assert records[1].bst_date == datetime.date(yesterday) assert records[1].postage == 'second' assert records[1].notifications_sent == 1 assert records[1].billable_units == 2