Adding a rule for duplicates search when saving a record

Introduction

Rules for duplicates search when saving a record have their specifics. When a record is saved, either basic rules or custom rules are applied (custom rules use the same fields as the basic rules).

To ensure that a custom deduplication rule is triggered not just during bulk deduplication but also when saving a record, take the following steps:

1. Execute the replacement for the DuplicatesSearchUtilitiesV2 schema.
2. Create a UsrSingleRequest class.
3. Add the UsrSingleRequesListener class.
4. Create a UsrDeduplicationProcessing class.
5. Compile and restart the application.
6. Create a custom service.
7. Replace the getDuplicatesServiceName and getFindDuplicatesServiceMethodNamе methods.
8. Delete stored procedures from the database.
9. Execute the script for the deletion of the CreatingObjectInfo type.
10. Create a tsp_FindDuplicate stored procedure.
11. Execute the installation process for remote stored procedures.
12. Add the tsp_FindAccountDuplicateByInn stored procedure.

Case description

Implement executing a custom deduplication rule when saving a record for an [Account] object.

Case implementation algorithm

1. Replace the DuplicatesSearchUtilitiesV2 schema

In the custom schema, replace the getDataForFindDuplicatesService() method by adding UsrINN in the same way as “Name”. The process for replacing the schema is covered in the "Configuration architectural elements” article.

The source code is available below.

getDataForFindDuplicatesService: function() {
    var communication = this.getCommunications();
    val email = this.get("Email");
    if (!this.Ext.isEmpty(email)) {
        communication.push({
            "Number": email,
            "CommunicationTypeId": ConfigurationConstants.CommunicationTypes.Email
        });
    }
    var data = {
        schemaName: this.entitySchemaName,
        request: {
            Id: this.get("Id"),
            Name: this.get("Name"),
            UsrINN: this.get("UsrINN"),
            AlternativeName: this.get("AlternativeName"),
            Communication: communication
        }
    };
    return data;
},

2. Create a UsrSingleRequest class

The UsrSingleRequest class must extend the SingleRequest class of the SearchDuplicatesService schema. Add the custom UsrINN property to the UsrSingleRequest class.

The source code is available below.

namespace Terrasoft.Configuration.SearchDuplicatesService {
    using System.ServiceModel;
    using System.ServiceModel.Web;
    using System.ServiceModel.Activation;
    using System.Web;
    using Terrasoft.Common;
    using Terrasoft.Core;
    using Terrasoft.Core.DB;
    using Terrasoft.Core.Entities;
    using Terrasoft.Core.Scheduler;
    using System;
    using System.Data;
    using System.Collections.Generic;
    using System.Linq;
    using System.Runtime.Serialization;
    using System.Xml;
    using Quartz;
    using Quartz.Impl.Triggers;
    using Column = Terrasoft.Core.DB.Column;
    
    [DataContract]
    public class UsrSingleRequest: SingleRequest {
        [DataMember]
        public string UsrINN { get; set; }
    }
}

3. Add the UsrSingleRequesListener class

Add the UsrSingleRequesListener class, which will replace a call to SingleRequest with a call to UsrSingleRequest. The UsrSingleRequesListener class must implement the IAppEventListener interface. Add ClassFactory.Bind() to the OnAppStart method.

The source code is available below.

namespace Terrasoft.Configuration
{
    using Terrasoft.Core.Factories;
    using Terrasoft.Web.Common;
    using Terrasoft.Configuration.SearchDuplicatesService;
    
    #region Class: UsrSingleRequestListener
    
    public class UsrSingleRequestListener: IAppEventListener
    {
        #region Methods: Public
        
        public void OnAppStart(AppEventContext context) {
            ClassFactory.Bind<SingleRequest, UsrSingleRequest>();
        }
        
        public void OnAppEnd(AppEventContext context) {
        }
        
        public void OnSessionStart(AppEventContext context) {
        }
        
        public void OnSessionStart(AppEventContext context) {
        }
        
        #endregion
    }
    
    #endregion
}

4. Create a UsrDeduplicationProcessing class

Create a UsrDeduplicationProcessing class inheriting the DeduplicationProcessing schema. Take the following steps in the created class:

