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

(-)a/C4/Utils/DataTables/Members.pm (-99 / +54 lines)
Lines 20-32 sub search { Link Here
20
        $searchmember = $dt_params->{sSearch} // '';
20
        $searchmember = $dt_params->{sSearch} // '';
21
    }
21
    }
22
22
23
    my $search_params = {};
24
    my $prefetch = [];
25
23
    # If branches are independent and user is not superlibrarian
26
    # If branches are independent and user is not superlibrarian
24
    # The search has to be only on the user branch
27
    # The search has to be only on the user branch
25
    my $userenv = C4::Context->userenv;
28
    my $userenv = C4::Context->userenv;
26
    my $logged_in_user = Koha::Patrons->find( $userenv->{number} );
29
    my $logged_in_user = Koha::Patrons->find( $userenv->{number} );
27
    my @restricted_branchcodes = $logged_in_user->libraries_where_can_see_patrons;
30
    my @restricted_branchcodes = $logged_in_user->libraries_where_can_see_patrons;
31
    if ( @restricted_branchcodes ) {
32
        $search_params->{'me.branchcode'} = { -in => \@restricted_branchcodes };
33
    }
34
35
28
36
29
    my ($sth, $query, $iTotalQuery, $iTotalRecords, $iTotalDisplayRecords);
30
    my $dbh = C4::Context->dbh;
37
    my $dbh = C4::Context->dbh;
31
38
32
    # Get the module_bit from a given permission code
39
    # Get the module_bit from a given permission code
Lines 34-60 sub search { Link Here
34
        ($has_permission->{module_bit}) = $dbh->selectrow_array(q|
41
        ($has_permission->{module_bit}) = $dbh->selectrow_array(q|
35
            SELECT bit FROM userflags WHERE flag=?
42
            SELECT bit FROM userflags WHERE flag=?
36
        |, undef, $has_permission->{permission});
43
        |, undef, $has_permission->{permission});
44
        my $module_bit = $has_permission->{module_bit};
45
        $search_params->{'-and'} = [ '-or' => [
46
            \"me.flags & (1 << $module_bit)",
47
            {'me.flags' => 1},
48
            { -and => [
49
                {'user_permissions.module_bit' => $module_bit},
50
                {'user_permissions.code' => $has_permission->{subpermission}}
51
            ]}
52
        ]];
53
        push @{$prefetch}, 'user_permissions';
37
    }
54
    }
38
55
39
    my (@where, @conditions);
40
    # Get the iTotalRecords DataTable variable
56
    # Get the iTotalRecords DataTable variable
41
    $iTotalQuery = "SELECT COUNT(borrowers.borrowernumber) FROM borrowers";
57
    my $iTotalRecords = Koha::Patrons->search($search_params,{ prefetch => $prefetch })->count();
42
    if ( $has_permission ) {
43
        $iTotalQuery .= ' LEFT JOIN user_permissions ON borrowers.borrowernumber=user_permissions.borrowernumber';
44
        $iTotalQuery .= ' AND module_bit=? AND code=?';
45
        push @conditions, $has_permission->{module_bit}, $has_permission->{subpermission};
46
    }
47
48
    if ( @restricted_branchcodes ) {
49
        push @where, "borrowers.branchcode IN (" . join( ',', ('?') x @restricted_branchcodes ) . ")";
50
        push @conditions, @restricted_branchcodes;
51
    }
52
    if ( $has_permission ) {
53
        push @where, '( borrowers.flags = 1 OR borrowers.flags & (1 << ?) OR module_bit=? AND code=? )';
54
        push @conditions, ($has_permission->{module_bit}) x 2, $has_permission->{subpermission};
55
    }
56
    $iTotalQuery .= ' WHERE ' . join ' AND ', @where if @where;
57
    ($iTotalRecords) = $dbh->selectrow_array( $iTotalQuery, undef, @conditions );
58
58
59
    # Do that after iTotalQuery!
59
    # Do that after iTotalQuery!
60
    if ( defined $branchcode and $branchcode ) {
60
    if ( defined $branchcode and $branchcode ) {
Lines 63-68 sub search { Link Here
63
                ? ($branchcode)
63
                ? ($branchcode)
64
                : (undef) # Do not return any results
64
                : (undef) # Do not return any results
65
            : ($branchcode);
65
            : ($branchcode);
66
        $search_params->{'me.branchcode'} = { -in => \@restricted_branchcodes };
66
    }
67
    }
67
68
68
    if ( $searchfieldstype eq 'dateofbirth' ) {
69
    if ( $searchfieldstype eq 'dateofbirth' ) {
Lines 77-115 sub search { Link Here
77
        }
78
        }
78
    }
79
    }
79
80
80
    my $select = "SELECT
81
    push @{$prefetch}, ('branchcode', 'categorycode');
81
        borrowers.borrowernumber, borrowers.surname, borrowers.firstname,
82
        borrowers.othernames,
83
        borrowers.flags,
