1 package C4::Utils::DataTables::Members;
5 use C4::Members qw/GetMemberIssuesAndFines/;
6 use C4::Utils::DataTables;
11 my $searchmember = $params->{searchmember};
12 my $firstletter = $params->{firstletter};
13 my $categorycode = $params->{categorycode};
14 my $branchcode = $params->{branchcode};
15 my $searchtype = $params->{searchtype} || 'contain';
16 my $searchfieldstype = $params->{searchfieldstype} || 'standard';
17 my $dt_params = $params->{dt_params};
19 unless ( $searchmember ) {
20 $searchmember = $dt_params->{sSearch} // '';
23 my ($sth, $query, $iTotalRecords, $iTotalDisplayRecords);
24 my $dbh = C4::Context->dbh;
25 # Get the iTotalRecords DataTable variable
26 $query = "SELECT COUNT(borrowers.borrowernumber) FROM borrowers";
27 $sth = $dbh->prepare($query);
29 ($iTotalRecords) = $sth->fetchrow_array;
31 if ( $searchfieldstype eq 'dateofbirth' ) {
32 # Return an empty list if the date of birth is not correctly formatted
33 $searchmember = eval { output_pref( { str => $searchmember, dateformat => 'iso', dateonly => 1 } ); };
34 if ( $@ or not $searchmember ) {
37 iTotalDisplayRecords => 0,
43 # If branches are independent and user is not superlibrarian
44 # The search has to be only on the user branch
45 if ( C4::Context::only_my_library ) {
46 my $userenv = C4::Context->userenv;
47 $branchcode = $userenv->{'branch'};
52 borrowers.borrowernumber, borrowers.surname, borrowers.firstname,
53 borrowers.streetnumber, borrowers.streettype, borrowers.address,
54 borrowers.address2, borrowers.city, borrowers.state, borrowers.zipcode,
55 borrowers.country, cardnumber, borrowers.dateexpiry,
56 borrowers.borrowernotes, borrowers.branchcode, borrowers.email,
57 borrowers.userid, borrowers.dateofbirth, borrowers.categorycode,
58 categories.description AS category_description, categories.category_type,
60 my $from = "FROM borrowers
61 LEFT JOIN branches ON borrowers.branchcode = branches.branchcode
62 LEFT JOIN categories ON borrowers.categorycode = categories.categorycode";
65 if(defined $firstletter and $firstletter ne '') {
66 push @where_strs, "borrowers.surname LIKE ?";
67 push @where_args, "$firstletter%";
69 if(defined $categorycode and $categorycode ne '') {
70 push @where_strs, "borrowers.categorycode = ?";
71 push @where_args, $categorycode;
73 if(defined $branchcode and $branchcode ne '') {
74 push @where_strs, "borrowers.branchcode = ?";
75 push @where_args, $branchcode;
79 standard => 'surname,firstname,othernames,cardnumber,userid',
81 email => 'email,emailpro,B_email',
82 borrowernumber => 'borrowernumber',
84 phone => 'phone,phonepro,B_phone,altcontactphone,mobile',
85 address => 'streettype,address,address2,city,state,zipcode,country',
86 dateofbirth => 'dateofbirth',
91 # * is replaced with % for sql
92 $searchmember =~ s/\*/%/g;
94 # split into search terms
96 # consider coma as space
97 $searchmember =~ s/,/ /g;
98 if ( $searchtype eq 'contain' ) {
99 @terms = split / /, $searchmember;
101 @terms = ($searchmember);
104 foreach my $term (@terms) {
107 $term .= '%' # end with anything
109 $term = "%$term" # begin with anythin unless start_with
110 if $searchtype eq 'contain' && $term !~ /^%/;
113 for my $searchfield ( split /,/, $searchfields->{$searchfieldstype} ) {
114 push @where_strs_or, "borrowers." . $dbh->quote_identifier($searchfield) . " LIKE ?";
115 push @where_args, $term;
118 if ( C4::Context->preference('ExtendedPatronAttributes') and $searchmember ) {
119 my $matching_borrowernumbers = C4::Members::Attributes::SearchIdMatchingAttribute($searchmember);
121 for my $borrowernumber ( @$matching_borrowernumbers ) {
122 push @where_strs_or, "borrowers.borrowernumber = ?";
123 push @where_args, $borrowernumber;
127 push @where_strs, '('. join (' OR ', @where_strs_or) . ')'
132 $where = " WHERE " . join (" AND ", @where_strs) if @where_strs;
133 my $orderby = dt_build_orderby($dt_params);
136 # If iDisplayLength == -1, we want to display all patrons
137 if ( !$dt_params->{iDisplayLength} || $dt_params->{iDisplayLength} > -1 ) {
138 # In order to avoid sql injection
139 $dt_params->{iDisplayStart} =~ s/\D//g if defined($dt_params->{iDisplayStart});
140 $dt_params->{iDisplayLength} =~ s/\D//g if defined($dt_params->{iDisplayLength});
141 $dt_params->{iDisplayStart} //= 0;
142 $dt_params->{iDisplayLength} //= 20;
143 $limit = "LIMIT $dt_params->{iDisplayStart},$dt_params->{iDisplayLength}";
148 ($select ? $select : ""),
149 ($from ? $from : ""),
150 ($where ? $where : ""),
151 ($orderby ? $orderby : ""),
152 ($limit ? $limit : "")
154 $sth = $dbh->prepare($query);
155 $sth->execute(@where_args);
156 my $patrons = $sth->fetchall_arrayref({});
158 # Get the iTotalDisplayRecords DataTable variable
159 $query = "SELECT COUNT(borrowers.borrowernumber) " . $from . ($where ? $where : "");
160 $sth = $dbh->prepare($query);
161 $sth->execute(@where_args);
162 ($iTotalDisplayRecords) = $sth->fetchrow_array;
164 # Get some information on patrons
165 foreach my $patron (@$patrons) {
166 ($patron->{overdues}, $patron->{issues}, $patron->{fines}) =
167 GetMemberIssuesAndFines($patron->{borrowernumber});
168 if($patron->{dateexpiry} and $patron->{dateexpiry} ne '0000-00-00') {
169 $patron->{dateexpiry} = output_pref( { dt => dt_from_string( $patron->{dateexpiry}, 'iso'), dateonly => 1} );
171 $patron->{dateexpiry} = '';
173 $patron->{fines} = sprintf("%.2f", $patron->{fines} || 0);
177 iTotalRecords => $iTotalRecords,
178 iTotalDisplayRecords => $iTotalDisplayRecords,
188 C4::Utils::DataTables::Members - module for using DataTables with patrons
192 This module provides (one for the moment) routines used by the patrons search
198 my $dt_infos = C4::Utils::DataTables::Members->search($params);
200 $params is a hashref with some keys:
206 String to search in the borrowers sql table
210 Introduced to contain 1 letter but can contain more.
211 The search will done on the borrowers.surname field
215 Search patrons with this categorycode
219 Search patrons with this branchcode
223 Can be 'start_with' or 'contain' (default value). Used for the searchmember parameter.
225 =item searchfieldstype
227 Can be 'standard' (default value), 'email', 'borrowernumber', 'phone', 'address' or 'dateofbirth', 'sort1', 'sort2'
231 Is the reference of C4::Utils::DataTables::dt_get_params($input);
239 This file is part of Koha.
241 Copyright 2013 BibLibre
243 Koha is free software; you can redistribute it and/or modify it
244 under the terms of the GNU General Public License as published by
245 the Free Software Foundation; either version 3 of the License, or
246 (at your option) any later version.
248 Koha is distributed in the hope that it will be useful, but
249 WITHOUT ANY WARRANTY; without even the implied warranty of
250 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
251 GNU General Public License for more details.
253 You should have received a copy of the GNU General Public License
254 along with Koha; if not, see <http://www.gnu.org/licenses>.