[Summary view]

Differences Between: [Versions 400 and 401] [Versions 401 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       * @return int
  34       */
  35      public static function get_question_entry_usage_count($question) {
  36          global $DB;
  37  
  38          $sql = 'SELECT COUNT(*) FROM (' . self::question_usage_sql() . ') quizid';
  39  
  40          return $DB->count_records_sql($sql, [$question->id, $question->questionbankentryid, 'mod_quiz', 'slot']);
  41      }
  42  
  43      /**
  44       * Get the sql for usage data.
  45       *
  46       * @return string
  47       */
  48      public static function question_usage_sql(): string {
  49          $sqlset = "(". self::get_question_attempt_usage_sql() .")".
  50                     "UNION".
  51                     "(". self::get_question_bank_usage_sql() .")";
  52          return $sqlset;
  53      }
  54  
  55      /**
  56       * Get question attempt count for the question.
  57       *
  58       * @param int $questionid
  59       * @param int $quizid
  60       * @return int
  61       */
  62      public static function get_question_attempts_count_in_quiz(int $questionid, $quizid = null): int {
  63          global $DB;
  64          if ($quizid) {
  65              $sql = 'SELECT COUNT(qatt.id)
  66                        FROM {quiz} qz
  67                        JOIN {quiz_attempts} qa ON qa.quiz = qz.id
  68                        JOIN {question_usages} qu ON qu.id = qa.uniqueid
  69                        JOIN {question_attempts} qatt ON qatt.questionusageid = qu.id
  70                        JOIN {question} q ON q.id = qatt.questionid
  71                       WHERE qatt.questionid = :questionid
  72                         AND qa.preview = 0
  73                         AND qz.id = :quizid';
  74              $param = ['questionid' => $questionid, 'quizid' => $quizid];
  75          } else {
  76              $sql = 'SELECT COUNT(qatt.id)
  77                        FROM {quiz_slots} qs
  78                        JOIN {quiz_attempts} qa ON qa.quiz = qs.quizid
  79                        JOIN {question_usages} qu ON qu.id = qa.uniqueid
  80                        JOIN {question_attempts} qatt ON qatt.questionusageid = qu.id
  81                        JOIN {question} q ON q.id = qatt.questionid
  82                       WHERE qatt.questionid = ?
  83                         AND qa.preview = 0';
  84              $param = ['questionid' => $questionid];
  85          }
  86          return $DB->count_records_sql($sql, $param);
  87      }
  88  
  89      /**
  90       * Get the question bank usage sql.
  91       *
  92       * The resulting string which represents a sql query has then to be
  93       * called accompanying a $params array which includes the necessary
  94       * parameters in the correct order which are the question id, then
  95       * the component and finally the question area.
  96       *
  97       * @return string
  98       */
  99      public static function get_question_bank_usage_sql(): string {
 100          $sql = "SELECT qz.id as quizid,
 101                         qz.name as modulename,
 102                         qz.course as courseid
 103                    FROM {quiz_slots} slot
 104                    JOIN {quiz} qz ON qz.id = slot.quizid
 105                    JOIN {question_references} qr ON qr.itemid = slot.id
 106                    JOIN {question_bank_entries} qbe ON qbe.id = qr.questionbankentryid
 107                    JOIN {question_versions} qv ON qv.questionbankentryid = qbe.id
 108                   WHERE qv.questionbankentryid = ?
 109                     AND qr.component = ?
 110                     AND qr.questionarea = ?";
 111          return $sql;
 112      }
 113  
 114      /**
 115       * Get the question attempt usage sql.
 116       *
 117       * The resulting string which represents a sql query has then to be
 118       * called accompanying a $params array which includes the necessary
 119       * parameter, the question id.
 120       *
 121       * @return string
 122       */
 123      public static function get_question_attempt_usage_sql(): string {
 124          $sql = "SELECT qz.id as quizid,
 125                         qz.name as modulename,
 126                         qz.course as courseid
 127                    FROM {quiz} qz
 128                    JOIN {quiz_attempts} qa ON qa.quiz = qz.id
 129                    JOIN {question_usages} qu ON qu.id = qa.uniqueid
 130                    JOIN {question_attempts} qatt ON qatt.questionusageid = qu.id
 131                    JOIN {question} q ON q.id = qatt.questionid
 132                   WHERE qa.preview = 0
 133                     AND q.id = ?";
 134          return $sql;
 135      }
 136  
 137  
 138      /**
 139       * Get the question last used sql.
 140       *
 141       * @return string
 142       */
 143      public static function get_question_last_used_sql(): string {
 144          $sql = "SELECT MAX(qa.timemodified) as lastused
 145                    FROM {quiz} qz
 146                    JOIN {quiz_attempts} qa ON qa.quiz = qz.id
 147                    JOIN {question_usages} qu ON qu.id = qa.uniqueid
 148                    JOIN {question_attempts} qatt ON qatt.questionusageid = qu.id
 149                    JOIN {question} q ON q.id = qatt.questionid
 150                   WHERE qa.preview = 0
 151                     AND q.id = ?";
 152          return $sql;
 153      }
 154  
 155  }