USP_MERGETASK_CONSTITUENTEVENTS
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @SOURCEID | uniqueidentifier | IN | |
| @TARGETID | uniqueidentifier | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN |
Definition
Copy
CREATE procedure dbo.USP_MERGETASK_CONSTITUENTEVENTS
(
@SOURCEID uniqueidentifier,
@TARGETID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier
)
as
set nocount on;
declare @DATECHANGED datetime = getdate();
declare @TABLENAME nvarchar(50);
declare @ONEPERECORD bit;
declare @VALUEFIELD nvarchar(50);
declare @SQL nvarchar(max);
declare @SOURCEIDSTRING nvarchar(36);
declare @TARGETIDSTRING nvarchar(36);
declare @SOURCECONSTITIDSTRING nvarchar(36);
declare @TARGETCONSTITIDSTRING nvarchar(36);
declare @CHANGEAGENTIDSTRING nvarchar(36);
--WI 229133
update
dbo.EVENTATTACHMENT
set
AUTHORID = @TARGETID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @DATECHANGED
where
AUTHORID = @SOURCEID;
-- Determine whether or not there are any redundant
-- Registrant records between the Target and the source
-- (i.e. cases where both the Target and the Source are registered
-- for the same event)
if exists
(
select top(1)
SOURCE.ID
from
dbo.REGISTRANT SOURCE
inner join
dbo.REGISTRANT TARGET on SOURCE.EVENTID = TARGET.EVENTID
where
SOURCE.CONSTITUENTID = @SOURCEID
and TARGET.CONSTITUENTID = @TARGETID
)
begin
-- Create a temporary table that holds all
-- of the redundant source-target registrant
-- pairs
create table #REDUNDANTREGISTRANTS
(
SRCREGID uniqueidentifier,
TRGTREGID uniqueidentifier,
SRCWILLNOTATTEND bit,
TRGTWILLNOTATTEND bit,
SRCATTENDED bit,
TRGTATTENDED bit,
SRCGUESTOFREGISTRANTID uniqueidentifier,
TRGTGUESTOFREGISTRANTID uniqueidentifier,
EVENTID uniqueidentifier
);
begin try
insert into #REDUNDANTREGISTRANTS
select
SOURCE.ID,
TARGET.ID,
SOURCE.WILLNOTATTEND,
TARGET.WILLNOTATTEND,
SOURCE.ATTENDED,
TARGET.ATTENDED,
SOURCE.GUESTOFREGISTRANTID,
TARGET.GUESTOFREGISTRANTID,
SOURCE.EVENTID
from
dbo.REGISTRANT SOURCE
inner join
dbo.REGISTRANT TARGET on SOURCE.EVENTID = TARGET.EVENTID
where
SOURCE.CONSTITUENTID = @SOURCEID
and TARGET.CONSTITUENTID = @TARGETID;
--Bug 373031 - If any payment exists towards a redundant event registration then throw an error and force manual intervention.
if exists (select 1 from (
select
(coalesce(count(EVENTREGISTRANTPAYMENTSOURCE.ID),0) + coalesce(count(BATCHREVENUEAPPLICATIONSOURCE.ID),0)) +
(coalesce(count(EVENTREGISTRANTPAYMENTTARGET.ID),0) + coalesce(count(BATCHREVENUEAPPLICATIONTARGET.ID),0)) as TOTALPAYMENTCOUNT
from #REDUNDANTREGISTRANTS REDUNDANTREGISTRANTS
left join dbo.EVENTREGISTRANTPAYMENT as EVENTREGISTRANTPAYMENTSOURCE on EVENTREGISTRANTPAYMENTSOURCE.REGISTRANTID = REDUNDANTREGISTRANTS.SRCREGID
left join dbo.BATCHREVENUEAPPLICATION as BATCHREVENUEAPPLICATIONSOURCE on BATCHREVENUEAPPLICATIONSOURCE.REGISTRANTID = REDUNDANTREGISTRANTS.SRCREGID
left join dbo.EVENTREGISTRANTPAYMENT as EVENTREGISTRANTPAYMENTTARGET on EVENTREGISTRANTPAYMENTTARGET.REGISTRANTID = REDUNDANTREGISTRANTS.TRGTREGID
left join dbo.BATCHREVENUEAPPLICATION as BATCHREVENUEAPPLICATIONTARGET on BATCHREVENUEAPPLICATIONTARGET.REGISTRANTID = REDUNDANTREGISTRANTS.TRGTREGID
group by REDUNDANTREGISTRANTS.EVENTID
) as TOTALPAYMENTBYEVENT
where TOTALPAYMENTCOUNT >= 1
)
begin
raiserror('BBERR_EVENTREGISTRANTMERGE_PAYMENTEXISTS',13,1);
end
-- Transfer non-conflicting source's registrant records to target
update dbo.REGISTRANT set
CONSTITUENTID = @TARGETID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @DATECHANGED
where
CONSTITUENTID = @SOURCEID
and ID not in (select SRCREGID from #REDUNDANTREGISTRANTS);
-- Mark redundant target registrant as will not attend if the source is marked as will not attend
update dbo.REGISTRANT set
WILLNOTATTEND = 1,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @DATECHANGED
where
ID in (select TRGTREGID from #REDUNDANTREGISTRANTS where SRCWILLNOTATTEND = 1 and TRGTWILLNOTATTEND = 0);
-- Mark redundant target registrants as attended if the source is marked as attended
update dbo.REGISTRANT set
ATTENDED = 1,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @DATECHANGED
where
ID in (select TRGTREGID from #REDUNDANTREGISTRANTS where SRCATTENDED = 1 and TRGTATTENDED = 0);
-- Bug 240705
-- If the target record is a guest of someone other than the source record, update RegistrantRegistrationMap
-- to reference a new unnamed guest as the target will become the host and should no longer be referenced
-- as a guest in the mapping table
create table #NEWREGISTRANTS (
NEWREGISTRANTID uniqueidentifier,
TARGETREGISTRANTID uniqueidentifier,
REGISTRANTREGISTRATIONMAPID uniqueidentifier
)
insert into #NEWREGISTRANTS
select
newID(),
REDUNDANTREGISTRANTS.TRGTREGID,
REGISTRANTREGISTRATIONMAP.ID
from
#REDUNDANTREGISTRANTS REDUNDANTREGISTRANTS
inner join dbo.REGISTRANTREGISTRATIONMAP on REDUNDANTREGISTRANTS.TRGTREGID = REGISTRANTREGISTRATIONMAP.REGISTRANTID
inner join dbo.REGISTRANTREGISTRATION on REGISTRANTREGISTRATIONMAP.REGISTRANTREGISTRATIONID = REGISTRANTREGISTRATION.ID
where
REGISTRANTREGISTRATION.REGISTRANTID <> REDUNDANTREGISTRANTS.SRCREGID
and REGISTRANTREGISTRATION.REGISTRANTID <> REDUNDANTREGISTRANTS.TRGTREGID;
insert into dbo.REGISTRANT
(ID, EVENTID, CONSTITUENTID, ATTENDED, WILLNOTATTEND, GUESTOFREGISTRANTID, EVENTSEATINGNOTE, BENEFITSWAIVED, ONLINEREGISTRANT, ISCANCELLED, CUSTOMIDENTIFIER, NOTES, ISWALKIN, USERMARKEDATTENDANCE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
select
NEWREGISTRANTS.NEWREGISTRANTID,
REGISTRANT.EVENTID,
null, -- unnamed guest
REGISTRANT.ATTENDED,
REGISTRANT.WILLNOTATTEND,
REGISTRANT.GUESTOFREGISTRANTID,
REGISTRANT.EVENTSEATINGNOTE,
REGISTRANT.BENEFITSWAIVED,
REGISTRANT.ONLINEREGISTRANT,
REGISTRANT.ISCANCELLED,
REGISTRANT.CUSTOMIDENTIFIER,
REGISTRANT.NOTES,
REGISTRANT.ISWALKIN,
REGISTRANT.USERMARKEDATTENDANCE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@DATECHANGED,
@DATECHANGED
from #NEWREGISTRANTS NEWREGISTRANTS
inner join dbo.REGISTRANT
on NEWREGISTRANTS.TARGETREGISTRANTID = REGISTRANT.ID;
update dbo.REGISTRANTREGISTRATIONMAP set
REGISTRANTREGISTRATIONMAP.REGISTRANTID = NEWREGISTRANTS.NEWREGISTRANTID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @DATECHANGED
from #NEWREGISTRANTS NEWREGISTRANTS
where REGISTRANTREGISTRATIONMAP.ID = NEWREGISTRANTS.REGISTRANTREGISTRATIONMAPID;
-- If the target record is a guest, the target should become the host.
-- Setting the guestofregistrantid to null will enable future steps, such as guest migration
-- and registrant registration reassignment. WI 179172
update dbo.REGISTRANT set
GUESTOFREGISTRANTID = null,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @DATECHANGED
from
#REDUNDANTREGISTRANTS
where
REGISTRANT.ID = TRGTREGID
and GUESTOFREGISTRANTID is not null
and REGISTRANT.CONSTITUENTID is not null;
-- Constraints on the REGISTRANT table prevent a registrant from being a guest and a host.
-- Migrate host assignments from the source to the target only if the target is not a guest.
with GUESTREGISTRANTS as
(
select
TRGTREG.EVENTID,
REDUNDANT.SRCREGID,
TRGTREG.ID TRGTREGID
from
#REDUNDANTREGISTRANTS REDUNDANT
inner join
dbo.REGISTRANT TRGTREG on REDUNDANT.TRGTREGID = TRGTREG.ID
where
TRGTREG.GUESTOFREGISTRANTID is null
)
update dbo.REGISTRANT set
GUESTOFREGISTRANTID = GUESTREGISTRANTS.TRGTREGID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @DATECHANGED
from
dbo.REGISTRANT
inner join
GUESTREGISTRANTS on REGISTRANT.EVENTID = GUESTREGISTRANTS.EVENTID
where
GUESTOFREGISTRANTID = GUESTREGISTRANTS.SRCREGID
-- Migrate the registration map for a redundant source registrant to the target registrant if the corresponding
-- registration is going to be migrated to the target registrant.
update dbo.REGISTRANTREGISTRATIONMAP set
REGISTRANTID = TRGTREGID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @DATECHANGED
from
#REDUNDANTREGISTRANTS
where
REGISTRANTID = SRCREGID
and not exists
(
select top(1) REGISTRANT.ID
from dbo.REGISTRANT
left outer join dbo.REGISTRANTREGISTRATION
on REGISTRANT.ID = REGISTRANTREGISTRATION.REGISTRANTID
where REGISTRANT.ID = #REDUNDANTREGISTRANTS.TRGTREGID
and
(
-- if the target is a guest, don't migrate
REGISTRANT.GUESTOFREGISTRANTID is not null
-- if the target is the registration, only migrate if the source is a guest in that registration (and the target is not mapped)
or (REGISTRANTREGISTRATION.REGISTRANTID is not null and not SRCGUESTOFREGISTRANTID = TRGTREGID)
)
)
and not exists
(
select 1 from dbo.REGISTRANTREGISTRATIONMAP
where REGISTRANTID = TRGTREGID
)
-- Migrate the registration for a redundant source registrant to the target registrant if the target
-- registrant does not have a registration record and the target record is not a guest
-- (guests cannot have registration records) or the source registrant has other registrants using the record.
update dbo.REGISTRANTREGISTRATION set
REGISTRANTID = TRGTREGID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @DATECHANGED
from
#REDUNDANTREGISTRANTS
where
REGISTRANTID = SRCREGID
and
(
not exists
(
select top(1) REGISTRANT.ID
from dbo.REGISTRANT
left outer join dbo.REGISTRANTREGISTRATION
on REGISTRANT.ID = REGISTRANTREGISTRATION.REGISTRANTID
where REGISTRANT.ID = #REDUNDANTREGISTRANTS.TRGTREGID
and
(
REGISTRANT.GUESTOFREGISTRANTID is not null
or REGISTRANTREGISTRATION.REGISTRANTID is not null
)
)
or
exists
(
select 1 from dbo.REGISTRANTREGISTRATIONMAP
where REGISTRANTREGISTRATIONMAP.REGISTRANTREGISTRATIONID = REGISTRANTREGISTRATION.ID
and REGISTRANTREGISTRATIONMAP.REGISTRANTID <> #REDUNDANTREGISTRANTS.SRCREGID
)
)
-- Migrate redundant source registrant benefits if the target registrant does not have a benefit.
update dbo.REGISTRANTBENEFIT set
REGISTRANTID = TRGTREGID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @DATECHANGED
from
#REDUNDANTREGISTRANTS
where
REGISTRANTID = SRCREGID
and not exists (select top(1) ID from dbo.REGISTRANTBENEFIT where REGISTRANTID = TRGTREGID)
-- Migrate redundant source registrant preferences if the target registrant does not have a preference.
update dbo.REGISTRANTPREFERENCE set
REGISTRANTID = TRGTREGID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @DATECHANGED
from
#REDUNDANTREGISTRANTS
where
REGISTRANTID = SRCREGID
and not exists (select top(1) ID from dbo.REGISTRANTPREFERENCE where REGISTRANTID = TRGTREGID)
update dbo.REGISTRANTPACKAGE set
CONSTITUENTID = @TARGETID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @DATECHANGED
where
CONSTITUENTID = @SOURCEID
update dbo.REGISTRANTPACKAGE set
GUESTOFCONSTITUENTID = @TARGETID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @DATECHANGED
where
GUESTOFCONSTITUENTID = @SOURCEID
-- Migrate redundant source seating if the target registrant does not have a seat record.
update dbo.EVENTSEATINGSEAT set
REGISTRANTID = TRGTREGID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @DATECHANGED
from
#REDUNDANTREGISTRANTS
where
REGISTRANTID = SRCREGID
and not exists (select top(1) ID from dbo.EVENTSEATINGSEAT where REGISTRANTID = TRGTREGID)
-- Migrate redundant source group membership if the target registrant is not in a group.
update dbo.EVENTGROUPMEMBER set
REGISTRANTID = TRGTREGID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @DATECHANGED
from
#REDUNDANTREGISTRANTS
where
REGISTRANTID = SRCREGID
and not exists (select top(1) ID from dbo.EVENTGROUPMEMBER where REGISTRANTID = TRGTREGID)
-- Migrate redundant source sales order registrants to the target registrants
update dbo.SALESORDERITEMEVENTREGISTRATION set
REGISTRANTID = TRGTREGID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @DATECHANGED
from
#REDUNDANTREGISTRANTS
where
REGISTRANTID = SRCREGID;
/*
WI 166678, If the source and the target are both registered for the same pre-registered program
set the source registrant row constituentid to null and set the guestofregistrantid = target registrant id
then before the cleanup cursor delete these specific rows from the #REDUNDANTREGISTRATIONS table
because these registrations are now valid as guest registrations of the target constituent.
this work is necessary due to the one to one constraint for the REGISTRANTID FK column on the
SALESORDERITEMTICKETREGISTRANT table.
*/
declare @REDUNDANTPREREGISTEREDREGISTRANTS table (SRCID uniqueidentifier, TGTID uniqueidentifier);
insert into @REDUNDANTPREREGISTEREDREGISTRANTS (SRCID, TGTID)
select
SRCREGID,
TRGTREGID
from dbo.REGISTRANT REG
inner join dbo.SALESORDERITEMTICKETREGISTRANT
on REG.ID = SALESORDERITEMTICKETREGISTRANT.REGISTRANTID
inner join #REDUNDANTREGISTRANTS
on SALESORDERITEMTICKETREGISTRANT.REGISTRANTID = #REDUNDANTREGISTRANTS.SRCREGID;
/*
Update guests of the source registrant to avoid CK_REGISTRANT_VALIDGUESTCOUNT
error when updating the source record in the next update statement. This will
update both named and unnamed registrants.
*/
update REG set
GUESTOFREGISTRANTID = RPR.TGTID
from dbo.REGISTRANT REG
inner join @REDUNDANTPREREGISTEREDREGISTRANTS RPR
on RPR.SRCID = REG.GUESTOFREGISTRANTID;
update REG set
CONSTITUENTID = null,
GUESTOFREGISTRANTID = RPR.TGTID
from dbo.REGISTRANT REG
inner join @REDUNDANTPREREGISTEREDREGISTRANTS RPR
on RPR.SRCID = REG.ID;
/*
WI 168312, If the target is a guest of the source and the source is not attending
set GUESTOFREGISTRANT = null for the target registrant record and delete the source
registrant record
*/
declare @TARGETGUESTOFSOURCE table (TGTID uniqueidentifier);
insert into @TARGETGUESTOFSOURCE (TGTID)
select
TRGTREGID
from
#REDUNDANTREGISTRANTS
where
SRCREGID not in (select REGISTRANTID from SALESORDERITEMTICKETREGISTRANT)
and TRGTGUESTOFREGISTRANTID = SRCREGID;
update REG set
GUESTOFREGISTRANTID = null
from dbo.REGISTRANT REG
inner join @TARGETGUESTOFSOURCE TGS
on REG.ID = TGS.TGTID;
--WI 166654
update dbo.CREDITITEMEVENTREGISTRATION set
REGISTRANTID = TRGTREGID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @DATECHANGED
from
#REDUNDANTREGISTRANTS
where
REGISTRANTID = SRCREGID;
-- Check comment block above regarding WI 166678 for explanation of this delete
delete from #REDUNDANTREGISTRANTS
where exists(
select
1
from
@REDUNDANTPREREGISTEREDREGISTRANTS
where
SRCID = SRCREGID
and TGTID = TRGTREGID
);
-- This cursor is used several times to migrate redundant source registrant data to the target registrant
declare @SRCREGID uniqueidentifier;
declare @TRGTREGID uniqueidentifier;
declare REDUNDANTREGISTRANTCURSOR cursor local fast_forward for select SRCREGID, TRGTREGID from #REDUNDANTREGISTRANTS;
-- Migrate any Registrant attributes from the source registrant records to the target registrant records
if dbo.UFN_ATTRIBUTECATEGORY_DEFINEDFORRECORDTYPE('Registrant') = 1
begin
set @CHANGEAGENTIDSTRING = convert(nvarchar(36), @CHANGEAGENTID);
set @SOURCECONSTITIDSTRING = convert(nvarchar(36), @SOURCEID);
set @TARGETCONSTITIDSTRING = convert(nvarchar(36), @TARGETID);
declare ATTRTABLECURSOR cursor local fast_forward for
select
TABLECATALOG.TABLENAME,
ATTRIBUTECATEGORY.ONLYALLOWONEPERRECORD,
VALUECOLUMNNAME
from
dbo.ATTRIBUTECATEGORY
inner join
dbo.TABLECATALOG on TABLECATALOG.ID = TABLECATALOGID
inner join
dbo.ATTRIBUTERECORDTYPE on ATTRIBUTERECORDTYPE.ID = ATTRIBUTERECORDTYPEID
inner join
dbo.RECORDTYPE on RECORDTYPE.ID = RECORDTYPEID
where
RECORDTYPE.NAME = 'Registrant';
open REDUNDANTREGISTRANTCURSOR;
fetch next from REDUNDANTREGISTRANTCURSOR into @SRCREGID, @TRGTREGID;
while @@fetch_status = 0
begin
set @SOURCEIDSTRING = convert(nvarchar(36), @SRCREGID);
set @TARGETIDSTRING = convert(nvarchar(36), @TRGTREGID);
open ATTRTABLECURSOR;
fetch next from ATTRTABLECURSOR into @TABLENAME, @ONEPERECORD, @VALUEFIELD;
while @@fetch_status = 0
begin
if @ONEPERECORD = 1
begin
set @SQL = '
if not exists
(
select top(1) ID
from dbo.' + @TABLENAME + '
where ID = ''' + @TARGETIDSTRING + '''
)
insert into dbo.' + @TABLENAME + '
(ID, ' + @VALUEFIELD + ', COMMENT, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
select
''' + @TARGETIDSTRING + ''',
' + @VALUEFIELD +',
COMMENT,
ADDEDBYID,
''' + @CHANGEAGENTIDSTRING + ''',
DATEADDED,
''' + convert(nvarchar,getdate()) + '''
from
dbo.' + @TABLENAME + '
where
ID = ''' + @SOURCEIDSTRING + ''';'
end
else
begin
set @SQL = '
update dbo.' + @TABLENAME + '
set REGISTRANTID = ''' + @TARGETIDSTRING + ''', CHANGEDBYID = ''' + @CHANGEAGENTIDSTRING + ''', DATECHANGED = ''' + convert(nvarchar,getdate()) + '''
where REGISTRANTID = ''' + @SOURCEIDSTRING + ''';'
end
-- Update any registrant constituent-type attributes to point from the source to the target
if @VALUEFIELD = 'CONSTITUENTVALUEID'
begin
set @SQL = @SQL + '
update dbo.' + @TABLENAME +'
set CONSTITUENTVALUEID = ''' + @TARGETCONSTITIDSTRING + ''', CHANGEDBYID = ''' + @CHANGEAGENTIDSTRING + ''', DATECHANGED = ''' + convert(nvarchar,getdate()) + '''
where CONSTITUENTVALUEID = ''' + @SOURCECONSTITIDSTRING + ''';'
end
exec(@SQL);
fetch next from ATTRTABLECURSOR into @TABLENAME, @ONEPERECORD, @VALUEFIELD;
end
close ATTRTABLECURSOR;
fetch next from REDUNDANTREGISTRANTCURSOR into @SRCREGID, @TRGTREGID;
end
close REDUNDANTREGISTRANTCURSOR;
deallocate ATTRTABLECURSOR;
end
/* Friends Asking Friends data */
-- combine comments that made to 2 different registrants
update dbo.FAFCOMMENTS set
REGISTRANTID = TRGTREGID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @DATECHANGED
from #REDUNDANTREGISTRANTS
where REGISTRANTID = SRCREGID
-- combine rss feeds that added to 2 different registrants
update dbo.FAFRSSFEED set
REGISTRANTID = TRGTREGID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @DATECHANGED
from #REDUNDANTREGISTRANTS
where REGISTRANTID = SRCREGID
-- combine offline gift link entered by registrant
update dbo.REVENUEOFFLINEDONATION set
REGISTRANTID = TRGTREGID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @DATECHANGED
from #REDUNDANTREGISTRANTS
where REGISTRANTID = SRCREGID
-- combine network post
update dbo.FAFEVENTSOCIALNETWORKPOST set
REGISTRANTID = TRGTREGID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @DATECHANGED
from #REDUNDANTREGISTRANTS
where REGISTRANTID = SRCREGID
--registrant donation
create table #REDUNDANTREGISTRANTDONATION
(
SRCREGID uniqueidentifier,
TRTREGID uniqueidentifier,
SRCVOLDONATION money,
TRTVOLDONATION money
);
insert into #REDUNDANTREGISTRANTDONATION
select
RR.SRCREGID,
RR.TRGTREGID,
isnull(RDS.VOLUNTARYDONATION, 0),
isnull(RDT.VOLUNTARYDONATION, 0)
from #REDUNDANTREGISTRANTS RR
left join dbo.REGISTRANTDONATION RDS on RDS.REGISTRANTID = RR.SRCREGID
left join dbo.REGISTRANTDONATION RDT on RDT.REGISTRANTID = RR.TRGTREGID
update dbo.REGISTRANTDONATION set
VOLUNTARYDONATION = SRCVOLDONATION + TRTVOLDONATION,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @DATECHANGED
from #REDUNDANTREGISTRANTDONATION
where REGISTRANTID = TRTREGID
drop table #REDUNDANTREGISTRANTDONATION
update RET set
ISPRIVATE = case when RE.ISPRIVATE = 1 then RE.ISPRIVATE else RET.ISPRIVATE end,
ALLOWADMINACCESSHQ = case when RE.ALLOWADMINACCESSHQ = 0 then RE.ALLOWADMINACCESSHQ else RET.ALLOWADMINACCESSHQ end,
CREDITCARDID = coalesce(RET.CREDITCARDID, RE.CREDITCARDID, null),
ORGANIZATIONCONSTITUENTID = coalesce(RET.ORGANIZATIONCONSTITUENTID, RE.ORGANIZATIONCONSTITUENTID, null),
ISDASHBOARDHELPERVISIBLE = case when RE.ISDASHBOARDHELPERVISIBLE = 1 then RE.ISDASHBOARDHELPERVISIBLE else RET.ISDASHBOARDHELPERVISIBLE end,
LOCALCORPPOSTCODE = case when RET.LOCALCORPPOSTCODE = '' then RE.LOCALCORPPOSTCODE else RET.LOCALCORPPOSTCODE end,
LOCALCORPID = coalesce(RET.LOCALCORPID, RE.LOCALCORPID, null),
ALLOWOTHERPARTICIPANTSCONTACTME = case when RE.ALLOWOTHERPARTICIPANTSCONTACTME = 0 then RE.ALLOWOTHERPARTICIPANTSCONTACTME else RET.ALLOWOTHERPARTICIPANTSCONTACTME end ,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @DATECHANGED
from REGISTRANTEXTENSION RET
join #REDUNDANTREGISTRANTS RR on RET.REGISTRANTID = RR.TRGTREGID
left join REGISTRANTEXTENSION RE on RR.SRCREGID = RE.REGISTRANTID
where RET.REGISTRANTID = RR.TRGTREGID
if exists(select ID from dbo.STORY S join #REDUNDANTREGISTRANTS RR on S.REGISTRANTID = RR.TRGTREGID)
begin
update dbo.STORY set
STORYTEXT = case when isnull(STORYTEXT, '') = '' then isnull((select STORYTEXT from dbo.STORY where REGISTRANTID = SRCREGID), '') else STORYTEXT end,
FAFIMAGESID = case when FAFIMAGESID is null then (select FAFIMAGESID from dbo.STORY where REGISTRANTID = SRCREGID) else FAFIMAGESID end,
IMAGECODE = case when IMAGECODE is null then (select IMAGECODE from dbo.STORY where REGISTRANTID = SRCREGID) else IMAGECODE end,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @DATECHANGED
from #REDUNDANTREGISTRANTS
where REGISTRANTID = SRCREGID
end
else
update dbo.STORY set
REGISTRANTID = TRGTREGID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @DATECHANGED
from #REDUNDANTREGISTRANTS
where REGISTRANTID = SRCREGID
;merge into dbo.FAFEVENTCOMMUNICATIONCHANNEL as Target
using
(
select FCS.TYPEGUID as SRCGID, R.SRCREGID, FC.TYPEGUID as TRGID, R.TRGTREGID from #REDUNDANTREGISTRANTS R
left join dbo.FAFEVENTCOMMUNICATIONCHANNEL FCS on R.SRCREGID = FCS.TYPEGUID and FCS.TYPECODE = 0
left join dbo.FAFEVENTCOMMUNICATIONCHANNEL FC on R.TRGTREGID = FC.TYPEGUID and FC.TYPECODE = 0
) as source on source.SRCREGID = Target.TYPEGUID
when matched and source.TRGID is null then
update set
TYPEGUID = source.TRGTREGID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @DATECHANGED
when matched then
delete;
-- friendly URL move or delete
;merge into dbo.VanityURL as Target
using
(
select R.SRCREGID, R.TRGTREGID, V1.ID SRCPID, V2.ID TRGTPID
from #REDUNDANTREGISTRANTS R
left join dbo.FAFFRIENDLYURLPARAMS FAF ON FAF.PARTICIPANTID = R.SRCREGID AND FAF.PAGETYPE = 0
left join dbo.VanityURL V1 on V1.RealmID = FAF.SEQUENCEID and V1.RealmTypeID = 7
left join dbo.FAFFRIENDLYURLPARAMS FAF2 ON FAF2.PARTICIPANTID = R.TRGTREGID AND FAF.PAGETYPE = 0
left join dbo.VanityURL V2 on V2.RealmID = FAF2.SEQUENCEID and V2.RealmTypeID = 7
) as source on source.SRCPID = Target.ID and source.TRGTPID is not null
when matched then
delete;
;merge into dbo.FAFFRIENDLYURLPARAMS as target
using
(
select R.SRCREGID, R.TRGTREGID, FAF.ID SRCPID, FAF2.ID TRGTPID
from #REDUNDANTREGISTRANTS R
left join dbo.FAFFRIENDLYURLPARAMS FAF ON FAF.PARTICIPANTID = R.SRCREGID AND FAF.PAGETYPE = 0
left join dbo.FAFFRIENDLYURLPARAMS FAF2 ON FAF2.PARTICIPANTID = R.TRGTREGID AND FAF.PAGETYPE = 0
) as source on source.SRCPID = target.ID
when matched and source.TRGTPID is null then
update set
PARTICIPANTID = source.TRGTREGID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @DATECHANGED
when matched then
delete;
/* end Friends Asking Friends data */
-- Now that the child records for the redundant registrant records have been migrated,
-- delete the redundant source registrants.
open REDUNDANTREGISTRANTCURSOR;
fetch next from REDUNDANTREGISTRANTCURSOR into @SRCREGID, @TRGTREGID;
while @@fetch_status = 0
begin
exec dbo.USP_REGISTRANT_DELETE @SRCREGID, @CHANGEAGENTID;
fetch next from REDUNDANTREGISTRANTCURSOR into @SRCREGID, @TRGTREGID;
end
close REDUNDANTREGISTRANTCURSOR;
deallocate REDUNDANTREGISTRANTCURSOR;
-- Finally, drop the redundant registrant table
drop table #REDUNDANTREGISTRANTS;
end try
begin catch
drop table #REDUNDANTREGISTRANTS;
declare @ERRORMESSAGE nvarchar(4000);
declare @ERRORSEVERITY int;
declare @ERRORSTATE int;
select
@ERRORMESSAGE = error_message(),
@ERRORSEVERITY = error_severity(),
@ERRORSTATE = error_state();
-- Use RAISERROR inside the CATCH block to return error information about the original error that caused
-- execution to jump to the CATCH block.
raiserror(@ERRORMESSAGE, @ERRORSEVERITY, @ERRORSTATE);
end catch
end
else
begin
-- There were no redundant registrants so the operation is much simpler
update dbo.REGISTRANT set
CONSTITUENTID = @TARGETID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @DATECHANGED
where
CONSTITUENTID = @SOURCEID;
update dbo.REGISTRANT set
GUESTOFREGISTRANTID = @TARGETID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @DATECHANGED
where
GUESTOFREGISTRANTID = @SOURCEID;
update dbo.REGISTRANTPACKAGE set
CONSTITUENTID = @TARGETID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @DATECHANGED
where
CONSTITUENTID = @SOURCEID
update dbo.REGISTRANTPACKAGE set
GUESTOFCONSTITUENTID = @TARGETID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @DATECHANGED
where
GUESTOFCONSTITUENTID = @SOURCEID
-- Migrate any Registrant attributes from the source registrant records to the target registrant records
set @CHANGEAGENTIDSTRING = convert(nvarchar(36), @CHANGEAGENTID);
set @SOURCECONSTITIDSTRING = convert(nvarchar(36), @SOURCEID);
set @TARGETCONSTITIDSTRING = convert(nvarchar(36), @TARGETID);
declare ATTRTABLECURSOR cursor local fast_forward for
select
TABLECATALOG.TABLENAME,
VALUECOLUMNNAME
from
dbo.ATTRIBUTECATEGORY
inner join
dbo.TABLECATALOG on TABLECATALOG.ID = TABLECATALOGID
inner join
dbo.ATTRIBUTERECORDTYPE on ATTRIBUTERECORDTYPE.ID = ATTRIBUTERECORDTYPEID
inner join
dbo.RECORDTYPE on RECORDTYPE.ID = RECORDTYPEID
where
RECORDTYPE.NAME = 'Registrant';
open ATTRTABLECURSOR;
fetch next from ATTRTABLECURSOR into @TABLENAME, @VALUEFIELD;
while @@fetch_status = 0
begin
if @VALUEFIELD = 'CONSTITUENTVALUEID'
begin
set @SQL = '
update dbo.' + @TABLENAME +'
set CONSTITUENTVALUEID = ''' + @TARGETCONSTITIDSTRING + ''', CHANGEDBYID = ''' + @CHANGEAGENTIDSTRING + ''', DATECHANGED = ''' + convert(nvarchar,getdate()) + '''
where CONSTITUENTVALUEID = ''' + @SOURCECONSTITIDSTRING + ''';'
exec(@SQL);
end
fetch next from ATTRTABLECURSOR into @TABLENAME, @VALUEFIELD;
end
close ATTRTABLECURSOR;
deallocate ATTRTABLECURSOR;
end;
-- Event Coordinator
declare @EVENTSWITHREDUNDANTCOORDINATORS table
(
EVENTID uniqueidentifier,
ISPRIMARY bit
)
insert into @EVENTSWITHREDUNDANTCOORDINATORS
select
a.EVENTID,
a.ISPRIMARY
from
dbo.EVENTCOORDINATOR a
inner join dbo.EVENTCOORDINATOR b on a.EVENTID = b.EVENTID
where
a.CONSTITUENTID = @SOURCEID
and b.CONSTITUENTID = @TARGETID
update dbo.EVENTCOORDINATOR set
CONSTITUENTID = @TARGETID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @DATECHANGED
where
CONSTITUENTID = @SOURCEID
and EVENTID not in (select EVENTID from @EVENTSWITHREDUNDANTCOORDINATORS)
delete from
dbo.EVENTCOORDINATOR
where
CONSTITUENTID = @SOURCEID
update dbo.EVENTCOORDINATOR set
ISPRIMARY = 1,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @DATECHANGED
where
CONSTITUENTID = @TARGETID
and EVENTID in (select EVENTID from @EVENTSWITHREDUNDANTCOORDINATORS where ISPRIMARY = 1)
update dbo.EVENTINSTRUCTOR set
CONSTITUENTID = @TARGETID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @DATECHANGED
where
CONSTITUENTID = @SOURCEID
and ID not in
(
select a.ID
from dbo.EVENTINSTRUCTOR a
inner join dbo.EVENTINSTRUCTOR b
on a.EVENTID = b.EVENTID
where a.CONSTITUENTID = @SOURCEID
and b.CONSTITUENTID = @TARGETID
)
update dbo.EVENTNOTE set
AUTHORID = @TARGETID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @DATECHANGED
where
AUTHORID = @SOURCEID
update dbo.EVENTTASK set
OWNERID = @TARGETID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @DATECHANGED
where
OWNERID = @SOURCEID
-- Move invitee records only if the target does not already have an invitee record for the same event
update dbo.INVITEE set
CONSTITUENTID = @TARGETID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @DATECHANGED
where
CONSTITUENTID = @SOURCEID
and ID not in
(
select a.ID
from dbo.INVITEE a
inner join dbo.INVITEE b
on a.EVENTID = b.EVENTID
where a.CONSTITUENTID = @SOURCEID
and b.CONSTITUENTID = @TARGETID
);
-- Migrate travel and lodging preferences if they don't already exist for the target
update dbo.REGISTRANTLODGING set
REGISTRANTCONSTITUENTID = @TARGETID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @DATECHANGED
where
REGISTRANTCONSTITUENTID = @SOURCEID
and ID not in
(
select a.ID
from dbo.REGISTRANTLODGING a
inner join dbo.REGISTRANTLODGING b
on a.EVENTID = b.EVENTID
where a.REGISTRANTCONSTITUENTID = @SOURCEID
and b.REGISTRANTCONSTITUENTID = @TARGETID
);
update dbo.REGISTRANTTRAVEL set
REGISTRANTCONSTITUENTID = @TARGETID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @DATECHANGED
where
REGISTRANTCONSTITUENTID = @SOURCEID
and ID not in
(
select a.ID
from dbo.REGISTRANTTRAVEL a
inner join dbo.REGISTRANTTRAVEL b
on a.MAINEVENTID = b.MAINEVENTID
where a.REGISTRANTCONSTITUENTID = @SOURCEID
and b.REGISTRANTCONSTITUENTID = @TARGETID
);
-- In the case that both the target and the source had invitee records for the same event,
-- make sure the target's invitee record is marked as declined if either the target
-- record or the source record is marked as declined
with DECLINEINVITES as
(
select
b.ID
from
dbo.INVITEE a
inner join
dbo.INVITEE b on a.EVENTID = b.EVENTID
where
a.CONSTITUENTID = @SOURCEID
and b.CONSTITUENTID = @TARGETID
and a.DECLINED = 1
and b.DECLINED = 0
)
update dbo.INVITEE set
DECLINED = 1,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @DATECHANGED
where
ID in (select ID from DECLINEINVITES);
-- In the case that both the target and the source had invitee records for the same event,
-- make sure that the target record has the most-recent invitation-sent-on-date
with UPDATEINVITES as
(
select
b.ID,
a.INVITATIONSENTON
from
dbo.INVITEE a
inner join
dbo.INVITEE b on a.EVENTID = b.EVENTID
where
a.CONSTITUENTID = @SOURCEID
and b.CONSTITUENTID = @TARGETID
and a.INVITATIONSENTON > b.INVITATIONSENTON
)
update dbo.INVITEE set
INVITEE.INVITATIONSENTON = UPDATEINVITES.INVITATIONSENTON,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @DATECHANGED
from
dbo.INVITEE
inner join UPDATEINVITES on INVITEE.ID = UPDATEINVITES.ID;
--Invitees can be invited to an event via a single household invite (Send one invite per household)
--We store the CONSTITUENTID of the household member that did receive the invite for the household
--on each other household member in the same invitee list.
--We need to update this CONSTITUENTID from source to target.
update dbo.INVITEE
set
INVITEE.INVITATIONRECIPIENTCONSTITUENTID = @TARGETID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @DATECHANGED
where
INVITEE.INVITATIONRECIPIENTCONSTITUENTID = @SOURCEID;
-- In the case that both the target and the source had invitee records for the same event,
-- if the source invitee was flagged for resend and the target invitee was not,
-- make sure the source invitee's resend record is moved to the target invitee
with UPDATERESEND as
(
select a.ID SINVID, b.ID as TINVID, INVITEERESEND.INVITATIONID
from dbo.INVITEE a
inner join dbo.INVITEE b
on a.EVENTID = b.EVENTID
inner join INVITEERESEND
on a.ID = INVITEERESEND.INVITEEID
where a.CONSTITUENTID = @SOURCEID
and b.CONSTITUENTID = @TARGETID
)
update dbo.INVITEERESEND set
INVITEEID = UPDATERESEND.TINVID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @DATECHANGED
from
dbo.INVITEERESEND
inner join
UPDATERESEND on INVITEERESEND.INVITEEID = UPDATERESEND.SINVID
where
not exists (select ID from dbo.INVITEERESEND where INVITEEID = UPDATERESEND.TINVID and INVITATIONID = UPDATERESEND.INVITATIONID)
update dbo.INVITEEHISTORY set
CONSTITUENTID = @TARGETID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @DATECHANGED
where
CONSTITUENTID = @SOURCEID
-- Delete the redundant source invitee records
declare @CONTEXTCACHE varbinary(128);
set @CONTEXTCACHE = CONTEXT_INFO();
if not @CHANGEAGENTID is null
set CONTEXT_INFO @CHANGEAGENTID;
delete from
dbo.INVITEE
where
CONSTITUENTID = @SOURCEID
and ID in
(
select a.ID
from dbo.INVITEE a
inner join dbo.INVITEE b
on a.EVENTID = b.EVENTID
where a.CONSTITUENTID = @SOURCEID
and b.CONSTITUENTID = @TARGETID
)
if not @CONTEXTCACHE is null
set CONTEXT_INFO @CONTEXTCACHE;
update dbo.EVENT set
EVENTLOCATIONCONTACTID = @TARGETID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @DATECHANGED
where
EVENTLOCATIONCONTACTID = @SOURCEID
update dbo.EVENTSPEAKER set
CONSTITUENTID = @TARGETID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @DATECHANGED
where
CONSTITUENTID = @SOURCEID
update dbo.EVENTEXPENSE set
VENDORID = @TARGETID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @DATECHANGED
where
VENDORID = @SOURCEID
-- Update event attribute records that have a "Constituent" data type that reference the source so that they reference the target
declare @ATTRTABLENAME nvarchar(50);
declare @ATTRVALUEFIELD nvarchar(50);
declare ATTRTABLECURSOR cursor local fast_forward for
select
TABLECATALOG.TABLENAME,
VALUECOLUMNNAME
from
dbo.ATTRIBUTECATEGORY
inner join
dbo.TABLECATALOG on TABLECATALOG.ID = TABLECATALOGID
inner join
dbo.ATTRIBUTERECORDTYPE on ATTRIBUTERECORDTYPE.ID = ATTRIBUTERECORDTYPEID
inner join
dbo.RECORDTYPE on RECORDTYPE.ID = RECORDTYPEID
where
RECORDTYPE.NAME = 'Event';
open ATTRTABLECURSOR;
fetch next from ATTRTABLECURSOR into @ATTRTABLENAME, @ATTRVALUEFIELD;
while @@fetch_status = 0
begin
if @ATTRVALUEFIELD = 'CONSTITUENTVALUEID'
begin
set @SQL = '
update dbo.' + @ATTRTABLENAME +'
set CONSTITUENTVALUEID = ''' + @TARGETCONSTITIDSTRING + ''', CHANGEDBYID = ''' + @CHANGEAGENTIDSTRING + ''', DATECHANGED = ''' + convert(nvarchar,getdate()) + '''
where CONSTITUENTVALUEID = ''' + @SOURCECONSTITIDSTRING + ''';'
exec(@SQL);
end
fetch next from ATTRTABLECURSOR into @TABLENAME, @VALUEFIELD;
end
close ATTRTABLECURSOR;
deallocate ATTRTABLECURSOR;
--update FAF data
-- event sponsorship
if exists (select top(1) SOURCE.ID from dbo.EVENTSPONSOR SOURCE inner join dbo.EVENTSPONSOR TARGET on SOURCE.EVENTID = TARGET.EVENTID
where SOURCE.CONSTITUENTID = @SOURCEID and TARGET.CONSTITUENTID = @TARGETID )
begin
create table #REDUNDANTSPONSOR
(
SRCSPONSORID uniqueidentifier,
TRGTSPONSORID uniqueidentifier,
SRCVOLUNTARYDONATION money,
TRGTVOLUNTARYDONATION money
);
insert into #REDUNDANTSPONSOR
select
SOURCE.ID,
TARGET.ID,
isnull(SOURCEDON.VOLUNTARYDONATION, 0),
isnull(TARGETDON.VOLUNTARYDONATION, 0)
from
dbo.EVENTSPONSOR SOURCE
left join dbo.SPONSORDONATION SOURCEDON on SOURCEDON.SPONSORID = SOURCE.ID
inner join dbo.EVENTSPONSOR TARGET on SOURCE.EVENTID = TARGET.EVENTID
left join dbo.SPONSORDONATION TARGETDON on TARGETDON.SPONSORID = TARGET.ID
where
SOURCE.CONSTITUENTID = @SOURCEID
and TARGET.CONSTITUENTID = @TARGETID;
-- update all records from this source id to target id that are not in the redundant record (could be from other events)
update dbo.EVENTSPONSOR set
CONSTITUENTID = @TARGETID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @DATECHANGED
where
CONSTITUENTID = @SOURCEID
and ID not in (select SRCSPONSORID from #REDUNDANTSPONSOR);
update dbo.SPONSORDONATION set
VOLUNTARYDONATION = VOLUNTARYDONATION + RSP.SRCVOLUNTARYDONATION,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @DATECHANGED
from #REDUNDANTSPONSOR RSP
where ID = RSP.TRGTSPONSORID;
delete EV
from dbo.EVENTSPONSOR EV
join #REDUNDANTSPONSOR T ON EV.ID = T.SRCSPONSORID
drop table #REDUNDANTSPONSOR;
end
else
update dbo.EVENTSPONSOR set
CONSTITUENTID = @TARGETID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @DATECHANGED
where CONSTITUENTID = @SOURCEID;
--- FAF participant address book
exec dbo.USP_MERGE_CONSTITUENT_WITHFAFDATA @SOURCEID=@SOURCEID, @TARGETID=@TARGETID, @CHANGEAGENTID=@CHANGEAGENTID, @DATECHANGED=@DATECHANGED
return 0;