Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
14.97% covered (danger)
14.97%
28 / 187
40.00% covered (danger)
40.00%
6 / 15
CRAP
0.00% covered (danger)
0.00%
0 / 1
User
14.97% covered (danger)
14.97%
28 / 187
40.00% covered (danger)
40.00%
6 / 15
1346.72
0.00% covered (danger)
0.00%
0 / 1
 __construct
100.00% covered (success)
100.00%
2 / 2
100.00% covered (success)
100.00%
1 / 1
1
 getModelClass
100.00% covered (success)
100.00%
1 / 1
100.00% covered (success)
100.00%
1 / 1
1
 addArchivedClause
0.00% covered (danger)
0.00%
0 / 3
0.00% covered (danger)
0.00%
0 / 1
6
 addOrder
0.00% covered (danger)
0.00%
0 / 3
0.00% covered (danger)
0.00%
0 / 1
6
 findOneOrNullByEmail
100.00% covered (success)
100.00%
6 / 6
100.00% covered (success)
100.00%
1 / 1
1
 findOneOrNullByForgetPasswordToken
100.00% covered (success)
100.00%
6 / 6
100.00% covered (success)
100.00%
1 / 1
1
 findOneOrNullLastLoginUserByCollectivity
0.00% covered (danger)
0.00%
0 / 7
0.00% covered (danger)
0.00%
0 / 1
2
 count
0.00% covered (danger)
0.00%
0 / 20
0.00% covered (danger)
0.00%
0 / 1
20
 findPaginated
0.00% covered (danger)
0.00%
0 / 21
0.00% covered (danger)
0.00%
0 / 1
20
 addTableOrder
0.00% covered (danger)
0.00%
0 / 46
0.00% covered (danger)
0.00%
0 / 1
110
 addTableWhere
0.00% covered (danger)
0.00%
0 / 42
0.00% covered (danger)
0.00%
0 / 1
240
 findOneOrNullBySsoKey
0.00% covered (danger)
0.00%
0 / 5
0.00% covered (danger)
0.00%
0 / 1
2
 findAllNoLogin
0.00% covered (danger)
0.00%
0 / 12
0.00% covered (danger)
0.00%
0 / 1
2
 findNonDpoUsers
100.00% covered (success)
100.00%
5 / 5
100.00% covered (success)
100.00%
1 / 1
1
 findNonDpoUsersForCollectivity
100.00% covered (success)
100.00%
8 / 8
100.00% covered (success)
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
22declare(strict_types=1);
23
24namespace App\Infrastructure\ORM\User\Repository;
25
26use App\Application\Doctrine\Repository\CRUDRepository;
27use App\Application\Traits\RepositoryUtils;
28use App\Domain\User\Dictionary\UserMoreInfoDictionary;
29use App\Domain\User\Dictionary\UserRoleDictionary;
30use App\Domain\User\Model;
31use App\Domain\User\Repository;
32use Doctrine\ORM\QueryBuilder;
33use Doctrine\ORM\Tools\Pagination\Paginator;
34use Doctrine\Persistence\ManagerRegistry;
35
36class 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}