+if ($noreport) {
+ # la de dah ... page comes up presto-quicko
+ $template->param( noreport => $noreport );
+} else {
+ # FIXME : the left joins + where clauses make the following SQL query really slow with large datasets :(
+ #
+ # FIX 1: use the table with the least rows as first in the join, second least second, etc
+ # ref: http://www.fiftyfoureleven.com/weblog/web-development/programming-and-scripts/mysql-optimization-tip
+ #
+ # FIX 2: ensure there are indexes for columns participating in the WHERE clauses, where feasible/reasonable
+
+
+ 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,
+ 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,
+ items.homebranch,
+ items.holdingbranch,
+ biblio.title,
+ biblio.author,
+ borrowers.borrowernumber,
+ biblio.biblionumber,
+ borrowers.branchcode,
+ items.itemcallnumber,
+ items.replacementprice,
+ items.enumchron
+ FROM issues
+ LEFT JOIN borrowers ON (issues.borrowernumber=borrowers.borrowernumber )
+ LEFT JOIN items ON (issues.itemnumber=items.itemnumber)
+ LEFT JOIN biblioitems ON (biblioitems.biblioitemnumber=items.biblioitemnumber)
+ 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 (borrowers.firstname like '".$bornamefilter."%' or borrowers.surname like '".$bornamefilter."%' or borrowers.cardnumber like '".$bornamefilter."%')" if($bornamefilter) ;
+ $strsth.=" AND borrowers.categorycode = '" . $borcatfilter . "' " if $borcatfilter;
+ 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 items.homebranch = '" . $homebranchfilter . "' " if $homebranchfilter;
+ $strsth.=" AND items.holdingbranch = '" . $holdingbranchfilter . "' " if $holdingbranchfilter;
+ $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") ? "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);
+ #warn "overdue.pl : query string ".$strsth;
+ $sth->execute();
+
+ my @overduedata;
+ while (my $data = $sth->fetchrow_hashref) {
+
+ # most of the overdue report data is linked to the database schema, i.e. things like borrowernumber and phone
+ # but the patron attributes (patron_attr_value_loop) are unnormalised and varies dynamically from one db to the next
+
+ my $pattrs = $borrowernumber_to_attributes{$data->{borrowernumber}} || {}; # patron attrs for this borrower
+ # $pattrs is a hash { attrcode => [ [value,displayvalue], [value,displayvalue]... ] }
+
+ my @patron_attr_value_loop; # template array [ {value=>v1}, {value=>v2} ... } ]
+ for my $pattr_filter (grep { ! $_->{isclone} } @patron_attr_filter_loop) {
+ 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 => output_pref($dt),
+ borrowernumber => $data->{borrowernumber},
+ barcode => $data->{barcode},
+ itemnum => $data->{itemnumber},
+ 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},
+ title => $data->{title},
+ author => $data->{author},
+ branchcode => $data->{branchcode},
+ homebranchcode => $data->{homebranchcode},
+ holdingbranchcode => $data->{holdingbranchcode},
+ itemcallnumber => $data->{itemcallnumber},
+ replacementprice => $data->{replacementprice},
+ enumchron => $data->{enumchron},
+ patron_attr_value_loop => \@patron_attr_value_loop,
+ };
+ }
+
+ my ($attrorder) = $order =~ /patron_attr_(.*)$/;
+ my $patrorder = '';
+ my $sortorder = 'asc';
+ if (defined $attrorder) {
+ ($sortorder, $patrorder) = split /_/, $attrorder, 2;
+ }
+ print STDERR ">>> order is $order, patrorder is $patrorder, sortorder is $sortorder\n" if $debug;
+
+ if (my @attrtype = grep { $_->{'code'} eq $patrorder } @patron_attr_filter_loop) { # sort by patron attrs perhaps?
+ my $ordinal = $attrtype[0]{ordinal};
+ print STDERR ">>> sort ordinal is $ordinal\n" if $debug;
+
+ sub patronattr_sorter_asc {
+ lc $a->{patron_attr_value_loop}[$ordinal]{value}
+ cmp
+ lc $b->{patron_attr_value_loop}[$ordinal]{value} }
+
+ sub patronattr_sorter_des { -patronattr_sorter_asc() }
+
+ my $sorter = $sortorder eq 'desc' ? \&patronattr_sorter_des : \&patronattr_sorter_asc;
+ @overduedata = sort $sorter @overduedata;
+ }
+
+ if ($op eq 'csv') {
+ binmode(STDOUT, ":encoding(UTF-8)");
+ 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;
+ }
+
+ # generate parameter list for CSV download link
+ my $new_cgi = CGI->new($input);
+ $new_cgi->delete('op');
+ my $csv_param_string = $new_cgi->query_string();
+
+ $template->param(
+ csv_param_string => $csv_param_string,
+ todaysdate => output_pref($today_dt),
+ overdueloop => \@overduedata,
+ nnoverdue => scalar(@overduedata),
+ noverdue_is_plural => scalar(@overduedata) != 1,
+ noreport => $noreport,
+ isfiltered => $isfiltered,
+ borflag_gonenoaddress => $borflagsfilter eq 'gonenoaddress',
+ borflag_debarred => $borflagsfilter eq 'debarred',
+ borflag_lost => $borflagsfilter eq 'lost',
+ );
+
+}
+