USP_SALESORDER_PREREGISTEREDPROGRAMEVENTTICKET_ADD
Adds preregistered program event tickets.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @SALESORDERID | uniqueidentifier | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @CURRENTAPPUSERID | uniqueidentifier | IN | |
| @EVENTID | uniqueidentifier | IN | |
| @CONSTITUENTID | uniqueidentifier | IN | |
| @REGISTRANTS | xml | IN | |
| @CURRENTDATE | datetime | IN | |
| @MARKREGISTRANTSATTENDED | bit | IN | |
| @ALLOWPASTEVENTS | bit | IN |
Definition
Copy
CREATE procedure dbo.USP_SALESORDER_PREREGISTEREDPROGRAMEVENTTICKET_ADD
(
@SALESORDERID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier = null,
@CURRENTAPPUSERID uniqueidentifier,
@EVENTID uniqueidentifier = null,
@CONSTITUENTID uniqueidentifier = null,
@REGISTRANTS xml = null,
@CURRENTDATE datetime = null,
@MARKREGISTRANTSATTENDED bit = 0,
@ALLOWPASTEVENTS bit = 0
)
as
set nocount on;
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
if @CURRENTDATE is null
set @CURRENTDATE = getdate();
declare @SALESMETHODTYPECODE tinyint;
select @SALESMETHODTYPECODE = SALESMETHODTYPECODE from dbo.SALESORDER where ID = @SALESORDERID;
-- Assign registrant IDs if they weren't passed in
set @REGISTRANTS = (
select
isnull(T.registrants.value('(ID)[1]', 'uniqueidentifier'), newid()) as [ID],
T.registrants.value('(PRICETYPECODEID)[1]', 'uniqueidentifier') as [PRICETYPECODEID],
T.registrants.value('(CONSTITUENTID)[1]', 'uniqueidentifier') as [CONSTITUENTID],
T.registrants.value('(NOTES)[1]', 'nvarchar(255)') as [NOTES],
T.registrants.query('./PREFERENCES/ITEM') as [PREFERENCES],
T.registrants.query('./REGISTRANTREGISTRATIONINFORMATION/ITEM') as [REGISTRANTREGISTRATIONINFORMATION]
from
@REGISTRANTS.nodes('/REGISTRANTS/ITEM') T(registrants)
for xml raw('ITEM'), type, elements, root('REGISTRANTS'), binary base64
);
declare @ORDERREGISTRANTS table
(
ID uniqueidentifier,
PRICETYPECODEID uniqueidentifier,
CONSTITUENTID uniqueidentifier,
NOTES nvarchar(255)
);
declare @ORDERREGISTRANTPREFERENCES table
(
ID uniqueidentifier,
REGISTRANTID uniqueidentifier,
EVENTPREFERENCEID uniqueidentifier
);
declare @ORDERREGISTRANTREGISTRATIONINFORMATION table
(
ID uniqueidentifier,
REGISTRANTID uniqueidentifier,
REGISTRATIONINFORMATIONID uniqueidentifier,
RESPONSETYPECODE tinyint,
PERSONDETAILTYPECODE tinyint,
TEXTVALUE nvarchar(max),
REGISTRATIONINFORMATIONOPTIONID uniqueidentifier
);
declare @AVAILABLEPRICETYPES table
(
PRICETYPECODEID uniqueidentifier,
DESCRIPTION nvarchar(100),
FACEPRICE money
);
insert into @ORDERREGISTRANTS
(
ID,
PRICETYPECODEID,
CONSTITUENTID,
NOTES
)
select
T.registrants.value('(ID)[1]', 'uniqueidentifier'),
T.registrants.value('(PRICETYPECODEID)[1]', 'uniqueidentifier'),
T.registrants.value('(CONSTITUENTID)[1]', 'uniqueidentifier'),
isnull(T.registrants.value('(NOTES)[1]', 'nvarchar(255)'), '')
from
@REGISTRANTS.nodes('/REGISTRANTS/ITEM') T(registrants);
insert into @ORDERREGISTRANTPREFERENCES
(
ID,
REGISTRANTID,
EVENTPREFERENCEID
)
select
isnull(T2.preferences.value('(ID)[1]', 'uniqueidentifier'), newid()),
T.registrants.value('(ID)[1]', 'uniqueidentifier'),
T2.preferences.value('(EVENTPREFERENCEID)[1]', 'uniqueidentifier')
from
@REGISTRANTS.nodes('/REGISTRANTS/ITEM') T(registrants)
cross apply
T.registrants.nodes('./PREFERENCES/ITEM') T2(preferences);
insert into @ORDERREGISTRANTREGISTRATIONINFORMATION
(
ID,
REGISTRANTID,
REGISTRATIONINFORMATIONID,
RESPONSETYPECODE,
PERSONDETAILTYPECODE,
TEXTVALUE,
REGISTRATIONINFORMATIONOPTIONID
)
select
newid(),
T.registrants.value('(ID)[1]', 'uniqueidentifier'),
T2.registrationinformation.value('(REGISTRATIONINFORMATIONID)[1]', 'uniqueidentifier'),
T2.registrationinformation.value('(RESPONSETYPECODE)[1]', 'tinyint'),
T2.registrationinformation.value('(PERSONDETAILTYPECODE)[1]', 'tinyint'),
isnull(T2.registrationinformation.value('(TEXTVALUE)[1]', 'nvarchar(max)'), ''),
cast(nullif(T2.registrationinformation.value('(REGISTRATIONINFORMATIONOPTIONID)[1]', 'nvarchar(36)'), '') as uniqueidentifier)
from
@REGISTRANTS.nodes('/REGISTRANTS/ITEM') T(registrants)
cross apply
T.registrants.nodes('./REGISTRANTREGISTRATIONINFORMATION/ITEM') T2(registrationinformation);
insert into @AVAILABLEPRICETYPES
(
PRICETYPECODEID,
DESCRIPTION,
FACEPRICE
)
select
PRICETYPECODE.ID,
PRICETYPECODE.DESCRIPTION,
PROGRAMEVENTPRICE.FACEPRICE
from
dbo.UFN_SALESMETHOD_ALLOWEDPRICETYPES(@SALESMETHODTYPECODE, 0) as PRICETYPECODE
inner join
dbo.UFN_PROGRAMEVENT_GETPRICES(@EVENTID, 0) as PROGRAMEVENTPRICE on PROGRAMEVENTPRICE.PRICETYPECODEID = PRICETYPECODE.ID;
begin try
if exists (
select top 1 1 from @ORDERREGISTRANTS
where PRICETYPECODEID not in (select PRICETYPECODEID from @AVAILABLEPRICETYPES)
)
raiserror('ERR_PRICETYPENOTFORSALE', 13, 1);
-- Add constituent to order
declare @ORDERCONSTITUENTID uniqueidentifier;
select @ORDERCONSTITUENTID = CONSTITUENTID from dbo.SALESORDER where ID = @SALESORDERID;
if @ORDERCONSTITUENTID is null
exec dbo.USP_SALESORDER_ADDPATRON
@SALESORDERID,
@CONSTITUENTID,
@CURRENTAPPUSERID,
@CHANGEAGENTID,
@CURRENTDATE;
-- Add tickets
declare @PRICETYPES xml;
set @PRICETYPES = (
select
PRICETYPES.DESCRIPTION as [PRICETYPE],
REGISTRANTS.PRICETYPECODEID,
PRICETYPES.FACEPRICE,
count(REGISTRANTS.PRICETYPECODEID) as [QUANTITY]
from
@ORDERREGISTRANTS as REGISTRANTS
inner join
@AVAILABLEPRICETYPES as PRICETYPES on REGISTRANTS.PRICETYPECODEID = PRICETYPES.PRICETYPECODEID
group by
PRICETYPES.DESCRIPTION,
REGISTRANTS.PRICETYPECODEID,
PRICETYPES.FACEPRICE
for xml raw('ITEM'), type, elements, root('PRICETYPES'), binary base64
);
exec dbo.USP_SALESORDERITEMTICKETS_ADD
@SALESORDERID = @SALESORDERID,
@CHANGEAGENTID = @CHANGEAGENTID,
@CURRENTDATE = @CURRENTDATE,
@EVENTID = @EVENTID,
@PRICETYPES = @PRICETYPES,
@ALLOWPASTEVENTS = @ALLOWPASTEVENTS;
-- See if the constituent is already registered for this event
declare @REGISTRANTID uniqueidentifier;
declare @GUESTOFREGISTRANTID uniqueidentifier;
declare @ORDERREGISTRANTID uniqueidentifier;
select
@REGISTRANTID = REGISTRANT.ID,
@GUESTOFREGISTRANTID = REGISTRANT.GUESTOFREGISTRANTID
from
dbo.REGISTRANT
where
REGISTRANT.EVENTID = @EVENTID
and REGISTRANT.CONSTITUENTID = @CONSTITUENTID
and REGISTRANT.ISCANCELLED = 0;
-- If the main constituent is a guest of someone else, get their host registrant
-- and make that person the host for the new tickets to be added
if @GUESTOFREGISTRANTID is not null
select
@REGISTRANTID = @GUESTOFREGISTRANTID,
@CONSTITUENTID = CONSTITUENTID
from
dbo.REGISTRANT
where
ID = @GUESTOFREGISTRANTID;
select @ORDERREGISTRANTID = ID from @ORDERREGISTRANTS where CONSTITUENTID = @CONSTITUENTID;
if @REGISTRANTID is null
begin
if @ORDERREGISTRANTID is null
begin
set @REGISTRANTID = newid();
insert into dbo.REGISTRANT
(
ID,
EVENTID,
CONSTITUENTID,
WILLNOTATTEND,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values
(
@REGISTRANTID,
@EVENTID,
@CONSTITUENTID,
1,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end
else
begin
set @REGISTRANTID = @ORDERREGISTRANTID;
insert into dbo.REGISTRANT
(
ID,
EVENTID,
CONSTITUENTID,
WILLNOTATTEND,
NOTES,
ATTENDED,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
ID,
@EVENTID,
CONSTITUENTID,
0,
NOTES,
@MARKREGISTRANTSATTENDED,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
@ORDERREGISTRANTS
where
ID = @ORDERREGISTRANTID;
end
end
else
begin
if @ORDERREGISTRANTID is not null
begin
update @ORDERREGISTRANTS set
ID = @REGISTRANTID
where
ID = @ORDERREGISTRANTID;
update @ORDERREGISTRANTPREFERENCES set
REGISTRANTID = @REGISTRANTID
where
REGISTRANTID = @ORDERREGISTRANTID;
update @ORDERREGISTRANTREGISTRATIONINFORMATION set
REGISTRANTID = @REGISTRANTID
where
REGISTRANTID = @ORDERREGISTRANTID;
set @ORDERREGISTRANTID = @REGISTRANTID;
update dbo.REGISTRANT set
WILLNOTATTEND = 0,
ATTENDED =
case
when REGISTRANT.ATTENDED = 1 then 1
else @MARKREGISTRANTSATTENDED
end,
NOTES = ORDERREGISTRANTS.NOTES,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from
dbo.REGISTRANT
inner join
@ORDERREGISTRANTS as ORDERREGISTRANTS on ORDERREGISTRANTS.ID = REGISTRANT.ID
where
REGISTRANT.ID = @ORDERREGISTRANTID;
end
end
insert into dbo.REGISTRANT
(
ID,
EVENTID,
CONSTITUENTID,
WILLNOTATTEND,
GUESTOFREGISTRANTID,
NOTES,
ATTENDED,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
ID,
@EVENTID,
CONSTITUENTID,
0,
@REGISTRANTID,
NOTES,
@MARKREGISTRANTSATTENDED,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
@ORDERREGISTRANTS
where
ID <> @REGISTRANTID;
insert into dbo.REGISTRANTPREFERENCE
(
ID,
REGISTRANTID,
EVENTPREFERENCEID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
ID,
REGISTRANTID,
EVENTPREFERENCEID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
@ORDERREGISTRANTPREFERENCES;
insert into dbo.REGISTRANTREGISTRATIONINFORMATION
(
ID,
REGISTRANTID,
REGISTRATIONINFORMATIONID,
PERSONDETAILTYPECODE,
REGISTRATIONINFORMATIONOPTIONID,
TEXTVALUE,
BOOLEANVALUE,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
ID,
REGISTRANTID,
REGISTRATIONINFORMATIONID,
PERSONDETAILTYPECODE,
case RESPONSETYPECODE
when 2 then REGISTRATIONINFORMATIONOPTIONID
else null
end,
TEXTVALUE,
case RESPONSETYPECODE
when 3 then
case REGISTRATIONINFORMATIONOPTIONID
when '11111111-1111-1111-1111-111111111111' then 1
else 0
end
else 0
end,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from @ORDERREGISTRANTREGISTRATIONINFORMATION;
insert into dbo.SALESORDERITEMTICKETREGISTRANT
(
ID,
SALESORDERITEMTICKETID,
REGISTRANTID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
newid(),
SALESORDERITEMTICKET.ID,
REGISTRANTS.ID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
dbo.SALESORDERITEMTICKET
inner join
@ORDERREGISTRANTS as REGISTRANTS on REGISTRANTS.PRICETYPECODEID = SALESORDERITEMTICKET.PRICETYPECODEID
inner join
dbo.SALESORDERITEM on SALESORDERITEM.ID = SALESORDERITEMTICKET.ID
where
SALESORDERITEM.SALESORDERID = @SALESORDERID
and SALESORDERITEMTICKET.EVENTID = @EVENTID;
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0