USP_DATALIST_ADDRESSBOOKFAFGROUP

CREATE procedure dbo.USP_DATALIST_ADDRESSBOOKFAFGROUP
(
@CONSTITUENTID uniqueidentifier
,@EVENTID uniqueidentifier
)
as
    set nocount on;
  --need to get role

  DECLARE 
@IsIndependentHouseholdMember bit 
,@IsIndependentHouseholdLeader bit  
,@IsIndependentTeamMember bit  
,@IsIndependentTeamLeader bit   
,@IsCompanyHouseholdMember bit   
,@IsCompanyHouseholdLeader bit   
,@IsCompanyTeamHouseholdLeader bit   
,@IsCompanyTeamHouseholdMember bit
,@IsCompanyTeamMember bit   
,@IsCompanyTeamLeader bit   
,@IsCompanyLeader bit   
,@IsTeamHouseholdMember bit
,@IsTeamHouseholdLeader bit

EXEC dbo.USP_FAF_REGISTRANT_ROLE
@CONSTITUENTID = @CONSTITUENTID
,@EVENTID =@EVENTID
,@IsIndependentHouseholdMember  = @IsIndependentHouseholdMember OUTPUT
,@IsIndependentHouseholdLeader  = @IsIndependentHouseholdLeader OUTPUT
,@IsIndependentTeamMember  = @IsIndependentTeamMember OUTPUT
,@IsIndependentTeamLeader   = @IsIndependentTeamLeader OUTPUT
,@IsCompanyHouseholdMember   = @IsCompanyHouseholdMember OUTPUT
,@IsCompanyHouseholdLeader   = @IsCompanyHouseholdLeader OUTPUT
,@IsCompanyTeamHouseholdLeader   = @IsCompanyTeamHouseholdLeader OUTPUT
,@IsCompanyTeamHouseholdMember   = @IsCompanyTeamHouseholdMember OUTPUT
,@IsCompanyTeamMember   = @IsCompanyTeamMember OUTPUT
,@IsCompanyTeamLeader   = @IsCompanyTeamLeader OUTPUT
,@IsCompanyLeader   = @IsCompanyLeader OUTPUT
,@IsTeamHouseholdMember  = @IsTeamHouseholdMember OUTPUT
,@IsTeamHouseholdLeader  = @IsTeamHouseholdLeader OUTPUT


DECLARE @output table (
  GROUPNAME nvarchar(100)
  ,MEMBERCOUNT integer)

declare @TEAMID uniqueidentifier  

--return based on the role

-- show 'My Household'

IF (@IsIndependentHouseholdLeader = 1 
  OR @IsIndependentHouseholdMember = 1 
  OR @IsCompanyHouseholdLeader = 1
  OR @IsCompanyHouseholdMember = 1
  OR @IsCompanyTeamHouseholdLeader = 1
  OR @IsTeamHouseholdLeader = 1
  OR @IsTeamHouseholdMember = 1
  OR @IsCompanyTeamHouseholdMember = 1)
BEGIN
  INSERT INTO @OUTPUT
  SELECT  
  'My Household' AS [GROUPNAME],
  COUNT(*) AS [MemberCount]
  FROM dbo.UFN_FAF_HOUSEHOLDMEMBERLIST(@CONSTITUENTID, @EVENTID)
END

--show 'My Team' 

IF (@IsIndependentTeamLeader = 1
  OR @IsIndependentTeamMember = 1
  OR @IsCompanyTeamMember = 1
  OR @IsCompanyTeamLeader = 1)  
BEGIN
  INSERT INTO @OUTPUT
  SELECT 
  'My Team',
  COUNT(*)
  FROM dbo.UFN_FAF_TEAMMEMBERLIST(@CONSTITUENTID, @EVENTID)  -- team

END

--show 'My Team' for team household leaders and members

if (@IsTeamHouseholdLeader = 1
or @IsTeamHouseholdMember = 1
or @IsCompanyTeamHouseholdLeader = 1
or @IsCompanyTeamHouseholdMember = 1)
begin
  --get teamID of the team that household belongs to

   select @TEAMID = PARENTTEAMID
   from dbo.UFN_FAF_GETTEAMINFO_BY_REGISTRANT(@CONSTITUENTID, @EVENTID) -- 1 = team


   if @TEAMID IS NOT NULL
   begin
    insert into @OUTPUT
    select 
    'My Team',
    count(*)
    from dbo.UFN_FAF_TEAMMEMBERLIST_BY_TEAMID(@TEAMID)
    end
end

--Household leaders

IF (@IsCompanyHouseholdLeader = 1
  OR @IsCompanyHouseholdMember = 1
)
BEGIN
  INSERT INTO @OUTPUT
  SELECT 
  'Household Leaders',
  COUNT(*)
  FROM dbo.UFN_FAF_HOUSEHOLDLEADERLIST(@CONSTITUENTID, @EVENTID)
END

--Team Leaders    

