1 package C4::Utils::DataTables::Members;
5 use C4::Utils::DataTables;
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};
19 unless ( $searchmember ) {
20 $searchmember = $dt_params->{sSearch} // '';
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;
29 my ($sth, $query, $iTotalQuery, $iTotalRecords, $iTotalDisplayRecords);
30 my $dbh = C4::Context->dbh;
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});
39 # Get the iTotalRecords DataTable variable
40 $iTotalQuery = "SELECT COUNT(borrowers.borrowernumber) FROM borrowers";
41 if ( $has_permission ) {
42 $iTotalQuery .= ' LEFT JOIN user_permissions on borrowers.borrowernumber=user_permissions.borrowernumber';
45 my (@where, @conditions);
46 if ( @restricted_branchcodes ) {
47 push @where, "borrowers.branchcode IN (" . join( ',', ('?') x @restricted_branchcodes ) . ")";
48 push @conditions, @restricted_branchcodes;
50 if ( $has_permission ) {
51 push @where, '( borrowers.flags = 1 OR borrowers.flags & (1 << ?) OR module_bit=? AND code=? )';
52 push @conditions, ($has_permission->{module_bit}) x 2, $has_permission->{subpermission};
54 $iTotalQuery .= ' WHERE ' . join ' AND ', @where if @where;
55 ($iTotalRecords) = $dbh->selectrow_array( $iTotalQuery, undef, @conditions );
57 # Do that after iTotalQuery!
58 if ( defined $branchcode and $branchcode ) {
59 @restricted_branchcodes = @restricted_branchcodes
60 ? grep { $_ eq $branchcode } @restricted_branchcodes
62 : (undef) # Do not return any results
66 if ( $searchfieldstype eq 'dateofbirth' ) {
67 # Return an empty list if the date of birth is not correctly formatted
68 $searchmember = eval { output_pref( { str => $searchmember, dateformat => 'iso', dateonly => 1 } ); };
69 if ( $@ or not $searchmember ) {
71 iTotalRecords => $iTotalRecords,
72 iTotalDisplayRecords => 0,
79 borrowers.borrowernumber, borrowers.surname, borrowers.firstname,
82 borrowers.streetnumber, borrowers.streettype, borrowers.address,
83 borrowers.address2, borrowers.city, borrowers.state, borrowers.zipcode,
84 borrowers.country, cardnumber, borrowers.dateexpiry,
85 borrowers.borrowernotes, borrowers.branchcode, borrowers.email,
86 borrowers.userid, borrowers.dateofbirth, borrowers.categorycode,
87 categories.description AS category_description, categories.category_type,
88 branches.branchname, borrowers.phone";
89 my $from = "FROM borrowers
90 LEFT JOIN branches ON borrowers.branchcode = branches.branchcode
91 LEFT JOIN categories ON borrowers.categorycode = categories.categorycode";
92 if ( $has_permission ) {
94 LEFT JOIN user_permissions on borrowers.borrowernumber=user_permissions.borrowernumber';
98 if(defined $firstletter and $firstletter ne '') {
99 push @where_strs, "borrowers.surname LIKE ?";
100 push @where_args, "$firstletter%";
102 if(defined $categorycode and $categorycode ne '') {
103 push @where_strs, "borrowers.categorycode = ?";
104 push @where_args, $categorycode;
106 if(@restricted_branchcodes ) {
107 push @where_strs, "borrowers.branchcode IN (" . join( ',', ('?') x @restricted_branchcodes ) . ")";
108 push @where_args, @restricted_branchcodes;
112 standard => C4::Context->preference('DefaultPatronSearchFields') || 'surname,firstname,othernames,cardnumber,userid',
113 email => 'email,emailpro,B_email',
114 borrowernumber => 'borrowernumber',
115 phone => 'phone,phonepro,B_phone,altcontactphone,mobile',
116 address => 'streetnumber,streettype,address,address2,city,state,zipcode,country',
119 # * is replaced with % for sql
120 $searchmember =~ s/\*/%/g;
122 # split into search terms
124 # consider coma as space
125 $searchmember =~ s/,/ /g;
126 if ( $searchtype eq 'contain' ) {
127 @terms = split / /, $searchmember;
129 @terms = ($searchmember);
132 foreach my $term (@terms) {
135 my $term_dt = eval { local $SIG{__WARN__} = {}; output_pref( { str => $term, dateonly => 1, dateformat => 'sql' } ); };
140 $term .= '%' # end with anything
142 $term = "%$term" # begin with anythin unless start_with
143 if $searchtype eq 'contain' && $term !~ /^%/;
147 if ( defined $searchfields->{$searchfieldstype} ) {
148 for my $searchfield ( split /,/, $searchfields->{$searchfieldstype} ) {
149 push @where_strs_or, "borrowers." . $dbh->quote_identifier($searchfield) . " LIKE ?";
150 push @where_args, $term;
153 push @where_strs_or, "borrowers." . $dbh->quote_identifier($searchfieldstype) . " LIKE ?";
154 push @where_args, $term;
158 if ( $searchfieldstype eq 'standard' and C4::Context->preference('ExtendedPatronAttributes') and $searchmember ) {
159 my @matching_borrowernumbers = Koha::Patrons->filter_by_attribute_value($searchmember)->get_column('borrowernumber');
161 for my $borrowernumber ( @matching_borrowernumbers ) {
162 push @where_strs_or, "borrowers.borrowernumber = ?";
163 push @where_args, $borrowernumber;
167 push @where_strs, '('. join (' OR ', @where_strs_or) . ')'
171 if ( $has_permission ) {
172 push @where_strs, '( borrowers.flags = 1 OR borrowers.flags & (1 << ?) OR module_bit=? AND code=? )';
173 push @where_args, ($has_permission->{module_bit}) x 2, $has_permission->{subpermission};
176 my $where = @where_strs ? " WHERE " . join (" AND ", @where_strs) : undef;
177 my $orderby = dt_build_orderby($dt_params);
180 # If iDisplayLength == -1, we want to display all patrons
181 if ( !$dt_params->{iDisplayLength} || $dt_params->{iDisplayLength} > -1 ) {
182 # In order to avoid sql injection
183 $dt_params->{iDisplayStart} =~ s/\D//g if defined($dt_params->{iDisplayStart});
184 $dt_params->{iDisplayLength} =~ s/\D//g if defined($dt_params->{iDisplayLength});
185 $dt_params->{iDisplayStart} //= 0;
186 $dt_params->{iDisplayLength} //= 20;
187 $limit = "LIMIT $dt_params->{iDisplayStart},$dt_params->{iDisplayLength}";
192 ($select ? $select : ""),
193 ($from ? $from : ""),
194 ($where ? $where : ""),
195 ($orderby ? $orderby : ""),
196 ($limit ? $limit : "")
198 $sth = $dbh->prepare($query);
199 $sth->execute(@where_args);
200 my $patrons = $sth->fetchall_arrayref({});
202 # Get the iTotalDisplayRecords DataTable variable
203 $query = "SELECT COUNT(borrowers.borrowernumber) " . $from . ($where ? $where : "");
204 $sth = $dbh->prepare($query);
205 $sth->execute(@where_args);
206 ($iTotalDisplayRecords) = $sth->fetchrow_array;
208 # Get some information on patrons
209 foreach my $patron (@$patrons) {
210 my $patron_object = Koha::Patrons->find( $patron->{borrowernumber} );
211 $patron->{overdues} = $patron_object->get_overdues->count;
212 $patron->{issues} = $patron_object->checkouts->count;
213 my $balance = $patron_object->account->balance;
214 # FIXME Should be formatted from the template
215 $patron->{fines} = sprintf("%.2f", $balance);
217 if( $patron->{dateexpiry} ) {
218 # FIXME We should not format the date here, do it in template-side instead
219 $patron->{dateexpiry} = output_pref( { dt => scalar dt_from_string( $patron->{dateexpiry}, 'iso'), dateonly => 1} );
221 $patron->{dateexpiry} = '';
226 iTotalRecords => $iTotalRecords,
227 iTotalDisplayRecords => $iTotalDisplayRecords,
237 C4::Utils::DataTables::Members - module for using DataTables with patrons
241 This module provides (one for the moment) routines used by the patrons search
247 my $dt_infos = C4::Utils::DataTables::Members->search($params);
249 $params is a hashref with some keys:
255 String to search in the borrowers sql table
259 Introduced to contain 1 letter but can contain more.
260 The search will done on the borrowers.surname field
264 Search patrons with this categorycode
268 Search patrons with this branchcode
272 Can be 'start_with' or 'contain' (default value). Used for the searchmember parameter.
274 =item searchfieldstype
276 Can be 'standard' (default value), 'email', 'borrowernumber', 'phone', 'address' or 'dateofbirth', 'sort1', 'sort2'
280 Is the reference of C4::Utils::DataTables::dt_get_params($input);
288 This file is part of Koha.
290 Copyright 2013 BibLibre
292 Koha is free software; you can redistribute it and/or modify it
293 under the terms of the GNU General Public License as published by
294 the Free Software Foundation; either version 3 of the License, or
295 (at your option) any later version.
297 Koha is distributed in the hope that it will be useful, but
298 WITHOUT ANY WARRANTY; without even the implied warranty of
299 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
300 GNU General Public License for more details.
302 You should have received a copy of the GNU General Public License
303 along with Koha; if not, see <http://www.gnu.org/licenses>.