[Summary view]

Differences Between: [Versions 400 and 401] [Versions 400 and 402] [Versions 400 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  declare(strict_types=1);
  18  
  19  namespace core_reportbuilder\table;
  20  
  21  use context;
  22  use moodle_url;
  23  use renderable;
  24  use table_sql;
  25  use html_writer;
  26  use core_table\dynamic;
  27  use core_reportbuilder\local\helpers\database;
  28  use core_reportbuilder\local\filters\base;
  29  use core_reportbuilder\local\models\report;
  30  use core_reportbuilder\local\report\base as base_report;
  31  use core_reportbuilder\local\report\filter;
  32  use core\output\notification;
  33  
  34  defined('MOODLE_INTERNAL') || die;
  35  
  36  require_once("{$CFG->libdir}/tablelib.php");
  37  
  38  /**
  39   * Base report dynamic table class
  40   *
  41   * @package     core_reportbuilder
  42   * @copyright   2021 David Matamoros <davidmc@moodle.com>
  43   * @license     http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
  44   */
  45  abstract class base_report_table extends table_sql implements dynamic, renderable {
  46  
  47      /** @var report $persistent */
  48      protected $persistent;
  49  
  50      /** @var base_report $report */
  51      protected $report;
  52  
  53      /** @var string $groupbysql */
  54      protected $groupbysql = '';
  55  
  56      /** @var bool $editing */
  57      protected $editing = false;
  58  
  59      /**
  60       * Initialises table SQL properties
  61       *
  62       * @param string $fields
  63       * @param string $from
  64       * @param array $joins
  65       * @param string $where
  66       * @param array $params
  67       * @param array $groupby
  68       */
  69      protected function init_sql(string $fields, string $from, array $joins, string $where, array $params,
  70              array $groupby = []): void {
  71  
  72          $wheres = [];
  73          if ($where !== '') {
  74              $wheres[] = $where;
  75          }
  76  
  77          // For each condition, we need to ensure their values are always accounted for in the report.
  78          $conditionvalues = $this->report->get_condition_values();
  79          foreach ($this->report->get_active_conditions() as $condition) {
  80              [$conditionsql, $conditionparams] = $this->get_filter_sql($condition, $conditionvalues);
  81              if ($conditionsql !== '') {
  82                  $joins = array_merge($joins, $condition->get_joins());
  83                  $wheres[] = "({$conditionsql})";
  84                  $params = array_merge($params, $conditionparams);
  85              }
  86          }
  87  
  88          // For each filter, we also need to apply their values (will differ according to user viewing the report).
  89          if (!$this->editing) {
  90              $filtervalues = $this->report->get_filter_values();
  91              foreach ($this->report->get_active_filters() as $filter) {
  92                  [$filtersql, $filterparams] = $this->get_filter_sql($filter, $filtervalues);
  93                  if ($filtersql !== '') {
  94                      $joins = array_merge($joins, $filter->get_joins());
  95                      $wheres[] = "({$filtersql})";
  96                      $params = array_merge($params, $filterparams);
  97                  }
  98              }
  99          }
 100  
 101          // Join all the filters into a SQL WHERE clause, falling back to all records.
 102          if (!empty($wheres)) {
 103              $wheresql = implode(' AND ', $wheres);
 104          } else {
 105              $wheresql = '1=1';
 106          }
 107  
 108          if (!empty($groupby)) {
 109              $this->groupbysql = 'GROUP BY ' . implode(', ', $groupby);
 110          }
 111  
 112          // Add unique table joins.
 113          $from .= ' ' . implode(' ', array_unique($joins));
 114  
 115          $this->set_sql($fields, $from, $wheresql, $params);
 116  
 117          $counttablealias = database::generate_alias();
 118          $this->set_count_sql("
 119              SELECT COUNT(1)
 120                FROM (SELECT {$fields}
 121                        FROM {$from}
 122                       WHERE {$wheresql}
 123                             {$this->groupbysql}
 124                     ) {$counttablealias}", $params);
 125      }
 126  
 127      /**
 128       * Whether the current report table is being edited, in which case certain actions are not applied to it, e.g. user filtering
 129       * and sorting. Default class value is false
 130       *
 131       * @param bool $editing
 132       */
 133      public function set_report_editing(bool $editing): void {
 134          $this->editing = $editing;
 135      }
 136  
 137      /**
 138       * Return SQL fragments from given filter instance suitable for inclusion in table SQL
 139       *
 140       * @param filter $filter
 141       * @param array $filtervalues
 142       * @return array [$sql, $params]
 143       */
 144      private function get_filter_sql(filter $filter, array $filtervalues): array {
 145          /** @var base $filterclass */
 146          $filterclass = $filter->get_filter_class();
 147  
 148          return $filterclass::create($filter)->get_sql_filter($filtervalues);
 149      }
 150  
 151      /**
 152       * Override parent method of the same, to make use of a recordset and avoid issues with duplicate values in the first column
 153       *
 154       * @param int $pagesize
 155       * @param bool $useinitialsbar
 156       */
 157      public function query_db($pagesize, $useinitialsbar = true) {
 158          global $DB;
 159  
 160          $sql = "SELECT {$this->sql->fields} FROM {$this->sql->from} WHERE {$this->sql->where} {$this->groupbysql}";
 161  
 162          $sort = $this->get_sql_sort();
 163          if ($sort) {
 164              $sql .= " ORDER BY {$sort}";
 165          }
 166  
 167          if (!$this->is_downloading()) {
 168              $this->pagesize($pagesize, $DB->count_records_sql($this->countsql, $this->countparams));
 169  
 170              $this->rawdata = $DB->get_recordset_sql($sql, $this->sql->params, $this->get_page_start(), $this->get_page_size());
 171          } else {
 172              $this->rawdata = $DB->get_recordset_sql($sql, $this->sql->params);
 173          }
 174      }
 175  
 176      /**
 177       * Override parent method of the same, to ensure that any columns with custom sort fields are accounted for
 178       *
 179       * @return string
 180       */
 181      public function get_sql_sort() {
 182          $columnsbyalias = $this->report->get_active_columns_by_alias();
 183          $columnsortby = [];
 184  
 185          // Iterate over all sorted report columns, replace with columns own fields if applicable.
 186          foreach ($this->get_sort_columns() as $alias => $order) {
 187              $column = $columnsbyalias[$alias] ?? null;
 188  
 189              // If the column is not being aggregated and defines custom sort fields, then use them.
 190              if ($column && !$column->get_aggregation() &&
 191                      ($sortfields = $column->get_sort_fields())) {
 192  
 193                  foreach ($sortfields as $sortfield) {
 194                      $columnsortby[$sortfield] = $order;
 195                  }
 196              } else {
 197                  $columnsortby[$alias] = $order;
 198              }
 199          }
 200  
 201          return static::construct_order_by($columnsortby);
 202      }
 203  
 204      /**
 205       * Get the context for the table (that of the report persistent)
 206       *
 207       * @return context
 208       */
 209      public function get_context(): context {
 210          return $this->persistent->get_context();
 211      }
 212  
 213      /**
 214       * Set the base URL of the table to the current page URL
 215       */
 216      public function guess_base_url(): void {
 217          $this->baseurl = new moodle_url('/');
 218      }
 219  
 220      /**
 221       * Override print_nothing_to_display to modity the output styles.
 222       */
 223      public function print_nothing_to_display() {
 224          global $OUTPUT;
 225  
 226          echo $this->get_dynamic_table_html_start();
 227          echo $this->render_reset_button();
 228  
 229          echo $OUTPUT->render(new notification(get_string('nothingtodisplay'), notification::NOTIFY_INFO, false));
 230  
 231          echo $this->get_dynamic_table_html_end();
 232      }
 233  
 234      /**
 235       * Override start of HTML to remove top pagination.
 236       */
 237      public function start_html() {
 238          // Render the dynamic table header.
 239          echo $this->get_dynamic_table_html_start();
 240  
 241          // Render button to allow user to reset table preferences.
 242          echo $this->render_reset_button();
 243  
 244          $this->wrap_html_start();
 245  
 246          echo html_writer::start_tag('div');
 247          echo html_writer::start_tag('table', $this->attributes);
 248      }
 249  }