use C4::Auth;
use C4::Branch;
use C4::Debug;
-use C4::Dates qw/format_date/;
+use C4::Dates qw/format_date format_date_in_iso/;
use Date::Calc qw/Today/;
use Text::CSV_XS;
my $borflagsfilter = $input->param('borflag') || '';
my $branchfilter = $input->param('branch') || '';
my $op = $input->param('op') || '';
+my $dateduefrom = format_date_in_iso($input->param( 'dateduefrom' )) || '';
+my $datedueto = format_date_in_iso($input->param( 'datedueto' )) || '';
my $isfiltered = $op =~ /apply/i && $op =~ /filter/i;
my $noreport = C4::Context->preference('FilterBeforeOverdueReport') && ! $isfiltered && $op ne "csv";
patron_attr_filter_loop => \@patron_attr_filter_loop,
borname => $bornamefilter,
order => $order,
- showall => $showall);
+ showall => $showall,
+ DHTMLcalendar_dateformat => C4::Dates->DHTMLcalendar(),
+ dateduefrom => $input->param( 'dateduefrom' ) || '',
+ datedueto => $input->param( 'datedueto' ) || '',
+);
if ($noreport) {
# la de dah ... page comes up presto-quicko
$bornamefilter =~s/\?/\_/g;
my $strsth="SELECT date_due,
- concat(surname,' ', firstname) as borrower,
+ borrowers.title as borrowertitle,
+ borrowers.surname,
+ borrowers.firstname,
+ borrowers.streetnumber,
+ borrowers.streettype,
+ borrowers.address,
+ borrowers.address2,
+ borrowers.city,
+ borrowers.zipcode,
+ borrowers.country,
borrowers.phone,
borrowers.email,
issues.itemnumber,
+ issues.issuedate,
items.barcode,
biblio.title,
biblio.author,
$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;
+ if( $itemtypefilter ){
+ if( C4::Context->preference('item-level_itypes') ){
+ $strsth.=" AND items.itype = '" . $itemtypefilter . "' ";
+ } else {
+ $strsth.=" AND biblioitems.itemtype = '" . $itemtypefilter . "' ";
+ }
+ }
$strsth.=" AND borrowers.flags = '" . $borflagsfilter . "' " if $borflagsfilter;
$strsth.=" AND borrowers.branchcode = '" . $branchfilter . "' " if $branchfilter;
+ $strsth.=" AND date_due < '" . $datedueto . "' " if $datedueto;
+ $strsth.=" AND date_due > '" . $dateduefrom . "' " if $dateduefrom;
# restrict patrons (borrowers) to those matching the patron attribute filter(s), if any
my $bnlist = $have_pattr_filter_data ? join(',',keys %borrowernumber_to_attributes) : '';
$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);
borrowernumber => $data->{borrowernumber},
barcode => $data->{barcode},
itemnum => $data->{itemnumber},
- name => $data->{borrower},
+ issuedate => format_date($data->{issuedate}),
+ borrowertitle => $data->{borrowertitle},
+ surname => $data->{surname},
+ firstname => $data->{firstname},
+ streetnumber => $data->{streetnumber},
+ streettype => $data->{streettype},
+ address => $data->{address},
+ address2 => $data->{address2},
+ city => $data->{city},
+ zipcode => $data->{zipcode},
+ country => $data->{country},
phone => $data->{phone},
email => $data->{email},
biblionumber => $data->{biblionumber},
}
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',
my @lines = ();
# build header ...
- my @keys = grep { $_ ne 'patron_attr_value_loop' } sort keys %{ $overdues->[0] };
+ my @keys = qw /duedate title author borrowertitle name 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();