[Summary view]

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

   1  <?php
   2  // This file is part of Moodle - http://moodle.org/
   3  //
   4  // Moodle is free software: you can redistribute it and/or modify
   5  // it under the terms of the GNU General Public License as published by
   6  // the Free Software Foundation, either version 3 of the License, or
   7  // (at your option) any later version.
   8  //
   9  // Moodle is distributed in the hope that it will be useful,
  10  // but WITHOUT ANY WARRANTY; without even the implied warranty of
  11  // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  12  // GNU General Public License for more details.
  13  //
  14  // You should have received a copy of the GNU General Public License
  15  // along with Moodle.  If not, see <http://www.gnu.org/licenses/>.
  16  
  17  namespace qbank_usage;
  18  
  19  /**
  20   * Helper class for usage.
  21   *
  22   * @package    qbank_usage
  23   * @copyright  2021 Catalyst IT Australia Pty Ltd
  24   * @author     Safat Shahin <safatshahin@catalyst-au.net>
  25   * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
  26   */
  27  class helper {
  28  
  29      /**
  30       * Get the usage count for a question.
  31       *
  32       * @param \question_definition $question
  33       * @param bool $specificversion Count usages just for this version of the question?
  34       * @return int
  35       */
  36      public static function get_question_entry_usage_count($question, bool $specificversion = false) {
  37          global $DB;
  38  
  39          $sql = 'SELECT COUNT(*) FROM (' . self::question_usage_sql($specificversion) . ') quizid';
  40  
  41          $params = [$question->id, $question->questionbankentryid, 'mod_quiz', 'slot'];
  42          if ($specificversion) {
  43              $params[] = $question->id;
  44          }
  45  
  46          return $DB->count_records_sql($sql, $params);
  47      }
  48  
  49      /**
  50       * Get the sql for usage data.
  51       *
  52       * @param bool $specificversion Count usages just for this version of the question?
  53       * @return string
  54       */
  55      public static function question_usage_sql(bool $specificversion = false): string {
  56          $sqlset = "(". self::get_question_attempt_usage_sql($specificversion) .")".
  57              "UNION".
  58              "(". self::get_question_bank_usage_sql($specificversion) .")";
  59          return $sqlset;
  60      }
  61  
  62      /**
  63       * Get question attempt count for the question.
  64       *
  65       * @param int $questionid
  66       * @param int $quizid
  67       * @return int
  68       */
  69      public static function get_question_attempts_count_in_quiz(int $questionid, $quizid = null): int {
  70          global $DB;
  71          if ($quizid) {
  72              $sql = 'SELECT COUNT(qatt.id)
  73                        FROM {quiz} qz
  74                        JOIN {quiz_attempts} qa ON qa.quiz = qz.id
  75                        JOIN {question_usages} qu ON qu.id = qa.uniqueid
  76                        JOIN {question_attempts} qatt ON qatt.questionusageid = qu.id
  77                        JOIN {question} q ON q.id = qatt.questionid
  78                       WHERE qatt.questionid = :questionid
  79                         AND qa.preview = 0
  80                         AND qz.id = :quizid';
  81              $param = ['questionid' => $questionid, 'quizid' => $quizid];
  82          } else {
  83              $sql = 'SELECT COUNT(qatt.id)
  84                        FROM {quiz_slots} qs
  85                        JOIN {quiz_attempts} qa ON qa.quiz = qs.quizid
  86                        JOIN {question_usages} qu ON qu.id = qa.uniqueid
  87                        JOIN {question_attempts} qatt ON qatt.questionusageid = qu.id
  88                        JOIN {question} q ON q.id = qatt.questionid
  89                       WHERE qatt.questionid = ?
  90                         AND qa.preview = 0';
  91              $param = ['questionid' => $questionid];
  92          }
  93          return $DB->count_records_sql($sql, $param);
  94      }
  95  
  96      /**
  97       * Get the question bank usage sql.
  98       *
  99       * The resulting string which represents a sql query has then to be
 100       * called accompanying a $params array which includes the necessary
 101       * parameters in the correct order which are the question id, then
 102       * the component and finally the question area.
 103       *
 104       * @param bool $specificversion Count usages just for this version of the question?
 105       * @return string
 106       */
 107      public static function get_question_bank_usage_sql(bool $specificversion = false): string {
 108          $sql = "SELECT qz.id as quizid,
 109                         qz.name as modulename,
 110                         qz.course as courseid
 111                    FROM {quiz_slots} slot
 112                    JOIN {quiz} qz ON qz.id = slot.quizid
 113                    JOIN {question_references} qr ON qr.itemid = slot.id
 114                    JOIN {question_bank_entries} qbe ON qbe.id = qr.questionbankentryid
 115                    JOIN {question_versions} qv ON qv.questionbankentryid = qbe.id
 116                   WHERE qv.questionbankentryid = ?
 117                     AND qr.component = ?
 118                     AND qr.questionarea = ?";
 119  
 120          if ($specificversion) {
 121              // Only get results where the reference matches the specific question ID that was requested,
 122              // or the question ID that's requested is the latest version, and the reference is set to null (always latest version).
 123              $sql .= " AND qv.questionid = ?
 124                        AND (
 125                            qv.version = qr.version
 126                            OR (
 127                                qr.version IS NULL
 128                                AND qv.version = (
 129                                    SELECT MAX(qv1.version)
 130                                      FROM {question_versions} qv1
 131                                     WHERE qv1.questionbankentryid = qbe.id
 132                                )
 133                            )
 134                        )";
 135          }
 136          return $sql;
 137      }
 138  
 139      /**
 140       * Get the question attempt usage sql.
 141       *
 142       * The resulting string which represents a sql query has then to be
 143       * called accompanying a $params array which includes the necessary
 144       * parameter, the question id.
 145       *
 146       * @param bool $specificversion Count usages just for this version of the question?
 147       * @return string
 148       */
 149      public static function get_question_attempt_usage_sql(bool $specificversion = false): string {
 150          $sql = "SELECT qz.id as quizid,
 151                         qz.name as modulename,
 152                         qz.course as courseid
 153                    FROM {quiz} qz
 154                    JOIN {quiz_attempts} qa ON qa.quiz = qz.id
 155                    JOIN {question_usages} qu ON qu.id = qa.uniqueid
 156                    JOIN {question_attempts} qatt ON qatt.questionusageid = qu.id";
 157          if ($specificversion) {
 158              $sql .= "
 159                    JOIN {question} q ON q.id = qatt.questionid
 160                   WHERE qa.preview = 0
 161                     AND q.id = ?";
 162          } else {
 163              $sql .= "
 164                    JOIN {question_versions} qv ON qv.questionid = qatt.questionid
 165                    JOIN {question_versions} qv2 ON qv.questionbankentryid = qv2.questionbankentryid
 166                   WHERE qa.preview = 0
 167                     AND qv2.questionid = ?";
 168          }
 169          return $sql;
 170      }
 171  
 172  
 173      /**
 174       * Get the question last used sql.
 175       *
 176       * @return string
 177       */
 178      public static function get_question_last_used_sql(): string {
 179          $sql = "SELECT MAX(qa.timemodified) as lastused
 180                    FROM {quiz} qz
 181                    JOIN {quiz_attempts} qa ON qa.quiz = qz.id
 182                    JOIN {question_usages} qu ON qu.id = qa.uniqueid
 183                    JOIN {question_attempts} qatt ON qatt.questionusageid = qu.id
 184                    JOIN {question} q ON q.id = qatt.questionid
 185                   WHERE qa.preview = 0
 186                     AND q.id = ?";
 187          return $sql;
 188      }
 189  
 190  }