CREATE PROCEDURE DBO.DeleteOldReservations(@DeletedFrom TINYINT, @NoOfDays INT = 0) AS
SET NOCOUNT ON
/*
    Created by Jacob Pedersen October 2005

    AMH 20200608 Don't delete online reservations with folios
    AMH 20200527 Possibility to delete online reservations that have not been completed 
    VD 20170613 Lock issue deleting attachments
    BA 20161222 BUG: CR117536/Adding missing ReservationsExt table.
    RN 20131016 BUG: CR065192, fixing 'The DELETE statement conflicted with the REFERENCE constraint "FK_ArrangementReservationConferenceLineXtab_RESERVATIONCONFERENCELINE".' 
    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,
@PrimaryKey2 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!
@ResConfLineNumber INT

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

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

IF @ReferenceNumber = 0
BEGIN
  /* 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)

  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
END
ELSE
BEGIN
  SET @MaximumDeletes = 8000

  /* Delete Cursor */
  DECLARE #ReservationCur CURSOR LOCAL FAST_FORWARD FOR
    SELECT DISTINCT Reservations.ReferenceNumber
    FROM Reservations WITH (NOLOCK)
    INNER JOIN ReservationMain WITH (NOLOCK) ON ReservationMain.ReferenceNumber = Reservations.ReferenceNumber
    WHERE
      Reservations.Status = 7 AND
      ReservationMain.CreationInProgress = 1 AND
      ReservationMain.SessionTimeStamp > 0 AND
      DATEDIFF(DAY, ReservationMain.SessionTimeStamp, @SystemDate) > @NoOfDays AND 
      dbo.GetFolioCount(Reservations.ReferenceNumber, -1) = 0
END
  
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 ReservationCards */ 
    DECLARE #DeleteCur CURSOR STATIC FOR
      SELECT LognumberCreate, LogNumberDelete 
      FROM ReservationCardXTab WITH (NOLOCK) 
      WHERE ReferenceNumber=@ReferenceNumber
    OPEN #DeleteCur

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

    FETCH NEXT FROM #DeleteCur
    INTO @PrimaryKey, @PrimaryKey2

    WHILE @@FETCH_STATUS <> -1
    BEGIN
      DELETE FROM LogPicasso WHERE LogNumber=@PrimaryKey
      SET @RowsAffected=@RowsAffected+@@rowcount
      DELETE FROM LogPicasso WHERE LogNumber=@PrimaryKey2
      SET @RowsAffected=@RowsAffected+@@rowcount

      FETCH NEXT FROM #DeleteCur
      INTO @PrimaryKey, @PrimaryKey2
    END

    CLOSE #DeleteCur
    DEALLOCATE #DeleteCUR

    /* Delete ReservationCategory */ 
    DELETE FROM ReservationCategoryProductXtab WHERE ReferenceNumber=@ReferenceNumber
    SET @RowsAffected=@RowsAffected+@@rowcount

    /* Delete ReservationChecklist */ 
    DECLARE #DeleteCur CURSOR STATIC FOR
      SELECT FK_CheckItem
      FROM ReservationCheckList WITH (NOLOCK) 
      WHERE ReferenceNumber=@ReferenceNumber
    OPEN #DeleteCur

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

    FETCH NEXT FROM #DeleteCur
    INTO @PrimaryKey

    WHILE @@FETCH_STATUS <> -1
    BEGIN
      DELETE FROM CheckItem WHERE PK_CheckItem=@PrimaryKey
      SET @RowsAffected=@RowsAffected+@@rowcount
      
      FETCH NEXT FROM #DeleteCur
      INTO @PrimaryKey
    END

    CLOSE #DeleteCur
    DEALLOCATE #DeleteCUR

   /* Delete Reservation External Xref */ 
    DECLARE #DeleteCur CURSOR STATIC FOR
      SELECT FK_ExternalRefID
      FROM ReservationExternalRefXtab WITH (NOLOCK) 
      WHERE FK_ReferenceNumber=@ReferenceNumber
    OPEN #DeleteCur

    DELETE FROM ReservationExternalRefXtab WHERE FK_ReferenceNumber=@ReferenceNumber
    SET @RowsAffected=@RowsAffected+@@rowcount

    FETCH NEXT FROM #DeleteCur
    INTO @PrimaryKey

    WHILE @@FETCH_STATUS <> -1
    BEGIN
      WITH ExtRef(ID, ParentID, Value, Qualifier) AS
      (
        SELECT ID, ParentID, VALUE, Qualifier
        FROM ExternalReference WITH (NOLOCK)
        Where ID = @PrimaryKey
        UNION ALL
        Select E.ID, E.ParentID, E.Value, E.Qualifier
        FROM ExternalReference E WITH (NOLOCK)
        INNER JOIN ExtRef ER On E.ParentID = ER.ID    
      )
      DELETE ExternalReference FROM ExternalReference
      INNER JOIN ExtRef ON ExternalReference.ID = ExtRef.ID 
      SET @RowsAffected=@RowsAffected+@@rowcount
      
      FETCH NEXT FROM #DeleteCur
      INTO @PrimaryKey
    END

    CLOSE #DeleteCur
    DEALLOCATE #DeleteCUR
 

    IF (
          SELECT COUNT(*) 
          FROM FolioRestResTableChairsXTab FTCX (NOLOCK) 
          INNER JOIN Transactions TR (NOLOCK) ON TR.FolioNumber = FTCX.FolioNumber
          WHERE FTCX.RestaurantReservationNumber in (SELECT ReservationConfLineNumber FROM RESERVATIONCONFERENCELINE (NOLOCK) WHERE TableBooking=1 AND ReferenceNumber = @ReferenceNumber)
       ) = 0    
    BEGIN
      /* Delete Arrangement */ 
      DECLARE #DeleteCur CURSOR STATIC FOR
        SELECT ArrangementLineNumber 
        FROM ArrangementReservationsXtab WITH (NOLOCK) 
        WHERE ReferenceNumber=@ReferenceNumber
      OPEN #DeleteCur

      FETCH NEXT FROM #DeleteCur
      INTO @PrimaryKey
    
      DELETE FROM ArrangementReservationConferenceLineXtab WHERE ArrangementLineNumber = @PrimaryKey

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

      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

      DECLARE #DeleteCUR CURSOR STATIC FOR
        SELECT ReservationConfLineNumber
        FROM ReservationConferenceLine WITH (NOLOCK)
        WHERE ReferenceNumber = @ReferenceNumber
      OPEN #DeleteCUR
      FETCH NEXT FROM #DeleteCUR INTO @ResConfLineNumber

      WHILE @@FETCH_STATUS <> -1
      BEGIN
        DELETE FROM ArrangementReservationConferenceLineXtab WHERE ReservationConfLineNumber = @ResConfLineNumber
        FETCH NEXT FROM #DeleteCUR INTO @ResConfLineNumber
      END
      CLOSE #DeleteCUR
      DEALLOCATE #DeleteCUR

      /* Delete from tables which does not have a X-tab */
      DELETE FROM ReservationCardXTab WHERE ReferenceNumber = @ReferenceNumber
      SET @RowsAffected=@RowsAffected+@@rowcount
      DELETE FROM GDPRMail 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 ReservationsExt where fk_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
  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 WITH (NOLOCK)) AND 
  AttachmentNumber NOT IN (SELECT AttachmentNumber
                           FROM ReservationAttachmentXTab WITH (NOLOCK)) 
                              
/* Deletion from the Attachment table itself moved to here from inside the loop */  

IF @NoOfDays = 0
BEGIN
  /* 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
END

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

CLOSE #ReservationCur
DEALLOCATE #ReservationCur
