From 4ca8a4dad464c62b77e6378fd8e88b0abf982050 Mon Sep 17 00:00:00 2001 From: miker Date: Fri, 7 Jul 2006 15:51:50 +0000 Subject: [PATCH] fixing collections calls git-svn-id: svn://svn.open-ils.org/ILS/trunk@4927 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- .../src/perlmods/OpenILS/Application/Storage/Publisher/money.pm | 7 ++++--- 1 file changed, 4 insertions(+), 3 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 5c78dfb155..396c1e53c2 100644 --- a/Open-ILS/src/perlmods/OpenILS/Application/Storage/Publisher/money.pm +++ b/Open-ILS/src/perlmods/OpenILS/Application/Storage/Publisher/money.pm @@ -20,7 +20,7 @@ sub new_collections { my $SQL = <<" SQL"; SELECT lt.usr, MAX(bl.billing_ts) AS last_pertinent_billing, - SUM(bl.amount) - SUM((SELECT SUM(amount) FROM money.payment WHERE xact = lt.id)) AS threshold_amount + SUM(bl.amount) - COALESCE(SUM((SELECT SUM(amount) FROM money.payment WHERE xact = lt.id)),0) 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 @@ -31,12 +31,13 @@ sub new_collections { HAVING SUM( (SELECT COUNT(*) FROM money.collections_tracker - WHERE location in ( + WHERE usr = lt.usr + AND location in ( (SELECT id FROM $descendants ) ) ) ) = 0 - AND (SUM(bl.amount) - SUM((SELECT SUM(amount) FROM money.payment WHERE xact = lt.id))) > ? + AND (SUM(bl.amount) - COALESCE(SUM((SELECT SUM(amount) FROM money.payment WHERE xact = lt.id)),0)) > ? SQL my @l_ids; -- 2.11.0