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
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;
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;