<?xml version="1.0" encoding="utf-8"?>
<AlvaoApplication xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" ModelVersion="1">
  <Applications>
    <Application id="2">
      <Name>Anonymization of deleted users</Name>
      <Description>Application anonymize data for deleted users (such as name, email,...) after 30 days. The 30 days can be changed in advanced settings with property Alvao.Users.AnonymizeDeletedAfterDays.</Description>
      <UniqueId>ba778045-f4a5-44c7-a579-8f776032942e</UniqueId>
      <Version>2</Version>
      <AdvancedSettings>
        <Setting>
          <Name>Alvao.Users.AnonymizeDeletedAfterDays</Name>
          <Value>30</Value>
        </Setting>
      </AdvancedSettings>
      <Scripts>
        <Script id="6">
          <Name>AnonymizeDeletedUsersPeriodicAction</Name>
          <Code>using System;
using Microsoft.Data.SqlClient;
using Alvao.API.Common;
using Alvao.Apps.API;
using Alvao.Context;
using Alvao.API.Utils;
using Dapper;

public class AnonymizeDeletedUsersPeriodicAction : IPeriodicAction
{
    public string Name 
    { 
        get =&gt; "AnonymizeDeletedUsersPeriodicAction".GetHashCode().ToString();
        set { }
    }

    public static int AnonymizeDeletedAfterDays
    {
        get
        {
            return CacheUtil.GetOrCreateCachedItem("AnonymizeDeletedAfterDaysCache", FetchAnonymizeDeletedAfterDays, minutesToCache: 1);
            int FetchAnonymizeDeletedAfterDays()
            {
                try
                {
                    using var scope = AlvaoContext.GetConnectionScope();
                    var value = scope.Connection.QueryFirstOrDefault&lt;string&gt;("SELECT TOP 1 sPropertyValue FROM tProperty WHERE sProperty = 'Alvao.Users.AnonymizeDeletedAfterDays'", null, scope.Transaction);
                    if (string.IsNullOrEmpty(value))
                    {
                        return 30;
                    }
                    int intValue = Int32.Parse(value);
                    return intValue;
                }
                catch (Exception)
                {
                    return 30;
                }
            }
        }
    }

    public void OnPeriod(SqlConnection con)
    {
        var currentTime = DateTime.Now;
        if (currentTime.Hour == 2)
        {
            using (var scope = AlvaoContext.GetConnectionScope())
            {
                int days = AnonymizeDeletedAfterDays;

                string sql = @"
DECLARE @DaysThreshold INT = @DaysThresholdParam;

DECLARE @DbLocaleId INT = (SELECT TOP 1 iDbLocaleId FROM tDb)

if object_id('tempdb..#AnonymizedUsers') is not null drop table #AnonymizedUsers 
CREATE TABLE #AnonymizedUsers (iPersonId INT, sPerson NVARCHAR(255));

INSERT INTO #AnonymizedUsers (iPersonId, sPerson)
SELECT iPersonId, 
       sPerson
FROM dbo.tPerson
WHERE dPersonRemoved IS NOT NULL
    AND DATEDIFF(DAY, dPersonRemoved, GETDATE()) &gt; @DaysThreshold
    AND sPerson NOT IN ('Deleted user', 'Odstraněný uživatel');

--select * from #AnonymizedUsers

UPDATE dbo.tPerson
SET 
    sPerson = CASE WHEN @DbLocaleId = 1029 THEN 'Odstraněný uživatel' ELSE 'Deleted user' END,
    sPersonLogin = NULL,
    sPersonEmail = NULL,
    sPersonPhone = NULL,
    sPersonMobile = NULL,
    sPersonOffice = NULL,
    sPersonDepartment = NULL,
    dPersonCreated = NULL,
    sPersonWorkPosition = NULL,
    mPersonContact = NULL,
    sPersonUrgentEmail = NULL,
    mPersonNotes = NULL,
    sPersonCountry = NULL,
    sPersonCountryAbbr = NULL,
    iPersonCountryCode = NULL,
    sPersonPreferredLanguage = NULL,
    sPersonCity = NULL,
    oPersonSID = NULL,
    sPersonAdPath = NULL,
    dPersonAdCreated = NULL,
    sPersonSID = NULL,
    liPersonManagerPersonId = NULL,
    sPersonPersonalNumber = NULL,
    liPersonDelegatePersonId = NULL,
    OutOfOfficeSince = NULL,
    iPersonLocaleId = NULL,
    sPersonAdName = NULL,
    liAccountId = NULL,
    sFirstName = NULL,
    sLastName = NULL,
    sImportSystem = NULL,
    sImportRecordId = NULL,
    sAdDisplayName = NULL,
    TimeZone = NULL,
    Email2 = NULL,
    Phone2 = NULL,
    PersonPortrait = NULL,
    PersonPortraitHash = NULL,
    OutOfOfficeUntil = NULL,
    ImportAdLastFound = NULL,
    AzureAdObjectId = NULL,
    AzureAdTenantId = NULL,
    SamAccountName = NULL,
    AadAppClientId = NULL,
    AadAppTenantId = NULL,
    TeamsConversationId = NULL,
    RefreshToken = NULL,
    PersonPortraitETag = NULL
WHERE iPersonId IN (SELECT iPersonId FROM #AnonymizedUsers);

UPDATE dbo.tHdTicket
SET 
    sHdTicketUser = CASE WHEN @DbLocaleId = 1029 THEN 'Odstraněný uživatel' ELSE 'Deleted user' END,
    sHdTicketUserEmail = NULL,
    sHdTicketUserPhone = NULL,
    mHdTicketUserContact = NULL,
    sHdTicketUserMobile = NULL,
    sHdTicketUserOffice = NULL,
    sHdTicketUserDepartment = NULL,
    sHdTicketUserWorkPosition = NULL,
    sHdTicketUserCompany = NULL,
	UserEmail2 = NULL,
	UserPhone2 = NULL,
    FullTextSearch = REPLACE(FullTextSearch collate database_default, u.sPerson collate database_default, '')
FROM dbo.tHdTicket t
JOIN #AnonymizedUsers u ON t.liHdTicketUserPersonId = u.iPersonId or t.liHdTicketSolverPersonId = u.iPersonId;

DROP TABLE #AnonymizedUsers;
                ";
                scope.Connection.Execute(sql, new {DaysThresholdParam = days}, scope.Transaction);
            }
        }
    }
}
</Code>
          <IsLibCode>false</IsLibCode>
          <Codesign>iFkkXdy1WXGfPvxU38MvM+jb3GhFg01AaKNgwefz1nFHz9Djq492VOAgMW4ZTFSzmc9WmsFgBOxMIX0+keAFq9ysHwIfcu6sBg4VB/CDdoOMUlhmqbGXEzOpRAL4QarsRBj2tKqL4V/eUTsAMpLe0TOFetNXMg8BQYeRM5A9UKji7aXXI724ayQFaOJwl4nHDESpMT5agwQbPe/8OHivC4kHZ5KA3b7Qt2VTdBJod+zDR+2QtowmIs01N3kESzWe+jU3MNmuflc600c+h4gmcaZY7qbW6cciSejr/DWrFDiUOCr76UxKJvoJ2aphlyJeRmgolFwVFPHltqs4BnfFKw==</Codesign>
        </Script>
      </Scripts>
    </Application>
  </Applications>
</AlvaoApplication>