CREATE PROCEDURE DBO.MergeOutOfOrderPeriods @RoomNumberID INT AS
/*
   Created by Jacob Pedersen
   Changes:

   JP 20051205: major rewrite; merge overlapping periods. faster because of less SQL. only merge future
   JP 20050106: match on time also
   JP 20040414: outforservice

   Description: If we have 2 roomlines which are overlapping we merge them into one

   NOTE: Only works with OutOfOrder rooms.  Not halls
*/
SET NOCOUNT ON

--print '-MergeOutOfOrderPeriods-'

DECLARE
@ReferenceNumber INT,
@LineNumber INT,
@Arrival DATETIME,
@Departure DATETIME,
@MergedArrival DATETIME,
@MergedDeparture DATETIME,
@SystemDate CHAR(8)

SELECT @SystemDate=SystemDate FROM System WITH (NOLOCK) WHERE Number=1

SET @ReferenceNumber = 0

WHILE @ReferenceNumber >= -2 -- 0 : OutOfOrder 
BEGIN
--  PRINT 'Current referencenumber: '+convert(varchar, @referencenumber)
  DECLARE #RoomLineCur2 CURSOR FORWARD_ONLY FOR

    SELECT ReservationRoomLine.LineNumber, CONVERT(DATETIME, ArrivalDate, 112)+CONVERT(DATETIME, ArrivalTime, 108), CONVERT(DATETIME, DepartureDate, 112)+CONVERT(DATETIME, DepartureTime, 108) FROM ReservationRoomLine WITH (NOLOCK)
    INNER JOIN ReservationRoomLineXTab WITH (NOLOCK) ON ReferenceNumber=@ReferenceNumber AND RoomNumber=@RoomNumberID AND ReservationRoomLine.LineNumber=ReservationRoomLineXTab.LineNumber AND DepartureDate>@SystemDate -- only look in future

  OPEN #RoomLineCur2

  FETCH NEXT FROM #RoomLineCur2
  INTO @LineNumber, @Arrival, @Departure

  IF @@FETCH_STATUS = -1
  BEGIN
    CLOSE #RoomLineCur2
    DEALLOCATE #RoomLineCur2
    BREAK -- No records. Need to break to avoid endless loop
  END

  WHILE  (@@FETCH_STATUS <> -1)
  BEGIN
    -- Find merged period
    SELECT @MergedArrival = Min(CONVERT(DATETIME, RL.ArrivalDate, 112)+CONVERT(DATETIME, RL.ArrivalTime, 108)), 
    @MergedDeparture = Max(CONVERT(DATETIME, RL.DepartureDate, 112)+CONVERT(DATETIME, RL.DepartureTime, 108)) FROM ReservationRoomLine RL WITH (NOLOCK) INNER JOIN ReservationRoomLineXTAB RLX WITH (NOLOCK) ON RLX.LineNumber=RL.LineNumber
    AND RLX.ReferenceNumber=@ReferenceNumber AND RL.RoomNumber=@RoomNumberID
    AND CONVERT(DATETIME, RL.ArrivalDate, 112)+CONVERT(DATETIME, RL.ArrivalTime, 108)<=@Departure
    AND CONVERT(DATETIME, RL.DepartureDate, 112)+CONVERT(DATETIME, RL.DepartureTime, 108)>=@Arrival

    -- If merged period is different than our period, then we have to update our line and delete the rest
    IF @Arrival <> @MergedArrival OR @Departure <> @MergedDeparture
    BEGIN
      UPDATE ReservationRoomLine SET ArrivalDate=CONVERT(CHAR(8), @MergedArrival, 112), ArrivalTime=CONVERT(CHAR(5), @MergedArrival, 108), DepartureDate=CONVERT(CHAR(8), @MergedDeparture, 112), DepartureTime=CONVERT(CHAR(5), @MergedDeparture, 108)
      WHERE LineNumber=@LineNumber

       -- delete other rows than our own
      DECLARE #DeleteCur CURSOR LOCAL FAST_FORWARD FOR

        SELECT RL.Linenumber FROM ReservationRoomLine RL WITH (NOLOCK) INNER JOIN ReservationRoomLineXTAB RLX WITH (NOLOCK) ON RLX.LineNumber=RL.LineNumber
        AND RLX.LineNumber<>@LineNumber
        AND RLX.ReferenceNumber=@ReferenceNumber AND RL.RoomNumber=@RoomNumberID
        AND CONVERT(DATETIME, RL.ArrivalDate, 112)+CONVERT(DATETIME, RL.ArrivalTime, 108)<=@Departure
        AND CONVERT(DATETIME, RL.DepartureDate, 112)+CONVERT(DATETIME, RL.DepartureTime, 108)>=@Arrival

      OPEN #DeleteCur

      FETCH NEXT FROM #DeleteCur
      INTO @LineNumber

      WHILE @@FETCH_STATUS <> -1
      BEGIN
        DELETE FROM ReservationRoomLineXTab WHERE LineNumber=@LineNumber
        DELETE FROM ReservationRoomLine WHERE LineNumber=@LineNumber
        DELETE FROM ReservationRoomLineSubTypeXTab WHERE ReservationRoomLineNumber=@LineNumber
        DELETE FROM InUseNoteXTab WHERE ObjectType = 0 AND ObjectNumber = @LineNumber 

        FETCH NEXT FROM #DeleteCur
        INTO @LineNumber
      END

      CLOSE #DeleteCur
      DEALLOCATE #DeleteCUR
    END

    FETCH NEXT FROM #RoomLineCur2
    INTO @LineNumber, @Arrival, @Departure
  END  -- WHILE

  CLOSE #RoomLineCur2
  DEALLOCATE #RoomLineCur2

  SET @ReferenceNumber = @ReferenceNumber - 1

END -- WHILE @ReferenceNumber > -X

