USP_DATALIST_EVENTCALENDAREVENTS
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 |
| @DATEFILTER | tinyint | IN | Events |
| @CURRENTAPPUSERID | uniqueidentifier | IN | Input parameter indicating the ID of the current user. |
| @SITEID | uniqueidentifier | IN | Site |
| @EVENTCATEGORYCODEID | uniqueidentifier | IN | Category |
| @EVENTLOCATIONID | uniqueidentifier | IN | Location |
| @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. |
| @HIDEEVENTS | bit | IN | Hide events |
Definition
Copy
CREATE procedure dbo.USP_DATALIST_EVENTCALENDAREVENTS
(
@INCLUDEINACTIVE bit = 0,
@ONLYSHOWUSERSEVENTS bit = 0,
@DATEFILTER tinyint = 2,
@CURRENTAPPUSERID uniqueidentifier = null,
@SITEID uniqueidentifier = null,
@EVENTCATEGORYCODEID uniqueidentifier = null,
@EVENTLOCATIONID uniqueidentifier = null,
@SECURITYFEATUREID uniqueidentifier = null,
@SECURITYFEATURETYPE tinyint = null,
@HIDEEVENTS bit = 0
)
as
set nocount on;
declare @DATE datetime
declare @STARTDATE datetime
declare @ENDDATE datetime
set @DATE = getdate();
if @DATEFILTER = 0 -- all
begin
set @STARTDATE = null
set @ENDDATE = null
end
if @DATEFILTER = 1 -- this week
begin
set @STARTDATE = dbo.UFN_DATE_THISWEEK_FIRSTDAY(@DATE, 0)
set @ENDDATE = dbo.UFN_DATE_THISWEEK_LASTDAY(@DATE, 0)
end
if @DATEFILTER = 2 -- this month
begin
set @STARTDATE = dbo.UFN_DATE_THISMONTH_FIRSTDAY(@DATE, 0)
set @ENDDATE = dbo.UFN_DATE_THISMONTH_LASTDAY(@DATE, 0)
end
if @DATEFILTER = 3 -- this quarter
begin
set @STARTDATE = dbo.UFN_DATE_THISQUARTER_FIRSTDAY(@DATE, 0)
set @ENDDATE = dbo.UFN_DATE_THISQUARTER_LASTDAY(@DATE, 0)
end
if @DATEFILTER = 4 --this calendar year
begin
set @STARTDATE = dbo.UFN_DATE_THISCALENDARYEAR_FIRSTDAY(@DATE, 0)
set @ENDDATE = dbo.UFN_DATE_THISCALENDARYEAR_LASTDAY(@DATE, 0)
end
if @ONLYSHOWUSERSEVENTS = 1
select
EVENT.ID,
EVENT.NAME,
EVENT.STARTDATE,
EVENT.STARTTIME,
EVENT.ENDDATE,
EVENT.ENDTIME,
(select count(ID) from dbo.REGISTRANT where REGISTRANT.EVENTID = EVENT.ID),
(select sum(coalesce(BUDGETEDAMOUNT,0)) from dbo.EVENTEXPENSE where EVENTEXPENSE.EVENTID = EVENT.ID),
(select sum(coalesce(ACTUALAMOUNT,0)) from dbo.EVENTEXPENSE where EVENTEXPENSE.EVENTID = EVENT.ID),
dbo.UFN_EVENT_GETNAME(EVENT.MAINEVENTID),
(select top(1) BASECURRENCYID from dbo.EVENTEXPENSE where EVENTEXPENSE.EVENTID = EVENT.ID),
dbo.UFN_IS_FRIENDS_ASKING_FRIENDS_EVENT(EVENT.ID) as ISFRIENDSASKINGFRIENDS
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)
)
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
EVENT.PROGRAMID is null
and EVENTCOORDINATOR.CONSTITUENTID = dbo.UFN_CONSTITUENT_GETIDFROMAPPUSERID(@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.HIDEFROMCALENDAR = 0 or @HIDEEVENTS = 0)
order by
EVENT.NAME;
else
select
EVENT.ID,
EVENT.NAME,
EVENT.STARTDATE,
EVENT.STARTTIME,
EVENT.ENDDATE,
EVENT.ENDTIME,
(select count(ID) from dbo.REGISTRANT where REGISTRANT.EVENTID = EVENT.ID),
(select sum(coalesce(BUDGETEDAMOUNT,0)) from dbo.EVENTEXPENSE where EVENTEXPENSE.EVENTID = EVENT.ID),
(select sum(coalesce(ACTUALAMOUNT,0)) from dbo.EVENTEXPENSE where EVENTEXPENSE.EVENTID = EVENT.ID),
dbo.UFN_EVENT_GETNAME(EVENT.MAINEVENTID),
(select top(1) BASECURRENCYID from dbo.EVENTEXPENSE where EVENTEXPENSE.EVENTID = EVENT.ID),
dbo.UFN_IS_FRIENDS_ASKING_FRIENDS_EVENT(EVENT.ID) as ISFRIENDSASKINGFRIENDS
from
dbo.EVENT
where
(
(@INCLUDEINACTIVE = 1)
or
(ISACTIVE = 1)
)
and
(
(@STARTDATE is null)
or
(EVENT.STARTDATE between @STARTDATE and @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 (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 or @HIDEEVENTS = 0)
order by
EVENT.NAME;