Commit | Line | Data |
---|---|---|
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 | */ | |
36 | class question_engine_data_mapper { | |
06f8ed54 TH |
37 | /** |
38 | * @var moodle_database normally points to global $DB, but I prefer not to | |
39 | * use globals if I can help it. | |
40 | */ | |
41 | protected $db; | |
42 | ||
43 | /** | |
44 | * @param moodle_database $db a database connectoin. Defaults to global $DB. | |
45 | */ | |
46 | public function __construct($db = null) { | |
47 | if (is_null($db)) { | |
48 | global $DB; | |
06f8ed54 TH |
49 | $this->db = $DB; |
50 | } else { | |
51 | $this->db = $db; | |
52 | } | |
53 | } | |
54 | ||
d1b7e03d TH |
55 | /** |
56 | * Store an entire {@link question_usage_by_activity} in the database, | |
57 | * including all the question_attempts that comprise it. | |
58 | * @param question_usage_by_activity $quba the usage to store. | |
59 | */ | |
60 | public function insert_questions_usage_by_activity(question_usage_by_activity $quba) { | |
61 | $record = new stdClass; | |
62 | $record->contextid = $quba->get_owning_context()->id; | |
63 | $record->component = addslashes($quba->get_owning_component()); | |
64 | $record->preferredbehaviour = addslashes($quba->get_preferred_behaviour()); | |
65 | ||
06f8ed54 | 66 | $newid = $this->db->insert_record('question_usages', $record); |
d1b7e03d TH |
67 | $quba->set_id_from_database($newid); |
68 | ||
69 | foreach ($quba->get_attempt_iterator() as $qa) { | |
70 | $this->insert_question_attempt($qa); | |
71 | } | |
72 | } | |
73 | ||
74 | /** | |
75 | * Store an entire {@link question_attempt} in the database, | |
76 | * including all the question_attempt_steps that comprise it. | |
77 | * @param question_attempt $qa the question attempt to store. | |
78 | */ | |
79 | public function insert_question_attempt(question_attempt $qa) { | |
80 | $record = new stdClass; | |
81 | $record->questionusageid = $qa->get_usage_id(); | |
82 | $record->slot = $qa->get_slot(); | |
83 | $record->behaviour = addslashes($qa->get_behaviour_name()); | |
84 | $record->questionid = $qa->get_question()->id; | |
85 | $record->maxmark = $qa->get_max_mark(); | |
86 | $record->minfraction = $qa->get_min_fraction(); | |
87 | $record->flagged = $qa->is_flagged(); | |
88 | $record->questionsummary = addslashes($qa->get_question_summary()); | |
89 | $record->rightanswer = addslashes($qa->get_right_answer_summary()); | |
90 | $record->responsesummary = addslashes($qa->get_response_summary()); | |
91 | $record->timemodified = time(); | |
06f8ed54 | 92 | $record->id = $this->db->insert_record('question_attempts', $record); |
d1b7e03d TH |
93 | |
94 | foreach ($qa->get_step_iterator() as $seq => $step) { | |
95 | $this->insert_question_attempt_step($step, $record->id, $seq); | |
96 | } | |
97 | } | |
98 | ||
99 | /** | |
100 | * Store a {@link question_attempt_step} in the database. | |
101 | * @param question_attempt_step $qa the step to store. | |
102 | */ | |
103 | public function insert_question_attempt_step(question_attempt_step $step, | |
104 | $questionattemptid, $seq) { | |
105 | $record = new stdClass; | |
106 | $record->questionattemptid = $questionattemptid; | |
107 | $record->sequencenumber = $seq; | |
108 | $record->state = addslashes('' . $step->get_state()); | |
109 | $record->fraction = $step->get_fraction(); | |
110 | $record->timecreated = $step->get_timecreated(); | |
111 | $record->userid = $step->get_user_id(); | |
112 | ||
06f8ed54 | 113 | $record->id = $this->db->insert_record('question_attempt_steps', $record); |
d1b7e03d TH |
114 | |
115 | foreach ($step->get_all_data() as $name => $value) { | |
116 | $data = new stdClass; | |
117 | $data->attemptstepid = $record->id; | |
118 | $data->name = addslashes($name); | |
119 | $data->value = addslashes($value); | |
06f8ed54 | 120 | $this->db->insert_record('question_attempt_step_data', $data, false); |
d1b7e03d TH |
121 | } |
122 | } | |
123 | ||
124 | /** | |
125 | * Load a {@link question_attempt_step} from the database. | |
126 | * @param integer $stepid the id of the step to load. | |
127 | * @param question_attempt_step the step that was loaded. | |
128 | */ | |
129 | public function load_question_attempt_step($stepid) { | |
06f8ed54 | 130 | $records = $this->db->get_records_sql(" |
d1b7e03d TH |
131 | SELECT |
132 | COALESCE(qasd.id, -1 * qas.id) AS id, | |
133 | qas.id AS attemptstepid, | |
134 | qas.questionattemptid, | |
135 | qas.sequencenumber, | |
136 | qas.state, | |
137 | qas.fraction, | |
138 | qas.timecreated, | |
139 | qas.userid, | |
140 | qasd.name, | |
141 | qasd.value | |
142 | ||
06f8ed54 TH |
143 | FROM {question_attempt_steps} qas |
144 | LEFT JOIN {question_attempt_step_data} qasd ON qasd.attemptstepid = qas.id | |
d1b7e03d TH |
145 | |
146 | WHERE | |
06f8ed54 TH |
147 | qas.id = :stepid |
148 | ", array('stepid' => $stepid)); | |
d1b7e03d TH |
149 | |
150 | if (!$records) { | |
151 | throw new Exception('Failed to load question_attempt_step ' . $stepid); | |
152 | } | |
153 | ||
154 | return question_attempt_step::load_from_records($records, $stepid); | |
155 | } | |
156 | ||
157 | /** | |
158 | * Load a {@link question_attempt} from the database, including all its | |
159 | * steps. | |
160 | * @param integer $questionattemptid the id of the question attempt to load. | |
161 | * @param question_attempt the question attempt that was loaded. | |
162 | */ | |
163 | public function load_question_attempt($questionattemptid) { | |
06f8ed54 | 164 | $records = $this->db->get_records_sql(" |
d1b7e03d TH |
165 | SELECT |
166 | COALESCE(qasd.id, -1 * qas.id) AS id, | |
7a719748 | 167 | quba.contextid, |
d1b7e03d TH |
168 | quba.preferredbehaviour, |
169 | qa.id AS questionattemptid, | |
170 | qa.questionusageid, | |
171 | qa.slot, | |
172 | qa.behaviour, | |
173 | qa.questionid, | |
174 | qa.maxmark, | |
175 | qa.minfraction, | |
176 | qa.flagged, | |
177 | qa.questionsummary, | |
178 | qa.rightanswer, | |
179 | qa.responsesummary, | |
180 | qa.timemodified, | |
181 | qas.id AS attemptstepid, | |
182 | qas.sequencenumber, | |
183 | qas.state, | |
184 | qas.fraction, | |
185 | qas.timecreated, | |
186 | qas.userid, | |
187 | qasd.name, | |
188 | qasd.value | |
189 | ||
06f8ed54 TH |
190 | FROM {question_attempts qa |
191 | JOIN {question_usages} quba ON quba.id = qa.questionusageid | |
192 | LEFT JOIN {question_attempt_steps} qas ON qas.questionattemptid = qa.id | |
193 | LEFT JOIN {question_attempt_step_data} qasd ON qasd.attemptstepid = qas.id | |
d1b7e03d TH |
194 | |
195 | WHERE | |
06f8ed54 | 196 | qa.id = :questionattemptid |
d1b7e03d TH |
197 | |
198 | ORDER 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 |
219 | SELECT |
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 |
246 | FROM {question_usages} quba |
247 | LEFT JOIN {question_attempts} qa ON qa.questionusageid = quba.id | |
248 | LEFT JOIN {question_attempt_steps} qas ON qas.questionattemptid = qa.id | |
249 | LEFT JOIN {question_attempt_step_data} qasd ON qasd.attemptstepid = qas.id | |
d1b7e03d TH |
250 | |
251 | WHERE | |
06f8ed54 | 252 | quba.id = :qubaid |
d1b7e03d TH |
253 | |
254 | ORDER BY | |
255 | qa.slot, | |
256 | qas.sequencenumber | |
c76145d3 | 257 | ", array('qubaid' => $qubaid)); |
d1b7e03d TH |
258 | |
259 | if (!$records) { | |
260 | throw new Exception('Failed to load questions_usage_by_activity ' . $qubaid); | |
261 | } | |
262 | ||
263 | return question_usage_by_activity::load_from_records($records, $qubaid); | |
264 | } | |
265 | ||
266 | /** | |
267 | * Load information about the latest state of each question from the database. | |
268 | * | |
269 | * @param qubaid_condition $qubaids used to restrict which usages are included | |
270 | * in the query. See {@link qubaid_condition}. | |
271 | * @param array $slots A list of slots for the questions you want to konw about. | |
272 | * @return array of records. See the SQL in this function to see the fields available. | |
273 | */ | |
274 | public function load_questions_usages_latest_steps(qubaid_condition $qubaids, $slots) { | |
c76145d3 | 275 | list($slottest, $params) = $this->db->get_in_or_equal($slots, SQL_PARAMS_NAMED, 'slot0000'); |
d1b7e03d TH |
276 | |
277 | $records = get_records_sql(" | |
278 | SELECT | |
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 | ||
299 | FROM {$qubaids->from_question_attempts('qa')} | |
06f8ed54 | 300 | JOIN {question_attempt_steps} qas ON |
d1b7e03d TH |
301 | qas.id = {$this->latest_step_for_qa_subquery()} |
302 | ||
303 | WHERE | |
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 |
327 | SELECT |
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 | ||
336 | FROM {$qubaids->from_question_attempts('qa')} | |
06f8ed54 | 337 | JOIN {question_attempt_steps} qas ON |
d1b7e03d | 338 | qas.id = {$this->latest_step_for_qa_subquery()} |
06f8ed54 | 339 | JOIN {question} q ON q.id = qa.questionid |
d1b7e03d TH |
340 | |
341 | WHERE | |
342 | {$qubaids->where()} AND | |
343 | qa.slot $slottest | |
344 | ||
345 | GROUP BY | |
346 | qa.slot, | |
347 | qa.questionid, | |
348 | q.name, | |
349 | q.id, | |
350 | summarystate | |
351 | ||
f9b0500f | 352 | ORDER BY |
d1b7e03d TH |
353 | qa.slot, |
354 | qa.questionid, | |
355 | q.name, | |
356 | q.id | |
06f8ed54 | 357 | ", $params + $qubaids->from_where_params()); |
d1b7e03d TH |
358 | |
359 | if (!$rs) { | |
360 | throw new moodle_exception('errorloadingdata'); | |
361 | } | |
362 | ||
363 | $results = array(); | |
364 | while ($row = rs_fetch_next_record($rs)) { | |
365 | $index = $row->slot . ',' . $row->questionid; | |
366 | ||
367 | if (!array_key_exists($index, $results)) { | |
368 | $res = new stdClass; | |
369 | $res->slot = $row->slot; | |
370 | $res->questionid = $row->questionid; | |
371 | $res->name = $row->name; | |
372 | $res->inprogress = 0; | |
373 | $res->needsgrading = 0; | |
374 | $res->autograded = 0; | |
375 | $res->manuallygraded = 0; | |
376 | $res->all = 0; | |
377 | $results[$index] = $res; | |
378 | } | |
379 | ||
380 | $results[$index]->{$row->summarystate} = $row->numattempts; | |
381 | $results[$index]->all += $row->numattempts; | |
382 | } | |
383 | rs_close($rs); | |
384 | ||
385 | return $results; | |
386 | } | |
387 | ||
388 | /** | |
389 | * Get a list of usage ids where the question with slot $slot, and optionally | |
390 | * also with question id $questionid, is in summary state $summarystate. Also | |
391 | * return the total count of such states. | |
392 | * | |
393 | * Only a subset of the ids can be returned by using $orderby, $limitfrom and | |
394 | * $limitnum. A special value 'random' can be passed as $orderby, in which case | |
395 | * $limitfrom is ignored. | |
396 | * | |
397 | * @param qubaid_condition $qubaids used to restrict which usages are included | |
398 | * in the query. See {@link qubaid_condition}. | |
399 | * @param integer $slot The slot for the questions you want to konw about. | |
400 | * @param integer $questionid (optional) Only return attempts that were of this specific question. | |
401 | * @param string $summarystate the summary state of interest, or 'all'. | |
402 | * @param string $orderby the column to order by. | |
403 | * @param integer $limitfrom implements paging of the results. | |
404 | * Ignored if $orderby = random or $limitnum is null. | |
405 | * @param integer $limitnum implements paging of the results. null = all. | |
406 | * @return array with two elements, an array of usage ids, and a count of the total number. | |
407 | */ | |
408 | public function load_questions_usages_where_question_in_state( | |
409 | qubaid_condition $qubaids, $summarystate, $slot, $questionid = null, | |
410 | $orderby = 'random', $limitfrom = 0, $limitnum = null) { | |
411 | global $CFG; | |
412 | ||
413 | $extrawhere = ''; | |
414 | if ($questionid) { | |
415 | $extrawhere .= ' AND qa.questionid = ' . $questionid; | |
416 | } | |
417 | if ($summarystate != 'all') { | |
418 | $test = $this->in_summary_state_test($summarystate); | |
419 | $extrawhere .= ' AND qas.state ' . $test; | |
420 | } | |
421 | ||
422 | if ($orderby == 'random') { | |
423 | $sqlorderby = ''; | |
424 | } else if ($orderby) { | |
425 | $sqlorderby = 'ORDER BY ' . $orderby; | |
426 | } else { | |
427 | $sqlorderby = ''; | |
428 | } | |
429 | ||
430 | // We always want the total count, as well as the partcular list of ids, | |
431 | // based on the paging and sort order. Becuase the list of ids is never | |
432 | // going to be too rediculously long. My worst-case scenario is | |
433 | // 10,000 students in the coures, each doing 5 quiz attempts. That | |
434 | // is a 50,000 element int => int array, which PHP seems to use 5MB | |
435 | // memeory to store on a 64 bit server. | |
436 | $qubaids = get_records_sql_menu(" | |
437 | SELECT | |
438 | qa.questionusageid, | |
439 | 1 | |
440 | ||
441 | FROM {$qubaids->from_question_attempts('qa')} | |
442 | JOIN {$CFG->prefix}question_attempt_steps qas ON | |
443 | qas.id = {$this->latest_step_for_qa_subquery()} | |
444 | JOIN {$CFG->prefix}question q ON q.id = qa.questionid | |
445 | ||
446 | WHERE | |
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(" | |
498 | SELECT | |
499 | qa.slot, | |
500 | AVG(COALESCE(qas.fraction, 0)) AS averagefraction, | |
501 | COUNT(1) AS numaveraged | |
502 | ||
503 | FROM {$qubaids->from_question_attempts('qa')} | |
504 | JOIN {$CFG->prefix}question_attempt_steps qas ON | |
505 | qas.id = {$this->latest_step_for_qa_subquery()} | |
506 | ||
507 | WHERE | |
508 | {$qubaids->where()} | |
509 | $slotwhere | |
510 | AND qas.state $statetest | |
511 | ||
512 | GROUP BY qa.slot | |
513 | ||
514 | ORDER 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(" | |
531 | SELECT | |
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 | ||
555 | FROM {$qubaids->from_question_attempts('qa')} | |
556 | JOIN {$CFG->prefix}question_usages quba ON quba.id = qa.questionusageid | |
557 | LEFT JOIN {$CFG->prefix}question_attempt_steps qas ON qas.questionattemptid = qa.id | |
558 | LEFT JOIN {$CFG->prefix}question_attempt_step_data qasd ON qasd.attemptstepid = qas.id | |
559 | ||
560 | WHERE | |
561 | {$qubaids->where()} AND | |
562 | qa.questionid = $questionid | |
563 | ||
564 | ORDER BY | |
565 | quba.id, | |
566 | qa.id, | |
567 | qas.sequencenumber | |
568 | "); | |
569 | ||
570 | if (!$records) { | |
571 | return array(); | |
572 | } | |
573 | ||
574 | $questionattempts = array(); | |
575 | $record = current($records); | |
576 | while ($record) { | |
577 | $questionattempts[$record->questionattemptid] = | |
578 | question_attempt::load_from_records($records, | |
579 | $record->questionattemptid, new question_usage_null_observer(), | |
580 | $record->preferredbehaviour); | |
581 | $record = current($records); | |
582 | } | |
583 | return $questionattempts; | |
584 | } | |
585 | ||
586 | /** | |
587 | * Update a question_usages row to refect any changes in a usage (but not | |
588 | * any of its question_attempts. | |
589 | * @param question_usage_by_activity $quba the usage that has changed. | |
590 | */ | |
591 | public function update_questions_usage_by_activity(question_usage_by_activity $quba) { | |
592 | $record = new stdClass; | |
593 | $record->id = $quba->get_id(); | |
594 | $record->contextid = $quba->get_owning_context()->id; | |
595 | $record->component = addslashes($quba->get_owning_component()); | |
596 | $record->preferredbehaviour = addslashes($quba->get_preferred_behaviour()); | |
597 | ||
c76145d3 | 598 | if (!$this->db->update_record('question_usages', $record)) { |
d1b7e03d TH |
599 | throw new Exception('Failed to update question_usage_by_activity ' . $record->id); |
600 | } | |
601 | } | |
602 | ||
603 | /** | |
604 | * Update a question_attempts row to refect any changes in a question_attempt | |
605 | * (but not any of its steps). | |
606 | * @param question_attempt $qa the question attempt that has changed. | |
607 | */ | |
608 | public function update_question_attempt(question_attempt $qa) { | |
c76145d3 TH |
609 | global $DB; |
610 | ||
d1b7e03d TH |
611 | $record = new stdClass; |
612 | $record->id = $qa->get_database_id(); | |
613 | $record->maxmark = $qa->get_max_mark(); | |
614 | $record->minfraction = $qa->get_min_fraction(); | |
615 | $record->flagged = $qa->is_flagged(); | |
616 | $record->questionsummary = addslashes($qa->get_question_summary()); | |
617 | $record->rightanswer = addslashes($qa->get_right_answer_summary()); | |
618 | $record->responsesummary = addslashes($qa->get_response_summary()); | |
619 | $record->timemodified = time(); | |
620 | ||
c76145d3 | 621 | if (!$this->db->update_record('question_attempts', $record)) { |
d1b7e03d TH |
622 | throw new Exception('Failed to update question_attempt ' . $record->id); |
623 | } | |
624 | } | |
625 | ||
626 | /** | |
627 | * Delete a question_usage_by_activity and all its associated | |
628 | * {@link question_attempts} and {@link question_attempt_steps} from the | |
629 | * database. | |
630 | * @param string $where a where clause. Becuase of MySQL limitations, you | |
631 | * must refer to {$CFG->prefix}question_usages.id in full like that. | |
c76145d3 | 632 | * @param array $params values to substitute for placeholders in $where. |
d1b7e03d | 633 | */ |
c76145d3 | 634 | public function delete_questions_usage_by_activities($where, $params) { |
d1b7e03d | 635 | global $CFG; |
c76145d3 | 636 | $this->db->delete_records_select('question_attempt_step_data', "attemptstepid IN ( |
d1b7e03d | 637 | SELECT qas.id |
c76145d3 TH |
638 | FROM {question_attempts} qa |
639 | JOIN {question_attempt_steps} qas ON qas.questionattemptid = qa.id | |
640 | JOIN {question_usages} ON qa.questionusageid = {question_usages}.id | |
641 | WHERE $where)", $params); | |
642 | $this->db->delete_records_select('question_attempt_steps', "questionattemptid IN ( | |
d1b7e03d | 643 | SELECT qa.id |
c76145d3 TH |
644 | FROM {question_attempts} qa |
645 | JOIN {question_usages} ON qa.questionusageid = {question_usages}.id | |
646 | WHERE $where)", $params); | |
647 | $this->db->delete_records_select('question_attempts', "questionusageid IN ( | |
d1b7e03d | 648 | SELECT id |
c76145d3 TH |
649 | FROM {question_usages} |
650 | WHERE $where)", $params); | |
651 | $this->db->delete_records_select('question_usages', $where, $params); | |
d1b7e03d TH |
652 | } |
653 | ||
654 | /** | |
655 | * Delete all the steps for a question attempt. | |
656 | * @param integer $qaids question_attempt id. | |
657 | */ | |
658 | public function delete_steps_for_question_attempts($qaids) { | |
659 | global $CFG; | |
660 | if (empty($qaids)) { | |
661 | return; | |
662 | } | |
663 | list($test, $params) = get_in_or_equal($qaids); | |
c76145d3 | 664 | $this->db->delete_records_select('question_attempt_step_data', "attemptstepid IN ( |
d1b7e03d | 665 | SELECT qas.id |
c76145d3 TH |
666 | FROM {question_attempt_steps} qas |
667 | WHERE questionattemptid $test)", $params); | |
668 | $this->db->delete_records_select('question_attempt_steps', 'questionattemptid ' . $test, $params); | |
d1b7e03d TH |
669 | } |
670 | ||
671 | /** | |
672 | * Delete all the previews for a given question. | |
673 | * @param integer $questionid question id. | |
674 | */ | |
675 | public function delete_previews($questionid) { | |
676 | global $CFG; | |
c76145d3 | 677 | $previews = $this->db->get_records_sql_menu(" |
d1b7e03d | 678 | SELECT DISTINCT quba.id, 1 |
c76145d3 TH |
679 | FROM {question_usages} quba |
680 | JOIN {question_attempts} qa ON qa.questionusageid = quba.id | |
d1b7e03d | 681 | WHERE quba.component = 'core_question_preview' AND |
c76145d3 | 682 | qa.questionid = ?", array($questionid)); |
d1b7e03d TH |
683 | if (empty($previews)) { |
684 | return; | |
685 | } | |
c76145d3 TH |
686 | list($test, $params) = $this->db->get_in_or_equal(array_keys($previews)); |
687 | $this->delete_questions_usage_by_activities('question_usages.id ' . $test, $params); | |
d1b7e03d TH |
688 | } |
689 | ||
690 | /** | |
691 | * Update the flagged state of a question in the database. | |
692 | * @param integer $qubaid the question usage id. | |
693 | * @param integer $questionid the question id. | |
694 | * @param integer $sessionid the question_attempt id. | |
695 | * @param boolean $newstate the new state of the flag. true = flagged. | |
696 | */ | |
06f8ed54 | 697 | public function update_question_attempt_flag($qubaid, $questionid, $qaid, $slot, $newstate) { |
f9b0500f | 698 | if (!$this->db->record_exists('question_attempts', array('id' => $qaid, |
06f8ed54 | 699 | 'questionusageid' => $qubaid, 'questionid' => $questionid, 'slot' => $slot))) { |
d1b7e03d TH |
700 | throw new Exception('invalid ids'); |
701 | } | |
702 | ||
06f8ed54 | 703 | $this->db->set_field('question_attempts', 'flagged', $newstate, array('id' => $qaid)); |
d1b7e03d TH |
704 | } |
705 | ||
706 | /** | |
707 | * Get all the WHEN 'x' THEN 'y' terms needed to convert the question_attempt_steps.state | |
708 | * column to a summary state. Use this like | |
709 | * CASE qas.state {$this->full_states_to_summary_state_sql()} END AS summarystate, | |
710 | * @param string SQL fragment. | |
711 | */ | |
712 | protected function full_states_to_summary_state_sql() { | |
713 | $sql = ''; | |
714 | foreach (question_state::get_all() as $state) { | |
715 | $sql .= "WHEN '$state' THEN '{$state->get_summary_state()}'\n"; | |
716 | } | |
717 | return $sql; | |
718 | } | |
719 | ||
720 | /** | |
721 | * Get the SQL needed to test that question_attempt_steps.state is in a | |
722 | * state corresponding to $summarystate. | |
723 | * @param string $summarystate one of | |
724 | * inprogress, needsgrading, manuallygraded or autograded | |
725 | * @param boolean $equal if false, do a NOT IN test. Default true. | |
726 | * @return string SQL fragment. | |
727 | */ | |
728 | public function in_summary_state_test($summarystate, $equal = true) { | |
729 | $states = question_state::get_all_for_summary_state($summarystate); | |
c76145d3 TH |
730 | list($sql, $params) = $this->db->get_in_or_equal($states, SQL_PARAMS_QM, 'param0000', $equal); |
731 | // TODO return $params; | |
d1b7e03d TH |
732 | return $sql; |
733 | } | |
734 | ||
735 | /** | |
736 | * Change the maxmark for the question_attempt with number in usage $slot | |
737 | * for all the specified question_attempts. | |
738 | * @param qubaid_condition $qubaids Selects which usages are updated. | |
739 | * @param integer $slot the number is usage to affect. | |
740 | * @param number $newmaxmark the new max mark to set. | |
741 | */ | |
742 | public function set_max_mark_in_attempts(qubaid_condition $qubaids, $slot, $newmaxmark) { | |
c76145d3 TH |
743 | $this->db->set_field_select('question_attempts', 'maxmark', $newmaxmark, |
744 | "questionusageid {$qubaids->usage_id_in()} AND slot = :slot", | |
745 | $qubaids->usage_id_in_params() + array('slot' => $slot)); | |
d1b7e03d TH |
746 | } |
747 | ||
748 | /** | |
749 | * Return a subquery that computes the sum of the marks for all the questions | |
750 | * in a usage. Which useage to compute the sum for is controlled bu the $qubaid | |
751 | * parameter. | |
752 | * | |
753 | * See {@link quiz_update_all_attempt_sumgrades()} for an example of the usage of | |
754 | * this method. | |
755 | * | |
756 | * @param string $qubaid SQL fragment that controls which usage is summed. | |
757 | * This might be the name of a column in the outer query. | |
758 | * @return string SQL code for the subquery. | |
759 | */ | |
760 | public function sum_usage_marks_subquery($qubaid) { | |
d1b7e03d | 761 | return "SELECT SUM(qa.maxmark * qas.fraction) |
c76145d3 | 762 | FROM {question_attempts} qa |
d1b7e03d TH |
763 | JOIN ( |
764 | SELECT summarks_qa.id AS questionattemptid, MAX(summarks_qas.id) AS latestid | |
c76145d3 TH |
765 | FROM {question_attempt_steps} summarks_qas |
766 | JOIN {question_attempts} summarks_qa ON summarks_qa.id = summarks_qas.questionattemptid | |
d1b7e03d TH |
767 | WHERE summarks_qa.questionusageid = $qubaid |
768 | GROUP BY summarks_qa.id | |
769 | ) lateststepid ON lateststepid.questionattemptid = qa.id | |
c76145d3 | 770 | JOIN {question_attempt_steps} qas ON qas.id = lateststepid.latestid |
d1b7e03d | 771 | WHERE qa.questionusageid = $qubaid |
f86390dc | 772 | HAVING COUNT(CASE WHEN qas.state = 'needsgrading' AND qa.maxmark > 0 THEN 1 ELSE NULL END) = 0"; |
c76145d3 | 773 | // TODO handle $qubaid with placeholders. |
d1b7e03d TH |
774 | } |
775 | ||
776 | public function question_attempt_latest_state_view($alias) { | |
d1b7e03d TH |
777 | return "( |
778 | SELECT | |
779 | {$alias}qa.id AS questionattemptid, | |
780 | {$alias}qa.questionusageid, | |
781 | {$alias}qa.slot, | |
782 | {$alias}qa.behaviour, | |
783 | {$alias}qa.questionid, | |
784 | {$alias}qa.maxmark, | |
785 | {$alias}qa.minfraction, | |
786 | {$alias}qa.flagged, | |
787 | {$alias}qa.questionsummary, | |
788 | {$alias}qa.rightanswer, | |
789 | {$alias}qa.responsesummary, | |
790 | {$alias}qa.timemodified, | |
791 | {$alias}qas.id AS attemptstepid, | |
792 | {$alias}qas.sequencenumber, | |
793 | {$alias}qas.state, | |
794 | {$alias}qas.fraction, | |
795 | {$alias}qas.timecreated, | |
796 | {$alias}qas.userid | |
797 | ||
798 | FROM {$CFG->prefix}question_attempts {$alias}qa | |
799 | JOIN {$CFG->prefix}question_attempt_steps {$alias}qas ON | |
800 | {$alias}qas.id = {$this->latest_step_for_qa_subquery($alias . 'qa.id')} | |
801 | ) $alias"; | |
802 | } | |
803 | ||
804 | protected function latest_step_for_qa_subquery($questionattemptid = 'qa.id') { | |
805 | global $CFG; | |
806 | return "( | |
807 | SELECT MAX(id) | |
c76145d3 | 808 | FROM {question_attempt_steps} |
d1b7e03d TH |
809 | WHERE questionattemptid = $questionattemptid |
810 | )"; | |
811 | } | |
812 | ||
813 | /** | |
814 | * @param array $questionids of question ids. | |
815 | * @return boolean whether any of these questions are being used by the question engine. | |
816 | */ | |
32d8935c | 817 | public function questions_in_use(array $questionids) { |
c76145d3 TH |
818 | list($test, $params) = $this->db->get_in_or_equal($questionids); |
819 | return $this->db->record_exists_select('question_attempts', | |
820 | 'questionid ' . $test, $params); | |
d1b7e03d TH |
821 | } |
822 | } | |
823 | ||
824 | /** | |
825 | * Implementation of the unit of work pattern for the question engine. | |
826 | * | |
827 | * See http://martinfowler.com/eaaCatalog/unitOfWork.html. This tracks all the | |
828 | * changes to a {@link question_usage_by_activity}, and its constituent parts, | |
829 | * so that the changes can be saved to the database when {@link save()} is called. | |
830 | * | |
831 | * @copyright 2009 The Open University | |
832 | * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later | |
833 | */ | |
834 | class 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 | */ | |
948 | abstract 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 | */ | |
986 | class 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 | */ | |
1056 | class 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 | } |