UFN_SECURITY_APPUSER_GRANTED_BATCH

create function dbo.UFN_SECURITY_APPUSER_GRANTED_BATCH
(
    @BATCHID uniqueidentifier = null, 
    @BATCHTEMPLATEID uniqueidentifier = null,
    @BATCHWORKFLOWSTATEID uniqueidentifier = null
)
/*
Use this function to get the application users who have access to batch features.  The parameters can vary
based on the context of the security check. I.e. if you pass in NULLs, it will return app users who have
rights to batches in general.  Passing in parameters will validate they have specific rights.

It will check access in the following gorder.
1. Check if app user owns a specific batch via batch
2. Check if app user has rights to the batch template via batch id
3. check if app user has rights to the batch type via batch id
4. Check if app user has rights to the batch template via batch template id
5. check if app user has rights to the batch type via batch template id
6. check if app user has rights to the batch workflow state
if all params are null
7. check if app user has rights to any batch workflow state (if workflow state Id is null)
8. check if app user has rights to any batch template (if template Id is null)
9. check if app user has rights to any batch type (if batch Id is null)
10. check if app user is assigned to any batch
*/
returns table
return
    select
        APPUSER.ID
    from
        dbo.APPUSER
    where
        case
            when APPUSER.ISSYSADMIN = 1 then 1
            when @BATCHID is not null and exists (select null 
                from 
                    dbo.BATCH
                join 
                    dbo.BATCHWORKFLOWSTATUS on BATCH.ID = BATCHWORKFLOWSTATUS.BATCHID
                where 
                    (
                        (BATCHWORKFLOWSTATUS.APPUSERID = APPUSER.ID and BATCH.STATUSCODE = 1) or 
                        BATCH.APPUSERID = APPUSER.ID
                    ) and BATCH.ID = @BATCHID) then 1
            when @BATCHID is not null and (select top 1 GRANTORDENY
                from 
                    dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_BATCHTEMPLATE
                    with (NOEXPAND, INDEX(IX_V_SECURITY_SYSTEMROLEASSIGNMENT_USER_BATCHTEMPLATE_BATCHTEMPLATEID_APPUSERID))                                
                join 
                    dbo.BATCH on BATCH.BATCHTEMPLATEID = V_SECURITY_SYSTEMROLEASSIGNMENT_USER_BATCHTEMPLATE.BATCHTEMPLATEID
                where 
                    V_SECURITY_SYSTEMROLEASSIGNMENT_USER_BATCHTEMPLATE.APPUSERID = APPUSER.ID
                    and BATCH.ID = @BATCHID
                    and ((V_SECURITY_SYSTEMROLEASSIGNMENT_USER_BATCHTEMPLATE.SITEID is null) or (dbo.UFN_SITEALLOWEDFORUSER(APPUSER.ID, V_SECURITY_SYSTEMROLEASSIGNMENT_USER_BATCHTEMPLATE.SITEID) = 1))
                order by 
                    GRANTORDENY ASC) = 1 then 1
            when @BATCHID is not null and (select top 1 GRANTORDENY
                from 
                    dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_BATCHTYPE
                join 
                    dbo.BATCHTEMPLATE on BATCHTEMPLATE.BATCHTYPECATALOGID = V_SECURITY_SYSTEMROLEASSIGNMENT_USER_BATCHTYPE.BATCHTYPECATALOGID
                join 
                    dbo.BATCH on BATCH.BATCHTEMPLATEID = BATCHTEMPLATE.ID                                
                where 
                    V_SECURITY_SYSTEMROLEASSIGNMENT_USER_BATCHTYPE.APPUSERID = APPUSER.ID
                    and BATCH.ID = @BATCHID
                order by 
                    GRANTORDENY ASC) = 1 then 1
            when @BATCHTEMPLATEID is not null and (select top 1 GRANTORDENY
                from 
                    dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_BATCHTEMPLATE
                    with (NOEXPAND, INDEX(IX_V_SECURITY_SYSTEMROLEASSIGNMENT_USER_BATCHTEMPLATE_BATCHTEMPLATEID_APPUSERID))                                
                where 
                    V_SECURITY_SYSTEMROLEASSIGNMENT_USER_BATCHTEMPLATE.APPUSERID = APPUSER.ID
                    and V_SECURITY_SYSTEMROLEASSIGNMENT_USER_BATCHTEMPLATE.BATCHTEMPLATEID = @BATCHTEMPLATEID
                    and ((V_SECURITY_SYSTEMROLEASSIGNMENT_USER_BATCHTEMPLATE.SITEID is null) or (dbo.UFN_SITEALLOWEDFORUSER(APPUSER.ID, V_SECURITY_SYSTEMROLEASSIGNMENT_USER_BATCHTEMPLATE.SITEID) = 1))
                order by 
                    GRANTORDENY ASC) = 1 then 1
            when @BATCHTEMPLATEID is not null and (select top 1 GRANTORDENY
                from 
                    dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_BATCHTYPE
                join 
                    dbo.BATCHTEMPLATE on BATCHTEMPLATE.BATCHTYPECATALOGID = V_SECURITY_SYSTEMROLEASSIGNMENT_USER_BATCHTYPE.BATCHTYPECATALOGID
                where 
                    V_SECURITY_SYSTEMROLEASSIGNMENT_USER_BATCHTYPE.APPUSERID = APPUSER.ID
                    and BATCHTEMPLATE.ID = @BATCHTEMPLATEID
                order by 
                    GRANTORDENY ASC) = 1 then 1                    
            when @BATCHWORKFLOWSTATEID is not null and (select top 1 GRANTORDENY
                from 
                    dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_BATCHWORKFLOWSTATE                            
                    with (NOEXPAND, INDEX(IX_V_SECURITY_SYSTEMROLEASSIGNMENT_USER_BATCHWORKFLOWSTATE_BATCHWORKFLOWSTATEID_APPUSERID))                            
                where 
                    V_SECURITY_SYSTEMROLEASSIGNMENT_USER_BATCHWORKFLOWSTATE.APPUSERID = APPUSER.ID and
                    V_SECURITY_SYSTEMROLEASSIGNMENT_USER_BATCHWORKFLOWSTATE.BATCHWORKFLOWSTATEID = @BATCHWORKFLOWSTATEID
                order by GRANTORDENY ASC) = 1 then 1
            when @BATCHID is null and @BATCHTEMPLATEID is null and @BATCHWORKFLOWSTATEID is null then
                case
                    when (select top 1 GRANTORDENY
                        from 
                            dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_BATCHWORKFLOWSTATE
                            with (NOEXPAND, INDEX(IX_V_SECURITY_SYSTEMROLEASSIGNMENT_USER_BATCHWORKFLOWSTATE_BATCHWORKFLOWSTATEID_APPUSERID))
                        join
                            dbo.BATCHWORKFLOWSTATE on V_SECURITY_SYSTEMROLEASSIGNMENT_USER_BATCHWORKFLOWSTATE.BATCHWORKFLOWSTATEID = BATCHWORKFLOWSTATE.ID
                        where 
                            V_SECURITY_SYSTEMROLEASSIGNMENT_USER_BATCHWORKFLOWSTATE.APPUSERID = APPUSER.ID
                        order by GRANTORDENY ASC) = 1 then 1
                    when (select top 1 GRANTORDENY
                        from
                            dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_BATCHTEMPLATE
                            with (NOEXPAND, INDEX(IX_V_SECURITY_SYSTEMROLEASSIGNMENT_USER_BATCHTEMPLATE_BATCHTEMPLATEID_APPUSERID))
                        join
                            dbo.BATCHTEMPLATE on V_SECURITY_SYSTEMROLEASSIGNMENT_USER_BATCHTEMPLATE.BATCHTEMPLATEID = BATCHTEMPLATE.ID 
                        where 
                            V_SECURITY_SYSTEMROLEASSIGNMENT_USER_BATCHTEMPLATE.APPUSERID = APPUSER.ID
                            and ((V_SECURITY_SYSTEMROLEASSIGNMENT_USER_BATCHTEMPLATE.SITEID is null) or (dbo.UFN_SITEALLOWEDFORUSER(APPUSER.ID, V_SECURITY_SYSTEMROLEASSIGNMENT_USER_BATCHTEMPLATE.SITEID) = 1))
                        order by 
                            GRANTORDENY ASC) = 1 then 1
                    when (select top 1 GRANTORDENY
                        from 
                            dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_BATCHTYPE
                        where 
                            V_SECURITY_SYSTEMROLEASSIGNMENT_USER_BATCHTYPE.APPUSERID = APPUSER.ID
                        order by 
                            GRANTORDENY ASC) = 1 then 1                      
                    when exists (select null 
                        from 
                            dbo.BATCH
                        join 
                            dbo.BATCHWORKFLOWSTATUS on BATCH.ID = BATCHWORKFLOWSTATUS.BATCHID
                        where 
                            BATCH.STATUSCODE = 1 and
                            (
                                BATCHWORKFLOWSTATUS.APPUSERID = APPUSER.ID or 
                                BATCH.APPUSERID = APPUSER.ID
                            )) then 1
                    else 0
                end
            else 0
        end = 1