From ef72cdf8ff54a32692c1b275d1738e65e448ce24 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 Signed-off-by: Terran McCanna Signed-off-by: Galen Charlton --- Open-ILS/examples/fm_IDL.xml | 20 +++++++++++++++ Open-ILS/src/sql/Pg/reporter-schema.sql | 24 ++++++++++++++++++ .../sql/Pg/upgrade/XXXX.schema.dewey_ranges.sql | 29 ++++++++++++++++++++++ .../Reports/dewey_call_ranges.adoc | 7 ++++++ 4 files changed, 80 insertions(+) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.schema.dewey_ranges.sql create mode 100644 docs/RELEASE_NOTES_NEXT/Reports/dewey_call_ranges.adoc diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml index 0cc6925bc2..32816d90d9 100644 --- a/Open-ILS/examples/fm_IDL.xml +++ b/Open-ILS/examples/fm_IDL.xml @@ -3337,6 +3337,7 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA + @@ -3350,6 +3351,7 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA + @@ -3360,6 +3362,24 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA + + + + + + + + + + + + + + + + + + diff --git a/Open-ILS/src/sql/Pg/reporter-schema.sql b/Open-ILS/src/sql/Pg/reporter-schema.sql index 68b7cfcb5e..fb427ca16a 100644 --- a/Open-ILS/src/sql/Pg/reporter-schema.sql +++ b/Open-ILS/src/sql/Pg/reporter-schema.sql @@ -232,6 +232,30 @@ CREATE OR REPLACE FUNCTION reporter.refresh_materialized_simple_record () RETURN SELECT reporter.enable_materialized_simple_record_trigger(); $$ LANGUAGE SQL; +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; + CREATE OR REPLACE VIEW reporter.demographic AS SELECT u.id, u.dob, 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..e877290d36 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.dewey_ranges.sql @@ -0,0 +1,29 @@ +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; diff --git a/docs/RELEASE_NOTES_NEXT/Reports/dewey_call_ranges.adoc b/docs/RELEASE_NOTES_NEXT/Reports/dewey_call_ranges.adoc new file mode 100644 index 0000000000..8897861f71 --- /dev/null +++ b/docs/RELEASE_NOTES_NEXT/Reports/dewey_call_ranges.adoc @@ -0,0 +1,7 @@ +Add Dewey Call Number Blocks and Ranges to Reports +^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ +A new view is added to the reporter with links from Call Number that +will allow users to display or filter on the Dewey 10's or 100's block +or range that a call number falls within. They can be accessed by +following the "Dewey Classification" link from Call Number. + -- 2.11.0