USP_SIMPLEDATALIST_BATCHWORKFLOWSTATEAVAILABLEUSERS

CREATE procedure dbo.USP_SIMPLEDATALIST_BATCHWORKFLOWSTATEAVAILABLEUSERS(@BATCHTEMPLATEID uniqueidentifier = null)
as begin

    declare @SITEID uniqueidentifier;
    select @SITEID = SITEID from dbo.BATCHTEMPLATE where ID = @BATCHTEMPLATEID;

    -- Moving this out of the SP provides significant performance improvements, but slightly changes

    -- functionality by only pulling the "first" initial workflow state. There should only be one 

    -- initial state, but the architecture does not expressly prevent this. So, it's possible to 

    -- have two initial states with different permissions. This would likely cause issues elsewhere.

    -- Order by date added to ensure consistent functionality.

    declare @BATCHWORKFLOWINITIALSTATEID uniqueidentifier = (
        select top 1 BATCHWORKFLOWSTATE.ID
        from 
            dbo.BATCHWORKFLOWSTATE
            inner join dbo.BATCHWORKFLOW on BATCHWORKFLOW.ID = BATCHWORKFLOWSTATE.BATCHWORKFLOWID
            inner join dbo.BATCHTEMPLATE on BATCHTEMPLATE.BATCHWORKFLOWID = BATCHWORKFLOW.ID
        where
            BATCHWORKFLOWSTATE.ISINITIALSTATE = 1 and
            BATCHTEMPLATE.ID = @BATCHTEMPLATEID
        order by
            BATCHWORKFLOWSTATE.DATEADDED

    );

    if @BATCHTEMPLATEID is null
        -- BATCHTEMPLATEID should only be null if one has not been selected in the add form. 

        -- This makes the owner drop down somewhat meaningless, but the old functionality 

        -- returned sys admins. So keep it consistent for the optimization while reducing work.

        select ID as VALUE, case when DISPLAYNAME = '' then USERNAME else DISPLAYNAME end as LABEL 
        from dbo.APPUSER                    
        where 
            APPUSER.ISSYSADMIN = 1
        order by LABEL
    else if @SITEID is null
        select ID as VALUE, case when DISPLAYNAME = '' then USERNAME else DISPLAYNAME end as LABEL 
        from dbo.APPUSER                    
        where 
            APPUSER.ISSYSADMIN = 1
            or dbo.UFN_SECURITY_APPUSER_GRANTED_BATCHTEMPLATE(APPUSER.ID, @BATCHTEMPLATEID) = 1    
            or dbo.UFN_SECURITY_APPUSER_GRANTED_BATCHWORKFLOWSTATE(APPUSER.ID, @BATCHWORKFLOWINITIALSTATEID) = 1
        order by LABEL
    else
        select ID as VALUE, case when DISPLAYNAME = '' then USERNAME else DISPLAYNAME end as LABEL 
        from dbo.APPUSER                    
        where 
            APPUSER.ISSYSADMIN = 1
            or (
                (
                    dbo.UFN_SECURITY_APPUSER_GRANTED_BATCHTEMPLATE(APPUSER.ID, @BATCHTEMPLATEID) = 1    
                    or dbo.UFN_SECURITY_APPUSER_GRANTED_BATCHWORKFLOWSTATE(APPUSER.ID, @BATCHWORKFLOWINITIALSTATEID) = 1    
                )
                and exists(
                    select
                        1
                    from
                        dbo.SYSTEMROLEAPPUSER
                        left join dbo.SITEPERMISSION on SITEPERMISSION.SYSTEMROLEAPPUSERID = SYSTEMROLEAPPUSER.ID 
                    where
                        SYSTEMROLEAPPUSER.APPUSERID = APPUSER.ID
                        and (
                            (SITEPERMISSION.SITEID = @SITEID)
                            or (SYSTEMROLEAPPUSER.SECURITYMODECODE = 0)
                        )
                )

            )
        order by LABEL

end