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)
190 having SUM(total_billing) - SUM(COALESCE(p.amount,0)) > ?
196 my ($org) = actor::org_unit->search( shortname => uc($l) );
199 my $o_list = actor::org_unit->db_Main->selectcol_arrayref( "SELECT id FROM actor.org_unit_descendants(?);", {}, $org->id );
200 next unless (@$o_list);
202 my $o_txt = join ',' => @$o_list;
204 (my $real_sql = $SQL) =~ s/XX/$o_txt/gsm;
206 my $sth = money::collections_tracker->db_Main->prepare($real_sql);
207 $sth->execute( $org->id, $age, $org->id, $age, $org->id, $age, $amount );
209 while (my $row = $sth->fetchrow_hashref) {
210 #$row->{usr} = actor::user->retrieve($row->{usr})->to_fieldmapper;
211 $client->respond( $row );
216 __PACKAGE__->register_method(
217 method => 'new_collections',
218 api_name => 'open-ils.storage.money.collections.users_of_interest',
223 sub active_in_collections {
226 my $startdate = shift;
230 my $mct = money::collections_tracker->table;
231 my $mb = money::billing->table;
232 my $circ = action::circulation->table;
233 my $mg = money::grocery->table;
237 MAX(last_pertinent_billing) AS last_pertinent_billing,
238 MAX(last_pertinent_payment) AS last_pertinent_payment
241 NULL::TIMESTAMPTZ AS last_pertinent_billing,
242 NULL::TIMESTAMPTZ AS last_pertinent_payment
243 FROM booking.reservation lt
244 JOIN money.collections_tracker cl ON (lt.usr = cl.usr)
245 JOIN money.billing bl ON (lt.id = bl.xact)
246 WHERE cl.location = ?
247 AND lt.pickup_lib IN (XX)
248 AND bl.void_time BETWEEN ? AND ?
253 MAX(bl.billing_ts) AS last_pertinent_billing,
254 NULL::TIMESTAMPTZ AS last_pertinent_payment
255 FROM booking.reservation lt
256 JOIN money.collections_tracker cl ON (lt.usr = cl.usr)
257 JOIN money.billing bl ON (lt.id = bl.xact)
258 WHERE cl.location = ?
259 AND lt.pickup_lib IN (XX)
260 AND bl.billing_ts BETWEEN ? AND ?
265 NULL::TIMESTAMPTZ AS last_pertinent_billing,
266 MAX(pm.payment_ts) AS last_pertinent_payment
267 FROM booking.reservation lt
268 JOIN money.collections_tracker cl ON (lt.usr = cl.usr)
269 JOIN money.payment pm ON (lt.id = pm.xact)
270 WHERE cl.location = ?
271 AND lt.pickup_lib IN (XX)
272 AND pm.payment_ts BETWEEN ? AND ?
277 NULL::TIMESTAMPTZ AS last_pertinent_billing,
278 NULL::TIMESTAMPTZ AS last_pertinent_payment
279 FROM money.grocery lt
280 JOIN money.collections_tracker cl ON (lt.usr = cl.usr)
281 JOIN money.billing bl ON (lt.id = bl.xact)
282 WHERE cl.location = ?
283 AND lt.billing_location IN (XX)
284 AND bl.void_time BETWEEN ? AND ?
289 MAX(bl.billing_ts) AS last_pertinent_billing,
290 NULL::TIMESTAMPTZ AS last_pertinent_payment
291 FROM money.grocery lt
292 JOIN money.collections_tracker cl ON (lt.usr = cl.usr)
293 JOIN money.billing bl ON (lt.id = bl.xact)
294 WHERE cl.location = ?
295 AND lt.billing_location IN (XX)
296 AND bl.billing_ts BETWEEN ? AND ?
301 NULL::TIMESTAMPTZ AS last_pertinent_billing,
302 MAX(pm.payment_ts) AS last_pertinent_payment
303 FROM money.grocery lt
304 JOIN money.collections_tracker cl ON (lt.usr = cl.usr)
305 JOIN money.payment pm ON (lt.id = pm.xact)
306 WHERE cl.location = ?
307 AND lt.billing_location IN (XX)
308 AND pm.payment_ts BETWEEN ? AND ?
313 NULL::TIMESTAMPTZ AS last_pertinent_billing,
314 NULL::TIMESTAMPTZ AS last_pertinent_payment
315 FROM action.circulation lt
316 JOIN money.collections_tracker cl ON (lt.usr = cl.usr)
317 WHERE cl.location = ?
318 AND lt.circ_lib IN (XX)
319 AND lt.checkin_time BETWEEN ? AND ?
324 NULL::TIMESTAMPTZ AS last_pertinent_billing,
325 MAX(pm.payment_ts) AS last_pertinent_payment
326 FROM action.circulation lt
327 JOIN money.collections_tracker cl ON (lt.usr = cl.usr)
328 JOIN money.payment pm ON (lt.id = pm.xact)
329 WHERE cl.location = ?
330 AND lt.circ_lib IN (XX)
331 AND pm.payment_ts BETWEEN ? AND ?
336 NULL::TIMESTAMPTZ AS last_pertinent_billing,
337 NULL::TIMESTAMPTZ AS last_pertinent_payment
338 FROM action.circulation 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.circ_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 action.circulation 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.circ_lib IN (XX)
355 AND bl.billing_ts BETWEEN ? AND ?
364 my ($org) = actor::org_unit->search( shortname => uc($l) );
367 my $o_list = actor::org_unit->db_Main->selectcol_arrayref( "SELECT id FROM actor.org_unit_descendants(?);", {}, $org->id );
368 next unless (@$o_list);
370 my $o_txt = join ',' => @$o_list;
372 (my $real_sql = $SQL) =~ s/XX/$o_txt/gsm;
374 my $sth = money::collections_tracker->db_Main->prepare($real_sql);
376 # reservation queries
377 $org->id, $startdate, $enddate,
378 $org->id, $startdate, $enddate,
379 $org->id, $startdate, $enddate,
382 $org->id, $startdate, $enddate,
383 $org->id, $startdate, $enddate,
384 $org->id, $startdate, $enddate,
387 $org->id, $startdate, $enddate,
388 $org->id, $startdate, $enddate,
389 $org->id, $startdate, $enddate,
390 $org->id, $startdate, $enddate
393 while (my $row = $sth->fetchrow_hashref) {
394 $row->{usr} = actor::user->retrieve($row->{usr})->to_fieldmapper;
395 $client->respond( $row );
400 __PACKAGE__->register_method(
401 method => 'active_in_collections',
402 api_name => 'open-ils.storage.money.collections.users_with_activity',
407 sub ou_desk_payments {
411 my $startdate = shift;
414 return undef unless ($startdate =~ /^\d{4}-\d{2}-\d{2}$/o);
415 return undef unless ($enddate =~ /^\d{4}-\d{2}-\d{2}$/o);
416 return undef unless ($lib =~ /^\d+$/o);
420 SELECT ws.id as workstation,
421 SUM( CASE WHEN p.payment_type = 'cash_payment' THEN p.amount ELSE 0.0 END ) as cash_payment,
422 SUM( CASE WHEN p.payment_type = 'check_payment' THEN p.amount ELSE 0.0 END ) as check_payment,
423 SUM( CASE WHEN p.payment_type = 'credit_card_payment' THEN p.amount ELSE 0.0 END ) as credit_card_payment
424 FROM money.desk_payment_view p
425 JOIN actor.workstation ws ON (ws.id = p.cash_drawer)
426 WHERE p.payment_ts >= '$startdate'
427 AND p.payment_ts < '$enddate'::TIMESTAMPTZ + INTERVAL '1 day'
428 AND p.voided IS FALSE
429 AND ws.owning_lib = $lib
435 my $rows = money::payment->db_Main->selectall_arrayref( $sql );
438 my $x = new Fieldmapper::money::workstation_payment_summary;
439 $x->workstation( actor::workstation->retrieve($$r[0])->to_fieldmapper );
440 $x->cash_payment($$r[1]);
441 $x->check_payment($$r[2]);
442 $x->credit_card_payment($$r[3]);
444 $client->respond($x);
449 __PACKAGE__->register_method(
450 method => 'ou_desk_payments',
451 api_name => 'open-ils.storage.money.org_unit.desk_payments',
456 sub ou_user_payments {
460 my $startdate = shift;
463 return undef unless ($startdate =~ /^\d{4}-\d{2}-\d{2}$/o);
464 return undef unless ($enddate =~ /^\d{4}-\d{2}-\d{2}$/o);
465 return undef unless ($lib =~ /^\d+$/o);
470 SUM( CASE WHEN p.payment_type = 'forgive_payment' THEN p.amount ELSE 0.0 END ) as forgive_payment,
471 SUM( CASE WHEN p.payment_type = 'work_payment' THEN p.amount ELSE 0.0 END ) as work_payment,
472 SUM( CASE WHEN p.payment_type = 'credit_payment' THEN p.amount ELSE 0.0 END ) as credit_payment,
473 SUM( CASE WHEN p.payment_type = 'goods_payment' THEN p.amount ELSE 0.0 END ) as goods_payment
474 FROM money.bnm_payment_view p
475 JOIN actor.usr au ON (au.id = p.accepting_usr)
476 WHERE p.payment_ts >= '$startdate'
477 AND p.payment_ts < '$enddate'::TIMESTAMPTZ + INTERVAL '1 day'
478 AND p.voided IS FALSE
479 AND au.home_ou = $lib
480 AND p.payment_type IN ('credit_payment','forgive_payment','work_payment','goods_payment')
486 my $rows = money::payment->db_Main->selectall_arrayref( $sql );
489 my $x = new Fieldmapper::money::user_payment_summary;
490 $x->usr( actor::user->retrieve($$r[0])->to_fieldmapper );
491 $x->forgive_payment($$r[1]);
492 $x->work_payment($$r[2]);
493 $x->credit_payment($$r[3]);
494 $x->goods_payment($$r[4]);
496 $client->respond($x);
501 __PACKAGE__->register_method(
502 method => 'ou_user_payments',
503 api_name => 'open-ils.storage.money.org_unit.user_payments',
508 sub mark_unrecovered {
512 my $x = money::billable_xact->retrieve($xact);
513 $x->unrecovered( 't' );
516 __PACKAGE__->register_method(
517 method => 'mark_unrecovered',
518 api_name => 'open-ils.storage.money.billable_xact.mark_unrecovered',