USP_DATALIST_PARTICIPANTGOALS

CREATE procedure dbo.USP_DATALIST_PARTICIPANTGOALS
(
@EVENTID uniqueidentifier = NULL,
@CLIENTUSERSID integer = NULL,
@REGTYPEGOALCODE TinyInt = 0 --0:Individual/participant Goal, 1:Team Goal, 2:Company Goal, 3:Household Goal

)
as
    set nocount on;

DECLARE @CONSTITUENTID uniqueidentifier = NULL
DECLARE @EVENTPRICEID uniqueidentifier = NULL
DECLARE @MINFUNDRAISING money = 0
DECLARE @TARGETFUNDRAISING money = 0
DECLARE @CURRENTGOAL money = 0 --Current Goal is for all roles such as current goal of individual participant, team goal, household goal, company goal

DECLARE @NUMOFCOMM Integer = 0
DECLARE @NUMOFVOL Integer = 0
DECLARE @NUMOFPARTS Integer = 0
DECLARE @PEROFDONORS decimal(6,2) = 0.0
DECLARE @PEROFTEAMMEMBERS decimal(6,2) = 0.0
DECLARE @OTHERGOAL money = 0
DECLARE @NUMOFTEAMS Integer = 0
DECLARE @PEROFTEAMS decimal(6,2) = 0.0
DECLARE @NUMOFTEAMEMBERS Integer = 0

SET @CONSTITUENTID=dbo.UFN_CONSTITUENT_GET_BY_CLIENTUSERID(@CLIENTUSERSID)

--Pull default and restriction

--Event default goals---

DECLARE @EVENTMINFUNDRAISING money = 0
DECLARE @EVENTTARGETFUNDRAISING money = 0
DECLARE @EVENTCURRENTGOAL money = 0 --Current Goal is for all roles such as current goal of individual participant, team goal, household goal, company goal

DECLARE @EVENTNUMOFCOMM Integer = 0
DECLARE @EVENTNUMOFVOL Integer = 0
DECLARE @EVENTNUMOFPARTS Integer = 0
DECLARE @EVENTPEROFDONORS decimal(6,2) = 0.0
DECLARE @EVENTPEROFTEAMMEMBERS decimal(6,2) = 0.0
DECLARE @EVENTOTHERGOAL Integer = 0
DECLARE @EVENTNUMOFTEAMS Integer = 0
DECLARE @EVENTPEROFTEAMS decimal(6,2) = 0.0
DECLARE @EVENTNUMOFTEAMEMBERS Integer = 0

DECLARE @ISNOTLOWERFUNDRAISING bit = 0
DECLARE @ISNOTLOWERRECRUITEMENT bit = 0
DECLARE @ISNOTLOWERVOLUNTEERRECRUITEMENT bit = 0
DECLARE @ISNOTLOWERDONORRETENTION bit = 0
DECLARE @ISNOTLOWERCOMMUNICATION bit =0
DECLARE @ISNOTLOWERTEAMMEMBERS bit = 0
DECLARE @ISNOTLOWERTEAMMEMBERRETENTION bit = 0 
DECLARE @ISNOTLOWERTEAMS bit = 0
DECLARE @ISNOTLOWERTEAMRETENTION bit = 0
DECLARE @ISNOTLOWERTARGETFUNDRAISING bit = 0

--Pull default and restriction

SELECT @EVENTMINFUNDRAISING=FG.MINFUNDRAISING,@EVENTTARGETFUNDRAISING=FG.TARGETFUNDRAISING, 
       @EVENTNUMOFPARTS=FG.RECRUITEMENT,@EVENTNUMOFTEAMEMBERS=FG.TEAMMEMBERS, @EVENTNUMOFVOL=FG.VOLUNTEERRECRUITEMENT,
       @EVENTPEROFTEAMMEMBERS=FG.TEAMMEMBERRETENTION, @EVENTNUMOFCOMM=FG.COMMUNICATIONS,@EVENTOTHERGOAL=0,@EVENTNUMOFTEAMS=FG.TEAMS,
       @EVENTPEROFTEAMS=TEAMRETENTION, @EVENTPEROFDONORS=FG.DONORRETENTION,
        @ISNOTLOWERFUNDRAISING = FG.ISNOTLOWERFUNDRAISING, 
        @ISNOTLOWERRECRUITEMENT = FG.ISNOTLOWERRECRUITEMENT,
        @ISNOTLOWERVOLUNTEERRECRUITEMENT = FG.ISNOTLOWERVOLUNTEERRECRUITEMENT,
        @ISNOTLOWERDONORRETENTION = FG.ISNOTLOWERDONORRETENTION,
        @ISNOTLOWERCOMMUNICATION = FG.ISNOTLOWERCOMMUNICATION,
        @ISNOTLOWERTEAMMEMBERS = FG.ISNOTLOWERTEAMMEMBERS,
        @ISNOTLOWERTEAMMEMBERRETENTION = FG.ISNOTLOWERTEAMMEMBERRETENTION,
        @ISNOTLOWERTEAMS = FG.ISNOTLOWERTEAMS,
        @ISNOTLOWERTEAMRETENTION = FG.ISNOTLOWERTEAMRETENTION,
        @ISNOTLOWERTARGETFUNDRAISING = FG.ISNOTLOWERTARGETFUNDRAISING,
    @EVENTPRICEID = FG.EVENTPRICEID
