From 522888735197d6a9579e2fba2922cde002bc3426 Mon Sep 17 00:00:00 2001
From: Bill Erickson <berickxx@gmail.com>
Date: Mon, 24 Sep 2018 10:33:13 -0400
Subject: [PATCH] LP#1793802 Add money.all_[payments|billings] views

Similar to money.all_circulation_slim, add views to collect data from
both active and aged versions of the payment and billing tables.

Signed-off-by: Bill Erickson <berickxx@gmail.com>
Signed-off-by: Chris Sharp <csharp@georgialibraries.org>
Signed-off-by: Galen Charlton <gmc@equinoxinitiative.org>
---
 Open-ILS/examples/fm_IDL.xml                  | 51 ++++++++++++++++++-
 Open-ILS/src/sql/Pg/080.schema.money.sql      | 11 ++++
 .../XXXX.schema.aged-billing-payment.sql      | 10 ++++
 3 files changed, 70 insertions(+), 2 deletions(-)

diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml
index 19e65a9f85..d1027b179d 100644
--- a/Open-ILS/examples/fm_IDL.xml
+++ b/Open-ILS/examples/fm_IDL.xml
@@ -8070,7 +8070,7 @@ SELECT  usr,
 		oils_obj:fieldmapper="money::aged_payment" 
 		oils_persist:tablename="money.aged_payment" 
 		reporter:label="Payments: Aged">
-		<fields oils_persist:primary="id" oils_persist:sequence="">
+		<fields oils_persist:primary="id" oils_persist:sequence="money.payment_id_seq">
 			<field reporter:label="Amount" name="amount" reporter:datatype="money" />
 			<field reporter:label="Payment ID" name="id" reporter:datatype="id" />
 			<field reporter:label="Note" name="note" reporter:datatype="text"/>
@@ -8090,6 +8090,26 @@ SELECT  usr,
 			</actions>
 		</permacrud>
 	</class>
+	<class id="mallp" controller="open-ils.cstore" 
+		oils_obj:fieldmapper="money::all_payments" 
+		oils_persist:tablename="money.all_payments" 
+		oils_persist="readonly" reporter:label="All Payments">
+		<fields oils_persist:primary="id" oils_persist:sequence="money.payment_id_seq">
+			<field reporter:label="Amount" name="amount" reporter:datatype="money" />
+			<field reporter:label="Payment ID" name="id" reporter:datatype="id" />
+			<field reporter:label="Note" name="note" reporter:datatype="text"/>
+			<field reporter:label="Payment Date/Time" name="payment_ts" reporter:datatype="timestamp"/>
+			<field reporter:label="Payment Type" name="payment_type" reporter:datatype="text"/>
+			<field reporter:label="Aged Circulation" name="xact" reporter:datatype="link"/>
+			<field reporter:label="Voided?" name="voided" reporter:datatype="bool"/>
+		</fields>
+		<links>
+			<!-- 
+				'xact' may link to a money.billabl_xact or to a money.aged_circulation.
+			 -->
+		</links>
+	</class>
+
 	<class id="mbp" controller="open-ils.cstore open-ils.pcrud" oils_obj:fieldmapper="money::bnm_payment" oils_persist:tablename="money.bnm_payment_view" reporter:core="true" reporter:label="Payments: Brick-and-mortar">
 		<fields oils_persist:primary="id" oils_persist:sequence="">
 			<field reporter:label="Amount" name="amount" reporter:datatype="money" />
@@ -8366,7 +8386,7 @@ SELECT  usr,
 		oils_obj:fieldmapper="money::aged_billing" 
 		oils_persist:tablename="money.aged_billing" 
 		reporter:label="Aged Billing Line Item">
-		<fields oils_persist:primary="id" oils_persist:sequence="">
+		<fields oils_persist:primary="id" oils_persist:sequence="money.billing_id_seq">
 			<field reporter:label="Amount" name="amount" reporter:datatype="money" />
 			<field reporter:label="Create Date" name="create_date" reporter:datatype="timestamp"/>
 			<field reporter:label="Billing Period Start" name="period_start" reporter:datatype="timestamp"/>
@@ -8394,6 +8414,33 @@ SELECT  usr,
 			</actions>
 		</permacrud>
 	</class>
