MDL-6751 improve the usability of the page a bit.
[moodle.git] / mod / quiz / report / reportlib.php
CommitLineData
f33c438e 1<?php
530d9866 2require_once($CFG->dirroot . '/mod/quiz/lib.php');
3
f33c438e 4define('QUIZ_REPORT_DEFAULT_PAGE_SIZE', 30);
f17a34b5 5define('QUIZ_REPORT_DEFAULT_GRADING_PAGE_SIZE', 10);
4469159e 6
7define('QUIZ_REPORT_ATTEMPTS_ALL', 0);
8define('QUIZ_REPORT_ATTEMPTS_STUDENTS_WITH_NO', 1);
9define('QUIZ_REPORT_ATTEMPTS_STUDENTS_WITH', 2);
10define('QUIZ_REPORT_ATTEMPTS_ALL_STUDENTS', 3);
2badf2e6 11/**
9cf4a18b 12 * Get newest graded state or newest state for a number of attempts. Pass in the
2badf2e6 13 * uniqueid field from quiz_attempt table not the id. Use question_state_is_graded
14 * function to check that the question is actually graded.
67d4dfb8 15 * @param array attemptidssql either an array of attemptids with numerical keys
16 * or an object with properties from, where and params.
17 * @param boolean idxattemptq true if a multidimensional array should be
18 * constructed with keys indexing array first by attempt and then by question
19 * id.
2badf2e6 20 */
67d4dfb8 21function quiz_get_newgraded_states($attemptidssql, $idxattemptq = true, $fields='qs.*'){
9cf4a18b 22 global $CFG, $DB;
67d4dfb8 23 if ($attemptidssql && is_array($attemptidssql)){
24 list($usql, $params) = $DB->get_in_or_equal($attemptidssql);
181608b7 25 $gradedstatesql = "SELECT $fields FROM " .
9cf4a18b 26 "{question_sessions} qns, " .
27 "{question_states} qs " .
28 "WHERE qns.attemptid $usql AND " .
0217f932 29 "qns.newgraded = qs.id";
9cf4a18b 30 $gradedstates = $DB->get_records_sql($gradedstatesql, $params);
67d4dfb8 31 } else if ($attemptidssql && is_object($attemptidssql)){
32 $gradedstatesql = "SELECT $fields FROM " .
33 $attemptidssql->from.",".
34 "{question_sessions} qns, " .
35 "{question_states} qs " .
36 "WHERE qns.attemptid = qa.uniqueid AND " .
37 $attemptidssql->where." AND ".
38 "qns.newgraded = qs.id";
39 $gradedstates = $DB->get_records_sql($gradedstatesql, $attemptidssql->params);
40 } else {
41 return array();
42 }
43 if ($idxattemptq){
98f38217 44 return quiz_report_index_by_keys($gradedstates, array('attempt', 'question'));
2badf2e6 45 } else {
67d4dfb8 46 return $gradedstates;
2badf2e6 47 }
48}
98f38217 49function quiz_report_index_by_keys($datum, $keys){
50 if (!$datum){
51 return $datum;
52 }
53 $key = array_shift($keys);
54 $datumkeyed = array();
55 foreach ($datum as $data){
56 if ($keys){
57 $datumkeyed[$data->{$key}][]= $data;
58 } else {
59 $datumkeyed[$data->{$key}]= $data;
60 }
61 }
62 if ($keys){
63 foreach ($datumkeyed as $datakey => $datakeyed){
64 $datumkeyed[$datakey] = quiz_report_index_by_keys($datakeyed, $keys);
65 }
66 }
67 return $datumkeyed;
68}
aad5b0fc 69
98f38217 70function quiz_get_regraded_qs($attemptidssql, $limitfrom=0, $limitnum=0){
71 global $CFG, $DB;
72 if ($attemptidssql && is_array($attemptidssql)){
73 list($asql, $params) = $DB->get_in_or_equal($attemptidssql);
74 $regradedqsql = "SELECT qqr.* FROM " .
75 "{quiz_question_regrade} qqr " .
76 "WHERE qqr.attemptid $asql";
77 $regradedqs = $DB->get_records_sql($regradedqsql, $params, $limitfrom, $limitnum);
78 } else if ($attemptidssql && is_object($attemptidssql)){
79 $regradedqsql = "SELECT qqr.* FROM " .
80 $attemptidssql->from.", ".
81 "{quiz_question_regrade} qqr " .
82 "WHERE qqr.attemptid = qa.uniqueid AND " .
83 $attemptidssql->where;
84 $regradedqs = $DB->get_records_sql($regradedqsql, $attemptidssql->params, $limitfrom, $limitnum);
85 } else {
86 return array();
87 }
88 return quiz_report_index_by_keys($regradedqs, array('attemptid', 'questionid'));
89}
aad5b0fc 90function quiz_get_average_grade_for_questions($quiz, $userids){
c6307ef2 91 global $CFG, $DB;
334edb71 92 $qmfilter = quiz_report_qm_filter_select($quiz);
93 list($usql, $params) = $DB->get_in_or_equal($userids);
94 $params[] = $quiz->id;
95 $questionavgssql = "SELECT qns.questionid, AVG(qs.grade) FROM
96 {quiz_attempts} qa
97 LEFT JOIN {question_sessions} qns ON (qns.attemptid = qa.uniqueid)
98 LEFT JOIN {question_states} qs ON (qns.newgraded = qs.id AND qs.event IN (".QUESTION_EVENTS_GRADED."))
99 WHERE " .
100 "($qmfilter) AND " .
101 "qa.userid $usql AND " .
102 "qa.quiz = ? ".
17312b83 103 "GROUP BY qns.questionid";
9cf4a18b 104 return $DB->get_records_sql_menu($questionavgssql, $params);
aad5b0fc 105}
720be6f2 106
107function quiz_get_total_qas_graded_and_ungraded($quiz, $questionids, $userids){
9cf4a18b 108 global $CFG, $DB;
109 $params = array($quiz->id);
8673a566 110 list($u_sql, $u_params) = $DB->get_in_or_equal($userids);
111 list($q_sql, $q_params) = $DB->get_in_or_equal($questionids);
9cf4a18b 112
113 $params = array_merge($params, $u_params, $q_params);
114 $sql = "SELECT qs.question, COUNT(1) AS totalattempts,
fb7396a1 115 SUM(CASE WHEN (qs.event IN (".QUESTION_EVENTS_GRADED.")) THEN 1 ELSE 0 END) AS gradedattempts
9cf4a18b 116 FROM
117 {quiz_attempts} qa,
118 {question_sessions} qns,
119 {question_states} qs
120 WHERE
121 qa.quiz = ? AND
122 qa.userid $u_sql AND
123 qns.attemptid = qa.uniqueid AND
124 qns.newgraded = qs.id AND
125 qs.question $q_sql
126 GROUP BY qs.question";
127 return $DB->get_records_sql($sql, $params);
720be6f2 128}
129
aad5b0fc 130function quiz_format_average_grade_for_questions($avggradebyq, $questions, $quiz, $download){
131 $row = array();
132 if (!$avggradebyq){
133 $avggradebyq = array();
134 }
135 foreach(array_keys($questions) as $questionid) {
136 if (isset($avggradebyq[$questionid])){
137 $grade = $avggradebyq[$questionid];
138 $grade = quiz_rescale_grade($grade, $quiz);
139 } else {
140 $grade = '--';
141 }
ca359748 142 $row['qsgrade'.$questionid]= $grade;
aad5b0fc 143 }
144 return $row;
145}
2badf2e6 146/**
147 * Load the question data necessary in the reports.
148 * - Remove description questions.
149 * - Order questions in order that they are in the quiz
150 * - Add question numbers.
151 * - Add grade from quiz_questions_instance
152 */
153function quiz_report_load_questions($quiz){
9cf4a18b 154 global $CFG, $DB;
2badf2e6 155 $questionlist = quiz_questions_in_quiz($quiz->questions);
9cf4a18b 156 //In fact in most cases the id IN $questionlist below is redundant
2badf2e6 157 //since we are also doing a JOIN on the qqi table. But will leave it in
158 //since this double check will probably do no harm.
9cf4a18b 159 list($usql, $params) = $DB->get_in_or_equal(explode(',', $questionlist));
160 $params[] = $quiz->id;
6559096f 161 if (!$questions = $DB->get_records_sql("SELECT q.*, qqi.grade AS maxgrade
9cf4a18b 162 FROM {question} q,
163 {quiz_question_instances} qqi
164 WHERE q.id $usql AND
165 qqi.question = q.id AND
166 qqi.quiz = ?", $params)) {
a5047f15 167 print_error('noquestionsfound', 'quiz');
2badf2e6 168 }
9cf4a18b 169 //Now we have an array of questions from a quiz we work out there question nos and remove
2badf2e6 170 //questions with zero length ie. description questions etc.
171 //also put questions in order.
172 $number = 1;
173 $realquestions = array();
174 $questionids = explode(',', $questionlist);
175 foreach ($questionids as $id) {
176 if ($questions[$id]->length) {
177 // Ignore questions of zero length
178 $realquestions[$id] = $questions[$id];
179 $realquestions[$id]->number = $number;
180 $number += $questions[$id]->length;
181 }
182 }
183 return $realquestions;
184}
4469159e 185/**
186 * Given the quiz grading method return sub select sql to find the id of the
9cf4a18b 187 * one attempt that will be graded for each user. Or return
4469159e 188 * empty string if all attempts contribute to final grade.
189 */
334edb71 190function quiz_report_qm_filter_select($quiz){
b621e1a0 191 if ($quiz->attempts == 1) {//only one attempt allowed on this quiz
192 return '';
193 }
334edb71 194 $useridsql = 'qa.userid';
195 $quizidsql = 'qa.quiz';
4469159e 196 $qmfilterattempts = true;
b621e1a0 197 switch ($quiz->grademethod) {
4469159e 198 case QUIZ_GRADEHIGHEST :
334edb71 199 $field1 = 'sumgrades';
200 $field2 = 'timestart';
201 $aggregator1 = 'MAX';
202 $aggregator2 = 'MIN';
203 $qmselectpossible = true;
4469159e 204 break;
205 case QUIZ_GRADEAVERAGE :
334edb71 206 $qmselectpossible = false;
4469159e 207 break;
208 case QUIZ_ATTEMPTFIRST :
334edb71 209 $field1 = 'timestart';
210 $field2 = 'id';
211 $aggregator1 = 'MIN';
212 $aggregator2 = 'MIN';
213 $qmselectpossible = true;
4469159e 214 break;
215 case QUIZ_ATTEMPTLAST :
334edb71 216 $field1 = 'timestart';
217 $field2 = 'id';
218 $aggregator1 = 'MAX';
219 $aggregator2 = 'MAX';
220 $qmselectpossible = true;
4469159e 221 break;
222 }
334edb71 223 if ($qmselectpossible){
224 $qmselect = "qa.$field1 = (SELECT $aggregator1(qa2.$field1) FROM {quiz_attempts} qa2 WHERE qa2.quiz = $quizidsql AND qa2.userid = $useridsql) AND " .
225 "qa.$field2 = (SELECT $aggregator2(qa3.$field2) FROM {quiz_attempts} qa3 WHERE qa3.quiz = $quizidsql AND qa3.userid = $useridsql AND qa3.$field1 = qa.$field1)";
4469159e 226 } else {
334edb71 227 $qmselect = '';
4469159e 228 }
334edb71 229
230 return $qmselect;
4469159e 231}
8b87ab00 232
8b2f8253 233function quiz_report_grade_bands($bandwidth, $bands, $quizid, $userids=array()){
c6307ef2 234 global $CFG, $DB;
8b2f8253 235 if ($userids){
236 list($usql, $params) = $DB->get_in_or_equal($userids);
237 } else {
238 $usql ='';
239 $params = array();
240 }
8b87ab00 241 $sql = "SELECT
b392a56c 242 FLOOR(qg.grade/$bandwidth) AS band,
8b87ab00 243 COUNT(1) AS num
244 FROM
c6307ef2 245 {quiz_grades} qg, {quiz} q
8b2f8253 246 WHERE qg.quiz = q.id " .
247 ($usql?"AND qg.userid $usql ":'') .
248 "AND qg.quiz = ?
8b87ab00 249 GROUP BY band
250 ORDER BY band";
8673a566 251 $params[] = $quizid;
9cf4a18b 252 $data = $DB->get_records_sql_menu($sql, $params);
8b87ab00 253 //need to create array elements with values 0 at indexes where there is no element
a5686531 254 $data = $data + array_fill(0, $bands+1, 0);
8b87ab00 255 ksort($data);
9cf4a18b 256 //place the maximum (prefect grade) into the last band i.e. make last
a5686531 257 //band for example 9 <= g <=10 (where 10 is the perfect grade) rather than
258 //just 9 <= g <10.
259 $data[$bands-1] += $data[$bands];
260 unset($data[$bands]);
8b87ab00 261 return $data;
262}
b621e1a0 263function quiz_report_highlighting_grading_method($quiz, $qmsubselect, $qmfilter){
264 if ($quiz->attempts == 1) {//only one attempt allowed on this quiz
265 return "<p>".get_string('onlyoneattemptallowed', "quiz_overview")."</p>";
266 } else if (!$qmsubselect){
267 return "<p>".get_string('allattemptscontributetograde', "quiz_overview")."</p>";
268 } else if ($qmfilter){
269 return "<p>".get_string('showinggraded', "quiz_overview")."</p>";
270 }else {
271 return "<p>".get_string('showinggradedandungraded', "quiz_overview",
272 ('<span class="highlight">'.quiz_get_grading_option_name($quiz->grademethod).'</span>'))."</p>";
273 }
274}
aad5b0fc 275
276
277/**
278 * Get the feedback text for a grade on this quiz. The feedback is
279 * processed ready for display.
280 *
281 * @param float $grade a grade on this quiz.
282 * @param integer $quizid the id of the quiz object.
283 * @return string the comment that corresponds to this grade (empty string if there is not one.
284 */
285function quiz_report_feedback_for_grade($grade, $quizid) {
9cf4a18b 286 global $DB;
aad5b0fc 287 static $feedbackcache = array();
288 if (!isset($feedbackcache[$quizid])){
9cf4a18b 289 $feedbackcache[$quizid] = $DB->get_records('quiz_feedback', array('quizid' => $quizid));
aad5b0fc 290 }
291 $feedbacks = $feedbackcache[$quizid];
292 $feedbacktext = '';
293 foreach ($feedbacks as $feedback) {
294 if ($feedback->mingrade <= $grade && $grade < $feedback->maxgrade){
295 $feedbacktext = $feedback->feedbacktext;
296 break;
297 }
298 }
299
300 // Clean the text, ready for display.
301 $formatoptions = new stdClass;
302 $formatoptions->noclean = true;
303 $feedbacktext = format_text($feedbacktext, FORMAT_MOODLE, $formatoptions);
304
305 return $feedbacktext;
306}
0c1c764e 307
308function quiz_report_scale_sumgrades_as_percentage($rawgrade, $quiz, $round = true) {
309 if ($quiz->sumgrades) {
310 $grade = $rawgrade * 100 / $quiz->sumgrades;
311 if ($round) {
f88fb62c 312 $grade = quiz_format_grade($quiz, $grade);
0c1c764e 313 }
314 } else {
315 $grade = 0;
316 }
43ec99aa 317 return $grade.'%';
0c1c764e 318}
7d4d5cb9 319?>