Hello everyone,
I would like to get a report with usernames and cohorts users belong to together with the status of the scorm activites on the site. I have found two SQL snippets of code but unfortunately I have no idea how to combine them to achieve what I want. I would be really grateful if there was someone who is able to help me with it.
Chris
1. Users and cohorts
SELECT u.firstname, u.lastname, h.idnumber, h.name
FROM prefix_cohort AS h
JOIN prefix_cohort_members AS hm ON h.id = hm.cohortid
JOIN prefix_user AS u ON hm.userid = u.id
ORDER BY u.firstname
2. List of users with the scorm statuses.
SELECT
u.username AS 'User',
c.shortname AS 'Course',
m.name AS Activitytype,
CASE
WHEN cm.module = 1 THEN (SELECT a1.name FROM prefix_assign a1 WHERE a1.id = cm.instance)
WHEN cm.module = 2 THEN (SELECT a2.name FROM prefix_assignment a2 WHERE a2.id = cm.instance)
WHEN cm.module = 3 THEN (SELECT a3.name FROM prefix_book a3 WHERE a3.id = cm.instance)
WHEN cm.module = 4 THEN (SELECT a4.name FROM prefix_chat a4 WHERE a4.id = cm.instance)
WHEN cm.module = 5 THEN (SELECT a5.name FROM prefix_choice a5 WHERE a5.id = cm.instance)
WHEN cm.module = 6 THEN (SELECT a6.name FROM prefix_data a6 WHERE a6.id = cm.instance)
WHEN cm.module = 7 THEN (SELECT a7.name FROM prefix_feedback a7 WHERE a7.id = cm.instance)
WHEN cm.module = 8 THEN (SELECT a8.name FROM prefix_folder a8 WHERE a8.id = cm.instance)
WHEN cm.module = 9 THEN (SELECT a9.name FROM prefix_forum a9 WHERE a9.id = cm.instance)
WHEN cm.module = 10 THEN (SELECT a10.name FROM prefix_glossary a10 WHERE a10.id = cm.instance)
WHEN cm.module = 11 THEN (SELECT a11.name FROM prefix_imscp a11 WHERE a11.id = cm.instance)
WHEN cm.module = 12 THEN (SELECT a12.name FROM prefix_label a12 WHERE a12.id = cm.instance)
WHEN cm.module = 13 THEN (SELECT a13.name FROM prefix_lesson a13 WHERE a13.id = cm.instance)
WHEN cm.module = 14 THEN (SELECT a14.name FROM prefix_lti a14 WHERE a14.id = cm.instance)
WHEN cm.module = 15 THEN (SELECT a15.name FROM prefix_page a15 WHERE a15.id = cm.instance)
WHEN cm.module = 16 THEN (SELECT a16.name FROM prefix_quiz a16 WHERE a16.id = cm.instance)
WHEN cm.module = 17 THEN (SELECT a17.name FROM prefix_resource a17 WHERE a17.id = cm.instance)
WHEN cm.module = 18 THEN (SELECT a18.name FROM prefix_scorm a18 WHERE a18.id = cm.instance)
WHEN cm.module = 19 THEN (SELECT a19.name FROM prefix_survey a19 WHERE a19.id = cm.instance)
WHEN cm.module = 20 THEN (SELECT a20.name FROM prefix_url a20 WHERE a20.id = cm.instance)
WHEN cm.module = 21 THEN (SELECT a21.name FROM prefix_wiki a21 WHERE a21.id = cm.instance)
WHEN cm.module = 22 THEN (SELECT a22.name FROM prefix_workshop a22 WHERE a22.id = cm.instance)
END AS Actvityname,
# cm.section AS Coursesection,
CASE
WHEN cm.completion = 0 THEN '0 None'
WHEN cm.completion = 1 THEN '1 Self'
WHEN cm.completion = 2 THEN '2 Auto'
END AS Activtycompletiontype,
CASE
WHEN cmc.completionstate = 0 THEN 'In Progress'
WHEN cmc.completionstate = 1 THEN 'Completed'
WHEN cmc.completionstate = 2 THEN 'Completed with Pass'
WHEN cmc.completionstate = 3 THEN 'Completed with Fail'
ELSE 'Unknown'
END AS 'Progress',
DATE_FORMAT(FROM_UNIXTIME(cmc.timemodified), '%Y-%m-%d %H:%i') AS 'When'
FROM prefix_course_modules_completion cmc
JOIN prefix_user u ON cmc.userid = u.id
JOIN prefix_course_modules cm ON cmc.coursemoduleid = cm.id
JOIN prefix_course c ON cm.course = c.id
JOIN prefix_modules m ON cm.module = m.id
# skip the predefined admin AND guest USER
WHERE u.id > 2
# config reports filters
%%FILTER_USERS:u.username%%
%%FILTER_SEARCHTEXT:m.name:~%%
%%FILTER_STARTTIME:cmc.timemodified:>%% %%FILTER_ENDTIME:cmc.timemodified:<%%
ORDER BY u.username