USP_DATALIST_FAFTRANSFERGIFTTOTEAMMEMBERLIST

      CREATE procedure dbo.USP_DATALIST_FAFTRANSFERGIFTTOTEAMMEMBERLIST  
              (             
                @CLIENTUSERSID int,  
             @EVENTID uniqueidentifier  
              )  
            as  
             set nocount on;  

    DECLARE @CONSTITUENTID uniqueidentifier  
             DECLARE @REGISTRANTID uniqueidentifier  
             DECLARE @TEAMID uniqueidentifier  
             DECLARE @TEAMTYPE tinyint  
             DECLARE @PARENTTEAMID uniqueidentifier

             DECLARE @DetailedRevenue table  
             (  
              ROLETYPE varchar(100),  
              GOAL money,  
              ACTUAL money,  
              NAME varchar(150),  
              CONSTITUENTID uniqueidentifier  
             )  

             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  

              SET @CONSTITUENTID = dbo.UFN_CONSTITUENT_GET_BY_CLIENTUSERID(@CLIENTUSERSID)   

             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  

             SELECT @REGISTRANTID = REGISTRANTID, @TEAMID = TEAMID, @TEAMTYPE = TEAMTYPECODE, @PARENTTEAMID = PARENTTEAMID  
             FROM dbo.UFN_FAF_GETTEAMINFO_BY_REGISTRANT(@CONSTITUENTID, @EVENTID) FNTEAM  

              -- Personal  

             BEGIN  
              INSERT INTO @DetailedRevenue (ROLETYPE,GOAL,ACTUAL,NAME,CONSTITUENTID)  
              SELECT   
               'Personal',  
               RE.TARGETFUNDRAISINGGOAL,  
               dbo.UFN_REVENUE_GETPARTICIPANTRAISEDTOTAL(@REGISTRANTID,@EVENTID),  
               C.NAME,  
               C.ID  
              FROM dbo.REGISTRANT R (NOLOCK)  
              INNER JOIN dbo.REGISTRANTEXTENSION RE (NOLOCK)  
              ON R.ID = RE.REGISTRANTID  
              INNER JOIN dbo.CONSTITUENT C (NOLOCK)  
              ON R.CONSTITUENTID = C.ID  
              WHERE R.ID = @REGISTRANTID  
             END  

             BEGIN  

              INSERT INTO @DetailedRevenue (ROLETYPE,GOAL,ACTUAL,NAME,CONSTITUENTID) 

              ---now a team can have more than one leader so team role type will reflect the users type  

   SELECT TFT.TEAMROLETYPE,  
              RE.TARGETFUNDRAISINGGOAL,  
              dbo.UFN_REVENUE_GETPARTICIPANTRAISEDTOTAL(TFT.REGISTRANTID,@EVENTID),  
              C.NAME,  
              C.ID 
              from dbo.UFN_FAF_CONSTITUENTS_BY_TEAMID(@TEAMID,@EVENTID) TFT  
              INNER JOIN dbo.REGISTRANT R (NOLOCK)  
              ON R.ID = TFT.REGISTRANTID  
              INNER JOIN dbo.REGISTRANTEXTENSION RE (NOLOCK)  
              ON R.ID = RE.REGISTRANTID  
              INNER JOIN dbo.CONSTITUENT C (NOLOCK)  
              ON R.CONSTITUENTID = C.ID  
              INNER JOIN dbo.TEAMFUNDRAISINGTEAM TEAMF              
              on TEAMF.ID = TFT.TEAMID
              WHERE R.ID <> @REGISTRANTID  

             END  

            ---if user is a leader and there is a subgroup, show the team leader for that subgroup, but not the members  

            IF @IsCompanyLeader = 1  OR @IsIndependentTeamLeader = 1 OR @IsCompanyTeamLeader = 1 
            OR @IsIndependentTeamMember = 1 OR @IsCompanyTeamMember = 1 OR @IsTeamHouseholdLeader = 1 
            OR @IsTeamHouseholdMember = 1
            BEGIN    
              ---return team info, but use the team leader constituent id; this is because the team leader should receive the transferred gift, not the team  

              INSERT INTO @DetailedRevenue (ROLETYPE,GOAL,ACTUAL,NAME,CONSTITUENTID)    

              select   
                TE.TYPE + ' Leader',  
                ---TFT.NAME + ' Leader',  

                TFT.GOAL as GOAL,  
                (select AMOUNTRAISED from dbo.UFN_FAF_GROUPTOTALSROLLUP(@EVENTID, TFT.ID)) as ACTUAL,   
                CHILDCONSTIT.NAME as NAME,  
                CHILDCONSTIT.ID as CONSTITUENTID  
              from dbo.TEAMFUNDRAISINGTEAM TFT (NOLOCK)    
                INNER JOIN dbo.TEAMEXTENSION TE ON TE.TEAMFUNDRAISINGTEAMID=TFT.ID --AND TE.TYPECODE=1    

                INNER JOIN dbo.TEAMFUNDRAISINGTEAMMEMBER M on M.TEAMFUNDRAISINGTEAMID = TFT.ID  
                INNER JOIN dbo.TEAMFUNDRAISER TF on M.TEAMFUNDRAISERID = TF.ID  
                INNER JOIN dbo.CONSTITUENT CHILDCONSTIT on CHILDCONSTIT.ID = TF.CONSTITUENTID  
                INNER JOIN dbo.TEAMFUNDRAISINGTEAMCAPTAIN TCAPT on TCAPT.CONSTITUENTID = CHILDCONSTIT.ID  
              WHERE TFT.PARENTTEAMID=@TEAMID  
              AND TE.STATUSCODE = 0  
            END 

            ---now if user is a sub captain, check for a parent team and show the leaders of the parent team

            IF (@PARENTTEAMID IS NOT null) 
            AND (@IsIndependentHouseholdLeader = 1
            OR @IsCompanyHouseholdLeader = 1
            OR @IsCompanyTeamHouseholdLeader = 1
            OR @IsCompanyTeamLeader = 1
            OR @IsTeamHouseholdLeader = 1)
             BEGIN    
              ---return team info, but use the team leader constituent id; this is because the team leader should receive the transferred gift, not the team  

              INSERT INTO @DetailedRevenue (ROLETYPE,GOAL,ACTUAL,NAME,CONSTITUENTID) 
                 SELECT TFT.TEAMROLETYPE,  
              RE.TARGETFUNDRAISINGGOAL,  
              dbo.UFN_REVENUE_GETPARTICIPANTRAISEDTOTAL(TFT.REGISTRANTID,@EVENTID),  
              C.NAME,  
              C.ID 
              from dbo.UFN_FAF_CONSTITUENTS_BY_TEAMID(@PARENTTEAMID,@EVENTID) TFT  
              INNER JOIN dbo.REGISTRANT R (NOLOCK)  
              ON R.ID = TFT.REGISTRANTID  
              INNER JOIN dbo.REGISTRANTEXTENSION RE (NOLOCK)  
              ON R.ID = RE.REGISTRANTID  
              INNER JOIN dbo.CONSTITUENT C (NOLOCK)  
              ON R.CONSTITUENTID = C.ID  
              INNER JOIN dbo.TEAMFUNDRAISINGTEAM TEAMF              
              on TEAMF.ID = TFT.TEAMID
              WHERE TFT.TEAMROLETYPE <> 'individual' ---if team capt on a company, the team capt should not see company individuals

             END 

              SELECT ROLETYPE ,  
                  GOAL ,  
                  ACTUAL ,  
                  NAME ,  
                  CONSTITUENTID   
              From @DetailedRevenue  
              ORDER BY ROLETYPE,NAME