USP_REPORT_FAFTOPLEVELFUNDRAISERSREPORT

Reruns Top Level Fundraisers
Parameters

Parameter	Parameter Type	Mode
@EventID	uniqueidentifier	IN
@GroupTypes	int	IN
@WEBURL	nvarchar(1200)	IN
@WEBURLGROUP	nvarchar(1200)	IN
@REPORTUSERID	nvarchar(128)	IN
@ALTREPORTUSERID	nvarchar(128)	IN
@SITEFILTERMODE	tinyint	IN
@SITESSELECTED	nvarchar(max)	IN
Definition

Copy


CREATE procedure dbo.USP_REPORT_FAFTOPLEVELFUNDRAISERSREPORT
(
    @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 
)
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,'8d77e177-2098-465a-a03b-d6a25332d681', 21)

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

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

declare  @GroupFacts as table (id uniqueidentifier, Members int, MembersRetained int, PctMembersRetained float, Teams int, TeamsRetained int, PctTeamsRetained float, CommsSent int)
insert into @GroupFacts (id, Members, MembersRetained, PctMembersRetained, Teams, TeamsRetained, PctTeamsRetained, CommsSent)
--select distinct ID, MEMBERCOUNT, MEMBERCOUNT - MEMBERNEWCOUNT, PARTMEMBERCOUNTRETAINED, SUBGROUPCOUNT, SUBGROUPRETAINEDCOUNT, PARCSUBGROUPRETAINEDCOUNT, COMMUNICATIONSENT

select distinct ID, MEMBERNUMBER Members, MEMBERNUMBER - MEMBERNUMBERNEW MembersRetained, PARTMEMBERNUMBERRETAINED PctMembersRetained, SUBGROUPNUMBER Teams, SUBGROUPRETAINEDNUMBER TeamsRetained, PARTOFSUBGROUPRETAINED PctTeamsRetained, COMMUNICATIONSENTACTUAL CommsSent
from [V_QUERY_REGISTRANT_FUNDRAISINGGROUP]
where EVENTID = @EventID

declare  @PriorGroupFacts as table (id uniqueidentifier, Members int, MembersRetained int, PctMembersRetained float, Teams int, TeamsRetained int, PctTeamsRetained float, CommsSent int)
insert into @PriorGroupFacts (id, Members, MembersRetained, PctMembersRetained, Teams, TeamsRetained, PctTeamsRetained, CommsSent)
--select distinct ID, MEMBERCOUNT, MEMBERCOUNT - MEMBERNEWCOUNT, PARTMEMBERCOUNTRETAINED, SUBGROUPCOUNT, SUBGROUPRETAINEDCOUNT, PARCSUBGROUPRETAINEDCOUNT, COMMUNICATIONSENT

select distinct ID, MEMBERNUMBER Members, MEMBERNUMBER - MEMBERNUMBERNEW MembersRetained, PARTMEMBERNUMBERRETAINED PctMembersRetained, SUBGROUPNUMBER Teams, SUBGROUPRETAINEDNUMBER TeamsRetained, PARTOFSUBGROUPRETAINED PctTeamsRetained, COMMUNICATIONSENTACTUAL CommsSent
from [V_QUERY_REGISTRANT_FUNDRAISINGGROUP]
where EVENTID = @EventIDP

declare @IncludeCompany as int    set @IncludeCompany =    case when @GroupTypes = 4 then 1 when @GroupTypes = 2 then 1 else 0  end 
declare @IncludeTeam as int       set @IncludeTeam =       case when @GroupTypes = 4 then 1 when @GroupTypes = 1 then 1 else 0  end 
declare @IncludeIndividual as int set @IncludeIndividual = case when @GroupTypes = 4 then 1 when @GroupTypes = 0 then 1 else 0  end 
declare @IncludeHousehold as int  set @includeHousehold =  case when @GroupTypes = 4 then 1 when @GroupTypes = 3 then 1 else 0  end 

--

-- Orphan Individuals --

--

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,

