reporting.py

from sqlbuilder.smartsql.expressions import func
from sqlbuilder.smartsql import Q

from core.models import AppLicenseGroup, AppLicenseGroupUser
from core.utils.db import fetch_many, fetch_one, norm_sum
from itrain.models import ModuleResponse, Module, ModuleAssignee


def fetch_average_to_complete(company_id, start_date, end_date):
    company_filter = Module.s.company_id == company_id
    interval_filter = ModuleResponse.s.completed_at.between(func.date(start_date), func.date(end_date))
    where = company_filter & interval_filter

    avg_duration_func = func.avg(func.date_part('epoch', ModuleResponse.s.completed_at - ModuleResponse.s.started_at))

    average_time_q = Q()\
        .tables((ModuleResponse.s & Module.s).on(ModuleResponse.s.module == Module.s.pk))\
        .fields(func.ceil(func.coalesce(avg_duration_func, 0)).as_('average_time_to_complete'))\
        .where(where)

    return fetch_one(average_time_q)['average_time_to_complete']


def fetch_tot_users_assigned(company_id, start_date, end_date):
    company_filter = Module.s.company_id == company_id
    published_filter = Module.s.status == 'published'
    interval_filter = (func.date(start_date) <= Module.s.start_datetime) & (
        Module.s.start_datetime <= func.date(end_date))

    where = company_filter & published_filter & interval_filter

    main_join = (ModuleAssignee.s & Module.s).on(ModuleAssignee.s.module == Module.s.pk)

    count_func = func.coalesce(func.count(1), 0).as_('tot')

    group_count_q = Q() \
        .tables(
          (AppLicenseGroupUser.s &
            (AppLicenseGroup.s & main_join).on(ModuleAssignee.s.app_license_group == AppLicenseGroup.s.pk)
          ).on(AppLicenseGroupUser.s.app_license_group == AppLicenseGroup.s.pk)
        )\
        .fields(count_func) \
        .where(where)

    single_count_q = Q() \
        .tables(main_join) \
        .fields(count_func) \
        .where(where & ModuleAssignee.s.user_id.is_not(None))

    union_table = (group_count_q.as_set(all=True) | single_count_q).as_table('count_union')

    total_count_q = Q() \
        .tables(union_table) \
        .fields(norm_sum(union_table.tot).as_('tot'))

    return fetch_one(total_count_q)['tot']


def fetch_pass_status_counts(company_id, start_date, end_date):
    company_filter = Module.s.company_id == company_id
    period_cond = ModuleResponse.s.completed_at.between(func.date(start_date), func.date(end_date))
    normalised_score = ModuleResponse.s.score * 100

    passed_cond = normalised_score >= Module.s.pass_rate
    failed_cond = normalised_score < Module.s.pass_rate

    tot_q = Q()\
        .tables(
            (ModuleResponse.s & Module.s).on(ModuleResponse.s.module == Module.s.pk)
        )\
        .fields(func.coalesce(func.count(1), 0).as_('tot_passed'))\
        .where(company_filter)

    tot_passed_q = tot_q.where(passed_cond)
    tot_failed_q = tot_q.where(failed_cond)

    all_counts_q = Q()\
        .tables(ModuleResponse.s)\
        .fields(
            tot_passed_q.where(period_cond).as_('tot_passed_in_period'),
            tot_passed_q.as_('tot_passed_overall'),
            tot_failed_q.where(period_cond).as_('tot_failed_in_period'),
            tot_passed_q.as_('tot_failed_overall'),
        )

    return fetch_one(all_counts_q)


def fetch_pass_status_daily_breakdown(company_id, start_date, end_date):
    company_filter = Module.s.company_id == company_id
    period_cond = ModuleResponse.s.completed_at.between(func.date(start_date), func.date(end_date))
    passed_cond = ModuleResponse.s.score * 100 >= Module.s.pass_rate
    failed_cond = ModuleResponse.s.score * 100 < Module.s.pass_rate

    breakdown_q = Q() \
        .tables((ModuleResponse.s & Module.s).on(ModuleResponse.s.module == Module.s.pk)) \
        .fields(
            func.date(ModuleResponse.s.completed_at).as_('day'),
            func.count('*').as_('tot')
        ) \
        .where(company_filter & period_cond) \
        .group_by(func.date(ModuleResponse.s.completed_at)) \
        .order_by(func.date(ModuleResponse.s.completed_at))

    passed_breakdown_q = breakdown_q.where(passed_cond)
    failed_breakdown_q = breakdown_q.where(failed_cond)

    return {
        'passed': fetch_many(passed_breakdown_q),
        'failed': fetch_many(failed_breakdown_q)
    }

  
