CREATE PROCEDURE dbo.StaffAnonymize @AAll BIT = 0 AS
/*

  Created by Kent Johansen, June 2021
  Changes:
    20230804 MLJ: ZipCodeNumber column changed to ZipCode, City and CountryNumber columns
    20221017: Added deactivate staff after x-days. Req #4122
    20210913: Only anonymize ordinary staff

*/

SET NOCOUNT ON

DECLARE @DeactivateStaffDays INT
SELECT @DeactivateStaffDays = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'StaffDeactiveDays'
SET @DeactivateStaffDays = ISNULL(@DeactivateStaffDays, 0)

DECLARE @AnonymizeStaff BIT
SELECT @AnonymizeStaff = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'AnonymizeStaffDataEnabled'

DECLARE @SystemDateStr VARCHAR(100)
DECLARE @SystemDate DATETIME

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

IF @AnonymizeStaff = 1 OR @AAll = 1
BEGIN
  DECLARE @MonthStaff INT
  DECLARE @dt DATETIME
  DECLARE @Month INT
  DECLARE @dtCleanup DATETIME
  DECLARE @ReplaceText VARCHAR(10)

  SELECT @MonthStaff = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'AnonymizeStaffDataMonth'
  SET @MonthStaff = ISNULL(@MonthStaff, 0)
  SET @dt = DATEADD(MONTH, -@MonthStaff, @SystemDate)

  IF CAST(ISNULL((SELECT KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'AnonymizeStaffDataReplaceWithStar'), 0) AS BIT) = 1
    SET @ReplaceText = '******'
  ELSE
    SET @ReplaceText = ''

  SELECT @Month = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'GDPRStaffDeleteAfter'
  SET @Month = ISNULL(@Month, 60)
  SET @dtCleanup = DATEADD(MONTH, -@Month, @SystemDate) 

  DELETE PersonelHidden WHERE HiddenDate < @dtCleanup

  CREATE TABLE #Staff
  (
    UserIdNumber INT
  )

  IF @AAll = 1
  BEGIN
    INSERT INTO #Staff
    SELECT UserIdNumber
    FROM Personel WITH (NOLOCK)
    WHERE ISNULL(StaffAnonymized, 0) = 0
    AND UserTypeNumber = 1 -- utOrdinary
  END
  ELSE
  BEGIN
    INSERT INTO #Staff
    SELECT UserIdNumber
    FROM Personel WITH (NOLOCK)
    WHERE ISNULL(StaffAnonymized, 0) = 0
    AND (DeletedDate < @dt or ForceAnonymization = 1)
    AND UserTypeNumber = 1 -- utOrdinary
  END

  INSERT INTO PersonelHidden (HiddenDate, FK_UserIDNumber, FirstName, LastName, SocialSecurityNumber, DateOfBirth, Address1, Address2, Address3, ZipCode, City, CountryNumber, EmploymentDate, BankAccountNumber, BankName,
    EMailHotel, EMailPrivate, MobilePhoneNumber, HomepageURL, FaxNumberHotel, FaxNumberPrivate, PhonePrivate, PhoneHotel, CardCode, MailSignature, EmailPassword,
    EmailOffice365LoginUsername, EmailOffice365LoginPassword, PasswordHash, PinCodeHash, Obsolete_Passwd)
  SELECT GETDATE(), UserIDNumber, FirstName, LastName, SocialSecurityNumber, DateOfBirth, Address1, Address2, Address3, ZipCode, City, CountryNumber, EmploymentDate, BankAccountNumber, BankName,
    EMailHotel, EMailPrivate, MobilePhoneNumber, HomepageURL, FaxNumberHotel, FaxNumberPrivate, PhonePrivate, PhoneHotel, CardCode, MailSignature, EmailPassword,
    EmailOffice365LoginUsername, EmailOffice365LoginPassword, PasswordHash, PinCodeHash, Obsolete_Passwd
  FROM Personel WITH (NOLOCK)
  WHERE UserIdNumber IN (SELECT UserIdNumber FROM #Staff) 

  DECLARE @AnonymizeStaffDataClearZipCode BIT = 0
  SELECT @AnonymizeStaffDataClearZipCode=ISNULL(CAST(KeyValue AS BIT),0) FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'AnonymizeStaffDataClearZipCode'

  UPDATE Personel
  SET 
    FirstName = SUBSTRING(SUBSTRING(FirstName, 1, (CAST(ISNULL((SELECT KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'AnonymizeStaffDataFirstName'), 0) AS INT))) + @ReplaceText, 1, 50),
    LastName = SUBSTRING(SUBSTRING(LastName, 1, (CAST(ISNULL((SELECT KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'AnonymizeStaffDataLastName'), 0) AS INT))) + @ReplaceText, 1, 50),
    SocialSecurityNumber = SUBSTRING(SUBSTRING(SocialSecurityNumber, 1, (CAST(ISNULL((SELECT KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'AnonymizeStaffDataSocialSecurityNumber'), 0) AS INT))) + @ReplaceText, 1, 16),
    Address1 = SUBSTRING(SUBSTRING(Address1, 1, (CAST(ISNULL((SELECT KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'AnonymizeStaffDataAddress1'), 0) AS INT))) + @ReplaceText, 1, 50),
    Address2 = SUBSTRING(SUBSTRING(Address2, 1, (CAST(ISNULL((SELECT KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'AnonymizeStaffDataAddress2'), 0) AS INT))) + @ReplaceText, 1, 50),
    Address3 = SUBSTRING(SUBSTRING(Address3, 1, (CAST(ISNULL((SELECT KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'AnonymizeStaffDataAddress3'), 0) AS INT))) + @ReplaceText, 1, 50),
    BankAccountNumber = SUBSTRING(SUBSTRING(BankAccountNumber, 1, (CAST(ISNULL((SELECT KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'AnonymizeStaffDataBankAccountNumber'), 0) AS INT))) + @ReplaceText, 1, 50),
    BankName = SUBSTRING(SUBSTRING(BankName, 1, (CAST(ISNULL((SELECT KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'AnonymizeStaffDataBankName'), 0) AS INT))) + @ReplaceText, 1, 30),
    EMailHotel = SUBSTRING(SUBSTRING(EMailHotel, 1, (CAST(ISNULL((SELECT KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'AnonymizeStaffDataHotelMail'), 0) AS INT))) + @ReplaceText, 1, 255),
    EMailPrivate = SUBSTRING(SUBSTRING(EMailPrivate, 1, (CAST(ISNULL((SELECT KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'AnonymizeStaffDataPrivateMail'), 0) AS INT))) + @ReplaceText, 1, 255),
    MobilePhoneNumber = SUBSTRING(SUBSTRING(MobilePhoneNumber, 1, (CAST(ISNULL((SELECT KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'AnonymizeStaffDataMobilePhoneFax'), 0) AS INT))) + @ReplaceText, 1, 50),
    HomepageURL = SUBSTRING(SUBSTRING(HomepageURL, 1, (CAST(ISNULL((SELECT KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'AnonymizeStaffDataHomePageURL'), 0) AS INT))) + @ReplaceText, 1, 255),
    FaxNumberHotel = SUBSTRING(SUBSTRING(FaxNumberHotel, 1, (CAST(ISNULL((SELECT KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'AnonymizeStaffDataMobilePhoneFax'), 0) AS INT))) + @ReplaceText, 1, 30),
    FaxNumberPrivate = SUBSTRING(SUBSTRING(FaxNumberPrivate, 1, (CAST(ISNULL((SELECT KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'AnonymizeStaffDataMobilePhoneFax'), 0) AS INT))) + @ReplaceText, 1, 30),
    PhonePrivate = SUBSTRING(SUBSTRING(PhonePrivate, 1, (CAST(ISNULL((SELECT KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'AnonymizeStaffDataMobilePhoneFax'), 0) AS INT))) + @ReplaceText, 1, 30),
    PhoneHotel = SUBSTRING(SUBSTRING(PhoneHotel, 1, (CAST(ISNULL((SELECT KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'AnonymizeStaffDataMobilePhoneFax'), 0) AS INT))) + @ReplaceText, 1, 30),

    DateOfBirth = CASE WHEN (CAST(ISNULL((SELECT KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'AnonymizeStaffDataClearBirthDate'), 0) AS BIT)) = 1 THEN NULL ELSE DateOfBirth END,
    EmploymentDate = CASE WHEN (CAST(ISNULL((SELECT KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'AnonymizeStaffDataClearEmploymentDate'), 0) AS BIT)) = 1 THEN NULL ELSE EmploymentDate END,
    CardCode = CASE WHEN (CAST(ISNULL((SELECT KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'AnonymizeStaffDataClearLoginCard'), 0) AS BIT)) = 1 THEN NULL ELSE CardCode END,
    ZipCode = CASE WHEN @AnonymizeStaffDataClearZipCode = 1 THEN NULL ELSE ZipCode END,
    City = CASE WHEN @AnonymizeStaffDataClearZipCode = 1 THEN NULL ELSE City END,
    CountryNumber = CASE WHEN @AnonymizeStaffDataClearZipCode = 1 THEN NULL ELSE CountryNumber END,
    MailSignature = CASE WHEN (CAST(ISNULL((SELECT KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'AnonymizeStaffDataClearSignatures'), 0) AS BIT)) = 1 THEN NULL ELSE MailSignature END,

    EmailPassword = CASE WHEN (CAST(ISNULL((SELECT KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'AnonymizeStaffDataClearMailLoginInfo'), 0) AS BIT)) = 1 THEN NULL ELSE EmailPassword END,
    EmailOffice365LoginUsername = CASE WHEN (CAST(ISNULL((SELECT KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'AnonymizeStaffDataClearMailLoginInfo'), 0) AS BIT)) = 1 THEN NULL ELSE EmailOffice365LoginUsername END,
    EmailOffice365LoginPassword = CASE WHEN (CAST(ISNULL((SELECT KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'AnonymizeStaffDataClearMailLoginInfo'), 0) AS BIT)) = 1 THEN NULL ELSE EmailOffice365LoginPassword END,
    PasswordHash = CASE WHEN (CAST(ISNULL((SELECT KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'AnonymizeStaffDataClearMailLoginInfo'), 0) AS BIT)) = 1 THEN 'aaa' ELSE PasswordHash END,
    PinCodeHash = CASE WHEN (CAST(ISNULL((SELECT KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'AnonymizeStaffDataClearMailLoginInfo'), 0) AS BIT)) = 1 THEN NULL ELSE PinCodeHash END,
    Obsolete_Passwd = CASE WHEN (CAST(ISNULL((SELECT KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'AnonymizeStaffDataClearMailLoginInfo'), 0) AS BIT)) = 1 THEN NULL ELSE Obsolete_Passwd END,

    ActivePerson = 0,
    StaffAnonymized = 1,
    AnonymizeDate = GETDATE()
  WHERE UserIdNumber IN (SELECT UserIdNumber FROM #Staff) 


  IF (CAST(ISNULL((SELECT KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'AnonymizeStaffDataClearSignatures'), 0) AS BIT)) = 1
    DELETE PersonnelSignatureXtab WHERE FK_UserIDNumber IN (SELECT UserIdNumber FROM #Staff)

  DROP TABLE #Staff

  IF ISNULL(@AAll, 0) = 1 -- If DemoHotel script
    UPDATE Personel 
    SET ActivePerson = 1, FirstName = UserIdCode, LastName = UserIdCode
    WHERE ISNULL(MarkAsDeleted, 0) = 0 AND UserGroup IN (2,3) AND ISNULL(ActivePerson, 0) = 0
END

IF @DeactivateStaffDays > 0
BEGIN
  UPDATE Personel 
  SET ActivePerson = 0
  WHERE DateOfLastLogin < DATEADD(DAY, -@DeactivateStaffDays, @SystemDate)
  AND ISNULL(DateOfLastLogin, 0) > 0
  AND ISNULL(ActivePerson, 0) = 1
  AND ISNULL(MarkAsDeleted, 0) = 0
  AND UserTypeNumber = 1 -- utOrdinary
END