UFN_PROGRAMEVENT_AVAILABILITYONDATE_DATETIMEOFFSET

Returns program event availability on a specific date with time zone awareness..
Return

Return Type
table
Parameters

Parameter	Parameter Type	Mode
@PROGRAMID	uniqueidentifier	IN
@DATEWITHTIMEOFFSET	datetimeoffset	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_DATETIMEOFFSET
                (
                    @PROGRAMID uniqueidentifier,
                    @DATEWITHTIMEOFFSET datetimeoffset,
                    @EVENTDATE date,
                    @SALESMETHODID uniqueidentifier,
                    @FILTERNOTONSALE bit = 1, --No longer used

                    @FILTERNOCAPACITY bit = 1, --No longer used

                    @INCLUDEFUTUREEVENTS bit = 0,
                    @MAXROWS integer = 0,
                    @PRICETYPECODEID uniqueidentifier
                )
                returns table
                as return 
                    select top(@MAXROWS)
                        [EVENT].[ID] as [EVENTID], 
                        [CAPACITY].[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 @DATEWITHTIMEOFFSET < [EVENTSALESMETHOD].[ONSALEDATETIMEWITHOFFSET] then 0
                                when [PROGRAMSALESMETHOD].[ID] is null and @DATEWITHTIMEOFFSET >= [EVENT].[STARTDATETIMEWITHOFFSET] then 0
                                when 
                                        @DATEWITHTIMEOFFSET >= case [PROGRAMSALESMETHOD].[ONSALEENDTYPECODE] 
                                            when 0 then [EVENT].[STARTDATETIMEWITHOFFSET]
                                            when 1 then dateadd(mi, -1 * [PROGRAMSALESMETHOD].[ONSALEENDINTERVAL], [EVENT].[STARTDATETIMEWITHOFFSET])
                                            when 2 then dateadd(hh, -1 * [PROGRAMSALESMETHOD].[ONSALEENDINTERVAL], [EVENT].[STARTDATETIMEWITHOFFSET])
                                            when 3 then dateadd(mi, [PROGRAMSALESMETHOD].[ONSALEENDINTERVAL], [EVENT].[STARTDATETIMEWITHOFFSET])
                                        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]
                    cross apply (
                        select dbo.UFN_EVENT_GETAVAILABILITY([EVENT].[ID]) as [AVAILABILITY]
                    ) as [CAPACITY]
                    where
                        [PROGRAM].[ISACTIVE] = 1 and
                        (
                            (@INCLUDEFUTUREEVENTS = 0 and [EVENT].[STARTDATE] = @EVENTDATE) or
                            (@INCLUDEFUTUREEVENTS = 1 and [EVENT].[STARTDATE] >= @EVENTDATE)
                        ) and
                        [EVENT].[PROGRAMID] = @PROGRAMID 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
                        [ONSALE].[ISONSALE] desc,
                        case [CAPACITY].[AVAILABILITY]
                            when 0 then
                                0
                            else
                                1
                        end desc,
                        [EVENT].[STARTDATE],
                        [EVENT].[STARTTIME];