USP_DATALIST_FAFDASHBOARDFUNDRAISING

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

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

                DECLARE @DetailedRevenue table
                (
                    ROLETYPE varchar(100),
                    GOAL money,
                    ACTUAL money,
                    NAME varchar(256),
                    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
                FROM dbo.UFN_FAF_GETTEAMINFO_BY_REGISTRANT(@CONSTITUENTID, @EVENTID) FNTEAM


                -- Household


                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 @DetailedRevenue (ROLETYPE,GOAL,ACTUAL,NAME,CONSTITUENTID)
                    Select 'Household',
                        TFT.GOAL,
                        (select AMOUNTRAISED from dbo.UFN_FAF_GROUPTOTALSROLLUP(@EVENTID, @TEAMID)),
                        TFT.NAME,
                        TE.TEAMCONSTITUENTID
                    from dbo.TEAMFUNDRAISINGTEAM TFT (NOLOCK)
                    INNER JOIN dbo.TEAMEXTENSION TE (NOLOCK) ON TE.TEAMFUNDRAISINGTEAMID = TFT.ID
                    WHERE TFT.ID = @TEAMID
                END

                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

                IF(@IsIndependentTeamLeader = 1 OR @IsCompanyTeamLeader = 1 OR @IsCompanyLeader = 1 OR @IsIndependentHouseholdLeader = 1 OR @IsCompanyHouseholdLeader = 1 OR @IsCompanyTeamHouseholdLeader = 1 OR @IsTeamHouseholdLeader = 1)
                BEGIN

                    INSERT INTO @DetailedRevenue (ROLETYPE,GOAL,ACTUAL,NAME,CONSTITUENTID)
                    SELECT (CASE TFT.TEAMTYPECODE  WHEN 1 THEN 'Teammember' WHEN 2 THEN 'Individual' WHEN 3 THEN 'Householdmember' ELSE '' END),
                    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
                    WHERE R.ID <> @REGISTRANTID

                    INSERT INTO @DetailedRevenue (ROLETYPE,GOAL,ACTUAL,NAME,CONSTITUENTID)
                    Select 'Household',
                        TE.TARGETFUNDRAISINGGOAL,
                        (select AMOUNTRAISED from dbo.UFN_FAF_GROUPTOTALSROLLUP(@EVENTID, TFT.ID)),
                        TFT.NAME,
                        TE.TEAMCONSTITUENTID
                    from dbo.TEAMFUNDRAISINGTEAM TFT (NOLOCK)
                    INNER JOIN dbo.TEAMEXTENSION TE (NOLOCK) ON TE.TEAMFUNDRAISINGTEAMID = TFT.ID
                    WHERE TFT.PARENTTEAMID = @TEAMID AND TE.TYPECODE=3

                    IF @TEAMTYPE = 1
                    begin
                        INSERT INTO @DetailedRevenue (ROLETYPE,GOAL,ACTUAL,NAME,CONSTITUENTID)
                        SELECT 'Total',
                                TFT.GOAL,
                                (select AMOUNTRAISED from dbo.UFN_FAF_GROUPTOTALSROLLUP(@EVENTID, @TEAMID)),
                                TFT.NAME,
                                TE.TEAMCONSTITUENTID
                        FROM dbo.TEAMFUNDRAISINGTEAM TFT (NOLOCK)
                        INNER JOIN dbo.TEAMEXTENSION TE (NOLOCK) ON TFT.ID = TE.TEAMFUNDRAISINGTEAMID
                        WHERE TFT.ID = @TEAMID
                    end
                END

                IF @IsCompanyLeader = 1
                BEGIN
                    INSERT INTO @DetailedRevenue (ROLETYPE,GOAL,ACTUAL,NAME,CONSTITUENTID)
                    select 'Team',
                        TFT.GOAL,
                        (select AMOUNTRAISED from dbo.UFN_FAF_GROUPTOTALSROLLUP(@EVENTID, TFT.ID)),
                        TFT.NAME,
                        TE.TEAMCONSTITUENTID
                    from dbo.TEAMFUNDRAISINGTEAM TFT (NOLOCK)
                    INNER JOIN dbo.TEAMEXTENSION TE ON TE.TEAMFUNDRAISINGTEAMID = TFT.ID
                    WHERE TFT.PARENTTEAMID = @TEAMID AND TE.STATUSCODE = 0 AND TE.TYPECODE = 1

                    INSERT INTO @DetailedRevenue (ROLETYPE,GOAL,ACTUAL,NAME,CONSTITUENTID)
                    SELECT 'Total',
                            TFT.GOAL,
                            (select AMOUNTRAISED from dbo.UFN_FAF_GROUPTOTALSROLLUP(@EVENTID, @TEAMID)),
                            TFT.NAME,
                            TE.TEAMCONSTITUENTID
                    FROM dbo.TEAMFUNDRAISINGTEAM TFT (NOLOCK)
                    INNER JOIN dbo.TEAMEXTENSION TE (NOLOCK) ON TFT.ID = TE.TEAMFUNDRAISINGTEAMID
                    WHERE TFT.ID = @TEAMID
                END

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