CREATE PROCEDURE [dbo].[GetRoomStatus] @RoomNumberID INT, @LineNumber INT, @ArrivalDate DATETIME, @DepartureDate DATETIME
AS  
BEGIN 

  DECLARE @RoomStatus INT
  DECLARE @DefaultArrivalTime VARCHAR(5)
  DECLARE @DefaultDepartureTime VARCHAR(5)
  DECLARE @OOSDecreaseVacantRooms VARCHAR(1)
  DECLARE @CashierDisabled VARCHAR(1)
  DECLARE @Status INT
  DECLARE @ReferenceNumber INT
  DECLARE @DoubleBookingAllowed BIT
  
  DECLARE @ArrivalDatePart VARCHAR(8)
  DECLARE @DepartureDatePart VARCHAR(8)
  DECLARE @ArrivalTimePart VARCHAR(5)
  DECLARE @DepartureTimePart VARCHAR(5)

  DECLARE @AvoidRef INT
  DECLARE @StatusOffset INT

  SELECT @DefaultArrivalTime = KeyValue FROM HotelSETUP WITH (NOLOCK) WHERE SearchKey = 'TimeOfArrivalForRes'
  SELECT @DefaultDepartureTime = KeyValue FROM HotelSETUP WITH (NOLOCK) WHERE SearchKey = 'TimeOfDepartureForRes'
  SELECT @OOSDecreaseVacantRooms = KeyValue FROM HotelSETUP WITH (NOLOCK)  WHERE SearchKey = 'OOSDecreaseVacantRooms'
  SELECT @CashierDisabled = KeyValue FROM HotelSETUP WITH (NOLOCK)  WHERE SearchKey = 'CashierDisabled' 

  SET @DefaultArrivalTime = ISNULL( @DefaultArrivalTime, '00:00')
  SET @DefaultDepartureTime = ISNULL(@DefaultDepartureTime, '00:00')
  SET @OOSDecreaseVacantRooms = ISNULL(@OOSDecreaseVacantRooms, '0')
  SET @CashierDisabled = ISNULL(@CashierDisabled, '0')

  SET @ArrivalDatePart = CONVERT(VARCHAR(8),  @ArrivalDate, 112)
  SET @DepartureDatePart = CONVERT(VARCHAR(8),  @DepartureDate, 112)
  SET @ArrivalTimePart = CONVERT(VARCHAR(8),  @ArrivalDate, 108)
  SET @DepartureTimePart = CONVERT(VARCHAR(8),  @DepartureDate, 108)



  SET @AvoidRef = -999
  IF @OOSDecreaseVacantRooms = '1'
    SET @AvoidRef = -2

  SET @StatusOffset = 0
  IF @CashierDisabled = '1' 
            SET @StatusOffset = 1;
  SET @StatusOffset = 6 + @StatusOffset;

  
 SELECT RL.RoomNumber, RL.LINENUMBER, RL.ArrivalDate, RL.ArrivalTime, RL.DepartureDate, RL.DepartureTime,
	CASE WHEN ISNULL(PE.UseDefaultBookTimeForRooms, 0) = 1 
				OR ISNULL(PE2.UseDefaultBookTimeForRooms, 0) = 1 
				OR ISNULL(PE3.UseDefaultBookTimeForRooms, 0) = 1 
				OR ISNULL(PE4.UseDefaultBookTimeForRooms, 0) = 1 THEN 1 ELSE 0 END AS DefaultBookTimeON
	INTO #T1
	FROM  ReservationRoomline rl (NOLOCK)
	LEFT OUTER JOIN ProductExtended PE WITH (NOLOCK) ON RL.ProductNumber = PE.FK_ProductNumber
	LEFT OUTER JOIN ProductExtended PE2 WITH (NOLOCK) ON RL.ProductNumber2 = PE2.FK_ProductNumber
	LEFT OUTER JOIN ProductExtended PE3 WITH (NOLOCK) ON RL.ProductNumber3 = PE3.FK_ProductNumber
	LEFT OUTER JOIN ProductExtended PE4 WITH (NOLOCK) ON RL.ProductNumber4 = PE4.FK_ProductNumber
	WHERE (RL.LINENUMBER <> @LineNumber OR @LineNumber = 0)
	AND (rl.RoomNumber = @RoomNumberID OR rl.RoomNumber = -1)
  
	
	SELECT RoomNumber,T1.LineNumber,
		CASE WHEN DefaultBookTimeON = 1 
		  THEN T1.ArrivalDate + ' ' + @DefaultArrivalTime
		  ELSE T1.ArrivalDate + ' ' + T1.ArrivalTime
		END AS RLArrival,
		CASE WHEN DefaultBookTimeON = 1 
		  THEN T1.DepartureDate + ' ' + @DefaultDepartureTime 
		  ELSE T1.DepartureDate + ' ' + T1.DepartureTime
		END AS RLDeparture,
		CASE WHEN DefaultBookTimeON = 1 
	 	  THEN @ArrivalDatePart + ' ' + @DefaultArrivalTime 
		  ELSE @ArrivalDatePart + ' ' + @ArrivalTimePart
		END AS InputArrival,
		CASE WHEN DefaultBookTimeON = 1 
		  THEN @DepartureDatePart + ' ' + @DefaultDepartureTime 
		  ELSE @DepartureDatePart + ' ' + @DepartureTimePart
		END AS InputDeparture
	INTO #T2
	FROM #T1 T1
	WHERE (T1.DepartureDate > T1.Arrivaldate) OR ((T1.DepartureDate = T1.Arrivaldate) AND (T1.ArrivalTime < T1.DepartureTime))
	
	SELECT R.STATUS, R.ReferenceNumber, ro.DoubleBookingAllowed
	INTO #T3
	FROM #T2 T2
	INNER JOIN ReservationRoomLineXTab RLX  WITH (NOLOCK) ON T2.LineNumber=RLX.LineNumber
	  AND RLX.ReferenceNumber<> @AVoidRef
	  AND  RLArrival <= InputDeparture
	  AND RLDeparture >= InputDeparture
	INNER JOIN Reservations R WITH (NOLOCK) ON RLX.ReferenceNumber=R.ReferenceNumber AND RLX.GuestNumber=R.GuestNumber
	  AND R.Status < @StatusOffset
	LEFT OUTER JOIN ROOMS ro WITH (NOLOCK) ON T2.RoomNumber = ro.RoomNumber 
	
	SELECT @Status = Status, @ReferenceNumber = ReferenceNumber, @DoubleBookingAllowed = DoubleBookingAllowed FROM #T3
	GROUP BY STATUS, ReferenceNumber,  DoubleBookingAllowed

	DROP TABLE #T1
	DROP TABLE #T2
	DROP TABLE #T3


  SET @Status = ISNULL(@Status, -1)
  SET @DoubleBookingAllowed = ISNULL(@DoubleBookingAllowed, 0)
  SET @ReferenceNumber = ISNULL(@ReferenceNumber, -1)

  SET @RoomStatus = 0 -- Available
  IF @Status > -1 
  BEGIN     
            IF @DoubleBookingAllowed = 0
                         SET @RoomStatus = 1 -- Booked
            IF @Status = 5
                         SET @RoomStatus = 2 -- Inhouse
            IF @ReferenceNumber = 0
                         SET @RoomStatus = 3 -- Out of order
            IF @ReferenceNumber = -2
                         SET @RoomStatus = 4 -- Out for service
            IF @ReferenceNumber = -3 OR @ReferenceNumber = -4
                         SET @RoomStatus = 5  -- Occupied
  END

  SELECT @RoomStatus
END
