3 INSERT INTO config.upgrade_log (version) VALUES ('0118');
5 INSERT INTO config.standing_penalty (id,name,label) VALUES (30,'PATRON_IN_COLLECTIONS','Patron has been referred to a collections agency');
7 CREATE OR REPLACE FUNCTION actor.calculate_system_penalties( match_user INT, context_org INT ) RETURNS SETOF actor.usr_standing_penalty AS $func$
9 user_object actor.usr%ROWTYPE;
10 new_sp_row actor.usr_standing_penalty%ROWTYPE;
11 existing_sp_row actor.usr_standing_penalty%ROWTYPE;
12 collections_fines permission.grp_penalty_threshold%ROWTYPE;
13 max_fines permission.grp_penalty_threshold%ROWTYPE;
14 max_overdue permission.grp_penalty_threshold%ROWTYPE;
15 max_items_out permission.grp_penalty_threshold%ROWTYPE;
19 context_org_list INT[];
20 current_fines NUMERIC(8,2) := 0.0;
21 tmp_fines NUMERIC(8,2);
24 tmp_org actor.org_unit%ROWTYPE;
25 tmp_penalty config.standing_penalty%ROWTYPE;
28 SELECT INTO user_object * FROM actor.usr WHERE id = match_user;
31 SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
33 -- Fail if the user has a high fine balance
35 tmp_grp := user_object.profile;
37 SELECT * INTO max_fines FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = 1 AND org_unit = tmp_org.id;
39 IF max_fines.threshold IS NULL THEN
40 SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
45 IF tmp_grp IS NULL THEN
50 IF max_fines.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
54 SELECT * INTO tmp_org FROM actor.org_unit WHERE id = tmp_org.parent_ou;
58 IF max_fines.threshold IS NOT NULL THEN
60 FOR existing_sp_row IN
62 FROM actor.usr_standing_penalty
63 WHERE usr = match_user
64 AND org_unit = max_fines.org_unit
65 AND (stop_date IS NULL or stop_date > NOW())
66 AND standing_penalty = 1
68 RETURN NEXT existing_sp_row;
71 SELECT SUM(f.balance_owed) INTO current_fines
72 FROM money.materialized_billable_xact_summary f
75 FROM booking.reservation r
76 JOIN actor.org_unit_full_path( max_fines.org_unit ) fp ON (r.pickup_lib = fp.id)
77 WHERE usr = match_user
78 AND xact_finish IS NULL
82 JOIN actor.org_unit_full_path( max_fines.org_unit ) fp ON (g.billing_location = fp.id)
83 WHERE usr = match_user
84 AND xact_finish IS NULL
87 FROM action.circulation circ
88 JOIN actor.org_unit_full_path( max_fines.org_unit ) fp ON (circ.circ_lib = fp.id)
89 WHERE usr = match_user
90 AND xact_finish IS NULL ) l USING (id);
92 IF current_fines >= max_fines.threshold THEN
93 new_sp_row.usr := match_user;
94 new_sp_row.org_unit := max_fines.org_unit;
95 new_sp_row.standing_penalty := 1;
96 RETURN NEXT new_sp_row;
100 -- Start over for max overdue
101 SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
103 -- Fail if the user has too many overdue items
105 tmp_grp := user_object.profile;
108 SELECT * INTO max_overdue FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = 2 AND org_unit = tmp_org.id;
110 IF max_overdue.threshold IS NULL THEN
111 SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
116 IF tmp_grp IS NULL THEN
121 IF max_overdue.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
125 SELECT INTO tmp_org * FROM actor.org_unit WHERE id = tmp_org.parent_ou;
129 IF max_overdue.threshold IS NOT NULL THEN
131 FOR existing_sp_row IN
133 FROM actor.usr_standing_penalty
134 WHERE usr = match_user
135 AND org_unit = max_overdue.org_unit
136 AND (stop_date IS NULL or stop_date > NOW())
137 AND standing_penalty = 2
139 RETURN NEXT existing_sp_row;
142 SELECT INTO items_overdue COUNT(*)
143 FROM action.circulation circ
144 JOIN actor.org_unit_full_path( max_overdue.org_unit ) fp ON (circ.circ_lib = fp.id)
145 WHERE circ.usr = match_user
146 AND circ.checkin_time IS NULL
147 AND circ.due_date < NOW()
148 AND (circ.stop_fines = 'MAXFINES' OR circ.stop_fines IS NULL);
150 IF items_overdue >= max_overdue.threshold::INT THEN
151 new_sp_row.usr := match_user;
152 new_sp_row.org_unit := max_overdue.org_unit;
153 new_sp_row.standing_penalty := 2;
154 RETURN NEXT new_sp_row;
158 -- Start over for max out
159 SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
161 -- Fail if the user has too many checked out items
163 tmp_grp := user_object.profile;
165 SELECT * INTO max_items_out FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = 3 AND org_unit = tmp_org.id;
167 IF max_items_out.threshold IS NULL THEN
168 SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
173 IF tmp_grp IS NULL THEN
178 IF max_items_out.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
182 SELECT INTO tmp_org * FROM actor.org_unit WHERE id = tmp_org.parent_ou;
187 -- Fail if the user has too many items checked out
188 IF max_items_out.threshold IS NOT NULL THEN
190 FOR existing_sp_row IN
192 FROM actor.usr_standing_penalty
193 WHERE usr = match_user
194 AND org_unit = max_items_out.org_unit
195 AND (stop_date IS NULL or stop_date > NOW())
196 AND standing_penalty = 3
198 RETURN NEXT existing_sp_row;
201 SELECT INTO items_out COUNT(*)
202 FROM action.circulation circ
203 JOIN actor.org_unit_full_path( max_items_out.org_unit ) fp ON (circ.circ_lib = fp.id)
204 WHERE circ.usr = match_user
205 AND circ.checkin_time IS NULL
206 AND (circ.stop_fines IN ('MAXFINES','LONGOVERDUE') OR circ.stop_fines IS NULL);
208 IF items_out >= max_items_out.threshold::INT THEN
209 new_sp_row.usr := match_user;
210 new_sp_row.org_unit := max_items_out.org_unit;
211 new_sp_row.standing_penalty := 3;
212 RETURN NEXT new_sp_row;
216 -- Start over for collections warning
217 SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
219 -- Fail if the user has a collections-level fine balance
221 tmp_grp := user_object.profile;
223 SELECT * INTO max_fines FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = 4 AND org_unit = tmp_org.id;
225 IF max_fines.threshold IS NULL THEN
226 SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
231 IF tmp_grp IS NULL THEN
236 IF max_fines.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
240 SELECT * INTO tmp_org FROM actor.org_unit WHERE id = tmp_org.parent_ou;
244 IF max_fines.threshold IS NOT NULL THEN
246 FOR existing_sp_row IN
248 FROM actor.usr_standing_penalty
249 WHERE usr = match_user
250 AND org_unit = max_fines.org_unit
251 AND (stop_date IS NULL or stop_date > NOW())
252 AND standing_penalty = 4
254 RETURN NEXT existing_sp_row;
257 SELECT SUM(f.balance_owed) INTO current_fines
258 FROM money.materialized_billable_xact_summary f
261 FROM booking.reservation r
262 JOIN actor.org_unit_full_path( max_fines.org_unit ) fp ON (r.pickup_lib = fp.id)
263 WHERE usr = match_user
264 AND xact_finish IS NULL
268 JOIN actor.org_unit_full_path( max_fines.org_unit ) fp ON (g.billing_location = fp.id)
269 WHERE usr = match_user
270 AND xact_finish IS NULL
273 FROM action.circulation circ
274 JOIN actor.org_unit_full_path( max_fines.org_unit ) fp ON (circ.circ_lib = fp.id)
275 WHERE usr = match_user
276 AND xact_finish IS NULL ) l USING (id);
278 IF current_fines >= max_fines.threshold THEN
279 new_sp_row.usr := match_user;
280 new_sp_row.org_unit := max_fines.org_unit;
281 new_sp_row.standing_penalty := 4;
282 RETURN NEXT new_sp_row;
286 -- Start over for in collections
287 SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
289 -- Remove the in-collections penalty if the user has paid down enough
290 -- This penalty is different, because this code is not responsible for creating
291 -- new in-collections penalties, only for removing them
293 tmp_grp := user_object.profile;
295 SELECT * INTO max_fines FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = 30 AND org_unit = tmp_org.id;
297 IF max_fines.threshold IS NULL THEN
298 SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
303 IF tmp_grp IS NULL THEN
308 IF max_fines.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
312 SELECT * INTO tmp_org FROM actor.org_unit WHERE id = tmp_org.parent_ou;
316 IF max_fines.threshold IS NOT NULL THEN
318 -- first, see if the user had paid down to the threshold
319 SELECT SUM(f.balance_owed) INTO current_fines
320 FROM money.materialized_billable_xact_summary f
323 FROM booking.reservation r
324 JOIN actor.org_unit_full_path( max_fines.org_unit ) fp ON (r.pickup_lib = fp.id)
325 WHERE usr = match_user
326 AND xact_finish IS NULL
330 JOIN actor.org_unit_full_path( max_fines.org_unit ) fp ON (g.billing_location = fp.id)
331 WHERE usr = match_user
332 AND xact_finish IS NULL
335 FROM action.circulation circ
336 JOIN actor.org_unit_full_path( max_fines.org_unit ) fp ON (circ.circ_lib = fp.id)
337 WHERE usr = match_user
338 AND xact_finish IS NULL ) l USING (id);
340 IF current_fines IS NULL OR current_fines <= max_fines.threshold THEN
341 -- patron has paid down enough
343 SELECT INTO tmp_penalty * FROM config.standing_penalty WHERE id = 30;
345 IF tmp_penalty.org_depth IS NOT NULL THEN
347 -- since this code is not responsible for applying the penalty, it can't
348 -- guarantee the current context org will match the org at which the penalty
349 --- was applied. search up the org tree until we hit the configured penalty depth
350 SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
351 SELECT INTO tmp_depth depth FROM actor.org_unit_type WHERE id = tmp_org.ou_type;
353 WHILE tmp_depth >= tmp_penalty.org_depth LOOP
355 FOR existing_sp_row IN
357 FROM actor.usr_standing_penalty
358 WHERE usr = match_user
359 AND org_unit = tmp_org.id
360 AND (stop_date IS NULL or stop_date > NOW())
361 AND standing_penalty = 30
364 -- Penalty exists, return it for removal
365 RETURN NEXT existing_sp_row;
368 IF tmp_org.parent_ou IS NULL THEN
372 SELECT * INTO tmp_org FROM actor.org_unit WHERE id = tmp_org.parent_ou;
373 SELECT INTO tmp_depth depth FROM actor.org_unit_type WHERE id = tmp_org.ou_type;
378 -- no penalty depth is defined, look for exact matches
380 FOR existing_sp_row IN
382 FROM actor.usr_standing_penalty
383 WHERE usr = match_user
384 AND org_unit = max_fines.org_unit
385 AND (stop_date IS NULL or stop_date > NOW())
386 AND standing_penalty = 30
388 -- Penalty exists, return it for removal
389 RETURN NEXT existing_sp_row;
399 $func$ LANGUAGE plpgsql;