From 918009b29055738c4085faf5fd5cf689ca656881 Mon Sep 17 00:00:00 2001 From: Martin Renvoize Date: Thu, 21 Jun 2018 14:18:17 +0100 Subject: [PATCH] Bug 11983: Centralised Koha::Patrons method OPTION 2 https://bugs.koha-community.org/show_bug.cgi?id=11982 --- Koha/Patrons.pm | 442 ++++++++++++++++++++++++++++++++++ t/db_dependent/Koha/Patrons.t | 351 ++++++++++++++++++++++++++- 2 files changed, 792 insertions(+), 1 deletion(-) diff --git a/Koha/Patrons.pm b/Koha/Patrons.pm index 3b3b4b1cb6..5da77178b1 100644 --- a/Koha/Patrons.pm +++ b/Koha/Patrons.pm @@ -65,6 +65,448 @@ sub search_limited { return $self->search( $params, $attributes ); } +=head3 filter_by_is_guarantor + + Koha::Patrons->filter_by_is_guarantor(1); + +Returns patrons filtered by whether they have a guarantor, are a guarantor or are not a guarantor. + +=head4 options + +=over 4 + +=item undef - do not apply filter + +=item 1 - boolean to limit to NOT guarantors + +=item 0 - boolean limit to ONLY guarantors + +=back + +=cut + +sub filter_by_is_guarantor { + my ( $self, $option ) = @_; + + return $self unless defined($option); + + my $guarantorList = Koha::Patron::Relationships->search( + {}, + { + columns => ['guarantor_id'], + distinct => 1 + } + )->_resultset->as_query; + + # Patrons who are guarantors + return $self->search( + { 'me.borrowernumber' => { '-in' => $guarantorList } } ) + if $option; + + # Patrons who are not guarantors + return $self->search( + { 'me.borrowernumber' => { '-not_in' => $guarantorList } } ); +} + +=head3 filter_by_has_guarantor + + Koha::Patrons->filter_by_has_guarantor(1); + +Returns patrons filtered by whether they have a guarantor, are a guarantor or are not a guarantor. + +=head4 options + +=over 4 + +=item undef - do not apply filter + +=item 1 - boolean to limit to users who have a guarantor + +=item 0 - boolean to limit to users who do not have a guarantor + +=back + +=cut + +sub filter_by_has_guarantor { + my ( $self, $option ) = @_; + + return $self unless defined($option); + + my $guaranteeList = Koha::Patron::Relationships->search( + {}, + { + columns => ['guarantee_id'], + distinct => 1 + } + )->_resultset->as_query; + + # Patrons who have guarantors + return $self->search( + { 'me.borrowernumber' => { '-in' => $guaranteeList } } ) + if $option; + + # Patrons who do not have guarantors + return $self->search( + { 'me.borrowernumber' => { '-not_in' => $guaranteeList } } ); +} + +=head3 filter_by_last_issued + + Koha::Patrons->filter_by_last_issued( { after => DateTime, before => DateTime } ); + +Returns patrons filtered by whether their last issue falls betwen the passed limits. + +=head4 arguments hashref + +=over 4 + +=item before (optional) - filter out patrons whose last_issue was since DateTime + +=item after (optional) - filter out patrons whose last_issue has been since DateTime + +=back + +=cut + +sub filter_by_last_issued { + my ( $self, $options ) = @_; + + return $self + unless ( defined($options) + && ( $options->{before} || $options->{after} ) ); + + my $where = {}; + my $group_by = [ map { 'me.' . $_ } $self->_resultset->result_source->columns ]; + my $attrs = { + join => 'old_issues', + group_by => $group_by, + order_by => { '-desc' => 'old_issues.timestamp' } + }; + my $dtf = Koha::Database->new->schema->storage->datetime_parser; + + push @{ $where->{'-and'} }, + { 'old_issues.timestamp' => + { '<' => $dtf->format_datetime( $options->{before} ) } } + if $options->{before}; + push @{ $where->{'-and'} }, + { 'old_issues.timestamp' => + { '>' => $dtf->format_datetime( $options->{after} ) } } + if $options->{after}; + + return $self->search( $where, $attrs ); +} + +# NOTE: We can't use a HAVING clause with SQL Strict modes and mysql 5.5 and 5.6 :( +# See https://bugs.mysql.com/bug.php?id=80455 +#sub filter_by_last_issued { +# my ( $self, $options ) = @_; +# +# return $self +# unless ( defined($options) +# && ( $options->{before} || $options->{after} ) ); +# +# my $where = {}; +# my $attrs = { +# join => 'old_issues', +# group_by => 'me.borrowernumber', +# '+select' => [ { max => 'old_issues.timestamp', '-as' => 'last_issued' } ], +# '+as' => [ 'last_issued' ] +# }; +# my $dtf = Koha::Database->new->schema->storage->datetime_parser; +# +# push @{ $attrs->{'having'}->{'-and'} }, +# { 'last_issued' => { '<' => $dtf->format_datetime( $options->{before} ) } +# } +# if $options->{before}; +# +# push @{ $attrs->{'having'}->{'-and'} }, +# { 'last_issued' => { '>' => $dtf->format_datetime( $options->{after} ) } } +# if $options->{after}; +# +# return $self->search( $where, $attrs ); +#} + +=head3 filter_by_has_issues + + Koha::Patrons->filter_by_has_issues(1); + +Returns patrons filtered by whether they have current issues or not. + +=head4 options + +=over 4 + +=item undef - do not apply filter + +=item 0 - limit to patrons WITHOUT current issues + +=item 1 - limit to patrons WITH current issues + +=back + +=cut + +sub filter_by_has_issues { + my ( $self, $option ) = @_; + + return $self + unless defined($option); + + my $issueList = Koha::Checkouts->search( + {}, + { + columns => ['borrowernumber'], + distinct => 1 + } + )->_resultset->as_query; + + # Patrons who have current issues + return $self->search( { 'me.borrowernumber' => { '-in' => $issueList } } ) + if $option; + + # Patrons who do not have current issues + return $self->search( + { 'me.borrowernumber' => { '-not_in' => $issueList } } ); +} + +# NOTE: We can't use a HAVING clause with SQL Strict modes and mysql 5.5 and 5.6 :( +# See https://bugs.mysql.com/bug.php?id=80455 +#sub filter_by_has_issues { +# my ( $self, $option ) = @_; +# +# return $self +# unless defined($option); +# +# my $where = {}; +# my $group_by = [ map { 'me.' . $_ } $self->_resultset->result_source->columns ]; +# my $attrs = { +# join => 'issues', +# group_by => $group_by, +# '+select' => [ { count => 'issues.issue_id', -as => 'current_issues' } ], +# '+as' => 'current_issues' +# }; +# +# # Patrons who have current issues +# if ($option) { +# $attrs->{'having'} = { 'current_issues' => { '>' => 0 } }; +# } +# +# # Patrons who do not have current issues +# else { +# $attrs->{'having'} = { 'current_issues' => 0 }; +# } +# +# return $self->search($where,$attrs); +#} + +=head3 filter_by_when_expired + + Koha::Patrons->filter_by_when_expired( { after => DateTime, before => DateTime ); + +Returns patrons filtered by whether their accounts expired between between the passed limits. + +=head4 arguments hashref + +=over 4 + +=item before (optional) - filter out patrons whose expired_date was since DateTime + +=item after (optional) - filter out patrons whose expired_date has been since DateTime + +=back + +=cut + +sub filter_by_when_expired { + my ( $self, $options ) = @_; + + return $self + unless ( defined($options) + && ( $options->{before} || $options->{after} ) ); + + my $where = {}; + my $attrs = {}; + my $dtf = Koha::Database->new->schema->storage->datetime_parser; + + # Before only + $where->{'me.dateexpiry'} = + { '<' => $dtf->format_datetime( $options->{before} ) } + if ( $options->{before} && !$options->{after} ); + + # After only (includes null) + $where->{'me.dateexpiry'} = + [ undef, { '>' => $dtf->format_datetime( $options->{after} ) } ] + if ( !$options->{before} && $options->{after} ); + + # Before and After (between) + $where->{'me.dateexpiry'} = [ + '-and', + { '<' => $dtf->format_datetime( $options->{before} ) }, + { '>' => $dtf->format_datetime( $options->{after} ) } + ] + if ( $options->{before} && $options->{after} ); + + return $self->search( $where, $attrs ); +} + + +=head3 filter_by_amount_owed + + Koha::Patrons->filter_by_amount_owed( { less_than => '2.00', more_than => '0.50' } ); + +Returns patrons filtered by how much money they owe, between passed limits. + +=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 + +=back + +=cut + +#sub filter_by_amount_owed { +# my ( $self, $options ) = @_; +# +# return $self +# unless ( defined($options) +# && ( $options->{less_than} || $options->{more_than} ) ); +# +# my $where = {}; +# $where = [ +# \[ 'SUM(amountoutstanding) < ?', $options->{less_than} ], +# \['SUM(amountoutstanding) = ?', '0'] +# ] +# if ( defined( $options->{less_than} ) +# && !defined( $options->{more_than} ) ); +# +# $where = \['SUM(amountoutstanding) > ?', $options->{more_than} ] +# if (!defined( $options->{less_than} ) +# && defined( $options->{more_than} ) ); +# +# $where = { +# '-and' => [ +# \[ 'SUM(amountoutstanding) > ?', $options->{more_than} ], +# \[ 'SUM(amountoutstanding) < ?', $options->{less_than} ] +# ] +# } +# if ( defined( $options->{less_than} ) +# && defined( $options->{more_than} ) ); +# +# my $accountsList = Koha::Account::Lines->search( +# $where, +# { +# columns => ['borrowernumber'], +# group_by => 'me.borrowernumber', +# } +# )->_resultset->as_query; +# +# return $self->search( { 'me.borrowernumber' => { '-in' => $accountsList } } ) +#} + +# NOTE: We can't use a HAVING clause with SQL Strict modes and mysql 5.5 and 5.6 :( +# See https://bugs.mysql.com/bug.php?id=80455 +sub filter_by_amount_owed { + my ( $self, $options ) = @_; + + return $self + unless ( defined($options) + && ( $options->{less_than} || $options->{more_than} ) ); + + my $where = {}; + my $group_by = [ map { 'me.' . $_ } $self->_resultset->result_source->columns ]; + #push @{$group_by}, 'outstanding'; + my $attrs = { + join => 'accountlines', + group_by => $group_by, + '+select' => + { sum => 'accountlines.amountoutstanding', '-as' => 'outstanding' }, + '+as' => 'outstanding' + }; + + $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 filter_by_in_lists + + Koha::Patrons->filter_by_in_lists(\@lists); + +Returns patrons filtered by whether that appear in ANY of the lists specified. + +=head4 arguments arrayref + +Arrayref of list ids + +=back + +=cut + +sub filter_by_in_lists { + my ( $self, $lists ) = @_; + + return $self + unless defined($lists); + + my $where = {}; + my $attrs = { join => 'patron_list_patrons' }; + + push @{ $where->{'-and'} }, + { 'patron_list_patrons.patron_list_id' => { '-in' => $lists } }; + + return $self->search( $where, $attrs ); +} + +=head3 filter_by_not_in_lists + + Koha::Patrons->filter_by_not_in_lists(\@lists); + +Returns patrons filtered by whether they do not appear in ANY of the lists specified. + +=head4 arguments arrayref + +Arrayref of list ids + +=back + +=cut + +sub filter_by_not_in_lists { + my ( $self, $lists ) = @_; + + return $self + unless defined($lists); + + my $where = {}; + my $attrs = { join => 'patron_list_patrons' }; + + push @{ $where->{'-and'} }, + { 'patron_list_patrons.patron_list_id' => { '-not_in' => $lists } }; + + return $self->search( $where, $attrs ); +} + =head3 search_housebound_choosers Returns all Patrons which are Housebound choosers. diff --git a/t/db_dependent/Koha/Patrons.t b/t/db_dependent/Koha/Patrons.t index c2824082e7..69e8a88ebf 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 => 41; +use Test::More tests => 42; use Test::Warn; use Test::Exception; use Test::MockModule; @@ -1602,6 +1602,355 @@ subtest 'BorrowersLog tests' => sub { is( scalar @logs, 1, 'With BorrowerLogs and TrackLastPatronActivity we should not spam the logs'); }; +subtest 'complex filters' => sub { + plan tests => 7; + + my $now = DateTime->now; + 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', + dateexpiry => $now->clone->subtract( days => 14 ) + } + )->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', + dateexpiry => $now->clone->subtract( days => 7 ) + } + )->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 + ] + } + ); + + # Set new_patron_cf_1 to be the guarantor for new_patron_cf_2 + $new_patron_cf_2->add_guarantor( + { + guarantor_id => $new_patron_cf_1->borrowernumber, + relationship => 'test' + } + ); + + subtest 'filter_by_is_guarantor' => sub { + plan tests => 6; + + my $filtered = $results->filter_by_is_guarantor(); + is( ref($filtered), 'Koha::Patrons', +'Koha::Patrons->filter_by_is_guarantor should return a Koha::Patrons result set in a scalar context' + ); + is( $filtered->count, 3, + 'filter_by_is_guarantor() found both patrons' ); + + $filtered = $results->filter_by_is_guarantor(1); + is( $filtered->count, 1, 'filter_by_is_guarantor(1) found one patron' ); + is( $filtered->next->guarantee_relationships->count, + 1, 'filter_by_is_guarantor(1) found the correct patron' ); + + $filtered = $results->filter_by_is_guarantor(0); + is( $filtered->count, 2, + 'filter_by_is_guarantor(0) found two patrons' ); + is( $filtered->next->guarantee_relationships->count, + 0, 'filter_by_is_guarantor(0) found the correct patrons' ); + }; + + subtest 'filter_by_has_guarantor' => sub { + plan tests => 6; + + my $filtered = $results->filter_by_has_guarantor(); + is( ref($filtered), 'Koha::Patrons', +'filter_by_has_guarantor() should return a Koha::Patrons result set in a scalar context' + ); + is( $filtered->count, 3, + 'filter_by_has_guarantor() found both patrons' ); + + $filtered = $results->filter_by_has_guarantor(1); + is( $filtered->count, 1, + 'filter_by_has_guarantor(1) found one patron' ); + is( + $filtered->next->borrowernumber, + $new_patron_cf_2->borrowernumber, + 'filter_by_has_guarantor(1) found the correct patron' + ); + + $filtered = $results->filter_by_has_guarantor(0); + is( $filtered->count, 2, + 'filter_by_has_guarantor(0) found two patrons' ); + is_deeply( + [ + sort( $filtered->next->borrowernumber, + $filtered->next->borrowernumber ) + ], + [ + sort ( $new_patron_cf_1->borrowernumber, + $new_patron_cf_3->borrowernumber ) + ], + 'filter_by_has_guarantor(0) found the correct patrons' + ); + }; + + subtest 'filter_by_last_issued' => sub { + plan tests => 4; + + my $first = DateTime->now()->subtract( days => 5 ); + my $last = $first->clone->add( days => 2 ); + my $old_issue_1 = $builder->build( + { + source => 'OldIssue', + value => { + borrowernumber => $new_patron_cf_1->borrowernumber, + timestamp => $first->clone->subtract( days => 1 ) + }, + } + ); + my $old_issue_2 = $builder->build( + { + source => 'OldIssue', + value => { + borrowernumber => $new_patron_cf_2->borrowernumber, + timestamp => $last->clone->add( days => 1 ) + }, + } + ); + my $old_issue_3 = $builder->build( + { + source => 'OldIssue', + value => { + borrowernumber => $new_patron_cf_3->borrowernumber, + timestamp => $first->clone->add( days => 1 ) + }, + } + ); + + my $filtered = $results->filter_by_last_issued(); + is( ref($filtered), 'Koha::Patrons', +'filter_by_last_issued() should return a Koha::Patrons result set in a scalar context' + ); + + $filtered = $results->filter_by_last_issued( { before => $last } ); + is( $filtered->_resultset->as_subselect_rs->count, + 2, "filter_by_last_issued({ before => $last }) found two patrons" ); + + $filtered = $results->filter_by_last_issued( { after => $first } ); + is( $filtered->_resultset->as_subselect_rs->count, + 2, "filter_by_last_issued({ after => $first }) found two patrons" ); + + $filtered = $results->filter_by_last_issued( + { after => $first, before => $last } ); + is( $filtered->_resultset->as_subselect_rs->count, 1, +"filter_by_last_issued({ after => $first, before => $last }) found two patrons" + ); + }; + + subtest 'filter_by_has_issues' => sub { + plan tests => 3; + + my $issue_1 = $builder->build( + { + source => 'Issue', + value => { + borrowernumber => $new_patron_cf_1->borrowernumber + }, + } + ); + + my $filtered = $results->filter_by_has_issues(); + is( ref($filtered), 'Koha::Patrons', +'filter_by_has_issues() should return a Koha::Patrons result set in a scalar context' + ); + + $filtered = $results->filter_by_has_issues(1); + is( $filtered->_resultset->as_subselect_rs->count, + 1, "filter_by_has_issues(1) found one patron" ); + + $filtered = $results->filter_by_has_issues(0); + is( $filtered->_resultset->as_subselect_rs->count, + 2, "filter_by_has_issues(0) found two patrons" ); + }; + + subtest 'filter_by_when_expired' => sub { + plan tests => 4; + + my $first = $now->clone->subtract( days => 12 ); + my $second = $now->clone->subtract( days => 4 ); + + my $filtered = $results->filter_by_when_expired(); + is( ref($filtered), 'Koha::Patrons', +'Koha::Patrons->filter_by_when_expired should return a Koha::Patrons result set in a scalar context' + ); + + $filtered = $results->filter_by_when_expired( { before => $second } ); + is( $filtered->_resultset->as_subselect_rs->count, + 2, + "filter_by_when_expired({ before => $second }) found two patrons" ); + + $filtered = $results->filter_by_when_expired( { after => $first } ); + is( $filtered->_resultset->as_subselect_rs->count, + 2, + "filter_by_when_expired({ after => $first }) found two patrons" ); + + $filtered = $results->filter_by_when_expired( + { after => $first, before => $second } ); + is( $filtered->_resultset->as_subselect_rs->count, 1, +"filter_by_when_expired({ after => $first, before => $second }) found one patrons" + ); + }; + + subtest 'filter_by_amount_owed' => sub { + plan tests => 4; + + my $fine1 = $builder->build( + { + source => 'Accountline', + value => { + borrowernumber => $new_patron_cf_1->borrowernumber, + amountoutstanding => 12.00 + }, + } + ); + my $fine2 = $builder->build( + { + source => 'Accountline', + value => { + borrowernumber => $new_patron_cf_2->borrowernumber, + amountoutstanding => 8.00 + }, + } + ); + my $fine3 = $builder->build( + { + source => 'Accountline', + value => { + borrowernumber => $new_patron_cf_2->borrowernumber, + amountoutstanding => 10.00 + }, + } + ); + + 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, + # user with 1 fine falls below lower limit + # and user with no fines falls below lower limit. + $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 one patrons" + ); + }; + + subtest 'filter_by_lists' => sub { + plan tests => 6; + + my $patronListPatron1 = $builder->build( + { + source => 'PatronListPatron', + value => { + borrowernumber => $new_patron_cf_1->borrowernumber + }, + } + ); + my $patronListPatron2 = $builder->build( + { + source => 'PatronListPatron', + value => { + borrowernumber => $new_patron_cf_2->borrowernumber + }, + } + ); + #my $patronListPatron3 = $builder->build( + # { + # source => 'PatronListPatron', + # value => { + # borrowernumber => $new_patron_cf_2->borrowernumber, + # patron_list_id => $patronListPatron1->patron_list_id + # }, + # } + #); + + my $filtered = $results->filter_by_in_lists(); + is( ref($filtered), 'Koha::Patrons', +'Koha::Patrons->filter_by_in_lists should return a Koha::Patrons result set in a scalar context' + ); + + $filtered = $results->filter_by_not_in_lists(); + is( ref($filtered), 'Koha::Patrons', +'Koha::Patrons->filter_by_not_in_lists should return a Koha::Patrons result set in a scalar context' + ); + + $filtered = + $results->filter_by_in_lists( + [ $patronListPatron1->{patron_list_id} ] ); + is( $filtered->_resultset->as_subselect_rs->count, 1, +"filter_by_in_lists([$patronListPatron1->patron_list_id]) found one patron" + ); + is( $filtered->next->borrowernumber, $new_patron_cf_1->borrowernumber, +"filter_by_in_lists([$patronListPatron1->{patron_list_id}]) found the correct patron" + ); + + $filtered = + $results->filter_by_in_lists( + [ $patronListPatron1->{patron_list_id} ] ); + is( $filtered->_resultset->as_subselect_rs->count, 1, +"filter_by_in_lists([$patronListPatron1->{patron_list_id}]) found one patron" + ); + is( $filtered->next->borrowernumber, $new_patron_cf_1->borrowernumber, +"filter_by_in_lists([$patronListPatron1->{patron_list_id}]) found the correct patron" + ); + + }; + + # subtest 'chaining' => sub { + # plan tests => 0; + # + #}; +}; + $schema->storage->txn_rollback; subtest 'Test Koha::Patrons::merge' => sub { -- 2.20.1