USP_DATALIST_EVENTCALENDARITEM

CREATE procedure dbo.USP_DATALIST_EVENTCALENDARITEM
(
  @STARTDATE datetime = null,
  @ENDDATE datetime = null,
  @ONLYSHOWUSERSEVENTS bit = 0,
  @EVENTLOCATIONID uniqueidentifier = null,
  @CURRENTAPPUSERID uniqueidentifier,
  @INCLUDEINVITATIONS bit = null,
  @SHOWTASKS bit = 0,
  @SHOWCOMPLETEDTASKS bit = 0
)
as
  set nocount on;

  declare @EVENT table(
    ID uniqueidentifier,
    NAME nvarchar(150),
    STARTDATE datetime,
    STARTTIME UDT_HOURMINUTE,
    ENDDATE datetime,
    ENDTIME UDT_HOURMINUTE,
    DESCRIPTION nvarchar(255),
    EVENTLOCATIONID uniqueidentifier,
    HIDEFROMCALENDAR bit,
    DATEADDED datetime,
    ADDEDBYID uniqueidentifier
  )
  declare @TASKS table(
    TASKID uniqueidentifier,
    EVENTID uniqueidentifier,
    COMPLETEBYDATE datetime,
    STATUSCODE int,
    TASKOVERDUE tinyint,
    TASKNAME nvarchar(100),
    DESCRIPTION nvarchar(300)
  )
  declare @RESULTS table(
    ID uniqueidentifier,
    EVENTID uniqueidentifier,
    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),
    TYPECODE tinyint, -- 0 = Event, 1 = Invitation, 2 = Task

    DESCRIPTION nvarchar(300),
    LOCATION nvarchar(255) null,  
    DATEADDED datetime null,  
    ADDEDBY nvarchar(255) null
  )  

  declare @CURRENT_CONSTITUENTID uniqueidentifier

  set @CURRENT_CONSTITUENTID = dbo.UFN_CONSTITUENT_GETIDFROMAPPUSERID(@CURRENTAPPUSERID)

  insert into @EVENT(
    ID, NAME, STARTDATE, STARTTIME, ENDDATE, ENDTIME, DESCRIPTION, EVENTLOCATIONID, HIDEFROMCALENDAR, DATEADDED, ADDEDBYID
  )
  (
    select distinct
      EVENT.ID,
      EVENT.NAME,
      --The "GETEARLIESTTIME" date function has been inlined here for performance (the part with "cast(@DATE as date)")...

      cast(EVENT.STARTDATE as date),
      EVENT.STARTTIME,
      --The "GETEARLIESTTIME" date function has been inlined here for performance (the part with "cast(@DATE as date)")...

      cast(EVENT.ENDDATE as date),
      EVENT.ENDTIME,
      EVENT.DESCRIPTION,
      EVENT.EVENTLOCATIONID,
      EVENT.HIDEFROMCALENDAR,
      EVENT.DATEADDED,
      EVENT.ADDEDBYID
    from dbo.EVENT 
      left outer join dbo.EVENTCOORDINATOR on EVENTCOORDINATOR.EVENTID = EVENT.ID
    where
      ( 
        (@EVENTLOCATIONID is null) 
        or (EVENT.EVENTLOCATIONID = @EVENTLOCATIONID) 
      )
      and EVENT.ISACTIVE = 1
      and ( 
        (@ONLYSHOWUSERSEVENTS = 0) 
        or (EVENTCOORDINATOR.CONSTITUENTID = dbo.UFN_CONSTITUENT_GETIDFROMAPPUSERID(@CURRENTAPPUSERID)) 
      )
      and EVENT.PROGRAMID is null
      and dbo.UFN_EVENT_USERHASSITEACCESS(@CURRENTAPPUSERID, EVENT.ID) = 1
  )

  insert into @RESULTS(
    ID, EVENTID, TASKCOMPLETED, TASKOVERDUE, EVENTNAME, STARTDATE, STARTTIME, ENDDATE, ENDTIME, TYPECODE, DESCRIPTION, LOCATION, DATEADDED, ADDEDBY
  )
  (
    select distinct
      EVENT.ID as ID,
      EVENT.ID,      
      0,
      0,
      EVENT.NAME,
      --The "GETEARLIESTTIME" date function has been inlined here for performance (the part with "cast(@DATE as date)")...

      cast(EVENT.STARTDATE as date),
      EVENT.STARTTIME,
      --The "GETEARLIESTTIME" date function has been inlined here for performance (the part with "cast(@DATE as date)")...

      cast(EVENT.ENDDATE as date),
      EVENT.ENDTIME,
      0 as TYPECODE,
      EVENT.DESCRIPTION,
      EVENTLOCATION.NAME,
      EVENT.DATEADDED,
      CHANGEAGENT.USERNAME
    from @EVENT EVENT
      inner join dbo.CHANGEAGENT on EVENT.ADDEDBYID = CHANGEAGENT.ID
      left join dbo.EVENTLOCATION on EVENTLOCATION.ID = EVENT.EVENTLOCATIONID
    where
      (
        (@STARTDATE is null and @ENDDATE is null)
        or ((@STARTDATE is not null and @ENDDATE is not null) and (EVENT.STARTDATE <= @ENDDATE and @STARTDATE <= EVENT.ENDDATE))
        or ( (EVENT.STARTDATE >= @STARTDATE) and (@ENDDATE is null and @STARTDATE is not null) )
        or ( (EVENT.ENDDATE <= @ENDDATE) and (@STARTDATE is null and @ENDDATE is not null) )
      )
      and EVENT.HIDEFROMCALENDAR = 0
  )

  if @INCLUDEINVITATIONS = 1
  begin
    insert into @RESULTS(
      ID, EVENTID, TASKCOMPLETED, TASKOVERDUE, EVENTNAME, STARTDATE, STARTTIME, ENDDATE, ENDTIME, TYPECODE, DESCRIPTION, LOCATION, DATEADDED, ADDEDBY
    )
    (
      select distinct
        INVITATION.ID,
        INVITATION.EVENTID,
        0,
        0,
        INVITATION.NAME,
        INVITATION.MAILDATE,
        '' as STARTTIME,
        INVITATION.MAILDATE as ENDDATE,
        '' as ENDTIME,
        1 as TYPECODE,
        INVITATION.DESCRIPTION,
        EVENTLOCATION.NAME,
        INVITATION.DATEADDED,
        CHANGEAGENT.USERNAME
      from dbo.INVITATION
        inner join @EVENT EVENT on EVENT.ID = INVITATION.EVENTID
        inner join dbo.CHANGEAGENT on INVITATION.ADDEDBYID = CHANGEAGENT.ID
        left join dbo.EVENTLOCATION on EVENTLOCATION.ID = EVENT.EVENTLOCATIONID
      where
        (
          ( (INVITATION.MAILDATE between @STARTDATE and @ENDDATE) or (@STARTDATE is null and @ENDDATE is null) )
          or ( (INVITATION.MAILDATE >= @STARTDATE) and (@ENDDATE is null and @STARTDATE is not null) )
          or ( (INVITATION.MAILDATE <= @ENDDATE) and (@STARTDATE is null and @ENDDATE is not null) )
        )
    )
  end

  if @SHOWTASKS = 1 
  begin
    if @ONLYSHOWUSERSEVENTS = 1
      begin  
        insert into @TASKS
        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 dbo.EVENTTASK TASKS
        left join @EVENT ET on TASKS.EVENTID = ET.ID
        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
        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 dbo.EVENTTASK TASKS
        inner join dbo.EVENT ET ON ET.ID = TASKS.EVENTID
        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(TASKS.OWNERID) NF
        where ((TASKS.STATUSCODE = 0) or (@SHOWCOMPLETEDTASKS = 1))                                
        and
        (
          (@EVENTLOCATIONID is null)
          or
          (ET.EVENTLOCATIONID = @EVENTLOCATIONID)
        ) 
      end

    insert into @RESULTS( 
      ID, EVENTID, TASKCOMPLETED, TASKOVERDUE, EVENTNAME, STARTDATE, STARTTIME, ENDDATE, ENDTIME, TYPECODE, DESCRIPTION
    )
    select
      TASKS.TASKID,
      TASKS.EVENTID,
      TASKS.STATUSCODE,
      TASKS.TASKOVERDUE,
      TASKS.TASKNAME,
      TASKS.COMPLETEBYDATE,
      NULL,
      TASKS.COMPLETEBYDATE,
      NULL,
      2 as TYPECODE,
      TASKS.DESCRIPTION
    from @TASKS TASKS
  end

  select
    ID,
    EVENTNAME as NAME,
    STARTDATE,
    STARTTIME,
    ENDDATE,
    ENDTIME,
    TYPECODE, 
    FORMATTEDDESCRIPTION.VALUE,
    DATEADDED,
    ADDEDBY,
    EVENTID,
    TASKCOMPLETED,
    TASKOVERDUE
  from
    @RESULTS
    outer apply (
        select case when LEN(LOCATION) > 50 then
                DESCRIPTION + char(10) + LEFT(LOCATION, 50) + '...'
            else
                DESCRIPTION + char(10) + LOCATION
            end as VALUE
    ) as FORMATTEDDESCRIPTION
  order by 
    DATEADDED desc;