Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
0.00% covered (danger)
0.00%
0 / 461
0.00% covered (danger)
0.00%
0 / 29
CRAP
0.00% covered (danger)
0.00%
0 / 1
MonitoringStorage
0.00% covered (danger)
0.00%
0 / 461
0.00% covered (danger)
0.00%
0 / 29
13110
0.00% covered (danger)
0.00%
0 / 1
 createMonitoringData
0.00% covered (danger)
0.00%
0 / 9
0.00% covered (danger)
0.00%
0 / 1
6
 getData
0.00% covered (danger)
0.00%
0 / 3
0.00% covered (danger)
0.00%
0 / 1
6
 buildData
0.00% covered (danger)
0.00%
0 / 2
0.00% covered (danger)
0.00%
0 / 1
2
 loadData
0.00% covered (danger)
0.00%
0 / 10
0.00% covered (danger)
0.00%
0 / 1
6
 find
0.00% covered (danger)
0.00%
0 / 41
0.00% covered (danger)
0.00%
0 / 1
72
 count
0.00% covered (danger)
0.00%
0 / 15
0.00% covered (danger)
0.00%
0 / 1
6
 save
0.00% covered (danger)
0.00%
0 / 10
0.00% covered (danger)
0.00%
0 / 1
30
 partialSave
0.00% covered (danger)
0.00%
0 / 10
0.00% covered (danger)
0.00%
0 / 1
30
 create
0.00% covered (danger)
0.00%
0 / 4
0.00% covered (danger)
0.00%
0 / 1
2
 update
0.00% covered (danger)
0.00%
0 / 12
0.00% covered (danger)
0.00%
0 / 1
12
 saveKvData
0.00% covered (danger)
0.00%
0 / 53
0.00% covered (danger)
0.00%
0 / 1
132
 delete
0.00% covered (danger)
0.00%
0 / 4
0.00% covered (danger)
0.00%
0 / 1
2
 deleteKvData
0.00% covered (danger)
0.00%
0 / 6
0.00% covered (danger)
0.00%
0 / 1
2
 joinKvData
0.00% covered (danger)
0.00%
0 / 9
0.00% covered (danger)
0.00%
0 / 1
12
 getKvColumns
0.00% covered (danger)
0.00%
0 / 10
0.00% covered (danger)
0.00%
0 / 1
6
 clearKvColumnsCache
0.00% covered (danger)
0.00%
0 / 3
0.00% covered (danger)
0.00%
0 / 1
2
 prepareOrderStmt
0.00% covered (danger)
0.00%
0 / 21
0.00% covered (danger)
0.00%
0 / 1
42
 getPersistence
0.00% covered (danger)
0.00%
0 / 3
0.00% covered (danger)
0.00%
0 / 1
2
 getPrimaryColumns
0.00% covered (danger)
0.00%
0 / 1
0.00% covered (danger)
0.00%
0 / 1
2
 extractPrimaryData
0.00% covered (danger)
0.00%
0 / 6
0.00% covered (danger)
0.00%
0 / 1
12
 extractKvData
0.00% covered (danger)
0.00%
0 / 6
0.00% covered (danger)
0.00%
0 / 1
12
 getKvData
0.00% covered (danger)
0.00%
0 / 11
0.00% covered (danger)
0.00%
0 / 1
12
 prepareCondition
0.00% covered (danger)
0.00%
0 / 53
0.00% covered (danger)
0.00%
0 / 1
812
 isNewRecord
0.00% covered (danger)
0.00%
0 / 8
0.00% covered (danger)
0.00%
0 / 1
2
 getSortByColumn
0.00% covered (danger)
0.00%
0 / 15
0.00% covered (danger)
0.00%
0 / 1
6
 getCountOfStatistics
0.00% covered (danger)
0.00%
0 / 11
0.00% covered (danger)
0.00%
0 / 1
2
 getStatusesStatistic
0.00% covered (danger)
0.00%
0 / 120
0.00% covered (danger)
0.00%
0 / 1
182
 deleteDeliveryExecutionData
0.00% covered (danger)
0.00%
0 / 4
0.00% covered (danger)
0.00%
0 / 1
2
 getQueryBuilder
