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