Вопрос

Создание нового правила дедубликации

26 января 2018 16:36

Кейс следующий: сделать правило дедубликации в разделе лиды по похожим контрагентам с проверкой ТОЛЬКО по телефону. Базовое правило не подходит, поскольку проверяет по названию И телефону.

Добавил правило в DuplicatesRule, добавил процедуру поиска с помощью SQL запроса (см. AccountPhoneRuleUsr). Ничего не выходит. Что интересно, если убрать из базового правила (phoneAndAccountNameRule) упоминание поиска по названию, то он тоже перестаёт отрабатывать в принципе. Такое ощущение, что где-то прописано, что поиск по телефону работает только в паре с каким-то совпадением.

Кто-нибудь сталкивался с данной задачкой?

IF(OBJECT_ID('dbo.tsp_FindAccountSimilarRecords','P')) IS NOT NULL
	DROP PROCEDURE dbo.tsp_FindAccountSimilarRecords
GO
 
CREATE PROCEDURE tsp_FindAccountSimilarRecords (@primaryColumnValue uniqueidentifier, @rawXMLConfig nvarchar(MAX), @offsetLimit int = 200)
AS 
 
 
DECLARE @parsedConfig TABLE (
	SchemaName nvarchar(128),
	ParentShemaName nvarchar(128) NULL,
	ColumnName nvarchar(128),
	ColumnValue nvarchar(4000),
	ColumnValueTypeId nvarchar(128)
 );
 
DECLARE @accountRulesConfig TABLE (
	RuleId uniqueidentifier, 
	SchemaName sysname,
	ParentSchemaName sysname NULL,
	ColumnName sysname, 
	NormalizeFnPattern nvarchar(4000),
	CleanDataSchemaName sysname NULL);
 
DECLARE @xmlBody xml;
 
DECLARE @webAndAccountNameRule uniqueidentifier = 'd3607fd4-8303-40ae-b66e-000000000013'	,
		@phoneAndAccountNameRule uniqueidentifier = 'd3607fd4-8303-40ae-b66e-000000000014'	,
		@cityAndAccountNameRule uniqueidentifier = 'd3607fd4-8303-40ae-b66e-000000000015'	,
		@countryAndAccountNameRule uniqueidentifier = 'd3607fd4-8303-40ae-b66e-000000000016'	,
		@AccountNameRule uniqueidentifier = 'd3607fd4-8303-40ae-b66e-000000000017'	,
		@AccountPhoneRuleUsr uniqueidentifier = '778e768d-3941-4461-8954-59702c1f1fab'	,
		@AccountInnRule uniqueidentifier = '1470da34-31ae-4da5-b6c8-9410751133a4'	;
 
DECLARE @leadSchemaUId uniqueidentifier = '41AF89E9-750B-4EBB-8CAC-FF39B64841EC' ;
 
DECLARE @accountSchemaName sysname = 'Account',
		@accountCleanDataSchemaName sysname = 'VwAccountCleanDataValues',
		@accountCommunicationSchemaName sysname = 'AccountCommunication',
		@accountCommunicationCleanDataSchemaName sysname = 'VwAccountCommunicationCleanDataValues',
		@accountAddressSchemaName sysname = 'AccountAddress';
 
DECLARE @accountNameColumn sysname = 'Name',
		@accountInnColumn sysname = 'UsrINN',
		@accountCommunicationColumn sysname = 'Number',
		@accountCommunicationPhoneColumn sysname = 'SearchNumber',
		@accountCountryColumn sysname = 'CountryId',
		@accountCityColumn sysname = 'CityId',
		@communicationTypeIdColumn sysname = 'CommunicationTypeId';
 
DECLARE @resultIdsTable TABLE (Id uniqueidentifier);
 
DECLARE @phoneCommunicationTypeTable TABLE (Id uniqueidentifier);
 
DECLARE @preStmtValues TABLE (
	ColumnName sysname NOT NULL,
	ColumnValue nvarchar (4000) NULL,
	SchemaName sysname NOT NULL,
	ColumnValueTypeId NVARCHAR(128)
);
 
 
DECLARE @emailNormalizePattern nvarchar(50) = N'0-9a-zа-я@_.',
		@nameNormalizePattern nvarchar(50) = N'0-9a-zа-я',
		@normalizedFnSelectStmt nvarchar(250),
		@innNormalizePattern nvarchar(12) = N'0-9',
		@normalizedFnValueStmt nvarchar(250);
 
DECLARE @execStmt nvarchar(MAX),
		@baseNormalizeFn sysname = 'dbo.fn_NormalizeString',
		@phoneNormalizeFn sysname = 'dbo.fn_ExtractDigitLimitFromNumber',
		@webNormalizeFn sysname = 'dbo.fn_ExtractDomainFromUrl';
 
