USP_DATAFORMTEMPLATE_EDITLOAD_SALESORDERITEMTICKETCOMBINATION

The load 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 used to load the fields defined on the form.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@TSLONG	bigint	INOUT	Output parameter indicating the TSLONG value of the record being edited. This is used to manage multi-user concurrency issues when multiple users access the same record.
@ORDERID	uniqueidentifier	INOUT	Order ID
@TICKETCOMBINATIONID	uniqueidentifier	INOUT	Ticket combination ID
@EVENTS	xml	INOUT	Events
@PRICETYPES	xml	INOUT	Prices
@CURRENTDATE	datetime	INOUT
@SAMEDAYEVENT	bit	INOUT	Same day event
@COMBINATIONNAME	nvarchar(100)	INOUT	Name
@COMBINATIONID	uniqueidentifier	INOUT	Combination ID
@PROGRAMGROUPS	xml	INOUT	Program group
@ALLOWPASTEVENTS	bit	INOUT	Allow sales to past events
@SALESMETHODTYPECODE	tinyint	INOUT
@DATA	xml	INOUT
@OPTIONS	xml	INOUT
@CALLBACKURL	nvarchar(255)	INOUT
@SYSTEMTYPENAME	nvarchar(255)	INOUT
@ASSEMBLYNAME	nvarchar(255)	INOUT
@ATTRIBUTES	xml	INOUT
@CATEGORYNAME	nvarchar(255)	INOUT
@EXPIREDCALLBACKURL	nvarchar(255)	INOUT
Definition

