USP_MKTEXPORTDEFINITION_GETAVAILABLEFIELDS

CREATE procedure dbo.[USP_MKTEXPORTDEFINITION_GETAVAILABLEFIELDS]
(
  @QUERYVIEWCATALOGID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier
)
as
  set nocount on;

  declare @AVAILABLEFIELDS table (
    [ID] uniqueidentifier,
    [CATEGORYORDER] tinyint,
    [QUERYVIEWCATALOGID] uniqueidentifier,
    [CUSTOMQUERYVIEWCATALOGID] uniqueidentifier,
    [QUERYVIEWNAME] nvarchar(255),
    [CATEGORY] nvarchar(255),
    [NAME] nvarchar(255),
    [CAPTION] nvarchar(255),
    [ISHIDDEN] nvarchar(5),
    [TYPECODE] tinyint,
    [DATATYPE] nvarchar(128),
    [ISALLOWED] bit,
    [INSTALLEDPRODUCTS] xml)

  declare @QUERYVIEWNAME nvarchar(255);
  declare @OUTPUTDEFINITIONXML xml;
  declare @QUERYVIEWOBJECTNAME nvarchar(128);
  declare @CUSTOMQUERYVIEWCATALOGID uniqueidentifier;
  declare @ID uniqueidentifier;
  declare @INSTALLEDPRODUCTS xml;

  if (dbo.[UFN_MKTRECORDSOURCE_VALIDFORPRODUCT](@QUERYVIEWCATALOGID) = 1)
    begin
      select
        @QUERYVIEWNAME = [DISPLAYNAME],
        @OUTPUTDEFINITIONXML = [OUTPUTDEFINITIONXML],
        @QUERYVIEWOBJECTNAME = [OBJECTNAME]
      from dbo.[QUERYVIEWCATALOG]
      where [ID] = @QUERYVIEWCATALOGID;

      -- query view fields

      with xmlnamespaces ('bb_appfx_queryview' as QV, 'bb_appfx_commontypes' as C)
      insert into @AVAILABLEFIELDS
        select
          newid() as [ID],
          0 as [CATEGORYORDER],
          @QUERYVIEWCATALOGID as [QUERYVIEWCATALOGID],
          null as [CUSTOMQUERYVIEWCATALOGID],
          @QUERYVIEWNAME as [QUERYVIEWNAME],
          T.c.value('(@Category)[1]','nvarchar(255)') as [CATEGORY],
          T.c.value('(@Name)[1]','nvarchar(255)') as [NAME],
          isnull(T.c.value('(@Caption)[1]','nvarchar(255)'), T.c.value('(@Name)[1]','nvarchar(255)')) as [CAPTION],
          isnull(T.c.value('(@IsHidden)[1]', 'nvarchar(5)'), 'false') as [ISHIDDEN],
          0 as [TYPECODE],
          (select isnull([DOMAIN_NAME], [DATA_TYPE]) from INFORMATION_SCHEMA.COLUMNS where [TABLE_SCHEMA] = 'dbo' and [TABLE_NAME] = @QUERYVIEWOBJECTNAME and [COLUMN_NAME] = T.c.value('(@Name)[1]','nvarchar(255)')) as [DATATYPE],
          1 as [ISALLOWED],
          T.c.query('./C:InstalledProductList/C:InstalledProduct') as [INSTALLEDPRODUCTS]
        from @OUTPUTDEFINITIONXML.nodes('/QueryViewOutput/QV:OutputFields/QV:OutputField') T(c);

      if (dbo.[UFN_MKTRECORDSOURCE_VALIDFORBBEC](@QUERYVIEWCATALOGID) = 1 and dbo.[UFN_MKTRECORDSOURCE_ISDONORRECORDSOURCE](@QUERYVIEWCATALOGID) = 1) or 
          exists (select top 1 1 from dbo.[MKTCONSOLIDATEDQUERYVIEWSPEC] where [CONSOLIDATEDQUERYVIEWCATALOGID] = @QUERYVIEWCATALOGID and dbo.[UFN_MKTRECORDSOURCE_VALIDFORBBEC]([ID]) = 1 and dbo.[UFN_MKTRECORDSOURCE_ISDONORRECORDSOURCE]([ID]) = 1)
        insert into @AVAILABLEFIELDS values (
          newid(),
          0,
          @QUERYVIEWCATALOGID,
          null,
          @QUERYVIEWNAME,
          null,
          'CONTACTADDRESSEE',
          'Contact addressee',
          'false',
          4,
          'nvarchar',
          1,
          null);

      if exists (select top 1 1 from dbo.[MKTMEMBERSHIPRECORDSOURCE] where [QUERYVIEWCATALOGID] = @QUERYVIEWCATALOGID)
        insert into @AVAILABLEFIELDS values (
          newid(),
          0,
          @QUERYVIEWCATALOGID,
          null,
          @QUERYVIEWNAME,
          null,
          'ISMEMBER',
          'Is member?',
          'false',
          4,
          'bit',
          1,
          null);

      -- if the consolidated query view ID has been passed, use the underlying record source's query view ID to 

      -- find smart fields and custom query view fields

      select
        @QUERYVIEWCATALOGID = isnull([ID], @QUERYVIEWCATALOGID)
      from dbo.[MKTCONSOLIDATEDQUERYVIEWSPEC]
      where [CONSOLIDATEDQUERYVIEWCATALOGID] = @QUERYVIEWCATALOGID;

      --Remove any fields that have product flags that are not installed...

      declare PRODUCTCURSOR cursor local fast_forward for
        select
          [ID],
          [INSTALLEDPRODUCTS]
        from @AVAILABLEFIELDS
        where [INSTALLEDPRODUCTS] is not null
        and cast([INSTALLEDPRODUCTS] as varchar(max)) <> '';

      open PRODUCTCURSOR;
      fetch next from PRODUCTCURSOR into @ID, @INSTALLEDPRODUCTS

      while (@@FETCH_STATUS = 0)
      begin
        if not exists(select top 1 1 from @INSTALLEDPRODUCTS.nodes('declare namespace C="bb_appfx_commontypes"; /C:InstalledProduct') T(c) where dbo.[UFN_INSTALLEDPRODUCTS_PRODUCTIS](T.c.value('(@ID)[1]', 'uniqueidentifier')) = 1)
          delete @AVAILABLEFIELDS
          where [ID] = @ID;

        fetch next from PRODUCTCURSOR into @ID, @INSTALLEDPRODUCTS
      end

      close PRODUCTCURSOR;
      deallocate PRODUCTCURSOR;
    end

  select 
    [QUERYVIEWCATALOGID],
    [CUSTOMQUERYVIEWCATALOGID],
    [QUERYVIEWNAME],
    [CATEGORY],
    [NAME],
    [CAPTION],
    [ISHIDDEN],
    [TYPECODE],
    [DATATYPE],
    [ISALLOWED]
  from @AVAILABLEFIELDS
  order by [CATEGORYORDER], [QUERYVIEWNAME], [CATEGORY], [CAPTION], [NAME];

  return 0;