@@ -, +, @@ --- C4/Utils/DataTables/Members.pm | 80 +++++++------- .../prog/en/modules/members/member.tt | 81 +++++--------- svc/members/search | 117 ++++++++++++++++----- 3 files changed, 152 insertions(+), 126 deletions(-) --- a/C4/Utils/DataTables/Members.pm +++ a/C4/Utils/DataTables/Members.pm @@ -9,12 +9,14 @@ use C4::Members::Attributes qw(SearchIdMatchingAttribute ); sub search { my ( $params ) = @_; my $searchmember = $params->{searchmember}; + my $firstquery = $params->{firstquery}; my $firstletter = $params->{firstletter}; my $categorycode = $params->{categorycode}; my $branchcode = $params->{branchcode}; my $searchtype = $params->{searchtype} || 'contain'; my $searchfieldstype = $params->{searchfieldstype} || 'standard'; my $dt_params = $params->{dt_params}; + my $searchfiltering = $params->{search_filter}; unless ( $searchmember ) { $searchmember = $dt_params->{sSearch} // ''; @@ -55,46 +57,44 @@ sub search { }; } } - - 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(@restricted_branchcodes ) { - push @where_strs, "borrowers.branchcode IN (" . join( ',', ('?') x @restricted_branchcodes ) . ")"; - push @where_args, @restricted_branchcodes; - } - - my $searchfields = { - standard => C4::Context->preference('DefaultPatronSearchFields') || 'surname,firstname,othernames,cardnumber,userid', - surname => 'surname', - email => 'email,emailpro,B_email', - borrowernumber => 'borrowernumber', - userid => 'userid', - phone => 'phone,phonepro,B_phone,altcontactphone,mobile', - address => 'streettype,address,address2,city,state,zipcode,country', - dateofbirth => 'dateofbirth', - sort1 => 'sort1', - sort2 => 'sort2', - }; + 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(@restricted_branchcodes ) { + push @where_strs, "borrowers.branchcode IN (" . join( ',', ('?') x @restricted_branchcodes ) . ")"; + push @where_args, @restricted_branchcodes; + } + my $searchfields = { + standard => C4::Context->preference('DefaultPatronSearchFields') || 'surname,firstname,othernames,cardnumber,userid', + surname => 'surname', + email => 'email,emailpro,B_email', + borrowernumber => 'borrowernumber', + userid => 'userid', + phone => 'phone,phonepro,B_phone,altcontactphone,mobile', + address => 'streettype,address,address2,city,state,zipcode,country', + dateofbirth => 'dateofbirth', + sort1 => 'sort1', + sort2 => 'sort2', + }; # * is replaced with % for sql $searchmember =~ s/\*/%/g; --- a/koha-tmpl/intranet-tmpl/prog/en/modules/members/member.tt +++ a/koha-tmpl/intranet-tmpl/prog/en/modules/members/member.tt @@ -109,67 +109,14 @@
+

Filters

+

Filter results by entering any value and all data values in the patron record(s) will be checked

  1. - -
  2. -
  3. - - +
  4. @@ -324,12 +271,17 @@ e.preventDefault(); clearFilters(true); }); - $("#searchform").on("submit", filter); + $("#searchform").on("submit", function(e) { + e.preventDefault(); + search = 1; + dtMemberResults.fnDraw(); + }); }); var dtMemberResults; var search = 1; $(document).ready(function() { + var first_query = document.getElementById("searchmember").value; [% IF searchmember %] $("#searchmember_filter").val("[% searchmember | html %]"); [% END %] @@ -390,6 +342,9 @@ return; } aoData.push({ + 'name': 'first_query', + 'value': $("#searchmember").val() + },{ 'name': 'searchmember', 'value': $("#searchmember_filter").val() },{ @@ -475,6 +430,18 @@ update_searched(); }); + function tooltip_display() { + var dateformat= document.getElementById("date_format").value; + if ( dateformat == 'us') { + $('#searchmember_filter').attr("title","Please enter date inputs in the format mm/dd/yyyy").tooltip('show'); + } else if ( dateformat == "metric") { + $('#searchmember_filter').attr("title","Please enter date inputs in the format dd/mm/yyyy").tooltip('show'); + } else if ( dateformat == "iso") { + $('#searchmember_filter').attr("title","Please enter date inputs in the format yyyy-mm-dd").tooltip('show'); + } else if ( dateformat == "dmydot") { + $('#searchmember_filter').attr("title","Please enter date inputs in the format dd.mm.yyyy").tooltip('show'); + } + } // Update the string "Results found ..." function update_searched(){ var searched = $("#searchfieldstype_filter").find("option:selected").text(); --- a/svc/members/search +++ a/svc/members/search @@ -26,6 +26,10 @@ use C4::Utils::DataTables qw( dt_get_params ); use C4::Utils::DataTables::Members qw( search ); use Koha::DateUtils qw( output_pref dt_from_string ); use Koha::Patrons; +use Data::Dumper; +use Time::Piece; +use JSON::XS qw( ); +use HTML::Entities qw( encode_entities ); my $input = new CGI; @@ -39,6 +43,7 @@ my ($template, $user, $cookie) = get_template_and_user({ flagsrequired => { borrowers => 'edit_borrowers' } }); +my $first_query = $input->param('first_query'); my $searchmember = $input->param('searchmember'); my $firstletter = $input->param('firstletter'); my $categorycode = $input->param('categorycode'); @@ -74,42 +79,96 @@ if ( $searchmember # Perform the patrons search $results = C4::Utils::DataTables::Members::search( - { - searchmember => $searchmember, - firstletter => $firstletter, - categorycode => $categorycode, - branchcode => $branchcode, - searchtype => $searchtype, - searchfieldstype => $searchfieldstype, - dt_params => \%dt_params, - } + { + searchmember => $first_query, + firstletter => $firstletter, + categorycode => $categorycode, + branchcode => $branchcode, + searchtype => $searchtype, + searchfieldstype => $searchfieldstype, + dt_params => \%dt_params, + } ) unless $results; -# It is not recommanded to use the has_permission param if you use the pagination -# The filter is done AFTER requested the data -if ($has_permission) { - my ( $permission, $subpermission ) = split /\./, $has_permission; - my @patrons_with_permission; - for my $patron ( @{ $results->{patrons} } ) { - my $perms = haspermission( $patron->{userid} ); - if ( $perms->{superlibrarian} == 1 - or $perms->{$permission} == 1 ) - { - push @patrons_with_permission, $patron; - next; + # It is not recommanded to use the has_permission param if you use the pagination + # The filter is done AFTER requested the data + if ($has_permission) { + my ( $permission, $subpermission ) = split /\./, $has_permission; + my @patrons_with_permission; + for my $patron ( @{ $results->{patrons} } ) { + my $perms = haspermission( $patron->{userid} ); + if ( $perms->{superlibrarian} == 1 + or $perms->{$permission} == 1 ) + { + push @patrons_with_permission, $patron; + next; + } + + if ($subpermission) { + my $subperms = get_user_subpermissions( $patron->{userid} ); + push @patrons_with_permission, $patron + if $subperms->{$permission}->{$subpermission}; + } } - - if ($subpermission) { - my $subperms = get_user_subpermissions( $patron->{userid} ); - push @patrons_with_permission, $patron - if $subperms->{$permission}->{$subpermission}; + $results->{patrons} = \@patrons_with_permission; + $results->{iTotalDisplayRecords} = scalar( @patrons_with_permission ); + } + my $date_format = C4::Context->preference("dateformat"); + + if ($searchmember) { + my @filtered_patron_list; + my $count; + for my $patron ( @{ $results->{patrons} } ) { + my $match; + for my $value (values $patron) { + warn $value; + my $lc_patron_value = lc $value; + my $lc_searchmember = lc $searchmember; + + #Reformat the date value + if ($lc_patron_value =~ /^\d{4}-\d\d-\d\d$/) { + if ($date_format eq "us" ) { + $lc_patron_value = Time::Piece->strptime($lc_patron_value, '%Y-%m-%d')->strftime('%m/%d/%Y'); + } elsif ($date_format eq "metric" ) { + $lc_patron_value = Time::Piece->strptime($lc_patron_value, '%Y-%m-%d')->strftime('%d/%m/%Y'); + } elsif ($date_format eq "iso" ) { + $lc_patron_value = Time::Piece->strptime($lc_patron_value, '%Y-%m-%d')->strftime('%Y-%m-%d'); + } elsif ($date_format eq "dmydot" ) { + $lc_patron_value = Time::Piece->strptime($lc_patron_value, '%Y-%m-%d')->strftime('%d.%m.%Y'); + + } + } + warn $lc_patron_value; + warn $lc_searchmember; + if ($searchtype eq "start_with") { + #Find a match starting with the start of a value in the patron record + if (index($lc_patron_value, $lc_searchmember) == 0) { + $match = 1; + warn $lc_patron_value; + warn $lc_searchmember; + } + } else { + if (($lc_patron_value) =~ /($lc_searchmember)/) { + $match = 1; + } + } + } + if ($match) { + push @filtered_patron_list, $patron; + next; + } } + $results->{patrons} = \@filtered_patron_list; + $results->{iTotalDisplayRecords} = scalar( @filtered_patron_list ); } - $results->{patrons} = \@patrons_with_permission; - $results->{iTotalDisplayRecords} = scalar( @patrons_with_permission ); -} + + my $date_format = C4::Context->preference("dateformat"); + my $json = JSON::XS->new->allow_nonref; + my $date_format = $json->encode($date_format); + my $date_format = encode_entities($date_format); $template->param( + dateformat => $date_format, sEcho => $sEcho, iTotalRecords => $results->{iTotalRecords}, iTotalDisplayRecords => $results->{iTotalDisplayRecords}, --