MDL-20636 Current work-in-progress converting the question engine to Moodle 2.0
[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 {
37 /**
38 * Store an entire {@link question_usage_by_activity} in the database,
39 * including all the question_attempts that comprise it.
40 * @param question_usage_by_activity $quba the usage to store.
41 */
42 public function insert_questions_usage_by_activity(question_usage_by_activity $quba) {
43 $record = new stdClass;
44 $record->contextid = $quba->get_owning_context()->id;
45 $record->component = addslashes($quba->get_owning_component());
46 $record->preferredbehaviour = addslashes($quba->get_preferred_behaviour());
47
48 $newid = insert_record('question_usages', $record);
49 if (!$newid) {
50 throw new Exception('Failed to save questions_usage_by_activity.');
51 }
52 $quba->set_id_from_database($newid);
53
54 foreach ($quba->get_attempt_iterator() as $qa) {
55 $this->insert_question_attempt($qa);
56 }
57 }
58
59 /**
60 * Store an entire {@link question_attempt} in the database,
61 * including all the question_attempt_steps that comprise it.
62 * @param question_attempt $qa the question attempt to store.
63 */
64 public function insert_question_attempt(question_attempt $qa) {
65 $record = new stdClass;
66 $record->questionusageid = $qa->get_usage_id();
67 $record->slot = $qa->get_slot();
68 $record->behaviour = addslashes($qa->get_behaviour_name());
69 $record->questionid = $qa->get_question()->id;
70 $record->maxmark = $qa->get_max_mark();
71 $record->minfraction = $qa->get_min_fraction();
72 $record->flagged = $qa->is_flagged();
73 $record->questionsummary = addslashes($qa->get_question_summary());
74 $record->rightanswer = addslashes($qa->get_right_answer_summary());
75 $record->responsesummary = addslashes($qa->get_response_summary());
76 $record->timemodified = time();
77 $record->id = insert_record('question_attempts', $record);
78 if (!$record->id) {
79 throw new Exception('Failed to save question_attempt ' . $qa->get_slot());
80 }
81
82 foreach ($qa->get_step_iterator() as $seq => $step) {
83 $this->insert_question_attempt_step($step, $record->id, $seq);
84 }
85 }
86
87 /**
88 * Store a {@link question_attempt_step} in the database.
89 * @param question_attempt_step $qa the step to store.
90 */
91 public function insert_question_attempt_step(question_attempt_step $step,
92 $questionattemptid, $seq) {
93 $record = new stdClass;
94 $record->questionattemptid = $questionattemptid;
95 $record->sequencenumber = $seq;
96 $record->state = addslashes('' . $step->get_state());
97 $record->fraction = $step->get_fraction();
98 $record->timecreated = $step->get_timecreated();
99 $record->userid = $step->get_user_id();
100
101 $record->id = insert_record('question_attempt_steps', $record);
102 if (!$record->id) {
103 throw new Exception('Failed to save question_attempt_step' . $seq .
104 ' for question attempt id ' . $questionattemptid);
105 }
106
107 foreach ($step->get_all_data() as $name => $value) {
108 $data = new stdClass;
109 $data->attemptstepid = $record->id;
110 $data->name = addslashes($name);
111 $data->value = addslashes($value);
112 insert_record('question_attempt_step_data', $data, false);
113 }
114 }
115
116 /**
117 * Load a {@link question_attempt_step} from the database.
118 * @param integer $stepid the id of the step to load.
119 * @param question_attempt_step the step that was loaded.
120 */
121 public function load_question_attempt_step($stepid) {
122 global $CFG;
123 $records = get_records_sql("
124SELECT
125 COALESCE(qasd.id, -1 * qas.id) AS id,
126 qas.id AS attemptstepid,
127 qas.questionattemptid,
128 qas.sequencenumber,
129 qas.state,
130 qas.fraction,
131 qas.timecreated,
132 qas.userid,
133 qasd.name,
134 qasd.value
135
136FROM {$CFG->prefix}question_attempt_steps qas
137LEFT JOIN {$CFG->prefix}question_attempt_step_data qasd ON qasd.attemptstepid = qas.id
138
139WHERE
140 qas.id = $stepid
141 ");
142
143 if (!$records) {
144 throw new Exception('Failed to load question_attempt_step ' . $stepid);
145 }
146
147 return question_attempt_step::load_from_records($records, $stepid);
148 }
149
150 /**
151 * Load a {@link question_attempt} from the database, including all its
152 * steps.
153 * @param integer $questionattemptid the id of the question attempt to load.
154 * @param question_attempt the question attempt that was loaded.
155 */
156 public function load_question_attempt($questionattemptid) {
157 global $CFG;
158 $records = get_records_sql("
159SELECT
160 COALESCE(qasd.id, -1 * qas.id) AS id,
161 quba.preferredbehaviour,
162 qa.id AS questionattemptid,
163 qa.questionusageid,
164 qa.slot,
165 qa.behaviour,
166 qa.questionid,
167 qa.maxmark,
168 qa.minfraction,
169 qa.flagged,
170 qa.questionsummary,
171 qa.rightanswer,
172 qa.responsesummary,
173 qa.timemodified,
174 qas.id AS attemptstepid,
175 qas.sequencenumber,
176 qas.state,
177 qas.fraction,
178 qas.timecreated,
179 qas.userid,
180 qasd.name,
181 qasd.value
182
183FROM {$CFG->prefix}question_attempts qa
184JOIN {$CFG->prefix}question_usages quba ON quba.id = qa.questionusageid
185LEFT JOIN {$CFG->prefix}question_attempt_steps qas ON qas.questionattemptid = qa.id
186LEFT JOIN {$CFG->prefix}question_attempt_step_data qasd ON qasd.attemptstepid = qas.id
187
188WHERE
189 qa.id = $questionattemptid
190
191ORDER BY
192 qas.sequencenumber
193 ");
194
195 if (!$records) {
196 throw new Exception('Failed to load question_attempt ' . $questionattemptid);
197 }
198
199 $record = current($records);
200 return question_attempt::load_from_records($records, $questionattemptid,
201 new question_usage_null_observer(), $record->preferredbehaviour);
202 }
203
204 /**
205 * Load a {@link question_usage_by_activity} from the database, including
206 * all its {@link question_attempt}s and all their steps.
207 * @param integer $qubaid the id of the usage to load.
208 * @param question_usage_by_activity the usage that was loaded.
209 */
210 public function load_questions_usage_by_activity($qubaid) {
211 global $CFG;
212 $records = get_records_sql("
213SELECT
214 COALESCE(qasd.id, -1 * qas.id) AS id,
215 quba.id AS qubaid,
216 quba.contextid,
217 quba.component,
218 quba.preferredbehaviour,
219 qa.id AS questionattemptid,
220 qa.questionusageid,
221 qa.slot,
222 qa.behaviour,
223 qa.questionid,
224 qa.maxmark,
225 qa.minfraction,
226 qa.flagged,
227 qa.questionsummary,
228 qa.rightanswer,
229 qa.responsesummary,
230 qa.timemodified,
231 qas.id AS attemptstepid,
232 qas.sequencenumber,
233 qas.state,
234 qas.fraction,
235 qas.timecreated,
236 qas.userid,
237 qasd.name,
238 qasd.value
239
240FROM {$CFG->prefix}question_usages quba
241LEFT JOIN {$CFG->prefix}question_attempts qa ON qa.questionusageid = quba.id
242LEFT JOIN {$CFG->prefix}question_attempt_steps qas ON qas.questionattemptid = qa.id
243LEFT JOIN {$CFG->prefix}question_attempt_step_data qasd ON qasd.attemptstepid = qas.id
244
245WHERE
246 quba.id = $qubaid
247
248ORDER BY
249 qa.slot,
250 qas.sequencenumber
251 ");
252
253 if (!$records) {
254 throw new Exception('Failed to load questions_usage_by_activity ' . $qubaid);
255 }
256
257 return question_usage_by_activity::load_from_records($records, $qubaid);
258 }
259
260 /**
261 * Load information about the latest state of each question from the database.
262 *
263 * @param qubaid_condition $qubaids used to restrict which usages are included
264 * in the query. See {@link qubaid_condition}.
265 * @param array $slots A list of slots for the questions you want to konw about.
266 * @return array of records. See the SQL in this function to see the fields available.
267 */
268 public function load_questions_usages_latest_steps(qubaid_condition $qubaids, $slots) {
269 global $CFG;
270
271 list($slottest, $params) = get_in_or_equal($slots, SQL_PARAMS_NAMED, 'slot0000');
272
273 $records = get_records_sql("
274SELECT
275 qas.id,
276 qa.id AS questionattemptid,
277 qa.questionusageid,
278 qa.slot,
279 qa.behaviour,
280 qa.questionid,
281 qa.maxmark,
282 qa.minfraction,
283 qa.flagged,
284 qa.questionsummary,
285 qa.rightanswer,
286 qa.responsesummary,
287 qa.timemodified,
288 qas.id AS attemptstepid,
289 qas.sequencenumber,
290 qas.state,
291 qas.fraction,
292 qas.timecreated,
293 qas.userid
294
295FROM {$qubaids->from_question_attempts('qa')}
296JOIN {$CFG->prefix}question_attempt_steps qas ON
297 qas.id = {$this->latest_step_for_qa_subquery()}
298
299WHERE
300 {$qubaids->where()} AND
301 qa.slot $slottest
302 ");
303
304 if (!$records) {
305 $records = array();
306 }
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) {
324 global $CFG;
325
326 list($slottest, $params) = get_in_or_equal($slots, SQL_PARAMS_NAMED, 'slot0000');
327
328 $rs = get_recordset_sql("
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')}
339JOIN {$CFG->prefix}question_attempt_steps qas ON
340 qas.id = {$this->latest_step_for_qa_subquery()}
341JOIN {$CFG->prefix}question q ON q.id = qa.questionid
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
354ORDER BY
355 qa.slot,
356 qa.questionid,
357 q.name,
358 q.id
359 ");
360
361 if (!$rs) {
362 throw new moodle_exception('errorloadingdata');
363 }
364
365 $results = array();
366 while ($row = rs_fetch_next_record($rs)) {
367 $index = $row->slot . ',' . $row->questionid;
368
369 if (!array_key_exists($index, $results)) {
370 $res = new stdClass;
371 $res->slot = $row->slot;
372 $res->questionid = $row->questionid;
373 $res->name = $row->name;
374 $res->inprogress = 0;
375 $res->needsgrading = 0;
376 $res->autograded = 0;
377 $res->manuallygraded = 0;
378 $res->all = 0;
379 $results[$index] = $res;
380 }
381
382 $results[$index]->{$row->summarystate} = $row->numattempts;
383 $results[$index]->all += $row->numattempts;
384 }
385 rs_close($rs);
386
387 return $results;
388 }
389
390 /**
391 * Get a list of usage ids where the question with slot $slot, and optionally
392 * also with question id $questionid, is in summary state $summarystate. Also
393 * return the total count of such states.
394 *
395 * Only a subset of the ids can be returned by using $orderby, $limitfrom and
396 * $limitnum. A special value 'random' can be passed as $orderby, in which case
397 * $limitfrom is ignored.
398 *
399 * @param qubaid_condition $qubaids used to restrict which usages are included
400 * in the query. See {@link qubaid_condition}.
401 * @param integer $slot The slot for the questions you want to konw about.
402 * @param integer $questionid (optional) Only return attempts that were of this specific question.
403 * @param string $summarystate the summary state of interest, or 'all'.
404 * @param string $orderby the column to order by.
405 * @param integer $limitfrom implements paging of the results.
406 * Ignored if $orderby = random or $limitnum is null.
407 * @param integer $limitnum implements paging of the results. null = all.
408 * @return array with two elements, an array of usage ids, and a count of the total number.
409 */
410 public function load_questions_usages_where_question_in_state(
411 qubaid_condition $qubaids, $summarystate, $slot, $questionid = null,
412 $orderby = 'random', $limitfrom = 0, $limitnum = null) {
413 global $CFG;
414
415 $extrawhere = '';
416 if ($questionid) {
417 $extrawhere .= ' AND qa.questionid = ' . $questionid;
418 }
419 if ($summarystate != 'all') {
420 $test = $this->in_summary_state_test($summarystate);
421 $extrawhere .= ' AND qas.state ' . $test;
422 }
423
424 if ($orderby == 'random') {
425 $sqlorderby = '';
426 } else if ($orderby) {
427 $sqlorderby = 'ORDER BY ' . $orderby;
428 } else {
429 $sqlorderby = '';
430 }
431
432 // We always want the total count, as well as the partcular list of ids,
433 // based on the paging and sort order. Becuase the list of ids is never
434 // going to be too rediculously long. My worst-case scenario is
435 // 10,000 students in the coures, each doing 5 quiz attempts. That
436 // is a 50,000 element int => int array, which PHP seems to use 5MB
437 // memeory to store on a 64 bit server.
438 $qubaids = get_records_sql_menu("
439SELECT
440 qa.questionusageid,
441 1
442
443FROM {$qubaids->from_question_attempts('qa')}
444JOIN {$CFG->prefix}question_attempt_steps qas ON
445 qas.id = {$this->latest_step_for_qa_subquery()}
446JOIN {$CFG->prefix}question q ON q.id = qa.questionid
447
448WHERE
449 {$qubaids->where()} AND
450 qa.slot = $slot
451 $extrawhere
452
453$sqlorderby
454 ");
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) {
480 global $CFG;
481
482 if (!empty($slots)) {
483 list($slottest, $params) = get_in_or_equal($slots, SQL_PARAMS_NAMED, 'slot0000');
484 $slotwhere = " AND qa.slot $slottest";
485 } else {
486 $slotwhere = '';
487 }
488
489 list($statetest) = get_in_or_equal(array(
490 question_state::$gaveup,
491 question_state::$gradedwrong,
492 question_state::$gradedpartial,
493 question_state::$gradedright,
494 question_state::$mangaveup,
495 question_state::$mangrwrong,
496 question_state::$mangrpartial,
497 question_state::$mangrright));
498
499 $records = get_records_sql("
500SELECT
501 qa.slot,
502 AVG(COALESCE(qas.fraction, 0)) AS averagefraction,
503 COUNT(1) AS numaveraged
504
505FROM {$qubaids->from_question_attempts('qa')}
506JOIN {$CFG->prefix}question_attempt_steps qas ON
507 qas.id = {$this->latest_step_for_qa_subquery()}
508
509WHERE
510 {$qubaids->where()}
511 $slotwhere
512 AND qas.state $statetest
513
514GROUP BY qa.slot
515
516ORDER BY qa.slot
517 ");
518
519 return $records;
520 }
521
522 /**
523 * Load a {@link question_attempt} from the database, including all its
524 * steps.
525 * @param integer $questionid the question to load all the attempts fors.
526 * @param qubaid_condition $qubaids used to restrict which usages are included
527 * in the query. See {@link qubaid_condition}.
528 * @return array of question_attempts.
529 */
530 public function load_attempts_at_question($questionid, qubaid_condition $qubaids) {
531 global $CFG;
532 $records = get_records_sql("
533SELECT
534 COALESCE(qasd.id, -1 * qas.id) AS id,
535 quba.preferredbehaviour,
536 qa.id AS questionattemptid,
537 qa.questionusageid,
538 qa.slot,
539 qa.behaviour,
540 qa.questionid,
541 qa.maxmark,
542 qa.minfraction,
543 qa.flagged,
544 qa.questionsummary,
545 qa.rightanswer,
546 qa.responsesummary,
547 qa.timemodified,
548 qas.id AS attemptstepid,
549 qas.sequencenumber,
550 qas.state,
551 qas.fraction,
552 qas.timecreated,
553 qas.userid,
554 qasd.name,
555 qasd.value
556
557FROM {$qubaids->from_question_attempts('qa')}
558JOIN {$CFG->prefix}question_usages quba ON quba.id = qa.questionusageid
559LEFT JOIN {$CFG->prefix}question_attempt_steps qas ON qas.questionattemptid = qa.id
560LEFT JOIN {$CFG->prefix}question_attempt_step_data qasd ON qasd.attemptstepid = qas.id
561
562WHERE
563 {$qubaids->where()} AND
564 qa.questionid = $questionid
565
566ORDER BY
567 quba.id,
568 qa.id,
569 qas.sequencenumber
570 ");
571
572 if (!$records) {
573 return array();
574 }
575
576 $questionattempts = array();
577 $record = current($records);
578 while ($record) {
579 $questionattempts[$record->questionattemptid] =
580 question_attempt::load_from_records($records,
581 $record->questionattemptid, new question_usage_null_observer(),
582 $record->preferredbehaviour);
583 $record = current($records);
584 }
585 return $questionattempts;
586 }
587
588 /**
589 * Update a question_usages row to refect any changes in a usage (but not
590 * any of its question_attempts.
591 * @param question_usage_by_activity $quba the usage that has changed.
592 */
593 public function update_questions_usage_by_activity(question_usage_by_activity $quba) {
594 $record = new stdClass;
595 $record->id = $quba->get_id();
596 $record->contextid = $quba->get_owning_context()->id;
597 $record->component = addslashes($quba->get_owning_component());
598 $record->preferredbehaviour = addslashes($quba->get_preferred_behaviour());
599
600 if (!update_record('question_usages', $record)) {
601 throw new Exception('Failed to update question_usage_by_activity ' . $record->id);
602 }
603 }
604
605 /**
606 * Update a question_attempts row to refect any changes in a question_attempt
607 * (but not any of its steps).
608 * @param question_attempt $qa the question attempt that has changed.
609 */
610 public function update_question_attempt(question_attempt $qa) {
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
621 if (!update_record('question_attempts', $record)) {
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.
632 */
633 public function delete_questions_usage_by_activities($where) {
634 global $CFG;
635 delete_records_select('question_attempt_step_data', "attemptstepid IN (
636 SELECT qas.id
637 FROM {$CFG->prefix}question_attempts qa
638 JOIN {$CFG->prefix}question_attempt_steps qas ON qas.questionattemptid = qa.id
639 JOIN {$CFG->prefix}question_usages ON qa.questionusageid = {$CFG->prefix}question_usages.id
640 WHERE $where)");
641 delete_records_select('question_attempt_steps', "questionattemptid IN (
642 SELECT qa.id
643 FROM {$CFG->prefix}question_attempts qa
644 JOIN {$CFG->prefix}question_usages ON qa.questionusageid = {$CFG->prefix}question_usages.id
645 WHERE $where)");
646 delete_records_select('question_attempts', "questionusageid IN (
647 SELECT id
648 FROM {$CFG->prefix}question_usages
649 WHERE $where)");
650 delete_records_select('question_usages', $where);
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);
663 delete_records_select('question_attempt_step_data', "attemptstepid IN (
664 SELECT qas.id
665 FROM {$CFG->prefix}question_attempt_steps qas
666 WHERE questionattemptid $test)");
667 delete_records_select('question_attempt_steps', 'questionattemptid ' . $test);
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;
676 $previews = get_records_sql_menu("
677 SELECT DISTINCT quba.id, 1
678 FROM {$CFG->prefix}question_usages quba
679 JOIN {$CFG->prefix}question_attempts qa ON qa.questionusageid = quba.id
680 WHERE quba.component = 'core_question_preview' AND
681 qa.questionid = '$questionid'");
682 if (empty($previews)) {
683 return;
684 }
685 $this->delete_questions_usage_by_activities(
686 "{$CFG->prefix}question_usages.id IN (" .
687 implode(',', array_keys($previews)) . ')');
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 */
697 public function update_question_attempt_flag($qubaid, $questionid, $qaid, $newstate) {
698 if (!record_exists('question_attempts', 'id', $qaid,
699 'questionusageid', $qubaid, 'questionid', $questionid)) {
700 throw new Exception('invalid ids');
701 }
702
703 if (!set_field('question_attempts', 'flagged', $newstate, 'id', $qaid)) {
704 throw new Exception('flag update failed');
705 }
706 }
707
708 /**
709 * Get all the WHEN 'x' THEN 'y' terms needed to convert the question_attempt_steps.state
710 * column to a summary state. Use this like
711 * CASE qas.state {$this->full_states_to_summary_state_sql()} END AS summarystate,
712 * @param string SQL fragment.
713 */
714 protected function full_states_to_summary_state_sql() {
715 $sql = '';
716 foreach (question_state::get_all() as $state) {
717 $sql .= "WHEN '$state' THEN '{$state->get_summary_state()}'\n";
718 }
719 return $sql;
720 }
721
722 /**
723 * Get the SQL needed to test that question_attempt_steps.state is in a
724 * state corresponding to $summarystate.
725 * @param string $summarystate one of
726 * inprogress, needsgrading, manuallygraded or autograded
727 * @param boolean $equal if false, do a NOT IN test. Default true.
728 * @return string SQL fragment.
729 */
730 public function in_summary_state_test($summarystate, $equal = true) {
731 $states = question_state::get_all_for_summary_state($summarystate);
732 list($sql, $params) = get_in_or_equal($states, SQL_PARAMS_QM, 'param0000', $equal);
733 return $sql;
734 }
735
736 /**
737 * Change the maxmark for the question_attempt with number in usage $slot
738 * for all the specified question_attempts.
739 * @param qubaid_condition $qubaids Selects which usages are updated.
740 * @param integer $slot the number is usage to affect.
741 * @param number $newmaxmark the new max mark to set.
742 */
743 public function set_max_mark_in_attempts(qubaid_condition $qubaids, $slot, $newmaxmark) {
744 set_field_select('question_attempts', 'maxmark', $newmaxmark,
745 "questionusageid {$qubaids->usage_id_in()} AND slot = $slot");
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) {
761 global $CFG;
762 return "SELECT SUM(qa.maxmark * qas.fraction)
763 FROM {$CFG->prefix}question_attempts qa
764 JOIN (
765 SELECT summarks_qa.id AS questionattemptid, MAX(summarks_qas.id) AS latestid
766 FROM {$CFG->prefix}question_attempt_steps summarks_qas
767 JOIN {$CFG->prefix}question_attempts summarks_qa ON summarks_qa.id = summarks_qas.questionattemptid
768 WHERE summarks_qa.questionusageid = $qubaid
769 GROUP BY summarks_qa.id
770 ) lateststepid ON lateststepid.questionattemptid = qa.id
771 JOIN {$CFG->prefix}question_attempt_steps qas ON qas.id = lateststepid.latestid
772 WHERE qa.questionusageid = $qubaid
773 HAVING COUNT(CASE WHEN qas.state = 'needsgrading' THEN 1 ELSE NULL END) = 0";
774 }
775
776 public function question_attempt_latest_state_view($alias) {
777 global $CFG;
778 return "(
779 SELECT
780 {$alias}qa.id AS questionattemptid,
781 {$alias}qa.questionusageid,
782 {$alias}qa.slot,
783 {$alias}qa.behaviour,
784 {$alias}qa.questionid,
785 {$alias}qa.maxmark,
786 {$alias}qa.minfraction,
787 {$alias}qa.flagged,
788 {$alias}qa.questionsummary,
789 {$alias}qa.rightanswer,
790 {$alias}qa.responsesummary,
791 {$alias}qa.timemodified,
792 {$alias}qas.id AS attemptstepid,
793 {$alias}qas.sequencenumber,
794 {$alias}qas.state,
795 {$alias}qas.fraction,
796 {$alias}qas.timecreated,
797 {$alias}qas.userid
798
799 FROM {$CFG->prefix}question_attempts {$alias}qa
800 JOIN {$CFG->prefix}question_attempt_steps {$alias}qas ON
801 {$alias}qas.id = {$this->latest_step_for_qa_subquery($alias . 'qa.id')}
802 ) $alias";
803 }
804
805 protected function latest_step_for_qa_subquery($questionattemptid = 'qa.id') {
806 global $CFG;
807 return "(
808 SELECT MAX(id)
809 FROM {$CFG->prefix}question_attempt_steps
810 WHERE questionattemptid = $questionattemptid
811 )";
812 }
813
814 /**
815 * @param array $questionids of question ids.
816 * @return boolean whether any of these questions are being used by the question engine.
817 */
818 public static function questions_in_use(array $questionids) {
819 return record_exists_select('question_attempts', 'questionid IN (' .
820 implode(',', $questionids) . ')');
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}