tablepress / libraries / vendor / PhpSpreadsheet / Calculation / Database / DatabaseAbstract.php

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

233 lines 9.0 KB

Raw Download Zip

1	<?php
2
3	namespace TablePress\PhpOffice\PhpSpreadsheet\Calculation\Database;
4
5	use TablePress\PhpOffice\PhpSpreadsheet\Calculation\Calculation;
6	use TablePress\PhpOffice\PhpSpreadsheet\Calculation\Functions;
7	use TablePress\PhpOffice\PhpSpreadsheet\Calculation\Internal\WildcardMatch;
8	use TablePress\PhpOffice\PhpSpreadsheet\Shared\StringHelper;
9
10	abstract class DatabaseAbstract
11	{
12	/**
13	* @param mixed[] $database The range of cells that makes up the list or database.
14	* A database is a list of related data in which rows of related
15	* information are records, and columns of data are fields. The
16	* first row of the list contains labels for each column.
17	* @param null\|array<mixed>\|int\|string $field Indicates which column is used in the function. Enter the
18	* column label enclosed between double quotation marks, such as
19	* "Age" or "Yield," or a number (without quotation marks) that
20	* represents the position of the column within the list: 1 for
21	* the first column, 2 for the second column, and so on.
22	* @param mixed[] $criteria The range of cells that contains the conditions you specify.
23	* You can use any range for the criteria argument, as long as it
24	* includes at least one column label and at least one cell below
25	* the column label in which you specify a condition for the
26	* column.
27	* @return null\|float\|int\|string
28	*/
29	abstract public static function evaluate(array $database, $field, array $criteria);
30
31	/**
32	* fieldExtract.
33	*
34	* Extracts the column ID to use for the data field.
35	*
36	* @param mixed[] $database The range of cells that makes up the list or database.
37	* A database is a list of related data in which rows of related
38	* information are records, and columns of data are fields. The
39	* first row of the list contains labels for each column.
40	* @param mixed $field Indicates which column is used in the function. Enter the
41	* column label enclosed between double quotation marks, such as
42	* "Age" or "Yield," or a number (without quotation marks) that
43	* represents the position of the column within the list: 1 for
44	* the first column, 2 for the second column, and so on.
45	*/
46	protected static function fieldExtract(array $database, $field): ?int
47	{
48	/** @var ?string */
49	$single = Functions::flattenSingleValue($field);
50	$field = strtoupper($single ?? '');
51	if ($field === '') {
52	return null;
53	}
54
55	/** @var callable */
56	$callable = 'strtoupper';
57	$fieldNames = array_map($callable, array_shift($database)); //* @phpstan-ignore argument.type (array_shift can return mixed not array?)
58	if (is_numeric($field)) {
59	$field = (int) $field - 1;
60	if ($field < 0 \|\| $field >= count($fieldNames)) {
61	return null;
62	}
63
64	return $field;
65	}
66	$key = array_search($field, array_values($fieldNames), true);
67
68	return ($key !== false) ? (int) $key : null;
69	}
70
71	/**
72	* filter.
73	*
74	* Parses the selection criteria, extracts the database rows that match those criteria, and
75	* returns that subset of rows.
76	*
77	* @param mixed[] $database The range of cells that makes up the list or database.
78	* A database is a list of related data in which rows of related
79	* information are records, and columns of data are fields. The
80	* first row of the list contains labels for each column.
81	* @param mixed[][] $criteria The range of cells that contains the conditions you specify.
82	* You can use any range for the criteria argument, as long as it
83	* includes at least one column label and at least one cell below
84	* the column label in which you specify a condition for the
85	* column.
86	*
87	* @return mixed[]
88	*/
89	protected static function filter(array $database, array $criteria): array
90	{
91	/** @var mixed[] */
92	$fieldNames = array_shift($database);
93	$criteriaNames = array_shift($criteria);
94
95	// Convert the criteria into a set of AND/OR conditions with [:placeholders]
96	/** @var string[] $criteriaNames */
97	$query = self::buildQuery($criteriaNames, $criteria);
98
99	// Loop through each row of the database
100	/** @var mixed[][] $criteriaNames */
101	return self::executeQuery($database, $query, $criteriaNames, $fieldNames);
102	}
103
104	/**
105	* @param mixed[] $database The range of cells that makes up the list or database
106	* @param mixed[][] $criteria
107	*
108	* @return mixed[]
109	*/
110	protected static function getFilteredColumn(array $database, ?int $field, array $criteria): array
111	{
112	// reduce the database to a set of rows that match all the criteria
113	$database = self::filter($database, $criteria);
114	$defaultReturnColumnValue = ($field === null) ? 1 : null;
115
116	// extract an array of values for the requested column
117	$columnData = [];
118	/** @var mixed[] $row */
119	foreach ($database as $rowKey => $row) {
120	$keys = array_keys($row);
121	$key = ($field === null) ? null : ($keys[$field] ?? null);
122	$columnKey = $key ?? 'A';
123	$columnData[$rowKey][$columnKey] = ($key === null) ? $defaultReturnColumnValue : ($row[$key] ?? $defaultReturnColumnValue);
124	}
125
126	return $columnData;
127	}
128
129	/**
130	* @param string[] $criteriaNames
131	* @param mixed[][] $criteria
132	*/
133	private static function buildQuery(array $criteriaNames, array $criteria): string
134	{
135	$baseQuery = [];
136	foreach ($criteria as $key => $criterion) {
137	foreach ($criterion as $field => $value) {
138	$criterionName = $criteriaNames[$field];
139	if ($value !== null) {
140	$condition = self::buildCondition($value, $criterionName);
141	$baseQuery[$key][] = $condition;
142	}
143	}
144	}
145
146	$rowQuery = array_map(
147	fn ($rowValue): string => (count($rowValue) > 1) ? 'AND(' . implode(',', $rowValue) . ')' : ($rowValue[0] ?? ''), // @phpstan-ignore nullCoalesce.offset ($rowValue[0] always exists?)
148	$baseQuery
149	);
150
151	return (count($rowQuery) > 1) ? 'OR(' . implode(',', $rowQuery) . ')' : ($rowQuery[0] ?? '');
152	}
153
154	/**
155	* @param mixed $criterion
156	*/
157	private static function buildCondition($criterion, string $criterionName): string
158	{
159	$ifCondition = Functions::ifCondition($criterion);
160
161	// Check for wildcard characters used in the condition
162	$result = preg_match('/(?<operator>[^"])(?<operand>".[?].")/ui', $ifCondition, $matches);
163	if ($result !== 1) {
164	return "[:{$criterionName}]{$ifCondition}";
165	}
166
167	$trueFalse = ($matches['operator'] !== '<>');
168	$wildcard = WildcardMatch::wildcard($matches['operand']);
169	$condition = "WILDCARDMATCH([:{$criterionName}],{$wildcard})";
170	if ($trueFalse === false) {
171	$condition = "NOT({$condition})";
172	}
173
174	return $condition;
175	}
176
177	/**
178	* @param mixed[] $database
179	* @param mixed[][] $criteria
180	* @param array<mixed> $fields
181	*
182	* @return mixed[]
183	*/
184	private static function executeQuery(array $database, string $query, array $criteria, array $fields): array
185	{
186	foreach ($database as $dataRow => $dataValues) {
187	// Substitute actual values from the database row for our [:placeholders]
188	$conditions = $query;
189	foreach ($criteria as $criterion) {
190	/** @var string $criterion */
191	/** @var mixed[] $dataValues */
192	$conditions = self::processCondition($criterion, $fields, $dataValues, $conditions);
193	}
194
195	// evaluate the criteria against the row data
196	$result = Calculation::getInstance()->_calculateFormulaValue('=' . $conditions);
197
198	// If the row failed to meet the criteria, remove it from the database
199	if ($result !== true) {
200	unset($database[$dataRow]);
201	}
202	}
203
204	return $database;
205	}
206
207	/**
208	* @param array<mixed> $fields
209	* @param array<mixed> $dataValues
210	*/
211	private static function processCondition(string $criterion, array $fields, array $dataValues, string $conditions): string
212	{
213	$key = array_search($criterion, $fields, true);
214
215	$dataValue = 'NULL';
216	if (is_bool($dataValues[$key])) {
217	$dataValue = ($dataValues[$key]) ? 'TRUE' : 'FALSE';
218	} elseif ($dataValues[$key] !== null) {
219	$dataValue = $dataValues[$key];
220	// escape quotes if we have a string containing quotes
221	if (is_string($dataValue) && str_contains($dataValue, '"')) {
222	$dataValue = str_replace('"', '""', $dataValue);
223	}
224	if (is_string($dataValue)) {
225	$dataValue = Calculation::wrapResult(strtoupper($dataValue));
226	}
227	$dataValue = StringHelper::convertToString($dataValue);
228	}
229
230	return str_replace('[:' . $criterion . ']', $dataValue, $conditions);
231	}
232	}
233