CADO resource/example report

Workflow report for the CADO plugin

This is created for use in the Ad-hoc database queries plugin. It has been written for a MySQL / MariaDB installation. The output includes:

• course, grouping, CADO name
• workflow status in five phases (Initialised, Creation, Reviewing, Approved, Regeneration)
• generate-user and approve-user full names
• modified date
• comment of last review / approve

It has:

• a filter for course date, (using earliest start date and latest end date)
• a choice of whether the html in the comment is to be included or not (value of 1 or 0 must be entered)

SELECT c.shortname course

 , ca.name cado_name
 , ifnull( g.name, "None") grouping_name
 , CASE
    WHEN ca.timegenerated = 0 THEN "Initialised"
    WHEN ca.timeproposed = 0 AND (ca.approvecomment is null OR ca.approvecomment = "") THEN "Creation"
    WHEN ca.timeapproved is null AND ca.timeproposed > 0 THEN "Reviewing"
    WHEN ca.timeapproved > 0 THEN "Approved"
    ELSE "Regeneration"
  END workflow_status
 , IF( ca.generateuser = 0, "Anonymous", concat( u1.firstname, " ", u1.lastname )) originator
 , IF( ca.approveuser = 0, "Anonymous", concat( u2.firstname, " ", u2.lastname )) approver
 , ca.timemodified modify_date
 , IF( :include_html_in_comment = 1, ca.approvecomment, ExtractValue(ca.approvecomment, '//text()')) comment

FROM {cado} ca JOIN {course} c ON c.id = ca.course JOIN {course_modules} cmod ON cmod.instance = ca.id JOIN {modules} m on m.id = cmod.module AND m.name = "cado" LEFT JOIN {groupings} g ON cmod.groupingid = g.id LEFT JOIN {user} u1 ON ca.generateuser = u1.id LEFT JOIN {user} u2 ON ca.approveuser = u2.id WHERE c.startdate >= :course_start_date AND c.enddate <= :course_end_date