USP_DATAFORMTEMPLATE_ADD_SALESORDERCOMBINATION
The save procedure used by the add dataform template "Sales Order Item Ticket Combination Add Data Form".
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | INOUT | The output parameter indicating the ID of the record added. |
| @COMBINATIONID | uniqueidentifier | IN | Input parameter indicating the context ID for the record being added. |
| @CHANGEAGENTID | uniqueidentifier | IN | Input parameter indicating the ID of the change agent invoking the procedure. |
| @DATESELECTTYPE | int | IN | Date |
| @STARTDATE | datetime | IN | From |
| @ENDDATE | datetime | IN | To |
| @ORDERID | uniqueidentifier | IN | Order ID |
| @COMBINATIONNAME | nvarchar(100) | IN | Name |
| @SAMEDAYEVENT | bit | IN | Same day event |
| @PROGRAMGROUPS | xml | IN | Program group |
| @PRICETYPES | xml | IN | Prices |
| @ALLOWPASTEVENTS | bit | IN | Allow sales to past 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 | |
| @TICKETCOMBINATIONID | uniqueidentifier | IN |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_SALESORDERCOMBINATION
(
@ID uniqueidentifier = null output,
@COMBINATIONID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier = null,
@DATESELECTTYPE int = 2,
@STARTDATE datetime = null,
@ENDDATE datetime = null,
@ORDERID uniqueidentifier = null,
@COMBINATIONNAME nvarchar(100) = null,
@SAMEDAYEVENT bit = null,
@PROGRAMGROUPS xml = null,
@PRICETYPES xml = null,
@ALLOWPASTEVENTS bit = 0,
@DATA xml = null,
@OPTIONS xml = null,
@CALLBACKURL nvarchar(255) = '',
@SYSTEMTYPENAME nvarchar(255) = '',
@ASSEMBLYNAME nvarchar(255) = '',
@ATTRIBUTES xml = null,
@CATEGORYNAME nvarchar(255) = '',
@EXPIREDCALLBACKURL nvarchar(255) = '',
@TICKETCOMBINATIONID uniqueidentifier = 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()
declare @CURRENTDATETIMEWITHOFFSET datetimeoffset = sysdatetimeoffset();
declare @SALESMETHODID uniqueidentifier;
declare @SALESMETHODTYPECODE tinyint;
select
@SALESMETHODID = [SALESMETHOD].[ID],
@SALESMETHODTYPECODE = [SALESMETHOD].[TYPECODE]
from dbo.[SALESORDER]
inner join dbo.[SALESMETHOD]
on [SALESORDER].[SALESMETHODTYPECODE] = [SALESMETHOD].[TYPECODE]
where [SALESORDER].[ID] = @ORDERID;
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 newid(),
T.items.value('(QUANTITY)[1]','integer'),
T.items.value('(PRICETYPECODEID)[1]', 'uniqueidentifier'),
T.items.value('(PRICETYPE)[1]','nvarchar(100)'),
PROGRAMGROUPPRICE.FACEPRICE,
T.items.value('(EVENTID)[1]', 'uniqueidentifier'),
T.items.value('(PROGRAMGROUPID)[1]', 'uniqueidentifier'),
case T.items.value('(ISDAILYADMISSION)[1]', 'bit')
when 1 then PROGRAM.NAME
when 0 then EVENT.NAME
end,
T.items.value('(ISDAILYADMISSION)[1]', 'bit')
from @PRICETYPES.nodes('/PRICETYPES/ITEM') T(items)
inner join (
dbo.COMBINATIONPRICETYPE
inner join dbo.PROGRAMGROUPPRICE
on PROGRAMGROUPPRICE.COMBINATIONPRICETYPEID = COMBINATIONPRICETYPE.ID
)
on
COMBINATIONPRICETYPE.PRICETYPECODEID = T.items.value('(PRICETYPECODEID)[1]', 'uniqueidentifier') and
PROGRAMGROUPPRICE.PROGRAMGROUPID = T.items.value('(PROGRAMGROUPID)[1]', 'uniqueidentifier')
left join dbo.PROGRAM
on PROGRAM.ID = T.items.value('(EVENTID)[1]', 'uniqueidentifier')
left join dbo.EVENT
on EVENT.ID = T.items.value('(EVENTID)[1]', 'uniqueidentifier')
where T.items.value('(QUANTITY)[1]','integer') > 0 and
COMBINATIONPRICETYPE.COMBINATIONID = @COMBINATIONID;
begin try
exec dbo.USP_SALESORDER_ISCOMPLETE_RAISERROR @ORDERID, @EXCLUDEGROUPSALES = 1;
-- Check that the events have not already ended
declare @EVENTENDDATETIMEWITHOFFSET datetimeoffset
select @EVENTENDDATETIMEWITHOFFSET = min([EVENTTIMES].[EVENTOFFTIME])
from (
select
case
when [PROGRAMSALESMETHOD].[ID] is null then [EVENT].[ENDDATETIMEWITHOFFSET]
when [PROGRAMSALESMETHOD].[ONSALEENDTYPECODE] = 0 then [EVENT].[STARTDATETIMEWITHOFFSET]
when [PROGRAMSALESMETHOD].[ONSALEENDTYPECODE] = 1 then dateadd(mi, -1 * [PROGRAMSALESMETHOD].[ONSALEENDINTERVAL], [EVENT].[STARTDATETIMEWITHOFFSET])
when [PROGRAMSALESMETHOD].[ONSALEENDTYPECODE] = 2 then dateadd(hh, -1 * [PROGRAMSALESMETHOD].[ONSALEENDINTERVAL], [EVENT].[STARTDATETIMEWITHOFFSET])
when [PROGRAMSALESMETHOD].[ONSALEENDTYPECODE] = 3 then dateadd(mi, [PROGRAMSALESMETHOD].[ONSALEENDINTERVAL], [EVENT].[STARTDATETIMEWITHOFFSET])
end as [EVENTOFFTIME]
from dbo.[EVENT]
inner join @ORDERITEMS [ITEMS]
on [ITEMS].[PROGRAMID] = [EVENT].[ID]
inner join dbo.[PROGRAM]
on [EVENT].[PROGRAMID] = [PROGRAM].[ID]
left join dbo.[PROGRAMSALESMETHOD]
on
[PROGRAM].[ID] = [PROGRAMSALESMETHOD].[PROGRAMID] and
[PROGRAMSALESMETHOD].[SALESMETHODID] = @SALESMETHODID and
[ITEMS].[ISDAILYADMISSION] = 0
) as [EVENTTIMES]
if (@CURRENTDATETIMEWITHOFFSET >= @EVENTENDDATETIMEWITHOFFSET) and @ALLOWPASTEVENTS = 0
begin
raiserror('ERR_PASTEVENT', 13, 1);
end
-- Check event availability
if exists(
select [EVENT].[ID]
from dbo.[EVENT]
inner join @ORDERITEMS ITEMS on [EVENT].[ID] = ITEMS.PROGRAMID and ITEMS.ISDAILYADMISSION = 0
group by EVENT.ID
having sum(ITEMS.QUANTITY) > dbo.UFN_EVENT_GETAVAILABILITY(EVENT.ID)
)
begin
raiserror('ERR_TICKETINSERT_UNAVAILABLE', 13, 1)
end
--For online, we need to make sure that this ticket can be delivered
if @SALESMETHODTYPECODE = 2
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
-- 4/20/09, LeeCh, Bug 19897, Starts
-- Check if total overflows by
-- adding the total in new order items and
-- the total in existing order items
-- An overflow error will be processed in the
-- catch block
declare @TOTAL money;
-- total in new order items
select @TOTAL = SUM(QUANTITY * PRICE)
from @ORDERITEMS;
-- total in existing order items
select @TOTAL = @TOTAL + isnull(SUM(SALESORDERITEM.TOTAL), 0)
from dbo.SALESORDERITEM
left join dbo.SALESORDERITEMFEE on
SALESORDERITEM.ID = SALESORDERITEMFEE.ID
where
SALESORDERITEM.SALESORDERID = @ORDERID and
(
SALESORDERITEM.TYPECODE <> 3 or
(SALESORDERITEM.TYPECODE = 3 and SALESORDERITEMFEE.APPLIESTOCODE = 0)
)
-- 4/20/09, LeeCh, Bug 19897, Ends
declare @EXISTINGTICKETCOMBINATIONID uniqueidentifier;
-- Check if need to update the quantity of existing tickets
select
@EXISTINGTICKETCOMBINATIONID = EXISTINGCOMBOTICKETS.TICKETCOMBINATIONID
from (
select PROGRAMGROUPID, PROGRAMID, ISDAILYADMISSION
from @ORDERITEMS
group by PROGRAMGROUPID, PROGRAMID, ISDAILYADMISSION
) as NEWCOMBOTICKETS
inner join (
select
PROGRAMGROUPID,
PROGRAMID,
EVENTID,
TICKETCOMBINATIONID
from dbo.SALESORDERITEM
inner join dbo.SALESORDERITEMTICKET
on SALESORDERITEM.ID = SALESORDERITEMTICKET.ID
inner join dbo.SALESORDERITEMTICKETCOMBINATION
on SALESORDERITEMTICKET.ID = SALESORDERITEMTICKETCOMBINATION.ID
where COMBINATIONID = @COMBINATIONID and SALESORDERITEM.SALESORDERID = @ORDERID
group by PROGRAMGROUPID, PROGRAMID, EVENTID, TICKETCOMBINATIONID
) as EXISTINGCOMBOTICKETS
on NEWCOMBOTICKETS.PROGRAMGROUPID = EXISTINGCOMBOTICKETS.PROGRAMGROUPID
and (
(NEWCOMBOTICKETS.PROGRAMID = EXISTINGCOMBOTICKETS.PROGRAMID and NEWCOMBOTICKETS.ISDAILYADMISSION = 1)
or (NEWCOMBOTICKETS.PROGRAMID = EXISTINGCOMBOTICKETS.EVENTID and NEWCOMBOTICKETS.ISDAILYADMISSION = 0)
)
group by EXISTINGCOMBOTICKETS.TICKETCOMBINATIONID
having COUNT(*) = (
select
COUNT(*)
from (
select
PROGRAMGROUPID,
PROGRAMID
from @ORDERITEMS
group by PROGRAMGROUPID, PROGRAMID
) as NEWCOMBOTICKETS
)
if @EXISTINGTICKETCOMBINATIONID is not null
begin
update dbo.SALESORDERITEM set
SALESORDERITEM.QUANTITY = SALESORDERITEM.QUANTITY + ITEMS.QUANTITY,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from @ORDERITEMS ITEMS
where
SALESORDERID = @ORDERID and
SALESORDERITEM.ID in (
select SALESORDERITEMTICKET.ID
from dbo.SALESORDERITEMTICKET
inner join dbo.SALESORDERITEMTICKETCOMBINATION
on SALESORDERITEMTICKET.ID = SALESORDERITEMTICKETCOMBINATION.ID
where
(
(ITEMS.PROGRAMID = SALESORDERITEMTICKET.PROGRAMID and ITEMS.ISDAILYADMISSION = 1) or
(ITEMS.PROGRAMID = SALESORDERITEMTICKET.EVENTID and ITEMS.ISDAILYADMISSION = 0)
) and
COMBINATIONID = @COMBINATIONID and
ITEMS.PROGRAMGROUPID = SALESORDERITEMTICKETCOMBINATION.PROGRAMGROUPID and
SALESORDERITEMTICKET.PRICETYPECODEID = ITEMS.PRICETYPECODEID and
SALESORDERITEMTICKETCOMBINATION.TICKETCOMBINATIONID = @EXISTINGTICKETCOMBINATIONID
);
if @CALLBACKURL is not null
set @CALLBACKURL = replace(@CALLBACKURL, cast(@TICKETCOMBINATIONID as nvarchar(36)), cast(@EXISTINGTICKETCOMBINATIONID as nvarchar(36)));
if @EXPIREDCALLBACKURL is not null
set @EXPIREDCALLBACKURL = replace(@EXPIREDCALLBACKURL, cast(@TICKETCOMBINATIONID as nvarchar(36)), cast(@EXISTINGTICKETCOMBINATIONID as nvarchar(36)));
set @TICKETCOMBINATIONID = @EXISTINGTICKETCOMBINATIONID;
end
else
begin
if @TICKETCOMBINATIONID is null
or @TICKETCOMBINATIONID = '00000000-0000-0000-0000-000000000000'
set @TICKETCOMBINATIONID = newid();
end
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 1
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
(
(ITEMS.PROGRAMID = SALESORDERITEMTICKET.PROGRAMID and ITEMS.ISDAILYADMISSION = 1) or
(ITEMS.PROGRAMID = SALESORDERITEMTICKET.EVENTID and ITEMS.ISDAILYADMISSION = 0)
) and
SALESORDERITEMTICKET.PRICETYPECODEID = ITEMS.PRICETYPECODEID and
SALESORDERITEMTICKETCOMBINATION.TICKETCOMBINATIONID = @TICKETCOMBINATIONID
);
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
ITEMS.ID in (
select ID
from dbo.SALESORDERITEM
where SALESORDERID = @ORDERID
);
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
ITEMS.ID in (
select ID
from dbo.SALESORDERITEM
where SALESORDERID = @ORDERID
);
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;
select top 1 @ID = [SALESORDERITEM].[ID]
from dbo.[SALESORDERITEMTICKETCOMBINATION]
inner join dbo.[SALESORDERITEM]
on [SALESORDERITEMTICKETCOMBINATION].[ID] = [SALESORDERITEM].[ID]
where
[SALESORDERITEMTICKETCOMBINATION].[COMBINATIONID] = @COMBINATIONID and
[SALESORDERITEM].[SALESORDERID] = @ORDERID
end try
begin catch
-- 4/20/09, LeeCh, Bug 19897, Starts
-- catch the datatype money overflow error,
if ERROR_NUMBER() = 8115
begin
raiserror('ERR_TICKETINSERT_MONEYOVERFLOW', 13, 1)
end
-- 4/20/09, LeeCh, Bug 19897, Ends
exec dbo.USP_RAISE_ERROR
return 1
end catch
return 0;