USP_DATAFORMTEMPLATE_EDIT_FAFGROUPMERGE

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_FAFGROUPMERGE (
    @ID uniqueidentifier,
     @CHANGEAGENTID uniqueidentifier = null,
  @GROUPTOMERGEID uniqueidentifier,
  @GROUPTOKEEPID uniqueidentifier
)
as

    set nocount on;

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

    declare @CURRENTDATE datetime,
        @GroupToKeepEventID uniqueidentifier,
        @GroupToMergeEventID uniqueidentifier,
        @GROUPTOKEEPCONSTITUENTID uniqueidentifier,
        @GROUPTOMERGECONSTITUENTID uniqueidentifier,
        @REGISTRANTCONSTITUENTID uniqueidentifier,
        @RoleCode tinyint,
        @GroupID uniqueidentifier

    set @CURRENTDATE = getdate()

  DECLARE @TYPECODE_from int, @TYPECODE_TO int, @STATUSCODE int

  select 
    @TYPECODE_from= TYPECODE, 
    @GroupToMergeEventID=EVENTID,  
    @GROUPTOMERGECONSTITUENTID=TEAMCONSTITUENTID
  from dbo.TEAMEXTENSION     
  where TEAMFUNDRAISINGTEAMID= @ID

  select 
    @TYPECODE_TO= TYPECODE, 
    @GroupToKeepEventID=EVENTID ,
    @GROUPTOKEEPCONSTITUENTID=TEAMCONSTITUENTID, 
    @STATUSCODE= STATUSCODE 
  from dbo.TEAMEXTENSION
  where TEAMFUNDRAISINGTEAMID= @GROUPTOKEEPID

    begin try

  if @GroupToMergeEventID <> @GroupToKeepEventID
    raiserror('GROUPNOTUNDERSAMEEVENT',13,1)

  IF @STATUSCODE <> 0 
     RAISERROR('ACTIVEGROUPSONLY', 13,1)

  IF (@TYPECODE_TO IN (1,3) AND @TYPECODE_from= 2) OR (@TYPECODE_TO =3 AND @TYPECODE_from= 1)
     RAISERROR('REVERSEHIERARCHY', 13,1)

  if ((@TYPECODE_from IN (1, 3) AND @TYPECODE_TO= 2) OR (@TYPECODE_from =3 AND @TYPECODE_TO= 1))
    begin
      select TFTC.CONSTITUENTID, TFTC.TEAMFUNDRAISINGTEAMID, MR.RoleCode into #tmpTeamCaptain
      from dbo.TEAMFUNDRAISINGTEAMCAPTAIN TFTC
      join dbo.UFN_REGISTRANT_GETFAFROLE(@GroupToMergeEventID, @GROUPTOMERGEID) MR on TFTC.CONSTITUENTID = MR.CONSTITUENTID
      where TFTC.TEAMFUNDRAISINGTEAMID = @GROUPTOMERGEID  

      insert into #tmpTeamCaptain
      select TFTC.CONSTITUENTID, TFTC.TEAMFUNDRAISINGTEAMID, MR.RoleCode 
      from dbo.TEAMFUNDRAISINGTEAMCAPTAIN TFTC
      join dbo.UFN_REGISTRANT_GETFAFROLE(@GroupToKeepEventID, @GROUPTOKEEPID) MR on TFTC.CONSTITUENTID = MR.CONSTITUENTID
      where TFTC.TEAMFUNDRAISINGTEAMID = @GROUPTOKEEPID 
    end
  else
    begin
      select TF.CONSTITUENTID, @GROUPTOKEEPID as TEAMFUNDRAISINGTEAMID, MR.RoleCode into #tmpTeamMembers
      from dbo.TEAMFUNDRAISINGTEAMMEMBER TFTM
      join dbo.TEAMFUNDRAISER TF on TFTM.TEAMFUNDRAISERID = TF.ID  
      join dbo.UFN_REGISTRANT_GETFAFROLE(@GroupToKeepEventID, @GROUPTOMERGEID) MR on TF.CONSTITUENTID = MR.CONSTITUENTID
      where TFTM.TEAMFUNDRAISINGTEAMID = @GROUPTOMERGEID

      insert into #tmpTeamMembers
      select TF.CONSTITUENTID, TFTM.TEAMFUNDRAISINGTEAMID, MR.RoleCode
      from dbo.TEAMFUNDRAISINGTEAMMEMBER TFTM
      join dbo.TEAMFUNDRAISER TF on TFTM.TEAMFUNDRAISERID = TF.ID  
      join dbo.UFN_REGISTRANT_GETFAFROLE(@GroupToKeepEventID, @GROUPTOKEEPID) MR on TF.CONSTITUENTID = MR.CONSTITUENTID
      where TFTM.TEAMFUNDRAISINGTEAMID = @GROUPTOKEEPID
    end

  --handle updating the data

  if @STATUSCODE= 0 AND ((@TYPECODE_from IN (1, 3) AND @TYPECODE_TO= 2) OR (@TYPECODE_from =3 AND @TYPECODE_TO= 1))
  begin
     update dbo.TEAMFUNDRAISINGTEAM 
     set PARENTTEAMID= @GROUPTOKEEPID,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
     from TEAMFUNDRAISINGTEAM 
     where ID= @ID

       -- update address book

      while exists(select top 1 CONSTITUENTID from #tmpTeamCaptain)
      begin
          select top 1 @REGISTRANTCONSTITUENTID=CONSTITUENTID, @GroupID=TEAMFUNDRAISINGTEAMID, @RoleCode=RoleCode from #tmpTeamCaptain

          exec dbo.USP_ADDRESSBOOKFAF_BULKADD_CONTACTBYGROUP 
                     @GROUPID=@GroupID, 
                     @CHANGEAGENTID=@CHANGEAGENTID,
                     @ROLECODE=@RoleCode,
                     @REGISTRANTCONSTITUENTID=@REGISTRANTCONSTITUENTID

          delete from #tmpTeamCaptain where CONSTITUENTID = @REGISTRANTCONSTITUENTID

          continue
      end

      drop table #tmpTeamCaptain      

  end
  else
    if @STATUSCODE= 0 AND ((@TYPECODE_from= 2 AND @TYPECODE_TO= 2) OR (@TYPECODE_from= 1 AND @TYPECODE_TO= 1) OR (@TYPECODE_from= 3 AND @TYPECODE_TO= 3))
    begin
          update TEAMFUNDRAISINGTEAMMEMBER   
        set TEAMFUNDRAISINGTEAMID= @GROUPTOKEEPID,
            CHANGEDBYID=@CHANGEAGENTID,
            DATECHANGED=@CURRENTDATE
        from TEAMFUNDRAISINGTEAMMEMBER    
        where TEAMFUNDRAISINGTEAMID= @ID    

            update TEAMFUNDRAISINGTEAMCAPTAIN  
        set TEAMFUNDRAISINGTEAMID= @GROUPTOKEEPID,
            CHANGEDBYID=@CHANGEAGENTID,
            DATECHANGED=@CURRENTDATE
        from TEAMFUNDRAISINGTEAMCAPTAIN    
        where TEAMFUNDRAISINGTEAMID= @ID    

       update dbo.TEAMFUNDRAISINGTEAM 
       set PARENTTEAMID = @GROUPTOKEEPID ,
           CHANGEDBYID=@CHANGEAGENTID,
           DATECHANGED=@CURRENTDATE
       from TEAMFUNDRAISINGTEAM 
       where PARENTTEAMID = @GROUPTOMERGEID 

       update dbo.REGISTRANTBATCH
       set TEAMFUNDRAISINGTEAMID = @GROUPTOKEEPID,
           CHANGEDBYID=@CHANGEAGENTID,
           DATECHANGED=@CURRENTDATE
       where TEAMFUNDRAISINGTEAMID = @GROUPTOMERGEID 


       delete from TEAMFUNDRAISINGTEAM      where ID= @ID    
       --- only need to delete from main Team Fundraising Team table, cascade delete action will take care of the rest.

       ------delete from TEAMEXTENSION                  where TEAMFUNDRAISINGTEAMID= @ID    


       -- move general recognition of team to the keep team

       update dbo.REVENUERECOGNITION
       set CONSTITUENTID = @GROUPTOKEEPCONSTITUENTID,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
       where CONSTITUENTID = @GROUPTOMERGECONSTITUENTID and REVENUESPLITID in 
       (select RS.ID from dbo.REVENUESPLIT RS
                  join dbo.REVENUE R on RS.REVENUEID = R.ID 
                  join dbo.EVENT E on E.APPEALID = R.APPEALID
                  where E.ID = @GroupToMergeEventID)

       -- update address book

      while exists(select top 1 CONSTITUENTID from #tmpTeamMembers)
      begin
          select top 1 @REGISTRANTCONSTITUENTID = CONSTITUENTID, @RoleCode=RoleCode from #tmpTeamMembers

          exec dbo.USP_ADDRESSBOOKFAF_BULKADD_CONTACTBYGROUP 
                     @GROUPID=@GROUPTOKEEPID, 
                     @CHANGEAGENTID=@CHANGEAGENTID,
                     @ROLECODE=@RoleCode,
                     @REGISTRANTCONSTITUENTID=@REGISTRANTCONSTITUENTID

          delete from #tmpTeamMembers where CONSTITUENTID = @REGISTRANTCONSTITUENTID

          continue
      end

      drop table #tmpTeamMembers             
    end    


  end try

    begin catch
        exec dbo.USP_RAISE_ERROR
        return 1
    end catch

return 0;