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

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

Return to bug 17500