UFN_SECURITY_APPUSER_GRANTED_CONSTITIDS_FORDATALIST

create function BBDW.[UFN_SECURITY_APPUSER_GRANTED_CONSTITIDS_FORDATALIST]

(
    @APPUSERID uniqueidentifier,
    @DATALISTCATALOGID uniqueidentifier
)

returns table as


/*

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

This function is optimized for use from the Blackbaud.AppFx.Security.Catalog.ConstitRecordSecurityService
class which implements the RecordSecurity service for Constituent record security.

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_DATALIST_IN_NONRACROLE 
occurs outside this function. If that function returns true there is no need to join to this TVF.

*/

return

(                    
    select 
        CSAA.[CONSTITUENTSYSTEMID] as [ID]
    from 
        BBDW.[FACT_CONSTITUENTSECURITYGROUPASSIGNMENT] as CSAA
    where
        CSAA.[CONSTITUENTSECURITYGROUPDIMID] in 
            (
                select 
                    [FACT_SYSTEMROLEAPPUSERCONSTITUENTSECURITY].[CONSTITUENTSECURITYGROUPDIMID]
                from 
                    BBDW.[v_SECURITY_SYSTEMROLEASSIGNMENT_USER_DATALIST] as SV
                    inner join BBDW.[FACT_SYSTEMROLEAPPUSER] on [FACT_SYSTEMROLEAPPUSER].[APPUSERDIMID] = SV.[APPUSERDIMID]
                    inner join BBDW.[FACT_SYSTEMROLEAPPUSERCONSTITUENTSECURITY] on [FACT_SYSTEMROLEAPPUSERCONSTITUENTSECURITY].[SYSTEMROLEAPPUSERFACTID] = [FACT_SYSTEMROLEAPPUSER].[SYSTEMROLEAPPUSERFACTID]
                where
                    SV.APPUSERID = @APPUSERID and 
                    SV.DATALISTCATALOGID = @DATALISTCATALOGID and 
                    SV.GRANTORDENY = 1 and
                    [FACT_SYSTEMROLEAPPUSER].[APPUSERSYSTEMID] = @APPUSERID and
                    [FACT_SYSTEMROLEAPPUSER].[CONSTITUENTSECURITYMODE] = 2 
            )                        

    union all

    --Constits with no security attributes if the user in a role with security mode = 1

    select 
        [CONSTITUENTSYSTEMID] as [ID]
    from 
        BBDW.[DIM_CONSTITUENT]
    where 
        exists
            (select 
                1
            from 
                BBDW.[v_SECURITY_SYSTEMROLEASSIGNMENT_USER_DATALIST] as SV
            where
                SV.[APPUSERID] = @APPUSERID and 
                SV.[DATALISTCATALOGID] = @DATALISTCATALOGID and 
                SV.[GRANTORDENY] = 1 and 
                SV.[RECORDSECURITYMODE] = 1
            )                
    and    [CONSTITUENTDIMID] not in
            (
              select 
                  [FACT_CONSTITUENTSECURITYGROUPASSIGNMENT].[CONSTITUENTDIMID] 
              from 
                BBDW.[FACT_CONSTITUENTSECURITYGROUPASSIGNMENT]
            )
)