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 @InputArrivalDate VARCHAR(14)
  DECLARE @InputDepartureDate VARCHAR(14)

  DECLARE @AvoidRef INT
  DECLARE @StatusOffset INT

  DECLARE @UseDefaultBookTimeForRooms BIT

  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 @UseDefaultBookTimeForRooms = 0

  IF @LineNumber > 0
  	 SELECT @UseDefaultBookTimeForRooms = 1 FROM reservationroomline rl
	  LEFT OUTER JOIN ProductExtended pe ON rl.ProductNumber = pe.FK_ProductNumber
	  LEFT OUTER JOIN ProductExtended pe2 ON rl.ProductNumber2 = pe2.FK_ProductNumber
	  LEFT OUTER JOIN ProductExtended pe3 ON rl.ProductNumber3 = pe3.FK_ProductNumber
	  LEFT OUTER JOIN ProductExtended pe4 ON rl.ProductNumber4 = pe4.FK_ProductNumber
	  WHERE rl.LINENUMBER = @LineNumber AND (ISNULL(pe.UseDefaultBookTimeForRooms,0) = 1 OR 
	  ISNULL(pe2.UseDefaultBookTimeForRooms,0) = 1 OR  
	  ISNULL(pe3.UseDefaultBookTimeForRooms,0) = 1 OR 
	  ISNULL(pe4.UseDefaultBookTimeForRooms,0) = 1)

  IF @UseDefaultBookTimeForRooms = 1
  BEGIN
	SET @InputArrivalDate = @ArrivalDatePart + ' ' + @DefaultArrivalTime
	SET @InputDepartureDate = @DepartureDatePart + ' ' + @DefaultDepartureTime
  END ELSE
  BEGIN
	SET @InputArrivalDate = @ArrivalDatePart + ' ' + @ArrivalTimePart
	SET @InputDepartureDate = @DepartureDatePart + ' ' + @DepartureTimePart
  END



  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 rlx.ReferenceNumber <= 0 THEN ISNULL(RL.StatusRL, 0) ELSE r.Status END Status,
	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)
	INNER JOIN RESERVATIONROOMLINEXTAB rlx (NOLOCK) ON rl.LINENUMBER = rlx.LINENUMBER
	INNER JOIN Reservations r (NOLOCK) ON rlx.ReferenceNumber = r.ReferenceNumber AND rlx.GuestNumber = r.GuestNumber
	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 Status = 9 THEN 0 ELSE Status END Status,
		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
	INTO #T2
	FROM #T1 T1
	WHERE (T1.DepartureDate > T1.Arrivaldate) OR ((T1.DepartureDate = T1.Arrivaldate) AND (T1.ArrivalTime < T1.DepartureTime))

	SELECT Status, RLX.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 <= @InputDepartureDate
	  AND RLDeparture >= @InputArrivalDate
	  AND 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 ELSE
  BEGIN
	DECLARE @HotelCustomerNumber INT
	SELECT @HotelCustomerNumber = C.CustomerNumber FROM Rooms ro (NOLOCK)
	INNER JOIN Customer C (NOLOCK) On ro.HotelNumber = c.HotelNumber

	IF dbo.GetHotelSetupKeyValue('CheckForSectorMaxPax', @HotelCustomerNumber) = '1'
	BEGIN
		DECLARE @Sector INT
		DECLARE @MaxPax INT
		SELECT @Sector = ISNULL(Sector, 0) FROM Rooms (Nolock) 
		WHERE RoomNumber = @RoomNumberID 

		IF @Sector > 0
		BEGIN
		  SELECT @MaxPax = ISNULL(MaxPax, 0) FROM Sectors (NOLOCK)
		  WHERE SectorNumber = @Sector
		  IF @MaxPax > 0
		  BEGIN
			DECLARE @SumPax INT
			DECLARE @WantedPax INT

			SELECT @SumPax = dbo.GetSumPaxForSector(@Sector, @ArrivalDate, @DepartureDate)

			SET @SumPax = ISNULL(@SumPax, 0)

			SELECT @WantedPax = ISNULL(Adults, 0) + ISNULL(Children1, 0) + ISNULL(Children2, 0) FROM ReservationRoomLine (NOLOCK)
			WHERE LINENUMBER = @LineNumber AND ISNULL(RoomNumber, 0) = 0

			SET @WantedPax = ISNULL(@WantedPax, 0)

			IF @SumPax + @WantedPax > @MaxPax
			  SET @RoomStatus = 7
		  END
		END
	END
  END

  SELECT @RoomStatus
END
