CREATE PROCEDURE dbo.DeleteOldFolios(@DeletedFrom TINYINT) AS
SET NOCOUNT ON
/*
  nb20160901: Speed optimization & cleanup
  KJ 20131017: Added TransactionDeleteNote
  nb20080115: BonusCardSystemTable changed name to FolioCardXTab
  nb20070227 Changed to handle the transactions from inside fixing the lock problem with the long transaction
  NB20070220 Optimization of Xtab deletes
  NB20070218 take care of FolioNumber=-1 values
  NB20060509 Bug Invoices table after run empty - the field name in transactions is InvoiceBillNumber not BillNumber but still that it can delete all content from because of that
  NB20060118 Omfattende test:
   1.Folio list select lavet om til at bruge IsOldReservation function
   2.Look at the transactions date <DeleteEndDate
  NB20051213 Final select from folios secured with a properly where clause, Kitchen delete ext to be done in two steps trough folios and trough transactions, BonusCardSystem cleanup added 
  NB20051101 Created

  Parameters @DeletedFrom is the same as TProgramPart in Types_Constants.pas
  Notes
*/

DECLARE @DeletedCount INT,
@RowCountTotal INT,
@RowCount INT,
@KeepMonth INT,
@FolioNumber INT,
@PrimaryKey INT,
@DeleteMax INT,
@DeleteDateEnd SMALLDATETIME,  -- The date we are deleting before now
@SystemDate DATETIME,
@DeleteDateStored 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!
@FolioNumberFirst INT,
@FolioNumberFirst1 INT,
@FolioNumberLast INT

SET @DeletedCount = 0
SET @RowCountTotal = 0
SET @StartDate = GetDate()

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

SELECT @DeleteMax=KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey='MaximumDeletes'
IF ISNULL (@DeleteMax, 0) = 0
  SET @DeleteMax = 30000

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

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

SET @DeleteDateEnd =DATEADD(MONTH, -@KeepMonth, @SystemDate)
--Find the first and last FolioNumber between the stored delete date and current delete dateransaction dates
SELECT TOP 1 @FolioNumberFirst= FolioNumber FROM Transactions WITH (NOLOCK) 
WHERE FolioNumber <> -1 AND DateStamp < @DeleteDateEnd ORDER BY FolioNumber

SELECT TOP 1 @FolioNumberLast= FolioNumber FROM Transactions WITH (NOLOCK)
WHERE FolioNumber <> -1 AND DateStamp <= @DeleteDateEnd ORDER BY DateStamp DESC
--Find also by the found FolioNumberFirst the first FolioNumber without transactions before the First folio with transactions
SELECT TOP 1 @FolioNumberFirst1= FolioNumber FROM Folios WITH (NOLOCK)
WHERE FolioNumber <> -1 AND FolioNumber < @FolioNumberFirst AND OpenFolio=0 AND ISNULL(dbo.GetFolioBalance(FolioNumber), 0) <> 0 
  AND (ISNULL(ReferenceNumber, 0) <=0 OR dbo.IsOldReservation(ReferenceNumber, @DeleteDateEnd) = 1) 
ORDER BY FolioNumber DESC

IF @FolioNumberFirst1 < @FolioNumberFirst SET @FolioNumberFirst = @FolioNumberFirst1
--PRINT @FolioNumberFirst
--PRINT @FolioNumberLast
-- Delete Cursor
DECLARE #FolioCur CURSOR LOCAL FAST_FORWARD FOR
--Make the Final select for the delete cursor 
SELECT TOP 30000 FolioNumber FROM Folios WITH (NOLOCK) 
WHERE Folios.FolioNumber <> -1 AND (Folios.FolioNumber BETWEEN @FolioNumberFirst AND @FolioNumberLast AND Folios.OpenFolio=0 AND ISNULL(dbo.GetFolioBalance(FolioNumber), 0) = 0 --FolioPart
  AND (ISNULL(ReferenceNumber, -1) <= -1 OR dbo.IsOldReservation(ReferenceNumber, @DeleteDateEnd) = 1)) --ReservationPart