DECLARE @ruleId uniqueidentifier,
		@countDetailValues int = 0;
 
DECLARE @inQueryConditions nvarchar(MAX),
		@equalQueryConditions nvarchar(MAX),
		@schemaName sysname,
		@queryColumnName nvarchar(128);
 
BEGIN
 
 
	SET @xmlBody  = CAST(@rawXMLConfig AS XML);
 
	INSERT INTO @phoneCommunicationTypeTable
	VALUES('0DA6A26B-D7BC-DF11-B00F-001D60E938C6') ,
		('3DDDB3CC-53EE-49C4-A71F-E9E257F59E49') ,
		('D4A2DC80-30CA-DF11-9B2A-001D60E938C6') ,
		('2B387201-67CC-DF11-9B2A-001D60E938C6') ,
		('6A3FB10C-67CC-DF11-9B2A-001D60E938C6') ,
		('E9D91E45-8D92-4E38-95A0-EF8AA28C9E7A') 
 
 
	INSERT INTO @accountRulesConfig (RuleId, SchemaName, ParentSchemaName, ColumnName, NormalizeFnPattern, CleanDataSchemaName)
	VALUES 	(@webAndAccountNameRule, @accountCommunicationSchemaName, @accountSchemaName, @accountCommunicationColumn,  @webNormalizeFn + '(N##VALUE##)', @accountCommunicationCleanDataSchemaName),
			(@webAndAccountNameRule, @accountSchemaName, NULL, @accountNameColumn,  @baseNormalizeFn + '(N##VALUE##,N''' + @nameNormalizePattern + ''')',  @accountCleanDataSchemaName),
			(@phoneAndAccountNameRule, @accountCommunicationSchemaName, @accountSchemaName, @accountCommunicationPhoneColumn,  @phoneNormalizeFn + '(##VALUE##)', @accountCommunicationCleanDataSchemaName),
			(@phoneAndAccountNameRule, @accountSchemaName, NULL, @accountNameColumn,  @baseNormalizeFn + '(N##VALUE##,N''' + @nameNormalizePattern + ''')', @accountCleanDataSchemaName),
			(@cityAndAccountNameRule, @accountAddressSchemaName, @accountSchemaName, @accountCityColumn, NULL, NULL),
			(@cityAndAccountNameRule, @accountSchemaName, NULL, @accountNameColumn, @baseNormalizeFn + '(N##VALUE##,N''' + @nameNormalizePattern + ''')', @accountCleanDataSchemaName),
			(@countryAndAccountNameRule, @accountAddressSchemaName, @accountSchemaName, @accountCountryColumn, NULL,NULL),
			(@countryAndAccountNameRule, @accountSchemaName, NULL, @accountNameColumn, @baseNormalizeFn + '(N##VALUE##,N''' + @nameNormalizePattern + ''')', @accountCleanDataSchemaName),
			(@AccountInnRule, @accountSchemaName, NULL, @accountInnColumn, @baseNormalizeFn + '(N##VALUE##,N''' + @innNormalizePattern + ''')', NULL),
			(@AccountPhoneRuleUsr, @accountCommunicationSchemaName, @accountSchemaName, @accountCommunicationPhoneColumn,  @phoneNormalizeFn + '(##VALUE##)', NULL),
			(@AccountNameRule, @accountSchemaName, NULL, @accountNameColumn, @baseNormalizeFn + '(N##VALUE##,N''' + @nameNormalizePattern + ''')', @accountCleanDataSchemaName);
 
	INSERT INTO @parsedConfig
	SELECT NULLIF(p.value('(./schemaName)[1]', 'VARCHAR(128)'),'') AS SchemaName,
		NULLIF(p.value('(./parentSchemaName)[1]', 'VARCHAR(128)'),'') AS ParentSchemaName,
		NULLIF(p.value('(./columnName)[1]', 'NVARCHAR(128)'),'') AS ColumnName,
		NULLIF(p.value('(./columnValue)[1]', 'NVARCHAR(4000)'),'') AS ColumnValue,
		NULLIF(p.value('(./typeId)[1]', 'NVARCHAR(128)'),'') AS ColumnValueTypeId
		FROM @xmlBody.nodes('/columns/item') t(p)
 
	UPDATE	@parsedConfig 
	SET ColumnValue = dbo.fn_GetPhoneNumberSearchForm(ColumnValue)
	WHERE ColumnName = @accountCommunicationPhoneColumn
 
	DECLARE activeRuleList CURSOR
	FOR SELECT Id from DuplicatesRule
		WHERE ObjectId = @leadSchemaUId
		AND Id IN (SELECT RuleId FROM @accountRulesConfig)
		AND isActive = 1;
 
	OPEN activeRuleList
	FETCH NEXT
		FROM activeRuleList INTO @ruleId 
 
	WHILE @@FETCH_STATUS = 0
	BEGIN
 
		SET @execStmt = NULL;
		SET @countDetailValues = 0;
		SET @equalQueryConditions = NULL;
		SET @inQueryConditions = NULL;
 
 
		IF EXISTS (SELECT NULL
			FROM @accountRulesConfig config
			LEFT JOIN @parsedConfig parsed
			ON config.ColumnName = parsed.ColumnName
			AND config.SchemaName = parsed.SchemaName
			WHERE parsed.ColumnName IS NULL 
			AND config.RuleId = @ruleId)
		BEGIN 
			FETCH NEXT
			FROM activeRuleList INTO @ruleId
			CONTINUE
		END
		ELSE
		BEGIN	
			IF EXISTS (
				SELECT NULL
					FROM @parsedConfig parsed, @accountRulesConfig config
					WHERE parsed.ColumnName =config.ColumnName
					AND parsed.SchemaName = config.SchemaName
					AND config.ParentSchemaName IS NULL
					AND parsed.ColumnValue IS NULL
					AND config.RuleId = @ruleId)
			BEGIN
				FETCH NEXT
					FROM activeRuleList INTO @ruleId
				CONTINUE
			END
			ELSE
			BEGIN
				DELETE FROM @preStmtValues
				INSERT INTO @preStmtValues (ColumnName, ColumnValue, SchemaName)
				SELECT ISNULL(rulesCfg.CleanDataSchemaName, rulesCfg.SchemaName)+'.'+rulesCfg.ColumnName,
						CASE WHEN NormalizeFnPattern IS NOT NULL THEN 
							REPLACE(NormalizeFnPattern, '##VALUE##', QUOTENAME(parsedCfg.ColumnValue, ''''))
						ELSE QUOTENAME(parsedCfg.ColumnValue, '''') END ColumnValue, 
						ISNULL(rulesCfg.CleanDataSchemaName, rulesCfg.SchemaName)
					FROM @accountRulesConfig rulesCfg, 
						 @parsedConfig parsedCfg
					WHERE rulesCfg.ColumnName = parsedCfg.ColumnName
					AND rulesCfg.SchemaName = parsedCfg.SchemaName
					AND rulesCfg.ParentSchemaName IS NULL
					AND RuleId = @ruleId
 
				SET @queryColumnName = 'Id';
 
				SELECT TOP 1 @schemaName = SchemaName FROM @preStmtValues 
 
				SELECT DISTINCT @inQueryConditions = 
					' AND ' + ColumnName + ' IN (' + STUFF((SELECT ',' + ColumnValue FROM @preStmtValues f 
					WHERE f.ColumnName = t.ColumnName AND f.ColumnName IN (
						SELECT t.ColumnName 
							FROM @preStmtValues t 
						GROUP BY ColumnName HAVING COUNT(*) > 1)
					FOR XML PATH(''), TYPE).value('(./text())[1]','nvarchar(max)'), 1,1, '') +')' FROM @preStmtValues t
 
				SELECT DISTINCT @equalQueryConditions = 
					STUFF((SELECT ' AND ' 
							+ ColumnName + ' LIKE ' 
							+ ColumnValue FROM @preStmtValues f
					WHERE f.ColumnName IN (
						SELECT t.ColumnName 
							FROM @preStmtValues t 
						GROUP BY ColumnName HAVING COUNT(*) = 1) 
					FOR XML PATH(''), TYPE).value('(./text())[1]','nvarchar(max)'), 1,1, '')
 
				SET @execStmt = 
					N'SELECT TOP '+CAST(@offsetLimit as NVARCHAR)+' '+@queryColumnName + CHAR(10) + 
					'FROM '+ CHAR(10) +
					@schemaName + CHAR(10) +
					'WITH (NOLOCK) ' + CHAR(10) +
					'WHERE 1=1'+ CHAR(10);
 
				IF @equalQueryConditions IS NOT NULL
				BEGIN
					SET @execStmt = @execStmt + CHAR(10) + 
					@equalQueryConditions
				END
 
				IF @inQueryConditions IS NOT NULL
				BEGIN
					SET @execStmt = @execStmt + CHAR(10) + 
					@inQueryConditions
				END
 
 
				IF EXISTS (SELECT NULL FROM @accountRulesConfig 
								WHERE RuleId = @ruleId
								AND ParentSchemaName IS NOT NULL)
				BEGIN
					IF EXISTS (
						SELECT NULL
						 FROM @parsedConfig parsed, @accountRulesConfig config
							WHERE parsed.ColumnName =config.ColumnName
								AND parsed.SchemaName = config.SchemaName
								AND parsed.ParentShemaName = config.ParentSchemaName
								AND parsed.ColumnValue IS NOT NULL
								AND config.RuleId = @ruleId)
					BEGIN
 
						DELETE FROM @preStmtValues
						INSERT INTO @preStmtValues (ColumnName, ColumnValue, SchemaName, ColumnValueTypeId)
						SELECT ISNULL(rulesCfg.CleanDataSchemaName, rulesCfg.SchemaName)+'.'+rulesCfg.ColumnName, 
							CASE WHEN NormalizeFnPattern IS NOT NULL THEN 
							REPLACE(NormalizeFnPattern, '##VALUE##', QUOTENAME(parsedCfg.ColumnValue, ''''))
							ELSE QUOTENAME(parsedCfg.ColumnValue, '''') END ColumnValue, 
							ISNULL(rulesCfg.CleanDataSchemaName, rulesCfg.SchemaName),
							ColumnValueTypeId
							FROM @accountRulesConfig rulesCfg, 
								 @parsedConfig parsedCfg
							WHERE rulesCfg.ColumnName = parsedCfg.ColumnName
							AND rulesCfg.ParentSchemaName = parsedCfg.ParentShemaName
							AND rulesCfg.SchemaName = parsedCfg.SchemaName
							AND RuleId = @ruleId
 
 
						SET @queryColumnName = 'AccountId';
 
						IF EXISTS (
							SELECT NULL 
								FROM @phoneCommunicationTypeTable 
								WHERE Id in (SELECT ColumnValueTypeId FROM @preStmtValues f))
						BEGIN 
							INSERT INTO @preStmtValues (ColumnName, ColumnValue, SchemaName)
							SELECT @communicationTypeIdColumn, 
							'''' + CAST(Id AS NVARCHAR(50)) + '''', 
							(SELECT TOP 1 SchemaName FROM @preStmtValues)
							FROM @phoneCommunicationTypeTable
						END
						ELSE 
						BEGIN
							INSERT INTO @preStmtValues (ColumnName, ColumnValue, SchemaName)
							SELECT @communicationTypeIdColumn, 
							'''' + ColumnValueTypeId + '''',
							SchemaName FROM @preStmtValues
							WHERE ColumnValueTypeId IS NOT NULL
						END	
 
						SELECT TOP 1 @schemaName = SchemaName FROM @preStmtValues 
 
						SELECT DISTINCT @inQueryConditions = 
							' AND ' + ColumnName + ' IN (' + STUFF((SELECT ',' + ColumnValue FROM @preStmtValues f 
							WHERE f.ColumnName = t.ColumnName AND f.ColumnName IN (
								SELECT t.ColumnName 
									FROM @preStmtValues t 
								GROUP BY ColumnName HAVING COUNT(*) > 1)
							FOR XML PATH(''), TYPE).value('(./text())[1]','nvarchar(max)'), 1,1, '') +')' FROM @preStmtValues t
 
						SELECT DISTINCT @equalQueryConditions = 
							STUFF((SELECT ' AND ' 
									+ ColumnName + ' LIKE ' 
									+ ColumnValue FROM @preStmtValues f
							WHERE f.ColumnName IN (
								SELECT t.ColumnName 
									FROM @preStmtValues t 
								GROUP BY ColumnName HAVING COUNT(*) = 1) 
							FOR XML PATH(''), TYPE).value('(./text())[1]','nvarchar(max)'), 1,1, '')
 
						IF @execStmt IS NOT NULL
						BEGIN
							SET @execStmt = 
							N'SELECT TOP '+CAST(@offsetLimit as NVARCHAR)+' '+@queryColumnName + CHAR(10) + 
							'FROM ' + CHAR(10) + 
							@schemaName + CHAR(10) +
							'WITH (NOLOCK) ' + CHAR(10) +
							'WHERE '+ @queryColumnName + CHAR(10) + 
							' IN ('	+ @execStmt + ')';
 
							IF @equalQueryConditions IS NOT NULL
							BEGIN
								SET @execStmt = @execStmt + CHAR(10) + 
								@equalQueryConditions;
							END	
 
							IF @inQueryConditions IS NOT NULL
							BEGIN
								SET @execStmt = @execStmt + CHAR(10) + 
								@inQueryConditions;
							END	
 
							INSERT INTO @resultIdsTable
							EXEC sp_executesql @execStmt;
						END
					END
				END
				ELSE
				BEGIN
					INSERT INTO @resultIdsTable
					EXEC sp_executesql @execStmt;
				END
			END
		END
 
	FETCH NEXT
		FROM activeRuleList INTO @ruleId
	END
	CLOSE activeRuleList
	DEALLOCATE activeRuleList
 
	DELETE
		FROM @resultIdsTable
		WHERE Id = @primaryColumnValue
 
	SELECT DISTINCT TOP (@offsetLimit) Id 
		FROM @resultIdsTable
 
END

Нравится

3 комментария

Москвитина Анастасия

26 января 2018 17:45

Здравствуйте, Данил!

Кастомизация правил поиска похожих лидов осуществляется по инструкции ниже.

1. Необходимо добавить скриптом новое правило в таблицу DuplicatesRule.

Пример скрипта:

insert into DuplicatesRule

(Name, IsActive, ObjectId)

values

('Имя правила', 1, '41AF89E9-750B-4EBB-8CAC-FF39B64841EC')

2. Заместить хранимую процедуру tsp_FindAccountSimilarRecordsMSSQL (для примера поиска по контрагентам, по контактам соответственно tsp_FindContactSimilarRecordsMSSQL). Для этого необходимо скопировать весь код из tsp_FindAccountSimilarRecordsMSSQL и дублировать его в новый SQL Script.

3. Объявить своё правилj в блоке с остальными правилами, где указать id добавленного правила с пункта 1 (из таблицы DuplicatesRule) http://prntscr.com/e2m5wz

4. Аналогично существующим правилам, добавить и своё в @accountRulesConfig http://prntscr.com/e2m72y

Например, если правило только для Телефона, копируем выделенную строчку на скриншоте и заменяем название правила на кастомное: http://prntscr.com/i5yogj 

5. Сохранить хранимую процедуру и выполнить установку в БД.

Если привязаться к Вашему коду, то ошибка вероятнее всего в п. 4. , т.к. правило определено неправильно: http://prntscr.com/i5yppg

Приятной работы

Ответить

Данил С

26 января 2018 17:53

Пробовал и полностью скопировав это правило (если вы про отсутствие @accountCleanDataSchemaName в конце) - не работает.

Что интересно, если я прописываю правило с поиском по ИНН+Телефон, то поиск отрабатывает корректно - проверяет совпадение в обоих полях. Но как только пробую оставить ТОЛЬКО по телефону, то такая дедубликация не работает.

Ответить

Demchenko Olha

7 февраля 2018 16:07

Здравствуйте!

Правила поиска дублей для похожих контрагентов/контактов в лиде сделаны таким образом, что нет возможности обращаться отдельно к таблице средств связи, по которой и происходит поиск только по телефону. Логика хранимых процедур была написана с учётом базовых правил, которые предполагают поиск похожих контактов/контрагентов только по правилам, которые включают поиск в таблице Account/Contact плюс таблица средств связи. А обращение только к таблице средств связи в текущей реализации хранимой процедуры не предусмотрено.

Для работы своего правила только по телефону или любого другого средства связи, необходимо внести изменения в хранимую процедуру tsp_FindAccountSimilarRecords, а именно вместо блока "IF @execStmt IS NOT NULL..."  (http://prntscr.com/ibe672) добавить код ниже:

SET @detailsSelectQuery = N'SELECT TOP '+ CAST(@offsetLimit as NVARCHAR) +

               ' ' + @queryColumnName + CHAR(10) +

               'FROM ' + CHAR(10) + @schemaName + CHAR(10) +

               'WITH (NOLOCK) ' + CHAR(10) +

               'WHERE 1=1 ';

               IF @execStmt IS NOT NULL

               BEGIN

                   SET @detailsSelectQuery =

                   @detailsSelectQuery + CHAR(10) + 'AND' + CHAR(10) + @queryColumnName + CHAR(10) + ' IN ('   + @execStmt + ')';

               END

               IF @equalQueryConditions IS NOT NULL

               BEGIN

                   SET @detailsSelectQuery = @detailsSelectQuery + CHAR(10) +

                   @equalQueryConditions;

               END

               IF @inQueryConditions IS NOT NULL

               BEGIN

                   SET @detailsSelectQuery = @detailsSelectQuery + CHAR(10) + @inQueryConditions;

               END

               IF @detailsSelectQuery IS NOT NULL

               BEGIN

                   INSERT INTO @resultIdsTable

                   EXEC sp_executesql @detailsSelectQuery;

               END

Ответить

Показать все комментарии