From 0e1fdb91a81b09c469485bb8e7db80e181ead3d7 Mon Sep 17 00:00:00 2001 From: Jason Boyer Date: Mon, 28 Jan 2019 10:22:01 -0500 Subject: [PATCH] LP1813191: Add a Dewey Classification View to acn The most-missed part of the Classic Item View in the reporter are the dewey ranges and blocks. This branch pulls those out into their own view and hangs it off of a link from asset.call_number so they can be used in reports from any path that connects to acn. Signed-off-by: Jason Boyer --- .../sql/Pg/upgrade/XXXX.schema.dewey_ranges.sql | 27 ++++++++++++++++++++++ 1 file changed, 27 insertions(+) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.schema.dewey_ranges.sql diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.dewey_ranges.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.dewey_ranges.sql new file mode 100644 index 0000000000..fdc85255b9 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.dewey_ranges.sql @@ -0,0 +1,27 @@ +BEGIN; + +SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version); + +CREATE OR REPLACE VIEW reporter.asset_call_number_dewey AS + SELECT id AS call_number, + call_number_dewey(label) AS dewey, + CASE + WHEN call_number_dewey(label) ~ '^[0-9][0-9.]*$'::text THEN btrim(to_char(10::double precision * floor(call_number_dewey(label)::double precision / 10::double precision), '000'::text)) + ELSE NULL::text + END AS dewey_block_tens, + CASE + WHEN call_number_dewey(label) ~ '^[0-9][0-9.]*$'::text THEN btrim(to_char(100::double precision * floor(call_number_dewey(label)::double precision / 100::double precision), '000'::text)) + ELSE NULL::text + END AS dewey_block_hundreds, + CASE + WHEN call_number_dewey(label) ~ '^[0-9][0-9.]*$'::text THEN (btrim(to_char(10::double precision * floor(call_number_dewey(label)::double precision / 10::double precision), '000'::text)) || '-'::text) || btrim(to_char(10::double precision * floor(call_number_dewey(label)::double precision / 10::double precision) + 9::double precision, '000'::text)) + ELSE NULL::text + END AS dewey_range_tens, + CASE + WHEN call_number_dewey(label) ~ '^[0-9][0-9.]*$'::text THEN (btrim(to_char(100::double precision * floor(call_number_dewey(label)::double precision / 100::double precision), '000'::text)) || '-'::text) || btrim(to_char(100::double precision * floor(call_number_dewey(label)::double precision / 100::double precision) + 99::double precision, '000'::text)) + ELSE NULL::text + END AS dewey_range_hundreds + FROM asset.call_number + WHERE call_number_dewey(label) ~ '^[0-9]'::text; + +COMMIT; -- 2.11.0