USP_DATAFORMTEMPLATE_EDIT_PRMOTEDEMOTEMEMBER

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_PRMOTEDEMOTEMEMBER  
(  
    @ID uniqueidentifier, --REGISTRANTID
    @CONSTITUENTID uniqueidentifier,
    @EVENTID uniqueidentifier,  
    @GROUPID uniqueidentifier,  
    @ROLECODE tinyint,  
    @ROLECODEORIGINAL tinyint,
    @REGISTRATIONOPTIONID uniqueidentifier
)  
as  
begin try  
    DECLARE @REGISTRATIONOPTIONIDORIGINAL uniqueidentifier --need look up
    DECLARE @FUNDRAISINGGOAL money   
    DECLARE @TARGETFUNDRAISINGGOAL money   
    DECLARE @PARTICIPANTRECRUITGOAL int  
    DECLARE @DONORRETENTIONGOAL decimal(5,2)   
    DECLARE @COMMUNICATIONGOAL int   
    DECLARE @GROUPFUNDRAISINGGOAL money    
    DECLARE @GROUPPARTICIPANTRECRUITMENTGOAL int   
    DECLARE @GROUPDONORRETENTIONGOAL decimal(5,2)  
    DECLARE @GROUPTEAMMEMBERSGOAL int  
    DECLARE @GROUPTEAMMEMBERSRETENTIONGOAL decimal(5,2)  
    DECLARE @GROUPTEAMSGOAL int  
    DECLARE @GROUPTEAMSRETENTIONGOAL decimal(5,2)  
    DECLARE @GROUPCOMMUNICATIONGOAL int  
    DECLARE @CLIENTUSERID int
  DECLARE @USEDQUANTITY int
  DECLARE @MAXQUANTITY int

    SET @FUNDRAISINGGOAL = 0
    SET @TARGETFUNDRAISINGGOAL = 0
    SET @PARTICIPANTRECRUITGOAL = 0 
    SET @DONORRETENTIONGOAL = 0  
    SET @COMMUNICATIONGOAL = 0  
    SET @GROUPFUNDRAISINGGOAL = 0  
    SET @GROUPPARTICIPANTRECRUITMENTGOAL = 0  
    SET @GROUPDONORRETENTIONGOAL = 0
    SET @GROUPTEAMMEMBERSGOAL = 0 
    SET @GROUPTEAMMEMBERSRETENTIONGOAL = 0
    SET @GROUPTEAMSGOAL = 0 
    SET @GROUPTEAMSRETENTIONGOAL = 0
    SET @GROUPCOMMUNICATIONGOAL = 0 

    SELECT @REGISTRATIONOPTIONIDORIGINAL = EVENTPRICEID FROM V_QUERY_REGISTRANTREGISTRATION RR WHERE RR.REGISTRANTID = @ID

