USP_DATALIST_EVENTCOORDINATORTASKS
Displays the tasks for a given event coordinator.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @CURRENTAPPUSERID | uniqueidentifier | IN | Input parameter indicating the ID of the current user. |
| @DATEFILTER | tinyint | IN | Tasks |
| @INCLUDECOMPLETEDTASKS | bit | IN | Include completed |
| @ONLYSHOWUSERSEVENTS | bit | IN | Only show my tasks |
| @SITEID | uniqueidentifier | IN | Site |
| @EVENTCATEGORYCODEID | uniqueidentifier | IN | Category |
| @EVENTLOCATIONID | uniqueidentifier | IN | Location |
Definition
Copy
CREATE procedure dbo.USP_DATALIST_EVENTCOORDINATORTASKS
(
@CURRENTAPPUSERID uniqueidentifier = null,
@DATEFILTER tinyint = 1,
@INCLUDECOMPLETEDTASKS bit = 0,
@ONLYSHOWUSERSEVENTS bit = 0,
@SITEID uniqueidentifier = null,
@EVENTCATEGORYCODEID uniqueidentifier = null,
@EVENTLOCATIONID uniqueidentifier = null
)
as
set nocount on;
declare @DATE datetime;
declare @ENDDATE datetime;
set @DATE = getdate();
if @DATEFILTER = 0 -- all
begin
set @ENDDATE = null
end
if @DATEFILTER = 1 -- this week
begin
set @ENDDATE = dbo.UFN_DATE_THISWEEK_LASTDAY(@DATE, 0)
end
if @DATEFILTER = 2 -- this month
begin
set @ENDDATE = dbo.UFN_DATE_THISMONTH_LASTDAY(@DATE, 0)
end
if @DATEFILTER = 3 -- this quarter
begin
set @ENDDATE = dbo.UFN_DATE_THISQUARTER_LASTDAY(@DATE, 0)
end
if @ONLYSHOWUSERSEVENTS = 1
--CR249204-071806 added distinct to prevent duplicate tasks when the owner is also a coordinator
select distinct
EVENTTASK.ID,
case when EVENTTASK.STATUSCODE = 0 and datediff(day, @DATE, EVENTTASK.COMPLETEBYDATE) < 0 then
1
else
0
end as OVERDUE,
case when EVENTTASK.STATUSCODE = 0 and datediff(day, @DATE, EVENTTASK.COMPLETEBYDATE) < 0 then
'RES:warning'
when EVENTTASK.STATUSCODE = 1 then
'RES:checkmark'
else
'RES:lv_spacer'
end as IMAGE,
EVENTTASK.COMPLETEBYDATE,
EVENTTASK.NAME,
EVENT.NAME,
dbo.UFN_EVENT_GETNAME(EVENT.MAINEVENTID),
NF.NAME,
EVENTTASK.DATECOMPLETED,
EVENTTASK.STATUSCODE,
EVENTTASK.COMMENT,
EVENT.ID
from
dbo.EVENTTASK
inner join dbo.EVENT on EVENT.ID = EVENTTASK.EVENTID
left join dbo.EVENTCOORDINATOR on EVENTCOORDINATOR.EVENTID = EVENT.ID
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(EVENTTASK.OWNERID) NF
where
(
EVENTCOORDINATOR.CONSTITUENTID = dbo.UFN_CONSTITUENT_GETIDFROMAPPUSERID(@CURRENTAPPUSERID)
or
EVENTTASK.OWNERID = dbo.UFN_CONSTITUENT_GETIDFROMAPPUSERID(@CURRENTAPPUSERID)
)
and
(
(@ENDDATE is null) or (EVENTTASK.COMPLETEBYDATE <= @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
(
(EVENTTASK.STATUSCODE = 0)
or
(@INCLUDECOMPLETEDTASKS = 1)
)
order by
EVENTTASK.STATUSCODE asc,
EVENTTASK.COMPLETEBYDATE asc,
EVENTTASK.DATECOMPLETED asc;
else
select distinct
EVENTTASK.ID,
case when EVENTTASK.STATUSCODE = 0 and datediff(day, @DATE, EVENTTASK.COMPLETEBYDATE) < 0 then
1
else
0
end as OVERDUE,
case when EVENTTASK.STATUSCODE = 0 and datediff(day, @DATE, EVENTTASK.COMPLETEBYDATE) < 0 then
'RES:warning'
when EVENTTASK.STATUSCODE = 1 then
'RES:checkmark'
else
'RES:lv_spacer'
end as IMAGE,
EVENTTASK.COMPLETEBYDATE,
EVENTTASK.NAME,
EVENT.NAME,
dbo.UFN_EVENT_GETNAME(EVENT.MAINEVENTID),
NF.NAME,
EVENTTASK.DATECOMPLETED,
EVENTTASK.STATUSCODE,
EVENTTASK.COMMENT,
EVENT.ID
from
dbo.EVENTTASK
inner join dbo.EVENT on EVENT.ID = EVENTTASK.EVENTID
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(EVENTTASK.OWNERID) NF
where
(
(@ENDDATE is null) or (EVENTTASK.COMPLETEBYDATE <= @ENDDATE)
)
and
(
(EVENTTASK.STATUSCODE = 0)
or
(@INCLUDECOMPLETEDTASKS = 1)
)
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 (@CURRENTAPPUSERID IS NULL OR dbo.UFN_EVENT_USERHASSITEACCESS(@CURRENTAPPUSERID, EVENT.ID) = 1)
order by
EVENTTASK.STATUSCODE asc,
EVENTTASK.COMPLETEBYDATE asc,
EVENTTASK.DATECOMPLETED asc;