



CREATE PROCEDURE DBO.FixDoubleBookings @StartDate DATETIME, @EndDate DATETIME AS
SET NOCOUNT ON
/*
    Created by: Jacob Pedersen 01-04-2003

    Last update by:

    NOTE: Currently only checks for double bookings within res 0 (out of order) and fixes these

*/
DECLARE @OverLap SMALLINT
DECLARE @UpdatePeriod SMALLINT
DECLARE @FormatedStartDate CHAR (8)
DECLARE @FormatedEndDate CHAR (8)

SET @FormatedStartDate = Convert (Char (8), @StartDate, 112)
SET @FormatedEndDate = Convert (Char (8), @EndDate, 112)

DECLARE @LineNumber INT
DECLARE @RoomNumber INT
DECLARE @ArrivalDate CHAR (8)
DECLARE @ArrivalTime CHAR (5)
DECLARE @DepartureDate CHAR (8)
DECLARE @DepartureTime CHAR (5)
DECLARE @FoundLineNumber INT
DECLARE @FoundArrivalDate CHAR (8)
DECLARE @FoundArrivalTime CHAR (5)
DECLARE @FoundDepartureDate CHAR (8)
DECLARE @FoundDepartureTime CHAR (5)

--- OUT OF ORDER DOUBLE BOOKINGS

DECLARE #RoomLineCur CURSOR FAST_FORWARD FOR
SELECT LineNumber, RoomNumber, ArrivalDate, ArrivalTime, DepartureDate, DepartureTime FROM ReservationRoomLine WITH (NOLOCK) WHERE LineNumber IN (SELECT LineNumber FROM ReservationRoomLineXTab WITH (NOLOCK) WHERE ReferenceNumber=0)
AND Arrivaldate <= @FormatedEndDate AND DepartureDate >= @FormatedStartDate

OPEN #RoomLineCur

FETCH NEXT FROM #RoomLineCur
INTO @LineNumber, @RoomNumber, @ArrivalDate, @ArrivalTime, @DepartureDate, @DepartureTime

WHILE  (@@FETCH_STATUS <> -1)
BEGIN
  SELECT @LineNumber=LineNumber FROM ReservationRoomLine WITH (NOLOCK) WHERE LineNumber=@LineNumber
  IF @@rowcount > 0
  BEGIN
    SET @UpdatePeriod = 0

    DECLARE #FoundCursor CURSOR FAST_FORWARD FOR

    SELECT LineNumber, ArrivalDate, ArrivalTime, DepartureDate, DepartureTime FROM ReservationRoomLine WITH (NOLOCK)
    WHERE LineNumber <> @LineNumber AND RoomNumber = @RoomNumber AND LineNumber IN (SELECT LineNumber FROM ReservationRoomLineXTab WITH (NOLOCK) WHERE ReferenceNumber=0)
    AND Arrivaldate <= @DepartureDate AND DepartureDate >= @ArrivalDate

    OPEN #FoundCursor

    FETCH NEXT FROM #FoundCursor
    INTO @FoundLineNumber, @FoundArrivalDate, @FoundArrivalTime, @FoundDepartureDate, @FoundDepartureTime

    WHILE  (@@FETCH_STATUS <> -1)
    BEGIN
if @roomnumber=79
begin
print 'checking:'+convert (char, @roomnumber)
print @arrivaldate
print @arrivaltime
print @departuredate
print @departuretime

