USP_REGISTRANTREGISTRATION_CHANGECONSTITUENT

CREATE procedure dbo.USP_REGISTRANTREGISTRATION_CHANGECONSTITUENT
(
  @ID uniqueidentifier,
  @EVENTID uniqueidentifier,
  @TEAMFUNDRAISERID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @CONSTITUENTID uniqueidentifier,
  @CONSTITUENTIDORIGINAL uniqueidentifier,  
  @WAIVEBENEFITS bit = 0
)
as
begin try

      declare @CURRENTDATE datetime,
            @GROUPID uniqueidentifier

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

      set @CURRENTDATE = getdate()

    if @CONSTITUENTID <> @CONSTITUENTIDORIGINAL
  begin
      if exists(select ID from dbo.REGISTRANT where CONSTITUENTID = @CONSTITUENTID and EVENTID = @EVENTID)
        raiserror('ALREADYREGISTRANT', 13,1)

      update dbo.REGISTRANT
        set CONSTITUENTID = @CONSTITUENTID,
            BENEFITSWAIVED = @WAIVEBENEFITS,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        where ID = @ID        

      select @GROUPID = TFTM.TEAMFUNDRAISINGTEAMID from dbo.TEAMFUNDRAISER TF
      join dbo.TEAMFUNDRAISINGTEAMMEMBER TFTM on TF.ID = TFTM.TEAMFUNDRAISERID
      where TF.ID = @TEAMFUNDRAISERID and TF.APPEALID in (select APPEALID from dbo.EVENT where ID = @EVENTID)

      /* update team fundraiser/captain if original constituent is changed and this constituent is a member in a group */ 

      if @GROUPID is not null
      begin
        update dbo.TEAMFUNDRAISER
          set CONSTITUENTID = @CONSTITUENTID,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
          where ID = @TEAMFUNDRAISERID

        /* updating teamcaptain constituent if exists */
        if exists(select ID from dbo.TEAMFUNDRAISINGTEAMCAPTAIN where CONSTITUENTID = @CONSTITUENTIDORIGINAL and TEAMFUNDRAISINGTEAMID = @GROUPID)
          update dbo.TEAMFUNDRAISINGTEAMCAPTAIN
            set CONSTITUENTID = @CONSTITUENTID,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE
            where TEAMFUNDRAISINGTEAMID = @GROUPID and CONSTITUENTID = @CONSTITUENTIDORIGINAL 

 -------  /* no need to update revenue if paid registration ?  */

      end

     delete from dbo.REVENUERECOGNITION 
      where REVENUERECOGNITION.ID in 
      (    Select RR.ID from dbo.REVENUERECOGNITION RR
              join dbo.REVENUESPLIT RS on RR.REVENUESPLITID = RS.ID
              join dbo.REVENUE R on RS.REVENUEID = R.ID
              join dbo.EVENT E on E.APPEALID = R.APPEALID
              join dbo.EVENTEXTENSION EX ON E.ID = EX.EVENTID
              where E.ID = @EVENTID and RR.CONSTITUENTID = @CONSTITUENTIDORIGINAL)

  end
  else
     update dbo.REGISTRANT
        set BENEFITSWAIVED = @WAIVEBENEFITS,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        where ID = @ID  

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