From 7a97a934ea035821f77d464e9e351f36f7209bbe Mon Sep 17 00:00:00 2001 From: hdl Date: Wed, 9 Mar 2005 14:21:56 +0000 Subject: [PATCH] Including null values to statistics reports --- .../en/reports/acquisitions_stats.tmpl | 6 +- .../default/en/reports/borrowers_stats.tmpl | 9 +- .../default/en/reports/catalogue_stats.tmpl | 14 +- .../default/en/reports/issues_stats.tmpl | 6 +- reports/acquisitions_stats.pl | 40 +++-- reports/borrowers_stats.pl | 37 ++-- reports/issues_stats.pl | 165 +++++++++++++----- 7 files changed, 191 insertions(+), 86 deletions(-) diff --git a/koha-tmpl/intranet-tmpl/default/en/reports/acquisitions_stats.tmpl b/koha-tmpl/intranet-tmpl/default/en/reports/acquisitions_stats.tmpl index a9518f9bd2..e8bad6167a 100644 --- a/koha-tmpl/intranet-tmpl/default/en/reports/acquisitions_stats.tmpl +++ b/koha-tmpl/intranet-tmpl/default/en/reports/acquisitions_stats.tmpl @@ -11,7 +11,7 @@
- +
@@ -25,7 +25,9 @@
/ - + +   + diff --git a/koha-tmpl/intranet-tmpl/default/en/reports/borrowers_stats.tmpl b/koha-tmpl/intranet-tmpl/default/en/reports/borrowers_stats.tmpl index 378c92a15d..6a45b88af8 100644 --- a/koha-tmpl/intranet-tmpl/default/en/reports/borrowers_stats.tmpl +++ b/koha-tmpl/intranet-tmpl/default/en/reports/borrowers_stats.tmpl @@ -10,7 +10,7 @@

