I am trying to write a report that would identify the Activity completion settings for each activity in a Moodle course, as well as the completion criteria set.
I’m really a novice with sql queries, so I don’t even know where to start. I found a forum report in the Moodle contributed reports that identifies the settings in a forum, as seen in the query below
This is very similar to what I would like to achieve for each activity
SELECT f.course AS 'Course ID', c.fullname AS 'Course Name', concat('<a target="_new" href="%%WWWROOT%%/mod/forum/view.php?id=',cm.id,'">',f.name,'</a>') AS 'Forum name',cm.id AS 'Forum ID',
CASE
WHEN f.forcesubscribe = 0 THEN "Optional"
WHEN f.forcesubscribe = 1 THEN "Forced"
WHEN f.forcesubscribe = 2 THEN "Auto"
WHEN f.forcesubscribe = 3 THEN "Disabled"
END As 'Subscription Mode',
CASE
WHEN f.assessed = 0 THEN "No ratings"
WHEN f.assessed = 1 THEN "Average"
WHEN f.assessed = 2 THEN "Count"
WHEN f.assessed = 3 THEN "Maximum"
WHEN f.assessed = 4 THEN "Minimum"
WHEN f.assessed = 5 THEN "Sum"
END As 'Rating type',
CASE
WHEN f.scale < 0 THEN "Scale"
WHEN f.scale > 0 THEN f.scale
END As 'Scale'
FROM prefix_forum AS f
INNER JOIN prefix_course AS c ON f.course=c.id
INNER JOIN prefix_course_modules AS cm ON cm.course=c.id AND cm.instance=f.id
INNER JOIN prefix_modules AS m
ON cm.module=m.id and m.name='forum'
I tried tweaking this some but when I run it I get an "Error reading from database message"
I'm guessing the problem is in the joins
Here's the query that is producing the error.
SELECT f.course AS 'Course ID', c.fullname AS 'Course Name', concat('<a target="_new" href="%%WWWROOT%%/mod/assign/view.php?id=',cm.id,'">',f.name,'</a>')
AS 'Assign name',cm.id AS 'Assign ID',
CASE
WHEN f.completion = 0 THEN "Do not indicate activity completion"
WHEN f.completion = 1 THEN "Students can manually mark"
WHEN f.completion = 2 THEN "Show activity as complete when conditions are"
END As 'Completion tracking',
CASE
FROM prefix_assign AS f
INNER JOIN prefix_course AS c ON f.course=c.id
INNER JOIN prefix_course_modules AS cm ON cm.course=c.id AND cm.instance=f.id
INNER JOIN prefix_modules AS m
ON cm.module=m.id and m.name='assign'
I would appreciate any help and guidance on this.
Thank you
Adele