USP_REGISTRANTREGISTRATION_CHANGE

CREATE procedure dbo.USP_REGISTRANTREGISTRATION_CHANGE
(
   @ID uniqueidentifier, -- registrant ID

   @EVENTID uniqueidentifier,
   @CHANGEAGENTID uniqueidentifier = null,
   @TEAMFUNDRAISERID uniqueidentifier,
   @CONSTITUENTID uniqueidentifier,  -- current constituentID

   @CONSTITUENTIDORIGINAL uniqueidentifier, -- old registrant constituent ID

   @GROUPID uniqueidentifier,
   @GROUPIDORIGINAL uniqueidentifier,
     @ROLECODE tinyint,
   @ROLECODEORIGINAL tinyint,
   @USERID int,
   @WAIVEBENEFITS bit = 0
)
as
begin try
  declare @TeamFundraisingTeamMemberID uniqueidentifier,
          @IsGroupLeader bit = 0, 
          @captaincount int = 0,
          @TeamFundraisingTeamCaptainID uniqueidentifier,
          @fafroles nvarchar(50) = 'individual',
          @GroupType tinyint = null

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

  declare @CURRENTDATE datetime
  set @CURRENTDATE = getdate()


   /* update constituent if change */
   exec dbo.USP_REGISTRANTREGISTRATION_CHANGECONSTITUENT 
            @ID=@ID, 
            @EVENTID=@EVENTID, 
            @TEAMFUNDRAISERID=@TeamFundraiserID,
            @CHANGEAGENTID=@CHANGEAGENTID,
            @CONSTITUENTID=@CONSTITUENTID,
            @CONSTITUENTIDORIGINAL=@CONSTITUENTIDORIGINAL,
            @WAIVEBENEFITS=@WAIVEBENEFITS

  -- user constituentid here b/c there is a chance constituentid is different than constituentidoriginal and the team captain record is already change with the new constituent


  select @GroupType = TYPECODE from dbo.TEAMEXTENSION where TEAMFUNDRAISINGTEAMID = @GROUPID
  select @captaincount = COUNT(*) from dbo.TEAMFUNDRAISINGTEAMCAPTAIN where TEAMFUNDRAISINGTEAMID = @GROUPIDORIGINAL
    select @TeamFundraisingTeamCaptainID = ID from dbo.TEAMFUNDRAISINGTEAMCAPTAIN where CONSTITUENTID = @CONSTITUENTID 
    and TEAMFUNDRAISINGTEAMID = @GROUPIDORIGINAL
  select @TeamFundraisingTeamMemberID = ID from dbo.TEAMFUNDRAISINGTEAMMEMBER where TEAMFUNDRAISERID = @TEAMFUNDRAISERID and TEAMFUNDRAISINGTEAMID = @GROUPIDORIGINAL

  if @ROLECODE = 5 and @GroupType <> 2
  begin
    set @GROUPID = null  
    set @GroupType = 0
  end

  if (    (@GroupType = 1 and @ROLECODE not in (1,2) )
       or (@GroupType = 2 and @ROLECODE not in (0,5) )
       or (@GroupType = 3 and @ROLECODE not in (3,4) )
     )
  begin
  if (@GROUPID <> @GROUPIDORIGINAL)
     raiserror('MISMATCHROLEANDGROUPTTYPE', 13,1) 
   else
     raiserror('REQUIREROLEANDGROUPTTYPE', 13,1)     
  end

  if (( @GROUPID <> @GROUPIDORIGINAL and @ROLECODEORIGINAL in (0,1,3) ) 
            or( @ROLECODEORIGINAL in (0,1,3) and @ROLECODE in (2,4,5) ) )
  begin
      if @captaincount < 2
          raiserror('CANNOTMOVELEADER', 13,1)     
  end

  if @ROLECODE = 3
    set @fafroles = @fafroles + ',household leader'
  else
    if @ROLECODE < 2
      set @fafroles = @fafroles + ',team leader'  

  --if @ROLECODE = 5 -- Individual

    --set @GROUPID = null      


    if @GROUPID is not null 
  begin
        if @GROUPIDORIGINAL is not null
      begin
        if  @GROUPID <> @GROUPIDORIGINAL
        begin       
                if @ROLECODEORIGINAL = @ROLECODE -- same role

            begin
                      if (@ROLECODE in (0,1,3))    
                      begin    
                            update dbo.TEAMFUNDRAISINGTEAMCAPTAIN
                      set TEAMFUNDRAISINGTEAMID = @GROUPID,
                          CHANGEDBYID = @CHANGEAGENTID,
                          DATECHANGED = @CURRENTDATE
                      where ID = @TeamFundraisingTeamCaptainID                                           
                      end    
            end
                else -- different role : from member to leader or leader to member

                begin
                    if ( @ROLECODE in (0,1,3) and @ROLECODEORIGINAL in (2,4,5) )
              begin
                          insert into dbo.TEAMFUNDRAISINGTEAMCAPTAIN
                       (ID,TEAMFUNDRAISINGTEAMID,CONSTITUENTID,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
                        values
                            (newid(),@GROUPID,@CONSTITUENTID,@CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE); 

              end
                    else -- from leader to member or leader type to another leader type                 

              begin
                if (@ROLECODE in (0,1,3) and @ROLECODEORIGINAL in (0,1,3))
                    update dbo.TEAMFUNDRAISINGTEAMCAPTAIN
                      set TEAMFUNDRAISINGTEAMID = @GROUPID,
                          CHANGEDBYID = @CHANGEAGENTID,
                          DATECHANGED = @CURRENTDATE
                      where ID = @TeamFundraisingTeamCaptainID                
                else
                begin
                   if (@ROLECODE in (2,4,5) and @ROLECODEORIGINAL in (0,1,3))
                                exec dbo.USP_TEAMFUNDRAISINGTEAMCAPTAIN_DELETE 
                              @ID=@TeamFundraisingTeamCaptainID,
                              @CHANGEAGENTID=@CHANGEAGENTID
                end
              end
              end    

            update dbo.TEAMFUNDRAISINGTEAMMEMBER
              set TEAMFUNDRAISINGTEAMID = @GROUPID,
                  CHANGEDBYID = @CHANGEAGENTID,
                  DATECHANGED = @CURRENTDATE
              where TEAMFUNDRAISERID = @TEAMFUNDRAISERID 


        end -- end of groupid <> groupidoriginal

        else /* promoting, demoting within same group */
          if @ROLECODE <> @ROLECODEORIGINAL
                begin
            /* demoting within same group */
            if ( @ROLECODEORIGINAL in (0,1,3) and @ROLECODE in (2,4,5) )
                exec dbo.USP_TEAMFUNDRAISINGTEAMCAPTAIN_DELETE @ID=@TeamFundraisingTeamCaptainID , @CHANGEAGENTID=@CHANGEAGENTID              
            else 
            begin
              /* promoting member to leader */
              if ( @ROLECODEORIGINAL in (2,4,5) and @ROLECODE in (0,1,3) )
              begin
                            insert into dbo.TEAMFUNDRAISINGTEAMCAPTAIN
                          (ID,TEAMFUNDRAISINGTEAMID,CONSTITUENTID,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
                      values
                          (newid(),@GROUPID,@CONSTITUENTID,@CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE);  

              end
              /*else  -- change from head of household to team/company leader or team leader to company leader
                        if (@ROLECODE < 2 and @ROLECODEORIGINAL = 3) or (@ROLECODE = 0 and @ROLECODEORIGINAL = 1)
                            exec dbo.USP_ADDRESSBOOKFAF_BULKADD_CONTACTBYGROUP 
                             @GROUPID=@GROUPID, 
                             @REGISTRANTCONSTITUENTID=@CONSTITUENTID,
                             @ROLECODE=@ROLECODE
                             @CHANGEAGENTID=@CHANGEAGENTID
              */
            end                 
                end        
      end
        else    /* promoting from individual to other type of role*/
          if @GROUPIDORIGINAL is null
          begin
              if ( @ROLECODE in (0,1,3) )
                  set @IsGroupLeader = 1  

                  exec dbo.USP_FAF_TEAMMEMBER_ADD_EDIT 
                        @ID=@TeamFundraisingTeamMemberID output, 
                        @CHANGEAGENTID=@CHANGEAGENTID, 
                        @EVENTID = @EVENTID, 
                        @TEAMFUNDRAISINGTEAMID = @GROUPID, 
                        @REGISTRANTID = @ID, 
                        @ISTEAMCAPTAIN = @IsGroupLeader          
        end          
  end   
  else /* demoting registrant individual */
        if @GROUPIDORIGINAL is not null
        begin      
            -- delete member, delete leader from team if allow


        if (@ROLECODEORIGINAL in (0,1,3)) 
                  exec dbo.USP_TEAMFUNDRAISINGTEAMCAPTAIN_DELETE @ID=@TeamFundraisingTeamCaptainID , @CHANGEAGENTID=@CHANGEAGENTID

             exec dbo.USP_TEAMFUNDRAISINGTEAMMEMBER_DELETE  @ID=@TeamFundraisingTeamMemberID, @CHANGEAGENTID =  @CHANGEAGENTID
        end

-- update registrant address book and members address book if applicable

exec dbo.USP_ADDRESSBOOKFAF_BULKADD_CONTACTBYGROUP 
     @GROUPID=@GROUPID, 
     @REGISTRANTCONSTITUENTID=@CONSTITUENTID,
     @ROLECODE=@ROLECODE,
     @CHANGEAGENTID=@CHANGEAGENTID

-- update notification only if promoting from member to leader and demote from leader to member

if (  ( @ROLECODE in (0,1,3) and @ROLECODEORIGINAL in (2,4,5) )
      or ( @ROLECODEORIGINAL in (0,1,3)  and @ROLECODE in (2,4,5) ) )      
begin
  delete from dbo.NotificationSubscription where UserID = @USERID                                     
  exec dbo.USP_FAFNOTIFICATION_SUBSCRIPTION @UserID=@USERID, @EVENTID=@EVENTID, @FAFRoles=@fafroles 
end

end try
begin catch
  exec USP_RAISE_ERROR
  return 1;
end catch

return 0;