USP_DATALIST_EXPORTPROCESS

CREATE procedure dbo.USP_DATALIST_EXPORTPROCESS(
    @CURRENTAPPUSERID uniqueidentifier,
    @SITEFILTERMODE tinyint = 0,
    @SITESSELECTED xml = null,
    @SECURITYFEATUREID uniqueidentifier = null,
    @SECURITYFEATURETYPE tinyint = null
)
with execute as caller
as

    set nocount on;

    declare @ISSYSADMIN bit = 0;

    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 @HASALLQUERIESPRIVILEGE bit = dbo.UFN_SECURITY_APPUSER_GRANTED_SYSTEMPRIVILEGE_IN_SYSTEMROLE(@CURRENTAPPUSERID, '5f9bbcaf-9c67-4539-9880-ae4f709a9c1f');

        declare @OKVIEWS table (OBJECTNAME nvarchar(128));

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

    declare @SMARTCATALOGITEMS table (SMARTQUERYCATALOGID uniqueidentifier);

    insert into @SMARTCATALOGITEMS (SMARTQUERYCATALOGID)
    select SMARTQUERYCATALOG.ID from dbo.SMARTQUERYCATALOG
    where exists (
      select [SMARTCATALOGITEMS].SMARTQUERYCATALOGID
      from dbo.UFN_SECURITY_GETGRANTEDSMARTQUERIESFORUSER(@CURRENTAPPUSERID) as [SMARTCATALOGITEMS] where [SMARTCATALOGITEMS].SMARTQUERYCATALOGID = SMARTQUERYCATALOG.ID);
    end;

    select EXPORTPROCESS.ID,
        EXPORTPROCESS.NAME,
        EXPORTPROCESS.DESCRIPTION,
        case when ADHOCQUERY.NAME is null then case when SMARTQUERYINSTANCE.NAME is null then IDSETREGISTER.NAME else SMARTQUERYINSTANCE.NAME end else ADHOCQUERY.NAME end as [QUERYNAME],
        case when ADHOCQUERY.NAME is null then case when SMARTQUERYINSTANCE.NAME is null then 'Export Definition' else 'Smart Query Instance' end else 'Ad-hoc Query' end as [QUERYTYPE],        
        BPI.OWNERID as OWNERID,
        coalesce(SITE.NAME, 'All Sites') SITES
        ,EXPORTDEFINITION.NAME EXPORTDEFINITIONNAME
    from dbo.EXPORTPROCESS
    left join dbo.ADHOCQUERY on EXPORTPROCESS.ADHOCQUERYID = ADHOCQUERY.ID
    left join dbo.SMARTQUERYINSTANCE on EXPORTPROCESS.SMARTQUERYINSTANCEID = SMARTQUERYINSTANCE.ID
    left join dbo.BUSINESSPROCESSINSTANCE BPI on EXPORTPROCESS.ID = BPI.BUSINESSPROCESSPARAMETERSETID
    left join dbo.SITE on SITE.ID = BPI.SITEID
    left join dbo.EXPORTDEFINITION on EXPORTDEFINITION.ID = EXPORTPROCESS.EXPORTDEFINITIONID
    left join dbo.IDSETREGISTER on IDSETREGISTER.ID = EXPORTPROCESS.SELECTIONID
  left join dbo.SMARTQUERYCATALOG on SMARTQUERYINSTANCE.SMARTQUERYCATALOGID = SMARTQUERYCATALOG.ID
    where 
    --Start Security Check

    (@ISSYSADMIN = 1 
    or (((ADHOCQUERYID is not null and 
    /* 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 ad-hoc queries should not be returned to the client. */
        not exists(select [QUERYVIEWSINUSE].OBJECTNAME 
            from dbo.UFN_ADHOCQUERY_QUERYVIEWSINUSE(ADHOCQUERY.QUERYDEFINITIONXML) as [QUERYVIEWSINUSE]
            where not exists (
                select [OKVIEWS].OBJECTNAME 
                from @OKVIEWS as [OKVIEWS] where [OKVIEWS].OBJECTNAME = [QUERYVIEWSINUSE].OBJECTNAME))
    /* End restricted query views filter */
    )
    or (EXPORTDEFINITIONID is not null and
    /* 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.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 */
    )
  or (SMARTQUERYINSTANCEID is not null and
     /* Filter out restricted query views */    
        /* This will return a list of smart queries in use by the smart query but not in the SMARTCATALOGITEMS variable;
    These smart queries should not be returned to the client. */
    exists (select [SMARTCATALOGITEMS].SMARTQUERYCATALOGID 
        from @SMARTCATALOGITEMS as [SMARTCATALOGITEMS] 
        where [SMARTCATALOGITEMS].SMARTQUERYCATALOGID = SMARTQUERYCATALOG.ID)
  /* End restricted query views filter */
  )
    )
    and (dbo.UFN_SECURITY_APPUSER_GRANTED_BUSINESSPROCESSINSTANCE_IN_SYSTEMROLE(@CURRENTAPPUSERID, EXPORTPROCESS.ID) = 1)
    and(
            (
                (--Check site security

                    select count(*) 
                    from dbo.UFN_SITEID_MAPFROM_EXPORTPROCESSID(EXPORTPROCESS.ID) as EXPORTPROCESSSITE 
                    where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[EXPORTPROCESSSITE].[SITEID] or (SITEID is null and [EXPORTPROCESSSITE].[SITEID] is null)))
                ) > 0
            )
    )))
    --End Security Check

    and
    (--JamesWill WI242118 2012-11-13 Apply the user site filter in addition to doing security checks instead of as part of the security checks

        @SITEFILTERMODE = 0
            or BPI.SITEID in (
                select SITEID
                from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) 
            )
    )
    ;