From b96f8b3e973b18121e57d4cabcebd3315ba18dd1 Mon Sep 17 00:00:00 2001 From: Martin Renvoize 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. Signed-off-by: David Nind Signed-off-by: Emmi Takkinen Signed-off-by: Nick Clemens Signed-off-by: Fridolin Somers --- 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 8e8bed9a0c..81c763cfb7 100644 --- a/Koha/Patrons.pm +++ b/Koha/Patrons.pm @@ -479,6 +479,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 390d9d3e66..5737461608 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.39.5