[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\helpers;
  20  
  21  use context_system;
  22  use core_text;
  23  use core_reportbuilder\local\filters\boolean_select;
  24  use core_reportbuilder\local\filters\date;
  25  use core_reportbuilder\local\filters\select;
  26  use core_reportbuilder\local\filters\text;
  27  use core_reportbuilder\local\report\column;
  28  use core_reportbuilder\local\report\filter;
  29  use lang_string;
  30  use profile_field_base;
  31  use stdClass;
  32  
  33  defined('MOODLE_INTERNAL') || die();
  34  
  35  global $CFG;
  36  require_once($CFG->dirroot.'/user/profile/lib.php');
  37  
  38  /**
  39   * Helper class for user profile fields.
  40   *
  41   * @package   core_reportbuilder
  42   * @copyright 2021 David Matamoros <davidmc@moodle.com>
  43   * @license   http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
  44   */
  45  class user_profile_fields {
  46  
  47      /** @var array user profile fields */
  48      private $userprofilefields;
  49  
  50      /** @var string $entityname Name of the entity */
  51      private $entityname;
  52  
  53      /** @var int $usertablefieldalias The user table/field alias */
  54      private $usertablefieldalias;
  55  
  56      /** @var array additional joins */
  57      private $joins = [];
  58  
  59      /**
  60       * Class userprofilefields constructor.
  61       *
  62       * @param string $usertablefieldalias The user table/field alias used when adding columns and filters.
  63       * @param string $entityname The entity name used when adding columns and filters.
  64       */
  65      public function __construct(string $usertablefieldalias, string $entityname) {
  66          $this->usertablefieldalias = $usertablefieldalias;
  67          $this->entityname = $entityname;
  68          $this->userprofilefields = $this->get_user_profile_fields();
  69      }
  70  
  71      /**
  72       * Retrieves the list of available/visible user profile fields
  73       *
  74       * @return profile_field_base[]
  75       */
  76      private function get_user_profile_fields(): array {
  77          return array_filter(profile_get_user_fields_with_data(0), static function(profile_field_base $profilefield): bool {
  78              return $profilefield->is_visible();
  79          });
  80      }
  81  
  82      /**
  83       * Additional join that is needed.
  84       *
  85       * @param string $join
  86       * @return self
  87       */
  88      public function add_join(string $join): self {
  89          $this->joins[trim($join)] = trim($join);
  90          return $this;
  91      }
  92  
  93      /**
  94       * Additional joins that are needed.
  95       *
  96       * @param array $joins
  97       * @return self
  98       */
  99      public function add_joins(array $joins): self {
 100          foreach ($joins as $join) {
 101              $this->add_join($join);
 102          }
 103          return $this;
 104      }
 105  
 106      /**
 107       * Return joins
 108       *
 109       * @return string[]
 110       */
 111      private function get_joins(): array {
 112          return array_values($this->joins);
 113      }
 114  
 115      /**
 116       * Generate table alias for given profile field
 117       *
 118       * The entity name is used to ensure the alias differs when the entity is used multiple times within the same report, each
 119       * having their own table alias/join
 120       *
 121       * @param profile_field_base $profilefield
 122       * @return string
 123       */
 124      private function get_table_alias(profile_field_base $profilefield): string {
 125          static $aliases = [];
 126  
 127          $aliaskey = "{$this->entityname}_{$profilefield->fieldid}";
 128          if (!array_key_exists($aliaskey, $aliases)) {
 129              $aliases[$aliaskey] = database::generate_alias();
 130          }
 131  
 132          return $aliases[$aliaskey];
 133      }
 134  
 135      /**
 136       * Generate table join for given profile field
 137       *
 138       * @param profile_field_base $profilefield
 139       * @return string
 140       */
 141      private function get_table_join(profile_field_base $profilefield): string {
 142          $userinfotablealias = $this->get_table_alias($profilefield);
 143  
 144          return "LEFT JOIN {user_info_data} {$userinfotablealias}
 145                         ON {$userinfotablealias}.userid = {$this->usertablefieldalias}
 146                        AND {$userinfotablealias}.fieldid = {$profilefield->fieldid}";
 147      }
 148  
 149      /**
 150       * Return the user profile fields visible columns.
 151       *
 152       * @return column[]
 153       */
 154      public function get_columns(): array {
 155          global $DB;
 156  
 157          $columns = [];
 158          foreach ($this->userprofilefields as $profilefield) {
 159              $columntype = $this->get_user_field_type($profilefield->field->datatype);
 160              $columnfieldsql = $this->get_table_alias($profilefield) . '.data';
 161  
 162              // Numeric (checkbox/time) fields should be cast, as should all fields for Oracle, for aggregation support.
 163              if ($columntype === column::TYPE_BOOLEAN || $columntype === column::TYPE_TIMESTAMP) {
 164                  $columnfieldsql = "CASE WHEN {$columnfieldsql} IS NULL THEN NULL ELSE " .
 165                      $DB->sql_cast_char2int($columnfieldsql, true) . " END";
 166              } else if ($DB->get_dbfamily() === 'oracle') {
 167                  $columnfieldsql = $DB->sql_order_by_text($columnfieldsql, 1024);
 168              }
 169  
 170              $columns[] = (new column(
 171                  'profilefield_' . core_text::strtolower($profilefield->field->shortname),
 172                  new lang_string('customfieldcolumn', 'core_reportbuilder',
 173                      format_string($profilefield->field->name, true,
 174                          ['escape' => false, 'context' => context_system::instance()])),
 175                  $this->entityname
 176              ))
 177                  ->add_joins($this->get_joins())
 178                  ->add_join($this->get_table_join($profilefield))
 179                  ->add_field($columnfieldsql, 'data')
 180                  ->set_type($columntype)
 181                  ->set_is_sortable($columntype !== column::TYPE_LONGTEXT)
 182                  ->add_callback(static function($value, stdClass $row, profile_field_base $field): string {
 183                      if ($value === null) {
 184                          return '';
 185                      }
 186  
 187                      $field->data = $value;
 188                      return (string) $field->display_data();
 189                  }, $profilefield);
 190          }
 191  
 192          return $columns;
 193      }
 194  
 195      /**
 196       * Get custom user profile fields filters.
 197       *
 198       * @return filter[]
 199       */
 200      public function get_filters(): array {
 201          global $DB;
 202  
 203          $filters = [];
 204          foreach ($this->userprofilefields as $profilefield) {
 205              $field = $this->get_table_alias($profilefield) . '.data';
 206              $params = [];
 207  
 208              switch ($profilefield->field->datatype) {
 209                  case 'checkbox':
 210                      $classname = boolean_select::class;
 211                      $fieldsql = "COALESCE(" . $DB->sql_cast_char2int($field, true) . ", 0)";
 212                      break;
 213                  case 'datetime':
 214                      $classname = date::class;
 215                      $fieldsql = $DB->sql_cast_char2int($field, true);
 216                      break;
 217                  case 'menu':
 218                      $classname = select::class;
 219  
 220                      $emptyparam = database::generate_param_name();
 221                      $fieldsql = "COALESCE(" . $DB->sql_compare_text($field, 255) . ", :{$emptyparam})";
 222                      $params[$emptyparam] = '';
 223  
 224                      break;
 225                  case 'text':
 226                  case 'textarea':
 227                  default:
 228                      $classname = text::class;
 229  
 230                      $emptyparam = database::generate_param_name();
 231                      $fieldsql = "COALESCE(" . $DB->sql_compare_text($field, 255) . ", :{$emptyparam})";
 232                      $params[$emptyparam] = '';
 233  
 234                      break;
 235              }
 236  
 237              $filter = (new filter(
 238                  $classname,
 239                  'profilefield_' . core_text::strtolower($profilefield->field->shortname),
 240                  new lang_string('customfieldcolumn', 'core_reportbuilder',
 241                      format_string($profilefield->field->name, true,
 242                          ['escape' => false, 'context' => context_system::instance()])),
 243                  $this->entityname,
 244                  $fieldsql,
 245                  $params
 246              ))
 247                  ->add_joins($this->get_joins())
 248                  ->add_join($this->get_table_join($profilefield));
 249  
 250              // If menu type then set filter options as appropriate.
 251              if ($profilefield->field->datatype === 'menu') {
 252                  $filter->set_options($profilefield->options);
 253              }
 254  
 255              $filters[] = $filter;
 256          }
 257  
 258          return $filters;
 259      }
 260  
 261      /**
 262       * Get user profile field type for report.
 263       *
 264       * @param string $userfield user field.
 265       * @return int the constant equivalent to this custom field type.
 266       */
 267      protected function get_user_field_type(string $userfield): int {
 268          switch ($userfield) {
 269              case 'checkbox':
 270                  $customfieldtype = column::TYPE_BOOLEAN;
 271                  break;
 272              case 'datetime':
 273                  $customfieldtype = column::TYPE_TIMESTAMP;
 274                  break;
 275              case 'textarea':
 276                  $customfieldtype = column::TYPE_LONGTEXT;
 277                  break;
 278              case 'menu':
 279              case 'text':
 280              default:
 281                  $customfieldtype = column::TYPE_TEXT;
 282                  break;
 283          }
 284          return $customfieldtype;
 285      }
 286  }