[Urgent] How to include grades in All activity module reports

[Urgent] How to include grades in All activity module reports

by Sarath P -
Number of replies: 6

Hello everyone,

I have a SQL code for showing  the users completion status of activities across all courses. I need to show the grades as well in this report. For example, if a user received a grade for a quiz, it should be displayed with respect to that quiz and for feedback, Url and other activities, if not received any grades, it should display 0 or Null. Can anyone please tell me how to get this kind of report? Where should I make change in order to get the required results? 

Moodle version: 3.7.1

Query 1:

SELECT u.username AS 'User', c.shortname AS 'Course', m.name AS Activitytype,

CASE 

WHEN cm.module = 1 THEN (SELECT a1.name FROM mdl_assign a1 WHERE a1.id = cm.instance)

WHEN cm.module = 2 THEN (SELECT a2.name FROM mdl_assignment a2 WHERE a2.id = cm.instance)

WHEN cm.module = 3 THEN (SELECT a3.name FROM mdl_book a3 WHERE a3.id = cm.instance) 

WHEN cm.module = 4 THEN (SELECT a4.name FROM mdl_chat a4 WHERE a4.id = cm.instance) 

WHEN cm.module = 5 THEN (SELECT a5.name FROM mdl_choice a5 WHERE a5.id = cm.instance)

WHEN cm.module = 6 THEN (SELECT a6.name FROM mdl_data a6 WHERE a6.id = cm.instance) 

WHEN cm.module = 7 THEN (SELECT a7.name FROM mdl_feedback a7 WHERE a7.id = cm.instance) 

WHEN cm.module = 8 THEN (SELECT a8.name FROM mdl_folder a8 WHERE a8.id = cm.instance)

WHEN cm.module = 9 THEN (SELECT a9.name FROM mdl_forum a9 WHERE a9.id = cm.instance) 

WHEN cm.module = 10 THEN (SELECT a10.name FROM mdl_glossary a10 WHERE a10.id = cm.instance)

WHEN cm.module = 11 THEN (SELECT a11.name FROM mdl_imscp a11 WHERE a11.id = cm.instance) 

WHEN cm.module = 12 THEN (SELECT a12.name FROM mdl_label a12 WHERE a12.id = cm.instance) 

WHEN cm.module = 13 THEN (SELECT a13.name FROM mdl_lesson a13 WHERE a13.id = cm.instance) 

WHEN cm.module = 14 THEN (SELECT a14.name FROM mdl_lti a14 WHERE a14.id = cm.instance) 

WHEN cm.module = 15 THEN (SELECT a15.name FROM mdl_page a15 WHERE a15.id = cm.instance)

WHEN cm.module = 16 THEN (SELECT a16.name FROM mdl_quiz a16 WHERE a16.id = cm.instance)

WHEN cm.module = 17 THEN (SELECT a17.name FROM mdl_resource a17 WHERE a17.id = cm.instance)

WHEN cm.module = 18 THEN (SELECT a18.name FROM mdl_scorm a18 WHERE a18.id = cm.instance)

WHEN cm.module = 19 THEN (SELECT a19.name FROM mdl_survey a19 WHERE a19.id = cm.instance) 

WHEN cm.module = 20 THEN (SELECT a20.name FROM mdl_url a20 WHERE a20.id = cm.instance)

WHEN cm.module = 21 THEN (SELECT a21.name FROM mdl_wiki a21 WHERE a21.id = cm.instance)

WHEN cm.module = 22 THEN (SELECT a22.name FROM mdl_workshop a22 WHERE a22.id = cm.instance)

END AS Actvityname,

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 mdl_course_modules_completion cmc 

JOIN mdl_user u ON cmc.userid = u.id 

JOIN mdl_course_modules cm ON cmc.coursemoduleid = cm.id 

JOIN mdl_course c ON cm.course = c.id 

JOIN mdl_modules m ON cm.module = m.id 

