USP_MKTEXPORTDEFINITIONOUTPUTFIELD_LOAD

CREATE procedure dbo.[USP_MKTEXPORTDEFINITIONOUTPUTFIELD_LOAD]
(
  @EXPORTDEFINITIONID uniqueidentifier,
  @SEGMENTATIONID uniqueidentifier = null,
  @APPUSERID uniqueidentifier
)
as
  set nocount on;

  declare @FIELDS table (
    [ID] uniqueidentifier,
    [NAME] nvarchar(255),
    [QUERYVIEWCATALOGID] uniqueidentifier,
    [CUSTOMQUERYVIEWCATALOGID] uniqueidentifier,
    [QUERYFIELD] nvarchar(255),
    [QUERYFIELDDISPLAYNAME] nvarchar(255),
    [TYPECODE] tinyint,
    [TYPE] nvarchar(20),
    [SEQUENCE] integer,
    [DATATYPE] nvarchar(128),
    [ISALLOWED] bit,
    [VIEWPATH] nvarchar(4000));

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

  declare @MAILINGTYPECODE tinyint;
  declare @ID uniqueidentifier;
  declare @NAME nvarchar(255);
  declare @QUERYVIEWCATALOGID uniqueidentifier;
  declare @CUSTOMQUERYVIEWCATALOGID uniqueidentifier;
  declare @QUERYFIELD nvarchar(255);
  declare @QUERYFIELDDISPLAYNAME nvarchar(255);
  declare @TYPECODE tinyint;
  declare @TYPE nvarchar(20);
  declare @SEQUENCE integer;
  declare @DATATYPE nvarchar(128);
  declare @VALID bit;
  declare @OUTPUTDEFINITIONXML xml;
  declare @ISALLOWED bit;
  declare @VIEWPATH nvarchar(4000);

  select
    @MAILINGTYPECODE = [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]();

      if not @BBECRECORDSOURCEID is null
        begin
          -- add the BBEC contact addressee as a special field

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

          if @MAILINGTYPECODE = 2 -- membership mailings

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

  declare FIELDS cursor local fast_forward for
    select 
      [E].[ID],
      [E].[NAME],
      (case when [E].[QUERYVIEWCATALOGID] = 'D67B643B-F31A-4A6A-88DD-5992DD670D26'
         then 'B453E917-6B30-4B7C-B83A-E3444B18A318'
         else [E].[QUERYVIEWCATALOGID]
       end) as [QUERYVIEWCATALOGID],
      [E].[CUSTOMQUERYVIEWCATALOGID],
      [E].[QUERYFIELD],
      isnull((case when [E].[CUSTOMQUERYVIEWCATALOGID] is not null
                then dbo.[UFN_QUERYVIEW_GETFIELDCAPTION]([E].[CUSTOMQUERYVIEWCATALOGID], [E].[QUERYFIELD])
                else dbo.[UFN_QUERYVIEW_GETFIELDCAPTION]([E].[QUERYVIEWCATALOGID], [E].[QUERYFIELD])
              end), [E].[NAME]) as [QUERYFIELDDISPLAYNAME],
      [E].[TYPECODE],
      [E].[TYPE],
      [E].[SEQUENCE],
      coalesce([SCHEMAQV].[DOMAIN_NAME], [SCHEMASF].[DOMAIN_NAME], [SCHEMAQV].[DATA_TYPE], [SCHEMASF].[DATA_TYPE], [SPECIALFIELDS].[DOMAIN_NAME], [SPECIALFIELDS].[DATA_TYPE]) as [DATATYPE],
      case when [CUSTOMQUERYVIEWCATALOGID] is not null
      then
        case when ([CUSTOMQUERYVIEWCATALOGID] in (select [OKVIEWS].[QUERYVIEWCATALOGID] from dbo.[UFN_SECURITY_GETGRANTEDQUERYVIEWSFORUSER](@APPUSERID) [OKVIEWS])
          or dbo.[UFN_APPUSER_ISSYSADMIN](@APPUSERID) = 1) then 1 else 0 end
        else 1 end as [ISALLOWED],
      [E].[VIEWPATH]
    from dbo.[MKTEXPORTDEFINITIONOUTPUTFIELD] as [E]
    left outer join dbo.[QUERYVIEWCATALOG] as [QVC] on [QVC].[ID] = isnull([E].[CUSTOMQUERYVIEWCATALOGID], [E].[QUERYVIEWCATALOGID])
    left outer join dbo.[TABLECATALOG] on ([TABLECATALOG].[TABLENAME] = [E].[QUERYFIELD] and [E].[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] = [E].[QUERYFIELD]
    left outer join [INFORMATION_SCHEMA].[COLUMNS] as [SCHEMASF] on [SCHEMASF].[TABLE_SCHEMA] = 'dbo' and [SCHEMASF].[TABLE_NAME] = ('V_QUERY_' + [E].[QUERYFIELD]) and [SCHEMASF].[COLUMN_NAME] = (case [E].[TYPECODE] when 1 then 'VALUE' when 3 then 'VALUEGROUP' else null end)
    left outer join @SPECIALFIELDS as [SPECIALFIELDS] on [SPECIALFIELDS].[QUERYVIEWCATALOGID] = [E].[QUERYVIEWCATALOGID] and [SPECIALFIELDS].[QUERYFIELD] = [E].[QUERYFIELD]
    where [E].[EXPORTDEFINITIONID] = @EXPORTDEFINITIONID
    and (@SEGMENTATIONID is null 
    or [E].[QUERYVIEWCATALOGID] in (select [ID] from dbo.[UFN_MKTEXPORTDEFINITION_GETSPECIALQUERYVIEWIDS]())
    or [E].[QUERYVIEWCATALOGID] in 
    (
      select
        case when @MAILINGTYPECODE = 0 -- Appeal

             then isnull([MKTCONSOLIDATEDQUERYVIEWSPEC].[CONSOLIDATEDQUERYVIEWCATALOGID], [RS].[QUERYVIEWCATALOGID])
             -- else is valid for Acknowledgement, Membership Mailings

             else [RS].[QUERYVIEWCATALOGID] 
        end
      from dbo.[UFN_MKTSEGMENTATION_GETDISTINCTRECORDSOURCES](@SEGMENTATIONID) [RS]
      left outer join dbo.[MKTCONSOLIDATEDQUERYVIEWSPEC] on [MKTCONSOLIDATEDQUERYVIEWSPEC].[ID] = [RS].[QUERYVIEWCATALOGID]
    )
    or (@MAILINGTYPECODE = 1 and [E].[QUERYVIEWCATALOGID] in 
    (
      select 
        [GRS].[QUERYVIEWCATALOGID]
      from dbo.[UFN_MKTSEGMENTATION_GETDISTINCTRECORDSOURCES](@SEGMENTATIONID) [RS]
      inner join dbo.[MKTGIFTRECORDSOURCE] [GRS] on [GRS].[ID] = [RS].[QUERYVIEWCATALOGID]
    ))
    or (@MAILINGTYPECODE = 2 and [E].[QUERYVIEWCATALOGID] in 
    (
      select 
        [MRS].[QUERYVIEWCATALOGID]
      from dbo.[UFN_MKTSEGMENTATION_GETDISTINCTRECORDSOURCES](@SEGMENTATIONID) [RS]
      inner join dbo.[MKTMEMBERSHIPRECORDSOURCE] [MRS] on [MRS].[ID] = [RS].[QUERYVIEWCATALOGID]
    ))
    or (@MAILINGTYPECODE = 3 and [E].[QUERYVIEWCATALOGID] in 
    (
      select 
        [MRS].[QUERYVIEWCATALOGID]
      from dbo.[UFN_MKTSEGMENTATION_GETDISTINCTRECORDSOURCES](@SEGMENTATIONID) [RS]
      inner join dbo.[MKTSPONSORSHIPRECORDSOURCE] [MRS] on [MRS].[ID] = [RS].[QUERYVIEWCATALOGID]
    )))
    order by (select min([F].[SEQUENCE]) from dbo.[MKTEXPORTDEFINITIONOUTPUTFIELD] as [F] where [F].[EXPORTDEFINITIONID] = @EXPORTDEFINITIONID and [F].[NAME] = [E].[NAME]), [SEQUENCE];

  open FIELDS;
  fetch next from FIELDS into @ID, @NAME, @QUERYVIEWCATALOGID, @CUSTOMQUERYVIEWCATALOGID, @QUERYFIELD, @QUERYFIELDDISPLAYNAME, @TYPECODE, @TYPE, @SEQUENCE, @DATATYPE, @ISALLOWED, @VIEWPATH;

  while (@@FETCH_STATUS = 0)
    begin
      set @VALID = 1;
      if @TYPECODE = 1 or @TYPECODE = 3 -- smart field

        set @VALID = (case when exists(select top 1 1 from INFORMATION_SCHEMA.TABLES where [TABLE_SCHEMA] = 'dbo' and [TABLE_NAME] = @QUERYFIELD)
                            and exists(select top 1 1
                                       from dbo.[TABLECATALOG]
                                       inner join dbo.[SMARTFIELD] on [SMARTFIELD].[TABLECATALOGID] = [TABLECATALOG].[ID]
                                       where [TABLECATALOG].[TABLENAME] = @QUERYFIELD)
                      then 1 else 0 end);
      else
        if @TYPECODE = 2                -- custom query view field

          begin
            select
              @OUTPUTDEFINITIONXML = [OUTPUTDEFINITIONXML]
            from dbo.[QUERYVIEWCATALOG]
            where [ID] = @CUSTOMQUERYVIEWCATALOGID;

            if @OUTPUTDEFINITIONXML is not null
              set @VALID = (case when exists(select top 1 1 
                                             from @OUTPUTDEFINITIONXML.nodes('declare namespace QV="bb_appfx_queryview";/QueryViewOutput/QV:OutputFields/QV:OutputField') T(c)
                                             where T.c.value('(@Name)[1]','nvarchar(255)') = @QUERYFIELD)
                            then 1 else 0 end);
            else
              set @VALID = 0;
          end;

      if @VALID = 1 
        insert into @FIELDS values (@ID, @NAME, @QUERYVIEWCATALOGID, @CUSTOMQUERYVIEWCATALOGID, @QUERYFIELD, @QUERYFIELDDISPLAYNAME, @TYPECODE, @TYPE, @SEQUENCE, @DATATYPE, @ISALLOWED, @VIEWPATH);

      fetch next from FIELDS into @ID, @NAME, @QUERYVIEWCATALOGID, @CUSTOMQUERYVIEWCATALOGID, @QUERYFIELD, @QUERYFIELDDISPLAYNAME, @TYPECODE, @TYPE, @SEQUENCE, @DATATYPE, @ISALLOWED, @VIEWPATH;
    end;

  close FIELDS;
  deallocate FIELDS;

  select
    [ID],
    [NAME],
    [QUERYVIEWCATALOGID],
    [CUSTOMQUERYVIEWCATALOGID],
    [QUERYFIELD],
    [QUERYFIELDDISPLAYNAME],
    [TYPECODE],
    [TYPE],
    [SEQUENCE],
    [DATATYPE],
    [ISALLOWED],
    [VIEWPATH]
  from @FIELDS
  order by [SEQUENCE];

  return 0;