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