USP_DATALIST_EVENTTICKETSELECT

                CREATE procedure dbo.USP_DATALIST_EVENTTICKETSELECT
                (
                    @SALESMETHODTYPECODE tinyint,
                    @CONSTITUENTID uniqueidentifier = null,
                    @DATESELECTTYPE tinyint = null,
                    @STARTDATE datetime = null,
                    @ENDDATE datetime = null,
                    @NAME nvarchar(512) = null,
                    @SHOWPAST bit = null
                )
                as
                set nocount on;

                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()))

                if @DATESELECTTYPE is null
                    set @DATESELECTTYPE = 1;

                set @STARTDATETIMEOFFSET = case @DATESELECTTYPE
                    when 1 then dbo.UFN_DATE_GETEARLIESTTIME(@CURRENTDATETIMEOFFSET)
                    when 2 then dbo.UFN_DATE_GETEARLIESTTIME(@CURRENTDATETIMEOFFSET)
                    when 3 then dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATETIMEOFFSET)
                    when 4 then dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATETIMEOFFSET)
                end;

                set @ENDDATETIMEOFFSET = case @DATESELECTTYPE
                    when 1 then dbo.UFN_DATE_GETLATESTTIME(@CURRENTDATETIMEOFFSET)
                    when 2 then dbo.UFN_DATE_GETLATESTTIME(dateadd(week,1, @CURRENTDATETIMEOFFSET))
                    when 3 then dbo.UFN_DATE_GETLATESTTIME(@STARTDATETIMEOFFSET)
                    when 4 then dbo.UFN_DATE_GETLATESTTIME(@ENDDATETIMEOFFSET)
                end;

                --If past events shouldn't be shown and the start date is before now, fix start date to now

                if (@SHOWPAST = 0 or @SHOWPAST is null) and (@STARTDATETIMEOFFSET < @CURRENTDATETIMEOFFSET)
                    set @STARTDATETIMEOFFSET = @CURRENTDATETIMEOFFSET;

                --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 
                  [ID], 
                  [PROGRAMID],
                  [NAME],
                  [AVAILABILITY],
                  [STARTDATE],
                  [STARTTIME], 
                  [ENDTIME],
                  [PROGRAMCATEGORY],                    
                  [ISDAILYADMISSION],
                  [ISCOMBINATION],
                  [EVENTSTATUSCODE]
                from dbo.UFN_SALESORDER_PROGRAMTICKETSELECT_DATETIMEOFFSET
                      (
                        @SALESMETHODID,
                        @CONSTITUENTID,
                        @DATESELECTTYPE,
                        @STARTDATETIMEOFFSET,
                        @ENDDATETIMEOFFSET,
                        @NAME,
                        @SHOWPAST,
                        @CURRENTDATETIMEOFFSET
                      ) 
                where 
                    @SALESMETHODTYPECODE <> 2 or
                    @HASUNRESTRICTEDDELIVERYMETHOD = 1 or
                    (([ISCOMBINATION] = 1 or [ISDAILYADMISSION] = 1) and @HASDELIVERYMETHOD = 1) or
                    ([EVENTSTARTDATETIMEWITHOFFSET] > @EARLIESTVALIDEVENTDATETIMEWITHOFFSET)