Code Coverage |
||||||||||
Lines |
Functions and Methods |
Classes and Traits |
||||||||
Total | |
20.89% |
33 / 158 |
|
28.57% |
4 / 14 |
CRAP | |
0.00% |
0 / 1 |
Survey | |
20.89% |
33 / 158 |
|
28.57% |
4 / 14 |
915.49 | |
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 | |||
findLatestByReferentialAndCollectivity | |
0.00% |
0 / 11 |
|
0.00% |
0 / 1 |
2 | |||
findAllByCollectivity | |
57.89% |
11 / 19 |
|
0.00% |
0 / 1 |
10.66 | |||
findPreviousById | |
100.00% |
15 / 15 |
|
100.00% |
1 / 1 |
1 | |||
averageSurveyDuringLastYear | |
0.00% |
0 / 13 |
|
0.00% |
0 / 1 |
6 | |||
findAllByCollectivities | |
0.00% |
0 / 17 |
|
0.00% |
0 / 1 |
12 | |||
findAllLate | |
0.00% |
0 / 7 |
|
0.00% |
0 / 1 |
2 | |||
addInClauseCollectivities | |
0.00% |
0 / 5 |
|
0.00% |
0 / 1 |
2 | |||
count | |
0.00% |
0 / 10 |
|
0.00% |
0 / 1 |
20 | |||
findPaginated | |
0.00% |
0 / 15 |
|
0.00% |
0 / 1 |
20 | |||
addTableOrder | |
0.00% |
0 / 15 |
|
0.00% |
0 / 1 |
42 | |||
addTableSearches | |
0.00% |
0 / 24 |
|
0.00% |
0 / 1 |
90 |
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\Maturity\Repository; |
25 | |
26 | use App\Application\Doctrine\Repository\CRUDRepository; |
27 | use App\Application\Traits\RepositoryUtils; |
28 | use App\Domain\Maturity\Model; |
29 | use App\Domain\Maturity\Repository; |
30 | use App\Domain\User\Model\Collectivity; |
31 | use Doctrine\Common\Collections\Collection; |
32 | use Doctrine\ORM\QueryBuilder; |
33 | use Doctrine\ORM\Tools\Pagination\Paginator; |
34 | use Doctrine\Persistence\ManagerRegistry; |
35 | |
36 | class Survey extends CRUDRepository implements Repository\Survey |
37 | { |
38 | use RepositoryUtils; |
39 | private string $lateSurveyDelayDays; |
40 | |
41 | /** |
42 | * CRUDRepository constructor. |
43 | */ |
44 | public function __construct(ManagerRegistry $registry, string $lateSurveyDelayDays) |
45 | { |
46 | parent::__construct($registry); |
47 | |
48 | $this->lateSurveyDelayDays = $lateSurveyDelayDays; |
49 | } |
50 | |
51 | protected function getModelClass(): string |
52 | { |
53 | return Model\Survey::class; |
54 | } |
55 | |
56 | protected function addCollectivityClause(QueryBuilder $qb, Collectivity $collectivity): QueryBuilder |
57 | { |
58 | return $qb |
59 | ->andWhere('o.collectivity = :collectivity') |
60 | ->setParameter('collectivity', $collectivity) |
61 | ; |
62 | } |
63 | |
64 | public function findLatestByReferentialAndCollectivity(Model\Referentiel $referentiel, Collectivity $collectivity) |
65 | { |
66 | return $this->createQueryBuilder() |
67 | ->select('o') |
68 | ->andWhere('o.referentiel = :referentiel') |
69 | ->setParameter('referentiel', $referentiel) |
70 | ->andWhere('o.collectivity = :collectivity') |
71 | ->setParameter('collectivity', $collectivity) |
72 | ->orderBy('o.createdAt', 'DESC') |
73 | ->setMaxResults(2) |
74 | ->getQuery() |
75 | ->getResult() |
76 | ; |
77 | } |
78 | |
79 | public function findAllByCollectivity(Collectivity $collectivity, array $order = [], ?int $limit = null, array $where = []): array |
80 | { |
81 | $qb = $this->createQueryBuilder(); |
82 | |
83 | if (!\is_null($collectivity)) { |
84 | $this->addCollectivityClause($qb, $collectivity); |
85 | } |
86 | |
87 | foreach ($order as $key => $dir) { |
88 | $qb->addOrderBy("o.{$key}", $dir); |
89 | } |
90 | |
91 | foreach ($where as $key => $value) { |
92 | if (is_int($key)) { |
93 | $qb->andWhere($value); |
94 | } elseif (is_string($key)) { |
95 | $qb->andWhere("o.{$key} = :o_{$key}") |
96 | ->setParameter("o_{$key}", $value); |
97 | } |
98 | } |
99 | |
100 | if (!\is_null($limit)) { |
101 | $qb |
102 | ->setFirstResult(0) |
103 | ->setMaxResults($limit); |
104 | } |
105 | |
106 | return $qb |
107 | ->getQuery() |
108 | ->getResult() |
109 | ; |
110 | } |
111 | |
112 | public function findPreviousById(string $id, int $limit = 1): iterable |
113 | { |
114 | $q = $this->createQueryBuilder() |
115 | ->select('s') |
116 | ->from(Model\Survey::class, 's') |
117 | ->andWhere('o.id = :id') |
118 | ->andWhere('o.collectivity = s.collectivity') |
119 | ->andWhere('o.referentiel = s.referentiel') // Referentiels must match |
120 | ->andWhere('o.createdAt > s.createdAt') |
121 | ->orderBy('s.createdAt', 'DESC') |
122 | ->setMaxResults($limit) |
123 | ->setParameter('id', $id) |
124 | ->getQuery() |
125 | ; |
126 | |
127 | return |
128 | $q->getResult() |
129 | ; |
130 | } |
131 | |
132 | public function averageSurveyDuringLastYear(array $collectivities = []) |
133 | { |
134 | $sql = 'SELECT AVG(a.rcount) FROM ( |
135 | SELECT IF(COUNT(ms.id) > 0, 1, 0) as rcount |
136 | FROM user_collectivity uc |
137 | LEFT OUTER JOIN maturity_survey ms ON (uc.id = ms.collectivity_id AND ms.created_at >= NOW() - INTERVAL 1 YEAR) |
138 | WHERE uc.active = 1'; |
139 | |
140 | if (!empty($collectivities)) { |
141 | $sql .= ' AND uc.id IN ('; |
142 | $sql .= \implode(',', \array_map(function ($collectivity) { |
143 | return '\'' . $collectivity->getId() . '\''; |
144 | }, $collectivities)); |
145 | $sql .= ') '; |
146 | } |
147 | |
148 | $sql .= ' |
149 | GROUP BY uc.id |
150 | ) a'; |
151 | |
152 | $stmt = $this->getManager()->getConnection()->prepare($sql); |
153 | $stmt->execute(); |
154 | |
155 | return $stmt->fetchColumn(); |
156 | } |
157 | |
158 | public function findAllByCollectivities(array $collectivities, array $order = [], ?int $limit = null): iterable |
159 | { |
160 | $qb = $this->createQueryBuilder(); |
161 | |
162 | $qb |
163 | ->andWhere( |
164 | $qb->expr()->in('o.collectivity', ':collectivities') |
165 | ) |
166 | ->setParameter('collectivities', $collectivities) |
167 | ; |
168 | |
169 | foreach ($order as $key => $dir) { |
170 | $qb->addOrderBy("o.{$key}", $dir); |
171 | } |
172 | |
173 | if (!\is_null($limit)) { |
174 | $qb |
175 | ->setFirstResult(0) |
176 | ->setMaxResults($limit); |
177 | } |
178 | |
179 | return $qb |
180 | ->getQuery() |
181 | ->getResult() |
182 | ; |
183 | } |
184 | |
185 | public function findAllLate(): array |
186 | { |
187 | $now = new \DateTime(); |
188 | $monthsAgo = $now->sub(\DateInterval::createFromDateString($this->lateSurveyDelayDays . ' days')); |
189 | |
190 | return $this->createQueryBuilder() |
191 | ->andWhere('o.updatedAt < :lastmonth') |
192 | ->setParameter('lastmonth', $monthsAgo->format('Y-m-d')) |
193 | ->getQuery() |
194 | ->getResult(); |
195 | } |
196 | |
197 | protected function addInClauseCollectivities(QueryBuilder $qb, array $collectivities) |
198 | { |
199 | $qb->andWhere( |
200 | $qb->expr()->in('collectivite', ':collectivities') |
201 | ) |
202 | ->setParameter('collectivities', $collectivities) |
203 | ; |
204 | } |
205 | |
206 | public function count(array $criteria = []) |
207 | { |
208 | $qb = $this->createQueryBuilder(); |
209 | if (isset($criteria['collectivity']) && $criteria['collectivity'] instanceof Collection) { |
210 | $qb->leftJoin('o.collectivity', 'collectivite'); |
211 | $this->addInClauseCollectivities($qb, $criteria['collectivity']->toArray()); |
212 | unset($criteria['collectivity']); |
213 | } |
214 | |
215 | if (isset($criteria['collectivity'])) { |
216 | $qb->andWhere('o.collectivity = :collectivity') |
217 | ->setParameter('collectivity', $criteria['collectivity']); |
218 | } |
219 | |
220 | $qb->select('COUNT(o.id)'); |
221 | |
222 | return $qb->getQuery()->getSingleScalarResult(); |
223 | } |
224 | |
225 | public function findPaginated($firstResult, $maxResults, $orderColumn, $orderDir, $searches, $criteria = []) |
226 | { |
227 | $qb = $this->createQueryBuilder() |
228 | ->leftJoin('o.collectivity', 'collectivite') |
229 | ->leftJoin('o.referentiel', 'referentiel'); |
230 | |
231 | if (isset($criteria['collectivity']) && $criteria['collectivity'] instanceof Collection) { |
232 | $this->addInClauseCollectivities($qb, $criteria['collectivity']->toArray()); |
233 | unset($criteria['collectivity']); |
234 | } elseif (isset($criteria['collectivity'])) { |
235 | $qb->andWhere('o.collectivity = :collectivity') |
236 | ->setParameter('collectivity', $criteria['collectivity']); |
237 | } |
238 | |
239 | $this->addTableSearches($qb, $searches); |
240 | $this->addTableOrder($qb, $orderColumn, $orderDir); |
241 | |
242 | $qb = $qb->getQuery(); |
243 | $qb->setFirstResult($firstResult); |
244 | $qb->setMaxResults($maxResults); |
245 | |
246 | return new Paginator($qb); |
247 | } |
248 | |
249 | private function addTableOrder(QueryBuilder $queryBuilder, $orderColumn, $orderDir) |
250 | { |
251 | switch ($orderColumn) { |
252 | case 'collectivity': |
253 | $queryBuilder->addOrderBy('collectivite.name', $orderDir); |
254 | break; |
255 | case 'referentiel': |
256 | $queryBuilder->addOrderBy('referentiel.name', $orderDir); |
257 | break; |
258 | case 'score': |
259 | $queryBuilder->addOrderBy('o.score', $orderDir); |
260 | break; |
261 | case 'updatedAt': |
262 | $queryBuilder->addOrderBy('o.updatedAt', $orderDir); |
263 | break; |
264 | case 'createdAt': |
265 | $queryBuilder->addOrderBy('o.createdAt', $orderDir); |
266 | break; |
267 | } |
268 | } |
269 | |
270 | private function addTableSearches(QueryBuilder $queryBuilder, $searches) |
271 | { |
272 | foreach ($searches as $columnName => $search) { |
273 | switch ($columnName) { |
274 | case 'collectivity': |
275 | $queryBuilder->andWhere('collectivite.name LIKE :collectivity') |
276 | ->setParameter('collectivity', '%' . $search . '%'); |
277 | break; |
278 | case 'referentiel': |
279 | $queryBuilder->andWhere('referentiel.name LIKE :referentiel') |
280 | ->setParameter('referentiel', $search); |
281 | break; |
282 | case 'score': |
283 | $this->addWhereClause($queryBuilder, 'score', '%' . $search . '%', 'LIKE'); |
284 | break; |
285 | case 'updatedAt': |
286 | if (is_string($search)) { |
287 | $queryBuilder->andWhere('o.updatedAt BETWEEN :updated_start_date AND :updated_finish_date') |
288 | ->setParameter('updated_start_date', date_create_from_format('d/m/y', substr($search, 0, 8))->format('Y-m-d 00:00:00')) |
289 | ->setParameter('updated_finish_date', date_create_from_format('d/m/y', substr($search, 11, 8))->format('Y-m-d 23:59:59')); |
290 | } |
291 | break; |
292 | case 'createdAt': |
293 | if (is_string($search)) { |
294 | $queryBuilder->andWhere('o.createdAt BETWEEN :create_start_date AND :create_finish_date') |
295 | ->setParameter('create_start_date', date_create_from_format('d/m/y', substr($search, 0, 8))->format('Y-m-d 00:00:00')) |
296 | ->setParameter('create_finish_date', date_create_from_format('d/m/y', substr($search, 11, 8))->format('Y-m-d 23:59:59')); |
297 | } |
298 | break; |
299 | } |
300 | } |
301 | } |
302 | } |