CREATE PROCEDURE dbo.GetHallReservations @AHotelNumber INT, @AFromDate DATETIME, @AToDate DATETIME AS
/*
  Created by Kent Johansen, September 2024
  Returns list of hall reservations for selected period
*/
DECLARE @Arr24HoursStarts CHAR(5)
SELECT @Arr24HoursStarts = KeyValue FROM HotelSetUp WITH (NOLOCK) WHERE SearchKey = 'Arr24HoursStarts'
SET @Arr24HoursStarts = ISNULL(@Arr24HoursStarts, '')

SET @AToDate = DATEADD(DAY, 1, @AToDate)

SELECT Reservations.ReferenceNumber, Reservations.GuestNumber, ReservationConferenceLine.ArrivalDate, ReservationConferenceLine.ArrivalTime, ReservationConferenceLine.DepartureDate, ReservationConferenceLine.DepartureTime,
Reservations.Status, ReservationConferenceLine.NumberOfRooms, ReservationConferenceLine.Plenum, 
C1.Number, C1.ConferenceRoomTypeNumber,
ReservationConferenceLine.ConferenceRoomNumber, ReservationConferenceLine.BookedConferenceRoomTypeNumber
FROM Reservations WITH (NOLOCK)
INNER JOIN ReservationConferenceLine WITH (NOLOCK) ON Reservations.ReferenceNumber = ReservationConferenceLine.ReferenceNumber 
  AND Reservations.GuestNumber = ISNULL(ReservationConferenceLine.GuestNo, 0)
  AND (ISNULL(ReservationConferenceLine.BookedConferenceRoomTypeNumber, 0) <> 0) AND (ISNULL(SchemePattern, 0) = 0)
LEFT JOIN ConferenceRooms WITH (NOLOCK) ON ConferenceRooms.ConferenceRoomNumber = ReservationConferenceLine.ConferenceRoomNumber
LEFT JOIN ConferenceRoomsXTab WITH (NOLOCK) ON ConferenceRoomsXTab.ConferenceRoomNumber = ReservationConferenceLine.ConferenceRoomNumber
LEFT JOIN ConferenceRooms C1 WITH (NOLOCK) ON C1.ConferenceRoomNumber = ConferenceRoomsXTab.CollectsConferenceRoomNumber
INNER JOIN ReservationMain WITH (NOLOCK) ON ReservationMain.ReferenceNumber = Reservations.ReferenceNumber
INNER JOIN ConferenceRoomTypes CRT WITH (NOLOCK) ON CRT.ConferenceRoomTypeNumber = ReservationConferenceLine.BookedConferenceRoomTypeNumber
  AND CRT.HotelNumber = 0
WHERE Reservations.Status < 7
AND ISNULL(ConferenceRooms.HotelNumber, 0) = @AHotelNumber
AND ISNULL(ReservationConferenceLine.TableBooking, 0) = 0
AND (ReservationConferenceLine.ArrivalDate < CONVERT(VARCHAR(10), @AToDate, 112) AND (ReservationConferenceLine.DepartureDate >= CONVERT(VARCHAR(10), @AFromDate, 112)) OR 
  (ReservationConferenceLine.ArrivalDate = CONVERT(VARCHAR(10), @AToDate, 112) AND ReservationConferenceLine.ArrivalTime < @Arr24HoursStarts))
ORDER BY ConferenceRooms.Number