From 2c4de06de467bd17780ef251d4639af1765c507b Mon Sep 17 00:00:00 2001 From: Bill Erickson Date: Tue, 16 Oct 2018 11:21:35 -0400 Subject: [PATCH] JBAS-1306 Lost/paid summary view SQL repair Avoid use of all_ciculation_slim and all_payments view for active lookups. These slow the queries down considerably for the normal use case of staff accepting payments and printing recteipts. If the B.O. needs access to payments for anon circs / money down the road we may can create a separate query. Signed-off-by: Bill Erickson --- KCLS/sql/schema/deploy/lost-paid-receipts.sql | 10 +++++----- 1 file changed, 5 insertions(+), 5 deletions(-) diff --git a/KCLS/sql/schema/deploy/lost-paid-receipts.sql b/KCLS/sql/schema/deploy/lost-paid-receipts.sql index 6a11e38819..f7a5a9083a 100644 --- a/KCLS/sql/schema/deploy/lost-paid-receipts.sql +++ b/KCLS/sql/schema/deploy/lost-paid-receipts.sql @@ -91,20 +91,20 @@ CREATE OR REPLACE VIEW money.refundable_xact_summary AS total_refunded.amount::NUMERIC(8,2) AS total_refunded, refundable_payment_count.count AS num_refundable_payments FROM money.refundable_xact xact - JOIN action.all_circulation_slim circ ON (circ.id = 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.id) + ON (summary.id = xact.xact) JOIN ( SELECT pay.xact, SUM(pay.amount) amount - FROM money.all_payments pay WHERE amount > 0 GROUP BY 1 + 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.all_payments pay ON (mrp.payment = pay.id) + JOIN money.payment pay ON (mrp.payment = pay.id) GROUP BY 1 ) refundable_paid ON (refundable_paid.refundable_xact = xact.id) LEFT JOIN ( @@ -132,7 +132,7 @@ CREATE OR REPLACE VIEW money.refundable_payment_summary AS pay.payment_type, aws.name AS workstation FROM money.refundable_payment mrp - JOIN money.all_payments pay ON (pay.id = mrp.payment) + JOIN money.payment_view pay ON (pay.id = mrp.payment) JOIN actor.org_unit aou ON (aou.id = mrp.payment_ou) LEFT JOIN money.cash_payment cash ON (cash.id = pay.id) LEFT JOIN actor.workstation aws ON (aws.id = cash.cash_drawer) -- 2.11.0