From 2521487ff636d6aea13ec89cc47979bb66631800 Mon Sep 17 00:00:00 2001 From: Bill Erickson Date: Wed, 8 Apr 2015 17:29:19 -0400 Subject: [PATCH] adding secondary collection hq update for 2.5 update Signed-off-by: Bill Erickson --- ....5.7-upgrade.sql => 01.2.4.1-2.5.7-upgrade.sql} | 0 KCLS/sql/jbas-495/02.collection-hq-items-2.5.sql | 126 +++++++++++++++++++++ 2 files changed, 126 insertions(+) rename KCLS/sql/jbas-495/{jbas-495-2.4.1-2.5.7-upgrade.sql => 01.2.4.1-2.5.7-upgrade.sql} (100%) create mode 100644 KCLS/sql/jbas-495/02.collection-hq-items-2.5.sql diff --git a/KCLS/sql/jbas-495/jbas-495-2.4.1-2.5.7-upgrade.sql b/KCLS/sql/jbas-495/01.2.4.1-2.5.7-upgrade.sql similarity index 100% rename from KCLS/sql/jbas-495/jbas-495-2.4.1-2.5.7-upgrade.sql rename to KCLS/sql/jbas-495/01.2.4.1-2.5.7-upgrade.sql diff --git a/KCLS/sql/jbas-495/02.collection-hq-items-2.5.sql b/KCLS/sql/jbas-495/02.collection-hq-items-2.5.sql new file mode 100644 index 0000000000..222687f9e2 --- /dev/null +++ b/KCLS/sql/jbas-495/02.collection-hq-items-2.5.sql @@ -0,0 +1,126 @@ +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; + + -- 2.11.0