USP_MKTSEGMENTATION_EXPORTDEFINITIONFILELAYOUT

CREATE procedure dbo.[USP_MKTSEGMENTATION_EXPORTDEFINITIONFILELAYOUT]
(
  @EXPORTDEFINITIONID uniqueidentifier = null
)
as
  set nocount on;

  declare @QUERYVIEWCATALOGID as uniqueidentifier;
  declare @CUSTOMQUERYVIEWCATALOGID as uniqueidentifier;
  declare @OUTPUTDEFINITIONXML as xml;
  declare @MAILINGTYPECODE as tinyint;

  declare @RESULTS table
  (
    [QUERYVIEWCATALOGID] uniqueidentifier,
    [DISPLAYNAME] nvarchar(255),
    [PATHALIAS] nvarchar(255),
    [CATEGORY] nvarchar(255),
    [HEADERNAME] nvarchar(255),
    [DATATYPE] nvarchar(255),
    [SEQUENCE] int,
    [MAILINGTYPECODE] tinyint,
    [FIELDISHIDDEN] nvarchar(5),
    [OUTPUTFIELDTYPECODE] tinyint
  );

  declare @SPECIALFIELDS table
  (
    [QUERYVIEWCATALOGID] uniqueidentifier not null,
    [QUERYFIELD] nvarchar(255),
    [DATA_TYPE] nvarchar(128) not null,
    [CHARACTER_MAXIMUM_LENGTH] int,
    [NUMERIC_PRECISION] tinyint,
    [NUMERIC_SCALE] int,
    [DOMAIN_NAME] nvarchar(128)
  );

  select 
    @MAILINGTYPECODE = [MKTEXPORTDEFINITION].[MAILINGTYPECODE]
  from dbo.[MKTEXPORTDEFINITION]
  where [ID] = @EXPORTDEFINITIONID;

  if dbo.[UFN_INSTALLEDPRODUCTS_PRODUCTIS]('BB9873D7-F1ED-430A-8AB4-F09F47056538') = 0 -- only perform this block for enterprise products

    begin
      declare @BBECRECORDSOURCEID uniqueidentifier;
      set @BBECRECORDSOURCEID = dbo.[UFN_MKTRECORDSOURCE_GETFIRSTBBECRECORDSOURCEID]();

      -- add the BBEC contact addressee as a special field

      insert into @SPECIALFIELDS
        select @BBECRECORDSOURCEID, 'CONTACTADDRESSEE', 'nvarchar', '700', null, null, null;

      if @MAILINGTYPECODE = 2 -- membership mailings

        insert into @SPECIALFIELDS
          select [QUERYVIEWCATALOGID], 'ISMEMBER', 'bit', null, null, null, null
          from dbo.[MKTMEMBERSHIPRECORDSOURCE]
          where [ID] = @BBECRECORDSOURCEID;
    end

  declare QUERYVIEWCURSOR cursor local fast_forward for
    select distinct
      [MKTEXPORTDEFINITIONOUTPUTFIELD].[QUERYVIEWCATALOGID],
      [MKTEXPORTDEFINITIONOUTPUTFIELD].[CUSTOMQUERYVIEWCATALOGID]
    from dbo.[MKTEXPORTDEFINITIONOUTPUTFIELD]
    where [EXPORTDEFINITIONID] = @EXPORTDEFINITIONID;

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

  while (@@FETCH_STATUS = 0)
  begin

    if @CUSTOMQUERYVIEWCATALOGID is not null
      begin
        select 
          @OUTPUTDEFINITIONXML = [QUERYVIEWCATALOG].[OUTPUTDEFINITIONXML] 
        from dbo.[QUERYVIEWCATALOG] 
        where [QUERYVIEWCATALOG].[ID] = @CUSTOMQUERYVIEWCATALOGID;

        --Insert custom query view output fields into the results table.

        insert into @RESULTS
          select
            @QUERYVIEWCATALOGID,
            [SQVC].[DISPLAYNAME],
            substring([VIEWPATH],patindex('%\%',[VIEWPATH]) + 1,1500) [PATHALIAS],
            T.c.value('(@Category)[1]','nvarchar(255)'),
            [MKTEXPORTDEFINITIONOUTPUTFIELD].[NAME] as [HEADERNAME],
            coalesce([SCHEMAQV].[DATA_TYPE], [SPECIALFIELDS].[DATA_TYPE]) as [DATATYPE],
            (select min([S].[SEQUENCE]) from dbo.[MKTEXPORTDEFINITIONOUTPUTFIELD] as [S] where [S].[EXPORTDEFINITIONID] = [MKTEXPORTDEFINITIONOUTPUTFIELD].[EXPORTDEFINITIONID] and [S].[NAME] = [MKTEXPORTDEFINITIONOUTPUTFIELD].[NAME]) as [SEQUENCE],
            @MAILINGTYPECODE,
            isnull(T.c.value('(@IsHidden)[1]','nvarchar(5)'), 'false'),
            [MKTEXPORTDEFINITIONOUTPUTFIELD].[TYPECODE]
          from dbo.[MKTEXPORTDEFINITIONOUTPUTFIELD]
            left outer join dbo.[QUERYVIEWCATALOG] [SQVC] on [SQVC].[ID] = [MKTEXPORTDEFINITIONOUTPUTFIELD].[QUERYVIEWCATALOGID]
            left outer join dbo.[QUERYVIEWCATALOG] as [QVC] on [QVC].[ID] = [MKTEXPORTDEFINITIONOUTPUTFIELD].[CUSTOMQUERYVIEWCATALOGID]
            --left outer join dbo.[TABLECATALOG] on ([TABLECATALOG].[TABLENAME] = [MKTEXPORTDEFINITIONOUTPUTFIELD].[QUERYFIELD] and [MKTEXPORTDEFINITIONOUTPUTFIELD].[TYPECODE] in (1, 3))

            --left outer join dbo.[SMARTFIELD] on [SMARTFIELD].[TABLECATALOGID] = [TABLECATALOG].[ID]

            left outer join [INFORMATION_SCHEMA].[COLUMNS] as [SCHEMAQV] on [SCHEMAQV].[TABLE_SCHEMA] = 'dbo' and [SCHEMAQV].[TABLE_NAME] = [QVC].[OBJECTNAME] and [SCHEMAQV].[COLUMN_NAME] = [MKTEXPORTDEFINITIONOUTPUTFIELD].[QUERYFIELD]
            --left outer join [INFORMATION_SCHEMA].[COLUMNS] as [SCHEMASF] on [SCHEMASF].[TABLE_SCHEMA] = 'dbo' and [SCHEMASF].[TABLE_NAME] = ('V_QUERY_' + [MKTEXPORTDEFINITIONOUTPUTFIELD].[QUERYFIELD]) and [SCHEMASF].[COLUMN_NAME] = (case [MKTEXPORTDEFINITIONOUTPUTFIELD].[TYPECODE] when 1 then [SMARTFIELD].[VALUECOLUMNNAME] when 3 then 'VALUEGROUP' else null end)

            left outer join @SPECIALFIELDS as [SPECIALFIELDS] on [SPECIALFIELDS].[QUERYVIEWCATALOGID] = [MKTEXPORTDEFINITIONOUTPUTFIELD].[QUERYVIEWCATALOGID] and [SPECIALFIELDS].[QUERYFIELD] = [MKTEXPORTDEFINITIONOUTPUTFIELD].[QUERYFIELD]
            left outer join @OUTPUTDEFINITIONXML.nodes('declare namespace QV="bb_appfx_queryview";/QueryViewOutput/QV:OutputFields/QV:OutputField') T(c) on T.c.value('(@Name)[1]','nvarchar(255)') = [MKTEXPORTDEFINITIONOUTPUTFIELD].[QUERYFIELD]
            --inner join dbo.[QUERYVIEWRELATIONSHIP] on [QUERYVIEWRELATIONSHIP].[ID] = [MKTEXPORTDEFINITIONOUTPUTFIELD].[CUSTOMQUERYVIEWRELATIONSHIPID]

          where [MKTEXPORTDEFINITIONOUTPUTFIELD].[EXPORTDEFINITIONID] = @EXPORTDEFINITIONID
            and [MKTEXPORTDEFINITIONOUTPUTFIELD].[QUERYVIEWCATALOGID] = @QUERYVIEWCATALOGID
            and [MKTEXPORTDEFINITIONOUTPUTFIELD].[CUSTOMQUERYVIEWCATALOGID] = @CUSTOMQUERYVIEWCATALOGID;
      end

    else if @QUERYVIEWCATALOGID is not null 
      begin
        select 
          @OUTPUTDEFINITIONXML = [QUERYVIEWCATALOG].[OUTPUTDEFINITIONXML] 
        from dbo.[QUERYVIEWCATALOG] 
        where [QUERYVIEWCATALOG].[ID] = @QUERYVIEWCATALOGID;

        --Insert non-custom query view output fields into the results table.

        insert into @RESULTS
          select
            @QUERYVIEWCATALOGID,
            [QVC].[DISPLAYNAME],
            null as [PATHALIAS],
            T.c.value('(@Category)[1]','nvarchar(255)'),
            [MKTEXPORTDEFINITIONOUTPUTFIELD].[NAME] as [HEADERNAME],
            coalesce([SCHEMAQV].[DATA_TYPE], [SPECIALFIELDS].[DATA_TYPE]) as [DATATYPE],
            (select min([S].[SEQUENCE]) from dbo.[MKTEXPORTDEFINITIONOUTPUTFIELD] as [S] where [S].[EXPORTDEFINITIONID] = [MKTEXPORTDEFINITIONOUTPUTFIELD].[EXPORTDEFINITIONID] and [S].[NAME] = [MKTEXPORTDEFINITIONOUTPUTFIELD].[NAME]) as [SEQUENCE],
            @MAILINGTYPECODE,
            isnull(T.c.value('(@IsHidden)[1]','nvarchar(5)'), 'false'),
            [MKTEXPORTDEFINITIONOUTPUTFIELD].[TYPECODE]
          from dbo.[MKTEXPORTDEFINITIONOUTPUTFIELD]
            left outer join dbo.[QUERYVIEWCATALOG] [QVC] on [QVC].[ID] = [MKTEXPORTDEFINITIONOUTPUTFIELD].[QUERYVIEWCATALOGID]
            --left outer join dbo.[TABLECATALOG] on ([TABLECATALOG].[TABLENAME] = [MKTEXPORTDEFINITIONOUTPUTFIELD].[QUERYFIELD] and [MKTEXPORTDEFINITIONOUTPUTFIELD].[TYPECODE] in (1, 3))

            --left outer join dbo.[SMARTFIELD] on [SMARTFIELD].[TABLECATALOGID] = [TABLECATALOG].[ID]

            left outer join [INFORMATION_SCHEMA].[COLUMNS] as [SCHEMAQV] on [SCHEMAQV].[TABLE_SCHEMA] = 'dbo' and [SCHEMAQV].[TABLE_NAME] = [QVC].[OBJECTNAME] and [SCHEMAQV].[COLUMN_NAME] = [MKTEXPORTDEFINITIONOUTPUTFIELD].[QUERYFIELD]
            --left outer join [INFORMATION_SCHEMA].[COLUMNS] as [SCHEMASF] on [SCHEMASF].[TABLE_SCHEMA] = 'dbo' and [SCHEMASF].[TABLE_NAME] = ('V_QUERY_' + [MKTEXPORTDEFINITIONOUTPUTFIELD].[QUERYFIELD]) and [SCHEMASF].[COLUMN_NAME] = (case [MKTEXPORTDEFINITIONOUTPUTFIELD].[TYPECODE] when 1 then [SMARTFIELD].[VALUECOLUMNNAME] when 3 then 'VALUEGROUP' else null end)

            left outer join @SPECIALFIELDS as [SPECIALFIELDS] on [SPECIALFIELDS].[QUERYVIEWCATALOGID] = [MKTEXPORTDEFINITIONOUTPUTFIELD].[QUERYVIEWCATALOGID] and [SPECIALFIELDS].[QUERYFIELD] = [MKTEXPORTDEFINITIONOUTPUTFIELD].[QUERYFIELD]
            left outer join @OUTPUTDEFINITIONXML.nodes('declare namespace QV="bb_appfx_queryview";/QueryViewOutput/QV:OutputFields/QV:OutputField') T(c) on T.c.value('(@Name)[1]','nvarchar(255)') = [MKTEXPORTDEFINITIONOUTPUTFIELD].[QUERYFIELD]
          where [MKTEXPORTDEFINITIONOUTPUTFIELD].[EXPORTDEFINITIONID] = @EXPORTDEFINITIONID 
            and [MKTEXPORTDEFINITIONOUTPUTFIELD].[QUERYVIEWCATALOGID] = @QUERYVIEWCATALOGID
            and [MKTEXPORTDEFINITIONOUTPUTFIELD].[CUSTOMQUERYVIEWCATALOGID] is null;   
      end

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

  close QUERYVIEWCURSOR;
  deallocate QUERYVIEWCURSOR;

  select
    [QUERYVIEWCATALOGID],
    [DISPLAYNAME],
    [PATHALIAS],
    [CATEGORY],
    [HEADERNAME],
    [DATATYPE],
    [SEQUENCE],
    [MAILINGTYPECODE],
    [FIELDISHIDDEN],
    [OUTPUTFIELDTYPECODE]
  from @RESULTS
  order by [SEQUENCE];

  return 0;