1 -- a script to set users to inactive per PINES policy
2 -- designed to run nightly via cron
8 alert_message = 'automatically set to inactive status via PINES policy ' || alert_message,
9 last_update_time = now()
10 -- no unfinished circulations and no circulations within the last 3 years
13 from action.circulation ac
16 xact_finish is null or (
17 now() - ac.xact_start < '3 years'::interval
21 -- no hold requests placed in the last 3 years
24 from action.hold_request ahr
26 and (now() - request_time) < '3 years'::interval
28 -- no owed money in either direction and no payment within the last 3 years
31 from money.materialized_billable_xact_summary mmbxs
32 where mmbxs.usr = au.id
34 balance_owed <> '0.00' or (now() - last_payment_ts) < '3 years'::interval)
36 -- no activity entries within the last 3 years
39 from actor.usr_activity aua
41 and (now() - event_time) < '3 years'::interval
43 -- we only care about active users
45 -- we don't care about deleted users
47 -- don't include non-expired users that don't otherwise meet the "inactive" criteria
48 and expire_date < now()
49 -- we don't want users that have been created within the last 3 years
50 and (now() - au.create_date) > '3 years'
51 -- restrict to patron profiles ('Patrons' = 45)
54 from permission.grp_descendants(45)