CREATE FUNCTION dbo.ReservationGap(@RoomNumber INTEGER, @LineNumber INTEGER, @GapDays INTEGER, @Arrival DATETIME, @Departure DATETIME)
RETURNS BIT AS
/*
  Function which returns 1 (True) if there is gap between current arrival and last booked period. Also checks for gap before next booked period if they are 4 weeks. The 4 weeks before or after must have a hole if there is not a gap

  LOG:
  20060222 JP: Re-created with more logic
  20060220 JP: Created

  PRE: We have the period of the reservation we are creating

  NOTES:
  - 
*/
BEGIN 
  DECLARE 
  @Result BIT,
  @ResCountBefore INTEGER,
  @ResCountAfter INTEGER

  SET @Result = 1 -- assume no gaps

  -- Gap before
  SELECT @ResCountBefore = COUNT(*) FROM ReservationRoomLine RL WITH (NOLOCK) 
  INNER JOIN ReservationRoomLineXTab RLX WITH (NOLOCK) ON RL.LineNumber=RLX.LineNumber AND RL.LineNumber<>@LineNumber AND RLX.ReferenceNumber>0 AND RL.RoomNumber=@RoomNumber AND RL.ArrivalDate<@Arrival AND RL.DepartureDate>@Arrival - @GapDays
  INNER JOIN Reservations R WITH (NOLOCK) ON R.ReferenceNumber=RLX.ReferenceNumber AND R.GuestNumber=RLX.GuestNumber AND R.Status<=6

  -- Gap after
  SELECT @ResCountAfter = COUNT(*) FROM ReservationRoomLine RL WITH (NOLOCK) 
  INNER JOIN ReservationRoomLineXTab RLX WITH (NOLOCK) ON RL.LineNumber=RLX.LineNumber AND RL.LineNumber<>@LineNumber AND RLX.ReferenceNumber>0 AND RL.RoomNumber=@RoomNumber AND RL.ArrivalDate<@Departure + @GapDays AND RL.DepartureDate>@Departure
  INNER JOIN Reservations R WITH (NOLOCK) ON R.ReferenceNumber=RLX.ReferenceNumber AND R.GuestNumber=RLX.GuestNumber AND R.Status<=6

  IF @ResCountBefore + @ResCountAfter > 0  -- we don't have our gaps, we do need to check for gap before and after
  BEGIN
    IF (@Result = 1) and (@ResCountBefore > 0)  -- check if the 4 weeks before is without holes. if it is, then we have a result = 0
      SELECT @Result = dbo.HolesInPeriod (@RoomNumber, @Arrival - @GapDays - (4*7), @Arrival - @GapDays)

    IF (@Result = 1) and (@ResCountAfter > 0)
      SELECT @Result = dbo.HolesInPeriod (@RoomNumber, @Departure + @GapDays, @Departure + @GapDays + (4*7))
  END

  RETURN @Result
END

