Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
13.02% covered (danger)
13.02%
53 / 407
36.84% covered (danger)
36.84%
7 / 19
CRAP
0.00% covered (danger)
0.00%
0 / 1
Treatment
13.02% covered (danger)
13.02%
53 / 407
36.84% covered (danger)
36.84%
7 / 19
6947.84
0.00% covered (danger)
0.00%
0 / 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
 addActiveClause
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
 findAllActive
100.00% covered (success)
100.00%
7 / 7
100.00% covered (success)
100.00%
1 / 1
1
 findAllByCollectivity
100.00% covered (success)
100.00%
7 / 7
100.00% covered (success)
100.00%
1 / 1
1
 findAllActiveByCollectivity
100.00% covered (success)
100.00%
9 / 9
100.00% covered (success)
100.00%
1 / 1
2
 countAllByCollectivity
0.00% covered (danger)
0.00%
0 / 4
0.00% covered (danger)
0.00%
0 / 1
2
 findOneOrNullLastUpdateByCollectivity
0.00% covered (danger)
0.00%
0 / 5
0.00% covered (danger)
0.00%
0 / 1
2
 countAllActiveByCollectivity
0.00% covered (danger)
0.00%
0 / 5
0.00% covered (danger)
0.00%
0 / 1
2
 findAllByActiveCollectivity
0.00% covered (danger)
0.00%
0 / 15
0.00% covered (danger)
0.00%
0 / 1
6
 findAllActiveByCollectivityWithHasModuleConformiteTraitement
75.00% covered (warning)
75.00%
18 / 24
0.00% covered (danger)
0.00%
0 / 1
3.14
 countAllWithNoConformiteTraitementByCollectivity
0.00% covered (danger)
0.00%
0 / 8
0.00% covered (danger)
0.00%
0 / 1
2
 count
0.00% covered (danger)
0.00%
0 / 12
0.00% covered (danger)
0.00%
0 / 1
30
 findPaginated
0.00% covered (danger)
0.00%
0 / 23
0.00% covered (danger)
0.00%
0 / 1
30
 addTableOrder
0.00% covered (danger)
0.00%
0 / 105
0.00% covered (danger)
0.00%
0 / 1
812
 addTableSearches
0.00% covered (danger)
0.00%
0 / 158
0.00% covered (danger)
0.00%
0 / 1
1980
 resetClonedFromCollectivity
0.00% covered (danger)
0.00%
0 / 5
0.00% covered (danger)
0.00%
0 / 1
2
 findAllByClonedFromCollectivity
