Code Coverage |
||||||||||
Lines |
Functions and Methods |
Classes and Traits |
||||||||
Total | |
0.95% |
2 / 210 |
|
8.33% |
2 / 24 |
CRAP | |
0.00% |
0 / 1 |
Proof | |
0.95% |
2 / 210 |
|
8.33% |
2 / 24 |
3103.25 | |
0.00% |
0 / 1 |
__construct | |
100.00% |
1 / 1 |
|
100.00% |
1 / 1 |
1 | |||
getManager | |
0.00% |
0 / 4 |
|
0.00% |
0 / 1 |
6 | |||
createQueryBuilder | |
0.00% |
0 / 5 |
|
0.00% |
0 / 1 |
2 | |||
insert | |
0.00% |
0 / 2 |
|
0.00% |
0 / 1 |
2 | |||
update | |
0.00% |
0 / 1 |
|
0.00% |
0 / 1 |
2 | |||
create | |
0.00% |
0 / 2 |
|
0.00% |
0 / 1 |
2 | |||
remove | |
0.00% |
0 / 2 |
|
0.00% |
0 / 1 |
2 | |||
findAll | |
0.00% |
0 / 8 |
|
0.00% |
0 / 1 |
6 | |||
findOneById | |
0.00% |
0 / 5 |
|
0.00% |
0 / 1 |
2 | |||
getModelClass | |
100.00% |
1 / 1 |
|
100.00% |
1 / 1 |
1 | |||
addArchivedClause | |
0.00% |
0 / 3 |
|
0.00% |
0 / 1 |
6 | |||
addCollectivityClause | |
0.00% |
0 / 4 |
|
0.00% |
0 / 1 |
2 | |||
addOrder | |
0.00% |
0 / 3 |
|
0.00% |
0 / 1 |
6 | |||
findAllByCollectivity | |
0.00% |
0 / 10 |
|
0.00% |
0 / 1 |
6 | |||
findBy | |
0.00% |
0 / 7 |
|
0.00% |
0 / 1 |
6 | |||
findAllArchivedByCollectivity | |
0.00% |
0 / 8 |
|
0.00% |
0 / 1 |
2 | |||
findOneOrNullByTypeAndCollectivity | |
0.00% |
0 / 10 |
|
0.00% |
0 / 1 |
2 | |||
countAllByCollectivity | |
0.00% |
0 / 5 |
|
0.00% |
0 / 1 |
2 | |||
averageProofFiled | |
0.00% |
0 / 13 |
|
0.00% |
0 / 1 |
6 | |||
averageBalanceSheetProof | |
0.00% |
0 / 14 |
|
0.00% |
0 / 1 |
6 | |||
count | |
0.00% |
0 / 17 |
|
0.00% |
0 / 1 |
30 | |||
findPaginated | |
0.00% |
0 / 19 |
|
0.00% |
0 / 1 |
30 | |||
addTableOrder | |
0.00% |
0 / 34 |
|
0.00% |
0 / 1 |
72 | |||
addTableWhere | |
0.00% |
0 / 32 |
|
0.00% |
0 / 1 |
110 |
1 | <?php |
2 | |
3 | /** |
4 | * This file is part of the MADIS - RGPD Management application. |
5 | * |
6 | * @copyright Copyright (c) 2018-2019 Soluris - Solutions Numériques Territoriales Innovantes |
7 | * |
8 | * This program is free software: you can redistribute it and/or modify |
9 | * it under the terms of the GNU Affero General Public License as published by |
10 | * the Free Software Foundation, either version 3 of the License, or |
11 | * (at your option) any later version. |
12 | * |
13 | * This program is distributed in the hope that it will be useful, |
14 | * but WITHOUT ANY WARRANTY; without even the implied warranty of |
15 | * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the |
16 | * GNU Affero General Public License for more details. |
17 | * |
18 | * You should have received a copy of the GNU Affero General Public License |
19 | * along with this program. If not, see <https://www.gnu.org/licenses/>. |
20 | */ |
21 | |
22 | declare(strict_types=1); |
23 | |
24 | namespace App\Infrastructure\ORM\Registry\Repository; |
25 | |
26 | use App\Application\Traits\RepositoryUtils; |
27 | use App\Domain\Registry\Dictionary\ProofTypeDictionary; |
28 | use App\Domain\Registry\Model; |
29 | use App\Domain\Registry\Repository; |
30 | use App\Domain\User\Model\Collectivity; |
31 | use Doctrine\Common\Collections\Collection; |
32 | use Doctrine\ORM\EntityManagerInterface; |
33 | use Doctrine\ORM\QueryBuilder; |
34 | use Doctrine\ORM\Tools\Pagination\Paginator; |
35 | use Doctrine\Persistence\ManagerRegistry; |
36 | |
37 | class Proof implements Repository\Proof |
38 | { |
39 | use RepositoryUtils; |
40 | |
41 | /** |
42 | * @var ManagerRegistry |
43 | */ |
44 | protected $registry; |
45 | |
46 | /** |
47 | * Proof constructor. |
48 | */ |
49 | public function __construct(ManagerRegistry $registry) |
50 | { |
51 | $this->registry = $registry; |
52 | } |
53 | |
54 | /** |
55 | * Get the registry manager |
56 | * Since we use Doctrine, we expect to get EntityManagerInterface. |
57 | * |
58 | * @throws \Exception |
59 | */ |
60 | protected function getManager(): EntityManagerInterface |
61 | { |
62 | $manager = $this->registry->getManager(); |
63 | |
64 | if (!$manager instanceof EntityManagerInterface) { |
65 | throw new \Exception('Registry Manager must be an instance of EntityManagerInterface #PHPStan'); |
66 | } |
67 | |
68 | return $manager; |
69 | } |
70 | |
71 | /** |
72 | * Create the base of QueryBuilder to use for repository calls. |
73 | * |
74 | * @throws \Exception |
75 | * |
76 | * @return QueryBuilder |
77 | */ |
78 | protected function createQueryBuilder() |
79 | { |
80 | return $this->getManager() |
81 | ->createQueryBuilder() |
82 | ->select('o') |
83 | ->from($this->getModelClass(), 'o') |
84 | ; |
85 | } |
86 | |
87 | /** |
88 | * Insert an object. |
89 | * |
90 | * @throws \Exception |
91 | */ |
92 | public function insert($object): void |
93 | { |
94 | $this->getManager()->persist($object); |
95 | $this->getManager()->flush(); |
96 | } |
97 | |
98 | /** |
99 | * Update an object. |
100 | * |
101 | * @throws \Exception |
102 | */ |
103 | public function update($object): void |
104 | { |
105 | $this->getManager()->flush(); |
106 | } |
107 | |
108 | /** |
109 | * Create an object. |
110 | */ |
111 | public function create() |
112 | { |
113 | $class = $this->getModelClass(); |
114 | |
115 | return new $class(); |
116 | } |
117 | |
118 | /** |
119 | * Remove an object. |
120 | * |
121 | * @throws \Exception |
122 | */ |
123 | public function remove($object): void |
124 | { |
125 | $this->getManager()->remove($object); |
126 | $this->getManager()->flush(); |
127 | } |
128 | |
129 | /** |
130 | * Find all data. |
131 | * |
132 | * @throws \Exception |
133 | */ |
134 | public function findAll(bool $deleted = false): array |
135 | { |
136 | $qb = $this->createQueryBuilder(); |
137 | |
138 | if ($deleted) { |
139 | $qb->andWhere('o.deletedAt is not null'); |
140 | } else { |
141 | $qb->andWhere('o.deletedAt is null'); |
142 | } |
143 | |
144 | return $qb |
145 | ->getQuery() |
146 | ->getResult() |
147 | ; |
148 | } |
149 | |
150 | /** |
151 | * Get an object by ID. |
152 | * |
153 | * @param string $id The ID to find |
154 | * |
155 | * @return object|null |
156 | */ |
157 | public function findOneById(string $id) |
158 | { |
159 | return $this->registry |
160 | ->getManager() |
161 | ->getRepository($this->getModelClass()) |
162 | ->find($id) |
163 | ; |
164 | } |
165 | |
166 | /** |
167 | * Get the model class name. |
168 | */ |
169 | protected function getModelClass(): string |
170 | { |
171 | return Model\Proof::class; |
172 | } |
173 | |
174 | /** |
175 | * Add archive clause to query. |
176 | */ |
177 | protected function addArchivedClause(QueryBuilder $qb, bool $archived = false): QueryBuilder |
178 | { |
179 | // Get not archived |
180 | if (!$archived) { |
181 | return $qb->andWhere('o.deletedAt is null'); |
182 | } |
183 | |
184 | // Get archived |
185 | return $qb->andWhere('o.deletedAt is not null'); |
186 | } |
187 | |
188 | /** |
189 | * Add collectivity clause to query. |
190 | */ |
191 | protected function addCollectivityClause(QueryBuilder $qb, Collectivity $collectivity): QueryBuilder |
192 | { |
193 | return $qb |
194 | ->andWhere('o.collectivity = :collectivity') |
195 | ->setParameter('collectivity', $collectivity) |
196 | ; |
197 | } |
198 | |
199 | /** |
200 | * Add order clause to query. |
201 | */ |
202 | protected function addOrder(QueryBuilder $qb, array $order = []): QueryBuilder |
203 | { |
204 | foreach ($order as $key => $dir) { |
205 | $qb->addOrderBy("o.{$key}", $dir); |
206 | } |
207 | |
208 | return $qb; |
209 | } |
210 | |
211 | /** |
212 | * @throws \Exception |
213 | */ |
214 | public function findAllByCollectivity(Collectivity $collectivity, bool $deleted = false, array $order = []) |
215 | { |
216 | $qb = $this->createQueryBuilder(); |
217 | |
218 | $this->addCollectivityClause($qb, $collectivity); |
219 | |
220 | if ($deleted) { |
221 | $qb->andWhere('o.deletedAt is not null'); |
222 | } else { |
223 | $qb->andWhere('o.deletedAt is null'); |
224 | } |
225 | |
226 | $this->addOrder($qb, $order); |
227 | |
228 | return $qb |
229 | ->getQuery() |
230 | ->getResult() |
231 | ; |
232 | } |
233 | |
234 | /** |
235 | * @throws \Exception |
236 | */ |
237 | public function findBy(array $criteria = []) |
238 | { |
239 | $qb = $this->createQueryBuilder(); |
240 | |
241 | foreach ($criteria as $key => $value) { |
242 | $this->addWhereClause($qb, $key, $value); |
243 | } |
244 | |
245 | return $qb |
246 | ->getQuery() |
247 | ->getResult() |
248 | ; |
249 | } |
250 | |
251 | /** |
252 | * @throws \Exception |
253 | */ |
254 | public function findAllArchivedByCollectivity(Collectivity $collectivity, bool $archived = false, array $order = []) |
255 | { |
256 | $qb = $this->createQueryBuilder(); |
257 | |
258 | $this->addCollectivityClause($qb, $collectivity); |
259 | $this->addArchivedClause($qb, $archived); |
260 | $this->addOrder($qb, $order); |
261 | |
262 | return $qb |
263 | ->getQuery() |
264 | ->getResult() |
265 | ; |
266 | } |
267 | |
268 | public function findOneOrNullByTypeAndCollectivity(string $type, Collectivity $collectivity): ?Model\Proof |
269 | { |
270 | $qb = $this->createQueryBuilder(); |
271 | |
272 | $qb->andWhere($qb->expr()->eq('o.type', ':type')); |
273 | $qb->andWhere($qb->expr()->eq('o.collectivity', ':collectivity')); |
274 | $qb->setParameters([ |
275 | 'type' => $type, |
276 | 'collectivity' => $collectivity, |
277 | ]); |
278 | $qb->addOrderBy('o.createdAt', 'DESC'); |
279 | $qb->setMaxResults(1); |
280 | |
281 | return $qb->getQuery()->getOneOrNullResult(); |
282 | } |
283 | |
284 | public function countAllByCollectivity(Collectivity $collectivity) |
285 | { |
286 | $qb = $this->createQueryBuilder(); |
287 | |
288 | $qb->select('COUNT(o.id)'); |
289 | $qb->andWhere($qb->expr()->eq('o.collectivity', ':collectivity')); |
290 | $qb->setParameter('collectivity', $collectivity); |
291 | |
292 | return $qb->getQuery()->getSingleScalarResult(); |
293 | } |
294 | |
295 | public function averageProofFiled(array $collectivities = []) |
296 | { |
297 | $sql = 'SELECT AVG(a.rcount) FROM ( |
298 | SELECT COUNT(rp.id) as rcount |
299 | FROM user_collectivity uc |
300 | LEFT OUTER JOIN registry_proof rp ON uc.id = rp.collectivity_id |
301 | WHERE uc.active = 1'; |
302 | |
303 | if (!empty($collectivities)) { |
304 | $sql .= ' AND uc.id IN ('; |
305 | $sql .= \implode(',', \array_map(function ($collectivity) { |
306 | return '\'' . $collectivity->getId() . '\''; |
307 | }, $collectivities)); |
308 | $sql .= ') '; |
309 | } |
310 | |
311 | $sql .= ' |
312 | GROUP BY uc.id |
313 | ) a'; |
314 | |
315 | $stmt = $this->getManager()->getConnection()->prepare($sql); |
316 | $stmt->execute(); |
317 | |
318 | return $stmt->fetchColumn(); |
319 | } |
320 | |
321 | public function averageBalanceSheetProof(array $collectivities = []) |
322 | { |
323 | $sql = 'SELECT AVG(a.rcount) FROM ( |
324 | SELECT IF(COUNT(rp.id) > 0, 1, 0) as rcount |
325 | FROM user_collectivity uc |
326 | LEFT OUTER JOIN registry_proof rp ON (uc.id = rp.collectivity_id AND rp.created_at >= NOW() - INTERVAL 1 YEAR AND rp.type = "' . ProofTypeDictionary::TYPE_BALANCE_SHEET . '") |
327 | WHERE uc.active = 1'; |
328 | |
329 | if (!empty($collectivities)) { |
330 | $sql .= ' AND uc.id IN ('; |
331 | $sql .= \implode(',', \array_map(function ($collectivity) { |
332 | return '\'' . $collectivity->getId() . '\''; |
333 | }, $collectivities)); |
334 | $sql .= ') '; |
335 | } |
336 | |
337 | $sql .= ' |
338 | GROUP BY uc.id |
339 | ) a'; |
340 | |
341 | $stmt = $this->getManager()->getConnection()->prepare($sql); |
342 | $stmt->execute(); |
343 | |
344 | return $stmt->fetchColumn(); |
345 | } |
346 | |
347 | public function count(array $criteria = []) |
348 | { |
349 | $qb = $this |
350 | ->createQueryBuilder() |
351 | ->select('count(o.id)') |
352 | ; |
353 | |
354 | if (\array_key_exists('archive', $criteria)) { |
355 | $this->addArchivedClause($qb, $criteria['archive']); |
356 | unset($criteria['archive']); |
357 | } |
358 | |
359 | if (isset($criteria['collectivity']) && $criteria['collectivity'] instanceof Collection) { |
360 | $qb->leftJoin('o.collectivity', 'collectivite'); |
361 | $this->addInClauseCollectivities($qb, $criteria['collectivity']->toArray()); |
362 | unset($criteria['collectivity']); |
363 | } |
364 | |
365 | foreach ($criteria as $key => $value) { |
366 | $this->addWhereClause($qb, $key, $value); |
367 | } |
368 | |
369 | return $qb |
370 | ->getQuery() |
371 | ->getSingleScalarResult() |
372 | ; |
373 | } |
374 | |
375 | public function findPaginated($firstResult, $maxResults, $orderColumn, $orderDir, $searches, $criteria = []) |
376 | { |
377 | $qb = $this->createQueryBuilder(); |
378 | |
379 | if (\array_key_exists('archive', $criteria)) { |
380 | $this->addArchivedClause($qb, $criteria['archive']); |
381 | unset($criteria['archive']); |
382 | } |
383 | |
384 | $qb |
385 | ->leftJoin('o.collectivity', 'collectivite') |
386 | ->leftJoin('o.service', 'service') |
387 | ->addSelect('collectivite'); |
388 | |
389 | if (isset($criteria['collectivity']) && $criteria['collectivity'] instanceof Collection) { |
390 | $this->addInClauseCollectivities($qb, $criteria['collectivity']->toArray()); |
391 | unset($criteria['collectivity']); |
392 | } |
393 | |
394 | foreach ($criteria as $key => $value) { |
395 | $this->addWhereClause($qb, $key, $value); |
396 | } |
397 | |
398 | $this->addTableOrder($qb, $orderColumn, $orderDir); |
399 | $this->addTableWhere($qb, $searches); |
400 | |
401 | $query = $qb->getQuery(); |
402 | $query->setFirstResult($firstResult); |
403 | $query->setMaxResults($maxResults); |
404 | |
405 | return new Paginator($query); |
406 | } |
407 | |
408 | private function addTableOrder(QueryBuilder $queryBuilder, $orderColumn, $orderDir) |
409 | { |
410 | switch ($orderColumn) { |
411 | case 'nom': |
412 | $queryBuilder->addOrderBy('o.name', $orderDir); |
413 | break; |
414 | case 'collectivite': |
415 | $queryBuilder->addOrderBy('collectivite.name', $orderDir); |
416 | break; |
417 | case 'service': |
418 | $queryBuilder->addOrderBy('service.name', $orderDir); |
419 | break; |
420 | case 'type': |
421 | $queryBuilder->addSelect('(case |
422 | WHEN o.type = \'' . ProofTypeDictionary::TYPE_MESUREMENT . '\' THEN 1 |
423 | WHEN o.type = \'' . ProofTypeDictionary::TYPE_CERTIFICATION . '\' THEN 2 |
424 | WHEN o.type = \'' . ProofTypeDictionary::TYPE_OTHER . '\' THEN 3 |
425 | WHEN o.type = \'' . ProofTypeDictionary::TYPE_BALANCE_SHEET . '\' THEN 4 |
426 | WHEN o.type = \'' . ProofTypeDictionary::TYPE_IT_CHARTER . '\' THEN 5 |
427 | WHEN o.type = \'' . ProofTypeDictionary::TYPE_CONTRACT . '\' THEN 6 |
428 | WHEN o.type = \'' . ProofTypeDictionary::TYPE_DELIBERATION . '\' THEN 7 |
429 | WHEN o.type = \'' . ProofTypeDictionary::TYPE_CONCERNED_PEOPLE_REQUEST . '\' THEN 8 |
430 | WHEN o.type = \'' . ProofTypeDictionary::TYPE_POLICY_MANAGEMENT . '\' THEN 9 |
431 | WHEN o.type = \'' . ProofTypeDictionary::TYPE_POLICY_PROTECTION . '\' THEN 10 |
432 | WHEN o.type = \'' . ProofTypeDictionary::TYPE_SENSITIZATION . '\' THEN 11 |
433 | ELSE 12 END) AS HIDDEN hidden_type') |
434 | ->addOrderBy('hidden_type', $orderDir); |
435 | break; |
436 | case 'commentaire': |
437 | $queryBuilder->addOrderBy('o.comment', $orderDir); |
438 | break; |
439 | case 'date': |
440 | $queryBuilder->addOrderBy('o.createdAt', $orderDir); |
441 | break; |
442 | case 'updatedAt': |
443 | $queryBuilder->addOrderBy('o.updatedAt', $orderDir); |
444 | break; |
445 | } |
446 | } |
447 | |
448 | private function addTableWhere(QueryBuilder $queryBuilder, $searches) |
449 | { |
450 | foreach ($searches as $columnName => $search) { |
451 | switch ($columnName) { |
452 | case 'nom': |
453 | $this->addWhereClause($queryBuilder, 'name', '%' . $search . '%', 'LIKE'); |
454 | break; |
455 | case 'collectivite': |
456 | $queryBuilder->andWhere('collectivite.name LIKE :nom') |
457 | ->setParameter('nom', '%' . $search . '%'); |
458 | break; |
459 | case 'service': |
460 | $queryBuilder->andWhere('service.name LIKE :sernom') |
461 | ->setParameter('sernom', '%' . $search . '%'); |
462 | break; |
463 | case 'type': |
464 | $this->addWhereClause($queryBuilder, 'type', $search); |
465 | break; |
466 | case 'commentaire': |
467 | $this->addWhereClause($queryBuilder, 'comment', '%' . $search . '%', 'LIKE'); |
468 | break; |
469 | case 'date': |
470 | $queryBuilder->andWhere('o.createdAt BETWEEN :created_start_date AND :created_finish_date') |
471 | ->setParameter('created_start_date', date_create_from_format('d/m/y', substr($search, 0, 8))->format('Y-m-d 00:00:00')) |
472 | ->setParameter('created_finish_date', date_create_from_format('d/m/y', substr($search, 11, 8))->format('Y-m-d 23:59:59')); |
473 | break; |
474 | case 'updatedAt': |
475 | $queryBuilder->andWhere('o.updatedAt BETWEEN :updated_start_date AND :updated_finish_date') |
476 | ->setParameter('updated_start_date', date_create_from_format('d/m/y', substr($search, 0, 8))->format('Y-m-d 00:00:00')) |
477 | ->setParameter('updated_finish_date', date_create_from_format('d/m/y', substr($search, 11, 8))->format('Y-m-d 23:59:59')); |
478 | break; |
479 | case 'updatedAt': |
480 | $queryBuilder->andWhere('o.updatedAt LIKE :date') |
481 | ->setParameter('date', date_create_from_format('d/m/Y', $search)->format('Y-m-d') . '%'); |
482 | break; |
483 | } |
484 | } |
485 | } |
486 | } |