--ALTER PROCEDURE DBO.DeleteOldReservations(@DeletedFrom TINYINT) AS
CREATE PROCEDURE DBO.DeleteOldReservations(@DeletedFrom TINYINT) AS
SET NOCOUNT ON
/*
    Created by Jacob Pedersen October 2005

    BA 20131015 BUB: CR065192, fixing 'The conflict occurred in database "xxx", table "dbo.ReservationCardXTab".
    RN 20120111 BUG: CR038580, switched deletion order of ArrangementRoomXtab and ArrangmentLines tables.
    VD 20110222 Bug fix and attunement on deletion of attachments
    VD 20110222 Reconfiguration of cursors and locking code
    JP 20060123 BUG: Saver deleting when looking for last date i 3 tables. Actually resultet in more reservations deleted :)
    JP 20051104 EXT: added MaxDeletes variable for Reservations only; MaximumDeletesReservations
    JP 20051104 OPTIMIZE: tried to make code faster by less deletion through cursors
    JP 20051103 BUG: added time to DateStamp in log
    JP 20051103 OPTIMIZE: Added TOP 30000 to minimize number of selected reservations. Optimal value would be TOP @MaximumDeletes

    Parameters: @DeletedFrom is the same as TProgramPart in Types_Constants.pas

    Notes:

    Todo: when recentlyusedobjects.objectnumber and objectnumber2 has been filled for objecttypenumber for some time, add deletion of these recentlyused also
*/

--EXEC FixReservationDates -1 -- NOTE: virker ikke -1 for alle hoteller!

DECLARE
@ReservationsDeleted INT,
@TotalRowsAffected INT,
@RowsAffected INT,
@KeepMonthReservations INT,
@ReferenceNumber INT,
@PrimaryKey INT,
@MaximumDeletes INT,
@DeleteBeforeDate SMALLDATETIME,  -- The date we are deleting before now
@SystemDate DATETIME,
@ReservationsDeletedBefore SMALLDATETIME, -- The stored date that we before have deleted before. This can never be set to a value lower than it currently is!
@tmpString CHAR(8),
@StartDate DATETIME -- MUST BE DATETIME. CANNOT BE SMALLDATETIME!

SET @ReservationsDeleted = 0
SET @TotalRowsAffected = 0
SET @StartDate = GetDate()

/* Read Hotel settings */
SELECT @KeepMonthReservations=KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey='KeepMonthReservations'
IF IsNull (@KeepMonthReservations, 0) = 0
  SET @KeepMonthReservations = 60

SELECT @MaximumDeletes=KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey='MaximumDeletesReservations'
IF IsNull (@MaximumDeletes, 0) = 0
  SET @MaximumDeletes = 8000

SELECT @tmpString=IsNull(KeyValue,0) FROM HotelSetup WITH (NOLOCK) WHERE SearchKey='ReservationsDeletedBefore' -- SAME CODE IS FOUND IN GENERATEAVAIL SP
IF IsNull(@tmpString, 0) = 0
  SET @ReservationsDeletedBefore = CONVERT(DATETIME, '19800101', 112) -- BEFORE: first picasso installation and before data in first converted installation
ELSE
  SET @ReservationsDeletedBefore = CONVERT(DATETIME, @tmpString, 112)

SELECT @SystemDate=CONVERT(DATETIME, SystemDate, 112) FROM System WITH (NOLOCK) WHERE Number=1

SET @DeleteBeforeDate=DATEADD(MONTH, -@KeepMonthReservations,@SystemDate)

/* Delete Cursor */
DECLARE #ReservationCur CURSOR LOCAL FAST_FORWARD FOR
  SELECT /*TOP 30000*/ ReferenceNumber  -- hotelsetup maximum 30.000 reservations to make SQL faster
  FROM Reservations WITH (NOLOCK) 
  WHERE 
    ReferenceNumber > 0 AND 
    GuestNumber = 0 AND 
    dbo.IsGroupNOX(ReferenceNumber) = 1 AND 
    dbo.GetFolioCount(ReferenceNumber, -1) = 0 AND 
    dbo.GetLastReservationDate(ReferenceNumber, GuestNumber, @DeleteBeforeDate) < @DeleteBeforeDate
  ORDER BY 1
