USP_MKTSOURCECODEMAP_POPULATE

CREATE procedure dbo.[USP_MKTSOURCECODEMAP_POPULATE]
(
  @SEGMENTATIONID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null,
  @SEGMENTATIONSEGMENTID uniqueidentifier = null
)
as
  set nocount on;

  declare @SOURCECODEID uniqueidentifier;
  declare @SOURCECODEISHISTORICAL bit;
  declare @HASCONSOLIDATEDLISTSEGMENTS bit;
  declare @SQL nvarchar(max);
  declare @CURRENTDATE datetime;

  begin try
    if @CHANGEAGENTID is null  
      exec dbo.[USP_CHANGEAGENT_GETORCREATECHANGEAGENT] @CHANGEAGENTID output;

    set @CURRENTDATE = getdate();

    select
      @SOURCECODEID = [MKTSEGMENTATION].[SOURCECODEID],
      @SOURCECODEISHISTORICAL = isnull([MKTSOURCECODE].[ISHISTORICAL], 0)
    from dbo.[MKTSEGMENTATION]
    left outer join dbo.[MKTSOURCECODE] on [MKTSOURCECODE].[ID] = [MKTSEGMENTATION].[SOURCECODEID]
    where [MKTSEGMENTATION].[ID] = @SEGMENTATIONID;

    if @SOURCECODEID is not null and @SOURCECODEISHISTORICAL = 0
      begin
        if @SEGMENTATIONSEGMENTID is not null
          begin
            declare @MKTSOURCECODEMAPID uniqueidentifier;

            select
              @MKTSOURCECODEMAPID = [MKTSOURCECODEMAP].[ID],
              @HASCONSOLIDATEDLISTSEGMENTS = dbo.[UFN_MKTSEGMENT_ISCONSOLIDATEDVIEWSEGMENT]([MKTSEGMENTATIONSEGMENT].[SEGMENTID])
            from dbo.[MKTSOURCECODEMAP]
            inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [MKTSOURCECODEMAP].[SEGMENTATIONSEGMENTID]
            where [MKTSOURCECODEMAP].[SEGMENTATIONID] = @SEGMENTATIONID
            and [MKTSOURCECODEMAP].[SEGMENTATIONSEGMENTID] = @SEGMENTATIONSEGMENTID;

            exec dbo.[USP_MKTSOURCECODEMAP_DELETEBYID_WITHCHANGEAGENTID] @MKTSOURCECODEMAPID, @CHANGEAGENTID;
          end
        else
          --Check if the mailing contains consolidated list segments so that we can maybe shortcut some processing...

          set @HASCONSOLIDATEDLISTSEGMENTS = dbo.[UFN_MKTSEGMENTATION_HASCONSOLIDATEDVIEWSEGMENTS](@SEGMENTATIONID);

        /* Insert the source code mapping information */

        set @SQL = 'insert into dbo.[MKTSOURCECODEMAP] (' + char(13) +
                   '  [ID],' + char(13) +
                   '  [SEGMENTATIONID],' + char(13) +
                   '  [SEGMENTATIONSEGMENTID],' + char(13) +
                   '  [SEGMENTATIONTESTSEGMENTID],' + char(13) +
                   '  [LISTID],' + char(13) +
                   '  [DONORQUERYVIEWCATALOGID],' + char(13) +
                   '  [SOURCECODEID],' + char(13) +
                   '  [SOURCECODE],' + char(13) +
                   '  [ADDEDBYID],' + char(13) +
                   '  [CHANGEDBYID],' + char(13) +
                   '  [DATEADDED],' + char(13) +
                   '  [DATECHANGED]' + char(13) +
                   ')' + char(13) +
                   'select' + char(13) +
                   '  newid() as [ID],' + char(13) +
                   '  @SEGMENTATIONID as [SEGMENTATIONID],' + char(13) +
                   '  isnull([DONORS].[SEGMENTID], [SEGMENTS].[SEGMENTID]) as [SEGMENTID],' + char(13) +
                   '  isnull([DONORS].[TESTSEGMENTID], [SEGMENTS].[TESTSEGMENTID]) as [TESTSEGMENTID],' + char(13) +
                   '  ' + (case when @HASCONSOLIDATEDLISTSEGMENTS = 1 then 'isnull([LISTSEGMENT].[LISTID], [CONSOLSEGMENT].[LISTID]) as [LISTID]' else '[LISTSEGMENT].[LISTID]' end) + ',' + char(13) +
                   '  [MKTSEGMENT].[QUERYVIEWCATALOGID] as [RECORDSOURCEID],' + char(13) +
                   '  [MKTSEGMENTATION].[SOURCECODEID],' + char(13) +
                   '  (case when [DONORS].[SEGMENTID] is not null then' + char(13) +
                   '     --This will give us the exact source code even for consolidated list segments...' + char(13) +

                   '     [DONORS].[SOURCECODE]' + char(13) +
                   '   else' + char(13) +
                   '     --This is only for vendor managed lists which do not get added to the data table...' + char(13) +

                   '     --Do not call this for consolidated list segments because it will return asterisks in the source code for the list part.' + char(13) +

                   '     dbo.[UFN_MKTSOURCECODE_BUILDCODE](isnull([SEGMENTS].[TESTSEGMENTID], [SEGMENTS].[SEGMENTID]), default, default)' + char(13) +
                   '   end) as [SOURCECODE],' + char(13) +
                   '  @CHANGEAGENTID as [ADDEDBYID],' + char(13) +
                   '  @CHANGEAGENTID as [CHANGEDBYID],' + char(13) +
                   '  @CURRENTDATE as [DATEADDED],' + char(13) +
                   '  @CURRENTDATE as [DATECHANGED]' + char(13) +
                   'from dbo.[UFN_MKTSEGMENTATIONACTIVATE_GETSEGMENTS](@SEGMENTATIONID) as [SEGMENTS]' + char(13) +
                   'inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [SEGMENTS].[SEGMENTID]' + char(13) +
                   'inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]' + char(13) +
                   'inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID]' + char(13) +
                   'left join dbo.[' + dbo.[UFN_MKTSEGMENTATIONACTIVATE_MAKETABLENAME](@SEGMENTATIONID) + '] as [DONORS] on [DONORS].[SEGMENTID] = [SEGMENTS].[SEGMENTID] and (([DONORS].[TESTSEGMENTID] is null and [SEGMENTS].[TESTSEGMENTID] is null) or ([DONORS].[TESTSEGMENTID] = [SEGMENTS].[TESTSEGMENTID]))' + char(13) +
                   'left join dbo.[MKTSEGMENTATIONSEGMENTLIST] on [MKTSEGMENTATIONSEGMENTLIST].[ID] = [MKTSEGMENTATIONSEGMENT].[ID]' + char(13) +
                   'left join dbo.[MKTSEGMENTLIST] as [LISTSEGMENT] on [MKTSEGMENT].[SEGMENTTYPECODE] = 2 and [LISTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENTLIST].[SEGMENTLISTID]' + char(13) +
                   (case when @HASCONSOLIDATEDLISTSEGMENTS = 1 then 'left join dbo.[MKTSEGMENTLIST] as [CONSOLSEGMENT] on [MKTSEGMENT].[SEGMENTTYPECODE] = 1 and [CONSOLSEGMENT].[QUERYVIEWCATALOGID] = [DONORS].[DONORQUERYVIEWCATALOGID]' + char(13) else '' end) +
                   'where [MKTSEGMENTATIONSEGMENT].[EXCLUDE] = 0' + char(13);

        if @SEGMENTATIONSEGMENTID is not null
          set @SQL += 'and [SEGMENTS].[SEGMENTID] = @SEGMENTATIONSEGMENTID' + char(13);

        set @SQL += 'group by [SEGMENTS].[SEGMENTID], [SEGMENTS].[TESTSEGMENTID], [MKTSEGMENT].[QUERYVIEWCATALOGID], [DONORS].[SEGMENTID], [DONORS].[TESTSEGMENTID], [LISTSEGMENT].[LISTID], ' + (case when @HASCONSOLIDATEDLISTSEGMENTS = 1 then '[CONSOLSEGMENT].[LISTID], ' else '' end) + '[MKTSEGMENTATION].[SOURCECODEID], [DONORS].[SOURCECODE];';

        if @SEGMENTATIONSEGMENTID is not null
          exec sp_executesql @SQL, N'@SEGMENTATIONID uniqueidentifier, @CHANGEAGENTID uniqueidentifier, @CURRENTDATE datetime, @SEGMENTATIONSEGMENTID uniqueidentifier', @SEGMENTATIONID = @SEGMENTATIONID, @CHANGEAGENTID = @CHANGEAGENTID, @CURRENTDATE = @CURRENTDATE, @SEGMENTATIONSEGMENTID = @SEGMENTATIONSEGMENTID;
        else
          exec sp_executesql @SQL, N'@SEGMENTATIONID uniqueidentifier, @CHANGEAGENTID uniqueidentifier, @CURRENTDATE datetime', @SEGMENTATIONID = @SEGMENTATIONID, @CHANGEAGENTID = @CHANGEAGENTID, @CURRENTDATE = @CURRENTDATE;

        /* Insert the source code part map information */

        insert into dbo.[MKTSOURCECODEMAPPART] (
          [ID],
          [MKTSOURCECODEMAPID],
          [MKTSOURCECODEPARTDEFINITIONID],
          [CODE],
          [MKTSOURCECODEPARTDEFINITIONVALUESID],
          [ADDEDBYID],
          [CHANGEDBYID],
          [DATEADDED],
          [DATECHANGED]
        )
        select
          newid() as [ID],
          [MKTSOURCECODEMAP].[ID] as [MKTSOURCECODEMAPID],
          [MKTSOURCECODEITEM].[MKTSOURCECODEPARTDEFINITIONID],
          (case when [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE] = 6 then
             --For list parts only...

             isnull([MKTSOURCECODEPART].[CODE], [MKTRECORDSOURCE].[CODE])
           else
             --For all other parts...

             dbo.[UFN_MKTSOURCECODE_BUILDCODE](isnull([MKTSOURCECODEMAP].[SEGMENTATIONTESTSEGMENTID], [MKTSOURCECODEMAP].[SEGMENTATIONSEGMENTID]), default, [MKTSOURCECODEITEM].[ID])
           end) as [CODE],
          case [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE]
            when 0 then [MKTSEGMENTATION].[PARTDEFINITIONVALUESID]
            when 1 then isnull([MKTSEGMENTATIONTESTSEGMENT].[PARTDEFINITIONVALUESID], [MKTSEGMENTATIONSEGMENT].[PARTDEFINITIONVALUESID])
            when 2 then isnull(TP.[PARTDEFINITIONVALUESID], SP.[PARTDEFINITIONVALUESID])
            when 3 then isnull(TP.[CHANNELPARTDEFINITIONVALUESID], SP.[CHANNELPARTDEFINITIONVALUESID])
            when 5 then
                case 
                  when [MKTSEGMENTATIONTESTSEGMENT].[ID] is null then
                    case
                      when exists(select 1 from dbo.[MKTSOURCECODEPART] where [SEGMENTATIONID] = [MKTSEGMENTATION].[ID] and [SOURCECODEITEMID] = [MKTSOURCECODEITEM].[ID] and [SEGMENTATIONSEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID]) 
                        then (select [PARTDEFINITIONVALUESID] from dbo.[MKTSOURCECODEPART] where [SEGMENTATIONID] = [MKTSEGMENTATION].[ID] and [SOURCECODEITEMID] = [MKTSOURCECODEITEM].[ID] and [SEGMENTATIONSEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID])
                      else (select [PARTDEFINITIONVALUESID] from dbo.[MKTSOURCECODEPART] where [SEGMENTATIONID] = [MKTSEGMENTATION].[ID] and [SOURCECODEITEMID] = [MKTSOURCECODEITEM].[ID] and [SEGMENTATIONSEGMENTID] is null and [SEGMENTATIONTESTSEGMENTID] is null)
                    end
                  else
                    case
                      when exists(select 1 from dbo.[MKTSOURCECODEPART] where [SEGMENTATIONID] = [MKTSEGMENTATION].[ID] and [SOURCECODEITEMID] = [MKTSOURCECODEITEM].[ID] and [SEGMENTATIONTESTSEGMENTID] = [MKTSEGMENTATIONTESTSEGMENT].[ID]) 
                        then (select [PARTDEFINITIONVALUESID] from dbo.[MKTSOURCECODEPART] where [SEGMENTATIONID] = [MKTSEGMENTATION].[ID] and [SOURCECODEITEMID] = [MKTSOURCECODEITEM].[ID] and [SEGMENTATIONTESTSEGMENTID] = [MKTSEGMENTATIONTESTSEGMENT].[ID])
                      else (select [PARTDEFINITIONVALUESID] from dbo.[MKTSOURCECODEPART] where [SEGMENTATIONID] = [MKTSEGMENTATION].[ID] and [SOURCECODEITEMID] = [MKTSOURCECODEITEM].[ID] and [SEGMENTATIONSEGMENTID] is null and [SEGMENTATIONTESTSEGMENTID] is null)
                    end
                end
            when 6 then isnull([MKTSOURCECODEPART].[PARTDEFINITIONVALUESID], [MKTRECORDSOURCE].[PARTDEFINITIONVALUESID])
            when 7 then isnull([MKTSEGMENTATIONTESTSEGMENT].[PARTDEFINITIONVALUESID], [MKTSEGMENTATIONSEGMENT].[TESTPARTDEFINITIONVALUESID])
          end as [MKTSOURCECODEPARTDEFINITIONVALUESID],
          @CHANGEAGENTID as [ADDEDBYID],
          @CHANGEAGENTID as [CHANGEDBYID],
          @CURRENTDATE as [DATEADDED],
          @CURRENTDATE as [DATECHANGED]
        from dbo.[MKTSOURCECODEMAP]
        inner join dbo.[MKTSOURCECODEITEM] on [MKTSOURCECODEITEM].[SOURCECODEID] = [MKTSOURCECODEMAP].[SOURCECODEID]
        inner join dbo.[MKTSOURCECODEPARTDEFINITION] on [MKTSOURCECODEPARTDEFINITION].[ID] = [MKTSOURCECODEITEM].[MKTSOURCECODEPARTDEFINITIONID]
        inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSOURCECODEMAP].[SEGMENTATIONID]
        inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [MKTSOURCECODEMAP].[SEGMENTATIONSEGMENTID]
        left join dbo.[MKTSEGMENTATIONPACKAGE] SP on SP.[SEGMENTATIONID] = [MKTSEGMENTATION].[ID] and SP.[PACKAGEID] = [MKTSEGMENTATIONSEGMENT].[PACKAGEID]
        left join dbo.[MKTSEGMENTATIONTESTSEGMENT] on [MKTSEGMENTATIONTESTSEGMENT].[ID] = [MKTSOURCECODEMAP].[SEGMENTATIONTESTSEGMENTID]
        left join dbo.[MKTSEGMENTATIONPACKAGE] TP on TP.[SEGMENTATIONID] = [MKTSEGMENTATION].[ID] and TP.[PACKAGEID] = [MKTSEGMENTATIONTESTSEGMENT].[PACKAGEID]
        left join dbo.[MKTSOURCECODEPART] on [MKTSOURCECODEPART].[SEGMENTATIONID] = [MKTSOURCECODEMAP].[SEGMENTATIONID] and [MKTSOURCECODEPART].[SOURCECODEITEMID] = [MKTSOURCECODEITEM].[ID] and [MKTSOURCECODEPART].[LISTID] = [MKTSOURCECODEMAP].[LISTID]
        left join dbo.[MKTRECORDSOURCE] on [MKTRECORDSOURCE].[ID] = [MKTSOURCECODEMAP].[DONORQUERYVIEWCATALOGID]
        where [MKTSOURCECODEMAP].[SEGMENTATIONID] = @SEGMENTATIONID
        and (@SEGMENTATIONSEGMENTID is null or [MKTSOURCECODEMAP].[SEGMENTATIONSEGMENTID] = @SEGMENTATIONSEGMENTID);
      end
  end try

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

  return 0;