use C4::Branch;
use C4::Dates qw/format_date/;
use Date::Calc qw/Today/;
+use Text::CSV_XS;
my $input = new CGI;
my $order = $input->param( 'order' ) || '';
my $dbh = C4::Context->dbh;
-# download the complete CSV
-if ($op eq 'csv') {
-warn "BRANCH : $branchfilter";
- my $lib = $branchfilter ? "-library $branchfilter" :'';
- my $csv = `../misc/cronjobs/overdue_notices.pl -csv -n $lib`;
- print $input->header(-type => 'application/vnd.sun.xml.calc',
- -encoding => 'utf-8',
- -attachment=>"overdues.csv",
- -filename=>"overdues.csv" );
- print $csv;
- exit;
-}
my $req;
$req = $dbh->prepare( "select categorycode, description from categories order by description");
$req->execute;
borname => $bornamefilter,
order => $order,
showall => $showall,
+ csv_param_string => $input->query_string(),
);
my @sort_roots = qw(borrower title barcode date_due);
$bornamefilter =~s/\?/\_/g;
my $strsth="SELECT date_due,
- concat(surname,' ', firstname) as borrower,
+ surname,
+ firstname,
+ borrowers.title as borrowertitle,
+ CONCAT(borrowers.address, '\n', borrowers.address2) as address,
+ borrowers.city,
+ borrowers.zipcode,
borrowers.phone,
borrowers.email,
issues.itemnumber,
+ issues.issuedate,
items.barcode,
+ items.itemcallnumber,
biblio.title,
biblio.author,
borrowers.borrowernumber,
LEFT JOIN biblio ON (biblio.biblionumber=items.biblionumber )
WHERE 1=1 "; # placeholder, since it is possible that none of the additional
# conditions will be selected by user
-$strsth.=" AND date_due < '" . $todaysdate . "' " unless ($showall);
+$strsth.=" AND date_due < NOW() " 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;
$strsth.=" AND borrowers.branchcode = '" . $branchfilter . "' " if $branchfilter;
$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 "surname" or $order eq "surname desc") ? "$order, date_due" :
+ ($order eq "title" or $order eq "title desc") ? "$order, date_due, surname" :
+ ($order eq "barcode" or $order eq "barcode desc") ? "items.$order, date_due, surname" :
+ ($order eq "date_due desc") ? "date_due DESC, surname" :
+ "date_due, surname" # default sort order
);
$template->param(sql=>$strsth);
my $sth=$dbh->prepare($strsth);
my @overduedata;
while (my $data=$sth->fetchrow_hashref) {
push @overduedata, {
+ issuedate => format_date($data->{issuedate}),
duedate => format_date($data->{date_due}),
+ surname => $data->{surname},
+ firstname => $data->{firstname},
+ borrowertitle => $data->{borrowertitle},
borrowernumber => $data->{borrowernumber},
barcode => $data->{barcode},
itemnum => $data->{itemnumber},
- name => $data->{borrower},
+ itemcallnumber => $data->{itemcallnumber},
+ address => $data->{address},
+ city => $data->{city},
+ zipcode => $data->{zipcode},
phone => $data->{phone},
email => $data->{email},
biblionumber => $data->{biblionumber},
overdueloop => \@overduedata
);
+# download the complete CSV
+if ($op eq 'csv') {
+ binmode(STDOUT, ":utf8");
+ my $csv = build_csv(\@overduedata);
+ print $input->header(-type => 'application/vnd.sun.xml.calc',
+ -encoding => 'utf-8',
+ -attachment=>"overdues.csv",
+ -filename=>"overdues.csv" );
+ print $csv;
+ exit;
+}
+
output_html_with_http_headers $input, $cookie, $template->output;
+
+
+sub build_csv {
+ my $overdues = shift;
+
+ return "" if scalar(@$overdues) == 0;
+
+ my @lines = ();
+
+ # build header ...
+ my @keys = sort keys %{ $overdues->[0] };
+ my $csv = Text::CSV_XS->new({
+ binary => 1,
+ sep_char => C4::Context->preference("delimiter") ?
+ C4::Context->preference("delimiter") : ';' ,
+ });
+ $csv->combine(@keys);
+ push @lines, $csv->string();
+
+ # ... and rest of report
+ foreach my $overdue ( @{ $overdues } ) {
+ push @lines, $csv->string() if $csv->combine(map { $overdue->{$_} } @keys);
+ }
+
+ return join("\n", @lines) . "\n";
+}