USP_DATALIST_EVENTCALENDAREVENTSLIST
Displays the events on the event calendar.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @INCLUDEINACTIVE | bit | IN | Include inactive |
| @ONLYSHOWUSERSEVENTS | bit | IN | Only show my events |
| @SHOWTASKS | bit | IN | Show tasks |
| @SHOWCOMPLETEDTASKS | bit | IN | Include completed tasks |
| @STARTDATE | datetime | IN | Start date |
| @ENDDATE | datetime | IN | End date |
| @CURRENTAPPUSERID | uniqueidentifier | IN | Input parameter indicating the ID of the current user. |
| @EVENTCATEGORYCODEID | uniqueidentifier | IN | Category |
| @EVENTLOCATIONID | uniqueidentifier | IN | Location |
| @HIDEEVENTS | bit | IN | Hide events |
| @SECURITYFEATUREID | uniqueidentifier | IN | Input parameter indicating the ID of the feature to use for site security checking. |
| @SECURITYFEATURETYPE | tinyint | IN | Input parameter indicating the type of the feature to use for site security checking. |
| @SITEID | uniqueidentifier | IN | Site |
Definition
Copy
CREATE procedure dbo.USP_DATALIST_EVENTCALENDAREVENTSLIST
(
@INCLUDEINACTIVE bit = 0,
@ONLYSHOWUSERSEVENTS bit = 0,
@SHOWTASKS bit = 0,
@SHOWCOMPLETEDTASKS bit = 0,
@STARTDATE datetime,
@ENDDATE datetime,
@CURRENTAPPUSERID uniqueidentifier = null,
@EVENTCATEGORYCODEID uniqueidentifier = null,
@EVENTLOCATIONID uniqueidentifier = null,
@HIDEEVENTS bit = 0,
@SECURITYFEATUREID uniqueidentifier = null,
@SECURITYFEATURETYPE tinyint = null,
@SITEID uniqueidentifier = null
)
as
set nocount on;
declare @EVENT_TEMP table
(
EVENTID uniqueidentifier,
EVENTNAME nvarchar(100),
STARTDATE datetime,
STARTTIME nvarchar(10),
ENDDATE datetime,
ENDTIME nvarchar(10),
DESCRIPTION nvarchar(300)
)
declare @TASKS_TEMP table
(
TASKID uniqueidentifier,
EVENTID uniqueidentifier,
COMPLETEBYDATE datetime,
STATUSCODE int,
TASKOVERDUE tinyint,
TASKNAME nvarchar(100),
DESCRIPTION nvarchar(300)
)
declare @RESULTS table
(
ID uniqueidentifier,
EVENTID uniqueidentifier,
EVENTORTASK tinyint, -- 1 = event, 2 = task
TASKCOMPLETED tinyint, -- 0 = active, 1 - completed
TASKOVERDUE tinyint, -- 0 = current , 1 = overdue
EVENTNAME nvarchar(100),
STARTDATE datetime,
STARTTIME nvarchar(10),
ENDDATE datetime,
ENDTIME nvarchar(10),
DESCRIPTION nvarchar(300)
)
declare @CURRENT_CONSTITUENTID uniqueidentifier
set @CURRENT_CONSTITUENTID = dbo.UFN_CONSTITUENT_GETIDFROMAPPUSERID(@CURRENTAPPUSERID)
if @ONLYSHOWUSERSEVENTS = 1
begin
insert into @EVENT_TEMP
select
EVENT.ID,
EVENT.NAME,
EVENT.STARTDATE,
EVENT.STARTTIME,
EVENT.ENDDATE,
EVENT.ENDTIME,
EVENT.DESCRIPTION
from
dbo.EVENT
inner join dbo.EVENTCOORDINATOR on EVENTCOORDINATOR.EVENTID = EVENT.ID
where
(
(@INCLUDEINACTIVE = 1)
or
(ISACTIVE = 1)
)
and
(
(@STARTDATE is null)
or
(EVENT.STARTDATE between @STARTDATE and @ENDDATE)
or
(EVENT.ENDDATE between @STARTDATE and @ENDDATE)
or
(EVENT.STARTDATE <= @STARTDATE and EVENT.ENDDATE >= @ENDDATE)
)
and
(
(@SITEID is null)
or
(EVENT.ID in(select EVENTID from dbo.EVENTSITE where EVENTSITE.SITEID = @SITEID))
)
and
(
(@EVENTCATEGORYCODEID is null)
or
(EVENT.EVENTCATEGORYCODEID = @EVENTCATEGORYCODEID)
)
and
(
(@EVENTLOCATIONID is null)
or
(EVENT.EVENTLOCATIONID = @EVENTLOCATIONID)
)
and
(
(@SITEID is null)
or
(EVENT.ID in(select EVENTID from dbo.EVENTSITE where EVENTSITE.SITEID in(select SITEID from dbo.UFN_SITESFORUSER(@CURRENTAPPUSERID))))
)
and EVENT.PROGRAMID is null
and EVENTCOORDINATOR.CONSTITUENTID = @CURRENT_CONSTITUENTID
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 @HIDEEVENTS = 0)
order by EVENT.NAME;
end
else
begin
insert into @EVENT_TEMP
select
EVENT.ID,
EVENT.NAME,
EVENT.STARTDATE,
EVENT.STARTTIME,
EVENT.ENDDATE,
EVENT.ENDTIME,
EVENT.DESCRIPTION
from
dbo.EVENT
where
(
(@INCLUDEINACTIVE = 1)
or
(ISACTIVE = 1)
)
and
(
(@STARTDATE is null)
or
(EVENT.STARTDATE between @STARTDATE and @ENDDATE)
or
(EVENT.ENDDATE between @STARTDATE and @ENDDATE)
or
(EVENT.STARTDATE <= @STARTDATE and EVENT.ENDDATE >= @ENDDATE)
)
and
(
(@SITEID is null)
or
(EVENT.ID in(select EVENTID from dbo.EVENTSITE where EVENTSITE.SITEID = @SITEID))
)
and
(
(@EVENTCATEGORYCODEID is null)
or
(EVENT.EVENTCATEGORYCODEID = @EVENTCATEGORYCODEID)
)
and
(
(@EVENTLOCATIONID is null)
or
(EVENT.EVENTLOCATIONID = @EVENTLOCATIONID)
)
and
(
(@SITEID is null)
or
(EVENT.ID in(select EVENTID from dbo.EVENTSITE where EVENTSITE.SITEID in(select SITEID from dbo.UFN_SITESFORUSER(@CURRENTAPPUSERID))))
)
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.PROGRAMID is null
and (EVENT.HIDEFROMCALENDAR = 0 and @HIDEEVENTS = 0)
order by EVENT.NAME;
end
if @SHOWTASKS = 1
begin
if @ONLYSHOWUSERSEVENTS = 1
begin
insert into @TASKS_TEMP
select
TASKS.ID,
TASKS.EVENTID,
TASKS.COMPLETEBYDATE,
TASKS.STATUSCODE,
(case when (TASKS.STATUSCODE = 0 and (datediff(day, GETDATE(), TASKS.COMPLETEBYDATE) < 0)) then
1
else
0
end) OVERDUE, --OVERDUE
TASKS.NAME,
('Owner:' + NF.NAME + (CHAR(10) + CHAR(13))+ 'Status:'+ TASKS.STATUS + (CHAR(10) + CHAR(13)) + TASKS.COMMENT) DESCRIPTION
from EVENTTASK TASKS
left join @EVENT_TEMP ET on TASKS.EVENTID = ET.EVENTID
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(TASKS.OWNERID) NF
where ((TASKS.STATUSCODE = 0) or (@SHOWCOMPLETEDTASKS = 1))
and (TASKS.OWNERID = @CURRENT_CONSTITUENTID
or exists(SELECT * from EVENTCOORDINATOR EC
inner join EVENT EV ON EC.EVENTID = EV.ID
inner join EVENTTASK ET ON EV.ID = ET.EVENTID
where ET.ID = TASKS.ID
and (TASKS.OWNERID = @CURRENT_CONSTITUENTID
or EC.CONSTITUENTID = @CURRENT_CONSTITUENTID)
)
)
end
else
begin
insert into @TASKS_TEMP
select
TASKS.ID,
TASKS.EVENTID,
TASKS.COMPLETEBYDATE,
TASKS.STATUSCODE,
(case when (TASKS.STATUSCODE = 0 and (datediff(day, GETDATE(), TASKS.COMPLETEBYDATE) < 0)) then
1
else
0
end) OVERDUE,
TASKS.NAME,
('Owner:' + NF.NAME + (CHAR(10) + CHAR(13))+ 'Status:'+ TASKS.STATUS + (CHAR(10) + CHAR(13)) + TASKS.COMMENT) DESCRIPTION
from EVENTTASK TASKS
inner join EVENT ET ON ET.ID = TASKS.EVENTID
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(TASKS.OWNERID) NF
where ((TASKS.STATUSCODE = 0) or (@SHOWCOMPLETEDTASKS = 1))
and
(
(@SITEID is null)
or
(ET.ID in(select EVENTID from dbo.EVENTSITE where EVENTSITE.SITEID = @SITEID))
)
and
(
(@EVENTCATEGORYCODEID is null)
or
(ET.EVENTCATEGORYCODEID = @EVENTCATEGORYCODEID)
)
and
(
(@EVENTLOCATIONID is null)
or
(ET.EVENTLOCATIONID = @EVENTLOCATIONID)
)
end
insert into @RESULTS
select
ET.EVENTID as ID,
ET.EVENTID,
1,
0,
0,
ET.EVENTNAME,
ET.STARTDATE,
ET.STARTTIME,
ET.ENDDATE,
ET.ENDTIME,
ET.DESCRIPTION
from @EVENT_TEMP ET
union all
select
TASKS.TASKID,
TASKS.EVENTID,
2,
TASKS.STATUSCODE,
TASKS.TASKOVERDUE,
TASKS.TASKNAME,
TASKS.COMPLETEBYDATE,
NULL,
TASKS.COMPLETEBYDATE,
NULL,
TASKS.DESCRIPTION
from @TASKS_TEMP TASKS
end
else
begin
insert into @results
SELECT
ET.EVENTID as ID,
ET.EVENTID,
1,
0,
0,
ET.EVENTNAME,
ET.STARTDATE,
ET.STARTTIME, --STARTTIME
ET.ENDDATE, --ENDDATE
ET.ENDTIME, -- ENDTIME
ET.DESCRIPTION
FROM @EVENT_TEMP ET
end
select * from @RESULTS