USP_REGISTRANT_APPENDREGISTRATIONS
Adds registrations and guests to a registrant, creating the registrant record if it does not exist.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | INOUT | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @EVENTID | uniqueidentifier | IN | |
| @CONSTITUENTID | uniqueidentifier | IN | |
| @DATEPURCHASED | datetime | IN | |
| @EVENTPRICEID | uniqueidentifier | IN | |
| @REGISTRANTSTATUS | tinyint | IN | |
| @WAIVEBENEFITS | bit | IN | |
| @QUANTITY | int | IN | |
| @GUESTS | xml | IN | |
| @REGISTRANTLOOKUPID | nvarchar(100) | IN | |
| @DESIGNATIONID | uniqueidentifier | IN | |
| @REGISTRATIONTYPECODE | tinyint | IN | |
| @REGISTRATIONSTATUSCODE | tinyint | IN | |
| @REGISTRATIONATTENDEDCODE | tinyint | IN |
Definition
Copy
CREATE procedure dbo.USP_REGISTRANT_APPENDREGISTRATIONS
(
@ID uniqueidentifier = null output,
@CHANGEAGENTID uniqueidentifier = null,
@EVENTID uniqueidentifier,
@CONSTITUENTID uniqueidentifier,
@DATEPURCHASED datetime = null,
@EVENTPRICEID uniqueidentifier,
@REGISTRANTSTATUS tinyint = 0,
@WAIVEBENEFITS bit = 0,
@QUANTITY int = 1,
@GUESTS xml = null,
@REGISTRANTLOOKUPID nvarchar(100) = '',
@DESIGNATIONID uniqueidentifier = null,
@REGISTRATIONTYPECODE tinyint = 0,
@REGISTRATIONSTATUSCODE tinyint = null,
@REGISTRATIONATTENDEDCODE tinyint = null
)
with execute as caller
as
set nocount on;
declare @UNKNOWNGUESTWELLKNOWNGUID uniqueidentifier;
set @UNKNOWNGUESTWELLKNOWNGUID = 'C776DAB5-65B2-4258-ADAE-AE396D28E251'; --Well-known GUID for unknown guest
declare @CURRENTDATE datetime;
set @CURRENTDATE = getdate();
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
if @REGISTRANTLOOKUPID is null
set @REGISTRANTLOOKUPID = '';
if @QUANTITY is null or @QUANTITY < 1
raiserror('ERR_REGISTRANTREGISTRATION_INVALIDQUANTITY',13,1);
--Use the existing registrant ID if one exists
select
@ID = REGISTRANT.ID
from
dbo.REGISTRANT
where
REGISTRANT.EVENTID = @EVENTID
and REGISTRANT.CONSTITUENTID = @CONSTITUENTID;
if @ID is null
set @ID = newid();
declare @REGISTRANTISNEW bit;
set @REGISTRANTISNEW = 0;
--Build a table of guests
declare @MAPPEDREGISTRANTTABLE table
(
REGISTRANTID uniqueidentifier,
REGISTRANTREGISTRATIONID uniqueidentifier,
CONSTITUENTID uniqueidentifier,
ATTENDED bit,
WAIVEREGISTRATIONFEE bit,
SEQUENCE int IDENTITY(0,1)
);
insert into @MAPPEDREGISTRANTTABLE
(
REGISTRANTID,
CONSTITUENTID,
ATTENDED,
WAIVEREGISTRATIONFEE
)
select
case
when [GUESTS].ITEM.value('CONSTITUENTID[1]', 'uniqueidentifier') = @CONSTITUENTID
then @ID
when
[GUESTS].ITEM.value('ID[1]', 'uniqueidentifier') = '00000000-0000-0000-0000-000000000000'
or
[GUESTS].ITEM.exist('ID') = 0
then newid()
else
[GUESTS].ITEM.value('ID[1]', 'uniqueidentifier')
end,
[GUESTS].ITEM.value('CONSTITUENTID[1]', 'uniqueidentifier'),
[GUESTS].ITEM.value('ATTENDED[1]', 'bit'),
ISNULL([GUESTS].ITEM.value('WAIVEREGISTRATIONFEE[1]', 'bit'),0)
from
@GUESTS.nodes('/GUESTS/ITEM') as [GUESTS](ITEM);
-- deleted validation logic here, since validation is performed in USP_DATAFORMTEMPLATE_ADD_BATCHEVENTREGISTRANTBATCHCOMMIT
-- before calling this sp
declare @EVENTPRICEAMOUNT money = 0;
declare @EVENTCOST money = 0;
declare @EVENTPRICERECEIPTAMOUNT money = 0;
declare @REGISTRANTCOUNT as int;
declare @EVENTPRICEREGISTRATIONCOUNT as int = 0;
declare @VALIDEVENTPRICE as bit = 0;
select
@VALIDEVENTPRICE = 1,
@EVENTPRICEAMOUNT = EVENTPRICE.AMOUNT,
@EVENTCOST = isnull(EVENTPRICE.COST, 0),
@EVENTPRICERECEIPTAMOUNT = EVENTPRICE.RECEIPTAMOUNT,
@REGISTRANTCOUNT = (EVENTPRICE.REGISTRATIONCOUNT * @QUANTITY),
@EVENTPRICEREGISTRATIONCOUNT = EVENTPRICE.REGISTRATIONCOUNT
from
dbo.EVENTPRICE
where
EVENTPRICE.ID = @EVENTPRICEID;
if @VALIDEVENTPRICE = 0
raiserror('ERR_REGISTRANTREGISTRATION_INVALIDEVENTPRICEID', 13, 1);
declare @REGISTRANTCOUNTDIFFERENCE int;
--KevinKoe 8/9/11 - If the registrant is included in the guest list, then do not subtract him from the count, otherwise he will be set
-- as "Will not attend", which is incorrect
set @REGISTRANTCOUNTDIFFERENCE = (@REGISTRANTCOUNT - (select count(*) from @MAPPEDREGISTRANTTABLE where REGISTRANTID <> @ID))
-- deleted validation logic here, since validation is performed in USP_DATAFORMTEMPLATE_ADD_BATCHEVENTREGISTRANTBATCHCOMMIT
-- before calling this sp
--AlexLa 2011-6-30 Bug 162766 If only guests going, set status of registrant to will not attend
--Moving insert and update statements for Registrant table after this so they reflect the change in status.
if @REGISTRANTCOUNTDIFFERENCE = 0
begin
if @REGISTRATIONSTATUSCODE is not null and @REGISTRATIONSTATUSCODE = 0 --don't want to mess up canceled (2)
begin
set @REGISTRATIONSTATUSCODE = 1 --will not attend
--KevinKoe 8/11/11 - Set the attended code to 0, as it is not possible for someone to be marked as "Attended" and
-- "Will not attend"
if @REGISTRATIONATTENDEDCODE is not null and @REGISTRATIONATTENDEDCODE <> 0
set @REGISTRATIONATTENDEDCODE = 0 --not attended
end
if @REGISTRATIONSTATUSCODE is null and @REGISTRANTSTATUS = 0 --handle old status if called from old code.
set @REGISTRANTSTATUS = 1
end
--Insert or update the registrant.
-- Update is necessary since Event Registrant batch has one row per registrant registration.
if exists
(
select
REGISTRANT.ID
from
dbo.REGISTRANT
where
REGISTRANT.ID = @ID
and REGISTRANT.GUESTOFREGISTRANTID is null --The existing registrant must not be a guest
)
update dbo.REGISTRANT set
[ATTENDED] =
case @REGISTRATIONATTENDEDCODE
when null then
case @REGISTRANTSTATUS
when 2 then 1 --Attended
else 0
end
when 1 then 1
else 0
end,
[WILLNOTATTEND] =
case @REGISTRATIONSTATUSCODE
when null then
case @REGISTRANTSTATUS
when 1 then 1 --Registered (will not attend)
else 0
end
when 1 then 1
else 0
end,
[ISCANCELLED] =
case @REGISTRATIONSTATUSCODE
when null then
case @REGISTRANTSTATUS
when 3 then 1 --Cancelled
else 0
end
when 2 then 1
else 0
end,
[BENEFITSWAIVED] = @WAIVEBENEFITS, --TODO: Do we have to do something if waiving benefits when there are existing registrations?
[CUSTOMIDENTIFIER] = @REGISTRANTLOOKUPID,
[CHANGEDBYID] = @CHANGEAGENTID,
[DATECHANGED] = @CURRENTDATE,
[ISWALKIN] = @REGISTRATIONTYPECODE,
[USERMARKEDATTENDANCE] =
case @REGISTRATIONATTENDEDCODE
when 1 then 1
else 0
end
where
REGISTRANT.ID = @ID
and
(
[ATTENDED] <>
case @REGISTRATIONATTENDEDCODE
when null then
case @REGISTRANTSTATUS
when 2 then 1 --Attended
else 0
end
when 1 then 1
else 0
end
or
[WILLNOTATTEND] <>
case @REGISTRATIONSTATUSCODE
when null then
case @REGISTRANTSTATUS
when 1 then 1 --Registered (will not attend)
else 0
end
when 1 then 1
else 0
end
or
[ISCANCELLED] <>
case @REGISTRATIONSTATUSCODE
when null then
case @REGISTRANTSTATUS
when 3 then 1 --Cancelled
else 0
end
when 2 then 1
else 0
end
or
[BENEFITSWAIVED] <> @WAIVEBENEFITS
or
[USERMARKEDATTENDANCE] <>
case @REGISTRATIONATTENDEDCODE
when null then 0
when 0 then 0
else 1
end
);
else
begin
set @REGISTRANTISNEW = 1;
insert into dbo.REGISTRANT
(
[ID],
[EVENTID],
[CONSTITUENTID],
[ATTENDED],
[WILLNOTATTEND],
[ISCANCELLED],
[GUESTOFREGISTRANTID],
[BENEFITSWAIVED],
[CUSTOMIDENTIFIER],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED],
[ISWALKIN],
[USERMARKEDATTENDANCE]
)
values
(
@ID,
@EVENTID,
@CONSTITUENTID,
case @REGISTRATIONATTENDEDCODE
when null then
case @REGISTRANTSTATUS
when 2 then 1 --Attended
else 0
end
when 1 then 1
else 0
end,
case @REGISTRATIONSTATUSCODE
when null then
case @REGISTRANTSTATUS
when 1 then 1 --Registered (will not attend)
else 0
end
when 1 then 1
else 0
end,
case @REGISTRATIONSTATUSCODE
when null then
case @REGISTRANTSTATUS
when 2 then 1 --Cancelled
else 0
end
when 2
then 1
else 0
end,
null,
@WAIVEBENEFITS,
@REGISTRANTLOOKUPID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE,
@REGISTRATIONTYPECODE,
case @REGISTRATIONATTENDEDCODE
when 0 then 0
else 1
end
);
end
--Keith Mar Bug 206392 Event Restrictions
declare @RETURNID uniqueidentifier
insert into dbo.REGISTRANTRESTRICTIONOPTION(REGISTRANTID, EVENTRESTRICTIONOPTIONID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
select
@ID as REGISTRANTID,
EVENTRESTRICTIONOPTIONID as EVENTRESTRICTIONOPTIONID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from dbo.CONSTITUENTRESTRICTIONOPTION
where
CONSTITUENTID = @CONSTITUENTID
--TommyVe 2010-03-18 Bug 78549 Map the host to a registration if they weren't already registered and will attend
if (@REGISTRANTCOUNTDIFFERENCE > 0)
and (@REGISTRANTISNEW = 1)
and (@REGISTRANTSTATUS <> 1 and @REGISTRATIONSTATUSCODE <> 1) --Registered (will not attend)
and not exists (select 1 from @MAPPEDREGISTRANTTABLE [MAPPEDREGISTRANTTABLE] where [MAPPEDREGISTRANTTABLE].CONSTITUENTID = @CONSTITUENTID)
insert into @MAPPEDREGISTRANTTABLE
(
REGISTRANTID,
CONSTITUENTID,
ATTENDED,
WAIVEREGISTRATIONFEE
)
values
(
@ID,
@CONSTITUENTID,
case @REGISTRATIONATTENDEDCODE
when null then
case @REGISTRANTSTATUS
when 1 then 1 --Registered (will not attend)
else 0
end
when 1 then 1
else 0
end,
0
);
--Create extra unnamed guests for the remaining registrations
--- added the @GUESTS is not null check for bug 167019
-- Commenting out this fix for now...
if @REGISTRANTCOUNTDIFFERENCE > 0 -- and @GUESTS is not null
while (@REGISTRANTCOUNT - (select count(*) from @MAPPEDREGISTRANTTABLE)) > 0
insert into @MAPPEDREGISTRANTTABLE
(
REGISTRANTID,
CONSTITUENTID,
ATTENDED,
WAIVEREGISTRATIONFEE
)
values
(
newid(),
null,
0,
0
);
--Add guests
insert into dbo.REGISTRANT
(
[ID],
[EVENTID],
[CONSTITUENTID],
[ATTENDED],
[WILLNOTATTEND],
[ISCANCELLED],
[GUESTOFREGISTRANTID],
[BENEFITSWAIVED],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED],
[ISWALKIN]
)
select
[MAPPEDREGISTRANT].REGISTRANTID,
@EVENTID,
case [MAPPEDREGISTRANT].CONSTITUENTID when @UNKNOWNGUESTWELLKNOWNGUID then null
else [MAPPEDREGISTRANT].CONSTITUENTID
end as CONSTITUENTID,
case when @REGISTRATIONTYPECODE = 1 then 1 else [MAPPEDREGISTRANT].ATTENDED end, --If walk-in, overwrite ATTENDED checkbox
0, --Will attend
0, --Not cancelled
@ID,
@WAIVEBENEFITS,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE,
@REGISTRATIONTYPECODE --Bug 288957 - Guest registrants should be walk-in/preregistered based on their host
from
@MAPPEDREGISTRANTTABLE [MAPPEDREGISTRANT]
where
[MAPPEDREGISTRANT].CONSTITUENTID <> @CONSTITUENTID
or [MAPPEDREGISTRANT].CONSTITUENTID is null
or [MAPPEDREGISTRANT].CONSTITUENTID = @UNKNOWNGUESTWELLKNOWNGUID;
-- Event Restrictions
insert into dbo.REGISTRANTRESTRICTIONOPTION(REGISTRANTID, EVENTRESTRICTIONOPTIONID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
select
[MAPPEDREGISTRANT].REGISTRANTID as REGISTRANTID,
EVENTRESTRICTIONOPTIONID as EVENTRESTRICTIONOPTIONID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
@MAPPEDREGISTRANTTABLE [MAPPEDREGISTRANT]
inner join dbo.CONSTITUENTRESTRICTIONOPTION
on [MAPPEDREGISTRANT].CONSTITUENTID = CONSTITUENTRESTRICTIONOPTION.CONSTITUENTID
where
[MAPPEDREGISTRANT].CONSTITUENTID <> @CONSTITUENTID
declare @NEWREGISTRANTREGISTRATIONID_WAIVEDFEE uniqueidentifier = newid();
declare @NEWREGISTRANTREGISTRATIONID_NOTWAIVEDFEE uniqueidentifier = newid();
declare @NEWREGISTRANTREGISTRATION table
(
REGISTRANTREGISTRATIONID uniqueidentifier,
WAIVEREGISTRATIONFEE bit,
SEQUENCE int IDENTITY(0,1),
QUANTITY int
);
--REGISTRANTS with Waived registration fee
if exists (select 1 from @MAPPEDREGISTRANTTABLE where WAIVEREGISTRATIONFEE = 1)
begin
insert into @NEWREGISTRANTREGISTRATION
(
REGISTRANTREGISTRATIONID,
WAIVEREGISTRATIONFEE,
QUANTITY
)
select
@NEWREGISTRANTREGISTRATIONID_WAIVEDFEE,
1,
count(1)/@EVENTPRICEREGISTRATIONCOUNT
from
@MAPPEDREGISTRANTTABLE MAPPEDREGISTRANT
where
MAPPEDREGISTRANT.WAIVEREGISTRATIONFEE = 1;
end
--REGISTRANTS with Not Waived registration fee
if exists (select 1 from @MAPPEDREGISTRANTTABLE where WAIVEREGISTRATIONFEE = 0)
begin
insert into @NEWREGISTRANTREGISTRATION
(
REGISTRANTREGISTRATIONID,
WAIVEREGISTRATIONFEE,
QUANTITY
)
select
@NEWREGISTRANTREGISTRATIONID_NOTWAIVEDFEE,
0,
count(1)/@EVENTPRICEREGISTRATIONCOUNT
from
@MAPPEDREGISTRANTTABLE MAPPEDREGISTRANT
where
MAPPEDREGISTRANT.WAIVEREGISTRATIONFEE = 0;
end
declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier;
declare @ORGANIZATIONAMOUNT money;
declare @EVENTBASECURRENCYID uniqueidentifier = (select top 1 BASECURRENCYID from dbo.EVENT where ID = @EVENTID);
if @EVENTBASECURRENCYID = @ORGANIZATIONCURRENCYID
set @ORGANIZATIONAMOUNT = @EVENTPRICEAMOUNT
else
begin
set @ORGANIZATIONEXCHANGERATEID = dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(@EVENTBASECURRENCYID, @ORGANIZATIONCURRENCYID, @DATEPURCHASED, 0, null)
set @ORGANIZATIONAMOUNT = dbo.UFN_CURRENCY_CONVERT(@EVENTPRICEAMOUNT, @ORGANIZATIONEXCHANGERATEID)
end
insert into dbo.REGISTRANTREGISTRATION
(
ID,
REGISTRANTID,
EVENTPRICEID,
QUANTITY,
AMOUNT,
RECEIPTAMOUNT,
DATEPURCHASED,
ORGANIZATIONAMOUNT,
ORGANIZATIONEXCHANGERATEID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
NEWREGISTRANTREGISTRATION.REGISTRANTREGISTRATIONID,
@ID,
@EVENTPRICEID,
NEWREGISTRANTREGISTRATION.QUANTITY,
case when NEWREGISTRANTREGISTRATION.WAIVEREGISTRATIONFEE = 1 then 0 else (@EVENTPRICEAMOUNT * NEWREGISTRANTREGISTRATION.QUANTITY) end,
case
when NEWREGISTRANTREGISTRATION.WAIVEREGISTRATIONFEE = 1
then 0
when @WAIVEBENEFITS = 0
then @EVENTPRICERECEIPTAMOUNT * NEWREGISTRANTREGISTRATION.QUANTITY
else
(@EVENTPRICEAMOUNT - @EVENTCOST)
end,
@DATEPURCHASED,
case when NEWREGISTRANTREGISTRATION.WAIVEREGISTRATIONFEE = 1 then 0 else (@ORGANIZATIONAMOUNT * NEWREGISTRANTREGISTRATION.QUANTITY) end,
@ORGANIZATIONEXCHANGERATEID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
@NEWREGISTRANTREGISTRATION NEWREGISTRANTREGISTRATION;
--Waived fee
update @MAPPEDREGISTRANTTABLE
set REGISTRANTREGISTRATIONID = @NEWREGISTRANTREGISTRATIONID_WAIVEDFEE
from
@MAPPEDREGISTRANTTABLE MAPPEDREGISTRANT
where MAPPEDREGISTRANT.WAIVEREGISTRATIONFEE = 1;
--Not Waived fee
update @MAPPEDREGISTRANTTABLE
set REGISTRANTREGISTRATIONID = @NEWREGISTRANTREGISTRATIONID_NOTWAIVEDFEE
from
@MAPPEDREGISTRANTTABLE MAPPEDREGISTRANT
where MAPPEDREGISTRANT.WAIVEREGISTRATIONFEE = 0;
--Map registrant/guests to the registration
insert into dbo.REGISTRANTREGISTRATIONMAP
(
ID,
REGISTRANTREGISTRATIONID,
REGISTRANTID,
REGISTRANTPACKAGEID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
newid(),
[MAPPEDREGISTRANT].REGISTRANTREGISTRATIONID,
[MAPPEDREGISTRANT].REGISTRANTID,
null,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
@MAPPEDREGISTRANTTABLE [MAPPEDREGISTRANT];
--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.REGISTRANTBENEFIT
(
REGISTRANTID,
BENEFITID,
UNITVALUE,
QUANTITY,
TOTALVALUE,
DETAILS,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
[MAPPEDREGISTRANT].REGISTRANTID,
EVENTPRICEBENEFIT.BENEFITID,
EVENTPRICEBENEFIT.UNITVALUE,
EVENTPRICEBENEFIT.QUANTITY,
(EVENTPRICEBENEFIT.UNITVALUE * EVENTPRICEBENEFIT.QUANTITY),
EVENTPRICEBENEFIT.DETAILS,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
dbo.EVENTPRICE
inner join dbo.EVENTPRICEBENEFIT on EVENTPRICE.ID = EVENTPRICEBENEFIT.EVENTPRICEID
cross join @MAPPEDREGISTRANTTABLE [MAPPEDREGISTRANT]
where
EVENTPRICE.ID = @EVENTPRICEID
and 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,
(EVENTPRICEBENEFIT.UNITVALUE),
(@QUANTITY * EVENTPRICEBENEFIT.QUANTITY),
((EVENTPRICEBENEFIT.UNITVALUE) * (@QUANTITY * EVENTPRICEBENEFIT.QUANTITY)),
EVENTPRICEBENEFIT.DETAILS,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
dbo.EVENTPRICE
inner join dbo.EVENTPRICEBENEFIT on EVENTPRICE.ID = EVENTPRICEBENEFIT.EVENTPRICEID
where
EVENTPRICE.ID = @EVENTPRICEID
and EVENTPRICE.REGISTRATIONCOUNT > 1;
--Add designation to registrant
declare @DESIGNATIONSONFEES bit
declare @DEFAULTDESIGNATIONID uniqueidentifier
select @DESIGNATIONSONFEES = DESIGNATIONSONFEES from dbo.EVENT where EVENT.ID=@EVENTID
select @DEFAULTDESIGNATIONID = DESIGNATIONID
from dbo.EVENTDESIGNATION
WHERE EVENTID = @EVENTID AND [DEFAULT] = 1
if @DESIGNATIONID is null
begin
set @DESIGNATIONID = @DEFAULTDESIGNATIONID
end
if (@DESIGNATIONSONFEES = 1) and (@DESIGNATIONID is not null)
begin
declare @UPDATEAMOUNT money
select @UPDATEAMOUNT = coalesce((select sum(REGISTRANTREGISTRATION.RECEIPTAMOUNT)
from dbo.REGISTRANTREGISTRATION
where REGISTRANTREGISTRATION.REGISTRANTID = @ID),0)
--if not exists(select ID from dbo.REGISTRANTDESIGNATION
-- where REGISTRANTID = @ID and DESIGNATIONID = @DESIGNATIONID)
--begin
if exists(select ID from dbo.REGISTRANTDESIGNATION
where REGISTRANTID = @ID)
begin
--registrant has designations
declare @count int
select @count = count(ID) from dbo.REGISTRANTDESIGNATION
where REGISTRANTID = @ID
if (@count = 1) -- only one exists - different or same
update dbo.REGISTRANTDESIGNATION
set DESIGNATIONID = @DESIGNATIONID, --even if same...update
AMOUNT = @UPDATEAMOUNT,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where REGISTRANTID = @ID
else -- more than one means, update only one but check the amounts
begin
declare @TOPID uniqueidentifier
if exists(select ID from dbo.REGISTRANTDESIGNATION
where REGISTRANTID = @ID and DESIGNATIONID = @DESIGNATIONID)
set @TOPID = (select ID from dbo.REGISTRANTDESIGNATION
where REGISTRANTID = @ID and DESIGNATIONID = @DESIGNATIONID)
else
set @TOPID = (select TOP (1) ID from dbo.REGISTRANTDESIGNATION
where REGISTRANTID = @ID order by dateadded)
if (@UPDATEAMOUNT <> coalesce((select SUM(AMOUNT)
from dbo.REGISTRANTDESIGNATION
where REGISTRANTID = @ID),0))
-- total amount has changed, so update accordingly
begin
declare @REMAMOUNT money
select @REMAMOUNT = coalesce((select SUM(AMOUNT)
from dbo.REGISTRANTDESIGNATION
where REGISTRANTID = @ID and ID <> @TOPID) ,0)
set @UPDATEAMOUNT = @UPDATEAMOUNT - @REMAMOUNT
end
update dbo.REGISTRANTDESIGNATION
set DESIGNATIONID = @DESIGNATIONID,
AMOUNT = @UPDATEAMOUNT,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where ID = @TOPID
end
end
else
--registrant has no designations - insert
insert into dbo.REGISTRANTDESIGNATION (
ID,
REGISTRANTID,
DESIGNATIONID,
AMOUNT,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values(
newid(),
@ID,
@DESIGNATIONID,
@UPDATEAMOUNT,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE)
--end
end
return 0;