Attachment 125376 Details for Bug 29125 – Bug 29125: Use patron objects in C4::Utils::DataTables::Members.pm

[patch] Bug 29125: Use patron objects in C4::Utils::DataTables::Members.pm

Bug-29125-Use-patron-objects-in-C4UtilsDataTablesM.patch (text/plain), 10.80 KB, created by Nick Clemens (kidclamp) on 2021-09-27 18:38:22 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Nick Clemens (kidclamp)

Created: 2021-09-27 18:38:22 UTC

Size: 10.80 KB

patch

obsolete

>From cad06e3c19895035f3213bb1a24d1a752176e242 Mon Sep 17 00:00:00 2001
>From: Nick Clemens <nick@bywatersolutions.com>
>Date: Mon, 27 Sep 2021 18:37:01 +0000
>Subject: [PATCH] Bug 29125: Use patron objects in
> C4::Utils::DataTables::Members.pm
>
>---
> C4/Utils/DataTables/Members.pm | 152 ++++++++++++---------------------
> 1 file changed, 54 insertions(+), 98 deletions(-)
>
>diff --git a/C4/Utils/DataTables/Members.pm b/C4/Utils/DataTables/Members.pm
>index 3d677b8e01..d75ecf0914 100644
>--- a/C4/Utils/DataTables/Members.pm
>+++ b/C4/Utils/DataTables/Members.pm
>@@ -20,13 +20,20 @@ sub search {
>         $searchmember = $dt_params->{sSearch} // '';
>     }
> 
>+    my $search_params = {};
>+    my $prefetch = [];
>+
>     # If branches are independent and user is not superlibrarian
>     # The search has to be only on the user branch
>     my $userenv = C4::Context->userenv;
>     my $logged_in_user = Koha::Patrons->find( $userenv->{number} );
>     my @restricted_branchcodes = $logged_in_user->libraries_where_can_see_patrons;
>+    if ( @restricted_branchcodes ) {
>+        $search_params->{'me.branchcode'} = { -in => \@restricted_branchcodes };
>+    }
>+
>+
> 
>-    my ($sth, $query, $iTotalQuery, $iTotalRecords, $iTotalDisplayRecords);
>     my $dbh = C4::Context->dbh;
> 
>     # Get the module_bit from a given permission code
>@@ -34,27 +41,20 @@ sub search {
>         ($has_permission->{module_bit}) = $dbh->selectrow_array(q|
>             SELECT bit FROM userflags WHERE flag=?
>         |, undef, $has_permission->{permission});
>+        my $module_bit = $has_permission->{module_bit};
>+        $search_params->{'-and'} = [ '-or' => [
>+            \"me.flags & (1 << $module_bit)",
>+            {'me.flags' => 1},
>+            { -and => [
>+                {'user_permissions.module_bit' => $module_bit},
>+                {'user_permissions.code' => $has_permission->{subpermission}}
>+            ]}
>+        ]];
>+        push @{$prefetch}, 'user_permissions';
>     }
> 
>-    my (@where, @conditions);
>     # Get the iTotalRecords DataTable variable
>-    $iTotalQuery = "SELECT COUNT(borrowers.borrowernumber) FROM borrowers";
>-    if ( $has_permission ) {
>-        $iTotalQuery .= ' LEFT JOIN user_permissions ON borrowers.borrowernumber=user_permissions.borrowernumber';
>-        $iTotalQuery .= ' AND module_bit=? AND code=?';
>-        push @conditions, $has_permission->{module_bit}, $has_permission->{subpermission};
>-    }
>-
>-    if ( @restricted_branchcodes ) {
>-        push @where, "borrowers.branchcode IN (" . join( ',', ('?') x @restricted_branchcodes ) . ")";
>-        push @conditions, @restricted_branchcodes;
>-    }
>-    if ( $has_permission ) {
>-        push @where, '( borrowers.flags = 1 OR borrowers.flags & (1 << ?) OR module_bit=? AND code=? )';
>-        push @conditions, ($has_permission->{module_bit}) x 2, $has_permission->{subpermission};
>-    }
>-    $iTotalQuery .= ' WHERE ' . join ' AND ', @where if @where;
>-    ($iTotalRecords) = $dbh->selectrow_array( $iTotalQuery, undef, @conditions );
>+    my $iTotalRecords = Koha::Patrons->search($search_params,{ prefetch => $prefetch })->count();
> 
>     # Do that after iTotalQuery!
>     if ( defined $branchcode and $branchcode ) {
>@@ -63,6 +63,7 @@ sub search {
>                 ? ($branchcode)
>                 : (undef) # Do not return any results
>             : ($branchcode);
>+        $search_params->{'me.branchcode'} = { -in => \@restricted_branchcodes };
>     }
> 
>     if ( $searchfieldstype eq 'dateofbirth' ) {
>@@ -77,39 +78,12 @@ sub search {
>         }
>     }
> 
>-    my $select = "SELECT
>-        borrowers.borrowernumber, borrowers.surname, borrowers.firstname,
>-        borrowers.othernames,
>-        borrowers.flags,
>-        borrowers.streetnumber, borrowers.streettype, borrowers.address,
>-        borrowers.address2, borrowers.city, borrowers.state, borrowers.zipcode,
>-        borrowers.country, cardnumber, borrowers.dateexpiry,
>-        borrowers.borrowernotes, borrowers.branchcode, borrowers.email,
>-        borrowers.userid, borrowers.dateofbirth, borrowers.categorycode,
>-        categories.description AS category_description, categories.category_type,
>-        branches.branchname, borrowers.phone";
>-    my $from = "FROM borrowers
>-                LEFT JOIN branches ON borrowers.branchcode = branches.branchcode
>-                LEFT JOIN categories ON borrowers.categorycode = categories.categorycode";
>-    my @where_args;
>-    if ( $has_permission ) {
>-        $from .= '
>-                LEFT JOIN user_permissions ON borrowers.borrowernumber=user_permissions.borrowernumber
>-                AND module_bit=? AND code=?';
>-        push @where_args, $has_permission->{module_bit}, $has_permission->{subpermission};
>-    }
>-    my @where_strs;
>+    push @{$prefetch}, ('branchcode', 'categorycode');
>     if(defined $firstletter and $firstletter ne '') {
>-        push @where_strs, "borrowers.surname LIKE ?";
>-        push @where_args, "$firstletter%";
>+        $search_params->{surname} = { -like => $firstletter . '%' };
>     }
>     if(defined $categorycode and $categorycode ne '') {
>-        push @where_strs, "borrowers.categorycode = ?";
>-        push @where_args, $categorycode;
>-    }
>-    if(@restricted_branchcodes ) {
>-        push @where_strs, "borrowers.branchcode IN (" . join( ',', ('?') x @restricted_branchcodes ) . ")";
>-        push @where_args, @restricted_branchcodes;
>+        $search_params->{'`me`.`categorycode`'} = $categorycode;
>     }
> 
>     my $searchfields = {
>@@ -147,40 +121,28 @@ sub search {
>               if $searchtype eq 'contain' && $term !~ /^%/;
>         }
> 
>-        my @where_strs_or;
>         if ( defined $searchfields->{$searchfieldstype} ) {
>             for my $searchfield ( split /,/, $searchfields->{$searchfieldstype} ) {
>-                push @where_strs_or, "borrowers." . $dbh->quote_identifier($searchfield) . " LIKE ?";
>-                push @where_args, $term;
>+                push @{$search_params->{'-or'}}, {$searchfield => { -like => $term }};
>             }
>         } else {
>-            push @where_strs_or, "borrowers." . $dbh->quote_identifier($searchfieldstype) . " LIKE ?";
>-            push @where_args, $term;
>+            push @{$search_params->{'-or'}}, {'me.' . $dbh->quote_identifier($searchfieldstype) => { -like => $term }};
>         }
> 
> 
>         if ( $searchfieldstype eq 'standard' and C4::Context->preference('ExtendedPatronAttributes') and $searchmember ) {
>-            my @matching_borrowernumbers = Koha::Patrons->filter_by_attribute_value($searchmember)->get_column('borrowernumber');
>-
>-            for my $borrowernumber ( @matching_borrowernumbers ) {
>-                push @where_strs_or, "borrowers.borrowernumber = ?";
>-                push @where_args, $borrowernumber;
>-            }
>+            push @{$search_params->{'-or'}}, { '-and' => [
>+                {'borrower_attribute_types.staff_searchable' => 1},
>+                {'borrower_attributes.attribute' => { -like => '%'.$term.'%' }}
>+            ]};
>+            push @{$prefetch}, ({ borrower_attributes => 'borrower_attribute_types'} );
>         }
> 
>-        push @where_strs, '('. join (' OR ', @where_strs_or) . ')'
>-            if @where_strs_or;
>     }
> 
>-    if ( $has_permission ) {
>-        push @where_strs, '( borrowers.flags = 1 OR borrowers.flags & (1 << ?) OR module_bit=? AND code=? )';
>-        push @where_args, ($has_permission->{module_bit}) x 2, $has_permission->{subpermission};
>-    }
>-
>-    my $where = @where_strs ? " WHERE " . join (" AND ", @where_strs) : undef;
>     my $orderby = dt_build_orderby($dt_params);
> 
>-    my $limit;
>+    my $options;
>     # If iDisplayLength == -1, we want to display all patrons
>     if ( !$dt_params->{iDisplayLength} || $dt_params->{iDisplayLength} > -1 ) {
>         # In order to avoid sql injection
>@@ -188,49 +150,43 @@ sub search {
>         $dt_params->{iDisplayLength} =~ s/\D//g if defined($dt_params->{iDisplayLength});
>         $dt_params->{iDisplayStart} //= 0;
>         $dt_params->{iDisplayLength} //= 20;
>-        $limit = "LIMIT $dt_params->{iDisplayStart},$dt_params->{iDisplayLength}";
>+        $options->{offset} = $dt_params->{iDisplayStart};
>+        $options->{rows} = $dt_params->{iDisplayLength} ;
>     }
> 
>-    $query = join(
>-        " ",
>-        ($select ? $select : ""),
>-        ($from ? $from : ""),
>-        ($where ? $where : ""),
>-        ($orderby ? $orderby : ""),
>-        ($limit ? $limit : "")
>-    );
>-    $sth = $dbh->prepare($query);
>-    $sth->execute(@where_args);
>-    my $patrons = $sth->fetchall_arrayref({});
> 
>     # Get the iTotalDisplayRecords DataTable variable
>-    $query = "SELECT COUNT(borrowers.borrowernumber) " . $from . ($where ? $where : "");
>-    $sth = $dbh->prepare($query);
>-    $sth->execute(@where_args);
>-    ($iTotalDisplayRecords) = $sth->fetchrow_array;
>+    my $iTotalDisplayRecords = Koha::Patrons->search($search_params,{ prefetch => $prefetch })->count();
>+
> 
>     # Get some information on patrons
>-    foreach my $patron (@$patrons) {
>-        my $patron_object = Koha::Patrons->find( $patron->{borrowernumber} );
>-        $patron->{overdues} = $patron_object->get_overdues->count;
>-        $patron->{issues} = $patron_object->checkouts->count;
>-        $patron->{age} = $patron_object->get_age;
>-        my $balance = $patron_object->account->balance;
>-        # FIXME Should be formatted from the template
>-        $patron->{fines} = sprintf("%.2f", $balance);
>-
>-        if( $patron->{dateexpiry} ) {
>+    push @{$prefetch}, ('issues', 'accountlines');
>+    $options->{prefetch} = $prefetch;
>+    my $patron_objects = Koha::Patrons->search($search_params,$options);
>+    my @patrons;
>+    while ( my $patron = $patron_objects->next ) {
>+        my $simple_patron = $patron->unblessed;
>+        $simple_patron->{overdues} = $patron->get_overdues->count;
>+        $simple_patron->{issues} = $patron->checkouts->count;
>+        $simple_patron->{age} = $patron->get_age;
>+        $simple_patron->{fines} = sprintf("%.2f",$patron->account->lines->total_outstanding);
>+        $simple_patron->{'branchname'} = $patron->library->branchname;
>+        $simple_patron->{'category_description'} = $patron->category->description;
>+        $simple_patron->{'category_type'} = $patron->category->category_type;
>+
>+        if( $simple_patron->{dateexpiry} ) {
>             # FIXME We should not format the date here, do it in template-side instead
>-            $patron->{dateexpiry} = output_pref( { dt => scalar dt_from_string( $patron->{dateexpiry}, 'iso'), dateonly => 1} );
>+            $simple_patron->{dateexpiry} = output_pref( { dt => scalar dt_from_string( $simple_patron->{dateexpiry}, 'iso'), dateonly => 1} );
>         } else {
>-            $patron->{dateexpiry} = '';
>+            $simple_patron->{dateexpiry} = '';
>         }
>+        push @patrons, $simple_patron;
>     }
> 
>     return {
>         iTotalRecords => $iTotalRecords,
>         iTotalDisplayRecords => $iTotalDisplayRecords,
>-        patrons => $patrons
>+        patrons => \@patrons
>     }
> }
> 
>-- 
>2.20.1

Actions: View | Diff | Splinter Review

Attachments on bug 29125: 125374 | 125375 | 125376 | 125979