IF(@IsCompanyTeamLeader = 1)
BEGIN
  -- insert team leaders across the company

  INSERT INTO @OUTPUT
  SELECT 
  'Team Leaders',
  COUNT(*)
  FROM dbo.UFN_TEAMLEADERLIST(@CONSTITUENTID, @EVENTID)  

   select @TEAMID = TEAMID
   from dbo.UFN_FAF_GETTEAMINFO_BY_REGISTRANT(@CONSTITUENTID, @EVENTID)

  if @TEAMID is not null
  begin
   --insert households directly under the team 

     insert into @OUTPUT
      select TFTHH.NAME, COUNT(*)
      from dbo.TEAMFUNDRAISINGTEAM TFT
      inner join dbo.TEAMFUNDRAISINGTEAM TFTHH
        on TFT.ID = TFTHH.PARENTTEAMID
      inner join dbo.TEAMEXTENSION TEHH
        on TFTHH.ID = TEHH.TEAMFUNDRAISINGTEAMID 
        and TEHH.TYPECODE = 3  --household

      inner join dbo.TEAMFUNDRAISINGTEAMMEMBER TFTM
        on TFTHH.ID = TFTM.TEAMFUNDRAISINGTEAMID
      where TFT.ID = @TEAMID   
      group by TFTHH.NAME

      UNION
      --also needs Household leaders under my team

      select 'Household Leaders', COUNT(*)
      from dbo.TEAMFUNDRAISINGTEAM TFTHH
      inner join TEAMEXTENSION TE  -- get those household

            on TFTHH.ID = TE.TEAMFUNDRAISINGTEAMID  
            and TE.TYPECODE = 3  
      inner join dbo.TEAMFUNDRAISINGTEAMCAPTAIN TC  --retrieve their leaders

            on TFTHH.ID = TC.TEAMFUNDRAISINGTEAMID
      inner join dbo.CONSTITUENT C
            on TC.CONSTITUENTID = C.ID  
      where TFTHH.PARENTTEAMID = @TEAMID 

  end
END 


--for company leaders only, 'My Individuals' and 'My Company', and 'Company Laders'

IF (@IsCompanyLeader = 1)
BEGIN

  INSERT INTO @OUTPUT
  SELECT 
  'My Individuals',
  COUNT(*)
  FROM dbo.UFN_FAF_COMPANYINDIVIDUALLIST(@CONSTITUENTID, @EVENTID)
  WHERE (@IsCompanyTeamLeader = 1)

  UNION 

  SELECT 
  'My Company',
  COUNT(*)
  FROM dbo.UFN_FAF_COMPANYMEMBERLIST(@CONSTITUENTID, @EVENTID)

  UNION 

  SELECT 
  'Company Leaders',
  COUNT(*)
  FROM dbo.UFN_FAF_COMPANYLEADERLIST(@EVENTID)

 --and insert teams and households under the company

   select @TEAMID = TEAMID
   from dbo.UFN_FAF_GETTEAMINFO_BY_REGISTRANT(@CONSTITUENTID, @EVENTID)

  if @TEAMID is not null
  begin
      insert into @OUTPUT
      select TFTHH.NAME, COUNT(*)
      from dbo.TEAMFUNDRAISINGTEAM TFT
      inner join dbo.TEAMFUNDRAISINGTEAM TFTHH
        on TFT.ID = TFTHH.PARENTTEAMID
      inner join dbo.TEAMEXTENSION TEHH
        on TFTHH.ID = TEHH.TEAMFUNDRAISINGTEAMID 
        and TEHH.TYPECODE in (1, 3)  --team and households

      inner join dbo.TEAMFUNDRAISINGTEAMMEMBER TFTM
        on TFTHH.ID = TFTM.TEAMFUNDRAISINGTEAMID
      where TFT.ID = @TEAMID  
      group by TFTHH.NAME

      UNION
  --team leaders under the company

      select 'Team Leaders', COUNT(*)
      from dbo.TEAMFUNDRAISINGTEAM TFTHH
      inner join TEAMEXTENSION TE  -- get those team

            on TFTHH.ID = TE.TEAMFUNDRAISINGTEAMID  
            and TE.TYPECODE = 1  
      inner join dbo.TEAMFUNDRAISINGTEAMCAPTAIN TC  --retrieve their leaders

            on TFTHH.ID = TC.TEAMFUNDRAISINGTEAMID
      inner join dbo.CONSTITUENT C
            on TC.CONSTITUENTID = C.ID  
      where TFTHH.PARENTTEAMID = @TEAMID 

  --Households, see above


    UNION
  --Company HH leaders

        select 'Household Leaders', COUNT(*)
      from dbo.TEAMFUNDRAISINGTEAM TFTHH
      inner join TEAMEXTENSION TE  -- get those household

            on TFTHH.ID = TE.TEAMFUNDRAISINGTEAMID  
            and TE.TYPECODE = 3  
      inner join dbo.TEAMFUNDRAISINGTEAMCAPTAIN TC  --retrieve their leaders

            on TFTHH.ID = TC.TEAMFUNDRAISINGTEAMID
      inner join dbo.CONSTITUENT C
            on TC.CONSTITUENTID = C.ID  
      where TFTHH.PARENTTEAMID = @TEAMID 

  end 
END

SELECT   
   GROUPNAME
  ,MEMBERCOUNT
FROM @OUTPUT