0.00% covered (danger)
0.00%
0 / 8
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\AIPD\Dictionary\StatutAnalyseImpactDictionary;
29use App\Domain\Registry\Dictionary\ConformiteTraitementLevelDictionary;
30use App\Domain\Registry\Dictionary\TreatmentStatutDictionary;
31use App\Domain\Registry\Model;
32use App\Domain\Registry\Repository;
33use App\Domain\User\Model\Collectivity;
34use App\Domain\User\Model\User;
35use Doctrine\Common\Collections\Collection;
36use Doctrine\ORM\QueryBuilder;
37use Doctrine\ORM\Tools\Pagination\Paginator;
38use Symfony\Component\Serializer\NameConverter\CamelCaseToSnakeCaseNameConverter;
39
40class Treatment extends CRUDRepository implements Repository\Treatment
41{
42    use RepositoryUtils;
43
44    protected function getModelClass(): string
45    {
46        return Model\Treatment::class;
47    }
48
49    /**
50     * Add collectivity clause to query.
51     */
52    protected function addCollectivityClause(QueryBuilder $qb, Collectivity $collectivity): QueryBuilder
53    {
54        return $qb
55            ->andWhere('o.collectivity = :collectivity')
56            ->setParameter('collectivity', $collectivity)
57        ;
58    }
59
60    /**
61     * Add active clause to query.
62     */
63    protected function addActiveClause(QueryBuilder $qb, bool $active = true): QueryBuilder
64    {
65        return $qb
66            ->andWhere('o.active = :active')
67            ->setParameter('active', $active)
68        ;
69    }
70
71    /**
72     * Add 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 findAllActive(bool $active = true, array $order = [])
84    {
85        $qb = $this->createQueryBuilder();
86
87        $this->addActiveClause($qb, $active);
88        $this->addOrder($qb, $order);
89
90        return $qb
91            ->getQuery()
92            ->getResult()
93        ;
94    }
95
96    public function findAllByCollectivity(Collectivity $collectivity, array $order = [])
97    {
98        $qb = $this->createQueryBuilder();
99
100        $this->addCollectivityClause($qb, $collectivity);
101        $this->addOrder($qb, $order);
102
103        return $qb
104            ->getQuery()
105            ->getResult()
106        ;
107    }
108
109    public function findAllActiveByCollectivity(?Collectivity $collectivity = null, bool $active = true, array $order = [])
110    {
111        $qb = $this->createQueryBuilder();
112
113        if (!\is_null($collectivity)) {
114            $this->addCollectivityClause($qb, $collectivity);
115        }
116        $this->addActiveClause($qb, $active);
117        $this->addOrder($qb, $order);
118
119        return $qb
120            ->getQuery()
121            ->getResult()
122        ;
123    }
124
125    public function countAllByCollectivity(Collectivity $collectivity)
126    {
127        $qb = $this->createQueryBuilder();
128
129        $qb->select('COUNT(o.id)');
130        $this->addCollectivityClause($qb, $collectivity);
131
132        return $qb->getQuery()->getSingleScalarResult();
133    }
134
135    public function findOneOrNullLastUpdateByCollectivity(Collectivity $collectivity): ?Model\Treatment
136    {
137        $qb = $this->createQueryBuilder();
138
139        $this->addCollectivityClause($qb, $collectivity);
140        $qb->addOrderBy('o.updatedAt', 'DESC');
141        $qb->setMaxResults(1);
142
143        return $qb->getQuery()->getOneOrNullResult();
144    }
145
146    public function countAllActiveByCollectivity(Collectivity $collectivity)
147    {
148        $qb = $this->createQueryBuilder();
149
150        $qb->select('COUNT(o.id)');
151        $this->addCollectivityClause($qb, $collectivity);
152        $this->addActiveClause($qb, true);
153
154        return $qb->getQuery()->getSingleScalarResult();
155    }
156
157    public function findAllByActiveCollectivity(bool $active = true, ?User $user = null)
158    {
159        $qb = $this->createQueryBuilder();
160
161        $qb->leftJoin('o.collectivity', 'c')
162            ->andWhere($qb->expr()->eq('c.active', ':active'))
163            ->setParameter('active', $active)
164            ->addOrderBy('c.name')
165            ->addOrderBy('o.createdAt', 'DESC')
166        ;
167
168        if (null !== $user) {
169            $qb->leftJoin('c.userReferents', 'u')
170                ->andWhere('u.id = :user')
171                ->setParameter('user', $user);
172        }
173
174        return $qb
175            ->getQuery()
176            ->getResult()
177        ;
178    }
179
180    public function findAllActiveByCollectivityWithHasModuleConformiteTraitement($collectivity = null, bool $active = true, array $order = ['name' => 'ASC'])
181    {
182        $qb = $this->createQueryBuilder();
183
184        if (!\is_null($collectivity)) {
185            if (\is_array($collectivity)) {
186                $qb
187                    ->andWhere(
188                        $qb->expr()->in('o.collectivity', ':collectivities')
189                    )
190                    ->setParameter('collectivities', $collectivity)
191                ;
192            } else {
193                $this->addCollectivityClause($qb, $collectivity);
194            }
195        }
196        $this->addActiveClause($qb, $active);
197        $this->addOrder($qb, $order);
198
199        $qb->leftJoin('o.collectivity', 'c')
200            ->andWhere($qb->expr()->eq('c.hasModuleConformiteTraitement', ':active'))
201            ->setParameter('active', true)
202        ;
203
204        $qb->leftJoin('o.conformiteTraitement', 'k')
205            ->addSelect('k')
206        ;
207
208        $qb->leftJoin('k.analyseImpacts', 'a')
209            // , 'WITH', 'a.dateValidation = (SELECT MAX(a2.dateValidation) FROM App\Domain\AIPD\Model\AnalyseImpact as a2 WHERE a2.conformiteTraitement = k)
210            ->addSelect('a')
211        ;
212
213        return $qb;
214    }
215
216    public function countAllWithNoConformiteTraitementByCollectivity(?Collectivity $collectivity)
217    {
218        $qb = $this->createQueryBuilder();
219
220        $qb->select('COUNT(o.id)');
221        $this->addCollectivityClause($qb, $collectivity);
222        $this->addActiveClause($qb, true);
223        $qb->leftJoin('o.conformiteTraitement', 'cT')
224            ->andWhere($qb->expr()->isNull('cT.id'))
225        ;
226
227        return $qb->getQuery()->getSingleScalarResult();
228    }
229
230    public function count(array $criteria = [])
231    {
232        $qb = $this->createQueryBuilder();
233
234        $qb->select('COUNT(o.id)');
235        $qb->leftJoin('o.collectivity', 'collectivite');
236        if (isset($criteria['collectivity']) && $criteria['collectivity'] instanceof Collection) {
237            $this->addInClauseCollectivities($qb, $criteria['collectivity']->toArray());
238            unset($criteria['collectivity']);
239        }
240
241        // Fix for https://gitlab.adullact.net/soluris/madis/-/issues/950#note_147289
242        if (isset($criteria['hasModuleConformiteTraitement'])) {
243            $qb->andWhere('collectivite.hasModuleConformiteTraitement=1');
244            unset($criteria['hasModuleConformiteTraitement']);
245        }
246
247        foreach ($criteria as $key => $value) {
248            $this->addWhereClause($qb, $key, $value);
249        }
250
251        return $qb->getQuery()->getSingleScalarResult();
252    }
253
254    public function findPaginated($firstResult, $maxResults, $orderColumn, $orderDir, $searches, $criteria = [])
255    {
256        $qb = $this->createQueryBuilder()
257            ->addSelect('collectivite')
258            ->leftJoin('o.collectivity', 'collectivite')
259            ->leftJoin('o.contractors', 'sous_traitants')
260            ->leftJoin('o.dataCategories', 'data_categories')
261            ->leftJoin('o.conformiteTraitement', 'conformiteTraitement')
262            ->leftJoin('conformiteTraitement.analyseImpacts', 'aipds')
263            ->groupBy('o.id')
264        ;
265
266        if (isset($criteria['collectivity']) && $criteria['collectivity'] instanceof Collection) {
267            $this->addInClauseCollectivities($qb, $criteria['collectivity']->toArray());
268            unset($criteria['collectivity']);
269        }
270
271        // Fix for https://gitlab.adullact.net/soluris/madis/-/issues/950#note_147289
272        if (isset($criteria['hasModuleConformiteTraitement'])) {
273            $qb->andWhere('collectivite.hasModuleConformiteTraitement=1');
274            unset($criteria['hasModuleConformiteTraitement']);
275        }
276
277        foreach ($criteria as $key => $value) {
278            $this->addWhereClause($qb, $key, $value);
279        }
280
281        $this->addTableOrder($qb, $orderColumn, $orderDir);
282        $this->addTableSearches($qb, $searches);
283
284        $qb = $qb->getQuery();
285        $qb->setFirstResult($firstResult);
286        $qb->setMaxResults($maxResults);
287
288        return new Paginator($qb);
289    }
290
291    private function addTableOrder(QueryBuilder $queryBuilder, $orderColumn, $orderDir)
292    {
293        switch ($orderColumn) {
294            case 'name':
295                $queryBuilder->addOrderBy('o.name', $orderDir);
296                break;
297            case 'collectivite':
298                $queryBuilder->addOrderBy('collectivite.name', $orderDir);
299                break;
300            case 'baseLegal':
301                $queryBuilder->addOrderBy('o.legalBasis', $orderDir);
302                break;
303            case 'logiciel':
304                // TODO order on joined table if tools module is active ?
305                $queryBuilder->addOrderBy('o.software', $orderDir);
306                break;
307            case 'enTantQue':
308                $queryBuilder->addOrderBy('o.author', $orderDir);
309                break;
310            case 'gestionnaire':
311                $queryBuilder->addOrderBy('o.manager', $orderDir);
312                break;
313            case 'controleAcces':
314                $queryBuilder->addOrderBy('o.securityAccessControl.check', $orderDir);
315                break;
316            case 'update':
317                $queryBuilder->addOrderBy('o.securityUpdate.check', $orderDir);
318                break;
319            case 'saving':
320                $queryBuilder->addOrderBy('o.securitySaving.check', $orderDir);
321                break;
322            case 'tracabilite':
323                $queryBuilder->addOrderBy('o.securityTracability.check', $orderDir);
324                break;
325            case 'other':
326                $queryBuilder->addOrderBy('o.securityOther.check', $orderDir);
327                break;
328            case 'entitledPersons':
329                $queryBuilder->addOrderBy('o.securityEntitledPersons', $orderDir);
330                break;
331            case 'openAccounts':
332                $queryBuilder->addOrderBy('o.securityOpenAccounts', $orderDir);
333                break;
334            case 'specificitiesDelivered':
335                $queryBuilder->addOrderBy('o.securitySpecificitiesDelivered', $orderDir);
336                break;
337            case 'responsableTraitement':
338                $queryBuilder->addOrderBy('o.coordonneesResponsableTraitement', $orderDir);
339                break;
340            case 'createdAt':
341                $queryBuilder->addOrderBy('o.createdAt', $orderDir);
342                break;
343            case 'updatedAt':
344                $queryBuilder->addOrderBy('o.updatedAt', $orderDir);
345                break;
346            case 'statut':
347                $queryBuilder->addSelect('(case
348                WHEN o.statut = \'' . TreatmentStatutDictionary::DRAFT . '\' THEN 1
349                WHEN o.statut = \'' . TreatmentStatutDictionary::CHECKED . '\' THEN 2
350                WHEN o.statut = \'' . TreatmentStatutDictionary::FINISHED . '\' THEN 3
351                ELSE 4 END) AS HIDDEN hidden_statut')
352                    ->addOrderBy('hidden_statut', $orderDir);
353                break;
354            case 'sensitiveData':
355                $queryBuilder->leftJoin('o.dataCategories', 'dco', 'WITH', 'dco.sensible = 1')
356                    ->addSelect('COUNT(dco.code) as sensitiveCount')
357                    ->addOrderBy('sensitiveCount', $orderDir)
358                    ->groupBy('o.id')
359                ;
360                break;
361            case 'exempt_AIPD':
362                $queryBuilder->addOrderBy('o.exempt_AIPD', $orderDir);
363                break;
364            case 'eval_createdAt':
365                $queryBuilder->addOrderBy('conformiteTraitement.createdAt', $orderDir);
366                break;
367            case 'conformite_traitement':
368                $queryBuilder->addSelect('(case
369                WHEN conformiteTraitement.nbNonConformesMineures = 0 AND conformiteTraitement.nbNonConformesMajeures = 0 THEN 2
370                WHEN conformiteTraitement.nbNonConformesMineures >= 1 AND conformiteTraitement.nbNonConformesMajeures = 0 THEN 3
371                WHEN conformiteTraitement.nbNonConformesMineures >= 3 OR conformiteTraitement.nbNonConformesMajeures >= 1 THEN 4
372                ELSE 5 END) AS HIDDEN hidden_conf_status')
373                    ->addOrderBy('hidden_conf_status', $orderDir);
374                break;
375            case 'avis_aipd':
376                $queryBuilder->addSelect('(case
377                WHEN aipds.statut = \'' . StatutAnalyseImpactDictionary::NON_REALISEE . '\'  THEN 1
378                WHEN aipds.statut = \'' . StatutAnalyseImpactDictionary::NON_FAVORABLE . '\'  THEN 2
379                WHEN aipds.statut = \'' . StatutAnalyseImpactDictionary::EN_COURS . '\'  THEN 3
380                WHEN aipds.statut = \'' . StatutAnalyseImpactDictionary::FAVORABLE_AVEC_RESERVES . '\'  THEN 4
381                WHEN aipds.statut = \'' . StatutAnalyseImpactDictionary::FAVORABLE_RESERVE . '\'  THEN 4
382                WHEN aipds.statut = \'' . StatutAnalyseImpactDictionary::FAVORABLE . '\'  THEN 5
383                ELSE 6 END) AS HIDDEN hidden_AIPD_status')
384                    ->addOrderBy('hidden_AIPD_status', $orderDir);
385
386                break;
387            case 'specific_traitement':
388                $queryBuilder->addOrderBy('o.systematicMonitoring + o.largeScaleCollection + o.vulnerablePeople + o.dataCrossing + o.evaluationOrRating + o.automatedDecisionsWithLegalEffect + o.automaticExclusionService + o.innovativeUse', $orderDir);
389                break;
390            case 'sousTraitant':
391                $queryBuilder
392                    ->addSelect('COUNT(DISTINCT sous_traitants.id) as contractorCount')
393                ->addOrderBy('contractorCount', $orderDir)
394                ->groupBy('o.id')
395                ;
396                break;
397            case 'service':
398                $queryBuilder->leftJoin('o.service', 'service_sort');
399                $queryBuilder->addOrderBy('service_sort.name', $orderDir);
400                break;
401            case 'aipd_createdAt':
402                $queryBuilder->addOrderBy('aipds.createdAt', $orderDir);
403                break;
404        }
405    }
406
407    private function addTableSearches(QueryBuilder $queryBuilder, $searches)
408    {
409        foreach ($searches as $columnName => $search) {
410            switch ($columnName) {
411                case 'nom':
412                    $this->addWhereClause($queryBuilder, 'name', '%' . $search . '%', 'LIKE');
413                    break;
414                case 'collectivite':
415                    $queryBuilder->andWhere('collectivite.name LIKE :nom')
416                        ->setParameter('nom', '%' . $search . '%');
417                    break;
418                case 'baseLegal':
419                    $this->addWhereClause($queryBuilder, 'legalBasis', '%' . $search . '%', 'LIKE');
420                    break;
421                case 'logiciel':
422                    // If collectivity has tools modules active, search in tools
423                    $queryBuilder->leftJoin('o.tools', 'tools')
424                        ->addSelect('GROUP_CONCAT(tools.name) as HIDDEN toolsNames')
425                        ->groupBy('o.id')
426                    ;
427
428                    $queryBuilder->andHaving($queryBuilder->expr()->orX('(toolsNames LIKE :software_tool AND collectivite.hasModuleTools = 1)', '(o.software LIKE :software_tool AND collectivite.hasModuleTools = 0)'))
429                        ->setParameter('software_tool', '%' . $search . '%')
430                    ;
431
432                    break;
433                case 'enTantQue':
434                    $this->addWhereClause($queryBuilder, 'author', '%' . $search . '%', 'LIKE');
435                    break;
436                case 'gestionnaire':
437                    $this->addWhereClause($queryBuilder, 'manager', '%' . $search . '%', 'LIKE');
438                    break;
439                case 'sousTraitant':
440                    $queryBuilder->andWhere('sous_traitants.name LIKE :st_nom')
441                        ->setParameter('st_nom', '%' . $search . '%');
442                    break;
443                case 'controleAcces':
444                    $queryBuilder->andWhere('o.securityAccessControl.check = :access_control')
445                        ->setParameter('access_control', $search);
446                    break;
447                case 'update':
448                    $queryBuilder->andWhere('o.securityUpdate.check = :update')
449                        ->setParameter('update', $search);
450                    break;
451                case 'saving':
452                    $queryBuilder->andWhere('o.securitySaving.check = :saving')
453                        ->setParameter('saving', $search);
454                    break;
455                case 'tracabilite':
456                    $queryBuilder->andWhere('o.securityTracability.check = :tracabilite')
457                        ->setParameter('tracabilite', $search);
458                    break;
459                case 'other':
460                    $queryBuilder->andWhere('o.securityOther.check = :other')
461                        ->setParameter('other', $search);
462                    break;
463                case 'entitledPersons':
464                    $queryBuilder->andWhere('o.securityEntitledPersons = :entitledPersons')
465                        ->setParameter('entitledPersons', $search);
466                    break;
467                case 'openAccounts':
468                    $queryBuilder->andWhere('o.securityOpenAccounts = :openAccounts')
469                        ->setParameter('openAccounts', $search);
470                    break;
471                case 'specificitiesDelivered':
472                    $this->addWhereClause($queryBuilder, 'securitySpecificitiesDelivered', '%' . $search . '%', 'LIKE');
473                    // $queryBuilder->andWhere('o.securitySpecificitiesDelivered LIKE :specificitiesDelivered')
474                    //    ->setParameter('specificitiesDelivered', '%' . $search . '%');
475                    break;
476                case 'exempt_AIPD':
477                    $queryBuilder->andWhere('o.exempt_AIPD = :exempt_AIPD')
478                        ->setParameter('exempt_AIPD', $search);
479                    break;
480                case 'createdAt':
481                    if (is_string($search)) {
482                        $queryBuilder->andWhere('o.createdAt BETWEEN :created_start_date AND :created_finish_date')
483                            ->setParameter('created_start_date', date_create_from_format('d/m/y', substr($search, 0, 8))->format('Y-m-d 00:00:00'))
484                            ->setParameter('created_finish_date', date_create_from_format('d/m/y', substr($search, 11, 8))->format('Y-m-d 23:59:59'));
485                    }
486                    break;
487                case 'updatedAt':
488                    if (is_string($search)) {
489                        $queryBuilder->andWhere('o.updatedAt BETWEEN :updated_start_date AND :updated_finish_date')
490                            ->setParameter('updated_start_date', date_create_from_format('d/m/y', substr($search, 0, 8))->format('Y-m-d 00:00:00'))
491                            ->setParameter('updated_finish_date', date_create_from_format('d/m/y', substr($search, 11, 8))->format('Y-m-d 23:59:59'));
492                    }
493                    break;
494                case 'public':
495                    $queryBuilder->andWhere('o.public = :public')
496                        ->setParameter('public', $search);
497                    break;
498                case 'responsableTraitement':
499                    $this->addWhereClause($queryBuilder, 'coordonneesResponsableTraitement', '%' . $search . '%', 'LIKE');
500                    break;
501                case 'sensitiveData':
502                    if ($search) {
503                        $queryBuilder
504                            ->leftJoin('o.dataCategories', 'dcs', 'WITH', 'dcs.sensible = :sensitiveDatas')
505                            ->addSelect('dcs.sensible AS sensitiveData')
506                            ->andHaving('COUNT(dcs.code) > 0')
507                            ->setParameter('sensitiveDatas', 1)
508                            ->groupBy('o.id')
509                        ;
510                    } else {
511                        $queryBuilder
512                            ->leftJoin('o.dataCategories', 'dcs', 'WITH', 'dcs.sensible = :sensitiveDatas')
513                            ->andHaving('COUNT(dcs.code) = 0')
514                            ->setParameter('sensitiveDatas', 1)
515                            ->groupBy('o.id')
516                        ;
517                    }
518
519                    break;
520                case 'statut':
521                    $queryBuilder->andWhere('o.statut = :statut')
522                        ->setParameter('statut', $search);
523                    break;
524                case 'service':
525                    $queryBuilder->leftJoin('o.service', 'service');
526                    $queryBuilder->andWhere('service.name LIKE :service_name')
527                        ->setParameter('service_name', '%' . $search . '%');
528                    break;
529                case 'eval_createdAt':
530                    if (is_string($search)) {
531                        $queryBuilder->andWhere('conformiteTraitement.updatedAt BETWEEN :updated_start_date AND :updated_finish_date')
532                            ->setParameter('updated_start_date', date_create_from_format('d/m/y', substr($search, 0, 8))->format('Y-m-d 00:00:00'))
533                            ->setParameter('updated_finish_date', date_create_from_format('d/m/y', substr($search, 11, 8))->format('Y-m-d 23:59:59'));
534                    }
535                    break;
536                case 'aipd_createdAt':
537                    if (is_string($search)) {
538                        $queryBuilder->andWhere('aipds.createdAt BETWEEN :aipd_start_date AND :aipd_finish_date')
539                            ->setParameter('aipd_start_date', date_create_from_format('d/m/y', substr($search, 0, 8))->format('Y-m-d 00:00:00'))
540                            ->setParameter('aipd_finish_date', date_create_from_format('d/m/y', substr($search, 11, 8))->format('Y-m-d 23:59:59'));
541                    }
542                    break;
543                case 'avis_aipd':
544                    if (StatutAnalyseImpactDictionary::NON_REALISEE === $search) {
545                        $queryBuilder->andWhere('conformiteTraitement IS NULL OR aipds IS NULL');
546                        break;
547                    }
548
549                    $queryBuilder->andWhere('aipds.statut = :aipdstatus');
550                    $queryBuilder->setParameter('aipdstatus', $search);
551                    break;
552                case 'specific_traitement':
553                    if ('null' === $search) {
554                        // Selectpicker returns null string when no values are selected
555                        break;
556                    }
557                    $values = explode(',', $search);
558                    $wheres = [];
559                    $conv   = new CamelCaseToSnakeCaseNameConverter();
560                    foreach ($values as $val) {
561                        if ($val) {
562                            $v        = $conv->denormalize($val);
563                            $wheres[] = 'o.' . $v . '=true';
564                        }
565                    }
566                    if (count($wheres)) {
567                        $queryBuilder->andWhere(join(' OR ', $wheres));
568                    }
569
570                    break;
571                case 'conformite_traitement':
572                    if (ConformiteTraitementLevelDictionary::NON_EVALUE === $search) {
573                        $queryBuilder->andWhere('conformiteTraitement is null');
574                        break;
575                    }
576                    if (ConformiteTraitementLevelDictionary::CONFORME === $search) {
577                        $queryBuilder->andWhere('conformiteTraitement.nbNonConformesMineures = 0');
578                        $queryBuilder->andWhere('conformiteTraitement.nbNonConformesMajeures = 0');
579                        break;
580                    }
581                    if (ConformiteTraitementLevelDictionary::NON_CONFORMITE_MINEURE === $search) {
582                        $queryBuilder->andWhere('conformiteTraitement.nbNonConformesMineures >= 1');
583                        $queryBuilder->andWhere('conformiteTraitement.nbNonConformesMineures < 3');
584                        $queryBuilder->andWhere('conformiteTraitement.nbNonConformesMajeures = 0');
585
586                        break;
587                    }
588                    if (ConformiteTraitementLevelDictionary::NON_CONFORMITE_MAJEURE === $search) {
589                        $queryBuilder->andWhere('conformiteTraitement.nbNonConformesMineures >= 3 OR conformiteTraitement.nbNonConformesMajeures >= 1');
590                        break;
591                    }
592                    break;
593            }
594        }
595    }
596
597    public function resetClonedFromCollectivity(Collectivity $collectivity)
598    {
599        $qb = $this->createQueryBuilder();
600
601        $qb->leftJoin('o.clonedFrom', 'c')
602            ->andWhere('c.collectivity = :collectivity')
603            ->setParameter('collectivity', $collectivity);
604
605        $qb->update(['o.clonedFrom' => null]);
606    }
607
608    public function findAllByClonedFromCollectivity(Collectivity $collectivity)
609    {
610        $qb = $this->createQueryBuilder();
611
612        $qb->leftJoin('o.clonedFrom', 'c')
613            ->andWhere('c.collectivity = :collectivity')
614            ->setParameter('collectivity', $collectivity);
615
616        return $qb
617            ->getQuery()
618            ->getResult()
619        ;
620    }
621}