the new "includes" features, for personalized templates. Look at koha-devel, i'll...

[koha-ffzg.git] / reports / borrowers_stats.pl

diff --git a/reports/borrowers_stats.pl b/reports/borrowers_stats.pl
index 797209c..5d37d55 100755 (executable)
--- a/reports/borrowers_stats.pl
+++ b/reports/borrowers_stats.pl
@@ -27,6 +27,7 @@ use HTML::Template;
 use C4::Search;
 use C4::Output;
 use C4::Koha;
+use C4::Acquisition;
 use C4::Interface::CGI::Output;
 use C4::Circulation::Circ2;
 
@@ -70,7 +71,9 @@ if ($do_it) {
                output_html_with_http_headers $input, $cookie, $template->output;
                exit(1);
        } else {
-               print $input->header(-type => 'application/vnd.sun.xml.calc', -name=>"$basename.csv" );
+               print $input->header(-type => 'application/vnd.sun.xml.calc',
+                                                        -name=>"$basename.csv",
+                                                        -attachment=>"$basename.csv");
                my $cols = @$results[0]->{loopcol};
                my $lines = @$results[0]->{looprow};
                my $sep;
@@ -79,7 +82,7 @@ if ($do_it) {
                foreach my $col ( @$cols ) {
                        print $col->{coltitle}.$sep;
                }
-               print "\n";
+               print "Total\n";
                foreach my $line ( @$lines ) {
                        my $x = $line->{loopcell};
                        print $line->{rowtitle}.$sep;
@@ -102,7 +105,7 @@ if ($do_it) {
        my @values;
        my %labels;
        my $req;
-       $req = $dbh->prepare( "select categorycode, description from categories");
+       $req = $dbh->prepare( "select categorycode, description from categories order by description");
        $req->execute;
        my %select_catcode;
        my @select_catcode;
@@ -119,7 +122,23 @@ if ($do_it) {
                                -size     => 1,
                                -multiple => 0 );
        
-       $req = $dbh->prepare( "select distinctrow sort1 from borrowers");
+       my @branches;
+       my @select_branch;
+       my %select_branches;
+       my ($count2,@branches)=branches();
+       push @select_branch,"";
+       $select_branches{''}='';
+       for (my $i=0;$i<$count2;$i++){
+                       push @select_branch, $branches[$i]->{'branchcode'};#
+                       $select_branches{$branches[$i]->{'branchcode'}} = $branches[$i]->{'branchname'};
+       }
+       my $CGIbranch=CGI::scrolling_list( -name     => 'pickup',
+                                                       -values   => \@select_branch,
+                                                       -labels   => \%select_branches,
+                                                       -size     => 1,
+                                                       -multiple => 0 );
+       
+       $req = $dbh->prepare( "select distinctrow sort1 from borrowers order by sort1");
        $req->execute;
        my @select_sort1;
        push @select_sort1,"";
@@ -136,7 +155,7 @@ if ($do_it) {
                                -size     => 1,
                                -multiple => 0 );
        
-       $req = $dbh->prepare( "select distinctrow sort2 from borrowers");
+       $req = $dbh->prepare( "select distinctrow sort2 from borrowers order by sort2");
        $req->execute;
        my @select_sort2;
        push @select_sort2,"";
@@ -178,7 +197,8 @@ if ($do_it) {
                                        CGISort2 => $CGIsort2,
                                        hassort2 => $hassort2,
                                        CGIextChoice => $CGIextChoice,
-                                       CGIsepChoice => $CGIsepChoice
+                                       CGIsepChoice => $CGIsepChoice,
+                                       CGIBranch => $CGIbranch
                                        );
 
 }
@@ -207,14 +227,16 @@ sub calculate {
        
        $linefilter = @$filters[0] if ($line =~ /categorycode/ )  ;
        $linefilter = @$filters[1] if ($line =~ /zipcode/ )  ;
-       $linefilter = @$filters[2] if ($line =~ /sort1/ ) ;
-       $linefilter = @$filters[3] if ($line =~ /sort2/ ) ;
+       $linefilter = @$filters[2] if ($line =~ /branccode/ ) ;
+       $linefilter = @$filters[3] if ($line =~ /sort1/ ) ;
+       $linefilter = @$filters[4] if ($line =~ /sort2/ ) ;
 # 
        my $colfilter = "";
        $colfilter = @$filters[0] if ($column =~ /categorycode/);
        $colfilter = @$filters[1] if ($column =~ /zipcode/);
-       $colfilter = @$filters[2] if ($column =~ /sort1/);
-       $colfilter = @$filters[3] if ($column =~ /sort2/);
+       $colfilter = @$filters[2] if ($column =~ /branchcode/);
+       $colfilter = @$filters[3] if ($column =~ /sort1/);
+       $colfilter = @$filters[4] if ($column =~ /sort2/);
 
        my @loopfilter;
        for (my $i=0;$i<=3;$i++) {
@@ -223,8 +245,9 @@ sub calculate {
                        $cell{filter} .= @$filters[$i];
                        $cell{crit} .="Cat Code " if ($i==0);
                        $cell{crit} .="Zip Code" if ($i==1);
-                       $cell{crit} .="Sort1" if ($i==2);
-                       $cell{crit} .="Sort2" if ($i==3);
+                       $cell{crit} .="Branchcode" if ($i==2);
+                       $cell{crit} .="Sort1" if ($i==3);
+                       $cell{crit} .="Sort2" if ($i==4);
                        push @loopfilter, \%cell;
                }
        }
@@ -259,8 +282,8 @@ sub calculate {
                my %cell;
                if ($celvalue) {
                        $cell{rowtitle} = $celvalue;
-               } else {
-                       $cell{rowtitle} = "";
+#              } else {
+#                      $cell{rowtitle} = "";
                }
                $cell{totalrow} = 0;
                push @loopline, \%cell;
@@ -278,7 +301,7 @@ sub calculate {
        $strsth2 .= "select distinctrow $colfield from borrowers where $column is not null";
        if ( $colfilter ) {
                $strsth2 .= " and $colfield LIKE ? ";
-       } 
+       }
        $strsth2 .= " and $status='1' " if ($status);
        $strsth2 .= " order by $colfield";
 #      warn "". $strsth2;
@@ -291,8 +314,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;
        }
        
@@ -313,7 +337,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]);
@@ -328,22 +352,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;
@@ -351,9 +385,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};
        }
                        
@@ -372,4 +408,3 @@ sub calculate {
        return \@mainloop;
 }
 
-1;
\ No newline at end of file