CREATE PROCEDURE dbo.ReservationCheckIn @ReferenceNumber INTEGER, @GuestNumber INTEGER OUTPUT, @ErrorLevel INTEGER OUTPUT AS
SET NOCOUNT ON
/*
  Stored procedure which checks in reservation if possible. Returns errorlevel if not and the GuestNumber which has the problem

  LOG:
  kj 20140527: Read arrivaldate from correct table.
  20080114 Added NOT NULL check for errorlevel 4
  20060130 JP: BUG: set the status to inhouse
  20051230 JP: Created

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

*/
/*
  Error Levels:
 -01 : Reservation already inhouse
  00 : No errors - CheckIn succesfully
  01 : Reservation is NOX
  02 : Reservation is not arriving today
  03 : A room has not been allocated
  04 : The allocated room is not clean
*/

-- if guestnumber > 0 then checkin guestnumber 0. if no errors (or already checkedin) then continue
IF @GuestNumber > 0
BEGIN
  EXEC dbo.ReservationCheckIn @ReferenceNumber, 0, @ErrorLevel OUTPUT
  IF @ErrorLevel > 00
  BEGIN
    SET @GuestNumber = 0
    RETURN
  END
END

SET @ErrorLevel = 00

-- Status < 5
DECLARE
@Status TINYINT,
@ArrivalDate CHAR(8)
--SELECT @Status=Status, @ArrivalDate=ArrivalDate FROM Reservations WITH (NOLOCK) WHERE ReferenceNumber=@ReferenceNumber AND GuestNumber=@GuestNumber

IF @GuestNumber = 0
  SELECT @ArrivalDate = reservationroomline.arrivaldate
  from reservationroomline (NOLOCK)
  inner join RESERVATIONROOMLINEXTAB (nolock) on RESERVATIONROOMLINEXTAB.LINENUMBER = reservationroomline.linenumber
  where reservationroomlinextab.referencenumber = @ReferenceNumber
  and reservationroomlinextab.guestnumber = @GuestNumber
else
  SELECT @ArrivalDate=ArrivalDate FROM Reservations(NOLOCK) WHERE ReferenceNumber=@ReferenceNumber AND GuestNumber=@GuestNumber

SELECT @Status=Status FROM Reservations(NOLOCK) WHERE ReferenceNumber=@ReferenceNumber AND GuestNumber=@GuestNumber

IF @Status = 5
BEGIN
  SET @ErrorLevel = -01
  RETURN
END
ELSE IF @Status > 5
BEGIN
  SET @ErrorLevel = 01
  RETURN
END

-- Arrival = Today (systemdate)
DECLARE
@SystemDate CHAR(8)
SELECT @SystemDate=SystemDate FROM System WITH (NOLOCK) WHERE Number=1

--- Test
--SET @SystemDate = convert(varchar(8),convert(datetime,'2006',112), 112)
--SET @SystemDate = '20080201'
---

IF @ArrivalDate <> @SystemDate
BEGIN
  SET @ErrorLevel = 02
  RETURN
END

-- Room must be allocated
DECLARE 
@RoomNumber INT
SELECT @RoomNumber=ISNULL(RoomNumber, 0) FROM ReservationRoomLine RL WITH (NOLOCK) INNER JOIN ReservationRoomLineXTab RLX WITH (NOLOCK) ON RL.LineNumber=RLX.LineNumber AND RLX.ReferenceNumber=@ReferenceNumber AND RLX.GuestNumber=@GuestNumber
IF @RoomNumber = 0
BEGIN
  SET @ErrorLevel = 03
  RETURN
END

-- Allocated Room must be clean
DECLARE
@CleanStatus TINYINT
SELECT @CleanStatus=CleanStatus FROM Rooms WITH (NOLOCK) WHERE RoomNumber = @RoomNumber
IF @CleanStatus <> 0 AND @RoomNumber IS NOT NULL
BEGIN
  SET @ErrorLevel = 04
  RETURN
END

-- Check in now that all criteria is met
UPDATE Reservations SET Status=5 WHERE ReferenceNumber=@ReferenceNumber AND GuestNumber=@GuestNumber -- TODO: update arrivaltime if hotelsetting is set

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

DECLARE @NewFollowUpNumber INT,
@SystemDateTime DATETIME,
@Text VARCHAR(96),
@UserIDNumber INT

EXEC SystemDateTime '', @SystemDateTime OUTPUT

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

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

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

EXEC OnlineUpdateFollowUp @ReferenceNumber