From 579346a141fb623938c05f944245d515db8e30c8 Mon Sep 17 00:00:00 2001 From: Chris Sharp Date: Thu, 8 Sep 2016 09:02:25 -0400 Subject: [PATCH] LP#1630271 - Creating acq purchase order and invoice reports views. We currently lack several desired linkages in the reporter for acquisitions data. I'm creating views for POs and invoices to hopefully alleviate some of the pain around acq reports. Signed-off-by: Chris Sharp --- Open-ILS/examples/fm_IDL.xml | 112 ++++++++++++++++- Open-ILS/src/sql/Pg/example.reporter-extension.sql | 136 ++++++++++++++++++++- 2 files changed, 241 insertions(+), 7 deletions(-) diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml index 7126f16779..19f84cdc42 100644 --- a/Open-ILS/examples/fm_IDL.xml +++ b/Open-ILS/examples/fm_IDL.xml @@ -10859,7 +10859,117 @@ 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 5095aeecf6..8ac49ad7fc 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,6 +316,130 @@ 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; -COMMIT; +DROP VIEW IF EXISTS reporter.acq_purchase_order_view; + +CREATE 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.id AS provider_id, + provider.name AS provider_name, + provider.san AS provider_san, + 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, + 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.id AS purchasing_lib_id, + 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 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, + 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, + 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, + 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, + 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 + 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.code) + 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