USP_MKTSEGMENTATIONEXCLUSION_ROLLBACK

CREATE procedure dbo.[USP_MKTSEGMENTATIONEXCLUSION_ROLLBACK]
(
  @SEGMENTATIONEXCLUSIONID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null
)
as
  set nocount on;

  declare @SEGMENTATIONID uniqueidentifier;
  declare @IDSETREGISTERID uniqueidentifier;
  declare @IDSETTABLENAME nvarchar(128);
  declare @IDSETFUNCTIONNAME nvarchar(128);
  declare @IDSETFUNCTIONNAMEEXISTS nvarchar(128);
  declare @RECORDSOURCEID uniqueidentifier;
  declare @SARTABLE nvarchar(128);
  declare @SAREXCLTABLE nvarchar(128);
  declare @DATATABLE nvarchar(128);
  declare @EXCLUSIONTABLE nvarchar(128);
  declare @COLUMNNAME nvarchar(255);
  declare @SQL nvarchar(max);
  declare @VALUESSQL nvarchar(max);

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

    select
      @SEGMENTATIONID = [SEGMENTATIONID]
    from dbo.[MKTSEGMENTATIONEXCLUSION]
    where [ID] = @SEGMENTATIONEXCLUSIONID;


    /**************************************/
    /* Delete any saved output selections */
    /**************************************/
    declare IDSETCURSOR cursor local fast_forward for
      select
        [IDSETREGISTER].[ID],
        [IDSETREGISTER].[DBOBJECTNAME]
      from dbo.[MKTSEGMENTATIONEXCLUSIONSELECTION]
      inner join dbo.[IDSETREGISTER] on [IDSETREGISTER].[ID] = [MKTSEGMENTATIONEXCLUSIONSELECTION].[IDSETREGISTERID]
      where [MKTSEGMENTATIONEXCLUSIONSELECTION].[SEGMENTATIONEXCLUSIONID] = @SEGMENTATIONEXCLUSIONID;

    open IDSETCURSOR;
    fetch next from IDSETCURSOR into @IDSETREGISTERID, @IDSETTABLENAME;

    while (@@FETCH_STATUS = 0)
    begin
      set @IDSETFUNCTIONNAME = 'UFN_' + @IDSETTABLENAME;
      set @IDSETFUNCTIONNAMEEXISTS = @IDSETFUNCTIONNAME + '_IDEXISTS';
      exec dbo.[USP_IDSETREGISTER_DELETEBYID_WITHCHANGEAGENTID] @IDSETREGISTERID, @CHANGEAGENTID;
      exec dbo.[USP_IDSET_DELETE] @IDSETFUNCTIONNAME, @IDSETFUNCTIONNAMEEXISTS;
      exec dbo.[USP_IDSET_DELETESTATICTABLE] @IDSETTABLENAME;

      fetch next from IDSETCURSOR into @IDSETREGISTERID, @IDSETTABLENAME;
    end

    close IDSETCURSOR;
    deallocate IDSETCURSOR;


    /*************************/
    /* Reinsert the SAR data */
    /*************************/
    declare RSCURSOR cursor local fast_forward for
      select [QUERYVIEWCATALOGID]
      from dbo.[UFN_MKTSEGMENTATION_GETDISTINCTRECORDSOURCES](@SEGMENTATIONID);

    open RSCURSOR;
    fetch next from RSCURSOR into @RECORDSOURCEID;

    while (@@FETCH_STATUS = 0)
    begin
      set @SARTABLE = dbo.[UFN_MKTSOURCEANALYSISRULE_MAKETABLENAME](@RECORDSOURCEID);
      set @SAREXCLTABLE = dbo.[UFN_MKTSOURCEANALYSISRULEEXCLUSION_MAKETABLENAME](@RECORDSOURCEID);

      set @SQL = 'insert into dbo.[' + @SARTABLE + '] (' + char(13);
      set @VALUESSQL = 'select' + char(13);

      if exists(select * from INFORMATION_SCHEMA.TABLES where [TABLE_SCHEMA] = 'dbo' and [TABLE_NAME] = @SAREXCLTABLE)
        begin
          --Build the SQL to copy all the columns...

          declare COLUMNCURSOR cursor local fast_forward for
            select [COLUMN_NAME] 
            from INFORMATION_SCHEMA.COLUMNS
            where [TABLE_SCHEMA] = 'dbo'
            and [TABLE_NAME] = @SARTABLE
            and [COLUMN_NAME] <> 'ID';  --ID is an identity field so we want to skip it


          open COLUMNCURSOR;
          fetch next from COLUMNCURSOR into @COLUMNNAME;

          if @@FETCH_STATUS = 0
            begin
              set @SQL += '  [' + @COLUMNNAME + ']' + char(13);
              set @VALUESSQL += '  [EXCL].[' + @COLUMNNAME + ']' + char(13);
              fetch next from COLUMNCURSOR into @COLUMNNAME;
            end

          while (@@FETCH_STATUS = 0)
          begin
            set @SQL += '  ,[' + @COLUMNNAME + ']' + char(13);
            set @VALUESSQL += '  ,[EXCL].[' + @COLUMNNAME + ']' + char(13);
            fetch next from COLUMNCURSOR into @COLUMNNAME;
          end

          close COLUMNCURSOR;
          deallocate COLUMNCURSOR;

          --Reinsert the SAR data...

          set @SQL += ')' + char(13) +
                      @VALUESSQL +
                      'from dbo.[' + @SAREXCLTABLE + '] as [EXCL]' + char(13) +
                      'left join dbo.[' + @SARTABLE + '] as [SAR] on [SAR].[FINDERNUMBER] = [EXCL].[FINDERNUMBER]' + char(13) +
                      'where [SEGMENTATIONEXCLUSIONID] = @SEGMENTATIONEXCLUSIONID' + char(13) +
                      'and [SAR].[FINDERNUMBER] is null';
          exec sp_executesql @SQL , N'@SEGMENTATIONEXCLUSIONID uniqueidentifier', @SEGMENTATIONEXCLUSIONID = @SEGMENTATIONEXCLUSIONID;

          --Delete from SAR exclusion table...

          set @SQL = 'delete from dbo.[' + @SAREXCLTABLE + ']' + char(13) +
                     'where [SEGMENTATIONEXCLUSIONID] = @SEGMENTATIONEXCLUSIONID';
          exec sp_executesql @SQL, N'@SEGMENTATIONEXCLUSIONID uniqueidentifier', @SEGMENTATIONEXCLUSIONID = @SEGMENTATIONEXCLUSIONID;
        end

      fetch next from RSCURSOR into @RECORDSOURCEID;      
    end

    close RSCURSOR;
    deallocate RSCURSOR;


    /*****************************/
    /* Reinsert the mailing data */
    /*****************************/
    set @DATATABLE = dbo.[UFN_MKTSEGMENTATIONACTIVATE_MAKETABLENAME](@SEGMENTATIONID);
    set @EXCLUSIONTABLE = dbo.[UFN_MKTSEGMENTATIONACTIVATEEXCLUSION_MAKETABLENAME](@SEGMENTATIONID);

    set @SQL = 'insert into dbo.[' + @DATATABLE + '] (' + char(13);
    set @VALUESSQL = 'select' + char(13);

    if exists(select * from INFORMATION_SCHEMA.TABLES where [TABLE_SCHEMA] = 'dbo' and [TABLE_NAME] = @EXCLUSIONTABLE)
      begin
        --Build the SQL to copy all the columns...

        declare COLUMNCURSOR cursor local fast_forward for
          select [COLUMN_NAME] 
          from INFORMATION_SCHEMA.COLUMNS 
          where [TABLE_SCHEMA] = 'dbo'
          and [TABLE_NAME] = @DATATABLE;

        open COLUMNCURSOR;
        fetch next from COLUMNCURSOR into @COLUMNNAME;

        if @@FETCH_STATUS = 0
          begin
            set @SQL += '  [' + @COLUMNNAME + ']' + char(13);
            set @VALUESSQL += '  [EXCL].[' + @COLUMNNAME + ']' + char(13);
            fetch next from COLUMNCURSOR into @COLUMNNAME;
          end

        while(@@FETCH_STATUS = 0)
        begin
          set @SQL += '  ,[' + @COLUMNNAME + ']' + char(13);
          set @VALUESSQL += '  ,[EXCL].[' + @COLUMNNAME + ']' + char(13);
          fetch next from COLUMNCURSOR into @COLUMNNAME;
        end

        close COLUMNCURSOR;
        deallocate COLUMNCURSOR;

        /****************************************/
        /* Restore the CONSTITUENTSEGMENT table */
        /****************************************/
        exec dbo.[USP_MKTSEGMENTATIONACTIVATE_CACHECONSTITUENTSEGMENTS] @SEGMENTATIONID, @SEGMENTATIONEXCLUSIONID;

        --Reinsert the mailing data...

        set @SQL += ')' + char(13) +
                    @VALUESSQL +
                    'from dbo.[' + @EXCLUSIONTABLE + '] as [EXCL]' + char(13) +
                    'left join dbo.[' + @DATATABLE + '] as [DATA] on [DATA].[FINDERNUMBER] = [EXCL].[FINDERNUMBER]' + char(13) +
                    'where [SEGMENTATIONEXCLUSIONID] = @SEGMENTATIONEXCLUSIONID' + char(13) +
                    'and [DATA].[FINDERNUMBER] is null';
        exec sp_executesql @SQL , N'@SEGMENTATIONEXCLUSIONID uniqueidentifier', @SEGMENTATIONEXCLUSIONID = @SEGMENTATIONEXCLUSIONID;

        --Delete from mailing data exclusion table...

        set @SQL = 'delete from dbo.[' + @EXCLUSIONTABLE + ']' + char(13) +
                   'where [SEGMENTATIONEXCLUSIONID] = @SEGMENTATIONEXCLUSIONID';

        exec sp_executesql @SQL, N'@SEGMENTATIONEXCLUSIONID uniqueidentifier', @SEGMENTATIONEXCLUSIONID = @SEGMENTATIONEXCLUSIONID;
      end

    /********************************/
  /* Delete mail exclusion record */
    /********************************/
    exec dbo.[USP_MKTSEGMENTATIONEXCLUSION_DELETEBYID_WITHCHANGEAGENTID] @SEGMENTATIONEXCLUSIONID, @CHANGEAGENTID;

  end try

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

  return 0;