1. Add the AddElementsToRow and GetPreparedXml methods, of the SearchDuplicatesService schema and change their names to UsrAddElementsToRow and UsrGetPreparedXml accordingly.
2. Add UsrINN to the UsrAddElementsToRow method.
3. In the UsrGetPreparedXml method, replace the call to the AddElementsToRow method with a call to the UsrAddElementsToRow method.

   The source code is available below.

   private XmlDocument UsrGetPreparedXml(UsrSingleRequest request) {
       XmlDocument xml = new XmlDocument();
       XmlElement elementRows = xml.CreateElement("rows");
       List<RequestCommunication> communicationsList = request.Communication && new List<RequestCommunication>();
       foreach (RequestCommunication communication in communicationsList) {
           XmlElement elementRow = xml.CreateElement("row");
           XmlElement elementCommunicationTypeId = xml.CreateElement("CommunicationTypeId");
           elementCommunicationTypeId.InnerText = communication.CommunicationTypeId.ToString();
           XmlElement elementNumber = xml.CreateElement("Number");
           elementNumber.InnerText = communication.Number;
           elementRow.AppendChild(elementCommunicationTypeId);
           elementRow.AppendChild(elementNumber);
           UsrAddElementsToRow(xml, elementRow, request);
           elementRows.AppendChild(elementRow);
       }
       if (communicationsList.Count < 1) {
           XmlElement elementRow = xml.CreateElement("row");
           UsrAddElementsToRow(xml, elementRow, request);
           elementRows.AppendChild(elementRow);
       }
       xml.AppendChild(elementRows);
       return xml;
   }
   
   private void UsrAddElementsToRow(XmlDocument xml, XmlElement elementRow, UsrSingleRequest request) {
       XmlElement elementName = xml.CreateElement("Name");
       ele.InnerText = request.Name;
       elementRow.AppendChild(elementName);
       XmlElement elementUsrINN = xml.CreateElement("UsrINN");
       elementUsrINN.InnerText = request.UsrINN;
       elementRow.AppendChild(elementUsrINN);
       if (request Id != Guid Empty) {
           XmlElement elementId = xml.CreateElement("Id");
           elementId.InnerText = request.Id.ToString();
           elementRow.AppendChild(elementId);
       }
   }
   

4. Override the FindDuplicates method.
5. Replace GetPreparedXml with UsrGetPreparedXml.
6. Cast the data parameter passed to the UsrGetPreparedXml to the UsrSingleRequest type.
7. In the UsrAddElementsToRow and UsrGetPreparedXml methods, change the type of the request parameter from SingleRequest to UsrSingleRequest.

   The source code is available below.

   public override List<Guid> FindDuplicates(string schemaName, SingleRequest data) {
       XmlDocument xml = UsrGetPreparedXml((UsrSingleRequest)data);
       return FindDuplicates(schemaName, xml);
   }
   

8. Add a parameterized constructor and call the basic implementation of the class constructor.

5. Compile and restart the application

After you complete steps 1 through 4, compile and restart the application.

6. Create a custom service

Create a custom service to replace DeduplicationService calls when saving accounts. Take the following steps in the created service:

1. Copy the FindDuplicatesOnSave method from the DeduplicationService schema.
2. Substitute DeduplicationProcessing with UsrDeduplicationProcessing.

   The source code is available below.

   namespace Terrasoft.Configuration
   {
       using System;
       using System.Collections.Generic;
       using System.Collections.ObjectModel;
       using System.Data;
       using System.Linq;
       using System.Runtime.Serialization;
       using System.Xml;
       using Terrasoft.Common;
       using Terrasoft.Configuration.RightsService;
       using Terrasoft.Core;
       using Terrasoft.Core.DB;
       using Terrasoft.Core.Entities;
       using Terrasoft.Core.Factories;
       using Terrasoft.Core.Scheduler;
       using Terrasoft.Nui.ServiceModel.Extensions;
       using Terrasoft.Configuration.SearchDuplicatesService;
       using EntityCollection = Terrasoft.Nui.ServiceModel.DataContract.EntityCollection;
       
       public class UsrDeduplicationProcessing: DeduplicationProcessing
       {
           public UsrDeduplicationProcessing(UserConnection userConnection): base(userConnection)
           {
               
           }
   

7. Replace the getDuplicatesServiceName and getFindDuplicatesServiceMethodNamе methods

The AccountPageV2 schema of the Deduplication package contains the getDuplicatesServiceName and getFindDuplicatesServiceMethodName methods. Replace the methods and pass to them the name of the custom service and the name of the FindDuplicatesOnSave method of your custom service.

8. Delete stored procedures from the database

Delete all stored deduplication procedures from the database (their names all start with Find).

9. Execute the script for the deletion of the CreatingObjectInfo type

Execute the script for the deletion of the CreatingObjectInfo type, which is created by tsp_FindDuplicate and is used in all stored deduplication procedures. To do so, execute the following SQL script:

IF TYPE_ID('[dbo].[CreatingObjectInfo]') IS NOT NULL
BEGIN
    drop type [CreatingObjectInfo];
END

10. Create a tsp_FindDuplicate stored procedure

Create a custom tsp_FindDuplicate stored procedure Take the following steps in the created procedure:

1. Create a CreatingObjectInfo type and add UsrINN to the new type. To do so, execute the following SQL script:

   ]IF TYPE_ID('[dbo].[CreatingObjectInfo]') IS NULL
   ]BEGIN
   ]    CREATE TYPE CreatingObjectInfo AS TABLE (
           Id NVARCHAR(36),
           ObjectModifiedOn NVARCHAR(128),
           CommunicationTypeId NVARCHAR(36),
           CityId NVARCHAR(36),
           Name NVARCHAR(128),
           UsrINN NVARCHAR(50),
           Number NVARCHAR(250),
           SearchNumber NVARCHAR(250),
           Web NVARCHAR(250)
       );
   END;
   GO    
   

