[Summary view]

   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\filters;
  20  
  21  use core_reportbuilder\local\helpers\database;
  22  
  23  /**
  24   * Text report filter
  25   *
  26   * @package     core_reportbuilder
  27   * @copyright   2021 David Matamoros <davidmc@moodle.com>
  28   * @license     http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
  29   */
  30  class text extends base {
  31  
  32      /** @var int */
  33      public const ANY_VALUE = 0;
  34  
  35      /** @var int */
  36      public const CONTAINS = 1;
  37  
  38      /** @var int */
  39      public const DOES_NOT_CONTAIN = 2;
  40  
  41      /** @var int */
  42      public const IS_EQUAL_TO = 3;
  43  
  44      /** @var int */
  45      public const IS_NOT_EQUAL_TO = 4;
  46  
  47      /** @var int */
  48      public const STARTS_WITH = 5;
  49  
  50      /** @var int */
  51      public const ENDS_WITH = 6;
  52  
  53      /** @var int */
  54      public const IS_EMPTY = 7;
  55  
  56      /** @var int */
  57      public const IS_NOT_EMPTY = 8;
  58  
  59      /**
  60       * Return an array of operators available for this filter
  61       *
  62       * @return array of comparison operators
  63       */
  64      private function get_operators() : array {
  65          $operators = [
  66              self::ANY_VALUE => get_string('filterisanyvalue', 'core_reportbuilder'),
  67              self::CONTAINS => get_string('filtercontains', 'core_reportbuilder'),
  68              self::DOES_NOT_CONTAIN => get_string('filterdoesnotcontain', 'core_reportbuilder'),
  69              self::IS_EQUAL_TO => get_string('filterisequalto', 'core_reportbuilder'),
  70              self::IS_NOT_EQUAL_TO => get_string('filterisnotequalto', 'core_reportbuilder'),
  71              self::STARTS_WITH => get_string('filterstartswith', 'core_reportbuilder'),
  72              self::ENDS_WITH => get_string('filterendswith', 'core_reportbuilder'),
  73              self::IS_EMPTY => get_string('filterisempty', 'core_reportbuilder'),
  74              self::IS_NOT_EMPTY => get_string('filterisnotempty', 'core_reportbuilder')
  75          ];
  76  
  77          return $this->filter->restrict_limited_operators($operators);
  78      }
  79  
  80      /**
  81       * Adds controls specific to this filter in the form.
  82       *
  83       * Operator selector use the "$this->name . '_operator'" naming convention and the fields to enter custom values should
  84       * use "$this->name . '_value'" or _value1/_value2/... in case there is more than one field for their naming.
  85       *
  86       * @param \MoodleQuickForm $mform
  87       */
  88      public function setup_form(\MoodleQuickForm $mform): void {
  89          $elements = [];
  90          $elements['operator'] = $mform->createElement('select', $this->name . '_operator',
  91              get_string('filterfieldoperator', 'core_reportbuilder', $this->get_header()), $this->get_operators());
  92          $elements['value'] = $mform->createElement('text', $this->name . '_value',
  93              get_string('filterfieldvalue', 'core_reportbuilder', $this->get_header()));
  94  
  95          $mform->addElement('group', $this->name . '_group', '', $elements, '', false);
  96  
  97          $mform->setType($this->name . '_value', PARAM_RAW);
  98          $mform->hideIf($this->name . '_value', $this->name . '_operator', 'eq', self::ANY_VALUE);
  99          $mform->hideIf($this->name . '_value', $this->name . '_operator', 'eq', self::IS_EMPTY);
 100          $mform->hideIf($this->name . '_value', $this->name . '_operator', 'eq', self::IS_NOT_EMPTY);
 101      }
 102  
 103      /**
 104       * Return filter SQL
 105       *
 106       * @param array|null $values
 107       * @return array array of two elements - SQL query and named parameters
 108       */
 109      public function get_sql_filter(?array $values) : array {
 110          global $DB;
 111          $name = database::generate_param_name();
 112  
 113          if (!$values) {
 114              return ['', []];
 115          }
 116  
 117          $operator = (int) ($values["{$this->name}_operator"] ?? self::ANY_VALUE);
 118          $value = $values["{$this->name}_value"] ?? '';
 119  
 120          $fieldsql = $this->filter->get_field_sql();
 121          $params = $this->filter->get_field_params();
 122  
 123          // Validate filter form values.
 124          if (!$this->validate_filter_values($operator, $value)) {
 125              // Filter configuration is invalid. Ignore the filter.
 126              return ['', []];
 127          }
 128  
 129          switch($operator) {
 130              case self::CONTAINS:
 131                  $res = $DB->sql_like($fieldsql, ":$name", false, false);
 132                  $value = $DB->sql_like_escape($value);
 133                  $params[$name] = "%$value%";
 134                  break;
 135              case self::DOES_NOT_CONTAIN:
 136                  $res = $DB->sql_like($fieldsql, ":$name", false, false, true);
 137                  $value = $DB->sql_like_escape($value);
 138                  $params[$name] = "%$value%";
 139                  break;
 140              case self::IS_EQUAL_TO:
 141                  $res = $DB->sql_equal($fieldsql, ":$name", false, false);
 142                  $params[$name] = $value;
 143                  break;
 144              case self::IS_NOT_EQUAL_TO:
 145                  $res = $DB->sql_equal($fieldsql, ":$name", false, false, true);
 146                  $params[$name] = $value;
 147                  break;
 148              case self::STARTS_WITH:
 149                  $res = $DB->sql_like($fieldsql, ":$name", false, false);
 150                  $value = $DB->sql_like_escape($value);
 151                  $params[$name] = "$value%";
 152                  break;
 153              case self::ENDS_WITH:
 154                  $res = $DB->sql_like($fieldsql, ":$name", false, false);
 155                  $value = $DB->sql_like_escape($value);
 156                  $params[$name] = "%$value";
 157                  break;
 158              case self::IS_EMPTY:
 159                  $paramempty = database::generate_param_name();
 160                  $res = "COALESCE({$fieldsql}, :{$paramempty}) = :{$name}";
 161                  $params[$paramempty] = $params[$name] = '';
 162                  break;
 163              case self::IS_NOT_EMPTY:
 164                  $paramempty = database::generate_param_name();
 165                  $res = "COALESCE({$fieldsql}, :{$paramempty}) != :{$name}";
 166                  $params[$paramempty] = $params[$name] = '';
 167                  break;
 168              default:
 169                  // Filter configuration is invalid. Ignore the filter.
 170                  return ['', []];
 171          }
 172          return array($res, $params);
 173      }
 174  
 175      /**
 176       * Validate filter form values
 177       *
 178       * @param int $operator
 179       * @param string|null $value
 180       * @return bool
 181       */
 182      private function validate_filter_values(int $operator, ?string $value): bool {
 183          $operatorsthatdontrequirevalue = [
 184              self::ANY_VALUE,
 185              self::IS_EMPTY,
 186              self::IS_NOT_EMPTY,
 187          ];
 188  
 189          if ($value === '' && !in_array($operator, $operatorsthatdontrequirevalue)) {
 190              return false;
 191          }
 192  
 193          return true;
 194      }
 195  
 196      /**
 197       * Return sample filter values
 198       *
 199       * @return array
 200       */
 201      public function get_sample_values(): array {
 202          return [
 203              "{$this->name}_operator" => self::IS_EQUAL_TO,
 204              "{$this->name}_value" => 'test',
 205          ];
 206      }
 207  }