Dear all, thanks for helping out in advance!
I'm helping with a project. This site has a customization (for Moodle 1.9) called braindband custom completion. However, this whole 3rd party module is kind of buggy, and I'm trying to trouble shoot a weird problem. It is running on top of a LAPP stack. It added four custom tables and injected some codes into some of the core library files (bad practice, I know):
- mdl_completion
- mdl_completion_instances
- mdl_ges_grade_categories
- mdl_ges_grade_category_progress
Basically it added some lines in the /course/user.php so that it would display each student's current progress:
if ($tmp AND $tmp->timeinstructor > 0)
print_heading(get_string('completed','completion'));
else {
$tmp = get_record_sql("
SELECT gcp.user_id, gcp.graded, gc.applicable_items
FROM {$CFG->prefix}ges_grade_category_progress gcp
JOIN {$CFG->prefix}ges_grade_categories gc ON (gc.grade_item_id = gcp.grade_item_id)
JOIN {$CFG->prefix}grade_items gi ON (gi.id = gc.grade_item_id)
WHERE gi.courseid = {$course->id} AND gi.itemtype = 'course'
AND gcp.user_id = {$user->id}
");
$mh_format_progress = format_float($tmp->graded * 100.0 / $tmp->applicable_items, 1);
print_heading('Progress: '. ($tmp ? ($mh_format_progress > 100) ? '100' : $mh_format_progress : '0.0') .' %');
Somehow, after I look into the applicable_items, it should be 19 but in the database it was only showing 7, so the calculation was wrong (some students are getting over 100% for their progress). Now I want to write a simple query to list out all the courses AND students who are getting >100% Progress errors. I think this would require a for-loop in Postgres SQL or something. Can I achieve this with one single Postgres DB query in pgAdmin III (or use Ad-hoc DB query block)? Or do I really need to write a simple PHP code to go through each course and do the calculation and see if any student are getting >100% percentage for their current progress? Any tips or comments would be highly appreciated. Thanks again!
Wen