From edda5d38ddf72405adf29033fcb0c5990d3e30d6 Mon Sep 17 00:00:00 2001 From: Paul Poulain Date: Fri, 22 Jul 2011 10:24:26 +0200 Subject: [PATCH] BZ6115: Acquisition reports : date filter & sorting don't work * the filter on dates don't work * when displaying by day or month, it was 100% alphabetic, april was before january for example. Adding the month number before the month name to get proper sorting * removed many commented warn & lines --- reports/acquisitions_stats.pl | 106 +++++------------------------------------ 1 files changed, 13 insertions(+), 93 deletions(-) diff --git a/reports/acquisitions_stats.pl b/reports/acquisitions_stats.pl index 8220148..ad0ac3f 100755 --- a/reports/acquisitions_stats.pl +++ b/reports/acquisitions_stats.pl @@ -47,10 +47,10 @@ my $fullreportname = "reports/acquisitions_stats.tmpl"; my $line = $input->param("Line"); my $column = $input->param("Column"); my @filters = $input->param("Filter"); -$filters[0]= (($line =~ /closedate/ || $column =~ /closedate/) ? format_date_in_iso($filters[0]) : undef); -$filters[1]= (($line =~ /closedate/ || $column =~ /closedate/) ? format_date_in_iso($filters[1]) : undef); -$filters[2]= (($line =~ /delivery/ || $column =~ /delivery/) ? format_date_in_iso($filters[2]) : undef); -$filters[3]= (($line =~ /delivery/ || $column =~ /delivery/) ? format_date_in_iso($filters[3]) : undef); +$filters[0] = format_date_in_iso( $filters[0] ); +$filters[1] = format_date_in_iso( $filters[1] ); +$filters[2] = format_date_in_iso( $filters[2] ); +$filters[3] = format_date_in_iso( $filters[3] ); my $podsp = $input->param("PlacedOnDisplay"); my $rodsp = $input->param("ReceivedOnDisplay"); my $aodsp = $input->param("AcquiredOnDisplay"); ##added by mason. @@ -58,7 +58,6 @@ my $calc = $input->param("Cellvalue"); my $output = $input->param("output"); my $basename = $input->param("basename"); -#warn "calcul : ".$calc; my ($template, $borrowernumber, $cookie) = get_template_and_user({template_name => $fullreportname, query => $input, @@ -303,56 +302,40 @@ sub calculate { my @linefilter; - # warn "filtres ".@filters[0]; - # warn "filtres ".@filters[1]; - # warn "filtres ".@filters[2]; - # warn "filtres ".@filters[3]; - $linefilter[0] = @$filters[0] if ( $line =~ /closedate/ ); $linefilter[1] = @$filters[1] if ( $line =~ /closedate/ ); $linefilter[0] = @$filters[2] if ( $line =~ /received/ ); $linefilter[1] = @$filters[3] if ( $line =~ /received/ ); -# $linefilter[0] = @$filters[4] if ( $line =~ /acquired/ ); -# $linefilter[1] = @$filters[5] if ( $line =~ /acquired/ ); - $linefilter[0] = @$filters[4] if ( $line =~ /bookseller/ ); $linefilter[0] = @$filters[5] if ( $line =~ /itemtype/ ); $linefilter[0] = @$filters[6] if ( $line =~ /budget/ ); $linefilter[0] = @$filters[7] if ( $line =~ /sort1/ ); $linefilter[0] = @$filters[8] if ( $line =~ /sort2/ ); - #warn "filtre lignes".$linefilter[0]." ".$linefilter[1]; - # my @colfilter; $colfilter[0] = @$filters[0] if ( $column =~ /closedate/ ); $colfilter[1] = @$filters[1] if ( $column =~ /closedate/ ); $colfilter[0] = @$filters[2] if ( $column =~ /received/ ); $colfilter[1] = @$filters[3] if ( $column =~ /received/ ); -# $colfilter[0] = @$filters[4] if ( $column =~ /acquired/ ); -# $colfilter[1] = @$filters[5] if ( $column =~ /acquired/ ); $colfilter[0] = @$filters[4] if ( $column =~ /bookseller/ ); $colfilter[0] = @$filters[5] if ( $column =~ /itemtype/ ); $colfilter[0] = @$filters[6] if ( $column =~ /budget/ ); $colfilter[0] = @$filters[7] if ( $column =~ /sort1/ ); $colfilter[0] = @$filters[8] if ( $column =~ /sort2/ ); - #warn "filtre col ".$colfilter[0]." ".$colfilter[1]; - -# warn "line=$line, podsp=$podsp, rodsp=$rodsp, aodsp=$aodsp\n"; - # 1st, loop rows. my $linefield; if ( ( $line =~ /closedate/ ) and ( $podsp == 1 ) ) { #Display by day - $linefield .= "dayname($line)"; + $linefield .= "concat(hex(weekday($line)+1),'-',dayname($line))"; } elsif ( ( $line =~ /closedate/ ) and ( $podsp == 2 ) ) { #Display by Month - $linefield .= "monthname($line)"; + $linefield .= "concat(hex(month($line)),'-',monthname($line))"; } elsif ( ( $line =~ /closedate/ ) and ( $podsp == 3 ) ) { @@ -363,12 +346,12 @@ sub calculate { elsif ( ( $line =~ /received/ ) and ( $rodsp == 1 ) ) { #Display by day - $linefield .= "dayname($line)"; + $linefield .= "concat(hex(weekday($line)+1),'-',dayname($line))"; } elsif ( ( $line =~ /received/ ) and ( $rodsp == 2 ) ) { #Display by Month - $linefield .= "monthname($line)"; + $linefield .= "concat(hex(month($line)),'-',monthname($line))"; } elsif ( ( $line =~ /received/ ) and ( $rodsp == 3 ) ) { @@ -376,22 +359,6 @@ sub calculate { $linefield .= "Year($line)"; } -# elsif ( ( $line =~ /acquired/ ) and ( $aodsp == 1 ) ) { -# -# #Display by day -# $linefield .= "dayname($line)"; -# } -# elsif ( ( $line =~ /acquired/ ) and ( $aodsp == 2 ) ) { -# -# #Display by Month -# $linefield .= "monthname($line)"; -# } -# elsif ( ( $line =~ /acquired/ ) and ( $aodsp == 3 ) ) { -# -# #Display by Year -# $linefield .= "Year($line)"; -# -# } else { $linefield .= $line; } @@ -405,8 +372,6 @@ sub calculate { LEFT JOIN aqbooksellers ON (aqbasket.booksellerid=aqbooksellers.id) WHERE (aqorders.basketno=aqbasket.basketno) AND $line IS NOT NULL AND $line <> '' "; - -# LEFT JOIN aqorderdelivery ON (aqorders.ordernumber =aqorderdelivery.ordernumber ) if (@linefilter) { if ( $linefilter[1] ) { @@ -421,7 +386,6 @@ sub calculate { ( $linefilter[0] ) and ( ( $line =~ /closedate/ ) or ( $line =~ /received/ )) -# or ( $line =~ /acquired/ ) ) ) { $strsth .= " AND $line >= ? "; @@ -434,8 +398,6 @@ sub calculate { $strsth .= " GROUP BY $linefield"; $strsth .= " ORDER BY $line"; - #warn "377:strsth= $strsth"; - my $sth = $dbh->prepare($strsth); if ( (@linefilter) and ( $linefilter[1] ) ) { $sth->execute( $linefilter[0], $linefilter[1] ); @@ -454,19 +416,17 @@ sub calculate { } $cell{totalrow} = 0; } -# warn "column=$column, podsp=$podsp, rodsp=$rodsp, aodsp=$aodsp\n"; - # 2nd, loop cols. my $colfield; if ( ( $column =~ /closedate/ ) and ( $podsp == 1 ) ) { #Display by day - $colfield .= "dayname($column)"; + $colfield .= "concat(hex(weekday($column)+1),'-',dayname($column))"; } elsif ( ( $column =~ /closedate/ ) and ( $podsp == 2 ) ) { #Display by Month - $colfield .= "monthname($column)"; + $colfield .= "concat(hex(month($column)),'-',monthname($column))"; } elsif ( ( $column =~ /closedate/ ) and ( $podsp == 3 ) ) { @@ -477,12 +437,12 @@ sub calculate { elsif ( ( $column =~ /received/ ) and ( $rodsp == 1 ) ) { #Display by day - $colfield .= "dayname($column)"; + $colfield .= "concat(hex(weekday($column)+1),'-',dayname($column))"; } elsif ( ( $column =~ /received/ ) and ( $rodsp == 2 ) ) { #Display by Month - $colfield .= "monthname($column)"; + $colfield .= "concat(hex(month($column)),'-',monthname($column))"; } elsif ( ( $column =~ /received/ ) and ( $rodsp == 3 ) ) { @@ -490,22 +450,6 @@ sub calculate { $colfield .= "Year($column)"; } -# elsif ( ( $column =~ /dateaccessioned/ ) and ( $aodsp == 1 ) ) { -# -# #Display by day -# $colfield .= "dayname($column)"; -# } -# elsif ( ( $column =~ /dateaccessioned/ ) and ( $aodsp == 2 ) ) { -# -# #Display by Month -# $colfield .= "monthname($column)"; -# } -# elsif ( ( $column =~ /dateaccessioned/ ) and ( $aodsp == 3 ) ) { -# -# #Display by Year -# $colfield .= "Year($column)"; -# -# } else { $colfield .= $column; } @@ -521,8 +465,6 @@ sub calculate { WHERE (aqorders.basketno=aqbasket.basketno) AND $column IS NOT NULL AND $column <> '' "; -# LEFT JOIN aqorderdelivery ON (aqorders.ordernumber =aqorderdelivery.ordernumber ) - if (@colfilter) { if ( $colfilter[1] ) { if ( $colfilter[0] ) { @@ -536,7 +478,6 @@ sub calculate { ( $colfilter[0] ) and ( ( $column =~ /closedate/ ) or ( $line =~ /received/ )) -# or ( $line =~ /acquired/ ) ) ) { $strsth2 .= " AND $column >= ? "; @@ -549,7 +490,7 @@ sub calculate { $strsth2 .= " GROUP BY $colfield"; - $strsth2 .= " ORDER BY $column"; + $strsth2 .= " ORDER BY $colfield"; my $sth2 = $dbh->prepare($strsth2); @@ -570,8 +511,6 @@ sub calculate { } } - # warn "fin des titres colonnes"; - my $i = 0; my @totalcol; my $hilighted = -1; @@ -579,7 +518,6 @@ sub calculate { #Initialization of cell values..... my %table; -# warn "init table...\n"; foreach my $row (@loopline) { foreach my $col (@loopcol) { $table{ $row->{rowtitle} }->{ $col->{coltitle} } = 0; @@ -600,8 +538,6 @@ sub calculate { LEFT JOIN aqbooksellers ON (aqbasket.booksellerid=aqbooksellers.id) WHERE (aqorders.basketno=aqbasket.basketno) "; - -# LEFT JOIN aqorderdelivery ON (aqorders.ordernumber =aqorderdelivery.ordernumber ) @$filters[0] =~ s/\*/%/g if ( @$filters[0] ); $strcalc .= " AND aqbasket.closedate >= '" . @$filters[0] . "'" @@ -615,12 +551,6 @@ sub calculate { @$filters[3] =~ s/\*/%/g if ( @$filters[3] ); $strcalc .= " AND aqorders.datereceived <= '" . @$filters[3] . "'" if ( @$filters[3] ); -# @$filters[4] =~ s/\*/%/g if ( @$filters[4] ); -# $strcalc .= " AND aqbasket.closedate >= '" . @$filters[4] . "'" -# if ( @$filters[4] ); -# @$filters[5] =~ s/\*/%/g if ( @$filters[5] ); -# $strcalc .= " AND aqbasket.closedate <= '" . @$filters[5] . "'" -# if ( @$filters[5] ); @$filters[4] =~ s/\*/%/g if ( @$filters[4] ); $strcalc .= " AND aqbooksellers.name LIKE '" . @$filters[4] . "'" if ( @$filters[4] ); @@ -640,19 +570,13 @@ sub calculate { $strcalc .= " AND aqorders.datecancellationprinted is NULL "; $strcalc .= " GROUP BY $linefield, $colfield ORDER BY $linefield,$colfield"; - -# warn $strcalc . "\n"; - my $dbcalc = $dbh->prepare($strcalc); $dbcalc->execute; - # warn "filling table"; my $emptycol; while ( my ( $row, $col, $value ) = $dbcalc->fetchrow ) { next if ($row eq undef || $col eq undef); -# warn "filling table $row / $col / $value "; - $emptycol = 1 if ( !defined($col) ); $col = "zzEMPTY" if ( !defined($col) ); $row = "zzEMPTY" if ( !defined($row) ); @@ -682,7 +606,6 @@ sub calculate { $hilighted = -$hilighted; } - # warn "footer processing"; foreach my $col (@loopcol) { my $total = 0; foreach my $row (@looprow) { @@ -693,11 +616,8 @@ sub calculate { ( $col->{coltitle} eq "NULL" ) ? "zzEMPTY" : $col->{coltitle} }; - -# warn "value added ".$table{$row->{rowtitle}}->{$col->{coltitle}}. "for line ".$row->{rowtitle}; } - # warn "summ for column ".$col->{coltitle}." = ".$total; push @loopfooter, { 'totalcol' => $total }; } -- 1.7.4.1