[Summary view]

Differences Between: [Versions 310 and 311] [Versions 310 and 400] [Versions 310 and 401] [Versions 310 and 402] [Versions 310 and 403]

   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 static $ruleTypes = [
  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 static $dateTimeGroups = [
  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 static $dynamicTypes = [
  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      /*
 129       *    The only valid filter rule operators for filter and customFilter types are:
 130       *        <xsd:enumeration value="equal"/>
 131       *        <xsd:enumeration value="lessThan"/>
 132       *        <xsd:enumeration value="lessThanOrEqual"/>
 133       *        <xsd:enumeration value="notEqual"/>
 134       *        <xsd:enumeration value="greaterThanOrEqual"/>
 135       *        <xsd:enumeration value="greaterThan"/>
 136       */
 137      const AUTOFILTER_COLUMN_RULE_EQUAL = 'equal';
 138      const AUTOFILTER_COLUMN_RULE_NOTEQUAL = 'notEqual';
 139      const AUTOFILTER_COLUMN_RULE_GREATERTHAN = 'greaterThan';
 140      const AUTOFILTER_COLUMN_RULE_GREATERTHANOREQUAL = 'greaterThanOrEqual';
 141      const AUTOFILTER_COLUMN_RULE_LESSTHAN = 'lessThan';
 142      const AUTOFILTER_COLUMN_RULE_LESSTHANOREQUAL = 'lessThanOrEqual';
 143  
 144      private static $operators = [
 145          self::AUTOFILTER_COLUMN_RULE_EQUAL,
 146          self::AUTOFILTER_COLUMN_RULE_NOTEQUAL,
 147          self::AUTOFILTER_COLUMN_RULE_GREATERTHAN,
 148          self::AUTOFILTER_COLUMN_RULE_GREATERTHANOREQUAL,
 149          self::AUTOFILTER_COLUMN_RULE_LESSTHAN,
 150          self::AUTOFILTER_COLUMN_RULE_LESSTHANOREQUAL,
 151      ];
 152  
 153      const AUTOFILTER_COLUMN_RULE_TOPTEN_BY_VALUE = 'byValue';
 154      const AUTOFILTER_COLUMN_RULE_TOPTEN_PERCENT = 'byPercent';
 155  
 156      private static $topTenValue = [
 157          self::AUTOFILTER_COLUMN_RULE_TOPTEN_BY_VALUE,
 158          self::AUTOFILTER_COLUMN_RULE_TOPTEN_PERCENT,
 159      ];
 160  
 161      const AUTOFILTER_COLUMN_RULE_TOPTEN_TOP = 'top';
 162      const AUTOFILTER_COLUMN_RULE_TOPTEN_BOTTOM = 'bottom';
 163  
 164      private static $topTenType = [
 165          self::AUTOFILTER_COLUMN_RULE_TOPTEN_TOP,
 166          self::AUTOFILTER_COLUMN_RULE_TOPTEN_BOTTOM,
 167      ];
 168  
 169      // Rule Operators (Numeric, Boolean etc)
 170  //    const AUTOFILTER_COLUMN_RULE_BETWEEN            = 'between';        //    greaterThanOrEqual 1 && lessThanOrEqual 2
 171      // Rule Operators (Numeric Special) which are translated to standard numeric operators with calculated values
 172  //    const AUTOFILTER_COLUMN_RULE_TOPTEN                = 'topTen';            //    greaterThan calculated value
 173  //    const AUTOFILTER_COLUMN_RULE_TOPTENPERCENT        = 'topTenPercent';    //    greaterThan calculated value
 174  //    const AUTOFILTER_COLUMN_RULE_ABOVEAVERAGE        = 'aboveAverage';    //    Value is calculated as the average
 175  //    const AUTOFILTER_COLUMN_RULE_BELOWAVERAGE        = 'belowAverage';    //    Value is calculated as the average
 176      // Rule Operators (String) which are set as wild-carded values
 177  //    const AUTOFILTER_COLUMN_RULE_BEGINSWITH            = 'beginsWith';            // A*
 178  //    const AUTOFILTER_COLUMN_RULE_ENDSWITH            = 'endsWith';            // *Z
 179  //    const AUTOFILTER_COLUMN_RULE_CONTAINS            = 'contains';            // *B*
 180  //    const AUTOFILTER_COLUMN_RULE_DOESNTCONTAIN        = 'notEqual';            //    notEqual *B*
 181      // Rule Operators (Date Special) which are translated to standard numeric operators with calculated values
 182  //    const AUTOFILTER_COLUMN_RULE_BEFORE                = 'lessThan';
 183  //    const AUTOFILTER_COLUMN_RULE_AFTER                = 'greaterThan';
 184  //    const AUTOFILTER_COLUMN_RULE_YESTERDAY            = 'yesterday';
 185  //    const AUTOFILTER_COLUMN_RULE_TODAY                = 'today';
 186  //    const AUTOFILTER_COLUMN_RULE_TOMORROW            = 'tomorrow';
 187  //    const AUTOFILTER_COLUMN_RULE_LASTWEEK            = 'lastWeek';
 188  //    const AUTOFILTER_COLUMN_RULE_THISWEEK            = 'thisWeek';
 189  //    const AUTOFILTER_COLUMN_RULE_NEXTWEEK            = 'nextWeek';
 190  //    const AUTOFILTER_COLUMN_RULE_LASTMONTH            = 'lastMonth';
 191  //    const AUTOFILTER_COLUMN_RULE_THISMONTH            = 'thisMonth';
 192  //    const AUTOFILTER_COLUMN_RULE_NEXTMONTH            = 'nextMonth';
 193  //    const AUTOFILTER_COLUMN_RULE_LASTQUARTER        = 'lastQuarter';
 194  //    const AUTOFILTER_COLUMN_RULE_THISQUARTER        = 'thisQuarter';
 195  //    const AUTOFILTER_COLUMN_RULE_NEXTQUARTER        = 'nextQuarter';
 196  //    const AUTOFILTER_COLUMN_RULE_LASTYEAR            = 'lastYear';
 197  //    const AUTOFILTER_COLUMN_RULE_THISYEAR            = 'thisYear';
 198  //    const AUTOFILTER_COLUMN_RULE_NEXTYEAR            = 'nextYear';
 199  //    const AUTOFILTER_COLUMN_RULE_YEARTODATE            = 'yearToDate';            //    <dynamicFilter val="40909" type="yearToDate" maxVal="41113"/>
 200  //    const AUTOFILTER_COLUMN_RULE_ALLDATESINMONTH    = 'allDatesInMonth';    //    <dynamicFilter type="M2"/> for Month/February
 201  //    const AUTOFILTER_COLUMN_RULE_ALLDATESINQUARTER    = 'allDatesInQuarter';    //    <dynamicFilter type="Q2"/> for Quarter 2
 202  
 203      /**
 204       * Autofilter Column.
 205       *
 206       * @var Column
 207       */
 208      private $parent;
 209  
 210      /**
 211       * Autofilter Rule Type.
 212       *
 213       * @var string
 214       */
 215      private $ruleType = self::AUTOFILTER_RULETYPE_FILTER;
 216  
 217      /**
 218       * Autofilter Rule Value.
 219       *
 220       * @var string
 221       */
 222      private $value = '';
 223  
 224      /**
 225       * Autofilter Rule Operator.
 226       *
 227       * @var string
 228       */
 229      private $operator = self::AUTOFILTER_COLUMN_RULE_EQUAL;
 230  
 231      /**
 232       * DateTimeGrouping Group Value.
 233       *
 234       * @var string
 235       */
 236      private $grouping = '';
 237  
 238      /**
 239       * Create a new Rule.
 240       *
 241       * @param Column $pParent
 242       */
 243      public function __construct(Column $pParent = null)
 244      {
 245          $this->parent = $pParent;
 246      }
 247  
 248      /**
 249       * Get AutoFilter Rule Type.
 250       *
 251       * @return string
 252       */
 253      public function getRuleType()
 254      {
 255          return $this->ruleType;
 256      }
 257  
 258      /**
 259       * Set AutoFilter Rule Type.
 260       *
 261       * @param string $pRuleType see self::AUTOFILTER_RULETYPE_*
 262       *
 263       * @throws PhpSpreadsheetException
 264       *
 265       * @return Rule
 266       */
 267      public function setRuleType($pRuleType)
 268      {
 269          if (!in_array($pRuleType, self::$ruleTypes)) {
 270              throw new PhpSpreadsheetException('Invalid rule type for column AutoFilter Rule.');
 271          }
 272  
 273          $this->ruleType = $pRuleType;
 274  
 275          return $this;
 276      }
 277  
 278      /**
 279       * Get AutoFilter Rule Value.
 280       *
 281       * @return string
 282       */
 283      public function getValue()
 284      {
 285          return $this->value;
 286      }
 287  
 288      /**
 289       * Set AutoFilter Rule Value.
 290       *
 291       * @param string|string[] $pValue
 292       *
 293       * @throws PhpSpreadsheetException
 294       *
 295       * @return Rule
 296       */
 297      public function setValue($pValue)
 298      {
 299          if (is_array($pValue)) {
 300              $grouping = -1;
 301              foreach ($pValue as $key => $value) {
 302                  //    Validate array entries
 303                  if (!in_array($key, self::$dateTimeGroups)) {
 304                      //    Remove any invalid entries from the value array
 305                      unset($pValue[$key]);
 306                  } else {
 307                      //    Work out what the dateTime grouping will be
 308                      $grouping = max($grouping, array_search($key, self::$dateTimeGroups));
 309                  }
 310              }
 311              if (count($pValue) == 0) {
 312                  throw new PhpSpreadsheetException('Invalid rule value for column AutoFilter Rule.');
 313              }
 314              //    Set the dateTime grouping that we've anticipated
 315              $this->setGrouping(self::$dateTimeGroups[$grouping]);
 316          }
 317          $this->value = $pValue;
 318  
 319          return $this;
 320      }
 321  
 322      /**
 323       * Get AutoFilter Rule Operator.
 324       *
 325       * @return string
 326       */
 327      public function getOperator()
 328      {
 329          return $this->operator;
 330      }
 331  
 332      /**
 333       * Set AutoFilter Rule Operator.
 334       *
 335       * @param string $pOperator see self::AUTOFILTER_COLUMN_RULE_*
 336       *
 337       * @throws PhpSpreadsheetException
 338       *
 339       * @return Rule
 340       */
 341      public function setOperator($pOperator)
 342      {
 343          if (empty($pOperator)) {
 344              $pOperator = self::AUTOFILTER_COLUMN_RULE_EQUAL;
 345          }
 346          if ((!in_array($pOperator, self::$operators)) &&
 347              (!in_array($pOperator, self::$topTenValue))) {
 348              throw new PhpSpreadsheetException('Invalid operator for column AutoFilter Rule.');
 349          }
 350          $this->operator = $pOperator;
 351  
 352          return $this;
 353      }
 354  
 355      /**
 356       * Get AutoFilter Rule Grouping.
 357       *
 358       * @return string
 359       */
 360      public function getGrouping()
 361      {
 362          return $this->grouping;
 363      }
 364  
 365      /**
 366       * Set AutoFilter Rule Grouping.
 367       *
 368       * @param string $pGrouping
 369       *
 370       * @throws PhpSpreadsheetException
 371       *
 372       * @return Rule
 373       */
 374      public function setGrouping($pGrouping)
 375      {
 376          if (($pGrouping !== null) &&
 377              (!in_array($pGrouping, self::$dateTimeGroups)) &&
 378              (!in_array($pGrouping, self::$dynamicTypes)) &&
 379              (!in_array($pGrouping, self::$topTenType))) {
 380              throw new PhpSpreadsheetException('Invalid rule type for column AutoFilter Rule.');
 381          }
 382          $this->grouping = $pGrouping;
 383  
 384          return $this;
 385      }
 386  
 387      /**
 388       * Set AutoFilter Rule.
 389       *
 390       * @param string $pOperator see self::AUTOFILTER_COLUMN_RULE_*
 391       * @param string|string[] $pValue
 392       * @param string $pGrouping
 393       *
 394       * @throws PhpSpreadsheetException
 395       *
 396       * @return Rule
 397       */
 398      public function setRule($pOperator, $pValue, $pGrouping = null)
 399      {
 400          $this->setOperator($pOperator);
 401          $this->setValue($pValue);
 402          //  Only set grouping if it's been passed in as a user-supplied argument,
 403          //      otherwise we're calculating it when we setValue() and don't want to overwrite that
 404          //      If the user supplies an argumnet for grouping, then on their own head be it
 405          if ($pGrouping !== null) {
 406              $this->setGrouping($pGrouping);
 407          }
 408  
 409          return $this;
 410      }
 411  
 412      /**
 413       * Get this Rule's AutoFilter Column Parent.
 414       *
 415       * @return Column
 416       */
 417      public function getParent()
 418      {
 419          return $this->parent;
 420      }
 421  
 422      /**
 423       * Set this Rule's AutoFilter Column Parent.
 424       *
 425       * @param Column $pParent
 426       *
 427       * @return Rule
 428       */
 429      public function setParent(Column $pParent = null)
 430      {
 431          $this->parent = $pParent;
 432  
 433          return $this;
 434      }
 435  
 436      /**
 437       * Implement PHP __clone to create a deep clone, not just a shallow copy.
 438       */
 439      public function __clone()
 440      {
 441          $vars = get_object_vars($this);
 442          foreach ($vars as $key => $value) {
 443              if (is_object($value)) {
 444                  if ($key == 'parent') {
 445                      //    Detach from autofilter column parent
 446                      $this->$key = null;
 447                  } else {
 448                      $this->$key = clone $value;
 449                  }
 450              } else {
 451                  $this->$key = $value;
 452              }
 453          }
 454      }
 455  }