[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  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       * Generate suitable SQL for the table
 153       *
 154       * @return string
 155       */
 156      protected function get_table_sql(): string {
 157          $sql = "SELECT {$this->sql->fields} FROM {$this->sql->from} WHERE {$this->sql->where} {$this->groupbysql}";
 158  
 159          $sort = $this->get_sql_sort();
 160          if ($sort) {
 161              $sql .= " ORDER BY {$sort}";
 162          }
 163  
 164          return $sql;
 165      }
 166  
 167      /**
 168       * Override parent method of the same, to make use of a recordset and avoid issues with duplicate values in the first column
 169       *
 170       * @param int $pagesize
 171       * @param bool $useinitialsbar
 172       */
 173      public function query_db($pagesize, $useinitialsbar = true): void {
 174          global $DB;
 175  
 176          if (!$this->is_downloading()) {
 177              $this->pagesize($pagesize, $DB->count_records_sql($this->countsql, $this->countparams));
 178  
 179              $this->rawdata = $DB->get_recordset_sql($this->get_table_sql(), $this->sql->params, $this->get_page_start(),
 180                  $this->get_page_size());
 181          } else {
 182              $this->rawdata = $DB->get_recordset_sql($this->get_table_sql(), $this->sql->params);
 183          }
 184      }
 185  
 186      /**
 187       * Override parent method of the same, to ensure that any columns with custom sort fields are accounted for
 188       *
 189       * @return string
 190       */
 191      public function get_sql_sort() {
 192          $columnsbyalias = $this->report->get_active_columns_by_alias();
 193          $columnsortby = [];
 194  
 195          // Iterate over all sorted report columns, replace with columns own fields if applicable.
 196          foreach ($this->get_sort_columns() as $alias => $order) {
 197              $column = $columnsbyalias[$alias] ?? null;
 198  
 199              // If the column is not being aggregated and defines custom sort fields, then use them.
 200              if ($column && !$column->get_aggregation() &&
 201                      ($sortfields = $column->get_sort_fields())) {
 202  
 203                  foreach ($sortfields as $sortfield) {
 204                      $columnsortby[$sortfield] = $order;
 205                  }
 206              } else {
 207                  $columnsortby[$alias] = $order;
 208              }
 209          }
 210  
 211          return static::construct_order_by($columnsortby);
 212      }
 213  
 214      /**
 215       * Get the context for the table (that of the report persistent)
 216       *
 217       * @return context
 218       */
 219      public function get_context(): context {
 220          return $this->persistent->get_context();
 221      }
 222  
 223      /**
 224       * Set the base URL of the table to the current page URL
 225       */
 226      public function guess_base_url(): void {
 227          $this->baseurl = new moodle_url('/');
 228      }
 229  
 230      /**
 231       * Override print_nothing_to_display to modity the output styles.
 232       */
 233      public function print_nothing_to_display() {
 234          global $OUTPUT;
 235  
 236          echo $this->get_dynamic_table_html_start();
 237          echo $this->render_reset_button();
 238  
 239          echo $OUTPUT->render(new notification(get_string('nothingtodisplay'), notification::NOTIFY_INFO, false));
 240  
 241          echo $this->get_dynamic_table_html_end();
 242      }
 243  
 244      /**
 245       * Override start of HTML to remove top pagination.
 246       */
 247      public function start_html() {
 248          // Render the dynamic table header.
 249          echo $this->get_dynamic_table_html_start();
 250  
 251          // Render button to allow user to reset table preferences.
 252          echo $this->render_reset_button();
 253  
 254          $this->wrap_html_start();
 255  
 256          echo html_writer::start_tag('div');
 257          echo html_writer::start_tag('table', $this->attributes);
 258      }
 259  }