report/questioninstances/index.php

   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  * For a given question type, list the number of
  19  *
  20  * @package    report
  21  * @subpackage questioninstances
  22  * @copyright  2008 Tim Hunt
  23  * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
  24  */
  25
  26 require(__DIR__.'/../../config.php');
  27 require_once($CFG->libdir.'/adminlib.php');
  28 require_once($CFG->libdir.'/questionlib.php');
  29
  30 // Get URL parameters.
  31 $requestedqtype = optional_param('qtype', '', PARAM_SAFEDIR);
  32
  33 // Print the header & check permissions.
  34 admin_externalpage_setup('reportquestioninstances', '', null, '', array('pagelayout'=>'report'));
  35 $PAGE->set_primary_active_tab('siteadminnode');
  36 echo $OUTPUT->header();
  37
  38 // Log.
  39 \report_questioninstances\event\report_viewed::create(array('other' => array('requestedqtype' => $requestedqtype)))->trigger();
  40
  41 // Prepare the list of capabilities to choose from
  42 $qtypes = question_bank::get_all_qtypes();
  43 $qtypechoices = array();
  44 foreach ($qtypes as $qtype) {
  45     $qtypechoices[$qtype->name()] = $qtype->local_name();
  46 }
  47
  48 // Print the settings form.
  49 echo $OUTPUT->box_start('generalbox boxwidthwide boxaligncenter centerpara');
  50 echo '<form method="get" action="." id="settingsform"><div>';
  51 echo $OUTPUT->heading(get_string('reportsettings', 'report_questioninstances'));
  52 echo '<p id="intro">', get_string('intro', 'report_questioninstances') , '</p>';
  53 echo '<p><label for="menuqtype"> ' . get_string('questiontype', 'admin') . '</label> ';
  54 echo html_writer::select($qtypechoices, 'qtype', $requestedqtype, array('_all_'=>get_string('all')));
  55 echo '</p>';
  56 echo '<p><input type="submit" class="btn btn-secondary" id="settingssubmit" value="' .
  57         get_string('getreport', 'report_questioninstances') . '" /></p>';
  58 echo '</div></form>';
  59 echo $OUTPUT->box_end();
  60
  61 $params[] = \core_question\local\bank\question_version_status::QUESTION_STATUS_HIDDEN;
  62 // If we have a qtype to report on, generate the report.
  63 if ($requestedqtype) {
  64
  65     // Work out the bits needed for the SQL WHERE clauses.
  66     if ($requestedqtype == 'missingtype') {
  67         $title = get_string('reportformissingqtypes', 'report_questioninstances');
  68
  69         $othertypes = array_keys($qtypes);
  70         $key = array_search('missingtype', $othertypes);
  71         unset($othertypes[$key]);
  72         list($sqlqtypetest, $params) = $DB->get_in_or_equal($othertypes, SQL_PARAMS_QM, '', false);
  73         $sqlqtypetest = 'WHERE qtype ' . $sqlqtypetest;
  74
  75     } else if ($requestedqtype == '_all_') {
  76         $title = get_string('reportforallqtypes', 'report_questioninstances');
  77
  78         $sqlqtypetest = '';
  79
  80     } else {
  81         $title = get_string('reportforqtype', 'report_questioninstances',
  82                 question_bank::get_qtype($requestedqtype)->local_name());
  83
  84         $sqlqtypetest = 'WHERE qtype = ?';
  85         $params [] = $requestedqtype;
  86     }
  87
  88     // Get the question counts, and all the context information, for each
  89     // context. That is, rows of these results can be used as $context objects.
  90     $ctxpreload = context_helper::get_preload_record_columns_sql('con');
  91     $ctxgroupby = implode(',', array_keys(context_helper::get_preload_record_columns('con')));
  92     $counts = $DB->get_records_sql("
  93             SELECT result.contextid, SUM(numquestions) AS numquestions, SUM(numhidden) AS numhidden, $ctxpreload
  94               FROM (SELECT data.contextid, data.versionid, COUNT(data.numquestions) AS numquestions,
  95                            (SELECT COUNT(qv.id)
  96                               FROM {question_versions} qv
  97                              WHERE qv.id = data.versionid
  98                                    AND qv.status = ?) AS numhidden
  99                       FROM (SELECT qv.id as versionid, qc.contextid, 1 AS numquestions
 100                               FROM {question} q
 101                               JOIN {question_versions} qv ON qv.questionid = q.id
 102                               JOIN {question_bank_entries} qbe ON qbe.id = qv.questionbankentryid
 103                               JOIN {question_categories} qc ON qc.id = qbe.questioncategoryid
 104                               JOIN {context} con ON con.id = qc.contextid
 105                               $sqlqtypetest
 106                                    AND qv.version = (SELECT MAX(v.version)
 107                                                        FROM {question_versions} v
 108                                                        JOIN {question_bank_entries} be
 109                                                          ON be.id = v.questionbankentryid
 110                                                       WHERE be.id = qbe.id)
 111                                    AND (q.parent = 0 OR q.parent = q.id)) data
 112                   GROUP BY data.contextid, data.versionid) result
 113               JOIN {context} con ON con.id = result.contextid
 114           GROUP BY result.contextid, $ctxgroupby
 115           ORDER BY numquestions DESC, numhidden ASC, con.contextlevel ASC, con.id ASC", $params);
 116
 117     // Print the report heading.
 118     echo $OUTPUT->heading($title);
 119
 120     // Initialise the table.
 121     $table = new html_table();
 122     $table->head = array(
 123             get_string('context', 'role'),
 124             get_string('totalquestions', 'report_questioninstances'),
 125             get_string('visiblequestions', 'report_questioninstances'),
 126             get_string('hiddenquestions', 'report_questioninstances'));
 127     $table->data = array();
 128     $table->class = '';
 129     $table->id = '';
 130
 131     // Add the data for each row.
 132     $totalquestions = 0;
 133     $totalvisible = 0;
 134     $totalhidden = 0;
 135     foreach ($counts as $count) {
 136         // Work out a link for editing questions in this context.
 137         context_helper::preload_from_record($count);
 138         $context = context::instance_by_id($count->contextid);
 139         $contextname = $context->get_context_name();
 140         $url = question_edit_url($context);
 141         if ($url) {
 142             $contextname = '<a href="' . $url . '" title="' .
 143                     get_string('editquestionshere', 'report_questioninstances') .
 144                     '">' . $contextname . '</a>';
 145         }
 146
 147         // Put the scores in the table.
 148         $numvisible = $count->numquestions - $count->numhidden;
 149         $table->data[] = array(
 150                 $contextname,
 151                 $count->numquestions,
 152                 $numvisible,
 153                 $count->numhidden);
 154
 155         // Update the totals.
 156         $totalquestions += $count->numquestions;
 157         $totalvisible += $numvisible;
 158         $totalhidden += $count->numhidden;
 159     }
 160
 161     // Add a totals row.
 162     $table->data[] = array(
 163             '<b>' . get_string('total') . '</b>',
 164             $totalquestions,
 165             $totalvisible,
 166             $totalhidden);
 167
 168     // Print it.
 169     echo html_writer::table($table);
 170 }
 171
 172 // Footer.
 173 echo $OUTPUT->footer();