CADO resource/example report

Jump to: navigation, search

Workflow report for the CADO plugin

This is created for reporting on workflow status of CADOs created in the CADO_resource, SQL code for use in the Ad-hoc database queries plugin.

There are two versions, one for a MySQL database, and one for a PostgreSQL one. 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)

MySQL

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

PostgreSQL

SELECT c.shortname course
  , ca.name cado_name
  , COALESCE( 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
  , CASE 
     WHEN ca.generateuser = 0 THEN 'Anonymous'
     ELSE concat( u1.firstname, ' ', u1.lastname )
     END originator
  , CASE 
     WHEN ca.approveuser = 0 THEN  'Anonymous'
     ELSE concat( u2.firstname, ' ', u2.lastname )
     END approver
  , ca.timemodified modify_date
  , CASE 
     WHEN :include_html_in_comment = 1 THEN ca.approvecomment
     ELSE array_to_string( xpath('//text()', concat('<div>', ca.approvecomment, '</div>')::xml)::text[] , ' ' ) 
     END approve_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