ILIAS  release_5-4 Revision v5.4.26-12-gabc799a52e6
DatabaseAbstract.php
Go to the documentation of this file.
1<?php
2
4
8
9abstract class DatabaseAbstract
10{
11 abstract public static function evaluate($database, $field, $criteria);
12
28 protected static function fieldExtract(array $database, $field): ?int
29 {
30 $field = strtoupper(Functions::flattenSingleValue($field));
31 if ($field === '') {
32 return null;
33 }
34
35 $fieldNames = array_map('strtoupper', array_shift($database));
36 if (is_numeric($field)) {
37 return ((int) $field) - 1;
38 }
39 $key = array_search($field, array_values($fieldNames), true);
40
41 return ($key !== false) ? (int) $key : null;
42 }
43
62 protected static function filter(array $database, array $criteria): array
63 {
64 $fieldNames = array_shift($database);
65 $criteriaNames = array_shift($criteria);
66
67 // Convert the criteria into a set of AND/OR conditions with [:placeholders]
68 $query = self::buildQuery($criteriaNames, $criteria);
69
70 // Loop through each row of the database
71 return self::executeQuery($database, $query, $criteriaNames, $fieldNames);
72 }
73
74 protected static function getFilteredColumn(array $database, ?int $field, array $criteria): array
75 {
76 // reduce the database to a set of rows that match all the criteria
77 $database = self::filter($database, $criteria);
78 $defaultReturnColumnValue = ($field === null) ? 1 : null;
79
80 // extract an array of values for the requested column
81 $columnData = [];
82 foreach ($database as $rowKey => $row) {
83 $keys = array_keys($row);
84 $key = $keys[$field] ?? null;
85 $columnKey = $key ?? 'A';
86 $columnData[$rowKey][$columnKey] = $row[$key] ?? $defaultReturnColumnValue;
87 }
88
89 return $columnData;
90 }
91
92 private static function buildQuery(array $criteriaNames, array $criteria): string
93 {
94 $baseQuery = [];
95 foreach ($criteria as $key => $criterion) {
96 foreach ($criterion as $field => $value) {
97 $criterionName = $criteriaNames[$field];
98 if ($value !== null && $value !== '') {
99 $condition = self::buildCondition($value, $criterionName);
100 $baseQuery[$key][] = $condition;
101 }
102 }
103 }
104
105 $rowQuery = array_map(
106 function ($rowValue) {
107 return (count($rowValue) > 1) ? 'AND(' . implode(',', $rowValue) . ')' : $rowValue[0];
108 },
109 $baseQuery
110 );
111
112 return (count($rowQuery) > 1) ? 'OR(' . implode(',', $rowQuery) . ')' : $rowQuery[0];
113 }
114
115 private static function buildCondition($criterion, string $criterionName): string
116 {
117 $ifCondition = Functions::ifCondition($criterion);
118
119 // Check for wildcard characters used in the condition
120 $result = preg_match('/(?<operator>[^"]*)(?<operand>".*[*?].*")/ui', $ifCondition, $matches);
121 if ($result !== 1) {
122 return "[:{$criterionName}]{$ifCondition}";
123 }
124
125 $trueFalse = ($matches['operator'] !== '<>');
126 $wildcard = WildcardMatch::wildcard($matches['operand']);
127 $condition = "WILDCARDMATCH([:{$criterionName}],{$wildcard})";
128 if ($trueFalse === false) {
129 $condition = "NOT({$condition})";
130 }
131
132 return $condition;
133 }
134
135 private static function executeQuery(array $database, string $query, array $criteria, array $fields): array
136 {
137 foreach ($database as $dataRow => $dataValues) {
138 // Substitute actual values from the database row for our [:placeholders]
139 $conditions = $query;
140 foreach ($criteria as $criterion) {
141 $conditions = self::processCondition($criterion, $fields, $dataValues, $conditions);
142 }
143
144 // evaluate the criteria against the row data
145 $result = Calculation::getInstance()->_calculateFormulaValue('=' . $conditions);
146
147 // If the row failed to meet the criteria, remove it from the database
148 if ($result !== true) {
149 unset($database[$dataRow]);
150 }
151 }
152
153 return $database;
154 }
155
156 private static function processCondition(string $criterion, array $fields, array $dataValues, string $conditions)
157 {
158 $key = array_search($criterion, $fields, true);
159
160 $dataValue = 'NULL';
161 if (is_bool($dataValues[$key])) {
162 $dataValue = ($dataValues[$key]) ? 'TRUE' : 'FALSE';
163 } elseif ($dataValues[$key] !== null) {
164 $dataValue = $dataValues[$key];
165 // escape quotes if we have a string containing quotes
166 if (is_string($dataValue) && strpos($dataValue, '"') !== false) {
167 $dataValue = str_replace('"', '""', $dataValue);
168 }
169 $dataValue = (is_string($dataValue)) ? Calculation::wrapResult(strtoupper($dataValue)) : $dataValue;
170 }
171
172 return str_replace('[:' . $criterion . ']', $dataValue, $conditions);
173 }
174}
$result
An exception for terminatinating execution or to throw for unit testing.
static wrapResult($value)
Wrap string values in quotes.
static getInstance(?Spreadsheet $spreadsheet=null)
Get an instance of this class.
static buildQuery(array $criteriaNames, array $criteria)
static processCondition(string $criterion, array $fields, array $dataValues, string $conditions)
static buildCondition($criterion, string $criterionName)
static executeQuery(array $database, string $query, array $criteria, array $fields)
static getFilteredColumn(array $database, ?int $field, array $criteria)
static filter(array $database, array $criteria)
filter.
static fieldExtract(array $database, $field)
fieldExtract.
static flattenSingleValue($value='')
Convert an array to a single scalar value by extracting the first element.
Definition: Functions.php:649
$key
Definition: croninfo.php:18
$keys
$row
$query