OPEN #ReservationCur

FETCH NEXT FROM #ReservationCur
INTO @ReferenceNumber

WHILE (@@FETCH_STATUS <> -1) AND (@ReservationsDeleted < @MaximumDeletes)
BEGIN
  IF @@FETCH_STATUS <> -2
  BEGIN
    SET @RowsAffected=0
    SET @ReservationsDeleted=@ReservationsDeleted+1

    /* Delete Addresses */ 
    DECLARE #DeleteCur CURSOR STATIC FOR
      SELECT AddressNumber 
      FROM ReservationAddressXTab WITH (NOLOCK) 
      WHERE ReferenceNumber=@ReferenceNumber
    OPEN #DeleteCur

    DELETE FROM ReservationAddressXTab WHERE ReferenceNumber=@ReferenceNumber
    SET @RowsAffected=@RowsAffected+@@rowcount

    FETCH NEXT FROM #DeleteCur
    INTO @PrimaryKey

    WHILE @@FETCH_STATUS <> -1
    BEGIN
      DELETE FROM ReservationAddress WHERE AddressNumber=@PrimaryKey
      SET @RowsAffected=@RowsAffected+@@rowcount

      FETCH NEXT FROM #DeleteCur
      INTO @PrimaryKey
    END

    CLOSE #DeleteCur
    DEALLOCATE #DeleteCUR

    /* Deletion of attachments, part one */  
       
    DELETE FROM ReservationAttachmentXTab 
    WHERE ReferenceNumber = @ReferenceNumber
    SET @RowsAffected = @RowsAffected + @@rowcount
    
    /* Deletion from the Attachment table itself moved to the end of the procedure */  
    
    /* Delete RoomLines */ 
    DECLARE #DeleteCur CURSOR STATIC FOR
      SELECT LineNumber 
      FROM ReservationRoomLineXTab WITH (NOLOCK) 
      WHERE ReferenceNumber=@ReferenceNumber
    OPEN #DeleteCur

    DELETE FROM ReservationRoomLineXTab WHERE ReferenceNumber=@ReferenceNumber
    SET @RowsAffected=@RowsAffected+@@rowcount

    FETCH NEXT FROM #DeleteCur
    INTO @PrimaryKey

    WHILE @@FETCH_STATUS <> -1
    BEGIN
      DELETE FROM ReservationRoomLine WHERE LineNumber=@PrimaryKey
      SET @RowsAffected=@RowsAffected+@@rowcount
      DELETE FROM ReservationRoomLineSubTypeXTab WHERE ReservationRoomLineNumber=@PrimaryKey
      SET @RowsAffected=@RowsAffected+@@rowcount

      FETCH NEXT FROM #DeleteCur
      INTO @PrimaryKey
    END

    CLOSE #DeleteCur
    DEALLOCATE #DeleteCUR

    /* Delete FollowUps */ 
    DECLARE #DeleteCur CURSOR STATIC FOR
      SELECT FollowUpNumber 
      FROM ReservationFollowUpXTab WITH (NOLOCK) 
      WHERE ReferenceNumber=@ReferenceNumber
    OPEN #DeleteCur

    DELETE FROM ReservationFollowUpXTab WHERE ReferenceNumber=@ReferenceNumber
    SET @RowsAffected=@RowsAffected+@@rowcount

    FETCH NEXT FROM #DeleteCur
    INTO @PrimaryKey

    WHILE @@FETCH_STATUS <> -1
    BEGIN
      DELETE FROM PersonalFollowUpXTab WHERE FollowUpNumber=@PrimaryKey
      SET @RowsAffected=@RowsAffected+@@rowcount
      DELETE FROM FollowUp WHERE FollowUpNumber=@PrimaryKey
      SET @RowsAffected=@RowsAffected+@@rowcount

      FETCH NEXT FROM #DeleteCur
      INTO @PrimaryKey
    END

    CLOSE #DeleteCur
    DEALLOCATE #DeleteCUR

    /* Delete Arrangement */ 
    DECLARE #DeleteCur CURSOR STATIC FOR
      SELECT ArrangementLineNumber 
      FROM ArrangementReservationsXtab WITH (NOLOCK) 
      WHERE ReferenceNumber=@ReferenceNumber
    OPEN #DeleteCur

    DELETE FROM ArrangementReservationsXtab WHERE ReferenceNumber=@ReferenceNumber
    SET @RowsAffected=@RowsAffected+@@rowcount

    FETCH NEXT FROM #DeleteCur
    INTO @PrimaryKey

    WHILE @@FETCH_STATUS <> -1
    BEGIN
      DELETE FROM ArrangementRoomXtab WHERE ArrangementLineNumber=@PrimaryKey
      SET @RowsAffected=@RowsAffected+@@rowcount
      DELETE FROM ArrangementLines WHERE ArrangementLineNumber=@PrimaryKey
      SET @RowsAffected=@RowsAffected+@@rowcount

      FETCH NEXT FROM #DeleteCur
      INTO @PrimaryKey
    END

    CLOSE #DeleteCur
    DEALLOCATE #DeleteCUR

    /* Delete from tables which does not have a X-tab */
    -- BA/20131015
    DELETE FROM ReservationCardXTab WHERE ReferenceNumber = @ReferenceNumber
    SET @RowsAffected=@RowsAffected+@@rowcount

    DELETE FROM ReservationMeetingXTab WHERE ReferenceNumber=@ReferenceNumber  -- must be done before ReservationConferenceLine
    SET @RowsAffected=@RowsAffected+@@rowcount
    DELETE FROM ReservationConferenceLine WHERE ReferenceNumber=@ReferenceNumber
    SET @RowsAffected=@RowsAffected+@@rowcount
    DELETE FROM ReservationNotes WHERE ReferenceNumber=@ReferenceNumber
    SET @RowsAffected=@RowsAffected+@@rowcount
    DELETE FROM Reservations WHERE ReferenceNumber=@ReferenceNumber
    SET @RowsAffected=@RowsAffected+@@rowcount
    DELETE FROM ReservationMain WHERE ReferenceNumber=@ReferenceNumber
    SET @RowsAffected=@RowsAffected+@@rowcount

    /* Write to Log */
    INSERT INTO DeleteLog(Type, ObjectPrimaryKey, DateStamp, DeletedBy, RowsAffected) VALUES (0, @ReferenceNumber, @SystemDate + CONVERT(DATETIME, CONVERT(CHAR, GETDATE(), 108), 108), @DeletedFrom, @RowsAffected)

    SET @TotalRowsAffected=@TotalRowsAffected+@RowsAffected
  END -- IF @@FETCH_STATUS <> -2

  FETCH NEXT FROM #ReservationCur
  INTO @ReferenceNumber
END -- WHILE

/* Deletion of attachments, part two */ 

DELETE FROM Attachment
WHERE  
  AttachmentNumber NOT IN (SELECT AttachmentNumber
                           FROM CustomerAttachmentXTab) AND 
  AttachmentNumber NOT IN (SELECT AttachmentNumber
                           FROM ReservationAttachmentXTab) 
                              
/* Deletion from the Attachment table itself moved to here from inside the loop */  

/* Write Delete date if it's greater than the old */
IF @DeleteBeforeDate > @ReservationsDeletedBefore
BEGIN
  UPDATE HotelSetup SET KeyValue=CONVERT(CHAR(8), @DeleteBeforeDate, 112) WHERE SearchKey LIKE 'ReservationsDeletedBefore'
  IF @@rowcount = 0
    INSERT INTO HotelSetup (SearchKey, KeyValue) VALUES ('ReservationsDeletedBefore', CONVERT(CHAR(8), @DeleteBeforeDate, 112))
END

SELECT @ReservationsDeleted AS Deleted, @TotalRowsAffected AS TotalRowsAffected, GetDate()-@StartDate AS TimeSpent

CLOSE #ReservationCur
DEALLOCATE #ReservationCur

