mod/quiz/report/reportlib.php

   1 <?php
   2 define('QUIZ_REPORT_DEFAULT_PAGE_SIZE', 30);
   3 define('QUIZ_REPORT_DEFAULT_GRADING_PAGE_SIZE', 10);
   4
   5 define('QUIZ_REPORT_ATTEMPTS_ALL', 0);
   6 define('QUIZ_REPORT_ATTEMPTS_STUDENTS_WITH_NO', 1);
   7 define('QUIZ_REPORT_ATTEMPTS_STUDENTS_WITH', 2);
   8 define('QUIZ_REPORT_ATTEMPTS_ALL_STUDENTS', 3);
   9 /**
  10  * Get newest graded state or newest state for a number of attempts. Pass in the
  11  * uniqueid field from quiz_attempt table not the id. Use question_state_is_graded
  12  * function to check that the question is actually graded.
  13  */
  14 function quiz_get_newgraded_states($attemptids, $idxattemptq = true, $fields='qs.*'){
  15     global $CFG;
  16     if ($attemptids){
  17         $attemptidlist = join($attemptids, ',');
  18         $gradedstatesql = "SELECT $fields FROM " .
  19                 "{$CFG->prefix}question_sessions qns, " .
  20                 "{$CFG->prefix}question_states qs " .
  21                 "WHERE qns.attemptid IN ($attemptidlist) AND " .
  22                 "qns.newgraded = qs.id";
  23         $gradedstates = get_records_sql($gradedstatesql);
  24         if ($idxattemptq){
  25             $gradedstatesbyattempt = array();
  26             foreach ($gradedstates as $gradedstate){
  27                 if (!isset($gradedstatesbyattempt[$gradedstate->attempt])){
  28                     $gradedstatesbyattempt[$gradedstate->attempt] = array();
  29                 }
  30                 $gradedstatesbyattempt[$gradedstate->attempt][$gradedstate->question] = $gradedstate;
  31             }
  32             return $gradedstatesbyattempt;
  33         } else {
  34             return $gradedstates;
  35         }
  36     } else {
  37         return array();
  38     }
  39 }
  40
  41 function quiz_get_average_grade_for_questions($quiz, $userids){
  42     global $CFG;
  43     $qmfilter = quiz_report_qm_filter_select($quiz);
  44     $questionavgssql = "SELECT qs.question, AVG(qs.grade) FROM " .
  45             "{$CFG->prefix}question_sessions qns, " .
  46             "{$CFG->prefix}quiz_attempts qa, " .
  47             "{$CFG->prefix}question_states qs " .
  48             "WHERE qns.attemptid = qa.uniqueid AND " .
  49             "qa.quiz = {$quiz->id} AND " .
  50             ($qmfilter?$qmfilter.' AND ':'').
  51             "qa.userid IN ({$userids}) AND " .
  52             "qs.event IN (".QUESTION_EVENTS_GRADED.") AND ".
  53             "qns.newgraded = qs.id GROUP BY qs.question";
  54     return get_records_sql_menu($questionavgssql);
  55 }
  56
  57 function quiz_get_total_qas_graded_and_ungraded($quiz, $questionids, $userids){
  58     global $CFG;
  59     $sql = "SELECT qs.question, COUNT(1) AS totalattempts, " .
  60             "SUM(CASE WHEN (qs.event IN (".QUESTION_EVENTS_GRADED.")) THEN 1 ELSE 0 END) AS gradedattempts " .
  61             "FROM " .
  62             "{$CFG->prefix}quiz_attempts qa, " .
  63             "{$CFG->prefix}question_sessions qns, " .
  64             "{$CFG->prefix}question_states qs " .
  65             "WHERE " .
  66             "qa.quiz = {$quiz->id} AND " .
  67             "qa.userid IN ({$userids}) AND " .
  68             "qns.attemptid = qa.uniqueid AND " .
  69             "qns.newgraded = qs.id AND " .
  70             "qs.question IN ({$questionids}) " .
  71             "GROUP BY qs.question";
  72     return get_records_sql($sql);
  73 }
  74
  75 function quiz_format_average_grade_for_questions($avggradebyq, $questions, $quiz, $download){
  76     $row = array();
  77     if (!$avggradebyq){
  78         $avggradebyq = array();
  79     }
  80     foreach(array_keys($questions) as $questionid) {
  81         if (isset($avggradebyq[$questionid])){
  82             $grade = $avggradebyq[$questionid];
  83             $grade = quiz_rescale_grade($grade, $quiz);
  84         } else {
  85             $grade = '--';
  86         }
  87         if (!$download) {
  88             $grade = $grade.'/'.quiz_rescale_grade($questions[$questionid]->grade, $quiz);
  89         }
  90         $row['qsgrade'.$questionid]= $grade;
  91     }
  92     return $row;
  93 }
  94 /**
  95  * Load the question data necessary in the reports.
  96  * - Remove description questions.
  97  * - Order questions in order that they are in the quiz
  98  * - Add question numbers.
  99  * - Add grade from quiz_questions_instance
 100  */
 101 function quiz_report_load_questions($quiz){
 102     global $CFG;
 103     $questionlist = quiz_questions_in_quiz($quiz->questions);
 104     //In fact in most cases the id IN $questionlist below is redundant
 105     //since we are also doing a JOIN on the qqi table. But will leave it in
 106     //since this double check will probably do no harm.
 107     if (!$questions = get_records_sql("SELECT q.*, qqi.grade " .
 108             "FROM {$CFG->prefix}question q, " .
 109             "{$CFG->prefix}quiz_question_instances qqi " .
 110             "WHERE q.id IN ($questionlist) AND " .
 111             "qqi.question = q.id AND " .
 112             "qqi.quiz =".$quiz->id)) {
 113         print_error('No questions found');
 114     }
 115     //Now we have an array of questions from a quiz we work out there question nos and remove
 116     //questions with zero length ie. description questions etc.
 117     //also put questions in order.
 118     $number = 1;
 119     $realquestions = array();
 120     $questionids = explode(',', $questionlist);
 121     foreach ($questionids as $id) {
 122         if ($questions[$id]->length) {
 123             // Ignore questions of zero length
 124             $realquestions[$id] = $questions[$id];
 125             $realquestions[$id]->number = $number;
 126             $number += $questions[$id]->length;
 127         }
 128     }
 129     return $realquestions;
 130 }
 131 /**
 132  * Given the quiz grading method return sub select sql to find the id of the
 133  * one attempt that will be graded for each user. Or return
 134  * empty string if all attempts contribute to final grade.
 135  */
 136 function quiz_report_qm_filter_select($quiz){
 137     global $CFG;
 138     if ($quiz->attempts == 1) {//only one attempt allowed on this quiz
 139         return '';
 140     }
 141     $useridsql = 'qa.userid';
 142     $quizidsql = 'qa.quiz';
 143     $qmfilterattempts = true;
 144     switch ($quiz->grademethod) {
 145     case QUIZ_GRADEHIGHEST :
 146         $field1 = 'sumgrades';
 147         $field2 = 'timestart';
 148         $aggregator1 = 'MAX';
 149         $aggregator2 = 'MIN';
 150         $qmselectpossible = true;
 151         break;
 152     case QUIZ_GRADEAVERAGE :
 153         $qmselectpossible = false;
 154         break;
 155     case QUIZ_ATTEMPTFIRST :
 156         $field1 = 'timestart';
 157         $field2 = 'id';
 158         $aggregator1 = 'MIN';
 159         $aggregator2 = 'MIN';
 160         $qmselectpossible = true;
 161         break;
 162     case QUIZ_ATTEMPTLAST :
 163         $field1 = 'timestart';
 164         $field2 = 'id';
 165         $aggregator1 = 'MAX';
 166         $aggregator2 = 'MAX';
 167         $qmselectpossible = true;
 168         break;
 169     }
 170     if ($qmselectpossible){
 171         $qmselect = "qa.$field1 = (SELECT $aggregator1(qa2.$field1) FROM {$CFG->prefix}quiz_attempts qa2 WHERE qa2.quiz = $quizidsql AND qa2.userid = $useridsql) AND " .
 172                     "qa.$field2 = (SELECT $aggregator2(qa3.$field2) FROM {$CFG->prefix}quiz_attempts qa3 WHERE qa3.quiz = $quizidsql AND qa3.userid = $useridsql AND qa3.$field1 = qa.$field1)";
 173     } else {
 174         $qmselect = '';
 175     }
 176
 177     return $qmselect;
 178 }
 179
 180
 181 function quiz_report_grade_bands($bandwidth, $bands, $quizid, $useridlist=''){
 182     global $CFG;
 183     $sql = "SELECT
 184         FLOOR(qg.grade/$bandwidth) AS band,
 185         COUNT(1) AS num
 186     FROM
 187         {$CFG->prefix}quiz_grades qg,
 188         {$CFG->prefix}quiz q
 189     WHERE qg.quiz = q.id AND qg.quiz = $quizid
 190             ".($useridlist?"AND qg.userid IN ($useridlist) ":'')."
 191     GROUP BY band
 192     ORDER BY band";
 193     if (!$data = get_records_sql_menu($sql)){
 194         $data= array();
 195     }
 196     //need to create array elements with values 0 at indexes where there is no element
 197     $data =  $data + array_fill(0, $bands+1, 0);
 198     ksort($data);
 199     //place the maximum (prefect grade) into the last band i.e. make last
 200     //band for example 9 <= g <=10 (where 10 is the perfect grade) rather than
 201     //just 9 <= g <10.
 202     $data[$bands-1] += $data[$bands];
 203     unset($data[$bands]);
 204     return $data;
 205
 206 }
 207 function quiz_report_highlighting_grading_method($quiz, $qmsubselect, $qmfilter){
 208     if ($quiz->attempts == 1) {//only one attempt allowed on this quiz
 209         return "<p>".get_string('onlyoneattemptallowed', "quiz_overview")."</p>";
 210     } else if (!$qmsubselect){
 211         return "<p>".get_string('allattemptscontributetograde', "quiz_overview")."</p>";
 212     } else if ($qmfilter){
 213         return "<p>".get_string('showinggraded', "quiz_overview")."</p>";
 214     }else {
 215         return "<p>".get_string('showinggradedandungraded', "quiz_overview",
 216                 ('<span class="highlight">'.quiz_get_grading_option_name($quiz->grademethod).'</span>'))."</p>";
 217     }
 218 }
 219
 220
 221 /**
 222  * Get the feedback text for a grade on this quiz. The feedback is
 223  * processed ready for display.
 224  *
 225  * @param float $grade a grade on this quiz.
 226  * @param integer $quizid the id of the quiz object.
 227  * @return string the comment that corresponds to this grade (empty string if there is not one.
 228  */
 229 function quiz_report_feedback_for_grade($grade, $quizid) {
 230     static $feedbackcache = array();
 231     if (!isset($feedbackcache[$quizid])){
 232         $feedbackcache[$quizid] = get_records('quiz_feedback', 'quizid', $quizid);
 233     }
 234     $feedbacks = $feedbackcache[$quizid];
 235     $feedbacktext = '';
 236     foreach ($feedbacks as $feedback) {
 237         if ($feedback->mingrade <= $grade && $grade < $feedback->maxgrade){
 238             $feedbacktext = $feedback->feedbacktext;
 239             break;
 240         }
 241     }
 242
 243     // Clean the text, ready for display.
 244     $formatoptions = new stdClass;
 245     $formatoptions->noclean = true;
 246     $feedbacktext = format_text($feedbacktext, FORMAT_MOODLE, $formatoptions);
 247
 248     return $feedbacktext;
 249 }
 250 ?>