use C4::Branch;
use C4::Debug;
use C4::Dates qw/format_date format_date_in_iso/;
-use Date::Calc qw/Today/;
use Text::CSV_XS;
+use Koha::DateUtils;
+use DateTime;
my $input = new CGI;
my $order = $input->param('order') || '';
my $op = $input->param('op') || '';
my $dateduefrom = format_date_in_iso($input->param( 'dateduefrom' )) || '';
my $datedueto = format_date_in_iso($input->param( 'datedueto' )) || '';
+# FIXME This is a kludge to include times
+if ($datedueto) {
+ $datedueto .= ' 23:59';
+}
+if ($dateduefrom) {
+ $dateduefrom .= ' 00:00';
+}
+# kludge end
my $isfiltered = $op =~ /apply/i && $op =~ /filter/i;
my $noreport = C4::Context->preference('FilterBeforeOverdueReport') && ! $isfiltered && $op ne "csv";
# FIX 2: ensure there are indexes for columns participating in the WHERE clauses, where feasible/reasonable
- my $todaysdate = sprintf("%-04.4d-%-02.2d-%02.2d", Today());
+ my $today_dt = DateTime->now(time_zone => C4::Context->tz);
+ $today_dt->truncate(to => 'minute');
+ my $todaysdate = $today_dt->strftime('%Y-%m-%d %H:%M');
$bornamefilter =~s/\*/\%/g;
$bornamefilter =~s/\?/\_/g;
my $strsth="SELECT date_due,
borrowers.title as borrowertitle,
- concat(surname,' ', firstname) as borrower,
+ borrowers.surname,
+ borrowers.firstname,
borrowers.streetnumber,
borrowers.streettype,
borrowers.address,
borrowers.phone,
borrowers.email,
issues.itemnumber,
+ issues.issuedate,
items.barcode,
biblio.title,
biblio.author,
biblio.biblionumber,
borrowers.branchcode,
items.itemcallnumber,
- items.replacementprice
+ items.replacementprice,
+ items.enumchron
FROM issues
LEFT JOIN borrowers ON (issues.borrowernumber=borrowers.borrowernumber )
LEFT JOIN items ON (issues.itemnumber=items.itemnumber)
$strsth.=" AND date_due < '" . $todaysdate . "' " unless ($showall);
$strsth.=" AND (borrowers.firstname like '".$bornamefilter."%' or borrowers.surname like '".$bornamefilter."%' or borrowers.cardnumber like '".$bornamefilter."%')" if($bornamefilter) ;
$strsth.=" AND borrowers.categorycode = '" . $borcatfilter . "' " if $borcatfilter;
- $strsth.=" AND biblioitems.itemtype = '" . $itemtypefilter . "' " if $itemtypefilter;
- $strsth.=" AND borrowers.flags = '" . $borflagsfilter . "' " if $borflagsfilter;
+ if( $itemtypefilter ){
+ if( C4::Context->preference('item-level_itypes') ){
+ $strsth.=" AND items.itype = '" . $itemtypefilter . "' ";
+ } else {
+ $strsth.=" AND biblioitems.itemtype = '" . $itemtypefilter . "' ";
+ }
+ }
+ if ( $borflagsfilter eq 'gonenoaddress' ) {
+ $strsth .= " AND borrowers.gonenoaddress <> 0";
+ }
+ elsif ( $borflagsfilter eq 'debarred' ) {
+ $strsth .= " AND borrowers.debarred >= CURDATE()" ;
+ }
+ elsif ( $borflagsfilter eq 'lost') {
+ $strsth .= " AND borrowers.lost <> 0";
+ }
$strsth.=" AND borrowers.branchcode = '" . $branchfilter . "' " if $branchfilter;
$strsth.=" AND date_due < '" . $datedueto . "' " if $datedueto;
$strsth.=" AND date_due > '" . $dateduefrom . "' " if $dateduefrom;
$strsth =~ s/WHERE 1=1/WHERE 1=1 AND borrowers.borrowernumber IN ($bnlist)/ if $bnlist;
$strsth =~ s/WHERE 1=1/WHERE 0=1/ if $have_pattr_filter_data && !$bnlist; # no match if no borrowers matched patron attrs
$strsth.=" ORDER BY " . (
- ($order eq "borrower" or $order eq "borrower desc") ? "$order, date_due" :
- ($order eq "title" or $order eq "title desc") ? "$order, date_due, borrower" :
- ($order eq "barcode" or $order eq "barcode desc") ? "items.$order, date_due, borrower" :
- ($order eq "date_due desc") ? "date_due DESC, borrower" :
- "date_due, borrower" # default sort order
+ ($order eq "borrower") ? "surname, firstname, date_due" :
+ ($order eq "borrower desc") ? "surname desc, firstname desc, date_due" :
+ ($order eq "title" or $order eq "title desc") ? "$order, date_due, surname, firstname" :
+ ($order eq "barcode" or $order eq "barcode desc") ? "items.$order, date_due, surname, firstname" :
+ ($order eq "date_due desc") ? "date_due DESC, surname, firstname" :
+ "date_due, surname, firstname" # default sort order
);
$template->param(sql=>$strsth);
my $sth=$dbh->prepare($strsth);
my @displayvalues = map { $_->[1] } @{ $pattrs->{$pattr_filter->{code}} }; # grab second value from each subarray
push @patron_attr_value_loop, { value => join(', ', sort { lc $a cmp lc $b } @displayvalues) };
}
+ my $dt = dt_from_string($data->{date_due}, 'sql');
push @overduedata, {
- duedate => format_date($data->{date_due}),
+ duedate => output_pref($dt),
borrowernumber => $data->{borrowernumber},
barcode => $data->{barcode},
itemnum => $data->{itemnumber},
+ issuedate => format_date($data->{issuedate}),
borrowertitle => $data->{borrowertitle},
- name => $data->{borrower},
+ surname => $data->{surname},
+ firstname => $data->{firstname},
streetnumber => $data->{streetnumber},
streettype => $data->{streettype},
address => $data->{address},
branchcode => $data->{branchcode},
itemcallnumber => $data->{itemcallnumber},
replacementprice => $data->{replacementprice},
+ enumchron => $data->{enumchron},
patron_attr_value_loop => \@patron_attr_value_loop,
};
}
}
if ($op eq 'csv') {
- binmode(STDOUT, ":utf8");
+ binmode(STDOUT, ":encoding(UTF-8)");
my $csv = build_csv(\@overduedata);
print $input->header(-type => 'application/vnd.sun.xml.calc',
-encoding => 'utf-8',
$template->param(
csv_param_string => $csv_param_string,
- todaysdate => format_date($todaysdate),
+ todaysdate => output_pref($today_dt),
overdueloop => \@overduedata,
nnoverdue => scalar(@overduedata),
noverdue_is_plural => scalar(@overduedata) != 1,
my @lines = ();
# build header ...
- my @keys = grep { $_ ne 'patron_attr_value_loop' } sort keys %{ $overdues->[0] };
+ my @keys = qw /duedate title author borrowertitle firstname surname phone barcode email address address2 zipcode city country
+ branchcode itemcallnumber biblionumber borrowernumber itemnum issuedate replacementprice streetnumber streettype/;
my $csv = Text::CSV_XS->new();
$csv->combine(@keys);
push @lines, $csv->string();
projects / koha.git / blobdiff