Custom SQL report for a moodle LMS
- or -
Post a project like this$$
- Posted:
- Proposals: 7
- Remote
- #2202773
- Expired
Top rated PHP Web Development | WordPress | Magento | Drupal | OpenCart | PrestaShop | Joomla
Leicester
Database Engineer, Infrastructure, DevOp and Tech Support, Software development
Mexico City
328276105075415006851529570205598422444572463547
Description
Experience Level: Intermediate
Estimated project duration: less than 1 week
I wanted to know if you could help me - We run and host a Moodle platform for our client and basically need a specific report producing.
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.
Mark
Please let me know if you have any questions or feel any of this is not possible.
-------------
SELECT
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,
CASE
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,
CASE
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'
ELSE 'Unknown'
END AS 'Progress',
CASE
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
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.
Mark
Please let me know if you have any questions or feel any of this is not possible.
-------------
SELECT
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,
CASE
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,
CASE
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'
ELSE 'Unknown'
END AS 'Progress',
CASE
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.
100% (2)Projects Completed
2
Freelancers worked with
1
Projects awarded
13%
Last project
17 Jun 2022
United Kingdom
New Proposal
Login to your account and send a proposal now to get this project.
Log inClarification Board Ask a Question
-
Hi Mark,
Do you use the custom report block - https://moodle.org/plugins/block_configurable_reports?
Best Regards
Dave
Mark B.29 Nov 2018We 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.
741482
We collect cookies to enable the proper functioning and security of our website, and to enhance your experience. By clicking on 'Accept All Cookies', you consent to the use of these cookies. You can change your 'Cookies Settings' at any time. For more information, please read ourCookie Policy
Cookie Settings
Accept All Cookies