3 INSERT INTO config.upgrade_log (version) VALUES ('0277'); -- miker
5 -- Use oils_xpath_table instead of pgxml's xpath_table
6 CREATE OR REPLACE FUNCTION acq.extract_holding_attr_table (lineitem int, tag text) RETURNS SETOF acq.flat_lineitem_holding_subfield AS $$
9 lida acq.flat_lineitem_holding_subfield%ROWTYPE;
12 SELECT COUNT(*) INTO counter
13 FROM oils_xpath_table(
17 '//*[@tag="' || tag || '"]',
21 FOR i IN 1 .. counter LOOP
24 FROM ( SELECT id,i,t,v
25 FROM oils_xpath_table(
29 '//*[@tag="' || tag || '"][position()=' || i || ']/*/@code|' ||
30 '//*[@tag="' || tag || '"][position()=' || i || ']/*[@code]',
32 ) as t(id int,t text,v text)
43 -- Use the identifier search class tsconfig
44 DROP TRIGGER metabib_identifier_field_entry_fti_trigger ON metabib.identifier_field_entry;
45 CREATE TRIGGER metabib_identifier_field_entry_fti_trigger
46 BEFORE INSERT OR UPDATE ON metabib.identifier_field_entry
48 EXECUTE PROCEDURE public.oils_tsearch2('identifier');
50 -- Return the correct fail_part when the item can't be found
51 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$
53 user_object actor.usr%ROWTYPE;
54 standing_penalty config.standing_penalty%ROWTYPE;
55 item_object asset.copy%ROWTYPE;
56 item_status_object config.copy_status%ROWTYPE;
57 item_location_object asset.copy_location%ROWTYPE;
58 result action.matrix_test_result;
59 circ_test config.circ_matrix_matchpoint%ROWTYPE;
60 out_by_circ_mod config.circ_matrix_circ_mod_test%ROWTYPE;
61 circ_mod_map config.circ_matrix_circ_mod_test_map%ROWTYPE;
62 hold_ratio action.hold_stats%ROWTYPE;
66 context_org_list INT[];
69 result.success := TRUE;
71 -- Fail if the user is BARRED
72 SELECT INTO user_object * FROM actor.usr WHERE id = match_user;
74 -- Fail if we couldn't find the user
75 IF user_object.id IS NULL THEN
76 result.fail_part := 'no_user';
77 result.success := FALSE;
83 SELECT INTO item_object * FROM asset.copy WHERE id = match_item;
85 -- Fail if we couldn't find the item
86 IF item_object.id IS NULL THEN
87 result.fail_part := 'no_item';
88 result.success := FALSE;
94 SELECT INTO circ_test * FROM action.find_circ_matrix_matchpoint(circ_ou, match_item, match_user, renewal);
95 result.matchpoint := circ_test.id;
97 -- Fail if we couldn't find a matchpoint
98 IF result.matchpoint IS NULL THEN
99 result.fail_part := 'no_matchpoint';
100 result.success := FALSE;
105 IF user_object.barred IS TRUE THEN
106 result.fail_part := 'actor.usr.barred';
107 result.success := FALSE;
112 -- Fail if the item can't circulate
113 IF item_object.circulate IS FALSE THEN
114 result.fail_part := 'asset.copy.circulate';
115 result.success := FALSE;
120 -- Fail if the item isn't in a circulateable status on a non-renewal
121 IF NOT renewal AND item_object.status NOT IN ( 0, 7, 8 ) THEN
122 result.fail_part := 'asset.copy.status';
123 result.success := FALSE;
126 ELSIF renewal AND item_object.status <> 1 THEN
127 result.fail_part := 'asset.copy.status';
128 result.success := FALSE;
133 -- Fail if the item can't circulate because of the shelving location
134 SELECT INTO item_location_object * FROM asset.copy_location WHERE id = item_object.location;
135 IF item_location_object.circulate IS FALSE THEN
136 result.fail_part := 'asset.copy_location.circulate';
137 result.success := FALSE;
142 SELECT INTO context_org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( circ_test.org_unit );
144 -- Fail if the test is set to hard non-circulating
145 IF circ_test.circulate IS FALSE THEN
146 result.fail_part := 'config.circ_matrix_test.circulate';
147 result.success := FALSE;
152 -- Fail if the total copy-hold ratio is too low
153 IF circ_test.total_copy_hold_ratio IS NOT NULL THEN
154 SELECT INTO hold_ratio * FROM action.copy_related_hold_stats(match_item);
155 IF hold_ratio.total_copy_ratio IS NOT NULL AND hold_ratio.total_copy_ratio < circ_test.total_copy_hold_ratio THEN
156 result.fail_part := 'config.circ_matrix_test.total_copy_hold_ratio';
157 result.success := FALSE;
163 -- Fail if the available copy-hold ratio is too low
164 IF circ_test.available_copy_hold_ratio IS NOT NULL THEN
165 SELECT INTO hold_ratio * FROM action.copy_related_hold_stats(match_item);
166 IF hold_ratio.available_copy_ratio IS NOT NULL AND hold_ratio.available_copy_ratio < circ_test.available_copy_hold_ratio THEN
167 result.fail_part := 'config.circ_matrix_test.available_copy_hold_ratio';
168 result.success := FALSE;
175 penalty_type = '%RENEW%';
177 penalty_type = '%CIRC%';
180 FOR standing_penalty IN
181 SELECT DISTINCT csp.*
182 FROM actor.usr_standing_penalty usp
183 JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty)
184 WHERE usr = match_user
185 AND usp.org_unit IN ( SELECT * FROM explode_array(context_org_list) )
186 AND (usp.stop_date IS NULL or usp.stop_date > NOW())
187 AND csp.block_list LIKE penalty_type LOOP
189 result.fail_part := standing_penalty.name;
190 result.success := FALSE;
195 -- Fail if the user has too many items with specific circ_modifiers checked out
196 FOR out_by_circ_mod IN SELECT * FROM config.circ_matrix_circ_mod_test WHERE matchpoint = circ_test.id LOOP
197 SELECT INTO items_out COUNT(*)
198 FROM action.circulation circ
199 JOIN asset.copy cp ON (cp.id = circ.target_copy)
200 WHERE circ.usr = match_user
201 AND circ.circ_lib IN ( SELECT * FROM explode_array(context_org_list) )
202 AND circ.checkin_time IS NULL
203 AND (circ.stop_fines IN ('MAXFINES','LONGOVERDUE') OR circ.stop_fines IS NULL)
204 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);
205 IF items_out >= out_by_circ_mod.items_out THEN
206 result.fail_part := 'config.circ_matrix_circ_mod_test';
207 result.success := FALSE;
213 -- If we passed everything, return the successful matchpoint id
220 $func$ LANGUAGE plpgsql;