@@ -, +, @@ guarantor to an existing patron --- C4/Utils/DataTables/Members.pm | 317 ++++++++++++++++++ .../en/modules/admin/preferences/patrons.pref | 2 +- .../prog/en/modules/common/patron_search.tt | 300 +++++++++++++++++ .../prog/en/modules/members/memberentrygen.tt | 2 +- .../members/tables/guarantor_search.tt | 36 ++ koha-tmpl/intranet-tmpl/prog/js/members.js | 13 +- svc/members/search | 153 +++++++++ 7 files changed, 819 insertions(+), 4 deletions(-) create mode 100644 C4/Utils/DataTables/Members.pm create mode 100644 koha-tmpl/intranet-tmpl/prog/en/modules/common/patron_search.tt create mode 100644 koha-tmpl/intranet-tmpl/prog/en/modules/members/tables/guarantor_search.tt create mode 100755 svc/members/search --- a/C4/Utils/DataTables/Members.pm +++ a/C4/Utils/DataTables/Members.pm @@ -0,0 +1,317 @@ +package C4::Utils::DataTables::Members; + +use Modern::Perl; +use C4::Context; +use C4::Utils::DataTables; +use Koha::DateUtils; + +sub search { + my ( $params ) = @_; + my $searchmember = $params->{searchmember}; + my $firstletter = $params->{firstletter}; + my $categorycode = $params->{categorycode}; + my $branchcode = $params->{branchcode}; + my $searchtype = $params->{searchtype} || 'contain'; + my $searchfieldstype = $params->{searchfieldstype} || 'standard'; + my $has_permission = $params->{has_permission}; + my $dt_params = $params->{dt_params}; + + unless ( $searchmember ) { + $searchmember = $dt_params->{sSearch} // ''; + } + + # If branches are independent and user is not superlibrarian + # The search has to be only on the user 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 module_bit from a given permission code + if ( $has_permission ) { + ($has_permission->{module_bit}) = $dbh->selectrow_array(q| + SELECT bit FROM userflags WHERE flag=? + |, undef, $has_permission->{permission}); + } + + my (@where, @conditions); + # Get the iTotalRecords DataTable variable + $iTotalQuery = "SELECT COUNT(borrowers.borrowernumber) FROM borrowers"; + if ( $has_permission ) { + $iTotalQuery .= ' LEFT JOIN user_permissions ON borrowers.borrowernumber=user_permissions.borrowernumber'; + $iTotalQuery .= ' AND module_bit=? AND code=?'; + push @conditions, $has_permission->{module_bit}, $has_permission->{subpermission}; + } + + if ( @restricted_branchcodes ) { + push @where, "borrowers.branchcode IN (" . join( ',', ('?') x @restricted_branchcodes ) . ")"; + push @conditions, @restricted_branchcodes; + } + if ( $has_permission ) { + push @where, '( borrowers.flags = 1 OR borrowers.flags & (1 << ?) OR module_bit=? AND code=? )'; + push @conditions, ($has_permission->{module_bit}) x 2, $has_permission->{subpermission}; + } + $iTotalQuery .= ' WHERE ' . join ' AND ', @where if @where; + ($iTotalRecords) = $dbh->selectrow_array( $iTotalQuery, undef, @conditions ); + + # 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 @columns = qw( borrowernumber surname firstname othernames flags streetnumber streettype address address2 city state zipcode country cardnumber dateexpiry borrowernotes branchcode email userid dateofbirth categorycode phone phonepro mobile fax email emailpro); + + my $prefillguarantorfields = C4::Context->preference("PrefillGuaranteeField"); + my @prefill_fields = split(/\,/,$prefillguarantorfields); + if ( @prefill_fields ) { + foreach my $field (@prefill_fields) { + if (! grep {$_ eq $field} @columns) { + push @columns, $field; + } + } + }; + + my $borrowers_columns = ""; + foreach my $item (@columns) { + $borrowers_columns .= "borrowers." . $item . ", "; + } + + my $select = "SELECT " . $borrowers_columns . " + categories.description AS category_description, categories.category_type, + branches.branchname, borrowers.phone"; + my $from = "FROM borrowers + LEFT JOIN branches ON borrowers.branchcode = branches.branchcode + LEFT JOIN categories ON borrowers.categorycode = categories.categorycode"; + my @where_args; + if ( $has_permission ) { + $from .= ' + LEFT JOIN user_permissions ON borrowers.borrowernumber=user_permissions.borrowernumber + AND module_bit=? AND code=?'; + push @where_args, $has_permission->{module_bit}, $has_permission->{subpermission}; + } + 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', + email => 'email,emailpro,B_email', + borrowernumber => 'borrowernumber', + phone => 'phone,phonepro,B_phone,altcontactphone,mobile', + address => 'streetnumber,streettype,address,address2,city,state,zipcode,country', + }; + + # * 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; + + 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; + 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 ( $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; + } + } + + push @where_strs, '('. join (' OR ', @where_strs_or) . ')' + if @where_strs_or; + } + + if ( $has_permission ) { + push @where_strs, '( borrowers.flags = 1 OR borrowers.flags & (1 << ?) OR module_bit=? AND code=? )'; + push @where_args, ($has_permission->{module_bit}) x 2, $has_permission->{subpermission}; + } + + my $where = @where_strs ? " WHERE " . join (" AND ", @where_strs) : undef; + 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}"; + } + + $query = join( + " ", + ($select ? $select : ""), + ($from ? $from : ""), + ($where ? $where : ""), + ($orderby ? $orderby : ""), + ($limit ? $limit : "") + ); + $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 some information on patrons + foreach my $patron (@$patrons) { + my $patron_object = Koha::Patrons->find( $patron->{borrowernumber} ); + $patron->{overdues} = $patron_object->get_overdues->count; + $patron->{issues} = $patron_object->checkouts->count; + $patron->{age} = $patron_object->get_age; + 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} = ''; + } + } + + return { + iTotalRecords => $iTotalRecords, + iTotalDisplayRecords => $iTotalDisplayRecords, + patrons => $patrons + } +} + +1; +__END__ + +=head1 NAME + +C4::Utils::DataTables::Members - module for using DataTables with patrons + +=head1 SYNOPSIS + +This module provides (one for the moment) routines used by the patrons search + +=head2 FUNCTIONS + +=head3 search + + my $dt_infos = C4::Utils::DataTables::Members->search($params); + +$params is a hashref with some keys: + +=over 4 + +=item searchmember + + String to search in the borrowers sql table + +=item firstletter + + Introduced to contain 1 letter but can contain more. + The search will done on the borrowers.surname field + +=item categorycode + + Search patrons with this categorycode + +=item branchcode + + Search patrons with this branchcode + +=item searchtype + + Can be 'start_with' or 'contain' (default value). Used for the searchmember parameter. + +=item searchfieldstype + + Can be 'standard' (default value), 'email', 'borrowernumber', 'phone', 'address' or 'dateofbirth', 'sort1', 'sort2' + +=item dt_params + + Is the reference of C4::Utils::DataTables::dt_get_params($input); + +=cut + +=back + +=head1 LICENSE + +This file is part of Koha. + +Copyright 2013 BibLibre + +Koha is free software; you can redistribute it and/or modify it +under the terms of the GNU General Public License as published by +the Free Software Foundation; either version 3 of the License, or +(at your option) any later version. + +Koha is distributed in the hope that it will be useful, but +WITHOUT ANY WARRANTY; without even the implied warranty of +MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +GNU General Public License for more details. + +You should have received a copy of the GNU General Public License +along with Koha; if not, see . --- a/koha-tmpl/intranet-tmpl/prog/en/modules/admin/preferences/patrons.pref +++ a/koha-tmpl/intranet-tmpl/prog/en/modules/admin/preferences/patrons.pref @@ -298,7 +298,7 @@ Patrons: class: multi - (input multiple choices separated by |). Leave empty to deactivate. - - - "When adding a guarantee to a guarantor patron fill the following fields in the guarantee's member entry form from the guarantors record:" + - "When adding a guarantor relationship to a patron fill the following unfilled fields in the guarantee's member entry form from the guarantors record:" - pref: PrefillGuaranteeField multiple: B_streettype: "Alternate address - Street type" --- a/koha-tmpl/intranet-tmpl/prog/en/modules/common/patron_search.tt +++ a/koha-tmpl/intranet-tmpl/prog/en/modules/common/patron_search.tt @@ -0,0 +1,300 @@ +[% USE raw %] +[% USE Asset %] +[% USE Koha %] +[% USE Branches %] +[% SET footerjs = 1 %] +[% INCLUDE 'doc-head-open.inc' %] +Koha › Patron search +[% INCLUDE 'doc-head-close.inc' %] + + + + + + +[% MACRO jsinclude BLOCK %] + [% INCLUDE 'datatables.inc' %] + + +[% END %] + +[% SET popup_window = 1 %] +[% INCLUDE 'intranet-bottom.inc' %] --- a/koha-tmpl/intranet-tmpl/prog/en/modules/members/memberentrygen.tt +++ a/koha-tmpl/intranet-tmpl/prog/en/modules/members/memberentrygen.tt @@ -1772,7 +1772,7 @@ legend:hover { [% IF new_guarantors %] [% FOREACH g IN new_guarantors %] - select_user( '[% g.patron.borrowernumber | html %]', [% To.json( g.patron.unblessed ) | $raw %], '[% g.relationship | html %]' ); + select_user( '[% g.patron.borrowernumber | html %]', [% To.json( g.patron.unblessed ) | $raw %], '[% g.relationship | html %]' [% IF guarantor_attributes %], [% To.json( guarantor_attributes ) | $raw %][% END %] ); [% END %] [% END %] --- a/koha-tmpl/intranet-tmpl/prog/en/modules/members/tables/guarantor_search.tt +++ a/koha-tmpl/intranet-tmpl/prog/en/modules/members/tables/guarantor_search.tt @@ -0,0 +1,36 @@ +[% USE raw %] +[% USE To %] +[% USE Branches %] +[% USE KohaDates %] +{ + "sEcho": [% sEcho | html %], + "iTotalRecords": [% iTotalRecords | html %], + "iTotalDisplayRecords": [% iTotalDisplayRecords | html %], + "aaData": [ + [% FOREACH data IN aaData %] + { + "dt_cardnumber": + "[% data.cardnumber | html %]", + "dt_name": + "[% INCLUDE 'patron-title.inc' borrowernumber = data.borrowernumber category_type = data.category_type firstname = data.firstname surname = data.surname othernames = data.othernames cardnumber = data.cardnumber invert_name = 1%]", + "dt_dateofbirth": + "[% INCLUDE 'patron-age.inc' patron = data %]", + "dt_address": + "[% INCLUDE escape_address data=data %]", + "dt_action": + "Select" + }[% UNLESS loop.last %],[% END %] + [% END %] + ] +} +[% BLOCK escape_address %] +[%~ SET address = data.streetnumber _ ' ' %] +[%~ IF data.address %][% SET address = address _ data.address _ ' ' %][% END %] +[%~ IF data.address2 %][% SET address = address _ data.address2 _ ' ' %][% END %] +[%~ IF data.city %][% SET address = address _ data.city _ ' ' %][% END %] +[%~ IF data.state %][% SET address = address _ data.state _ ' ' %][% END %] +[%~ IF data.zipcode %][% SET address = address _ data.zipcode _ ' ' %][% END %] +[%~ IF data.country %][% SET address = address _ data.country _ ' ' %][% END %] +[%~ SET address = address _ Branches.GetName( data.branchcode ) %] +[%~ To.json( address ) | $raw ~%] +[% END %] --- a/koha-tmpl/intranet-tmpl/prog/js/members.js +++ a/koha-tmpl/intranet-tmpl/prog/js/members.js @@ -76,8 +76,8 @@ function update_category_code(category_code) { hint.html(hint_string); } -function select_user(borrowernumber, borrower, relationship) { - let is_guarantor = $(`.guarantor-details[data-borrowernumber=${borrowernumber}]`).length; +function select_user(borrowernumber, borrower, relationship, attributes) { + let is_guarantor = $(`.guarantor-details[data-borrowernumber=${borrower.borrowernumber}]`).length; if ( is_guarantor ) { alert("Patron is already a guarantor for this patron"); @@ -121,6 +121,15 @@ function select_user(borrowernumber, borrower, relationship) { if ( relationship ) { fieldset.find('.new_guarantor_relationship').val(relationship); } + + if ( attributes ) { + for (var i = 0; i < parseInt(attributes.length, 10); i++) { + var attribute = attributes[i]; + if ( borrower[attribute] != null && document.forms.entryform[attribute].value == "" ) { + document.forms.entryform[attribute].value = borrower[attribute]; + } + } + } } return 0; --- a/svc/members/search +++ a/svc/members/search @@ -0,0 +1,153 @@ +#!/usr/bin/perl + +# Copyright 2013 BibLibre +# +# This file is part of Koha +# +# Koha is free software; you can redistribute it and/or modify it +# under the terms of the GNU General Public License as published by +# the Free Software Foundation; either version 3 of the License, or +# (at your option) any later version. +# +# Koha is distributed in the hope that it will be useful, but +# WITHOUT ANY WARRANTY; without even the implied warranty of +# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +# GNU General Public License for more details. +# +# You should have received a copy of the GNU General Public License +# along with Koha; if not, see . + +use Modern::Perl; +use CGI; + +use C4::Auth qw( get_template_and_user haspermission get_user_subpermissions ); +use C4::Output qw( output_with_http_headers ); +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; + +my $input = CGI->new; + +exit unless $input->param('template_path'); + +my ($template, $user, $cookie) = get_template_and_user({ + template_name => scalar $input->param('template_path'), + query => $input, + type => "intranet", + flagsrequired => { borrowers => 'edit_borrowers' } +}); + +my $searchmember = $input->param('searchmember'); +my $firstletter = $input->param('firstletter'); +my $categorycode = $input->param('categorycode'); +my $branchcode = $input->param('branchcode'); +my $searchtype = $input->param('searchtype'); +my $searchfieldstype = $input->param('searchfieldstype') || 'standard'; +my $has_permission = $input->param('has_permission'); +my $selection_type = $input->param('selection_type'); + +# variable information for DataTables (id) +my $sEcho = $input->param('sEcho'); + +my %dt_params = dt_get_params($input); +foreach (grep {$_ =~ /^mDataProp/} keys %dt_params) { + $dt_params{$_} =~ s/^dt_//; +} + +my $results; +# If the user filled a term, maybe it's a cardnumber. +# This cannot be the case if a first letter is given. +if ( $searchmember + and not $firstletter + and $searchfieldstype + and $searchfieldstype eq 'standard' ) +{ + my $member = Koha::Patrons->find( { cardnumber => $searchmember } ); + $results = { + iTotalRecords => 1, + iTotalDisplayRecords => 1, + patrons => [ $member->unblessed ], + } if $member; +} + +if ($has_permission) { + my ( $permission, $subpermission ) = split /\./, $has_permission; + $has_permission = {permission => $permission, subpermission => $subpermission}; +} + +# 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, + ( $has_permission ? ( has_permission => $has_permission ) : () ), + } +) unless $results; + +my $prefillguarantorfields = C4::Context->preference("PrefillGuaranteeField"); +my @prefill_fields = split(/\,/,$prefillguarantorfields); + +$template->param( + sEcho => $sEcho, + iTotalRecords => $results->{iTotalRecords}, + iTotalDisplayRecords => $results->{iTotalDisplayRecords}, + aaData => $results->{patrons}, + selection_type => $selection_type, + guarantor_attributes => \@prefill_fields, +); + +output_with_http_headers $input, $cookie, $template->output, 'json'; + +__END__ + +=head1 NAME + +search - a search script for finding patrons + +=head1 SYNOPSIS + +This script provides a service for template for patron search using DataTables + +=head2 Performing a search + +Call this script from a DataTables table my $searchmember = $input->param('searchmember'); +All following params are optional: + searchmember => the search terms + firstletter => search patrons with surname begins with this pattern (currently only used for 1 letter) + categorycode and branchcode => search patrons belong to a given categorycode or a branchcode + searchtype: can be 'contain' or 'start_with' + searchfieldstype: Can be 'standard', 'email', 'borrowernumber', 'userid', 'phone' or 'address' + +=cut + +=back + +=head1 LICENSE + +Copyright 2013 BibLibre + +This file is part of Koha. + +Koha is free software; you can redistribute it and/or modify it under the +terms of the GNU General Public License as published by the Free Software +Foundation; either version 2 of the License, or (at your option) any later +version. + +Koha is free software; you can redistribute it and/or modify it +under the terms of the GNU General Public License as published by +the Free Software Foundation; either version 3 of the License, or +(at your option) any later version. + +Koha is distributed in the hope that it will be useful, but +WITHOUT ANY WARRANTY; without even the implied warranty of +MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +GNU General Public License for more details. + +You should have received a copy of the GNU General Public License +along with Koha; if not, see . --