MDL-20636 It is now possible to save a truefalse question you have created.
[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;
49 new moodle_database;
50 $this->db = $DB;
51 } else {
52 $this->db = $db;
53 }
54 }
55
d1b7e03d
TH
56 /**
57 * Store an entire {@link question_usage_by_activity} in the database,
58 * including all the question_attempts that comprise it.
59 * @param question_usage_by_activity $quba the usage to store.
60 */
61 public function insert_questions_usage_by_activity(question_usage_by_activity $quba) {
62 $record = new stdClass;
63 $record->contextid = $quba->get_owning_context()->id;
64 $record->component = addslashes($quba->get_owning_component());
65 $record->preferredbehaviour = addslashes($quba->get_preferred_behaviour());
66
06f8ed54 67 $newid = $this->db->insert_record('question_usages', $record);
d1b7e03d
TH
68 $quba->set_id_from_database($newid);
69
70 foreach ($quba->get_attempt_iterator() as $qa) {
71 $this->insert_question_attempt($qa);
72 }
73 }
74
75 /**
76 * Store an entire {@link question_attempt} in the database,
77 * including all the question_attempt_steps that comprise it.
78 * @param question_attempt $qa the question attempt to store.
79 */
80 public function insert_question_attempt(question_attempt $qa) {
81 $record = new stdClass;
82 $record->questionusageid = $qa->get_usage_id();
83 $record->slot = $qa->get_slot();
84 $record->behaviour = addslashes($qa->get_behaviour_name());
85 $record->questionid = $qa->get_question()->id;
86 $record->maxmark = $qa->get_max_mark();
87 $record->minfraction = $qa->get_min_fraction();
88 $record->flagged = $qa->is_flagged();
89 $record->questionsummary = addslashes($qa->get_question_summary());
90 $record->rightanswer = addslashes($qa->get_right_answer_summary());
91 $record->responsesummary = addslashes($qa->get_response_summary());
92 $record->timemodified = time();
06f8ed54 93 $record->id = $this->db->insert_record('question_attempts', $record);
d1b7e03d
TH
94
95 foreach ($qa->get_step_iterator() as $seq => $step) {
96 $this->insert_question_attempt_step($step, $record->id, $seq);
97 }
98 }
99
100 /**
101 * Store a {@link question_attempt_step} in the database.
102 * @param question_attempt_step $qa the step to store.
103 */
104 public function insert_question_attempt_step(question_attempt_step $step,
105 $questionattemptid, $seq) {
106 $record = new stdClass;
107 $record->questionattemptid = $questionattemptid;
108 $record->sequencenumber = $seq;
109 $record->state = addslashes('' . $step->get_state());
110 $record->fraction = $step->get_fraction();
111 $record->timecreated = $step->get_timecreated();
112 $record->userid = $step->get_user_id();
113
06f8ed54 114 $record->id = $this->db->insert_record('question_attempt_steps', $record);
d1b7e03d
TH
115
116 foreach ($step->get_all_data() as $name => $value) {
117 $data = new stdClass;
118 $data->attemptstepid = $record->id;
119 $data->name = addslashes($name);
120 $data->value = addslashes($value);
06f8ed54 121 $this->db->insert_record('question_attempt_step_data', $data, false);
d1b7e03d
TH
122 }
123 }
124
125 /**
126 * Load a {@link question_attempt_step} from the database.
127 * @param integer $stepid the id of the step to load.
128 * @param question_attempt_step the step that was loaded.
129 */
130 public function load_question_attempt_step($stepid) {
06f8ed54 131 $records = $this->db->get_records_sql("
d1b7e03d
TH
132SELECT
133 COALESCE(qasd.id, -1 * qas.id) AS id,
134 qas.id AS attemptstepid,
135 qas.questionattemptid,
136 qas.sequencenumber,
137 qas.state,
138 qas.fraction,
139 qas.timecreated,
140 qas.userid,
141 qasd.name,
142 qasd.value
143
06f8ed54
TH
144FROM {question_attempt_steps} qas
145LEFT JOIN {question_attempt_step_data} qasd ON qasd.attemptstepid = qas.id
d1b7e03d
TH
146
147WHERE
06f8ed54
TH
148 qas.id = :stepid
149 ", array('stepid' => $stepid));
d1b7e03d
TH
150
151 if (!$records) {
152 throw new Exception('Failed to load question_attempt_step ' . $stepid);
153 }
154
155 return question_attempt_step::load_from_records($records, $stepid);
156 }
157
158 /**
159 * Load a {@link question_attempt} from the database, including all its
160 * steps.
161 * @param integer $questionattemptid the id of the question attempt to load.
162 * @param question_attempt the question attempt that was loaded.
163 */
164 public function load_question_attempt($questionattemptid) {
06f8ed54 165 $records = $this->db->get_records_sql("
d1b7e03d
TH
166SELECT
167 COALESCE(qasd.id, -1 * qas.id) AS id,
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
06f8ed54 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) {
d1b7e03d
TH
275 list($slottest, $params) = get_in_or_equal($slots, SQL_PARAMS_NAMED, 'slot0000');
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
352ORDER BY
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
598 if (!update_record('question_usages', $record)) {
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) {
609 $record = new stdClass;
610 $record->id = $qa->get_database_id();
611 $record->maxmark = $qa->get_max_mark();
612 $record->minfraction = $qa->get_min_fraction();
613 $record->flagged = $qa->is_flagged();
614 $record->questionsummary = addslashes($qa->get_question_summary());
615 $record->rightanswer = addslashes($qa->get_right_answer_summary());
616 $record->responsesummary = addslashes($qa->get_response_summary());
617 $record->timemodified = time();
618
619 if (!update_record('question_attempts', $record)) {
620 throw new Exception('Failed to update question_attempt ' . $record->id);
621 }
622 }
623
624 /**
625 * Delete a question_usage_by_activity and all its associated
626 * {@link question_attempts} and {@link question_attempt_steps} from the
627 * database.
628 * @param string $where a where clause. Becuase of MySQL limitations, you
629 * must refer to {$CFG->prefix}question_usages.id in full like that.
630 */
631 public function delete_questions_usage_by_activities($where) {
632 global $CFG;
633 delete_records_select('question_attempt_step_data', "attemptstepid IN (
634 SELECT qas.id
635 FROM {$CFG->prefix}question_attempts qa
636 JOIN {$CFG->prefix}question_attempt_steps qas ON qas.questionattemptid = qa.id
637 JOIN {$CFG->prefix}question_usages ON qa.questionusageid = {$CFG->prefix}question_usages.id
638 WHERE $where)");
639 delete_records_select('question_attempt_steps', "questionattemptid IN (
640 SELECT qa.id
641 FROM {$CFG->prefix}question_attempts qa
642 JOIN {$CFG->prefix}question_usages ON qa.questionusageid = {$CFG->prefix}question_usages.id
643 WHERE $where)");
644 delete_records_select('question_attempts', "questionusageid IN (
645 SELECT id
646 FROM {$CFG->prefix}question_usages
647 WHERE $where)");
648 delete_records_select('question_usages', $where);
649 }
650
651 /**
652 * Delete all the steps for a question attempt.
653 * @param integer $qaids question_attempt id.
654 */
655 public function delete_steps_for_question_attempts($qaids) {
656 global $CFG;
657 if (empty($qaids)) {
658 return;
659 }
660 list($test, $params) = get_in_or_equal($qaids);
661 delete_records_select('question_attempt_step_data', "attemptstepid IN (
662 SELECT qas.id
663 FROM {$CFG->prefix}question_attempt_steps qas
664 WHERE questionattemptid $test)");
665 delete_records_select('question_attempt_steps', 'questionattemptid ' . $test);
666 }
667
668 /**
669 * Delete all the previews for a given question.
670 * @param integer $questionid question id.
671 */
672 public function delete_previews($questionid) {
673 global $CFG;
674 $previews = get_records_sql_menu("
675 SELECT DISTINCT quba.id, 1
676 FROM {$CFG->prefix}question_usages quba
677 JOIN {$CFG->prefix}question_attempts qa ON qa.questionusageid = quba.id
678 WHERE quba.component = 'core_question_preview' AND
679 qa.questionid = '$questionid'");
680 if (empty($previews)) {
681 return;
682 }
683 $this->delete_questions_usage_by_activities(
684 "{$CFG->prefix}question_usages.id IN (" .
685 implode(',', array_keys($previews)) . ')');
686 }
687
688 /**
689 * Update the flagged state of a question in the database.
690 * @param integer $qubaid the question usage id.
691 * @param integer $questionid the question id.
692 * @param integer $sessionid the question_attempt id.
693 * @param boolean $newstate the new state of the flag. true = flagged.
694 */
06f8ed54
TH
695 public function update_question_attempt_flag($qubaid, $questionid, $qaid, $slot, $newstate) {
696 if (!$this->db->record_exists('question_attempts', array('id' => $qaid,
697 'questionusageid' => $qubaid, 'questionid' => $questionid, 'slot' => $slot))) {
d1b7e03d
TH
698 throw new Exception('invalid ids');
699 }
700
06f8ed54 701 $this->db->set_field('question_attempts', 'flagged', $newstate, array('id' => $qaid));
d1b7e03d
TH
702 }
703
704 /**
705 * Get all the WHEN 'x' THEN 'y' terms needed to convert the question_attempt_steps.state
706 * column to a summary state. Use this like
707 * CASE qas.state {$this->full_states_to_summary_state_sql()} END AS summarystate,
708 * @param string SQL fragment.
709 */
710 protected function full_states_to_summary_state_sql() {
711 $sql = '';
712 foreach (question_state::get_all() as $state) {
713 $sql .= "WHEN '$state' THEN '{$state->get_summary_state()}'\n";
714 }
715 return $sql;
716 }
717
718 /**
719 * Get the SQL needed to test that question_attempt_steps.state is in a
720 * state corresponding to $summarystate.
721 * @param string $summarystate one of
722 * inprogress, needsgrading, manuallygraded or autograded
723 * @param boolean $equal if false, do a NOT IN test. Default true.
724 * @return string SQL fragment.
725 */
726 public function in_summary_state_test($summarystate, $equal = true) {
727 $states = question_state::get_all_for_summary_state($summarystate);
728 list($sql, $params) = get_in_or_equal($states, SQL_PARAMS_QM, 'param0000', $equal);
729 return $sql;
730 }
731
732 /**
733 * Change the maxmark for the question_attempt with number in usage $slot
734 * for all the specified question_attempts.
735 * @param qubaid_condition $qubaids Selects which usages are updated.
736 * @param integer $slot the number is usage to affect.
737 * @param number $newmaxmark the new max mark to set.
738 */
739 public function set_max_mark_in_attempts(qubaid_condition $qubaids, $slot, $newmaxmark) {
740 set_field_select('question_attempts', 'maxmark', $newmaxmark,
741 "questionusageid {$qubaids->usage_id_in()} AND slot = $slot");
742 }
743
744 /**
745 * Return a subquery that computes the sum of the marks for all the questions
746 * in a usage. Which useage to compute the sum for is controlled bu the $qubaid
747 * parameter.
748 *
749 * See {@link quiz_update_all_attempt_sumgrades()} for an example of the usage of
750 * this method.
751 *
752 * @param string $qubaid SQL fragment that controls which usage is summed.
753 * This might be the name of a column in the outer query.
754 * @return string SQL code for the subquery.
755 */
756 public function sum_usage_marks_subquery($qubaid) {
757 global $CFG;
758 return "SELECT SUM(qa.maxmark * qas.fraction)
759 FROM {$CFG->prefix}question_attempts qa
760 JOIN (
761 SELECT summarks_qa.id AS questionattemptid, MAX(summarks_qas.id) AS latestid
762 FROM {$CFG->prefix}question_attempt_steps summarks_qas
763 JOIN {$CFG->prefix}question_attempts summarks_qa ON summarks_qa.id = summarks_qas.questionattemptid
764 WHERE summarks_qa.questionusageid = $qubaid
765 GROUP BY summarks_qa.id
766 ) lateststepid ON lateststepid.questionattemptid = qa.id
767 JOIN {$CFG->prefix}question_attempt_steps qas ON qas.id = lateststepid.latestid
768 WHERE qa.questionusageid = $qubaid
769 HAVING COUNT(CASE WHEN qas.state = 'needsgrading' THEN 1 ELSE NULL END) = 0";
770 }
771
772 public function question_attempt_latest_state_view($alias) {
773 global $CFG;
774 return "(
775 SELECT
776 {$alias}qa.id AS questionattemptid,
777 {$alias}qa.questionusageid,
778 {$alias}qa.slot,
779 {$alias}qa.behaviour,
780 {$alias}qa.questionid,
781 {$alias}qa.maxmark,
782 {$alias}qa.minfraction,
783 {$alias}qa.flagged,
784 {$alias}qa.questionsummary,
785 {$alias}qa.rightanswer,
786 {$alias}qa.responsesummary,
787 {$alias}qa.timemodified,
788 {$alias}qas.id AS attemptstepid,
789 {$alias}qas.sequencenumber,
790 {$alias}qas.state,
791 {$alias}qas.fraction,
792 {$alias}qas.timecreated,
793 {$alias}qas.userid
794
795 FROM {$CFG->prefix}question_attempts {$alias}qa
796 JOIN {$CFG->prefix}question_attempt_steps {$alias}qas ON
797 {$alias}qas.id = {$this->latest_step_for_qa_subquery($alias . 'qa.id')}
798 ) $alias";
799 }
800
801 protected function latest_step_for_qa_subquery($questionattemptid = 'qa.id') {
802 global $CFG;
803 return "(
804 SELECT MAX(id)
805 FROM {$CFG->prefix}question_attempt_steps
806 WHERE questionattemptid = $questionattemptid
807 )";
808 }
809
810 /**
811 * @param array $questionids of question ids.
812 * @return boolean whether any of these questions are being used by the question engine.
813 */
814 public static function questions_in_use(array $questionids) {
815 return record_exists_select('question_attempts', 'questionid IN (' .
816 implode(',', $questionids) . ')');
817 }
818}
819
820/**
821 * Implementation of the unit of work pattern for the question engine.
822 *
823 * See http://martinfowler.com/eaaCatalog/unitOfWork.html. This tracks all the
824 * changes to a {@link question_usage_by_activity}, and its constituent parts,
825 * so that the changes can be saved to the database when {@link save()} is called.
826 *
827 * @copyright 2009 The Open University
828 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
829 */
830class question_engine_unit_of_work implements question_usage_observer {
831 /** @var question_usage_by_activity the usage being tracked. */
832 protected $quba;
833
834 /** @var boolean whether any of the fields of the usage have been changed. */
835 protected $modified = false;
836
837 /**
838 * @var array list of number in usage => {@link question_attempt}s that
839 * were already in the usage, and which have been modified.
840 */
841 protected $attemptsmodified = array();
842
843 /**
844 * @var array list of number in usage => {@link question_attempt}s that
845 * have been added to the usage.
846 */
847 protected $attemptsadded = array();
848
849 /**
850 * @var array list of question attempt ids to delete the steps for, before
851 * inserting new steps.
852 */
853 protected $attemptstodeletestepsfor = array();
854
855 /**
856 * @var array list of array(question_attempt_step, question_attempt id, seq number)
857 * of steps that have been added to question attempts in this usage.
858 */
859 protected $stepsadded = array();
860
861 /**
862 * Constructor.
863 * @param question_usage_by_activity $quba the usage to track.
864 */
865 public function __construct(question_usage_by_activity $quba) {
866 $this->quba = $quba;
867 }
868
869 public function notify_modified() {
870 $this->modified = true;
871 }
872
873 public function notify_attempt_modified(question_attempt $qa) {
874 $no = $qa->get_slot();
875 if (!array_key_exists($no, $this->attemptsadded)) {
876 $this->attemptsmodified[$no] = $qa;
877 }
878 }
879
880 public function notify_attempt_added(question_attempt $qa) {
881 $this->attemptsadded[$qa->get_slot()] = $qa;
882 }
883
884 public function notify_delete_attempt_steps(question_attempt $qa) {
885
886 if (array_key_exists($qa->get_slot(), $this->attemptsadded)) {
887 return;
888 }
889
890 $qaid = $qa->get_database_id();
891 foreach ($this->stepsadded as $key => $stepinfo) {
892 if ($stepinfo[1] == $qaid) {
893 unset($this->stepsadded[$key]);
894 }
895 }
896
897 $this->attemptstodeletestepsfor[$qaid] = 1;
898 }
899
900 public function notify_step_added(question_attempt_step $step, question_attempt $qa, $seq) {
901 if (array_key_exists($qa->get_slot(), $this->attemptsadded)) {
902 return;
903 }
904 $this->stepsadded[] = array($step, $qa->get_database_id(), $seq);
905 }
906
907 /**
908 * Write all the changes we have recorded to the database.
909 * @param question_engine_data_mapper $dm the mapper to use to update the database.
910 */
911 public function save(question_engine_data_mapper $dm) {
912 $dm->delete_steps_for_question_attempts(array_keys($this->attemptstodeletestepsfor));
913 foreach ($this->stepsadded as $stepinfo) {
914 list($step, $questionattemptid, $seq) = $stepinfo;
915 $dm->insert_question_attempt_step($step, $questionattemptid, $seq);
916 }
917 foreach ($this->attemptsadded as $qa) {
918 $dm->insert_question_attempt($qa);
919 }
920 foreach ($this->attemptsmodified as $qa) {
921 $dm->update_question_attempt($qa);
922 }
923 if ($this->modified) {
924 $dm->update_questions_usage_by_activity($this->quba);
925 }
926 }
927}
928
929
930/**
931 * This class represents a restriction on the set of question_usage ids to include
932 * in a larger database query. Depending of the how you are going to restrict the
933 * list of usages, construct an appropriate subclass.
934 *
935 * If $qubaids is an instance of this class, example usage might be
936 *
937 * SELECT qa.id, qa.maxmark
938 * FROM $qubaids->from_question_attempts('qa')
939 * WHERE $qubaids->where() AND qa.slot = 1
940 *
941 * @copyright 2010 The Open University
942 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
943 */
944abstract class qubaid_condition {
945
946 /**
947 * @return string the SQL that needs to go in the FROM clause when trying
948 * to select records from the 'question_attempts' table based on the
949 * qubaid_condition.
950 */
951 public abstract function from_question_attempts($alias);
952
953 /** @return string the SQL that needs to go in the where clause. */
954 public abstract function where();
955
956 /**
957 * @return the params needed by a query that uses
958 * {@link from_question_attempts()} and {@link where()}.
959 */
960 public abstract function from_where_params();
961
962 /**
963 * @return string SQL that can use used in a WHERE qubaid IN (...) query.
964 * This method returns the "IN (...)" part.
965 */
966 public abstract function usage_id_in();
967
968 /**
969 * @return the params needed by a query that uses {@link usage_id_in()}.
970 */
971 public abstract function usage_id_in_params();
972}
973
974
975/**
976 * This class represents a restriction on the set of question_usage ids to include
977 * in a larger database query based on an explicit list of ids.
978 *
979 * @copyright 2010 The Open University
980 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
981 */
982class qubaid_list extends qubaid_condition {
983 /** @var array of ids. */
984 protected $qubaids;
985 protected $columntotest = null;
986 protected $params;
987
988 /**
989 * Constructor.
990 * @param array $qubaids of question usage ids.
991 */
992 public function __construct(array $qubaids) {
993 $this->qubaids = $qubaids;
994 }
995
996 public function from_question_attempts($alias) {
997 global $CFG;
998 $this->columntotest = $alias . '.questionusageid';
999 return "{$CFG->prefix}question_attempts $alias";
1000 }
1001
1002 public function where() {
1003 global $DB;
1004
1005 if (is_null($this->columntotest)) {
1006 throw new coding_exception('Must call another method that before where().');
1007 }
1008 if (empty($this->qubaids)) {
1009 return '1 = 0';
1010 }
1011 list($where, $this->params) = $DB->get_in_or_equal($this->qubaids, SQL_PARAMS_NAMED, 'qubaid0000');
1012
1013 return "{$this->columntotest} {$this->usage_id_in()}";
1014 }
1015
1016 public function from_where_params() {
1017 return $this->params;
1018 }
1019
1020 public function usage_id_in() {
1021 global $DB;
1022
1023 if (empty($this->qubaids)) {
1024 return '= 0';
1025 }
1026 list($where, $this->params) = $DB->get_in_or_equal($this->qubaids, SQL_PARAMS_NAMED, 'qubaid0000');
1027 return $where;
1028 }
1029
1030 public function usage_id_in_params() {
1031 return $this->params;
1032 }
1033}
1034
1035
1036/**
1037 * This class represents a restriction on the set of question_usage ids to include
1038 * in a larger database query based on JOINing to some other tables.
1039 *
1040 * The general form of the query is something like
1041 *
1042 * SELECT qa.id, qa.maxmark
1043 * FROM $from
1044 * JOIN {$CFG->prefix}question_attempts qa ON qa.questionusageid = $usageidcolumn
1045 * WHERE $where AND qa.slot = 1
1046 *
1047 * where $from, $usageidcolumn and $where are the arguments to the constructor.
1048 *
1049 * @copyright 2010 The Open University
1050 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
1051 */
1052class qubaid_join extends qubaid_condition {
1053 public $from;
1054 public $usageidcolumn;
1055 public $where;
1056 public $params;
1057
1058 /**
1059 * Constructor. The meaning of the arguments is explained in the class comment.
1060 * @param string $from SQL fragemnt to go in the FROM clause.
1061 * @param string $usageidcolumn the column in $from that should be
1062 * made equal to the usageid column in the JOIN clause.
1063 * @param string $where SQL fragment to go in the where clause.
1064 */
1065 public function __construct($from, $usageidcolumn, $where = '', $params = array()) {
1066 $this->from = $from;
1067 $this->usageidcolumn = $usageidcolumn;
1068 $this->params = $params;
1069 if (empty($where)) {
1070 $where = '1 = 1';
1071 }
1072 $this->where = $where;
1073 }
1074
1075 public function from_question_attempts($alias) {
1076 global $CFG;
1077 return "$this->from
1078 JOIN {$CFG->prefix}question_attempts {$alias} ON " .
1079 "{$alias}.questionusageid = $this->usageidcolumn";
1080 }
1081
1082 public function where() {
1083 return $this->where;
1084 }
1085
1086 public function from_where_params() {
1087 return $this->params;
1088 }
1089
1090 public function usage_id_in() {
1091 return "IN (SELECT $this->usageidcolumn FROM $this->from WHERE $this->where)";
1092 }
1093
1094 public function usage_id_in_params() {
1095 return $this->params;
1096 }
1097}