USP_DATALIST_MICROSITEPROGRAMMINGEVENTS

CREATE procedure dbo.USP_DATALIST_MICROSITEPROGRAMMINGEVENTS
    (
        @ACTIVEONLY bit = 0,
        @NAMELIKE nvarchar(512) = null,
        @DATESELECTTYPE tinyint = 0,
        @STARTDATE datetime = null,
        @ENDDATE datetime = null
    ) as
    set nocount on;

    --@DATESELECTTYPE

    --2    All dates

    --0    Next 10 days

    --1    Today

    --4    Date range

    --3    Specific date  

    --5    Next 30 days


    if @NAMELIKE = ''
        set @NAMELIKE = null;
    set @NAMELIKE = '%' + replace(replace(@NAMELIKE, '*', '%'), '?', '_') + '%';

    if @ACTIVEONLY is null
        set @ACTIVEONLY = 0

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

       if @DATESELECTTYPE is null set @DATESELECTTYPE = 0;

    set @STARTDATE = case @DATESELECTTYPE
        when 1 then dbo.UFN_DATE_GETEARLIESTTIME(@CURRENTDATETIMEOFFSET)
        when 0 then dbo.UFN_DATE_GETEARLIESTTIME(@CURRENTDATETIMEOFFSET)
        when 3 then dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE)
        when 4 then dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE)
        when 5 then @CURRENTDATETIMEOFFSET
    end;
    if (@STARTDATE < @CURRENTDATETIMEOFFSET) set @STARTDATE = @CURRENTDATETIMEOFFSET;

    set @ENDDATE = case @DATESELECTTYPE
        when 1 then dbo.UFN_DATE_GETLATESTTIME(@CURRENTDATETIMEOFFSET)
        when 0 then dbo.UFN_DATE_GETLATESTTIME(dateadd(DAY, 10, @CURRENTDATETIMEOFFSET))
        when 3 then dbo.UFN_DATE_GETLATESTTIME(@STARTDATE)
        when 4 then dbo.UFN_DATE_GETLATESTTIME(@ENDDATE)
        when 5 then dbo.UFN_DATE_GETLATESTTIME(dateadd(Day, 30, @CURRENTDATETIMEOFFSET))
    end;

    declare @STARTDATETIMEOFFSET as datetimeoffset = dbo.UFN_TIMEZONEENTRY_GETSYSTEMDEFAULTDATEWITHTIMEOFFSET(@STARTDATE, 0);
  declare @ENDDATETIMEOFFSET as datetimeoffset = dbo.UFN_TIMEZONEENTRY_GETSYSTEMDEFAULTDATEWITHTIMEOFFSET(@ENDDATE, 0);        

    declare @ONLINESALESMETHOD uniqueidentifier = dbo.UFN_SALESMETHOD_GETIDFROMTYPECODE(2);--2 = online


    with EVENT_CTE (ID, NAME, STARTDATE, STARTTIME, STARTDATETIME, PARENTID, ISACTIVE, ISDAILYADMISSION, ONLINE, ISLIVE, PROGRAMNAME, EVENT_MICROSITEEMAILTEMPLATEID, HASACTIVEEMAILTEMPLATE) as (
        select 
            E.ID,
            E.NAME,
            E.STARTDATE,
            E.STARTTIME,
            E.STARTDATETIME,
            E.PROGRAMID as PARENTID,
            P.ISACTIVE,
            0 as ISDAILYADMISSION,
            case when EVENTSALESMETHOD.ID is null then 0 else 1 end as ONLINE,
            [LIVESTATUS].[ISLIVE],
            P.NAME,
            EVENT_MICROSITEEMAILTEMPLATE.ID as EVENT_MICROSITEEMAILTEMPLATEID,
            EVENT_MICROSITEEMAILTEMPLATE.ACTIVE as HASACTIVEEMAILTEMPLATE
        from dbo.[EVENT] as E
        inner join dbo.PROGRAM as P
            on 
                (E.PROGRAMID = P.ID) and
                (P.ISPREREGISTERED = 0) and
                (E.ENDDATETIMEWITHOFFSET > @CURRENTDATETIMEOFFSET) and
                (
                    (@DATESELECTTYPE = 2) or 
                    (STARTDATETIMEWITHOFFSET between @STARTDATETIMEOFFSET and @ENDDATETIMEOFFSET) or 
                    (@STARTDATETIMEOFFSET between STARTDATETIMEWITHOFFSET and ENDDATETIMEWITHOFFSET)
                ) and
                (@NAMELIKE is null or E.NAME like @NAMELIKE escape '\')
        left outer join dbo.EVENTSALESMETHOD
            on 
                (E.ID = EVENTSALESMETHOD.EVENTID) and 
                (EVENTSALESMETHOD.SALESMETHODID = @ONLINESALESMETHOD )
        left outer join dbo.EVENT_MICROSITEEMAILTEMPLATE
            on E.ID = EVENT_MICROSITEEMAILTEMPLATE.EVENTID
        cross apply (
            select case 
                when 
                        (EVENTSALESMETHOD.SALESMETHODID = @ONLINESALESMETHOD)--(@CURRENTDATETIMEOFFSET between EVENTSALESMETHOD.ONSALEDATETIMEWITHOFFSET and E.ENDDATETIMEWITHOFFSET)

                        and (P.ISACTIVE = 1) -- observe this

                    and (E.ISACTIVE = 1) -- future-proofing

                    then 1
                else 0
            end as ISLIVE
        ) as [LIVESTATUS]
        where (@ACTIVEONLY = 0 or [LIVESTATUS].[ISLIVE] = 1)
    )            

    select 
        PROGRAM.ID as ID,
        PROGRAM.NAME,
        null as STARTDATE,
        null as STARTTIME,
        null as STARTDATETIME,
        null as PARENTID,
        PROGRAM.ISACTIVE,
        PROGRAM.ISDAILYADMISSION,
        case when PROGRAMSALESMETHOD.ID is null then 0 else 1 end as ONLINE,
          case
            when PROGRAM.ISDAILYADMISSION = 0 then 
                case 
                    when exists (select 1 from EVENT_CTE where (PROGRAM.ID = EVENT_CTE.PARENTID) and (EVENT_CTE.ISLIVE = 1))
                        then PROGRAM.ISACTIVE
                    else 0
                end
            else case when PROGRAMSALESMETHOD.ID is null then 0 else PROGRAM.ISACTIVE end
        end as ISLIVE,
        PROGRAM.NAME as PROGRAMNAME,
        PROGRAM_MICROSITEEMAILTEMPLATE.ID as MICROSITEEMAILTEMPLATEID,
        coalesce(PROGRAM_MICROSITEEMAILTEMPLATE.ACTIVE, 0) as HASACTIVEEMAILTEMPLATE
    from dbo.PROGRAM
    left outer join dbo.PROGRAMSALESMETHOD
        on 
            PROGRAM.ID = PROGRAMSALESMETHOD.PROGRAMID and
            PROGRAMSALESMETHOD.SALESMETHODID = @ONLINESALESMETHOD 
    left outer join dbo.PROGRAM_MICROSITEEMAILTEMPLATE
        on PROGRAM.ID = PROGRAM_MICROSITEEMAILTEMPLATE.PROGRAMID
    where     
        (PROGRAM.ISPREREGISTERED = 0) and
        (@NAMELIKE is null or PROGRAM.NAME like @NAMELIKE escape '\') and
        (@ACTIVEONLY = 0 or PROGRAM.ISACTIVE = 1) and
        (
            (PROGRAM.ISDAILYADMISSION = 1) or 
            exists(select 1 from EVENT_CTE where PROGRAM.ID = EVENT_CTE.PARENTID)
        )

    union all    
    select 
        ID, 
        NAME, 
        STARTDATE, 
        STARTTIME, 
        STARTDATETIME, 
        PARENTID, 
        ISACTIVE, 
        ISDAILYADMISSION, 
        ONLINE, 
        ISLIVE,
        PROGRAMNAME,
        EVENT_MICROSITEEMAILTEMPLATEID as MICROSITEEMAILTEMPLATEID,
        coalesce(HASACTIVEEMAILTEMPLATE, 0) as HASACTIVEACKNOWLEDGEMENTEMAIL
    from EVENT_CTE
    order by ISDAILYADMISSION desc, PROGRAMNAME, PARENTID, NAME, STARTDATETIME