Copy


            CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_SALESORDERITEMTICKETCOMBINATION(
                @ID uniqueidentifier,
                @DATALOADED bit = 0 output,
                @TSLONG bigint = 0 output,
                @ORDERID uniqueidentifier = null output,
                @TICKETCOMBINATIONID uniqueidentifier = null output,
                @EVENTS xml = null output,
                @PRICETYPES xml = null output,
                @CURRENTDATE datetime = null output,
                @SAMEDAYEVENT bit = null output,
                @COMBINATIONNAME nvarchar(100) = null output,
                @COMBINATIONID uniqueidentifier = null output,
                @PROGRAMGROUPS xml = null output,
                @ALLOWPASTEVENTS bit = null output,
                @SALESMETHODTYPECODE tinyint = null output,
                @DATA xml = null output,
                @OPTIONS xml = null output,
                @CALLBACKURL nvarchar(255) = null output,
                @SYSTEMTYPENAME nvarchar(255) = null output,
                @ASSEMBLYNAME nvarchar(255) = null output,
                @ATTRIBUTES xml = null output,
                @CATEGORYNAME nvarchar(255) = null output,
                @EXPIREDCALLBACKURL nvarchar(255) = null output
            )
            as
                set nocount on;

                set @DATALOADED = 0;
                set @TSLONG = 0;                

                declare @PROGRAMID uniqueidentifier;

                select top 1
                    @DATALOADED = 1,
                    @ORDERID = SALESORDERID,
                    @TICKETCOMBINATIONID = TICKETCOMBINATIONID,
                    @COMBINATIONID = COMBINATIONID,
                    @SALESMETHODTYPECODE = SALESORDER.SALESMETHODTYPECODE,
                    @DATA = SALESORDERITEM.DATA,
                    @OPTIONS = SALESORDERITEM.OPTIONS,
                    @CALLBACKURL = SALESORDERITEM.CALLBACKURL,
                    @SYSTEMTYPENAME = SALESORDERITEM.SYSTEMTYPENAME,
                    @ASSEMBLYNAME = SALESORDERITEM.ASSEMBLYNAME,
                    @ATTRIBUTES = SALESORDERITEM.ATTRIBUTES,
                    @CATEGORYNAME = SALESORDERITEM.CATEGORYNAME,
                    @EXPIREDCALLBACKURL = SALESORDERITEM.EXPIREDCALLBACKURL
                from dbo.SALESORDERITEM
                inner join dbo.SALESORDERITEMTICKET on SALESORDERITEM.ID = SALESORDERITEMTICKET.ID
                inner join dbo.SALESORDERITEMTICKETCOMBINATION on SALESORDERITEMTICKET.ID = SALESORDERITEMTICKETCOMBINATION.ID
                inner join dbo.SALESORDER on SALESORDERITEM.SALESORDERID = SALESORDER.ID
                where SALESORDERITEM.ID = @ID;

                select 
                    @COMBINATIONNAME = COMBINATION.NAME, 
                    @SAMEDAYEVENT = EVENTSSAMEDAY
                from dbo.COMBINATION where COMBINATION.ID = @COMBINATIONID

                declare @SALESMETHODID uniqueidentifier
                select @SALESMETHODID = ID
                from dbo.SALESMETHOD
                where TYPECODE = @SALESMETHODTYPECODE

                select @EVENTS = (
                    select 
                        distinct SALESORDERITEMTICKETCOMBINATION.PROGRAMGROUPID,
                        isnull(EVENT.ID , PROGRAM.ID) as ID,
                        isnull(EVENT.NAME, PROGRAM.NAME) as EVENTNAME,
                        EVENT.STARTDATE as EVENTDATE,
                        EVENT.STARTTIME as STARTTIME,
                        EVENT.ENDTIME as ENDTIME,
                        case 
                            when EVENT.ID is null then 1
                            else 0
                        end as ISDAILYADMISSION,
                        case when [EVENT].[ID] is not null then dbo.UFN_EVENT_GETAVAILABILITY([EVENT].[ID]) else 0 end as [AVAILABILITY]
                    from dbo.SALESORDERITEMTICKETCOMBINATION 
                    inner join dbo.SALESORDERITEMTICKET on SALESORDERITEMTICKETCOMBINATION.ID = SALESORDERITEMTICKET.ID 
                    inner join dbo.SALESORDERITEM on SALESORDERITEMTICKET.ID = SALESORDERITEM.ID
                    left join dbo.PROGRAM on PROGRAM.ID = SALESORDERITEMTICKET.PROGRAMID 
                    left join dbo.EVENT on EVENT.ID = SALESORDERITEMTICKET.EVENTID
                    where SALESORDERITEMTICKETCOMBINATION.TICKETCOMBINATIONID = @TICKETCOMBINATIONID
                    for xml raw ('ITEM'), type, elements, root('EVENTS'), BINARY BASE64
                )

                select @PRICETYPES = (
                    select distinct 
                        COMBINATIONPRICETYPE.ID,
                        dbo.UFN_PRICETYPECODE_GETDESCRIPTION(COMBINATIONPRICETYPE.PRICETYPECODEID) as PRICETYPE,
                        COMBINATIONPRICETYPE.PRICETYPECODEID,
                        COMBINATIONPRICETYPE.SEQUENCE,
                        dbo.UFN_COMBINATION_GETPRICE(@COMBINATIONID, COMBINATIONPRICETYPE.ID) as FACEPRICE,
                        isnull(SALESORDERITEM.QUANTITY, 0) as QUANTITY
                    from dbo.COMBINATIONPRICETYPE
                    inner join dbo.[COMBINATION] on COMBINATIONPRICETYPE.[COMBINATIONID] = [COMBINATION].[ID]
                    inner join dbo.PRICETYPECODE on PRICETYPECODE.ID = COMBINATIONPRICETYPE.PRICETYPECODEID
                    left join (dbo.SALESORDERITEMTICKETCOMBINATION 
                                inner join dbo.SALESORDERITEMTICKET on SALESORDERITEMTICKETCOMBINATION.ID = SALESORDERITEMTICKET.ID 
                                inner join dbo.SALESORDERITEM on SALESORDERITEMTICKET.ID = SALESORDERITEM.ID) 
                            on [COMBINATIONPRICETYPE].PRICETYPECODEID  = [SALESORDERITEMTICKET].[PRICETYPECODEID]
                                            and [COMBINATION].[ID] = [COMBINATIONPRICETYPE].[COMBINATIONID] 
                                            and SALESORDERITEMTICKETCOMBINATION.TICKETCOMBINATIONID = @TICKETCOMBINATIONID
                    where COMBINATIONPRICETYPE.COMBINATIONID = @COMBINATIONID and
              (SALESORDERITEMTICKETCOMBINATION.TICKETCOMBINATIONID is null or SALESORDERITEMTICKETCOMBINATION.TICKETCOMBINATIONID = @TICKETCOMBINATIONID) and 
              PRICETYPECODE.ACTIVE = 1 and
                            not exists (select 1 
                                        from dbo.SALESMETHODEXCLUDEDPRICETYPE 
                                        where SALESMETHODEXCLUDEDPRICETYPE.SALESMETHODID = @SALESMETHODID and
                                        SALESMETHODEXCLUDEDPRICETYPE.PRICETYPECODEID = COMBINATIONPRICETYPE.PRICETYPECODEID)
                    order by COMBINATIONPRICETYPE.SEQUENCE asc
                    for xml raw ('ITEM'), type, elements, root('PRICETYPES'), BINARY BASE64
                )         

                set @PROGRAMGROUPS = (
                    select ID, NAME, IDSETREGISTERID as SELECTIONID
                    from dbo.PROGRAMGROUP where PROGRAMGROUP.COMBINATIONID = @COMBINATIONID
                    for xml raw('ITEM'),type,elements,root('PROGRAMGROUPS'),BINARY BASE64
                )

                set @CURRENTDATE = sysdatetimeoffset();

                --If events in the past are in the cart, we'll need to show the past events in the event selection form
                if exists (
                    select 1
                    from dbo.[EVENTSALESMETHOD]
                    inner join dbo.[EVENT] as [EVENT] on [EVENTSALESMETHOD].[EVENTID] = [EVENT].[ID]
                    inner join @EVENTS.nodes('/EVENTS/ITEM') T(items) on [EVENT].[ID] = T.items.value('(ID)[1]','uniqueidentifier')
                    inner join dbo.[PROGRAMSALESMETHOD] on EVENT.[PROGRAMID] = [PROGRAMSALESMETHOD].[PROGRAMID]
                    where
                          @CURRENTDATE > case [PROGRAMSALESMETHOD].[ONSALEENDTYPECODE] 
                                when 0 then convert(datetime, [EVENT].[STARTDATETIMEWITHOFFSET])
                                when 1 then convert(datetime, dateadd(mi, -1 * [PROGRAMSALESMETHOD].[ONSALEENDINTERVAL], [EVENT].[STARTDATETIMEWITHOFFSET]))
                                when 2 then convert(datetime, dateadd(hh, -1 * [PROGRAMSALESMETHOD].[ONSALEENDINTERVAL], [EVENT].[STARTDATETIMEWITHOFFSET]))
                                when 3 then convert(datetime, dateadd(mi, [PROGRAMSALESMETHOD].[ONSALEENDINTERVAL], [EVENT].[STARTDATETIMEWITHOFFSET]))
                            end
                )
                    set @ALLOWPASTEVENTS = 1
                else
                    set @ALLOWPASTEVENTS = 0

                return 0;