USP_REPORT_FAFFUNDRAISINGGROUPSHIERARCHY

CREATE procedure dbo.USP_REPORT_FAFFUNDRAISINGGROUPSHIERARCHY
(
    @EventID uniqueidentifier,
    @GroupTypes int = 4,
    @WEBURL nvarchar(1200) = '',
    @WEBURLGROUP nvarchar(1200) = '',
  @REPORTUSERID nvarchar(128) = null,
  @ALTREPORTUSERID nvarchar(128) = null,
  @SITEFILTERMODE tinyint = 0,     
  @SITESSELECTED nvarchar(max) = null, 
  @GROUPID uniqueidentifier = null
)

as
begin

declare @CURRENTAPPUSERID as uniqueidentifier 
 set @CURRENTAPPUSERID = dbo.UFN_APPUSER_GETREPORTAPPUSERID(@REPORTUSERID, @ALTREPORTUSERID);
declare @ISADMIN bit;
  declare @SITESGRANTED table(
    SITEID uniqueidentifier
  )
  insert into @SITESGRANTED
  select SITEID
  from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,'30ec8b3f-2cde-416d-993e-c35ae722d54b', 21)

  set @ISADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);


declare @EventIDP as uniqueidentifier set @EventIDP = (Select PRIORYEAREVENTID from EVENTEXTENSION where EVENTID = @EventID);
--select @EventIDP


with GroupFacts as (
--select distinct ID, MEMBERCOUNT Members, MEMBERCOUNT - MEMBERNEWCOUNT MembersRetained, PARTMEMBERCOUNTRETAINED PctMembersRetained, SUBGROUPCOUNT Teams, SUBGROUPRETAINEDCOUNT TeamsRetained, PARCSUBGROUPRETAINEDCOUNT PctTeamsRetained, COMMUNICATIONSENT CommsSent

select distinct ID, MEMBERNUMBER Members, MEMBERNUMBER - MEMBERNUMBERNEW MembersRetained, PARTMEMBERNUMBERRETAINED PctMembersRetained, SUBGROUPNUMBER Teams, SUBGROUPRETAINEDNUMBER TeamsRetained, PARTOFSUBGROUPRETAINED PctTeamsRetained, ( COMMUNICATIONSENTACTUAL + isnull( COMMUNICATIONSENTSOCIAL,0) ) CommsSent
from [V_QUERY_REGISTRANT_FUNDRAISINGGROUP]
where EVENTID = @EventID
), PriorGroupFacts as (
--select distinct RF.ID, MEMBERCOUNT Members, MEMBERCOUNT - MEMBERNEWCOUNT MembersRetained, PARTMEMBERCOUNTRETAINED PctMembersRetained, SUBGROUPCOUNT Teams, SUBGROUPRETAINEDCOUNT TeamsRetained, PARCSUBGROUPRETAINEDCOUNT PctTeamsRetained, COMMUNICATIONSENT CommsSent

select distinct RF.ID, MEMBERNUMBER Members, MEMBERNUMBER - MEMBERNUMBERNEW MembersRetained, PARTMEMBERNUMBERRETAINED PctMembersRetained, SUBGROUPNUMBER Teams, SUBGROUPRETAINEDNUMBER TeamsRetained, PARTOFSUBGROUPRETAINED PctTeamsRetained, ( COMMUNICATIONSENTACTUAL + isnull( COMMUNICATIONSENTSOCIAL,0) ) CommsSent
from [V_QUERY_REGISTRANT_FUNDRAISINGGROUP] RF
inner join TEAMEXTENSION P on P.TEAMFUNDRAISINGTEAMID = RF.ID
inner join TEAMEXTENSION C on C.TEAMCONSTITUENTID = P.TEAMCONSTITUENTID
where P.EVENTID = @EventIDP and C.EVENTID = @EventID
), RegistrantRoles as (
select * from UFN_REGISTRANT_GETFAFROLE(@EventID, null)
)

select         CASE WHEN @WEBURL = '' THEN 'http://www.blackbaud.com/REGISTRANTID?REGISTRANTID=' + convert(nvarchar(36), R.ID)
        ELSE @WEBURL +  convert(nvarchar(36), R.ID) END as REGISTRANTID,
--'http://www.blackbaud.com/REGISTRANTID?REGISTRANTID=' + convert(nvarchar(36), R.ID) as REGISTRANTID, 

