CREATE PROCEDURE dbo.OnlineFindAvailableCourtsTime @AStartDateTime DATETIME, @AEndDateTime DATETIME, @AInterval INT, @AOnlineBookTypeNumber INT, @AHotelNumber INT, @ANumberOfHalls INT, @ACountryNumber  INT = 0 AS

--Last changed 2007 0928 param onlinebooktypenumber changed from key to visual number
--Created AV 2007 0905

  DECLARE
  @HallNumberId INT,
  @HallName VARCHAR(50),
  @TranslatedHallName VARCHAR(50),
  @HallLineNumber INT,
  @StartTime VARCHAR(5),
  @EndTime VARCHAR(5),
  @TempDateTime DATETIME,
  @ArrivalDate DATETIME,
  @DepartureDate DATETIME,
  @IntervalStartTime DATETIME,
  @IntervalEndTime DATETIME,
  @DoContinue BIT,
  @MinAvailableHalls INT,
  @MaxUnitsPerReservation INT,
  @FoundAvailableHalls INT,
  @OnlineBookTypeNumberID INT
   
  SET @StartTime = CONVERT(VARCHAR(5),@AStartDateTime,108)   
  SET @EndTime = CONVERT(VARCHAR(5),@AEndDateTime,108) 



  CREATE TABLE #ResultSet (
		HallNumberID INT,
                           HallName VARCHAR(50),
		IntervalStartTime DATETIME,
                           IntervalEndTime DATETIME
		)
  SET @DoContinue = 1
  SELECT @OnlineBookTypeNumberID = OnlineBookTypeNumber FROM OnlineBookType WITH (NOLOCK) WHERE Number = @AOnlineBookTypeNumber

  SELECT @MinAvailableHalls =ISNULL( YieldOnlineBookTypeXTab.MinAvailableUnits,0), @MaxUnitsPerReservation = ISNULL(YieldOnlineBookTypeXTab.MaxUnitsReservation,0)  FROM RoomBookingStatusYield WITH (NOLOCK)  INNER JOIN Yield WITH (NOLOCK) ON Yield.YieldNumber = RoomBookingStatusYield.OnlineYieldFilterHall AND Yield.FilterTypeNumber = 2 
  INNER JOIN YieldOnlineBookTypeXTab WITH (NOLOCK) ON Yield.YieldNumber = YieldOnlineBookTypeXTab.FK_YieldNumber AND YieldOnlineBookTypeXTab.FK_OnlineBookTypeNumber = @OnlineBookTypeNumberID
  WHERE CONVERT(VARCHAR(8),DateStamp,112) = CONVERT(VARCHAR(8),@AStartDateTime,112)

  IF @MaxUnitsPerReservation < @ANumberOfHalls
    SET @DoContinue = 0

  IF @DoContinue = 1
  BEGIN

    SET @TempDateTime = @AStartDateTime

    WHILE @TempDateTime <@AEndDateTime
    BEGIN
      DECLARE #Hall_Cursor CURSOR LOCAL FAST_FORWARD FOR
      SELECT  DISTINCT ConferenceRooms.ConferenceRoomNumber, ConferenceRooms.Name,Translation.Name AS TranslatedHallName
      FROM ConferenceRooms WITH (NOLOCK)
      LEFT OUTER JOIN Translation ON Translation.ObjectNumber = ConferenceRooms.ConferenceRoomNumber AND Translation.Type = 4 AND CountryNumber = @ACountryNumber
      LEFT OUTER JOIN OnlineBookTypeHallXTab WITH (NOLOCK) ON OnlineBookTypeHallXTab.HallNumber = ConferenceRooms.ConferenceRoomNumber
      WHERE ISNULL(ISTable,0) = 0 
      AND AllowFilterPicassoOnline = 1
      AND (CONVERT(VARCHAR(8),OpeningTime,108) ='00:00:00' OR (CONVERT(VARCHAR(5),OpeningTime,108) <=@StartTime  AND CONVERT(VARCHAR(5), CloseTime,108) >=@EndTime))
      AND (@AHotelNumber = -1 OR (@AHotelNumber > -1 AND ConferenceRooms.HotelNumber = @AHotelNumber))
      AND OnlineBookTypeHallXTab.OnlineBookTypeNumber = @OnlineBookTypeNumberID

      OPEN #Hall_Cursor

      FETCH NEXT FROM #Hall_Cursor
      INTO @HallNumberId, @HallName,@TranslatedHallName

      WHILE (@@FETCH_STATUS <> -1)
      BEGIN
        IF @TranslatedHallName IS NOT NULL 
          SET @HallName = @TranslatedHallName
        INSERT INTO #ResultSet (HallNumberID, HallName, IntervalStartTime, IntervalEndTime) VALUES (@HallNumberID, @HallName, @TempDateTime, DATEADD(MINUTE,@AInterVal,@TempDateTime))
        FETCH NEXT FROM #Hall_Cursor
        INTO @HallNumberId, @HallName,@TranslatedHallName
      END
      SET @TempDateTime = DATEADD(MINUTE, @AInterval, @TempDateTime)
      CLOSE #Hall_Cursor
      DEALLOCATE #Hall_Cursor
    END


    DECLARE #Rescur CURSOR LOCAL FAST_FORWARD FOR
    SELECT DISTINCT ConferenceRoomsXtab.CollectsConferenceRoomNumber,ReservationConfLineNumber, CONVERT(DATETIME,ReservationConferenceLine.ArrivalDate,112) + CONVERT(DATETIME,ReservationConferenceLine.ArrivalTime,108),
    CONVERT(DATETIME,ReservationConferenceLine.DepartureDate,112) + CONVERT(DATETIME,ReservationConferenceLine.DepartureTime,108)

     FROM ReservationConferenceLine WITH (NOLOCK)  INNER JOIN RESERVATIONS WITH (NOLOCK) 
    ON RESERVATIONCONFERENCELINE.ReferenceNumber = RESERVATIONS.ReferenceNumber
    LEFT OUTER JOIN ReservationMain WITH (NOLOCK) ON ReservationMain.ReferenceNumber = Reservations.ReferenceNumber
    INNER JOIN CONFERENCEROOMSXTAB WITH (NOLOCK) 
    ON ConferenceRoomsxtab.ConferenceRoomNumber = ReservationConferenceLine.ConferenceRoomNumber 
    INNER JOIN ConferenceRooms WITH (NOLOCK) ON ConferenceRoomsXtab.CollectsConferenceRoomNumber = ConferenceRooms.ConferenceRoomNumber
    LEFT OUTER JOIN OnlineBookTypeHallXTab WITH (NOLOCK) ON OnlineBookTypeHallXTab.HallNumber = ReservationConferenceLine.ConferenceRoomNumber
    WHERE Reservations.GuestNumber = 0  AND 
    ISNULL(ReservationConferenceLine.SchemePattern, 0) = 0 AND Reservations.Status < 7
    AND (OnlineBookTypeHallXTab.OnlineBookTypeNumber=  @OnlineBookTypeNumberID)
    AND ((@AHotelNumber = -1) OR (@AHotelNumber > -1 AND ReservationMain.HotelNumber = @AHotelNumber))
    AND CONVERT(DATETIME,ReservationConferenceLine.ArrivalDate,112) + CONVERT(DATETIME,ReservationConferenceLine.ArrivalTime,108) < DATEADD(MINUTE, ISNULL(ConferenceRooms.PauseInMinutes,0),@AEndDateTime )
    AND CONVERT(DATETIME,ReservationConferenceLine.DepartureDate,112) + CONVERT(DATETIME,ReservationConferenceLine.DepartureTime,108) > DATEADD(MINUTE,- ISNULL(ConferenceRooms.PauseInMinutes,0),@AStartDateTime)
    OPEN #Rescur
  
    FETCH NEXT FROM #Rescur
    INTO @HallNumberId, @HallLineNumber, @ArrivalDate, @DepartureDate
    WHILE (@@FETCH_STATUS <> -1)
    BEGIN
      SET @TempDateTime = @AStartDateTime
      WHILE @TempDateTime <@AEndDateTime
      BEGIN
        IF @ArrivalDate < DATEADD(MINUTE,@AInterVal,@TempDateTime) AND @DepartureDate > @TempDateTime
          DELETE FROM #ResultSet WHERE HAllNumberID = @HallNumberID AND IntervalStartTime = @TempDateTime
        SET @TempDateTime = DATEADD(MINUTE, @AInterval, @TempDateTime)
      END

    FETCH NEXT FROM #Rescur INTO @HallNumberId, @HallLineNumber, @ArrivalDate, @DepartureDate
    END
    CLOSE #Rescur
    DEALLOCATE #Rescur


--FINALLY WE HAVE TO CHECK THE YIELDFILTER MINAVAILABLE HALLS

   SET @TempDateTime = @AStartDateTime
   WHILE @TempDateTime <@AEndDateTime
   BEGIN
     SELECT @FoundAvailableHalls = Count(*) FROM #ResultSet WITH (NOLOCK) WHERE  IntervalStartTime = @TempDateTime
     SET @FoundAvailableHalls = @FoundAvailableHalls - @ANumberOfHalls

     IF @FoundAvailableHalls < @MinAvailableHalls 
       DELETE FROM #ResultSet WHERE IntervalStartTime = @TempDateTime
     SET @TempDateTime = DATEADD(MINUTE, @AInterval, @TempDateTime)
   END

    
  END
SELECT * FROM #ResultSet

DROP TABLE #ResultSet

