From 08332649ffec34d7eea22e09ecff75a033b30287 Mon Sep 17 00:00:00 2001 From: Julian Maurice Date: Wed, 5 Feb 2014 15:40:34 +0100 Subject: [PATCH] Bug 7679: Fix several bugs in circulation statistics wizard - Display values in NULL columns/rows - Display columns/rows that contain values only in NULL rows/columns - "To" filter in period row should be inclusive (YYYY-MM-DD should be YYYY-MM-DD 23:59:59) - Make it possible to use only the "To" filter in period row (actually it results in DBI error (2 bind variables instead of 1)) --- .../prog/en/modules/reports/issues_stats.tt | 2 +- reports/issues_stats.pl | 54 ++++++++++++---------- 2 files changed, 31 insertions(+), 25 deletions(-) diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/issues_stats.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/reports/issues_stats.tt index 6096aa3..c412ce8 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/issues_stats.tt +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/reports/issues_stats.tt @@ -69,7 +69,7 @@ [% loopro.rowtitle_display or "UNKNOWN VALUE" |html %] [% FOREACH loopcel IN loopro.loopcell %] - [% loopcel.value %] + [% loopcel.value || 0 %] [% END %] [% loopro.totalrow %] diff --git a/reports/issues_stats.pl b/reports/issues_stats.pl index cdb89a5..46fa03f 100755 --- a/reports/issues_stats.pl +++ b/reports/issues_stats.pl @@ -311,23 +311,23 @@ sub calculate { if($line_attribute_type) { $strsth = "SELECT attribute FROM borrower_attributes WHERE code = '$line_attribute_type' "; } else { - $strsth = "SELECT distinctrow $linefield FROM statistics, "; + $strsth = "SELECT distinctrow $linefield FROM statistics "; # get stats on items if ccode or location, otherwise borrowers. $strsth .= ( $linesource eq 'items' ) - ? " items WHERE (statistics.itemnumber=items.itemnumber) " - : " borrowers WHERE (statistics.borrowernumber=borrowers.borrowernumber) "; + ? " LEFT JOIN items ON (statistics.itemnumber = items.itemnumber) " + : " LEFT JOIN borrowers ON (statistics.borrowernumber = borrowers.borrowernumber) "; } - $strsth .= " AND $line is not null "; + $strsth .= " WHERE $line is not null "; if ( $line =~ /datetime/ ) { if ( $linefilter[1] and ( $linefilter[0] ) ) { $strsth .= " AND $line between ? AND ? "; } elsif ( $linefilter[1] ) { - $strsth .= " AND $line < ? "; + $strsth .= " AND $line <= ? "; } elsif ( $linefilter[0] ) { - $strsth .= " AND $line > ? "; + $strsth .= " AND $line >= ? "; } $strsth .= " AND type ='" . $type . "' " if $type; $strsth .= " AND dayname(datetime) ='" . $daysel . "' " if $daysel; @@ -340,8 +340,10 @@ sub calculate { $debug and warn $strsth; push @loopfilter, { crit => 'SQL =', sql => 1, filter => $strsth }; my $sth = $dbh->prepare($strsth); - if ( (@linefilter) and ( $linefilter[1] ) ) { - $sth->execute( $linefilter[0], $linefilter[1] ); + if ( (@linefilter) and ($linefilter[0]) and ($linefilter[1]) ) { + $sth->execute( $linefilter[0], $linefilter[1] . " 23:59:59" ); + } elsif ( $linefilter[1] ) { + $sth->execute( $linefilter[1] . " 23:59:59" ); } elsif ( $linefilter[0] ) { $sth->execute( $linefilter[0] ); } else { @@ -397,23 +399,23 @@ sub calculate { if($column_attribute_type) { $strsth2 = "SELECT attribute FROM borrower_attributes WHERE code = '$column_attribute_type' "; } else { - $strsth2 = "SELECT distinctrow $colfield FROM statistics, "; + $strsth2 = "SELECT distinctrow $colfield FROM statistics "; # get stats on items if ccode or location, otherwise borrowers. $strsth2 .= ( $colsource eq 'items' ) - ? "items WHERE (statistics.itemnumber=items.itemnumber) " - : "borrowers WHERE (statistics.borrowernumber=borrowers.borrowernumber) "; + ? "LEFT JOIN items ON (statistics.itemnumber = items.itemnumber) " + : "LEFT JOIN borrowers ON (statistics.borrowernumber = borrowers.borrowernumber) "; } - $strsth2 .= " AND $column IS NOT NULL "; + $strsth2 .= " WHERE $column IS NOT NULL "; if ( $column =~ /datetime/ ) { if ( ( $colfilter[1] ) and ( $colfilter[0] ) ) { $strsth2 .= " AND $column BETWEEN ? AND ? "; } elsif ( $colfilter[1] ) { - $strsth2 .= " AND $column < ? "; + $strsth2 .= " AND $column <= ? "; } elsif ( $colfilter[0] ) { - $strsth2 .= " AND $column > ? "; + $strsth2 .= " AND $column >= ? "; } $strsth2 .= " AND type ='". $type ."' " if $type; $strsth2 .= " AND dayname(datetime) ='". $daysel ."' " if $daysel; @@ -427,8 +429,10 @@ sub calculate { $debug and warn $strsth2; push @loopfilter, { crit => 'SQL =', sql => 1, filter => $strsth2 }; my $sth2 = $dbh->prepare($strsth2); - if ( (@colfilter) and ( $colfilter[1] ) ) { - $sth2->execute( $colfilter[0], $colfilter[1] ); + if ( (@colfilter) and ($colfilter[0]) and ($colfilter[1]) ) { + $sth2->execute( $colfilter[0], $colfilter[1] . " 23:59:59" ); + } elsif ( $colfilter[1] ) { + $sth2->execute( $colfilter[1] . " 23:59:59" ); } elsif ( $colfilter[0] ) { $sth2->execute( $colfilter[0] ); } else { @@ -592,12 +596,11 @@ sub calculate { my $value = table_get(\%table, $row->{rowtitle}, $col->{coltitle}); push @loopcell, { value => $value }; } - my $rowtitle = ( $row->{rowtitle} eq "NULL" ) ? "zzEMPTY" : $row->{rowtitle}; push @looprow, { 'rowtitle_display' => $row->{rowtitle_display}, - 'rowtitle' => $rowtitle, + 'rowtitle' => $row->{rowtitle}, 'loopcell' => \@loopcell, - 'totalrow' => table_get(\%table, $rowtitle, 'totalrow'), + 'totalrow' => table_get(\%table, $row->{rowtitle}, 'totalrow'), }; } for my $col (@loopcol) { @@ -624,11 +627,14 @@ sub calculate { return [ ( \%globalline ) ]; } -sub null_to_zzempty ($) { - my $string = shift; - defined($string) or return 'zzEMPTY'; - ($string eq "NULL") and return 'zzEMPTY'; - return $string; # else return the valid value +sub null_to_zzempty { + my $string = shift; + + if (!defined($string) or $string eq '' or uc($string) eq 'NULL') { + return 'zzEMPTY'; + } + + return $string; } sub table_set { -- 2.1.4