Booking Time slot, with Equipment Stock query, in Laravel

Here’s an interesting one for you all. I’ve found myself in a implementation conundrum.

I’m developing a booking app in Laravel that allows people to book rooms and equipment for spaces at specific times. The amount of equipment available is limited, so the amount of stock has to be queried in tandem with the time slots associated with the booking.

I thought I’d solved the problem by iterating through the ‘other bookings’ that occur at the same time and count up the items that are currently in use -> then check that against what’s available in stock.

For 90% of my tests this was working fine but have just managed to find a bug that won’t allow me to do this.

    $guitarheadcount = 0;
    $guitarcabcount = 0;
    $guitarcombocount = 0;
    $bassheadcount = 0;
    $basscabcount = 0;
    $basscombocount = 0;
    $drumkitcount = 0;
    $cymbalscount = 0;

    $otherbookings = Booking::where('Room_id', '!=', $bookinginfo->Room_id)
        ->where(function ($query) use ($begin, $end) {
            $query->where(function ($q) use ($begin, $end) {
                $q->where('Booking_start', '>=', $begin)
                ->where('Booking_start', '<', $end);
            })->orWhere(function ($q) use ($begin, $end) {
                $q->where('Booking_start', '<=', $begin)
                ->where('Booking_end', '>', $end);
            })->orWhere(function ($q) use ($begin, $end) {
                $q->where('Booking_end', '>', $begin)
                ->where('Booking_end', '<=', $end);
            })->orWhere(function ($q) use ($begin, $end) {
                $q->where('Booking_start', '>=', $begin)
                ->where('Booking_end', '<=', $end);
            });
    })->get();

   //'amounts' are attributes for Booking objects. 

    foreach($otherbookings as $other){
        $guitarheadcount = $other->Equip->guitarheadamount + $guitarheadcount;
        $guitarcabcount = $other->Equip->guitarcabamount + $guitarcabcount;
        $guitarcombocount = $other->Equip->guitarcomboamount + $guitarcombocount;
        $bassheadcount = $other->Equip->bassheadamount + $bassheadcount;
        $basscabcount = $other->Equip->basscabamount + $basscabcount;
        $basscombocount = $other->Equip->basscomboamount + $basscombocount;
        $drumkitcount = $other->Equip->drumkitamount + $drumkitcount;
        $cymbalscount = $other->Equip->cymbalsamount + $cymbalscount;
    }

I then used individual if statements to redirect if the count wound up larger than the amount in the stock.

An example of the bug in action is:

2 drum kits in stock. Make one booking for 1-2pm… then another in the same room for 2-3pm. Both requiring a drumkit.

If I then try and make a booking in a different space from 1-3pm, the count is already 2, even if one set of drums is free again (If that makes sense).

I’m really stumped. I can’t envisage if I need to:

• Continue with ‘counting’, but produce something that deducts 1 for each item that is in the same room as another (?).
• Whether I should scrap counting completely and write individual queries for each equipment item… and how I would even do that!

Any help would be much obliged.

Answer

Although the method in my previous answer looked promising… it started to make unfixable counting errors as the stock amount and adjacent bookings scaled up.

I had a rethink and devised a better solution. Users will only be able to book at a minimum 30 minute slots anyway.

So using a do/while loop, for every 30 minute increment of users chosen time slot, it’ll do a stock check.

I’m sure it isn’t great to be hammering the server but at least there’s a fixed amount of time it will be queried during a booking.

