Statistics.php 41.6 KB
Newer Older
Jerome Boucher's avatar
Jerome Boucher committed
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
<?php

/*
 * Copyright (C) 2020 Maarch
 *
 * This file is part of bundle recordsManagement.
 *
 * Bundle recordsManagement is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * Bundle recordsManagement is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with bundle recordsManagement.  If not, see <http://www.gnu.org/licenses/>.
 */

namespace bundle\Statistics\Controller;

/**
 * Managemet of the access rule of an archive
 *
 * @author Jérôme Boucher <jerome.boucher@maarch.org>
 */
class Statistics
{
31
32
33
    public $sdoFactory;
    protected $minDate;
    protected $maxDate;
34
    protected $sizeFilter;
35
    protected $sizeCategories;
36
    protected $translator;
37
38
39
40
41
42

    /**
     * Constructor of access control class
     *
     * @param \dependency\sdo\Factory $sdoFactory The factory
     */
43
    public function __construct(\dependency\sdo\Factory $sdoFactory, \dependency\localisation\TranslatorInterface $translator)
44
45
46
    {
        $this->sdoFactory = $sdoFactory;
        $this->pdo = $sdoFactory->das->pdo;
47
        $this->sizeCategories = ["Octets", "Ko", "Mo", "Go"];
48
49
50
51
52
53
54
        $this->translator = $translator;
        $this->translator->setCatalog("Statistics/Statistics");
    }

    /**
     * Retrieve default stats for screen
     *
55
     * @return array Associative array of properties
56
57
58
59
     */
    public function index()
    {
        return $this->retrieve(null, null, null, null, 3);
60
61
62
    }

