CREATE PROCEDURE [dbo].[OptimizeHallTypeAllocations]
@AStartDateTime datetime, 
@AEndDateTime datetime, 
@AHallType int
AS

-- # HallCur variables
DECLARE @HallCurHallNumberID INT
DECLARE @HallCurHallNumber INT

-- #RefCur variables:
DECLARE @ReferenceNumber INT
DECLARE @ReferenceConferenceLineCount INT
DECLARE @ReferenceArrivalTime VARCHAR(5)
DECLARE @ReferenceDepartureTime VARCHAR(5)

-- #RefConfLineCursor variables:
DECLARE @ResConfLineHallNumberID INT
DECLARE @ResConfLineHallLineID INT
DECLARE @ResConfLineReferenceNumber INT 
DECLARE @ResConfLineHallNumber INT
DECLARE @ResConfLineArrivalDate VARCHAR(8) 
DECLARE @ResConfLineArrivalTime VARCHAR(5)
DECLARE @ResConfLineDepartureDate VARCHAR(8) 
DECLARE @ResConfLineDepartureTime VARCHAR(5)

-- Misc. variables:
DECLARE @WeekDayNumber INT
DECLARE @NewHallNumberId INT

DECLARE @NewReservationConferenceLineNumber INT
DECLARE @NewHallNumber INT
DECLARE @NewRecordNumber INT
DECLARE @NewTemp INT

DECLARE @TempHallNumberId INT
DECLARE @TempHallNumber INT
DECLARE @TempInUse INT
DECLARE @TempNewHallNumber INT
DECLARE @TempDepartureDateTime DATETIME

SET @WeekDayNumber = DATEPART(WEEKDAY,@AStartDateTime)

CREATE TABLE #AvailableHalls (
  HallNumberId INT NOT NULL,
  HallNumber INT NOT NULL,
  InUse INT                            
)

-- Fetch Halls we need to optimize:
DECLARE #HallCur CURSOR LOCAL FAST_FORWARD FOR
SELECT ConferenceRooms.ConferenceRoomNumber, ConferenceRooms.Number
FROM ConferenceRooms WITH (NOLOCK)
WHERE ConferenceRoomTypeNumber = @AHallType
  AND CONVERT(VARCHAR(8),TableDate, 112 ) = CONVERT(VARCHAR(8), @AStartDateTime, 112)   
--ORDER BY Number

OPEN #HallCur

FETCH NEXT FROM #HallCur
INTO @HallCurHallNumberId, @HallCurHallNumber

WHILE (@@FETCH_STATUS <> -1)
BEGIN
  INSERT INTO [#AvailableHalls] (HallNumberID, HallNumber, InUse) VALUES (@HallCurHallNumberID,@HallCurHallNumber, 0)
  FETCH NEXT FROM #HallCur
  INTO @HallCurHallNumberId, @HallCurHallNumber
END

CLOSE #HallCur 
DEALLOCATE #HallCur

-- Used to save the new tablenumbers for ReservationConferenceLine
CREATE TABLE #TempReservationConfLine (
  ReferenceNumber INT NOT NULL,
  ReservationConfLineNumber INT NOT NULL,
  ArrivalDateTime DATETIME,
  DepartureDateTime DATETIME,
  OldHallNumber INT,
  NewHallNumber INT,
  RecordCount INT
)

-- Select ReferenceNumbers we need to process
DECLARE #RefCursor CURSOR LOCAL FAST_FORWARD FOR
SELECT ReservationConferenceLine.ReferenceNumber, COUNT(ReservationConferenceLine.ReferenceNumber)
,ReservationConferenceLine.ArrivalTime,ReservationConferenceLine.DepartureTime
FROM ReservationConferenceLine WITH (NOLOCK)
INNER JOIN RESERVATIONS WITH (NOLOCK) ON RESERVATIONCONFERENCELINE.ReferenceNumber = RESERVATIONS.ReferenceNumber AND ISNULL(ReservationConferenceLine.GuestNo, 0) = Reservations.GuestNumber
INNER JOIN CONFERENCEROOMSXTAB WITH (NOLOCK) ON ConferenceRoomsxtab.ConferenceRoomNumber = ReservationConferenceLine.ConferenceRoomNumber 
INNER JOIN ConferenceRooms WITH (NOLOCK) ON ConferenceRoomsXtab.CollectsConferenceRoomNumber = ConferenceRooms.ConferenceRoomNumber
WHERE ISNULL(ReservationConferenceLine.SchemePattern, 0) = 0
  AND Reservations.Status < 5
  AND ReservationConferenceLine.ConferenceRoomNumber IS NOT NULL
  AND ReservationConferenceLine.ArrivalDate = CONVERT(VARCHAR(8),@AStartDatetime,112)
  AND ConferenceRooms.ConferenceRoomTypeNumber = @AHallType
