USP_DATAFORMTEMPLATE_ADD_REGISTRANTFROMINVITEE
The save procedure used by the add dataform template "Registrant 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_REGISTRANTFROMINVITEE
(
@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
);
insert into @REGISTRANTMAPPINGTABLE
(
EVENTPRICEID,
CONSTITUENTID
)
--TODO: Change to FROMITEMLISTXML function
SELECT
T.c.value('(ID)[1]','uniqueidentifier') AS 'ID',
T.c.value('(CONSTITUENTID)[1]','uniqueidentifier') AS 'CONSTITUENTID'
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;
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;