C4/Utils/DataTables/Members.pm

   1 package C4::Utils::DataTables::Members;
   2
   3 use Modern::Perl;
   4 use C4::Context;
   5 use C4::Utils::DataTables;
   6 use Koha::DateUtils;
   7
   8 sub search {
   9     my ( $params ) = @_;
  10     my $searchmember = $params->{searchmember};
  11     my $firstletter = $params->{firstletter};
  12     my $categorycode = $params->{categorycode};
  13     my $branchcode = $params->{branchcode};
  14     my $searchtype = $params->{searchtype} || 'contain';
  15     my $searchfieldstype = $params->{searchfieldstype} || 'standard';
  16     my $has_permission = $params->{has_permission};
  17     my $dt_params = $params->{dt_params};
  18
  19     unless ( $searchmember ) {
  20         $searchmember = $dt_params->{sSearch} // '';
  21     }
  22
  23     # If branches are independent and user is not superlibrarian
  24     # The search has to be only on the user branch
  25     my $userenv = C4::Context->userenv;
  26     my $logged_in_user = Koha::Patrons->find( $userenv->{number} );
  27     my @restricted_branchcodes = $logged_in_user->libraries_where_can_see_patrons;
  28
  29     my ($sth, $query, $iTotalQuery, $iTotalRecords, $iTotalDisplayRecords);
  30     my $dbh = C4::Context->dbh;
  31
  32     # Get the module_bit from a given permission code
  33     if ( $has_permission ) {
  34         ($has_permission->{module_bit}) = $dbh->selectrow_array(q|
  35             SELECT bit FROM userflags WHERE flag=?
  36         |, undef, $has_permission->{permission});
  37     }
  38
  39     my (@where, @conditions);
  40     # Get the iTotalRecords DataTable variable
  41     $iTotalQuery = "SELECT COUNT(borrowers.borrowernumber) FROM borrowers";
  42     if ( $has_permission ) {
  43         $iTotalQuery .= ' LEFT JOIN user_permissions ON borrowers.borrowernumber=user_permissions.borrowernumber';
  44         $iTotalQuery .= ' AND module_bit=? AND code=?';
  45         push @conditions, $has_permission->{module_bit}, $has_permission->{subpermission};
  46     }
  47
  48     if ( @restricted_branchcodes ) {
  49         push @where, "borrowers.branchcode IN (" . join( ',', ('?') x @restricted_branchcodes ) . ")";
  50         push @conditions, @restricted_branchcodes;
  51     }
  52     if ( $has_permission ) {
  53         push @where, '( borrowers.flags = 1 OR borrowers.flags & (1 << ?) OR module_bit=? AND code=? )';
  54         push @conditions, ($has_permission->{module_bit}) x 2, $has_permission->{subpermission};
  55     }
  56     $iTotalQuery .= ' WHERE ' . join ' AND ', @where if @where;
  57     ($iTotalRecords) = $dbh->selectrow_array( $iTotalQuery, undef, @conditions );
  58
  59     # Do that after iTotalQuery!
  60     if ( defined $branchcode and $branchcode ) {
  61         @restricted_branchcodes = @restricted_branchcodes
  62             ? grep ({ $_ eq $branchcode } @restricted_branchcodes)
  63                 ? ($branchcode)
  64                 : (undef) # Do not return any results
  65             : ($branchcode);
  66     }
  67
  68     if ( $searchfieldstype eq 'dateofbirth' ) {
  69         # Return an empty list if the date of birth is not correctly formatted
  70         $searchmember = eval { output_pref( { str => $searchmember, dateformat => 'iso', dateonly => 1 } ); };
  71         if ( $@ or not $searchmember ) {
  72             return {
  73                 iTotalRecords        => $iTotalRecords,
  74                 iTotalDisplayRecords => 0,
  75                 patrons              => [],
  76             };
  77         }
  78     }
  79
  80     my $select = "SELECT
  81         borrowers.borrowernumber, borrowers.surname, borrowers.firstname,
  82         borrowers.othernames,
  83         borrowers.flags,
  84         borrowers.streetnumber, borrowers.streettype, borrowers.address,
  85         borrowers.address2, borrowers.city, borrowers.state, borrowers.zipcode,
  86         borrowers.country, cardnumber, borrowers.dateexpiry,
  87         borrowers.borrowernotes, borrowers.branchcode, borrowers.email,
  88         borrowers.userid, borrowers.dateofbirth, borrowers.categorycode,
  89         categories.description AS category_description, categories.category_type,
  90         branches.branchname, borrowers.phone";
  91     my $from = "FROM borrowers
  92                 LEFT JOIN branches ON borrowers.branchcode = branches.branchcode
  93                 LEFT JOIN categories ON borrowers.categorycode = categories.categorycode";
  94     my @where_args;
  95     if ( $has_permission ) {
  96         $from .= '
  97                 LEFT JOIN user_permissions ON borrowers.borrowernumber=user_permissions.borrowernumber
  98                 AND module_bit=? AND code=?';
  99         push @where_args, $has_permission->{module_bit}, $has_permission->{subpermission};
 100     }
 101     my @where_strs;
 102     if(defined $firstletter and $firstletter ne '') {
 103         push @where_strs, "borrowers.surname LIKE ?";
 104         push @where_args, "$firstletter%";
 105     }
 106     if(defined $categorycode and $categorycode ne '') {
 107         push @where_strs, "borrowers.categorycode = ?";
 108         push @where_args, $categorycode;
 109     }
 110     if(@restricted_branchcodes ) {
 111         push @where_strs, "borrowers.branchcode IN (" . join( ',', ('?') x @restricted_branchcodes ) . ")";
 112         push @where_args, @restricted_branchcodes;
 113     }
 114
 115     my $searchfields = {
 116         standard => C4::Context->preference('DefaultPatronSearchFields') || 'surname,firstname,othernames,cardnumber,userid',
 117         email => 'email,emailpro,B_email',
 118         borrowernumber => 'borrowernumber',
 119         phone => 'phone,phonepro,B_phone,altcontactphone,mobile',
 120         address => 'streetnumber,streettype,address,address2,city,state,zipcode,country',
 121     };
 122
 123     # * is replaced with % for sql
 124     $searchmember =~ s/\*/%/g;
 125
 126     # split into search terms
 127     my @terms;
 128     # consider coma as space
 129     $searchmember =~ s/,/ /g;
 130     if ( $searchtype eq 'contain' ) {
 131        @terms = split / /, $searchmember;
 132     } else {
 133        @terms = ($searchmember);
 134     }
 135
 136     foreach my $term (@terms) {
 137         next unless $term;
 138
 139         my $term_dt = eval { local $SIG{__WARN__} = {}; output_pref( { str => $term, dateonly => 1, dateformat => 'sql' } ); };
 140
 141         if ($term_dt) {
 142             $term = $term_dt;
 143         } else {
 144             $term .= '%'    # end with anything
 145               if $term !~ /%$/;
 146             $term = "%$term"    # begin with anythin unless start_with
 147               if $searchtype eq 'contain' && $term !~ /^%/;
 148         }
 149
 150         my @where_strs_or;
 151         if ( defined $searchfields->{$searchfieldstype} ) {
 152             for my $searchfield ( split /,/, $searchfields->{$searchfieldstype} ) {
 153                 push @where_strs_or, "borrowers." . $dbh->quote_identifier($searchfield) . " LIKE ?";
 154                 push @where_args, $term;
 155             }
 156         } else {
 157             push @where_strs_or, "borrowers." . $dbh->quote_identifier($searchfieldstype) . " LIKE ?";
 158             push @where_args, $term;
 159         }
 160
 161
 162         if ( $searchfieldstype eq 'standard' and C4::Context->preference('ExtendedPatronAttributes') and $searchmember ) {
 163             my @matching_borrowernumbers = Koha::Patrons->filter_by_attribute_value($searchmember)->get_column('borrowernumber');
 164
 165             for my $borrowernumber ( @matching_borrowernumbers ) {
 166                 push @where_strs_or, "borrowers.borrowernumber = ?";
 167                 push @where_args, $borrowernumber;
 168             }
 169         }
 170
 171         push @where_strs, '('. join (' OR ', @where_strs_or) . ')'
 172             if @where_strs_or;
 173     }
 174
 175     if ( $has_permission ) {
 176         push @where_strs, '( borrowers.flags = 1 OR borrowers.flags & (1 << ?) OR module_bit=? AND code=? )';
 177         push @where_args, ($has_permission->{module_bit}) x 2, $has_permission->{subpermission};
 178     }
 179
 180     my $where = @where_strs ? " WHERE " . join (" AND ", @where_strs) : undef;
 181     my $orderby = dt_build_orderby($dt_params);
 182
 183     my $limit;
 184     # If iDisplayLength == -1, we want to display all patrons
 185     if ( !$dt_params->{iDisplayLength} || $dt_params->{iDisplayLength} > -1 ) {
 186         # In order to avoid sql injection
 187         $dt_params->{iDisplayStart} =~ s/\D//g if defined($dt_params->{iDisplayStart});
 188         $dt_params->{iDisplayLength} =~ s/\D//g if defined($dt_params->{iDisplayLength});
 189         $dt_params->{iDisplayStart} //= 0;
 190         $dt_params->{iDisplayLength} //= 20;
 191         $limit = "LIMIT $dt_params->{iDisplayStart},$dt_params->{iDisplayLength}";
 192     }
 193
 194     $query = join(
 195         " ",
 196         ($select ? $select : ""),
 197         ($from ? $from : ""),
 198         ($where ? $where : ""),
 199         ($orderby ? $orderby : ""),
 200         ($limit ? $limit : "")
 201     );
 202     $sth = $dbh->prepare($query);
 203     $sth->execute(@where_args);
 204     my $patrons = $sth->fetchall_arrayref({});
 205
 206     # Get the iTotalDisplayRecords DataTable variable
 207     $query = "SELECT COUNT(borrowers.borrowernumber) " . $from . ($where ? $where : "");
 208     $sth = $dbh->prepare($query);
 209     $sth->execute(@where_args);
 210     ($iTotalDisplayRecords) = $sth->fetchrow_array;
 211
 212     # Get some information on patrons
 213     foreach my $patron (@$patrons) {
 214         my $patron_object = Koha::Patrons->find( $patron->{borrowernumber} );
 215         $patron->{overdues} = $patron_object->get_overdues->count;
 216         $patron->{issues} = $patron_object->checkouts->count;
 217         $patron->{age} = $patron_object->get_age;
 218         my $balance = $patron_object->account->balance;
 219         # FIXME Should be formatted from the template
 220         $patron->{fines} = sprintf("%.2f", $balance);
 221
 222         if( $patron->{dateexpiry} ) {
 223             # FIXME We should not format the date here, do it in template-side instead
 224             $patron->{dateexpiry} = output_pref( { dt => scalar dt_from_string( $patron->{dateexpiry}, 'iso'), dateonly => 1} );
 225         } else {
 226             $patron->{dateexpiry} = '';
 227         }
 228     }
 229
 230     return {
 231         iTotalRecords => $iTotalRecords,
 232         iTotalDisplayRecords => $iTotalDisplayRecords,
 233         patrons => $patrons
 234     }
 235 }
 236
 237 1;
 238 __END__
 239
 240 =head1 NAME
 241
 242 C4::Utils::DataTables::Members - module for using DataTables with patrons
 243
 244 =head1 SYNOPSIS
 245
 246 This module provides (one for the moment) routines used by the patrons search
 247
 248 =head2 FUNCTIONS
 249
 250 =head3 search
 251
 252     my $dt_infos = C4::Utils::DataTables::Members->search($params);
 253
 254 $params is a hashref with some keys:
 255
 256 =over 4
 257
 258 =item searchmember
 259
 260   String to search in the borrowers sql table
 261
 262 =item firstletter
 263
 264   Introduced to contain 1 letter but can contain more.
 265   The search will done on the borrowers.surname field
 266
 267 =item categorycode
 268
 269   Search patrons with this categorycode
 270
 271 =item branchcode
 272
 273   Search patrons with this branchcode
 274
 275 =item searchtype
 276
 277   Can be 'start_with' or 'contain' (default value). Used for the searchmember parameter.
 278
 279 =item searchfieldstype
 280
 281   Can be 'standard' (default value), 'email', 'borrowernumber', 'phone', 'address' or 'dateofbirth', 'sort1', 'sort2'
 282
 283 =item dt_params
 284
 285   Is the reference of C4::Utils::DataTables::dt_get_params($input);
 286
 287 =cut
 288
 289 =back
 290
 291 =head1 LICENSE
 292
 293 This file is part of Koha.
 294
 295 Copyright 2013 BibLibre
 296
 297 Koha is free software; you can redistribute it and/or modify it
 298 under the terms of the GNU General Public License as published by
 299 the Free Software Foundation; either version 3 of the License, or
 300 (at your option) any later version.
 301
 302 Koha is distributed in the hope that it will be useful, but
 303 WITHOUT ANY WARRANTY; without even the implied warranty of
 304 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
 305 GNU General Public License for more details.
 306
 307 You should have received a copy of the GNU General Public License
 308 along with Koha; if not, see <http://www.gnu.org/licenses>.