def test_get_rates_for_daterange_returns_empty_list_if_year_is_before_earliest_rate(
notify_db, notify_db_session):
set_up_rate(notify_db, datetime(2010, 6, 30, 14, 00), 0.015)
set_up_rate(notify_db, datetime(2011, 9, 1), 0.0175)
start_date, end_date = get_financial_year(2008)
rates = get_rates_for_daterange(start_date, end_date, SMS_TYPE)
assert rates == []
def test_get_financial_year():
start, end = get_financial_year(2000)
assert str(
start) == '2000-06-30 14:00:00' # 01 July 2000 00:00:00.0 in UTC
assert str(
end
) == '2001-06-30 13:59:59.999999' # 30 June 2001 23:59:59.999999 in UTC
def dao_fetch_monthly_historical_stats_for_service(service_id, year):
month = get_sydney_month_from_utc_column(NotificationHistory.created_at)
start_date, end_date = get_financial_year(year)
rows = db.session.query(
NotificationHistory.notification_type, NotificationHistory.status,
month,
func.count(NotificationHistory.id).label('count')).filter(
NotificationHistory.service_id == service_id,
NotificationHistory.created_at.between(
start_date,
end_date)).group_by(NotificationHistory.notification_type,
NotificationHistory.status,
month).order_by(month)
months = {
datetime.strftime(created_date, '%Y-%m'): {
template_type: dict.fromkeys(NOTIFICATION_STATUS_TYPES, 0)
for template_type in TEMPLATE_TYPES
}
for created_date in
[datetime(year, month, 1) for month in range(7, 13)] +
[datetime(year + 1, month, 1) for month in range(1, 7)]
}
for notification_type, status, created_date, count in rows:
months[datetime.strftime(created_date,
"%Y-%m")][notification_type][status] = count
return months
def get_fragment_count(service_id, year=None):
shared_filters = [
NotificationHistory.service_id == service_id,
NotificationHistory.status.in_(NOTIFICATION_STATUS_TYPES_BILLABLE),
NotificationHistory.key_type != KEY_TYPE_TEST
]
if year:
shared_filters.append(NotificationHistory.created_at.between(
*get_financial_year(year)
))
sms_count = db.session.query(
func.sum(NotificationHistory.billable_units)
).filter(
NotificationHistory.notification_type == SMS_TYPE,
*shared_filters
)
email_count = db.session.query(
func.count(NotificationHistory.id)
).filter(
NotificationHistory.notification_type == EMAIL_TYPE,
*shared_filters
)
return {
'sms_count': int(sms_count.scalar() or 0),
'email_count': email_count.scalar() or 0
}
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 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 get_monthly_template_usage(service_id):
try:
start_date, end_date = get_financial_year(int(request.args.get('year', 'NaN')))
data = fetch_monthly_template_usage_for_service(
start_date=start_date,
end_date=end_date,
service_id=service_id
)
stats = list()
for i in data:
stats.append(
{
'template_id': str(i.template_id),
'name': i.name,
'type': i.template_type,
'month': i.month,
'year': i.year,
'count': i.count,
'is_precompiled_letter': i.is_precompiled_letter
}
)
return jsonify(stats=stats), 200
except ValueError:
raise InvalidRequest('Year must be a number', status_code=400)
def test_get_rates_for_daterange_early_rate(notify_db, notify_db_session):
set_up_rate(notify_db, datetime(2015, 6, 1), 0.014)
set_up_rate(notify_db, datetime(2016, 6, 1), 0.015)
set_up_rate(notify_db, datetime(2016, 9, 1), 0.016)
set_up_rate(notify_db, datetime(2017, 6, 1), 0.0175)
start_date, end_date = get_financial_year(2016)
rates = get_rates_for_daterange(start_date, end_date, SMS_TYPE)
assert len(rates) == 3
def test_get_rates_for_daterange_in_the_future(notify_db, notify_db_session):
set_up_rate(notify_db, datetime(2016, 4, 1), 0.015)
set_up_rate(notify_db, datetime(2017, 6, 1), 0.0175)
start_date, end_date = get_financial_year(2018)
rates = get_rates_for_daterange(start_date, end_date, SMS_TYPE)
assert datetime.strftime(rates[0].valid_from,
'%Y-%m-%d %H:%M:%S') == "2017-06-01 00:00:00"
assert rates[0].rate == 0.0175
def test_get_rates_for_daterange(notify_db, notify_db_session):
set_up_rate(notify_db, datetime(2016, 5, 18), 0.016)
set_up_rate(notify_db, datetime(2017, 3, 31, 23), 0.0158)
start_date, end_date = get_financial_year(2017)
rates = get_rates_for_daterange(start_date, end_date, SMS_TYPE)
assert len(rates) == 1
assert datetime.strftime(rates[0].valid_from,
'%Y-%m-%d %H:%M:%S') == "2017-03-31 23:00:00"
assert rates[0].rate == 0.0158
def test_get_rates_for_daterange_edge_case(notify_db, notify_db_session):
set_up_rate(notify_db, datetime(2010, 6, 30, 14, 00), 0.015)
set_up_rate(notify_db, datetime(2011, 6, 30, 14, 00), 0.0175)
start_date, end_date = get_financial_year(2010)
rates = get_rates_for_daterange(start_date, end_date, SMS_TYPE)
assert len(rates) == 1
assert datetime.strftime(rates[0].valid_from,
'%Y-%m-%d %H:%M:%S') == "2010-06-30 14:00:00"
assert rates[0].rate == 0.015
def get_billing_data_for_financial_year(service_id, year, notification_types=[SMS_TYPE, EMAIL_TYPE, LETTER_TYPE]):
# Update totals to the latest so we include data for today
now = datetime.utcnow()
create_or_update_monthly_billing(service_id=service_id, billing_month=now)
start_date, end_date = get_financial_year(year)
results = get_yearly_billing_data_for_date_range(
service_id, start_date, end_date, notification_types
)
return results
def get_monthly_billing_data(service_id, year):
start_date, end_date = get_financial_year(year)
rates = get_rates_for_daterange(start_date, end_date, SMS_TYPE)
if not rates:
return []
result = []
for r, n in zip(rates, rates[1:]):
result.extend(billing_data_per_month_query(r.rate, service_id, r.valid_from, n.valid_from, SMS_TYPE))
result.extend(billing_data_per_month_query(rates[-1].rate, service_id, rates[-1].valid_from, end_date, SMS_TYPE))
return [(datetime.strftime(x[0], "%B"), x[1], x[2], x[3], x[4], x[5]) for x in result]
def test_get_rates_for_daterange_multiple_result_per_year(
notify_db, notify_db_session):
set_up_rate(notify_db, datetime(2010, 6, 30, 14, 00), 0.015)
set_up_rate(notify_db, datetime(2010, 7, 18), 0.016)
set_up_rate(notify_db, datetime(2011, 6, 30, 14, 00), 0.0158)
start_date, end_date = get_financial_year(2010)
rates = get_rates_for_daterange(start_date, end_date, SMS_TYPE)
assert len(rates) == 2
assert datetime.strftime(rates[0].valid_from,
'%Y-%m-%d %H:%M:%S') == "2010-06-30 14:00:00"
assert rates[0].rate == 0.015
assert datetime.strftime(rates[1].valid_from,
'%Y-%m-%d %H:%M:%S') == "2010-07-18 00:00:00"
assert rates[1].rate == 0.016
def test_get_rates_for_daterange_returns_correct_rates(notify_db,
notify_db_session):
set_up_rate(notify_db, datetime(2016, 4, 1), 0.015)
set_up_rate(notify_db, datetime(2016, 9, 1), 0.016)
set_up_rate(notify_db, datetime(2017, 6, 1), 0.0175)
start_date, end_date = get_financial_year(2017)
rates_2017 = get_rates_for_daterange(start_date, end_date, SMS_TYPE)
assert len(rates_2017) == 2
assert datetime.strftime(rates_2017[0].valid_from,
'%Y-%m-%d %H:%M:%S') == "2016-09-01 00:00:00"
assert rates_2017[0].rate == 0.016
assert datetime.strftime(rates_2017[1].valid_from,
'%Y-%m-%d %H:%M:%S') == "2017-06-01 00:00:00"
assert rates_2017[1].rate == 0.0175
def test_get_rates_for_daterange_returns_correct_rates(notify_db,
notify_db_session):
set_up_rate(notify_db, datetime(2010, 6, 30, 14, 00), 0.015)
set_up_rate(notify_db, datetime(2010, 12, 1), 0.016)
set_up_rate(notify_db, datetime(2011, 8, 1), 0.0175)
start_date, end_date = get_financial_year(2011)
rates_2011 = get_rates_for_daterange(start_date, end_date, SMS_TYPE)
assert len(rates_2011) == 2
assert datetime.strftime(rates_2011[0].valid_from,
'%Y-%m-%d %H:%M:%S') == "2010-12-01 00:00:00"
assert rates_2011[0].rate == 0.016
assert datetime.strftime(rates_2011[1].valid_from,
'%Y-%m-%d %H:%M:%S') == "2011-08-01 00:00:00"
assert rates_2011[1].rate == 0.0175
def fetch_billing_totals_for_year(service_id, year):
year_start_date, year_end_date = get_financial_year(year)
print(year_start_date, year_end_date)
"""
Billing for email: only record the total number of emails.
Billing for letters: The billing units is used to fetch the correct rate for the sheet count of the letter.
Total cost is notifications_sent * rate.
Rate multiplier does not apply to email or letters.
"""
email_and_letters = (
db.session.query(
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"),
).filter(
FactBilling.service_id == service_id,
FactBilling.bst_date >= year_start_date.strftime("%Y-%m-%d"),
# This works only for timezones to the west of GMT
FactBilling.bst_date < year_end_date.strftime("%Y-%m-%d"),
FactBilling.notification_type.in_([EMAIL_TYPE, LETTER_TYPE]),
).group_by(FactBilling.rate, FactBilling.notification_type))
"""
Billing for SMS using the billing_units * rate_multiplier. Billing unit of SMS is the fragment count of a message
"""
sms = (
db.session.query(
func.sum(
FactBilling.notifications_sent).label("notifications_sent"),
func.sum(FactBilling.billable_units *
FactBilling.rate_multiplier).label("billable_units"),
FactBilling.rate,
FactBilling.notification_type,
).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"
), # This works only for timezones to the west of GMT
FactBilling.notification_type == SMS_TYPE,
).group_by(FactBilling.rate, FactBilling.notification_type))
yearly_data = email_and_letters.union_all(sms).order_by(
"notification_type", "rate").all()
return yearly_data
def get_monthly_notification_stats(service_id):
# check service_id validity
dao_fetch_service_by_id(service_id)
try:
year = int(request.args.get('year', 'NaN'))
except ValueError:
raise InvalidRequest('Year must be a number', status_code=400)
start_date, end_date = get_financial_year(year)
data = statistics.create_empty_monthly_notification_status_stats_dict(year)
stats = fetch_notification_status_for_service_by_month(start_date, end_date, service_id)
statistics.add_monthly_notification_status_stats(data, stats)
now = datetime.utcnow()
if end_date > now:
todays_deltas = fetch_notification_status_for_service_for_day(convert_utc_to_bst(now), service_id=service_id)
statistics.add_monthly_notification_status_stats(data, todays_deltas)
return jsonify(data=data)
def get_monthly_template_usage(service_id):
try:
start_date, end_date = get_financial_year(
int(request.args.get("year", "NaN")))
data = fetch_monthly_template_usage_for_service(start_date=start_date,
end_date=end_date,
service_id=service_id)
stats = list()
for i in data:
stats.append({
"template_id": str(i.template_id),
"name": i.name,
"type": i.template_type,
"month": i.month,
"year": i.year,
"count": i.count,
"is_precompiled_letter": i.is_precompiled_letter,
})
return jsonify(stats=stats), 200
except ValueError:
raise InvalidRequest("Year must be a number", status_code=400)
def test_get_financial_year():
start, end = get_financial_year(2000)
assert str(start) == "2000-04-01 05:00:00"
assert str(end) == "2001-04-01 04:59:59.999999"
def test_get_financial_year():
start, end = get_financial_year(2000)
assert str(start) == '2000-03-31 23:00:00'
assert str(end) == '2001-03-31 22:59:59.999999'
def dao_fetch_monthly_historical_usage_by_template_for_service(service_id, year):
results = dao_get_template_usage_stats_by_service(service_id, year)
stats = []
for result in results:
stat = type("", (), {})()
stat.template_id = result.template_id
stat.template_type = result.template_type
stat.name = str(result.name)
stat.month = result.month
stat.year = result.year
stat.count = result.count
stat.is_precompiled_letter = result.is_precompiled_letter
stats.append(stat)
month = get_london_month_from_utc_column(Notification.created_at)
year_func = func.date_trunc("year", Notification.created_at)
start_date = datetime.combine(date.today(), time.min)
fy_start, fy_end = get_financial_year(year)
if fy_start < datetime.now() < fy_end:
today_results = db.session.query(
Notification.template_id,
Template.is_precompiled_letter,
Template.name,
Template.template_type,
extract('month', month).label('month'),
extract('year', year_func).label('year'),
func.count().label('count')
).join(
Template, Notification.template_id == Template.id,
).filter(
Notification.created_at >= start_date,
Notification.service_id == service_id,
# we don't want to include test keys
Notification.key_type != KEY_TYPE_TEST
).group_by(
Notification.template_id,
Template.hidden,
Template.name,
Template.template_type,
month,
year_func
).order_by(
Notification.template_id
).all()
for today_result in today_results:
add_to_stats = True
for stat in stats:
if today_result.template_id == stat.template_id and today_result.month == stat.month \
and today_result.year == stat.year:
stat.count = stat.count + today_result.count
add_to_stats = False
if add_to_stats:
new_stat = type("StatsTemplateUsageByMonth", (), {})()
new_stat.template_id = today_result.template_id
new_stat.template_type = today_result.template_type
new_stat.name = today_result.name
new_stat.month = int(today_result.month)
new_stat.year = int(today_result.year)
new_stat.count = today_result.count
new_stat.is_precompiled_letter = today_result.is_precompiled_letter
stats.append(new_stat)
return stats
