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 This file is part of Koha.
200 Copyright 2013 BibLibre
202 Koha is free software; you can redistribute it and/or modify it
203 under the terms of the GNU General Public License as published by
204 the Free Software Foundation; either version 3 of the License, or
205 (at your option) any later version.
207 Koha is distributed in the hope that it will be useful, but
208 WITHOUT ANY WARRANTY; without even the implied warranty of
209 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
210 GNU General Public License for more details.
212 You should have received a copy of the GNU General Public License
213 along with Koha; if not, see <http://www.gnu.org/licenses>.