CREATE PROCEDURE dbo.CustomerAnonymize @AHotelCustomerNumber INT, @AAll BIT = 0 AS
/*
  Created by Kent Johansen, July 2017
  Changes:
  KJ 20220221: Bug: Fix for "Customer is anonymized even if turnover in Ordrebook". Case #CR215312
  KJ 20210610: Bug: Fix for "Customers not anonymized with stars"
  KJ 20210413: Ext: Do not anonymize owners. Req. #3962
  KJ 20210219: Bug: Fix for "All customer not anonymized when running in demoscript".
  KJ 20201203: Bug: Customer not anonomized when ForceAnonomize is set. Case #CR202693
  KJ 20191205: Ext: removed use of hidden
  vd 20191001 Discontinuing table CustomerVarious
  KJ 20190430: Performance issue. 
  KJ 20181113: AllContactsLinkID (Mødeleder)
  KJ 20181112: AllContactsLinkID (Mødeleder)
  KJ 20180716: Bug: Fix for "Anonymization is very slow". Case #CR149958
  KJ 20180604: Ext: Different amount of month for anonymize Guests and contact persons.
  KJ 20180528: Bug: Fix for "String or binary data would be truncated."
  KJ 20180522: Ext: Added anonymize all
  KJ 20180507: Ext: exclude customers with turnover within x-months
  KJ 20180419: Ext: Added hidden tables
  KJ 20180206: Added anonymize contact person.
  KJ 20180201: Added Clear notes
  KJ 20180111: Added Approval expire.
  Altered by Villy Damsbo, December 2017. GDPRMailDate in table CUSTOMER no longer exists
  Altered by Villy Damsbo, November 2017. GDPRMailDate moved from table CUSTOMER to new table GDPRMAIL

*/
SET NOCOUNT ON

DECLARE @AnonymizeCustomer BIT
SELECT @AnonymizeCustomer = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'AnonymizeCustomerDataEnabled'

DECLARE @SystemDateStr VARCHAR(100)
DECLARE @SystemDate DATETIME

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

