Need to pull list of users by role from backend script

OpenEMR Development Data Analysis
1

All,
Version: 7.0.3 DB: MySQL
I am pulling data from my OPEN EMR instance, and need to pull users by their role. Can someone share the query please

Just to give more clrity for my questions, how do I get list of Physicians, Practitioners, front office managers, front office users, admins, etc
Regards

2 
SELECT 
    u.id AS user_id,
    u.username,
    u.fname AS first_name,
    u.lname AS last_name,
    u.active,
    u.see_auth,
    u.npi,
    u.federaldrugid,
    g.name AS role_name,
    g.id AS role_id
FROM 
    users u
LEFT JOIN 
    gacl_aro aro ON u.username = aro.value
LEFT JOIN 
    gacl_groups_aro_map gam ON aro.id = gam.aro_id
LEFT JOIN 
    gacl_aro_groups g ON gam.group_id = g.id
ORDER BY 
    u.lname, u.fname;

You can edit the columns.
Regards,
Luis

3

If you want Facilities

SELECT 
    u.id AS user_id,
    u.username,
    u.fname AS first_name,
    u.lname AS last_name,
    u.active,
    u.see_auth,
    u.npi,
    u.federaldrugid,
    g.name AS role_name,
    g.id AS role_id,
    f.name AS facility_name,
    f.id AS facility_id
FROM 
    users u
LEFT JOIN 
    gacl_aro aro ON u.username = aro.value
LEFT JOIN 
    gacl_groups_aro_map gam ON aro.id = gam.aro_id
LEFT JOIN 
    gacl_aro_groups g ON gam.group_id = g.id
LEFT JOIN 
    facility f ON u.facility_id = f.id
WHERE u.active = '1'
ORDER BY 
    u.lname, u.fname;
4

Thank you, Luis. Appreciate your response

1 Like