@@ -, +, @@ --- C4/Utils/DataTables/Members.pm | 138 ++++++++++++++++------------------------- 1 file changed, 55 insertions(+), 83 deletions(-) --- a/C4/Utils/DataTables/Members.pm +++ a/C4/Utils/DataTables/Members.pm @@ -1,10 +1,12 @@ package C4::Utils::DataTables::Members; use Modern::Perl; +use Catmandu::Store::ElasticSearch; use C4::Context; use C4::Members qw/GetMemberIssuesAndFines/; use C4::Utils::DataTables; use Koha::DateUtils; +use Koha::SearchEngine::Elasticsearch::Search; sub search { my ( $params ) = @_; @@ -30,34 +32,6 @@ sub search { } - 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"; - my $from = "FROM borrowers - LEFT JOIN branches ON borrowers.branchcode = branches.branchcode - LEFT JOIN categories ON borrowers.categorycode = categories.categorycode"; - my @where_args; - my @where_strs; - if(defined $firstletter and $firstletter ne '') { - push @where_strs, "borrowers.surname LIKE ?"; - push @where_args, "$firstletter%"; - } - if(defined $categorycode and $categorycode ne '') { - push @where_strs, "borrowers.categorycode = ?"; - push @where_args, $categorycode; - } - if(defined $branchcode and $branchcode ne '') { - push @where_strs, "borrowers.branchcode = ?"; - push @where_args, $branchcode; - } - my $searchfields = { standard => 'surname,firstname,othernames,cardnumber,userid', surname => 'surname', @@ -71,8 +45,24 @@ sub search { sort2 => 'sort2', }; - # * is replaced with % for sql - $searchmember =~ s/\*/%/g; + my $searcher = Koha::SearchEngine::Elasticsearch::Search->new({ index => 'patrons' }); + my $es_params = $searcher->get_elasticsearch_params(); + $searcher->store( + Catmandu::Store::ElasticSearch->new( + %$es_params, + ) + ) unless $searcher->store; + + my @conditions; + if ( defined $firstletter and $firstletter ne '' ) { + push @conditions, "surname:$firstletter*"; + } + if ( defined $categorycode and $categorycode ne '' ) { + push @conditions, "categorycode:$categorycode"; + } + if ( defined $branchcode and $branchcode ne '' ) { + push @conditions, "branchcode:$branchcode"; + } # split into search terms my @terms; @@ -87,68 +77,50 @@ sub search { foreach my $term (@terms) { next unless $term; - $term .= '%' # end with anything - if $term !~ /%$/; - $term = "%$term" # begin with anythin unless start_with - if $searchtype eq 'contain' && $term !~ /^%/; + $term .= '*' # end with anything + if $term !~ /\*$/; + $term = "\*$term" # begin with anythin unless start_with + if $searchtype eq 'contain' && $term !~ /^\*/; - my @where_strs_or; + my @conditions_or; for my $searchfield ( split /,/, $searchfields->{$searchfieldstype} ) { - push @where_strs_or, "borrowers." . $dbh->quote_identifier($searchfield) . " LIKE ?"; - push @where_args, $term; + push @conditions_or, "$searchfield:$term"; } - if ( C4::Context->preference('ExtendedPatronAttributes') and $searchmember ) { - my $matching_borrowernumbers = C4::Members::Attributes::SearchIdMatchingAttribute($searchmember); + # TODO + #if ( C4::Context->preference('ExtendedPatronAttributes') and $searchmember ) { + # my $matching_borrowernumbers = C4::Members::Attributes::SearchIdMatchingAttribute($searchmember); - for my $borrowernumber ( @$matching_borrowernumbers ) { - push @where_strs_or, "borrowers.borrowernumber = ?"; - push @where_args, $borrowernumber; - } - } + # for my $borrowernumber ( @$matching_borrowernumbers ) { + # push @where_strs_or, "borrowers.borrowernumber = ?"; + # push @where_args, $borrowernumber; + # } + #} - push @where_strs, '('. join (' OR ', @where_strs_or) . ')' - if @where_strs_or; + push @conditions, '('. join (' OR ', @conditions_or) . ')' + if @conditions_or; } - my $where; - $where = " WHERE " . join (" AND ", @where_strs) if @where_strs; - my $orderby = dt_build_orderby($dt_params); - - my $limit; - # If iDisplayLength == -1, we want to display all patrons - if ( !$dt_params->{iDisplayLength} || $dt_params->{iDisplayLength} > -1 ) { - # In order to avoid sql injection - $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}"; - } + @conditions = ('*') unless @conditions; + my $query = { + query => { + query_string => { + query => join ' AND ', @conditions, + } + } + }; + + # Do not display more than 1000 patrons + $dt_params->{iDisplayLength} = 1000 if $dt_params->{iDisplayLength} == -1; + my $paging = { + limit => $dt_params->{iDisplayLength} // 20, + start => $dt_params->{iDisplayStart} // 0, + }; - my $query = join( - " ", - ($select ? $select : ""), - ($from ? $from : ""), - ($where ? $where : ""), - ($orderby ? $orderby : ""), - ($limit ? $limit : "") - ); - my $sth = $dbh->prepare($query); - $sth->execute(@where_args); - my $patrons = $sth->fetchall_arrayref({}); - - # Get the iTotalDisplayRecords DataTable variable - $query = "SELECT COUNT(borrowers.borrowernumber) " . $from . ($where ? $where : ""); - $sth = $dbh->prepare($query); - $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; + my $results = $searcher->store->bag->search( %$query, %$paging ); + my $patrons = $results->hits; + $iTotalRecords = $results->total; + $iTotalDisplayRecords = $iTotalRecords; # FIXME this is wrong # Get some information on patrons foreach my $patron (@$patrons) { --