USP_DATAFORMTEMPLATE_ADD_REGISTRANTFROMINVITEETEAMFUNDRAISER
The save procedure used by the add dataform template "Registrant Team Fundraising from Invitee Add Form".
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | INOUT | The output parameter indicating the ID of the record added. |
| @CHANGEAGENTID | uniqueidentifier | IN | Input parameter indicating the ID of the change agent invoking the procedure. |
| @INVITEEID | uniqueidentifier | IN | Input parameter indicating the context ID for the record being added. |
| @CONSTITUENTID | uniqueidentifier | IN | Registrant |
| @DATEPURCHASED | datetime | IN | Date |
| @WAIVEBENEFITS | bit | IN | Waive benefits |
| @REGISTRATIONS | xml | IN | Registrations |
| @REGISTRANTMAPPINGS | xml | IN | Guests and details |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_REGISTRANTFROMINVITEETEAMFUNDRAISER
(
@ID uniqueidentifier = null output,
@CHANGEAGENTID uniqueidentifier = null,
@INVITEEID uniqueidentifier,
@CONSTITUENTID uniqueidentifier = null,
@DATEPURCHASED datetime = null,
@WAIVEBENEFITS bit = 0,
@REGISTRATIONS xml = null,
@REGISTRANTMAPPINGS xml = null
)
as
set nocount on;
declare @CURRENTDATE datetime;
if @ID is null
set @ID = newid();
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
set @CURRENTDATE = getdate();
begin try
declare @EVENTID uniqueidentifier;
select @EVENTID = EVENTID from dbo.INVITEE where ID = @INVITEEID;
declare @REGISTRANTMAPPINGTABLE table
(
REGISTRANTID uniqueidentifier default newid(),
EVENTPRICEID uniqueidentifier,
CONSTITUENTID uniqueidentifier,
TEAMFUNDRAISERTYPECODE tinyint,
TEAMFUNDRAISINGTEAMID uniqueidentifier,
GOAL money,
ISTEAMCAPTAIN bit
);
insert into @REGISTRANTMAPPINGTABLE
(
EVENTPRICEID,
CONSTITUENTID,
TEAMFUNDRAISERTYPECODE,
TEAMFUNDRAISINGTEAMID,
GOAL,
ISTEAMCAPTAIN
)
--TODO: Change to FROMITEMLISTXML function
SELECT
T.c.value('(ID)[1]','uniqueidentifier') AS 'ID',
T.c.value('(CONSTITUENTID)[1]','uniqueidentifier') AS 'CONSTITUENTID',
T.c.value('(TEAMFUNDRAISERTYPECODE)[1]','bit') AS 'TEAMFUNDRAISERTYPECODE',
T.c.value('(TEAMFUNDRAISINGTEAMID)[1]','uniqueidentifier') AS 'TEAMFUNDRAISINGTEAMID',
T.c.value('(GOAL)[1]','money') AS 'GOAL',
T.c.value('(ISTEAMCAPTAIN)[1]','bit') AS 'ISTEAMCAPTAIN'
FROM @REGISTRANTMAPPINGS.nodes('/REGISTRANTMAPPINGS/ITEM') T(c);
declare @WILLNOTATTEND bit;
--Mark the registrant as WILLNOTATTEND if they are not mapped to a registration
if not exists
(
select
CONSTITUENTID
from
@REGISTRANTMAPPINGTABLE
where
CONSTITUENTID = @CONSTITUENTID
)
set @WILLNOTATTEND = 1;
else
set @WILLNOTATTEND = 0;
insert into dbo.REGISTRANT
(
ID,
CONSTITUENTID,
EVENTID,
BENEFITSWAIVED,
WILLNOTATTEND,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values
(
@ID,
@CONSTITUENTID,
@EVENTID,
@WAIVEBENEFITS,
@WILLNOTATTEND,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
declare @SELECTEDREGISTRATIONTABLE table
(
REGISTRANTREGISTRATIONID uniqueidentifier default newid(),
EVENTPRICEID uniqueidentifier,
AMOUNT money,
RECEIPTAMOUNT money,
QUANTITY int
);
insert into @SELECTEDREGISTRATIONTABLE
(
EVENTPRICEID,
AMOUNT,
RECEIPTAMOUNT,
QUANTITY
)
select
EVENTPRICEID,
AMOUNT,
RECEIPTAMOUNT,
QUANTITY
from
dbo.UFN_REGISTRANT_GETREGISTRATIONS_FROMITEMLISTXML(@REGISTRATIONS);
insert into dbo.REGISTRANTREGISTRATION
(
ID,
REGISTRANTID,
EVENTPRICEID,
AMOUNT,
RECEIPTAMOUNT,
QUANTITY,
DATEPURCHASED,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
REGISTRANTREGISTRATIONID,
@ID,
EVENTPRICEID,
AMOUNT,
RECEIPTAMOUNT,
QUANTITY,
@DATEPURCHASED,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
@SELECTEDREGISTRATIONTABLE;
--Add guests, both known and unknown
insert into dbo.REGISTRANT
(
ID,
EVENTID,
CONSTITUENTID,
GUESTOFREGISTRANTID,
BENEFITSWAIVED,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
REGISTRANTMAPPING.REGISTRANTID,
@EVENTID,
REGISTRANTMAPPING.CONSTITUENTID,
@ID,
@WAIVEBENEFITS,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
@REGISTRANTMAPPINGTABLE REGISTRANTMAPPING
where
REGISTRANTMAPPING.CONSTITUENTID is null
or REGISTRANTMAPPING.CONSTITUENTID <> @CONSTITUENTID;
--The registrant was inserted with an earlier statement, update the registrant ID for use when inserting benefits
update @REGISTRANTMAPPINGTABLE
set
REGISTRANTID = @ID
where
CONSTITUENTID = @CONSTITUENTID;
--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
)
select
REGISTRANTMAPPING.REGISTRANTID,
EVENTPRICEBENEFIT.BENEFITID,
EVENTPRICEBENEFIT.UNITVALUE,
EVENTPRICEBENEFIT.QUANTITY,
(EVENTPRICEBENEFIT.UNITVALUE * EVENTPRICEBENEFIT.QUANTITY),
EVENTPRICEBENEFIT.DETAILS,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
@REGISTRANTMAPPINGTABLE REGISTRANTMAPPING
inner join dbo.EVENTPRICEBENEFIT on REGISTRANTMAPPING.EVENTPRICEID = EVENTPRICEBENEFIT.EVENTPRICEID
left join dbo.EVENTPRICE on REGISTRANTMAPPING.EVENTPRICEID = EVENTPRICE.ID
left join dbo.EVENTREGISTRATIONTYPE on EVENTPRICE.EVENTREGISTRATIONTYPEID = EVENTREGISTRATIONTYPE.ID
where
EVENTPRICE.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.REGISTRANTBENEFIT
(
REGISTRANTID,
BENEFITID,
UNITVALUE,
QUANTITY,
TOTALVALUE,
DETAILS,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
@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
@SELECTEDREGISTRATIONTABLE 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
where
EVENTPRICE.REGISTRATIONCOUNT > 1;
--Team fundraising
declare @APPEALID uniqueidentifier;
select
@APPEALID = EVENT.APPEALID
from
dbo.EVENT
where
EVENT.ID = @EVENTID;
update
dbo.TEAMFUNDRAISER
set
GOAL = REGISTRANTMAPPING.GOAL,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from
@REGISTRANTMAPPINGTABLE REGISTRANTMAPPING
inner join dbo.TEAMFUNDRAISER on
REGISTRANTMAPPING.CONSTITUENTID = TEAMFUNDRAISER.CONSTITUENTID
and @APPEALID = TEAMFUNDRAISER.APPEALID
where
TEAMFUNDRAISER.GOAL <> REGISTRANTMAPPING.GOAL
and
(
REGISTRANTMAPPING.TEAMFUNDRAISERTYPECODE = 1
or
REGISTRANTMAPPING.TEAMFUNDRAISERTYPECODE = 2
);
insert into dbo.TEAMFUNDRAISER
(
APPEALID,
CONSTITUENTID,
GOAL,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
@APPEALID,
REGISTRANTMAPPING.CONSTITUENTID,
REGISTRANTMAPPING.GOAL,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
@REGISTRANTMAPPINGTABLE REGISTRANTMAPPING
left join dbo.TEAMFUNDRAISER on REGISTRANTMAPPING.CONSTITUENTID = TEAMFUNDRAISER.CONSTITUENTID
where
TEAMFUNDRAISER.ID is null
and
(
REGISTRANTMAPPING.TEAMFUNDRAISERTYPECODE = 1
or
REGISTRANTMAPPING.TEAMFUNDRAISERTYPECODE = 2
);
insert into dbo.TEAMFUNDRAISINGTEAMMEMBER
(
TEAMFUNDRAISINGTEAMID,
TEAMFUNDRAISERID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
REGISTRANTMAPPING.TEAMFUNDRAISINGTEAMID,
TEAMFUNDRAISER.ID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
@REGISTRANTMAPPINGTABLE REGISTRANTMAPPING
left join dbo.TEAMFUNDRAISER on
REGISTRANTMAPPING.CONSTITUENTID = TEAMFUNDRAISER.CONSTITUENTID
and @APPEALID = TEAMFUNDRAISER.APPEALID
left join dbo.TEAMFUNDRAISINGTEAMMEMBER on
REGISTRANTMAPPING.TEAMFUNDRAISINGTEAMID = TEAMFUNDRAISINGTEAMMEMBER.TEAMFUNDRAISINGTEAMID
and TEAMFUNDRAISER.ID = TEAMFUNDRAISINGTEAMMEMBER.TEAMFUNDRAISERID
where
REGISTRANTMAPPING.TEAMFUNDRAISERTYPECODE = 1
and TEAMFUNDRAISINGTEAMMEMBER.ID is null;
insert into dbo.TEAMFUNDRAISINGTEAMMEMBER
(
TEAMFUNDRAISINGTEAMID,
TEAMFUNDRAISERID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
null,
TEAMFUNDRAISER.ID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
@REGISTRANTMAPPINGTABLE REGISTRANTMAPPING
left join dbo.TEAMFUNDRAISER on
REGISTRANTMAPPING.CONSTITUENTID = TEAMFUNDRAISER.CONSTITUENTID
and @APPEALID = TEAMFUNDRAISER.APPEALID
left join dbo.TEAMFUNDRAISINGTEAMMEMBER on
TEAMFUNDRAISINGTEAMMEMBER.TEAMFUNDRAISINGTEAMID is null
and TEAMFUNDRAISER.ID = TEAMFUNDRAISINGTEAMMEMBER.TEAMFUNDRAISERID
where
REGISTRANTMAPPING.TEAMFUNDRAISERTYPECODE = 2
and TEAMFUNDRAISINGTEAMMEMBER.ID is null;
insert into dbo.TEAMFUNDRAISINGTEAMCAPTAIN
(
TEAMFUNDRAISINGTEAMID,
CONSTITUENTID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
REGISTRANTMAPPING.TEAMFUNDRAISINGTEAMID,
REGISTRANTMAPPING.CONSTITUENTID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
@REGISTRANTMAPPINGTABLE REGISTRANTMAPPING
left join dbo.TEAMFUNDRAISINGTEAMCAPTAIN on
REGISTRANTMAPPING.TEAMFUNDRAISINGTEAMID = TEAMFUNDRAISINGTEAMCAPTAIN.TEAMFUNDRAISINGTEAMID
and REGISTRANTMAPPING.CONSTITUENTID = TEAMFUNDRAISINGTEAMCAPTAIN.CONSTITUENTID
where
REGISTRANTMAPPING.TEAMFUNDRAISERTYPECODE = 1
and REGISTRANTMAPPING.ISTEAMCAPTAIN = 1
and TEAMFUNDRAISINGTEAMCAPTAIN.ID is null;
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;