From 06d3df85c9342abeefc6231a2ed0f3fbb3beb615 Mon Sep 17 00:00:00 2001 From: Chris Sharp Date: Wed, 28 Sep 2016 12:54:59 -0400 Subject: [PATCH] First attempt adding Invoice data - needs work! Signed-off-by: Chris Sharp --- Open-ILS/src/sql/Pg/example.reporter-extension.sql | 125 ++++++++++++++++++--- 1 file changed, 107 insertions(+), 18 deletions(-) diff --git a/Open-ILS/src/sql/Pg/example.reporter-extension.sql b/Open-ILS/src/sql/Pg/example.reporter-extension.sql index 53359e25cf..3a77ea72f4 100644 --- a/Open-ILS/src/sql/Pg/example.reporter-extension.sql +++ b/Open-ILS/src/sql/Pg/example.reporter-extension.sql @@ -5,7 +5,7 @@ * * This program is free software; you can redistribute it and/or * modify it under the terms of the GNU General Public License - * as published by the Free Software Foundation; either version 2 + * AS published by the Free Software Foundation; either version 2 * of the License, or (at your option) any later version. * * This program is distributed in the hope that it will be useful, @@ -178,7 +178,7 @@ SELECT rmsr.title, cp.id, cp.price, cp.barcode, - cn.label as call_number_label, + cn.label AS call_number_label, CASE WHEN call_number_dewey(cn.label) ~ E'^[0-9.]+$' THEN @@ -199,7 +199,7 @@ SELECT rmsr.title, ) ELSE NULL END AS dewey_block_hundreds, - erfcc.circ_count as use_count, + erfcc.circ_count AS use_count, cp.circ_modifier, sl.name AS shelving_location, sc1.stat_cat_entry AS stat_cat_1, @@ -222,8 +222,8 @@ SELECT rmsr.title, cp.status, circ.stop_fines, circ.due_date, - circ_card.barcode as patron_barcode, - circ_u.first_given_name || ' ' || circ_u.family_name as patron_name + circ_card.barcode AS patron_barcode, + circ_u.first_given_name || ' ' || circ_u.family_name AS patron_name FROM asset.copy cp JOIN extend_reporter.full_circ_count erfcc ON (cp.id = erfcc.id) JOIN asset.copy_location sl ON (cp.location = sl.id) @@ -316,7 +316,7 @@ 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; -DROP VIEW IF EXISTS reporter.acq.purchase_order_view; +DROP VIEW IF EXISTS reporter.acq_purchase_order_view; CREATE VIEW reporter.acq_purchase_order_view AS SELECT po.id AS po_id, @@ -357,18 +357,107 @@ CREATE VIEW reporter.acq_purchase_order_view AS 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 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); + 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 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); + +DROP VIEW IF EXISTS reporter.acq_invoice_view; + +CREATE VIEW reporter.acq_invoice_view AS + SELECT inv.id AS inv_id, + inv.inv_ident AS inv_ident, + provider.id AS provider_id, + provider.name AS provider_name, + shipper.id AS shipper_id, + shipper.name AS shipper_name, + inv.recv_date AS recv_date, + receiver.id AS receiver_id, + receiver.name AS receiver_name, + receiver.shortname AS receiver_shortname, + inv.recv_method AS recv_method, + inv.inv_type AS inv_type, + inv.payment_auth AS payment_auth, + inv.payment_method AS payment_method, + inv.note AS inv_note, + inv.complete AS inv_complete, + li.id AS li_id, + po.id AS po_id, + li_identifier.attr_value AS li_identifier, + li_title.attr_value AS li_title, + (SELECT count(*) + FROM acq.lineitem_detail acqlid + WHERE acqlid.lineitem = li.id + AND cancel_reason IS NULL) AS quantity_ordered, + -- this total is from the PO, not the invoice + (SELECT sum(estimated_unit_price) + FROM acq.lineitem acqli + WHERE acqli.purchase_order = po.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.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.id AS inv_item_id, + inv_item.title AS inv_item_title, + inv_item.author AS inv_item_author, + inv_item.note AS inv_item_note, + inv_item.cost_billed AS inv_item_cost_billed, + inv_item.actual_cost AS inv_item_actual_cost, + inv_item.amount_paid AS inv_item_amount_paid, + inv_item_type.code AS inv_item_type_code, + inv_item_type.name AS inv_item_type_name, + inv_item_fund.id AS inv_item_fund_id, + 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, + -- this is not working + (SELECT sum(estimated_unit_price) + FROM acq.lineitem acqli + WHERE acqli.purchase_order = po.id) + + (SELECT sum(actual_cost) + FROM acq.invoice_item acqii + WHERE acqii.invoice = inv.id) AS invoice_total + 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) + 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) + 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_identifier.lineitem = li.id AND li_identifier.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.invoice_item inv_item ON (inv_item.invoice = inv.id) + LEFT OUTER JOIN acq.invoice_item_type inv_item_type ON (inv_item.inv_item_type = inv_item_type.code) + LEFT OUTER JOIN acq.fund inv_item_fund ON (inv_item.fund = inv_item_fund.id) + 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