From 47c9946f45c03503d5ecf16517b41efadacf3a0a Mon Sep 17 00:00:00 2001 From: miker Date: Wed, 28 Jun 2006 19:24:09 +0000 Subject: [PATCH] adding collections related stuff everywhere... git-svn-id: svn://svn.open-ils.org/ILS/trunk@4818 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/examples/fm_IDL.xml | 17 ++++ .../OpenILS/Application/Storage/CDBI/money.pm | 7 ++ .../Application/Storage/Driver/Pg/dbi.pm | 6 ++ .../Application/Storage/Publisher/money.pm | 96 +++++++++++++++++++ .../src/perlmods/OpenILS/Utils/Fieldmapper.pm | 1 + Open-ILS/src/sql/Pg/080.schema.money.sql | 8 ++ 6 files changed, 135 insertions(+) diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml index 70f73b2e7c..58da7eae78 100644 --- a/Open-ILS/examples/fm_IDL.xml +++ b/Open-ILS/examples/fm_IDL.xml @@ -341,6 +341,23 @@ + + + + + + + + + + + + + + + + + diff --git a/Open-ILS/src/perlmods/OpenILS/Application/Storage/CDBI/money.pm b/Open-ILS/src/perlmods/OpenILS/Application/Storage/CDBI/money.pm index 89ee44744c..37f4060add 100644 --- a/Open-ILS/src/perlmods/OpenILS/Application/Storage/CDBI/money.pm +++ b/Open-ILS/src/perlmods/OpenILS/Application/Storage/CDBI/money.pm @@ -6,6 +6,13 @@ package money; use base qw/OpenILS::Application::Storage::CDBI/; #------------------------------------------------------------------------------- +package money::collections_tracker; +use base qw/money/; +__PACKAGE__->table('money_collections_tracker'); +__PACKAGE__->columns(Primary => 'id'); +__PACKAGE__->columns(Essential => qw/usr collector location enter_time/); +#------------------------------------------------------------------------------- + package money::billable_transaction; use base qw/money/; __PACKAGE__->table('money_billable_xact'); diff --git a/Open-ILS/src/perlmods/OpenILS/Application/Storage/Driver/Pg/dbi.pm b/Open-ILS/src/perlmods/OpenILS/Application/Storage/Driver/Pg/dbi.pm index cbd062172e..0cf0046c50 100644 --- a/Open-ILS/src/perlmods/OpenILS/Application/Storage/Driver/Pg/dbi.pm +++ b/Open-ILS/src/perlmods/OpenILS/Application/Storage/Driver/Pg/dbi.pm @@ -54,6 +54,12 @@ money::grocery->table( 'money.grocery' ); money::grocery->sequence( 'money.billable_xact_id_seq' ); + #--------------------------------------------------------------------- + package money::collections_tracker; + + money::collections_tracker->table( 'money.collections_tracker' ); + money::collections_tracker->sequence( 'money.collections_tracker_id_seq' ); + #--------------------------------------------------------------------- package money::billable_transaction; diff --git a/Open-ILS/src/perlmods/OpenILS/Application/Storage/Publisher/money.pm b/Open-ILS/src/perlmods/OpenILS/Application/Storage/Publisher/money.pm index e7a7e6d73a..d8f2c6c7c1 100644 --- a/Open-ILS/src/perlmods/OpenILS/Application/Storage/Publisher/money.pm +++ b/Open-ILS/src/perlmods/OpenILS/Application/Storage/Publisher/money.pm @@ -4,4 +4,100 @@ use OpenSRF::Utils::Logger qw/:level/; my $log = 'OpenSRF::Utils::Logger'; +sub new_collections { + my $self = shift; + my $client = shift; + my $age = shift; + my $amount = shift; + my @loc = @_; + + my $mct = money::collections_tracker->table; + my $mb = money::billing->table; + my $circ = action::circulation->table; + my $mg = money::grocery->table; + my $descendants = "actor.org_unit_descendants((select id from actor.org_unit where shortname=?))"; + + my $SQL = <<" SQL"; + SELECT lt.usr, + lt.location, + MAX(bl.billing_ts) AS last_pertinent_billing, + SUM(bl.amount) - SUM(COALESCE(pm.amount,0)) AS threshold_amount + FROM ( SELECT id,usr,billing_location AS location FROM money.grocery + UNION ALL + SELECT id,usr,circ_lib AS location FROM action.circulation ) AS lt + JOIN $descendants d ON (lt.location = d.id) + JOIN money.billing bl ON (lt.id = bl.xact) + LEFT JOIN money.payment pm ON (lt.id = pm.xact) + LEFT JOIN money.collections_tracker cl USING (usr,location) + WHERE AGE(bl.billing_ts) > ? + AND cl.usr IS NULL + GROUP BY 1, 2 HAVING (SUM(bl.amount) - SUM(COALESCE(pm.amount,0))) > ? + SQL + + my @l_ids; + for my $l (@loc) { + my $sth = money::collections_tracker->db_Main->prepare($SQL); + $sth->execute(uc($l), $age, $amount ); + while (my $row = $sth->fetchrow_hashref) { + $row->{usr} = actor::user->retrieve($row->{usr})->to_fieldmapper; + $client->respond( $row ); + } + } + return undef; +} +__PACKAGE__->register_method( + method => 'new_collections', + api_name => 'open-ils.storage.money.collections.users_of_interest', + stream => 1, + argc => 3, +); + +sub active_in_collections { + my $self = shift; + my $client = shift; + my $startdate = shift; + my $enddate = shift; + my @loc = @_; + + my $mct = money::collections_tracker->table; + my $mb = money::billing->table; + my $circ = action::circulation->table; + my $mg = money::grocery->table; + my $descendants = "actor.org_unit_descendants((select id from actor.org_unit where shortname=?))"; + + my $SQL = <<" SQL"; + SELECT lt.usr, + lt.location, + MAX(bl.billing_ts) AS last_pertinent_billing, + MAX(pm.payment_ts) AS last_pertinent_payment + FROM ( SELECT id,usr,billing_location AS location FROM money.grocery + UNION ALL + SELECT id,usr,circ_lib AS location FROM action.circulation ) AS lt + JOIN $descendants d ON (lt.location = d.id) + JOIN money.collections_tracker cl USING (usr,location) + LEFT JOIN money.billing bl ON (lt.id = bl.xact) + LEFT JOIN money.payment pm ON (lt.id = pm.xact) + WHERE bl.billing_ts between ? and ? + OR pm.payment_ts between ? and ? + GROUP BY 1, 2 + SQL + + my @l_ids; + for my $l (@loc) { + my $sth = money::collections_tracker->db_Main->prepare($SQL); + $sth->execute(uc($l), $startdate, $enddate, $startdate, $enddate ); + while (my $row = $sth->fetchrow_hashref) { + $row->{usr} = actor::user->retrieve($row->{usr})->to_fieldmapper; + $client->respond( $row ); + } + } + return undef; +} +__PACKAGE__->register_method( + method => 'active_in_collections', + api_name => 'open-ils.storage.money.collections.users_with_activity', + stream => 1, + argc => 3, +); + 1; diff --git a/Open-ILS/src/perlmods/OpenILS/Utils/Fieldmapper.pm b/Open-ILS/src/perlmods/OpenILS/Utils/Fieldmapper.pm index 2bbe82433e..fdf6053812 100644 --- a/Open-ILS/src/perlmods/OpenILS/Utils/Fieldmapper.pm +++ b/Open-ILS/src/perlmods/OpenILS/Utils/Fieldmapper.pm @@ -170,6 +170,7 @@ sub _init { 'Fieldmapper::money::credit_card_payment' => { hint => 'mccp' }, 'Fieldmapper::money::forgive_payment' => { hint => 'mfp' }, 'Fieldmapper::money::work_payment' => { hint => 'mwp' }, + 'Fieldmapper::money::collections_tracker' => { hint => 'mct' }, 'Fieldmapper::money::billing' => { hint => 'mb' }, 'Fieldmapper::money::billable_transaction' => { hint => 'mbt' }, diff --git a/Open-ILS/src/sql/Pg/080.schema.money.sql b/Open-ILS/src/sql/Pg/080.schema.money.sql index 60f3a12c6b..d11432ab56 100644 --- a/Open-ILS/src/sql/Pg/080.schema.money.sql +++ b/Open-ILS/src/sql/Pg/080.schema.money.sql @@ -4,6 +4,14 @@ BEGIN; CREATE SCHEMA money; +CREATE TABLE money.collections_tracker ( + id BIGSERIAL PRIMARY KEY, + usr INT NOT NULL REFERENCES actor.usr (id), -- actor.usr.id + collector INT NOT NULL REFERENCES actor.usr (id), + location INT NOT NULL REFERENCES actor.org_unit (id), + enter_time TIMESTAMP WITH TIME ZONE +); + CREATE TABLE money.billable_xact ( id BIGSERIAL PRIMARY KEY, usr INT NOT NULL, -- actor.usr.id -- 2.43.2