HTML_EDITOR, REPOSITORY/MDL-16650
[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 " .
8b92c1e3 29 "qns.newest = 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 ".
8b92c1e3 38 "qns.newest = qs.id";
67d4dfb8 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}
869309b8 49/**
50 * Takes an array of objects and constructs a multidimensional array keyed by
51 * the keys it finds on the object.
52 * @param array $datum an array of objects with properties on the object
53 * including the keys passed as the next param.
54 * @param array $keys Array of strings with the names of the properties on the
55 * objects in datum that you want to index the multidimensional array by.
56 * @param boolean $keysunique If there is not only one object for each
57 * combination of keys you are using you should set $keysunique to true.
58 * Otherwise all the object will be added to a zero based array. So the array
59 * returned will have count($keys) + 1 indexs.
60 * @return array multidimensional array properly indexed.
61 */
62function quiz_report_index_by_keys($datum, $keys, $keysunique=true){
98f38217 63 if (!$datum){
64 return $datum;
65 }
66 $key = array_shift($keys);
67 $datumkeyed = array();
68 foreach ($datum as $data){
869309b8 69 if ($keys || !$keysunique){
98f38217 70 $datumkeyed[$data->{$key}][]= $data;
71 } else {
72 $datumkeyed[$data->{$key}]= $data;
73 }
74 }
75 if ($keys){
76 foreach ($datumkeyed as $datakey => $datakeyed){
869309b8 77 $datumkeyed[$datakey] = quiz_report_index_by_keys($datakeyed, $keys, $keysunique);
98f38217 78 }
79 }
80 return $datumkeyed;
81}
869309b8 82function quiz_report_unindex($datum){
83 if (!$datum){
84 return $datum;
85 }
86 $datumunkeyed = array();
87 foreach ($datum as $value){
88 if (is_array($value)){
89 $datumunkeyed = array_merge($datumunkeyed, quiz_report_unindex($value));
90 } else {
91 $datumunkeyed[] = $value;
92 }
93 }
94 return $datumunkeyed;
95}
98f38217 96function quiz_get_regraded_qs($attemptidssql, $limitfrom=0, $limitnum=0){
97 global $CFG, $DB;
98 if ($attemptidssql && is_array($attemptidssql)){
99 list($asql, $params) = $DB->get_in_or_equal($attemptidssql);
100 $regradedqsql = "SELECT qqr.* FROM " .
101 "{quiz_question_regrade} qqr " .
102 "WHERE qqr.attemptid $asql";
103 $regradedqs = $DB->get_records_sql($regradedqsql, $params, $limitfrom, $limitnum);
104 } else if ($attemptidssql && is_object($attemptidssql)){
105 $regradedqsql = "SELECT qqr.* FROM " .
106 $attemptidssql->from.", ".
107 "{quiz_question_regrade} qqr " .
108 "WHERE qqr.attemptid = qa.uniqueid AND " .
109 $attemptidssql->where;
110 $regradedqs = $DB->get_records_sql($regradedqsql, $attemptidssql->params, $limitfrom, $limitnum);
111 } else {
112 return array();
113 }
114 return quiz_report_index_by_keys($regradedqs, array('attemptid', 'questionid'));
115}
aad5b0fc 116function quiz_get_average_grade_for_questions($quiz, $userids){
c6307ef2 117 global $CFG, $DB;
334edb71 118 $qmfilter = quiz_report_qm_filter_select($quiz);
119 list($usql, $params) = $DB->get_in_or_equal($userids);
120 $params[] = $quiz->id;
121 $questionavgssql = "SELECT qns.questionid, AVG(qs.grade) FROM
122 {quiz_attempts} qa
123 LEFT JOIN {question_sessions} qns ON (qns.attemptid = qa.uniqueid)
124 LEFT JOIN {question_states} qs ON (qns.newgraded = qs.id AND qs.event IN (".QUESTION_EVENTS_GRADED."))
125 WHERE " .
126 "($qmfilter) AND " .
127 "qa.userid $usql AND " .
128 "qa.quiz = ? ".
17312b83 129 "GROUP BY qns.questionid";
9cf4a18b 130 return $DB->get_records_sql_menu($questionavgssql, $params);
aad5b0fc 131}
720be6f2 132
133function quiz_get_total_qas_graded_and_ungraded($quiz, $questionids, $userids){
9cf4a18b 134 global $CFG, $DB;
135 $params = array($quiz->id);
8673a566 136 list($u_sql, $u_params) = $DB->get_in_or_equal($userids);
137 list($q_sql, $q_params) = $DB->get_in_or_equal($questionids);
8b92c1e3 138
9cf4a18b 139 $params = array_merge($params, $u_params, $q_params);
140 $sql = "SELECT qs.question, COUNT(1) AS totalattempts,
8b92c1e3 141 SUM(CASE WHEN (qs.event IN(".QUESTION_EVENTS_GRADED.")) THEN 1 ELSE 0 END) AS gradedattempts
9cf4a18b 142 FROM
143 {quiz_attempts} qa,
144 {question_sessions} qns,
145 {question_states} qs
146 WHERE
147 qa.quiz = ? AND
148 qa.userid $u_sql AND
149 qns.attemptid = qa.uniqueid AND
8b92c1e3 150 qns.newest = qs.id AND
151 qs.event IN (".QUESTION_EVENTS_CLOSED_OR_GRADED.") AND
9cf4a18b 152 qs.question $q_sql
153 GROUP BY qs.question";
154 return $DB->get_records_sql($sql, $params);
720be6f2 155}
156
aad5b0fc 157function quiz_format_average_grade_for_questions($avggradebyq, $questions, $quiz, $download){
158 $row = array();
159 if (!$avggradebyq){
160 $avggradebyq = array();
161 }
162 foreach(array_keys($questions) as $questionid) {
163 if (isset($avggradebyq[$questionid])){
164 $grade = $avggradebyq[$questionid];
165 $grade = quiz_rescale_grade($grade, $quiz);
166 } else {
167 $grade = '--';
168 }
ca359748 169 $row['qsgrade'.$questionid]= $grade;
aad5b0fc 170 }
171 return $row;
172}
2badf2e6 173/**
174 * Load the question data necessary in the reports.
175 * - Remove description questions.
176 * - Order questions in order that they are in the quiz
177 * - Add question numbers.
178 * - Add grade from quiz_questions_instance
179 */
180function quiz_report_load_questions($quiz){
9cf4a18b 181 global $CFG, $DB;
2badf2e6 182 $questionlist = quiz_questions_in_quiz($quiz->questions);
9cf4a18b 183 //In fact in most cases the id IN $questionlist below is redundant
2badf2e6 184 //since we are also doing a JOIN on the qqi table. But will leave it in
185 //since this double check will probably do no harm.
9cf4a18b 186 list($usql, $params) = $DB->get_in_or_equal(explode(',', $questionlist));
187 $params[] = $quiz->id;
6559096f 188 if (!$questions = $DB->get_records_sql("SELECT q.*, qqi.grade AS maxgrade
9cf4a18b 189 FROM {question} q,
190 {quiz_question_instances} qqi
191 WHERE q.id $usql AND
192 qqi.question = q.id AND
193 qqi.quiz = ?", $params)) {
a5047f15 194 print_error('noquestionsfound', 'quiz');
2badf2e6 195 }
9cf4a18b 196 //Now we have an array of questions from a quiz we work out there question nos and remove
2badf2e6 197 //questions with zero length ie. description questions etc.
198 //also put questions in order.
199 $number = 1;
200 $realquestions = array();
201 $questionids = explode(',', $questionlist);
202 foreach ($questionids as $id) {
203 if ($questions[$id]->length) {
204 // Ignore questions of zero length
205 $realquestions[$id] = $questions[$id];
206 $realquestions[$id]->number = $number;
207 $number += $questions[$id]->length;
208 }
209 }
210 return $realquestions;
211}
4469159e 212/**
213 * Given the quiz grading method return sub select sql to find the id of the
9cf4a18b 214 * one attempt that will be graded for each user. Or return
4469159e 215 * empty string if all attempts contribute to final grade.
216 */
334edb71 217function quiz_report_qm_filter_select($quiz){
b621e1a0 218 if ($quiz->attempts == 1) {//only one attempt allowed on this quiz
219 return '';
220 }
334edb71 221 $useridsql = 'qa.userid';
222 $quizidsql = 'qa.quiz';
4469159e 223 $qmfilterattempts = true;
b621e1a0 224 switch ($quiz->grademethod) {
4469159e 225 case QUIZ_GRADEHIGHEST :
334edb71 226 $field1 = 'sumgrades';
227 $field2 = 'timestart';
228 $aggregator1 = 'MAX';
229 $aggregator2 = 'MIN';
230 $qmselectpossible = true;
4469159e 231 break;
232 case QUIZ_GRADEAVERAGE :
334edb71 233 $qmselectpossible = false;
4469159e 234 break;
235 case QUIZ_ATTEMPTFIRST :
334edb71 236 $field1 = 'timestart';
237 $field2 = 'id';
238 $aggregator1 = 'MIN';
239 $aggregator2 = 'MIN';
240 $qmselectpossible = true;
4469159e 241 break;
242 case QUIZ_ATTEMPTLAST :
334edb71 243 $field1 = 'timestart';
244 $field2 = 'id';
245 $aggregator1 = 'MAX';
246 $aggregator2 = 'MAX';
247 $qmselectpossible = true;
4469159e 248 break;
249 }
334edb71 250 if ($qmselectpossible){
251 $qmselect = "qa.$field1 = (SELECT $aggregator1(qa2.$field1) FROM {quiz_attempts} qa2 WHERE qa2.quiz = $quizidsql AND qa2.userid = $useridsql) AND " .
252 "qa.$field2 = (SELECT $aggregator2(qa3.$field2) FROM {quiz_attempts} qa3 WHERE qa3.quiz = $quizidsql AND qa3.userid = $useridsql AND qa3.$field1 = qa.$field1)";
4469159e 253 } else {
334edb71 254 $qmselect = '';
4469159e 255 }
334edb71 256
257 return $qmselect;
4469159e 258}
8b87ab00 259
8b2f8253 260function quiz_report_grade_bands($bandwidth, $bands, $quizid, $userids=array()){
c6307ef2 261 global $CFG, $DB;
8b2f8253 262 if ($userids){
263 list($usql, $params) = $DB->get_in_or_equal($userids);
264 } else {
265 $usql ='';
266 $params = array();
267 }
8b87ab00 268 $sql = "SELECT
b392a56c 269 FLOOR(qg.grade/$bandwidth) AS band,
8b87ab00 270 COUNT(1) AS num
271 FROM
c6307ef2 272 {quiz_grades} qg, {quiz} q
8b2f8253 273 WHERE qg.quiz = q.id " .
274 ($usql?"AND qg.userid $usql ":'') .
275 "AND qg.quiz = ?
78268862 276 GROUP BY FLOOR(qg.grade/$bandwidth)
8b87ab00 277 ORDER BY band";
8673a566 278 $params[] = $quizid;
9cf4a18b 279 $data = $DB->get_records_sql_menu($sql, $params);
8b87ab00 280 //need to create array elements with values 0 at indexes where there is no element
a5686531 281 $data = $data + array_fill(0, $bands+1, 0);
8b87ab00 282 ksort($data);
9cf4a18b 283 //place the maximum (prefect grade) into the last band i.e. make last
a5686531 284 //band for example 9 <= g <=10 (where 10 is the perfect grade) rather than
285 //just 9 <= g <10.
286 $data[$bands-1] += $data[$bands];
287 unset($data[$bands]);
8b87ab00 288 return $data;
289}
04299b1f 290function quiz_report_highlighting_grading_method($quiz, $qmsubselect, $qmfilter){
b621e1a0 291 if ($quiz->attempts == 1) {//only one attempt allowed on this quiz
292 return "<p>".get_string('onlyoneattemptallowed', "quiz_overview")."</p>";
293 } else if (!$qmsubselect){
294 return "<p>".get_string('allattemptscontributetograde', "quiz_overview")."</p>";
295 } else if ($qmfilter){
296 return "<p>".get_string('showinggraded', "quiz_overview")."</p>";
297 }else {
04299b1f 298 return "<p>".get_string('showinggradedandungraded', "quiz_overview",
299 ('<span class="highlight">'.quiz_get_grading_option_name($quiz->grademethod).'</span>'))."</p>";
b621e1a0 300 }
301}
aad5b0fc 302
303
304/**
305 * Get the feedback text for a grade on this quiz. The feedback is
306 * processed ready for display.
307 *
308 * @param float $grade a grade on this quiz.
309 * @param integer $quizid the id of the quiz object.
310 * @return string the comment that corresponds to this grade (empty string if there is not one.
311 */
312function quiz_report_feedback_for_grade($grade, $quizid) {
9cf4a18b 313 global $DB;
aad5b0fc 314 static $feedbackcache = array();
315 if (!isset($feedbackcache[$quizid])){
9cf4a18b 316 $feedbackcache[$quizid] = $DB->get_records('quiz_feedback', array('quizid' => $quizid));
aad5b0fc 317 }
318 $feedbacks = $feedbackcache[$quizid];
319 $feedbacktext = '';
320 foreach ($feedbacks as $feedback) {
321 if ($feedback->mingrade <= $grade && $grade < $feedback->maxgrade){
322 $feedbacktext = $feedback->feedbacktext;
323 break;
324 }
325 }
326
327 // Clean the text, ready for display.
328 $formatoptions = new stdClass;
329 $formatoptions->noclean = true;
330 $feedbacktext = format_text($feedbacktext, FORMAT_MOODLE, $formatoptions);
331
332 return $feedbacktext;
333}
0c1c764e 334
335function quiz_report_scale_sumgrades_as_percentage($rawgrade, $quiz, $round = true) {
869309b8 336 if ($quiz->sumgrades != 0) {
0c1c764e 337 $grade = $rawgrade * 100 / $quiz->sumgrades;
338 if ($round) {
f88fb62c 339 $grade = quiz_format_grade($quiz, $grade);
0c1c764e 340 }
341 } else {
869309b8 342 return '';
0c1c764e 343 }
43ec99aa 344 return $grade.'%';
0c1c764e 345}
7d4d5cb9 346?>