From 2b2ea4ce459803554748dac813d5b9ae7603e407 Mon Sep 17 00:00:00 2001
From: miker <miker@dcc99617-32d9-48b4-a31d-7c20da2025e4>
Date: Tue, 29 Dec 2009 16:06:10 +0000
Subject: [PATCH] add mbts variant view which includes billing location

git-svn-id: svn://svn.open-ils.org/ILS/trunk@15238 dcc99617-32d9-48b4-a31d-7c20da2025e4
---
 Open-ILS/examples/fm_IDL.xml                       | 23 ++++++++++++++++++++++
 Open-ILS/src/sql/Pg/500.view.cross-schema.sql      |  6 ++++++
 .../0126.schema.mbts-with-location-view.sql        | 14 +++++++++++++
 3 files changed, 43 insertions(+)
 create mode 100644 Open-ILS/src/sql/Pg/upgrade/0126.schema.mbts-with-location-view.sql

diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml
index 014144b374..efe055c6ba 100644
--- a/Open-ILS/examples/fm_IDL.xml
+++ b/Open-ILS/examples/fm_IDL.xml
@@ -1107,6 +1107,29 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA
 			<link field="usr" reltype="has_a" key="id" map="" class="au"/>
 		</links>
 	</class>
+	<class id="mbtslv" controller="open-ils.cstore" oils_obj:fieldmapper="money::billable_transaction_summary_location_view" oils_persist:tablename="money.billable_xact_summary_location_view" reporter:label="Billable Transaction Summary with Billing Location" oils_persist:readonly="true" reporter:core="true">
+		<fields oils_persist:primary="id" oils_persist:sequence="">
+			<field reporter:label="Balance Owed" name="balance_owed" reporter:datatype="money"/>
+			<field reporter:label="Transaction ID" name="id" reporter:datatype="id"/>
+			<field reporter:label="Last Billing Note" name="last_billing_note" reporter:datatype="text"/>
+			<field reporter:label="Last Billing Timestamp" name="last_billing_ts" reporter:datatype="timestamp"/>
+			<field reporter:label="Last Billing Type" name="last_billing_type" reporter:datatype="text"/>
+			<field reporter:label="Last Payment Note" name="last_payment_note" reporter:datatype="text"/>
+			<field reporter:label="Last Payment Timestamp" name="last_payment_ts" reporter:datatype="timestamp"/>
+			<field reporter:label="Last Payment Type" name="last_payment_type" reporter:datatype="text"/>
+			<field reporter:label="Total Owed" name="total_owed" reporter:datatype="money"/>
+			<field reporter:label="Total Paid" name="total_paid" reporter:datatype="money"/>
+			<field reporter:label="Billed User" name="usr" reporter:datatype="link"/>
+			<field reporter:label="Transaction Finish Time" name="xact_finish" reporter:datatype="timestamp" />
+			<field reporter:label="Transaction Start Time" name="xact_start" reporter:datatype="timestamp" />
+			<field reporter:label="Transaction Type" name="xact_type" reporter:datatype="text"/>
+			<field reporter:label="Billing Location" name="billing_location" reporter:datatype="link"/>
+		</fields>
+		<links>
+			<link field="usr" reltype="has_a" key="id" map="" class="au"/>
+			<link field="billing_location" reltype="has_a" key="id" map="" class="aou"/>
+		</links>
+	</class>
 	<class id="aun" controller="open-ils.cstore" oils_obj:fieldmapper="actor::usr_note" oils_persist:tablename="actor.usr_note" reporter:label="User Note">
 		<fields oils_persist:primary="id" oils_persist:sequence="actor.usr_note_id_seq">
 			<field reporter:label="Creation Date/Time" name="create_date" reporter:datatype="timestamp"/>
diff --git a/Open-ILS/src/sql/Pg/500.view.cross-schema.sql b/Open-ILS/src/sql/Pg/500.view.cross-schema.sql
index 4909e8219b..b6df81eb23 100644
--- a/Open-ILS/src/sql/Pg/500.view.cross-schema.sql
+++ b/Open-ILS/src/sql/Pg/500.view.cross-schema.sql
@@ -64,6 +64,12 @@ CREATE OR REPLACE VIEW money.open_billable_xact_summary AS
 	  GROUP BY 1,2,3,4,5,15
 	  ORDER BY MAX(debit.billing_ts), MAX(credit.payment_ts);
 
+CREATE OR REPLACE VIEW money.billable_xact_summary_location_view AS
+    SELECT  m.*, COALESCE(c.circ_lib, g.billing_location, r.pickup_lib) AS billing_location
+      FROM  money.materialized_billable_xact_summary m
+            LEFT JOIN action.circulation c ON (c.id = m.id)
+            LEFT JOIN money.grocery g ON (g.id = m.id)
+            LEFT JOIN booking.reservation r ON (r.id = m.id);
 
 CREATE OR REPLACE VIEW money.open_usr_summary AS
 	SELECT	usr,
diff --git a/Open-ILS/src/sql/Pg/upgrade/0126.schema.mbts-with-location-view.sql b/Open-ILS/src/sql/Pg/upgrade/0126.schema.mbts-with-location-view.sql
new file mode 100644
index 0000000000..9856e943d7
--- /dev/null
+++ b/Open-ILS/src/sql/Pg/upgrade/0126.schema.mbts-with-location-view.sql
@@ -0,0 +1,14 @@
+
+BEGIN;
+
+INSERT INTO config.upgrade_log (version) VALUES ('0126'); -- miker
+
+CREATE OR REPLACE VIEW money.billable_xact_summary_location_view AS
+    SELECT  m.*, COALESCE(c.circ_lib, g.billing_location, r.pickup_lib) AS billing_location
+      FROM  money.materialized_billable_xact_summary m
+            LEFT JOIN action.circulation c ON (c.id = m.id)
+            LEFT JOIN money.grocery g ON (g.id = m.id)
+            LEFT JOIN booking.reservation r ON (r.id = m.id);
+
+COMMIT;
+
-- 
2.11.0