def dao_fetch_trial_services_data():
year_start_date, year_end_date = get_current_financial_year()
this_year_ft_billing = FactBilling.query.filter(
FactBilling.aet_date >= year_start_date,
FactBilling.aet_date <= year_end_date,
).subquery()
data = db.session.query(
Service.id,
Organisation.name.label("organisation_name"),
Service.organisation_type,
Service.name.label("service_name"),
Service.count_as_live,
case([(this_year_ft_billing.c.notification_type == 'email',
func.sum(this_year_ft_billing.c.notifications_sent))],
else_=0).label("email_totals"),
case([(this_year_ft_billing.c.notification_type
== 'sms', func.sum(this_year_ft_billing.c.notifications_sent))],
else_=0).label("sms_totals"),
case([(this_year_ft_billing.c.notification_type == 'letter',
func.sum(this_year_ft_billing.c.notifications_sent))],
else_=0).label("letter_totals"),
).outerjoin(Service.organisation).outerjoin(
this_year_ft_billing,
Service.id == this_year_ft_billing.c.service_id).filter(
Service.count_as_live.is_(True),
Service.active.is_(True),
Service.restricted.is_(True),
).group_by(Service.id, Organisation.name, Service.organisation_type,
Service.name, Service.count_as_live,
this_year_ft_billing.c.notification_type).order_by(
asc(Service.name)).all()
results = []
for row in data:
is_service_in_list = None
i = 0
while i < len(results):
if results[i]["service_id"] == row.id:
is_service_in_list = i
break
else:
i += 1
if is_service_in_list is not None:
results[is_service_in_list]["email_totals"] += row.email_totals
results[is_service_in_list]["sms_totals"] += row.sms_totals
results[is_service_in_list]["letter_totals"] += row.letter_totals
else:
results.append({
"service_id": row.id,
"service_name": row.service_name,
"organisation_name": row.organisation_name,
"organisation_type": row.organisation_type,
"sms_totals": row.sms_totals,
"email_totals": row.email_totals,
"letter_totals": row.letter_totals,
})
return results
def dao_fetch_live_services_data():
year_start_date, year_end_date = get_current_financial_year()
most_recent_annual_billing = db.session.query(
AnnualBilling.service_id,
func.max(AnnualBilling.financial_year_start).label('year')
).group_by(
AnnualBilling.service_id
).subquery()
this_year_ft_billing = FactBilling.query.filter(
FactBilling.bst_date >= year_start_date,
FactBilling.bst_date <= year_end_date,
).subquery()
data = db.session.query(
Service.id.label('service_id'),
Service.name.label("service_name"),
Organisation.name.label("organisation_name"),
Organisation.organisation_type.label('organisation_type'),
Service.consent_to_research.label('consent_to_research'),
User.name.label('contact_name'),
User.email_address.label('contact_email'),
User.mobile_number.label('contact_mobile'),
Service.go_live_at.label("live_date"),
Service.volume_sms.label('sms_volume_intent'),
Service.volume_email.label('email_volume_intent'),
Service.volume_letter.label('letter_volume_intent'),
case([
(this_year_ft_billing.c.notification_type == 'email', func.sum(this_year_ft_billing.c.notifications_sent))
], else_=0).label("email_totals"),
case([
(this_year_ft_billing.c.notification_type == 'sms', func.sum(this_year_ft_billing.c.notifications_sent))
], else_=0).label("sms_totals"),
case([
(this_year_ft_billing.c.notification_type == 'letter', func.sum(this_year_ft_billing.c.notifications_sent))
], else_=0).label("letter_totals"),
AnnualBilling.free_sms_fragment_limit,
).join(
Service.annual_billing
).join(
most_recent_annual_billing,
and_(
Service.id == most_recent_annual_billing.c.service_id,
AnnualBilling.financial_year_start == most_recent_annual_billing.c.year
)
).outerjoin(
Service.organisation
).outerjoin(
this_year_ft_billing, Service.id == this_year_ft_billing.c.service_id
).outerjoin(
User, Service.go_live_user_id == User.id
).filter(
Service.count_as_live.is_(True),
Service.active.is_(True),
Service.restricted.is_(False),
).group_by(
Service.id,
Organisation.name,
Organisation.organisation_type,
Service.name,
Service.consent_to_research,
Service.count_as_live,
Service.go_live_user_id,
User.name,
User.email_address,
User.mobile_number,
Service.go_live_at,
Service.volume_sms,
Service.volume_email,
Service.volume_letter,
this_year_ft_billing.c.notification_type,
AnnualBilling.free_sms_fragment_limit,
).order_by(
asc(Service.go_live_at)
).all()
results = []
for row in data:
existing_service = next((x for x in results if x['service_id'] == row.service_id), None)
if existing_service is not None:
existing_service["email_totals"] += row.email_totals
existing_service["sms_totals"] += row.sms_totals
existing_service["letter_totals"] += row.letter_totals
else:
results.append(row._asdict())
return results
def dao_fetch_live_services_data():
year_start_date, year_end_date = get_current_financial_year()
this_year_ft_billing = FactBilling.query.filter(
FactBilling.aet_date >= year_start_date,
FactBilling.aet_date <= year_end_date,
).subquery()
data = db.session.query(
Service.id,
Organisation.name.label("organisation_name"),
Service.organisation_type,
Service.name.label("service_name"),
Service.go_live_user_id,
Service.count_as_live,
User.name.label('user_name'),
User.email_address,
User.mobile_number,
Service.go_live_at.label("live_date"),
case([(this_year_ft_billing.c.notification_type == 'email',
func.sum(this_year_ft_billing.c.notifications_sent))],
else_=0).label("email_totals"),
case([(this_year_ft_billing.c.notification_type
== 'sms', func.sum(this_year_ft_billing.c.notifications_sent))],
else_=0).label("sms_totals"),
case([(this_year_ft_billing.c.notification_type == 'letter',
func.sum(this_year_ft_billing.c.notifications_sent))],
else_=0).label("letter_totals"),
).outerjoin(Service.organisation).outerjoin(
this_year_ft_billing,
Service.id == this_year_ft_billing.c.service_id).outerjoin(
User, Service.go_live_user_id == User.id).filter(
Service.count_as_live.is_(True),
Service.active.is_(True),
Service.restricted.is_(False),
).group_by(Service.id, Organisation.name,
Service.organisation_type, Service.name,
Service.count_as_live, Service.go_live_user_id,
User.name, User.email_address, User.mobile_number,
Service.go_live_at,
this_year_ft_billing.c.notification_type).order_by(
asc(Service.go_live_at)).all()
results = []
for row in data:
is_service_in_list = None
i = 0
while i < len(results):
if results[i]["service_id"] == row.id:
is_service_in_list = i
break
else:
i += 1
if is_service_in_list is not None:
results[is_service_in_list]["email_totals"] += row.email_totals
results[is_service_in_list]["sms_totals"] += row.sms_totals
results[is_service_in_list]["letter_totals"] += row.letter_totals
else:
results.append({
"service_id": row.id,
"service_name": row.service_name,
"organisation_name": row.organisation_name,
"organisation_type": row.organisation_type,
"consent_to_research":
None, # TODO real value when col exists in DB
"contact_name": row.user_name,
"contact_email": row.email_address,
"contact_mobile": row.mobile_number,
"live_date": row.live_date,
"sms_volume_intent":
None, # TODO real value when col exists in DB
"email_volume_intent":
None, # TODO real value when col exists in DB
"letter_volume_intent":
None, # TODO real value when col exists in DB
"sms_totals": row.sms_totals,
"email_totals": row.email_totals,
"letter_totals": row.letter_totals,
})
return results