[Summary view]

Differences Between: [Versions 310 and 401] [Versions 311 and 401] [Versions 39 and 401]

   1  <?php
   2  
   3  namespace PhpOffice\PhpSpreadsheet\Worksheet\AutoFilter\Column;
   4  
   5  use PhpOffice\PhpSpreadsheet\Exception as PhpSpreadsheetException;
   6  use PhpOffice\PhpSpreadsheet\Worksheet\AutoFilter\Column;
   7  
   8  class Rule
   9  {
  10      const AUTOFILTER_RULETYPE_FILTER = 'filter';
  11      const AUTOFILTER_RULETYPE_DATEGROUP = 'dateGroupItem';
  12      const AUTOFILTER_RULETYPE_CUSTOMFILTER = 'customFilter';
  13      const AUTOFILTER_RULETYPE_DYNAMICFILTER = 'dynamicFilter';
  14      const AUTOFILTER_RULETYPE_TOPTENFILTER = 'top10Filter';
  15  
  16      private const RULE_TYPES = [
  17          //    Currently we're not handling
  18          //        colorFilter
  19          //        extLst
  20          //        iconFilter
  21          self::AUTOFILTER_RULETYPE_FILTER,
  22          self::AUTOFILTER_RULETYPE_DATEGROUP,
  23          self::AUTOFILTER_RULETYPE_CUSTOMFILTER,
  24          self::AUTOFILTER_RULETYPE_DYNAMICFILTER,
  25          self::AUTOFILTER_RULETYPE_TOPTENFILTER,
  26      ];
  27  
  28      const AUTOFILTER_RULETYPE_DATEGROUP_YEAR = 'year';
  29      const AUTOFILTER_RULETYPE_DATEGROUP_MONTH = 'month';
  30      const AUTOFILTER_RULETYPE_DATEGROUP_DAY = 'day';
  31      const AUTOFILTER_RULETYPE_DATEGROUP_HOUR = 'hour';
  32      const AUTOFILTER_RULETYPE_DATEGROUP_MINUTE = 'minute';
  33      const AUTOFILTER_RULETYPE_DATEGROUP_SECOND = 'second';
  34  
  35      private const DATE_TIME_GROUPS = [
  36          self::AUTOFILTER_RULETYPE_DATEGROUP_YEAR,
  37          self::AUTOFILTER_RULETYPE_DATEGROUP_MONTH,
  38          self::AUTOFILTER_RULETYPE_DATEGROUP_DAY,
  39          self::AUTOFILTER_RULETYPE_DATEGROUP_HOUR,
  40          self::AUTOFILTER_RULETYPE_DATEGROUP_MINUTE,
  41          self::AUTOFILTER_RULETYPE_DATEGROUP_SECOND,
  42      ];
  43  
  44      const AUTOFILTER_RULETYPE_DYNAMIC_YESTERDAY = 'yesterday';
  45      const AUTOFILTER_RULETYPE_DYNAMIC_TODAY = 'today';
  46      const AUTOFILTER_RULETYPE_DYNAMIC_TOMORROW = 'tomorrow';
  47      const AUTOFILTER_RULETYPE_DYNAMIC_YEARTODATE = 'yearToDate';
  48      const AUTOFILTER_RULETYPE_DYNAMIC_THISYEAR = 'thisYear';
  49      const AUTOFILTER_RULETYPE_DYNAMIC_THISQUARTER = 'thisQuarter';
  50      const AUTOFILTER_RULETYPE_DYNAMIC_THISMONTH = 'thisMonth';
  51      const AUTOFILTER_RULETYPE_DYNAMIC_THISWEEK = 'thisWeek';
  52      const AUTOFILTER_RULETYPE_DYNAMIC_LASTYEAR = 'lastYear';
  53      const AUTOFILTER_RULETYPE_DYNAMIC_LASTQUARTER = 'lastQuarter';
  54      const AUTOFILTER_RULETYPE_DYNAMIC_LASTMONTH = 'lastMonth';
  55      const AUTOFILTER_RULETYPE_DYNAMIC_LASTWEEK = 'lastWeek';
  56      const AUTOFILTER_RULETYPE_DYNAMIC_NEXTYEAR = 'nextYear';
  57      const AUTOFILTER_RULETYPE_DYNAMIC_NEXTQUARTER = 'nextQuarter';
  58      const AUTOFILTER_RULETYPE_DYNAMIC_NEXTMONTH = 'nextMonth';
  59      const AUTOFILTER_RULETYPE_DYNAMIC_NEXTWEEK = 'nextWeek';
  60      const AUTOFILTER_RULETYPE_DYNAMIC_MONTH_1 = 'M1';
  61      const AUTOFILTER_RULETYPE_DYNAMIC_JANUARY = self::AUTOFILTER_RULETYPE_DYNAMIC_MONTH_1;
  62      const AUTOFILTER_RULETYPE_DYNAMIC_MONTH_2 = 'M2';
  63      const AUTOFILTER_RULETYPE_DYNAMIC_FEBRUARY = self::AUTOFILTER_RULETYPE_DYNAMIC_MONTH_2;
  64      const AUTOFILTER_RULETYPE_DYNAMIC_MONTH_3 = 'M3';
  65      const AUTOFILTER_RULETYPE_DYNAMIC_MARCH = self::AUTOFILTER_RULETYPE_DYNAMIC_MONTH_3;
  66      const AUTOFILTER_RULETYPE_DYNAMIC_MONTH_4 = 'M4';
  67      const AUTOFILTER_RULETYPE_DYNAMIC_APRIL = self::AUTOFILTER_RULETYPE_DYNAMIC_MONTH_4;
  68      const AUTOFILTER_RULETYPE_DYNAMIC_MONTH_5 = 'M5';
  69      const AUTOFILTER_RULETYPE_DYNAMIC_MAY = self::AUTOFILTER_RULETYPE_DYNAMIC_MONTH_5;
  70      const AUTOFILTER_RULETYPE_DYNAMIC_MONTH_6 = 'M6';
  71      const AUTOFILTER_RULETYPE_DYNAMIC_JUNE = self::AUTOFILTER_RULETYPE_DYNAMIC_MONTH_6;
  72      const AUTOFILTER_RULETYPE_DYNAMIC_MONTH_7 = 'M7';
  73      const AUTOFILTER_RULETYPE_DYNAMIC_JULY = self::AUTOFILTER_RULETYPE_DYNAMIC_MONTH_7;
  74      const AUTOFILTER_RULETYPE_DYNAMIC_MONTH_8 = 'M8';
  75      const AUTOFILTER_RULETYPE_DYNAMIC_AUGUST = self::AUTOFILTER_RULETYPE_DYNAMIC_MONTH_8;
  76      const AUTOFILTER_RULETYPE_DYNAMIC_MONTH_9 = 'M9';
  77      const AUTOFILTER_RULETYPE_DYNAMIC_SEPTEMBER = self::AUTOFILTER_RULETYPE_DYNAMIC_MONTH_9;
  78      const AUTOFILTER_RULETYPE_DYNAMIC_MONTH_10 = 'M10';
  79      const AUTOFILTER_RULETYPE_DYNAMIC_OCTOBER = self::AUTOFILTER_RULETYPE_DYNAMIC_MONTH_10;
  80      const AUTOFILTER_RULETYPE_DYNAMIC_MONTH_11 = 'M11';
  81      const AUTOFILTER_RULETYPE_DYNAMIC_NOVEMBER = self::AUTOFILTER_RULETYPE_DYNAMIC_MONTH_11;
  82      const AUTOFILTER_RULETYPE_DYNAMIC_MONTH_12 = 'M12';
  83      const AUTOFILTER_RULETYPE_DYNAMIC_DECEMBER = self::AUTOFILTER_RULETYPE_DYNAMIC_MONTH_12;
  84      const AUTOFILTER_RULETYPE_DYNAMIC_QUARTER_1 = 'Q1';
  85      const AUTOFILTER_RULETYPE_DYNAMIC_QUARTER_2 = 'Q2';
  86      const AUTOFILTER_RULETYPE_DYNAMIC_QUARTER_3 = 'Q3';
  87      const AUTOFILTER_RULETYPE_DYNAMIC_QUARTER_4 = 'Q4';
  88      const AUTOFILTER_RULETYPE_DYNAMIC_ABOVEAVERAGE = 'aboveAverage';
  89      const AUTOFILTER_RULETYPE_DYNAMIC_BELOWAVERAGE = 'belowAverage';
  90  
  91      private const DYNAMIC_TYPES = [
  92          self::AUTOFILTER_RULETYPE_DYNAMIC_YESTERDAY,
  93          self::AUTOFILTER_RULETYPE_DYNAMIC_TODAY,
  94          self::AUTOFILTER_RULETYPE_DYNAMIC_TOMORROW,
  95          self::AUTOFILTER_RULETYPE_DYNAMIC_YEARTODATE,
  96          self::AUTOFILTER_RULETYPE_DYNAMIC_THISYEAR,
  97          self::AUTOFILTER_RULETYPE_DYNAMIC_THISQUARTER,
  98          self::AUTOFILTER_RULETYPE_DYNAMIC_THISMONTH,
  99          self::AUTOFILTER_RULETYPE_DYNAMIC_THISWEEK,
 100          self::AUTOFILTER_RULETYPE_DYNAMIC_LASTYEAR,
 101          self::AUTOFILTER_RULETYPE_DYNAMIC_LASTQUARTER,
 102          self::AUTOFILTER_RULETYPE_DYNAMIC_LASTMONTH,
 103          self::AUTOFILTER_RULETYPE_DYNAMIC_LASTWEEK,
 104          self::AUTOFILTER_RULETYPE_DYNAMIC_NEXTYEAR,
 105          self::AUTOFILTER_RULETYPE_DYNAMIC_NEXTQUARTER,
 106          self::AUTOFILTER_RULETYPE_DYNAMIC_NEXTMONTH,
 107          self::AUTOFILTER_RULETYPE_DYNAMIC_NEXTWEEK,
 108          self::AUTOFILTER_RULETYPE_DYNAMIC_MONTH_1,
 109          self::AUTOFILTER_RULETYPE_DYNAMIC_MONTH_2,
 110          self::AUTOFILTER_RULETYPE_DYNAMIC_MONTH_3,
 111          self::AUTOFILTER_RULETYPE_DYNAMIC_MONTH_4,
 112          self::AUTOFILTER_RULETYPE_DYNAMIC_MONTH_5,
 113          self::AUTOFILTER_RULETYPE_DYNAMIC_MONTH_6,
 114          self::AUTOFILTER_RULETYPE_DYNAMIC_MONTH_7,
 115          self::AUTOFILTER_RULETYPE_DYNAMIC_MONTH_8,
 116          self::AUTOFILTER_RULETYPE_DYNAMIC_MONTH_9,
 117          self::AUTOFILTER_RULETYPE_DYNAMIC_MONTH_10,
 118          self::AUTOFILTER_RULETYPE_DYNAMIC_MONTH_11,
 119          self::AUTOFILTER_RULETYPE_DYNAMIC_MONTH_12,
 120          self::AUTOFILTER_RULETYPE_DYNAMIC_QUARTER_1,
 121          self::AUTOFILTER_RULETYPE_DYNAMIC_QUARTER_2,
 122          self::AUTOFILTER_RULETYPE_DYNAMIC_QUARTER_3,
 123          self::AUTOFILTER_RULETYPE_DYNAMIC_QUARTER_4,
 124          self::AUTOFILTER_RULETYPE_DYNAMIC_ABOVEAVERAGE,
 125          self::AUTOFILTER_RULETYPE_DYNAMIC_BELOWAVERAGE,
 126      ];
 127  
 128      // Filter rule operators for filter and customFilter types.
 129      const AUTOFILTER_COLUMN_RULE_EQUAL = 'equal';
 130      const AUTOFILTER_COLUMN_RULE_NOTEQUAL = 'notEqual';
 131      const AUTOFILTER_COLUMN_RULE_GREATERTHAN = 'greaterThan';
 132      const AUTOFILTER_COLUMN_RULE_GREATERTHANOREQUAL = 'greaterThanOrEqual';
 133      const AUTOFILTER_COLUMN_RULE_LESSTHAN = 'lessThan';
 134      const AUTOFILTER_COLUMN_RULE_LESSTHANOREQUAL = 'lessThanOrEqual';
 135  
 136      private const OPERATORS = [
 137          self::AUTOFILTER_COLUMN_RULE_EQUAL,
 138          self::AUTOFILTER_COLUMN_RULE_NOTEQUAL,
 139          self::AUTOFILTER_COLUMN_RULE_GREATERTHAN,
 140          self::AUTOFILTER_COLUMN_RULE_GREATERTHANOREQUAL,
 141          self::AUTOFILTER_COLUMN_RULE_LESSTHAN,
 142          self::AUTOFILTER_COLUMN_RULE_LESSTHANOREQUAL,
 143      ];
 144  
 145      const AUTOFILTER_COLUMN_RULE_TOPTEN_BY_VALUE = 'byValue';
 146      const AUTOFILTER_COLUMN_RULE_TOPTEN_PERCENT = 'byPercent';
 147  
 148      private const TOP_TEN_VALUE = [
 149          self::AUTOFILTER_COLUMN_RULE_TOPTEN_BY_VALUE,
 150          self::AUTOFILTER_COLUMN_RULE_TOPTEN_PERCENT,
 151      ];
 152  
 153      const AUTOFILTER_COLUMN_RULE_TOPTEN_TOP = 'top';
 154      const AUTOFILTER_COLUMN_RULE_TOPTEN_BOTTOM = 'bottom';
 155  
 156      private const TOP_TEN_TYPE = [
 157          self::AUTOFILTER_COLUMN_RULE_TOPTEN_TOP,
 158          self::AUTOFILTER_COLUMN_RULE_TOPTEN_BOTTOM,
 159      ];
 160  
 161      //  Unimplented Rule Operators (Numeric, Boolean etc)
 162      //    const AUTOFILTER_COLUMN_RULE_BETWEEN = 'between';        //    greaterThanOrEqual 1 && lessThanOrEqual 2
 163      // Rule Operators (Numeric Special) which are translated to standard numeric operators with calculated values
 164      // Rule Operators (String) which are set as wild-carded values
 165      //    const AUTOFILTER_COLUMN_RULE_BEGINSWITH            = 'beginsWith';            // A*
 166      //    const AUTOFILTER_COLUMN_RULE_ENDSWITH            = 'endsWith';            // *Z
 167      //    const AUTOFILTER_COLUMN_RULE_CONTAINS            = 'contains';            // *B*
 168      //    const AUTOFILTER_COLUMN_RULE_DOESNTCONTAIN        = 'notEqual';            //    notEqual *B*
 169      // Rule Operators (Date Special) which are translated to standard numeric operators with calculated values
 170      //    const AUTOFILTER_COLUMN_RULE_BEFORE                = 'lessThan';
 171      //    const AUTOFILTER_COLUMN_RULE_AFTER                = 'greaterThan';
 172  
 173      /**
 174       * Autofilter Column.
 175       *
 176       * @var ?Column
 177       */
 178      private $parent;
 179  
 180      /**
 181       * Autofilter Rule Type.
 182       *
 183       * @var string
 184       */
 185      private $ruleType = self::AUTOFILTER_RULETYPE_FILTER;
 186  
 187      /**
 188       * Autofilter Rule Value.
 189       *
 190       * @var int|int[]|string|string[]
 191       */
 192      private $value = '';
 193  
 194      /**
 195       * Autofilter Rule Operator.
 196       *
 197       * @var string
 198       */
 199      private $operator = self::AUTOFILTER_COLUMN_RULE_EQUAL;
 200  
 201      /**
 202       * DateTimeGrouping Group Value.
 203       *
 204       * @var string
 205       */
 206      private $grouping = '';
 207  
 208      /**
 209       * Create a new Rule.
 210       */
 211      public function __construct(?Column $parent = null)
 212      {
 213          $this->parent = $parent;
 214      }
 215  
 216      private function setEvaluatedFalse(): void
 217      {
 218          if ($this->parent !== null) {
 219              $this->parent->setEvaluatedFalse();
 220          }
 221      }
 222  
 223      /**
 224       * Get AutoFilter Rule Type.
 225       *
 226       * @return string
 227       */
 228      public function getRuleType()
 229      {
 230          return $this->ruleType;
 231      }
 232  
 233      /**
 234       * Set AutoFilter Rule Type.
 235       *
 236       * @param string $ruleType see self::AUTOFILTER_RULETYPE_*
 237       *
 238       * @return $this
 239       */
 240      public function setRuleType($ruleType)
 241      {
 242          $this->setEvaluatedFalse();
 243          if (!in_array($ruleType, self::RULE_TYPES)) {
 244              throw new PhpSpreadsheetException('Invalid rule type for column AutoFilter Rule.');
 245          }
 246  
 247          $this->ruleType = $ruleType;
 248  
 249          return $this;
 250      }
 251  
 252      /**
 253       * Get AutoFilter Rule Value.
 254       *
 255       * @return int|int[]|string|string[]
 256       */
 257      public function getValue()
 258      {
 259          return $this->value;
 260      }
 261  
 262      /**
 263       * Set AutoFilter Rule Value.
 264       *
 265       * @param int|int[]|string|string[] $value
 266       *
 267       * @return $this
 268       */
 269      public function setValue($value)
 270      {
 271          $this->setEvaluatedFalse();
 272          if (is_array($value)) {
 273              $grouping = -1;
 274              foreach ($value as $key => $v) {
 275                  //    Validate array entries
 276                  if (!in_array($key, self::DATE_TIME_GROUPS)) {
 277                      //    Remove any invalid entries from the value array
 278                      unset($value[$key]);
 279                  } else {
 280                      //    Work out what the dateTime grouping will be
 281                      $grouping = max($grouping, array_search($key, self::DATE_TIME_GROUPS));
 282                  }
 283              }
 284              if (count($value) == 0) {
 285                  throw new PhpSpreadsheetException('Invalid rule value for column AutoFilter Rule.');
 286              }
 287              //    Set the dateTime grouping that we've anticipated
 288              $this->setGrouping(self::DATE_TIME_GROUPS[$grouping]);
 289          }
 290          $this->value = $value;
 291  
 292          return $this;
 293      }
 294  
 295      /**
 296       * Get AutoFilter Rule Operator.
 297       *
 298       * @return string
 299       */
 300      public function getOperator()
 301      {
 302          return $this->operator;
 303      }
 304  
 305      /**
 306       * Set AutoFilter Rule Operator.
 307       *
 308       * @param string $operator see self::AUTOFILTER_COLUMN_RULE_*
 309       *
 310       * @return $this
 311       */
 312      public function setOperator($operator)
 313      {
 314          $this->setEvaluatedFalse();
 315          if (empty($operator)) {
 316              $operator = self::AUTOFILTER_COLUMN_RULE_EQUAL;
 317          }
 318          if (
 319              (!in_array($operator, self::OPERATORS)) &&
 320              (!in_array($operator, self::TOP_TEN_VALUE))
 321          ) {
 322              throw new PhpSpreadsheetException('Invalid operator for column AutoFilter Rule.');
 323          }
 324          $this->operator = $operator;
 325  
 326          return $this;
 327      }
 328  
 329      /**
 330       * Get AutoFilter Rule Grouping.
 331       *
 332       * @return string
 333       */
 334      public function getGrouping()
 335      {
 336          return $this->grouping;
 337      }
 338  
 339      /**
 340       * Set AutoFilter Rule Grouping.
 341       *
 342       * @param string $grouping
 343       *
 344       * @return $this
 345       */
 346      public function setGrouping($grouping)
 347      {
 348          $this->setEvaluatedFalse();
 349          if (
 350              ($grouping !== null) &&
 351              (!in_array($grouping, self::DATE_TIME_GROUPS)) &&
 352              (!in_array($grouping, self::DYNAMIC_TYPES)) &&
 353              (!in_array($grouping, self::TOP_TEN_TYPE))
 354          ) {
 355              throw new PhpSpreadsheetException('Invalid grouping for column AutoFilter Rule.');
 356          }
 357          $this->grouping = $grouping;
 358  
 359          return $this;
 360      }
 361  
 362      /**
 363       * Set AutoFilter Rule.
 364       *
 365       * @param string $operator see self::AUTOFILTER_COLUMN_RULE_*
 366       * @param int|int[]|string|string[] $value
 367       * @param string $grouping
 368       *
 369       * @return $this
 370       */
 371      public function setRule($operator, $value, $grouping = null)
 372      {
 373          $this->setEvaluatedFalse();
 374          $this->setOperator($operator);
 375          $this->setValue($value);
 376          //  Only set grouping if it's been passed in as a user-supplied argument,
 377          //      otherwise we're calculating it when we setValue() and don't want to overwrite that
 378          //      If the user supplies an argumnet for grouping, then on their own head be it
 379          if ($grouping !== null) {
 380              $this->setGrouping($grouping);
 381          }
 382  
 383          return $this;
 384      }
 385  
 386      /**
 387       * Get this Rule's AutoFilter Column Parent.
 388       *
 389       * @return ?Column
 390       */
 391      public function getParent()
 392      {
 393          return $this->parent;
 394      }
 395  
 396      /**
 397       * Set this Rule's AutoFilter Column Parent.
 398       *
 399       * @return $this
 400       */
 401      public function setParent(?Column $parent = null)
 402      {
 403          $this->setEvaluatedFalse();
 404          $this->parent = $parent;
 405  
 406          return $this;
 407      }
 408  
 409      /**
 410       * Implement PHP __clone to create a deep clone, not just a shallow copy.
 411       */
 412      public function __clone()
 413      {
 414          $vars = get_object_vars($this);
 415          foreach ($vars as $key => $value) {
 416              if (is_object($value)) {
 417                  if ($key == 'parent') { // this is only object
 418                      //    Detach from autofilter column parent
 419                      $this->$key = null;
 420                  }
 421              } else {
 422                  $this->$key = $value;
 423              }
 424          }
 425      }
 426  }