USP_DATALIST_EVENTSWORKCENTERCALENDAREVENTSLIST2

CREATE procedure dbo.USP_DATALIST_EVENTSWORKCENTERCALENDAREVENTSLIST2
(
    @INCLUDEINACTIVE bit = 0,
    @STARTDATE datetime = null,
    @ENDDATE datetime = null,
    @CURRENTAPPUSERID uniqueidentifier = null,
    @EVENTCATEGORYCODEIDS xml = null,
    @EVENTLOCATIONIDS xml = null,
    @ONLYSHOWUSERSEVENTS bit = 0,
    @SHOWTASKS bit = 0,
    @SHOWCOMPLETEDTASKS bit = 0,
    @SITEFILTERMODE tinyint = 0,
    @SITESSELECTED xml = null,
    @LOCATIONFILTERMODE tinyint = 0,
    @CATEGORYCODEFILTERMODE tinyint = 0
)
as
    set nocount on;

    declare @CURRENT_CONSTITUENTID uniqueidentifier
    set @CURRENT_CONSTITUENTID = dbo.UFN_CONSTITUENT_GETIDFROMAPPUSERID(@CURRENTAPPUSERID)

    declare @CLIENTDATE date = dbo.UFN_TIMEZONEENTRY_GETSYSTEMDEFAULTDATEFROMUTC(getutcdate());

    declare @EVENTCATEGORYCODEIDSTABLE table (
        ID uniqueidentifier,
        SELECTED bit,
        COLORCODE int
    );

    insert into @EVENTCATEGORYCODEIDSTABLE
    select isnull(T.c.value('(EVENTCATEGORYCODEID)[1]','uniqueidentifier'), '00000000-0000-0000-0000-000000000000') as 'ID',
            T.c.value('(EVENTCATEGORYCODE)[1]','bit') as 'SELECTED',
            T.c.value('(EVENTCATEGORYCOLORCODE)[1]','integer') as 'COLORCODE'
    from @EVENTCATEGORYCODEIDS.nodes('/EVENTCATEGORYCODEIDS/ITEM') T(c);

    declare @INCLUDEALLEVENTCATEGORIES bit = 0

    if not exists (select 1 from @EVENTCATEGORYCODEIDSTABLE where SELECTED = 1) begin
        set @INCLUDEALLEVENTCATEGORIES = 1;
    end

    declare @EVENTLOCATIONIDSTABLE table (
        ID uniqueidentifier
    );

    declare @INCLUDEALLEVENTLOCATIONS bit = 0;
    declare @NOEVENTLOCATIONOPTIONSELECTED bit = 0;

    insert into @EVENTLOCATIONIDSTABLE
    select isnull(T.c.value('(EVENTLOCATIONID)[1]','uniqueidentifier'), '00000000-0000-0000-0000-000000000000') as 'ID'
    from @EVENTLOCATIONIDS.nodes('/EVENTLOCATIONIDS/ITEM') T(c)
    where T.c.value('(EVENTLOCATION)[1]','bit') = 1;

    if @@rowcount = 0 begin
        set @INCLUDEALLEVENTLOCATIONS = 1;
    end
    else begin
        select @NOEVENTLOCATIONOPTIONSELECTED = 1
        from @EVENTLOCATIONIDSTABLE
        where ID = '00000000-0000-0000-0000-000000000000';
    end

    declare @RESULT table (
        ID uniqueidentifier,
        EVENTID uniqueidentifier,
        NAME nvarchar(100),
        STARTDATE datetime,
        STARTTIME nvarchar(10),
        ENDDATE datetime,
        ENDTIME nvarchar(10),
        DESCRIPTION nvarchar(300),
        EVENTCATEGORYCODE nvarchar(100),
        EVENTLOCATION nvarchar(500),
        AVAILABILITY integer,
        EVENTTYPE integer,
        COLORCODE integer,
        CAPACITY integer,
        EVENTORTASK tinyint, -- 1 = event, 2 = task

        TASKID uniqueidentifier,
        TASKCOMPLETED tinyint,
        TASKOVERDUE tinyint,
        RSSPUBLISHDATE datetime
    );

    with FILTEREDEVENTS as (
        select
            EVENT.ID,
            EVENT.NAME,
            EVENT.STARTDATE,
            EVENT.STARTTIME,
            EVENT.ENDDATE,
            EVENT.ENDTIME,
            EVENT.DESCRIPTION,
            dbo.UFN_EVENT_GETCATEGORY(EVENT.ID, EVENT.PROGRAMID) as EVENTCATEGORYCODE,
            dbo.UFN_EVENT_GETLOCATIONNAME(EVENT.ID) as EVENTLOCATION,
            case 
                when EVENTAUCTION.ID is not null then 5 -- Auction

                when EVENT.PROGRAMID is not null then 10 -- Program event

                else 1 -- Event

            end as EVENTTYPE,
            (select COLORCODE from @EVENTCATEGORYCODEIDSTABLE C where C.ID = coalesce(EVENT.EVENTCATEGORYCODEID, PROGRAM.PROGRAMCATEGORYCODEID, '00000000-0000-0000-0000-000000000000')) as COLORCODE,
            coalesce(EVENT.CAPACITY, PROGRAM.CAPACITY) as CAPACITY,
            EVENT.DATECHANGED as RSSPUBLISHDATE
        from
            dbo.EVENT
        left outer join
            dbo.EVENTAUCTION on EVENTAUCTION.ID = EVENT.ID
        left outer join
            dbo.PROGRAM on PROGRAM.ID = EVENT.PROGRAMID
        where
            (
                @INCLUDEINACTIVE = 1
                or (
                    EVENT.ISACTIVE = 1
                    and (EVENT.PROGRAMID is null or PROGRAM.ISACTIVE = 1)
                )
            )
            and (
                @INCLUDEALLEVENTCATEGORIES = 1
                or (
                    EVENT.PROGRAMID is null
                    and isnull(EVENT.EVENTCATEGORYCODEID, '00000000-0000-0000-0000-000000000000') in (select ID from @EVENTCATEGORYCODEIDSTABLE where SELECTED = 1)
                )
                or (
                    EVENT.PROGRAMID is not null
                    and isnull(PROGRAM.PROGRAMCATEGORYCODEID, '00000000-0000-0000-0000-000000000000') in (select ID from @EVENTCATEGORYCODEIDSTABLE where SELECTED = 1)
                )
            )
            and (
                @INCLUDEALLEVENTLOCATIONS = 1
                or (
                    EVENT.PROGRAMID is null
                    and isnull(EVENT.EVENTLOCATIONID, '00000000-0000-0000-0000-000000000000') in (select ID from @EVENTLOCATIONIDSTABLE)
                )
                or (
                    EVENT.PROGRAMID is not null
                    and (
                        (@NOEVENTLOCATIONOPTIONSELECTED = 1 and not exists (select 1 from dbo.PROGRAMEVENTLOCATION where PROGRAMEVENTLOCATION.EVENTID = EVENT.ID))
                        or exists (
                            select 1
                            from dbo.PROGRAMEVENTLOCATION
                            inner join @EVENTLOCATIONIDSTABLE on [@EVENTLOCATIONIDSTABLE].ID = PROGRAMEVENTLOCATION.EVENTLOCATIONID
                            where PROGRAMEVENTLOCATION.EVENTID = EVENT.ID
                        )
                    )
                )
            )
            and dbo.UFN_EVENT_USERHASSITEACCESS(@CURRENTAPPUSERID, EVENT.ID) = 1
            and (
                @SITEFILTERMODE = 0
                or exists (
                    select 1
                    from dbo.EVENTSITE with (nolock)
                    inner join dbo.UFN_SITE_BUILDDATALISTSITEFILTER(@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) on UFN_SITE_BUILDDATALISTSITEFILTER.SITEID = EVENTSITE.SITEID  
                    where EVENTSITE.EVENTID = EVENT.ID
                )
            )
            and EVENT.HIDEFROMCALENDAR = 0
    )
    insert into @RESULT
    select
        newid() as ID,
        FILTEREDEVENTS.ID as EVENTID,
        FILTEREDEVENTS.NAME,
        FILTEREDEVENTS.STARTDATE,
        FILTEREDEVENTS.STARTTIME,
        FILTEREDEVENTS.ENDDATE,
        FILTEREDEVENTS.ENDTIME,
        FILTEREDEVENTS.DESCRIPTION,
        FILTEREDEVENTS.EVENTCATEGORYCODE,
        FILTEREDEVENTS.EVENTLOCATION,
        dbo.UFN_EVENT_GETCAPACITY(FILTEREDEVENTS.ID) as AVAILABILITY,
        FILTEREDEVENTS.EVENTTYPE,
        FILTEREDEVENTS.COLORCODE,
        FILTEREDEVENTS.CAPACITY,
        1 as EVENTORTASK,  -- Event

        null as TASKID,
        null as TASKCOMPLETED,
        null as TASKOVERDUE,
        FILTEREDEVENTS.RSSPUBLISHDATE
    from
        FILTEREDEVENTS
    where
        (
            (@STARTDATE is null and @ENDDATE is null)
            or (not (FILTEREDEVENTS.STARTDATE > @ENDDATE or FILTEREDEVENTS.ENDDATE < @STARTDATE))
        )
        and (
            @ONLYSHOWUSERSEVENTS = 0
            or exists (select 1 from dbo.EVENT inner join dbo.EVENTCOORDINATOR on EVENTCOORDINATOR.EVENTID = FILTEREDEVENTS.ID where EVENTCOORDINATOR.CONSTITUENTID = @CURRENT_CONSTITUENTID)
        )

    union all

    select
        newid() as ID,
        FILTEREDEVENTS.ID as EVENTID,
        EVENTTASK.NAME,
        EVENTTASK.COMPLETEBYDATE as STARTDATE,
        null as    STARTTIME,
        EVENTTASK.COMPLETEBYDATE as ENDDATE,
        null as ENDTIME,
        ('Owner:' + NF.NAME + (CHAR(10) + CHAR(13))+ 'Status:'+ EVENTTASK.STATUS + (CHAR(10) + CHAR(13)) + EVENTTASK.COMMENT) as DESCRIPTION,
        FILTEREDEVENTS.EVENTCATEGORYCODE,
        FILTEREDEVENTS.EVENTLOCATION,
        0 as AVAILABILITY,
        FILTEREDEVENTS.EVENTTYPE,
        FILTEREDEVENTS.COLORCODE,
        0 as CAPACITY,
        2 as EVENTORTASK,  -- Task

        EVENTTASK.ID as TASKID,
        EVENTTASK.STATUSCODE as TASKCOMPLETED,
        case
            when (EVENTTASK.STATUSCODE = 0 and (datediff(day, @CLIENTDATE, EVENTTASK.COMPLETEBYDATE) < 0)) then 1
            else 0
        end as TASKOVERDUE,
        FILTEREDEVENTS.RSSPUBLISHDATE
    from
        FILTEREDEVENTS
    inner join
        dbo.EVENTTASK on EVENTTASK.EVENTID = FILTEREDEVENTS.ID
    outer apply
        dbo.UFN_CONSTITUENT_DISPLAYNAME(EVENTTASK.OWNERID) as NF
    where
        @SHOWTASKS = 1
        and (@SHOWCOMPLETEDTASKS = 1 or EVENTTASK.STATUSCODE = 0)
        and (
            (@STARTDATE is null and @ENDDATE is null)
            or (EVENTTASK.COMPLETEBYDATE <= @ENDDATE and EVENTTASK.COMPLETEBYDATE >= @STARTDATE)
        )
        and (
            @ONLYSHOWUSERSEVENTS = 0
            or exists (select 1 from dbo.EVENT inner join dbo.EVENTCOORDINATOR on EVENTCOORDINATOR.EVENTID = FILTEREDEVENTS.ID where EVENTCOORDINATOR.CONSTITUENTID = @CURRENT_CONSTITUENTID)
            or EVENTTASK.OWNERID = @CURRENT_CONSTITUENTID
        )

    select
        ID,
        EVENTID,
        NAME,
        STARTDATE,
        STARTTIME,
        ENDDATE,
        ENDTIME,
        DESCRIPTION,
        EVENTCATEGORYCODE,
        EVENTLOCATION,
        AVAILABILITY,
        EVENTTYPE,
        COLORCODE,
        CAPACITY,
        EVENTORTASK,
        TASKID,
        TASKCOMPLETED,
        TASKOVERDUE,
        RSSPUBLISHDATE
    from @RESULT
    order by
        RSSPUBLISHDATE desc,
        NAME,
        STARTDATE,
        STARTTIME,
        ENDDATE,
        ENDTIME;