From 2c939c5cf92df59359009efcca1f98e5aa75cc43 Mon Sep 17 00:00:00 2001 From: Chris Sharp Date: Thu, 15 Dec 2016 14:20:36 -0500 Subject: [PATCH] Add Invoice Summary Reporting View. Before now, there have been no easy ways to gather acq receiving totals from reports. Here is an attempt to remedy that situation. Signed-off-by: Chris Sharp --- Open-ILS/examples/fm_IDL.xml | 26 ++++++++ Open-ILS/src/sql/Pg/example.reporter-extension.sql | 71 +++++++++++++++++++++ .../XXXX.schema.acq_invoice_reporter_view.sql | 74 ++++++++++++++++++++++ 3 files changed, 171 insertions(+) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.schema.acq_invoice_reporter_view.sql diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml index a4fb45b078..76d655ed95 100644 --- a/Open-ILS/examples/fm_IDL.xml +++ b/Open-ILS/examples/fm_IDL.xml @@ -11306,6 +11306,32 @@ 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 21c9fbca88..b77116e33f 100644 --- a/Open-ILS/src/sql/Pg/example.reporter-extension.sql +++ b/Open-ILS/src/sql/Pg/example.reporter-extension.sql @@ -315,6 +315,77 @@ CREATE OR REPLACE VIEW money.open_balance_by_usr_home_and_owning_lib AS SUM(billed) - SUM( COALESCE((SELECT SUM(amount) AS paid FROM money.payment WHERE NOT voided AND xact = x.id), 0::NUMERIC) ) AS balance FROM money.open_circ_balance_by_usr_home_and_owning_lib x GROUP BY 1,2; +CREATE OR REPLACE VIEW reporter.acq_invoice_total_cost_billed AS +WITH acq_cost_billed (invoice, total_cost_billed) AS + (SELECT acqie.invoice, + COALESCE(SUM(acqie.cost_billed), '0.00') AS total_cost_billed + FROM acq.invoice_entry acqie + GROUP BY acqie.invoice + UNION ALL + SELECT acqii.invoice, + COALESCE(SUM(acqii.cost_billed), '0.00') AS total_cost_billed + FROM acq.invoice_item acqii + GROUP BY acqii.invoice) + SELECT invoice, COALESCE(SUM(total_cost_billed), '0.00') AS total_cost_billed + FROM acq_cost_billed + GROUP BY invoice; + +CREATE OR REPLACE VIEW reporter.acq_invoice_total_actual_cost AS +WITH acq_actual_cost (invoice, total_actual_cost) AS + (SELECT acqie.invoice, + COALESCE(SUM(acqie.actual_cost), '0.00') AS total_actual_cost + FROM acq.invoice_entry acqie + GROUP BY acqie.invoice + UNION ALL + SELECT acqii.invoice, + COALESCE(SUM(acqii.actual_cost), '0.00') AS total_actual_cost + FROM acq.invoice_item acqii + GROUP BY acqii.invoice) + SELECT invoice, COALESCE(SUM(total_actual_cost), '0.00') AS total_actual_cost + FROM acq_actual_cost + GROUP BY invoice; + +CREATE OR REPLACE VIEW reporter.acq_invoice_total_amount_paid AS +WITH acq_amount_paid (invoice, total_amount_paid) AS + (SELECT acqie.invoice, + COALESCE(SUM(acqie.amount_paid), '0.00') AS total_amount_paid + FROM acq.invoice_entry acqie + GROUP BY acqie.invoice + UNION ALL + SELECT acqii.invoice, + COALESCE(SUM(acqii.amount_paid), '0.00') AS total_amount_paid + FROM acq.invoice_item acqii + GROUP BY acqii.invoice) + SELECT invoice, COALESCE(SUM(total_amount_paid), '0.00') AS total_amount_paid + FROM acq_amount_paid + GROUP BY invoice; + +CREATE OR REPLACE VIEW reporter.acq_invoice_summary_view AS + SELECT invoice.id AS inv_id, + invoice.inv_ident AS inv_vendor_ident, + invoice.recv_date, + provider.id AS provider_id, + provider.name AS provider_name, + provider.code AS provider_code, + shipper.id AS shipper_id, + shipper.name AS shipper_name, + shipper.code AS shipper_code, + receiver.id AS receiver_id, + receiver.shortname AS receiver_shortname, + receiver.name AS receiver_name, + invoice.payment_auth, + 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 + 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); + 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 new file mode 100644 index 0000000000..9aab645275 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.acq_invoice_reporter_view.sql @@ -0,0 +1,74 @@ +BEGIN; + +CREATE OR REPLACE VIEW reporter.acq_invoice_total_cost_billed AS +WITH acq_cost_billed (invoice, total_cost_billed) AS + (SELECT acqie.invoice, + COALESCE(SUM(acqie.cost_billed), '0.00') AS total_cost_billed + FROM acq.invoice_entry acqie + GROUP BY acqie.invoice + UNION ALL + SELECT acqii.invoice, + COALESCE(SUM(acqii.cost_billed), '0.00') AS total_cost_billed + FROM acq.invoice_item acqii + GROUP BY acqii.invoice) + SELECT invoice, COALESCE(SUM(total_cost_billed), '0.00') AS total_cost_billed + FROM acq_cost_billed + GROUP BY invoice; + +CREATE OR REPLACE VIEW reporter.acq_invoice_total_actual_cost AS +WITH acq_actual_cost (invoice, total_actual_cost) AS + (SELECT acqie.invoice, + COALESCE(SUM(acqie.actual_cost), '0.00') AS total_actual_cost + FROM acq.invoice_entry acqie + GROUP BY acqie.invoice + UNION ALL + SELECT acqii.invoice, + COALESCE(SUM(acqii.actual_cost), '0.00') AS total_actual_cost + FROM acq.invoice_item acqii + GROUP BY acqii.invoice) + SELECT invoice, COALESCE(SUM(total_actual_cost), '0.00') AS total_actual_cost + FROM acq_actual_cost + GROUP BY invoice; + +CREATE OR REPLACE VIEW reporter.acq_invoice_total_amount_paid AS +WITH acq_amount_paid (invoice, total_amount_paid) AS + (SELECT acqie.invoice, + COALESCE(SUM(acqie.amount_paid), '0.00') AS total_amount_paid + FROM acq.invoice_entry acqie + GROUP BY acqie.invoice + UNION ALL + SELECT acqii.invoice, + COALESCE(SUM(acqii.amount_paid), '0.00') AS total_amount_paid + FROM acq.invoice_item acqii + GROUP BY acqii.invoice) + SELECT invoice, COALESCE(SUM(total_amount_paid), '0.00') AS total_amount_paid + FROM acq_amount_paid + GROUP BY invoice; + +CREATE OR REPLACE VIEW reporter.acq_invoice_summary_view AS + SELECT invoice.id AS inv_id, + invoice.inv_ident AS inv_vendor_ident, + invoice.recv_date, + provider.id AS provider_id, + provider.name AS provider_name, + provider.code AS provider_code, + shipper.id AS shipper_id, + shipper.name AS shipper_name, + shipper.code AS shipper_code, + receiver.id AS receiver_id, + receiver.shortname AS receiver_shortname, + receiver.name AS receiver_name, + invoice.payment_auth, + 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 + 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); + +COMMIT; -- 2.11.0