FROM FAFREGISTRATIONTYPEGOAL FG, REGISTRANTREGISTRATION RR 
WHERE RR.REGISTRANTID = (SELECT REG.ID FROM REGISTRANT REG WHERE REG.CONSTITUENTID=@CONSTITUENTID AND REG.EVENTID=@EVENTID)
AND RR.EVENTPRICEID = FG.EVENTPRICEID 
AND FG.REGTYPEGOALCODE = @REGTYPEGOALCODE

If @REGTYPEGOALCODE = 0 --Personal goals

    Begin

SELECT @CURRENTGOAL=RE.FUNDRAISINGGOAL,@MINFUNDRAISING=RE.FUNDRAISINGGOAL,@TARGETFUNDRAISING=RE.TARGETFUNDRAISINGGOAL, 
       @NUMOFCOMM=RE.COMMUNICATIONGOAL, @NUMOFVOL=RE.VOLUNTEERRECRUITMENTGOAL, 
       @NUMOFPARTS=RE.MEMBERECRUITMENTGOAL,@OTHERGOAL=RE.OTHERUNITGOAL, @PEROFDONORS=RE.DONORRETENTIONGOAL
FROM REGISTRANTEXTENSION RE, REGISTRANT REG 
WHERE RE.REGISTRANTID=REG.ID 
AND REG.CONSTITUENTID=@CONSTITUENTID 
AND REG.EVENTID=@EVENTID

    End

Else If @REGTYPEGOALCODE = 3  --Household goals

        Begin
            --Two cases here, user could be a household leader or a household member

            If NOT EXISTS(SELECT ID FROM TEAMFUNDRAISINGTEAMCAPTAIN TFTC0 WHERE TFTC0.CONSTITUENTID=@CONSTITUENTID)
                Begin
                    SELECT @NUMOFPARTS=TE.PARTICIPANTGOAL,@NUMOFTEAMEMBERS=TE.TEAMMEMBERGOAL, 
                    @NUMOFVOL=TE.VOLUNTEERGOAL, @NUMOFCOMM=TE.COMMUNICATIONGOAL, @OTHERGOAL=TE.OTHERGOAL,  
                    @PEROFTEAMMEMBERS=TE.PCTTEAMMEMBERRETENSION, @NUMOFTEAMS=TE.TEAMRECRUITMENTGOAL,@PEROFTEAMS=TE.PCTTEAMSRETENSION,
                    @TARGETFUNDRAISING=TE.TARGETFUNDRAISINGGOAL,@PEROFDONORS=TE.DONORRETENTIONGOAL,@CURRENTGOAL=TFT.GOAL

                    FROM TEAMFUNDRAISER TFR INNER JOIN TEAMFUNDRAISINGTEAMMEMBER TFTM ON TFR.ID = TFTM.TEAMFUNDRAISERID
                    INNER JOIN TEAMFUNDRAISINGTEAM TFT ON TFTM.TEAMFUNDRAISINGTEAMID = TFT.ID
                    INNER JOIN TEAMEXTENSION TE ON TE.TEAMFUNDRAISINGTEAMID=TFT.ID
                    WHERE TFR.CONSTITUENTID=@CONSTITUENTID 
                    AND TE.EVENTID=@EVENTID
                    ORDER BY TFT.DATECHANGED DESC
                End
            Else
                Begin
                    SELECT @NUMOFPARTS=PARTICIPANTGOAL,@NUMOFTEAMEMBERS=TEAMMEMBERGOAL, 
                    @NUMOFVOL=VOLUNTEERGOAL, @NUMOFCOMM=COMMUNICATIONGOAL, @OTHERGOAL=OTHERGOAL,  
                    @PEROFTEAMMEMBERS=PCTTEAMMEMBERRETENSION, @NUMOFTEAMS=TEAMRECRUITMENTGOAL,@PEROFTEAMS=PCTTEAMSRETENSION,
                    @TARGETFUNDRAISING=TARGETFUNDRAISINGGOAL,@PEROFDONORS=DONORRETENTIONGOAL,@CURRENTGOAL=TFT.GOAL

                    FROM TEAMEXTENSION TE INNER JOIN TEAMFUNDRAISINGTEAM TFT ON TE.TEAMFUNDRAISINGTEAMID=TFT.ID
                    LEFT JOIN TEAMFUNDRAISINGTEAMCAPTAIN TFTC ON TFT.ID=TFTC.TEAMFUNDRAISINGTEAMID
                    WHERE TFTC.CONSTITUENTID=@CONSTITUENTID
                    AND TE.EVENTID=@EVENTID
                End    
        End        
