CREATE PROCEDURE dbo.ReservationAddressAnonymize @AHotelCustomerNumber INT, @AMainHotel BIT, @AAll BIT = 0 AS
/*
  Created by Kent Johansen, April 2017
  Changes:
    KJ 20240603: Bug: Fix for "Query does not complete". Case #CR244734
    KJ 20240308: Bug: Fix for "Some reservations not anonymized". Case #CR241011. Nenad provided the revised sql.
    KJ 20221101: Opt: Optimized
    KJ 20220502: Bug: Fix for "Reservation is anonymized enven if active GDPR Mail". Case #CR217852
    KJ 20220304: Bug: Fix for "String or binary data would be truncated". Case #CR215795
    KJ 20211125: Ext: Anonymize folio and invoice note. Req #4001
    KJ 20210915: Added: Anonymize invoices
    KJ 20210906: Ext: Added passport number.
    KJ 20210611: Ext: Added staff anonymize
    KJ 20210511: Bug: Fix for "Not all transactions with name info is anonymized. Case #CR209060"
    KJ 20210419: Bug: Fix for "Not all transactions with name info is anonymized. Case #CR202302"
    KJ 20200414: Anonymize hall name list. 
    KJ 20200206: Anonymize CC data changed from month to days
    KJ 20200109: Default values for anonymize transactions and cc data change
    KJ 20191209: Ext: Anonymize Transaction and CC info
    KJ 20191205: Ext: removed use of hidden
    KJ 20190506: Bug: Fix for "Violation of PRIMARY KEY constraint 'PK_RESERVATIONADDRESSHIDDEN'. Cannot insert duplicate key in object 'dbo.ReservationAddressHidden'"
    KJ 20190417: Added AltResNo
    KJ 20181112: AllContactsLinkID (Mødeleder)
    KJ 20180920: Bug: Fix for "Violation of PRIMARY KEY constraint 'PK_ReservationNotesHidden'. Cannot insert duplicate key in object 'dbo.ReservationNotesHidden'.". Case #CR152817
    KC 20180910: Ext: Cleaning up invalid ContactPersonCustomerNumber in ReservationAddress
    KJ 20180528: Dynamically set number of records to anonymize.
    KJ 20180528: Bug: Fix for "String or binary data would be truncated."
    KJ 20180419: Ext: Added hidden tables for attachment and notes
    KJ 20180418: Bug: Fix for "not anonymized if activ GDPR mail and forece anonymize".
    KJ 20180201: Added Clear reservation notes
    KJ 20180109: Bug: Fix for "Addresses marked for anonymized not anonymized if many older reservations should be anonymized."
    KJ 20180108: Anonymize if manually marked for anonymize
    Altered by Villy Damsbo, November 2017. GDPRMailDate moved from table CUSTOMER to new table GDPRMAIL
*/
SET NOCOUNT ON

CREATE TABLE #ResTable
(
  AddressNumber INT,
  ReferenceNumber INT,
  GuestNumber INT
)

