|
1
|
|
|
<?php |
|
2
|
|
|
|
|
3
|
|
|
declare(strict_types=1); |
|
4
|
|
|
|
|
5
|
|
|
namespace Application\Repository; |
|
6
|
|
|
|
|
7
|
|
|
use Application\DBAL\Types\BookingStatusType; |
|
8
|
|
|
use Application\Model\Bookable; |
|
9
|
|
|
use Application\Model\Booking; |
|
10
|
|
|
use Application\Model\User; |
|
11
|
|
|
use Cake\Chronos\Date; |
|
12
|
|
|
use Doctrine\DBAL\Connection; |
|
13
|
|
|
use Doctrine\ORM\Query\ResultSetMappingBuilder; |
|
14
|
|
|
|
|
15
|
|
|
class BookingRepository extends AbstractRepository |
|
16
|
|
|
{ |
|
17
|
|
|
/** |
|
18
|
|
|
* All non-terminated bookings |
|
19
|
|
|
* for active, periodic bookable |
|
20
|
|
|
* for active member/responsible/administrator |
|
21
|
|
|
* but that do not already have an existing transaction_line in the user account for this year |
|
22
|
|
|
* |
|
23
|
|
|
* @return Booking[] |
|
24
|
|
|
*/ |
|
25
|
1 |
|
public function getAllToInvoice(): array |
|
26
|
|
|
{ |
|
27
|
1 |
|
$rsm = new ResultSetMappingBuilder($this->getEntityManager(), ResultSetMappingBuilder::COLUMN_RENAMING_INCREMENT); |
|
28
|
1 |
|
$rsm->addRootEntityFromClassMetadata(Booking::class, 'booking'); |
|
29
|
1 |
|
$rsm->addJoinedEntityFromClassMetadata(Bookable::class, 'bookable', 'booking', 'bookable'); |
|
30
|
1 |
|
$selectClause = $rsm->generateSelectClause(); |
|
31
|
|
|
|
|
32
|
|
|
$sql = " |
|
33
|
1 |
|
SELECT $selectClause FROM booking |
|
34
|
|
|
JOIN bookable ON booking.bookable_id = bookable.id |
|
35
|
|
|
JOIN user ON booking.owner_id = user.id AND user.role IN (:roles) AND user.status = :userStatus |
|
36
|
|
|
LEFT JOIN account ON user.id = account.owner_id |
|
37
|
|
|
LEFT JOIN transaction_line ON account.id = transaction_line.debit_id AND transaction_line.bookable_id = bookable.id AND transaction_line.creation_date >= :currentYear |
|
38
|
|
|
WHERE |
|
39
|
|
|
booking.start_date < :nextYear |
|
40
|
|
|
AND (booking.end_date IS NULL) |
|
41
|
|
|
AND bookable.is_active |
|
42
|
|
|
AND bookable.periodic_price != 0 |
|
43
|
|
|
AND transaction_line.id IS NULL |
|
44
|
|
|
ORDER BY booking.owner_id ASC, bookable.name ASC |
|
45
|
|
|
"; |
|
46
|
|
|
|
|
47
|
1 |
|
$query = $this->getEntityManager()->createNativeQuery($sql, $rsm) |
|
48
|
1 |
|
->setParameter('bookingStatus', BookingStatusType::BOOKED) |
|
49
|
1 |
|
->setParameter('userStatus', User::STATUS_ACTIVE) |
|
50
|
1 |
|
->setParameter('currentYear', Date::now()->firstOfYear()->toDateString()) |
|
51
|
1 |
|
->setParameter('nextYear', Date::now()->firstOfYear()->addYear()->toDateString()) |
|
52
|
1 |
|
->setParameter('roles', [User::ROLE_MEMBER, User::ROLE_RESPONSIBLE, User::ROLE_ADMINISTRATOR], Connection::PARAM_STR_ARRAY); |
|
53
|
|
|
|
|
54
|
1 |
|
$result = $query->getResult(); |
|
55
|
|
|
|
|
56
|
1 |
|
return $result; |
|
57
|
|
|
} |
|
58
|
|
|
} |
|
59
|
|
|
|
Ecodev /
my-ichtus