84
        borrowers.streetnumber, borrowers.streettype, borrowers.address,
85
        borrowers.address2, borrowers.city, borrowers.state, borrowers.zipcode,
86
        borrowers.country, cardnumber, borrowers.dateexpiry,
87
        borrowers.borrowernotes, borrowers.branchcode, borrowers.email,
88
        borrowers.userid, borrowers.dateofbirth, borrowers.categorycode,
89
        categories.description AS category_description, categories.category_type,
90
        branches.branchname, borrowers.phone";
91
    my $from = "FROM borrowers
92
                LEFT JOIN branches ON borrowers.branchcode = branches.branchcode
93
                LEFT JOIN categories ON borrowers.categorycode = categories.categorycode";
94
    my @where_args;
95
    if ( $has_permission ) {
96
        $from .= '
97
                LEFT JOIN user_permissions ON borrowers.borrowernumber=user_permissions.borrowernumber
98
                AND module_bit=? AND code=?';
99
        push @where_args, $has_permission->{module_bit}, $has_permission->{subpermission};
100
    }
101
    my @where_strs;
102
    if(defined $firstletter and $firstletter ne '') {
82
    if(defined $firstletter and $firstletter ne '') {
103
        push @where_strs, "borrowers.surname LIKE ?";
83
        $search_params->{surname} = { -like => $firstletter . '%' };
104
        push @where_args, "$firstletter%";
105
    }
84
    }
106
    if(defined $categorycode and $categorycode ne '') {
85
    if(defined $categorycode and $categorycode ne '') {
107
        push @where_strs, "borrowers.categorycode = ?";
86
        $search_params->{'`me`.`categorycode`'} = $categorycode;
108
        push @where_args, $categorycode;
109
    }
110
    if(@restricted_branchcodes ) {
111
        push @where_strs, "borrowers.branchcode IN (" . join( ',', ('?') x @restricted_branchcodes ) . ")";
112
        push @where_args, @restricted_branchcodes;
113
    }
87
    }
114
88
115
    my $searchfields = {
89
    my $searchfields = {
Lines 147-186 sub search { Link Here
147
              if $searchtype eq 'contain' && $term !~ /^%/;
121
              if $searchtype eq 'contain' && $term !~ /^%/;
148
        }
122
        }
149
123
150
        my @where_strs_or;
151
        if ( defined $searchfields->{$searchfieldstype} ) {
124
        if ( defined $searchfields->{$searchfieldstype} ) {
152
            for my $searchfield ( split /,/, $searchfields->{$searchfieldstype} ) {
125
            for my $searchfield ( split /,/, $searchfields->{$searchfieldstype} ) {
153
                push @where_strs_or, "borrowers." . $dbh->quote_identifier($searchfield) . " LIKE ?";
126
                push @{$search_params->{'-or'}}, {$searchfield => { -like => $term }};
154
                push @where_args, $term;
155
            }
127
            }
156
        } else {
128
        } else {
157
            push @where_strs_or, "borrowers." . $dbh->quote_identifier($searchfieldstype) . " LIKE ?";
129
            push @{$search_params->{'-or'}}, {'me.' . $dbh->quote_identifier($searchfieldstype) => { -like => $term }};
158
            push @where_args, $term;
159
        }
130
        }
160
131
161
132
162
        if ( $searchfieldstype eq 'standard' and C4::Context->preference('ExtendedPatronAttributes') and $searchmember ) {
133
        if ( $searchfieldstype eq 'standard' and C4::Context->preference('ExtendedPatronAttributes') and $searchmember ) {
163
            my @matching_borrowernumbers = Koha::Patrons->filter_by_attribute_value($searchmember)->get_column('borrowernumber');
134
            push @{$search_params->{'-or'}}, { '-and' => [
164
135
                {'borrower_attribute_types.staff_searchable' => 1},
165
            for my $borrowernumber ( @matching_borrowernumbers ) {
136
                {'borrower_attributes.attribute' => { -like => '%'.$term.'%' }}
166
                push @where_strs_or, "borrowers.borrowernumber = ?";
137
            ]};
167
                push @where_args, $borrowernumber;
138
            push @{$prefetch}, ({ borrower_attributes => 'borrower_attribute_types'} );
168
            }
169
        }
139
        }
170
140
171
        push @where_strs, '('. join (' OR ', @where_strs_or) . ')'
172
            if @where_strs_or;
173
    }
141
    }
174
142
175
    if ( $has_permission ) {
176
        push @where_strs, '( borrowers.flags = 1 OR borrowers.flags & (1 << ?) OR module_bit=? AND code=? )';
177
        push @where_args, ($has_permission->{module_bit}) x 2, $has_permission->{subpermission};
178
    }
179
180
    my $where = @where_strs ? " WHERE " . join (" AND ", @where_strs) : undef;
181
    my $orderby = dt_build_orderby($dt_params);
143
    my $orderby = dt_build_orderby($dt_params);
