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