// Generated queries:
1.
SELECT CEIL(COALESCE(AVG(DATE_PART('epoch', ("itrain_moduleresponse"."completed_at" - "itrain_moduleresponse"."started_at"))), 0)) AS "average_time_to_complete"
FROM "itrain_moduleresponse"
INNER JOIN "itrain_module" ON ("itrain_moduleresponse"."module_id" = "itrain_module"."id")
WHERE "itrain_module"."company_id" = 3
  AND "itrain_moduleresponse"."completed_at" BETWEEN DATE('2017-08-01T00:00:00'::TIMESTAMP) AND DATE('2017-12-08T00:00:00'::TIMESTAMP)

2.
SELECT COALESCE(SUM("count_union"."tot"), 0) AS "tot"
FROM (
        (SELECT COALESCE(COUNT(1), 0) AS "tot"
         FROM "core_applicensegroupuser"
         INNER JOIN "core_applicensegroup"
         INNER JOIN "itrain_moduleassignee"
         INNER JOIN "itrain_module" ON ("itrain_moduleassignee"."module_id" = "itrain_module"."id") ON ("itrain_moduleassignee"."app_license_group_id" = "core_applicensegroup"."id") ON ("core_applicensegroupuser"."app_license_group_id" = "core_applicensegroup"."id")
         WHERE "itrain_module"."company_id" = 3
           AND "itrain_module"."status" = 'published'
           AND DATE('2017-08-01T00:00:00'::TIMESTAMP) <= "itrain_module"."start_datetime"
           AND "itrain_module"."start_datetime" <= DATE('2017-12-08T00:00:00'::TIMESTAMP))
      UNION ALL
        (SELECT COALESCE(COUNT(1), 0) AS "tot"
         FROM "itrain_moduleassignee"
         INNER JOIN "itrain_module" ON ("itrain_moduleassignee"."module_id" = "itrain_module"."id")
         WHERE "itrain_module"."company_id" = 3
           AND "itrain_module"."status" = 'published'
           AND DATE('2017-08-01T00:00:00'::TIMESTAMP) <= "itrain_module"."start_datetime"
           AND "itrain_module"."start_datetime" <= DATE('2017-12-08T00:00:00'::TIMESTAMP)
           AND "itrain_moduleassignee"."user_id" IS NOT NULL)) AS "count_union"

3.
SELECT
  (SELECT COALESCE(COUNT(1), 0) AS "tot_passed"
   FROM "itrain_moduleresponse"
   INNER JOIN "itrain_module" ON ("itrain_moduleresponse"."module_id" = "itrain_module"."id")
   WHERE "itrain_module"."company_id" = 3
     AND "itrain_moduleresponse"."score" * 100 >= "itrain_module"."pass_rate"
     AND "itrain_moduleresponse"."completed_at" BETWEEN DATE('2017-08-01T00:00:00'::TIMESTAMP) AND DATE('2017-12-08T00:00:00'::TIMESTAMP)) AS "tot_passed_in_period",

  (SELECT COALESCE(COUNT(1), 0) AS "tot_passed"
   FROM "itrain_moduleresponse"
   INNER JOIN "itrain_module" ON ("itrain_moduleresponse"."module_id" = "itrain_module"."id")
   WHERE "itrain_module"."company_id" = 3
     AND "itrain_moduleresponse"."score" * 100 >= "itrain_module"."pass_rate") AS "tot_passed_overall",

  (SELECT COALESCE(COUNT(1), 0) AS "tot_passed"
   FROM "itrain_moduleresponse"
   INNER JOIN "itrain_module" ON ("itrain_moduleresponse"."module_id" = "itrain_module"."id")
   WHERE "itrain_module"."company_id" = 3
     AND "itrain_moduleresponse"."score" * 100 < "itrain_module"."pass_rate"
     AND "itrain_moduleresponse"."completed_at" BETWEEN DATE('2017-08-01T00:00:00'::TIMESTAMP) AND DATE('2017-12-08T00:00:00'::TIMESTAMP)) AS "tot_failed_in_period",

  (SELECT COALESCE(COUNT(1), 0) AS "tot_passed"
   FROM "itrain_moduleresponse"
   INNER JOIN "itrain_module" ON ("itrain_moduleresponse"."module_id" = "itrain_module"."id")
   WHERE "itrain_module"."company_id" = 3
     AND "itrain_moduleresponse"."score" * 100 >= "itrain_module"."pass_rate") AS "tot_failed_overall"
FROM "itrain_moduleresponse"