-- Configurable Reports (block_configurable_reports) SQL report:
-- every deadline extension granted by local_extenddeadline.
--
-- Setup: Site administration > Reports > Configurable reports > Add report,
-- report type "SQL report", then paste this query in.

SELECT
    CONCAT(u.firstname, ' ', u.lastname)          AS learner,
    u.email                                       AS email,
    lp.name                                       AS program,
    c.fullname                                    AS course,
    e.modname                                     AS activity_type,
    CASE e.modname
        WHEN 'assign' THEN a.name
        WHEN 'quiz'   THEN q.name
        ELSE '(unknown)'
    END                                           AS activity,
    e.daysgranted                                 AS days_granted,
    e.reasonbucket                                AS reason,
    e.reasontext                                  AS reason_detail,
    FROM_UNIXTIME(e.originaldeadline)             AS original_deadline,
    FROM_UNIXTIME(e.granteddeadline)              AS new_deadline,
    FROM_UNIXTIME(e.timecreated)                  AS granted_at,
    (SELECT COUNT(*)
       FROM prefix_local_extenddeadline_grant e2
      WHERE e2.userid = e.userid
        AND e2.programid = e.programid)           AS grants_used_in_program
FROM prefix_local_extenddeadline_grant e
JOIN prefix_user u          ON u.id = e.userid
JOIN prefix_course c        ON c.id = e.courseid
LEFT JOIN prefix_learningpaths lp ON lp.id = e.programid
LEFT JOIN prefix_assign a   ON e.modname = 'assign' AND a.id = e.instanceid
LEFT JOIN prefix_quiz q     ON e.modname = 'quiz'   AND q.id = e.instanceid
ORDER BY e.timecreated DESC
