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