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