From 58a51f418c93fd62c679ef6ad37e1c292d162975 Mon Sep 17 00:00:00 2001 From: miker Date: Wed, 10 Jan 2007 07:17:58 +0000 Subject: [PATCH] collections activity query update git-svn-id: svn://svn.open-ils.org/ILS/branches/rel_1_0@6751 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- .../OpenILS/Application/Storage/Publisher/money.pm | 103 +++++++++++++++++---- 1 file changed, 83 insertions(+), 20 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 ed25451394..6034ee400e 100644 --- a/Open-ILS/src/perlmods/OpenILS/Application/Storage/Publisher/money.pm +++ b/Open-ILS/src/perlmods/OpenILS/Application/Storage/Publisher/money.pm @@ -171,32 +171,95 @@ sub active_in_collections { my $mb = money::billing->table; my $circ = action::circulation->table; my $mg = money::grocery->table; - my $descendants = "actor.org_unit_descendants((select id from actor.org_unit where shortname = ?))"; my $SQL = <<" SQL"; - SELECT lt.usr, - MAX(bl.billing_ts) AS last_pertinent_billing, - MAX(pm.payment_ts) AS last_pertinent_payment - FROM ( SELECT id,usr,billing_location AS location, 'g'::char AS x_type FROM money.grocery - UNION ALL - SELECT id,usr,circ_lib AS location, 'c'::char AS x_type FROM action.circulation - UNION ALL - SELECT id,usr,circ_lib AS location, 'i'::char AS x_type FROM action.circulation - WHERE checkin_time between ? and ? ) AS lt - JOIN $descendants d ON (lt.location = d.id) - JOIN money.collections_tracker cl ON (lt.usr = cl.usr) - LEFT JOIN money.billing bl ON (lt.id = bl.xact) - LEFT JOIN money.payment pm ON (lt.id = pm.xact) - WHERE bl.billing_ts between ? and ? - OR pm.payment_ts between ? and ? - OR lt.x_type = 'i'::char - GROUP BY 1 +SELECT usr, + MAX(last_pertinent_billing) AS last_pertinent_billing, + MAX(last_pertinent_payment) AS last_pertinent_payment + FROM ( + SELECT lt.usr, + MAX(bl.billing_ts) AS last_pertinent_billing, + NULL::TIMESTAMPTZ AS last_pertinent_payment + FROM money.grocery lt + JOIN money.collections_tracker cl ON (lt.usr = cl.usr) + JOIN money.billing bl ON (lt.id = bl.xact) + WHERE cl.location = ? + AND lt.billing_location IN (XX) + AND bl.billing_ts BETWEEN ? AND ? + GROUP BY 1 + + UNION ALL + SELECT lt.usr, + NULL::TIMESTAMPTZ AS last_pertinent_billing, + MAX(pm.payment_ts) AS last_pertinent_payment + FROM money.grocery lt + JOIN money.collections_tracker cl ON (lt.usr = cl.usr) + JOIN money.payment pm ON (lt.id = pm.xact) + WHERE cl.location = ? + AND lt.billing_location IN (XX) + AND pm.payment_ts BETWEEN ? AND ? + GROUP BY 1 + + UNION ALL + SELECT lt.usr, + NULL::TIMESTAMPTZ AS last_pertinent_billing, + NULL::TIMESTAMPTZ AS last_pertinent_payment + FROM action.circulation lt + JOIN money.collections_tracker cl ON (lt.usr = cl.usr) + WHERE cl.location = ? + AND lt.circ_lib IN (XX) + AND lt.checkin_time BETWEEN ? AND ? + GROUP BY 1 + + UNION ALL + SELECT lt.usr, + NULL::TIMESTAMPTZ AS last_pertinent_billing, + MAX(pm.payment_ts) AS last_pertinent_payment + FROM action.circulation lt + JOIN money.collections_tracker cl ON (lt.usr = cl.usr) + JOIN money.payment pm ON (lt.id = pm.xact) + WHERE cl.location = ? + AND lt.circ_lib IN (XX) + AND pm.payment_ts BETWEEN ? AND ? + GROUP BY 1 + + UNION ALL + SELECT lt.usr, + MAX(bl.billing_ts) AS last_pertinent_billing, + NULL::TIMESTAMPTZ AS last_pertinent_payment + FROM action.circulation lt + JOIN money.collections_tracker cl ON (lt.usr = cl.usr) + JOIN money.billing bl ON (lt.id = bl.xact) + WHERE cl.location = ? + AND lt.circ_lib IN (XX) + AND bl.billing_ts BETWEEN ? AND ? + GROUP BY 1 + ) foo + GROUP BY 1 +; SQL my @l_ids; for my $l (@loc) { - my $sth = money::collections_tracker->db_Main->prepare($SQL); - $sth->execute( $startdate, $enddate, uc($l), $startdate, $enddate, $startdate, $enddate ); + my ($org) = actor::org_unit->search( shortname => uc($l) ); + next unless $org; + + my $o_list = actor::org_unit->db_Main->selectcol_arrayref->( "SELECT id FROM actor.org_unit_descendants(?);", {}, $org->id ); + next unless (@$o_list); + + my $o_txt = join ',' => @$o_list; + + (my $real_sql = $SQL) =~ s/XX/$o_txt/gsm; + + my $sth = money::collections_tracker->db_Main->prepare($real_sql); + $sth->execute( + $org->id, $startdate, $enddate, + $org->id, $startdate, $enddate, + $org->id, $startdate, $enddate, + $org->id, $startdate, $enddate, + $org->id, $startdate, $enddate, + ); + while (my $row = $sth->fetchrow_hashref) { $row->{usr} = actor::user->retrieve($row->{usr})->to_fieldmapper; $client->respond( $row ); -- 2.11.0