Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
20.89% covered (danger)
20.89%
33 / 158
28.57% covered (danger)
28.57%
4 / 14
CRAP
0.00% covered (danger)
0.00%
0 / 1
Survey
20.89% covered (danger)
20.89%
33 / 158
28.57% covered (danger)
28.57%
4 / 14
915.49
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
 addCollectivityClause
100.00% covered (success)
100.00%
4 / 4
100.00% covered (success)
100.00%
1 / 1
1
 findLatestByReferentialAndCollectivity
0.00% covered (danger)
0.00%
0 / 11
0.00% covered (danger)
0.00%
0 / 1
2
 findAllByCollectivity
57.89% covered (warning)
57.89%
11 / 19
0.00% covered (danger)
0.00%
0 / 1
10.66
 findPreviousById
100.00% covered (success)
100.00%
15 / 15
100.00% covered (success)
100.00%
1 / 1
1
 averageSurveyDuringLastYear
0.00% covered (danger)
0.00%
0 / 13
0.00% covered (danger)
0.00%
0 / 1
6
 findAllByCollectivities
0.00% covered (danger)
0.00%
0 / 17
0.00% covered (danger)
0.00%
0 / 1
12
 findAllLate
0.00% covered (danger)
0.00%
0 / 7
0.00% covered (danger)
0.00%
0 / 1
2
 addInClauseCollectivities
0.00% covered (danger)
0.00%
0 / 5
0.00% covered (danger)
0.00%
0 / 1
2
 count
0.00% covered (danger)
0.00%
0 / 10
0.00% covered (danger)
0.00%
0 / 1
20
 findPaginated
0.00% covered (danger)
0.00%
0 / 15
0.00% covered (danger)
0.00%
0 / 1
20
 addTableOrder
0.00% covered (danger)
0.00%
0 / 15
0.00% covered (danger)
0.00%
0 / 1
42
 addTableSearches
0.00% covered (danger)
0.00%
0 / 24
0.00% covered (danger)
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
22declare(strict_types=1);
23
24namespace App\Infrastructure\ORM\Maturity\Repository;
25
26use App\Application\Doctrine\Repository\CRUDRepository;
27use App\Application\Traits\RepositoryUtils;
28use App\Domain\Maturity\Model;
29use App\Domain\Maturity\Repository;
30use App\Domain\User\Model\Collectivity;
31use Doctrine\Common\Collections\Collection;
32use Doctrine\ORM\QueryBuilder;
33use Doctrine\ORM\Tools\Pagination\Paginator;
34use Doctrine\Persistence\ManagerRegistry;
35
36class 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}