    /**
63
     * Retrieve filtered stats
64
     *
65
66
67
68
     * @param  string $operation  Type of operation to count or sum
     * @param  string $startDate  Start date
     * @param  string $endDate    End date
     * @param  string $filter     Filtering parameter to group query by
69
     * @param  float  $sizeFilter Power of 10 to divide by
70
     *
71
     * @return array              Associative array of properties
72
     */
73
    public function retrieve($operation = null, $startDate = null, $endDate = null, $filter = null, $sizeFilter = 0)
74
75
76
77
78
79
    {
        if (!empty($startDate)) {
            $startDate = \laabs::newDateTime($startDate);
        } else {
            $startDate = null;
        }
80
        if (!empty($endDate)) {
81
82
83
84
85
86
            $endDate = \laabs::newDateTime($endDate);
        } else {
            $endDate = null;
        }

        if (!is_null($startDate) && is_null($endDate)) {
87
            throw new \core\Exception\BadRequestException($this->translator->getText("End Date is mandatory if start date is filled"));
88
        } elseif (is_null($startDate) && !is_null($endDate)) {
89
            throw new \core\Exception\BadRequestException($this->translator->getText("Start Date is mandatory if end date is filled"));
90
91
92
93
94
        }

        if ($startDate == $endDate && !is_null($startDate)) {
            $endDate->setTime(23, 59, 59);
        } elseif ($startDate > $endDate) {
95
            throw new \core\Exception\BadRequestException($this->translator->getText("Start Date cannot be past end date"));
96
97
        }

98
99
100
101
102
        if (!empty($startDate)) {
            $startDate = (string) $startDate->format('Y-m-d 00:00:00');
            $endDate = (string) $endDate->format('Y-m-d 23:59:59');
        }

103
104
105
106
        if ($sizeFilter > 3 || $sizeFilter < 0) {
            throw new \core\Exception\BadRequestException($this->translator->getText("The sizeFilter parameter must be between 0 and 3 included"));
        }

107
108
        $this->sizeFilter = $sizeFilter;

109
        $statistics = ["unit" => $this->sizeCategories[$this->sizeFilter]];
110
        if (is_null($operation) || empty($operation)) {
111
            $statistics = $this->defaultStats($startDate, $endDate, $statistics);
112
        } elseif (!empty($operation) && !in_array($operation, ['deposit', 'deleted', 'conserved', 'restituted', 'transfered', 'communicated'])) {
113
            throw new \core\Exception\BadRequestException($this->translator->getText("Operation type not supported"));
114
        } elseif (!empty($operation) && is_null($filter)) {
115
            throw new \core\Exception\BadRequestException($this->translator->getText("Filter cannot be null if an operation is selected"));
116
117
        }

118
        if (!empty($operation)) {
119
120
            switch ($operation) {
                case 'deposit':
121
                    $statistics = $this->depositStats($startDate, $endDate, $filter, $statistics);
Jerome Boucher's avatar
Jerome Boucher committed
122
                    break;
123
                case 'deleted':
124
                    $statistics = $this->deletedStats($startDate, $endDate, $filter, $statistics);
125
126
                    break;
                case 'conserved':
127
                    $statistics = $this->conservedStats($endDate, $filter, $statistics);
Jerome Boucher's avatar
Jerome Boucher committed
128
                    break;
129
                case 'restituted':
130
                    $statistics = $this->restitutedStats($startDate, $endDate, $filter, $statistics);
131
132
                    break;
                case 'transfered':
133
                    $statistics = $this->transferedStats($startDate, $endDate, $filter, $statistics);
134
                    break;
135
136
137
                case 'communicated':
                    $statistics = $this->communicatedStats($startDate, $endDate, $filter, $statistics);
                    break;
Jerome Boucher's avatar
Jerome Boucher committed
138
            }
Jerome Boucher's avatar
Jerome Boucher committed
139
        }
140

Jerome Boucher's avatar
Jerome Boucher committed
141
        return $statistics;
142
143
    }

144
145
146
    /**
     * Basics stats to return for homepage
     *
147
148
149
     * @param  datetime $startDate  Starting Date
     * @param  datetime $endDate    End date
     * @param  array    $statistics Array of statistics
150
     *
151
     * @return array                Associative array of statistics
152
     */
153
    protected function defaultStats($startDate, $endDate, $statistics = [])
154
    {
155
156
        $statistics['depositMemorySize'] = floatval(str_replace(" ", "", $this->getSizeByEventType('ArchiveTransfer', ['recordsManagement/deposit', 'recordsManagement/depositNewResource'], $jsonColumnNumber = 8, $startDate, $endDate, true)))
                                        + floatval(str_replace(" ", "", $this->getSizeForDirectEvent('recordsManagement/deposit', 8, null, $startDate, $endDate)));
157
        $statistics['depositMemorySize'] = $this->formatSize($statistics['depositMemorySize'], false);
158
159
160
161
162
        $statistics['depositMemoryCount'] = $this->getCountByEventType('ArchiveTransfer', $startDate, $endDate, true)
                                        + $this->getCountForDirectEvent('recordsManagement/deposit', null, $startDate, $endDate);

        $statistics['deletedMemorySize'] = floatval(str_replace(" ", "", $this->getSizeByEventType('ArchiveDestructionRequest', ['recordsManagement/destruction'], $jsonColumnNumber = 6, $startDate, $endDate)))
                                        + floatval(str_replace(" ", "", $this->getSizeForDirectEvent('recordsManagement/destruction', 6, null, $startDate, $endDate)));
163
        $statistics['deletedMemorySize'] = $this->formatSize($statistics['deletedMemorySize'], false);
164
165
        $statistics['deletedMemoryCount'] = $this->getCountByEventType('ArchiveDestructionRequest', $startDate, $endDate)
                                        + $this->getCountForDirectEvent('recordsManagement/destruction', null, $startDate, $endDate);
166
167

        if (\laabs::configuration('medona')['transaction']) {
168
            $statistics['transferedMemorySize'] = $this->getSizeByEventType('ArchiveTransfer', ['recordsManagement/outgoingTransfer'], $jsonColumnNumber = 6, $startDate, $endDate);
169
            $statistics['transferedMemoryCount'] = $this->getCountByEventType('ArchiveTransfer', $startDate, $endDate);
170
            $statistics['restitutionMemorySize'] = $this->getSizeByEventType('ArchiveRestitutionRequest', ['recordsManagement/restitution'], $jsonColumnNumber = 6, $startDate, $endDate);
171
            $statistics['restitutionMemoryCount'] = $this->getCountByEventType('ArchiveRestitutionRequest', $startDate, $endDate);
172
            $statistics['communicatedMemorySize'] = $this->getSizeByEventType('ArchiveDeliveryRequest', ['recordsManagement/delivery'], $jsonColumnNumber = 6, $startDate, $endDate);
173
            $statistics['communicatedMemoryCount'] = $this->getCountByEventType('ArchiveDeliveryRequest', $startDate, $endDate);
174
175
        }

176
177
178
        $statistics['currentMemorySize'] = $this->getArchiveSize($endDate);
        $statistics['currentMemoryCount'] = $this->getArchiveCount($endDate);

179
180
181
182
        if ($startDate) {
            $statistics['evolutionSize'] = $statistics['currentMemorySize'] - $this->getArchiveSize($startDate);
            $statistics['evolutionCount'] = $statistics['currentMemoryCount'] - $this->getArchiveCount($startDate);
            if ($statistics['evolutionSize'] != (integer)$statistics['evolutionSize']) {
183
                $statistics['evolutionSize'] = number_format($statistics['evolutionSize'], 3, ".", " ");
184
185
            }
        }
186

187
        if ($statistics['currentMemorySize'] != (integer)$statistics['currentMemorySize']) {
188
            $statistics['currentMemorySize'] = number_format($statistics['currentMemorySize'], 3, ".", " ");
189
190
        }

191
        return $statistics;
192
193
    }

194
195
196
197
198
    protected function addDirectStats($stats, $directStats, $filter, $resultType)
    {
        foreach ($directStats as $groupBy => $result) {
            $groupByFound = false;
            for ($i = 0; $i < count($stats); $i++) {
199
                if ($stats[$i][$filter] == $groupBy) {
200
201
202
203
                    if ($resultType == 'sum') {
                        $result1 = floatval(str_replace(" ", "", $stats[$i][$resultType]));
                        $result2 = floatval(str_replace(" ", "", $result));
                    }
204
                    $stats[$i][$resultType] = $this->formatSize($resultType == 'sum' ? $result1 + $result2 : $stats[$i][$resultType] + $result);
205
206
207
208
209
210
211
212
213
214
215
                    $groupByFound = true;
                    break;
                }
            }
            if (!$groupByFound) {
                $stats[] = [$filter => $groupBy, $resultType => $result];
            }
        }
        return $stats;
    }

216
217
218
219
220
221
    /**
     * Statistics aggregator for deposit event
     *
     * @param  datetime $startDate starting date
     * @param  datetime $endDate   End date
     * @param  string   $filter    Group by argument
222
     * @param  array    $statistics Array of statistics
223
224
225
     *
     * @return array               Associative of statistics
     */
226
    protected function depositStats($startDate, $endDate, $filter, $statistics = [])
227
    {
228
229
230
231
        $statistics['groupedDepositMemorySize'] = $this->getSizeByEventTypeOrdered('ArchiveTransfer', ['recordsManagement/deposit', 'recordsManagement/depositNewResource'], 8, $startDate, $endDate, $filter, true);
        $directArchiveTransferStatsSize = $this->getSizeForDirectEvent('recordsManagement/deposit', 8, $filter, $startDate, $endDate);
        $statistics['groupedDepositMemorySize'] = $this->addDirectStats($statistics['groupedDepositMemorySize'], $directArchiveTransferStatsSize, strtolower($filter), 'sum');

232
        $statistics['groupedDepositMemoryCount'] = $this->getCountByEventTypeOrdered('ArchiveTransfer', $startDate, $endDate, $filter, true);
233
234
235
        $directArchiveTransferStatsCount = $this->getCountForDirectEvent('recordsManagement/deposit', $filter, $startDate, $endDate);
        $statistics['groupedDepositMemoryCount'] = $this->addDirectStats($statistics['groupedDepositMemoryCount'], $directArchiveTransferStatsCount, strtolower($filter), 'count');

236
        return $statistics;
237
238
    }

239
240
241
242
243
244
    /**
     * Statistics aggregator for deleted event
     *
     * @param  datetime $startDate starting date
     * @param  datetime $endDate   End date
     * @param  string   $filter    Group by argument
245
     * @param  array    $statistics Array of statistics
246
247
248
     *
     * @return array               Associative of statistics
     */
249
    protected function deletedStats($startDate, $endDate, $filter, $statistics = [])
250
    {
251
252
253
254
        $statistics['deletedGroupedMemorySize'] = $this->getSizeByEventTypeOrdered('ArchiveDestructionRequest', ['recordsManagement/destruction', 'recordsManagement/elimination'], 6, $startDate, $endDate, $filter);
        $directDeletedStatsSize = $this->getSizeForDirectEvent('recordsManagement/destruction', 6, $filter, $startDate, $endDate);
        $statistics['deletedGroupedMemorySize'] = $this->addDirectStats($statistics['deletedGroupedMemorySize'], $directDeletedStatsSize, strtolower($filter), 'sum');

255
        $statistics['deletedGroupedMemoryCount'] = $this->getCountByEventTypeOrdered('ArchiveDestructionRequest', $startDate, $endDate, $filter);
256
257
        $directDeletedStatsCount = $this->getCountForDirectEvent('recordsManagement/destruction', $filter, $startDate, $endDate);
        $statistics['deletedGroupedMemoryCount'] = $this->addDirectStats($statistics['deletedGroupedMemoryCount'], $directDeletedStatsCount, strtolower($filter), 'count');
258
259
260
261

        return $statistics;
    }

262
263
264
    /**
     * Statistics aggregator for conserved archive
     *
265
266
     * @param  datetime $endDate    End date
     * @param  string   $filter     Group by argument
267
     * @param  array    $statistics Array of statistics
268
     *
269
     * @return array                Associative of statistics
270
     */
271
    protected function conservedStats($endDate, $filter, $statistics = [])
272
273
274
275
276
277
278
    {
        $statistics['groupedArchiveSize'] = $this->getArchiveSizeOrdered($filter, $endDate);
        $statistics['groupedArchiveCount'] = $this->getArchiveCountOrdered($filter, $endDate);

        return $statistics;
    }

279
280
281
    /**
     * Statistics aggregator for restituted event
     *
282
283
284
     * @param  datetime $startDate  starting date
     * @param  datetime $endDate    End date
     * @param  string   $filter     Group by argument
285
     * @param  array    $statistics Array of statistics
286
     *
287
     * @return array                Associative of statistics
288
     */
289
    protected function restitutedStats($startDate, $endDate, $filter, $statistics = [])
290
    {
291
292
        $jsonSizeColumnNumber = 6;
        $statistics['restitutedGroupedMemorySize'] = $this->getSizeByEventTypeOrdered('ArchiveRestitutionRequest', ['recordsManagement/restitution'], $jsonSizeColumnNumber, $startDate, $endDate, $filter);
293
        $statistics['restitutedGroupedMemoryCount'] = $this->getCountByEventTypeOrdered('ArchiveRestitutionRequest', $startDate, $endDate, $filter);
294
295
296
297
298
299
300

        return $statistics;
    }

