package C4::Utils::DataTables::Members;
-use C4::Branch qw/onlymine/;
+use Modern::Perl;
use C4::Context;
-use C4::Members qw/GetMemberIssuesAndFines/;
use C4::Utils::DataTables;
-use Modern::Perl;
+use Koha::DateUtils;
sub search {
my ( $params ) = @_;
my $firstletter = $params->{firstletter};
my $categorycode = $params->{categorycode};
my $branchcode = $params->{branchcode};
- my $searchtype = $params->{searchtype};
- my $searchfieldstype = $params->{searchfieldstype};
+ my $searchtype = $params->{searchtype} || 'contain';
+ my $searchfieldstype = $params->{searchfieldstype} || 'standard';
my $dt_params = $params->{dt_params};
- my ($iTotalRecords, $iTotalDisplayRecords);
+ unless ( $searchmember ) {
+ $searchmember = $dt_params->{sSearch} // '';
+ }
- # If branches are independant and user is not superlibrarian
+ # If branches are independent and user is not superlibrarian
# The search has to be only on the user branch
- if ( C4::Branch::onlymine ) {
- my $userenv = C4::Context->userenv;
- $branchcode = $userenv->{'branch'};
+ my $userenv = C4::Context->userenv;
+ my $logged_in_user = Koha::Patrons->find( $userenv->{number} );
+ my @restricted_branchcodes = $logged_in_user->libraries_where_can_see_patrons;
+ my ($sth, $query, $iTotalQuery, $iTotalRecords, $iTotalDisplayRecords);
+ my $dbh = C4::Context->dbh;
+ # Get the iTotalRecords DataTable variable
+ $query = $iTotalQuery = "SELECT COUNT(borrowers.borrowernumber) FROM borrowers";
+ if ( @restricted_branchcodes ) {
+ $iTotalQuery .= " WHERE borrowers.branchcode IN (" . join( ',', ('?') x @restricted_branchcodes ) . ")";
+ }
+ ($iTotalRecords) = $dbh->selectrow_array( $iTotalQuery, undef, @restricted_branchcodes );
+
+ # Do that after iTotalQuery!
+ if ( defined $branchcode and $branchcode ) {
+ @restricted_branchcodes = @restricted_branchcodes
+ ? grep { $_ eq $branchcode } @restricted_branchcodes
+ ? ($branchcode)
+ : (undef) # Do not return any results
+ : ($branchcode);
+ }
+
+ if ( $searchfieldstype eq 'dateofbirth' ) {
+ # Return an empty list if the date of birth is not correctly formatted
+ $searchmember = eval { output_pref( { str => $searchmember, dateformat => 'iso', dateonly => 1 } ); };
+ if ( $@ or not $searchmember ) {
+ return {
+ iTotalRecords => $iTotalRecords,
+ iTotalDisplayRecords => 0,
+ patrons => [],
+ };
+ }
}
- my $dbh = C4::Context->dbh;
my $select = "SELECT
borrowers.borrowernumber, borrowers.surname, borrowers.firstname,
borrowers.streetnumber, borrowers.streettype, borrowers.address,
borrowers.address2, borrowers.city, borrowers.state, borrowers.zipcode,
borrowers.country, cardnumber, borrowers.dateexpiry,
borrowers.borrowernotes, borrowers.branchcode, borrowers.email,
+ borrowers.userid, borrowers.dateofbirth, borrowers.categorycode,
categories.description AS category_description, categories.category_type,
- branches.branchname";
+ branches.branchname, borrowers.phone";
my $from = "FROM borrowers
LEFT JOIN branches ON borrowers.branchcode = branches.branchcode
LEFT JOIN categories ON borrowers.categorycode = categories.categorycode";
push @where_strs, "borrowers.categorycode = ?";
push @where_args, $categorycode;
}
- if(defined $branchcode and $branchcode ne '') {
- push @where_strs, "borrowers.branchcode = ?";
- push @where_args, $branchcode;
+ if(@restricted_branchcodes ) {
+ push @where_strs, "borrowers.branchcode IN (" . join( ',', ('?') x @restricted_branchcodes ) . ")";
+ push @where_args, @restricted_branchcodes;
}
- # split on coma
- $searchmember =~ s/,/ /g if $searchmember;
my $searchfields = {
- standard => 'surname,firstname,othernames,cardnumber',
+ standard => C4::Context->preference('DefaultPatronSearchFields') || 'surname,firstname,othernames,cardnumber,userid',
email => 'email,emailpro,B_email',
borrowernumber => 'borrowernumber',
phone => 'phone,phonepro,B_phone,altcontactphone,mobile',
- address => 'streettype,address,address2,city,state,zipcode,country',
- dateofbirth => 'dateofbirth',
- sort1 => 'sort1',
- sort2 => 'sort2',
+ address => 'streetnumber,streettype,address,address2,city,state,zipcode,country',
};
- foreach my $term ( split / /, $searchmember) {
+
+ # * is replaced with % for sql
+ $searchmember =~ s/\*/%/g;
+
+ # split into search terms
+ my @terms;
+ # consider coma as space
+ $searchmember =~ s/,/ /g;
+ if ( $searchtype eq 'contain' ) {
+ @terms = split / /, $searchmember;
+ } else {
+ @terms = ($searchmember);
+ }
+
+ foreach my $term (@terms) {
next unless $term;
- $searchmember =~ s/\*/%/g; # * is replaced with % for sql
- $term .= '%' # end with anything
- if $term !~ /%$/;
- $term = "%$term" # begin with anythin unless start_with
- if $term !~ /^%/
- and $searchtype eq "contain";
+
+ my $term_dt = eval { local $SIG{__WARN__} = {}; output_pref( { str => $term, dateonly => 1, dateformat => 'sql' } ); };
+
+ if ($term_dt) {
+ $term = $term_dt;
+ } else {
+ $term .= '%' # end with anything
+ if $term !~ /%$/;
+ $term = "%$term" # begin with anythin unless start_with
+ if $searchtype eq 'contain' && $term !~ /^%/;
+ }
+
my @where_strs_or;
- for my $searchfield ( split /,/, $searchfields->{$searchfieldstype} ) {
- push @where_strs_or, "borrowers." . $dbh->quote_identifier($searchfield) . " LIKE ?";
+ if ( defined $searchfields->{$searchfieldstype} ) {
+ for my $searchfield ( split /,/, $searchfields->{$searchfieldstype} ) {
+ push @where_strs_or, "borrowers." . $dbh->quote_identifier($searchfield) . " LIKE ?";
+ push @where_args, $term;
+ }
+ } else {
+ push @where_strs_or, "borrowers." . $dbh->quote_identifier($searchfieldstype) . " LIKE ?";
push @where_args, $term;
}
- if ( C4::Context->preference('ExtendedPatronAttributes') and $searchmember ) {
- my $matching_borrowernumbers = C4::Members::Attributes::SearchIdMatchingAttribute($searchmember);
- for my $borrowernumber ( @$matching_borrowernumbers ) {
+ if ( $searchfieldstype eq 'standard' and C4::Context->preference('ExtendedPatronAttributes') and $searchmember ) {
+ my @matching_borrowernumbers = Koha::Patrons->filter_by_attribute_value($searchmember)->get_column('borrowernumber');
+
+ for my $borrowernumber ( @matching_borrowernumbers ) {
push @where_strs_or, "borrowers.borrowernumber = ?";
push @where_args, $borrowernumber;
}
my $limit;
# If iDisplayLength == -1, we want to display all patrons
- if ( $dt_params->{iDisplayLength} > -1 ) {
+ if ( !$dt_params->{iDisplayLength} || $dt_params->{iDisplayLength} > -1 ) {
# In order to avoid sql injection
- $dt_params->{iDisplayStart} =~ s/\D//g;
- $dt_params->{iDisplayLength} =~ s/\D//g;
+ $dt_params->{iDisplayStart} =~ s/\D//g if defined($dt_params->{iDisplayStart});
+ $dt_params->{iDisplayLength} =~ s/\D//g if defined($dt_params->{iDisplayLength});
$dt_params->{iDisplayStart} //= 0;
$dt_params->{iDisplayLength} //= 20;
$limit = "LIMIT $dt_params->{iDisplayStart},$dt_params->{iDisplayLength}";
}
- my $query = join(
+ $query = join(
" ",
($select ? $select : ""),
($from ? $from : ""),
($orderby ? $orderby : ""),
($limit ? $limit : "")
);
- my $sth = $dbh->prepare($query);
+ $sth = $dbh->prepare($query);
$sth->execute(@where_args);
my $patrons = $sth->fetchall_arrayref({});
$sth->execute(@where_args);
($iTotalDisplayRecords) = $sth->fetchrow_array;
- # Get the iTotalRecords DataTable variable
- $query = "SELECT COUNT(borrowers.borrowernumber) FROM borrowers";
- $sth = $dbh->prepare($query);
- $sth->execute;
- ($iTotalRecords) = $sth->fetchrow_array;
-
# Get some information on patrons
foreach my $patron (@$patrons) {
- ($patron->{overdues}, $patron->{issues}, $patron->{fines}) =
- GetMemberIssuesAndFines($patron->{borrowernumber});
- if($patron->{dateexpiry} and $patron->{dateexpiry} ne '0000-00-00') {
- $patron->{dateexpiry} = C4::Dates->new($patron->{dateexpiry}, "iso")->output();
+ my $patron_object = Koha::Patrons->find( $patron->{borrowernumber} );
+ $patron->{overdues} = $patron_object->get_overdues->count;
+ $patron->{issues} = $patron_object->checkouts->count;
+ my $balance = $patron_object->account->balance;
+ # FIXME Should be formatted from the template
+ $patron->{fines} = sprintf("%.2f", $balance);
+
+ if( $patron->{dateexpiry} ) {
+ # FIXME We should not format the date here, do it in template-side instead
+ $patron->{dateexpiry} = output_pref( { dt => scalar dt_from_string( $patron->{dateexpiry}, 'iso'), dateonly => 1} );
} else {
$patron->{dateexpiry} = '';
}
- $patron->{fines} = sprintf("%.2f", $patron->{fines} || 0);
}
return {
=item searchtype
- Can be 'contain' or 'start_with'. Used for the searchmember parameter.
+ Can be 'start_with' or 'contain' (default value). Used for the searchmember parameter.
=item searchfieldstype
- Can be 'standard', 'email', 'borrowernumber', 'phone', 'address' or 'dateofbirth', 'sort1', 'sort2'
+ Can be 'standard' (default value), 'email', 'borrowernumber', 'phone', 'address' or 'dateofbirth', 'sort1', 'sort2'
=item dt_params