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

  Created by Kent Johansen, June 2021
  Changes:
    20210913: Only anonymize ordinary staff

*/

SET NOCOUNT ON

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, ZipCodeNumber, 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, ZipCodeNumber, 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) 

  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,
    ZipCodeNumber = CASE WHEN (CAST(ISNULL((SELECT KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'AnonymizeStaffDataClearZipCode'), 0) AS BIT)) = 1 THEN NULL ELSE ZipCodeNumber 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