We are using Moodle 3.1.6 and have the Ad-hoc database plugin. Our Moodle database is postgres (9.3.17). Unfortunately, my knowledge of postgres is very small and I seem to have reached my limits with the code I am writing, so any help would be appreciated.
We want a report that provides a list of students whose course will be expiring in the next 30 days (e.g. month). The report should return the Course name, Group name, Student name and Expiry date. Students are grouped according to their tutor ('teacher') which is why we would like to include the group name.
So far I have been able to write two separate queries to gather this information but need to combine them so that we have one report with all four columns, which is where I am stuck. Report 1 generates Course name, Student name and Expiry date (returning just those expiring in the next 30 days). Report 2 generates Group name, Student name, and Course name.
The code I have written is as follows:
Report 1
SELECT c.fullname AS "Course", u.firstname||' '||u.lastname||'' AS "Student", to_timestamp(ue.timeend) AS "Expiry Date"
FROM {enrol} e
JOIN {user_enrolments} ue ON ue.enrolid = e.id
JOIN {course} c ON c.id = e.courseid
JOIN {user} u ON u.id = ue.userid
WHERE ue.enrolid = e.id AND ue.timeend > 0 AND now() + interval '30 days' > to_timestamp(ue.timeend)
Report 2
SELECT g.name AS "Tutor", u.firstname||' '||u.lastname||'' AS "Student", c.fullname AS "Course"
FROM {groups} g
LEFT JOIN {groups_members} gm ON g.id = gm.groupid
JOIN {user} u ON u.id = gm.userid
JOIN {course} c ON c.id = g.courseid