From f76acc2753a49f41c60d4cf01c70909d1d6ee013 Mon Sep 17 00:00:00 2001
From: Martin Renvoize <martin.renvoize@ptfs-europe.com>
Date: Wed, 20 Jun 2018 16:28:19 +0100
Subject: [PATCH] Bug 11983: Centralised Koha::Patrons method OPTION 1

---
 Koha/Patrons.pm | 139 ++++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 139 insertions(+)

diff --git a/Koha/Patrons.pm b/Koha/Patrons.pm
index 0d2d9ec6b4..bf8ad3fca1 100644
--- a/Koha/Patrons.pm
+++ b/Koha/Patrons.pm
@@ -136,6 +136,145 @@ sub guarantor {
     return Koha::Patrons->find( $self->guarantorid() );
 }
 
+=head3 filter
+
+    my $patrons = Koha::Patrons->filter({});
+
+This method returns a Koha::Patrons resultset that matches the passed filters; The primary objective of this method is 
+to simplify the use of commonly required filters which would normally require complex joins.
+
+Available filters (all are optional and may be used in combination):
+
+=over 4
+
+=item not_guarantors
+
+Boolean stating whether to filter out guarantors
+
+=item issued_before
+
+DateTime denoting last time the patrons were lent to
+
+=item has_issues
+
+Boolean stating whether to filter out patrons with current issues
+
+=item expired_before
+
+DateTime denoting beggining of period before which the users have expired
+
+=item amount_outstanding
+
+Number (decimal) denoting the total amount outstanding a use may have before being filtered out
+
+=item last_seen
+
+DateTime denoting the date since which the patron has not been seen
+
+=item branch
+
+String OR Arrayref of strings matching patron branchcodes to filter by
+
+=item categories
+
+String OR Arrayref of strings matching patron categories to filter by
+
+=item in_list
+
+String OR Arrayref of strings matching patron list ids to filter by
+
+=back
+
+=cut
+
+sub filter {
+    my ( $class, $filters ) = @_;
+
+    my $where = {};
+    my $attr  = {};
+    my $dtf   = Koha::Database->new->schema->storage->datetime_parser;
+
+    # Track joins to prevent unrequired double joins
+    my $joined_issues = 0;
+
+    # Filter out all guarantors
+    if ( $filters->{not_guarantors} ) {
+        my $guarantorList = Koha::Patrons->search(
+            { guarantorid => [ { '!=' => 0 }, { '!=' => undef } ] },
+            { select      => ['borrowernumber'] } )->_resultset->as_query;
+
+        push @{ $where->{'-and'} },
+          { 'me.borrowernumber' => { '-not_in' => $guarantorList } };
+    }
+
+    # Limit to patrons not issued to for at least X days
+    if ( $filters->{issued_before} ) {
+        push @{ $attr->{'join'} }, ( 'issues', 'old_issues' );
+        push @{ $attr->{'+select'} },
+          { max => 'old_issues.timestamp', '-as' => 'lastissue' };
+        push @{ $attr->{'+as'} }, 'lastissue';
+        push @{ $attr->{'having'}->{'-and'} },
+          { 'lastissue' =>
+              { '<' => $dtf->format_datetime( $filters->{issued_before} ) } };
+        $joined_issues++;
+    }
+
+    # Limit to patrons without any current issues
+    if ( defined( $filters->{has_issues} ) && !$filters->{has_issues} ) {
+        push @{ $attr->{'join'} }, ('issues') unless $joined_issues;
+        push @{ $attr->{'+select'} },
+          { max => 'issues.timestamp', '-as' => 'currentissue' };
+        push @{ $attr->{'+as'} }, 'currentissue';
+        push @{ $attr->{'having'}->{'-and'} }, { 'currentissue' => undef };
+    }
+
+    # Limit to patrons expired more than X days
+    if ( $filters->{expired_before} ) {
+        push @{ $where->{'-and'} },
+          { 'dateexpiry' =>
+              { '<' => $dtf->format_datetime( $filters->{expired_before} ) } };
+    }
+
+    # Limit to patrons not owing more than X in fines
+    if ( defined( $filters->{amount_outstanding} ) ) {
+        push @{ $attr->{'join'} }, 'accountlines';
+        push @{ $attr->{'+select'} },
+          { sum => 'accountlines.amountoutstanding', '-as' => 'outstanding' };
+        push @{ $attr->{'+as'} }, 'outstanding';
+        push @{ $attr->{'having'}->{'-and'} },
+          { outstanding => { '<=' => $filters->{amount_outstanding} } };
+    }
+
+    # Limit to patrons not seen for at least X days
+    if ( $filters->{last_seen} ) {
+        push @{ $where->{'-and'} },
+          { lastseen =>
+              { '<' => $dtf->format_datetime( $filters->{last_seen} ) } };
+    }
+
+    # Limit to patrons enrolled at branch X
+    if ( $filters->{branch} ) {
+        push @{ $where->{'-and'} }, { branchcode => $filters->{branch} };
+    }
+
+    # Limit to patrons belonging to categories specified
+    if ( @{ $filters->{categories} } ) {
+        push @{ $where->{'-and'} }, { categorycode => $filters->{categories} };
+    }
+
+    # Limit to patrons on patron list X
+    if ( $filters->{in_list} ) {
+        push @{ $attr->{'join'} }, 'patron_list_patrons';
+        push @{ $where->{'-and'} },
+          { 'patron_list_patrons.patron_list_id' => $filters->{in_list} };
+    }
+
+    # Group by borrowernumber
+    $attr->{group_by} = 'me.borrowernumber';
+
+    return $class->search( $where, $attr );
+}
+
 =head3 search_patrons_to_anonymise
 
     my $patrons = Koha::Patrons->search_patrons_to_anonymise( { before => $older_than_date, [ library => $library ] } );
-- 
2.17.1