USP_MKTSEGMENTATIONACTIVATE_MERGECONSTITUENTS
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @SEGMENTATIONID | uniqueidentifier | IN | |
| @SEGMENTATIONEXCLUSIONID | uniqueidentifier | IN |
Definition
Copy
CREATE procedure dbo.[USP_MKTSEGMENTATIONACTIVATE_MERGECONSTITUENTS]
(
@SEGMENTATIONID uniqueidentifier,
@SEGMENTATIONEXCLUSIONID uniqueidentifier = null
)
as
set nocount on;
declare @ISBBEC bit;
declare @MAILINGTYPECODE tinyint;
declare @SEGMENTATIONISHISTORICAL bit;
declare @LASTACTIVITYDATE datetime;
declare @TABLENAME nvarchar(128);
declare @BASETABLENAME nvarchar(128);
declare @BASEPRIMARYKEYFIELD nvarchar(128);
declare @IDFIELD nvarchar(128);
declare @DONORIDFIELD nvarchar(128);
declare @EXTRAJOINCONDITION nvarchar(max);
declare @SQL nvarchar(max);
begin try
select
@SEGMENTATIONISHISTORICAL = [ISHISTORICAL],
@ISBBEC = (case when dbo.[UFN_INSTALLEDPRODUCTS_PRODUCTIS]('BB9873D7-F1ED-430A-8AB4-F09F47056538') = 0 then 1 else 0 end)
from dbo.[MKTSEGMENTATION] where [ID] = @SEGMENTATIONID;
--Make sure we only run this for BBEC databases. Also, if we are running this to undo a remove members process, make sure the process ID is valid and the mailing is activated.
if @ISBBEC = 1 and @SEGMENTATIONISHISTORICAL = 0 and (@SEGMENTATIONEXCLUSIONID is null or (@SEGMENTATIONEXCLUSIONID is not null and exists(select * from dbo.[MKTSEGMENTATIONEXCLUSION] where [ID] = @SEGMENTATIONEXCLUSIONID) and dbo.[UFN_MKTSEGMENTATION_ISACTIVE](@SEGMENTATIONID) = 1))
begin
if @SEGMENTATIONEXCLUSIONID is null
--Running activate right now which means that segment counts were done in the past by the segment counts process (either before activate or as part of activate).
select
@LASTACTIVITYDATE = max([BUSINESSPROCESSSTATUS].[ENDEDON])
from dbo.[MKTSEGMENTATIONSEGMENTCALCULATEPROCESS]
inner join dbo.[MKTSEGMENTATIONSEGMENTCALCULATEPROCESSSTATUS] on [MKTSEGMENTATIONSEGMENTCALCULATEPROCESSSTATUS].[PARAMETERSETID] = [MKTSEGMENTATIONSEGMENTCALCULATEPROCESS].[ID]
inner join dbo.[BUSINESSPROCESSSTATUS] on [BUSINESSPROCESSSTATUS].[ID] = [MKTSEGMENTATIONSEGMENTCALCULATEPROCESSSTATUS].[ID]
where [MKTSEGMENTATIONSEGMENTCALCULATEPROCESS].[SEGMENTATIONID] = @SEGMENTATIONID
and [BUSINESSPROCESSSTATUS].[STATUSCODE] = 0;
else
--Grab the date the remove members process was successfully run...
select
@LASTACTIVITYDATE = [BUSINESSPROCESSSTATUS].[ENDEDON]
from dbo.[MKTSEGMENTATIONEXCLUSION]
inner join dbo.[BUSINESSPROCESSSTATUS] on [BUSINESSPROCESSSTATUS].[ID] = [MKTSEGMENTATIONEXCLUSION].[STATUSID]
where [MKTSEGMENTATIONEXCLUSION].[ID] = @SEGMENTATIONEXCLUSIONID
and [MKTSEGMENTATIONEXCLUSION].[DELETEDQUANTITY] > 0
and [BUSINESSPROCESSSTATUS].[STATUSCODE] = 0;
if @LASTACTIVITYDATE is not null and exists(select * from dbo.[CONSTITUENTMERGEOPERATIONS] where [DATEADDED] >= @LASTACTIVITYDATE)
begin
--Find constituent merges that occurred after mailing segment counts, and translate any merged constituent IDs into their new IDs.
--For accuracy, this has to be done as a cursor so that we are updating the IDs in the same order they were actually merged.
--This cannot be done as a recursive CTE or any other way due to all the different ways you can merge constituents. Hopefully,
--there will only be a handful of merges to loop through at the most, and even then, this only happens in specific situations.
--
--Keep track of the very original constituent IDs from a mailing. If a constituent has already been merged once, then
--we don't want subsequent merges to overwrite the very original constituent ID, so we can check their findernumbers to
--see if it is the very original constituent ID or not (basically if the finder number already exists in the table, then
--we don't overwrite it). We only want to save the very original constituent ID that was in the mailing when it was activated.
--Also, update the mailing data table with the new merged constituent ID.
--
--For mailings that are not constituent based (acknowledgements, membership, sponsorship), we need to make sure the base-record
--was actually merged onto the new constituent or not. This can happen because the user can setup different "merge configurations"
--with different options and may choose to not merge some data. This would leave the base-record on the old constituent, and in
--this case it would not be appropriate to update the constituent ID in our mailing data table. Some merge tasks will delete the
--base record, so we need to use a "left" join and "is null" to account for those.
--
--The "insert" and "update" statements are really the only piece that needs to be dynamic SQL because they reference the mailing
--data table, but since the table is going to be same for each time through the loop, it is better if we don't have to recompile and
--execute a dynamic statement with each loop. Instead the whole cursor is dynamic so we only have one thing to compile.
--
--This exact same dynamic SQL is used in "Blackbaud.AppFx.Constituent.Catalog\ConstituentAppeals.MergeTask.xml". Please
--make sure both of these files stay consistent when modifications are made.
select
@TABLENAME = (case when @SEGMENTATIONEXCLUSIONID is null then dbo.[UFN_MKTSEGMENTATIONACTIVATE_MAKETABLENAME]([ID]) else dbo.[UFN_MKTSEGMENTATIONACTIVATEEXCLUSION_MAKETABLENAME]([ID]) end),
@MAILINGTYPECODE = [MAILINGTYPECODE]
from dbo.[MKTSEGMENTATION]
where [ID] = @SEGMENTATIONID;
if @MAILINGTYPECODE in (1, 5) --Acknowledgement
begin
set @BASETABLENAME = 'FINANCIALTRANSACTION';
set @BASEPRIMARYKEYFIELD = 'ID';
set @IDFIELD = 'REVENUEID';
set @DONORIDFIELD = 'CONSTITUENTID';
set @EXTRAJOINCONDITION = ' and [FINANCIALTRANSACTION].[DELETEDON] is null';
end
else if @MAILINGTYPECODE = 2 --Membership
begin
set @BASETABLENAME = 'MEMBER';
set @BASEPRIMARYKEYFIELD = 'MEMBERSHIPID';
set @IDFIELD = 'MEMBERSHIPID';
set @DONORIDFIELD = 'CONSTITUENTID';
set @EXTRAJOINCONDITION = ' and [MEMBER].[ISPRIMARY] = 1 and [MEMBER].[ISDROPPED] = 0'
end
else if @MAILINGTYPECODE = 3 --Sponsorship
begin
set @BASETABLENAME = 'SPONSORSHIP';
set @BASEPRIMARYKEYFIELD = 'ID';
set @IDFIELD = 'SPONSORSHIPID';
set @DONORIDFIELD = 'CONSTITUENTID';
end
set @SQL = 'declare @SOURCEID uniqueidentifier;' + char(13) +
'declare @TARGETID uniqueidentifier;' + char(13) +
char(13) +
'declare MERGECURSOR cursor local fast_forward for' + char(13) +
' select' + char(13) +
' [SOURCEID],' + char(13) +
' [TARGETID]' + char(13) +
' from dbo.[CONSTITUENTMERGEOPERATIONS]' + char(13) +
' where [DATEADDED] >= @LASTACTIVITYDATE' + char(13) +
' order by [DATEADDED], [TSLONG];' + char(13) +
char(13) +
'open MERGECURSOR;' + char(13) +
'fetch next from MERGECURSOR into @SOURCEID, @TARGETID;' + char(13) +
char(13) +
'while (@@fetch_status = 0)' + char(13) +
'begin' + char(13) +
' --Keep track of the original constituent ID...' + char(13) +
' insert into dbo.[MKTSEGMENTATIONMERGEDORIGINALCONSTITUENTS] ([SEGMENTATIONID], [FINDERNUMBER], [ORIGINALCONSTITUENTID])' + char(13) +
' select' + char(13) +
' @SEGMENTATIONID,' + char(13) +
' [DONORS].[FINDERNUMBER],' + char(13) +
' @SOURCEID' + char(13) +
' from dbo.[' + @TABLENAME + '] as [DONORS]' + char(13) +
' inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [DONORS].[SEGMENTID] and [MKTSEGMENTATIONSEGMENT].[EXCLUDE] = 0' + char(13);
if @BASETABLENAME is not null
--Some merge tasks will delete the base record, so we need to "left" join (instead of "inner" join) and add an "is null" to the "where" clause to take that into account...
set @SQL += ' left join dbo.[' + @BASETABLENAME + '] on [' + @BASETABLENAME + '].[' + @BASEPRIMARYKEYFIELD + '] = [DONORS].[' + @IDFIELD + ']' + isnull(@EXTRAJOINCONDITION, '') + char(13);
set @SQL += ' where [DONORS].[DONORQUERYVIEWCATALOGID] = ''DFB4B8C1-5E9A-4C14-ACE3-01C096B53BA0''' + char(13) +
' and [DONORS].[DONORID] = @SOURCEID' + char(13);
if @SEGMENTATIONEXCLUSIONID is not null
set @SQL += ' and [DONORS].[SEGMENTATIONEXCLUSIONID] = @SEGMENTATIONEXCLUSIONID' + char(13);
if @BASETABLENAME is not null
set @SQL += ' and ([' + @BASETABLENAME + '].[' + @DONORIDFIELD + '] is null or [' + @BASETABLENAME + '].[' + @DONORIDFIELD + '] <> @SOURCEID)' + char(13);
set @SQL += ' and not exists(select * from dbo.[MKTSEGMENTATIONMERGEDORIGINALCONSTITUENTS] where [FINDERNUMBER] = [DONORS].[FINDERNUMBER] and [SEGMENTATIONID] = @SEGMENTATIONID);' + char(13) +
char(13) +
' --Update the mailing data table...' + char(13) +
' update dbo.[' + @TABLENAME + '] set' + char(13) +
' [DONORID] = @TARGETID' + char(13) +
' from dbo.[' + @TABLENAME + '] as [DONORS]' + char(13);
if @BASETABLENAME is not null
--Some merge tasks will delete the base record, so we need to "left" join (instead of "inner" join) and add an "is null" to the "where" clause to take that into account...
set @SQL += ' left join dbo.[' + @BASETABLENAME + '] on [' + @BASETABLENAME + '].[' + @BASEPRIMARYKEYFIELD + '] = [DONORS].[' + @IDFIELD + ']' + isnull(@EXTRAJOINCONDITION, '') + char(13);
set @SQL += ' where [DONORS].[DONORQUERYVIEWCATALOGID] = ''DFB4B8C1-5E9A-4C14-ACE3-01C096B53BA0''' + char(13) +
' and [DONORS].[DONORID] = @SOURCEID';
if @SEGMENTATIONEXCLUSIONID is not null
set @SQL += char(13) +
' and [DONORS].[SEGMENTATIONEXCLUSIONID] = @SEGMENTATIONEXCLUSIONID';
if @BASETABLENAME is not null
set @SQL += char(13) +
' and ([' + @BASETABLENAME + '].[' + @DONORIDFIELD + '] is null or [' + @BASETABLENAME + '].[' + @DONORIDFIELD + '] <> @SOURCEID)';
set @SQL += ';' + char(13);
--If we are undoing a remove members process, we need to also merge the SAR exclusions table, so that it gets put back into the real SAR table with the correct merged constituent IDs...
if @SEGMENTATIONEXCLUSIONID is not null and exists(select * from INFORMATION_SCHEMA.TABLES where [TABLE_SCHEMA] = 'dbo' and [TABLE_NAME] = 'MKTSOURCEANALYSISRULEDATA_DFB4B8C1_5E9A_4C14_ACE3_01C096B53BA0_EXCLUSION')
begin
set @SQL += char(13) +
' --Update the SAR remove members table...' + char(13) +
' update dbo.[MKTSOURCEANALYSISRULEDATA_DFB4B8C1_5E9A_4C14_ACE3_01C096B53BA0_EXCLUSION] set' + char(13) +
' [DONORID] = @TARGETID' + char(13) +
' from dbo.[MKTSOURCEANALYSISRULEDATA_DFB4B8C1_5E9A_4C14_ACE3_01C096B53BA0_EXCLUSION] as [SAREX]' + char(13);
if @BASETABLENAME is not null
--Some merge tasks will delete the base record, so we need to "left" join (instead of "inner" join) and add an "is null" to the "where" clause to take that into account...
set @SQL += ' inner join dbo.[' + @TABLENAME + '] as [DONORS] on [DONORS].[FINDERNUMBER] = [SAREX].[FINDERNUMBER]' + char(13) +
' left join dbo.[' + @BASETABLENAME + '] on [' + @BASETABLENAME + '].[' + @BASEPRIMARYKEYFIELD + '] = [DONORS].[' + @IDFIELD + ']' + isnull(@EXTRAJOINCONDITION, '') + char(13);
set @SQL += ' where [SAREX].[SEGMENTATIONEXCLUSIONID] = @SEGMENTATIONEXCLUSIONID' + char(13) +
' and [SAREX].[DONORID] = @SOURCEID';
if @BASETABLENAME is not null
set @SQL += char(13) +
' and ([' + @BASETABLENAME + '].[' + @DONORIDFIELD + '] is null or [' + @BASETABLENAME + '].[' + @DONORIDFIELD + '] <> @SOURCEID)';
set @SQL += ';' + char(13);
end
set @SQL += char(13) +
' fetch next from MERGECURSOR into @SOURCEID, @TARGETID;' + char(13) +
'end' + char(13) +
char(13) +
'close MERGECURSOR;' + char(13) +
'deallocate MERGECURSOR;';
if @SEGMENTATIONEXCLUSIONID is null
exec sp_executesql @SQL, N'@LASTACTIVITYDATE datetime, @SEGMENTATIONID uniqueidentifier', @LASTACTIVITYDATE = @LASTACTIVITYDATE, @SEGMENTATIONID = @SEGMENTATIONID;
else
exec sp_executesql @SQL, N'@LASTACTIVITYDATE datetime, @SEGMENTATIONID uniqueidentifier, @SEGMENTATIONEXCLUSIONID uniqueidentifier', @LASTACTIVITYDATE = @LASTACTIVITYDATE, @SEGMENTATIONID = @SEGMENTATIONID, @SEGMENTATIONEXCLUSIONID = @SEGMENTATIONEXCLUSIONID;
end
end
end try
begin catch
exec dbo.[USP_RAISE_ERROR];
return 1;
end catch
return 0;