IF CAST(ISNULL(dbo.GetHotelSetupKeyValue('AnonymizeReservationDataEnabled', @AHotelCustomerNumber), 0) AS BIT) = 1 OR @AAll = 1
BEGIN
  DECLARE @Month INT
  DECLARE @Days INT
  DECLARE @dt DATETIME
  DECLARE @dtContact DATETIME
  DECLARE @dtInvoice DATETIME
  DECLARE @dtCleanup DATETIME
  DECLARE @SystemDateStr VARCHAR(100)
  DECLARE @SystemDate DATETIME
  DECLARE @ReplaceText VARCHAR(10)
  DECLARE @GDPRMailDays INT
  DECLARE @dtGDPRMail DATE

  DECLARE @ReferenceNumber INT
  DECLARE @GuestNumber INT
  DECLARE @FollowupText VARCHAR(8000)
  DECLARE @UserIDNumber INT

  DECLARE @NotePageCSV VARCHAR(10)

  DECLARE @NoRecords INT

  IF @AAll = 1
    SET @NotePageCSV = '-1,1,2,3'
  ELSE
  BEGIN
    SET @NotePageCSV = '-1'
    IF CAST(ISNULL(dbo.GetHotelSetupKeyValue('AnonymizeReservationClearNote1', @AHotelCustomerNumber), 0) AS BIT) = 1
      SET @NotePageCSV = @NotePageCSV + ',1'
    IF CAST(ISNULL(dbo.GetHotelSetupKeyValue('AnonymizeReservationClearNote2', @AHotelCustomerNumber), 0) AS BIT) = 1
      SET @NotePageCSV = @NotePageCSV + ',2'
    IF CAST(ISNULL(dbo.GetHotelSetupKeyValue('AnonymizeReservationClearNote3', @AHotelCustomerNumber), 0) AS BIT) = 1
      SET @NotePageCSV = @NotePageCSV + ',3'
  END

  IF CAST(ISNULL(dbo.GetHotelSetupKeyValue('AnonymizeReservationDataReplaceWithStar', @AHotelCustomerNumber), 0) AS BIT) = 1
    SET @ReplaceText = '******'
  ELSE
    SET @ReplaceText = ''

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

  SET @Month = dbo.GetHotelSetupKeyValue('AnonymizeReservationDataMonth', @AHotelCustomerNumber)
  SET @Month = ISNULL(@Month, 6)
  SET @dt = DATEADD(MONTH, -@Month, @SystemDate)
  SET @dtContact = @dt
  SET @dtInvoice = @dt

  SET @GDPRMailDays = dbo.GetHotelSetupKeyValue('GDPRMailDays', @AHotelCustomerNumber)
  SET @GDPRMailDays = ISNULL(@GDPRMailDays, 21)*-1

  UPDATE ReservationAddress
  SET ContactPersonCustomerNumber = NULL
  FROM ReservationAddress WITH (NOLOCK)
  LEFT OUTER JOIN Customer WITH (NOLOCK) ON Customer.CustomerNumber = ReservationAddress.ContactPersonCustomerNumber
  WHERE ReservationAddress.ContactPersonCustomerNumber IS NOT NULL 
  AND Customer.CustomerNumber IS NULL

  CREATE TABLE #ResTableDistinct
  (
    ReferenceNumber INT,
    GuestNumber INT
  )

  SELECT @NoRecords = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'GDPRNumberOfResAddress'
  SET @NoRecords = ISNULL(@NoRecords, 10000)

  DECLARE @SQL NVARCHAR(MAX)

  IF @AAll = 1
  BEGIN
    INSERT INTO #ResTable
    SELECT ra.AddressNumber, Reservations.ReferenceNumber, Reservations.GuestNumber
    FROM ReservationAddress RA WITH (NOLOCK)
    INNER JOIN ReservationAddressXTab WITH (NOLOCK) ON ReservationAddressXTab.AddressNumber = RA.AddressNumber
    INNER JOIN Reservations WITH (NOLOCK) ON Reservations.ReferenceNumber = ReservationAddressXTab.ReferenceNumber
      AND Reservations.GuestNumber = ReservationAddressXTab.GuestNumber
    INNER JOIN ReservationMain WITH (NOLOCK) ON ReservationMain.ReferenceNumber = Reservations.ReferenceNumber
      AND FkCustomerHotel = @AHotelCustomerNumber
    WHERE RA.AddressNumber > 0
    AND RA.AddressType = 1 
    AND ISNULL(RA.AddressAnonymized, 0) = 0
  END
  ELSE
  BEGIN
    SET @SQL = '
      SELECT Folios.ReservationAddressNumber
      INTO #Tran
      FROM dbo.Transactions T WITH (NOLOCK)
        JOIN dbo.Folios WITH (NOLOCK) ON Folios.FolioNumber = T.FolioNumber
      WHERE T.MovedDeleted = 0 AND T.ShowOnBillFlag = 1
      EXCEPT --Settled when Prebill
      SELECT Folios.ReservationAddressNumber
      FROM dbo.Transactions T WITH (NOLOCK)
        JOIN Folios WITH (NOLOCK) ON Folios.FolioNumber = T.FolioNumber
        JOIN dbo.InvoiceTransactionXtab ITX WITH(NOLOCK) ON ITX.FkTransaction = T.TransactionNumber
        JOIN dbo.Invoice I WITH(NOLOCK) ON ITX.FkInvoice = I.PkInvoice AND I.ReceiptType <> 0 AND NOT I.PkInvoice IS NULL AND I.CancelDate IS NULL
      WHERE NOT I.PkInvoice IS NULL AND I.CancelDate IS NULL OR I.ReceiptType = 0

    SELECT TOP ' + CAST(@NoRecords AS VARCHAR) + ' ra.AddressNumber, Reservations.ReferenceNumber, Reservations.GuestNumber
    FROM ReservationAddress RA WITH (NOLOCK)
    INNER JOIN ReservationAddressXTab WITH (NOLOCK) ON ReservationAddressXTab.AddressNumber = RA.AddressNumber
    INNER JOIN Reservations WITH (NOLOCK) ON Reservations.ReferenceNumber = ReservationAddressXTab.ReferenceNumber
      AND Reservations.GuestNumber = ReservationAddressXTab.GuestNumber
      AND (RA.DoAnonymize = 1 OR (CASE WHEN Reservations.DepartureDate IS NULL THEN 
        (
          SELECT DepartureDate
          FROM ReservationConferenceLine WITH (NOLOCK)
          INNER JOIN ReservationMeetingXTab WITH (NOLOCK) ON ReservationMeetingXTab.ReservationConfLineNumber = ReservationConferenceLine.ReservationConfLineNumber
            AND ReservationMeetingXTab.ReferenceNumber = Reservations.ReferenceNumber
            AND ReservationMeetingXTab.GuestNumber = Reservations.GuestNumber
        ) 
        ELSE 
          Reservations.DepartureDate 
        END <= ' + CONVERT(CHAR(8), @dt, 112) + '))
    INNER JOIN ReservationMain WITH (NOLOCK) ON ReservationMain.ReferenceNumber = Reservations.ReferenceNumber
      AND FkCustomerHotel = ' + CAST(@AHotelCustomerNumber AS VARCHAR) + '
    WHERE RA.AddressNumber > 0
    AND RA.AddressType = 1 
    AND ISNULL(RA.AddressAnonymized, 0) = 0
    AND (RA.Link IS NULL OR RA.LINK NOT IN 
    (
      SELECT FK_CustomerNumber
      FROM GDPRApproveCustomerXTab WITH (NOLOCK)
      INNER JOIN GDPRApprove WITH (NOLOCK) ON GDPRApprove.Id = GDPRApproveCustomerXTab.FK_GDPRApproveId
        AND GDPRApprove.GDPRApprovalType IN (1,2)
      WHERE ISNULL(GDPRApproveCustomerXTab.DateApprove, 0) >= ISNULL(GDPRApproveCustomerXTab.DateReject, 0)

      UNION

      SELECT DISTINCT CustomerNumber
      FROM GDPRMAIL WITH (NOLOCK)
      WHERE (ISNULL(RA.DoAnonymize, 0) = 0) AND GDPRMailDate >= CAST(DATEADD(DAY, ' + CAST(@GDPRMailDays AS VARCHAR) + ', GETDATE()) AS DATE)
    ))
    AND RA.AddressNumber NOT IN (SELECT ReservationAddressNumber FROM #Tran)
    ORDER BY RA.DoAnonymize DESC, Reservations.DepartureDate ASC
    
    DROP TABLE #Tran'

    INSERT INTO #ResTable
      EXEC sp_executeSQL @SQL
  END

  INSERT INTO #ResTableDistinct
  SELECT DISTINCT ReferenceNumber, GuestNumber
  FROM #ResTable WITH (NOLOCK)

  EXEC ReservationAddressAnonymize_Do @AHotelCustomerNumber, @ReplaceText

  UPDATE Reservations
  SET
    AltResNo = SUBSTRING(SUBSTRING(AltResNo, 1, (CAST(ISNULL(dbo.GetHotelSetupKeyValue('AnonymizeReservationDataAltResNo', @AHotelCustomerNumber), 30) AS INT))) + @ReplaceText, 1, 30),
    PassPortNumber = SUBSTRING(SUBSTRING(PassPortNumber, 1, (CAST(ISNULL(dbo.GetHotelSetupKeyValue('AnonymizeReservationDataPassPortNo', @AHotelCustomerNumber), 0) AS INT))) + @ReplaceText, 1, 25)
  FROM Reservations WITH (NOLOCK)
  WHERE Reservations.ReferenceNumber IN (SELECT ReferenceNumber FROM #ResTableDistinct)

  DELETE ResAXtab FROM ReservationAttachmentXTab ResAXtab
  INNER JOIN #ResTableDistinct ON #ResTableDistinct.ReferenceNumber = ResAXtab.ReferenceNumber
    AND #ResTableDistinct.GuestNumber = ResAXtab.GuestNumber

  DELETE Attachment WHERE AttachmentNumber NOT IN (SELECT AttachmentNumber FROM ReservationAttachmentXTab WITH (NOLOCK) UNION SELECT AttachmentNumber FROM CustomerAttachmentXTab WITH (NOLOCK))

  IF @NotePageCSV <> '-1'
  BEGIN
    DELETE ResNotes FROM ReservationNotes ResNotes
    INNER JOIN #ResTableDistinct ON #ResTableDistinct.ReferenceNumber = ResNotes.ReferenceNumber
      AND #ResTableDistinct.GuestNumber = ResNotes.GuestNumber
    WHERE NotePage IN (SELECT Number FROM  dbo.iter_intlist_to_table_No_Dummy(@NotePageCSV))
  END

  DECLARE @NewFollowUpNumber INT
  
  EXEC SystemDateTime '', @dt OUTPUT

  SELECT TOP 1 @UserIDNumber = UserIdNumber FROM Personel WITH (NOLOCK) WHERE UserTypeNumber = 2

  DECLARE #ResCur CURSOR FAST_FORWARD READ_ONLY FOR
    SELECT ReferenceNumber, GuestNumber FROM #ResTableDistinct
  OPEN #ResCur

  FETCH NEXT FROM #ResCur INTO @ReferenceNumber, @GuestNumber

  WHILE @@FETCH_STATUS = 0
  BEGIN
    SET @FollowupText = 'Guest ' + CAST(@GuestNumber AS VARCHAR) + ' anonymized'

    INSERT INTO FollowUp ([Date], Type, Priority, State, Note, CreatedDate, CreatedBy, SubType)
    VALUES (@dt, 5, 12, 3, @FollowupText, @dt, @UserIDNumber, 2)
    SELECT @NewFollowUpNumber = @@identity
    INSERT INTO ReservationFollowUpXTab (ReferenceNumber, GuestNumber, FollowUpNumber) VALUES (@ReferenceNumber, @GuestNumber, @NewFollowUpNumber)    

    FETCH NEXT FROM #ResCur INTO @ReferenceNumber, @GuestNumber
  END

  CLOSE #ResCur
  DEALLOCATE #ResCur

  DROP TABLE #ResTableDistinct
  DELETE #ResTable

  -- Contact person
  CREATE TABLE #ResTable1
  (
    AddressNumber INT,
    ReferenceNumber INT,
    GuestNumber INT
  )

  IF @AAll = 1
  BEGIN
    INSERT INTO #ResTable1
    SELECT ra.AddressNumber, Reservations.ReferenceNumber, Reservations.GuestNumber
    FROM ReservationAddress RA WITH (NOLOCK)
    INNER JOIN ReservationAddressXTab WITH (NOLOCK) ON ReservationAddressXTab.AddressNumber = RA.AddressNumber
    INNER JOIN Reservations WITH (NOLOCK) ON Reservations.ReferenceNumber = ReservationAddressXTab.ReferenceNumber
      AND Reservations.GuestNumber = ReservationAddressXTab.GuestNumber
    INNER JOIN ReservationMain WITH (NOLOCK) ON ReservationMain.ReferenceNumber = Reservations.ReferenceNumber
      AND FkCustomerHotel = @AHotelCustomerNumber
    WHERE RA.AddressNumber > 0
    AND RA.AddressType > 1 
    AND ISNULL(RA.AddressAnonymized, 0) = 0
    AND RA.ContactpersonCustomerNumber IS NULL   
  END
  ELSE
  BEGIN
    SET @dtGDPRMail = CAST(DATEADD(DAY, @GDPRMailDays, GETDATE()) AS DATE)

    INSERT INTO #ResTable1
    SELECT TOP 10000 ra.AddressNumber, Reservations.ReferenceNumber, Reservations.GuestNumber
    FROM ReservationAddress RA WITH (NOLOCK)
    INNER JOIN ReservationAddressXTab WITH (NOLOCK) ON ReservationAddressXTab.AddressNumber = RA.AddressNumber
    INNER JOIN Reservations WITH (NOLOCK) ON Reservations.ReferenceNumber = ReservationAddressXTab.ReferenceNumber
      AND Reservations.GuestNumber = ReservationAddressXTab.GuestNumber
      AND (Reservations.DepartureDate <= @dtContact OR RA.DoAnonymize = 1)
    INNER JOIN ReservationMain WITH (NOLOCK) ON ReservationMain.ReferenceNumber = Reservations.ReferenceNumber
      AND FkCustomerHotel = @AHotelCustomerNumber
    WHERE RA.AddressNumber > 0
    AND RA.AddressType > 1 
    AND ISNULL(RA.AddressAnonymized, 0) = 0
    AND 
    (
      (RA.ContactpersonCustomerNumber IS NULL AND RA.AllContactsLinkID IS NULL)
    
      OR RA.ContactpersonCustomerNumber NOT IN 
      (
        SELECT FK_CustomerNumber
        FROM GDPRApproveCustomerXTab WITH (NOLOCK)
        INNER JOIN GDPRApprove WITH (NOLOCK) ON GDPRApprove.Id = GDPRApproveCustomerXTab.FK_GDPRApproveId
          AND GDPRApprove.GDPRApprovalType IN (1,2)
        WHERE ISNULL(GDPRApproveCustomerXTab.DateApprove, 0) >= ISNULL(GDPRApproveCustomerXTab.DateReject, 0)

        UNION

        SELECT DISTINCT CustomerNumber
        FROM GDPRMAIL WITH (NOLOCK)
        WHERE GDPRMailDate >= @dtGDPRMail AND GDPRMailType = 1
      )
      OR RA.AllContactsLinkID NOT IN 
      (
        SELECT FK_CustomerNumber
        FROM GDPRApproveCustomerXTab WITH (NOLOCK)
        INNER JOIN GDPRApprove WITH (NOLOCK) ON GDPRApprove.Id = GDPRApproveCustomerXTab.FK_GDPRApproveId
          AND GDPRApprove.GDPRApprovalType IN (1,2)
        WHERE ISNULL(GDPRApproveCustomerXTab.DateApprove, 0) >= ISNULL(GDPRApproveCustomerXTab.DateReject, 0)

        UNION

        SELECT DISTINCT CustomerNumber
        FROM GDPRMAIL WITH (NOLOCK)
        WHERE GDPRMailDate >= @dtGDPRMail AND GDPRMailType = 1
      )
    )
    ORDER BY RA.DoAnonymize DESC, Reservations.DepartureDate ASC
  END

  UPDATE ReservationAddress
  SET
    AttentionName = SUBSTRING(SUBSTRING(AttentionName, 1, (CAST(ISNULL(dbo.GetHotelSetupKeyValue('AnonymizeReservationDataAttentionName', @AHotelCustomerNumber), 0) AS INT))) + @ReplaceText, 1, 50),
    ContactpersonCustomerNumber = CASE WHEN (CAST(ISNULL(dbo.GetHotelSetupKeyValue('AnonymizeReservationDataClearCustAttention', @AHotelCustomerNumber), 1) AS BIT)) = 1 THEN NULL ELSE ContactpersonCustomerNumber END,
    AllContactsLinkID = CASE WHEN (CAST(ISNULL(dbo.GetHotelSetupKeyValue('AnonymizeReservationDataClearCustAttention', @AHotelCustomerNumber), 1) AS BIT)) = 1 THEN NULL ELSE AllContactsLinkID END,
    AddressAnonymized = 1,
    AnonymizeDate = GETDATE()
  FROM ReservationAddress WITH (NOLOCK)
  WHERE ReservationAddress.AddressNumber IN (SELECT AddressNumber FROM #ResTable1)

  SET @dt = GETDATE()

  SELECT TOP 1 @UserIDNumber = UserIdNumber FROM Personel WITH (NOLOCK) WHERE UserTypeNumber = 2

  DECLARE #ResCur CURSOR FAST_FORWARD READ_ONLY FOR
    SELECT ReferenceNumber, GuestNumber FROM #ResTable1
  OPEN #ResCur

  FETCH NEXT FROM #ResCur INTO @ReferenceNumber, @GuestNumber

  WHILE @@FETCH_STATUS = 0
  BEGIN
    SET @FollowupText = 'Contact info anonymized'

    INSERT INTO FollowUp ([Date], Type, Priority, State, Note, CreatedDate, CreatedBy, SubType)
    VALUES (@dt, 5, 12, 3, @FollowupText, @dt, @UserIDNumber, 2)
    SELECT @NewFollowUpNumber = @@identity
    INSERT INTO ReservationFollowUpXTab (ReferenceNumber, GuestNumber, FollowUpNumber) VALUES (@ReferenceNumber, @GuestNumber, @NewFollowUpNumber)    

    FETCH NEXT FROM #ResCur INTO @ReferenceNumber, @GuestNumber
  END

  CLOSE #ResCur
  DEALLOCATE #ResCur

  DROP TABLE #ResTable1
END

-- Anonymize Transactions
IF CAST(ISNULL(dbo.GetHotelSetupKeyValue('AnonymizeTransactionDataEnabled', @AHotelCustomerNumber), 1) AS BIT) = 1 OR @AAll = 1
BEGIN
  SET @Month = dbo.GetHotelSetupKeyValue('AnonymizeTransactionDataMonth', @AHotelCustomerNumber)
  SET @Month = ISNULL(@Month, 60)
  SET @dt = DATEADD(MONTH, -@Month, @SystemDate)

  IF CAST(ISNULL(dbo.GetHotelSetupKeyValue('AnonymizeTransactionDataReplaceWithStar', @AHotelCustomerNumber), 1) AS BIT) = 1
    SET @ReplaceText = '******'
  ELSE
    SET @ReplaceText = ''

  SELECT GlobalTransactionNumber
  INTO #gtn
  FROM Transactions WITH (NOLOCK)
  INNER JOIN Accounts WITH (NOLOCK) ON Transactions.accountnumber = Accounts.AccountNumber
    AND Accounts.AccountType = 3
  INNER JOIN Ware WITH (NOLOCK) ON Transactions.Warenumber = Ware.WareNumber
  INNER JOIN WareGroups WITH (NOLOCK) ON Ware.WareGroupNumber = WareGroups.WareGroupNumber
    AND WareGroups.Payment IN (2,3,7,8)  -- System room/hall + manuel room/Hall
  WHERE (@AAll = 1 OR DateStamp < @dt)
  AND Transactions.HotelCustomerNumber = @AHotelCustomerNumber

  UPDATE Transactions
  SET 
    TextOverwrite = SUBSTRING(SUBSTRING(TextOverwrite, 1, (CAST(ISNULL(dbo.GetHotelSetupKeyValue('AnonymizeTransactionDataTextOverwrite', @AHotelCustomerNumber), 18) AS INT))) + @ReplaceText, 1, 150),
    AnonymizeDate = GETDATE()
  FROM Transactions
  INNER JOIN #gtn ON #gtn.GlobalTransactionNumber = Transactions.GlobalTransactionNumber
  AND AnonymizeDate IS NULL

  DROP TABLE #gtn

  IF @AAll = 1
  BEGIN
    UPDATE Invoice
    SET 
      Note = '',
      AnonymizeDate = GETDATE()
    WHERE ISNULL(Note, '') <> ''
    AND AnonymizeDate IS NULL
    AND FkCustomerHotel = @AHotelCustomerNumber

    UPDATE Folios
    SET 
      Note = '',
      AnonymizeDate = GETDATE()
    WHERE ISNULL(Note, '') <> '' 
    AND AnonymizeDate IS NULL
    AND HotelCustomerNumber = @AHotelCustomerNumber
  END
  ELSE
  BEGIN
    IF CAST(ISNULL(dbo.GetHotelSetupKeyValue('AnonymizeTransactionClearInvoiceNote', @AHotelCustomerNumber), 0) AS BIT) = 1
    BEGIN
      UPDATE Invoice
      SET 
        Note = '',
        AnonymizeDate = GETDATE()
      WHERE CreatedDate < @dt
      AND ISNULL(Note, '') <> ''
      AND AnonymizeDate IS NULL
      AND FkCustomerHotel = @AHotelCustomerNumber
    END
 
    IF CAST(ISNULL(dbo.GetHotelSetupKeyValue('AnonymizeTransactionClearFolioNote', @AHotelCustomerNumber), 0) AS BIT) = 1
    BEGIN
      UPDATE Folios
      SET 
        Note = '',
        AnonymizeDate = GETDATE()
      WHERE ISNULL(Note, '') <> '' 
      AND AnonymizeDate IS NULL
      AND HotelCustomerNumber = @AHotelCustomerNumber
      AND FolioNumber IN (
        SELECT DISTINCT FolioNumber
        FROM Transactions WITH (NOLOCK)
        GROUP BY FolioNumber
        HAVING MAX(DateStamp) < @dt   
      )
    END
  END
END

-- Anonymize CC info
IF CAST(ISNULL(dbo.GetHotelSetupKeyValue('AnonymizeCardDataEnabled', @AHotelCustomerNumber), 1) AS BIT) = 1 OR @AAll = 1
BEGIN
  SET @Days = dbo.GetHotelSetupKeyValue('AnonymizeCardDataDays', @AHotelCustomerNumber)
  SET @Days = ISNULL(@Days, 180)
  SET @dt = DATEADD(DAY, -@Days, @SystemDate)

  SELECT DISTINCT Reservations.ReferenceNumber, Reservations.GuestNumber
  INTO #ResTableDistinctCard
  FROM Reservations WITH (NOLOCK)
  INNER JOIN ReservationMain WITH (NOLOCK) ON ReservationMain.FkCustomerHotel = @AHotelCustomerNumber
  WHERE Reservations.DepartureDate <= CONVERT(CHAR(8), @dt, 112)
  AND CardDataAnonymizeDate IS NULL

  UPDATE Reservations
  SET 
    CreditCardNumber = NULL,
    ExpireDate = NULL,
    ControlCode = NULL,
    CardDataAnonymizeDate = GETDATE()
  FROM Reservations
  INNER JOIN #ResTableDistinctCard ON #ResTableDistinctCard.ReferenceNumber = Reservations.ReferenceNumber
    AND #ResTableDistinctCard.GuestNumber = Reservations.GuestNumber

  DROP TABLE #ResTableDistinctCard
END

-- Anonymize Invoices
IF CAST(ISNULL(dbo.GetHotelSetupKeyValue('AnonymizeReservationDataEnabled', @AHotelCustomerNumber), 0) AS BIT) = 1 OR @AAll = 1
BEGIN
  IF @AAll = 1
  BEGIN
    INSERT INTO #ResTable
    SELECT Folios.ReservationAddressNumber, -1, -1
    FROM Folios WITH (NOLOCK)
    INNER JOIN ReservationAddress WITH (NOLOCK) ON ReservationAddress.AddressNumber = Folios.ReservationAddressNumber
      AND ReservationAddress.AddressType = 1
    WHERE ISNULL(Folios.ReferenceNumber, 0) = 0 
    AND ISNULL(Folios.ReservationAddressNumber, 0) > 0
    AND Folios.HotelCustomerNumber = @AHotelCustomerNumber
    AND ISNULL(ReservationAddress.AddressAnonymized, 0) = 0
  END
  ELSE
  BEGIN
    INSERT INTO #ResTable
    SELECT Folios.ReservationAddressNumber, -1, -1
    FROM Folios WITH (NOLOCK)
    INNER JOIN ReservationAddress WITH (NOLOCK) ON ReservationAddress.AddressNumber = Folios.ReservationAddressNumber
      AND ReservationAddress.AddressType = 1
    INNER JOIN Transactions WITH (NOLOCK) ON Transactions.FolioNumber = Folios.FolioNumber
    WHERE Folios.OpenFolio = 0
    AND ISNULL(Folios.ReferenceNumber, 0) = 0 
    AND ISNULL(Folios.ReservationAddressNumber, 0) > 0
    AND Folios.HotelCustomerNumber = @AHotelCustomerNumber
    AND ISNULL(ReservationAddress.AddressAnonymized, 0) = 0
    AND ReservationAddress.Link NOT IN (
      SELECT FK_CustomerNumber
      FROM GDPRApproveCustomerXTab WITH (NOLOCK)
      INNER JOIN GDPRApprove WITH (NOLOCK) ON GDPRApprove.Id = GDPRApproveCustomerXTab.FK_GDPRApproveId
        AND GDPRApprove.GDPRApprovalType IN (1,2)
      WHERE ISNULL(GDPRApproveCustomerXTab.DateApprove, 0) >= ISNULL(GDPRApproveCustomerXTab.DateReject, 0)

      UNION

      SELECT DISTINCT CustomerNumber
      FROM GDPRMAIL WITH (NOLOCK)
      WHERE (ISNULL(ReservationAddress.DoAnonymize, 0) = 0) AND GDPRMailDate >= CAST(DATEADD(DAY, @GDPRMailDays, GETDATE()) AS DATE) AND GDPRMailType = 1
    ) -- Exclude customer/guest that has approved GDPR and them that has an active GDPR Link
    AND ReservationAddress.AddressNumber NOT IN (
      SELECT DISTINCT Folios.ReservationAddressNumber
      FROM Transactions WITH (NOLOCK)
      INNER JOIN Folios WITH (NOLOCK) ON Folios.FolioNumber = Transactions.FolioNumber
      LEFT JOIN InvoiceTransactionXtab WITH (NOLOCK) ON InvoiceTransactionXtab.FkTransaction = Transactions.TransactionNumber
      LEFT JOIN Invoice WITH (NOLOCK) ON Invoice.PkInvoice = InvoiceTransactionXtab.FkInvoice
      WHERE ISNULL(Invoice.ReceiptType, 0) <= 0
      AND Transactions.ShowOnBillFlag = 1 
      AND Transactions.MovedDeleted = 0 
      AND Invoice.CancelDate IS NULL
      AND Folios.ReservationAddressNumber > 0
    )
    GROUP BY Folios.FolioNumber, Folios.ReservationAddressNumber
    HAVING MAX(Transactions.DateStamp) <= @dtInvoice
  END

  EXEC ReservationAddressAnonymize_Do @AHotelCustomerNumber, @ReplaceText
END

DROP TABLE #ResTable

IF @AMainHotel = 1
BEGIN
  EXEC CustomerAnonymize @AHotelCustomerNumber, @AAll
  EXEC StaffAnonymize @AAll
END