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