Active Patron Count

drop table if exists rogan.active_sip;
drop table if exists rogan.active_circ;
drop table if exists rogan.active_expire;
drop table if exists rogan.active_usrs;

create table rogan.active_sip as
SELECT au.id, au.home_ou, MAX(aua.event_time) AS TIME
FROM actor.usr au
JOIN actor.usr_activity aua ON aua.usr = au.id
join actor.org_unit aou on aou.id = au.home_ou
WHERE aou.shortname ilike 'dcl%'
GROUP BY 1, 2
;

create table rogan.active_circ as
SELECT au.id, au.home_ou, MAX(acirc.xact_start) AS TIME
FROM actor.usr au
JOIN ACTION.circulation acirc ON acirc.usr = au.id
join actor.org_unit aou on aou.id = au.home_ou
WHERE aou.shortname ilike 'dcl%'
GROUP BY 1, 2
;

create table rogan.active_expire as
select au.id, au.home_ou, au.expire_date as TIME
from actor.usr au
join actor.org_unit aou on aou.id = au.home_ou
where aou.shortname ilike 'dcl%' and au.deleted = false and au.active = true
;

create table rogan.active_usrs as
select distinct id, home_ou
from ( select * from rogan.active_sip
    UNION ALL
    select * from rogan.active_expire
    UNION ALL
    select * from rogan.active_circ
    ) peel
where time >= now() - interval '3 years'
;

-- active users
select emma.shortname as library, emma.active, peel.inactive from
( select count(au.id) as active, aou.shortname
    from rogan.active_usrs au
    join actor.org_unit aou on aou.id = au.home_ou
    group by 2 ) emma
join ( SELECT count(au.id) as inactive, aou.shortname
    FROM actor.usr au
    join actor.org_unit aou on aou.id = au.home_ou
    WHERE aou.shortname ilike 'dcl%' and au.id not in (select id from rogan.active_usrs)
    GROUP BY 2 ) peel on peel.shortname = emma.shortname
;



/*** profile counts ***/

DROP TABLE if exists rogan.active_sip;
DROP TABLE if exists rogan.active_circ;
DROP TABLE if exists rogan.active_expire;
DROP TABLE if exists rogan.active_usrs;

CREATE TABLE rogan.active_sip AS
SELECT au.id, au.home_ou, au.profile, MAX(aua.event_time) AS TIME
FROM actor.usr au
JOIN actor.usr_activity aua ON aua.usr = au.id
JOIN actor.org_unit aou ON aou.id = au.home_ou
WHERE aou.shortname ILIKE 'dcl%'
GROUP BY 1, 2, 3
;

CREATE TABLE rogan.active_circ AS
SELECT au.id, au.home_ou, au.profile, MAX(acirc.xact_start) AS TIME
FROM actor.usr au
JOIN ACTION.circulation acirc ON acirc.usr = au.id
JOIN actor.org_unit aou ON aou.id = au.home_ou
WHERE aou.shortname ILIKE 'dcl%'
GROUP BY 1, 2, 3
;

CREATE TABLE rogan.active_expire AS
SELECT au.id, au.home_ou, au.profile, au.expire_date AS TIME
FROM actor.usr au
JOIN actor.org_unit aou ON aou.id = au.home_ou
WHERE aou.shortname ILIKE 'dcl%' AND au.deleted = FALSE AND au.active = TRUE
;

CREATE TABLE rogan.active_usrs AS
SELECT DISTINCT id, home_ou, profile
FROM ( SELECT * FROM rogan.active_sip
    UNION ALL
    SELECT * FROM rogan.active_expire
    UNION ALL
    SELECT * FROM rogan.active_circ
    ) peel
WHERE TIME >= NOW() - INTERVAL '3 years'
;

SELECT aou.shortname as library, pgt.name as profile, COUNT(au.id) AS active
    FROM rogan.active_usrs au
    JOIN actor.org_unit aou ON aou.id = au.home_ou
    join permission.grp_tree pgt on pgt.id = au.profile
    GROUP BY 1, 2
    Order by 1, 2
;


SELECT aou.shortname as library, pgt.name as profile, COUNT(au.id) AS inactive
    FROM actor.usr au
    JOIN actor.org_unit aou ON aou.id = au.home_ou
    join permission.grp_tree pgt on pgt.id = au.profile
    WHERE aou.shortname ILIKE 'dcl%' AND au.id NOT IN (SELECT id FROM rogan.active_usrs)
    GROUP BY 1, 2
    order by 1, 2
;