USP_REGISTRANT_UNIFIEDUPDATE
Saves updates to a registrant's registrations and guests.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | INOUT | |
| @CURRENTAPPUSERID | uniqueidentifier | IN | |
| @SECURITYCONTEXTFORMINSTANCEID | uniqueidentifier | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @CURRENTDATE | datetime | IN | |
| @EVENTID | uniqueidentifier | IN | |
| @CONSTITUENTID | uniqueidentifier | IN | |
| @DATEPURCHASED | datetime | IN | |
| @PACKAGEREGISTRATIONS | xml | IN | |
| @PACKAGESPRICES | xml | IN | |
| @SINGLEEVENTREGISTRATIONS | xml | IN | |
| @WAIVEBENEFITS | bit | IN | |
| @REGISTRANTMAPPINGS | xml | IN | |
| @DELETEDREGISTRANTREGISTRATIONMAPS | xml | IN | |
| @ISADD | bit | IN | |
| @BYPASSSECURITY | bit | IN | |
| @ISWALKIN | bit | IN |
Definition
Copy
CREATE procedure dbo.USP_REGISTRANT_UNIFIEDUPDATE
(
@ID uniqueidentifier = null output,
@CURRENTAPPUSERID uniqueidentifier = null,
@SECURITYCONTEXTFORMINSTANCEID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier = null,
@CURRENTDATE datetime = null,
@EVENTID uniqueidentifier,
@CONSTITUENTID uniqueidentifier,
@DATEPURCHASED datetime,
@PACKAGEREGISTRATIONS xml,
@PACKAGESPRICES xml,
@SINGLEEVENTREGISTRATIONS xml,
@WAIVEBENEFITS bit,
@REGISTRANTMAPPINGS xml,
@DELETEDREGISTRANTREGISTRATIONMAPS xml,
@ISADD bit = 0,
@BYPASSSECURITY bit = 0,
@ISWALKIN bit = 0
)
with execute as caller
as
set nocount on;
begin try
declare @ISADMIN bit;
set @ISADMIN = @BYPASSSECURITY;
if @ISADMIN = 0
set @ISADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);
if @ISADMIN = 0
begin
if not exists
(
select
SITEID
from
dbo.UFN_SITEID_MAPFROM_CONSTITUENTID(@CONSTITUENTID) SITES
where
dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_FORSITE(@CURRENTAPPUSERID, @SECURITYCONTEXTFORMINSTANCEID, SITES.SITEID) = 1
)
or dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_FORCONSTIT(@CURRENTAPPUSERID, @SECURITYCONTEXTFORMINSTANCEID, @CONSTITUENTID) <> 1
begin
raiserror ('ERR_CONSTITUENT_RECORDSECURITY_PERMISSION_DENIED',13,1);
return 1;
end
end
if @ID is null
set @ID = newid();
declare @REGISTRANTEXISTSWITHID bit;
if exists(select REGISTRANT.ID from dbo.REGISTRANT where REGISTRANT.ID = @ID)
set @REGISTRANTEXISTSWITHID = 1;
else
set @REGISTRANTEXISTSWITHID = 0;
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
set @CURRENTDATE = getdate();
declare @CONTEXTCACHE varbinary(128);
declare @UNKNOWNGUESTWELLKNOWNGUID uniqueidentifier;
set @UNKNOWNGUESTWELLKNOWNGUID = 'C776DAB5-65B2-4258-ADAE-AE396D28E251'; --Well-known GUID for unknown guest
declare @REGISTRANTWELLKNOWNGUID uniqueidentifier;
set @REGISTRANTWELLKNOWNGUID = '0F87025C-8FCA-4392-9780-45E4569DC331'; --Well-known GUID for registrant
declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
--List the registrations that will be created or updated
declare @REGISTRATIONTOADDTABLE table
(
REGISTRATIONSCOLLECTIONID uniqueidentifier,
EVENTID uniqueidentifier,
EVENTPRICEID uniqueidentifier,
AMOUNT money,
RECEIPTAMOUNT money,
QUANTITY int,
DATEPURCHASED datetime,
REGISTRATIONPACKAGEID uniqueidentifier,
REGISTRANTREGISTRATIONID uniqueidentifier,
NEWREGISTRANTREGISTRATION bit
);
insert into @REGISTRATIONTOADDTABLE
(
REGISTRATIONSCOLLECTIONID,
EVENTID,
EVENTPRICEID,
AMOUNT,
RECEIPTAMOUNT,
QUANTITY,
DATEPURCHASED,
REGISTRATIONPACKAGEID,
REGISTRANTREGISTRATIONID,
NEWREGISTRANTREGISTRATION
)
select
SELECTEDPACKAGE.PACKAGEREGISTRATIONID,
EVENTPRICE.EVENTID,
PACKAGEPRICE.EVENTPRICEID,
coalesce(SELECTEDPACKAGEREGISTRANTREGISTRATION.AMOUNT, (EVENTPRICE.AMOUNT * SELECTEDPACKAGE.QUANTITY)),
coalesce(SELECTEDPACKAGEREGISTRANTREGISTRATION.RECEIPTAMOUNT, (EVENTPRICE.RECEIPTAMOUNT * SELECTEDPACKAGE.QUANTITY)),
SELECTEDPACKAGE.QUANTITY,
@DATEPURCHASED,
SELECTEDPACKAGE.PACKAGEREGISTRATIONPACKAGEID,
SELECTEDPACKAGEREGISTRANTREGISTRATION.PACKAGEREGISTRANTREGISTRATIONID,
0
from
(
--Directly selecting from collection field rather than using FROMITEMLISTXML function
SELECT
T.c.value('(PACKAGEREGISTRATIONID)[1]','uniqueidentifier') AS 'PACKAGEREGISTRATIONID',
T.c.value('(PACKAGEREGISTRATIONPACKAGEID)[1]','uniqueidentifier') AS 'PACKAGEREGISTRATIONPACKAGEID',
T.c.value('(QUANTITY)[1]','int') AS 'QUANTITY',
T.c.query('(PACKAGEREGISTRANTREGISTRATIONS)[1]') AS 'PACKAGEREGISTRANTREGISTRATIONS'
FROM @PACKAGEREGISTRATIONS.nodes('/PACKAGEREGISTRATIONS/ITEM') T(c)
) SELECTEDPACKAGE
inner join dbo.UFN_REGISTRATIONPACKAGE_GETPACKAGESPRICES_FROMITEMLISTXML(@PACKAGESPRICES) PACKAGEPRICE on SELECTEDPACKAGE.PACKAGEREGISTRATIONPACKAGEID = PACKAGEPRICE.ID
inner join dbo.EVENTPRICE on PACKAGEPRICE.EVENTPRICEID = EVENTPRICE.ID
cross apply
(
--Directly selecting from collection field rather than using FROMITEMLISTXML function
SELECT
T.c.value('(PACKAGEREGISTRANTREGISTRATIONID)[1]','uniqueidentifier') AS 'PACKAGEREGISTRANTREGISTRATIONID',
T.c.value('(AMOUNT)[1]','money') AS 'AMOUNT',
T.c.value('(RECEIPTAMOUNT)[1]','money') AS 'RECEIPTAMOUNT'
FROM SELECTEDPACKAGE.PACKAGEREGISTRANTREGISTRATIONS.nodes('./PACKAGEREGISTRANTREGISTRATIONS/ITEM') T(c)
WHERE T.c.value('(PACKAGEEVENTPRICEID)[1]','uniqueidentifier') = PACKAGEPRICE.EVENTPRICEID
) SELECTEDPACKAGEREGISTRANTREGISTRATION;
insert into @REGISTRATIONTOADDTABLE
(
REGISTRATIONSCOLLECTIONID,
EVENTID,
EVENTPRICEID,
AMOUNT,
RECEIPTAMOUNT,
QUANTITY,
DATEPURCHASED,
REGISTRATIONPACKAGEID,
REGISTRANTREGISTRATIONID,
NEWREGISTRANTREGISTRATION
)
select
SELECTEDREGISTRATION.SINGLEEVENTREGISTRATIONID,
EVENTPRICE.EVENTID,
SELECTEDREGISTRATION.EVENTPRICEID,
SELECTEDREGISTRATION.AMOUNT,
SELECTEDREGISTRATION.RECEIPTAMOUNT,
SELECTEDREGISTRATION.QUANTITY,
@DATEPURCHASED,
null,
SELECTEDREGISTRATION.SINGLEEVENTREGISTRANTREGISTRATIONID,
0
from
(
--Directly selecting from collection field rather than using FROMITEMLISTXML function
SELECT
T.c.value('(SINGLEEVENTREGISTRATIONID)[1]','uniqueidentifier') AS 'SINGLEEVENTREGISTRATIONID',
T.c.value('(SINGLEEVENTREGISTRATIONEVENTPRICEID)[1]','uniqueidentifier') AS 'EVENTPRICEID',
T.c.value('(QUANTITY)[1]','int') AS 'QUANTITY',
T.c.value('(AMOUNT)[1]','money') AS 'AMOUNT',
T.c.value('(RECEIPTAMOUNT)[1]','money') AS 'RECEIPTAMOUNT',
T.c.value('(REGISTRATIONCOUNT)[1]','int') AS 'REGISTRATIONCOUNT',
T.c.value('(SINGLEEVENTREGISTRANTREGISTRATIONID)[1]','uniqueidentifier') AS 'SINGLEEVENTREGISTRANTREGISTRATIONID'
FROM @SINGLEEVENTREGISTRATIONS.nodes('/SINGLEEVENTREGISTRATIONS/ITEM') T(c)
) SELECTEDREGISTRATION
inner join dbo.EVENTPRICE on SELECTEDREGISTRATION.EVENTPRICEID = EVENTPRICE.ID;
--List the registration mappings
declare @REGISTRANTMAPPINGTABLE table
(
REGISTRANTPACKAGEID uniqueidentifier,
REGISTRATIONPACKAGEID uniqueidentifier,
EVENTID uniqueidentifier,
EVENTPRICEID uniqueidentifier,
REGISTRATIONSCOLLECTIONID uniqueidentifier,
REGISTRANTREGISTRATIONMAPS xml,
REGISTRANTWAIVEBENEFITS xml,
CONSTITUENTID uniqueidentifier,
DONOTMAPTOUNKNOWNGUEST bit, --For unmapped registrations, the user is not forced to map them to an unknown guest
TEAMFUNDRAISING xml,
PREFERENCES xml,
MAPPINGID uniqueidentifier default newid(), --Used to connect preferences to unknown guest registrant IDs
ONLINEREGISTRANT bit,
BATCHREVENUEREGISTRANTID uniqueidentifier --Keep the same ID as the batch row
);
insert into @REGISTRANTMAPPINGTABLE
(
REGISTRANTPACKAGEID,
REGISTRATIONPACKAGEID,
EVENTID,
EVENTPRICEID,
REGISTRATIONSCOLLECTIONID,
REGISTRANTREGISTRATIONMAPS,
REGISTRANTWAIVEBENEFITS,
CONSTITUENTID,
TEAMFUNDRAISING,
PREFERENCES,
DONOTMAPTOUNKNOWNGUEST,
ONLINEREGISTRANT,
BATCHREVENUEREGISTRANTID
)
--Directly selecting from collection field rather than using FROMITEMLISTXML function
SELECT
T.c.value('(REGISTRANTPACKAGEID)[1]','uniqueidentifier') AS 'REGISTRANTPACKAGEID',
T.c.value('(REGISTRATIONPACKAGEID)[1]','uniqueidentifier') AS 'REGISTRATIONPACKAGEID',
T.c.value('(EVENTID)[1]','uniqueidentifier') AS 'EVENTID',
T.c.value('(EVENTPRICEID)[1]','uniqueidentifier') AS 'EVENTPRICEID',
T.c.value('(REGISTRATIONSCOLLECTIONID)[1]','uniqueidentifier') AS 'REGISTRATIONSCOLLECTIONID',
T.c.query('(REGISTRANTREGISTRATIONMAPS)[1]') AS 'REGISTRANTREGISTRATIONMAPS',
T.c.query('(REGISTRANTWAIVEBENEFITS)[1]') AS 'REGISTRANTWAIVEBENEFITS',
T.c.value('(GUESTCONSTITUENTID)[1]','uniqueidentifier') AS 'GUESTCONSTITUENTID',
T.c.query('(TEAMFUNDRAISING)[1]') AS 'TEAMFUNDRAISING',
T.c.query('(PREFERENCES)[1]') AS 'PREFERENCES',
case
when T.c.value('(GUESTCONSTITUENTID)[1]','uniqueidentifier') is null then 1
else 0
end,
isnull(T.c.value('(ONLINEREGISTRANT)[1]','bit'),0) AS 'ONLINEREGISTRANT',
T.c.value('(BATCHREVENUEREGISTRANTID)[1]','uniqueidentifier') AS 'BATCHREVENUEREGISTRANTID'
FROM @REGISTRANTMAPPINGS.nodes('/REGISTRANTMAPPINGS/ITEM') T(c);
if @ISADMIN = 0 and exists
(
select top 1
1
from
@REGISTRANTMAPPINGTABLE as REGISTRANTMAP
inner join dbo.EVENTSITE on EVENTSITE.EVENTID = REGISTRANTMAP.EVENTID
where
not exists
(
select top 1
1
from
dbo.EVENTSITE
where
EVENTSITE.EVENTID = REGISTRANTMAP.EVENTID
and dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_FORSITE(@CURRENTAPPUSERID, @SECURITYCONTEXTFORMINSTANCEID, EVENTSITE.SITEID) = 1
)
)
begin
raiserror('BBERR_DB_RECORDSECURITY_PERMISSION_DENIED',13,1)
return 1;
end
--Replace the well-known GUIDs for registrant mapping with the actual IDs
update @REGISTRANTMAPPINGTABLE
set
CONSTITUENTID = @CONSTITUENTID
where
CONSTITUENTID = @REGISTRANTWELLKNOWNGUID ; --Well-known GUID for registrant
update @REGISTRANTMAPPINGTABLE
set
CONSTITUENTID = null
where
CONSTITUENTID = @UNKNOWNGUESTWELLKNOWNGUID; --Well-known GUID for unknown guest
--List the event-to-constituent mappings by expanding the package mappings
declare @REGISTRANTEVENTMAPPINGTABLE table
(
REGISTRANTID uniqueidentifier, --Used to keep track of unknown guests
MAPPINGID uniqueidentifier, --Used to map the registrant ID for unknown guests back to preferences in @REGISTRANTMAPPINGTABLE
EVENTID uniqueidentifier,
EVENTPRICEID uniqueidentifier,
CONSTITUENTID uniqueidentifier,
REGISTRANTREGISTRATIONMAPID uniqueidentifier,
NEWREGISTRANTREGISTRATIONMAP bit,
WAIVEBENEFITS bit,
REGISTRANTIDISNEWID bit,
OLDREGISTRANTID uniqueidentifier,
DONOTMAPTOUNKNOWNGUEST bit,
ONLINEREGISTRANT bit,
BATCHREVENUEREGISTRANTID uniqueidentifier
);
insert into @REGISTRANTEVENTMAPPINGTABLE
(
REGISTRANTID,
MAPPINGID,
EVENTID,
EVENTPRICEID,
CONSTITUENTID,
REGISTRANTREGISTRATIONMAPID,
NEWREGISTRANTREGISTRATIONMAP,
WAIVEBENEFITS,
DONOTMAPTOUNKNOWNGUEST,
ONLINEREGISTRANT,
BATCHREVENUEREGISTRANTID
)
select
REGISTRANTREGISTRATIONMAPITEM.REGISTRANTID,
REGISTRANTMAPPING.MAPPINGID,
coalesce(EVENTPRICE.EVENTID, REGISTRANTMAPPING.EVENTID, @EVENTID),
coalesce(EVENTPRICE.ID, REGISTRANTMAPPING.EVENTPRICEID),
case
--Override for registrant mappings that correspond to multiple constituents
when REGISTRANTREGISTRATIONMAPITEM.REGISTRANTID is not null and REGISTRANTREGISTRATIONMAPITEM.CONSTITUENTID = @UNKNOWNGUESTWELLKNOWNGUID
then null
when REGISTRANTMAPPING.REGISTRANTREGISTRATIONMAPS.exist('/REGISTRANTREGISTRATIONMAPS/ITEM') = 1
then REGISTRANTREGISTRATIONMAPITEM.CONSTITUENTID
else REGISTRANTMAPPING.CONSTITUENTID
end,
REGISTRANTREGISTRATIONMAPITEM.REGISTRANTREGISTRATIONMAPID,
case when REGISTRANTREGISTRATIONMAPITEM.REGISTRANTREGISTRATIONMAPID is null then 1 else 0 end,
coalesce(REGISTRANTWAIVEBENEFITSITEM.WAIVEBENEFITS, 0),
case
--Override for registrant mappings that correspond to multiple constituents
when REGISTRANTMAPPING.REGISTRANTREGISTRATIONMAPS.exist('/REGISTRANTREGISTRATIONMAPS/ITEM') = 1
then 1
else REGISTRANTMAPPING.DONOTMAPTOUNKNOWNGUEST
end,
REGISTRANTMAPPING.ONLINEREGISTRANT,
REGISTRANTMAPPING.BATCHREVENUEREGISTRANTID
from
@REGISTRANTMAPPINGTABLE REGISTRANTMAPPING
left join dbo.UFN_REGISTRATIONPACKAGE_GETPACKAGESPRICES_FROMITEMLISTXML(@PACKAGESPRICES) PACKAGEPRICE on REGISTRANTMAPPING.REGISTRATIONPACKAGEID = PACKAGEPRICE.ID
left join dbo.EVENTPRICE on PACKAGEPRICE.EVENTPRICEID = EVENTPRICE.ID
outer apply
(
--Directly selecting from collection field rather than using FROMITEMLISTXML function
SELECT
T.c.value('(REGISTRANTID)[1]', 'uniqueidentifier') AS 'REGISTRANTID',
T.c.value('(REGISTRANTREGISTRATIONMAPID)[1]', 'uniqueidentifier') AS 'REGISTRANTREGISTRATIONMAPID',
T.c.value('(CONSTITUENTID)[1]', 'uniqueidentifier') AS 'CONSTITUENTID'
FROM REGISTRANTMAPPING.REGISTRANTREGISTRATIONMAPS.nodes('/REGISTRANTREGISTRATIONMAPS/ITEM') T(c)
WHERE T.c.value('(EVENTID)[1]', 'uniqueidentifier') = EVENTPRICE.EVENTID
or T.c.value('(EVENTID)[1]', 'uniqueidentifier') = REGISTRANTMAPPING.EVENTID
) REGISTRANTREGISTRATIONMAPITEM
outer apply
(
--Directly selecting from collection field rather than using FROMITEMLISTXML function
SELECT
T.c.value('(WAIVEBENEFITS)[1]', 'bit') AS 'WAIVEBENEFITS'
FROM REGISTRANTMAPPING.REGISTRANTWAIVEBENEFITS.nodes('/REGISTRANTWAIVEBENEFITS/ITEM') T(c)
WHERE T.c.value('(EVENTID)[1]', 'uniqueidentifier') = EVENTPRICE.EVENTID
or T.c.value('(EVENTID)[1]', 'uniqueidentifier') = REGISTRANTMAPPING.EVENTID
) REGISTRANTWAIVEBENEFITSITEM;
--Generate a REGISTRANTID for unknown guests that are replacing guests that were loaded
update @REGISTRANTEVENTMAPPINGTABLE
set
REGISTRANTID = newid(),
REGISTRANTIDISNEWID = 1,
OLDREGISTRANTID = MAPPING.REGISTRANTID
from
@REGISTRANTEVENTMAPPINGTABLE MAPPING
left join dbo.REGISTRANT [SOURCEREGISTRANT] on MAPPING.REGISTRANTID = [SOURCEREGISTRANT].ID
where
MAPPING.REGISTRANTID is not null
and MAPPING.CONSTITUENTID is null
and [SOURCEREGISTRANT].CONSTITUENTID is not null;
--Set a REGISTRANTID for unknown guests added through batch
update [EVENTMAPPING]
set
REGISTRANTID = REGISTRANTMAPPING.BATCHREVENUEREGISTRANTID,
REGISTRANTIDISNEWID = 1
from
@REGISTRANTEVENTMAPPINGTABLE [EVENTMAPPING]
inner join @REGISTRANTMAPPINGTABLE REGISTRANTMAPPING on REGISTRANTMAPPING.MAPPINGID = [EVENTMAPPING].MAPPINGID
where
[EVENTMAPPING].REGISTRANTID is null
and [EVENTMAPPING].CONSTITUENTID is null
and [EVENTMAPPING].DONOTMAPTOUNKNOWNGUEST = 0
and REGISTRANTMAPPING.BATCHREVENUEREGISTRANTID is not null;
--Generate a REGISTRANTID for new unknown guests
update @REGISTRANTEVENTMAPPINGTABLE
set
REGISTRANTID = newid(),
REGISTRANTIDISNEWID = 1
from
@REGISTRANTEVENTMAPPINGTABLE
where
REGISTRANTID is null
and CONSTITUENTID is null
and DONOTMAPTOUNKNOWNGUEST = 0;
--If this is an edit and the host registrant was removed, mark the host to WILLNOTATTEND=1
--and remove from the REGISTRANTREGISTRATIONMAP table. (See Bug 55645.)
if @REGISTRANTEXISTSWITHID = 1 and exists (select ID from dbo.REGISTRANTREGISTRATIONMAP where REGISTRANTID = @ID)
and not exists (select CONSTITUENTID from @REGISTRANTMAPPINGTABLE where CONSTITUENTID in
(select CONSTITUENTID from dbo.REGISTRANT where REGISTRANT.ID = @ID))
begin
update dbo.REGISTRANT set
ATTENDED = 0,
WILLNOTATTEND = 1,
USERMARKEDATTENDANCE = 0,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
ID = @ID;
delete dbo.REGISTRANTREGISTRATIONMAP where REGISTRANTID = @ID
end
--If this is an add operation (not an edit) and there are no registrations then add the
--constituent to the main event
if not exists(select MAPPING.EVENTID from @REGISTRANTEVENTMAPPINGTABLE MAPPING)
and @REGISTRANTEXISTSWITHID = 0
begin
insert into dbo.REGISTRANT
(
[ID],
[EVENTID],
[CONSTITUENTID],
[ATTENDED],
[WILLNOTATTEND],
[GUESTOFREGISTRANTID],
[BENEFITSWAIVED],
[ISWALKIN],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
values
(
@ID,
@EVENTID,
@CONSTITUENTID,
@ISWALKIN, --Walk-in registrants attended by definition, pre-registrations set to not attended by default.
0, --Will attend
null,
@WAIVEBENEFITS,
@ISWALKIN,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
--default registrant restriction options from the constituent record
insert into dbo.REGISTRANTRESTRICTIONOPTION(REGISTRANTID, EVENTRESTRICTIONOPTIONID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
select
@ID,
CONSTITUENTRESTRICTIONOPTION.EVENTRESTRICTIONOPTIONID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
dbo.CONSTITUENTRESTRICTIONOPTION
left join dbo.REGISTRANTRESTRICTIONOPTION on REGISTRANTRESTRICTIONOPTION.REGISTRANTID = @ID and REGISTRANTRESTRICTIONOPTION.EVENTRESTRICTIONOPTIONID = CONSTITUENTRESTRICTIONOPTION.EVENTRESTRICTIONOPTIONID
where
CONSTITUENTRESTRICTIONOPTION.CONSTITUENTID = @CONSTITUENTID
and REGISTRANTRESTRICTIONOPTION.ID is null
and exists(select 1 from dbo.REGISTRANT where REGISTRANT.ID = @ID);
end
--Add a registrant row for events with registrations
--Filter for existing registrant records, which would cause an error; adding
--a registrant to an event where they are already registered implies adding registrations.
--Existing registrants may already be marked with WILLNOTATTEND as false and
--that value will not be changed, those with WILLNOTATTEND set to true will be updated
--in the next statement.
--Use the passed in ID when adding a registrant to the passed in event.
if @REGISTRANTEXISTSWITHID = 0
begin
insert into dbo.REGISTRANT
(
[ID],
[EVENTID],
[CONSTITUENTID],
[ATTENDED],
[WILLNOTATTEND],
[GUESTOFREGISTRANTID],
[BENEFITSWAIVED],
[ISWALKIN],
[ONLINEREGISTRANT],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
select distinct
@ID,
@EVENTID,
@CONSTITUENTID,
case @ISWALKIN
when 0 then 0 -- Not attended by default.
else case WILLATTEND.CNT when 0 then 0 else 1 end -- Assume walk-ins are attending unless willnotattend=1
end,
case WILLATTEND.CNT
when 0 then 1 --Will not attend
else 0 --Will attend (the registrant is mapped to a registration for this event)
end, --CR261256-120706 by determining the correct WILLNOTATTEND value in this insert we avoid an unneeded row in the audit table
null,
case
when exists
(
select
WAIVEBENEFITSMAP.CONSTITUENTID
from
@REGISTRANTEVENTMAPPINGTABLE WAIVEBENEFITSMAP
where
WAIVEBENEFITSMAP.EVENTID = MAPPING.EVENTID
and WAIVEBENEFITSMAP.CONSTITUENTID = @CONSTITUENTID
and WAIVEBENEFITSMAP.WAIVEBENEFITS = 1
)
then
1 --Waive benefits, there is at least one waive benefits row
else
0 --Do not waive benefits
end,
@ISWALKIN,
MAPPING.ONLINEREGISTRANT,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
@REGISTRANTEVENTMAPPINGTABLE MAPPING
left join dbo.REGISTRANT on
MAPPING.EVENTID = REGISTRANT.EVENTID
and @CONSTITUENTID = REGISTRANT.CONSTITUENTID
cross apply (select count(*) CNT
from @REGISTRANTEVENTMAPPINGTABLE MAPPINGFORCONSTITUENT
where MAPPINGFORCONSTITUENT.EVENTID = MAPPING.EVENTID
and MAPPINGFORCONSTITUENT.CONSTITUENTID = @CONSTITUENTID) WILLATTEND
where
REGISTRANT.ID is null
and MAPPING.EVENTID = @EVENTID;
--default registrant restriction options from the constituent record
insert into dbo.REGISTRANTRESTRICTIONOPTION(REGISTRANTID, EVENTRESTRICTIONOPTIONID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
select
@ID,
CONSTITUENTRESTRICTIONOPTION.EVENTRESTRICTIONOPTIONID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
dbo.CONSTITUENTRESTRICTIONOPTION
left join dbo.REGISTRANTRESTRICTIONOPTION on REGISTRANTRESTRICTIONOPTION.REGISTRANTID = @ID and REGISTRANTRESTRICTIONOPTION.EVENTRESTRICTIONOPTIONID = CONSTITUENTRESTRICTIONOPTION.EVENTRESTRICTIONOPTIONID
where
CONSTITUENTRESTRICTIONOPTION.CONSTITUENTID = @CONSTITUENTID
and REGISTRANTRESTRICTIONOPTION.ID is null
and exists(select 1 from dbo.REGISTRANT where REGISTRANT.ID = @ID);
end
--Add new registrants to a temp table so we know their IDs and can more easily default restrictions
declare @NEWREGISTRANTS table
(
REGISTRANTID uniqueidentifier default newid(),
EVENTID uniqueidentifier,
CONSTITUENTID uniqueidentifier,
ATTENDED bit,
WILLNOTATTEND bit,
GUESTOFREGISTRANTID uniqueidentifier,
BENEFITSWAIVED bit,
ISWALKIN bit,
ONLINEREGISTRANT bit
);
-- making copy for default designations
declare @COPYOFNEWREGISTRANTS table
(
REGISTRANTID uniqueidentifier,
EVENTID uniqueidentifier,
CONSTITUENTID uniqueidentifier
);
insert into @NEWREGISTRANTS
(
EVENTID,
CONSTITUENTID,
ATTENDED,
WILLNOTATTEND,
GUESTOFREGISTRANTID,
BENEFITSWAIVED,
ISWALKIN,
ONLINEREGISTRANT
)
select distinct
MAPPING.EVENTID,
@CONSTITUENTID,
case @ISWALKIN
when 0 then 0 -- Not attended by default.
else case WILLATTEND.CNT when 0 then 0 else 1 end -- Assume walk-ins are attending unless willnotattend=1
end,
case WILLATTEND.CNT
when 0 then 1 --Will not attend
else 0 --Will attend (the registrant is mapped to a registration for this event)
end, --CR261256-120706 by determining the correct WILLNOTATTEND value in this insert we avoid an unneeded row in the audit table
null,
case
when exists
(
select
WAIVEBENEFITSMAP.CONSTITUENTID
from
@REGISTRANTEVENTMAPPINGTABLE WAIVEBENEFITSMAP
where
WAIVEBENEFITSMAP.EVENTID = MAPPING.EVENTID
and WAIVEBENEFITSMAP.CONSTITUENTID = @CONSTITUENTID
and WAIVEBENEFITSMAP.WAIVEBENEFITS = 1
)
then
1 --Waive benefits, there is at least one waive benefits row
else
0 --Do not waive benefits
end,
@ISWALKIN,
MAPPING.ONLINEREGISTRANT
from
@REGISTRANTEVENTMAPPINGTABLE MAPPING
left join dbo.REGISTRANT on
MAPPING.EVENTID = REGISTRANT.EVENTID
and @CONSTITUENTID = REGISTRANT.CONSTITUENTID
cross apply (select count(*) CNT
from @REGISTRANTEVENTMAPPINGTABLE MAPPINGFORCONSTITUENT
where MAPPINGFORCONSTITUENT.EVENTID = MAPPING.EVENTID
and MAPPINGFORCONSTITUENT.CONSTITUENTID = @CONSTITUENTID) WILLATTEND
where
REGISTRANT.ID is null;
update NEWREGISTRANTS set
REGISTRANTID = MAPPING.BATCHREVENUEREGISTRANTID
from
@NEWREGISTRANTS as NEWREGISTRANTS
inner join @REGISTRANTEVENTMAPPINGTABLE as MAPPING on
MAPPING.EVENTID = NEWREGISTRANTS.EVENTID
and @CONSTITUENTID = NEWREGISTRANTS.CONSTITUENTID
where
MAPPING.BATCHREVENUEREGISTRANTID is not null;
insert into dbo.REGISTRANT
(
[ID],
[EVENTID],
[CONSTITUENTID],
[ATTENDED],
[WILLNOTATTEND],
[GUESTOFREGISTRANTID],
[BENEFITSWAIVED],
[ISWALKIN],
[ONLINEREGISTRANT],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
select REGISTRANTID, EVENTID, CONSTITUENTID, ATTENDED, WILLNOTATTEND, GUESTOFREGISTRANTID, BENEFITSWAIVED, ISWALKIN, ONLINEREGISTRANT, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
from @NEWREGISTRANTS
-- Do not add any more registrants. we only need the main registrants for all events - no guests
insert into @COPYOFNEWREGISTRANTS
(REGISTRANTID, EVENTID, CONSTITUENTID)
select REGISTRANTID, EVENTID, CONSTITUENTID
from @NEWREGISTRANTS
--default event restrictions
insert into dbo.REGISTRANTRESTRICTIONOPTION(REGISTRANTID, EVENTRESTRICTIONOPTIONID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
select
[NEWREGISTRANTS].REGISTRANTID,
CONSTITUENTRESTRICTIONOPTION.EVENTRESTRICTIONOPTIONID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
@NEWREGISTRANTS [NEWREGISTRANTS]
inner join dbo.CONSTITUENTRESTRICTIONOPTION on CONSTITUENTRESTRICTIONOPTION.CONSTITUENTID = [NEWREGISTRANTS].CONSTITUENTID
left join dbo.REGISTRANTRESTRICTIONOPTION on REGISTRANTRESTRICTIONOPTION.REGISTRANTID = [NEWREGISTRANTS].REGISTRANTID and REGISTRANTRESTRICTIONOPTION.EVENTRESTRICTIONOPTIONID = CONSTITUENTRESTRICTIONOPTION.EVENTRESTRICTIONOPTIONID
where
REGISTRANTRESTRICTIONOPTION.ID is null
and exists(select 1 from dbo.REGISTRANT where REGISTRANT.ID = [NEWREGISTRANTS].REGISTRANTID);
--since we've inserted these registrants, clear out the table so we can re-use it
delete @NEWREGISTRANTS
--Update the WILLNOTATTEND bit to 0 on events where the registrant is mapped to a
--registration and that registrant is currently marked as will not attend. Also update
--waive benefits flag at the same time.
update dbo.REGISTRANT
set
WILLNOTATTEND = case when REGISTRANT.WILLNOTATTEND = 0 then 0 else MAPPING.WILLNOTATTEND end,
BENEFITSWAIVED = MAPPING.WAIVEBENEFITS,
/*ISWALKIN = @ISWALKIN,
ATTENDED = case
-- for walk-ins, set attended to 1 unless willnotattend=1
when @ISWALKIN = 1 then case case when REGISTRANT.WILLNOTATTEND = 0 then 0 else MAPPING.WILLNOTATTEND end when 1 then 0 else 1 end
else REGISTRANT.ATTENDED
end,*/
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from
(
select
MAPPINGAGGREGATE.EVENTID,
case
when exists
(
select
MAPPINGFORCONSTITUENT.CONSTITUENTID
from
@REGISTRANTEVENTMAPPINGTABLE MAPPINGFORCONSTITUENT
where
MAPPINGFORCONSTITUENT.EVENTID = MAPPINGAGGREGATE.EVENTID
and MAPPINGFORCONSTITUENT.CONSTITUENTID = @CONSTITUENTID
)
then
0 --Will attend (the registrant is mapped to a registration for this event)
else
1 --Will not attend
end [WILLNOTATTEND],
case
when exists
(
select
WAIVEBENEFITSMAP.CONSTITUENTID
from
@REGISTRANTEVENTMAPPINGTABLE WAIVEBENEFITSMAP
where
WAIVEBENEFITSMAP.EVENTID = MAPPINGAGGREGATE.EVENTID
and WAIVEBENEFITSMAP.CONSTITUENTID = @CONSTITUENTID
and WAIVEBENEFITSMAP.WAIVEBENEFITS = 1
)
then
1 --Waive benefits, there is at least one waive benefits row
else
0 --Do not waive benefits
end [WAIVEBENEFITS]
from
@REGISTRANTEVENTMAPPINGTABLE MAPPINGAGGREGATE
) MAPPING
inner join dbo.REGISTRANT on
MAPPING.EVENTID = REGISTRANT.EVENTID
and @CONSTITUENTID = REGISTRANT.CONSTITUENTID
where
(
REGISTRANT.WILLNOTATTEND = 1
and MAPPING.WILLNOTATTEND = 0
)
or REGISTRANT.BENEFITSWAIVED <> MAPPING.WAIVEBENEFITS
--or REGISTRANT.ISWALKIN <> @ISWALKIN
;
--Add a registrant row for the guests mapped to events (excluding unknown guests)
--Filter for existing registrant records where the guest is already a guest of the same constituent
--Allow errors where the guest is a guest of some other constituent for the same event, a
--constituent may only be a guest once per event
--Check record access security first
if @ISADMIN = 0
if exists
(
select
1
from
@REGISTRANTEVENTMAPPINGTABLE MAPPING
inner join dbo.REGISTRANT HOSTREGISTRANT on
MAPPING.EVENTID = HOSTREGISTRANT.EVENTID
and @CONSTITUENTID = HOSTREGISTRANT.CONSTITUENTID
left join dbo.REGISTRANT EXISTINGGUEST on
MAPPING.EVENTID = EXISTINGGUEST.EVENTID
and MAPPING.CONSTITUENTID = EXISTINGGUEST.CONSTITUENTID
and HOSTREGISTRANT.ID = EXISTINGGUEST.GUESTOFREGISTRANTID
where
MAPPING.CONSTITUENTID is not null
and MAPPING.CONSTITUENTID <> @CONSTITUENTID
and EXISTINGGUEST.ID is null
and dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_FORCONSTIT(@CURRENTAPPUSERID, @SECURITYCONTEXTFORMINSTANCEID, MAPPING.CONSTITUENTID) <> 1
)
or exists
(
select top 1
1
from
@REGISTRANTEVENTMAPPINGTABLE as MAPPING
inner join dbo.REGISTRANT HOSTREGISTRANT on
MAPPING.EVENTID = HOSTREGISTRANT.EVENTID
and @CONSTITUENTID = HOSTREGISTRANT.CONSTITUENTID
left join dbo.REGISTRANT EXISTINGGUEST on
MAPPING.EVENTID = EXISTINGGUEST.EVENTID
and MAPPING.CONSTITUENTID = EXISTINGGUEST.CONSTITUENTID
and HOSTREGISTRANT.ID = EXISTINGGUEST.GUESTOFREGISTRANTID
where
MAPPING.CONSTITUENTID is not null
and EXISTINGGUEST.ID is null
and not exists
(
select top 1
1
from
dbo.UFN_SITEID_MAPFROM_CONSTITUENTID(MAPPING.CONSTITUENTID) SITES
where
dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_FORSITE(@CURRENTAPPUSERID, @SECURITYCONTEXTFORMINSTANCEID, SITES.SITEID) = 1
)
)
begin
raiserror ('ERR_GUESTCONSTITUENT_RECORDSECURITY_PERMISSION_DENIED',13,1);
return 1;
end
insert into @NEWREGISTRANTS
(
EVENTID,
CONSTITUENTID,
ATTENDED,
WILLNOTATTEND,
GUESTOFREGISTRANTID,
BENEFITSWAIVED,
ISWALKIN,
ONLINEREGISTRANT
)
select distinct
MAPPING.EVENTID,
MAPPING.CONSTITUENTID,
@ISWALKIN, --Walk-in registrants attended by definition, pre-registrations set to not attended by default.
0, --Will attend
HOSTREGISTRANT.ID,
case
when exists
(
select
WAIVEBENEFITSMAP.CONSTITUENTID
from
@REGISTRANTEVENTMAPPINGTABLE WAIVEBENEFITSMAP
where
WAIVEBENEFITSMAP.EVENTID = MAPPING.EVENTID
and WAIVEBENEFITSMAP.CONSTITUENTID = MAPPING.CONSTITUENTID
and WAIVEBENEFITSMAP.WAIVEBENEFITS = 1
)
then
1 --Waive benefits, there is at least one waive benefits row
else
0 --Do not waive benefits
end,
@ISWALKIN,
MAPPING.ONLINEREGISTRANT
from
@REGISTRANTEVENTMAPPINGTABLE MAPPING
inner join dbo.REGISTRANT HOSTREGISTRANT on
MAPPING.EVENTID = HOSTREGISTRANT.EVENTID
and @CONSTITUENTID = HOSTREGISTRANT.CONSTITUENTID
left join dbo.REGISTRANT EXISTINGGUEST on
MAPPING.EVENTID = EXISTINGGUEST.EVENTID
and MAPPING.CONSTITUENTID = EXISTINGGUEST.CONSTITUENTID
and HOSTREGISTRANT.ID = EXISTINGGUEST.GUESTOFREGISTRANTID
where
MAPPING.CONSTITUENTID is not null
and MAPPING.CONSTITUENTID <> @CONSTITUENTID
and EXISTINGGUEST.ID is null;
update NEWREGISTRANTS set
REGISTRANTID = MAPPING.BATCHREVENUEREGISTRANTID
from
@NEWREGISTRANTS as NEWREGISTRANTS
inner join @REGISTRANTEVENTMAPPINGTABLE as MAPPING on
MAPPING.EVENTID = NEWREGISTRANTS.EVENTID
and MAPPING.CONSTITUENTID = NEWREGISTRANTS.CONSTITUENTID
where
MAPPING.BATCHREVENUEREGISTRANTID is not null;
insert into dbo.REGISTRANT
(
[ID],
[EVENTID],
[CONSTITUENTID],
[ATTENDED],
[WILLNOTATTEND],
[GUESTOFREGISTRANTID],
[BENEFITSWAIVED],
[ISWALKIN],
[ONLINEREGISTRANT],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
select REGISTRANTID, EVENTID, CONSTITUENTID, ATTENDED, WILLNOTATTEND, GUESTOFREGISTRANTID, BENEFITSWAIVED, ISWALKIN, ONLINEREGISTRANT, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
from @NEWREGISTRANTS
--default event restrictions
insert into dbo.REGISTRANTRESTRICTIONOPTION(REGISTRANTID, EVENTRESTRICTIONOPTIONID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
select
[NEWREGISTRANTS].REGISTRANTID,
CONSTITUENTRESTRICTIONOPTION.EVENTRESTRICTIONOPTIONID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
@NEWREGISTRANTS [NEWREGISTRANTS]
inner join dbo.CONSTITUENTRESTRICTIONOPTION on CONSTITUENTRESTRICTIONOPTION.CONSTITUENTID = [NEWREGISTRANTS].CONSTITUENTID
left join dbo.REGISTRANTRESTRICTIONOPTION on REGISTRANTRESTRICTIONOPTION.REGISTRANTID = [NEWREGISTRANTS].REGISTRANTID and REGISTRANTRESTRICTIONOPTION.EVENTRESTRICTIONOPTIONID = CONSTITUENTRESTRICTIONOPTION.EVENTRESTRICTIONOPTIONID
where
REGISTRANTRESTRICTIONOPTION.ID is null
and exists(select 1 from dbo.REGISTRANT where REGISTRANT.ID = [NEWREGISTRANTS].REGISTRANTID);
--since we've inserted these registrants, clear out the table so we can re-use it
delete @NEWREGISTRANTS
--Add a registrant row for the new unknown guests mapped to events
insert into @NEWREGISTRANTS
(
REGISTRANTID,
EVENTID,
CONSTITUENTID,
ATTENDED,
WILLNOTATTEND,
GUESTOFREGISTRANTID,
BENEFITSWAIVED,
ISWALKIN,
ONLINEREGISTRANT
)
select
MAPPING.REGISTRANTID,
MAPPING.EVENTID,
null,
@ISWALKIN, --Walk-in registrants attended by definition, pre-registrations set to not attended by default.
0, --Will attend
HOSTREGISTRANT.ID,
case
when exists
(
select
WAIVEBENEFITSMAP.CONSTITUENTID
from
@REGISTRANTEVENTMAPPINGTABLE WAIVEBENEFITSMAP
where
WAIVEBENEFITSMAP.EVENTID = MAPPING.EVENTID
and WAIVEBENEFITSMAP.REGISTRANTID = MAPPING.REGISTRANTID
and WAIVEBENEFITSMAP.WAIVEBENEFITS = 1
)
then
1 --Waive benefits, there is at least one waive benefits row
else
0 --Do not waive benefits
end,
@ISWALKIN,
MAPPING.ONLINEREGISTRANT
from
@REGISTRANTEVENTMAPPINGTABLE MAPPING
inner join dbo.REGISTRANT HOSTREGISTRANT on
MAPPING.EVENTID = HOSTREGISTRANT.EVENTID
and @CONSTITUENTID = HOSTREGISTRANT.CONSTITUENTID
where
MAPPING.CONSTITUENTID is null
and MAPPING.REGISTRANTIDISNEWID = 1;
insert into dbo.REGISTRANT
(
[ID],
[EVENTID],
[CONSTITUENTID],
[ATTENDED],
[WILLNOTATTEND],
[GUESTOFREGISTRANTID],
[BENEFITSWAIVED],
[ISWALKIN],
[ONLINEREGISTRANT],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
select REGISTRANTID, EVENTID, CONSTITUENTID, ATTENDED, WILLNOTATTEND, GUESTOFREGISTRANTID, BENEFITSWAIVED, ISWALKIN, ONLINEREGISTRANT, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
from @NEWREGISTRANTS
--default event restrictions
insert into dbo.REGISTRANTRESTRICTIONOPTION(REGISTRANTID, EVENTRESTRICTIONOPTIONID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
select
[NEWREGISTRANTS].REGISTRANTID,
CONSTITUENTRESTRICTIONOPTION.EVENTRESTRICTIONOPTIONID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
@NEWREGISTRANTS [NEWREGISTRANTS]
inner join dbo.CONSTITUENTRESTRICTIONOPTION on CONSTITUENTRESTRICTIONOPTION.CONSTITUENTID = [NEWREGISTRANTS].CONSTITUENTID
left join dbo.REGISTRANTRESTRICTIONOPTION on REGISTRANTRESTRICTIONOPTION.REGISTRANTID = [NEWREGISTRANTS].REGISTRANTID and REGISTRANTRESTRICTIONOPTION.EVENTRESTRICTIONOPTIONID = CONSTITUENTRESTRICTIONOPTION.EVENTRESTRICTIONOPTIONID
where
REGISTRANTRESTRICTIONOPTION.ID is null
and exists(select 1 from dbo.REGISTRANT where REGISTRANT.ID = [NEWREGISTRANTS].REGISTRANTID);
--since we've inserted these registrants, clear out the table so we can re-use it
delete @NEWREGISTRANTS
--Merge registrant records for guest that are now pointing to the same constituent.
update dbo.EVENTSEATINGSEAT
set
REGISTRANTID =
case
when MAPPING.CONSTITUENTID is null then MAPPING.REGISTRANTID
else [TARGETREGISTRANT].ID
end,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from
@REGISTRANTEVENTMAPPINGTABLE MAPPING
left join dbo.REGISTRANT [TARGETREGISTRANT] on MAPPING.CONSTITUENTID = [TARGETREGISTRANT].CONSTITUENTID and MAPPING.EVENTID = [TARGETREGISTRANT].EVENTID
left join dbo.REGISTRANT [SOURCEREGISTRANT] on coalesce(MAPPING.OLDREGISTRANTID, MAPPING.REGISTRANTID) = [SOURCEREGISTRANT].ID
left join dbo.EVENTSEATINGSEAT [TARGETREGISTRANTSEAT] on coalesce([TARGETREGISTRANT].ID, MAPPING.REGISTRANTID) = [TARGETREGISTRANTSEAT].REGISTRANTID
left join dbo.EVENTSEATINGSEAT on [SOURCEREGISTRANT].ID = EVENTSEATINGSEAT.REGISTRANTID
left join @REGISTRANTEVENTMAPPINGTABLE CURRENTLYMAPPEDREGISTRANTS on [SOURCEREGISTRANT].EVENTID = CURRENTLYMAPPEDREGISTRANTS.EVENTID and [SOURCEREGISTRANT].CONSTITUENTID = CURRENTLYMAPPEDREGISTRANTS.CONSTITUENTID
where
[TARGETREGISTRANTSEAT].ID is null --If the target registrant already has a seat, don't change it
and
(
[SOURCEREGISTRANT].CONSTITUENTID <> MAPPING.CONSTITUENTID
or
([SOURCEREGISTRANT].CONSTITUENTID is null and MAPPING.CONSTITUENTID is not null)
or
([SOURCEREGISTRANT].CONSTITUENTID is not null and MAPPING.CONSTITUENTID is null)
)
and
CURRENTLYMAPPEDREGISTRANTS.EVENTID is null; --Don't change a constituent's seat if they are still a registrant
update dbo.EVENTGROUPMEMBER
set
REGISTRANTID =
case
when MAPPING.CONSTITUENTID is null then MAPPING.REGISTRANTID
else [TARGETREGISTRANT].ID
end,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from
@REGISTRANTEVENTMAPPINGTABLE MAPPING
left join dbo.REGISTRANT [TARGETREGISTRANT] on MAPPING.CONSTITUENTID = [TARGETREGISTRANT].CONSTITUENTID and MAPPING.EVENTID = [TARGETREGISTRANT].EVENTID
left join dbo.REGISTRANT [SOURCEREGISTRANT] on coalesce(MAPPING.OLDREGISTRANTID, MAPPING.REGISTRANTID) = [SOURCEREGISTRANT].ID
left join dbo.EVENTGROUPMEMBER [TARGETREGISTRANTGROUPMEMBER] on coalesce([TARGETREGISTRANT].ID, MAPPING.REGISTRANTID) = [TARGETREGISTRANTGROUPMEMBER].REGISTRANTID
left join dbo.EVENTGROUPMEMBER on [SOURCEREGISTRANT].ID = EVENTGROUPMEMBER.REGISTRANTID
left join @REGISTRANTEVENTMAPPINGTABLE CURRENTLYMAPPEDREGISTRANTS on [SOURCEREGISTRANT].EVENTID = CURRENTLYMAPPEDREGISTRANTS.EVENTID and [SOURCEREGISTRANT].CONSTITUENTID = CURRENTLYMAPPEDREGISTRANTS.CONSTITUENTID
where
[TARGETREGISTRANTGROUPMEMBER].ID is null --If the target registrant already has a group membership, don't add another
and
(
[SOURCEREGISTRANT].CONSTITUENTID <> MAPPING.CONSTITUENTID
or
([SOURCEREGISTRANT].CONSTITUENTID is null and MAPPING.CONSTITUENTID is not null)
or
([SOURCEREGISTRANT].CONSTITUENTID is not null and MAPPING.CONSTITUENTID is null)
)
and
CURRENTLYMAPPEDREGISTRANTS.EVENTID is null; --Don't change a constituent's seat if they are still a registrant
update dbo.REGISTRANTPREFERENCE
set
REGISTRANTID =
case
when MAPPING.CONSTITUENTID is null then MAPPING.REGISTRANTID
else [TARGETREGISTRANT].ID
end,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from
@REGISTRANTEVENTMAPPINGTABLE MAPPING
left join dbo.REGISTRANT [TARGETREGISTRANT] on MAPPING.CONSTITUENTID = [TARGETREGISTRANT].CONSTITUENTID and MAPPING.EVENTID = [TARGETREGISTRANT].EVENTID
left join dbo.REGISTRANT [SOURCEREGISTRANT] on coalesce(MAPPING.OLDREGISTRANTID, MAPPING.REGISTRANTID) = [SOURCEREGISTRANT].ID
left join dbo.REGISTRANTPREFERENCEMAP on [MAPPING].[REGISTRANTREGISTRATIONMAPID] = REGISTRANTPREFERENCEMAP.REGISTRANTREGISTRATIONMAPID
left join dbo.REGISTRANTPREFERENCE on REGISTRANTPREFERENCEMAP.ID = REGISTRANTPREFERENCE.ID
where
(
[SOURCEREGISTRANT].CONSTITUENTID <> MAPPING.CONSTITUENTID
or
([SOURCEREGISTRANT].CONSTITUENTID is null and MAPPING.CONSTITUENTID is not null)
or
([SOURCEREGISTRANT].CONSTITUENTID is not null and MAPPING.CONSTITUENTID is null)
);
--Avoiding multiple updates on REGISTRANTREGISTRATIONMAP for deadlock WI 190892
declare @REGISTRANTREGISTRATIONMAP table (
ID uniqueidentifier,
REGISTRANTREGISTRATIONID uniqueidentifier,
REGISTRANTID uniqueidentifier
)
insert into @REGISTRANTREGISTRATIONMAP
select
REGISTRANTREGISTRATIONMAP.ID,
REGISTRANTREGISTRATIONMAP.REGISTRANTREGISTRATIONID,
REGISTRANTREGISTRATIONMAP.REGISTRANTID
from dbo.REGISTRANTREGISTRATIONMAP with (nolock)
inner join @REGISTRANTEVENTMAPPINGTABLE MAPPING
on REGISTRANTREGISTRATIONMAP.ID = [MAPPING].[REGISTRANTREGISTRATIONMAPID]
insert into @REGISTRANTREGISTRATIONMAP
select
REGISTRANTREGISTRATIONMAP.ID,
REGISTRANTREGISTRATIONMAP.REGISTRANTREGISTRATIONID,
REGISTRANTREGISTRATIONMAP.REGISTRANTID
from dbo.EVENT
inner join dbo.REGISTRANT on EVENT.ID = REGISTRANT.EVENTID
inner join dbo.[REGISTRANTREGISTRATIONMAP] with (nolock) on REGISTRANT.ID = REGISTRANTREGISTRATIONMAP.REGISTRANTID
where
not exists (select 1 from @REGISTRANTREGISTRATIONMAP [RRM] where REGISTRANTREGISTRATIONMAP.ID = [RRM].ID) and
(
(
EVENT.ID = @EVENTID
and
EVENT.MAINEVENTID is not null
)
or
EVENT.MAINEVENTID = @EVENTID
)
and
REGISTRANT.CONSTITUENTID = @CONSTITUENTID
delete [REGISTRANTREGISTRATIONMAP]
from @REGISTRANTREGISTRATIONMAP [REGISTRANTREGISTRATIONMAP]
inner join @DELETEDREGISTRANTREGISTRATIONMAPS.nodes('/DELETEDREGISTRANTREGISTRATIONMAPS/ITEM') T(c) on
T.c.value('(REGISTRANTREGISTRATIONMAPID)[1]','uniqueidentifier') = REGISTRANTREGISTRATIONMAP.ID;
update REGISTRANTBENEFIT
set REGISTRANTBENEFIT.REGISTRANTID =
case
when MAPPING.CONSTITUENTID is null then MAPPING.REGISTRANTID
else [TARGETREGISTRANT].ID
end
from @REGISTRANTREGISTRATIONMAP [REGISTRANTREGISTRATIONMAP]
inner join @REGISTRANTEVENTMAPPINGTABLE MAPPING on REGISTRANTREGISTRATIONMAP.ID = [MAPPING].[REGISTRANTREGISTRATIONMAPID]
left join dbo.REGISTRANT [TARGETREGISTRANT] on MAPPING.CONSTITUENTID = [TARGETREGISTRANT].CONSTITUENTID and (MAPPING.EVENTID = [TARGETREGISTRANT].EVENTID)
left join dbo.REGISTRANT [SOURCEREGISTRANT] on coalesce(MAPPING.OLDREGISTRANTID, MAPPING.REGISTRANTID) = [SOURCEREGISTRANT].ID
inner join dbo.REGISTRANTBENEFIT on REGISTRANTBENEFIT.REGISTRANTID=MAPPING.REGISTRANTID
where
(
[SOURCEREGISTRANT].CONSTITUENTID <> MAPPING.CONSTITUENTID
or
([SOURCEREGISTRANT].CONSTITUENTID is null and MAPPING.CONSTITUENTID is not null)
or
([SOURCEREGISTRANT].CONSTITUENTID is not null and MAPPING.CONSTITUENTID is null)
);
update [REGISTRANTREGISTRATIONMAP]
set REGISTRANTID =
case
when MAPPING.CONSTITUENTID is null then MAPPING.REGISTRANTID
else [TARGETREGISTRANT].ID
end
from @REGISTRANTREGISTRATIONMAP [REGISTRANTREGISTRATIONMAP]
inner join @REGISTRANTEVENTMAPPINGTABLE MAPPING on REGISTRANTREGISTRATIONMAP.ID = [MAPPING].[REGISTRANTREGISTRATIONMAPID]
left join dbo.REGISTRANT [TARGETREGISTRANT] on MAPPING.CONSTITUENTID = [TARGETREGISTRANT].CONSTITUENTID and MAPPING.EVENTID = [TARGETREGISTRANT].EVENTID
left join dbo.REGISTRANT [SOURCEREGISTRANT] on coalesce(MAPPING.OLDREGISTRANTID, MAPPING.REGISTRANTID) = [SOURCEREGISTRANT].ID
where
(
[SOURCEREGISTRANT].CONSTITUENTID <> MAPPING.CONSTITUENTID
or
([SOURCEREGISTRANT].CONSTITUENTID is null and MAPPING.CONSTITUENTID is not null)
or
([SOURCEREGISTRANT].CONSTITUENTID is not null and MAPPING.CONSTITUENTID is null)
);
--Set the CONTEXT_INFO for delete operations
/* cache current context information */
set @CONTEXTCACHE = CONTEXT_INFO();
/* set CONTEXT_INFO to @CHANGEAGENTID */
set CONTEXT_INFO @CHANGEAGENTID;
--Remove registrant preferences mapped to deleted registrant mappings
delete from dbo.REGISTRANTPREFERENCE
from
dbo.REGISTRANTPREFERENCE
inner join dbo.REGISTRANTPREFERENCEMAP on REGISTRANTPREFERENCE.ID = REGISTRANTPREFERENCEMAP.ID
inner join @DELETEDREGISTRANTREGISTRATIONMAPS.nodes('/DELETEDREGISTRANTREGISTRATIONMAPS/ITEM') T(c) on
T.c.value('(REGISTRANTREGISTRATIONMAPID)[1]','uniqueidentifier') = REGISTRANTPREFERENCEMAP.REGISTRANTREGISTRATIONMAPID;
--Remove deleted registrant mappings
delete from dbo.REGISTRANTREGISTRATIONMAP
from
dbo.REGISTRANTREGISTRATIONMAP
inner join @DELETEDREGISTRANTREGISTRATIONMAPS.nodes('/DELETEDREGISTRANTREGISTRATIONMAPS/ITEM') T(c) on
T.c.value('(REGISTRANTREGISTRATIONMAPID)[1]','uniqueidentifier') = REGISTRANTREGISTRATIONMAP.ID;
--Remove registrant registrations where the quantity has been reduced to zero
delete from dbo.REGISTRANTREGISTRATION
from
dbo.REGISTRANTREGISTRATION
inner join @REGISTRATIONTOADDTABLE SELECTEDREGISTRATION on REGISTRANTREGISTRATION.ID = SELECTEDREGISTRATION.REGISTRANTREGISTRATIONID
where
SELECTEDREGISTRATION.QUANTITY = 0;
--Remove deleted registrants if those registrants do not have other reasons to remain
declare @OBSOLETEREGISTRANTS table
(
ID uniqueidentifier
);
--For guests' registrant registration maps that were deleted or changed to a different constituent,
--delete the original guest registrant records if those registrants are not tied to other existing
--or new registrant registration maps.
insert into @OBSOLETEREGISTRANTS (ID)
select
REGISTRANT.ID
from
(
select
T.c.value('(REGISTRANTID)[1]','uniqueidentifier') [REGISTRANTID]
from
@DELETEDREGISTRANTREGISTRATIONMAPS.nodes('/DELETEDREGISTRANTREGISTRATIONMAPS/ITEM') T(c)
union all
select
REGISTRANT.ID
from
@REGISTRANTEVENTMAPPINGTABLE MAPPING
inner join dbo.REGISTRANT on coalesce(MAPPING.OLDREGISTRANTID, MAPPING.REGISTRANTID) = REGISTRANT.ID
left join @REGISTRANTEVENTMAPPINGTABLE CURRENTLYMAPPEDREGISTRANTS on REGISTRANT.EVENTID = CURRENTLYMAPPEDREGISTRANTS.EVENTID and REGISTRANT.CONSTITUENTID = CURRENTLYMAPPEDREGISTRANTS.CONSTITUENTID
where
(
REGISTRANT.CONSTITUENTID <> MAPPING.CONSTITUENTID
or
(REGISTRANT.CONSTITUENTID is not null and MAPPING.CONSTITUENTID is null)
or
(REGISTRANT.CONSTITUENTID is null and MAPPING.CONSTITUENTID is not null)
)
and
CURRENTLYMAPPEDREGISTRANTS.EVENTID is null
) [SELECTEDREGISTRANT]
inner join dbo.REGISTRANT on [SELECTEDREGISTRANT].[REGISTRANTID] = REGISTRANT.ID
left join @REGISTRANTREGISTRATIONMAP [REGISTRANTREGISTRATIONMAP] on REGISTRANT.ID = REGISTRANTREGISTRATIONMAP.REGISTRANTID
left join @REGISTRANTEVENTMAPPINGTABLE REGISTRANTEVENTMAPPING on
REGISTRANT.CONSTITUENTID = REGISTRANTEVENTMAPPING.CONSTITUENTID and
REGISTRANT.EVENTID = REGISTRANTEVENTMAPPING.EVENTID
left join @REGISTRANTMAPPINGTABLE REGISTRANTMAPPING on REGISTRANTEVENTMAPPING.MAPPINGID = REGISTRANTMAPPING.MAPPINGID
left join @REGISTRATIONTOADDTABLE SELECTEDREGISTRATION on
REGISTRANTMAPPING.REGISTRATIONSCOLLECTIONID = SELECTEDREGISTRATION.REGISTRATIONSCOLLECTIONID
and REGISTRANT.EVENTID = SELECTEDREGISTRATION.EVENTID
where
REGISTRANTREGISTRATIONMAP.ID is null
and
SELECTEDREGISTRATION.REGISTRATIONSCOLLECTIONID is null
and
(
REGISTRANT.CONSTITUENTID is null
or
REGISTRANT.CONSTITUENTID <> @CONSTITUENTID
);
--For the registrant's records on sub events, delete them if all guests on the event are going
--to be deleted and the registrant has no existing or new registration mappings.
insert into @OBSOLETEREGISTRANTS (ID)
select
REGISTRANT.ID
from
dbo.EVENT
inner join dbo.REGISTRANT on EVENT.ID = REGISTRANT.EVENTID
left join @REGISTRANTREGISTRATIONMAP [REGISTRANTREGISTRATIONMAP] on REGISTRANT.ID = REGISTRANTREGISTRATIONMAP.REGISTRANTID
left join @REGISTRANTEVENTMAPPINGTABLE REGISTRANTEVENTMAPPING on REGISTRANT.CONSTITUENTID = REGISTRANTEVENTMAPPING.CONSTITUENTID
left join @REGISTRANTMAPPINGTABLE REGISTRANTMAPPING on REGISTRANTEVENTMAPPING.MAPPINGID = REGISTRANTMAPPING.MAPPINGID
left join @REGISTRATIONTOADDTABLE SELECTEDREGISTRATION on
REGISTRANTMAPPING.REGISTRATIONSCOLLECTIONID = SELECTEDREGISTRATION.REGISTRATIONSCOLLECTIONID
and REGISTRANTEVENTMAPPING.EVENTID = SELECTEDREGISTRATION.EVENTID
left join
(
select
[GUEST].GUESTOFREGISTRANTID
from
dbo.REGISTRANT [GUEST]
left join @OBSOLETEREGISTRANTS OBSOLETEREGISTRANTS on [GUEST].ID = OBSOLETEREGISTRANTS.ID
where
OBSOLETEREGISTRANTS.ID is null
) [REMAININGGUEST] on REGISTRANT.ID = [REMAININGGUEST].GUESTOFREGISTRANTID
where
(
(
EVENT.ID = @EVENTID
and
EVENT.MAINEVENTID is not null
)
or
EVENT.MAINEVENTID = @EVENTID
)
and
REGISTRANT.CONSTITUENTID = @CONSTITUENTID
and
REGISTRANTREGISTRATIONMAP.ID is null
and
SELECTEDREGISTRATION.REGISTRATIONSCOLLECTIONID is null
and
[REMAININGGUEST].[GUESTOFREGISTRANTID] is null
and
(@ID is null or REGISTRANT.ID <> @ID);
--There is the possibility that the above selects could pick up registrants that are new, so make sure that their information is not
-- deleted by removing them from the list of obsoletes
delete from @OBSOLETEREGISTRANTS where ID in (select REGISTRANTID from @COPYOFNEWREGISTRANTS)
-- Delete any team fundraising information for the obsolete registrants if this is a team fundraising event
-- Deleting from TEAMFUNDRAISER cascade deletes from TEAMFUNDRAISINGTEAMMEMBER
delete from
dbo.TEAMFUNDRAISER
from
dbo.REGISTRANT
left join dbo.EVENT on REGISTRANT.EVENTID = EVENT.ID
left join dbo.TEAMFUNDRAISER on
REGISTRANT.CONSTITUENTID = TEAMFUNDRAISER.CONSTITUENTID
and EVENT.APPEALID = TEAMFUNDRAISER.APPEALID
where
REGISTRANT.ID in (select ID from @OBSOLETEREGISTRANTS OBSOLETEREGISTRANTS);
delete from
dbo.TEAMFUNDRAISINGTEAMCAPTAIN
from
dbo.REGISTRANT
inner join @OBSOLETEREGISTRANTS [OBSOLETEREGISTRANTS] on [REGISTRANT].[ID] = [OBSOLETEREGISTRANTS].[ID]
left join dbo.EVENT on REGISTRANT.EVENTID = EVENT.ID
left join dbo.TEAMFUNDRAISINGTEAM on EVENT.APPEALID = TEAMFUNDRAISINGTEAM.APPEALID
left join dbo.TEAMFUNDRAISINGTEAMCAPTAIN on
REGISTRANT.CONSTITUENTID = TEAMFUNDRAISINGTEAMCAPTAIN.CONSTITUENTID
and TEAMFUNDRAISINGTEAM.ID = TEAMFUNDRAISINGTEAMCAPTAIN.TEAMFUNDRAISINGTEAMID;
delete from dbo.EVENTGROUPMEMBER
where
EVENTGROUPMEMBER.REGISTRANTID in (select ID from @OBSOLETEREGISTRANTS OBSOLETEREGISTRANTS);
update dbo.EVENTSEATINGSEAT set
EVENTSEATINGSEAT.REGISTRANTID = null,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
EVENTSEATINGSEAT.REGISTRANTID in (select ID from @OBSOLETEREGISTRANTS OBSOLETEREGISTRANTS);
--Remove deleted packages
delete from dbo.REGISTRANTPACKAGE
from
dbo.REGISTRANTPACKAGE
inner join @DELETEDREGISTRANTREGISTRATIONMAPS.nodes('/DELETEDREGISTRANTREGISTRATIONMAPS/ITEM') T(c) on
T.c.value('(REGISTRANTPACKAGEID)[1]','uniqueidentifier') = REGISTRANTPACKAGE.ID;
--Update existing registrants with the waive benefits flag
declare @REGISTRANTWAIVEBENEFITSTABLE table (
REGISTRANTID uniqueidentifier,
WAIVEBENEFITS bit
);
with MAPPING_CTE as (
select
MAPPINGAGGREGATE.REGISTRANTID,
MAPPINGAGGREGATE.EVENTID,
MAPPINGAGGREGATE.CONSTITUENTID,
(
select top (1)
1 --Waive benefits, if there is at least one waive benefits row
from
@REGISTRANTEVENTMAPPINGTABLE WAIVEBENEFITSMAP
where
(
WAIVEBENEFITSMAP.REGISTRANTID = MAPPINGAGGREGATE.REGISTRANTID
or
(
WAIVEBENEFITSMAP.EVENTID = MAPPINGAGGREGATE.EVENTID
and
WAIVEBENEFITSMAP.CONSTITUENTID = MAPPINGAGGREGATE.CONSTITUENTID
)
)
and
WAIVEBENEFITSMAP.WAIVEBENEFITS = 1
) [WAIVEBENEFITS]
from
@REGISTRANTEVENTMAPPINGTABLE MAPPINGAGGREGATE
)
insert into @REGISTRANTWAIVEBENEFITSTABLE (REGISTRANTID, WAIVEBENEFITS)
select REGISTRANTID, WAIVEBENEFITS
from
(
select
REGISTRANT.ID as REGISTRANTID, coalesce(MAPPING.WAIVEBENEFITS, 0) as WAIVEBENEFITS
from MAPPING_CTE as MAPPING
inner join dbo.REGISTRANT on MAPPING.REGISTRANTID = REGISTRANT.ID
where
MAPPING.REGISTRANTID = REGISTRANT.ID and
not exists (select 1 from @OBSOLETEREGISTRANTS where [ID] = REGISTRANT.ID)
union
select
REGISTRANT.ID as REGISTRANTID, coalesce(MAPPING.WAIVEBENEFITS, 0) as WAIVEBENEFITS
from MAPPING_CTE as MAPPING
inner join dbo.REGISTRANT on
(
MAPPING.EVENTID = REGISTRANT.EVENTID
and
MAPPING.CONSTITUENTID = REGISTRANT.CONSTITUENTID
)
where not exists (select 1 from @OBSOLETEREGISTRANTS where [ID] = REGISTRANT.ID)
) as SUBQ;
update dbo.REGISTRANT
set
--WILLNOTATTEND = 0,
BENEFITSWAIVED = REGISTRANTWAIVEBENEFITS.WAIVEBENEFITS,
ISWALKIN = @ISWALKIN,
ATTENDED = case
-- for walk-ins, set attended to 1 unless willnotattend=1
when @ISWALKIN = 1 then case WILLNOTATTEND when 1 then 0 else 1 end
-- if changing from walk-in to preregister, the user didn't set to attended, and the event has not yet been processed, set attended to false
when ISWALKIN = 1 and USERMARKEDATTENDANCE = 0 and (select ISPROCESSED from dbo.EVENT where ID = REGISTRANT.EVENTID) = 0 then 0
-- otherwise leave attended alone
else REGISTRANT.ATTENDED
end,
-- if user marked attended no explicitly, but now is changing to walk-in (or marking will not attend), clear the usermarkedattendance flag
USERMARKEDATTENDANCE = case when @ISWALKIN = 1 and (ATTENDED = 0 or WILLNOTATTEND = 1) then 0 else USERMARKEDATTENDANCE end,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from
@REGISTRANTWAIVEBENEFITSTABLE REGISTRANTWAIVEBENEFITS
inner join dbo.REGISTRANT on REGISTRANT.ID = REGISTRANTWAIVEBENEFITS.REGISTRANTID
where
--REGISTRANT.WILLNOTATTEND = 1
(
REGISTRANT.BENEFITSWAIVED <> REGISTRANTWAIVEBENEFITS.WAIVEBENEFITS or
REGISTRANT.ISWALKIN <> @ISWALKIN
) and
not exists (select 1 from @OBSOLETEREGISTRANTS where [ID] = REGISTRANT.ID);
--Add the packages
update @REGISTRANTMAPPINGTABLE
set
REGISTRANTPACKAGEID = newid()
where
REGISTRATIONPACKAGEID <> '00000000-0000-0000-0000-000000000000'
and REGISTRANTPACKAGEID = '00000000-0000-0000-0000-000000000000';
insert into dbo.REGISTRANTPACKAGE
(
[ID],
[CONSTITUENTID],
[REGISTRATIONPACKAGEID],
[GUESTOFCONSTITUENTID],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
select
REGISTRATIONMAPPING.REGISTRANTPACKAGEID,
REGISTRATIONMAPPING.CONSTITUENTID,
REGISTRATIONMAPPING.REGISTRATIONPACKAGEID,
case
when REGISTRATIONMAPPING.CONSTITUENTID = @CONSTITUENTID then null
else @CONSTITUENTID
end,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
@REGISTRANTMAPPINGTABLE REGISTRATIONMAPPING
left join dbo.REGISTRANTPACKAGE on REGISTRATIONMAPPING.REGISTRANTPACKAGEID = REGISTRANTPACKAGE.ID
where
REGISTRATIONMAPPING.REGISTRATIONPACKAGEID <> '00000000-0000-0000-0000-000000000000'
and REGISTRANTPACKAGE.ID is null;
--Note: Ignore DATEPURCHASED when determining whether or not to update the registrant registration row.
update dbo.REGISTRANTREGISTRATION
set
EVENTPRICEID = SELECTEDREGISTRATION.EVENTPRICEID,
QUANTITY = SELECTEDREGISTRATION.QUANTITY,
AMOUNT = SELECTEDREGISTRATION.AMOUNT,
RECEIPTAMOUNT = SELECTEDREGISTRATION.RECEIPTAMOUNT,
ORGANIZATIONAMOUNT =
case
when EVENT.BASECURRENCYID = @ORGANIZATIONCURRENCYID
then SELECTEDREGISTRATION.AMOUNT
else
case
when REGISTRANTREGISTRATION.ORGANIZATIONEXCHANGERATEID is null
then dbo.UFN_CURRENCY_CONVERT(SELECTEDREGISTRATION.AMOUNT, dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(EVENT.BASECURRENCYID, @ORGANIZATIONCURRENCYID, @DATEPURCHASED, 0, null))
else
dbo.UFN_CURRENCY_CONVERT(SELECTEDREGISTRATION.AMOUNT, REGISTRANTREGISTRATION.ORGANIZATIONEXCHANGERATEID)
end
end,
ORGANIZATIONEXCHANGERATEID =
case
when EVENT.BASECURRENCYID = @ORGANIZATIONCURRENCYID
then null
else
case
when REGISTRANTREGISTRATION.ORGANIZATIONEXCHANGERATEID is null
then dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(EVENT.BASECURRENCYID, @ORGANIZATIONCURRENCYID, @DATEPURCHASED, 0, null)
else
REGISTRANTREGISTRATION.ORGANIZATIONEXCHANGERATEID
end
end,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from
dbo.REGISTRANTREGISTRATION
inner join @REGISTRATIONTOADDTABLE SELECTEDREGISTRATION on REGISTRANTREGISTRATION.ID = SELECTEDREGISTRATION.REGISTRANTREGISTRATIONID
inner join dbo.EVENT on SELECTEDREGISTRATION.EVENTID = EVENT.ID
where
REGISTRANTREGISTRATION.EVENTPRICEID <> SELECTEDREGISTRATION.EVENTPRICEID
or REGISTRANTREGISTRATION.QUANTITY <> SELECTEDREGISTRATION.QUANTITY
or REGISTRANTREGISTRATION.AMOUNT <> SELECTEDREGISTRATION.AMOUNT
or REGISTRANTREGISTRATION.RECEIPTAMOUNT <> SELECTEDREGISTRATION.RECEIPTAMOUNT;
update @REGISTRATIONTOADDTABLE
set
REGISTRANTREGISTRATIONID = newid(),
NEWREGISTRANTREGISTRATION = 1
where
REGISTRANTREGISTRATIONID is null;
--Add the registrations
insert into dbo.REGISTRANTREGISTRATION
(
ID,
REGISTRANTID,
EVENTPRICEID,
QUANTITY,
AMOUNT,
RECEIPTAMOUNT,
DATEPURCHASED,
ORGANIZATIONAMOUNT,
ORGANIZATIONEXCHANGERATEID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
SELECTEDREGISTRATION.REGISTRANTREGISTRATIONID,
REGISTRANT.ID,
SELECTEDREGISTRATION.EVENTPRICEID,
SELECTEDREGISTRATION.QUANTITY,
SELECTEDREGISTRATION.AMOUNT,
SELECTEDREGISTRATION.RECEIPTAMOUNT,
SELECTEDREGISTRATION.DATEPURCHASED,
case
when EVENT.BASECURRENCYID = @ORGANIZATIONCURRENCYID
then SELECTEDREGISTRATION.AMOUNT
else
dbo.UFN_CURRENCY_CONVERT(SELECTEDREGISTRATION.AMOUNT, dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(EVENT.BASECURRENCYID, @ORGANIZATIONCURRENCYID, @DATEPURCHASED, 0, null))
end,
case
when EVENT.BASECURRENCYID = @ORGANIZATIONCURRENCYID
then null
else
dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(EVENT.BASECURRENCYID, @ORGANIZATIONCURRENCYID, @DATEPURCHASED, 0, null)
end,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
dbo.REGISTRANT
inner join @REGISTRATIONTOADDTABLE SELECTEDREGISTRATION on
SELECTEDREGISTRATION.EVENTID = REGISTRANT.EVENTID
inner join dbo.EVENT on SELECTEDREGISTRATION.EVENTID = EVENT.ID
where
@CONSTITUENTID = REGISTRANT.CONSTITUENTID
and SELECTEDREGISTRATION.QUANTITY > 0
and SELECTEDREGISTRATION.REGISTRANTREGISTRATIONID not in
(select ID from dbo.REGISTRANTREGISTRATION)
and not exists (select 1 from @OBSOLETEREGISTRANTS where [ID] = REGISTRANT.ID);
update REGISTRANTREGISTRATIONMAP
set REGISTRANTREGISTRATIONID = SELECTEDREGISTRATION.REGISTRANTREGISTRATIONID
from @REGISTRANTREGISTRATIONMAP as [REGISTRANTREGISTRATIONMAP]
inner join @REGISTRANTEVENTMAPPINGTABLE MAPPING on
REGISTRANTREGISTRATIONMAP.ID = MAPPING.REGISTRANTREGISTRATIONMAPID
inner join @REGISTRANTMAPPINGTABLE REGISTRANTMAPPING on
MAPPING.MAPPINGID = REGISTRANTMAPPING.MAPPINGID
inner join @REGISTRATIONTOADDTABLE SELECTEDREGISTRATION on
REGISTRANTMAPPING.REGISTRATIONSCOLLECTIONID = SELECTEDREGISTRATION.REGISTRATIONSCOLLECTIONID and
MAPPING.EVENTID = SELECTEDREGISTRATION.EVENTID
where
SELECTEDREGISTRATION.NEWREGISTRANTREGISTRATION = 1
and SELECTEDREGISTRATION.QUANTITY > 0;
update dbo.REGISTRANTREGISTRATIONMAP
set
REGISTRANTREGISTRATIONMAP.[REGISTRANTID] = [RRM].[REGISTRANTID],
REGISTRANTREGISTRATIONMAP.[REGISTRANTREGISTRATIONID] = [RRM].[REGISTRANTREGISTRATIONID],
REGISTRANTREGISTRATIONMAP.CHANGEDBYID = @CHANGEAGENTID,
REGISTRANTREGISTRATIONMAP.DATECHANGED = @CURRENTDATE
from dbo.REGISTRANTREGISTRATIONMAP
inner join @REGISTRANTREGISTRATIONMAP [RRM] on
REGISTRANTREGISTRATIONMAP.[ID] = [RRM].[ID];
--3.19.12: 190892 Moving delete on registrant after single update on REGISTRANTREGISTRATIONMAP
delete from dbo.REGISTRANT
where
REGISTRANT.ID in (select ID from @OBSOLETEREGISTRANTS OBSOLETEREGISTRANTS);
/* reset CONTEXT_INFO to previous value */
if not @CONTEXTCACHE is null
set CONTEXT_INFO @CONTEXTCACHE;
--Add the registrant registration maps
update @REGISTRANTEVENTMAPPINGTABLE
set
REGISTRANTREGISTRATIONMAPID = newid()
where
REGISTRANTREGISTRATIONMAPID is null;
insert into dbo.REGISTRANTREGISTRATIONMAP
(
ID,
REGISTRANTREGISTRATIONID,
REGISTRANTID,
REGISTRANTPACKAGEID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
MAPPING.REGISTRANTREGISTRATIONMAPID,
SELECTEDREGISTRATION.REGISTRANTREGISTRATIONID,
coalesce(REGISTRANT.ID, UNKNOWNGUESTMAPPING.REGISTRANTID),
case
when REGISTRANTMAPPING.REGISTRANTPACKAGEID = '00000000-0000-0000-0000-000000000000'
then null
else
REGISTRANTMAPPING.REGISTRANTPACKAGEID
end,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
@REGISTRANTMAPPINGTABLE REGISTRANTMAPPING
inner join @REGISTRATIONTOADDTABLE SELECTEDREGISTRATION on
REGISTRANTMAPPING.REGISTRATIONSCOLLECTIONID = SELECTEDREGISTRATION.REGISTRATIONSCOLLECTIONID
inner join @REGISTRANTEVENTMAPPINGTABLE MAPPING on
REGISTRANTMAPPING.MAPPINGID = MAPPING.MAPPINGID
and SELECTEDREGISTRATION.EVENTID = MAPPING.EVENTID
left join dbo.REGISTRANT on
SELECTEDREGISTRATION.EVENTID = REGISTRANT.EVENTID
and REGISTRANTMAPPING.CONSTITUENTID = REGISTRANT.CONSTITUENTID
left join @REGISTRANTEVENTMAPPINGTABLE UNKNOWNGUESTMAPPING on
SELECTEDREGISTRATION.EVENTID = UNKNOWNGUESTMAPPING.EVENTID
and REGISTRANTMAPPING.MAPPINGID = UNKNOWNGUESTMAPPING.MAPPINGID
left join dbo.REGISTRANTREGISTRATIONMAP on MAPPING.REGISTRANTREGISTRATIONMAPID = REGISTRANTREGISTRATIONMAP.ID
where
REGISTRANTREGISTRATIONMAP.ID is null
and SELECTEDREGISTRATION.QUANTITY > 0
and REGISTRANTMAPPING.DONOTMAPTOUNKNOWNGUEST = 0
and
(
REGISTRANTMAPPING.CONSTITUENTID is null
or
MAPPING.CONSTITUENTID = REGISTRANTMAPPING.CONSTITUENTID
);
--Update existing preferences
update dbo.REGISTRANTPREFERENCE
set
REGISTRANTID = coalesce(REGISTRANT.ID, MAPPING.REGISTRANTID), --Get registrant ID from MAPPING for unknown guests
EVENTPREFERENCEID = SELECTEDPREFERENCE.EVENTPREFERENCEID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from
@REGISTRANTMAPPINGTABLE REGISTRANTMAPPING
cross apply
(
--Directly selecting from collection field rather than using FROMITEMLISTXML function
SELECT
T.c.value('(REGISTRANTPREFERENCEID)[1]', 'uniqueidentifier') AS 'REGISTRANTPREFERENCEID',
T.c.value('(EVENTPREFERENCEID)[1]', 'uniqueidentifier') AS 'EVENTPREFERENCEID'
FROM REGISTRANTMAPPING.PREFERENCES.nodes('/PREFERENCES/ITEM') T(c)
) SELECTEDPREFERENCE
inner join dbo.REGISTRANTPREFERENCE on SELECTEDPREFERENCE.REGISTRANTPREFERENCEID = REGISTRANTPREFERENCE.ID
inner join dbo.EVENTPREFERENCE on SELECTEDPREFERENCE.EVENTPREFERENCEID = EVENTPREFERENCE.ID
inner join dbo.EVENTPREFERENCEGROUP on EVENTPREFERENCE.EVENTPREFERENCEGROUPID = EVENTPREFERENCEGROUP.ID
left join dbo.REGISTRANT on
EVENTPREFERENCEGROUP.EVENTID = REGISTRANT.EVENTID
and REGISTRANTMAPPING.CONSTITUENTID = REGISTRANT.CONSTITUENTID
left join @REGISTRANTEVENTMAPPINGTABLE MAPPING on
EVENTPREFERENCEGROUP.EVENTID = MAPPING.EVENTID
and REGISTRANTMAPPING.MAPPINGID = MAPPING.MAPPINGID
where
REGISTRANTPREFERENCE.REGISTRANTID <> coalesce(REGISTRANT.ID, MAPPING.REGISTRANTID)
or
REGISTRANTPREFERENCE.EVENTPREFERENCEID <> SELECTEDPREFERENCE.EVENTPREFERENCEID;
--Delete cleared preferences
/* cache current context information */
set @CONTEXTCACHE = CONTEXT_INFO();
/* set CONTEXT_INFO to @CHANGEAGENTID */
set CONTEXT_INFO @CHANGEAGENTID;
delete from dbo.REGISTRANTPREFERENCE
from
@REGISTRANTMAPPINGTABLE REGISTRANTMAPPING
cross apply
(
--Directly selecting from collection field rather than using FROMITEMLISTXML function
SELECT
T.c.value('(REGISTRANTPREFERENCEID)[1]', 'uniqueidentifier') AS 'REGISTRANTPREFERENCEID',
T.c.value('(EVENTPREFERENCEID)[1]', 'uniqueidentifier') AS 'EVENTPREFERENCEID'
FROM REGISTRANTMAPPING.PREFERENCES.nodes('/PREFERENCES/ITEM') T(c)
) SELECTEDPREFERENCE
inner join dbo.REGISTRANTPREFERENCE on SELECTEDPREFERENCE.REGISTRANTPREFERENCEID = REGISTRANTPREFERENCE.ID
where
SELECTEDPREFERENCE.EVENTPREFERENCEID = '00000000-0000-0000-0000-000000000000'
or SELECTEDPREFERENCE.EVENTPREFERENCEID is null;
/* reset CONTEXT_INFO to previous value */
if not @CONTEXTCACHE is null
set CONTEXT_INFO @CONTEXTCACHE;
--Add new preferences
declare @REGISTRANTPREFERENCETOADD table
(
ID uniqueidentifier default newid(),
REGISTRANTID uniqueidentifier,
EVENTPREFERENCEID uniqueidentifier,
REGISTRANTREGISTRATIONMAPID uniqueidentifier
);
insert into @REGISTRANTPREFERENCETOADD
(
REGISTRANTID,
EVENTPREFERENCEID,
REGISTRANTREGISTRATIONMAPID
)
select
coalesce(REGISTRANT.ID, MAPPING.REGISTRANTID), --Get registrant ID from MAPPING for unknown guests
SELECTEDPREFERENCE.EVENTPREFERENCEID,
MAPPING.REGISTRANTREGISTRATIONMAPID
from
@REGISTRANTMAPPINGTABLE REGISTRANTMAPPING
cross apply
(
--Directly selecting from collection field rather than using FROMITEMLISTXML function
SELECT
T.c.value('(REGISTRANTPREFERENCEID)[1]', 'uniqueidentifier') AS 'REGISTRANTPREFERENCEID',
T.c.value('(EVENTPREFERENCEID)[1]', 'uniqueidentifier') AS 'EVENTPREFERENCEID'
FROM REGISTRANTMAPPING.PREFERENCES.nodes('/PREFERENCES/ITEM') T(c)
) SELECTEDPREFERENCE
inner join dbo.EVENTPREFERENCE on SELECTEDPREFERENCE.EVENTPREFERENCEID = EVENTPREFERENCE.ID
inner join dbo.EVENTPREFERENCEGROUP on EVENTPREFERENCE.EVENTPREFERENCEGROUPID = EVENTPREFERENCEGROUP.ID
left join dbo.REGISTRANT on
EVENTPREFERENCEGROUP.EVENTID = REGISTRANT.EVENTID
and REGISTRANTMAPPING.CONSTITUENTID = REGISTRANT.CONSTITUENTID
left join @REGISTRANTEVENTMAPPINGTABLE MAPPING on
EVENTPREFERENCEGROUP.EVENTID = MAPPING.EVENTID
and REGISTRANTMAPPING.MAPPINGID = MAPPING.MAPPINGID
left join dbo.REGISTRANTPREFERENCE on SELECTEDPREFERENCE.REGISTRANTPREFERENCEID = REGISTRANTPREFERENCE.ID
where
REGISTRANTPREFERENCE.ID is null;
insert into dbo.REGISTRANTPREFERENCE
(
ID,
REGISTRANTID,
EVENTPREFERENCEID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
[PREFERENCETOADD].ID,
[PREFERENCETOADD].REGISTRANTID,
[PREFERENCETOADD].EVENTPREFERENCEID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
@REGISTRANTPREFERENCETOADD [PREFERENCETOADD];
insert into dbo.REGISTRANTPREFERENCEMAP
(
ID,
REGISTRANTREGISTRATIONMAPID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
[PREFERENCETOADD].ID,
[PREFERENCETOADD].REGISTRANTREGISTRATIONMAPID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
@REGISTRANTPREFERENCETOADD [PREFERENCETOADD]
left join dbo.REGISTRANTREGISTRATIONMAP on [PREFERENCETOADD].REGISTRANTREGISTRATIONMAPID = REGISTRANTREGISTRATIONMAP.ID
where
REGISTRANTREGISTRATIONMAP.ID is not null;
--Team fundraising
--Deletes are only performed if the registrant record is deleted earlier in the procedure.
--Team membership can be added (possibly with a new captain record).
--Team fundraiser goals are updated as well.
--Add a team fundraiser record if there is a new team and no existing team fundraiser record
insert into dbo.TEAMFUNDRAISER
(
APPEALID,
CONSTITUENTID,
GOAL,
ORGANIZATIONGOAL,
ORGANIZATIONEXCHANGERATEID,
BASECURRENCYID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select distinct
EVENT.APPEALID,
REGISTRANTMAPPING.CONSTITUENTID,
max(CONSTITUENTGOAL.TEAMFUNDRAISERGOAL),
dbo.UFN_CURRENCY_CONVERTTOORGANIZATIONCURRENCY(max(CONSTITUENTGOAL.TEAMFUNDRAISERGOAL), dbo.UFN_CURRENCYEXCHANGERATE_GETLATESTORGANIZATIONRATE(APPEAL.BASECURRENCYID, @ORGANIZATIONCURRENCYID, getdate(), null, null), null),
dbo.UFN_CURRENCYEXCHANGERATE_GETLATESTORGANIZATIONRATE(APPEAL.BASECURRENCYID, @ORGANIZATIONCURRENCYID, getdate(), null, null),
APPEAL.BASECURRENCYID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
@REGISTRANTMAPPINGTABLE REGISTRANTMAPPING
outer apply
(
--Directly selecting from collection field rather than using FROMITEMLISTXML function
SELECT
T.c.value('(../../EVENTID)[1]', 'uniqueidentifier') AS 'EVENTID'
FROM REGISTRANTMAPPING.TEAMFUNDRAISING.nodes('/TEAMFUNDRAISING/ITEM/TEAMFUNDRAISINGTEAMS/ITEM') T(c)
WHERE coalesce(T.c.value('(TEAMFUNDRAISINGTEAMMEMBERID)[1]', 'uniqueidentifier'), '00000000-0000-0000-0000-000000000000') = '00000000-0000-0000-0000-000000000000'
) NEWTEAM
left join @REGISTRANTMAPPINGTABLE CONSTITUENTREGISTRANTMAPPING on REGISTRANTMAPPING.CONSTITUENTID = CONSTITUENTREGISTRANTMAPPING.CONSTITUENTID
outer apply
(
--Directly selecting from collection field rather than using FROMITEMLISTXML function
SELECT
T.c.value('(TEAMFUNDRAISERGOAL)[1]', 'money') AS 'TEAMFUNDRAISERGOAL'
FROM CONSTITUENTREGISTRANTMAPPING.TEAMFUNDRAISING.nodes('/TEAMFUNDRAISING/ITEM') T(c)
WHERE T.c.value('(EVENTID)[1]', 'uniqueidentifier') = NEWTEAM.EVENTID
) CONSTITUENTGOAL --Pull goal from all registrant mappings for this constituent to avoid an extra update
left join dbo.EVENT on [NEWTEAM].[EVENTID] = EVENT.ID
left join dbo.APPEAL on APPEAL.ID = EVENT.APPEALID
left join dbo.TEAMFUNDRAISER on
REGISTRANTMAPPING.CONSTITUENTID = TEAMFUNDRAISER.CONSTITUENTID
and EVENT.APPEALID = TEAMFUNDRAISER.APPEALID
left join @REGISTRANTMAPPINGTABLE REGISTRANTMAPPINGGOAL on REGISTRANTMAPPING.CONSTITUENTID = REGISTRANTMAPPINGGOAL.CONSTITUENTID
where
EVENT.APPEALID is not null
and TEAMFUNDRAISER.ID is null
group by
REGISTRANTMAPPING.CONSTITUENTID,
EVENT.APPEALID,
APPEAL.BASECURRENCYID;
--Update any existing team fundraiser records with changed goals
update dbo.TEAMFUNDRAISER set
GOAL = [TEAMFUNDRAISINGGOALDISTINCT].TEAMFUNDRAISERGOAL,
ORGANIZATIONGOAL = dbo.UFN_CURRENCY_CONVERTTOORGANIZATIONCURRENCY([TEAMFUNDRAISINGGOALDISTINCT].TEAMFUNDRAISERGOAL, dbo.UFN_CURRENCYEXCHANGERATE_GETLATESTORGANIZATIONRATE(APPEAL.BASECURRENCYID, @ORGANIZATIONCURRENCYID, getdate(), null, null), null),
ORGANIZATIONEXCHANGERATEID = dbo.UFN_CURRENCYEXCHANGERATE_GETLATESTORGANIZATIONRATE(APPEAL.BASECURRENCYID, @ORGANIZATIONCURRENCYID, getdate(), null, null),
BASECURRENCYID = APPEAL.BASECURRENCYID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from
(
select distinct
REGISTRANTMAPPING.CONSTITUENTID,
EVENT.APPEALID,
max(TEAMFUNDRAISINGGOAL.TEAMFUNDRAISERGOAL) [TEAMFUNDRAISERGOAL]
from
@REGISTRANTMAPPINGTABLE REGISTRANTMAPPING
outer apply
(
--Directly selecting from collection field rather than using FROMITEMLISTXML function
SELECT
T.c.value('(EVENTID)[1]', 'uniqueidentifier') AS 'EVENTID',
T.c.value('(TEAMFUNDRAISERGOAL)[1]', 'money') AS 'TEAMFUNDRAISERGOAL'
FROM REGISTRANTMAPPING.TEAMFUNDRAISING.nodes('/TEAMFUNDRAISING/ITEM') T(c)
) TEAMFUNDRAISINGGOAL
left join dbo.EVENT on [TEAMFUNDRAISINGGOAL].[EVENTID] = EVENT.ID
where
[TEAMFUNDRAISINGGOAL].[TEAMFUNDRAISERGOAL] is not null
group by
REGISTRANTMAPPING.CONSTITUENTID,
EVENT.APPEALID
) [TEAMFUNDRAISINGGOALDISTINCT]
inner join dbo.TEAMFUNDRAISER on
[TEAMFUNDRAISINGGOALDISTINCT].[CONSTITUENTID] = TEAMFUNDRAISER.CONSTITUENTID
and [TEAMFUNDRAISINGGOALDISTINCT].[APPEALID] = TEAMFUNDRAISER.APPEALID
left join dbo.APPEAL on APPEAL.ID = TEAMFUNDRAISER.APPEALID
where
[TEAMFUNDRAISINGGOALDISTINCT].TEAMFUNDRAISERGOAL is not null
and TEAMFUNDRAISER.GOAL <> [TEAMFUNDRAISINGGOALDISTINCT].TEAMFUNDRAISERGOAL;
--Add a team member record for new teams
insert into dbo.TEAMFUNDRAISINGTEAMMEMBER
(
TEAMFUNDRAISERID,
TEAMFUNDRAISINGTEAMID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select distinct
TEAMFUNDRAISER.ID,
case
when NEWTEAM.TEAMFUNDRAISINGTEAMID = '00000000-0000-0000-0000-000000000000' then null
else NEWTEAM.TEAMFUNDRAISINGTEAMID
end,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
@REGISTRANTMAPPINGTABLE REGISTRANTMAPPING
cross apply
(
--Directly selecting from collection field rather than using FROMITEMLISTXML function
SELECT
T.c.value('(../../EVENTID)[1]', 'uniqueidentifier') AS 'EVENTID',
T.c.value('(TEAMFUNDRAISINGTEAMID)[1]', 'uniqueidentifier') AS 'TEAMFUNDRAISINGTEAMID'
FROM REGISTRANTMAPPING.TEAMFUNDRAISING.nodes('/TEAMFUNDRAISING/ITEM/TEAMFUNDRAISINGTEAMS/ITEM') T(c)
WHERE coalesce(T.c.value('(TEAMFUNDRAISINGTEAMMEMBERID)[1]', 'uniqueidentifier'), '00000000-0000-0000-0000-000000000000') = '00000000-0000-0000-0000-000000000000'
) NEWTEAM
left join dbo.EVENT on [NEWTEAM].[EVENTID] = EVENT.ID
left join dbo.TEAMFUNDRAISER on
REGISTRANTMAPPING.CONSTITUENTID = TEAMFUNDRAISER.CONSTITUENTID
and EVENT.APPEALID = TEAMFUNDRAISER.APPEALID
left join dbo.TEAMFUNDRAISINGTEAMMEMBER on
TEAMFUNDRAISER.ID = TEAMFUNDRAISINGTEAMMEMBER.TEAMFUNDRAISERID
and
(
NEWTEAM.TEAMFUNDRAISINGTEAMID = TEAMFUNDRAISINGTEAMMEMBER.TEAMFUNDRAISINGTEAMID
or
(
TEAMFUNDRAISINGTEAMMEMBER.TEAMFUNDRAISINGTEAMID is null
and
coalesce(NEWTEAM.TEAMFUNDRAISINGTEAMID, '00000000-0000-0000-0000-000000000000') = '00000000-0000-0000-0000-000000000000'
)
)
where
TEAMFUNDRAISINGTEAMMEMBER.ID is null;
--Add a team captain record for new teams where the is captain flag is true and the
--constituent is not already a captain on the team
insert into dbo.TEAMFUNDRAISINGTEAMCAPTAIN
(
TEAMFUNDRAISINGTEAMID,
CONSTITUENTID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select distinct
NEWTEAM.TEAMFUNDRAISINGTEAMID,
REGISTRANTMAPPING.CONSTITUENTID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
@REGISTRANTMAPPINGTABLE REGISTRANTMAPPING
cross apply
(
--Directly selecting from collection field rather than using FROMITEMLISTXML function
SELECT
T.c.value('(TEAMFUNDRAISINGTEAMID)[1]', 'uniqueidentifier') AS 'TEAMFUNDRAISINGTEAMID'
FROM REGISTRANTMAPPING.TEAMFUNDRAISING.nodes('/TEAMFUNDRAISING/ITEM/TEAMFUNDRAISINGTEAMS/ITEM') T(c)
WHERE
coalesce(T.c.value('(TEAMFUNDRAISINGTEAMMEMBERID)[1]', 'uniqueidentifier'), '00000000-0000-0000-0000-000000000000') = '00000000-0000-0000-0000-000000000000'
and coalesce(T.c.value('(TEAMFUNDRAISINGTEAMID)[1]', 'uniqueidentifier'), '00000000-0000-0000-0000-000000000000') <> '00000000-0000-0000-0000-000000000000'
and T.c.value('(ISTEAMCAPTAIN)[1]', 'bit') = 1
) NEWTEAM
left join dbo.TEAMFUNDRAISINGTEAMCAPTAIN on
REGISTRANTMAPPING.CONSTITUENTID = TEAMFUNDRAISINGTEAMCAPTAIN.CONSTITUENTID
and NEWTEAM.TEAMFUNDRAISINGTEAMID = TEAMFUNDRAISINGTEAMCAPTAIN.TEAMFUNDRAISINGTEAMID
where
TEAMFUNDRAISINGTEAMCAPTAIN.ID is null;
--TommyVe 2010-10-25 Bug 123733 Now we add benefits for both edit and add.
--Add the default benefits for the added registrations, give benefits to
--the registrant/guests when they are mapped and when the registration count is 1.
insert into dbo.REGISTRANTBENEFIT
(
REGISTRANTID,
BENEFITID,
UNITVALUE,
QUANTITY,
TOTALVALUE,
DETAILS,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED,
BASECURRENCYID,
BASEEXCHANGERATEID,
ORGANIZATIONEXCHANGERATEID,
EVENTBASECURRENCYTOTALVALUE,
ORGANIZATIONTOTALVALUE
)
select
coalesce(REGISTRANTEVENTMAPPING.REGISTRANTID, REGISTRANT.ID),
EVENTPRICEBENEFIT.BENEFITID,
EVENTPRICEBENEFIT.UNITVALUE,
EVENTPRICEBENEFIT.QUANTITY,
(EVENTPRICEBENEFIT.UNITVALUE * EVENTPRICEBENEFIT.QUANTITY),
EVENTPRICEBENEFIT.DETAILS,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE,
EVENTPRICEBENEFIT.BASECURRENCYID,
case when EVENTPRICEBENEFIT.BASECURRENCYID = EVENT.BASECURRENCYID
then null
else dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(EVENTPRICEBENEFIT.BASECURRENCYID, EVENT.BASECURRENCYID, @DATEPURCHASED, 1, null)
end,
case when EVENTPRICEBENEFIT.BASECURRENCYID = @ORGANIZATIONCURRENCYID
then null
else dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(EVENTPRICEBENEFIT.BASECURRENCYID, @ORGANIZATIONCURRENCYID, @DATEPURCHASED, 0, null)
end,
case when EVENTPRICEBENEFIT.BASECURRENCYID = EVENT.BASECURRENCYID
then EVENTPRICEBENEFIT.TOTALVALUE
else dbo.UFN_CURRENCY_CONVERT(EVENTPRICEBENEFIT.TOTALVALUE, dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(EVENTPRICEBENEFIT.BASECURRENCYID, EVENT.BASECURRENCYID, @DATEPURCHASED, 1, null))
end,
case when EVENTPRICEBENEFIT.BASECURRENCYID = @ORGANIZATIONCURRENCYID
then EVENTPRICEBENEFIT.TOTALVALUE
else dbo.UFN_CURRENCY_CONVERT(EVENTPRICEBENEFIT.TOTALVALUE, dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(EVENTPRICEBENEFIT.BASECURRENCYID, @ORGANIZATIONCURRENCYID, @DATEPURCHASED, 0, null))
end
from
@REGISTRANTEVENTMAPPINGTABLE REGISTRANTEVENTMAPPING
inner join dbo.EVENTPRICEBENEFIT on REGISTRANTEVENTMAPPING.EVENTPRICEID = EVENTPRICEBENEFIT.EVENTPRICEID
left join dbo.EVENTPRICE on REGISTRANTEVENTMAPPING.EVENTPRICEID = EVENTPRICE.ID
left join dbo.EVENT on EVENTPRICE.EVENTID = EVENT.ID
left join dbo.EVENTREGISTRATIONTYPE on EVENTPRICE.EVENTREGISTRATIONTYPEID = EVENTREGISTRATIONTYPE.ID
left join dbo.REGISTRANT on
REGISTRANTEVENTMAPPING.EVENTID = REGISTRANT.EVENTID
and REGISTRANTEVENTMAPPING.CONSTITUENTID = REGISTRANT.CONSTITUENTID
where
EVENTPRICE.REGISTRATIONCOUNT = 1
and REGISTRANTEVENTMAPPING.NEWREGISTRANTREGISTRATIONMAP = 1;
--Benefits from registration options with high registration counts always
--go to the registrant so that they don't have to be divided among guests
insert into dbo.REGISTRANTBENEFIT
(
REGISTRANTID,
BENEFITID,
UNITVALUE,
QUANTITY,
TOTALVALUE,
DETAILS,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED,
BASECURRENCYID,
BASEEXCHANGERATEID,
ORGANIZATIONEXCHANGERATEID,
EVENTBASECURRENCYTOTALVALUE,
ORGANIZATIONTOTALVALUE
)
select
REGISTRANT.ID,
EVENTPRICEBENEFIT.BENEFITID,
EVENTPRICEBENEFIT.UNITVALUE,
(SELECTEDREGISTRATION.QUANTITY * EVENTPRICEBENEFIT.QUANTITY),
((EVENTPRICEBENEFIT.UNITVALUE) * (SELECTEDREGISTRATION.QUANTITY * EVENTPRICEBENEFIT.QUANTITY)),
EVENTPRICEBENEFIT.DETAILS,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE,
EVENTPRICEBENEFIT.BASECURRENCYID,
case when EVENTPRICEBENEFIT.BASECURRENCYID = EVENT.BASECURRENCYID
then null
else dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(EVENTPRICEBENEFIT.BASECURRENCYID, EVENT.BASECURRENCYID, @DATEPURCHASED, 1, null)
end,
case when EVENTPRICEBENEFIT.BASECURRENCYID = @ORGANIZATIONCURRENCYID
then null
else dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(EVENTPRICEBENEFIT.BASECURRENCYID, @ORGANIZATIONCURRENCYID, @DATEPURCHASED, 0, null)
end,
case when EVENTPRICEBENEFIT.BASECURRENCYID = EVENT.BASECURRENCYID
then ((SELECTEDREGISTRATION.QUANTITY * EVENTPRICEBENEFIT.UNITVALUE) * (SELECTEDREGISTRATION.QUANTITY * EVENTPRICEBENEFIT.QUANTITY))
else dbo.UFN_CURRENCY_CONVERT(((SELECTEDREGISTRATION.QUANTITY * EVENTPRICEBENEFIT.UNITVALUE) * (SELECTEDREGISTRATION.QUANTITY * EVENTPRICEBENEFIT.QUANTITY)), dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(EVENTPRICEBENEFIT.BASECURRENCYID, EVENT.BASECURRENCYID, @DATEPURCHASED, 1, null))
end,
case when EVENTPRICEBENEFIT.BASECURRENCYID = @ORGANIZATIONCURRENCYID
then ((SELECTEDREGISTRATION.QUANTITY * EVENTPRICEBENEFIT.UNITVALUE) * (SELECTEDREGISTRATION.QUANTITY * EVENTPRICEBENEFIT.QUANTITY))
else dbo.UFN_CURRENCY_CONVERT(((SELECTEDREGISTRATION.QUANTITY * EVENTPRICEBENEFIT.UNITVALUE) * (SELECTEDREGISTRATION.QUANTITY * EVENTPRICEBENEFIT.QUANTITY)), dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(EVENTPRICEBENEFIT.BASECURRENCYID, @ORGANIZATIONCURRENCYID, @DATEPURCHASED, 0, null))
end
from
@REGISTRATIONTOADDTABLE SELECTEDREGISTRATION
inner join dbo.EVENTPRICEBENEFIT on SELECTEDREGISTRATION.EVENTPRICEID = EVENTPRICEBENEFIT.EVENTPRICEID
left join dbo.EVENTPRICE on SELECTEDREGISTRATION.EVENTPRICEID = EVENTPRICE.ID
left join dbo.EVENT on EVENTPRICE.EVENTID = EVENT.ID
left join dbo.EVENTREGISTRATIONTYPE on EVENTPRICE.EVENTREGISTRATIONTYPEID = EVENTREGISTRATIONTYPE.ID
left join dbo.REGISTRANT on
SELECTEDREGISTRATION.EVENTID = REGISTRANT.EVENTID
and @CONSTITUENTID = REGISTRANT.CONSTITUENTID
where
EVENTPRICE.REGISTRATIONCOUNT > 1
and SELECTEDREGISTRATION.QUANTITY > 0
and SELECTEDREGISTRATION.NEWREGISTRANTREGISTRATION = 1;
--Delete cleared preferences
/* cache current context information */
set @CONTEXTCACHE = CONTEXT_INFO();
/* set CONTEXT_INFO to @CHANGEAGENTID */
set CONTEXT_INFO @CHANGEAGENTID;
--Deleting duplicate records in REGISTRANTBENEFIT TABLE, BUG 689729
with ROWSTODELETE
as
(
select RowNum = Dense_Rank() over(partition by REGISTRANTID order by REGISTRANTBENEFIT.DATEADDED desc),
REGISTRANTBENEFIT.ID
from dbo.REGISTRANTBENEFIT
inner join dbo.REGISTRANT Registrant
on REGISTRANTID = Registrant.ID
where Registrant.EVENTID = @EVENTID
)
delete from dbo.REGISTRANTBENEFIT
where REGISTRANTBENEFIT.ID in (select ID from ROWSTODELETE where ROWSTODELETE.RowNum > 1);
/* reset CONTEXT_INFO to previous value */
if not @CONTEXTCACHE is null
set CONTEXT_INFO @CONTEXTCACHE;
--insert default designation for main registrants here using newregistrants table
if @REGISTRANTEXISTSWITHID = 0
begin
--default designation on main registrant
insert into dbo.REGISTRANTDESIGNATION(REGISTRANTID,DESIGNATIONID, AMOUNT, ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
select REGISTRANT.ID,
DESIGNATIONID,
coalesce((
select sum(REGISTRANTREGISTRATION.RECEIPTAMOUNT)
from dbo.REGISTRANTREGISTRATION
where REGISTRANTREGISTRATION.REGISTRANTID = REGISTRANT.ID),0),
@CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE
from dbo.REGISTRANT
inner join dbo.EVENT on REGISTRANT.EVENTID = EVENT.ID
inner join dbo.EVENTDESIGNATION on EVENTDESIGNATION.EVENTID = EVENT.ID and EVENTDESIGNATION.[DEFAULT] = 1
where REGISTRANT.ID = @ID and EVENT.DESIGNATIONSONFEES = 1
--default designation on main registrant for other events
insert into dbo.REGISTRANTDESIGNATION(REGISTRANTID,DESIGNATIONID, AMOUNT, ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
select CNR.REGISTRANTID,
DESIGNATIONID,
coalesce((
select sum(REGISTRANTREGISTRATION.RECEIPTAMOUNT)
from dbo.REGISTRANTREGISTRATION
where REGISTRANTREGISTRATION.REGISTRANTID = CNR.REGISTRANTID),0),
@CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE
from @COPYOFNEWREGISTRANTS [CNR]
inner join dbo.EVENT on CNR.EVENTID = EVENT.ID
inner join dbo.EVENTDESIGNATION on EVENTDESIGNATION.EVENTID = EVENT.ID and EVENTDESIGNATION.[DEFAULT] = 1
where EVENT.DESIGNATIONSONFEES = 1
end
if @REGISTRANTEXISTSWITHID = 1
begin
declare @DESIGNATIONSPLITS xml;
set @DESIGNATIONSPLITS = (select ID,
AMOUNT
from dbo.REGISTRANTDESIGNATION
where REGISTRANTDESIGNATION.REGISTRANTID = @ID
for xml raw('ITEM'),type,elements,root('AMOUNTSTOPRORATE'),BINARY BASE64 );
declare @OLDDESIGNATIONBALANCE money = (select sum(coalesce(REGISTRANTDESIGNATION.AMOUNT,0))
from dbo.REGISTRANTDESIGNATION
where REGISTRANTDESIGNATION.REGISTRANTID = @ID);
declare @NEWRECEIPTAMOUNT money = (select sum(coalesce(REGISTRANTREGISTRATION.RECEIPTAMOUNT,0))
from dbo.REGISTRANTREGISTRATION
where REGISTRANTREGISTRATION.REGISTRANTID = @ID);
declare @REGISTRANTEVENTBASECURRENCYID uniqueidentifier = (select EVENT.BASECURRENCYID
from dbo.REGISTRANT
inner join dbo.EVENT on EVENT.ID = REGISTRANT.EVENTID
where REGISTRANT.ID = @ID);
update dbo.REGISTRANTDESIGNATION
set
REGISTRANTDESIGNATION.AMOUNT = PRORATEDAMOUNT.AMOUNT,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from dbo.REGISTRANTDESIGNATION
inner join dbo.UFN_SPLITS_PRORATEAMOUNTS(@OLDDESIGNATIONBALANCE ,@NEWRECEIPTAMOUNT,(select DECIMALDIGITS from dbo.UFN_CURRENCY_GETPROPERTIES(@REGISTRANTEVENTBASECURRENCYID)),@DESIGNATIONSPLITS) PRORATEDAMOUNT
on PRORATEDAMOUNT.ID = REGISTRANTDESIGNATION.ID
where PRORATEDAMOUNT.ID = REGISTRANTDESIGNATION.ID
if exists (select 1 from @COPYOFNEWREGISTRANTS where REGISTRANTID is not null)
begin
update dbo.REGISTRANTDESIGNATION
set
AMOUNT = PRORATEDAMOUNTS.AMOUNT,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from @COPYOFNEWREGISTRANTS [CNR]
cross apply dbo.UFN_SPLITS_PRORATEAMOUNTS(
(select sum(coalesce(REGISTRANTDESIGNATION.AMOUNT,0))
from dbo.REGISTRANTDESIGNATION
where REGISTRANTDESIGNATION.REGISTRANTID = [CNR].REGISTRANTID),
(select sum(coalesce(REGISTRANTREGISTRATION.RECEIPTAMOUNT,0))
from dbo.REGISTRANTREGISTRATION
where REGISTRANTREGISTRATION.REGISTRANTID = [CNR].REGISTRANTID),
(select DECIMALDIGITS from dbo.UFN_CURRENCY_GETPROPERTIES((select EVENT.BASECURRENCYID from dbo.REGISTRANT inner join dbo.EVENT on EVENT.ID = REGISTRANT.EVENTID where REGISTRANT.ID = [CNR].REGISTRANTID))),
(select ID,
AMOUNT
from dbo.REGISTRANTDESIGNATION
where REGISTRANTDESIGNATION.REGISTRANTID = [CNR].REGISTRANTID
for xml raw('ITEM'),type,elements,root('AMOUNTSTOPRORATE'),BINARY BASE64 )
) PRORATEDAMOUNTS
where REGISTRANTDESIGNATION.ID = PRORATEDAMOUNTS.ID
end
end
--Multiple registrant rows can be created so always select and return the registrant ID for the current event
--If registrant did not register for event return the registrant ID for a related event.
--On an add form, the ID may be passed in, make sure a record exists.
if not exists
(
select
REGISTRANT.ID
from
dbo.REGISTRANT
where
REGISTRANT.ID = @ID
)
set @ID = null;
if @ID is null
select
@ID = ID
from
dbo.REGISTRANT
where
CONSTITUENTID = @CONSTITUENTID and
EVENTID = @EVENTID;
if @ID is null
select top(1)
@ID = REGISTRANT.ID
from
dbo.REGISTRANT
inner join dbo.UFN_EVENT_GETRELATEDEVENTS(@EVENTID) EVENTS on REGISTRANT.EVENTID = EVENTS.ID
inner join dbo.EVENT on EVENT.ID = EVENTS.ID
where
REGISTRANT.CONSTITUENTID = @CONSTITUENTID and
dbo.UFN_EVENT_USERHASSITEACCESS(@CURRENTAPPUSERID, EVENT.ID) = 1
order by
EVENT.NAME;
if @ID is null
select top(1)
@ID = REGISTRANT.ID
from
dbo.REGISTRANT
inner join @REGISTRANTMAPPINGTABLE RMT on REGISTRANT.EVENTID = RMT.EVENTID
inner join dbo.EVENT on RMT.EVENTID = EVENT.ID
where
REGISTRANT.CONSTITUENTID = @CONSTITUENTID
order by
EVENT.NAME;
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;