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; | |
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 |
132 | SELECT |
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 |
144 | FROM {question_attempt_steps} qas |
145 | LEFT JOIN {question_attempt_step_data} qasd ON qasd.attemptstepid = qas.id | |
d1b7e03d TH |
146 | |
147 | WHERE | |
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 |
166 | SELECT |
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 |
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 | |
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(" | |
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 | ||
352 | ORDER 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(" | |
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 | ||
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 | */ | |
830 | class 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 | */ | |
944 | abstract 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 | */ | |
982 | class 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 | */ | |
1052 | class 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 | } |