UFN_PROGRAMEVENT_AVAILABILITYONDATE

Returns program event availability on a specific date.
Return

Return Type
table
Parameters

Parameter	Parameter Type	Mode
@PROGRAMID	uniqueidentifier	IN
@DATE	datetime	IN
@EVENTDATE	date	IN
@SALESMETHODID	uniqueidentifier	IN
@FILTERNOTONSALE	bit	IN
@FILTERNOCAPACITY	bit	IN
@INCLUDEFUTUREEVENTS	bit	IN
@MAXROWS	int	IN
@PRICETYPECODEID	uniqueidentifier	IN
Definition

Copy


                CREATE function dbo.UFN_PROGRAMEVENT_AVAILABILITYONDATE
                (
                    @PROGRAMID uniqueidentifier,
                    @DATE datetime,
                    @EVENTDATE date,
                    @SALESMETHODID uniqueidentifier,
                    @FILTERNOTONSALE bit = 1,
                    @FILTERNOCAPACITY bit = 1,
                    @INCLUDEFUTUREEVENTS bit = 0,
                    @MAXROWS integer = 0,
                    @PRICETYPECODEID uniqueidentifier
                )
                returns table
                as return 
                    select top(@MAXROWS)
                        [EVENT].[ID] as [EVENTID],
                        TICKETCOUNTS.AVAILABILITY,
                        [ONSALE].[ISONSALE],
                        [EVENT].[STARTDATE],
                        [EVENT].[STARTTIME],
                        [EVENT].[ENDDATE],
                        [EVENT].[ENDTIME]
                    from dbo.[EVENT] 
                    inner join dbo.[PROGRAM]
                        on [EVENT].[PROGRAMID] = [PROGRAM].[ID]
                    cross apply (
                        select
                            case 
                                when @DATE < [EVENTSALESMETHOD].[ONSALEDATETIME] then 0
                                when [PROGRAMSALESMETHOD].[ID] is null and @DATE >= [EVENT].[STARTDATETIME] then 0
                                when 
                                        @DATE >= 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 0
                                else 1
                            end as [ISONSALE]
                        from dbo.[EVENTSALESMETHOD]
                        left join dbo.[PROGRAMSALESMETHOD]
                            on 
                                [PROGRAM].[ID] = [PROGRAMSALESMETHOD].[PROGRAMID] and
                                [PROGRAMSALESMETHOD].[SALESMETHODID] = @SALESMETHODID
                        where 
                            [EVENTSALESMETHOD].[SALESMETHODID] = @SALESMETHODID and
                            [EVENT].[ID] = [EVENTSALESMETHOD].[EVENTID]
                    ) as [ONSALE]
                    inner join
                        dbo.V_PROGRAMEVENT_TICKETCOUNTS as TICKETCOUNTS on TICKETCOUNTS.ID = EVENT.ID
                    where
                        [PROGRAM].[ISACTIVE] = 1 and
                        (
                            (@INCLUDEFUTUREEVENTS = 0 and [EVENT].[STARTDATE] = @EVENTDATE) or
                            (@INCLUDEFUTUREEVENTS = 1 and [EVENT].[STARTDATE] >= @EVENTDATE)
                        ) and
                        [EVENT].[PROGRAMID] = @PROGRAMID and
                        (@FILTERNOTONSALE = 0 or [ONSALE].[ISONSALE] = 1) and
                        (@FILTERNOCAPACITY = 0 or TICKETCOUNTS.AVAILABILITY > 0) and
                        (
                            @PRICETYPECODEID is null or 
                            exists (
                                select 1
                                from dbo.[PROGRAMPRICE]
                                where 
                                    [PROGRAM].[ID] = [PROGRAMPRICE].[PROGRAMID] and
                                    not exists(select 1 from dbo.[PROGRAMEVENTPRICE] where [EVENTID] = [EVENT].[ID]) and
                                    [PROGRAMPRICE].[PRICETYPECODEID] = @PRICETYPECODEID

                                union all
                                select 1
                                from dbo.[PROGRAMEVENTPRICE]
                                where 
                                    [EVENT].[ID] = [PROGRAMEVENTPRICE].[EVENTID] and
                                    [PROGRAMEVENTPRICE].[PRICETYPECODEID] = @PRICETYPECODEID
                            )
                        )
                    order by 
                        [EVENT].[STARTDATE],
                        [EVENT].[STARTTIME];