USP_SALESORDERITEMEVENTREGISTRATION_ADD
Adds collection of host registrants to order.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | INOUT | |
| @SALESORDERID | uniqueidentifier | IN | |
| @REGISTRATIONS | xml | IN | |
| @CURRENTDATE | datetime | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN |
Definition
Copy
CREATE procedure dbo.USP_SALESORDERITEMEVENTREGISTRATION_ADD (
@ID uniqueidentifier output,
@SALESORDERID uniqueidentifier,
@REGISTRATIONS xml,
@CURRENTDATE datetime = null,
@CHANGEAGENTID uniqueidentifier = null
)
as
begin
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output
if @CURRENTDATE is null
set @CURRENTDATE = getdate()
--Determine which other registrations could have been created/altered
declare @SELECTEDREGISTRATIONS table (
[ID] uniqueidentifier,
[REGISTRANTID] uniqueidentifier,
[CONSTITUENTID] uniqueidentifier,
[EVENTID] uniqueidentifier,
[AMOUNT] money --Order total
)
insert @SELECTEDREGISTRATIONS
select
coalesce(( --Get sales order item id for registrations that already exist in order
select [SALESORDERITEMEVENTREGISTRATION].[ID]
from dbo.[SALESORDERITEMEVENTREGISTRATION]
inner join dbo.[SALESORDERITEM]
on [SALESORDERITEMEVENTREGISTRATION].[ID] = [SALESORDERITEM].[ID]
where
[SALESORDERITEM].[SALESORDERID] = @SALESORDERID and
[SALESORDERITEMEVENTREGISTRATION].[REGISTRANTID] = T.registrant.value('(REGISTRANTID)[1]', 'uniqueidentifier')
),newid()) as [ID],
T.registrant.value('(REGISTRANTID)[1]', 'uniqueidentifier') as [REGISTRANTID],
(select [REGISTRANT].[CONSTITUENTID] from dbo.REGISTRANT where [REGISTRANT].[ID] = T.registrant.value('(REGISTRANTID)[1]', 'uniqueidentifier')) as [CONSTITUENTID],
(
select [REGISTRANT].[EVENTID]
from dbo.[REGISTRANT]
where [REGISTRANT].[ID] = T.registrant.value('(REGISTRANTID)[1]', 'uniqueidentifier')
) as [EVENTID],
T.registrant.value('(AMOUNT)[1]', 'money') as [AMOUNT]
from @REGISTRATIONS.nodes('/REGISTRATIONS/ITEM') T(registrant)
where T.registrant.value('(ISSELECTED)[1]', 'bit') = 1
--Return a valid sales order item id
set @ID = (select top 1 [ID] from @SELECTEDREGISTRATIONS)
--Add/Update Sales order items
begin try
--If the user entered an AMOUNT < 0, return an error
if exists (
select [ID]
from @SELECTEDREGISTRATIONS
where [AMOUNT] < 0
)
begin
raiserror('BBEER_REGISTRATIONS_INVALIDAMOUNT', 13, 1);
return 1;
end
declare @CONSTITUENTID uniqueidentifier = null
if 1 = (select count(distinct [CONSTITUENTID]) from @SELECTEDREGISTRATIONS)
set @CONSTITUENTID = (select top 1 [CONSTITUENTID] from @SELECTEDREGISTRATIONS)
--Make the CONSTITUENTID the patron of the order if there is no patron
if @CONSTITUENTID is not null and (select [CONSTITUENTID] from dbo.[SALESORDER] where [ID] = @SALESORDERID) is null
begin
update dbo.[SALESORDER]
set
[CONSTITUENTID] = @CONSTITUENTID,
[DATECHANGED] = @CURRENTDATE,
[CHANGEDBYID] = @CHANGEAGENTID
where [ID] = @SALESORDERID
end
--Make the CONSTITUENTID the recipient of the order if there is no recipient
if @CONSTITUENTID is not null and (select [RECIPIENTID] from dbo.[SALESORDER] where [ID] = @SALESORDERID) is null
begin
update dbo.[SALESORDER]
set
[RECIPIENTID] = @CONSTITUENTID,
[ADDRESSID] = (select top(1) [ID] from dbo.[ADDRESS] where [CONSTITUENTID] = @CONSTITUENTID and [ISPRIMARY] = 1 and [DONOTMAIL] = 0),
[PHONEID] = (select top(1) [ID] from dbo.[PHONE] where [PHONE].[CONSTITUENTID] = @CONSTITUENTID and [PHONE].[ISPRIMARY] = 1 and [DONOTCALL] = 0),
[EMAILADDRESSID] = (select top(1) [ID] from dbo.[EMAILADDRESS] where [CONSTITUENTID] = @CONSTITUENTID and [ISPRIMARY] = 1 and [DONOTEMAIL] = 0),
[DATECHANGED] = @CURRENTDATE,
[CHANGEDBYID] = @CHANGEAGENTID
where [ID] = @SALESORDERID
end
--Update registrations that may have changed through registration form
update dbo.[SALESORDERITEM]
set
[PRICE] = (
select [AMOUNT]
from @SELECTEDREGISTRATIONS as [REGISTRATIONS]
inner join dbo.[SALESORDERITEMEVENTREGISTRATION]
on [REGISTRATIONS].[REGISTRANTID] = [SALESORDERITEMEVENTREGISTRATION].[REGISTRANTID]
where [SALESORDERITEMEVENTREGISTRATION].[ID] = [SALESORDERITEM].[ID]
),
[CHANGEDBYID] = @CHANGEAGENTID,
[DATECHANGED] = @CURRENTDATE
where exists (
select [REGISTRATIONS].[REGISTRANTID]
from @SELECTEDREGISTRATIONS as [REGISTRATIONS]
inner join dbo.[SALESORDERITEMEVENTREGISTRATION]
on [REGISTRATIONS].[REGISTRANTID] = [SALESORDERITEMEVENTREGISTRATION].[REGISTRANTID]
inner join dbo.[SALESORDERITEM] as [SOI]
on [SALESORDERITEMEVENTREGISTRATION].[ID] = [SOI].[ID]
where
[SALESORDERITEM].[SALESORDERID] = @SALESORDERID and
[SOI].[ID] = [SALESORDERITEM].[ID]
)
update dbo.[SALESORDERITEMEVENTREGISTRANTREGISTRATION]
set
[QUANTITY] = (
select coalesce(sum([REGISTRANTREGISTRATION].[QUANTITY]),0)
from dbo.[SALESORDERITEMEVENTREGISTRATION]
inner join dbo.[REGISTRANTREGISTRATION]
on [SALESORDERITEMEVENTREGISTRATION].[REGISTRANTID] = [REGISTRANTREGISTRATION].[REGISTRANTID]
where
[REGISTRANTREGISTRATION].[EVENTPRICEID] = [SALESORDERITEMEVENTREGISTRANTREGISTRATION].[EVENTPRICEID] and
[SALESORDERITEMEVENTREGISTRATION].[ID] = [SALESORDERITEMEVENTREGISTRANTREGISTRATION].[SALESORDERITEMEVENTREGISTRATIONID]
),
[AMOUNT] = (
select coalesce(sum([REGISTRANTREGISTRATION].[AMOUNT]),0)
from dbo.[SALESORDERITEMEVENTREGISTRATION]
inner join dbo.[REGISTRANTREGISTRATION]
on [SALESORDERITEMEVENTREGISTRATION].[REGISTRANTID] = [REGISTRANTREGISTRATION].[REGISTRANTID]
where
[REGISTRANTREGISTRATION].[EVENTPRICEID] = [SALESORDERITEMEVENTREGISTRANTREGISTRATION].[EVENTPRICEID] and
[SALESORDERITEMEVENTREGISTRATION].[ID] = [SALESORDERITEMEVENTREGISTRANTREGISTRATION].[SALESORDERITEMEVENTREGISTRATIONID]
),
[CHANGEDBYID] = @CHANGEAGENTID,
[DATECHANGED] = @CURRENTDATE
where exists (
select [SALESORDERITEMEVENTREGISTRATION].[REGISTRANTID]
from @SELECTEDREGISTRATIONS as [REGISTRATIONS]
inner join dbo.[REGISTRANTREGISTRATION]
on [REGISTRATIONS].[REGISTRANTID] = [REGISTRANTREGISTRATION].[REGISTRANTID]
inner join dbo.[SALESORDERITEMEVENTREGISTRATION]
on [REGISTRATIONS].[REGISTRANTID] = [SALESORDERITEMEVENTREGISTRATION].[REGISTRANTID]
inner join dbo.[SALESORDERITEM]
on [SALESORDERITEM].[ID] = [SALESORDERITEMEVENTREGISTRATION].[ID]
where
[SALESORDERITEM].[SALESORDERID] = @SALESORDERID and
[SALESORDERITEMEVENTREGISTRATION].[REGISTRANTID] = [REGISTRATIONS].[REGISTRANTID] and
[SALESORDERITEMEVENTREGISTRANTREGISTRATION].[EVENTPRICEID] = [REGISTRANTREGISTRATION].[EVENTPRICEID]
)
insert into dbo.[SALESORDERITEM]
(
[ID],
[SALESORDERID],
[TYPECODE],
[DESCRIPTION],
[QUANTITY],
[PRICE],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
select
[ID],
@SALESORDERID,
6,
dbo.UFN_EVENT_GETNAME([EVENTID]),
1,
[AMOUNT],
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from @SELECTEDREGISTRATIONS as [REGISTRATIONS]
where
--Registration is not already in order
[REGISTRANTID] not in (
select [SALESORDERITEMEVENTREGISTRATION].[REGISTRANTID]
from dbo.[SALESORDERITEMEVENTREGISTRATION]
inner join dbo.[SALESORDERITEM]
on [SALESORDERITEM].[ID] = [SALESORDERITEMEVENTREGISTRATION].[ID]
where [SALESORDERITEM].[SALESORDERID] = @SALESORDERID
)
insert into dbo.[SALESORDERITEMEVENTREGISTRATION]
(
[ID],
[REGISTRANTID],
[EVENTNAME],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
select
[ID],
[REGISTRANTID],
coalesce((select [EVENT].[NAME] from dbo.[EVENT] where [EVENT].[ID] = [REGISTRATIONS].[EVENTID]),''),
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from @SELECTEDREGISTRATIONS as [REGISTRATIONS]
where
--Registration not already in order
[REGISTRANTID] not in (
select [SALESORDERITEMEVENTREGISTRATION].[REGISTRANTID]
from dbo.[SALESORDERITEMEVENTREGISTRATION]
inner join dbo.[SALESORDERITEM]
on [SALESORDERITEM].[ID] = [SALESORDERITEMEVENTREGISTRATION].[ID]
where [SALESORDERITEM].[SALESORDERID] = @SALESORDERID
)
insert into dbo.[SALESORDERITEMEVENTREGISTRANTREGISTRATION]
(
[ID],
[SALESORDERITEMEVENTREGISTRATIONID],
[EVENTPRICEID],
[QUANTITY],
[AMOUNT],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
select
newid(),
[REGISTRATIONS].[ID],
[REGISTRANTREGISTRATION].[EVENTPRICEID],
coalesce(sum([REGISTRANTREGISTRATION].[QUANTITY]),0),
coalesce(sum([REGISTRANTREGISTRATION].[AMOUNT]),0),
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from @SELECTEDREGISTRATIONS as [REGISTRATIONS]
inner join dbo.[REGISTRANTREGISTRATION]
on [REGISTRATIONS].[REGISTRANTID] = [REGISTRANTREGISTRATION].[REGISTRANTID]
where not exists ( --Allows for new pricing information on new and altered registrations
select [SALESORDERITEMEVENTREGISTRATION].[REGISTRANTID]
from dbo.[SALESORDERITEMEVENTREGISTRATION]
inner join dbo.[SALESORDERITEM]
on [SALESORDERITEM].[ID] = [SALESORDERITEMEVENTREGISTRATION].[ID]
inner join dbo.[SALESORDERITEMEVENTREGISTRANTREGISTRATION]
on [SALESORDERITEMEVENTREGISTRATION].[ID] = [SALESORDERITEMEVENTREGISTRANTREGISTRATION].[SALESORDERITEMEVENTREGISTRATIONID]
where
[SALESORDERITEM].[SALESORDERID] = @SALESORDERID and
[SALESORDERITEMEVENTREGISTRATION].[REGISTRANTID] = [REGISTRATIONS].[REGISTRANTID] and
[SALESORDERITEMEVENTREGISTRANTREGISTRATION].[EVENTPRICEID] = [REGISTRANTREGISTRATION].[EVENTPRICEID]
)
group by
[REGISTRANTREGISTRATION].[EVENTPRICEID],
[REGISTRATIONS].[ID]
--Delete pricing data that has been deleted from registrations that are in the cart that have been edited within the form
declare @ORDERITEM uniqueidentifier = null
declare deleteprice_cursor cursor local fast_forward for
select [SALESORDERITEMEVENTREGISTRANTREGISTRATION].[ID]
from dbo.[SALESORDERITEMEVENTREGISTRANTREGISTRATION]
inner join dbo.[SALESORDERITEMEVENTREGISTRATION]
on [SALESORDERITEMEVENTREGISTRANTREGISTRATION].[SALESORDERITEMEVENTREGISTRATIONID] = [SALESORDERITEMEVENTREGISTRATION].[ID]
inner join dbo.[SALESORDERITEM]
on [SALESORDERITEMEVENTREGISTRATION].[ID] = [SALESORDERITEM].[ID]
where
[SALESORDERITEMEVENTREGISTRANTREGISTRATION].[EVENTPRICEID] not in (
select [EVENTPRICEID]
from dbo.[REGISTRANTREGISTRATION]
where [REGISTRANTREGISTRATION].[REGISTRANTID] = [SALESORDERITEMEVENTREGISTRATION].[REGISTRANTID]
) and
[SALESORDERITEM].[SALESORDERID] = @SALESORDERID
OPEN deleteprice_cursor
FETCH NEXT FROM deleteprice_cursor
INTO @ORDERITEM
while @@FETCH_STATUS = 0
begin
exec dbo.USP_SALESORDERITEMEVENTREGISTRANTREGISTRATION_DELETEBYID_WITHCHANGEAGENTID @ORDERITEM, @CHANGEAGENTID
FETCH NEXT FROM deleteprice_cursor
INTO @ORDERITEM
end
close deleteprice_cursor
deallocate deleteprice_cursor
if exists (select top 1 1 from dbo.SALESORDERITEM where SALESORDERID = @SALESORDERID)
begin
exec dbo.USP_COMBINATION_REMOVEINELIGIBLECOMBINATIONS @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 try
begin catch
exec dbo.USP_RAISE_ERROR
return 1
end catch
end