USP_DATAFORMTEMPLATE_ADD_SALESORDERITEMGENERIC_EVENTREGISTRATION_ONLINE
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | INOUT | |
| @SALESORDERID | uniqueidentifier | IN | |
| @TYPECODE | tinyint | IN | |
| @DESCRIPTION | nvarchar(255) | IN | |
| @QUANTITY | decimal(18, 0) | IN | |
| @AMOUNT | money | IN | |
| @DATA | xml | IN | |
| @OPTIONS | xml | IN | |
| @CALLBACKURL | nvarchar(255) | IN | |
| @SYSTEMTYPENAME | nvarchar(255) | IN | |
| @ASSEMBLYNAME | nvarchar(255) | IN | |
| @ATTRIBUTES | xml | IN | |
| @CATEGORYNAME | nvarchar(255) | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @ACKNOWLEDGEMENT | nvarchar(max) | IN | |
| @EVENTID | uniqueidentifier | IN | |
| @REGISTRANTMAPPINGS | xml | IN |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_SALESORDERITEMGENERIC_EVENTREGISTRATION_ONLINE
(
@ID uniqueidentifier = null output,
@SALESORDERID uniqueidentifier,
@TYPECODE tinyint,
@DESCRIPTION nvarchar(255) = null,
@QUANTITY decimal = 1,
@AMOUNT money,
@DATA xml = null,
@OPTIONS xml = null,
@CALLBACKURL nvarchar(255) = null,
@SYSTEMTYPENAME nvarchar(255) = null,
@ASSEMBLYNAME nvarchar(255) = null,
@ATTRIBUTES xml = null,
@CATEGORYNAME nvarchar(255) = null,
@CHANGEAGENTID uniqueidentifier = null,
@ACKNOWLEDGEMENT nvarchar(max) = null,
@EVENTID uniqueidentifier = null,
@REGISTRANTMAPPINGS xml = null
)
as
set nocount on;
if @ID is null
set @ID = newid()
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output
declare @CURRENTDATE datetime
set @CURRENTDATE = getdate()
begin try
declare @STARTDATE datetime = null;
declare @ENDDATE datetime = null;
declare @ENDTIME dbo.UDT_HOURMINUTE = null;
select
@STARTDATE = dbo.UFN_DATE_ADDHOURMINUTE([EVENT].[STARTDATE], [EVENT].[STARTTIME]),
@ENDDATE = dbo.UFN_DATE_ADDHOURMINUTE([EVENT].[ENDDATE], [EVENT].[ENDTIME]),
@ENDTIME = isnull([EVENT].[ENDTIME],'')
from dbo.[EVENT]
where [ID] = @EVENTID
declare @EXPIRATIONDATE datetime = null
declare @EXPIRATIONDATEWITHOFFSET datetimeoffset = null
declare @ISEXPIRED bit = 0
--Calculate expiration from end of start date
if @STARTDATE > @ENDDATE
begin
set @ENDDATE = @STARTDATE
set @EXPIRATIONDATE = dbo.UFN_DATE_GETLATESTTIME(@STARTDATE)
end
else if @ENDTIME = ''
set @EXPIRATIONDATE = dbo.UFN_DATE_GETLATESTTIME(@ENDDATE)
else
set @EXPIRATIONDATE = @ENDDATE
declare @CURRENTDATETIMEOFFSET datetimeoffset = dbo.UFN_TIMEZONEENTRY_GETSYSTEMDEFAULTDATEWITHTIMEOFFSET(sysutcdatetime(), 1);
set @EXPIRATIONDATEWITHOFFSET = dbo.UFN_TIMEZONEENTRY_GETSYSTEMDEFAULTDATEWITHTIMEOFFSET(@EXPIRATIONDATE, 0);
set @ISEXPIRED = case when @EXPIRATIONDATEWITHOFFSET < @CURRENTDATETIMEOFFSET then 1 else 0 end
if @ISEXPIRED = 1
raiserror('BBERR_SALESORDERITEMONLINE_EVENTREGISTRATION_EVENTEXPIRED', 13, 1);
declare @REGISTRANTS_TABLE table (
[REGISTERLATER] bit,
[CONSTITUENTID] uniqueidentifier,
[FIRSTNAME] nvarchar(50),
[KEYNAME] nvarchar(100),
[PHONE] nvarchar(100),
[EMAIL] dbo.UDT_EMAILADDRESS,
[TITLECODEID] uniqueidentifier,
[ADDRESSBLOCK] nvarchar(150),
[CITY] nvarchar(50),
[STATEID] uniqueidentifier,
[POSTCODE] nvarchar(12),
[COUNTRYID] uniqueidentifier
);
insert into @REGISTRANTS_TABLE
select
isnull(T.registrants.value('(REGISTERLATER)[1]','bit'),0) as 'REGISTERLATER',
T.registrants.value('(GUESTCONSTITUENTID)[1]','uniqueidentifier') as 'CONSTITUENTID',
T.registrants.value('(FIRSTNAME)[1]','nvarchar(50)') as 'FIRSTNAME',
T.registrants.value('(KEYNAME)[1]','nvarchar(100)') as 'KEYNAME',
T.registrants.value('(PHONE)[1]','nvarchar(100)') as 'PHONE',
isnull(T.registrants.value('(EMAIL)[1]','dbo.UDT_EMAILADDRESS'),'') as 'EMAIL',
dbo.UFN_TITLECODE_GETID(T.registrants.value('(TITLE)[1]','nvarchar(100)')) as 'TITLECODEID',
T.registrants.value('(ADDRESSBLOCK)[1]','nvarchar(150)') as 'ADDRESSBLOCK',
T.registrants.value('(CITY)[1]','nvarchar(50)') as 'CITY',
T.registrants.value('(STATEID)[1]','uniqueidentifier') as 'STATEID',
T.registrants.value('(POSTCODE)[1]','nvarchar(12)') as 'POSTCODE',
T.registrants.value('(COUNTRYID)[1]','uniqueidentifier') as 'COUNTRYID'
from @REGISTRANTMAPPINGS.nodes('/REGISTRANTMAPPINGS/ITEM') T(registrants)
--For registrations, we need to check if the registrant quantity exceeds the event's capacity
declare @CAPACITY integer = 0;
declare @CAPACITYRESERVED integer = 0;
declare @AVAILABLECAPACITY integer = 0;
select
@CAPACITY = [EVENT].[CAPACITY],
@CAPACITYRESERVED = [CAPACITY].[RESERVED]
from dbo.[EVENT]
outer apply (
select count(REGISTRANT.ID) as [RESERVED]
from dbo.[REGISTRANT]
where
[REGISTRANT].[EVENTID] = [EVENT].[ID] and
[REGISTRANT].[ISCANCELLED] = 0 and
[REGISTRANT].[WILLNOTATTEND] = 0
) [CAPACITY]
where [EVENT].[ID] = @EVENTID
set @AVAILABLECAPACITY = @CAPACITY - @CAPACITYRESERVED
if @AVAILABLECAPACITY < 0
set @AVAILABLECAPACITY = 0;
if (select count(*) from @REGISTRANTS_TABLE) > @AVAILABLECAPACITY
raiserror('BBERR_SALESORDERITEMONLINE_EVENTREGISTRATION_OVERCAPACITY', 13, 1);
--We also need to check if any of the regsitrants already has a registration for this event
--Automatch prep (don't want to do this work each time through the registrant cursor)
declare @ADDRESSSUBSTITUTIONS dbo.UDT_STRINGPAIR_100
insert into @ADDRESSSUBSTITUTIONS
select
[DESIGNATION],
[ABBREVIATION]
from dbo.UFN_ADDRESS_ADDRESSBLOCK_STANDARDUSABBREVIATIONS_LOWERCASE()
declare @CONSTITUENTMATCHES table (
[ID] uniqueidentifier
)
--End automatch prep
--Registrant cursor prep
--Matching/Creating constituent records for registrants without them
--Updating constituent records for title, firstname
declare
@REGISTRANTREGISTERLATER bit,
@REGISTRANTCONSTITUENTID uniqueidentifier,
@REGISTRANTFIRSTNAME nvarchar(50),
@REGISTRANTKEYNAME nvarchar(100),
@REGISTRANTPHONE nvarchar(100),
@REGISTRANTTITLECODEID uniqueidentifier,
@REGISTRANTEMAIL dbo.UDT_EMAILADDRESS,
@REGISTRANTCOUNTRYID uniqueidentifier,
@REGISTRANTSTATEID uniqueidentifier,
@REGISTRANTADDRESSBLOCK nvarchar(150),
@REGISTRANTCITY nvarchar(50),
@REGISTRANTPOSTCODE nvarchar(12)
declare REGISTRANT_CURSOR cursor local fast_forward for
select
[REGISTERLATER],
case when [CONSTITUENTID] = '00000000-0000-0000-0000-000000000000' then null else [CONSTITUENTID] end,
isnull([FIRSTNAME],''),
isnull([KEYNAME],''),
[TITLECODEID],
isnull([PHONE],''),
isnull([EMAIL],''),
[COUNTRYID],
[STATEID],
isnull([ADDRESSBLOCK],''),
isnull([CITY],''),
isnull([POSTCODE],'')
from @REGISTRANTS_TABLE;
--End registrant cursor prep
--Starting registrant matching/creating work
open REGISTRANT_CURSOR
fetch next from REGISTRANT_CURSOR into
@REGISTRANTREGISTERLATER,
@REGISTRANTCONSTITUENTID,
@REGISTRANTFIRSTNAME,
@REGISTRANTKEYNAME,
@REGISTRANTTITLECODEID,
@REGISTRANTPHONE,
@REGISTRANTEMAIL,
@REGISTRANTCOUNTRYID,
@REGISTRANTSTATEID,
@REGISTRANTADDRESSBLOCK,
@REGISTRANTCITY,
@REGISTRANTPOSTCODE
while @@FETCH_STATUS = 0
begin
if @REGISTRANTCOUNTRYID= '00000000-0000-0000-0000-000000000000'
set @REGISTRANTCOUNTRYID = null
if @REGISTRANTSTATEID = '00000000-0000-0000-0000-000000000000'
set @REGISTRANTSTATEID = null
set @REGISTRANTADDRESSBLOCK = isnull(@REGISTRANTADDRESSBLOCK,'')
set @REGISTRANTCITY = isnull(@REGISTRANTCITY, '')
set @REGISTRANTPOSTCODE = isnull(@REGISTRANTPOSTCODE, '')
--Find a constituent record match if we don't have a constituent record
if @REGISTRANTCONSTITUENTID is null or @REGISTRANTREGISTERLATER = 1
begin
delete @CONSTITUENTMATCHES
insert into @CONSTITUENTMATCHES
select [CONSTITUENT].[ID]
from dbo.UFN_WEBFORMS_CONSTITUENT_EXACTAUTOMATCH (
50,
@REGISTRANTKEYNAME,
@REGISTRANTFIRSTNAME,
@REGISTRANTTITLECODEID,
@REGISTRANTADDRESSBLOCK,
@REGISTRANTCITY,
@REGISTRANTCOUNTRYID,
@REGISTRANTSTATEID,
@REGISTRANTPOSTCODE,
@REGISTRANTPHONE,
@REGISTRANTEMAIL,
@ADDRESSSUBSTITUTIONS
) [CONSTITUENT]
--Check and error if any of the matches are already registered for the event
if exists(
select 1
from dbo.[REGISTRANT]
inner join @CONSTITUENTMATCHES [MATCHES]
on [REGISTRANT].[CONSTITUENTID] = [MATCHES].[ID]
where
[REGISTRANT].[EVENTID] = @EVENTID and
--Allow hosts that haven't been marked as attending to match
(
[REGISTRANT].[WILLNOTATTEND] = 0 or
exists(select top 1 [ID] from dbo.REGISTRANTREGISTRATIONMAP where [REGISTRANTID] = [REGISTRANT].[ID])
)
)
begin
raiserror('BBERR_SALESORDERITEMONLINE_EVENTREGISTRATION_REGISTRANTMATCH', 13, 1);
end
end
fetch next from REGISTRANT_CURSOR into
@REGISTRANTREGISTERLATER,
@REGISTRANTCONSTITUENTID,
@REGISTRANTFIRSTNAME,
@REGISTRANTKEYNAME,
@REGISTRANTTITLECODEID,
@REGISTRANTPHONE,
@REGISTRANTEMAIL,
@REGISTRANTCOUNTRYID,
@REGISTRANTSTATEID,
@REGISTRANTADDRESSBLOCK,
@REGISTRANTCITY,
@REGISTRANTPOSTCODE
end
close REGISTRANT_CURSOR
deallocate REGISTRANT_CURSOR
-- handle inserting the data
merge dbo.[SALESORDERITEM] as [TARGET]
using (select @ID as ID) as [SOURCE]
on (TARGET.ID = SOURCE.ID)
when matched then
update
set
DESCRIPTION = @DESCRIPTION,
PRICE = @AMOUNT,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE,
DATA = @DATA,
OPTIONS = @OPTIONS,
CALLBACKURL = @CALLBACKURL,
SYSTEMTYPENAME = @SYSTEMTYPENAME,
ASSEMBLYNAME = @ASSEMBLYNAME,
ATTRIBUTES = @ATTRIBUTES,
CATEGORYNAME = @CATEGORYNAME,
ACKNOWLEDGEMENT = @ACKNOWLEDGEMENT,
SALESORDERID = @SALESORDERID,
TYPECODE = @TYPECODE
when not matched then
insert (
ID,
SALESORDERID,
TYPECODE,
DESCRIPTION,
QUANTITY,
PRICE,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED,
DATA,
OPTIONS,
CALLBACKURL,
SYSTEMTYPENAME,
ASSEMBLYNAME,
ATTRIBUTES,
CATEGORYNAME,
ACKNOWLEDGEMENT
)
values (
@ID,
@SALESORDERID,
@TYPECODE,
@DESCRIPTION,
@QUANTITY,
@AMOUNT,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE,
@DATA,
@OPTIONS,
@CALLBACKURL,
@SYSTEMTYPENAME,
@ASSEMBLYNAME,
@ATTRIBUTES,
@CATEGORYNAME,
@ACKNOWLEDGEMENT
);
end try
begin catch
exec dbo.USP_RAISE_ERROR
return 1
end catch
return 0