Code Coverage |
||||||||||
Lines |
Functions and Methods |
Classes and Traits |
||||||||
Total | |
14.97% |
28 / 187 |
|
40.00% |
6 / 15 |
CRAP | |
0.00% |
0 / 1 |
User | |
14.97% |
28 / 187 |
|
40.00% |
6 / 15 |
1346.72 | |
0.00% |
0 / 1 |
__construct | |
100.00% |
2 / 2 |
|
100.00% |
1 / 1 |
1 | |||
getModelClass | |
100.00% |
1 / 1 |
|
100.00% |
1 / 1 |
1 | |||
addArchivedClause | |
0.00% |
0 / 3 |
|
0.00% |
0 / 1 |
6 | |||
addOrder | |
0.00% |
0 / 3 |
|
0.00% |
0 / 1 |
6 | |||
findOneOrNullByEmail | |
100.00% |
6 / 6 |
|
100.00% |
1 / 1 |
1 | |||
findOneOrNullByForgetPasswordToken | |
100.00% |
6 / 6 |
|
100.00% |
1 / 1 |
1 | |||
findOneOrNullLastLoginUserByCollectivity | |
0.00% |
0 / 7 |
|
0.00% |
0 / 1 |
2 | |||
count | |
0.00% |
0 / 20 |
|
0.00% |
0 / 1 |
20 | |||
findPaginated | |
0.00% |
0 / 21 |
|
0.00% |
0 / 1 |
20 | |||
addTableOrder | |
0.00% |
0 / 46 |
|
0.00% |
0 / 1 |
110 | |||
addTableWhere | |
0.00% |
0 / 42 |
|
0.00% |
0 / 1 |
240 | |||
findOneOrNullBySsoKey | |
0.00% |
0 / 5 |
|
0.00% |
0 / 1 |
2 | |||
findAllNoLogin | |
0.00% |
0 / 12 |
|
0.00% |
0 / 1 |
2 | |||
findNonDpoUsers | |
100.00% |
5 / 5 |
|
100.00% |
1 / 1 |
1 | |||
findNonDpoUsersForCollectivity | |
100.00% |
8 / 8 |
|
100.00% |
1 / 1 |
1 |
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\User\Repository; |
25 | |
26 | use App\Application\Doctrine\Repository\CRUDRepository; |
27 | use App\Application\Traits\RepositoryUtils; |
28 | use App\Domain\User\Dictionary\UserMoreInfoDictionary; |
29 | use App\Domain\User\Dictionary\UserRoleDictionary; |
30 | use App\Domain\User\Model; |
31 | use App\Domain\User\Repository; |
32 | use Doctrine\ORM\QueryBuilder; |
33 | use Doctrine\ORM\Tools\Pagination\Paginator; |
34 | use Doctrine\Persistence\ManagerRegistry; |
35 | |
36 | class User extends CRUDRepository implements Repository\User |
37 | { |
38 | use RepositoryUtils; |
39 | |
40 | private string $inactiveUserDelayDays; |
41 | |
42 | public function __construct(ManagerRegistry $registry, string $inactiveUserDelayDays) |
43 | { |
44 | parent::__construct($registry); |
45 | $this->inactiveUserDelayDays = $inactiveUserDelayDays; |
46 | } |
47 | |
48 | protected function getModelClass(): string |
49 | { |
50 | return Model\User::class; |
51 | } |
52 | |
53 | /** |
54 | * Add archive clause to query. |
55 | */ |
56 | protected function addArchivedClause(QueryBuilder $qb, bool $archived = false): QueryBuilder |
57 | { |
58 | // Get not archived |
59 | if (!$archived) { |
60 | return $qb->andWhere('o.deletedAt is null'); |
61 | } |
62 | |
63 | // Get archived |
64 | return $qb->andWhere('o.deletedAt is not null'); |
65 | } |
66 | |
67 | /** |
68 | * Add order to query. |
69 | */ |
70 | protected function addOrder(QueryBuilder $qb, array $order = []): QueryBuilder |
71 | { |
72 | foreach ($order as $key => $dir) { |
73 | $qb->addOrderBy("o.{$key}", $dir); |
74 | } |
75 | |
76 | return $qb; |
77 | } |
78 | |
79 | /** |
80 | * @throws \Doctrine\ORM\NonUniqueResultException |
81 | */ |
82 | public function findOneOrNullByEmail(string $email): ?Model\User |
83 | { |
84 | return $this->createQueryBuilder() |
85 | ->andWhere('o.email = :email') |
86 | ->setParameter('email', $email) |
87 | ->getQuery() |
88 | ->getOneOrNullResult() |
89 | ; |
90 | } |
91 | |
92 | /** |
93 | * @throws \Doctrine\ORM\NonUniqueResultException |
94 | */ |
95 | public function findOneOrNullByForgetPasswordToken(string $token): ?Model\User |
96 | { |
97 | return $this->createQueryBuilder() |
98 | ->andWhere('o.forgetPasswordToken = :forgetPasswordToken') |
99 | ->setParameter('forgetPasswordToken', $token) |
100 | ->getQuery() |
101 | ->getOneOrNullResult() |
102 | ; |
103 | } |
104 | |
105 | public function findOneOrNullLastLoginUserByCollectivity(Model\Collectivity $collectivity): ?Model\User |
106 | { |
107 | $qb = $this->createQueryBuilder(); |
108 | |
109 | $qb->andWhere($qb->expr()->eq('o.collectivity', ':collectivity')); |
110 | $qb->andWhere($qb->expr()->isNotNull('o.lastLogin')); |
111 | $qb->setParameter('collectivity', $collectivity); |
112 | $qb->addOrderBy('o.lastLogin', 'DESC'); |
113 | $qb->setMaxResults(1); |
114 | |
115 | return $qb->getQuery()->getOneOrNullResult(); |
116 | } |
117 | |
118 | public function count(array $criteria = []) |
119 | { |
120 | $qb = $this |
121 | ->createQueryBuilder() |
122 | ->select('count(o.id)') |
123 | ; |
124 | |
125 | if (\array_key_exists('archive', $criteria)) { |
126 | $this->addArchivedClause($qb, $criteria['archive']); |
127 | unset($criteria['archive']); |
128 | } |
129 | if (\array_key_exists('collectivitesReferees', $criteria)) { |
130 | $qb->leftJoin('o.collectivity', 'collectivite') |
131 | ->andWhere($qb->expr()->in('collectivite.id', ':collectivitesReferees')) |
132 | ->setParameter('collectivitesReferees', $criteria['collectivitesReferees']) |
133 | ->andWhere('JSON_UNQUOTE(JSON_EXTRACT(o.roles, \'$[0]\')) <> :role_admin') |
134 | ->setParameter('role_admin', UserRoleDictionary::ROLE_ADMIN); |
135 | unset($criteria['collectivitesReferees']); |
136 | } |
137 | |
138 | foreach ($criteria as $key => $value) { |
139 | $this->addWhereClause($qb, $key, $value); |
140 | } |
141 | |
142 | return $qb |
143 | ->getQuery() |
144 | ->getSingleScalarResult() |
145 | ; |
146 | } |
147 | |
148 | public function findPaginated($firstResult, $maxResults, $orderColumn, $orderDir, $searches, $criteria = []) |
149 | { |
150 | $qb = $this->createQueryBuilder() |
151 | ->addSelect('collectivite') |
152 | ->leftJoin('o.collectivity', 'collectivite') |
153 | ->leftJoin('o.services', 'services'); |
154 | |
155 | if (\array_key_exists('archive', $criteria)) { |
156 | $this->addArchivedClause($qb, $criteria['archive']); |
157 | unset($criteria['archive']); |
158 | } |
159 | if (\array_key_exists('collectivitesReferees', $criteria)) { |
160 | $qb->andWhere($qb->expr()->in('collectivite.id', ':collectivitesReferees')) |
161 | ->setParameter('collectivitesReferees', $criteria['collectivitesReferees']) |
162 | ->andWhere('JSON_UNQUOTE(JSON_EXTRACT(o.roles, \'$[0]\')) <> :role_admin') |
163 | ->setParameter('role_admin', UserRoleDictionary::ROLE_ADMIN); |
164 | unset($criteria['collectivitesReferees']); |
165 | } |
166 | |
167 | foreach ($criteria as $key => $value) { |
168 | $this->addWhereClause($qb, $key, $value); |
169 | } |
170 | |
171 | $this->addTableOrder($qb, $orderColumn, $orderDir); |
172 | $this->addTableWhere($qb, $searches); |
173 | |
174 | $query = $qb->getQuery(); |
175 | $query->setFirstResult($firstResult); |
176 | $query->setMaxResults($maxResults); |
177 | |
178 | return new Paginator($query); |
179 | } |
180 | |
181 | private function addTableOrder(QueryBuilder $queryBuilder, $orderColumn, $orderDir) |
182 | { |
183 | switch ($orderColumn) { |
184 | case 'prenom': |
185 | $queryBuilder->addOrderBy('o.firstName', $orderDir); |
186 | break; |
187 | case 'nom': |
188 | $queryBuilder->addOrderBy('o.lastName', $orderDir); |
189 | break; |
190 | case 'email': |
191 | $queryBuilder->addOrderBy('o.email', $orderDir); |
192 | break; |
193 | case 'collectivite': |
194 | $queryBuilder->addOrderBy('collectivite.name', $orderDir); |
195 | break; |
196 | case 'roles': |
197 | $queryBuilder->addSelect(" |
198 | CASE |
199 | WHEN JSON_UNQUOTE(JSON_EXTRACT(o.roles, '$[0]')) = :role_admin THEN 1 |
200 | WHEN JSON_UNQUOTE(JSON_EXTRACT(o.roles, '$[0]')) = :role_user THEN 2 |
201 | WHEN JSON_UNQUOTE(JSON_EXTRACT(o.roles, '$[0]')) = :role_preview THEN 3 |
202 | ELSE 4 |
203 | END as HIDDEN json_role"); |
204 | $queryBuilder->addOrderBy('json_role', $orderDir); |
205 | $queryBuilder->setParameters( |
206 | [ |
207 | 'role_admin' => UserRoleDictionary::ROLE_ADMIN, |
208 | 'role_user' => UserRoleDictionary::ROLE_USER, |
209 | 'role_preview' => UserRoleDictionary::ROLE_PREVIEW, |
210 | ] |
211 | ); |
212 | break; |
213 | case 'connexion': |
214 | $queryBuilder->addOrderBy('o.lastLogin', $orderDir); |
215 | break; |
216 | case 'updatedAt': |
217 | $queryBuilder->addOrderBy('o.updatedAt', $orderDir); |
218 | break; |
219 | case 'createdAt': |
220 | $queryBuilder->addOrderBy('o.createdAt', $orderDir); |
221 | break; |
222 | case 'moreInfos': |
223 | $queryBuilder->addSelect(' |
224 | CASE |
225 | WHEN JSON_UNQUOTE(JSON_EXTRACT(o.moreInfos, \'$[0]\')) = :treatment THEN 1 |
226 | WHEN JSON_UNQUOTE(JSON_EXTRACT(o.moreInfos, \'$[0]\')) = :info THEN 2 |
227 | WHEN JSON_UNQUOTE(JSON_EXTRACT(o.moreInfos, \'$[0]\')) = :ope THEN 3 |
228 | WHEN JSON_UNQUOTE(JSON_EXTRACT(o.moreInfos, \'$[0]\')) = :dpd THEN 4 |
229 | ELSE 5 |
230 | END as HIDDEN json_more'); |
231 | $queryBuilder->addOrderBy('json_more', $orderDir); |
232 | $queryBuilder->setParameters( |
233 | [ |
234 | 'treatment' => UserMoreInfoDictionary::MOREINFO_TREATMENT, |
235 | 'info' => UserMoreInfoDictionary::MOREINFO_INFORMATIC, |
236 | 'ope' => UserMoreInfoDictionary::MOREINFO_OPERATIONNAL, |
237 | 'dpd' => UserMoreInfoDictionary::MOREINFO_DPD, |
238 | ] |
239 | ); |
240 | break; |
241 | } |
242 | } |
243 | |
244 | private function addTableWhere(QueryBuilder $queryBuilder, $searches) |
245 | { |
246 | foreach ($searches as $columnName => $search) { |
247 | switch ($columnName) { |
248 | case 'prenom': |
249 | $this->addWhereClause($queryBuilder, 'firstName', '%' . $search . '%', 'LIKE'); |
250 | break; |
251 | case 'nom': |
252 | $this->addWhereClause($queryBuilder, 'lastName', '%' . $search . '%', 'LIKE'); |
253 | break; |
254 | case 'email': |
255 | $this->addWhereClause($queryBuilder, 'email', '%' . $search . '%', 'LIKE'); |
256 | break; |
257 | case 'collectivite': |
258 | $queryBuilder->andWhere('collectivite.name LIKE :collectivite_name') |
259 | ->setParameter('collectivite_name', '%' . $search . '%'); |
260 | break; |
261 | case 'roles': |
262 | $this->addWhereClause($queryBuilder, 'roles', '%' . $search . '%', 'LIKE'); |
263 | break; |
264 | case 'moreInfos': |
265 | $this->addWhereClause($queryBuilder, 'moreInfos', '%' . $search . '%', 'LIKE'); |
266 | break; |
267 | case 'connexion': |
268 | if (is_string($search)) { |
269 | $queryBuilder->andWhere('o.lastLogin BETWEEN :connexion_start_date AND :connexion_finish_date') |
270 | ->setParameter('connexion_start_date', date_create_from_format('d/m/y', substr($search, 0, 8))->format('Y-m-d 00:00:00')) |
271 | ->setParameter('connexion_finish_date', date_create_from_format('d/m/y', substr($search, 11, 8))->format('Y-m-d 23:59:59')); |
272 | } |
273 | break; |
274 | case 'services': |
275 | $queryBuilder->andWhere('services.name LIKE :service_name') |
276 | ->setParameter('service_name', '%' . $search . '%'); |
277 | break; |
278 | case 'createdAt': |
279 | if (is_string($search)) { |
280 | $queryBuilder->andWhere('o.createdAt BETWEEN :created_start_date AND :created_finish_date') |
281 | ->setParameter('created_start_date', date_create_from_format('d/m/y', substr($search, 0, 8))->format('Y-m-d 00:00:00')) |
282 | ->setParameter('created_finish_date', date_create_from_format('d/m/y', substr($search, 11, 8))->format('Y-m-d 23:59:59')); |
283 | } |
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 | } |
293 | } |
294 | } |
295 | |
296 | /** |
297 | * @throws \Doctrine\ORM\NonUniqueResultException |
298 | * @throws \Exception |
299 | */ |
300 | public function findOneOrNullBySsoKey(string $ssoKey): ?Model\User |
301 | { |
302 | return $this->createQueryBuilder() |
303 | ->andWhere('o.ssoKey = :ssoKey') |
304 | ->setParameter('ssoKey', $ssoKey) |
305 | ->getQuery() |
306 | ->getOneOrNullResult(); |
307 | } |
308 | |
309 | public function findAllNoLogin() |
310 | { |
311 | $now = new \DateTime(); |
312 | $monthsAgo = $now->sub(\DateInterval::createFromDateString($this->inactiveUserDelayDays . ' days')); |
313 | $qb = $this->createQueryBuilder(); |
314 | $query = $qb->where($qb->expr()->isNull('o.lastLogin')) |
315 | ->andWhere($qb->expr()->orX( |
316 | $qb->expr()->isNull('o.createdAt'), |
317 | 'o.createdAt < :monthsAgo' |
318 | )) |
319 | ->setParameter('monthsAgo', $monthsAgo->format('Y-m-d H:i')) |
320 | ->getQuery(); |
321 | |
322 | return $query->getResult() |
323 | ; |
324 | } |
325 | |
326 | public function findNonDpoUsers() |
327 | { |
328 | $qb = $this->createQueryBuilder(); |
329 | $qb->andWhere('o.roles NOT LIKE :role') |
330 | // TODO add andwhere with "is_dpo" |
331 | ->setParameter('role', sprintf('"%s"', '%ROLE_ADMIN%')); |
332 | |
333 | return $qb->getQuery()->getResult(); |
334 | } |
335 | |
336 | public function findNonDpoUsersForCollectivity(Model\Collectivity $collectivity) |
337 | { |
338 | $qb = $this->createQueryBuilder(); |
339 | $qb->andWhere('o.roles NOT LIKE :role') |
340 | // TODO add andwhere with "is_dpo" |
341 | ->setParameter('role', sprintf('"%s"', '%ROLE_ADMIN%')) |
342 | ->andWhere('o.collectivity = :collectivity') |
343 | ->setParameter('collectivity', $collectivity) |
344 | ; |
345 | |
346 | return $qb->getQuery()->getResult(); |
347 | } |
348 | } |