2. Add UsrINN to @parsedConfig. To do so, execute the following SQL script:

   IF @xmlRows <> ''
   BEGIN
       SET @minimumGroupCount = 0;
       SET @xmlRowsConfig = CAST(@xmlRows AS XML);
       INSERT INTO @parsedConfig
       SELECT
           NULLIF(b.value('(./Id/text())[1]', 'NVARCHAR(36)'), NEWID()) AS [Id],
           NULLIF(b.value('(./ContactModifiedOn/text())[1]', 'NVARCHAR(128)'), AS [ObjectModifiedOn],
           NULLIF(b.value('(./CommunicationTypeId/text())[1]', 'NVARCHAR(36)'), AS [CommunicationTypeId],
           NULL AS [CityId],
           NULLIF(b.value('(./Name/text())[1]', 'NVARCHAR(128)'), AS [Name],
           NULLIF(b.value('(./UsrINN/text())[1]', 'NVARCHAR(50)'), AS [UsrINN],
           [dbo].[fn_NormalizeString](NULLIF(b.value('(./Number/text())[1]', 'NVARCHAR(250)'), ''),N'0-9a-za-я@_.') AS [Number],
           [dbo].[fn_ExtractDigitLimitFromNumber](LTRIM(
               [dbo].[fn_GetPhoneNumberSearchForm](NULLIF(b.value('(./Number/text())[1]', 'NVARCHAR(250)'), ''))
           )) AS [SearchNumber],
           NULLIF([dbo].[fn_ExtractDomainFromUrl](b.value('((./Number/text())[1]', 'NVARCHAR(250)')), '') AS [Web]
       FROM @xmlRowsConfig.nodes('/rows/row') as a(b);
       SET @processingRowId = (SELECT TOP 1 Id FROM @parsedConfig);
   END;        
   

3. Install tsp_FindDuplicate in the database.

11. Execute the installation process for remote stored procedures

Execute the database installation process for all stored procedures that have been removed from the database as described in step 9.

12. Add the tsp_FindAccountDuplicateByInn stored procedure

Add the user-stored tsp_FindAccountDuplicateByInn procedure and complete the procedure with an ELSE block when adding records to #searchAccount. To do so, execute the following SQL script:

IF @parsedConfigRowsCount = 0
BEGIN
    INSERT INTO #searchAccount ([Name], [SortDate])
    SELECT
        [dedup].[Name],
        MAX([dedup].[SortDate]) [SortDate]
    FROM (
        SELECT [Id]
            [dbo].[fn_NormalizeString]([Name], @validChar) AS [Name],
            MAX([ModifiedOn]) [SortDate]
        FROM [VmAccountCleanDataValues] WITH (NOEXPAND)
        GROUP BY [Id], [Name]
    ) AS [dedup]
    GROUP BY [dedup], [Name]
    HAVING COUNT(*) > 1;
END;
ELSE
BEGIN
    INSERT INTO #searchAccount ([Name], [SortDate])
    SELECT
        [dbo].[fn_NormalizeString]([Name], @validChar) AS [Name],
        GETDATE() AS [SortDate]
    FROM @parsedConfig
END;

Install tsp_FindAccountDuplicateByInn in the database.