USP_DATALIST_FAF_GIFT_ACKNOWLEDGEMENT_TEAM

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

    DECLARE @TEAMCONSTITUENTID uniqueidentifier  
    DECLARE @CONSTITUENTID uniqueidentifier  
    DECLARE @APPEALID uniqueidentifier  

    SELECT @APPEALID=APPEALID FROM dbo.[EVENT] (NOLOCK) WHERE ID=@EVENTID   

    Select @TEAMCONSTITUENTID = TEAMCONSTITUENTID from dbo.TEAMEXTENSION WHERE TEAMFUNDRAISINGTEAMID = @TEAMID  

    IF ISNULL(@CLIENTUSERSID,-1) > 0  
        SET @CONSTITUENTID = dbo.UFN_CONSTITUENT_GET_BY_CLIENTUSERID(@CLIENTUSERSID)

    -- save revenue ids in temp table for performance improvement


    declare @DONORREVENUEIDS as table 
    (
        REVENUESPLITID uniqueidentifier, 
        REVENUEID uniqueidentifier
    )

    insert into @DONORREVENUEIDS
        select REVENUESPLIT.ID as REVENUESPLITID,  REVENUESPLIT.REVENUEID  from dbo.REVENUESPLIT (NOLOCK) 
        where ID in (
            select REVENUESPLITID from dbo.REVENUERECOGNITION (NOLOCK) 
            where CONSTITUENTID = @TEAMCONSTITUENTID) 
        and REVENUESPLIT.TYPECODE=0
        and ISNULL(REVENUESPLIT.AMOUNT,0) > 0  
      and APPLICATION <> 'matching gift'

    declare @TEAMREVENUEIDS as table 
    (
        REVENUESPLITID uniqueidentifier, 
        REVENUEID uniqueidentifier
    )

    insert into @TEAMREVENUEIDS
        select REVENUESPLIT.ID as REVENUESPLITID,  REVENUESPLIT.REVENUEID  from dbo.REVENUESPLIT (NOLOCK) 
        where ID in (
            select REVENUESPLITID from dbo.REVENUERECOGNITION (NOLOCK) 
              INNER JOIN dbo.TEAMFUNDRAISINGTEAMMEMBER TM (NOLOCK)  
              ON TM.TEAMFUNDRAISINGTEAMID = @TEAMID  
              INNER JOIN dbo.TEAMFUNDRAISER TF (NOLOCK)  
              ON TM.TEAMFUNDRAISERID = TF.ID
            where REVENUERECOGNITION.CONSTITUENTID = TF.CONSTITUENTID
        ) 
        and REVENUESPLIT.TYPECODE=0
        and ISNULL(REVENUESPLIT.AMOUNT,0) > 0  
      and APPLICATION <> 'matching gift'


    declare @HOUSEHOLDREVENUEIDS as table 
    (
        REVENUESPLITID uniqueidentifier, 
        REVENUEID uniqueidentifier
    )

    insert into @HOUSEHOLDREVENUEIDS
        select REVENUESPLIT.ID as REVENUESPLITID,  REVENUESPLIT.REVENUEID  from dbo.REVENUESPLIT (NOLOCK) 
        where ID in (
            select REVENUESPLITID from dbo.REVENUERECOGNITION (NOLOCK) 
            INNER JOIN [dbo].[TEAMFUNDRAISINGTEAM] T (NOLOCK)  
            ON T.APPEALID = @APPEALID AND T.PARENTTEAMID = @TEAMID 
            INNER JOIN [dbo].TEAMEXTENSION TE (NOLOCK)  
            ON T.ID = TE.TEAMFUNDRAISINGTEAMID  AND TE.TYPECODE = 3 
            where REVENUERECOGNITION.CONSTITUENTID = TE.TEAMCONSTITUENTID
        ) 
        and REVENUESPLIT.TYPECODE=0
        and ISNULL(REVENUESPLIT.AMOUNT,0) > 0 
      and APPLICATION <> 'matching gift'

    -- twist the query for performance improvement


    declare @D_RESULT as table   
    (  
     CONSTITUENTID uniqueidentifier,
     DONATIONDATE datetime,   
     DONATIONAMOUNT money,
     ISACKNOWLEDGED bit,  
     REVENUEID uniqueidentifier,
     [REVENUESPLITID] uniqueidentifier,
     [TYPE] nvarchar(100),
     [NAME] nvarchar(308)
    )

    declare @T_RESULT as table   
    (  
     CONSTITUENTID uniqueidentifier,
     DONATIONDATE datetime,   
     DONATIONAMOUNT money,
     ISACKNOWLEDGED bit,  
     REVENUEID uniqueidentifier,
     [REVENUESPLITID] uniqueidentifier,
     [TYPE] nvarchar(100),
     [NAME] nvarchar(308)
    )

    declare @H_RESULT as table   
    (  
     CONSTITUENTID uniqueidentifier,
     DONATIONDATE datetime,   
     DONATIONAMOUNT money,
     ISACKNOWLEDGED bit,  
     REVENUEID uniqueidentifier,
     [REVENUESPLITID] uniqueidentifier,
     [TYPE] nvarchar(100),
     [NAME] nvarchar(308)
    )


       ;with   
    [R] as  
    (  
     select ID, CONSTITUENTID, DATE, TRANSACTIONTYPE   
     from dbo.REVENUE (NOLOCK)   
     where REVENUE.ID in (select distinct REVENUEID from @DONORREVENUEIDS)  
     and REVENUE.APPEALID = @APPEALID   
    ),  
    [RS] as    
    (    
     select     
     REVENUESPLIT.ID,     
     REVENUESPLIT.REVENUEID,     
     REVENUESPLIT.AMOUNT,    
     R.CONSTITUENTID,    
     R.DATE,    
     R.TRANSACTIONTYPE    
     from dbo.REVENUESPLIT (NOLOCK)  
     inner join [R] ON REVENUESPLIT.REVENUEID = R.ID   
     where REVENUESPLIT.ID in (select REVENUESPLITID from @DONORREVENUEIDS)  
    )
    insert into @D_RESULT 
    SELECT RS.CONSTITUENTID,           -- DONOR    

        RS.DATE as [DONATIONDATE],     
        ISNULL(RS.AMOUNT,0) as DONATIONAMOUNT,     
        ISNULL(GA.ISACKNOWLEDGED,0) as ISACKNOWLEDGED,    
        RS.REVENUEID,    
        RS.ID as [REVENUESPLITID],    
        'DONOR' as TYPE,    
        C.NAME    
    FROM [RS]  
     LEFT OUTER JOIN dbo.FAFGIFTACKNOWLEDGEMENT GA (NOLOCK)    
     ON GA.REVENUESPLITID = RS.ID    
     LEFT OUTER JOIN dbo.CONSTITUENT C    
     ON C.ID = RS.CONSTITUENTID    

    ;with [R_TEAM] as  
    (  
     select ID, CONSTITUENTID, DATE, TRANSACTIONTYPE   
     from dbo.REVENUE (NOLOCK)   
     where REVENUE.ID in (select distinct REVENUEID from @TEAMREVENUEIDS)  
     and REVENUE.APPEALID = @APPEALID   
    ),  
    [RS_TEAM] as    
    (    
     select     
     REVENUESPLIT.ID,     
     REVENUESPLIT.REVENUEID,     
     REVENUESPLIT.AMOUNT,    
     RevenueRecognition.CONSTITUENTID,    
     [R_TEAM].DATE,    
     [R_TEAM].TRANSACTIONTYPE    
     from dbo.REVENUESPLIT (NOLOCK)  
       inner join [R_TEAM] ON REVENUESPLIT.REVENUEID = [R_TEAM].ID
       inner join RevenueRecognition on RevenueRecognition.REVENUESPLITID = REVENUESPLIT.ID
     where REVENUESPLIT.ID in (select REVENUESPLITID from @TEAMREVENUEIDS)  
    )
    insert into @T_RESULT 
    SELECT [RS_TEAM].CONSTITUENTID,     --  Team Member    

         [RS_TEAM].DATE as [DONATIONDATE],    
         ISNULL([RS_TEAM].AMOUNT,0) as DONATIONAMOUNT,    
         0 as ISACKNOWLEDGED,    
         [RS_TEAM].REVENUEID,    
         [RS_TEAM].ID as [REVENUESPLITID],    
         'TEAMMEMBER' as TYPE,    
         C.NAME as NAME    
    FROM  [RS_TEAM]   
     INNER JOIN dbo.REVENUE ON REVENUE.ID = RS_TEAM.REVENUEID   
     LEFT OUTER JOIN dbo.FAFGIFTACKNOWLEDGEMENT GA (NOLOCK)    
     ON GA.REVENUESPLITID = [RS_TEAM].ID    
     LEFT OUTER JOIN dbo.CONSTITUENT C    
     ON C.ID = [RS_TEAM].CONSTITUENTID    
     WHERE  
     GA.REVENUESPLITID IS NULL    
     --AND [RS_TEAM].CONSTITUENTID <> REVENUE.CONSTITUENTID    


    ;with [R_HOUSEHOLD] as  
    (  
     select ID, CONSTITUENTID, DATE, TRANSACTIONTYPE   
     from dbo.REVENUE (NOLOCK)   
     where REVENUE.ID in (select distinct REVENUEID from @HOUSEHOLDREVENUEIDS)  
     and REVENUE.APPEALID = @APPEALID   
    ),  
    [RS_HOUSEHOLD] as    
    (    
     select     
     REVENUESPLIT.ID,     
     REVENUESPLIT.REVENUEID,     
     REVENUESPLIT.AMOUNT,    
     [R_HOUSEHOLD].CONSTITUENTID,    
     [R_HOUSEHOLD].DATE,    
     [R_HOUSEHOLD].TRANSACTIONTYPE    
     from dbo.REVENUESPLIT (NOLOCK)  
     inner join [R_HOUSEHOLD] ON REVENUESPLIT.REVENUEID = [R_HOUSEHOLD].ID   
     where REVENUESPLIT.ID in (select REVENUESPLITID from @HOUSEHOLDREVENUEIDS)  
    )    
    insert into @H_RESULT 
    SELECT [RS_HOUSEHOLD].CONSTITUENTID,           -- Household    

        [RS_HOUSEHOLD].DATE as [DONATIONDATE],     
        ISNULL([RS_HOUSEHOLD].AMOUNT,0) as DONATIONAMOUNT,     
        ISNULL(GA.ISACKNOWLEDGED,0) as ISACKNOWLEDGED,    
        [RS_HOUSEHOLD].REVENUEID,    
        [RS_HOUSEHOLD].ID as [REVENUESPLITID],    
        'HOUSEHOLD' as TYPE,    
        C.NAME    
    FROM [RS_HOUSEHOLD]  
     LEFT OUTER JOIN dbo.FAFGIFTACKNOWLEDGEMENT GA (NOLOCK)    
     ON GA.REVENUESPLITID = [RS_HOUSEHOLD].ID    
     LEFT OUTER JOIN dbo.CONSTITUENT C    
     ON C.ID = [RS_HOUSEHOLD].CONSTITUENTID    

    select * from @D_RESULT 
    union all
    select * from @T_RESULT 
    union all 
    select * from @H_RESULT 
    ORDER BY TYPE, NAME