WHERE c.shortname= 'dummycourse' 

Output for Query 1:

Output for Query1

Query 1 is working perfectly. What I need is to include the respective grades to this report. So I modified the query.

Query 2:

SELECT u.username AS 'User', c.shortname AS 'Course', m.name AS Activitytype,

CASE 

WHEN cm.module = 1 THEN (SELECT a1.name FROM mdl_assign a1 WHERE a1.id = cm.instance)

WHEN cm.module = 2 THEN (SELECT a2.name FROM mdl_assignment a2 WHERE a2.id = cm.instance)

WHEN cm.module = 3 THEN (SELECT a3.name FROM mdl_book a3 WHERE a3.id = cm.instance) 

WHEN cm.module = 4 THEN (SELECT a4.name FROM mdl_chat a4 WHERE a4.id = cm.instance) 

WHEN cm.module = 5 THEN (SELECT a5.name FROM mdl_choice a5 WHERE a5.id = cm.instance)

WHEN cm.module = 6 THEN (SELECT a6.name FROM mdl_data a6 WHERE a6.id = cm.instance) 

WHEN cm.module = 7 THEN (SELECT a7.name FROM mdl_feedback a7 WHERE a7.id = cm.instance) 

WHEN cm.module = 8 THEN (SELECT a8.name FROM mdl_folder a8 WHERE a8.id = cm.instance)

WHEN cm.module = 9 THEN (SELECT a9.name FROM mdl_forum a9 WHERE a9.id = cm.instance) 

WHEN cm.module = 10 THEN (SELECT a10.name FROM mdl_glossary a10 WHERE a10.id = cm.instance)

WHEN cm.module = 11 THEN (SELECT a11.name FROM mdl_imscp a11 WHERE a11.id = cm.instance) 

WHEN cm.module = 12 THEN (SELECT a12.name FROM mdl_label a12 WHERE a12.id = cm.instance) 

WHEN cm.module = 13 THEN (SELECT a13.name FROM mdl_lesson a13 WHERE a13.id = cm.instance) 

WHEN cm.module = 14 THEN (SELECT a14.name FROM mdl_lti a14 WHERE a14.id = cm.instance) 

WHEN cm.module = 15 THEN (SELECT a15.name FROM mdl_page a15 WHERE a15.id = cm.instance)

WHEN cm.module = 16 THEN (SELECT a16.name FROM mdl_quiz a16 WHERE a16.id = cm.instance)

WHEN cm.module = 17 THEN (SELECT a17.name FROM mdl_resource a17 WHERE a17.id = cm.instance)

WHEN cm.module = 18 THEN (SELECT a18.name FROM mdl_scorm a18 WHERE a18.id = cm.instance)

WHEN cm.module = 19 THEN (SELECT a19.name FROM mdl_survey a19 WHERE a19.id = cm.instance) 

WHEN cm.module = 20 THEN (SELECT a20.name FROM mdl_url a20 WHERE a20.id = cm.instance)

WHEN cm.module = 21 THEN (SELECT a21.name FROM mdl_wiki a21 WHERE a21.id = cm.instance)

WHEN cm.module = 22 THEN (SELECT a22.name FROM mdl_workshop a22 WHERE a22.id = cm.instance)

END AS Actvityname,

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' ,gg.finalgrade AS 'Marks' 

FROM mdl_course_modules_completion cmc 

JOIN mdl_user u ON cmc.userid = u.id 

JOIN mdl_course_modules cm ON cmc.coursemoduleid = cm.id 

JOIN mdl_course c ON cm.course = c.id 

JOIN mdl_modules m ON cm.module = m.id

 JOIN mdl_grade_items gi ON gi.courseid= c.id AND gi.itemmodule= m.name

JOIN mdl_grade_grades gg ON gg.userid=u.id AND gg.itemid=gi.id 

WHERE c.shortname= 'dummycourse'

Output for Query 2:

