[Koha-cvs] CVS: koha/reports issues_stats.pl,1.2,1.3 borrowers_stats.pl,1.6,1.7 acquisitions_stats.pl,1.3,1.4
Henri-Damien LAURENT
hdl at users.sourceforge.net
Wed Mar 9 15:21:59 CET 2005
- Previous message: [Koha-cvs] CVS: koha/koha-tmpl/intranet-tmpl/default/en/reports catalogue_stats.tmpl,1.4,1.5
- Next message: [Koha-cvs] CVS: koha/koha-tmpl/intranet-tmpl/default/en/reports issues_stats.tmpl,1.1,1.2 catalogue_stats.tmpl,1.5,1.6 borrowers_stats.tmpl,1.4,1.5 acquisitions_stats.tmpl,1.2,1.3
- Messages sorted by:
[ date ]
[ thread ]
[ subject ]
[ author ]
Update of /cvsroot/koha/koha/reports
In directory sc8-pr-cvs1.sourceforge.net:/tmp/cvs-serv29733/reports
Modified Files:
issues_stats.pl borrowers_stats.pl acquisitions_stats.pl
Log Message:
Including null values to statistics reports
Index: issues_stats.pl
===================================================================
RCS file: /cvsroot/koha/koha/reports/issues_stats.pl,v
retrieving revision 1.2
retrieving revision 1.3
diff -C2 -r1.2 -r1.3
*** issues_stats.pl 4 Mar 2005 13:48:16 -0000 1.2
--- issues_stats.pl 9 Mar 2005 14:21:56 -0000 1.3
***************
*** 30,33 ****
--- 30,34 ----
use C4::Interface::CGI::Output;
use C4::Circulation::Circ2;
+ use Date::Manip;
=head1 NAME
***************
*** 436,488 ****
# 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 " ;
! }
! $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 );
!
! $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
foreach my $col ( @loopcol ) {
! push @loopcell, {value => $table{$row}->{$col->{coltitle}}} ;
}
! push @looprow,{ 'rowtitle' => $row,
'loopcell' => \@loopcell,
! 'hilighted' => 1 ,
'totalrow' => $table{$row}->{totalrow}
};
--- 437,567 ----
# preparing calculation
my $strcalc ;
! 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;
! }
! }
! 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 ) {
! 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 ) {
! 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}
};
***************
*** 490,501 ****
}
! # 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};
}
! # warn "summ for column ".$col->{coltitle}." = ".$total;
push @loopfooter, {'totalcol' => $total};
}
--- 569,580 ----
}
! # 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};
}
Index: borrowers_stats.pl
===================================================================
RCS file: /cvsroot/koha/koha/reports/borrowers_stats.pl,v
retrieving revision 1.6
retrieving revision 1.7
diff -C2 -r1.6 -r1.7
*** borrowers_stats.pl 4 Mar 2005 13:48:16 -0000 1.6
--- borrowers_stats.pl 9 Mar 2005 14:21:56 -0000 1.7
***************
*** 262,267 ****
if ($celvalue) {
$cell{rowtitle} = $celvalue;
! } else {
! $cell{rowtitle} = "";
}
$cell{totalrow} = 0;
--- 262,267 ----
if ($celvalue) {
$cell{rowtitle} = $celvalue;
! # } else {
! # $cell{rowtitle} = "";
}
$cell{totalrow} = 0;
***************
*** 294,299 ****
my %cell;
my %ft;
! $cell{coltitle} = $celvalue;
! $ft{totalcol} = 0;
push @loopcol, \%cell;
}
--- 294,300 ----
my %cell;
my %ft;
! if ($celvalue) {
! $cell{coltitle} = $celvalue;
! }
push @loopcol, \%cell;
}
***************
*** 316,320 ****
# preparing calculation
! my $strcalc .= "SELECT $linefield, $colfield, count( * ) FROM borrowers WHERE $line is not null AND $column is not null";
@$filters[0]=~ s/\*/%/g if (@$filters[0]);
$strcalc .= " AND categorycode like '" . @$filters[0] ."'" if ( @$filters[0] );
--- 317,321 ----
# preparing calculation
! 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] );
***************
*** 331,350 ****
$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 ( keys %table ) {
my @loopcell;
#@loopcol ensures the order for columns is common with column titles
foreach my $col ( @loopcol ) {
! push @loopcell, {value => $table{$row}->{$col->{coltitle}}} ;
}
! push @looprow,{ 'rowtitle' => $row,
'loopcell' => \@loopcell,
! 'hilighted' => 1 ,
'totalrow' => $table{$row}->{totalrow}
};
--- 332,361 ----
$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 ) {
! 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}
};
***************
*** 354,360 ****
foreach my $col ( @loopcol ) {
my $total=0;
! foreach my $row ( @loopline ) {
! $total += $table{$row->{rowtitle}}->{$col->{coltitle}};
}
push @loopfooter, {'totalcol' => $total};
}
--- 365,373 ----
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};
}
Index: acquisitions_stats.pl
===================================================================
RCS file: /cvsroot/koha/koha/reports/acquisitions_stats.pl,v
retrieving revision 1.3
retrieving revision 1.4
diff -C2 -r1.3 -r1.4
*** acquisitions_stats.pl 4 Mar 2005 13:48:16 -0000 1.3
--- acquisitions_stats.pl 9 Mar 2005 14:21:56 -0000 1.4
***************
*** 329,334 ****
if ($celvalue) {
$cell{rowtitle} = $celvalue;
! } else {
! $cell{rowtitle} = "";
}
$cell{totalrow} = 0;
--- 329,334 ----
if ($celvalue) {
$cell{rowtitle} = $celvalue;
! # } else {
! # $cell{rowtitle} = "";
}
$cell{totalrow} = 0;
***************
*** 393,400 ****
while (my ($celvalue) = $sth2->fetchrow) {
my %cell;
! my %ft;
# warn "coltitle :".$celvalue;
! $cell{coltitle} = $celvalue;
! $ft{totalcol} = 0;
push @loopcol, \%cell;
}
--- 393,400 ----
while (my ($celvalue) = $sth2->fetchrow) {
my %cell;
! if ($celvalue){
# warn "coltitle :".$celvalue;
! $cell{coltitle} = $celvalue;
! }
push @loopcol, \%cell;
}
***************
*** 421,425 ****
$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 ";
@$filters[0]=~ s/\*/%/g if (@$filters[0]);
--- 421,425 ----
$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) ";
@$filters[0]=~ s/\*/%/g if (@$filters[0]);
***************
*** 443,463 ****
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
foreach my $col ( @loopcol ) {
! push @loopcell, {value => $table{$row}->{$col->{coltitle}}} ;
}
! push @looprow,{ 'rowtitle' => $row,
'loopcell' => \@loopcell,
! 'hilighted' => 1 ,
'totalrow' => $table{$row}->{totalrow}
};
--- 443,473 ----
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 ) {
! 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}
};
***************
*** 465,476 ****
}
! # 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};
}
! # warn "summ for column ".$col->{coltitle}." = ".$total;
push @loopfooter, {'totalcol' => $total};
}
--- 475,486 ----
}
! # 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};
}
- Previous message: [Koha-cvs] CVS: koha/koha-tmpl/intranet-tmpl/default/en/reports catalogue_stats.tmpl,1.4,1.5
- Next message: [Koha-cvs] CVS: koha/koha-tmpl/intranet-tmpl/default/en/reports issues_stats.tmpl,1.1,1.2 catalogue_stats.tmpl,1.5,1.6 borrowers_stats.tmpl,1.4,1.5 acquisitions_stats.tmpl,1.2,1.3
- Messages sorted by:
[ date ]
[ thread ]
[ subject ]
[ author ]
More information about the Koha-cvs
mailing list