Fixing Hierarchies : taking any heading subfield

[koha.git] / reports / borrowers_stats.pl

diff --git a/reports/borrowers_stats.pl b/reports/borrowers_stats.pl
index df9e4df..d2aa522 100755 (executable)
--- a/reports/borrowers_stats.pl
+++ b/reports/borrowers_stats.pl
@@ -26,7 +26,9 @@ use C4::Koha;
 use C4::Dates;
 use C4::Acquisition;
 use C4::Output;
+use C4::Reports;
 use C4::Circulation;
+use C4::Dates qw/format_date format_date_in_iso/;
 use Date::Calc qw(
   Today
   Add_Delta_YM
@@ -48,6 +50,8 @@ my $fullreportname = "reports/borrowers_stats.tmpl";
 my $line = $input->param("Line");
 my $column = $input->param("Column");
 my @filters = $input->param("Filter");
+$filters[3]=format_date_in_iso($filters[3]);
+$filters[4]=format_date_in_iso($filters[4]);
 my $digits = $input->param("digits");
 my $period = $input->param("period");
 my $borstat = $input->param("status");
@@ -55,7 +59,8 @@ my $borstat1 = $input->param("activity");
 my $output = $input->param("output");
 my $basename = $input->param("basename");
 my $mime = $input->param("MIME");
-my $del = $input->param("sep");
+our $sep     = $input->param("sep");
+$sep = "\t" if ($sep eq 'tabulation');
 my $selected_branch; # = $input->param("?");
 
 our $branches = GetBranches;
@@ -81,8 +86,6 @@ if ($do_it) {
                        -attachment => "$basename.csv");
                my $cols = @$results[0]->{loopcol};
                my $lines = @$results[0]->{looprow};
-               my $sep;
-               $sep =C4::Context->preference("delimiter");
                print @$results[0]->{line} ."/". @$results[0]->{column} .$sep;
                foreach my $col ( @$cols ) {
                        print $col->{coltitle}.$sep;
@@ -122,7 +125,7 @@ if ($do_it) {
        $req = $dbh->prepare("SELECT authorised_value,lib FROM authorised_values WHERE category='Bsort1' ORDER BY lib");
        $req->execute;
        $template->param( SORT1_LOOP => $req->fetchall_arrayref({}));
-       $req = $dbh->prepare("SELECT DISTINCTROW sort2 AS value FROM borrowers WHERE sort2 IS NOT NULL AND sort <> '' ORDER BY sort2 LIMIT 200");
+       $req = $dbh->prepare("SELECT DISTINCTROW sort2 AS value FROM borrowers WHERE sort2 IS NOT NULL AND sort2 <> '' ORDER BY sort2 LIMIT 200");
                # More than 200 items in a dropdown is not going to be useful anyway, and w/ 50,000 patrons we can destory DB performance.
        $req->execute;
        $template->param( SORT2_LOOP => $req->fetchall_arrayref({}));
@@ -135,13 +138,7 @@ if ($do_it) {
                                -values   => \@mime,
                                -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(
                CGIextChoice => $CGIextChoice,
                CGIsepChoice => $CGIsepChoice,
@@ -187,28 +184,36 @@ sub calculate {
        $linefilter = @$filters[0] if ($line =~ /categorycode/ )  ;
        $linefilter = @$filters[1] if ($line =~ /zipcode/ )  ;
        $linefilter = @$filters[2] if ($line =~ /branchcode/ ) ;
-       $linefilter = @$filters[5] if ($line =~ /sort1/ ) ;
-       $linefilter = @$filters[6] if ($line =~ /sort2/ ) ;
+       $linefilter = @$filters[5] if ($line =~ /sex/);
+       $linefilter = @$filters[6] if ($line =~ /sort1/ ) ;
+    $linefilter = @$filters[7] if ($line =~ /sort2/ ) ;
 # 
        my $colfilter = "";
        $colfilter = @$filters[0] if ($column =~ /categorycode/);
        $colfilter = @$filters[1] if ($column =~ /zipcode/);
        $colfilter = @$filters[2] if ($column =~ /branchcode/);
-       $colfilter = @$filters[5] if ($column =~ /sort1/);
-       $colfilter = @$filters[6] if ($column =~ /sort2/);
+    $colfilter = @$filters[5] if ($column =~ /sex/);
+    $colfilter = @$filters[6] if ($column =~ /sort1/);
+       $colfilter = @$filters[7] if ($column =~ /sort2/);
 
        my @loopfilter;
-       for (my $i=0;$i<=6;$i++) {
+       for (my $i=0;$i<=7;$i++) {
                my %cell;
                if ( @$filters[$i] ) {
-                       $cell{filter} .= @$filters[$i];
+                   if($i == 3 or $i == 4){
+                       $cell{filter} .= format_date(@$filters[$i]);
+                   }else{
+                       $cell{filter} .= @$filters[$i];
+                   }
+                       
                        $cell{crit} .="Cat Code " if ($i==0);
                        $cell{crit} .="Zip Code" if ($i==1);
                        $cell{crit} .="Branchcode" if ($i==2);
                        $cell{crit} .="Date of Birth" if ($i==3);
                        $cell{crit} .="Date of Birth" if ($i==4);
-                       $cell{crit} .="Sort1" if ($i==5);
-                       $cell{crit} .="Sort2" if ($i==6);
+            $cell{crit} .="Sex" if ($i==5);
+                       $cell{crit} .="Sort1" if ($i==6);
+                       $cell{crit} .="Sort2" if ($i==7);
                        push @loopfilter, \%cell;
                }
        }
@@ -312,10 +317,12 @@ sub calculate {
        $strcalc .= " AND dateofbirth > '" . @$filters[3] ."'" if ( @$filters[3] );
        @$filters[4]=~ s/\*/%/g if (@$filters[4]);
        $strcalc .= " AND dateofbirth < '" . @$filters[4] ."'" if ( @$filters[4] );
-       @$filters[5]=~ s/\*/%/g if (@$filters[5]);
-       $strcalc .= " AND sort1 like '" . @$filters[5] ."'" if ( @$filters[5] );
-       @$filters[6]=~ s/\*/%/g if (@$filters[6]);
-       $strcalc .= " AND sort2 like '" . @$filters[6] ."'" if ( @$filters[6] );
+    @$filters[5]=~ s/\*/%/g if (@$filters[5]);
+    $strcalc .= " AND sex like '" . @$filters[5] ."'" if ( @$filters[5] );
+    @$filters[6]=~ s/\*/%/g if (@$filters[6]);
+       $strcalc .= " AND sort1 like '" . @$filters[6] ."'" if ( @$filters[6] );
+       @$filters[7]=~ s/\*/%/g if (@$filters[7]);
+       $strcalc .= " AND sort2 like '" . @$filters[7] ."'" if ( @$filters[7] );
        $strcalc .= " AND borrowernumber in (select distinct(borrowernumber) from old_issues where issuedate > '" . $newperioddate . "')" if ($activity eq 'active');
        $strcalc .= " AND borrowernumber not in (select distinct(borrowernumber) from old_issues where issuedate > '" . $newperioddate . "')" if ($activity eq 'nonactive');
        $strcalc .= " AND $status='1' " if ($status);
@@ -327,9 +334,9 @@ sub calculate {
        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);
+               $emptycol = 1 if (!defined($col));
+               $col = "zzEMPTY" if (!defined($col));
+               $row = "zzEMPTY" if (!defined($row));
                
                $table{$row}->{$col}+=$value;
                $table{$row}->{totalrow}+=$value;