From f148305567b55ed8dd15922d57e18e943a4c6db8 Mon Sep 17 00:00:00 2001
From: Chris Sharp <csharp@georgialibraries.org>
Date: Thu, 8 Sep 2016 09:02:25 -0400
Subject: [PATCH] Creating acq purchase order reports view.

Signed-off-by: Chris Sharp <csharp@georgialibraries.org>
---
 Open-ILS/examples/fm_IDL.xml                       | 46 +++++++++++++++++
 Open-ILS/src/sql/Pg/example.reporter-extension.sql | 54 ++++++++++++++++++++
 .../XXXX.schema.acq_po_and_invoice_views.sql       | 57 ++++++++++++++++++++++
 3 files changed, 157 insertions(+)
 create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.schema.acq_po_and_invoice_views.sql

diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml
index 7126f16779..999559693f 100644
--- a/Open-ILS/examples/fm_IDL.xml
+++ b/Open-ILS/examples/fm_IDL.xml
@@ -10859,6 +10859,52 @@ SELECT  usr,
 		</permacrud>
 	</class>
 
+	<class id="racqpov" controller="open-ils.reporter-store" oils_obj:fieldmapper="reporter::acq_purchase_order_view" oils_persist:tablename="reporter.acq_purchase_order_view" reporter:core="false" reporter:label="Acquisitions: Purchase Order View">
+		<fields oils_persist:primary="po_id">
+			<field reporter:label="Purchase Order ID" name="po_id" reporter:datatype="id"/>
+			<field reporter:label="Purchase Order Name" name="po_name" reporter:datatype="text"/>
+			<field reporter:label="Purchase Order Date/Time" name="po_order_date" reporter:datatype="timestamp"/>
+			<field reporter:label="Provider Name" name="provider_name" reporter:datatype="text"/>
+			<field reporter:label="Provider Address Link" name="provider_address_id" reporter:datatype="link"/>
+			<field reporter:label="Ordering Agency Link" name="ordering_agency_id" reporter:datatype="link"/>
+			<field reporter:label="Ordering Agency Name" name="ordering_agency_name" reporter:datatype="text"/>
+			<field reporter:label="Ordering Agency Short (Policy) Name" name="ordering_agency_shortname" reporter:datatype="text"/> 
+			<field reporter:label="Ordering Agency Mailing Address Link" name="ordering_agency_mailing_address_id" reporter:datatype="link"/>
+			<field reporter:label="Ordering Agency Billing Address Link" name="ordering_agency_billing_address_id" reporter:datatype="link"/>
+			<field reporter:label="Lineitem ID" name="lineitem_id" reporter:datatype="id"/>
+			<field reporter:label="Lineitem Identifier" name="lineitem_identifier" reporter:datatype="text"/>
+			<field reporter:label="Lineitem Title" name="lineitem_title" reporter:datatype="text"/>
+			<field reporter:label="Quantity Ordered" name="quantity_ordered" reporter:datatype="int"/>
+			<field reporter:label="Estimated Unit Price" name="estimated_unit_price" reporter:datatype="money"/>
+			<field reporter:label="Purchasing Library Name" name="purchasing_lib_name" reporter:datatype="text"/>
+			<field reporter:label="Purchasing Library Short (Policy) Name" name="purchasing_lib_shortname" reporter:datatype="text"/>
+			<field reporter:label="Fund Link" name="fund_id" reporter:datatype="link"/>
+			<field reporter:label="Fund Name (Year)" name="fund_name" reporter:datatype="text"/>
+			<field reporter:label="Purchase Order Item Type Code" name="po_item_type_code" reporter:datatype="text"/>
+			<field reporter:label="Purchase Order Item Type Name" name="po_item_type_name" reporter:datatype="text"/>
+			<field reporter:label="Purchase Order Item Link" name="po_item_id" reporter:datatype="link"/>
+			<field reporter:label="Purchase Order Item Title" name="po_item_title" reporter:datatype="text"/>
+			<field reporter:label="Purchase Order Item Author" name="po_item_author" reporter:datatype="text"/>
+			<field reporter:label="Purchase Order Item Note" name="po_item_note" reporter:datatype="text"/>
+			<field reporter:label="Purchase Order Item Estimated Cost" name="po_item_estimated_cost" reporter:datatype="money"/>
+			<field reporter:label="Purchase Order Item Fund Link" name="po_item_fund_id" reporter:datatype="link"/>
+			<field reporter:label="Purchase Order Item Fund Name (Year)" name="po_item_fund_name" reporter:datatype="text"/>
+			<field reporter:label="Line Item Total" name="lineitem_total_price" reporter:datatype="money"/>
+			<field reporter:label="Purchase Order Item Total" name="po_item_total_price" reporter:datatype="money"/>
+		</fields>
+		<links>
+			<link field="po_id" reltype="has_a" key="id" map="" class="acqpo"/>
+			<link field="provider_address_id" reltype="has_a" key="id" map="" class="acqpa"/>
+			<link field="ordering_agency_id" reltype="has_a" key="id" map="" class="aou"/>
+			<link field="ordering_agency_mailing_address" reltype="might_have" key="id" map="" class="aoa"/>
+			<link field="ordering_agency_billing_address" reltype="might_have" key="id" map="" class="aoa"/>
+			<link field="lineitem_id" reltype="might_have" key="id" map="" class="jub"/>
+			<link field="fund_id" reltype="has_a" key="id" map="" class="acqf"/>
+			<link field="po_item_id" reltype="might_have" key="id" map="" class="acqpoi"/>
+			<link field="po_item_fund_id" reltype="might_have" key="id" map="" class="acqf"/>
+		</links>
+	</class>	
+
 
 	<!-- ********************************************************************************************************************* -->
 	<!-- What follows is a set of example extensions that are useful for PINES.  Comment out or remove if you don't want them. -->
