USP_CONSTITUENTGROUP_INTERACTIONCOUNT

create procedure dbo.USP_CONSTITUENTGROUP_INTERACTIONCOUNT
(
  @GROUPID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier,
  @SECURITYFEATUREID uniqueidentifier = null,
  @SECURITYFEATURETYPE tinyint = null,
  @INTERACTIONCOUNT int = null output
)
as
begin
  -- Drop temp table if needed

  if object_id('tempdb..#CONSTITUENTGROUPINTERACTION2_GROUPORMEMBERCONSTITUENT') is not null
    drop table #CONSTITUENTGROUPINTERACTION2_GROUPORMEMBERCONSTITUENT;

  create table #CONSTITUENTGROUPINTERACTION2_GROUPORMEMBERCONSTITUENT
  (
    ID uniqueidentifier,
    DATEFROM datetime,
    DATETO datetime
  );

  create nonclustered index [IX_#CONSTITUENTGROUPINTERACTION2_GROUPORMEMBERCONSTITUENT_ID]
  on #CONSTITUENTGROUPINTERACTION2_GROUPORMEMBERCONSTITUENT (ID) include (DATEFROM, DATETO);

  insert into #CONSTITUENTGROUPINTERACTION2_GROUPORMEMBERCONSTITUENT
  select
    CONSTITUENT.ID as ID,
    GROUPMEMBERDATERANGE.DATEFROM,
    GROUPMEMBERDATERANGE.DATETO
  from dbo.GROUPMEMBER
    inner join dbo.CONSTITUENT on CONSTITUENT.ID = GROUPMEMBER.MEMBERID
    left join dbo.GROUPMEMBERDATERANGE on GROUPMEMBERDATERANGE.GROUPMEMBERID = GROUPMEMBER.ID
  where GROUPMEMBER.GROUPID = @GROUPID

  union all

  select
    @GROUPID,
    null as DATEFROM,
    null as DATETO;

  declare @INTERACTIONFORGROUPORMEMBER table
  (
    ID uniqueidentifier,
    LINKEDTHROUGHCONSTITUENTID uniqueidentifier,
    unique clustered(ID, LINKEDTHROUGHCONSTITUENTID)
  );

  insert into @INTERACTIONFORGROUPORMEMBER
  select
    V_CONSTITUENT_INTERACTION.ID,
    GROUPORMEMBERCONSTITUENT.ID as LINKEDTHROUGHCONSTITUENTID
  from dbo.V_CONSTITUENT_INTERACTION
    inner join #CONSTITUENTGROUPINTERACTION2_GROUPORMEMBERCONSTITUENT GROUPORMEMBERCONSTITUENT on GROUPORMEMBERCONSTITUENT.ID = V_CONSTITUENT_INTERACTION.CONSTITUENTID

  union

  select
    V_CONSTITUENT_INTERACTION.ID,
    GROUPORMEMBERCONSTITUENT.ID as LINKEDTHROUGHCONSTITUENTID
  from dbo.V_CONSTITUENT_INTERACTION
    inner join #CONSTITUENTGROUPINTERACTION2_GROUPORMEMBERCONSTITUENT GROUPORMEMBERCONSTITUENT on GROUPORMEMBERCONSTITUENT.ID = V_CONSTITUENT_INTERACTION.CONTACTPERSONID

  union

  select
    V_CONSTITUENT_INTERACTION.ID,
    GROUPORMEMBERCONSTITUENT.ID as LINKEDTHROUGHCONSTITUENTID
  from dbo.V_CONSTITUENT_INTERACTION
    inner join dbo.INTERACTIONPARTICIPANT on INTERACTIONPARTICIPANT.INTERACTIONID = V_CONSTITUENT_INTERACTION.ID
    inner join #CONSTITUENTGROUPINTERACTION2_GROUPORMEMBERCONSTITUENT GROUPORMEMBERCONSTITUENT on GROUPORMEMBERCONSTITUENT.ID = INTERACTIONPARTICIPANT.CONSTITUENTID;

  select
    @INTERACTIONCOUNT = count(INTERACTION.ID)
  from @INTERACTIONFORGROUPORMEMBER GROUPINTERACTION
    inner join dbo.V_CONSTITUENT_INTERACTION INTERACTION on GROUPINTERACTION.ID = INTERACTION.ID
    inner join #CONSTITUENTGROUPINTERACTION2_GROUPORMEMBERCONSTITUENT CONSTITUENT on GROUPINTERACTION.LINKEDTHROUGHCONSTITUENTID = CONSTITUENT.ID
  where
    (
      (
        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
    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
              )
          )
        )
    );

  drop table #CONSTITUENTGROUPINTERACTION2_GROUPORMEMBERCONSTITUENT;
end