USP_DATALIST_PROGRAMBYDATEBYSALESMETHOD

Lists all programs filtered by date and sales method.
Parameters

Parameter	Parameter Type	Mode	Description
@STARTDATE	datetime	IN
@ENDDATE	datetime	IN
@SALESMETHODTYPECODE	tinyint	IN	On-sale via specified sales method
@PROGRAMCATEGORYCODEID	uniqueidentifier	IN
@INCLUDEPREREGISTERED	bit	IN
Definition

Copy


            CREATE procedure dbo.USP_DATALIST_PROGRAMBYDATEBYSALESMETHOD
            (
                @STARTDATE datetime = null,
                @ENDDATE datetime = null,
                @SALESMETHODTYPECODE tinyint,
                @PROGRAMCATEGORYCODEID uniqueidentifier = null,
                @INCLUDEPREREGISTERED bit = 0
            )
            as
                set nocount on;    

                /* begin preparing parameters for UFN_SALESORDER_PROGRAMTICKETSELECT */    
                    declare @SALESMETHODID uniqueidentifier 
                    set @SALESMETHODID = [dbo].[UFN_SALESMETHOD_GETIDFROMTYPECODE] (@SALESMETHODTYPECODE)

                    --set @NAME = '%' + replace(replace(@NAME, '*', '%'), '?', '_') + '%';


                    declare @CURRENTDATETIMEOFFSET datetimeoffset = dbo.UFN_TIMEZONEENTRY_GETSYSTEMDEFAULTDATEWITHTIMEOFFSET(sysutcdatetime(), 1)

                    declare @STARTDATETIMEOFFSET as datetimeoffset = TODATETIMEOFFSET(@STARTDATE, DATEPART(tz, SYSDATETIMEOFFSET()))
                    declare @ENDDATETIMEOFFSET as datetimeoffset = TODATETIMEOFFSET(@ENDDATE, DATEPART(tz, SYSDATETIMEOFFSET()))

                    set @STARTDATETIMEOFFSET = dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATETIMEOFFSET)
                    set @ENDDATETIMEOFFSET = dbo.UFN_DATE_GETLATESTTIME(@ENDDATETIMEOFFSET)

                    --if the start date is before now, bring the start date forward to now

                    if (@STARTDATETIMEOFFSET < @CURRENTDATETIMEOFFSET)
                        set @STARTDATETIMEOFFSET = @CURRENTDATETIMEOFFSET;
                /* end preparing parameters for UFN_SALESORDER_PROGRAMTICKETSELECT */                    

                select 
                    ID,
                    NAME,
                    PUBLICDESCRIPTIONHTML,
                    ISPREREGISTERED
                from dbo.PROGRAM
                where (ISACTIVE = 1)
                    and ((PROGRAMCATEGORYCODEID = @PROGRAMCATEGORYCODEID) or (@PROGRAMCATEGORYCODEID IS NULL))
                    and exists (select PROGRAMID from [dbo].[UFN_SALESORDER_PROGRAMTICKETSELECT_DATETIMEOFFSET] (@SALESMETHODID, NULL, 4, @STARTDATETIMEOFFSET, @ENDDATETIMEOFFSET, NULL, 0, @CURRENTDATETIMEOFFSET) where PROGRAMID = PROGRAM.ID)
                    and (ISDAILYADMISSION = 0)    
                    and ((ISPREREGISTERED = 0) or (ISPREREGISTERED = @INCLUDEPREREGISTERED))