1 -- Patch from Doug Kyle re: https://bugs.launchpad.net/evergreen/+bug/822918
4 SELECT evergreen.upgrade_deps_block_check('0637', :eg_version);
6 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$
8 user_object actor.usr%ROWTYPE;
9 standing_penalty config.standing_penalty%ROWTYPE;
10 item_object asset.copy%ROWTYPE;
11 item_status_object config.copy_status%ROWTYPE;
12 item_location_object asset.copy_location%ROWTYPE;
13 result action.circ_matrix_test_result;
14 circ_test action.found_circ_matrix_matchpoint;
15 circ_matchpoint config.circ_matrix_matchpoint%ROWTYPE;
16 out_by_circ_mod config.circ_matrix_circ_mod_test%ROWTYPE;
17 circ_mod_map config.circ_matrix_circ_mod_test_map%ROWTYPE;
18 hold_ratio action.hold_stats%ROWTYPE;
21 context_org_list INT[];
24 -- Assume success unless we hit a failure condition
25 result.success := TRUE;
27 -- Need user info to look up matchpoints
28 SELECT INTO user_object * FROM actor.usr WHERE id = match_user AND NOT deleted;
30 -- (Insta)Fail if we couldn't find the user
31 IF user_object.id IS NULL THEN
32 result.fail_part := 'no_user';
33 result.success := FALSE;
39 -- Need item info to look up matchpoints
40 SELECT INTO item_object * FROM asset.copy WHERE id = match_item AND NOT deleted;
42 -- (Insta)Fail if we couldn't find the item
43 IF item_object.id IS NULL THEN
44 result.fail_part := 'no_item';
45 result.success := FALSE;
51 SELECT INTO circ_test * FROM action.find_circ_matrix_matchpoint(circ_ou, item_object, user_object, renewal);
53 circ_matchpoint := circ_test.matchpoint;
54 result.matchpoint := circ_matchpoint.id;
55 result.circulate := circ_matchpoint.circulate;
56 result.duration_rule := circ_matchpoint.duration_rule;
57 result.recurring_fine_rule := circ_matchpoint.recurring_fine_rule;
58 result.max_fine_rule := circ_matchpoint.max_fine_rule;
59 result.hard_due_date := circ_matchpoint.hard_due_date;
60 result.renewals := circ_matchpoint.renewals;
61 result.grace_period := circ_matchpoint.grace_period;
62 result.buildrows := circ_test.buildrows;
64 -- (Insta)Fail if we couldn't find a matchpoint
65 IF circ_test.success = false THEN
66 result.fail_part := 'no_matchpoint';
67 result.success := FALSE;
73 -- All failures before this point are non-recoverable
74 -- Below this point are possibly overridable failures
76 -- Fail if the user is barred
77 IF user_object.barred IS TRUE THEN
78 result.fail_part := 'actor.usr.barred';
79 result.success := FALSE;
84 -- Fail if the item can't circulate
85 IF item_object.circulate IS FALSE THEN
86 result.fail_part := 'asset.copy.circulate';
87 result.success := FALSE;
92 -- Fail if the item isn't in a circulateable status on a non-renewal
93 IF NOT renewal AND item_object.status NOT IN ( 0, 7, 8 ) THEN
94 result.fail_part := 'asset.copy.status';
95 result.success := FALSE;
98 -- Alternately, fail if the item isn't checked out on a renewal
99 ELSIF renewal AND item_object.status <> 1 THEN
100 result.fail_part := 'asset.copy.status';
101 result.success := FALSE;
106 -- Fail if the item can't circulate because of the shelving location
107 SELECT INTO item_location_object * FROM asset.copy_location WHERE id = item_object.location;
108 IF item_location_object.circulate IS FALSE THEN
109 result.fail_part := 'asset.copy_location.circulate';
110 result.success := FALSE;
115 -- Use Circ OU for penalties and such
116 SELECT INTO context_org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( circ_ou );
119 penalty_type = '%RENEW%';
121 penalty_type = '%CIRC%';
124 FOR standing_penalty IN
125 SELECT DISTINCT csp.*
126 FROM actor.usr_standing_penalty usp
127 JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty)
128 WHERE usr = match_user
129 AND usp.org_unit IN ( SELECT * FROM unnest(context_org_list) )
130 AND (usp.stop_date IS NULL or usp.stop_date > NOW())
131 AND csp.block_list LIKE penalty_type LOOP
133 result.fail_part := standing_penalty.name;
134 result.success := FALSE;
139 -- Fail if the test is set to hard non-circulating
140 IF circ_matchpoint.circulate IS FALSE THEN
141 result.fail_part := 'config.circ_matrix_test.circulate';
142 result.success := FALSE;
147 -- Fail if the total copy-hold ratio is too low
148 IF circ_matchpoint.total_copy_hold_ratio IS NOT NULL THEN
149 SELECT INTO hold_ratio * FROM action.copy_related_hold_stats(match_item);
150 IF hold_ratio.total_copy_ratio IS NOT NULL AND hold_ratio.total_copy_ratio < circ_matchpoint.total_copy_hold_ratio THEN
151 result.fail_part := 'config.circ_matrix_test.total_copy_hold_ratio';
152 result.success := FALSE;
158 -- Fail if the available copy-hold ratio is too low
159 IF circ_matchpoint.available_copy_hold_ratio IS NOT NULL THEN
160 IF hold_ratio.hold_count IS NULL THEN
161 SELECT INTO hold_ratio * FROM action.copy_related_hold_stats(match_item);
163 IF hold_ratio.available_copy_ratio IS NOT NULL AND hold_ratio.available_copy_ratio < circ_matchpoint.available_copy_hold_ratio THEN
164 result.fail_part := 'config.circ_matrix_test.available_copy_hold_ratio';
165 result.success := FALSE;
171 -- Fail if the user has too many items with specific circ_modifiers checked out
173 FOR out_by_circ_mod IN SELECT * FROM config.circ_matrix_circ_mod_test WHERE matchpoint = circ_matchpoint.id LOOP
174 SELECT INTO items_out COUNT(*)
175 FROM action.circulation circ
176 JOIN asset.copy cp ON (cp.id = circ.target_copy)
177 WHERE circ.usr = match_user
178 AND circ.circ_lib IN ( SELECT * FROM unnest(context_org_list) )
179 AND circ.checkin_time IS NULL
180 AND (circ.stop_fines IN ('MAXFINES','LONGOVERDUE') OR circ.stop_fines IS NULL)
181 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);
182 IF items_out >= out_by_circ_mod.items_out THEN
183 result.fail_part := 'config.circ_matrix_circ_mod_test';
184 result.success := FALSE;
191 -- If we passed everything, return the successful matchpoint
198 $func$ LANGUAGE plpgsql;