1 package C4::Utils::DataTables::Members;
3 use C4::Branch qw/onlymine/;
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};
16 my $searchfieldstype = $params->{searchfieldstype} || 'standard';
17 my $dt_params = $params->{dt_params};
19 unless ( $searchmember ) {
20 $searchmember = $dt_params->{sSearch} // '';
23 my ($iTotalRecords, $iTotalDisplayRecords);
25 # If branches are independent and user is not superlibrarian
26 # The search has to be only on the user branch
27 if ( C4::Branch::onlymine ) {
28 my $userenv = C4::Context->userenv;
29 $branchcode = $userenv->{'branch'};
33 my $dbh = C4::Context->dbh;
35 borrowers.borrowernumber, borrowers.surname, borrowers.firstname,
36 borrowers.streetnumber, borrowers.streettype, borrowers.address,
37 borrowers.address2, borrowers.city, borrowers.state, borrowers.zipcode,
38 borrowers.country, cardnumber, borrowers.dateexpiry,
39 borrowers.borrowernotes, borrowers.branchcode, borrowers.email,
40 borrowers.userid, borrowers.dateofbirth, borrowers.categorycode,
41 categories.description AS category_description, categories.category_type,
43 my $from = "FROM borrowers
44 LEFT JOIN branches ON borrowers.branchcode = branches.branchcode
45 LEFT JOIN categories ON borrowers.categorycode = categories.categorycode";
48 if(defined $firstletter and $firstletter ne '') {
49 push @where_strs, "borrowers.surname LIKE ?";
50 push @where_args, "$firstletter%";
52 if(defined $categorycode and $categorycode ne '') {
53 push @where_strs, "borrowers.categorycode = ?";
54 push @where_args, $categorycode;
56 if(defined $branchcode and $branchcode ne '') {
57 push @where_strs, "borrowers.branchcode = ?";
58 push @where_args, $branchcode;
62 $searchmember =~ s/,/ /g if $searchmember;
64 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',
74 foreach my $term ( split / /, $searchmember) {
76 $searchmember =~ s/\*/%/g; # * is replaced with % for sql
77 $term .= '%' # end with anything
79 $term = "%$term" # begin with anythin unless start_with
80 if (defined $searchtype) && $searchtype eq "contain"
83 for my $searchfield ( split /,/, $searchfields->{$searchfieldstype} ) {
84 push @where_strs_or, "borrowers." . $dbh->quote_identifier($searchfield) . " LIKE ?";
85 push @where_args, $term;
88 if ( C4::Context->preference('ExtendedPatronAttributes') and $searchmember ) {
89 my $matching_borrowernumbers = C4::Members::Attributes::SearchIdMatchingAttribute($searchmember);
91 for my $borrowernumber ( @$matching_borrowernumbers ) {
92 push @where_strs_or, "borrowers.borrowernumber = ?";
93 push @where_args, $borrowernumber;
97 push @where_strs, '('. join (' OR ', @where_strs_or) . ')'
102 $where = " WHERE " . join (" AND ", @where_strs) if @where_strs;
103 my $orderby = dt_build_orderby($dt_params);
106 # If iDisplayLength == -1, we want to display all patrons
107 if ( !$dt_params->{iDisplayLength} || $dt_params->{iDisplayLength} > -1 ) {
108 # In order to avoid sql injection
109 $dt_params->{iDisplayStart} =~ s/\D//g if defined($dt_params->{iDisplayStart});
110 $dt_params->{iDisplayLength} =~ s/\D//g if defined($dt_params->{iDisplayLength});
111 $dt_params->{iDisplayStart} //= 0;
112 $dt_params->{iDisplayLength} //= 20;
113 $limit = "LIMIT $dt_params->{iDisplayStart},$dt_params->{iDisplayLength}";
118 ($select ? $select : ""),
119 ($from ? $from : ""),
120 ($where ? $where : ""),
121 ($orderby ? $orderby : ""),
122 ($limit ? $limit : "")
124 my $sth = $dbh->prepare($query);
125 $sth->execute(@where_args);
126 my $patrons = $sth->fetchall_arrayref({});
128 # Get the iTotalDisplayRecords DataTable variable
129 $query = "SELECT COUNT(borrowers.borrowernumber) " . $from . ($where ? $where : "");
130 $sth = $dbh->prepare($query);
131 $sth->execute(@where_args);
132 ($iTotalDisplayRecords) = $sth->fetchrow_array;
134 # Get the iTotalRecords DataTable variable
135 $query = "SELECT COUNT(borrowers.borrowernumber) FROM borrowers";
136 $sth = $dbh->prepare($query);
138 ($iTotalRecords) = $sth->fetchrow_array;
140 # Get some information on patrons
141 foreach my $patron (@$patrons) {
142 ($patron->{overdues}, $patron->{issues}, $patron->{fines}) =
143 GetMemberIssuesAndFines($patron->{borrowernumber});
144 if($patron->{dateexpiry} and $patron->{dateexpiry} ne '0000-00-00') {
145 $patron->{dateexpiry} = C4::Dates->new($patron->{dateexpiry}, "iso")->output();
147 $patron->{dateexpiry} = '';
149 $patron->{fines} = sprintf("%.2f", $patron->{fines} || 0);
153 iTotalRecords => $iTotalRecords,
154 iTotalDisplayRecords => $iTotalDisplayRecords,
164 C4::Utils::DataTables::Members - module for using DataTables with patrons
168 This module provides (one for the moment) routines used by the patrons search
174 my $dt_infos = C4::Utils::DataTables::Members->search($params);
176 $params is a hashref with some keys:
182 String to search in the borrowers sql table
186 Introduced to contain 1 letter but can contain more.
187 The search will done on the borrowers.surname field
191 Search patrons with this categorycode
195 Search patrons with this branchcode
199 Can be 'contain' or 'start_with'. Used for the searchmember parameter.
201 =item searchfieldstype
203 Can be 'standard', 'email', 'borrowernumber', 'phone', 'address' or 'dateofbirth', 'sort1', 'sort2'
207 Is the reference of C4::Utils::DataTables::dt_get_params($input);
215 This file is part of Koha.
217 Copyright 2013 BibLibre
219 Koha is free software; you can redistribute it and/or modify it
220 under the terms of the GNU General Public License as published by
221 the Free Software Foundation; either version 3 of the License, or
222 (at your option) any later version.
224 Koha is distributed in the hope that it will be useful, but
225 WITHOUT ANY WARRANTY; without even the implied warranty of
226 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
227 GNU General Public License for more details.
229 You should have received a copy of the GNU General Public License
230 along with Koha; if not, see <http://www.gnu.org/licenses>.