From 7b12ae81a56b1d998eb6c9f239ef259d3e23027a Mon Sep 17 00:00:00 2001
From: Bill Erickson <berick@esilibrary.com>
Date: Fri, 20 Jul 2012 10:25:39 -0400
Subject: [PATCH] ACQ lineitem summary moved to real DB view

This is allows us to create new IDL views based on the lineitem summary.

Signed-off-by: Bill Erickson <berick@esilibrary.com>
Signed-off-by: Lebbeous Fogle-Weekley <lebbeous@esilibrary.com>
---
 Open-ILS/examples/fm_IDL.xml                  | 72 +++++--------------
 Open-ILS/src/sql/Pg/200.schema.acq.sql        | 50 +++++++++++++
 .../XXXX.schema.acq-lineitem-summary.sql      | 53 ++++++++++++++
 3 files changed, 122 insertions(+), 53 deletions(-)
 create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.schema.acq-lineitem-summary.sql

diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml
index 243f918963..71706383d8 100644
--- a/Open-ILS/examples/fm_IDL.xml
+++ b/Open-ILS/examples/fm_IDL.xml
@@ -9654,58 +9654,26 @@ SELECT  usr,
 			<link field="claim_policy_action" reltype="has_a" key="id" map="" class="acqclpa"/>
 		</links>
 	</class>
-
-	<class id="acqlisum" controller="open-ils.cstore" oils_obj:fieldmapper="acq::lineitem_summary" oils_persist:readonly="true" reporter:label="Lineitem Summary">
+	<class id="acqlisum" controller="open-ils.cstore" oils_obj:fieldmapper="acq::lineitem_summary" oils_persist:readonly="true" reporter:label="Lineitem Summary" oils_persist:tablename="acq.lineitem_summary">
+		<fields oils_persist:primary="lineitem" oils_persist:sequence="acq.lineitem_id_seq">
+			<field reporter:label="Lineitem" name="lineitem" reporter:datatype="link"/>
+			<field reporter:label="Item Count" name="item_count" reporter:datatype="int"/>
+			<field reporter:label="Receive Count" name="recv_count" reporter:datatype="int"/>
+			<field reporter:label="Cancel Count" name="cancel_count" reporter:datatype="int"/>
+			<field reporter:label="Invoice Count" name="invoice_count" reporter:datatype="int"/>
+			<field reporter:label="Claim Count" name="claim_count" reporter:datatype="int"/>
+			<field reporter:label="Estimated Amount" name="estimated_amount" reporter:datatype="money"/>
+			<field reporter:label="Encumbrance Amount" name="encumbrance_amount" reporter:datatype="money"/>
+			<field reporter:label="Paid Amount" name="paid_amount" reporter:datatype="money"/>
+		</fields>
+		<links>
+			<link field="lineitem" reltype="has_a" key="id" map="" class="jub"/>
+		</links>
+    </class>
+	<class id="acqlisumi" controller="open-ils.cstore" oils_obj:fieldmapper="acq::lineitem_summary_invoiceable" oils_persist:readonly="true" reporter:label="Invoiceable Lineitem Summary">
 		<oils_persist:source_definition>
-
-            SELECT 
-                li.id AS lineitem, 
-                (
-                    SELECT COUNT(lid.id) 
-                    FROM acq.lineitem_detail lid
-                    WHERE lineitem = li.id
-                ) AS item_count,
-                (
-                    SELECT COUNT(lid.id) 
-                    FROM acq.lineitem_detail lid
-                    WHERE recv_time IS NOT NULL AND lineitem = li.id
-                ) AS recv_count,
-                (
-                    SELECT COUNT(lid.id) 
-                    FROM acq.lineitem_detail lid
-                    WHERE cancel_reason IS NOT NULL AND lineitem = li.id
-                ) AS cancel_count,
-                (
-                    SELECT COUNT(lid.id) 
-                    FROM acq.lineitem_detail lid
-                        JOIN acq.fund_debit debit ON (lid.fund_debit = debit.id)
-                    WHERE NOT debit.encumbrance AND lineitem = li.id
-                ) AS invoice_count,
-                (
-                    SELECT COUNT(DISTINCT(lid.id)) 
-                    FROM acq.lineitem_detail lid
-                        JOIN acq.claim claim ON (claim.lineitem_detail = lid.id)
-                    WHERE lineitem = li.id
-                ) AS claim_count,
-                (
-                    SELECT (COUNT(lid.id) * li.estimated_unit_price)::NUMERIC(8,2)
-                    FROM acq.lineitem_detail lid
-                    WHERE lid.cancel_reason IS NULL AND lineitem = li.id
-                ) AS estimated_amount,
-                (
-                    SELECT SUM(debit.amount)::NUMERIC(8,2)
-                    FROM acq.lineitem_detail lid
-                        JOIN acq.fund_debit debit ON (lid.fund_debit = debit.id)
-                    WHERE debit.encumbrance AND lineitem = li.id
-                ) AS encumbrance_amount,
-                (
-                    SELECT SUM(debit.amount)::NUMERIC(8,2)
-                    FROM acq.lineitem_detail lid
-                        JOIN acq.fund_debit debit ON (lid.fund_debit = debit.id)
-                    WHERE NOT debit.encumbrance AND lineitem = li.id
-                ) AS paid_amount
-
-                FROM acq.lineitem AS li
+            SELECT * FROM acq.lineitem_summary 
+				WHERE item_count > (invoice_count + cancel_count)
         </oils_persist:source_definition>
 		<fields oils_persist:primary="lineitem" oils_persist:sequence="acq.lineitem_id_seq">
 			<field reporter:label="Lineitem" name="lineitem" reporter:datatype="link"/>
