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 where x.xact_finish is null
144 and x.circ_lib in (XX)
145 and b.billing_ts < current_timestamp - ? * '1 day'::interval
154 MAX(b.billing_ts) as last_billing,
155 SUM(b.amount) AS total_billing
157 left join money.collections_tracker c ON (c.usr = x.usr AND c.location = ?)
158 join money.billing b on (b.xact = x.id)
159 where x.xact_finish is null
161 and x.billing_location in (XX)
162 and b.billing_ts < current_timestamp - ? * '1 day'::interval
171 MAX(b.billing_ts) as last_billing,
172 SUM(b.amount) AS total_billing
173 from booking.reservation x
174 left join money.collections_tracker c ON (c.usr = x.usr AND c.location = ?)
175 join money.billing b on (b.xact = x.id)
176 where x.xact_finish is null
178 and x.pickup_lib in (XX)
179 and b.billing_ts < current_timestamp - ? * '1 day'::interval
183 left join money.payment p on (full_list.id = p.xact)
185 having SUM(total_billing) - SUM(COALESCE(p.amount,0)) > ?
191 my ($org) = actor::org_unit->search( shortname => uc($l) );
194 my $o_list = actor::org_unit->db_Main->selectcol_arrayref( "SELECT id FROM actor.org_unit_descendants(?);", {}, $org->id );
195 next unless (@$o_list);
197 my $o_txt = join ',' => @$o_list;
199 (my $real_sql = $SQL) =~ s/XX/$o_txt/gsm;
201 my $sth = money::collections_tracker->db_Main->prepare($real_sql);
202 $sth->execute( $org->id, $age, $org->id, $age, $org->id, $age, $amount );
204 while (my $row = $sth->fetchrow_hashref) {
205 #$row->{usr} = actor::user->retrieve($row->{usr})->to_fieldmapper;
206 $client->respond( $row );
211 __PACKAGE__->register_method(
212 method => 'new_collections',
213 api_name => 'open-ils.storage.money.collections.users_of_interest',
218 sub users_owing_money {
226 my $mct = money::collections_tracker->table;
227 my $mb = money::billing->table;
228 my $circ = action::circulation->table;
229 my $mg = money::grocery->table;
230 my $descendants = "actor.org_unit_descendants((select id from actor.org_unit where shortname = ?))";
236 SUM(total_billing) - SUM(COALESCE(p.amount,0)) as threshold_amount
240 SUM(b.amount) AS total_billing
241 from action.circulation x
242 join money.billing b on (b.xact = x.id)
243 where x.xact_finish is null
244 and x.circ_lib in (XX)
245 and b.billing_ts between ? and ?
254 SUM(b.amount) AS total_billing
256 join money.billing b on (b.xact = x.id)
257 where x.xact_finish is null
258 and x.billing_location in (XX)
259 and b.billing_ts between ? and ?
268 SUM(b.amount) AS total_billing
269 from booking.reservation x
270 join money.billing b on (b.xact = x.id)
271 where x.xact_finish is null
272 and x.pickup_lib in (XX)
273 and b.billing_ts between ? and ?
277 left join money.payment p on (full_list.id = p.xact)
279 having SUM(total_billing) - SUM(COALESCE(p.amount,0)) > ?
285 my ($org) = actor::org_unit->search( shortname => uc($l) );
288 my $o_list = actor::org_unit->db_Main->selectcol_arrayref( "SELECT id FROM actor.org_unit_descendants(?);", {}, $org->id );
289 next unless (@$o_list);
291 my $o_txt = join ',' => @$o_list;
293 (my $real_sql = $SQL) =~ s/XX/$o_txt/gsm;
295 my $sth = money::collections_tracker->db_Main->prepare($real_sql);
296 $sth->execute( $start, $end, $start, $end, $amount );
298 while (my $row = $sth->fetchrow_hashref) {
299 #$row->{usr} = actor::user->retrieve($row->{usr})->to_fieldmapper;
300 $client->respond( $row );
305 __PACKAGE__->register_method(
306 method => 'users_owing_money',
307 api_name => 'open-ils.storage.money.collections.users_owing_money',
312 sub active_in_collections {
315 my $startdate = shift;
319 my $mct = money::collections_tracker->table;
320 my $mb = money::billing->table;
321 my $circ = action::circulation->table;
322 my $mg = money::grocery->table;
326 MAX(last_pertinent_billing) AS last_pertinent_billing,
327 MAX(last_pertinent_payment) AS last_pertinent_payment
330 NULL::TIMESTAMPTZ AS last_pertinent_billing,
331 NULL::TIMESTAMPTZ AS last_pertinent_payment
332 FROM booking.reservation lt
333 JOIN money.collections_tracker cl ON (lt.usr = cl.usr)
334 JOIN money.billing bl ON (lt.id = bl.xact)
335 WHERE cl.location = ?
336 AND lt.pickup_lib IN (XX)
337 AND bl.void_time BETWEEN ? AND ?
342 MAX(bl.billing_ts) AS last_pertinent_billing,
343 NULL::TIMESTAMPTZ AS last_pertinent_payment
344 FROM booking.reservation lt
345 JOIN money.collections_tracker cl ON (lt.usr = cl.usr)
346 JOIN money.billing bl ON (lt.id = bl.xact)
347 WHERE cl.location = ?
348 AND lt.pickup_lib IN (XX)
349 AND bl.billing_ts BETWEEN ? AND ?
354 NULL::TIMESTAMPTZ AS last_pertinent_billing,
355 MAX(pm.payment_ts) AS last_pertinent_payment
356 FROM booking.reservation lt
357 JOIN money.collections_tracker cl ON (lt.usr = cl.usr)
358 JOIN money.payment pm ON (lt.id = pm.xact)
359 WHERE cl.location = ?
360 AND lt.pickup_lib IN (XX)
361 AND pm.payment_ts BETWEEN ? AND ?
366 NULL::TIMESTAMPTZ AS last_pertinent_billing,
367 NULL::TIMESTAMPTZ AS last_pertinent_payment
368 FROM money.grocery lt
369 JOIN money.collections_tracker cl ON (lt.usr = cl.usr)
370 JOIN money.billing bl ON (lt.id = bl.xact)
371 WHERE cl.location = ?
372 AND lt.billing_location IN (XX)
373 AND bl.void_time BETWEEN ? AND ?
378 MAX(bl.billing_ts) AS last_pertinent_billing,
379 NULL::TIMESTAMPTZ AS last_pertinent_payment
380 FROM money.grocery lt
381 JOIN money.collections_tracker cl ON (lt.usr = cl.usr)
382 JOIN money.billing bl ON (lt.id = bl.xact)
383 WHERE cl.location = ?
384 AND lt.billing_location IN (XX)
385 AND bl.billing_ts BETWEEN ? AND ?
390 NULL::TIMESTAMPTZ AS last_pertinent_billing,
391 MAX(pm.payment_ts) AS last_pertinent_payment
392 FROM money.grocery lt
393 JOIN money.collections_tracker cl ON (lt.usr = cl.usr)
394 JOIN money.payment pm ON (lt.id = pm.xact)
395 WHERE cl.location = ?
396 AND lt.billing_location IN (XX)
397 AND pm.payment_ts BETWEEN ? AND ?
402 NULL::TIMESTAMPTZ AS last_pertinent_billing,
403 NULL::TIMESTAMPTZ AS last_pertinent_payment
404 FROM action.circulation lt
405 JOIN money.collections_tracker cl ON (lt.usr = cl.usr)
406 WHERE cl.location = ?
407 AND lt.circ_lib IN (XX)
408 AND lt.checkin_time BETWEEN ? AND ?
413 NULL::TIMESTAMPTZ AS last_pertinent_billing,
414 MAX(pm.payment_ts) AS last_pertinent_payment
415 FROM action.circulation lt
416 JOIN money.collections_tracker cl ON (lt.usr = cl.usr)
417 JOIN money.payment pm ON (lt.id = pm.xact)
418 WHERE cl.location = ?
419 AND lt.circ_lib IN (XX)
420 AND pm.payment_ts BETWEEN ? AND ?
425 NULL::TIMESTAMPTZ AS last_pertinent_billing,
426 NULL::TIMESTAMPTZ AS last_pertinent_payment
427 FROM action.circulation lt
428 JOIN money.collections_tracker cl ON (lt.usr = cl.usr)
429 JOIN money.billing bl ON (lt.id = bl.xact)
430 WHERE cl.location = ?
431 AND lt.circ_lib IN (XX)
432 AND bl.void_time BETWEEN ? AND ?
437 MAX(bl.billing_ts) AS last_pertinent_billing,
438 NULL::TIMESTAMPTZ AS last_pertinent_payment
439 FROM action.circulation lt
440 JOIN money.collections_tracker cl ON (lt.usr = cl.usr)
441 JOIN money.billing bl ON (lt.id = bl.xact)
442 WHERE cl.location = ?
443 AND lt.circ_lib IN (XX)
444 AND bl.billing_ts BETWEEN ? AND ?
453 my ($org) = actor::org_unit->search( shortname => uc($l) );
456 my $o_list = actor::org_unit->db_Main->selectcol_arrayref( "SELECT id FROM actor.org_unit_descendants(?);", {}, $org->id );
457 next unless (@$o_list);
459 my $o_txt = join ',' => @$o_list;
461 (my $real_sql = $SQL) =~ s/XX/$o_txt/gsm;
463 my $sth = money::collections_tracker->db_Main->prepare($real_sql);
465 # reservation queries
466 $org->id, $startdate, $enddate,
467 $org->id, $startdate, $enddate,
468 $org->id, $startdate, $enddate,
471 $org->id, $startdate, $enddate,
472 $org->id, $startdate, $enddate,
473 $org->id, $startdate, $enddate,
476 $org->id, $startdate, $enddate,
477 $org->id, $startdate, $enddate,
478 $org->id, $startdate, $enddate,
479 $org->id, $startdate, $enddate
482 while (my $row = $sth->fetchrow_hashref) {
483 $row->{usr} = actor::user->retrieve($row->{usr})->to_fieldmapper;
484 $client->respond( $row );
489 __PACKAGE__->register_method(
490 method => 'active_in_collections',
491 api_name => 'open-ils.storage.money.collections.users_with_activity',
496 sub ou_desk_payments {
500 my $startdate = shift;
503 return undef unless ($startdate =~ /^\d{4}-\d{2}-\d{2}$/o);
504 return undef unless ($enddate =~ /^\d{4}-\d{2}-\d{2}$/o);
505 return undef unless ($lib =~ /^\d+$/o);
509 SELECT ws.id as workstation,
510 SUM( CASE WHEN p.payment_type = 'cash_payment' THEN p.amount ELSE 0.0 END ) as cash_payment,
511 SUM( CASE WHEN p.payment_type = 'check_payment' THEN p.amount ELSE 0.0 END ) as check_payment,
512 SUM( CASE WHEN p.payment_type = 'credit_card_payment' THEN p.amount ELSE 0.0 END ) as credit_card_payment
513 FROM money.desk_payment_view p
514 JOIN actor.workstation ws ON (ws.id = p.cash_drawer)
515 WHERE p.payment_ts >= '$startdate'
516 AND p.payment_ts < '$enddate'::TIMESTAMPTZ + INTERVAL '1 day'
517 AND p.voided IS FALSE
518 AND ws.owning_lib = $lib
524 my $rows = money::payment->db_Main->selectall_arrayref( $sql );
527 my $x = new Fieldmapper::money::workstation_payment_summary;
528 $x->workstation( actor::workstation->retrieve($$r[0])->to_fieldmapper );
529 $x->cash_payment($$r[1]);
530 $x->check_payment($$r[2]);
531 $x->credit_card_payment($$r[3]);
533 $client->respond($x);
538 __PACKAGE__->register_method(
539 method => 'ou_desk_payments',
540 api_name => 'open-ils.storage.money.org_unit.desk_payments',
545 sub ou_user_payments {
549 my $startdate = shift;
552 return undef unless ($startdate =~ /^\d{4}-\d{2}-\d{2}$/o);
553 return undef unless ($enddate =~ /^\d{4}-\d{2}-\d{2}$/o);
554 return undef unless ($lib =~ /^\d+$/o);
559 SUM( CASE WHEN p.payment_type = 'forgive_payment' THEN p.amount ELSE 0.0 END ) as forgive_payment,
560 SUM( CASE WHEN p.payment_type = 'work_payment' THEN p.amount ELSE 0.0 END ) as work_payment,
561 SUM( CASE WHEN p.payment_type = 'credit_payment' THEN p.amount ELSE 0.0 END ) as credit_payment,
562 SUM( CASE WHEN p.payment_type = 'goods_payment' THEN p.amount ELSE 0.0 END ) as goods_payment
563 FROM money.bnm_payment_view p
564 JOIN actor.usr au ON (au.id = p.accepting_usr)
565 WHERE p.payment_ts >= '$startdate'
566 AND p.payment_ts < '$enddate'::TIMESTAMPTZ + INTERVAL '1 day'
567 AND p.voided IS FALSE
568 AND au.home_ou = $lib
569 AND p.payment_type IN ('credit_payment','forgive_payment','work_payment','goods_payment')
575 my $rows = money::payment->db_Main->selectall_arrayref( $sql );
578 my $x = new Fieldmapper::money::user_payment_summary;
579 $x->usr( actor::user->retrieve($$r[0])->to_fieldmapper );
580 $x->forgive_payment($$r[1]);
581 $x->work_payment($$r[2]);
582 $x->credit_payment($$r[3]);
583 $x->goods_payment($$r[4]);
585 $client->respond($x);
590 __PACKAGE__->register_method(
591 method => 'ou_user_payments',
592 api_name => 'open-ils.storage.money.org_unit.user_payments',
597 sub mark_unrecovered {
601 my $x = money::billable_xact->retrieve($xact);
602 $x->unrecovered( 't' );
605 __PACKAGE__->register_method(
606 method => 'mark_unrecovered',
607 api_name => 'open-ils.storage.money.billable_xact.mark_unrecovered',