How to check if 2 dates and time ranges overlaps in laravel eloquent

user_18582044 0
Viewed 279

I'm building a reservation system for a smart lockers project.

The user should be able to book via the website a locker for the time he needs and obviously, I need to make sure that there are no overbookings.

I'm trying to build a query with eloquent to get total count of the bookings for the dates requested from the user.

So far, in my controller I have done the following :

public function create(Request $request)
    {

        $locationId = $request->input('location_id');
        $beginningDate = Carbon::parse($request->input('beginning_date'))->format('d-m-Y');
        $beginningTime = Carbon::parse($request->input('beginning_time'))->format('H:m');
        $duration = $request->input('duration');

        $totalPrice=PriceList::where('duration_to_hours', $duration)->value('price');

        $reservationStartingDate = $beginningDate. ','.$beginningTime;

        $calculateReservationDates = Carbon::parse($reservationStartingDate)->addHours($duration);

        $endDate= $calculateReservationDates->format('d-m-Y');
        $endTime = $calculateReservationDates->format('H:m');

       $reservedCount = Reservation::where('beginning_date',  '<=', $endDate)
        ->where('end_date', '>=', $beginningDate)
        ->where('beginning_time', '<=', $endTime)
        ->where('end_time', '>=', $beginningTime)
        ->count();

        $totalBoxes = Box::where('location_id', $locationId)->count();

        dd($totalBoxes, $reservedCount);

        $available = false;

        if($totalBoxes > $reservedCount){
            $available = true;
        }


        return view('checkout', compact('locationId', 'totalPrice', 'endDate', 'endTime', 'beginningDate', 'beginningTime', 'duration', 'available'));
    }

This query is working if the dates and time requested from the user are the same as the other reservation.

Let's suppose there's a reservation for 06/04/2022 from 7 pm for 2 hours and there's only one box available in total. If the new user try to make a reservation for the same time and dates, than it returns no availability but, if the user try to make a reservation for 06/04/2022 from 7.30 pm for 2 hours it returns that there's availability, when in reality it should return there isn't.

I don't get where I'm missing something, do you have any idea on how i can solve this matter?

Thank you

laravel-9 laravel-8 eloquent mysql php
1 Answers

you need check conditions :

Time request from user :

 A : beginning_time, B : end_time

This has 4 case match with records from DB:

 Y : beginning_time_record, Z : end_time_record

case 1 :

Y -> A -> Z -> B

case 2 :

Y -> A -> B -> Z

case 3 :

A -> Y -> Z -> B

case 4 :

A -> Y -> B -> Z

same as :

$existReservation = Reservation::orWhere(function ($q1) use ($beginningDatetime, $endDatetime) {
   $q1->where('beginning_date_time', '>=', $beginningDatetime )
      ->where('beginning_date_time', '<=', $endDatetime);
})
->orWhere(function ($q2) use ($beginningDatetime, $endDatetime) {
   $q2->where('end_date_time', '>=', $beginningDatetime )
      ->where('end_date_time', '<=', $endDatetime);
})
->orWhere(function ($q3) use ($beginningDatetime, $endDatetime) {
   $q3->where('beginning_date_time', '>=', $beginningDatetime)
      ->where('end_date_time', '<=', $endDatetime);
})
->orWhere(function ($q4) use ($beginningDatetime, $endDatetime) {
   $q4->where('beginning_date_time', '<=', $beginningDatetime)
      ->where('end_date_time', '>=', $endDatetime);
})
->count();

or group conditions :

$existReservation = Reservation::orWhere(function ($q1) use ($beginningDatetime, $endDatetime) {
       $q1->where('beginning_date_time', '>=', $beginningDatetime )
          ->where('beginning_date_time', '<=', $endDatetime);
    })
    ->orWhere(function ($q2) use ($beginningDatetime, $endDatetime) {
       $q2->where('beginning_date_time', '<=', $beginningDatetime )
          ->where('end_date_time', '>=', $beginningDatetime );
    })
    ->count();

for check no availability

Update : other way :

$existReservation = Reservation::where('end_date_time', '>=', $beginningDatetime)
    ->where('beginning_date_time', '<=', $endDatetime)
    ->count();

Database : beginning_date_time and end_date_time

user_68616830
Related