USP_FAFADDRESSBOOK_GETALLGROUPMEMBERS

CREATE procedure dbo.USP_FAFADDRESSBOOK_GETALLGROUPMEMBERS
(
     @TYPE               varchar(10),
     @EVENTID            uniqueidentifier, 
     @CONSTITUENTID      uniqueidentifier
)
as
Begin
       -- get role for current event

     DECLARE @IsIndependentHouseholdMember bit     
             ,@IsIndependentHouseholdLeader bit      
             ,@IsIndependentTeamMember bit      
             ,@IsIndependentTeamLeader bit       
             ,@IsCompanyHouseholdMember bit       
             ,@IsCompanyHouseholdLeader bit       
             ,@IsCompanyTeamHouseholdLeader bit       
             ,@IsCompanyTeamMember bit       
             ,@IsCompanyTeamLeader bit       
             ,@IsCompanyLeader bit       
             ,@IsCompanyTeamHouseholdMember 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    
               ,@IsCompanyTeamMember   = @IsCompanyTeamMember OUTPUT    
               ,@IsCompanyTeamLeader   = @IsCompanyTeamLeader OUTPUT    
               ,@IsCompanyLeader   = @IsCompanyLeader OUTPUT    
               ,@IsCompanyTeamHouseholdMember = @IsCompanyTeamHouseholdMember OUTPUT    
               ,@IsTeamHouseholdMember = @IsTeamHouseholdMember OUTPUT    
               ,@IsTeamHouseholdLeader = @IsTeamHouseholdLeader OUTPUT    


       -- get members

       DECLARE @CompanyLeaderGroup     varchar(40)
       DECLARE @TeamMemberGroup        varchar(40)
       DECLARE @TeamLeaderGroup        varchar(40)
       DECLARE @DonorGroup             varchar(40)
       DECLARE @ContactGroup          varchar(40)
       DECLARE @IndividualGroup        varchar(40)
       DECLARE @CompanyLeadGroup       varchar(40)
       DECLARE @CompanyMemberGroup     varchar(40)
       DECLARE @HouseholdMemberGroup   varchar(40)
       DECLARE @HouseholdLeaderGroup   varchar(40)
       DECLARE @TypeText               varchar(20)

       If @Type = 'Current' 
          Begin
               Set @CompanyLeaderGroup = 'Company leaders - current'
               Set @TeamMemberGroup = 'Team members'
               Set @TeamLeaderGroup = 'Team leaders'
               Set @DonorGroup = 'Donors - current' 
               Set @ContactGroup = 'Contacts'
               Set @IndividualGroup = 'Individuals - current'
               Set @CompanyLeadGroup = 'Company leaders - current'
               Set @CompanyMemberGroup = 'Company members - current'
               Set @HouseholdMemberGroup = 'household members - current'
               Set @HouseholdLeaderGroup = 'household leaders - current'  
               Set @TypeText =  ' - current' 
          End
       Else
          Begin
               Set @CompanyLeaderGroup = 'Company leaders - Previous'
               Set @TeamMemberGroup = 'Team members - Previous'
               Set @TeamLeaderGroup = 'Team leaders - Previous'
               Set @DonorGroup = 'Donors - Previous' 
               Set @ContactGroup = 'Contacts'
               Set @IndividualGroup = 'Individuals - Previous'
               Set @CompanyLeadGroup = 'Company leaders - Previous'
               Set @CompanyMemberGroup = 'Company members - Previous'
           Set @HouseholdMemberGroup = 'household members - Previous'
               Set @HouseholdLeaderGroup = 'household leaders - Previous' 
               Set @TypeText =  ' - Previous' 
          End

       DECLARE @MYADDRESSBOOK table (    
             GROUPNAME        nvarchar(100),    
             CATEGORYSTATUS   nvarchar(100),    
             ADDRESSBOOKID    uniqueidentifier null,    
             NAME             varchar(200) null,    
             CONSTITUENTID    uniqueidentifier null,    
             EMAILADDRESS     varchar(200) null,    
             REGISTRANTID     uniqueidentifier null,
             ALLOWOTHERPARTICIPANTSCONTACTME bit  default(1)
      ) 

      Declare @TEAMID uniqueidentifier, @COMPANYID uniqueidentifier    

      -- add team members

      IF (@IsIndependentTeamLeader = 1 OR @IsIndependentTeamMember = 1    
             OR @IsCompanyTeamMember = 1 OR @IsCompanyTeamLeader = 1)   

       BEGIN    
         INSERT INTO @MYADDRESSBOOK (GROUPNAME, NAME, CONSTITUENTID)    
         SELECT @TeamMemberGroup, Name, ID    
         FROM dbo.UFN_FAF_TEAMMEMBERLIST(@CONSTITUENTID, @EVENTID)    -- 1=team


         -- change household leader group name

         Update MA Set GROUPNAME = @HouseholdLeaderGroup
            from @MYADDRESSBOOK MA Inner Join dbo.UFN_REGISTRANT_GETFAFROLE(@EVENTID, null) r
              On MA.CONSTITUENTID = r.CONSTITUENTID and r.RoleCode = 3


         --don't get the team leaders group here for @IsCompanyTeamLeader, as it has special business logic,

         --and is retrieved later (see --company Team Leaders) section

         IF @IsCompanyTeamLeader = 0
         BEGIN
             INSERT INTO @MYADDRESSBOOK (GROUPNAME, NAME, CONSTITUENTID)    
             SELECT @TeamLeaderGroup, Name, ID    
             FROM dbo.UFN_INDEPENDENTTEAMLEADERLIST(@CONSTITUENTID, @EVENTID)  -- 1 = team    

         END

       END

        --show 'My Team' for company team HH leader & team HH leader    

      If (@IsCompanyTeamHouseholdLeader = 1  OR @IsTeamHouseholdLeader = 1)    
        Begin    
             SELECT @TEAMID = PARENTTEAMID    
             FROM dbo.UFN_FAF_GETTEAMINFO_BY_REGISTRANT(@CONSTITUENTID, @EVENTID)   

             INSERT INTO @MYADDRESSBOOK (GROUPNAME, NAME, CONSTITUENTID)      
             SELECT   
                 CASE   
                   WHEN TFTC.ID IS NULL THEN @TeamMemberGroup  
                   WHEN TFTC.ID IS NOT NULL THEN @TeamLeaderGroup  
                 END,   
                 Name,  
                 TML.ID  
             FROM dbo.UFN_FAF_TEAMMEMBERLIST_BY_TEAMID(@TEAMID) TML  
             LEFT OUTER JOIN TEAMFUNDRAISINGTEAMCAPTAIN TFTC ON TML.TEAMID = TFTC.TEAMFUNDRAISINGTEAMID AND TFTC.CONSTITUENTID = TML.ID  
        End 

      --Household leaders    

      IF (@IsCompanyHouseholdLeader = 1 OR @IsCompanyHouseholdMember = 1 OR @IsCompanyTeamHouseholdLeader = 1    
             OR @IsCompanyTeamHouseholdMember = 1 OR @IsTeamHouseholdMember = 1 OR @IsTeamHouseholdLeader = 1    
             OR @IsIndependentHouseholdMember = 1 OR @IsIndependentHouseholdLeader = 1    
      )    
      BEGIN    
        INSERT INTO  @MYADDRESSBOOK (GROUPNAME, NAME, CONSTITUENTID)    
        SELECT     
            @HouseholdLeaderGroup,    
            name,    
            ID    
        FROM dbo.UFN_FAF_HOUSEHOLDLEADERLIST(@CONSTITUENTID, @EVENTID)    
      END

      --Household Members    

     IF (@IsCompanyHouseholdLeader = 1 OR @IsCompanyHouseholdMember = 1 OR @IsCompanyTeamHouseholdLeader = 1    
           OR @IsCompanyTeamHouseholdMember = 1 OR @IsTeamHouseholdMember = 1 OR @IsTeamHouseholdLeader = 1    
           OR @IsIndependentHouseholdMember = 1 OR @IsIndependentHouseholdLeader = 1    
     )    
     BEGIN    
       INSERT INTO @MYADDRESSBOOK (GROUPNAME, NAME, CONSTITUENTID)    
       SELECT @HouseholdMemberGroup, Name, ID    
       FROM dbo.UFN_FAF_HOUSEHOLDMEMBERLIST(@CONSTITUENTID, @EVENTID)    
        --WHERE ID <> @CONSTITUENTID    

     END 

     --company Team Leaders, need to add "team leaders across"+households under the team + HH leaders across    

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

          /*
          INSERT INTO @MYADDRESSBOOK (GROUPNAME, NAME, CONSTITUENTID)    
          SELECT     
              @TeamLeaderGroup,    
              name,     
              ID    
          FROM dbo.UFN_TEAMLEADERLIST(@CONSTITUENTID, @EVENTID)    
          */
          --where ID <> @CONSTITUENTID    


          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 @MYADDRESSBOOK (GROUPNAME, NAME, CONSTITUENTID)    
              select 'Household - ' + TFTC.NAME, FN.Name, FN.id     
              from dbo.TEAMFUNDRAISINGTEAM TFT    
              inner join dbo.TEAMFUNDRAISINGTEAM TFTC    
                on TFT.ID = TFTC.PARENTTEAMID    
              inner join dbo.TEAMEXTENSION TEHH    
                on TFTC.ID = TEHH.TEAMFUNDRAISINGTEAMID     
                and TEHH.TYPECODE =3  --households    

              cross apply dbo.UFN_FAF_HOUSEHOLDMEMBERLIST_BY_HOUSEHOLDID(TFTC.ID) FN    
              where TFT.ID = @TEAMID      

              --also needs Household leaders under my team    

             insert into @MYADDRESSBOOK (GROUPNAME, NAME, CONSTITUENTID)    
              select @HouseholdLeaderGroup, C.name, C.ID    
              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    
           print @CONSTITUENTID
           INSERT INTO @MYADDRESSBOOK (GROUPNAME, NAME, CONSTITUENTID)    
           SELECT     
               @IndividualGroup, Name, ID    
           FROM dbo.UFN_FAF_COMPANYINDIVIDUALLIST(@CONSTITUENTID, @EVENTID)    

           -- add company co-leader

           UNION     

           SELECT @CompanyLeaderGroup, C.Name As Name, C.ID As ID 
              From dbo.CONSTITUENT C 
                  Inner Join dbo.TEAMFUNDRAISINGTEAMCAPTAIN TC 
                      On C.ID = TC.CONSTITUENTID
                  Inner Join   
                           (Select TM.NAME as COMPANYNAME, TM.ID as COMPANYID, TC.CONSTITUENTID 
                              from dbo.TEAMEXTENSION TE    
                              INNER JOIN dbo.TEAMFUNDRAISINGTEAM TM  
                                  ON TE.TEAMFUNDRAISINGTEAMID = TM.ID  
                              INNER JOIN dbo.TEAMFUNDRAISINGTEAMCAPTAIN TC  --retrieve team leaders  

                                  ON TE.TEAMFUNDRAISINGTEAMID = TC.TEAMFUNDRAISINGTEAMID  
                             WHERE TE.EVENTID = @EVENTID
                                and TC.CONSTITUENTID = @CONSTITUENTID) TTCC    
                   On TTCC.COMPANYID = TC.TEAMFUNDRAISINGTEAMID

           UNION     

           -- team leaders, household leaders under this company    

           SELECT 
                groupname + @TypeText, Name, ID    
           FROM dbo.UFN_FAF_COMPANYTEAMLEADERLIST(@CONSTITUENTID, @EVENTID)    

           UNION    

            SELECT @CompanyMemberGroup, Name, ID    
            FROM dbo.UFN_FAF_COMPANYMEMBERLIST(@CONSTITUENTID, @EVENTID)
            WHERE ID NOT IN (SELECT ID FROM DBO.UFN_FAF_COMPANYLEADERLIST(@EVENTID))

             --and insert teams and households under the company        

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

                 print @COMPANYID

             if(@COMPANYID is not null)    
             begin    
                      Insert into @MYADDRESSBOOK (GROUPNAME, CATEGORYSTATUS, NAME, CONSTITUENTID) 
                      Select 
                          GROUPNAME,
                          CASE   
                              WHEN TFTC.ID IS NULL THEN @TeamMemberGroup 
                              WHEN TFTC.ID IS NOT NULL THEN @TeamLeaderGroup  
                          END as CATEGORYSTATUS,
                          NAME, 
                          TML.ID from (   
                              select 'Team - '+ TFTC.NAME as GROUPNAME, FN.Name, FN.id AS ID, TFTC.ID AS TEAMID     
                              from dbo.TEAMFUNDRAISINGTEAM TFT    
                              inner join dbo.TEAMFUNDRAISINGTEAM TFTC    
                                  on TFT.ID = TFTC.PARENTTEAMID    
                              inner join dbo.TEAMEXTENSION TEHH    
                                  on TFTC.ID = TEHH.TEAMFUNDRAISINGTEAMID     
                                  and TEHH.TYPECODE =1  --team    

                              cross apply dbo.UFN_FAF_TEAMMEMBERLIST_BY_TEAMID(TFTC.ID) FN    
                              where TFT.ID = @COMPANYID      
                          ) TML
                          LEFT OUTER JOIN TEAMFUNDRAISINGTEAMCAPTAIN TFTC 
                              ON TML.TEAMID = TFTC.TEAMFUNDRAISINGTEAMID 
                              AND TFTC.CONSTITUENTID = TML.ID  

                      Insert into @MYADDRESSBOOK (GROUPNAME, CATEGORYSTATUS, NAME, CONSTITUENTID)    
                      SELECT
                          GROUPNAME,
                          CASE   
                              WHEN TFTC.ID IS NULL THEN @HouseholdMemberGroup 
                              WHEN TFTC.ID IS NOT NULL THEN @HouseholdLeaderGroup  
                          END as CATEGORYSTATUS,
                          NAME, 
                          TML.ID from (         
                              select 'Household - ' + TFTC.NAME AS GROUPNAME, FN.Name, FN.id AS ID, TFTC.ID AS TEAMID      
                              from dbo.TEAMFUNDRAISINGTEAM TFT    
                              inner join dbo.TEAMFUNDRAISINGTEAM TFTC    
                                  on TFT.ID = TFTC.PARENTTEAMID    
                              inner join dbo.TEAMEXTENSION TEHH    
                                  on TFTC.ID = TEHH.TEAMFUNDRAISINGTEAMID     
                                  and TEHH.TYPECODE =3  --households    

                              cross apply dbo.UFN_FAF_HOUSEHOLDMEMBERLIST_BY_HOUSEHOLDID(TFTC.ID) FN    
                              where TFT.ID = @COMPANYID      
                          ) TML
                          LEFT OUTER JOIN TEAMFUNDRAISINGTEAMCAPTAIN TFTC 
                              ON TML.TEAMID = TFTC.TEAMFUNDRAISINGTEAMID 
                              AND TFTC.CONSTITUENTID = TML.ID  
             end    
        END 

       -- update group name for previous event

      if @TYPE='Previous'
      begin
         update @MYADDRESSBOOK set groupname = groupname + ' - Previous'
           where groupname not like '%Previous'
      end

      -- return all data

      Select ab.* from @MYADDRESSBOOK ab inner join Registrant r on r.constituentID = ab.constituentID
        where r.EventId = @EVENTID
End