USP_SEARCHLIST_EXPORTDEFINITION

CREATE procedure dbo.[USP_SEARCHLIST_EXPORTDEFINITION]
(
  @NAME nvarchar(200) = null,
  @RECORDTYPEID uniqueidentifier = null,
  @RECORDTYPENAME nvarchar(100) = null,
  @CONSOLIDATEDRECEIPTS bit = 0,
  @CURRENTAPPUSERID uniqueidentifier,
  @MAXROWS smallint = 500,
  @MARKETING bit = 0,
  @HASVIEWDATAFORMTEMPLATE bit = 0
)
as
  set nocount on;

  set @NAME = isnull(@NAME, '') + '%';

  declare @ISSYSADMIN bit;

  select @ISSYSADMIN = ISSYSADMIN from dbo.APPUSER where ID = @CURRENTAPPUSERID;

  /* Filter out any export processes involving query views to which the current user does not have rights */
  if @ISSYSADMIN = 0 
    begin
      declare @OKVIEWS table (OBJECTNAME nvarchar(128));

      insert into @OKVIEWS (OBJECTNAME)
        select QUERYVIEWCATALOG.OBJECTNAME from dbo.QUERYVIEWCATALOG
        where exists (
          select [OKVIEWS].QUERYVIEWCATALOGID 
          from dbo.UFN_SECURITY_GETGRANTEDQUERYVIEWSFORUSER(@CURRENTAPPUSERID) as [OKVIEWS]
          where [OKVIEWS].QUERYVIEWCATALOGID = QUERYVIEWCATALOG.ID
        );
    end;

  select top(@MAXROWS)
    [EXPORTDEFINITION].[ID],
    [EXPORTDEFINITION].[NAME],
    [EXPORTDEFINITION].[DESCRIPTION],
    [RECORDTYPE].[NAME] as [TYPE]
  from 
    dbo.[EXPORTDEFINITION]
    inner join dbo.[RECORDTYPE] on [RECORDTYPE].[ID] = [EXPORTDEFINITION].[RECORDTYPEID]
  where
    ([EXPORTDEFINITION].[NAME] like @NAME)
    and (@RECORDTYPEID is null or [EXPORTDEFINITION].[RECORDTYPEID] = @RECORDTYPEID)
    and (@RECORDTYPENAME is null or [EXPORTDEFINITION].[RECORDTYPEID] = (select [ID] from dbo.[RECORDTYPE] where [NAME] = @RECORDTYPENAME))
    and (@CONSOLIDATEDRECEIPTS = 0 or [EXPORTDEFINITION].[RECORDTYPEID] in (select [ID] from dbo.[RECORDTYPE] where [NAME] = 'CONSTITUENT' or [NAME] = 'REVENUE'))
    and (@MARKETING = 0 and [EXPORTDEFINITION].[RECORDTYPEID] not in (select [ID] from dbo.[RECORDTYPE] where [NAME] in ('Direct Marketing Effort Segment Member Export', 'Marketing Acknowledgement Segment Member Export', 'Membership Effort Segment Member Export', 'Sponsorship Effort Segment Member Export')) or
         @MARKETING = 1 and [EXPORTDEFINITION].[RECORDTYPEID] in (select [ID] from dbo.[RECORDTYPE] where [NAME] in ('Direct Marketing Effort Segment Member Export', 'Marketing Acknowledgement Segment Member Export', 'Membership Effort Segment Member Export', 'Sponsorship Effort Segment Member Export')))
    and ([EXPORTDEFINITION].[ISSYSTEM] = 0)
    and
    (@ISSYSADMIN = 1 or
    /* Filter out restricted query views */
    /* This will return a list of query views in use by the ad-hoc query but not in the OK views table variable; 
       These export definitions should not be returned to the client. */

    not exists(select [QUERYVIEWSINUSE].OBJECTNAME 
      from dbo.EXPORTDEFINITION as [EXPORTDEFINITIONRIGHTSTEST]
      outer apply dbo.UFN_EXPORTDEFINITION_QUERYVIEWSINUSE(EXPORTDEFINITION.EXPORTDEFINITIONXML) as [QUERYVIEWSINUSE]
      where not exists (
        select [OKVIEWS].OBJECTNAME 
        from @OKVIEWS as [OKVIEWS] where [OKVIEWS].OBJECTNAME = [QUERYVIEWSINUSE].OBJECTNAME))
    /* End restricted query views filter */
    )
    and
    (
      EXPORTDEFINITION.SITEID is null 
      or
      dbo.UFN_SITEALLOWEDFORUSER(@CURRENTAPPUSERID, EXPORTDEFINITION.SITEID) = 1
    )
    and
    (
      @HASVIEWDATAFORMTEMPLATE = 0 or [EXPORTDEFINITION].[VIEWDATAFORMTEMPLATEID] is not null
    )
  order by [EXPORTDEFINITION].[NAME] asc;

  return 0;