From 7fba2c76b3bdbfb5aa78cae9fcbb2642338dc5ca Mon Sep 17 00:00:00 2001 From: miker Date: Tue, 3 Nov 2009 16:45:20 +0000 Subject: [PATCH] adding stored procedure for calculating available and total copy/hold ratios for a given copy git-svn-id: svn://svn.open-ils.org/ILS/trunk@14740 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/src/sql/Pg/002.schema.config.sql | 2 +- Open-ILS/src/sql/Pg/999.functions.global.sql | 51 +++++++++++++++++++ .../Pg/upgrade/0067.schema.hold-copy-ratios.sql | 57 ++++++++++++++++++++++ 3 files changed, 109 insertions(+), 1 deletion(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/0067.schema.hold-copy-ratios.sql diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql index 70d4119162..d3fcbe8ea9 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 ('0065'); -- Scott McKellar +INSERT INTO config.upgrade_log (version) VALUES ('0067'); -- miker CREATE TABLE config.bib_source ( id SERIAL PRIMARY KEY, diff --git a/Open-ILS/src/sql/Pg/999.functions.global.sql b/Open-ILS/src/sql/Pg/999.functions.global.sql index fd1937a9dc..fe3167df9e 100644 --- a/Open-ILS/src/sql/Pg/999.functions.global.sql +++ b/Open-ILS/src/sql/Pg/999.functions.global.sql @@ -1108,3 +1108,54 @@ 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 new file mode 100644 index 0000000000..26bf4a1cae --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/0067.schema.hold-copy-ratios.sql @@ -0,0 +1,57 @@ +BEGIN; + +INSERT INTO config.upgrade_log (version) VALUES ('0067'); + +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; + +COMMIT; + -- 2.11.0