From 4e64cbbc4cdda08a60d3e9c45a90f2e9ca0309f5 Mon Sep 17 00:00:00 2001
From: blake <blake@mobiusconsortium.org>
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 <blake@mobiusconsortium.org>
Signed-off-by: Chris Sharp <csharp@georgialibraries.org>
Signed-off-by: Galen Charlton <gmc@esilibrary.com>
Signed-off-by: blake <blake@mobiusconsortium.org>

Conflicts:
	Open-ILS/examples/fm_IDL.xml

Signed-off-by: Galen Charlton <gmc@equinoxinitiative.org>
---
 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 86efb0dcf1..50b0dc4ff5 100644
--- a/Open-ILS/examples/fm_IDL.xml
+++ b/Open-ILS/examples/fm_IDL.xml
@@ -4219,6 +4219,26 @@ SELECT  usr,
 			</actions>
 		</permacrud>
 	</class>
+	<class id="aacct" controller="open-ils.cstore open-ils.pcrud" oils_obj:fieldmapper="action::all_circulation_combined_types" oils_persist:tablename="action.all_circulation_combined_types" reporter:core="true" reporter:label="All Circulation Combined Types" oils_persist:readonly="true">
+		<fields>
+			<field reporter:label="Circulating Library" name="circ_lib" reporter:datatype="org_unit"/>
+			<field reporter:label="Circulating Staff" name="circ_staff" reporter:datatype="link"/>
+			<field reporter:label="Circ ID" name="id" reporter:datatype="text" />
+			<field reporter:label="Checkout Date/Time" name="xact_start" reporter:datatype="timestamp" />
+			<field reporter:label="Create Date/Time" name="create_time" reporter:datatype="timestamp" />
+			<field reporter:label="Circulation Type" name="circ_type" reporter:datatype="text"/>
+			<field reporter:label="Item Type" name="item_type" reporter:datatype="text"/>
+		</fields>
+		<links>
+			<link field="circ_staff" reltype="has_a" key="id" map="" class="au"/>
+			<link field="circ_lib" reltype="has_a" key="id" map="" class="aou"/>
+		</links>
+		<permacrud xmlns="http://open-ils.org/spec/opensrf/IDL/permacrud/v1">
+			<actions>
+				<retrieve permission="VIEW_CIRCULATIONS" context_field="circ_lib" />
+			</actions>
+		</permacrud>
+	</class>
 	<class id="combcirc" controller="open-ils.cstore open-ils.pcrud" oils_obj:fieldmapper="action::all_circulation" oils_persist:tablename="action.all_circulation" reporter:core="true" reporter:label="Combined Aged and Active Circulations" oils_persist:readonly="true">
 		<fields oils_persist:primary="id" oils_persist:sequence="money.billable_xact_id_seq">
 			<field reporter:label="Check In Library" name="checkin_lib" reporter:datatype="org_unit"/>
diff --git a/Open-ILS/src/sql/Pg/090.schema.action.sql b/Open-ILS/src/sql/Pg/090.schema.action.sql
index 5832034eba..3744e1562e 100644
--- a/Open-ILS/src/sql/Pg/090.schema.action.sql
+++ b/Open-ILS/src/sql/Pg/090.schema.action.sql
@@ -1502,4 +1502,60 @@ CREATE TRIGGER maintain_usr_circ_history_tgr
     AFTER INSERT OR UPDATE ON action.circulation 
     FOR EACH ROW EXECUTE PROCEDURE action.maintain_usr_circ_history();
 
+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