From 94dbfe0b4118406897f8db6ac62725974b0775e6 Mon Sep 17 00:00:00 2001 From: Bill Erickson Date: Wed, 14 Nov 2018 11:51:43 -0500 Subject: [PATCH] JBAS-2101 Improve refundable xact summary view speed Signed-off-by: Bill Erickson --- .../deploy/refundable-xact-summary-view-speed.sql | 73 ++++++++++++++++++++++ .../revert/refundable-xact-summary-view-speed.sql | 57 +++++++++++++++++ KCLS/sql/schema/sqitch.plan | 1 + 3 files changed, 131 insertions(+) create mode 100644 KCLS/sql/schema/deploy/refundable-xact-summary-view-speed.sql create mode 100644 KCLS/sql/schema/revert/refundable-xact-summary-view-speed.sql diff --git a/KCLS/sql/schema/deploy/refundable-xact-summary-view-speed.sql b/KCLS/sql/schema/deploy/refundable-xact-summary-view-speed.sql new file mode 100644 index 0000000000..a1adab27de --- /dev/null +++ b/KCLS/sql/schema/deploy/refundable-xact-summary-view-speed.sql @@ -0,0 +1,73 @@ +-- Deploy kcls-evergreen:refundable-xact-summary-view-speed to pg +-- requires: aged-money-purge + +BEGIN; + +CREATE OR REPLACE VIEW money.refundable_xact_summary AS + SELECT + xact.*, + acp.id AS copy, + acp.barcode AS copy_barcode, + acn.label AS call_number, + CASE WHEN acn.id = -1 + THEN acp.dummy_title + ELSE rsr.title + END AS title, + circ.usr AS usr, -- may be null + circ.xact_start AS xact_start, + circ.xact_finish AS xact_finish, + summary.total_owed AS total_owed, + summary.balance_owed AS balance_owed, + SUM(positive_payments.amount)::NUMERIC(8,2) AS refundable_paid, + SUM(total_paid.amount)::NUMERIC(8,2) AS total_paid, + COALESCE(-SUM(refund_payments.amount), 0)::NUMERIC(8,2) AS total_refunded, + COUNT(mrp.id) AS num_refundable_payments + FROM money.refundable_xact xact + JOIN action.circulation circ ON (circ.id = xact.xact) + JOIN asset.copy acp ON (acp.id = circ.target_copy) + JOIN asset.call_number acn ON (acn.id = acp.call_number) + JOIN reporter.materialized_simple_record rsr ON (rsr.id = acn.record) + JOIN money.materialized_billable_xact_summary summary + ON (summary.id = xact.xact) + JOIN money.payment total_paid + ON (total_paid.xact = xact.xact AND amount > 0) + JOIN money.refundable_payment mrp + ON (mrp.refundable_xact = xact.id) + JOIN money.payment positive_payments + ON (positive_payments.id = mrp.payment) + LEFT JOIN money.cash_payment refund_payments + ON (refund_payments.xact = xact.xact AND refund_payments.amount < 0) + GROUP BY + xact.id, + xact.xact, + xact.action_date, + xact.action_by, + xact.item_price, + xact.refund_amount, + xact.rejected, + xact.notes, + xact.usr_first_name, + xact.usr_middle_name, + xact.usr_family_name, + xact.usr_barcode, + xact.usr_street1, + xact.usr_street2, + xact.usr_city, + xact.usr_state, + xact.usr_post_code, + acp.id, + acp.barcode, + acn.label, + CASE WHEN acn.id = -1 + THEN acp.dummy_title + ELSE rsr.title + END, + circ.usr, + circ.xact_start, + circ.xact_finish, + summary.total_owed, + summary.balance_owed + ; + +COMMIT; + diff --git a/KCLS/sql/schema/revert/refundable-xact-summary-view-speed.sql b/KCLS/sql/schema/revert/refundable-xact-summary-view-speed.sql new file mode 100644 index 0000000000..cbbe617248 --- /dev/null +++ b/KCLS/sql/schema/revert/refundable-xact-summary-view-speed.sql @@ -0,0 +1,57 @@ +-- Revert kcls-evergreen:refundable-xact-summary-view-speed from pg + +BEGIN; + +CREATE OR REPLACE VIEW money.refundable_xact_summary AS + SELECT + xact.*, + acp.id AS copy, + acp.barcode AS copy_barcode, + acn.label AS call_number, + CASE WHEN acn.id = -1 + THEN acp.dummy_title + ELSE rsr.title + END AS title, + circ.usr AS usr, -- may be null + circ.xact_start AS xact_start, + circ.xact_finish AS xact_finish, + summary.total_owed AS total_owed, + summary.balance_owed AS balance_owed, + refundable_paid.amount::NUMERIC(8,2) AS refundable_paid, + total_paid.amount::NUMERIC(8,2) AS total_paid, + total_refunded.amount::NUMERIC(8,2) AS total_refunded, + refundable_payment_count.count AS num_refundable_payments + FROM money.refundable_xact xact + JOIN action.circulation circ ON (circ.id = xact.xact) + JOIN asset.copy acp ON (acp.id = circ.target_copy) + JOIN asset.call_number acn ON (acn.id = acp.call_number) + JOIN reporter.materialized_simple_record rsr ON (rsr.id = acn.record) + JOIN money.materialized_billable_xact_summary summary + ON (summary.id = xact.xact) + JOIN ( + SELECT pay.xact, SUM(pay.amount) amount + FROM money.payment pay WHERE amount > 0 GROUP BY 1 + ) total_paid ON (total_paid.xact = xact.xact) + JOIN ( + SELECT mrp.refundable_xact, SUM(pay.amount) AS amount + FROM money.refundable_payment mrp + JOIN money.payment pay ON (mrp.payment = pay.id) + GROUP BY 1 + ) refundable_paid ON (refundable_paid.refundable_xact = xact.id) + LEFT JOIN ( + -- refunds are negative payments, negate the amount + SELECT pay.xact, -SUM(pay.amount) amount + FROM money.cash_payment pay + WHERE amount < 0 + GROUP BY 1 + ) total_refunded ON (total_refunded.xact = xact.xact) + JOIN ( + SELECT COUNT(*) AS count, mrp.refundable_xact + FROM money.refundable_payment mrp + GROUP BY 2 + ) refundable_payment_count + ON (refundable_payment_count.refundable_xact = xact.id) +; + + +COMMIT; diff --git a/KCLS/sql/schema/sqitch.plan b/KCLS/sql/schema/sqitch.plan index 2905d34fc0..0f82e9beab 100644 --- a/KCLS/sql/schema/sqitch.plan +++ b/KCLS/sql/schema/sqitch.plan @@ -77,6 +77,7 @@ all-circs-slim-backport [aged-billings-payments] 2018-02-08T15:09:53Z Bill Erick lost-paid-receipts [all-circs-slim-backport] 2017-07-03T20:10:59Z Bill Erickson,,, # Lost/Paid tracking and receipts lost-paid-receipts-data [lost-paid-receipts] 2017-08-02T15:28:08Z Bill Erickson,,, # Lost/Paid permissions and receipts aged-money-purge [lost-paid-receipts-data] 2018-10-16T19:33:11Z Bill Erickson,,, # aged billing/payment migration +refundable-xact-summary-view-speed [aged-money-purge] 2018-11-14T16:49:15Z Bill Erickson,,, # Speed up refundable xact summary stock-browse-schema [ecard-notice-validator] 2018-08-31T15:22:58Z Bill Erickson,,, # Recover stock browse data tables, etc. stock-browse-headings-report [stock-browse-schema] 2018-10-04T15:56:18Z Bill Erickson,,, # New heading report updates for stock browse stock-browse-cleanup [stock-browse-schema] 2018-10-03T18:05:49Z Bill Erickson,,, # Delete old browse data -- 2.11.0