Else --Team or Company goals

    Begin

SELECT @NUMOFPARTS=PARTICIPANTGOAL,@NUMOFTEAMEMBERS=TEAMMEMBERGOAL, 
@NUMOFVOL=VOLUNTEERGOAL, @NUMOFCOMM=COMMUNICATIONGOAL, @OTHERGOAL=OTHERGOAL,  
@PEROFTEAMMEMBERS=PCTTEAMMEMBERRETENSION, @NUMOFTEAMS=TEAMRECRUITMENTGOAL,@PEROFTEAMS=PCTTEAMSRETENSION,
@TARGETFUNDRAISING=TARGETFUNDRAISINGGOAL,@PEROFDONORS=DONORRETENTIONGOAL,@CURRENTGOAL=TFT.GOAL

FROM TEAMEXTENSION TE INNER JOIN TEAMFUNDRAISINGTEAM TFT ON TE.TEAMFUNDRAISINGTEAMID=TFT.ID
LEFT JOIN TEAMFUNDRAISINGTEAMCAPTAIN TFTC ON TFT.ID=TFTC.TEAMFUNDRAISINGTEAMID
WHERE TFTC.CONSTITUENTID=@CONSTITUENTID
AND TE.EVENTID=@EVENTID

    End

--Now check with event goal rules to output correct goals value

If (@ISNOTLOWERFUNDRAISING = 1 AND @CURRENTGOAL < @EVENTMINFUNDRAISING)
    SET @CURRENTGOAL=@EVENTMINFUNDRAISING

If     (@ISNOTLOWERRECRUITEMENT = 1 AND @NUMOFPARTS < @EVENTNUMOFPARTS)
    SET @NUMOFPARTS=@EVENTNUMOFPARTS

If    (@ISNOTLOWERVOLUNTEERRECRUITEMENT = 1 AND @NUMOFVOL < @EVENTNUMOFVOL)
    SET @NUMOFVOL=@EVENTNUMOFVOL

If (@ISNOTLOWERDONORRETENTION = 1 AND @PEROFDONORS < @EVENTPEROFDONORS)    
    SET @PEROFDONORS=@EVENTPEROFDONORS

If (@ISNOTLOWERCOMMUNICATION = 1 AND @NUMOFCOMM < @EVENTNUMOFCOMM)
    SET @NUMOFCOMM = @EVENTNUMOFCOMM

If    (@ISNOTLOWERTEAMMEMBERS = 1 AND @NUMOFTEAMEMBERS < @EVENTNUMOFTEAMEMBERS)
    SET @NUMOFTEAMEMBERS = @EVENTNUMOFTEAMEMBERS

If     (@ISNOTLOWERTEAMMEMBERRETENTION = 1 and @PEROFTEAMMEMBERS < @EVENTPEROFTEAMMEMBERS)
    set @PEROFTEAMMEMBERS = @EVENTPEROFTEAMMEMBERS

If    (@ISNOTLOWERTEAMS = 1 and @NUMOFTEAMS < @EVENTNUMOFTEAMS)
    SET @NUMOFTEAMS = @EVENTNUMOFTEAMS

If    (@ISNOTLOWERTEAMRETENTION = 1 and @PEROFTEAMS < @EVENTPEROFTEAMS)
    SET @PEROFTEAMS = @EVENTPEROFTEAMS

If    (@ISNOTLOWERFUNDRAISING = 1 and @TARGETFUNDRAISING < @EVENTMINFUNDRAISING)
    SET @TARGETFUNDRAISING = @EVENTMINFUNDRAISING

SELECT ISNULL(@CURRENTGOAL,0) AS CURRENTGOAL,ISNULL(@MINFUNDRAISING,0) AS MINFUNDRAISING,ISNULL(@TARGETFUNDRAISING,0) AS TARGETFUNDRAISING,
ISNULL(@NUMOFCOMM,0) AS NUMOFCOMM, ISNULL(@NUMOFVOL,0) AS NUMOFVOL, ISNULL(@NUMOFPARTS,0) AS NUMOFPARTS, ISNULL((@PEROFDONORS*100),0) AS PEROFDONORS,
ISNULL(@OTHERGOAL,0) AS OTHERGOAL, ISNULL(@NUMOFTEAMS,0) AS NUMOFTEAMS, ISNULL((@PEROFTEAMS*100),0) AS PEROFTEAMS, ISNULL(@NUMOFTEAMEMBERS,0) AS NUMOFTEAMEMBERS, ISNULL((@PEROFTEAMMEMBERS*100),0) AS PEROFTEAMMEMBERS,
@EVENTPRICEID AS EVENTPRICEID