Its showing Null as the the marks for the assignment because the tutor has not yet provided the grade. 

Output for Query 2

As you can see, other activities are gone. I want to display all the activities and their grades, if they are graded.


Thanks in advance.

Average of ratings: -
In reply to Sarath P

Re: [Urgent] How to include grades in All activity module reports

by David Saylor -
You should LEFT JOIN mdl_grade_items and mdl_grade_grades. A JOIN will leave a data row out of the results unless the join is successful, so for ungraded activities it is excluding the entire row. LEFT JOIN, however, will keep the rest of the data row even if that specific join isn't able to be completed.

SELECT u.username AS 'User', c.shortname AS 'Course', m.name AS Activitytype,

CASE

WHEN cm.module = 1 THEN (SELECT a1.name FROM mdl_assign a1 WHERE a1.id = cm.instance)

WHEN cm.module = 2 THEN (SELECT a2.name FROM mdl_assignment a2 WHERE a2.id = cm.instance)

WHEN cm.module = 3 THEN (SELECT a3.name FROM mdl_book a3 WHERE a3.id = cm.instance)

WHEN cm.module = 4 THEN (SELECT a4.name FROM mdl_chat a4 WHERE a4.id = cm.instance)

WHEN cm.module = 5 THEN (SELECT a5.name FROM mdl_choice a5 WHERE a5.id = cm.instance)

WHEN cm.module = 6 THEN (SELECT a6.name FROM mdl_data a6 WHERE a6.id = cm.instance)

WHEN cm.module = 7 THEN (SELECT a7.name FROM mdl_feedback a7 WHERE a7.id = cm.instance)

WHEN cm.module = 8 THEN (SELECT a8.name FROM mdl_folder a8 WHERE a8.id = cm.instance)

WHEN cm.module = 9 THEN (SELECT a9.name FROM mdl_forum a9 WHERE a9.id = cm.instance)

WHEN cm.module = 10 THEN (SELECT a10.name FROM mdl_glossary a10 WHERE a10.id = cm.instance)

WHEN cm.module = 11 THEN (SELECT a11.name FROM mdl_imscp a11 WHERE a11.id = cm.instance)

WHEN cm.module = 12 THEN (SELECT a12.name FROM mdl_label a12 WHERE a12.id = cm.instance)

WHEN cm.module = 13 THEN (SELECT a13.name FROM mdl_lesson a13 WHERE a13.id = cm.instance)

WHEN cm.module = 14 THEN (SELECT a14.name FROM mdl_lti a14 WHERE a14.id = cm.instance)

WHEN cm.module = 15 THEN (SELECT a15.name FROM mdl_page a15 WHERE a15.id = cm.instance)

WHEN cm.module = 16 THEN (SELECT a16.name FROM mdl_quiz a16 WHERE a16.id = cm.instance)

WHEN cm.module = 17 THEN (SELECT a17.name FROM mdl_resource a17 WHERE a17.id = cm.instance)

WHEN cm.module = 18 THEN (SELECT a18.name FROM mdl_scorm a18 WHERE a18.id = cm.instance)

WHEN cm.module = 19 THEN (SELECT a19.name FROM mdl_survey a19 WHERE a19.id = cm.instance)

WHEN cm.module = 20 THEN (SELECT a20.name FROM mdl_url a20 WHERE a20.id = cm.instance)

WHEN cm.module = 21 THEN (SELECT a21.name FROM mdl_wiki a21 WHERE a21.id = cm.instance)

WHEN cm.module = 22 THEN (SELECT a22.name FROM mdl_workshop a22 WHERE a22.id = cm.instance)

END AS Actvityname,

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' ,gg.finalgrade AS 'Marks'

FROM mdl_course_modules_completion cmc

JOIN mdl_user u ON cmc.userid = u.id

JOIN mdl_course_modules cm ON cmc.coursemoduleid = cm.id

JOIN mdl_course c ON cm.course = c.id

