[Summary view]

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\entities;
  20  
  21  use context_helper;
  22  use context_system;
  23  use context_user;
  24  use core_component;
  25  use html_writer;
  26  use lang_string;
  27  use moodle_url;
  28  use stdClass;
  29  use core_user\fields;
  30  use core_reportbuilder\local\filters\boolean_select;
  31  use core_reportbuilder\local\filters\date;
  32  use core_reportbuilder\local\filters\select;
  33  use core_reportbuilder\local\filters\text;
  34  use core_reportbuilder\local\filters\user as user_filter;
  35  use core_reportbuilder\local\helpers\user_profile_fields;
  36  use core_reportbuilder\local\helpers\format;
  37  use core_reportbuilder\local\report\column;
  38  use core_reportbuilder\local\report\filter;
  39  
  40  /**
  41   * User entity class implementation.
  42   *
  43   * This entity defines all the user columns and filters to be used in any report.
  44   *
  45   * @package    core_reportbuilder
  46   * @copyright  2020 Sara Arjona <sara@moodle.com> based on Marina Glancy code.
  47   * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
  48   */
  49  class user extends base {
  50  
  51      /**
  52       * Database tables that this entity uses and their default aliases
  53       *
  54       * @return array
  55       */
  56      protected function get_default_table_aliases(): array {
  57          return [
  58              'user' => 'u',
  59              'context' => 'uctx',
  60              'tag_instance' => 'uti',
  61              'tag' => 'ut',
  62          ];
  63      }
  64  
  65      /**
  66       * The default title for this entity
  67       *
  68       * @return lang_string
  69       */
  70      protected function get_default_entity_title(): lang_string {
  71          return new lang_string('entityuser', 'core_reportbuilder');
  72      }
  73  
  74      /**
  75       * Initialise the entity, add all user fields and all 'visible' user profile fields
  76       *
  77       * @return base
  78       */
  79      public function initialise(): base {
  80          $userprofilefields = $this->get_user_profile_fields();
  81  
  82          $columns = array_merge($this->get_all_columns(), $userprofilefields->get_columns());
  83          foreach ($columns as $column) {
  84              $this->add_column($column);
  85          }
  86  
  87          $filters = array_merge($this->get_all_filters(), $userprofilefields->get_filters());
  88          foreach ($filters as $filter) {
  89              $this->add_filter($filter);
  90          }
  91  
  92          $conditions = array_merge($this->get_all_filters(), $userprofilefields->get_filters());
  93          foreach ($conditions as $condition) {
  94              $this->add_condition($condition);
  95          }
  96  
  97          return $this;
  98      }
  99  
 100      /**
 101       * Get user profile fields helper instance
 102       *
 103       * @return user_profile_fields
 104       */
 105      protected function get_user_profile_fields(): user_profile_fields {
 106          $userprofilefields = new user_profile_fields($this->get_table_alias('user') . '.id', $this->get_entity_name());
 107          $userprofilefields->add_joins($this->get_joins());
 108          return $userprofilefields;
 109      }
 110  
 111      /**
 112       * Returns column that corresponds to the given identity field, profile field identifiers will be converted to those
 113       * used by the {@see user_profile_fields} helper
 114       *
 115       * @param string $identityfield Field from the user table, or a custom profile field
 116       * @return column
 117       */
 118      public function get_identity_column(string $identityfield): column {
 119          if (preg_match(fields::PROFILE_FIELD_REGEX, $identityfield, $matches)) {
 120              $identityfield = 'profilefield_' . $matches[1];
 121          }
 122  
 123          return $this->get_column($identityfield);
 124      }
 125  
 126      /**
 127       * Returns filter that corresponds to the given identity field, profile field identifiers will be converted to those
 128       * used by the {@see user_profile_fields} helper
 129       *
 130       * @param string $identityfield Field from the user table, or a custom profile field
 131       * @return filter
 132       */
 133      public function get_identity_filter(string $identityfield): filter {
 134          if (preg_match(fields::PROFILE_FIELD_REGEX, $identityfield, $matches)) {
 135              $identityfield = 'profilefield_' . $matches[1];
 136          }
 137  
 138          return $this->get_filter($identityfield);
 139      }
 140  
 141      /**
 142       * Return joins necessary for retrieving tags
 143       *
 144       * @return string[]
 145       */
 146      public function get_tag_joins(): array {
 147          return $this->get_tag_joins_for_entity('core', 'user', $this->get_table_alias('user') . '.id');
 148      }
 149  
 150      /**
 151       * Returns list of all available columns
 152       *
 153       * These are all the columns available to use in any report that uses this entity.
 154       *
 155       * @return column[]
 156       */
 157      protected function get_all_columns(): array {
 158          global $DB;
 159  
 160          $usertablealias = $this->get_table_alias('user');
 161          $contexttablealias = $this->get_table_alias('context');
 162  
 163          $fullnameselect = self::get_name_fields_select($usertablealias);
 164          $fullnamesort = explode(', ', $fullnameselect);
 165  
 166          $userpictureselect = fields::for_userpic()->get_sql($usertablealias, false, '', '', false)->selects;
 167          $viewfullnames = has_capability('moodle/site:viewfullnames', context_system::instance());
 168  
 169          // Fullname column.
 170          $columns[] = (new column(
 171              'fullname',
 172              new lang_string('fullname'),
 173              $this->get_entity_name()
 174          ))
 175              ->add_joins($this->get_joins())
 176              ->add_fields($fullnameselect)
 177              ->set_type(column::TYPE_TEXT)
 178              ->set_is_sortable($this->is_sortable('fullname'), $fullnamesort)
 179              ->add_callback(static function(?string $value, stdClass $row) use ($viewfullnames): string {
 180                  if ($value === null) {
 181                      return '';
 182                  }
 183  
 184                  // Ensure we populate all required name properties.
 185                  $namefields = fields::get_name_fields();
 186                  foreach ($namefields as $namefield) {
 187                      $row->{$namefield} = $row->{$namefield} ?? '';
 188                  }
 189  
 190                  return fullname($row, $viewfullnames);
 191              });
 192  
 193          // Formatted fullname columns (with link, picture or both).
 194          $fullnamefields = [
 195              'fullnamewithlink' => new lang_string('userfullnamewithlink', 'core_reportbuilder'),
 196              'fullnamewithpicture' => new lang_string('userfullnamewithpicture', 'core_reportbuilder'),
 197              'fullnamewithpicturelink' => new lang_string('userfullnamewithpicturelink', 'core_reportbuilder'),
 198          ];
 199          foreach ($fullnamefields as $fullnamefield => $fullnamelang) {
 200              $column = (new column(
 201                  $fullnamefield,
 202                  $fullnamelang,
 203                  $this->get_entity_name()
 204              ))
 205                  ->add_joins($this->get_joins())
 206                  ->add_fields($fullnameselect)
 207                  ->add_field("{$usertablealias}.id")
 208                  ->set_type(column::TYPE_TEXT)
 209                  ->set_is_sortable($this->is_sortable($fullnamefield), $fullnamesort)
 210                  ->add_callback(static function(?string $value, stdClass $row) use ($fullnamefield, $viewfullnames): string {
 211                      global $OUTPUT;
 212  
 213                      if ($value === null) {
 214                          return '';
 215                      }
 216  
 217                      // Ensure we populate all required name properties.
 218                      $namefields = fields::get_name_fields();
 219                      foreach ($namefields as $namefield) {
 220                          $row->{$namefield} = $row->{$namefield} ?? '';
 221                      }
 222  
 223                      if ($fullnamefield === 'fullnamewithlink') {
 224                          return html_writer::link(new moodle_url('/user/profile.php', ['id' => $row->id]),
 225                              fullname($row, $viewfullnames));
 226                      }
 227                      if ($fullnamefield === 'fullnamewithpicture') {
 228                          return $OUTPUT->user_picture($row, ['link' => false, 'alttext' => false]) .
 229                              fullname($row, $viewfullnames);
 230                      }
 231                      if ($fullnamefield === 'fullnamewithpicturelink') {
 232                          return html_writer::link(new moodle_url('/user/profile.php', ['id' => $row->id]),
 233                              $OUTPUT->user_picture($row, ['link' => false, 'alttext' => false]) .
 234                              fullname($row, $viewfullnames));
 235                      }
 236  
 237                      return $value;
 238                  });
 239  
 240              // Picture fields need some more data.
 241              if (strpos($fullnamefield, 'picture') !== false) {
 242                  $column->add_fields($userpictureselect);
 243              }
 244  
 245              $columns[] = $column;
 246          }
 247  
 248          // Picture column.
 249          $columns[] = (new column(
 250              'picture',
 251              new lang_string('userpicture', 'core_reportbuilder'),
 252              $this->get_entity_name()
 253          ))
 254              ->add_joins($this->get_joins())
 255              ->add_fields($userpictureselect)
 256              ->set_type(column::TYPE_INTEGER)
 257              ->set_is_sortable($this->is_sortable('picture'))
 258              // It doesn't make sense to offer integer aggregation methods for this column.
 259              ->set_disabled_aggregation(['avg', 'max', 'min', 'sum'])
 260              ->add_callback(static function ($value, stdClass $row): string {
 261                  global $OUTPUT;
 262  
 263                  return !empty($row->id) ? $OUTPUT->user_picture($row, ['link' => false, 'alttext' => false]) : '';
 264              });
 265  
 266          // Add all other user fields.
 267          $userfields = $this->get_user_fields();
 268          foreach ($userfields as $userfield => $userfieldlang) {
 269              $columntype = $this->get_user_field_type($userfield);
 270  
 271              $columnfieldsql = "{$usertablealias}.{$userfield}";
 272              if ($columntype === column::TYPE_LONGTEXT && $DB->get_dbfamily() === 'oracle') {
 273                  $columnfieldsql = $DB->sql_order_by_text($columnfieldsql, 1024);
 274              }
 275  
 276              $column = (new column(
 277                  $userfield,
 278                  $userfieldlang,
 279                  $this->get_entity_name()
 280              ))
 281                  ->add_joins($this->get_joins())
 282                  ->set_type($columntype)
 283                  ->add_field($columnfieldsql, $userfield)
 284                  ->set_is_sortable($this->is_sortable($userfield))
 285                  ->add_callback([$this, 'format'], $userfield);
 286  
 287              // Some columns also have specific format callbacks.
 288              if ($userfield === 'country') {
 289                  $column->add_callback(static function(string $country): string {
 290                      $countries = get_string_manager()->get_list_of_countries(true);
 291                      return $countries[$country] ?? '';
 292                  });
 293              } else if ($userfield === 'description') {
 294                  // Select enough fields in order to format the column.
 295                  $column
 296                      ->add_join("LEFT JOIN {context} {$contexttablealias}
 297                             ON {$contexttablealias}.contextlevel = " . CONTEXT_USER . "
 298                            AND {$contexttablealias}.instanceid = {$usertablealias}.id")
 299                      ->add_fields("{$usertablealias}.descriptionformat, {$usertablealias}.id")
 300                      ->add_fields(context_helper::get_preload_record_columns_sql($contexttablealias));
 301              }
 302  
 303              $columns[] = $column;
 304          }
 305  
 306          return $columns;
 307      }
 308  
 309      /**
 310       * Check if this field is sortable
 311       *
 312       * @param string $fieldname
 313       * @return bool
 314       */
 315      protected function is_sortable(string $fieldname): bool {
 316          // Some columns can't be sorted, like longtext or images.
 317          $nonsortable = [
 318              'description',
 319              'picture',
 320          ];
 321  
 322          return !in_array($fieldname, $nonsortable);
 323      }
 324  
 325      /**
 326       * Formats the user field for display.
 327       *
 328       * @param mixed $value Current field value.
 329       * @param stdClass $row Complete row.
 330       * @param string $fieldname Name of the field to format.
 331       * @return string
 332       */
 333      public function format($value, stdClass $row, string $fieldname): string {
 334          global $CFG;
 335  
 336          if ($this->get_user_field_type($fieldname) === column::TYPE_BOOLEAN) {
 337              return format::boolean_as_text($value);
 338          }
 339  
 340          if ($this->get_user_field_type($fieldname) === column::TYPE_TIMESTAMP) {
 341              return format::userdate($value, $row);
 342          }
 343  
 344          if ($fieldname === 'description') {
 345              if (empty($row->id)) {
 346                  return '';
 347              }
 348  
 349              require_once("{$CFG->libdir}/filelib.php");
 350  
 351              context_helper::preload_from_record($row);
 352              $context = context_user::instance($row->id);
 353  
 354              $description = file_rewrite_pluginfile_urls($value, 'pluginfile.php', $context->id, 'user', 'profile', null);
 355              return format_text($description, $row->descriptionformat, ['context' => $context->id]);
 356          }
 357  
 358          return s($value);
 359      }
 360  
 361      /**
 362       * Returns a SQL statement to select all user fields necessary for fullname() function
 363       *
 364       * Note the implementation here is similar to {@see fields::get_sql_fullname} but without concatenation
 365       *
 366       * @param string $usertablealias
 367       * @return string
 368       */
 369      public static function get_name_fields_select(string $usertablealias = 'u'): string {
 370  
 371          $namefields = fields::get_name_fields(true);
 372  
 373          // Create a dummy user object containing all name fields.
 374          $dummyuser = (object) array_combine($namefields, $namefields);
 375          $dummyfullname = fullname($dummyuser, true);
 376  
 377          // Extract any name fields from the fullname format in the order that they appear.
 378          $matchednames = array_values(order_in_string($namefields, $dummyfullname));
 379  
 380          $userfields = array_map(static function(string $userfield) use ($usertablealias): string {
 381              if (!empty($usertablealias)) {
 382                  $userfield = "{$usertablealias}.{$userfield}";
 383              }
 384  
 385              return $userfield;
 386          }, $matchednames);
 387  
 388          return implode(', ', $userfields);
 389      }
 390  
 391      /**
 392       * User fields
 393       *
 394       * @return lang_string[]
 395       */
 396      protected function get_user_fields(): array {
 397          return [
 398              'firstname' => new lang_string('firstname'),
 399              'lastname' => new lang_string('lastname'),
 400              'email' => new lang_string('email'),
 401              'city' => new lang_string('city'),
 402              'country' => new lang_string('country'),
 403              'description' => new lang_string('description'),
 404              'firstnamephonetic' => new lang_string('firstnamephonetic'),
 405              'lastnamephonetic' => new lang_string('lastnamephonetic'),
 406              'middlename' => new lang_string('middlename'),
 407              'alternatename' => new lang_string('alternatename'),
 408              'idnumber' => new lang_string('idnumber'),
 409              'institution' => new lang_string('institution'),
 410              'department' => new lang_string('department'),
 411              'phone1' => new lang_string('phone1'),
 412              'phone2' => new lang_string('phone2'),
 413              'address' => new lang_string('address'),
 414              'lastaccess' => new lang_string('lastaccess'),
 415              'suspended' => new lang_string('suspended'),
 416              'confirmed' => new lang_string('confirmed', 'admin'),
 417              'username' => new lang_string('username'),
 418              'moodlenetprofile' => new lang_string('moodlenetprofile', 'user'),
 419              'timecreated' => new lang_string('timecreated', 'core_reportbuilder'),
 420          ];
 421      }
 422  
 423      /**
 424       * Return appropriate column type for given user field
 425       *
 426       * @param string $userfield
 427       * @return int
 428       */
 429      protected function get_user_field_type(string $userfield): int {
 430          switch ($userfield) {
 431              case 'description':
 432                  $fieldtype = column::TYPE_LONGTEXT;
 433                  break;
 434              case 'confirmed':
 435              case 'suspended':
 436                  $fieldtype = column::TYPE_BOOLEAN;
 437                  break;
 438              case 'lastaccess':
 439              case 'timecreated':
 440                  $fieldtype = column::TYPE_TIMESTAMP;
 441                  break;
 442              default:
 443                  $fieldtype = column::TYPE_TEXT;
 444                  break;
 445          }
 446  
 447          return $fieldtype;
 448      }
 449  
 450      /**
 451       * Return list of all available filters
 452       *
 453       * @return filter[]
 454       */
 455      protected function get_all_filters(): array {
 456          global $DB;
 457  
 458          $filters = [];
 459          $tablealias = $this->get_table_alias('user');
 460  
 461          // Fullname filter.
 462          $canviewfullnames = has_capability('moodle/site:viewfullnames', context_system::instance());
 463          [$fullnamesql, $fullnameparams] = fields::get_sql_fullname($tablealias, $canviewfullnames);
 464          $filters[] = (new filter(
 465              text::class,
 466              'fullname',
 467              new lang_string('fullname'),
 468              $this->get_entity_name(),
 469              $fullnamesql,
 470              $fullnameparams
 471          ))
 472              ->add_joins($this->get_joins());
 473  
 474          // User fields filters.
 475          $fields = $this->get_user_fields();
 476          foreach ($fields as $field => $name) {
 477              $filterfieldsql = "{$tablealias}.{$field}";
 478              if ($this->get_user_field_type($field) === column::TYPE_LONGTEXT) {
 479                  $filterfieldsql = $DB->sql_cast_to_char($filterfieldsql);
 480              }
 481  
 482              $optionscallback = [static::class, 'get_options_for_' . $field];
 483              if (is_callable($optionscallback)) {
 484                  $classname = select::class;
 485              } else if ($this->get_user_field_type($field) === column::TYPE_BOOLEAN) {
 486                  $classname = boolean_select::class;
 487              } else if ($this->get_user_field_type($field) === column::TYPE_TIMESTAMP) {
 488                  $classname = date::class;
 489              } else {
 490                  $classname = text::class;
 491              }
 492  
 493              $filter = (new filter(
 494                  $classname,
 495                  $field,
 496                  $name,
 497                  $this->get_entity_name(),
 498                  $filterfieldsql
 499              ))
 500                  ->add_joins($this->get_joins());
 501  
 502              // Populate filter options by callback, if available.
 503              if (is_callable($optionscallback)) {
 504                  $filter->set_options_callback($optionscallback);
 505              }
 506  
 507              $filters[] = $filter;
 508          }
 509  
 510          // User select filter.
 511          $filters[] = (new filter(
 512              user_filter::class,
 513              'userselect',
 514              new lang_string('userselect', 'core_reportbuilder'),
 515              $this->get_entity_name(),
 516              "{$tablealias}.id"
 517          ))
 518              ->add_joins($this->get_joins());
 519  
 520          // Authentication method filter.
 521          $filters[] = (new filter(
 522              select::class,
 523              'auth',
 524              new lang_string('authentication', 'moodle'),
 525              $this->get_entity_name(),
 526              "{$tablealias}.auth"
 527          ))
 528              ->add_joins($this->get_joins())
 529              ->set_options_callback(static function(): array {
 530                  $plugins = core_component::get_plugin_list('auth');
 531                  $enabled = get_string('pluginenabled', 'core_plugin');
 532                  $disabled = get_string('plugindisabled', 'core_plugin');
 533                  $authoptions = [$enabled => [], $disabled => []];
 534  
 535                  foreach ($plugins as $pluginname => $unused) {
 536                      $plugin = get_auth_plugin($pluginname);
 537                      if (is_enabled_auth($pluginname)) {
 538                          $authoptions[$enabled][$pluginname] = $plugin->get_title();
 539                      } else {
 540                          $authoptions[$disabled][$pluginname] = $plugin->get_title();
 541                      }
 542                  }
 543                  return $authoptions;
 544              });
 545  
 546          return $filters;
 547      }
 548  
 549      /**
 550       * List of options for the field country.
 551       *
 552       * @return string[]
 553       */
 554      public static function get_options_for_country(): array {
 555          return array_map('shorten_text', get_string_manager()->get_list_of_countries());
 556      }
 557  }