- +
@@ -24,8 +24,11 @@ + + +   + + - - - - - +
/ - - diff --git a/koha-tmpl/intranet-tmpl/default/en/reports/catalogue_stats.tmpl b/koha-tmpl/intranet-tmpl/default/en/reports/catalogue_stats.tmpl index 307422acec..a038f5e646 100644 --- a/koha-tmpl/intranet-tmpl/default/en/reports/catalogue_stats.tmpl +++ b/koha-tmpl/intranet-tmpl/default/en/reports/catalogue_stats.tmpl @@ -24,15 +24,11 @@ - - -   - + +   + + diff --git a/koha-tmpl/intranet-tmpl/default/en/reports/issues_stats.tmpl b/koha-tmpl/intranet-tmpl/default/en/reports/issues_stats.tmpl index db8c10ff2b..e6acdf5a24 100644 --- a/koha-tmpl/intranet-tmpl/default/en/reports/issues_stats.tmpl +++ b/koha-tmpl/intranet-tmpl/default/en/reports/issues_stats.tmpl @@ -11,7 +11,7 @@
- +
@@ -25,7 +25,9 @@
/ - + +   + diff --git a/reports/acquisitions_stats.pl b/reports/acquisitions_stats.pl index a54ff87cc5..abb8f18221 100755 --- a/reports/acquisitions_stats.pl +++ b/reports/acquisitions_stats.pl @@ -328,8 +328,8 @@ sub calculate { my %cell; if ($celvalue) { $cell{rowtitle} = $celvalue; - } else { - $cell{rowtitle} = ""; +# } else { +# $cell{rowtitle} = ""; } $cell{totalrow} = 0; push @loopline, \%cell; @@ -392,10 +392,10 @@ sub calculate { while (my ($celvalue) = $sth2->fetchrow) { my %cell; - my %ft; + if ($celvalue){ # warn "coltitle :".$celvalue; - $cell{coltitle} = $celvalue; - $ft{totalcol} = 0; + $cell{coltitle} = $celvalue; + } push @loopcol, \%cell; } # warn "fin des titres colonnes"; @@ -420,7 +420,7 @@ sub calculate { $strcalc .= "SELECT $linefield, $colfield, "; $strcalc .= "COUNT( aqorders.ordernumber ) " if ($process ==1); $strcalc .= "SUM( aqorders.quantity * aqorders.listprice ) " if ($process ==2); - $strcalc .= "FROM aqorders, aqbasket,aqorderbreakdown left join aqorderdelivery on (aqorders.ordernumber =aqorderdelivery.ordernumber ) left join aqbooksellers on (aqbasket.booksellerid=aqbooksellers.id) where (aqorders.basketno=aqbasket.basketno) and (aqorderbreakdown.ordernumber=aqorders.ordernumber) and $column is not null and $line is not null "; + $strcalc .= "FROM aqorders, aqbasket,aqorderbreakdown left join aqorderdelivery on (aqorders.ordernumber =aqorderdelivery.ordernumber ) left join aqbooksellers on (aqbasket.booksellerid=aqbooksellers.id) where (aqorders.basketno=aqbasket.basketno) and (aqorderbreakdown.ordernumber=aqorders.ordernumber) "; @$filters[0]=~ s/\*/%/g if (@$filters[0]); $strcalc .= " AND aqbasket.closedate > '" . @$filters[0] ."'" if ( @$filters[0] ); @@ -442,36 +442,46 @@ sub calculate { warn "". $strcalc; my $dbcalc = $dbh->prepare($strcalc); $dbcalc->execute; + # warn "filling table"; + my $emptycol; while (my ($row, $col, $value) = $dbcalc->fetchrow) { -# warn "filling table $row / $col / $value "; - $table{$row}->{$col}=$value; +# warn "filling table $row / $col / $value "; + $emptycol = 1 if ($col eq undef); + $col = "zzEMPTY" if ($col eq undef); + $row = "zzEMPTY" if ($row eq undef); + + $table{$row}->{$col}+=$value; $table{$row}->{totalrow}+=$value; $grantotal += $value; } + + push @loopcol,{coltitle => "NULL"} if ($emptycol); foreach my $row ( sort keys %table ) { my @loopcell; #@loopcol ensures the order for columns is common with column titles + # and the number matches the number of columns foreach my $col ( @loopcol ) { - push @loopcell, {value => $table{$row}->{$col->{coltitle}}} ; + my $value =$table{$row}->{($col->{coltitle} eq "NULL")?"zzEMPTY":$col->{coltitle}}; + push @loopcell, {value => $value } ; } - push @looprow,{ 'rowtitle' => $row, + push @looprow,{ 'rowtitle' => ($row eq "zzEMPTY")?"NULL":$row, 'loopcell' => \@loopcell, - 'hilighted' => 1 , + 'hilighted' => ($hilighted >0), 'totalrow' => $table{$row}->{totalrow} }; $hilighted = -$hilighted; } -# warn "footer processing"; +# warn "footer processing"; foreach my $col ( @loopcol ) { my $total=0; foreach my $row ( @looprow ) { - $total += $table{$row->{rowtitle}}->{$col->{coltitle}}; -# warn "value added ".$table{$row->{rowtitle}}->{$col->{coltitle}}. "for line ".$row->{rowtitle}; + $total += $table{($row->{rowtitle} eq "NULL")?"zzEMPTY":$row->{rowtitle}}->{($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; +# warn "summ for column ".$col->{coltitle}." = ".$total; push @loopfooter, {'totalcol' => $total}; } diff --git a/reports/borrowers_stats.pl b/reports/borrowers_stats.pl index 9fce669651..1d05600ab0 100755 --- a/reports/borrowers_stats.pl +++ b/reports/borrowers_stats.pl @@ -261,8 +261,8 @@ sub calculate { my %cell; if ($celvalue) { $cell{rowtitle} = $celvalue; - } else { - $cell{rowtitle} = ""; +# } else { +# $cell{rowtitle} = ""; } $cell{totalrow} = 0; push @loopline, \%cell; @@ -293,8 +293,9 @@ sub calculate { while (my ($celvalue) = $sth2->fetchrow) { my %cell; my %ft; - $cell{coltitle} = $celvalue; - $ft{totalcol} = 0; + if ($celvalue) { + $cell{coltitle} = $celvalue; + } push @loopcol, \%cell; } @@ -315,7 +316,7 @@ sub calculate { } # preparing calculation - my $strcalc .= "SELECT $linefield, $colfield, count( * ) FROM borrowers WHERE $line is not null AND $column is not null"; + my $strcalc .= "SELECT $linefield, $colfield, count( * ) FROM borrowers WHERE 1 "; @$filters[0]=~ s/\*/%/g if (@$filters[0]); $strcalc .= " AND categorycode like '" . @$filters[0] ."'" if ( @$filters[0] ); @$filters[1]=~ s/\*/%/g if (@$filters[1]); @@ -330,22 +331,32 @@ sub calculate { my $dbcalc = $dbh->prepare($strcalc); $dbcalc->execute; # warn "filling table"; + + my $emptycol; while (my ($row, $col, $value) = $dbcalc->fetchrow) { # warn "filling table $row / $col / $value "; - $table{$row}->{$col}=$value; + $emptycol = 1 if ($col eq undef); + $col = "zzEMPTY" if ($col eq undef); + $row = "zzEMPTY" if ($row eq undef); + + $table{$row}->{$col}+=$value; $table{$row}->{totalrow}+=$value; $grantotal += $value; } - foreach my $row ( keys %table ) { + push @loopcol,{coltitle => "NULL"} if ($emptycol); + + foreach my $row ( sort keys %table ) { my @loopcell; #@loopcol ensures the order for columns is common with column titles + # and the number matches the number of columns foreach my $col ( @loopcol ) { - push @loopcell, {value => $table{$row}->{$col->{coltitle}}} ; + my $value =$table{$row}->{($col->{coltitle} eq "NULL")?"zzEMPTY":$col->{coltitle}}; + push @loopcell, {value => $value } ; } - push @looprow,{ 'rowtitle' => $row, + push @looprow,{ 'rowtitle' => ($row eq "zzEMPTY")?"NULL":$row, 'loopcell' => \@loopcell, - 'hilighted' => 1 , + 'hilighted' => ($hilighted >0), 'totalrow' => $table{$row}->{totalrow} }; $hilighted = -$hilighted; @@ -353,9 +364,11 @@ sub calculate { foreach my $col ( @loopcol ) { my $total=0; - foreach my $row ( @loopline ) { - $total += $table{$row->{rowtitle}}->{$col->{coltitle}}; + foreach my $row ( @looprow ) { + $total += $table{($row->{rowtitle} eq "NULL")?"zzEMPTY":$row->{rowtitle}}->{($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}; } diff --git a/reports/issues_stats.pl b/reports/issues_stats.pl index 5da297ad95..b9fe3638f6 100755 --- a/reports/issues_stats.pl +++ b/reports/issues_stats.pl @@ -29,6 +29,7 @@ use C4::Output; use C4::Koha; use C4::Interface::CGI::Output; use C4::Circulation::Circ2; +use Date::Manip; =head1 NAME @@ -435,68 +436,146 @@ sub calculate { # preparing calculation my $strcalc ; - $strcalc .= "SELECT $linefield, $colfield, "; - $strcalc .= "COUNT( * ) " if ($process ==1); - if ($process ==3){ - my $rqbookcount = $dbh->prepare("SELECT count(*) FROM items"); - $rqbookcount->execute; - my ($bookcount) = $rqbookcount->fetchrow; - $strcalc .= "100*(COUNT(itemnumber))/ $bookcount " ; + if ($process ==2) { + # Processing average loanperiods + $strcalc .= "SELECT $linefield, $colfield, "; + $strcalc .= " DATE_SUB(date_due, INTERVAL CAST(issues.renewals AS SIGNED INTEGER) * (CAST(issues.renewals AS SIGNED INTEGER)+1) DAY) AS issuedate, returndate, 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 "; + + @$filters[0]=~ s/\*/%/g if (@$filters[0]); + $strcalc .= " AND issues.returndate > '" . @$filters[0] ."'" if ( @$filters[0] ); + @$filters[1]=~ s/\*/%/g if (@$filters[1]); + $strcalc .= " AND issues.returndate < '" . @$filters[1] ."'" if ( @$filters[1] ); + @$filters[2]=~ s/\*/%/g if (@$filters[2]); + $strcalc .= " AND borrowers.categorycode like '" . @$filters[2] ."'" if ( @$filters[2] ); + @$filters[3]=~ s/\*/%/g if (@$filters[3]); + $strcalc .= " AND biblioitems.itemtype like '" . @$filters[3] ."'" if ( @$filters[3] ); + @$filters[4]=~ s/\*/%/g if (@$filters[4]); + $strcalc .= " AND issues.branchcode like '" . @$filters[4] ."'" if ( @$filters[4] ); + @$filters[5]=~ s/\*/%/g if (@$filters[5]); + $strcalc .= " AND borrowers.sort1 like '" . @$filters[5] ."'" if ( @$filters[5] ); + @$filters[6]=~ s/\*/%/g if (@$filters[6]); + $strcalc .= " AND borrowers.sort2 like '" . @$filters[6] ."'" if ( @$filters[6] ); + $strcalc .= " AND dayname(timestamp) like '" . $daysel ."'" if ( $daysel ); + $strcalc .= " AND monthname(timestamp) like '" . $monthsel ."'" if ( $monthsel ); + + $strcalc .= " group by issuedate, returndate, $linefield, $colfield order by $linefield,$colfield"; + + my $dbcalc = $dbh->prepare($strcalc); + $dbcalc->execute; + # warn "filling table"; + my $emptycol; + my $issues_count; + while (my ($row, $col, $issuedate, $returndate) = $dbcalc->fetchrow) { + # warn "filling table $row / $col / $value "; + $emptycol = 1 if ($col eq undef); + $col = "zzEMPTY" if ($col eq undef); + $row = "zzEMPTY" if ($row eq undef); + + $table{$row}->{$col}+=$value; + $table{$row}->{totalrow}+=$value; + $grantotal += $value; + } } - $strcalc .= "FROM statistics,borrowers where (statistics.borrowernumber=borrowers.borrowernumber) and $column is not null and $line is not null "; - - @$filters[0]=~ s/\*/%/g if (@$filters[0]); - $strcalc .= " AND statistics.datetime > '" . @$filters[0] ."'" if ( @$filters[0] ); - @$filters[1]=~ s/\*/%/g if (@$filters[1]); - $strcalc .= " AND statistics.datetime < '" . @$filters[1] ."'" if ( @$filters[1] ); - @$filters[2]=~ s/\*/%/g if (@$filters[2]); - $strcalc .= " AND borrowers.categorycode like '" . @$filters[2] ."'" if ( @$filters[2] ); - @$filters[3]=~ s/\*/%/g if (@$filters[3]); - $strcalc .= " AND statistics.itemtype like '" . @$filters[3] ."'" if ( @$filters[3] ); - @$filters[4]=~ s/\*/%/g if (@$filters[4]); - $strcalc .= " AND statistics.branch like '" . @$filters[4] ."'" if ( @$filters[4] ); - @$filters[5]=~ s/\*/%/g if (@$filters[5]); - $strcalc .= " AND borrowers.sort1 like '" . @$filters[5] ."'" if ( @$filters[5] ); - @$filters[6]=~ s/\*/%/g if (@$filters[6]); - $strcalc .= " AND borrowers.sort2 like '" . @$filters[6] ."'" if ( @$filters[6] ); - $strcalc .= " AND dayname(datetime) like '" . $daysel ."'" if ( $daysel ); - $strcalc .= " AND monthname(datetime) like '" . $monthsel ."'" if ( $monthsel ); - $strcalc .= " AND statistics.type like '" . $type ."'" if ( $type ); + push @loopcol,{coltitle => "NULL"} if ($emptycol); - $strcalc .= " group by $linefield, $colfield order by $linefield,$colfield"; -# warn "". $strcalc; - my $dbcalc = $dbh->prepare($strcalc); - $dbcalc->execute; -# warn "filling table"; - while (my ($row, $col, $value) = $dbcalc->fetchrow) { -# warn "filling table $row / $col / $value "; - $table{$row}->{$col}=$value; - $table{$row}->{totalrow}+=$value; - $grantotal += $value; + foreach my $row ( sort keys %table ) { + my @loopcell; + #@loopcol ensures the order for columns is common with column titles + # and the number matches the number of columns + foreach my $col ( @loopcol ) { + my $value =$table{$row}->{($col->{coltitle} eq "NULL")?"zzEMPTY":$col->{coltitle}}; + push @loopcell, {value => $value } ; + } + push @looprow,{ 'rowtitle' => ($row eq "zzEMPTY")?"NULL":$row, + 'loopcell' => \@loopcell, + 'hilighted' => ($hilighted >0), + 'totalrow' => $table{$row}->{totalrow} + }; + $hilighted = -$hilighted; + } + +# warn "footer processing"; + foreach my $col ( @loopcol ) { + my $total=0; + foreach my $row ( @looprow ) { + $total += $table{($row->{rowtitle} eq "NULL")?"zzEMPTY":$row->{rowtitle}}->{($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}; + + }else { + $strcalc .= "SELECT $linefield, $colfield, "; + $strcalc .= "COUNT( * ) " if ($process ==1); + if ($process ==3){ + my $rqbookcount = $dbh->prepare("SELECT count(*) FROM items"); + $rqbookcount->execute; + my ($bookcount) = $rqbookcount->fetchrow; + $strcalc .= "100*(COUNT(itemnumber))/ $bookcount " ; + } + $strcalc .= "FROM statistics,borrowers where (statistics.borrowernumber=borrowers.borrowernumber) "; + + @$filters[0]=~ s/\*/%/g if (@$filters[0]); + $strcalc .= " AND statistics.datetime > '" . @$filters[0] ."'" if ( @$filters[0] ); + @$filters[1]=~ s/\*/%/g if (@$filters[1]); + $strcalc .= " AND statistics.datetime < '" . @$filters[1] ."'" if ( @$filters[1] ); + @$filters[2]=~ s/\*/%/g if (@$filters[2]); + $strcalc .= " AND borrowers.categorycode like '" . @$filters[2] ."'" if ( @$filters[2] ); + @$filters[3]=~ s/\*/%/g if (@$filters[3]); + $strcalc .= " AND statistics.itemtype like '" . @$filters[3] ."'" if ( @$filters[3] ); + @$filters[4]=~ s/\*/%/g if (@$filters[4]); + $strcalc .= " AND statistics.branch like '" . @$filters[4] ."'" if ( @$filters[4] ); + @$filters[5]=~ s/\*/%/g if (@$filters[5]); + $strcalc .= " AND borrowers.sort1 like '" . @$filters[5] ."'" if ( @$filters[5] ); + @$filters[6]=~ s/\*/%/g if (@$filters[6]); + $strcalc .= " AND borrowers.sort2 like '" . @$filters[6] ."'" if ( @$filters[6] ); + $strcalc .= " AND dayname(datetime) like '" . $daysel ."'" if ( $daysel ); + $strcalc .= " AND monthname(datetime) like '" . $monthsel ."'" if ( $monthsel ); + $strcalc .= " AND statistics.type like '" . $type ."'" if ( $type ); + + $strcalc .= " group by $linefield, $colfield order by $linefield,$colfield"; + # warn "". $strcalc; + my $dbcalc = $dbh->prepare($strcalc); + $dbcalc->execute; + # warn "filling table"; + my $emptycol; + while (my ($row, $col, $value) = $dbcalc->fetchrow) { + # warn "filling table $row / $col / $value "; + $emptycol = 1 if ($col eq undef); + $col = "zzEMPTY" if ($col eq undef); + $row = "zzEMPTY" if ($row eq undef); + + $table{$row}->{$col}+=$value; + $table{$row}->{totalrow}+=$value; + $grantotal += $value; + } } + push @loopcol,{coltitle => "NULL"} if ($emptycol); foreach my $row ( sort keys %table ) { my @loopcell; #@loopcol ensures the order for columns is common with column titles + # and the number matches the number of columns foreach my $col ( @loopcol ) { - push @loopcell, {value => $table{$row}->{$col->{coltitle}}} ; + my $value =$table{$row}->{($col->{coltitle} eq "NULL")?"zzEMPTY":$col->{coltitle}}; + push @loopcell, {value => $value } ; } - push @looprow,{ 'rowtitle' => $row, + push @looprow,{ 'rowtitle' => ($row eq "zzEMPTY")?"NULL":$row, 'loopcell' => \@loopcell, - 'hilighted' => 1 , + 'hilighted' => ($hilighted >0), 'totalrow' => $table{$row}->{totalrow} }; $hilighted = -$hilighted; } -# warn "footer processing"; +# warn "footer processing"; foreach my $col ( @loopcol ) { my $total=0; foreach my $row ( @looprow ) { - $total += $table{$row->{rowtitle}}->{$col->{coltitle}}; -# warn "value added ".$table{$row->{rowtitle}}->{$col->{coltitle}}. "for line ".$row->{rowtitle}; + $total += $table{($row->{rowtitle} eq "NULL")?"zzEMPTY":$row->{rowtitle}}->{($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; +# warn "summ for column ".$col->{coltitle}." = ".$total; push @loopfooter, {'totalcol' => $total}; } -- 2.39.5