use C4::Koha;
use C4::Interface::CGI::Output;
use C4::Circulation::Circ2;
+use Date::Manip;
=head1 NAME
=cut
+
+
my $input = new CGI;
my $do_it=$input->param('do_it');
my $fullreportname = "reports/issues_stats.tmpl";
flagsrequired => {editcatalogue => 1},
debug => 1,
});
-$template->param(do_it => $do_it);
+$template->param(do_it => $do_it,
+ intranetcolorstylesheet => C4::Context->preference("intranetcolorstylesheet"),
+ intranetstylesheet => C4::Context->preference("intranetstylesheet"),
+ IntranetNav => C4::Context->preference("IntranetNav"),
+ );
if ($do_it) {
# Displaying results
my $results = calculate($line, $column, $podsp, $type, $daysel, $monthsel, $calc, \@filters);
$hassort1 =1 if ($value);
push @select, $value;
}
- my $branches=getbranches();
+ my $branches=GetBranches();
my @select_branch;
my %select_branches;
push @select_branch,"";
push @loopfilter,{crit=>"Issue|Return ",filter=>$type};
push @loopfilter,{crit=>"Display by ",filter=>$dsp} if ($dsp);
push @loopfilter,{crit=>"Select Day ",filter=>$daysel} if ($daysel);
- push @loopfilter,{crit=>"Select Month ",filter=>$daysel} if ($monthsel);
+ push @loopfilter,{crit=>"Select Month ",filter=>$monthsel} if ($monthsel);
my @linefilter;
} else {
$linefield .= $line;
}
-
+ my $lineorder = $linefield;
+ $lineorder = "weekday($line)" if $linefield =~ /dayname/;
+ $lineorder = "month($line)" if $linefield =~ "^month";
+ $lineorder = $linefield if (not ($linefield =~ "^month") and not($linefield =~ /dayname/));
+
my $strsth;
$strsth .= "select distinctrow $linefield from statistics, borrowers where (statistics.borrowernumber=borrowers.borrowernumber) and $line is not null ";
$strsth .= " and $line LIKE ? " ;
}
$strsth .=" group by $linefield";
- $strsth .=" order by $linefield";
-# warn "". $strsth;
+ $strsth .=" order by $lineorder";
+ warn "". $strsth;
my $sth = $dbh->prepare( $strsth );
if (( @linefilter ) and ($linefilter[1])){
}
# 2nd, loop cols.
- my $colfield;
+ my $colfield;
+ my $colorder;
if (($column =~/datetime/) and ($dsp == 1)) {
#Display by day
$colfield .="dayname($column)";
} else {
$colfield .= $column;
}
+ $colorder = "weekday($line)" if $colfield =~ "^dayname";
+ $colorder = "month($line)" if $colfield =~ "^month";
+ $colorder = $colfield if (not ($colfield =~ "^month") and not($colfield =~ "^dayname"));
my $strsth2;
$strsth2 .= "select distinctrow $colfield from statistics, borrowers where (statistics.borrowernumber=borrowers.borrowernumber) and $column is not null ";
$strsth2 .= " and $column LIKE ? " ;
}
$strsth2 .=" group by $colfield";
- $strsth2 .=" order by $colfield";
+ $strsth2 .=" order by $colorder";
# warn "". $strsth2;
my $sth2 = $dbh->prepare( $strsth2 );
# preparing calculation
my $strcalc ;
+
$strcalc .= "SELECT $linefield, $colfield, ";
$strcalc .= "COUNT( * ) " if ($process ==1);
+ if ($process ==2){
+ $strcalc .= "(COUNT(DISTINCT borrowers.borrowernumber))" ;
+ }
if ($process ==3){
- my $rqbookcount = $dbh->prepare("SELECT count(*) FROM items");
- $rqbookcount->execute;
- my ($bookcount) = $rqbookcount->fetchrow;
- $strcalc .= "100*(COUNT(itemnumber))/ $bookcount " ;
+ $strcalc .= "(COUNT(DISTINCT issues.itemnumber))" ;
+ }
+ if ($process ==4){
+ my $rqbookcount = $dbh->prepare("SELECT count(*) FROM items");
+ $rqbookcount->execute;
+ my ($bookcount) = $rqbookcount->fetchrow;
+ $strcalc .= "100*(COUNT(DISTINCT issues.itemnumber))/ $bookcount " ;
}
- $strcalc .= "FROM statistics,borrowers where (statistics.borrowernumber=borrowers.borrowernumber) and $column is not null and $line is not null ";
+ $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] );
$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;
+ $strcalc .= " group by $linefield, $colfield order by $lineorder,$colorder";
+ 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;
}
-
- foreach my $row ( sort keys %table ) {
+ push @loopcol,{coltitle => "NULL"} if ($emptycol);
+
+ foreach my $row (@loopline) {
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->{rowtitle} eq "NULL")?"zzEMPTY":$row->{rowtitle}}->{($col->{coltitle} eq "NULL")?"zzEMPTY":$col->{coltitle}};
+ push @loopcell, {value => $value } ;
}
- push @looprow,{ 'rowtitle' => $row,
- 'loopcell' => \@loopcell,
- 'hilighted' => 1 ,
- 'totalrow' => $table{$row}->{totalrow}
- };
+ push @looprow,{ 'rowtitle' => ($row->{rowtitle} eq "NULL")?"zzEMPTY":$row->{rowtitle},
+ 'loopcell' => \@loopcell,
+ 'hilighted' => ($hilighted >0),
+ 'totalrow' => $table{($row->{rowtitle} eq "NULL")?"zzEMPTY":$row->{rowtitle}}->{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};
}
return \@mainloop;
}
-1;
\ No newline at end of file
+1;