wpdatatables / lib / phpoffice / phpspreadsheet / src / PhpSpreadsheet / Calculation / Database / DatabaseAbstract.php

DatabaseAbstract.php in wpDataTables – WordPress Data Table, Dynamic Tables & Table Charts Plugin 6.5.1.7, at lib/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/Database/DatabaseAbstract.php

193 lines 7.6 KB

Raw Download Zip

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