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