USP_DATALIST_PROGRAMEVENTONSALE

Events on sale in the specified date range via the specified sales method. May specify program and/or program category.

Parameters

Parameter	Parameter Type	Mode	Description
@CONTEXTID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@SALESMETHODTYPECODE	tinyint	IN	On-sale via specified sales method
@STARTDATETIME	datetime	IN
@ENDDATETIME	datetime	IN
@PROGRAMCATEGORYCODEID	uniqueidentifier	IN
@INCLUDESOLDOUT	bit	IN
@INCLUDEPREREGISTERED	bit	IN

Definition

Copy



            CREATE procedure dbo.USP_DATALIST_PROGRAMEVENTONSALE
            (
                @CONTEXTID uniqueidentifier = null,
                @SALESMETHODTYPECODE tinyint,
                @STARTDATETIME datetime = null,
                @ENDDATETIME datetime = null,
                @PROGRAMCATEGORYCODEID uniqueidentifier = null,
                @INCLUDESOLDOUT bit = null,
                @INCLUDEPREREGISTERED bit = 0
            )
            as
            set nocount on;

            declare @SALESMETHODID uniqueidentifier 
            set @SALESMETHODID = [dbo].[UFN_SALESMETHOD_GETIDFROMTYPECODE] (@SALESMETHODTYPECODE)

            declare @CURRENTDATETIMEOFFSET datetimeoffset = dbo.UFN_TIMEZONEENTRY_GETSYSTEMDEFAULTDATEWITHTIMEOFFSET(sysutcdatetime(), 1)
            declare @STARTDATETIMEOFFSET as datetimeoffset = TODATETIMEOFFSET(@STARTDATETIME, DATEPART(tz, SYSDATETIMEOFFSET()))
            declare @ENDDATETIMEOFFSET as datetimeoffset = TODATETIMEOFFSET(@ENDDATETIME, DATEPART(tz, SYSDATETIMEOFFSET()))

            --For online orders, we are going to avoid showing undeliverable event tickets

            declare @HASDELIVERYMETHOD bit = 0
            declare @HASUNRESTRICTEDDELIVERYMETHOD bit = 0
            declare @EARLIESTVALIDEVENTDATETIMEWITHOFFSET datetimeoffset = null
            if @SALESMETHODTYPECODE = 2
            begin
                exec dbo.USP_DELIVERYMETHOD_INFO_BYSALESMETHODID 
                    @SALESMETHODID, 
                    @HASDELIVERYMETHOD output, 
                    @HASUNRESTRICTEDDELIVERYMETHOD output, 
                    @EARLIESTVALIDEVENTDATETIMEWITHOFFSET output
            end

            select 
                E.ID,
                E.NAME,
                dbo.UFN_EVENT_GETLOCATIONNAME(E.ID) AS LOCATIONNAME,
                E.AVAILABILITY, --dbo.UFN_EVENT_GETAVAILABILITY(E.ID) AS AVAILABILITY,

                E.STARTDATE,
                E.STARTTIME,
                E.ENDTIME,
                E.PROGRAMID,
                E.PROGRAMCATEGORY,
                E.EVENTSTATUSCODE,
                E.ISCOMBINATION,
                E.ISDAILYADMISSION,
                PROGRAM.ISPREREGISTERED
            from [dbo].[UFN_SALESORDER_PROGRAMTICKETSELECT_DATETIMEOFFSET] (@SALESMETHODID, NULL, 4, @STARTDATETIMEOFFSET, @ENDDATETIMEOFFSET, NULL, 0, @CURRENTDATETIMEOFFSET) as E
            inner join dbo.PROGRAM  
                on PROGRAM.ID = E.PROGRAMID
            where ((PROGRAM.ID = @CONTEXTID) OR (@CONTEXTID = '00000000-0000-0000-0000-000000000000') OR (@CONTEXTID IS NULL))
                and ((PROGRAM.PROGRAMCATEGORYCODEID = @PROGRAMCATEGORYCODEID) or (@PROGRAMCATEGORYCODEID IS NULL))
                and ((@INCLUDESOLDOUT = 1) or (dbo.UFN_EVENT_GETAVAILABILITY(E.ID) > 0))
                and ((ISPREREGISTERED = 0) or (ISPREREGISTERED = @INCLUDEPREREGISTERED)) 
                --and (SALESMETHOD.ISACTIVE = 1)

                and                     
                    (
                        @SALESMETHODTYPECODE <> 2 or
                        @HASUNRESTRICTEDDELIVERYMETHOD = 1 or
                        ((E.[ISCOMBINATION] = 1 or E.[ISDAILYADMISSION] = 1) and @HASDELIVERYMETHOD = 1) or
                        ([EVENTSTARTDATETIMEWITHOFFSET] > @EARLIESTVALIDEVENTDATETIMEWITHOFFSET)
                    )
            ORDER BY STARTDATE, STARTTIME, ENDTIME