CREATE PROCEDURE dbo.ReservationAllocate @LineNumber INTEGER, @SubTypes VARCHAR(255), @AllocatedRoomNumber INTEGER OUTPUT, @FRoomNumber INTEGER OUTPUT, @ErrorLevel INTEGER OUTPUT AS
SET NOCOUNT ON
/*
  Stored procedure which allocates a clean room to a reservation with matching subtypes. Returns errorlevel if not possible to find a suitable room

  LOG:
  KJ 20211130: Added SubType2. Req #3991
   TB20060601: Added @AllocatedRoomNumber to select
   TB20060224: Added: @FRoomNumber as output
  20060215 JP: BUG: didn't check if room was inhouse or not but still available
  TB20060129: Set @AllocatedRoomNumber if room already allocated
  20060109 JP: BUG: Rooms.KeyPuncher must be 1
  20051230 JP: Created

  NOTES: This routine is currently only used for Online allocation of room reservations, hence it does only support room reservations. Also log is created for Online user

*/
/*
  Error Levels:
 -01 : Reservation already allocated
  00 : No errors - Allocation succesfully
  01 : Reservation is not arriving
  02 : No room is vacant and meeting our requirements
*/
SET @ErrorLevel = 00


-- A Room is already allocated to Reservation
DECLARE 
@RoomNumber INT
SELECT @RoomNumber=ISNULL(RoomNumber, 0) FROM ReservationRoomLine RL WITH (NOLOCK) WHERE LineNumber=@LineNumber
IF @RoomNumber > 0
BEGIN
  SET @AllocatedRoomNumber = @RoomNumber --TB20060129
  SET @ErrorLevel = -01
	print @AllocatedRoomNumber
  RETURN
END

-- Status < 5
DECLARE
@Status TINYINT,
@ReferenceNumber INTEGER
SELECT @ReferenceNumber=R.ReferenceNumber, @Status=Status FROM Reservations R WITH (NOLOCK)
INNER JOIN ReservationRoomLineXTab RLX WITH (NOLOCK) ON R.ReferenceNumber=RLX.ReferenceNumber AND R.GuestNumber=RLX.GuestNumber AND RLX.LineNumber=@LineNumber -- todo: if namelist created then we have can diff subres with diff statuses
IF @Status >= 5
BEGIN
  SET @ErrorLevel = 01
  RETURN
END


DECLARE
@BookedTypeNumber INTEGER,
@Arrival DATETIME,
@Departure DATETIME,
@Number INTEGER

SELECT @BookedTypeNumber=BookedTypeNumber, @Arrival = CONVERT(DATETIME, ArrivalDate, 112)+CONVERT(DATETIME, ArrivalTime, 108), @Departure = CONVERT(DATETIME, DepartureDate, 112)+CONVERT(DATETIME, DepartureTime, 108) FROM ReservationRoomLine WITH (NOLOCK) WHERE LineNumber=@LineNumber

-- Find first vacant room meeting our requirements - 20060601: Added @AllocatedRoomNumber to select
--SELECT TOP 1 @AllocatedRoomNumber = RMSTX.RoomNumber, @Number = RM.Number FROM RoomRoomSubTypeXTab RMSTX (NOLOCK) INNER JOIN Rooms RM (NOLOCK) ON RMSTX.RoomNumber=RM.RoomNumber AND RM.CleanStatus=0 AND RM.RoomTypeNumber=@BookedTypeNumber AND ((LEN(@SubTypes) = 0) OR (SubTypeNumber IN (SELECT Value FROM dbo.SplitNumberList(@SubTypes)))) AND dbo.IsRoomAvailable(RM.RoomNumber, 0, @Arrival, @Departure, 1) = 1
--SELECT TOP 1 @AllocatedRoomNumber = RMSTX.RoomNumber, @Number = RM.Number FROM RoomRoomSubTypeXTab RMSTX (NOLOCK) INNER JOIN Rooms RM (NOLOCK) ON RM.KeyPuncher=1 AND RMSTX.RoomNumber=RM.RoomNumber AND RM.CleanStatus=0 AND RM.RoomTypeNumber=@BookedTypeNumber AND ((LEN(@SubTypes) = 0) OR (SubTypeNumber IN (SELECT Value FROM dbo.SplitNumberList(@SubTypes)))) AND dbo.IsRoomAvailable(RM.RoomNumber, 0, @Arrival, @Departure, 1) = 1
SELECT TOP 1 @AllocatedRoomNumber = RMSTX.RoomNumber, @Number =  RM.Number, @FRoomNumber =   RM.Number  FROM RoomRoomSubTypeXTab RMSTX WITH (NOLOCK) INNER JOIN Rooms RM WITH (NOLOCK) ON RM.KeyPuncher=1 AND RMSTX.RoomNumber=RM.RoomNumber AND RM.CleanStatus=0 AND RM.RoomTypeNumber=@BookedTypeNumber AND ((LEN(@SubTypes) = 0) OR (SubTypeNumber IN (SELECT Value FROM dbo.SplitNumberList(@SubTypes)))) AND dbo.IsRoomAvailable(RM.RoomNumber, 0, @Arrival, @Departure, 1) = 1 AND dbo.IsRoomInhouse(RM.RoomNumber) = 0
GROUP BY RMSTX.RoomNumber, RM.Number
HAVING (LEN(@SubTypes) = 0) OR (COUNT(*)=dbo.ElementCount(@SubTypes, ','))

IF @@rowcount = 0
BEGIN
  SET @ErrorLevel = 02  -- one or more possibilities; room is not clean, no clean room meets our subtype requirements, no room for this roomtype is actually available. 	
  RETURN
END

--SET @FRoomNumber = @Number -- TB20060224
--print @FRoomNumber

-- Allocate the room
UPDATE ReservationRoomLine SET RoomNumber = @AllocatedRoomNumber WHERE LineNumber = @LineNumber

-- FOLLOWUP ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

DECLARE @NewFollowUpNumber INT,
@SystemDateTime DATETIME,
@Text VARCHAR(96),
@UserIDNumber INT
SELECT @SystemDateTime = CONVERT (DATETIME, SystemDate, 112)+CONVERT (CHAR, GetDate(), 108) FROM System  WITH (NOLOCK) WHERE NUMBER = 1  -- Only one record YYYYMMDD

-- Find the UserIDNumber for PicassoOnline
SELECT @UserIDNumber=UserIDNumber FROM Personel WITH (NOLOCK) WHERE UserTypeNumber=5

-- Create log
SET @Text = 'Room ' + CONVERT(VARCHAR, @Number) + ' allocated'
INSERT INTO FollowUp ([Date], Type, Priority, State, Note, CreatedDate, CreatedBy, SubType, SubType2)
VALUES (@SystemDateTime, 5, 12, 3, @Text, @SystemDateTime, @UserIDNumber, 1, 3)
SELECT @NewFollowUpNumber = @@identity
INSERT INTO ReservationFollowUpXTab (ReferenceNumber, GuestNumber, FollowUpNumber) VALUES (@ReferenceNumber, 0, @NewFollowUpNumber)

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

EXEC OnlineUpdateFollowUp @ReferenceNumber

