Example #1
0
def rebuild_ft_billing_for_day(service_id, day):
    """
    Rebuild the data in ft_billing for the given service_id and date
    """
    def rebuild_ft_data(process_day, service):
        deleted_rows = delete_billing_data_for_service_for_day(process_day, service)
        current_app.logger.info('deleted {} existing billing rows for {} on {}'.format(
            deleted_rows,
            service,
            process_day
        ))
        transit_data = fetch_billing_data_for_day(process_day=process_day, service_id=service)
        # transit_data = every row that should exist
        for data in transit_data:
            # upsert existing rows
            update_fact_billing(data, process_day)
        current_app.logger.info('added/updated {} billing rows for {} on {}'.format(
            len(transit_data),
            service,
            process_day
        ))

    if service_id:
        # confirm the service exists
        dao_fetch_service_by_id(service_id)
        rebuild_ft_data(day, service_id)
    else:
        services = get_service_ids_that_need_billing_populated(
            get_local_timezone_midnight_in_utc(day),
            get_local_timezone_midnight_in_utc(day + timedelta(days=1))
        )
        for row in services:
            rebuild_ft_data(day, row.service_id)
Example #2
0
def dao_fetch_todays_stats_for_all_services(include_from_test_key=True,
                                            only_active=True):
    today = date.today()
    start_date = get_local_timezone_midnight_in_utc(today)
    end_date = get_local_timezone_midnight_in_utc(today + timedelta(days=1))

    subquery = db.session.query(
        Notification.notification_type, Notification.status,
        Notification.service_id,
        func.count(Notification.id).label('count')).filter(
            Notification.created_at >= start_date,
            Notification.created_at < end_date).group_by(
                Notification.notification_type, Notification.status,
                Notification.service_id)

    if not include_from_test_key:
        subquery = subquery.filter(Notification.key_type != KEY_TYPE_TEST)

    subquery = subquery.subquery()

    query = db.session.query(
        Service.id.label('service_id'), Service.name, Service.restricted,
        Service.research_mode, Service.active, Service.created_at,
        subquery.c.notification_type,
        subquery.c.status, subquery.c.count).outerjoin(
            subquery, subquery.c.service_id == Service.id).order_by(Service.id)

    if only_active:
        query = query.filter(Service.active)

    return query.all()
def delete_notifications_older_than_retention_by_type(notification_type,
                                                      qry_limit=10000):
    current_app.logger.info(
        'Deleting {} notifications for services with flexible data retention'.
        format(notification_type))

    flexible_data_retention = ServiceDataRetention.query.filter(
        ServiceDataRetention.notification_type == notification_type).all()
    deleted = 0
    for f in flexible_data_retention:
        days_of_retention = get_local_timezone_midnight_in_utc(
            convert_utc_to_local_timezone(
                datetime.utcnow()).date()) - timedelta(
                    days=f.days_of_retention)

        if notification_type == LETTER_TYPE:
            _delete_letters_from_s3(notification_type, f.service_id,
                                    days_of_retention, qry_limit)

        insert_update_notification_history(notification_type,
                                           days_of_retention, f.service_id)

        current_app.logger.info(
            "Deleting {} notifications for service id: {}".format(
                notification_type, f.service_id))
        deleted += _delete_notifications(notification_type, days_of_retention,
                                         f.service_id, qry_limit)

    current_app.logger.info(
        'Deleting {} notifications for services without flexible data retention'
        .format(notification_type))

    seven_days_ago = get_local_timezone_midnight_in_utc(
        convert_utc_to_local_timezone(
            datetime.utcnow()).date()) - timedelta(days=7)
    services_with_data_retention = [
        x.service_id for x in flexible_data_retention
    ]
    service_ids_to_purge = db.session.query(Service.id).filter(
        Service.id.notin_(services_with_data_retention)).all()

    for service_id in service_ids_to_purge:
        if notification_type == LETTER_TYPE:
            _delete_letters_from_s3(notification_type, service_id,
                                    seven_days_ago, qry_limit)
        insert_update_notification_history(notification_type, seven_days_ago,
                                           service_id)
        deleted += _delete_notifications(notification_type, seven_days_ago,
                                         service_id, qry_limit)

    current_app.logger.info(
        'Finished deleting {} notifications'.format(notification_type))

    return deleted
Example #4
0
def fetch_notification_status_for_service_for_day(bst_day, service_id):
    return db.session.query(
        # return current month as a datetime so the data has the same shape as the ft_notification_status query
        literal(bst_day.replace(day=1), type_=DateTime).label('month'),
        Notification.notification_type,
        Notification.status.label('notification_status'),
        func.count().label('count')).filter(
            Notification.created_at >=
            get_local_timezone_midnight_in_utc(bst_day),
            Notification.created_at <
            get_local_timezone_midnight_in_utc(bst_day + timedelta(days=1)),
            Notification.service_id == service_id,
            Notification.key_type != KEY_TYPE_TEST).group_by(
                Notification.notification_type, Notification.status).all()
