Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
94.05%
174 / 185
78.95%
15 / 19
 CRAP
0.00%
0 / 1
TransactionRepository
94.05%
174 / 185
78.95%
15 / 19
 38.30
0.00%
0 / 1
 __construct
100.00%
1 / 1
100.00%
1 / 1
 1
 findByStoreAndYear
100.00%
9 / 9
100.00%
1 / 1
 1
 findByStoreYearAndUser
100.00%
11 / 11
100.00%
1 / 1
 1
 getSaldos
0.00%
0 / 6
0.00%
0 / 1
 2
 getSaldo
100.00%
6 / 6
100.00%
1 / 1
 1
 getSaldoAnterior
100.00%
8 / 8
100.00%
1 / 1
 1
 getSaldoALaFecha
100.00%
8 / 8
100.00%
1 / 1
 1
 getSaldoALaFechaByStores
93.33%
14 / 15
0.00%
0 / 1
 3.00
 findMonthPayments
100.00%
14 / 14
100.00%
1 / 1
 1
 findMonthPaymentsByStores
100.00%
19 / 19
100.00%
1 / 1
 3
 findByDate
100.00%
10 / 10
100.00%
1 / 1
 1
 findByIds
100.00%
8 / 8
100.00%
1 / 1
 2
 getPagosPorAno
100.00%
14 / 14
100.00%
1 / 1
 2
 getRawList
100.00%
12 / 12
100.00%
1 / 1
 3
 hasCriteria
100.00%
2 / 2
100.00%
1 / 1
 2
 applySearchFilters
100.00%
18 / 18
100.00%
1 / 1
 7
 getLastRecipeNo
71.43%
5 / 7
0.00%
0 / 1
 2.09
 checkChargementRequired
80.00%
8 / 10
0.00%
0 / 1
 4.13
 getLastChargementDate
100.00%
7 / 7
100.00%
1 / 1
 1
