UFN_SECURITY_APPUSER_GRANTED_CONSTITIDS_FORFORM

create function BBDW.[UFN_SECURITY_APPUSER_GRANTED_CONSTITIDS_FORFORM]
(
  @APPUSERID uniqueidentifier,
  @DATAFORMINSTANCEID uniqueidentifier
)
returns TABLE as

/*
Returns a row for every constituent that the dataform 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_DATAFORM_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_FORM] 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.[DATAFORMINSTANCECATALOGID] = @DATAFORMINSTANCEID 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_FORM] as SV
      where
        SV.[APPUSERID] = @APPUSERID and 
        SV.[DATAFORMINSTANCECATALOGID] = @DATAFORMINSTANCEID and 
        SV.[GRANTORDENY] = 1 and 
        SV.[RECORDSECURITYMODE] = 1
      )                
    and    [CONSTITUENTDIMID] not in
      (
        select 
          [FACT_CONSTITUENTSECURITYGROUPASSIGNMENT].[CONSTITUENTDIMID] 
        from 
        BBDW.[FACT_CONSTITUENTSECURITYGROUPASSIGNMENT]
      )
);