USP_MERGETASK_CONSTITUENTSOLICITCODES

CREATE procedure dbo.USP_MERGETASK_CONSTITUENTSOLICITCODES
(
  @SOURCEID uniqueidentifier,
  @TARGETID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier,
  @PRIMARYCRITERIA int = 0,
  @DELETEDUPES bit = 0
)
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 codes

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

  declare @CURRENTDATE datetime = getdate();

  -- Target has no codes, move source's over

  if not exists(select top 1 1 from dbo.CONSTITUENTSOLICITCODE where CONSTITUENTID = @TARGETID)
  begin
    update dbo.CONSTITUENTSOLICITCODE
    set
      CONSTITUENTID = @TARGETID,
      CHANGEDBYID = @CHANGEAGENTID,
      DATECHANGED = @CURRENTDATE
    where CONSTITUENTID = @SOURCEID;

    return 0;
  end

  -- @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,
    SOLICITCODEID uniqueidentifier,
    STARTDATE datetime,
    ENDDATE datetime,
    COMMENTS nvarchar(100),
    CONSENTCODE tinyint,
    HASDATECONFLICT bit -- Only used for consent-based codes

  );

  declare @ID uniqueidentifier;
  declare @SOLICITCODEID uniqueidentifier;
  declare @STARTDATE datetime;
  declare @ENDDATE datetime;
  declare @COMMENTS nvarchar(100);
  declare @ISFROMTARGET bit;
  declare @CONSENTCODE tinyint;

  declare RECORD_CURSOR cursor for
    select
      CONSTITUENTSOLICITCODE.ID,
      CONSTITUENTSOLICITCODE.SOLICITCODEID,
      CONSTITUENTSOLICITCODE.STARTDATE,
      CONSTITUENTSOLICITCODE.ENDDATE,
      CONSTITUENTSOLICITCODE.COMMENTS,
      case
        when CONSTITUENTSOLICITCODE.CONSTITUENTID = @SOURCEID then 0
        else 1
      end as ISFROMTARGET,
      SOLICITCODE.CONSENTCODE
    from dbo.CONSTITUENTSOLICITCODE
      inner join dbo.SOLICITCODE on CONSTITUENTSOLICITCODE.SOLICITCODEID = SOLICITCODE.ID
    where CONSTITUENTSOLICITCODE.CONSTITUENTID in (@SOURCEID, @TARGETID)
    order by
      CONSTITUENTSOLICITCODE.SOLICITCODEID,
      CONSTITUENTSOLICITCODE.STARTDATE,
      CONSTITUENTSOLICITCODE.ENDDATE;

  open RECORD_CURSOR;

  fetch next from RECORD_CURSOR
  into @ID, @SOLICITCODEID, @STARTDATE, @ENDDATE, @COMMENTS, @ISFROMTARGET, @CONSENTCODE;

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

    select @OVERLAPPINGID = ID
    from @MERGE_RECORDSET RECORDSET
    where
      RECORDSET.SOLICITCODEID = @SOLICITCODEID and
      dbo.UFN_DATES_AREDATESOVERLAPPING(@STARTDATE, @ENDDATE, RECORDSET.STARTDATE, RECORDSET.ENDDATE) = 1;

    if @OVERLAPPINGID is not null
    begin
      if @CONSENTCODE = 0 -- None

      begin
        update RECORDSET set
          -- Preserve data (i.e. specified date over null)

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

          ENDDATE = 
            case
              when RECORDSET.ENDDATE is null or @ENDDATE is null then null
              when RECORDSET.ENDDATE > @ENDDATE then RECORDSET.ENDDATE
              else @ENDDATE
            end,
          COMMENTS =
            case
              when ((@ISFROMTARGET = 1 and @COMMENTS <> '') or RECORDSET.COMMENTS = '') then @COMMENTS
              else RECORDSET.COMMENTS
            end,
          HASDATECONFLICT = 1
        from @MERGE_RECORDSET RECORDSET
        where RECORDSET.ID = @OVERLAPPINGID;
      end
      else -- GDPR or Advanced Consent

      begin
        -- Mark existing record as conflicting

        update RECORDSET set
        HASDATECONFLICT = 1
        from @MERGE_RECORDSET RECORDSET
        where ID = @OVERLAPPINGID;

        -- Add newly found code, since we don't fuse the date ranges here like standard codes

        -- We need to track all conflicting codes so they are not deleted later or if

        -- later codes conflict with this one

        insert into @MERGE_RECORDSET
          (ID, SOLICITCODEID, STARTDATE, ENDDATE, COMMENTS, CONSENTCODE, HASDATECONFLICT)
        values
          (@ID, @SOLICITCODEID, @STARTDATE, @ENDDATE, @COMMENTS, @CONSENTCODE, 1);
      end
    end
    else
    begin
      insert into @MERGE_RECORDSET
        (ID, SOLICITCODEID, STARTDATE, ENDDATE, COMMENTS, CONSENTCODE, HASDATECONFLICT)
      values
        (@ID, @SOLICITCODEID, @STARTDATE, @ENDDATE, @COMMENTS, @CONSENTCODE, 0);
    end

    fetch next from RECORD_CURSOR
    into @ID, @SOLICITCODEID, @STARTDATE, @ENDDATE, @COMMENTS, @ISFROMTARGET, @CONSENTCODE;
  end

  close RECORD_CURSOR;
  deallocate RECORD_CURSOR;

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

  if not @CHANGEAGENTID is null
    set context_info @CHANGEAGENTID;

  -- Delete any records that aren't in our final table (since they may have overlapped with the source)

  delete from CONSTITUENTSOLICITCODE
  from dbo.CONSTITUENTSOLICITCODE
    inner join dbo.SOLICITCODE on CONSTITUENTSOLICITCODE.SOLICITCODEID = SOLICITCODE.ID
  where
    SOLICITCODE.CONSENTCODE = 0 -- Standard only

    and CONSTITUENTSOLICITCODE.CONSTITUENTID = @TARGETID
    and CONSTITUENTSOLICITCODE.ID not in (select ID from @MERGE_RECORDSET);

  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

  update CONSTITUENTSOLICITCODE
  set
    CONSTITUENTID = @TARGETID,
    STARTDATE = RECORDSET.STARTDATE,
    ENDDATE = RECORDSET.ENDDATE,
    COMMENTS = RECORDSET.COMMENTS,
    CHANGEDBYID = @CHANGEAGENTID,
    DATECHANGED = @CURRENTDATE
  from dbo.CONSTITUENTSOLICITCODE
    inner join @MERGE_RECORDSET RECORDSET on RECORDSET.ID = CONSTITUENTSOLICITCODE.ID
  where RECORDSET.CONSENTCODE = 0 -- Standard only;


  -- Update consent-based records with no date conflict by just changing the constituent

  update CONSTITUENTSOLICITCODE
  set
    CONSTITUENTID = @TARGETID,
    CHANGEDBYID = @CHANGEAGENTID,
    DATECHANGED = @CURRENTDATE
  from dbo.CONSTITUENTSOLICITCODE
    inner join @MERGE_RECORDSET RECORDSET on RECORDSET.ID = CONSTITUENTSOLICITCODE.ID
  where
    RECORDSET.CONSENTCODE in (1, 2)   -- Consent-based

    and RECORDSET.HASDATECONFLICT = 0 -- No date conflicts;


  return 0;