USP_DATALIST_FAFDONORRETENTION

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

DECLARE @CONSTITUENTID uniqueidentifier
DECLARE @CURRENTDONORS int
DECLARE @PREVIOUSDONORS int
DECLARE @PREVIOUSEVENT uniqueidentifier
DECLARE @TEAMID uniqueidentifier
DECLARE @PREVIOUSTEAMID uniqueidentifier
DECLARE @TEAMCONSTITUENTID uniqueidentifier
DECLARE @PREVIOUSTEAMCONSTITUENTID uniqueidentifier
DECLARE @CURRENTGENERALTEAMDONORS int
DECLARE @PREVIOUSGENERALTEAMDONORS int
DECLARE @ISTEAMLEADER bit
DECLARE @ISPREVIOUSTEAMLEADER bit

SET @CONSTITUENTID = dbo.UFN_CONSTITUENT_GET_BY_CLIENTUSERID(@CLIENTUSERSID);  

SELECT @PREVIOUSEVENT = PRIORYEAREVENTID from dbo.EVENTEXTENSION (NOLOCK) WHERE EVENTID = @EVENTID;  

IF @PREVIOUSEVENT IS NOT NULL  
BEGIN  
    SELECT @TEAMID = TEAMID from dbo.UFN_FAF_GETTEAMINFO_BY_REGISTRANT(@CONSTITUENTID, @EVENTID);  
    SELECT @TEAMCONSTITUENTID = TEAMCONSTITUENTID from TEAMEXTENSION (nolock) where TEAMFUNDRAISINGTEAMID = @TEAMID  
    IF EXISTS(SELECT 1 FROM dbo.TEAMFUNDRAISINGTEAMCAPTAIN WHERE CONSTITUENTID = @CONSTITUENTID AND TEAMFUNDRAISINGTEAMID = @TEAMID)  
        SET @ISTEAMLEADER = 1; 

    SELECT @PREVIOUSTEAMID = TEAMID from dbo.UFN_FAF_GETTEAMINFO_BY_REGISTRANT(@CONSTITUENTID, @PREVIOUSEVENT);  
    SELECT @PREVIOUSTEAMCONSTITUENTID = TEAMCONSTITUENTID from TEAMEXTENSION (nolock) where TEAMFUNDRAISINGTEAMID = @PREVIOUSTEAMID;  
    IF EXISTS(SELECT 1 FROM dbo.TEAMFUNDRAISINGTEAMCAPTAIN WHERE CONSTITUENTID = @CONSTITUENTID AND TEAMFUNDRAISINGTEAMID = @PREVIOUSTEAMID)  
        SET @ISPREVIOUSTEAMLEADER = 1  

    DECLARE @PREVDONORS as table([CONSTITUENTID]    uniqueidentifier)
    DECLARE @CURRDONORS as table([CONSTITUENTID]    uniqueidentifier)

    INSERT @PREVDONORS 
        select CONSTITUENTID from [dbo].[UFN_FAFEVENT_PARTICIPANTDONORS](@PREVIOUSEVENT,@CONSTITUENTID) 
        union 
        SELECT CONSTITUENTID FROM [dbo].[UFN_FAFEVENT_PARTICIPANTDONORS](@PREVIOUSEVENT,@PREVIOUSTEAMCONSTITUENTID)
        where @PREVIOUSTEAMCONSTITUENTID IS NOT NULL AND ISNULL(@ISPREVIOUSTEAMLEADER,0) = 1

    INSERT @CURRDONORS 
        select CONSTITUENTID from [dbo].[UFN_FAFEVENT_PARTICIPANTDONORS](@EVENTID,@CONSTITUENTID) 
        union 
        SELECT CONSTITUENTID FROM [dbo].[UFN_FAFEVENT_PARTICIPANTDONORS](@EVENTID,@TEAMCONSTITUENTID)
        where @TEAMCONSTITUENTID IS NOT NULL AND ISNULL(@ISTEAMLEADER,0) = 1

    select @PREVIOUSDONORS = COUNT(*) 
    from @PREVDONORS  

    select @CURRENTDONORS = COUNT(*)  
    from @CURRDONORS C 
    inner join @PREVDONORS P on P.CONSTITUENTID = C.CONSTITUENTID        
END

select R.ID,  
     RE.DONORRETENTIONGOAL,  
     ISNULL(@CURRENTDONORS,0) AS CURRENTDONORS,  
     ISNULL(@PREVIOUSDONORS,0) AS PREVIOUSDONORS  
from dbo.REGISTRANT R (NOLOCK)  
INNER JOIN dbo.REGISTRANTEXTENSION RE (NOLOCK)  
ON R.ID = RE.REGISTRANTID  
where R.CONSTITUENTID = @CONSTITUENTID  
AND R.EVENTID = @EVENTID