MDL-20636 Reveiw all throw statements.
[moodle.git] / question / engine / datalib.php
1 <?php
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/>.
18 /**
19  * Code for loading and saving quiz attempts to and from the database.
20  *
21  * @package    moodlecore
22  * @subpackage questionengine
23  * @copyright  2009 The Open University
24  * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
25  */
28 defined('MOODLE_INTERNAL') || die();
31 /**
32  * This class controls the loading and saving of question engine data to and from
33  * the database.
34  *
35  * @copyright  2009 The Open University
36  * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
37  */
38 class question_engine_data_mapper {
39     /**
40      * @var moodle_database normally points to global $DB, but I prefer not to
41      * use globals if I can help it.
42      */
43     protected $db;
45     /**
46      * @param moodle_database $db a database connectoin. Defaults to global $DB.
47      */
48     public function __construct($db = null) {
49         if (is_null($db)) {
50             global $DB;
51             $this->db = $DB;
52         } else {
53             $this->db = $db;
54         }
55     }
57     /**
58      * Store an entire {@link question_usage_by_activity} in the database,
59      * including all the question_attempts that comprise it.
60      * @param question_usage_by_activity $quba the usage to store.
61      */
62     public function insert_questions_usage_by_activity(question_usage_by_activity $quba) {
63         $record = new stdClass();
64         $record->contextid = $quba->get_owning_context()->id;
65         $record->component = addslashes($quba->get_owning_component());
66         $record->preferredbehaviour = addslashes($quba->get_preferred_behaviour());
68         $newid = $this->db->insert_record('question_usages', $record);
69         $quba->set_id_from_database($newid);
71         foreach ($quba->get_attempt_iterator() as $qa) {
72             $this->insert_question_attempt($qa);
73         }
74     }
76     /**
77      * Store an entire {@link question_attempt} in the database,
78      * including all the question_attempt_steps that comprise it.
79      * @param question_attempt $qa the question attempt to store.
80      */
81     public function insert_question_attempt(question_attempt $qa) {
82         $record = new stdClass();
83         $record->questionusageid = $qa->get_usage_id();
84         $record->slot = $qa->get_slot();
85         $record->behaviour = addslashes($qa->get_behaviour_name());
86         $record->questionid = $qa->get_question()->id;
87         $record->maxmark = $qa->get_max_mark();
88         $record->minfraction = $qa->get_min_fraction();
89         $record->flagged = $qa->is_flagged();
90         $record->questionsummary = addslashes($qa->get_question_summary());
91         $record->rightanswer = addslashes($qa->get_right_answer_summary());
92         $record->responsesummary = addslashes($qa->get_response_summary());
93         $record->timemodified = time();
94         $record->id = $this->db->insert_record('question_attempts', $record);
96         foreach ($qa->get_step_iterator() as $seq => $step) {
97             $this->insert_question_attempt_step($step, $record->id, $seq);
98         }
99     }
101     /**
102      * Store a {@link question_attempt_step} in the database.
103      * @param question_attempt_step $qa the step to store.
104      */
105     public function insert_question_attempt_step(question_attempt_step $step,
106             $questionattemptid, $seq) {
107         $record = new stdClass();
108         $record->questionattemptid = $questionattemptid;
109         $record->sequencenumber = $seq;
110         $record->state = addslashes('' . $step->get_state());
111         $record->fraction = $step->get_fraction();
112         $record->timecreated = $step->get_timecreated();
113         $record->userid = $step->get_user_id();
115         $record->id = $this->db->insert_record('question_attempt_steps', $record);
117         foreach ($step->get_all_data() as $name => $value) {
118             $data = new stdClass();
119             $data->attemptstepid = $record->id;
120             $data->name = addslashes($name);
121             $data->value = addslashes($value);
122             $this->db->insert_record('question_attempt_step_data', $data, false);
123         }
124     }
126     /**
127      * Load a {@link question_attempt_step} from the database.
128      * @param int $stepid the id of the step to load.
129      * @param question_attempt_step the step that was loaded.
130      */
131     public function load_question_attempt_step($stepid) {
132         $records = $this->db->get_records_sql("
133 SELECT
134     COALESCE(qasd.id, -1 * qas.id) AS id,
135     qas.id AS attemptstepid,
136     qas.questionattemptid,
137     qas.sequencenumber,
138     qas.state,
139     qas.fraction,
140     qas.timecreated,
141     qas.userid,
142     qasd.name,
143     qasd.value
145 FROM {question_attempt_steps} qas
146 LEFT JOIN {question_attempt_step_data} qasd ON qasd.attemptstepid = qas.id
148 WHERE
149     qas.id = :stepid
150         ", array('stepid' => $stepid));
152         if (!$records) {
153             throw new coding_exception('Failed to load question_attempt_step ' . $stepid);
154         }
156         return question_attempt_step::load_from_records($records, $stepid);
157     }
159     /**
160      * Load a {@link question_attempt} from the database, including all its
161      * steps.
162      * @param int $questionattemptid the id of the question attempt to load.
163      * @param question_attempt the question attempt that was loaded.
164      */
165     public function load_question_attempt($questionattemptid) {
166         $records = $this->db->get_records_sql("
167 SELECT
168     COALESCE(qasd.id, -1 * qas.id) AS id,
169     quba.contextid,
170     quba.preferredbehaviour,
171     qa.id AS questionattemptid,
172     qa.questionusageid,
173     qa.slot,
174     qa.behaviour,
175     qa.questionid,
176     qa.maxmark,
177     qa.minfraction,
178     qa.flagged,
179     qa.questionsummary,
180     qa.rightanswer,
181     qa.responsesummary,
182     qa.timemodified,
183     qas.id AS attemptstepid,
184     qas.sequencenumber,
185     qas.state,
186     qas.fraction,
187     qas.timecreated,
188     qas.userid,
189     qasd.name,
190     qasd.value
192 FROM      {question_attempts           qa
193 JOIN      {question_usages}            quba ON quba.id               = qa.questionusageid
194 LEFT JOIN {question_attempt_steps}     qas  ON qas.questionattemptid = qa.id
195 LEFT JOIN {question_attempt_step_data} qasd ON qasd.attemptstepid    = qas.id
197 WHERE
198     qa.id = :questionattemptid
200 ORDER BY
201     qas.sequencenumber
202         ", array('questionattemptid' => $questionattemptid));
204         if (!$records) {
205             throw new coding_exception('Failed to load question_attempt ' . $questionattemptid);
206         }
208         $record = current($records);
209         return question_attempt::load_from_records($records, $questionattemptid,
210                 new question_usage_null_observer(), $record->preferredbehaviour);
211     }
213     /**
214      * Load a {@link question_usage_by_activity} from the database, including
215      * all its {@link question_attempt}s and all their steps.
216      * @param int $qubaid the id of the usage to load.
217      * @param question_usage_by_activity the usage that was loaded.
218      */
219     public function load_questions_usage_by_activity($qubaid) {
220         $records = $this->db->get_records_sql("
221 SELECT
222     COALESCE(qasd.id, -1 * qas.id) AS id,
223     quba.id AS qubaid,
224     quba.contextid,
225     quba.component,
226     quba.preferredbehaviour,
227     qa.id AS questionattemptid,
228     qa.questionusageid,
229     qa.slot,
230     qa.behaviour,
231     qa.questionid,
232     qa.maxmark,
233     qa.minfraction,
234     qa.flagged,
235     qa.questionsummary,
236     qa.rightanswer,
237     qa.responsesummary,
238     qa.timemodified,
239     qas.id AS attemptstepid,
240     qas.sequencenumber,
241     qas.state,
242     qas.fraction,
243     qas.timecreated,
244     qas.userid,
245     qasd.name,
246     qasd.value
248 FROM      {question_usages}            quba
249 LEFT JOIN {question_attempts}          qa   ON qa.questionusageid    = quba.id
250 LEFT JOIN {question_attempt_steps}     qas  ON qas.questionattemptid = qa.id
251 LEFT JOIN {question_attempt_step_data} qasd ON qasd.attemptstepid    = qas.id
253 WHERE
254     quba.id = :qubaid
256 ORDER BY
257     qa.slot,
258     qas.sequencenumber
259     ", array('qubaid' => $qubaid));
261         if (!$records) {
262             throw new coding_exception('Failed to load questions_usage_by_activity ' . $qubaid);
263         }
265         return question_usage_by_activity::load_from_records($records, $qubaid);
266     }
268     /**
269      * Load information about the latest state of each question from the database.
270      *
271      * @param qubaid_condition $qubaids used to restrict which usages are included
272      * in the query. See {@link qubaid_condition}.
273      * @param array $slots A list of slots for the questions you want to konw about.
274      * @return array of records. See the SQL in this function to see the fields available.
275      */
276     public function load_questions_usages_latest_steps(qubaid_condition $qubaids, $slots) {
277         list($slottest, $params) = $this->db->get_in_or_equal($slots, SQL_PARAMS_NAMED, 'slot0000');
279         $records = $this->db->get_records_sql("
280 SELECT
281     qas.id,
282     qa.id AS questionattemptid,
283     qa.questionusageid,
284     qa.slot,
285     qa.behaviour,
286     qa.questionid,
287     qa.maxmark,
288     qa.minfraction,
289     qa.flagged,
290     qa.questionsummary,
291     qa.rightanswer,
292     qa.responsesummary,
293     qa.timemodified,
294     qas.id AS attemptstepid,
295     qas.sequencenumber,
296     qas.state,
297     qas.fraction,
298     qas.timecreated,
299     qas.userid
301 FROM {$qubaids->from_question_attempts('qa')}
302 JOIN {question_attempt_steps} qas ON
303         qas.id = {$this->latest_step_for_qa_subquery()}
305 WHERE
306     {$qubaids->where()} AND
307     qa.slot $slottest
308         ", $params + $qubaids->from_where_params());
310         return $records;
311     }
313     /**
314      * Load summary information about the state of each question in a group of attempts.
315      * This is used by the quiz manual grading report, to show how many attempts
316      * at each question need to be graded.
317      *
318      * @param qubaid_condition $qubaids used to restrict which usages are included
319      * in the query. See {@link qubaid_condition}.
320      * @param array $slots A list of slots for the questions you want to konw about.
321      * @return array The array keys are slot,qestionid. The values are objects with
322      * fields $slot, $questionid, $inprogress, $name, $needsgrading, $autograded,
323      * $manuallygraded and $all.
324      */
325     public function load_questions_usages_question_state_summary(qubaid_condition $qubaids, $slots) {
326         list($slottest, $params) = $this->db->get_in_or_equal($slots, SQL_PARAMS_NAMED, 'slot0000');
328         $rs = $this->db->get_recordset_sql("
329 SELECT
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
338 FROM {$qubaids->from_question_attempts('qa')}
339 JOIN {question_attempt_steps} qas ON
340         qas.id = {$this->latest_step_for_qa_subquery()}
341 JOIN {question} q ON q.id = qa.questionid
343 WHERE
344     {$qubaids->where()} AND
345     qa.slot $slottest
347 GROUP BY
348     qa.slot,
349     qa.questionid,
350     q.name,
351     q.id,
352     summarystate
354 ORDER BY
355     qa.slot,
356     qa.questionid,
357     q.name,
358     q.id
359         ", $params + $qubaids->from_where_params());
361         $results = array();
362         foreach ($rs as $row) {
363             $index = $row->slot . ',' . $row->questionid;
365             if (!array_key_exists($index, $results)) {
366                 $res = new stdClass();
367                 $res->slot = $row->slot;
368                 $res->questionid = $row->questionid;
369                 $res->name = $row->name;
370                 $res->inprogress = 0;
371                 $res->needsgrading = 0;
372                 $res->autograded = 0;
373                 $res->manuallygraded = 0;
374                 $res->all = 0;
375                 $results[$index] = $res;
376             }
378             $results[$index]->{$row->summarystate} = $row->numattempts;
379             $results[$index]->all += $row->numattempts;
380         }
381         $rs->close();
383         return $results;
384     }
386     /**
387      * Get a list of usage ids where the question with slot $slot, and optionally
388      * also with question id $questionid, is in summary state $summarystate. Also
389      * return the total count of such states.
390      *
391      * Only a subset of the ids can be returned by using $orderby, $limitfrom and
392      * $limitnum. A special value 'random' can be passed as $orderby, in which case
393      * $limitfrom is ignored.
394      *
395      * @param qubaid_condition $qubaids used to restrict which usages are included
396      * in the query. See {@link qubaid_condition}.
397      * @param int $slot The slot for the questions you want to konw about.
398      * @param int $questionid (optional) Only return attempts that were of this specific question.
399      * @param string $summarystate the summary state of interest, or 'all'.
400      * @param string $orderby the column to order by.
401      * @param array $params any params required by any of the SQL fragments.
402      * @param int $limitfrom implements paging of the results.
403      *      Ignored if $orderby = random or $limitnum is null.
404      * @param int $limitnum implements paging of the results. null = all.
405      * @return array with two elements, an array of usage ids, and a count of the total number.
406      */
407     public function load_questions_usages_where_question_in_state(
408             qubaid_condition $qubaids, $summarystate, $slot, $questionid = null,
409             $orderby = 'random', $params, $limitfrom = 0, $limitnum = null) {
411         $extrawhere = '';
412         if ($questionid) {
413             $extrawhere .= ' AND qa.questionid = :questionid';
414             $params['questionid'] = $questionid;
415         }
416         if ($summarystate != 'all') {
417             list($test, $sparams) = $this->in_summary_state_test($summarystate);
418             $extrawhere .= ' AND qas.state ' . $test;
419             $params += $sparams;
420         }
422         if ($orderby == 'random') {
423             $sqlorderby = '';
424         } else if ($orderby) {
425             $sqlorderby = 'ORDER BY ' . $orderby;
426         } else {
427             $sqlorderby = '';
428         }
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         $params += $qubaids->from_where_params();
437         $params['slot'] = $slot;
438         $qubaids = $this->db->get_records_sql_menu("
439 SELECT
440     qa.questionusageid,
441     1
443 FROM {$qubaids->from_question_attempts('qa')}
444 JOIN {question_attempt_steps} qas ON
445         qas.id = {$this->latest_step_for_qa_subquery()}
446 JOIN {question} q ON q.id = qa.questionid
448 WHERE
449     {$qubaids->where()} AND
450     qa.slot = :slot
451     $extrawhere
453 $sqlorderby
454         ", $params);
456         $qubaids = array_keys($qubaids);
457         $count = count($qubaids);
459         if ($orderby == 'random') {
460             shuffle($qubaids);
461             $limitfrom = 0;
462         }
464         if (!is_null($limitnum)) {
465             $qubaids = array_slice($qubaids, $limitfrom, $limitnum);
466         }
468         return array($qubaids, $count);
469     }
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         if (!empty($slots)) {
481             list($slottest, $slotsparams) = $this->db->get_in_or_equal($slots, SQL_PARAMS_NAMED, 'slot0000');
482             $slotwhere = " AND qa.slot $slottest";
483         } else {
484             $slotwhere = '';
485             $params = array();
486         }
488         list($statetest, $stateparams) = $this->db->get_in_or_equal(array(
489                 question_state::$gaveup,
490                 question_state::$gradedwrong,
491                 question_state::$gradedpartial,
492                 question_state::$gradedright,
493                 question_state::$mangaveup,
494                 question_state::$mangrwrong,
495                 question_state::$mangrpartial,
496                 question_state::$mangrright), SQL_PARAMS_NAMED, 'st00');
498         return $this->db->get_records_sql("
499 SELECT
500     qa.slot,
501     AVG(COALESCE(qas.fraction, 0)) AS averagefraction,
502     COUNT(1) AS numaveraged
504 FROM {$qubaids->from_question_attempts('qa')}
505 JOIN {question_attempt_steps} qas ON
506         qas.id = {$this->latest_step_for_qa_subquery()}
508 WHERE
509     {$qubaids->where()}
510     $slotwhere
511     AND qas.state $statetest
513 GROUP BY qa.slot
515 ORDER BY qa.slot
516         ", $slotsparams + $stateparams + $qubaids->from_where_params());
517     }
519     /**
520      * Load a {@link question_attempt} from the database, including all its
521      * steps.
522      * @param int $questionid the question to load all the attempts fors.
523      * @param qubaid_condition $qubaids used to restrict which usages are included
524      * in the query. See {@link qubaid_condition}.
525      * @return array of question_attempts.
526      */
527     public function load_attempts_at_question($questionid, qubaid_condition $qubaids) {
528         global $DB;
530         $params = $qubaids->from_where_params();
531         $params['questionid'] = $questionid;
533         $records = $DB->get_records_sql("
534 SELECT
535     COALESCE(qasd.id, -1 * qas.id) AS id,
536     quba.contextid,
537     quba.preferredbehaviour,
538     qa.id AS questionattemptid,
539     qa.questionusageid,
540     qa.slot,
541     qa.behaviour,
542     qa.questionid,
543     qa.maxmark,
544     qa.minfraction,
545     qa.flagged,
546     qa.questionsummary,
547     qa.rightanswer,
548     qa.responsesummary,
549     qa.timemodified,
550     qas.id AS attemptstepid,
551     qas.sequencenumber,
552     qas.state,
553     qas.fraction,
554     qas.timecreated,
555     qas.userid,
556     qasd.name,
557     qasd.value
559 FROM {$qubaids->from_question_attempts('qa')}
560 JOIN {question_usages} quba ON quba.id = qa.questionusageid
561 LEFT JOIN {question_attempt_steps} qas ON qas.questionattemptid = qa.id
562 LEFT JOIN {question_attempt_step_data} qasd ON qasd.attemptstepid = qas.id
564 WHERE
565     {$qubaids->where()} AND
566     qa.questionid = :questionid
568 ORDER BY
569     quba.id,
570     qa.id,
571     qas.sequencenumber
572         ", $params);
574         if (!$records) {
575             return array();
576         }
578         $questionattempts = array();
579         $record = current($records);
580         while ($record) {
581             $questionattempts[$record->questionattemptid] =
582                     question_attempt::load_from_records($records,
583                     $record->questionattemptid, new question_usage_null_observer(),
584                     $record->preferredbehaviour);
585             $record = current($records);
586         }
587         return $questionattempts;
588     }
590     /**
591      * Update a question_usages row to refect any changes in a usage (but not
592      * any of its question_attempts.
593      * @param question_usage_by_activity $quba the usage that has changed.
594      */
595     public function update_questions_usage_by_activity(question_usage_by_activity $quba) {
596         $record = new stdClass();
597         $record->id = $quba->get_id();
598         $record->contextid = $quba->get_owning_context()->id;
599         $record->component = addslashes($quba->get_owning_component());
600         $record->preferredbehaviour = addslashes($quba->get_preferred_behaviour());
602         $this->db->update_record('question_usages', $record);
603     }
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();
621         $this->db->update_record('question_attempts', $record);
622     }
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 {question_usages}.id in full like that.
630      * @param array $params values to substitute for placeholders in $where.
631      */
632     public function delete_questions_usage_by_activities($where, $params) {
633         $this->db->delete_records_select('question_attempt_step_data', "attemptstepid IN (
634                 SELECT qas.id
635                 FROM {question_attempts} qa
636                 JOIN {question_attempt_steps} qas ON qas.questionattemptid = qa.id
637                 JOIN {question_usages} ON qa.questionusageid = {question_usages}.id
638                 WHERE $where)", $params);
639         $this->db->delete_records_select('question_attempt_steps', "questionattemptid IN (
640                 SELECT qa.id
641                 FROM {question_attempts} qa
642                 JOIN {question_usages} ON qa.questionusageid = {question_usages}.id
643                 WHERE $where)", $params);
644         $this->db->delete_records_select('question_attempts', "questionusageid IN (
645                 SELECT id
646                 FROM {question_usages}
647                 WHERE $where)", $params);
648         $this->db->delete_records_select('question_usages', $where, $params);
649     }
651     /**
652      * Delete all the steps for a question attempt.
653      * @param int $qaids question_attempt id.
654      */
655     public function delete_steps_for_question_attempts($qaids) {
656         if (empty($qaids)) {
657             return;
658         }
659         list($test, $params) = $this->db->get_in_or_equal($qaids);
660         $this->db->delete_records_select('question_attempt_step_data', "attemptstepid IN (
661                 SELECT qas.id
662                 FROM {question_attempt_steps} qas
663                 WHERE questionattemptid $test)", $params);
664         $this->db->delete_records_select('question_attempt_steps', 'questionattemptid ' . $test, $params);
665     }
667     /**
668      * Delete all the previews for a given question.
669      * @param int $questionid question id.
670      */
671     public function delete_previews($questionid) {
672         $previews = $this->db->get_records_sql_menu("
673                 SELECT DISTINCT quba.id, 1
674                 FROM {question_usages} quba
675                 JOIN {question_attempts} qa ON qa.questionusageid = quba.id
676                 WHERE quba.component = 'core_question_preview' AND
677                     qa.questionid = ?", array($questionid));
678         if (empty($previews)) {
679             return;
680         }
681         list($test, $params) = $this->db->get_in_or_equal(array_keys($previews));
682         $this->delete_questions_usage_by_activities('question_usages.id ' . $test, $params);
683     }
685     /**
686      * Update the flagged state of a question in the database.
687      * @param int $qubaid the question usage id.
688      * @param int $questionid the question id.
689      * @param int $sessionid the question_attempt id.
690      * @param bool $newstate the new state of the flag. true = flagged.
691      */
692     public function update_question_attempt_flag($qubaid, $questionid, $qaid, $slot, $newstate) {
693         if (!$this->db->record_exists('question_attempts', array('id' => $qaid,
694                 'questionusageid' => $qubaid, 'questionid' => $questionid, 'slot' => $slot))) {
695             throw new moodle_exception('errorsavingflags', 'question');
696         }
698         $this->db->set_field('question_attempts', 'flagged', $newstate, array('id' => $qaid));
699     }
701     /**
702      * Get all the WHEN 'x' THEN 'y' terms needed to convert the question_attempt_steps.state
703      * column to a summary state. Use this like
704      * CASE qas.state {$this->full_states_to_summary_state_sql()} END AS summarystate,
705      * @param string SQL fragment.
706      */
707     protected function full_states_to_summary_state_sql() {
708         $sql = '';
709         foreach (question_state::get_all() as $state) {
710             $sql .= "WHEN '$state' THEN '{$state->get_summary_state()}'\n";
711         }
712         return $sql;
713     }
715     /**
716      * Get the SQL needed to test that question_attempt_steps.state is in a
717      * state corresponding to $summarystate.
718      * @param string $summarystate one of
719      * inprogress, needsgrading, manuallygraded or autograded
720      * @param bool $equal if false, do a NOT IN test. Default true.
721      * @return string SQL fragment.
722      */
723     public function in_summary_state_test($summarystate, $equal = true, $prefix = 'summarystates') {
724         $states = question_state::get_all_for_summary_state($summarystate);
725         return $this->db->get_in_or_equal($states, SQL_PARAMS_NAMED, $prefix . '00', $equal);
726     }
728     /**
729      * Change the maxmark for the question_attempt with number in usage $slot
730      * for all the specified question_attempts.
731      * @param qubaid_condition $qubaids Selects which usages are updated.
732      * @param int $slot the number is usage to affect.
733      * @param number $newmaxmark the new max mark to set.
734      */
735     public function set_max_mark_in_attempts(qubaid_condition $qubaids, $slot, $newmaxmark) {
736         $this->db->set_field_select('question_attempts', 'maxmark', $newmaxmark,
737                 "questionusageid {$qubaids->usage_id_in()} AND slot = :slot",
738                 $qubaids->usage_id_in_params() + array('slot' => $slot));
739     }
741     /**
742      * Return a subquery that computes the sum of the marks for all the questions
743      * in a usage. Which useage to compute the sum for is controlled bu the $qubaid
744      * parameter.
745      *
746      * See {@link quiz_update_all_attempt_sumgrades()} for an example of the usage of
747      * this method.
748      *
749      * @param string $qubaid SQL fragment that controls which usage is summed.
750      * This might be the name of a column in the outer query.
751      * @return string SQL code for the subquery.
752      */
753     public function sum_usage_marks_subquery($qubaid) {
754         return "SELECT SUM(qa.maxmark * qas.fraction)
755             FROM {question_attempts} qa
756             JOIN (
757                 SELECT summarks_qa.id AS questionattemptid, MAX(summarks_qas.id) AS latestid
758                 FROM {question_attempt_steps} summarks_qas
759                 JOIN {question_attempts} summarks_qa ON summarks_qa.id = summarks_qas.questionattemptid
760                 WHERE summarks_qa.questionusageid = $qubaid
761                 GROUP BY summarks_qa.id
762             ) lateststepid ON lateststepid.questionattemptid = qa.id
763             JOIN {question_attempt_steps} qas ON qas.id = lateststepid.latestid
764             WHERE qa.questionusageid = $qubaid
765             HAVING COUNT(CASE WHEN qas.state = 'needsgrading' AND qa.maxmark > 0 THEN 1 ELSE NULL END) = 0";
766         // TODO handle $qubaid with placeholders.
767     }
769     public function question_attempt_latest_state_view($alias) {
770         return "(
771                 SELECT
772                     {$alias}qa.id AS questionattemptid,
773                     {$alias}qa.questionusageid,
774                     {$alias}qa.slot,
775                     {$alias}qa.behaviour,
776                     {$alias}qa.questionid,
777                     {$alias}qa.maxmark,
778                     {$alias}qa.minfraction,
779                     {$alias}qa.flagged,
780                     {$alias}qa.questionsummary,
781                     {$alias}qa.rightanswer,
782                     {$alias}qa.responsesummary,
783                     {$alias}qa.timemodified,
784                     {$alias}qas.id AS attemptstepid,
785                     {$alias}qas.sequencenumber,
786                     {$alias}qas.state,
787                     {$alias}qas.fraction,
788                     {$alias}qas.timecreated,
789                     {$alias}qas.userid
791                 FROM {question_attempts} {$alias}qa
792                 JOIN {question_attempt_steps} {$alias}qas ON
793                         {$alias}qas.id = {$this->latest_step_for_qa_subquery($alias . 'qa.id')}
794             ) $alias";
795     }
797     protected function latest_step_for_qa_subquery($questionattemptid = 'qa.id') {
798         return "(
799                 SELECT MAX(id)
800                 FROM {question_attempt_steps}
801                 WHERE questionattemptid = $questionattemptid
802             )";
803     }
805     /**
806      * @param array $questionids of question ids.
807      * @return bool whether any of these questions are being used by the question engine.
808      */
809     public function questions_in_use(array $questionids) {
810         list($test, $params) = $this->db->get_in_or_equal($questionids);
811         return $this->db->record_exists_select('question_attempts',
812                 'questionid ' . $test, $params);
813     }
816 /**
817  * Implementation of the unit of work pattern for the question engine.
818  *
819  * See http://martinfowler.com/eaaCatalog/unitOfWork.html. This tracks all the
820  * changes to a {@link question_usage_by_activity}, and its constituent parts,
821  * so that the changes can be saved to the database when {@link save()} is called.
822  *
823  * @copyright  2009 The Open University
824  * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
825  */
826 class question_engine_unit_of_work implements question_usage_observer {
827     /** @var question_usage_by_activity the usage being tracked. */
828     protected $quba;
830     /** @var boolean whether any of the fields of the usage have been changed. */
831     protected $modified = false;
833     /**
834      * @var array list of number in usage => {@link question_attempt}s that
835      * were already in the usage, and which have been modified.
836      */
837     protected $attemptsmodified = array();
839     /**
840      * @var array list of number in usage => {@link question_attempt}s that
841      * have been added to the usage.
842      */
843     protected $attemptsadded = array();
845     /**
846      * @var array list of question attempt ids to delete the steps for, before
847      * inserting new steps.
848      */
849     protected $attemptstodeletestepsfor = array();
851     /**
852      * @var array list of array(question_attempt_step, question_attempt id, seq number)
853      * of steps that have been added to question attempts in this usage.
854      */
855     protected $stepsadded = array();
857     /**
858      * Constructor.
859      * @param question_usage_by_activity $quba the usage to track.
860      */
861     public function __construct(question_usage_by_activity $quba) {
862         $this->quba = $quba;
863     }
865     public function notify_modified() {
866         $this->modified = true;
867     }
869     public function notify_attempt_modified(question_attempt $qa) {
870         $no = $qa->get_slot();
871         if (!array_key_exists($no, $this->attemptsadded)) {
872             $this->attemptsmodified[$no] = $qa;
873         }
874     }
876     public function notify_attempt_added(question_attempt $qa) {
877         $this->attemptsadded[$qa->get_slot()] = $qa;
878     }
880     public function notify_delete_attempt_steps(question_attempt $qa) {
882         if (array_key_exists($qa->get_slot(), $this->attemptsadded)) {
883             return;
884         }
886         $qaid = $qa->get_database_id();
887         foreach ($this->stepsadded as $key => $stepinfo) {
888             if ($stepinfo[1] == $qaid) {
889                 unset($this->stepsadded[$key]);
890             }
891         }
893         $this->attemptstodeletestepsfor[$qaid] = 1;
894     }
896     public function notify_step_added(question_attempt_step $step, question_attempt $qa, $seq) {
897         if (array_key_exists($qa->get_slot(), $this->attemptsadded)) {
898             return;
899         }
900         $this->stepsadded[] = array($step, $qa->get_database_id(), $seq);
901     }
903     /**
904      * Write all the changes we have recorded to the database.
905      * @param question_engine_data_mapper $dm the mapper to use to update the database.
906      */
907     public function save(question_engine_data_mapper $dm) {
908         $dm->delete_steps_for_question_attempts(array_keys($this->attemptstodeletestepsfor));
909         foreach ($this->stepsadded as $stepinfo) {
910             list($step, $questionattemptid, $seq) = $stepinfo;
911             $dm->insert_question_attempt_step($step, $questionattemptid, $seq);
912         }
913         foreach ($this->attemptsadded as $qa) {
914             $dm->insert_question_attempt($qa);
915         }
916         foreach ($this->attemptsmodified as $qa) {
917             $dm->update_question_attempt($qa);
918         }
919         if ($this->modified) {
920             $dm->update_questions_usage_by_activity($this->quba);
921         }
922     }
926 /**
927  * This class represents a restriction on the set of question_usage ids to include
928  * in a larger database query. Depending of the how you are going to restrict the
929  * list of usages, construct an appropriate subclass.
930  *
931  * If $qubaids is an instance of this class, example usage might be
932  *
933  * SELECT qa.id, qa.maxmark
934  * FROM $qubaids->from_question_attempts('qa')
935  * WHERE $qubaids->where() AND qa.slot = 1
936  *
937  * @copyright  2010 The Open University
938  * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
939  */
940 abstract class qubaid_condition {
942     /**
943      * @return string the SQL that needs to go in the FROM clause when trying
944      * to select records from the 'question_attempts' table based on the
945      * qubaid_condition.
946      */
947     public abstract function from_question_attempts($alias);
949     /** @return string the SQL that needs to go in the where clause. */
950     public abstract function where();
952     /**
953      * @return the params needed by a query that uses
954      * {@link from_question_attempts()} and {@link where()}.
955      */
956     public abstract function from_where_params();
958     /**
959      * @return string SQL that can use used in a WHERE qubaid IN (...) query.
960      * This method returns the "IN (...)" part.
961      */
962     public abstract function usage_id_in();
964     /**
965      * @return the params needed by a query that uses {@link usage_id_in()}.
966      */
967     public abstract function usage_id_in_params();
971 /**
972  * This class represents a restriction on the set of question_usage ids to include
973  * in a larger database query based on an explicit list of ids.
974  *
975  * @copyright  2010 The Open University
976  * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
977  */
978 class qubaid_list extends qubaid_condition {
979     /** @var array of ids. */
980     protected $qubaids;
981     protected $columntotest = null;
982     protected $params;
984     /**
985      * Constructor.
986      * @param array $qubaids of question usage ids.
987      */
988     public function __construct(array $qubaids) {
989         $this->qubaids = $qubaids;
990     }
992     public function from_question_attempts($alias) {
993         $this->columntotest = $alias . '.questionusageid';
994         return '{question_attempts} ' . $alias;
995     }
997     public function where() {
998         global $DB;
1000         if (is_null($this->columntotest)) {
1001             throw new coding_exception('Must call from_question_attempts before where().');
1002         }
1003         if (empty($this->qubaids)) {
1004             $this->params = array();
1005             return '1 = 0';
1006         }
1007         list($where, $this->params) = $DB->get_in_or_equal($this->qubaids, SQL_PARAMS_NAMED, 'qubaid0000');
1009         return $this->columntotest . ' ' . $this->usage_id_in();
1010     }
1012     public function from_where_params() {
1013         return $this->params;
1014     }
1016     public function usage_id_in() {
1017         global $DB;
1019         if (empty($this->qubaids)) {
1020             return '= 0';
1021         }
1022         list($where, $this->params) = $DB->get_in_or_equal($this->qubaids, SQL_PARAMS_NAMED, 'qubaid0000');
1023         return $where;
1024     }
1026     public function usage_id_in_params() {
1027         return $this->params;
1028     }
1032 /**
1033  * This class represents a restriction on the set of question_usage ids to include
1034  * in a larger database query based on JOINing to some other tables.
1035  *
1036  * The general form of the query is something like
1037  *
1038  * SELECT qa.id, qa.maxmark
1039  * FROM $from
1040  * JOIN {question_attempts} qa ON qa.questionusageid = $usageidcolumn
1041  * WHERE $where AND qa.slot = 1
1042  *
1043  * where $from, $usageidcolumn and $where are the arguments to the constructor.
1044  *
1045  * @copyright  2010 The Open University
1046  * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
1047  */
1048 class qubaid_join extends qubaid_condition {
1049     public $from;
1050     public $usageidcolumn;
1051     public $where;
1052     public $params;
1054     /**
1055      * Constructor. The meaning of the arguments is explained in the class comment.
1056      * @param string $from SQL fragemnt to go in the FROM clause.
1057      * @param string $usageidcolumn the column in $from that should be
1058      * made equal to the usageid column in the JOIN clause.
1059      * @param string $where SQL fragment to go in the where clause.
1060      * @param array $params required by the SQL. You must use named parameters.
1061      */
1062     public function __construct($from, $usageidcolumn, $where = '', $params = array()) {
1063         $this->from = $from;
1064         $this->usageidcolumn = $usageidcolumn;
1065         $this->params = $params;
1066         if (empty($where)) {
1067             $where = '1 = 1';
1068         }
1069         $this->where = $where;
1070     }
1072     public function from_question_attempts($alias) {
1073         return "$this->from
1074                 JOIN {question_attempts} {$alias} ON " .
1075                         "{$alias}.questionusageid = $this->usageidcolumn";
1076     }
1078     public function where() {
1079         return $this->where;
1080     }
1082     public function from_where_params() {
1083         return $this->params;
1084     }
1086     public function usage_id_in() {
1087         return "IN (SELECT $this->usageidcolumn FROM $this->from WHERE $this->where)";
1088     }
1090     public function usage_id_in_params() {
1091         return $this->params;
1092     }