tablepress / libraries / vendor / PhpSpreadsheet / Calculation / Statistical / Conditional.php

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

372 lines 10.3 KB

Raw Download Zip

1	<?php
2
3	namespace TablePress\PhpOffice\PhpSpreadsheet\Calculation\Statistical;
4
5	use TablePress\PhpOffice\PhpSpreadsheet\Calculation\Database\DAverage;
6	use TablePress\PhpOffice\PhpSpreadsheet\Calculation\Database\DCount;
7	use TablePress\PhpOffice\PhpSpreadsheet\Calculation\Database\DMax;
8	use TablePress\PhpOffice\PhpSpreadsheet\Calculation\Database\DMin;
9	use TablePress\PhpOffice\PhpSpreadsheet\Calculation\Database\DSum;
10	use TablePress\PhpOffice\PhpSpreadsheet\Calculation\Exception as CalcException;
11	use TablePress\PhpOffice\PhpSpreadsheet\Calculation\Functions;
12	use TablePress\PhpOffice\PhpSpreadsheet\Calculation\Information\ExcelError;
13	use TablePress\PhpOffice\PhpSpreadsheet\Shared\StringHelper;
14
15	class Conditional
16	{
17	private const CONDITION_COLUMN_NAME = 'CONDITION';
18	private const VALUE_COLUMN_NAME = 'VALUE';
19	private const CONDITIONAL_COLUMN_NAME = 'CONDITIONAL %d';
20
21	/**
22	* AVERAGEIF.
23	*
24	* Returns the average value from a range of cells that contain numbers within the list of arguments
25	*
26	* Excel Function:
27	* AVERAGEIF(range,condition[, average_range])
28	*
29	* @param mixed $range Data values, expect array
30	* @param mixed $condition the criteria that defines which cells will be checked, expect null\|mixed[]\|string
31	* @param mixed $averageRange Data values
32	* @return null\|int\|float\|string
33	*/
34	public static function AVERAGEIF($range, $condition, $averageRange = [])
35	{
36	if ($condition !== null && !is_array($condition)) {
37	$condition = StringHelper::convertToString($condition);
38	}
39	if (!is_array($range) \|\| !is_array($averageRange) \|\| array_key_exists(0, $range) \|\| array_key_exists(0, $averageRange)) {
40	$refError = ExcelError::REF();
41	if (in_array($refError, [$range, $averageRange], true)) {
42	return $refError;
43	}
44
45	throw new CalcException('Must specify range of cells, not any kind of literal');
46	}
47	$database = self::databaseFromRangeAndValue($range, $averageRange);
48	$condition = Functions::flattenSingleValue($condition);
49	$condition = [[self::CONDITION_COLUMN_NAME, self::VALUE_COLUMN_NAME], [$condition, null]];
50
51	return DAverage::evaluate($database, self::VALUE_COLUMN_NAME, $condition);
52	}
53
54	/**
55	* AVERAGEIFS.
56	*
57	* Counts the number of cells that contain numbers within the list of arguments
58	*
59	* Excel Function:
60	* AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2]…)
61	*
62	* @param mixed $args Pairs of Ranges and Criteria
63	* @return null\|int\|float\|string
64	*/
65	public static function AVERAGEIFS(...$args)
66	{
67	if (empty($args)) {
68	return 0.0;
69	}
70	if (count($args) === 3) {
71	return self::AVERAGEIF($args[1], $args[2], $args[0]);
72	}
73	foreach ($args as $arg) {
74	if (is_array($arg) && array_key_exists(0, $arg)) {
75	throw new CalcException('Must specify range of cells, not any kind of literal');
76	}
77	}
78
79	$conditions = self::buildConditionSetForValueRange(...$args);
80	$database = self::buildDatabaseWithValueRange(...$args);
81
82	return DAverage::evaluate($database, self::VALUE_COLUMN_NAME, $conditions);
83	}
84
85	/**
86	* COUNTIF.
87	*
88	* Counts the number of cells that contain numbers within the list of arguments
89	*
90	* Excel Function:
91	* COUNTIF(range,condition)
92	*
93	* @param mixed $range Data values, expect array
94	* @param null\|mixed[]\|string $condition the criteria that defines which cells will be counted
95	* @return string\|int
96	*/
97	public static function COUNTIF($range, $condition)
98	{
99	if (
100	!is_array($range)
101	\|\| array_key_exists(0, $range)
102	) {
103	if ($range === ExcelError::REF()) {
104	return $range;
105	}
106
107	throw new CalcException('Must specify range of cells, not any kind of literal');
108	}
109	// Filter out any empty values that shouldn't be included in a COUNT
110	$range = array_filter(
111	Functions::flattenArray($range),
112	fn ($value): bool => $value !== null && $value !== ''
113	);
114
115	$range = array_merge([[self::CONDITION_COLUMN_NAME]], array_chunk($range, 1));
116	$condition = Functions::flattenSingleValue($condition);
117	$condition = array_merge([[self::CONDITION_COLUMN_NAME]], [[$condition]]);
118
119	return DCount::evaluate($range, null, $condition, false);
120	}
121
122	/**
123	* COUNTIFS.
124	*
125	* Counts the number of cells that contain numbers within the list of arguments
126	*
127	* Excel Function:
128	* COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)
129	*
130	* @param mixed $args Pairs of Ranges and Criteria
131	* @return int\|string
132	*/
133	public static function COUNTIFS(...$args)
134	{
135	if (empty($args)) {
136	return 0;
137	} elseif (count($args) === 2) {
138	return self::COUNTIF(...$args);
139	}
140
141	$database = self::buildDatabase(...$args);
142	$conditions = self::buildConditionSet(...$args);
143
144	return DCount::evaluate($database, null, $conditions, false);
145	}
146
147	/**
148	* MAXIFS.
149	*
150	* Returns the maximum value within a range of cells that contain numbers within the list of arguments
151	*
152	* Excel Function:
153	* MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2]…)
154	*
155	* @param mixed $args Pairs of Ranges and Criteria
156	* @return null\|float\|string
157	*/
158	public static function MAXIFS(...$args)
159	{
160	if (empty($args)) {
161	return 0.0;
162	}
163
164	$conditions = self::buildConditionSetForValueRange(...$args);
165	$database = self::buildDatabaseWithValueRange(...$args);
166
167	return DMax::evaluate($database, self::VALUE_COLUMN_NAME, $conditions, false);
168	}
169
170	/**
171	* MINIFS.
172	*
173	* Returns the minimum value within a range of cells that contain numbers within the list of arguments
174	*
175	* Excel Function:
176	* MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2]…)
177	*
178	* @param mixed $args Pairs of Ranges and Criteria
179	* @return null\|float\|string
180	*/
181	public static function MINIFS(...$args)
182	{
183	if (empty($args)) {
184	return 0.0;
185	}
186
187	$conditions = self::buildConditionSetForValueRange(...$args);
188	$database = self::buildDatabaseWithValueRange(...$args);
189
190	return DMin::evaluate($database, self::VALUE_COLUMN_NAME, $conditions, false);
191	}
192
193	/**
194	* SUMIF.
195	*
196	* Totals the values of cells that contain numbers within the list of arguments
197	*
198	* Excel Function:
199	* SUMIF(range, criteria, [sum_range])
200	*
201	* @param mixed $range Data values, expecting array
202	* @param mixed $sumRange Data values, expecting array
203	* @return null\|float\|string
204	* @param mixed $condition
205	*/
206	public static function SUMIF($range, $condition, $sumRange = [])
207	{
208	if (
209	!is_array($range)
210	\|\| array_key_exists(0, $range)
211	\|\| !is_array($sumRange)
212	\|\| array_key_exists(0, $sumRange)
213	) {
214	$refError = ExcelError::REF();
215	if (in_array($refError, [$range, $sumRange], true)) {
216	return $refError;
217	}
218
219	throw new CalcException('Must specify range of cells, not any kind of literal');
220	}
221	$database = self::databaseFromRangeAndValue($range, $sumRange);
222	$condition = Functions::flattenSingleValue($condition);
223	$condition = [[self::CONDITION_COLUMN_NAME, self::VALUE_COLUMN_NAME], [$condition, null]];
224
225	return DSum::evaluate($database, self::VALUE_COLUMN_NAME, $condition);
226	}
227
228	/**
229	* SUMIFS.
230	*
231	* Counts the number of cells that contain numbers within the list of arguments
232	*
233	* Excel Function:
234	* SUMIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2]…)
235	*
236	* @param mixed $args Pairs of Ranges and Criteria
237	* @return null\|float\|string
238	*/
239	public static function SUMIFS(...$args)
240	{
241	if (empty($args)) {
242	return 0.0;
243	} elseif (count($args) === 3) {
244	return self::SUMIF($args[1], $args[2], $args[0]);
245	}
246
247	$conditions = self::buildConditionSetForValueRange(...$args);
248	$database = self::buildDatabaseWithValueRange(...$args);
249
250	return DSum::evaluate($database, self::VALUE_COLUMN_NAME, $conditions);
251	}
252
253	/**
254	* @param mixed[] $args
255	*
256	* @return mixed[][]
257	*/
258	private static function buildConditionSet(...$args): array
259	{
260	$conditions = self::buildConditions(1, ...$args);
261
262	return array_map(null, ...$conditions);
263	}
264
265	/**
266	* @param mixed[] $args
267	*
268	* @return mixed[][]
269	*/
270	private static function buildConditionSetForValueRange(...$args): array
271	{
272	$conditions = self::buildConditions(2, ...$args);
273
274	if (count($conditions) === 1) {
275	return array_map(
276	fn ($value): array => [$value],
277	$conditions[0]
278	);
279	}
280
281	return array_map(null, ...$conditions);
282	}
283
284	/**
285	* @param mixed[] $args
286	*
287	* @return mixed[][]
288	*/
289	private static function buildConditions(int $startOffset, ...$args): array
290	{
291	$conditions = [];
292
293	$pairCount = 1;
294	$argumentCount = count($args);
295	for ($argument = $startOffset; $argument < $argumentCount; $argument += 2) {
296	$conditions[] = array_merge([sprintf(self::CONDITIONAL_COLUMN_NAME, $pairCount)], [$args[$argument]]);
297	++$pairCount;
298	}
299
300	return $conditions;
301	}
302
303	/**
304	* @param mixed[] $args
305	*
306	* @return mixed[]
307	*/
308	private static function buildDatabase(...$args): array
309	{
310	$database = [];
311
312	return self::buildDataSet(0, $database, ...$args);
313	}
314
315	/**
316	* @param mixed[] $args
317	*
318	* @return mixed[]
319	*/
320	private static function buildDatabaseWithValueRange(...$args): array
321	{
322	$database = [];
323	$database[] = array_merge(
324	[self::VALUE_COLUMN_NAME],
325	Functions::flattenArray($args[0])
326	);
327
328	return self::buildDataSet(1, $database, ...$args);
329	}
330
331	/**
332	* @param mixed[][] $database
333	* @param mixed[] $args
334	*
335	* @return mixed[]
336	*/
337	private static function buildDataSet(int $startOffset, array $database, ...$args): array
338	{
339	$pairCount = 1;
340	$argumentCount = count($args);
341	for ($argument = $startOffset; $argument < $argumentCount; $argument += 2) {
342	$database[] = array_merge(
343	[sprintf(self::CONDITIONAL_COLUMN_NAME, $pairCount)],
344	Functions::flattenArray($args[$argument])
345	);
346	++$pairCount;
347	}
348
349	return array_map(null, ...$database);
350	}
351
352	/**
353	* @param mixed[] $range
354	* @param mixed[] $valueRange
355	*
356	* @return mixed[]
357	*/
358	private static function databaseFromRangeAndValue(array $range, array $valueRange = []): array
359	{
360	$range = Functions::flattenArray($range);
361
362	$valueRange = Functions::flattenArray($valueRange);
363	if (empty($valueRange)) {
364	$valueRange = $range;
365	}
366
367	$database = array_map(null, array_merge([self::CONDITION_COLUMN_NAME], $range), array_merge([self::VALUE_COLUMN_NAME], $valueRange));
368
369	return $database;
370	}
371	}
372