@@ -9722,8 +9690,6 @@ SELECT  usr,
 			<link field="lineitem" reltype="has_a" key="id" map="" class="jub"/>
 		</links>
     </class>
-
-
 	<class id="iatc" controller="open-ils.reporter-store" oils_obj:fieldmapper="action::intersystem_transit_copy" oils_persist:readonly="true" reporter:core="true" reporter:label="Inter-system Copy Transit">
 		<oils_persist:source_definition>
 
diff --git a/Open-ILS/src/sql/Pg/200.schema.acq.sql b/Open-ILS/src/sql/Pg/200.schema.acq.sql
index c84279b3e6..aad60a4c2f 100644
--- a/Open-ILS/src/sql/Pg/200.schema.acq.sql
+++ b/Open-ILS/src/sql/Pg/200.schema.acq.sql
@@ -2418,4 +2418,54 @@ CREATE TABLE acq.serial_claim_event (
 
 CREATE INDEX serial_claim_event_claim_date_idx ON acq.serial_claim_event( claim, event_date );
 
+CREATE OR REPLACE VIEW acq.lineitem_summary AS
+    SELECT 
+        li.id AS lineitem, 
+        (
+            SELECT COUNT(lid.id) 
+            FROM acq.lineitem_detail lid
+            WHERE lineitem = li.id
+        ) AS item_count,
+        (
+            SELECT COUNT(lid.id) 
+            FROM acq.lineitem_detail lid
+            WHERE recv_time IS NOT NULL AND lineitem = li.id
+        ) AS recv_count,
+        (
+            SELECT COUNT(lid.id) 
+            FROM acq.lineitem_detail lid
+            WHERE cancel_reason IS NOT NULL AND lineitem = li.id
+        ) AS cancel_count,
+        (
+            SELECT COUNT(lid.id) 
+            FROM acq.lineitem_detail lid
+                JOIN acq.fund_debit debit ON (lid.fund_debit = debit.id)
+            WHERE NOT debit.encumbrance AND lineitem = li.id
+        ) AS invoice_count,
+        (
+            SELECT COUNT(DISTINCT(lid.id)) 
+            FROM acq.lineitem_detail lid
+                JOIN acq.claim claim ON (claim.lineitem_detail = lid.id)
+            WHERE lineitem = li.id
+        ) AS claim_count,
+        (
+            SELECT (COUNT(lid.id) * li.estimated_unit_price)::NUMERIC(8,2)
+            FROM acq.lineitem_detail lid
+            WHERE lid.cancel_reason IS NULL AND lineitem = li.id
+        ) AS estimated_amount,
+        (
+            SELECT SUM(debit.amount)::NUMERIC(8,2)
+            FROM acq.lineitem_detail lid
+                JOIN acq.fund_debit debit ON (lid.fund_debit = debit.id)
+            WHERE debit.encumbrance AND lineitem = li.id
+        ) AS encumbrance_amount,
+        (
+            SELECT SUM(debit.amount)::NUMERIC(8,2)
+            FROM acq.lineitem_detail lid
+                JOIN acq.fund_debit debit ON (lid.fund_debit = debit.id)
+            WHERE NOT debit.encumbrance AND lineitem = li.id
+        ) AS paid_amount
+
+        FROM acq.lineitem AS li;
+
 COMMIT;
diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.acq-lineitem-summary.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.acq-lineitem-summary.sql
new file mode 100644
index 0000000000..31b4c60da5
--- /dev/null
+++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.acq-lineitem-summary.sql
@@ -0,0 +1,53 @@
+BEGIN;
+
+CREATE OR REPLACE VIEW acq.lineitem_summary AS
+    SELECT 
+        li.id AS lineitem, 
+        (
+            SELECT COUNT(lid.id) 
+            FROM acq.lineitem_detail lid
+            WHERE lineitem = li.id
+        ) AS item_count,
+        (
+            SELECT COUNT(lid.id) 
+            FROM acq.lineitem_detail lid
+            WHERE recv_time IS NOT NULL AND lineitem = li.id
+        ) AS recv_count,
+        (
+            SELECT COUNT(lid.id) 
+            FROM acq.lineitem_detail lid
+            WHERE cancel_reason IS NOT NULL AND lineitem = li.id
+        ) AS cancel_count,
+        (
+            SELECT COUNT(lid.id) 
+            FROM acq.lineitem_detail lid
+                JOIN acq.fund_debit debit ON (lid.fund_debit = debit.id)
+            WHERE NOT debit.encumbrance AND lineitem = li.id
+        ) AS invoice_count,
+        (
+            SELECT COUNT(DISTINCT(lid.id)) 
+            FROM acq.lineitem_detail lid
+                JOIN acq.claim claim ON (claim.lineitem_detail = lid.id)
+            WHERE lineitem = li.id
+        ) AS claim_count,
+        (
+            SELECT (COUNT(lid.id) * li.estimated_unit_price)::NUMERIC(8,2)
+            FROM acq.lineitem_detail lid
+            WHERE lid.cancel_reason IS NULL AND lineitem = li.id
+        ) AS estimated_amount,
+        (
+            SELECT SUM(debit.amount)::NUMERIC(8,2)
+            FROM acq.lineitem_detail lid
+                JOIN acq.fund_debit debit ON (lid.fund_debit = debit.id)
+            WHERE debit.encumbrance AND lineitem = li.id
+        ) AS encumbrance_amount,
+        (
+            SELECT SUM(debit.amount)::NUMERIC(8,2)
+            FROM acq.lineitem_detail lid
+                JOIN acq.fund_debit debit ON (lid.fund_debit = debit.id)
+            WHERE NOT debit.encumbrance AND lineitem = li.id
+        ) AS paid_amount
+
+        FROM acq.lineitem AS li;
+
+COMMIT;
-- 
2.43.2