From 1604227d20c46888d048edbbd7f35c968cc0ec9c Mon Sep 17 00:00:00 2001 From: Chris Sharp Date: Tue, 20 Jun 2017 09:05:07 -0400 Subject: [PATCH] LP#1630271 - Add Purchase Order Summary View The purchase order reporting source contains several fields that are available in the reporter but are non-functional (because they are generated on-the-fly within user interfaces). This view allows a similar calculation and provides access back to the PO object as well. Signed-off-by: Chris Sharp --- Open-ILS/examples/fm_IDL.xml | 34 +++++- Open-ILS/src/sql/Pg/example.reporter-extension.sql | 120 ++++++++++++++++++++- .../XXXX.schema.acq_invoice_reporter_view.sql | 111 +++++++++++++++++++ 3 files changed, 261 insertions(+), 4 deletions(-) diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml index 76d655ed95..dd6c525fa9 100644 --- a/Open-ILS/examples/fm_IDL.xml +++ b/Open-ILS/examples/fm_IDL.xml @@ -11310,7 +11310,7 @@ SELECT usr, - + @@ -11325,11 +11325,43 @@ SELECT usr, + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + diff --git a/Open-ILS/src/sql/Pg/example.reporter-extension.sql b/Open-ILS/src/sql/Pg/example.reporter-extension.sql index b77116e33f..15fe4c5a7c 100644 --- a/Open-ILS/src/sql/Pg/example.reporter-extension.sql +++ b/Open-ILS/src/sql/Pg/example.reporter-extension.sql @@ -1,5 +1,5 @@ /* - * Copyright (C) 2004-2008 Georgia Public Library Service + * Copyright (C) 2004-2017 Georgia Public Library Service * Copyright (C) 2007-2008 Equinox Software, Inc. * Mike Rylander * @@ -377,15 +377,129 @@ CREATE OR REPLACE VIEW reporter.acq_invoice_summary_view AS invoice.payment_method, raitcb.total_cost_billed AS total_cost_billed, raitac.total_actual_cost AS total_actual_cost, - raitap.total_amount_paid AS total_amount_paid + raitap.total_amount_paid AS total_amount_paid, + invoice.close_date, + invoice.closed_by, + closer.usrname AS closed_by_usrname FROM acq.invoice invoice JOIN reporter.acq_invoice_total_cost_billed raitcb ON (raitcb.invoice = invoice.id) JOIN reporter.acq_invoice_total_actual_cost raitac ON (raitac.invoice = invoice.id) JOIN reporter.acq_invoice_total_amount_paid raitap ON (raitap.invoice = invoice.id) JOIN acq.provider provider ON (invoice.provider = provider.id) LEFT OUTER JOIN acq.provider shipper ON (invoice.shipper = shipper.id) - LEFT OUTER JOIN actor.org_unit receiver ON (invoice.receiver = receiver.id); + LEFT OUTER JOIN actor.org_unit receiver ON (invoice.receiver = receiver.id) + LEFT OUTER JOIN actor.usr closer ON (invoice.closed_by = closer.id); +CREATE VIEW reporter.acq_purchase_order_summary_view AS +SELECT po.id, + po.owner, + po.creator, + po.editor, + po.ordering_agency, + po.create_time, + po.edit_time, + po.provider, + po.state, + po.order_date, + po.name, + po.cancel_reason, + po.prepayment_required, + -- lineitem_count + (SELECT COALESCE(COUNT(*), 0) + FROM acq.lineitem li + WHERE li.purchase_order = po.id) AS lineitem_count, + -- amount_encumbered + (SELECT COALESCE(SUM(amount), '0.00') + FROM acq.fund_debit afd + WHERE afd.encumbrance = TRUE + AND afd.id IN ( + SELECT fund_debit + FROM acq.lineitem_detail lid + WHERE lineitem IN ( + SELECT id + FROM acq.lineitem + WHERE purchase_order = po.id) + ) OR afd.id IN ( + SELECT fund_debit + FROM acq.po_item + WHERE purchase_order = po.id + ) OR afd.id IN ( + SELECT fund_debit + FROM acq.invoice_item + WHERE purchase_order = po.id + )) AS amount_encumbered, + -- amount_spent + (SELECT COALESCE(SUM(amount), '0.00') + FROM acq.fund_debit afd + WHERE afd.encumbrance = FALSE + AND afd.id IN ( + SELECT fund_debit + FROM acq.lineitem_detail lid + WHERE lineitem IN ( + SELECT id + FROM acq.lineitem + WHERE purchase_order = po.id) + ) OR afd.id IN ( + SELECT fund_debit + FROM acq.po_item + WHERE purchase_order = po.id + ) OR afd.id IN ( + SELECT fund_debit + FROM acq.invoice_item + WHERE purchase_order = po.id + )) AS amount_spent, + -- amount_estimated + CASE + WHEN ( + SELECT id + FROM acq.fund_debit + WHERE id IN ( + SELECT fund_debit + FROM acq.lineitem_detail + WHERE lineitem IN ( + SELECT id + FROM acq.lineitem + WHERE purchase_order = po.id + ) + ) OR id IN ( + SELECT fund_debit + FROM acq.po_item + WHERE purchase_order = po.id + ) OR id IN ( + SELECT fund_debit + FROM acq.invoice_item + WHERE purchase_order = po.id + ) limit 1 + ) IS NOT NULL THEN ( + SELECT COALESCE(SUM(amount), '0.00') + FROM acq.fund_debit + WHERE id IN ( + SELECT fund_debit + FROM acq.lineitem_detail + WHERE lineitem IN ( + SELECT id + FROM acq.lineitem + WHERE purchase_order = po.id + ) + ) OR id IN ( + SELECT fund_debit + FROM acq.po_item + WHERE purchase_order = po.id + ) OR id IN ( + SELECT fund_debit + FROM acq.invoice_item + WHERE purchase_order = po.id + ) + ) ELSE ( + SELECT ( + COALESCE(SUM(li.estimated_unit_price), '0.00') + + COALESCE(SUM(poi.estimated_cost), '0.00') + ) FROM acq.lineitem li + LEFT OUTER JOIN acq.lineitem_detail lid ON (li.id = lid.lineitem) + LEFT OUTER JOIN acq.po_item poi ON (poi.purchase_order = po.id) + WHERE li.purchase_order = po.id + ) END AS amount_estimated +FROM acq.purchase_order po; CREATE OR REPLACE VIEW reporter.copy_statistics_view AS SELECT acp.id as copy_id, diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.acq_invoice_reporter_view.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.acq_invoice_reporter_view.sql index 9aab645275..37b8223ba2 100644 --- a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.acq_invoice_reporter_view.sql +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.acq_invoice_reporter_view.sql @@ -71,4 +71,115 @@ CREATE OR REPLACE VIEW reporter.acq_invoice_summary_view AS LEFT OUTER JOIN acq.provider shipper ON (invoice.shipper = shipper.id) LEFT OUTER JOIN actor.org_unit receiver ON (invoice.receiver = receiver.id); +CREATE VIEW reporter.acq_purchase_order_summary_view AS +SELECT po.id, + po.owner, + po.creator, + po.editor, + po.ordering_agency, + po.create_time, + po.edit_time, + po.provider, + po.state, + po.order_date, + po.name, + po.cancel_reason, + po.prepayment_required, + -- lineitem_count + (SELECT COALESCE(COUNT(*), 0) + FROM acq.lineitem li + WHERE li.purchase_order = po.id) AS lineitem_count, + -- amount_encumbered + (SELECT COALESCE(SUM(amount), '0.00') + FROM acq.fund_debit afd + WHERE afd.encumbrance = TRUE + AND (afd.id IN ( + SELECT fund_debit + FROM acq.lineitem_detail lid + WHERE lineitem IN ( + SELECT id + FROM acq.lineitem + WHERE purchase_order = po.id) + ) OR afd.id IN ( + SELECT fund_debit + FROM acq.po_item + WHERE purchase_order = po.id + ) OR afd.id IN ( + SELECT fund_debit + FROM acq.invoice_item + WHERE purchase_order = po.id + ))) AS amount_encumbered, + -- amount_spent + (SELECT COALESCE(SUM(amount), '0.00') + FROM acq.fund_debit afd + WHERE afd.encumbrance = FALSE + AND afd.id IN ( + SELECT fund_debit + FROM acq.lineitem_detail lid + WHERE lineitem IN ( + SELECT id + FROM acq.lineitem + WHERE purchase_order = po.id) + ) OR afd.id IN ( + SELECT fund_debit + FROM acq.po_item + WHERE purchase_order = po.id + ) OR afd.id IN ( + SELECT fund_debit + FROM acq.invoice_item + WHERE purchase_order = po.id + )) AS amount_spent, + -- amount_estimated + CASE + WHEN ( + SELECT id + FROM acq.fund_debit + WHERE id IN ( + SELECT fund_debit + FROM acq.lineitem_detail + WHERE lineitem IN ( + SELECT id + FROM acq.lineitem + WHERE purchase_order = po.id + ) + ) OR id IN ( + SELECT fund_debit + FROM acq.po_item + WHERE purchase_order = po.id + ) OR id IN ( + SELECT fund_debit + FROM acq.invoice_item + WHERE purchase_order = po.id + ) limit 1 + ) IS NOT NULL THEN ( + SELECT COALESCE(SUM(amount), '0.00') + FROM acq.fund_debit + WHERE id IN ( + SELECT fund_debit + FROM acq.lineitem_detail + WHERE lineitem IN ( + SELECT id + FROM acq.lineitem + WHERE purchase_order = po.id + ) + ) OR id IN ( + SELECT fund_debit + FROM acq.po_item + WHERE purchase_order = po.id + ) OR id IN ( + SELECT fund_debit + FROM acq.invoice_item + WHERE purchase_order = po.id + ) + ) ELSE ( + SELECT ( + COALESCE(SUM(li.estimated_unit_price), '0.00') + + COALESCE(SUM(poi.estimated_cost), '0.00') + ) FROM acq.lineitem li + LEFT OUTER JOIN acq.lineitem_detail lid ON (li.id = lid.lineitem) + LEFT OUTER JOIN acq.po_item poi ON (poi.purchase_order = po.id) + WHERE li.purchase_order = po.id + ) END AS amount_estimated +FROM acq.purchase_order po; + COMMIT; -- 2.11.0