'' as GROUPID,
C.NAME, R.CONSTITUENTID as ConstituentID,
Case when RP.ID is null then 0 else 1 end as Returner,
'Individual' as Type, 0 as TypeCode,
RR.DATEPURCHASED as RegistrationDate,
dbo.UFN_REVENUE_GETPARTICIPANTRAISEDTOTAL(R.ID, R.EVENTID) FundsRaised,
RX.TARGETFUNDRAISINGGOAL FundsGoal,
dbo.UFN_FAFGETTOTALCOMMUNICATIONS(R.EVENTID, dbo.UFN_CLIENTUSERID_GET_BY_CONSTITUENT(R.CONSTITUENTID)) CommsSent,
RX.COMMUNICATIONGOAL CommsGoal,
null Teams,
null TeamsGoal,
null TeamsPrior,
null TeamsRetained,
null TeamsPctRetained,
null TeamsRetentionGoal,
null TeamsToRetain,
null TeamMembers,
null TeamMembersGoal,
null TeamMembersPrior,
null TeamMembersRetained,
null TeamMembersPctRetained,
null TeamMemberRetentionGoal,
null TeamMembersToRetain 
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 RR.REGISTRANTID = R.ID
cross apply UFN_FAF_GETTEAMCOMPANYHOUSEHOLDINFO_BY_REGISTRANT(R.EVENTID, R.CONSTITUENTID) H
left join REGISTRANT RP on RP.CONSTITUENTID = R.CONSTITUENTID and RP.EVENTID = @EventIDP
where R.EVENTID = @EventID
and H.COMPANYID is null 
and H.TEAMID is null
and H.HouseholdID is null
and R.EVENTID = @EventID
and 1 = @IncludeIndividual
  -- 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))
  ))
union
--

-- Orphan Teams --

--

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

TFT.NAME, TX.TEAMCONSTITUENTID as ConstituentID,
Case when TXP.ID is null then 0 else 1 end as Returner,
TX.TYPE as Type, 1 as TypeCode,
TFT.DATEADDED as RegistrationDate,
dbo.UFN_REVENUE_GETTEAMRAISEDTOTAL(TFT.ID, TX.EVENTID) FundsRaised,
TFT.ORGANIZATIONGOAL, --TX.TARGETFUNDRAISINGGOAL,

GF.CommsSent CommsSent,
TX.COMMUNICATIONGOAL CommsGoal,
null Teams,
null TeamsGoal,
null TeamsPrior,
null TeamsRetained,
null TeamsPctRetained,
null TeamsRetentionGoal,
null TeamsToRetain,
GF.Members TeamMembers,
TX.TEAMMEMBERGOAL TeamMembersGoal,
PGF.Members TeamMembersPrior,
GF.MembersRetained TeamMembersRetained,
GF.PctMembersRetained TeamMembersPctRetained,
TX.PCTTEAMMEMBERRETENSION TeamMemberRetentionGoal,
PGF.Members * TX.PCTTEAMMEMBERRETENSION TeamMembersToRetain  
from TEAMFUNDRAISINGTEAM TFT
inner join TEAMEXTENSION TX on TX.TEAMFUNDRAISINGTEAMID = TFT.ID
cross apply UFN_FAF_GETTEAMHIEARACHY(TFT.ID) TH
left join TEAMEXTENSION TXP on TXP.TEAMCONSTITUENTID = TX.TEAMCONSTITUENTID and TXP.EVENTID = @EventIDP
left join @GroupFacts as GF on GF.ID = TFT.ID
left join @PriorGroupFacts as PGF on PGF.ID = TFT.ID
where TX.TYPECODE = 1 
and TH.ParentTeamID is null
and TX.EVENTID = @EventID
and 1 = @IncludeTeam
 -- site filter

 and
      (
        @SITEFILTERMODE = 0
        or exists(
            select 1 from dbo.UFN_SITEID_MAPFROM_EVENTID(TX.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(TX.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))
  ))
union
--

-- I guess Companies are never orphans, but always included --

--

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

