|
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 |
- |
|
|