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} || 'start_with';
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',
64 email => 'email,emailpro,B_email',
65 borrowernumber => 'borrowernumber',
67 phone => 'phone,phonepro,B_phone,altcontactphone,mobile',
68 address => 'streettype,address,address2,city,state,zipcode,country',
69 dateofbirth => 'dateofbirth',
74 # * is replaced with % for sql
75 $searchmember =~ s/\*/%/g;
77 # split into search terms
79 # consider coma as space
80 $searchmember =~ s/,/ /g;
81 if ( $searchtype eq 'contain' ) {
82 @terms = split / /, $searchmember;
84 @terms = ($searchmember);
87 foreach my $term (@terms) {
90 $term .= '%' # end with anything
92 $term = "%$term" # begin with anythin unless start_with
93 if $searchtype eq 'contain' && $term !~ /^%/;
96 for my $searchfield ( split /,/, $searchfields->{$searchfieldstype} ) {
97 push @where_strs_or, "borrowers." . $dbh->quote_identifier($searchfield) . " LIKE ?";
98 push @where_args, $term;
101 if ( C4::Context->preference('ExtendedPatronAttributes') and $searchmember ) {
102 my $matching_borrowernumbers = C4::Members::Attributes::SearchIdMatchingAttribute($searchmember);
104 for my $borrowernumber ( @$matching_borrowernumbers ) {
105 push @where_strs_or, "borrowers.borrowernumber = ?";
106 push @where_args, $borrowernumber;
110 push @where_strs, '('. join (' OR ', @where_strs_or) . ')'
115 $where = " WHERE " . join (" AND ", @where_strs) if @where_strs;
116 my $orderby = dt_build_orderby($dt_params);
119 # If iDisplayLength == -1, we want to display all patrons
120 if ( !$dt_params->{iDisplayLength} || $dt_params->{iDisplayLength} > -1 ) {
121 # In order to avoid sql injection
122 $dt_params->{iDisplayStart} =~ s/\D//g if defined($dt_params->{iDisplayStart});
123 $dt_params->{iDisplayLength} =~ s/\D//g if defined($dt_params->{iDisplayLength});
124 $dt_params->{iDisplayStart} //= 0;
125 $dt_params->{iDisplayLength} //= 20;
126 $limit = "LIMIT $dt_params->{iDisplayStart},$dt_params->{iDisplayLength}";
131 ($select ? $select : ""),
132 ($from ? $from : ""),
133 ($where ? $where : ""),
134 ($orderby ? $orderby : ""),
135 ($limit ? $limit : "")
137 my $sth = $dbh->prepare($query);
138 $sth->execute(@where_args);
139 my $patrons = $sth->fetchall_arrayref({});
141 # Get the iTotalDisplayRecords DataTable variable
142 $query = "SELECT COUNT(borrowers.borrowernumber) " . $from . ($where ? $where : "");
143 $sth = $dbh->prepare($query);
144 $sth->execute(@where_args);
145 ($iTotalDisplayRecords) = $sth->fetchrow_array;
147 # Get the iTotalRecords DataTable variable
148 $query = "SELECT COUNT(borrowers.borrowernumber) FROM borrowers";
149 $sth = $dbh->prepare($query);
151 ($iTotalRecords) = $sth->fetchrow_array;
153 # Get some information on patrons
154 foreach my $patron (@$patrons) {
155 ($patron->{overdues}, $patron->{issues}, $patron->{fines}) =
156 GetMemberIssuesAndFines($patron->{borrowernumber});
157 if($patron->{dateexpiry} and $patron->{dateexpiry} ne '0000-00-00') {
158 $patron->{dateexpiry} = output_pref( { dt => dt_from_string( $patron->{dateexpiry}, 'iso'), dateonly => 1} );
160 $patron->{dateexpiry} = '';
162 $patron->{fines} = sprintf("%.2f", $patron->{fines} || 0);
166 iTotalRecords => $iTotalRecords,
167 iTotalDisplayRecords => $iTotalDisplayRecords,
177 C4::Utils::DataTables::Members - module for using DataTables with patrons
181 This module provides (one for the moment) routines used by the patrons search
187 my $dt_infos = C4::Utils::DataTables::Members->search($params);
189 $params is a hashref with some keys:
195 String to search in the borrowers sql table
199 Introduced to contain 1 letter but can contain more.
200 The search will done on the borrowers.surname field
204 Search patrons with this categorycode
208 Search patrons with this branchcode
212 Can be 'contain' or 'start_with' (default value). Used for the searchmember parameter.
214 =item searchfieldstype
216 Can be 'standard' (default value), 'email', 'borrowernumber', 'phone', 'address' or 'dateofbirth', 'sort1', 'sort2'
220 Is the reference of C4::Utils::DataTables::dt_get_params($input);
228 This file is part of Koha.
230 Copyright 2013 BibLibre
232 Koha is free software; you can redistribute it and/or modify it
233 under the terms of the GNU General Public License as published by
234 the Free Software Foundation; either version 3 of the License, or
235 (at your option) any later version.
237 Koha is distributed in the hope that it will be useful, but
238 WITHOUT ANY WARRANTY; without even the implied warranty of
239 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
240 GNU General Public License for more details.
242 You should have received a copy of the GNU General Public License
243 along with Koha; if not, see <http://www.gnu.org/licenses>.