Code Coverage |
||||||||||
Lines |
Functions and Methods |
Classes and Traits |
||||||||
Total | |
7.05% |
17 / 241 |
|
27.78% |
5 / 18 |
CRAP | |
0.00% |
0 / 1 |
Mesurement | |
7.05% |
17 / 241 |
|
27.78% |
5 / 18 |
3352.92 | |
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 / 13 |
|
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()->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 | } |