Example #5
0
def validate_date_range_is_within_a_financial_year(start_date, end_date):
    try:
        start_date = datetime.strptime(start_date, "%Y-%m-%d").date()
        end_date = datetime.strptime(end_date, "%Y-%m-%d").date()
    except ValueError:
        raise InvalidRequest(message="Input must be a date in the format: YYYY-MM-DD", status_code=400)
    if end_date < start_date:
        raise InvalidRequest(message="Start date must be before end date", status_code=400)

    start_fy = get_financial_year_for_datetime(get_local_timezone_midnight_in_utc(start_date))
    end_fy = get_financial_year_for_datetime(get_local_timezone_midnight_in_utc(end_date))

    if start_fy != end_fy:
        raise InvalidRequest(message="Date must be in a single financial year.", status_code=400)

    return start_date, end_date
Example #6
0
def send_total_sent_notifications_to_performance_platform(bst_date):
    count_dict = total_sent_notifications.get_total_sent_notifications_for_day(bst_date)
    start_time = get_local_timezone_midnight_in_utc(bst_date).strftime("%Y-%m-%d")

    email_sent_count = count_dict['email']
    sms_sent_count = count_dict['sms']
    letter_sent_count = count_dict['letter']

    current_app.logger.info(
        "Attempting to update Performance Platform for {} with {} emails, {} text messages and {} letters"
        .format(bst_date, email_sent_count, sms_sent_count, letter_sent_count)
    )

    total_sent_notifications.send_total_notifications_sent_for_day_stats(
        start_time,
        'sms',
        sms_sent_count
    )

    total_sent_notifications.send_total_notifications_sent_for_day_stats(
        start_time,
        'email',
        email_sent_count
    )

    total_sent_notifications.send_total_notifications_sent_for_day_stats(
        start_time,
        'letter',
        letter_sent_count
    )
def fetch_notification_status_for_service_for_today_and_7_previous_days(service_id, by_template=False, limit_days=7):
    start_date = midnight_n_days_ago(limit_days)
    now = datetime.utcnow()
    stats_for_7_days = db.session.query(
        FactNotificationStatus.notification_type.label('notification_type'),
        FactNotificationStatus.notification_status.label('status'),
        *([FactNotificationStatus.template_id.label('template_id')] if by_template else []),
        FactNotificationStatus.notification_count.label('count')
    ).filter(
        FactNotificationStatus.service_id == service_id,
        FactNotificationStatus.bst_date >= start_date,
        FactNotificationStatus.key_type != KEY_TYPE_TEST
    )

    stats_for_today = db.session.query(
        Notification.notification_type.cast(db.Text),
        Notification.status,
        *([Notification.template_id] if by_template else []),
        func.count().label('count')
    ).filter(
        Notification.created_at >= get_local_timezone_midnight_in_utc(now),
        Notification.service_id == service_id,
        Notification.key_type != KEY_TYPE_TEST
    ).group_by(
        Notification.notification_type,
        *([Notification.template_id] if by_template else []),
        Notification.status
    )

    all_stats_table = stats_for_7_days.union_all(stats_for_today).subquery()

    query = db.session.query(
        *([
            Template.name.label("template_name"),
            Template.is_precompiled_letter,
            all_stats_table.c.template_id
        ] if by_template else []),
        all_stats_table.c.notification_type,
        all_stats_table.c.status,
        func.cast(func.sum(all_stats_table.c.count), Integer).label('count'),
    )

    if by_template:
        query = query.filter(all_stats_table.c.template_id == Template.id)

    return query.group_by(
        *([Template.name, Template.is_precompiled_letter, all_stats_table.c.template_id] if by_template else []),
        all_stats_table.c.notification_type,
        all_stats_table.c.status,
    ).all()
def fetch_notification_status_totals_for_all_services(start_date, end_date):
    stats = db.session.query(
        FactNotificationStatus.notification_type.label('notification_type'),
        FactNotificationStatus.notification_status.label('status'),
        FactNotificationStatus.key_type.label('key_type'),
        func.sum(FactNotificationStatus.notification_count).label('count')
    ).filter(
        FactNotificationStatus.bst_date >= start_date,
        FactNotificationStatus.bst_date <= end_date
    ).group_by(
        FactNotificationStatus.notification_type,
        FactNotificationStatus.notification_status,
        FactNotificationStatus.key_type,
    )
    today = get_local_timezone_midnight_in_utc(datetime.utcnow())
    if start_date <= datetime.utcnow().date() <= end_date:
        stats_for_today = db.session.query(
            Notification.notification_type.cast(db.Text).label('notification_type'),
            Notification.status,
            Notification.key_type,
            func.count().label('count')
        ).filter(
            Notification.created_at >= today
        ).group_by(
            Notification.notification_type.cast(db.Text),
            Notification.status,
            Notification.key_type,
        )
        all_stats_table = stats.union_all(stats_for_today).subquery()
        query = db.session.query(
            all_stats_table.c.notification_type,
            all_stats_table.c.status,
            all_stats_table.c.key_type,
            func.cast(func.sum(all_stats_table.c.count), Integer).label('count'),
        ).group_by(
            all_stats_table.c.notification_type,
            all_stats_table.c.status,
            all_stats_table.c.key_type,
        ).order_by(
            all_stats_table.c.notification_type
        )
    else:
        query = stats.order_by(
            FactNotificationStatus.notification_type
        )
    return query.all()
