CREATE PROCEDURE dbo.OnlineFindAvailableHallsTimeFromWare @AStartDateTime DATETIME, @AEndDateTime DATETIME, @AInterval INT, @ATreatmentTimeInMinutes INT, @AHotelNumber INT, @ANumberOfHalls INT, @ACountryNumber  INT = 0, @AWareNumber INT = 0  AS

-- Created AV 2008 0529

-- Last Changed: AV 2009 0423 added parameter @ATreatmentTimeInMinutes
--               RN 2009 0930 Added ISNULL check for Translation.Type

  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,
  @FoundAvailableHalls INT,
  @Counter 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 @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 ISNULL(Translation.Type,0) = 4 AND CountryNumber = @ACountryNumber
      INNER JOIN WareHallXTab WITH (NOLOCK) ON WareHallXTab.FK_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 WareHallXTab.FK_WareNumber = @AWareNumber
      AND dbo.ValidateWareWeekDay(WareHallXTab.FK_WareNumber, @AStartDateTime) = 1

      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,@AtreatmentTimeInMinutes,@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
    INNER JOIN WareHallXTab WITH (NOLOCK) ON WareHallXTab.FK_HallNumber = ConferenceRooms.ConferenceRoomNumber
    WHERE Reservations.GuestNumber = 0  AND 
    ISNULL(ReservationConferenceLine.SchemePattern, 0) = 0 AND Reservations.Status < 7
    AND (ReservationConferenceLine.ReferenceNumber >= 0) -- Added 20091016/RN, need to discard Out of Order and Out for Service
    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)
    AND WareHallXTab.FK_WareNumber = @AWareNumber 
    AND ConferenceRooms.AllowFilterPicassoOnline = 1
    AND dbo.ValidateWareWeekDay(WareHallXTab.FK_WareNumber, @AStartDateTime) = 1
    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,@AtreatmentTimeInMinutes,@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 delete double records. We only need one record per interval. 

   SET @TempDateTime = @AStartDateTime
   WHILE @TempDateTime < @AEndDateTime
   BEGIN
     SELECT @FoundAvailableHalls = Count(*) FROM #ResultSet WITH (NOLOCK) WHERE  IntervalStartTime = @TempDateTime
     IF ISNULL(@FoundAvailableHalls, 0) > 1
     BEGIN
       SET @Counter = 1
       DECLARE #Deletecur CURSOR LOCAL FAST_FORWARD FOR      
       SELECT DISTINCT HallNumberID FROM #ResultSet WITH (NOLOCK) WHERE  IntervalStartTime = @TempDateTime
       OPEN #Deletecur
       FETCH NEXT FROM #Deletecur INTO @HallNumberId
       WHILE (@@FETCH_STATUS <> -1)
       BEGIN
         IF @Counter > 1
           DELETE FROM #ResultSet WHERE IntervalStartTime = @TempDateTime AND HallNumberID = @HallNumberID
         SET @Counter = @Counter + 1  
         FETCH NEXT FROM #Deletecur INTO @HallNumberId
       END   
       CLOSE #Deletecur
       DEALLOCATE #Deletecur
     END
     SET @TempDateTime = DATEADD(MINUTE, @AInterval, @TempDateTime)
   END


  SELECT * FROM #ResultSet WITH (NOLOCK)

  DROP TABLE #ResultSet
