X-Git-Url: http://git.rot13.org/?a=blobdiff_plain;f=reports%2Fissues_avg_stats.pl;h=372f3c00733f4f6df036a612f427ed791ddbf130;hb=f22d9e1337183dfe6afe2fb014841bf17d657a11;hp=b3ef7380a05f76c830240371e25eb32ec332e65d;hpb=63dd9232e85fa6009242b33ffd11e4c9e1e20798;p=koha.git diff --git a/reports/issues_avg_stats.pl b/reports/issues_avg_stats.pl index b3ef7380a0..372f3c0073 100755 --- a/reports/issues_avg_stats.pl +++ b/reports/issues_avg_stats.pl @@ -14,11 +14,12 @@ # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR # A PARTICULAR PURPOSE. See the GNU General Public License for more details. # -# You should have received a copy of the GNU General Public License along with -# Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place, -# Suite 330, Boston, MA 02111-1307 USA +# You should have received a copy of the GNU General Public License along +# with Koha; if not, write to the Free Software Foundation, Inc., +# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA. use strict; +#use warnings; FIXME - Bug 2505 use C4::Auth; use CGI; use C4::Context; @@ -26,7 +27,8 @@ use C4::Branch; # GetBranches use C4::Output; use C4::Koha; use C4::Circulation; -use C4::Dates; +use C4::Reports; +use C4::Dates qw/format_date format_date_in_iso/; use Date::Calc qw(Delta_Days); =head1 NAME @@ -54,17 +56,17 @@ my $rodsp = $input->param("ReturnDisplay"); my $calc = $input->param("Cellvalue"); my $output = $input->param("output"); my $basename = $input->param("basename"); -my $mime = $input->param("MIME"); -my $del = $input->param("sep"); #warn "calcul : ".$calc; my ($template, $borrowernumber, $cookie) = get_template_and_user({template_name => $fullreportname, query => $input, type => "intranet", authnotrequired => 0, - flagsrequired => {reports => 1}, + flagsrequired => {reports => '*'}, debug => 1, }); +our $sep = $input->param("sep"); +$sep = "\t" if ($sep eq 'tabulation'); $template->param(do_it => $do_it, DHTMLcalendar_dateformat => C4::Dates->DHTMLcalendar(), ); @@ -75,7 +77,7 @@ if ($do_it) { # Printing results to screen $template->param(mainloop => $results); output_html_with_http_headers $input, $cookie, $template->output; - exit(1); + exit; } else { # Printing to a csv file print $input->header(-type => 'application/vnd.sun.xml.calc', @@ -84,8 +86,6 @@ if ($do_it) { -filename=>"$basename.csv" ); my $cols = @$results[0]->{loopcol}; my $lines = @$results[0]->{looprow}; - my $sep; - $sep =C4::Context->preference("delimiter"); # header top-right print @$results[0]->{line} ."/". @$results[0]->{column} .$sep; # Other header @@ -110,7 +110,7 @@ if ($do_it) { print $sep.$col->{totalcol}; } print $sep.@$results[0]->{total}; - exit(1); + exit; } # Displaying choices } else { @@ -200,25 +200,14 @@ if ($do_it) { -size => 1, -multiple => 0 ); - my @mime = ( C4::Context->preference("MIME") ); -# foreach my $mime (@mime){ -# warn "".$mime; -# } - my $CGIextChoice=CGI::scrolling_list( -name => 'MIME', -id => 'MIME', - -values => \@mime, + -values => ['CSV'], # FIXME translation -size => 1, -multiple => 0 ); - my @dels = ( C4::Context->preference("delimiter") ); - my $CGIsepChoice=CGI::scrolling_list( - -name => 'sep', - -id => 'sep', - -values => \@dels, - -size => 1, - -multiple => 0 ); + my $CGIsepChoice=GetDelimiterChoices; $template->param( CGIBorCat => $CGIBorCat, @@ -292,7 +281,7 @@ sub calculate { # warn "filtres ".@filters[1]; # warn "filtres ".@filters[2]; # warn "filtres ".@filters[3]; - $line = "issues.".$line if ($line=~/branchcode/) or ($line=~/timestamp/); + $line = "old_issues.".$line if ($line=~/branchcode/) or ($line=~/timestamp/); $line = "biblioitems.".$line if $line=~/itemtype/; $linefilter[0] = @$filters[0] if ($line =~ /timestamp/ ) ; @@ -311,7 +300,7 @@ sub calculate { $linefilter[0] = @$filters[12] if ($line =~ /sort2/ ) ; #warn "filtre lignes".$linefilter[0]." ".$linefilter[1]; # - $column = "issues.".$column if (($column=~/branchcode/) or ($column=~/timestamp/)); + $column = "old_issues.".$column if (($column=~/branchcode/) or ($column=~/timestamp/)); $column = "biblioitems.".$column if $column=~/itemtype/; my @colfilter ; $colfilter[0] = @$filters[0] if ($column =~ /timestamp/ ) ; @@ -354,29 +343,38 @@ sub calculate { } my $strsth; - $strsth .= "select distinctrow $linefield FROM `issues`,borrowers,biblioitems LEFT JOIN items ON (biblioitems.biblioitemnumber=items.biblioitemnumber) LEFT JOIN issuingrules ON (issuingrules.branchcode=issues.branchcode AND issuingrules.itemtype=biblioitems.itemtype AND issuingrules.categorycode=borrowers.categorycode) WHERE issues.itemnumber=items.itemnumber AND issues.borrowernumber=borrowers.borrowernumber and returndate is not null"; + $strsth .= "select distinctrow $linefield + FROM `old_issues` + LEFT JOIN borrowers ON borrowers.borrowernumber=old_issues.borrowernumber + LEFT JOIN items ON old_issues.itemnumber=items.itemnumber + LEFT JOIN biblioitems ON (biblioitems.biblioitemnumber=items.biblioitemnumber) + LEFT JOIN issuingrules ON + (issuingrules.branchcode=old_issues.branchcode + AND issuingrules.itemtype=biblioitems.itemtype + AND issuingrules.categorycode=borrowers.categorycode) + WHERE 1"; if (($line=~/timestamp/) or ($line=~/returndate/)){ if ($linefilter[1] and ($linefilter[0])){ - $strsth .= " and $line between '$linefilter[0]' and '$linefilter[1]' " ; + $strsth .= " AND $line BETWEEN '$linefilter[0]' AND '$linefilter[1]' " ; } elsif ($linefilter[1]) { - $strsth .= " and $line < \'$linefilter[1]\' " ; + $strsth .= " AND $line < \'$linefilter[1]\' " ; } elsif ($linefilter[0]) { - $strsth .= " and $line > \'$linefilter[0]\' " ; + $strsth .= " AND $line > \'$linefilter[0]\' " ; } if ($linefilter[2]){ - $strsth .= " and dayname($line) = '$linefilter[2]' " ; + $strsth .= " AND dayname($line) = '$linefilter[2]' " ; } if ($linefilter[3]){ - $strsth .= " and monthname($line) = '$linefilter[3]' " ; + $strsth .= " AND monthname($line) = '$linefilter[3]' " ; } } elsif ($linefilter[0]) { $linefilter[0] =~ s/\*/%/g; - $strsth .= " and $line LIKE '$linefilter[0]' " ; + $strsth .= " AND $line LIKE '$linefilter[0]' " ; } - $strsth .=" group by $linefield"; - $strsth .=" order by $lineorder"; - + $strsth .=" GROUP BY $linefield"; + $strsth .=" ORDER BY $lineorder"; + my $sth = $dbh->prepare( $strsth ); $sth->execute; @@ -416,29 +414,37 @@ sub calculate { } my $strsth2; - $strsth2 .= "select distinctrow $colfield FROM `issues`,borrowers,biblioitems LEFT JOIN items ON (biblioitems.biblioitemnumber=items.biblioitemnumber) LEFT JOIN issuingrules ON (issuingrules.branchcode=issues.branchcode AND issuingrules.itemtype=biblioitems.itemtype AND issuingrules.categorycode=borrowers.categorycode) WHERE issues.itemnumber=items.itemnumber AND issues.borrowernumber=borrowers.borrowernumber and returndate is not null"; + $strsth2 .= "SELECT distinctrow $colfield + FROM `old_issues` + LEFT JOIN borrowers ON borrowers.borrowernumber=old_issues.borrowernumber + LEFT JOIN items ON items.itemnumber=old_issues.itemnumber + LEFT JOIN biblioitems ON (biblioitems.biblioitemnumber=items.biblioitemnumber) + LEFT JOIN issuingrules ON + (issuingrules.branchcode=old_issues.branchcode + AND issuingrules.itemtype=biblioitems.itemtype + AND issuingrules.categorycode=borrowers.categorycode) + WHERE 1"; if (($column=~/timestamp/) or ($column=~/returndate/)){ if ($colfilter[1] and ($colfilter[0])){ - $strsth2 .= " and $column between '$colfilter[0]' and '$colfilter[1]' " ; + $strsth2 .= " AND $column BETWEEN '$colfilter[0]' AND '$colfilter[1]' " ; } elsif ($colfilter[1]) { - $strsth2 .= " and $column < '$colfilter[1]' " ; + $strsth2 .= " AND $column < '$colfilter[1]' " ; } elsif ($colfilter[0]) { - $strsth2 .= " and $column > '$colfilter[0]' " ; + $strsth2 .= " AND $column > '$colfilter[0]' " ; } if ($colfilter[2]){ - $strsth2 .= " and dayname($column) = '$colfilter[2]' " ; + $strsth2 .= " AND dayname($column) = '$colfilter[2]' " ; } if ($colfilter[3]){ - $strsth2 .= " and monthname($column) = '$colfilter[3]' " ; + $strsth2 .= " AND monthname($column) = '$colfilter[3]' " ; } } elsif ($colfilter[0]) { $colfilter[0] =~ s/\*/%/g; - $strsth2 .= " and $column LIKE '$colfilter[0]' " ; + $strsth2 .= " AND $column LIKE '$colfilter[0]' " ; } - $strsth2 .=" group by $colfield"; - $strsth2 .=" order by $colorder"; - warn "". $strsth2; + $strsth2 .=" GROUP BY $colfield"; + $strsth2 .=" ORDER BY $colorder"; my $sth2 = $dbh->prepare( $strsth2 ); if (( @colfilter ) and ($colfilter[1])){ @@ -483,22 +489,22 @@ sub calculate { # Processing average loanperiods $strcalc .= "SELECT $linefield, $colfield, "; - $strcalc .= " issuedate, returndate, timestamp, COUNT(*), date_due, issues.renewals, issuelength FROM `issues`,borrowers,biblioitems LEFT JOIN items ON (biblioitems.biblioitemnumber=items.biblioitemnumber) LEFT JOIN issuingrules ON (issuingrules.branchcode=issues.branchcode AND issuingrules.itemtype=biblioitems.itemtype AND issuingrules.categorycode=borrowers.categorycode) WHERE issues.itemnumber=items.itemnumber AND issues.borrowernumber=borrowers.borrowernumber AND returndate IS NOT NULL"; + $strcalc .= " issuedate, returndate, old_issues.timestamp, COUNT(*), date_due, old_issues.renewals, issuelength FROM `old_issues`,borrowers,biblioitems LEFT JOIN items ON (biblioitems.biblioitemnumber=items.biblioitemnumber) LEFT JOIN issuingrules ON (issuingrules.branchcode=branchcode AND issuingrules.itemtype=biblioitems.itemtype AND issuingrules.categorycode=categorycode) WHERE old_issues.itemnumber=items.itemnumber AND old_issues.borrowernumber=borrowers.borrowernumber"; @$filters[0]=~ s/\*/%/g if (@$filters[0]); - $strcalc .= " AND issues.timestamp > '" . @$filters[0] ."'" if ( @$filters[0] ); + $strcalc .= " AND old_issues.timestamp > '" . @$filters[0] ."'" if ( @$filters[0] ); @$filters[1]=~ s/\*/%/g if (@$filters[1]); - $strcalc .= " AND issues.timestamp < '" . @$filters[1] ."'" if ( @$filters[1] ); + $strcalc .= " AND old_issues.timestamp < '" . @$filters[1] ."'" if ( @$filters[1] ); @$filters[4]=~ s/\*/%/g if (@$filters[4]); - $strcalc .= " AND issues.returndate > '" . @$filters[4] ."'" if ( @$filters[4] ); + $strcalc .= " AND old_issues.returndate > '" . @$filters[4] ."'" if ( @$filters[4] ); @$filters[5]=~ s/\*/%/g if (@$filters[5]); - $strcalc .= " AND issues.returndate < '" . @$filters[5] ."'" if ( @$filters[5] ); + $strcalc .= " AND old_issues.returndate < '" . @$filters[5] ."'" if ( @$filters[5] ); @$filters[8]=~ s/\*/%/g if (@$filters[8]); $strcalc .= " AND borrowers.categorycode like '" . @$filters[8] ."'" if ( @$filters[8] ); @$filters[9]=~ s/\*/%/g if (@$filters[9]); $strcalc .= " AND biblioitems.itemtype like '" . @$filters[9] ."'" if ( @$filters[9] ); @$filters[10]=~ s/\*/%/g if (@$filters[10]); - $strcalc .= " AND issues.branchcode like '" . @$filters[10] ."'" if ( @$filters[10] ); + $strcalc .= " AND old_issues.branchcode like '" . @$filters[10] ."'" if ( @$filters[10] ); @$filters[11]=~ s/\*/%/g if (@$filters[11]); $strcalc .= " AND borrowers.sort1 like '" . @$filters[11] ."'" if ( @$filters[11] ); @$filters[12]=~ s/\*/%/g if (@$filters[12]); @@ -509,7 +515,6 @@ sub calculate { $strcalc .= " AND monthname(returndate) like '" . @$filters[6] ."'" if ( @$filters[6] ); $strcalc .= " group by $linefield, $colfield, issuedate, returndate order by $linefield, $colfield"; - warn "SQL :". $strcalc; my $dbcalc = $dbh->prepare($strcalc); $dbcalc->execute; @@ -525,9 +530,9 @@ sub calculate { while (my @data = $dbcalc->fetchrow) { my ($row, $col, $issuedate, $returndate, $weight)=@data; # warn "filling table $row / $col / $issuedate / $returndate /$weight"; - $emptycol=1 if ($col eq undef); - $col = "zzEMPTY" if ($col eq undef); - $row = "zzEMPTY" if ($row eq undef); + $emptycol=1 if (!defined($col)); + $col = "zzEMPTY" if (!defined($col)); + $row = "zzEMPTY" if (!defined($row)); # fill returndate to avoid an error with date calc (needed for all non returned issues) $returndate= join '-',Date::Calc::Today if $returndate eq '0000-00-00'; # DateCalc returns => 0:0:WK:DD:HH:MM:SS the weeks, days, hours, minutes, @@ -549,8 +554,23 @@ sub calculate { # and the number matches the number of columns my $colcount=0; foreach my $col ( @loopcol ) { - my $value =$table{$row}->{(($col->{coltitle} eq "NULL")or ($col->{coltitle} eq ""))?"zzEMPTY":$col->{coltitle}} / $wgttable{$row}->{(($col->{coltitle} eq "NULL")or ($col->{coltitle} eq ""))?"zzEMPTY":$col->{coltitle}} if ($table{$row}->{(($col->{coltitle} eq "NULL")or ($col->{coltitle} eq ""))?"zzEMPTY":$col->{coltitle}}); - + my $value; + if ($table{$row}->{ + ( ( $col->{coltitle} eq 'NULL' ) + or ( $col->{coltitle} eq q{} ) + ) ? 'zzEMPTY' : $col->{coltitle} + } + ) { + $value = $table{$row}->{ + ( ( $col->{coltitle} eq 'NULL' ) + or ( $col->{coltitle} eq q{} ) + ) ? 'zzEMPTY' : $col->{coltitle} + } / $wgttable{$row}->{ + ( ( $col->{coltitle} eq 'NULL' ) + or ( $col->{coltitle} eq q{} ) + ) ? 'zzEMPTY' : $col->{coltitle} + }; + } $table{$row}->{(($col->{coltitle} eq "NULL")or ($col->{coltitle} eq ""))?"zzEMPTY":$col->{coltitle}} = $value; $table{$row}->{totalrow}+=$value; #warn "row : $row col:$col $cnttable{$row}->{(($col->{coltitle} eq \"NULL\")or ($col->{coltitle} eq \"\"))?\"zzEMPTY\":$col->{coltitle}}"; @@ -558,12 +578,16 @@ sub calculate { push @loopcell, {value => ($value)?sprintf("%.2f",$value):0 } ; } #warn "row : $row colcount:$colcount"; - my $total = $table{$row}->{totalrow}/$colcount if ($colcount>0); - push @looprow,{ 'rowtitle' => ($row eq "zzEMPTY")?"NULL":$row, - 'loopcell' => \@loopcell, - 'hilighted' => ($hilighted >0), - 'totalrow' => ($total)?sprintf("%.2f",$total):0 - }; + my $total; + if ( $colcount > 0 ) { + $total = $table{$row}->{totalrow} / $colcount; + } + push @looprow, + { 'rowtitle' => ( $row eq "zzEMPTY" ) ? "NULL" : $row, + 'loopcell' => \@loopcell, + 'hilighted' => ( $hilighted > 0 ), + 'totalrow' => ($total) ? sprintf( "%.2f", $total ) : 0 + }; $hilighted = -$hilighted; } #