Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
7.00% covered (danger)
7.00%
17 / 243
27.78% covered (danger)
27.78%
5 / 18
CRAP
0.00% covered (danger)
0.00%
0 / 1
Mesurement
7.00% covered (danger)
7.00%
17 / 243
27.78% covered (danger)
27.78%
5 / 18
3359.08
0.00% covered (danger)
0.00%
0 / 1
 __construct
100.00% covered (success)
100.00%
2 / 2
100.00% covered (success)
100.00%
1 / 1
1
 getModelClass
100.00% covered (success)
100.00%
1 / 1
100.00% covered (success)
100.00%
1 / 1
1
 addCollectivityClause
100.00% covered (success)
100.00%
4 / 4
100.00% covered (success)
100.00%
1 / 1
1
 addOrder
100.00% covered (success)
100.00%
3 / 3
100.00% covered (success)
100.00%
1 / 1
2
 findAllByCollectivity
100.00% covered (success)
100.00%
7 / 7
100.00% covered (success)
100.00%
1 / 1
1
 findBy
0.00% covered (danger)
0.00%
0 / 7
0.00% covered (danger)
0.00%
0 / 1
6
 findByPlanified
0.00% covered (danger)
0.00%
0 / 9
0.00% covered (danger)
0.00%
0 / 1
6
 countPlanifiedByCollectivity
0.00% covered (danger)
0.00%
0 / 10
0.00% covered (danger)
0.00%
0 / 1
2
 countAppliedByCollectivity
0.00% covered (danger)
0.00%
0 / 9
0.00% covered (danger)
0.00%
0 / 1
2
 planifiedAverageOnAllCollectivity
0.00% covered (danger)
0.00%
0 / 15
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
42
 findPaginated
0.00% covered (danger)
0.00%
0 / 19
0.00% covered (danger)
0.00%
0 / 1
42
 addTableWhere
0.00% covered (danger)
0.00%
0 / 51
0.00% covered (danger)
0.00%
0 / 1
342
 addTableOrder
0.00% covered (danger)
0.00%
0 / 44
0.00% covered (danger)
0.00%
0 / 1
210
 findAllByActiveCollectivity
0.00% covered (danger)
0.00%
0 / 15
0.00% covered (danger)
0.00%
0 / 1
6
 findAllByClonedFromCollectivity
0.00% covered (danger)
0.00%
0 / 8
0.00% covered (danger)
0.00%
0 / 1
2
 getPlanifiedActionsDashBoard
0.00% covered (danger)
0.00%
0 / 17
0.00% covered (danger)
0.00%
0 / 1
6
 resetClonedFromCollectivity
