Skip to content

Instantly share code, notes, and snippets.

@mackensen
Created August 14, 2017 14:01
Show Gist options
  • Save mackensen/ae2aa70cf2b6663ff8bf92c2b106ceb4 to your computer and use it in GitHub Desktop.
Save mackensen/ae2aa70cf2b6663ff8bf92c2b106ceb4 to your computer and use it in GitHub Desktop.
This report lists all courses for a given term with activity status.
SELECT c.fullname,
c.shortname,
c.idnumber,
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%'
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment