From b0e18d9c1d3e2c75465c4ec4740dab3ae1979e27 Mon Sep 17 00:00:00 2001 From: blake Date: Thu, 7 Jul 2016 15:01:36 -0500 Subject: [PATCH] LP1599634 Circulation report source to include in-house(non cat), and non cat circ Added a simple view and supporting IDL. This will allow all the circulation data to output in the same report. Signed-off-by: blake Signed-off-by: Chris Sharp Conflicts: Open-ILS/src/sql/Pg/090.schema.action.sql --- Open-ILS/examples/fm_IDL.xml | 20 +++++++ Open-ILS/src/sql/Pg/090.schema.action.sql | 56 +++++++++++++++++++ ..._source_to_include_in-house_and_non_cat_circ.pg | 27 ++++++++++ ...ort_source_to_include_in-house_and_non_cat_circ | 63 ++++++++++++++++++++++ ...ource_to_include_in-house_and_non_cat_circ.adoc | 12 +++++ 5 files changed, 178 insertions(+) create mode 100644 Open-ILS/src/sql/Pg/t/lp1599634_Circulation_report_source_to_include_in-house_and_non_cat_circ.pg create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.LP1599634_Circulation_report_source_to_include_in-house_and_non_cat_circ create mode 100644 docs/lp1599634_Circulation_report_source_to_include_in-house_and_non_cat_circ.adoc diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml index 8cff802157..498718956a 100644 --- a/Open-ILS/examples/fm_IDL.xml +++ b/Open-ILS/examples/fm_IDL.xml @@ -4113,6 +4113,26 @@ SELECT usr, + + + + + + + + + + + + + + + + + + + + diff --git a/Open-ILS/src/sql/Pg/090.schema.action.sql b/Open-ILS/src/sql/Pg/090.schema.action.sql index ca1e7f3894..2b5406037f 100644 --- a/Open-ILS/src/sql/Pg/090.schema.action.sql +++ b/Open-ILS/src/sql/Pg/090.schema.action.sql @@ -1370,4 +1370,60 @@ $f$ LANGUAGE PLPGSQL; CREATE TRIGGER hold_copy_proximity_update_tgr BEFORE INSERT OR UPDATE ON action.hold_copy_map FOR EACH ROW EXECUTE PROCEDURE action.hold_copy_calculated_proximity_update (); +CREATE OR REPLACE VIEW action.all_circulation_combined_types AS + SELECT 'regularcirc'::text || acirc.id AS id, + acirc.xact_start, + acirc.circ_lib, + acirc.circ_staff, + acirc.create_time, + ac_acirc.circ_modifier AS item_type, + 'regular_circ'::text AS circ_type + FROM action.circulation acirc, + asset.copy ac_acirc + WHERE acirc.target_copy = ac_acirc.id +UNION ALL + SELECT 'noncatcirc'::text || ancc.id AS id, + ancc.circ_time AS xact_start, + ancc.circ_lib, + ancc.staff AS circ_staff, + ancc.circ_time AS create_time, + cnct_ancc.name AS item_type, + 'non-cat_circ'::text AS circ_type + FROM action.non_cataloged_circulation ancc, + config.non_cataloged_type cnct_ancc + WHERE ancc.item_type = cnct_ancc.id +UNION ALL + SELECT 'inhouseuse'::text || aihu.id AS id, + aihu.use_time AS xact_start, + aihu.org_unit AS circ_lib, + aihu.staff AS circ_staff, + aihu.use_time AS create_time, + ac_aihu.circ_modifier AS item_type, + 'in-house_use'::text AS circ_type + FROM action.in_house_use aihu, + asset.copy ac_aihu + WHERE aihu.item = ac_aihu.id +UNION ALL + SELECT 'noncatinhouseuse'::text || ancihu.id AS id, + ancihu.use_time AS xact_start, + ancihu.org_unit AS circ_lib, + ancihu.staff AS circ_staff, + ancihu.use_time AS create_time, + cnct_ancihu.name AS item_type, + 'non-cat_circ'::text AS circ_type + FROM action.non_cat_in_house_use ancihu, + config.non_cataloged_type cnct_ancihu + WHERE ancihu.item_type = cnct_ancihu.id +UNION ALL + SELECT 'agedcirc'::text || aacirc.id::text AS id, + aacirc.xact_start, + aacirc.circ_lib, + aacirc.circ_staff, + aacirc.create_time, + ac_aacirc.circ_modifier AS item_type, + 'aged_circ'::text AS circ_type + FROM action.aged_circulation aacirc, + asset.copy ac_aacirc + WHERE aacirc.target_copy = ac_aacirc.id; + COMMIT; diff --git a/Open-ILS/src/sql/Pg/t/lp1599634_Circulation_report_source_to_include_in-house_and_non_cat_circ.pg b/Open-ILS/src/sql/Pg/t/lp1599634_Circulation_report_source_to_include_in-house_and_non_cat_circ.pg new file mode 100644 index 0000000000..27957199e4 --- /dev/null +++ b/Open-ILS/src/sql/Pg/t/lp1599634_Circulation_report_source_to_include_in-house_and_non_cat_circ.pg @@ -0,0 +1,27 @@ +\set ECHO +\set QUIET 1 +-- Turn off echo and keep things quiet. + +-- Format the output for nice TAP. +\pset format unaligned +\pset tuples_only true +\pset pager + +-- Revert all changes on failure. +\set ON_ERROR_ROLLBACK 1 +\set ON_ERROR_STOP true +\set QUIET 1 + +-- Load the TAP functions. +BEGIN; + +-- Plan the tests. +SELECT plan(1); + +-- Run the tests. +-- Check to make sure that the action.all_circulation_combined_types view exists +SELECT has_view( 'action', 'all_circulation_combined_types', 'VIEW action.all_circulation_combined_types EXISTS' ); + +-- Finish the tests and clean up. +SELECT * FROM finish(); +ROLLBACK; diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.LP1599634_Circulation_report_source_to_include_in-house_and_non_cat_circ b/Open-ILS/src/sql/Pg/upgrade/XXXX.LP1599634_Circulation_report_source_to_include_in-house_and_non_cat_circ new file mode 100644 index 0000000000..8042c32740 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.LP1599634_Circulation_report_source_to_include_in-house_and_non_cat_circ @@ -0,0 +1,63 @@ +BEGIN; + +SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version); + +DROP VIEW action.all_circulation_combined_types; + +CREATE OR REPLACE VIEW action.all_circulation_combined_types AS + SELECT 'regularcirc'::text || acirc.id AS id, + acirc.xact_start, + acirc.circ_lib, + acirc.circ_staff, + acirc.create_time, + ac_acirc.circ_modifier AS item_type, + 'regular_circ'::text AS circ_type + FROM action.circulation acirc, + asset.copy ac_acirc + WHERE acirc.target_copy = ac_acirc.id +UNION ALL + SELECT 'noncatcirc'::text || ancc.id AS id, + ancc.circ_time AS xact_start, + ancc.circ_lib, + ancc.staff AS circ_staff, + ancc.circ_time AS create_time, + cnct_ancc.name AS item_type, + 'non-cat_circ'::text AS circ_type + FROM action.non_cataloged_circulation ancc, + config.non_cataloged_type cnct_ancc + WHERE ancc.item_type = cnct_ancc.id +UNION ALL + SELECT 'inhouseuse'::text || aihu.id AS id, + aihu.use_time AS xact_start, + aihu.org_unit AS circ_lib, + aihu.staff AS circ_staff, + aihu.use_time AS create_time, + ac_aihu.circ_modifier AS item_type, + 'in-house_use'::text AS circ_type + FROM action.in_house_use aihu, + asset.copy ac_aihu + WHERE aihu.item = ac_aihu.id +UNION ALL + SELECT 'noncatinhouseuse'::text || ancihu.id AS id, + ancihu.use_time AS xact_start, + ancihu.org_unit AS circ_lib, + ancihu.staff AS circ_staff, + ancihu.use_time AS create_time, + cnct_ancihu.name AS item_type, + 'non-cat_circ'::text AS circ_type + FROM action.non_cat_in_house_use ancihu, + config.non_cataloged_type cnct_ancihu + WHERE ancihu.item_type = cnct_ancihu.id +UNION ALL + SELECT 'agedcirc'::text || aacirc.id::text AS id, + aacirc.xact_start, + aacirc.circ_lib, + aacirc.circ_staff, + aacirc.create_time, + ac_aacirc.circ_modifier AS item_type, + 'aged_circ'::text AS circ_type + FROM action.aged_circulation aacirc, + asset.copy ac_aacirc + WHERE aacirc.target_copy = ac_aacirc.id; + +COMMIT; diff --git a/docs/lp1599634_Circulation_report_source_to_include_in-house_and_non_cat_circ.adoc b/docs/lp1599634_Circulation_report_source_to_include_in-house_and_non_cat_circ.adoc new file mode 100644 index 0000000000..46d12cb19b --- /dev/null +++ b/docs/lp1599634_Circulation_report_source_to_include_in-house_and_non_cat_circ.adoc @@ -0,0 +1,12 @@ +New circulation report source "All Circulation Combined Types" +^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ + +This report source will allow you to create a single report template for all of the following: + * In House + * In House Non Cat + * Circulation (standard) + * Non Cat Circulation + +These columns are important to display: + * Item Type + * Circulation Type -- 2.11.0