def create_ft_billing(utc_date,
notification_type,
template=None,
service=None,
provider='test',
rate_multiplier=1,
international=False,
rate=0,
billable_unit=1,
notifications_sent=1,
postage='none',
):
if not service:
service = create_service()
if not template:
template = create_template(service=service, template_type=notification_type)
data = FactBilling(bst_date=utc_date,
service_id=service.id,
template_id=template.id,
notification_type=notification_type,
provider=provider,
rate_multiplier=rate_multiplier,
international=international,
rate=rate,
billable_units=billable_unit,
notifications_sent=notifications_sent,
postage=postage)
db.session.add(data)
db.session.commit()
return data
def create_ft_billing(bst_date,
template,
*,
provider='test',
rate_multiplier=1,
international=False,
rate=0,
billable_unit=1,
notifications_sent=1,
postage='none'
):
data = FactBilling(bst_date=bst_date,
service_id=template.service_id,
template_id=template.id,
notification_type=template.template_type,
provider=provider,
rate_multiplier=rate_multiplier,
international=international,
rate=rate,
billable_units=billable_unit,
notifications_sent=notifications_sent,
postage=postage)
db.session.add(data)
db.session.commit()
return data
def create_billing_record(data, rate, process_day):
billing_record = FactBilling(
bst_date=process_day.date(),
template_id=data.template_id,
service_id=data.service_id,
notification_type=data.notification_type,
provider=data.sent_by,
rate_multiplier=data.rate_multiplier,
international=data.international,
billable_units=data.billable_units,
notifications_sent=data.notifications_sent,
rate=rate
)
return billing_record
def create_nightly_billing():
yesterday = datetime.utcnow() - timedelta(days=1)
non_letter_rates = [
(r.notification_type, r.valid_from, r.rate)
for r in Rate.query.order_by(desc(Rate.valid_from)).all()
]
letter_rates = [
(r.start_date, r.crown, r.sheet_count, r.rate)
for r in LetterRate.query.order_by(desc(LetterRate.start_date)).all()
]
# 3 days of data counting back from yesterday is consolidated.
for i in range(0, 3):
process_day = yesterday - timedelta(days=i)
process_day_in_aet = convert_utc_to_aet(process_day)
ds = datetime.combine(process_day, time.min)
de = datetime.combine(process_day + timedelta(days=1), time.min)
transit_data = db.session.query(
Notification.template_id,
Notification.service_id,
Notification.notification_type,
func.coalesce(
Notification.sent_by,
case([(Notification.notification_type == 'letter', 'dvla'),
(Notification.notification_type == 'sms', 'unknown'),
(Notification.notification_type == 'email', 'ses')]),
).label('sent_by'),
func.coalesce(Notification.rate_multiplier,
1).label('rate_multiplier'),
func.coalesce(Notification.international,
False).label('international'),
func.sum(Notification.billable_units).label('billable_units'),
func.count().label('notifications_sent'),
Service.crown,
).filter(
Notification.status !=
NOTIFICATION_CREATED, # at created status, provider information is not available
Notification.status != NOTIFICATION_TECHNICAL_FAILURE,
Notification.key_type != KEY_TYPE_TEST,
Notification.created_at >= ds,
Notification.created_at < de).group_by(
Notification.template_id, Notification.service_id,
Notification.notification_type, 'sent_by',
Notification.rate_multiplier, Notification.international,
Service.crown).join(Service).all()
updated_records = 0
inserted_records = 0
for data in transit_data:
update_count = FactBilling.query.filter(
FactBilling.aet_date == datetime.date(process_day_in_aet),
FactBilling.template_id == data.template_id,
FactBilling.service_id == data.service_id,
FactBilling.provider == data.
sent_by, # This could be zero - this is a bug that needs to be fixed.
FactBilling.rate_multiplier == data.rate_multiplier,
FactBilling.notification_type == data.notification_type,
FactBilling.international == data.international).update(
{
"notifications_sent": data.notifications_sent,
"billable_units": data.billable_units
},
synchronize_session=False)
if update_count == 0:
billing_record = FactBilling(
aet_date=process_day_in_aet,
template_id=data.template_id,
service_id=data.service_id,
notification_type=data.notification_type,
provider=data.sent_by,
rate_multiplier=data.rate_multiplier,
international=data.international,
billable_units=data.billable_units,
notifications_sent=data.notifications_sent,
rate=get_rate(non_letter_rates, letter_rates,
data.notification_type, process_day,
data.crown, data.rate_multiplier))
db.session.add(billing_record)
inserted_records += 1
updated_records += update_count
db.session.commit()
current_app.logger.info(
'ft_billing {} to {}: {} rows updated, {} rows inserted'.format(
ds, de, updated_records, inserted_records))