IF @AnonymizeCustomer = 1 OR @AAll = 1
BEGIN
  DECLARE @ReplaceText VARCHAR(10)
  DECLARE @GDPRMailDays INT
  DECLARE @CustomerNumber INT
  DECLARE @Month INT
  DECLARE @MonthCust INT
  DECLARE @dt DATETIME
  DECLARE @dtContact DATETIME
  
  SELECT @MonthCust = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'AnonymizeCustomerDataMonth'
  SET @MonthCust = ISNULL(@MonthCust, 0)
  SET @dt = DATEADD(MONTH, -@MonthCust, @SystemDate)

  SELECT @MonthCust = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'AnonymizeContactsDataMonth'
  SET @MonthCust = ISNULL(@MonthCust, 0)
  SET @dtContact = DATEADD(MONTH, -@MonthCust, @SystemDate)

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

  DECLARE @NotePageCSV VARCHAR(10)
  DECLARE @CustTypeCSV VARCHAR(10)

  IF @AAll = 1
    SET @NotePageCSV = '-1,1,2,3'
  ELSE
  BEGIN
    SET @NotePageCSV = '-1'
    IF CAST(ISNULL((SELECT KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'AnonymizeCustClearNote1'), 0) AS BIT) = 1
      SET @NotePageCSV = @NotePageCSV + ',1'
    IF CAST(ISNULL((SELECT KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'AnonymizeCustClearNote2'), 0) AS BIT) = 1
      SET @NotePageCSV = @NotePageCSV + ',2'
    IF CAST(ISNULL((SELECT KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'AnonymizeCustClearNote3'), 0) AS BIT) = 1
      SET @NotePageCSV = @NotePageCSV + ',3'
  END

  IF @AAll = 1
    SET @CustTypeCSV = '1,8'
  ELSE
  BEGIN
    SET @CustTypeCSV = '1'
    IF CAST(ISNULL((SELECT KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'AnonymizeCustomerDataContactInfoEnabled'), 0) AS BIT) = 1
      SET @CustTypeCSV = @CustTypeCSV + ',8'
  END
  
  IF CAST(ISNULL((SELECT KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'AnonymizeCustomerDataReplaceWithStar'), 0) AS BIT) = 1
    SET @ReplaceText = '******'
  ELSE
    SET @ReplaceText = ''

  CREATE TABLE #CustNo
  (
    CustomerNumber INT
  )

  IF @AAll = 1
  BEGIN
    INSERT INTO #CustNo
    SELECT CustomerNumber
    FROM Customer WITH (NOLOCK)
    WHERE ISNULL(CustomerAnonymized, 0) = 0
    AND CustomerTypeNumber IN (SELECT Number FROM  dbo.iter_intlist_to_table_No_Dummy(@CustTypeCSV))
  END
  ELSE
  BEGIN
    INSERT INTO #CustNo
    SELECT CustomerNumber
    FROM Customer WITH (NOLOCK)
    WHERE ISNULL(CustomerAnonymized, 0) = 0
    AND CustomerTypeNumber IN (SELECT Number FROM  dbo.iter_intlist_to_table_No_Dummy(@CustTypeCSV))
    AND CustomerNumber NOT IN (
      SELECT RA.ContactpersonCustomerNumber
      FROM ReservationAddress RA WITH (NOLOCK)
      LEFT OUTER JOIN Folios WITH (NOLOCK) ON Folios.ReservationAddressNumber = RA.AddressNumber 
      WHERE RA.AddressNumber > 0
      AND RA.AddressType > 1 
      AND ISNULL(RA.AddressAnonymized, 0) = 0
      AND (ISNULL(Folios.FolioNumber, 0) = 0 OR Folios.ReferenceNumber > 0)  
      AND RA.ContactpersonCustomerNumber > 0

      UNION

      SELECT RA.AllContactsLinkID
      FROM ReservationAddress RA WITH (NOLOCK)
      LEFT OUTER JOIN Folios WITH (NOLOCK) ON Folios.ReservationAddressNumber = RA.AddressNumber 
      WHERE RA.AddressNumber > 0
      AND ISNULL(RA.AddressAnonymized, 0) = 0
      AND (ISNULL(Folios.FolioNumber, 0) = 0 OR Folios.ReferenceNumber > 0)  
      AND RA.AllContactsLinkID > 0

      UNION

      SELECT RA.Link
      FROM ReservationAddress RA WITH (NOLOCK)
      LEFT OUTER JOIN Folios WITH (NOLOCK) ON Folios.ReservationAddressNumber = RA.AddressNumber 
      WHERE RA.AddressNumber > 0
      AND RA.AddressType = 1 
      AND ISNULL(RA.AddressAnonymized, 0) = 0
      AND (ISNULL(Folios.FolioNumber, 0) = 0 OR Folios.ReferenceNumber > 0)  
      AND RA.Link > 0
    )

    DELETE #CustNo WHERE CustomerNumber IN (
      SELECT DISTINCT CustomerOrdreBook.CustomerNumber
      FROM CustomerOrdreBook WITH (NOLOCK)
      INNER JOIN Customer WITH (NOLOCK) ON Customer.CustomerNumber = CustomerOrdreBook.CustomerNumber
      WHERE ((CustomerTypeNumber = 1 AND DateForValue >= @dt) OR (CustomerTypeNumber = 8 AND DateForValue >= @dtContact))
      AND ISNULL(Customer.ForceAnonymization, 0) <> 1
    )

    DELETE #CustNo WHERE CustomerNumber IN (
      SELECT FK_CustomerNumber
      FROM GDPRApproveCustomerXTab WITH (NOLOCK)
      INNER JOIN GDPRApprove WITH (NOLOCK) ON GDPRApprove.Id = GDPRApproveCustomerXTab.FK_GDPRApproveId
      INNER JOIN Customer WITH (NOLOCK) ON Customer.CustomerNumber = GDPRApproveCustomerXTab.FK_CustomerNumber
      WHERE ISNULL(GDPRApproveCustomerXTab.DateApprove, 0) >= ISNULL(GDPRApproveCustomerXTab.DateReject, 0)
      AND ISNULL(Customer.ForceAnonymization, 0) <> 1
    )

    DELETE #CustNo WHERE CustomerNumber IN (
      SELECT DISTINCT CustomerNumber
      FROM GDPRMAIL WITH (NOLOCK)
      WHERE GDPRMailDate >= CAST(DATEADD(DAY, @GDPRMailDays, GETDATE()) AS DATE) AND GDPRMailType = 1  
    )

    DELETE #CustNo WHERE CustomerNumber IN (
      SELECT DISTINCT CustomerNumber
      FROM Rooms WITH (NOLOCK)
      WHERE ISNULL(CustomerNumber, 0) > 0
    )
  END

  UPDATE Customer
  SET 
    FirstName = SUBSTRING(SUBSTRING(FirstName, 1, (CAST(ISNULL((SELECT KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'AnonymizeCustomerDataFirstName'), 0) AS INT))) + @ReplaceText, 1, 50),
    LastName = 
      CASE WHEN SUBSTRING(LastName, 1, (CAST(ISNULL((SELECT KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'AnonymizeCustomerDataLastName'), 0) AS INT))) + @ReplaceText = '' THEN
        ''
      ELSE
        SUBSTRING(SUBSTRING(LastName, 1, (CAST(ISNULL((SELECT KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'AnonymizeCustomerDataLastName'), 0) AS INT))) + @ReplaceText, 1, 60)
      END,
    Address1 = SUBSTRING(SUBSTRING(Address1, 1, (CAST(ISNULL((SELECT KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'AnonymizeCustomerDataAddress1'), 0) AS INT))) + @ReplaceText, 1, 100),
    Address2 = SUBSTRING(SUBSTRING(Address2, 1, (CAST(ISNULL((SELECT KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'AnonymizeCustomerDataAddress2'), 0) AS INT))) + @ReplaceText, 1, 100),
    Address3 = SUBSTRING(SUBSTRING(Address3, 1, (CAST(ISNULL((SELECT KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'AnonymizeCustomerDataAddress3'), 0) AS INT))) + @ReplaceText, 1, 100),
    ZipCodeOverwrite = SUBSTRING(SUBSTRING(ZipCodeOverwrite, 1, (CAST(ISNULL((SELECT KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'AnonymizeCustomerDataZipCode'), 0) AS INT))) + @ReplaceText, 1, 10),
    PhoneNumber = SUBSTRING(SUBSTRING(PhoneNumber, 1, (CAST(ISNULL((SELECT KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'AnonymizeCustomerDataPhone'), 0) AS INT))) + @ReplaceText, 1, 60),
    TelexNumber = SUBSTRING(SUBSTRING(TelexNumber, 1, (CAST(ISNULL((SELECT KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'AnonymizeCustomerDataTelex'), 0) AS INT))) + @ReplaceText, 1, 30),
    FaxNumber = SUBSTRING(SUBSTRING(FaxNumber, 1, (CAST(ISNULL((SELECT KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'AnonymizeCustomerDataFax'), 0) AS INT))) + @ReplaceText, 1, 30),
    CellPhoneNumber = SUBSTRING(SUBSTRING(CellPhoneNumber, 1, (CAST(ISNULL((SELECT KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'AnonymizeCustomerDataMobileCell'), 0) AS INT))) + @ReplaceText, 1, 60),
    Title = SUBSTRING(SUBSTRING(Title, 1, (CAST(ISNULL((SELECT KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'AnonymizeCustomerDataTitle'), 0) AS INT))) + @ReplaceText, 1, 100),
    WWWAddress = SUBSTRING(SUBSTRING(WWWAddress, 1, (CAST(ISNULL((SELECT KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'AnonymizeCustomerDataWWW'), 0) AS INT))) + @ReplaceText, 1, 100),
    MemberCardNumber = CASE WHEN (CAST(ISNULL((SELECT KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'AnonymizeCustomerDataClearMemberCard'), 1) AS BIT)) = 1 THEN NULL ELSE MemberCardNumber END,
    GenderTitle = SUBSTRING(SUBSTRING(GenderTitle, 1, (CAST(ISNULL((SELECT KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'AnonymizeCustomerDataGenderTitle'), 0) AS INT))) + @ReplaceText, 1, 50),
    CountryNumber = NULL,
    CityOverWrite = SUBSTRING(SUBSTRING(CityOverWrite, 1, (CAST(ISNULL((SELECT KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'AnonymizeCustomerDataCity'), 0) AS INT))) + @ReplaceText, 1, 50),
    PassPortNumber = SUBSTRING(SUBSTRING(PassPortNumber, 1, (CAST(ISNULL((SELECT KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'AnonymizeCustomerDataPassport'), 0) AS INT))) + @ReplaceText, 1, 25),
    MiddleName = SUBSTRING(SUBSTRING(MiddleName, 1, (CAST(ISNULL((SELECT KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'AnonymizeCustomerDataMiddleName'), 0) AS INT))) + @ReplaceText, 1, 40),
    Suffix = SUBSTRING(SUBSTRING(Suffix, 1, (CAST(ISNULL((SELECT KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'AnonymizeCustomerDataTitle'), 0) AS INT))) + @ReplaceText, 1, 40),
    CustomerAnonymized = 1,
    MarkAsDeleted = 1,
    GDPRId = NULL,
    AnonymizeDate = GETDATE()
  WHERE CustomerNumber IN (SELECT CustomerNumber FROM #CustNo) 
  
  UPDATE MailAddresses
  SET 
    MailAddress = SUBSTRING(MailAddress, 1, (CAST(ISNULL((SELECT KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'AnonymizeCustomerDataMail'), 0) AS INT))) + @ReplaceText
  WHERE CustomerNumber IN (SELECT CustomerNumber FROM #CustNo) 

  DELETE InetCustomer WHERE CustomerNumber IN (SELECT CustomerNumber FROM #CustNo)
  DELETE CustomerCardXTab WHERE CustomerNumber IN (SELECT CustomerNumber FROM #CustNo)
  DELETE CustomerAttachmentXTab WHERE CustomerNumber IN (SELECT CustomerNumber FROM #CustNo)
  DELETE Attachment WHERE AttachmentNumber NOT IN (SELECT AttachmentNumber FROM ReservationAttachmentXTab WITH (NOLOCK) UNION SELECT AttachmentNumber FROM CustomerAttachmentXTab WITH (NOLOCK))
  DELETE CustomerLinks WHERE CustomerNumber IN (SELECT CustomerNumber FROM #CustNo) OR CustomerLinkNumber IN (SELECT CustomerNumber FROM #CustNo)

  IF @NotePageCSV <> '-1'
  BEGIN
    DELETE NoteContents
    WHERE PageNumber IN (SELECT Number FROM  dbo.iter_intlist_to_table_No_Dummy(@NotePageCSV))
    AND CustomerNumber IN (SELECT CustomerNumber FROM #CustNo)
  END

  DECLARE @dtstr CHAR(8)
  DECLARE @timestr CHAR(5)
  
  SET @dtStr = YEAR(GETDATE()) * 10000 + MONTH(GETDATE()) * 100 + DAY(GETDATE())
  SET @timestr = CONVERT(CHAR(5), GETDATE(), 108) 

  DECLARE #CustCur CURSOR FAST_FORWARD READ_ONLY FOR
    SELECT CustomerNumber FROM #CustNo
  OPEN #CustCur

  FETCH NEXT FROM #CustCur INTO @CustomerNumber

  WHILE @@FETCH_STATUS = 0
  BEGIN
    INSERT INTO UniversalLog (UserId, Time, SubType, Link, Description, DateStamp, AText) VALUES 
      ('TECHOTEL', @timestr, 3, @CustomerNumber, 'Customer anonymized and marked as deleted', @dtStr, 'Customer anonymized and marked as deleted')

    FETCH NEXT FROM #CustCur INTO @CustomerNumber
  END

  CLOSE #CustCur
  DEALLOCATE #CustCur

  DROP TABLE #CustNo
END

-- Handle expire
DECLARE @ServerDateTime DATETIME
DECLARE @RejectDT DATETIME
DECLARE @CustReject INT
DECLARE @ApproveId INT
DECLARE @GDPRTypeName VARCHAR(50)

DECLARE @SystemUserIDNumber INT
SELECT TOP 1 @SystemUserIDNumber = UserIdNumber FROM Personel WITH (NOLOCK) WHERE UserTypeNumber = 4 -- AutoNight

SET @ServerDateTime = CURRENT_TIMESTAMP

SET @RejectDT = CONVERT(DATETIME, @SystemDateStr + ' ' + CONVERT(CHAR(8), @ServerDateTime, 108))

DECLARE #CustCurReject CURSOR FAST_FORWARD READ_ONLY FOR
  SELECT GDPRApproveCustomerXTab.FK_CustomerNumber, GDPRApproveCustomerXTab.FK_GDPRApproveId, GDPRApprove.Name
  FROM GDPRApproveCustomerXTab WITH (NOLOCK)
  INNER JOIN GDPRApprove WITH (NOLOCK) ON GDPRApprove.Id = GDPRApproveCustomerXTab.FK_GDPRApproveId
  WHERE ISNULL(GDPRApprove.ExpireMonth, 0) > 0
  AND ISNULL(GDPRApproveCustomerXTab.DateReject, 0) = 0
  AND ISNULL(GDPRApproveCustomerXTab.DateApprove, 0) > 0
  AND ISNULL(GDPRApproveCustomerXTab.DateApprove, 0) <= DATEADD(MONTH, -GDPRApprove.ExpireMonth, @SystemDate)
OPEN #CustCurReject

FETCH NEXT FROM #CustCurReject INTO @CustReject, @ApproveId, @GDPRTypeName

WHILE @@FETCH_STATUS = 0
BEGIN
  UPDATE GDPRApproveCustomerXTab 
  SET DateReject = @RejectDT, FK_UserIdNumberReject = @SystemUserIDNumber 
  WHERE dbo.GDPRApproveCustomerXTab.FK_CustomerNumber = @CustReject 
  AND dbo.GDPRApproveCustomerXTab.FK_GDPRApproveId = @ApproveId

  -- Make log
  INSERT INTO dbo.UniversalLog (UserId, Time, SubType, Link, Description, DateStamp, AText) VALUES 
  ('TECHOTEL', CONVERT(CHAR(5), @ServerDateTime, 108), 19, @CustReject, 'GDPR Expire (' + @GDPRTypeName + ')', @SystemDateStr, 'GDPR Expire')

  FETCH NEXT FROM #CustCurReject INTO @CustReject, @ApproveId, @GDPRTypeName
END

CLOSE #CustCurReject
DEALLOCATE #CustCurReject