    /**
     * Statistics aggregator for transfered event
     *
301
302
303
     * @param  datetime $startDate  starting date
     * @param  datetime $endDate    End date
     * @param  string   $filter     Group by argument
304
     * @param  array    $statistics Array of statistics
305
     *
306
     * @return array                Associative of statistics
307
     */
308
    protected function transferedStats($startDate, $endDate, $filter, $statistics = [])
309
    {
310
311
        $jsonSizeColumnNumber = 6;
        $statistics['transferedGroupedMemorySize'] = $this->getSizeByEventTypeOrdered('Deletion', ['recordsManagement/outgoingTransfer'], $jsonSizeColumnNumber, $startDate, $endDate, $filter);
312
        $statistics['transferedGroupedMemoryCount'] = $this->getCountByEventTypeOrdered('Deletion', $startDate, $endDate, $filter);
313
314
315
316

        return $statistics;
    }

317
318
319
    /**
     * Statistics aggregator for communicated event
     *
320
321
322
     * @param  datetime $startDate  starting date
     * @param  datetime $endDate    End date
     * @param  string   $filter     Group by argument
323
     * @param  array    $statistics Array of statistics
324
     *
325
     * @return array                Associative of statistics
326
327
328
     */
    protected function communicatedStats($startDate, $endDate, $filter, $statistics = [])
    {
329
330
        $jsonSizeColumnNumber = 6;
        $statistics['communicatedGroupedMemorySize'] = $this->getSizeByEventTypeOrdered('ArchiveDeliveryRequest', ['recordsManagement/delivery'], $jsonSizeColumnNumber, $startDate, $endDate, $filter);
331
        $statistics['communicatedGroupedMemoryCount'] = $this->getCountByEventTypeOrdered('ArchiveDeliveryRequest', $startDate, $endDate, $filter);
332
333
334
335

        return $statistics;
    }

336
337
338
    /**
     * Sum all event info for a particular event
     *
339
     * @param  array    $messageType      The type of the message
340
341
342
343
     * @param  array    $eventTypes       Array of event types
     * @param  integer  $jsonColumnNumber json Column number for size parameter in lifeCycle event table
     * @param  datetime $startDate        Starting Date
     * @param  datetime $endDate          End date
344
     * @param  boolean  $isIncoming       Is the message incoming if type is Archive Transfer
345
346
347
     *
     * @return integer                    Sum of size for events
     */
348
    protected function getSizeByEventType($messageType, $eventTypes, $jsonColumnNumber, $startDate = null, $endDate = null, $isIncoming = false)
349
350
351
352
353
    {
        $explodingEventTypes = $this->stringifyEventTypes($eventTypes);
        $in = $explodingEventTypes['in'];
        $inParams = $explodingEventTypes['inParams'];

354
        if ($messageType == "ArchiveTransfer") {
355
            $isIncomingCondition = '';
356
            if (!$isIncoming) {
357
                $isIncomingCondition .= ' OR "message"."status" = \'validated\'';
358
            }
359
            $isIncomingCondition .= ') AND "message"."isIncoming" = ' . ($isIncoming ? 'TRUE' : 'FALSE');
360
361
        }

362
363
364
365
        $query = 'WITH RECURSIVE get_children_size(archive_id, volume) AS (
            SELECT "archive"."archiveId", "event"."eventInfo"::json->>'.$jsonColumnNumber.'
            FROM "medona"."unitIdentifier" "unitIdentifier"
            INNER JOIN "medona"."message" "message"
366
            ON "message"."messageId" = "unitIdentifier"."messageId" AND "message"."type" = \''.$messageType.'\' AND ("message"."status" = \'processed\''. (isset($isIncomingCondition) ? $isIncomingCondition : ')') .
367
368
369
370
371
372
            ($startDate ? ' AND "message"."date">\''.$startDate.'\'::timestamp AND "message"."date"<\''.$endDate.'\'::timestamp' : '').'
            INNER JOIN "recordsManagement"."archive" "archive"
            ON "archive"."archiveId" = "unitIdentifier"."objectId" AND ("archive"."parentArchiveId" is null or "archive"."parentArchiveId" not in (
                SELECT "unitIdentifier"."objectId"
                FROM "medona"."unitIdentifier" "unitIdentifier"
                INNER JOIN "medona"."message" "message"
373
                ON "message"."messageId" = "unitIdentifier"."messageId" AND "message"."type" = \''.$messageType.'\' AND ("message"."status" = \'processed\''. (isset($isIncomingCondition) ? $isIncomingCondition : ')') .
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
                ($startDate ? ' AND "message"."date">\''.$startDate.'\'::timestamp AND "message"."date"<\''.$endDate.'\'::timestamp' : '').'
            ))
            INNER JOIN "lifeCycle"."event" "event" ON "event"."objectId" = "archive"."archiveId" AND "event"."eventType" IN ('.$in.')
                UNION ALL
            SELECT "archive"."archiveId", "event"."eventInfo"::json->>8
            FROM "recordsManagement"."archive" "archive"
            JOIN get_children_size "archive_size" ON 1=1
            LEFT JOIN "lifeCycle"."event" "event" ON "event"."objectId" = "archive"."archiveId" AND "event"."eventType" IN ('.$in.')'.
            ($startDate ? ' AND "event"."timestamp">\''.$startDate.'\'::timestamp AND "event"."timestamp"<\''.$endDate.'\'::timestamp' : '').'
            WHERE "archive"."parentArchiveId" = "archive_size"."archive_id"
        )
        SELECT SUM(CAST(NULLIF("archive_size"."volume", \'\') AS INTEGER))
        FROM get_children_size "archive_size"';

        $result = $this->executeQuery($query, $inParams, $eventTypes[0] == 'recordsManagement/deposit');
389
390
391
392
        $sum = 0;
        if (isset($result[0]['sum'])) {
            $sum = $result[0]['sum'];
        }
Jerome Boucher's avatar
Jerome Boucher committed
393
394
395
396

        return $sum;
    }

397
398
399
    /**
     * Count all event info for particular event(s)
     *
400
     * @param  array    $messageType           The type of the message
401
402
     * @param  datetime $startDate             Starting Date
     * @param  datetime $endDate               End date
403
     * @param  boolean  $isIncoming            Is the message incoming if type is Archive Transfer
404
     *
405
     * @return integer                         Count of size for events
406
     */
407
    protected function getCountByEventType($messageType, $startDate = null, $endDate = null, $isIncoming = false)
408
    {
409
        if ($messageType == "ArchiveTransfer") {
410
            $isIncomingCondition = '';
411
            if (!$isIncoming) {
412
                $isIncomingCondition .= ' OR "message"."status" = \'validated\'';
413
            }
414
            $isIncomingCondition .= ') AND "message"."isIncoming" = ' . ($isIncoming ? 'TRUE' : 'FALSE');
415
416
        }

417
418
419
420
421
422
423
424
425
426
427
428
        $query = 'SELECT  COUNT("unitIdentifier"."objectId")
            FROM "medona"."message" "message"
            INNER JOIN "medona"."unitIdentifier" "unitIdentifier"
            ON "unitIdentifier"."messageId" = "message"."messageId"
            INNER JOIN "recordsManagement"."archive" "archive"
            ON "archive"."archiveId" = "unitIdentifier"."objectId"
            AND ("archive"."parentArchiveId" IS NULL OR NOT "archive"."parentArchiveId" IN (
                SELECT "unitIdentifier"."objectId"
                FROM "medona"."message" "message"
                INNER JOIN "medona"."unitIdentifier" "unitIdentifier"
                ON "unitIdentifier"."messageId" = "message"."messageId"
                WHERE "message"."type" = \''.$messageType.'\'
429
                AND ("message"."status" = \'processed\''.
430
                (isset($isIncomingCondition) ? $isIncomingCondition : ')').'
431
432
            ))
            WHERE "message"."type" = \''.$messageType.'\' 
433
            AND ("message"."status" = \'processed\''. (isset($isIncomingCondition) ? $isIncomingCondition : ')') .
434
435
436
            ($startDate ? ' AND "message"."date">\''.$startDate.'\'::timestamp AND "message"."date"<\''.$endDate.'\'::timestamp' : '');

        $count = $this->executeQuery($query)[0]['count'];
437
438
439
        return $count;
    }

440
441
442
    /**
     * Count all event info for particular event(s) ordered by another event
     *
443
     * @param  array    $messageType           The type of the message
444
445
446
     * @param  datetime $startDate             Starting Date
     * @param  datetime $endDate               End date
     * @param  string   $groupBy               Name of Group By
447
     * @param  boolean  $isIncoming            Is the message incoming if type is Archive Transfer
448
     *
449
     * @return integer                         Count of size for events
450
     */
451
    protected function getCountByEventTypeOrdered($messageType, $startDate = null, $endDate = null, $groupBy = null, $isIncoming = false)
Jerome Boucher's avatar
Jerome Boucher committed
452
    {
453
454
        $isArchivalProfile = $groupBy == "archivalProfile";

455
        if ($messageType == "ArchiveTransfer") {
456
            $isIncomingCondition = '';
457
            if (!$isIncoming) {
458
                $isIncomingCondition .= ' OR "message"."status" = \'validated\'';
459
            }
460
            $isIncomingCondition .= ') AND "message"."isIncoming" = ' . ($isIncoming ? 'TRUE' : 'FALSE');
461
462
        }

463
464
465
466
467
468
469
470
471
472
473
        $query = 'SELECT '.($isArchivalProfile ? 'COALESCE("archivalProfile"."name", \'Without profile\')' : '"organization"."displayName"').' as '.$groupBy.', COUNT("unitIdentifier"."objectId")
        FROM "medona"."message" "message"
        INNER JOIN "medona"."unitIdentifier" "unitIdentifier"
        ON "unitIdentifier"."messageId" = "message"."messageId"
        INNER JOIN "recordsManagement"."archive" "archive"
        ON "archive"."archiveId" = "unitIdentifier"."objectId" AND ("archive"."parentArchiveId" IS NULL OR NOT "archive"."parentArchiveId" IN (
            SELECT "unitIdentifier"."objectId"
            FROM "medona"."message" "message"
            INNER JOIN "medona"."unitIdentifier" "unitIdentifier"
            ON "unitIdentifier"."messageId" = "message"."messageId"
            WHERE "message"."type" = \''.$messageType.'\'
474
            AND ("message"."status" = \'processed\''. (isset($isIncomingCondition) ? $isIncomingCondition : ')') .
475
476
477
478
479
480
481
482
            ($startDate ? ' AND "message"."date">\''.$startDate.'\'::timestamp AND "message"."date"<\''.$endDate.'\'::timestamp' : '').'
        ))'.
        ($isArchivalProfile
            ? ' LEFT JOIN "recordsManagement"."archivalProfile" "archivalProfile"
                ON "archivalProfile"."reference" = "archive"."archivalProfileReference"'
            : ' INNER JOIN "organization"."organization" "organization"
                ON "organization"."registrationNumber" = "archive"."archiverOrgRegNumber"').
        ' WHERE "message"."type" = \''.$messageType.'\'
483
        AND ("message"."status" = \'processed\''. (isset($isIncomingCondition) ? $isIncomingCondition : ')') .
484
485
486
487
        ($startDate ? ' AND "message"."date">\''.$startDate.'\'::timestamp AND "message"."date"<\''.$endDate.'\'::timestamp' : '').'
        GROUP BY '.($isArchivalProfile ? '"archivalProfile"."name"' : '"organization"."displayName"');

        $sum = $this->executeQuery($query);
Jerome Boucher's avatar
Jerome Boucher committed
488
489
490
        return $sum;
    }

491
492
493
    /**
     * Sum all events info for particular event(s) ordered by another event
     *
494
     * @param  array    $messageType           The type of the message
495
496
497
498
499
     * @param  array    $eventTypes            Array of event types
     * @param  integer  $jsonColumnNumber      json Column number for size parameter in lifeCycle event table
     * @param  datetime $startDate             Starting Date
     * @param  datetime $endDate               End date
     * @param  string   $groupBy               Name of Group By
500
     * @param  boolean  $isIncoming            Is the message incoming if type is Archive Transfer
501
     *
502
     * @return integer                         Sum of size for events
503
     */
504
    protected function getSizeByEventTypeOrdered($messageType, $eventTypes, $jsonColumnNumber, $startDate = null, $endDate = null, $groupBy = null, $isIncoming = false)
Jerome Boucher's avatar
Jerome Boucher committed
505
506
507
508
    {
        $explodingEventTypes = $this->stringifyEventTypes($eventTypes);
        $in = $explodingEventTypes['in'];
        $inParams = $explodingEventTypes['inParams'];
509
510
        $isArchivalProfile = $groupBy == "archivalProfile";

511
        if ($messageType == "ArchiveTransfer") {
512
            $isIncomingCondition = '';
513
            if (!$isIncoming) {
514
                $isIncomingCondition .= ' OR "message"."status" = \'validated\'';
515
            }
516
            $isIncomingCondition .= ') AND "message"."isIncoming" = ' . ($isIncoming ? 'TRUE' : 'FALSE');
517
518
        }

519
520
521
522
        $query = 'WITH RECURSIVE get_children_size(archive_id, volume, '.($isArchivalProfile ? "profile" : "org_reg").') AS (
            SELECT "archive"."archiveId", "event"."eventInfo"::json->>'.$jsonColumnNumber.', '.($isArchivalProfile ? 'COALESCE("archive"."archivalProfileReference", \'\')' : '"archive"."archiverOrgRegNumber"').'
            FROM "medona"."unitIdentifier" "unitIdentifier"
            INNER JOIN "medona"."message" "message"
523
            ON "message"."messageId" = "unitIdentifier"."messageId" and "message"."type" = \''.$messageType.'\' AND ("message"."status" = \'processed\''. (isset($isIncomingCondition) ? $isIncomingCondition : ')') .
524
525
526
527
528
529
            ($startDate ? ' AND "message"."date">\''.$startDate.'\'::timestamp AND "message"."date"<\''.$endDate.'\'::timestamp' : '').'
            INNER JOIN "recordsManagement"."archive" "archive"
            ON "archive"."archiveId" = "unitIdentifier"."objectId" and ("archive"."parentArchiveId" is null or "archive"."parentArchiveId" not in (
                SELECT "unitIdentifier"."objectId"
                FROM "medona"."unitIdentifier" "unitIdentifier"
                INNER JOIN "medona"."message" "message"
530
                ON "message"."messageId" = "unitIdentifier"."messageId" and "message"."type" = \''.$messageType.'\' AND ("message"."status" = \'processed\''. (isset($isIncomingCondition) ? $isIncomingCondition : ')') .
531
532
533
534
535
536
537
538
539
540
                ($startDate ? ' AND "message"."date">\''.$startDate.'\'::timestamp AND "message"."date"<\''.$endDate.'\'::timestamp' : '').'
            ))
            INNER JOIN "lifeCycle"."event" "event" ON "event"."objectId" = "archive"."archiveId" AND "event"."eventType" IN ('.$in.')
                UNION ALL
            SELECT "archive"."archiveId", "event"."eventInfo"::json->>'.$jsonColumnNumber.', '.($isArchivalProfile ? 'coalesce("archive"."archivalProfileReference", "archive_size"."profile", \'\')' : '"archive"."archiverOrgRegNumber"').'
            FROM "recordsManagement"."archive" "archive"
            JOIN get_children_size "archive_size" ON 1=1
            LEFT JOIN "lifeCycle"."event" "event" ON "event"."objectId" = "archive"."archiveId" AND "event"."eventType" IN ('.$in.')'.
            ($startDate ? ' AND "event"."timestamp">\''.$startDate.'\'::timestamp AND "event"."timestamp"<\''.$endDate.'\'::timestamp' : '').'
            WHERE "archive"."parentArchiveId" = "archive_size"."archive_id"
541
        )
542
        SELECT '.($isArchivalProfile ? 'COALESCE("archivalProfile"."name", \'Without profile\')' : '"organization"."displayName"').' as '.$groupBy.', SUM(CAST(NULLIF("archive_size"."volume", \'\') AS INTEGER))
543
        FROM get_children_size "archive_size"'.
544
545
546
547
548
        ($isArchivalProfile
            ? ' LEFT JOIN "recordsManagement"."archivalProfile" "archivalProfile" on "archivalProfile"."reference" = "archive_size"."profile"'
            : ' INNER JOIN "organization"."organization" "organization" on "organization"."registrationNumber" = "archive_size"."org_reg"'
        ).'
        GROUP BY '.($isArchivalProfile ? '"archivalProfile"."name"' : '"organization"."displayName"');
549
550

        $sum = $this->executeQuery($query, $inParams);
551
552
553
554

        return $sum;
    }

555
556
557
558
559
560
561
562
563
564
565
566
567
    /**
     * Retrieve count of archives unto a specific date
     *
     * @param  datetime $endDate End date
     *
     * @return integer           Size of archive
     */
    protected function getArchiveCount($endDate = null)
    {
        if (is_null($endDate)) {
            $endDate = (string) \laabs::newDateTime()->format('Y-m-d H:i:s');
        }

568
569
570
571
572
        $query = 'SELECT COUNT(*)
                FROM "recordsManagement"."archive"
                WHERE "depositDate"<\''.$endDate.'\'::timestamp
                AND ("status" = \'preserved\' OR ("lastModificationDate" IS NOT NULL AND "lastModificationDate">\''.$endDate.'\'::timestamp))
                AND "parentArchiveId" IS NULL';
573
574
575
576
577
578
579
580

        $stmt = $this->pdo->prepare($query);
        $stmt->execute();
        $result = $stmt->fetch()['count'];

        return $result;
    }

581
582
583
584
585
586
587
    /**
     * Retrieve size of digital resources unto a specific date
     *
     * @param  datetime $endDate End date
     *
     * @return integer           Size of archive
     */
588
    protected function getArchiveSize($endDate = null)
589
590
591
592
    {
        if (is_null($endDate)) {
            $endDate = (string) \laabs::newDateTime()->format('Y-m-d H:i:s');
        }
Jerome Boucher's avatar
Jerome Boucher committed
593

594
        $query = <<<EOT
595
596
597
SELECT SUM("digitalResource"."size")
FROM "digitalResource"."digitalResource"
INNER JOIN "recordsManagement"."archive" ON "archive"."archiveId" = "digitalResource"."archiveId"
598
WHERE "archive"."depositDate"<'$endDate'::timestamp AND ("archive"."status" = 'preserved' OR ("archive"."lastModificationDate" IS NOT NULL AND "archive"."lastModificationDate">'$endDate'::timestamp));
599
EOT;
600

601
602
        $stmt = $this->pdo->prepare($query);
        $stmt->execute();
603
604
605
        $result = $stmt->fetch()['sum'];
        $sum = (integer)$result / pow(1000, $this->sizeFilter);

606
        return $sum;
607
608
    }

609
610
611
612
613
614
615
616
    /**
     * Retrieve size of digital resources unto a specific date group by a parameter
     *
     * @param  string   $groupBy Ordering parameter
     * @param  datetime $endDate End date
     *
     * @return array             Size of archive ordered by parameter
     */
Jerome Boucher's avatar
Jerome Boucher committed
617
618
    protected function getArchiveSizeOrdered($groupBy, $endDate = null)
    {
619
        $isArchivalProfile = false;
Jerome Boucher's avatar
Jerome Boucher committed
620
621
622
        switch ($groupBy) {
            case 'archivalProfile':
                $tableProperty = "archivalProfileReference";
623
                $isArchivalProfile = true;
Jerome Boucher's avatar
Jerome Boucher committed
624
625
626
627
628
629
630
631
632
633
                break;
            case 'originatingOrg':
                $tableProperty = "originatorOrgRegNumber";
                break;
        }

        if (is_null($endDate)) {
            $endDate = (string) \laabs::newDateTime()->format('Y-m-d H:i:s');
        }

634
635
636
637
        $query = 'WITH RECURSIVE get_children_size(archive_id, volume, group_by) AS (
            SELECT "archive"."archiveId", "digitalResource"."size", "archive"."'.$tableProperty.'"
            FROM "recordsManagement"."archive" "archive"
            LEFT JOIN "digitalResource"."digitalResource" "digitalResource" ON "digitalResource"."archiveId" = "archive"."archiveId"
638
            WHERE "archive"."parentArchiveId" IS NULL AND "archive"."depositDate" < \''.$endDate.'\'::timestamp AND ("status" = \'preserved\' OR ("lastModificationDate" IS NOT NULL AND "lastModificationDate">\''.$endDate.'\'::timestamp))
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
          UNION ALL
            SELECT "archive"."archiveId", "digitalResource"."size", "archive_size"."group_by"
            FROM "recordsManagement"."archive" "archive"
            JOIN get_children_size "archive_size" ON 1=1
            LEFT JOIN "digitalResource"."digitalResource" "digitalResource" ON "digitalResource"."archiveId" = "archive"."archiveId"
            WHERE "archive"."parentArchiveId" = "archive_size"."archive_id"
        )
        SELECT '.($isArchivalProfile ? '"archivalProfile"."name"' : '"organization"."displayName"').' AS '.$groupBy.', SUM(CAST("archive_size"."volume" AS INTEGER))
        FROM get_children_size "archive_size"'.(
            $isArchivalProfile
            ? ' INNER JOIN "recordsManagement"."archivalProfile" "archivalProfile" ON "archivalProfile"."reference" = "archive_size"."group_by"'
            : ' INNER JOIN "organization"."organization" "organization" ON "organization"."registrationNumber" = "archive_size"."group_by"'
        ).
        ' GROUP BY '.($isArchivalProfile ? '"archivalProfile"."name"' : '"organization"."displayName"');
        
654
        $results = $this->executeQuery($query);
Jerome Boucher's avatar
Jerome Boucher committed
655
656
657
658

        return $results;
    }

659
660
661
662
663
664
665
666
    /**
     * Retrieve count of digital resources unto a specific date group by a parameter
     *
     * @param  string   $groupBy Ordering parameter
     * @param  datetime $endDate End date
     *
     * @return array             Count of archive ordered by parameter
     */
Jerome Boucher's avatar
Jerome Boucher committed
667
668
    protected function getArchiveCountOrdered($groupBy, $endDate = null)
    {
669
        $isArchivalProfile = false;
Jerome Boucher's avatar
Jerome Boucher committed
670
671
        switch ($groupBy) {
            case 'archivalProfile':
672
                $isArchivalProfile = true;
Jerome Boucher's avatar
Jerome Boucher committed
673
674
675
676
677
678
679
680
681
682
683
                $tableProperty = "archivalProfileReference";
                break;
            case 'originatingOrg':
                $tableProperty = "originatorOrgRegNumber";
                break;
        }

        if (is_null($endDate)) {
            $endDate = (string) \laabs::newDateTime()->format('Y-m-d H:i:s');
        }

684
685
686
687
688
689
690
        $query = 'SELECT '.($isArchivalProfile ? '"archivalProfile"."name"' : '"organization"."displayName"').' AS '.$groupBy.', COUNT ("archive".*)
                FROM "recordsManagement"."archive" "archive"'.
                (
                    $isArchivalProfile
                    ? ' INNER JOIN "recordsManagement"."archivalProfile" "archivalProfile" ON "archivalProfile"."reference" = "archive"."'.$tableProperty.'"'
                    : ' INNER JOIN "organization"."organization" "organization" ON "organization"."registrationNumber" = "archive"."'.$tableProperty.'"'
                ).
691
                ' WHERE "depositDate" < \''.$endDate.'\'::timestamp AND ("status" = \'preserved\' OR ("lastModificationDate" IS NOT NULL AND "lastModificationDate">\''.$endDate.'\'::timestamp)) AND "archive"."parentArchiveId" IS NULL
692
                GROUP BY '.($isArchivalProfile ? '"archivalProfile"."name"' : '"organization"."displayName"');
Jerome Boucher's avatar
Jerome Boucher committed
693

694
        $results = $this->executeQuery($query);
Jerome Boucher's avatar
Jerome Boucher committed
695
696
697
698

        return $results;
    }

699
700
701
    /**
     * Sum all archives size for direct archive transfer
     *
702
703
704
705
706
     * @param  string   $eventType             Type of event
     * @param  integer  $jsonSizeColumnNumber  json Column number for size parameter in lifeCycle event table
     * @param  string   $groupBy               Ordering parameter
     * @param  datetime $startDate             Starting Date
     * @param  datetime $endDate               End date
707
     *
708
     * @return integer                         Sum of size for events
709
710
711
712
     */
    protected function getSizeForDirectEvent($eventType, $jsonSizeColumnNumber, $groupBy = null, $startDate = null, $endDate = null)
    {
        if ($groupBy) {
713
714
715
            $selectCondition = ($groupBy == 'archivalProfile') ? 'COALESCE("archivalProfile"."name", \'Without profile\')' : '"organization"."displayName"';
            $groupByCondition = ($groupBy == 'archivalProfile') ? '"archivalProfile"."name"' : '"organization"."displayName"';
            $joinCondition = ($groupBy == 'archivalProfile')
716
717
718
                ? ' LEFT JOIN "recordsManagement"."archivalProfile" "archivalProfile"
                ON "archivalProfile"."reference" = "event"."eventInfo"::json->>10'
                : ' INNER JOIN "organization"."organization" "organization"
719
                ON "organization"."registrationNumber" = "event"."eventInfo"::json->>4';
720
721
        }

722
        $query = 'SELECT '.($groupBy ? $selectCondition . ' AS "'.$groupBy.'", ' : '').'SUM(CAST(COALESCE(NULLIF("event"."eventInfo"::json->>'.$jsonSizeColumnNumber.', \'\'), \'0\') AS INTEGER))
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
        FROM "lifeCycle"."event" "event"'.
        ($groupBy ? $joinCondition : '').'
        WHERE "event"."eventType" IN (\''.$eventType.'\')
        AND "event"."objectId" NOT IN (
            SELECT "objectId"
            FROM "medona"."unitIdentifier"
        )'.
        ($startDate ? ' AND "event"."timestamp">\''.$startDate.'\'::timestamp AND "event"."timestamp"<\''.$endDate.'\'::timestamp' : '').
        ($groupBy ? ' GROUP BY ' . $groupByCondition : '');

        $result = $this->executeQuery($query);

        $sum = 0;
        if ($groupBy) {
            $sum = [];
            foreach ($result as $row) {
                if (isset($row[$groupBy])) {
                    $sum[$row[$groupBy]] = $row["sum"];
                }
            }
        } elseif (isset($result[0]['sum'])) {
            $sum = $result[0]['sum'];
        }

        return $sum;
    }

    /**
     * Count all archives for direct archive transfer
     *
753
754
     * @param  string   $eventType        Type of event
     * @param  string   $groupBy          Ordering parameter
755
756
757
758
759
760
761
762
763
764
765
766
767
768
     * @param  datetime $startDate        Starting Date
     * @param  datetime $endDate          End date
     *
     * @return integer                    Sum of size for events
     */
    protected function getCountForDirectEvent($eventType, $groupBy = false, $startDate = null, $endDate = null)
    {
        if ($groupBy) {
            $selectCondition = $groupBy == 'archivalProfile' ? 'COALESCE("archivalProfile"."name", \'Without profile\')' : '"organization"."displayName"';
            $groupByCondition = $groupBy == 'archivalProfile' ? '"archivalProfile"."name"' : '"organization"."displayName"';
            $joinCondition = $groupBy == 'archivalProfile'
                ? ' LEFT JOIN "recordsManagement"."archivalProfile" "archivalProfile"
                ON "archivalProfile"."reference" = "event"."eventInfo"::json->>10'
                : ' INNER JOIN "organization"."organization" "organization"
769
                ON "organization"."registrationNumber" = "event"."eventInfo"::json->>4';
770
771
        }

772
        $query = 'SELECT '.($groupBy ? $selectCondition . ' AS "'.$groupBy.'", ' : '').'COUNT("event"."eventId")
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
        FROM "lifeCycle"."event" "event"'.
        ($groupBy ? $joinCondition : '').'
        WHERE "event"."eventType" IN (\''.$eventType.'\')
        AND "event"."objectId" NOT IN (
            SELECT "objectId"
            FROM "medona"."unitIdentifier"
        )'.
        ($startDate ? ' AND "event"."timestamp">\''.$startDate.'\'::timestamp AND "event"."timestamp"<\''.$endDate.'\'::timestamp' : '').
        ($groupBy ? ' GROUP BY ' . $groupByCondition : '');

        $result = $this->executeQuery($query);
        
        $count = 0;
        if ($groupBy) {
            $count = [];
            foreach ($result as $row) {
                if (isset($row[$groupBy])) {
                    $count[$row[$groupBy]] = $row["count"];
                }
            }
        } elseif (isset($result[0]['count'])) {
            $count = $result[0]['count'];
        }

        return $count;
    }

800
801
802
    /**
     * Stringify array of type of events for better sql query
     *
803
     * @param array $eventTypes Types of event
804
     *
805
     * @return array
806
807
808
809
     */
    protected function stringifyEventTypes($eventTypes)
    {
        $in = "";
810
        $inParams = [];
811
812
813
814
815
816
817
818
819
820
821
822
823
        foreach ($eventTypes as $key => $eventType) {
            $k = ":eventType" . $key;
            $in .= "$k,";
            $inParams[$k] = $eventType;
        }
        $in = rtrim($in, ",");

        return [
            'in' => $in,
            'inParams' => $inParams
        ];
    }

824
    /**
825
     * format a size
826
     *
827
     * @param float     $size       Size to format
828
     *
829
     * @return string               Formatted size
830
     */
831
    protected function formatSize($size, $formatType = true)
832
833
    {
        if ($formatType) {
834
            $size /= pow(1000, $this->sizeFilter);
835
        }
836
837
        if ($size != (integer)$size) {
            $size = number_format($size, 3, ".", " ");
838
        }
839
        return $size;
840
841
    }

Jerome Boucher's avatar
Jerome Boucher committed
842
    /**
843
844
845
846
847
     * Execute query
     *
     * @param string   $query                           Query to send
     * @param string   $secondary_parameters            Secondary parameters
     *
848
     * @return array                                    Results of query
849
     */
850
    public function executeQuery($query, $secondary_parameters = [])
851
852
    {
        $stmt = $this->pdo->prepare($query);
853
        $stmt->execute($secondary_parameters);
Jerome Boucher's avatar
Jerome Boucher committed
854
        $results = [];
855
856

        while ($result = $stmt->fetch(\PDO::FETCH_ASSOC)) {
857
            $result['sum'] = isset($result['sum']) ? $this->formatSize($result['sum']) : '0.000';
Jerome Boucher's avatar
Jerome Boucher committed
858
            $results[] = $result;
859
860
        }

Jerome Boucher's avatar
Jerome Boucher committed
861
        return $results;
862
    }
Jerome Boucher's avatar
Jerome Boucher committed
863
}