USP_DATALIST_EVENTCOORDINATOREVENTS

create procedure dbo.USP_DATALIST_EVENTCOORDINATOREVENTS
(
  @INCLUDECOMPLETED bit = 0,
  @DATEFILTER tinyint = 0,
  @CURRENTAPPUSERID uniqueidentifier = null
)
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
  else 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
  else 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
  else 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
  else 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

  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(AMOUNTPAID,0)) from dbo.EVENTEXPENSE where EVENTEXPENSE.EVENTID = EVENT.ID),
    dbo.UFN_EVENT_GETNAME(EVENT.MAINEVENTID)
  from dbo.EVENT                                 
  inner join dbo.EVENTCOORDINATOR on EVENTCOORDINATOR.EVENTID = EVENT.ID
  where
    (
      (@INCLUDECOMPLETED = 1) 
      or 
      --The "GETEARLIESTTIME" date function has been inlined here for performance (the part with "cast(@DATE as date)")...

      (cast(EVENT.STARTDATE as date) >= cast(getdate() as date))
    )
    and
    (
      (@STARTDATE is null) 
      or 
      (EVENT.STARTDATE between @STARTDATE and @ENDDATE)
    )                        
    and EVENTCOORDINATOR.CONSTITUENTID = dbo.UFN_CONSTITUENT_GETIDFROMAPPUSERID(@CURRENTAPPUSERID)
  order by EVENT.NAME;

  return 0;