LP#1394989: Do not include deleted users when retrieving for Collections
[Evergreen.git] / Open-ILS / src / perlmods / lib / OpenILS / Application / Storage / Publisher / money.pm
1 package OpenILS::Application::Storage::Publisher::money;
2 use base qw/OpenILS::Application::Storage/;
3 use OpenSRF::Utils::Logger qw/:level/;
4
5 my $log = 'OpenSRF::Utils::Logger';
6
7 sub _make_mbts {
8         my @xacts = @_;
9
10         my @mbts;
11         for my $x (@xacts) {
12                 my $s = new Fieldmapper::money::billable_transaction_summary;
13                 $s->id( $x->id );
14                 $s->usr( $x->usr );
15                 $s->xact_start( $x->xact_start );
16                 $s->xact_finish( $x->xact_finish );
17
18                 my $to = 0;
19                 my $lb = undef;
20                 for my $b ($x->billings) {
21                         next if ($b->voided);
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) {
26                                 $lb = $b->billing_ts;
27                                 $s->last_billing_note($b->note);
28                                 $s->last_billing_ts($b->billing_ts);
29                                 $s->last_billing_type($b->billing_type);
30                         }
31                 }
32
33                 $s->total_owed( sprintf('%0.2f', ($to) / 100 ) );
34
35                 my $tp = 0;
36                 my $lp = undef;
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) {
43                                 $lp = $p->payment_ts;
44                                 $s->last_payment_note($p->note);
45                                 $s->last_payment_ts($p->payment_ts);
46                                 $s->last_payment_type($p->payment_type);
47                         }
48                 }
49                 $s->total_paid( sprintf('%0.2f', ($tp) / 100 ) );
50
51                 $s->balance_owed( sprintf('%0.2f', (($to) - ($tp)) / 100) );
52         #$log->debug( "balance of ".$x->id." == ".$s->balance_owed, DEBUG );
53
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' );
60                 }
61
62                 push @mbts, $s;
63         }
64
65         return @mbts;
66 }
67
68 sub search_mbts {
69     my $self = shift;
70     my $client = shift;
71     my $search = shift;
72
73     my @xacts = money::billable_transaction->search_where( $search );
74     $client->respond( $_ ) for (_make_mbts(@xacts));
75
76     return undef;
77 }
78 __PACKAGE__->register_method(
79     method      => 'search_mbts',
80     api_name    => 'open-ils.storage.money.billable_transaction.summary.search',
81     stream      => 1,
82     argc        => 1,
83 );
84
85 sub search_ous {
86     my $self = shift;
87     my $client = shift;
88     my $usr = shift;
89
90     my @xacts = $self->method_lookup( 'open-ils.storage.money.billable_transaction.summary.search' )->run( { usr => $usr, xact_finish => undef } );
91
92     my ($total,$owed,$paid) = (0.0,0.0,0.0);
93     for my $x (@xacts) {
94         $total += $x->total_owed;
95         $owed += $x->balance_owed;
96         $paid += $x->total_paid;
97     }
98
99     my $ous = Fieldmapper::money::open_user_summary->new;
100     $ous->usr( $usr );
101     $ous->total_paid( sprintf('%0.2f', $paid) );
102     $ous->total_owed( sprintf('%0.2f', $total) );
103     $ous->balance_owed( sprintf('%0.2f', $owed) );
104
105     return $ous;
106 }
107 __PACKAGE__->register_method(
108     method      => 'search_ous',
109     api_name    => 'open-ils.storage.money.open_user_summary.search',
110     argc        => 1,
111 );
112
113
114 sub new_collections {
115     my $self = shift;
116     my $client = shift;
117     my $age = shift;
118     my $amount = shift;
119     my @loc = @_;
120
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;
126
127     my $SQL = <<"    SQL";
128
129 select
130         usr,
131         MAX(last_billing) as last_pertinent_billing,
132         SUM(total_billing) - SUM(COALESCE(p.amount,0)) as threshold_amount
133   from  (select
134                 x.id,
135                 x.usr,
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
143                 and c.id is null
144                 and x.circ_lib in (XX)
145                 and b.billing_ts < current_timestamp - ? * '1 day'::interval
146                 and not b.voided
147                 and set.id IS NULL
148           group by 1,2
149
150                   union all
151
152          select
153                 x.id,
154                 x.usr,
155                 MAX(b.billing_ts) as last_billing,
156                 SUM(b.amount) AS total_billing
157           from  money.grocery x
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
162                 and c.id is null
163                 and x.billing_location in (XX)
164                 and b.billing_ts < current_timestamp - ? * '1 day'::interval
165                 and not b.voided
166                 and set.id IS NULL
167           group by 1,2
168
169                   union all
170
171          select
172                 x.id,
173                 x.usr,
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
181                 and c.id is null
182                 and x.pickup_lib in (XX)
183                 and b.billing_ts < current_timestamp - ? * '1 day'::interval
184                 and not b.voided
185                 and set.id IS NULL
186           group by 1,2
187         ) full_list
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
191   group by 1
192   having SUM(total_billing) - SUM(COALESCE(p.amount,0)) > ?
193 ;
194     SQL
195
196     my @l_ids;
197     for my $l (@loc) {
198         my ($org) = actor::org_unit->search( shortname => uc($l) );
199         next unless $org;
200
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);
203
204         my $o_txt = join ',' => @$o_list;
205
206         (my $real_sql = $SQL) =~ s/XX/$o_txt/gsm;
207
208         my $sth = money::collections_tracker->db_Main->prepare($real_sql);
209         $sth->execute( $org->id, $age, $org->id, $age, $org->id, $age, $amount );
210
211         while (my $row = $sth->fetchrow_hashref) {
212             #$row->{usr} = actor::user->retrieve($row->{usr})->to_fieldmapper;
213             $client->respond( $row );
214         }
215     }
216     return undef;
217 }
218 __PACKAGE__->register_method(
219     method      => 'new_collections',
220     api_name    => 'open-ils.storage.money.collections.users_of_interest',
221     stream      => 1,
222     argc        => 3,
223 );
224
225 sub active_in_collections {
226     my $self = shift;
227     my $client = shift;
228     my $startdate = shift;
229     my $enddate = shift;
230     my @loc = @_;
231
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;
236
237     my $SQL = <<"    SQL";
238 SELECT  usr,
239         MAX(last_pertinent_billing) AS last_pertinent_billing,
240         MAX(last_pertinent_payment) AS last_pertinent_payment
241   FROM  (
242                 SELECT  lt.usr,
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 ?
251                   GROUP BY 1
252
253                                 UNION ALL
254                 SELECT  lt.usr,
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 ?
263                   GROUP BY 1
264
265                                 UNION ALL
266                 SELECT  lt.usr,
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 ?
275                   GROUP BY 1
276
277                                 UNION ALL
278                  SELECT  lt.usr,
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 ?
287                   GROUP BY 1
288
289                                 UNION ALL
290                 SELECT  lt.usr,
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 ?
299                   GROUP BY 1
300
301                                 UNION ALL
302                 SELECT  lt.usr,
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 ?
311                   GROUP BY 1
312
313                                 UNION ALL
314                 SELECT  lt.usr,
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 ?
322                   GROUP BY 1
323
324                                 UNION ALL
325                 SELECT  lt.usr,
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 ?
334                   GROUP BY 1
335
336                                 UNION ALL
337                 SELECT  lt.usr,
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 ?
346                   GROUP BY 1
347
348                                 UNION ALL
349                 SELECT  lt.usr,
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 ?
358                   GROUP BY 1
359         ) foo
360   GROUP BY 1
361 ;
362     SQL
363
364     my @l_ids;
365     for my $l (@loc) {
366         my ($org) = actor::org_unit->search( shortname => uc($l) );
367         next unless $org;
368
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);
371
372         my $o_txt = join ',' => @$o_list;
373
374         (my $real_sql = $SQL) =~ s/XX/$o_txt/gsm;
375
376         my $sth = money::collections_tracker->db_Main->prepare($real_sql);
377         $sth->execute(
378             # reservation queries
379             $org->id, $startdate, $enddate,
380             $org->id, $startdate, $enddate,
381             $org->id, $startdate, $enddate,
382
383             # grocery queries
384             $org->id, $startdate, $enddate,
385             $org->id, $startdate, $enddate,
386             $org->id, $startdate, $enddate,
387
388             # circ queries
389             $org->id, $startdate, $enddate,
390             $org->id, $startdate, $enddate,
391             $org->id, $startdate, $enddate,
392             $org->id, $startdate, $enddate
393         );
394
395         while (my $row = $sth->fetchrow_hashref) {
396             $row->{usr} = actor::user->retrieve($row->{usr})->to_fieldmapper;
397             $client->respond( $row );
398         }
399     }
400     return undef;
401 }
402 __PACKAGE__->register_method(
403     method      => 'active_in_collections',
404     api_name    => 'open-ils.storage.money.collections.users_with_activity',
405     stream      => 1,
406     argc        => 3,
407 );
408
409 sub ou_desk_payments {
410     my $self = shift;
411     my $client = shift;
412     my $lib = shift;
413     my $startdate = shift;
414     my $enddate = shift;
415
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);
419
420     my $sql = <<"    SQL";
421
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
432      GROUP BY 1
433      ORDER BY 1;
434
435     SQL
436
437     my $rows = money::payment->db_Main->selectall_arrayref( $sql );
438
439     for my $r (@$rows) {
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]);
445
446         $client->respond($x);
447     }
448
449     return undef;
450 }
451 __PACKAGE__->register_method(
452     method      => 'ou_desk_payments',
453     api_name    => 'open-ils.storage.money.org_unit.desk_payments',
454     stream      => 1,
455     argc        => 3,
456 );
457
458 sub ou_user_payments {
459     my $self = shift;
460     my $client = shift;
461     my $lib = shift;
462     my $startdate = shift;
463     my $enddate = shift;
464
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);
468
469     my $sql = <<"    SQL";
470
471         SELECT  au.id as usr,
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')
483          GROUP BY 1
484          ORDER BY 1;
485
486     SQL
487
488     my $rows = money::payment->db_Main->selectall_arrayref( $sql );
489
490     for my $r (@$rows) {
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]);
497
498         $client->respond($x);
499     }
500
501     return undef;
502 }
503 __PACKAGE__->register_method(
504     method      => 'ou_user_payments',
505     api_name    => 'open-ils.storage.money.org_unit.user_payments',
506     stream      => 1,
507     argc        => 3,
508 );
509
510 sub mark_unrecovered {
511     my $self = shift;
512     my $xact = shift;
513
514     my $x = money::billable_xact->retrieve($xact);
515     $x->unrecovered( 't' );
516     return $x->update;
517 }
518 __PACKAGE__->register_method(
519     method      => 'mark_unrecovered',
520     api_name    => 'open-ils.storage.money.billable_xact.mark_unrecovered',
521     argc        => 1,
522 );
523
524
525 1;