MDL-20636 Implement display of grader instructions. #216
[moodle.git] / question / engine / datalib.php
CommitLineData
d1b7e03d
TH
1<?php
2
3// This file is part of Moodle - http://moodle.org/
4//
5// Moodle is free software: you can redistribute it and/or modify
6// it under the terms of the GNU General Public License as published by
7// the Free Software Foundation, either version 3 of the License, or
8// (at your option) any later version.
9//
10// Moodle is distributed in the hope that it will be useful,
11// but WITHOUT ANY WARRANTY; without even the implied warranty of
12// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
13// GNU General Public License for more details.
14//
15// You should have received a copy of the GNU General Public License
16// along with Moodle. If not, see <http://www.gnu.org/licenses/>.
17
d1b7e03d 18/**
5e8a85aa 19 * Code for loading and saving question attempts to and from the database.
d1b7e03d 20 *
017bc1d9 21 * @package moodlecore
d1b7e03d 22 * @subpackage questionengine
017bc1d9
TH
23 * @copyright 2009 The Open University
24 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
d1b7e03d
TH
25 */
26
27
a17b297d
TH
28defined('MOODLE_INTERNAL') || die();
29
30
d1b7e03d
TH
31/**
32 * This class controls the loading and saving of question engine data to and from
33 * the database.
34 *
017bc1d9
TH
35 * @copyright 2009 The Open University
36 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
d1b7e03d
TH
37 */
38class question_engine_data_mapper {
06f8ed54
TH
39 /**
40 * @var moodle_database normally points to global $DB, but I prefer not to
41 * use globals if I can help it.
42 */
43 protected $db;
44
45 /**
46 * @param moodle_database $db a database connectoin. Defaults to global $DB.
47 */
48 public function __construct($db = null) {
49 if (is_null($db)) {
50 global $DB;
06f8ed54
TH
51 $this->db = $DB;
52 } else {
53 $this->db = $db;
54 }
55 }
56
d1b7e03d
TH
57 /**
58 * Store an entire {@link question_usage_by_activity} in the database,
59 * including all the question_attempts that comprise it.
60 * @param question_usage_by_activity $quba the usage to store.
61 */
62 public function insert_questions_usage_by_activity(question_usage_by_activity $quba) {
0ff4bd08 63 $record = new stdClass();
d1b7e03d 64 $record->contextid = $quba->get_owning_context()->id;
56b0df7e
TH
65 $record->component = $quba->get_owning_component();
66 $record->preferredbehaviour = $quba->get_preferred_behaviour();
d1b7e03d 67
06f8ed54 68 $newid = $this->db->insert_record('question_usages', $record);
d1b7e03d
TH
69 $quba->set_id_from_database($newid);
70
71 foreach ($quba->get_attempt_iterator() as $qa) {
72 $this->insert_question_attempt($qa);
73 }
74 }
75
76 /**
77 * Store an entire {@link question_attempt} in the database,
78 * including all the question_attempt_steps that comprise it.
79 * @param question_attempt $qa the question attempt to store.
80 */
81 public function insert_question_attempt(question_attempt $qa) {
0ff4bd08 82 $record = new stdClass();
d1b7e03d
TH
83 $record->questionusageid = $qa->get_usage_id();
84 $record->slot = $qa->get_slot();
56b0df7e 85 $record->behaviour = $qa->get_behaviour_name();
d1b7e03d
TH
86 $record->questionid = $qa->get_question()->id;
87 $record->maxmark = $qa->get_max_mark();
88 $record->minfraction = $qa->get_min_fraction();
89 $record->flagged = $qa->is_flagged();
56b0df7e
TH
90 $record->questionsummary = $qa->get_question_summary();
91 $record->rightanswer = $qa->get_right_answer_summary();
92 $record->responsesummary = $qa->get_response_summary();
d1b7e03d 93 $record->timemodified = time();
06f8ed54 94 $record->id = $this->db->insert_record('question_attempts', $record);
d1b7e03d
TH
95
96 foreach ($qa->get_step_iterator() as $seq => $step) {
97 $this->insert_question_attempt_step($step, $record->id, $seq);
98 }
99 }
100
101 /**
102 * Store a {@link question_attempt_step} in the database.
103 * @param question_attempt_step $qa the step to store.
104 */
105 public function insert_question_attempt_step(question_attempt_step $step,
106 $questionattemptid, $seq) {
0ff4bd08 107 $record = new stdClass();
d1b7e03d
TH
108 $record->questionattemptid = $questionattemptid;
109 $record->sequencenumber = $seq;
56b0df7e 110 $record->state = '' . $step->get_state();
d1b7e03d
TH
111 $record->fraction = $step->get_fraction();
112 $record->timecreated = $step->get_timecreated();
113 $record->userid = $step->get_user_id();
114
06f8ed54 115 $record->id = $this->db->insert_record('question_attempt_steps', $record);
d1b7e03d
TH
116
117 foreach ($step->get_all_data() as $name => $value) {
0ff4bd08 118 $data = new stdClass();
d1b7e03d 119 $data->attemptstepid = $record->id;
56b0df7e
TH
120 $data->name = $name;
121 $data->value = $value;
06f8ed54 122 $this->db->insert_record('question_attempt_step_data', $data, false);
d1b7e03d
TH
123 }
124 }
125
126 /**
127 * Load a {@link question_attempt_step} from the database.
f7970e3c 128 * @param int $stepid the id of the step to load.
d1b7e03d
TH
129 * @param question_attempt_step the step that was loaded.
130 */
131 public function load_question_attempt_step($stepid) {
06f8ed54 132 $records = $this->db->get_records_sql("
d1b7e03d
TH
133SELECT
134 COALESCE(qasd.id, -1 * qas.id) AS id,
135 qas.id AS attemptstepid,
136 qas.questionattemptid,
137 qas.sequencenumber,
138 qas.state,
139 qas.fraction,
140 qas.timecreated,
141 qas.userid,
142 qasd.name,
143 qasd.value
144
06f8ed54
TH
145FROM {question_attempt_steps} qas
146LEFT JOIN {question_attempt_step_data} qasd ON qasd.attemptstepid = qas.id
d1b7e03d
TH
147
148WHERE
06f8ed54
TH
149 qas.id = :stepid
150 ", array('stepid' => $stepid));
d1b7e03d
TH
151
152 if (!$records) {
88f0eb15 153 throw new coding_exception('Failed to load question_attempt_step ' . $stepid);
d1b7e03d
TH
154 }
155
156 return question_attempt_step::load_from_records($records, $stepid);
157 }
158
159 /**
160 * Load a {@link question_attempt} from the database, including all its
161 * steps.
f7970e3c 162 * @param int $questionattemptid the id of the question attempt to load.
d1b7e03d
TH
163 * @param question_attempt the question attempt that was loaded.
164 */
165 public function load_question_attempt($questionattemptid) {
06f8ed54 166 $records = $this->db->get_records_sql("
d1b7e03d
TH
167SELECT
168 COALESCE(qasd.id, -1 * qas.id) AS id,
7a719748 169 quba.contextid,
d1b7e03d
TH
170 quba.preferredbehaviour,
171 qa.id AS questionattemptid,
172 qa.questionusageid,
173 qa.slot,
174 qa.behaviour,
175 qa.questionid,
176 qa.maxmark,
177 qa.minfraction,
178 qa.flagged,
179 qa.questionsummary,
180 qa.rightanswer,
181 qa.responsesummary,
182 qa.timemodified,
183 qas.id AS attemptstepid,
184 qas.sequencenumber,
185 qas.state,
186 qas.fraction,
187 qas.timecreated,
188 qas.userid,
189 qasd.name,
190 qasd.value
191
06f8ed54
TH
192FROM {question_attempts qa
193JOIN {question_usages} quba ON quba.id = qa.questionusageid
194LEFT JOIN {question_attempt_steps} qas ON qas.questionattemptid = qa.id
195LEFT JOIN {question_attempt_step_data} qasd ON qasd.attemptstepid = qas.id
d1b7e03d
TH
196
197WHERE
06f8ed54 198 qa.id = :questionattemptid
d1b7e03d
TH
199
200ORDER BY
201 qas.sequencenumber
06f8ed54 202 ", array('questionattemptid' => $questionattemptid));
d1b7e03d
TH
203
204 if (!$records) {
88f0eb15 205 throw new coding_exception('Failed to load question_attempt ' . $questionattemptid);
d1b7e03d
TH
206 }
207
208 $record = current($records);
209 return question_attempt::load_from_records($records, $questionattemptid,
210 new question_usage_null_observer(), $record->preferredbehaviour);
211 }
212
213 /**
214 * Load a {@link question_usage_by_activity} from the database, including
215 * all its {@link question_attempt}s and all their steps.
f7970e3c 216 * @param int $qubaid the id of the usage to load.
d1b7e03d
TH
217 * @param question_usage_by_activity the usage that was loaded.
218 */
219 public function load_questions_usage_by_activity($qubaid) {
06f8ed54 220 $records = $this->db->get_records_sql("
d1b7e03d
TH
221SELECT
222 COALESCE(qasd.id, -1 * qas.id) AS id,
223 quba.id AS qubaid,
224 quba.contextid,
225 quba.component,
226 quba.preferredbehaviour,
227 qa.id AS questionattemptid,
228 qa.questionusageid,
229 qa.slot,
230 qa.behaviour,
231 qa.questionid,
232 qa.maxmark,
233 qa.minfraction,
234 qa.flagged,
235 qa.questionsummary,
236 qa.rightanswer,
237 qa.responsesummary,
238 qa.timemodified,
239 qas.id AS attemptstepid,
240 qas.sequencenumber,
241 qas.state,
242 qas.fraction,
243 qas.timecreated,
244 qas.userid,
245 qasd.name,
246 qasd.value
247
06f8ed54
TH
248FROM {question_usages} quba
249LEFT JOIN {question_attempts} qa ON qa.questionusageid = quba.id
250LEFT JOIN {question_attempt_steps} qas ON qas.questionattemptid = qa.id
251LEFT JOIN {question_attempt_step_data} qasd ON qasd.attemptstepid = qas.id
d1b7e03d
TH
252
253WHERE
06f8ed54 254 quba.id = :qubaid
d1b7e03d
TH
255
256ORDER BY
257 qa.slot,
258 qas.sequencenumber
c76145d3 259 ", array('qubaid' => $qubaid));
d1b7e03d
TH
260
261 if (!$records) {
88f0eb15 262 throw new coding_exception('Failed to load questions_usage_by_activity ' . $qubaid);
d1b7e03d
TH
263 }
264
265 return question_usage_by_activity::load_from_records($records, $qubaid);
266 }
267
268 /**
269 * Load information about the latest state of each question from the database.
270 *
271 * @param qubaid_condition $qubaids used to restrict which usages are included
272 * in the query. See {@link qubaid_condition}.
273 * @param array $slots A list of slots for the questions you want to konw about.
274 * @return array of records. See the SQL in this function to see the fields available.
275 */
276 public function load_questions_usages_latest_steps(qubaid_condition $qubaids, $slots) {
c76145d3 277 list($slottest, $params) = $this->db->get_in_or_equal($slots, SQL_PARAMS_NAMED, 'slot0000');
d1b7e03d 278
9b40c540 279 $records = $this->db->get_records_sql("
d1b7e03d
TH
280SELECT
281 qas.id,
282 qa.id AS questionattemptid,
283 qa.questionusageid,
284 qa.slot,
285 qa.behaviour,
286 qa.questionid,
287 qa.maxmark,
288 qa.minfraction,
289 qa.flagged,
290 qa.questionsummary,
291 qa.rightanswer,
292 qa.responsesummary,
293 qa.timemodified,
294 qas.id AS attemptstepid,
295 qas.sequencenumber,
296 qas.state,
297 qas.fraction,
298 qas.timecreated,
299 qas.userid
300
301FROM {$qubaids->from_question_attempts('qa')}
06f8ed54 302JOIN {question_attempt_steps} qas ON
d1b7e03d
TH
303 qas.id = {$this->latest_step_for_qa_subquery()}
304
305WHERE
306 {$qubaids->where()} AND
307 qa.slot $slottest
06f8ed54 308 ", $params + $qubaids->from_where_params());
d1b7e03d
TH
309
310 return $records;
311 }
312
313 /**
5e8a85aa
TH
314 * Load summary information about the state of each question in a group of
315 * attempts. This is used, for example, by the quiz manual grading report,
316 * to show how many attempts at each question need to be graded.
d1b7e03d
TH
317 *
318 * @param qubaid_condition $qubaids used to restrict which usages are included
319 * in the query. See {@link qubaid_condition}.
320 * @param array $slots A list of slots for the questions you want to konw about.
321 * @return array The array keys are slot,qestionid. The values are objects with
322 * fields $slot, $questionid, $inprogress, $name, $needsgrading, $autograded,
323 * $manuallygraded and $all.
324 */
325 public function load_questions_usages_question_state_summary(qubaid_condition $qubaids, $slots) {
cf3b6568 326 list($slottest, $params) = $this->db->get_in_or_equal($slots, SQL_PARAMS_NAMED, 'slot0000');
d1b7e03d 327
06f8ed54 328 $rs = $this->db->get_recordset_sql("
d1b7e03d
TH
329SELECT
330 qa.slot,
331 qa.questionid,
332 q.name,
333 CASE qas.state
334 {$this->full_states_to_summary_state_sql()}
335 END AS summarystate,
336 COUNT(1) AS numattempts
337
338FROM {$qubaids->from_question_attempts('qa')}
06f8ed54 339JOIN {question_attempt_steps} qas ON
d1b7e03d 340 qas.id = {$this->latest_step_for_qa_subquery()}
06f8ed54 341JOIN {question} q ON q.id = qa.questionid
d1b7e03d
TH
342
343WHERE
344 {$qubaids->where()} AND
345 qa.slot $slottest
346
347GROUP BY
348 qa.slot,
349 qa.questionid,
350 q.name,
351 q.id,
352 summarystate
353
f9b0500f 354ORDER BY
d1b7e03d
TH
355 qa.slot,
356 qa.questionid,
357 q.name,
358 q.id
06f8ed54 359 ", $params + $qubaids->from_where_params());
d1b7e03d 360
d1b7e03d 361 $results = array();
cf3b6568 362 foreach ($rs as $row) {
d1b7e03d
TH
363 $index = $row->slot . ',' . $row->questionid;
364
365 if (!array_key_exists($index, $results)) {
0ff4bd08 366 $res = new stdClass();
d1b7e03d
TH
367 $res->slot = $row->slot;
368 $res->questionid = $row->questionid;
369 $res->name = $row->name;
370 $res->inprogress = 0;
371 $res->needsgrading = 0;
372 $res->autograded = 0;
373 $res->manuallygraded = 0;
374 $res->all = 0;
375 $results[$index] = $res;
376 }
377
378 $results[$index]->{$row->summarystate} = $row->numattempts;
379 $results[$index]->all += $row->numattempts;
380 }
cf3b6568 381 $rs->close();
d1b7e03d
TH
382
383 return $results;
384 }
385
386 /**
387 * Get a list of usage ids where the question with slot $slot, and optionally
388 * also with question id $questionid, is in summary state $summarystate. Also
389 * return the total count of such states.
390 *
391 * Only a subset of the ids can be returned by using $orderby, $limitfrom and
392 * $limitnum. A special value 'random' can be passed as $orderby, in which case
393 * $limitfrom is ignored.
394 *
395 * @param qubaid_condition $qubaids used to restrict which usages are included
396 * in the query. See {@link qubaid_condition}.
f7970e3c
TH
397 * @param int $slot The slot for the questions you want to konw about.
398 * @param int $questionid (optional) Only return attempts that were of this specific question.
d1b7e03d
TH
399 * @param string $summarystate the summary state of interest, or 'all'.
400 * @param string $orderby the column to order by.
cf3b6568 401 * @param array $params any params required by any of the SQL fragments.
f7970e3c 402 * @param int $limitfrom implements paging of the results.
d1b7e03d 403 * Ignored if $orderby = random or $limitnum is null.
f7970e3c 404 * @param int $limitnum implements paging of the results. null = all.
d1b7e03d
TH
405 * @return array with two elements, an array of usage ids, and a count of the total number.
406 */
407 public function load_questions_usages_where_question_in_state(
408 qubaid_condition $qubaids, $summarystate, $slot, $questionid = null,
cf3b6568 409 $orderby = 'random', $params, $limitfrom = 0, $limitnum = null) {
d1b7e03d
TH
410
411 $extrawhere = '';
412 if ($questionid) {
cf3b6568
TH
413 $extrawhere .= ' AND qa.questionid = :questionid';
414 $params['questionid'] = $questionid;
d1b7e03d
TH
415 }
416 if ($summarystate != 'all') {
cf3b6568 417 list($test, $sparams) = $this->in_summary_state_test($summarystate);
d1b7e03d 418 $extrawhere .= ' AND qas.state ' . $test;
cf3b6568 419 $params += $sparams;
d1b7e03d
TH
420 }
421
422 if ($orderby == 'random') {
423 $sqlorderby = '';
424 } else if ($orderby) {
425 $sqlorderby = 'ORDER BY ' . $orderby;
426 } else {
427 $sqlorderby = '';
428 }
429
430 // We always want the total count, as well as the partcular list of ids,
431 // based on the paging and sort order. Becuase the list of ids is never
432 // going to be too rediculously long. My worst-case scenario is
433 // 10,000 students in the coures, each doing 5 quiz attempts. That
434 // is a 50,000 element int => int array, which PHP seems to use 5MB
435 // memeory to store on a 64 bit server.
cf3b6568
TH
436 $params += $qubaids->from_where_params();
437 $params['slot'] = $slot;
438 $qubaids = $this->db->get_records_sql_menu("
d1b7e03d
TH
439SELECT
440 qa.questionusageid,
441 1
442
443FROM {$qubaids->from_question_attempts('qa')}
cf3b6568 444JOIN {question_attempt_steps} qas ON
d1b7e03d 445 qas.id = {$this->latest_step_for_qa_subquery()}
cf3b6568 446JOIN {question} q ON q.id = qa.questionid
d1b7e03d
TH
447
448WHERE
449 {$qubaids->where()} AND
cf3b6568 450 qa.slot = :slot
d1b7e03d
TH
451 $extrawhere
452
453$sqlorderby
cf3b6568 454 ", $params);
d1b7e03d
TH
455
456 $qubaids = array_keys($qubaids);
457 $count = count($qubaids);
458
459 if ($orderby == 'random') {
460 shuffle($qubaids);
461 $limitfrom = 0;
462 }
463
464 if (!is_null($limitnum)) {
465 $qubaids = array_slice($qubaids, $limitfrom, $limitnum);
466 }
467
468 return array($qubaids, $count);
469 }
470
471 /**
472 * Load a {@link question_usage_by_activity} from the database, including
473 * all its {@link question_attempt}s and all their steps.
474 * @param qubaid_condition $qubaids used to restrict which usages are included
475 * in the query. See {@link qubaid_condition}.
476 * @param array $slots if null, load info for all quesitions, otherwise only
477 * load the averages for the specified questions.
478 */
479 public function load_average_marks(qubaid_condition $qubaids, $slots = null) {
d1b7e03d 480 if (!empty($slots)) {
e24ee794 481 list($slottest, $slotsparams) = $this->db->get_in_or_equal($slots, SQL_PARAMS_NAMED, 'slot0000');
d1b7e03d
TH
482 $slotwhere = " AND qa.slot $slottest";
483 } else {
484 $slotwhere = '';
e24ee794 485 $params = array();
d1b7e03d
TH
486 }
487
e24ee794 488 list($statetest, $stateparams) = $this->db->get_in_or_equal(array(
d1b7e03d
TH
489 question_state::$gaveup,
490 question_state::$gradedwrong,
491 question_state::$gradedpartial,
492 question_state::$gradedright,
493 question_state::$mangaveup,
494 question_state::$mangrwrong,
495 question_state::$mangrpartial,
e24ee794 496 question_state::$mangrright), SQL_PARAMS_NAMED, 'st00');
d1b7e03d 497
e24ee794 498 return $this->db->get_records_sql("
d1b7e03d
TH
499SELECT
500 qa.slot,
501 AVG(COALESCE(qas.fraction, 0)) AS averagefraction,
502 COUNT(1) AS numaveraged
503
504FROM {$qubaids->from_question_attempts('qa')}
2a3bdbf9 505JOIN {question_attempt_steps} qas ON
d1b7e03d
TH
506 qas.id = {$this->latest_step_for_qa_subquery()}
507
508WHERE
509 {$qubaids->where()}
510 $slotwhere
511 AND qas.state $statetest
512
513GROUP BY qa.slot
514
515ORDER BY qa.slot
e24ee794 516 ", $slotsparams + $stateparams + $qubaids->from_where_params());
d1b7e03d
TH
517 }
518
519 /**
520 * Load a {@link question_attempt} from the database, including all its
521 * steps.
f7970e3c 522 * @param int $questionid the question to load all the attempts fors.
d1b7e03d
TH
523 * @param qubaid_condition $qubaids used to restrict which usages are included
524 * in the query. See {@link qubaid_condition}.
525 * @return array of question_attempts.
526 */
527 public function load_attempts_at_question($questionid, qubaid_condition $qubaids) {
9b40c540 528 global $DB;
04853f27
TH
529
530 $params = $qubaids->from_where_params();
531 $params['questionid'] = $questionid;
532
9b40c540 533 $records = $DB->get_records_sql("
d1b7e03d
TH
534SELECT
535 COALESCE(qasd.id, -1 * qas.id) AS id,
04853f27 536 quba.contextid,
d1b7e03d
TH
537 quba.preferredbehaviour,
538 qa.id AS questionattemptid,
539 qa.questionusageid,
540 qa.slot,
541 qa.behaviour,
542 qa.questionid,
543 qa.maxmark,
544 qa.minfraction,
545 qa.flagged,
546 qa.questionsummary,
547 qa.rightanswer,
548 qa.responsesummary,
549 qa.timemodified,
550 qas.id AS attemptstepid,
551 qas.sequencenumber,
552 qas.state,
553 qas.fraction,
554 qas.timecreated,
555 qas.userid,
556 qasd.name,
557 qasd.value
558
559FROM {$qubaids->from_question_attempts('qa')}
04853f27
TH
560JOIN {question_usages} quba ON quba.id = qa.questionusageid
561LEFT JOIN {question_attempt_steps} qas ON qas.questionattemptid = qa.id
562LEFT JOIN {question_attempt_step_data} qasd ON qasd.attemptstepid = qas.id
d1b7e03d
TH
563
564WHERE
565 {$qubaids->where()} AND
04853f27 566 qa.questionid = :questionid
d1b7e03d
TH
567
568ORDER BY
569 quba.id,
570 qa.id,
571 qas.sequencenumber
04853f27 572 ", $params);
d1b7e03d
TH
573
574 if (!$records) {
575 return array();
576 }
577
578 $questionattempts = array();
579 $record = current($records);
580 while ($record) {
581 $questionattempts[$record->questionattemptid] =
582 question_attempt::load_from_records($records,
583 $record->questionattemptid, new question_usage_null_observer(),
584 $record->preferredbehaviour);
585 $record = current($records);
586 }
587 return $questionattempts;
588 }
589
590 /**
591 * Update a question_usages row to refect any changes in a usage (but not
592 * any of its question_attempts.
593 * @param question_usage_by_activity $quba the usage that has changed.
594 */
595 public function update_questions_usage_by_activity(question_usage_by_activity $quba) {
0ff4bd08 596 $record = new stdClass();
d1b7e03d
TH
597 $record->id = $quba->get_id();
598 $record->contextid = $quba->get_owning_context()->id;
56b0df7e
TH
599 $record->component = $quba->get_owning_component();
600 $record->preferredbehaviour = $quba->get_preferred_behaviour();
d1b7e03d 601
88f0eb15 602 $this->db->update_record('question_usages', $record);
d1b7e03d
TH
603 }
604
605 /**
606 * Update a question_attempts row to refect any changes in a question_attempt
607 * (but not any of its steps).
608 * @param question_attempt $qa the question attempt that has changed.
609 */
610 public function update_question_attempt(question_attempt $qa) {
0ff4bd08 611 $record = new stdClass();
d1b7e03d
TH
612 $record->id = $qa->get_database_id();
613 $record->maxmark = $qa->get_max_mark();
614 $record->minfraction = $qa->get_min_fraction();
615 $record->flagged = $qa->is_flagged();
56b0df7e
TH
616 $record->questionsummary = $qa->get_question_summary();
617 $record->rightanswer = $qa->get_right_answer_summary();
618 $record->responsesummary = $qa->get_response_summary();
d1b7e03d
TH
619 $record->timemodified = time();
620
88f0eb15 621 $this->db->update_record('question_attempts', $record);
d1b7e03d
TH
622 }
623
624 /**
625 * Delete a question_usage_by_activity and all its associated
626 * {@link question_attempts} and {@link question_attempt_steps} from the
627 * database.
628 * @param string $where a where clause. Becuase of MySQL limitations, you
2a3bdbf9 629 * must refer to {question_usages}.id in full like that.
c76145d3 630 * @param array $params values to substitute for placeholders in $where.
d1b7e03d 631 */
c76145d3 632 public function delete_questions_usage_by_activities($where, $params) {
c76145d3 633 $this->db->delete_records_select('question_attempt_step_data', "attemptstepid IN (
d1b7e03d 634 SELECT qas.id
c76145d3
TH
635 FROM {question_attempts} qa
636 JOIN {question_attempt_steps} qas ON qas.questionattemptid = qa.id
637 JOIN {question_usages} ON qa.questionusageid = {question_usages}.id
638 WHERE $where)", $params);
639 $this->db->delete_records_select('question_attempt_steps', "questionattemptid IN (
d1b7e03d 640 SELECT qa.id
c76145d3
TH
641 FROM {question_attempts} qa
642 JOIN {question_usages} ON qa.questionusageid = {question_usages}.id
643 WHERE $where)", $params);
644 $this->db->delete_records_select('question_attempts', "questionusageid IN (
d1b7e03d 645 SELECT id
c76145d3
TH
646 FROM {question_usages}
647 WHERE $where)", $params);
648 $this->db->delete_records_select('question_usages', $where, $params);
d1b7e03d
TH
649 }
650
651 /**
652 * Delete all the steps for a question attempt.
f7970e3c 653 * @param int $qaids question_attempt id.
d1b7e03d
TH
654 */
655 public function delete_steps_for_question_attempts($qaids) {
d1b7e03d
TH
656 if (empty($qaids)) {
657 return;
658 }
e24ee794 659 list($test, $params) = $this->db->get_in_or_equal($qaids);
c76145d3 660 $this->db->delete_records_select('question_attempt_step_data', "attemptstepid IN (
d1b7e03d 661 SELECT qas.id
c76145d3
TH
662 FROM {question_attempt_steps} qas
663 WHERE questionattemptid $test)", $params);
664 $this->db->delete_records_select('question_attempt_steps', 'questionattemptid ' . $test, $params);
d1b7e03d
TH
665 }
666
667 /**
668 * Delete all the previews for a given question.
f7970e3c 669 * @param int $questionid question id.
d1b7e03d
TH
670 */
671 public function delete_previews($questionid) {
c76145d3 672 $previews = $this->db->get_records_sql_menu("
d1b7e03d 673 SELECT DISTINCT quba.id, 1
c76145d3
TH
674 FROM {question_usages} quba
675 JOIN {question_attempts} qa ON qa.questionusageid = quba.id
d1b7e03d 676 WHERE quba.component = 'core_question_preview' AND
c76145d3 677 qa.questionid = ?", array($questionid));
d1b7e03d
TH
678 if (empty($previews)) {
679 return;
680 }
c76145d3
TH
681 list($test, $params) = $this->db->get_in_or_equal(array_keys($previews));
682 $this->delete_questions_usage_by_activities('question_usages.id ' . $test, $params);
d1b7e03d
TH
683 }
684
685 /**
686 * Update the flagged state of a question in the database.
f7970e3c
TH
687 * @param int $qubaid the question usage id.
688 * @param int $questionid the question id.
689 * @param int $sessionid the question_attempt id.
690 * @param bool $newstate the new state of the flag. true = flagged.
d1b7e03d 691 */
06f8ed54 692 public function update_question_attempt_flag($qubaid, $questionid, $qaid, $slot, $newstate) {
f9b0500f 693 if (!$this->db->record_exists('question_attempts', array('id' => $qaid,
06f8ed54 694 'questionusageid' => $qubaid, 'questionid' => $questionid, 'slot' => $slot))) {
88f0eb15 695 throw new moodle_exception('errorsavingflags', 'question');
d1b7e03d
TH
696 }
697
06f8ed54 698 $this->db->set_field('question_attempts', 'flagged', $newstate, array('id' => $qaid));
d1b7e03d
TH
699 }
700
701 /**
702 * Get all the WHEN 'x' THEN 'y' terms needed to convert the question_attempt_steps.state
703 * column to a summary state. Use this like
704 * CASE qas.state {$this->full_states_to_summary_state_sql()} END AS summarystate,
705 * @param string SQL fragment.
706 */
707 protected function full_states_to_summary_state_sql() {
708 $sql = '';
709 foreach (question_state::get_all() as $state) {
710 $sql .= "WHEN '$state' THEN '{$state->get_summary_state()}'\n";
711 }
712 return $sql;
713 }
714
715 /**
716 * Get the SQL needed to test that question_attempt_steps.state is in a
717 * state corresponding to $summarystate.
718 * @param string $summarystate one of
719 * inprogress, needsgrading, manuallygraded or autograded
f7970e3c 720 * @param bool $equal if false, do a NOT IN test. Default true.
d1b7e03d
TH
721 * @return string SQL fragment.
722 */
cf3b6568 723 public function in_summary_state_test($summarystate, $equal = true, $prefix = 'summarystates') {
d1b7e03d 724 $states = question_state::get_all_for_summary_state($summarystate);
cf3b6568 725 return $this->db->get_in_or_equal($states, SQL_PARAMS_NAMED, $prefix . '00', $equal);
d1b7e03d
TH
726 }
727
728 /**
729 * Change the maxmark for the question_attempt with number in usage $slot
730 * for all the specified question_attempts.
731 * @param qubaid_condition $qubaids Selects which usages are updated.
f7970e3c 732 * @param int $slot the number is usage to affect.
d1b7e03d
TH
733 * @param number $newmaxmark the new max mark to set.
734 */
735 public function set_max_mark_in_attempts(qubaid_condition $qubaids, $slot, $newmaxmark) {
c76145d3
TH
736 $this->db->set_field_select('question_attempts', 'maxmark', $newmaxmark,
737 "questionusageid {$qubaids->usage_id_in()} AND slot = :slot",
738 $qubaids->usage_id_in_params() + array('slot' => $slot));
d1b7e03d
TH
739 }
740
741 /**
742 * Return a subquery that computes the sum of the marks for all the questions
743 * in a usage. Which useage to compute the sum for is controlled bu the $qubaid
744 * parameter.
745 *
746 * See {@link quiz_update_all_attempt_sumgrades()} for an example of the usage of
747 * this method.
748 *
749 * @param string $qubaid SQL fragment that controls which usage is summed.
2daffca5
TH
750 * This will normally be the name of a column in the outer query. Not that this
751 * SQL fragment must not contain any placeholders.
d1b7e03d
TH
752 * @return string SQL code for the subquery.
753 */
754 public function sum_usage_marks_subquery($qubaid) {
d1b7e03d 755 return "SELECT SUM(qa.maxmark * qas.fraction)
c76145d3 756 FROM {question_attempts} qa
d1b7e03d
TH
757 JOIN (
758 SELECT summarks_qa.id AS questionattemptid, MAX(summarks_qas.id) AS latestid
c76145d3
TH
759 FROM {question_attempt_steps} summarks_qas
760 JOIN {question_attempts} summarks_qa ON summarks_qa.id = summarks_qas.questionattemptid
d1b7e03d
TH
761 WHERE summarks_qa.questionusageid = $qubaid
762 GROUP BY summarks_qa.id
763 ) lateststepid ON lateststepid.questionattemptid = qa.id
c76145d3 764 JOIN {question_attempt_steps} qas ON qas.id = lateststepid.latestid
d1b7e03d 765 WHERE qa.questionusageid = $qubaid
f86390dc 766 HAVING COUNT(CASE WHEN qas.state = 'needsgrading' AND qa.maxmark > 0 THEN 1 ELSE NULL END) = 0";
d1b7e03d
TH
767 }
768
769 public function question_attempt_latest_state_view($alias) {
d1b7e03d
TH
770 return "(
771 SELECT
772 {$alias}qa.id AS questionattemptid,
773 {$alias}qa.questionusageid,
774 {$alias}qa.slot,
775 {$alias}qa.behaviour,
776 {$alias}qa.questionid,
777 {$alias}qa.maxmark,
778 {$alias}qa.minfraction,
779 {$alias}qa.flagged,
780 {$alias}qa.questionsummary,
781 {$alias}qa.rightanswer,
782 {$alias}qa.responsesummary,
783 {$alias}qa.timemodified,
784 {$alias}qas.id AS attemptstepid,
785 {$alias}qas.sequencenumber,
786 {$alias}qas.state,
787 {$alias}qas.fraction,
788 {$alias}qas.timecreated,
789 {$alias}qas.userid
790
2a3bdbf9
TH
791 FROM {question_attempts} {$alias}qa
792 JOIN {question_attempt_steps} {$alias}qas ON
d1b7e03d
TH
793 {$alias}qas.id = {$this->latest_step_for_qa_subquery($alias . 'qa.id')}
794 ) $alias";
795 }
796
797 protected function latest_step_for_qa_subquery($questionattemptid = 'qa.id') {
d1b7e03d
TH
798 return "(
799 SELECT MAX(id)
c76145d3 800 FROM {question_attempt_steps}
d1b7e03d
TH
801 WHERE questionattemptid = $questionattemptid
802 )";
803 }
804
805 /**
806 * @param array $questionids of question ids.
07f88584
TH
807 * @param qubaid_condition $qubaids ids of the usages to consider.
808 * @return boolean whether any of these questions are being used by any of
809 * those usages.
d1b7e03d 810 */
07f88584 811 public function questions_in_use(array $questionids, qubaid_condition $qubaids) {
c76145d3
TH
812 list($test, $params) = $this->db->get_in_or_equal($questionids);
813 return $this->db->record_exists_select('question_attempts',
07f88584
TH
814 'questionid ' . $test . ' AND questionusageid ' .
815 $qubaids->usage_id_in(), $params + $qubaids->usage_id_in_params());
d1b7e03d
TH
816 }
817}
818
819/**
820 * Implementation of the unit of work pattern for the question engine.
821 *
822 * See http://martinfowler.com/eaaCatalog/unitOfWork.html. This tracks all the
823 * changes to a {@link question_usage_by_activity}, and its constituent parts,
824 * so that the changes can be saved to the database when {@link save()} is called.
825 *
017bc1d9
TH
826 * @copyright 2009 The Open University
827 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
d1b7e03d
TH
828 */
829class question_engine_unit_of_work implements question_usage_observer {
830 /** @var question_usage_by_activity the usage being tracked. */
831 protected $quba;
832
833 /** @var boolean whether any of the fields of the usage have been changed. */
834 protected $modified = false;
835
836 /**
837 * @var array list of number in usage => {@link question_attempt}s that
838 * were already in the usage, and which have been modified.
839 */
840 protected $attemptsmodified = array();
841
842 /**
843 * @var array list of number in usage => {@link question_attempt}s that
844 * have been added to the usage.
845 */
846 protected $attemptsadded = array();
847
848 /**
849 * @var array list of question attempt ids to delete the steps for, before
850 * inserting new steps.
851 */
852 protected $attemptstodeletestepsfor = array();
853
854 /**
855 * @var array list of array(question_attempt_step, question_attempt id, seq number)
856 * of steps that have been added to question attempts in this usage.
857 */
858 protected $stepsadded = array();
859
860 /**
861 * Constructor.
862 * @param question_usage_by_activity $quba the usage to track.
863 */
864 public function __construct(question_usage_by_activity $quba) {
865 $this->quba = $quba;
866 }
867
868 public function notify_modified() {
869 $this->modified = true;
870 }
871
872 public function notify_attempt_modified(question_attempt $qa) {
873 $no = $qa->get_slot();
874 if (!array_key_exists($no, $this->attemptsadded)) {
875 $this->attemptsmodified[$no] = $qa;
876 }
877 }
878
879 public function notify_attempt_added(question_attempt $qa) {
880 $this->attemptsadded[$qa->get_slot()] = $qa;
881 }
882
883 public function notify_delete_attempt_steps(question_attempt $qa) {
884
885 if (array_key_exists($qa->get_slot(), $this->attemptsadded)) {
886 return;
887 }
888
889 $qaid = $qa->get_database_id();
890 foreach ($this->stepsadded as $key => $stepinfo) {
891 if ($stepinfo[1] == $qaid) {
892 unset($this->stepsadded[$key]);
893 }
894 }
895
896 $this->attemptstodeletestepsfor[$qaid] = 1;
897 }
898
899 public function notify_step_added(question_attempt_step $step, question_attempt $qa, $seq) {
900 if (array_key_exists($qa->get_slot(), $this->attemptsadded)) {
901 return;
902 }
903 $this->stepsadded[] = array($step, $qa->get_database_id(), $seq);
904 }
905
906 /**
907 * Write all the changes we have recorded to the database.
908 * @param question_engine_data_mapper $dm the mapper to use to update the database.
909 */
910 public function save(question_engine_data_mapper $dm) {
911 $dm->delete_steps_for_question_attempts(array_keys($this->attemptstodeletestepsfor));
912 foreach ($this->stepsadded as $stepinfo) {
913 list($step, $questionattemptid, $seq) = $stepinfo;
914 $dm->insert_question_attempt_step($step, $questionattemptid, $seq);
915 }
916 foreach ($this->attemptsadded as $qa) {
917 $dm->insert_question_attempt($qa);
918 }
919 foreach ($this->attemptsmodified as $qa) {
920 $dm->update_question_attempt($qa);
921 }
922 if ($this->modified) {
923 $dm->update_questions_usage_by_activity($this->quba);
924 }
925 }
926}
927
928
929/**
930 * This class represents a restriction on the set of question_usage ids to include
931 * in a larger database query. Depending of the how you are going to restrict the
932 * list of usages, construct an appropriate subclass.
933 *
934 * If $qubaids is an instance of this class, example usage might be
935 *
936 * SELECT qa.id, qa.maxmark
937 * FROM $qubaids->from_question_attempts('qa')
938 * WHERE $qubaids->where() AND qa.slot = 1
939 *
f7970e3c 940 * @copyright 2010 The Open University
017bc1d9 941 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
d1b7e03d
TH
942 */
943abstract class qubaid_condition {
944
945 /**
946 * @return string the SQL that needs to go in the FROM clause when trying
947 * to select records from the 'question_attempts' table based on the
948 * qubaid_condition.
949 */
950 public abstract function from_question_attempts($alias);
951
952 /** @return string the SQL that needs to go in the where clause. */
953 public abstract function where();
954
955 /**
956 * @return the params needed by a query that uses
957 * {@link from_question_attempts()} and {@link where()}.
958 */
959 public abstract function from_where_params();
960
961 /**
962 * @return string SQL that can use used in a WHERE qubaid IN (...) query.
963 * This method returns the "IN (...)" part.
964 */
965 public abstract function usage_id_in();
966
967 /**
968 * @return the params needed by a query that uses {@link usage_id_in()}.
969 */
970 public abstract function usage_id_in_params();
971}
972
973
974/**
975 * This class represents a restriction on the set of question_usage ids to include
976 * in a larger database query based on an explicit list of ids.
977 *
f7970e3c 978 * @copyright 2010 The Open University
017bc1d9 979 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
d1b7e03d
TH
980 */
981class qubaid_list extends qubaid_condition {
982 /** @var array of ids. */
983 protected $qubaids;
984 protected $columntotest = null;
985 protected $params;
986
987 /**
988 * Constructor.
989 * @param array $qubaids of question usage ids.
990 */
991 public function __construct(array $qubaids) {
992 $this->qubaids = $qubaids;
993 }
994
995 public function from_question_attempts($alias) {
d1b7e03d 996 $this->columntotest = $alias . '.questionusageid';
9b40c540 997 return '{question_attempts} ' . $alias;
d1b7e03d
TH
998 }
999
1000 public function where() {
1001 global $DB;
1002
1003 if (is_null($this->columntotest)) {
88f0eb15 1004 throw new coding_exception('Must call from_question_attempts before where().');
d1b7e03d
TH
1005 }
1006 if (empty($this->qubaids)) {
9b40c540 1007 $this->params = array();
d1b7e03d
TH
1008 return '1 = 0';
1009 }
1010 list($where, $this->params) = $DB->get_in_or_equal($this->qubaids, SQL_PARAMS_NAMED, 'qubaid0000');
1011
9b40c540 1012 return $this->columntotest . ' ' . $this->usage_id_in();
d1b7e03d
TH
1013 }
1014
1015 public function from_where_params() {
1016 return $this->params;
1017 }
1018
1019 public function usage_id_in() {
1020 global $DB;
1021
1022 if (empty($this->qubaids)) {
1023 return '= 0';
1024 }
1025 list($where, $this->params) = $DB->get_in_or_equal($this->qubaids, SQL_PARAMS_NAMED, 'qubaid0000');
1026 return $where;
1027 }
1028
1029 public function usage_id_in_params() {
1030 return $this->params;
1031 }
1032}
1033
1034
1035/**
1036 * This class represents a restriction on the set of question_usage ids to include
1037 * in a larger database query based on JOINing to some other tables.
1038 *
1039 * The general form of the query is something like
1040 *
1041 * SELECT qa.id, qa.maxmark
1042 * FROM $from
2a3bdbf9 1043 * JOIN {question_attempts} qa ON qa.questionusageid = $usageidcolumn
d1b7e03d
TH
1044 * WHERE $where AND qa.slot = 1
1045 *
1046 * where $from, $usageidcolumn and $where are the arguments to the constructor.
1047 *
f7970e3c 1048 * @copyright 2010 The Open University
017bc1d9 1049 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
d1b7e03d
TH
1050 */
1051class qubaid_join extends qubaid_condition {
1052 public $from;
1053 public $usageidcolumn;
1054 public $where;
1055 public $params;
1056
1057 /**
1058 * Constructor. The meaning of the arguments is explained in the class comment.
1059 * @param string $from SQL fragemnt to go in the FROM clause.
1060 * @param string $usageidcolumn the column in $from that should be
1061 * made equal to the usageid column in the JOIN clause.
1062 * @param string $where SQL fragment to go in the where clause.
e24ee794 1063 * @param array $params required by the SQL. You must use named parameters.
d1b7e03d
TH
1064 */
1065 public function __construct($from, $usageidcolumn, $where = '', $params = array()) {
1066 $this->from = $from;
1067 $this->usageidcolumn = $usageidcolumn;
1068 $this->params = $params;
1069 if (empty($where)) {
1070 $where = '1 = 1';
1071 }
1072 $this->where = $where;
1073 }
1074
1075 public function from_question_attempts($alias) {
d1b7e03d 1076 return "$this->from
0f33deaf 1077 JOIN {question_attempts} {$alias} ON " .
d1b7e03d
TH
1078 "{$alias}.questionusageid = $this->usageidcolumn";
1079 }
1080
1081 public function where() {
1082 return $this->where;
1083 }
1084
1085 public function from_where_params() {
1086 return $this->params;
1087 }
1088
1089 public function usage_id_in() {
1090 return "IN (SELECT $this->usageidcolumn FROM $this->from WHERE $this->where)";
1091 }
1092
1093 public function usage_id_in_params() {
1094 return $this->params;
1095 }
1096}