USP_MKTSOURCEANALYSISRULE_CACHEDATA

CREATE procedure dbo.[USP_MKTSOURCEANALYSISRULE_CACHEDATA]
(
  @SEGMENTATIONID uniqueidentifier,
  @ONLYCACHEDELTA bit = 1,
  @INSERTSQL nvarchar(max),
  @JOINSQL nvarchar(max),
  @SELECTSQL nvarchar(max),
  @WITHSQL nvarchar(max),
  @RECORDSOURCEID uniqueidentifier
)
as
  set nocount on;

  declare @SOURCEANALYSISRULEDATATABLE nvarchar(128);
  declare @DATATABLE nvarchar(128);
  declare @SQL nvarchar(max);

  begin try
    --Create a temp table of segments for the record source...

    create table #SEGMENTS ([SEGMENTID] uniqueidentifier not null, [TESTSEGMENTID] uniqueidentifier);

    --Get all the segments and test segments for the record source...

    insert into #SEGMENTS ([SEGMENTID], [TESTSEGMENTID])
      select [MKTSEGMENTATIONSEGMENT].[ID], null
      from dbo.[MKTSEGMENTATIONSEGMENT]
      inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
      where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID
      and [MKTSEGMENTATIONSEGMENT].[EXCLUDE] = 0
      and [MKTSEGMENT].[SEGMENTTYPECODE] not in (2, 6, 7, 8) -- skip list segments, public media segments

      and [MKTSEGMENT].[QUERYVIEWCATALOGID] = @RECORDSOURCEID
      union all
      select [MKTSEGMENTATIONSEGMENT].[ID], [MKTSEGMENTATIONTESTSEGMENT].[ID]
      from dbo.[MKTSEGMENTATIONSEGMENT]
      inner join dbo.[MKTSEGMENTATIONTESTSEGMENT] on [MKTSEGMENTATIONTESTSEGMENT].[SEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID]
      inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
      where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID
      and [MKTSEGMENTATIONSEGMENT].[EXCLUDE] = 0
      and [MKTSEGMENT].[SEGMENTTYPECODE] not in (2, 6, 7, 8) -- skip list segments, public media segments

      and [MKTSEGMENT].[QUERYVIEWCATALOGID] = @RECORDSOURCEID;

    --Only insert source analysis data if we have house file or consolidated list segments...

    if exists(select top 1 1 from #SEGMENTS)
      begin
        if isnull(@INSERTSQL, '') = ''
          raiserror('The @INSERTSQL parameter is required.', 13, 1);
        if isnull(@SELECTSQL, '') = ''
          raiserror('The @SELECTSQL parameter is required.', 13, 1);

        set @DATATABLE = dbo.[UFN_MKTSEGMENTATIONACTIVATE_MAKETABLENAME](@SEGMENTATIONID);
        set @SOURCEANALYSISRULEDATATABLE = dbo.[UFN_MKTSOURCEANALYSISRULE_MAKETABLENAME](@RECORDSOURCEID);

        /* Create or update the source analysis data table */
        exec dbo.[USP_MKTSOURCEANALYSISRULE_CREATEDATATABLE] @RECORDSOURCEID;


        /* Execute the SQL here, only once per record source in order to limit the number of times we have to hit the linked server constituent view */
        /* now that SAR data can be cached before activation:
           - only insert into the table those donors who are new since the data was last cached 
             (if SAR data is being re-cached, the data from the table will have been truncated before this)
           - delete from the table those donors who are no longer in the mailing data 
        */
        set @SQL = 'create table #SEGMENTDONORS (' + char(13) +
                   '  [DONORID] ' + dbo.[UFN_MKTRECORDSOURCE_GETDONORIDDATATYPE](@RECORDSOURCEID) + ' not null,' + char(13) +
                   '  [SEGMENTID] uniqueidentifier not null,' + char(13) +
                   '  [TESTSEGMENTID] uniqueidentifier,' + char(13) +
                   '  [FINDERNUMBER] bigint not null' + char(13) +
                   ');' + char(13) +
                   char(13) +
                   'create clustered index [IX_SEGMENTDONORS_DONORID] on #SEGMENTDONORS ([DONORID]);' + char(13) +
                   char(13) +
                   '--Get all the donor IDs for all the segments and test segments...' + char(13) +

                   'insert into #SEGMENTDONORS ([DONORID], [SEGMENTID], [TESTSEGMENTID], [FINDERNUMBER])' + char(13) +
                   '  select distinct' + char(13) +
                   '    [DONORS].[DONORID],' + char(13) +
                   '    [SEGMENTS].[SEGMENTID],' + char(13) +
                   '    [SEGMENTS].[TESTSEGMENTID],' + char(13) +
                   '    [DONORS].[FINDERNUMBER]' + char(13) +
                   '  from #SEGMENTS as [SEGMENTS]' + char(13) +
                   '  inner join dbo.[' + @DATATABLE + '] as [DONORS] on [DONORS].[SEGMENTID] = [SEGMENTS].[SEGMENTID] and (([DONORS].[TESTSEGMENTID] is null and [SEGMENTS].[TESTSEGMENTID] is null) or [DONORS].[TESTSEGMENTID] = [SEGMENTS].[TESTSEGMENTID])' + char(13) +
                   '  where [DONORS].[DONORQUERYVIEWCATALOGID] = @RECORDSOURCEID;' + char(13) + 
                   char(13);

        if @ONLYCACHEDELTA = 0
          set @SQL += '--Clear out any pre-existing SAR data for this mailing...' + char(13) +

                      'delete from dbo.[' + @SOURCEANALYSISRULEDATATABLE + '] where [MAILINGID] = @SEGMENTATIONID;' + char(13) +
                      char(13);
        else
          set @SQL += '--Delete from the table those donors who are no longer in the mailing data...' + char(13) +

                      'delete from dbo.[' + @SOURCEANALYSISRULEDATATABLE + ']' + char(13) +
                      'from dbo.[' + @SOURCEANALYSISRULEDATATABLE + '] as [SAR]' + char(13) +
                      'where [SAR].[MAILINGID] = @SEGMENTATIONID' + char(13) +
                      'and not exists(select * from #SEGMENTDONORS as [DONORS] where [DONORS].[DONORID] = [SAR].[DONORID] and [DONORS].[FINDERNUMBER] = [SAR].[FINDERNUMBER]);' + char(13) +
                      char(13) +
                      '--For those donors who are still in the mailing, make sure they have the right SEGMENTID and TESTSEGMENTID...' + char(13) +

                      'update dbo.[' + @SOURCEANALYSISRULEDATATABLE + '] set' + char(13) +
                      '  [SEGMENTID] = [DONORS].[SEGMENTID],' + char(13) +
                      '  [TESTSEGMENTID] = [DONORS].[TESTSEGMENTID]' + char(13) +
                      'from dbo.[' + @SOURCEANALYSISRULEDATATABLE + '] as [SAR]' + char(13) +
                      'inner join #SEGMENTDONORS as [DONORS] on [DONORS].[DONORID] = [SAR].[DONORID] and [DONORS].[FINDERNUMBER] = [SAR].[FINDERNUMBER]' + char(13) +
                      'where [SAR].[MAILINGID] = @SEGMENTATIONID;' + char(13) +
                      char(13);

        set @SQL += '--Insert into the source analysis table the data for each donor who isn''t already there...' + char(13) +

                    'with [DONORS] ([DONORID], [SEGMENTID], [TESTSEGMENTID], [FINDERNUMBER]) as' + char(13) +
                    '(' + char(13) +
                    '  select' + char(13) +
                    '    [DONORID],' + char(13) +
                    '    [SEGMENTID],' + char(13) +
                    '    [TESTSEGMENTID],' + char(13) +
                    '    [FINDERNUMBER]' + char(13) +
                    '  from #SEGMENTDONORS' + char(13) +
                    ')' + isnull(@WITHSQL, '') +
                    char(13) +
                    @INSERTSQL +
                    @SELECTSQL +
                    '  from [DONORS]' + char(13);

        if @ONLYCACHEDELTA = 0
          set @SQL += isnull(@JOINSQL, '');
        else
          set @SQL += '  left outer join dbo.[' + @SOURCEANALYSISRULEDATATABLE + '] as [EXISTING] on ([EXISTING].[MAILINGID] = @SEGMENTATIONID and [EXISTING].[DONORID] = [DONORS].[DONORID] and [EXISTING].[FINDERNUMBER] = [DONORS].[FINDERNUMBER])' + char(13) +
                      isnull(@JOINSQL, '') +
                      '  --Only insert into the table those donors who are new since the data was last cached...' + char(13) +

                      '  where [EXISTING].[DONORID] is null;';

        set @SQL += char(13) + char(13) +
                    'drop table #SEGMENTDONORS;'

        exec sp_executesql @SQL, N'@SEGMENTATIONID uniqueidentifier, @RECORDSOURCEID uniqueidentifier', @SEGMENTATIONID = @SEGMENTATIONID, @RECORDSOURCEID = @RECORDSOURCEID;
      end

    --Drop the segment temp table...

    drop table #SEGMENTS;
  end try

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

  return 0;