The moodle course is set up with PDF's uploaded each week, which the user has to read (download or click on) and then agree to a statement confirming that they have read the pdf (currently we have set up a label for each pdf with 'Activity completion' manually marked by the user). The client needs to report which users 'HAVE COMPLETED' read and agreed each statement and which 'HAVE NOT COMPLETED'. They ideally need to know when each task was added and when they completed each task (ie Time/Date).
I need to produce a report that not only tells the client which activities have been completed, but the activities which have not been completed. This is where I'm struggling. There doesn't appear to be a database entry for each user created for an activity unless they interact with the activity. Is there a way to set all activities (cm.completion in the prefix_course_modules_completion cmc database) to either NULL or 0 as default?
The reason for not using the standard Activity Completion report within the course is that we need the report to also include custom user profile fields for each user. If you are aware of a method in which we can customise the actual reports within moodle, I'd be happy to go with this route also.
Below is the Query SQL that I'm using for guidance.
If you have any experience in the following or know someone who does, please let me know.
Thanks in advance.
Please let me know if you have any questions or feel any of this is not possible.
u.firstname AS 'Firstname',
u.lastname AS 'Lastname',
u.email AS 'Email',
CONCAT(u.firstname, ' ', u.lastname) AS `Name`,
c.shortname AS 'Course',
m.name AS Activitytype,
# cm.section AS Coursesection,
WHEN cm.completion = NULL THEN 'Not completed'
WHEN cm.completion = 0 THEN '0 None'
WHEN cm.completion = 1 THEN '1 Self'
WHEN cm.completion = 2 THEN '2 Auto'
END AS Activtycompletiontype,
WHEN cmc.completionstate = NULL THEN 'Not completed'
WHEN cmc.completionstate = 0 THEN 'Not completed'
WHEN cmc.completionstate = 1 THEN 'Completed'
WHEN cmc.completionstate = 2 THEN 'Completed with Pass'
WHEN cmc.completionstate = 3 THEN 'Completed with Fail'
END AS 'Progress',
WHEN cmc.viewed = 0 THEN 'NO'
WHEN cmc.viewed = 1 THEN 'YES'
END AS 'Agreed',
DATE_FORMAT(FROM_UNIXTIME(cmc.timemodified), '%Y-%m-%d %H:%i') AS 'When',
(SELECT data FROM prefix_user_info_data WHERE prefix_user_info_data.userid=u.id AND prefix_user_info_data.fieldid = 5) AS 'Employee Number',
(SELECT data FROM prefix_user_info_data WHERE prefix_user_info_data.userid=u.id AND prefix_user_info_data.fieldid = 3) AS 'Job Role',
(SELECT data FROM prefix_user_info_data WHERE prefix_user_info_data.userid=u.id AND prefix_user_info_data.fieldid = 1) AS 'Business Area',
(SELECT data FROM prefix_user_info_data WHERE prefix_user_info_data.userid=u.id AND prefix_user_info_data.fieldid = 6) AS Team
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
Mark B.0% (0)
Create an account now and send a proposal now to get this project.Sign up
Clarification Board Ask a Question
Do you use the custom report block - https://moodle.org/plugins/block_configurable_reports?
Mark B.29 Nov 2018
We haven't had any experience in using or configuring this plugin, but would be open to it if it offered a solution to the issue of reporting data on users who haven't viewed or completed certain tasks.