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};
17 my $dt_params = $params->{dt_params};
19 my ($iTotalRecords, $iTotalDisplayRecords);
21 # If branches are independant and user is not superlibrarian
22 # The search has to be only on the user branch
23 if ( C4::Branch::onlymine ) {
24 my $userenv = C4::Context->userenv;
25 $branchcode = $userenv->{'branch'};
29 my $dbh = C4::Context->dbh;
31 borrowers.borrowernumber, borrowers.surname, borrowers.firstname, borrowers.address,
32 borrowers.address2, borrowers.city, borrowers.zipcode, borrowers.country,
33 CAST(borrowers.cardnumber AS UNSIGNED) AS cardnumber, borrowers.dateexpiry,
34 borrowers.borrowernotes, borrowers.branchcode,
35 categories.description AS category_description, categories.category_type,
37 my $from = "FROM borrowers
38 LEFT JOIN branches ON borrowers.branchcode = branches.branchcode
39 LEFT JOIN categories ON borrowers.categorycode = categories.categorycode";
42 if(defined $firstletter and $firstletter ne '') {
43 push @where_strs, "borrowers.surname LIKE ?";
44 push @where_args, "$firstletter%";
46 if(defined $categorycode and $categorycode ne '') {
47 push @where_strs, "borrowers.categorycode = ?";
48 push @where_args, $categorycode;
50 if(defined $branchcode and $branchcode ne '') {
51 push @where_strs, "borrowers.branchcode = ?";
52 push @where_args, $branchcode;
56 $searchmember =~ s/,/ /g if $searchmember;
59 standard => 'surname,firstname,othernames,cardnumber',
60 email => 'email,emailpro,B_email',
61 borrowernumber => 'borrowernumber',
62 phone => 'phone,phonepro,B_phone,altcontactphone,mobile',
63 address => 'streettype,address,address2,city,state,zipcode,country',
64 dateofbirth => 'dateofbirth',
68 for my $searchfield ( split /,/, $searchfields->{$searchfieldstype} ) {
69 foreach my $term ( split / /, $searchmember) {
71 $searchmember =~ s/\*/%/g; # * is replaced with % for sql
72 $term .= '%' # end with anything
74 $term = "%$term" # begin with anythin unless start_with
76 and $searchtype eq "contain";
77 push @where_strs_or, "borrowers." . $dbh->quote_identifier($searchfield) . " LIKE ?";
78 push @where_args, $term;
81 push @where_strs, '('. join (' OR ', @where_strs_or) . ')'
85 $where = " WHERE " . join (" AND ", @where_strs) if @where_strs;
86 my $orderby = dt_build_orderby($dt_params);
89 # If iDisplayLength == -1, we want to display all patrons
90 if ( $dt_params->{iDisplayLength} > -1 ) {
91 # In order to avoid sql injection
92 $dt_params->{iDisplayStart} =~ s/\D//g;
93 $dt_params->{iDisplayLength} =~ s/\D//g;
94 $dt_params->{iDisplayStart} //= 0;
95 $dt_params->{iDisplayLength} //= 20;
96 $limit = "LIMIT $dt_params->{iDisplayStart},$dt_params->{iDisplayLength}";
101 ($select ? $select : ""),
102 ($from ? $from : ""),
103 ($where ? $where : ""),
104 ($orderby ? $orderby : ""),
105 ($limit ? $limit : "")
107 my $sth = $dbh->prepare($query);
108 $sth->execute(@where_args);
109 my $patrons = $sth->fetchall_arrayref({});
111 # Get the iTotalDisplayRecords DataTable variable
112 $query = "SELECT COUNT(borrowers.borrowernumber) " . $from . ($where ? $where : "");
113 $sth = $dbh->prepare($query);
114 $sth->execute(@where_args);
115 ($iTotalDisplayRecords) = $sth->fetchrow_array;
117 # Get the iTotalRecords DataTable variable
118 $query = "SELECT COUNT(borrowers.borrowernumber) FROM borrowers";
119 $sth = $dbh->prepare($query);
121 ($iTotalRecords) = $sth->fetchrow_array;
123 # Get some information on patrons
124 foreach my $patron (@$patrons) {
125 ($patron->{overdues}, $patron->{issues}, $patron->{fines}) =
126 GetMemberIssuesAndFines($patron->{borrowernumber});
127 if($patron->{dateexpiry} and $patron->{dateexpiry} ne '0000-00-00') {
128 $patron->{dateexpiry} = C4::Dates->new($patron->{dateexpiry}, "iso")->output();
130 $patron->{dateexpiry} = '';
132 $patron->{fines} = sprintf("%.2f", $patron->{fines} || 0);
136 iTotalRecords => $iTotalRecords,
137 iTotalDisplayRecords => $iTotalDisplayRecords,
147 C4::Utils::DataTables::Members - module for using DataTables with patrons
151 This module provides (one for the moment) routines used by the patrons search
157 my $dt_infos = C4::Utils::DataTables::Members->search($params);
159 $params is a hashref with some keys:
165 String to search in the borrowers sql table
169 Introduced to contain 1 letter but can contain more.
170 The search will done on the borrowers.surname field
174 Search patrons with this categorycode
178 Search patrons with this branchcode
182 Can be 'contain' or 'start_with'. Used for the searchmember parameter.
184 =item searchfieldstype
186 Can be 'standard', 'email', 'borrowernumber', 'phone', 'address' or 'dateofbirth', 'sort1', 'sort2'
190 Is the reference of C4::Utils::DataTables::dt_get_params($input);
198 Copyright 2013 BibLibre
200 This file is part of Koha.
202 Koha is free software; you can redistribute it and/or modify it under the
203 terms of the GNU General Public License as published by the Free Software
204 Foundation; either version 2 of the License, or (at your option) any later
207 Koha is distributed in the hope that it will be useful, but WITHOUT ANY
208 WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
209 A PARTICULAR PURPOSE. See the GNU General Public License for more details.
211 You should have received a copy of the GNU General Public License along
212 with Koha; if not, write to the Free Software Foundation, Inc.,
213 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.