From 30fcf8a643346995c35f6528c6667680a51e319a Mon Sep 17 00:00:00 2001 From: miker Date: Tue, 25 Nov 2008 15:41:12 +0000 Subject: [PATCH] clean up hold-related standing penalties (still need to make max holds an SP); also, tab-to-space git-svn-id: svn://svn.open-ils.org/ILS/trunk@11337 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/src/sql/Pg/100.circ_matrix.sql | 536 +++++++++++++++---------------- Open-ILS/src/sql/Pg/110.hold_matrix.sql | 552 ++++++++++++++++---------------- 2 files changed, 545 insertions(+), 543 deletions(-) diff --git a/Open-ILS/src/sql/Pg/100.circ_matrix.sql b/Open-ILS/src/sql/Pg/100.circ_matrix.sql index 6a3226bc36..8235c2c127 100644 --- a/Open-ILS/src/sql/Pg/100.circ_matrix.sql +++ b/Open-ILS/src/sql/Pg/100.circ_matrix.sql @@ -11,11 +11,11 @@ LANGUAGE 'sql' IMMUTABLE; -- New table needed to handle circ modifiers inside the DB. Will still require -- central admin. The circ_modifier column on asset.copy will become an fkey to this table. CREATE TABLE config.circ_modifier ( - code TEXT PRIMARY KEY, - name TEXT UNIQUE NOT NULL, - description TEXT NOT NULL, - sip2_media_type TEXT NOT NULL, - magnetic_media BOOL NOT NULL DEFAULT TRUE + code TEXT PRIMARY KEY, + name TEXT UNIQUE NOT NULL, + description TEXT NOT NULL, + sip2_media_type TEXT NOT NULL, + magnetic_media BOOL NOT NULL DEFAULT TRUE ); /* @@ -48,8 +48,8 @@ ALTER TABLE asset.copy ADD CONSTRAINT circ_mod_fkey FOREIGN KEY (circ_modifier) -- config table to hold the vr_format names CREATE TABLE config.videorecording_format_map ( - code TEXT PRIMARY KEY, - value TEXT NOT NULL + code TEXT PRIMARY KEY, + value TEXT NOT NULL ); INSERT INTO config.videorecording_format_map VALUES ('a','Beta'); @@ -96,113 +96,113 @@ INSERT INTO config.videorecording_format_map VALUES ('z','Other'); -- CREATE TABLE config.circ_matrix_matchpoint ( - id SERIAL PRIMARY KEY, - active BOOL NOT NULL DEFAULT TRUE, - org_unit INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED, -- Set to the top OU for the matchpoint applicability range; we can use org_unit_prox to choose the "best" - grp INT NOT NULL REFERENCES permission.grp_tree (id) DEFERRABLE INITIALLY DEFERRED, -- Set to the top applicable group from the group tree; will need descendents and prox functions for filtering - circ_modifier TEXT REFERENCES config.circ_modifier (code) DEFERRABLE INITIALLY DEFERRED, - marc_type TEXT REFERENCES config.item_type_map (code) DEFERRABLE INITIALLY DEFERRED, - marc_form TEXT REFERENCES config.item_form_map (code) DEFERRABLE INITIALLY DEFERRED, - marc_vr_format TEXT REFERENCES config.videorecording_format_map (code) DEFERRABLE INITIALLY DEFERRED, - ref_flag BOOL, - is_renewal BOOL, - usr_age_lower_bound INTERVAL, - usr_age_upper_bound INTERVAL, - circulate BOOL NOT NULL DEFAULT TRUE, -- Hard "can't circ" flag requiring an override - duration_rule INT NOT NULL REFERENCES config.rule_circ_duration (id) DEFERRABLE INITIALLY DEFERRED, - recurring_fine_rule INT NOT NULL REFERENCES config.rule_recuring_fine (id) DEFERRABLE INITIALLY DEFERRED, - max_fine_rule INT NOT NULL REFERENCES config.rule_max_fine (id) DEFERRABLE INITIALLY DEFERRED, - script_test TEXT, -- javascript source - CONSTRAINT ep_once_per_grp_loc_mod_marc UNIQUE (grp, org_unit, circ_modifier, marc_type, marc_form, marc_vr_format, ref_flag, usr_age_lower_bound, usr_age_upper_bound, is_renewal) + id SERIAL PRIMARY KEY, + active BOOL NOT NULL DEFAULT TRUE, + org_unit INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED, -- Set to the top OU for the matchpoint applicability range; we can use org_unit_prox to choose the "best" + grp INT NOT NULL REFERENCES permission.grp_tree (id) DEFERRABLE INITIALLY DEFERRED, -- Set to the top applicable group from the group tree; will need descendents and prox functions for filtering + circ_modifier TEXT REFERENCES config.circ_modifier (code) DEFERRABLE INITIALLY DEFERRED, + marc_type TEXT REFERENCES config.item_type_map (code) DEFERRABLE INITIALLY DEFERRED, + marc_form TEXT REFERENCES config.item_form_map (code) DEFERRABLE INITIALLY DEFERRED, + marc_vr_format TEXT REFERENCES config.videorecording_format_map (code) DEFERRABLE INITIALLY DEFERRED, + ref_flag BOOL, + is_renewal BOOL, + usr_age_lower_bound INTERVAL, + usr_age_upper_bound INTERVAL, + circulate BOOL NOT NULL DEFAULT TRUE, -- Hard "can't circ" flag requiring an override + duration_rule INT NOT NULL REFERENCES config.rule_circ_duration (id) DEFERRABLE INITIALLY DEFERRED, + recurring_fine_rule INT NOT NULL REFERENCES config.rule_recuring_fine (id) DEFERRABLE INITIALLY DEFERRED, + max_fine_rule INT NOT NULL REFERENCES config.rule_max_fine (id) DEFERRABLE INITIALLY DEFERRED, + script_test TEXT, -- javascript source + CONSTRAINT ep_once_per_grp_loc_mod_marc UNIQUE (grp, org_unit, circ_modifier, marc_type, marc_form, marc_vr_format, ref_flag, usr_age_lower_bound, usr_age_upper_bound, is_renewal) ); -- Tests for max items out by circ_modifier CREATE TABLE config.circ_matrix_circ_mod_test ( - id SERIAL PRIMARY KEY, - matchpoint INT NOT NULL REFERENCES config.circ_matrix_matchpoint (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, - items_out INT NOT NULL, -- Total current active circulations must be less than this, NULL means skip (always pass) - circ_mod TEXT NOT NULL REFERENCES config.circ_modifier (code) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED-- circ_modifier type that the max out applies to + id SERIAL PRIMARY KEY, + matchpoint INT NOT NULL REFERENCES config.circ_matrix_matchpoint (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, + items_out INT NOT NULL, -- Total current active circulations must be less than this, NULL means skip (always pass) + circ_mod TEXT NOT NULL REFERENCES config.circ_modifier (code) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED-- circ_modifier type that the max out applies to ); CREATE OR REPLACE FUNCTION action.find_circ_matrix_matchpoint( context_ou INT, match_item BIGINT, match_user INT, renewal BOOL ) RETURNS config.circ_matrix_matchpoint AS $func$ DECLARE - current_group permission.grp_tree%ROWTYPE; - user_object actor.usr%ROWTYPE; - item_object asset.copy%ROWTYPE; - rec_descriptor metabib.rec_descriptor%ROWTYPE; - current_mp config.circ_matrix_matchpoint%ROWTYPE; - matchpoint config.circ_matrix_matchpoint%ROWTYPE; + current_group permission.grp_tree%ROWTYPE; + user_object actor.usr%ROWTYPE; + item_object asset.copy%ROWTYPE; + rec_descriptor metabib.rec_descriptor%ROWTYPE; + current_mp config.circ_matrix_matchpoint%ROWTYPE; + matchpoint config.circ_matrix_matchpoint%ROWTYPE; BEGIN - SELECT INTO user_object * FROM actor.usr WHERE id = match_user; - SELECT INTO item_object * FROM asset.copy WHERE id = match_item; - SELECT INTO rec_descriptor r.* FROM metabib.rec_descriptor r JOIN asset.call_number c USING (record) WHERE c.id = item_object.call_number; - SELECT INTO current_group * FROM permission.grp_tree WHERE id = user_object.profile; - - LOOP - -- for each potential matchpoint for this ou and group ... - FOR current_mp IN - SELECT m.* - FROM config.circ_matrix_matchpoint m - JOIN actor.org_unit_ancestors( context_ou ) d ON (m.org_unit = d.id) - LEFT JOIN actor.org_unit_proximity p ON (p.from_org = context_ou AND p.to_org = d.id) - WHERE m.grp = current_group.id AND m.active - ORDER BY CASE WHEN p.prox IS NULL THEN 999 ELSE p.prox END, - CASE WHEN m.is_renewal = renewal THEN 64 ELSE 0 END + - CASE WHEN m.circ_modifier IS NOT NULL THEN 32 ELSE 0 END + - CASE WHEN m.marc_type IS NOT NULL THEN 16 ELSE 0 END + - CASE WHEN m.marc_form IS NOT NULL THEN 8 ELSE 0 END + - CASE WHEN m.marc_vr_format IS NOT NULL THEN 4 ELSE 0 END + - CASE WHEN m.ref_flag IS NOT NULL THEN 2 ELSE 0 END + - CASE WHEN m.usr_age_lower_bound IS NOT NULL THEN 0.5 ELSE 0 END + - CASE WHEN m.usr_age_upper_bound IS NOT NULL THEN 0.5 ELSE 0 END DESC LOOP - - IF current_mp.circ_modifier IS NOT NULL THEN - CONTINUE WHEN current_mp.circ_modifier <> item_object.circ_modifier; - END IF; - - IF current_mp.marc_type IS NOT NULL THEN - IF item_object.circ_as_type IS NOT NULL THEN - CONTINUE WHEN current_mp.marc_type <> item_object.circ_as_type; - ELSE - CONTINUE WHEN current_mp.marc_type <> rec_descriptor.item_type; - END IF; - END IF; - - IF current_mp.marc_form IS NOT NULL THEN - CONTINUE WHEN current_mp.marc_form <> rec_descriptor.item_form; - END IF; - - IF current_mp.marc_vr_format IS NOT NULL THEN - CONTINUE WHEN current_mp.marc_vr_format <> rec_descriptor.vr_format; - END IF; - - IF current_mp.ref_flag IS NOT NULL THEN - CONTINUE WHEN current_mp.ref_flag <> item_object.ref; - END IF; - - IF current_mp.usr_age_lower_bound IS NOT NULL THEN - CONTINUE WHEN user_object.dob IS NULL OR current_mp.usr_age_lower_bound < age(user_object.dob); - END IF; - - IF current_mp.usr_age_upper_bound IS NOT NULL THEN - CONTINUE WHEN user_object.dob IS NULL OR current_mp.usr_age_upper_bound > age(user_object.dob); - END IF; - - - -- everything was undefined or matched - matchpoint = current_mp; - - EXIT WHEN matchpoint.id IS NOT NULL; - END LOOP; - - EXIT WHEN current_group.parent IS NULL OR matchpoint.id IS NOT NULL; - - SELECT INTO current_group * FROM permission.grp_tree WHERE id = current_group.parent; - END LOOP; - - RETURN matchpoint; + SELECT INTO user_object * FROM actor.usr WHERE id = match_user; + SELECT INTO item_object * FROM asset.copy WHERE id = match_item; + SELECT INTO rec_descriptor r.* FROM metabib.rec_descriptor r JOIN asset.call_number c USING (record) WHERE c.id = item_object.call_number; + SELECT INTO current_group * FROM permission.grp_tree WHERE id = user_object.profile; + + LOOP + -- for each potential matchpoint for this ou and group ... + FOR current_mp IN + SELECT m.* + FROM config.circ_matrix_matchpoint m + JOIN actor.org_unit_ancestors( context_ou ) d ON (m.org_unit = d.id) + LEFT JOIN actor.org_unit_proximity p ON (p.from_org = context_ou AND p.to_org = d.id) + WHERE m.grp = current_group.id AND m.active + ORDER BY CASE WHEN p.prox IS NULL THEN 999 ELSE p.prox END, + CASE WHEN m.is_renewal = renewal THEN 64 ELSE 0 END + + CASE WHEN m.circ_modifier IS NOT NULL THEN 32 ELSE 0 END + + CASE WHEN m.marc_type IS NOT NULL THEN 16 ELSE 0 END + + CASE WHEN m.marc_form IS NOT NULL THEN 8 ELSE 0 END + + CASE WHEN m.marc_vr_format IS NOT NULL THEN 4 ELSE 0 END + + CASE WHEN m.ref_flag IS NOT NULL THEN 2 ELSE 0 END + + CASE WHEN m.usr_age_lower_bound IS NOT NULL THEN 0.5 ELSE 0 END + + CASE WHEN m.usr_age_upper_bound IS NOT NULL THEN 0.5 ELSE 0 END DESC LOOP + + IF current_mp.circ_modifier IS NOT NULL THEN + CONTINUE WHEN current_mp.circ_modifier <> item_object.circ_modifier; + END IF; + + IF current_mp.marc_type IS NOT NULL THEN + IF item_object.circ_as_type IS NOT NULL THEN + CONTINUE WHEN current_mp.marc_type <> item_object.circ_as_type; + ELSE + CONTINUE WHEN current_mp.marc_type <> rec_descriptor.item_type; + END IF; + END IF; + + IF current_mp.marc_form IS NOT NULL THEN + CONTINUE WHEN current_mp.marc_form <> rec_descriptor.item_form; + END IF; + + IF current_mp.marc_vr_format IS NOT NULL THEN + CONTINUE WHEN current_mp.marc_vr_format <> rec_descriptor.vr_format; + END IF; + + IF current_mp.ref_flag IS NOT NULL THEN + CONTINUE WHEN current_mp.ref_flag <> item_object.ref; + END IF; + + IF current_mp.usr_age_lower_bound IS NOT NULL THEN + CONTINUE WHEN user_object.dob IS NULL OR current_mp.usr_age_lower_bound < age(user_object.dob); + END IF; + + IF current_mp.usr_age_upper_bound IS NOT NULL THEN + CONTINUE WHEN user_object.dob IS NULL OR current_mp.usr_age_upper_bound > age(user_object.dob); + END IF; + + + -- everything was undefined or matched + matchpoint = current_mp; + + EXIT WHEN matchpoint.id IS NOT NULL; + END LOOP; + + EXIT WHEN current_group.parent IS NULL OR matchpoint.id IS NOT NULL; + + SELECT INTO current_group * FROM permission.grp_tree WHERE id = current_group.parent; + END LOOP; + + RETURN matchpoint; END; $func$ LANGUAGE plpgsql; @@ -210,92 +210,92 @@ $func$ LANGUAGE plpgsql; CREATE TYPE action.matrix_test_result AS ( success BOOL, matchpoint INT, fail_part TEXT ); 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$ DECLARE - user_object actor.usr%ROWTYPE; - standing_penalty config.standing_penalty%ROWTYPE; - item_object asset.copy%ROWTYPE; - item_status_object config.copy_status%ROWTYPE; - item_location_object asset.copy_location%ROWTYPE; - result action.matrix_test_result; - circ_test config.circ_matrix_matchpoint%ROWTYPE; - out_by_circ_mod config.circ_matrix_circ_mod_test%ROWTYPE; - penalty_type TEXT; - tmp_grp INT; - items_out INT; - context_org_list INT[]; - done BOOL := FALSE; + user_object actor.usr%ROWTYPE; + standing_penalty config.standing_penalty%ROWTYPE; + item_object asset.copy%ROWTYPE; + item_status_object config.copy_status%ROWTYPE; + item_location_object asset.copy_location%ROWTYPE; + result action.matrix_test_result; + circ_test config.circ_matrix_matchpoint%ROWTYPE; + out_by_circ_mod config.circ_matrix_circ_mod_test%ROWTYPE; + penalty_type TEXT; + tmp_grp INT; + items_out INT; + context_org_list INT[]; + done BOOL := FALSE; BEGIN - result.success := TRUE; - - -- Fail if the user is BARRED - SELECT INTO user_object * FROM actor.usr WHERE id = match_user; - - -- Fail if we couldn't find a set of tests - IF user_object.id IS NULL THEN - result.fail_part := 'no_user'; - result.success := FALSE; - done := TRUE; - RETURN NEXT result; - RETURN; - END IF; - - IF user_object.barred IS TRUE THEN - result.fail_part := 'actor.usr.barred'; - result.success := FALSE; - done := TRUE; - RETURN NEXT result; - END IF; - - -- Fail if the item can't circulate - SELECT INTO item_object * FROM asset.copy WHERE id = match_item; - IF item_object.circulate IS FALSE THEN - result.fail_part := 'asset.copy.circulate'; - result.success := FALSE; - done := TRUE; - RETURN NEXT result; - END IF; - - -- Fail if the item isn't in a circulateable status on a non-renewal - IF NOT renewal AND item_object.status NOT IN ( 0, 7, 8 ) THEN - result.fail_part := 'asset.copy.status'; - result.success := FALSE; - done := TRUE; - RETURN NEXT result; - ELSIF renewal AND item_object.status <> 1 THEN - result.fail_part := 'asset.copy.status'; - result.success := FALSE; - done := TRUE; - RETURN NEXT result; - END IF; - - -- Fail if the item can't circulate because of the shelving location - SELECT INTO item_location_object * FROM asset.copy_location WHERE id = item_object.location; - IF item_location_object.circulate IS FALSE THEN - result.fail_part := 'asset.copy_location.circulate'; - result.success := FALSE; - done := TRUE; - RETURN NEXT result; - END IF; - - SELECT INTO circ_test * FROM action.find_circ_matrix_matchpoint(circ_ou, match_item, match_user, renewal); - result.matchpoint := circ_test.id; - - SELECT INTO context_org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( circ_test.org_unit ); - - -- Fail if we couldn't find a set of tests - IF result.matchpoint IS NULL THEN - result.fail_part := 'no_matchpoint'; - result.success := FALSE; - done := TRUE; - RETURN NEXT result; - END IF; - - -- Fail if the test is set to hard non-circulating - IF circ_test.circulate IS FALSE THEN - result.fail_part := 'config.circ_matrix_test.circulate'; - result.success := FALSE; - done := TRUE; - RETURN NEXT result; - END IF; + result.success := TRUE; + + -- Fail if the user is BARRED + SELECT INTO user_object * FROM actor.usr WHERE id = match_user; + + -- Fail if we couldn't find a set of tests + IF user_object.id IS NULL THEN + result.fail_part := 'no_user'; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + RETURN; + END IF; + + IF user_object.barred IS TRUE THEN + result.fail_part := 'actor.usr.barred'; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + END IF; + + -- Fail if the item can't circulate + SELECT INTO item_object * FROM asset.copy WHERE id = match_item; + IF item_object.circulate IS FALSE THEN + result.fail_part := 'asset.copy.circulate'; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + END IF; + + -- Fail if the item isn't in a circulateable status on a non-renewal + IF NOT renewal AND item_object.status NOT IN ( 0, 7, 8 ) THEN + result.fail_part := 'asset.copy.status'; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + ELSIF renewal AND item_object.status <> 1 THEN + result.fail_part := 'asset.copy.status'; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + END IF; + + -- Fail if the item can't circulate because of the shelving location + SELECT INTO item_location_object * FROM asset.copy_location WHERE id = item_object.location; + IF item_location_object.circulate IS FALSE THEN + result.fail_part := 'asset.copy_location.circulate'; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + END IF; + + SELECT INTO circ_test * FROM action.find_circ_matrix_matchpoint(circ_ou, match_item, match_user, renewal); + result.matchpoint := circ_test.id; + + SELECT INTO context_org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( circ_test.org_unit ); + + -- Fail if we couldn't find a set of tests + IF result.matchpoint IS NULL THEN + result.fail_part := 'no_matchpoint'; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + END IF; + + -- Fail if the test is set to hard non-circulating + IF circ_test.circulate IS FALSE THEN + result.fail_part := 'config.circ_matrix_test.circulate'; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + END IF; IF renewal THEN penalty_type = '%RENEW%'; @@ -317,66 +317,66 @@ BEGIN RETURN NEXT result; END LOOP; - -- Fail if the user has too many items with specific circ_modifiers checked out - FOR out_by_circ_mod IN SELECT * FROM config.circ_matrix_circ_mod_test WHERE matchpoint = circ_test.id LOOP - SELECT INTO items_out COUNT(*) - FROM action.circulation circ - JOIN asset.copy cp ON (cp.id = circ.target_copy) - WHERE circ.usr = match_user - AND circ_lib IN ( SELECT * FROM explode_array(context_org_list) ) - AND circ.checkin_time IS NULL - AND (circ.stop_fines NOT IN ('LOST','CLAIMSRETURNED','LONGOVERDUE') OR circ.stop_fines IS NULL) - AND cp.circ_modifier = out_by_circ_mod.circ_mod; - IF items_out >= out_by_circ_mod.items_out THEN - result.fail_part := 'config.circ_matrix_circ_mod_test'; - result.success := FALSE; - done := TRUE; - RETURN NEXT result; - END IF; - END LOOP; - - -- If we passed everything, return the successful matchpoint id - IF NOT done THEN - RETURN NEXT result; - END IF; - - RETURN; + -- Fail if the user has too many items with specific circ_modifiers checked out + FOR out_by_circ_mod IN SELECT * FROM config.circ_matrix_circ_mod_test WHERE matchpoint = circ_test.id LOOP + SELECT INTO items_out COUNT(*) + FROM action.circulation circ + JOIN asset.copy cp ON (cp.id = circ.target_copy) + WHERE circ.usr = match_user + AND circ_lib IN ( SELECT * FROM explode_array(context_org_list) ) + AND circ.checkin_time IS NULL + AND (circ.stop_fines NOT IN ('LOST','CLAIMSRETURNED','LONGOVERDUE') OR circ.stop_fines IS NULL) + AND cp.circ_modifier = out_by_circ_mod.circ_mod; + IF items_out >= out_by_circ_mod.items_out THEN + result.fail_part := 'config.circ_matrix_circ_mod_test'; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + END IF; + END LOOP; + + -- If we passed everything, return the successful matchpoint id + IF NOT done THEN + RETURN NEXT result; + END IF; + + RETURN; END; $func$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION action.item_user_circ_test( INT, BIGINT, INT ) RETURNS SETOF action.matrix_test_result AS $func$ - SELECT * FROM action.item_user_circ_test( $1, $2, $3, FALSE ); + SELECT * FROM action.item_user_circ_test( $1, $2, $3, FALSE ); $func$ LANGUAGE SQL; CREATE OR REPLACE FUNCTION action.item_user_renew_test( INT, BIGINT, INT ) RETURNS SETOF action.matrix_test_result AS $func$ - SELECT * FROM action.item_user_circ_test( $1, $2, $3, TRUE ); + SELECT * FROM action.item_user_circ_test( $1, $2, $3, TRUE ); $func$ LANGUAGE SQL; CREATE OR REPLACE FUNCTION actor.calculate_system_penalties( match_user INT, context_org INT ) RETURNS SETOF actor.usr_standing_penalty AS $func$ DECLARE - user_object actor.usr%ROWTYPE; - new_sp_row actor.usr_standing_penalty%ROWTYPE; - existing_sp_row actor.usr_standing_penalty%ROWTYPE; + user_object actor.usr%ROWTYPE; + new_sp_row actor.usr_standing_penalty%ROWTYPE; + existing_sp_row actor.usr_standing_penalty%ROWTYPE; max_fines permission.grp_penalty_threshold%ROWTYPE; max_overdue permission.grp_penalty_threshold%ROWTYPE; max_items_out permission.grp_penalty_threshold%ROWTYPE; - tmp_grp INT; - items_overdue INT; - items_out INT; - context_org_list INT[]; - current_fines NUMERIC(8,2) := 0.0; - tmp_fines NUMERIC(8,2); - tmp_groc RECORD; - tmp_circ RECORD; - tmp_org actor.org_unit%ROWTYPE; + tmp_grp INT; + items_overdue INT; + items_out INT; + context_org_list INT[]; + current_fines NUMERIC(8,2) := 0.0; + tmp_fines NUMERIC(8,2); + tmp_groc RECORD; + tmp_circ RECORD; + tmp_org actor.org_unit%ROWTYPE; BEGIN - SELECT INTO user_object * FROM actor.usr WHERE id = match_user; + SELECT INTO user_object * FROM actor.usr WHERE id = match_user; -- Max fines - SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org; + SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org; - -- Fail if the user has a high fine balance + -- Fail if the user has a high fine balance LOOP tmp_grp := user_object.profile; LOOP @@ -397,11 +397,11 @@ BEGIN EXIT; END IF; - SELECT * INTO tmp_org FROM actor.org_unit WHERE id = tmp_org.parent_ou; + SELECT * INTO tmp_org FROM actor.org_unit WHERE id = tmp_org.parent_ou; END LOOP; - IF max_fines.threshold IS NOT NULL THEN + IF max_fines.threshold IS NOT NULL THEN FOR existing_sp_row IN SELECT * @@ -413,44 +413,44 @@ BEGIN RETURN NEXT existing_sp_row; END LOOP; - FOR tmp_groc IN + FOR tmp_groc IN SELECT * FROM money.grocery g JOIN actor.org_unit_full_path( max_fines.org_unit ) fp ON (g.billing_location = fp.id) WHERE usr = match_user AND xact_finish IS NULL LOOP - SELECT INTO tmp_fines SUM( amount ) FROM money.billing WHERE xact = tmp_groc.id AND NOT voided; - current_fines = current_fines + COALESCE(tmp_fines, 0.0); - SELECT INTO tmp_fines SUM( amount ) FROM money.payment WHERE xact = tmp_groc.id AND NOT voided; - current_fines = current_fines - COALESCE(tmp_fines, 0.0); - END LOOP; + SELECT INTO tmp_fines SUM( amount ) FROM money.billing WHERE xact = tmp_groc.id AND NOT voided; + current_fines = current_fines + COALESCE(tmp_fines, 0.0); + SELECT INTO tmp_fines SUM( amount ) FROM money.payment WHERE xact = tmp_groc.id AND NOT voided; + current_fines = current_fines - COALESCE(tmp_fines, 0.0); + END LOOP; - FOR tmp_circ IN + FOR tmp_circ IN SELECT * FROM action.circulation circ JOIN actor.org_unit_full_path( max_fines.org_unit ) fp ON (circ.circ_lib = fp.id) WHERE usr = match_user AND xact_finish IS NULL LOOP - SELECT INTO tmp_fines SUM( amount ) FROM money.billing WHERE xact = tmp_circ.id AND NOT voided; - current_fines = current_fines + COALESCE(tmp_fines, 0.0); - SELECT INTO tmp_fines SUM( amount ) FROM money.payment WHERE xact = tmp_circ.id AND NOT voided; - current_fines = current_fines - COALESCE(tmp_fines, 0.0); - END LOOP; + SELECT INTO tmp_fines SUM( amount ) FROM money.billing WHERE xact = tmp_circ.id AND NOT voided; + current_fines = current_fines + COALESCE(tmp_fines, 0.0); + SELECT INTO tmp_fines SUM( amount ) FROM money.payment WHERE xact = tmp_circ.id AND NOT voided; + current_fines = current_fines - COALESCE(tmp_fines, 0.0); + END LOOP; - IF current_fines >= max_fines.threshold THEN + IF current_fines >= max_fines.threshold THEN new_sp_row.usr := match_user; new_sp_row.org_unit := max_fines.org_unit; new_sp_row.standing_penalty := 1; RETURN NEXT new_sp_row; - END IF; - END IF; + END IF; + END IF; -- Start over for max overdue - SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org; + SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org; - -- Fail if the user has too many overdue items + -- Fail if the user has too many overdue items LOOP tmp_grp := user_object.profile; LOOP @@ -472,11 +472,11 @@ BEGIN EXIT; END IF; - SELECT INTO tmp_org * FROM actor.org_unit WHERE id = tmp_org.parent_ou; + SELECT INTO tmp_org * FROM actor.org_unit WHERE id = tmp_org.parent_ou; END LOOP; - IF max_overdue.threshold IS NOT NULL THEN + IF max_overdue.threshold IS NOT NULL THEN FOR existing_sp_row IN SELECT * @@ -488,26 +488,26 @@ BEGIN RETURN NEXT existing_sp_row; END LOOP; - SELECT INTO items_overdue COUNT(*) - FROM action.circulation circ + SELECT INTO items_overdue COUNT(*) + FROM action.circulation circ JOIN actor.org_unit_full_path( max_overdue.org_unit ) fp ON (circ.circ_lib = fp.id) - WHERE circ.usr = match_user - AND circ.checkin_time IS NULL - AND circ.due_date < NOW() - AND (circ.stop_fines NOT IN ('LOST','CLAIMSRETURNED','LONGOVERDUE') OR circ.stop_fines IS NULL); + WHERE circ.usr = match_user + AND circ.checkin_time IS NULL + AND circ.due_date < NOW() + AND (circ.stop_fines NOT IN ('LOST','CLAIMSRETURNED','LONGOVERDUE') OR circ.stop_fines IS NULL); - IF items_overdue >= max_overdue.threshold::INT THEN + IF items_overdue >= max_overdue.threshold::INT THEN new_sp_row.usr := match_user; new_sp_row.org_unit := max_overdue.org_unit; new_sp_row.standing_penalty := 2; RETURN NEXT new_sp_row; - END IF; - END IF; + END IF; + END IF; -- Start over for max out - SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org; + SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org; - -- Fail if the user has too many overdue items + -- Fail if the user has too many overdue items LOOP tmp_grp := user_object.profile; LOOP @@ -528,13 +528,13 @@ BEGIN EXIT; END IF; - SELECT INTO tmp_org * FROM actor.org_unit WHERE id = tmp_org.parent_ou; + SELECT INTO tmp_org * FROM actor.org_unit WHERE id = tmp_org.parent_ou; END LOOP; - -- Fail if the user has too many items checked out - IF max_items_out.threshold IS NOT NULL THEN + -- Fail if the user has too many items checked out + IF max_items_out.threshold IS NOT NULL THEN FOR existing_sp_row IN SELECT * @@ -546,20 +546,20 @@ BEGIN RETURN NEXT existing_sp_row; END LOOP; - SELECT INTO items_out COUNT(*) + SELECT INTO items_out COUNT(*) FROM action.circulation circ JOIN actor.org_unit_full_path( max_items_out.org_unit ) fp ON (circ.circ_lib = fp.id) WHERE circ.usr = match_user AND circ.checkin_time IS NULL AND (circ.stop_fines NOT IN ('LOST','CLAIMSRETURNED','LONGOVERDUE') OR circ.stop_fines IS NULL); - IF items_out >= max_items_out.threshold::INT THEN + IF items_out >= max_items_out.threshold::INT THEN new_sp_row.usr := match_user; new_sp_row.org_unit := max_items_out.org_unit; new_sp_row.standing_penalty := 3; RETURN NEXT new_sp_row; - END IF; - END IF; + END IF; + END IF; RETURN; END; diff --git a/Open-ILS/src/sql/Pg/110.hold_matrix.sql b/Open-ILS/src/sql/Pg/110.hold_matrix.sql index 0531116c86..0aebe89796 100644 --- a/Open-ILS/src/sql/Pg/110.hold_matrix.sql +++ b/Open-ILS/src/sql/Pg/110.hold_matrix.sql @@ -29,294 +29,296 @@ BEGIN; CREATE TABLE config.hold_matrix_matchpoint ( - id SERIAL PRIMARY KEY, - active BOOL NOT NULL DEFAULT TRUE, - user_home_ou INT REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED, -- Set to the top OU for the matchpoint applicability range; we can use org_unit_prox to choose the "best" - request_ou INT REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED, -- Set to the top OU for the matchpoint applicability range; we can use org_unit_prox to choose the "best" - pickup_ou INT REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED, -- Set to the top OU for the matchpoint applicability range; we can use org_unit_prox to choose the "best" - item_owning_ou INT REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED, -- Set to the top OU for the matchpoint applicability range; we can use org_unit_prox to choose the "best" - item_circ_ou INT REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED, -- Set to the top OU for the matchpoint applicability range; we can use org_unit_prox to choose the "best" - usr_grp INT REFERENCES permission.grp_tree (id) DEFERRABLE INITIALLY DEFERRED, -- Set to the top applicable group from the group tree; will need descendents and prox functions for filtering - requestor_grp INT NOT NULL REFERENCES permission.grp_tree (id) DEFERRABLE INITIALLY DEFERRED, -- Set to the top applicable group from the group tree; will need descendents and prox functions for filtering - circ_modifier TEXT REFERENCES config.circ_modifier (code) DEFERRABLE INITIALLY DEFERRED, - marc_type TEXT REFERENCES config.item_type_map (code) DEFERRABLE INITIALLY DEFERRED, - marc_form TEXT REFERENCES config.item_form_map (code) DEFERRABLE INITIALLY DEFERRED, - marc_vr_format TEXT REFERENCES config.videorecording_format_map (code) DEFERRABLE INITIALLY DEFERRED, - ref_flag BOOL, - holdable BOOL NOT NULL DEFAULT TRUE, -- Hard "can't hold" flag requiring an override - distance_is_from_owner BOOL NOT NULL DEFAULT FALSE, -- How to calculate transit_range. True means owning lib, false means copy circ lib - transit_range INT REFERENCES actor.org_unit_type (id) DEFERRABLE INITIALLY DEFERRED, -- Can circ inside range of cn.owner/cp.circ_lib at depth of the org_unit_type specified here - max_holds INT, -- Total hold requests must be less than this, NULL means skip (always pass) - include_frozen_holds BOOL NOT NULL DEFAULT TRUE, -- Include frozen hold requests in the count for max_holds test - stop_blocked_user BOOL NOT NULL DEFAULT FALSE, -- Stop users who cannot check out items from placing holds - age_hold_protect_rule INT REFERENCES config.rule_age_hold_protect (id) DEFERRABLE INITIALLY DEFERRED, -- still not sure we want to move this off the copy - CONSTRAINT hous_once_per_grp_loc_mod_marc UNIQUE (user_home_ou, request_ou, pickup_ou, item_owning_ou, item_circ_ou, requestor_grp, usr_grp, circ_modifier, marc_type, marc_form, marc_vr_format) + id SERIAL PRIMARY KEY, + active BOOL NOT NULL DEFAULT TRUE, + user_home_ou INT REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED, -- Set to the top OU for the matchpoint applicability range; we can use org_unit_prox to choose the "best" + request_ou INT REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED, -- Set to the top OU for the matchpoint applicability range; we can use org_unit_prox to choose the "best" + pickup_ou INT REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED, -- Set to the top OU for the matchpoint applicability range; we can use org_unit_prox to choose the "best" + item_owning_ou INT REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED, -- Set to the top OU for the matchpoint applicability range; we can use org_unit_prox to choose the "best" + item_circ_ou INT REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED, -- Set to the top OU for the matchpoint applicability range; we can use org_unit_prox to choose the "best" + usr_grp INT REFERENCES permission.grp_tree (id) DEFERRABLE INITIALLY DEFERRED, -- Set to the top applicable group from the group tree; will need descendents and prox functions for filtering + requestor_grp INT NOT NULL REFERENCES permission.grp_tree (id) DEFERRABLE INITIALLY DEFERRED, -- Set to the top applicable group from the group tree; will need descendents and prox functions for filtering + circ_modifier TEXT REFERENCES config.circ_modifier (code) DEFERRABLE INITIALLY DEFERRED, + marc_type TEXT REFERENCES config.item_type_map (code) DEFERRABLE INITIALLY DEFERRED, + marc_form TEXT REFERENCES config.item_form_map (code) DEFERRABLE INITIALLY DEFERRED, + marc_vr_format TEXT REFERENCES config.videorecording_format_map (code) DEFERRABLE INITIALLY DEFERRED, + ref_flag BOOL, + holdable BOOL NOT NULL DEFAULT TRUE, -- Hard "can't hold" flag requiring an override + distance_is_from_owne BOOL NOT NULL DEFAULT FALSE, -- How to calculate transit_range. True means owning lib, false means copy circ lib + transit_range INT REFERENCES actor.org_unit_type (id) DEFERRABLE INITIALLY DEFERRED, -- Can circ inside range of cn.owner/cp.circ_lib at depth of the org_unit_type specified here + max_holds INT, -- Total hold requests must be less than this, NULL means skip (always pass) + include_frozen_holds BOOL NOT NULL DEFAULT TRUE, -- Include frozen hold requests in the count for max_holds test + stop_blocked_user BOOL NOT NULL DEFAULT FALSE, -- Stop users who cannot check out items from placing holds + age_hold_protect_rule INT REFERENCES config.rule_age_hold_protect (id) DEFERRABLE INITIALLY DEFERRED, -- still not sure we want to move this off the copy + CONSTRAINT hous_once_per_grp_loc_mod_marc UNIQUE (user_home_ou, request_ou, pickup_ou, item_owning_ou, item_circ_ou, requestor_grp, usr_grp, circ_modifier, marc_type, marc_form, marc_vr_format) ); CREATE OR REPLACE FUNCTION action.find_hold_matrix_matchpoint( pickup_ou INT, request_ou INT, match_item BIGINT, match_user INT, match_requestor INT ) RETURNS INT AS $func$ DECLARE - current_requestor_group permission.grp_tree%ROWTYPE; - root_ou actor.org_unit%ROWTYPE; - requestor_object actor.usr%ROWTYPE; - user_object actor.usr%ROWTYPE; - item_object asset.copy%ROWTYPE; - item_cn_object asset.call_number%ROWTYPE; - rec_descriptor metabib.rec_descriptor%ROWTYPE; - current_mp_weight FLOAT; - matchpoint_weight FLOAT; - tmp_weight FLOAT; - current_mp config.hold_matrix_matchpoint%ROWTYPE; - matchpoint config.hold_matrix_matchpoint%ROWTYPE; + current_requestor_group permission.grp_tree%ROWTYPE; + root_ou actor.org_unit%ROWTYPE; + requestor_object actor.usr%ROWTYPE; + user_object actor.usr%ROWTYPE; + item_object asset.copy%ROWTYPE; + item_cn_object asset.call_number%ROWTYPE; + rec_descriptor metabib.rec_descriptor%ROWTYPE; + current_mp_weight FLOAT; + matchpoint_weight FLOAT; + tmp_weight FLOAT; + current_mp config.hold_matrix_matchpoint%ROWTYPE; + matchpoint config.hold_matrix_matchpoint%ROWTYPE; BEGIN - SELECT INTO root_ou * FROM actor.org_unit WHERE parent_ou IS NULL; - SELECT INTO user_object * FROM actor.usr WHERE id = match_user; - SELECT INTO requestor_object * FROM actor.usr WHERE id = match_requestor; - SELECT INTO item_object * FROM asset.copy WHERE id = match_item; - SELECT INTO item_cn_object * FROM asset.call_number WHERE id = item_object.call_number; - SELECT INTO rec_descriptor r.* FROM metabib.rec_descriptor r WHERE r.record = item_cn_object.record; - SELECT INTO current_requestor_group * FROM permission.grp_tree WHERE id = requestor_object.profile; - - LOOP - -- for each potential matchpoint for this ou and group ... - FOR current_mp IN - SELECT m.* - FROM config.hold_matrix_matchpoint m - WHERE m.requestor_grp = current_requestor_group.id AND m.active - ORDER BY CASE WHEN m.circ_modifier IS NOT NULL THEN 16 ELSE 0 END + - CASE WHEN m.marc_type IS NOT NULL THEN 8 ELSE 0 END + - CASE WHEN m.marc_form IS NOT NULL THEN 4 ELSE 0 END + - CASE WHEN m.marc_vr_format IS NOT NULL THEN 2 ELSE 0 END + - CASE WHEN m.ref_flag IS NOT NULL THEN 1 ELSE 0 END DESC LOOP - - current_mp_weight := 5.0; - - IF current_mp.circ_modifier IS NOT NULL THEN - CONTINUE WHEN current_mp.circ_modifier <> item_object.circ_modifier; - END IF; - - IF current_mp.marc_type IS NOT NULL THEN - IF item_object.circ_as_type IS NOT NULL THEN - CONTINUE WHEN current_mp.marc_type <> item_object.circ_as_type; - ELSE - CONTINUE WHEN current_mp.marc_type <> rec_descriptor.item_type; - END IF; - END IF; - - IF current_mp.marc_form IS NOT NULL THEN - CONTINUE WHEN current_mp.marc_form <> rec_descriptor.item_form; - END IF; - - IF current_mp.marc_vr_format IS NOT NULL THEN - CONTINUE WHEN current_mp.marc_vr_format <> rec_descriptor.vr_format; - END IF; - - IF current_mp.ref_flag IS NOT NULL THEN - CONTINUE WHEN current_mp.ref_flag <> item_object.ref; - END IF; - - - -- caclulate the rule match weight - IF current_mp.item_owning_ou IS NOT NULL AND current_mp.item_owning_ou <> root_ou.id THEN - SELECT INTO tmp_weight 1.0 / (actor.org_unit_proximity(current_mp.item_owning_ou, item_cn_object.owning_lib)::FLOAT + 1.0)::FLOAT; - current_mp_weight := current_mp_weight - tmp_weight; - END IF; - - IF current_mp.item_circ_ou IS NOT NULL AND current_mp.item_circ_ou <> root_ou.id THEN - SELECT INTO tmp_weight 1.0 / (actor.org_unit_proximity(current_mp.item_circ_ou, item_object.circ_lib)::FLOAT + 1.0)::FLOAT; - current_mp_weight := current_mp_weight - tmp_weight; - END IF; - - IF current_mp.pickup_ou IS NOT NULL AND current_mp.pickup_ou <> root_ou.id THEN - SELECT INTO tmp_weight 1.0 / (actor.org_unit_proximity(current_mp.pickup_ou, pickup_ou)::FLOAT + 1.0)::FLOAT; - current_mp_weight := current_mp_weight - tmp_weight; - END IF; - - IF current_mp.request_ou IS NOT NULL AND current_mp.request_ou <> root_ou.id THEN - SELECT INTO tmp_weight 1.0 / (actor.org_unit_proximity(current_mp.request_ou, request_ou)::FLOAT + 1.0)::FLOAT; - current_mp_weight := current_mp_weight - tmp_weight; - END IF; - - IF current_mp.user_home_ou IS NOT NULL AND current_mp.user_home_ou <> root_ou.id THEN - SELECT INTO tmp_weight 1.0 / (actor.org_unit_proximity(current_mp.user_home_ou, user_object.home_ou)::FLOAT + 1.0)::FLOAT; - current_mp_weight := current_mp_weight - tmp_weight; - END IF; - - -- set the matchpoint if we found the best one - IF matchpoint_weight IS NULL OR matchpoint_weight > current_mp_weight THEN - matchpoint = current_mp; - matchpoint_weight = current_mp_weight; - END IF; - - END LOOP; - - EXIT WHEN current_requestor_group.parent IS NULL OR matchpoint.id IS NOT NULL; - - SELECT INTO current_requestor_group * FROM permission.grp_tree WHERE id = current_requestor_group.parent; - END LOOP; - - RETURN matchpoint.id; + SELECT INTO root_ou * FROM actor.org_unit WHERE parent_ou IS NULL; + SELECT INTO user_object * FROM actor.usr WHERE id = match_user; + SELECT INTO requestor_object * FROM actor.usr WHERE id = match_requestor; + SELECT INTO item_object * FROM asset.copy WHERE id = match_item; + SELECT INTO item_cn_object * FROM asset.call_number WHERE id = item_object.call_number; + SELECT INTO rec_descriptor r.* FROM metabib.rec_descriptor r WHERE r.record = item_cn_object.record; + SELECT INTO current_requestor_group * FROM permission.grp_tree WHERE id = requestor_object.profile; + + LOOP + -- for each potential matchpoint for this ou and group ... + FOR current_mp IN + SELECT m.* + FROM config.hold_matrix_matchpoint m + WHERE m.requestor_grp = current_requestor_group.id AND m.active + ORDER BY CASE WHEN m.circ_modifier IS NOT NULL THEN 16 ELSE 0 END + + CASE WHEN m.marc_type IS NOT NULL THEN 8 ELSE 0 END + + CASE WHEN m.marc_form IS NOT NULL THEN 4 ELSE 0 END + + CASE WHEN m.marc_vr_format IS NOT NULL THEN 2 ELSE 0 END + + CASE WHEN m.ref_flag IS NOT NULL THEN 1 ELSE 0 END DESC LOOP + + current_mp_weight := 5.0; + + IF current_mp.circ_modifier IS NOT NULL THEN + CONTINUE WHEN current_mp.circ_modifier <> item_object.circ_modifier; + END IF; + + IF current_mp.marc_type IS NOT NULL THEN + IF item_object.circ_as_type IS NOT NULL THEN + CONTINUE WHEN current_mp.marc_type <> item_object.circ_as_type; + ELSE + CONTINUE WHEN current_mp.marc_type <> rec_descriptor.item_type; + END IF; + END IF; + + IF current_mp.marc_form IS NOT NULL THEN + CONTINUE WHEN current_mp.marc_form <> rec_descriptor.item_form; + END IF; + + IF current_mp.marc_vr_format IS NOT NULL THEN + CONTINUE WHEN current_mp.marc_vr_format <> rec_descriptor.vr_format; + END IF; + + IF current_mp.ref_flag IS NOT NULL THEN + CONTINUE WHEN current_mp.ref_flag <> item_object.ref; + END IF; + + + -- caclulate the rule match weight + IF current_mp.item_owning_ou IS NOT NULL AND current_mp.item_owning_ou <> root_ou.id THEN + SELECT INTO tmp_weight 1.0 / (actor.org_unit_proximity(current_mp.item_owning_ou, item_cn_object.owning_lib)::FLOAT + 1.0)::FLOAT; + current_mp_weight := current_mp_weight - tmp_weight; + END IF; + + IF current_mp.item_circ_ou IS NOT NULL AND current_mp.item_circ_ou <> root_ou.id THEN + SELECT INTO tmp_weight 1.0 / (actor.org_unit_proximity(current_mp.item_circ_ou, item_object.circ_lib)::FLOAT + 1.0)::FLOAT; + current_mp_weight := current_mp_weight - tmp_weight; + END IF; + + IF current_mp.pickup_ou IS NOT NULL AND current_mp.pickup_ou <> root_ou.id THEN + SELECT INTO tmp_weight 1.0 / (actor.org_unit_proximity(current_mp.pickup_ou, pickup_ou)::FLOAT + 1.0)::FLOAT; + current_mp_weight := current_mp_weight - tmp_weight; + END IF; + + IF current_mp.request_ou IS NOT NULL AND current_mp.request_ou <> root_ou.id THEN + SELECT INTO tmp_weight 1.0 / (actor.org_unit_proximity(current_mp.request_ou, request_ou)::FLOAT + 1.0)::FLOAT; + current_mp_weight := current_mp_weight - tmp_weight; + END IF; + + IF current_mp.user_home_ou IS NOT NULL AND current_mp.user_home_ou <> root_ou.id THEN + SELECT INTO tmp_weight 1.0 / (actor.org_unit_proximity(current_mp.user_home_ou, user_object.home_ou)::FLOAT + 1.0)::FLOAT; + current_mp_weight := current_mp_weight - tmp_weight; + END IF; + + -- set the matchpoint if we found the best one + IF matchpoint_weight IS NULL OR matchpoint_weight > current_mp_weight THEN + matchpoint = current_mp; + matchpoint_weight = current_mp_weight; + END IF; + + END LOOP; + + EXIT WHEN current_requestor_group.parent IS NULL OR matchpoint.id IS NOT NULL; + + SELECT INTO current_requestor_group * FROM permission.grp_tree WHERE id = current_requestor_group.parent; + END LOOP; + + RETURN matchpoint.id; END; $func$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION action.hold_request_permit_test( pickup_ou INT, request_ou INT, match_item BIGINT, match_user INT, match_requestor INT ) RETURNS SETOF action.matrix_test_result AS $func$ DECLARE - matchpoint_id INT; - user_object actor.usr%ROWTYPE; - age_protect_object config.rule_age_hold_protect%ROWTYPE; - transit_range_ou_type actor.org_unit_type%ROWTYPE; - transit_source actor.org_unit%ROWTYPE; - item_object asset.copy%ROWTYPE; - result action.matrix_test_result; - hold_test config.hold_matrix_matchpoint%ROWTYPE; - hold_count INT; - hold_transit_prox INT; - frozen_hold_count INT; - patron_penalties INT; - done BOOL := FALSE; + matchpoint_id INT; + user_object actor.usr%ROWTYPE; + age_protect_object config.rule_age_hold_protect%ROWTYPE; + standing_penalty config.standing_penalty%ROWTYPE; + transit_range_ou_type actor.org_unit_type%ROWTYPE; + transit_source actor.org_unit%ROWTYPE; + item_object asset.copy%ROWTYPE; + result action.matrix_test_result; + hold_test config.hold_matrix_matchpoint%ROWTYPE; + hold_count INT; + hold_transit_prox INT; + frozen_hold_count INT; + context_org_list INT; + done BOOL := FALSE; BEGIN - SELECT INTO user_object * FROM actor.usr WHERE id = match_user; - - -- Fail if we couldn't find a user - IF user_object.id IS NULL THEN - result.fail_part := 'no_user'; - result.success := FALSE; - done := TRUE; - RETURN NEXT result; - RETURN; - END IF; - - -- Fail if user is barred - IF user_object.barred IS TRUE THEN - result.fail_part := 'actor.usr.barred'; - result.success := FALSE; - done := TRUE; - RETURN NEXT result; - RETURN; - END IF; - - SELECT INTO item_object * FROM asset.copy WHERE id = match_item; - - -- Fail if we couldn't find a copy - IF item_object.id IS NULL THEN - result.fail_part := 'no_item'; - result.success := FALSE; - done := TRUE; - RETURN NEXT result; - RETURN; - END IF; - - SELECT INTO matchpoint_id action.find_hold_matrix_matchpoint(pickup_ou, request_ou, match_item, match_user, match_requestor); - - -- Fail if we couldn't find any matchpoint (requires a default) - IF matchpoint_id IS NULL THEN - result.fail_part := 'no_matchpoint'; - result.success := FALSE; - done := TRUE; - RETURN NEXT result; - RETURN; - END IF; - - SELECT INTO hold_test * FROM config.hold_matrix_matchpoint WHERE id = matchpoint_id; - - result.matchpoint := hold_test.id; - result.success := TRUE; - - IF hold_test.holdable IS FALSE THEN - result.fail_part := 'config.hold_matrix_test.holdable'; - result.success := FALSE; - done := TRUE; - RETURN NEXT result; - END IF; - - IF hold_test.transit_range IS NOT NULL THEN - SELECT INTO transit_range_ou_type * FROM actor.org_unit_type WHERE id = hold_test.transit_range; - IF hold_test.distance_is_from_owner THEN - SELECT INTO transit_source ou.* FROM actor.org_unit ou JOIN asset.call_number cn ON (cn.owning_lib = ou.id) WHERE cn.id = item_object.call_number; - ELSE - SELECT INTO transit_source * FROM actor.org_unit WHERE id = item_object.circ_lib; - END IF; - - PERFORM * FROM actor.org_unit_descendants( transit_source.id, transit_range_ou_type.depth ) WHERE id = pickup_ou; - - IF NOT FOUND THEN - result.fail_part := 'transit_range'; - result.success := FALSE; - done := TRUE; - RETURN NEXT result; - END IF; - END IF; - - SELECT INTO patron_penalties COUNT(*) - FROM actor.usr_standing_penalty usp - JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty) - WHERE usr = match_user - AND csp.block_list LIKE '%HOLD%'; - - IF patron_penalties > 0 THEN - result.fail_part := 'config.hold_matrix_test.stop_blocked_user.hold'; - result.success := FALSE; - done := TRUE; - RETURN NEXT result; - END IF; - - patron_penalties := 0; - - IF hold_test.stop_blocked_user IS TRUE THEN - SELECT INTO patron_penalties COUNT(*) - FROM actor.usr_standing_penalty usp + SELECT INTO user_object * FROM actor.usr WHERE id = match_user; + SELECT INTO context_org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( pickup_ou ); + + -- Fail if we couldn't find a user + IF user_object.id IS NULL THEN + result.fail_part := 'no_user'; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + RETURN; + END IF; + + -- Fail if user is barred + IF user_object.barred IS TRUE THEN + result.fail_part := 'actor.usr.barred'; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + RETURN; + END IF; + + SELECT INTO item_object * FROM asset.copy WHERE id = match_item; + + -- Fail if we couldn't find a copy + IF item_object.id IS NULL THEN + result.fail_part := 'no_item'; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + RETURN; + END IF; + + SELECT INTO matchpoint_id action.find_hold_matrix_matchpoint(pickup_ou, request_ou, match_item, match_user, match_requestor); + + -- Fail if we couldn't find any matchpoint (requires a default) + IF matchpoint_id IS NULL THEN + result.fail_part := 'no_matchpoint'; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + RETURN; + END IF; + + SELECT INTO hold_test * FROM config.hold_matrix_matchpoint WHERE id = matchpoint_id; + + result.matchpoint := hold_test.id; + result.success := TRUE; + + IF hold_test.holdable IS FALSE THEN + result.fail_part := 'config.hold_matrix_test.holdable'; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + END IF; + + IF hold_test.transit_range IS NOT NULL THEN + SELECT INTO transit_range_ou_type * FROM actor.org_unit_type WHERE id = hold_test.transit_range; + IF hold_test.distance_is_from_owner THEN + SELECT INTO transit_source ou.* FROM actor.org_unit ou JOIN asset.call_number cn ON (cn.owning_lib = ou.id) WHERE cn.id = item_object.call_number; + ELSE + SELECT INTO transit_source * FROM actor.org_unit WHERE id = item_object.circ_lib; + END IF; + + PERFORM * FROM actor.org_unit_descendants( transit_source.id, transit_range_ou_type.depth ) WHERE id = pickup_ou; + + IF NOT FOUND THEN + result.fail_part := 'transit_range'; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + END IF; + END IF; + + FOR standing_penalty IN + SELECT DISTINCT csp.* + FROM actor.usr_standing_penalty usp JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty) - WHERE usr = match_user - AND csp.block_list LIKE '%CIRC%'; - - IF patron_penalties > 0 THEN - result.fail_part := 'config.hold_matrix_test.stop_blocked_user.circ'; - result.success := FALSE; - done := TRUE; - RETURN NEXT result; - END IF; - END IF; - - IF hold_test.max_holds IS NOT NULL THEN - SELECT INTO hold_count COUNT(*) - FROM action.hold_request - WHERE usr = match_user - AND fulfillment_time IS NULL - AND cancel_time IS NULL - AND CASE WHEN hold_test.include_frozen_holds THEN TRUE ELSE frozen IS FALSE END; - - IF hold_count >= hold_test.max_holds THEN - result.fail_part := 'config.hold_matrix_test.max_holds'; - result.success := FALSE; - done := TRUE; - RETURN NEXT result; - END IF; - END IF; - - IF item_object.age_protect IS NOT NULL THEN - SELECT INTO age_protect_object * FROM config.rule_age_hold_protect WHERE id = item_object.age_protect; - - IF item_object.create_date + age_protect_object.age > NOW() THEN - IF hold_test.distance_is_from_owner THEN - SELECT INTO hold_transit_prox prox FROM actor.org_unit_prox WHERE from_org = item_cn_object.owning_lib AND to_org = pickup_ou; - ELSE - SELECT INTO hold_transit_prox prox FROM actor.org_unit_prox WHERE from_org = item_object.circ_lib AND to_org = pickup_ou; - END IF; - - IF hold_transit_prox > age_protect_object.prox THEN - result.fail_part := 'config.rule_age_hold_protect.prox'; - result.success := FALSE; - done := TRUE; - RETURN NEXT result; - END IF; - END IF; - END IF; - - IF NOT done THEN - RETURN NEXT result; - END IF; - - RETURN; + WHERE usr = match_user + AND usp.org_unit IN ( SELECT * FROM explode_array(context_org_list) ) + AND csp.block_list LIKE '%HOLD%'; + + result.fail_part := standing_penalty.name; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + END LOOP; + + IF hold_test.stop_blocked_user IS TRUE THEN + FOR standing_penalty IN + SELECT DISTINCT csp.* + FROM actor.usr_standing_penalty usp + JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty) + WHERE usr = match_user + AND usp.org_unit IN ( SELECT * FROM explode_array(context_org_list) ) + AND csp.block_list LIKE '%CIRC%'; + + result.fail_part := standing_penalty.name; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + END LOOP; + END IF; + + IF hold_test.max_holds IS NOT NULL THEN + SELECT INTO hold_count COUNT(*) + FROM action.hold_request + WHERE usr = match_user + AND fulfillment_time IS NULL + AND cancel_time IS NULL + AND CASE WHEN hold_test.include_frozen_holds THEN TRUE ELSE frozen IS FALSE END; + + IF hold_count >= hold_test.max_holds THEN + result.fail_part := 'config.hold_matrix_test.max_holds'; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + END IF; + END IF; + + IF item_object.age_protect IS NOT NULL THEN + SELECT INTO age_protect_object * FROM config.rule_age_hold_protect WHERE id = item_object.age_protect; + + IF item_object.create_date + age_protect_object.age > NOW() THEN + IF hold_test.distance_is_from_owner THEN + SELECT INTO hold_transit_prox prox FROM actor.org_unit_prox WHERE from_org = item_cn_object.owning_lib AND to_org = pickup_ou; + ELSE + SELECT INTO hold_transit_prox prox FROM actor.org_unit_prox WHERE from_org = item_object.circ_lib AND to_org = pickup_ou; + END IF; + + IF hold_transit_prox > age_protect_object.prox THEN + result.fail_part := 'config.rule_age_hold_protect.prox'; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + END IF; + END IF; + END IF; + + IF NOT done THEN + RETURN NEXT result; + END IF; + + RETURN; END; $func$ LANGUAGE plpgsql; -- 2.11.0