///STOCK CHECK

    $stockcheckbegin = $begin;
    $stockcheckend = date("Y-m-d H:i", strtotime('+30 minutes',strtotime($begin)));

    $tempguitarheadcount = 0;
    $tempguitarcabcount = 0;
    $tempguitarcombocount = 0;
    $tempbassheadcount = 0;
    $tempbasscabcount = 0;
    $tempbasscombocount = 0;
    $tempdrumkitcount = 0;
    $tempcymbalscount = 0;

    do {
        $otherbookings = Booking::orderby('Booking_start', 'ASC')
        ->where(function ($query) use ($stockcheckbegin, $stockcheckend) {
            $query->where(function ($q) use ($stockcheckbegin, $stockcheckend) {
                $q->where('Booking_start', '>=', $stockcheckbegin)
                ->where('Booking_start', '<', $stockcheckend);
            })->orWhere(function ($q) use ($stockcheckbegin, $stockcheckend) {
                $q->where('Booking_start', '<=', $stockcheckbegin)
                ->where('Booking_end', '>', $stockcheckend);
            })->orWhere(function ($q) use ($stockcheckbegin, $stockcheckend) {
                $q->where('Booking_end', '>', $stockcheckbegin)
                ->where('Booking_end', '<=', $stockcheckend);
            })->orWhere(function ($q) use ($stockcheckbegin, $stockcheckend) {
                $q->where('Booking_start', '>=', $stockcheckbegin)
                ->where('Booking_end', '<=', $stockcheckend);
            });
        })->get();

        foreach($otherbookings as $other){

            $tempguitarheadcount = $tempguitarheadcount + $other->Equip->guitarheadamount;
            $tempguitarcabcount = $tempguitarcabcount + $other->Equip->guitarcabamount;
            $tempguitarcombocount = $tempguitarcombocount + $other->Equip->guitarcomboamount;
            $tempbassheadcount = $tempbassheadcount + $other->Equip->bassheadamount;
            $tempbasscabcount = $tempbasscabcount + $other->Equip->basscabamount;
            $tempbasscombocount = $tempbasscombocount + $other->Equip->basscomboamount;
            $tempdrumkitcount = $tempdrumkitcount + $other->Equip->drumkitamount;
            $tempcymbalscount = $tempcymbalscount + $other->Equip->cymbalsamount;

            if(($currenthireprices->guitarheadstock - ($guitarheadamount + $tempguitarheadcount)) < 0){
                return redirect()->back()->withInput()->with('Booking_query_error', "Sorry! Our 'Guitar Head' stock will run out during the course of this booking. (Between $stockcheckbegin and $stockcheckend)");
            }
            if(($currenthireprices->guitarcabstock - ($guitarcabamount + $tempguitarcabcount)) < 0){
                return redirect()->back()->withInput()->with('Booking_query_error', "Sorry! Our 'Guitar Cab' stock will run out during the course of this booking. (Between $stockcheckbegin and $stockcheckend)");
            }
            if(($currenthireprices->guitarcombostock - ($guitarcomboamount + $tempguitarcombocount)) < 0){
                return redirect()->back()->withInput()->with('Booking_query_error', "Sorry! Our 'Guitar Combo' stock will run out during the course of this booking. (Between $stockcheckbegin and $stockcheckend)");
            }
            if(($currenthireprices->bassheadstock - ($bassheadamount + $tempbassheadcount)) < 0){
                return redirect()->back()->withInput()->with('Booking_query_error', "Sorry! Our 'Bass Head' stock will run out during the course of this booking. (Between $stockcheckbegin and $stockcheckend)");
            }
            if(($currenthireprices->basscabstock - ($basscabamount + $tempbasscabcount)) < 0){
                return redirect()->back()->withInput()->with('Booking_query_error', "Sorry! Our 'Bass Cab' stock will run out during the course of this booking. (Between $stockcheckbegin and $stockcheckend)");
            }
            if(($currenthireprices->basscombostock - ($basscomboamount + $tempbasscombocount)) < 0){
                return redirect()->back()->withInput()->with('Booking_query_error', "Sorry! Our 'Bass Combo' stock will run out during the course of this booking. (Between $stockcheckbegin and $stockcheckend)");
            }
            if(($currenthireprices->drumkitstock - ($drumkitamount + $tempdrumkitcount)) < 0){
                return redirect()->back()->withInput()->with('Booking_query_error', "Sorry! Our 'Drum kit' stock will run out during the course of this booking. (Between $stockcheckbegin and $stockcheckend)");
            }
            if(($currenthireprices->cymbalsstock - ($cymbalsamount + $tempcymbalscount)) < 0){
                return redirect()->back()->withInput()->with('Booking_query_error', "Sorry! Our sets of 'Cymbals' stock will run out during the course of this booking. (Between $stockcheckbegin and $stockcheckend)");
            }
        }

            $tempguitarheadcount = 0;
            $tempguitarcabcount = 0;
            $tempguitarcombocount = 0;
            $tempbassheadcount = 0;
            $tempbasscabcount = 0;
            $tempbasscombocount = 0;
            $tempdrumkitcount = 0;
            $tempcymbalscount = 0;

            $stockcheckbegin = date("Y-m-d H:i", strtotime('+30 minutes',strtotime($stockcheckbegin)));
            $stockcheckend = date("Y-m-d H:i", strtotime('+30 minutes',strtotime($stockcheckend)));

    } while ($stockcheckbegin != $end);

    if(($currenthireprices->guitarheadstock - ($guitarheadamount + $guitarheadcount)) < 0){
        return redirect()->back()->withInput()->with('Booking_query_error', "Unfortunatly there aren't $guitarheadamount guitar heads available between $timestart and $timeend. There's $currenthireprices->guitarheadstock in stock and $guitarheadcount in use.");
    }
    if(($currenthireprices->guitarcabstock - ($guitarcabamount + $guitarcabcount)) < 0){
        return redirect()->back()->withInput()->with('Booking_query_error', "Unfortunatly there aren't $guitarcabamount 'Guitar cabs' available between $timestart and $timeend. There's $currenthireprices->guitarcabstock in stock and $guitarcabcount in use.");
    }
    if(($currenthireprices->guitarcombostock - ($guitarcomboamount + $guitarcombocount)) < 0){
        return redirect()->back()->withInput()->with('Booking_query_error', "Unfortunatly there aren't $guitarcomboamount 'Guitar combos' available between $timestart and $timeend. There's $currenthireprices->guitarcombostock in stock and $guitarcombocount in use.");
    }
    if(($currenthireprices->bassheadstock - ($bassheadamount + $bassheadcount)) < 0) {
        return redirect()->back()->withInput()->with('Booking_query_error', "Unfortunatly there aren't $bassheadamount 'Bass heads' available between $timestart and $timeend. There's $currenthireprices->bassheadstock in stock and $bassheadcount in use.");
    }
    if(($currenthireprices->basscabstock - ($basscabamount + $basscabcount)) < 0) {
        return redirect()->back()->withInput()->with('Booking_query_error', "Unfortunatly there aren't $basscabamount 'Bass cabs' available between $timestart and $timeend. There's $currenthireprices->basscabstock in stock and $basscabcount in use.");
    }
    if(($currenthireprices->basscombostock - ($basscomboamount + $basscombocount)) < 0){
        return redirect()->back()->withInput()->with('Booking_query_error', "Unfortunatly there aren't $basscomboamount 'Bass combos' available between $timestart and $timeend. There's $currenthireprices->basscombostock in stock and $basscombocount in use.");
    }
    if(($currenthireprices->drumkitstock - ($drumkitamount + $drumkitcount)) < 0){
        return redirect()->back()->withInput()->with('Booking_query_error', "Unfortunatly there aren't $drumkitamount 'Drum kits' available between $timestart and $timeend. There's $currenthireprices->drumkitstock in stock and $drumkitcount in use.");
    }
    if(($currenthireprices->cymbalsstock - ($cymbalsamount + $cymbalscount)) < 0){
        return redirect()->back()->withInput()->with('Booking_query_error', "Unfortunatly there aren't $cymbalsamount 'sets of cymbals' available between $timestart and $timeend. There's $currenthireprices->cymbalsstock in stock and $cymbalscount in use.");
    }

    //----- END OF STOCK CHECK