USP_FAF_TEAMMEMBER_ADD_EDIT

CREATE PROCEDURE dbo.USP_FAF_TEAMMEMBER_ADD_EDIT 
    (
        @ID uniqueidentifier = NULL output,    -- TEAMFUNDRAISINGTEAMMEMBERID

        @CHANGEAGENTID uniqueidentifier = NULL,
        @EVENTID uniqueidentifier,
        @TEAMFUNDRAISINGTEAMID uniqueidentifier,
        @REGISTRANTID uniqueidentifier,
        @FUNDRAISINGGOAL money = NULL,
        @MEMBERECRUITMENTGOAL int = NULL,
        @ADVOCACYACTIONGOAL int = NULL,
        @COMMUNICATIONGOAL int = NULL, -- emails sent

        @VOLUNTEERRECRUITMENTGOAL int = NULL,
        @ISTEAMCAPTAIN bit = 0,     -- 1: Yes, 0: NO

        @REMOVETEAMCAPTAIN bit = 0 -- 1: Yes, 0: No

    ) WITH EXECUTE AS CALLER
AS
BEGIN
        SET NOCOUNT ON;

        DECLARE @CURRENTDATE datetime;
        DECLARE @APPEALID uniqueidentifier
        DECLARE @CONSTITUENTID uniqueidentifier
        DECLARE @TEAMFUNDRAISERID uniqueidentifier
        DECLARE @CURRENTGOAL money
        DECLARE @REGISTRANTEXTENSIONID uniqueidentifier
        DECLARE @TEAMCAPTAINID uniqueidentifier
        DECLARE @TEAMCONSTITUENTID uniqueidentifier
        DECLARE @HOUSEHOLDID uniqueidentifier
        DECLARE @PARENTTEAMCONSTITUENTID uniqueidentifier

        IF @ID is null
            SET @ID = newid();

        IF @CHANGEAGENTID is null
            EXEC dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

        SET @CURRENTDATE = getdate();

        SELECT @APPEALID = APPEALID FROM EVENT WITH (NOLOCK) WHERE ID = @EVENTID
        SELECT @CONSTITUENTID = CONSTITUENTID FROM REGISTRANT WITH (NOLOCK) WHERE ID = @REGISTRANTID

        select @PARENTTEAMCONSTITUENTID = TEAMEXTENSION.TEAMCONSTITUENTID
            from TEAMFUNDRAISINGTEAM 
            inner join TEAMEXTENSION on TEAMFUNDRAISINGTEAM.PARENTTEAMID = TEAMEXTENSION.TEAMFUNDRAISINGTEAMID
        where TEAMFUNDRAISINGTEAM.ID = @TEAMFUNDRAISINGTEAMID;

        SELECT
                @TEAMFUNDRAISERID = TEAMFUNDRAISER.ID,
                @CURRENTGOAL = TEAMFUNDRAISER.GOAL,
                @TEAMCONSTITUENTID = TEAMEXTENSION.TEAMCONSTITUENTID,
                @HOUSEHOLDID = TEAMEXTENSION.HOUSEHOLDID
            FROM
                dbo.TEAMFUNDRAISINGTEAM
                left join dbo.TEAMFUNDRAISER on
                    TEAMFUNDRAISINGTEAM.APPEALID = TEAMFUNDRAISER.APPEALID
                    and TEAMFUNDRAISER.CONSTITUENTID = @CONSTITUENTID
                 left join dbo.TEAMEXTENSION on
                    TEAMFUNDRAISINGTEAM.ID = TEAMEXTENSION.TEAMFUNDRAISINGTEAMID 
            WHERE
                TEAMFUNDRAISINGTEAM.ID = @TEAMFUNDRAISINGTEAMID;

        BEGIN TRY

