UFN_SECURITY_APPUSER_GRANTED_DATALIST_FORCONSTIT

create function BBDW.[UFN_SECURITY_APPUSER_GRANTED_DATALIST_FORCONSTIT]
  (
      @APPUSERID uniqueidentifier,
      @DATALISTID uniqueidentifier,
      @CONSTITUENTID uniqueidentifier
  )

returns bit as

/*

Returns true if the given user has permissions to the given list
in a role whose security group is either

1.) Blank and record security mode=0
2.) Assigned to the given Constituent.
3.) Blank and record security mode=1 and the constit has no security groups

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.  

*/

begin

--If user granted permission to the feature in a role with no ringfence then 
--the user has permission regardless of the record in question.

if exists
    (
        select 
            1 
        from 
            BBDW.[v_SECURITY_SYSTEMROLEASSIGNMENT_USER_DATALIST] as SV
        where 
            SV.[APPUSERID] = @APPUSERID and 
            SV.[DATALISTCATALOGID] = @DATALISTID and 
            SV.[GRANTORDENY] = 1 and 
            SV.[RECORDSECURITYMODE] = 0
    )

    return 1;

if exists

    (
        select 
            1 
        from 
            BBDW.[v_SECURITY_SYSTEMROLEASSIGNMENT_USER_DATALIST] as SV
        where 
            SV.[APPUSERID] = @APPUSERID and 
          SV.[DATALISTCATALOGID] = @DATALISTID and 
            SV.[GRANTORDENY] = 1 and 
            SV.[RECORDSECURITYMODE] = 1
    )

      begin
    --check if constit has no security attributes

        if not exists(
                    select 
                        1 
                    from 
              BBDW.[FACT_CONSTITUENTSECURITYGROUPASSIGNMENT] csa
                    where 
                        csa.[CONSTITUENTSYSTEMID] = @CONSTITUENTID)

        return 1;
      end


--Next check if constit has any of the ringfences 
--that the user has permissions to the features in the role of

if exists(

            select 
                1 
            from 
                BBDW.[FACT_CONSTITUENTSECURITYGROUPASSIGNMENT] as csa
            where 
                csa.[CONSTITUENTSYSTEMID] = @CONSTITUENTID and    
        csa.[CONSTITUENTSECURITYGROUPDIMID] in
                (
                    select 
            cs.[CONSTITUENTSECURITYGROUPDIMID]
                    from 
                        BBDW.[v_SECURITY_SYSTEMROLEASSIGNMENT_USER_DATALIST] as sv
            inner join BBDW.[FACT_SYSTEMROLEAPPUSERCONSTITUENTSECURITY]  cs on cs.[SYSTEMROLEAPPUSERFACTID] = sv.[SYSTEMROLEAPPUSERFACTID]

                    where
                        sv.APPUSERID = @APPUSERID and 
                        sv.RECORDSECURITYMODE = 2 and
                        sv.DATALISTCATALOGID = @DATALISTID and 
                        sv.GRANTORDENY = 1
                )
        )

  return 1;

--If neither granted in role with no ringfence or not granted in a ringfence applied to the constit
--then return false        

return  0;

end