UFN_FAF_GROUPTOTALSROLLUP

CREATE function dbo.UFN_FAF_GROUPTOTALSROLLUP(
    @EVENTID uniqueidentifier,
    @TEAMFUNDRAISINGTEAMID uniqueidentifier
)
returns table
as return

--with GroupHierarchy_CTE as      --gets all groups under this group

--(

--    select TeamID

--    from UFN_FAF_GETTEAMHIEARACHY(@TEAMFUNDRAISINGTEAMID)

--),

--MembersAndDirects_CTE as 

--(

--    -- members

--    select TFR.CONSTITUENTID as CONSTITUENTID, 1 as MEMBERCOUNT

--    from GroupHierarchy_CTE H

--    inner join dbo.TEAMFUNDRAISINGTEAMMEMBER TM (nolock) on TM.TEAMFUNDRAISINGTEAMID = H.TeamID

--    inner join dbo.TEAMFUNDRAISER TFR (nolock) ON TFR.ID = TM.TEAMFUNDRAISERID

--    

--    union all

--    

--    -- directs

--    select TE.TEAMCONSTITUENTID as CONSTITUENTID, 0 as MEMBERCOUNT

--    from GroupHierarchy_CTE H

--    inner join dbo.TEAMEXTENSION TE (nolock) on TE.TEAMFUNDRAISINGTEAMID = H.TeamID

--)

--select

--    isnull(sum(PRT.AMOUNTRAISED), 0) as AMOUNTRAISED,

--    isnull(sum(PRT.DONORCOUNT), 0) as DONORCOUNT,      -- DONORCOUNT is kind of a compromise in that it's distinct donor count at participant levelt, but not at any group level (sum rollup instead)

--    isnull(sum(MAD.MEMBERCOUNT), 0) as MEMBERCOUNT,

--    isnull(sum(PRT.GIFTCOUNT), 0) as GIFTCOUNT

--from MembersAndDirects_CTE MAD            -- yes i am

--left join dbo.UFN_REVENUE_GETPARTICIPANTRAISEDTOTAL_2(@EVENTID) PRT on PRT.CONSTITUENTID = MAD.CONSTITUENTID


  -- Update the logic to use the group roll up data in table FAFGROUPSUMMARYINFORMATION which generated by business process directly

  select
      isnull(GS.TOTALFUNDRAISE, 0) as AMOUNTRAISED,
      isnull(GS.TOTALDONORCOUNT, 0) as DONORCOUNT,     
      isnull(GS.TOTALPARTICIPANTS, 0) as MEMBERCOUNT,
      isnull(GS.TOTALGIFTCOUNT, 0) as GIFTCOUNT
  from dbo.FAFGROUPSUMMARYINFORMATION GS (nolock)
  where GS.EVENTID=@EVENTID and GS.ID=@TEAMFUNDRAISINGTEAMID