USP_DATAFORMTEMPLATE_ADD_SALESORDERITEMGENERIC_PREREGISTEREDEVENT_ONLINE
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | INOUT | |
| @SALESORDERID | uniqueidentifier | 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 | |
| @TIMEOUTMINUTES | int | IN | |
| @EVENTID | uniqueidentifier | IN | |
| @PRICETYPES | xml | IN | |
| @REGISTRANTMAPPINGS | xml | IN | |
| @ITEMVALIDATIONONLY | bit | IN |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_SALESORDERITEMGENERIC_PREREGISTEREDEVENT_ONLINE
(
@ID uniqueidentifier = null output,
@SALESORDERID uniqueidentifier,
@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,
@TIMEOUTMINUTES integer = null,
@EVENTID uniqueidentifier = null,
@PRICETYPES xml = null,
@REGISTRANTMAPPINGS xml = null,
@ITEMVALIDATIONONLY bit = 0
)
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 @SALESMETHODID uniqueidentifier = (select top 1 [ID] from dbo.[SALESMETHOD] where [TYPECODE] = 2)
declare @CURRENTDATETIMEOFFSET datetimeoffset = dbo.UFN_TIMEZONEENTRY_GETSYSTEMDEFAULTDATEWITHTIMEOFFSET(sysutcdatetime(), 1);
declare @ISEXPIRED bit = 0
--Calculate expiration from end of start date
select
@ISEXPIRED = case when @CURRENTDATETIMEOFFSET >
--Event expiration date with offset
(
case [ONSALEEND].TYPECODE
when 0 then [EVENT].[STARTDATETIMEWITHOFFSET]
when 1 then dateadd(mi, -1 * [ONSALEEND].INTERVAL, [EVENT].[STARTDATETIMEWITHOFFSET])
when 2 then dateadd(hh, -1 * [ONSALEEND].INTERVAL, [EVENT].[STARTDATETIMEWITHOFFSET])
when 3 then dateadd(mi, [ONSALEEND].INTERVAL, [EVENT].[STARTDATETIMEWITHOFFSET])
end
)
then 1
else 0
end
from dbo.[EVENT]
outer apply(
select top 1
ONSALEENDTYPECODE,
ONSALEENDINTERVAL
from dbo.PROGRAMSALESMETHOD
where
[PROGRAMSALESMETHOD].[PROGRAMID] = [EVENT].[PROGRAMID] and
[PROGRAMSALESMETHOD].[SALESMETHODID] = @SALESMETHODID
) [PROGRAMSALESMETHOD]
cross apply (
select
isnull([PROGRAMSALESMETHOD].ONSALEENDTYPECODE, 0) as [TYPECODE],
ONSALEENDINTERVAL as [INTERVAL]
) as [ONSALEEND]
where [EVENT].[ID] = @ID
if @ISEXPIRED = 1
raiserror('BBERR_SALESORDERITEMONLINE_PREREGISTEREDITCKET_EVENTEXPIRED', 13, 1);
--End expired check
--Check if the item can be delivered
declare @HASUNRESTRICTEDDELIVERYMETHOD bit = 0
declare @EARLIESTVALIDEVENTDATETIMEWITHOFFSET datetimeoffset = null
declare @DELIVERYMETHODEXISTS bit = 0
exec dbo.USP_DELIVERYMETHOD_INFO_BYSALESMETHODID
@SALESMETHODID,
@DELIVERYMETHODEXISTS output,
@HASUNRESTRICTEDDELIVERYMETHOD output,
@EARLIESTVALIDEVENTDATETIMEWITHOFFSET output
if @DELIVERYMETHODEXISTS = 1
begin
if @HASUNRESTRICTEDDELIVERYMETHOD = 0
begin
if @EARLIESTVALIDEVENTDATETIMEWITHOFFSET > (select top 1 [STARTDATETIMEWITHOFFSET] from dbo.[EVENT] where [ID] = @EVENTID)
raiserror('BBERR_SALESORDERITEMONLINE_PREREGISTEREDITCKET_HASNODELIVERYMETHOD', 13, 1)
end
end
else
raiserror('BBERR_SALESORDERITEMONLINE_PREREGISTEREDITCKET_HASNODELIVERYMETHOD', 13, 1)
--end deliverability check
declare @REGISTRANTS_TABLE table (
[REGISTERLATER] bit,
[PRICETYPECODEID] uniqueidentifier,
[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('(PRICETYPECODEID)[1]','uniqueidentifier') as 'PRICETYPECODEID',
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 @TICKETQUANTITY integer = (select count(1) from @REGISTRANTS_TABLE)
if dbo.UFN_SALESORDERTICKET_VALIDQUANTITY(@EVENTID, @SALESORDERID, @TICKETQUANTITY) = 0
raiserror('BBERR_SALESORDERITEMONLINE_PREREGISTEREDITCKET_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 @CONSTITUENTMATCHES table (
[ID] uniqueidentifier
)
declare @AUTOMATCHTHRESHOLD decimal(20,4) = dbo.UFN_CONSTITUENTDUPLICATESEARCHSETTINGS_GETAUTOMATCHTHRESHOLD_BYID('7BDE63AA-73B8-4A31-BE9F-82D92B67E2F4')
if @AUTOMATCHTHRESHOLD is null
set @AUTOMATCHTHRESHOLD = 95
--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
CONSTITUENTID
from dbo.UFN_FINDCONSTITUENTMATCHES_COMPOSITETHRESHOLD_2 (
@REGISTRANTTITLECODEID,
@REGISTRANTFIRSTNAME,
'', -- middle name
@REGISTRANTKEYNAME,
null, --suffix
@REGISTRANTADDRESSBLOCK,--no city or state in this algorithm
@REGISTRANTPOSTCODE,
@REGISTRANTCOUNTRYID,
0, --is organization
0, --is group
null, --lookup ID
null, --alternate lookup IDs
@REGISTRANTEMAIL,
@REGISTRANTPHONE,
@AUTOMATCHTHRESHOLD, --overall match threshold
@AUTOMATCHTHRESHOLD
)
--Check and error if any of the matches are already registered for the event
if exists(
select 1
from dbo.[SALESORDERITEMTICKET]
inner join dbo.[SALESORDERITEMTICKETREGISTRANT]
on [SALESORDERITEMTICKET].[ID] = [SALESORDERITEMTICKETREGISTRANT].[SALESORDERITEMTICKETID]
inner join dbo.[REGISTRANT]
on [SALESORDERITEMTICKETREGISTRANT].[REGISTRANTID] = [REGISTRANT].[ID]
inner join @CONSTITUENTMATCHES [MATCHES]
on [REGISTRANT].[CONSTITUENTID] = [MATCHES].[ID]
where
[SALESORDERITEMTICKET].[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_PREREGISTEREDITCKET_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
--Add items work
if @ITEMVALIDATIONONLY = 0
begin
declare @CURRENTEVENTSALESORDERITEMS table (
ID uniqueidentifier,
PRICETYPECODEID uniqueidentifier,
QUANTITY integer
)
insert @CURRENTEVENTSALESORDERITEMS
select
SALESORDERITEM.ID,
SALESORDERITEMTICKET.PRICETYPECODEID,
0
from dbo.SALESORDERITEM
inner join dbo.[SALESORDERITEMTICKET]
on [SALESORDERITEM].[ID] = [SALESORDERITEMTICKET].[ID]
where
[SALESORDERITEM].[SALESORDERID] = @SALESORDERID and
[SALESORDERITEMTICKET].[EVENTID] = @EVENTID
declare @PROGRAMID uniqueidentifier
declare @PROGRAMNAME nvarchar(100)
declare @PROGRAMCATEGORYNAME nvarchar(100)
declare @EVENTNAME nvarchar(100)
select
@PROGRAMID = [EVENT].[PROGRAMID],
@PROGRAMNAME = isnull([PROGRAM].[NAME], ''),
@EVENTNAME = [EVENT].[NAME],
@PROGRAMCATEGORYNAME = isnull([PROGRAMCATEGORYCODE].[DESCRIPTION], '')
from dbo.[EVENT]
inner join dbo.[PROGRAM]
on [EVENT].[PROGRAMID] = [PROGRAM].[ID]
left join dbo.[PROGRAMCATEGORYCODE]
on [PROGRAM].[PROGRAMCATEGORYCODEID] = [PROGRAMCATEGORYCODE].[ID]
where [EVENT].[ID] = @EVENTID
declare PRICETYPES_CURSOR cursor local fast_forward for
select
T.pricetypes.query('./EventRegistrationTransaction'),
T.pricetypes.value('(PRICE)[1]','money') as 'PRICE',
T.pricetypes.value('(PRICETYPECODEID)[1]','uniqueidentifier') as 'PRICETYPECODEID'
from @PRICETYPES.nodes('PriceTypes/Items/PriceType') T(pricetypes)
declare @DATA xml
declare @PRICE money
declare @PRICETYPECODEID uniqueidentifier
declare @QUANTITY integer
open PRICETYPES_CURSOR
fetch next from PRICETYPES_CURSOR into
@DATA,
@PRICE,
@PRICETYPECODEID
while @@FETCH_STATUS = 0
begin
set @ID = null
select @ID = [ID]
from @CURRENTEVENTSALESORDERITEMS
where [PRICETYPECODEID] = @PRICETYPECODEID
if @ID is null
set @ID = newid()
set @QUANTITY = isnull((select count(1) from @REGISTRANTS_TABLE where [PRICETYPECODEID] = @PRICETYPECODEID), 0)
update @CURRENTEVENTSALESORDERITEMS set [QUANTITY] = @QUANTITY where [ID] = @ID
if @QUANTITY > 0
begin
declare @PRICETYPENAME nvarchar(100) = (select [DESCRIPTION] from dbo.[PRICETYPECODE] where [ID] = @PRICETYPECODEID)
-- 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 = @EVENTNAME + ' - ' + @PRICETYPENAME,
TYPECODE = 0,
PRICE = @PRICE,
QUANTITY = @QUANTITY,
DATA = @DATA,
OPTIONS = @OPTIONS,
CALLBACKURL = @CALLBACKURL,
SYSTEMTYPENAME = @SYSTEMTYPENAME,
ASSEMBLYNAME = @ASSEMBLYNAME,
ATTRIBUTES = @ATTRIBUTES,
CATEGORYNAME = @CATEGORYNAME,
ACKNOWLEDGEMENT = @ACKNOWLEDGEMENT,
SALESORDERID = @SALESORDERID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
when not matched then
insert (
ID,
SALESORDERID,
TYPECODE,
DESCRIPTION,
QUANTITY,
PRICE,
DATA,
OPTIONS,
CALLBACKURL,
SYSTEMTYPENAME,
ASSEMBLYNAME,
ATTRIBUTES,
CATEGORYNAME,
ACKNOWLEDGEMENT,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values (
@ID,
@SALESORDERID,
0,
@EVENTNAME + ' - ' + @PRICETYPENAME,
@QUANTITY,
@PRICE,
@DATA,
@OPTIONS,
@CALLBACKURL,
@SYSTEMTYPENAME,
@ASSEMBLYNAME,
@ATTRIBUTES,
@CATEGORYNAME,
@ACKNOWLEDGEMENT,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
merge dbo.[SALESORDERITEMTICKET] as [TARGET]
using (select @ID as ID) as [SOURCE]
on (TARGET.ID = SOURCE.ID)
when matched then
update
set
PROGRAMID = @PROGRAMID,
EVENTID = @EVENTID,
PRICETYPECODEID = @PRICETYPECODEID,
PRICE = @PRICE,
PROGRAMNAME = @PROGRAMNAME,
PROGRAMCATEGORYNAME = @PROGRAMCATEGORYNAME,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
when not matched then
insert (
ID,
PROGRAMID,
EVENTID,
PRICETYPECODEID,
PRICE,
PROGRAMNAME,
PROGRAMCATEGORYNAME,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values (
@ID,
@PROGRAMID,
@EVENTID,
@PRICETYPECODEID,
@PRICE,
@PROGRAMNAME,
@PROGRAMCATEGORYNAME,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
if @TIMEOUTMINUTES > 0
begin
merge dbo.[SALESORDERRESERVEDITEM] as [TARGET]
using (select @ID as ID) as [SOURCE]
on (TARGET.ID = SOURCE.ID)
when matched then
update
set
SECONDSUNTILEXPIRATION = @TIMEOUTMINUTES * 60,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
when not matched then
insert (
ID,
SALESORDERID,
SECONDSUNTILEXPIRATION,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values (
@ID,
@SALESORDERID,
@TIMEOUTMINUTES * 60,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end
--End timeout check
end
--End quantity check
fetch next from PRICETYPES_CURSOR into
@DATA,
@PRICE,
@PRICETYPECODEID
end
--End cursor
close PRICETYPES_CURSOR
deallocate PRICETYPES_CURSOR
-- Remove ticket items for price types no longer on the order
declare @contextCache varbinary(128);
set @contextCache = CONTEXT_INFO();
set CONTEXT_INFO @CHANGEAGENTID;
-- delete discount details
delete from dbo.SALESORDERITEMORDERDISCOUNTDETAIL
where SALESORDERITEMORDERDISCOUNTDETAIL.SALESORDERITEMID in
(
select ID
from dbo.SALESORDERITEM
where SALESORDERITEM.SALESORDERID = @SALESORDERID
)
-- delete any fees
delete from dbo.[SALESORDERITEM]
where
[SALESORDERITEM].[SALESORDERID] = @SALESORDERID and
exists (
select 1
from dbo.[SALESORDERITEMFEE]
where [SALESORDERITEMFEE].[ID] = [SALESORDERITEM].[ID]
)
delete dbo.[SALESORDERITEM]
where exists(
select 1
from @CURRENTEVENTSALESORDERITEMS [ITEMS]
where
[ITEMS].[QUANTITY] = 0 and
[ITEMS].[ID] = [SALESORDERITEM].[ID]
)
if not @contextCache is null
set CONTEXT_INFO @contextCache;
exec dbo.USP_SALESORDER_CALCULATEFEES @SALESORDERID, @CHANGEAGENTID;
exec dbo.USP_SALESORDER_APPLYITEMDISCOUNTS @SALESORDERID, @CHANGEAGENTID;
exec dbo.USP_SALESORDER_APPLYORDERDISCOUNTS @SALESORDERID, @CHANGEAGENTID;
exec dbo.USP_SALESORDER_CALCULATETAXES @SALESORDERID, @CHANGEAGENTID;
end
--End validation only check
end try
begin catch
exec dbo.USP_RAISE_ERROR
return 1
end catch
return 0