Search moodle.org's
Developer Documentation

See Release Notes

  • Bug fixes for general core bugs in 3.11.x will end 14 Nov 2022 (12 months plus 6 months extension).
  • Bug fixes for security issues in 3.11.x will end 13 Nov 2023 (18 months plus 12 months extension).
  • PHP version: minimum PHP 7.3.0 Note: minimum PHP version has increased since Moodle 3.10. PHP 7.4.x is supported too.

Differences Between: [Versions 311 and 400] [Versions 311 and 401] [Versions 311 and 402] [Versions 311 and 403]

   1  <?php
   2  // This file is part of Moodle - http://moodle.org/
   3  //
   4  // Moodle is free software: you can redistribute it and/or modify
   5  // it under the terms of the GNU General Public License as published by
   6  // the Free Software Foundation, either version 3 of the License, or
   7  // (at your option) any later version.
   8  //
   9  // Moodle is distributed in the hope that it will be useful,
  10  // but WITHOUT ANY WARRANTY; without even the implied warranty of
  11  // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  12  // GNU General Public License for more details.
  13  //
  14  // You should have received a copy of the GNU General Public License
  15  // along with Moodle.  If not, see <http://www.gnu.org/licenses/>.
  16  
  17  /**
  18   * Code for loading and saving question attempts to and from the database.
  19   *
  20   * Note that many of the methods of this class should be considered private to
  21   * the question engine. They should be accessed through the
  22   * {@link question_engine} class. For example, you should call
  23   * {@link question_engine::save_questions_usage_by_activity()} rather than
  24   * {@link question_engine_data_mapper::insert_questions_usage_by_activity()}.
  25   * The exception to this is some of the reporting methods, like
  26   * {@link question_engine_data_mapper::load_attempts_at_question()}.
  27   *
  28   * A note for future reference. This code is pretty efficient but there are some
  29   * potential optimisations that could be contemplated, at the cost of making the
  30   * code more complex:
  31   *
  32   * 1. (This is probably not worth doing.) In the unit-of-work save method, we
  33   *    could get all the ids for steps due to be deleted or modified,
  34   *    and delete all the question_attempt_step_data for all of those steps in one
  35   *    query. That would save one DB query for each ->stepsupdated. However that number
  36   *    is 0 except when re-grading, and when regrading, there are many more inserts
  37   *    into question_attempt_step_data than deletes, so it is really hardly worth it.
  38   *
  39   * @package    core_question
  40   * @copyright  2009 The Open University
  41   * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
  42   */
  43  
  44  
  45  defined('MOODLE_INTERNAL') || die();
  46  
  47  
  48  /**
  49   * This class controls the loading and saving of question engine data to and from
  50   * the database.
  51   *
  52   * @copyright  2009 The Open University
  53   * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
  54   */
  55  class question_engine_data_mapper {
  56      /**
  57       * @var moodle_database normally points to global $DB, but I prefer not to
  58       * use globals if I can help it.
  59       */
  60      protected $db;
  61  
  62      /**
  63       * @param moodle_database $db a database connectoin. Defaults to global $DB.
  64       */
  65      public function __construct(moodle_database $db = null) {
  66          if (is_null($db)) {
  67              global $DB;
  68              $this->db = $DB;
  69          } else {
  70              $this->db = $db;
  71          }
  72      }
  73  
  74      /**
  75       * Store an entire {@link question_usage_by_activity} in the database,
  76       * including all the question_attempts that comprise it.
  77       *
  78       * You should not call this method directly. You should use
  79       * @link question_engine::save_questions_usage_by_activity()}.
  80       *
  81       * @param question_usage_by_activity $quba the usage to store.
  82       */
  83      public function insert_questions_usage_by_activity(question_usage_by_activity $quba) {
  84          $record = new stdClass();
  85          $record->contextid = $quba->get_owning_context()->id;
  86          $record->component = $quba->get_owning_component();
  87          $record->preferredbehaviour = $quba->get_preferred_behaviour();
  88  
  89          $newid = $this->db->insert_record('question_usages', $record);
  90          $quba->set_id_from_database($newid);
  91  
  92          // Initially an array of array of question_attempt_step_objects.
  93          // Built as a nested array for efficiency, then flattened.
  94          $stepdata = array();
  95  
  96          foreach ($quba->get_attempt_iterator() as $qa) {
  97              $stepdata[] = $this->insert_question_attempt($qa, $quba->get_owning_context());
  98          }
  99  
 100          $this->insert_all_step_data($this->combine_step_data($stepdata));
 101  
 102          $quba->set_observer(new question_engine_unit_of_work($quba));
 103      }
 104  
 105      /**
 106       * Store an entire {@link question_attempt} in the database,
 107       * including all the question_attempt_steps that comprise it.
 108       *
 109       * You should not call this method directly. You should use
 110       * @link question_engine::save_questions_usage_by_activity()}.
 111       *
 112       * @param question_attempt $qa the question attempt to store.
 113       * @param context $context the context of the owning question_usage_by_activity.
 114       * @return array of question_attempt_step_data rows, that still need to be inserted.
 115       */
 116      public function insert_question_attempt(question_attempt $qa, $context) {
 117          $record = new stdClass();
 118          $record->questionusageid = $qa->get_usage_id();
 119          $record->slot = $qa->get_slot();
 120          $record->behaviour = $qa->get_behaviour_name();
 121          $record->questionid = $qa->get_question_id();
 122          $record->variant = $qa->get_variant();
 123          $record->maxmark = $qa->get_max_mark();
 124          $record->minfraction = $qa->get_min_fraction();
 125          $record->maxfraction = $qa->get_max_fraction();
 126          $record->flagged = $qa->is_flagged();
 127          $record->questionsummary = $qa->get_question_summary();
 128          if (core_text::strlen($record->questionsummary) > question_bank::MAX_SUMMARY_LENGTH) {
 129              // It seems some people write very long quesions! MDL-30760
 130              $record->questionsummary = core_text::substr($record->questionsummary,
 131                      0, question_bank::MAX_SUMMARY_LENGTH - 3) . '...';
 132          }
 133          $record->rightanswer = $qa->get_right_answer_summary();
 134          $record->responsesummary = $qa->get_response_summary();
 135          $record->timemodified = time();
 136          $record->id = $this->db->insert_record('question_attempts', $record);
 137          $qa->set_database_id($record->id);
 138  
 139          // Initially an array of array of question_attempt_step_objects.
 140          // Built as a nested array for efficiency, then flattened.
 141          $stepdata = array();
 142  
 143          foreach ($qa->get_step_iterator() as $seq => $step) {
 144              $stepdata[] = $this->insert_question_attempt_step($step, $record->id, $seq, $context);
 145          }
 146  
 147          return $this->combine_step_data($stepdata);
 148      }
 149  
 150      /**
 151       * Helper method used by insert_question_attempt_step and update_question_attempt_step
 152       * @param question_attempt_step $step the step to store.
 153       * @param int $questionattemptid the question attept id this step belongs to.
 154       * @param int $seq the sequence number of this stop.
 155       * @return stdClass data to insert into the database.
 156       */
 157      protected function make_step_record(question_attempt_step $step, $questionattemptid, $seq) {
 158          $record = new stdClass();
 159          $record->questionattemptid = $questionattemptid;
 160          $record->sequencenumber = $seq;
 161          $record->state = (string) $step->get_state();
 162          $record->fraction = $step->get_fraction();
 163          $record->timecreated = $step->get_timecreated();
 164          $record->userid = $step->get_user_id();
 165          return $record;
 166      }
 167  
 168      /**
 169       * Take an array of arrays, and flatten it, even if the outer array is empty.
 170       *
 171       * Only public so it can be called from the unit of work. Not part of the
 172       * public API of this class.
 173       *
 174       * @param array $stepdata array of zero or more arrays.
 175       * @return array made by concatenating all the separate arrays.
 176       */
 177      public function combine_step_data(array $stepdata): array {
 178          if (empty($stepdata)) {
 179              return [];
 180          }
 181          return call_user_func_array('array_merge', $stepdata);
 182      }
 183  
 184      /**
 185       * Helper method used by insert_question_attempt_step and update_question_attempt_step
 186       * @param question_attempt_step $step the step to store.
 187       * @param int $stepid the id of the step.
 188       * @param context $context the context of the owning question_usage_by_activity.
 189       * @return array of question_attempt_step_data rows, that still need to be inserted.
 190       */
 191      protected function prepare_step_data(question_attempt_step $step, $stepid, $context) {
 192          $rows = array();
 193          foreach ($step->get_all_data() as $name => $value) {
 194              if ($value instanceof question_file_saver) {
 195                  $value->save_files($stepid, $context);
 196              }
 197              if ($value instanceof question_response_files) {
 198                  $value = (string) $value;
 199              }
 200  
 201              $data = new stdClass();
 202              $data->attemptstepid = $stepid;
 203              $data->name = $name;
 204              $data->value = $value;
 205              $rows[] = $data;
 206          }
 207          return $rows;
 208      }
 209  
 210      /**
 211       * Insert a lot of records into question_attempt_step_data in one go.
 212       *
 213       * Private method, only for use by other parts of the question engine.
 214       *
 215       * @param array $rows the rows to insert.
 216       */
 217      public function insert_all_step_data(array $rows) {
 218          if (!$rows) {
 219              return;
 220          }
 221          $this->db->insert_records('question_attempt_step_data', $rows);
 222      }
 223  
 224      /**
 225       * Store a {@link question_attempt_step} in the database.
 226       *
 227       * Private method, only for use by other parts of the question engine.
 228       *
 229       * @param question_attempt_step $step the step to store.
 230       * @param int $questionattemptid the question attept id this step belongs to.
 231       * @param int $seq the sequence number of this stop.
 232       * @param context $context the context of the owning question_usage_by_activity.
 233       * @return array of question_attempt_step_data rows, that still need to be inserted.
 234       */
 235      public function insert_question_attempt_step(question_attempt_step $step,
 236              $questionattemptid, $seq, $context) {
 237  
 238          $record = $this->make_step_record($step, $questionattemptid, $seq);
 239          $record->id = $this->db->insert_record('question_attempt_steps', $record);
 240  
 241          return $this->prepare_step_data($step, $record->id, $context);
 242      }
 243  
 244      /**
 245       * Update a {@link question_attempt_step} in the database.
 246       *
 247       * Private method, only for use by other parts of the question engine.
 248       *
 249       * @param question_attempt_step $step the step to store.
 250       * @param int $questionattemptid the question attept id this step belongs to.
 251       * @param int $seq the sequence number of this stop.
 252       * @param context $context the context of the owning question_usage_by_activity.
 253       * @return array of question_attempt_step_data rows, that still need to be inserted.
 254       */
 255      public function update_question_attempt_step(question_attempt_step $step,
 256              $questionattemptid, $seq, $context) {
 257  
 258          $record = $this->make_step_record($step, $questionattemptid, $seq);
 259          $record->id = $step->get_id();
 260          $this->db->update_record('question_attempt_steps', $record);
 261  
 262          $this->db->delete_records('question_attempt_step_data',
 263                  array('attemptstepid' => $record->id));
 264          return $this->prepare_step_data($step, $record->id, $context);
 265      }
 266  
 267      /**
 268       * Store new metadata for an existing {@link question_attempt} in the database.
 269       *
 270       * Private method, only for use by other parts of the question engine.
 271       *
 272       * @param question_attempt $qa the question attempt to store meta data for.
 273       * @param array $names the names of the metadata variables to store.
 274       * @return array of question_attempt_step_data rows, that still need to be inserted.
 275       */
 276      public function insert_question_attempt_metadata(question_attempt $qa, array $names) {
 277          $firststep = $qa->get_step(0);
 278  
 279          $rows = array();
 280          foreach ($names as $name) {
 281              $data = new stdClass();
 282              $data->attemptstepid = $firststep->get_id();
 283              $data->name = ':_' . $name;
 284              $data->value = $firststep->get_metadata_var($name);
 285              $rows[] = $data;
 286          }
 287  
 288          return $rows;
 289      }
 290  
 291      /**
 292       * Updates existing metadata for an existing {@link question_attempt} in the database.
 293       *
 294       * Private method, only for use by other parts of the question engine.
 295       *
 296       * @param question_attempt $qa the question attempt to store meta data for.
 297       * @param array $names the names of the metadata variables to store.
 298       * @return array of question_attempt_step_data rows, that still need to be inserted.
 299       */
 300      public function update_question_attempt_metadata(question_attempt $qa, array $names) {
 301          global $DB;
 302          if (!$names) {
 303              return [];
 304          }
 305          // Use case-sensitive function sql_equal() and not get_in_or_equal().
 306          // Some databases may use case-insensitive collation, we don't want to delete 'X' instead of 'x'.
 307          $sqls = [];
 308          $params = [$qa->get_step(0)->get_id()];
 309          foreach ($names as $name) {
 310              $sqls[] = $DB->sql_equal('name', '?');
 311              $params[] = $name;
 312          }
 313          $DB->delete_records_select('question_attempt_step_data',
 314              'attemptstepid = ? AND (' . join(' OR ', $sqls) . ')', $params);
 315          return $this->insert_question_attempt_metadata($qa, $names);
 316      }
 317  
 318      /**
 319       * Load a {@link question_attempt_step} from the database.
 320       *
 321       * Private method, only for use by other parts of the question engine.
 322       *
 323       * @param int $stepid the id of the step to load.
 324       * @return question_attempt_step the step that was loaded.
 325       */
 326      public function load_question_attempt_step($stepid) {
 327          $records = $this->db->get_recordset_sql("
 328  SELECT
 329      quba.contextid,
 330      COALESCE(q.qtype, 'missingtype') AS qtype,
 331      qas.id AS attemptstepid,
 332      qas.questionattemptid,
 333      qas.sequencenumber,
 334      qas.state,
 335      qas.fraction,
 336      qas.timecreated,
 337      qas.userid,
 338      qasd.name,
 339      qasd.value
 340  
 341  FROM      {question_attempt_steps}     qas
 342  JOIN      {question_attempts}          qa   ON qa.id              = qas.questionattemptid
 343  JOIN      {question_usages}            quba ON quba.id            = qa.questionusageid
 344  LEFT JOIN {question}                   q    ON q.id               = qa.questionid
 345  LEFT JOIN {question_attempt_step_data} qasd ON qasd.attemptstepid = qas.id
 346  
 347  WHERE
 348      qas.id = :stepid
 349          ", array('stepid' => $stepid));
 350  
 351          if (!$records->valid()) {
 352              throw new coding_exception('Failed to load question_attempt_step ' . $stepid);
 353          }
 354  
 355          $step = question_attempt_step::load_from_records($records, $stepid);
 356          $records->close();
 357  
 358          return $step;
 359      }
 360  
 361      /**
 362       * Load a {@link question_attempt} from the database, including all its
 363       * steps.
 364       *
 365       * Normally, you should use {@link question_engine::load_questions_usage_by_activity()}
 366       * but there may be rare occasions where for performance reasons, you only
 367       * wish to load one qa, in which case you may call this method.
 368       *
 369       * @param int $questionattemptid the id of the question attempt to load.
 370       * @return question_attempt the question attempt that was loaded.
 371       */
 372      public function load_question_attempt($questionattemptid) {
 373          $records = $this->db->get_recordset_sql("
 374  SELECT
 375      quba.contextid,
 376      quba.preferredbehaviour,
 377      qa.id AS questionattemptid,
 378      qa.questionusageid,
 379      qa.slot,
 380      qa.behaviour,
 381      qa.questionid,
 382      qa.variant,
 383      qa.maxmark,
 384      qa.minfraction,
 385      qa.maxfraction,
 386      qa.flagged,
 387      qa.questionsummary,
 388      qa.rightanswer,
 389      qa.responsesummary,
 390      qa.timemodified,
 391      qas.id AS attemptstepid,
 392      qas.sequencenumber,
 393      qas.state,
 394      qas.fraction,
 395      qas.timecreated,
 396      qas.userid,
 397      qasd.name,
 398      qasd.value
 399  
 400  FROM      {question_attempts}          qa
 401  JOIN      {question_usages}            quba ON quba.id               = qa.questionusageid
 402  LEFT JOIN {question_attempt_steps}     qas  ON qas.questionattemptid = qa.id
 403  LEFT JOIN {question_attempt_step_data} qasd ON qasd.attemptstepid    = qas.id
 404  
 405  WHERE
 406      qa.id = :questionattemptid
 407  
 408  ORDER BY
 409      qas.sequencenumber
 410          ", array('questionattemptid' => $questionattemptid));
 411  
 412          if (!$records->valid()) {
 413              throw new coding_exception('Failed to load question_attempt ' . $questionattemptid);
 414          }
 415  
 416          $record = $records->current();
 417          $qa = question_attempt::load_from_records($records, $questionattemptid,
 418                  new question_usage_null_observer(), $record->preferredbehaviour);
 419          $records->close();
 420  
 421          return $qa;
 422      }
 423  
 424      /**
 425       * Load a {@link question_usage_by_activity} from the database, including
 426       * all its {@link question_attempt}s and all their steps.
 427       *
 428       * You should call {@link question_engine::load_questions_usage_by_activity()}
 429       * rather than calling this method directly.
 430       *
 431       * @param int $qubaid the id of the usage to load.
 432       * @return question_usage_by_activity the usage that was loaded.
 433       */
 434      public function load_questions_usage_by_activity($qubaid) {
 435          $records = $this->db->get_recordset_sql("
 436  SELECT
 437      quba.id AS qubaid,
 438      quba.contextid,
 439      quba.component,
 440      quba.preferredbehaviour,
 441      qa.id AS questionattemptid,
 442      qa.questionusageid,
 443      qa.slot,
 444      qa.behaviour,
 445      qa.questionid,
 446      qa.variant,
 447      qa.maxmark,
 448      qa.minfraction,
 449      qa.maxfraction,
 450      qa.flagged,
 451      qa.questionsummary,
 452      qa.rightanswer,
 453      qa.responsesummary,
 454      qa.timemodified,
 455      qas.id AS attemptstepid,
 456      qas.sequencenumber,
 457      qas.state,
 458      qas.fraction,
 459      qas.timecreated,
 460      qas.userid,
 461      qasd.name,
 462      qasd.value
 463  
 464  FROM      {question_usages}            quba
 465  LEFT JOIN {question_attempts}          qa   ON qa.questionusageid    = quba.id
 466  LEFT JOIN {question_attempt_steps}     qas  ON qas.questionattemptid = qa.id
 467  LEFT JOIN {question_attempt_step_data} qasd ON qasd.attemptstepid    = qas.id
 468  
 469  WHERE
 470      quba.id = :qubaid
 471  
 472  ORDER BY
 473      qa.slot,
 474      qas.sequencenumber
 475      ", array('qubaid' => $qubaid));
 476  
 477          if (!$records->valid()) {
 478              throw new coding_exception('Failed to load questions_usage_by_activity ' . $qubaid);
 479          }
 480  
 481          $quba = question_usage_by_activity::load_from_records($records, $qubaid);
 482          $records->close();
 483  
 484          return $quba;
 485      }
 486  
 487      /**
 488       * Load all {@link question_usage_by_activity} from the database for one qubaid_condition
 489       * Include all its {@link question_attempt}s and all their steps.
 490       *
 491       * This method may be called publicly.
 492       *
 493       * @param qubaid_condition $qubaids the condition that tells us which usages to load.
 494       * @return question_usage_by_activity[] the usages that were loaded.
 495       */
 496      public function load_questions_usages_by_activity($qubaids) {
 497          $records = $this->db->get_recordset_sql("
 498  SELECT
 499      quba.id AS qubaid,
 500      quba.contextid,
 501      quba.component,
 502      quba.preferredbehaviour,
 503      qa.id AS questionattemptid,
 504      qa.questionusageid,
 505      qa.slot,
 506      qa.behaviour,
 507      qa.questionid,
 508      qa.variant,
 509      qa.maxmark,
 510      qa.minfraction,
 511      qa.maxfraction,
 512      qa.flagged,
 513      qa.questionsummary,
 514      qa.rightanswer,
 515      qa.responsesummary,
 516      qa.timemodified,
 517      qas.id AS attemptstepid,
 518      qas.sequencenumber,
 519      qas.state,
 520      qas.fraction,
 521      qas.timecreated,
 522      qas.userid,
 523      qasd.name,
 524      qasd.value
 525  
 526  FROM      {question_usages}            quba
 527  LEFT JOIN {question_attempts}          qa   ON qa.questionusageid    = quba.id
 528  LEFT JOIN {question_attempt_steps}     qas  ON qas.questionattemptid = qa.id
 529  LEFT JOIN {question_attempt_step_data} qasd ON qasd.attemptstepid    = qas.id
 530  
 531  WHERE
 532      quba.id {$qubaids->usage_id_in()}
 533  
 534  ORDER BY
 535      quba.id,
 536      qa.slot,
 537      qas.sequencenumber
 538      ", $qubaids->usage_id_in_params());
 539  
 540          $qubas = array();
 541          while ($records->valid()) {
 542              $record = $records->current();
 543              $qubas[$record->qubaid] = question_usage_by_activity::load_from_records($records, $record->qubaid);
 544          }
 545  
 546          $records->close();
 547  
 548          return $qubas;
 549      }
 550  
 551      /**
 552       * Load information about the latest state of each question from the database.
 553       *
 554       * This method may be called publicly.
 555       *
 556       * @param qubaid_condition $qubaids used to restrict which usages are included
 557       *                                  in the query. See {@link qubaid_condition}.
 558       * @param array|null       $slots   (optional) list of slots for which to return information. Default all slots.
 559       * @param string|null      $fields
 560       * @return array of records. See the SQL in this function to see the fields available.
 561       */
 562      public function load_questions_usages_latest_steps(qubaid_condition $qubaids, $slots = null, $fields = null) {
 563          if ($slots !== null) {
 564              [$slottest, $params] = $this->db->get_in_or_equal($slots, SQL_PARAMS_NAMED, 'slot');
 565              $slotwhere = " AND qa.slot {$slottest}";
 566          } else {
 567              $slotwhere = '';
 568              $params = [];
 569          }
 570  
 571          if ($fields === null) {
 572              $fields = "qas.id,
 573      qa.id AS questionattemptid,
 574      qa.questionusageid,
 575      qa.slot,
 576      qa.behaviour,
 577      qa.questionid,
 578      qa.variant,
 579      qa.maxmark,
 580      qa.minfraction,
 581      qa.maxfraction,
 582      qa.flagged,
 583      qa.questionsummary,
 584      qa.rightanswer,
 585      qa.responsesummary,
 586      qa.timemodified,
 587      qas.id AS attemptstepid,
 588      qas.sequencenumber,
 589      qas.state,
 590      qas.fraction,
 591      qas.timecreated,
 592      qas.userid";
 593  
 594          }
 595  
 596          $records = $this->db->get_records_sql("
 597  SELECT
 598      {$fields}
 599  
 600  FROM {$qubaids->from_question_attempts('qa')}
 601  JOIN {question_attempt_steps} qas ON qas.questionattemptid = qa.id
 602          AND qas.sequencenumber = {$this->latest_step_for_qa_subquery()}
 603  
 604  WHERE
 605      {$qubaids->where()}
 606      $slotwhere
 607          ", $params + $qubaids->from_where_params());
 608  
 609          return $records;
 610      }
 611  
 612      /**
 613       * Load summary information about the state of each question in a group of
 614       * attempts. This is used, for example, by the quiz manual grading report,
 615       * to show how many attempts at each question need to be graded.
 616       *
 617       * This method may be called publicly.
 618       *
 619       * @param qubaid_condition $qubaids used to restrict which usages are included
 620       * in the query. See {@link qubaid_condition}.
 621       * @param array|null $slots (optional) list of slots for which to return information. Default all slots.
 622       * @return array The array keys are 'slot,questionid'. The values are objects with
 623       * fields $slot, $questionid, $inprogress, $name, $needsgrading, $autograded,
 624       * $manuallygraded and $all.
 625       */
 626      public function load_questions_usages_question_state_summary(qubaid_condition $qubaids, $slots = null) {
 627          if ($slots !== null) {
 628              [$slottest, $params] = $this->db->get_in_or_equal($slots, SQL_PARAMS_NAMED, 'slot');
 629              $slotwhere = " AND qa.slot {$slottest}";
 630          } else {
 631              $slotwhere = '';
 632              $params = [];
 633          }
 634  
 635          $rs = $this->db->get_recordset_sql("
 636  SELECT
 637      qa.slot,
 638      qa.questionid,
 639      q.name,
 640      CASE qas.state
 641          {$this->full_states_to_summary_state_sql()}
 642      END AS summarystate,
 643      COUNT(1) AS numattempts
 644  
 645  FROM {$qubaids->from_question_attempts('qa')}
 646  JOIN {question_attempt_steps} qas ON qas.questionattemptid = qa.id
 647          AND qas.sequencenumber = {$this->latest_step_for_qa_subquery()}
 648  JOIN {question} q ON q.id = qa.questionid
 649  
 650  WHERE
 651      {$qubaids->where()}
 652      $slotwhere
 653  
 654  GROUP BY
 655      qa.slot,
 656      qa.questionid,
 657      q.name,
 658      q.id,
 659      CASE qas.state
 660          {$this->full_states_to_summary_state_sql()}
 661      END
 662  
 663  ORDER BY
 664      qa.slot,
 665      qa.questionid,
 666      q.name,
 667      q.id
 668          ", $params + $qubaids->from_where_params());
 669  
 670          $results = array();
 671          foreach ($rs as $row) {
 672              $index = $row->slot . ',' . $row->questionid;
 673  
 674              if (!array_key_exists($index, $results)) {
 675                  $res = new stdClass();
 676                  $res->slot = $row->slot;
 677                  $res->questionid = $row->questionid;
 678                  $res->name = $row->name;
 679                  $res->inprogress = 0;
 680                  $res->needsgrading = 0;
 681                  $res->autograded = 0;
 682                  $res->manuallygraded = 0;
 683                  $res->all = 0;
 684                  $results[$index] = $res;
 685              }
 686  
 687              $results[$index]->{$row->summarystate} = $row->numattempts;
 688              $results[$index]->all += $row->numattempts;
 689          }
 690          $rs->close();
 691  
 692          return $results;
 693      }
 694  
 695      /**
 696       * Get a list of usage ids where the question with slot $slot, and optionally
 697       * also with question id $questionid, is in summary state $summarystate. Also
 698       * return the total count of such states.
 699       *
 700       * Only a subset of the ids can be returned by using $orderby, $limitfrom and
 701       * $limitnum. A special value 'random' can be passed as $orderby, in which case
 702       * $limitfrom is ignored.
 703       *
 704       * This method may be called publicly.
 705       *
 706       * @param qubaid_condition $qubaids used to restrict which usages are included
 707       * in the query. See {@link qubaid_condition}.
 708       * @param int $slot The slot for the questions you want to know about.
 709       * @param int $questionid (optional) Only return attempts that were of this specific question.
 710       * @param string $summarystate the summary state of interest, or 'all'.
 711       * @param string $orderby the column to order by.
 712       * @param array $params any params required by any of the SQL fragments.
 713       * @param int $limitfrom implements paging of the results.
 714       *      Ignored if $orderby = random or $limitnum is null.
 715       * @param int $limitnum implements paging of the results. null = all.
 716       * @return array with two elements, an array of usage ids, and a count of the total number.
 717       */
 718      public function load_questions_usages_where_question_in_state(
 719              qubaid_condition $qubaids, $summarystate, $slot, $questionid = null,
 720              $orderby = 'random', $params = array(), $limitfrom = 0, $limitnum = null) {
 721  
 722          $extrawhere = '';
 723          if ($questionid) {
 724              $extrawhere .= ' AND qa.questionid = :questionid';
 725              $params['questionid'] = $questionid;
 726          }
 727          if ($summarystate != 'all') {
 728              list($test, $sparams) = $this->in_summary_state_test($summarystate);
 729              $extrawhere .= ' AND qas.state ' . $test;
 730              $params += $sparams;
 731          }
 732  
 733          if ($orderby == 'random') {
 734              $sqlorderby = '';
 735          } else if ($orderby) {
 736              $sqlorderby = 'ORDER BY ' . $orderby;
 737          } else {
 738              $sqlorderby = '';
 739          }
 740  
 741          // We always want the total count, as well as the partcular list of ids
 742          // based on the paging and sort order. Because the list of ids is never
 743          // going to be too ridiculously long. My worst-case scenario is
 744          // 10,000 students in the course, each doing 5 quiz attempts. That
 745          // is a 50,000 element int => int array, which PHP seems to use 5MB
 746          // memory to store on a 64 bit server.
 747          $qubaidswhere = $qubaids->where(); // Must call this before params.
 748          $params += $qubaids->from_where_params();
 749          $params['slot'] = $slot;
 750  
 751          $qubaids = $this->db->get_records_sql_menu("
 752  SELECT
 753      qa.questionusageid,
 754      1
 755  
 756  FROM {$qubaids->from_question_attempts('qa')}
 757  JOIN {question_attempt_steps} qas ON qas.questionattemptid = qa.id
 758          AND qas.sequencenumber = {$this->latest_step_for_qa_subquery()}
 759  JOIN {question} q ON q.id = qa.questionid
 760  
 761  WHERE
 762      {$qubaidswhere} AND
 763      qa.slot = :slot
 764      $extrawhere
 765  
 766  $sqlorderby
 767          ", $params);
 768  
 769          $qubaids = array_keys($qubaids);
 770          $count = count($qubaids);
 771  
 772          if ($orderby == 'random') {
 773              shuffle($qubaids);
 774              $limitfrom = 0;
 775          }
 776  
 777          if (!is_null($limitnum)) {
 778              $qubaids = array_slice($qubaids, $limitfrom, $limitnum);
 779          }
 780  
 781          return array($qubaids, $count);
 782      }
 783  
 784      /**
 785       * Load the average mark, and number of attempts, for each slot in a set of
 786       * question usages..
 787       *
 788       * This method may be called publicly.
 789       *
 790       * @param qubaid_condition $qubaids used to restrict which usages are included
 791       * in the query. See {@link qubaid_condition}.
 792       * @param array|null $slots if null, load info for all quesitions, otherwise only
 793       * load the averages for the specified questions.
 794       * @return array of objects with fields ->slot, ->averagefraction and ->numaveraged.
 795       */
 796      public function load_average_marks(qubaid_condition $qubaids, $slots = null) {
 797          if (!empty($slots)) {
 798              list($slottest, $slotsparams) = $this->db->get_in_or_equal(
 799                      $slots, SQL_PARAMS_NAMED, 'slot');
 800              $slotwhere = " AND qa.slot {$slottest}";
 801          } else {
 802              $slotwhere = '';
 803              $slotsparams = array();
 804          }
 805  
 806          list($statetest, $stateparams) = $this->db->get_in_or_equal(array(
 807                  (string) question_state::$gaveup,
 808                  (string) question_state::$gradedwrong,
 809                  (string) question_state::$gradedpartial,
 810                  (string) question_state::$gradedright,
 811                  (string) question_state::$mangaveup,
 812                  (string) question_state::$mangrwrong,
 813                  (string) question_state::$mangrpartial,
 814                  (string) question_state::$mangrright), SQL_PARAMS_NAMED, 'st');
 815  
 816          return $this->db->get_records_sql("
 817  SELECT
 818      qa.slot,
 819      AVG(COALESCE(qas.fraction, 0)) AS averagefraction,
 820      COUNT(1) AS numaveraged
 821  
 822  FROM {$qubaids->from_question_attempts('qa')}
 823  JOIN {question_attempt_steps} qas ON qas.questionattemptid = qa.id
 824          AND qas.sequencenumber = {$this->latest_step_for_qa_subquery()}
 825  
 826  WHERE
 827      {$qubaids->where()}
 828      $slotwhere
 829      AND qas.state $statetest
 830  
 831  GROUP BY qa.slot
 832  
 833  ORDER BY qa.slot
 834          ", $slotsparams + $stateparams + $qubaids->from_where_params());
 835      }
 836  
 837      /**
 838       * Load all the attempts at a given queston from a set of question_usages.
 839       * steps.
 840       *
 841       * This method may be called publicly.
 842       *
 843       * @param int $questionid the question to load all the attempts fors.
 844       * @param qubaid_condition $qubaids used to restrict which usages are included
 845       * in the query. See {@link qubaid_condition}.
 846       * @return question_attempt[] array of question_attempts that were loaded.
 847       */
 848      public function load_attempts_at_question($questionid, qubaid_condition $qubaids) {
 849          $sql = "
 850  SELECT
 851      quba.contextid,
 852      quba.preferredbehaviour,
 853      qa.id AS questionattemptid,
 854      qa.questionusageid,
 855      qa.slot,
 856      qa.behaviour,
 857      qa.questionid,
 858      qa.variant,
 859      qa.maxmark,
 860      qa.minfraction,
 861      qa.maxfraction,
 862      qa.flagged,
 863      qa.questionsummary,
 864      qa.rightanswer,
 865      qa.responsesummary,
 866      qa.timemodified,
 867      qas.id AS attemptstepid,
 868      qas.sequencenumber,
 869      qas.state,
 870      qas.fraction,
 871      qas.timecreated,
 872      qas.userid,
 873      qasd.name,
 874      qasd.value
 875  
 876  FROM {$qubaids->from_question_attempts('qa')}
 877  JOIN {question_usages} quba ON quba.id = qa.questionusageid
 878  LEFT JOIN {question_attempt_steps} qas ON qas.questionattemptid = qa.id
 879  LEFT JOIN {question_attempt_step_data} qasd ON qasd.attemptstepid = qas.id
 880  
 881  WHERE
 882      {$qubaids->where()} AND
 883      qa.questionid = :questionid
 884  
 885  ORDER BY
 886      quba.id,
 887      qa.id,
 888      qas.sequencenumber";
 889  
 890          // For qubaid_list must call this after calling methods that generate sql.
 891          $params = $qubaids->from_where_params();
 892          $params['questionid'] = $questionid;
 893  
 894          $records = $this->db->get_recordset_sql($sql, $params);
 895  
 896          $questionattempts = array();
 897          while ($records->valid()) {
 898              $record = $records->current();
 899              $questionattempts[$record->questionattemptid] =
 900                      question_attempt::load_from_records($records,
 901                      $record->questionattemptid, new question_usage_null_observer(),
 902                      $record->preferredbehaviour);
 903          }
 904          $records->close();
 905  
 906          return $questionattempts;
 907      }
 908  
 909      /**
 910       * Update a question_usages row to refect any changes in a usage (but not
 911       * any of its question_attempts.
 912       *
 913       * You should not call this method directly. You should use
 914       * @link question_engine::save_questions_usage_by_activity()}.
 915       *
 916       * @param question_usage_by_activity $quba the usage that has changed.
 917       */
 918      public function update_questions_usage_by_activity(question_usage_by_activity $quba) {
 919          $record = new stdClass();
 920          $record->id = $quba->get_id();
 921          $record->contextid = $quba->get_owning_context()->id;
 922          $record->component = $quba->get_owning_component();
 923          $record->preferredbehaviour = $quba->get_preferred_behaviour();
 924  
 925          $this->db->update_record('question_usages', $record);
 926      }
 927  
 928      /**
 929       * Update a question_attempts row to refect any changes in a question_attempt
 930       * (but not any of its steps).
 931       *
 932       * You should not call this method directly. You should use
 933       * @link question_engine::save_questions_usage_by_activity()}.
 934       *
 935       * @param question_attempt $qa the question attempt that has changed.
 936       */
 937      public function update_question_attempt(question_attempt $qa) {
 938          $record = new stdClass();
 939          $record->id = $qa->get_database_id();
 940          $record->slot = $qa->get_slot();
 941          $record->variant = $qa->get_variant();
 942          $record->maxmark = $qa->get_max_mark();
 943          $record->minfraction = $qa->get_min_fraction();
 944          $record->maxfraction = $qa->get_max_fraction();
 945          $record->flagged = $qa->is_flagged();
 946          $record->questionsummary = $qa->get_question_summary();
 947          $record->rightanswer = $qa->get_right_answer_summary();
 948          $record->responsesummary = $qa->get_response_summary();
 949          $record->timemodified = time();
 950  
 951          $this->db->update_record('question_attempts', $record);
 952      }
 953  
 954      /**
 955       * Delete a question_usage_by_activity and all its associated
 956       *
 957       * You should not call this method directly. You should use
 958       * @link question_engine::delete_questions_usage_by_activities()}.
 959       *
 960       * {@link question_attempts} and {@link question_attempt_steps} from the
 961       * database.
 962       * @param qubaid_condition $qubaids identifies which question useages to delete.
 963       */
 964      public function delete_questions_usage_by_activities(qubaid_condition $qubaids) {
 965          $where = "qa.questionusageid {$qubaids->usage_id_in()}";
 966          $params = $qubaids->usage_id_in_params();
 967  
 968          $contextids = $this->db->get_records_sql_menu("
 969                  SELECT DISTINCT contextid, 1
 970                  FROM {question_usages}
 971                  WHERE id {$qubaids->usage_id_in()}", $qubaids->usage_id_in_params());
 972          foreach ($contextids as $contextid => $notused) {
 973              $this->delete_response_files($contextid, "IN (
 974                      SELECT qas.id
 975                      FROM {question_attempts} qa
 976                      JOIN {question_attempt_steps} qas ON qas.questionattemptid = qa.id
 977                      WHERE $where)", $params);
 978          }
 979  
 980          if ($this->db->get_dbfamily() == 'mysql') {
 981              $this->delete_usage_records_for_mysql($qubaids);
 982              return;
 983          }
 984  
 985          $this->db->delete_records_select('question_attempt_step_data', "attemptstepid IN (
 986                  SELECT qas.id
 987                  FROM {question_attempts} qa
 988                  JOIN {question_attempt_steps} qas ON qas.questionattemptid = qa.id
 989                  WHERE $where)", $params);
 990  
 991          $this->db->delete_records_select('question_attempt_steps', "questionattemptid IN (
 992                  SELECT qa.id
 993                  FROM {question_attempts} qa
 994                  WHERE $where)", $params);
 995  
 996          $this->db->delete_records_select('question_attempts',
 997                  "{question_attempts}.questionusageid {$qubaids->usage_id_in()}",
 998                  $qubaids->usage_id_in_params());
 999  
1000          $this->db->delete_records_select('question_usages',
1001                  "{question_usages}.id {$qubaids->usage_id_in()}", $qubaids->usage_id_in_params());
1002      }
1003  
1004      /**
1005       * This function is a work-around for poor MySQL performance with
1006       * DELETE FROM x WHERE id IN (SELECT ...). We have to use a non-standard
1007       * syntax to get good performance. See MDL-29520.
1008       * @param qubaid_condition $qubaids identifies which question useages to delete.
1009       */
1010      protected function delete_usage_records_for_mysql(qubaid_condition $qubaids) {
1011          // Get the list of question attempts to delete and delete them in chunks.
1012          $allids = $this->db->get_records_sql_menu("
1013                  SELECT DISTINCT id, id AS id2
1014                    FROM {question_usages}
1015                   WHERE id " . $qubaids->usage_id_in(),
1016                  $qubaids->usage_id_in_params());
1017  
1018          foreach (array_chunk($allids, 1000) as $todelete) {
1019              list($idsql, $idparams) = $this->db->get_in_or_equal($todelete);
1020              $this->db->execute('
1021                      DELETE qu, qa, qas, qasd
1022                        FROM {question_usages}            qu
1023                        JOIN {question_attempts}          qa   ON qa.questionusageid = qu.id
1024                   LEFT JOIN {question_attempt_steps}     qas  ON qas.questionattemptid = qa.id
1025                   LEFT JOIN {question_attempt_step_data} qasd ON qasd.attemptstepid = qas.id
1026                       WHERE qu.id ' . $idsql,
1027                      $idparams);
1028          }
1029      }
1030  
1031      /**
1032       * Delete some steps of a question attempt.
1033       *
1034       * Private method, only for use by other parts of the question engine.
1035       *
1036       * @param array $stepids array of step ids to delete.
1037       * @param context $context the context that the $quba belongs to.
1038       */
1039      public function delete_steps($stepids, $context) {
1040          if (empty($stepids)) {
1041              return;
1042          }
1043          list($test, $params) = $this->db->get_in_or_equal($stepids, SQL_PARAMS_NAMED);
1044  
1045          $this->delete_response_files($context->id, $test, $params);
1046  
1047          $this->db->delete_records_select('question_attempt_step_data',
1048                  "attemptstepid {$test}", $params);
1049          $this->db->delete_records_select('question_attempt_steps',
1050                  "id {$test}", $params);
1051      }
1052  
1053      /**
1054       * Delete all the files belonging to the response variables in the gives
1055       * question attempt steps.
1056       * @param int $contextid the context these attempts belong to.
1057       * @param string $itemidstest a bit of SQL that can be used in a
1058       *      WHERE itemid $itemidstest clause. Must use named params.
1059       * @param array $params any query parameters used in $itemidstest.
1060       */
1061      protected function delete_response_files($contextid, $itemidstest, $params) {
1062          $fs = get_file_storage();
1063          foreach (question_engine::get_all_response_file_areas() as $filearea) {
1064              $fs->delete_area_files_select($contextid, 'question', $filearea,
1065                      $itemidstest, $params);
1066          }
1067      }
1068  
1069      /**
1070       * Delete all the previews for a given question.
1071       *
1072       * Private method, only for use by other parts of the question engine.
1073       *
1074       * @param int $questionid question id.
1075       */
1076      public function delete_previews($questionid) {
1077          $previews = $this->db->get_records_sql_menu("
1078                  SELECT DISTINCT quba.id, 1
1079                  FROM {question_usages} quba
1080                  JOIN {question_attempts} qa ON qa.questionusageid = quba.id
1081                  WHERE quba.component = 'core_question_preview' AND
1082                      qa.questionid = ?", array($questionid));
1083          if (empty($previews)) {
1084              return;
1085          }
1086          $this->delete_questions_usage_by_activities(new qubaid_list($previews));
1087      }
1088  
1089      /**
1090       * Update the flagged state of a question in the database.
1091       *
1092       * You should call {@link question_engine::update_flag()()}
1093       * rather than calling this method directly.
1094       *
1095       * @param int $qubaid the question usage id.
1096       * @param int $questionid the question id.
1097       * @param int $qaid the question_attempt id.
1098       * @param int $slot the slot number of the question attempt to update.
1099       * @param bool $newstate the new state of the flag. true = flagged.
1100       */
1101      public function update_question_attempt_flag($qubaid, $questionid, $qaid, $slot, $newstate) {
1102          if (!$this->db->record_exists('question_attempts', array('id' => $qaid,
1103                  'questionusageid' => $qubaid, 'questionid' => $questionid, 'slot' => $slot))) {
1104              throw new moodle_exception('errorsavingflags', 'question');
1105          }
1106  
1107          $this->db->set_field('question_attempts', 'flagged', $newstate, array('id' => $qaid));
1108      }
1109  
1110      /**
1111       * Get all the WHEN 'x' THEN 'y' terms needed to convert the question_attempt_steps.state
1112       * column to a summary state. Use this like
1113       * CASE qas.state {$this->full_states_to_summary_state_sql()} END AS summarystate,
1114       *
1115       * @return string SQL fragment.
1116       */
1117      protected function full_states_to_summary_state_sql() {
1118          $sql = '';
1119          foreach (question_state::get_all() as $state) {
1120              $sql .= "WHEN '{$state}' THEN '{$state->get_summary_state()}'\n";
1121          }
1122          return $sql;
1123      }
1124  
1125      /**
1126       * Get the SQL needed to test that question_attempt_steps.state is in a
1127       * state corresponding to $summarystate.
1128       *
1129       * This method may be called publicly.
1130       *
1131       * @param string $summarystate one of
1132       * inprogress, needsgrading, manuallygraded or autograded
1133       * @param bool $equal if false, do a NOT IN test. Default true.
1134       * @param string $prefix used in the call to $DB->get_in_or_equal().
1135       * @return array as returned by $DB->get_in_or_equal().
1136       */
1137      public function in_summary_state_test($summarystate, $equal = true, $prefix = 'summarystates') {
1138          $states = question_state::get_all_for_summary_state($summarystate);
1139          return $this->db->get_in_or_equal(array_map('strval', $states),
1140                  SQL_PARAMS_NAMED, $prefix, $equal);
1141      }
1142  
1143      /**
1144       * Change the maxmark for the question_attempt with number in usage $slot
1145       * for all the specified question_attempts.
1146       *
1147       * You should call {@link question_engine::set_max_mark_in_attempts()}
1148       * rather than calling this method directly.
1149       *
1150       * @param qubaid_condition $qubaids Selects which usages are updated.
1151       * @param int $slot the number is usage to affect.
1152       * @param number $newmaxmark the new max mark to set.
1153       */
1154      public function set_max_mark_in_attempts(qubaid_condition $qubaids, $slot, $newmaxmark) {
1155          if ($this->db->get_dbfamily() == 'mysql') {
1156              // MySQL's query optimiser completely fails to cope with the
1157              // set_field_select call below, so we have to give it a clue. See MDL-32616.
1158              // TODO MDL-29589 encapsulate this MySQL-specific code with a $DB method.
1159              $this->db->execute("
1160                      UPDATE " . $qubaids->from_question_attempts('qa') . "
1161                         SET qa.maxmark = :newmaxmark
1162                       WHERE " . $qubaids->where() . "
1163                         AND slot = :slot
1164                      ", $qubaids->from_where_params() + array('newmaxmark' => $newmaxmark, 'slot' => $slot));
1165              return;
1166          }
1167  
1168          // Normal databases.
1169          $this->db->set_field_select('question_attempts', 'maxmark', $newmaxmark,
1170                  "questionusageid {$qubaids->usage_id_in()} AND slot = :slot",
1171                  $qubaids->usage_id_in_params() + array('slot' => $slot));
1172      }
1173  
1174      /**
1175       * Return a subquery that computes the sum of the marks for all the questions
1176       * in a usage. Which useage to compute the sum for is controlled bu the $qubaid
1177       * parameter.
1178       *
1179       * See {@link quiz_update_all_attempt_sumgrades()} for an example of the usage of
1180       * this method.
1181       *
1182       * This method may be called publicly.
1183       *
1184       * @param string $qubaid SQL fragment that controls which usage is summed.
1185       * This will normally be the name of a column in the outer query. Not that this
1186       * SQL fragment must not contain any placeholders.
1187       * @return string SQL code for the subquery.
1188       */
1189      public function sum_usage_marks_subquery($qubaid) {
1190          // To explain the COALESCE in the following SQL: SUM(lots of NULLs) gives
1191          // NULL, while SUM(one 0.0 and lots of NULLS) gives 0.0. We don't want that.
1192          // We always want to return a number, so the COALESCE is there to turn the
1193          // NULL total into a 0.
1194          return "SELECT COALESCE(SUM(qa.maxmark * qas.fraction), 0)
1195              FROM {question_attempts} qa
1196              JOIN {question_attempt_steps} qas ON qas.questionattemptid = qa.id
1197                      AND qas.sequencenumber = (
1198                              SELECT MAX(summarks_qas.sequencenumber)
1199                                FROM {question_attempt_steps} summarks_qas
1200                               WHERE summarks_qas.questionattemptid = qa.id
1201              )
1202              WHERE qa.questionusageid = $qubaid
1203              HAVING COUNT(CASE
1204                  WHEN qas.state = 'needsgrading' AND qa.maxmark > 0 THEN 1
1205                  ELSE NULL
1206              END) = 0";
1207      }
1208  
1209      /**
1210       * Get a subquery that returns the latest step of every qa in some qubas.
1211       * Currently, this is only used by the quiz reports. See
1212       * {@link quiz_attempts_report_table::add_latest_state_join()}.
1213       *
1214       * This method may be called publicly.
1215       *
1216       * @param string $alias alias to use for this inline-view.
1217       * @param qubaid_condition $qubaids restriction on which question_usages we
1218       *      are interested in. This is important for performance.
1219       * @return array with two elements, the SQL fragment and any params requried.
1220       */
1221      public function question_attempt_latest_state_view($alias, qubaid_condition $qubaids) {
1222          return array("(
1223                  SELECT {$alias}qa.id AS questionattemptid,
1224                         {$alias}qa.questionusageid,
1225                         {$alias}qa.slot,
1226                         {$alias}qa.behaviour,
1227                         {$alias}qa.questionid,
1228                         {$alias}qa.variant,
1229                         {$alias}qa.maxmark,
1230                         {$alias}qa.minfraction,
1231                         {$alias}qa.maxfraction,
1232                         {$alias}qa.flagged,
1233                         {$alias}qa.questionsummary,
1234                         {$alias}qa.rightanswer,
1235                         {$alias}qa.responsesummary,
1236                         {$alias}qa.timemodified,
1237                         {$alias}qas.id AS attemptstepid,
1238                         {$alias}qas.sequencenumber,
1239                         {$alias}qas.state,
1240                         {$alias}qas.fraction,
1241                         {$alias}qas.timecreated,
1242                         {$alias}qas.userid
1243  
1244                    FROM {$qubaids->from_question_attempts($alias . 'qa')}
1245                    JOIN {question_attempt_steps} {$alias}qas ON {$alias}qas.questionattemptid = {$alias}qa.id
1246                              AND {$alias}qas.sequencenumber = {$this->latest_step_for_qa_subquery($alias . 'qa.id')}
1247                   WHERE {$qubaids->where()}
1248              ) {$alias}", $qubaids->from_where_params());
1249      }
1250  
1251      protected function latest_step_for_qa_subquery($questionattemptid = 'qa.id') {
1252          return "(
1253                  SELECT MAX(sequencenumber)
1254                  FROM {question_attempt_steps}
1255                  WHERE questionattemptid = $questionattemptid
1256              )";
1257      }
1258  
1259      /**
1260       * Are any of these questions are currently in use?
1261       *
1262       * You should call {@link question_engine::questions_in_use()}
1263       * rather than calling this method directly.
1264       *
1265       * @param array $questionids of question ids.
1266       * @param qubaid_condition $qubaids ids of the usages to consider.
1267       * @return bool whether any of these questions are being used by any of
1268       *      those usages.
1269       */
1270      public function questions_in_use(array $questionids, qubaid_condition $qubaids) {
1271          list($test, $params) = $this->db->get_in_or_equal($questionids);
1272          return $this->db->record_exists_select('question_attempts',
1273                  'questionid ' . $test . ' AND questionusageid ' .
1274                  $qubaids->usage_id_in(), $params + $qubaids->usage_id_in_params());
1275      }
1276  
1277      /**
1278       * Get the number of times each variant has been used for each question in a list
1279       * in a set of usages.
1280       * @param array $questionids of question ids.
1281       * @param qubaid_condition $qubaids ids of the usages to consider.
1282       * @return array questionid => variant number => num uses.
1283       */
1284      public function load_used_variants(array $questionids, qubaid_condition $qubaids) {
1285          list($test, $params) = $this->db->get_in_or_equal($questionids, SQL_PARAMS_NAMED, 'qid');
1286          $recordset = $this->db->get_recordset_sql("
1287                  SELECT qa.questionid, qa.variant, COUNT(1) AS usescount
1288                    FROM " . $qubaids->from_question_attempts('qa') . "
1289                   WHERE qa.questionid $test
1290                     AND " . $qubaids->where() . "
1291                GROUP BY qa.questionid, qa.variant
1292                ORDER BY COUNT(1) ASC
1293                  ", $params + $qubaids->from_where_params());
1294  
1295          $usedvariants = array_combine($questionids, array_fill(0, count($questionids), array()));
1296          foreach ($recordset as $row) {
1297              $usedvariants[$row->questionid][$row->variant] = $row->usescount;
1298          }
1299          $recordset->close();
1300          return $usedvariants;
1301      }
1302  }
1303  
1304  
1305  /**
1306   * Implementation of the unit of work pattern for the question engine.
1307   *
1308   * See http://martinfowler.com/eaaCatalog/unitOfWork.html. This tracks all the
1309   * changes to a {@link question_usage_by_activity}, and its constituent parts,
1310   * so that the changes can be saved to the database when {@link save()} is called.
1311   *
1312   * @copyright  2009 The Open University
1313   * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
1314   */
1315  class question_engine_unit_of_work implements question_usage_observer {
1316      /** @var question_usage_by_activity the usage being tracked. */
1317      protected $quba;
1318  
1319      /** @var boolean whether any of the fields of the usage have been changed. */
1320      protected $modified = false;
1321  
1322      /**
1323       * @var question_attempt[] list of slot => {@link question_attempt}s that
1324       * have been added to the usage.
1325       */
1326      protected $attemptsadded = array();
1327  
1328      /**
1329       * @var question_attempt[] list of slot => {@link question_attempt}s that
1330       * were already in the usage, and which have been modified.
1331       */
1332      protected $attemptsmodified = array();
1333  
1334      /**
1335       * @var question_attempt[] list of slot => {@link question_attempt}s that
1336       * have been added to the usage.
1337       */
1338      protected $attemptsdeleted = array();
1339  
1340      /**
1341       * @var array of array(question_attempt_step, question_attempt id, seq number)
1342       * of steps that have been added to question attempts in this usage.
1343       */
1344      protected $stepsadded = array();
1345  
1346      /**
1347       * @var array of array(question_attempt_step, question_attempt id, seq number)
1348       * of steps that have been modified in their attempt.
1349       */
1350      protected $stepsmodified = array();
1351  
1352      /**
1353       * @var question_attempt_step[] list of question_attempt_step.id => question_attempt_step of steps
1354       * that were previously stored in the database, but which are no longer required.
1355       */
1356      protected $stepsdeleted = array();
1357  
1358      /**
1359       * @var array int slot => string name => question_attempt.
1360       */
1361      protected $metadataadded = array();
1362  
1363      /**
1364       * @var array int slot => string name => question_attempt.
1365       */
1366      protected $metadatamodified = array();
1367  
1368      /**
1369       * Constructor.
1370       * @param question_usage_by_activity $quba the usage to track.
1371       */
1372      public function __construct(question_usage_by_activity $quba) {
1373          $this->quba = $quba;
1374      }
1375  
1376      public function notify_modified() {
1377          $this->modified = true;
1378      }
1379  
1380      public function notify_attempt_added(question_attempt $qa) {
1381          $this->attemptsadded[$qa->get_slot()] = $qa;
1382      }
1383  
1384      public function notify_attempt_modified(question_attempt $qa) {
1385          $slot = $qa->get_slot();
1386          if (!array_key_exists($slot, $this->attemptsadded)) {
1387              $this->attemptsmodified[$slot] = $qa;
1388          }
1389      }
1390  
1391      public function notify_attempt_moved(question_attempt $qa, $oldslot) {
1392          $newslot = $qa->get_slot();
1393  
1394          if (array_key_exists($oldslot, $this->attemptsadded)) {
1395              unset($this->attemptsadded[$oldslot]);
1396              $this->attemptsadded[$newslot] = $qa;
1397              return;
1398          }
1399  
1400          if (array_key_exists($oldslot, $this->attemptsmodified)) {
1401              unset($this->attemptsmodified[$oldslot]);
1402          }
1403          $this->attemptsmodified[$newslot] = $qa;
1404  
1405          if (array_key_exists($oldslot, $this->metadataadded)) {
1406              $this->metadataadded[$newslot] = $this->metadataadded[$oldslot];
1407              unset($this->metadataadded[$oldslot]);
1408          }
1409          if (array_key_exists($oldslot, $this->metadatamodified)) {
1410              $this->metadatamodified[$newslot] = $this->metadatamodified[$oldslot];
1411              unset($this->metadatamodified[$oldslot]);
1412          }
1413      }
1414  
1415      public function notify_step_added(question_attempt_step $step, question_attempt $qa, $seq) {
1416          if (array_key_exists($qa->get_slot(), $this->attemptsadded)) {
1417              return;
1418          }
1419  
1420          if (($key = $this->is_step_added($step)) !== false) {
1421              return;
1422          }
1423  
1424          if (($key = $this->is_step_modified($step)) !== false) {
1425              throw new coding_exception('Cannot add a step that has already been modified.');
1426          }
1427  
1428          if (($key = $this->is_step_deleted($step)) !== false) {
1429              unset($this->stepsdeleted[$step->get_id()]);
1430              $this->stepsmodified[] = array($step, $qa->get_database_id(), $seq);
1431              return;
1432          }
1433  
1434          $stepid = $step->get_id();
1435          if ($stepid) {
1436              if (array_key_exists($stepid, $this->stepsdeleted)) {
1437                  unset($this->stepsdeleted[$stepid]);
1438              }
1439              $this->stepsmodified[] = array($step, $qa->get_database_id(), $seq);
1440  
1441          } else {
1442              $this->stepsadded[] = array($step, $qa->get_database_id(), $seq);
1443          }
1444      }
1445  
1446      public function notify_step_modified(question_attempt_step $step, question_attempt $qa, $seq) {
1447          if (array_key_exists($qa->get_slot(), $this->attemptsadded)) {
1448              return;
1449          }
1450  
1451          if (($key = $this->is_step_added($step)) !== false) {
1452              return;
1453          }
1454  
1455          if (($key = $this->is_step_deleted($step)) !== false) {
1456              throw new coding_exception('Cannot modify a step after it has been deleted.');
1457          }
1458  
1459          $stepid = $step->get_id();
1460          if (empty($stepid)) {
1461              throw new coding_exception('Cannot modify a step that has never been stored in the database.');
1462          }
1463  
1464          $this->stepsmodified[] = array($step, $qa->get_database_id(), $seq);
1465      }
1466  
1467      public function notify_step_deleted(question_attempt_step $step, question_attempt $qa) {
1468          if (array_key_exists($qa->get_slot(), $this->attemptsadded)) {
1469              return;
1470          }
1471  
1472          if (($key = $this->is_step_added($step)) !== false) {
1473              unset($this->stepsadded[$key]);
1474              return;
1475          }
1476  
1477          if (($key = $this->is_step_modified($step)) !== false) {
1478              unset($this->stepsmodified[$key]);
1479          }
1480  
1481          $stepid = $step->get_id();
1482          if (empty($stepid)) {
1483              return; // Was never in the database.
1484          }
1485  
1486          $this->stepsdeleted[$stepid] = $step;
1487      }
1488  
1489      public function notify_metadata_added(question_attempt $qa, $name) {
1490          if (array_key_exists($qa->get_slot(), $this->attemptsadded)) {
1491              return;
1492          }
1493  
1494          if ($this->is_step_added($qa->get_step(0)) !== false) {
1495              return;
1496          }
1497  
1498          if (isset($this->metadataadded[$qa->get_slot()][$name])) {
1499              return;
1500          }
1501  
1502          $this->metadataadded[$qa->get_slot()][$name] = $qa;
1503      }
1504  
1505      public function notify_metadata_modified(question_attempt $qa, $name) {
1506          if (array_key_exists($qa->get_slot(), $this->attemptsadded)) {
1507              return;
1508          }
1509  
1510          if ($this->is_step_added($qa->get_step(0)) !== false) {
1511              return;
1512          }
1513  
1514          if (isset($this->metadataadded[$qa->get_slot()][$name])) {
1515              return;
1516          }
1517  
1518          if (isset($this->metadatamodified[$qa->get_slot()][$name])) {
1519              return;
1520          }
1521  
1522          $this->metadatamodified[$qa->get_slot()][$name] = $qa;
1523      }
1524  
1525      /**
1526       * Determine if a step is new. If so get its array key.
1527       *
1528       * @param question_attempt_step $step a step
1529       * @return int|false if the step is in the list of steps to be added, return
1530       *      the key, otherwise return false.
1531       */
1532      protected function is_step_added(question_attempt_step $step) {
1533          foreach ($this->stepsadded as $key => $data) {
1534              list($addedstep) = $data;
1535              if ($addedstep === $step) {
1536                  return $key;
1537              }
1538          }
1539          return false;
1540      }
1541  
1542      /**
1543       * Determine if a step is modified. If so get its array key.
1544       *
1545       * @param question_attempt_step $step a step
1546       * @return int|false if the step is in the list of steps to be modified, return
1547       *      the key, otherwise return false.
1548       */
1549      protected function is_step_modified(question_attempt_step $step) {
1550          foreach ($this->stepsmodified as $key => $data) {
1551              list($modifiedstep) = $data;
1552              if ($modifiedstep === $step) {
1553                  return $key;
1554              }
1555          }
1556          return false;
1557      }
1558  
1559      /**
1560       * @param question_attempt_step $step a step
1561       * @return bool whether the step is in the list of steps to be deleted.
1562       */
1563      protected function is_step_deleted(question_attempt_step $step) {
1564          foreach ($this->stepsdeleted as $deletedstep) {
1565              if ($deletedstep === $step) {
1566                  return true;
1567              }
1568          }
1569          return false;
1570      }
1571  
1572      /**
1573       * Write all the changes we have recorded to the database.
1574       * @param question_engine_data_mapper $dm the mapper to use to update the database.
1575       */
1576      public function save(question_engine_data_mapper $dm) {
1577          $dm->delete_steps(array_keys($this->stepsdeleted), $this->quba->get_owning_context());
1578  
1579          // Initially an array of array of question_attempt_step_objects.
1580          // Built as a nested array for efficiency, then flattened.
1581          $stepdata = array();
1582  
1583          foreach ($this->stepsmodified as $stepinfo) {
1584              list($step, $questionattemptid, $seq) = $stepinfo;
1585              $stepdata[] = $dm->update_question_attempt_step(
1586                      $step, $questionattemptid, $seq, $this->quba->get_owning_context());
1587          }
1588  
1589          foreach ($this->stepsadded as $stepinfo) {
1590              list($step, $questionattemptid, $seq) = $stepinfo;
1591              $stepdata[] = $dm->insert_question_attempt_step(
1592                      $step, $questionattemptid, $seq, $this->quba->get_owning_context());
1593          }
1594  
1595          foreach ($this->attemptsmodified as $qa) {
1596              $dm->update_question_attempt($qa);
1597          }
1598  
1599          foreach ($this->attemptsadded as $qa) {
1600              $stepdata[] = $dm->insert_question_attempt(
1601                      $qa, $this->quba->get_owning_context());
1602          }
1603  
1604          foreach ($this->metadataadded as $info) {
1605              $qa = reset($info);
1606              $stepdata[] = $dm->insert_question_attempt_metadata($qa, array_keys($info));
1607          }
1608  
1609          foreach ($this->metadatamodified as $info) {
1610              $qa = reset($info);
1611              $stepdata[] = $dm->update_question_attempt_metadata($qa, array_keys($info));
1612          }
1613  
1614          if ($this->modified) {
1615              $dm->update_questions_usage_by_activity($this->quba);
1616          }
1617  
1618          $dm->insert_all_step_data($dm->combine_step_data($stepdata));
1619  
1620          $this->stepsdeleted = array();
1621          $this->stepsmodified = array();
1622          $this->stepsadded = array();
1623          $this->attemptsdeleted = array();
1624          $this->attemptsadded = array();
1625          $this->attemptsmodified = array();
1626          $this->modified = false;
1627      }
1628  }
1629  
1630  
1631  /**
1632   * The interface implemented by {@link question_file_saver} and {@link question_file_loader}.
1633   *
1634   * @copyright  2012 The Open University
1635   * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
1636   */
1637  interface question_response_files {
1638      /**
1639       * Get the files that were submitted.
1640       * @return array of stored_files objects.
1641       */
1642      public function get_files();
1643  }
1644  
1645  
1646  /**
1647   * This class represents the promise to save some files from a particular draft
1648   * file area into a particular file area. It is used beause the necessary
1649   * information about what to save is to hand in the
1650   * {@link question_attempt::process_response_files()} method, but we don't know
1651   * if this question attempt will actually be saved in the database until later,
1652   * when the {@link question_engine_unit_of_work} is saved, if it is.
1653   *
1654   * @copyright  2011 The Open University
1655   * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
1656   */
1657  class question_file_saver implements question_response_files {
1658      /** @var int the id of the draft file area to save files from. */
1659      protected $draftitemid;
1660      /** @var string the owning component name. */
1661      protected $component;
1662      /** @var string the file area name. */
1663      protected $filearea;
1664  
1665      /**
1666       * @var string the value to store in the question_attempt_step_data to
1667       * represent these files.
1668       */
1669      protected $value = null;
1670  
1671      /**
1672       * Constructor.
1673       *
1674       * @param int $draftitemid the draft area to save the files from.
1675       * @param string $component the component for the file area to save into.
1676       * @param string $filearea the name of the file area to save into.
1677       * @param string $text optional content containing file links.
1678       */
1679      public function __construct($draftitemid, $component, $filearea, $text = null) {
1680          $this->draftitemid = $draftitemid;
1681          $this->component = $component;
1682          $this->filearea = $filearea;
1683          $this->value = $this->compute_value($draftitemid, $text);
1684      }
1685  
1686      /**
1687       * Compute the value that should be stored in the question_attempt_step_data table.
1688       *
1689       * Contains a hash that (almost) uniquely encodes all the files.
1690       *
1691       * @param int $draftitemid the draft file area itemid.
1692       * @param string $text optional content containing file links.
1693       * @return string the value.
1694       */
1695      protected function compute_value($draftitemid, $text) {
1696          global $USER;
1697  
1698          $fs = get_file_storage();
1699          $usercontext = context_user::instance($USER->id);
1700  
1701          $files = $fs->get_area_files($usercontext->id, 'user', 'draft',
1702                  $draftitemid, 'sortorder, filepath, filename', false);
1703  
1704          $string = '';
1705          foreach ($files as $file) {
1706              $string .= $file->get_filepath() . $file->get_filename() . '|' .
1707                      $file->get_contenthash() . '|';
1708          }
1709          $hash = md5($string);
1710  
1711          if (is_null($text)) {
1712              if ($string) {
1713                  return $hash;
1714              } else {
1715                  return '';
1716              }
1717          }
1718  
1719          // We add the file hash so a simple string comparison will say if the
1720          // files have been changed. First strip off any existing file hash.
1721          if ($text !== '') {
1722              $text = preg_replace('/\s*<!-- File hash: \w+ -->\s*$/', '', $text);
1723              $text = file_rewrite_urls_to_pluginfile($text, $draftitemid);
1724              if ($string) {
1725                  $text .= '<!-- File hash: ' . $hash . ' -->';
1726              }
1727          }
1728          return $text;
1729      }
1730  
1731      public function __toString() {
1732          return $this->value;
1733      }
1734  
1735      /**
1736       * Actually save the files.
1737       *
1738       * @param integer $itemid the item id for the file area to save into.
1739       * @param context $context the context where the files should be saved.
1740       */
1741      public function save_files($itemid, $context) {
1742          file_save_draft_area_files($this->draftitemid, $context->id,
1743                  $this->component, $this->filearea, $itemid);
1744      }
1745  
1746      /**
1747       * Get the files that were submitted.
1748       * @return array of stored_files objects.
1749       */
1750      public function get_files() {
1751          global $USER;
1752  
1753          $fs = get_file_storage();
1754          $usercontext = context_user::instance($USER->id);
1755  
1756          return $fs->get_area_files($usercontext->id, 'user', 'draft',
1757                  $this->draftitemid, 'sortorder, filepath, filename', false);
1758      }
1759  }
1760  
1761  
1762  /**
1763   * This class is the mirror image of {@link question_file_saver}. It allows
1764   * files to be accessed again later (e.g. when re-grading) using that same
1765   * API as when doing the original grading.
1766   *
1767   * @copyright  2012 The Open University
1768   * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
1769   */
1770  class question_file_loader implements question_response_files {
1771      /** @var question_attempt_step the step that these files belong to. */
1772      protected $step;
1773  
1774      /** @var string the field name for these files - which is used to construct the file area name. */
1775      protected $name;
1776  
1777      /**
1778       * @var string the value to stored in the question_attempt_step_data to
1779       * represent these files.
1780       */
1781      protected $value;
1782  
1783      /** @var int the context id that the files belong to. */
1784      protected $contextid;
1785  
1786      /**
1787       * Constuctor.
1788       * @param question_attempt_step $step the step that these files belong to.
1789       * @param string $name string the field name for these files - which is used to construct the file area name.
1790       * @param string $value the value to stored in the question_attempt_step_data to
1791       *      represent these files.
1792       * @param int $contextid the context id that the files belong to.
1793       */
1794      public function __construct(question_attempt_step $step, $name, $value, $contextid) {
1795          $this->step = $step;
1796          $this->name = $name;
1797          $this->value = $value;
1798          $this->contextid = $contextid;
1799      }
1800  
1801      public function __toString() {
1802          return $this->value;
1803      }
1804  
1805      /**
1806       * Get the files that were submitted.
1807       * @return array of stored_files objects.
1808       */
1809      public function get_files() {
1810          return $this->step->get_qt_files($this->name, $this->contextid);
1811      }
1812  
1813      /**
1814       * Copy these files into a draft area, and return the corresponding
1815       * {@link question_file_saver} that can save them again.
1816       *
1817       * This is used by {@link question_attempt::start_based_on()}, which is used
1818       * (for example) by the quizzes 'Each attempt builds on last' feature.
1819       *
1820       * @return question_file_saver that can re-save these files again.
1821       */
1822      public function get_question_file_saver() {
1823  
1824          // There are three possibilities here for what $value will look like:
1825          // 1) some HTML content followed by an MD5 hash in a HTML comment;
1826          // 2) a plain MD5 hash;
1827          // 3) or some real content, without any hash.
1828          // The problem is that 3) is ambiguous in the case where a student writes
1829          // a response that looks exactly like an MD5 hash. For attempts made now,
1830          // we avoid case 3) by always going for case 1) or 2) (except when the
1831          // response is blank. However, there may be case 3) data in the database
1832          // so we need to handle it as best we can.
1833          if (preg_match('/\s*<!-- File hash: [0-9a-zA-Z]{32} -->\s*$/', $this->value)) {
1834              $value = preg_replace('/\s*<!-- File hash: [0-9a-zA-Z]{32} -->\s*$/', '', $this->value);
1835  
1836          } else if (preg_match('/^[0-9a-zA-Z]{32}$/', $this->value)) {
1837              $value = null;
1838  
1839          } else {
1840              $value = $this->value;
1841          }
1842  
1843          list($draftid, $text) = $this->step->prepare_response_files_draft_itemid_with_text(
1844                  $this->name, $this->contextid, $value);
1845          return new question_file_saver($draftid, 'question', 'response_' . $this->name, $text);
1846      }
1847  }
1848  
1849  
1850  /**
1851   * This class represents a restriction on the set of question_usage ids to include
1852   * in a larger database query. Depending of the how you are going to restrict the
1853   * list of usages, construct an appropriate subclass.
1854   *
1855   * If $qubaids is an instance of this class, example usage might be
1856   *
1857   * SELECT qa.id, qa.maxmark
1858   * FROM $qubaids->from_question_attempts('qa')
1859   * WHERE $qubaids->where() AND qa.slot = 1
1860   *
1861   * @copyright  2010 The Open University
1862   * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
1863   */
1864  abstract class qubaid_condition {
1865  
1866      /**
1867       * Get the SQL fragment to go in a FROM clause.
1868       *
1869       * The SQL that needs to go in the FROM clause when trying
1870       * to select records from the 'question_attempts' table based on this
1871       * qubaid_condition.
1872       *
1873       * @param string $alias
1874       * @return string SQL fragment.
1875       */
1876      public abstract function from_question_attempts($alias);
1877  
1878      /** @return string the SQL that needs to go in the where clause. */
1879      public abstract function where();
1880  
1881      /**
1882       * @return array the params needed by a query that uses
1883       * {@link from_question_attempts()} and {@link where()}.
1884       */
1885      public abstract function from_where_params();
1886  
1887      /**
1888       * @return string SQL that can use used in a WHERE qubaid IN (...) query.
1889       * This method returns the "IN (...)" part.
1890       */
1891      public abstract function usage_id_in();
1892  
1893      /**
1894       * @return array the params needed by a query that uses {@link usage_id_in()}.
1895       */
1896      public abstract function usage_id_in_params();
1897  
1898      /**
1899       * @return string 40-character hash code that uniquely identifies the combination of properties and class name of this qubaid
1900       *                  condition.
1901       */
1902      public function get_hash_code() {
1903          return sha1(serialize($this));
1904      }
1905  }
1906  
1907  
1908  /**
1909   * This class represents a restriction on the set of question_usage ids to include
1910   * in a larger database query based on an explicit list of ids.
1911   *
1912   * @copyright  2010 The Open University
1913   * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
1914   */
1915  class qubaid_list extends qubaid_condition {
1916      /** @var array of ids. */
1917      protected $qubaids;
1918      protected $columntotest = null;
1919      protected $params;
1920  
1921      /**
1922       * Constructor.
1923       * @param array $qubaids of question usage ids.
1924       */
1925      public function __construct(array $qubaids) {
1926          $this->qubaids = $qubaids;
1927      }
1928  
1929      public function from_question_attempts($alias) {
1930          $this->columntotest = $alias . '.questionusageid';
1931          return '{question_attempts} ' . $alias;
1932      }
1933  
1934      public function where() {
1935          if (is_null($this->columntotest)) {
1936              throw new coding_exception('Must call from_question_attempts before where().');
1937          }
1938          if (empty($this->qubaids)) {
1939              $this->params = array();
1940              return '1 = 0';
1941          }
1942  
1943          return $this->columntotest . ' ' . $this->usage_id_in();
1944      }
1945  
1946      public function from_where_params() {
1947          return $this->params;
1948      }
1949  
1950      public function usage_id_in() {
1951          global $DB;
1952  
1953          if (empty($this->qubaids)) {
1954              $this->params = array();
1955              return '= 0';
1956          }
1957          list($where, $this->params) = $DB->get_in_or_equal(
1958                  $this->qubaids, SQL_PARAMS_NAMED, 'qubaid');
1959          return $where;
1960      }
1961  
1962      public function usage_id_in_params() {
1963          return $this->params;
1964      }
1965  }
1966  
1967  
1968  /**
1969   * This class represents a restriction on the set of question_usage ids to include
1970   * in a larger database query based on JOINing to some other tables.
1971   *
1972   * The general form of the query is something like
1973   *
1974   * SELECT qa.id, qa.maxmark
1975   * FROM $from
1976   * JOIN {question_attempts} qa ON qa.questionusageid = $usageidcolumn
1977   * WHERE $where AND qa.slot = 1
1978   *
1979   * where $from, $usageidcolumn and $where are the arguments to the constructor.
1980   *
1981   * @copyright  2010 The Open University
1982   * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
1983   */
1984  class qubaid_join extends qubaid_condition {
1985      public $from;
1986      public $usageidcolumn;
1987      public $where;
1988      public $params;
1989  
1990      /**
1991       * Constructor. The meaning of the arguments is explained in the class comment.
1992       * @param string $from SQL fragemnt to go in the FROM clause.
1993       * @param string $usageidcolumn the column in $from that should be
1994       * made equal to the usageid column in the JOIN clause.
1995       * @param string $where SQL fragment to go in the where clause.
1996       * @param array $params required by the SQL. You must use named parameters.
1997       */
1998      public function __construct($from, $usageidcolumn, $where = '', $params = array()) {
1999          $this->from = $from;
2000          $this->usageidcolumn = $usageidcolumn;
2001          $this->params = $params;
2002          if (empty($where)) {
2003              $where = '1 = 1';
2004          }
2005          $this->where = $where;
2006      }
2007  
2008      public function from_question_attempts($alias) {
2009          return "{$this->from}
2010                  JOIN {question_attempts} {$alias} ON " .
2011                          "{$alias}.questionusageid = $this->usageidcolumn";
2012      }
2013  
2014      public function where() {
2015          return $this->where;
2016      }
2017  
2018      public function from_where_params() {
2019          return $this->params;
2020      }
2021  
2022      public function usage_id_in() {
2023          return "IN (SELECT {$this->usageidcolumn} FROM {$this->from} WHERE {$this->where})";
2024      }
2025  
2026      public function usage_id_in_params() {
2027          return $this->params;
2028      }
2029  }