CREATE PROCEDURE DBO.SetOutOfOrderRoomAllFutureAllRooms @ReferenceNumber INT = 0, @GuestNumber INT = 0, @CustomerNumber INT = 0, @MergePeriods BIT = 0, @TheRoomNumber INT = 0 AS
SET NOCOUNT ON
/*
   DEPENDENCIES: UpdateArrivalAndDeparture

    Special OutOfOrder routine for Skallerup Klit Feriecenter.

    Created		18-09-2003 Jacob Pedersen
    Last updated by 	22-09-2003 Jacob Pedersen Added @CustomerNumber param and changed @ReferenceNumber to read from Rooms table instead of only rooms on res (for change room)
			10-02-2004 Jacob Pedersen Check for SetOwnedRoomsOutOfOrder setting
*/

-- Set Rooms outoforder /Skallerup --

DECLARE @SetOwnedRoomsOutOfOrder BIT
SELECT @SetOwnedRoomsOutOfOrder=KeyValue FROM HotelSetup WITH (NOLOCK) WHERE UPPER (SearchKey) = UPPER ('SetOwnedRoomsOutOfOrder')
IF IsNull (@SetOwnedRoomsOutOfOrder, 0) = 0
  Set @SetOwnedRoomsOutOfOrder = 0
IF @SetOwnedRoomsOutOfOrder = 0
  RETURN

DECLARE @RoomNumber INT

IF @CustomerNumber > 0
BEGIN
  DECLARE #RoomLineCur3 CURSOR FAST_FORWARD FOR

  SELECT RoomNumber FROM Rooms WITH (NOLOCK) WHERE CustomerNumber = @CustomerNumber
END
ELSE IF @ReferenceNumber > 0
BEGIN
  DECLARE #RoomLineCur3 CURSOR FAST_FORWARD FOR

--  SELECT RoomNumber FROM Rooms (NOLOCK) WHERE (CustomerNumber > 0) AND (CustomerNumber IN (SELECT Link /*AS CustomerNumber*/ FROM ReservationAddress (NOLOCK) WHERE AddressNumber in (SELECT AddressNumber FROM ReservationAddressXTab (NOLOCK) WHERE ReferenceNumber=@ReferenceNumber AND GuestNumber=@GuestNumber) AND AddressType=2))

   -- NB remarked SQL only selects the rooms owned by the customer of the res, but since all customers can be used now we have to do this for all rooms now!

  -- Only rooms on reservation - this might not be good enough since it can be moved around. maybe all rooms should be used like this:   SELECT RoomNumber FROM Rooms (NOLOCK) WHERE CustomerNumber >= 1
--  SELECT RoomNumber FROM ReservationRoomline (NOLOCK) WHERE LineNumber IN
--  (SELECT DISTINCT LineNumber FROM ReservationRoomlineXTab (NOLOCK) INNER JOIN Reservations (NOLOCK) on Reservations.referencenumber=reservationroomlinextab.referencenumber and Reservations.guestnumber=reservationroomlinextab.guestnumber where reservationroomlinextab.referencenumber=@referencenumber and ReservationRoomlineXtab.GuestNumber=@GuestNumber and Status < 7)
-- Yes all rooms have to be processed, lets not hope it will give locking problems
  SELECT RoomNumber FROM Rooms WITH (NOLOCK) WHERE CustomerNumber >= 1
END
ELSE IF @TheRoomNumber > 0
BEGIN
  DECLARE #RoomLineCur3 CURSOR FAST_FORWARD FOR

  SELECT RoomNumber FROM Rooms WITH (NOLOCK) WHERE RoomNumber=@TheRoomNumber
END
else
BEGIN
  DECLARE #RoomLineCur3 CURSOR FAST_FORWARD FOR

  SELECT RoomNumber FROM Rooms WITH (NOLOCK) WHERE CustomerNumber >= 1
END

Open #RoomLineCur3

Fetch Next From #RoomLineCur3
Into @RoomNumber

While  (@@FETCH_STATUS <> -1)
BEGIN
  IF IsNull (@RoomNumber, 0) = 0
    Set @RoomNumber = 0

  IF @RoomNumber > 0
    EXEC SetOutOfOrderRoomAllFuture @RoomNumber

  IF @MergePeriods = 1
    EXEC MergeOutOfOrderPeriods @RoomNumber

  Fetch Next From #RoomLineCur3
  Into @RoomNumber
END

Close #RoomLineCur3
Deallocate #RoomLineCur3

