USP_REVENUEBATCH_REGISTRANT_UNIFIEDUPDATE
Saves updates to a revenue batch 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 | |
| @BATCHID | uniqueidentifier | IN | |
| @ISWALKIN | bit | IN |
Definition
Copy
CREATE procedure dbo.USP_REVENUEBATCH_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,
@BATCHID uniqueidentifier,
@ISWALKIN bit=0
)
with execute as caller
as
set nocount on;
--TommyVe 9/2/2009 We don't need to check constituent security when adding data
-- to revenue batch tables. Search list record security will generally prevent
-- access to restricted records. When committing the batch, record security will
-- be checked by dbo.USP_REGISTRANT_UNIFIEDUPDATE. I consulted with BryanZe before
-- making this change, if we wanted to check record security in this procedure
-- we would probably need to extend platform support for record security from
-- the context of batch.
--declare @ISADMIN bit;
--set @ISADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);
--
--if @ISADMIN = 0
-- if dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_FORCONSTIT(@CURRENTAPPUSERID, @SECURITYCONTEXTFORMINSTANCEID, @CONSTITUENTID) <> 1
-- begin
-- raiserror ('ERR_CONSTITUENT_RECORDSECURITY_PERMISSION_DENIED',13,1);
-- return 1;
-- end
if @ID is null
set @ID = newid();
declare @REGISTRANTEXISTSWITHID bit;
if exists(select BATCHREVENUEREGISTRANT.ID from dbo.BATCHREVENUEREGISTRANT where BATCHREVENUEREGISTRANT.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
--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
);
insert into @REGISTRATIONTOADDTABLE
(
REGISTRATIONSCOLLECTIONID,
EVENTID,
EVENTPRICEID,
AMOUNT,
RECEIPTAMOUNT,
QUANTITY,
DATEPURCHASED,
REGISTRATIONPACKAGEID,
REGISTRANTREGISTRATIONID
)
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
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
)
select
SELECTEDREGISTRATION.SINGLEEVENTREGISTRATIONID,
EVENTPRICE.EVENTID,
SELECTEDREGISTRATION.EVENTPRICEID,
SELECTEDREGISTRATION.AMOUNT,
SELECTEDREGISTRATION.RECEIPTAMOUNT,
SELECTEDREGISTRATION.QUANTITY,
@DATEPURCHASED,
null,
SELECTEDREGISTRATION.SINGLEEVENTREGISTRANTREGISTRATIONID
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
);
insert into @REGISTRANTMAPPINGTABLE
(
REGISTRANTPACKAGEID,
REGISTRATIONPACKAGEID,
EVENTID,
EVENTPRICEID,
REGISTRATIONSCOLLECTIONID,
REGISTRANTREGISTRATIONMAPS,
REGISTRANTWAIVEBENEFITS,
CONSTITUENTID,
TEAMFUNDRAISING,
PREFERENCES,
DONOTMAPTOUNKNOWNGUEST
)
--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
FROM @REGISTRANTMAPPINGS.nodes('/REGISTRANTMAPPINGS/ITEM') T(c);
--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,
WAIVEBENEFITS bit,
REGISTRANTIDISNEWID bit,
OLDREGISTRANTID uniqueidentifier,
DONOTMAPTOUNKNOWNGUEST bit
);
insert into @REGISTRANTEVENTMAPPINGTABLE
(
REGISTRANTID,
MAPPINGID,
EVENTID,
EVENTPRICEID,
CONSTITUENTID,
REGISTRANTREGISTRATIONMAPID,
WAIVEBENEFITS,
DONOTMAPTOUNKNOWNGUEST
)
select
REGISTRANTREGISTRATIONMAPITEM.REGISTRANTID,
REGISTRANTMAPPING.MAPPINGID,
coalesce(EVENTPRICE.EVENTID, REGISTRANTMAPPING.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,
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
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.BATCHREVENUEREGISTRANT [SOURCEREGISTRANT] on MAPPING.REGISTRANTID = [SOURCEREGISTRANT].ID
where
MAPPING.REGISTRANTID is not null
and MAPPING.CONSTITUENTID is null
and [SOURCEREGISTRANT].CONSTITUENTID 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 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.BATCHREVENUEREGISTRANT
(
[ID],
[EVENTID],
[CONSTITUENTID],
[ATTENDED],
[WILLNOTATTEND],
[GUESTOFREGISTRANTID],
[BENEFITSWAIVED],
[BATCHID],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED],
[ISWALKIN]
)
values
(
@ID,
@EVENTID,
@CONSTITUENTID,
0, --Not attended by default
0, --Will attend
null,
@WAIVEBENEFITS,
@BATCHID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE,
@ISWALKIN
);
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
insert into dbo.BATCHREVENUEREGISTRANT
(
[ID],
[EVENTID],
[CONSTITUENTID],
[ATTENDED],
[WILLNOTATTEND],
[GUESTOFREGISTRANTID],
[BENEFITSWAIVED],
[BATCHID],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED],
[ISWALKIN]
)
select distinct
@ID,
@EVENTID,
@CONSTITUENTID,
0, --Not attended
case
when exists
(
select
MAPPINGFORCONSTITUENT.CONSTITUENTID
from
@REGISTRANTEVENTMAPPINGTABLE MAPPINGFORCONSTITUENT
where
MAPPINGFORCONSTITUENT.EVENTID = MAPPING.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, --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,
@BATCHID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE,
@ISWALKIN
from
@REGISTRANTEVENTMAPPINGTABLE MAPPING
left join dbo.BATCHREVENUEREGISTRANT on
MAPPING.EVENTID = BATCHREVENUEREGISTRANT.EVENTID
and @CONSTITUENTID = BATCHREVENUEREGISTRANT.CONSTITUENTID
and @BATCHID = BATCHREVENUEREGISTRANT.BATCHID
where
BATCHREVENUEREGISTRANT.ID is null
and MAPPING.EVENTID = @EVENTID;
insert into dbo.BATCHREVENUEREGISTRANT
(
[EVENTID],
[CONSTITUENTID],
[ATTENDED],
[WILLNOTATTEND],
[GUESTOFREGISTRANTID],
[BENEFITSWAIVED],
[BATCHID],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED],
[ISWALKIN]
)
select distinct
MAPPING.EVENTID,
@CONSTITUENTID,
0, --Not attended
case
when exists
(
select
MAPPINGFORCONSTITUENT.CONSTITUENTID
from
@REGISTRANTEVENTMAPPINGTABLE MAPPINGFORCONSTITUENT
where
MAPPINGFORCONSTITUENT.EVENTID = MAPPING.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, --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,
@BATCHID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE,
@ISWALKIN
from
@REGISTRANTEVENTMAPPINGTABLE MAPPING
left join dbo.BATCHREVENUEREGISTRANT on
MAPPING.EVENTID = BATCHREVENUEREGISTRANT.EVENTID
and @CONSTITUENTID = BATCHREVENUEREGISTRANT.CONSTITUENTID
and @BATCHID = BATCHREVENUEREGISTRANT.BATCHID
where
BATCHREVENUEREGISTRANT.ID is null;
--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.
-- Record ISWALKIN value as well if it has changed.
update dbo.BATCHREVENUEREGISTRANT
set
WILLNOTATTEND = case when BATCHREVENUEREGISTRANT.WILLNOTATTEND = 0 then 0 else MAPPING.WILLNOTATTEND end,
BENEFITSWAIVED = MAPPING.WAIVEBENEFITS,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE,
ISWALKIN = @ISWALKIN
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.BATCHREVENUEREGISTRANT on
MAPPING.EVENTID = BATCHREVENUEREGISTRANT.EVENTID
and @CONSTITUENTID = BATCHREVENUEREGISTRANT.CONSTITUENTID
and @BATCHID = BATCHREVENUEREGISTRANT.BATCHID
where
(
BATCHREVENUEREGISTRANT.WILLNOTATTEND = 1
and MAPPING.WILLNOTATTEND = 0
)
or BATCHREVENUEREGISTRANT.BENEFITSWAIVED <> MAPPING.WAIVEBENEFITS
or BATCHREVENUEREGISTRANT.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 --TommyVe 9/2/2009 Not checking record security
-- when adding rows to the batch table, see the comment at the top of this USP.
--if @ISADMIN = 0
-- if exists
-- (
-- select
-- 1
-- from
-- @REGISTRANTEVENTMAPPINGTABLE MAPPING
-- inner join dbo.BATCHREVENUEREGISTRANT HOSTREGISTRANT on
-- MAPPING.EVENTID = HOSTREGISTRANT.EVENTID
-- and @CONSTITUENTID = HOSTREGISTRANT.CONSTITUENTID
-- and @BATCHID = HOSTREGISTRANT.BATCHID
-- left join dbo.BATCHREVENUEREGISTRANT 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
-- )
-- begin
-- raiserror ('ERR_GUESTCONSTITUENT_RECORDSECURITY_PERMISSION_DENIED',13,1);
-- return 1;
-- end
insert into dbo.BATCHREVENUEREGISTRANT
(
[EVENTID],
[CONSTITUENTID],
[ATTENDED],
[WILLNOTATTEND],
[GUESTOFREGISTRANTID],
[BENEFITSWAIVED],
[BATCHID],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED],
[ISWALKIN]
)
select distinct
MAPPING.EVENTID,
MAPPING.CONSTITUENTID,
0, --Not attended
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,
@BATCHID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE,
@ISWALKIN
from
@REGISTRANTEVENTMAPPINGTABLE MAPPING
inner join dbo.BATCHREVENUEREGISTRANT HOSTREGISTRANT on
MAPPING.EVENTID = HOSTREGISTRANT.EVENTID
and @CONSTITUENTID = HOSTREGISTRANT.CONSTITUENTID
and @BATCHID = HOSTREGISTRANT.BATCHID
left join dbo.BATCHREVENUEREGISTRANT 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;
--Add a registrant row for the new unknown guests mapped to events
insert into dbo.BATCHREVENUEREGISTRANT
(
[ID],
[EVENTID],
[CONSTITUENTID],
[ATTENDED],
[WILLNOTATTEND],
[GUESTOFREGISTRANTID],
[BENEFITSWAIVED],
[BATCHID],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED],
[ISWALKIN]
)
select
MAPPING.REGISTRANTID,
MAPPING.EVENTID,
null,
0, --Not attended
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,
@BATCHID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE,
@ISWALKIN
from
@REGISTRANTEVENTMAPPINGTABLE MAPPING
inner join dbo.BATCHREVENUEREGISTRANT HOSTREGISTRANT on
MAPPING.EVENTID = HOSTREGISTRANT.EVENTID
and @CONSTITUENTID = HOSTREGISTRANT.CONSTITUENTID
and @BATCHID = HOSTREGISTRANT.BATCHID
where
MAPPING.CONSTITUENTID is null
and MAPPING.REGISTRANTIDISNEWID = 1;
-- TODO: include event setting and event groups
--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 registra
update dbo.BATCHREVENUEREGISTRANTPREFERENCE
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.BATCHREVENUEREGISTRANT [TARGETREGISTRANT] on MAPPING.CONSTITUENTID = [TARGETREGISTRANT].CONSTITUENTID and MAPPING.EVENTID = [TARGETREGISTRANT].EVENTID and @BATCHID = [TARGETREGISTRANT].BATCHID
left join dbo.BATCHREVENUEREGISTRANT [SOURCEREGISTRANT] on coalesce(MAPPING.OLDREGISTRANTID, MAPPING.REGISTRANTID) = [SOURCEREGISTRANT].ID and @BATCHID = [SOURCEREGISTRANT].BATCHID
left join dbo.BATCHREVENUEREGISTRANTPREFERENCEMAP on [MAPPING].[REGISTRANTREGISTRATIONMAPID] = BATCHREVENUEREGISTRANTPREFERENCEMAP.REGISTRANTREGISTRATIONMAPID
left join dbo.BATCHREVENUEREGISTRANTPREFERENCE on BATCHREVENUEREGISTRANTPREFERENCEMAP.ID = BATCHREVENUEREGISTRANTPREFERENCE.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)
);
update dbo.BATCHREVENUEREGISTRANTREGISTRATIONMAP
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.BATCHREVENUEREGISTRANT [TARGETREGISTRANT] on MAPPING.CONSTITUENTID = [TARGETREGISTRANT].CONSTITUENTID and MAPPING.EVENTID = [TARGETREGISTRANT].EVENTID and @BATCHID = [TARGETREGISTRANT].BATCHID
left join dbo.BATCHREVENUEREGISTRANT [SOURCEREGISTRANT] on coalesce(MAPPING.OLDREGISTRANTID, MAPPING.REGISTRANTID) = [SOURCEREGISTRANT].ID and @BATCHID = [SOURCEREGISTRANT].BATCHID
left join dbo.BATCHREVENUEREGISTRANTREGISTRATIONMAP on [MAPPING].[REGISTRANTREGISTRATIONMAPID] = BATCHREVENUEREGISTRANTREGISTRATIONMAP.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.BATCHREVENUEREGISTRANTPREFERENCE
from
dbo.BATCHREVENUEREGISTRANTPREFERENCE
inner join dbo.BATCHREVENUEREGISTRANTPREFERENCEMAP on BATCHREVENUEREGISTRANTPREFERENCE.ID = BATCHREVENUEREGISTRANTPREFERENCEMAP.ID
inner join dbo.BATCHREVENUEREGISTRANTREGISTRATIONMAP on BATCHREVENUEREGISTRANTPREFERENCEMAP.REGISTRANTREGISTRATIONMAPID = BATCHREVENUEREGISTRANTREGISTRATIONMAP.ID
inner join @DELETEDREGISTRANTREGISTRATIONMAPS.nodes('/DELETEDREGISTRANTREGISTRATIONMAPS/ITEM') T(c) on
T.c.value('(REGISTRANTREGISTRATIONMAPID)[1]','uniqueidentifier') = BATCHREVENUEREGISTRANTREGISTRATIONMAP.ID;
--Remove deleted registrant mappings
delete from dbo.BATCHREVENUEREGISTRANTREGISTRATIONMAP
from
dbo.BATCHREVENUEREGISTRANTREGISTRATIONMAP
inner join @DELETEDREGISTRANTREGISTRATIONMAPS.nodes('/DELETEDREGISTRANTREGISTRATIONMAPS/ITEM') T(c) on
T.c.value('(REGISTRANTREGISTRATIONMAPID)[1]','uniqueidentifier') = BATCHREVENUEREGISTRANTREGISTRATIONMAP.ID;
--Remove registrant registrations where the quantity has been reduced to zero
delete from dbo.BATCHREVENUEREGISTRANTREGISTRATION
from
dbo.BATCHREVENUEREGISTRANTREGISTRATION
inner join @REGISTRATIONTOADDTABLE SELECTEDREGISTRATION on BATCHREVENUEREGISTRANTREGISTRATION.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
BATCHREVENUEREGISTRANT.ID
from
(
select
T.c.value('(REGISTRANTID)[1]','uniqueidentifier') [REGISTRANTID]
from
@DELETEDREGISTRANTREGISTRATIONMAPS.nodes('/DELETEDREGISTRANTREGISTRATIONMAPS/ITEM') T(c)
union all
select
BATCHREVENUEREGISTRANT.ID
from
@REGISTRANTEVENTMAPPINGTABLE MAPPING
inner join dbo.BATCHREVENUEREGISTRANT on coalesce(MAPPING.OLDREGISTRANTID, MAPPING.REGISTRANTID) = BATCHREVENUEREGISTRANT.ID
left join @REGISTRANTEVENTMAPPINGTABLE CURRENTLYMAPPEDREGISTRANTS on BATCHREVENUEREGISTRANT.EVENTID = CURRENTLYMAPPEDREGISTRANTS.EVENTID and BATCHREVENUEREGISTRANT.CONSTITUENTID = CURRENTLYMAPPEDREGISTRANTS.CONSTITUENTID
where
(
BATCHREVENUEREGISTRANT.CONSTITUENTID <> MAPPING.CONSTITUENTID
or
(BATCHREVENUEREGISTRANT.CONSTITUENTID is not null and MAPPING.CONSTITUENTID is null)
or
(BATCHREVENUEREGISTRANT.CONSTITUENTID is null and MAPPING.CONSTITUENTID is not null)
)
and
CURRENTLYMAPPEDREGISTRANTS.EVENTID is null
) [SELECTEDREGISTRANT]
inner join dbo.BATCHREVENUEREGISTRANT on [SELECTEDREGISTRANT].[REGISTRANTID] = BATCHREVENUEREGISTRANT.ID
left join dbo.BATCHREVENUEREGISTRANTREGISTRATIONMAP on BATCHREVENUEREGISTRANT.ID = BATCHREVENUEREGISTRANTREGISTRATIONMAP.REGISTRANTID
left join @REGISTRANTEVENTMAPPINGTABLE REGISTRANTEVENTMAPPING on BATCHREVENUEREGISTRANT.CONSTITUENTID = REGISTRANTEVENTMAPPING.CONSTITUENTID
left join @REGISTRANTMAPPINGTABLE REGISTRANTMAPPING on REGISTRANTEVENTMAPPING.MAPPINGID = REGISTRANTMAPPING.MAPPINGID
left join @REGISTRATIONTOADDTABLE SELECTEDREGISTRATION on
REGISTRANTMAPPING.REGISTRATIONSCOLLECTIONID = SELECTEDREGISTRATION.REGISTRATIONSCOLLECTIONID
and BATCHREVENUEREGISTRANT.EVENTID = SELECTEDREGISTRATION.EVENTID
where
BATCHREVENUEREGISTRANTREGISTRATIONMAP.ID is null
and
SELECTEDREGISTRATION.REGISTRATIONSCOLLECTIONID is null
and
(
BATCHREVENUEREGISTRANT.CONSTITUENTID is null
or
BATCHREVENUEREGISTRANT.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
BATCHREVENUEREGISTRANT.ID
from
dbo.EVENT
inner join dbo.BATCHREVENUEREGISTRANT on EVENT.ID = BATCHREVENUEREGISTRANT.EVENTID
left join dbo.BATCHREVENUEREGISTRANTREGISTRATIONMAP on BATCHREVENUEREGISTRANT.ID = BATCHREVENUEREGISTRANTREGISTRATIONMAP.REGISTRANTID
left join @REGISTRANTEVENTMAPPINGTABLE REGISTRANTEVENTMAPPING on BATCHREVENUEREGISTRANT.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.BATCHREVENUEREGISTRANT [GUEST]
left join @OBSOLETEREGISTRANTS OBSOLETEREGISTRANTS on [GUEST].ID = OBSOLETEREGISTRANTS.ID
where
OBSOLETEREGISTRANTS.ID is null
) [REMAININGGUEST] on BATCHREVENUEREGISTRANT.ID = [REMAININGGUEST].GUESTOFREGISTRANTID
where
(
(
EVENT.ID = @EVENTID
and
EVENT.MAINEVENTID is not null
)
or
EVENT.MAINEVENTID = @EVENTID
)
and
BATCHREVENUEREGISTRANT.CONSTITUENTID = @CONSTITUENTID
and
BATCHREVENUEREGISTRANT.BATCHID = @BATCHID
and
BATCHREVENUEREGISTRANTREGISTRATIONMAP.ID is null
and
SELECTEDREGISTRATION.REGISTRATIONSCOLLECTIONID is null
and
[REMAININGGUEST].[GUESTOFREGISTRANTID] is null
and
(@ID is null or BATCHREVENUEREGISTRANT.ID <> @ID);
-- TODO: include team fundraising
-- 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
-- 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
--where
-- REGISTRANT.ID in (select ID from @OBSOLETEREGISTRANTS OBSOLETEREGISTRANTS);
--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);
delete from dbo.BATCHREVENUEREGISTRANT
where
BATCHREVENUEREGISTRANT.ID in (select ID from @OBSOLETEREGISTRANTS OBSOLETEREGISTRANTS);
--Remove deleted packages
delete from dbo.BATCHREVENUEREGISTRANTPACKAGE
from
dbo.BATCHREVENUEREGISTRANTPACKAGE
inner join @DELETEDREGISTRANTREGISTRATIONMAPS.nodes('/DELETEDREGISTRANTREGISTRATIONMAPS/ITEM') T(c) on
T.c.value('(REGISTRANTPACKAGEID)[1]','uniqueidentifier') = BATCHREVENUEREGISTRANTPACKAGE.ID;
/* reset CONTEXT_INFO to previous value */
if not @contextCache is null
set CONTEXT_INFO @contextCache;
--Update existing registrants with the waive benefits flag
update dbo.BATCHREVENUEREGISTRANT
set
WILLNOTATTEND = 0,
BENEFITSWAIVED = coalesce(MAPPING.WAIVEBENEFITS, 0),
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from
(
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
) MAPPING
inner join dbo.BATCHREVENUEREGISTRANT on
MAPPING.REGISTRANTID = BATCHREVENUEREGISTRANT.ID
or
(
MAPPING.EVENTID = BATCHREVENUEREGISTRANT.EVENTID
and
MAPPING.CONSTITUENTID = BATCHREVENUEREGISTRANT.CONSTITUENTID
and
@BATCHID = BATCHREVENUEREGISTRANT.BATCHID
)
where
BATCHREVENUEREGISTRANT.WILLNOTATTEND = 1
or BATCHREVENUEREGISTRANT.BENEFITSWAIVED <> coalesce(MAPPING.WAIVEBENEFITS, 0);
-- Update ISWALKIN
update dbo.BATCHREVENUEREGISTRANT
set
ISWALKIN = @ISWALKIN,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from
(
select
MAPPINGAGGREGATE.REGISTRANTID,
MAPPINGAGGREGATE.EVENTID,
MAPPINGAGGREGATE.CONSTITUENTID
from
@REGISTRANTEVENTMAPPINGTABLE MAPPINGAGGREGATE
) MAPPING
inner join dbo.BATCHREVENUEREGISTRANT on
MAPPING.REGISTRANTID = BATCHREVENUEREGISTRANT.ID
or
(
MAPPING.EVENTID = BATCHREVENUEREGISTRANT.EVENTID
and
MAPPING.CONSTITUENTID = BATCHREVENUEREGISTRANT.CONSTITUENTID
and
@BATCHID = BATCHREVENUEREGISTRANT.BATCHID
)
where
BATCHREVENUEREGISTRANT.ISWALKIN <> @ISWALKIN
--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.BATCHREVENUEREGISTRANTPACKAGE
(
[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.BATCHREVENUEREGISTRANTPACKAGE on REGISTRATIONMAPPING.REGISTRANTPACKAGEID = BATCHREVENUEREGISTRANTPACKAGE.ID
where
REGISTRATIONMAPPING.REGISTRATIONPACKAGEID <> '00000000-0000-0000-0000-000000000000'
and BATCHREVENUEREGISTRANTPACKAGE.ID is null;
--Note: Ignore DATEPURCHASED when determining whether or not to update the registrant registration row.
update dbo.BATCHREVENUEREGISTRANTREGISTRATION
set
EVENTPRICEID = SELECTEDREGISTRATION.EVENTPRICEID,
QUANTITY = SELECTEDREGISTRATION.QUANTITY,
AMOUNT = SELECTEDREGISTRATION.AMOUNT,
RECEIPTAMOUNT = SELECTEDREGISTRATION.RECEIPTAMOUNT,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from
dbo.BATCHREVENUEREGISTRANTREGISTRATION
inner join @REGISTRATIONTOADDTABLE SELECTEDREGISTRATION on BATCHREVENUEREGISTRANTREGISTRATION.ID = SELECTEDREGISTRATION.REGISTRANTREGISTRATIONID
where
BATCHREVENUEREGISTRANTREGISTRATION.EVENTPRICEID <> SELECTEDREGISTRATION.EVENTPRICEID
or BATCHREVENUEREGISTRANTREGISTRATION.QUANTITY <> SELECTEDREGISTRATION.QUANTITY
or BATCHREVENUEREGISTRANTREGISTRATION.AMOUNT <> SELECTEDREGISTRATION.AMOUNT
or BATCHREVENUEREGISTRANTREGISTRATION.RECEIPTAMOUNT <> SELECTEDREGISTRATION.RECEIPTAMOUNT;
update @REGISTRATIONTOADDTABLE
set
REGISTRANTREGISTRATIONID = newid()
where
REGISTRANTREGISTRATIONID is null;
--Add the registrations
insert into dbo.BATCHREVENUEREGISTRANTREGISTRATION
(
ID,
REGISTRANTID,
EVENTPRICEID,
QUANTITY,
AMOUNT,
RECEIPTAMOUNT,
DATEPURCHASED,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
SELECTEDREGISTRATION.REGISTRANTREGISTRATIONID,
BATCHREVENUEREGISTRANT.ID,
SELECTEDREGISTRATION.EVENTPRICEID,
SELECTEDREGISTRATION.QUANTITY,
SELECTEDREGISTRATION.AMOUNT,
SELECTEDREGISTRATION.RECEIPTAMOUNT,
SELECTEDREGISTRATION.DATEPURCHASED,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
@REGISTRATIONTOADDTABLE SELECTEDREGISTRATION
inner join dbo.BATCHREVENUEREGISTRANT on
SELECTEDREGISTRATION.EVENTID = BATCHREVENUEREGISTRANT.EVENTID
and @CONSTITUENTID = BATCHREVENUEREGISTRANT.CONSTITUENTID
and @BATCHID = BATCHREVENUEREGISTRANT.BATCHID
left join dbo.BATCHREVENUEREGISTRANTREGISTRATION on SELECTEDREGISTRATION.REGISTRANTREGISTRATIONID = BATCHREVENUEREGISTRANTREGISTRATION.ID
where
BATCHREVENUEREGISTRANTREGISTRATION.ID is null
and SELECTEDREGISTRATION.QUANTITY > 0;
--Add the registrant registration maps
update @REGISTRANTEVENTMAPPINGTABLE
set
REGISTRANTREGISTRATIONMAPID = newid()
where
REGISTRANTREGISTRATIONMAPID is null;
insert into dbo.BATCHREVENUEREGISTRANTREGISTRATIONMAP
(
ID,
REGISTRANTREGISTRATIONID,
REGISTRANTID,
REGISTRANTPACKAGEID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
MAPPING.REGISTRANTREGISTRATIONMAPID,
SELECTEDREGISTRATION.REGISTRANTREGISTRATIONID,
coalesce(BATCHREVENUEREGISTRANT.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.BATCHREVENUEREGISTRANT on
SELECTEDREGISTRATION.EVENTID = BATCHREVENUEREGISTRANT.EVENTID
and REGISTRANTMAPPING.CONSTITUENTID = BATCHREVENUEREGISTRANT.CONSTITUENTID
and BATCHREVENUEREGISTRANT.BATCHID = @BATCHID
left join @REGISTRANTEVENTMAPPINGTABLE UNKNOWNGUESTMAPPING on
SELECTEDREGISTRATION.EVENTID = UNKNOWNGUESTMAPPING.EVENTID
and REGISTRANTMAPPING.MAPPINGID = UNKNOWNGUESTMAPPING.MAPPINGID
left join dbo.BATCHREVENUEREGISTRANTREGISTRATIONMAP on MAPPING.REGISTRANTREGISTRATIONMAPID = BATCHREVENUEREGISTRANTREGISTRATIONMAP.ID
where
BATCHREVENUEREGISTRANTREGISTRATIONMAP.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.BATCHREVENUEREGISTRANTPREFERENCE
set
REGISTRANTID = coalesce(BATCHREVENUEREGISTRANT.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.BATCHREVENUEREGISTRANTPREFERENCE on SELECTEDPREFERENCE.REGISTRANTPREFERENCEID = BATCHREVENUEREGISTRANTPREFERENCE.ID
inner join dbo.EVENTPREFERENCE on SELECTEDPREFERENCE.EVENTPREFERENCEID = EVENTPREFERENCE.ID
inner join dbo.EVENTPREFERENCEGROUP on EVENTPREFERENCE.EVENTPREFERENCEGROUPID = EVENTPREFERENCEGROUP.ID
left join dbo.BATCHREVENUEREGISTRANT on
EVENTPREFERENCEGROUP.EVENTID = BATCHREVENUEREGISTRANT.EVENTID
and REGISTRANTMAPPING.CONSTITUENTID = BATCHREVENUEREGISTRANT.CONSTITUENTID
and @BATCHID = BATCHREVENUEREGISTRANT.BATCHID
left join @REGISTRANTEVENTMAPPINGTABLE MAPPING on
EVENTPREFERENCEGROUP.EVENTID = MAPPING.EVENTID
and REGISTRANTMAPPING.MAPPINGID = MAPPING.MAPPINGID
where
BATCHREVENUEREGISTRANTPREFERENCE.REGISTRANTID <> coalesce(BATCHREVENUEREGISTRANT.ID, MAPPING.REGISTRANTID)
or
BATCHREVENUEREGISTRANTPREFERENCE.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.BATCHREVENUEREGISTRANTPREFERENCE
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.BATCHREVENUEREGISTRANTPREFERENCE on SELECTEDPREFERENCE.REGISTRANTPREFERENCEID = BATCHREVENUEREGISTRANTPREFERENCE.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(BATCHREVENUEREGISTRANT.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.BATCHREVENUEREGISTRANT on
EVENTPREFERENCEGROUP.EVENTID = BATCHREVENUEREGISTRANT.EVENTID
and REGISTRANTMAPPING.CONSTITUENTID = BATCHREVENUEREGISTRANT.CONSTITUENTID
and @BATCHID = BATCHREVENUEREGISTRANT.BATCHID
left join @REGISTRANTEVENTMAPPINGTABLE MAPPING on
EVENTPREFERENCEGROUP.EVENTID = MAPPING.EVENTID
and REGISTRANTMAPPING.MAPPINGID = MAPPING.MAPPINGID
left join dbo.BATCHREVENUEREGISTRANTPREFERENCE on SELECTEDPREFERENCE.REGISTRANTPREFERENCEID = BATCHREVENUEREGISTRANTPREFERENCE.ID
where
BATCHREVENUEREGISTRANTPREFERENCE.ID is null;
insert into dbo.BATCHREVENUEREGISTRANTPREFERENCE
(
ID,
REGISTRANTID,
EVENTPREFERENCEID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
[PREFERENCETOADD].ID,
[PREFERENCETOADD].REGISTRANTID,
[PREFERENCETOADD].EVENTPREFERENCEID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
@REGISTRANTPREFERENCETOADD [PREFERENCETOADD];
insert into dbo.BATCHREVENUEREGISTRANTPREFERENCEMAP
(
ID,
REGISTRANTREGISTRATIONMAPID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
[PREFERENCETOADD].ID,
[PREFERENCETOADD].REGISTRANTREGISTRATIONMAPID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
@REGISTRANTPREFERENCETOADD [PREFERENCETOADD]
left join dbo.BATCHREVENUEREGISTRANTREGISTRATIONMAP on [PREFERENCETOADD].REGISTRANTREGISTRATIONMAPID = BATCHREVENUEREGISTRANTREGISTRATIONMAP.ID
where
BATCHREVENUEREGISTRANTREGISTRATIONMAP.ID is not null;
-- TODO: include team fundraising
--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.BATCHREVENUETEAMFUNDRAISER
--(
-- APPEALID,
-- CONSTITUENTID,
-- GOAL,
-- ADDEDBYID,
-- CHANGEDBYID,
-- DATEADDED,
-- DATECHANGED
--)
--select distinct
-- EVENT.APPEALID,
-- REGISTRANTMAPPING.CONSTITUENTID,
-- max(CONSTITUENTGOAL.TEAMFUNDRAISERGOAL),
-- @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.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;
----Update any existing team fundraiser records with changed goals
--update dbo.TEAMFUNDRAISER set
-- GOAL = [TEAMFUNDRAISINGGOALDISTINCT].TEAMFUNDRAISERGOAL,
-- 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
--where
-- [TEAMFUNDRAISINGGOALDISTINCT].TEAMFUNDRAISERGOAL is not null
-- and TEAMFUNDRAISER.GOAL <> [TEAMFUNDRAISINGGOALDISTINCT].TEAMFUNDRAISERGOAL;
----Add a team member record for new teams
--insert into dbo.BATCHREVENUETEAMFUNDRAISINGTEAMMEMBER
--(
-- 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.BATCHREVENUETEAMFUNDRAISINGTEAMCAPTAIN
--(
-- 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 nul
if @REGISTRANTEXISTSWITHID = 0
begin
--Only add benefits when called from an add form, not when called from an edit form
--TODO: Handle waive benefits
--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.BATCHREVENUEREGISTRANTBENEFIT
(
REGISTRANTID,
BENEFITID,
UNITVALUE,
QUANTITY,
TOTALVALUE,
DETAILS,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
coalesce(REGISTRANTEVENTMAPPING.REGISTRANTID, BATCHREVENUEREGISTRANT.ID),
EVENTPRICEBENEFIT.BENEFITID,
EVENTPRICEBENEFIT.UNITVALUE,
EVENTPRICEBENEFIT.QUANTITY,
(EVENTPRICEBENEFIT.UNITVALUE * EVENTPRICEBENEFIT.QUANTITY),
EVENTPRICEBENEFIT.DETAILS,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
@REGISTRANTEVENTMAPPINGTABLE REGISTRANTEVENTMAPPING
inner join dbo.EVENTPRICEBENEFIT on REGISTRANTEVENTMAPPING.EVENTPRICEID = EVENTPRICEBENEFIT.EVENTPRICEID
left join dbo.EVENTPRICE on REGISTRANTEVENTMAPPING.EVENTPRICEID = EVENTPRICE.ID
left join dbo.EVENTREGISTRATIONTYPE on EVENTPRICE.EVENTREGISTRATIONTYPEID = EVENTREGISTRATIONTYPE.ID
left join dbo.BATCHREVENUEREGISTRANT on
REGISTRANTEVENTMAPPING.EVENTID = BATCHREVENUEREGISTRANT.EVENTID
and REGISTRANTEVENTMAPPING.CONSTITUENTID = BATCHREVENUEREGISTRANT.CONSTITUENTID
and @BATCHID = BATCHREVENUEREGISTRANT.BATCHID
where
EVENTREGISTRATIONTYPE.REGISTRATIONCOUNT = 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.BATCHREVENUEREGISTRANTBENEFIT
(
REGISTRANTID,
BENEFITID,
UNITVALUE,
QUANTITY,
TOTALVALUE,
DETAILS,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
BATCHREVENUEREGISTRANT.ID,
EVENTPRICEBENEFIT.BENEFITID,
(SELECTEDREGISTRATION.QUANTITY * EVENTPRICEBENEFIT.UNITVALUE),
(SELECTEDREGISTRATION.QUANTITY * EVENTPRICEBENEFIT.QUANTITY),
((SELECTEDREGISTRATION.QUANTITY * EVENTPRICEBENEFIT.UNITVALUE) * (SELECTEDREGISTRATION.QUANTITY * EVENTPRICEBENEFIT.QUANTITY)),
EVENTPRICEBENEFIT.DETAILS,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
@REGISTRATIONTOADDTABLE SELECTEDREGISTRATION
inner join dbo.EVENTPRICEBENEFIT on SELECTEDREGISTRATION.EVENTPRICEID = EVENTPRICEBENEFIT.EVENTPRICEID
left join dbo.EVENTPRICE on SELECTEDREGISTRATION.EVENTPRICEID = EVENTPRICE.ID
left join dbo.EVENTREGISTRATIONTYPE on EVENTPRICE.EVENTREGISTRATIONTYPEID = EVENTREGISTRATIONTYPE.ID
left join dbo.BATCHREVENUEREGISTRANT on
SELECTEDREGISTRATION.EVENTID = BATCHREVENUEREGISTRANT.EVENTID
and @CONSTITUENTID = BATCHREVENUEREGISTRANT.CONSTITUENTID
and @BATCHID = BATCHREVENUEREGISTRANT.BATCHID
where
EVENTREGISTRATIONTYPE.REGISTRATIONCOUNT > 1
and SELECTEDREGISTRATION.QUANTITY > 0;
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 main event return the registrant ID for a sub event.
--On an add form, the ID may be passed in, make sure a record exists.
if not exists
(
select
BATCHREVENUEREGISTRANT.ID
from
dbo.BATCHREVENUEREGISTRANT
where
BATCHREVENUEREGISTRANT.ID = @ID
)
set @ID = null;
if @ID is null
select
@ID = ID
from
dbo.BATCHREVENUEREGISTRANT
where
CONSTITUENTID = @CONSTITUENTID and
EVENTID = @EVENTID and
BATCHID = @BATCHID;
if @ID is null
select top(1)
@ID = BATCHREVENUEREGISTRANT.ID
from
dbo.BATCHREVENUEREGISTRANT
inner join dbo.EVENT on BATCHREVENUEREGISTRANT.EVENTID = EVENT.ID
where
BATCHREVENUEREGISTRANT.CONSTITUENTID = @CONSTITUENTID and
EVENT.MAINEVENTID = @EVENTID and
BATCHREVENUEREGISTRANT.BATCHID = @BATCHID
order by
EVENT.NAME;
if @ID is null
select top(1)
@ID = BATCHREVENUEREGISTRANT.ID
from
dbo.BATCHREVENUEREGISTRANT
inner join @REGISTRANTMAPPINGTABLE RMT on BATCHREVENUEREGISTRANT.EVENTID = RMT.EVENTID
inner join dbo.EVENT on RMT.EVENTID = EVENT.ID
where
BATCHREVENUEREGISTRANT.CONSTITUENTID = @CONSTITUENTID
and BATCHREVENUEREGISTRANT.BATCHID = @BATCHID
order by
EVENT.NAME;
return 0;