-- Bug #219326: throw error if user is having their reg option changed, and the new selected reg option is already at or exceeds max quantity.
  SELECT @MAXQUANTITY = MAXQUANTITY from dbo.FAFREGISTRATIONTYPE where EVENTPRICEID=@REGISTRATIONOPTIONID
  SELECT @USEDQUANTITY = COUNT(*) from dbo.REGISTRANTREGISTRATION where EVENTPRICEID=@REGISTRATIONOPTIONID

  IF @REGISTRATIONOPTIONIDORIGINAL <> @REGISTRATIONOPTIONID AND @MAXQUANTITY > 0 AND @USEDQUANTITY >= @MAXQUANTITY
    RAISERROR('The selected registration option is already at max capacity.',13,1)

 IF @ROLECODE = 3 OR @ROLECODE = 4 OR @ROLECODE = 2 OR @ROLECODE = 1
 BEGIN
   DECLARE @IsCompanyHouseholdMember bit       
    ,@IsCompanyHouseholdLeader bit           
    ,@IsCompanyTeamMember bit       
    ,@IsCompanyTeamLeader bit       

  EXEC dbo.USP_FAF_REGISTRANT_ROLE    
   @CONSTITUENTID = @CONSTITUENTID    
   ,@EVENTID =@EVENTID        
   ,@IsCompanyHouseholdMember   = @IsCompanyHouseholdMember OUTPUT    
   ,@IsCompanyHouseholdLeader   = @IsCompanyHouseholdLeader OUTPUT     
   ,@IsCompanyTeamMember   = @IsCompanyTeamMember OUTPUT    
   ,@IsCompanyTeamLeader   = @IsCompanyTeamLeader OUTPUT 

   if (@IsCompanyHouseholdMember = 1    
    OR @IsCompanyTeamMember = 1   
    OR @IsCompanyHouseholdLeader = 1    
    OR @IsCompanyTeamLeader = 1   
   ) 
  BEGIN
      declare @TEAMID uniqueidentifier
      SELECT @TEAMID = PARENTTEAMID    
   FROM dbo.UFN_FAF_GETTEAMINFO_BY_REGISTRANT(@CONSTITUENTID, @EVENTID)  
   if @ROLECODE = 3 OR @ROLECODE = 1   
       exec USP_FAFADDRESSBOOK_REGISTRANT_ADD @TEAMFUNDRAISINGTEAMID=@TEAMID, @REGISTRANTCONSTITUENTID=@CONSTITUENTID
   else
     BEGIN  
       declare @PARENTCONSTITUENTID uniqueidentifier
       select      
         @PARENTCONSTITUENTID = C.ID
         from dbo.TEAMFUNDRAISINGTEAMMEMBER TM  
         inner join dbo.TEAMEXTENSION TE
         on TM.TEAMFUNDRAISINGTEAMID = TE.TEAMFUNDRAISINGTEAMID  
         inner join dbo.TEAMFUNDRAISINGTEAM TFT
         on TM.TEAMFUNDRAISINGTEAMID = TFT.ID
         inner join dbo.TEAMFUNDRAISER TF  
         on TM.TEAMFUNDRAISERID = TF.ID  
         inner join dbo.CONSTITUENT C  
         on TF.CONSTITUENTID = C.ID  
         where TE.TEAMFUNDRAISINGTEAMID =@TEAMID

       declare @SequenceID int
       select @SequenceID = C.SEQUENCEID
          from dbo.CONSTITUENT C(nolock) 
          where C.ID = @PARENTCONSTITUENTID;  
       declare @ClientUsersID int;
       set @ClientUsersID = dbo.fnGetUserIDFromLinkedRecordID(@SequenceID, 0);
       declare @ADDRESSBOOKFAFID uniqueidentifier
       select @ADDRESSBOOKFAFID = ID from ADDRESSBOOKFAF where CONSTITUENTID=@CONSTITUENTID and CLIENTUSERSID=@ClientUsersID
       exec dbo.USP_ADDRESSBOOKFAF_DELETE @ID = @ADDRESSBOOKFAFID,@CHANGEAGENTID=NULL
     END
  END
 END

    IF @ROLECODE = 3 OR @ROLECODE = 4
        SELECT 
        @FUNDRAISINGGOAL = HG.MINFUNDRAISING,
        @TARGETFUNDRAISINGGOAL = HG.TARGETFUNDRAISING,
        @PARTICIPANTRECRUITGOAL = HG.RECRUITEMENT,
        @COMMUNICATIONGOAL = HG.COMMUNICATIONS,
        @DONORRETENTIONGOAL = HG.DONORRETENTION * 100
        FROM V_QUERY_HOUSEHOLDGOALS HG WHERE EVENTPRICEID = @REGISTRATIONOPTIONID    
    ELSE 
    BEGIN        
        SELECT  
        @FUNDRAISINGGOAL = IG.MINFUNDRAISING,
        @TARGETFUNDRAISINGGOAL = IG.TARGETFUNDRAISING,
        @PARTICIPANTRECRUITGOAL = IG.RECRUITEMENT,
        @DONORRETENTIONGOAL = IG.DONORRETENTION * 100,
        @COMMUNICATIONGOAL = IG.COMMUNICATIONS
        FROM V_QUERY_INDIVIDUALGOALS IG WHERE EVENTPRICEID = @REGISTRATIONOPTIONID

        IF @ROLECODE = 0 OR @ROLECODE = 5 
            SELECT 
            @GROUPFUNDRAISINGGOAL=CG.MINFUNDRAISING,
            @GROUPTEAMSGOAL=CG.TEAMS,
            @GROUPTEAMSRETENTIONGOAL=CG.TEAMRETENTION * 100,
            @GROUPTEAMMEMBERSGOAL=CG.TEAMMEMBERS,
            @GROUPTEAMMEMBERSRETENTIONGOAL=CG.TEAMMEMBERRETENTION * 100,
            @GROUPPARTICIPANTRECRUITMENTGOAL=CG.RECRUITEMENT,
            @GROUPCOMMUNICATIONGOAL=CG.COMMUNICATIONS
            FROM V_QUERY_COMPANYGOALS CG WHERE EVENTPRICEID = @REGISTRATIONOPTIONID
        ELSE IF @ROLECODE = 1 OR @ROLECODE = 2
            SELECT 
            @GROUPFUNDRAISINGGOAL=TG.MINFUNDRAISING,
            @GROUPTEAMSGOAL=0,
            @GROUPTEAMSRETENTIONGOAL=0,
            @GROUPTEAMMEMBERSGOAL=TG.TEAMMEMBERS,
            @GROUPTEAMMEMBERSRETENTIONGOAL=TG.TEAMMEMBERRETENTION * 100,
            @GROUPPARTICIPANTRECRUITMENTGOAL=TG.RECRUITEMENT,
            @GROUPCOMMUNICATIONGOAL=TG.COMMUNICATIONS
            FROM V_QUERY_TEAMGOALS TG WHERE EVENTPRICEID = @REGISTRATIONOPTIONID    
    END

    SET @CLIENTUSERID = dbo.UFN_CLIENTUSERID_GET_BY_CONSTITUENT(@CONSTITUENTID)

  if @ROLECODE <> 3 and @ROLECODE <> 4  -- Fix bug #133753: only reset goals if this is not about promoting/demoting within a household.
      exec dbo.USP_REGISTRANTREGISTRATION_CHANGEGOAL 
      @ID=@ID,
      @GROUPID=@GROUPID,
      @ROLECODE=@ROLECODE,
      @REGISTRATIONOPTIONID=@REGISTRATIONOPTIONID,
      @FUNDRAISINGGOAL=@FUNDRAISINGGOAL,
      @TARGETFUNDRAISINGGOAL=@TARGETFUNDRAISINGGOAL,
      @PARTICIPANTRECRUITGOAL=@PARTICIPANTRECRUITGOAL,
      @DONORRETENTIONGOAL=0,
      @COMMUNICATIONGOAL=@COMMUNICATIONGOAL,
      @GROUPFUNDRAISINGGOAL=@GROUPFUNDRAISINGGOAL,
      @GROUPPARTICIPANTRECRUITMENTGOAL=@GROUPPARTICIPANTRECRUITMENTGOAL,
      @GROUPDONORRETENTIONGOAL=@GROUPDONORRETENTIONGOAL,
      @GROUPTEAMMEMBERSGOAL=@GROUPTEAMMEMBERSGOAL,
      @GROUPTEAMMEMBERSRETENTIONGOAL=@GROUPTEAMMEMBERSRETENTIONGOAL,
      @GROUPTEAMSGOAL=@GROUPTEAMSGOAL,
      @GROUPTEAMSRETENTIONGOAL=@GROUPTEAMSRETENTIONGOAL,
      @GROUPCOMMUNICATIONGOAL=@GROUPCOMMUNICATIONGOAL,
    @SKIPGROUPGOALS=1 --Bug #195273 - don't update group goals when promoting/demoting on the front-end

    exec dbo.USP_REGISTRANTREGISTRATION_CHANGEREGISTRATIONOPTION 
    @ID=@ID,
    @CHANGEAGENTID=NULL,
    @REGISTRATIONOPTIONID=@REGISTRATIONOPTIONID,
    @REGISTRATIONOPTIONIDORIGINAL=@REGISTRATIONOPTIONIDORIGINAL,
    @ROLECODE=@ROLECODE,
    @ROLECODEORIGINAL=@ROLECODEORIGINAL

    exec dbo.USP_REGISTRANTREGISTRATION_CHANGE
    @ID=@ID,
    @EVENTID=@EVENTID,
    @CHANGEAGENTID=NULL,
    @TEAMFUNDRAISERID=NULL,
    @CONSTITUENTID=@CONSTITUENTID,
    @CONSTITUENTIDORIGINAL=@CONSTITUENTID,
    @GROUPID=@GROUPID,
    @GROUPIDORIGINAL=@GROUPID,
    @ROLECODE=@ROLECODE,
    @ROLECODEORIGINAL=@ROLECODEORIGINAL,
    @USERID=@CLIENTUSERID,
    @WAIVEBENEFITS=0

end try  
begin catch  
  exec USP_RAISE_ERROR  
  return 1;  
end catch  

return 0;