USP_DATALIST_CONSTITUENTGROUP_PENDINGINTERACTIONS

CREATE procedure dbo.USP_DATALIST_CONSTITUENTGROUP_PENDINGINTERACTIONS
(
  @GROUPID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier,
  @DATEFILTER tinyint = 0,
  @STARTDATE datetime = null,
  @ENDDATE datetime = null,
  @TYPEFILTER uniqueidentifier = null,
  @SITEFILTERMODE tinyint = 0,
  @SITESSELECTED xml = null,
  @SECURITYFEATUREID uniqueidentifier = null,
  @SECURITYFEATURETYPE tinyint = null,
  @SHOWCOMPLETED bit = 0
) 
as begin
  set nocount on;

  declare @DATE datetime

  set @DATE = getdate();

  if @DATEFILTER = 0        -- all

    begin
      set @STARTDATE = null
      set @ENDDATE = null
    end

  if @DATEFILTER = 1        -- this year

    begin
      set @STARTDATE = dbo.UFN_DATE_THISCALENDARYEAR_FIRSTDAY(@DATE, 0)
      set @ENDDATE = dbo.UFN_DATE_THISCALENDARYEAR_LASTDAY(@DATE, 0)
    end

  if @DATEFILTER = 2        -- this quarter

    begin
      set @STARTDATE = dbo.UFN_DATE_THISQUARTER_FIRSTDAY(@DATE, 0)
      set @ENDDATE = dbo.UFN_DATE_THISQUARTER_LASTDAY(@DATE, 0)
    end

  if @DATEFILTER = 3        -- this month

  begin
    set @STARTDATE = dbo.UFN_DATE_THISMONTH_FIRSTDAY(@DATE, 0)
    set @ENDDATE = dbo.UFN_DATE_THISMONTH_LASTDAY(@DATE, 0)
  end

  if @DATEFILTER = 4        -- this week

  begin
    set @STARTDATE = dbo.UFN_DATE_THISWEEK_FIRSTDAY(@DATE, 0)
    set @ENDDATE = dbo.UFN_DATE_THISWEEK_LASTDAY(@DATE, 0)
  end

  if @DATEFILTER = 5        -- next week

  begin
    set @STARTDATE = dbo.UFN_DATE_NEXTWEEK_FIRSTDAY(@DATE, 0)
    set @ENDDATE = dbo.UFN_DATE_NEXTWEEK_LASTDAY(@DATE, 0)
  end

  if @DATEFILTER = 6        -- next month

  begin
    set @STARTDATE = dbo.UFN_DATE_NEXTMONTH_FIRSTDAY(@DATE, 0)
    set @ENDDATE = dbo.UFN_DATE_NEXTMONTH_LASTDAY(@DATE, 0)
  end

  if @DATEFILTER = 7        -- next quarter

  begin
    set @STARTDATE = dbo.UFN_DATE_NEXTQUARTER_FIRSTDAY(@DATE, 0)
    set @ENDDATE = dbo.UFN_DATE_NEXTQUARTER_LASTDAY(@DATE, 0)
  end

  if @DATEFILTER = 8        -- next year

  begin
    set @STARTDATE = dbo.UFN_DATE_NEXTCALENDARYEAR_FIRSTDAY(@DATE, 0)
    set @ENDDATE = dbo.UFN_DATE_NEXTCALENDARYEAR_LASTDAY(@DATE, 0)
  end

  declare @CURRENTDATE datetime;
  set @CURRENTDATE = dbo.UFN_DATE_GETEARLIESTTIME(getdate());

  declare @DATALISTID uniqueidentifier;
  set @DATALISTID = '9dbf2aca-66da-443d-bfc9-89cad0d53e72';

  with CONSTITUENTS_CTE as
  (
    select
    CONSTITUENT.ID as ID,
    NF.NAME,
    CONSTITUENT.KEYNAME,
    CONSTITUENT.FIRSTNAME,
    GROUPMEMBER.ISPRIMARY,
    GMDR.DATEFROM,
    GMDR.DATETO,
    0 as ISGROUP
    from
    dbo.GROUPMEMBER
    inner join
    dbo.CONSTITUENT on CONSTITUENT.ID = GROUPMEMBER.MEMBERID
    left join
    dbo.GROUPMEMBERDATERANGE GMDR on GMDR.GROUPMEMBERID = GROUPMEMBER.ID
    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) NF
    where
    GROUPMEMBER.GROUPID = @GROUPID

    union all

    select
    ID,
    NF.NAME,
    '' as KEYNAME,
    '' as FIRSTNAME,
    0 as ISPRIMARY,
    null as DATEFROM,
    null as DATETO,
    1 as ISGROUP
    from
    dbo.CONSTITUENT
    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) NF
    where
    CONSTITUENT.ID = @GROUPID
  ),

  INTERACTION_CTE as
  (
    select
    INTERACTION.ID,
    INTERACTION.DATE,
    INTERACTION.STATUS,
    case
      when INTERACTION.INTERACTIONBASETYPE = 2 then INTERACTION.CONSTITUENTID
      else INTERACTION.FUNDRAISERID
    end as OWNERID,
    INTERACTION.OBJECTIVE,
    INTERACTION.INTERACTIONTYPECODEID,
    case
      when INTERACTION.INTERACTIONBASETYPE = 2 then
      case
        when exists(select 1 from dbo.STEWARDSHIPNOTE where STEWARDSHIPNOTE.STEPID = INTERACTION.ID)
        or exists(select 1 from dbo.STEWARDSHIPATTACHMENT where STEWARDSHIPATTACHMENT.STEPID = INTERACTION.ID)
        or exists(select 1 from dbo.STEWARDSHIPMEDIALINK where STEWARDSHIPMEDIALINK.STEPID = INTERACTION.ID) then 1
      else 0
      end
      else
      case
        when exists(select 1 from dbo.INTERACTIONNOTE where INTERACTIONNOTE.INTERACTIONID = INTERACTION.ID)
        or exists(select 1 from dbo.INTERACTIONATTACHMENT where INTERACTIONATTACHMENT.INTERACTIONID = INTERACTION.ID)
        or exists(select 1 from dbo.INTERACTIONMEDIALINK where INTERACTIONMEDIALINK.INTERACTIONID = INTERACTION.ID) then 1
      else 0
      end
    end as HASDOCUMENTATION,
    case
      when INTERACTION.INTERACTIONBASETYPE = 1 then 1
      else 0
    end as ISMOVE,
    INTERACTION.EVENTID,
    coalesce(INTERACTIONCATEGORY.NAME, STEWARDSHIPSTEPCATEGORYCODE.DESCRIPTION) as CATEGORY,
    INTERACTIONSUBCATEGORY.NAME as SUBCATEGORY,
    INTERACTION.STARTTIME,
    INTERACTION.ENDTIME,
    INTERACTION.TIMEZONEENTRYID,
    case
      when INTERACTION.INTERACTIONBASETYPE = 2 then 1
      else 0
    end as ISSTEWARDSHIPSTEP,
    case
      when CONSTITUENT.ISGROUP = 1 then '0'
      else
      case
        when CONSTITUENT.ISPRIMARY = 1 then '1'
        else '2' + CONSTITUENT.KEYNAME + ' ' + CONSTITUENT.FIRSTNAME + ' ' + cast(CONSTITUENT.ID as nvarchar(36))
      end
    end as GROUPORPRIMARYSORT,
    CONSTITUENT.NAME as GROUPORPRIMARYSORTVALUE,
    case 
      when dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 then 1 
      else dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_FORCONSTIT(@CURRENTAPPUSERID, @DATALISTID, CONSTITUENT.ID)
    end HASPERMISSIONS,
    INTERACTION.INTERACTIONBASETYPE,
    INTERACTION.PROSPECTPLANID,
    INTERACTION.STEWARDSHIPPLANID,
    case
      when INTERACTIONCONSTITUENT.ISGROUP = 1 then 0
      when INTERACTIONCONSTITUENT.ISORGANIZATION = 1 then 0
      else 1
    end as ISINDIVIDUALSTEP
    from
    dbo.V_CONSTITUENT_INTERACTION INTERACTION
    left join
    dbo.INTERACTIONSUBCATEGORY on INTERACTIONSUBCATEGORY.ID = INTERACTION.INTERACTIONSUBCATEGORYID
    left join
    dbo.INTERACTIONCATEGORY on INTERACTIONCATEGORY.ID = INTERACTIONSUBCATEGORY.INTERACTIONCATEGORYID
    left join
    dbo.STEWARDSHIPSTEPCATEGORYCODE on STEWARDSHIPSTEPCATEGORYCODE.ID = INTERACTION.STEWARDSHIPSTEPCATEGORYCODEID
    left join
    dbo.INTERACTIONPARTICIPANT on INTERACTIONPARTICIPANT.INTERACTIONID = INTERACTION.ID
    inner join
    CONSTITUENTS_CTE CONSTITUENT on (CONSTITUENT.ID = INTERACTION.CONSTITUENTID) or (CONSTITUENT.ID = INTERACTION.CONTACTPERSONID) or (CONSTITUENT.ID = INTERACTIONPARTICIPANT.CONSTITUENTID)
    left join
    dbo.CONSTITUENT INTERACTIONCONSTITUENT on INTERACTIONCONSTITUENT.ID = INTERACTION.CONSTITUENTID
    where
    (
      (INTERACTION.DATE between @STARTDATE and @ENDDATE) or
      (@STARTDATE is null and @ENDDATE is null) or
      (INTERACTION.DATE >= @STARTDATE and @ENDDATE is null) or
      (@STARTDATE is null and INTERACTION.DATE <= @ENDDATE)
    ) and
    (
      (CONSTITUENT.DATEFROM is null and (CONSTITUENT.DATETO is null or CONSTITUENT.DATETO > INTERACTION.DATE)) or
      (CONSTITUENT.DATETO is null and (CONSTITUENT.DATEFROM is null or CONSTITUENT.DATEFROM <= INTERACTION.DATE)) or
      (CONSTITUENT.DATEFROM <= INTERACTION.DATE and CONSTITUENT.DATETO > INTERACTION.DATE)
    ) and
    (@TYPEFILTER is null or INTERACTION.INTERACTIONTYPECODEID = @TYPEFILTER) and
    (@SHOWCOMPLETED = 1 or INTERACTION.STATUS != 'Completed')    and
    exists(
      select top 1 INTERACTIONSITE.SITEID from dbo.UFN_SITEID_MAPFROM_INTERACTIONID(INTERACTION.ID) INTERACTIONSITE
      where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[INTERACTIONSITE].[SITEID] or (SITEID is null and [INTERACTIONSITE].[SITEID] is null)))
    ) and
    (
      @SITEFILTERMODE = 0 or
      (INTERACTION.INTERACTIONBASETYPE = 0 and INTERACTION.ID in 
      (
select INTERACTIONSITE.INTERACTIONID
        from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) SITEFILTER
        inner join dbo.INTERACTIONSITE on INTERACTIONSITE.SITEID = SITEFILTER.SITEID
      )
      ) or
      (INTERACTION.INTERACTIONBASETYPE = 1 and INTERACTION.PROSPECTPLANID in
      (
        select PROSPECTPLANSITE.PROSPECTPLANID
        from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) SITEFILTER
        inner join dbo.PROSPECTPLANSITE on PROSPECTPLANSITE.SITEID = SITEFILTER.SITEID
      )
      ) or
      (INTERACTION.INTERACTIONBASETYPE = 2 and INTERACTION.STEWARDSHIPPLANID in
      (
        select STEWARDSHIPPLANSITE.STEWARDSHIPPLANID
        from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) SITEFILTER
        inner join dbo.STEWARDSHIPPLANSITE on STEWARDSHIPPLANSITE.SITEID = SITEFILTER.SITEID
      )
      )
    )
  )
  select
    I.ID,
    I.DATE,
    I.GROUPORPRIMARYSORTVALUE,
    I.STATUS,
    FC.ID OWNERID,
    NF.NAME FUNDRAISER,
    I.OBJECTIVE,
    INTERACTIONTYPECODE.DESCRIPTION INTERACTIONTYPE,
    I.ISMOVE ISSTEP,
    I.HASDOCUMENTATION,
    I.HASPERMISSIONS,
    min(I.GROUPORPRIMARYSORT) GROUPORPRIMARYSORT,
    I.CATEGORY NAME,
    I.SUBCATEGORY NAME,
    case
    when I.INTERACTIONBASETYPE = 1 then '6ce4b0e8-cfcf-4f57-94c3-00eea94fb499'
    when I.INTERACTIONBASETYPE = 2 then '0a9d7c4b-018d-48b5-aa14-589cf6c0d97b'
    else 'b525985b-be02-4f02-a9b8-c110411cf936'
    end as VIEWFORMID,
    case
    when I.INTERACTIONBASETYPE = 1 then dbo.UFN_STEWARDSHIPPLAN_GETSITELIST(I.STEWARDSHIPPLANID)
    when I.INTERACTIONBASETYPE = 2 then dbo.UFN_PROSPECTPLAN_GETSITELIST(I.PROSPECTPLANID)
    else dbo.UFN_CONSTITUENTINTERACTION_GETSITELIST(I.ID)
    end as SITES,
    I.ISSTEWARDSHIPSTEP,
    I.ISINDIVIDUALSTEP
  from
    INTERACTION_CTE I
  inner join
    dbo.INTERACTIONTYPECODE on I.INTERACTIONTYPECODEID = INTERACTIONTYPECODE.ID
  left outer join 
    dbo.CONSTITUENT FC on FC.ID = I.OWNERID
  outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(FC.ID) NF
  group by
    I.ID, I.DATE, I.GROUPORPRIMARYSORTVALUE, I.STATUS, FC.ID, NF.NAME, I.OBJECTIVE, I.INTERACTIONTYPECODEID,
    I.ISMOVE, I.HASDOCUMENTATION, I.HASPERMISSIONS, I.CATEGORY, I.SUBCATEGORY, I.INTERACTIONBASETYPE,
    I.PROSPECTPLANID, I.STEWARDSHIPPLANID, INTERACTIONTYPECODE.DESCRIPTION, I.ISSTEWARDSHIPSTEP, I.ISINDIVIDUALSTEP
  order by
    GROUPORPRIMARYSORT, I.DATE;

end