def test_fetch_sms_billing_for_all_services_without_an_organisation_appears(
notify_db_session,
):
org, org_2, service, service_2, service_3, service_sms_only = set_up_usage_data(datetime(2019, 5, 1))
results = fetch_sms_billing_for_all_services(datetime(2019, 5, 1), datetime(2019, 5, 31))
assert len(results) == 2
# organisation_name, organisation_id, service_name, service_id, free_sms_fragment_limit,
# sms_rate, sms_remainder, sms_billable_units, chargeable_billable_units, 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] == (
None,
None,
service_sms_only.name,
service_sms_only.id,
10,
Decimal("0.11"),
0,
3,
3,
Decimal("0.33"),
)
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 get_usage_for_all_services():
# TODO: Add defeaults or request validation
start_date = request.args.get('start_date')
end_date = request.args.get('end_date')
start_date, end_date = validate_date_range_is_within_a_financial_year(start_date, end_date)
sms_costs = fetch_sms_billing_for_all_services(start_date, end_date)
letter_costs = fetch_letter_costs_for_all_services(start_date, end_date)
letter_breakdown = fetch_letter_line_items_for_all_services(start_date, end_date)
lb_by_service = [
(lb.service_id, "{} {} class letters at {}p".format(lb.letters_sent, lb.postage, int(lb.letter_rate * 100)))
for lb in letter_breakdown
]
combined = {}
# TODO: Add email costs?
for s in sms_costs:
entry = {
"organisation_id": str(s.organisation_id) if s.organisation_id else "",
"organisation_name": s.organisation_name or "",
"service_id": str(s.service_id),
"service_name": s.service_name,
"sms_cost": float(s.sms_cost),
"sms_fragments": s.chargeable_billable_sms,
"letter_cost": 0,
"letter_breakdown": ""
}
combined[s.service_id] = entry
for l in letter_costs:
if l.service_id in combined:
combined[l.service_id].update({'letter_cost': float(l.letter_cost)})
else:
letter_entry = {
"organisation_id": str(l.organisation_id) if l.organisation_id else "",
"organisation_name": l.organisation_name or "",
"service_id": str(l.service_id),
"service_name": l.service_name,
"sms_cost": 0,
"sms_fragments": 0,
"letter_cost": float(l.letter_cost),
"letter_breakdown": ""
}
combined[l.service_id] = letter_entry
for service_id, breakdown in lb_by_service:
combined[service_id]['letter_breakdown'] += (breakdown + '\n')
# sorting first by name == '' means that blank orgs will be sorted last.
return jsonify(sorted(combined.values(), key=lambda x: (
x['organisation_name'] == '',
x['organisation_name'],
x['service_name']
)))
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_fetch_sms_billing_for_all_services_without_an_organisation_appears(
notify_db_session):
fixtures = set_up_usage_data(datetime(2019, 5, 1))
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, service_id, free_sms_fragment_limit,
# sms_rate, sms_remainder, sms_billable_units, chargeable_billable_units, sms_cost
assert results[0] == (fixtures["org_1"].name, fixtures["org_1"].id,
fixtures["service_1_sms_and_letter"].name,
fixtures["service_1_sms_and_letter"].id, 10,
Decimal('0.11'), 8, 3, 0, Decimal('0'))
assert results[1] == (None, None,
fixtures["service_with_sms_without_org"].name,
fixtures["service_with_sms_without_org"].id, 10,
Decimal('0.11'), 0, 3, 3, Decimal('0.33'))
assert results[2] == (None, None,
fixtures["service_with_sms_within_allowance"].name,
fixtures["service_with_sms_within_allowance"].id, 10,
Decimal('0.11'), 10, 2, 0, Decimal('0.00'))
def get_billing_for_all_services():
start_date = request.args.get('start_date')
end_date = request.args.get('end_date')
start_date, end_date = validate_date_range_is_within_a_financial_year(
start_date, end_date)
sms_costs = fetch_sms_billing_for_all_services(start_date, end_date)
def present_cost(s):
return {
"service_id": str(s.service_id),
"service_name": s.service_name,
"sms_rate": float(s.sms_rate),
# number of free sms available from start of this period FY
"sms_free_rollover": s.sms_remainder,
# the total number of units(fragments) we sent out.
"billable_units": int(s.billable_units),
# the total number of adjusted units we sent out.
# fragments * international modifier
"billable_units_adjusted": float(s.billable_units_adjusted),
# number of adjusted units sent out after free allowance removed
"chargeable_units": float(s.chargeable_units),
# total cost of adjusted, non-free units at sms rate
"total_cost": float(s.total_cost),
# international/domestic unit count split
"domestic_units": int(s.domestic_units),
"international_units": int(s.international_units),
# notifications sent without being broken down to units
"notifications_sent": int(s.notifications_sent),
}
service_costs = [present_cost(c) for c in sms_costs]
return jsonify(sorted(service_costs, key=lambda x: x['service_name']))
def get_usage_for_all_services():
start_date = request.args.get("start_date")
end_date = request.args.get("end_date")
start_date, end_date = validate_date_range_is_within_a_financial_year(
start_date, end_date)
sms_costs = fetch_sms_billing_for_all_services(start_date, end_date)
letter_costs = fetch_letter_costs_for_all_services(start_date, end_date)
letter_breakdown = fetch_letter_line_items_for_all_services(
start_date, end_date)
lb_by_service = [(
lb.service_id,
"{} {} class letters at {}p".format(lb.letters_sent, lb.postage,
int(lb.letter_rate * 100)),
) for lb in letter_breakdown]
combined = {}
for s in sms_costs:
entry = {
"organisation_id":
str(s.organisation_id) if s.organisation_id else "",
"organisation_name": s.organisation_name or "",
"service_id": str(s.service_id),
"service_name": s.service_name,
"sms_cost": float(s.sms_cost),
"sms_fragments": s.chargeable_billable_sms,
"letter_cost": 0,
"letter_breakdown": "",
}
combined[s.service_id] = entry
for letter_cost in letter_costs:
if letter_cost.service_id in combined:
combined[letter_cost.service_id].update(
{"letter_cost": float(letter_cost.letter_cost)})
else:
letter_entry = {
"organisation_id":
str(letter_cost.organisation_id)
if letter_cost.organisation_id else "",
"organisation_name":
letter_cost.organisation_name or "",
"service_id":
str(letter_cost.service_id),
"service_name":
letter_cost.service_name,
"sms_cost":
0,
"sms_fragments":
0,
"letter_cost":
float(letter_cost.letter_cost),
"letter_breakdown":
"",
}
combined[letter_cost.service_id] = letter_entry
for service_id, breakdown in lb_by_service:
combined[service_id]["letter_breakdown"] += breakdown + "\n"
# sorting first by name == '' means that blank orgs will be sorted last.
return jsonify(
sorted(
combined.values(),
key=lambda x: (
x["organisation_name"] == "",
x["organisation_name"],
x["service_name"],
),
))
def get_data_for_billing_report():
start_date = request.args.get('start_date')
end_date = request.args.get('end_date')
start_date, end_date = validate_date_range_is_within_a_financial_year(start_date, end_date)
sms_costs = fetch_sms_billing_for_all_services(start_date, end_date)
letter_costs = fetch_letter_costs_for_all_services(start_date, end_date)
letter_breakdown = fetch_letter_line_items_for_all_services(start_date, end_date)
lb_by_service = [
(lb.service_id,
f"{lb.letters_sent} {postage_description(lb.postage)} letters at {format_letter_rate(lb.letter_rate)}")
for lb in letter_breakdown
]
combined = {}
for s in sms_costs:
if float(s.sms_cost) > 0:
entry = {
"organisation_id": str(s.organisation_id) if s.organisation_id else "",
"organisation_name": s.organisation_name or "",
"service_id": str(s.service_id),
"service_name": s.service_name,
"sms_cost": float(s.sms_cost),
"sms_fragments": s.chargeable_billable_sms,
"letter_cost": 0,
"letter_breakdown": ""
}
combined[s.service_id] = entry
for letter_cost in letter_costs:
if letter_cost.service_id in combined:
combined[letter_cost.service_id].update({'letter_cost': float(letter_cost.letter_cost)})
else:
letter_entry = {
"organisation_id": str(letter_cost.organisation_id) if letter_cost.organisation_id else "",
"organisation_name": letter_cost.organisation_name or "",
"service_id": str(letter_cost.service_id),
"service_name": letter_cost.service_name,
"sms_cost": 0,
"sms_fragments": 0,
"letter_cost": float(letter_cost.letter_cost),
"letter_breakdown": ""
}
combined[letter_cost.service_id] = letter_entry
for service_id, breakdown in lb_by_service:
combined[service_id]['letter_breakdown'] += (breakdown + '\n')
billing_details = fetch_billing_details_for_all_services()
for service in billing_details:
if service.service_id in combined:
combined[service.service_id].update({
'purchase_order_number': service.purchase_order_number,
'contact_names': service.billing_contact_names,
'contact_email_addresses': service.billing_contact_email_addresses,
'billing_reference': service.billing_reference
})
# sorting first by name == '' means that blank orgs will be sorted last.
result = sorted(combined.values(), key=lambda x: (
x['organisation_name'] == '',
x['organisation_name'],
x['service_name']
))
return jsonify(result)
