Code Coverage |
||||||||||
Lines |
Functions and Methods |
Classes and Traits |
||||||||
Total | |
13.02% |
53 / 407 |
|
36.84% |
7 / 19 |
CRAP | |
0.00% |
0 / 1 |
Treatment | |
13.02% |
53 / 407 |
|
36.84% |
7 / 19 |
6947.84 | |
0.00% |
0 / 1 |
getModelClass | |
100.00% |
1 / 1 |
|
100.00% |
1 / 1 |
1 | |||
addCollectivityClause | |
100.00% |
4 / 4 |
|
100.00% |
1 / 1 |
1 | |||
addActiveClause | |
100.00% |
4 / 4 |
|
100.00% |
1 / 1 |
1 | |||
addOrder | |
100.00% |
3 / 3 |
|
100.00% |
1 / 1 |
2 | |||
findAllActive | |
100.00% |
7 / 7 |
|
100.00% |
1 / 1 |
1 | |||
findAllByCollectivity | |
100.00% |
7 / 7 |
|
100.00% |
1 / 1 |
1 | |||
findAllActiveByCollectivity | |
100.00% |
9 / 9 |
|
100.00% |
1 / 1 |
2 | |||
countAllByCollectivity | |
0.00% |
0 / 4 |
|
0.00% |
0 / 1 |
2 | |||
findOneOrNullLastUpdateByCollectivity | |
0.00% |
0 / 5 |
|
0.00% |
0 / 1 |
2 | |||
countAllActiveByCollectivity | |
0.00% |
0 / 5 |
|
0.00% |
0 / 1 |
2 | |||
findAllByActiveCollectivity | |
0.00% |
0 / 15 |
|
0.00% |
0 / 1 |
6 | |||
findAllActiveByCollectivityWithHasModuleConformiteTraitement | |
75.00% |
18 / 24 |
|
0.00% |
0 / 1 |
3.14 | |||
countAllWithNoConformiteTraitementByCollectivity | |
0.00% |
0 / 8 |
|
0.00% |
0 / 1 |
2 | |||
count | |
0.00% |
0 / 12 |
|
0.00% |
0 / 1 |
30 | |||
findPaginated | |
0.00% |
0 / 23 |
|
0.00% |
0 / 1 |
30 | |||
addTableOrder | |
0.00% |
0 / 105 |
|
0.00% |
0 / 1 |
812 | |||
addTableSearches | |
0.00% |
0 / 158 |
|
0.00% |
0 / 1 |
1980 | |||
resetClonedFromCollectivity | |
0.00% |
0 / 5 |
|
0.00% |
0 / 1 |
2 | |||
findAllByClonedFromCollectivity | |
0.00% |
0 / 8 |
|
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 | |
22 | declare(strict_types=1); |
23 | |
24 | namespace App\Infrastructure\ORM\Registry\Repository; |
25 | |
26 | use App\Application\Doctrine\Repository\CRUDRepository; |
27 | use App\Application\Traits\RepositoryUtils; |
28 | use App\Domain\AIPD\Dictionary\StatutAnalyseImpactDictionary; |
29 | use App\Domain\Registry\Dictionary\ConformiteTraitementLevelDictionary; |
30 | use App\Domain\Registry\Dictionary\TreatmentStatutDictionary; |
31 | use App\Domain\Registry\Model; |
32 | use App\Domain\Registry\Repository; |
33 | use App\Domain\User\Model\Collectivity; |
34 | use App\Domain\User\Model\User; |
35 | use Doctrine\Common\Collections\Collection; |
36 | use Doctrine\ORM\QueryBuilder; |
37 | use Doctrine\ORM\Tools\Pagination\Paginator; |
38 | use Symfony\Component\Serializer\NameConverter\CamelCaseToSnakeCaseNameConverter; |
39 | |
40 | class 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 | } |