My boss wanted a report to show who had submitted a certification of their debit by courses. We have approximately 120 courses. All courses have a consultant with teacher role. All the student are in separate cohorts. Each cohorts added one-by-one to courses so one cohort to one course. Consultants need to know who paid or who not. So I have made a separate course with a single assignment where students upload their certifications. In each course I have to made configurable report where the consultants see only their students upload status.
So the SQL is:
SELECT u.lastname Lastname, u.firstname Firstname, u.username Username, u.email Email, ch.name Cohortname, s.status SubmissionStatus, concat('<a target="_blank" href="%%WWWROOT%%/pluginfile.php/Submission-id/assignsubmission_file/submission_files/',f.itemid,'/',f.filename,'?forcedownload=1">',f.filename,'</a>') Download, FROM_UNIXTIME(f.timecreated) SubmissionDate
FROM prefix_user AS u
JOIN prefix_cohort_members AS cm ON cm.userid = u.id
JOIN prefix_cohort AS ch ON ch.id = cm.cohortid and ch.idnumber = 'Cohort-id'
JOIN prefix_role_assignments AS ra ON ra.userid=u.id and ra.contextid in
(SELECT co.id from prefix_context co WHERE co.contextlevel = 50 and instanceid in (
SELECT id from prefix_course where id = Course-id)
) AND ra.roleid=5
LEFT JOIN prefix_assign_submission AS s on u.id = s.userid AND s.assignment = (SELECT instance FROM prefix_course_modules WHERE course=Course-id and id=Assignment-id)
LEFT JOIN prefix_assignsubmission_file AS asf ON s.id = asf.submission AND s.assignment = asf.assignment
left join prefix_files f ON f.itemid = asf.submission AND f.filesize > 0
LEFT JOIN prefix_files_reference r ON f.referencefileid = r.id
ORDER BY ch.name, u.lastname, u.firstname
And finally all the consultants are satisfied.