USP_MKTSEGMENTATIONACTIVATE_UPDATEASKLADDERS

create procedure dbo.[USP_MKTSEGMENTATIONACTIVATE_UPDATEASKLADDERS]
(
  @SEGMENTATIONID uniqueidentifier
)
as
  set nocount on;

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

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

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

        --  1) all the mailing's segments

        --  2) all the mailing's test segments

        --  3) all the mailing's ask ladder overrides

        --  4) all the mailing's ask ladder overrides selections

        --  5) all the base ask ladders used in the mailing

        --Any deletes of segments/tests or ask ladder overrides will cause the record count cache to 

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

        if @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([DATECHANGED]) from dbo.[MKTSEGMENTATIONASKLADDEROVERRIDE] where [SEGMENTATIONID] = @SEGMENTATIONID) or
           @ENDEDONDATE < (select max([IDSETREGISTER].[DATECHANGED]) from dbo.[MKTSEGMENTATIONASKLADDEROVERRIDE] inner join dbo.[IDSETREGISTER] on [IDSETREGISTER].[ID] = [MKTSEGMENTATIONASKLADDEROVERRIDE].[IDSETREGISTERID] where [MKTSEGMENTATIONASKLADDEROVERRIDE].[SEGMENTATIONID] = @SEGMENTATIONID) or
           @ENDEDONDATE < (select max([DATECHANGED]) from dbo.[MKTASKLADDER] where [ID] in (
                            select [ASKLADDERID] from dbo.[MKTSEGMENTATIONSEGMENT] where [SEGMENTATIONID] = @SEGMENTATIONID and [ASKLADDERID] is not null
                            union
                            select [MKTSEGMENTATIONTESTSEGMENT].[ASKLADDERID] from dbo.[MKTSEGMENTATIONTESTSEGMENT] inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[SEGMENTID] where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID and [MKTSEGMENTATIONTESTSEGMENT].[ASKLADDERID] is not null
                            union
                            select [ASKLADDERID] from dbo.[MKTSEGMENTATIONASKLADDEROVERRIDE] where [SEGMENTATIONID] = @SEGMENTATIONID and [ASKLADDERID] is not null
                          ))
          begin
            set @DATATABLE = dbo.[UFN_MKTSEGMENTATIONACTIVATE_MAKETABLENAME](@SEGMENTATIONID);

            if exists(select * from INFORMATION_SCHEMA.TABLES where [TABLE_SCHEMA] = 'dbo' and [TABLE_NAME] = @DATATABLE)
              begin
                --Make sure the data table does not contain any previous ask ladder info...

                set @SQL = 'update dbo.[' + @DATATABLE + '] set' + char(13) +
                           '  [ASKLADDERID] = null,' + char(13) +
                           '  [ENTRYAMOUNT] = null' + char(13) +
                           'where [ASKLADDERID] is not null';
                exec (@SQL);

                --Check if the mailing is using ask ladders...

                if dbo.[UFN_MKTSEGMENTATION_INCLUDEASKLADDERS](@SEGMENTATIONID) = 1
                  begin
                    --Update the ask ladder info in the data table...

                    exec dbo.[USP_MKTSEGMENTATIONACTIVATE_SETASKLADDERINFO] @SEGMENTATIONID;
                  end
              end
          end
      end
  end try

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

  return 0;