MDL-20636 Add missing defined('MOODLE_INTERNAL') || die();
[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
TH
18/**
19 * Code for loading and saving quiz attempts to and from the database.
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
TH
64 $record->contextid = $quba->get_owning_context()->id;
65 $record->component = addslashes($quba->get_owning_component());
66 $record->preferredbehaviour = addslashes($quba->get_preferred_behaviour());
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();
85 $record->behaviour = addslashes($qa->get_behaviour_name());
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();
90 $record->questionsummary = addslashes($qa->get_question_summary());
91 $record->rightanswer = addslashes($qa->get_right_answer_summary());
92 $record->responsesummary = addslashes($qa->get_response_summary());
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;
110 $record->state = addslashes('' . $step->get_state());
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
TH
119 $data->attemptstepid = $record->id;
120 $data->name = addslashes($name);
121 $data->value = addslashes($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.
128 * @param integer $stepid the id of the step to load.
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) {
153 throw new Exception('Failed to load question_attempt_step ' . $stepid);
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.
162 * @param integer $questionattemptid the id of the question attempt to load.
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) {
205 throw new Exception('Failed to load question_attempt ' . $questionattemptid);
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.
216 * @param integer $qubaid the id of the usage to load.
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) {
262 throw new Exception('Failed to load questions_usage_by_activity ' . $qubaid);
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 /**
314 * Load summary information about the state of each question in a group of attempts.
315 * This is used by the quiz manual grading report, to show how many attempts
316 * at each question need to be graded.
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}.
397 * @param integer $slot The slot for the questions you want to konw about.
398 * @param integer $questionid (optional) Only return attempts that were of this specific question.
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.
d1b7e03d
TH
402 * @param integer $limitfrom implements paging of the results.
403 * Ignored if $orderby = random or $limitnum is null.
404 * @param integer $limitnum implements paging of the results. null = all.
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.
522 * @param integer $questionid the question to load all the attempts fors.
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;
599 $record->component = addslashes($quba->get_owning_component());
600 $record->preferredbehaviour = addslashes($quba->get_preferred_behaviour());
601
c76145d3 602 if (!$this->db->update_record('question_usages', $record)) {
d1b7e03d
TH
603 throw new Exception('Failed to update question_usage_by_activity ' . $record->id);
604 }
605 }
606
607 /**
608 * Update a question_attempts row to refect any changes in a question_attempt
609 * (but not any of its steps).
610 * @param question_attempt $qa the question attempt that has changed.
611 */
612 public function update_question_attempt(question_attempt $qa) {
0ff4bd08 613 $record = new stdClass();
d1b7e03d
TH
614 $record->id = $qa->get_database_id();
615 $record->maxmark = $qa->get_max_mark();
616 $record->minfraction = $qa->get_min_fraction();
617 $record->flagged = $qa->is_flagged();
618 $record->questionsummary = addslashes($qa->get_question_summary());
619 $record->rightanswer = addslashes($qa->get_right_answer_summary());
620 $record->responsesummary = addslashes($qa->get_response_summary());
621 $record->timemodified = time();
622
c76145d3 623 if (!$this->db->update_record('question_attempts', $record)) {
d1b7e03d
TH
624 throw new Exception('Failed to update question_attempt ' . $record->id);
625 }
626 }
627
628 /**
629 * Delete a question_usage_by_activity and all its associated
630 * {@link question_attempts} and {@link question_attempt_steps} from the
631 * database.
632 * @param string $where a where clause. Becuase of MySQL limitations, you
2a3bdbf9 633 * must refer to {question_usages}.id in full like that.
c76145d3 634 * @param array $params values to substitute for placeholders in $where.
d1b7e03d 635 */
c76145d3 636 public function delete_questions_usage_by_activities($where, $params) {
c76145d3 637 $this->db->delete_records_select('question_attempt_step_data', "attemptstepid IN (
d1b7e03d 638 SELECT qas.id
c76145d3
TH
639 FROM {question_attempts} qa
640 JOIN {question_attempt_steps} qas ON qas.questionattemptid = qa.id
641 JOIN {question_usages} ON qa.questionusageid = {question_usages}.id
642 WHERE $where)", $params);
643 $this->db->delete_records_select('question_attempt_steps', "questionattemptid IN (
d1b7e03d 644 SELECT qa.id
c76145d3
TH
645 FROM {question_attempts} qa
646 JOIN {question_usages} ON qa.questionusageid = {question_usages}.id
647 WHERE $where)", $params);
648 $this->db->delete_records_select('question_attempts', "questionusageid IN (
d1b7e03d 649 SELECT id
c76145d3
TH
650 FROM {question_usages}
651 WHERE $where)", $params);
652 $this->db->delete_records_select('question_usages', $where, $params);
d1b7e03d
TH
653 }
654
655 /**
656 * Delete all the steps for a question attempt.
657 * @param integer $qaids question_attempt id.
658 */
659 public function delete_steps_for_question_attempts($qaids) {
d1b7e03d
TH
660 if (empty($qaids)) {
661 return;
662 }
e24ee794 663 list($test, $params) = $this->db->get_in_or_equal($qaids);
c76145d3 664 $this->db->delete_records_select('question_attempt_step_data', "attemptstepid IN (
d1b7e03d 665 SELECT qas.id
c76145d3
TH
666 FROM {question_attempt_steps} qas
667 WHERE questionattemptid $test)", $params);
668 $this->db->delete_records_select('question_attempt_steps', 'questionattemptid ' . $test, $params);
d1b7e03d
TH
669 }
670
671 /**
672 * Delete all the previews for a given question.
673 * @param integer $questionid question id.
674 */
675 public function delete_previews($questionid) {
c76145d3 676 $previews = $this->db->get_records_sql_menu("
d1b7e03d 677 SELECT DISTINCT quba.id, 1
c76145d3
TH
678 FROM {question_usages} quba
679 JOIN {question_attempts} qa ON qa.questionusageid = quba.id
d1b7e03d 680 WHERE quba.component = 'core_question_preview' AND
c76145d3 681 qa.questionid = ?", array($questionid));
d1b7e03d
TH
682 if (empty($previews)) {
683 return;
684 }
c76145d3
TH
685 list($test, $params) = $this->db->get_in_or_equal(array_keys($previews));
686 $this->delete_questions_usage_by_activities('question_usages.id ' . $test, $params);
d1b7e03d
TH
687 }
688
689 /**
690 * Update the flagged state of a question in the database.
691 * @param integer $qubaid the question usage id.
692 * @param integer $questionid the question id.
693 * @param integer $sessionid the question_attempt id.
694 * @param boolean $newstate the new state of the flag. true = flagged.
695 */
06f8ed54 696 public function update_question_attempt_flag($qubaid, $questionid, $qaid, $slot, $newstate) {
f9b0500f 697 if (!$this->db->record_exists('question_attempts', array('id' => $qaid,
06f8ed54 698 'questionusageid' => $qubaid, 'questionid' => $questionid, 'slot' => $slot))) {
d1b7e03d
TH
699 throw new Exception('invalid ids');
700 }
701
06f8ed54 702 $this->db->set_field('question_attempts', 'flagged', $newstate, array('id' => $qaid));
d1b7e03d
TH
703 }
704
705 /**
706 * Get all the WHEN 'x' THEN 'y' terms needed to convert the question_attempt_steps.state
707 * column to a summary state. Use this like
708 * CASE qas.state {$this->full_states_to_summary_state_sql()} END AS summarystate,
709 * @param string SQL fragment.
710 */
711 protected function full_states_to_summary_state_sql() {
712 $sql = '';
713 foreach (question_state::get_all() as $state) {
714 $sql .= "WHEN '$state' THEN '{$state->get_summary_state()}'\n";
715 }
716 return $sql;
717 }
718
719 /**
720 * Get the SQL needed to test that question_attempt_steps.state is in a
721 * state corresponding to $summarystate.
722 * @param string $summarystate one of
723 * inprogress, needsgrading, manuallygraded or autograded
724 * @param boolean $equal if false, do a NOT IN test. Default true.
725 * @return string SQL fragment.
726 */
cf3b6568 727 public function in_summary_state_test($summarystate, $equal = true, $prefix = 'summarystates') {
d1b7e03d 728 $states = question_state::get_all_for_summary_state($summarystate);
cf3b6568 729 return $this->db->get_in_or_equal($states, SQL_PARAMS_NAMED, $prefix . '00', $equal);
d1b7e03d
TH
730 }
731
732 /**
733 * Change the maxmark for the question_attempt with number in usage $slot
734 * for all the specified question_attempts.
735 * @param qubaid_condition $qubaids Selects which usages are updated.
736 * @param integer $slot the number is usage to affect.
737 * @param number $newmaxmark the new max mark to set.
738 */
739 public function set_max_mark_in_attempts(qubaid_condition $qubaids, $slot, $newmaxmark) {
c76145d3
TH
740 $this->db->set_field_select('question_attempts', 'maxmark', $newmaxmark,
741 "questionusageid {$qubaids->usage_id_in()} AND slot = :slot",
742 $qubaids->usage_id_in_params() + array('slot' => $slot));
d1b7e03d
TH
743 }
744
745 /**
746 * Return a subquery that computes the sum of the marks for all the questions
747 * in a usage. Which useage to compute the sum for is controlled bu the $qubaid
748 * parameter.
749 *
750 * See {@link quiz_update_all_attempt_sumgrades()} for an example of the usage of
751 * this method.
752 *
753 * @param string $qubaid SQL fragment that controls which usage is summed.
754 * This might be the name of a column in the outer query.
755 * @return string SQL code for the subquery.
756 */
757 public function sum_usage_marks_subquery($qubaid) {
d1b7e03d 758 return "SELECT SUM(qa.maxmark * qas.fraction)
c76145d3 759 FROM {question_attempts} qa
d1b7e03d
TH
760 JOIN (
761 SELECT summarks_qa.id AS questionattemptid, MAX(summarks_qas.id) AS latestid
c76145d3
TH
762 FROM {question_attempt_steps} summarks_qas
763 JOIN {question_attempts} summarks_qa ON summarks_qa.id = summarks_qas.questionattemptid
d1b7e03d
TH
764 WHERE summarks_qa.questionusageid = $qubaid
765 GROUP BY summarks_qa.id
766 ) lateststepid ON lateststepid.questionattemptid = qa.id
c76145d3 767 JOIN {question_attempt_steps} qas ON qas.id = lateststepid.latestid
d1b7e03d 768 WHERE qa.questionusageid = $qubaid
f86390dc 769 HAVING COUNT(CASE WHEN qas.state = 'needsgrading' AND qa.maxmark > 0 THEN 1 ELSE NULL END) = 0";
c76145d3 770 // TODO handle $qubaid with placeholders.
d1b7e03d
TH
771 }
772
773 public function question_attempt_latest_state_view($alias) {
d1b7e03d
TH
774 return "(
775 SELECT
776 {$alias}qa.id AS questionattemptid,
777 {$alias}qa.questionusageid,
778 {$alias}qa.slot,
779 {$alias}qa.behaviour,
780 {$alias}qa.questionid,
781 {$alias}qa.maxmark,
782 {$alias}qa.minfraction,
783 {$alias}qa.flagged,
784 {$alias}qa.questionsummary,
785 {$alias}qa.rightanswer,
786 {$alias}qa.responsesummary,
787 {$alias}qa.timemodified,
788 {$alias}qas.id AS attemptstepid,
789 {$alias}qas.sequencenumber,
790 {$alias}qas.state,
791 {$alias}qas.fraction,
792 {$alias}qas.timecreated,
793 {$alias}qas.userid
794
2a3bdbf9
TH
795 FROM {question_attempts} {$alias}qa
796 JOIN {question_attempt_steps} {$alias}qas ON
d1b7e03d
TH
797 {$alias}qas.id = {$this->latest_step_for_qa_subquery($alias . 'qa.id')}
798 ) $alias";
799 }
800
801 protected function latest_step_for_qa_subquery($questionattemptid = 'qa.id') {
d1b7e03d
TH
802 return "(
803 SELECT MAX(id)
c76145d3 804 FROM {question_attempt_steps}
d1b7e03d
TH
805 WHERE questionattemptid = $questionattemptid
806 )";
807 }
808
809 /**
810 * @param array $questionids of question ids.
811 * @return boolean whether any of these questions are being used by the question engine.
812 */
32d8935c 813 public function questions_in_use(array $questionids) {
c76145d3
TH
814 list($test, $params) = $this->db->get_in_or_equal($questionids);
815 return $this->db->record_exists_select('question_attempts',
816 'questionid ' . $test, $params);
d1b7e03d
TH
817 }
818}
819
820/**
821 * Implementation of the unit of work pattern for the question engine.
822 *
823 * See http://martinfowler.com/eaaCatalog/unitOfWork.html. This tracks all the
824 * changes to a {@link question_usage_by_activity}, and its constituent parts,
825 * so that the changes can be saved to the database when {@link save()} is called.
826 *
017bc1d9
TH
827 * @copyright 2009 The Open University
828 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
d1b7e03d
TH
829 */
830class question_engine_unit_of_work implements question_usage_observer {
831 /** @var question_usage_by_activity the usage being tracked. */
832 protected $quba;
833
834 /** @var boolean whether any of the fields of the usage have been changed. */
835 protected $modified = false;
836
837 /**
838 * @var array list of number in usage => {@link question_attempt}s that
839 * were already in the usage, and which have been modified.
840 */
841 protected $attemptsmodified = array();
842
843 /**
844 * @var array list of number in usage => {@link question_attempt}s that
845 * have been added to the usage.
846 */
847 protected $attemptsadded = array();
848
849 /**
850 * @var array list of question attempt ids to delete the steps for, before
851 * inserting new steps.
852 */
853 protected $attemptstodeletestepsfor = array();
854
855 /**
856 * @var array list of array(question_attempt_step, question_attempt id, seq number)
857 * of steps that have been added to question attempts in this usage.
858 */
859 protected $stepsadded = array();
860
861 /**
862 * Constructor.
863 * @param question_usage_by_activity $quba the usage to track.
864 */
865 public function __construct(question_usage_by_activity $quba) {
866 $this->quba = $quba;
867 }
868
869 public function notify_modified() {
870 $this->modified = true;
871 }
872
873 public function notify_attempt_modified(question_attempt $qa) {
874 $no = $qa->get_slot();
875 if (!array_key_exists($no, $this->attemptsadded)) {
876 $this->attemptsmodified[$no] = $qa;
877 }
878 }
879
880 public function notify_attempt_added(question_attempt $qa) {
881 $this->attemptsadded[$qa->get_slot()] = $qa;
882 }
883
884 public function notify_delete_attempt_steps(question_attempt $qa) {
885
886 if (array_key_exists($qa->get_slot(), $this->attemptsadded)) {
887 return;
888 }
889
890 $qaid = $qa->get_database_id();
891 foreach ($this->stepsadded as $key => $stepinfo) {
892 if ($stepinfo[1] == $qaid) {
893 unset($this->stepsadded[$key]);
894 }
895 }
896
897 $this->attemptstodeletestepsfor[$qaid] = 1;
898 }
899
900 public function notify_step_added(question_attempt_step $step, question_attempt $qa, $seq) {
901 if (array_key_exists($qa->get_slot(), $this->attemptsadded)) {
902 return;
903 }
904 $this->stepsadded[] = array($step, $qa->get_database_id(), $seq);
905 }
906
907 /**
908 * Write all the changes we have recorded to the database.
909 * @param question_engine_data_mapper $dm the mapper to use to update the database.
910 */
911 public function save(question_engine_data_mapper $dm) {
912 $dm->delete_steps_for_question_attempts(array_keys($this->attemptstodeletestepsfor));
913 foreach ($this->stepsadded as $stepinfo) {
914 list($step, $questionattemptid, $seq) = $stepinfo;
915 $dm->insert_question_attempt_step($step, $questionattemptid, $seq);
916 }
917 foreach ($this->attemptsadded as $qa) {
918 $dm->insert_question_attempt($qa);
919 }
920 foreach ($this->attemptsmodified as $qa) {
921 $dm->update_question_attempt($qa);
922 }
923 if ($this->modified) {
924 $dm->update_questions_usage_by_activity($this->quba);
925 }
926 }
927}
928
929
930/**
931 * This class represents a restriction on the set of question_usage ids to include
932 * in a larger database query. Depending of the how you are going to restrict the
933 * list of usages, construct an appropriate subclass.
934 *
935 * If $qubaids is an instance of this class, example usage might be
936 *
937 * SELECT qa.id, qa.maxmark
938 * FROM $qubaids->from_question_attempts('qa')
939 * WHERE $qubaids->where() AND qa.slot = 1
940 *
941 * @copyright 2010 The Open University
017bc1d9 942 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
d1b7e03d
TH
943 */
944abstract class qubaid_condition {
945
946 /**
947 * @return string the SQL that needs to go in the FROM clause when trying
948 * to select records from the 'question_attempts' table based on the
949 * qubaid_condition.
950 */
951 public abstract function from_question_attempts($alias);
952
953 /** @return string the SQL that needs to go in the where clause. */
954 public abstract function where();
955
956 /**
957 * @return the params needed by a query that uses
958 * {@link from_question_attempts()} and {@link where()}.
959 */
960 public abstract function from_where_params();
961
962 /**
963 * @return string SQL that can use used in a WHERE qubaid IN (...) query.
964 * This method returns the "IN (...)" part.
965 */
966 public abstract function usage_id_in();
967
968 /**
969 * @return the params needed by a query that uses {@link usage_id_in()}.
970 */
971 public abstract function usage_id_in_params();
972}
973
974
975/**
976 * This class represents a restriction on the set of question_usage ids to include
977 * in a larger database query based on an explicit list of ids.
978 *
979 * @copyright 2010 The Open University
017bc1d9 980 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
d1b7e03d
TH
981 */
982class qubaid_list extends qubaid_condition {
983 /** @var array of ids. */
984 protected $qubaids;
985 protected $columntotest = null;
986 protected $params;
987
988 /**
989 * Constructor.
990 * @param array $qubaids of question usage ids.
991 */
992 public function __construct(array $qubaids) {
993 $this->qubaids = $qubaids;
994 }
995
996 public function from_question_attempts($alias) {
d1b7e03d 997 $this->columntotest = $alias . '.questionusageid';
9b40c540 998 return '{question_attempts} ' . $alias;
d1b7e03d
TH
999 }
1000
1001 public function where() {
1002 global $DB;
1003
1004 if (is_null($this->columntotest)) {
1005 throw new coding_exception('Must call another method that before where().');
1006 }
1007 if (empty($this->qubaids)) {
9b40c540 1008 $this->params = array();
d1b7e03d
TH
1009 return '1 = 0';
1010 }
1011 list($where, $this->params) = $DB->get_in_or_equal($this->qubaids, SQL_PARAMS_NAMED, 'qubaid0000');
1012
9b40c540 1013 return $this->columntotest . ' ' . $this->usage_id_in();
d1b7e03d
TH
1014 }
1015
1016 public function from_where_params() {
1017 return $this->params;
1018 }
1019
1020 public function usage_id_in() {
1021 global $DB;
1022
1023 if (empty($this->qubaids)) {
1024 return '= 0';
1025 }
1026 list($where, $this->params) = $DB->get_in_or_equal($this->qubaids, SQL_PARAMS_NAMED, 'qubaid0000');
1027 return $where;
1028 }
1029
1030 public function usage_id_in_params() {
1031 return $this->params;
1032 }
1033}
1034
1035
1036/**
1037 * This class represents a restriction on the set of question_usage ids to include
1038 * in a larger database query based on JOINing to some other tables.
1039 *
1040 * The general form of the query is something like
1041 *
1042 * SELECT qa.id, qa.maxmark
1043 * FROM $from
2a3bdbf9 1044 * JOIN {question_attempts} qa ON qa.questionusageid = $usageidcolumn
d1b7e03d
TH
1045 * WHERE $where AND qa.slot = 1
1046 *
1047 * where $from, $usageidcolumn and $where are the arguments to the constructor.
1048 *
1049 * @copyright 2010 The Open University
017bc1d9 1050 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
d1b7e03d
TH
1051 */
1052class qubaid_join extends qubaid_condition {
1053 public $from;
1054 public $usageidcolumn;
1055 public $where;
1056 public $params;
1057
1058 /**
1059 * Constructor. The meaning of the arguments is explained in the class comment.
1060 * @param string $from SQL fragemnt to go in the FROM clause.
1061 * @param string $usageidcolumn the column in $from that should be
1062 * made equal to the usageid column in the JOIN clause.
1063 * @param string $where SQL fragment to go in the where clause.
e24ee794 1064 * @param array $params required by the SQL. You must use named parameters.
d1b7e03d
TH
1065 */
1066 public function __construct($from, $usageidcolumn, $where = '', $params = array()) {
1067 $this->from = $from;
1068 $this->usageidcolumn = $usageidcolumn;
1069 $this->params = $params;
1070 if (empty($where)) {
1071 $where = '1 = 1';
1072 }
1073 $this->where = $where;
1074 }
1075
1076 public function from_question_attempts($alias) {
d1b7e03d 1077 return "$this->from
0f33deaf 1078 JOIN {question_attempts} {$alias} ON " .
d1b7e03d
TH
1079 "{$alias}.questionusageid = $this->usageidcolumn";
1080 }
1081
1082 public function where() {
1083 return $this->where;
1084 }
1085
1086 public function from_where_params() {
1087 return $this->params;
1088 }
1089
1090 public function usage_id_in() {
1091 return "IN (SELECT $this->usageidcolumn FROM $this->from WHERE $this->where)";
1092 }
1093
1094 public function usage_id_in_params() {
1095 return $this->params;
1096 }
1097}