0.00% covered (danger)
0.00%
0 / 1
0.00% covered (danger)
0.00%
0 / 1
2
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) 2015 (original work) Open Assessment Technologies SA;
19 *
20 *
21 */
22
23namespace oat\taoProctoring\model\monitorCache\implementation;
24
25use Doctrine\DBAL\DBALException;
26use Doctrine\DBAL\Exception\UniqueConstraintViolationException;
27use oat\generis\persistence\PersistenceManager;
28use oat\taoDelivery\model\execution\DeliveryExecutionInterface;
29use oat\taoDelivery\model\execution\ServiceProxy;
30use oat\taoDelivery\model\execution\Delete\DeliveryExecutionDeleteRequest;
31use oat\taoProctoring\helpers\DeliveryHelper;
32use oat\taoProctoring\model\monitorCache\DeliveryMonitoringService;
33use oat\taoProctoring\model\monitorCache\DeliveryMonitoringData as DeliveryMonitoringDataInterface;
34use oat\oatbox\service\ConfigurableService;
35use oat\generis\model\OntologyAwareTrait;
36use oat\taoProctoring\model\execution\DeliveryExecution as ProctoredDeliveryExecution;
37
38/**
39 * Class DeliveryMonitoringService
40 *
41 * Usage example:
42 *
43 * Save trying first to UPDATE, then INSERT if update fails
44 * ----
45 *
46 * ```php
47 * $data = new DeliveryMonitoringData($deliveryExecution, []);
48 * $data->addValue('new_key', 'new_value');
49 * $deliveryMonitoringService->partialSave($data);
50 * ```
51 *
52 * Save new record using INSERT
53 * ----
54 *
55 * ```php
56 * $data = new DeliveryMonitoringData($deliveryExecution, []);
57 * $data->addValue('new_key', 'new_value');
58 * $deliveryMonitoringService->save($data);
59 * ```
60 *
61 * Find
62 * ----
63 *
64 * ```php
65 * $data = $deliveryMonitoringService->find([
66 *   'state' => 'ACTIVE'
67 * ],[
68 *   'limit' => 10,
69 *   'order' = >'id ASC',
70 * ]);
71 * ```
72 *
73 * @package oat\taoProctoring\model
74 * @author Aleh Hutnikau <hutnikau@1pt.com>
75 *
76 * @deprecated
77 */
78class MonitoringStorage extends ConfigurableService implements DeliveryMonitoringService
79{
80    use OntologyAwareTrait;
81
82    public const OPTION_PERSISTENCE = 'persistence';
83
84    public const OPTION_USE_UPDATE_MULTIPLE = 'use_update_multiple';
85
86    public const OPTION_CACHE_SIZE = 'cache_size';
87
88    public const OPTION_PRIMARY_COLUMNS = 'primary_columns';
89
90    public const TABLE_NAME = 'delivery_monitoring';
91
92    public const COLUMN_ID = DeliveryMonitoringService::DELIVERY_EXECUTION_ID;
93    public const COLUMN_DELIVERY_EXECUTION_ID = DeliveryMonitoringService::DELIVERY_EXECUTION_ID;
94    public const COLUMN_STATUS = DeliveryMonitoringService::STATUS;
95    public const COLUMN_CURRENT_ASSESSMENT_ITEM = DeliveryMonitoringService::CURRENT_ASSESSMENT_ITEM;
96    public const COLUMN_TEST_TAKER = DeliveryMonitoringService::TEST_TAKER;
97    public const COLUMN_TEST_TAKER_FIRST_NAME = DeliveryMonitoringService::TEST_TAKER_FIRST_NAME;
98    public const COLUMN_TEST_TAKER_LAST_NAME = DeliveryMonitoringService::TEST_TAKER_LAST_NAME;
99    public const COLUMN_AUTHORIZED_BY = DeliveryMonitoringService::AUTHORIZED_BY;
100    public const COLUMN_START_TIME = DeliveryMonitoringService::START_TIME;
101    public const COLUMN_END_TIME = DeliveryMonitoringService::END_TIME;
102    public const COLUMN_REMAINING_TIME = DeliveryMonitoringService::REMAINING_TIME;
103    public const COLUMN_EXTRA_TIME = DeliveryMonitoringService::EXTRA_TIME;
104    public const COLUMN_CONSUMED_EXTRA_TIME = DeliveryMonitoringService::CONSUMED_EXTRA_TIME;
105
106    public const KV_TABLE_NAME = 'kv_delivery_monitoring';
107    public const KV_COLUMN_ID = 'id';
108    public const KV_COLUMN_PARENT_ID = 'parent_id';
109    public const KV_COLUMN_KEY = 'monitoring_key';
110    public const KV_COLUMN_VALUE = 'monitoring_value';
111    public const KV_FK_PARENT = 'FK_DeliveryMonitoring_kvDeliveryMonitoring';
112
113    public const DEFAULT_SORT_COLUMN = self::COLUMN_ID;
114    public const DEFAULT_SORT_ORDER = 'ASC';
115    public const DEFAULT_SORT_TYPE = 'string';
116
117    protected $joins = [];
118    protected $queryParams = [];
119    protected $selectColumns = [];
120    protected $groupColumns = [];
121
122    /**
123     * @param DeliveryExecutionInterface $deliveryExecution
124     * @param $data
125     * @return DeliveryMonitoringData
126     * @throws \common_exception_NotFound
127     */
128    public function createMonitoringData(DeliveryExecutionInterface $deliveryExecution, $data = [])
129    {
130        $data = array_merge([
131            DeliveryMonitoringService::DELIVERY_EXECUTION_ID => $deliveryExecution->getIdentifier(),
132        ], $data);
133
134        if (!array_key_exists(DeliveryMonitoringService::STATUS, $data)) {
135            $data[DeliveryMonitoringService::STATUS] = $deliveryExecution->getState()->getUri();
136        }
137
138        $monitoringData = new DeliveryMonitoringData($deliveryExecution, $data);
139        $this->propagate($monitoringData);
140
141        return $monitoringData;
142    }
143
144    /**
145     * (non-PHPdoc)
146     * @see \oat\taoProctoring\model\monitorCache\DeliveryMonitoringService::getData()
147     */
148    public function getData(DeliveryExecutionInterface $deliveryExecution)
149    {
150        $data = $this->loadData($deliveryExecution->getIdentifier());
151        $data = $data == false ? [] : $data;
152        return $this->buildData($deliveryExecution, $data);
153    }
154
155    /**
156     * Ensure that all DeliveryMonitoringData are unique
157     * per delivery execution id
158     * @param DeliveryExecutionInterface $deliveryExecution
159     * @param array $data
160     * @return DeliveryMonitoringData
161     * @throws \common_exception_NotFound
162     */
163    protected function buildData(DeliveryExecutionInterface $deliveryExecution, $data)
164    {
165        $dataObject = $this->createMonitoringData($deliveryExecution, $data);
166
167        return $dataObject;
168    }
169
170    /**
171     * Load data instead of searching
172     * Returns false on failure
173     * @param string $deliveryExecutionId
174     * @return array
175     */
176    protected function loadData($deliveryExecutionId)
177    {
178        $qb = $this->getPersistence()->getPlatForm()->getQueryBuilder();
179        $qb->select('*')
180            ->from(self::TABLE_NAME)
181            ->where(self::DELIVERY_EXECUTION_ID . '= :deid')
182            ->setParameter('deid', $deliveryExecutionId);
183        $data = $qb->execute()->fetch(\PDO::FETCH_ASSOC);
184        $kvData = $this->getKvData([$deliveryExecutionId]);
185        if (isset($kvData[$deliveryExecutionId])) {
186            $data =  array_merge($data, $kvData[$deliveryExecutionId]);
187        }
188        return $data;
189    }
190
191    /**
192     * Find delivery monitoring data.
193     *
194     * Examples:
195     * Find by delivery execution id:
196     * ------------------------------
197     * ```php
198     * $deliveryMonitoringService->find([
199     *     ['delivery_execution_id' => 'http://sample/first.rdf#i1450191587554175']
200     * ]);
201     * ```
202     *
203     * Find by two fields with `AND` operator
204     * --------------------------------------
205     * ```php
206     * $deliveryMonitoringService->find([
207     *     ['status' => 'active'],
208     *     ['start_time' => '>1450428401'],
209     * ]);
210     * ```
211     *
212     * Find by two fields with `OR` operator
213     * -------------------------------------
214     * ```php
215     * $deliveryMonitoringService->find([
216     *     ['status' => 'active'],
217     *     'OR',
218     *     ['start_time' => '>1450428401'],
219     * ]);
220     * ```
221     *
222     *
223     * Combined condition
224     * ------------------
225     * ```php
226     * $deliveryMonitoringService->find([
227     *    ['status' => 'finished'],
228     *    'AND',
229     *    [['error_code' => '0'], 'OR', ['error_code' => '1']],
230     * ]);
231     * ```
232     * supports also the following syntax
233     * ```php
234     * $deliveryMonitoringService->find([
235     *    ['status' => 'finished'],
236     *    'AND',
237     *    [['error_code' => ['0', '1']],
238     * ]);
239     * ```
240     *
241     * @param array $criteria - criteria to find data.
242     * The comparison operator is determined based on the first few
243     * characters in the given value. It recognizes the following operators
244     * if they appear as the leading characters in the given value:
245     * <ul>
246     *   <li><code>&lt;</code>: the column must be less than the given value.</li>
247     *   <li><code>&gt;</code>: the column must be greater than the given value.</li>
248     *   <li><code>&lt;=</code>: the column must be less than or equal to the given value.</li>
249     *   <li><code>&gt;=</code>: the column must be greater than or equal to the given value.</li>
250     *   <li><code>&lt;&gt;</code>: the column must not be the same as the given value.</li>
251     *   <li><code>=</code>: the column must be equal to the given value.</li>
252     *   <li>none of the above: the column must be equal to the given value.</li>
253     * </ul>
254     * @param array $options
255     * <ul>
256     *   <li>string `$options['order']='id ASC numeric'`</li>
257     *   <li>integer `$options['limit']=null`</li>
258     *   <li>integer `$options['offset']=0`</li>
259     *   <li>integer `$options['asArray']=false` whether data should be returned as multidimensional or as array of
260     *                                           `DeliveryMonitoringData` instances</li>
261     * </ul>
262     * @param boolean $together - whether the secondary data should be fetched together with primary.
263     * @return DeliveryMonitoringData[]
264     */
265    public function find(array $criteria = [], array $options = [], $together = false)
266    {
267        $result = [];
268        $this->joins = [];
269        $this->queryParams = [];
270        $this->selectColumns = $this->getPrimaryColumns();
271        $this->groupColumns = [];
272        $defaultOptions = [
273            'order' => join(' ', [static::DEFAULT_SORT_COLUMN, static::DEFAULT_SORT_ORDER, static::DEFAULT_SORT_TYPE]),
274            'offset' => 0,
275            'asArray' => false
276        ];
277        $options = array_merge($defaultOptions, $options);
278
279        if ($together === true) {
280            $this->groupColumns = ['t.delivery_execution_id'];
281            $this->joinKvData();
282        }
283
284        $options['order'] = $this->prepareOrderStmt($options['order']);
285        $fromClause = "FROM " . self::TABLE_NAME . " t ";
286
287        $whereClause = $this->prepareCondition($criteria, $this->queryParams, $selectClause);
288        if ($whereClause !== '') {
289            $whereClause = 'WHERE ' . $whereClause;
290        }
291
292        $selectClause = "SELECT " . implode(',' . PHP_EOL, $this->selectColumns) . PHP_EOL;
293
294        $sql = $selectClause . ' ' . $fromClause . PHP_EOL .
295            implode(PHP_EOL, $this->joins) . PHP_EOL .
296            $whereClause . PHP_EOL;
297
298        if (!empty($this->groupColumns)) {
299            if (!in_array('t.delivery_execution_id', $this->groupColumns)) {
300                $this->groupColumns[] = 't.delivery_execution_id';
301            }
302            $sql .= 'GROUP BY ' . implode(',', $this->groupColumns) . PHP_EOL;
303        }
304
305        $sql .= "ORDER BY " . $options['order'];
306
307        if (isset($options['limit'])) {
308            $sql = $this->getPersistence()->getPlatForm()->limitStatement($sql, $options['limit'], $options['offset']);
309        }
310
311        $stmt = $this->getPersistence()->query($sql, $this->queryParams);
312
313        $data = $stmt->fetchAll(\PDO::FETCH_ASSOC);
314
315        if ($options['asArray']) {
316            $result = $data;
317        } else {
318            foreach ($data as $row) {
319                $deliveryExecution = ServiceProxy::singleton()->getDeliveryExecution(
320                    $row[self::COLUMN_DELIVERY_EXECUTION_ID]
321                );
322                $result[] = $this->buildData($deliveryExecution, $row);
323            }
324        }
325
326        return $result;
327    }
328
329    /**
330     * @param array $criteria
331     * @return mixed
332     */
333    public function count(array $criteria = [])
334    {
335        $this->joins = [];
336        $this->queryParams = [];
337
338        $selectClause = "select COUNT(*) FROM (SELECT t.delivery_execution_id ";
339        $fromClause = "FROM " . self::TABLE_NAME . " t ";
340        $whereClause = $this->prepareCondition($criteria, $this->queryParams, $selectClause);
341        if ($whereClause !== '') {
342            $whereClause = 'WHERE ' . $whereClause;
343        }
344        $sql = $selectClause . $fromClause . PHP_EOL .
345            implode(PHP_EOL, $this->joins) . PHP_EOL .
346            $whereClause . PHP_EOL .
347            'GROUP BY t.' . self::DELIVERY_EXECUTION_ID . ') as count_q';
348
349        $stmt = $this->getPersistence()->query($sql, $this->queryParams);
350        $result = $stmt->fetch(\PDO::FETCH_BOTH);
351        return intval($result[0]);
352    }
353
354    /**
355     * @param DeliveryMonitoringDataInterface $deliveryMonitoring
356     * @return bool|mixed
357     * @throws \Exception
358     */
359    public function save(DeliveryMonitoringDataInterface $deliveryMonitoring)
360    {
361        $result = false;
362        if ($deliveryMonitoring->validate()) {
363            try {
364                // we should be ready for unique violation error when the calling side calls
365                // save() instead of partialSave()
366                $result = $this->create($deliveryMonitoring);
367            } catch (\PDOException $e) {
368                // when the PDO implementation of RDS is used as a persistence
369                // unfortunately the exception is very broad so it can cover more than intended cases
370            } catch (UniqueConstraintViolationException $e) {
371                // when the DBAL implementation of RDS is used as a persistence
372            }
373            if (!$result) {
374                $this->update($deliveryMonitoring);
375            }
376            $this->saveKvData($deliveryMonitoring);
377            $result = true;
378        }
379        return $result;
380    }
381
382    /**
383     * @param DeliveryMonitoringDataInterface $deliveryMonitoring
384     * @return bool|mixed
385     * @throws \Exception
386     */
387    public function partialSave(DeliveryMonitoringDataInterface $deliveryMonitoring)
388    {
389        $result = false;
390        if ($deliveryMonitoring->validate()) {
391            $rowsUpdated = $this->update($deliveryMonitoring);
392            if ($rowsUpdated === 0) {
393                // doesn't mean an error for sure, cause persistence may return the number of rows actually changed,
394                // and not the number of rows matched by the where clause.
395                // So just in case try to create without fallback
396                try {
397                    $this->create($deliveryMonitoring);
398                } catch (\PDOException $e) {
399                    // when the PDO implementation of RDS is used as a persistence
400                } catch (UniqueConstraintViolationException $e) {
401                    // when the DBAL implementation of RDS is used as a persistence
402                }
403            }
404            $this->saveKvData($deliveryMonitoring);
405            $result = true;
406        }
407
408        return $result;
409    }
410
411    /**
412     * Create new record
413     * @param DeliveryMonitoringDataInterface $deliveryMonitoring
414     * @return boolean whether data is saved
415     * @throws \Exception
416     */
417    protected function create(DeliveryMonitoringDataInterface $deliveryMonitoring)
418    {
419        $data = $deliveryMonitoring->get();
420
421        $primaryTableData = $this->extractPrimaryData($data);
422
423        $result = $this->getPersistence()->insert(self::TABLE_NAME, $primaryTableData) === 1;
424
425        return $result;
426    }
427
428    /**
429     * Update existing record
430     * @param DeliveryMonitoringDataInterface $deliveryMonitoring
431     * @return boolean whether data is saved
432     */
433    protected function update(DeliveryMonitoringDataInterface $deliveryMonitoring)
434    {
435        $setClause = '';
436        $params = [':delivery_execution_id' => $deliveryMonitoring->get()[self::COLUMN_DELIVERY_EXECUTION_ID]];
437
438        $data = $deliveryMonitoring->get();
439        $primaryTableData = $this->extractPrimaryData($data);
440
441        unset($primaryTableData['delivery_execution_id']);
442        foreach ($primaryTableData as $dataKey => $dataValue) {
443            $setClause .= ($setClause === '') ? "$dataKey = :$dataKey" : "$dataKey = :$dataKey";
444            $params[":$dataKey"] = $dataValue;
445        }
446
447        $sql = "UPDATE " . self::TABLE_NAME . " SET $setClause
448        WHERE " . self::COLUMN_DELIVERY_EXECUTION_ID . '=:delivery_execution_id';
449
450        $rowsUpdated = $this->getPersistence()->exec($sql, $params);
451
452        return $rowsUpdated;
453    }
454
455    /**
456     * Delete all related records from secondary table
457     * @param DeliveryMonitoringDataInterface $deliveryMonitoring
458     * @throws \Exception
459     */
460    protected function saveKvData(DeliveryMonitoringDataInterface $deliveryMonitoring)
461    {
462        $data = $deliveryMonitoring->get();
463
464        $id = $data[self::COLUMN_DELIVERY_EXECUTION_ID];
465        $kvTableData = $this->extractKvData($data);
466
467        if (empty($kvTableData)) {
468            return;
469        }
470
471        $query = 'SELECT ' . self::KV_COLUMN_KEY . ',' . self::KV_COLUMN_VALUE . '
472            FROM ' . self::KV_TABLE_NAME . '
473            WHERE ' . self::KV_COLUMN_PARENT_ID . ' =? AND ' . self::KV_COLUMN_KEY . ' IN(';
474        $keys = array_fill(0, count($kvTableData), '?');
475        $query .= implode(',', $keys);
476        $query .= ')';
477
478        $params = array_merge([$id], array_keys($kvTableData));
479
480        $stmt = $this->getPersistence()->query($query, $params);
481        $existent = $stmt->fetchAll(\PDO::FETCH_ASSOC);
482        $existent = array_combine(
483            array_column($existent, self::KV_COLUMN_KEY),
484            array_column($existent, self::KV_COLUMN_VALUE)
485        );
486        $dataToBeInserted = [];
487        $dataToBeUpdated = [];
488        $kvColumns = $this->getKvColumns();
489        foreach ($kvTableData as $kvDataKey => $kvDataValue) {
490            if (!in_array($kvDataKey, $kvColumns, true)) {
491                $this->clearKvColumnsCache();
492            }
493            if (isset($existent[$kvDataKey]) && $existent[$kvDataKey] == $kvDataValue) {
494                continue;
495            }
496
497            if (array_key_exists($kvDataKey, $existent)) {
498                if ($this->getOption(static::OPTION_USE_UPDATE_MULTIPLE) === true) {
499                    $dataToBeUpdated[] = [
500                        'conditions' => [
501                            self::KV_COLUMN_PARENT_ID => $id,
502                            self::KV_COLUMN_KEY => $kvDataKey,
503                        ],
504                        'updateValues' => [
505                            self::KV_COLUMN_VALUE => $kvDataValue
506                        ]
507                    ];
508                } else {
509                    $this->getPersistence()->exec(
510                        'UPDATE ' . self::KV_TABLE_NAME . '
511                              SET ' . self::KV_COLUMN_VALUE . ' = ?
512                            WHERE ' . self::KV_COLUMN_PARENT_ID . ' = ?
513                              AND ' . self::KV_COLUMN_KEY . ' = ?;',
514                        [$kvDataValue, $id, $kvDataKey]
515                    );
516                }
517            } else {
518                $dataToBeInserted[] = [
519                    self::KV_COLUMN_PARENT_ID => $id,
520                    self::KV_COLUMN_KEY => $kvDataKey,
521                    self::KV_COLUMN_VALUE => $kvDataValue,
522                ];
523            }
524        }
525
526
527        if ($this->getOption(static::OPTION_USE_UPDATE_MULTIPLE) === true && !empty($dataToBeUpdated)) {
528            $this->getPersistence()->updateMultiple(self::KV_TABLE_NAME, $dataToBeUpdated);
529        }
530
531        if (!empty($dataToBeInserted)) {
532            $this->getPersistence()->insertMultiple(self::KV_TABLE_NAME, $dataToBeInserted);
533        }
534    }
535
536    /**
537     * @param DeliveryMonitoringDataInterface $deliveryMonitoring
538     * @return boolean
539     */
540    public function delete(DeliveryMonitoringDataInterface $deliveryMonitoring)
541    {
542        $data = $deliveryMonitoring->get();
543
544        $sql = 'DELETE FROM ' . self::TABLE_NAME . '
545                WHERE ' . self::COLUMN_DELIVERY_EXECUTION_ID . '=?';
546
547        return $this->getPersistence()->exec($sql, [$data[self::COLUMN_DELIVERY_EXECUTION_ID]]) === 1;
548    }
549
550
551    /**
552     * Delete all related records from secondary table
553     * @param DeliveryMonitoringDataInterface $deliveryMonitoring
554     * @return boolean
555     */
556    protected function deleteKvData(DeliveryMonitoringDataInterface $deliveryMonitoring)
557    {
558        $data = $deliveryMonitoring->get();
559
560        $sql = 'DELETE FROM ' . self::KV_TABLE_NAME . '
561                WHERE ' . self::KV_COLUMN_PARENT_ID . '=?';
562        $this->getPersistence()->exec($sql, [$data[self::COLUMN_DELIVERY_EXECUTION_ID]]);
563        $result = true;
564
565        return $result;
566    }
567
568    /**
569     * Join data from kv storage.
570     */
571    protected function joinKvData()
572    {
573        $kvColumns = $this->getKvColumns();
574        $this->joins[] = 'LEFT JOIN kv_delivery_monitoring as kv_case
575                   on kv_case.parent_id = t.delivery_execution_id';
576        foreach ($kvColumns as $kvColNum => $kvColName) {
577            if ($this->getPersistence()->getPlatForm()->getName() == 'postgresql') {
578                $this->selectColumns[] = 'string_agg(case when kv_case.monitoring_key = \'' . $kvColName
579                    . '\' then kv_case.monitoring_value else \'\' end,\'\') as "' . $kvColName . '"';
580            } else {
581                $this->selectColumns[] = 'GROUP_CONCAT(case when kv_case.monitoring_key = \'' . $kvColName
582                    . '\' then kv_case.monitoring_value else \'\' end SEPARATOR \'\' ) as `' . $kvColName . '`';
583            }
584        }
585    }
586
587    /**
588     * @return array
589     * @throws \common_Exception
590     */
591    protected function getKvColumns()
592    {
593        /** @var \common_persistence_KeyValuePersistence $cache */
594        $cache = $this->getServiceLocator()->get(PersistenceManager::SERVICE_ID)->getPersistenceById('cache');
595        $key = self::class . '_KvColumns';
596        if (!$cache->exists($key)) {
597            $kvColumns = $this
598                ->getPersistence()
599                ->query('SELECT DISTINCT monitoring_key FROM kv_delivery_monitoring')
600                ->fetchAll(\PDO::FETCH_COLUMN);
601            //remove columns which presented in primary columns list
602            $cache->set($key, json_encode($kvColumns));
603        } else {
604            $kvColumns = json_decode($cache->get($key), true);
605        }
606        return array_diff($kvColumns, $this->getPrimaryColumns());
607    }
608
609    /**
610     * Crear kv columns cache
611     */
612    protected function clearKvColumnsCache()
613    {
614        /** @var \common_persistence_KeyValuePersistence $cache */
615        $cache = $this->getServiceLocator()->get(PersistenceManager::SERVICE_ID)->getPersistenceById('cache');
616        $key = self::class . '_KvColumns';
617        $cache->del($key);
618    }
619
620    /**
621     * @param $order
622     * @return array
623     */
624    protected function prepareOrderStmt($order)
625    {
626        $order = explode(',', $order);
627        $result = [];
628        $primaryTableColumns = $this->getPrimaryColumns();
629        foreach ($order as $ruleNum => $orderRule) {
630            preg_match('/([a-zA-Z_][a-zA-Z0-9_]*)\s?(asc|desc)?\s?(string|numeric)?/i', $orderRule, $ruleParts);
631
632            if (!in_array($ruleParts[1], $primaryTableColumns)) {
633                $colName = $ruleParts[1];
634                $joinNum = count($this->joins);
635                $this->joins[] = "LEFT JOIN " . self::KV_TABLE_NAME . " kv_t_$joinNum 
636                    ON kv_t_$joinNum." . self::KV_COLUMN_PARENT_ID . " = t." . self::COLUMN_DELIVERY_EXECUTION_ID . "
637                    AND kv_t_$joinNum.monitoring_key = ?";
638                $this->queryParams[] = $colName;
639                $this->selectColumns[] = "kv_t_$joinNum.monitoring_value as $colName";
640                $this->groupColumns[] = "kv_t_$joinNum.monitoring_value";
641
642                $sortingColumn = "kv_t_$joinNum.monitoring_value";
643            } else {
644                $sortingColumn = $ruleParts[1];
645            }
646
647            $result[] = isset($ruleParts[3]) && $ruleParts[3] === 'numeric'
648                ? sprintf("cast(nullif(%s, '') as decimal) %s", $sortingColumn, $ruleParts[2])
649                : sprintf('%s %s', $sortingColumn, isset($ruleParts[2]) ? $ruleParts[2] : 'ASC');
650        }
651
652        $result = implode(', ', $result);
653
654        return $result;
655    }
656
657    /**
658     * @return \common_persistence_SqlPersistence
659     */
660    public function getPersistence()
661    {
662        return $this->getServiceLocator()
663            ->get(PersistenceManager::SERVICE_ID)
664            ->getPersistenceById($this->getOption(self::OPTION_PERSISTENCE));
665    }
666
667    /**
668     * Get list of table column names
669     * @return array
670     */
671    protected function getPrimaryColumns()
672    {
673        return $this->getOption(self::OPTION_PRIMARY_COLUMNS);
674    }
675
676    /**
677     * @param array $data
678     * @return array
679     */
680    protected function extractPrimaryData(array $data)
681    {
682        $result = [];
683        $primaryTableCols = $this->getPrimaryColumns();
684        foreach ($primaryTableCols as $primaryTableCol) {
685            if (isset($data[$primaryTableCol])) {
686                $result[$primaryTableCol] = $data[$primaryTableCol];
687            }
688        }
689        return $result;
690    }
691
692    /**
693     * @param array $data
694     * @return array
695     */
696    protected function extractKvData(array $data)
697    {
698        $result = [];
699        $primaryTableCols = $this->getPrimaryColumns();
700        foreach ($data as $key => $val) {
701            if (!in_array($key, $primaryTableCols)) {
702                $result[$key] = $val;
703            }
704        }
705        return $result;
706    }
707
708    /**
709     * Get secondary data by parent data id
710     * @param array $ids
711     * @return array
712     */
713    protected function getKvData(array $ids)
714    {
715        if (empty($ids)) {
716            return [];
717        }
718        $result = [];
719        $sql = 'SELECT * FROM ' . self::KV_TABLE_NAME . '
720                WHERE ' . self::KV_COLUMN_PARENT_ID . ' IN(' . join(',', array_map(function () {
721            return '?';
722        }, $ids)) . ')';
723        $secondaryData = $this->getPersistence()->query($sql, $ids)->fetchAll(\PDO::FETCH_ASSOC);
724
725        foreach ($secondaryData as $data) {
726            $result[$data[self::KV_COLUMN_PARENT_ID]][$data[self::KV_COLUMN_KEY]] = $data[self::KV_COLUMN_VALUE];
727        }
728
729        return $result;
730    }
731
732    /**
733     * @param $parameters
734     * @param $selectClause
735     * @return string
736     */
737    /**
738     * @param $condition
739     * @param $parameters
740     * @param $selectClause
741     * @return string
742     */
743    protected function prepareCondition($condition, &$parameters, &$selectClause)
744    {
745        $whereClause = '';
746
747        //if condition is [ [ key => val ] ] then flatten to [ key => val ]
748        if (
749            is_array($condition)
750            && count($condition) === 1
751            && is_array(current($condition))
752            && gettype(array_keys($condition)[0]) == 'integer'
753        ) {
754            $condition = current($condition);
755        }
756
757        if (is_string($condition) && in_array(mb_strtoupper($condition), ['OR', 'AND'])) {
758            $whereClause .= " $condition ";
759        } elseif (is_array($condition) && count($condition) > 1) {
760            $whereClause .=  '(';
761            $previousCondition = null;
762            foreach ($condition as $subCondition) {
763                if (is_array($subCondition) && is_array($previousCondition)) {
764                    $whereClause .= 'AND';
765                }
766                $whereClause .=  $this->prepareCondition($subCondition, $parameters, $selectClause);
767                $previousCondition = $subCondition;
768            }
769            $whereClause .=  ')';
770        } elseif (is_array($condition) && count($condition) === 1) {
771            $primaryColumns = $this->getPrimaryColumns();
772            $key = array_keys($condition)[0];
773            $value = $condition[$key];
774            $toLower = false;
775
776            if ($value === null) {
777                $op = 'IS NULL';
778            } elseif (is_array($value)) {
779                $op = 'IN (' . join(',', array_map(function () {
780                    return '?';
781                }, $value)) . ')';
782            } elseif (
783                preg_match('/^(?:\s*(<>|<=|>=|<|>|=|LIKE|ILIKE|NOT\sLIKE|NOT\sILIKE))?(.*)$/', $value, $matches)
784            ) {
785                if (!empty($matches[1]) && preg_grep('/' . $matches[1] . '/i', ['like','ilike'])) {
786                    $toLower = true;
787                    $op = 'LIKE';
788                } elseif (!empty($matches[1]) && preg_grep('/' . $matches[1] . '/i', ['not like','not ilike'])) {
789                    $toLower = true;
790                    $op = 'NOT LIKE';
791                } else {
792                    $op = $matches[1] ? $matches[1] : '=';
793                }
794                $op .= ' ? ';
795                $value = $toLower ? strtolower($matches[2]) : $matches[2];
796            }
797
798            if (in_array($key, $primaryColumns)) {
799                $whereClause .= $toLower ? " LOWER(t.$key" : " t.$key ";
800                $whereClause .= $op;
801            } else {
802                $joinNum = count($this->joins);
803                $whereClause .= " (kv_t_$joinNum.monitoring_key = ? AND ";
804                $whereClause .= $toLower ? "LOWER(kv_t_$joinNum.monitoring_value)" : "kv_t_$joinNum.monitoring_value";
805                $whereClause .= " $op";
806
807                $this->joins[] = "LEFT JOIN " . self::KV_TABLE_NAME . " kv_t_$joinNum ON kv_t_$joinNum."
808                    . self::KV_COLUMN_PARENT_ID . " = t." . self::COLUMN_DELIVERY_EXECUTION_ID;
809                $parameters[] = trim($key);
810            }
811
812            if (is_array($value)) {
813                $parameters = array_merge($parameters, $value);
814            } elseif ($value !== null) {
815                $parameters[] = trim($value);
816            }
817        }
818        return $whereClause;
819    }
820
821    /**
822     * @param DeliveryMonitoringDataInterface $deliveryMonitoring
823     * @return boolean
824     */
825    /**
826     * Check if record for delivery execution already exists in the storage.
827     * @todo add isNewRecord property to DeliveryMonitoringDataInterface to prevent repeated queries to DB.
828     * @param DeliveryMonitoringDataInterface $deliveryMonitoring
829     * @return boolean
830     */
831    protected function isNewRecord(DeliveryMonitoringDataInterface $deliveryMonitoring)
832    {
833        $data = $deliveryMonitoring->get();
834        $deliveryExecutionId = $data[self::COLUMN_DELIVERY_EXECUTION_ID];
835
836        $sql = "SELECT EXISTS( " . PHP_EOL .
837            "SELECT " . self::COLUMN_DELIVERY_EXECUTION_ID . PHP_EOL .
838            "FROM " . self::TABLE_NAME . PHP_EOL .
839            "WHERE " . self::COLUMN_DELIVERY_EXECUTION_ID . "=?)";
840        $exists = $this->getPersistence()->query($sql, [$deliveryExecutionId])->fetch(\PDO::FETCH_COLUMN);
841
842        return !((bool) $exists);
843    }
844
845    /**
846     * @param string $sortBy
847     * @return string
848     */
849    public static function getSortByColumn($sortBy)
850    {
851        $map = array_merge(
852            [
853            'firstname' => self::COLUMN_TEST_TAKER_FIRST_NAME,
854            'lastname' => self::TEST_TAKER_LAST_NAME,
855            'delivery' => self::DELIVERY_NAME,
856            'status' => self::STATUS,
857            'connectivity' => self::CONNECTIVITY,
858            ],
859            array_combine(array_map(function ($property) {
860                return strtolower($property['id']);
861            }, DeliveryHelper::getExtraFields()), array_map(function ($property) {
862                return $property['id'];
863            }, DeliveryHelper::getExtraFields()))
864        );
865
866        return array_key_exists(strtolower($sortBy), $map) ? $map[strtolower($sortBy)] : self::DEFAULT_SORT_COLUMN;
867    }
868
869    /**
870     * @return mixed
871     */
872    public function getCountOfStatistics()
873    {
874        $groupedQueryBuilder = $this->getQueryBuilder();
875        $groupedQueryBuilder->select('delivery_monitoring.delivery_id');
876        $groupedQueryBuilder->from('delivery_monitoring');
877        $groupedQueryBuilder->groupBy('delivery_monitoring.delivery_id');
878        $groupedSql = $groupedQueryBuilder->getSQL();
879
880        $countQueryBuilder = $this->getQueryBuilder();
881        $countQueryBuilder->select('count(grouped.delivery_id)');
882        $countQueryBuilder->from('(' . $groupedSql . ')', 'grouped');
883        $stmt = $this->getPersistence()->query($countQueryBuilder->getSQL());
884        $count = $stmt->fetch(\PDO::FETCH_COLUMN);
885        return $count;
886    }
887
888    /**
889     * @param int $limit
890     * @param int $offset
891     * @param string $orderby
892     * @param string $orderdir
893     * @return mixed|void
894     */
895    public function getStatusesStatistic($limit = 10, $offset = 0, $orderby = 'label', $orderdir = 'asc')
896    {
897        $statusesList = [
898            $this->getResource(ProctoredDeliveryExecution::STATE_ACTIVE),
899            $this->getResource(ProctoredDeliveryExecution::STATE_AUTHORIZED),
900            $this->getResource(ProctoredDeliveryExecution::STATE_AWAITING),
901            $this->getResource(ProctoredDeliveryExecution::STATE_CANCELED),
902            $this->getResource(ProctoredDeliveryExecution::STATE_FINISHED),
903            $this->getResource(ProctoredDeliveryExecution::STATE_PAUSED),
904            $this->getResource(ProctoredDeliveryExecution::STATE_TERMINATED)
905        ];
906        $statusesMap = [];
907        foreach ($statusesList as $status) {
908            $statusesMap[$status->getLabel()] = $status->getUri();
909        }
910
911        $paramsValues = [];
912        $limitQueryBuilder = $this->getQueryBuilder();
913        $limitQueryBuilder->select('limit_q.delivery_id');
914        $limitQueryBuilder->groupBy('limit_q.delivery_id');
915
916        if (isset($statusesMap[$orderby])) {
917            $innerQueryBuilder = $this->getQueryBuilder();
918            $innerQueryBuilder->select('delivery_monitoring.delivery_id');
919            $innerQueryBuilder->addSelect('COALESCE( order_join.order_status, 0 ) as order_val');
920            $innerQueryBuilder->from('delivery_monitoring');
921
922            $statusBuilder = $this->getQueryBuilder();
923            $statusBuilder->select('status_d_m.delivery_id, count(status_d_m.status) order_status');
924            $statusBuilder->from('delivery_monitoring', 'status_d_m');
925            $statusBuilder->where('status_d_m.status = :status_order');
926            $paramsValues[':status_order'] =  $statusesMap[$orderby];
927            $statusBuilder->groupBy('status_d_m.delivery_id');
928            $statusSql = $statusBuilder->getSQL();
929
930            $innerQueryBuilder->leftJoin(
931                'delivery_monitoring',
932                '(' . $statusSql . ')',
933                'order_join',
934                'order_join.delivery_id=delivery_monitoring.delivery_id'
935            );
936            $innerQueryBuilder->groupBy('delivery_monitoring.delivery_id, order_val');
937            $innerQueryBuilder->orderBy('order_val', $orderdir);
938
939            if ($limit) {
940                $innerQueryBuilder->setMaxResults($limit);
941            }
942            $innerQueryBuilder->setFirstResult($offset);
943
944            $innerSql = $innerQueryBuilder->getSQL();
945            $limitQueryBuilder->from('(' . $innerSql . ')', 'limit_q');
946            $limitQueryBuilder->addGroupBy('limit_q.order_val');
947            $limitQueryBuilder->orderBy('order_val', $orderdir);
948        } elseif ($orderby == 'label') {
949            $limitQueryBuilder->from('delivery_monitoring', 'limit_q');
950            $limitQueryBuilder->addSelect('limit_q.delivery_name');
951            $limitQueryBuilder->addGroupBy('limit_q.delivery_name');
952            $limitQueryBuilder->orderBy('limit_q.delivery_name', $orderdir);
953            if ($limit) {
954                $limitQueryBuilder->setMaxResults($limit);
955            }
956            $limitQueryBuilder->setFirstResult($offset);
957        } else {
958            $limitQueryBuilder->from('delivery_monitoring', 'limit_q');
959            $limitQueryBuilder->addSelect('max(limit_q.start_time) as max_start_time');
960            $limitQueryBuilder->orderBy('max_start_time', $orderdir);
961            if ($limit) {
962                $limitQueryBuilder->setMaxResults($limit);
963            }
964            $limitQueryBuilder->setFirstResult($offset);
965        }
966        $limitQueryBuilder->andWhere('limit_q.delivery_id IS NOT NULL');
967        $limitSql = $limitQueryBuilder->getSQL();
968        $stmtLimit = $this->getPersistence()->query($limitSql, $paramsValues);
969        $dataLimit = $stmtLimit->fetchAll(\PDO::FETCH_COLUMN);
970
971
972        $queryBuilder = $this->getQueryBuilder();
973        $conn = $queryBuilder->getConnection();
974        $queryBuilder->select('delivery_m.delivery_id, delivery_m.delivery_name');
975
976        foreach ($statusesMap as $label => $statusUri) {
977            $queryBuilder->addSelect(
978                'count(' . $conn->quoteIdentifier('s_' . $label) . '.status) as ' . $conn->quoteIdentifier($label)
979            );
980        }
981
982        $queryBuilder->addSelect(
983            'max(' . $conn->quoteIdentifier('last_launch') . '.start_time) as '
984                . $conn->quoteIdentifier(__('Last launch'))
985        );
986
987        $queryBuilder->from(self::TABLE_NAME, 'delivery_m');
988
989        $paramsValues = [];
990        $statusNum = 0;
991        foreach ($statusesMap as $label => $statusUri) {
992            $queryBuilder->leftJoin(
993                'delivery_m',
994                self::TABLE_NAME,
995                $conn->quoteIdentifier('s_' . $label),
996                'delivery_m.delivery_execution_id=' . $conn->quoteIdentifier('s_' . $label)
997                    . '.delivery_execution_id and ' . $conn->quoteIdentifier('s_' . $label)
998                    . '.status = :status_uri_' . $statusNum
999            );
1000            $paramsValues[':status_uri_' . $statusNum] = $statusUri;
1001            $statusNum++;
1002        }
1003        $queryBuilder->leftJoin(
1004            'delivery_m',
1005            self::TABLE_NAME,
1006            $conn->quoteIdentifier('last_launch'),
1007            'delivery_m.delivery_execution_id=' . $conn->quoteIdentifier('last_launch') . '.delivery_execution_id'
1008        );
1009
1010        if ($dataLimit) {
1011            $placeHolders = [];
1012            foreach ($dataLimit as $index => $value) {
1013                $key = ':in_condition_' . $index;
1014                $placeHolders[] = $key;
1015                $paramsValues[$key] = $value;
1016            }
1017            $placeHolders = implode(',', $placeHolders);
1018            $queryBuilder->where("delivery_m.delivery_id IN ($placeHolders)");
1019        }
1020
1021        $queryBuilder->groupBy('delivery_m.delivery_id, delivery_m.delivery_name');
1022
1023        foreach ($statusesMap as $label => $statusUri) {
1024            $queryBuilder->addGroupBy($conn->quoteIdentifier('s_' . $label) . '.status');
1025        }
1026
1027        $outerQueryBuilder = $this->getQueryBuilder();
1028
1029        $outerQueryBuilder->select('delivery_name as label, delivery_id');
1030
1031        foreach ($statusesMap as $label => $statusUri) {
1032            $outerQueryBuilder->addSelect(
1033                'sum(' . $conn->quoteIdentifier($label) . ') as ' . $conn->quoteIdentifier($label)
1034            );
1035        }
1036        $outerQueryBuilder->addSelect(
1037            'max(' . $conn->quoteIdentifier(__('Last launch')) . ') as '
1038            .  $conn->quoteIdentifier(__('Last launch'))
1039        );
1040        $outerQueryBuilder->from('(' . $queryBuilder->getSQL() . ')', 'delivery_statuses');
1041        $outerQueryBuilder->groupBy('delivery_id, label');
1042        $outerQueryBuilder->orderBy($conn->quoteIdentifier($orderby), $orderdir);
1043
1044        $sql = $outerQueryBuilder->getSQL();
1045
1046        $stmt = $this->getPersistence()->query($sql, $paramsValues);
1047        $data = $stmt->fetchAll(\PDO::FETCH_ASSOC);
1048
1049        return $data;
1050    }
1051
1052    /**
1053     * @inheritdoc
1054     */
1055    public function deleteDeliveryExecutionData(DeliveryExecutionDeleteRequest $request)
1056    {
1057        $data = $this->getData($request->getDeliveryExecution());
1058        $return = $this->delete($data);
1059        $this->deleteKvData($data);
1060
1061        return $return;
1062    }
1063
1064    /**
1065     * @return \Doctrine\DBAL\Query\QueryBuilder
1066     */
1067    private function getQueryBuilder()
1068    {
1069        return $this->getPersistence()->getPlatForm()->getQueryBuilder();
1070    }
1071}