1<?php
2
3namespace PhpOffice\PhpSpreadsheet\Worksheet;
4
5use PhpOffice\PhpSpreadsheet\Calculation\Calculation;
6use PhpOffice\PhpSpreadsheet\Calculation\DateTime;
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 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 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 = DateTime::DATENOW();
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        return array_pop(array_slice($dataValues, 0, $ruleValue));
599    }
600
601    /**
602     * Apply the AutoFilter rules to the AutoFilter Range.
603     *
604     * @return $this
605     */
606    public function showHideRows()
607    {
608        [$rangeStart, $rangeEnd] = Coordinate::rangeBoundaries($this->range);
609
610        //    The heading row should always be visible
611        $this->workSheet->getRowDimension($rangeStart[1])->setVisible(true);
612
613        $columnFilterTests = [];
614        foreach ($this->columns as $columnID => $filterColumn) {
615            $rules = $filterColumn->getRules();
616            switch ($filterColumn->getFilterType()) {
617                case AutoFilter\Column::AUTOFILTER_FILTERTYPE_FILTER:
618                    $ruleType = null;
619                    $ruleValues = [];
620                    //    Build a list of the filter value selections
621                    foreach ($rules as $rule) {
622                        $ruleType = $rule->getRuleType();
623                        $ruleValues[] = $rule->getValue();
624                    }
625                    //    Test if we want to include blanks in our filter criteria
626                    $blanks = false;
627                    $ruleDataSet = array_filter($ruleValues);
628                    if (count($ruleValues) != count($ruleDataSet)) {
629                        $blanks = true;
630                    }
631                    if ($ruleType == AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_FILTER) {
632                        //    Filter on absolute values
633                        $columnFilterTests[$columnID] = [
634                            'method' => 'filterTestInSimpleDataSet',
635                            'arguments' => ['filterValues' => $ruleDataSet, 'blanks' => $blanks],
636                        ];
637                    } else {
638                        //    Filter on date group values
639                        $arguments = [
640                            'date' => [],
641                            'time' => [],
642                            'dateTime' => [],
643                        ];
644                        foreach ($ruleDataSet as $ruleValue) {
645                            $date = $time = '';
646                            if (
647                                (isset($ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_YEAR])) &&
648                                ($ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_YEAR] !== '')
649                            ) {
650                                $date .= sprintf('%04d', $ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_YEAR]);
651                            }
652                            if (
653                                (isset($ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_MONTH])) &&
654                                ($ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_MONTH] != '')
655                            ) {
656                                $date .= sprintf('%02d', $ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_MONTH]);
657                            }
658                            if (
659                                (isset($ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_DAY])) &&
660                                ($ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_DAY] !== '')
661                            ) {
662                                $date .= sprintf('%02d', $ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_DAY]);
663                            }
664                            if (
665                                (isset($ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_HOUR])) &&
666                                ($ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_HOUR] !== '')
667                            ) {
668                                $time .= sprintf('%02d', $ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_HOUR]);
669                            }
670                            if (
671                                (isset($ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_MINUTE])) &&
672                                ($ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_MINUTE] !== '')
673                            ) {
674                                $time .= sprintf('%02d', $ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_MINUTE]);
675                            }
676                            if (
677                                (isset($ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_SECOND])) &&
678                                ($ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_SECOND] !== '')
679                            ) {
680                                $time .= sprintf('%02d', $ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_SECOND]);
681                            }
682                            $dateTime = $date . $time;
683                            $arguments['date'][] = $date;
684                            $arguments['time'][] = $time;
685                            $arguments['dateTime'][] = $dateTime;
686                        }
687                        //    Remove empty elements
688                        $arguments['date'] = array_filter($arguments['date']);
689                        $arguments['time'] = array_filter($arguments['time']);
690                        $arguments['dateTime'] = array_filter($arguments['dateTime']);
691                        $columnFilterTests[$columnID] = [
692                            'method' => 'filterTestInDateGroupSet',
693                            'arguments' => ['filterValues' => $arguments, 'blanks' => $blanks],
694                        ];
695                    }
696
697                    break;
698                case AutoFilter\Column::AUTOFILTER_FILTERTYPE_CUSTOMFILTER:
699                    $customRuleForBlanks = false;
700                    $ruleValues = [];
701                    //    Build a list of the filter value selections
702                    foreach ($rules as $rule) {
703                        $ruleValue = $rule->getValue();
704                        if (!is_numeric($ruleValue)) {
705                            //    Convert to a regexp allowing for regexp reserved characters, wildcards and escaped wildcards
706                            $ruleValue = preg_quote($ruleValue);
707                            $ruleValue = str_replace(self::$fromReplace, self::$toReplace, $ruleValue);
708                            if (trim($ruleValue) == '') {
709                                $customRuleForBlanks = true;
710                                $ruleValue = trim($ruleValue);
711                            }
712                        }
713                        $ruleValues[] = ['operator' => $rule->getOperator(), 'value' => $ruleValue];
714                    }
715                    $join = $filterColumn->getJoin();
716                    $columnFilterTests[$columnID] = [
717                        'method' => 'filterTestInCustomDataSet',
718                        'arguments' => ['filterRules' => $ruleValues, 'join' => $join, 'customRuleForBlanks' => $customRuleForBlanks],
719                    ];
720
721                    break;
722                case AutoFilter\Column::AUTOFILTER_FILTERTYPE_DYNAMICFILTER:
723                    $ruleValues = [];
724                    foreach ($rules as $rule) {
725                        //    We should only ever have one Dynamic Filter Rule anyway
726                        $dynamicRuleType = $rule->getGrouping();
727                        if (
728                            ($dynamicRuleType == AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_ABOVEAVERAGE) ||
729                            ($dynamicRuleType == AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_BELOWAVERAGE)
730                        ) {
731                            //    Number (Average) based
732                            //    Calculate the average
733                            $averageFormula = '=AVERAGE(' . $columnID . ($rangeStart[1] + 1) . ':' . $columnID . $rangeEnd[1] . ')';
734                            $average = Calculation::getInstance()->calculateFormula($averageFormula, null, $this->workSheet->getCell('A1'));
735                            //    Set above/below rule based on greaterThan or LessTan
736                            $operator = ($dynamicRuleType === AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_ABOVEAVERAGE)
737                                ? AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_GREATERTHAN
738                                : AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_LESSTHAN;
739                            $ruleValues[] = [
740                                'operator' => $operator,
741                                'value' => $average,
742                            ];
743                            $columnFilterTests[$columnID] = [
744                                'method' => 'filterTestInCustomDataSet',
745                                'arguments' => ['filterRules' => $ruleValues, 'join' => AutoFilter\Column::AUTOFILTER_COLUMN_JOIN_OR],
746                            ];
747                        } else {
748                            //    Date based
749                            if ($dynamicRuleType[0] == 'M' || $dynamicRuleType[0] == 'Q') {
750                                $periodType = '';
751                                $period = 0;
752                                //    Month or Quarter
753                                sscanf($dynamicRuleType, '%[A-Z]%d', $periodType, $period);
754                                if ($periodType == 'M') {
755                                    $ruleValues = [$period];
756                                } else {
757                                    --$period;
758                                    $periodEnd = (1 + $period) * 3;
759                                    $periodStart = 1 + $period * 3;
760                                    $ruleValues = range($periodStart, $periodEnd);
761                                }
762                                $columnFilterTests[$columnID] = [
763                                    'method' => 'filterTestInPeriodDateSet',
764                                    'arguments' => $ruleValues,
765                                ];
766                                $filterColumn->setAttributes([]);
767                            } else {
768                                //    Date Range
769                                $columnFilterTests[$columnID] = $this->dynamicFilterDateRange($dynamicRuleType, $filterColumn);
770
771                                break;
772                            }
773                        }
774                    }
775
776                    break;
777                case AutoFilter\Column::AUTOFILTER_FILTERTYPE_TOPTENFILTER:
778                    $ruleValues = [];
779                    $dataRowCount = $rangeEnd[1] - $rangeStart[1];
780                    foreach ($rules as $rule) {
781                        //    We should only ever have one Dynamic Filter Rule anyway
782                        $toptenRuleType = $rule->getGrouping();
783                        $ruleValue = $rule->getValue();
784                        $ruleOperator = $rule->getOperator();
785                    }
786                    if ($ruleOperator === AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_PERCENT) {
787                        $ruleValue = floor($ruleValue * ($dataRowCount / 100));
788                    }
789                    if ($ruleValue < 1) {
790                        $ruleValue = 1;
791                    }
792                    if ($ruleValue > 500) {
793                        $ruleValue = 500;
794                    }
795
796                    $maxVal = $this->calculateTopTenValue($columnID, $rangeStart[1] + 1, $rangeEnd[1], $toptenRuleType, $ruleValue);
797
798                    $operator = ($toptenRuleType == AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_TOP)
799                        ? AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_GREATERTHANOREQUAL
800                        : AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_LESSTHANOREQUAL;
801                    $ruleValues[] = ['operator' => $operator, 'value' => $maxVal];
802                    $columnFilterTests[$columnID] = [
803                        'method' => 'filterTestInCustomDataSet',
804                        'arguments' => ['filterRules' => $ruleValues, 'join' => AutoFilter\Column::AUTOFILTER_COLUMN_JOIN_OR],
805                    ];
806                    $filterColumn->setAttributes(['maxVal' => $maxVal]);
807
808                    break;
809            }
810        }
811
812        //    Execute the column tests for each row in the autoFilter range to determine show/hide,
813        for ($row = $rangeStart[1] + 1; $row <= $rangeEnd[1]; ++$row) {
814            $result = true;
815            foreach ($columnFilterTests as $columnID => $columnFilterTest) {
816                $cellValue = $this->workSheet->getCell($columnID . $row)->getCalculatedValue();
817                //    Execute the filter test
818                $result = $result &&
819                    call_user_func_array(
820                        [self::class, $columnFilterTest['method']],
821                        [$cellValue, $columnFilterTest['arguments']]
822                    );
823                //    If filter test has resulted in FALSE, exit the loop straightaway rather than running any more tests
824                if (!$result) {
825                    break;
826                }
827            }
828            //    Set show/hide for the row based on the result of the autoFilter result
829            $this->workSheet->getRowDimension($row)->setVisible($result);
830        }
831
832        return $this;
833    }
834
835    /**
836     * Implement PHP __clone to create a deep clone, not just a shallow copy.
837     */
838    public function __clone()
839    {
840        $vars = get_object_vars($this);
841        foreach ($vars as $key => $value) {
842            if (is_object($value)) {
843                if ($key === 'workSheet') {
844                    //    Detach from worksheet
845                    $this->{$key} = null;
846                } else {
847                    $this->{$key} = clone $value;
848                }
849            } elseif ((is_array($value)) && ($key == 'columns')) {
850                //    The columns array of \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet\AutoFilter objects
851                $this->{$key} = [];
852                foreach ($value as $k => $v) {
853                    $this->{$key}[$k] = clone $v;
854                    // attach the new cloned Column to this new cloned Autofilter object
855                    $this->{$key}[$k]->setParent($this);
856                }
857            } else {
858                $this->{$key} = $value;
859            }
860        }
861    }
862
863    /**
864     * toString method replicates previous behavior by returning the range if object is
865     * referenced as a property of its parent.
866     */
867    public function __toString()
868    {
869        return (string) $this->range;
870    }
871}
872