Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
7.05% covered (danger)
7.05%
17 / 241
27.78% covered (danger)
27.78%
5 / 18
CRAP
0.00% covered (danger)
0.00%
0 / 1
Mesurement
7.05% covered (danger)
7.05%
17 / 241
27.78% covered (danger)
27.78%
5 / 18
3352.92
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 / 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
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()->getConnection()->prepare($sql);
179        $stmt->execute();
180
181        return $stmt->fetchColumn();
182    }
183
184    public function count(array $criteria = [])
185    {
186        $qb = $this
187            ->createQueryBuilder()
188            ->select('count(o.id)')
189        ;
190
191        if (isset($criteria['collectivity']) && $criteria['collectivity'] instanceof Collection) {
192            $qb->leftJoin('o.collectivity', 'collectivite');
193            $this->addInClauseCollectivities($qb, $criteria['collectivity']->toArray());
194            unset($criteria['collectivity']);
195        }
196
197        if (isset($criteria['planificationDate']) && 'null' === $criteria['planificationDate']) {
198            $qb->andWhere($qb->expr()->isNotNull('o.planificationDate'));
199            unset($criteria['planificationDate']);
200        }
201
202        foreach ($criteria as $key => $value) {
203            $this->addWhereClause($qb, $key, $value);
204        }
205
206        return $qb
207            ->getQuery()
208            ->getSingleScalarResult()
209        ;
210    }
211
212    public function findPaginated($firstResult, $maxResults, $orderColumn, $orderDir, $searches, $criteria = [])
213    {
214        $query = $this->createQueryBuilder();
215
216        $query
217            ->leftJoin('o.collectivity', 'collectivite')
218            ->leftJoin('o.service', 'service')
219            ->addSelect('collectivite');
220
221        if (isset($criteria['collectivity']) && $criteria['collectivity'] instanceof Collection) {
222            $this->addInClauseCollectivities($query, $criteria['collectivity']->toArray());
223            unset($criteria['collectivity']);
224        }
225
226        if (isset($criteria['planificationDate']) && 'null' === $criteria['planificationDate']) {
227            $query->andWhere($query->expr()->isNotNull('o.planificationDate'));
228            unset($criteria['planificationDate']);
229        }
230
231        foreach ($criteria as $key => $value) {
232            $this->addWhereClause($query, $key, $value);
233        }
234        $this->addTableWhere($query, $searches);
235        $this->addTableOrder($query, $orderColumn, $orderDir);
236
237        $query = $query->getQuery();
238        $query->setFirstResult($firstResult);
239        $query->setMaxResults($maxResults);
240
241        return new Paginator($query);
242    }
243
244    private function addTableWhere(QueryBuilder $queryBuilder, array $searches)
245    {
246        foreach ($searches as $columnName => $search) {
247            switch ($columnName) {
248                case 'nom':
249                    $this->addWhereClause($queryBuilder, 'name', '%' . $search . '%', 'LIKE');
250                    break;
251                case 'collectivite':
252                    $queryBuilder->andWhere('collectivite.name LIKE :collectivite')
253                        ->setParameter('collectivite', '%' . $search . '%');
254                    break;
255                case 'service':
256                    $queryBuilder->andWhere('service.name LIKE :service')
257                        ->setParameter('service', '%' . $search . '%');
258                    break;
259                case 'statut':
260                    $this->addWhereClause($queryBuilder, 'status', $search);
261                    break;
262                case 'cout':
263                    $this->addWhereClause($queryBuilder, 'cost', '%' . $search . '%', 'LIKE');
264                    break;
265                case 'charge':
266                    $this->addWhereClause($queryBuilder, 'charge', '%' . $search . '%', 'LIKE');
267                    break;
268                case 'priorite':
269                    $this->addWhereClause($queryBuilder, 'priority', $search);
270                    break;
271                case 'date_planification':
272                    if (is_string($search)) {
273                        $queryBuilder->andWhere('o.planificationDate BETWEEN :planned_start_date AND :planned_finish_date')
274                            ->setParameter('planned_start_date', date_create_from_format('d/m/y', substr($search, 0, 8))->format('Y-m-d 00:00:00'))
275                            ->setParameter('planned_finish_date', date_create_from_format('d/m/y', substr($search, 11, 8))->format('Y-m-d 23:59:59'));
276                    }
277                    break;
278                case 'responsable_action':
279                    $this->addWhereClause($queryBuilder, 'manager', '%' . $search . '%', 'LIKE');
280                    break;
281                case 'observation':
282                    $this->addWhereClause($queryBuilder, 'comment', '%' . $search . '%', 'LIKE');
283                    break;
284                case 'description':
285                    $this->addWhereClause($queryBuilder, $columnName, '%' . $search . '%', 'LIKE');
286                    break;
287                case 'createdAt':
288                    if (is_string($search)) {
289                        $queryBuilder->andWhere('o.createdAt BETWEEN :created_start_date AND :created_finish_date')
290                            ->setParameter('created_start_date', date_create_from_format('d/m/y', substr($search, 0, 8))->format('Y-m-d 00:00:00'))
291                            ->setParameter('created_finish_date', date_create_from_format('d/m/y', substr($search, 11, 8))->format('Y-m-d 23:59:59'));
292                    }
293                    break;
294                case 'updatedAt':
295                    if (is_string($search)) {
296                        $queryBuilder->andWhere('o.updatedAt BETWEEN :updated_start_date AND :updated_finish_date')
297                            ->setParameter('updated_start_date', date_create_from_format('d/m/y', substr($search, 0, 8))->format('Y-m-d 00:00:00'))
298                            ->setParameter('updated_finish_date', date_create_from_format('d/m/y', substr($search, 11, 8))->format('Y-m-d 23:59:59'));
299                    }
300                    break;
301            }
302        }
303    }
304
305    private function addTableOrder(QueryBuilder $queryBuilder, $orderColumn, $orderDir)
306    {
307        switch ($orderColumn) {
308            case 'nom':
309                $queryBuilder->addOrderBy('o.name', $orderDir);
310                break;
311            case 'statut':
312                $queryBuilder->addOrderBy('o.status', $orderDir);
313                break;
314            case 'cout':
315                $queryBuilder->addOrderBy('o.cost', $orderDir);
316                break;
317            case 'charge':
318                $queryBuilder->addOrderBy('o.charge', $orderDir);
319                break;
320            case 'description':
321                $queryBuilder->addOrderBy('o.description', $orderDir);
322                break;
323            case 'observation':
324                $queryBuilder->addOrderBy('o.comment', $orderDir);
325                break;
326            case 'collectivite':
327                $queryBuilder->addOrderBy('collectivite.name', $orderDir);
328                break;
329            case 'service':
330                $queryBuilder->addOrderBy('service.name', $orderDir);
331                break;
332            case 'priorite':
333                $queryBuilder->addSelect('(case
334                WHEN o.priority = \'' . MesurementPriorityDictionary::PRIORITY_LOW . '\' THEN 1
335                WHEN o.priority = \'' . MesurementPriorityDictionary::PRIORITY_NORMAL . '\' THEN 2
336                WHEN o.priority = \'' . MesurementPriorityDictionary::PRIORITY_HIGH . '\' THEN 3
337                ELSE 4 END) AS HIDDEN hidden_priority')
338                    ->addOrderBy('hidden_priority', $orderDir);
339                break;
340            case 'date_planification':
341                $queryBuilder->addOrderBy('o.planificationDate', $orderDir);
342                break;
343            case 'responsable_action':
344                $queryBuilder->addOrderBy('o.manager', $orderDir);
345                break;
346            case 'createdAt':
347                $queryBuilder->addOrderBy('o.createdAt', $orderDir);
348                break;
349            case 'updatedAt':
350                $queryBuilder->addOrderBy('o.updatedAt', $orderDir);
351                break;
352        }
353    }
354
355    public function findAllByActiveCollectivity(bool $active = true, ?User $user = null)
356    {
357        $qb = $this->createQueryBuilder();
358
359        $qb->leftJoin('o.collectivity', 'c')
360            ->andWhere($qb->expr()->eq('c.active', ':active'))
361            ->setParameter('active', $active)
362            ->addOrderBy('c.name')
363            ->addOrderBy('o.createdAt', 'DESC')
364        ;
365
366        if (null !== $user) {
367            $qb->leftJoin('c.userReferents', 'u')
368                ->andWhere('u.id = :user')
369                ->setParameter('user', $user);
370        }
371
372        return $qb
373            ->getQuery()
374            ->getResult()
375        ;
376    }
377
378    public function findAllByClonedFromCollectivity(Collectivity $collectivity)
379    {
380        $qb = $this->createQueryBuilder();
381
382        $qb->leftJoin('o.clonedFrom', 'c')
383            ->andWhere('c.collectivity = :collectivity')
384            ->setParameter('collectivity', $collectivity);
385
386        return $qb
387            ->getQuery()
388            ->getResult()
389        ;
390    }
391
392    public function getPlanifiedActionsDashBoard($limit = 1000, ?Collectivity $collectivity = null)
393    {
394        // Add old actions again.
395        // Fixes https://gitlab.adullact.net/soluris/madis/-/issues/529
396        // $date         = new \DateTime();
397        $queryBuilder = $this->createQueryBuilder();
398        $queryBuilder
399            ->where('o.status = :status')
400            ->setParameter('status', MesurementStatusDictionary::STATUS_NOT_APPLIED)
401            ->andWhere('o.planificationDate is not null')
402            ->orderBy('o.planificationDate', 'DESC')
403        ;
404
405        if ($collectivity) {
406            $queryBuilder
407                ->andWhere('o.collectivity = :collectivity')
408                ->setParameter('collectivity', $collectivity)
409            ;
410        }
411
412        $query = $queryBuilder
413            ->groupBy('o.id')
414            ->setMaxResults((int) $limit)
415            ->getQuery();
416
417        return $query->getResult();
418    }
419
420    public function resetClonedFromCollectivity(Collectivity $collectivity)
421    {
422        $qb = $this->createQueryBuilder();
423
424        $qb->leftJoin('o.clonedFrom', 'c')
425            ->andWhere('c.collectivity = :collectivity')
426            ->setParameter('collectivity', $collectivity);
427
428        $qb->update(['o.clonedFrom' => null]);
429    }
430}