UFN_QUERY_SMARTFIELD_LISTBUILDER

CREATE function dbo.[UFN_QUERY_SMARTFIELD_LISTBUILDER]
  (
    @SITEFILTERMODE tinyint = 0,
    @SITESSELECTED xml = null,
    @CURRENTAPPUSERID uniqueidentifier = null,
    @SECURITYFEATUREID uniqueidentifier = null,
    @SECURITYFEATURETYPE tinyint = null
  )

  returns @SMARTFIELDS table 
  (
    [ID] uniqueidentifier,
    [SMARTFIELDNAME] nvarchar(100),
    [LASTRUNON] datetime,
    [RECORDTYPEID] uniqueidentifier,
    [RECORDTYPE] nvarchar(50),
    [SMARTFIELDTYPE] nvarchar(255),
    [DATATYPE] nvarchar(50),
    [SMARTFIELDPROCEDUREID] uniqueidentifier,
    [SMARTFIELDDATAFORMITEM] xml,
    [SYSTEMFIELD] bit,
    [SITES] nvarchar(255),
    [SMARTFIELDDESCRIPTION] nvarchar(4000),
    [LASTRUNDURATION] time,
    [LASTRUNBY_USERNAME] nvarchar(255),
    [LASTRUNRESULTCOUNT] int,
    [LASTRUNSTATUS] nvarchar(25),
    [INCLUDEDINWAREHOUSE] bit,
    [ELIGIBLEFORWAREHOUSE] bit,
    [SMARTFIELDCATEGORYCODEID] uniqueidentifier,
    [SMARTFIELDCATEGORY] nvarchar(255),
    [CREATEDBY_USERNAME] nvarchar(255),
    [CREATEDON] datetime,
    [SHOWNINFUNDRAISERONTHEGO] bit
  )

  with execute as caller

  as

  begin

    insert into @SMARTFIELDS (
      [ID], 
      [SMARTFIELDNAME], 
      [LASTRUNON], 
      [RECORDTYPEID], 
      [RECORDTYPE], 
      [SMARTFIELDTYPE], 
      [DATATYPE], 
      [SMARTFIELDPROCEDUREID], 
      [SMARTFIELDDATAFORMITEM], 
      [SYSTEMFIELD], 
      [SITES], 
      [SMARTFIELDDESCRIPTION], 
      [LASTRUNDURATION], 
      [LASTRUNBY_USERNAME], 
      [LASTRUNRESULTCOUNT], 
      [LASTRUNSTATUS],
      [INCLUDEDINWAREHOUSE],
      [ELIGIBLEFORWAREHOUSE],
      [SMARTFIELDCATEGORYCODEID],
      [SMARTFIELDCATEGORY],
      [CREATEDBY_USERNAME],
      [CREATEDON],
      [SHOWNINFUNDRAISERONTHEGO]
    )
    select 
      [SMARTFIELD].[ID],
      [SMARTFIELD].[NAME],
      [SMARTFIELD].[LASTRUNON],
      [RECORDTYPE].[ID] as [RECORDTYPEID],
      [RECORDTYPE].[NAME],
      [V_INSTALLED_SMARTFIELDCATALOG].[DISPLAYNAME] as [SMARTFIELDTYPE],
      [V_INSTALLED_SMARTFIELDCATALOG].[DATATYPE],
      [V_INSTALLED_SMARTFIELDCATALOG].[ID],
      coalesce([SMARTFIELD].[SMARTFIELDDATAFORMITEM], ''),
      [SMARTFIELD].[SYSTEMFIELD],
      case 
      when [SMARTFIELD].[SITEFILTERENABLEDFORINSTANCE] = 1 
        then (select 
            substring(dbo.[UDA_BUILDLIST]([SITE].[NAME]), 0, 255) as [SITES]
            from dbo.[SMARTFIELDSITE]
            inner join dbo.[SITE] on [SMARTFIELDSITE].[SITEID] = [SITE].[ID]
            where [SMARTFIELDSITE].[SMARTFIELDID] = [SMARTFIELD].[ID])
      else ''
      end as [SITES],
      [SMARTFIELD].[DESCRIPTION],
      cast([BPS].[ENDEDON] - [BPS].[STARTEDON] as time) as [LASTRUNDURATION],
      case when [STARTEDBY].[DISPLAYNAME] = '' then [STARTEDBY].[USERNAME] else [STARTEDBY].[DISPLAYNAME] end  as [LASTRUNBY_USERNAME],
      [BPS].[NUMBERPROCESSED] as [LASTRUNRESULTCOUNT],
      isnull([BPS].[STATUS], 'Not started') as [LASTRUNSTATUS],
      case             
        when [OLAPDATASOURCESMARTFIELD].[ID] is null then 0 
        else 1 
      end [INCLUDEDINWAREHOUSE],
      case 
        when [RECORDTYPE].[NAME] in ('Constituent','Financial Transaction Line Item')
          then 1
        else 0
      end [ELIGIBLEFORWAREHOUSE],         
      [SMARTFIELD].[SMARTFIELDCATEGORYCODEID], 
      [SMARTFIELDCATEGORYCODE].[DESCRIPTION] as [SMARTFIELDCATEGORY],
      [CREATEDBY].[USERNAME] as [CREATEDBY_USERNAME],
      [SMARTFIELD].[DATEADDED] [CREATEDON],
      [SMARTFIELD].[SHOWINFUNDRAISERONTHEGO] as [SHOWNINFUNDRAISERONTHEGO]
    from dbo.[SMARTFIELD]
    inner join dbo.[V_INSTALLED_SMARTFIELDCATALOG] on [V_INSTALLED_SMARTFIELDCATALOG].[ID] = [SMARTFIELD].[SMARTFIELDCATALOGID]
    inner join dbo.[RECORDTYPE] on [RECORDTYPE].[ID] = [V_INSTALLED_SMARTFIELDCATALOG].[RECORDTYPEID]
    left join dbo.[OLAPDATASOURCESMARTFIELD] on [OLAPDATASOURCESMARTFIELD].[SMARTFIELDID] = [SMARTFIELD].[ID] and [OLAPDATASOURCESMARTFIELD].[OLAPDATASOURCEID] = '75A36279-8531-4A5F-AFD6-483FE5D26037' --BBDW

    outer apply (
        select
          top 1
          [STARTEDON], 
          [STATUS], 
          [STARTEDBYUSERID],
          [ENDEDON],
          [NUMBERPROCESSED],
          [BUSINESSPROCESSPARAMETERSETID]
        from dbo.[BUSINESSPROCESSSTATUS] 
        where [BUSINESSPROCESSCATALOGID] = '2505C856-DEB9-415E-BD7C-D2FED90622F5' and [BUSINESSPROCESSPARAMETERSETID] = [SMARTFIELD].[ID]
        order by [STARTEDON] desc
      ) as [BPS]
    left join dbo.[APPUSER] [STARTEDBY] on [STARTEDBY].[ID] = [BPS].[STARTEDBYUSERID]
    left join dbo.[CHANGEAGENT] [CREATEDBY] on [CREATEDBY].[ID] = [SMARTFIELD].[ADDEDBYID]
    left join dbo.[SMARTFIELDCATEGORYCODE] on [SMARTFIELD].[SMARTFIELDCATEGORYCODEID] = [SMARTFIELDCATEGORYCODE].[ID]
    where exists (select top 1 1
          from dbo.[UFN_SITEID_MAPFROM_SMARTFIELDID]([SMARTFIELD].[ID]) as [SMARTFIELDSITE]
          where 
          -- Don't remove smart fields because of site if they don't have site filtering.

          dbo.[V_INSTALLED_SMARTFIELDCATALOG].[HASSITEFILTER] = 0 or
          dbo.[UFN_APPUSER_ISSYSADMIN](@CURRENTAPPUSERID) = 1 or 
          exists (select 1 from dbo.[UFN_SITESFORUSERONFEATURE](@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where [SITEID] = [SMARTFIELDSITE].[SITEID] or ([SITEID] is null and [SMARTFIELDSITE].[SITEID] is null))
          )
    and (coalesce(@SITEFILTERMODE, 0) = 0 or
          exists (select top 1 1
                  from dbo.[SMARTFIELDSITE]
                  inner join dbo.[UFN_SITE_BUILDDATALISTSITEFILTER] (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) as [VALIDSITE] on [SMARTFIELDSITE].[SITEID] = [VALIDSITE].[SITEID]
                  where [SMARTFIELDSITE].[SMARTFIELDID] = [SMARTFIELD].[ID]))
    order by [SMARTFIELD].[NAME], [RECORDTYPE].[NAME];

    return;

  end