182
144
183
    my $limit;
145
    my $options;
184
    # If iDisplayLength == -1, we want to display all patrons
146
    # If iDisplayLength == -1, we want to display all patrons
185
    if ( !$dt_params->{iDisplayLength} || $dt_params->{iDisplayLength} > -1 ) {
147
    if ( !$dt_params->{iDisplayLength} || $dt_params->{iDisplayLength} > -1 ) {
186
        # In order to avoid sql injection
148
        # In order to avoid sql injection
Lines 188-236 sub search { Link Here
188
        $dt_params->{iDisplayLength} =~ s/\D//g if defined($dt_params->{iDisplayLength});
150
        $dt_params->{iDisplayLength} =~ s/\D//g if defined($dt_params->{iDisplayLength});
189
        $dt_params->{iDisplayStart} //= 0;
151
        $dt_params->{iDisplayStart} //= 0;
190
        $dt_params->{iDisplayLength} //= 20;
152
        $dt_params->{iDisplayLength} //= 20;
191
        $limit = "LIMIT $dt_params->{iDisplayStart},$dt_params->{iDisplayLength}";
153
        $options->{offset} = $dt_params->{iDisplayStart};
154
        $options->{rows} = $dt_params->{iDisplayLength} ;
192
    }
155
    }
193
156
194
    $query = join(
195
        " ",
196
        ($select ? $select : ""),
197
        ($from ? $from : ""),
198
        ($where ? $where : ""),
199
        ($orderby ? $orderby : ""),
200
        ($limit ? $limit : "")
201
    );
202
    $sth = $dbh->prepare($query);
203
    $sth->execute(@where_args);
204
    my $patrons = $sth->fetchall_arrayref({});
205
157
206
    # Get the iTotalDisplayRecords DataTable variable
158
    # Get the iTotalDisplayRecords DataTable variable
207
    $query = "SELECT COUNT(borrowers.borrowernumber) " . $from . ($where ? $where : "");
159
    my $iTotalDisplayRecords = Koha::Patrons->search($search_params,{ prefetch => $prefetch })->count();
208
    $sth = $dbh->prepare($query);
160
209
    $sth->execute(@where_args);
210
    ($iTotalDisplayRecords) = $sth->fetchrow_array;
211
161
212
    # Get some information on patrons
162
    # Get some information on patrons
213
    foreach my $patron (@$patrons) {
163
    push @{$prefetch}, ('issues', 'accountlines');
214
        my $patron_object = Koha::Patrons->find( $patron->{borrowernumber} );
164
    $options->{prefetch} = $prefetch;
215
        $patron->{overdues} = $patron_object->get_overdues->count;
165
    my $patron_objects = Koha::Patrons->search($search_params,$options);
216
        $patron->{issues} = $patron_object->checkouts->count;
166
    my @patrons;
217
        $patron->{age} = $patron_object->get_age;
167
    while ( my $patron = $patron_objects->next ) {
218
        my $balance = $patron_object->account->balance;
168
        my $simple_patron = $patron->unblessed;
219
        # FIXME Should be formatted from the template
169
        $simple_patron->{overdues} = $patron->get_overdues->count;
220
        $patron->{fines} = sprintf("%.2f", $balance);
170
        $simple_patron->{issues} = $patron->checkouts->count;
221
171
        $simple_patron->{age} = $patron->get_age;
222
        if( $patron->{dateexpiry} ) {
172
        $simple_patron->{fines} = sprintf("%.2f",$patron->account->lines->total_outstanding);
173
        $simple_patron->{'branchname'} = $patron->library->branchname;
174
        $simple_patron->{'category_description'} = $patron->category->description;
175
        $simple_patron->{'category_type'} = $patron->category->category_type;
176
177
        if( $simple_patron->{dateexpiry} ) {
223
            # FIXME We should not format the date here, do it in template-side instead
178
            # FIXME We should not format the date here, do it in template-side instead
224
            $patron->{dateexpiry} = output_pref( { dt => scalar dt_from_string( $patron->{dateexpiry}, 'iso'), dateonly => 1} );
179
            $simple_patron->{dateexpiry} = output_pref( { dt => scalar dt_from_string( $simple_patron->{dateexpiry}, 'iso'), dateonly => 1} );
225
        } else {
180
        } else {
226
            $patron->{dateexpiry} = '';
181
            $simple_patron->{dateexpiry} = '';
227
        }
182
        }
183
        push @patrons, $simple_patron;
228
    }
184
    }
229
185
230
    return {
186
    return {
231
        iTotalRecords => $iTotalRecords,
187
        iTotalRecords => $iTotalRecords,
232
        iTotalDisplayRecords => $iTotalDisplayRecords,
188
        iTotalDisplayRecords => $iTotalDisplayRecords,
233
        patrons => $patrons
189
        patrons => \@patrons
234
    }
190
    }
235
}
191
}
236
192
237
- 

Return to bug 29125