0.00% covered (danger)
0.00%
0 / 5
0.00% covered (danger)
0.00%
0 / 1
2
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\Doctrine\Repository\CRUDRepository;
27use App\Application\Traits\RepositoryUtils;
28use App\Domain\Registry\Dictionary\MesurementPriorityDictionary;
29use App\Domain\Registry\Dictionary\MesurementStatusDictionary;
30use App\Domain\Registry\Model;
31use App\Domain\Registry\Repository;
32use App\Domain\User\Model\Collectivity;
33use App\Domain\User\Model\User;
34use Doctrine\Common\Collections\Collection;
35use Doctrine\ORM\QueryBuilder;
36use Doctrine\ORM\Tools\Pagination\Paginator;
37use Doctrine\Persistence\ManagerRegistry;
38use Symfony\Component\Security\Core\Security;
39
40class Mesurement extends CRUDRepository implements Repository\Mesurement
41{
42    use RepositoryUtils;
43
44    /**
45     * @var Security
46     */
47    private $security;
48
49    public function __construct(ManagerRegistry $registry, Security $security)
50    {
51        parent::__construct($registry);
52        $this->security = $security;
53    }
54
55    protected function getModelClass(): string
56    {
57        return Model\Mesurement::class;
58    }
59
60    /**
61     * Add a collectivity appartenance clause.
62     */
63    protected function addCollectivityClause(QueryBuilder $qb, Collectivity $collectivity): QueryBuilder
64    {
65        return $qb
66            ->andWhere('o.collectivity = :collectivity')
67            ->setParameter('collectivity', $collectivity)
68        ;
69    }
70
71    /**
72     * Add an order to query.
73     */
74    protected function addOrder(QueryBuilder $qb, array $order = []): QueryBuilder
75    {
76        foreach ($order as $key => $dir) {
77            $qb->addOrderBy("o.{$key}", $dir);
78        }
79
80        return $qb;
81    }
82
83    public function findAllByCollectivity(Collectivity $collectivity, array $order = [])
84    {
85        $qb = $this->createQueryBuilder();
86
87        $this->addCollectivityClause($qb, $collectivity);
88        $this->addOrder($qb, $order);
89
90        return $qb
91            ->getQuery()
92            ->getResult()
93        ;
94    }
95
96    public function findBy(array $criteria = [])
97    {
98        $qb = $this->createQueryBuilder();
99
100        foreach ($criteria as $key => $value) {
101            $this->addWhereClause($qb, $key, $value);
102        }
103
104        return $qb
105            ->getQuery()
106            ->getResult()
107        ;
108    }
109
110    public function findByPlanified(array $criteria = [])
111    {
112        $qb = $this->createQueryBuilder();
113
114        foreach ($criteria as $key => $value) {
115            $this->addWhereClause($qb, $key, $value);
116        }
117        $qb->andWhere('o.planificationDate is not null');
118
119        $qb->orderBy('o.planificationDate', 'ASC');
120
121        return $qb
122            ->getQuery()
123            ->getResult()
124        ;
125    }
126
127    public function countPlanifiedByCollectivity(Collectivity $collectivity)
128    {
129        $qb = $this->createQueryBuilder();
130
131        $qb->select('COUNT(o.id)');
132        $qb->andWhere($qb->expr()->isNotNull('o.planificationDate'));
133        $qb->andWhere($qb->expr()->eq('o.collectivity', ':collectivity'));
134        $qb->andWhere($qb->expr()->neq('o.status', ':status'));
135        $qb->setParameters([
136            'status'       => MesurementStatusDictionary::STATUS_APPLIED,
137            'collectivity' => $collectivity,
138        ]);
139
140        return $qb->getQuery()->getSingleScalarResult();
141    }
142
143    public function countAppliedByCollectivity(Collectivity $collectivity)
144    {
145        $qb = $this->createQueryBuilder();
146
147        $qb->select('COUNT(o.id)');
148        $qb->andWhere($qb->expr()->eq('o.collectivity', ':collectivity'));
149        $qb->andWhere($qb->expr()->eq('o.status', ':status'));
150        $qb->setParameters([
151            'status'       => MesurementStatusDictionary::STATUS_APPLIED,
152            'collectivity' => $collectivity,
153        ]);
154
155        return $qb->getQuery()->getSingleScalarResult();
156    }
157
158    public function planifiedAverageOnAllCollectivity($collectivities)
159    {
160        $sql = 'SELECT AVG(a.rcount) FROM (
161            SELECT COUNT(rm.id) as rcount
162            FROM user_collectivity uc
163            LEFT OUTER JOIN registry_mesurement rm ON (uc.id = rm.collectivity_id AND rm.planification_date is not null
164            AND rm.status = "applied" )
165            WHERE uc.active = 1';
166
167        if (!empty($collectivities)) {
168            $sql .= ' AND uc.id IN (';
169            $sql .= \implode(',', \array_map(function ($collectivity) {
170                return '\'' . $collectivity->getId() . '\'';
171            }, $collectivities));
172            $sql .= ') ';
173        }
174
175        $sql .= ' GROUP BY uc.id
176        ) a';
177
178        $stmt = $this->getManager()
179            ->getConnection()
180            ->prepare($sql)
181            ->executeQuery();
182
183        return $stmt->fetchFirstColumn();
184    }
185
186    public function count(array $criteria = [])
187    {
188        $qb = $this
189            ->createQueryBuilder()
190            ->select('count(o.id)')
191        ;
192
193        if (isset($criteria['collectivity']) && $criteria['collectivity'] instanceof Collection) {
194            $qb->leftJoin('o.collectivity', 'collectivite');
195            $this->addInClauseCollectivities($qb, $criteria['collectivity']->toArray());
196            unset($criteria['collectivity']);
197        }
198
199        if (isset($criteria['planificationDate']) && 'null' === $criteria['planificationDate']) {
200            $qb->andWhere($qb->expr()->isNotNull('o.planificationDate'));
201            unset($criteria['planificationDate']);
202        }
203
204        foreach ($criteria as $key => $value) {
205            $this->addWhereClause($qb, $key, $value);
206        }
207
208        return $qb
209            ->getQuery()
210            ->getSingleScalarResult()
211        ;
212    }
213
214    public function findPaginated($firstResult, $maxResults, $orderColumn, $orderDir, $searches, $criteria = [])
215    {
216        $query = $this->createQueryBuilder();
217
218        $query
219            ->leftJoin('o.collectivity', 'collectivite')
220            ->leftJoin('o.service', 'service')
221            ->addSelect('collectivite');
222
223        if (isset($criteria['collectivity']) && $criteria['collectivity'] instanceof Collection) {
224            $this->addInClauseCollectivities($query, $criteria['collectivity']->toArray());
225            unset($criteria['collectivity']);
226        }
227
228        if (isset($criteria['planificationDate']) && 'null' === $criteria['planificationDate']) {
229            $query->andWhere($query->expr()->isNotNull('o.planificationDate'));
230            unset($criteria['planificationDate']);
231        }
232
233        foreach ($criteria as $key => $value) {
234            $this->addWhereClause($query, $key, $value);
235        }
236        $this->addTableWhere($query, $searches);
237        $this->addTableOrder($query, $orderColumn, $orderDir);
238
239        $query = $query->getQuery();
240        $query->setFirstResult($firstResult);
241        $query->setMaxResults($maxResults);
242
243        return new Paginator($query);
244    }
245
246    private function addTableWhere(QueryBuilder $queryBuilder, array $searches)
247    {
248        foreach ($searches as $columnName => $search) {
249            switch ($columnName) {
250                case 'nom':
251                    $this->addWhereClause($queryBuilder, 'name', '%' . $search . '%', 'LIKE');
252                    break;
253                case 'collectivite':
254                    $queryBuilder->andWhere('collectivite.name LIKE :collectivite')
255                        ->setParameter('collectivite', '%' . $search . '%');
256                    break;
257                case 'service':
258                    $queryBuilder->andWhere('service.name LIKE :service')
259                        ->setParameter('service', '%' . $search . '%');
260                    break;
261                case 'statut':
262                    $this->addWhereClause($queryBuilder, 'status', $search);
263                    break;
264                case 'cout':
265                    $this->addWhereClause($queryBuilder, 'cost', '%' . $search . '%', 'LIKE');
266                    break;
267                case 'charge':
268                    $this->addWhereClause($queryBuilder, 'charge', '%' . $search . '%', 'LIKE');
269                    break;
270                case 'priorite':
271                    $this->addWhereClause($queryBuilder, 'priority', $search);
272                    break;
273                case 'date_planification':
274                    if (is_string($search)) {
275                        $queryBuilder->andWhere('o.planificationDate BETWEEN :planned_start_date AND :planned_finish_date')
276                            ->setParameter('planned_start_date', date_create_from_format('d/m/y', substr($search, 0, 8))->format('Y-m-d 00:00:00'))
277                            ->setParameter('planned_finish_date', date_create_from_format('d/m/y', substr($search, 11, 8))->format('Y-m-d 23:59:59'));
278                    }
279                    break;
280                case 'responsable_action':
281                    $this->addWhereClause($queryBuilder, 'manager', '%' . $search . '%', 'LIKE');
282                    break;
283                case 'observation':
284                    $this->addWhereClause($queryBuilder, 'comment', '%' . $search . '%', 'LIKE');
285                    break;
286                case 'description':
287                    $this->addWhereClause($queryBuilder, $columnName, '%' . $search . '%', 'LIKE');
288                    break;
289                case 'createdAt':
290                    if (is_string($search)) {
291                        $queryBuilder->andWhere('o.createdAt BETWEEN :created_start_date AND :created_finish_date')
292                            ->setParameter('created_start_date', date_create_from_format('d/m/y', substr($search, 0, 8))->format('Y-m-d 00:00:00'))
293                            ->setParameter('created_finish_date', date_create_from_format('d/m/y', substr($search, 11, 8))->format('Y-m-d 23:59:59'));
294                    }
295                    break;
296                case 'updatedAt':
297                    if (is_string($search)) {
298                        $queryBuilder->andWhere('o.updatedAt BETWEEN :updated_start_date AND :updated_finish_date')
299                            ->setParameter('updated_start_date', date_create_from_format('d/m/y', substr($search, 0, 8))->format('Y-m-d 00:00:00'))
300                            ->setParameter('updated_finish_date', date_create_from_format('d/m/y', substr($search, 11, 8))->format('Y-m-d 23:59:59'));
301                    }
302                    break;
303            }
304        }
305    }
306
307    private function addTableOrder(QueryBuilder $queryBuilder, $orderColumn, $orderDir)
308    {
309        switch ($orderColumn) {
310            case 'nom':
311                $queryBuilder->addOrderBy('o.name', $orderDir);
312                break;
313            case 'statut':
314                $queryBuilder->addOrderBy('o.status', $orderDir);
315                break;
316            case 'cout':
317                $queryBuilder->addOrderBy('o.cost', $orderDir);
318                break;
319            case 'charge':
320                $queryBuilder->addOrderBy('o.charge', $orderDir);
321                break;
322            case 'description':
323                $queryBuilder->addOrderBy('o.description', $orderDir);
324                break;
325            case 'observation':
326                $queryBuilder->addOrderBy('o.comment', $orderDir);
327                break;
328            case 'collectivite':
329                $queryBuilder->addOrderBy('collectivite.name', $orderDir);
330                break;
331            case 'service':
332                $queryBuilder->addOrderBy('service.name', $orderDir);
333                break;
334            case 'priorite':
335                $queryBuilder->addSelect('(case
336                WHEN o.priority = \'' . MesurementPriorityDictionary::PRIORITY_LOW . '\' THEN 1
337                WHEN o.priority = \'' . MesurementPriorityDictionary::PRIORITY_NORMAL . '\' THEN 2
338                WHEN o.priority = \'' . MesurementPriorityDictionary::PRIORITY_HIGH . '\' THEN 3
339                ELSE 4 END) AS HIDDEN hidden_priority')
340                    ->addOrderBy('hidden_priority', $orderDir);
341                break;
342            case 'date_planification':
343                $queryBuilder->addOrderBy('o.planificationDate', $orderDir);
344                break;
345            case 'responsable_action':
346                $queryBuilder->addOrderBy('o.manager', $orderDir);
347                break;
348            case 'createdAt':
349                $queryBuilder->addOrderBy('o.createdAt', $orderDir);
350                break;
351            case 'updatedAt':
352                $queryBuilder->addOrderBy('o.updatedAt', $orderDir);
353                break;
354        }
355    }
356
357    public function findAllByActiveCollectivity(bool $active = true, ?User $user = null)
358    {
359        $qb = $this->createQueryBuilder();
360
361        $qb->leftJoin('o.collectivity', 'c')
362            ->andWhere($qb->expr()->eq('c.active', ':active'))
363            ->setParameter('active', $active)
364            ->addOrderBy('c.name')
365            ->addOrderBy('o.createdAt', 'DESC')
366        ;
367
368        if (null !== $user) {
369            $qb->leftJoin('c.userReferents', 'u')
370                ->andWhere('u.id = :user')
371                ->setParameter('user', $user);
372        }
373
374        return $qb
375            ->getQuery()
376            ->getResult()
377        ;
378    }
379
380    public function findAllByClonedFromCollectivity(Collectivity $collectivity)
381    {
382        $qb = $this->createQueryBuilder();
383
384        $qb->leftJoin('o.clonedFrom', 'c')
385            ->andWhere('c.collectivity = :collectivity')
386            ->setParameter('collectivity', $collectivity);
387
388        return $qb
389            ->getQuery()
390            ->getResult()
391        ;
392    }
393
394    public function getPlanifiedActionsDashBoard($limit = 1000, ?Collectivity $collectivity = null)
395    {
396        // Add old actions again.
397        // Fixes https://gitlab.adullact.net/soluris/madis/-/issues/529
398        // $date         = new \DateTime();
399        $queryBuilder = $this->createQueryBuilder();
400        $queryBuilder
401            ->where('o.status = :status')
402            ->setParameter('status', MesurementStatusDictionary::STATUS_NOT_APPLIED)
403            ->andWhere('o.planificationDate is not null')
404            ->orderBy('o.planificationDate', 'DESC')
405        ;
406
407        if ($collectivity) {
408            $queryBuilder
409                ->andWhere('o.collectivity = :collectivity')
410                ->setParameter('collectivity', $collectivity)
411            ;
412        }
413
414        $query = $queryBuilder
415            ->groupBy('o.id')
416            ->setMaxResults((int) $limit)
417            ->getQuery();
418
419        return $query->getResult();
420    }
421
422    public function resetClonedFromCollectivity(Collectivity $collectivity)
423    {
424        $qb = $this->createQueryBuilder();
425
426        $qb->leftJoin('o.clonedFrom', 'c')
427            ->andWhere('c.collectivity = :collectivity')
428            ->setParameter('collectivity', $collectivity);
429
430        $qb->update(['o.clonedFrom' => null]);
431    }
432}