def test_fetch_sms_free_allowance_remainder_with_two_services(notify_db_session):
service = create_service(service_name='has free allowance')
template = create_template(service=service)
org = create_organisation(name="Org for {}".format(service.name))
dao_add_service_to_organisation(service=service, organisation_id=org.id)
create_annual_billing(service_id=service.id, free_sms_fragment_limit=10, financial_year_start=2016)
create_ft_billing(service=service, template=template,
utc_date=datetime(2016, 4, 20), notification_type='sms', billable_unit=2, rate=0.11)
create_ft_billing(service=service, template=template, utc_date=datetime(2016, 5, 20), notification_type='sms',
billable_unit=3, rate=0.11)
service_2 = create_service(service_name='used free allowance')
template_2 = create_template(service=service_2)
org_2 = create_organisation(name="Org for {}".format(service_2.name))
dao_add_service_to_organisation(service=service_2, organisation_id=org_2.id)
create_annual_billing(service_id=service_2.id, free_sms_fragment_limit=20, financial_year_start=2016)
create_ft_billing(service=service_2, template=template_2, utc_date=datetime(2016, 4, 20), notification_type='sms',
billable_unit=12, rate=0.11)
create_ft_billing(service=service_2, template=template_2, utc_date=datetime(2016, 4, 22), notification_type='sms',
billable_unit=10, rate=0.11)
create_ft_billing(service=service_2, template=template_2, utc_date=datetime(2016, 5, 20), notification_type='sms',
billable_unit=3, rate=0.11)
results = fetch_sms_free_allowance_remainder(datetime(2016, 5, 1)).all()
assert len(results) == 2
service_result = [row for row in results if row[0] == service.id]
assert service_result[0] == (service.id, 10, 2, 8)
service_2_result = [row for row in results if row[0] == service_2.id]
assert service_2_result[0] == (service_2.id, 20, 22, 0)
def test_get_free_sms_fragment_limit_future_year_not_exist(client, sample_service):
current_year = get_current_financial_year_start_year()
create_annual_billing(
sample_service.id,
free_sms_fragment_limit=9999,
financial_year_start=current_year - 1,
)
create_annual_billing(
sample_service.id,
free_sms_fragment_limit=10000,
financial_year_start=current_year + 1,
)
annual_billing = dao_get_free_sms_fragment_limit_for_year(sample_service.id, current_year + 2)
assert annual_billing is None
res_get = client.get(
"service/{}/billing/free-sms-fragment-limit?financial_year_start={}".format(sample_service.id, current_year + 2),
headers=[("Content-Type", "application/json"), create_authorization_header()],
)
json_resp = json.loads(res_get.get_data(as_text=True))
assert res_get.status_code == 200
assert json_resp["financial_year_start"] == current_year + 2
assert json_resp["free_sms_fragment_limit"] == 10000
def test_get_organisation_services_usage(admin_request, notify_db_session):
org = create_organisation(name='Organisation without live services')
service = create_service()
template = create_template(service=service)
dao_add_service_to_organisation(service=service, organisation_id=org.id)
create_annual_billing(service_id=service.id,
free_sms_fragment_limit=10,
financial_year_start=2019)
create_ft_billing(bst_date=datetime.utcnow().date(),
template=template,
billable_unit=19,
rate=0.060,
notifications_sent=19)
response = admin_request.get(
'organisation.get_organisation_services_usage',
organisation_id=org.id,
**{"year": 2019})
assert len(response) == 1
assert len(response['services']) == 1
service_usage = response['services'][0]
assert service_usage['service_id'] == str(service.id)
assert service_usage['service_name'] == service.name
assert service_usage['chargeable_billable_sms'] == 9.0
assert service_usage['emails_sent'] == 0
assert service_usage['free_sms_limit'] == 10
assert service_usage['letter_cost'] == 0
assert service_usage['sms_billable_units'] == 19
assert service_usage['sms_remainder'] == 10
assert service_usage['sms_cost'] == 0.54
def test_fetch_sms_billing_for_all_services_for_first_quarter(notify_db_session):
# This test is useful because the inner query resultset is empty.
service = create_service(service_name="a - has free allowance")
template = create_template(service=service)
org = create_organisation(name="Org for {}".format(service.name))
dao_add_service_to_organisation(service=service, organisation_id=org.id)
create_annual_billing(service_id=service.id, free_sms_fragment_limit=25000, financial_year_start=2019)
create_ft_billing(
service=service,
template=template,
utc_date=datetime(2019, 4, 20),
notification_type="sms",
billable_unit=44,
rate=0.11,
)
results = fetch_sms_billing_for_all_services(datetime(2019, 4, 2), datetime(2019, 5, 30))
assert len(results) == 1
assert results[0] == (
org.name,
org.id,
service.name,
service.id,
25000,
Decimal("0.11"),
25000,
44,
0,
Decimal("0"),
)
def test_update_free_sms_fragment_limit_data(client, sample_service):
current_year = get_current_financial_year_start_year()
create_annual_billing(sample_service.id, free_sms_fragment_limit=250000, financial_year_start=current_year - 1)
update_free_sms_fragment_limit_data(sample_service.id, 9999, current_year)
annual_billing = dao_get_free_sms_fragment_limit_for_year(sample_service.id, current_year)
assert annual_billing.free_sms_fragment_limit == 9999
def test_get_free_sms_fragment_limit_current_year_creates_new_row(client, sample_service):
current_year = get_current_financial_year_start_year()
create_annual_billing(sample_service.id, 9999, current_year - 1)
response_get = client.get(
'service/{}/billing/free-sms-fragment-limit'.format(sample_service.id),
headers=[('Content-Type', 'application/json'), create_authorization_header()])
json_resp = json.loads(response_get.get_data(as_text=True))
assert response_get.status_code == 200
assert json_resp['financial_year_start'] == get_current_financial_year_start_year()
assert json_resp['free_sms_fragment_limit'] == 9999
def test_get_free_sms_fragment_limit(client, sample_service):
create_annual_billing(service_id=sample_service.id,
free_sms_fragment_limit=11000,
financial_year_start=2021)
response_get = client.get(
'service/{}/billing/free-sms-fragment-limit'.format(sample_service.id),
headers=[('Content-Type', 'application/json'),
create_authorization_header()])
json_resp = json.loads(response_get.get_data(as_text=True))
assert response_get.status_code == 200
assert json_resp['financial_year_start'] == 2021
assert json_resp['free_sms_fragment_limit'] == 11000
def test_dao_fetch_live_services_data(sample_user):
org = create_organisation(organisation_type='other')
service = create_service(go_live_user=sample_user,
go_live_at='2014-04-20T10:00:00')
template = create_template(service=service)
service_2 = create_service(service_name='second',
go_live_at='2017-04-20T10:00:00',
go_live_user=sample_user)
service_3 = create_service(service_name='third',
go_live_at='2016-04-20T10:00:00')
# below services should be filtered out:
create_service(service_name='restricted', restricted=True)
create_service(service_name='not_active', active=False)
create_service(service_name='not_live', count_as_live=False)
template2 = create_template(service=service, template_type='email')
template_letter_1 = create_template(service=service,
template_type='letter')
template_letter_2 = create_template(service=service_2,
template_type='letter')
dao_add_service_to_organisation(service=service, organisation_id=org.id)
# two sms billing records for 1st service within current financial year:
create_ft_billing(utc_date='2019-04-20',
notification_type='sms',
template=template,
service=service)
create_ft_billing(utc_date='2019-04-21',
notification_type='sms',
template=template,
service=service)
# one sms billing record for 1st service from previous financial year, should not appear in the result:
create_ft_billing(utc_date='2018-04-20',
notification_type='sms',
template=template,
service=service)
# one email billing record for 1st service within current financial year:
create_ft_billing(utc_date='2019-04-20',
notification_type='email',
template=template2,
service=service)
# one letter billing record for 1st service within current financial year:
create_ft_billing(utc_date='2019-04-15',
notification_type='letter',
template=template_letter_1,
service=service)
# one letter billing record for 2nd service within current financial year:
create_ft_billing(utc_date='2019-04-16',
notification_type='letter',
template=template_letter_2,
service=service_2)
# 1st service: billing from 2018 and 2019
create_annual_billing(service.id, 500, 2018)
create_annual_billing(service.id, 100, 2019)
# 2nd service: billing from 2018
create_annual_billing(service_2.id, 300, 2018)
# 3rd service: billing from 2019
create_annual_billing(service_3.id, 200, 2019)
results = dao_fetch_live_services_data()
assert len(results) == 3
def test_dao_fetch_live_services_data(sample_user):
org = create_organisation(organisation_type='nhs_central')
service = create_service(go_live_user=sample_user, go_live_at='2014-04-20T10:00:00')
sms_template = create_template(service=service)
service_2 = create_service(service_name='second', go_live_at='2017-04-20T10:00:00', go_live_user=sample_user)
service_3 = create_service(service_name='third', go_live_at='2016-04-20T10:00:00')
# below services should be filtered out:
create_service(service_name='restricted', restricted=True)
create_service(service_name='not_active', active=False)
create_service(service_name='not_live', count_as_live=False)
email_template = create_template(service=service, template_type='email')
template_letter_1 = create_template(service=service, template_type='letter')
template_letter_2 = create_template(service=service_2, template_type='letter')
dao_add_service_to_organisation(service=service, organisation_id=org.id)
# two sms billing records for 1st service within current financial year:
create_ft_billing(bst_date='2019-04-20', template=sms_template)
create_ft_billing(bst_date='2019-04-21', template=sms_template)
# one sms billing record for 1st service from previous financial year, should not appear in the result:
create_ft_billing(bst_date='2018-04-20', template=sms_template)
# one email billing record for 1st service within current financial year:
create_ft_billing(bst_date='2019-04-20', template=email_template)
# one letter billing record for 1st service within current financial year:
create_ft_billing(bst_date='2019-04-15', template=template_letter_1)
# one letter billing record for 2nd service within current financial year:
create_ft_billing(bst_date='2019-04-16', template=template_letter_2)
# 1st service: billing from 2018 and 2019
create_annual_billing(service.id, 500, 2018)
create_annual_billing(service.id, 100, 2019)
# 2nd service: billing from 2018
create_annual_billing(service_2.id, 300, 2018)
# 3rd service: billing from 2019
create_annual_billing(service_3.id, 200, 2019)
results = dao_fetch_live_services_data()
assert len(results) == 3
# checks the results and that they are ordered by date:
assert results == [
{'service_id': mock.ANY, 'service_name': 'Sample service', 'organisation_name': 'test_org_1',
'organisation_type': 'nhs_central', 'consent_to_research': None, 'contact_name': 'Test User',
'contact_email': '*****@*****.**', 'contact_mobile': '+447700900986',
'live_date': datetime(2014, 4, 20, 10, 0), 'sms_volume_intent': None, 'email_volume_intent': None,
'letter_volume_intent': None, 'sms_totals': 2, 'email_totals': 1, 'letter_totals': 1,
'free_sms_fragment_limit': 100},
{'service_id': mock.ANY, 'service_name': 'third', 'organisation_name': None, 'consent_to_research': None,
'organisation_type': None, 'contact_name': None, 'contact_email': None,
'contact_mobile': None, 'live_date': datetime(2016, 4, 20, 10, 0), 'sms_volume_intent': None,
'email_volume_intent': None, 'letter_volume_intent': None,
'sms_totals': 0, 'email_totals': 0, 'letter_totals': 0,
'free_sms_fragment_limit': 200},
{'service_id': mock.ANY, 'service_name': 'second', 'organisation_name': None, 'consent_to_research': None,
'contact_name': 'Test User', 'contact_email': '*****@*****.**',
'contact_mobile': '+447700900986', 'live_date': datetime(2017, 4, 20, 10, 0), 'sms_volume_intent': None,
'organisation_type': None, 'email_volume_intent': None, 'letter_volume_intent': None,
'sms_totals': 0, 'email_totals': 0, 'letter_totals': 1,
'free_sms_fragment_limit': 300}
]
def test_get_free_sms_fragment_limit_past_year_not_exist(client, sample_service):
current_year = get_current_financial_year_start_year()
create_annual_billing(sample_service.id, 9999, current_year - 1)
create_annual_billing(sample_service.id, 10000, current_year + 1)
annual_billing = dao_get_free_sms_fragment_limit_for_year(sample_service.id, current_year - 2)
assert annual_billing is None
res_get = client.get(
'service/{}/billing/free-sms-fragment-limit?financial_year_start={}'
.format(sample_service.id, current_year - 2),
headers=[('Content-Type', 'application/json'), create_authorization_header()])
json_resp = json.loads(res_get.get_data(as_text=True))
assert res_get.status_code == 200
assert json_resp['financial_year_start'] == current_year - 1
assert json_resp['free_sms_fragment_limit'] == 9999
def test_get_organisation_services_usage_sort_active_first(
admin_request, notify_db_session):
org = create_organisation(name='Organisation without live services')
service = create_service(service_name='live service')
archived_service = create_service(service_name='archived_service')
template = create_template(service=service)
dao_add_service_to_organisation(service=service, organisation_id=org.id)
dao_add_service_to_organisation(service=archived_service,
organisation_id=org.id)
create_annual_billing(service_id=service.id,
free_sms_fragment_limit=10,
financial_year_start=2019)
create_ft_billing(bst_date=datetime.utcnow().date(),
template=template,
billable_unit=19,
rate=0.060,
notifications_sent=19)
response = admin_request.get(
'organisation.get_organisation_services_usage',
organisation_id=org.id,
**{"year": 2019})
assert len(response) == 1
assert len(response['services']) == 2
first_service = response['services'][0]
assert first_service['service_id'] == str(archived_service.id)
assert first_service['service_name'] == archived_service.name
assert first_service['active'] is True
last_service = response['services'][1]
assert last_service['service_id'] == str(service.id)
assert last_service['service_name'] == service.name
assert last_service['active'] is True
dao_archive_service(service_id=archived_service.id)
response_after_archive = admin_request.get(
'organisation.get_organisation_services_usage',
organisation_id=org.id,
**{"year": 2019})
first_service = response_after_archive['services'][0]
assert first_service['service_id'] == str(service.id)
assert first_service['service_name'] == service.name
assert first_service['active'] is True
last_service = response_after_archive['services'][1]
assert last_service['service_id'] == str(archived_service.id)
assert last_service['service_name'] == archived_service.name
assert last_service['active'] is False
def test_create_or_update_free_sms_fragment_limit_past_year_doenst_update_other_years(
admin_request, sample_service, update_existing
):
current_year = get_current_financial_year_start_year()
create_annual_billing(sample_service.id, 1, current_year)
if update_existing:
create_annual_billing(sample_service.id, 1, current_year - 1)
data = {"financial_year_start": current_year - 1, "free_sms_fragment_limit": 9999}
admin_request.post(
"billing.create_or_update_free_sms_fragment_limit",
service_id=sample_service.id,
_data=data,
_expected_status=201,
)
assert dao_get_free_sms_fragment_limit_for_year(sample_service.id, current_year - 1).free_sms_fragment_limit == 9999
assert dao_get_free_sms_fragment_limit_for_year(sample_service.id, current_year).free_sms_fragment_limit == 1
def test_create_free_sms_fragment_limit_updates_existing_year(admin_request, sample_service):
current_year = get_current_financial_year_start_year()
annual_billing = create_annual_billing(sample_service.id, 1, current_year)
admin_request.post(
'billing.create_or_update_free_sms_fragment_limit',
service_id=sample_service.id,
_data={'financial_year_start': current_year, 'free_sms_fragment_limit': 2},
_expected_status=201)
assert annual_billing.free_sms_fragment_limit == 2
def test_fetch_sms_billing_for_all_services_with_remainder(notify_db_session):
service = create_service(service_name='a - has free allowance')
template = create_template(service=service)
org = create_organisation(name="Org for {}".format(service.name))
dao_add_service_to_organisation(service=service, organisation_id=org.id)
create_annual_billing(service_id=service.id, free_sms_fragment_limit=10, financial_year_start=2019)
create_ft_billing(service=service, template=template,
utc_date=datetime(2019, 4, 20), notification_type='sms', billable_unit=2, rate=0.11)
create_ft_billing(service=service, template=template, utc_date=datetime(2019, 5, 20), notification_type='sms',
billable_unit=2, rate=0.11)
create_ft_billing(service=service, template=template, utc_date=datetime(2019, 5, 22), notification_type='sms',
billable_unit=1, rate=0.11)
service_2 = create_service(service_name='b - used free allowance')
template_2 = create_template(service=service_2)
org_2 = create_organisation(name="Org for {}".format(service_2.name))
dao_add_service_to_organisation(service=service_2, organisation_id=org_2.id)
create_annual_billing(service_id=service_2.id, free_sms_fragment_limit=10, financial_year_start=2019)
create_ft_billing(service=service_2, template=template_2, utc_date=datetime(2019, 4, 20), notification_type='sms',
billable_unit=12, rate=0.11)
create_ft_billing(service=service_2, template=template_2, utc_date=datetime(2019, 5, 20), notification_type='sms',
billable_unit=3, rate=0.11)
service_3 = create_service(service_name='c - partial allowance')
template_3 = create_template(service=service_3)
org_3 = create_organisation(name="Org for {}".format(service_3.name))
dao_add_service_to_organisation(service=service_3, organisation_id=org_3.id)
create_annual_billing(service_id=service_3.id, free_sms_fragment_limit=10, financial_year_start=2019)
create_ft_billing(service=service_3, template=template_3, utc_date=datetime(2019, 4, 20), notification_type='sms',
billable_unit=5, rate=0.11)
create_ft_billing(service=service_3, template=template_3, utc_date=datetime(2019, 5, 20), notification_type='sms',
billable_unit=7, rate=0.11)
service_4 = create_service(service_name='d - email only')
email_template = create_template(service=service_4, template_type='email')
org_4 = create_organisation(name="Org for {}".format(service_4.name))
dao_add_service_to_organisation(service=service_4, organisation_id=org_4.id)
create_annual_billing(service_id=service_4.id, free_sms_fragment_limit=10, financial_year_start=2019)
create_ft_billing(service=service_4, template=email_template, utc_date=datetime(2019, 5, 22), notifications_sent=5,
notification_type='email', billable_unit=0, rate=0)
results = fetch_sms_billing_for_all_services(datetime(2019, 5, 1), datetime(2019, 5, 31))
assert len(results) == 3
# (organisation_name, organisation_id, service_name, free_sms_fragment_limit, sms_rate,
# sms_remainder, sms_billable_units, chargeable_billable_sms, sms_cost)
assert results[0] == (org.name, org.id, service.name, service.id, 10, Decimal('0.11'), 8, 3, 0, Decimal('0'))
assert results[1] == (org_2.name, org_2.id, service_2.name, service_2.id, 10, Decimal('0.11'), 0, 3, 3,
Decimal('0.33'))
assert results[2] == (org_3.name, org_3.id, service_3.name, service_3.id, 10, Decimal('0.11'), 5, 7, 2,
Decimal('0.22'))
def test_create_free_sms_fragment_limit_current_year_updates_future_years(admin_request, sample_service):
current_year = get_current_financial_year_start_year()
future_billing = create_annual_billing(sample_service.id, 1, current_year + 1)
admin_request.post(
'billing.create_or_update_free_sms_fragment_limit',
service_id=sample_service.id,
_data={'free_sms_fragment_limit': 9999},
_expected_status=201
)
current_billing = dao_get_free_sms_fragment_limit_for_year(sample_service.id, current_year)
assert future_billing.free_sms_fragment_limit == 9999
assert current_billing.financial_year_start == current_year
assert current_billing.free_sms_fragment_limit == 9999
def test_fetch_usage_year_for_organisation_populates_ft_billing_for_today(
notify_db_session):
create_letter_rate(start_date=datetime.utcnow() - timedelta(days=1))
create_rate(start_date=datetime.utcnow() - timedelta(days=1),
value=0.65,
notification_type='sms')
new_org = create_organisation(name='New organisation')
service = create_service()
template = create_template(service=service)
dao_add_service_to_organisation(service=service,
organisation_id=new_org.id)
current_year = datetime.utcnow().year
create_annual_billing(service_id=service.id,
free_sms_fragment_limit=10,
financial_year_start=current_year)
assert FactBilling.query.count() == 0
create_notification(template=template, status='delivered')
results = fetch_usage_year_for_organisation(organisation_id=new_org.id,
year=current_year)
assert len(results) == 1
assert FactBilling.query.count() == 1
def test_dao_update_annual_billing_for_future_years(notify_db_session, sample_service):
current_year = get_current_financial_year_start_year()
limits = [1, 2, 3, 4]
create_annual_billing(sample_service.id, limits[0], current_year - 1)
create_annual_billing(sample_service.id, limits[2], current_year + 1)
create_annual_billing(sample_service.id, limits[3], current_year + 2)
dao_update_annual_billing_for_future_years(sample_service.id, 9999, current_year)
assert dao_get_free_sms_fragment_limit_for_year(sample_service.id, current_year - 1).free_sms_fragment_limit == 1
# current year is not created
assert dao_get_free_sms_fragment_limit_for_year(sample_service.id, current_year) is None
assert dao_get_free_sms_fragment_limit_for_year(sample_service.id, current_year + 1).free_sms_fragment_limit == 9999
assert dao_get_free_sms_fragment_limit_for_year(sample_service.id, current_year + 2).free_sms_fragment_limit == 9999
def test_dao_fetch_live_services_data(sample_user):
org = create_organisation(organisation_type="nhs_central")
service = create_service(go_live_user=sample_user,
go_live_at="2014-04-20T10:00:00")
template = create_template(service=service)
service_2 = create_service(
service_name="second",
go_live_at="2017-04-20T10:00:00",
go_live_user=sample_user,
)
service_3 = create_service(service_name="third",
go_live_at="2016-04-20T10:00:00")
# below services should be filtered out:
create_service(service_name="restricted", restricted=True)
create_service(service_name="not_active", active=False)
create_service(service_name="not_live", count_as_live=False)
template2 = create_template(service=service, template_type="email")
template_letter_1 = create_template(service=service,
template_type="letter")
template_letter_2 = create_template(service=service_2,
template_type="letter")
dao_add_service_to_organisation(service=service, organisation_id=org.id)
# two sms billing records for 1st service within current financial year:
create_ft_billing(
utc_date="2019-04-20",
notification_type="sms",
template=template,
service=service,
)
create_ft_billing(
utc_date="2019-04-21",
notification_type="sms",
template=template,
service=service,
)
# one sms billing record for 1st service from previous financial year, should not appear in the result:
create_ft_billing(
utc_date="2018-04-20",
notification_type="sms",
template=template,
service=service,
)
# one email billing record for 1st service within current financial year:
create_ft_billing(
utc_date="2019-04-20",
notification_type="email",
template=template2,
service=service,
)
# one letter billing record for 1st service within current financial year:
create_ft_billing(
utc_date="2019-04-15",
notification_type="letter",
template=template_letter_1,
service=service,
)
# one letter billing record for 2nd service within current financial year:
create_ft_billing(
utc_date="2019-04-16",
notification_type="letter",
template=template_letter_2,
service=service_2,
)
# 1st service: billing from 2018 and 2019
create_annual_billing(service.id, 500, 2018)
create_annual_billing(service.id, 100, 2019)
# 2nd service: billing from 2018
create_annual_billing(service_2.id, 300, 2018)
# 3rd service: billing from 2019
create_annual_billing(service_3.id, 200, 2019)
results = dao_fetch_live_services_data()
assert len(results) == 3