USP_MKTSEGMENTLIST_DELETEIMPORTDATA

CREATE procedure dbo.[USP_MKTSEGMENTLIST_DELETEIMPORTDATA]
(
  @SEGMENTID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier,
  @DELETINGSEGMENT bit,
  @CHANGEAGENTID uniqueidentifier = null
)
as
  set nocount on;

  declare @SEGMENTLISTID uniqueidentifier;
  declare @QUERYVIEWCATALOGID as uniqueidentifier;
  declare @STANDARDIDSETID uniqueidentifier;
  declare @DUPLICATEIDSETID uniqueidentifier;
  declare @IDSETRECORDTYPEID uniqueidentifier;
  declare @RECORDSOURCEID uniqueidentifier;
  declare @QUERYVIEWS table([ID] uniqueidentifier primary key, [RECORDTYPEID] uniqueidentifier);

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

    select
      @SEGMENTLISTID = [MKTSEGMENTLIST].[ID],
      @QUERYVIEWCATALOGID = [MKTSEGMENTLIST].[QUERYVIEWCATALOGID],
      @STANDARDIDSETID = [MKTSEGMENTLIST].[STANDARDIDSETID],
      @DUPLICATEIDSETID = [MKTSEGMENTLIST].[DUPLICATEIDSETID],
      @IDSETRECORDTYPEID = [MKTSEGMENTLIST].[IDSETRECORDTYPEID],
      @RECORDSOURCEID = [MKTLIST].[RECORDSOURCEID]
    from dbo.[MKTSEGMENT]
    inner join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENT].[CURRENTSEGMENTLISTID]
    inner join dbo.[MKTLIST] on [MKTLIST].[ID] = [MKTSEGMENTLIST].[LISTID]
    where [MKTSEGMENT].[ID] = @SEGMENTID;


    if @DELETINGSEGMENT = 1
      --Grab all the different query views for this segment so we know what to clean up...

      insert into @QUERYVIEWS ([ID], [RECORDTYPEID])
        select
          [QUERYVIEWCATALOG].[ID],
          [QUERYVIEWCATALOG].[RECORDTYPEID]
        from dbo.[MKTSEGMENTLIST]
        inner join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[ID] = [MKTSEGMENTLIST].[QUERYVIEWCATALOGID]
        where [SEGMENTID] = @SEGMENTID;
    else if @QUERYVIEWCATALOGID is not null and @IDSETRECORDTYPEID is not null
      --Grab just the one query view for this list segment...

      insert into @QUERYVIEWS (
        [ID],
        [RECORDTYPEID]
      ) values (
        @QUERYVIEWCATALOGID,
        @IDSETRECORDTYPEID
      );


    --Set all IDSet related values to null so we don't invalidate any foreign key relationships when the queries are deleted.

    --Do not clear the QUERYVIEWCATALOGID field except when deleting the entire segment because we need it for activated mailings.

    update dbo.[MKTSEGMENTLIST] set
      [STANDARDIDSETID] = null,
      [DUPLICATEIDSETID] = null,
      [IDSETRECORDTYPEID] = null,
      [CONSOLIDATEDQUERYVIEWID] = null,
      [CHANGEDBYID] = @CHANGEAGENTID,
      [DATECHANGED] = getdate()
    where (@DELETINGSEGMENT = 0 and [ID] = @SEGMENTLISTID)
    or (@DELETINGSEGMENT = 1 and [SEGMENTID] = @SEGMENTID);


    /****************************************************/
    /* Delete the list segment deduplication process... */
    /****************************************************/
    declare @SEGMENTLISTDEDUPEPROCESSID uniqueidentifier;

    select @SEGMENTLISTDEDUPEPROCESSID = [ID]
    from dbo.[MKTSEGMENTLISTDEDUPEPROCESS]
    where [SEGMENTLISTID] = @SEGMENTLISTID;

    if @SEGMENTLISTDEDUPEPROCESSID is not null
      exec dbo.[USP_MKTSEGMENTLISTDEDUPEPROCESS_DELETEBYID_WITHCHANGEAGENTID] @SEGMENTLISTDEDUPEPROCESSID, @CHANGEAGENTID;


    /*********************************************/
    /* Delete the list segment import process... */
    /*********************************************/
    declare @SEGMENTLISTIMPORTPROCESSID uniqueidentifier;

    select @SEGMENTLISTIMPORTPROCESSID = [ID]
    from dbo.[MKTSEGMENTLISTIMPORTPROCESS]
    where [SEGMENTLISTID] = @SEGMENTLISTID;

    if @SEGMENTLISTIMPORTPROCESSID is not null
      exec dbo.[USP_MKTSEGMENTLISTIMPORTPROCESS_DELETEBYID_WITHCHANGEAGENTID] @SEGMENTLISTIMPORTPROCESSID, @CHANGEAGENTID;


    if @DELETINGSEGMENT = 1
      begin
        /***************************************************************************/
        /* Delete all ad-hoc queries and their IDSets associated with this list... */
        /***************************************************************************/
        declare @ADHOCQUERYID uniqueidentifier;
        declare ADHOCQUERYCURSOR cursor local fast_forward for
          select [ID]
          from dbo.[ADHOCQUERY]
          where [QUERYVIEWCATALOGID] in (select [ID] from @QUERYVIEWS);

        open ADHOCQUERYCURSOR;
        fetch next from ADHOCQUERYCURSOR into @ADHOCQUERYID;

        while (@@FETCH_STATUS = 0)
        begin
          exec dbo.[USP_ADHOCQUERY_DELETE] @ADHOCQUERYID, @CURRENTAPPUSERID, @CHANGEAGENTID;
          fetch next from ADHOCQUERYCURSOR into @ADHOCQUERYID;
        end

        close ADHOCQUERYCURSOR;
        deallocate ADHOCQUERYCURSOR;


        /***************************************************************************/
        /* Delete any query view relationships along with the actual query view... */
        /***************************************************************************/
        declare @QUERYVIEWRELATIONSHIPID uniqueidentifier;
        declare QUERYVIEWRELATIONSHIPCURSOR cursor local fast_forward for
          select [ID]
          from dbo.[QUERYVIEWRELATIONSHIP]
          where [ROOTQUERYVIEWID] in (select [ID] from @QUERYVIEWS);

        open QUERYVIEWRELATIONSHIPCURSOR;
        fetch next from QUERYVIEWRELATIONSHIPCURSOR into @QUERYVIEWRELATIONSHIPID;

        while (@@FETCH_STATUS = 0)
        begin
          exec dbo.[USP_QUERYVIEWRELATIONSHIP_DELETEBYID_WITHCHANGEAGENTID] @QUERYVIEWRELATIONSHIPID, @CHANGEAGENTID;
          fetch next from QUERYVIEWRELATIONSHIPCURSOR into @QUERYVIEWRELATIONSHIPID;
        end

        close QUERYVIEWRELATIONSHIPCURSOR;
        deallocate QUERYVIEWRELATIONSHIPCURSOR;


        /****************************************************************************************************************************/
        /* Check for and update any "upgraded" list segments that point at the same SEGMENTLISTID in the MKTESEGMENTLISTDATA table. */
        /****************************************************************************************************************************/
        --Check for other list segments that may point at the same records in MKTSEGMENTLISTDATA as the one we are trying to delete.

        --All this work only needs to be done if the user deletes the list segment whose ID is used in MKTSEGMENTLISTDATA.  If one

        --of the other "upgrade" list segment's ID does not exist in the MKTSEGMENTLISTDATA table, then we can just run through our

        --normal delete process.  This is only here so we don't delete the actual imported list data until there is only one list

        --segment left in the system that points at the set of records in MKTSEGMENTLISTDATA.

        declare @UPGRADEVIEWS table([NEWSEGMENTLISTID] uniqueidentifier, [VIEWDEFINITION] nvarchar(max));
        declare @NEWSEGMENTLISTID uniqueidentifier;
        declare @VIEWDEFINITION nvarchar(max);

        insert into @UPGRADEVIEWS ([NEWSEGMENTLISTID], [VIEWDEFINITION])
          select
            cast(replace(substring([O].[NAME], len('V_MKTSEGMENTLIST_') + 1, 36), '_', '-') as uniqueidentifier) as [SEGMENTLISTID],
            [M].[DEFINITION]
          from sys.all_sql_modules as [M]
          inner join sys.objects as [O] on [M].[OBJECT_ID] = [O].[OBJECT_ID]
          where ([O].[NAME] like 'V\_MKTSEGMENTLIST\_[A-Fa-f0-9][A-Fa-f0-9][A-Fa-f0-9][A-Fa-f0-9][A-Fa-f0-9][A-Fa-f0-9][A-Fa-f0-9][A-Fa-f0-9]\_[A-Fa-f0-9][A-Fa-f0-9][A-Fa-f0-9][A-Fa-f0-9]\_[A-Fa-f0-9][A-Fa-f0-9][A-Fa-f0-9][A-Fa-f0-9]\_[A-Fa-f0-9][A-Fa-f0-9][A-Fa-f0-9][A-Fa-f0-9]\_[A-Fa-f0-9][A-Fa-f0-9][A-Fa-f0-9][A-Fa-f0-9][A-Fa-f0-9][A-Fa-f0-9][A-Fa-f0-9][A-Fa-f0-9][A-Fa-f0-9][A-Fa-f0-9][A-Fa-f0-9][A-Fa-f0-9]' escape '\'
          or [O].[NAME] like 'V\_MKTSEGMENTLIST\_[A-Fa-f0-9][A-Fa-f0-9][A-Fa-f0-9][A-Fa-f0-9][A-Fa-f0-9][A-Fa-f0-9][A-Fa-f0-9][A-Fa-f0-9]\_[A-Fa-f0-9][A-Fa-f0-9][A-Fa-f0-9][A-Fa-f0-9]\_[A-Fa-f0-9][A-Fa-f0-9][A-Fa-f0-9][A-Fa-f0-9]\_[A-Fa-f0-9][A-Fa-f0-9][A-Fa-f0-9][A-Fa-f0-9]\_[A-Fa-f0-9][A-Fa-f0-9][A-Fa-f0-9][A-Fa-f0-9][A-Fa-f0-9][A-Fa-f0-9][A-Fa-f0-9][A-Fa-f0-9][A-Fa-f0-9][A-Fa-f0-9][A-Fa-f0-9][A-Fa-f0-9]\_DUPLICATE' escape '\')
          and [O].[NAME] <> ('V_MKTSEGMENTLIST_' + replace(cast(@SEGMENTLISTID as nvarchar(36)), '-', '_'))
          and [O].[NAME] <> ('V_MKTSEGMENTLIST_' + replace(cast(@SEGMENTLISTID as nvarchar(36)), '-', '_') + '_DUPLICATE')
          and [M].[DEFINITION] like '/*##LIST UPGRADE##%'
          and [M].[DEFINITION] like '%\[SEGMENTLISTID\] = ''' + cast(@SEGMENTLISTID as nvarchar(36)) + '''%' escape '\';

        select top 1
          @NEWSEGMENTLISTID = [NEWSEGMENTLISTID]
        from @UPGRADEVIEWS;

        if @NEWSEGMENTLISTID is not null
          begin
            --Update the MKTSEGMENTLISTDATA table to point at a different SEGMENTLISTID...
            update dbo.[MKTSEGMENTLISTDATA] set
              [SEGMENTLISTID] = @NEWSEGMENTLISTID
            where [SEGMENTLISTID] = @SEGMENTLISTID;

            --Update the remaining "upgrade" views to point at the new SEGMENTLISTID...
            declare VIEWCURSOR cursor local fast_forward for
              select [VIEWDEFINITION]
              from @UPGRADEVIEWS;

            open VIEWCURSOR;
            fetch next from VIEWCURSOR into @VIEWDEFINITION;

            while (@@FETCH_STATUS = 0)
            begin
              set @VIEWDEFINITION = replace(@VIEWDEFINITION, 'create view', 'alter view');
              set @VIEWDEFINITION = replace(@VIEWDEFINITION, '[SEGMENTLISTID] = ''' + cast(@SEGMENTLISTID as nvarchar(36)) + '''', '[SEGMENTLISTID] = ''' + cast(@NEWSEGMENTLISTID as nvarchar(36)) + '''');
              exec (@VIEWDEFINITION);

              fetch next from VIEWCURSOR into @VIEWDEFINITION;
            end

            close VIEWCURSOR;
            deallocate VIEWCURSOR;
          end
      end


    /*************************/
    /* Delete the ID sets... */
    /*************************/
    exec dbo.[USP_MKTSEGMENTLIST_DELETEIDSETS] @SEGMENTLISTID, @STANDARDIDSETID, @DUPLICATEIDSETID, null, @CHANGEAGENTID;


    if @DELETINGSEGMENT = 1
      begin
        /************************************************************************************/
        /* Delete all the query view catalog items for all finder files for this segment... */
        /************************************************************************************/
        --Clear the QUERYVIEWCATALOGIDs for all the finder files for this segment...

        update dbo.[MKTSEGMENTLIST] set
          [QUERYVIEWCATALOGID] = null,
          [CHANGEDBYID] = @CHANGEAGENTID,
          [DATECHANGED] = getdate()
        where [SEGMENTID] = @SEGMENTID;

        --Delete the records from the query view catalog and drop the underlying views...

        declare @VIEWNAMEFORQUERY nvarchar(128);
        declare QUERYVIEWCURSOR cursor local fast_forward for
          select [QV].[ID], [QUERYVIEWCATALOG].[OBJECTNAME]
          from @QUERYVIEWS as [QV]
          inner join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[ID] = [QV].[ID];

        open QUERYVIEWCURSOR;
        fetch next from QUERYVIEWCURSOR into @QUERYVIEWCATALOGID, @VIEWNAMEFORQUERY;

        while (@@FETCH_STATUS = 0)
        begin
          --Delete the query view catalog record...

          exec dbo.[USP_QUERYVIEWCATALOG_DELETEBYID_WITHCHANGEAGENTID] @QUERYVIEWCATALOGID, @CHANGEAGENTID;

          --Drop the query view for each finder file...

          if exists(select 1 from INFORMATION_SCHEMA.VIEWS where [TABLE_SCHEMA] = 'dbo' and [TABLE_NAME] = @VIEWNAMEFORQUERY)
            exec ('drop view dbo.[' + @VIEWNAMEFORQUERY + ']');

          fetch next from QUERYVIEWCURSOR into @QUERYVIEWCATALOGID, @VIEWNAMEFORQUERY;
        end

        close QUERYVIEWCURSOR;
        deallocate QUERYVIEWCURSOR;


        /*******************************************************************************/
        /* Cleanup the LoadSpecLog table so it doesn't get cluttered with artifacts... */
        /*******************************************************************************/
        declare @LOADSPECLOGID uniqueidentifier;
        declare LOADSPECLOGCURSOR cursor local fast_forward for
          select [ID]
          from dbo.[LOADSPECLOG]
          where [SPECID] in (select [ID] from @QUERYVIEWS);

        open LOADSPECLOGCURSOR;
        fetch next from LOADSPECLOGCURSOR into @LOADSPECLOGID;

        while (@@FETCH_STATUS = 0)
        begin
          exec dbo.[USP_LOADSPECLOG_DELETEBYID_WITHCHANGEAGENTID] @LOADSPECLOGID, @CHANGEAGENTID;
          fetch next from LOADSPECLOGCURSOR into @LOADSPECLOGID;
        end

        close LOADSPECLOGCURSOR;
        deallocate LOADSPECLOGCURSOR;


        /********************************************************/
        /* Delete all the IDSet recordtypes for this segment... */
        /********************************************************/
        declare RECORDTYPECURSOR cursor local fast_forward for
          select distinct [RECORDTYPEID]
          from @QUERYVIEWS;

        open RECORDTYPECURSOR;
        fetch next from RECORDTYPECURSOR into @IDSETRECORDTYPEID;

        while (@@FETCH_STATUS = 0)
        begin
          exec dbo.[USP_RECORDTYPE_DELETEBYID_WITHCHANGEAGENTID] @IDSETRECORDTYPEID, @CHANGEAGENTID;
          fetch next from RECORDTYPECURSOR into @IDSETRECORDTYPEID;
        end

        close RECORDTYPECURSOR;
        deallocate RECORDTYPECURSOR;


        /***********************************************************************/
        /* Delete all this segment's imported data for all its finder files... */
        /***********************************************************************/
        declare @SQL nvarchar(max);

        --Delete all the records in the matchback gift donors table.  Since the user cannot change the record source on a list,

        --we only have to worry about deleting from one record source matchback table here.

        set @SQL = 'delete from dbo.[MKTSEGMENTLISTDATADONORS_' + replace(cast(@RECORDSOURCEID as nvarchar(36)), '-', '_') + ']' + char(13) +
                   'where [ID] in (select [ID] from dbo.[MKTSEGMENTLISTDATA] where [SEGMENTLISTID] in (select [ID] from dbo.[MKTSEGMENTLIST] where [SEGMENTID] = @SEGMENTID))';
        exec sp_executesql @SQL, N'@SEGMENTID uniqueidentifier', @SEGMENTID = @SEGMENTID;

        --Delete all the imported records for this segment, Imported, Historical, Purged, .... , everything...

        delete from dbo.[MKTSEGMENTLISTDATA]
        where [SEGMENTLISTID] in (select [ID] from dbo.[MKTSEGMENTLIST] where [SEGMENTID] = @SEGMENTID);


        /****************************************************/
        /* Delete all this segment's finder file records... */
        /****************************************************/
        --Update the segment and clear the current finder file reference so we don't get foreign key errors when we delete the finder files below...

        update dbo.[MKTSEGMENT] set
          [CURRENTSEGMENTLISTID] = null,
          [CHANGEDBYID] = @CHANGEAGENTID,
          [DATECHANGED] = getdate()
        where [ID] = @SEGMENTID;

        --Delete all the list segment finder files for this segment...

        declare SEGMENTLISTCURSOR cursor local fast_forward for
          select [ID], dbo.[UFN_MKTSEGMENTLIST_MAKEVIEWNAME_FORQUERY]([ID])
          from dbo.[MKTSEGMENTLIST]
          where [SEGMENTID] = @SEGMENTID;

        open SEGMENTLISTCURSOR;
        fetch next from SEGMENTLISTCURSOR into @SEGMENTLISTID, @VIEWNAMEFORQUERY;

        while (@@FETCH_STATUS = 0)
        begin
          exec dbo.[USP_MKTSEGMENTLIST_DELETEBYID_WITHCHANGEAGENTID] @SEGMENTLISTID, @CHANGEAGENTID;
     fetch next from SEGMENTLISTCURSOR into @SEGMENTLISTID, @VIEWNAMEFORQUERY;
        end

        close SEGMENTLISTCURSOR;
        deallocate SEGMENTLISTCURSOR;
      end
  end try

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

  return 0;