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',