def delete_notifications_older_than_retention_by_type(notification_type, qry_limit=50000):
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:
current_app.logger.info(
"Deleting {} notifications for service id: {}".format(notification_type, f.service_id))
day_to_delete_backwards_from = get_london_midnight_in_utc(
convert_utc_to_bst(datetime.utcnow()).date()) - timedelta(days=f.days_of_retention)
deleted += _move_notifications_to_notification_history(
notification_type, f.service_id, day_to_delete_backwards_from, qry_limit)
current_app.logger.info(
'Deleting {} notifications for services without flexible data retention'.format(notification_type))
seven_days_ago = get_london_midnight_in_utc(convert_utc_to_bst(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:
deleted += _move_notifications_to_notification_history(
notification_type, service_id, seven_days_ago, qry_limit)
current_app.logger.info('Finished deleting {} notifications'.format(notification_type))
return deleted
def letter_can_be_cancelled(notification_status, notification_created_at):
'''
If letter does not have status of created or pending-virus-check
=> can't be cancelled (it has already been processed)
If it's after 5.30pm local time and the notification was created today before 5.30pm local time
=> can't be cancelled (it will already be zipped up to be sent)
'''
if notification_status not in ('created', 'pending-virus-check'):
return False
if _after_letter_processing_deadline() and _notification_created_before_today_deadline(notification_created_at):
return False
time_created_at = convert_utc_to_bst(notification_created_at)
day_created_on = time_created_at.date()
current_time = convert_utc_to_bst(datetime.utcnow())
current_day = current_time.date()
if _notification_created_before_that_day_deadline(notification_created_at) and day_created_on < current_day:
return False
if (current_day - day_created_on).days > 1:
return False
return True
def fetch_monthly_billing_for_year(service_id, year):
year_start_datetime, year_end_datetime = get_financial_year(year)
year_start_date = convert_utc_to_bst(year_start_datetime).date()
year_end_date = convert_utc_to_bst(year_end_datetime).date()
today = convert_utc_to_bst(datetime.utcnow()).date()
# 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,
FactBilling.bst_date <= year_end_date,
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,
FactBilling.bst_date <= year_end_date,
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 fetch_usage_year_for_organisation(organisation_id, year):
year_start_datetime, year_end_datetime = get_financial_year(year)
year_start_date = convert_utc_to_bst(year_start_datetime).date()
year_end_date = convert_utc_to_bst(year_end_datetime).date()
today = convert_utc_to_bst(datetime.utcnow()).date()
services = dao_get_organisation_live_services(organisation_id)
# 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:
for service in services:
data = fetch_billing_data_for_day(process_day=today,
service_id=service.id)
for d in data:
update_fact_billing(data=d, process_day=today)
service_with_usage = {}
# initialise results
for service in services:
service_with_usage[str(service.id)] = {
'service_id': service.id,
'service_name': service.name,
'free_sms_limit': 0,
'sms_remainder': 0,
'sms_billable_units': 0,
'chargeable_billable_sms': 0,
'sms_cost': 0.0,
'letter_cost': 0.0,
'emails_sent': 0,
'active': service.active
}
sms_usages = fetch_sms_billing_for_organisation(organisation_id,
year_start_date,
year_end_date)
letter_usages = fetch_letter_costs_for_organisation(
organisation_id, year_start_date, year_end_date)
email_usages = fetch_email_usage_for_organisation(organisation_id,
year_start_date,
year_end_date)
for usage in sms_usages:
service_with_usage[str(usage.service_id)] = {
'service_id': usage.service_id,
'service_name': usage.service_name,
'free_sms_limit': usage.free_sms_fragment_limit,
'sms_remainder': usage.sms_remainder,
'sms_billable_units': usage.sms_billable_units,
'chargeable_billable_sms': usage.chargeable_billable_sms,
'sms_cost': float(usage.sms_cost),
'letter_cost': 0.0,
'emails_sent': 0,
'active': usage.active
}
for letter_usage in letter_usages:
service_with_usage[str(
letter_usage.service_id)]['letter_cost'] = float(
letter_usage.letter_cost)
for email_usage in email_usages:
service_with_usage[str(
email_usage.service_id)]['emails_sent'] = email_usage.emails_sent
return service_with_usage
def _notification_created_before_today_deadline(notification_created_at):
current_bst_datetime = convert_utc_to_bst(datetime.utcnow())
todays_deadline = current_bst_datetime.replace(
hour=LETTER_PROCESSING_DEADLINE.hour,
minute=LETTER_PROCESSING_DEADLINE.minute,
)
notification_created_at_in_bst = convert_utc_to_bst(notification_created_at)
return notification_created_at_in_bst <= todays_deadline
def letter_print_day(created_at):
bst_print_datetime = convert_utc_to_bst(created_at) + timedelta(hours=6, minutes=30)
bst_print_date = bst_print_datetime.date()
current_bst_date = convert_utc_to_bst(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 create_nightly_notification_status():
current_app.logger.info("create-nightly-notification-status task: started")
yesterday = convert_utc_to_bst(
datetime.utcnow()).date() - timedelta(days=1)
# email and sms
for i in range(4):
process_day = yesterday - timedelta(days=i)
for notification_type in [SMS_TYPE, EMAIL_TYPE]:
create_nightly_notification_status_for_day.apply_async(
kwargs={
'process_day': process_day.isoformat(),
'notification_type': notification_type
},
queue=QueueNames.REPORTING)
current_app.logger.info(
f"create-nightly-notification-status task: create-nightly-notification-status-for-day task created "
f"for type {notification_type} for {process_day}")
# letters get modified for a longer time period than sms and email, so we need to reprocess for more days
for i in range(10):
process_day = yesterday - timedelta(days=i)
create_nightly_notification_status_for_day.apply_async(
kwargs={
'process_day': process_day.isoformat(),
'notification_type': LETTER_TYPE
},
queue=QueueNames.REPORTING)
current_app.logger.info(
f"create-nightly-notification-status task: create-nightly-notification-status-for-day task created "
f"for type letter for {process_day}")
def dao_get_provider_stats():
# this query does not include the current day since the task to populate ft_billing runs overnight
current_bst_datetime = convert_utc_to_bst(datetime.utcnow())
first_day_of_the_month = current_bst_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_bst(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 _notification_created_before_that_day_deadline(notification_created_at):
notification_created_at_bst_datetime = convert_utc_to_bst(notification_created_at)
created_at_day_deadline = notification_created_at_bst_datetime.replace(
hour=LETTER_PROCESSING_DEADLINE.hour,
minute=LETTER_PROCESSING_DEADLINE.minute,
)
return notification_created_at_bst_datetime <= created_at_day_deadline
def printing_today_or_tomorrow():
now_utc = datetime.utcnow()
now_bst = convert_utc_to_bst(now_utc)
if now_bst.time() < time(17, 30):
return 'today'
else:
return 'tomorrow'
def collate_letter_pdfs_to_be_sent():
"""
Finds all letters which are still waiting to be sent to DVLA for printing
This would usually be run at 5.50pm and collect up letters created between before 5:30pm today
that have not yet been sent.
If run after midnight, it will collect up letters created before 5:30pm the day before.
"""
current_app.logger.info("starting collate-letter-pdfs-to-be-sent")
print_run_date = convert_utc_to_bst(datetime.utcnow())
if print_run_date.time() < LETTER_PROCESSING_DEADLINE:
print_run_date = print_run_date - timedelta(days=1)
print_run_deadline = print_run_date.replace(hour=17,
minute=30,
second=0,
microsecond=0)
_get_letters_and_sheets_volumes_and_send_to_dvla(print_run_deadline)
for postage in POSTAGE_TYPES:
current_app.logger.info(
f"starting collate-letter-pdfs-to-be-sent processing for postage class {postage}"
)
letters_to_print = get_key_and_size_of_letters_to_be_sent_to_print(
print_run_deadline, postage)
for i, letters in enumerate(group_letters(letters_to_print)):
filenames = [letter['Key'] for letter in letters]
service_id = letters[0]['ServiceId']
hash = urlsafe_b64encode(
sha512(''.join(filenames).encode()).digest())[:20].decode()
# eg NOTIFY.2018-12-31.001.Wjrui5nAvObjPd-3GEL-.ZIP
dvla_filename = 'NOTIFY.{date}.{postage}.{num:03}.{hash}.{service_id}.ZIP'.format(
date=print_run_deadline.strftime("%Y-%m-%d"),
postage=RESOLVE_POSTAGE_FOR_FILE_NAME[postage],
num=i + 1,
hash=hash,
service_id=service_id)
current_app.logger.info(
'Calling task zip-and-send-letter-pdfs for {} pdfs to upload {} with total size {:,} bytes'
.format(len(filenames), dvla_filename,
sum(letter['Size'] for letter in letters)))
notify_celery.send_task(name=TaskNames.ZIP_AND_SEND_LETTER_PDFS,
kwargs={
'filenames_to_zip': filenames,
'upload_filename': dvla_filename
},
queue=QueueNames.PROCESS_FTP,
compression='zlib')
current_app.logger.info(
f"finished collate-letter-pdfs-to-be-sent processing for postage class {postage}"
)
current_app.logger.info("finished collate-letter-pdfs-to-be-sent")
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_bst(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, *, dont_use_sending_date=False):
if dont_use_sending_date:
folder_name = ''
else:
print_datetime = convert_utc_to_bst(_now)
if print_datetime.time() > LETTER_PROCESSING_DEADLINE:
print_datetime += timedelta(days=1)
folder_name = '{}/'.format(print_datetime.date())
return folder_name
def printing_today_or_tomorrow(created_at):
print_cutoff = convert_bst_to_utc(
convert_utc_to_bst(datetime.utcnow()).replace(
hour=17, minute=30)).replace(tzinfo=pytz.utc)
created_at = utc_string_to_aware_gmt_datetime(created_at)
if created_at < print_cutoff:
return 'today'
else:
return 'tomorrow'
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_bst(datetime.utcnow())
results = fetch_billing_data_for_day(today.date())
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_bst(datetime.utcnow())
results = fetch_billing_data_for_day(today.date())
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_bst(datetime.utcnow())
results = fetch_billing_data_for_day(today.date())
assert len(results) == 2
assert results[0].notifications_sent == 1
assert results[1].notifications_sent == 1
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_bst(datetime.utcnow())
results = fetch_billing_data_for_day(process_day=today.date(), service_id=service.id)
assert len(results) == 1
assert results[0].service_id == service.id
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_bst(datetime.utcnow())
results = fetch_billing_data_for_day(today.date())
assert len(results) == 2
assert results[0].postage == 'none'
assert results[1].postage == 'none'
def test_fetch_billing_data_for_day_groups_by_page_count(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='second', billable_units=1)
create_notification(template=letter_template, status='delivered', postage='second', billable_units=1)
create_notification(template=letter_template, status='delivered', postage='second', billable_units=2)
create_notification(template=email_template, status='delivered')
today = convert_utc_to_bst(datetime.utcnow())
results = fetch_billing_data_for_day(today.date())
assert len(results) == 3
def dao_old_letters_with_created_status():
yesterday_bst = convert_utc_to_bst(datetime.utcnow()) - timedelta(days=1)
last_processing_deadline = yesterday_bst.replace(hour=17, minute=30, second=0, microsecond=0)
notifications = Notification.query.filter(
Notification.created_at < convert_bst_to_utc(last_processing_deadline),
Notification.notification_type == LETTER_TYPE,
Notification.status == NOTIFICATION_CREATED
).order_by(
Notification.created_at
).all()
return notifications
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_bst(datetime.utcnow())
results = fetch_billing_data_for_day(today.date())
assert len(results) == 2
assert results[0].notifications_sent == 1
assert results[1].notifications_sent == 1
def test_fetch_billing_data_for_day_only_calls_query_for_all_channels(
notify_db_session, notification_type):
service = create_service(service_permissions=[notification_type])
email_template = create_template(service=service, template_type="email")
sms_template = create_template(service=service, template_type="sms")
letter_template = create_template(service=service, template_type="letter")
create_notification(template=email_template, status='delivered')
create_notification(template=sms_template, status='delivered')
create_notification(template=letter_template, status='delivered')
today = convert_utc_to_bst(datetime.utcnow())
results = fetch_billing_data_for_day(process_day=today.date(),
check_permissions=False)
assert len(results) == 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, 3, 31, 23, 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_bst(process_day)
results = fetch_billing_data_for_day(day_under_test.date())
assert len(results) == 1
assert results[0].notifications_sent == 2
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_bst(datetime.utcnow())
results = fetch_billing_data_for_day(today.date())
assert results == []
for status in ['delivered', 'sending', 'temporary-failure']:
create_notification(template=template, status=status)
results = fetch_billing_data_for_day(today.date())
assert len(results) == 1
assert results[0].notifications_sent == 3
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_bst(
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_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_bst(datetime.utcnow())
results = fetch_billing_data_for_day(today.date())
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 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_bst(
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)
for notification_type in [SMS_TYPE, EMAIL_TYPE, LETTER_TYPE]:
create_nightly_notification_status_for_day.apply_async(
kwargs={
'process_day': process_day.isoformat(),
'notification_type': notification_type
},
queue=QueueNames.REPORTING)
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_bst(datetime.utcnow())
results = fetch_billing_data_for_day(process_day=today.date(), 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]
