CREATE PROCEDURE [dbo].[AvailableMinutes] @RoomNumber INT, @Arrival DATETIME, @IncludeInhouse BIT = 1
AS  
BEGIN 
	DECLARE
	@Result INT,
	@tmpArrival DATETIME,
	@CashierDisabled BIT,
	@DefaultArrivalTime VARCHAR(5),
	@DefaultDepartureTime VARCHAR(5),
	@MinStatus INT

	SELECT @CashierDisabled = CONVERT(BIT, KeyValue) FROM HotelSetup WITH (NOLOCK) WHERE SearchKey='CashierDisabling'
	SELECT @DefaultArrivalTime = KeyValue FROM HotelSETUP WITH (NOLOCK) WHERE SearchKey = 'TimeOfArrivalForRes'
	SET @DefaultArrivalTime = ISNULL(@DefaultArrivalTime, '0')
	SELECT @DefaultDepartureTime = KeyValue FROM HotelSETUP WITH (NOLOCK) WHERE SearchKey = 'TimeOfDepartureForRes'
	SET @DefaultDepartureTime = ISNULL(@DefaultDepartureTime, '0')

	SET @MinStatus = 5
	IF @IncludeInhouse = 1
		SET  @MinStatus = 6
	IF @CashierDisabled = 1
		SET  @MinStatus = 7



	SELECT 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
	INNER JOIN dbo.ReservationRoomLineXTab RLX WITH (NOLOCK) ON RL.LineNumber=RLX.LineNumber
	INNER JOIN dbo.Reservations R WITH (NOLOCK) ON R.ReferenceNumber=RLX.ReferenceNumber AND R.GuestNumber=RLX.GuestNumber AND R.Status < @MinStatus
	WHERE RoomNumber = @RoomNumber

	SELECT T1.LineNumber,
		CASE WHEN DefaultBookTimeON = 1 
		  THEN CONVERT(DATETIME,T1.ArrivalDate, 112) + CONVERT(DATETIME,@DefaultArrivalTime, 108) 
		  ELSE CONVERT(DATETIME,T1.ArrivalDate, 112) + CONVERT(DATETIME,T1.ArrivalTime, 108)
		END AS RLArrival,
		CASE WHEN DefaultBookTimeON = 1 
		  THEN CONVERT(DATETIME,T1.DepartureDate, 112) + CONVERT(DATETIME,@DefaultDepartureTime, 108)
		  ELSE CONVERT(DATETIME,DepartureDate, 112) + CONVERT(DATETIME,DepartureTime, 108)
		END AS RLDeparture,
		CASE WHEN DefaultBookTimeON = 1 
	 	  THEN DATEADD(dd, 0, DATEDIFF(dd, 0, @Arrival)) + CONVERT(DATETIME,@DefaultArrivalTime, 108)
		  ELSE  @Arrival
		END AS InputArrival
	INTO #T2
	FROM #T1 T1

	SELECT @tmpArrival = Min(RLArrival)
	FROM #T2 T2
	WHERE RLDeparture >= InputArrival

	DROP TABLE #T1
	DROP TABLE #T2

	IF @tmpArrival is null -- available in the whole period we are checking so set result to this many days
	  SET @Result = 10000000 -- 10 mill for eternity DATEDIFF(MINUTE, @Arrival, @Departure)
	ELSE
	  SET @Result = DATEDIFF(MINUTE, @Arrival, @tmpArrival)

	SELECT @Result
	
END