USP_MKTSEGMENTATIONTESTSEGMENT_DELETE

CREATE procedure dbo.[USP_MKTSEGMENTATIONTESTSEGMENT_DELETE]
(
  @ID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier
)
as
  set nocount on;

  declare @ACTIVE bit;
  declare @SEGMENTATIONID uniqueidentifier;
  declare @SEGMENTATIONPACKAGEID uniqueidentifier;
  declare @PACKAGEID uniqueidentifier;
  declare @PARENTSEGMENTID uniqueidentifier;
  declare @RECORDSOURCEID uniqueidentifier;
  declare @SEQUENCE int;
  declare @DATATABLE nvarchar(128);
  declare @SQL nvarchar(max);

  begin try
    /* Grab some info so we can remove the package and reorder the rest of the test segments after the delete */
    select 
      @SEGMENTATIONID = [SEG].[SEGMENTATIONID], 
      @SEGMENTATIONPACKAGEID = (select [PAK].[ID] from dbo.[MKTSEGMENTATIONPACKAGE] as [PAK] where [PAK].[SEGMENTATIONID] = [SEG].[SEGMENTATIONID] and [PAK].[PACKAGEID] = [TESTSEG].[PACKAGEID]),
      @PACKAGEID = [TESTSEG].[PACKAGEID],
      @PARENTSEGMENTID = [SEG].[ID],
      @RECORDSOURCEID = [MKTSEGMENT].[QUERYVIEWCATALOGID],
      @SEQUENCE = [TESTSEG].[SEQUENCE]
    from dbo.[MKTSEGMENTATIONTESTSEGMENT] as [TESTSEG]
    inner join dbo.[MKTSEGMENTATIONSEGMENT] as [SEG] on [SEG].[ID] = [TESTSEG].[SEGMENTID]
    inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [SEG].[SEGMENTID]
    where [TESTSEG].[ID] = @ID;

    select @ACTIVE = [ACTIVE] from dbo.[MKTSEGMENTATION] where [ID] = @SEGMENTATIONID;

    if @ACTIVE = 0
      --Check if the mailing is currently being activated...

      exec dbo.[USP_MKTSEGMENTATION_CHECKACTIVATION] @SEGMENTATIONID;

    /* Delete segmentation test segment rows from the MKTSOURCECODEPART table. */
    declare @MKTSOURCECODEPARTID uniqueidentifier;
    declare MKTSOURCECODEPARTCURSOR cursor local fast_forward for
      select [ID]
      from dbo.[MKTSOURCECODEPART]
      where [SEGMENTATIONTESTSEGMENTID] = @ID;

    open MKTSOURCECODEPARTCURSOR;
    fetch next from MKTSOURCECODEPARTCURSOR into @MKTSOURCECODEPARTID;

    while (@@FETCH_STATUS = 0)
    begin
      exec dbo.[USP_MKTSOURCECODEPART_DELETEBYID_WITHCHANGEAGENTID] @MKTSOURCECODEPARTID, @CHANGEAGENTID;
      fetch next from MKTSOURCECODEPARTCURSOR into @MKTSOURCECODEPARTID;
    end;

    close MKTSOURCECODEPARTCURSOR;
    deallocate MKTSOURCECODEPARTCURSOR;

    /* delete the mailing data and source analysis rule data */
    if @ACTIVE = 0
      begin
        set @DATATABLE = dbo.[UFN_MKTSEGMENTATIONACTIVATE_MAKETABLENAME](@SEGMENTATIONID);

        if exists(select top 1 1 from [INFORMATION_SCHEMA].[TABLES] where [TABLE_SCHEMA] = 'dbo' and [TABLE_NAME] = @DATATABLE)
          begin
            set @SQL = 'update dbo.[' + @DATATABLE + '] set [TESTSEGMENTID] = null where [SEGMENTID] = @SEGMENTID and [TESTSEGMENTID] = @ID;';

            exec sp_executesql @SQL, N'@SEGMENTID uniqueidentifier, @ID uniqueidentifier', @SEGMENTID = @PARENTSEGMENTID, @ID = @ID;
          end

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

        if exists(select top 1 1 from [INFORMATION_SCHEMA].[TABLES] where [TABLE_SCHEMA] = 'dbo' and [TABLE_NAME] = @DATATABLE)
          begin
            set @SQL = 'update dbo.[' + @DATATABLE + '] set [TESTSEGMENTID] = null where [SEGMENTID] = @SEGMENTID and [TESTSEGMENTID] = @ID;';

            exec sp_executesql @SQL, N'@SEGMENTID uniqueidentifier, @ID uniqueidentifier', @SEGMENTID = @PARENTSEGMENTID, @ID = @ID;
          end
      end

    /* Delete test segment from segmented house file import */
    if dbo.[UFN_MKTCONSTITUENTFILEIMPORT_IMPORTTABLEEXISTS](@SEGMENTATIONID) = 1
      begin
        set @DATATABLE = dbo.[UFN_MKTCONSTITUENTFILEIMPORT_BUILDTABLENAME](@SEGMENTATIONID);
        set @SQL = 'delete from dbo.[' + @DATATABLE + '] where [SEGMENTATIONTESTSEGMENTID] = @ID;';
        exec sp_executesql @SQL, N'@ID uniqueidentifier', @ID = @ID;
      end

    -- clear the cached information for the parent segment to force mailing data recalculation

    exec dbo.[USP_MKTSEGMENTATIONSEGMENT_CLEARCACHE] @PARENTSEGMENTID, 0, 1;

    /* Delete the test segment */
    exec dbo.[USP_MKTSEGMENTATIONTESTSEGMENT_DELETEBYID_WITHCHANGEAGENTID] @ID, @CHANGEAGENTID;

    /* Remove the package, only if is not being used by any other segments or test segments */
    if not exists(select [ID] from dbo.[MKTSEGMENTATIONSEGMENT] where [SEGMENTATIONID] = @SEGMENTATIONID and [PACKAGEID] = @PACKAGEID) and 
       not exists(select [MKTSEGMENTATIONTESTSEGMENT].[ID] from dbo.[MKTSEGMENTATIONTESTSEGMENT] inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[SEGMENTID] where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID and [MKTSEGMENTATIONTESTSEGMENT].[PACKAGEID] = @PACKAGEID)
      exec dbo.[USP_MKTSEGMENTATIONPACKAGE_DELETE] @SEGMENTATIONPACKAGEID, @CHANGEAGENTID;

    /* Reorder the rest of the test segments in this parent segment */
    update dbo.[MKTSEGMENTATIONTESTSEGMENT]
    set [SEQUENCE] = ([SEQUENCE] - 1), [CHANGEDBYID]=@CHANGEAGENTID
    where [SEGMENTID] = @PARENTSEGMENTID and [SEQUENCE] > @SEQUENCE;
  end try

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

  return 0;