def fetch_notification_status_for_day(process_day, service_id=None):
start_date = convert_bst_to_utc(datetime.combine(process_day, time.min))
end_date = convert_bst_to_utc(
datetime.combine(process_day + timedelta(days=1), time.min))
# use notification_history if process day is older than 7 days
# this is useful if we need to rebuild the ft_billing table for a date older than 7 days ago.
current_app.logger.info("Fetch ft_notification_status for {} to {}".format(
start_date, end_date))
table = Notification
if start_date < datetime.utcnow() - timedelta(days=7):
table = NotificationHistory
transit_data = db.session.query(
table.template_id, table.service_id,
func.coalesce(table.job_id,
'00000000-0000-0000-0000-000000000000').label('job_id'),
table.notification_type, table.key_type, table.status,
func.count().label('notification_count')).filter(
table.created_at >= start_date,
table.created_at < end_date).group_by(table.template_id,
table.service_id, 'job_id',
table.notification_type,
table.key_type, table.status)
if service_id:
transit_data = transit_data.filter(table.service_id == service_id)
return transit_data.all()
def get_months_for_financial_year(year):
return [
convert_bst_to_utc(month) for month in (
get_months_for_year(4, 13, year) +
get_months_for_year(1, 4, year + 1)
)
if convert_bst_to_utc(month) < datetime.now()
]
def get_month_start_and_end_date_in_utc(month_year):
"""
This function return the start and date of the month_year as UTC,
:param month_year: the datetime to calculate the start and end date for that month
:return: start_date, end_date, month
"""
import calendar
_, num_days = calendar.monthrange(month_year.year, month_year.month)
first_day = datetime(month_year.year, month_year.month, 1, 0, 0, 0)
last_day = datetime(month_year.year, month_year.month, num_days, 23, 59, 59, 99999)
return convert_bst_to_utc(first_day), convert_bst_to_utc(last_day)
def fetch_billing_data_for_day(process_day, service_id=None):
start_date = convert_bst_to_utc(datetime.combine(process_day, time.min))
end_date = convert_bst_to_utc(datetime.combine(process_day + timedelta(days=1), time.min))
# use notification_history if process day is older than 7 days
# this is useful if we need to rebuild the ft_billing table for a date older than 7 days ago.
current_app.logger.info("Populate ft_billing for {} to {}".format(start_date, end_date))
table = Notification
if start_date < datetime.utcnow() - timedelta(days=7):
table = NotificationHistory
transit_data = db.session.query(
table.template_id,
table.service_id,
table.notification_type,
func.coalesce(table.sent_by,
case(
[
(table.notification_type == 'letter', 'dvla'),
(table.notification_type == 'sms', 'unknown'),
(table.notification_type == 'email', 'ses')
]),
).label('sent_by'),
func.coalesce(table.rate_multiplier, 1).cast(Integer).label('rate_multiplier'),
func.coalesce(table.international, False).label('international'),
case(
[
(table.notification_type == 'letter', table.billable_units),
]
).label('letter_page_count'),
func.sum(table.billable_units).label('billable_units'),
func.count().label('notifications_sent'),
Service.crown,
).filter(
table.status.in_(NOTIFICATION_STATUS_TYPES_BILLABLE),
table.key_type != KEY_TYPE_TEST,
table.created_at >= start_date,
table.created_at < end_date
).group_by(
table.template_id,
table.service_id,
table.notification_type,
'sent_by',
'letter_page_count',
table.rate_multiplier,
table.international,
Service.crown
).join(
Service
)
if service_id:
transit_data = transit_data.filter(table.service_id == service_id)
return transit_data.all()
def format_mmg_datetime(date):
"""
We expect datetimes in format 2017-05-21+11%3A56%3A11 - ie, spaces replaced with pluses, and URI encoded
(the same as UTC)
"""
orig_date = format_mmg_message(date)
parsed_datetime = iso8601.parse_date(orig_date).replace(tzinfo=None)
return convert_bst_to_utc(parsed_datetime)
def test_convert_bst_to_utc():
bst = "2017-05-12 13:15"
bst_datetime = datetime.strptime(bst, "%Y-%m-%d %H:%M")
utc = convert_bst_to_utc(bst_datetime)
assert utc == datetime(2017, 5, 12, 12, 15)
def persist_scheduled_notification(notification_id, scheduled_for):
scheduled_datetime = convert_bst_to_utc(
datetime.strptime(scheduled_for, "%Y-%m-%d %H:%M"))
scheduled_notification = ScheduledNotification(
notification_id=notification_id, scheduled_for=scheduled_datetime)
dao_created_scheduled_notification(scheduled_notification)