1 package C4::Utils::DataTables::Members;
4 use C4::Branch qw/onlymine/;
6 use C4::Members qw/GetMemberIssuesAndFines/;
7 use C4::Utils::DataTables;
12 my $searchmember = $params->{searchmember};
13 my $firstletter = $params->{firstletter};
14 my $categorycode = $params->{categorycode};
15 my $branchcode = $params->{branchcode};
16 my $searchtype = $params->{searchtype};
17 my $searchfieldstype = $params->{searchfieldstype} || 'standard';
18 my $dt_params = $params->{dt_params};
20 unless ( $searchmember ) {
21 $searchmember = $dt_params->{sSearch} // '';
24 my ($iTotalRecords, $iTotalDisplayRecords);
26 # If branches are independent and user is not superlibrarian
27 # The search has to be only on the user branch
28 if ( C4::Branch::onlymine ) {
29 my $userenv = C4::Context->userenv;
30 $branchcode = $userenv->{'branch'};
34 my $dbh = C4::Context->dbh;
36 borrowers.borrowernumber, borrowers.surname, borrowers.firstname,
37 borrowers.streetnumber, borrowers.streettype, borrowers.address,
38 borrowers.address2, borrowers.city, borrowers.state, borrowers.zipcode,
39 borrowers.country, cardnumber, borrowers.dateexpiry,
40 borrowers.borrowernotes, borrowers.branchcode, borrowers.email,
41 borrowers.userid, borrowers.dateofbirth, borrowers.categorycode,
42 categories.description AS category_description, categories.category_type,
44 my $from = "FROM borrowers
45 LEFT JOIN branches ON borrowers.branchcode = branches.branchcode
46 LEFT JOIN categories ON borrowers.categorycode = categories.categorycode";
49 if(defined $firstletter and $firstletter ne '') {
50 push @where_strs, "borrowers.surname LIKE ?";
51 push @where_args, "$firstletter%";
53 if(defined $categorycode and $categorycode ne '') {
54 push @where_strs, "borrowers.categorycode = ?";
55 push @where_args, $categorycode;
57 if(defined $branchcode and $branchcode ne '') {
58 push @where_strs, "borrowers.branchcode = ?";
59 push @where_args, $branchcode;
63 $searchmember =~ s/,/ /g if $searchmember;
65 standard => 'surname,firstname,othernames,cardnumber,userid',
66 email => 'email,emailpro,B_email',
67 borrowernumber => 'borrowernumber',
69 phone => 'phone,phonepro,B_phone,altcontactphone,mobile',
70 address => 'streettype,address,address2,city,state,zipcode,country',
71 dateofbirth => 'dateofbirth',
75 foreach my $term ( split / /, $searchmember) {
77 $searchmember =~ s/\*/%/g; # * is replaced with % for sql
78 $term .= '%' # end with anything
80 $term = "%$term" # begin with anythin unless start_with
81 if (defined $searchtype) && $searchtype eq "contain"
84 for my $searchfield ( split /,/, $searchfields->{$searchfieldstype} ) {
85 push @where_strs_or, "borrowers." . $dbh->quote_identifier($searchfield) . " LIKE ?";
86 push @where_args, $term;
89 if ( C4::Context->preference('ExtendedPatronAttributes') and $searchmember ) {
90 my $matching_borrowernumbers = C4::Members::Attributes::SearchIdMatchingAttribute($searchmember);
92 for my $borrowernumber ( @$matching_borrowernumbers ) {
93 push @where_strs_or, "borrowers.borrowernumber = ?";
94 push @where_args, $borrowernumber;
98 push @where_strs, '('. join (' OR ', @where_strs_or) . ')'
103 $where = " WHERE " . join (" AND ", @where_strs) if @where_strs;
104 my $orderby = dt_build_orderby($dt_params);
107 # If iDisplayLength == -1, we want to display all patrons
108 if ( !$dt_params->{iDisplayLength} || $dt_params->{iDisplayLength} > -1 ) {
109 # In order to avoid sql injection
110 $dt_params->{iDisplayStart} =~ s/\D//g if defined($dt_params->{iDisplayStart});
111 $dt_params->{iDisplayLength} =~ s/\D//g if defined($dt_params->{iDisplayLength});
112 $dt_params->{iDisplayStart} //= 0;
113 $dt_params->{iDisplayLength} //= 20;
114 $limit = "LIMIT $dt_params->{iDisplayStart},$dt_params->{iDisplayLength}";
119 ($select ? $select : ""),
120 ($from ? $from : ""),
121 ($where ? $where : ""),
122 ($orderby ? $orderby : ""),
123 ($limit ? $limit : "")
125 my $sth = $dbh->prepare($query);
126 $sth->execute(@where_args);
127 my $patrons = $sth->fetchall_arrayref({});
129 # Get the iTotalDisplayRecords DataTable variable
130 $query = "SELECT COUNT(borrowers.borrowernumber) " . $from . ($where ? $where : "");
131 $sth = $dbh->prepare($query);
132 $sth->execute(@where_args);
133 ($iTotalDisplayRecords) = $sth->fetchrow_array;
135 # Get the iTotalRecords DataTable variable
136 $query = "SELECT COUNT(borrowers.borrowernumber) FROM borrowers";
137 $sth = $dbh->prepare($query);
139 ($iTotalRecords) = $sth->fetchrow_array;
141 # Get some information on patrons
142 foreach my $patron (@$patrons) {
143 ($patron->{overdues}, $patron->{issues}, $patron->{fines}) =
144 GetMemberIssuesAndFines($patron->{borrowernumber});
145 if($patron->{dateexpiry} and $patron->{dateexpiry} ne '0000-00-00') {
146 $patron->{dateexpiry} = output_pref( { dt => dt_from_string( $patron->{dateexpiry}, 'iso'), dateonly => 1} );
148 $patron->{dateexpiry} = '';
150 $patron->{fines} = sprintf("%.2f", $patron->{fines} || 0);
154 iTotalRecords => $iTotalRecords,
155 iTotalDisplayRecords => $iTotalDisplayRecords,
165 C4::Utils::DataTables::Members - module for using DataTables with patrons
169 This module provides (one for the moment) routines used by the patrons search
175 my $dt_infos = C4::Utils::DataTables::Members->search($params);
177 $params is a hashref with some keys:
183 String to search in the borrowers sql table
187 Introduced to contain 1 letter but can contain more.
188 The search will done on the borrowers.surname field
192 Search patrons with this categorycode
196 Search patrons with this branchcode
200 Can be 'contain' or 'start_with'. Used for the searchmember parameter.
202 =item searchfieldstype
204 Can be 'standard', 'email', 'borrowernumber', 'phone', 'address' or 'dateofbirth', 'sort1', 'sort2'
208 Is the reference of C4::Utils::DataTables::dt_get_params($input);
216 This file is part of Koha.
218 Copyright 2013 BibLibre
220 Koha is free software; you can redistribute it and/or modify it
221 under the terms of the GNU General Public License as published by
222 the Free Software Foundation; either version 3 of the License, or
223 (at your option) any later version.
225 Koha is distributed in the hope that it will be useful, but
226 WITHOUT ANY WARRANTY; without even the implied warranty of
227 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
228 GNU General Public License for more details.
230 You should have received a copy of the GNU General Public License
231 along with Koha; if not, see <http://www.gnu.org/licenses>.