From cad06e3c19895035f3213bb1a24d1a752176e242 Mon Sep 17 00:00:00 2001 From: Nick Clemens 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