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