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