print @foundarrivaldate
print @foundarrivaltime
print @founddeparturedate
print @founddeparturetime
end
      SET @OverLap = 0

      --check om arrivaldate+time er inden for periode
      IF @FoundArrivalDate < @DepartureDate AND @FoundArrivalDate > @ArrivalDate  -- arrivaldate within period
        SET @OverLap = 1
      ELSE IF @FoundDepartureDate < @DepartureDate AND @FoundDepartureDate > @ArrivalDate -- departuredate within periode
        SET @OverLap = 2
      ELSE IF @FoundArrivalDate = @DepartureDate  AND @FoundArrivalTime <= @DepartureTime  -- arrivaldate = departuredate -> check if time overlaps
        SET @OverLap = 3
      ELSE IF @FoundDepartureDate = @ArrivalDate  AND @FoundDepartureTime >= @ArrivalTime  -- departuredate = arrivaldate -> check if time overlaps
        SET @OverLap = 4
      ELSE IF @FoundArrivalDate = @ArrivalDate  AND @FoundArrivalTime <= @ArrivalTime AND ((@FoundDepartureTime >= @ArrivalTime AND @FoundDepartureDate=@ArrivalDate) OR (@FoundDepartureDate > @ArrivalDate))  -- arrivaldate = departuredate -> check if time overlaps
        SET @OverLap = 5
      ELSE IF @FoundDepartureDate = @DepartureDate  AND @FoundDepartureTime >= @DepartureTime AND ((@FoundArrivalTime <= @DepartureTime AND @FoundArrivalDate=@DepartureDate) OR (@FoundArrivalDate < @DepartureDate))  -- arrivaldate = departuredate -> check if time overlaps
        SET @OverLap = 6

    --check om departuredate+time er inden for periode

    -- hvis periode overlapper så opdater periode
    IF @OverLap > 0 --and @roomnumber=79
    BEGIN
--    print 'FOUND OVERLAP! room:'+convert (varchar, @roomnumber)
--    print @arrivaldate
--    print @departuredate
      IF @FoundArrivalDate < @ArrivalDate
      BEGIN
        SET @ArrivalDate = @FoundArrivalDate
        SET @ArrivalTime = @FoundArrivalTime
      END
      ELSE IF @FoundArrivalDate = @ArrivalDate AND @FoundArrivalTime < @ArrivalTime
        SET @ArrivalTime = @FoundArrivalTime
      ELSE IF @FoundDepartureDate > @DepartureDate
      BEGIN
        SET @DepartureDate = @FoundDepartureDate
        SET @DepartureTime = @FoundDepartureTime
      END
      ELSE IF @FoundDepartureDate = @DepartureDate AND @FoundDepartureTime > @DepartureTime
        SET @DepartureTime = @FoundDepartureTime
--      print 'delete line:'+convert(varchar, @FoundLineNumber)
      DELETE FROM ReservationRoomLine WHERE LineNumber=@FoundLineNumber
      DELETE FROM ReservationRoomLineXTab WHERE LineNumber=@FoundLineNumber
      print ''
      SET @UpdatePeriod = 1
    END

    FETCH NEXT FROM #FoundCursor
    INTO @FoundLineNumber, @FoundArrivalDate, @FoundArrivalTime, @FoundDepartureDate, @FoundDepartureTime
  END -- END INNER WHILE
  IF @UpdatePeriod > 0
  BEGIN
     SELECT @FoundArrivalDate=ArrivalDate, @FoundArrivalTime=ArrivalTime, @FoundDepartureDate=DepartureDate, @FoundDepartureTime=DepartureTime FROM ReservationRoomLine WITH (NOLOCK)
     WHERE LineNumber=@LineNumber
print 'oldvalues'
print @foundarrivaldate
print @foundarrivaltime
print @founddeparturedate
print @founddeparturetime

print 'newvalues'
print @arrivaldate
print @arrivaltime
print @departuredate
print @departuretime
     UPDATE ReservationRoomLine SET ArrivalDate=@ArrivalDate, ArrivalTime=@ArrivalTime, DepartureDate=@DepartureDate, DepartureTime=@DepartureTime WHERE LineNumber=@LineNumber
     AND ArrivalDate<>@ArrivalDate OR ArrivalTime<>@ArrivalTime OR DepartureDate<>@DepartureDate OR DepartureTime<>@DepartureTime 
  END

  CLOSE #FoundCursor
  DEALLOCATE #FoundCursor

  END

  FETCH NEXT FROM #RoomLineCur
  INTO @LineNumber, @RoomNumber, @ArrivalDate, @ArrivalTime, @DepartureDate, @DepartureTime
END -- END OUTER WILE

CLOSE #RoomLineCur
DEALLOCATE #RoomLineCur

