From 52bd87088f5e909435e7f3ba4a48134dff384ecc Mon Sep 17 00:00:00 2001 From: Fredrick Parks Date: Wed, 10 Jul 2013 11:12:17 -0700 Subject: [PATCH] LP 1103706 Hold ratios in circ policies cause errors when trying to renew items Changed the function action.copy_related_hold_stats to accept a bigint as the perameter instead of an integer. Copy_related_hold_stats is only called by the function action.item_user_circ_test which trys to pass a bigint. Signed-off-by: Fredrick Parks Signed-off-by: Mike Rylander --- Open-ILS/src/sql/Pg/100.circ_matrix.sql | 2 +- .../XXXX.function.copy_related_hold_stats.sql | 60 +++++++++++++++++++ 2 files changed, 61 insertions(+), 1 deletion(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.function.copy_related_hold_stats.sql diff --git a/Open-ILS/src/sql/Pg/100.circ_matrix.sql b/Open-ILS/src/sql/Pg/100.circ_matrix.sql index 3dbb328ecc..1e4761a66a 100644 --- a/Open-ILS/src/sql/Pg/100.circ_matrix.sql +++ b/Open-ILS/src/sql/Pg/100.circ_matrix.sql @@ -365,7 +365,7 @@ CREATE TYPE action.hold_stats AS ( available_copy_ratio FLOAT ); -CREATE OR REPLACE FUNCTION action.copy_related_hold_stats (copy_id INT) RETURNS action.hold_stats AS $func$ +CREATE OR REPLACE FUNCTION action.copy_related_hold_stats (copy_id BIGINT) RETURNS action.hold_stats AS $func$ DECLARE output action.hold_stats%ROWTYPE; hold_count INT := 0; diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.function.copy_related_hold_stats.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.function.copy_related_hold_stats.sql new file mode 100644 index 0000000000..a59b517f56 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.function.copy_related_hold_stats.sql @@ -0,0 +1,60 @@ +BEGIN; + +-- check whether patch can be applied +SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version); + +DROP FUNCTION action.copy_related_hold_stats(integer); + +CREATE OR REPLACE FUNCTION action.copy_related_hold_stats(copy_id bigint) + RETURNS action.hold_stats AS +$BODY$ +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; +$BODY$ + LANGUAGE plpgsql VOLATILE + COST 100; +ALTER FUNCTION action.copy_related_hold_stats(bigint) + OWNER TO evergreen; + +COMMIT; + -- 2.43.2