From a89c60a84e095c5ea03c564c6dd47293d98b044f Mon Sep 17 00:00:00 2001
From: Bill Erickson <berick@esilibrary.com>
Date: Mon, 2 Jun 2014 16:54:01 -0400
Subject: [PATCH] LP#1270289 Split canceled into delayed vs canceled

The acq.lineitem_summary DB view (and related
acq::lineitem_summary_invoiceable IDL view) now differentitate between
canceled items and delayed items by inspecting the keep_debits flag on
the cancel reason.

Signed-off-by: Bill Erickson <berick@esilibrary.com>
Signed-off-by: Kathy Lussier <klussier@masslnc.org>
Signed-off-by: Ben Shum <bshum@biblio.org>
---
 Open-ILS/examples/fm_IDL.xml                  |  2 +
 Open-ILS/src/sql/Pg/200.schema.acq.sql        |  9 ++-
 .../XXXX.schema.acq-cancel-display.sql        | 63 +++++++++++++++++++
 3 files changed, 73 insertions(+), 1 deletion(-)
 create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.schema.acq-cancel-display.sql

diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml
index e574b29e77..2f9fc7f51d 100644
--- a/Open-ILS/examples/fm_IDL.xml
+++ b/Open-ILS/examples/fm_IDL.xml
@@ -10512,6 +10512,7 @@ SELECT  usr,
 			<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="Delay Count" name="delay_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"/>
@@ -10532,6 +10533,7 @@ SELECT  usr,
 			<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="Delay Count" name="delay_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"/>
diff --git a/Open-ILS/src/sql/Pg/200.schema.acq.sql b/Open-ILS/src/sql/Pg/200.schema.acq.sql
index 35be244e9f..abde889dc3 100644
--- a/Open-ILS/src/sql/Pg/200.schema.acq.sql
+++ b/Open-ILS/src/sql/Pg/200.schema.acq.sql
@@ -2452,8 +2452,15 @@ CREATE OR REPLACE VIEW acq.lineitem_summary AS
         (
             SELECT COUNT(lid.id) 
             FROM acq.lineitem_detail lid
-            WHERE cancel_reason IS NOT NULL AND lineitem = li.id
+                JOIN acq.cancel_reason acqcr ON (acqcr.id = lid.cancel_reason)
+            WHERE acqcr.keep_debits IS FALSE AND lineitem = li.id
         ) AS cancel_count,
+        (
+            SELECT COUNT(lid.id) 
+            FROM acq.lineitem_detail lid
+                JOIN acq.cancel_reason acqcr ON (acqcr.id = lid.cancel_reason)
+            WHERE acqcr.keep_debits IS TRUE AND lineitem = li.id
+        ) AS delay_count,
         (
             SELECT COUNT(lid.id) 
             FROM acq.lineitem_detail lid
diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.acq-cancel-display.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.acq-cancel-display.sql
new file mode 100644
index 0000000000..7509e1f0d8
--- /dev/null
+++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.acq-cancel-display.sql
@@ -0,0 +1,63 @@
+
+BEGIN;
+
+DROP VIEW acq.lineitem_summary;
+
+CREATE 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
+                JOIN acq.cancel_reason acqcr ON (acqcr.id = lid.cancel_reason)
+            WHERE acqcr.keep_debits IS FALSE AND lineitem = li.id
+        ) AS cancel_count,
+        (
+            SELECT COUNT(lid.id) 
+            FROM acq.lineitem_detail lid
+                JOIN acq.cancel_reason acqcr ON (acqcr.id = lid.cancel_reason)
+            WHERE acqcr.keep_debits IS TRUE AND lineitem = li.id
+        ) AS delay_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