From a8e91f352ad260d16b1d2b39740a019890b5d2e7 Mon Sep 17 00:00:00 2001 From: miker Date: Tue, 3 Nov 2009 17:46:17 +0000 Subject: [PATCH] moving the hold ratio stats function, and fixing a div-by-0 thinko git-svn-id: svn://svn.open-ils.org/ILS/trunk@14742 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/src/sql/Pg/999.functions.global.sql | 51 ---------------------- .../Pg/upgrade/0067.schema.hold-copy-ratios.sql | 34 ++++++++------- 2 files changed, 18 insertions(+), 67 deletions(-) diff --git a/Open-ILS/src/sql/Pg/999.functions.global.sql b/Open-ILS/src/sql/Pg/999.functions.global.sql index fe3167df9e..fd1937a9dc 100644 --- a/Open-ILS/src/sql/Pg/999.functions.global.sql +++ b/Open-ILS/src/sql/Pg/999.functions.global.sql @@ -1108,54 +1108,3 @@ 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; - - 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; - - RETURN output; - -END; -$func$ LANGUAGE PLPGSQL; - diff --git a/Open-ILS/src/sql/Pg/upgrade/0067.schema.hold-copy-ratios.sql b/Open-ILS/src/sql/Pg/upgrade/0067.schema.hold-copy-ratios.sql index 26bf4a1cae..33cf1bc3ad 100644 --- a/Open-ILS/src/sql/Pg/upgrade/0067.schema.hold-copy-ratios.sql +++ b/Open-ILS/src/sql/Pg/upgrade/0067.schema.hold-copy-ratios.sql @@ -31,22 +31,24 @@ BEGIN output.hold_count := hold_count; - 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; + 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; -- 2.11.0