From a47e26ab8dc031a81b4457b51d95510a633c4b67 Mon Sep 17 00:00:00 2001
From: Martin Renvoize <martin.renvoize@ptfs-europe.com>
Date: Fri, 2 Jul 2021 09:07:13 +0100
Subject: [PATCH] Bug 15156: Add filter_by_amount_owed that's chainable
This patch acts as an alternative here. We pull out the code from bug
11983 and extend it to support filtering of debts by branchcode and
debit_type.
The advantage of this approach is that we can add the filter into the
middle of a search chain to filter down an pre-existing search.
---
Koha/Patrons.pm | 79 ++++++++++++++++++
t/db_dependent/Koha/Patrons.t | 146 +++++++++++++++++++++++++++++++++-
2 files changed, 224 insertions(+), 1 deletion(-)
diff --git a/Koha/Patrons.pm b/Koha/Patrons.pm
index 4f2b55956a..688cf5fa20 100644
--- a/Koha/Patrons.pm
+++ b/Koha/Patrons.pm
@@ -480,6 +480,85 @@ sub filter_by_attribute_value {
return Koha::Patrons->_new_from_dbic($rs);
}
+=head3 filter_by_amount_owed
+
+ Koha::Patrons->filter_by_amount_owed(
+ {
+ less_than => '2.00',
+ more_than => '0.50',
+ debit_type => $debit_type_code,
+ library => $branchcode
+ }
+ );
+
+Returns patrons filtered by how much money they owe, between passed limits.
+
+Optionally limit to debts of a particular debit_type or/and owed to a particular library.
+
+=head4 arguments hashref
+
+=over 4
+
+=item less_than (optional) - filter out patrons who owe less than Amount
+
+=item more_than (optional) - filter out patrons who owe more than Amount
+
+=item debit_type (optional) - filter the amount owed by debit type
+
+=item library (optional) - filter the amount owed to a particular branch
+
+=back
+
+=cut
+
+sub filter_by_amount_owed {
+ my ( $self, $options ) = @_;
+
+ return $self
+ unless (
+ defined($options)
+ && ( defined( $options->{less_than} )
+ || defined( $options->{more_than} ) )
+ );
+
+ my $where = {};
+ my $group_by =
+ [ map { 'me.' . $_ } $self->_resultset->result_source->columns ];
+
+ my $attrs = {
+ join => 'accountlines',
+ group_by => $group_by,
+ '+select' =>
+ { sum => 'accountlines.amountoutstanding', '-as' => 'outstanding' },
+ '+as' => 'outstanding'
+ };
+
+ $where->{'accountlines.debit_type_code'} = $options->{debit_type}
+ if defined( $options->{debit_type} );
+
+ $where->{'accountlines.branchcode'} = $options->{library}
+ if defined( $options->{library} );
+
+ $attrs->{'having'} = [
+ { 'outstanding' => { '<' => $options->{less_than} } },
+ { 'outstanding' => undef }
+ ]
+ if ( defined( $options->{less_than} )
+ && !defined( $options->{more_than} ) );
+
+ $attrs->{'having'} = { 'outstanding' => { '>' => $options->{more_than} } }
+ if (!defined( $options->{less_than} )
+ && defined( $options->{more_than} ) );
+
+ $attrs->{'having'}->{'-and'} = [
+ { 'outstanding' => { '>' => $options->{more_than} } },
+ { 'outstanding' => { '<' => $options->{less_than} } }
+ ]
+ if ( defined( $options->{less_than} )
+ && defined( $options->{more_than} ) );
+
+ return $self->search( $where, $attrs );
+}
=head3 _type
diff --git a/t/db_dependent/Koha/Patrons.t b/t/db_dependent/Koha/Patrons.t
index 152b80c35b..a25bfd8553 100755
--- a/t/db_dependent/Koha/Patrons.t
+++ b/t/db_dependent/Koha/Patrons.t
@@ -19,7 +19,7 @@
use Modern::Perl;
-use Test::More tests => 42;
+use Test::More tests => 43;
use Test::Warn;
use Test::Exception;
use Test::MockModule;
@@ -2184,4 +2184,148 @@ subtest 'queue_notice' => sub {
is( Koha::Notice::Messages->search({borrowernumber => $patron->borrowernumber })->count, $counter,"Count of queued notices not increased in test mode");
};
+subtest 'filter_by_amount_owed' => sub {
+ plan tests => 6;
+
+ my $library = $builder->build({source => 'Branch' });
+ my $category = $builder->build({source => 'Category' });
+
+ my $new_patron_cf_1 = Koha::Patron->new(
+ {
+ cardnumber => 'test_cn_cf_1',
+ branchcode => $library->{branchcode},
+ categorycode => $category->{categorycode},
+ surname => 'surname for patron1',
+ firstname => 'firstname for patron1',
+ userid => 'a_nonexistent_userid_cf_1',
+ }
+ )->store;
+ my $new_patron_cf_2 = Koha::Patron->new(
+ {
+ cardnumber => 'test_cn_cf_2',
+ branchcode => $library->{branchcode},
+ categorycode => $category->{categorycode},
+ surname => 'surname for patron2',
+ firstname => 'firstname for patron2',
+ userid => 'a_nonexistent_userid_cf_2',
+ }
+ )->store;
+ my $new_patron_cf_3 = Koha::Patron->new(
+ {
+ cardnumber => 'test_cn_cf_3',
+ branchcode => $library->{branchcode},
+ categorycode => $category->{categorycode},
+ surname => 'surname for patron3',
+ firstname => 'firstname for patron3',
+ userid => 'a_nonexistent_userid_cf_3',
+ }
+ )->store;
+
+ my $results = Koha::Patrons->search(
+ {
+ 'me.borrowernumber' => [
+ $new_patron_cf_1->borrowernumber,
+ $new_patron_cf_2->borrowernumber,
+ $new_patron_cf_3->borrowernumber
+ ]
+ }
+ );
+
+ my $fine1 = $builder->build(
+ {
+ source => 'Accountline',
+ value => {
+ borrowernumber => $new_patron_cf_1->borrowernumber,
+ amountoutstanding => 12.00,
+ amount => 12.00,
+ debit_type_code => 'OVERDUE',
+ branchcode => $library->{branchcode}
+ },
+ }
+ );
+ my $fine2 = $builder->build(
+ {
+ source => 'Accountline',
+ value => {
+ borrowernumber => $new_patron_cf_2->borrowernumber,
+ amountoutstanding => 8.00,
+ amount => 8.00,
+ debit_type_code => 'OVERDUE',
+ branchcode => $library->{branchcode}
+
+ },
+ }
+ );
+ my $fine3 = $builder->build(
+ {
+ source => 'Accountline',
+ value => {
+ borrowernumber => $new_patron_cf_2->borrowernumber,
+ amountoutstanding => 10.00,
+ amount => 10.00,
+ debit_type_code => 'OVERDUE',
+ branchcode => $library->{branchcode}
+ },
+ }
+ );
+
+ my $filtered = $results->filter_by_amount_owed();
+ is( ref($filtered), 'Koha::Patrons',
+'Koha::Patrons->filter_by_amount_owed should return a Koha::Patrons result set in a scalar context'
+ );
+
+ my $lower_limit = 12.00;
+ my $upper_limit = 16.00;
+
+ # Catch user with 1 x 12.00 fine and user with no fines.
+ $filtered =
+ $results->filter_by_amount_owed( { less_than => $upper_limit } );
+ is( $filtered->_resultset->as_subselect_rs->count, 2,
+"filter_by_amount_owed({ less_than => $upper_limit }) found two patrons"
+ );
+
+ # Catch user with 1 x 8.00 and 1 x 10.00 fine
+ $filtered =
+ $results->filter_by_amount_owed( { more_than => $lower_limit } );
+ is( $filtered->_resultset->as_subselect_rs->count, 1,
+"filter_by_amount_owed({ more_than => $lower_limit }) found two patrons"
+ );
+
+ # User with 2 fines falls above upper limit - Excluded,
+ # user with 1 fine falls below lower limit - Excluded
+ # and user with no fines falls below lower limit - Excluded.
+ $filtered = $results->filter_by_amount_owed(
+ { more_than => $lower_limit, less_than => $upper_limit } );
+ is( $filtered->_resultset->as_subselect_rs->count, 0,
+"filter_by_amount_owed({ more_than => $lower_limit, less_than => $upper_limit }) found zero patrons"
+ );
+
+ my $library2 = $builder->build({source => 'Branch' });
+ my $fine4 = $builder->build(
+ {
+ source => 'Accountline',
+ value => {
+ borrowernumber => $new_patron_cf_2->borrowernumber,
+ amountoutstanding => 10.00,
+ amount => 10.00,
+ debit_type_code => 'HOLD',
+ branchcode => $library2->{branchcode}
+ },
+ }
+ );
+
+ # Catch only the user with a HOLD fee over 6.00
+ $filtered = $results->filter_by_amount_owed( { more_than => 6.00, debit_type => 'HOLD' } );
+ is( $filtered->_resultset->as_subselect_rs->count, 1,
+"filter_by_amount_owed({ more_than => 6.00, debit_type => 'HOLD' }) found one patron"
+ );
+
+ # Catch only the user with a fee over 6.00 at the specified library
+ $filtered = $results->filter_by_amount_owed( { more_than => 6.00, library => $library2->{branchcode} } );
+ is( $filtered->_resultset->as_subselect_rs->count, 1,
+"filter_by_amount_owed({ more_than => 6.00, library => $library2->{branchcode} }) found one patron"
+ );
+
+};
+
$schema->storage->txn_rollback;
--
2.20.1