UFN_CONSTITUENTINTERACTION_GETLIST

Create function [dbo].[UFN_CONSTITUENTINTERACTION_GETLIST]
(
    @CONSTITUENTID uniqueidentifier,
    @STARTDATE datetime,
    @ENDDATE datetime,
    @TYPEFILTER uniqueidentifier,
    @CURRENTAPPUSERID uniqueidentifier,
    @SITEFILTERMODE tinyint,
    @SITESSELECTED xml,
    @SECURITYFEATUREID uniqueidentifier,
    @SECURITYFEATURETYPE tinyint
)
returns table
as return
(
  with INTERACTION_CTE as
  (
    select 
      I.ID,
      I.DATE,
      case I.COMPLETED when 1 then 'Completed' else I.STATUS end STATUS,
      I.OBJECTIVE,
      I.INTERACTIONTYPECODEID,
      case when I.PROSPECTPLANID is null then 0 else 1 end ISSTEP,
      I.FUNDRAISERID,
      I.PROSPECTPLANID,
      I.ISINTERACTION,
      I.STATUSCODE,
      I.EVENTID,
      I.INTERACTIONSUBCATEGORYID, 
      I.EXPECTEDSTARTTIME as STARTTIME,
      I.EXPECTEDENDTIME as ENDTIME,
      I.TIMEZONEENTRYID
    from
      dbo.INTERACTION I
    where
      I.CONSTITUENTID = @CONSTITUENTID -- prospect\subject of plan

      and I.STATUSCODE not in (4,5) -- Not Cancelled, Declined


    union

    select
      I.ID,
      I.DATE,
      case I.COMPLETED when 1 then 'Completed' else I.STATUS end,
      I.OBJECTIVE,
      I.INTERACTIONTYPECODEID,
      case when I.PROSPECTPLANID is null then 0 else 1 end,
      I.FUNDRAISERID,
      I.PROSPECTPLANID,
      I.ISINTERACTION,
      I.STATUSCODE,
      I.EVENTID,
      I.INTERACTIONSUBCATEGORYID,
      I.EXPECTEDSTARTTIME as STARTTIME,
      I.EXPECTEDENDTIME as ENDTIME,
      I.TIMEZONEENTRYID
    from
      dbo.INTERACTION I
    inner join
      dbo.INTERACTIONPARTICIPANT IP on IP.INTERACTIONID = I.ID
    where
      IP.CONSTITUENTID = @CONSTITUENTID -- can be any constit, but most likely is a household member with, or relationship to, plan subject

      and I.STATUSCODE not in (4,5) -- Not Cancelled, Declined

  )
  -- where clause for spStep: inner join to STEWARDSHIPPLAN => .CONSTITUENTID 

  select distinct
        I.ID,
        I.DATE,
        I.STATUS,
        FC.ID OWNERID, -- OWNERID (must be fundraiser); s.CONSTITUENTID (assigned to)

        I.OBJECTIVE,
        I.INTERACTIONTYPECODEID, -- Caption = "Contact method"; s.CONTACTMETHODCODEID => INTERACTIONTYPECODE ** just pass ID, move _GETDESCRIPTION outside **

        case when exists(select 1 from dbo.INTERACTIONNOTE where INTERACTIONNOTE.INTERACTIONID=I.ID)
               or exists(select 1 from dbo.INTERACTIONATTACHMENT where INTERACTIONATTACHMENT.INTERACTIONID=I.ID)
               or exists(select 1 from dbo.INTERACTIONMEDIALINK where INTERACTIONMEDIALINK.INTERACTIONID=I.ID)
        then convert(bit,1) else convert(bit,0) end HASDOCUMENTATION, -- HASDOCUMENTATION; sStep joins with stewardship doc tables on = doc.STEPID

        I.ISSTEP ISMOVE, -- ISMOVE (essentially, is this step associated w/a prospect plan?: determines whether Go to targets ConstituentInteraction or Interaction; unnecessary for Stew bc/ there is only StewardshipPlanStep.Page.  ** add an ISSTEWARDSHIPPLANSTEP outside to assist page's Go To logic **

        I.EVENTID [EVENTID], -- EVENT; s.EVENTID ** move UFN call outside **

        case when exists(select 1 from dbo.INTERACTIONRESPONSE where INTERACTIONRESPONSE.INTERACTIONID=I.ID)
        then convert(bit,1) else convert(bit,0) end HASRESPONSES, -- HASRESPONSES; always 0 for sStep

        IC.NAME CATEGORY, -- CATEGORY; spStep.CATEGORYCODEID => STEWARDSHIPSTEPCATEGORYCODE

        ISC.NAME SUBCATEGORY, -- SUBCATEGORY;NULL for spStep 

        case when I.ISSTEP = 1 then '6ce4b0e8-cfcf-4f57-94c3-00eea94fb499'
             else 'b525985b-be02-4f02-a9b8-c110411cf936'
        end VIEWFORMID, -- '?' for spStep

        case 
            when I.ISSTEP = 1 then dbo.UFN_PROSPECTPLAN_GETSITELIST(I.PROSPECTPLANID)
             else dbo.UFN_CONSTITUENTINTERACTION_GETSITELIST(I.ID)
        end SITES, 
        I.STARTTIME, -- spStep.TARGETSTARTTIME

        I.ENDTIME, -- spStep.TARGETENDTIME

        I.TIMEZONEENTRYID -- spStep.TIMEZONEENTRYID  ** move outside: (select DISPLAYNAME from TIMEZONEENTRY where TIMEZONEENTRY.ID = I.TIMEZONEENTRYID) as TIMEZONE

    from
        INTERACTION_CTE I
        left outer join 
            dbo.CONSTITUENT FC on FC.ID=I.FUNDRAISERID
        left outer join 
            dbo.INTERACTIONSUBCATEGORY ISC on ISC.ID = I.INTERACTIONSUBCATEGORYID
        left outer join 
            dbo.INTERACTIONCATEGORY IC on IC.ID = ISC.INTERACTIONCATEGORYID
    where
        I.ISINTERACTION = 1
            and I.STATUSCODE != 0
            and (
                (I.DATE between @STARTDATE and @ENDDATE)
                    or (@STARTDATE is null and @ENDDATE is null)
                    or (I.DATE >= @STARTDATE and @ENDDATE is null)
                    or (@STARTDATE is null and I.DATE <= @ENDDATE)
            )
            and (
                (I.INTERACTIONTYPECODEID = @TYPEFILTER)
                    or (@TYPEFILTER is null)
            ) 
            and (
                select count(*) 
                from dbo.UFN_SITEID_MAPFROM_INTERACTIONID(I.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)))
            ) > 0
            and (
                @SITEFILTERMODE = 0
                    or( I.ISSTEP = 0
                        and I.ID in (
                            select INTERACTIONSITE.INTERACTIONID
                            from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) SITEFILTER
                                inner join dbo.INTERACTIONSITE on INTERACTIONSITE.SITEID = SITEFILTER.SITEID
                        )
                    )
                    or( I.ISSTEP = 1
                        and I.PROSPECTPLANID in(
                            select PROSPECTPLANSITE.PROSPECTPLANID
                            from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) SITEFILTER
                                inner join dbo.PROSPECTPLANSITE on PROSPECTPLANSITE.SITEID = SITEFILTER.SITEID
                        )
                    )
            )
);