CADO resource/example report: Difference between revisions
From MoodleDocs
Naomi Quirke (talk | contribs) |
Naomi Quirke (talk | contribs) |
||
| Line 1: | Line 1: | ||
==Workflow report for the CADO plugin== | ==Workflow report for the CADO plugin== | ||
This is created for use in the Ad-hoc database queries plugin. | This is created 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: | The output includes: | ||
* course, grouping, CADO name | * course, grouping, CADO name | ||
| Line 11: | Line 10: | ||
* a filter for course date, (using earliest start date and latest end date) | * 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) | * a choice of whether the html in the comment is to be included or not (value of 1 or 0 must be entered) | ||
===MySQL=== | |||
<code sql> | <code sql> | ||
SELECT c.shortname course | SELECT c.shortname course | ||
| Line 31: | Line 30: | ||
JOIN {course_modules} cmod ON cmod.instance = ca.id | JOIN {course_modules} cmod ON cmod.instance = ca.id | ||
JOIN {modules} m on m.id = cmod.module AND m.name = "cado" | 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 | |||
</code> | |||
===PostgreSQL=== | |||
<code sql> | |||
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 {groupings} g ON cmod.groupingid = g.id | ||
LEFT JOIN {user} u1 ON ca.generateuser = u1.id | LEFT JOIN {user} u1 ON ca.generateuser = u1.id | ||
Revision as of 04:38, 8 August 2020
Workflow report for the CADO plugin
This is created 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('
', ca.approvecomment, '')::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
