CREATE FUNCTION dbo.GetAvailableMins(@RoomNumber INT, @Arrival DATETIME, @IncludeInhouse BIT = 1)
RETURNS INT AS
/*
    Created by Jacob Pedersen 2006-07-17

    Last Changed:
    20061011 JP BUG: do not check outofhouse status when cashier enabled (see IsRoomAvailable)

    NOTES: @IncludeInhouse is used when allocating a room while checking in. If you want a room which is not already inhouse
*/
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 @tmpArrival=MIN(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 CONVERT(DATETIME,RL.ArrivalDate, 112) + CONVERT(DATETIME,@DefaultArrivalTime, 108) 
	        ELSE CONVERT(DATETIME,RL.ArrivalDate, 112) + CONVERT(DATETIME,RL.ArrivalTime, 108)
		    END)
FROM dbo.ReservationRoomLine RL WITH (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 -- AND RLX.ReferenceNumber <> -1
 AND RL.RoomNumber=@RoomNumber AND 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 CONVERT(DATETIME,DepartureDate, 112) + CONVERT(DATETIME,@DefaultDepartureTime, 108)
	        ELSE CONVERT(DATETIME,DepartureDate, 112) + CONVERT(DATETIME,DepartureTime, 108)
	     END >= 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 DATEADD(dd, 0, DATEDIFF(dd, 0, @Arrival)) + CONVERT(DATETIME,@DefaultArrivalTime, 108)
	        ELSE  @Arrival END
INNER JOIN dbo.Reservations R WITH (NOLOCK) ON R.ReferenceNumber=RLX.ReferenceNumber AND R.GuestNumber=RLX.GuestNumber AND R.Status < @MinStatus
--ORDER BY CONVERT(DATETIME,RL.ArrivalDate, 112) + CONVERT(DATETIME,RL.ArrivalTime, 108)

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)


RETURN @Result

END

