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