USP_DATALIST_MICROSITE_EVENTS

                CREATE procedure dbo.USP_DATALIST_MICROSITE_EVENTS(
                    @ISLIVE bit = 0,
                    @NAME nvarchar(100) = '',
                    @DATESELECTTYPE tinyint = 2,
                    @STARTDATE datetime = null,
                    @ENDDATE datetime = null
                )
                as
                    set nocount on;

                    set @NAME = '%' + isnull(replace(replace(@NAME, '*', '%'), '?', '_'),'') + '%';
                    declare @BBNCURL nvarchar(1024) = dbo.UFN_BBNC_URL();

                    if @DATESELECTTYPE is null
                        set @DATESELECTTYPE = 2;

                    declare @CURRENTDATE date = getdate()

                    set @STARTDATE = case @DATESELECTTYPE
                        when 1 then @CURRENTDATE
                        when 2 then @CURRENTDATE
                        when 3 then dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE)
                        when 4 then dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE)
                        when 5 then dbo.UFN_DATE_GETEARLIESTTIME(@CURRENTDATE)
                    end

                    set @ENDDATE = case @DATESELECTTYPE
                        when 1 then dbo.UFN_DATE_GETLATESTTIME(dateadd(day, 10, @CURRENTDATE))
                        when 2 then dbo.UFN_DATE_GETLATESTTIME(dateadd(day, 30, @CURRENTDATE))
                        when 3 then dbo.UFN_DATE_GETLATESTTIME(@ENDDATE)
                        when 4 then dbo.UFN_DATE_GETLATESTTIME(@STARTDATE)
                        when 5 then dbo.UFN_DATE_GETLATESTTIME(@CURRENTDATE)
                    end

                    declare @CURRENTDATETIMEOFFSET datetimeoffset = dbo.UFN_TIMEZONEENTRY_GETSYSTEMDEFAULTDATEWITHTIMEOFFSET(sysutcdatetime(), 1);

                    declare @EVENTS table (
                        ID uniqueidentifier,
                        NAME nvarchar(100),
                        STARTDATE datetime,
                        STARTTIME dbo.UDT_HOURMINUTE,
                        ISLIVE bit,
                        ISACTIVE bit,
                        ISAPPROVED bit,
                        PRIMARYCONTENTID int,
                        PROGRAMID uniqueidentifier,
                        EVENT_MICROSITEEMAILTEMPLATEID uniqueidentifier,
                        HASACTIVEEMAILTEMPLATE bit
                    )

                    insert into @EVENTS
                    select 
                        [EVENT].[ID],
                        [EVENT].[NAME],
                        [EVENT].[STARTDATE],
                        [EVENT].[STARTTIME],
                        [LIVESTATUS].[ISLIVE],
                        [ACTIVE].[IS] as [ISACTIVE],
                        [ONLINEINFO].[ISAPPROVED],
                        [ONLINEINFO].[PRIMARYCONTENTID],
                        [EVENT].[PROGRAMID],
                        [EVENT_MICROSITEEMAILTEMPLATE].[ID] as [EVENT_MICROSITEEMAILTEMPLATEID],
                        [EVENT_MICROSITEEMAILTEMPLATE].[ACTIVE] as [HASACTIVEEMAILTEMPLATE]
                    from dbo.[EVENT]
                    left join dbo.[EVENTMANAGEMENTOPTIONS]
                        on EVENTMANAGEMENTOPTIONS.EVENTID = EVENT.ID
                    left join dbo.[PROGRAM]
                        on [EVENT].[PROGRAMID] = [PROGRAM].[ID]
                    left outer join dbo.[EVENT_MICROSITEEMAILTEMPLATE]
                        on [EVENT].[ID] = [EVENT_MICROSITEEMAILTEMPLATE].[EVENTID]
                    outer apply (
                        select 
                            case when MICROSITEPAGE.SITEPAGESID is not null then 1 else 0 end as [ISAPPROVED],
                            MICROSITEPAGE.PRIMARYCONTENTID as [PRIMARYCONTENTID],
                            @BBNCURL + [VanityURL].[VanityURL] as [URL]
                        from dbo.[MICROSITEPAGE]
                        inner join dbo.VanityURL on
                            MICROSITEPAGE.SITEPAGESID = VanityURL.PageID
                        where MICROSITEPAGE.OBJECTID = EVENT.ID and MICROSITEPAGE.EXCLUDED = 0
                    ) as [ONLINEINFO]
                    cross apply (
                        select case 
                            when [PROGRAM].[ID] is null then [EVENT].[ISACTIVE]
                            else [PROGRAM].[ISACTIVE]
                        end as [IS]
                    ) [ACTIVE]
                    cross apply (
                        select case when [ONLINEINFO].[ISAPPROVED] = 1 and [ACTIVE].[IS] = 1 then 1 else 0 end as [ISLIVE]
                    ) [LIVESTATUS]
                    where 
                        --Date filter

                        (
                            @DATESELECTTYPE = 0 or
                            [EVENT].[STARTDATE] between @STARTDATE and @ENDDATE
                        ) and
                        --Name filter

                        [EVENT].[NAME] like @NAME escape '/' and
                        --IsLive filter

                        (
                            @ISLIVE = 0 or
                            [LIVESTATUS].[ISLIVE] = 1
                        ) and
                        (
                            --Special events

                            (
                                [EVENT].[PROGRAMID] is null and
                                --Not multi-level

                                [EVENT].[MAINEVENTID] is null and
                                not exists(select 1 from dbo.[EVENT] as [E] where [E].[MAINEVENTID] = [EVENT].[ID]) and
                                [EVENTMANAGEMENTOPTIONS].[ID] is null
                            ) or
                            --Pre-registered

                            (
                                [PROGRAM].[ID] is not null and
                                [PROGRAM].[ISPREREGISTERED] = 1
                            )
                        )
                    order by 
                        coalesce([PROGRAM].[ISPREREGISTERED], 0) asc, 
                        [PROGRAM].[ID], 
                        [EVENT].[NAME],
                        [EVENT].[STARTDATETIME]

                    select
                        ID,
                        NAME,
                        STARTDATE,
                        STARTTIME,
                        ISLIVE,
                        ISACTIVE,
                        ISAPPROVED,
                        PRIMARYCONTENTID,
                        PROGRAMID,
                        'cfff2e8d-8f1b-481b-8beb-d52271095e83' as DETAILVIEWID,
                        1 as ISEVENT,
                        EVENT_MICROSITEEMAILTEMPLATEID as MICROSITEEMAILTEMPLATEID,
                        coalesce(HASACTIVEEMAILTEMPLATE, 0) as HASACTIVEACKNOWLEDGEMENTEMAIL
                    from @EVENTS

                    union all
                    select
                        ID,
                        NAME,
                        null STARTDATE,
                        null STARTTIME,
                        1 as ISLIVE,
                        ISACTIVE,
                        null ISAPPROVED,
                        null PRIMARYCONTENTID,
                        null as PROGRAMID,
                        'bfd26069-fbe5-4e65-8654-d203ad90e303' as DETAILVIEWID,
                        0 as ISEVENT,
                        MICROSITEEMAILTEMPLATEID,
                        coalesce(HASACTIVEEMAILTEMPLATE , 0) as HASACTIVEACKNOWLEDGEMENTEMAIL
                    from (
                        select top 1000 --Need to specify a limit to use 'order by'

                            [PROGRAM].ID,
                            [PROGRAM].NAME,
                            [PROGRAM].ISACTIVE,
                            [PROGRAM_MICROSITEEMAILTEMPLATE].[ID] as [MICROSITEEMAILTEMPLATEID],
                            [PROGRAM_MICROSITEEMAILTEMPLATE].[ACTIVE] as HASACTIVEEMAILTEMPLATE
                        from dbo.[PROGRAM]
                        left outer join dbo.[PROGRAM_MICROSITEEMAILTEMPLATE]
                            on [PROGRAM].[ID] = [PROGRAM_MICROSITEEMAILTEMPLATE].[PROGRAMID]
                        where exists(select 1 from @EVENTS where [PROGRAMID] = [PROGRAM].[ID])
                        order by NAME
                    ) [PROGRAM]