CREATE PROCEDURE dbo.DeallocateReservation @ReferenceNumber INT, @LineNumber INT, @UserIDNumber INT, @ErrorLevel INT OUTPUT AS
-- DEPENDENCIES: NONE

/*
    Created by Jacob Pedersen

    Last update:
    BA/080303 (EXT): Do not deallocate Rooms with RoomTypes.ResourceTypeNumber = rtrSummerhouse (= 2)
    20071018 JP EXT: prefixed objects with dbo and added WITH (ROWLOCK) for updates/inserts
    20060522 JP BUG: release allotment within release period
    2005-04-28 JP: Clear Allocation Locked flag
    2004-11-24 JP: If multible rows on mainres and deallocating one of them -> bang. Therefore is parameters changed to LineNumber

    NOTE: Routine for fast deallocation of room from a given reservation. All linked subres are processed and logs are written.

    Error Levels (See ReservationFX/TDeallocateStatus = (deWasNotAllocated, deNoErrors, deRoomIsIO, 
                                                         deReservationLocked, deUnknownError, deCannotDeallocate)
   -01 : Was not allocated
    00 : No errors. Deallocated with success.
    01 : Inhouse or Out-of-house reservations exist in the room
    02 : deReservationLocked
    03 : deUnknownError
    04 : Cannot Deallocate (RoomTypes.ResourceTypeNumber = rtrSummerhouse)
*/

-- check if reservation is even allocated
DECLARE @RoomNumber INT
DECLARE @RoomTypeNumber INT

SELECT @RoomNumber = IsNull(ReservationRoomLine.RoomNumber, 0), @RoomTypeNumber = dbo.RoomTypes.ResourceTypeNumber 
FROM dbo.ReservationRoomLine WITH (NOLOCK) 
INNER JOIN dbo.Rooms WITH (NOLOCK) ON
  dbo.ReservationRoomLine.RoomNumber = dbo.Rooms.RoomNumber
INNER JOIN dbo.RoomTypes ON
  dbo.Rooms.RoomTypeNumber = dbo.RoomTypes.RoomTypeNumber
WHERE dbo.ReservationRoomLine.LineNumber=@LineNumber

IF @RoomTypeNumber = 2 -- rtrSummerhouse
BEGIN
  SET @ErrorLevel = 04
  RETURN
END ELSE
IF @RoomNumber = 0
BEGIN
  SET @ErrorLevel = -01
  RETURN
END

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- select the number of I+O in the room, if greater than 0 then error. Only error if mainres with one line (singleres)
DECLARE
@GuestNumber INT,
@CountLines INT,
@CountIO INT

SELECT TOP 1 @GuestNumber=GuestNumber FROM dbo.ReservationRoomLineXTab WITH (NOLOCK) WHERE LineNumber=@LineNumber ORDER BY GuestNumber

SELECT @CountIO=COUNT(*) FROM dbo.Reservations B WITH (NOLOCK) INNER JOIN dbo.ReservationRoomLineXTab A WITH (NOLOCK) ON A.ReferenceNumber=B.ReferenceNumber AND A.GuestNumber=B.GuestNumber AND Status IN (5,6) AND LineNumber = @LineNumber

IF @GuestNumber = 0
BEGIN
  SELECT @CountLines=COUNT(*) FROM dbo.ReservationRoomLineXTab B WITH (NOLOCK) INNER JOIN dbo.Reservations A WITH (NOLOCK) ON A.ReferenceNumber=B.ReferenceNumber AND A.GuestNumber=B.GuestNumber AND B.ReferenceNumber=@ReferenceNumber AND B.GuestNumber=0

  IF @CountLines = 1 AND @CountIO > 0
  BEGIN
    SET @ErrorLevel = 01
    RETURN
  END
END
ELSE IF @GuestNumber > 0
BEGIN
  IF @CountIO > 0
  BEGIN
    SET @ErrorLevel = 01
    RETURN
  END
END

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- NO ERRORS: do the deallocation
UPDATE dbo.ReservationRoomLine WITH (ROWLOCK) SET RoomNumber = NULL, AllocationLocked = NULL WHERE LineNumber = @LineNumber

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Release allotment - important within release period
DECLARE @SystemDate DATETIME, @AllotmentNumber INT
SELECT @AllotmentNumber = AllotmentNumber FROM dbo.ReservationMain WITH (NOLOCK) WHERE ReferenceNumber=@ReferenceNumber
SELECT @SystemDate=CONVERT (DATETIME, SystemDate, 112) FROM dbo.System WITH (NOLOCK) WHERE Number=1

IF @AllotmentNumber >=1
  EXEC dbo.GA_AllotmentReleaseSpecific @AllotmentNumber, @SystemDate
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Write to reservation logs

DECLARE
@NewFollowUpNumber INT,
@SystemDateTime DATETIME,
@Text VARCHAR (8000)

SELECT @RoomNumber = Number FROM dbo.Rooms WITH (NOLOCK) WHERE RoomNumber=@RoomNumber  -- Get the real roomnumber instead of the key
SELECT @SystemDateTime = CONVERT (DATETIME, @SystemDate, 112)+CONVERT (CHAR, GetDate(), 108)

DECLARE #log_cursor CURSOR FORWARD_ONLY FOR

  SELECT A.GuestNumber FROM dbo.ReservationRoomLineXtab A WITH (NOLOCK) INNER JOIN dbo.Reservations B WITH (NOLOCK) ON A.ReferenceNumber=B.ReferenceNumber AND A.GuestNumber=B.GuestNumber AND Status<5 AND LineNumber = @LineNumber

OPEN #log_cursor

FETCH NEXT FROM #log_cursor
INTO @GuestNumber

WHILE (@@FETCH_STATUS = 0)
BEGIN
  -- Insert into log of each linked res
  SET @Text = 'Room '+Convert (VARCHAR, @RoomNumber)+' was deallocated.'
  INSERT INTO dbo.FollowUp WITH (ROWLOCK) ([Date], Type, Priority, State, Note, CreatedDate, CreatedBy, SubType) VALUES (@SystemDateTime, 5, 12, 3, @Text, @SystemDateTime, @UserIDNumber, 2)
  SELECT @NewFollowUpNumber = @@identity
  INSERT INTO dbo.ReservationFollowUpXTab WITH (ROWLOCK) (ReferenceNumber, GuestNumber, FollowUpNumber) VALUES (@ReferenceNumber, @GuestNumber, @NewFollowUpNumber)

  FETCH NEXT FROM #log_cursor
  INTO @GuestNumber
END

CLOSE #log_cursor
DEALLOCATE #log_cursor

GO
