I’m working on a hotel reservation system where I need to accurately calculate room availability, including rooms that are listed as “extra rooms” in reservations. Here’s the scenario:

• We have multiple room types, each with a certain stock.
• A single reservation can include multiple rooms, listed in both the room_id field and the extra_room_ids field (which is a JSON encoded array).
• I need to determine how many rooms are available, and also how many will be available after 11:00 AM (check-out time) on a given date.

My current function calculates availability, but it’s not accounting for extra rooms correctly. Here’s my current function:

    public function getRoomAvailability($startDate, $endDate) {
        $checkOutTime = $startDate . ' 11:00:00'; // Check-out time
        $query = "
            SELECT rooms.id, rooms.name, rooms.stock,
            COALESCE(
                rooms.stock - SUM(
                    CASE 
                        WHEN reservations.start_time IS NOT NULL 
                        THEN 1 
                        ELSE 0
                    END
                ), rooms.stock
            ) AS available_rooms,
            0 AS partially_available_rooms
            FROM rooms
            LEFT JOIN reservations ON (rooms.id = reservations.room_id OR FIND_IN_SET(rooms.id, reservations.extra_room_ids))
            AND (reservations.start_time <= :end_date AND reservations.end_time >= :start_date)
            GROUP BY rooms.id, rooms.name, rooms.stock
        ";
    
        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(':start_date', $startDate);
        $stmt->bindParam(':end_date', $endDate);
        $stmt->execute();
    
        $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
        // Manually calculate the partially available rooms
        foreach ($result as &$room) {
            $roomId = $room['id'];
            $partialAvailableQuery = "
                SELECT COUNT(*) AS partial_count 
                FROM " . $this->table_name . " 
                WHERE (room_id=:room_id OR FIND_IN_SET(:room_id, extra_room_ids)) 
                AND end_time = :check_out_time
            ";
            $partialStmt = $this->conn->prepare($partialAvailableQuery);
            $partialStmt->bindParam(':room_id', $roomId);
            $partialStmt->bindParam(':check_out_time', $checkOutTime);
            $partialStmt->execute();
            $partialResult = $partialStmt->fetch(PDO::FETCH_ASSOC);
    
            $room['partially_available_rooms'] = $partialResult['partial_count'];
        }
    
        return $result;
    }

This array output:

Array
(
    [0] => Array
        (
            [id] => 1
            [name] => Double Room
            [stock] => 3
            [available_rooms] => 1
            [partially_available_rooms] => 2
        )

    [1] => Array
        (
            [id] => 2
            [name] => 4 Person Rooms
            [stock] => 3
            [available_rooms] => 3
            [partially_available_rooms] => 0
        )

    [2] => Array
        (
            [id] => 3
            [name] => Yellow Stone House (4 Persons)
            [stock] => 1
            [available_rooms] => 1
            [partially_available_rooms] => 0
        )

    [3] => Array
        (
            [id] => 4
            [name] => Yellow Stone House (3 Persons)
            [stock] => 1
            [available_rooms] => 1
            [partially_available_rooms] => 0
        )

    [4] => Array
        (
            [id] => 5
            [name] => Honeymoon Blue Stone House
            [stock] => 1
            [available_rooms] => 1
            [partially_available_rooms] => 0
        )

    [5] => Array
        (
            [id] => 6
            [name] => Two Single Beds 
            [stock] => 1
            [available_rooms] => 1
            [partially_available_rooms] => 0
        )

)

Scenario: On the 22nd, 3 double rooms become available. One reservation has two double rooms booked, and another reservation has one double room booked. Normally, all 3 rooms should be listed under partially_available_rooms in the array. However, as seen, 2 rooms are listed under partially_available_rooms, and one room is shown as available in stock.

I tried many different getRoom Availability functions but still extra rooms and rooms are does not calculate well.