reportbuilder/classes/local/entities/user.php

   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
 113      *
 114      * @param string $identityfield Field from the user table, or the shortname of a custom profile field
 115      * @return column
 116      */
 117     public function get_identity_column(string $identityfield): column {
 118         if (preg_match("/^profile_field_(?<shortname>.*)$/", $identityfield, $matches)) {
 119             $identityfield = 'profilefield_' . $matches['shortname'];
 120         }
 121
 122         return $this->get_column($identityfield);
 123     }
 124
 125     /**
 126      * Returns filter that corresponds to the given identity field
 127      *
 128      * @param string $identityfield Field from the user table, or the shortname of a custom profile field
 129      * @return filter
 130      */
 131     public function get_identity_filter(string $identityfield): filter {
 132         if (preg_match("/^profile_field_(?<shortname>.*)$/", $identityfield, $matches)) {
 133             $identityfield = 'profilefield_' . $matches['shortname'];
 134         }
 135
 136         return $this->get_filter($identityfield);
 137     }
 138
 139     /**
 140      * Return joins necessary for retrieving tags
 141      *
 142      * @return string[]
 143      */
 144     public function get_tag_joins(): array {
 145         $user = $this->get_table_alias('user');
 146         $taginstance = $this->get_table_alias('tag_instance');
 147         $tag = $this->get_table_alias('tag');
 148
 149         return [
 150             "LEFT JOIN {tag_instance} {$taginstance}
 151                     ON {$taginstance}.component = 'core'
 152                    AND {$taginstance}.itemtype = 'user'
 153                    AND {$taginstance}.itemid = {$user}.id",
 154             "LEFT JOIN {tag} {$tag}
 155                     ON {$tag}.id = {$taginstance}.tagid",
 156         ];
 157     }
 158
 159     /**
 160      * Returns list of all available columns
 161      *
 162      * These are all the columns available to use in any report that uses this entity.
 163      *
 164      * @return column[]
 165      */
 166     protected function get_all_columns(): array {
 167         global $DB;
 168
 169         $usertablealias = $this->get_table_alias('user');
 170         $contexttablealias = $this->get_table_alias('context');
 171
 172         $fullnameselect = self::get_name_fields_select($usertablealias);
 173         $fullnamesort = explode(', ', $fullnameselect);
 174
 175         $userpictureselect = fields::for_userpic()->get_sql($usertablealias, false, '', '', false)->selects;
 176         $viewfullnames = has_capability('moodle/site:viewfullnames', context_system::instance());
 177
 178         // Fullname column.
 179         $columns[] = (new column(
 180             'fullname',
 181             new lang_string('fullname'),
 182             $this->get_entity_name()
 183         ))
 184             ->add_joins($this->get_joins())
 185             ->add_fields($fullnameselect)
 186             ->set_type(column::TYPE_TEXT)
 187             ->set_is_sortable($this->is_sortable('fullname'), $fullnamesort)
 188             ->add_callback(static function(?string $value, stdClass $row) use ($viewfullnames): string {
 189                 if ($value === null) {
 190                     return '';
 191                 }
 192
 193                 // Ensure we populate all required name properties.
 194                 $namefields = fields::get_name_fields();
 195                 foreach ($namefields as $namefield) {
 196                     $row->{$namefield} = $row->{$namefield} ?? '';
 197                 }
 198
 199                 return fullname($row, $viewfullnames);
 200             });
 201
 202         // Formatted fullname columns (with link, picture or both).
 203         $fullnamefields = [
 204             'fullnamewithlink' => new lang_string('userfullnamewithlink', 'core_reportbuilder'),
 205             'fullnamewithpicture' => new lang_string('userfullnamewithpicture', 'core_reportbuilder'),
 206             'fullnamewithpicturelink' => new lang_string('userfullnamewithpicturelink', 'core_reportbuilder'),
 207         ];
 208         foreach ($fullnamefields as $fullnamefield => $fullnamelang) {
 209             $column = (new column(
 210                 $fullnamefield,
 211                 $fullnamelang,
 212                 $this->get_entity_name()
 213             ))
 214                 ->add_joins($this->get_joins())
 215                 ->add_fields($fullnameselect)
 216                 ->add_field("{$usertablealias}.id")
 217                 ->set_type(column::TYPE_TEXT)
 218                 ->set_is_sortable($this->is_sortable($fullnamefield), $fullnamesort)
 219                 ->add_callback(static function(?string $value, stdClass $row) use ($fullnamefield, $viewfullnames): string {
 220                     global $OUTPUT;
 221
 222                     if ($value === null) {
 223                         return '';
 224                     }
 225
 226                     // Ensure we populate all required name properties.
 227                     $namefields = fields::get_name_fields();
 228                     foreach ($namefields as $namefield) {
 229                         $row->{$namefield} = $row->{$namefield} ?? '';
 230                     }
 231
 232                     if ($fullnamefield === 'fullnamewithlink') {
 233                         return html_writer::link(new moodle_url('/user/profile.php', ['id' => $row->id]),
 234                             fullname($row, $viewfullnames));
 235                     }
 236                     if ($fullnamefield === 'fullnamewithpicture') {
 237                         return $OUTPUT->user_picture($row, ['link' => false, 'alttext' => false]) .
 238                             fullname($row, $viewfullnames);
 239                     }
 240                     if ($fullnamefield === 'fullnamewithpicturelink') {
 241                         return html_writer::link(new moodle_url('/user/profile.php', ['id' => $row->id]),
 242                             $OUTPUT->user_picture($row, ['link' => false, 'alttext' => false]) .
 243                             fullname($row, $viewfullnames));
 244                     }
 245
 246                     return $value;
 247                 });
 248
 249             // Picture fields need some more data.
 250             if (strpos($fullnamefield, 'picture') !== false) {
 251                 $column->add_fields($userpictureselect);
 252             }
 253
 254             $columns[] = $column;
 255         }
 256
 257         // Picture column.
 258         $columns[] = (new column(
 259             'picture',
 260             new lang_string('userpicture', 'core_reportbuilder'),
 261             $this->get_entity_name()
 262         ))
 263             ->add_joins($this->get_joins())
 264             ->add_fields($userpictureselect)
 265             ->set_type(column::TYPE_INTEGER)
 266             ->set_is_sortable($this->is_sortable('picture'))
 267             // It doesn't make sense to offer integer aggregation methods for this column.
 268             ->set_disabled_aggregation(['avg', 'max', 'min', 'sum'])
 269             ->add_callback(static function ($value, stdClass $row): string {
 270                 global $OUTPUT;
 271
 272                 return !empty($row->id) ? $OUTPUT->user_picture($row, ['link' => false, 'alttext' => false]) : '';
 273             });
 274
 275         // Add all other user fields.
 276         $userfields = $this->get_user_fields();
 277         foreach ($userfields as $userfield => $userfieldlang) {
 278             $columntype = $this->get_user_field_type($userfield);
 279
 280             $columnfieldsql = "{$usertablealias}.{$userfield}";
 281             if ($columntype === column::TYPE_LONGTEXT && $DB->get_dbfamily() === 'oracle') {
 282                 $columnfieldsql = $DB->sql_order_by_text($columnfieldsql, 1024);
 283             }
 284
 285             $column = (new column(
 286                 $userfield,
 287                 $userfieldlang,
 288                 $this->get_entity_name()
 289             ))
 290                 ->add_joins($this->get_joins())
 291                 ->set_type($columntype)
 292                 ->add_field($columnfieldsql, $userfield)
 293                 ->set_is_sortable($this->is_sortable($userfield))
 294                 ->add_callback([$this, 'format'], $userfield);
 295
 296             // Some columns also have specific format callbacks.
 297             if ($userfield === 'country') {
 298                 $column->add_callback(static function(string $country): string {
 299                     $countries = get_string_manager()->get_list_of_countries(true);
 300                     return $countries[$country] ?? '';
 301                 });
 302             } else if ($userfield === 'description') {
 303                 // Select enough fields in order to format the column.
 304                 $column
 305                     ->add_join("LEFT JOIN {context} {$contexttablealias}
 306                            ON {$contexttablealias}.contextlevel = " . CONTEXT_USER . "
 307                           AND {$contexttablealias}.instanceid = {$usertablealias}.id")
 308                     ->add_fields("{$usertablealias}.descriptionformat, {$usertablealias}.id")
 309                     ->add_fields(context_helper::get_preload_record_columns_sql($contexttablealias));
 310             }
 311
 312             $columns[] = $column;
 313         }
 314
 315         return $columns;
 316     }
 317
 318     /**
 319      * Check if this field is sortable
 320      *
 321      * @param string $fieldname
 322      * @return bool
 323      */
 324     protected function is_sortable(string $fieldname): bool {
 325         // Some columns can't be sorted, like longtext or images.
 326         $nonsortable = [
 327             'description',
 328             'picture',
 329         ];
 330
 331         return !in_array($fieldname, $nonsortable);
 332     }
 333
 334     /**
 335      * Formats the user field for display.
 336      *
 337      * @param mixed $value Current field value.
 338      * @param stdClass $row Complete row.
 339      * @param string $fieldname Name of the field to format.
 340      * @return string
 341      */
 342     public function format($value, stdClass $row, string $fieldname): string {
 343         global $CFG;
 344
 345         if ($this->get_user_field_type($fieldname) === column::TYPE_BOOLEAN) {
 346             return format::boolean_as_text($value);
 347         }
 348
 349         if ($this->get_user_field_type($fieldname) === column::TYPE_TIMESTAMP) {
 350             return format::userdate($value, $row);
 351         }
 352
 353         if ($fieldname === 'description') {
 354             if (empty($row->id)) {
 355                 return '';
 356             }
 357
 358             require_once("{$CFG->libdir}/filelib.php");
 359
 360             context_helper::preload_from_record($row);
 361             $context = context_user::instance($row->id);
 362
 363             $description = file_rewrite_pluginfile_urls($value, 'pluginfile.php', $context->id, 'user', 'profile', null);
 364             return format_text($description, $row->descriptionformat, ['context' => $context->id]);
 365         }
 366
 367         return s($value);
 368     }
 369
 370     /**
 371      * Returns a SQL statement to select all user fields necessary for fullname() function
 372      *
 373      * Note the implementation here is similar to {@see fields::get_sql_fullname} but without concatenation
 374      *
 375      * @param string $usertablealias
 376      * @return string
 377      */
 378     public static function get_name_fields_select(string $usertablealias = 'u'): string {
 379
 380         $namefields = fields::get_name_fields(true);
 381
 382         // Create a dummy user object containing all name fields.
 383         $dummyuser = (object) array_combine($namefields, $namefields);
 384         $dummyfullname = fullname($dummyuser, true);
 385
 386         // Extract any name fields from the fullname format in the order that they appear.
 387         $matchednames = array_values(order_in_string($namefields, $dummyfullname));
 388
 389         $userfields = array_map(static function(string $userfield) use ($usertablealias): string {
 390             if (!empty($usertablealias)) {
 391                 $userfield = "{$usertablealias}.{$userfield}";
 392             }
 393
 394             return $userfield;
 395         }, $matchednames);
 396
 397         return implode(', ', $userfields);
 398     }
 399
 400     /**
 401      * User fields
 402      *
 403      * @return lang_string[]
 404      */
 405     protected function get_user_fields(): array {
 406         return [
 407             'firstname' => new lang_string('firstname'),
 408             'lastname' => new lang_string('lastname'),
 409             'email' => new lang_string('email'),
 410             'city' => new lang_string('city'),
 411             'country' => new lang_string('country'),
 412             'description' => new lang_string('description'),
 413             'firstnamephonetic' => new lang_string('firstnamephonetic'),
 414             'lastnamephonetic' => new lang_string('lastnamephonetic'),
 415             'middlename' => new lang_string('middlename'),
 416             'alternatename' => new lang_string('alternatename'),
 417             'idnumber' => new lang_string('idnumber'),
 418             'institution' => new lang_string('institution'),
 419             'department' => new lang_string('department'),
 420             'phone1' => new lang_string('phone1'),
 421             'phone2' => new lang_string('phone2'),
 422             'address' => new lang_string('address'),
 423             'lastaccess' => new lang_string('lastaccess'),
 424             'suspended' => new lang_string('suspended'),
 425             'confirmed' => new lang_string('confirmed', 'admin'),
 426             'username' => new lang_string('username'),
 427             'moodlenetprofile' => new lang_string('moodlenetprofile', 'user'),
 428             'timecreated' => new lang_string('timecreated', 'core_reportbuilder'),
 429         ];
 430     }
 431
 432     /**
 433      * Return appropriate column type for given user field
 434      *
 435      * @param string $userfield
 436      * @return int
 437      */
 438     protected function get_user_field_type(string $userfield): int {
 439         switch ($userfield) {
 440             case 'description':
 441                 $fieldtype = column::TYPE_LONGTEXT;
 442                 break;
 443             case 'confirmed':
 444             case 'suspended':
 445                 $fieldtype = column::TYPE_BOOLEAN;
 446                 break;
 447             case 'lastaccess':
 448             case 'timecreated':
 449                 $fieldtype = column::TYPE_TIMESTAMP;
 450                 break;
 451             default:
 452                 $fieldtype = column::TYPE_TEXT;
 453                 break;
 454         }
 455
 456         return $fieldtype;
 457     }
 458
 459     /**
 460      * Return list of all available filters
 461      *
 462      * @return filter[]
 463      */
 464     protected function get_all_filters(): array {
 465         global $DB;
 466
 467         $filters = [];
 468         $tablealias = $this->get_table_alias('user');
 469
 470         // Fullname filter.
 471         $canviewfullnames = has_capability('moodle/site:viewfullnames', context_system::instance());
 472         [$fullnamesql, $fullnameparams] = fields::get_sql_fullname($tablealias, $canviewfullnames);
 473         $filters[] = (new filter(
 474             text::class,
 475             'fullname',
 476             new lang_string('fullname'),
 477             $this->get_entity_name(),
 478             $fullnamesql,
 479             $fullnameparams
 480         ))
 481             ->add_joins($this->get_joins());
 482
 483         // User fields filters.
 484         $fields = $this->get_user_fields();
 485         foreach ($fields as $field => $name) {
 486             // Filtering isn't supported for LONGTEXT fields on Oracle.
 487             if ($this->get_user_field_type($field) === column::TYPE_LONGTEXT &&
 488                     $DB->get_dbfamily() === 'oracle') {
 489
 490                 continue;
 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                 $tablealias . '.' . $field
 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 }