GROUP BY reservationconferenceline.referencenumber,reservationconferenceline.arrivaltime,reservationconferenceline.departuretime
ORDER BY reservationconferenceline.arrivaltime ASC, reservationconferenceline.departuretime DESC, count(reservationconferenceline.referencenumber) DESC

OPEN #RefCursor

FETCH NEXT FROM #RefCursor
INTO @ReferenceNumber, @ReferenceConferenceLineCount, @ReferenceArrivalTime, @ReferenceDepartureTime

SET @NewHallNumberId = -1

WHILE (@@FETCH_STATUS <> -1)
BEGIN
  -- For each ReferenceNumber we need to select all ReservationConferenceLines:
  DECLARE #RefConfLineCursor CURSOR LOCAL FAST_FORWARD FOR
  SELECT DISTINCT ConferenceRoomsXtab.CollectsConferenceRoomNumber, ReservationConferenceLine.ReservationConfLineNumber, ReservationConferenceLine.ReferenceNumber, ConferenceRooms.Number, ConferenceRooms.ConferenceRoomNumber, ReservationConferenceLine.ArrivalDate, ReservationConferenceLine.ArrivalTime, ReservationConferenceLine.DepartureDate, ReservationConferenceLine.DepartureTime
  FROM ReservationConferenceLine
  INNER JOIN CONFERENCEROOMSXTAB WITH (NOLOCK) ON ConferenceRoomsxtab.ConferenceRoomNumber = ReservationConferenceLine.ConferenceRoomNumber 
  INNER JOIN ConferenceRooms WITH (NOLOCK) ON ConferenceRoomsXtab.CollectsConferenceRoomNumber = ConferenceRooms.ConferenceRoomNumber
  WHERE ReservationConferenceLine.ReferenceNumber = @ReferenceNumber
    AND ReservationConferenceLine.ArrivalDate = CONVERT(VARCHAR(8),@AStartDateTime,112)
    AND ConferenceRooms.ConferenceRoomTypeNumber = @AHallType

  OPEN #RefConfLineCursor
  FETCH NEXT FROM #RefConfLineCursor
  INTO @ResConfLineHallNumberID, @ResConfLineHallLineID, @ResConfLineReferenceNumber, 
       @ResConfLineHallNumber, @ResConfLineHallNumberId, @ResConfLineArrivalDate, 
       @ResConfLineArrivalTime, @ResConfLineDepartureDate, @ResConfLineDepartureTime

  SET @NewRecordNumber = 0

  WHILE (@@FETCH_STATUS <> -1)
  BEGIN
    --
    -- For each ReferenceConferenceLine we need to find the next available Hall in the specified timeframe.
    -- 
    -- NOTE: We need to check the weekday (even/odd number) also and allocate the new hallnumber accordingly (lowest -> highest or highest -> lowest)
    IF @NewHallNumberId = -1
    BEGIN
      IF @WeekDayNumber IN (1,3,5,7)
        SELECT TOP 1 @NewHallNumberId = HallNumberId FROM #AvailableHalls
        ORDER BY HallNumber DESC
      ELSE
        SELECT TOP 1 @NewHallNumberId = HallNumberId FROM #AvailableHalls
        ORDER BY HallNumber ASC

      INSERT INTO #TempReservationConfLine (
        ReferenceNumber,
        ReservationConfLineNumber,
        ArrivalDateTime,
        DepartureDateTime,
        OldHallNumber,
        NewHallNumber,
        RecordCount
      ) VALUES (
        @ResConfLineReferenceNumber,
        @ResConfLineHallLineID,
        CONVERT(DATETIME,@ResConfLineArrivalDate,112)+CONVERT(DATETIME,@ResConfLineArrivalTime,108),
        CONVERT(DATETIME,@ResConfLineDepartureDate,112)+CONVERT(DATETIME,@ResConfLineDepartureTime,108),
        @ResConfLineHallNumberId,
        @NewHallNumberId,
        @ReferenceConferenceLineCount
      )
    END
    ELSE
    BEGIN

      -- How do we get the next Hallnumber to use ?
      -- Loop: Hent nyt Hallnummer fra #AvailableTables
      --   Check om det er brugt i TempReservationConfLine i det fundne tidsrum fra #RefConfLineCursor

      IF @WeekDayNumber IN (1,3,5,7)
        DECLARE #TempHalls CURSOR LOCAL FAST_FORWARD FOR
        SELECT * FROM #AvailableHalls 
        WHERE InUse = 0
        ORDER BY HallNumber DESC
      ELSE
        DECLARE #TempHalls CURSOR LOCAL FAST_FORWARD FOR
        SELECT * FROM #AvailableHalls 
        WHERE InUse = 0
        ORDER BY HallNumber ASC

      OPEN #TempHalls
      FETCH NEXT FROM #TempHalls
      INTO @TempHallNumberId, @TempHallNumber, @TempInUse

      WHILE (@@FETCH_STATUS <> -1)
      BEGIN
        -- 20090729/RN: This Loop is NOT ready for use

        -- Select records with the Hallnumber from #AvailableHalls
        -- and check each's DepartureTime, if it is equal or higher
        -- than @ResConfLineArrivalTime we can use this hallnumber
        -- else we need a new hallnumber
        SELECT @TempNewHallNumber = NewHallNumber,
          @TempDepartureDateTime = DepartureDateTime
        FROM #TempReservationConfLine
        WHERE NewHallNumber = @TempHallNumberId
          --AND ArrivalTime <= @ResConfLineArrivalTime
          --AND DepartureTime <= @ResConfLineDepartureTime
        --ORDER BY DepartureTime ASC

		IF CONVERT(DATETIME,@ResConfLineArrivalDate,112)+CONVERT(DATETIME, @ResConfLineArrivalTime,108) >= @TempDepartureDateTime
        BEGIN
          SET @NewTemp = @TempHallNumberId
          BREAK
        END
        FETCH NEXT FROM #TempHalls
        INTO @TempHallNumberId, @TempHallNumber, @TempInUse
      END
      
      CLOSE #TempHalls
      DEALLOCATE #TempHalls
      
      /*
      SELECT @NewTemp = MAX(NewHallNumber)
      FROM #TempReservationConfLine
      WHERE  ArrivalTime <= @ResConfLineArrivalTime
        AND  DepartureTime >= @ResConfLineDepartureTime
        --AND RecordCount <= @ReferenceConferenceLineCount
      */
      -- Select next Hallnumber which is higher than the one we just found: 
      
      IF @WeekDayNumber IN (1,3,5,7)
        SELECT TOP 1 @NewHallNumberId = HallNumberId FROM #AvailableHalls 
        WHERE HallNumberId < @NewTemp
          AND InUse = 0
        ORDER BY HallNumber DESC
      ELSE
        SELECT TOP 1 @NewHallNumberId = HallNumberId FROM #AvailableHalls 
        WHERE HallNumberId > @NewTemp
          AND InUse = 0
        ORDER BY HallNumber ASC
      

      INSERT INTO #TempReservationConfLine (
        ReferenceNumber,
        ReservationConfLineNumber,
        ArrivalDateTime,
        DepartureDateTime,
        OldHallNumber,
        NewHallNumber,
        RecordCount
      ) VALUES (
        @ResConfLineReferenceNumber,
        @ResConfLineHallLineID,
        CONVERT(DATETIME,@ResConfLineArrivalDate,112)+CONVERT(DATETIME,@ResConfLineArrivalTime,108),
        CONVERT(DATETIME,@ResConfLineDepartureDate,112)+CONVERT(DATETIME,@ResConfLineDepartureTime,108),
        @ResConfLineHallNumberId,
        @NewHallNumberId,
        @ReferenceConferenceLineCount
      )

      UPDATE #AvailableHalls SET InUse = 1 WHERE HallNumberId = @NewHallNumberId
    END

    FETCH NEXT FROM #RefConfLineCursor
    INTO @ResConfLineHallNumberID, @ResConfLineHallLineID, @ResConfLineReferenceNumber, 
         @ResConfLineHallNumber, @ResConfLineHallNumberId, @ResConfLineArrivalDate, 
         @ResConfLineArrivalTime, @ResConfLineDepartureDate, @ResConfLineDepartureTime
  END

  CLOSE #RefConfLineCursor
  DEALLOCATE #RefConfLineCursor

  UPDATE #AvailableHalls SET InUse = 0

  FETCH NEXT FROM #RefCursor
  INTO @ReferenceNumber, @ReferenceConferenceLineCount, @ReferenceArrivalTime, @ReferenceDepartureTime
END

CLOSE #RefCursor
DEALLOCATE #RefCursor


-- When finished we need to update the real ReservationConferenceLine table
-- with the newly allocated HallNumbers:

DECLARE #NewCur CURSOR LOCAL FAST_FORWARD FOR
SELECT ReservationConfLineNumber,NewHallNumber
FROM #TempReservationConfLine

OPEN #NewCur
FETCH NEXT FROM #NewCur
INTO @NewReservationConferenceLineNumber, @NewHallNumber

WHILE (@@FETCH_STATUS <> -1)
BEGIN
  --UPDATE ReservationConferenceLine
  --SET ConferenceRoomNumber= @NewHallNumber
  --WHERE ReservationConfLineNumber = @NewReservationConferenceLineNumber

  FETCH NEXT FROM #NewCur
  INTO @NewReservationConferenceLineNumber, @NewHallNumber
END

CLOSE #NewCur
DEALLOCATE #NewCur

SELECT * FROM #TempReservationConfLine
SELECT * FROM #AvailableHalls

DROP TABLE #AvailableHalls
DROP TABLE #TempReservationConfLine
