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,
40 categories.description AS category_description, categories.category_type,
42 my $from = "FROM borrowers
43 LEFT JOIN branches ON borrowers.branchcode = branches.branchcode
44 LEFT JOIN categories ON borrowers.categorycode = categories.categorycode";
47 if(defined $firstletter and $firstletter ne '') {
48 push @where_strs, "borrowers.surname LIKE ?";
49 push @where_args, "$firstletter%";
51 if(defined $categorycode and $categorycode ne '') {
52 push @where_strs, "borrowers.categorycode = ?";
53 push @where_args, $categorycode;
55 if(defined $branchcode and $branchcode ne '') {
56 push @where_strs, "borrowers.branchcode = ?";
57 push @where_args, $branchcode;
61 $searchmember =~ s/,/ /g if $searchmember;
63 standard => 'surname,firstname,othernames,cardnumber',
64 email => 'email,emailpro,B_email',
65 borrowernumber => 'borrowernumber',
66 phone => 'phone,phonepro,B_phone,altcontactphone,mobile',
67 address => 'streettype,address,address2,city,state,zipcode,country',
68 dateofbirth => 'dateofbirth',
72 foreach my $term ( split / /, $searchmember) {
74 $searchmember =~ s/\*/%/g; # * is replaced with % for sql
75 $term .= '%' # end with anything
77 $term = "%$term" # begin with anythin unless start_with
79 and $searchtype eq "contain";
81 for my $searchfield ( split /,/, $searchfields->{$searchfieldstype} ) {
82 push @where_strs_or, "borrowers." . $dbh->quote_identifier($searchfield) . " LIKE ?";
83 push @where_args, $term;
86 if ( C4::Context->preference('ExtendedPatronAttributes') and $searchmember ) {
87 my $matching_borrowernumbers = C4::Members::Attributes::SearchIdMatchingAttribute($searchmember);
89 for my $borrowernumber ( @$matching_borrowernumbers ) {
90 push @where_strs_or, "borrowers.borrowernumber = ?";
91 push @where_args, $borrowernumber;
95 push @where_strs, '('. join (' OR ', @where_strs_or) . ')'
100 $where = " WHERE " . join (" AND ", @where_strs) if @where_strs;
101 my $orderby = dt_build_orderby($dt_params);
104 # If iDisplayLength == -1, we want to display all patrons
105 if ( $dt_params->{iDisplayLength} > -1 ) {
106 # In order to avoid sql injection
107 $dt_params->{iDisplayStart} =~ s/\D//g;
108 $dt_params->{iDisplayLength} =~ s/\D//g;
109 $dt_params->{iDisplayStart} //= 0;
110 $dt_params->{iDisplayLength} //= 20;
111 $limit = "LIMIT $dt_params->{iDisplayStart},$dt_params->{iDisplayLength}";
116 ($select ? $select : ""),
117 ($from ? $from : ""),
118 ($where ? $where : ""),
119 ($orderby ? $orderby : ""),
120 ($limit ? $limit : "")
122 my $sth = $dbh->prepare($query);
123 $sth->execute(@where_args);
124 my $patrons = $sth->fetchall_arrayref({});
126 # Get the iTotalDisplayRecords DataTable variable
127 $query = "SELECT COUNT(borrowers.borrowernumber) " . $from . ($where ? $where : "");
128 $sth = $dbh->prepare($query);
129 $sth->execute(@where_args);
130 ($iTotalDisplayRecords) = $sth->fetchrow_array;
132 # Get the iTotalRecords DataTable variable
133 $query = "SELECT COUNT(borrowers.borrowernumber) FROM borrowers";
134 $sth = $dbh->prepare($query);
136 ($iTotalRecords) = $sth->fetchrow_array;
138 # Get some information on patrons
139 foreach my $patron (@$patrons) {
140 ($patron->{overdues}, $patron->{issues}, $patron->{fines}) =
141 GetMemberIssuesAndFines($patron->{borrowernumber});
142 if($patron->{dateexpiry} and $patron->{dateexpiry} ne '0000-00-00') {
143 $patron->{dateexpiry} = C4::Dates->new($patron->{dateexpiry}, "iso")->output();
145 $patron->{dateexpiry} = '';
147 $patron->{fines} = sprintf("%.2f", $patron->{fines} || 0);
151 iTotalRecords => $iTotalRecords,
152 iTotalDisplayRecords => $iTotalDisplayRecords,
162 C4::Utils::DataTables::Members - module for using DataTables with patrons
166 This module provides (one for the moment) routines used by the patrons search
172 my $dt_infos = C4::Utils::DataTables::Members->search($params);
174 $params is a hashref with some keys:
180 String to search in the borrowers sql table
184 Introduced to contain 1 letter but can contain more.
185 The search will done on the borrowers.surname field
189 Search patrons with this categorycode
193 Search patrons with this branchcode
197 Can be 'contain' or 'start_with'. Used for the searchmember parameter.
199 =item searchfieldstype
201 Can be 'standard', 'email', 'borrowernumber', 'phone', 'address' or 'dateofbirth', 'sort1', 'sort2'
205 Is the reference of C4::Utils::DataTables::dt_get_params($input);
213 This file is part of Koha.
215 Copyright 2013 BibLibre
217 Koha is free software; you can redistribute it and/or modify it
218 under the terms of the GNU General Public License as published by
219 the Free Software Foundation; either version 3 of the License, or
220 (at your option) any later version.
222 Koha is distributed in the hope that it will be useful, but
223 WITHOUT ANY WARRANTY; without even the implied warranty of
224 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
225 GNU General Public License for more details.
227 You should have received a copy of the GNU General Public License
228 along with Koha; if not, see <http://www.gnu.org/licenses>.