From c120c5f810c66bea4c8b491930a35513f0ae5c74 Mon Sep 17 00:00:00 2001 From: miker Date: Tue, 3 Nov 2009 17:49:57 +0000 Subject: [PATCH] adding total and available copy-hold ratio support to in-db circ git-svn-id: svn://svn.open-ils.org/ILS/trunk@14743 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/src/sql/Pg/002.schema.config.sql | 2 +- Open-ILS/src/sql/Pg/100.circ_matrix.sql | 77 ++++++++++ .../0068.schema.copy-hold-ratio-support.sql | 171 +++++++++++++++++++++ 3 files changed, 249 insertions(+), 1 deletion(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/0068.schema.copy-hold-ratio-support.sql diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql index d3fcbe8ea..26598f870 100644 --- a/Open-ILS/src/sql/Pg/002.schema.config.sql +++ b/Open-ILS/src/sql/Pg/002.schema.config.sql @@ -51,7 +51,7 @@ CREATE TABLE config.upgrade_log ( install_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW() ); -INSERT INTO config.upgrade_log (version) VALUES ('0067'); -- miker +INSERT INTO config.upgrade_log (version) VALUES ('0068'); -- miker CREATE TABLE config.bib_source ( id SERIAL PRIMARY KEY, diff --git a/Open-ILS/src/sql/Pg/100.circ_matrix.sql b/Open-ILS/src/sql/Pg/100.circ_matrix.sql index 9eb1d5bf2..08abe3932 100644 --- a/Open-ILS/src/sql/Pg/100.circ_matrix.sql +++ b/Open-ILS/src/sql/Pg/100.circ_matrix.sql @@ -114,6 +114,8 @@ CREATE TABLE config.circ_matrix_matchpoint ( 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 + total_copy_hold_ratio FLOAT, + available_copy_hold_ratio FLOAT, CONSTRAINT ep_once_per_grp_loc_mod_marc UNIQUE (grp, org_unit, circ_modifier, marc_type, marc_form, marc_vr_format, ref_flag, juvenile_flag, usr_age_lower_bound, usr_age_upper_bound, is_renewal) ); @@ -217,6 +219,58 @@ BEGIN END; $func$ LANGUAGE plpgsql; +CREATE TYPE action.hold_stats AS ( + hold_count INT, + copy_count INT, + available_count INT, + total_copy_ratio FLOAT, + available_copy_ratio FLOAT +); + +CREATE OR REPLACE FUNCTION action.copy_related_hold_stats (copy_id INT) RETURNS action.hold_stats AS $func$ +DECLARE + output action.hold_stats%ROWTYPE; + hold_count INT := 0; + copy_count INT := 0; + available_count INT := 0; + hold_map_data RECORD; +BEGIN + + output.hold_count := 0; + output.copy_count := 0; + output.available_count := 0; + + SELECT COUNT( DISTINCT m.hold ) INTO hold_count + FROM action.hold_copy_map m + JOIN action.hold_request h ON (m.hold = h.id) + WHERE m.target_copy = copy_id + AND NOT h.frozen; + + output.hold_count := hold_count; + + IF output.hold_count > 0 THEN + FOR hold_map_data IN + SELECT DISTINCT m.target_copy, + acp.status + FROM action.hold_copy_map m + JOIN asset.copy acp ON (m.target_copy = acp.id) + JOIN action.hold_request h ON (m.hold = h.id) + WHERE m.hold IN ( SELECT DISTINCT hold FROM action.hold_copy_map WHERE target_copy = copy_id ) AND NOT h.frozen + LOOP + output.copy_count := output.copy_count + 1; + IF hold_map_data.status IN (0,7,12) THEN + output.available_count := output.available_count + 1; + END IF; + END LOOP; + output.total_copy_ratio = output.copy_count::FLOAT / output.hold_count::FLOAT; + output.available_copy_ratio = output.available_count::FLOAT / output.hold_count::FLOAT; + + END IF; + + RETURN output; + +END; +$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$ @@ -230,6 +284,7 @@ DECLARE circ_test config.circ_matrix_matchpoint%ROWTYPE; out_by_circ_mod config.circ_matrix_circ_mod_test%ROWTYPE; circ_mod_map config.circ_matrix_circ_mod_test_map%ROWTYPE; + hold_ratio action.hold_stats%ROWTYPE; penalty_type TEXT; tmp_grp INT; items_out INT; @@ -309,6 +364,28 @@ BEGIN RETURN NEXT result; END IF; + -- Fail if the total copy-hold ratio is too low + IF circ_test.total_copy_hold_ratio IS NOT NULL THEN + SELECT INTO hold_ratio * FROM action.copy_related_hold_stats(match_item); + IF hold_ratio.total_copy_ratio IS NOT NULL AND hold_ratio.total_copy_ratio < circ_test.total_copy_hold_ratio THEN + result.fail_part := 'config.circ_matrix_test.total_copy_hold_ratio'; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + END IF; + END IF; + + -- Fail if the available copy-hold ratio is too low + IF circ_test.available_copy_hold_ratio IS NOT NULL THEN + SELECT INTO hold_ratio * FROM action.copy_related_hold_stats(match_item); + IF hold_ratio.available_copy_ratio IS NOT NULL AND hold_ratio.available_copy_ratio < circ_test.available_copy_hold_ratio THEN + result.fail_part := 'config.circ_matrix_test.available_copy_hold_ratio'; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + END IF; + END IF; + IF renewal THEN penalty_type = '%RENEW%'; ELSE diff --git a/Open-ILS/src/sql/Pg/upgrade/0068.schema.copy-hold-ratio-support.sql b/Open-ILS/src/sql/Pg/upgrade/0068.schema.copy-hold-ratio-support.sql new file mode 100644 index 000000000..324b5bc1c --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/0068.schema.copy-hold-ratio-support.sql @@ -0,0 +1,171 @@ + +BEGIN; + +INSERT INTO config.upgrade_log (version) VALUES ('0068'); + +ALTER TABLE config.circ_matrix_matchpoint ADD COLUMN total_copy_hold_ratio FLOAT; +ALTER TABLE config.circ_matrix_matchpoint ADD COLUMN available_copy_hold_ratio FLOAT; + +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; + circ_mod_map config.circ_matrix_circ_mod_test_map%ROWTYPE; + hold_ratio action.hold_stats%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; + + -- Fail if the total copy-hold ratio is too low + IF circ_test.total_copy_hold_ratio IS NOT NULL THEN + SELECT INTO hold_ratio * FROM action.copy_related_hold_stats(match_item); + IF hold_ratio.total_copy_ratio IS NOT NULL AND hold_ratio.total_copy_ratio < circ_test.total_copy_hold_ratio THEN + result.fail_part := 'config.circ_matrix_test.total_copy_hold_ratio'; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + END IF; + END IF; + + -- Fail if the available copy-hold ratio is too low + IF circ_test.available_copy_hold_ratio IS NOT NULL THEN + SELECT INTO hold_ratio * FROM action.copy_related_hold_stats(match_item); + IF hold_ratio.available_copy_ratio IS NOT NULL AND hold_ratio.available_copy_ratio < circ_test.available_copy_hold_ratio THEN + result.fail_part := 'config.circ_matrix_test.available_copy_hold_ratio'; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + END IF; + END IF; + + IF renewal THEN + penalty_type = '%RENEW%'; + ELSE + penalty_type = '%CIRC%'; + 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 usp.org_unit IN ( SELECT * FROM explode_array(context_org_list) ) + AND (usp.stop_date IS NULL or usp.stop_date > NOW()) + AND csp.block_list LIKE penalty_type LOOP + + result.fail_part := standing_penalty.name; + result.success := FALSE; + done := TRUE; + 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.circ_lib IN ( SELECT * FROM explode_array(context_org_list) ) + AND circ.checkin_time IS NULL + AND (circ.stop_fines IN ('MAXFINES','LONGOVERDUE') OR circ.stop_fines IS NULL) + 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); + 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; + +COMMIT; + -- 2.11.0