CREATE FUNCTION dbo.IsRoomAvailable (@RoomNumber INTEGER, @LineNumber INTEGER, @StartDate DATETIME, @EndDate DATETIME, @IncludeOutOfHouseStatus BIT)
RETURNS BIT AS  
/*
  Function which returns True if specified room is available in specified period.

  LOG:
  20060102 JP: Created

  NOTES:
  - LineNumber can be specified if we already have a booking which we are changing period for. It's not a requirement!
  - Same logic as in TRoomFX.IsRoomAvailable function

*/
BEGIN 
  DECLARE
  @Result BIT,
  @ResCount INTEGER,
  @Status TINYINT

  IF @IncludeOutOfHouseStatus = 1
    SET @Status = 7
  ELSE
    SET @Status = 6

  SELECT @ResCount = COUNT(*) FROM ReservationRoomLine RL WITH (NOLOCK)
  INNER JOIN ReservationRoomLineXTab RLX WITH (NOLOCK) ON RL.LineNumber=RLX.LineNumber AND RL.RoomNumber=@RoomNumber AND RL.LineNumber <> @LineNumber AND CONVERT(DATETIME,ArrivalDate,112)+CONVERT(DATETIME,ArrivalTime,108) <= @EndDate AND CONVERT(DATETIME,DepartureDate,112)+CONVERT(DATETIME,DepartureTime,108) >= @StartDate AND RLX.ReferenceNumber <> -2
  INNER JOIN Reservations R WITH (NOLOCK) ON R.ReferenceNumber=RLX.ReferenceNumber AND R.GuestNumber=RLX.GuestNumber AND R.Status<@Status

  IF @ResCount = 0
    SET @Result = 1
  ELSE
    SET @Result = 0

  RETURN @Result
END

