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;
131 MAX(last_billing) as last_pertinent_billing,
132 SUM(total_billing) - SUM(COALESCE(p.amount,0)) as threshold_amount
136 MAX(b.billing_ts) as last_billing,
137 SUM(b.amount) AS total_billing
138 from action.circulation x
139 left join money.collections_tracker c ON (c.usr = x.usr AND c.location = ?)
140 join money.billing b on (b.xact = x.id)
141 LEFT JOIN actor.usr_setting set ON (set.usr = x.usr and set.name='circ.collections.exempt' and set.value = 'true')
142 where x.xact_finish is null
144 and x.circ_lib in (XX)
145 and b.billing_ts < current_timestamp - ? * '1 day'::interval
155 MAX(b.billing_ts) as last_billing,
156 SUM(b.amount) AS total_billing
158 left join money.collections_tracker c ON (c.usr = x.usr AND c.location = ?)
159 join money.billing b on (b.xact = x.id)
160 LEFT JOIN actor.usr_setting set ON (set.usr = x.usr and set.name='circ.collections.exempt' and set.value = 'true')
161 where x.xact_finish is null
163 and x.billing_location in (XX)
164 and b.billing_ts < current_timestamp - ? * '1 day'::interval
174 MAX(b.billing_ts) as last_billing,
175 SUM(b.amount) AS total_billing
176 from booking.reservation x
177 left join money.collections_tracker c ON (c.usr = x.usr AND c.location = ?)
178 join money.billing b on (b.xact = x.id)
179 LEFT JOIN actor.usr_setting set ON (set.usr = x.usr and set.name='circ.collections.exempt' and set.value = 'true')
180 where x.xact_finish is null
182 and x.pickup_lib in (XX)
183 and b.billing_ts < current_timestamp - ? * '1 day'::interval
188 left join money.payment p on (full_list.id = p.xact)
189 left join actor.usr au on (au.id = usr)
190 where au.deleted is false
192 having SUM(total_billing) - SUM(COALESCE(p.amount,0)) > ?
198 my ($org) = actor::org_unit->search( shortname => uc($l) );
201 my $o_list = actor::org_unit->db_Main->selectcol_arrayref( "SELECT id FROM actor.org_unit_descendants(?);", {}, $org->id );
202 next unless (@$o_list);
204 my $o_txt = join ',' => @$o_list;
206 (my $real_sql = $SQL) =~ s/XX/$o_txt/gsm;
208 my $sth = money::collections_tracker->db_Main->prepare($real_sql);
209 $sth->execute( $org->id, $age, $org->id, $age, $org->id, $age, $amount );
211 while (my $row = $sth->fetchrow_hashref) {
212 #$row->{usr} = actor::user->retrieve($row->{usr})->to_fieldmapper;
213 $client->respond( $row );
218 __PACKAGE__->register_method(
219 method => 'new_collections',
220 api_name => 'open-ils.storage.money.collections.users_of_interest',
225 sub active_in_collections {
228 my $startdate = shift;
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;
239 MAX(last_pertinent_billing) AS last_pertinent_billing,
240 MAX(last_pertinent_payment) AS last_pertinent_payment
243 NULL::TIMESTAMPTZ AS last_pertinent_billing,
244 NULL::TIMESTAMPTZ AS last_pertinent_payment
245 FROM booking.reservation lt
246 JOIN money.collections_tracker cl ON (lt.usr = cl.usr)
247 JOIN money.billing bl ON (lt.id = bl.xact)
248 WHERE cl.location = ?
249 AND lt.pickup_lib IN (XX)
250 AND bl.void_time BETWEEN ? AND ?
255 MAX(bl.billing_ts) AS last_pertinent_billing,
256 NULL::TIMESTAMPTZ AS last_pertinent_payment
257 FROM booking.reservation lt
258 JOIN money.collections_tracker cl ON (lt.usr = cl.usr)
259 JOIN money.billing bl ON (lt.id = bl.xact)
260 WHERE cl.location = ?
261 AND lt.pickup_lib IN (XX)
262 AND bl.billing_ts BETWEEN ? AND ?
267 NULL::TIMESTAMPTZ AS last_pertinent_billing,
268 MAX(pm.payment_ts) AS last_pertinent_payment
269 FROM booking.reservation lt
270 JOIN money.collections_tracker cl ON (lt.usr = cl.usr)
271 JOIN money.payment pm ON (lt.id = pm.xact)
272 WHERE cl.location = ?
273 AND lt.pickup_lib IN (XX)
274 AND pm.payment_ts BETWEEN ? AND ?
279 NULL::TIMESTAMPTZ AS last_pertinent_billing,
280 NULL::TIMESTAMPTZ AS last_pertinent_payment
281 FROM money.grocery lt
282 JOIN money.collections_tracker cl ON (lt.usr = cl.usr)
283 JOIN money.billing bl ON (lt.id = bl.xact)
284 WHERE cl.location = ?
285 AND lt.billing_location IN (XX)
286 AND bl.void_time BETWEEN ? AND ?
291 MAX(bl.billing_ts) AS last_pertinent_billing,
292 NULL::TIMESTAMPTZ AS last_pertinent_payment
293 FROM money.grocery lt
294 JOIN money.collections_tracker cl ON (lt.usr = cl.usr)
295 JOIN money.billing bl ON (lt.id = bl.xact)
296 WHERE cl.location = ?
297 AND lt.billing_location IN (XX)
298 AND bl.billing_ts BETWEEN ? AND ?
303 NULL::TIMESTAMPTZ AS last_pertinent_billing,
304 MAX(pm.payment_ts) AS last_pertinent_payment
305 FROM money.grocery lt
306 JOIN money.collections_tracker cl ON (lt.usr = cl.usr)
307 JOIN money.payment pm ON (lt.id = pm.xact)
308 WHERE cl.location = ?
309 AND lt.billing_location IN (XX)
310 AND pm.payment_ts BETWEEN ? AND ?
315 NULL::TIMESTAMPTZ AS last_pertinent_billing,
316 NULL::TIMESTAMPTZ AS last_pertinent_payment
317 FROM action.circulation lt
318 JOIN money.collections_tracker cl ON (lt.usr = cl.usr)
319 WHERE cl.location = ?
320 AND lt.circ_lib IN (XX)
321 AND lt.checkin_time BETWEEN ? AND ?
326 NULL::TIMESTAMPTZ AS last_pertinent_billing,
327 MAX(pm.payment_ts) AS last_pertinent_payment
328 FROM action.circulation lt
329 JOIN money.collections_tracker cl ON (lt.usr = cl.usr)
330 JOIN money.payment pm ON (lt.id = pm.xact)
331 WHERE cl.location = ?
332 AND lt.circ_lib IN (XX)
333 AND pm.payment_ts BETWEEN ? AND ?
338 NULL::TIMESTAMPTZ AS last_pertinent_billing,
339 NULL::TIMESTAMPTZ AS last_pertinent_payment
340 FROM action.circulation lt
341 JOIN money.collections_tracker cl ON (lt.usr = cl.usr)
342 JOIN money.billing bl ON (lt.id = bl.xact)
343 WHERE cl.location = ?
344 AND lt.circ_lib IN (XX)
345 AND bl.void_time BETWEEN ? AND ?
350 MAX(bl.billing_ts) AS last_pertinent_billing,
351 NULL::TIMESTAMPTZ AS last_pertinent_payment
352 FROM action.circulation lt
353 JOIN money.collections_tracker cl ON (lt.usr = cl.usr)
354 JOIN money.billing bl ON (lt.id = bl.xact)
355 WHERE cl.location = ?
356 AND lt.circ_lib IN (XX)
357 AND bl.billing_ts BETWEEN ? AND ?
366 my ($org) = actor::org_unit->search( shortname => uc($l) );
369 my $o_list = actor::org_unit->db_Main->selectcol_arrayref( "SELECT id FROM actor.org_unit_descendants(?);", {}, $org->id );
370 next unless (@$o_list);
372 my $o_txt = join ',' => @$o_list;
374 (my $real_sql = $SQL) =~ s/XX/$o_txt/gsm;
376 my $sth = money::collections_tracker->db_Main->prepare($real_sql);
378 # reservation queries
379 $org->id, $startdate, $enddate,
380 $org->id, $startdate, $enddate,
381 $org->id, $startdate, $enddate,
384 $org->id, $startdate, $enddate,
385 $org->id, $startdate, $enddate,
386 $org->id, $startdate, $enddate,
389 $org->id, $startdate, $enddate,
390 $org->id, $startdate, $enddate,
391 $org->id, $startdate, $enddate,
392 $org->id, $startdate, $enddate
395 while (my $row = $sth->fetchrow_hashref) {
396 $row->{usr} = actor::user->retrieve($row->{usr})->to_fieldmapper;
397 $client->respond( $row );
402 __PACKAGE__->register_method(
403 method => 'active_in_collections',
404 api_name => 'open-ils.storage.money.collections.users_with_activity',
409 sub ou_desk_payments {
413 my $startdate = shift;
416 return undef unless ($startdate =~ /^\d{4}-\d{2}-\d{2}$/o);
417 return undef unless ($enddate =~ /^\d{4}-\d{2}-\d{2}$/o);
418 return undef unless ($lib =~ /^\d+$/o);
422 SELECT ws.id as workstation,
423 SUM( CASE WHEN p.payment_type = 'cash_payment' THEN p.amount ELSE 0.0 END ) as cash_payment,
424 SUM( CASE WHEN p.payment_type = 'check_payment' THEN p.amount ELSE 0.0 END ) as check_payment,
425 SUM( CASE WHEN p.payment_type = 'credit_card_payment' THEN p.amount ELSE 0.0 END ) as credit_card_payment,
426 SUM( CASE WHEN p.payment_type = 'debit_card_payment' THEN p.amount ELSE 0.0 END ) as debit_card_payment
427 FROM money.desk_payment_view p
428 JOIN actor.workstation ws ON (ws.id = p.cash_drawer)
429 WHERE p.payment_ts >= '$startdate'
430 AND p.payment_ts < '$enddate'::TIMESTAMPTZ + INTERVAL '1 day'
431 AND p.voided IS FALSE
432 AND ws.owning_lib = $lib
438 my $rows = money::payment->db_Main->selectall_arrayref( $sql );
441 my $x = new Fieldmapper::money::workstation_payment_summary;
442 $x->workstation( actor::workstation->retrieve($$r[0])->to_fieldmapper );
443 $x->cash_payment($$r[1]);
444 $x->check_payment($$r[2]);
445 $x->credit_card_payment($$r[3]);
446 $x->debit_card_payment($$r[4]);
448 $client->respond($x);
453 __PACKAGE__->register_method(
454 method => 'ou_desk_payments',
455 api_name => 'open-ils.storage.money.org_unit.desk_payments',
460 sub ou_user_payments {
464 my $startdate = shift;
467 return undef unless ($startdate =~ /^\d{4}-\d{2}-\d{2}$/o);
468 return undef unless ($enddate =~ /^\d{4}-\d{2}-\d{2}$/o);
469 return undef unless ($lib =~ /^\d+$/o);
474 SUM( CASE WHEN p.payment_type = 'forgive_payment' THEN p.amount ELSE 0.0 END ) as forgive_payment,
475 SUM( CASE WHEN p.payment_type = 'work_payment' THEN p.amount ELSE 0.0 END ) as work_payment,
476 SUM( CASE WHEN p.payment_type = 'credit_payment' THEN p.amount ELSE 0.0 END ) as credit_payment,
477 SUM( CASE WHEN p.payment_type = 'goods_payment' THEN p.amount ELSE 0.0 END ) as goods_payment
478 FROM money.bnm_payment_view p
479 JOIN actor.usr au ON (au.id = p.accepting_usr)
480 WHERE p.payment_ts >= '$startdate'
481 AND p.payment_ts < '$enddate'::TIMESTAMPTZ + INTERVAL '1 day'
482 AND p.voided IS FALSE
483 AND au.home_ou = $lib
484 AND p.payment_type IN ('credit_payment','forgive_payment','work_payment','goods_payment')
490 my $rows = money::payment->db_Main->selectall_arrayref( $sql );
493 my $x = new Fieldmapper::money::user_payment_summary;
494 $x->usr( actor::user->retrieve($$r[0])->to_fieldmapper );
495 $x->forgive_payment($$r[1]);
496 $x->work_payment($$r[2]);
497 $x->credit_payment($$r[3]);
498 $x->goods_payment($$r[4]);
500 $client->respond($x);
505 __PACKAGE__->register_method(
506 method => 'ou_user_payments',
507 api_name => 'open-ils.storage.money.org_unit.user_payments',
512 sub mark_unrecovered {
516 my $x = money::billable_xact->retrieve($xact);
517 $x->unrecovered( 't' );
520 __PACKAGE__->register_method(
521 method => 'mark_unrecovered',
522 api_name => 'open-ils.storage.money.billable_xact.mark_unrecovered',
527 sub clear_cc_number {
530 my $payment_age = shift;
532 # using NOW() in the AGE() calculation lets us modify payments
533 # that occurred today without having to specify negative ages.
536 UPDATE money.credit_card_payment
538 WHERE AGE(NOW(), payment_ts) > ?::INTERVAL;
541 my $sth = actor::user->db_Main->prepare_cached($sql);
542 $sth->execute($payment_age);
547 __PACKAGE__->register_method(
548 api_name => 'open-ils.storage.money.clear_cc_number',
549 method => 'clear_cc_number',
552 Credit card payments store the last 4 digits of the card
553 number. This API call set the credit card number field
554 to NULL to remove this data after the payment has
555 reached the specified age.
556 @param payment_age The age as a string.