ORDER BY FolioNumber
OPEN #FolioCur
FETCH NEXT FROM #FolioCur
INTO @FolioNumber
--nb20070427 Moved before transaction to ensure the date is set before making any deletions and possible errors occure
IF @DeleteDateEnd  > @DeleteDateStored
BEGIN 
  UPDATE HotelSetup SET KeyValue=CONVERT(CHAR(8), @DeleteDateEnd, 112) WHERE SearchKey LIKE 'FoliosDeletedBefore'
  IF @@ROWCOUNT = 0
    INSERT INTO HotelSetup (SearchKey, KeyValue) VALUES ('FoliosDeletedBefore', CONVERT(CHAR(8), @DeleteDateEnd, 112))
END
WHILE (@@FETCH_STATUS = 0) AND (@DeletedCount < @DeleteMax)
BEGIN
--PRINT '-------'
--PRINT @FolioNumber
--PRINT @DeletedCount
--PRINT @RowCountTotal
  SELECT @RowCount = COUNT(*) FROM Transactions WITH (NOLOCK) 
  WHERE FolioNumber=@FolioNumber AND DateStamp > @DeleteDateEnd 
  IF (@RowCount = 0)
  BEGIN
    SET @DeletedCount=@DeletedCount+1
    BEGIN TRAN
    --nb20080115: BonusCardSystemTable changed name to FolioCardXTab
    DELETE FROM FolioCardXTab WHERE FolioNumber = @FolioNumber 
    --nb20070220 Can be optimized by using cascade deletes
    -- Delete KitchenStatements
    DECLARE #DeleteCur CURSOR LOCAL FAST_FORWARD FOR
    SELECT KitchenStatementNumber FROM TransactionKitchenStatementXTab WITH (NOLOCK)
      INNER JOIN Transactions WITH (NOLOCK) ON Transactions.TransactionNumber = TransactionKitchenStatementXTab.TransactionNumber 
    WHERE Transactions.FolioNumber = @FolioNumber
    OPEN #DeleteCur
    DELETE FROM TransactionKitchenStatementXTab WHERE TransactionNumber IN (
      SELECT TransactionNumber FROM Transactions WITH (NOLOCK)
        WHERE Transactions.FolioNumber = @FolioNumber
    )
    SET @RowCount=@RowCount+@@ROWCOUNT
    FETCH NEXT FROM #DeleteCur
    INTO @PrimaryKey
    WHILE @@FETCH_STATUS <> -1
    BEGIN
      DELETE FROM KitchenStatement WHERE KitchenStatementNumber = @PrimaryKey
      SET @RowCount=@RowCount+@@ROWCOUNT
      FETCH NEXT FROM #DeleteCur
      INTO @PrimaryKey
    END
    CLOSE #DeleteCur
    DEALLOCATE #DeleteCur
    -- Delete Invoices
    DECLARE #DeleteCur CURSOR LOCAL FAST_FORWARD FOR
    SELECT BillNumber FROM FolioBillXTab WITH (NOLOCK) WHERE FolioNumber = @FolioNumber
    OPEN #DeleteCur
    DELETE FROM FolioBillXTab WHERE FolioNumber = @FolioNumber
    SET @RowCount=@RowCount+@@ROWCOUNT
    FETCH NEXT FROM #DeleteCur
    INTO @PrimaryKey
    WHILE @@FETCH_STATUS <> -1
    BEGIN
      DELETE FROM Invoices WHERE BillNumber = @PrimaryKey
      SET @RowCount=@RowCount+@@ROWCOUNT
      FETCH NEXT FROM #DeleteCur
      INTO @PrimaryKey
    END
    CLOSE #DeleteCur
    DEALLOCATE #DeleteCUR

    DELETE FROM TransactionDeleteNote WHERE FK_TransactionNumber IN (SELECT TransactionNumber FROM Transactions WITH (NOLOCK) WHERE FolioNumber = @FolioNumber)
    SET @RowCount=@RowCount+@@ROWCOUNT
    DELETE FROM Invoices WHERE BillNumber IN (SELECT DISTINCT InvoiceBillNumber FROM Transactions WITH (NOLOCK) WHERE FolioNumber = @FolioNumber)
    SET @RowCount=@RowCount+@@ROWCOUNT
    DELETE FROM Transactions WHERE FolioNumber = @FolioNumber
    SET @RowCount=@RowCount+@@ROWCOUNT
    DELETE FROM BillCardInfo WHERE TransactionNumber IN (SELECT TransactionNumber FROM Transactions WITH (NOLOCK) WHERE FolioNumber = @FolioNumber)
    SET @RowCount=@RowCount+@@ROWCOUNT
    DELETE FROM FolioRestResTableChairsXTab WHERE FolioNumber = @FolioNumber
    SET @RowCount=@RowCount+@@ROWCOUNT
    DELETE FROM Folios WHERE FolioNumber = @FolioNumber
    SET @RowCount=@RowCount+@@ROWCOUNT  
    -- Write to Log
    INSERT INTO DeleteLog(Type, ObjectPrimaryKey, DateStamp, DeletedBy, RowsAffected) VALUES (1, @FolioNumber, @SystemDate+ CONVERT(DATETIME, CONVERT(CHAR, GETDATE(), 108), 108), @DeletedFrom, @RowCount)
    SET @RowCountTotal= @RowCountTotal+ @RowCount 
    COMMIT TRAN
  END
  FETCH NEXT FROM #FolioCur
  INTO @FolioNumber
