From: berick <berick@esilibrary.com>
Date: Mon, 9 May 2011 19:33:08 +0000 (-0400)
Subject: hopefully more readable sql for excluding exempt patrons from collections.  thanks... 
X-Git-Url: http://git.equinoxoli.org/?p=evergreen-equinox.git;a=commitdiff_plain;h=patron_no_collections

hopefully more readable sql for excluding exempt patrons from collections.  thanks, miker
---

diff --git a/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Publisher/money.pm b/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Publisher/money.pm
index 050ded1..a8336ea 100644
--- a/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Publisher/money.pm
+++ b/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Publisher/money.pm
@@ -139,14 +139,13 @@ select
           from  action.circulation x
                 left join money.collections_tracker c ON (c.usr = x.usr AND c.location = ?)
                 join money.billing b on (b.xact = x.id)
+                LEFT JOIN actor.usr_setting set ON (set.usr = x.usr and set.name='circ.collections.exempt' and set.value = 'true')
           where x.xact_finish is null
                 and c.id is null
                 and x.circ_lib in (XX)
                 and b.billing_ts < current_timestamp - ? * '1 day'::interval
                 and not b.voided
-                and not exists (
-                    select set.id from actor.usr_setting set where set.usr = x.usr and set.name='circ.collections.exempt' and set.value = 'true'
-                )
+                and set.id IS NULL
           group by 1,2
 
                   union all
@@ -159,14 +158,13 @@ select
           from  money.grocery x
                 left join money.collections_tracker c ON (c.usr = x.usr AND c.location = ?)
                 join money.billing b on (b.xact = x.id)
+                LEFT JOIN actor.usr_setting set ON (set.usr = x.usr and set.name='circ.collections.exempt' and set.value = 'true')
           where x.xact_finish is null
                 and c.id is null
                 and x.billing_location in (XX)
                 and b.billing_ts < current_timestamp - ? * '1 day'::interval
                 and not b.voided
-                and not exists (
-                    select set.id from actor.usr_setting set where set.usr = x.usr and set.name='circ.collections.exempt' and set.value = 'true'
-                )
+                and set.id IS NULL
           group by 1,2
 
                   union all
@@ -179,14 +177,13 @@ select
           from  booking.reservation x
                 left join money.collections_tracker c ON (c.usr = x.usr AND c.location = ?)
                 join money.billing b on (b.xact = x.id)
+                LEFT JOIN actor.usr_setting set ON (set.usr = x.usr and set.name='circ.collections.exempt' and set.value = 'true')
           where x.xact_finish is null
                 and c.id is null
                 and x.pickup_lib in (XX)
                 and b.billing_ts < current_timestamp - ? * '1 day'::interval
                 and not b.voided
-                and not exists (
-                    select set.id from actor.usr_setting set where set.usr = x.usr and set.name='circ.collections.exempt' and set.value = 'true'
-                )
+                and set.id IS NULL
           group by 1,2
         ) full_list
         left join money.payment p on (full_list.id = p.xact)