JOIN mdl_modules m ON cm.module = m.id

LEFT JOIN mdl_grade_items gi ON gi.courseid= c.id AND gi.itemmodule= m.name

LEFT JOIN mdl_grade_grades gg ON gg.userid=u.id AND gg.itemid=gi.id

WHERE c.shortname= 'dummycourse'
Average of ratings: Useful (2)
In reply to David Saylor

Re: [Urgent] How to include grades in All activity module reports

by Sarath P -
Thank you David. It worked smile smile smile
Average of ratings: -
In reply to David Saylor

Re: [Urgent] How to include grades in All activity module reports

by Sarath P -

Hi David. The code worked as it is now showing all the activities in the report. I logged in as a test user and attempted all the activities which included a SCORM file as well. When it comes to the SCORM file, it is showing duplicate values. Please see the attached images. The 'Chapter 01' and 'mark test' are the SCORM activities. The 'Chapter 01' activity doesn't have marks hence it should be 0 as the grade. Once the user completed the 'mark test' activity, its mark which is 75 is also being displayed for the 'Chapter 01' activity. 

Only the 'mark test' activity have marks which displayed as 75 and is correct but a duplicate activity of the same is also generated with a grade as 0.

During my attempt for the first SCORM which is 'Chapter 01', only single activity was displayed. As soon as I completed the activity duplicate activity is also displayed.

Following is the image while the test user is still attending the scorm activity.

Report while the user is still attending the scorm

Report during the first scorm activity attempt

The second image is when the user completed the 'Chapter 01' activity. Here the activity is duplicated.

Report when the user completed attending the first scorm

After completing the scorm attempt


This is the image when the test user completed all the activities in a course

Report after completing all the activities in a course

After completing all the activities

As you can see, the marks for the 'mark test' activity is displayed for the 'Chapter 01' activity as well. Also, both the scorm activities are duplicated as well. 

Please suggest a solution for this issue.

Thank you.



Average of ratings: -
In reply to Sarath P

Re: [Urgent] How to include grades in All activity module reports

by David Saylor -

Hey Sarath,

The problem is stemming from how you're joining the grade items table.

LEFT JOIN mdl_grade_items gi ON gi.courseid= c.id AND gi.itemmodule= m.name


I haven't had a chance to look at how you need to update your query specifically, but basically you need another condition on that join that includes the iteminstance number that links to the relevant table for that activity. I'll try and have a look soon if I see you haven't come further.

Average of ratings: Useful (1)
In reply to David Saylor

Re: [Urgent] How to include grades in All activity module reports

by Sarath P -
Hi David,
Like you said I searched for the relevant table that links the iteminstance number and found that the iteminstance number is linking to the course_modules table.
The following is my updated code:

LEFT JOIN prefix_grade_items gi ON gi.courseid= c.id AND gi.itemmodule= m.name AND gi.iteminstance=cm.instance

Final output

Final output

During my testing, I found another issue with the report. When an activity is in progress state the time format in the report is different.
Date format issue when an activity is in progress state

But as soon as the user completed the activity the date format is correct(please see the final output image). 
This is code I am using for getting the date:

DATE_FORMAT(CONVERT_TZ(FROM_UNIXTIME(cmc.timemodified), '+00:00', '+05:30'), '%Y/%m/%d/ %r') AS 'When'

Can you please tell me what's causing this issue.
Thank you

Average of ratings: -
In reply to Sarath P

Re: [Urgent] How to include grades in All activity module reports

by David Saylor -
That date is what happens when the the number 0 is converted to a date format (timestamps stored in the DB count in seconds and start from 1970/01/01). You would need to check if the timemodified is > 0 before formatting it. You could either do that with a WHERE statement which would exclude that row altogether, or you could do a CASE statement in the SELECT portion of the query to handle it differently (maybe display nothing when it doesn't have a value > 0)
Average of ratings: Useful (1)