From 145f585694d439a7459f5e20547d1e64f91552f9 Mon Sep 17 00:00:00 2001 From: miker Date: Thu, 6 Jul 2006 16:39:08 +0000 Subject: [PATCH] fixing collection-finder method git-svn-id: svn://svn.open-ils.org/ILS/trunk@4907 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- .../src/perlmods/OpenILS/Application/Storage/Publisher/money.pm | 7 +++---- 1 file changed, 3 insertions(+), 4 deletions(-) diff --git a/Open-ILS/src/perlmods/OpenILS/Application/Storage/Publisher/money.pm b/Open-ILS/src/perlmods/OpenILS/Application/Storage/Publisher/money.pm index fc62b0daf0..2ad7ac0be9 100644 --- a/Open-ILS/src/perlmods/OpenILS/Application/Storage/Publisher/money.pm +++ b/Open-ILS/src/perlmods/OpenILS/Application/Storage/Publisher/money.pm @@ -20,17 +20,16 @@ sub new_collections { my $SQL = <<" SQL"; SELECT lt.usr, MAX(bl.billing_ts) AS last_pertinent_billing, - SUM(bl.amount) - SUM(COALESCE(pm.amount,0)) AS threshold_amount + SUM(bl.amount) - SUM((SELECT SUM(amount) FROM money.payment WHERE xact = lt.id)) AS threshold_amount FROM ( SELECT id,usr,billing_location AS location FROM money.grocery UNION ALL SELECT id,usr,circ_lib AS location FROM action.circulation ) AS lt JOIN $descendants d ON (lt.location = d.id) - JOIN money.billing bl ON (lt.id = bl.xact) - LEFT JOIN money.payment pm ON (lt.id = pm.xact) + JOIN money.billing bl ON (lt.id = bl.xact AND bl.voided IS FALSE) LEFT JOIN money.collections_tracker cl USING (usr,location) WHERE AGE(bl.billing_ts) > ? AND cl.usr IS NULL - GROUP BY lt.usr HAVING (SUM(bl.amount) - SUM(COALESCE(pm.amount,0))) > ? + GROUP BY lt.usr HAVING (SUM(bl.amount) - SUM((SELECT SUM(amount) FROM money.payment WHERE xact = lt.id))) > ? SQL my @l_ids; -- 2.11.0