USP_MKTSEGMENTATIONSEGMENT_DELETE

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

  declare @ACTIVE bit;
  declare @MAILINGTYPECODE tinyint;
  declare @SEGMENTATIONID uniqueidentifier;
  declare @SEGMENTATIONPACKAGEID uniqueidentifier;
  declare @PACKAGEID uniqueidentifier;
  declare @RECORDSOURCEID uniqueidentifier;
  declare @SEQUENCE int;
  declare @SEGMENTTYPECODE tinyint;
  declare @ISVENDORMANAGED bit;
  declare @DATATABLE nvarchar(128);
  declare @SQL nvarchar(max);
  declare @RESPONSECOUNTS table([OFFERS] int, [RESPONDERS] int, [RESPONSES] int, [TOTALGIFTAMOUNT] money, [AVERAGEGIFTAMOUNT] money, [ORGANIZATIONTOTALGIFTAMOUNT] money, [ORGANIZATIONAVERAGEGIFTAMOUNT] money);

  begin try
    /* Grab some info so we can remove the package and reorder the rest of the segments after the delete */
    select 
      @SEGMENTATIONID = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID],
      @DATATABLE = dbo.[UFN_MKTSEGMENTATIONACTIVATE_MAKETABLENAME]([MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID]),
      @SEGMENTATIONPACKAGEID = (select [ID] from dbo.[MKTSEGMENTATIONPACKAGE] where [MKTSEGMENTATIONPACKAGE].[SEGMENTATIONID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] and [MKTSEGMENTATIONPACKAGE].[PACKAGEID] = [MKTSEGMENTATIONSEGMENT].[PACKAGEID]),
      @PACKAGEID = [MKTSEGMENTATIONSEGMENT].[PACKAGEID],
      @SEQUENCE = [MKTSEGMENTATIONSEGMENT].[SEQUENCE],
      @RECORDSOURCEID = [MKTSEGMENT].[QUERYVIEWCATALOGID],
      @SEGMENTTYPECODE = [MKTSEGMENT].[SEGMENTTYPECODE],
      @ISVENDORMANAGED = (case when [MKTSEGMENT].[SEGMENTTYPECODE] = 2 then dbo.[UFN_MKTSEGMENT_ISVENDORMANAGEDLIST]([MKTSEGMENT].[ID]) else 0 end)
    from dbo.[MKTSEGMENTATIONSEGMENT]
    inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
    where [MKTSEGMENTATIONSEGMENT].[ID] = @ID;

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

    if @ACTIVE = 0 and @MAILINGTYPECODE <> 4
      -- check if the mailing is currently being activated

      exec dbo.[USP_MKTSEGMENTATION_CHECKACTIVATION] @SEGMENTATIONID;

    if @MAILINGTYPECODE = 4 and exists (select top 1 1 from sys.tables where name = @DATATABLE)
      begin
        insert into @RESPONSECOUNTS
          exec dbo.[USP_MKTSEGMENTATIONSEGMENT_GETRESPONSECOUNTS] @ID, null, null;

        if (select top 1 [RESPONSES] from @RESPONSECOUNTS) > 0
          begin
            raiserror('ERR_MKTSEGMENTATIONSEGMENTPASSIVE_HASRESPONSES', 13, 1);
            return 1;
          end          
      end

    /* Delete each test segment individually so the packages get adjusted correctly */
    declare @TESTSEGMENTID uniqueidentifier;
    declare TESTSEGMENTCURSOR cursor local fast_forward for
      select [ID]
      from dbo.[MKTSEGMENTATIONTESTSEGMENT]
      where [SEGMENTID] = @ID
      order by [SEQUENCE] desc;

    open TESTSEGMENTCURSOR;
    fetch next from TESTSEGMENTCURSOR into @TESTSEGMENTID;

    while (@@FETCH_STATUS = 0)
      begin
        exec dbo.[USP_MKTSEGMENTATIONTESTSEGMENT_DELETE] @TESTSEGMENTID, @CHANGEAGENTID;

        fetch next from TESTSEGMENTCURSOR into @TESTSEGMENTID;
      end;

    close TESTSEGMENTCURSOR;
    deallocate TESTSEGMENTCURSOR;

    /* Clear any segment cache */
    if @SEGMENTTYPECODE in (1, 3, 4, 5) or (@SEGMENTTYPECODE = 2 and @ISVENDORMANAGED = 0)  --Constituent, revenue, membership, sponsorship, or imported list

      exec dbo.[USP_MKTSEGMENTATIONSEGMENT_CLEARCACHE] @ID, 1, 0;
    if @SEGMENTTYPECODE = 2 and @ISVENDORMANAGED = 1  --Vendor managed list

      delete from dbo.[MKTSEGMENTATIONSEGMENTCACHEINFO] where [SEGMENTID] = @ID;
    if @SEGMENTTYPECODE in (6, 7, 8)
      begin
        declare @MKTSEGMENTATIONSEGMENTACTIVEID uniqueidentifier;

        select
          @MKTSEGMENTATIONSEGMENTACTIVEID = [ID]
        from dbo.[MKTSEGMENTATIONSEGMENTACTIVE]
        where [SEGMENTID] = @ID;

        exec dbo.[USP_MKTSEGMENTATIONSEGMENTACTIVE_DELETEBYID_WITHCHANGEAGENTID] @MKTSEGMENTATIONSEGMENTACTIVEID, @CHANGEAGENTID;
      end

    /* Delete segmentation segment rows from the MKTSOURCECODEPART table. */
    declare @MKTSOURCECODEPARTID uniqueidentifier;
    declare MKTSOURCECODEPARTCURSOR cursor local fast_forward for
      select [ID]
      from dbo.[MKTSOURCECODEPART]
      where [SEGMENTATIONSEGMENTID] = @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 or @MAILINGTYPECODE = 4
      begin
        if exists(select top 1 1 from [INFORMATION_SCHEMA].[TABLES] where [TABLE_SCHEMA] = 'dbo' and [TABLE_NAME] = @DATATABLE)
          begin
            set @SQL = 'delete from dbo.[' + @DATATABLE + '] where [SEGMENTID] = @ID;';

            exec sp_executesql @SQL, N'@ID uniqueidentifier', @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 = 'delete from dbo.[' + @DATATABLE + '] where [SEGMENTID] = @ID;';

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

    /* Delete 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 [SEGMENTATIONSEGMENTID] = @ID;';
        exec sp_executesql @SQL, N'@ID uniqueidentifier', @ID = @ID;
      end

    /* Delete the segment */
    exec dbo.[USP_MKTSEGMENTATIONSEGMENT_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 segments */
    update dbo.[MKTSEGMENTATIONSEGMENT] set
      [SEQUENCE] = ([SEQUENCE] - 1),
      [CHANGEDBYID] = @CHANGEAGENTID,
      [DATECHANGED] = getdate()
    where [SEGMENTATIONID] = @SEGMENTATIONID
    and [SEQUENCE] > @SEQUENCE;
  end try

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

  return 0;