Merge rebased branch 'MDL-25769' of git://github.com/andyjdavis/moodle.git
authorSam Hemelryk <sam@moodle.com>
Tue, 18 Jan 2011 04:03:11 +0000 (12:03 +0800)
committerSam Hemelryk <sam@moodle.com>
Tue, 18 Jan 2011 04:03:11 +0000 (12:03 +0800)
grade/import/lib.php [changed mode: 0755->0644]
grade/report/grader/lib.php
grade/report/lib.php [changed mode: 0755->0644]

old mode 100755 (executable)
new mode 100644 (file)
index 574b354..1209e1c
@@ -163,22 +163,31 @@ function get_unenrolled_users_in_import($importcode, $courseid) {
     global $CFG, $DB;
     $relatedctxcondition = get_related_contexts_string(get_context_instance(CONTEXT_COURSE, $courseid));
 
-    list($usql, $params) = $DB->get_in_or_equal(explode(',', $CFG->gradebookroles));
+    //users with a gradeable role
+    list($gradebookrolessql, $gradebookrolesparams) = $DB->get_in_or_equal(explode(',', $CFG->gradebookroles), SQL_PARAMS_NAMED, 'grbr0');
+
+    //enrolled users
+    $context = get_context_instance(CONTEXT_COURSE, $courseid);
+    list($enrolledsql, $enrolledparams) = get_enrolled_sql($context);
 
     $sql = "SELECT giv.id, u.firstname, u.lastname, u.idnumber AS useridnumber,
-                COALESCE(gi.idnumber, gin.itemname) AS gradeidnumber
-            FROM
-                {grade_import_values} giv
-                JOIN {user} u ON giv.userid = u.id
-                LEFT JOIN {grade_items} gi ON gi.id = giv.itemid
-                LEFT JOIN {grade_import_newitem} gin ON gin.id = giv.newgradeitem
-                LEFT JOIN {role_assignments} ra ON (giv.userid = ra.userid AND
-                    ra.roleid $usql AND
-                    ra.contextid $relatedctxcondition)
-                WHERE giv.importcode = ?
-                    AND ra.id IS NULL
-                ORDER BY gradeidnumber, u.lastname, u.firstname";
-    $params[] = $importcode;
+                   COALESCE(gi.idnumber, gin.itemname) AS gradeidnumber
+              FROM {grade_import_values} giv
+              JOIN {user} u
+                   ON giv.userid = u.id
+              LEFT JOIN {grade_items} gi
+                        ON gi.id = giv.itemid
+              LEFT JOIN {grade_import_newitem} gin
+                        ON gin.id = giv.newgradeitem
+              LEFT JOIN ($enrolledsql) je
+                        ON je.id = u.id
+              LEFT JOIN {role_assignments} ra
+                        ON (giv.userid = ra.userid AND ra.roleid $gradebookrolessql AND ra.contextid $relatedctxcondition)
+             WHERE giv.importcode = :importcode
+                   AND (ra.id IS NULL OR je.id IS NULL)
+          ORDER BY gradeidnumber, u.lastname, u.firstname";
+    $params = array_merge($gradebookrolesparams, $enrolledparams);
+    $params['importcode'] = $importcode;
 
     return $DB->get_records_sql($sql, $params);
 }
