USP_DATALIST_EVENTSWORKCENTERCALENDAREVENTSLIST

        CREATE procedure dbo.USP_DATALIST_EVENTSWORKCENTERCALENDAREVENTSLIST
        (
            @INCLUDEINACTIVE bit = 0,
            @STARTDATE datetime = null,
            @ENDDATE datetime = null,
            @CURRENTAPPUSERID uniqueidentifier = null,
            @EVENTCATEGORYCODEIDS xml = null,
            @EVENTLOCATIONIDS xml = null,
            @SECURITYFEATUREID uniqueidentifier = null,
            @SECURITYFEATURETYPE tinyint = null,
            @SITEID uniqueidentifier = null,
             @ONLYSHOWUSERSEVENTS bit = 0,
            @SHOWTASKS bit = 0,
            @SHOWCOMPLETEDTASKS bit = 0
       )
        as
            set nocount on;

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

            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 @EVENTLOCATIONIDSTABLE table (
                ID uniqueidentifier
            );

            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;

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

                TASKID uniqueidentifier,
                TASKCOMPLETED tinyint,
                TASKOVERDUE tinyint
            );

            insert into @RESULT
            select 
                newid() as ID,
                EVENT.ID as EVENTID,                        
                EVENT.NAME,
                EVENT.STARTDATE,
                EVENT.STARTTIME,
                EVENT.ENDDATE,                        
                EVENT.ENDTIME,
                EVENT.DESCRIPTION,
                dbo.UFN_EVENT_GETCATEGORY(EVENT.ID, EVENT.PROGRAMID) as EVENTCATEGORYCODE,
                coalesce(case
                    when EVENT.PROGRAMID is not null then dbo.UFN_EVENTLOCATION_GETNAME(PROGRAMEVENTLOCATION.EVENTLOCATIONID)
                    else dbo.UFN_EVENTLOCATION_GETNAME(EVENT.EVENTLOCATIONID)
                end, '') as EVENTLOCATION,
                dbo.UFN_EVENT_GETCAPACITY(EVENT.ID) as AVAILABILITY,
                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(coalesce(EVENT.EVENTCATEGORYCODEID, PROGRAM.PROGRAMCATEGORYCODEID), '00000000-0000-0000-0000-000000000000')) as COLORCODE,
                coalesce(EVENT.CAPACITY, PROGRAM.CAPACITY) as CAPACITY,
                1,
                null as TASKID,
                0 as TASKCOMPLETED,
            0 as TASKOVERDUE
            from dbo.EVENT 
                left outer join dbo.EVENTAUCTION on EVENT.ID = EVENTAUCTION.ID
                left outer join dbo.PROGRAMEVENTLOCATION on EVENT.ID = PROGRAMEVENTLOCATION.EVENTID
                left outer join dbo.PROGRAM on EVENT.PROGRAMID = PROGRAM.ID
                left outer join dbo.EVENTCOORDINATOR on EVENTCOORDINATOR.EVENTID = EVENT.ID
            where
                (
                    (@INCLUDEINACTIVE = 1) 
                    or 
                    (EVENT.ISACTIVE = 1)
                )
                and
                (
                    (@STARTDATE is null and @ENDDATE is null)
                    or
                    (not (EVENT.STARTDATE > @ENDDATE or EVENT.ENDDATE < @STARTDATE))
                )                        
                and
                (
                    ((select count(*) from @EVENTCATEGORYCODEIDSTABLE where SELECTED = 1) = 0)
                    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
                (
                    ((select count(*) from @EVENTLOCATIONIDSTABLE) = 0)
                    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 isnull(PROGRAMEVENTLOCATION.EVENTLOCATIONID, '00000000-0000-0000-0000-000000000000') in (select ID from @EVENTLOCATIONIDSTABLE))
                )   
                and
                (
                    (@SITEID is null)
                    or
                    (EVENT.ID in(select EVENTID from dbo.EVENTSITE where EVENTSITE.SITEID = @SITEID))
                )
                and dbo.UFN_EVENT_USERHASSITEACCESS(@CURRENTAPPUSERID, EVENT.ID) = 1
                and (select count(*) from dbo.UFN_SITEID_MAPFROM_EVENTID(EVENT.ID) as EVENTSITE where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[EVENTSITE].[SITEID] or (SITEID is null and [EVENTSITE].[SITEID] is null)))) > 0
                and (EVENT.HIDEFROMCALENDAR = 0)
                and
                (
                    (@ONLYSHOWUSERSEVENTS = 0)
                    or
                    (EVENTCOORDINATOR.ID is not null and EVENTCOORDINATOR.CONSTITUENTID = @CURRENT_CONSTITUENTID)
                );

                if @SHOWTASKS = 1 
                    insert into @RESULT
                    select 
                        newid() as ID,
                        EVENT.ID as EVENTID,                        
                        EVENTTASK.NAME,
                        EVENTTASK.COMPLETEBYDATE,
                        null,
                        EVENTTASK.COMPLETEBYDATE,                        
                        null,
                        ('Owner:' + NF.NAME + (CHAR(10) + CHAR(13))+ 'Status:'+ EVENTTASK.STATUS + (CHAR(10) + CHAR(13)) + EVENTTASK.COMMENT),
                        dbo.UFN_EVENT_GETCATEGORY(EVENT.ID, EVENT.PROGRAMID) as EVENTCATEGORYCODE,
                        coalesce(case
                            when EVENT.PROGRAMID is not null then dbo.UFN_EVENTLOCATION_GETNAME(PROGRAMEVENTLOCATION.EVENTLOCATIONID)
                            else dbo.UFN_EVENTLOCATION_GETNAME(EVENT.EVENTLOCATIONID)
                        end, '') as EVENTLOCATION,
                        0 as AVAILABILITY,
                        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(coalesce(EVENT.EVENTCATEGORYCODEID, PROGRAM.PROGRAMCATEGORYCODEID), '00000000-0000-0000-0000-000000000000')) as COLORCODE,
                        0 as CAPACITY,
                        2,
                        EVENTTASK.ID as TASKID,
                        EVENTTASK.STATUSCODE as TASKCOMPLETED,
                        (case when (EVENTTASK.STATUSCODE = 0 and (datediff(day, GETDATE(), EVENTTASK.COMPLETEBYDATE) < 0)) then 
                                1
                            else 
                                0
                            end) as TASKOVERDUE
                    from EVENTTASK 
                        inner join EVENT on EVENT.ID = EVENTTASK.EVENTID
                        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(EVENTTASK.OWNERID) NF
                        left outer join dbo.EVENTAUCTION on EVENT.ID = EVENTAUCTION.ID
                        left outer join dbo.PROGRAMEVENTLOCATION on EVENT.ID = PROGRAMEVENTLOCATION.EVENTID
                        left outer join dbo.PROGRAM on EVENT.PROGRAMID = PROGRAM.ID
                        left outer join dbo.EVENTCOORDINATOR on EVENTCOORDINATOR.EVENTID = EVENT.ID
                    where 
                        ((EVENTTASK.STATUSCODE = 0) or (@SHOWCOMPLETEDTASKS = 1))    
                        and
                        (
                            (@STARTDATE is null and @ENDDATE is null)
                            or
                            (EVENTTASK.COMPLETEBYDATE <= @ENDDATE and EVENTTASK.COMPLETEBYDATE >= @STARTDATE)
                        )                        
                        and
                        (
                            ((select count(*) from @EVENTCATEGORYCODEIDSTABLE where SELECTED = 1) = 0)
                            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
                        (
                            ((select count(*) from @EVENTLOCATIONIDSTABLE) = 0)
                            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 isnull(PROGRAMEVENTLOCATION.EVENTLOCATIONID, '00000000-0000-0000-0000-000000000000') in (select ID from @EVENTLOCATIONIDSTABLE))
                        )   
                        and
                        (
                            (@SITEID is null)
                            or
                            (EVENT.ID in(select EVENTID from dbo.EVENTSITE where EVENTSITE.SITEID = @SITEID))
                        )
                        and dbo.UFN_EVENT_USERHASSITEACCESS(@CURRENTAPPUSERID, EVENT.ID) = 1
                        and (select count(*) from dbo.UFN_SITEID_MAPFROM_EVENTID(EVENT.ID) as EVENTSITE where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[EVENTSITE].[SITEID] or (SITEID is null and [EVENTSITE].[SITEID] is null)))) > 0
                        and (EVENT.HIDEFROMCALENDAR = 0)
                        and
                        (
                            (@ONLYSHOWUSERSEVENTS = 0)
                            or
                            (EVENTCOORDINATOR.ID is not null and EVENTCOORDINATOR.CONSTITUENTID = @CURRENT_CONSTITUENTID)
                            or
                            (EVENTTASK.OWNERID is not null and EVENTTASK.OWNERID = @CURRENT_CONSTITUENTID)
                        );

                select * from @RESULT
                order by NAME;