USP_DATALIST_RECRUITMENTGOALS

CREATE procedure dbo.USP_DATALIST_RECRUITMENTGOALS
(
   @CLIENTUSERSID int,
   @EVENTID uniqueidentifier,
   @ROLETYPE tinyint = 0 -- 0:Individual, 1:Team Leader, 2:Team Member, 3: Company Leader

)
as

set nocount on;


DECLARE @TEAMMEMBERS int
DECLARE @TEAMID uniqueidentifier
DECLARE @REGISTRANTID uniqueidentifier
DECLARE @TEAMLEADERS int    
DECLARE @EVENTSTATUS bit -- 1:active, 0:not active

DECLARE  @CONSTITUENTID uniqueidentifier 
DECLARE @GOAL int
DECLARE @CURRENTTOTAL int
DECLARE @HOUSEHOLDID uniqueidentifier
DECLARE @HOUSEHOLDMEMBERS int
SET @CONSTITUENTID = dbo.UFN_CONSTITUENT_GET_BY_CLIENTUSERID(@CLIENTUSERSID)

DECLARE @RECRUITMENTGOAL table (
GOAL int,
CURRENTTOTAL int
)

IF EXISTS (Select 1 from dbo.EVENT (NOLOCK) WHERE ID = @EVENTID AND ENDDATE IS NOT NULL AND ENDDATE < getdate())
  SET @EVENTSTATUS = 0
ELSE 
  SET @EVENTSTATUS = 1

SELECT @TEAMID = TEAMID, @REGISTRANTID = RegistrantID 
from dbo.UFN_FAF_GETTEAMINFO_BY_REGISTRANT(@CONSTITUENTID, @EVENTID)



SELECT @TEAMMEMBERS = count(*) from dbo.UFN_FAF_TEAMMEMBERLIST(@CONSTITUENTID,@EVENTID) -- 1 = team

SET @TEAMMEMBERS = ISNULL(@TEAMMEMBERS,0)+1

SELECT @TEAMLEADERS = count(*) from dbo.UFN_FAF_COMPANYTEAMLEADERLIST(@CONSTITUENTID,@EVENTID)

IF @ROLETYPE = 0 
  SELECT @GOAL = re.MEMBERECRUITMENTGOAL from dbo.REGISTRANTEXTENSION re where REGISTRANTID = @REGISTRANTID        
  SELECT @CURRENTTOTAL = @TEAMMEMBERS

IF @ROLETYPE = 1
  SELECT @GOAL = te.TEAMMEMBERGOAL from dbo.TEAMEXTENSION te where TEAMFUNDRAISINGTEAMID = @TEAMID        
  SELECT @CURRENTTOTAL = @TEAMMEMBERS

IF @ROLETYPE = 2
  SELECT @GOAL = re.MEMBERECRUITMENTGOAL from dbo.REGISTRANTEXTENSION re where REGISTRANTID = @REGISTRANTID        
  SELECT @CURRENTTOTAL = @TEAMMEMBERS

IF @ROLETYPE = 3
  SELECT @GOAL = te.TEAMRECRUITMENTGOAL from dbo.TEAMEXTENSION te where TEAMFUNDRAISINGTEAMID = @TEAMID        
  SELECT @CURRENTTOTAL = @TEAMLEADERS

IF @ROLETYPE = 5 OR @ROLETYPE = 6
  SELECT @HOUSEHOLDID = HOUSEHOLDID from TEAMEXTENSION where TYPECODE = 3 and TEAMFUNDRAISINGTEAMID = @TEAMID
  SELECT @HOUSEHOLDMEMBERS = count(*) from dbo.UFN_FAF_HOUSEHOLDMEMBERLIST(@CONSTITUENTID,@EVENTID)

  SELECT @GOAL = te.PARTICIPANTGOAL from dbo.TEAMEXTENSION te where TEAMFUNDRAISINGTEAMID = @TEAMID        
  SELECT @CURRENTTOTAL = @HOUSEHOLDMEMBERS



INSERT INTO @RECRUITMENTGOAL  
VALUES(@GOAL,@CURRENTTOTAL)  

select GOAL, CURRENTTOTAL from @RECRUITMENTGOAL