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 } );
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 = ?))";
124 MAX(bl.billing_ts) AS last_pertinent_billing,
125 SUM(bl.amount) - COALESCE(SUM((SELECT SUM(amount) FROM money.payment WHERE xact = lt.id)),0) AS threshold_amount
126 FROM ( SELECT id,usr,billing_location AS location FROM money.grocery
128 SELECT id,usr,circ_lib AS location FROM action.circulation ) AS lt
129 JOIN $descendants d ON (lt.location = d.id)
130 JOIN money.billing bl ON (lt.id = bl.xact AND bl.voided IS FALSE)
131 WHERE AGE(bl.billing_ts) > ?
135 FROM money.collections_tracker
142 AND (SUM(bl.amount) - COALESCE(SUM((SELECT SUM(amount) FROM money.payment WHERE xact = lt.id)),0)) > ?
147 my $sth = money::collections_tracker->db_Main->prepare($SQL);
148 $sth->execute(uc($l), $age, uc($l), $amount );
149 while (my $row = $sth->fetchrow_hashref) {
150 #$row->{usr} = actor::user->retrieve($row->{usr})->to_fieldmapper;
151 $client->respond( $row );
156 __PACKAGE__->register_method(
157 method => 'new_collections',
158 api_name => 'open-ils.storage.money.collections.users_of_interest',
163 sub active_in_collections {
166 my $startdate = shift;
170 my $mct = money::collections_tracker->table;
171 my $mb = money::billing->table;
172 my $circ = action::circulation->table;
173 my $mg = money::grocery->table;
174 my $descendants = "actor.org_unit_descendants((select id from actor.org_unit where shortname = ?))";
178 MAX(bl.billing_ts) AS last_pertinent_billing,
179 MAX(pm.payment_ts) AS last_pertinent_payment
180 FROM ( SELECT id,usr,billing_location AS location, 'g'::char AS x_type FROM money.grocery
182 SELECT id,usr,circ_lib AS location, 'c'::char AS x_type FROM action.circulation
184 SELECT id,usr,circ_lib AS location, 'i'::char AS x_type FROM action.circulation
185 WHERE checkin_time between ? and ? ) AS lt
186 JOIN $descendants d ON (lt.location = d.id)
187 JOIN money.collections_tracker cl ON (lt.usr = cl.usr)
188 LEFT JOIN money.billing bl ON (lt.id = bl.xact)
189 LEFT JOIN money.payment pm ON (lt.id = pm.xact)
190 WHERE bl.billing_ts between ? and ?
191 OR pm.payment_ts between ? and ?
192 OR lt.x_type = 'i'::char
198 my $sth = money::collections_tracker->db_Main->prepare($SQL);
199 $sth->execute( $startdate, $enddate, uc($l), $startdate, $enddate, $startdate, $enddate );
200 while (my $row = $sth->fetchrow_hashref) {
201 $row->{usr} = actor::user->retrieve($row->{usr})->to_fieldmapper;
202 $client->respond( $row );
207 __PACKAGE__->register_method(
208 method => 'active_in_collections',
209 api_name => 'open-ils.storage.money.collections.users_with_activity',
214 sub ou_desk_payments {
218 my $startdate = shift;
221 return undef unless ($startdate =~ /^\d{4}-\d{2}-\d{2}$/o);
222 return undef unless ($enddate =~ /^\d{4}-\d{2}-\d{2}$/o);
223 return undef unless ($lib =~ /^\d+$/o);
231 SUM(COALESCE(p.amount,0.0))
232 FROM money.desk_payment_view p
233 JOIN actor.workstation ws ON (ws.id = p.cash_drawer)
234 WHERE p.payment_ts >= '$startdate'
235 AND p.payment_ts < '$enddate'::TIMESTAMPTZ + INTERVAL '1 day'
236 AND p.voided IS FALSE
237 AND ws.owning_lib = $lib
242 cash_payment numeric(10,2),
243 check_payment numeric(10,2),
244 credit_card_payment numeric(10,2) );
248 my $rows = money::payment->db_Main->selectall_arrayref( $sql );
251 my $x = new Fieldmapper::money::workstation_payment_summary;
252 $x->workstation( actor::workstation->retrieve($$r[0])->to_fieldmapper );
253 $x->cash_payment($$r[1]);
254 $x->check_payment($$r[2]);
255 $x->credit_card_payment($$r[3]);
257 $client->respond($x);
262 __PACKAGE__->register_method(
263 method => 'ou_desk_payments',
264 api_name => 'open-ils.storage.money.org_unit.desk_payments',
269 sub ou_user_payments {
273 my $startdate = shift;
276 return undef unless ($startdate =~ /^\d{4}-\d{2}-\d{2}$/o);
277 return undef unless ($enddate =~ /^\d{4}-\d{2}-\d{2}$/o);
278 return undef unless ($lib =~ /^\d+$/o);
286 SUM(COALESCE(p.amount,0.0))
287 FROM money.bnm_payment_view p
288 JOIN actor.usr au ON (au.id = p.accepting_usr)
289 WHERE p.payment_ts >= '$startdate'
290 AND p.payment_ts < '$enddate'::TIMESTAMPTZ + INTERVAL '1 day'
291 AND p.voided IS FALSE
292 AND au.home_ou = $lib
293 AND p.payment_type IN ('credit_payment','forgive_payment','work_payment')
298 forgive_payment numeric(10,2),
299 work_payment numeric(10,2),
300 credit_payment numeric(10,2) );
304 my $rows = money::payment->db_Main->selectall_arrayref( $sql );
307 my $x = new Fieldmapper::money::user_payment_summary;
308 $x->usr( actor::user->retrieve($$r[0])->to_fieldmapper );
309 $x->forgive_payment($$r[1]);
310 $x->work_payment($$r[2]);
311 $x->credit_payment($$r[3]);
313 $client->respond($x);
318 __PACKAGE__->register_method(
319 method => 'ou_user_payments',
320 api_name => 'open-ils.storage.money.org_unit.user_payments',