Example #9
0
def get_rate(non_letter_rates,
             letter_rates,
             notification_type,
             date,
             crown=None,
             letter_page_count=None,
             post_class='second'):
    start_of_day = get_local_timezone_midnight_in_utc(date)

    if notification_type == LETTER_TYPE:
        if letter_page_count == 0:
            return 0
        return next(r.rate for r in letter_rates if (
            start_of_day >= r.start_date and crown == r.crown and
            letter_page_count == r.sheet_count and post_class == r.post_class))
    elif notification_type == SMS_TYPE:
        return next(r.rate for r in non_letter_rates
                    if (notification_type == r.notification_type
                        and start_of_day >= r.valid_from))
    else:
        return 0
Example #10
0
def backfill_processing_time(start_date, end_date):
    """
    Send historical processing time to Performance Platform.
    """

    delta = end_date - start_date

    print('Sending notification processing-time data for all days between {} and {}'.format(start_date, end_date))

    for i in range(delta.days + 1):
        # because the tz conversion funcs talk about midnight, and the midnight before last,
        # we want to pretend we're running this from the next morning, so add one.
        process_date = start_date + timedelta(days=i + 1)

        process_start_date = get_midnight_for_day_before(process_date)
        process_end_date = get_local_timezone_midnight_in_utc(process_date)

        print('Sending notification processing-time for {} - {}'.format(
            process_start_date.isoformat(),
            process_end_date.isoformat()
        ))
        send_processing_time_for_start_and_end(process_start_date, process_end_date)
Example #11
0
def fetch_monthly_template_usage_for_service(start_date, end_date, service_id):
    # services_dao.replaces dao_fetch_monthly_historical_usage_by_template_for_service
    stats = db.session.query(
        FactNotificationStatus.template_id.label('template_id'),
        Template.name.label('name'),
        Template.template_type.label('template_type'),
        Template.is_precompiled_letter.label('is_precompiled_letter'),
        extract('month', FactNotificationStatus.bst_date).label('month'),
        extract('year', FactNotificationStatus.bst_date).label('year'),
        func.sum(FactNotificationStatus.notification_count).label('count')
    ).join(Template, FactNotificationStatus.template_id == Template.id).filter(
        FactNotificationStatus.service_id == service_id,
        FactNotificationStatus.bst_date >= start_date.strftime("%Y-%m-%d"),
        # This works only for timezones to the west of GMT
        FactNotificationStatus.bst_date < end_date.strftime("%Y-%m-%d"),
        FactNotificationStatus.key_type != KEY_TYPE_TEST,
        FactNotificationStatus.notification_status != NOTIFICATION_CANCELLED,
    ).group_by(
        FactNotificationStatus.template_id,
        Template.name,
        Template.template_type,
        Template.is_precompiled_letter,
        extract('month', FactNotificationStatus.bst_date).label('month'),
        extract('year', FactNotificationStatus.bst_date).label('year'),
    ).order_by(extract('year', FactNotificationStatus.bst_date),
               extract('month', FactNotificationStatus.bst_date),
               Template.name)

    if start_date <= datetime.utcnow() <= end_date:
        today = get_local_timezone_midnight_in_utc(datetime.utcnow())
        month = get_local_timezone_month_from_utc_column(
            Notification.created_at)

        stats_for_today = db.session.query(
            Notification.template_id.label('template_id'),
            Template.name.label('name'),
            Template.template_type.label('template_type'),
            Template.is_precompiled_letter.label('is_precompiled_letter'),
            extract('month', month).label('month'),
            extract('year', month).label('year'),
            func.count().label('count')).join(
                Template,
                Notification.template_id == Template.id,
            ).filter(Notification.created_at >= today,
                     Notification.service_id == service_id,
                     Notification.key_type != KEY_TYPE_TEST,
                     Notification.status != NOTIFICATION_CANCELLED).group_by(
                         Notification.template_id, Template.hidden,
                         Template.name, Template.template_type, month)

        all_stats_table = stats.union_all(stats_for_today).subquery()
        query = db.session.query(
            all_stats_table.c.template_id,
            all_stats_table.c.name,
            all_stats_table.c.is_precompiled_letter,
            all_stats_table.c.template_type,
            func.cast(all_stats_table.c.month, Integer).label('month'),
            func.cast(all_stats_table.c.year, Integer).label('year'),
            func.cast(func.sum(all_stats_table.c.count),
                      Integer).label('count'),
        ).group_by(
            all_stats_table.c.template_id,
            all_stats_table.c.name,
            all_stats_table.c.is_precompiled_letter,
            all_stats_table.c.template_type,
            all_stats_table.c.month,
            all_stats_table.c.year,
        ).order_by(all_stats_table.c.year, all_stats_table.c.month,
                   all_stats_table.c.name)
    else:
        query = stats
    return query.all()
