Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
0.95% covered (danger)
0.95%
2 / 210
8.33% covered (danger)
8.33%
2 / 24
CRAP
0.00% covered (danger)
0.00%
0 / 1
Proof
0.95% covered (danger)
0.95%
2 / 210
8.33% covered (danger)
8.33%
2 / 24
3103.25
0.00% covered (danger)
0.00%
0 / 1
 __construct
100.00% covered (success)
100.00%
1 / 1
100.00% covered (success)
100.00%
1 / 1
1
 getManager
0.00% covered (danger)
0.00%
0 / 4
0.00% covered (danger)
0.00%
0 / 1
6
 createQueryBuilder
0.00% covered (danger)
0.00%
0 / 5
0.00% covered (danger)
0.00%
0 / 1
2
 insert
0.00% covered (danger)
0.00%
0 / 2
0.00% covered (danger)
0.00%
0 / 1
2
 update
0.00% covered (danger)
0.00%
0 / 1
0.00% covered (danger)
0.00%
0 / 1
2
 create
0.00% covered (danger)
0.00%
0 / 2
0.00% covered (danger)
0.00%
0 / 1
2
 remove
0.00% covered (danger)
0.00%
0 / 2
0.00% covered (danger)
0.00%
0 / 1
2
 findAll
0.00% covered (danger)
0.00%
0 / 8
0.00% covered (danger)
0.00%
0 / 1
6
 findOneById
0.00% covered (danger)
0.00%
0 / 5
0.00% covered (danger)
0.00%
0 / 1
2
 getModelClass
100.00% covered (success)
100.00%
1 / 1
100.00% covered (success)
100.00%
1 / 1
1
 addArchivedClause
0.00% covered (danger)
0.00%
0 / 3
0.00% covered (danger)
0.00%
0 / 1
6
 addCollectivityClause
0.00% covered (danger)
0.00%
0 / 4
0.00% covered (danger)
0.00%
0 / 1
2
 addOrder
0.00% covered (danger)
0.00%
0 / 3
0.00% covered (danger)
0.00%
0 / 1
6
 findAllByCollectivity
0.00% covered (danger)
0.00%
0 / 10
0.00% covered (danger)
0.00%
0 / 1
6
 findBy
0.00% covered (danger)
0.00%
0 / 7
0.00% covered (danger)
0.00%
0 / 1
6
 findAllArchivedByCollectivity
0.00% covered (danger)
0.00%
0 / 8
0.00% covered (danger)
0.00%
0 / 1
2
 findOneOrNullByTypeAndCollectivity
0.00% covered (danger)
0.00%
0 / 10
0.00% covered (danger)
0.00%
0 / 1
2
 countAllByCollectivity
0.00% covered (danger)
0.00%
0 / 5
0.00% covered (danger)
0.00%
0 / 1
2
 averageProofFiled
0.00% covered (danger)
0.00%
0 / 13
0.00% covered (danger)
0.00%
0 / 1
6
 averageBalanceSheetProof
0.00% covered (danger)
0.00%
0 / 14
0.00% covered (danger)
0.00%
0 / 1
6
 count
0.00% covered (danger)
0.00%
0 / 17
0.00% covered (danger)
0.00%
0 / 1
30
 findPaginated
0.00% covered (danger)
0.00%
0 / 19
0.00% covered (danger)
0.00%
0 / 1
30
 addTableOrder
0.00% covered (danger)
0.00%
0 / 34
0.00% covered (danger)
0.00%
0 / 1
72
 addTableWhere
