From 231f0a96ba01f54e1025659f5223bc5868ff6b6e Mon Sep 17 00:00:00 2001 From: Jeff Godin Date: Thu, 2 Aug 2012 12:01:03 -0400 Subject: [PATCH] Scope collectionHQ extract to org unit + children Enable collectionHQ extract functions to scope to a specified org unit ID and that org unit's children. Specify a default org unit of 1 to closely reproduce previous behavior. NOTE: Even with an org unit of 1, this work-in-progress changes previous behavior due to requiring an asset.call_number with owning_lib in scope. Previously ALL bibs were included. Signed-off-by: Jeff Godin Signed-off-by: Galen Charlton --- collectionHQ/functions.sql | 14 ++++++++------ collectionHQ/get_bibs.sql | 2 +- collectionHQ/get_items.sql | 2 +- 3 files changed, 10 insertions(+), 8 deletions(-) diff --git a/collectionHQ/functions.sql b/collectionHQ/functions.sql index 7aada0b..bfbb149 100644 --- a/collectionHQ/functions.sql +++ b/collectionHQ/functions.sql @@ -60,12 +60,13 @@ CREATE OR REPLACE FUNCTION collectionHQ.quote (TEXT) RETURNS TEXT AS $$ $$ LANGUAGE PLPGSQL STRICT STABLE; -CREATE OR REPLACE FUNCTION collectionHQ.write_item_rows_to_stdout (TEXT) RETURNS TEXT AS $$ --- Usage: SELECT collectionHQ.write_item_rows_to_stdout ('LIBRARYCODE'); +CREATE OR REPLACE FUNCTION collectionHQ.write_item_rows_to_stdout (TEXT, INT) RETURNS TEXT AS $$ +-- Usage: SELECT collectionHQ.write_item_rows_to_stdout ('LIBRARYCODE',org_unit_id); DECLARE item BIGINT; authority_code ALIAS FOR $1; + org_unit_id ALIAS for $2; lms_bib_id TEXT; library_code TEXT; bar_code TEXT; @@ -95,7 +96,7 @@ CREATE OR REPLACE FUNCTION collectionHQ.write_item_rows_to_stdout (TEXT) RETURNS BEGIN FOR item IN - EXECUTE ('SELECT id FROM asset.copy WHERE NOT deleted ORDER BY id;') + EXECUTE ('SELECT id FROM asset.copy WHERE NOT deleted AND circ_lib IN (SELECT id FROM actor.org_unit_descendants(' || org_unit_id || ')) ORDER BY id;') LOOP EXECUTE ('SELECT cn.record FROM asset.call_number cn, asset.copy c WHERE c.call_number = cn.id AND c.id = ' || item || ';') INTO lms_bib_id; @@ -171,11 +172,12 @@ CREATE OR REPLACE FUNCTION collectionHQ.write_item_rows_to_stdout (TEXT) RETURNS $$ LANGUAGE plpgsql; -CREATE OR REPLACE FUNCTION collectionHQ.write_bib_rows_to_stdout (TEXT) RETURNS TEXT AS $$ --- Usage: SELECT collectionHQ.write_bib_rows_to_stdout('LIBRARYCODE'); +CREATE OR REPLACE FUNCTION collectionHQ.write_bib_rows_to_stdout (TEXT, INT) RETURNS TEXT AS $$ +-- Usage: SELECT collectionHQ.write_bib_rows_to_stdout('LIBRARYCODE', org_unit_id); DECLARE library_service_code ALIAS FOR $1; + org_unit_id ALIAS FOR $2; isbn TEXT := ''; title TEXT := ''; author TEXT := ''; @@ -193,7 +195,7 @@ CREATE OR REPLACE FUNCTION collectionHQ.write_bib_rows_to_stdout (TEXT) RETURNS BEGIN FOR lms_bib_id IN - EXECUTE ('SELECT id FROM biblio.record_entry;') + EXECUTE ('SELECT bre.id FROM biblio.record_entry bre JOIN asset.call_number acn ON (acn.record = bre.id) WHERE acn.owning_lib IN (SELECT id FROM actor.org_unit_descendants(' || org_unit_id || ')) AND NOT acn.deleted;') LOOP EXECUTE (E'SELECT isbn[1] FROM reporter.materialized_simple_record r WHERE r.id = ' || lms_bib_id || ';') INTO isbn; diff --git a/collectionHQ/get_bibs.sql b/collectionHQ/get_bibs.sql index fbae37d..833db34 100644 --- a/collectionHQ/get_bibs.sql +++ b/collectionHQ/get_bibs.sql @@ -1 +1 @@ -SELECT collectionHQ.write_bib_rows_to_stdout('LIBRARYCODE'); +SELECT collectionHQ.write_bib_rows_to_stdout('LIBRARYCODE',1); diff --git a/collectionHQ/get_items.sql b/collectionHQ/get_items.sql index 0836cf6..218f593 100644 --- a/collectionHQ/get_items.sql +++ b/collectionHQ/get_items.sql @@ -1 +1 @@ -SELECT collectionHQ.write_item_rows_to_stdout('LIBRARYCODE'); +SELECT collectionHQ.write_item_rows_to_stdout('LIBRARYCODE',1); -- 2.11.0