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.