USP_CAMPAIGNHIERARCHYBYCONSTITUENT

CREATE procedure dbo.USP_CAMPAIGNHIERARCHYBYCONSTITUENT(
  @CURRENTAPPUSERID uniqueidentifier
  ,@SECURITYFEATURETYPE int
  ,@SECURITYFEATUREID uniqueidentifier
  ,@CONSTITUENTID uniqueidentifier
  ,@INCLUDEREVENUE bit
  ,@INCLUDERECOGNITION bit
  )
  with execute as caller
  as
begin
  if object_id('tempdb..#TMP_DATA_CAMPAIGNHIERARCHYBYCONSTITUENT_CAMPAIGNS') is not null
    drop table #TMP_DATA_CAMPAIGNHIERARCHYBYCONSTITUENT_CAMPAIGNS;

  create table #TMP_DATA_CAMPAIGNHIERARCHYBYCONSTITUENT_CAMPAIGNS(
    ID uniqueidentifier
    ,PARENTCAMPAIGNID uniqueidentifier
    ,NAME nvarchar(100) collate DATABASE_DEFAULT
    ,SEQUENCE int
    ,FILTERCONSTITUENT bit
    );

  insert #TMP_DATA_CAMPAIGNHIERARCHYBYCONSTITUENT_CAMPAIGNS
  select ID
    ,PARENTCAMPAIGNID
    ,name
    ,SEQUENCE
    ,CAST(0 as bit) FILTERCONSTITUENT
  from dbo.UFN_CAMPAIGNHIERARCHY_SECURED(@CURRENTAPPUSERID, @SECURITYFEATURETYPE, @SECURITYFEATUREID)

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

  create table #TMP_DATA_CAMPAIGNHIERARCHYBYCONSTITUENT_CONSTITUENTS(
    CONSTITUENTID uniqueidentifier
  );

  insert into #TMP_DATA_CAMPAIGNHIERARCHYBYCONSTITUENT_CONSTITUENTS(CONSTITUENTID) select @CONSTITUENTID

  insert into #TMP_DATA_CAMPAIGNHIERARCHYBYCONSTITUENT_CONSTITUENTS(CONSTITUENTID) select
    GROUPMEMBER.MEMBERID 
  from dbo.GROUPMEMBER
    left join dbo.GROUPMEMBERDATERANGE on GROUPMEMBERDATERANGE.GROUPMEMBERID = GROUPMEMBER.ID
  where
    GROUPMEMBER.GROUPID = @CONSTITUENTID;

  if @INCLUDEREVENUE = 1
    update c
      set FILTERCONSTITUENT = 1
    from #TMP_DATA_CAMPAIGNHIERARCHYBYCONSTITUENT_CONSTITUENTS g
      inner join FINANCIALTRANSACTION ft on g.CONSTITUENTID = ft.CONSTITUENTID
      inner join FINANCIALTRANSACTIONLINEITEM ftli on ftli.FINANCIALTRANSACTIONID = ft.ID
      inner join REVENUESPLITCAMPAIGN rsc on rsc.REVENUESPLITID = ftli.ID
      inner join #TMP_DATA_CAMPAIGNHIERARCHYBYCONSTITUENT_CAMPAIGNS c on rsc.CAMPAIGNID = c.ID
    where
      ftli.DELETEDON is null

  if @INCLUDERECOGNITION = 1
    update c
      set FILTERCONSTITUENT = 1
    from #TMP_DATA_CAMPAIGNHIERARCHYBYCONSTITUENT_CONSTITUENTS g
      inner join dbo.REVENUERECOGNITION rr on rr.CONSTITUENTID = g.CONSTITUENTID
      left join dbo.FINANCIALTRANSACTIONLINEITEM ftli on rr.REVENUESPLITID = ftli.ID
      inner join dbo.REVENUESPLITCAMPAIGN rsc on rr.REVENUESPLITID = rsc.REVENUESPLITID
      inner join #TMP_DATA_CAMPAIGNHIERARCHYBYCONSTITUENT_CAMPAIGNS c on c.ID = rsc.CAMPAIGNID
    where
      ftli.DELETEDON is null

  ;with CTE
  as (
    select c.ID
      ,c.PARENTCAMPAIGNID
      ,c.name
      ,c.SEQUENCE
      ,c.FILTERCONSTITUENT
    from #TMP_DATA_CAMPAIGNHIERARCHYBYCONSTITUENT_CAMPAIGNS c
    where c.FILTERCONSTITUENT = 1

    union all

    select c.ID
      ,c.PARENTCAMPAIGNID
      ,c.name
      ,c.SEQUENCE
      ,CAST(1 as bit) [FILTERCONSTITUENT]
    from CTE
    inner join #TMP_DATA_CAMPAIGNHIERARCHYBYCONSTITUENT_CAMPAIGNS c on CTE.PARENTCAMPAIGNID = c.ID
    where c.FILTERCONSTITUENT = 0
    )

  select distinct ID
    ,PARENTCAMPAIGNID
    ,NAME
    ,SEQUENCE
  from CTE order by NAME -- the campaign hierarchy allows multiple root campaigns and these should be ordered alphabetically


end