Lines 1-6
Link Here
|
1 |
#!/usr/bin/perl |
1 |
#!/usr/bin/perl |
2 |
|
2 |
|
3 |
|
|
|
4 |
# Copyright 2000-2002 Katipo Communications |
3 |
# Copyright 2000-2002 Katipo Communications |
5 |
# Parts copyright 2010 BibLibre |
4 |
# Parts copyright 2010 BibLibre |
6 |
# |
5 |
# |
Lines 22-53
Link Here
|
22 |
use Modern::Perl; |
21 |
use Modern::Perl; |
23 |
use C4::Context; |
22 |
use C4::Context; |
24 |
use C4::Output qw( output_html_with_http_headers ); |
23 |
use C4::Output qw( output_html_with_http_headers ); |
25 |
use CGI qw(-oldstyle_urls -utf8); |
24 |
use CGI qw(-oldstyle_urls -utf8); |
26 |
use C4::Auth qw( get_template_and_user ); |
25 |
use C4::Auth qw( get_template_and_user ); |
27 |
use Text::CSV_XS; |
26 |
use Text::CSV_XS; |
28 |
use Koha::DateUtils qw( dt_from_string ); |
27 |
use Koha::DateUtils qw( dt_from_string ); |
29 |
use Koha::Patron::Attribute::Types; |
28 |
use Koha::Patron::Attribute::Types; |
30 |
use DateTime; |
29 |
use DateTime; |
31 |
use DateTime::Format::MySQL; |
30 |
use DateTime::Format::MySQL; |
32 |
|
31 |
|
33 |
my $input = CGI->new; |
32 |
my $input = CGI->new; |
34 |
my $showall = $input->param('showall'); |
33 |
my $showall = $input->param('showall'); |
35 |
my $bornamefilter = $input->param('borname') || ''; |
34 |
my $bornamefilter = $input->param('borname') || ''; |
36 |
my $borcatfilter = $input->param('borcat') || ''; |
35 |
my $borcatfilter = $input->param('borcat') || ''; |
37 |
my $itemtypefilter = $input->param('itemtype') || ''; |
36 |
my $itemtypefilter = $input->param('itemtype') || ''; |
38 |
my $borflagsfilter = $input->param('borflag') || ''; |
37 |
my $borflagsfilter = $input->param('borflag') || ''; |
39 |
my $branchfilter = $input->param('branch') || ''; |
38 |
my $branchfilter = $input->param('branch') || ''; |
40 |
my $homebranchfilter = $input->param('homebranch') || ''; |
39 |
my $homebranchfilter = $input->param('homebranch') || ''; |
41 |
my $holdingbranchfilter = $input->param('holdingbranch') || ''; |
40 |
my $holdingbranchfilter = $input->param('holdingbranch') || ''; |
42 |
my $dateduefrom = $input->param('dateduefrom'); |
41 |
my $dateduefrom = $input->param('dateduefrom'); |
43 |
my $datedueto = $input->param('datedueto'); |
42 |
my $datedueto = $input->param('datedueto'); |
44 |
my $op = $input->param('op') || ''; |
43 |
my $op = $input->param('op') || ''; |
45 |
|
44 |
|
46 |
if ( $dateduefrom ) { |
45 |
if ($dateduefrom) { |
47 |
$dateduefrom = dt_from_string( $dateduefrom ); |
46 |
$dateduefrom = dt_from_string($dateduefrom); |
48 |
} |
47 |
} |
49 |
if ( $datedueto ) { |
48 |
if ($datedueto) { |
50 |
$datedueto = dt_from_string( $datedueto )->set_hour(23)->set_minute(59); |
49 |
$datedueto = dt_from_string($datedueto)->set_hour(23)->set_minute(59); |
51 |
} |
50 |
} |
52 |
|
51 |
|
53 |
my $filters = { |
52 |
my $filters = { |
Lines 64-90
my $filters = {
Link Here
|
64 |
showall => $showall, |
63 |
showall => $showall, |
65 |
}; |
64 |
}; |
66 |
|
65 |
|
67 |
my $isfiltered = $op =~ /apply/i && $op =~ /filter/i; |
66 |
my $isfiltered = $op =~ /apply/i && $op =~ /filter/i; |
68 |
my $noreport = C4::Context->preference('FilterBeforeOverdueReport') && ! $isfiltered && $op ne "csv"; |
67 |
my $noreport = C4::Context->preference('FilterBeforeOverdueReport') && !$isfiltered && $op ne "csv"; |
69 |
|
68 |
|
70 |
my ( $template, $loggedinuser, $cookie ) = get_template_and_user( |
69 |
my ( $template, $loggedinuser, $cookie ) = get_template_and_user( |
71 |
{ |
70 |
{ |
72 |
template_name => "circ/overdue.tt", |
71 |
template_name => "circ/overdue.tt", |
73 |
query => $input, |
72 |
query => $input, |
74 |
type => "intranet", |
73 |
type => "intranet", |
75 |
flagsrequired => { circulate => "overdues_report" }, |
74 |
flagsrequired => { circulate => "overdues_report" }, |
76 |
} |
75 |
} |
77 |
); |
76 |
); |
78 |
|
77 |
|
79 |
our $logged_in_user = Koha::Patrons->find( $loggedinuser ); |
78 |
our $logged_in_user = Koha::Patrons->find($loggedinuser); |
80 |
|
79 |
|
81 |
my $dbh = C4::Context->dbh; |
80 |
my $dbh = C4::Context->dbh; |
82 |
|
81 |
|
83 |
my $req; |
82 |
my $req; |
84 |
$req = $dbh->prepare( "select categorycode, description from categories order by description"); |
83 |
$req = $dbh->prepare("select categorycode, description from categories order by description"); |
85 |
$req->execute; |
84 |
$req->execute; |
86 |
my @borcatloop; |
85 |
my @borcatloop; |
87 |
while (my ($catcode, $description) =$req->fetchrow) { |
86 |
while ( my ( $catcode, $description ) = $req->fetchrow ) { |
88 |
push @borcatloop, { |
87 |
push @borcatloop, { |
89 |
value => $catcode, |
88 |
value => $catcode, |
90 |
selected => $catcode eq $borcatfilter ? 1 : 0, |
89 |
selected => $catcode eq $borcatfilter ? 1 : 0, |
Lines 92-101
while (my ($catcode, $description) =$req->fetchrow) {
Link Here
|
92 |
}; |
91 |
}; |
93 |
} |
92 |
} |
94 |
|
93 |
|
95 |
$req = $dbh->prepare( "select itemtype, description from itemtypes order by description"); |
94 |
$req = $dbh->prepare("select itemtype, description from itemtypes order by description"); |
96 |
$req->execute; |
95 |
$req->execute; |
97 |
my @itemtypeloop; |
96 |
my @itemtypeloop; |
98 |
while (my ($itemtype, $description) =$req->fetchrow) { |
97 |
while ( my ( $itemtype, $description ) = $req->fetchrow ) { |
99 |
push @itemtypeloop, { |
98 |
push @itemtypeloop, { |
100 |
value => $itemtype, |
99 |
value => $itemtype, |
101 |
selected => $itemtype eq $itemtypefilter ? 1 : 0, |
100 |
selected => $itemtype eq $itemtypefilter ? 1 : 0, |
Lines 108-123
while (my ($itemtype, $description) =$req->fetchrow) {
Link Here
|
108 |
# %cgi_attrcode_to_attrvalues contains the patron attribute filter values, as returned by the CGI |
107 |
# %cgi_attrcode_to_attrvalues contains the patron attribute filter values, as returned by the CGI |
109 |
# %borrowernumber_to_attributes is populated by those borrowernumbers matching the patron attribute filters |
108 |
# %borrowernumber_to_attributes is populated by those borrowernumbers matching the patron attribute filters |
110 |
|
109 |
|
111 |
my %cgi_attrcode_to_attrvalues; # ( patron_attribute_code => [ zero or more attribute filter values from the CGI ] ) |
110 |
my %cgi_attrcode_to_attrvalues; # ( patron_attribute_code => [ zero or more attribute filter values from the CGI ] ) |
112 |
for my $attrcode (grep { /^patron_attr_filter_/ } $input->multi_param) { |
111 |
for my $attrcode ( grep { /^patron_attr_filter_/ } $input->multi_param ) { |
113 |
if (my @attrvalues = grep { length($_) > 0 } $input->multi_param($attrcode)) { |
112 |
if ( my @attrvalues = grep { length($_) > 0 } $input->multi_param($attrcode) ) { |
114 |
$attrcode =~ s/^patron_attr_filter_//; |
113 |
$attrcode =~ s/^patron_attr_filter_//; |
115 |
$cgi_attrcode_to_attrvalues{$attrcode} = \@attrvalues; |
114 |
$cgi_attrcode_to_attrvalues{$attrcode} = \@attrvalues; |
116 |
} |
115 |
} |
117 |
} |
116 |
} |
118 |
my $have_pattr_filter_data = keys(%cgi_attrcode_to_attrvalues) > 0; |
117 |
my $have_pattr_filter_data = keys(%cgi_attrcode_to_attrvalues) > 0; |
119 |
|
118 |
|
120 |
my @patron_attr_filter_loop; # array of [ domid cgivalue ismany isclone ordinal code description repeatable is_date authorised_value_category ] |
119 |
my @patron_attr_filter_loop |
|
|
120 |
; # array of [ domid cgivalue ismany isclone ordinal code description repeatable is_date authorised_value_category ] |
121 |
|
121 |
|
122 |
my $patron_attrs = Koha::Patron::Attribute::Types->search_with_library_limits( |
122 |
my $patron_attrs = Koha::Patron::Attribute::Types->search_with_library_limits( |
123 |
{ |
123 |
{ |
Lines 128-158
my $patron_attrs = Koha::Patron::Attribute::Types->search_with_library_limits(
Link Here
|
128 |
); |
128 |
); |
129 |
|
129 |
|
130 |
my $ordinal = 0; |
130 |
my $ordinal = 0; |
131 |
while (my $attr = $patron_attrs->next ) { |
131 |
while ( my $attr = $patron_attrs->next ) { |
132 |
my $row = { |
132 |
my $row = { |
133 |
code => $attr->code, |
133 |
code => $attr->code, |
134 |
description => $attr->description, |
134 |
description => $attr->description, |
135 |
repeatable => $attr->repeatable, |
135 |
repeatable => $attr->repeatable, |
136 |
is_date => $attr->is_date, |
136 |
is_date => $attr->is_date, |
137 |
authorised_value_category => $attr->authorised_value_category, |
137 |
authorised_value_category => $attr->authorised_value_category, |
138 |
}; |
138 |
}; |
139 |
$row->{ordinal} = $ordinal; |
139 |
$row->{ordinal} = $ordinal; |
140 |
my $code = $row->{code}; |
140 |
my $code = $row->{code}; |
141 |
my $cgivalues = $cgi_attrcode_to_attrvalues{$code} || [ '' ]; |
141 |
my $cgivalues = $cgi_attrcode_to_attrvalues{$code} || ['']; |
142 |
my $isclone = 0; |
142 |
my $isclone = 0; |
143 |
$row->{ismany} = @$cgivalues > 1; |
143 |
$row->{ismany} = @$cgivalues > 1; |
144 |
my $serial = 0; |
144 |
my $serial = 0; |
145 |
for (@$cgivalues) { |
145 |
for (@$cgivalues) { |
146 |
$row->{domid} = $ordinal * 1000 + $serial; |
146 |
$row->{domid} = $ordinal * 1000 + $serial; |
147 |
$row->{cgivalue} = $_; |
147 |
$row->{cgivalue} = $_; |
148 |
$row->{isclone} = $isclone; |
148 |
$row->{isclone} = $isclone; |
149 |
push @patron_attr_filter_loop, { %$row }; # careful: must store a *deep copy* of the modified row |
149 |
push @patron_attr_filter_loop, {%$row}; # careful: must store a *deep copy* of the modified row |
150 |
} continue { $isclone = 1, ++$serial } |
150 |
} continue { |
151 |
} continue { ++$ordinal } |
151 |
$isclone = 1, ++$serial; |
|
|
152 |
} |
153 |
} continue { |
154 |
++$ordinal; |
155 |
} |
152 |
|
156 |
|
153 |
my %borrowernumber_to_attributes; # hash of { borrowernumber => { attrcode => [ [val,display], [val,display], ... ] } } |
157 |
my %borrowernumber_to_attributes; # hash of { borrowernumber => { attrcode => [ [val,display], [val,display], ... ] } } |
154 |
# i.e. val differs from display when attr is an authorised value |
158 |
# i.e. val differs from display when attr is an authorised value |
155 |
if (@patron_attr_filter_loop) { |
159 |
if (@patron_attr_filter_loop) { |
|
|
160 |
|
156 |
# MAYBE FIXME: currently, *all* borrower_attributes are loaded into %borrowernumber_to_attributes |
161 |
# MAYBE FIXME: currently, *all* borrower_attributes are loaded into %borrowernumber_to_attributes |
157 |
# then filtered and honed down to match the patron attribute filters. If this is |
162 |
# then filtered and honed down to match the patron attribute filters. If this is |
158 |
# too resource intensive, MySQL can be used to do the filtering, i.e. rewire the |
163 |
# too resource intensive, MySQL can be used to do the filtering, i.e. rewire the |
Lines 166-187
if (@patron_attr_filter_loop) {
Link Here
|
166 |
}; |
171 |
}; |
167 |
my $sth = $dbh->prepare($sql); |
172 |
my $sth = $dbh->prepare($sql); |
168 |
$sth->execute(); |
173 |
$sth->execute(); |
169 |
while (my $row = $sth->fetchrow_hashref) { |
174 |
while ( my $row = $sth->fetchrow_hashref ) { |
170 |
my $pattrs = $borrowernumber_to_attributes{$row->{bn}} ||= { }; |
175 |
my $pattrs = $borrowernumber_to_attributes{ $row->{bn} } ||= {}; |
171 |
push @{ $pattrs->{$row->{attrcode}} }, [ |
176 |
push @{ $pattrs->{ $row->{attrcode} } }, [ |
172 |
$row->{attrval}, |
177 |
$row->{attrval}, |
173 |
defined $row->{avdescription} ? $row->{avdescription} : $row->{attrval}, |
178 |
defined $row->{avdescription} ? $row->{avdescription} : $row->{attrval}, |
174 |
]; |
179 |
]; |
175 |
} |
180 |
} |
176 |
|
181 |
|
177 |
for my $bn (keys %borrowernumber_to_attributes) { |
182 |
for my $bn ( keys %borrowernumber_to_attributes ) { |
178 |
my $pattrs = $borrowernumber_to_attributes{$bn}; |
183 |
my $pattrs = $borrowernumber_to_attributes{$bn}; |
179 |
my $keep = 1; |
184 |
my $keep = 1; |
180 |
for my $code (keys %cgi_attrcode_to_attrvalues) { |
185 |
for my $code ( keys %cgi_attrcode_to_attrvalues ) { |
|
|
186 |
|
181 |
# discard patrons that do not match (case insensitive) at least one of each attribute filter value |
187 |
# discard patrons that do not match (case insensitive) at least one of each attribute filter value |
182 |
my $discard = 1; |
188 |
my $discard = 1; |
183 |
for my $attrval (map { lc $_ } @{ $cgi_attrcode_to_attrvalues{$code} }) { |
189 |
for my $attrval ( map { lc $_ } @{ $cgi_attrcode_to_attrvalues{$code} } ) { |
184 |
if (grep { $attrval eq lc($_->[0]) } @{ $pattrs->{$code} }) { |
190 |
if ( grep { $attrval eq lc( $_->[0] ) } @{ $pattrs->{$code} } ) { |
185 |
$discard = 0; |
191 |
$discard = 0; |
186 |
last; |
192 |
last; |
187 |
} |
193 |
} |
Lines 195-214
if (@patron_attr_filter_loop) {
Link Here
|
195 |
} |
201 |
} |
196 |
} |
202 |
} |
197 |
|
203 |
|
198 |
|
|
|
199 |
$template->param( |
204 |
$template->param( |
200 |
patron_attr_header_loop => [ map { { header => $_->{description} } } grep { ! $_->{isclone} } @patron_attr_filter_loop ], |
205 |
patron_attr_header_loop => |
201 |
filters => $filters, |
206 |
[ map { { header => $_->{description} } } grep { !$_->{isclone} } @patron_attr_filter_loop ], |
202 |
borcatloop=> \@borcatloop, |
207 |
filters => $filters, |
203 |
itemtypeloop => \@itemtypeloop, |
208 |
borcatloop => \@borcatloop, |
|
|
209 |
itemtypeloop => \@itemtypeloop, |
204 |
patron_attr_filter_loop => \@patron_attr_filter_loop, |
210 |
patron_attr_filter_loop => \@patron_attr_filter_loop, |
205 |
showall => $showall, |
211 |
showall => $showall, |
206 |
); |
212 |
); |
207 |
|
213 |
|
208 |
if ($noreport) { |
214 |
if ($noreport) { |
|
|
215 |
|
209 |
# la de dah ... page comes up presto-quicko |
216 |
# la de dah ... page comes up presto-quicko |
210 |
$template->param( noreport => $noreport ); |
217 |
$template->param( noreport => $noreport ); |
211 |
} else { |
218 |
} else { |
|
|
219 |
|
212 |
# FIXME : the left joins + where clauses make the following SQL query really slow with large datasets :( |
220 |
# FIXME : the left joins + where clauses make the following SQL query really slow with large datasets :( |
213 |
# |
221 |
# |
214 |
# FIX 1: use the table with the least rows as first in the join, second least second, etc |
222 |
# FIX 1: use the table with the least rows as first in the join, second least second, etc |
Lines 216-230
if ($noreport) {
Link Here
|
216 |
# |
224 |
# |
217 |
# FIX 2: ensure there are indexes for columns participating in the WHERE clauses, where feasible/reasonable |
225 |
# FIX 2: ensure there are indexes for columns participating in the WHERE clauses, where feasible/reasonable |
218 |
|
226 |
|
219 |
|
|
|
220 |
my $today_dt = dt_from_string(); |
227 |
my $today_dt = dt_from_string(); |
221 |
$today_dt->truncate(to => 'minute'); |
228 |
$today_dt->truncate( to => 'minute' ); |
222 |
my $todaysdate = $today_dt->strftime('%Y-%m-%d %H:%M'); |
229 |
my $todaysdate = $today_dt->strftime('%Y-%m-%d %H:%M'); |
223 |
|
230 |
|
224 |
$bornamefilter =~s/\*/\%/g; |
231 |
$bornamefilter =~ s/\*/\%/g; |
225 |
$bornamefilter =~s/\?/\_/g; |
232 |
$bornamefilter =~ s/\?/\_/g; |
226 |
|
233 |
|
227 |
my $strsth="SELECT date_due, |
234 |
my $strsth = "SELECT date_due, |
228 |
borrowers.title as borrowertitle, |
235 |
borrowers.title as borrowertitle, |
229 |
borrowers.surname, |
236 |
borrowers.surname, |
230 |
borrowers.firstname, |
237 |
borrowers.firstname, |
Lines 266-373
if ($noreport) {
Link Here
|
266 |
LEFT JOIN biblioitems ON (biblioitems.biblioitemnumber=items.biblioitemnumber) |
273 |
LEFT JOIN biblioitems ON (biblioitems.biblioitemnumber=items.biblioitemnumber) |
267 |
LEFT JOIN biblio ON (biblio.biblionumber=items.biblionumber ) |
274 |
LEFT JOIN biblio ON (biblio.biblionumber=items.biblionumber ) |
268 |
LEFT JOIN return_claims ON (return_claims.borrowernumber=borrowers.borrowernumber AND return_claims.itemnumber=items.itemnumber) |
275 |
LEFT JOIN return_claims ON (return_claims.borrowernumber=borrowers.borrowernumber AND return_claims.itemnumber=items.itemnumber) |
269 |
WHERE 1=1 "; # placeholder, since it is possible that none of the additional |
276 |
WHERE 1=1 "; # placeholder, since it is possible that none of the additional |
270 |
# conditions will be selected by user |
277 |
# conditions will be selected by user |
271 |
$strsth.=" AND date_due < '" . $todaysdate . "' " unless ($showall or $datedueto); |
278 |
$strsth .= " AND date_due < '" . $todaysdate . "' " unless ( $showall or $datedueto ); |
272 |
$strsth.=" AND (borrowers.firstname like '".$bornamefilter."%' or borrowers.surname like '".$bornamefilter."%' or borrowers.cardnumber like '".$bornamefilter."%')" if($bornamefilter) ; |
279 |
$strsth .= |
273 |
$strsth.=" AND borrowers.categorycode = '" . $borcatfilter . "' " if $borcatfilter; |
280 |
" AND (borrowers.firstname like '" |
274 |
if( $itemtypefilter ){ |
281 |
. $bornamefilter |
275 |
if( C4::Context->preference('item-level_itypes') ){ |
282 |
. "%' or borrowers.surname like '" |
276 |
$strsth.=" AND items.itype = '" . $itemtypefilter . "' "; |
283 |
. $bornamefilter |
|
|
284 |
. "%' or borrowers.cardnumber like '" |
285 |
. $bornamefilter . "%')" |
286 |
if ($bornamefilter); |
287 |
$strsth .= " AND borrowers.categorycode = '" . $borcatfilter . "' " if $borcatfilter; |
288 |
|
289 |
if ($itemtypefilter) { |
290 |
if ( C4::Context->preference('item-level_itypes') ) { |
291 |
$strsth .= " AND items.itype = '" . $itemtypefilter . "' "; |
277 |
} else { |
292 |
} else { |
278 |
$strsth.=" AND biblioitems.itemtype = '" . $itemtypefilter . "' "; |
293 |
$strsth .= " AND biblioitems.itemtype = '" . $itemtypefilter . "' "; |
279 |
} |
294 |
} |
280 |
} |
295 |
} |
281 |
if ( $borflagsfilter eq 'gonenoaddress' ) { |
296 |
if ( $borflagsfilter eq 'gonenoaddress' ) { |
282 |
$strsth .= " AND borrowers.gonenoaddress <> 0"; |
297 |
$strsth .= " AND borrowers.gonenoaddress <> 0"; |
283 |
} |
298 |
} elsif ( $borflagsfilter eq 'debarred' ) { |
284 |
elsif ( $borflagsfilter eq 'debarred' ) { |
299 |
$strsth .= " AND borrowers.debarred >= CURDATE()"; |
285 |
$strsth .= " AND borrowers.debarred >= CURDATE()" ; |
300 |
} elsif ( $borflagsfilter eq 'lost' ) { |
286 |
} |
|
|
287 |
elsif ( $borflagsfilter eq 'lost') { |
288 |
$strsth .= " AND borrowers.lost <> 0"; |
301 |
$strsth .= " AND borrowers.lost <> 0"; |
289 |
} |
302 |
} |
290 |
$strsth.=" AND borrowers.branchcode = '" . $branchfilter . "' " if $branchfilter; |
303 |
$strsth .= " AND borrowers.branchcode = '" . $branchfilter . "' " if $branchfilter; |
291 |
$strsth.=" AND items.homebranch = '" . $homebranchfilter . "' " if $homebranchfilter; |
304 |
$strsth .= " AND items.homebranch = '" . $homebranchfilter . "' " if $homebranchfilter; |
292 |
$strsth.=" AND items.holdingbranch = '" . $holdingbranchfilter . "' " if $holdingbranchfilter; |
305 |
$strsth .= " AND items.holdingbranch = '" . $holdingbranchfilter . "' " if $holdingbranchfilter; |
293 |
$strsth.=" AND date_due >= ?" if $dateduefrom; |
306 |
$strsth .= " AND date_due >= ?" if $dateduefrom; |
294 |
$strsth.=" AND date_due <= ?" if $datedueto; |
307 |
$strsth .= " AND date_due <= ?" if $datedueto; |
|
|
308 |
|
295 |
# restrict patrons (borrowers) to those matching the patron attribute filter(s), if any |
309 |
# restrict patrons (borrowers) to those matching the patron attribute filter(s), if any |
296 |
my $bnlist = $have_pattr_filter_data ? join(',',keys %borrowernumber_to_attributes) : ''; |
310 |
my $bnlist = $have_pattr_filter_data ? join( ',', keys %borrowernumber_to_attributes ) : ''; |
297 |
$strsth =~ s/WHERE 1=1/WHERE 1=1 AND borrowers.borrowernumber IN ($bnlist)/ if $bnlist; |
311 |
$strsth =~ s/WHERE 1=1/WHERE 1=1 AND borrowers.borrowernumber IN ($bnlist)/ if $bnlist; |
298 |
$strsth =~ s/WHERE 1=1/WHERE 0=1/ if $have_pattr_filter_data && !$bnlist; # no match if no borrowers matched patron attrs |
312 |
$strsth =~ s/WHERE 1=1/WHERE 0=1/ |
299 |
$strsth.=" ORDER BY date_due, surname, firstname"; |
313 |
if $have_pattr_filter_data && !$bnlist; # no match if no borrowers matched patron attrs |
300 |
$template->param(sql=>$strsth); |
314 |
$strsth .= " ORDER BY date_due, surname, firstname"; |
301 |
my $sth=$dbh->prepare($strsth); |
315 |
$template->param( sql => $strsth ); |
|
|
316 |
my $sth = $dbh->prepare($strsth); |
302 |
$sth->execute( |
317 |
$sth->execute( |
303 |
($dateduefrom ? DateTime::Format::MySQL->format_datetime($dateduefrom) : ()), |
318 |
( $dateduefrom ? DateTime::Format::MySQL->format_datetime($dateduefrom) : () ), |
304 |
($datedueto ? DateTime::Format::MySQL->format_datetime($datedueto) : ()), |
319 |
( $datedueto ? DateTime::Format::MySQL->format_datetime($datedueto) : () ), |
305 |
); |
320 |
); |
306 |
|
321 |
|
307 |
my @overduedata; |
322 |
my @overduedata; |
308 |
while (my $data = $sth->fetchrow_hashref) { |
323 |
while ( my $data = $sth->fetchrow_hashref ) { |
309 |
|
324 |
|
310 |
# most of the overdue report data is linked to the database schema, i.e. things like borrowernumber and phone |
325 |
# most of the overdue report data is linked to the database schema, i.e. things like borrowernumber and phone |
311 |
# but the patron attributes (patron_attr_value_loop) are unnormalised and varies dynamically from one db to the next |
326 |
# but the patron attributes (patron_attr_value_loop) are unnormalised and varies dynamically from one db to the next |
312 |
|
327 |
|
313 |
my $pattrs = $borrowernumber_to_attributes{$data->{borrowernumber}} || {}; # patron attrs for this borrower |
328 |
my $pattrs = $borrowernumber_to_attributes{ $data->{borrowernumber} } || {}; # patron attrs for this borrower |
314 |
# $pattrs is a hash { attrcode => [ [value,displayvalue], [value,displayvalue]... ] } |
329 |
# $pattrs is a hash { attrcode => [ [value,displayvalue], [value,displayvalue]... ] } |
315 |
|
330 |
|
316 |
my @patron_attr_value_loop; # template array [ {value=>v1}, {value=>v2} ... } ] |
331 |
my @patron_attr_value_loop; # template array [ {value=>v1}, {value=>v2} ... } ] |
317 |
for my $pattr_filter (grep { ! $_->{isclone} } @patron_attr_filter_loop) { |
332 |
for my $pattr_filter ( grep { !$_->{isclone} } @patron_attr_filter_loop ) { |
318 |
my @displayvalues = map { $_->[1] } @{ $pattrs->{$pattr_filter->{code}} }; # grab second value from each subarray |
333 |
my @displayvalues = |
319 |
push @patron_attr_value_loop, { value => join(', ', sort { lc $a cmp lc $b } @displayvalues) }; |
334 |
map { $_->[1] } @{ $pattrs->{ $pattr_filter->{code} } }; # grab second value from each subarray |
|
|
335 |
push @patron_attr_value_loop, { value => join( ', ', sort { lc $a cmp lc $b } @displayvalues ) }; |
320 |
} |
336 |
} |
321 |
|
337 |
|
322 |
push @overduedata, { |
338 |
push @overduedata, { |
323 |
patron => Koha::Patrons->find( $data->{borrowernumber} ), |
339 |
patron => Koha::Patrons->find( $data->{borrowernumber} ), |
324 |
duedate => $data->{date_due}, |
340 |
duedate => $data->{date_due}, |
325 |
borrowernumber => $data->{borrowernumber}, |
341 |
borrowernumber => $data->{borrowernumber}, |
326 |
cardnumber => $data->{cardnumber}, |
342 |
cardnumber => $data->{cardnumber}, |
327 |
borrowertitle => $data->{borrowertitle}, |
343 |
borrowertitle => $data->{borrowertitle}, |
328 |
surname => $data->{surname}, |
344 |
surname => $data->{surname}, |
329 |
firstname => $data->{firstname}, |
345 |
firstname => $data->{firstname}, |
330 |
streetnumber => $data->{streetnumber}, |
346 |
streetnumber => $data->{streetnumber}, |
331 |
streettype => $data->{streettype}, |
347 |
streettype => $data->{streettype}, |
332 |
address => $data->{address}, |
348 |
address => $data->{address}, |
333 |
address2 => $data->{address2}, |
349 |
address2 => $data->{address2}, |
334 |
city => $data->{city}, |
350 |
city => $data->{city}, |
335 |
zipcode => $data->{zipcode}, |
351 |
zipcode => $data->{zipcode}, |
336 |
country => $data->{country}, |
352 |
country => $data->{country}, |
337 |
phone => $data->{phone}, |
353 |
phone => $data->{phone}, |
338 |
email => $data->{email}, |
354 |
email => $data->{email}, |
339 |
branchcode => $data->{branchcode}, |
355 |
branchcode => $data->{branchcode}, |
340 |
barcode => $data->{barcode}, |
356 |
barcode => $data->{barcode}, |
341 |
datelastborrowed => $data->{datelastborrowed}, |
357 |
datelastborrowed => $data->{datelastborrowed}, |
342 |
itemnum => $data->{itemnumber}, |
358 |
itemnum => $data->{itemnumber}, |
343 |
issuedate => $data->{issuedate}, |
359 |
issuedate => $data->{issuedate}, |
344 |
biblionumber => $data->{biblionumber}, |
360 |
biblionumber => $data->{biblionumber}, |
345 |
title => $data->{title}, |
361 |
title => $data->{title}, |
346 |
subtitle => $data->{subtitle}, |
362 |
subtitle => $data->{subtitle}, |
347 |
part_number => $data->{part_number}, |
363 |
part_number => $data->{part_number}, |
348 |
part_name => $data->{part_name}, |
364 |
part_name => $data->{part_name}, |
349 |
author => $data->{author}, |
365 |
author => $data->{author}, |
350 |
homebranchcode => $data->{homebranch}, |
366 |
homebranchcode => $data->{homebranch}, |
351 |
holdingbranchcode => $data->{holdingbranch}, |
367 |
holdingbranchcode => $data->{holdingbranch}, |
352 |
location => $data->{location}, |
368 |
location => $data->{location}, |
353 |
itemcallnumber => $data->{itemcallnumber}, |
369 |
itemcallnumber => $data->{itemcallnumber}, |
354 |
replacementprice => $data->{replacementprice}, |
370 |
replacementprice => $data->{replacementprice}, |
355 |
itemnotes_nonpublic => $data->{itemnotes_nonpublic}, |
371 |
itemnotes_nonpublic => $data->{itemnotes_nonpublic}, |
356 |
return_claim_created_on => $data->{return_claim_created_on}, |
372 |
return_claim_created_on => $data->{return_claim_created_on}, |
357 |
return_claim_id => $data->{return_claim_id}, |
373 |
return_claim_id => $data->{return_claim_id}, |
358 |
enumchron => $data->{enumchron}, |
374 |
enumchron => $data->{enumchron}, |
359 |
itemtype => $data->{itype}, |
375 |
itemtype => $data->{itype}, |
360 |
patron_attr_value_loop => \@patron_attr_value_loop, |
376 |
patron_attr_value_loop => \@patron_attr_value_loop, |
361 |
}; |
377 |
}; |
362 |
} |
378 |
} |
363 |
|
379 |
|
364 |
if ($op eq 'csv') { |
380 |
if ( $op eq 'csv' ) { |
365 |
binmode(STDOUT, ":encoding(UTF-8)"); |
381 |
binmode( STDOUT, ":encoding(UTF-8)" ); |
366 |
my $csv = build_csv(\@overduedata); |
382 |
my $csv = build_csv( \@overduedata ); |
367 |
print $input->header(-type => 'application/vnd.sun.xml.calc', |
383 |
print $input->header( |
368 |
-encoding => 'utf-8', |
384 |
-type => 'application/vnd.sun.xml.calc', |
369 |
-attachment=>"overdues.csv", |
385 |
-encoding => 'utf-8', |
370 |
-filename=>"overdues.csv" ); |
386 |
-attachment => "overdues.csv", |
|
|
387 |
-filename => "overdues.csv" |
388 |
); |
371 |
print $csv; |
389 |
print $csv; |
372 |
exit; |
390 |
exit; |
373 |
} |
391 |
} |
Lines 377-398
if ($noreport) {
Link Here
|
377 |
$new_cgi->delete('op'); |
395 |
$new_cgi->delete('op'); |
378 |
|
396 |
|
379 |
$template->param( |
397 |
$template->param( |
380 |
todaysdate => $today_dt, |
398 |
todaysdate => $today_dt, |
381 |
overdueloop => \@overduedata, |
399 |
overdueloop => \@overduedata, |
382 |
nnoverdue => scalar(@overduedata), |
400 |
nnoverdue => scalar(@overduedata), |
383 |
noverdue_is_plural => scalar(@overduedata) != 1, |
401 |
noverdue_is_plural => scalar(@overduedata) != 1, |
384 |
noreport => $noreport, |
402 |
noreport => $noreport, |
385 |
isfiltered => $isfiltered, |
403 |
isfiltered => $isfiltered, |
386 |
borflag_gonenoaddress => $borflagsfilter eq 'gonenoaddress', |
404 |
borflag_gonenoaddress => $borflagsfilter eq 'gonenoaddress', |
387 |
borflag_debarred => $borflagsfilter eq 'debarred', |
405 |
borflag_debarred => $borflagsfilter eq 'debarred', |
388 |
borflag_lost => $borflagsfilter eq 'lost', |
406 |
borflag_lost => $borflagsfilter eq 'lost', |
389 |
); |
407 |
); |
390 |
|
408 |
|
391 |
} |
409 |
} |
392 |
|
410 |
|
393 |
output_html_with_http_headers $input, $cookie, $template->output; |
411 |
output_html_with_http_headers $input, $cookie, $template->output; |
394 |
|
412 |
|
395 |
|
|
|
396 |
sub build_csv { |
413 |
sub build_csv { |
397 |
my $overdues = shift; |
414 |
my $overdues = shift; |
398 |
|
415 |
|
Lines 402-421
sub build_csv {
Link Here
|
402 |
|
419 |
|
403 |
# build header ... |
420 |
# build header ... |
404 |
my @keys = |
421 |
my @keys = |
405 |
qw ( duedate title author borrowertitle firstname surname phone barcode email address address2 zipcode city country |
422 |
qw ( duedate title author borrowertitle firstname surname phone barcode email address address2 zipcode city country |
406 |
branchcode datelastissued itemcallnumber biblionumber borrowernumber itemnum issuedate replacementprice itemnotes_nonpublic streetnumber streettype); |
423 |
branchcode datelastissued itemcallnumber biblionumber borrowernumber itemnum issuedate replacementprice itemnotes_nonpublic streetnumber streettype); |
407 |
my $csv = Text::CSV_XS->new(); |
424 |
my $csv = Text::CSV_XS->new(); |
408 |
$csv->combine(@keys); |
425 |
$csv->combine(@keys); |
409 |
push @lines, $csv->string(); |
426 |
push @lines, $csv->string(); |
410 |
|
427 |
|
411 |
my @private_keys = qw( borrowertitle firstname surname phone email address address2 zipcode city country streetnumber streettype ); |
428 |
my @private_keys = |
|
|
429 |
qw( borrowertitle firstname surname phone email address address2 zipcode city country streetnumber streettype ); |
430 |
|
412 |
# ... and rest of report |
431 |
# ... and rest of report |
413 |
foreach my $overdue ( @{ $overdues } ) { |
432 |
foreach my $overdue ( @{$overdues} ) { |
414 |
unless ( $logged_in_user->can_see_patron_infos( $overdue->{patron} ) ) { |
433 |
unless ( $logged_in_user->can_see_patron_infos( $overdue->{patron} ) ) { |
415 |
$overdue->{$_} = undef for @private_keys; |
434 |
$overdue->{$_} = undef for @private_keys; |
416 |
} |
435 |
} |
417 |
push @lines, $csv->string() if $csv->combine(map { $overdue->{$_} } @keys); |
436 |
push @lines, $csv->string() if $csv->combine( map { $overdue->{$_} } @keys ); |
418 |
} |
437 |
} |
419 |
|
438 |
|
420 |
return join("\n", @lines) . "\n"; |
439 |
return join( "\n", @lines ) . "\n"; |
421 |
} |
440 |
} |
422 |
- |
|
|