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} || 'contain';
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 standard => 'surname,firstname,othernames,cardnumber,userid',
65 email => 'email,emailpro,B_email',
66 borrowernumber => 'borrowernumber',
68 phone => 'phone,phonepro,B_phone,altcontactphone,mobile',
69 address => 'streettype,address,address2,city,state,zipcode,country',
70 dateofbirth => 'dateofbirth',
75 # * is replaced with % for sql
76 $searchmember =~ s/\*/%/g;
78 # split into search terms
80 # consider coma as space
81 $searchmember =~ s/,/ /g;
82 if ( $searchtype eq 'contain' ) {
83 @terms = split / /, $searchmember;
85 @terms = ($searchmember);
88 foreach my $term (@terms) {
91 $term .= '%' # end with anything
93 $term = "%$term" # begin with anythin unless start_with
94 if $searchtype eq 'contain' && $term !~ /^%/;
97 for my $searchfield ( split /,/, $searchfields->{$searchfieldstype} ) {
98 push @where_strs_or, "borrowers." . $dbh->quote_identifier($searchfield) . " LIKE ?";
99 push @where_args, $term;
102 if ( C4::Context->preference('ExtendedPatronAttributes') and $searchmember ) {
103 my $matching_borrowernumbers = C4::Members::Attributes::SearchIdMatchingAttribute($searchmember);
105 for my $borrowernumber ( @$matching_borrowernumbers ) {
106 push @where_strs_or, "borrowers.borrowernumber = ?";
107 push @where_args, $borrowernumber;
111 push @where_strs, '('. join (' OR ', @where_strs_or) . ')'
116 $where = " WHERE " . join (" AND ", @where_strs) if @where_strs;
117 my $orderby = dt_build_orderby($dt_params);
120 # If iDisplayLength == -1, we want to display all patrons
121 if ( !$dt_params->{iDisplayLength} || $dt_params->{iDisplayLength} > -1 ) {
122 # In order to avoid sql injection
123 $dt_params->{iDisplayStart} =~ s/\D//g if defined($dt_params->{iDisplayStart});
124 $dt_params->{iDisplayLength} =~ s/\D//g if defined($dt_params->{iDisplayLength});
125 $dt_params->{iDisplayStart} //= 0;
126 $dt_params->{iDisplayLength} //= 20;
127 $limit = "LIMIT $dt_params->{iDisplayStart},$dt_params->{iDisplayLength}";
132 ($select ? $select : ""),
133 ($from ? $from : ""),
134 ($where ? $where : ""),
135 ($orderby ? $orderby : ""),
136 ($limit ? $limit : "")
138 my $sth = $dbh->prepare($query);
139 $sth->execute(@where_args);
140 my $patrons = $sth->fetchall_arrayref({});
142 # Get the iTotalDisplayRecords DataTable variable
143 $query = "SELECT COUNT(borrowers.borrowernumber) " . $from . ($where ? $where : "");
144 $sth = $dbh->prepare($query);
145 $sth->execute(@where_args);
146 ($iTotalDisplayRecords) = $sth->fetchrow_array;
148 # Get the iTotalRecords DataTable variable
149 $query = "SELECT COUNT(borrowers.borrowernumber) FROM borrowers";
150 $sth = $dbh->prepare($query);
152 ($iTotalRecords) = $sth->fetchrow_array;
154 # Get some information on patrons
155 foreach my $patron (@$patrons) {
156 ($patron->{overdues}, $patron->{issues}, $patron->{fines}) =
157 GetMemberIssuesAndFines($patron->{borrowernumber});
158 if($patron->{dateexpiry} and $patron->{dateexpiry} ne '0000-00-00') {
159 $patron->{dateexpiry} = output_pref( { dt => dt_from_string( $patron->{dateexpiry}, 'iso'), dateonly => 1} );
161 $patron->{dateexpiry} = '';
163 $patron->{fines} = sprintf("%.2f", $patron->{fines} || 0);
167 iTotalRecords => $iTotalRecords,
168 iTotalDisplayRecords => $iTotalDisplayRecords,
178 C4::Utils::DataTables::Members - module for using DataTables with patrons
182 This module provides (one for the moment) routines used by the patrons search
188 my $dt_infos = C4::Utils::DataTables::Members->search($params);
190 $params is a hashref with some keys:
196 String to search in the borrowers sql table
200 Introduced to contain 1 letter but can contain more.
201 The search will done on the borrowers.surname field
205 Search patrons with this categorycode
209 Search patrons with this branchcode
213 Can be 'contain' or 'start_with' (default value). Used for the searchmember parameter.
215 =item searchfieldstype
217 Can be 'standard' (default value), 'email', 'borrowernumber', 'phone', 'address' or 'dateofbirth', 'sort1', 'sort2'
221 Is the reference of C4::Utils::DataTables::dt_get_params($input);
229 This file is part of Koha.
231 Copyright 2013 BibLibre
233 Koha is free software; you can redistribute it and/or modify it
234 under the terms of the GNU General Public License as published by
235 the Free Software Foundation; either version 3 of the License, or
236 (at your option) any later version.
238 Koha is distributed in the hope that it will be useful, but
239 WITHOUT ANY WARRANTY; without even the implied warranty of
240 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
241 GNU General Public License for more details.
243 You should have received a copy of the GNU General Public License
244 along with Koha; if not, see <http://www.gnu.org/licenses>.