1<?php
2
3namespace PhpOffice\PhpSpreadsheet\Worksheet;
4
5use PhpOffice\PhpSpreadsheet\Calculation\Calculation;
6use PhpOffice\PhpSpreadsheet\Calculation\DateTimeExcel;
7use PhpOffice\PhpSpreadsheet\Calculation\Functions;
8use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
9use PhpOffice\PhpSpreadsheet\Exception as PhpSpreadsheetException;
10use PhpOffice\PhpSpreadsheet\Shared\Date;
11
12class AutoFilter
13{
14    /**
15     * Autofilter Worksheet.
16     *
17     * @var null|Worksheet
18     */
19    private $workSheet;
20
21    /**
22     * Autofilter Range.
23     *
24     * @var string
25     */
26    private $range = '';
27
28    /**
29     * Autofilter Column Ruleset.
30     *
31     * @var AutoFilter\Column[]
32     */
33    private $columns = [];
34
35    /**
36     * Create a new AutoFilter.
37     *
38     * @param string $pRange Cell range (i.e. A1:E10)
39     * @param Worksheet $pSheet
40     */
41    public function __construct($pRange = '', ?Worksheet $pSheet = null)
42    {
43        $this->range = $pRange;
44        $this->workSheet = $pSheet;
45    }
46
47    /**
48     * Get AutoFilter Parent Worksheet.
49     *
50     * @return null|Worksheet
51     */
52    public function getParent()
53    {
54        return $this->workSheet;
55    }
56
57    /**
58     * Set AutoFilter Parent Worksheet.
59     *
60     * @param Worksheet $pSheet
61     *
62     * @return $this
63     */
64    public function setParent(?Worksheet $pSheet = null)
65    {
66        $this->workSheet = $pSheet;
67
68        return $this;
69    }
70
71    /**
72     * Get AutoFilter Range.
73     *
74     * @return string
75     */
76    public function getRange()
77    {
78        return $this->range;
79    }
80
81    /**
82     * Set AutoFilter Range.
83     *
84     * @param string $pRange Cell range (i.e. A1:E10)
85     *
86     * @return $this
87     */
88    public function setRange($pRange)
89    {
90        // extract coordinate
91        [$worksheet, $pRange] = Worksheet::extractSheetTitle($pRange, true);
92
93        if (strpos($pRange, ':') !== false) {
94            $this->range = $pRange;
95        } elseif (empty($pRange)) {
96            $this->range = '';
97        } else {
98            throw new PhpSpreadsheetException('Autofilter must be set on a range of cells.');
99        }
100
101        if (empty($pRange)) {
102            //    Discard all column rules
103            $this->columns = [];
104        } else {
105            //    Discard any column rules that are no longer valid within this range
106            [$rangeStart, $rangeEnd] = Coordinate::rangeBoundaries($this->range);
107            foreach ($this->columns as $key => $value) {
108                $colIndex = Coordinate::columnIndexFromString($key);
109                if (($rangeStart[0] > $colIndex) || ($rangeEnd[0] < $colIndex)) {
110                    unset($this->columns[$key]);
111                }
112            }
113        }
114
115        return $this;
116    }
117
118    /**
119     * Get all AutoFilter Columns.
120     *
121     * @return AutoFilter\Column[]
122     */
123    public function getColumns()
124    {
125        return $this->columns;
126    }
127
128    /**
129     * Validate that the specified column is in the AutoFilter range.
130     *
131     * @param string $column Column name (e.g. A)
132     *
133     * @return int The column offset within the autofilter range
134     */
135    public function testColumnInRange($column)
136    {
137        if (empty($this->range)) {
138            throw new PhpSpreadsheetException('No autofilter range is defined.');
139        }
140
141        $columnIndex = Coordinate::columnIndexFromString($column);
142        [$rangeStart, $rangeEnd] = Coordinate::rangeBoundaries($this->range);
143        if (($rangeStart[0] > $columnIndex) || ($rangeEnd[0] < $columnIndex)) {
144            throw new PhpSpreadsheetException('Column is outside of current autofilter range.');
145        }
146
147        return $columnIndex - $rangeStart[0];
148    }
149
150    /**
151     * Get a specified AutoFilter Column Offset within the defined AutoFilter range.
152     *
153     * @param string $pColumn Column name (e.g. A)
154     *
155     * @return int The offset of the specified column within the autofilter range
156     */
157    public function getColumnOffset($pColumn)
158    {
159        return $this->testColumnInRange($pColumn);
160    }
161
162    /**
163     * Get a specified AutoFilter Column.
164     *
165     * @param string $pColumn Column name (e.g. A)
166     *
167     * @return AutoFilter\Column
168     */
169    public function getColumn($pColumn)
170    {
171        $this->testColumnInRange($pColumn);
172
173        if (!isset($this->columns[$pColumn])) {
174            $this->columns[$pColumn] = new AutoFilter\Column($pColumn, $this);
175        }
176
177        return $this->columns[$pColumn];
178    }
179
180    /**
181     * Get a specified AutoFilter Column by it's offset.
182     *
183     * @param int $pColumnOffset Column offset within range (starting from 0)
184     *
185     * @return AutoFilter\Column
186     */
187    public function getColumnByOffset($pColumnOffset)
188    {
189        [$rangeStart, $rangeEnd] = Coordinate::rangeBoundaries($this->range);
190        $pColumn = Coordinate::stringFromColumnIndex($rangeStart[0] + $pColumnOffset);
191
192        return $this->getColumn($pColumn);
193    }
194
195    /**
196     * Set AutoFilter.
197     *
198     * @param AutoFilter\Column|string $pColumn
199     *            A simple string containing a Column ID like 'A' is permitted
200     *
201     * @return $this
202     */
203    public function setColumn($pColumn)
204    {
205        if ((is_string($pColumn)) && (!empty($pColumn))) {
206            $column = $pColumn;
207        } elseif (is_object($pColumn) && ($pColumn instanceof AutoFilter\Column)) {
208            $column = $pColumn->getColumnIndex();
209        } else {
210            throw new PhpSpreadsheetException('Column is not within the autofilter range.');
211        }
212        $this->testColumnInRange($column);
213
214        if (is_string($pColumn)) {
215            $this->columns[$pColumn] = new AutoFilter\Column($pColumn, $this);
216        } elseif (is_object($pColumn) && ($pColumn instanceof AutoFilter\Column)) {
217            $pColumn->setParent($this);
218            $this->columns[$column] = $pColumn;
219        }
220        ksort($this->columns);
221
222        return $this;
223    }
224
225    /**
226     * Clear a specified AutoFilter Column.
227     *
228     * @param string $pColumn Column name (e.g. A)
229     *
230     * @return $this
231     */
232    public function clearColumn($pColumn)
233    {
234        $this->testColumnInRange($pColumn);
235
236        if (isset($this->columns[$pColumn])) {
237            unset($this->columns[$pColumn]);
238        }
239
240        return $this;
241    }
242
243    /**
244     * Shift an AutoFilter Column Rule to a different column.
245     *
246     * Note: This method bypasses validation of the destination column to ensure it is within this AutoFilter range.
247     *        Nor does it verify whether any column rule already exists at $toColumn, but will simply override any existing value.
248     *        Use with caution.
249     *
250     * @param string $fromColumn Column name (e.g. A)
251     * @param string $toColumn Column name (e.g. B)
252     *
253     * @return $this
254     */
255    public function shiftColumn($fromColumn, $toColumn)
256    {
257        $fromColumn = strtoupper($fromColumn);
258        $toColumn = strtoupper($toColumn);
259
260        if (($fromColumn !== null) && (isset($this->columns[$fromColumn])) && ($toColumn !== null)) {
261            $this->columns[$fromColumn]->setParent();
262            $this->columns[$fromColumn]->setColumnIndex($toColumn);
263            $this->columns[$toColumn] = $this->columns[$fromColumn];
264            $this->columns[$toColumn]->setParent($this);
265            unset($this->columns[$fromColumn]);
266
267            ksort($this->columns);
268        }
269
270        return $this;
271    }
272
273    /**
274     * Test if cell value is in the defined set of values.
275     *
276     * @param mixed $cellValue
277     * @param mixed[] $dataSet
278     *
279     * @return bool
280     */
281    private static function filterTestInSimpleDataSet($cellValue, $dataSet)
282    {
283        $dataSetValues = $dataSet['filterValues'];
284        $blanks = $dataSet['blanks'];
285        if (($cellValue == '') || ($cellValue === null)) {
286            return $blanks;
287        }
288
289        return in_array($cellValue, $dataSetValues);
290    }
291
292    /**
293     * Test if cell value is in the defined set of Excel date values.
294     *
295     * @param mixed $cellValue
296     * @param mixed[] $dataSet
297     *
298     * @return bool
299     */
300    private static function filterTestInDateGroupSet($cellValue, $dataSet)
301    {
302        $dateSet = $dataSet['filterValues'];
303        $blanks = $dataSet['blanks'];
304        if (($cellValue == '') || ($cellValue === null)) {
305            return $blanks;
306        }
307
308        if (is_numeric($cellValue)) {
309            $dateValue = Date::excelToTimestamp($cellValue);
310            if ($cellValue < 1) {
311                //    Just the time part
312                $dtVal = date('His', $dateValue);
313                $dateSet = $dateSet['time'];
314            } elseif ($cellValue == floor($cellValue)) {
315                //    Just the date part
316                $dtVal = date('Ymd', $dateValue);
317                $dateSet = $dateSet['date'];
318            } else {
319                //    date and time parts
320                $dtVal = date('YmdHis', $dateValue);
321                $dateSet = $dateSet['dateTime'];
322            }
323            foreach ($dateSet as $dateValue) {
324                //    Use of substr to extract value at the appropriate group level
325                if (substr($dtVal, 0, strlen($dateValue)) == $dateValue) {
326                    return true;
327                }
328            }
329        }
330
331        return false;
332    }
333
334    /**
335     * Test if cell value is within a set of values defined by a ruleset.
336     *
337     * @param mixed $cellValue
338     * @param mixed[] $ruleSet
339     *
340     * @return bool
341     */
342    private static function filterTestInCustomDataSet($cellValue, $ruleSet)
343    {
344        $dataSet = $ruleSet['filterRules'];
345        $join = $ruleSet['join'];
346        $customRuleForBlanks = $ruleSet['customRuleForBlanks'] ?? false;
347
348        if (!$customRuleForBlanks) {
349            //    Blank cells are always ignored, so return a FALSE
350            if (($cellValue == '') || ($cellValue === null)) {
351                return false;
352            }
353        }
354        $returnVal = ($join == AutoFilter\Column::AUTOFILTER_COLUMN_JOIN_AND);
355        foreach ($dataSet as $rule) {
356            $retVal = false;
357
358            if (is_numeric($rule['value'])) {
359                //    Numeric values are tested using the appropriate operator
360                switch ($rule['operator']) {
361                    case AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_EQUAL:
362                        $retVal = ($cellValue == $rule['value']);
363
364                        break;
365                    case AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_NOTEQUAL:
366                        $retVal = ($cellValue != $rule['value']);
367
368                        break;
369                    case AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_GREATERTHAN:
370                        $retVal = ($cellValue > $rule['value']);
371
372                        break;
373                    case AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_GREATERTHANOREQUAL:
374                        $retVal = ($cellValue >= $rule['value']);
375
376                        break;
377                    case AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_LESSTHAN:
378                        $retVal = ($cellValue < $rule['value']);
379
380                        break;
381                    case AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_LESSTHANOREQUAL:
382                        $retVal = ($cellValue <= $rule['value']);
383
384                        break;
385                }
386            } elseif ($rule['value'] == '') {
387                switch ($rule['operator']) {
388                    case AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_EQUAL:
389                        $retVal = (($cellValue == '') || ($cellValue === null));
390
391                        break;
392                    case AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_NOTEQUAL:
393                        $retVal = (($cellValue != '') && ($cellValue !== null));
394
395                        break;
396                    default:
397                        $retVal = true;
398
399                        break;
400                }
401            } else {
402                //    String values are always tested for equality, factoring in for wildcards (hence a regexp test)
403                $retVal = preg_match('/^' . $rule['value'] . '$/i', $cellValue);
404            }
405            //    If there are multiple conditions, then we need to test both using the appropriate join operator
406            switch ($join) {
407                case AutoFilter\Column::AUTOFILTER_COLUMN_JOIN_OR:
408                    $returnVal = $returnVal || $retVal;
409                    //    Break as soon as we have a TRUE match for OR joins,
410                    //        to avoid unnecessary additional code execution
411                    if ($returnVal) {
412                        return $returnVal;
413                    }
414
415                    break;
416                case AutoFilter\Column::AUTOFILTER_COLUMN_JOIN_AND:
417                    $returnVal = $returnVal && $retVal;
418
419                    break;
420            }
421        }
422
423        return $returnVal;
424    }
425
426    /**
427     * Test if cell date value is matches a set of values defined by a set of months.
428     *
429     * @param mixed $cellValue
430     * @param mixed[] $monthSet
431     *
432     * @return bool
433     */
434    private static function filterTestInPeriodDateSet($cellValue, $monthSet)
435    {
436        //    Blank cells are always ignored, so return a FALSE
437        if (($cellValue == '') || ($cellValue === null)) {
438            return false;
439        }
440
441        if (is_numeric($cellValue)) {
442            $dateValue = date('m', Date::excelToTimestamp($cellValue));
443            if (in_array($dateValue, $monthSet)) {
444                return true;
445            }
446        }
447
448        return false;
449    }
450
451    /**
452     * Search/Replace arrays to convert Excel wildcard syntax to a regexp syntax for preg_matching.
453     *
454     * @var array
455     */
456    private static $fromReplace = ['\*', '\?', '~~', '~.*', '~.?'];
457
458    private static $toReplace = ['.*', '.', '~', '\*', '\?'];
459
460    /**
461     * Convert a dynamic rule daterange to a custom filter range expression for ease of calculation.
462     *
463     * @param string $dynamicRuleType
464     * @param AutoFilter\Column $filterColumn
465     *
466     * @return mixed[]
467     */
468    private function dynamicFilterDateRange($dynamicRuleType, &$filterColumn)
469    {
470        $rDateType = Functions::getReturnDateType();
471        Functions::setReturnDateType(Functions::RETURNDATE_PHP_NUMERIC);
472        $val = $maxVal = null;
473
474        $ruleValues = [];
475        $baseDate = DateTimeExcel\Current::now();
476        //    Calculate start/end dates for the required date range based on current date
477        switch ($dynamicRuleType) {
478            case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_LASTWEEK:
479                $baseDate = strtotime('-7 days', $baseDate);
480
481                break;
482            case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_NEXTWEEK:
483                $baseDate = strtotime('-7 days', $baseDate);
484
485                break;
486            case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_LASTMONTH:
487                $baseDate = strtotime('-1 month', gmmktime(0, 0, 0, 1, date('m', $baseDate), date('Y', $baseDate)));
488
489                break;
490            case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_NEXTMONTH:
491                $baseDate = strtotime('+1 month', gmmktime(0, 0, 0, 1, date('m', $baseDate), date('Y', $baseDate)));
492
493                break;
494            case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_LASTQUARTER:
495                $baseDate = strtotime('-3 month', gmmktime(0, 0, 0, 1, date('m', $baseDate), date('Y', $baseDate)));
496
497                break;
498            case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_NEXTQUARTER:
499                $baseDate = strtotime('+3 month', gmmktime(0, 0, 0, 1, date('m', $baseDate), date('Y', $baseDate)));
500
501                break;
502            case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_LASTYEAR:
503                $baseDate = strtotime('-1 year', gmmktime(0, 0, 0, 1, date('m', $baseDate), date('Y', $baseDate)));
504
505                break;
506            case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_NEXTYEAR:
507                $baseDate = strtotime('+1 year', gmmktime(0, 0, 0, 1, date('m', $baseDate), date('Y', $baseDate)));
508
509                break;
510        }
511
512        switch ($dynamicRuleType) {
513            case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_TODAY:
514            case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_YESTERDAY:
515            case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_TOMORROW:
516                $maxVal = (int) Date::PHPtoExcel(strtotime('+1 day', $baseDate));
517                $val = (int) Date::PHPToExcel($baseDate);
518
519                break;
520            case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_YEARTODATE:
521                $maxVal = (int) Date::PHPtoExcel(strtotime('+1 day', $baseDate));
522                $val = (int) Date::PHPToExcel(gmmktime(0, 0, 0, 1, 1, date('Y', $baseDate)));
523
524                break;
525            case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_THISYEAR:
526            case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_LASTYEAR:
527            case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_NEXTYEAR:
528                $maxVal = (int) Date::PHPToExcel(gmmktime(0, 0, 0, 31, 12, date('Y', $baseDate)));
529                ++$maxVal;
530                $val = (int) Date::PHPToExcel(gmmktime(0, 0, 0, 1, 1, date('Y', $baseDate)));
531
532                break;
533            case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_THISQUARTER:
534            case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_LASTQUARTER:
535            case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_NEXTQUARTER:
536                $thisMonth = date('m', $baseDate);
537                $thisQuarter = floor(--$thisMonth / 3);
538                $maxVal = (int) Date::PHPtoExcel(gmmktime(0, 0, 0, date('t', $baseDate), (1 + $thisQuarter) * 3, date('Y', $baseDate)));
539                ++$maxVal;
540                $val = (int) Date::PHPToExcel(gmmktime(0, 0, 0, 1, 1 + $thisQuarter * 3, date('Y', $baseDate)));
541
542                break;
543            case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_THISMONTH:
544            case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_LASTMONTH:
545            case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_NEXTMONTH:
546                $maxVal = (int) Date::PHPtoExcel(gmmktime(0, 0, 0, date('t', $baseDate), date('m', $baseDate), date('Y', $baseDate)));
547                ++$maxVal;
548                $val = (int) Date::PHPToExcel(gmmktime(0, 0, 0, 1, date('m', $baseDate), date('Y', $baseDate)));
549
550                break;
551            case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_THISWEEK:
552            case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_LASTWEEK:
553            case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_NEXTWEEK:
554                $dayOfWeek = date('w', $baseDate);
555                $val = (int) Date::PHPToExcel($baseDate) - $dayOfWeek;
556                $maxVal = $val + 7;
557
558                break;
559        }
560
561        switch ($dynamicRuleType) {
562            //    Adjust Today dates for Yesterday and Tomorrow
563            case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_YESTERDAY:
564                --$maxVal;
565                --$val;
566
567                break;
568            case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_TOMORROW:
569                ++$maxVal;
570                ++$val;
571
572                break;
573        }
574
575        //    Set the filter column rule attributes ready for writing
576        $filterColumn->setAttributes(['val' => $val, 'maxVal' => $maxVal]);
577
578        //    Set the rules for identifying rows for hide/show
579        $ruleValues[] = ['operator' => AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_GREATERTHANOREQUAL, 'value' => $val];
580        $ruleValues[] = ['operator' => AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_LESSTHAN, 'value' => $maxVal];
581        Functions::setReturnDateType($rDateType);
582
583        return ['method' => 'filterTestInCustomDataSet', 'arguments' => ['filterRules' => $ruleValues, 'join' => AutoFilter\Column::AUTOFILTER_COLUMN_JOIN_AND]];
584    }
585
586    private function calculateTopTenValue($columnID, $startRow, $endRow, $ruleType, $ruleValue)
587    {
588        $range = $columnID . $startRow . ':' . $columnID . $endRow;
589        $dataValues = Functions::flattenArray($this->workSheet->rangeToArray($range, null, true, false));
590
591        $dataValues = array_filter($dataValues);
592        if ($ruleType == AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_TOP) {
593            rsort($dataValues);
594        } else {
595            sort($dataValues);
596        }
597
598        $slice = array_slice($dataValues, 0, $ruleValue);
599
600        return array_pop($slice);
601    }
602
603    /**
604     * Apply the AutoFilter rules to the AutoFilter Range.
605     *
606     * @return $this
607     */
608    public function showHideRows()
609    {
610        [$rangeStart, $rangeEnd] = Coordinate::rangeBoundaries($this->range);
611
612        //    The heading row should always be visible
613        $this->workSheet->getRowDimension($rangeStart[1])->setVisible(true);
614
615        $columnFilterTests = [];
616        foreach ($this->columns as $columnID => $filterColumn) {
617            $rules = $filterColumn->getRules();
618            switch ($filterColumn->getFilterType()) {
619                case AutoFilter\Column::AUTOFILTER_FILTERTYPE_FILTER:
620                    $ruleType = null;
621                    $ruleValues = [];
622                    //    Build a list of the filter value selections
623                    foreach ($rules as $rule) {
624                        $ruleType = $rule->getRuleType();
625                        $ruleValues[] = $rule->getValue();
626                    }
627                    //    Test if we want to include blanks in our filter criteria
628                    $blanks = false;
629                    $ruleDataSet = array_filter($ruleValues);
630                    if (count($ruleValues) != count($ruleDataSet)) {
631                        $blanks = true;
632                    }
633                    if ($ruleType == AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_FILTER) {
634                        //    Filter on absolute values
635                        $columnFilterTests[$columnID] = [
636                            'method' => 'filterTestInSimpleDataSet',
637                            'arguments' => ['filterValues' => $ruleDataSet, 'blanks' => $blanks],
638                        ];
639                    } else {
640                        //    Filter on date group values
641                        $arguments = [
642                            'date' => [],
643                            'time' => [],
644                            'dateTime' => [],
645                        ];
646                        foreach ($ruleDataSet as $ruleValue) {
647                            $date = $time = '';
648                            if (
649                                (isset($ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_YEAR])) &&
650                                ($ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_YEAR] !== '')
651                            ) {
652                                $date .= sprintf('%04d', $ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_YEAR]);
653                            }
654                            if (
655                                (isset($ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_MONTH])) &&
656                                ($ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_MONTH] != '')
657                            ) {
658                                $date .= sprintf('%02d', $ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_MONTH]);
659                            }
660                            if (
661                                (isset($ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_DAY])) &&
662                                ($ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_DAY] !== '')
663                            ) {
664                                $date .= sprintf('%02d', $ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_DAY]);
665                            }
666                            if (
667                                (isset($ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_HOUR])) &&
668                                ($ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_HOUR] !== '')
669                            ) {
670                                $time .= sprintf('%02d', $ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_HOUR]);
671                            }
672                            if (
673                                (isset($ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_MINUTE])) &&
674                                ($ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_MINUTE] !== '')
675                            ) {
676                                $time .= sprintf('%02d', $ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_MINUTE]);
677                            }
678                            if (
679                                (isset($ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_SECOND])) &&
680                                ($ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_SECOND] !== '')
681                            ) {
682                                $time .= sprintf('%02d', $ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_SECOND]);
683                            }
684                            $dateTime = $date . $time;
685                            $arguments['date'][] = $date;
686                            $arguments['time'][] = $time;
687                            $arguments['dateTime'][] = $dateTime;
688                        }
689                        //    Remove empty elements
690                        $arguments['date'] = array_filter($arguments['date']);
691                        $arguments['time'] = array_filter($arguments['time']);
692                        $arguments['dateTime'] = array_filter($arguments['dateTime']);
693                        $columnFilterTests[$columnID] = [
694                            'method' => 'filterTestInDateGroupSet',
695                            'arguments' => ['filterValues' => $arguments, 'blanks' => $blanks],
696                        ];
697                    }
698
699                    break;
700                case AutoFilter\Column::AUTOFILTER_FILTERTYPE_CUSTOMFILTER:
701                    $customRuleForBlanks = false;
702                    $ruleValues = [];
703                    //    Build a list of the filter value selections
704                    foreach ($rules as $rule) {
705                        $ruleValue = $rule->getValue();
706                        if (!is_numeric($ruleValue)) {
707                            //    Convert to a regexp allowing for regexp reserved characters, wildcards and escaped wildcards
708                            $ruleValue = preg_quote($ruleValue);
709                            $ruleValue = str_replace(self::$fromReplace, self::$toReplace, $ruleValue);
710                            if (trim($ruleValue) == '') {
711                                $customRuleForBlanks = true;
712                                $ruleValue = trim($ruleValue);
713                            }
714                        }
715                        $ruleValues[] = ['operator' => $rule->getOperator(), 'value' => $ruleValue];
716                    }
717                    $join = $filterColumn->getJoin();
718                    $columnFilterTests[$columnID] = [
719                        'method' => 'filterTestInCustomDataSet',
720                        'arguments' => ['filterRules' => $ruleValues, 'join' => $join, 'customRuleForBlanks' => $customRuleForBlanks],
721                    ];
722
723                    break;
724                case AutoFilter\Column::AUTOFILTER_FILTERTYPE_DYNAMICFILTER:
725                    $ruleValues = [];
726                    foreach ($rules as $rule) {
727                        //    We should only ever have one Dynamic Filter Rule anyway
728                        $dynamicRuleType = $rule->getGrouping();
729                        if (
730                            ($dynamicRuleType == AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_ABOVEAVERAGE) ||
731                            ($dynamicRuleType == AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_BELOWAVERAGE)
732                        ) {
733                            //    Number (Average) based
734                            //    Calculate the average
735                            $averageFormula = '=AVERAGE(' . $columnID . ($rangeStart[1] + 1) . ':' . $columnID . $rangeEnd[1] . ')';
736                            $average = Calculation::getInstance()->calculateFormula($averageFormula, null, $this->workSheet->getCell('A1'));
737                            //    Set above/below rule based on greaterThan or LessTan
738                            $operator = ($dynamicRuleType === AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_ABOVEAVERAGE)
739                                ? AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_GREATERTHAN
740                                : AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_LESSTHAN;
741                            $ruleValues[] = [
742                                'operator' => $operator,
743                                'value' => $average,
744                            ];
745                            $columnFilterTests[$columnID] = [
746                                'method' => 'filterTestInCustomDataSet',
747                                'arguments' => ['filterRules' => $ruleValues, 'join' => AutoFilter\Column::AUTOFILTER_COLUMN_JOIN_OR],
748                            ];
749                        } else {
750                            //    Date based
751                            if ($dynamicRuleType[0] == 'M' || $dynamicRuleType[0] == 'Q') {
752                                $periodType = '';
753                                $period = 0;
754                                //    Month or Quarter
755                                sscanf($dynamicRuleType, '%[A-Z]%d', $periodType, $period);
756                                if ($periodType == 'M') {
757                                    $ruleValues = [$period];
758                                } else {
759                                    --$period;
760                                    $periodEnd = (1 + $period) * 3;
761                                    $periodStart = 1 + $period * 3;
762                                    $ruleValues = range($periodStart, $periodEnd);
763                                }
764                                $columnFilterTests[$columnID] = [
765                                    'method' => 'filterTestInPeriodDateSet',
766                                    'arguments' => $ruleValues,
767                                ];
768                                $filterColumn->setAttributes([]);
769                            } else {
770                                //    Date Range
771                                $columnFilterTests[$columnID] = $this->dynamicFilterDateRange($dynamicRuleType, $filterColumn);
772
773                                break;
774                            }
775                        }
776                    }
777
778                    break;
779                case AutoFilter\Column::AUTOFILTER_FILTERTYPE_TOPTENFILTER:
780                    $ruleValues = [];
781                    $dataRowCount = $rangeEnd[1] - $rangeStart[1];
782                    $toptenRuleType = null;
783                    $ruleValue = 0;
784                    $ruleOperator = null;
785                    foreach ($rules as $rule) {
786                        //    We should only ever have one Dynamic Filter Rule anyway
787                        $toptenRuleType = $rule->getGrouping();
788                        $ruleValue = $rule->getValue();
789                        $ruleOperator = $rule->getOperator();
790                    }
791                    if ($ruleOperator === AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_PERCENT) {
792                        $ruleValue = floor($ruleValue * ($dataRowCount / 100));
793                    }
794                    if (!is_array($ruleValue) && $ruleValue < 1) {
795                        $ruleValue = 1;
796                    }
797                    if (!is_array($ruleValue) && $ruleValue > 500) {
798                        $ruleValue = 500;
799                    }
800
801                    $maxVal = $this->calculateTopTenValue($columnID, $rangeStart[1] + 1, $rangeEnd[1], $toptenRuleType, $ruleValue);
802
803                    $operator = ($toptenRuleType == AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_TOP)
804                        ? AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_GREATERTHANOREQUAL
805                        : AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_LESSTHANOREQUAL;
806                    $ruleValues[] = ['operator' => $operator, 'value' => $maxVal];
807                    $columnFilterTests[$columnID] = [
808                        'method' => 'filterTestInCustomDataSet',
809                        'arguments' => ['filterRules' => $ruleValues, 'join' => AutoFilter\Column::AUTOFILTER_COLUMN_JOIN_OR],
810                    ];
811                    $filterColumn->setAttributes(['maxVal' => $maxVal]);
812
813                    break;
814            }
815        }
816
817        //    Execute the column tests for each row in the autoFilter range to determine show/hide,
818        for ($row = $rangeStart[1] + 1; $row <= $rangeEnd[1]; ++$row) {
819            $result = true;
820            foreach ($columnFilterTests as $columnID => $columnFilterTest) {
821                $cellValue = $this->workSheet->getCell($columnID . $row)->getCalculatedValue();
822                //    Execute the filter test
823                $result = $result &&
824                    call_user_func_array(
825                        [self::class, $columnFilterTest['method']],
826                        [$cellValue, $columnFilterTest['arguments']]
827                    );
828                //    If filter test has resulted in FALSE, exit the loop straightaway rather than running any more tests
829                if (!$result) {
830                    break;
831                }
832            }
833            //    Set show/hide for the row based on the result of the autoFilter result
834            $this->workSheet->getRowDimension($row)->setVisible($result);
835        }
836
837        return $this;
838    }
839
840    /**
841     * Implement PHP __clone to create a deep clone, not just a shallow copy.
842     */
843    public function __clone()
844    {
845        $vars = get_object_vars($this);
846        foreach ($vars as $key => $value) {
847            if (is_object($value)) {
848                if ($key === 'workSheet') {
849                    //    Detach from worksheet
850                    $this->{$key} = null;
851                } else {
852                    $this->{$key} = clone $value;
853                }
854            } elseif ((is_array($value)) && ($key == 'columns')) {
855                //    The columns array of \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet\AutoFilter objects
856                $this->{$key} = [];
857                foreach ($value as $k => $v) {
858                    $this->{$key}[$k] = clone $v;
859                    // attach the new cloned Column to this new cloned Autofilter object
860                    $this->{$key}[$k]->setParent($this);
861                }
862            } else {
863                $this->{$key} = $value;
864            }
865        }
866    }
867
868    /**
869     * toString method replicates previous behavior by returning the range if object is
870     * referenced as a property of its parent.
871     */
872    public function __toString()
873    {
874        return (string) $this->range;
875    }
876}
877