From edc91676f123a69ccb22e1f8d20083f302e71714 Mon Sep 17 00:00:00 2001
From: toyomoyo <toyomoyo>
Date: Tue, 31 Jul 2007 03:19:32 +0000
Subject: [PATCH] fixing the average query to use subquery so that userids can
 be distinct, this prevents duplication of grades in case of multiple roles
 for same user

---
 grade/report/grader/lib.php | 30 ++++++++++++++++++++++++++----
 1 file changed, 26 insertions(+), 4 deletions(-)

diff --git a/grade/report/grader/lib.php b/grade/report/grader/lib.php
index 264ac4da5d..7c914cf019 100644
--- a/grade/report/grader/lib.php
+++ b/grade/report/grader/lib.php
@@ -748,11 +748,15 @@ class grade_report_grader extends grade_report {
         } else {
             $totalcount = $this->get_numusers();
         }
+        
         if ($showaverages) {
+            /*
+             * this sql is broken in the event of multiple grade book roles assigned to one user
+             * or same role in multiple contexts
             $SQL = "SELECT g.itemid, SUM(g.finalgrade) as sum, COUNT(DISTINCT(u.id)) as count
-                FROM {$CFG->prefix}grade_items gi INNER JOIN
-                     {$CFG->prefix}grade_grades g ON gi.id = g.itemid INNER JOIN
-                     {$CFG->prefix}user u ON u.id = g.userid INNER JOIN
+                FROM {$CFG->prefix}grade_items gi LEFT JOIN
+                     {$CFG->prefix}grade_grades g ON gi.id = g.itemid LEFT JOIN
+                     {$CFG->prefix}user u ON u.id = g.userid LEFT JOIN
                      {$CFG->prefix}role_assignments ra ON u.id = ra.userid
                      $groupsql
                 WHERE gi.courseid = $this->courseid
@@ -760,7 +764,25 @@ class grade_report_grader extends grade_report {
                 AND ra.roleid in ($this->gradebookroles)
                 AND ra.contextid ".get_related_contexts_string($this->context)."
                 GROUP BY g.itemid";
-
+            */
+        
+            // the first join on user is needed for groupsql
+            $SQL = "SELECT g.itemid, SUM(g.finalgrade) as sum, COUNT(DISTINCT(u.id)) as count
+                FROM {$CFG->prefix}grade_items gi LEFT JOIN
+                     {$CFG->prefix}grade_grades g ON gi.id = g.itemid LEFT JOIN
+                     {$CFG->prefix}user u ON g.userid = u.id
+                     $groupsql
+                WHERE gi.courseid = $this->courseid
+                     $groupwheresql
+                     AND g.userid IN (                     
+                        SELECT DISTINCT(u.id) 
+                        FROM {$CFG->prefix}user u LEFT JOIN
+                             {$CFG->prefix}role_assignments ra ON u.id = ra.userid
+                        WHERE ra.roleid in ($this->gradebookroles)
+                             AND ra.contextid ".get_related_contexts_string($this->context)."
+                     )
+                GROUP BY g.itemid";
+            
             $sum_array = array();
             $count_array = array();
             $sums = get_records_sql($SQL);
-- 
2.39.5