diff --git a/Open-ILS/src/sql/Pg/example.reporter-extension.sql b/Open-ILS/src/sql/Pg/example.reporter-extension.sql
index 5095aeecf6..949427f4cf 100644
--- a/Open-ILS/src/sql/Pg/example.reporter-extension.sql
+++ b/Open-ILS/src/sql/Pg/example.reporter-extension.sql
@@ -316,6 +316,60 @@ CREATE OR REPLACE VIEW money.open_balance_by_usr_home_and_owning_lib AS
 	  FROM	money.open_circ_balance_by_usr_home_and_owning_lib x
 	  GROUP BY 1,2;
 
+CREATE OR REPLACE VIEW reporter.acq_purchase_order_view AS
+ SELECT  po.id AS po_id,
+        po.name AS po_name,
+        po.order_date AS po_order_date,
+        provider.name AS provider_name,
+        provider_address.id AS provider_address_id,
+        ord_agency.id AS ordering_agency_id,
+        ord_agency.name AS ordering_agency_name,
+        ord_agency.shortname AS ordering_agency_shortname,
+        oa_mailing_address.id AS ordering_agency_mailing_address_id,
+        oa_billing_address.id AS orderind_agency_billing_address_id,
+        li.id AS lineitem_id,
+        li_identifier.attr_value AS lineitem_identifier,
+        li_title.attr_value AS lineitem_title,
+        (SELECT count(*)
+                FROM acq.lineitem_detail acqlid
+                WHERE acqlid.lineitem = li.id
+                AND cancel_reason IS NULL) AS quantity_ordered,
+        li.estimated_unit_price,
+        purchasing_lib.name AS purchasing_lib_name,
+        purchasing_lib.shortname AS purchasing_lib_shortname,
+        fund.id AS fund_id,
+        fund.name || ' (' || fund.year || ')' AS fund_name,
+        inv_item_type.code AS po_item_type_code,
+        inv_item_type.name AS po_item_type_name,
+        po_item.id AS po_item_id,
+        po_item.title AS po_item_title,
+        po_item.author AS po_item_author,
+        po_item.note AS po_item_note,
+        po_item.estimated_cost AS po_item_estimated_cost,
+        po_item_fund.id AS po_item_fund_id,
+        po_item_fund.name || ' (' || po_item_fund.year || ')' AS po_item_fund_name,
+        (SELECT sum(estimated_unit_price)
+                FROM acq.lineitem acqli
+                WHERE acqli.purchase_order = po.id) AS lineitem_total_price,
+        (SELECT sum(estimated_cost)
+                FROM acq.po_item acqpoi
+                WHERE acqpoi.purchase_order = po.id) AS po_item_total_price
+FROM    acq.purchase_order po
+        inner join acq.provider provider ON (po.provider = provider.id)
+        left outer join acq.provider_address provider_address ON (provider_address.provider = provider.id)
+        inner join actor.org_unit ord_agency ON (po.ordering_agency = ord_agency.id)
+        left outer join actor.org_address oa_mailing_address ON (ord_agency.mailing_address = oa_mailing_address.id)
+        left outer join actor.org_address oa_billing_address ON (ord_agency.billing_address = oa_billing_address.id)
+        left outer join acq.lineitem li ON (li.purchase_order = po.id)
+        left outer join acq.lineitem_attr li_identifier ON (li_identifier.lineitem = li.id AND li_identifier.attr_name = 'identifier')
+        left outer join acq.lineitem_attr li_title ON (li_title.lineitem = li.id AND li_title.attr_name = 'title')
+        left outer join acq.lineitem_detail lid ON (lid.lineitem = li.id)
+        left outer join actor.org_unit purchasing_lib ON (lid.owning_lib = purchasing_lib.id)
+        left outer join acq.fund fund ON (lid.fund = fund.id)
+        left outer join acq.po_item po_item ON (po_item.purchase_order = po.id)
+        left outer join acq.invoice_item_type inv_item_type ON (po_item.inv_item_type = inv_item_type.code)
+        left outer join acq.fund po_item_fund ON (po_item.fund = po_item_fund.id);
+
 COMMIT;
 
 
diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.acq_po_and_invoice_views.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.acq_po_and_invoice_views.sql
new file mode 100644
index 0000000000..19cd830b1a
--- /dev/null
+++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.acq_po_and_invoice_views.sql
@@ -0,0 +1,57 @@
+BEGIN;
+
+CREATE OR REPLACE VIEW reporter.acq_purchase_order_view AS
+ SELECT  po.id AS po_id,
+        po.name AS po_name,
+        po.order_date AS po_order_date,
+        provider.name AS provider_name,
+        provider_address.id AS provider_address_id,
+        ord_agency.id AS ordering_agency_id,
+        ord_agency.name AS ordering_agency_name,
+        ord_agency.shortname AS ordering_agency_shortname,
+        oa_mailing_address.id AS ordering_agency_mailing_address_id,
+        oa_billing_address.id AS orderind_agency_billing_address_id,
+        li.id AS lineitem_id,
+        li_identifier.attr_value AS lineitem_identifier,
+        li_title.attr_value AS lineitem_title,
+        (SELECT count(*)
+                FROM acq.lineitem_detail acqlid
+                WHERE acqlid.lineitem = li.id
+                AND cancel_reason IS NULL) AS quantity_ordered,
+        li.estimated_unit_price,
+        purchasing_lib.name AS purchasing_lib_name,
+        purchasing_lib.shortname AS purchasing_lib_shortname,
+		fund.id AS fund_id,
+        fund.name || ' (' || fund.year || ')' AS fund_name,
+        inv_item_type.code AS po_item_type_code,
+        inv_item_type.name AS po_item_type_name,
+        po_item.id AS po_item_id,
+        po_item.title AS po_item_title,
+        po_item.author AS po_item_author,
+        po_item.note AS po_item_note,
+        po_item.estimated_cost AS po_item_estimated_cost,
+		po_item_fund.id AS po_item_fund_id,
+        po_item_fund.name || ' (' || po_item_fund.year || ')' AS po_item_fund_name,
+        (SELECT sum(estimated_unit_price)
+                FROM acq.lineitem acqli
+                WHERE acqli.purchase_order = po.id) AS lineitem_total_price,
+        (SELECT sum(estimated_cost)
+                FROM acq.po_item acqpoi
+                WHERE acqpoi.purchase_order = po.id) AS po_item_total_price
+FROM    acq.purchase_order po
+        inner join acq.provider provider ON (po.provider = provider.id)
+        left outer join acq.provider_address provider_address ON (provider_address.provider = provider.id)
+        inner join actor.org_unit ord_agency ON (po.ordering_agency = ord_agency.id)
+        left outer join actor.org_address oa_mailing_address ON (ord_agency.mailing_address = oa_mailing_address.id)
+        left outer join actor.org_address oa_billing_address ON (ord_agency.billing_address = oa_billing_address.id)
+        left outer join acq.lineitem li ON (li.purchase_order = po.id)
+        left outer join acq.lineitem_attr li_identifier ON (li_identifier.lineitem = li.id AND li_identifier.attr_name = 'identifier')
+        left outer join acq.lineitem_attr li_title ON (li_title.lineitem = li.id AND li_title.attr_name = 'title')
+        left outer join acq.lineitem_detail lid ON (lid.lineitem = li.id)
+        left outer join actor.org_unit purchasing_lib ON (lid.owning_lib = purchasing_lib.id)
+        left outer join acq.fund fund ON (lid.fund = fund.id)
+        left outer join acq.po_item po_item ON (po_item.purchase_order = po.id)
+        left outer join acq.invoice_item_type inv_item_type ON (po_item.inv_item_type = inv_item_type.code)
+        left outer join acq.fund po_item_fund ON (po_item.fund = po_item_fund.id);
+
+COMMIT;
-- 
2.11.0