From 6d2688364464f3953ae23795d6acce69e98faf41 Mon Sep 17 00:00:00 2001 From: Julian Maurice Date: Mon, 24 Mar 2014 11:56:58 +0100 Subject: [PATCH] Bug 7679: Various fixes for circulation statistics wizard - use SQL TRIM functions to avoid having '' and ' ' considered as different values - use Text::Unaccent to remove accents from columns or rows values when accessing %table. This is required as MySQL consider as equals two strings that differ only by their accents when using GROUP BY clause. - Exclude '' values from the list of columns or rows. Otherwise we could have a row 'UNKNOWN VALUE' and a row 'NULL' which both have the same values in their cells. --- reports/issues_stats.pl | 25 ++++++++++++++++--------- 1 file changed, 16 insertions(+), 9 deletions(-) diff --git a/reports/issues_stats.pl b/reports/issues_stats.pl index 194c842..b7c17c0 100755 --- a/reports/issues_stats.pl +++ b/reports/issues_stats.pl @@ -22,6 +22,7 @@ use warnings; use CGI qw ( -utf8 ); use Date::Manip; +use Text::Unaccent; use C4::Auth; use C4::Debug; @@ -318,7 +319,7 @@ sub calculate { ( $linesource eq 'items' ) ? " LEFT JOIN items ON (statistics.itemnumber = items.itemnumber) " : " LEFT JOIN borrowers ON (statistics.borrowernumber = borrowers.borrowernumber) "; - $strsth .= " WHERE $line is not null "; + $strsth .= " WHERE $line is not null AND $line != '' "; } if ( $line =~ /datetime/ ) { @@ -406,7 +407,7 @@ sub calculate { ( $colsource eq 'items' ) ? "LEFT JOIN items ON (statistics.itemnumber = items.itemnumber) " : "LEFT JOIN borrowers ON (statistics.borrowernumber = borrowers.borrowernumber) "; - $strsth2 .= " WHERE $column IS NOT NULL "; + $strsth2 .= " WHERE $column IS NOT NULL AND $column != '' "; } if ( $column =~ /datetime/ ) { @@ -478,14 +479,14 @@ sub calculate { # preparing calculation my $strcalc = "SELECT "; if($line_attribute_type) { - $strcalc .= "attribute_$line_attribute_type.attribute AS line_attribute, "; + $strcalc .= "TRIM(attribute_$line_attribute_type.attribute) AS line_attribute, "; } else { - $strcalc .= "$linefield, "; + $strcalc .= "TRIM($linefield), "; } if($column_attribute_type) { - $strcalc .= "attribute_$column_attribute_type.attribute AS column_attribute, "; + $strcalc .= "TRIM(attribute_$column_attribute_type.attribute) AS column_attribute, "; } else { - $strcalc .= "$colfield, "; + $strcalc .= "TRIM($colfield), "; } $strcalc .= ( $process == 1 ) ? " COUNT(*) " @@ -640,19 +641,25 @@ sub null_to_zzempty { sub table_set { my ($table, $row, $col, $val) = @_; - $table->{ null_to_zzempty(lc($row)) }->{ null_to_zzempty(lc($col)) } = $val; + $row = lc(unac_string('utf-8', $row // '')); + $col = lc(unac_string('utf-8', $col // '')); + $table->{ null_to_zzempty($row) }->{ null_to_zzempty($col) } = $val; } sub table_get { my ($table, $row, $col) = @_; - return $table->{ null_to_zzempty(lc($row)) }->{ null_to_zzempty(lc($col)) }; + $row = lc(unac_string('utf-8', $row // '')); + $col = lc(unac_string('utf-8', $col // '')); + return $table->{ null_to_zzempty($row) }->{ null_to_zzempty($col) }; } sub table_inc { my ($table, $row, $col, $inc) = @_; - $table->{ null_to_zzempty(lc($row // '')) }->{ null_to_zzempty(lc($col // '')) } += $inc; + $row = lc(unac_string('utf-8', $row // '')); + $col = lc(unac_string('utf-8', $col // '')); + $table->{ null_to_zzempty($row) }->{ null_to_zzempty($col) } += $inc; } 1; -- 2.1.4