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 FROM money.desk_payment_view p
427 JOIN actor.workstation ws ON (ws.id = p.cash_drawer)
428 WHERE p.payment_ts >= '$startdate'
429 AND p.payment_ts < '$enddate'::TIMESTAMPTZ + INTERVAL '1 day'
430 AND p.voided IS FALSE
431 AND ws.owning_lib = $lib
437 my $rows = money::payment->db_Main->selectall_arrayref( $sql );
440 my $x = new Fieldmapper::money::workstation_payment_summary;
441 $x->workstation( actor::workstation->retrieve($$r[0])->to_fieldmapper );
442 $x->cash_payment($$r[1]);
443 $x->check_payment($$r[2]);
444 $x->credit_card_payment($$r[3]);
446 $client->respond($x);
451 __PACKAGE__->register_method(
452 method => 'ou_desk_payments',
453 api_name => 'open-ils.storage.money.org_unit.desk_payments',
458 sub ou_user_payments {
462 my $startdate = shift;
465 return undef unless ($startdate =~ /^\d{4}-\d{2}-\d{2}$/o);
466 return undef unless ($enddate =~ /^\d{4}-\d{2}-\d{2}$/o);
467 return undef unless ($lib =~ /^\d+$/o);
472 SUM( CASE WHEN p.payment_type = 'forgive_payment' THEN p.amount ELSE 0.0 END ) as forgive_payment,
473 SUM( CASE WHEN p.payment_type = 'work_payment' THEN p.amount ELSE 0.0 END ) as work_payment,
474 SUM( CASE WHEN p.payment_type = 'credit_payment' THEN p.amount ELSE 0.0 END ) as credit_payment,
475 SUM( CASE WHEN p.payment_type = 'goods_payment' THEN p.amount ELSE 0.0 END ) as goods_payment
476 FROM money.bnm_payment_view p
477 JOIN actor.usr au ON (au.id = p.accepting_usr)
478 WHERE p.payment_ts >= '$startdate'
479 AND p.payment_ts < '$enddate'::TIMESTAMPTZ + INTERVAL '1 day'
480 AND p.voided IS FALSE
481 AND au.home_ou = $lib
482 AND p.payment_type IN ('credit_payment','forgive_payment','work_payment','goods_payment')
488 my $rows = money::payment->db_Main->selectall_arrayref( $sql );
491 my $x = new Fieldmapper::money::user_payment_summary;
492 $x->usr( actor::user->retrieve($$r[0])->to_fieldmapper );
493 $x->forgive_payment($$r[1]);
494 $x->work_payment($$r[2]);
495 $x->credit_payment($$r[3]);
496 $x->goods_payment($$r[4]);
498 $client->respond($x);
503 __PACKAGE__->register_method(
504 method => 'ou_user_payments',
505 api_name => 'open-ils.storage.money.org_unit.user_payments',
510 sub mark_unrecovered {
514 my $x = money::billable_xact->retrieve($xact);
515 $x->unrecovered( 't' );
518 __PACKAGE__->register_method(
519 method => 'mark_unrecovered',
520 api_name => 'open-ils.storage.money.billable_xact.mark_unrecovered',
525 sub clear_cc_number {
528 my $payment_age = shift;
530 # using NOW() in the AGE() calculation lets us modify payments
531 # that occurred today without having to specify negative ages.
534 UPDATE money.credit_card_payment
536 WHERE AGE(NOW(), payment_ts) > ?::INTERVAL;
539 my $sth = actor::user->db_Main->prepare_cached($sql);
540 $sth->execute($payment_age);
545 __PACKAGE__->register_method(
546 api_name => 'open-ils.storage.money.clear_cc_number',
547 method => 'clear_cc_number',
550 Credit card payments store the last 4 digits of the card
551 number. This API call set the credit card number field
552 to NULL to remove this data after the payment has
553 reached the specified age.
554 @param payment_age The age as a string.