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 $s->xact_type( 'grocery' ) if (money::grocery->retrieve($x->id));
55 $s->xact_type( 'circulation' ) if (action::circulation->retrieve($x->id));
68 my @xacts = money::billable_transaction->search_where( $search );
69 $client->respond( $_ ) for (_make_mbts(@xacts));
73 __PACKAGE__->register_method(
74 method => 'search_mbts',
75 api_name => 'open-ils.storage.money.billable_transaction.summary.search',
85 my @xacts = $self->method_lookup( 'open-ils.storage.money.billable_transaction.summary.search' )->run( { usr => $usr, xact_finish => undef } );
87 my ($total,$owed,$paid) = (0.0,0.0,0.0);
89 $total += $x->total_owed;
90 $owed += $x->balance_owed;
91 $paid += $x->total_paid;
94 my $ous = Fieldmapper::money::open_user_summary->new;
96 $ous->total_paid( sprintf('%0.2f', $paid) );
97 $ous->total_owed( sprintf('%0.2f', $total) );
98 $ous->balance_owed( sprintf('%0.2f', $owed) );
102 __PACKAGE__->register_method(
103 method => 'search_ous',
104 api_name => 'open-ils.storage.money.open_user_summary.search',
109 sub new_collections {
116 my $mct = money::collections_tracker->table;
117 my $mb = money::billing->table;
118 my $circ = action::circulation->table;
119 my $mg = money::grocery->table;
120 my $descendants = "actor.org_unit_descendants((select id from actor.org_unit where shortname = ?))";
126 MAX(last_billing) as last_pertinent_billing,
127 SUM(total_billing) - SUM(COALESCE(p.amount,0)) as threshold_amount
131 MAX(b.billing_ts) as last_billing,
132 SUM(b.amount) AS total_billing
133 from action.circulation x
134 left join money.collections_tracker c ON (c.usr = x.usr AND c.location = ?)
135 join money.billing b on (b.xact = x.id)
136 where x.xact_finish is null
138 and x.circ_lib in (XX)
139 and b.billing_ts < current_timestamp - ? * '1 day'::interval
148 MAX(b.billing_ts) as last_billing,
149 SUM(b.amount) AS total_billing
151 left join money.collections_tracker c ON (c.usr = x.usr AND c.location = ?)
152 join money.billing b on (b.xact = x.id)
153 where x.xact_finish is null
155 and x.billing_location in (XX)
156 and b.billing_ts < current_timestamp - ? * '1 day'::interval
160 left join money.payment p on (full_list.id = p.xact)
162 having SUM(total_billing) - SUM(COALESCE(p.amount,0)) > ?
168 my ($org) = actor::org_unit->search( shortname => uc($l) );
171 my $o_list = actor::org_unit->db_Main->selectcol_arrayref( "SELECT id FROM actor.org_unit_descendants(?);", {}, $org->id );
172 next unless (@$o_list);
174 my $o_txt = join ',' => @$o_list;
176 (my $real_sql = $SQL) =~ s/XX/$o_txt/gsm;
178 my $sth = money::collections_tracker->db_Main->prepare($real_sql);
179 $sth->execute( $org->id, $age, $org->id, $age, $amount );
181 while (my $row = $sth->fetchrow_hashref) {
182 #$row->{usr} = actor::user->retrieve($row->{usr})->to_fieldmapper;
183 $client->respond( $row );
188 __PACKAGE__->register_method(
189 method => 'new_collections',
190 api_name => 'open-ils.storage.money.collections.users_of_interest',
195 sub active_in_collections {
198 my $startdate = shift;
202 my $mct = money::collections_tracker->table;
203 my $mb = money::billing->table;
204 my $circ = action::circulation->table;
205 my $mg = money::grocery->table;
209 MAX(last_pertinent_billing) AS last_pertinent_billing,
210 MAX(last_pertinent_payment) AS last_pertinent_payment
213 MAX(bl.billing_ts) AS last_pertinent_billing,
214 NULL::TIMESTAMPTZ AS last_pertinent_payment
215 FROM money.grocery lt
216 JOIN money.collections_tracker cl ON (lt.usr = cl.usr)
217 JOIN money.billing bl ON (lt.id = bl.xact)
218 WHERE cl.location = ?
219 AND lt.billing_location IN (XX)
220 AND bl.billing_ts BETWEEN ? AND ?
225 NULL::TIMESTAMPTZ AS last_pertinent_billing,
226 MAX(pm.payment_ts) AS last_pertinent_payment
227 FROM money.grocery lt
228 JOIN money.collections_tracker cl ON (lt.usr = cl.usr)
229 JOIN money.payment pm ON (lt.id = pm.xact)
230 WHERE cl.location = ?
231 AND lt.billing_location IN (XX)
232 AND pm.payment_ts BETWEEN ? AND ?
237 NULL::TIMESTAMPTZ AS last_pertinent_billing,
238 NULL::TIMESTAMPTZ AS last_pertinent_payment
239 FROM action.circulation lt
240 JOIN money.collections_tracker cl ON (lt.usr = cl.usr)
241 WHERE cl.location = ?
242 AND lt.circ_lib IN (XX)
243 AND lt.checkin_time BETWEEN ? AND ?
248 NULL::TIMESTAMPTZ AS last_pertinent_billing,
249 MAX(pm.payment_ts) AS last_pertinent_payment
250 FROM action.circulation lt
251 JOIN money.collections_tracker cl ON (lt.usr = cl.usr)
252 JOIN money.payment pm ON (lt.id = pm.xact)
253 WHERE cl.location = ?
254 AND lt.circ_lib IN (XX)
255 AND pm.payment_ts BETWEEN ? AND ?
260 MAX(bl.billing_ts) AS last_pertinent_billing,
261 NULL::TIMESTAMPTZ AS last_pertinent_payment
262 FROM action.circulation lt
263 JOIN money.collections_tracker cl ON (lt.usr = cl.usr)
264 JOIN money.billing bl ON (lt.id = bl.xact)
265 WHERE cl.location = ?
266 AND lt.circ_lib IN (XX)
267 AND bl.billing_ts BETWEEN ? AND ?
276 my ($org) = actor::org_unit->search( shortname => uc($l) );
279 my $o_list = actor::org_unit->db_Main->selectcol_arrayref( "SELECT id FROM actor.org_unit_descendants(?);", {}, $org->id );
280 next unless (@$o_list);
282 my $o_txt = join ',' => @$o_list;
284 (my $real_sql = $SQL) =~ s/XX/$o_txt/gsm;
286 my $sth = money::collections_tracker->db_Main->prepare($real_sql);
288 $org->id, $startdate, $enddate,
289 $org->id, $startdate, $enddate,
290 $org->id, $startdate, $enddate,
291 $org->id, $startdate, $enddate,
292 $org->id, $startdate, $enddate
295 while (my $row = $sth->fetchrow_hashref) {
296 $row->{usr} = actor::user->retrieve($row->{usr})->to_fieldmapper;
297 $client->respond( $row );
302 __PACKAGE__->register_method(
303 method => 'active_in_collections',
304 api_name => 'open-ils.storage.money.collections.users_with_activity',
309 sub ou_desk_payments {
313 my $startdate = shift;
316 return undef unless ($startdate =~ /^\d{4}-\d{2}-\d{2}$/o);
317 return undef unless ($enddate =~ /^\d{4}-\d{2}-\d{2}$/o);
318 return undef unless ($lib =~ /^\d+$/o);
322 SELECT ws.id as workstation,
323 SUM( CASE WHEN p.payment_type = 'cash_payment' THEN p.amount ELSE 0.0 END ) as cash_payment,
324 SUM( CASE WHEN p.payment_type = 'check_payment' THEN p.amount ELSE 0.0 END ) as check_payment,
325 SUM( CASE WHEN p.payment_type = 'credit_card_payment' THEN p.amount ELSE 0.0 END ) as credit_card_payment
326 FROM money.desk_payment_view p
327 JOIN actor.workstation ws ON (ws.id = p.cash_drawer)
328 WHERE p.payment_ts >= '$startdate'
329 AND p.payment_ts < '$enddate'::TIMESTAMPTZ + INTERVAL '1 day'
330 AND p.voided IS FALSE
331 AND ws.owning_lib = $lib
337 my $rows = money::payment->db_Main->selectall_arrayref( $sql );
340 my $x = new Fieldmapper::money::workstation_payment_summary;
341 $x->workstation( actor::workstation->retrieve($$r[0])->to_fieldmapper );
342 $x->cash_payment($$r[1]);
343 $x->check_payment($$r[2]);
344 $x->credit_card_payment($$r[3]);
346 $client->respond($x);
351 __PACKAGE__->register_method(
352 method => 'ou_desk_payments',
353 api_name => 'open-ils.storage.money.org_unit.desk_payments',
358 sub ou_user_payments {
362 my $startdate = shift;
365 return undef unless ($startdate =~ /^\d{4}-\d{2}-\d{2}$/o);
366 return undef unless ($enddate =~ /^\d{4}-\d{2}-\d{2}$/o);
367 return undef unless ($lib =~ /^\d+$/o);
372 SUM( CASE WHEN p.payment_type = 'forgive_payment' THEN p.amount ELSE 0.0 END ) as forgive_payment,
373 SUM( CASE WHEN p.payment_type = 'work_payment' THEN p.amount ELSE 0.0 END ) as work_payment,
374 SUM( CASE WHEN p.payment_type = 'credit_payment' THEN p.amount ELSE 0.0 END ) as credit_payment
375 FROM money.bnm_payment_view p
376 JOIN actor.usr au ON (au.id = p.accepting_usr)
377 WHERE p.payment_ts >= '$startdate'
378 AND p.payment_ts < '$enddate'::TIMESTAMPTZ + INTERVAL '1 day'
379 AND p.voided IS FALSE
380 AND au.home_ou = $lib
381 AND p.payment_type IN ('credit_payment','forgive_payment','work_payment')
387 my $rows = money::payment->db_Main->selectall_arrayref( $sql );
390 my $x = new Fieldmapper::money::user_payment_summary;
391 $x->usr( actor::user->retrieve($$r[0])->to_fieldmapper );
392 $x->forgive_payment($$r[1]);
393 $x->work_payment($$r[2]);
394 $x->credit_payment($$r[3]);
396 $client->respond($x);
401 __PACKAGE__->register_method(
402 method => 'ou_user_payments',
403 api_name => 'open-ils.storage.money.org_unit.user_payments',