TFT.NAME, TX.TEAMCONSTITUENTID as ConstituentID,
Case when TXP.ID is null then 0 else 1 end as Returner,
TX.TYPE as Type, 2 as TypeCode,
TFT.DATEADDED as RegistrationDate,
dbo.UFN_REVENUE_GETCOMPANYRAISEDTOTAL(TFT.ID, TX.EVENTID) FundsRaised,
TFT.ORGANIZATIONGOAL, --TX.TARGETFUNDRAISINGGOAL,

GF.CommsSent CommsSent,
TX.COMMUNICATIONGOAL CommsGoal,
GF.Teams Teams,
TX.TEAMRECRUITMENTGOAL TeamsGoal,
PGF.Teams TeamsPrior,
GF.TeamsRetained TeamsRetained,
GF.PctTeamsRetained TeamsPctRetained,
TX.PCTTEAMSRETENSION TeamsRetentionGoal,
PGF.Teams * TX.PCTTEAMSRETENSION TeamsToRetain,
GF.Members TeamMembers,
TX.TEAMMEMBERGOAL TeamMembersGoal,
PGF.Members TeamMembersPrior,
GF.MembersRetained TeamMembersRetained,
GF.PctTeamsRetained TeamMembersPctRetained,
TX.PCTTEAMMEMBERRETENSION TeamMemberRetentionGoal,
PGF.Members * TX.PCTTEAMMEMBERRETENSION TeamMembersToRetain  
from TEAMFUNDRAISINGTEAM TFT
inner join TEAMEXTENSION TX on TX.TEAMFUNDRAISINGTEAMID = TFT.ID
cross apply UFN_FAF_GETTEAMHIEARACHY(TFT.ID) TH
left join TEAMEXTENSION TXP on TXP.TEAMCONSTITUENTID = TX.TEAMCONSTITUENTID and TXP.EVENTID = @EventIDP
left join @GroupFacts as GF on GF.ID = TFT.ID
left join @PriorGroupFacts as PGF on PGF.ID = TFT.ID
where TX.TYPECODE = 2
and TX.EVENTID = @EventID
and 1 = @IncludeCompany
 -- site filter

 and
      (
        @SITEFILTERMODE = 0
        or exists(
            select 1 from dbo.UFN_SITEID_MAPFROM_EVENTID(TX.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(TX.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))
  ))
union
--

-- Orphan Households --

--

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

TFT.NAME, TX.TEAMCONSTITUENTID as ConstituentID,
Case when TXP.ID is null then 0 else 1 end as Returner,
TX.TYPE as Type, 3 as TypeCode,
TFT.DATEADDED as RegistrationDate,
dbo.UFN_REVENUE_GETHOUSEHOLDRAISEDTOTAL(TFT.ID, TX.EVENTID) FundsRaised,
TX.TARGETFUNDRAISINGGOAL,
GF.CommsSent CommsSent,
TX.COMMUNICATIONGOAL CommsGoal,
null Teams,
null TeamsGoal,
null TeamsPrior,
null TeamsRetained,
null TeamPctRetained,
null TeamsRetentionGoal,
null TeamsToRetain,
null TeamMembers,
TX.TEAMMEMBERGOAL TeamMembersGoal,
null TeamMembersPrior,
null TeamMembersRetained,
null TeamMembersPctRetained,
null TeamMemberRetentionGoal,
null TeamMembersToRetain  
from TEAMFUNDRAISINGTEAM TFT
inner join TEAMEXTENSION TX on TX.TEAMFUNDRAISINGTEAMID = TFT.ID
cross apply UFN_FAF_GETTEAMHIEARACHY(TFT.ID) TH
left join TEAMEXTENSION TXP on TXP.TEAMCONSTITUENTID = TX.TEAMCONSTITUENTID and TXP.EVENTID = @EventIDP
left join @GroupFacts as GF on GF.ID = TFT.ID
where TX.TYPECODE = 3 
and TH.ParentTeamID is null
and TX.EVENTID = @EventID
and 1 = @IncludeHousehold
 -- site filter

 and
      (
        @SITEFILTERMODE = 0
        or exists(
            select 1 from dbo.UFN_SITEID_MAPFROM_EVENTID(TX.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(TX.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))
  ))
order by Type, Name

end