CREATE PROCEDURE dbo.GetRoomReservations @AHotelNumber INT, @AFromDate DATETIME, @AToDate DATETIME AS
/*
  Created by Kent Johansen, September 2024
  Returns list room reservations for selected period
*/
SELECT Reservations.ReferenceNumber, Reservations.GuestNumber, 
CASE WHEN Reservations.GuestNumber = 0 THEN ReservationRoomLine.ArrivalDate ELSE Reservations.ArrivalDate END AS ArrivalDate,
CASE WHEN Reservations.GuestNumber = 0 THEN ReservationRoomLine.ArrivalTime ELSE Reservations.ArrivalTime END AS ArrivalTime,
CASE WHEN Reservations.GuestNumber = 0 THEN ReservationRoomLine.DepartureDate ELSE Reservations.DepartureDate END AS DepartureDate,
CASE WHEN Reservations.GuestNumber = 0 THEN ReservationRoomLine.DepartureTime ELSE Reservations.DepartureTime END AS DepartureTime,
CASE WHEN Reservations.GuestNumber = 0 THEN ReservationRoomLine.NumberOfRooms ELSE 1 END AS NumberOfRooms,
Reservations.Status, Rooms.Number, 
CASE WHEN Rooms.RoomNumber IS NULL THEN ReservationRoomLine.BookedTypeNumber ELSE Rooms.RoomTypeNumber END AS RoomTypeNumber

FROM ReservationRoomLine WITH (NOLOCK)
INNER JOIN ReservationRoomLineXTab WITH (NOLOCK) ON ReservationRoomLine.LineNumber = ReservationRoomLineXTab.LineNumber
INNER JOIN Reservations WITH (NOLOCK) ON ReservationRoomLineXTab.ReferenceNumber = Reservations.ReferenceNumber
  AND ReservationRoomLineXTab.GuestNumber = Reservations.GuestNumber
INNER JOIN ReservationMain WITH (NOLOCK) ON ReservationMain.ReferenceNumber = Reservations.ReferenceNumber
INNER JOIN ReservationAddressXTab WITH (NOLOCK) ON Reservations.ReferenceNumber = ReservationAddressXTab.ReferenceNumber
  AND Reservations.GuestNumber = ReservationAddressXTab.GuestNumber
INNER JOIN ReservationAddress raGuest WITH (NOLOCK) ON raGuest.AddressNumber = ReservationAddressXTab.AddressNumber AND AddressType = 1
LEFT OUTER JOIN Rooms WITH (NOLOCK) ON Rooms.RoomNumber = ReservationRoomLine.RoomNumber
LEFT OUTER JOIN RoomTypes WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = Rooms.RoomTypeNumber
LEFT OUTER JOIN RoomTypes RoomTypesBooked WITH (NOLOCK) ON RoomTypesBooked.RoomTypeNumber = ReservationRoomLine.BookedTypeNumber
LEFT OUTER JOIN ReservationReservationXtab resresx WITH (NOLOCK) ON resresx.FK_ReferenceNumberSub = ReservationMain.ReferenceNumber
WHERE Reservations.Status < 7
AND ((
(((ReservationRoomLine.DepartureDate > CONVERT(VARCHAR(10), @AFromDate, 112)) AND (ReservationRoomLine.ArrivalDate <= CONVERT(VARCHAR(10), @AToDate, 112)) AND (Reservations.GuestNumber = 0)) 
OR  ((Reservations.DepartureDate > CONVERT(VARCHAR(10), @AFromDate, 112)) AND (Reservations.ArrivalDate <= CONVERT(VARCHAR(10), @AToDate, 112))) AND (reservations.GuestNumber > 0))) 
AND (((ReservationRoomLine.DepartureDate <> ReservationRoomLine.ArrivalDate) AND (Reservations.GuestNumber = 0)) OR ((Reservations.DepartureDate <> Reservations.ArrivalDate)) AND (Reservations.GuestNumber > 0))
 ) 
AND ReservationMain.HotelNumber = @AHotelNumber
AND Reservations.Referencenumber > 0
AND Reservations.DepartureDate > Reservations.ArrivalDate
ORDER BY ReservationRoomLine.LineNumber, Reservations.ReferenceNumber, Reservations.GUESTNUMBER, Reservations.ArrivalDate