USP_MERGETASK_CONSTITUENTEMAIL
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @SOURCEID | uniqueidentifier | IN | |
| @TARGETID | uniqueidentifier | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @PRIMARYCRITERIA | int | IN | |
| @DELETEDUPES | bit | IN | |
| @INCLUDEPREFS | bit | IN | |
| @PREFCRITERIA | int | IN |
Definition
Copy
CREATE procedure dbo.USP_MERGETASK_CONSTITUENTEMAIL
(
@SOURCEID uniqueidentifier,
@TARGETID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier,
@PRIMARYCRITERIA int = 0,
@DELETEDUPES bit = 0,
@INCLUDEPREFS bit = 1,
@PREFCRITERIA int = 0
)
as
set nocount on;
declare @CHANGEDATE datetime = getdate();
--Store the primary email indicators for later access
declare @SOURCEPRIMARYID as uniqueidentifier;
select @SOURCEPRIMARYID = ID
from dbo.EMAILADDRESS
where CONSTITUENTID = @SOURCEID and ISPRIMARY = 1;
declare @TARGETPRIMARYID as uniqueidentifier;
select @TARGETPRIMARYID = ID
from dbo.EMAILADDRESS
where CONSTITUENTID = @TARGETID and ISPRIMARY = 1;
--Wipe the primary email indicators before the merge
--to keep from violating any constraints
update dbo.EMAILADDRESS
set ISPRIMARY = 0, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
where ID in (@SOURCEPRIMARYID, @TARGETPRIMARYID)
and RELATIONSHIPID is null;
declare @DUPLICATEEMAILS table (SOURCE_EMAILADDRESSID uniqueidentifier, TARGET_EMAILADDRESSID uniqueidentifier,
SOURCE_DONOTEMAIL bit, SOURCE_ISCONFIDENTIAL bit, SOURCE_DONOTEMAILREASONCODEID uniqueidentifier);
--move all of the source constit's email addresses over to
--the target constit
if @DELETEDUPES = 0
begin
update dbo.EMAILADDRESS
set CONSTITUENTID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
where CONSTITUENTID = @SOURCEID
and RELATIONSHIPID is null;
end
else
begin
insert into @DUPLICATEEMAILS
select
SOURCE.ID,
TARGET.ID,
SOURCE.DONOTEMAIL,
SOURCE.ISCONFIDENTIAL,
SOURCE.DONOTEMAILREASONCODEID
from
dbo.EMAILADDRESS SOURCE
inner join
dbo.EMAILADDRESS TARGET on TARGET.CONSTITUENTID = @TARGETID
where
SOURCE.CONSTITUENTID = @SOURCEID and
SOURCE.RELATIONSHIPID is null and
SOURCE.EMAILADDRESS = TARGET.EMAILADDRESS and
((SOURCE.EMAILADDRESSTYPECODEID = TARGET.EMAILADDRESSTYPECODEID) or (SOURCE.EMAILADDRESSTYPECODEID is null and TARGET.EMAILADDRESSTYPECODEID is null))
--Omit duplicate email addresses if the @DELETEDUPES
--flag is set
update dbo.EMAILADDRESS
set CONSTITUENTID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
where CONSTITUENTID = @SOURCEID
and RELATIONSHIPID is null
and ID not in (select SOURCE_EMAILADDRESSID from @DUPLICATEEMAILS)
--Update some specific items that wouldn't otherwise be carried over on duplicate emails
update TARGET set
DONOTEMAIL =
case
when TARGET.DONOTEMAIL = 1
then 1
when exists(select 1 from @DUPLICATEEMAILS DUPE where DUPE.TARGET_EMAILADDRESSID = TARGET.ID and DUPE.SOURCE_DONOTEMAIL = 1)
then 1
else
0
end,
ISCONFIDENTIAL =
case
when TARGET.ISCONFIDENTIAL = 1
then 1
when exists(select 1 from @DUPLICATEEMAILS DUPE where DUPE.TARGET_EMAILADDRESSID = TARGET.ID and DUPE.SOURCE_ISCONFIDENTIAL = 1)
then 1
else
0
end,
DONOTEMAILREASONCODEID =
case
when TARGET.DONOTEMAIL = 1
then TARGET.DONOTEMAILREASONCODEID
when exists(select 1 from @DUPLICATEEMAILS DUPE where DUPE.TARGET_EMAILADDRESSID = TARGET.ID and DUPE.SOURCE_DONOTEMAIL = 1)
then (select DUPE.SOURCE_DONOTEMAILREASONCODEID from @DUPLICATEEMAILS DUPE where DUPE.TARGET_EMAILADDRESSID = TARGET.ID and DUPE.SOURCE_DONOTEMAIL = 1)
else
null
end,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CHANGEDATE
from
dbo.EMAILADDRESS TARGET
where
TARGET.CONSTITUENTID = @TARGETID;
end
--Select the primary email address
if @PRIMARYCRITERIA = 0 and @TARGETPRIMARYID is not null
begin
-- The target's original primary email record
-- is assured of being associated with the target
-- so simply reset it's primary indicator.
update dbo.EMAILADDRESS
set ISPRIMARY = 1, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
where ID = @TARGETPRIMARYID
and RELATIONSHIPID is null;
-- If delete duplicates is set We may have to reset the
-- primary indicator on the source email address if it was a duplicate
if @DELETEDUPES <> 0
begin
declare @TEMPUID uniqueidentifier;
select @TEMPUID = CONSTITUENTID
from dbo.EMAILADDRESS
where ID = @SOURCEPRIMARYID;
if @TEMPUID = @SOURCEID
begin
-- If the source's primary email address is still associated to
-- the source, then reset the primary indicator
update dbo.EMAILADDRESS
set ISPRIMARY = 1, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
where ID = @SOURCEPRIMARYID
and RELATIONSHIPID is null;
end
end
end
else
begin
-- The target had no primary email record,
-- or we are preserving the source's primary
if @SOURCEPRIMARYID is null
begin
-- If the source had no primary email record then
-- attempt to reset the target's primary record indicator
update dbo.EMAILADDRESS
set ISPRIMARY = 1, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
where ID = @TARGETPRIMARYID
and RELATIONSHIPID is null;
end
else
begin
-- Otherwise, the source had a primary email record
if @DELETEDUPES = 0
begin
-- If we are not deleting duplicate email info
-- then we can be assured the source's primary
-- email info is associated with the target, so
-- simply reset its primary indicator
update dbo.EMAILADDRESS
set ISPRIMARY = 1, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
where ID = @SOURCEPRIMARYID
and RELATIONSHIPID is null;
end
else
begin
declare @TEMPID uniqueidentifier;
select @TEMPID = CONSTITUENTID
from dbo.EMAILADDRESS
where ID = @SOURCEPRIMARYID;
if @TEMPID = @TARGETID
begin
-- If the source primary email is now associated
-- with the target, then reset its primary indicator
update dbo.EMAILADDRESS
set ISPRIMARY = 1, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
where ID = @SOURCEPRIMARYID
and RELATIONSHIPID is null;
end
else
begin
-- Otherwise, the source's primary email was
-- not associated with the target b/c it is a
-- duplicate of one of the target's email records.
-- In this scenario, the target's email record that
-- is a duplicate of the source's primary email
-- should be set as the target's primary email.
set @TEMPID = null;
select @TEMPID = TARGET_EMAILADDRESSID
from @DUPLICATEEMAILS DE
where DE.SOURCE_EMAILADDRESSID = @SOURCEPRIMARYID;
if @TEMPID is not null
begin
update dbo.EMAILADDRESS
set ISPRIMARY = 1, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
where ID = @TEMPID
and RELATIONSHIPID is null;
end
else
begin
-- If no match was found then reset the primary
-- indicator on the target's original primary
-- record
update dbo.EMAILADDRESS
set ISPRIMARY = 1, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
where ID = @TARGETPRIMARYID
and RELATIONSHIPID is null;
end
end
end
end
end
--Reaim any mail prefs that might be on a group the source might belong to.
--This function will always remove prefs that will be invalidated by the merge,
--but it won't actually update the mail prefs unless the Personal Information
--Merge Task is also run
exec dbo.USP_CONSTITUENTMERGE_UPDATEGROUPEMAILMAILPREFS @TARGETID, @SOURCEID, @CHANGEAGENTID, @DELETEDUPES
if @INCLUDEPREFS = 1
begin
--Merge email address mail preferences
--Due to table constraints, we have to remove
--potential dupes before merging the preferences.
--If the target's preferences supersede,
--delete those the source has that would
--come over, but have a dupe on the target.
if @PREFCRITERIA = 0
begin
delete from MAILPREFERENCE
where
CONSTITUENTID=@SOURCEID and
(USEPRIMARYEMAIL=1 or
(EMAILADDRESSID is not null and
EMAILADDRESSID in (
select ID
from EMAILADDRESS
where CONSTITUENTID=@TARGETID
)
)
) and
dbo.UFN_MAILPREFERENCE_VALIDATEUNIQUE(ID,@TARGETID,MAILTYPECODE,BUSINESSUNITCODEID,CATEGORYCODEID,
EVENTCATEGORYCODEID,SITEID,ACKNOWLEDGEMENTID,CORRESPONDENCEID,PLEDGEREMINDERID,CORRESPONDENCECODEID,PURPOSEID)=0
end
--If the source's preferences supersede,
--delete those the target has that dupe
--a preference that is going to come over
--from the source.
if @PREFCRITERIA = 1
begin
delete targetmp
from MAILPREFERENCE targetmp, MAILPREFERENCE sourcemp
where
targetmp.CONSTITUENTID=@TARGETID and
sourcemp.CONSTITUENTID=@SOURCEID and
(sourcemp.USEPRIMARYEMAIL=1 or
(sourcemp.EMAILADDRESSID is not null and
sourcemp.EMAILADDRESSID in (
select ID
from EMAILADDRESS
where CONSTITUENTID=@TARGETID
)
)
) and
((targetmp.MAILTYPECODE = sourcemp.MAILTYPECODE) or (targetmp.MAILTYPECODE is null and sourcemp.MAILTYPECODE is null)) and
((targetmp.BUSINESSUNITCODEID = sourcemp.BUSINESSUNITCODEID) or (targetmp.BUSINESSUNITCODEID is null and sourcemp.BUSINESSUNITCODEID is null)) and
((targetmp.CATEGORYCODEID = sourcemp.CATEGORYCODEID) or (targetmp.CATEGORYCODEID is null and sourcemp.CATEGORYCODEID is null)) and
((targetmp.EVENTCATEGORYCODEID = sourcemp.EVENTCATEGORYCODEID) or (targetmp.EVENTCATEGORYCODEID is null and sourcemp.EVENTCATEGORYCODEID is null)) and
((targetmp.SITEID = sourcemp.SITEID) or (targetmp.SITEID is null and sourcemp.SITEID is null)) and
((targetmp.ACKNOWLEDGEMENTID = sourcemp.ACKNOWLEDGEMENTID) or (targetmp.ACKNOWLEDGEMENTID is null and sourcemp.ACKNOWLEDGEMENTID is null)) and
((targetmp.CORRESPONDENCEID = sourcemp.CORRESPONDENCEID) or (targetmp.CORRESPONDENCEID is null and sourcemp.CORRESPONDENCEID is null)) and
((targetmp.PLEDGEREMINDERID = sourcemp.PLEDGEREMINDERID) or (targetmp.PLEDGEREMINDERID is null and sourcemp.PLEDGEREMINDERID is null)) and
((targetmp.CORRESPONDENCECODEID = sourcemp.CORRESPONDENCECODEID) or (targetmp.CORRESPONDENCECODEID is null and sourcemp.CORRESPONDENCECODEID is null)) and
((targetmp.PURPOSEID = sourcemp.PURPOSEID) or (targetmp.PURPOSEID is null and sourcemp.PURPOSEID is null))
end
--Move preferences whose address also moved,
--send to the primary address, or are marked "Do not send"
update MAILPREFERENCE
set
CONSTITUENTID=@TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
where
CONSTITUENTID=@SOURCEID and
(USEPRIMARYEMAIL=1 or
(EMAILADDRESSID is not null and
EMAILADDRESSID in (
select ID
from EMAILADDRESS
where CONSTITUENTID=@TARGETID
)
)
)
end
--update sales order if deleting duplicates
if @DELETEDUPES <> 0
begin
update dbo.SALESORDER
set
EMAILADDRESSID = coalesce((
select top 1 target.ID
from dbo.EMAILADDRESS target
inner join dbo.EMAILADDRESS source
on target.EMAILADDRESS = source.EMAILADDRESS
and (target.EMAILADDRESSTYPECODEID = source.EMAILADDRESSTYPECODEID or (target.EMAILADDRESSTYPECODEID is null and source.EMAILADDRESSTYPECODEID is null))
where target.CONSTITUENTID = @TARGETID
and source.ID = SALESORDER.EMAILADDRESSID
), SALESORDER.EMAILADDRESSID),
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CHANGEDATE
where
SALESORDER.RECIPIENTID = @SOURCEID
end
update dbo.BATCHEMAILFINDER
set
CONSTITUENTID = @TARGETID
where
CONSTITUENTID = @SOURCEID
return 0;