tablepress / libraries / vendor / PhpSpreadsheet / Calculation / LookupRef / Sort.php

Sort.php in TablePress – Tables in WordPress made easy 3.4, at libraries/vendor/PhpSpreadsheet/Calculation/LookupRef/Sort.php

417 lines 12.8 KB

Raw Download Zip

1	<?php
2
3	namespace TablePress\PhpOffice\PhpSpreadsheet\Calculation\LookupRef;
4
5	use TablePress\PhpOffice\PhpSpreadsheet\Calculation\Exception;
6	use TablePress\PhpOffice\PhpSpreadsheet\Calculation\Functions;
7	use TablePress\PhpOffice\PhpSpreadsheet\Calculation\Information\ExcelError;
8	use TablePress\PhpOffice\PhpSpreadsheet\Cell\Coordinate;
9	use TablePress\PhpOffice\PhpSpreadsheet\Shared\StringHelper;
10
11	class Sort extends LookupRefValidations
12	{
13	public const ORDER_ASCENDING = 1;
14	public const ORDER_DESCENDING = -1;
15
16	/**
17	* SORT
18	* The SORT function returns a sorted array of the elements in an array.
19	* The returned array is the same shape as the provided array argument.
20	* Both $sortIndex and $sortOrder can be arrays, to provide multi-level sorting.
21	*
22	* NOTE: If $sortArray contains a mixture of data types
23	* (string/int/bool), the results may be unexpected.
24	* This is also true if the array consists of string
25	* representations of numbers, especially if there are
26	* both positive and negative numbers in the mix.
27	*
28	* @param mixed $sortArray The range of cells being sorted
29	* @param mixed $sortIndex The column or row number within the sortArray to sort on
30	* @param mixed $sortOrder Flag indicating whether to sort ascending or descending
31	* Ascending = 1 (self::ORDER_ASCENDING)
32	* Descending = -1 (self::ORDER_DESCENDING)
33	* @param mixed $byColumn Whether the sort should be determined by row (the default) or by column
34	*
35	* @return mixed The sorted values from the sort range
36	*/
37	public static function sort($sortArray, $sortIndex = 1, $sortOrder = self::ORDER_ASCENDING, $byColumn = false)
38	{
39	if (!is_array($sortArray)) {
40	$sortArray = [[$sortArray]];
41	}
42
43	/** @var mixed[][] */
44	$sortArray = self::enumerateArrayKeys($sortArray);
45
46	$byColumn = (bool) $byColumn;
47	$lookupIndexSize = $byColumn ? count($sortArray) : count($sortArray[0]);
48
49	try {
50	// If $sortIndex and $sortOrder are scalars, then convert them into arrays
51	if (!is_array($sortIndex)) {
52	$sortIndex = [$sortIndex];
53	$sortOrder = is_scalar($sortOrder) ? [$sortOrder] : $sortOrder;
54	}
55	// but the values of those array arguments still need validation
56	$sortOrder = (empty($sortOrder) ? [self::ORDER_ASCENDING] : $sortOrder);
57	self::validateArrayArgumentsForSort($sortIndex, $sortOrder, $lookupIndexSize);
58	} catch (Exception $e) {
59	return $e->getMessage();
60	}
61
62	// We want a simple, enumerated array of arrays where we can reference column by its index number.
63	/** @var callable(mixed): mixed */
64	$temp = 'array_values';
65	/** @var array<int> $sortOrder */
66	$sortArray = array_values(array_map($temp, $sortArray));
67	/** @var int[] $sortIndex */
68
69	return ($byColumn === true)
70	? self::sortByColumn($sortArray, $sortIndex, $sortOrder)
71	: self::sortByRow($sortArray, $sortIndex, $sortOrder);
72	}
73
74	/**
75	* SORTBY
76	* The SORTBY function sorts the contents of a range or array based on the values in a corresponding range or array.
77	* The returned array is the same shape as the provided array argument.
78	* Both $sortIndex and $sortOrder can be arrays, to provide multi-level sorting.
79	* Microsoft doesn't even bother documenting that a column sort
80	* is possible. However, it is. According to:
81	* https://exceljet.net/functions/sortby-function
82	* When by_array is a horizontal range, SORTBY sorts horizontally by columns.
83	* My interpretation of this is that by_array must be an
84	* array which contains exactly one row.
85	*
86	* NOTE: If the "byArray" contains a mixture of data types
87	* (string/int/bool), the results may be unexpected.
88	* This is also true if the array consists of string
89	* representations of numbers, especially if there are
90	* both positive and negative numbers in the mix.
91	*
92	* @param mixed $sortArray The range of cells being sorted
93	* @param mixed $args
94	* At least one additional argument must be provided, The vector or range to sort on
95	* After that, arguments are passed as pairs:
96	* sort order: ascending or descending
97	* Ascending = 1 (self::ORDER_ASCENDING)
98	* Descending = -1 (self::ORDER_DESCENDING)
99	* additional arrays or ranges for multi-level sorting
100	*
101	* @return mixed The sorted values from the sort range
102	*/
103	public static function sortBy($sortArray, ...$args)
104	{
105	if (!is_array($sortArray)) {
106	$sortArray = [[$sortArray]];
107	}
108	$transpose = false;
109	$args0 = $args[0] ?? null;
110	if (is_array($args0) && count($args0) === 1) {
111	$args0 = reset($args0);
112	if (is_array($args0) && count($args0) > 1) {
113	$transpose = true;
114	$sortArray = Matrix::transpose($sortArray);
115	}
116	}
117
118	$sortArray = self::enumerateArrayKeys($sortArray);
119
120	$lookupArraySize = count($sortArray);
121	$argumentCount = count($args);
122
123	try {
124	$sortBy = $sortOrder = [];
125	for ($i = 0; $i < $argumentCount; $i += 2) {
126	$argsI = $args[$i];
127	if (!is_array($argsI)) {
128	$argsI = [[$argsI]];
129	}
130	$sortBy[] = self::validateSortVector($argsI, $lookupArraySize);
131	$sortOrder[] = self::validateSortOrder($args[$i + 1] ?? self::ORDER_ASCENDING);
132	}
133	} catch (Exception $e) {
134	return $e->getMessage();
135	}
136
137	$temp = self::processSortBy($sortArray, $sortBy, $sortOrder);
138	if ($transpose) {
139	$temp = Matrix::transpose($temp);
140	}
141
142	return $temp;
143	}
144
145	/**
146	* @param mixed[] $sortArray
147	*
148	* @return mixed[]
149	*/
150	private static function enumerateArrayKeys(array $sortArray): array
151	{
152	array_walk(
153	$sortArray,
154	function (&$columns): void {
155	if (is_array($columns)) {
156	$columns = array_values($columns);
157	}
158	}
159	);
160
161	return array_values($sortArray);
162	}
163
164	/**
165	* @param mixed $sortIndex
166	* @param mixed $sortOrder
167	*/
168	private static function validateScalarArgumentsForSort(&$sortIndex, &$sortOrder, int $sortArraySize): void
169	{
170	$sortIndex = self::validatePositiveInt($sortIndex, false);
171
172	if ($sortIndex > $sortArraySize) {
173	throw new Exception(ExcelError::VALUE());
174	}
175
176	$sortOrder = self::validateSortOrder($sortOrder);
177	}
178
179	/**
180	* @param mixed[] $sortVector
181	*
182	* @return mixed[]
183	*/
184	private static function validateSortVector(array $sortVector, int $sortArraySize): array
185	{
186	// It doesn't matter if it's a row or a column vectors, it works either way
187	$sortVector = Functions::flattenArray($sortVector);
188	if (count($sortVector) !== $sortArraySize) {
189	throw new Exception(ExcelError::VALUE());
190	}
191
192	return $sortVector;
193	}
194
195	/**
196	* @param mixed $sortOrder
197	*/
198	private static function validateSortOrder($sortOrder): int
199	{
200	$sortOrder = self::validateInt($sortOrder);
201	if (($sortOrder == self::ORDER_ASCENDING \|\| $sortOrder === self::ORDER_DESCENDING) === false) {
202	throw new Exception(ExcelError::VALUE());
203	}
204
205	return $sortOrder;
206	}
207
208	/** @param mixed[] $sortIndex
209	* @param mixed $sortOrder */
210	private static function validateArrayArgumentsForSort(array &$sortIndex, &$sortOrder, int $sortArraySize): void
211	{
212	// It doesn't matter if they're row or column vectors, it works either way
213	$sortIndex = Functions::flattenArray($sortIndex);
214	$sortOrder = Functions::flattenArray($sortOrder);
215
216	if (
217	count($sortOrder) === 0 \|\| count($sortOrder) > $sortArraySize
218	\|\| (count($sortOrder) > count($sortIndex))
219	) {
220	throw new Exception(ExcelError::VALUE());
221	}
222
223	if (count($sortIndex) > count($sortOrder)) {
224	// If $sortOrder has fewer elements than $sortIndex, then the last order element is repeated.
225	$sortOrder = array_merge(
226	$sortOrder,
227	array_fill(0, count($sortIndex) - count($sortOrder), array_pop($sortOrder))
228	);
229	}
230
231	foreach ($sortIndex as $key => &$value) {
232	self::validateScalarArgumentsForSort($value, $sortOrder[$key], $sortArraySize);
233	}
234	}
235
236	/**
237	* @param mixed[] $sortVector
238	*
239	* @return mixed[]
240	*/
241	private static function prepareSortVectorValues(array $sortVector): array
242	{
243	// Strings should be sorted case-insensitive.
244	// Booleans are a complete mess. Excel always seems to sort
245	// booleans in a mixed vector at either the top or the bottom,
246	// so converting them to string or int doesn't really work.
247	// Best advice is to use them in a boolean-only vector.
248	// Code below chooses int conversion, which is sensible,
249	// and, as a bonus, compatible with LibreOffice.
250	return array_map(
251	function ($value) {
252	if (is_bool($value)) {
253	return (int) $value;
254	}
255	if (is_string($value)) {
256	return StringHelper::strToLower($value);
257	}
258
259	return $value;
260	},
261	$sortVector
262	);
263	}
264
265	/**
266	* @param mixed[] $sortArray
267	* @param mixed[] $sortIndex
268	* @param int[] $sortOrder
269	*
270	* @return mixed[]
271	*/
272	private static function processSortBy(array $sortArray, array $sortIndex, array $sortOrder): array
273	{
274	$sortArguments = [];
275	/** @var mixed[] */
276	$sortData = [];
277	foreach ($sortIndex as $index => $sortValues) {
278	/** @var mixed[] $sortValues */
279	$sortData[] = $sortValues;
280	$sortArguments[] = self::prepareSortVectorValues($sortValues);
281	$sortArguments[] = $sortOrder[$index] === self::ORDER_ASCENDING ? SORT_ASC : SORT_DESC;
282	}
283	$sortArguments = self::applyPHP7Patch($sortArray, $sortArguments);
284
285	$sortVector = self::executeVectorSortQuery($sortData, $sortArguments);
286
287	return self::sortLookupArrayFromVector($sortArray, $sortVector);
288	}
289
290	/**
291	* @param mixed[] $sortArray
292	* @param int[] $sortIndex
293	* @param int[] $sortOrder
294	*
295	* @return mixed[]
296	*/
297	private static function sortByRow(array $sortArray, array $sortIndex, array $sortOrder): array
298	{
299	$sortVector = self::buildVectorForSort($sortArray, $sortIndex, $sortOrder);
300
301	return self::sortLookupArrayFromVector($sortArray, $sortVector);
302	}
303
304	/**
305	* @param mixed[] $sortArray
306	* @param int[] $sortIndex
307	* @param int[] $sortOrder
308	*
309	* @return mixed[]
310	*/
311	private static function sortByColumn(array $sortArray, array $sortIndex, array $sortOrder): array
312	{
313	$sortArray = Matrix::transpose($sortArray);
314	$result = self::sortByRow($sortArray, $sortIndex, $sortOrder);
315
316	return Matrix::transpose($result);
317	}
318
319	/**
320	* @param mixed[] $sortArray
321	* @param int[] $sortIndex
322	* @param int[] $sortOrder
323	*
324	* @return mixed[]
325	*/
326	private static function buildVectorForSort(array $sortArray, array $sortIndex, array $sortOrder): array
327	{
328	$sortArguments = [];
329	$sortData = [];
330	foreach ($sortIndex as $index => $sortIndexValue) {
331	$sortValues = array_column($sortArray, $sortIndexValue - 1);
332	$sortData[] = $sortValues;
333	$sortArguments[] = self::prepareSortVectorValues($sortValues);
334	$sortArguments[] = $sortOrder[$index] === self::ORDER_ASCENDING ? SORT_ASC : SORT_DESC;
335	}
336	$sortArguments = self::applyPHP7Patch($sortArray, $sortArguments);
337
338	$sortData = self::executeVectorSortQuery($sortData, $sortArguments);
339
340	return $sortData;
341	}
342
343	/**
344	* @param mixed[] $sortData
345	* @param mixed[] $sortArguments
346	*
347	* @return mixed[]
348	*/
349	private static function executeVectorSortQuery(array $sortData, array $sortArguments): array
350	{
351	$sortData = Matrix::transpose($sortData);
352
353	// We need to set an index that can be retained, as array_multisort doesn't maintain numeric keys.
354	$sortDataIndexed = [];
355	foreach ($sortData as $key => $value) {
356	$sortDataIndexed[Coordinate::stringFromColumnIndex($key + 1)] = $value;
357	}
358	unset($sortData);
359
360	$sortArguments[] = &$sortDataIndexed;
361
362	array_multisort(...$sortArguments);
363
364	// After the sort, we restore the numeric keys that will now be in the correct, sorted order
365	$sortedData = [];
366	foreach (array_keys($sortDataIndexed) as $key) {
367	$sortedData[] = Coordinate::columnIndexFromString($key) - 1;
368	}
369
370	return $sortedData;
371	}
372
373	/**
374	* @param mixed[] $sortArray
375	* @param mixed[] $sortVector
376	*
377	* @return mixed[]
378	*/
379	private static function sortLookupArrayFromVector(array $sortArray, array $sortVector): array
380	{
381	// Building a new array in the correct (sorted) order works; but may be memory heavy for larger arrays
382	$sortedArray = [];
383	foreach ($sortVector as $index) {
384	/** @var int\|string $index */
385	$sortedArray[] = $sortArray[$index];
386	}
387
388	return $sortedArray;
389
390	// uksort(
391	// $lookupArray,
392	// function (int $a, int $b) use (array $sortVector) {
393	// return $sortVector[$a] <=> $sortVector[$b];
394	// }
395	// );
396	//
397	// return $lookupArray;
398	}
399
400	/**
401	* Hack to handle PHP 7:
402	* From PHP 8.0.0, If two members compare as equal in a sort, they retain their original order;
403	* but prior to PHP 8.0.0, their relative order in the sorted array was undefined.
404	* MS Excel replicates the PHP 8.0.0 behaviour, retaining the original order of matching elements.
405	* To replicate that behaviour with PHP 7, we add an extra sort based on the row index.
406	*/
407	private static function applyPHP7Patch(array $sortArray, array $sortArguments): array
408	{
409	if (PHP_VERSION_ID < 80000) {
410	$sortArguments[] = range(1, count($sortArray));
411	$sortArguments[] = SORT_ASC;
412	}
413
414	return $sortArguments;
415	}
416	}
417