View | Details | Raw Unified | Return to bug 20346
Collapse All | Expand All

(-)a/C4/Utils/DataTables.pm (+2 lines)
Lines 18-23 package C4::Utils::DataTables; Link Here
18
# along with Koha; if not, see <http://www.gnu.org/licenses>.
18
# along with Koha; if not, see <http://www.gnu.org/licenses>.
19
19
20
use Modern::Perl;
20
use Modern::Perl;
21
use Data::Dumper;
21
require Exporter;
22
require Exporter;
22
23
23
use vars qw(@ISA @EXPORT);
24
use vars qw(@ISA @EXPORT);
Lines 197-202 sub dt_get_params { Link Here
197
    foreach(qw/ iDisplayStart iDisplayLength iColumns sSearch bRegex iSortingCols sEcho /) {
198
    foreach(qw/ iDisplayStart iDisplayLength iColumns sSearch bRegex iSortingCols sEcho /) {
198
        $dtparam{$_} = $input->param($_);
199
        $dtparam{$_} = $input->param($_);
199
    }
200
    }
201
200
    foreach(grep /(?:_sorton|_filteron)$/, keys %$vars) {
202
    foreach(grep /(?:_sorton|_filteron)$/, keys %$vars) {
201
        $dtparam{$_} = $vars->{$_};
203
        $dtparam{$_} = $vars->{$_};
202
    }
204
    }
(-)a/C4/Utils/DataTables/Members.pm (-40 / +40 lines)
Lines 9-20 use C4::Members::Attributes qw(SearchIdMatchingAttribute ); Link Here
9
sub search {
9
sub search {
10
    my ( $params ) = @_;
10
    my ( $params ) = @_;
11
    my $searchmember = $params->{searchmember};
11
    my $searchmember = $params->{searchmember};
12
    my $firstquery = $params->{firstquery};
12
    my $firstletter = $params->{firstletter};
13
    my $firstletter = $params->{firstletter};
13
    my $categorycode = $params->{categorycode};
14
    my $categorycode = $params->{categorycode};
14
    my $branchcode = $params->{branchcode};
15
    my $branchcode = $params->{branchcode};
15
    my $searchtype = $params->{searchtype} || 'contain';
16
    my $searchtype = $params->{searchtype} || 'contain';
16
    my $searchfieldstype = $params->{searchfieldstype} || 'standard';
17
    my $searchfieldstype = $params->{searchfieldstype} || 'standard';
17
    my $dt_params = $params->{dt_params};
18
    my $dt_params = $params->{dt_params};
19
    my $searchfiltering = $params->{search_filter};
18
20
19
    unless ( $searchmember ) {
21
    unless ( $searchmember ) {
20
        $searchmember = $dt_params->{sSearch} // '';
22
        $searchmember = $dt_params->{sSearch} // '';
Lines 55-100 sub search { Link Here
55
            };
57
            };
56
        }
58
        }
57
    }
59
    }
58
60
        my $select = "SELECT
59
    my $select = "SELECT
61
            borrowers.borrowernumber, borrowers.surname, borrowers.firstname,
60
        borrowers.borrowernumber, borrowers.surname, borrowers.firstname,
62
            borrowers.streetnumber, borrowers.streettype, borrowers.address,
61
        borrowers.streetnumber, borrowers.streettype, borrowers.address,
63
            borrowers.address2, borrowers.city, borrowers.state, borrowers.zipcode,
62
        borrowers.address2, borrowers.city, borrowers.state, borrowers.zipcode,
64
            borrowers.country, cardnumber, borrowers.dateexpiry,
63
        borrowers.country, cardnumber, borrowers.dateexpiry,
65
            borrowers.borrowernotes, borrowers.branchcode, borrowers.email,
64
        borrowers.borrowernotes, borrowers.branchcode, borrowers.email,
66
            borrowers.userid, borrowers.dateofbirth, borrowers.categorycode,
65
        borrowers.userid, borrowers.dateofbirth, borrowers.categorycode,
67
            categories.description AS category_description, categories.category_type,
66
        categories.description AS category_description, categories.category_type,
68
            branches.branchname";
67
        branches.branchname";
69
        my $from = "FROM borrowers
68
    my $from = "FROM borrowers
70
            LEFT JOIN branches ON borrowers.branchcode = branches.branchcode
69
        LEFT JOIN branches ON borrowers.branchcode = branches.branchcode
71
            LEFT JOIN categories ON borrowers.categorycode = categories.categorycode";
70
        LEFT JOIN categories ON borrowers.categorycode = categories.categorycode";
72
        my @where_args;
71
    my @where_args;
73
        my @where_strs;
72
    my @where_strs;
74
        if(defined $firstletter and $firstletter ne '') {
73
    if(defined $firstletter and $firstletter ne '') {
75
            push @where_strs, "borrowers.surname LIKE ?";
74
        push @where_strs, "borrowers.surname LIKE ?";
76
            push @where_args, "$firstletter%";
75
        push @where_args, "$firstletter%";
77
        }
76
    }
78
        if(defined $categorycode and $categorycode ne '') {
77
    if(defined $categorycode and $categorycode ne '') {
79
            push @where_strs, "borrowers.categorycode = ?";
78
        push @where_strs, "borrowers.categorycode = ?";
80
            push @where_args, $categorycode;
79
        push @where_args, $categorycode;
81
        }
80
    }
82
        if(@restricted_branchcodes ) {
81
    if(@restricted_branchcodes ) {
83
            push @where_strs, "borrowers.branchcode IN (" . join( ',', ('?') x @restricted_branchcodes ) . ")";
82
        push @where_strs, "borrowers.branchcode IN (" . join( ',', ('?') x @restricted_branchcodes ) . ")";
84
            push @where_args, @restricted_branchcodes;
83
        push @where_args, @restricted_branchcodes;
85
        }
84
    }
86
        my $searchfields = {
85
87
            standard => C4::Context->preference('DefaultPatronSearchFields') || 'surname,firstname,othernames,cardnumber,userid',
86
    my $searchfields = {
88
            surname => 'surname',
87
        standard => C4::Context->preference('DefaultPatronSearchFields') || 'surname,firstname,othernames,cardnumber,userid',
89
            email => 'email,emailpro,B_email',
88
        surname => 'surname',
90
            borrowernumber => 'borrowernumber',
89
        email => 'email,emailpro,B_email',
91
            userid => 'userid',
90
        borrowernumber => 'borrowernumber',
92
            phone => 'phone,phonepro,B_phone,altcontactphone,mobile',
91
        userid => 'userid',
93
            address => 'streettype,address,address2,city,state,zipcode,country',
92
        phone => 'phone,phonepro,B_phone,altcontactphone,mobile',
94
            dateofbirth => 'dateofbirth',
93
        address => 'streettype,address,address2,city,state,zipcode,country',
95
            sort1 => 'sort1',
94
        dateofbirth => 'dateofbirth',
96
            sort2 => 'sort2',
95
        sort1 => 'sort1',
97
        };
96
        sort2 => 'sort2',
97
    };
98
98
99
    # * is replaced with % for sql
99
    # * is replaced with % for sql
100
    $searchmember =~ s/\*/%/g;
100
    $searchmember =~ s/\*/%/g;
(-)a/koha-tmpl/intranet-tmpl/prog/en/modules/members/member.tt (-57 / +24 lines)
Lines 109-175 Link Here
109
    <div class="yui-b">
109
    <div class="yui-b">
110
      <form method="get" id="searchform">
110
      <form method="get" id="searchform">
111
        <input type="hidden" id="firstletter_filter" value="" />
111
        <input type="hidden" id="firstletter_filter" value="" />
112
        <input type="hidden" id="date_format" value="[% dateformat%]"/>
112
        <fieldset class="brief">
113
        <fieldset class="brief">
113
          <h3>Filters</h3>
114
          <h3>Filters</h3>
115
          <p> Filter results by entering any value and all data values in the patron record(s) will be checked </p>
114
          <ol>
116
          <ol>
115
            <li>
117
            <li>
116
              <label for="searchmember_filter">Search:</label>
118
              <label for="searchmember_filter">Search:</label>
117
              <input type="text" id="searchmember_filter" value="[% searchmember %]"/>
119
              <input type="text" id="searchmember_filter" onmouseover="tooltip_display()" value="[% searchmember %]"/>
118
            </li>
119
            <li>
120
              <label for="searchfieldstype_filter">Search fields:</label>
121
              <select name="searchfieldstype" id="searchfieldstype_filter">
122
                [% IF searchfieldstype == "standard" %]
123
                  <option selected="selected" value='standard'>Standard</option>
124
                [% ELSE %]
125
                  <option value='standard'>Standard</option>
126
                [% END %]
127
                [% IF searchfieldstype == "surname" %]
128
                  <option selected="selected" value='surname'>Surname</option>
129
                [% ELSE %]
130
                  <option value='surname'>Surname</option>
131
                [% END %]
132
                [% IF searchfieldstype == "email" %]
133
                  <option selected="selected" value='email'>Email</option>
134
                [% ELSE %]
135
                  <option value='email'>Email</option>
136
                [% END %]
137
                [% IF searchfieldstype == "borrowernumber" %]
138
                  <option selected="selected" value='borrowernumber'>Borrower number</option>
139
                [% ELSE %]
140
                  <option value='borrowernumber'>Borrower number</option>
141
                [% END %]
142
                [% IF searchfieldstype == "userid" %]
143
                  <option selected="selected" value='userid'>Username</option>
144
                [% ELSE %]
145
                  <option value='userid'>Username</option>
146
                [% END %]
147
                [% IF searchfieldstype == "phone" %]
148
                  <option selected="selected" value='phone'>Phone number</option>
149
                [% ELSE %]
150
                  <option value='phone'>Phone number</option>
151
                [% END %]
152
                [% IF searchfieldstype == "address" %]
153
                  <option selected="selected" value='address'>Street address</option>
154
                [% ELSE %]
155
                  <option value='address'>Street address</option>
156
                [% END %]
157
                [% IF searchfieldstype == "dateofbirth" %]
158
                  <option selected="selected" value='dateofbirth'>Date of birth</option>
159
                [% ELSE %]
160
                  <option value='dateofbirth'>Date of birth</option>
161
                [% END %]
162
                [% IF searchfieldstype == "sort1" %]
163
                  <option selected="selected" value='sort1'>Sort field 1</option>
164
                [% ELSE %]
165
                  <option value='sort1'>Sort field 1</option>
166
                [% END %]
167
                [% IF searchfieldstype == "sort2" %]
168
                  <option selected="selected" value='sort2'>Sort field 2</option>
169
                [% ELSE %]
170
                  <option value='sort2'>Sort field 2</option>
171
                [% END %]
172
              </select>
173
            </li>
120
            </li>
174
            <li>
121
            <li>
175
              <label for="searchtype_filter">Search type:</label>
122
              <label for="searchtype_filter">Search type:</label>
Lines 324-335 Link Here
324
                e.preventDefault();
271
                e.preventDefault();
325
                clearFilters(true);
272
                clearFilters(true);
326
            });
273
            });
327
            $("#searchform").on("submit", filter);
274
            $("#searchform").on("submit", function(e) {
275
                e.preventDefault();
276
                search = 1;
277
                dtMemberResults.fnDraw();
278
            });
328
        });
279
        });
329
280
330
        var dtMemberResults;
281
        var dtMemberResults;
331
        var search = 1;
282
        var search = 1;
332
        $(document).ready(function() {
283
        $(document).ready(function() {
284
            var first_query = document.getElementById("searchmember").value; 
333
            [% IF searchmember %]
285
            [% IF searchmember %]
334
                $("#searchmember_filter").val("[% searchmember | html %]");
286
                $("#searchmember_filter").val("[% searchmember | html %]");
335
            [% END %]
287
            [% END %]
Lines 390-395 Link Here
390
                        return;
342
                        return;
391
                    }
343
                    }
392
                    aoData.push({
344
                    aoData.push({
345
                        'name': 'first_query',
346
                        'value': $("#searchmember").val()
347
                    },{
393
                        'name': 'searchmember',
348
                        'name': 'searchmember',
394
                        'value': $("#searchmember_filter").val()
349
                        'value': $("#searchmember_filter").val()
395
                    },{
350
                    },{
Lines 475-480 Link Here
475
            update_searched();
430
            update_searched();
476
        });
431
        });
477
432
433
        function tooltip_display() {
434
                var dateformat= document.getElementById("date_format").value;
435
                if ( dateformat == 'us') {
436
                    $('#searchmember_filter').attr("title","Please enter date inputs in the format mm/dd/yyyy").tooltip('show');
437
                } else if ( dateformat  == "metric") {
438
                    $('#searchmember_filter').attr("title","Please enter date inputs in the format dd/mm/yyyy").tooltip('show');
439
                } else if ( dateformat  == "iso") {
440
                    $('#searchmember_filter').attr("title","Please enter date inputs in the format yyyy-mm-dd").tooltip('show');
441
                } else if ( dateformat == "dmydot") {
442
                    $('#searchmember_filter').attr("title","Please enter date inputs in the format dd.mm.yyyy").tooltip('show');
443
                }
444
        }
478
        // Update the string "Results found ..."
445
        // Update the string "Results found ..."
479
        function update_searched(){
446
        function update_searched(){
480
            var searched = $("#searchfieldstype_filter").find("option:selected").text();
447
            var searched = $("#searchfieldstype_filter").find("option:selected").text();
(-)a/svc/members/search (-33 / +121 lines)
Lines 26-31 use C4::Utils::DataTables qw( dt_get_params ); Link Here
26
use C4::Utils::DataTables::Members qw( search );
26
use C4::Utils::DataTables::Members qw( search );
27
use Koha::DateUtils qw( output_pref dt_from_string );
27
use Koha::DateUtils qw( output_pref dt_from_string );
28
use Koha::Patrons;
28
use Koha::Patrons;
29
use Data::Dumper;
30
use Time::Piece;
31
use JSON::XS qw( );
32
use HTML::Entities qw( encode_entities );
33
use List::Slice 'head';
29
34
30
my $input = new CGI;
35
my $input = new CGI;
31
36
Lines 39-44 my ($template, $user, $cookie) = get_template_and_user({ Link Here
39
    flagsrequired   => { borrowers => 'edit_borrowers' }
44
    flagsrequired   => { borrowers => 'edit_borrowers' }
40
});
45
});
41
46
47
my $first_query = $input->param('first_query');
42
my $searchmember = $input->param('searchmember');
48
my $searchmember = $input->param('searchmember');
43
my $firstletter  = $input->param('firstletter');
49
my $firstletter  = $input->param('firstletter');
44
my $categorycode = $input->param('categorycode');
50
my $categorycode = $input->param('categorycode');
Lines 50-57 my $selection_type = $input->param('selection_type'); Link Here
50
56
51
# variable information for DataTables (id)
57
# variable information for DataTables (id)
52
my $sEcho = $input->param('sEcho');
58
my $sEcho = $input->param('sEcho');
53
54
my %dt_params = dt_get_params($input);
59
my %dt_params = dt_get_params($input);
60
55
foreach (grep {$_ =~ /^mDataProp/} keys %dt_params) {
61
foreach (grep {$_ =~ /^mDataProp/} keys %dt_params) {
56
    $dt_params{$_} =~ s/^dt_//;
62
    $dt_params{$_} =~ s/^dt_//;
57
}
63
}
Lines 72-115 if ( $searchmember Link Here
72
    } if $member;
78
    } if $member;
73
}
79
}
74
80
75
# Perform the patrons search
81
my $filterable_results;
76
$results = C4::Utils::DataTables::Members::search(
82
if ($searchmember ne $first_query) {
77
    {
83
    # Perform the patrons search
78
        searchmember => $searchmember,
84
    $results = C4::Utils::DataTables::Members::search(
79
        firstletter => $firstletter,
80
        categorycode => $categorycode,
81
        branchcode => $branchcode,
82
        searchtype => $searchtype,
83
        searchfieldstype => $searchfieldstype,
84
        dt_params => \%dt_params,
85
    }
86
) unless $results;
87
88
# It is not recommanded to use the has_permission param if you use the pagination
89
# The filter is done AFTER requested the data
90
if ($has_permission) {
91
    my ( $permission, $subpermission ) = split /\./, $has_permission;
92
    my @patrons_with_permission;
93
    for my $patron ( @{ $results->{patrons} } ) {
94
        my $perms = haspermission( $patron->{userid} );
95
        if (   $perms->{superlibrarian} == 1
96
            or $perms->{$permission} == 1 )
97
        {
85
        {
98
            push @patrons_with_permission, $patron;
86
            searchmember => $first_query,
99
            next;
87
            firstletter => $firstletter,
88
            categorycode => $categorycode,
89
            branchcode => $branchcode,
90
            searchtype => $searchtype,
91
            searchfieldstype => $searchfieldstype,
92
            dt_params => \%dt_params,
100
        }
93
        }
94
    ) unless $results;
101
95
102
        if ($subpermission) {
96
    my $value_to_display = $dt_params{'iDisplayLength'};
103
            my $subperms = get_user_subpermissions( $patron->{userid} );
97
    $dt_params{'iDisplayLength'} = -1;
104
            push @patrons_with_permission, $patron
98
105
              if $subperms->{$permission}->{$subpermission};
99
    # Retreive all patrons matching the $first_query value. These will be filtered
100
    $filterable_results = C4::Utils::DataTables::Members::search(
101
        {
102
           searchmember => $first_query,
103
           firstletter => $firstletter,
104
           categorycode => $categorycode,
105
           branchcode => $branchcode,
106
           searchtype => $searchtype,
107
           searchfieldstype => $searchfieldstype,
108
           dt_params => \%dt_params,
106
        }
109
        }
107
    }
110
        ) unless $filterable_results;
108
    $results->{patrons} = \@patrons_with_permission;
111
} else {
109
    $results->{iTotalDisplayRecords} = scalar( @patrons_with_permission );
112
    # Perform the patrons search
113
    $results = C4::Utils::DataTables::Members::search(
114
      {
115
           searchmember => $first_query,
116
           firstletter => $firstletter,
117
           categorycode => $categorycode,
118
           branchcode => $branchcode,
119
           searchtype => $searchtype,
120
           searchfieldstype => $searchfieldstype,
121
           dt_params => \%dt_params,
122
      }
123
      ) unless $results;
110
}
124
}
111
125
126
    # It is not recommanded to use the has_permission param if you use the pagination
127
    # The filter is done AFTER requested the data
128
    if ($has_permission) {
129
        my ( $permission, $subpermission ) = split /\./, $has_permission;
130
        my @patrons_with_permission;
131
        for my $patron ( @{ $results->{patrons} } ) {
132
            my $perms = haspermission( $patron->{userid} );
133
            if (   $perms->{superlibrarian} == 1
134
                or $perms->{$permission} == 1 )
135
            {
136
                push @patrons_with_permission, $patron;
137
                next;
138
            }
139
140
            if ($subpermission) {
141
                my $subperms = get_user_subpermissions( $patron->{userid} );
142
                push @patrons_with_permission, $patron
143
                if $subperms->{$permission}->{$subpermission};
144
            }
145
        }
146
        $results->{patrons} = \@patrons_with_permission;
147
        $results->{iTotalDisplayRecords} = scalar( @patrons_with_permission );
148
    }
149
    my $date_format = C4::Context->preference("dateformat");
150
151
    my $count;
152
    if ($searchmember ne $first_query) {
153
        my @filtered_patron_list;
154
        for my $patron ( @{ $filterable_results->{patrons} } ) {
155
            my $match;
156
            for my $value (values $patron) {
157
                my $lc_patron_value = lc $value;
158
                my $lc_searchmember = lc $searchmember;
159
160
                #Reformat the date value
161
                if ($lc_patron_value =~ /^\d{4}-\d\d-\d\d$/) {
162
                    if ($date_format eq "us" ) {
163
                       $lc_patron_value = Time::Piece->strptime($lc_patron_value, '%Y-%m-%d')->strftime('%m/%d/%Y');
164
                    } elsif ($date_format eq "metric" ) {
165
                         $lc_patron_value = Time::Piece->strptime($lc_patron_value, '%Y-%m-%d')->strftime('%d/%m/%Y');
166
                    } elsif ($date_format eq "iso" ) {
167
                         $lc_patron_value = Time::Piece->strptime($lc_patron_value, '%Y-%m-%d')->strftime('%Y-%m-%d');
168
                    } elsif ($date_format eq "dmydot" ) {
169
                         $lc_patron_value = Time::Piece->strptime($lc_patron_value, '%Y-%m-%d')->strftime('%d.%m.%Y');
170
171
                    }
172
                }
173
                if ($searchtype eq "start_with") {
174
                    #Find a match starting with the start of a value in the patron record
175
                    if (index($lc_patron_value, $lc_searchmember) == 0) {
176
                        $match = 1;
177
                    }
178
                } else {
179
                    if (($lc_patron_value) =~ /($lc_searchmember)/) {
180
                        $match = 1;
181
                    }
182
                }
183
            }
184
            if ($match) {
185
                $count++;
186
                push @filtered_patron_list, $patron;
187
                next;
188
            }
189
        }
190
        $results->{patrons} = \@filtered_patron_list;
191
        if ($searchmember ne $first_query) {
192
            $results->{iTotalDisplayRecords} = scalar @{$results->{patrons}};
193
        }
194
    }
195
    $date_format = C4::Context->preference("dateformat");
196
    my $json = JSON::XS->new->allow_nonref;
197
    $date_format = $json->encode($date_format);
198
    $date_format = encode_entities($date_format);
199
112
$template->param(
200
$template->param(
201
    dateformat => $date_format,
113
    sEcho => $sEcho,
202
    sEcho => $sEcho,
114
    iTotalRecords => $results->{iTotalRecords},
203
    iTotalRecords => $results->{iTotalRecords},
115
    iTotalDisplayRecords => $results->{iTotalDisplayRecords},
204
    iTotalDisplayRecords => $results->{iTotalDisplayRecords},
116
- 

Return to bug 20346