USP_MERGE_GROUPMEMBERSHIP

CREATE procedure dbo.USP_MERGE_GROUPMEMBERSHIP
(
  @SOURCEID uniqueidentifier,
  @TARGETID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier,
  @DATECHANGED datetime
)
as
  set nocount on;

  --This code is similar to what USP_CONSTITUENCY_MERGE builds but the table does not follow the same structure

  --so I've broken it out here.


  --Source has no group memberships

  if not exists(select top 1 1 from dbo.GROUPMEMBER where MEMBERID = @SOURCEID)
    return 0;

  declare @CURRENTDATE datetime = getdate();
  declare @TARGETCURRENTHOUSEHOLD uniqueidentifier = null;

  select
    @TARGETCURRENTHOUSEHOLD = GROUPMEMBER.GROUPID
  from
    dbo.GROUPMEMBER
  inner join
    dbo.GROUPDATA on GROUPDATA.ID = GROUPMEMBER.GROUPID
  where
    GROUPMEMBER.MEMBERID = @TARGETID and
    dbo.UFN_GROUPMEMBER_ISCURRENTMEMBER(GROUPMEMBER.ID) = 1;

  --Find all the overlapping group memberships and if the source was the primary record

  declare @OVERLAP_GROUPS table(ID uniqueidentifier, SOURCEISPRIMARY bit);

  insert into @OVERLAP_GROUPS
  select
    GMS.GROUPID,
    max(convert(int, GMS.ISPRIMARY))
  from
    dbo.GROUPMEMBER GMS
  inner join
    dbo.GROUPMEMBER GMT on GMT.GROUPID = GMS.GROUPID
  where
    GMS.MEMBERID = @SOURCEID and
    GMT.MEMBERID = @TARGETID
  group by
    GMS.GROUPID;

  --Move over all the source group memberships that are not overlapping (don't move source current household if the target is currently in a household)

  update
    GM
  set
    MEMBERID = @TARGETID,
    CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CURRENTDATE
  from
    dbo.GROUPMEMBER GM
  inner join
    dbo.GROUPDATA on GROUPDATA.ID = GM.GROUPID
  left join
    dbo.GROUPMEMBERDATERANGE GMDR on GMDR.GROUPMEMBERID = GM.ID
  where
    GM.MEMBERID = @SOURCEID and
    GM.GROUPID <> @TARGETID and --Make sure source group is not a member of target

    not exists (select 1 from @OVERLAP_GROUPS OG where GM.GROUPID = OG.ID) and
    (GROUPDATA.GROUPTYPECODE <> 0 or (@TARGETCURRENTHOUSEHOLD is null or GMDR.DATETO is not null));

  -- If a household was merged to the target constituent, the household

  -- name will need to be updated to reflect the new constituent.

  exec dbo.USP_HOUSEHOLD_REFRESHNAME @TARGETID, @CHANGEAGENTID

  --Grab all the dates for the given overlapping groups and remove any dates that are disjoint

  declare @DATES table(GROUPID uniqueidentifier, DATEFROM datetime, DATETO datetime);

  insert into @DATES
  select
    GROUPID,
    DATEFROM,
    DATETO
  from
    @OVERLAP_GROUPS OLG
  inner join
    dbo.GROUPMEMBER on GROUPMEMBER.GROUPID = OLG.ID
  left join
    dbo.GROUPMEMBERDATERANGE GMDR on GMDR.GROUPMEMBERID = GROUPMEMBER.ID
  where
    GROUPMEMBER.MEMBERID in (@SOURCEID, @TARGETID);

  delete D1 from
    @DATES D1
  inner join
    @DATES D2 on D2.GROUPID = D1.GROUPID and coalesce(D1.DATETO, '9999-12-31') < coalesce(dateadd(day, -1, D2.DATEFROM), '1753-01-01');

  --Find the latest end date (include null) and the earliest start date (exclude null if possible) for each overlapping group

  declare @GROUPDATES table(GROUPID uniqueidentifier, DATEFROM datetime, DATETO datetime);

  insert into @GROUPDATES
  select
    GROUPID,
    case
      when exists(select 1 from @DATES where DATEFROM is null)
        then null
      else
        min(DATEFROM)
      end as DATEFROM,
    case
      when exists(select 1 from @DATES where DATETO is null)
        then null
      else
      max(DATETO)
    end as DATETO
  from
    @DATES
  group by
    GROUPID;

  update
    GMDR
  set
    DATEFROM = SUBQ.DATEFROM,
    DATETO = SUBQ.DATETO,
    CHANGEDBYID = @CHANGEAGENTID,
    DATECHANGED = @DATECHANGED
  from
    dbo.GROUPMEMBERDATERANGE GMDR
  inner join
    dbo.GROUPMEMBER on GROUPMEMBER.ID = GMDR.GROUPMEMBERID
  inner join
    @GROUPDATES SUBQ on SUBQ.GROUPID = GROUPMEMBER.GROUPID
  where
    GROUPMEMBER.MEMBERID = @TARGETID;

  insert into dbo.GROUPMEMBERDATERANGE(GROUPMEMBERID, DATEFROM, DATETO, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
  select
    GROUPMEMBER.ID,
    DATEFROM = SUBQ.DATEFROM,
    DATETO = SUBQ.DATETO,
    @CHANGEAGENTID, @CHANGEAGENTID, @DATECHANGED, @DATECHANGED
  from
    dbo.GROUPMEMBER
  inner join
    @GROUPDATES    SUBQ on SUBQ.GROUPID = GROUPMEMBER.GROUPID
  where
    GROUPMEMBER.MEMBERID = @TARGETID and
    not exists(select 1 from dbo.GROUPMEMBERDATERANGE GMDR where GMDR.GROUPMEMBERID = GROUPMEMBER.ID);


  --Update the group member roles    


  --@MERGE_RECORDSET will hold the end result of our operations

  --We will use this table to do the final updates back to the record table

  declare @MERGE_RECORDSET table
  (
  ID uniqueidentifier,
    GROUPID uniqueidentifier,
    GROUPMEMBERROLECODEID uniqueidentifier,
    STARTDATE datetime,
    ENDDATE datetime
  );

  declare @ID uniqueidentifier;
  declare @GROUPID uniqueidentifier;
  declare @GROUPMEMBERROLECODEID uniqueidentifier;
  declare @STARTDATE datetime;
  declare @ENDDATE datetime;

  declare RECORD_CURSOR cursor for
    select
      GROUPMEMBERROLE.ID,
      GROUPMEMBER.GROUPID,
      GROUPMEMBERROLE.GROUPMEMBERROLECODEID,
      GROUPMEMBERROLE.STARTDATE,
      GROUPMEMBERROLE.ENDDATE
    from
    dbo.GROUPMEMBERROLE
  inner join
    dbo.GROUPMEMBER on GROUPMEMBER.ID = GROUPMEMBERROLE.GROUPMEMBERID
  inner join
    @OVERLAP_GROUPS OLG on OLG.ID = GROUPMEMBER.GROUPID
    where
      GROUPMEMBER.MEMBERID in (@SOURCEID, @TARGETID)
    order by
      GROUPMEMBERROLECODEID,
      STARTDATE,
      ENDDATE

  open RECORD_CURSOR;

  fetch next from RECORD_CURSOR
  into @ID, @GROUPID, @GROUPMEMBERROLECODEID, @STARTDATE, @ENDDATE;

  while @@FETCH_STATUS = 0
  begin
    declare @OVERLAPPINGID uniqueidentifier = null;

    select
      @OVERLAPPINGID = ID
    from
      @MERGE_RECORDSET MRS
    where
    MRS.GROUPID = @GROUPID and
      MRS.GROUPMEMBERROLECODEID = @GROUPMEMBERROLECODEID and
      (
        coalesce(MRS.STARTDATE, '1753-01-01') between coalesce(@STARTDATE, '1753-01-01') and coalesce(@ENDDATE, '9999-12-31') or
        coalesce(MRS.ENDDATE, '9999-12-31') between coalesce(@STARTDATE, '1753-01-01') and coalesce(@ENDDATE, '9999-12-31') or
        coalesce(@STARTDATE, '1753-01-01') between coalesce(MRS.STARTDATE, '1753-01-01') and coalesce(MRS.ENDDATE, '9999-12-31') or
        coalesce(@ENDDATE, '9999-12-31') between coalesce(MRS.STARTDATE, '1753-01-01') and coalesce(MRS.ENDDATE, '9999-12-31')
      )

    if @OVERLAPPINGID is not null
    begin
      update MRS set
        --Preserve data (i.e. specified date over null)

        STARTDATE =
          case
            when MRS.STARTDATE is null
              then @STARTDATE
            when @STARTDATE is null
              then MRS.STARTDATE
            when MRS.STARTDATE < @STARTDATE
              then MRS.STARTDATE
            else
              @STARTDATE
          end,
        --Preserve status (i.e. currently a member over specified date)

        ENDDATE = 
          case
            when MRS.ENDDATE is null or @ENDDATE is null
              then null
            when MRS.ENDDATE > @ENDDATE
              then MRS.ENDDATE
            else
              @ENDDATE
          end
      from
        @MERGE_RECORDSET MRS
      where
        MRS.ID = @OVERLAPPINGID;
    end

    else
    begin
      insert into @MERGE_RECORDSET
        (ID, GROUPID, GROUPMEMBERROLECODEID, STARTDATE, ENDDATE)
      values
        (@ID, @GROUPID, @GROUPMEMBERROLECODEID, @STARTDATE, @ENDDATE)
    end

    fetch next from RECORD_CURSOR
    into @ID, @GROUPID, @GROUPMEMBERROLECODEID, @STARTDATE, @ENDDATE;
  end

  close RECORD_CURSOR;
  deallocate RECORD_CURSOR;

  declare @CONTEXTCACHE varbinary(128);
  set @CONTEXTCACHE = context_info();

  if not @CHANGEAGENTID is null
    set context_info @CHANGEAGENTID;

  if not @CONTEXTCACHE is null
    set context_info @CONTEXTCACHE;

  --Update the records in our final set with the new dates and to point to the target

  delete GROUPMEMBERROLE from
    dbo.GROUPMEMBERROLE
  inner join
    dbo.GROUPMEMBER on GROUPMEMBER.ID = GROUPMEMBERROLE.GROUPMEMBERID
  inner join
    @OVERLAP_GROUPS OLG on OLG.ID = GROUPMEMBER.GROUPID
  where
    GROUPMEMBER.MEMBERID = @TARGETID and
    not exists(select ID from @MERGE_RECORDSET where ID = GROUPMEMBERROLE.ID);

  --Update the records in our final set with the new dates and to point to the target

  update 
  GROUPMEMBERROLE
  set
    GROUPMEMBERID = GMT.ID,
    STARTDATE = MRS.STARTDATE,
    ENDDATE = MRS.ENDDATE,
    CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CURRENTDATE
  from
    dbo.GROUPMEMBERROLE
  inner join
    @MERGE_RECORDSET MRS on MRS.ID = GROUPMEMBERROLE.ID
  inner join
  dbo.GROUPMEMBER GMT on GMT.MEMBERID = @TARGETID and GMT.GROUPID = MRS.GROUPID;

  --End update group member roles


  exec dbo.USP_CONSTITUENTMERGE_UPDATEGROUPADDRESSMAILPREFS @TARGETID, @SOURCEID, @CHANGEAGENTID, 0
  exec dbo.USP_CONSTITUENTMERGE_UPDATEGROUPEMAILMAILPREFS @TARGETID, @SOURCEID, @CHANGEAGENTID, 0

  --Get rid of the source member records

  declare @GROUPMEMBERIDS table (ID uniqueidentifier)
  insert into @GROUPMEMBERIDS(ID)
  select GM.ID from dbo.GROUPMEMBER as GM where GM.MEMBERID = @SOURCEID

  delete from dbo.GROUPMEMBERROLE where GROUPMEMBERID in (select ID from @GROUPMEMBERIDS)
  delete from dbo.GROUPMEMBERDATERANGE where GROUPMEMBERID in (select ID from @GROUPMEMBERIDS)
  delete from dbo.GROUPMEMBER where MEMBERID = @SOURCEID

  --Associate the old source primary group memberships that were overlapping to the target

  update
    GM
  set
    ISPRIMARY = 1,
  CHANGEDBYID = @CHANGEAGENTID,
  DATECHANGED = @DATECHANGED
  from
    dbo.GROUPMEMBER GM
  left join
    dbo.GROUPMEMBERDATERANGE GMDR on GMDR.GROUPMEMBERID = GM.ID
  where
    MEMBERID = @TARGETID and
    exists(select 1 from @OVERLAP_GROUPS OLG where OLG.ID = GROUPID and OLG.SOURCEISPRIMARY = 1) and
    not exists(select 1 from @GROUPDATES GD where GD.GROUPID = GROUPID and (coalesce(GMDR.DATEFROM, '1753-01-01') = coalesce(GD.DATEFROM, '1753-01-01')
      and coalesce(GMDR.DATETO, '9999-12-31') = coalesce(GD.DATETO, '9999-12-31')));

  return 0;