MDL-3030 quiz overdue handling: only finished attempts in stats.
[moodle.git] / mod / quiz / report / attemptsreport_table.php
CommitLineData
f5e42695
TH
1<?php
2// This file is part of Moodle - http://moodle.org/
3//
4// Moodle is free software: you can redistribute it and/or modify
5// it under the terms of the GNU General Public License as published by
6// the Free Software Foundation, either version 3 of the License, or
7// (at your option) any later version.
8//
9// Moodle is distributed in the hope that it will be useful,
10// but WITHOUT ANY WARRANTY; without even the implied warranty of
11// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
12// GNU General Public License for more details.
13//
14// You should have received a copy of the GNU General Public License
15// along with Moodle. If not, see <http://www.gnu.org/licenses/>.
16
17/**
ac4d9157 18 * Base class for the table used by a {@link quiz_attempts_report}.
f5e42695
TH
19 *
20 * @package mod_quiz
21 * @copyright 2010 The Open University
22 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
23 */
24
25
26defined('MOODLE_INTERNAL') || die();
27
28require_once($CFG->libdir.'/tablelib.php');
29
30
31/**
ac4d9157 32 * Base class for the table used by a {@link quiz_attempts_report}.
f5e42695
TH
33 *
34 * @copyright 2010 The Open University
35 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
36 */
ac4d9157 37abstract class quiz_attempts_report_table extends table_sql {
f5e42695
TH
38 public $useridfield = 'userid';
39
40 /** @var moodle_url the URL of this report. */
41 protected $reporturl;
42
43 /** @var array the display options. */
44 protected $displayoptions;
45
46 /**
47 * @var array information about the latest step of each question.
48 * Loaded by {@link load_question_latest_steps()}, if applicable.
49 */
50 protected $lateststeps = null;
51
9d58dae3 52 /** @var object the quiz settings for the quiz we are reporting on. */
f5e42695 53 protected $quiz;
9d58dae3
TH
54
55 /** @var context the quiz context. */
f5e42695 56 protected $context;
9d58dae3
TH
57
58 /** @var string HTML fragment to select the first/best/last attempt, if appropriate. */
f5e42695 59 protected $qmsubselect;
9d58dae3
TH
60
61 /** @var object mod_quiz_attempts_report_options the options affecting this report. */
e97d60ad 62 protected $options;
9d58dae3
TH
63
64 /** @var bool whether to only display the first/best/last attempt for each student. */
f5e42695 65 protected $qmfilter;
9d58dae3
TH
66
67 /** @var int which attempts/students to include in the report.. */
f5e42695 68 protected $attemptsmode;
9d58dae3
TH
69
70 /** @var object the ids of the students in the currently selected group, if applicable. */
f5e42695 71 protected $groupstudents;
9d58dae3
TH
72
73 /** @var object the ids of the students in the course. */
f5e42695 74 protected $students;
9d58dae3
TH
75
76 /** @var object the questions that comprise this quiz.. */
f5e42695 77 protected $questions;
9d58dae3
TH
78
79 /** @var bool whether to include the column with checkboxes to select each attempt. */
f5e42695
TH
80 protected $includecheckboxes;
81
9d58dae3
TH
82 /**
83 * Constructor
84 * @param string $uniqueid
85 * @param object $quiz
86 * @param context $context
87 * @param string $qmsubselect
88 * @param mod_quiz_attempts_report_options $options
89 * @param array $groupstudents
90 * @param array $students
91 * @param array $questions
92 * @param moodle_url $reporturl
93 */
e97d60ad
TH
94 public function __construct($uniqueid, $quiz, $context, $qmsubselect,
95 mod_quiz_attempts_report_options $options, $groupstudents, $students,
96 $questions, $reporturl) {
f5e42695
TH
97 parent::__construct($uniqueid);
98 $this->quiz = $quiz;
99 $this->context = $context;
100 $this->qmsubselect = $qmsubselect;
e97d60ad
TH
101 $this->qmfilter = $options->onlygraded;
102 $this->attemptsmode = $options->attempts;
f5e42695
TH
103 $this->groupstudents = $groupstudents;
104 $this->students = $students;
105 $this->questions = $questions;
e97d60ad 106 $this->includecheckboxes = $options->checkboxcolumn;
f5e42695 107 $this->reporturl = $reporturl;
e97d60ad 108 $this->options = $options;
f5e42695
TH
109 }
110
9d58dae3
TH
111 /**
112 * Generate the display of the checkbox column.
113 * @param object $attempt the table row being output.
114 * @return string HTML content to go inside the td.
115 */
f5e42695
TH
116 public function col_checkbox($attempt) {
117 if ($attempt->attempt) {
118 return '<input type="checkbox" name="attemptid[]" value="'.$attempt->attempt.'" />';
119 } else {
120 return '';
121 }
122 }
123
9d58dae3
TH
124 /**
125 * Generate the display of the user's picture column.
126 * @param object $attempt the table row being output.
127 * @return string HTML content to go inside the td.
128 */
f5e42695
TH
129 public function col_picture($attempt) {
130 global $OUTPUT;
131 $user = new stdClass();
132 $user->id = $attempt->userid;
133 $user->lastname = $attempt->lastname;
134 $user->firstname = $attempt->firstname;
135 $user->imagealt = $attempt->imagealt;
136 $user->picture = $attempt->picture;
137 $user->email = $attempt->email;
138 return $OUTPUT->user_picture($user);
139 }
140
9d58dae3
TH
141 /**
142 * Generate the display of the user's full name column.
143 * @param object $attempt the table row being output.
144 * @return string HTML content to go inside the td.
145 */
f5e42695
TH
146 public function col_fullname($attempt) {
147 $html = parent::col_fullname($attempt);
148 if ($this->is_downloading()) {
149 return $html;
150 }
151
152 return $html . html_writer::empty_tag('br') . html_writer::link(
153 new moodle_url('/mod/quiz/review.php', array('attempt' => $attempt->attempt)),
154 get_string('reviewattempt', 'quiz'), array('class' => 'reviewlink'));
155 }
156
c547514a
TH
157 /**
158 * Generate the display of the attempt state column.
159 * @param object $attempt the table row being output.
160 * @return string HTML content to go inside the td.
161 */
162 public function col_state($attempt) {
163 return quiz_attempt::state_name($attempt->state);
164 }
165
9d58dae3
TH
166 /**
167 * Generate the display of the start time column.
168 * @param object $attempt the table row being output.
169 * @return string HTML content to go inside the td.
170 */
f5e42695
TH
171 public function col_timestart($attempt) {
172 if ($attempt->attempt) {
173 return userdate($attempt->timestart, $this->strtimeformat);
174 } else {
175 return '-';
176 }
177 }
178
9d58dae3
TH
179 /**
180 * Generate the display of the finish time column.
181 * @param object $attempt the table row being output.
182 * @return string HTML content to go inside the td.
183 */
f5e42695
TH
184 public function col_timefinish($attempt) {
185 if ($attempt->attempt && $attempt->timefinish) {
186 return userdate($attempt->timefinish, $this->strtimeformat);
187 } else {
188 return '-';
189 }
190 }
191
9d58dae3
TH
192 /**
193 * Generate the display of the time taken column.
194 * @param object $attempt the table row being output.
195 * @return string HTML content to go inside the td.
196 */
f5e42695
TH
197 public function col_duration($attempt) {
198 if ($attempt->timefinish) {
199 return format_time($attempt->timefinish - $attempt->timestart);
200 } else if ($attempt->timestart) {
201 return get_string('unfinished', 'quiz');
202 } else {
203 return '-';
204 }
205 }
206
9d58dae3
TH
207 /**
208 * Generate the display of the feedback column.
209 * @param object $attempt the table row being output.
210 * @return string HTML content to go inside the td.
211 */
f5e42695
TH
212 public function col_feedbacktext($attempt) {
213 if (!$attempt->timefinish) {
214 return '-';
215 }
216
217 $feedback = quiz_report_feedback_for_grade(
218 quiz_rescale_grade($attempt->sumgrades, $this->quiz, false),
219 $this->quiz->id, $this->context);
220
221 if ($this->is_downloading()) {
222 $feedback = strip_tags($feedback);
223 }
224
225 return $feedback;
226 }
227
228 public function get_row_class($attempt) {
229 if ($this->qmsubselect && $attempt->gradedattempt) {
230 return 'gradedattempt';
231 } else {
232 return '';
233 }
234 }
235
236 /**
237 * Make a link to review an individual question in a popup window.
238 *
239 * @param string $data HTML fragment. The text to make into the link.
240 * @param object $attempt data for the row of the table being output.
241 * @param int $slot the number used to identify this question within this usage.
242 */
243 public function make_review_link($data, $attempt, $slot) {
244 global $OUTPUT;
245
246 $stepdata = $this->lateststeps[$attempt->usageid][$slot];
247 $state = question_state::get($stepdata->state);
248
249 $flag = '';
250 if ($stepdata->flagged) {
251 $flag = ' ' . $OUTPUT->pix_icon('i/flagged', get_string('flagged', 'question'),
252 'moodle', array('class' => 'questionflag'));
253 }
254
255 $feedbackimg = '';
256 if ($state->is_finished() && $state != question_state::$needsgrading) {
257 $feedbackimg = ' ' . $this->icon_for_fraction($stepdata->fraction);
258 }
259
260 $output = html_writer::tag('span', html_writer::tag('span',
261 $data . $feedbackimg . $flag,
262 array('class' => $state->get_state_class(true))), array('class' => 'que'));
263
264 $url = new moodle_url('/mod/quiz/reviewquestion.php',
265 array('attempt' => $attempt->attempt, 'slot' => $slot));
266 $output = $OUTPUT->action_link($url, $output,
267 new popup_action('click', $url, 'reviewquestion',
268 array('height' => 450, 'width' => 650)),
269 array('title' => get_string('reviewresponse', 'quiz')));
270
271 return $output;
272 }
273
274 /**
275 * Return an appropriate icon (green tick, red cross, etc.) for a grade.
276 * @param float $fraction grade on a scale 0..1.
277 * @return string html fragment.
278 */
279 protected function icon_for_fraction($fraction) {
280 global $OUTPUT;
281
282 $state = question_state::graded_state_for_fraction($fraction);
283 if ($state == question_state::$gradedright) {
284 $icon = 'i/tick_green_big';
285 } else if ($state == question_state::$gradedpartial) {
286 $icon = 'i/tick_amber_big';
287 } else {
288 $icon = 'i/cross_red_big';
289 }
290
291 return $OUTPUT->pix_icon($icon, get_string($state->get_feedback_class(), 'question'),
292 'moodle', array('class' => 'icon'));
293 }
294
295 /**
296 * Load information about the latest state of selected questions in selected attempts.
297 *
298 * The results are returned as an two dimensional array $qubaid => $slot => $dataobject
299 *
300 * @param qubaid_condition $qubaids used to restrict which usages are included
301 * in the query. See {@link qubaid_condition}.
f5e42695
TH
302 * @return array of records. See the SQL in this function to see the fields available.
303 */
304 protected function load_question_latest_steps(qubaid_condition $qubaids) {
305 $dm = new question_engine_data_mapper();
306 $latesstepdata = $dm->load_questions_usages_latest_steps(
307 $qubaids, array_keys($this->questions));
308
309 $lateststeps = array();
310 foreach ($latesstepdata as $step) {
311 $lateststeps[$step->questionusageid][$step->slot] = $step;
312 }
313
314 return $lateststeps;
315 }
316
317 /**
9d58dae3
TH
318 * Does this report require the detailed information for each question from the
319 * question_attempts_steps table?
f5e42695
TH
320 * @return bool should {@link query_db()} call {@link load_question_latest_steps}?
321 */
322 protected function requires_latest_steps_loaded() {
323 return false;
324 }
325
326 /**
327 * Is this a column that depends on joining to the latest state information?
328 * If so, return the corresponding slot. If not, return false.
329 * @param string $column a column name
330 * @return int false if no, else a slot.
331 */
332 protected function is_latest_step_column($column) {
333 return false;
334 }
335
336 /**
337 * Get any fields that might be needed when sorting on date for a particular slot.
338 * @param int $slot the slot for the column we want.
339 * @param string $alias the table alias for latest state information relating to that slot.
340 */
341 protected function get_required_latest_state_fields($slot, $alias) {
342 return '';
343 }
344
345 /**
346 * Contruct all the parts of the main database query.
347 * @param array $reportstudents list if userids of users to include in the report.
348 * @return array with 4 elements ($fields, $from, $where, $params) that can be used to
349 * build the actual database query.
350 */
351 public function base_sql($reportstudents) {
352 global $DB;
353
354 $fields = $DB->sql_concat('u.id', "'#'", 'COALESCE(quiza.attempt, 0)') . ' AS uniqueid,';
355
356 if ($this->qmsubselect) {
357 $fields .= "\n(CASE WHEN $this->qmsubselect THEN 1 ELSE 0 END) AS gradedattempt,";
358 }
359
360 $extrafields = get_extra_user_fields_sql($this->context, 'u', '',
361 array('id', 'idnumber', 'firstname', 'lastname', 'picture',
362 'imagealt', 'institution', 'department', 'email'));
363 $fields .= '
364 quiza.uniqueid AS usageid,
365 quiza.id AS attempt,
366 u.id AS userid,
367 u.idnumber,
368 u.firstname,
369 u.lastname,
370 u.picture,
371 u.imagealt,
372 u.institution,
373 u.department,
374 u.email' . $extrafields . ',
c547514a 375 quiza.state,
f5e42695
TH
376 quiza.sumgrades,
377 quiza.timefinish,
378 quiza.timestart,
379 CASE WHEN quiza.timefinish = 0 THEN null
380 WHEN quiza.timefinish > quiza.timestart THEN quiza.timefinish - quiza.timestart
381 ELSE 0 END AS duration';
382 // To explain that last bit, in MySQL, qa.timestart and qa.timefinish
383 // are unsigned. Since MySQL 5.5.5, when they introduced strict mode,
384 // subtracting a larger unsigned int from a smaller one gave an error.
385 // Therefore, we avoid doing that. timefinish can be non-zero and less
386 // than timestart when you have two load-balanced servers with very
768a7588 387 // badly synchronised clocks, and a student does a really quick attempt.
f5e42695 388
768a7588 389 // This part is the same for all cases. Join the users and quiz_attempts tables.
f5e42695
TH
390 $from = "\n{user} u";
391 $from .= "\nLEFT JOIN {quiz_attempts} quiza ON
392 quiza.userid = u.id AND quiza.quiz = :quizid";
393 $params = array('quizid' => $this->quiz->id);
394
395 if ($this->qmsubselect && $this->qmfilter) {
396 $from .= " AND $this->qmsubselect";
397 }
398 switch ($this->attemptsmode) {
ac4d9157 399 case quiz_attempts_report::ALL_ATTEMPTS:
768a7588 400 // Show all attempts, including students who are no longer in the course.
f5e42695
TH
401 $where = 'quiza.id IS NOT NULL AND quiza.preview = 0';
402 break;
ac4d9157 403 case quiz_attempts_report::STUDENTS_WITH:
768a7588 404 // Show only students with attempts.
f5e42695
TH
405 list($usql, $uparams) = $DB->get_in_or_equal(
406 $reportstudents, SQL_PARAMS_NAMED, 'u');
407 $params += $uparams;
408 $where = "u.id $usql AND quiza.preview = 0 AND quiza.id IS NOT NULL";
409 break;
ac4d9157 410 case quiz_attempts_report::STUDENTS_WITH_NO:
768a7588 411 // Show only students without attempts.
f5e42695
TH
412 list($usql, $uparams) = $DB->get_in_or_equal(
413 $reportstudents, SQL_PARAMS_NAMED, 'u');
414 $params += $uparams;
415 $where = "u.id $usql AND quiza.id IS NULL";
416 break;
ac4d9157 417 case quiz_attempts_report::ALL_STUDENTS:
768a7588 418 // Show all students with or without attempts.
f5e42695
TH
419 list($usql, $uparams) = $DB->get_in_or_equal(
420 $reportstudents, SQL_PARAMS_NAMED, 'u');
421 $params += $uparams;
422 $where = "u.id $usql AND (quiza.preview = 0 OR quiza.preview IS NULL)";
423 break;
424 }
425
426 return array($fields, $from, $where, $params);
427 }
428
429 /**
430 * Add the information about the latest state of the question with slot
431 * $slot to the query.
432 *
433 * The extra information is added as a join to a
434 * 'table' with alias qa$slot, with columns that are a union of
435 * the columns of the question_attempts and question_attempts_states tables.
436 *
437 * @param int $slot the question to add information for.
438 */
439 protected function add_latest_state_join($slot) {
440 $alias = 'qa' . $slot;
441
442 $fields = $this->get_required_latest_state_fields($slot, $alias);
443 if (!$fields) {
444 return;
445 }
446
447 // This condition roughly filters the list of attempts to be considered.
448 // It is only used in a subselect to help crappy databases (see MDL-30122)
449 // therefore, it is better to use a very simple join, which may include
450 // too many records, than to do a super-accurate join.
451 $qubaids = new qubaid_join("{quiz_attempts} {$alias}quiza", "{$alias}quiza.uniqueid",
452 "{$alias}quiza.quiz = :{$alias}quizid", array("{$alias}quizid" => $this->sql->params['quizid']));
453
454 $dm = new question_engine_data_mapper();
455 list($inlineview, $viewparams) = $dm->question_attempt_latest_state_view($alias, $qubaids);
456
457 $this->sql->fields .= ",\n$fields";
458 $this->sql->from .= "\nLEFT JOIN $inlineview ON " .
459 "$alias.questionusageid = quiza.uniqueid AND $alias.slot = :{$alias}slot";
460 $this->sql->params[$alias . 'slot'] = $slot;
461 $this->sql->params = array_merge($this->sql->params, $viewparams);
462 }
463
464 /**
465 * Get an appropriate qubaid_condition for loading more data about the
466 * attempts we are displaying.
467 * @return qubaid_condition
468 */
469 protected function get_qubaids_condition() {
470 if (is_null($this->rawdata)) {
471 throw new coding_exception(
472 'Cannot call get_qubaids_condition until the main data has been loaded.');
473 }
474
475 if ($this->is_downloading()) {
476 // We want usages for all attempts.
477 return new qubaid_join($this->sql->from, 'quiza.uniqueid',
478 $this->sql->where, $this->sql->params);
479 }
480
481 $qubaids = array();
482 foreach ($this->rawdata as $attempt) {
483 if ($attempt->usageid > 0) {
484 $qubaids[] = $attempt->usageid;
485 }
486 }
487
488 return new qubaid_list($qubaids);
489 }
490
491 public function query_db($pagesize, $useinitialsbar = true) {
492 $doneslots = array();
493 foreach ($this->get_sort_columns() as $column => $notused) {
494 $slot = $this->is_latest_step_column($column);
495 if ($slot && !in_array($slot, $doneslots)) {
496 $this->add_latest_state_join($slot);
497 $doneslots[] = $slot;
498 }
499 }
500
501 parent::query_db($pagesize, $useinitialsbar);
502
503 if ($this->requires_latest_steps_loaded()) {
504 $qubaids = $this->get_qubaids_condition();
505 $this->lateststeps = $this->load_question_latest_steps($qubaids);
506 }
507 }
508
509 public function get_sort_columns() {
510 // Add attemptid as a final tie-break to the sort. This ensures that
511 // Attempts by the same student appear in order when just sorting by name.
512 $sortcolumns = parent::get_sort_columns();
513 $sortcolumns['quiza.id'] = SORT_ASC;
514 return $sortcolumns;
515 }
516
517 public function wrap_html_start() {
518 if ($this->is_downloading() || !$this->includecheckboxes) {
519 return;
520 }
521
522 $url = new moodle_url($this->reporturl, $this->displayoptions);
523 $url->param('sesskey', sesskey());
524
525 echo '<div id="tablecontainer">';
526 echo '<form id="attemptsform" method="post" action="' . $url->out_omit_querystring() . '">';
527
528 echo html_writer::input_hidden_params($url);
529 echo '<div>';
530 }
531
532 public function wrap_html_finish() {
533 if ($this->is_downloading() || !$this->includecheckboxes) {
534 return;
535 }
536
537 echo '<div id="commands">';
538 echo '<a href="javascript:select_all_in(\'DIV\', null, \'tablecontainer\');">' .
539 get_string('selectall', 'quiz') . '</a> / ';
540 echo '<a href="javascript:deselect_all_in(\'DIV\', null, \'tablecontainer\');">' .
541 get_string('selectnone', 'quiz') . '</a> ';
542 echo '&nbsp;&nbsp;';
543 $this->submit_buttons();
544 echo '</div>';
768a7588
TH
545
546 // Close the form.
f5e42695
TH
547 echo '</div>';
548 echo '</form></div>';
549 }
550
551 /**
552 * Output any submit buttons required by the $this->includecheckboxes form.
553 */
554 protected function submit_buttons() {
555 global $PAGE;
556 if (has_capability('mod/quiz:deleteattempts', $this->context)) {
557 echo '<input type="submit" id="deleteattemptsbutton" name="delete" value="' .
558 get_string('deleteselected', 'quiz_overview') . '"/>';
559 $PAGE->requires->event_handler('#deleteattemptsbutton', 'click', 'M.util.show_confirm_dialog',
560 array('message' => get_string('deleteattemptcheck', 'quiz')));
561 }
562 }
563}