USP_REPORT_FAFLAPSEDFUNDRAISINGGROUPS

CREATE procedure dbo.USP_REPORT_FAFLAPSEDFUNDRAISINGGROUPS
(
    @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,'24ec10b8-ba27-4700-bddc-6a66de2819fd', 21)

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


  declare @CURRENTEVENTID as uniqueidentifier 
 set @CURRENTEVENTID = @EVENTID;

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

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 = @EventIDP

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 = @EventIDPP

DECLARE @PreviousEventName nvarchar(200)  
SET @PreviousEventName = (SELECT NAME FROM Event WHERE ID = @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,
@PreviousEventName as PreviousEventName
from (select * from REGISTRANT where EVENTID = @EventIDP and CONSTITUENTID not in (select CONSTITUENTID from REGISTRANT where EVENTID = @EventID)) 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 = @EventIDPP
where 1 = 1 
--and R.EVENTID = @EventIDP

and H.COMPANYID is null 
and H.TEAMID is null
and H.HouseholdID is null
and 1 = @IncludeIndividual
  -- site filter  

 and  
      (  
        @SITEFILTERMODE = 0  
        or exists(  
            select 1 from dbo.UFN_SITEID_MAPFROM_EVENTID(@CURRENTEVENTID) 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(@CURRENTEVENTID) 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, 2 as TypeCode,
TFT.DATEADDED as RegistrationDate,
dbo.UFN_REVENUE_GETTEAMRAISEDTOTAL(TFT.ID, TX.EVENTID) FundsRaised,
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,
PGF.Members TeamMembersPrior,
TX.TEAMMEMBERGOAL TeamMembersGoal,
GF.MembersRetained TeamMembersRetained,
GF.PctMembersRetained TeamMembersPctRetained,
TX.PCTTEAMMEMBERRETENSION TeamMemberRetentionGoal,
PGF.Members * TX.PCTTEAMMEMBERRETENSION TeamMembersToRetain,
@PreviousEventName AS PreviousEventName
--from TEAMFUNDRAISINGTEAM TFT

from (select * from TEAMFUNDRAISINGTEAM where ID in (select TEAMFUNDRAISINGTEAMID from TEAMEXTENSION where EVENTID = @EventIDP and TEAMCONSTITUENTID not in (select TEAMCONSTITUENTID from TEAMEXTENSION where EVENTID = @EventID))) 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 = @EventIDPP
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(@CURRENTEVENTID) 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(@CURRENTEVENTID) 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, 3 as TypeCode,
TFT.DATEADDED as RegistrationDate,
dbo.UFN_REVENUE_GETCOMPANYRAISEDTOTAL(TFT.ID, TX.EVENTID) FundsRaised,
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,
@PreviousEventName AS PreviousEventName
--from TEAMFUNDRAISINGTEAM TFT

from (select * from TEAMFUNDRAISINGTEAM where ID in (select TEAMFUNDRAISINGTEAMID from TEAMEXTENSION where EVENTID = @EventIDP and TEAMCONSTITUENTID not in (select TEAMCONSTITUENTID from TEAMEXTENSION where EVENTID = @EventID))) 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 = @EventIDPP
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 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, 1 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,
null TeamMembersGoal,
null TeamMembersPrior,
null TeamMembersRetained,
null TeamMembersPctRetained,
null TeamMemberRetentionGoal,
null TeamMembersToRetain,
@PreviousEventName AS PreviousEventName
--from TEAMFUNDRAISINGTEAM TFT

from (select * from TEAMFUNDRAISINGTEAM where ID in (select TEAMFUNDRAISINGTEAMID from TEAMEXTENSION where EVENTID = @EventIDP and TEAMCONSTITUENTID not in (select TEAMCONSTITUENTID from TEAMEXTENSION where EVENTID = @EventID))) 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 = @EventIDPP
left join @GroupFacts as GF on GF.ID = TFT.ID
where TX.TYPECODE = 3 
and TH.ParentTeamID is null
and 1 = @IncludeHousehold
  -- site filter  

 and  
      (  
        @SITEFILTERMODE = 0  
        or exists(  
            select 1 from dbo.UFN_SITEID_MAPFROM_EVENTID(@CURRENTEVENTID) 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(@CURRENTEVENTID) 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 TypeCode Desc, Name

end