Dear Autors of this plugin and Moodle Users,
I've developed a report (Moodle 3.6) showing the following data:
user, e-mail, name, surname, course, category of course, type of activity, score, maximum score, %, if passed/failed, date when completed.
I've managed to create someting which looks like this (see below), but there is 1 issue: ROWS DUPLICATE (attachment), and with having many students attending many courses at the same time, this becomes really problematic. Unfortunately, this is where my knowledge of SQL ends, and my first thought was to turn to more experienced Users I hope this is the right place. Has anyone come accross a similar issue? I would be very gratefull for any guidance and help.
SELECT
u.username AS 'User',
u.email AS 'Email',
u.firstname AS 'Name' ,
u.lastname AS 'Surname',
c.shortname AS 'Course',
m.name AS 'ActivityType',
cc.name AS 'Category',
ROUND(gg.finalgrade,2) AS Score,ROUND(gg.rawgrademax,2) AS MAX, ROUND(gg.finalgrade / gg.rawgrademax * 100 ,2) AS Percentage,
IF (ROUND(gg.finalgrade / gg.rawgrademax * 100 ,2) > 75,'Yes' , 'No') AS Pass,
# cm.section AS Coursesection,
CASE
WHEN cmc.completionstate = 0 THEN 'In Progress'
WHEN cmc.completionstate = 1 THEN 'Completed'
WHEN cmc.completionstate = 2 THEN 'Passed'
WHEN cmc.completionstate = 3 THEN 'Failedl'
ELSE 'Unknown'
END AS 'Progress',
DATE_FORMAT(FROM_UNIXTIME(cmc.timemodified), '%Y-%m-%d %H:%i') AS 'Date'
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
JOIN prefix_context AS ctx ON c.id = ctx.instanceid
JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
JOIN prefix_grade_grades AS gg ON gg.userid = u.id
JOIN prefix_grade_items AS gi ON gi.id = gg.itemid
JOIN prefix_course_categories AS cc ON cc.id = c.category
WHERE gi.courseid = c.id AND gi.itemname != 'Attendance' and u.id > 2
%%FILTER_USERS:u.username%%
%%FILTER_SEARCHTEXT:m.name:~%%
%%FILTER_STARTTIME:cmc.timemodified:>%% %%FILTER_ENDTIME:cmc.timemodified:<%%
ORDER BY u.lastname
Thank you and have a nice day
Renata