1<?php
2
3declare(strict_types=1);
4
5namespace App\Repository;
6
7use App\Entity\Store;
8use App\Entity\Transaction;
9use App\Entity\User;
10use App\Helper\Paginator\PaginatorOptions;
11use App\Helper\Paginator\PaginatorRepoTrait;
12use App\Type\TransactionType;
13use DateTime;
14use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
15use Doctrine\ORM\NonUniqueResultException;
16use Doctrine\ORM\NoResultException;
17use Doctrine\ORM\Query;
18use Doctrine\ORM\QueryBuilder;
19use Doctrine\ORM\Tools\Pagination\Paginator;
20use Doctrine\Persistence\ManagerRegistry;
21use Exception;
22use Symfony\Component\Clock\ClockInterface;
23
24/**
25 * @method Transaction|null find($id, $lockMode = null, $lockVersion = null)
26 * @method Transaction|null findOneBy(array<string, mixed> $criteria, ?array<string, string> $orderBy = null)
27 * @method Transaction[]    findAll()
28 * @method Transaction[]    findBy(array<string, mixed> $criteria, ?array<string, string> $orderBy = null, $limit = null, $offset = null)
29 *
30 * @extends ServiceEntityRepository<Transaction>
31 */
32class TransactionRepository extends ServiceEntityRepository
33{
34    use PaginatorRepoTrait;
35
36    public function __construct(ManagerRegistry $registry, private readonly ClockInterface $clock)
37    {
38        parent::__construct($registry, Transaction::class);
39    }
40
41    /**
42     * @return Transaction[]
43     */
44    public function findByStoreAndYear(Store $store, int $year): array
45    {
46        /** @var Transaction[] $result */
47        $result = $this->createQueryBuilder('p')
48            ->where('p.store = :store')
49            ->andWhere('YEAR(p.date) = :year')
50            ->setParameter('store', $store->getId())
51            ->setParameter('year', $year)
52            ->orderBy('p.date, p.type', 'ASC')
53            ->getQuery()
54            ->getResult();
55
56        return $result;
57    }
58
59    /**
60     * @return Transaction[]
61     */
62    public function findByStoreYearAndUser(
63        Store $store,
64        int $year,
65        User $user
66    ): array
67    {
68        /** @var Transaction[] $result */
69        $result = $this->createQueryBuilder('p')
70            ->where('p.store = :store')
71            ->andWhere('YEAR(p.date) = :year')
72            ->andWhere('p.user = :user')
73            ->setParameter('store', $store->getId())
74            ->setParameter('year', $year)
75            ->setParameter('user', $user)
76            ->orderBy('p.date, p.type', 'ASC')
77            ->getQuery()
78            ->getResult();
79
80        return $result;
81    }
82
83    /**
84     * @return array<float>
85     */
86    public function getSaldos(): array
87    {
88        /** @var array<float> $result */
89        $result = $this->createQueryBuilder('t')
90            ->select('t as data, SUM(t.amount) AS amount')
91            ->groupBy('t.store')
92            ->getQuery()
93            ->getResult();
94
95        return $result;
96    }
97
98    /**
99     * @throws NoResultException
100     * @throws NonUniqueResultException
101     */
102    public function getSaldo(Store $store): ?float
103    {
104        return (float)$this->createQueryBuilder('t')
105            ->select('SUM(t.amount) AS amount')
106            ->where('t.store = :store')
107            ->setParameter('store', $store->getId())
108            ->getQuery()
109            ->getSingleScalarResult();
110    }
111
112    /**
113     * @return int|mixed|string
114     *
115     * @throws NoResultException
116     * @throws NonUniqueResultException
117     */
118    public function getSaldoAnterior(Store $store, int $year): mixed
119    {
120        return $this->createQueryBuilder('t')
121            ->select('SUM(t.amount)')
122            ->where('t.store = :store')
123            ->andWhere('YEAR(t.date) < :year')
124            ->setParameter('store', $store->getId())
125            ->setParameter('year', $year)
126            ->getQuery()
127            ->getSingleScalarResult();
128    }
129
130    /**
131     * @return int|mixed|string
132     */
133    public function getSaldoALaFecha(Store $store, string $date): mixed
134    {
135        return $this->createQueryBuilder('t')
136            ->select('SUM(t.amount)')
137            ->where('t.store = :store')
138            ->andWhere('t.date < :date')
139            ->setParameter('store', $store->getId())
140            ->setParameter('date', $date)
141            ->getQuery()
142            ->getSingleScalarResult();
143    }
144
145    /**
146     * @param array<int> $storeIds
147     * @return array<int, mixed>
148     */
149    public function getSaldoALaFechaByStores(array $storeIds, string $date): array
150    {
151        if ($storeIds === []) {
152            return [];
153        }
154
155        /** @var array<array{storeId: string, total: mixed}> $rows */
156        $rows = $this->createQueryBuilder('t')
157            ->select('IDENTITY(t.store) as storeId, SUM(t.amount) as total')
158            ->andWhere('t.store IN (:storeIds)')
159            ->andWhere('t.date < :date')
160            ->setParameter('storeIds', $storeIds)
161            ->setParameter('date', $date)
162            ->groupBy('t.store')
163            ->getQuery()
164            ->getResult();
165
166        $result = [];
167        foreach ($rows as $row) {
168            $result[(int) $row['storeId']] = $row['total'];
169        }
170
171        return $result;
172    }
173
174    /**
175     * @return array<float>
176     */
177    public function findMonthPayments(
178        Store $store,
179        int $month,
180        int $year
181    ): array
182    {
183        /** @var array<float> $result */
184        $result = $this->createQueryBuilder('p')
185            ->where('p.store = :store')
186            ->andWhere('MONTH(p.date) = :month')
187            ->andWhere('YEAR(p.date) = :year')
188            ->andWhere('p.type =  :type1 OR p.type = :type2')
189            ->setParameter('store', $store->getId())
190            ->setParameter('month', $month)
191            ->setParameter('year', $year)
192            ->setParameter('type1', TransactionType::payment)
193            ->setParameter('type2', TransactionType::adjustment)
194            ->orderBy('p.date', 'ASC')
195            ->getQuery()
196            ->getResult();
197
198        return $result;
199    }
200
201    /**
202     * @param array<int> $storeIds
203     * @return array<int, Transaction[]>
204     */
205    public function findMonthPaymentsByStores(array $storeIds, int $month, int $year): array
206    {
207        if ($storeIds === []) {
208            return [];
209        }
210
211        /** @var Transaction[] $transactions */
212        $transactions = $this->createQueryBuilder('p')
213            ->andWhere('p.store IN (:storeIds)')
214            ->andWhere('MONTH(p.date) = :month')
215            ->andWhere('YEAR(p.date) = :year')
216            ->andWhere('p.type = :type1 OR p.type = :type2')
217            ->setParameter('storeIds', $storeIds)
218            ->setParameter('month', $month)
219            ->setParameter('year', $year)
220            ->setParameter('type1', TransactionType::payment)
221            ->setParameter('type2', TransactionType::adjustment)
222            ->orderBy('p.date', 'ASC')
223            ->getQuery()
224            ->getResult();
225
226        $result = [];
227        foreach ($transactions as $transaction) {
228            $result[(int) $transaction->getStore()->getId()][] = $transaction;
229        }
230
231        return $result;
232    }
233
234    /**
235     * @return Transaction[]
236     */
237    public function findByDate(int $year, int $month): array
238    {
239        /** @var Transaction[] $result */
240        $result = $this->createQueryBuilder('t')
241            ->andWhere('YEAR(t.date) = :year')
242            ->andWhere('MONTH(t.date) = :month')
243            ->andWhere('t.type = :type')
244            ->setParameter('year', $year)
245            ->setParameter('month', $month)
246            ->setParameter('type', TransactionType::payment)
247            ->getQuery()
248            ->getResult();
249
250        return $result;
251    }
252
253    /**
254     * @param array<int> $ids
255     * @return Transaction[]
256     */
257    public function findByIds(array $ids): array
258    {
259        if ($ids === []) {
260            return [];
261        }
262
263        /** @var Transaction[] $result */
264        $result = $this->createQueryBuilder('t')
265            ->andWhere('t.id IN (:ids)')
266            ->setParameter('ids', $ids)
267            ->getQuery()
268            ->getResult();
269
270        return $result;
271    }
272
273    /**
274     * @return array<int|string, array<int, array<int, array<int, Transaction>>>>
275     */
276    public function getPagosPorAno(int $year): array
277    {
278        /**
279         * @var Transaction[] $transactions
280         */
281        $transactions = $this->createQueryBuilder('t')
282            ->where('YEAR(t.date) = :year')
283            ->andWhere('t.type = :type')
284            ->setParameter('year', $year)
285            ->setParameter('type', TransactionType::payment)
286            ->getQuery()
287            ->getResult();
288
289        $payments = [];
290
291        foreach ($transactions as $transaction) {
292            $mes = (int)$transaction->getDate()->format('m');
293            $day = (int)$transaction->getDate()->format('d');
294
295            $payments[(int)$transaction->getStore()->getId()][$mes][$day][]
296                = $transaction;
297        }
298
299        return $payments;
300    }
301
302    /**
303     * @return Paginator<Query>
304     */
305    public function getRawList(PaginatorOptions $options): Paginator
306    {
307        $criteria = $options->getCriteria();
308
309        $query = $this->createQueryBuilder('t')
310            ->orderBy('t.'.$options->getOrder(), $options->getOrderDir());
311
312        if (isset($criteria['type']) && $criteria['type']) {
313            $query->where('t.type = :type')
314                ->setParameter('type', (int)$criteria['type']);
315        }
316
317        $this->applySearchFilters($query, $options);
318
319        return $this->paginate(
320            $query->getQuery(),
321            $options->getPage(),
322            $options->getLimit()
323        );
324    }
325
326    private function hasCriteria(PaginatorOptions $options, string $key): bool
327    {
328        $value = $options->searchCriteria($key);
329
330        return $value !== '' && $value !== '0';
331    }
332
333    private function applySearchFilters(QueryBuilder $query, PaginatorOptions $options): void
334    {
335        if ($this->hasCriteria($options, 'amount')) {
336            $query->andWhere('t.amount = :amount')
337                ->setParameter('amount', (float)$options->searchCriteria('amount'));
338        }
339
340        if ($this->hasCriteria($options, 'store')) {
341            $query->andWhere('t.store = :store')
342                ->setParameter('store', (int)$options->searchCriteria('store'));
343        }
344
345        if ($this->hasCriteria($options, 'date_from')) {
346            $query->andWhere('t.date >= :date_from')
347                ->setParameter('date_from', $options->searchCriteria('date_from'));
348        }
349
350        if ($this->hasCriteria($options, 'date_to')) {
351            $query->andWhere('t.date <= :date_to')
352                ->setParameter('date_to', $options->searchCriteria('date_to'));
353        }
354
355        if ($this->hasCriteria($options, 'recipe')) {
356            $query->andWhere('t.recipeNo = :recipe')
357                ->setParameter('recipe', (int)$options->searchCriteria('recipe'));
358        }
359
360        if ($this->hasCriteria($options, 'comment')) {
361            $query->andWhere('t.comment LIKE :searchTerm')
362                ->setParameter('searchTerm', '%'.$options->searchCriteria('comment').'%');
363        }
364    }
365
366    public function getLastRecipeNo(): int
367    {
368        try {
369            $number = (int)$this->createQueryBuilder('t')
370                ->select('MAX(t.recipeNo)')
371                ->getQuery()
372                ->getSingleScalarResult();
373        } catch (Exception) {
374            $number = 0;
375        }
376
377        return $number;
378    }
379
380    public function checkChargementRequired(): bool
381    {
382        $now = $this->clock->now();
383        $currentYear = (int) $now->format('Y');
384        $lastChargedYear = (int)$this->getLastChargementDate()->format('Y');
385
386        if ($lastChargedYear < $currentYear) {
387            return true;
388        }
389
390        $currentMonth = (int) $now->format('m');
391        $lastChargedMonth = (int)$this->getLastChargementDate()->format('m');
392
393        if (12 === $currentMonth && 1 === $lastChargedMonth) {
394            return true;
395        }
396
397        return $lastChargedMonth < $currentMonth;
398    }
399
400    public function getLastChargementDate(): DateTime
401    {
402        $date = $this->createQueryBuilder('t')
403            ->select('MAX(t.date)')
404            ->andWhere('t.type = :type')
405            ->setParameter('type', TransactionType::rent)
406            ->getQuery()
407            ->getSingleScalarResult();
408
409        return new DateTime((string)$date);
410    }
411}