1 package OpenILS::Application::Storage::Publisher::money;
2 use base qw/OpenILS::Application::Storage/;
3 use OpenSRF::Utils::Logger qw/:level/;
5 my $log = 'OpenSRF::Utils::Logger';
12 my $s = new Fieldmapper::money::billable_transaction_summary;
15 $s->xact_start( $x->xact_start );
16 $s->xact_finish( $x->xact_finish );
20 for my $b ($x->billings) {
22 #$log->debug( "billing is ".$b->amount, DEBUG );
23 $to += ($b->amount * 100);
24 $lb ||= $b->billing_ts;
25 if ($b->billing_ts ge $lb) {
27 $s->last_billing_note($b->note);
28 $s->last_billing_ts($b->billing_ts);
29 $s->last_billing_type($b->billing_type);
33 $s->total_owed( sprintf('%0.2f', ($to) / 100 ) );
37 for my $p ($x->payments) {
38 #$log->debug( "payment is ".$p->amount." voided = ".$p->voided, DEBUG );
39 next if ($p->voided eq 't');
40 $tp += ($p->amount * 100);
41 $lp ||= $p->payment_ts;
42 if ($p->payment_ts ge $lp) {
44 $s->last_payment_note($p->note);
45 $s->last_payment_ts($p->payment_ts);
46 $s->last_payment_type($p->payment_type);
49 $s->total_paid( sprintf('%0.2f', ($tp) / 100 ) );
51 $s->balance_owed( sprintf('%0.2f', (($to) - ($tp)) / 100) );
52 #$log->debug( "balance of ".$x->id." == ".$s->balance_owed, DEBUG );
54 $s->xact_type( 'grocery' ) if (money::grocery->retrieve($x->id));
55 $s->xact_type( 'circulation' ) if (action::circulation->retrieve($x->id));
68 my @xacts = money::billable_transaction->search_where( $search );
69 $client->respond( $_ ) for (_make_mbts(@xacts));
73 __PACKAGE__->register_method(
74 method => 'search_mbts',
75 api_name => 'open-ils.storage.money.billable_transaction.summary.search',
85 my @xacts = $self->method_lookup( 'open-ils.storage.money.billable_transaction.summary.search' )->run( { usr => $usr, xact_finish => undef } );
87 my ($total,$owed,$paid) = (0.0,0.0,0.0);
89 $total += $x->total_owed;
90 $owed += $x->balance_owed;
91 $paid += $x->total_paid;
94 my $ous = Fieldmapper::money::open_user_summary->new;
96 $ous->total_paid( sprintf('%0.2f', $paid) );
97 $ous->total_owed( sprintf('%0.2f', $total) );
98 $ous->balance_owed( sprintf('%0.2f', $owed) );
102 __PACKAGE__->register_method(
103 method => 'search_ous',
104 api_name => 'open-ils.storage.money.open_user_summary.search',
109 sub new_collections {
116 my $mct = money::collections_tracker->table;
117 my $mb = money::billing->table;
118 my $circ = action::circulation->table;
119 my $mg = money::grocery->table;
120 my $descendants = "actor.org_unit_descendants((select id from actor.org_unit where shortname = ?))";
126 MAX(last_billing) as last_pertinent_billing,
127 SUM(total_billing) - SUM(COALESCE(p.amount,0)) as threshold_amount
131 MAX(b.billing_ts) as last_billing,
132 SUM(b.amount) AS total_billing
133 from action.circulation x
134 left join money.collections_tracker c ON (c.usr = x.usr AND c.location = ?)
135 join money.billing b on (b.xact = x.id)
136 where x.xact_finish is null
138 and x.circ_lib in (XX)
139 and b.billing_ts < current_timestamp - ? * '1 day'::interval
148 MAX(b.billing_ts) as last_billing,
149 SUM(b.amount) AS total_billing
151 left join money.collections_tracker c ON (c.usr = x.usr AND c.location = ?)
152 join money.billing b on (b.xact = x.id)
153 where x.xact_finish is null
155 and x.billing_location in (XX)
156 and b.billing_ts < current_timestamp - ? * '1 day'::interval
160 left join money.payment p on (full_list.id = p.xact)
162 having SUM(total_billing) - SUM(COALESCE(p.amount,0)) > ?
168 my ($org) = actor::org_unit->search( shortname => uc($l) );
171 my $o_list = actor::org_unit->db_Main->selectcol_arrayref( "SELECT id FROM actor.org_unit_descendants(?);", {}, $org->id );
172 next unless (@$o_list);
174 my $o_txt = join ',' => @$o_list;
176 (my $real_sql = $SQL) =~ s/XX/$o_txt/gsm;
178 my $sth = money::collections_tracker->db_Main->prepare($real_sql);
179 $sth->execute( $org->id, $age, $org->id, $age, $amount );
181 while (my $row = $sth->fetchrow_hashref) {
182 #$row->{usr} = actor::user->retrieve($row->{usr})->to_fieldmapper;
183 $client->respond( $row );
188 __PACKAGE__->register_method(
189 method => 'new_collections',
190 api_name => 'open-ils.storage.money.collections.users_of_interest',
195 sub users_owing_money {
203 my $mct = money::collections_tracker->table;
204 my $mb = money::billing->table;
205 my $circ = action::circulation->table;
206 my $mg = money::grocery->table;
207 my $descendants = "actor.org_unit_descendants((select id from actor.org_unit where shortname = ?))";
213 SUM(total_billing) - SUM(COALESCE(p.amount,0)) as threshold_amount
217 SUM(b.amount) AS total_billing
218 from action.circulation x
219 join money.billing b on (b.xact = x.id)
220 where x.xact_finish is null
221 and x.circ_lib in (XX)
222 and b.billing_ts between ? and ?
231 SUM(b.amount) AS total_billing
233 join money.billing b on (b.xact = x.id)
234 where x.xact_finish is null
235 and x.billing_location in (XX)
236 and b.billing_ts between ? and ?
240 left join money.payment p on (full_list.id = p.xact)
242 having SUM(total_billing) - SUM(COALESCE(p.amount,0)) > ?
248 my ($org) = actor::org_unit->search( shortname => uc($l) );
251 my $o_list = actor::org_unit->db_Main->selectcol_arrayref( "SELECT id FROM actor.org_unit_descendants(?);", {}, $org->id );
252 next unless (@$o_list);
254 my $o_txt = join ',' => @$o_list;
256 (my $real_sql = $SQL) =~ s/XX/$o_txt/gsm;
258 my $sth = money::collections_tracker->db_Main->prepare($real_sql);
259 $sth->execute( $start, $end, $start, $end, $amount );
261 while (my $row = $sth->fetchrow_hashref) {
262 #$row->{usr} = actor::user->retrieve($row->{usr})->to_fieldmapper;
263 $client->respond( $row );
268 __PACKAGE__->register_method(
269 method => 'users_owing_money',
270 api_name => 'open-ils.storage.money.collections.users_owing_money',
275 sub active_in_collections {
278 my $startdate = shift;
282 my $mct = money::collections_tracker->table;
283 my $mb = money::billing->table;
284 my $circ = action::circulation->table;
285 my $mg = money::grocery->table;
289 MAX(last_pertinent_billing) AS last_pertinent_billing,
290 MAX(last_pertinent_payment) AS last_pertinent_payment
293 NULL::TIMESTAMPTZ AS last_pertinent_billing,
294 NULL::TIMESTAMPTZ AS last_pertinent_payment
295 FROM money.grocery lt
296 JOIN money.collections_tracker cl ON (lt.usr = cl.usr)
297 JOIN money.billing bl ON (lt.id = bl.xact)
298 WHERE cl.location = ?
299 AND lt.billing_location IN (XX)
300 AND bl.void_time BETWEEN ? AND ?
305 MAX(bl.billing_ts) AS last_pertinent_billing,
306 NULL::TIMESTAMPTZ AS last_pertinent_payment
307 FROM money.grocery lt
308 JOIN money.collections_tracker cl ON (lt.usr = cl.usr)
309 JOIN money.billing bl ON (lt.id = bl.xact)
310 WHERE cl.location = ?
311 AND lt.billing_location IN (XX)
312 AND bl.billing_ts BETWEEN ? AND ?
317 NULL::TIMESTAMPTZ AS last_pertinent_billing,
318 MAX(pm.payment_ts) AS last_pertinent_payment
319 FROM money.grocery lt
320 JOIN money.collections_tracker cl ON (lt.usr = cl.usr)
321 JOIN money.payment pm ON (lt.id = pm.xact)
322 WHERE cl.location = ?
323 AND lt.billing_location IN (XX)
324 AND pm.payment_ts BETWEEN ? AND ?
329 NULL::TIMESTAMPTZ AS last_pertinent_billing,
330 NULL::TIMESTAMPTZ AS last_pertinent_payment
331 FROM action.circulation lt
332 JOIN money.collections_tracker cl ON (lt.usr = cl.usr)
333 WHERE cl.location = ?
334 AND lt.circ_lib IN (XX)
335 AND lt.checkin_time BETWEEN ? AND ?
340 NULL::TIMESTAMPTZ AS last_pertinent_billing,
341 MAX(pm.payment_ts) AS last_pertinent_payment
342 FROM action.circulation lt
343 JOIN money.collections_tracker cl ON (lt.usr = cl.usr)
344 JOIN money.payment pm ON (lt.id = pm.xact)
345 WHERE cl.location = ?
346 AND lt.circ_lib IN (XX)
347 AND pm.payment_ts BETWEEN ? AND ?
352 NULL::TIMESTAMPTZ AS last_pertinent_billing,
353 NULL::TIMESTAMPTZ AS last_pertinent_payment
354 FROM action.circulation lt
355 JOIN money.collections_tracker cl ON (lt.usr = cl.usr)
356 JOIN money.billing bl ON (lt.id = bl.xact)
357 WHERE cl.location = ?
358 AND lt.circ_lib IN (XX)
359 AND bl.void_time BETWEEN ? AND ?
364 MAX(bl.billing_ts) AS last_pertinent_billing,
365 NULL::TIMESTAMPTZ AS last_pertinent_payment
366 FROM action.circulation lt
367 JOIN money.collections_tracker cl ON (lt.usr = cl.usr)
368 JOIN money.billing bl ON (lt.id = bl.xact)
369 WHERE cl.location = ?
370 AND lt.circ_lib IN (XX)
371 AND bl.billing_ts BETWEEN ? AND ?
380 my ($org) = actor::org_unit->search( shortname => uc($l) );
383 my $o_list = actor::org_unit->db_Main->selectcol_arrayref( "SELECT id FROM actor.org_unit_descendants(?);", {}, $org->id );
384 next unless (@$o_list);
386 my $o_txt = join ',' => @$o_list;
388 (my $real_sql = $SQL) =~ s/XX/$o_txt/gsm;
390 my $sth = money::collections_tracker->db_Main->prepare($real_sql);
392 $org->id, $startdate, $enddate,
393 $org->id, $startdate, $enddate,
394 $org->id, $startdate, $enddate,
395 $org->id, $startdate, $enddate,
396 $org->id, $startdate, $enddate
399 while (my $row = $sth->fetchrow_hashref) {
400 $row->{usr} = actor::user->retrieve($row->{usr})->to_fieldmapper;
401 $client->respond( $row );
406 __PACKAGE__->register_method(
407 method => 'active_in_collections',
408 api_name => 'open-ils.storage.money.collections.users_with_activity',
413 sub ou_desk_payments {
417 my $startdate = shift;
420 return undef unless ($startdate =~ /^\d{4}-\d{2}-\d{2}$/o);
421 return undef unless ($enddate =~ /^\d{4}-\d{2}-\d{2}$/o);
422 return undef unless ($lib =~ /^\d+$/o);
426 SELECT ws.id as workstation,
427 SUM( CASE WHEN p.payment_type = 'cash_payment' THEN p.amount ELSE 0.0 END ) as cash_payment,
428 SUM( CASE WHEN p.payment_type = 'check_payment' THEN p.amount ELSE 0.0 END ) as check_payment,
429 SUM( CASE WHEN p.payment_type = 'credit_card_payment' THEN p.amount ELSE 0.0 END ) as credit_card_payment
430 FROM money.desk_payment_view p
431 JOIN actor.workstation ws ON (ws.id = p.cash_drawer)
432 WHERE p.payment_ts >= '$startdate'
433 AND p.payment_ts < '$enddate'::TIMESTAMPTZ + INTERVAL '1 day'
434 AND p.voided IS FALSE
435 AND ws.owning_lib = $lib
441 my $rows = money::payment->db_Main->selectall_arrayref( $sql );
444 my $x = new Fieldmapper::money::workstation_payment_summary;
445 $x->workstation( actor::workstation->retrieve($$r[0])->to_fieldmapper );
446 $x->cash_payment($$r[1]);
447 $x->check_payment($$r[2]);
448 $x->credit_card_payment($$r[3]);
450 $client->respond($x);
455 __PACKAGE__->register_method(
456 method => 'ou_desk_payments',
457 api_name => 'open-ils.storage.money.org_unit.desk_payments',
462 sub ou_user_payments {
466 my $startdate = shift;
469 return undef unless ($startdate =~ /^\d{4}-\d{2}-\d{2}$/o);
470 return undef unless ($enddate =~ /^\d{4}-\d{2}-\d{2}$/o);
471 return undef unless ($lib =~ /^\d+$/o);
476 SUM( CASE WHEN p.payment_type = 'forgive_payment' THEN p.amount ELSE 0.0 END ) as forgive_payment,
477 SUM( CASE WHEN p.payment_type = 'work_payment' THEN p.amount ELSE 0.0 END ) as work_payment,
478 SUM( CASE WHEN p.payment_type = 'credit_payment' THEN p.amount ELSE 0.0 END ) as credit_payment
479 FROM money.bnm_payment_view p
480 JOIN actor.usr au ON (au.id = p.accepting_usr)
481 WHERE p.payment_ts >= '$startdate'
482 AND p.payment_ts < '$enddate'::TIMESTAMPTZ + INTERVAL '1 day'
483 AND p.voided IS FALSE
484 AND au.home_ou = $lib
485 AND p.payment_type IN ('credit_payment','forgive_payment','work_payment')
491 my $rows = money::payment->db_Main->selectall_arrayref( $sql );
494 my $x = new Fieldmapper::money::user_payment_summary;
495 $x->usr( actor::user->retrieve($$r[0])->to_fieldmapper );
496 $x->forgive_payment($$r[1]);
497 $x->work_payment($$r[2]);
498 $x->credit_payment($$r[3]);
500 $client->respond($x);
505 __PACKAGE__->register_method(
506 method => 'ou_user_payments',
507 api_name => 'open-ils.storage.money.org_unit.user_payments',