[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\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          $user = $this->get_table_alias('user');
 148          $taginstance = $this->get_table_alias('tag_instance');
 149          $tag = $this->get_table_alias('tag');
 150  
 151          return [
 152              "LEFT JOIN {tag_instance} {$taginstance}
 153                      ON {$taginstance}.component = 'core'
 154                     AND {$taginstance}.itemtype = 'user'
 155                     AND {$taginstance}.itemid = {$user}.id",
 156              "LEFT JOIN {tag} {$tag}
 157                      ON {$tag}.id = {$taginstance}.tagid",
 158          ];
 159      }
 160  
 161      /**
 162       * Returns list of all available columns
 163       *
 164       * These are all the columns available to use in any report that uses this entity.
 165       *
 166       * @return column[]
 167       */
 168      protected function get_all_columns(): array {
 169          global $DB;
 170  
 171          $usertablealias = $this->get_table_alias('user');
 172          $contexttablealias = $this->get_table_alias('context');
 173  
 174          $fullnameselect = self::get_name_fields_select($usertablealias);
 175          $fullnamesort = explode(', ', $fullnameselect);
 176  
 177          $userpictureselect = fields::for_userpic()->get_sql($usertablealias, false, '', '', false)->selects;
 178          $viewfullnames = has_capability('moodle/site:viewfullnames', context_system::instance());
 179  
 180          // Fullname column.
 181          $columns[] = (new column(
 182              'fullname',
 183              new lang_string('fullname'),
 184              $this->get_entity_name()
 185          ))
 186              ->add_joins($this->get_joins())
 187              ->add_fields($fullnameselect)
 188              ->set_type(column::TYPE_TEXT)
 189              ->set_is_sortable($this->is_sortable('fullname'), $fullnamesort)
 190              ->add_callback(static function(?string $value, stdClass $row) use ($viewfullnames): string {
 191                  if ($value === null) {
 192                      return '';
 193                  }
 194  
 195                  // Ensure we populate all required name properties.
 196                  $namefields = fields::get_name_fields();
 197                  foreach ($namefields as $namefield) {
 198                      $row->{$namefield} = $row->{$namefield} ?? '';
 199                  }
 200  
 201                  return fullname($row, $viewfullnames);
 202              });
 203  
 204          // Formatted fullname columns (with link, picture or both).
 205          $fullnamefields = [
 206              'fullnamewithlink' => new lang_string('userfullnamewithlink', 'core_reportbuilder'),
 207              'fullnamewithpicture' => new lang_string('userfullnamewithpicture', 'core_reportbuilder'),
 208              'fullnamewithpicturelink' => new lang_string('userfullnamewithpicturelink', 'core_reportbuilder'),
 209          ];
 210          foreach ($fullnamefields as $fullnamefield => $fullnamelang) {
 211              $column = (new column(
 212                  $fullnamefield,
 213                  $fullnamelang,
 214                  $this->get_entity_name()
 215              ))
 216                  ->add_joins($this->get_joins())
 217                  ->add_fields($fullnameselect)
 218                  ->add_field("{$usertablealias}.id")
 219                  ->set_type(column::TYPE_TEXT)
 220                  ->set_is_sortable($this->is_sortable($fullnamefield), $fullnamesort)
 221                  ->add_callback(static function(?string $value, stdClass $row) use ($fullnamefield, $viewfullnames): string {
 222                      global $OUTPUT;
 223  
 224                      if ($value === null) {
 225                          return '';
 226                      }
 227  
 228                      // Ensure we populate all required name properties.
 229                      $namefields = fields::get_name_fields();
 230                      foreach ($namefields as $namefield) {
 231                          $row->{$namefield} = $row->{$namefield} ?? '';
 232                      }
 233  
 234                      if ($fullnamefield === 'fullnamewithlink') {
 235                          return html_writer::link(new moodle_url('/user/profile.php', ['id' => $row->id]),
 236                              fullname($row, $viewfullnames));
 237                      }
 238                      if ($fullnamefield === 'fullnamewithpicture') {
 239                          return $OUTPUT->user_picture($row, ['link' => false, 'alttext' => false]) .
 240                              fullname($row, $viewfullnames);
 241                      }
 242                      if ($fullnamefield === 'fullnamewithpicturelink') {
 243                          return html_writer::link(new moodle_url('/user/profile.php', ['id' => $row->id]),
 244                              $OUTPUT->user_picture($row, ['link' => false, 'alttext' => false]) .
 245                              fullname($row, $viewfullnames));
 246                      }
 247  
 248                      return $value;
 249                  });
 250  
 251              // Picture fields need some more data.
 252              if (strpos($fullnamefield, 'picture') !== false) {
 253                  $column->add_fields($userpictureselect);
 254              }
 255  
 256              $columns[] = $column;
 257          }
 258  
 259          // Picture column.
 260          $columns[] = (new column(
 261              'picture',
 262              new lang_string('userpicture', 'core_reportbuilder'),
 263              $this->get_entity_name()
 264          ))
 265              ->add_joins($this->get_joins())
 266              ->add_fields($userpictureselect)
 267              ->set_type(column::TYPE_INTEGER)
 268              ->set_is_sortable($this->is_sortable('picture'))
 269              // It doesn't make sense to offer integer aggregation methods for this column.
 270              ->set_disabled_aggregation(['avg', 'max', 'min', 'sum'])
 271              ->add_callback(static function ($value, stdClass $row): string {
 272                  global $OUTPUT;
 273  
 274                  return !empty($row->id) ? $OUTPUT->user_picture($row, ['link' => false, 'alttext' => false]) : '';
 275              });
 276  
 277          // Add all other user fields.
 278          $userfields = $this->get_user_fields();
 279          foreach ($userfields as $userfield => $userfieldlang) {
 280              $columntype = $this->get_user_field_type($userfield);
 281  
 282              $columnfieldsql = "{$usertablealias}.{$userfield}";
 283              if ($columntype === column::TYPE_LONGTEXT && $DB->get_dbfamily() === 'oracle') {
 284                  $columnfieldsql = $DB->sql_order_by_text($columnfieldsql, 1024);
 285              }
 286  
 287              $column = (new column(
 288                  $userfield,
 289                  $userfieldlang,
 290                  $this->get_entity_name()
 291              ))
 292                  ->add_joins($this->get_joins())
 293                  ->set_type($columntype)
 294                  ->add_field($columnfieldsql, $userfield)
 295                  ->set_is_sortable($this->is_sortable($userfield))
 296                  ->add_callback([$this, 'format'], $userfield);
 297  
 298              // Some columns also have specific format callbacks.
 299              if ($userfield === 'country') {
 300                  $column->add_callback(static function(string $country): string {
 301                      $countries = get_string_manager()->get_list_of_countries(true);
 302                      return $countries[$country] ?? '';
 303                  });
 304              } else if ($userfield === 'description') {
 305                  // Select enough fields in order to format the column.
 306                  $column
 307                      ->add_join("LEFT JOIN {context} {$contexttablealias}
 308                             ON {$contexttablealias}.contextlevel = " . CONTEXT_USER . "
 309                            AND {$contexttablealias}.instanceid = {$usertablealias}.id")
 310                      ->add_fields("{$usertablealias}.descriptionformat, {$usertablealias}.id")
 311                      ->add_fields(context_helper::get_preload_record_columns_sql($contexttablealias));
 312              }
 313  
 314              $columns[] = $column;
 315          }
 316  
 317          return $columns;
 318      }
 319  
 320      /**
 321       * Check if this field is sortable
 322       *
 323       * @param string $fieldname
 324       * @return bool
 325       */
 326      protected function is_sortable(string $fieldname): bool {
 327          // Some columns can't be sorted, like longtext or images.
 328          $nonsortable = [
 329              'description',
 330              'picture',
 331          ];
 332  
 333          return !in_array($fieldname, $nonsortable);
 334      }
 335  
 336      /**
 337       * Formats the user field for display.
 338       *
 339       * @param mixed $value Current field value.
 340       * @param stdClass $row Complete row.
 341       * @param string $fieldname Name of the field to format.
 342       * @return string
 343       */
 344      public function format($value, stdClass $row, string $fieldname): string {
 345          global $CFG;
 346  
 347          if ($this->get_user_field_type($fieldname) === column::TYPE_BOOLEAN) {
 348              return format::boolean_as_text($value);
 349          }
 350  
 351          if ($this->get_user_field_type($fieldname) === column::TYPE_TIMESTAMP) {
 352              return format::userdate($value, $row);
 353          }
 354  
 355          if ($fieldname === 'description') {
 356              if (empty($row->id)) {
 357                  return '';
 358              }
 359  
 360              require_once("{$CFG->libdir}/filelib.php");
 361  
 362              context_helper::preload_from_record($row);
 363              $context = context_user::instance($row->id);
 364  
 365              $description = file_rewrite_pluginfile_urls($value, 'pluginfile.php', $context->id, 'user', 'profile', null);
 366              return format_text($description, $row->descriptionformat, ['context' => $context->id]);
 367          }
 368  
 369          return s($value);
 370      }
 371  
 372      /**
 373       * Returns a SQL statement to select all user fields necessary for fullname() function
 374       *
 375       * Note the implementation here is similar to {@see fields::get_sql_fullname} but without concatenation
 376       *
 377       * @param string $usertablealias
 378       * @return string
 379       */
 380      public static function get_name_fields_select(string $usertablealias = 'u'): string {
 381  
 382          $namefields = fields::get_name_fields(true);
 383  
 384          // Create a dummy user object containing all name fields.
 385          $dummyuser = (object) array_combine($namefields, $namefields);
 386          $dummyfullname = fullname($dummyuser, true);
 387  
 388          // Extract any name fields from the fullname format in the order that they appear.
 389          $matchednames = array_values(order_in_string($namefields, $dummyfullname));
 390  
 391          $userfields = array_map(static function(string $userfield) use ($usertablealias): string {
 392              if (!empty($usertablealias)) {
 393                  $userfield = "{$usertablealias}.{$userfield}";
 394              }
 395  
 396              return $userfield;
 397          }, $matchednames);
 398  
 399          return implode(', ', $userfields);
 400      }
 401  
 402      /**
 403       * User fields
 404       *
 405       * @return lang_string[]
 406       */
 407      protected function get_user_fields(): array {
 408          return [
 409              'firstname' => new lang_string('firstname'),
 410              'lastname' => new lang_string('lastname'),
 411              'email' => new lang_string('email'),
 412              'city' => new lang_string('city'),
 413              'country' => new lang_string('country'),
 414              'description' => new lang_string('description'),
 415              'firstnamephonetic' => new lang_string('firstnamephonetic'),
 416              'lastnamephonetic' => new lang_string('lastnamephonetic'),
 417              'middlename' => new lang_string('middlename'),
 418              'alternatename' => new lang_string('alternatename'),
 419              'idnumber' => new lang_string('idnumber'),
 420              'institution' => new lang_string('institution'),
 421              'department' => new lang_string('department'),
 422              'phone1' => new lang_string('phone1'),
 423              'phone2' => new lang_string('phone2'),
 424              'address' => new lang_string('address'),
 425              'lastaccess' => new lang_string('lastaccess'),
 426              'suspended' => new lang_string('suspended'),
 427              'confirmed' => new lang_string('confirmed', 'admin'),
 428              'username' => new lang_string('username'),
 429              'moodlenetprofile' => new lang_string('moodlenetprofile', 'user'),
 430              'timecreated' => new lang_string('timecreated', 'core_reportbuilder'),
 431          ];
 432      }
 433  
 434      /**
 435       * Return appropriate column type for given user field
 436       *
 437       * @param string $userfield
 438       * @return int
 439       */
 440      protected function get_user_field_type(string $userfield): int {
 441          switch ($userfield) {
 442              case 'description':
 443                  $fieldtype = column::TYPE_LONGTEXT;
 444                  break;
 445              case 'confirmed':
 446              case 'suspended':
 447                  $fieldtype = column::TYPE_BOOLEAN;
 448                  break;
 449              case 'lastaccess':
 450              case 'timecreated':
 451                  $fieldtype = column::TYPE_TIMESTAMP;
 452                  break;
 453              default:
 454                  $fieldtype = column::TYPE_TEXT;
 455                  break;
 456          }
 457  
 458          return $fieldtype;
 459      }
 460  
 461      /**
 462       * Return list of all available filters
 463       *
 464       * @return filter[]
 465       */
 466      protected function get_all_filters(): array {
 467          global $DB;
 468  
 469          $filters = [];
 470          $tablealias = $this->get_table_alias('user');
 471  
 472          // Fullname filter.
 473          $canviewfullnames = has_capability('moodle/site:viewfullnames', context_system::instance());
 474          [$fullnamesql, $fullnameparams] = fields::get_sql_fullname($tablealias, $canviewfullnames);
 475          $filters[] = (new filter(
 476              text::class,
 477              'fullname',
 478              new lang_string('fullname'),
 479              $this->get_entity_name(),
 480              $fullnamesql,
 481              $fullnameparams
 482          ))
 483              ->add_joins($this->get_joins());
 484  
 485          // User fields filters.
 486          $fields = $this->get_user_fields();
 487          foreach ($fields as $field => $name) {
 488              $filterfieldsql = "{$tablealias}.{$field}";
 489              if ($this->get_user_field_type($field) === column::TYPE_LONGTEXT) {
 490                  $filterfieldsql = $DB->sql_cast_to_char($filterfieldsql);
 491              }
 492  
 493              $optionscallback = [static::class, 'get_options_for_' . $field];
 494              if (is_callable($optionscallback)) {
 495                  $classname = select::class;
 496              } else if ($this->get_user_field_type($field) === column::TYPE_BOOLEAN) {
 497                  $classname = boolean_select::class;
 498              } else if ($this->get_user_field_type($field) === column::TYPE_TIMESTAMP) {
 499                  $classname = date::class;
 500              } else {
 501                  $classname = text::class;
 502              }
 503  
 504              $filter = (new filter(
 505                  $classname,
 506                  $field,
 507                  $name,
 508                  $this->get_entity_name(),
 509                  $filterfieldsql
 510              ))
 511                  ->add_joins($this->get_joins());
 512  
 513              // Populate filter options by callback, if available.
 514              if (is_callable($optionscallback)) {
 515                  $filter->set_options_callback($optionscallback);
 516              }
 517  
 518              $filters[] = $filter;
 519          }
 520  
 521          // User select filter.
 522          $filters[] = (new filter(
 523              user_filter::class,
 524              'userselect',
 525              new lang_string('userselect', 'core_reportbuilder'),
 526              $this->get_entity_name(),
 527              "{$tablealias}.id"
 528          ))
 529              ->add_joins($this->get_joins());
 530  
 531          // Authentication method filter.
 532          $filters[] = (new filter(
 533              select::class,
 534              'auth',
 535              new lang_string('authentication', 'moodle'),
 536              $this->get_entity_name(),
 537              "{$tablealias}.auth"
 538          ))
 539              ->add_joins($this->get_joins())
 540              ->set_options_callback(static function(): array {
 541                  $plugins = core_component::get_plugin_list('auth');
 542                  $enabled = get_string('pluginenabled', 'core_plugin');
 543                  $disabled = get_string('plugindisabled', 'core_plugin');
 544                  $authoptions = [$enabled => [], $disabled => []];
 545  
 546                  foreach ($plugins as $pluginname => $unused) {
 547                      $plugin = get_auth_plugin($pluginname);
 548                      if (is_enabled_auth($pluginname)) {
 549                          $authoptions[$enabled][$pluginname] = $plugin->get_title();
 550                      } else {
 551                          $authoptions[$disabled][$pluginname] = $plugin->get_title();
 552                      }
 553                  }
 554                  return $authoptions;
 555              });
 556  
 557          return $filters;
 558      }
 559  
 560      /**
 561       * List of options for the field country.
 562       *
 563       * @return string[]
 564       */
 565      public static function get_options_for_country(): array {
 566          return array_map('shorten_text', get_string_manager()->get_list_of_countries());
 567      }
 568  }