END -- WHILE
CLOSE #FolioCur
DEALLOCATE #FolioCur

--nb20091119 Close inproper open folios. Speed opt: if recordcount less than @DeleteMax-100 (-100 because some of folio transactions are newly created and therefore folio is skipped)
IF @DeletedCount < @DeleteMax-100
BEGIN 
  UPDATE Folios set OpenFolio = 0 WHERE FolioNumber IN (
    --Reservationer
    SELECT FolioNumber FROM Folios WITH (NOLOCK) 
    WHERE OpenFolio=1 AND ISNULL(ReferenceNumber, 0) > 0 AND dbo.IsGroupNOX(Folios.ReferenceNumber) = 1 AND ISNULL(dbo.GetFolioBalance(FolioNumber), 0) = 0 
    UNION ALL
    --Tables (new table booking)
    SELECT F.FolioNumber FROM Folios F WITH (NOLOCK) 
      inner join dbo.FolioRestResTableChairsXTab FRX ON F.FolioNumber = FRX.FolioNumber
      inner join dbo.ReservationConferenceLine RCL ON RCL.ReservationConfLineNumber = FRX.RestaurantReservationNumber AND RCL.TableBooking = 1
    WHERE F.OpenFolio=1 AND ISNULL(RCL.ReferenceNumber, 0) > 0 AND dbo.IsGroupNOX(RCL.ReferenceNumber) = 1 AND ISNULL(dbo.GetFolioBalance(F.FolioNumber), 0) = 0 
    UNION ALL
    --Tables (old and chair>1 table booking)
    SELECT F.FolioNumber FROM Folios F WITH (NOLOCK) 
      inner join dbo.FolioRestResTableChairsXTab FRX ON F.FolioNumber = FRX.FolioNumber
      inner join dbo.RestaurantReservations RR ON RR.RestaurantReservationNumber = FRX.RestaurantReservationNumber 
    WHERE F.OpenFolio=1 AND ISNULL(RR.Status, 0) > 5 AND ISNULL(dbo.GetFolioBalance(F.FolioNumber), 0) = 0 
  )
END
--PRINT @DeleteMAx
--PRINT @DeletedCount
--PRINT @RowCountTotal
--PRINT @DeleteDateStored 
--PRINT @DeleteDateEnd 
SELECT @DeletedCount AS Deleted, @RowCountTotal AS TotalRowsAffected, GETDATE()-@StartDate AS TimeSpent

