src/Repository/ReservationRepository.php line 286

  1. <?php
  3. namespace App\Repository;
  5. use App\Entity\Habitat;
  6. use App\Entity\Property;
  7. use App\Entity\Reservation;
  8. use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
  9. use Doctrine\ORM\NoResultException;
  10. use Doctrine\Persistence\ManagerRegistry;
  12. /**
  13.  * @extends ServiceEntityRepository<Reservation>
  14.  *
  15.  * @method Reservation|null find($id, $lockMode = null, $lockVersion = null)
  16.  * @method Reservation|null findOneBy(array $criteria, array $orderBy = null)
  17.  * @method Reservation[]    findAll()
  18.  * @method Reservation[]    findBy(array $criteria, array $orderBy = null, $limit = null, $offset = null)
  19.  */
  20. class ReservationRepository extends ServiceEntityRepository
  21. {
  22.     public function __construct(ManagerRegistry $registry)
  23.     {
  24.         parent::__construct($registry, Reservation::class);
  25.     }
  27.     public function add(Reservation $entity, bool $flush = false): void
  28.     {
  29.         $this->getEntityManager()->persist($entity);
  31.         if ($flush) {
  32.             $this->getEntityManager()->flush();
  33.         }
  34.     }
  36.     public function remove(Reservation $entity, bool $flush = false): void
  37.     {
  38.         $this->getEntityManager()->remove($entity);
  40.         if ($flush) {
  41.             $this->getEntityManager()->flush();
  42.         }
  43.     }
  45.     /**
  46.      * Return booked intervals for a habitat in a window (optional).
  47.      *
  48.      * @param int                  $habitatId
  49.      * @param \DateTimeInterface|null $from
  50.      * @param \DateTimeInterface|null $to
  51.      * @param int                  $marchandId
  52.      * @param string[]             $statuses
  53.      * @return array<array{checkin:\DateTimeInterface, checkout:\DateTimeInterface}>
  54.      */
  55.     public function findBookedRanges(int $habitatId, ?\DateTimeInterface $from = null, ?\DateTimeInterface $to = null, int $marchandId, array $statuses = []): array
  56.     {
  57.         $qb = $this->createQueryBuilder('r')
  58.             ->join('r.marchand', 'm')
  59.             ->join('r.habitat', 'h')
  60.             ->andWhere('m.id = :mid')->setParameter('mid', $marchandId)
  61.             ->andWhere('h.id = :hid')->setParameter('hid', $habitatId);
  63.         // Optional status filter (only certain ones block dates)
  64.         if (!empty($statuses)) {
  65.             $qb->andWhere('r.status IN (:statuses)')
  66.                 ->setParameter('statuses', $statuses);
  67.         }
  69.         // Optional window optimization
  70.         if ($from) {
  71.             $qb->andWhere('r.checkOutDate > :from')->setParameter('from', $from);
  72.         }
  73.         if ($to) {
  74.             $qb->andWhere('r.checkInDate < :to')->setParameter('to', $to);
  75.         }
  77.         $qb->orderBy('r.checkInDate', 'ASC');
  79.         $rows = $qb->getQuery()->getResult();
  81.         return array_map(function(Reservation $r) {
  82.             return [
  83.                 'checkin'  => $r->getCheckInDate(),
  84.                 'checkout' => $r->getCheckOutDate(),
  85.             ];
  86.         }, $rows);
  87.     }
  89.     /**
  90.      * True if any reservation overlaps with [checkin, checkout) for the given habitat.
  91.      * Overlap rule (half-open interval):
  92.      *   [checkIn, checkOut) overlaps [checkin, checkout)  <=>  checkIn < checkout AND checkOut > checkin
  93.      *
  94.      * @param int                   $habitatId
  95.      * @param \DateTimeInterface    $checkin    inclusive
  96.      * @param \DateTimeInterface    $checkout   exclusive
  97.      * @param string[]|null         $statuses   null => default blocking statuses (tentative, confirmed, arrived);
  98.      *                                          []   => no status filter; custom array to override
  99.      */
  100.     public function hasOverlap(
  101.         int $habitatId,
  102.         \DateTimeInterface $checkin,
  103.         \DateTimeInterface $checkout,
  104.         ?array $statuses = null
  105.     ): bool {
  106.         if ($checkout <= $checkin) {
  107.             throw new \InvalidArgumentException('checkout must be strictly after checkin.');
  108.         }
  110.         // Normalize (use DATE at midnight if your columns are DATE)
  111.         $ci = $checkin instanceof \DateTimeImmutable ? \DateTime::createFromImmutable($checkin) : (clone $checkin);
  112.         $co = $checkout instanceof \DateTimeImmutable ? \DateTime::createFromImmutable($checkout) : (clone $checkout);
  113.         $ci->setTime(0, 0, 0);
  114.         $co->setTime(0, 0, 0);
  116.         // Default statuses that block dates if none provided
  117.         if ($statuses === null) {
  118.             $statuses = [
  119.                 Reservation::STATUS_TENTATIVE,
  120.                 Reservation::STATUS_CONFIRMED,
  121.                 Reservation::STATUS_ARRIVED,
  122.             ];
  123.         }
  125.         $qb = $this->createQueryBuilder('r')
  126.             ->select('COUNT(r.id)')
  127.             ->join('r.habitat', 'h')
  128.             ->andWhere('h.id = :hid')
  129.             // half-open overlap condition:
  130.             ->andWhere('r.checkInDate < :reqCheckout')
  131.             ->andWhere('r.checkOutDate > :reqCheckin')
  132.             ->setParameter('hid', $habitatId)
  133.             ->setParameter('reqCheckin', $ci)
  134.             ->setParameter('reqCheckout', $co);
  136.         // Optional status filter
  137.         if ($statuses !== []) {
  138.             $qb->andWhere('r.status IN (:statuses)')
  139.                 ->setParameter('statuses', $statuses);
  140.         }
  142.         $count = (int) $qb->getQuery()->getSingleScalarResult();
  144.         return $count > 0;
  145.     }
  148.     public function listData(array $filters, int $marchandId): array
  149.     {
  150.         $from       = $filters['from']       ?? null;   // \DateTimeInterface|null
  151.         $to         = $filters['to']         ?? null;   // \DateTimeInterface|null
  152.         $status     = $filters['status']     ?? 'all';  // string
  153.         $termFilter = $filters['term']       ?? 'all';  // 'all'|'night'|'week'|'month'
  154.         $propertyId = $filters['propertyId'] ?? null;   // int|null
  156.         // Base QB
  157.         $qb = $this->createQueryBuilder('r')
  158.             ->join('r.marchand', 'm')
  159.             ->leftJoin('r.habitat', 'h')
  160.             ->leftJoin('r.locataire', 't')
  161.             ->addSelect('h.libelle AS property')
  162.             ->addSelect("CONCAT(COALESCE(t.name,''),' ',COALESCE(t.lastname,'')) AS locataire")
  163.             // include term and totalAmount explicitly
  164.             ->addSelect('r.id, r.code, r.status, r.term, r.checkInDate, r.checkOutDate, r.totalAmount')
  165.             ->andWhere('m.id = :mid')->setParameter('mid', $marchandId);
  167.         // Filters
  168.         if ($from instanceof \DateTimeInterface) {
  169.             $from0 = $from instanceof \DateTimeImmutable ? $from : \DateTimeImmutable::createFromMutable($from);
  170.             $qb->andWhere('r.checkInDate >= :from')->setParameter('from', $from0->setTime(0,0));
  171.         }
  172.         if ($to instanceof \DateTimeInterface) {
  173.             $to2359 = $to instanceof \DateTimeImmutable ? $to : \DateTimeImmutable::createFromMutable($to);
  174.             $qb->andWhere('r.checkOutDate <= :to')->setParameter('to', $to2359->setTime(23,59,59));
  175.         }
  176.         if ($status !== 'all') {
  177.             $qb->andWhere('r.status = :st')->setParameter('st', $status);
  178.         }
  179.         if ($termFilter !== 'all') {
  180.             $qb->andWhere('r.term = :tm')->setParameter('tm', $termFilter);
  181.         }
  182.         if ($propertyId) {
  183.             $qb->andWhere('h.id = :pid')->setParameter('pid', $propertyId);
  184.         }
  186.         $rows = $qb->orderBy('r.checkInDate', 'DESC')->getQuery()->getArrayResult();
  188.         // Helpers
  189.         $toImmutable = static function ($v): \DateTimeImmutable {
  190.             if ($v instanceof \DateTimeImmutable) return $v;
  191.             if ($v instanceof \DateTime) return \DateTimeImmutable::createFromMutable($v);
  192.             return new \DateTimeImmutable((string)$v);
  193.         };
  195.         // Compute deriveds
  196.         $active = 0;
  197.         $upcoming = 0;
  198.         $revenue = 0.0;
  199.         $count = count($rows);
  200.         $today = new \DateTimeImmutable('today');
  202.         $revenueStatuses = ['confirmed', 'confirmée', 'completed'];
  204.         foreach ($rows as &$r) {
  205.             $ci = $toImmutable($r['checkInDate'] ?? null);
  206.             $co = $toImmutable($r['checkOutDate'] ?? null);
  208.             $r['nights'] = max(0, (int)$co->diff($ci)->format('%a'));
  210.             if ($ci <= $today && $today < $co) {
  211.                 $active++;
  212.             }
  213.             if ($ci > $today && $ci <= $today->modify('+7 days')) {
  214.                 $upcoming++;
  215.             }
  217.             if (in_array(($r['status'] ?? ''), $revenueStatuses, true)) {
  218.                 $revenue += (float)($r['totalAmount'] ?? 0);
  219.             }
  220.         }
  221.         unset($r);
  223.         // Occupancy
  224.         $occupancyRate = $count > 0 ? min(100.0, ($active / max(1, $count)) * 100.0) : 0.0;
  226.         // Series (last 12 months)
  227.         $seriesReservations = [];
  228.         $seriesRevenue = [];
  229.         $now = new \DateTimeImmutable('first day of this month');
  230.         for ($i = 11; $i >= 0; $i--) {
  231.             $m = $now->modify("-{$i} months");
  232.             $label = $m->format('Y-m');
  233.             $seriesReservations[$label] = 0;
  234.             $seriesRevenue[$label] = 0.0;
  235.         }
  237.         foreach ($rows as $r) {
  238.             $ci = $toImmutable($r['checkInDate'] ?? null);
  239.             $label = $ci->format('Y-m');
  241.             if (isset($seriesReservations[$label])) {
  242.                 $seriesReservations[$label]++;
  243.                 if (in_array(($r['status'] ?? ''), $revenueStatuses, true)) {
  244.                     $seriesRevenue[$label] += (float)($r['totalAmount'] ?? 0);
  245.                 }
  246.             }
  247.         }
  249.         return [
  250.             'totals' => [
  251.                 'count'         => $count,
  252.                 'active'        => $active,
  253.                 'upcoming'      => $upcoming,
  254.                 'revenue'       => round($revenue, 2),
  255.                 'occupancyRate' => $occupancyRate,
  256.             ],
  257.             'table' => array_map(static function(array $r) {
  258.                 return [
  259.                     'id'          => $r['id'],
  260.                     'code'        => $r['code'],
  261.                     'status'      => $r['status'],
  262.                     'term'        => $r['term'],
  263.                     'checkInDate' => $r['checkInDate'],
  264.                     'checkOutDate'=> $r['checkOutDate'],
  265.                     'nights'      => $r['nights'] ?? 0,
  266.                     'total'       => $r['totalAmount'] ?? 0,
  267.                     'property'    => $r['property'] ?? '',
  268.                     'locataire'   => $r['locataire'] ?? '',
  269.                 ];
  270.             }, $rows),
  271.             'series' => [
  272.                 'reservations' => array_map(
  273.                     fn($k,$v)=>['label'=>$k,'value'=>$v],
  274.                     array_keys($seriesReservations),
  275.                     array_values($seriesReservations)
  276.                 ),
  277.                 'revenue'      => array_map(
  278.                     fn($k,$v)=>['label'=>$k,'value'=>round($v,2)],
  279.                     array_keys($seriesRevenue),
  280.                     array_values($seriesRevenue)
  281.                 ),
  282.             ],
  283.         ];
  284.     }
  286.     public function dashboardData(array $filters, int $totalActiveUnits = 0, int $marchandId): array
  287.     {
  288.         $from       = $filters['from'] ?? null;                 // \DateTimeInterface|null
  289.         $to         = $filters['to'] ?? null;                   // \DateTimeInterface|null
  290.         $status     = $filters['status'] ?? 'all';              // 'all' or a status
  291.         $termFilter = $filters['term'] ?? 'all';               // 'all'|'night'|'week'|'month'
  292.         $propertyId = $filters['propertyId'] ?? null;          // int|null
  294.         $today  = new \DateTimeImmutable('today');
  295.         $tomorrow = $today->modify('+1 day');
  296.         $in7    = $today->modify('+7 days');
  298.         // ---------- Base rows for the main table (respecting filters) ----------
  299.         $qb = $this->createQueryBuilder('r')
  300.             ->join('r.marchand', 'm')
  301.             ->leftJoin('r.habitat', 'h')
  302.             ->leftJoin('r.locataire', 't')
  303.             ->addSelect('h.libelle AS property')
  304.             ->andWhere('m.id = :mid')->setParameter('mid', $marchandId)
  305.             ->addSelect("CONCAT(COALESCE(t.name,''),' ',COALESCE(t.lastname,'')) AS tenant")
  306.             ->addSelect('r.id, r.code, r.status, r.term, r.checkInDate, r.checkOutDate, r.totalAmount');
  308.         if ($from)       { $qb->andWhere('r.checkInDate >= :from')->setParameter('from', (clone $from)->setTime(0,0)); }
  309.         if ($to)         { $qb->andWhere('r.checkOutDate <= :to')->setParameter('to', (clone $to)->setTime(23,59,59)); }
  310.         if ($status !== 'all') { $qb->andWhere('r.status = :st')->setParameter('st', $status); }
  311.         if ($termFilter !== 'all') { $qb->andWhere('r.term = :tm')->setParameter('tm', $termFilter); }
  312.         if ($propertyId) { $qb->andWhere('h.id = :pid')->setParameter('pid', (int) $propertyId); }
  313.         if ($marchandId) { $qb->andWhere('h.marchand = :mid')->setParameter('mid', (int) $marchandId); }
  315.         $rows = $qb->orderBy('r.checkInDate', 'DESC')->getQuery()->getArrayResult();
  317.         // ---------- KPIs + series ----------
  318.         $totalReservations = \count($rows);
  320.         $activeNow = 0;
  321.         $upcomingCheckins = 0;
  322.         $revenue = 0.0;
  324.         // Lists
  325.         $listCurrent = [];     // stays currently in-house
  326.         $listTodayCI = [];     // check-ins today
  328.         $toImmutable = static fn($v): \DateTimeImmutable =>
  329.         $v instanceof \DateTimeImmutable ? $v :
  330.             ($v instanceof \DateTime ? \DateTimeImmutable::createFromMutable($v) : new \DateTimeImmutable((string)$v));
  332.         foreach ($rows as &$r) {
  333.             $ci = $toImmutable($r['checkInDate']);
  334.             $co = $toImmutable($r['checkOutDate']);
  336.             // Active now (checkIn <= today < checkOut) and not canceled
  337.             if ($ci <= $today && $today < $co && !in_array(($r['status'] ?? ''), ['canceled','annulée'], true)) {
  338.                 $activeNow++;
  339.                 $listCurrent[] = [
  340.                     'code'     => $r['code'],
  341.                     'property' => $r['property'],
  342.                     'tenant'   => $r['tenant'],
  343.                     'from'     => $ci,
  344.                     'to'       => $co,
  345.                     'status'   => $r['status'],
  346.                 ];
  347.             }
  349.             // Upcoming arrivals in next 7 days (excluding today)
  350.             if ($ci >= $tomorrow && $ci <= $in7 && !in_array(($r['status'] ?? ''), ['canceled','annulée'], true)) {
  351.                 $upcomingCheckins++;
  352.             }
  354.             // Today check-ins list
  355.             if ($ci->format('Y-m-d') === $today->format('Y-m-d') && !in_array(($r['status'] ?? ''), ['canceled','annulée'], true)) {
  356.                 $listTodayCI[] = [
  357.                     'code'     => $r['code'],
  358.                     'property' => $r['property'],
  359.                     'tenant'   => $r['tenant'],
  360.                     'from'     => $ci,
  361.                     'to'       => $co,
  362.                     'status'   => $r['status'],
  363.                 ];
  364.             }
  366.             // Revenue (confirmed/arrived/departed i.e., realized)
  367.             if (in_array(($r['status'] ?? ''), ['confirmed','confirmée','arrived','arrivée','departed','parti'], true)) {
  368.                 $revenue += (float)($r['totalAmount'] ?? 0);
  369.             }
  371.             // Derived nights for table
  372.             $r['nights'] = max(0, (int)$co->diff($ci)->format('%a'));
  373.             $r['checkin']  = $ci;
  374.             $r['checkout'] = $co;
  375.         }
  376.         unset($r);
  378.         // Current occupancy rate
  379.         $occupancyRate = 0.0;
  380.         if ($totalActiveUnits > 0) {
  381.             $occupancyRate = min(100.0, ($activeNow / $totalActiveUnits) * 100.0);
  382.         }
  384.         // Series for last 12 months (reservations count + revenue)
  385.         $seriesReservations = [];
  386.         $seriesRevenue = [];
  387.         $nowFirst = new \DateTimeImmutable('first day of this month');
  388.         for ($i = 11; $i >= 0; $i--) {
  389.             $m = $nowFirst->modify("-{$i} months")->format('Y-m');
  390.             $seriesReservations[$m] = 0;
  391.             $seriesRevenue[$m]      = 0.0;
  392.         }
  393.         foreach ($rows as $r) {
  394.             $label = $toImmutable($r['checkInDate'])->format('Y-m');
  395.             if (isset($seriesReservations[$label])) {
  396.                 $seriesReservations[$label]++;
  397.                 if (in_array(($r['status'] ?? ''), ['confirmed','confirmée','arrived','arrivée','departed','parti'], true)) {
  398.                     $seriesRevenue[$label] += (float)($r['totalAmount'] ?? 0);
  399.                 }
  400.             }
  401.         }
  403.         return [
  404.             'stats' => [
  405.                 'total'             => $totalReservations,
  406.                 'active'            => $activeNow,
  407.                 'upcomingCheckins'  => $upcomingCheckins,
  408.                 'occupancyRate'     => round($occupancyRate, 1),
  409.                 'revenue'           => round($revenue, 2),
  410.                 'currencySymbol'    => $filters['currencySymbol'] ?? '$',
  411.             ],
  412.             'series' => [
  413.                 'reservations' => array_map(fn($k,$v)=>['label'=>$k,'value'=>$v], array_keys($seriesReservations), array_values($seriesReservations)),
  414.                 'revenue'      => array_map(fn($k,$v)=>['label'=>$k,'value'=>round($v,2)], array_keys($seriesRevenue), array_values($seriesRevenue)),
  415.             ],
  416.             // Keep your previous table shape but normalize field names a bit
  417.             'table' => [
  418.                 'rows' => array_map(static function(array $r) {
  419.                     return [
  420.                         'code'     => $r['code'],
  421.                         'property' => $r['property'],
  422.                         'tenant'   => $r['tenant'],
  423.                         'checkin'  => $r['checkin'],
  424.                         'checkout' => $r['checkout'],
  425.                         'term'     => $r['term'],
  426.                         'nights'   => $r['nights'],
  427.                         'total'    => (float)($r['totalAmount'] ?? 0),
  428.                         'status'   => $r['status'],
  429.                     ];
  430.                 }, $rows),
  431.                 'currencySymbol' => $filters['currencySymbol'] ?? '$',
  432.             ],
  433.             'lists' => [
  434.                 'current'       => $listCurrent,
  435.                 'todayCheckins' => $listTodayCI,
  436.             ],
  437.         ];
  438.     }
  442.     /**
  443.      * Returns true if $habitat has NO non-canceled reservations overlapping [from, to).
  444.      *
  445.      * Overlap rule (half-open interval):
  446.      *   [checkIn, checkOut) overlaps [from, to)  <=>  checkIn < to AND checkOut > from
  447.      *
  448.      * @param Habitat              $habitat
  449.      * @param \DateTimeInterface   $from   inclusive (start of stay)
  450.      * @param \DateTimeInterface   $to     exclusive (end of stay)
  451.      * @param int|null             $excludeReservationId  reservation ID to ignore (e.g. when modifying/moving)
  452.      */
  453.     public function isHabitatAvailable(
  454.         Habitat $habitat,
  455.         \DateTimeInterface $from,
  456.         \DateTimeInterface $to,
  457.         ?int $excludeReservationId = null, int $marchandId
  458.     ): bool {
  459.         // Basic guard
  460.         if ($to <= $from) {
  461.             throw new \InvalidArgumentException('The "to" date must be strictly after the "from" date.');
  462.         }
  464.         // Normalize to mutable DateTime for DBAL (columns are DATE, but DateTime works fine)
  465.         $fromDT = $from instanceof \DateTimeImmutable ? \DateTime::createFromImmutable($from) : clone $from;
  466.         $toDT   = $to   instanceof \DateTimeImmutable ? \DateTime::createFromImmutable($to)   : clone $to;
  468.         // If you store DATE (no time), setting time to midnight is harmless/explicit
  469.         $fromDT->setTime(0, 0, 0);
  470.         $toDT->setTime(0, 0, 0);
  472.         $qb = $this->createQueryBuilder('r')
  473.             ->select('COUNT(r.id)')
  474.             ->join('r.marchand', 'm')
  475.             ->where('r.habitat = :habitat')
  476.             ->andWhere('m.id = :mid')->setParameter('mid', $marchandId)
  477.             // ignore canceled reservations
  478.             ->andWhere('r.status <> :canceled')
  479.             // half-open overlap condition:
  480.             ->andWhere('r.checkInDate < :to')
  481.             ->andWhere('r.checkOutDate > :from')
  482.             ->setParameter('habitat', $habitat)
  483.             ->setParameter('canceled', Reservation::STATUS_CANCELED)
  484.             ->setParameter('from', $fromDT)
  485.             ->setParameter('to', $toDT);
  487.         if ($excludeReservationId !== null) {
  488.             $qb->andWhere('r.id <> :excludeId')
  489.                 ->setParameter('excludeId', $excludeReservationId);
  490.         }
  492.         $conflicts = (int) $qb->getQuery()->getSingleScalarResult();
  494.         return $conflicts === 0;
  495.     }
  497.     public function loadReservationsForPeriodForSingleAppartment($startDate, $period, Habitat $property)
  498.     {
  499.         $start = date('Y-m-d', $startDate);
  500.         $end = date('Y-m-d', $startDate + ($period * 3600 * 24));
  502.         $q = $this
  503.             ->createQueryBuilder('u')
  504.             ->select('u')
  505.             ->where('u.habitat = :app ')
  506.             ->andWhere('((u.checkInDate >= :start AND u.checkOutDate <= :end) OR'
  507.                 .'(u.checkInDate < :start AND u.checkOutDate >= :start) OR'
  508.                 .'(u.checkInDate <= :end AND u.checkOutDate > :end) OR'
  509.                 .'(u.checkInDate < :start AND u.checkOutDate > :end))')
  510. //            ->andWhere('u.confirmed = :confirmed')
  511.             ->setParameter('start', $start)
  512.             ->setParameter('end', $end)
  513.             ->setParameter('app', $property)
  514. //            ->setParameter('confirmed', true)
  515.             ->addOrderBy('u.checkOutDate', 'ASC')
  516.             ->getQuery();
  518.         $reservations = null;
  519.         try {
  520.             $reservations = $q->getResult();
  521.         } catch (NoResultException $e) {
  522.         }
  524.         return $reservations;
  525.     }
  528. }