C.NAME, C.ID as ConstituentID,
-- Think of TopLevel as 'Where the hierarchy stops rolling up'

case when H.HouseholdID is null and H.TEAMID is null and H.COMPANYID is null then 1  -- Independent Individuals

     when TXH.TYPECODE = 3 and H.TEAMID is null and H.COMPANYID is null then 2       -- Independent Households 

     when TX.TYPECODE  = 1 and H.COMPANYID is null then 3                            -- Independent Teams

     when TXC.TYPECODE = 2 then 4                                                    -- Under a company

     else 9 end as TopLevel,   -- One can't imagine how one gets this result

ROLES.Role,
ROLES.isLeader,
RR.DATEPURCHASED RegistrationDate,
RX.FUNDRAISINGGOAL FRGoal, RX.TARGETFUNDRAISINGGOAL TgtGoal,
dbo.UFN_REVENUE_GETPARTICIPANTRAISEDTOTAL(R.ID, R.EVENTID) Raised, 
RX.COMMUNICATIONGOAL CommsGoal, 
dbo.UFN_FAFGETTOTALCOMMUNICATIONS(R.EVENTID, dbo.UFN_CLIENTUSERID_GET_BY_CONSTITUENT(R.CONSTITUENTID)) +
(SELECT COUNT(*) 
  FROM   [FAFEVENTSOCIALNETWORKPOST] 
  WHERE  registrantid  = r.id 
) CommsSent, 
case when RP.ID is null then 0 else 1 end as ParticipantReturned,
--Team

CASE WHEN @WEBURLGROUP = '' THEN 'http://www.blackbaud.com/TEAMID?TEAMID=' + convert(nvarchar(36), H.TEAMID)
        ELSE @WEBURLGROUP +  convert(nvarchar(36), H.TEAMID) END as TEAMID,
--- 'http://www.blackbaud.com/TEAMID?TEAMID=' + convert(nvarchar(36), H.TEAMID) as TEAMID, 

TX.TEAMCONSTITUENTID as TeamConstituentID,
H.TEAMNAME, 
T.DATEADDED as TeamRegistrationDate,
T.GOAL TeamGoal, 
dbo.UFN_REVENUE_GETTEAMRAISEDTOTAL(H.TEAMID, R.EVENTID) TeamRaised,
TGF.CommsSent TeamCommsSent,
TX.COMMUNICATIONGOAL TeamCommsGoal,
TGF.Members TeamTeamMembers,
TGFP.Members TeamPriorMembers,
case when TGFP.ID is null then 0 else 1 end as TeamReturned,
--TX.TEAMMEMBERGOAL,

--TX.PCTTEAMMEMBERRETENSION,

--Company


CASE WHEN @WEBURLGROUP = '' THEN 'http://www.blackbaud.com/COMPANYID?COMPANYID=' + convert(nvarchar(36), H.COMPANYID)
        ELSE @WEBURLGROUP +  convert(nvarchar(36), H.COMPANYID) END as COMPANYID,
--'http://www.blackbaud.com/COMPANYID?COMPANYID=' + convert(nvarchar(36), H.COMPANYID) as COMPANYID, 

TXC.TEAMCONSTITUENTID as CompanyConstituentID,
H.COMPANYNAME,
TC.DATEADDED as CompanyRegistrationDate,
TC.GOAL CompanyGoal,
dbo.UFN_REVENUE_GETCOMPANYRAISEDTOTAL(H.COMPANYID, R.EVENTID) CompanyRaised, 
CGF.CommsSent CompanyCommsSent,
TXC.COMMUNICATIONGOAL CompanyCommGoal,
CGF.Members CompanyTeamMembers,
CGFP.Members CompanyPriorMembers,
CGF.Teams CompanyTeams,
CGFP.Teams CompanyPriorTeams,
case when CGFP.ID is null then 0 else 1 end as CompanyReturned,
--Household


CASE WHEN @WEBURLGROUP = '' THEN 'http://www.blackbaud.com/HOUSEHOLDID?HOUSEHOLDID=' + convert(nvarchar(36), H.HOUSEHOLDID)
        ELSE @WEBURLGROUP +  convert(nvarchar(36), H.HOUSEHOLDID) END as HOUSEHOLDID,
