The following SQL scripts might be helpful to find different anomalies on your Moodle site.
Modules and their ID number in your database
Before you adopt one of the following codes, you have to check your module IDs in your database as the codes are based on our modules table and it can be different in your database.
Start with the following query:
select * from mdl_modules
and save it somewhere.
Our most popular modules are:
Your database might be different, so you have to use your ID numbers in the following queries. The code looks similar to this one:
... AND cm.module = 55 -- 55 is our module ID for assignments, you have to use your own ID
Let's find some weird settings in Assignments
Missing 'Grade to pass'
Our assignments use the Attempts reopened: Automatically until pass setting under the Submissions settings. It is a great way to keep the students previous attempts accessible on the VLE, so we can see their progress during their studies. We can also control the number of their attempts (Maximum attempts), normally if it is a formative assessment, the students are allowed to submit unlimited time, but if it is a summative one, they have limited attempts.
Everything works fine apart from one little thing. If you leave the Grade to pass value as 0 under the Grade settings, then everyone will pass the assignment, regardless to the actual grading scale or point, so the assignment never reopens automatically, no one can resubmit anything.
The following report helps you to find mismatching settings:
SELECT gi.courseid, gi.itemname, gi.gradepass, gi.scaleid FROM mdl_grade_items AS gi JOIN mdl_course AS c ON c.id = gi.courseid JOIN mdl_assign AS ass ON gi.iteminstance = ass.id AND gi.courseid = ass.course JOIN mdl_course_modules AS cm ON cm.instance = ass.id AND cm.module = 55 -- don't forget to use your own assign module ID here JOIN mdl_course_sections AS cs ON cs.id = cm.section WHERE gi.gradepass = 0 AND gi.itemmodule = 'assign' AND gi.hidden = 0 -- visible grade item AND c.visible = 1 -- visible course AND ass.attemptreopenmethod = 'untilpass' AND cm.visible = 1 -- visible activity AND cs.visible = 1 -- visible course section
If the grade type is Point (e.g. between 0 and 100), the Grade to pass can be a point (e.g. 40), but when the grade type is Scale you have to be more careful.
If the grade type is Scale: every scale item has a number, e.g.
FAIL=1, PASS=2, MERIT=3, DISTINCTION=4
therefore the Grade to pass should be 2 (=PASS) in this case.
Assignments not sending notifications to graders
It is really important that the grader receives notifications about submitted files need to be graded.
Let's find assignments without notifying the grader.
SELECT cm.id AS moduleid, c.id AS courseid, c.shortname, ass.name FROM mdl_assign AS ass JOIN mdl_assign_plugin_config AS apc ON apc.assignment = ass.id JOIN mdl_course_modules AS cm ON cm.instance = ass.id AND cm.module = 55 -- don't forget to use your own assign module ID here JOIN mdl_course AS c ON c.id = cm.course WHERE ass.sendnotifications = 0 -- no notification AND cm.visible = 1 -- visible activity AND c.visible = 1 -- visible course AND apc.plugin = 'file' AND apc.subtype = 'assignsubmission' AND apc.name = 'enabled' AND apc.value = 1 -- file submission enabled AND ass.submissiondrafts = 1 -- student has to press Submit button AND ass.grade <> 0 -- graded assignment ORDER BY c.id
File submissions need to be graded but grader cannot upload feedback file
SELECT ass.course, ass.name FROM mdl_assign_plugin_config AS apc JOIN mdl_assign AS ass ON ass.id = apc.assignment WHERE ass.grade <> 0 -- needs to be graded AND apc.plugin = 'file' AND apc.subtype = 'assignfeedback' AND apc.name = 'enabled' AND apc.value = 0 -- no feedback file AND apc.assignment IN (SELECT ass.id FROM mdl_assign_plugin_config AS apc JOIN mdl_assign AS ass ON ass.id = apc.assignment JOIN mdl_course AS c ON c.id = ass.course JOIN mdl_course_modules AS cm ON cm.course = c.id AND cm.instance = ass.id AND cm.module = 55 -- don't forget to use your own assign module ID here WHERE apc.plugin = 'file' AND apc.subtype = 'assignsubmission' AND apc.name = 'enabled' AND apc.value = 1 -- file submission enabled AND c.visible = 1 -- visible course AND cm.visible = 1) -- visible activity ORDER BY ass.course DESC