Code Coverage |
||||||||||
Lines |
Functions and Methods |
Classes and Traits |
||||||||
Total | |
23.93% |
95 / 397 |
|
37.50% |
9 / 24 |
CRAP | |
0.00% |
0 / 1 |
MonitoringRepository | |
23.93% |
95 / 397 |
|
37.50% |
9 / 24 |
4958.20 | |
0.00% |
0 / 1 |
createMonitoringData | |
100.00% |
8 / 8 |
|
100.00% |
1 / 1 |
2 | |||
getData | |
100.00% |
4 / 4 |
|
100.00% |
1 / 1 |
1 | |||
find | |
0.00% |
0 / 38 |
|
0.00% |
0 / 1 |
72 | |||
count | |
0.00% |
0 / 8 |
|
0.00% |
0 / 1 |
6 | |||
save | |
66.67% |
6 / 9 |
|
0.00% |
0 / 1 |
5.93 | |||
partialSave | |
77.78% |
7 / 9 |
|
0.00% |
0 / 1 |
5.27 | |||
delete | |
0.00% |
0 / 3 |
|
0.00% |
0 / 1 |
2 | |||
getPersistence | |
100.00% |
3 / 3 |
|
100.00% |
1 / 1 |
1 | |||
getCountOfStatistics | |
0.00% |
0 / 10 |
|
0.00% |
0 / 1 |
2 | |||
getStatusesStatistic | |
0.00% |
0 / 119 |
|
0.00% |
0 / 1 |
182 | |||
deleteDeliveryExecutionData | |
0.00% |
0 / 3 |
|
0.00% |
0 / 1 |
2 | |||
prepareOrderStmt | |
0.00% |
0 / 7 |
|
0.00% |
0 / 1 |
12 | |||
buildSingleOrderRule | |
0.00% |
0 / 19 |
|
0.00% |
0 / 1 |
30 | |||
buildNumericOrderWithCastingToDecimal | |
0.00% |
0 / 3 |
|
0.00% |
0 / 1 |
6 | |||
loadData | |
64.29% |
9 / 14 |
|
0.00% |
0 / 1 |
4.73 | |||
create | |
100.00% |
6 / 6 |
|
100.00% |
1 / 1 |
2 | |||
reformatExtraData | |
62.50% |
5 / 8 |
|
0.00% |
0 / 1 |
3.47 | |||
update | |
69.57% |
32 / 46 |
|
0.00% |
0 / 1 |
7.01 | |||
getPrimaryColumns | |
100.00% |
1 / 1 |
|
100.00% |
1 / 1 |
1 | |||
extractPrimaryData | |
100.00% |
6 / 6 |
|
100.00% |
1 / 1 |
3 | |||
extractKvData | |
100.00% |
6 / 6 |
|
100.00% |
1 / 1 |
3 | |||
prepareCondition | |
0.00% |
0 / 65 |
|
0.00% |
0 / 1 |
992 | |||
getQueryBuilder | |
100.00% |
1 / 1 |
|
100.00% |
1 / 1 |
1 | |||
getPlatformName | |
100.00% |
1 / 1 |
|
100.00% |
1 / 1 |
1 |
1 | <?php |
2 | |
3 | /** |
4 | * This program is free software; you can redistribute it and/or |
5 | * modify it under the terms of the GNU General Public License |
6 | * as published by the Free Software Foundation; under version 2 |
7 | * of the License (non-upgradable). |
8 | * |
9 | * This program is distributed in the hope that it will be useful, |
10 | * but WITHOUT ANY WARRANTY; without even the implied warranty of |
11 | * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the |
12 | * GNU General Public License for more details. |
13 | * |
14 | * You should have received a copy of the GNU General Public License |
15 | * along with this program; if not, write to the Free Software |
16 | * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA. |
17 | * |
18 | * Copyright (c) 2021 (original work) Open Assessment Technologies SA; |
19 | * |
20 | */ |
21 | |
22 | declare(strict_types=1); |
23 | |
24 | namespace oat\taoProctoring\model\repository; |
25 | |
26 | use common_exception_NotFound; |
27 | use common_persistence_SqlPersistence; |
28 | use Doctrine\DBAL\Exception\UniqueConstraintViolationException; |
29 | use Doctrine\DBAL\Query\QueryBuilder; |
30 | use Exception; |
31 | use oat\generis\model\OntologyAwareTrait; |
32 | use oat\generis\persistence\PersistenceManager; |
33 | use oat\oatbox\service\ConfigurableService; |
34 | use oat\taoDelivery\model\execution\Delete\DeliveryExecutionDeleteRequest; |
35 | use oat\taoDelivery\model\execution\DeliveryExecutionInterface; |
36 | use oat\taoDelivery\model\execution\ServiceProxy; |
37 | use oat\taoProctoring\model\execution\DeliveryExecution as ProctoredDeliveryExecution; |
38 | use oat\taoProctoring\model\monitorCache\DeliveryMonitoringData as DeliveryMonitoringDataInterface; |
39 | use oat\taoProctoring\model\monitorCache\DeliveryMonitoringService; |
40 | use oat\taoProctoring\model\monitorCache\implementation\DeliveryMonitoringData; |
41 | use PDO; |
42 | use PDOException; |
43 | |
44 | class MonitoringRepository extends ConfigurableService implements DeliveryMonitoringService |
45 | { |
46 | use OntologyAwareTrait; |
47 | |
48 | public const OPTION_PERSISTENCE = 'persistence'; |
49 | public const OPTION_USE_UPDATE_MULTIPLE = 'use_update_multiple'; |
50 | |
51 | public const OPTION_PRIMARY_COLUMNS = 'primary_columns'; |
52 | |
53 | public const TABLE_NAME = 'delivery_monitoring'; |
54 | |
55 | public const COLUMN_ID = DeliveryMonitoringService::DELIVERY_EXECUTION_ID; |
56 | public const COLUMN_DELIVERY_EXECUTION_ID = DeliveryMonitoringService::DELIVERY_EXECUTION_ID; |
57 | public const COLUMN_STATUS = DeliveryMonitoringService::STATUS; |
58 | public const COLUMN_CURRENT_ASSESSMENT_ITEM = DeliveryMonitoringService::CURRENT_ASSESSMENT_ITEM; |
59 | public const COLUMN_TEST_TAKER = DeliveryMonitoringService::TEST_TAKER; |
60 | public const COLUMN_TEST_TAKER_FIRST_NAME = DeliveryMonitoringService::TEST_TAKER_FIRST_NAME; |
61 | public const COLUMN_TEST_TAKER_LAST_NAME = DeliveryMonitoringService::TEST_TAKER_LAST_NAME; |
62 | public const COLUMN_AUTHORIZED_BY = DeliveryMonitoringService::AUTHORIZED_BY; |
63 | public const COLUMN_START_TIME = DeliveryMonitoringService::START_TIME; |
64 | public const COLUMN_END_TIME = DeliveryMonitoringService::END_TIME; |
65 | public const COLUMN_REMAINING_TIME = DeliveryMonitoringService::REMAINING_TIME; |
66 | public const COLUMN_EXTRA_TIME = DeliveryMonitoringService::EXTRA_TIME; |
67 | public const COLUMN_CONSUMED_EXTRA_TIME = DeliveryMonitoringService::CONSUMED_EXTRA_TIME; |
68 | |
69 | public const COLUMN_EXTRA_DATA = 'extra_data'; |
70 | |
71 | public const DEFAULT_SORT_COLUMN = self::COLUMN_ID; |
72 | public const DEFAULT_SORT_ORDER = 'ASC'; |
73 | public const DEFAULT_SORT_TYPE = 'string'; |
74 | |
75 | private $queryParams = []; |
76 | |
77 | /** |
78 | * @param DeliveryExecutionInterface $deliveryExecution |
79 | * @param $data |
80 | * @return DeliveryMonitoringData |
81 | * @throws common_exception_NotFound |
82 | */ |
83 | public function createMonitoringData(DeliveryExecutionInterface $deliveryExecution, $data = []) |
84 | { |
85 | $data = array_merge([ |
86 | DeliveryMonitoringService::DELIVERY_EXECUTION_ID => $deliveryExecution->getIdentifier(), |
87 | ], $data); |
88 | |
89 | if (!array_key_exists(DeliveryMonitoringService::STATUS, $data)) { |
90 | $data[DeliveryMonitoringService::STATUS] = $deliveryExecution->getState()->getUri(); |
91 | } |
92 | |
93 | // @todo data object should not use ServiceLocator |
94 | $monitoringData = new DeliveryMonitoringData($deliveryExecution, $data); |
95 | $this->propagate($monitoringData); |
96 | |
97 | return $monitoringData; |
98 | } |
99 | |
100 | /** |
101 | * @param DeliveryExecutionInterface $deliveryExecution |
102 | * @return array|DeliveryMonitoringData |
103 | * @throws common_exception_NotFound |
104 | */ |
105 | public function getData(DeliveryExecutionInterface $deliveryExecution): DeliveryMonitoringData |
106 | { |
107 | return $this->createMonitoringData( |
108 | $deliveryExecution, |
109 | $this->loadData($deliveryExecution->getIdentifier()) |
110 | ); |
111 | } |
112 | |
113 | /** |
114 | * @param array $criteria |
115 | * @param array $options |
116 | * @return DeliveryMonitoringData[] |
117 | * @throws common_exception_NotFound |
118 | */ |
119 | public function find(array $criteria = [], array $options = []): array |
120 | { |
121 | $this->queryParams = []; |
122 | |
123 | $whereClause = $this->prepareCondition($criteria, $this->queryParams); |
124 | if ($whereClause !== '') { |
125 | $whereClause = 'WHERE ' . $whereClause; |
126 | } |
127 | |
128 | $defaultOptions = [ |
129 | 'order' => join(' ', [static::DEFAULT_SORT_COLUMN, static::DEFAULT_SORT_ORDER, static::DEFAULT_SORT_TYPE]), |
130 | 'offset' => 0, |
131 | 'asArray' => false |
132 | ]; |
133 | $options = array_merge($defaultOptions, $options); |
134 | |
135 | $orderClause = $this->prepareOrderStmt($options['order']); |
136 | if ($orderClause !== '') { |
137 | $orderClause = 'ORDER BY ' . $orderClause; |
138 | } |
139 | |
140 | $sql = sprintf( |
141 | 'SELECT %s FROM %s t %s %s', |
142 | implode(', ', $this->getPrimaryColumns()), |
143 | self::TABLE_NAME, |
144 | $whereClause, |
145 | $orderClause |
146 | ); |
147 | |
148 | if (isset($options['limit'])) { |
149 | $sql = $this->getPersistence()->getPlatForm()->limitStatement($sql, $options['limit'], $options['offset']); |
150 | } |
151 | |
152 | $stmt = $this->getPersistence()->query($sql, $this->queryParams); |
153 | |
154 | $data = $stmt->fetchAll(PDO::FETCH_ASSOC); |
155 | |
156 | foreach ($data as &$row) { |
157 | $extraData = []; |
158 | if (isset($row[self::COLUMN_EXTRA_DATA])) { |
159 | $decodedExtraData = json_decode($row[self::COLUMN_EXTRA_DATA], true); |
160 | if (json_last_error() === JSON_ERROR_NONE) { |
161 | $extraData = $decodedExtraData; |
162 | } |
163 | } |
164 | unset($row[self::COLUMN_EXTRA_DATA]); |
165 | $row = array_merge($row, $extraData); |
166 | |
167 | if (!$options['asArray']) { |
168 | $deliveryExecution = ServiceProxy::singleton()->getDeliveryExecution( |
169 | $row[self::COLUMN_DELIVERY_EXECUTION_ID] |
170 | ); |
171 | $row = $this->createMonitoringData($deliveryExecution, $row); |
172 | } |
173 | } |
174 | |
175 | return $data; |
176 | } |
177 | |
178 | public function count(array $criteria = []): int |
179 | { |
180 | $this->queryParams = []; |
181 | |
182 | $whereClause = $this->prepareCondition($criteria, $this->queryParams); |
183 | if ($whereClause !== '') { |
184 | $whereClause = 'WHERE ' . $whereClause; |
185 | } |
186 | |
187 | $sql = sprintf('select count(*) from %s t %s', self::TABLE_NAME, $whereClause); |
188 | |
189 | $stmt = $this->getPersistence()->query($sql, $this->queryParams); |
190 | $result = $stmt->fetch(PDO::FETCH_BOTH); |
191 | |
192 | return (int) $result[0]; |
193 | } |
194 | |
195 | /** |
196 | * @param DeliveryMonitoringDataInterface $deliveryMonitoring |
197 | * @return bool|mixed |
198 | * @throws Exception |
199 | */ |
200 | public function save(DeliveryMonitoringDataInterface $deliveryMonitoring): bool |
201 | { |
202 | $result = false; |
203 | if ($deliveryMonitoring->validate()) { |
204 | try { |
205 | // we should be ready for unique violation error when the calling side calls |
206 | // save() instead of partialSave() |
207 | $result = $this->create($deliveryMonitoring); |
208 | } catch (PDOException $e) { |
209 | // when the PDO implementation of RDS is used as a persistence |
210 | // unfortunately the exception is very broad so it can cover more than intended cases |
211 | } catch (UniqueConstraintViolationException $e) { |
212 | // when the DBAL implementation of RDS is used as a persistence |
213 | } |
214 | if (!$result) { |
215 | $this->update($deliveryMonitoring); |
216 | } |
217 | |
218 | $result = true; |
219 | } |
220 | |
221 | return $result; |
222 | } |
223 | |
224 | /** |
225 | * @param DeliveryMonitoringDataInterface $deliveryMonitoring |
226 | * @return bool |
227 | * @throws Exception |
228 | */ |
229 | public function partialSave(DeliveryMonitoringDataInterface $deliveryMonitoring): bool |
230 | { |
231 | $result = false; |
232 | if ($deliveryMonitoring->validate()) { |
233 | $rowsUpdated = $this->update($deliveryMonitoring); |
234 | if ($rowsUpdated === 0) { |
235 | // doesn't mean an error for sure, cause persistence may return the number of rows actually changed, |
236 | // and not the number of rows matched by the where clause. |
237 | // So just in case try to create without fallback |
238 | try { |
239 | $this->create($deliveryMonitoring); |
240 | } catch (PDOException $e) { |
241 | // when the PDO implementation of RDS is used as a persistence |
242 | } catch (UniqueConstraintViolationException $e) { |
243 | // when the DBAL implementation of RDS is used as a persistence |
244 | } |
245 | } |
246 | |
247 | $result = true; |
248 | } |
249 | |
250 | return $result; |
251 | } |
252 | |
253 | /** |
254 | * @param DeliveryMonitoringDataInterface $deliveryMonitoring |
255 | * @return boolean |
256 | */ |
257 | public function delete(DeliveryMonitoringDataInterface $deliveryMonitoring): bool |
258 | { |
259 | $data = $deliveryMonitoring->get(); |
260 | |
261 | $sql = sprintf('DELETE FROM %s WHERE %s = ?', self::TABLE_NAME, self::COLUMN_DELIVERY_EXECUTION_ID); |
262 | |
263 | return $this->getPersistence()->exec($sql, [$data[self::COLUMN_DELIVERY_EXECUTION_ID]]) === 1; |
264 | } |
265 | |
266 | /** |
267 | * @return common_persistence_SqlPersistence |
268 | */ |
269 | public function getPersistence() |
270 | { |
271 | return $this->getServiceLocator() |
272 | ->get(PersistenceManager::SERVICE_ID) |
273 | ->getPersistenceById($this->getOption(self::OPTION_PERSISTENCE)); |
274 | } |
275 | |
276 | /** |
277 | * @todo extract this method to another service with statistic responsabilities |
278 | * |
279 | * @return mixed |
280 | */ |
281 | public function getCountOfStatistics() |
282 | { |
283 | $groupedQueryBuilder = $this->getQueryBuilder(); |
284 | $groupedQueryBuilder->select('delivery_monitoring.delivery_id'); |
285 | $groupedQueryBuilder->from('delivery_monitoring'); |
286 | $groupedQueryBuilder->groupBy('delivery_monitoring.delivery_id'); |
287 | $groupedSql = $groupedQueryBuilder->getSQL(); |
288 | |
289 | $countQueryBuilder = $this->getQueryBuilder(); |
290 | $countQueryBuilder->select('count(grouped.delivery_id)'); |
291 | $countQueryBuilder->from('(' . $groupedSql . ')', 'grouped'); |
292 | $stmt = $this->getPersistence()->query($countQueryBuilder->getSQL()); |
293 | return $stmt->fetch(PDO::FETCH_COLUMN); |
294 | } |
295 | |
296 | /** |
297 | * @todo extract this method to another service with statistic responsibilities |
298 | * @todo query of 150 lines, erf |
299 | * |
300 | * @param int $limit |
301 | * @param int $offset |
302 | * @param string $orderby |
303 | * @param string $orderdir |
304 | * @return mixed|void |
305 | */ |
306 | public function getStatusesStatistic($limit = 10, $offset = 0, $orderby = 'label', $orderdir = 'asc') |
307 | { |
308 | $statusesList = [ |
309 | $this->getResource(ProctoredDeliveryExecution::STATE_ACTIVE), |
310 | $this->getResource(ProctoredDeliveryExecution::STATE_AUTHORIZED), |
311 | $this->getResource(ProctoredDeliveryExecution::STATE_AWAITING), |
312 | $this->getResource(ProctoredDeliveryExecution::STATE_CANCELED), |
313 | $this->getResource(ProctoredDeliveryExecution::STATE_FINISHED), |
314 | $this->getResource(ProctoredDeliveryExecution::STATE_PAUSED), |
315 | $this->getResource(ProctoredDeliveryExecution::STATE_TERMINATED) |
316 | ]; |
317 | $statusesMap = []; |
318 | foreach ($statusesList as $status) { |
319 | $statusesMap[$status->getLabel()] = $status->getUri(); |
320 | } |
321 | |
322 | $paramsValues = []; |
323 | $limitQueryBuilder = $this->getQueryBuilder(); |
324 | $limitQueryBuilder->select('limit_q.delivery_id'); |
325 | $limitQueryBuilder->groupBy('limit_q.delivery_id'); |
326 | |
327 | if (isset($statusesMap[$orderby])) { |
328 | $innerQueryBuilder = $this->getQueryBuilder(); |
329 | $innerQueryBuilder->select('delivery_monitoring.delivery_id'); |
330 | $innerQueryBuilder->addSelect('COALESCE( order_join.order_status, 0 ) as order_val'); |
331 | $innerQueryBuilder->from('delivery_monitoring'); |
332 | |
333 | $statusBuilder = $this->getQueryBuilder(); |
334 | $statusBuilder->select('status_d_m.delivery_id, count(status_d_m.status) order_status'); |
335 | $statusBuilder->from('delivery_monitoring', 'status_d_m'); |
336 | $statusBuilder->where('status_d_m.status = :status_order'); |
337 | $paramsValues[':status_order'] = $statusesMap[$orderby]; |
338 | $statusBuilder->groupBy('status_d_m.delivery_id'); |
339 | $statusSql = $statusBuilder->getSQL(); |
340 | |
341 | $innerQueryBuilder->leftJoin( |
342 | 'delivery_monitoring', |
343 | '(' . $statusSql . ')', |
344 | 'order_join', |
345 | 'order_join.delivery_id=delivery_monitoring.delivery_id' |
346 | ); |
347 | $innerQueryBuilder->groupBy('delivery_monitoring.delivery_id, order_val'); |
348 | $innerQueryBuilder->orderBy('order_val', $orderdir); |
349 | |
350 | if ($limit) { |
351 | $innerQueryBuilder->setMaxResults($limit); |
352 | } |
353 | $innerQueryBuilder->setFirstResult($offset); |
354 | |
355 | $innerSql = $innerQueryBuilder->getSQL(); |
356 | $limitQueryBuilder->from('(' . $innerSql . ')', 'limit_q'); |
357 | $limitQueryBuilder->addGroupBy('limit_q.order_val'); |
358 | $limitQueryBuilder->orderBy('order_val', $orderdir); |
359 | } elseif ($orderby == 'label') { |
360 | $limitQueryBuilder->from('delivery_monitoring', 'limit_q'); |
361 | $limitQueryBuilder->addSelect('limit_q.delivery_name'); |
362 | $limitQueryBuilder->addGroupBy('limit_q.delivery_name'); |
363 | $limitQueryBuilder->orderBy('limit_q.delivery_name', $orderdir); |
364 | if ($limit) { |
365 | $limitQueryBuilder->setMaxResults($limit); |
366 | } |
367 | $limitQueryBuilder->setFirstResult($offset); |
368 | } else { |
369 | $limitQueryBuilder->from('delivery_monitoring', 'limit_q'); |
370 | $limitQueryBuilder->addSelect('max(limit_q.start_time) as max_start_time'); |
371 | $limitQueryBuilder->orderBy('max_start_time', $orderdir); |
372 | if ($limit) { |
373 | $limitQueryBuilder->setMaxResults($limit); |
374 | } |
375 | $limitQueryBuilder->setFirstResult($offset); |
376 | } |
377 | $limitQueryBuilder->andWhere('limit_q.delivery_id IS NOT NULL'); |
378 | $limitSql = $limitQueryBuilder->getSQL(); |
379 | $stmtLimit = $this->getPersistence()->query($limitSql, $paramsValues); |
380 | $dataLimit = $stmtLimit->fetchAll(PDO::FETCH_COLUMN); |
381 | |
382 | |
383 | $queryBuilder = $this->getQueryBuilder(); |
384 | $conn = $queryBuilder->getConnection(); |
385 | $queryBuilder->select('delivery_m.delivery_id, delivery_m.delivery_name'); |
386 | |
387 | foreach ($statusesMap as $label => $statusUri) { |
388 | $queryBuilder->addSelect( |
389 | 'count(' . $conn->quoteIdentifier('s_' . $label) . '.status) as ' . $conn->quoteIdentifier($label) |
390 | ); |
391 | } |
392 | |
393 | $queryBuilder->addSelect( |
394 | 'max(' . $conn->quoteIdentifier('last_launch') . '.start_time) as ' |
395 | . $conn->quoteIdentifier(__('Last launch')) |
396 | ); |
397 | |
398 | $queryBuilder->from(self::TABLE_NAME, 'delivery_m'); |
399 | |
400 | $paramsValues = []; |
401 | $statusNum = 0; |
402 | foreach ($statusesMap as $label => $statusUri) { |
403 | $queryBuilder->leftJoin( |
404 | 'delivery_m', |
405 | self::TABLE_NAME, |
406 | $conn->quoteIdentifier('s_' . $label), |
407 | 'delivery_m.delivery_execution_id=' . $conn->quoteIdentifier('s_' . $label) |
408 | . '.delivery_execution_id and ' |
409 | . $conn->quoteIdentifier('s_' . $label) . '.status = :status_uri_' . $statusNum |
410 | ); |
411 | $paramsValues[':status_uri_' . $statusNum] = $statusUri; |
412 | $statusNum++; |
413 | } |
414 | $queryBuilder->leftJoin( |
415 | 'delivery_m', |
416 | self::TABLE_NAME, |
417 | $conn->quoteIdentifier('last_launch'), |
418 | 'delivery_m.delivery_execution_id=' . $conn->quoteIdentifier('last_launch') . '.delivery_execution_id' |
419 | ); |
420 | |
421 | if ($dataLimit) { |
422 | $placeHolders = []; |
423 | foreach ($dataLimit as $index => $value) { |
424 | $key = ':in_condition_' . $index; |
425 | $placeHolders[] = $key; |
426 | $paramsValues[$key] = $value; |
427 | } |
428 | $placeHolders = implode(',', $placeHolders); |
429 | $queryBuilder->where("delivery_m.delivery_id IN ($placeHolders)"); |
430 | } |
431 | |
432 | $queryBuilder->groupBy('delivery_m.delivery_id, delivery_m.delivery_name'); |
433 | |
434 | foreach ($statusesMap as $label => $statusUri) { |
435 | $queryBuilder->addGroupBy($conn->quoteIdentifier('s_' . $label) . '.status'); |
436 | } |
437 | |
438 | $outerQueryBuilder = $this->getQueryBuilder(); |
439 | |
440 | $outerQueryBuilder->select('delivery_name as label, delivery_id'); |
441 | |
442 | foreach ($statusesMap as $label => $statusUri) { |
443 | $outerQueryBuilder->addSelect( |
444 | 'sum(' . $conn->quoteIdentifier($label) . ') as ' . $conn->quoteIdentifier($label) |
445 | ); |
446 | } |
447 | $outerQueryBuilder->addSelect( |
448 | 'max(' . $conn->quoteIdentifier(__('Last launch')) . ') as ' |
449 | . $conn->quoteIdentifier(__('Last launch')) |
450 | ); |
451 | $outerQueryBuilder->from('(' . $queryBuilder->getSQL() . ')', 'delivery_statuses'); |
452 | $outerQueryBuilder->groupBy('delivery_id, label'); |
453 | $outerQueryBuilder->orderBy($conn->quoteIdentifier($orderby), $orderdir); |
454 | |
455 | $sql = $outerQueryBuilder->getSQL(); |
456 | |
457 | $stmt = $this->getPersistence()->query($sql, $paramsValues); |
458 | return $stmt->fetchAll(PDO::FETCH_ASSOC); |
459 | } |
460 | |
461 | public function deleteDeliveryExecutionData(DeliveryExecutionDeleteRequest $request): bool |
462 | { |
463 | return $this->delete( |
464 | $this->getData($request->getDeliveryExecution()) |
465 | ); |
466 | } |
467 | |
468 | /** |
469 | * @param $order |
470 | * @return string |
471 | */ |
472 | protected function prepareOrderStmt($order): string |
473 | { |
474 | $order = explode(',', $order); |
475 | $result = []; |
476 | foreach ($order as $orderRule) { |
477 | $orderStmt = $this->buildSingleOrderRule($orderRule); |
478 | if ($orderStmt) { |
479 | $result[] = $orderStmt; |
480 | } |
481 | } |
482 | |
483 | return implode(', ', $result); |
484 | } |
485 | |
486 | private function buildSingleOrderRule(string $orderRule): string |
487 | { |
488 | if ( |
489 | !preg_match( |
490 | '/([a-z_][a-z0-9_]*)\s?(asc|desc)?\s?(string|numeric)?/i', |
491 | $orderRule, |
492 | $ruleParts |
493 | ) |
494 | ) { |
495 | return ''; |
496 | } |
497 | |
498 | $orderBy = $ruleParts[1]; |
499 | $order = $ruleParts[2] ?? 'ASC'; |
500 | $type = $ruleParts[3] ?? null; |
501 | |
502 | if (!in_array($orderBy, $this->getPrimaryColumns(), true)) { |
503 | $colName = $orderBy; |
504 | if (in_array($this->getPlatformName(), ['mysql', 'sqlite'])) { |
505 | $colName = sprintf('JSON_EXTRACT(t.%s, \'$.%s\')', self::COLUMN_EXTRA_DATA, $colName); |
506 | } else { |
507 | $colName = sprintf('t.%s ->> \'%s\'', self::COLUMN_EXTRA_DATA, $colName); |
508 | } |
509 | $sortingColumn = $colName; |
510 | } else { |
511 | $sortingColumn = $orderBy; |
512 | } |
513 | |
514 | return $type === 'numeric' |
515 | ? $this->buildNumericOrderWithCastingToDecimal($sortingColumn, $order) |
516 | : sprintf('%s %s', $sortingColumn, $order); |
517 | } |
518 | |
519 | /** |
520 | * to cover cases when numeric order requested for not numeric fields |
521 | */ |
522 | private function buildNumericOrderWithCastingToDecimal(string $sortingColumn, string $direction): string |
523 | { |
524 | return in_array($this->getPlatformName(), ['mysql', 'sqlite']) |
525 | ? sprintf("cast(%s as DECIMAL) %s", $sortingColumn, $direction) |
526 | : sprintf("cast(NULLIF(regexp_replace(%s, '\D', '', 'g'), '') as decimal) %s", $sortingColumn, $direction); |
527 | } |
528 | |
529 | /** |
530 | * Load data instead of searching |
531 | * |
532 | * @param string $deliveryExecutionId |
533 | * @return array |
534 | */ |
535 | private function loadData(string $deliveryExecutionId): array |
536 | { |
537 | $qb = $this->getQueryBuilder(); |
538 | $qb->select('*') |
539 | ->from(self::TABLE_NAME) |
540 | ->where(self::DELIVERY_EXECUTION_ID . '= :id') |
541 | ->setParameter('id', $deliveryExecutionId); |
542 | |
543 | $data = $qb->execute()->fetch(PDO::FETCH_ASSOC); |
544 | |
545 | if ($data === false) { |
546 | $data = []; |
547 | } else { |
548 | if (isset($data[self::COLUMN_EXTRA_DATA])) { |
549 | $extraData = json_decode($data[self::COLUMN_EXTRA_DATA], true); |
550 | if (json_last_error() === JSON_ERROR_NONE) { |
551 | $data = array_merge($data, $extraData); |
552 | } |
553 | } |
554 | unset($data[self::COLUMN_EXTRA_DATA]); |
555 | } |
556 | |
557 | return $data; |
558 | } |
559 | |
560 | /** |
561 | * Create new record |
562 | * @param DeliveryMonitoringDataInterface $deliveryMonitoring |
563 | * @return boolean whether data is saved |
564 | * @throws Exception |
565 | */ |
566 | private function create(DeliveryMonitoringDataInterface $deliveryMonitoring): bool |
567 | { |
568 | $data = $deliveryMonitoring->get(); |
569 | |
570 | $primaryTableData = $this->extractPrimaryData($data); |
571 | |
572 | $extraData = $this->extractKvData($data); |
573 | |
574 | $types[self::COLUMN_EXTRA_DATA] = (in_array($this->getPlatformName(), ['mysql','sqlite'])) ? 'json' : 'jsonb'; |
575 | $primaryTableData[self::COLUMN_EXTRA_DATA] = json_encode($this->reformatExtraData($extraData)); |
576 | |
577 | return $this->getPersistence()->insert(self::TABLE_NAME, $primaryTableData, $types) === 1; |
578 | } |
579 | |
580 | /** |
581 | * Reformat extra data array in case it was wrongly formatted and it has extra unneeded array level |
582 | */ |
583 | private function reformatExtraData(array $data): array |
584 | { |
585 | $reformatted = []; |
586 | |
587 | foreach ($data as $extraDataKey => $extraDataValue) { |
588 | if (is_array($extraDataValue)) { |
589 | $realKey = current(array_keys($extraDataValue)); |
590 | $realVal = current(array_values($extraDataValue)); |
591 | $reformatted[$realKey] = $realVal; |
592 | } else { |
593 | $reformatted[$extraDataKey] = $extraDataValue; |
594 | } |
595 | } |
596 | |
597 | return $reformatted; |
598 | } |
599 | |
600 | /** |
601 | * Update existing record |
602 | * @param DeliveryMonitoringDataInterface $deliveryMonitoring |
603 | * @return boolean whether data is saved |
604 | */ |
605 | private function update(DeliveryMonitoringDataInterface $deliveryMonitoring) |
606 | { |
607 | $params = [':delivery_execution_id' => $deliveryMonitoring->get()[self::COLUMN_DELIVERY_EXECUTION_ID]]; |
608 | |
609 | $data = $deliveryMonitoring->get(); |
610 | $extraData = $this->extractKvData($data); |
611 | |
612 | $primaryTableData = $this->extractPrimaryData($data); |
613 | |
614 | unset($primaryTableData['delivery_execution_id']); |
615 | $setClauses = []; |
616 | foreach ($primaryTableData as $dataKey => $dataValue) { |
617 | $setClauses[] = sprintf('%s = :%s', $dataKey, $dataKey); |
618 | $params[sprintf(':%s', $dataKey)] = $dataValue; |
619 | } |
620 | |
621 | $setExtraDataClauses = []; |
622 | $platformName = $this->getPlatformName(); |
623 | |
624 | $extraData = $this->reformatExtraData($extraData); |
625 | |
626 | foreach ($extraData as $extraDataKey => $extraDataValue) { |
627 | if (in_array($platformName, ['mysql','sqlite'])) { |
628 | $setExtraDataClauses[] = sprintf('\'$.%s\', :%s', $extraDataKey, $extraDataKey); |
629 | } else { |
630 | $setExtraDataClauses[] = sprintf( |
631 | 'jsonb_build_object(\'%s\', :%s::jsonb)', |
632 | $extraDataKey, |
633 | $extraDataKey |
634 | ); |
635 | $extraDataValue = json_encode($extraDataValue); |
636 | } |
637 | $params[sprintf(':%s', $extraDataKey)] = $extraDataValue; |
638 | } |
639 | |
640 | if (!empty($setExtraDataClauses)) { |
641 | if (in_array($platformName, ['mysql','sqlite'])) { |
642 | $setClauses[] = sprintf( |
643 | '%s = json_set(COALESCE(%s, \'{}\'), %s)', |
644 | self::COLUMN_EXTRA_DATA, |
645 | self::COLUMN_EXTRA_DATA, |
646 | implode(', ', $setExtraDataClauses) |
647 | ); |
648 | } else { |
649 | $setClauses[] = sprintf( |
650 | '%s = CASE WHEN %s IS NULL THEN %s ELSE %s || %s END', |
651 | self::COLUMN_EXTRA_DATA, |
652 | self::COLUMN_EXTRA_DATA, |
653 | implode(' || ', $setExtraDataClauses), |
654 | self::COLUMN_EXTRA_DATA, |
655 | implode(' || ', $setExtraDataClauses) |
656 | ); |
657 | } |
658 | } |
659 | |
660 | $setClause = implode(', ', $setClauses); |
661 | |
662 | $sql = sprintf( |
663 | 'UPDATE %s SET %s WHERE %s = :delivery_execution_id', |
664 | self::TABLE_NAME, |
665 | $setClause, |
666 | self::COLUMN_DELIVERY_EXECUTION_ID |
667 | ); |
668 | |
669 | return $this->getPersistence()->exec($sql, $params); |
670 | } |
671 | |
672 | /** |
673 | * Get list of table column names |
674 | * @return array |
675 | */ |
676 | private function getPrimaryColumns() |
677 | { |
678 | return $this->getOption(self::OPTION_PRIMARY_COLUMNS); |
679 | } |
680 | |
681 | /** |
682 | * @todo cast data to object |
683 | * @param array $data |
684 | * @return array |
685 | */ |
686 | private function extractPrimaryData(array $data) |
687 | { |
688 | $result = []; |
689 | $primaryTableCols = $this->getPrimaryColumns(); |
690 | foreach ($primaryTableCols as $primaryTableCol) { |
691 | if (isset($data[$primaryTableCol])) { |
692 | $result[$primaryTableCol] = $data[$primaryTableCol]; |
693 | } |
694 | } |
695 | return $result; |
696 | } |
697 | |
698 | /** |
699 | * @param array $data |
700 | * @return array |
701 | */ |
702 | private function extractKvData(array $data) |
703 | { |
704 | $result = []; |
705 | $primaryTableCols = $this->getPrimaryColumns(); |
706 | foreach ($data as $key => $val) { |
707 | if (!in_array($key, $primaryTableCols)) { |
708 | $result[$key] = $val; |
709 | } |
710 | } |
711 | |
712 | return $result; |
713 | } |
714 | |
715 | /** |
716 | * @param $condition |
717 | * @param $parameters |
718 | * @param $selectClause |
719 | * @return string |
720 | */ |
721 | private function prepareCondition($condition, &$parameters) |
722 | { |
723 | $whereClause = ''; |
724 | |
725 | //if condition is [ [ key => val ] ] then flatten to [ key => val ] |
726 | if ( |
727 | is_array($condition) |
728 | && count($condition) === 1 |
729 | && is_array(current($condition)) |
730 | && gettype(array_keys($condition)[0]) == 'integer' |
731 | ) { |
732 | $condition = current($condition); |
733 | } |
734 | |
735 | if (is_string($condition) && in_array(mb_strtoupper($condition), ['OR', 'AND'])) { |
736 | $whereClause .= " $condition "; |
737 | } elseif (is_array($condition) && count($condition) > 1) { |
738 | $whereClause .= '('; |
739 | $previousCondition = null; |
740 | foreach ($condition as $subCondition) { |
741 | if (is_array($subCondition) && is_array($previousCondition)) { |
742 | $whereClause .= 'AND'; |
743 | } |
744 | $whereClause .= $this->prepareCondition($subCondition, $parameters); |
745 | $previousCondition = $subCondition; |
746 | } |
747 | $whereClause .= ')'; |
748 | } elseif (is_array($condition) && count($condition) === 1) { |
749 | $primaryColumns = $this->getPrimaryColumns(); |
750 | $key = array_keys($condition)[0]; |
751 | $value = $condition[$key]; |
752 | $toLower = false; |
753 | |
754 | if ($value === null) { |
755 | $op = 'IS NULL'; |
756 | } elseif (is_array($value)) { |
757 | $op = 'IN (' . join(',', array_map(function () { |
758 | return '?'; |
759 | }, $value)) . ')'; |
760 | } elseif ( |
761 | preg_match( |
762 | '/^(?:\s*(<>|<=|>=|<|>|=|LIKE|ILIKE|NOT\sLIKE|NOT\sILIKE))?(.*)$/', |
763 | (string)$value, |
764 | $matches |
765 | ) |
766 | ) { |
767 | if (!empty($matches[1]) && preg_grep('/' . $matches[1] . '/i', ['like','ilike'])) { |
768 | $toLower = true; |
769 | $op = 'LIKE'; |
770 | } elseif (!empty($matches[1]) && preg_grep('/' . $matches[1] . '/i', ['not like','not ilike'])) { |
771 | $toLower = true; |
772 | $op = 'NOT LIKE'; |
773 | } else { |
774 | $op = $matches[1] ? $matches[1] : '='; |
775 | } |
776 | $op .= ' ? '; |
777 | $value = trim($toLower ? strtolower($matches[2]) : $matches[2]); |
778 | } |
779 | |
780 | if (in_array($key, $primaryColumns)) { |
781 | $whereClause .= $toLower ? " LOWER(t.$key) " : " t.$key "; |
782 | $whereClause .= $op; |
783 | } elseif (in_array($this->getPlatformName(), ['mysql','sqlite'])) { |
784 | $whereClause .= sprintf(' JSON_EXTRACT(t.%s, \'$.%s\') %s ', self::COLUMN_EXTRA_DATA, trim($key), $op); |
785 | } else { |
786 | $isLikeSearch = isset($op) && stripos($op, 'like') !== false; |
787 | $value = is_array($value) ? $value : [$value]; |
788 | if ($isLikeSearch) { |
789 | $jsonDataAccessOperator = '->>'; |
790 | } else { |
791 | $jsonDataAccessOperator = '->'; |
792 | $value = array_map('json_encode', $value); |
793 | } |
794 | $whereClause .= sprintf( |
795 | ' t.%s %s \'%s\' %s ', |
796 | self::COLUMN_EXTRA_DATA, |
797 | $jsonDataAccessOperator, |
798 | trim($key), |
799 | $op |
800 | ); |
801 | } |
802 | |
803 | if (is_array($value)) { |
804 | $parameters = array_merge($parameters, $value); |
805 | } elseif ($value !== null) { |
806 | $parameters[] = trim($value); |
807 | } |
808 | } |
809 | return $whereClause; |
810 | } |
811 | |
812 | private function getQueryBuilder(): QueryBuilder |
813 | { |
814 | return $this->getPersistence()->getPlatForm()->getQueryBuilder(); |
815 | } |
816 | |
817 | private function getPlatformName(): string |
818 | { |
819 | return $this->getPersistence()->getPlatForm()->getName(); |
820 | } |
821 | } |