MDL-20636 Fix install
[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,
7a719748 167 quba.contextid,
d1b7e03d
TH
168 quba.preferredbehaviour,
169 qa.id AS questionattemptid,
170 qa.questionusageid,
171 qa.slot,
172 qa.behaviour,
173 qa.questionid,
174 qa.maxmark,
175 qa.minfraction,
176 qa.flagged,
177 qa.questionsummary,
178 qa.rightanswer,
179 qa.responsesummary,
180 qa.timemodified,
181 qas.id AS attemptstepid,
182 qas.sequencenumber,
183 qas.state,
184 qas.fraction,
185 qas.timecreated,
186 qas.userid,
187 qasd.name,
188 qasd.value
189
06f8ed54
TH
190FROM {question_attempts qa
191JOIN {question_usages} quba ON quba.id = qa.questionusageid
192LEFT JOIN {question_attempt_steps} qas ON qas.questionattemptid = qa.id
193LEFT JOIN {question_attempt_step_data} qasd ON qasd.attemptstepid = qas.id
d1b7e03d
TH
194
195WHERE
06f8ed54 196 qa.id = :questionattemptid
d1b7e03d
TH
197
198ORDER BY
199 qas.sequencenumber
06f8ed54 200 ", array('questionattemptid' => $questionattemptid));
d1b7e03d
TH
201
202 if (!$records) {
203 throw new Exception('Failed to load question_attempt ' . $questionattemptid);
204 }
205
206 $record = current($records);
207 return question_attempt::load_from_records($records, $questionattemptid,
208 new question_usage_null_observer(), $record->preferredbehaviour);
209 }
210
211 /**
212 * Load a {@link question_usage_by_activity} from the database, including
213 * all its {@link question_attempt}s and all their steps.
214 * @param integer $qubaid the id of the usage to load.
215 * @param question_usage_by_activity the usage that was loaded.
216 */
217 public function load_questions_usage_by_activity($qubaid) {
06f8ed54 218 $records = $this->db->get_records_sql("
d1b7e03d
TH
219SELECT
220 COALESCE(qasd.id, -1 * qas.id) AS id,
221 quba.id AS qubaid,
222 quba.contextid,
223 quba.component,
224 quba.preferredbehaviour,
225 qa.id AS questionattemptid,
226 qa.questionusageid,
227 qa.slot,
228 qa.behaviour,
229 qa.questionid,
230 qa.maxmark,
231 qa.minfraction,
232 qa.flagged,
233 qa.questionsummary,
234 qa.rightanswer,
235 qa.responsesummary,
236 qa.timemodified,
237 qas.id AS attemptstepid,
238 qas.sequencenumber,
239 qas.state,
240 qas.fraction,
241 qas.timecreated,
242 qas.userid,
243 qasd.name,
244 qasd.value
245
06f8ed54
TH
246FROM {question_usages} quba
247LEFT JOIN {question_attempts} qa ON qa.questionusageid = quba.id
248LEFT JOIN {question_attempt_steps} qas ON qas.questionattemptid = qa.id
249LEFT JOIN {question_attempt_step_data} qasd ON qasd.attemptstepid = qas.id
d1b7e03d
TH
250
251WHERE
06f8ed54 252 quba.id = :qubaid
d1b7e03d
TH
253
254ORDER BY
255 qa.slot,
256 qas.sequencenumber
c76145d3 257 ", array('qubaid' => $qubaid));
d1b7e03d
TH
258
259 if (!$records) {
260 throw new Exception('Failed to load questions_usage_by_activity ' . $qubaid);
261 }
262
263 return question_usage_by_activity::load_from_records($records, $qubaid);
264 }
265
266 /**
267 * Load information about the latest state of each question from the database.
268 *
269 * @param qubaid_condition $qubaids used to restrict which usages are included
270 * in the query. See {@link qubaid_condition}.
271 * @param array $slots A list of slots for the questions you want to konw about.
272 * @return array of records. See the SQL in this function to see the fields available.
273 */
274 public function load_questions_usages_latest_steps(qubaid_condition $qubaids, $slots) {
c76145d3 275 list($slottest, $params) = $this->db->get_in_or_equal($slots, SQL_PARAMS_NAMED, 'slot0000');
d1b7e03d
TH
276
277 $records = get_records_sql("
278SELECT
279 qas.id,
280 qa.id AS questionattemptid,
281 qa.questionusageid,
282 qa.slot,
283 qa.behaviour,
284 qa.questionid,
285 qa.maxmark,
286 qa.minfraction,
287 qa.flagged,
288 qa.questionsummary,
289 qa.rightanswer,
290 qa.responsesummary,
291 qa.timemodified,
292 qas.id AS attemptstepid,
293 qas.sequencenumber,
294 qas.state,
295 qas.fraction,
296 qas.timecreated,
297 qas.userid
298
299FROM {$qubaids->from_question_attempts('qa')}
06f8ed54 300JOIN {question_attempt_steps} qas ON
d1b7e03d
TH
301 qas.id = {$this->latest_step_for_qa_subquery()}
302
303WHERE
304 {$qubaids->where()} AND
305 qa.slot $slottest
06f8ed54 306 ", $params + $qubaids->from_where_params());
d1b7e03d
TH
307
308 return $records;
309 }
310
311 /**
312 * Load summary information about the state of each question in a group of attempts.
313 * This is used by the quiz manual grading report, to show how many attempts
314 * at each question need to be graded.
315 *
316 * @param qubaid_condition $qubaids used to restrict which usages are included
317 * in the query. See {@link qubaid_condition}.
318 * @param array $slots A list of slots for the questions you want to konw about.
319 * @return array The array keys are slot,qestionid. The values are objects with
320 * fields $slot, $questionid, $inprogress, $name, $needsgrading, $autograded,
321 * $manuallygraded and $all.
322 */
323 public function load_questions_usages_question_state_summary(qubaid_condition $qubaids, $slots) {
d1b7e03d
TH
324 list($slottest, $params) = get_in_or_equal($slots, SQL_PARAMS_NAMED, 'slot0000');
325
06f8ed54 326 $rs = $this->db->get_recordset_sql("
d1b7e03d
TH
327SELECT
328 qa.slot,
329 qa.questionid,
330 q.name,
331 CASE qas.state
332 {$this->full_states_to_summary_state_sql()}
333 END AS summarystate,
334 COUNT(1) AS numattempts
335
336FROM {$qubaids->from_question_attempts('qa')}
06f8ed54 337JOIN {question_attempt_steps} qas ON
d1b7e03d 338 qas.id = {$this->latest_step_for_qa_subquery()}
06f8ed54 339JOIN {question} q ON q.id = qa.questionid
d1b7e03d
TH
340
341WHERE
342 {$qubaids->where()} AND
343 qa.slot $slottest
344
345GROUP BY
346 qa.slot,
347 qa.questionid,
348 q.name,
349 q.id,
350 summarystate
351
f9b0500f 352ORDER BY
d1b7e03d
TH
353 qa.slot,
354 qa.questionid,
355 q.name,
356 q.id
06f8ed54 357 ", $params + $qubaids->from_where_params());
d1b7e03d
TH
358
359 if (!$rs) {
360 throw new moodle_exception('errorloadingdata');
361 }
362
363 $results = array();
364 while ($row = rs_fetch_next_record($rs)) {
365 $index = $row->slot . ',' . $row->questionid;
366
367 if (!array_key_exists($index, $results)) {
368 $res = new stdClass;
369 $res->slot = $row->slot;
370 $res->questionid = $row->questionid;
371 $res->name = $row->name;
372 $res->inprogress = 0;
373 $res->needsgrading = 0;
374 $res->autograded = 0;
375 $res->manuallygraded = 0;
376 $res->all = 0;
377 $results[$index] = $res;
378 }
379
380 $results[$index]->{$row->summarystate} = $row->numattempts;
381 $results[$index]->all += $row->numattempts;
382 }
383 rs_close($rs);
384
385 return $results;
386 }
387
388 /**
389 * Get a list of usage ids where the question with slot $slot, and optionally
390 * also with question id $questionid, is in summary state $summarystate. Also
391 * return the total count of such states.
392 *
393 * Only a subset of the ids can be returned by using $orderby, $limitfrom and
394 * $limitnum. A special value 'random' can be passed as $orderby, in which case
395 * $limitfrom is ignored.
396 *
397 * @param qubaid_condition $qubaids used to restrict which usages are included
398 * in the query. See {@link qubaid_condition}.
399 * @param integer $slot The slot for the questions you want to konw about.
400 * @param integer $questionid (optional) Only return attempts that were of this specific question.
401 * @param string $summarystate the summary state of interest, or 'all'.
402 * @param string $orderby the column to order by.
403 * @param integer $limitfrom implements paging of the results.
404 * Ignored if $orderby = random or $limitnum is null.
405 * @param integer $limitnum implements paging of the results. null = all.
406 * @return array with two elements, an array of usage ids, and a count of the total number.
407 */
408 public function load_questions_usages_where_question_in_state(
409 qubaid_condition $qubaids, $summarystate, $slot, $questionid = null,
410 $orderby = 'random', $limitfrom = 0, $limitnum = null) {
411 global $CFG;
412
413 $extrawhere = '';
414 if ($questionid) {
415 $extrawhere .= ' AND qa.questionid = ' . $questionid;
416 }
417 if ($summarystate != 'all') {
418 $test = $this->in_summary_state_test($summarystate);
419 $extrawhere .= ' AND qas.state ' . $test;
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.
436 $qubaids = get_records_sql_menu("
437SELECT
438 qa.questionusageid,
439 1
440
441FROM {$qubaids->from_question_attempts('qa')}
442JOIN {$CFG->prefix}question_attempt_steps qas ON
443 qas.id = {$this->latest_step_for_qa_subquery()}
444JOIN {$CFG->prefix}question q ON q.id = qa.questionid
445
446WHERE
447 {$qubaids->where()} AND
448 qa.slot = $slot
449 $extrawhere
450
451$sqlorderby
452 ");
453
454 $qubaids = array_keys($qubaids);
455 $count = count($qubaids);
456
457 if ($orderby == 'random') {
458 shuffle($qubaids);
459 $limitfrom = 0;
460 }
461
462 if (!is_null($limitnum)) {
463 $qubaids = array_slice($qubaids, $limitfrom, $limitnum);
464 }
465
466 return array($qubaids, $count);
467 }
468
469 /**
470 * Load a {@link question_usage_by_activity} from the database, including
471 * all its {@link question_attempt}s and all their steps.
472 * @param qubaid_condition $qubaids used to restrict which usages are included
473 * in the query. See {@link qubaid_condition}.
474 * @param array $slots if null, load info for all quesitions, otherwise only
475 * load the averages for the specified questions.
476 */
477 public function load_average_marks(qubaid_condition $qubaids, $slots = null) {
478 global $CFG;
479
480 if (!empty($slots)) {
481 list($slottest, $params) = get_in_or_equal($slots, SQL_PARAMS_NAMED, 'slot0000');
482 $slotwhere = " AND qa.slot $slottest";
483 } else {
484 $slotwhere = '';
485 }
486
487 list($statetest) = get_in_or_equal(array(
488 question_state::$gaveup,
489 question_state::$gradedwrong,
490 question_state::$gradedpartial,
491 question_state::$gradedright,
492 question_state::$mangaveup,
493 question_state::$mangrwrong,
494 question_state::$mangrpartial,
495 question_state::$mangrright));
496
497 $records = get_records_sql("
498SELECT
499 qa.slot,
500 AVG(COALESCE(qas.fraction, 0)) AS averagefraction,
501 COUNT(1) AS numaveraged
502
503FROM {$qubaids->from_question_attempts('qa')}
504JOIN {$CFG->prefix}question_attempt_steps qas ON
505 qas.id = {$this->latest_step_for_qa_subquery()}
506
507WHERE
508 {$qubaids->where()}
509 $slotwhere
510 AND qas.state $statetest
511
512GROUP BY qa.slot
513
514ORDER BY qa.slot
515 ");
516
517 return $records;
518 }
519
520 /**
521 * Load a {@link question_attempt} from the database, including all its
522 * steps.
523 * @param integer $questionid the question to load all the attempts fors.
524 * @param qubaid_condition $qubaids used to restrict which usages are included
525 * in the query. See {@link qubaid_condition}.
526 * @return array of question_attempts.
527 */
528 public function load_attempts_at_question($questionid, qubaid_condition $qubaids) {
529 global $CFG;
530 $records = get_records_sql("
531SELECT
532 COALESCE(qasd.id, -1 * qas.id) AS id,
533 quba.preferredbehaviour,
534 qa.id AS questionattemptid,
535 qa.questionusageid,
536 qa.slot,
537 qa.behaviour,
538 qa.questionid,
539 qa.maxmark,
540 qa.minfraction,
541 qa.flagged,
542 qa.questionsummary,
543 qa.rightanswer,
544 qa.responsesummary,
545 qa.timemodified,
546 qas.id AS attemptstepid,
547 qas.sequencenumber,
548 qas.state,
549 qas.fraction,
550 qas.timecreated,
551 qas.userid,
552 qasd.name,
553 qasd.value
554
555FROM {$qubaids->from_question_attempts('qa')}
556JOIN {$CFG->prefix}question_usages quba ON quba.id = qa.questionusageid
557LEFT JOIN {$CFG->prefix}question_attempt_steps qas ON qas.questionattemptid = qa.id
558LEFT JOIN {$CFG->prefix}question_attempt_step_data qasd ON qasd.attemptstepid = qas.id
559
560WHERE
561 {$qubaids->where()} AND
562 qa.questionid = $questionid
563
564ORDER BY
565 quba.id,
566 qa.id,
567 qas.sequencenumber
568 ");
569
570 if (!$records) {
571 return array();
572 }
573
574 $questionattempts = array();
575 $record = current($records);
576 while ($record) {
577 $questionattempts[$record->questionattemptid] =
578 question_attempt::load_from_records($records,
579 $record->questionattemptid, new question_usage_null_observer(),
580 $record->preferredbehaviour);
581 $record = current($records);
582 }
583 return $questionattempts;
584 }
585
586 /**
587 * Update a question_usages row to refect any changes in a usage (but not
588 * any of its question_attempts.
589 * @param question_usage_by_activity $quba the usage that has changed.
590 */
591 public function update_questions_usage_by_activity(question_usage_by_activity $quba) {
592 $record = new stdClass;
593 $record->id = $quba->get_id();
594 $record->contextid = $quba->get_owning_context()->id;
595 $record->component = addslashes($quba->get_owning_component());
596 $record->preferredbehaviour = addslashes($quba->get_preferred_behaviour());
597
c76145d3 598 if (!$this->db->update_record('question_usages', $record)) {
d1b7e03d
TH
599 throw new Exception('Failed to update question_usage_by_activity ' . $record->id);
600 }
601 }
602
603 /**
604 * Update a question_attempts row to refect any changes in a question_attempt
605 * (but not any of its steps).
606 * @param question_attempt $qa the question attempt that has changed.
607 */
608 public function update_question_attempt(question_attempt $qa) {
c76145d3
TH
609 global $DB;
610
d1b7e03d
TH
611 $record = new stdClass;
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();
616 $record->questionsummary = addslashes($qa->get_question_summary());
617 $record->rightanswer = addslashes($qa->get_right_answer_summary());
618 $record->responsesummary = addslashes($qa->get_response_summary());
619 $record->timemodified = time();
620
c76145d3 621 if (!$this->db->update_record('question_attempts', $record)) {
d1b7e03d
TH
622 throw new Exception('Failed to update question_attempt ' . $record->id);
623 }
624 }
625
626 /**
627 * Delete a question_usage_by_activity and all its associated
628 * {@link question_attempts} and {@link question_attempt_steps} from the
629 * database.
630 * @param string $where a where clause. Becuase of MySQL limitations, you
631 * must refer to {$CFG->prefix}question_usages.id in full like that.
c76145d3 632 * @param array $params values to substitute for placeholders in $where.
d1b7e03d 633 */
c76145d3 634 public function delete_questions_usage_by_activities($where, $params) {
d1b7e03d 635 global $CFG;
c76145d3 636 $this->db->delete_records_select('question_attempt_step_data', "attemptstepid IN (
d1b7e03d 637 SELECT qas.id
c76145d3
TH
638 FROM {question_attempts} qa
639 JOIN {question_attempt_steps} qas ON qas.questionattemptid = qa.id
640 JOIN {question_usages} ON qa.questionusageid = {question_usages}.id
641 WHERE $where)", $params);
642 $this->db->delete_records_select('question_attempt_steps', "questionattemptid IN (
d1b7e03d 643 SELECT qa.id
c76145d3
TH
644 FROM {question_attempts} qa
645 JOIN {question_usages} ON qa.questionusageid = {question_usages}.id
646 WHERE $where)", $params);
647 $this->db->delete_records_select('question_attempts', "questionusageid IN (
d1b7e03d 648 SELECT id
c76145d3
TH
649 FROM {question_usages}
650 WHERE $where)", $params);
651 $this->db->delete_records_select('question_usages', $where, $params);
d1b7e03d
TH
652 }
653
654 /**
655 * Delete all the steps for a question attempt.
656 * @param integer $qaids question_attempt id.
657 */
658 public function delete_steps_for_question_attempts($qaids) {
659 global $CFG;
660 if (empty($qaids)) {
661 return;
662 }
663 list($test, $params) = 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) {
676 global $CFG;
c76145d3 677 $previews = $this->db->get_records_sql_menu("
d1b7e03d 678 SELECT DISTINCT quba.id, 1
c76145d3
TH
679 FROM {question_usages} quba
680 JOIN {question_attempts} qa ON qa.questionusageid = quba.id
d1b7e03d 681 WHERE quba.component = 'core_question_preview' AND
c76145d3 682 qa.questionid = ?", array($questionid));
d1b7e03d
TH
683 if (empty($previews)) {
684 return;
685 }
c76145d3
TH
686 list($test, $params) = $this->db->get_in_or_equal(array_keys($previews));
687 $this->delete_questions_usage_by_activities('question_usages.id ' . $test, $params);
d1b7e03d
TH
688 }
689
690 /**
691 * Update the flagged state of a question in the database.
692 * @param integer $qubaid the question usage id.
693 * @param integer $questionid the question id.
694 * @param integer $sessionid the question_attempt id.
695 * @param boolean $newstate the new state of the flag. true = flagged.
696 */
06f8ed54 697 public function update_question_attempt_flag($qubaid, $questionid, $qaid, $slot, $newstate) {
f9b0500f 698 if (!$this->db->record_exists('question_attempts', array('id' => $qaid,
06f8ed54 699 'questionusageid' => $qubaid, 'questionid' => $questionid, 'slot' => $slot))) {
d1b7e03d
TH
700 throw new Exception('invalid ids');
701 }
702
06f8ed54 703 $this->db->set_field('question_attempts', 'flagged', $newstate, array('id' => $qaid));
d1b7e03d
TH
704 }
705
706 /**
707 * Get all the WHEN 'x' THEN 'y' terms needed to convert the question_attempt_steps.state
708 * column to a summary state. Use this like
709 * CASE qas.state {$this->full_states_to_summary_state_sql()} END AS summarystate,
710 * @param string SQL fragment.
711 */
712 protected function full_states_to_summary_state_sql() {
713 $sql = '';
714 foreach (question_state::get_all() as $state) {
715 $sql .= "WHEN '$state' THEN '{$state->get_summary_state()}'\n";
716 }
717 return $sql;
718 }
719
720 /**
721 * Get the SQL needed to test that question_attempt_steps.state is in a
722 * state corresponding to $summarystate.
723 * @param string $summarystate one of
724 * inprogress, needsgrading, manuallygraded or autograded
725 * @param boolean $equal if false, do a NOT IN test. Default true.
726 * @return string SQL fragment.
727 */
728 public function in_summary_state_test($summarystate, $equal = true) {
729 $states = question_state::get_all_for_summary_state($summarystate);
c76145d3
TH
730 list($sql, $params) = $this->db->get_in_or_equal($states, SQL_PARAMS_QM, 'param0000', $equal);
731 // TODO return $params;
d1b7e03d
TH
732 return $sql;
733 }
734
735 /**
736 * Change the maxmark for the question_attempt with number in usage $slot
737 * for all the specified question_attempts.
738 * @param qubaid_condition $qubaids Selects which usages are updated.
739 * @param integer $slot the number is usage to affect.
740 * @param number $newmaxmark the new max mark to set.
741 */
742 public function set_max_mark_in_attempts(qubaid_condition $qubaids, $slot, $newmaxmark) {
c76145d3
TH
743 $this->db->set_field_select('question_attempts', 'maxmark', $newmaxmark,
744 "questionusageid {$qubaids->usage_id_in()} AND slot = :slot",
745 $qubaids->usage_id_in_params() + array('slot' => $slot));
d1b7e03d
TH
746 }
747
748 /**
749 * Return a subquery that computes the sum of the marks for all the questions
750 * in a usage. Which useage to compute the sum for is controlled bu the $qubaid
751 * parameter.
752 *
753 * See {@link quiz_update_all_attempt_sumgrades()} for an example of the usage of
754 * this method.
755 *
756 * @param string $qubaid SQL fragment that controls which usage is summed.
757 * This might be the name of a column in the outer query.
758 * @return string SQL code for the subquery.
759 */
760 public function sum_usage_marks_subquery($qubaid) {
d1b7e03d 761 return "SELECT SUM(qa.maxmark * qas.fraction)
c76145d3 762 FROM {question_attempts} qa
d1b7e03d
TH
763 JOIN (
764 SELECT summarks_qa.id AS questionattemptid, MAX(summarks_qas.id) AS latestid
c76145d3
TH
765 FROM {question_attempt_steps} summarks_qas
766 JOIN {question_attempts} summarks_qa ON summarks_qa.id = summarks_qas.questionattemptid
d1b7e03d
TH
767 WHERE summarks_qa.questionusageid = $qubaid
768 GROUP BY summarks_qa.id
769 ) lateststepid ON lateststepid.questionattemptid = qa.id
c76145d3 770 JOIN {question_attempt_steps} qas ON qas.id = lateststepid.latestid
d1b7e03d 771 WHERE qa.questionusageid = $qubaid
f86390dc 772 HAVING COUNT(CASE WHEN qas.state = 'needsgrading' AND qa.maxmark > 0 THEN 1 ELSE NULL END) = 0";
c76145d3 773 // TODO handle $qubaid with placeholders.
d1b7e03d
TH
774 }
775
776 public function question_attempt_latest_state_view($alias) {
d1b7e03d
TH
777 return "(
778 SELECT
779 {$alias}qa.id AS questionattemptid,
780 {$alias}qa.questionusageid,
781 {$alias}qa.slot,
782 {$alias}qa.behaviour,
783 {$alias}qa.questionid,
784 {$alias}qa.maxmark,
785 {$alias}qa.minfraction,
786 {$alias}qa.flagged,
787 {$alias}qa.questionsummary,
788 {$alias}qa.rightanswer,
789 {$alias}qa.responsesummary,
790 {$alias}qa.timemodified,
791 {$alias}qas.id AS attemptstepid,
792 {$alias}qas.sequencenumber,
793 {$alias}qas.state,
794 {$alias}qas.fraction,
795 {$alias}qas.timecreated,
796 {$alias}qas.userid
797
798 FROM {$CFG->prefix}question_attempts {$alias}qa
799 JOIN {$CFG->prefix}question_attempt_steps {$alias}qas ON
800 {$alias}qas.id = {$this->latest_step_for_qa_subquery($alias . 'qa.id')}
801 ) $alias";
802 }
803
804 protected function latest_step_for_qa_subquery($questionattemptid = 'qa.id') {
805 global $CFG;
806 return "(
807 SELECT MAX(id)
c76145d3 808 FROM {question_attempt_steps}
d1b7e03d
TH
809 WHERE questionattemptid = $questionattemptid
810 )";
811 }
812
813 /**
814 * @param array $questionids of question ids.
815 * @return boolean whether any of these questions are being used by the question engine.
816 */
32d8935c 817 public function questions_in_use(array $questionids) {
c76145d3
TH
818 list($test, $params) = $this->db->get_in_or_equal($questionids);
819 return $this->db->record_exists_select('question_attempts',
820 'questionid ' . $test, $params);
d1b7e03d
TH
821 }
822}
823
824/**
825 * Implementation of the unit of work pattern for the question engine.
826 *
827 * See http://martinfowler.com/eaaCatalog/unitOfWork.html. This tracks all the
828 * changes to a {@link question_usage_by_activity}, and its constituent parts,
829 * so that the changes can be saved to the database when {@link save()} is called.
830 *
831 * @copyright 2009 The Open University
832 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
833 */
834class question_engine_unit_of_work implements question_usage_observer {
835 /** @var question_usage_by_activity the usage being tracked. */
836 protected $quba;
837
838 /** @var boolean whether any of the fields of the usage have been changed. */
839 protected $modified = false;
840
841 /**
842 * @var array list of number in usage => {@link question_attempt}s that
843 * were already in the usage, and which have been modified.
844 */
845 protected $attemptsmodified = array();
846
847 /**
848 * @var array list of number in usage => {@link question_attempt}s that
849 * have been added to the usage.
850 */
851 protected $attemptsadded = array();
852
853 /**
854 * @var array list of question attempt ids to delete the steps for, before
855 * inserting new steps.
856 */
857 protected $attemptstodeletestepsfor = array();
858
859 /**
860 * @var array list of array(question_attempt_step, question_attempt id, seq number)
861 * of steps that have been added to question attempts in this usage.
862 */
863 protected $stepsadded = array();
864
865 /**
866 * Constructor.
867 * @param question_usage_by_activity $quba the usage to track.
868 */
869 public function __construct(question_usage_by_activity $quba) {
870 $this->quba = $quba;
871 }
872
873 public function notify_modified() {
874 $this->modified = true;
875 }
876
877 public function notify_attempt_modified(question_attempt $qa) {
878 $no = $qa->get_slot();
879 if (!array_key_exists($no, $this->attemptsadded)) {
880 $this->attemptsmodified[$no] = $qa;
881 }
882 }
883
884 public function notify_attempt_added(question_attempt $qa) {
885 $this->attemptsadded[$qa->get_slot()] = $qa;
886 }
887
888 public function notify_delete_attempt_steps(question_attempt $qa) {
889
890 if (array_key_exists($qa->get_slot(), $this->attemptsadded)) {
891 return;
892 }
893
894 $qaid = $qa->get_database_id();
895 foreach ($this->stepsadded as $key => $stepinfo) {
896 if ($stepinfo[1] == $qaid) {
897 unset($this->stepsadded[$key]);
898 }
899 }
900
901 $this->attemptstodeletestepsfor[$qaid] = 1;
902 }
903
904 public function notify_step_added(question_attempt_step $step, question_attempt $qa, $seq) {
905 if (array_key_exists($qa->get_slot(), $this->attemptsadded)) {
906 return;
907 }
908 $this->stepsadded[] = array($step, $qa->get_database_id(), $seq);
909 }
910
911 /**
912 * Write all the changes we have recorded to the database.
913 * @param question_engine_data_mapper $dm the mapper to use to update the database.
914 */
915 public function save(question_engine_data_mapper $dm) {
916 $dm->delete_steps_for_question_attempts(array_keys($this->attemptstodeletestepsfor));
917 foreach ($this->stepsadded as $stepinfo) {
918 list($step, $questionattemptid, $seq) = $stepinfo;
919 $dm->insert_question_attempt_step($step, $questionattemptid, $seq);
920 }
921 foreach ($this->attemptsadded as $qa) {
922 $dm->insert_question_attempt($qa);
923 }
924 foreach ($this->attemptsmodified as $qa) {
925 $dm->update_question_attempt($qa);
926 }
927 if ($this->modified) {
928 $dm->update_questions_usage_by_activity($this->quba);
929 }
930 }
931}
932
933
934/**
935 * This class represents a restriction on the set of question_usage ids to include
936 * in a larger database query. Depending of the how you are going to restrict the
937 * list of usages, construct an appropriate subclass.
938 *
939 * If $qubaids is an instance of this class, example usage might be
940 *
941 * SELECT qa.id, qa.maxmark
942 * FROM $qubaids->from_question_attempts('qa')
943 * WHERE $qubaids->where() AND qa.slot = 1
944 *
945 * @copyright 2010 The Open University
946 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
947 */
948abstract class qubaid_condition {
949
950 /**
951 * @return string the SQL that needs to go in the FROM clause when trying
952 * to select records from the 'question_attempts' table based on the
953 * qubaid_condition.
954 */
955 public abstract function from_question_attempts($alias);
956
957 /** @return string the SQL that needs to go in the where clause. */
958 public abstract function where();
959
960 /**
961 * @return the params needed by a query that uses
962 * {@link from_question_attempts()} and {@link where()}.
963 */
964 public abstract function from_where_params();
965
966 /**
967 * @return string SQL that can use used in a WHERE qubaid IN (...) query.
968 * This method returns the "IN (...)" part.
969 */
970 public abstract function usage_id_in();
971
972 /**
973 * @return the params needed by a query that uses {@link usage_id_in()}.
974 */
975 public abstract function usage_id_in_params();
976}
977
978
979/**
980 * This class represents a restriction on the set of question_usage ids to include
981 * in a larger database query based on an explicit list of ids.
982 *
983 * @copyright 2010 The Open University
984 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
985 */
986class qubaid_list extends qubaid_condition {
987 /** @var array of ids. */
988 protected $qubaids;
989 protected $columntotest = null;
990 protected $params;
991
992 /**
993 * Constructor.
994 * @param array $qubaids of question usage ids.
995 */
996 public function __construct(array $qubaids) {
997 $this->qubaids = $qubaids;
998 }
999
1000 public function from_question_attempts($alias) {
1001 global $CFG;
1002 $this->columntotest = $alias . '.questionusageid';
1003 return "{$CFG->prefix}question_attempts $alias";
1004 }
1005
1006 public function where() {
1007 global $DB;
1008
1009 if (is_null($this->columntotest)) {
1010 throw new coding_exception('Must call another method that before where().');
1011 }
1012 if (empty($this->qubaids)) {
1013 return '1 = 0';
1014 }
1015 list($where, $this->params) = $DB->get_in_or_equal($this->qubaids, SQL_PARAMS_NAMED, 'qubaid0000');
1016
1017 return "{$this->columntotest} {$this->usage_id_in()}";
1018 }
1019
1020 public function from_where_params() {
1021 return $this->params;
1022 }
1023
1024 public function usage_id_in() {
1025 global $DB;
1026
1027 if (empty($this->qubaids)) {
1028 return '= 0';
1029 }
1030 list($where, $this->params) = $DB->get_in_or_equal($this->qubaids, SQL_PARAMS_NAMED, 'qubaid0000');
1031 return $where;
1032 }
1033
1034 public function usage_id_in_params() {
1035 return $this->params;
1036 }
1037}
1038
1039
1040/**
1041 * This class represents a restriction on the set of question_usage ids to include
1042 * in a larger database query based on JOINing to some other tables.
1043 *
1044 * The general form of the query is something like
1045 *
1046 * SELECT qa.id, qa.maxmark
1047 * FROM $from
1048 * JOIN {$CFG->prefix}question_attempts qa ON qa.questionusageid = $usageidcolumn
1049 * WHERE $where AND qa.slot = 1
1050 *
1051 * where $from, $usageidcolumn and $where are the arguments to the constructor.
1052 *
1053 * @copyright 2010 The Open University
1054 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
1055 */
1056class qubaid_join extends qubaid_condition {
1057 public $from;
1058 public $usageidcolumn;
1059 public $where;
1060 public $params;
1061
1062 /**
1063 * Constructor. The meaning of the arguments is explained in the class comment.
1064 * @param string $from SQL fragemnt to go in the FROM clause.
1065 * @param string $usageidcolumn the column in $from that should be
1066 * made equal to the usageid column in the JOIN clause.
1067 * @param string $where SQL fragment to go in the where clause.
1068 */
1069 public function __construct($from, $usageidcolumn, $where = '', $params = array()) {
1070 $this->from = $from;
1071 $this->usageidcolumn = $usageidcolumn;
1072 $this->params = $params;
1073 if (empty($where)) {
1074 $where = '1 = 1';
1075 }
1076 $this->where = $where;
1077 }
1078
1079 public function from_question_attempts($alias) {
1080 global $CFG;
1081 return "$this->from
1082 JOIN {$CFG->prefix}question_attempts {$alias} ON " .
1083 "{$alias}.questionusageid = $this->usageidcolumn";
1084 }
1085
1086 public function where() {
1087 return $this->where;
1088 }
1089
1090 public function from_where_params() {
1091 return $this->params;
1092 }
1093
1094 public function usage_id_in() {
1095 return "IN (SELECT $this->usageidcolumn FROM $this->from WHERE $this->where)";
1096 }
1097
1098 public function usage_id_in_params() {
1099 return $this->params;
1100 }
1101}