+	<class id="mallb" controller="open-ils.cstore" 
+		oils_obj:fieldmapper="money::all_billings" 
+		oils_persist:tablename="money.all_billings" 
+		oils_persist="readonly" reporter:label="All Billing Line Items">
+		<fields oils_persist:primary="id" oils_persist:sequence="">
+			<field reporter:label="Amount" name="amount" reporter:datatype="money" />
+			<field reporter:label="Create Date" name="create_date" reporter:datatype="timestamp"/>
+			<field reporter:label="Billing Period Start" name="period_start" reporter:datatype="timestamp"/>
+			<field reporter:label="Billing Period End" name="period_end" reporter:datatype="timestamp"/>
+			<field reporter:label="Legacy Billing Timestamp" name="billing_ts" reporter:datatype="timestamp"/>
+			<field reporter:label="Legacy Billing Type" name="billing_type" reporter:datatype="text"/>
+			<field reporter:label="Billing ID" name="id" reporter:datatype="id" />
+			<field reporter:label="Note" name="note" reporter:datatype="text"/>
+			<field reporter:label="Void Timestamp" name="void_time" reporter:datatype="timestamp"/>
+			<field reporter:label="Voided?" name="voided" reporter:datatype="bool"/>
+			<field reporter:label="Voiding Staff Member" name="voider" reporter:datatype="link"/>
+			<field reporter:label="Transaction" name="xact" reporter:datatype="link"/>
+			<field reporter:label="Type" name="btype" reporter:datatype="link"/>
+		</fields>
+		<links>
+			<link field="voider" reltype="has_a" key="id" map="" class="au"/>
+			<link field="btype" reltype="has_a" key="id" map="" class="cbt"/>
+			<!-- 
+				'xact' may link to a money.billabl_xact or to a money.aged_circulation.
+			 -->
+		</links>
+	</class>
 	<class id="pugm" controller="open-ils.cstore" oils_obj:fieldmapper="permission::usr_grp_map" oils_persist:tablename="permission.usr_grp_map" reporter:label="User Group Map">
 		<fields oils_persist:primary="id" oils_persist:sequence="permission.usr_grp_map_id_seq">
 			<field name="grp" />
diff --git a/Open-ILS/src/sql/Pg/080.schema.money.sql b/Open-ILS/src/sql/Pg/080.schema.money.sql
index 85352ab2ab..7d30797b1e 100644
--- a/Open-ILS/src/sql/Pg/080.schema.money.sql
+++ b/Open-ILS/src/sql/Pg/080.schema.money.sql
@@ -691,7 +691,18 @@ CREATE OR REPLACE VIEW money.cashdrawer_payment_view AS
 -- Create 'aged' clones of billing and payment_view tables
 CREATE TABLE money.aged_payment (LIKE money.payment INCLUDING INDEXES);
 ALTER TABLE money.aged_payment ADD COLUMN payment_type TEXT NOT NULL;
+
 CREATE TABLE money.aged_billing (LIKE money.billing INCLUDING INDEXES);
 
+CREATE OR REPLACE VIEW money.all_payments AS
+    SELECT * FROM money.payment_view 
+    UNION ALL
+    SELECT * FROM money.aged_payment;
+
+CREATE OR REPLACE VIEW money.all_billings AS
+    SELECT * FROM money.billing
+    UNION ALL
+    SELECT * FROM money.aged_billing;
+
 COMMIT;
 
diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.aged-billing-payment.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.aged-billing-payment.sql
index f465411dc9..204e95df5c 100644
--- a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.aged-billing-payment.sql
+++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.aged-billing-payment.sql
@@ -7,6 +7,7 @@ BEGIN;
 
 CREATE TABLE money.aged_payment (LIKE money.payment INCLUDING INDEXES);
 ALTER TABLE money.aged_payment ADD COLUMN payment_type TEXT NOT NULL;
+
 CREATE TABLE money.aged_billing (LIKE money.billing INCLUDING INDEXES);
 
 INSERT INTO money.aged_payment 
@@ -27,6 +28,15 @@ DELETE FROM money.billing WHERE id IN (
     JOIN action.aged_circulation circ ON (circ.id = mb.xact)
 );
 
+CREATE OR REPLACE VIEW money.all_payments AS
+    SELECT * FROM money.payment_view 
+    UNION ALL
+    SELECT * FROM money.aged_payment;
+
+CREATE OR REPLACE VIEW money.all_billings AS
+    SELECT * FROM money.billing
+    UNION ALL
+    SELECT * FROM money.aged_billing;
 
 CREATE OR REPLACE FUNCTION action.age_circ_on_delete () RETURNS TRIGGER AS $$
 DECLARE
-- 
2.43.2