Code Coverage |
||||||||||
Lines |
Functions and Methods |
Classes and Traits |
||||||||
Total | |
20.62% |
33 / 160 |
|
28.57% |
4 / 14 |
CRAP | |
0.00% |
0 / 1 |
Survey | |
20.62% |
33 / 160 |
|
28.57% |
4 / 14 |
924.16 | |
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 / 15 |
|
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() |
153 | ->getConnection() |
154 | ->prepare($sql) |
155 | ->executeQuery(); |
156 | |
157 | return $stmt->fetchFirstColumn(); |
158 | } |
159 | |
160 | public function findAllByCollectivities(array $collectivities, array $order = [], ?int $limit = null): iterable |
161 | { |
162 | $qb = $this->createQueryBuilder(); |
163 | |
164 | $qb |
165 | ->andWhere( |
166 | $qb->expr()->in('o.collectivity', ':collectivities') |
167 | ) |
168 | ->setParameter('collectivities', $collectivities) |
169 | ; |
170 | |
171 | foreach ($order as $key => $dir) { |
172 | $qb->addOrderBy("o.{$key}", $dir); |
173 | } |
174 | |
175 | if (!\is_null($limit)) { |
176 | $qb |
177 | ->setFirstResult(0) |
178 | ->setMaxResults($limit); |
179 | } |
180 | |
181 | return $qb |
182 | ->getQuery() |
183 | ->getResult() |
184 | ; |
185 | } |
186 | |
187 | public function findAllLate(): array |
188 | { |
189 | $now = new \DateTime(); |
190 | $monthsAgo = $now->sub(\DateInterval::createFromDateString($this->lateSurveyDelayDays . ' days')); |
191 | |
192 | return $this->createQueryBuilder() |
193 | ->andWhere('o.updatedAt < :lastmonth') |
194 | ->setParameter('lastmonth', $monthsAgo->format('Y-m-d')) |
195 | ->getQuery() |
196 | ->getResult(); |
197 | } |
198 | |
199 | protected function addInClauseCollectivities(QueryBuilder $qb, array $collectivities) |
200 | { |
201 | $qb->andWhere( |
202 | $qb->expr()->in('collectivite', ':collectivities') |
203 | ) |
204 | ->setParameter('collectivities', $collectivities) |
205 | ; |
206 | } |
207 | |
208 | public function count(array $criteria = []) |
209 | { |
210 | $qb = $this->createQueryBuilder(); |
211 | if (isset($criteria['collectivity']) && $criteria['collectivity'] instanceof Collection) { |
212 | $qb->leftJoin('o.collectivity', 'collectivite'); |
213 | $this->addInClauseCollectivities($qb, $criteria['collectivity']->toArray()); |
214 | unset($criteria['collectivity']); |
215 | } |
216 | |
217 | if (isset($criteria['collectivity'])) { |
218 | $qb->andWhere('o.collectivity = :collectivity') |
219 | ->setParameter('collectivity', $criteria['collectivity']); |
220 | } |
221 | |
222 | $qb->select('COUNT(o.id)'); |
223 | |
224 | return $qb->getQuery()->getSingleScalarResult(); |
225 | } |
226 | |
227 | public function findPaginated($firstResult, $maxResults, $orderColumn, $orderDir, $searches, $criteria = []) |
228 | { |
229 | $qb = $this->createQueryBuilder() |
230 | ->leftJoin('o.collectivity', 'collectivite') |
231 | ->leftJoin('o.referentiel', 'referentiel'); |
232 | |
233 | if (isset($criteria['collectivity']) && $criteria['collectivity'] instanceof Collection) { |
234 | $this->addInClauseCollectivities($qb, $criteria['collectivity']->toArray()); |
235 | unset($criteria['collectivity']); |
236 | } elseif (isset($criteria['collectivity'])) { |
237 | $qb->andWhere('o.collectivity = :collectivity') |
238 | ->setParameter('collectivity', $criteria['collectivity']); |
239 | } |
240 | |
241 | $this->addTableSearches($qb, $searches); |
242 | $this->addTableOrder($qb, $orderColumn, $orderDir); |
243 | |
244 | $qb = $qb->getQuery(); |
245 | $qb->setFirstResult($firstResult); |
246 | $qb->setMaxResults($maxResults); |
247 | |
248 | return new Paginator($qb); |
249 | } |
250 | |
251 | private function addTableOrder(QueryBuilder $queryBuilder, $orderColumn, $orderDir) |
252 | { |
253 | switch ($orderColumn) { |
254 | case 'collectivity': |
255 | $queryBuilder->addOrderBy('collectivite.name', $orderDir); |
256 | break; |
257 | case 'referentiel': |
258 | $queryBuilder->addOrderBy('referentiel.name', $orderDir); |
259 | break; |
260 | case 'score': |
261 | $queryBuilder->addOrderBy('o.score', $orderDir); |
262 | break; |
263 | case 'updatedAt': |
264 | $queryBuilder->addOrderBy('o.updatedAt', $orderDir); |
265 | break; |
266 | case 'createdAt': |
267 | $queryBuilder->addOrderBy('o.createdAt', $orderDir); |
268 | break; |
269 | } |
270 | } |
271 | |
272 | private function addTableSearches(QueryBuilder $queryBuilder, $searches) |
273 | { |
274 | foreach ($searches as $columnName => $search) { |
275 | switch ($columnName) { |
276 | case 'collectivity': |
277 | $queryBuilder->andWhere('collectivite.name LIKE :collectivity') |
278 | ->setParameter('collectivity', '%' . $search . '%'); |
279 | break; |
280 | case 'referentiel': |
281 | $queryBuilder->andWhere('referentiel.name LIKE :referentiel') |
282 | ->setParameter('referentiel', $search); |
283 | break; |
284 | case 'score': |
285 | $this->addWhereClause($queryBuilder, 'score', '%' . $search . '%', 'LIKE'); |
286 | break; |
287 | case 'updatedAt': |
288 | if (is_string($search)) { |
289 | $queryBuilder->andWhere('o.updatedAt BETWEEN :updated_start_date AND :updated_finish_date') |
290 | ->setParameter('updated_start_date', date_create_from_format('d/m/y', substr($search, 0, 8))->format('Y-m-d 00:00:00')) |
291 | ->setParameter('updated_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 'createdAt': |
295 | if (is_string($search)) { |
296 | $queryBuilder->andWhere('o.createdAt BETWEEN :create_start_date AND :create_finish_date') |
297 | ->setParameter('create_start_date', date_create_from_format('d/m/y', substr($search, 0, 8))->format('Y-m-d 00:00:00')) |
298 | ->setParameter('create_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 | } |