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