USP_DATAFORMTEMPLATE_EDIT_SALESORDERITEMTICKETCOMBINATION
The save procedure used by the edit dataform template "Sales Order Item Ticket Combination Edit Data Form".
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | IN | The input ID parameter indicating the ID of the record being edited. |
| @CHANGEAGENTID | uniqueidentifier | IN | Input parameter indicating the ID of the change agent invoking the procedure. |
| @ORDERID | uniqueidentifier | IN | Order ID |
| @TICKETCOMBINATIONID | uniqueidentifier | IN | Ticket combination ID |
| @PRICETYPES | xml | IN | Prices |
| @EVENTS | xml | IN | Events |
| @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 | |
| @EXPIREDCALLBACKURL | nvarchar(255) | IN |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_SALESORDERITEMTICKETCOMBINATION (
@ID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier = null,
@ORDERID uniqueidentifier,
@TICKETCOMBINATIONID uniqueidentifier,
@PRICETYPES xml,
@EVENTS xml,
@DATA xml,
@OPTIONS xml,
@CALLBACKURL nvarchar(255),
@SYSTEMTYPENAME nvarchar(255),
@ASSEMBLYNAME nvarchar(255),
@ATTRIBUTES xml,
@CATEGORYNAME nvarchar(255),
@EXPIREDCALLBACKURL nvarchar(255)
)
as
set nocount on;
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output
declare @CURRENTDATE datetime
set @CURRENTDATE = getdate()
declare @COMBINATIONID uniqueidentifier;
select top 1 @COMBINATIONID = COMBINATIONID
from dbo.SALESORDERITEMTICKETCOMBINATION
where ID = @ID;
declare @COMBINATIONPROGRAMS table (
PROGRAMID uniqueidentifier,
PROGRAMGROUPID uniqueidentifier,
ISDAILYADMISSION bit
);
insert into @COMBINATIONPROGRAMS
(
PROGRAMID,
PROGRAMGROUPID,
ISDAILYADMISSION
)
select distinct
T.items.value('(ID)[1]','uniqueidentifier'),
T.items.value('(PROGRAMGROUPID)[1]','uniqueidentifier'),
T.items.value('(ISDAILYADMISSION)[1]','bit')
from @EVENTS.nodes('/EVENTS/ITEM') T(items)
-- Get new order items
-- price types and quantity are from input parameter
-- the rest information is from existing order items
declare @ORDERITEMS table (
ID uniqueidentifier,
QUANTITY int,
PRICETYPECODEID uniqueidentifier,
PRICETYPE nvarchar(100),
PRICE money,
PROGRAMID uniqueidentifier,
PROGRAMGROUPID uniqueidentifier,
DESCRIPTION nvarchar(100),
ISDAILYADMISSION bit
);
insert into @ORDERITEMS
select isnull(SALESORDERITEMTICKET.ID, newid()),
T.items.value('(QUANTITY)[1]','integer'),
T.items.value('(PRICETYPECODEID)[1]', 'uniqueidentifier'),
T.items.value('(PRICETYPE)[1]','nvarchar(100)'),
T.items.value('(FACEPRICE)[1]','money'),
PROGRAMS.PROGRAMID,
PROGRAMS.PROGRAMGROUPID,
case PROGRAMS.ISDAILYADMISSION
when 1 then PROGRAM.NAME
when 0 then EVENT.NAME
end,
PROGRAMS.ISDAILYADMISSION
from @PRICETYPES.nodes('/PRICETYPES/ITEM') T(items)
full outer join @COMBINATIONPROGRAMS PROGRAMS
on PROGRAMS.PROGRAMID is not null
left join (
dbo.SALESORDERITEMTICKET
inner join dbo.SALESORDERITEMTICKETCOMBINATION
on SALESORDERITEMTICKET.ID = SALESORDERITEMTICKETCOMBINATION.ID
)
on
(
(
PROGRAMS.PROGRAMGROUPID = SALESORDERITEMTICKETCOMBINATION.PROGRAMGROUPID and
T.items.value('(PRICETYPECODEID)[1]', 'uniqueidentifier') = SALESORDERITEMTICKET.PRICETYPECODEID and
SALESORDERITEMTICKETCOMBINATION.TICKETCOMBINATIONID = @TICKETCOMBINATIONID
)
)
left join dbo.PROGRAM on PROGRAM.ID = PROGRAMS.PROGRAMID
left join dbo.EVENT on EVENT.ID = PROGRAMS.PROGRAMID
where T.items.value('(QUANTITY)[1]','integer') > 0;
-- Update face price with actual program/event price
-- The face price get from input is combo price
update @ORDERITEMS set PRICE = PROGRAMGROUPPRICE.FACEPRICE
from @ORDERITEMS ITEMS
inner join dbo.PROGRAMGROUP on PROGRAMGROUP.ID = ITEMS.PROGRAMGROUPID
inner join dbo.PROGRAMGROUPPRICE on PROGRAMGROUPPRICE.PROGRAMGROUPID = PROGRAMGROUP.ID
inner join (dbo.COMBINATIONPRICETYPE inner join dbo.COMBINATION on COMBINATION.ID = COMBINATIONPRICETYPE.COMBINATIONID)
on COMBINATIONPRICETYPE.PRICETYPECODEID = ITEMS.PRICETYPECODEID and COMBINATIONPRICETYPE.ID = PROGRAMGROUPPRICE.COMBINATIONPRICETYPEID
where COMBINATION.ID = @COMBINATIONID
begin try
exec dbo.USP_SALESORDER_ISCOMPLETE_RAISERROR @ORDERID, @EXCLUDEGROUPSALES = 1;
-- Check if total of the order overflows
declare @TOTAL money;
-- Total of new order items
select @TOTAL = SUM(QUANTITY * PRICE)
from @ORDERITEMS;
-- total of existing order items - excludes the same combo
select @TOTAL = @TOTAL + isnull(SUM(SALESORDERITEM.TOTAL), 0)
from dbo.SALESORDERITEM
left join dbo.SALESORDERITEMFEE on SALESORDERITEM.ID = SALESORDERITEMFEE.ID
inner join dbo.SALESORDERITEMTICKET on SALESORDERITEM.ID = SALESORDERITEMTICKET.ID
left join dbo.SALESORDERITEMTICKETCOMBINATION on SALESORDERITEMTICKET.ID = SALESORDERITEMTICKETCOMBINATION.ID
where
SALESORDERITEM.SALESORDERID = @ORDERID and
(
(SALESORDERITEMTICKETCOMBINATION.TICKETCOMBINATIONID is null) or
(
not SALESORDERITEMTICKETCOMBINATION.TICKETCOMBINATIONID is null and
SALESORDERITEMTICKETCOMBINATION.TICKETCOMBINATIONID <> @TICKETCOMBINATIONID
)
) and
(
SALESORDERITEM.TYPECODE <> 3 or
(SALESORDERITEM.TYPECODE = 3 and SALESORDERITEMFEE.APPLIESTOCODE = 0)
)
-- Check availability of scheduled events
if exists (
select 1
from (
select
PROGRAMID as [EVENTID],
sum(QUANTITY) as [QUANTITY]
from @ORDERITEMS [ITEMS]
where ITEMS.ISDAILYADMISSION = 0
group by PROGRAMID
) [ITEMSEVENTS]
where dbo.UFN_SALESORDERTICKET_VALIDQUANTITY([ITEMSEVENTS].[EVENTID], @ORDERID, [ITEMSEVENTS].[QUANTITY]) = 0
)
begin
raiserror('ERR_TICKETINSERT_UNAVAILABLE', 13, 1)
end
declare @SALESORDERSTATUSCODE tinyint;
declare @SALESMETHODID uniqueidentifier;
declare @SALESMETHODTYPECODE tinyint;
select
@SALESORDERSTATUSCODE = [SALESORDER].[STATUSCODE],
@SALESMETHODID = [SALESMETHOD].[ID],
@SALESMETHODTYPECODE = [SALESMETHOD].[TYPECODE]
from dbo.[SALESORDER]
inner join dbo.[SALESMETHOD]
on [SALESORDER].[SALESMETHODTYPECODE] = [SALESMETHOD].[TYPECODE]
where [SALESORDER].[ID] = @ORDERID;
--For online, we need to make sure that this ticket can be delivered
if @SALESMETHODTYPECODE = 2 and @SALESORDERSTATUSCODE <> 7 --unless it's an unresolved order
begin
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 exists(select 1 from @ORDERITEMS ORDERITEMS inner join dbo.[EVENT] on ORDERITEMS.PROGRAMID = [EVENT].ID where @EARLIESTVALIDEVENTDATETIMEWITHOFFSET > [EVENT].[STARTDATETIMEWITHOFFSET])
raiserror('ERR_HASNODELIVERYMETHOD', 13, 1)
end
end
else
raiserror('ERR_HASNODELIVERYMETHOD', 13, 1)
end
declare @contextCache varbinary(128);
declare @e int;
-- cache current context information
set @contextCache = CONTEXT_INFO();
-- set CONTEXT_INFO to @CHANGEAGENTID
if not @CHANGEAGENTID is null
set CONTEXT_INFO @CHANGEAGENTID;
-- delete any fees attached to items no longer in the table
delete from dbo.[SALESORDERITEM]
where ID in (
select SALESORDERITEM.ID from SALESORDERITEM
inner join dbo.SALESORDERITEMFEE on
SALESORDERITEM.ID = SALESORDERITEMFEE.ID
where
SALESORDERITEMFEE.SALESORDERITEMID in (
select SALESORDERITEM.ID
from dbo.SALESORDERITEM
inner join dbo.SALESORDERITEMTICKET on SALESORDERITEM.ID = SALESORDERITEMTICKET.ID
inner join dbo.SALESORDERITEMTICKETCOMBINATION on SALESORDERITEMTICKET.ID = SALESORDERITEMTICKETCOMBINATION.ID
where
SALESORDERID = @ORDERID
and SALESORDERITEMTICKETCOMBINATION.TICKETCOMBINATIONID = @TICKETCOMBINATIONID
EXCEPT select ID from @ORDERITEMS
)
)
-- delete any items that no longer exist in the XML table
delete from dbo.[SALESORDERITEM]
where [SALESORDERITEM].ID in (
select SALESORDERITEM.ID
from dbo.SALESORDERITEM
inner join dbo.SALESORDERITEMTICKET on SALESORDERITEM.ID = SALESORDERITEMTICKET.ID
inner join dbo.SALESORDERITEMTICKETCOMBINATION on SALESORDERITEMTICKET.ID = SALESORDERITEMTICKETCOMBINATION.ID
where
SALESORDERID = @ORDERID
and SALESORDERITEMTICKETCOMBINATION.TICKETCOMBINATIONID = @TICKETCOMBINATIONID
EXCEPT select ID from @ORDERITEMS
)
select @e=@@error;
-- reset CONTEXT_INFO to previous value
if not @contextCache is null
set CONTEXT_INFO @contextCache;
if @e <> 0
return 2;
-- update the items that exist in the XML table and the db
update dbo.[SALESORDERITEM]
set
[SALESORDERITEM].[QUANTITY]=ITEMS.[QUANTITY],
[SALESORDERITEM].[DESCRIPTION]=ITEMS.DESCRIPTION + ' - ' + ITEMS.PRICETYPE,
[SALESORDERITEM].CHANGEDBYID = @CHANGEAGENTID,
[SALESORDERITEM].DATECHANGED = @CURRENTDATE
from dbo.[SALESORDERITEM]
inner join @ORDERITEMS as [ITEMS] on [SALESORDERITEM].ID = [ITEMS].ID
update dbo.[SALESORDERITEMTICKET]
set
[SALESORDERITEMTICKET].[PROGRAMID] = case when ITEMS.ISDAILYADMISSION = 1 then ITEMS.[PROGRAMID] else (select [PROGRAMID] from dbo.[EVENT] where [ID] = ITEMS.[PROGRAMID]) end,
[SALESORDERITEMTICKET].[EVENTID] = case when ITEMS.ISDAILYADMISSION = 0 then ITEMS.[PROGRAMID] end,
[SALESORDERITEMTICKET].CHANGEDBYID = @CHANGEAGENTID,
[SALESORDERITEMTICKET].DATECHANGED = @CURRENTDATE
from dbo.[SALESORDERITEMTICKET]
inner join @ORDERITEMS as [ITEMS] on [SALESORDERITEMTICKET].ID = [ITEMS].ID
if @@Error <> 0
return 3;
-- insert new items
insert into dbo.SALESORDERITEM (
ID,
SALESORDERID,
TYPECODE,
DESCRIPTION,
QUANTITY,
PRICE,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED,
DATA,
OPTIONS,
CALLBACKURL,
SYSTEMTYPENAME,
ASSEMBLYNAME,
ATTRIBUTES,
CATEGORYNAME,
EXPIREDCALLBACKURL
)
select
ITEMS.ID,
@ORDERID,
0,
ITEMS.DESCRIPTION + ' - ' + ITEMS.PRICETYPE,
ITEMS.QUANTITY,
ITEMS.PRICE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE,
@DATA,
@OPTIONS,
@CALLBACKURL,
@SYSTEMTYPENAME,
@ASSEMBLYNAME,
@ATTRIBUTES,
@CATEGORYNAME,
@EXPIREDCALLBACKURL
from @ORDERITEMS ITEMS
where not exists (select ID from dbo.[SALESORDERITEM] as data where data.ID = [ITEMS].ID)
if @@Error <> 0
return 4;
insert into dbo.SALESORDERITEMTICKET
(
ID,
PROGRAMID,
EVENTID,
PROGRAMNAME,
PROGRAMCATEGORYNAME,
PRICETYPECODEID,
PRICE,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
ITEMS.ID,
case ITEMS.ISDAILYADMISSION
when 1 then ITEMS.PROGRAMID
else (select PROGRAMID from dbo.EVENT where ID = ITEMS.PROGRAMID)
end,
case ITEMS.ISDAILYADMISSION
when 0 then ITEMS.PROGRAMID
else null
end,
case ITEMS.ISDAILYADMISSION
when 1 then coalesce((select [NAME] from dbo.[PROGRAM] where [ID] = ITEMS.PROGRAMID),'')
when 0 then coalesce((
select [PROGRAM].[NAME]
from dbo.[EVENT]
inner join dbo.[PROGRAM]
on [EVENT].[PROGRAMID] = [PROGRAM].[ID]
where [EVENT].[ID] = ITEMS.PROGRAMID
),'')
end,
case ITEMS.ISDAILYADMISSION
when 1 then coalesce((
select [PROGRAMCATEGORYCODE].[DESCRIPTION]
from dbo.[PROGRAM]
inner join dbo.[PROGRAMCATEGORYCODE]
on [PROGRAM].[PROGRAMCATEGORYCODEID] = [PROGRAMCATEGORYCODE].[ID]
where [PROGRAM].[ID] = ITEMS.PROGRAMID
),'')
when 0 then coalesce((
select [PROGRAMCATEGORYCODE].[DESCRIPTION]
from dbo.[EVENT]
inner join dbo.[PROGRAM]
on [EVENT].[PROGRAMID] = [PROGRAM].[ID]
inner join dbo.[PROGRAMCATEGORYCODE]
on [PROGRAM].[PROGRAMCATEGORYCODEID] = [PROGRAMCATEGORYCODE].[ID]
where [EVENT].[ID] = ITEMS.PROGRAMID
),'')
end,
ITEMS.PRICETYPECODEID,
ITEMS.PRICE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from @ORDERITEMS ITEMS
where not exists (select ID from dbo.[SALESORDERITEMTICKET] as data where data.ID = [ITEMS].ID)
insert into dbo.SALESORDERITEMTICKETCOMBINATION
(
ID,
COMBINATIONID,
PROGRAMGROUPID,
TICKETCOMBINATIONID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
ITEMS.ID,
@COMBINATIONID,
ITEMS.PROGRAMGROUPID,
@TICKETCOMBINATIONID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from @ORDERITEMS ITEMS
where not exists (select ID from dbo.[SALESORDERITEMTICKETCOMBINATION] as data where data.ID = [ITEMS].ID)
if @@Error <> 0
return 4;
exec dbo.USP_SALESORDER_CALCULATEFEES @ORDERID, @CHANGEAGENTID;
exec dbo.USP_SALESORDER_APPLYITEMDISCOUNTS @ORDERID, @CHANGEAGENTID;
exec dbo.USP_SALESORDER_APPLYORDERDISCOUNTS @ORDERID, @CHANGEAGENTID;
exec dbo.USP_SALESORDER_CALCULATETAXES @ORDERID, @CHANGEAGENTID;
end try
begin catch
-- catch the datatype money overflow error,
if ERROR_NUMBER() = 8115
begin
raiserror('ERR_TICKETINSERT_MONEYOVERFLOW', 13, 1)
end
exec dbo.USP_RAISE_ERROR
return 1
end catch
return 0;