def test_get_rates_for_daterange_returns_empty_list_if_year_is_before_earliest_rate( notify_db, notify_db_session): set_up_rate(notify_db, datetime(2010, 6, 30, 14, 00), 0.015) set_up_rate(notify_db, datetime(2011, 9, 1), 0.0175) start_date, end_date = get_financial_year(2008) rates = get_rates_for_daterange(start_date, end_date, SMS_TYPE) assert rates == []
def test_get_financial_year(): start, end = get_financial_year(2000) assert str( start) == '2000-06-30 14:00:00' # 01 July 2000 00:00:00.0 in UTC assert str( end ) == '2001-06-30 13:59:59.999999' # 30 June 2001 23:59:59.999999 in UTC
def dao_fetch_monthly_historical_stats_for_service(service_id, year): month = get_sydney_month_from_utc_column(NotificationHistory.created_at) start_date, end_date = get_financial_year(year) rows = db.session.query( NotificationHistory.notification_type, NotificationHistory.status, month, func.count(NotificationHistory.id).label('count')).filter( NotificationHistory.service_id == service_id, NotificationHistory.created_at.between( start_date, end_date)).group_by(NotificationHistory.notification_type, NotificationHistory.status, month).order_by(month) months = { datetime.strftime(created_date, '%Y-%m'): { template_type: dict.fromkeys(NOTIFICATION_STATUS_TYPES, 0) for template_type in TEMPLATE_TYPES } for created_date in [datetime(year, month, 1) for month in range(7, 13)] + [datetime(year + 1, month, 1) for month in range(1, 7)] } for notification_type, status, created_date, count in rows: months[datetime.strftime(created_date, "%Y-%m")][notification_type][status] = count return months
def get_fragment_count(service_id, year=None): shared_filters = [ NotificationHistory.service_id == service_id, NotificationHistory.status.in_(NOTIFICATION_STATUS_TYPES_BILLABLE), NotificationHistory.key_type != KEY_TYPE_TEST ] if year: shared_filters.append(NotificationHistory.created_at.between( *get_financial_year(year) )) sms_count = db.session.query( func.sum(NotificationHistory.billable_units) ).filter( NotificationHistory.notification_type == SMS_TYPE, *shared_filters ) email_count = db.session.query( func.count(NotificationHistory.id) ).filter( NotificationHistory.notification_type == EMAIL_TYPE, *shared_filters ) return { 'sms_count': int(sms_count.scalar() or 0), 'email_count': email_count.scalar() or 0 }
def fetch_usage_year_for_organisation(organisation_id, year): year_start_datetime, year_end_datetime = get_financial_year(year) year_start_date = convert_utc_to_bst(year_start_datetime).date() year_end_date = convert_utc_to_bst(year_end_datetime).date() today = convert_utc_to_bst(datetime.utcnow()).date() services = dao_get_organisation_live_services(organisation_id) # if year end date is less than today, we are calculating for data in the past and have no need for deltas. if year_end_date >= today: for service in services: data = fetch_billing_data_for_day(process_day=today, service_id=service.id) for d in data: update_fact_billing(data=d, process_day=today) service_with_usage = {} # initialise results for service in services: service_with_usage[str(service.id)] = { 'service_id': service.id, 'service_name': service.name, 'free_sms_limit': 0, 'sms_remainder': 0, 'sms_billable_units': 0, 'chargeable_billable_sms': 0, 'sms_cost': 0.0, 'letter_cost': 0.0, 'emails_sent': 0, 'active': service.active } sms_usages = fetch_sms_billing_for_organisation(organisation_id, year_start_date, year_end_date) letter_usages = fetch_letter_costs_for_organisation( organisation_id, year_start_date, year_end_date) email_usages = fetch_email_usage_for_organisation(organisation_id, year_start_date, year_end_date) for usage in sms_usages: service_with_usage[str(usage.service_id)] = { 'service_id': usage.service_id, 'service_name': usage.service_name, 'free_sms_limit': usage.free_sms_fragment_limit, 'sms_remainder': usage.sms_remainder, 'sms_billable_units': usage.sms_billable_units, 'chargeable_billable_sms': usage.chargeable_billable_sms, 'sms_cost': float(usage.sms_cost), 'letter_cost': 0.0, 'emails_sent': 0, 'active': usage.active } for letter_usage in letter_usages: service_with_usage[str( letter_usage.service_id)]['letter_cost'] = float( letter_usage.letter_cost) for email_usage in email_usages: service_with_usage[str( email_usage.service_id)]['emails_sent'] = email_usage.emails_sent return service_with_usage
def fetch_monthly_billing_for_year(service_id, year): year_start_datetime, year_end_datetime = get_financial_year(year) year_start_date = convert_utc_to_bst(year_start_datetime).date() year_end_date = convert_utc_to_bst(year_end_datetime).date() today = convert_utc_to_bst(datetime.utcnow()).date() # if year end date is less than today, we are calculating for data in the past and have no need for deltas. if year_end_date >= today: yesterday = today - timedelta(days=1) for day in [yesterday, today]: data = fetch_billing_data_for_day(process_day=day, service_id=service_id) for d in data: update_fact_billing(data=d, process_day=day) email_and_letters = db.session.query( func.date_trunc('month', FactBilling.bst_date).cast(Date).label("month"), func.sum(FactBilling.notifications_sent).label("notifications_sent"), func.sum(FactBilling.notifications_sent).label("billable_units"), FactBilling.rate.label('rate'), FactBilling.notification_type.label('notification_type'), FactBilling.postage ).filter( FactBilling.service_id == service_id, FactBilling.bst_date >= year_start_date, FactBilling.bst_date <= year_end_date, FactBilling.notification_type.in_([EMAIL_TYPE, LETTER_TYPE]) ).group_by( 'month', FactBilling.rate, FactBilling.notification_type, FactBilling.postage ) sms = db.session.query( func.date_trunc('month', FactBilling.bst_date).cast(Date).label("month"), func.sum(FactBilling.notifications_sent).label("notifications_sent"), func.sum(FactBilling.billable_units * FactBilling.rate_multiplier).label("billable_units"), FactBilling.rate, FactBilling.notification_type, FactBilling.postage ).filter( FactBilling.service_id == service_id, FactBilling.bst_date >= year_start_date, FactBilling.bst_date <= year_end_date, FactBilling.notification_type == SMS_TYPE ).group_by( 'month', FactBilling.rate, FactBilling.notification_type, FactBilling.postage ) yearly_data = email_and_letters.union_all(sms).order_by( 'month', 'notification_type', 'rate' ).all() return yearly_data
def get_monthly_template_usage(service_id): try: start_date, end_date = get_financial_year(int(request.args.get('year', 'NaN'))) data = fetch_monthly_template_usage_for_service( start_date=start_date, end_date=end_date, service_id=service_id ) stats = list() for i in data: stats.append( { 'template_id': str(i.template_id), 'name': i.name, 'type': i.template_type, 'month': i.month, 'year': i.year, 'count': i.count, 'is_precompiled_letter': i.is_precompiled_letter } ) return jsonify(stats=stats), 200 except ValueError: raise InvalidRequest('Year must be a number', status_code=400)
def test_get_rates_for_daterange_early_rate(notify_db, notify_db_session): set_up_rate(notify_db, datetime(2015, 6, 1), 0.014) set_up_rate(notify_db, datetime(2016, 6, 1), 0.015) set_up_rate(notify_db, datetime(2016, 9, 1), 0.016) set_up_rate(notify_db, datetime(2017, 6, 1), 0.0175) start_date, end_date = get_financial_year(2016) rates = get_rates_for_daterange(start_date, end_date, SMS_TYPE) assert len(rates) == 3
def test_get_rates_for_daterange_in_the_future(notify_db, notify_db_session): set_up_rate(notify_db, datetime(2016, 4, 1), 0.015) set_up_rate(notify_db, datetime(2017, 6, 1), 0.0175) start_date, end_date = get_financial_year(2018) rates = get_rates_for_daterange(start_date, end_date, SMS_TYPE) assert datetime.strftime(rates[0].valid_from, '%Y-%m-%d %H:%M:%S') == "2017-06-01 00:00:00" assert rates[0].rate == 0.0175
def test_get_rates_for_daterange(notify_db, notify_db_session): set_up_rate(notify_db, datetime(2016, 5, 18), 0.016) set_up_rate(notify_db, datetime(2017, 3, 31, 23), 0.0158) start_date, end_date = get_financial_year(2017) rates = get_rates_for_daterange(start_date, end_date, SMS_TYPE) assert len(rates) == 1 assert datetime.strftime(rates[0].valid_from, '%Y-%m-%d %H:%M:%S') == "2017-03-31 23:00:00" assert rates[0].rate == 0.0158
def test_get_rates_for_daterange_edge_case(notify_db, notify_db_session): set_up_rate(notify_db, datetime(2010, 6, 30, 14, 00), 0.015) set_up_rate(notify_db, datetime(2011, 6, 30, 14, 00), 0.0175) start_date, end_date = get_financial_year(2010) rates = get_rates_for_daterange(start_date, end_date, SMS_TYPE) assert len(rates) == 1 assert datetime.strftime(rates[0].valid_from, '%Y-%m-%d %H:%M:%S') == "2010-06-30 14:00:00" assert rates[0].rate == 0.015
def get_billing_data_for_financial_year(service_id, year, notification_types=[SMS_TYPE, EMAIL_TYPE, LETTER_TYPE]): # Update totals to the latest so we include data for today now = datetime.utcnow() create_or_update_monthly_billing(service_id=service_id, billing_month=now) start_date, end_date = get_financial_year(year) results = get_yearly_billing_data_for_date_range( service_id, start_date, end_date, notification_types ) return results
def get_monthly_billing_data(service_id, year): start_date, end_date = get_financial_year(year) rates = get_rates_for_daterange(start_date, end_date, SMS_TYPE) if not rates: return [] result = [] for r, n in zip(rates, rates[1:]): result.extend(billing_data_per_month_query(r.rate, service_id, r.valid_from, n.valid_from, SMS_TYPE)) result.extend(billing_data_per_month_query(rates[-1].rate, service_id, rates[-1].valid_from, end_date, SMS_TYPE)) return [(datetime.strftime(x[0], "%B"), x[1], x[2], x[3], x[4], x[5]) for x in result]
def test_get_rates_for_daterange_multiple_result_per_year( notify_db, notify_db_session): set_up_rate(notify_db, datetime(2010, 6, 30, 14, 00), 0.015) set_up_rate(notify_db, datetime(2010, 7, 18), 0.016) set_up_rate(notify_db, datetime(2011, 6, 30, 14, 00), 0.0158) start_date, end_date = get_financial_year(2010) rates = get_rates_for_daterange(start_date, end_date, SMS_TYPE) assert len(rates) == 2 assert datetime.strftime(rates[0].valid_from, '%Y-%m-%d %H:%M:%S') == "2010-06-30 14:00:00" assert rates[0].rate == 0.015 assert datetime.strftime(rates[1].valid_from, '%Y-%m-%d %H:%M:%S') == "2010-07-18 00:00:00" assert rates[1].rate == 0.016
def test_get_rates_for_daterange_returns_correct_rates(notify_db, notify_db_session): set_up_rate(notify_db, datetime(2016, 4, 1), 0.015) set_up_rate(notify_db, datetime(2016, 9, 1), 0.016) set_up_rate(notify_db, datetime(2017, 6, 1), 0.0175) start_date, end_date = get_financial_year(2017) rates_2017 = get_rates_for_daterange(start_date, end_date, SMS_TYPE) assert len(rates_2017) == 2 assert datetime.strftime(rates_2017[0].valid_from, '%Y-%m-%d %H:%M:%S') == "2016-09-01 00:00:00" assert rates_2017[0].rate == 0.016 assert datetime.strftime(rates_2017[1].valid_from, '%Y-%m-%d %H:%M:%S') == "2017-06-01 00:00:00" assert rates_2017[1].rate == 0.0175
def test_get_rates_for_daterange_returns_correct_rates(notify_db, notify_db_session): set_up_rate(notify_db, datetime(2010, 6, 30, 14, 00), 0.015) set_up_rate(notify_db, datetime(2010, 12, 1), 0.016) set_up_rate(notify_db, datetime(2011, 8, 1), 0.0175) start_date, end_date = get_financial_year(2011) rates_2011 = get_rates_for_daterange(start_date, end_date, SMS_TYPE) assert len(rates_2011) == 2 assert datetime.strftime(rates_2011[0].valid_from, '%Y-%m-%d %H:%M:%S') == "2010-12-01 00:00:00" assert rates_2011[0].rate == 0.016 assert datetime.strftime(rates_2011[1].valid_from, '%Y-%m-%d %H:%M:%S') == "2011-08-01 00:00:00" assert rates_2011[1].rate == 0.0175
def fetch_billing_totals_for_year(service_id, year): year_start_date, year_end_date = get_financial_year(year) print(year_start_date, year_end_date) """ Billing for email: only record the total number of emails. Billing for letters: The billing units is used to fetch the correct rate for the sheet count of the letter. Total cost is notifications_sent * rate. Rate multiplier does not apply to email or letters. """ email_and_letters = ( db.session.query( func.sum( FactBilling.notifications_sent).label("notifications_sent"), func.sum(FactBilling.notifications_sent).label("billable_units"), FactBilling.rate.label("rate"), FactBilling.notification_type.label("notification_type"), ).filter( FactBilling.service_id == service_id, FactBilling.bst_date >= year_start_date.strftime("%Y-%m-%d"), # This works only for timezones to the west of GMT FactBilling.bst_date < year_end_date.strftime("%Y-%m-%d"), FactBilling.notification_type.in_([EMAIL_TYPE, LETTER_TYPE]), ).group_by(FactBilling.rate, FactBilling.notification_type)) """ Billing for SMS using the billing_units * rate_multiplier. Billing unit of SMS is the fragment count of a message """ sms = ( db.session.query( func.sum( FactBilling.notifications_sent).label("notifications_sent"), func.sum(FactBilling.billable_units * FactBilling.rate_multiplier).label("billable_units"), FactBilling.rate, FactBilling.notification_type, ).filter( FactBilling.service_id == service_id, FactBilling.bst_date >= year_start_date.strftime("%Y-%m-%d"), FactBilling.bst_date < year_end_date.strftime( "%Y-%m-%d" ), # This works only for timezones to the west of GMT FactBilling.notification_type == SMS_TYPE, ).group_by(FactBilling.rate, FactBilling.notification_type)) yearly_data = email_and_letters.union_all(sms).order_by( "notification_type", "rate").all() return yearly_data
def get_monthly_notification_stats(service_id): # check service_id validity dao_fetch_service_by_id(service_id) try: year = int(request.args.get('year', 'NaN')) except ValueError: raise InvalidRequest('Year must be a number', status_code=400) start_date, end_date = get_financial_year(year) data = statistics.create_empty_monthly_notification_status_stats_dict(year) stats = fetch_notification_status_for_service_by_month(start_date, end_date, service_id) statistics.add_monthly_notification_status_stats(data, stats) now = datetime.utcnow() if end_date > now: todays_deltas = fetch_notification_status_for_service_for_day(convert_utc_to_bst(now), service_id=service_id) statistics.add_monthly_notification_status_stats(data, todays_deltas) return jsonify(data=data)
def get_monthly_template_usage(service_id): try: start_date, end_date = get_financial_year( int(request.args.get("year", "NaN"))) data = fetch_monthly_template_usage_for_service(start_date=start_date, end_date=end_date, service_id=service_id) stats = list() for i in data: stats.append({ "template_id": str(i.template_id), "name": i.name, "type": i.template_type, "month": i.month, "year": i.year, "count": i.count, "is_precompiled_letter": i.is_precompiled_letter, }) return jsonify(stats=stats), 200 except ValueError: raise InvalidRequest("Year must be a number", status_code=400)
def test_get_financial_year(): start, end = get_financial_year(2000) assert str(start) == "2000-04-01 05:00:00" assert str(end) == "2001-04-01 04:59:59.999999"
def test_get_financial_year(): start, end = get_financial_year(2000) assert str(start) == '2000-03-31 23:00:00' assert str(end) == '2001-03-31 22:59:59.999999'
def dao_fetch_monthly_historical_usage_by_template_for_service(service_id, year): results = dao_get_template_usage_stats_by_service(service_id, year) stats = [] for result in results: stat = type("", (), {})() stat.template_id = result.template_id stat.template_type = result.template_type stat.name = str(result.name) stat.month = result.month stat.year = result.year stat.count = result.count stat.is_precompiled_letter = result.is_precompiled_letter stats.append(stat) month = get_london_month_from_utc_column(Notification.created_at) year_func = func.date_trunc("year", Notification.created_at) start_date = datetime.combine(date.today(), time.min) fy_start, fy_end = get_financial_year(year) if fy_start < datetime.now() < fy_end: today_results = db.session.query( Notification.template_id, Template.is_precompiled_letter, Template.name, Template.template_type, extract('month', month).label('month'), extract('year', year_func).label('year'), func.count().label('count') ).join( Template, Notification.template_id == Template.id, ).filter( Notification.created_at >= start_date, Notification.service_id == service_id, # we don't want to include test keys Notification.key_type != KEY_TYPE_TEST ).group_by( Notification.template_id, Template.hidden, Template.name, Template.template_type, month, year_func ).order_by( Notification.template_id ).all() for today_result in today_results: add_to_stats = True for stat in stats: if today_result.template_id == stat.template_id and today_result.month == stat.month \ and today_result.year == stat.year: stat.count = stat.count + today_result.count add_to_stats = False if add_to_stats: new_stat = type("StatsTemplateUsageByMonth", (), {})() new_stat.template_id = today_result.template_id new_stat.template_type = today_result.template_type new_stat.name = today_result.name new_stat.month = int(today_result.month) new_stat.year = int(today_result.year) new_stat.count = today_result.count new_stat.is_precompiled_letter = today_result.is_precompiled_letter stats.append(new_stat) return stats