3 INSERT INTO config.upgrade_log (version) VALUES ('0105'); -- miker
5 CREATE TRIGGER mat_summary_create_tgr AFTER INSERT ON booking.reservation FOR EACH ROW EXECUTE PROCEDURE money.mat_summary_create ('reservation');
6 CREATE TRIGGER mat_summary_change_tgr AFTER UPDATE ON booking.reservation FOR EACH ROW EXECUTE PROCEDURE money.mat_summary_update ();
7 CREATE TRIGGER mat_summary_remove_tgr AFTER DELETE ON booking.reservation FOR EACH ROW EXECUTE PROCEDURE money.mat_summary_delete ();
9 CREATE OR REPLACE VIEW money.open_billable_xact_summary AS
12 COALESCE(circ.circ_lib,groc.billing_location,res.pickup_lib) AS billing_location,
13 xact.xact_start AS xact_start,
14 xact.xact_finish AS xact_finish,
15 SUM(credit.amount) AS total_paid,
16 MAX(credit.payment_ts) AS last_payment_ts,
17 LAST(credit.note) AS last_payment_note,
18 LAST(credit.payment_type) AS last_payment_type,
19 SUM(debit.amount) AS total_owed,
20 MAX(debit.billing_ts) AS last_billing_ts,
21 LAST(debit.note) AS last_billing_note,
22 LAST(debit.billing_type) AS last_billing_type,
23 COALESCE(SUM(debit.amount),0) - COALESCE(SUM(credit.amount),0) AS balance_owed,
24 p.relname AS xact_type
25 FROM money.billable_xact xact
26 JOIN pg_class p ON (xact.tableoid = p.oid)
27 LEFT JOIN "action".circulation circ ON (circ.id = xact.id)
28 LEFT JOIN money.grocery groc ON (groc.id = xact.id)
29 LEFT JOIN booking.reservation res ON (groc.id = xact.id)
33 sum(billing.amount) AS amount,
34 max(billing.billing_ts) AS billing_ts,
35 last(billing.note) AS note,
36 last(billing.billing_type) AS billing_type
38 WHERE billing.voided IS FALSE
39 GROUP BY billing.xact, billing.voided
40 ) debit ON (xact.id = debit.xact AND debit.voided IS FALSE)
42 SELECT payment_view.xact,
44 sum(payment_view.amount) AS amount,
45 max(payment_view.payment_ts) AS payment_ts,
46 last(payment_view.note) AS note,
47 last(payment_view.payment_type) AS payment_type
48 FROM money.payment_view
49 WHERE payment_view.voided IS FALSE
50 GROUP BY payment_view.xact, payment_view.voided
51 ) credit ON (xact.id = credit.xact AND credit.voided IS FALSE)
52 WHERE xact.xact_finish IS NULL
54 ORDER BY MAX(debit.billing_ts), MAX(credit.payment_ts);
56 CREATE OR REPLACE FUNCTION actor.calculate_system_penalties( match_user INT, context_org INT ) RETURNS SETOF actor.usr_standing_penalty AS $func$
58 user_object actor.usr%ROWTYPE;
59 new_sp_row actor.usr_standing_penalty%ROWTYPE;
60 existing_sp_row actor.usr_standing_penalty%ROWTYPE;
61 collections_fines permission.grp_penalty_threshold%ROWTYPE;
62 max_fines permission.grp_penalty_threshold%ROWTYPE;
63 max_overdue permission.grp_penalty_threshold%ROWTYPE;
64 max_items_out permission.grp_penalty_threshold%ROWTYPE;
68 context_org_list INT[];
69 current_fines NUMERIC(8,2) := 0.0;
70 tmp_fines NUMERIC(8,2);
73 tmp_org actor.org_unit%ROWTYPE;
75 SELECT INTO user_object * FROM actor.usr WHERE id = match_user;
78 SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
80 -- Fail if the user has a high fine balance
82 tmp_grp := user_object.profile;
84 SELECT * INTO max_fines FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = 1 AND org_unit = tmp_org.id;
86 IF max_fines.threshold IS NULL THEN
87 SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
92 IF tmp_grp IS NULL THEN
97 IF max_fines.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
101 SELECT * INTO tmp_org FROM actor.org_unit WHERE id = tmp_org.parent_ou;
105 IF max_fines.threshold IS NOT NULL THEN
107 FOR existing_sp_row IN
109 FROM actor.usr_standing_penalty
110 WHERE usr = match_user
111 AND org_unit = max_fines.org_unit
112 AND (stop_date IS NULL or stop_date > NOW())
113 AND standing_penalty = 1
115 RETURN NEXT existing_sp_row;
118 SELECT SUM(f.balance_owed) INTO current_fines
119 FROM money.materialized_billable_xact_summary f
122 FROM booking.reservation r
123 JOIN actor.org_unit_full_path( max_fines.org_unit ) fp ON (r.pickup_lib = fp.id)
124 WHERE usr = match_user
125 AND xact_finish IS NULL
129 JOIN actor.org_unit_full_path( max_fines.org_unit ) fp ON (g.billing_location = fp.id)
130 WHERE usr = match_user
131 AND xact_finish IS NULL
134 FROM action.circulation circ
135 JOIN actor.org_unit_full_path( max_fines.org_unit ) fp ON (circ.circ_lib = fp.id)
136 WHERE usr = match_user
137 AND xact_finish IS NULL ) l USING (id);
139 IF current_fines >= max_fines.threshold THEN
140 new_sp_row.usr := match_user;
141 new_sp_row.org_unit := max_fines.org_unit;
142 new_sp_row.standing_penalty := 1;
143 RETURN NEXT new_sp_row;
147 -- Start over for max overdue
148 SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
150 -- Fail if the user has too many overdue items
152 tmp_grp := user_object.profile;
155 SELECT * INTO max_overdue FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = 2 AND org_unit = tmp_org.id;
157 IF max_overdue.threshold IS NULL THEN
158 SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
163 IF tmp_grp IS NULL THEN
168 IF max_overdue.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
172 SELECT INTO tmp_org * FROM actor.org_unit WHERE id = tmp_org.parent_ou;
176 IF max_overdue.threshold IS NOT NULL THEN
178 FOR existing_sp_row IN
180 FROM actor.usr_standing_penalty
181 WHERE usr = match_user
182 AND org_unit = max_overdue.org_unit
183 AND (stop_date IS NULL or stop_date > NOW())
184 AND standing_penalty = 2
186 RETURN NEXT existing_sp_row;
189 SELECT INTO items_overdue COUNT(*)
190 FROM action.circulation circ
191 JOIN actor.org_unit_full_path( max_overdue.org_unit ) fp ON (circ.circ_lib = fp.id)
192 WHERE circ.usr = match_user
193 AND circ.checkin_time IS NULL
194 AND circ.due_date < NOW()
195 AND (circ.stop_fines = 'MAXFINES' OR circ.stop_fines IS NULL);
197 IF items_overdue >= max_overdue.threshold::INT THEN
198 new_sp_row.usr := match_user;
199 new_sp_row.org_unit := max_overdue.org_unit;
200 new_sp_row.standing_penalty := 2;
201 RETURN NEXT new_sp_row;
205 -- Start over for max out
206 SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
208 -- Fail if the user has too many checked out items
210 tmp_grp := user_object.profile;
212 SELECT * INTO max_items_out FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = 3 AND org_unit = tmp_org.id;
214 IF max_items_out.threshold IS NULL THEN
215 SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
220 IF tmp_grp IS NULL THEN
225 IF max_items_out.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
229 SELECT INTO tmp_org * FROM actor.org_unit WHERE id = tmp_org.parent_ou;
234 -- Fail if the user has too many items checked out
235 IF max_items_out.threshold IS NOT NULL THEN
237 FOR existing_sp_row IN
239 FROM actor.usr_standing_penalty
240 WHERE usr = match_user
241 AND org_unit = max_items_out.org_unit
242 AND (stop_date IS NULL or stop_date > NOW())
243 AND standing_penalty = 3
245 RETURN NEXT existing_sp_row;
248 SELECT INTO items_out COUNT(*)
249 FROM action.circulation circ
250 JOIN actor.org_unit_full_path( max_items_out.org_unit ) fp ON (circ.circ_lib = fp.id)
251 WHERE circ.usr = match_user
252 AND circ.checkin_time IS NULL
253 AND (circ.stop_fines IN ('MAXFINES','LONGOVERDUE') OR circ.stop_fines IS NULL);
255 IF items_out >= max_items_out.threshold::INT THEN
256 new_sp_row.usr := match_user;
257 new_sp_row.org_unit := max_items_out.org_unit;
258 new_sp_row.standing_penalty := 3;
259 RETURN NEXT new_sp_row;
263 -- Start over for collections warning
264 SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
266 -- Fail if the user has a collections-level fine balance
268 tmp_grp := user_object.profile;
270 SELECT * INTO max_fines FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = 4 AND org_unit = tmp_org.id;
272 IF max_fines.threshold IS NULL THEN
273 SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
278 IF tmp_grp IS NULL THEN
283 IF max_fines.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
287 SELECT * INTO tmp_org FROM actor.org_unit WHERE id = tmp_org.parent_ou;
291 IF max_fines.threshold IS NOT NULL THEN
293 FOR existing_sp_row IN
295 FROM actor.usr_standing_penalty
296 WHERE usr = match_user
297 AND org_unit = max_fines.org_unit
298 AND (stop_date IS NULL or stop_date > NOW())
299 AND standing_penalty = 4
301 RETURN NEXT existing_sp_row;
304 SELECT SUM(f.balance_owed) INTO current_fines
305 FROM money.materialized_billable_xact_summary f
308 FROM booking.reservation r
309 JOIN actor.org_unit_full_path( max_fines.org_unit ) fp ON (r.pickup_lib = fp.id)
310 WHERE usr = match_user
311 AND xact_finish IS NULL
315 JOIN actor.org_unit_full_path( max_fines.org_unit ) fp ON (g.billing_location = fp.id)
316 WHERE usr = match_user
317 AND xact_finish IS NULL
320 FROM action.circulation circ
321 JOIN actor.org_unit_full_path( max_fines.org_unit ) fp ON (circ.circ_lib = fp.id)
322 WHERE usr = match_user
323 AND xact_finish IS NULL ) l USING (id);
325 IF current_fines >= max_fines.threshold THEN
326 new_sp_row.usr := match_user;
327 new_sp_row.org_unit := max_fines.org_unit;
328 new_sp_row.standing_penalty := 4;
329 RETURN NEXT new_sp_row;
336 $func$ LANGUAGE plpgsql;