Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
0.96% covered (danger)
0.96%
2 / 208
8.33% covered (danger)
8.33%
2 / 24
CRAP
0.00% covered (danger)
0.00%
0 / 1
Proof
0.96% covered (danger)
0.96%
2 / 208
8.33% covered (danger)
8.33%
2 / 24
3102.41
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 / 12
0.00% covered (danger)
0.00%
0 / 1
6
 averageBalanceSheetProof
0.00% covered (danger)
0.00%
0 / 13
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)->executeQuery();
316
317        return $stmt->fetchFirstColumn();
318    }
319
320    public function averageBalanceSheetProof(array $collectivities = [])
321    {
322        $sql = 'SELECT AVG(a.rcount) FROM (
323            SELECT IF(COUNT(rp.id) > 0, 1, 0) as rcount
324            FROM user_collectivity uc
325            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 . '")
326            WHERE uc.active = 1';
327
328        if (!empty($collectivities)) {
329            $sql .= ' AND uc.id IN (';
330            $sql .= \implode(',', \array_map(function ($collectivity) {
331                return '\'' . $collectivity->getId() . '\'';
332            }, $collectivities));
333            $sql .= ') ';
334        }
335
336        $sql .= '
337            GROUP BY uc.id
338        ) a';
339
340        $stmt = $this->getManager()->getConnection()->prepare($sql)->executeQuery();
341
342        return $stmt->fetchFirstColumn();
343    }
344
345    public function count(array $criteria = [])
346    {
347        $qb = $this
348            ->createQueryBuilder()
349            ->select('count(o.id)')
350        ;
351
352        if (\array_key_exists('archive', $criteria)) {
353            $this->addArchivedClause($qb, $criteria['archive']);
354            unset($criteria['archive']);
355        }
356
357        if (isset($criteria['collectivity']) && $criteria['collectivity'] instanceof Collection) {
358            $qb->leftJoin('o.collectivity', 'collectivite');
359            $this->addInClauseCollectivities($qb, $criteria['collectivity']->toArray());
360            unset($criteria['collectivity']);
361        }
362
363        foreach ($criteria as $key => $value) {
364            $this->addWhereClause($qb, $key, $value);
365        }
366
367        return $qb
368            ->getQuery()
369            ->getSingleScalarResult()
370        ;
371    }
372
373    public function findPaginated($firstResult, $maxResults, $orderColumn, $orderDir, $searches, $criteria = [])
374    {
375        $qb = $this->createQueryBuilder();
376
377        if (\array_key_exists('archive', $criteria)) {
378            $this->addArchivedClause($qb, $criteria['archive']);
379            unset($criteria['archive']);
380        }
381
382        $qb
383            ->leftJoin('o.collectivity', 'collectivite')
384            ->leftJoin('o.service', 'service')
385            ->addSelect('collectivite');
386
387        if (isset($criteria['collectivity']) && $criteria['collectivity'] instanceof Collection) {
388            $this->addInClauseCollectivities($qb, $criteria['collectivity']->toArray());
389            unset($criteria['collectivity']);
390        }
391
392        foreach ($criteria as $key => $value) {
393            $this->addWhereClause($qb, $key, $value);
394        }
395
396        $this->addTableOrder($qb, $orderColumn, $orderDir);
397        $this->addTableWhere($qb, $searches);
398
399        $query = $qb->getQuery();
400        $query->setFirstResult($firstResult);
401        $query->setMaxResults($maxResults);
402
403        return new Paginator($query);
404    }
405
406    private function addTableOrder(QueryBuilder $queryBuilder, $orderColumn, $orderDir)
407    {
408        switch ($orderColumn) {
409            case 'nom':
410                $queryBuilder->addOrderBy('o.name', $orderDir);
411                break;
412            case 'collectivite':
413                $queryBuilder->addOrderBy('collectivite.name', $orderDir);
414                break;
415            case 'service':
416                $queryBuilder->addOrderBy('service.name', $orderDir);
417                break;
418            case 'type':
419                $queryBuilder->addSelect('(case
420                WHEN o.type = \'' . ProofTypeDictionary::TYPE_MESUREMENT . '\' THEN 1
421                WHEN o.type = \'' . ProofTypeDictionary::TYPE_CERTIFICATION . '\' THEN 2
422                WHEN o.type = \'' . ProofTypeDictionary::TYPE_OTHER . '\' THEN 3
423                WHEN o.type = \'' . ProofTypeDictionary::TYPE_BALANCE_SHEET . '\' THEN 4
424                WHEN o.type = \'' . ProofTypeDictionary::TYPE_IT_CHARTER . '\' THEN 5
425                WHEN o.type = \'' . ProofTypeDictionary::TYPE_CONTRACT . '\' THEN 6
426                WHEN o.type = \'' . ProofTypeDictionary::TYPE_DELIBERATION . '\' THEN 7
427                WHEN o.type = \'' . ProofTypeDictionary::TYPE_CONCERNED_PEOPLE_REQUEST . '\' THEN 8
428                WHEN o.type = \'' . ProofTypeDictionary::TYPE_POLICY_MANAGEMENT . '\' THEN 9
429                WHEN o.type = \'' . ProofTypeDictionary::TYPE_POLICY_PROTECTION . '\' THEN 10
430                WHEN o.type = \'' . ProofTypeDictionary::TYPE_SENSITIZATION . '\' THEN 11
431                ELSE 12 END) AS HIDDEN hidden_type')
432                    ->addOrderBy('hidden_type', $orderDir);
433                break;
434            case 'commentaire':
435                $queryBuilder->addOrderBy('o.comment', $orderDir);
436                break;
437            case 'date':
438                $queryBuilder->addOrderBy('o.createdAt', $orderDir);
439                break;
440            case 'updatedAt':
441                $queryBuilder->addOrderBy('o.updatedAt', $orderDir);
442                break;
443        }
444    }
445
446    private function addTableWhere(QueryBuilder $queryBuilder, $searches)
447    {
448        foreach ($searches as $columnName => $search) {
449            switch ($columnName) {
450                case 'nom':
451                    $this->addWhereClause($queryBuilder, 'name', '%' . $search . '%', 'LIKE');
452                    break;
453                case 'collectivite':
454                    $queryBuilder->andWhere('collectivite.name LIKE :nom')
455                        ->setParameter('nom', '%' . $search . '%');
456                    break;
457                case 'service':
458                    $queryBuilder->andWhere('service.name LIKE :sernom')
459                        ->setParameter('sernom', '%' . $search . '%');
460                    break;
461                case 'type':
462                    $this->addWhereClause($queryBuilder, 'type', $search);
463                    break;
464                case 'commentaire':
465                    $this->addWhereClause($queryBuilder, 'comment', '%' . $search . '%', 'LIKE');
466                    break;
467                case 'date':
468                    $queryBuilder->andWhere('o.createdAt BETWEEN :created_start_date AND :created_finish_date')
469                        ->setParameter('created_start_date', date_create_from_format('d/m/y', substr($search, 0, 8))->format('Y-m-d 00:00:00'))
470                        ->setParameter('created_finish_date', date_create_from_format('d/m/y', substr($search, 11, 8))->format('Y-m-d 23:59:59'));
471                    break;
472                case 'updatedAt':
473                    $queryBuilder->andWhere('o.updatedAt BETWEEN :updated_start_date AND :updated_finish_date')
474                        ->setParameter('updated_start_date', date_create_from_format('d/m/y', substr($search, 0, 8))->format('Y-m-d 00:00:00'))
475                        ->setParameter('updated_finish_date', date_create_from_format('d/m/y', substr($search, 11, 8))->format('Y-m-d 23:59:59'));
476                    break;
477                case 'updatedAt':
478                    $queryBuilder->andWhere('o.updatedAt LIKE :date')
479                        ->setParameter('date', date_create_from_format('d/m/Y', $search)->format('Y-m-d') . '%');
480                    break;
481            }
482        }
483    }
484}