UFN_SALESORDER_PROGRAMTICKETSELECT

        CREATE function dbo.UFN_SALESORDER_PROGRAMTICKETSELECT
            (
            @SALESMETHODID uniqueidentifier = null,
            @CONSTITUENTID uniqueidentifier = null,
            @DATESELECTTYPE tinyint = null,
            @STARTDATE datetime = null,
            @ENDDATE datetime = null,
            @NAME nvarchar(512) = null,
            @SHOWPAST bit = null,
            @CURRENTDATE datetime = null
            )
        returns table
        as return

            --Combo

            select 
              [COMBINATION].[ID], 
              null [PROGRAMID],
              [COMBINATION].[NAME] as [NAME],
              null as [AVAILABILITY],
              null as [STARTDATE],
              null as [STARTTIME], 
              null as [ENDTIME],
              null as PROGRAMCATEGORY,                    
              0 as [ISDAILYADMISSION],
              1 as [ISCOMBINATION],
              0 as [EVENTSTATUSCODE]
            from dbo.[COMBINATION]
            where
              -- Combination level check

              [COMBINATION].[ISACTIVE] = 1 and
              dbo.UFN_COMBINATION_AVAILABLEFORORDER(COMBINATION.ID, @SALESMETHODID) = 1 and
              -- Combination eligibility 

              dbo.UFN_COMBINATION_ELIGIBLEFORORDER(COMBINATION.ID, @CONSTITUENTID) = 1 and
              -- Combination has price options

              exists (
                select [COMBINATIONPRICETYPE].[ID]
                from dbo.[COMBINATIONPRICETYPE]
                where [COMBINATIONPRICETYPE].[COMBINATIONID] = [COMBINATION].[ID] 
              ) and
              -- Combination Name filter

              (
                @NAME is null or
                [COMBINATION].[NAME] like @NAME escape '\'
              ) and
              -- Program group level check

              -- Each program group has at least one available daily admission program or event

              dbo.UFN_COMBINATION_PROGRAMSAVAILABLE(COMBINATION.ID, @SALESMETHODID, @DATESELECTTYPE, @STARTDATE, @ENDDATE, @SHOWPAST) = 1

            union all

            --Daily Admission

            select 
                [PROGRAM].[ID], 
                null as [PROGRAMID],
                [PROGRAM].[NAME] as [NAME],
                null as [AVAILABILITY],
                null as [STARTDATE],
                null as [STARTTIME], 
                null as [ENDTIME],
                [PROGRAMCATEGORYCODE].[DESCRIPTION] as PROGRAMCATEGORY,                    
                [ISDAILYADMISSION],
                0 as [ISCOMBINATION],
                0 as [EVENTSTATUSCODE]
            from dbo.[PROGRAM]
            inner join dbo.[PROGRAMSALESMETHOD] on [PROGRAM].[ID] = [PROGRAMSALESMETHOD].[PROGRAMID]
            left join dbo.[PROGRAMCATEGORYCODE] on [PROGRAM].[PROGRAMCATEGORYCODEID] = [PROGRAMCATEGORYCODE].[ID]
            where 
                (
                    @NAME is null or
                    [PROGRAM].[NAME] like @NAME escape '\'
                ) and
                [PROGRAM].[ISACTIVE] = 1 and
                [PROGRAM].[ISDAILYADMISSION] = 1 and
                [PROGRAMSALESMETHOD].[SALESMETHODID] = @SALESMETHODID
                and exists ( --Has price options

                    select [PROGRAMPRICE].[ID]
                    from dbo.[PROGRAMPRICE]
                    where [PROGRAMID] = [PROGRAM].[ID]
                ) and
                (
                    @DATESELECTTYPE = 0 or
                    @STARTDATE <= @ENDDATE
                )

            union all 
            --Scheduled programs

            select 
                [EVENT].[ID], 
                [EVENT].[PROGRAMID] as [PROGRAMID],
                [EVENT].[NAME],
                AVAILABLE.QUANTITY as [AVAILABILITY],
                [EVENT].[STARTDATE],
                [EVENT].[STARTTIME], 
                [EVENT].[ENDTIME],
                [PROGRAMCATEGORYCODE].[DESCRIPTION] as PROGRAMCATEGORY,                    
                [ISDAILYADMISSION],
                0 as [ISCOMBINATION],
                case 
                    when [PROGRAMSALESMETHOD].[ID] is null and @CURRENTDATE = [EVENT].[STARTDATETIME] then 1
                    when @CURRENTDATE between [EVENT].[STARTDATETIME] and case [PROGRAMSALESMETHOD].[ONSALEENDTYPECODE] 
                                            when 0 then [EVENT].[STARTDATETIME]
                                            when 1 then dateadd(mi, -1 * [PROGRAMSALESMETHOD].[ONSALEENDINTERVAL], [EVENT].[STARTDATETIME])
                                            when 2 then dateadd(hh, -1 * [PROGRAMSALESMETHOD].[ONSALEENDINTERVAL], [EVENT].[STARTDATETIME])
                                            when 3 then dateadd(mi, [PROGRAMSALESMETHOD].[ONSALEENDINTERVAL], [EVENT].[STARTDATETIME])
                                        end then 1
                    when [PROGRAMSALESMETHOD].[ID] is null and @CURRENTDATE >= [EVENT].[STARTDATETIME] then 2
                    when @CURRENTDATE >= case [PROGRAMSALESMETHOD].[ONSALEENDTYPECODE] 
                                            when 0 then [EVENT].[STARTDATETIME]
                                            when 1 then dateadd(mi, -1 * [PROGRAMSALESMETHOD].[ONSALEENDINTERVAL], [EVENT].[STARTDATETIME])
                                            when 2 then dateadd(hh, -1 * [PROGRAMSALESMETHOD].[ONSALEENDINTERVAL], [EVENT].[STARTDATETIME])
                                            when 3 then dateadd(mi, [PROGRAMSALESMETHOD].[ONSALEENDINTERVAL], [EVENT].[STARTDATETIME])
                                        end
                         then 2
                    else 0
                end as [EVENTSTATUSCODE]
            from dbo.[EVENT] 
            inner join dbo.EVENTSALESMETHOD on [EVENT].ID = EVENTSALESMETHOD.EVENTID 
            left join dbo.PROGRAMSALESMETHOD 
                on 
                    [EVENT].PROGRAMID = PROGRAMSALESMETHOD.PROGRAMID and 
                    [PROGRAMSALESMETHOD].[SALESMETHODID] = @SALESMETHODID
            inner join dbo.UFN_EVENT_GETAVAILABILITYWITHEVENTS() as AVAILABLE on EVENT.ID = AVAILABLE.EVENTID
            inner join dbo.[PROGRAM] on [EVENT].[PROGRAMID] = [PROGRAM].[ID]
            left join dbo.[PROGRAMCATEGORYCODE] on [PROGRAM].[PROGRAMCATEGORYCODEID] = [PROGRAMCATEGORYCODE].[ID]
            where
                [PROGRAM].[ISACTIVE] = 1 
                and [EVENTSALESMETHOD].[SALESMETHODID] = @SALESMETHODID 
                and @CURRENTDATE >= [EVENTSALESMETHOD].[ONSALEDATETIME]
                and
                (
                    @NAME is null or
                    [EVENT].[NAME] like @NAME escape '\' or
                    [PROGRAM].[NAME] like @NAME escape '\'
                ) and
                ( 
                    (@DATESELECTTYPE = 0 and @SHOWPAST = 1) 
                    or
                        ((@DATESELECTTYPE = 0 and [EVENT].[STARTDATETIME] >= @CURRENTDATE) 
                        or (@DATESELECTTYPE <> 0 and 
                            (
                            ([PROGRAMSALESMETHOD].[ID] is null and [EVENT].[STARTDATETIME] between @STARTDATE and @ENDDATE) or
                            case [PROGRAMSALESMETHOD].[ONSALEENDTYPECODE]
                                            when 0 then [EVENT].[STARTDATETIME]
                                            when 1 then dateadd(mi, -1 * [PROGRAMSALESMETHOD].[ONSALEENDINTERVAL], [EVENT].[STARTDATETIME])
                                            when 2 then dateadd(hh, -1 * [PROGRAMSALESMETHOD].[ONSALEENDINTERVAL], [EVENT].[STARTDATETIME])
                                            when 3 then dateadd(mi, [PROGRAMSALESMETHOD].[ONSALEENDINTERVAL], [EVENT].[STARTDATETIME])
                                        end between @STARTDATE and @ENDDATE or 
                            (@STARTDATE between [EVENT].[STARTDATETIME] and case [PROGRAMSALESMETHOD].[ONSALEENDTYPECODE] 
                                            when 0 then [EVENT].[STARTDATETIME]
                                            when 1 then dateadd(mi, -1 * [PROGRAMSALESMETHOD].[ONSALEENDINTERVAL], [EVENT].[STARTDATETIME])
                                            when 2 then dateadd(hh, -1 * [PROGRAMSALESMETHOD].[ONSALEENDINTERVAL], [EVENT].[STARTDATETIME])
                                            when 3 then dateadd(mi, [PROGRAMSALESMETHOD].[ONSALEENDINTERVAL], [EVENT].[STARTDATETIME])

                                        end))))
                            --Currently happening events should be shown even when our startdate parameter is just within the event's start and end times

                ) 
                and
                exists ( --Has price options

                    select [PROGRAMPRICE].[ID]
                    from dbo.[PROGRAMPRICE]
                    where [PROGRAMID] = [EVENT].[PROGRAMID]
                )