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