4 -- Using a tool such as pgadmin to run this script may fail
5 -- If it does, try psql command line.
7 -- Change this to FALSE to disable updating existing circs
8 -- Otherwise will use the fine interval for the grace period
11 INSERT INTO config.upgrade_log (version) VALUES ('0503');
15 ALTER TABLE config.circ_matrix_matchpoint
16 ADD COLUMN grace_period INTERVAL;
18 ALTER TABLE config.rule_recurring_fine
19 ADD COLUMN grace_period INTERVAL NOT NULL DEFAULT '1 day';
21 ALTER TABLE action.circulation
22 ADD COLUMN grace_period INTERVAL NOT NULL DEFAULT '0 seconds';
24 ALTER TABLE action.aged_circulation
25 ADD COLUMN grace_period INTERVAL NOT NULL DEFAULT '0 seconds';
27 -- Remove defaults needed to stop null complaints
29 ALTER TABLE action.circulation
30 ALTER COLUMN grace_period DROP DEFAULT;
32 ALTER TABLE action.aged_circulation
33 ALTER COLUMN grace_period DROP DEFAULT;
37 DROP VIEW action.all_circulation;
38 DROP VIEW action.open_circulation;
39 DROP VIEW action.billable_circulations;
43 CREATE OR REPLACE VIEW action.all_circulation AS
44 SELECT id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location,
45 copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy,
46 circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, grace_period, due_date,
47 stop_fines_time, checkin_time, create_time, duration, fine_interval, recurring_fine,
48 max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recurring_fine_rule,
49 max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ
50 FROM action.aged_circulation
52 SELECT DISTINCT circ.id,COALESCE(a.post_code,b.post_code) AS usr_post_code, p.home_ou AS usr_home_ou, p.profile AS usr_profile, EXTRACT(YEAR FROM p.dob)::INT AS usr_birth_year,
53 cp.call_number AS copy_call_number, cp.location AS copy_location, cn.owning_lib AS copy_owning_lib, cp.circ_lib AS copy_circ_lib,
54 cn.record AS copy_bib_record, circ.xact_start, circ.xact_finish, circ.target_copy, circ.circ_lib, circ.circ_staff, circ.checkin_staff,
55 circ.checkin_lib, circ.renewal_remaining, circ.grace_period, circ.due_date, circ.stop_fines_time, circ.checkin_time, circ.create_time, circ.duration,
56 circ.fine_interval, circ.recurring_fine, circ.max_fine, circ.phone_renewal, circ.desk_renewal, circ.opac_renewal, circ.duration_rule,
57 circ.recurring_fine_rule, circ.max_fine_rule, circ.stop_fines, circ.workstation, circ.checkin_workstation, circ.checkin_scan_time,
59 FROM action.circulation circ
60 JOIN asset.copy cp ON (circ.target_copy = cp.id)
61 JOIN asset.call_number cn ON (cp.call_number = cn.id)
62 JOIN actor.usr p ON (circ.usr = p.id)
63 LEFT JOIN actor.usr_address a ON (p.mailing_address = a.id)
64 LEFT JOIN actor.usr_address b ON (p.billing_address = a.id);
66 CREATE OR REPLACE VIEW action.open_circulation AS
68 FROM action.circulation
69 WHERE checkin_time IS NULL
73 CREATE OR REPLACE VIEW action.billable_circulations AS
75 FROM action.circulation
76 WHERE xact_finish IS NULL;
78 -- Drop Functions that rely on types
80 DROP FUNCTION action.item_user_circ_test(INT, BIGINT, INT, BOOL);
81 DROP FUNCTION action.item_user_circ_test(INT, BIGINT, INT);
82 DROP FUNCTION action.item_user_renew_test(INT, BIGINT, INT);
84 -- Drop Types that are changing
86 DROP TYPE action.circ_matrix_test_result;
90 CREATE TYPE action.circ_matrix_test_result AS ( success BOOL, fail_part TEXT, buildrows INT[], matchpoint INT, circulate BOOL, duration_rule INT, recurring_fine_rule INT, max_fine_rule INT, hard_due_date INT, renewals INT, grace_period INTERVAL );
92 -- Fix/Replace Functions
94 CREATE OR REPLACE FUNCTION action.find_circ_matrix_matchpoint( context_ou INT, item_object asset.copy, user_object actor.usr, renewal BOOL ) RETURNS action.found_circ_matrix_matchpoint AS $func$
96 cn_object asset.call_number%ROWTYPE;
97 rec_descriptor metabib.rec_descriptor%ROWTYPE;
98 cur_matchpoint config.circ_matrix_matchpoint%ROWTYPE;
99 matchpoint config.circ_matrix_matchpoint%ROWTYPE;
100 weights config.circ_matrix_weights%ROWTYPE;
102 denominator NUMERIC(6,2);
104 result action.found_circ_matrix_matchpoint;
107 result.success = false;
110 SELECT INTO cn_object * FROM asset.call_number WHERE id = item_object.call_number;
111 SELECT INTO rec_descriptor * FROM metabib.rec_descriptor WHERE record = cn_object.record;
113 -- Pre-generate this so we only calc it once
114 IF user_object.dob IS NOT NULL THEN
115 SELECT INTO user_age age(user_object.dob);
118 -- Grab the closest set circ weight setting.
119 SELECT INTO weights cw.*
120 FROM config.weight_assoc wa
121 JOIN config.circ_matrix_weights cw ON (cw.id = wa.circ_weights)
122 JOIN actor.org_unit_ancestors_distance( context_ou ) d ON (wa.org_unit = d.id)
127 -- No weights? Bad admin! Defaults to handle that anyway.
128 IF weights.id IS NULL THEN
130 weights.org_unit := 10.0;
131 weights.circ_modifier := 5.0;
132 weights.marc_type := 4.0;
133 weights.marc_form := 3.0;
134 weights.marc_vr_format := 2.0;
135 weights.copy_circ_lib := 8.0;
136 weights.copy_owning_lib := 8.0;
137 weights.user_home_ou := 8.0;
138 weights.ref_flag := 1.0;
139 weights.juvenile_flag := 6.0;
140 weights.is_renewal := 7.0;
141 weights.usr_age_lower_bound := 0.0;
142 weights.usr_age_upper_bound := 0.0;
145 -- Determine the max (expected) depth (+1) of the org tree and max depth of the permisson tree
146 -- If you break your org tree with funky parenting this may be wrong
147 -- Note: This CTE is duplicated in the find_hold_matrix_matchpoint function, and it may be a good idea to split it off to a function
148 -- We use one denominator for all tree-based checks for when permission groups and org units have the same weighting
149 WITH all_distance(distance) AS (
150 SELECT depth AS distance FROM actor.org_unit_type
152 SELECT distance AS distance FROM permission.grp_ancestors_distance((SELECT id FROM permission.grp_tree WHERE parent IS NULL))
154 SELECT INTO denominator MAX(distance) + 1 FROM all_distance;
156 -- Loop over all the potential matchpoints
157 FOR cur_matchpoint IN
159 FROM config.circ_matrix_matchpoint m
160 /*LEFT*/ JOIN permission.grp_ancestors_distance( user_object.profile ) upgad ON m.grp = upgad.id
161 /*LEFT*/ JOIN actor.org_unit_ancestors_distance( context_ou ) ctoua ON m.org_unit = ctoua.id
162 LEFT JOIN actor.org_unit_ancestors_distance( cn_object.owning_lib ) cnoua ON m.copy_owning_lib = cnoua.id
163 LEFT JOIN actor.org_unit_ancestors_distance( item_object.circ_lib ) iooua ON m.copy_circ_lib = iooua.id
164 LEFT JOIN actor.org_unit_ancestors_distance( user_object.home_ou ) uhoua ON m.user_home_ou = uhoua.id
167 -- AND (m.grp IS NULL OR upgad.id IS NOT NULL) -- Optional Permission Group?
169 -- AND (m.org_unit IS NULL OR ctoua.id IS NOT NULL) -- Optional Org Unit?
170 AND (m.copy_owning_lib IS NULL OR cnoua.id IS NOT NULL)
171 AND (m.copy_circ_lib IS NULL OR iooua.id IS NOT NULL)
172 AND (m.user_home_ou IS NULL OR uhoua.id IS NOT NULL)
174 AND (m.is_renewal IS NULL OR m.is_renewal = renewal)
175 -- Static User Checks
176 AND (m.juvenile_flag IS NULL OR m.juvenile_flag = user_object.juvenile)
177 AND (m.usr_age_lower_bound IS NULL OR (user_age IS NOT NULL AND m.usr_age_lower_bound < user_age))
178 AND (m.usr_age_upper_bound IS NULL OR (user_age IS NOT NULL AND m.usr_age_upper_bound > user_age))
179 -- Static Item Checks
180 AND (m.circ_modifier IS NULL OR m.circ_modifier = item_object.circ_modifier)
181 AND (m.marc_type IS NULL OR m.marc_type = COALESCE(item_object.circ_as_type, rec_descriptor.item_type))
182 AND (m.marc_form IS NULL OR m.marc_form = rec_descriptor.item_form)
183 AND (m.marc_vr_format IS NULL OR m.marc_vr_format = rec_descriptor.vr_format)
184 AND (m.ref_flag IS NULL OR m.ref_flag = item_object.ref)
187 CASE WHEN upgad.distance IS NOT NULL THEN 2^(2*weights.grp - (upgad.distance/denominator)) ELSE 0.0 END +
189 CASE WHEN ctoua.distance IS NOT NULL THEN 2^(2*weights.org_unit - (ctoua.distance/denominator)) ELSE 0.0 END +
190 CASE WHEN cnoua.distance IS NOT NULL THEN 2^(2*weights.copy_owning_lib - (cnoua.distance/denominator)) ELSE 0.0 END +
191 CASE WHEN iooua.distance IS NOT NULL THEN 2^(2*weights.copy_circ_lib - (iooua.distance/denominator)) ELSE 0.0 END +
192 CASE WHEN uhoua.distance IS NOT NULL THEN 2^(2*weights.user_home_ou - (uhoua.distance/denominator)) ELSE 0.0 END +
193 -- Circ Type -- Note: 4^x is equiv to 2^(2*x)
194 CASE WHEN m.is_renewal IS NOT NULL THEN 4^weights.is_renewal ELSE 0.0 END +
195 -- Static User Checks
196 CASE WHEN m.juvenile_flag IS NOT NULL THEN 4^weights.juvenile_flag ELSE 0.0 END +
197 CASE WHEN m.usr_age_lower_bound IS NOT NULL THEN 4^weights.usr_age_lower_bound ELSE 0.0 END +
198 CASE WHEN m.usr_age_upper_bound IS NOT NULL THEN 4^weights.usr_age_upper_bound ELSE 0.0 END +
199 -- Static Item Checks
200 CASE WHEN m.circ_modifier IS NOT NULL THEN 4^weights.circ_modifier ELSE 0.0 END +
201 CASE WHEN m.marc_type IS NOT NULL THEN 4^weights.marc_type ELSE 0.0 END +
202 CASE WHEN m.marc_form IS NOT NULL THEN 4^weights.marc_form ELSE 0.0 END +
203 CASE WHEN m.marc_vr_format IS NOT NULL THEN 4^weights.marc_vr_format ELSE 0.0 END +
204 CASE WHEN m.ref_flag IS NOT NULL THEN 4^weights.ref_flag ELSE 0.0 END DESC,
205 -- Final sort on id, so that if two rules have the same sorting in the previous sort they have a defined order
206 -- This prevents "we changed the table order by updating a rule, and we started getting different results"
209 -- Record the full matching row list
210 row_list := row_list || cur_matchpoint.id;
212 -- No matchpoint yet?
213 IF matchpoint.id IS NULL THEN
214 -- Take the entire matchpoint as a starting point
215 matchpoint := cur_matchpoint;
216 CONTINUE; -- No need to look at this row any more.
219 -- Incomplete matchpoint?
220 IF matchpoint.circulate IS NULL THEN
221 matchpoint.circulate := cur_matchpoint.circulate;
223 IF matchpoint.duration_rule IS NULL THEN
224 matchpoint.duration_rule := cur_matchpoint.duration_rule;
226 IF matchpoint.recurring_fine_rule IS NULL THEN
227 matchpoint.recurring_fine_rule := cur_matchpoint.recurring_fine_rule;
229 IF matchpoint.max_fine_rule IS NULL THEN
230 matchpoint.max_fine_rule := cur_matchpoint.max_fine_rule;
232 IF matchpoint.hard_due_date IS NULL THEN
233 matchpoint.hard_due_date := cur_matchpoint.hard_due_date;
235 IF matchpoint.total_copy_hold_ratio IS NULL THEN
236 matchpoint.total_copy_hold_ratio := cur_matchpoint.total_copy_hold_ratio;
238 IF matchpoint.available_copy_hold_ratio IS NULL THEN
239 matchpoint.available_copy_hold_ratio := cur_matchpoint.available_copy_hold_ratio;
241 IF matchpoint.renewals IS NULL THEN
242 matchpoint.renewals := cur_matchpoint.renewals;
244 IF matchpoint.grace_period IS NULL THEN
245 matchpoint.grace_period := cur_matchpoint.grace_period;
249 -- Check required fields
250 IF matchpoint.circulate IS NOT NULL AND
251 matchpoint.duration_rule IS NOT NULL AND
252 matchpoint.recurring_fine_rule IS NOT NULL AND
253 matchpoint.max_fine_rule IS NOT NULL THEN
254 -- All there? We have a completed match.
255 result.success := true;
258 -- Include the assembled matchpoint, even if it isn't complete
259 result.matchpoint := matchpoint;
261 -- Include (for debugging) the full list of matching rows
262 result.buildrows := row_list;
264 -- Hand the result back to caller
267 $func$ LANGUAGE plpgsql;
269 CREATE OR REPLACE FUNCTION action.item_user_circ_test( circ_ou INT, match_item BIGINT, match_user INT, renewal BOOL ) RETURNS SETOF action.circ_matrix_test_result AS $func$
271 user_object actor.usr%ROWTYPE;
272 standing_penalty config.standing_penalty%ROWTYPE;
273 item_object asset.copy%ROWTYPE;
274 item_status_object config.copy_status%ROWTYPE;
275 item_location_object asset.copy_location%ROWTYPE;
276 result action.circ_matrix_test_result;
277 circ_test action.found_circ_matrix_matchpoint;
278 circ_matchpoint config.circ_matrix_matchpoint%ROWTYPE;
279 out_by_circ_mod config.circ_matrix_circ_mod_test%ROWTYPE;
280 circ_mod_map config.circ_matrix_circ_mod_test_map%ROWTYPE;
281 hold_ratio action.hold_stats%ROWTYPE;
284 context_org_list INT[];
287 -- Assume success unless we hit a failure condition
288 result.success := TRUE;
290 -- Fail if the user is BARRED
291 SELECT INTO user_object * FROM actor.usr WHERE id = match_user;
293 -- Fail if we couldn't find the user
294 IF user_object.id IS NULL THEN
295 result.fail_part := 'no_user';
296 result.success := FALSE;
302 SELECT INTO item_object * FROM asset.copy WHERE id = match_item;
304 -- Fail if we couldn't find the item
305 IF item_object.id IS NULL THEN
306 result.fail_part := 'no_item';
307 result.success := FALSE;
313 IF user_object.barred IS TRUE THEN
314 result.fail_part := 'actor.usr.barred';
315 result.success := FALSE;
320 -- Fail if the item can't circulate
321 IF item_object.circulate IS FALSE THEN
322 result.fail_part := 'asset.copy.circulate';
323 result.success := FALSE;
328 -- Fail if the item isn't in a circulateable status on a non-renewal
329 IF NOT renewal AND item_object.status NOT IN ( 0, 7, 8 ) THEN
330 result.fail_part := 'asset.copy.status';
331 result.success := FALSE;
334 ELSIF renewal AND item_object.status <> 1 THEN
335 result.fail_part := 'asset.copy.status';
336 result.success := FALSE;
341 -- Fail if the item can't circulate because of the shelving location
342 SELECT INTO item_location_object * FROM asset.copy_location WHERE id = item_object.location;
343 IF item_location_object.circulate IS FALSE THEN
344 result.fail_part := 'asset.copy_location.circulate';
345 result.success := FALSE;
350 SELECT INTO circ_test * FROM action.find_circ_matrix_matchpoint(circ_ou, item_object, user_object, renewal);
352 circ_matchpoint := circ_test.matchpoint;
353 result.matchpoint := circ_matchpoint.id;
354 result.circulate := circ_matchpoint.circulate;
355 result.duration_rule := circ_matchpoint.duration_rule;
356 result.recurring_fine_rule := circ_matchpoint.recurring_fine_rule;
357 result.max_fine_rule := circ_matchpoint.max_fine_rule;
358 result.hard_due_date := circ_matchpoint.hard_due_date;
359 result.renewals := circ_matchpoint.renewals;
360 result.grace_period := circ_matchpoint.grace_period;
361 result.buildrows := circ_test.buildrows;
363 -- Fail if we couldn't find a matchpoint
364 IF circ_test.success = false THEN
365 result.fail_part := 'no_matchpoint';
366 result.success := FALSE;
369 RETURN; -- All tests after this point require a matchpoint. No sense in running on an incomplete or missing one.
372 -- Apparently....use the circ matchpoint org unit to determine what org units are valid.
373 SELECT INTO context_org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( circ_matchpoint.org_unit );
376 penalty_type = '%RENEW%';
378 penalty_type = '%CIRC%';
381 FOR standing_penalty IN
382 SELECT DISTINCT csp.*
383 FROM actor.usr_standing_penalty usp
384 JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty)
385 WHERE usr = match_user
386 AND usp.org_unit IN ( SELECT * FROM unnest(context_org_list) )
387 AND (usp.stop_date IS NULL or usp.stop_date > NOW())
388 AND csp.block_list LIKE penalty_type LOOP
390 result.fail_part := standing_penalty.name;
391 result.success := FALSE;
396 -- Fail if the test is set to hard non-circulating
397 IF circ_matchpoint.circulate IS FALSE THEN
398 result.fail_part := 'config.circ_matrix_test.circulate';
399 result.success := FALSE;
404 -- Fail if the total copy-hold ratio is too low
405 IF circ_matchpoint.total_copy_hold_ratio IS NOT NULL THEN
406 SELECT INTO hold_ratio * FROM action.copy_related_hold_stats(match_item);
407 IF hold_ratio.total_copy_ratio IS NOT NULL AND hold_ratio.total_copy_ratio < circ_matchpoint.total_copy_hold_ratio THEN
408 result.fail_part := 'config.circ_matrix_test.total_copy_hold_ratio';
409 result.success := FALSE;
415 -- Fail if the available copy-hold ratio is too low
416 IF circ_matchpoint.available_copy_hold_ratio IS NOT NULL THEN
417 IF hold_ratio.hold_count IS NULL THEN
418 SELECT INTO hold_ratio * FROM action.copy_related_hold_stats(match_item);
420 IF hold_ratio.available_copy_ratio IS NOT NULL AND hold_ratio.available_copy_ratio < circ_matchpoint.available_copy_hold_ratio THEN
421 result.fail_part := 'config.circ_matrix_test.available_copy_hold_ratio';
422 result.success := FALSE;
428 -- Fail if the user has too many items with specific circ_modifiers checked out
429 FOR out_by_circ_mod IN SELECT * FROM config.circ_matrix_circ_mod_test WHERE matchpoint = circ_matchpoint.id LOOP
430 SELECT INTO items_out COUNT(*)
431 FROM action.circulation circ
432 JOIN asset.copy cp ON (cp.id = circ.target_copy)
433 WHERE circ.usr = match_user
434 AND circ.circ_lib IN ( SELECT * FROM unnest(context_org_list) )
435 AND circ.checkin_time IS NULL
436 AND (circ.stop_fines IN ('MAXFINES','LONGOVERDUE') OR circ.stop_fines IS NULL)
437 AND cp.circ_modifier IN (SELECT circ_mod FROM config.circ_matrix_circ_mod_test_map WHERE circ_mod_test = out_by_circ_mod.id);
438 IF items_out >= out_by_circ_mod.items_out THEN
439 result.fail_part := 'config.circ_matrix_circ_mod_test';
440 result.success := FALSE;
446 -- If we passed everything, return the successful matchpoint id
453 $func$ LANGUAGE plpgsql;
455 CREATE OR REPLACE FUNCTION action.item_user_circ_test( INT, BIGINT, INT ) RETURNS SETOF action.circ_matrix_test_result AS $func$
456 SELECT * FROM action.item_user_circ_test( $1, $2, $3, FALSE );
459 CREATE OR REPLACE FUNCTION action.item_user_renew_test( INT, BIGINT, INT ) RETURNS SETOF action.circ_matrix_test_result AS $func$
460 SELECT * FROM action.item_user_circ_test( $1, $2, $3, TRUE );
463 -- Update recurring fine rules
464 UPDATE config.rule_recurring_fine SET grace_period=recurrence_interval;
466 -- Update Circulation Data
467 -- Only update if we were told to and the circ hasn't been checked in
468 UPDATE action.circulation SET grace_period=fine_interval WHERE :CircGrace AND (checkin_time IS NULL);