This report lists all courses for a given term with activity status.

allcoursesterm.sql

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%'