USP_DATAFORMTEMPLATE_EDIT_TEAMEXTENSION

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_TEAMEXTENSION
(
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @TEAMFUNDRAISINGTEAMID uniqueidentifier,
    @EVENTID uniqueidentifier,
    @PARTICIPANTGOAL int,
    @TEAMMEMBERGOAL int,
    @VOLUNTEERGOAL int,
    @COMMUNICATIONGOAL int,
    @OTHERGOAL int,
    @PCTTEAMMEMBERRETENSION decimal(6,2),
    @TEAMRECRUITMENTGOAL int,
    @PCTTEAMSRETENSION decimal(6,2),
    --@STATUSCODE tinyint,
    --@FAFFUNDRAISINGGAUGEID uniqueidentifier,
    --@TYPECODE tinyint,
    --@HOUSEHOLDID uniqueidentifier,
    @TARGETFUNDRAISINGGOAL money,
    @DONORRETENTIONGOAL decimal(6,2),
    --@TEAMCONSTITUENTID uniqueidentifier
    @GROUPCURRENTGOAL money    
)
as

    set nocount on;

    if @CHANGEAGENTID is null  
        exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

    begin try

        --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        

        DECLARE @CONSTITUENTID uniqueidentifier = NULL
        DECLARE @REGTYPEGOALCODE TinyInt = 1

        SELECT @CONSTITUENTID=TFTC.CONSTITUENTID, @EVENTID=TE.EVENTID, @REGTYPEGOALCODE=TE.TYPECODE FROM TEAMFUNDRAISINGTEAM TFT INNER JOIN TEAMFUNDRAISINGTEAMCAPTAIN TFTC ON TFT.ID=TFTC.TEAMFUNDRAISINGTEAMID INNER JOIN TEAMEXTENSION TE ON TE.TEAMFUNDRAISINGTEAMID=TFT.ID WHERE TFT.ID=@ID

        --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
        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

        --Now check with event goal rules to output correct goals value
        If (@ISNOTLOWERFUNDRAISING = 1 AND @GROUPCURRENTGOAL < @EVENTMINFUNDRAISING)
            SET @GROUPCURRENTGOAL=@EVENTMINFUNDRAISING

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

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

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

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

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

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

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

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

        If    (@ISNOTLOWERTARGETFUNDRAISING = 1 and @TARGETFUNDRAISINGGOAL < @EVENTTARGETFUNDRAISING)
            SET @TARGETFUNDRAISINGGOAL = @EVENTTARGETFUNDRAISING

        -- handle updating the data
    /*if @DONORRETENTIONGOAL > 0.0
        set @DONORRETENTIONGOAL = @DONORRETENTIONGOAL / 100 
    if @PCTTEAMMEMBERRETENSION > 0.0
        set @PCTTEAMMEMBERRETENSION = @PCTTEAMMEMBERRETENSION / 100
    if @PCTTEAMSRETENSION > 0.0
        set @PCTTEAMSRETENSION = @PCTTEAMSRETENSION / 100*/

        update dbo.TEAMEXTENSION set
            TEAMFUNDRAISINGTEAMID = @TEAMFUNDRAISINGTEAMID,
            EVENTID = @EVENTID,
            PARTICIPANTGOAL = @PARTICIPANTGOAL,
            TEAMMEMBERGOAL = @TEAMMEMBERGOAL,
            VOLUNTEERGOAL = @VOLUNTEERGOAL,
            COMMUNICATIONGOAL = @COMMUNICATIONGOAL,
            OTHERGOAL = @OTHERGOAL,
            PCTTEAMMEMBERRETENSION = @PCTTEAMMEMBERRETENSION / 100,
            TEAMRECRUITMENTGOAL = @TEAMRECRUITMENTGOAL,
            PCTTEAMSRETENSION = @PCTTEAMSRETENSION / 100,
            --STATUSCODE = @STATUSCODE,
            --FAFFUNDRAISINGGAUGEID = @FAFFUNDRAISINGGAUGEID,
            --TYPECODE = @TYPECODE,
            --HOUSEHOLDID = @HOUSEHOLDID,
            TARGETFUNDRAISINGGOAL = @TARGETFUNDRAISINGGOAL,
            DONORRETENTIONGOAL = @DONORRETENTIONGOAL / 100,
            --TEAMCONSTITUENTID = @TEAMCONSTITUENTID,

            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = getdate()
        where TEAMFUNDRAISINGTEAMID = @ID

        --IF @STATUSCODE = 0
        --    UPDATE CONSTITUENT
        --    set ISINACTIVE = 0
        --    WHERE ID = @TEAMCONSTITUENTID

            UPDATE dbo.TEAMFUNDRAISINGTEAM
            SET GOAL=@GROUPCURRENTGOAL
            WHERE ID=@ID

    end try
    begin catch
        exec dbo.USP_RAISE_ERROR
        return 1
    end catch

return 0;