Example #12
0
def fetch_stats_for_all_services_by_date_range(start_date,
                                               end_date,
                                               include_from_test_key=True):
    stats = db.session.query(
        FactNotificationStatus.service_id.label('service_id'),
        Service.name.label('name'), Service.restricted.label('restricted'),
        Service.research_mode.label('research_mode'),
        Service.active.label('active'), Service.created_at.label('created_at'),
        FactNotificationStatus.notification_type.label('notification_type'),
        FactNotificationStatus.notification_status.label('status'),
        func.sum(
            FactNotificationStatus.notification_count).label('count')).filter(
                FactNotificationStatus.bst_date >= start_date,
                FactNotificationStatus.bst_date <= end_date,
                FactNotificationStatus.service_id == Service.id,
            ).group_by(
                FactNotificationStatus.service_id.label('service_id'),
                Service.name,
                Service.restricted,
                Service.research_mode,
                Service.active,
                Service.created_at,
                FactNotificationStatus.notification_type,
                FactNotificationStatus.notification_status,
            ).order_by(FactNotificationStatus.service_id,
                       FactNotificationStatus.notification_type)
    if not include_from_test_key:
        stats = stats.filter(FactNotificationStatus.key_type != KEY_TYPE_TEST)

    if start_date <= datetime.utcnow().date() <= end_date:
        today = get_local_timezone_midnight_in_utc(datetime.utcnow())
        subquery = db.session.query(
            Notification.notification_type.cast(
                db.Text).label('notification_type'),
            Notification.status.label('status'),
            Notification.service_id.label('service_id'),
            func.count(Notification.id).label('count')).filter(
                Notification.created_at >= today).group_by(
                    Notification.notification_type, Notification.status,
                    Notification.service_id)
        if not include_from_test_key:
            subquery = subquery.filter(Notification.key_type != KEY_TYPE_TEST)
        subquery = subquery.subquery()

        stats_for_today = db.session.query(
            Service.id.label('service_id'), Service.name.label('name'),
            Service.restricted.label('restricted'),
            Service.research_mode.label('research_mode'),
            Service.active.label('active'),
            Service.created_at.label('created_at'),
            subquery.c.notification_type.label('notification_type'),
            subquery.c.status.label('status'),
            subquery.c.count.label('count')).outerjoin(
                subquery, subquery.c.service_id == Service.id)

        all_stats_table = stats.union_all(stats_for_today).subquery()
        query = db.session.query(
            all_stats_table.c.service_id,
            all_stats_table.c.name,
            all_stats_table.c.restricted,
            all_stats_table.c.research_mode,
            all_stats_table.c.active,
            all_stats_table.c.created_at,
            all_stats_table.c.notification_type,
            all_stats_table.c.status,
            func.cast(func.sum(all_stats_table.c.count),
                      Integer).label('count'),
        ).group_by(
            all_stats_table.c.service_id,
            all_stats_table.c.name,
            all_stats_table.c.restricted,
            all_stats_table.c.research_mode,
            all_stats_table.c.active,
            all_stats_table.c.created_at,
            all_stats_table.c.notification_type,
            all_stats_table.c.status,
        ).order_by(all_stats_table.c.name, all_stats_table.c.notification_type,
                   all_stats_table.c.status)
    else:
        query = stats
    return query.all()
Example #13
0
def test_get_local_timezone_midnight_in_utc_returns_expected_date(
        date, expected_date):
    assert get_local_timezone_midnight_in_utc(date) == expected_date
def send_processing_time_to_performance_platform(bst_date):
    start_time = get_local_timezone_midnight_in_utc(bst_date)
    end_time = get_local_timezone_midnight_in_utc(bst_date + timedelta(days=1))

    send_processing_time_for_start_and_end(start_time, end_time)
Example #15
0
def fetch_count_of_complaints(start_date, end_date):
    start_date = get_local_timezone_midnight_in_utc(start_date)
    end_date = get_local_timezone_midnight_in_utc(end_date + timedelta(days=1))

    return Complaint.query.filter(Complaint.created_at >= start_date, Complaint.created_at < end_date).count()