===== 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.

<code sql>
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
;
</code>

==== 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.

<code sql>
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
;
</code>