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 independant 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,
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',
65 email => 'email,emailpro,B_email',
66 borrowernumber => 'borrowernumber',
67 phone => 'phone,phonepro,B_phone,altcontactphone,mobile',
68 address => 'streettype,address,address2,city,state,zipcode,country',
69 dateofbirth => 'dateofbirth',
73 foreach my $term ( split / /, $searchmember) {
75 $searchmember =~ s/\*/%/g; # * is replaced with % for sql
76 $term .= '%' # end with anything
78 $term = "%$term" # begin with anythin unless start_with
80 and $searchtype eq "contain";
82 for my $searchfield ( split /,/, $searchfields->{$searchfieldstype} ) {
83 push @where_strs_or, "borrowers." . $dbh->quote_identifier($searchfield) . " LIKE ?";
84 push @where_args, $term;
87 if ( C4::Context->preference('ExtendedPatronAttributes') and $searchmember ) {
88 my $matching_borrowernumbers = C4::Members::Attributes::SearchIdMatchingAttribute($searchmember);
90 for my $borrowernumber ( @$matching_borrowernumbers ) {
91 push @where_strs_or, "borrowers.borrowernumber = ?";
92 push @where_args, $borrowernumber;
96 push @where_strs, '('. join (' OR ', @where_strs_or) . ')'
101 $where = " WHERE " . join (" AND ", @where_strs) if @where_strs;
102 my $orderby = dt_build_orderby($dt_params);
105 # If iDisplayLength == -1, we want to display all patrons
106 if ( $dt_params->{iDisplayLength} > -1 ) {
107 # In order to avoid sql injection
108 $dt_params->{iDisplayStart} =~ s/\D//g;
109 $dt_params->{iDisplayLength} =~ s/\D//g;
110 $dt_params->{iDisplayStart} //= 0;
111 $dt_params->{iDisplayLength} //= 20;
112 $limit = "LIMIT $dt_params->{iDisplayStart},$dt_params->{iDisplayLength}";
117 ($select ? $select : ""),
118 ($from ? $from : ""),
119 ($where ? $where : ""),
120 ($orderby ? $orderby : ""),
121 ($limit ? $limit : "")
123 my $sth = $dbh->prepare($query);
124 $sth->execute(@where_args);
125 my $patrons = $sth->fetchall_arrayref({});
127 # Get the iTotalDisplayRecords DataTable variable
128 $query = "SELECT COUNT(borrowers.borrowernumber) " . $from . ($where ? $where : "");
129 $sth = $dbh->prepare($query);
130 $sth->execute(@where_args);
131 ($iTotalDisplayRecords) = $sth->fetchrow_array;
133 # Get the iTotalRecords DataTable variable
134 $query = "SELECT COUNT(borrowers.borrowernumber) FROM borrowers";
135 $sth = $dbh->prepare($query);
137 ($iTotalRecords) = $sth->fetchrow_array;
139 # Get some information on patrons
140 foreach my $patron (@$patrons) {
141 ($patron->{overdues}, $patron->{issues}, $patron->{fines}) =
142 GetMemberIssuesAndFines($patron->{borrowernumber});
143 if($patron->{dateexpiry} and $patron->{dateexpiry} ne '0000-00-00') {
144 $patron->{dateexpiry} = C4::Dates->new($patron->{dateexpiry}, "iso")->output();
146 $patron->{dateexpiry} = '';
148 $patron->{fines} = sprintf("%.2f", $patron->{fines} || 0);
152 iTotalRecords => $iTotalRecords,
153 iTotalDisplayRecords => $iTotalDisplayRecords,
163 C4::Utils::DataTables::Members - module for using DataTables with patrons
167 This module provides (one for the moment) routines used by the patrons search
173 my $dt_infos = C4::Utils::DataTables::Members->search($params);
175 $params is a hashref with some keys:
181 String to search in the borrowers sql table
185 Introduced to contain 1 letter but can contain more.
186 The search will done on the borrowers.surname field
190 Search patrons with this categorycode
194 Search patrons with this branchcode
198 Can be 'contain' or 'start_with'. Used for the searchmember parameter.
200 =item searchfieldstype
202 Can be 'standard', 'email', 'borrowernumber', 'phone', 'address' or 'dateofbirth', 'sort1', 'sort2'
206 Is the reference of C4::Utils::DataTables::dt_get_params($input);
214 This file is part of Koha.
216 Copyright 2013 BibLibre
218 Koha is free software; you can redistribute it and/or modify it
219 under the terms of the GNU General Public License as published by
220 the Free Software Foundation; either version 3 of the License, or
221 (at your option) any later version.
223 Koha is distributed in the hope that it will be useful, but
224 WITHOUT ANY WARRANTY; without even the implied warranty of
225 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
226 GNU General Public License for more details.
228 You should have received a copy of the GNU General Public License
229 along with Koha; if not, see <http://www.gnu.org/licenses>.