From 929970f91be9ba5a28018b53f7c550428e988eea Mon Sep 17 00:00:00 2001 From: Bill Erickson Date: Thu, 17 Sep 2015 14:55:50 -0400 Subject: [PATCH] JBAS-909 CollectionHQ floating case/when repair Avoid logging every copy as floating by using CASE WHEN NULL correctly in the CHQ query. Signed-off-by: Bill Erickson --- .../schema/deploy/collection-hq-floating-case.sql | 134 +++++++++++++++++++++ .../schema/revert/collection-hq-floating-case.sql | 131 ++++++++++++++++++++ KCLS/sql/schema/sqitch.plan | 1 + .../schema/verify/collection-hq-floating-case.sql | 7 ++ 4 files changed, 273 insertions(+) create mode 100644 KCLS/sql/schema/deploy/collection-hq-floating-case.sql create mode 100644 KCLS/sql/schema/revert/collection-hq-floating-case.sql create mode 100644 KCLS/sql/schema/verify/collection-hq-floating-case.sql diff --git a/KCLS/sql/schema/deploy/collection-hq-floating-case.sql b/KCLS/sql/schema/deploy/collection-hq-floating-case.sql new file mode 100644 index 0000000000..d3e38da368 --- /dev/null +++ b/KCLS/sql/schema/deploy/collection-hq-floating-case.sql @@ -0,0 +1,134 @@ +-- Deploy kcls-evergreen:collection-hq-floating-case to pg +-- requires: blanket-po + +BEGIN; + +CREATE OR REPLACE FUNCTION + collectionhq.write_item_rows_to_stdout(TEXT, INTEGER) RETURNS TEXT AS $$ + +DECLARE + item BIGINT; + authority_code ALIAS FOR $1; + org_unit_id ALIAS for $2; + lms_bib_id BIGINT; + library_code TEXT; + bar_code TEXT; + last_use_date TEXT; + cumulative_use_total TEXT; + cumulative_use_current TEXT; + status TEXT; + date_added TEXT; + price TEXT; + purchase_code TEXT; + rotating_stock TEXT; + lib_supsel_tag TEXT; + gen_supsel_tag TEXT; + notes TEXT; + extract_date TEXT; + collection_code TEXT; + collection_code_level_2 TEXT; + filter_level_1 TEXT; + filter_level_2 TEXT; + filter_level_3 TEXT; + filter_level_4 TEXT; + isbn TEXT := ''; + output TEXT := ''; + arrived TIMESTAMPTZ; + num_rows INTEGER := 0; + +BEGIN + + SELECT REPLACE(NOW()::DATE::TEXT, '-', '') INTO extract_date; + FOR item, arrived, cumulative_use_total IN + SELECT cp.id, dest_recv_time, COALESCE(sum(DISTINCT c.circ_count), 0::bigint) + COALESCE(count(DISTINCT circ.id), 0::bigint) + COALESCE(count(DISTINCT acirc.id), 0::bigint) AS circ_count + FROM asset.copy cp + LEFT JOIN extend_reporter.legacy_circ_count c USING (id) + LEFT JOIN (SELECT max(dest_recv_time) as dest_recv_time, target_copy, dest from action.transit_copy group by target_copy, dest) atc ON (cp.id = atc.target_copy AND cp.circ_lib = atc.dest) + LEFT JOIN action.circulation circ ON circ.target_copy = cp.id + LEFT JOIN action.aged_circulation acirc ON acirc.target_copy = cp.id + WHERE NOT cp.deleted AND cp.circ_lib IN (SELECT id FROM actor.org_unit_descendants(org_unit_id)) GROUP BY cp.id, dest_recv_time ORDER BY cp.id + LOOP + + SELECT cn.record, cn.label + INTO lms_bib_id, filter_level_1 + FROM asset.call_number cn, asset.copy c + WHERE c.call_number = cn.id AND c.id = item; + SELECT r.isbn[1] INTO isbn + FROM reporter.materialized_simple_record r + WHERE id = lms_bib_id; + SELECT collectionHQ.attempt_price(ac.price::TEXT), barcode, ac.status, + REPLACE(create_date::DATE::TEXT, '-', ''), + CASE WHEN floating IS NULL THEN NULL ELSE 'Y' END + INTO price, bar_code, status, date_added, rotating_stock + FROM asset.copy ac + WHERE id = item; + IF price IS NULL OR price = '' THEN + SELECT collectionHQ.attempt_price((XPATH('//marc:datafield[@tag="020"][1]/marc:subfield[@code="c"]/text()', marc::XML, ARRAY[ARRAY['marc', 'http://www.loc.gov/MARC21/slim']]))[1]::TEXT) + INTO price + FROM biblio.record_entry + WHERE id = lms_bib_id; + END IF; + SELECT ou.shortname INTO library_code FROM actor.org_unit ou, asset.copy c WHERE ou.id = c.circ_lib AND c.id = item; + /* SELECT REPLACE(xact_start::DATE::TEXT, '-', '') INTO last_use_date FROM action.circulation WHERE target_copy = item ORDER BY xact_start DESC LIMIT 1; */ + SELECT REPLACE(xact_start::DATE::TEXT, '-', '') INTO last_use_date FROM ( SELECT action.aged_circulation.xact_start FROM action.aged_circulation where target_copy = item UNION SELECT action.circulation.xact_start FROM action.circulation where target_copy = item) as lu order by xact_start DESC limit 1; + + + IF arrived IS NOT NULL THEN + SELECT COUNT(*) INTO cumulative_use_current FROM action.circulation WHERE target_copy = item AND xact_start > arrived; + ELSE + cumulative_use_current := '0'; + END IF; + SELECT SUBSTRING(value FROM 1 FOR 100) INTO notes FROM asset.copy_note WHERE owning_copy = item AND title ILIKE '%collectionHQ%' ORDER BY id LIMIT 1; + SELECT l.name INTO collection_code FROM asset.copy c, asset.copy_location l WHERE c.location = l.id AND c.id = item; + + purchase_code := ''; -- FIXME do we want something else here? + lib_supsel_tag := ''; -- FIXME do we want something else here? + gen_supsel_tag := ''; -- FIXME do we want something else here? + collection_code_level_2 := ''; -- FIXME do we want something else here? + filter_level_2 := ''; -- FIXME do we want something else here? + filter_level_3 := ''; -- FIXME do we want something else here? + filter_level_4 := ''; -- FIXME do we want something else here? + + output := '##HOLD##,' + || lms_bib_id || ',' + || COALESCE(collectionHQ.quote(authority_code), '') || ',' + || COALESCE(collectionHQ.quote(library_code), '') || ',' + || COALESCE(collectionHQ.quote(bar_code), '') || ',' + || COALESCE(collectionHQ.quote(last_use_date), '') || ',' + || COALESCE(cumulative_use_total, '') || ',' + || COALESCE(cumulative_use_current, '') || ',' + || COALESCE(collectionHQ.quote(status), '') || ',' + || COALESCE(collectionHQ.quote(date_added), '') || ',' + || COALESCE(price, '') || ',' + || COALESCE(collectionHQ.quote(purchase_code), '') || ',' + || COALESCE(collectionHQ.quote(rotating_stock), '') || ',' + || COALESCE(collectionHQ.quote(lib_supsel_tag), '') || ',' + || COALESCE(collectionHQ.quote(gen_supsel_tag), '') || ',' + || COALESCE(collectionHQ.quote(notes), '') || ',' + || COALESCE(collectionHQ.quote(extract_date), '') || ',' + || COALESCE(collectionHQ.quote(collection_code), '') || ',' + || COALESCE(collectionHQ.quote(collection_code_level_2), '') || ',' + || COALESCE(collectionHQ.quote(filter_level_1), '') || ',' + || COALESCE(collectionHQ.quote(filter_level_2), '') || ',' + || COALESCE(collectionHQ.quote(filter_level_3), '') || ',' + || COALESCE(collectionHQ.quote(filter_level_4), '') || ',' + || COALESCE(collectionHQ.quote(isbn), ''); + + RAISE INFO '%', output; + + num_rows := num_rows + 1; + IF (num_rows::numeric % 1000.0 = 0.0) THEN RAISE INFO '% rows written', num_rows; END IF; + + END LOOP; + + RAISE INFO '% rows written in total.', num_rows; + + RETURN ''; +END; + +$$ LANGUAGE plpgsql VOLATILE; + + +-- XXX Add DDLs here. + +COMMIT; diff --git a/KCLS/sql/schema/revert/collection-hq-floating-case.sql b/KCLS/sql/schema/revert/collection-hq-floating-case.sql new file mode 100644 index 0000000000..ec27e65d2d --- /dev/null +++ b/KCLS/sql/schema/revert/collection-hq-floating-case.sql @@ -0,0 +1,131 @@ +-- Revert kcls-evergreen:collection-hq-floating-case from pg + +BEGIN; + +CREATE OR REPLACE FUNCTION + collectionhq.write_item_rows_to_stdout(TEXT, INTEGER) RETURNS TEXT AS $$ + +DECLARE + item BIGINT; + authority_code ALIAS FOR $1; + org_unit_id ALIAS for $2; + lms_bib_id BIGINT; + library_code TEXT; + bar_code TEXT; + last_use_date TEXT; + cumulative_use_total TEXT; + cumulative_use_current TEXT; + status TEXT; + date_added TEXT; + price TEXT; + purchase_code TEXT; + rotating_stock TEXT; + lib_supsel_tag TEXT; + gen_supsel_tag TEXT; + notes TEXT; + extract_date TEXT; + collection_code TEXT; + collection_code_level_2 TEXT; + filter_level_1 TEXT; + filter_level_2 TEXT; + filter_level_3 TEXT; + filter_level_4 TEXT; + isbn TEXT := ''; + output TEXT := ''; + arrived TIMESTAMPTZ; + num_rows INTEGER := 0; + +BEGIN + + SELECT REPLACE(NOW()::DATE::TEXT, '-', '') INTO extract_date; + FOR item, arrived, cumulative_use_total IN + SELECT cp.id, dest_recv_time, COALESCE(sum(DISTINCT c.circ_count), 0::bigint) + COALESCE(count(DISTINCT circ.id), 0::bigint) + COALESCE(count(DISTINCT acirc.id), 0::bigint) AS circ_count + FROM asset.copy cp + LEFT JOIN extend_reporter.legacy_circ_count c USING (id) + LEFT JOIN (SELECT max(dest_recv_time) as dest_recv_time, target_copy, dest from action.transit_copy group by target_copy, dest) atc ON (cp.id = atc.target_copy AND cp.circ_lib = atc.dest) + LEFT JOIN action.circulation circ ON circ.target_copy = cp.id + LEFT JOIN action.aged_circulation acirc ON acirc.target_copy = cp.id + WHERE NOT cp.deleted AND cp.circ_lib IN (SELECT id FROM actor.org_unit_descendants(org_unit_id)) GROUP BY cp.id, dest_recv_time ORDER BY cp.id + LOOP + + SELECT cn.record, cn.label + INTO lms_bib_id, filter_level_1 + FROM asset.call_number cn, asset.copy c + WHERE c.call_number = cn.id AND c.id = item; + SELECT r.isbn[1] INTO isbn + FROM reporter.materialized_simple_record r + WHERE id = lms_bib_id; + SELECT collectionHQ.attempt_price(ac.price::TEXT), barcode, ac.status, + REPLACE(create_date::DATE::TEXT, '-', ''), + CASE floating WHEN NULL THEN NULL ELSE 'Y' END + INTO price, bar_code, status, date_added, rotating_stock + FROM asset.copy ac + WHERE id = item; + IF price IS NULL OR price = '' THEN + SELECT collectionHQ.attempt_price((XPATH('//marc:datafield[@tag="020"][1]/marc:subfield[@code="c"]/text()', marc::XML, ARRAY[ARRAY['marc', 'http://www.loc.gov/MARC21/slim']]))[1]::TEXT) + INTO price + FROM biblio.record_entry + WHERE id = lms_bib_id; + END IF; + SELECT ou.shortname INTO library_code FROM actor.org_unit ou, asset.copy c WHERE ou.id = c.circ_lib AND c.id = item; + /* SELECT REPLACE(xact_start::DATE::TEXT, '-', '') INTO last_use_date FROM action.circulation WHERE target_copy = item ORDER BY xact_start DESC LIMIT 1; */ + SELECT REPLACE(xact_start::DATE::TEXT, '-', '') INTO last_use_date FROM ( SELECT action.aged_circulation.xact_start FROM action.aged_circulation where target_copy = item UNION SELECT action.circulation.xact_start FROM action.circulation where target_copy = item) as lu order by xact_start DESC limit 1; + + + IF arrived IS NOT NULL THEN + SELECT COUNT(*) INTO cumulative_use_current FROM action.circulation WHERE target_copy = item AND xact_start > arrived; + ELSE + cumulative_use_current := '0'; + END IF; + SELECT SUBSTRING(value FROM 1 FOR 100) INTO notes FROM asset.copy_note WHERE owning_copy = item AND title ILIKE '%collectionHQ%' ORDER BY id LIMIT 1; + SELECT l.name INTO collection_code FROM asset.copy c, asset.copy_location l WHERE c.location = l.id AND c.id = item; + + purchase_code := ''; -- FIXME do we want something else here? + lib_supsel_tag := ''; -- FIXME do we want something else here? + gen_supsel_tag := ''; -- FIXME do we want something else here? + collection_code_level_2 := ''; -- FIXME do we want something else here? + filter_level_2 := ''; -- FIXME do we want something else here? + filter_level_3 := ''; -- FIXME do we want something else here? + filter_level_4 := ''; -- FIXME do we want something else here? + + output := '##HOLD##,' + || lms_bib_id || ',' + || COALESCE(collectionHQ.quote(authority_code), '') || ',' + || COALESCE(collectionHQ.quote(library_code), '') || ',' + || COALESCE(collectionHQ.quote(bar_code), '') || ',' + || COALESCE(collectionHQ.quote(last_use_date), '') || ',' + || COALESCE(cumulative_use_total, '') || ',' + || COALESCE(cumulative_use_current, '') || ',' + || COALESCE(collectionHQ.quote(status), '') || ',' + || COALESCE(collectionHQ.quote(date_added), '') || ',' + || COALESCE(price, '') || ',' + || COALESCE(collectionHQ.quote(purchase_code), '') || ',' + || COALESCE(collectionHQ.quote(rotating_stock), '') || ',' + || COALESCE(collectionHQ.quote(lib_supsel_tag), '') || ',' + || COALESCE(collectionHQ.quote(gen_supsel_tag), '') || ',' + || COALESCE(collectionHQ.quote(notes), '') || ',' + || COALESCE(collectionHQ.quote(extract_date), '') || ',' + || COALESCE(collectionHQ.quote(collection_code), '') || ',' + || COALESCE(collectionHQ.quote(collection_code_level_2), '') || ',' + || COALESCE(collectionHQ.quote(filter_level_1), '') || ',' + || COALESCE(collectionHQ.quote(filter_level_2), '') || ',' + || COALESCE(collectionHQ.quote(filter_level_3), '') || ',' + || COALESCE(collectionHQ.quote(filter_level_4), '') || ',' + || COALESCE(collectionHQ.quote(isbn), ''); + + RAISE INFO '%', output; + + num_rows := num_rows + 1; + IF (num_rows::numeric % 1000.0 = 0.0) THEN RAISE INFO '% rows written', num_rows; END IF; + + END LOOP; + + RAISE INFO '% rows written in total.', num_rows; + + RETURN ''; +END; + +$$ LANGUAGE plpgsql VOLATILE; + + +COMMIT; diff --git a/KCLS/sql/schema/sqitch.plan b/KCLS/sql/schema/sqitch.plan index 580fb4380e..a63a4c3980 100644 --- a/KCLS/sql/schema/sqitch.plan +++ b/KCLS/sql/schema/sqitch.plan @@ -11,3 +11,4 @@ KMAIN-1765-reingest-causing-false-browse-headings [02.collection-hq-items-2.5] 2 insert-on-deploy [KMAIN-1765-reingest-causing-false-browse-headings] 2015-08-14T20:36:56Z Bill Erickson # Check for wether to insert data on deploy copy-stat-checkout-ok [insert-on-deploy] 2015-08-14T20:47:19Z Bill Erickson # copy_status.checkout_ok flag for Grand Opening blanket-po [copy-stat-checkout-ok] 2015-08-14T21:03:02Z Bill Erickson # Blanket PO +collection-hq-floating-case [blanket-po] 2015-09-17T18:42:09Z Bill Erickson # CollectionHQ floating CASE/WHEN fix diff --git a/KCLS/sql/schema/verify/collection-hq-floating-case.sql b/KCLS/sql/schema/verify/collection-hq-floating-case.sql new file mode 100644 index 0000000000..9c511930e4 --- /dev/null +++ b/KCLS/sql/schema/verify/collection-hq-floating-case.sql @@ -0,0 +1,7 @@ +-- Verify kcls-evergreen:collection-hq-floating-case on pg + +BEGIN; + +-- XXX Add verifications here. + +ROLLBACK; -- 2.11.0