===== Staff Reports =====
==== Staff with permission groups at one or more libraries ====
**Submission Notes:**
This report was submitted on 2019-10-04 by Dan Scott of Laurentian University. It was created by Dan Scott.
**Version**: Used on Evergreen 3.1
**Data Sources Used**: actor.org_unit, actor.usr, permission.grp_tree, permission.usr_work_ou_map
**Purpose**: -- List all people who have permissions, and their associated permissions, based on permission groups at one or more libraries.
**Running Tips/Guidelines**: There a few parameters you will want to set. Where it says 'ORG_UNIT_ID' inside the "WHERE work_ou_in ()" clause, put the org unit ID of the library that represents the working location where those staff have permissions. You can have one or more comma-delimited ORG_UNIT_ID parameters.
WITH x AS (
SELECT au.id AS usr, family_name, first_given_name, au.email, expire_date, aou.name
FROM actor.usr au
INNER JOIN permission.usr_work_ou_map ON usr = au.id
INNER JOIN actor.org_unit aou ON aou.id = work_ou
WHERE work_ou IN (ORG_UNIT_ID, ORG_UNIT_ID, ORG_UNIT_ID))
SELECT x.*, pgt.name AS group_name
FROM x
INNER JOIN permission.usr_grp_map pugm ON x.usr = pugm.usr
INNER JOIN permission.grp_tree pgt ON pgt.id = pugm.grp
ORDER BY family_name, first_given_name, x.name, group_name
;
==== Staff with individual permissions at one or more libraries ====
**Submission Notes:**
This report was submitted on 2019-10-04 by Dan Scott of Laurentian University. It was created by Dan Scott.
**Version**: Used on Evergreen 3.1
**Data Sources Used**: actor.org_unit, actor.usr, permission.grp_tree, permission.usr_work_ou_map
**Purpose**: -- List all people who have individually assigned permissions at one or more libraries.
**Running Tips/Guidelines**: There a few parameters you will want to set. Where it says 'ORG_UNIT_ID' inside the "WHERE work_ou_in ()" clause, put the org unit ID of the library that represents the working location where those staff have permissions. You can have one or more comma-delimited ORG_UNIT_ID parameters.
WITH x AS (
SELECT au.id AS usr, family_name, first_given_name, au.email, expire_date, aou.name
FROM actor.usr au
INNER JOIN permission.usr_work_ou_map ON usr = au.id
INNER JOIN actor.org_unit aou ON aou.id = work_ou
WHERE work_ou IN (ORG_UNIT_ID, ORG_UNIT_ID, ORG_UNIT_ID))
SELECT x.*, ppl.code AS perm_name
FROM x
INNER JOIN permission.usr_perm_map pupm ON x.usr = pupm.usr
INNER JOIN permission.perm_list ppl ON ppl.id = pupm.perm
ORDER BY family_name, first_given_name, x.name, ppl.code
;