Created
August 14, 2017 13:54
-
-
Save mackensen/548da267954ce4292667e73a572fd7c0 to your computer and use it in GitHub Desktop.
This report groups all Moodle courses for a given term by department with activity status.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT department, | |
COUNT(*) AS courses, | |
SUM(CASE | |
WHEN active=1 THEN 1 | |
ELSE 0 | |
END) active, | |
SUM(CASE | |
WHEN active=0 THEN 1 | |
ELSE 0 | |
END) inactive | |
FROM | |
(SELECT LEFT(c.fullname, LOCATE(' ', c.fullname) - 1) AS department, | |
CASE | |
WHEN EXISTS | |
(SELECT id | |
FROM prefix_logstore_standard_log lsl | |
WHERE lsl.courseid=c.id | |
AND (lsl.crud='c' | |
OR lsl.crud='u') | |
AND lsl.userid <> 0 | |
AND lsl.userid <> 2 | |
LIMIT 1) | |
AND NOT EXISTS | |
(SELECT id | |
FROM prefix_enrol e | |
WHERE e.enrol='meta' | |
AND (e.courseid=c.id | |
OR e.customint1=c.id) | |
LIMIT 1) THEN 1 | |
WHEN EXISTS | |
(SELECT id | |
FROM prefix_enrol e | |
WHERE e.enrol='meta' | |
AND e.customint1=c.id | |
LIMIT 1) THEN 1 | |
ELSE 0 | |
END AS active | |
FROM prefix_course c | |
WHERE EXISTS | |
(SELECT id | |
FROM prefix_course_categories cc | |
WHERE cc.id=c.category | |
AND cc.parent=:term) | |
AND NOT EXISTS | |
(SELECT id | |
FROM prefix_enrol e | |
WHERE e.enrol='meta' | |
AND e.courseid=c.id) | |
AND c.shortname NOT LIKE 'EXCL%' | |
AND c.fullname NOT LIKE '%Independent%' | |
AND c.fullname NOT LIKE '%Thesis%') t1 | |
GROUP BY department |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment