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

(-)a/C4/Utils/DataTables/Members.pm (-84 / +55 lines)
Lines 1-10 Link Here
1
package C4::Utils::DataTables::Members;
1
package C4::Utils::DataTables::Members;
2
2
3
use Modern::Perl;
3
use Modern::Perl;
4
use Catmandu::Store::ElasticSearch;
4
use C4::Context;
5
use C4::Context;
5
use C4::Members qw/GetMemberIssuesAndFines/;
6
use C4::Members qw/GetMemberIssuesAndFines/;
6
use C4::Utils::DataTables;
7
use C4::Utils::DataTables;
7
use Koha::DateUtils;
8
use Koha::DateUtils;
9
use Koha::SearchEngine::Elasticsearch::Search;
8
10
9
sub search {
11
sub search {
10
    my ( $params ) = @_;
12
    my ( $params ) = @_;
Lines 30-63 sub search { Link Here
30
32
31
    }
33
    }
32
34
33
    my $dbh = C4::Context->dbh;
34
    my $select = "SELECT
35
        borrowers.borrowernumber, borrowers.surname, borrowers.firstname,
36
        borrowers.streetnumber, borrowers.streettype, borrowers.address,
37
        borrowers.address2, borrowers.city, borrowers.state, borrowers.zipcode,
38
        borrowers.country, cardnumber, borrowers.dateexpiry,
39
        borrowers.borrowernotes, borrowers.branchcode, borrowers.email,
40
        borrowers.userid, borrowers.dateofbirth, borrowers.categorycode,
41
        categories.description AS category_description, categories.category_type,
42
        branches.branchname";
43
    my $from = "FROM borrowers
44
        LEFT JOIN branches ON borrowers.branchcode = branches.branchcode
45
        LEFT JOIN categories ON borrowers.categorycode = categories.categorycode";
46
    my @where_args;
47
    my @where_strs;
48
    if(defined $firstletter and $firstletter ne '') {
49
        push @where_strs, "borrowers.surname LIKE ?";
50
        push @where_args, "$firstletter%";
51
    }
52
    if(defined $categorycode and $categorycode ne '') {
53
        push @where_strs, "borrowers.categorycode = ?";
54
        push @where_args, $categorycode;
55
    }
56
    if(defined $branchcode and $branchcode ne '') {
57
        push @where_strs, "borrowers.branchcode = ?";
58
        push @where_args, $branchcode;
59
    }
60
61
    my $searchfields = {
35
    my $searchfields = {
62
        standard => 'surname,firstname,othernames,cardnumber,userid',
36
        standard => 'surname,firstname,othernames,cardnumber,userid',
63
        surname => 'surname',
37
        surname => 'surname',
Lines 71-78 sub search { Link Here
71
        sort2 => 'sort2',
45
        sort2 => 'sort2',
72
    };
46
    };
73
47
74
    # * is replaced with % for sql
48
    my $searcher = Koha::SearchEngine::Elasticsearch::Search->new({ index => 'patrons' });
75
    $searchmember =~ s/\*/%/g;
49
    my $es_params = $searcher->get_elasticsearch_params();
50
    $searcher->store(
51
        Catmandu::Store::ElasticSearch->new(
52
            %$es_params,
53
        )
54
    ) unless $searcher->store;
55
56
    my @conditions;
57
    if ( defined $firstletter and $firstletter ne '' ) {
58
        push @conditions, "surname:$firstletter*";
59
    }
60
    if ( defined $categorycode and $categorycode ne '' ) {
61
        push @conditions, "categorycode:$categorycode";
62
    }
63
    if ( defined $branchcode and $branchcode ne '' ) {
64
        push @conditions, "branchcode:$branchcode";
65
    }
76
66
77
    # split into search terms
67
    # split into search terms
78
    my @terms;
68
    my @terms;
Lines 87-154 sub search { Link Here
87
    foreach my $term (@terms) {
77
    foreach my $term (@terms) {
88
        next unless $term;
78
        next unless $term;
89
79
90
        $term .= '%' # end with anything
80
        $term .= '*' # end with anything
91
            if $term !~ /%$/;
81
            if $term !~ /\*$/;
92
        $term = "%$term" # begin with anythin unless start_with
82
        $term = "\*$term" # begin with anythin unless start_with
93
            if $searchtype eq 'contain' && $term !~ /^%/;
83
            if $searchtype eq 'contain' && $term !~ /^\*/;
94
84
95
        my @where_strs_or;
85
        my @conditions_or;
96
        for my $searchfield ( split /,/, $searchfields->{$searchfieldstype} ) {
86
        for my $searchfield ( split /,/, $searchfields->{$searchfieldstype} ) {
97
            push @where_strs_or, "borrowers." . $dbh->quote_identifier($searchfield) . " LIKE ?";
87
            push @conditions_or, "$searchfield:$term";
98
            push @where_args, $term;
99
        }
88
        }
100
89
101
        if ( C4::Context->preference('ExtendedPatronAttributes') and $searchmember ) {
90
        # TODO
102
            my $matching_borrowernumbers = C4::Members::Attributes::SearchIdMatchingAttribute($searchmember);
91
        #if ( C4::Context->preference('ExtendedPatronAttributes') and $searchmember ) {
92
        #    my $matching_borrowernumbers = C4::Members::Attributes::SearchIdMatchingAttribute($searchmember);
103
93
104
            for my $borrowernumber ( @$matching_borrowernumbers ) {
94
        #    for my $borrowernumber ( @$matching_borrowernumbers ) {
105
                push @where_strs_or, "borrowers.borrowernumber = ?";
95
        #        push @where_strs_or, "borrowers.borrowernumber = ?";
106
                push @where_args, $borrowernumber;
96
        #        push @where_args, $borrowernumber;
107
            }
97
        #    }
108
        }
98
        #}
109
99
110
        push @where_strs, '('. join (' OR ', @where_strs_or) . ')'
100
        push @conditions, '('. join (' OR ', @conditions_or) . ')'
111
            if @where_strs_or;
101
            if @conditions_or;
112
    }
102
    }
113
103
114
    my $where;
104
    @conditions = ('*') unless @conditions;
115
    $where = " WHERE " . join (" AND ", @where_strs) if @where_strs;
105
    my $query = {
116
    my $orderby = dt_build_orderby($dt_params);
106
        query => {
117
107
            query_string => {
118
    my $limit;
108
                query => join ' AND ', @conditions,
119
    # If iDisplayLength == -1, we want to display all patrons
109
            }
120
    if ( !$dt_params->{iDisplayLength} || $dt_params->{iDisplayLength} > -1 ) {
110
        }
121
        # In order to avoid sql injection
111
    };
122
        $dt_params->{iDisplayStart} =~ s/\D//g if defined($dt_params->{iDisplayStart});
112
123
        $dt_params->{iDisplayLength} =~ s/\D//g if defined($dt_params->{iDisplayLength});
113
    # Do not display more than 1000 patrons
124
        $dt_params->{iDisplayStart} //= 0;
114
    $dt_params->{iDisplayLength} = 1000 if $dt_params->{iDisplayLength} == -1;
125
        $dt_params->{iDisplayLength} //= 20;
115
    my $paging = {
126
        $limit = "LIMIT $dt_params->{iDisplayStart},$dt_params->{iDisplayLength}";
116
        limit => $dt_params->{iDisplayLength} // 20,
127
    }
117
        start => $dt_params->{iDisplayStart} // 0,
118
    };
128
119
129
    my $query = join(
120
    my $results = $searcher->store->bag->search( %$query, %$paging );
130
        " ",
121
    my $patrons = $results->hits;
131
        ($select ? $select : ""),
122
    $iTotalRecords = $results->total;
132
        ($from ? $from : ""),
123
    $iTotalDisplayRecords = $iTotalRecords; # FIXME this is wrong
133
        ($where ? $where : ""),
134
        ($orderby ? $orderby : ""),
135
        ($limit ? $limit : "")
136
    );
137
    my $sth = $dbh->prepare($query);
138
    $sth->execute(@where_args);
139
    my $patrons = $sth->fetchall_arrayref({});
140
141
    # Get the iTotalDisplayRecords DataTable variable
142
    $query = "SELECT COUNT(borrowers.borrowernumber) " . $from . ($where ? $where : "");
143
    $sth = $dbh->prepare($query);
144
    $sth->execute(@where_args);
145
    ($iTotalDisplayRecords) = $sth->fetchrow_array;
146
147
    # Get the iTotalRecords DataTable variable
148
    $query = "SELECT COUNT(borrowers.borrowernumber) FROM borrowers";
149
    $sth = $dbh->prepare($query);
150
    $sth->execute;
151
    ($iTotalRecords) = $sth->fetchrow_array;
152
124
153
    # Get some information on patrons
125
    # Get some information on patrons
154
    foreach my $patron (@$patrons) {
126
    foreach my $patron (@$patrons) {
155
- 

Return to bug 17500