Кейс следующий: сделать правило дедубликации в разделе лиды по похожим контрагентам с проверкой ТОЛЬКО по телефону. Базовое правило не подходит, поскольку проверяет по названию И телефону.
Добавил правило в 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
Нравится
Здравствуйте, Данил!
Кастомизация правил поиска похожих лидов осуществляется по инструкции ниже.
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
Приятной работы
Пробовал и полностью скопировав это правило (если вы про отсутствие @accountCleanDataSchemaName в конце) - не работает.
Что интересно, если я прописываю правило с поиском по ИНН+Телефон, то поиск отрабатывает корректно - проверяет совпадение в обоих полях. Но как только пробую оставить ТОЛЬКО по телефону, то такая дедубликация не работает.
Здравствуйте!
Правила поиска дублей для похожих контрагентов/контактов в лиде сделаны таким образом, что нет возможности обращаться отдельно к таблице средств связи, по которой и происходит поиск только по телефону. Логика хранимых процедур была написана с учётом базовых правил, которые предполагают поиск похожих контактов/контрагентов только по правилам, которые включают поиск в таблице 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