[Summary view]

Differences Between: [Versions 400 and 402] [Versions 401 and 402] [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  declare(strict_types=1);
  18  
  19  namespace core_reportbuilder\local\report;
  20  
  21  use coding_exception;
  22  use lang_string;
  23  use core_reportbuilder\local\helpers\aggregation;
  24  use core_reportbuilder\local\helpers\database;
  25  use core_reportbuilder\local\aggregation\base;
  26  use core_reportbuilder\local\models\column as column_model;
  27  
  28  /**
  29   * Class to represent a report column
  30   *
  31   * @package     core_reportbuilder
  32   * @copyright   2020 Paul Holden <paulh@moodle.com>
  33   * @license     http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
  34   */
  35  final class column {
  36  
  37      /** @var int Column type is integer */
  38      public const TYPE_INTEGER = 1;
  39  
  40      /** @var int Column type is text */
  41      public const TYPE_TEXT = 2;
  42  
  43      /** @var int Column type is timestamp */
  44      public const TYPE_TIMESTAMP = 3;
  45  
  46      /** @var int Column type is boolean */
  47      public const TYPE_BOOLEAN = 4;
  48  
  49      /** @var int Column type is float */
  50      public const TYPE_FLOAT = 5;
  51  
  52      /** @var int Column type is long text */
  53      public const TYPE_LONGTEXT = 6;
  54  
  55      /** @var int $index Column index within a report */
  56      private $index;
  57  
  58      /** @var string $columnname Internal reference to name of column */
  59      private $columnname;
  60  
  61      /** @var lang_string $columntitle Used as a title for the column in reports */
  62      private $columntitle;
  63  
  64      /** @var bool $hascustomcolumntitle Used to store if the column has been given a custom title */
  65      private $hascustomcolumntitle = false;
  66  
  67      /** @var string $entityname Name of the entity this column belongs to */
  68      private $entityname;
  69  
  70      /** @var int $type Column data type (one of the TYPE_* class constants) */
  71      private $type = self::TYPE_TEXT;
  72  
  73      /** @var string[] $joins List of SQL joins for this column */
  74      private $joins = [];
  75  
  76      /** @var array $fields */
  77      private $fields = [];
  78  
  79      /** @var array $params  */
  80      private $params = [];
  81  
  82      /** @var string $groupbysql */
  83      private $groupbysql;
  84  
  85      /** @var array[] $callbacks Array of [callable, additionalarguments] */
  86      private $callbacks = [];
  87  
  88      /** @var base|null $aggregation Aggregation type to apply to column */
  89      private $aggregation = null;
  90  
  91      /** @var array $disabledaggregation Aggregation types explicitly disabled  */
  92      private $disabledaggregation = [];
  93  
  94      /** @var bool $issortable Used to indicate if a column is sortable */
  95      private $issortable = false;
  96  
  97      /** @var array $sortfields Fields to sort the column by */
  98      private $sortfields = [];
  99  
 100      /** @var array $attributes */
 101      private $attributes = [];
 102  
 103      /** @var bool $available Used to know if column is available to the current user or not */
 104      protected $available = true;
 105  
 106      /** @var column_model $persistent */
 107      protected $persistent;
 108  
 109      /**
 110       * Column constructor
 111       *
 112       * For better readability use chainable methods, for example:
 113       *
 114       * $report->add_column(
 115       *    (new column('name', new lang_string('name'), 'user'))
 116       *    ->add_join('left join {table} t on t.id = p.tableid')
 117       *    ->add_field('t.name')
 118       *    ->add_callback([format::class, 'format_string']));
 119       *
 120       * @param string $name Internal name of the column
 121       * @param lang_string|null $title Title of the column used in reports (null for blank)
 122       * @param string $entityname Name of the entity this column belongs to. Typically when creating columns within entities
 123       *      this value should be the result of calling {@see get_entity_name}, however if creating columns inside reports directly
 124       *      it should be the name of the entity as passed to {@see \core_reportbuilder\local\report\base::annotate_entity}
 125       */
 126      public function __construct(string $name, ?lang_string $title, string $entityname) {
 127          $this->columnname = $name;
 128          $this->columntitle = $title;
 129          $this->entityname = $entityname;
 130      }
 131  
 132      /**
 133       * Set column name
 134       *
 135       * @param string $name
 136       * @return self
 137       */
 138      public function set_name(string $name): self {
 139          $this->columnname = $name;
 140          return $this;
 141      }
 142  
 143      /**
 144       * Return column name
 145       *
 146       * @return mixed
 147       */
 148      public function get_name(): string {
 149          return $this->columnname;
 150      }
 151  
 152      /**
 153       * Set column title
 154       *
 155       * @param lang_string|null $title
 156       * @return self
 157       */
 158      public function set_title(?lang_string $title): self {
 159          $this->columntitle = $title;
 160          $this->hascustomcolumntitle = true;
 161          return $this;
 162      }
 163  
 164      /**
 165       * Return column title
 166       *
 167       * @return string
 168       */
 169      public function get_title(): string {
 170          return $this->columntitle ? (string) $this->columntitle : '';
 171      }
 172  
 173      /**
 174       * Check whether this column has been given a custom title
 175       *
 176       * @return bool
 177       */
 178      public function has_custom_title(): bool {
 179          return $this->hascustomcolumntitle;
 180      }
 181  
 182      /**
 183       * Get column entity name
 184       *
 185       * @return string
 186       */
 187      public function get_entity_name(): string {
 188          return $this->entityname;
 189      }
 190  
 191  
 192      /**
 193       * Return unique identifier for this column
 194       *
 195       * @return string
 196       */
 197      public function get_unique_identifier(): string {
 198          return $this->get_entity_name() . ':' . $this->get_name();
 199      }
 200  
 201      /**
 202       * Set the column index within the current report
 203       *
 204       * @param int $index
 205       * @return self
 206       */
 207      public function set_index(int $index): self {
 208          $this->index = $index;
 209          return $this;
 210      }
 211  
 212      /**
 213       * Set the column type, if not called then the type will be assumed to be {@see TYPE_TEXT}
 214       *
 215       * The type of a column is used to cast the first column field passed to any callbacks {@see add_callback} as well as the
 216       * aggregation options available for the column
 217       *
 218       * @param int $type
 219       * @return self
 220       * @throws coding_exception
 221       */
 222      public function set_type(int $type): self {
 223          $allowedtypes = [
 224              self::TYPE_INTEGER,
 225              self::TYPE_TEXT,
 226              self::TYPE_TIMESTAMP,
 227              self::TYPE_BOOLEAN,
 228              self::TYPE_FLOAT,
 229              self::TYPE_LONGTEXT,
 230          ];
 231          if (!in_array($type, $allowedtypes)) {
 232              throw new coding_exception('Invalid column type', $type);
 233          }
 234  
 235          $this->type = $type;
 236          return $this;
 237      }
 238  
 239      /**
 240       * Return column type, that being one of the TYPE_* class constants
 241       *
 242       * @return int
 243       */
 244      public function get_type(): int {
 245          return $this->type;
 246      }
 247  
 248      /**
 249       * Add join clause required for this column to join to existing tables/entities
 250       *
 251       * This is necessary in the case where {@see add_field} is selecting data from a table that isn't otherwise queried
 252       *
 253       * @param string $join
 254       * @return self
 255       */
 256      public function add_join(string $join): self {
 257          $this->joins[trim($join)] = trim($join);
 258          return $this;
 259      }
 260  
 261      /**
 262       * Add multiple join clauses required for this column, passing each to {@see add_join}
 263       *
 264       * Typically when defining columns in entities, you should pass {@see \core_reportbuilder\local\report\base::get_joins} to
 265       * this method, so that all entity joins are included in the report when your column is added to it
 266       *
 267       * @param string[] $joins
 268       * @return self
 269       */
 270      public function add_joins(array $joins): self {
 271          foreach ($joins as $join) {
 272              $this->add_join($join);
 273          }
 274          return $this;
 275      }
 276  
 277      /**
 278       * Return column joins
 279       *
 280       * @return string[]
 281       */
 282      public function get_joins(): array {
 283          return array_values($this->joins);
 284      }
 285  
 286      /**
 287       * Adds a field to be queried from the database that is necessary for this column
 288       *
 289       * Multiple fields can be added per column, this method may be called several times. Field aliases must be unique inside
 290       * any given column, but there will be no conflicts if the same aliases are used in other columns in the same report
 291       *
 292       * @param string $sql SQL query, this may be a simple "tablealias.fieldname" or a complex sub-query that returns only one field
 293       * @param string $alias
 294       * @param array $params
 295       * @return self
 296       * @throws coding_exception
 297       */
 298      public function add_field(string $sql, string $alias = '', array $params = []): self {
 299          database::validate_params($params);
 300  
 301          // SQL ends with a space and a word - this looks like an alias was passed as part of the field.
 302          if (preg_match('/ \w+$/', $sql) && empty($alias)) {
 303              throw new coding_exception('Column alias must be passed as a separate argument', $sql);
 304          }
 305  
 306          // If no alias was specified, auto-detect it based on common patterns ("table.column" or just "column").
 307          if (empty($alias) && preg_match('/^(\w+\.)?(?<fieldname>\w+)$/', $sql, $matches)) {
 308              $alias = $matches['fieldname'];
 309          }
 310  
 311          if (empty($alias)) {
 312              throw new coding_exception('Complex columns must have an alias', $sql);
 313          }
 314  
 315          $this->fields[$alias] = $sql;
 316          $this->params += $params;
 317  
 318          return $this;
 319      }
 320  
 321      /**
 322       * Add a list of comma-separated fields
 323       *
 324       * @param string $sql
 325       * @param array $params
 326       * @return self
 327       */
 328      public function add_fields(string $sql, array $params = []): self {
 329          database::validate_params($params);
 330  
 331          // Split SQL into separate fields (separated by comma).
 332          $fields = preg_split('/\s*,\s*/', $sql);
 333          foreach ($fields as $field) {
 334              // Split each field into expression, <field> <as> <alias> where "as" and "alias" are optional.
 335              $fieldparts = preg_split('/\s+/', $field);
 336  
 337              if (count($fieldparts) == 2 || (count($fieldparts) == 3 && strtolower($fieldparts[1]) === 'as')) {
 338                  $sql = reset($fieldparts);
 339                  $alias = array_pop($fieldparts);
 340                  $this->add_field($sql, $alias);
 341              } else {
 342                  $this->add_field($field);
 343              }
 344          }
 345  
 346          $this->params += $params;
 347  
 348          return $this;
 349      }
 350  
 351      /**
 352       * Given a param name, add a unique prefix to ensure that the same column with params can be added multiple times to a report
 353       *
 354       * @param string $name
 355       * @return string
 356       */
 357      private function unique_param_name(string $name): string {
 358          return "p{$this->index}_{$name}";
 359      }
 360  
 361      /**
 362       * Helper method to take all fields added to the column, and return appropriate SQL and alias
 363       *
 364       * @return array[]
 365       */
 366      private function get_fields_sql_alias(): array {
 367          $fields = [];
 368  
 369          foreach ($this->fields as $alias => $sql) {
 370  
 371              // Ensure parameter names within SQL are prefixed with column index.
 372              $params = array_keys($this->params);
 373              $sql = database::sql_replace_parameter_names($sql, $params, function(string $param): string {
 374                  return $this->unique_param_name($param);
 375              });
 376  
 377              $fields[$alias] = [
 378                  'sql' => $sql,
 379                  'alias' => substr("c{$this->index}_{$alias}", 0, 30),
 380              ];
 381          }
 382  
 383          return $fields;
 384      }
 385  
 386      /**
 387       * Return array of SQL expressions for each field of this column
 388       *
 389       * @return array
 390       */
 391      public function get_fields(): array {
 392          $fieldsalias = $this->get_fields_sql_alias();
 393  
 394          if (!empty($this->aggregation)) {
 395              $fieldsaliassql = array_column($fieldsalias, 'sql');
 396              $field = reset($fieldsalias);
 397  
 398              // If aggregating the column, generate SQL from column fields and use it to generate aggregation SQL.
 399              $columnfieldsql = $this->aggregation::get_column_field_sql($fieldsaliassql);
 400              $aggregationfieldsql = $this->aggregation::get_field_sql($columnfieldsql, $this->get_type());
 401  
 402              $fields = ["{$aggregationfieldsql} AS {$field['alias']}"];
 403          } else {
 404              $fields = array_map(static function(array $field): string {
 405                  return "{$field['sql']} AS {$field['alias']}";
 406              }, $fieldsalias);
 407          }
 408  
 409          return array_values($fields);
 410      }
 411  
 412      /**
 413       * Return column parameters, prefixed by the current index to allow the column to be added multiple times to a report
 414       *
 415       * @return array
 416       */
 417      public function get_params(): array {
 418          $params = [];
 419  
 420          foreach ($this->params as $name => $value) {
 421              $paramname = $this->unique_param_name($name);
 422              $params[$paramname] = $value;
 423          }
 424  
 425          return $params;
 426      }
 427  
 428      /**
 429       * Return an alias for this column (the generated alias of it's first field)
 430       *
 431       * @return string
 432       * @throws coding_exception
 433       */
 434      public function get_column_alias(): string {
 435          if (!$fields = $this->get_fields_sql_alias()) {
 436              throw new coding_exception('Column ' . $this->get_unique_identifier() . ' contains no fields');
 437          }
 438  
 439          return reset($fields)['alias'];
 440      }
 441  
 442      /**
 443       * Define suitable SQL fragment for grouping by the columns fields. This will be returned from {@see get_groupby_sql} if set
 444       *
 445       * @param string $groupbysql
 446       * @return self
 447       */
 448      public function set_groupby_sql(string $groupbysql): self {
 449          $this->groupbysql = $groupbysql;
 450          return $this;
 451      }
 452  
 453      /**
 454       * Return suitable SQL fragment for grouping by the column fields (during aggregation)
 455       *
 456       * @return array
 457       */
 458      public function get_groupby_sql(): array {
 459          global $DB;
 460  
 461          // Return defined value if it's already been set during column definition.
 462          if (!empty($this->groupbysql)) {
 463              return [$this->groupbysql];
 464          }
 465  
 466          $fieldsalias = $this->get_fields_sql_alias();
 467  
 468          // Note that we can reference field aliases in GROUP BY only in MySQL/Postgres.
 469          $usealias = in_array($DB->get_dbfamily(), ['mysql', 'postgres']);
 470          $columnname = $usealias ? 'alias' : 'sql';
 471  
 472          return array_column($fieldsalias, $columnname);
 473      }
 474  
 475      /**
 476       * Adds column callback (in the case there are multiple, they will be called iteratively - the result of each passed
 477       * along to the next in the chain)
 478       *
 479       * The callback should implement the following signature (where $value is the first column field, $row is all column
 480       * fields, $additionalarguments are those passed to this method, and $aggregation indicates the current aggregation type
 481       * being applied to the column):
 482       *
 483       * function($value, stdClass $row, $additionalarguments, ?string $aggregation): string
 484       *
 485       * The type of the $value parameter passed to the callback is determined by calling {@see set_type}, this type is preserved
 486       * if the column is part of a report source and is being aggregated. For entities that can be left joined to a report, the
 487       * first argument of the callback must be nullable (as it should also be if the first column field is itself nullable).
 488       *
 489       * @param callable $callable
 490       * @param mixed $additionalarguments
 491       * @return self
 492       */
 493      public function add_callback(callable $callable, $additionalarguments = null): self {
 494          $this->callbacks[] = [$callable, $additionalarguments];
 495          return $this;
 496      }
 497  
 498      /**
 499       * Sets column callback. This will overwrite any previously added callbacks {@see add_callback}
 500       *
 501       * @param callable $callable
 502       * @param mixed $additionalarguments
 503       * @return self
 504       */
 505      public function set_callback(callable $callable, $additionalarguments = null): self {
 506          $this->callbacks = [];
 507          return $this->add_callback($callable, $additionalarguments);
 508      }
 509  
 510      /**
 511       * Set column aggregation type
 512       *
 513       * @param string|null $aggregation Type of aggregation, e.g. 'sum', 'count', etc
 514       * @return self
 515       * @throws coding_exception For invalid aggregation type, or one that is incompatible with column type
 516       */
 517      public function set_aggregation(?string $aggregation): self {
 518          if (!empty($aggregation)) {
 519              $aggregation = aggregation::get_full_classpath($aggregation);
 520              if (!aggregation::valid($aggregation) || !$aggregation::compatible($this->get_type())) {
 521                  throw new coding_exception('Invalid column aggregation', $aggregation);
 522              }
 523          }
 524  
 525          $this->aggregation = $aggregation;
 526          return $this;
 527      }
 528  
 529      /**
 530       * Get column aggregation type
 531       *
 532       * @return base|null
 533       */
 534      public function get_aggregation(): ?string {
 535          return $this->aggregation;
 536      }
 537  
 538      /**
 539       * Set disabled aggregation methods for the column. Typically only those methods suitable for the current column type are
 540       * available: {@see aggregation::get_column_aggregations}, however in some cases we may want to disable specific methods
 541       *
 542       * @param array $disabledaggregation Array of types, e.g. ['min', 'sum']
 543       * @return self
 544       */
 545      public function set_disabled_aggregation(array $disabledaggregation): self {
 546          $this->disabledaggregation = $disabledaggregation;
 547          return $this;
 548      }
 549  
 550      /**
 551       * Disable all aggregation methods for the column, for instance when current database can't aggregate fields that contain
 552       * sub-queries
 553       *
 554       * @return self
 555       */
 556      public function set_disabled_aggregation_all(): self {
 557          $aggregationnames = array_map(static function(string $aggregation): string {
 558              return $aggregation::get_class_name();
 559          }, aggregation::get_aggregations());
 560  
 561          return $this->set_disabled_aggregation($aggregationnames);
 562      }
 563  
 564      /**
 565       * Return those aggregations methods explicitly disabled for the column
 566       *
 567       * @return array
 568       */
 569      public function get_disabled_aggregation(): array {
 570          return $this->disabledaggregation;
 571      }
 572  
 573      /**
 574       * Sets the column as sortable
 575       *
 576       * @param bool $issortable
 577       * @param array $sortfields Define the fields that should be used when the column is sorted, typically a subset of the fields
 578       *      selected for the column, via {@see add_field}. If omitted then the first selected field is used
 579       * @return self
 580       */
 581      public function set_is_sortable(bool $issortable, array $sortfields = []): self {
 582          $this->issortable = $issortable;
 583          $this->sortfields = $sortfields;
 584          return $this;
 585      }
 586  
 587      /**
 588       * Return sortable status of column
 589       *
 590       * @return bool
 591       */
 592      public function get_is_sortable(): bool {
 593  
 594          // Defer sortable status to aggregation type if column is being aggregated.
 595          if (!empty($this->aggregation)) {
 596              return $this->aggregation::sortable($this->issortable);
 597          }
 598  
 599          return $this->issortable;
 600      }
 601  
 602      /**
 603       * Return fields to use for sorting of the column, where available the field aliases will be returned
 604       *
 605       * @return array
 606       */
 607      public function get_sort_fields(): array {
 608          $fieldsalias = $this->get_fields_sql_alias();
 609  
 610          return array_map(static function(string $sortfield) use ($fieldsalias): string {
 611  
 612              // Check whether sortfield refers to a defined field alias.
 613              if (array_key_exists($sortfield, $fieldsalias)) {
 614                  return $fieldsalias[$sortfield]['alias'];
 615              }
 616  
 617              // Check whether sortfield refers to field SQL.
 618              foreach ($fieldsalias as $field) {
 619                  if (strcasecmp($sortfield, $field['sql']) === 0) {
 620                      $sortfield = $field['alias'];
 621                      break;
 622                  }
 623              }
 624  
 625              return $sortfield;
 626          }, $this->sortfields);
 627      }
 628  
 629      /**
 630       * Extract all values from given row for this column
 631       *
 632       * @param array $row
 633       * @return array
 634       */
 635      private function get_values(array $row): array {
 636          $values = [];
 637  
 638          // During aggregation we only get a single alias back, subsequent aliases won't exist.
 639          foreach ($this->get_fields_sql_alias() as $alias => $field) {
 640              $values[$alias] = $row[$field['alias']] ?? null;
 641          }
 642  
 643          return $values;
 644      }
 645  
 646      /**
 647       * Return the default column value, that being the value of it's first field
 648       *
 649       * @param array $values
 650       * @param int $columntype
 651       * @return mixed
 652       */
 653      public static function get_default_value(array $values, int $columntype) {
 654          $value = reset($values);
 655          if ($value === null) {
 656              return $value;
 657          }
 658  
 659          // Ensure default value is cast to it's strict type.
 660          switch ($columntype) {
 661              case self::TYPE_INTEGER:
 662              case self::TYPE_TIMESTAMP:
 663                  $value = (int) $value;
 664                  break;
 665              case self::TYPE_FLOAT:
 666                  $value = (float) $value;
 667                  break;
 668              case self::TYPE_BOOLEAN:
 669                  $value = (bool) $value;
 670                  break;
 671          }
 672  
 673          return $value;
 674      }
 675  
 676      /**
 677       * Return column value based on complete table row
 678       *
 679       * @param array $row
 680       * @return mixed
 681       */
 682      public function format_value(array $row) {
 683          $values = $this->get_values($row);
 684          $value = self::get_default_value($values, $this->type);
 685  
 686          // If column is being aggregated then defer formatting to them, otherwise loop through all column callbacks.
 687          if (!empty($this->aggregation)) {
 688              $value = $this->aggregation::format_value($value, $values, $this->callbacks, $this->type);
 689          } else {
 690              foreach ($this->callbacks as $callback) {
 691                  [$callable, $arguments] = $callback;
 692                  $value = ($callable)($value, (object) $values, $arguments, null);
 693              }
 694          }
 695  
 696          return $value;
 697      }
 698  
 699      /**
 700       * Add column attributes (data-, class, etc.) that will be included in HTML when column is displayed
 701       *
 702       * @param array $attributes
 703       * @return self
 704       */
 705      public function add_attributes(array $attributes): self {
 706          $this->attributes = $attributes + $this->attributes;
 707          return $this;
 708      }
 709  
 710      /**
 711       * Returns the column HTML attributes
 712       *
 713       * @return array
 714       */
 715      public function get_attributes(): array {
 716          return $this->attributes;
 717      }
 718  
 719      /**
 720       * Return available state of the column for the current user. For instance the column may be added to a report with the
 721       * expectation that only some users are able to see it
 722       *
 723       * @return bool
 724       */
 725      public function get_is_available(): bool {
 726          return $this->available;
 727      }
 728  
 729      /**
 730       * Conditionally set whether the column is available.
 731       *
 732       * @param bool $available
 733       * @return self
 734       */
 735      public function set_is_available(bool $available): self {
 736          $this->available = $available;
 737          return $this;
 738      }
 739  
 740      /**
 741       * Set column persistent
 742       *
 743       * @param column_model $persistent
 744       * @return self
 745       */
 746      public function set_persistent(column_model $persistent): self {
 747          $this->persistent = $persistent;
 748          return $this;
 749      }
 750  
 751      /**
 752       * Return column persistent
 753       *
 754       * @return mixed
 755       */
 756      public function get_persistent(): column_model {
 757          return $this->persistent;
 758      }
 759  }