-- 'http://www.blackbaud.com/HOUSEHOLDID?HOUSEHOLDID=' + convert(nvarchar(36), H.HOUSEHOLDID) as HOUSEHOLDID, 

TXH.TEAMCONSTITUENTID as HouseholdConstituentID,
H.HouseholdName,
TH.DATEADDED as HouseholdRegistrationDate,
TH.GOAL HouseholdGoal,
dbo.UFN_REVENUE_GETHOUSEHOLDRAISEDTOTAL(H.HouseholdID, R.EVENTID) HouseholdRaised,
HGF.CommsSent HouseholdCommsSent,
TXH.COMMUNICATIONGOAL HouseholdCommsGoal,
HGF.Members HouseholdMembers,
HGFP.Members HouseholdPriorMembers,
case when HGFP.ID is null then 0 else 1 end as HouseholdReturned
--, H.*

--, RX.*

from REGISTRANT R
inner join CONSTITUENT C on R.CONSTITUENTID = C.ID
inner join REGISTRANTEXTENSION RX on R.ID = RX.REGISTRANTID
left join REGISTRANTREGISTRATION RR on R.ID = RR.REGISTRANTID
left join REGISTRANT RP on R.CONSTITUENTID = RP.CONSTITUENTID and RP.EVENTID = @EventIDP
--left join RegistrantRoles ROLES on ROLES.REGISTRANTID = R.ID

outer apply (select top 1 * from RegistrantRoles rrr where rrr.REGISTRANTID = R.ID) ROLES    --(Make sure we only return 1 record)

outer apply UFN_FAF_GETTEAMCOMPANYHOUSEHOLDINFO_BY_REGISTRANT(@EventID, R.CONSTITUENTID) H
outer apply UFN_FAF_GETTEAMCOMPANYHOUSEHOLDINFO_BY_REGISTRANT(@EventIDP, R.CONSTITUENTID) HP
left join TEAMFUNDRAISINGTEAM T on T.ID = H.TEAMID
left join TEAMFUNDRAISINGTEAM TC on TC.ID = H.COMPANYID
left join TEAMFUNDRAISINGTEAM TH on TH.ID = H.HouseholdID
left join GroupFacts      TGF  on TGF.id  = H.TEAMID
left join PriorGroupFacts TGFP on TGFP.id = HP.TEAMID
left join GroupFacts      HGF  on HGF.id  = H.HouseholdID
left join PriorGroupFacts HGFP on HGFP.id = HP.HouseholdID
left join GroupFacts      CGF  on CGF.id  = H.COMPANYID
left join PriorGroupFacts CGFP on CGFP.id = HP.COMPANYID
left join TEAMEXTENSION TX  on TX.TEAMFUNDRAISINGTEAMID = H.TEAMID
left join TEAMEXTENSION TXC on TXC.TEAMFUNDRAISINGTEAMID = H.COMPANYID
left join TEAMEXTENSION TXH on TXH.TEAMFUNDRAISINGTEAMID = H.HouseholdID
where R.EVENTID = @EventID
--and TopLevel = ISNULL(@GroupType, TopLevel)

 -- site filter

 and
      (
        @SITEFILTERMODE = 0
        or exists(
            select 1 from dbo.UFN_SITEID_MAPFROM_EVENTID(R.EVENTID) EVENTSITE
            where EVENTSITE.SITEID in (select SITEID from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED))
           )
      )
 -- Check site security

    and
    exists(
         select 1 from dbo.UFN_SITEID_MAPFROM_EVENTID(R.EVENTID) EVENTSITE
        where (@ISADMIN = 1 or exists (select 1 from @SITESGRANTED SITESGRANTED  where SITESGRANTED.SITEID=[EVENTSITE].[SITEID] or (SITESGRANTED.SITEID is null and [EVENTSITE].[SITEID] is null))
  ))
and ( ISNULL( @GROUPID , T.ID ) = T.ID  OR ISNULL( @GROUPID , TC.ID ) = TC.ID OR ISNULL( @GROUPID , TH.ID ) = TH.ID 
OR ( ISNULL(@GROUPID, @GROUPID ) is null and ROLES.Role = 'Individual' ) )

order by TopLevel desc, H.COMPANYNAME, H.TEAMNAME, H.HouseholdName, isLeader desc, C.KEYNAME

end