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 if (action::circulation->retrieve($x->id)) {
55 $s->xact_type( 'circulation' );
56 } elsif (money::grocery->retrieve($x->id)) {
57 $s->xact_type( 'grocery' );
58 } elsif (booking::reservation->retrieve($x->id)) {
59 $s->xact_type( 'reservation' );
73 my @xacts = money::billable_transaction->search_where( $search );
74 $client->respond( $_ ) for (_make_mbts(@xacts));
78 __PACKAGE__->register_method(
79 method => 'search_mbts',
80 api_name => 'open-ils.storage.money.billable_transaction.summary.search',
90 my @xacts = $self->method_lookup( 'open-ils.storage.money.billable_transaction.summary.search' )->run( { usr => $usr, xact_finish => undef } );
92 my ($total,$owed,$paid) = (0.0,0.0,0.0);
94 $total += $x->total_owed;
95 $owed += $x->balance_owed;
96 $paid += $x->total_paid;
99 my $ous = Fieldmapper::money::open_user_summary->new;
101 $ous->total_paid( sprintf('%0.2f', $paid) );
102 $ous->total_owed( sprintf('%0.2f', $total) );
103 $ous->balance_owed( sprintf('%0.2f', $owed) );
107 __PACKAGE__->register_method(
108 method => 'search_ous',
109 api_name => 'open-ils.storage.money.open_user_summary.search',
114 sub new_collections {
121 my $mct = money::collections_tracker->table;
122 my $mb = money::billing->table;
123 my $circ = action::circulation->table;
124 my $mg = money::grocery->table;
125 my $res = booking::reservation->table;
126 my $descendants = "actor.org_unit_descendants((select id from actor.org_unit where shortname = ?))";
132 MAX(last_billing) as last_pertinent_billing,
133 SUM(total_billing) - SUM(COALESCE(p.amount,0)) as threshold_amount
137 MAX(b.billing_ts) as last_billing,
138 SUM(b.amount) AS total_billing
139 from action.circulation x
140 left join money.collections_tracker c ON (c.usr = x.usr AND c.location = ?)
141 join money.billing b on (b.xact = x.id)
142 LEFT JOIN actor.usr_setting set ON (set.usr = x.usr and set.name='circ.collections.exempt' and set.value = 'true')
143 where x.xact_finish is null
145 and x.circ_lib in (XX)
146 and b.billing_ts < current_timestamp - ? * '1 day'::interval
156 MAX(b.billing_ts) as last_billing,
157 SUM(b.amount) AS total_billing
159 left join money.collections_tracker c ON (c.usr = x.usr AND c.location = ?)
160 join money.billing b on (b.xact = x.id)
161 LEFT JOIN actor.usr_setting set ON (set.usr = x.usr and set.name='circ.collections.exempt' and set.value = 'true')
162 where x.xact_finish is null
164 and x.billing_location in (XX)
165 and b.billing_ts < current_timestamp - ? * '1 day'::interval
175 MAX(b.billing_ts) as last_billing,
176 SUM(b.amount) AS total_billing
177 from booking.reservation x
178 left join money.collections_tracker c ON (c.usr = x.usr AND c.location = ?)
179 join money.billing b on (b.xact = x.id)
180 LEFT JOIN actor.usr_setting set ON (set.usr = x.usr and set.name='circ.collections.exempt' and set.value = 'true')
181 where x.xact_finish is null
183 and x.pickup_lib in (XX)
184 and b.billing_ts < current_timestamp - ? * '1 day'::interval
189 left join money.payment p on (full_list.id = p.xact)
191 having SUM(total_billing) - SUM(COALESCE(p.amount,0)) > ?
197 my ($org) = actor::org_unit->search( shortname => uc($l) );
200 my $o_list = actor::org_unit->db_Main->selectcol_arrayref( "SELECT id FROM actor.org_unit_descendants(?);", {}, $org->id );
201 next unless (@$o_list);
203 my $o_txt = join ',' => @$o_list;
205 (my $real_sql = $SQL) =~ s/XX/$o_txt/gsm;
207 my $sth = money::collections_tracker->db_Main->prepare($real_sql);
208 $sth->execute( $org->id, $age, $org->id, $age, $org->id, $age, $amount );
210 while (my $row = $sth->fetchrow_hashref) {
211 #$row->{usr} = actor::user->retrieve($row->{usr})->to_fieldmapper;
212 $client->respond( $row );
217 __PACKAGE__->register_method(
218 method => 'new_collections',
219 api_name => 'open-ils.storage.money.collections.users_of_interest',
224 sub users_owing_money {
232 my $mct = money::collections_tracker->table;
233 my $mb = money::billing->table;
234 my $circ = action::circulation->table;
235 my $mg = money::grocery->table;
236 my $descendants = "actor.org_unit_descendants((select id from actor.org_unit where shortname = ?))";
242 SUM(total_billing) - SUM(COALESCE(p.amount,0)) as threshold_amount
246 SUM(b.amount) AS total_billing
247 from action.circulation x
248 join money.billing b on (b.xact = x.id)
249 where x.xact_finish is null
250 and x.circ_lib in (XX)
251 and b.billing_ts between ? and ?
260 SUM(b.amount) AS total_billing
262 join money.billing b on (b.xact = x.id)
263 where x.xact_finish is null
264 and x.billing_location in (XX)
265 and b.billing_ts between ? and ?
274 SUM(b.amount) AS total_billing
275 from booking.reservation x
276 join money.billing b on (b.xact = x.id)
277 where x.xact_finish is null
278 and x.pickup_lib in (XX)
279 and b.billing_ts between ? and ?
283 left join money.payment p on (full_list.id = p.xact)
285 having SUM(total_billing) - SUM(COALESCE(p.amount,0)) > ?
291 my ($org) = actor::org_unit->search( shortname => uc($l) );
294 my $o_list = actor::org_unit->db_Main->selectcol_arrayref( "SELECT id FROM actor.org_unit_descendants(?);", {}, $org->id );
295 next unless (@$o_list);
297 my $o_txt = join ',' => @$o_list;
299 (my $real_sql = $SQL) =~ s/XX/$o_txt/gsm;
301 my $sth = money::collections_tracker->db_Main->prepare($real_sql);
302 $sth->execute( $start, $end, $start, $end, $amount );
304 while (my $row = $sth->fetchrow_hashref) {
305 #$row->{usr} = actor::user->retrieve($row->{usr})->to_fieldmapper;
306 $client->respond( $row );
311 __PACKAGE__->register_method(
312 method => 'users_owing_money',
313 api_name => 'open-ils.storage.money.collections.users_owing_money',
318 sub active_in_collections {
321 my $startdate = shift;
325 my $mct = money::collections_tracker->table;
326 my $mb = money::billing->table;
327 my $circ = action::circulation->table;
328 my $mg = money::grocery->table;
332 MAX(last_pertinent_billing) AS last_pertinent_billing,
333 MAX(last_pertinent_payment) AS last_pertinent_payment
336 NULL::TIMESTAMPTZ AS last_pertinent_billing,
337 NULL::TIMESTAMPTZ AS last_pertinent_payment
338 FROM booking.reservation lt
339 JOIN money.collections_tracker cl ON (lt.usr = cl.usr)
340 JOIN money.billing bl ON (lt.id = bl.xact)
341 WHERE cl.location = ?
342 AND lt.pickup_lib IN (XX)
343 AND bl.void_time BETWEEN ? AND ?
348 MAX(bl.billing_ts) AS last_pertinent_billing,
349 NULL::TIMESTAMPTZ AS last_pertinent_payment
350 FROM booking.reservation lt
351 JOIN money.collections_tracker cl ON (lt.usr = cl.usr)
352 JOIN money.billing bl ON (lt.id = bl.xact)
353 WHERE cl.location = ?
354 AND lt.pickup_lib IN (XX)
355 AND bl.billing_ts BETWEEN ? AND ?
360 NULL::TIMESTAMPTZ AS last_pertinent_billing,
361 MAX(pm.payment_ts) AS last_pertinent_payment
362 FROM booking.reservation lt
363 JOIN money.collections_tracker cl ON (lt.usr = cl.usr)
364 JOIN money.payment pm ON (lt.id = pm.xact)
365 WHERE cl.location = ?
366 AND lt.pickup_lib IN (XX)
367 AND pm.payment_ts BETWEEN ? AND ?
372 NULL::TIMESTAMPTZ AS last_pertinent_billing,
373 NULL::TIMESTAMPTZ AS last_pertinent_payment
374 FROM money.grocery lt
375 JOIN money.collections_tracker cl ON (lt.usr = cl.usr)
376 JOIN money.billing bl ON (lt.id = bl.xact)
377 WHERE cl.location = ?
378 AND lt.billing_location IN (XX)
379 AND bl.void_time BETWEEN ? AND ?
384 MAX(bl.billing_ts) AS last_pertinent_billing,
385 NULL::TIMESTAMPTZ AS last_pertinent_payment
386 FROM money.grocery lt
387 JOIN money.collections_tracker cl ON (lt.usr = cl.usr)
388 JOIN money.billing bl ON (lt.id = bl.xact)
389 WHERE cl.location = ?
390 AND lt.billing_location IN (XX)
391 AND bl.billing_ts BETWEEN ? AND ?
396 NULL::TIMESTAMPTZ AS last_pertinent_billing,
397 MAX(pm.payment_ts) AS last_pertinent_payment
398 FROM money.grocery lt
399 JOIN money.collections_tracker cl ON (lt.usr = cl.usr)
400 JOIN money.payment pm ON (lt.id = pm.xact)
401 WHERE cl.location = ?
402 AND lt.billing_location IN (XX)
403 AND pm.payment_ts BETWEEN ? AND ?
408 NULL::TIMESTAMPTZ AS last_pertinent_billing,
409 NULL::TIMESTAMPTZ AS last_pertinent_payment
410 FROM action.circulation lt
411 JOIN money.collections_tracker cl ON (lt.usr = cl.usr)
412 WHERE cl.location = ?
413 AND lt.circ_lib IN (XX)
414 AND lt.checkin_time BETWEEN ? AND ?
419 NULL::TIMESTAMPTZ AS last_pertinent_billing,
420 MAX(pm.payment_ts) AS last_pertinent_payment
421 FROM action.circulation lt
422 JOIN money.collections_tracker cl ON (lt.usr = cl.usr)
423 JOIN money.payment pm ON (lt.id = pm.xact)
424 WHERE cl.location = ?
425 AND lt.circ_lib IN (XX)
426 AND pm.payment_ts BETWEEN ? AND ?
431 NULL::TIMESTAMPTZ AS last_pertinent_billing,
432 NULL::TIMESTAMPTZ AS last_pertinent_payment
433 FROM action.circulation lt
434 JOIN money.collections_tracker cl ON (lt.usr = cl.usr)
435 JOIN money.billing bl ON (lt.id = bl.xact)
436 WHERE cl.location = ?
437 AND lt.circ_lib IN (XX)
438 AND bl.void_time BETWEEN ? AND ?
443 MAX(bl.billing_ts) AS last_pertinent_billing,
444 NULL::TIMESTAMPTZ AS last_pertinent_payment
445 FROM action.circulation lt
446 JOIN money.collections_tracker cl ON (lt.usr = cl.usr)
447 JOIN money.billing bl ON (lt.id = bl.xact)
448 WHERE cl.location = ?
449 AND lt.circ_lib IN (XX)
450 AND bl.billing_ts BETWEEN ? AND ?
459 my ($org) = actor::org_unit->search( shortname => uc($l) );
462 my $o_list = actor::org_unit->db_Main->selectcol_arrayref( "SELECT id FROM actor.org_unit_descendants(?);", {}, $org->id );
463 next unless (@$o_list);
465 my $o_txt = join ',' => @$o_list;
467 (my $real_sql = $SQL) =~ s/XX/$o_txt/gsm;
469 my $sth = money::collections_tracker->db_Main->prepare($real_sql);
471 # reservation queries
472 $org->id, $startdate, $enddate,
473 $org->id, $startdate, $enddate,
474 $org->id, $startdate, $enddate,
477 $org->id, $startdate, $enddate,
478 $org->id, $startdate, $enddate,
479 $org->id, $startdate, $enddate,
482 $org->id, $startdate, $enddate,
483 $org->id, $startdate, $enddate,
484 $org->id, $startdate, $enddate,
485 $org->id, $startdate, $enddate
488 while (my $row = $sth->fetchrow_hashref) {
489 $row->{usr} = actor::user->retrieve($row->{usr})->to_fieldmapper;
490 $client->respond( $row );
495 __PACKAGE__->register_method(
496 method => 'active_in_collections',
497 api_name => 'open-ils.storage.money.collections.users_with_activity',
502 sub ou_desk_payments {
506 my $startdate = shift;
509 return undef unless ($startdate =~ /^\d{4}-\d{2}-\d{2}$/o);
510 return undef unless ($enddate =~ /^\d{4}-\d{2}-\d{2}$/o);
511 return undef unless ($lib =~ /^\d+$/o);
515 SELECT ws.id as workstation,
516 SUM( CASE WHEN p.payment_type = 'cash_payment' THEN p.amount ELSE 0.0 END ) as cash_payment,
517 SUM( CASE WHEN p.payment_type = 'check_payment' THEN p.amount ELSE 0.0 END ) as check_payment,
518 SUM( CASE WHEN p.payment_type = 'credit_card_payment' THEN p.amount ELSE 0.0 END ) as credit_card_payment
519 FROM money.desk_payment_view p
520 JOIN actor.workstation ws ON (ws.id = p.cash_drawer)
521 WHERE p.payment_ts >= '$startdate'
522 AND p.payment_ts < '$enddate'::TIMESTAMPTZ + INTERVAL '1 day'
523 AND p.voided IS FALSE
524 AND ws.owning_lib = $lib
530 my $rows = money::payment->db_Main->selectall_arrayref( $sql );
533 my $x = new Fieldmapper::money::workstation_payment_summary;
534 $x->workstation( actor::workstation->retrieve($$r[0])->to_fieldmapper );
535 $x->cash_payment($$r[1]);
536 $x->check_payment($$r[2]);
537 $x->credit_card_payment($$r[3]);
539 $client->respond($x);
544 __PACKAGE__->register_method(
545 method => 'ou_desk_payments',
546 api_name => 'open-ils.storage.money.org_unit.desk_payments',
551 sub ou_user_payments {
555 my $startdate = shift;
558 return undef unless ($startdate =~ /^\d{4}-\d{2}-\d{2}$/o);
559 return undef unless ($enddate =~ /^\d{4}-\d{2}-\d{2}$/o);
560 return undef unless ($lib =~ /^\d+$/o);
565 SUM( CASE WHEN p.payment_type = 'forgive_payment' THEN p.amount ELSE 0.0 END ) as forgive_payment,
566 SUM( CASE WHEN p.payment_type = 'work_payment' THEN p.amount ELSE 0.0 END ) as work_payment,
567 SUM( CASE WHEN p.payment_type = 'credit_payment' THEN p.amount ELSE 0.0 END ) as credit_payment,
568 SUM( CASE WHEN p.payment_type = 'goods_payment' THEN p.amount ELSE 0.0 END ) as goods_payment
569 FROM money.bnm_payment_view p
570 JOIN actor.usr au ON (au.id = p.accepting_usr)
571 WHERE p.payment_ts >= '$startdate'
572 AND p.payment_ts < '$enddate'::TIMESTAMPTZ + INTERVAL '1 day'
573 AND p.voided IS FALSE
574 AND au.home_ou = $lib
575 AND p.payment_type IN ('credit_payment','forgive_payment','work_payment','goods_payment')
581 my $rows = money::payment->db_Main->selectall_arrayref( $sql );
584 my $x = new Fieldmapper::money::user_payment_summary;
585 $x->usr( actor::user->retrieve($$r[0])->to_fieldmapper );
586 $x->forgive_payment($$r[1]);
587 $x->work_payment($$r[2]);
588 $x->credit_payment($$r[3]);
589 $x->goods_payment($$r[4]);
591 $client->respond($x);
596 __PACKAGE__->register_method(
597 method => 'ou_user_payments',
598 api_name => 'open-ils.storage.money.org_unit.user_payments',
603 sub mark_unrecovered {
607 my $x = money::billable_xact->retrieve($xact);
608 $x->unrecovered( 't' );
611 __PACKAGE__->register_method(
612 method => 'mark_unrecovered',
613 api_name => 'open-ils.storage.money.billable_xact.mark_unrecovered',