USP_MKTSEGMENTATIONACTIVATE_UPDATESOURCECODES

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

  declare @ACTIVE bit;
  declare @SOURCECODEID uniqueidentifier;
  declare @SOURCECODEISHISTORICAL bit;
  declare @ENDEDONDATE datetime;
  declare @DATATABLE nvarchar(128);
  declare @SQL nvarchar(max);

  begin try
    select top 1
      @ACTIVE = [MKTSEGMENTATION].[ACTIVE],
      @SOURCECODEID = [MKTSEGMENTATION].[SOURCECODEID],
      @SOURCECODEISHISTORICAL = isnull([MKTSOURCECODE].[ISHISTORICAL], 0),
      @ENDEDONDATE = [BUSINESSPROCESSSTATUS].[ENDEDON]
    from dbo.[MKTSEGMENTATION]
    left outer join dbo.[MKTSOURCECODE] on [MKTSOURCECODE].[ID] = [MKTSEGMENTATION].[SOURCECODEID]
    left outer join dbo.[MKTSEGMENTATIONSEGMENTCALCULATEPROCESS] on [MKTSEGMENTATIONSEGMENTCALCULATEPROCESS].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
    left outer join dbo.[MKTSEGMENTATIONSEGMENTCALCULATEPROCESSSTATUS] on [MKTSEGMENTATIONSEGMENTCALCULATEPROCESSSTATUS].[PARAMETERSETID] = [MKTSEGMENTATIONSEGMENTCALCULATEPROCESS].[ID]
    left outer join dbo.[BUSINESSPROCESSSTATUS] on [BUSINESSPROCESSSTATUS].[ID] = [MKTSEGMENTATIONSEGMENTCALCULATEPROCESSSTATUS].[ID]
    where [MKTSEGMENTATION].[ID] = @SEGMENTATIONID
    and isnull([BUSINESSPROCESSSTATUS].[STATUSCODE], 0) = 0
    order by [BUSINESSPROCESSSTATUS].[ENDEDON] desc;

    --Only update the sourcecodes for non-activated mailings...

    if @ACTIVE = 0 and @ENDEDONDATE is not null and @SOURCECODEISHISTORICAL = 0
      begin
        --See if we need to update sourcecodes by doing a basic compare of the date changed for:

        --  1) the mailing

        --  2) all the mailing's segments

        --  3) all the mailing's test segments

        --  4) all base segments in the mailing

        --  5) all packages in the mailing

        --  6) all lists in the mailing

        --  7) all sourcecode parts used by the mailing

        --  8) the recordsources used by the mailing

        --Any deletes of segments/tests will cause the record count cache to automatically

        --be invalidated, so we don't need to worry about that here.

        if @ENDEDONDATE < (select [DATECHANGED] from dbo.[MKTSEGMENTATION] where [ID] = @SEGMENTATIONID) or 
           @ENDEDONDATE < (select max([DATECHANGED]) from dbo.[MKTSEGMENTATIONSEGMENT] where [SEGMENTATIONID] = @SEGMENTATIONID) or
           @ENDEDONDATE < (select max([MKTSEGMENTATIONTESTSEGMENT].[DATECHANGED]) from dbo.[MKTSEGMENTATIONTESTSEGMENT] inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[SEGMENTID] where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID) or
           @ENDEDONDATE < (select max([MKTSEGMENT].[DATECHANGED]) from dbo.[MKTSEGMENTATIONSEGMENT] inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID] where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID) or
           @ENDEDONDATE < (select max([MKTPACKAGE].[DATECHANGED]) from dbo.[MKTSEGMENTATIONPACKAGE] inner join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = [MKTSEGMENTATIONPACKAGE].[PACKAGEID] where [MKTSEGMENTATIONPACKAGE].[SEGMENTATIONID] = @SEGMENTATIONID) or
           @ENDEDONDATE < (select max([MKTLIST].[DATECHANGED]) from dbo.[MKTSEGMENTATIONSEGMENT] inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID] inner join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENT].[CURRENTSEGMENTLISTID] inner join dbo.[MKTLIST] on [MKTLIST].[ID] = [MKTSEGMENTLIST].[LISTID] where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID) or
           @ENDEDONDATE < (select max([MKTSOURCECODEITEM].[DATECHANGED]) from dbo.[MKTSOURCECODEITEM] inner join dbo.[MKTSOURCECODEPARTDEFINITION] on [MKTSOURCECODEPARTDEFINITION].[ID] = [MKTSOURCECODEITEM].[MKTSOURCECODEPARTDEFINITIONID] where [MKTSOURCECODEITEM].[SOURCECODEID] = @SOURCECODEID) or
           @ENDEDONDATE < (select max([MKTRECORDSOURCE].[DATECHANGED]) from dbo.[UFN_MKTSEGMENTATION_GETDISTINCTRECORDSOURCES](@SEGMENTATIONID) as [DRS] inner join dbo.[MKTRECORDSOURCE] on [MKTRECORDSOURCE].[ID] = [DRS].[QUERYVIEWCATALOGID])
          begin
            set @DATATABLE = dbo.[UFN_MKTSEGMENTATIONACTIVATE_MAKETABLENAME](@SEGMENTATIONID);

            if exists(select * from INFORMATION_SCHEMA.TABLES where [TABLE_SCHEMA] = 'dbo' and [TABLE_NAME] = @DATATABLE)
              begin
                if @SOURCECODEID is null
                  begin
                    --Make sure the data table does not contain any previous sourcecodes since the mailing is not using sourcecodes...

                    set @SQL = 'update dbo.[' + @DATATABLE + '] set' + char(13) +
                               '  [SOURCECODE] = ''''' + char(13) +
                               'where [SOURCECODE] is null' + char(13) +
                               'or [SOURCECODE] <> ''''';
                    exec (@SQL);
                  end
                else
                  begin
                    --Update the data table to contain the current sourcecodes (if they changed)...

                    set @SQL = 'declare @SC table (' + char(13) +
                               '  [DONORQUERYVIEWCATALOGID] uniqueidentifier not null,' + char(13) +
                               '  [SEGMENTID] uniqueidentifier not null,' + char(13) +
                               '  [TESTSEGMENTID] uniqueidentifier,' + char(13) +
                               '  [OLDSOURCECODE] nvarchar(50) not null,' + char(13) +
                               '  [NEWSOURCECODE] nvarchar(50) not null' + char(13) +
                               ');' + char(13) +
                               char(13) +
                               '--Grab all distinct sourcecodes from the datatable...' + char(13) +

                               'insert into @SC ([DONORQUERYVIEWCATALOGID], [SEGMENTID], [TESTSEGMENTID], [OLDSOURCECODE], [NEWSOURCECODE])' + char(13) +
                               '  select distinct' + char(13) +
                               '    [DONORQUERYVIEWCATALOGID],' + char(13) +
                               '    [SEGMENTID],' + char(13) +
                               '    [TESTSEGMENTID],' + char(13) +
                               '    isnull([SOURCECODE], ''''),' + char(13) +
                               '    ''''' + char(13) +
                               '  from dbo.[' + @DATATABLE + '];' + char(13) +
                               char(13) +
                               '--Build new sourcecodes to see if any of the old sourcecodes need updated.  This statement is separate' + char(13) +

                               '--from the above insert to avoid the performance penalty of having to calculate sourcecodes for all' + char(13) +

                               '--records in the datatable and then perform the distinct operation.' + char(13) +

                               'update @SC set' + char(13) +
                               '  [NEWSOURCECODE] = dbo.[UFN_MKTSOURCECODE_BUILDCODE](isnull([TESTSEGMENTID], [SEGMENTID]), [DONORQUERYVIEWCATALOGID], null);' + char(13) +
                               char(13) +
                               '--Update any sourcecodes that are out-of-date...' + char(13) +

                               'if exists(select * from @SC where [OLDSOURCECODE] <> [NEWSOURCECODE])' + char(13) +
                               '  update dbo.[' + @DATATABLE + '] set' + char(13) +
                               '    [SOURCECODE] = [SC].[NEWSOURCECODE]' + char(13) +
                               '  from dbo.[' + @DATATABLE + '] as [DONORS]' + char(13) +
                               '  inner join @SC as [SC] on [SC].[DONORQUERYVIEWCATALOGID] = [DONORS].[DONORQUERYVIEWCATALOGID] and [SC].[SEGMENTID] = [DONORS].[SEGMENTID] and (([SC].[TESTSEGMENTID] is null and [DONORS].[TESTSEGMENTID] is null) or ([SC].[TESTSEGMENTID] = [DONORS].[TESTSEGMENTID]));';
                    exec (@SQL);
                  end
              end
          end
      end
  end try

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

  return 0;