index d3bedfe..d76d679 100644 (file)
@@ -333,23 +333,35 @@ class grade_report_grader extends grade_report {
     public function load_users() {
         global $CFG, $DB;
 
-        list($usql, $gbrparams) = $DB->get_in_or_equal(explode(',', $this->gradebookroles), SQL_PARAMS_NAMED, 'grbr0');
+        //limit to users with a gradeable role
+        list($gradebookrolessql, $gradebookrolesparams) = $DB->get_in_or_equal(explode(',', $this->gradebookroles), SQL_PARAMS_NAMED, 'grbr0');
 
+        //limit to users with an active enrollment
+        list($enrolledsql, $enrolledparams) = get_enrolled_sql($this->context);
+
+        //fields we need from the user table
+        $userfields = user_picture::fields('u', array('idnumber'));
+
+        //if the user has clicked one of the sort asc/desc arrows
         if (is_numeric($this->sortitemid)) {
-            $params = array_merge(array('gitemid'=>$this->sortitemid), $gbrparams, $this->groupwheresql_params);
+            $params = array_merge(array('gitemid'=>$this->sortitemid), $gradebookrolesparams, $this->groupwheresql_params, $enrolledparams);
             // the MAX() magic is required in order to please PG
             $sort = "MAX(g.finalgrade) $this->sortorder";
 
-            $ufields = user_picture::fields('u', array('idnumber'));
-            $sql = "SELECT $ufields
+            $sql = "SELECT $userfields
                       FROM {user} u
-                           JOIN {role_assignments} ra ON ra.userid = u.id
-                           $this->groupsql
-                           LEFT JOIN {grade_grades} g ON (g.userid = u.id AND g.itemid = :gitemid)
-                     WHERE ra.roleid $usql AND u.deleted = 0
-                           $this->groupwheresql
+                      JOIN ($enrolledsql) je
+                           ON je.id = u.id
+                      JOIN {role_assignments} ra
+                           ON ra.userid = u.id
+                      $this->groupsql
+                      LEFT JOIN {grade_grades} g
+                                ON (g.userid = u.id AND g.itemid = :gitemid)
+                     WHERE ra.roleid $gradebookrolessql
+                           AND u.deleted = 0
                            AND ra.contextid ".get_related_contexts_string($this->context)."
-                  GROUP BY $ufields
+                           $this->groupwheresql
+                  GROUP BY $userfields
                   ORDER BY $sort";
 
         } else {
@@ -363,16 +375,19 @@ class grade_report_grader extends grade_report {
                     $sort = "u.idnumber $this->sortorder"; break;
             }
 
-            $params = array_merge($gbrparams, $this->groupwheresql_params);
+            $params = array_merge($gradebookrolesparams, $this->groupwheresql_params, $enrolledparams);
 
-            $userfields = user_picture::fields('u', array('idnumber'));
             $sql = "SELECT DISTINCT $userfields
                       FROM {user} u
-                           JOIN {role_assignments} ra ON u.id = ra.userid
+                      JOIN ($enrolledsql) je
+                           ON je.id = u.id
+                      JOIN {role_assignments} ra
+                           ON u.id = ra.userid
                            $this->groupsql
-                     WHERE ra.roleid $usql AND u.deleted = 0
-                           $this->groupwheresql
+                     WHERE ra.roleid $gradebookrolessql
+                           AND u.deleted = 0
                            AND ra.contextid ".get_related_contexts_string($this->context)."
+                           $this->groupwheresql
                   ORDER BY $sort";
         }
 
@@ -1269,20 +1284,29 @@ class grade_report_grader extends grade_report {
 
         $totalcount = $this->get_numusers($grouponly);
 
-        list($usql, $rolesparams) = $DB->get_in_or_equal(explode(',', $this->gradebookroles), SQL_PARAMS_NAMED, 'grbr0');
+        //limit to users with a gradeable role
+        list($gradebookrolessql, $gradebookrolesparams) = $DB->get_in_or_equal(explode(',', $this->gradebookroles), SQL_PARAMS_NAMED, 'grbr0');
+
+        //limit to users with an active enrollment
+        list($enrolledsql, $enrolledparams) = get_enrolled_sql($this->context);
 
         if ($showaverages) {
-            $params = array_merge(array('courseid'=>$this->courseid), $rolesparams, $groupwheresqlparams);
+            $params = array_merge(array('courseid'=>$this->courseid), $gradebookrolesparams, $enrolledparams, $groupwheresqlparams);
 
             // find sums of all grade items in course
             $SQL = "SELECT g.itemid, SUM(g.finalgrade) AS sum
                       FROM {grade_items} gi
-                           JOIN {grade_grades} g      ON g.itemid = gi.id
-                           JOIN {user} u              ON u.id = g.userid
-                           JOIN {role_assignments} ra ON ra.userid = u.id
-                           $groupsql
+                      JOIN {grade_grades} g
+                           ON g.itemid = gi.id
+                      JOIN {user} u
+                           ON u.id = g.userid
+                      JOIN ($enrolledsql) je
+                           ON je.id = u.id
+                      JOIN {role_assignments} ra
+                           ON ra.userid = u.id
+                      $groupsql
                      WHERE gi.courseid = :courseid
-                           AND ra.roleid $usql
+                           AND ra.roleid $gradebookrolessql
                            AND ra.contextid ".get_related_contexts_string($this->context)."
                            AND g.finalgrade IS NOT NULL
                            $groupwheresql
@@ -1296,15 +1320,18 @@ class grade_report_grader extends grade_report {
 
             // MDL-10875 Empty grades must be evaluated as grademin, NOT always 0
             // This query returns a count of ungraded grades (NULL finalgrade OR no matching record in grade_grades table)
-            $params = array_merge(array('courseid'=>$this->courseid), $rolesparams, $groupwheresqlparams);
             $SQL = "SELECT gi.id, COUNT(u.id) AS count
                       FROM {grade_items} gi
-                           CROSS JOIN {user} u
-                           JOIN {role_assignments} ra        ON ra.userid = u.id
-                           LEFT OUTER JOIN  {grade_grades} g ON (g.itemid = gi.id AND g.userid = u.id AND g.finalgrade IS NOT NULL)
-                           $groupsql
+                      CROSS JOIN {user} u
+                      JOIN ($enrolledsql) je
+                           ON je.id = u.id
+                      JOIN {role_assignments} ra
+                           ON ra.userid = u.id
+                      LEFT OUTER JOIN {grade_grades} g
+                           ON (g.itemid = gi.id AND g.userid = u.id AND g.finalgrade IS NOT NULL)
+                      $groupsql
                      WHERE gi.courseid = :courseid
-                           AND ra.roleid $usql
+                           AND ra.roleid $gradebookrolessql
                            AND ra.contextid ".get_related_contexts_string($this->context)."
                            AND g.id IS NULL
                            $groupwheresql
old mode 100755 (executable)
new mode 100644 (file)
index 45540a9..a49b89e
@@ -274,7 +274,14 @@ abstract class grade_report {
 
         $groupsql      = "";
         $groupwheresql = "";
-        list($usql, $params) = $DB->get_in_or_equal(explode(',', $this->gradebookroles), SQL_PARAMS_NAMED, 'grbr0');
+
+        //limit to users with a gradeable role
+        list($gradebookrolessql, $gradebookrolesparams) = $DB->get_in_or_equal(explode(',', $this->gradebookroles), SQL_PARAMS_NAMED, 'grbr0');
+
+        //limit to users with an active enrollment
+        list($enrolledsql, $enrolledparams) = get_enrolled_sql($this->context);
+
+        $params = array_merge($gradebookrolesparams, $enrolledparams);
 
         if ($groups) {
             $groupsql      = $this->groupsql;
@@ -284,9 +291,13 @@ abstract class grade_report {
 
         $countsql = "SELECT COUNT(DISTINCT u.id)
                        FROM {user} u
-                            JOIN {role_assignments} ra ON u.id = ra.userid
-                            $groupsql
-                      WHERE ra.roleid $usql AND u.deleted = 0
+                       JOIN ($enrolledsql) je
+                            ON je.id = u.id
+                       JOIN {role_assignments} ra
+                            ON u.id = ra.userid
+                       $groupsql
+                      WHERE ra.roleid $gradebookrolessql
+                            AND u.deleted = 0
                             $groupwheresql
                             AND ra.contextid ".get_related_contexts_string($this->context);
         return $DB->count_records_sql($countsql, $params);