Today, we need reports of all active users for our applications. Here, I whip up SQL to retrieve this data in Voyager, Drupal, and ILLiad.
I find most interesting that each application has a unique approach to staff accounts. Both Voyager and ILLiad have a distinct table for its patrons and staff. Drupal makes no distinction. This makes sense in terms of design intentions. Voyager and ILLiad are products with an obvious distinction between customer and employee. Drupal can implement the same effect, but comes as a more generic tool.
My least favorite name for a staff account is Voyager's use of "operator". It sounds as if we should be handling heavy equipment.
This will list all the operators with their assigned circulation, acquisitions, and cataloging profile. Notice I use left joins in order to be sure all operators appear whether or not they happen to have profiles for any of the three modules.
SELECT
first_name
, last_name
, circ_profile_name
, acq_profile_name
, cat_profile_name
FROM
operator
left join circ_operator using(operator_id)
left join circ_profile using(circ_profile_id)
left join acq_operator using(operator_id)
left join acq_profile using(acq_profile_id)
left join cat_operator using(operator_id)
left join cat_profile using(cat_profile_id)
ORDER BY
2, 1 ASC
Drupal users can have multiple roles. Thus, I grouped by username and then concatenated all the roles with a comma. If you have more structured roles (e.g. if their permissions could be isolated to particular modules like the Circ, Acq, and Cat modules in Voyager), you could make this more elegant. We don't do such things in Drupal, so I don't bother.
SELECT
users.name as Username
, group_concat(role.name ORDER BY role.name ASC SEPARATOR ", ") as Roles
FROM
users
LEFT JOIN users_roles USING(uid)
LEFT JOIN role USING(rid)
WHERE
status > 0
GROUP BY
uid
ORDER BY
users.name ASC
LIMIT 0, 500
Lastly, the ILLiad staff accounts. Simple and easy.
SELECT
dbo_Staff.Username
, dbo_Staff.StaffFirstName
, dbo_Staff.StaffLastName
, dbo_Staff.UserGroup
FROM
dbo_Staff;