From 119e7b963aa9e74381e1514e1d79ed1ddcd99116 Mon Sep 17 00:00:00 2001 From: Chris Sharp Date: Tue, 4 Oct 2016 08:44:48 -0400 Subject: [PATCH] Adding Invoice View and making corrections. Signed-off-by: Chris Sharp --- Open-ILS/examples/fm_IDL.xml | 71 +++++++++++++++++++++- Open-ILS/src/sql/Pg/example.reporter-extension.sql | 24 ++------ 2 files changed, 74 insertions(+), 21 deletions(-) diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml index 70a0094b63..cf4705d88d 100644 --- a/Open-ILS/examples/fm_IDL.xml +++ b/Open-ILS/examples/fm_IDL.xml @@ -10874,8 +10874,9 @@ SELECT usr, - + + @@ -10889,7 +10890,7 @@ SELECT usr, - + @@ -10899,12 +10900,76 @@ 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 584d89de6c..e343b0e095 100644 --- a/Open-ILS/src/sql/Pg/example.reporter-extension.sql +++ b/Open-ILS/src/sql/Pg/example.reporter-extension.sql @@ -337,6 +337,7 @@ CREATE VIEW reporter.acq_purchase_order_view AS WHERE acqlid.lineitem = li.id AND cancel_reason IS NULL) AS quantity_ordered, li.estimated_unit_price, + purchasing_lib.id AS purchasing_lib_id, purchasing_lib.name AS purchasing_lib_name, purchasing_lib.shortname AS purchasing_lib_shortname, fund.id AS fund_id, @@ -383,7 +384,8 @@ CREATE VIEW reporter.acq_invoice_view AS receiver.id AS receiver_id, receiver.name AS receiver_name, receiver.shortname AS receiver_shortname, - inv.recv_method AS recv_method, + recv_method.name AS recv_method_name, + recv_method.code AS recv_method_code, inv.inv_type AS inv_type, inv.payment_auth AS payment_auth, inv.payment_method AS payment_method, @@ -397,15 +399,12 @@ CREATE VIEW reporter.acq_invoice_view AS FROM acq.lineitem_detail acqlid WHERE acqlid.lineitem = li.id AND cancel_reason IS NULL) AS quantity_ordered, - -- this is still wrong :-/ - (SELECT sum(estimated_unit_price) - FROM acq.lineitem acqli - WHERE inv_entry.lineitem = acqli.id) AS lineitem_total, li.estimated_unit_price, inv_entry.billed_per_item, inv_entry.cost_billed, inv_entry.actual_cost, inv_entry.amount_paid, + purchasing_lib.id AS purchasing_lib_id, purchasing_lib.name AS purchasing_lib_name, purchasing_lib.shortname AS purchasing_lib_shortname, fund.id AS fund_id, @@ -423,17 +422,12 @@ CREATE VIEW reporter.acq_invoice_view AS inv_item_fund.name || ' (' || inv_item_fund.year || ')' AS inv_item_fund_name, inv_item_po.id AS inv_item_po_id, po_item.id AS po_item_id, - inv_item.target AS inv_item_target, - (SELECT sum(actual_cost) - FROM acq.invoice_item acqii - WHERE acqii.invoice = inv.id) AS inv_item_total, - (SELECT COUNT(*) - FROM acq.lineitem acqli - WHERE inv_entry.lineitem = acqli.id) AS lineitem_count + inv_item.target AS inv_item_target FROM acq.invoice inv INNER JOIN acq.provider provider ON (inv.provider = provider.id) INNER JOIN acq.provider shipper ON (inv.shipper = shipper.id) INNER JOIN actor.org_unit receiver ON (inv.receiver = receiver.id) + INNER JOIN acq.invoice_method recv_method ON (inv.recv_method = recv_method.id) LEFT OUTER JOIN acq.invoice_entry inv_entry ON (inv_entry.invoice = inv.id) LEFT OUTER JOIN acq.lineitem li ON (inv_entry.lineitem = li.id) LEFT OUTER JOIN acq.purchase_order po ON (li.purchase_order = po.id) @@ -448,10 +442,4 @@ CREATE VIEW reporter.acq_invoice_view AS LEFT OUTER JOIN acq.po_item po_item ON (inv_item.po_item = po_item.id) LEFT OUTER JOIN acq.purchase_order inv_item_po ON (inv_item.purchase_order = inv_item_po.id); - - - - COMMIT; - - -- 2.11.0