UFN_ONLINEPROGRAMEVENTONSALE_BYPROGRAM_CORE_2

create function dbo.UFN_ONLINEPROGRAMEVENTONSALE_BYPROGRAM_CORE_2(
    @PROGRAMID uniqueidentifier = null,
    @STARTDATETIME datetime = null,
    @ENDDATETIME datetime = null,
    @NAME nvarchar(150) = null,
    @EXCLUDEDATE date = null,
    @LIMITROWS int = 0,
    @MAXROWS int = 0,
    @EXCLUDEOFFSALE bit = 0
)
returns @result table (
    [ID] uniqueidentifier,
    [NAME] nvarchar(100),
    [STARTDATE] date,
    [STARTTIME] dbo.UDT_HOURMINUTE,
    [ENDTIME] dbo.UDT_HOURMINUTE,
    [URL] nvarchar(1024)
)
with execute as caller
as begin
    declare @SALESMETHODID uniqueidentifier = [dbo].[UFN_SALESMETHOD_GETIDFROMTYPECODE] (2)
    declare @BBNCURL nvarchar(1024) = replace(dbo.UFN_BBNC_URL(), 'http://', 'https://');

    declare @PROGRAMHASPRICES bit = 0
    select @PROGRAMHASPRICES = case --If program has prices, then event will.  If not, we have to check that event has custom prices.

        when exists (select 1 from dbo.[PROGRAMPRICE] where [PROGRAMID] = @PROGRAMID) then 1
        else 0
    end

    --preserving original functionality of USP_DATALIST_ONLINEPROGRAMEVENTONSALE_BYPROGRAM while letting the ..._NOMAX version work

    if ISNULL(@LIMITROWS, 0) = 0 set @MAXROWS = 2147483647
    if @LIMITROWS > 0 and @MAXROWS > @LIMITROWS
        set @MAXROWS = @LIMITROWS

    declare @CURRENTDATETIMEWITHOFFSET datetimeoffset = dbo.UFN_TIMEZONEENTRY_GETSYSTEMDEFAULTDATEWITHTIMEOFFSET(sysutcdatetime(), 1)
    declare @STARTDATETIMEWITHOFFSET as datetimeoffset = TODATETIMEOFFSET(@STARTDATETIME, DATEPART(tz, SYSDATETIMEOFFSET()))
    declare @ENDDATETIMEWITHOFFSET as datetimeoffset = TODATETIMEOFFSET(@ENDDATETIME, DATEPART(tz, SYSDATETIMEOFFSET()))

    declare @CURRENTDATE date = @CURRENTDATETIMEWITHOFFSET

    --Do not show undeliverable events

    declare @HASUNRESTRICTEDDELIVERY bit = 0
    declare @EARLIESTVALIDEVENTDATETIMEWITHOFFSET datetimeoffset = null
    select @HASUNRESTRICTEDDELIVERY = [HASUNRESTRICTEDDELIVERY], @EARLIESTVALIDEVENTDATETIMEWITHOFFSET = [EARLIESTVALIDEVENTDATETIMEWITHOFFSET]
    from dbo.UFN_DELIVERYMETHOD_INFO_BYSALESMETHODID(@SALESMETHODID)

    insert into @result
    select top(@MAXROWS)
        [EVENT].[ID], 
        [EVENT].[NAME],
        [EVENT].[STARTDATE],
        [EVENT].[STARTTIME],
        [EVENT].[ENDTIME],
        [ONLINEINFO].[URL]
    from dbo.[PROGRAM]
    inner join dbo.[EVENT]
        on 
            [PROGRAM].[ID] = @PROGRAMID and
            [PROGRAM].[ID] = [EVENT].[PROGRAMID] and
            [PROGRAM].[ISACTIVE] = 1 and
            [EVENT].[STARTDATETIMEWITHOFFSET] >= @STARTDATETIMEWITHOFFSET and 
            [EVENT].[STARTDATETIMEWITHOFFSET] <= @ENDDATETIMEWITHOFFSET and
            (@EXCLUDEDATE is null or [EVENT].[STARTDATE] <> @EXCLUDEDATE) and 
            (@NAME is null or [EVENT].[NAME] like @NAME escape '\')
    left join dbo.[MICROSITEPAGE]
        on [EVENT].[ID] = [MICROSITEPAGE].[OBJECTID]
    left join dbo.VanityUrl
        on MICROSITEPAGE.SITEPAGESID = VanityURL.PageID
    outer apply (
        select 
            case when MICROSITEPAGE.SITEPAGESID is not null then 1 else 0 end as [ISAPPROVED],
            @BBNCURL + [VanityURL].[VanityURL] as [URL]
        where 
            MICROSITEPAGE.EXCLUDED = 0 and
            PROGRAM.ISPREREGISTERED = 1 --Only need to return URLs for preregistered events

    ) as [ONLINEINFO]
    inner join dbo.[EVENTSALESMETHOD]
        on 
            [EVENT].[ID] = [EVENTSALESMETHOD].[EVENTID] and 
            [EVENTSALESMETHOD].[SALESMETHODID] = @SALESMETHODID and
            [EVENTSALESMETHOD].[ONSALEDATETIMEWITHOFFSET] <= @CURRENTDATETIMEWITHOFFSET and
            ([PROGRAM].[ISPREREGISTERED] = 0 or [ONLINEINFO].[ISAPPROVED] = 1)
    left join dbo.[PROGRAMSALESMETHOD]
        on 
            [EVENT].[PROGRAMID] = [PROGRAMSALESMETHOD].[PROGRAMID] and 
            [PROGRAMSALESMETHOD].[SALESMETHODID] = @SALESMETHODID
    cross apply (
        select
            case 
                when [PROGRAMSALESMETHOD].[ONSALEENDTYPECODE] is not null then
                    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
                else [EVENT].[STARTDATETIMEWITHOFFSET]
            end as [TIME]
    ) [EVENTOFFSALESDATETIMEWITHOFFSET]
    where
        (
            @PROGRAMHASPRICES = 1 or
            exists (
                select 1
                from dbo.[PROGRAMEVENTPRICE]
                where [EVENTID] = [EVENT].[ID]
            )
        ) and
        (
            @HASUNRESTRICTEDDELIVERY = 1 or
            ([EVENT].[STARTDATETIMEWITHOFFSET] > @EARLIESTVALIDEVENTDATETIMEWITHOFFSET)
        ) and
        (
            @EXCLUDEOFFSALE = 0 or
            [EVENTOFFSALESDATETIMEWITHOFFSET].[TIME] > @CURRENTDATETIMEWITHOFFSET
        )
    order by [EVENT].[STARTDATE], [EVENT].[STARTTIME], [EVENT].[ENDTIME]

    return
end