0.00% covered (danger)
0.00%
0 / 32
0.00% covered (danger)
0.00%
0 / 1
110
1<?php
2
3/**
4 * This file is part of the MADIS - RGPD Management application.
5 *
6 * @copyright Copyright (c) 2018-2019 Soluris - Solutions Numériques Territoriales Innovantes
7 *
8 * This program is free software: you can redistribute it and/or modify
9 * it under the terms of the GNU Affero General Public License as published by
10 * the Free Software Foundation, either version 3 of the License, or
11 * (at your option) any later version.
12 *
13 * This program is distributed in the hope that it will be useful,
14 * but WITHOUT ANY WARRANTY; without even the implied warranty of
15 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
16 * GNU Affero General Public License for more details.
17 *
18 * You should have received a copy of the GNU Affero General Public License
19 * along with this program. If not, see <https://www.gnu.org/licenses/>.
20 */
21
22declare(strict_types=1);
23
24namespace App\Infrastructure\ORM\Registry\Repository;
25
26use App\Application\Traits\RepositoryUtils;
27use App\Domain\Registry\Dictionary\ProofTypeDictionary;
28use App\Domain\Registry\Model;
29use App\Domain\Registry\Repository;
30use App\Domain\User\Model\Collectivity;
31use Doctrine\Common\Collections\Collection;
32use Doctrine\ORM\EntityManagerInterface;
33use Doctrine\ORM\QueryBuilder;
34use Doctrine\ORM\Tools\Pagination\Paginator;
35use Doctrine\Persistence\ManagerRegistry;
36
37class Proof implements Repository\Proof
38{
39    use RepositoryUtils;
40
41    /**
42     * @var ManagerRegistry
43     */
44    protected $registry;
45
46    /**
47     * Proof constructor.
48     */
49    public function __construct(ManagerRegistry $registry)
50    {
51        $this->registry = $registry;
52    }
53
54    /**
55     * Get the registry manager
56     * Since we use Doctrine, we expect to get EntityManagerInterface.
57     *
58     * @throws \Exception
59     */
60    protected function getManager(): EntityManagerInterface
61    {
62        $manager = $this->registry->getManager();
63
64        if (!$manager instanceof EntityManagerInterface) {
65            throw new \Exception('Registry Manager must be an instance of EntityManagerInterface #PHPStan');
66        }
67
68        return $manager;
69    }
70
71    /**
72     * Create the base of QueryBuilder to use for repository calls.
73     *
74     * @throws \Exception
75     *
76     * @return QueryBuilder
77     */
78    protected function createQueryBuilder()
79    {
80        return $this->getManager()
81            ->createQueryBuilder()
82            ->select('o')
83            ->from($this->getModelClass(), 'o')
84        ;
85    }
86
87    /**
88     * Insert an object.
89     *
90     * @throws \Exception
91     */
92    public function insert($object): void
93    {
94        $this->getManager()->persist($object);
95        $this->getManager()->flush();
96    }
97
98    /**
99     * Update an object.
100     *
101     * @throws \Exception
102     */
103    public function update($object): void
104    {
105        $this->getManager()->flush();
106    }
107
108    /**
109     * Create an object.
110     */
111    public function create()
112    {
113        $class = $this->getModelClass();
114
115        return new $class();
116    }
117
118    /**
119     * Remove an object.
120     *
121     * @throws \Exception
122     */
123    public function remove($object): void
124    {
125        $this->getManager()->remove($object);
126        $this->getManager()->flush();
127    }
128
129    /**
130     * Find all data.
131     *
132     * @throws \Exception
133     */
134    public function findAll(bool $deleted = false): array
135    {
136        $qb = $this->createQueryBuilder();
137
138        if ($deleted) {
139            $qb->andWhere('o.deletedAt is not null');
140        } else {
141            $qb->andWhere('o.deletedAt is null');
142        }
143
144        return $qb
145            ->getQuery()
146            ->getResult()
147        ;
148    }
149
150    /**
151     * Get an object by ID.
152     *
153     * @param string $id The ID to find
154     *
155     * @return object|null
156     */
157    public function findOneById(string $id)
158    {
159        return $this->registry
160            ->getManager()
161            ->getRepository($this->getModelClass())
162            ->find($id)
163        ;
164    }
165
166    /**
167     * Get the model class name.
168     */
169    protected function getModelClass(): string
170    {
171        return Model\Proof::class;
172    }
173
174    /**
175     * Add archive clause to query.
176     */
177    protected function addArchivedClause(QueryBuilder $qb, bool $archived = false): QueryBuilder
178    {
179        // Get not archived
180        if (!$archived) {
181            return $qb->andWhere('o.deletedAt is null');
182        }
183
184        // Get archived
185        return $qb->andWhere('o.deletedAt is not null');
186    }
187
188    /**
189     * Add collectivity clause to query.
190     */
191    protected function addCollectivityClause(QueryBuilder $qb, Collectivity $collectivity): QueryBuilder
192    {
193        return $qb
194            ->andWhere('o.collectivity = :collectivity')
195            ->setParameter('collectivity', $collectivity)
196        ;
197    }
198
199    /**
200     * Add order clause to query.
201     */
202    protected function addOrder(QueryBuilder $qb, array $order = []): QueryBuilder
203    {
204        foreach ($order as $key => $dir) {
205            $qb->addOrderBy("o.{$key}", $dir);
206        }
207
208        return $qb;
209    }
210
211    /**
212     * @throws \Exception
213     */
214    public function findAllByCollectivity(Collectivity $collectivity, bool $deleted = false, array $order = [])
215    {
216        $qb = $this->createQueryBuilder();
217
218        $this->addCollectivityClause($qb, $collectivity);
219
220        if ($deleted) {
221            $qb->andWhere('o.deletedAt is not null');
222        } else {
223            $qb->andWhere('o.deletedAt is null');
224        }
225
226        $this->addOrder($qb, $order);
227
228        return $qb
229            ->getQuery()
230            ->getResult()
231        ;
232    }
233
234    /**
235     * @throws \Exception
236     */
237    public function findBy(array $criteria = [])
238    {
239        $qb = $this->createQueryBuilder();
240
241        foreach ($criteria as $key => $value) {
242            $this->addWhereClause($qb, $key, $value);
243        }
244
245        return $qb
246            ->getQuery()
247            ->getResult()
248        ;
249    }
250
251    /**
252     * @throws \Exception
253     */
254    public function findAllArchivedByCollectivity(Collectivity $collectivity, bool $archived = false, array $order = [])
255    {
256        $qb = $this->createQueryBuilder();
257
258        $this->addCollectivityClause($qb, $collectivity);
259        $this->addArchivedClause($qb, $archived);
260        $this->addOrder($qb, $order);
261
262        return $qb
263            ->getQuery()
264            ->getResult()
265        ;
266    }
267
268    public function findOneOrNullByTypeAndCollectivity(string $type, Collectivity $collectivity): ?Model\Proof
269    {
270        $qb = $this->createQueryBuilder();
271
272        $qb->andWhere($qb->expr()->eq('o.type', ':type'));
273        $qb->andWhere($qb->expr()->eq('o.collectivity', ':collectivity'));
274        $qb->setParameters([
275            'type'         => $type,
276            'collectivity' => $collectivity,
277        ]);
278        $qb->addOrderBy('o.createdAt', 'DESC');
279        $qb->setMaxResults(1);
280
281        return $qb->getQuery()->getOneOrNullResult();
282    }
283
284    public function countAllByCollectivity(Collectivity $collectivity)
285    {
286        $qb = $this->createQueryBuilder();
287
288        $qb->select('COUNT(o.id)');
289        $qb->andWhere($qb->expr()->eq('o.collectivity', ':collectivity'));
290        $qb->setParameter('collectivity', $collectivity);
291
292        return $qb->getQuery()->getSingleScalarResult();
293    }
294
295    public function averageProofFiled(array $collectivities = [])
296    {
297        $sql = 'SELECT AVG(a.rcount) FROM (
298            SELECT COUNT(rp.id) as rcount
299            FROM user_collectivity uc
300            LEFT OUTER JOIN registry_proof rp ON uc.id = rp.collectivity_id
301            WHERE uc.active = 1';
302
303        if (!empty($collectivities)) {
304            $sql .= ' AND uc.id IN (';
305            $sql .= \implode(',', \array_map(function ($collectivity) {
306                return '\'' . $collectivity->getId() . '\'';
307            }, $collectivities));
308            $sql .= ') ';
309        }
310
311        $sql .= '
312            GROUP BY uc.id
313        ) a';
314
315        $stmt = $this->getManager()->getConnection()->prepare($sql);
316        $stmt->execute();
317
318        return $stmt->fetchColumn();
319    }
320
321    public function averageBalanceSheetProof(array $collectivities = [])
322    {
323        $sql = 'SELECT AVG(a.rcount) FROM (
324            SELECT IF(COUNT(rp.id) > 0, 1, 0) as rcount
325            FROM user_collectivity uc
326            LEFT OUTER JOIN registry_proof rp ON (uc.id = rp.collectivity_id AND rp.created_at >= NOW() - INTERVAL 1 YEAR AND rp.type = "' . ProofTypeDictionary::TYPE_BALANCE_SHEET . '")
327            WHERE uc.active = 1';
328
329        if (!empty($collectivities)) {
330            $sql .= ' AND uc.id IN (';
331            $sql .= \implode(',', \array_map(function ($collectivity) {
332                return '\'' . $collectivity->getId() . '\'';
333            }, $collectivities));
334            $sql .= ') ';
335        }
336
337        $sql .= '
338            GROUP BY uc.id
339        ) a';
340
341        $stmt = $this->getManager()->getConnection()->prepare($sql);
342        $stmt->execute();
343
344        return $stmt->fetchColumn();
345    }
346
347    public function count(array $criteria = [])
348    {
349        $qb = $this
350            ->createQueryBuilder()
351            ->select('count(o.id)')
352        ;
353
354        if (\array_key_exists('archive', $criteria)) {
355            $this->addArchivedClause($qb, $criteria['archive']);
356            unset($criteria['archive']);
357        }
358
359        if (isset($criteria['collectivity']) && $criteria['collectivity'] instanceof Collection) {
360            $qb->leftJoin('o.collectivity', 'collectivite');
361            $this->addInClauseCollectivities($qb, $criteria['collectivity']->toArray());
362            unset($criteria['collectivity']);
363        }
364
365        foreach ($criteria as $key => $value) {
366            $this->addWhereClause($qb, $key, $value);
367        }
368
369        return $qb
370            ->getQuery()
371            ->getSingleScalarResult()
372        ;
373    }
374
375    public function findPaginated($firstResult, $maxResults, $orderColumn, $orderDir, $searches, $criteria = [])
376    {
377        $qb = $this->createQueryBuilder();
378
379        if (\array_key_exists('archive', $criteria)) {
380            $this->addArchivedClause($qb, $criteria['archive']);
381            unset($criteria['archive']);
382        }
383
384        $qb
385            ->leftJoin('o.collectivity', 'collectivite')
386            ->leftJoin('o.service', 'service')
387            ->addSelect('collectivite');
388
389        if (isset($criteria['collectivity']) && $criteria['collectivity'] instanceof Collection) {
390            $this->addInClauseCollectivities($qb, $criteria['collectivity']->toArray());
391            unset($criteria['collectivity']);
392        }
393
394        foreach ($criteria as $key => $value) {
395            $this->addWhereClause($qb, $key, $value);
396        }
397
398        $this->addTableOrder($qb, $orderColumn, $orderDir);
399        $this->addTableWhere($qb, $searches);
400
401        $query = $qb->getQuery();
402        $query->setFirstResult($firstResult);
403        $query->setMaxResults($maxResults);
404
405        return new Paginator($query);
406    }
407
408    private function addTableOrder(QueryBuilder $queryBuilder, $orderColumn, $orderDir)
409    {
410        switch ($orderColumn) {
411            case 'nom':
412                $queryBuilder->addOrderBy('o.name', $orderDir);
413                break;
414            case 'collectivite':
415                $queryBuilder->addOrderBy('collectivite.name', $orderDir);
416                break;
417            case 'service':
418                $queryBuilder->addOrderBy('service.name', $orderDir);
419                break;
420            case 'type':
421                $queryBuilder->addSelect('(case
422                WHEN o.type = \'' . ProofTypeDictionary::TYPE_MESUREMENT . '\' THEN 1
423                WHEN o.type = \'' . ProofTypeDictionary::TYPE_CERTIFICATION . '\' THEN 2
424                WHEN o.type = \'' . ProofTypeDictionary::TYPE_OTHER . '\' THEN 3
425                WHEN o.type = \'' . ProofTypeDictionary::TYPE_BALANCE_SHEET . '\' THEN 4
426                WHEN o.type = \'' . ProofTypeDictionary::TYPE_IT_CHARTER . '\' THEN 5
427                WHEN o.type = \'' . ProofTypeDictionary::TYPE_CONTRACT . '\' THEN 6
428                WHEN o.type = \'' . ProofTypeDictionary::TYPE_DELIBERATION . '\' THEN 7
429                WHEN o.type = \'' . ProofTypeDictionary::TYPE_CONCERNED_PEOPLE_REQUEST . '\' THEN 8
430                WHEN o.type = \'' . ProofTypeDictionary::TYPE_POLICY_MANAGEMENT . '\' THEN 9
431                WHEN o.type = \'' . ProofTypeDictionary::TYPE_POLICY_PROTECTION . '\' THEN 10
432                WHEN o.type = \'' . ProofTypeDictionary::TYPE_SENSITIZATION . '\' THEN 11
433                ELSE 12 END) AS HIDDEN hidden_type')
434                    ->addOrderBy('hidden_type', $orderDir);
435                break;
436            case 'commentaire':
437                $queryBuilder->addOrderBy('o.comment', $orderDir);
438                break;
439            case 'date':
440                $queryBuilder->addOrderBy('o.createdAt', $orderDir);
441                break;
442            case 'updatedAt':
443                $queryBuilder->addOrderBy('o.updatedAt', $orderDir);
444                break;
445        }
446    }
447
448    private function addTableWhere(QueryBuilder $queryBuilder, $searches)
449    {
450        foreach ($searches as $columnName => $search) {
451            switch ($columnName) {
452                case 'nom':
453                    $this->addWhereClause($queryBuilder, 'name', '%' . $search . '%', 'LIKE');
454                    break;
455                case 'collectivite':
456                    $queryBuilder->andWhere('collectivite.name LIKE :nom')
457                        ->setParameter('nom', '%' . $search . '%');
458                    break;
459                case 'service':
460                    $queryBuilder->andWhere('service.name LIKE :sernom')
461                        ->setParameter('sernom', '%' . $search . '%');
462                    break;
463                case 'type':
464                    $this->addWhereClause($queryBuilder, 'type', $search);
465                    break;
466                case 'commentaire':
467                    $this->addWhereClause($queryBuilder, 'comment', '%' . $search . '%', 'LIKE');
468                    break;
469                case 'date':
470                    $queryBuilder->andWhere('o.createdAt BETWEEN :created_start_date AND :created_finish_date')
471                        ->setParameter('created_start_date', date_create_from_format('d/m/y', substr($search, 0, 8))->format('Y-m-d 00:00:00'))
472                        ->setParameter('created_finish_date', date_create_from_format('d/m/y', substr($search, 11, 8))->format('Y-m-d 23:59:59'));
473                    break;
474                case 'updatedAt':
475                    $queryBuilder->andWhere('o.updatedAt BETWEEN :updated_start_date AND :updated_finish_date')
476                        ->setParameter('updated_start_date', date_create_from_format('d/m/y', substr($search, 0, 8))->format('Y-m-d 00:00:00'))
477                        ->setParameter('updated_finish_date', date_create_from_format('d/m/y', substr($search, 11, 8))->format('Y-m-d 23:59:59'));
478                    break;
479                case 'updatedAt':
480                    $queryBuilder->andWhere('o.updatedAt LIKE :date')
481                        ->setParameter('date', date_create_from_format('d/m/Y', $search)->format('Y-m-d') . '%');
482                    break;
483            }
484        }
485    }
486}