-- add to TEAMFUNDRAISER                        


            IF @TEAMFUNDRAISERID is null
                BEGIN
                    SET @TEAMFUNDRAISERID = newid();

                    INSERT INTO dbo.TEAMFUNDRAISER
                        (ID,APPEALID,CONSTITUENTID,GOAL,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
                    VALUES
                        (@TEAMFUNDRAISERID,@APPEALID,@CONSTITUENTID,ISNULL(@FUNDRAISINGGOAL,0),@CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE);
                END
            ELSE
                BEGIN
                UPDATE TEAMFUNDRAISER
                    SET GOAL = ISNULL(@FUNDRAISINGGOAL,GOAL),
                        CHANGEDBYID = @CHANGEAGENTID,
                        DATECHANGED = @CURRENTDATE
                    WHERE
                        ID = @TEAMFUNDRAISERID;
                END
-- add as Team Member                            


            IF NOT EXISTS(SELECT 1 FROM TEAMFUNDRAISINGTEAMMEMBER WHERE TEAMFUNDRAISINGTEAMID = @TEAMFUNDRAISINGTEAMID AND TEAMFUNDRAISERID = @TEAMFUNDRAISERID)    
            INSERT INTO dbo.TEAMFUNDRAISINGTEAMMEMBER
                    (ID,TEAMFUNDRAISINGTEAMID,TEAMFUNDRAISERID,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
                VALUES
                    (@ID,@TEAMFUNDRAISINGTEAMID,@TEAMFUNDRAISERID,@CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE);

-- add personal goals


         SELECT @REGISTRANTEXTENSIONID = ID FROM REGISTRANTEXTENSION WHERE REGISTRANTID = @REGISTRANTID

         IF @REGISTRANTEXTENSIONID IS NULL
         BEGIN

            SET @REGISTRANTEXTENSIONID = NEWID();

            INSERT INTO dbo.REGISTRANTEXTENSION
                (ID, REGISTRANTID, FUNDRAISINGGOAL, ADVOCACYACTIONGOAL, MEMBERECRUITMENTGOAL, COMMUNICATIONGOAL, VOLUNTEERRECRUITMENTGOAL, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
            VALUES
                (@REGISTRANTEXTENSIONID, @REGISTRANTID, ISNULL(@FUNDRAISINGGOAL,0), ISNULL(@ADVOCACYACTIONGOAL,0), ISNULL(@MEMBERECRUITMENTGOAL,0), ISNULL(@COMMUNICATIONGOAL,0), ISNULL(@VOLUNTEERRECRUITMENTGOAL,0), @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)
         END
         ELSE
         UPDATE REGISTRANTEXTENSION
         SET 
            FUNDRAISINGGOAL = ISNULL(@FUNDRAISINGGOAL,FUNDRAISINGGOAL),
            ADVOCACYACTIONGOAL = ISNULL(@ADVOCACYACTIONGOAL,ADVOCACYACTIONGOAL),
            MEMBERECRUITMENTGOAL = ISNULL(@MEMBERECRUITMENTGOAL,MEMBERECRUITMENTGOAL),
            COMMUNICATIONGOAL = ISNULL(@COMMUNICATIONGOAL,COMMUNICATIONGOAL),
            VOLUNTEERRECRUITMENTGOAL = ISNULL(@VOLUNTEERRECRUITMENTGOAL,VOLUNTEERRECRUITMENTGOAL),

            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
         WHERE ID = @REGISTRANTEXTENSIONID AND REGISTRANTID = @REGISTRANTID

-- add as Team Captain if required


        SELECT @TEAMCAPTAINID = ID FROM TEAMFUNDRAISINGTEAMCAPTAIN WITH (NOLOCK) 
            WHERE TEAMFUNDRAISINGTEAMID = @TEAMFUNDRAISINGTEAMID AND CONSTITUENTID = @CONSTITUENTID

        IF @ISTEAMCAPTAIN = 1 AND @TEAMCAPTAINID IS NULL
        BEGIN
            SET @TEAMCAPTAINID = NEWID();
            INSERT INTO dbo.TEAMFUNDRAISINGTEAMCAPTAIN
                (ID,TEAMFUNDRAISINGTEAMID,CONSTITUENTID,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
            VALUES
                (@TEAMCAPTAINID,@TEAMFUNDRAISINGTEAMID,@CONSTITUENTID,@CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE);
        END

        IF @REMOVETEAMCAPTAIN = 1 AND @TEAMCAPTAINID IS NOT NULL
          EXEC USP_TEAMFUNDRAISINGTEAMCAPTAIN_DELETE @ID = @TEAMCAPTAINID, @CHANGEAGENTID = @CHANGEAGENTID

    END TRY

    BEGIN CATCH
        EXEC dbo.USP_RAISE_ERROR;
        RETURN 1;
    END CATCH

    RETURN 0;

END