UFN_SECURITY_APPUSER_GRANTED_CONSTITIDS_FORSMARTQUERY_BYSITE

create function BBDW.[UFN_SECURITY_APPUSER_GRANTED_CONSTITIDS_FORSMARTQUERY_BYSITE]
(
    @APPUSERID uniqueidentifier,
    @SMARTQUERYCATALOGID uniqueidentifier
)
returns table as

/*
Returns a row for every constituent that the user has rights to according to site access security.


This function is optimized for use from within a smart query.
which respects the constituent security policy that has been configured.

As such, it assumes that a check for DENY occurs outside this function
and also assumes that a check for if the user is ISSYSADMIN occurs outside 
this function.  

It also assumes a check for UFN_SECURITY_APPUSER_GRANTED_SMARTQUERY_IN_NONSITEROLE
occurs outside this function. If that function returns true there is no need to join to this TVF.

*/

return

(
    with [SECURITY_CTE] as (
        select distinct
            sv.[SITESECURITYMODE], sp.[SYSTEMROLEDIMID]
        from 
            BBDW.[V_SECURITY_SYSTEMROLEASSIGNMENT_USER_SMARTQUERY] as sv
            left join BBDW.[FACT_SITEPERMISSION] as sp on sp.[APPUSERDIMID] = sv.[APPUSERDIMID] and sp.[SYSTEMROLEDIMID] = sv.[SYSTEMROLEDIMID]
        where 
            sv.[APPUSERID] = @APPUSERID and 
            sv.[SMARTQUERYCATALOGID] = @SMARTQUERYCATALOGID and 
            sv.[GRANTORDENY] = 1
    )
    select
    --Check if the user has permissions to records with no site and for constituents that have no site assignments
        c.[CONSTITUENTSYSTEMID] as [ID]
    from
        BBDW.[DIM_CONSTITUENT] c
        cross join (select distinct 1 as [SECURE] from [SECURITY_CTE] where SITESECURITYMODE = 1) as [ISSECURE]
    where
        not exists (select [CONSTITUENTSITEFACTID] from BBDW.[FACT_CONSTITUENTSITE] where [FACT_CONSTITUENTSITE].[CONSTITUENTDIMID] = c.[CONSTITUENTDIMID])

    union

    select
    --Next check for constituents that have any of the sites that the user has been granted in roles that have been granted this feature
        cs.[CONSTITUENTSYSTEMID] as [ID]
    from 
        BBDW.[FACT_CONSTITUENTSITE] cs
        cross join (select distinct 1 as [SECURE] from [SECURITY_CTE] where [SITESECURITYMODE] <> 0 and [SITESECURITYMODE] <> 1) [ISSECURE]
        inner join BBDW.[FACT_SITEPERMISSION] sp on sp.[SITEDIMID] = cs.[SITEDIMID]
    inner join BBDW.[DIM_APPUSER] a on sp.[APPUSERDIMID] = a.[APPUSERDIMID] and a.[APPUSERSYSTEMID] = @APPUSERID
        inner join [SECURITY_CTE] on [SECURITY_CTE].[SYSTEMROLEDIMID] = sp.[SYSTEMROLEDIMID]
);