USP_MKTSEGMENTATION_GETALLSELECTIONS

CREATE procedure dbo.[USP_MKTSEGMENTATION_GETALLSELECTIONS]
(
  @SEGMENTATIONID uniqueidentifier = null
)
as
  begin
    declare @SELECTIONS table ([SELECTIONID] uniqueidentifier, [PROCESSED] bit default 0);
    declare @SELECTIONID uniqueidentifier;
    declare @UNPROCESSEDCOUNT integer = -1;
    declare @SQL nvarchar(max);
    declare @XML xml;

    create table #SEGMENTEDHOUSEFILESEGMENTS ([ID] uniqueidentifier);

    set nocount on;

    -- this is a giant while loop because you can't call insert into [...] exec [SP] recursively


    while (@UNPROCESSEDCOUNT <> 0)
      begin
        if @UNPROCESSEDCOUNT < 0
          begin
            -- this is the first time through the loop, so get the selections that make up the marketing effort


            -- ignore segmented house file segments

            if dbo.[UFN_MKTCONSTITUENTFILEIMPORT_IMPORTTABLEEXISTS](@SEGMENTATIONID) = 1
              begin
                set @SQL = 'insert into ##SEGMENTEDHOUSEFILESEGMENTS select distinct [SEGMENTATIONSEGMENTID] from dbo.[' + dbo.[UFN_MKTCONSTITUENTFILEIMPORT_BUILDTABLENAME](@SEGMENTATIONID) + '];'

                exec (@SQL);
              end

            insert into @SELECTIONS ([SELECTIONID], [PROCESSED])
            select distinct
              [MKTSEGMENTSELECTION].[SELECTIONID],
              case when [IDSETREGISTERADHOCQUERY].[ADHOCQUERYID] is null then 1 else 0 end
            from dbo.[MKTSEGMENTATIONSEGMENT]
            inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
            left join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENT].[CURRENTSEGMENTLISTID]
            inner join dbo.[MKTSEGMENTSELECTION] on [MKTSEGMENTSELECTION].[SEGMENTID] = [MKTSEGMENT].[ID]
            inner join dbo.[IDSETREGISTER] on [IDSETREGISTER].[ID] = [MKTSEGMENTSELECTION].[SELECTIONID]
            left outer join dbo.[IDSETREGISTERADHOCQUERY] on [IDSETREGISTERADHOCQUERY].[IDSETREGISTERID] = [IDSETREGISTER].[ID]
            left outer join dbo.[IDSETREGISTERSMARTQUERYINSTANCE] on [IDSETREGISTERSMARTQUERYINSTANCE].[IDSETREGISTERID] = [IDSETREGISTER].[ID]
            left outer join dbo.[IDSETREGISTERDYNAMICIDSET] on [IDSETREGISTERDYNAMICIDSET].[STATICIDSETREGISTERID] = [IDSETREGISTER].[ID]
            where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID
            and ([MKTSEGMENT].[SEGMENTTYPECODE] <> 2 or [MKTSEGMENTLIST].[PARENTSEGMENTID] is not null)
            and not exists(select top 1 1
                    from dbo.[APPEALMAILING]
                    inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [APPEALMAILING].[MKTSEGMENTATIONSEGMENTID]
            where [MKTSEGMENTATIONSEGMENT].[SEGMENTID] = [MKTSEGMENT].[ID])
            and not exists(select top 1 1
                    from #SEGMENTEDHOUSEFILESEGMENTS
                    where #SEGMENTEDHOUSEFILESEGMENTS.[ID] = [MKTSEGMENTATIONSEGMENT].[ID])
            and [IDSETREGISTER].[STATIC] = 1
            and ([IDSETREGISTERADHOCQUERY].[ID] is not null or [IDSETREGISTERSMARTQUERYINSTANCE].[ID] is not null or [IDSETREGISTERDYNAMICIDSET].[ID] is not null)
            union
            select distinct
              [MKTSEGMENTATIONFILTERSELECTION].[SELECTIONID],
              case when [IDSETREGISTERADHOCQUERY].[ADHOCQUERYID] is null then 1 else 0 end
            from dbo.[MKTSEGMENTATIONFILTERSELECTION]
            inner join dbo.[IDSETREGISTER] on [IDSETREGISTER].[ID] = [MKTSEGMENTATIONFILTERSELECTION].[SELECTIONID]
            left outer join dbo.[IDSETREGISTERADHOCQUERY] on [IDSETREGISTERADHOCQUERY].[IDSETREGISTERID] = [IDSETREGISTER].[ID]
            left outer join dbo.[IDSETREGISTERSMARTQUERYINSTANCE] on [IDSETREGISTERSMARTQUERYINSTANCE].[IDSETREGISTERID] = [IDSETREGISTER].[ID]
            left outer join dbo.[IDSETREGISTERDYNAMICIDSET] on [IDSETREGISTERDYNAMICIDSET].[STATICIDSETREGISTERID] = [IDSETREGISTER].[ID]
            where [MKTSEGMENTATIONFILTERSELECTION].[SEGMENTATIONID] = @SEGMENTATIONID
            and [IDSETREGISTER].[STATIC] = 1
            and ([IDSETREGISTERADHOCQUERY].[ID] is not null or [IDSETREGISTERSMARTQUERYINSTANCE].[ID] is not null or [IDSETREGISTERDYNAMICIDSET].[ID] is not null)
          end
        else
          begin
            -- this is not the first time through the loop, so count the selections used by any selections that haven't been counted yet


            declare SELECTIONSTOPROCESS cursor local fast_forward for
            select [SELECTIONID] from @SELECTIONS where [PROCESSED] = 0;

            open SELECTIONSTOPROCESS;
            fetch next from SELECTIONSTOPROCESS into @SELECTIONID;

            while (@@FETCH_STATUS = 0)
              begin
                select 
                  @XML = [ADHOCQUERY].[QUERYDEFINITIONXML]
                from dbo.[ADHOCQUERY]
                inner join dbo.[IDSETREGISTERADHOCQUERY] on [IDSETREGISTERADHOCQUERY].[ADHOCQUERYID] = [ADHOCQUERY].[ID]
                where [IDSETREGISTERADHOCQUERY].[IDSETREGISTERID] = @SELECTIONID;

                if @XML is not null
                  with xmlnamespaces('Blackbaud.AppFx.WebService.API.1' as api),
                  [CHILDSELECTIONS] ([SELECTIONID]) as (
                  select
                    T.c.value('(api:IDSetFieldInfo/api:ID)[1]', 'uniqueidentifier') as [SELECTIONID]
                  from @XML.nodes('/api:AdHocQuery/api:FilterFields/api:f') as T(c)
                  where T.c.value('@IsIDSetField', 'bit') = 1
                  union
                  select
                    T.c.value('(api:IDSetFieldInfo/api:ID)[1]', 'uniqueidentifier') as [SELECTIONID]
                  from @XML.nodes('/api:AdHocQuery/api:SelectFields/api:f') as T(c)
                  where T.c.value('@IsIDSetField', 'bit') = 1)
                  insert into @SELECTIONS ([SELECTIONID], [PROCESSED])
                  select
                    [CHILDSELECTIONS].[SELECTIONID],
                    case when [IDSETREGISTERADHOCQUERY].[ADHOCQUERYID] is null then 1 else 0 end
                  from [CHILDSELECTIONS]
                  left outer join @SELECTIONS as [SELECTIONSSOFAR] on [SELECTIONSSOFAR].[SELECTIONID] = [CHILDSELECTIONS].[SELECTIONID]
                  left outer join dbo.[IDSETREGISTERADHOCQUERY] on [IDSETREGISTERADHOCQUERY].[IDSETREGISTERID] = [CHILDSELECTIONS].[SELECTIONID]
                  where [SELECTIONSSOFAR].[SELECTIONID] is null;

                update @SELECTIONS set [PROCESSED] = 1 where [SELECTIONID] = @SELECTIONID;

                fetch next from SELECTIONSTOPROCESS into @SELECTIONID;
              end

              close SELECTIONSTOPROCESS;
              deallocate SELECTIONSTOPROCESS;
          end

        select @UNPROCESSEDCOUNT = count(*) from @SELECTIONS where [PROCESSED] = 0;
      end

    drop table #SEGMENTEDHOUSEFILESEGMENTS;

    select [IDSETREGISTER].[ID], [IDSETREGISTER].[NAME]
    from @SELECTIONS as [S]
    inner join dbo.[IDSETREGISTER] on [IDSETREGISTER].[ID] = [S].[SELECTIONID]

    return 0;
  end