finishing up mbts thingy
[Evergreen.git] / Open-ILS / src / perlmods / 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                         $to += int($b->amount * 100);
23                         $lb ||= $b->billing_ts;
24                         if ($b->billing_ts ge $lb) {
25                                 $lb = $b->billing_ts;
26                                 $s->last_billing_note($b->note);
27                                 $s->last_billing_ts($b->billing_ts);
28                                 $s->last_billing_type($b->billing_type);
29                         }
30                 }
31
32                 $s->total_owed( sprintf('%0.2f', $to / 100 ) );
33
34                 my $tp = 0;
35                 my $lp = undef;
36                 for my $p ($x->payments) {
37                         next if ($p->voided);
38                         $tp += int($p->amount * 100);
39                         $lp ||= $p->payment_ts;
40                         if ($p->payment_ts ge $lp) {
41                                 $lp = $p->payment_ts;
42                                 $s->last_payment_note($p->note);
43                                 $s->last_payment_ts($p->payment_ts);
44                                 $s->last_payment_type($p->payment_type);
45                         }
46                 }
47                 $s->total_paid( sprintf('%0.2f', $tp / 100 ) );
48
49                 $s->balance_owed( sprintf('%0.2f', int($to - $tp) / 100) );
50
51                 $s->xact_type( 'grocery' ) if (money::grocery->retrieve($x->id));
52                 $s->xact_type( 'circulation' ) if (action::circulation->retrieve($x->id));
53
54                 push @mbts, $s;
55         }
56
57         return @mbts;
58 }
59
60 sub search_mbts {
61         my $self = shift;
62         my $client = shift;
63         my $search = shift;
64
65         my @xacts = money::billable_transaction->search_where( $search );
66         $client->respond( $_ ) for (_make_mbts(@xacts));
67
68         return undef;
69 }
70 __PACKAGE__->register_method(
71         method          => 'search_mbts',
72         api_name        => 'open-ils.storage.money.billable_transaction.summary.search',
73         stream          => 1,
74         argc            => 1,
75 );
76
77
78 sub new_collections {
79         my $self = shift;
80         my $client = shift;
81         my $age = shift;
82         my $amount = shift;
83         my @loc = @_;
84
85         my $mct = money::collections_tracker->table;
86         my $mb = money::billing->table;
87         my $circ = action::circulation->table;
88         my $mg = money::grocery->table;
89         my $descendants = "actor.org_unit_descendants((select id from actor.org_unit where shortname = ?))";
90
91         my $SQL = <<"   SQL";
92                 SELECT  lt.usr,
93                         MAX(bl.billing_ts) AS last_pertinent_billing,
94                         SUM(bl.amount) - COALESCE(SUM((SELECT SUM(amount) FROM money.payment WHERE xact = lt.id)),0) AS threshold_amount
95                   FROM  ( SELECT id,usr,billing_location AS location FROM money.grocery
96                                 UNION ALL
97                           SELECT id,usr,circ_lib AS location FROM action.circulation ) AS lt
98                         JOIN $descendants d ON (lt.location = d.id)
99                         JOIN money.billing bl ON (lt.id = bl.xact AND bl.voided IS FALSE)
100                   WHERE AGE(bl.billing_ts) > ?
101                   GROUP BY lt.usr
102                   HAVING  SUM(
103                                 (SELECT COUNT(*)
104                                   FROM  money.collections_tracker
105                                   WHERE usr = lt.usr
106                                         AND location in (
107                                                 (SELECT id
108                                                   FROM  $descendants )
109                                         )
110                                 ) ) = 0
111                         AND (SUM(bl.amount) - COALESCE(SUM((SELECT SUM(amount) FROM money.payment WHERE xact = lt.id)),0)) > ? 
112         SQL
113
114         my @l_ids;
115         for my $l (@loc) {
116                 my $sth = money::collections_tracker->db_Main->prepare($SQL);
117                 $sth->execute(uc($l), $age, uc($l), $amount );
118                 while (my $row = $sth->fetchrow_hashref) {
119                         #$row->{usr} = actor::user->retrieve($row->{usr})->to_fieldmapper;
120                         $client->respond( $row );
121                 }
122         }
123         return undef;
124 }
125 __PACKAGE__->register_method(
126         method          => 'new_collections',
127         api_name        => 'open-ils.storage.money.collections.users_of_interest',
128         stream          => 1,
129         argc            => 3,
130 );
131
132 sub active_in_collections {
133         my $self = shift;
134         my $client = shift;
135         my $startdate = shift;
136         my $enddate = shift;
137         my @loc = @_;
138
139         my $mct = money::collections_tracker->table;
140         my $mb = money::billing->table;
141         my $circ = action::circulation->table;
142         my $mg = money::grocery->table;
143         my $descendants = "actor.org_unit_descendants((select id from actor.org_unit where shortname = ?))";
144
145         my $SQL = <<"   SQL";
146                 SELECT  lt.usr,
147                         MAX(bl.billing_ts) AS last_pertinent_billing,
148                         MAX(pm.payment_ts) AS last_pertinent_payment
149                   FROM  ( SELECT id,usr,billing_location AS location, 'g'::char AS x_type FROM money.grocery
150                                 UNION ALL
151                           SELECT id,usr,circ_lib AS location, 'c'::char AS x_type FROM action.circulation
152                                 UNION ALL
153                           SELECT id,usr,circ_lib AS location, 'i'::char AS x_type FROM action.circulation
154                             WHERE checkin_time between ? and ? ) AS lt
155                         JOIN $descendants d ON (lt.location = d.id)
156                         JOIN money.collections_tracker cl ON (lt.usr = cl.usr)
157                         LEFT JOIN money.billing bl ON (lt.id = bl.xact)
158                         LEFT JOIN money.payment pm ON (lt.id = pm.xact)
159                   WHERE bl.billing_ts between ? and ?
160                         OR pm.payment_ts between ? and ?
161                         OR lt.x_type = 'i'::char
162                   GROUP BY 1
163         SQL
164
165         my @l_ids;
166         for my $l (@loc) {
167                 my $sth = money::collections_tracker->db_Main->prepare($SQL);
168                 $sth->execute( $startdate, $enddate, uc($l), $startdate, $enddate, $startdate, $enddate );
169                 while (my $row = $sth->fetchrow_hashref) {
170                         $row->{usr} = actor::user->retrieve($row->{usr})->to_fieldmapper;
171                         $client->respond( $row );
172                 }
173         }
174         return undef;
175 }
176 __PACKAGE__->register_method(
177         method          => 'active_in_collections',
178         api_name        => 'open-ils.storage.money.collections.users_with_activity',
179         stream          => 1,
180         argc            => 3,
181 );
182
183 sub ou_desk_payments {
184         my $self = shift;
185         my $client = shift;
186         my $lib = shift;
187         my $startdate = shift;
188         my $enddate = shift;
189
190         return undef unless ($startdate =~ /^\d{4}-\d{2}-\d{2}$/o);
191         return undef unless ($enddate =~ /^\d{4}-\d{2}-\d{2}$/o);
192         return undef unless ($lib =~ /^\d+$/o);
193
194         my $sql = <<"   SQL";
195
196 SELECT  *
197   FROM  crosstab(\$\$
198          SELECT ws.id,
199                 p.payment_type,
200                 SUM(COALESCE(p.amount,0.0))
201           FROM  money.desk_payment_view p
202                 JOIN actor.workstation ws ON (ws.id = p.cash_drawer)
203           WHERE p.payment_ts >= '$startdate'
204                 AND p.payment_ts < '$enddate'::TIMESTAMPTZ + INTERVAL '1 day'
205                 AND p.voided IS FALSE
206                 AND ws.owning_lib = $lib
207          GROUP BY 1, 2
208          ORDER BY 1,2
209         \$\$) AS X(
210           workstation int,
211           cash_payment numeric(10,2),
212           check_payment numeric(10,2),
213           credit_card_payment numeric(10,2) );
214
215         SQL
216
217         my $rows = money::payment->db_Main->selectall_arrayref( $sql );
218
219         for my $r (@$rows) {
220                 my $x = new Fieldmapper::money::workstation_payment_summary;
221                 $x->workstation( actor::workstation->retrieve($$r[0])->to_fieldmapper );
222                 $x->cash_payment($$r[1]);
223                 $x->check_payment($$r[2]);
224                 $x->credit_card_payment($$r[3]);
225
226                 $client->respond($x);
227         }
228
229         return undef;
230 }
231 __PACKAGE__->register_method(
232         method          => 'ou_desk_payments',
233         api_name        => 'open-ils.storage.money.org_unit.desk_payments',
234         stream          => 1,
235         argc            => 3,
236 );
237
238 sub ou_user_payments {
239         my $self = shift;
240         my $client = shift;
241         my $lib = shift;
242         my $startdate = shift;
243         my $enddate = shift;
244
245         return undef unless ($startdate =~ /^\d{4}-\d{2}-\d{2}$/o);
246         return undef unless ($enddate =~ /^\d{4}-\d{2}-\d{2}$/o);
247         return undef unless ($lib =~ /^\d+$/o);
248
249         my $sql = <<"   SQL";
250
251 SELECT  *
252   FROM  crosstab(\$\$
253          SELECT au.id,
254                 p.payment_type,
255                 SUM(COALESCE(p.amount,0.0))
256           FROM  money.bnm_payment_view p
257                 JOIN actor.usr au ON (au.id = p.accepting_usr)
258           WHERE p.payment_ts >= '$startdate'
259                 AND p.payment_ts < '$enddate'::TIMESTAMPTZ + INTERVAL '1 day'
260                 AND p.voided IS FALSE
261                 AND au.home_ou = $lib
262                 AND p.payment_type IN ('credit_payment','forgive_payment','work_payment')
263          GROUP BY 1, 2
264          ORDER BY 1,2
265         \$\$) AS X(
266           usr int,
267           forgive_payment numeric(10,2),
268           work_payment numeric(10,2),
269           credit_payment numeric(10,2) );
270
271         SQL
272
273         my $rows = money::payment->db_Main->selectall_arrayref( $sql );
274
275         for my $r (@$rows) {
276                 my $x = new Fieldmapper::money::user_payment_summary;
277                 $x->usr( actor::user->retrieve($$r[0])->to_fieldmapper );
278                 $x->forgive_payment($$r[1]);
279                 $x->work_payment($$r[2]);
280                 $x->credit_payment($$r[3]);
281
282                 $client->respond($x);
283         }
284
285         return undef;
286 }
287 __PACKAGE__->register_method(
288         method          => 'ou_user_payments',
289         api_name        => 'open-ils.storage.money.org_unit.user_payments',
290         stream          => 1,
291         argc            => 3,
292 );
293
294
295 1;