4 INSERT INTO config.upgrade_log (version) VALUES ('0068');
6 ALTER TABLE config.circ_matrix_matchpoint ADD COLUMN total_copy_hold_ratio FLOAT;
7 ALTER TABLE config.circ_matrix_matchpoint ADD COLUMN available_copy_hold_ratio FLOAT;
9 CREATE OR REPLACE FUNCTION action.item_user_circ_test( circ_ou INT, match_item BIGINT, match_user INT, renewal BOOL ) RETURNS SETOF action.matrix_test_result AS $func$
11 user_object actor.usr%ROWTYPE;
12 standing_penalty config.standing_penalty%ROWTYPE;
13 item_object asset.copy%ROWTYPE;
14 item_status_object config.copy_status%ROWTYPE;
15 item_location_object asset.copy_location%ROWTYPE;
16 result action.matrix_test_result;
17 circ_test config.circ_matrix_matchpoint%ROWTYPE;
18 out_by_circ_mod config.circ_matrix_circ_mod_test%ROWTYPE;
19 circ_mod_map config.circ_matrix_circ_mod_test_map%ROWTYPE;
20 hold_ratio action.hold_stats%ROWTYPE;
24 context_org_list INT[];
27 result.success := TRUE;
29 -- Fail if the user is BARRED
30 SELECT INTO user_object * FROM actor.usr WHERE id = match_user;
32 -- Fail if we couldn't find a set of tests
33 IF user_object.id IS NULL THEN
34 result.fail_part := 'no_user';
35 result.success := FALSE;
41 IF user_object.barred IS TRUE THEN
42 result.fail_part := 'actor.usr.barred';
43 result.success := FALSE;
48 -- Fail if the item can't circulate
49 SELECT INTO item_object * FROM asset.copy WHERE id = match_item;
50 IF item_object.circulate IS FALSE THEN
51 result.fail_part := 'asset.copy.circulate';
52 result.success := FALSE;
57 -- Fail if the item isn't in a circulateable status on a non-renewal
58 IF NOT renewal AND item_object.status NOT IN ( 0, 7, 8 ) THEN
59 result.fail_part := 'asset.copy.status';
60 result.success := FALSE;
63 ELSIF renewal AND item_object.status <> 1 THEN
64 result.fail_part := 'asset.copy.status';
65 result.success := FALSE;
70 -- Fail if the item can't circulate because of the shelving location
71 SELECT INTO item_location_object * FROM asset.copy_location WHERE id = item_object.location;
72 IF item_location_object.circulate IS FALSE THEN
73 result.fail_part := 'asset.copy_location.circulate';
74 result.success := FALSE;
79 SELECT INTO circ_test * FROM action.find_circ_matrix_matchpoint(circ_ou, match_item, match_user, renewal);
80 result.matchpoint := circ_test.id;
82 SELECT INTO context_org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( circ_test.org_unit );
84 -- Fail if we couldn't find a set of tests
85 IF result.matchpoint IS NULL THEN
86 result.fail_part := 'no_matchpoint';
87 result.success := FALSE;
92 -- Fail if the test is set to hard non-circulating
93 IF circ_test.circulate IS FALSE THEN
94 result.fail_part := 'config.circ_matrix_test.circulate';
95 result.success := FALSE;
100 -- Fail if the total copy-hold ratio is too low
101 IF circ_test.total_copy_hold_ratio IS NOT NULL THEN
102 SELECT INTO hold_ratio * FROM action.copy_related_hold_stats(match_item);
103 IF hold_ratio.total_copy_ratio IS NOT NULL AND hold_ratio.total_copy_ratio < circ_test.total_copy_hold_ratio THEN
104 result.fail_part := 'config.circ_matrix_test.total_copy_hold_ratio';
105 result.success := FALSE;
111 -- Fail if the available copy-hold ratio is too low
112 IF circ_test.available_copy_hold_ratio IS NOT NULL THEN
113 SELECT INTO hold_ratio * FROM action.copy_related_hold_stats(match_item);
114 IF hold_ratio.available_copy_ratio IS NOT NULL AND hold_ratio.available_copy_ratio < circ_test.available_copy_hold_ratio THEN
115 result.fail_part := 'config.circ_matrix_test.available_copy_hold_ratio';
116 result.success := FALSE;
123 penalty_type = '%RENEW%';
125 penalty_type = '%CIRC%';
128 FOR standing_penalty IN
129 SELECT DISTINCT csp.*
130 FROM actor.usr_standing_penalty usp
131 JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty)
132 WHERE usr = match_user
133 AND usp.org_unit IN ( SELECT * FROM explode_array(context_org_list) )
134 AND (usp.stop_date IS NULL or usp.stop_date > NOW())
135 AND csp.block_list LIKE penalty_type LOOP
137 result.fail_part := standing_penalty.name;
138 result.success := FALSE;
143 -- Fail if the user has too many items with specific circ_modifiers checked out
144 FOR out_by_circ_mod IN SELECT * FROM config.circ_matrix_circ_mod_test WHERE matchpoint = circ_test.id LOOP
145 SELECT INTO items_out COUNT(*)
146 FROM action.circulation circ
147 JOIN asset.copy cp ON (cp.id = circ.target_copy)
148 WHERE circ.usr = match_user
149 AND circ.circ_lib IN ( SELECT * FROM explode_array(context_org_list) )
150 AND circ.checkin_time IS NULL
151 AND (circ.stop_fines IN ('MAXFINES','LONGOVERDUE') OR circ.stop_fines IS NULL)
152 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);
153 IF items_out >= out_by_circ_mod.items_out THEN
154 result.fail_part := 'config.circ_matrix_circ_mod_test';
155 result.success := FALSE;
161 -- If we passed everything, return the successful matchpoint id
168 $func$ LANGUAGE plpgsql;