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