Search moodle.org's
Developer Documentation

  • 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 37 and 311] [Versions 38 and 311]

       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  }