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 new_collections {
   8         my $self = shift;
   9         my $client = shift;
  10         my $age = shift;
  11         my $amount = shift;
  12         my @loc = @_;
  13
  14         my $mct = money::collections_tracker->table;
  15         my $mb = money::billing->table;
  16         my $circ = action::circulation->table;
  17         my $mg = money::grocery->table;
  18         my $descendants = "actor.org_unit_descendants((select id from actor.org_unit where shortname = ?))";
  19
  20         my $SQL = <<"   SQL";
  21                 SELECT  lt.usr,
  22                         MAX(bl.billing_ts) AS last_pertinent_billing,
  23                         SUM(bl.amount) - SUM((SELECT SUM(amount) FROM money.payment WHERE xact = lt.id)) AS threshold_amount
  24                   FROM  ( SELECT id,usr,billing_location AS location FROM money.grocery
  25                                 UNION ALL
  26                           SELECT id,usr,circ_lib AS location FROM action.circulation ) AS lt
  27                         JOIN $descendants d ON (lt.location = d.id)
  28                         JOIN money.billing bl ON (lt.id = bl.xact AND bl.voided IS FALSE)
  29                   WHERE AGE(bl.billing_ts) > ?
  30                   GROUP BY lt.usr
  31                   HAVING  SUM(
  32                                 (SELECT COUNT(*)
  33                                   FROM  money.collections_tracker
  34                                   WHERE location in (
  35                                                 (SELECT id
  36                                                   FROM  $descendants )
  37                                         )
  38                                 ) ) = 0
  39                         AND (SUM(bl.amount) - SUM((SELECT SUM(amount) FROM money.payment WHERE xact = lt.id))) > ?
  40         SQL
  41
  42         my @l_ids;
  43         for my $l (@loc) {
  44                 my $sth = money::collections_tracker->db_Main->prepare($SQL);
  45                 $sth->execute(uc($l), $age, uc($l), $amount );
  46                 while (my $row = $sth->fetchrow_hashref) {
  47                         #$row->{usr} = actor::user->retrieve($row->{usr})->to_fieldmapper;
  48                         $client->respond( $row );
  49                 }
  50         }
  51         return undef;
  52 }
  53 __PACKAGE__->register_method(
  54         method          => 'new_collections',
  55         api_name        => 'open-ils.storage.money.collections.users_of_interest',
  56         stream          => 1,
  57         argc            => 3,
  58 );
  59
  60 sub active_in_collections {
  61         my $self = shift;
  62         my $client = shift;
  63         my $startdate = shift;
  64         my $enddate = shift;
  65         my @loc = @_;
  66
  67         my $mct = money::collections_tracker->table;
  68         my $mb = money::billing->table;
  69         my $circ = action::circulation->table;
  70         my $mg = money::grocery->table;
  71         my $descendants = "actor.org_unit_descendants((select id from actor.org_unit where shortname = ?))";
  72
  73         my $SQL = <<"   SQL";
  74                 SELECT  lt.usr,
  75                         MAX(bl.billing_ts) AS last_pertinent_billing,
  76                         MAX(pm.payment_ts) AS last_pertinent_payment
  77                   FROM  ( SELECT id,usr,billing_location AS location FROM money.grocery
  78                                 UNION ALL
  79                           SELECT id,usr,circ_lib AS location FROM action.circulation ) AS lt
  80                         JOIN $descendants d ON (lt.location = d.id)
  81                         JOIN money.collections_tracker cl ON (lt.usr = cl.usr)
  82                         LEFT JOIN money.billing bl ON (lt.id = bl.xact)
  83                         LEFT JOIN money.payment pm ON (lt.id = pm.xact)
  84                   WHERE cl.location IN ((SELECT id FROM $descendants))
  85                         AND (   bl.billing_ts between ? and ?
  86                                 OR pm.payment_ts between ? and ? )
  87                   GROUP BY 1
  88         SQL
  89
  90         my @l_ids;
  91         for my $l (@loc) {
  92                 my $sth = money::collections_tracker->db_Main->prepare($SQL);
  93                 $sth->execute(uc($l), uc($l), $startdate, $enddate, $startdate, $enddate );
  94                 while (my $row = $sth->fetchrow_hashref) {
  95                         $row->{usr} = actor::user->retrieve($row->{usr})->to_fieldmapper;
  96                         $client->respond( $row );
  97                 }
  98         }
  99         return undef;
 100 }
 101 __PACKAGE__->register_method(
 102         method          => 'active_in_collections',
 103         api_name        => 'open-ils.storage.money.collections.users_with_activity',
 104         stream          => 1,
 105         argc            => 3,
 106 );
 107
 108 1;