USP_MKTSEGMENTATIONPASSIVE_MATCHBACK

CREATE procedure dbo.[USP_MKTSEGMENTATIONPASSIVE_MATCHBACK]
(
  @SEGMENTATIONID uniqueidentifier
)
as
  set nocount on;

  declare @MAILINGTYPECODE tinyint;
  declare @SEGMENTID uniqueidentifier;
  declare @SEGMENTSOURCECODE nvarchar(255);
  declare @MATCHBACKTABLENAME nvarchar(128);
  declare @RECORDSOURCEID uniqueidentifier;

  declare @GIFTVIEWNAME nvarchar(255);
  declare @GIFTVIEWDONORIDFIELD nvarchar(255);
  declare @GIFTVIEWAPPEALIDFIELD nvarchar(255);
  declare @GIFTVIEWAPPEALSYSTEMIDFIELD nvarchar(255);
  declare @GIFTVIEWMAILINGIDFIELD nvarchar(255);
  declare @GIFTVIEWSOURCECODEFIELD nvarchar(255);
  declare @GIFTVIEWFINDERNUMBERFIELD nvarchar(255);
  declare @GIFTVIEWPRIMARYKEYFIELD nvarchar(255);
  declare @APPEALID nvarchar(100);
  declare @APPEALSYSTEMID nvarchar(36);

  declare @SQL nvarchar(max);
  declare @PARAMDEF nvarchar(255);
  declare @DATATABLENAME nvarchar(128);
  declare @MAILINGID nvarchar(36);
  declare @TEMPTABLENAME nvarchar(128);
  declare @ISBBEC bit;
  declare @ACTIVE bit;
  declare @FINDERNUMBER bigint;

  begin try
    set @ISBBEC = (case when dbo.[UFN_INSTALLEDPRODUCTS_PRODUCTIS]('BB9873D7-F1ED-430A-8AB4-F09F47056538') = 0 then 1 else 0 end);

    select
      @MAILINGTYPECODE = [MAILINGTYPECODE],
      @MAILINGID = (case when @ISBBEC = 1 then convert(nvarchar(36), [ID]) else convert(nvarchar(36), [IDINTEGER]) end),
      @DATATABLENAME = dbo.[UFN_MKTSEGMENTATIONACTIVATE_MAKETABLENAME]([ID]),
      @ACTIVE = [ACTIVE]
    from dbo.[MKTSEGMENTATION]
    where [ID] = @SEGMENTATIONID;

    if @MAILINGTYPECODE <> 4 raiserror('Invalid marketing effort type.', 13, 1);

    -- always start from scratch so we get the most accurate gift information

    -- if the appealID changed on a gift, then that needs to be reflected here, so the matchback is removed from this mailing

    exec dbo.[USP_MKTSEGMENTATION_MATCHBACKDELETE] @SEGMENTATIONID, 1;

    -- loop through each segment in the mailing and insert donor matchback info

    declare SEGMENTCURSOR cursor local fast_forward for
      select 
        [MKTSEGMENTATIONSEGMENT].[ID],
        dbo.[UFN_MKTSOURCECODE_BUILDCODE]([MKTSEGMENTATIONSEGMENT].[ID], default, default),
        [MKTSEGMENT].[QUERYVIEWCATALOGID]
      from dbo.[MKTSEGMENTATIONSEGMENT]
      inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
      where [SEGMENTATIONID] = @SEGMENTATIONID;

    open SEGMENTCURSOR;
    fetch next from SEGMENTCURSOR into @SEGMENTID, @SEGMENTSOURCECODE, @RECORDSOURCEID;

    while (@@FETCH_STATUS = 0)
    begin
      -- gather some info about the gift view we'll be using

      select distinct
        @GIFTVIEWNAME = [QUERYVIEWCATALOG].[OBJECTNAME],
        @GIFTVIEWPRIMARYKEYFIELD = [QUERYVIEWCATALOG].[PRIMARYKEYFIELD],
        @GIFTVIEWDONORIDFIELD = [MKTGIFTRECORDSOURCE].[DONORIDFIELD],
        @GIFTVIEWAPPEALIDFIELD = [MKTGIFTRECORDSOURCE].[APPEALIDFIELD],
        @GIFTVIEWAPPEALSYSTEMIDFIELD = [MKTGIFTRECORDSOURCE].[APPEALSYSTEMIDFIELD],
        @GIFTVIEWMAILINGIDFIELD = [MKTGIFTRECORDSOURCE].[MAILINGIDFIELD],
        @GIFTVIEWSOURCECODEFIELD = [MKTGIFTRECORDSOURCE].[SOURCECODEFIELD],
        @APPEALID = isnull([MKTSEGMENTATIONACTIVATE].[APPEALID],''),
        @APPEALSYSTEMID = isnull([MKTSEGMENTATIONACTIVATE].[APPEALSYSTEMID],''),
        @MATCHBACKTABLENAME = dbo.[UFN_MKTRECORDSOURCE_MAKEMATCHBACKTABLENAME]([MKTGIFTRECORDSOURCE].[ID])
      from dbo.[MKTGIFTRECORDSOURCE]
      inner join dbo.[MKTSEGMENTATIONACTIVATE] on [MKTSEGMENTATIONACTIVATE].[SEGMENTATIONID] = @SEGMENTATIONID and [MKTSEGMENTATIONACTIVATE].[RECORDSOURCEID] = [MKTGIFTRECORDSOURCE].[ID]
      inner join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[ID] = [MKTGIFTRECORDSOURCE].[QUERYVIEWCATALOGID]
      where [MKTGIFTRECORDSOURCE].[ID] = @RECORDSOURCEID;

      -- create a global temp table because we need to use it in the dynamic sql below

      set @TEMPTABLENAME = '##TEMP_' + replace(cast(newid() as nvarchar(36)), '-', '_');

      -- select the donor ID and the source system donor ID into a temp table...

      set @SQL = 'select' + char(13) +
                 '  newid() as [ID],' + char(13) +
                 '  [' + @GIFTVIEWDONORIDFIELD + '] as [DONORID],' + char(13) +
                 '  [' + @GIFTVIEWPRIMARYKEYFIELD + '] as [GIFTID]' + char(13) +
                 'into dbo.[' + @TEMPTABLENAME + ']' + char(13) +
                 'from dbo.[' + @GIFTVIEWNAME + ']' + char(13) +
                 'where ' + (case when len(@APPEALSYSTEMID) > 0
                               then '[' + @GIFTVIEWAPPEALSYSTEMIDFIELD + '] = @APPEALSYSTEMID'
                               else '[' + @GIFTVIEWAPPEALIDFIELD + '] = @APPEALID'
                             end) + char(13) +
                 'and [' + @GIFTVIEWMAILINGIDFIELD + '] = @MAILINGID' + char(13) +
                 'and [' + @GIFTVIEWSOURCECODEFIELD + '] = @SEGMENTSOURCECODE';
      exec sp_executesql @SQL, N'@APPEALSYSTEMID nvarchar(36), @APPEALID nvarchar(100), @MAILINGID nvarchar(36), @SEGMENTSOURCECODE nvarchar(255)', @APPEALSYSTEMID = @APPEALSYSTEMID, @APPEALID = @APPEALID, @MAILINGID = @MAILINGID, @SEGMENTSOURCECODE = @SEGMENTSOURCECODE;

      -- update the temp table so that donors that gave multiple gifts will have the same ID

      set @SQL = 'update dbo.[' + @TEMPTABLENAME + '] set' + char(13) +
                 '  [ID] = (select top 1 [T1].[ID] from dbo.[' + @TEMPTABLENAME + '] as [T1] where [T1].[DONORID] = [' + @TEMPTABLENAME + '].[DONORID])';
      exec (@SQL);

      --Update any gifts that may already be matched back for a different mailing, because they would not have gotten deleted above...

      --Insert matched records into the matchback table...

      --Delete any old records that were previously matched to this mailing but are not anymore...

      set @SQL = 'merge into dbo.[' + @MATCHBACKTABLENAME + '] t' + char(13) +
                  'using dbo.[' + @TEMPTABLENAME + '] s on s.[GIFTID] = t.[GIFTID]' + char(13) +
                  'when matched and (s.[ID] <> t.[ID] or s.[DONORID] <> t.[DONORID] or t.[SEGMENTATIONID] <> @SEGMENTATIONID) then' + char(13) +
                  '  update set [ID] = s.[ID], [DONORID] = s.[DONORID], [SEGMENTATIONID] = @SEGMENTATIONID' + char(13) +
                  'when not matched then' + char(13) +
                  '  insert ([ID], [DONORID], [GIFTID], [SEGMENTATIONID])' + char(13) +
                  '  values (s.[ID], s.[DONORID], s.[GIFTID], @SEGMENTATIONID);';
      exec sp_executesql @SQL, N'@SEGMENTATIONID uniqueidentifier', @SEGMENTATIONID = @SEGMENTATIONID;

      -- get smallest finder number to count back from

      set @SQL = 'select @FINDERNUMBER = isnull(min([FINDERNUMBER]),0) from dbo.[' + @DATATABLENAME + '];';
      exec sp_executesql @SQL, N'@FINDERNUMBER bigint output', @FINDERNUMBER = @FINDERNUMBER output;
      if @FINDERNUMBER > 0
        set @FINDERNUMBER = 0;

      -- insert the donors into the mailing activated data table

      set @SQL = 'insert into dbo.[' + @DATATABLENAME + '] ([FINDERNUMBER], [SEGMENTID], [DONORID], [DONORQUERYVIEWCATALOGID], [SOURCECODE]' + (case when @ISBBEC = 1 then ', [CONSTITUENTAPPEALID])' else ')' end) + char(13) +
                 '  select' + char(13) +
                 '    @FINDERNUMBER - row_number() over(order by  [TEMP].[ID]),' + char(13) +
                 '    @SEGMENTID,' + char(13) +
                 '    [TEMP].[ID],' + char(13) +
                 '    null,' + char(13) +
                 '    @SEGMENTSOURCECODE';

      if @ISBBEC = 1
        set @SQL = @SQL + ',' + char(13) +
                   '    (select top 1 [ID]' + char(13) +
                   '     from dbo.[CONSTITUENTAPPEAL]' + char(13) +
                   '     where [CONSTITUENTID] = [TEMP].[DONORID]' + char(13) +
                   '     and [MKTSEGMENTATIONSEGMENTID] = @SEGMENTID' + char(13) +
                   '     and [MKTSEGMENTATIONTESTSEGMENTID] is null' + char(13) +
                   '     and ([FINDERNUMBER] = 0' + char(13) +
                   '       or [FINDERNUMBER] = (select min([FINDERNUMBER])' + char(13) +
                   '                            from dbo.[CONSTITUENTAPPEAL]' + char(13) +
                   '                            where [CONSTITUENTID] = [TEMP].[DONORID]' + char(13) +
                   '                            and [MKTSEGMENTATIONSEGMENTID] = @SEGMENTID' + char(13) +
                   '                            and [MKTSEGMENTATIONTESTSEGMENTID] is null)))';

      set @SQL = @SQL + char(13) + '  from (select distinct [ID], [DONORID] from dbo.[' + @TEMPTABLENAME + ']) as [TEMP]';

      exec sp_executesql @SQL, N'@FINDERNUMBER bigint, @SEGMENTID uniqueidentifier, @SEGMENTSOURCECODE nvarchar(255)', @FINDERNUMBER = @FINDERNUMBER, @SEGMENTID = @SEGMENTID, @SEGMENTSOURCECODE = @SEGMENTSOURCECODE;

      -- drop the global temp table

      set @SQL = 'drop table dbo.[' + @TEMPTABLENAME + ']';
      exec (@SQL);

      fetch next from SEGMENTCURSOR into @SEGMENTID, @SEGMENTSOURCECODE, @RECORDSOURCEID;
    end

    close SEGMENTCURSOR;
    deallocate SEGMENTCURSOR;

    -- rebuild all the indexes on the mailing activated data table to clean up fragmentation

    exec dbo.[USP_MKTCOMMON_REBUILDINDEX] @DATATABLENAME, null, 100, 1;
  end try

  begin catch
    exec dbo.[USP_RAISE_ERROR];
    return 1;
  end catch

  return 0;