X-Git-Url: http://koha-dev.rot13.org:8081/gitweb/?a=blobdiff_plain;f=reports%2Fbor_issues_top.pl;h=9618faf3a4b5410ceeeb478ef38735471ef574a7;hb=870cafa4afa9b94d54ed68daa3866a55d86e8c22;hp=01df7385db2ff0dcd5512ee2edd7d24cd5a72d08;hpb=669a146e1a10b2b468e42039fa295d20b9d78b6c;p=koha_fer

diff --git a/reports/bor_issues_top.pl b/reports/bor_issues_top.pl
index 01df7385db..9618faf3a4 100755
--- a/reports/bor_issues_top.pl
+++ b/reports/bor_issues_top.pl
@@ -1,7 +1,5 @@
 #!/usr/bin/perl
 
-# $Id$
-
 # Copyright 2000-2002 Katipo Communications
 #
 # This file is part of Koha.
@@ -15,11 +13,12 @@
 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
 # A PARTICULAR PURPOSE.  See the GNU General Public License for more details.
 #
-# You should have received a copy of the GNU General Public License along with
-# Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place,
-# Suite 330, Boston, MA  02111-1307 USA
+# You should have received a copy of the GNU General Public License along
+# with Koha; if not, write to the Free Software Foundation, Inc.,
+# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
 
 use strict;
+#use warnings; FIXME - Bug 2505
 use CGI;
 use C4::Auth;
 use C4::Output;
@@ -28,7 +27,9 @@ use C4::Branch; # GetBranches
 use C4::Koha;
 use C4::Circulation;
 use C4::Members;
-use C4::Date;
+use C4::Reports;
+use C4::Debug;
+use C4::Dates qw(format_date format_date_in_iso);
 
 =head1 NAME
 
@@ -40,31 +41,31 @@ plugin that shows a stats on borrowers
 
 =cut
 
+$debug = 1;
+$debug and open DEBUG, ">/tmp/bor_issues_top.debug.log";
+
 my $input = new CGI;
-my $do_it=$input->param('do_it');
 my $fullreportname = "reports/bor_issues_top.tmpl";
-my $limit = $input->param("Limit");
-my $column = $input->param("Criteria");
+my $do_it   = $input->param('do_it');
+my $limit   = $input->param("Limit");
+my $column  = $input->param("Criteria");
 my @filters = $input->param("Filter");
-$filters[0]=format_date_in_iso($filters[0]);
-$filters[1]=format_date_in_iso($filters[1]);
-$filters[2]=format_date_in_iso($filters[2]);
-$filters[3]=format_date_in_iso($filters[3]);
-my $output = $input->param("output");
+foreach ( @filters[0..3] ) {
+	$_ and $_ = format_date_in_iso($_);	
+}
+my $output   = $input->param("output");
 my $basename = $input->param("basename");
-my $mime = $input->param("MIME");
-my $del = $input->param("sep");
-#warn "calcul : ".$calc;
 my ($template, $borrowernumber, $cookie)
     = get_template_and_user({template_name => $fullreportname,
                 query => $input,
                 type => "intranet",
                 authnotrequired => 0,
-                flagsrequired => {reports => 1},
+                flagsrequired => {reports => '*'},
                 debug => 1,
                 });
+our $sep     = $input->param("sep");
+$sep = "\t" if ($sep eq 'tabulation');
 $template->param(do_it => $do_it,
-        DHTMLcalendar_dateformat => get_date_format_string_for_DHTMLcalendar(),
         );
 if ($do_it) {
 # Displaying results
@@ -73,134 +74,106 @@ if ($do_it) {
 # Printing results to screen
         $template->param(mainloop => $results, limit=>$limit);
         output_html_with_http_headers $input, $cookie, $template->output;
-        exit(1);
     } else {
 # Printing to a csv file
         print $input->header(-type => 'application/vnd.sun.xml.calc',
                             -encoding    => 'utf-8',
                             -attachment=>"$basename.csv",
                             -filename=>"$basename.csv" );
-        my $cols = @$results[0]->{loopcol};
+        my $cols  = @$results[0]->{loopcol};
         my $lines = @$results[0]->{looprow};
-        my $sep;
-        $sep =C4::Context->preference("delimiter");
 # header top-right
         print @$results[0]->{line} ."/". @$results[0]->{column} .$sep;
 # Other header
-        foreach my $col ( @$cols ) {
-            print $col->{coltitle}.$sep;
-        }
-        print "Total\n";
+		print join($sep, map {$_->{coltitle}} @$cols);
+        print $sep . "Total\n";
 # Table
         foreach my $line ( @$lines ) {
             my $x = $line->{loopcell};
             print $line->{rowtitle}.$sep;
-            foreach my $cell (@$x) {
-                print $cell->{value}.$sep;
-            }
-            print $line->{totalrow};
+			print join($sep, map {$_->{value}} @$x);
+            print $sep,$line->{totalrow};
             print "\n";
         }
 # footer
         print "TOTAL";
         $cols = @$results[0]->{loopfooter};
-        foreach my $col ( @$cols ) {
-            print $sep.$col->{totalcol};
-        }
+		print join($sep, map {$_->{totalcol}} @$cols);
         print $sep.@$results[0]->{total};
-        exit(1);
-    }
-# Displaying choices
-} else {
-    my $dbh = C4::Context->dbh;
-    my @values;
-    my %labels;
-    my %select;
-    my $req;
-    
-    my @mime = ( C4::Context->preference("MIME") );
-#	foreach my $mime (@mime){
-#		warn "".$mime;
-#	}
-        my $CGIextChoice=CGI::scrolling_list(
-                                -name     => 'MIME',
-                                -id       => 'MIME',
-                                -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 );
-        #branch
-        my $branches = GetBranches;
-	my @branchloop;
-	foreach my $thisbranch (sort keys %$branches) {
-            my %row =(value => $thisbranch,
-                                    branchname => $branches->{$thisbranch}->{'branchname'},
-                            );
-            push @branchloop, \%row;
     }
+    exit;
+}
 
-    #doctype
-    my $itemtypes = GetItemTypes;
-    my @itemtypeloop;
-    foreach my $thisitemtype (keys %$itemtypes) {
-# 			my $selected = 1 if $thisbranch eq $branch;
-            my %row =(value => $thisitemtype,
-# 									selected => $selected,
-                                    description => $itemtypes->{$thisitemtype}->{'description'},
-                            );
-            push @itemtypeloop, \%row;
-    }
+my $dbh = C4::Context->dbh;
+my @values;
+
+# here each element returned by map is a hashref, get it?
+my @mime  = ( map { {type =>$_} } (split /[;:]/, 'CSV') ); # FIXME translation
+my $delims = GetDelimiterChoices;
+my $branches = GetBranches;
+my @branchloop;
+foreach (sort keys %$branches) {
+# 	my $selected = 1 if $thisbranch eq $branch;
+	my %row = ( value => $_,
+#				selected => $selected,
+				branchname => $branches->{$_}->{branchname},
+			);
+	push @branchloop, \%row;
+}
+
+my $itemtypes = GetItemTypes;
+my @itemtypeloop;
+foreach (sort {$itemtypes->{$a}->{description} cmp $itemtypes->{$b}->{description}} keys %$itemtypes) {
+	my %row = (value => $_,
+               description => $itemtypes->{$_}->{description},
+              );
+    push @itemtypeloop, \%row;
+}
     
-    #borcat
-    my ($codes,$labels) = GetborCatFromCatType(undef,undef);
-    my @borcatloop;
-    foreach my $thisborcat (sort keys %$labels) {
-# 			my $selected = 1 if $thisbranch eq $branch;
-            my %row =(value => $thisborcat,
-# 									selected => $selected,
-                                    description => $labels->{$thisborcat},
-                            );
-            push @borcatloop, \%row;
-    }
+my ($codes,$labels) = GetborCatFromCatType(undef,undef);
+my @borcatloop;
+foreach (sort keys %$labels) {
+	my %row =(value => $_,
+              description => $labels->{$_},
+             );
+    push @borcatloop, \%row;
+}
     
-    #Day
-    #Month
-    $template->param(
-                    CGIextChoice => $CGIextChoice,
-                    CGIsepChoice => $CGIsepChoice,
-                    branchloop =>\@branchloop,
-                    itemtypeloop =>\@itemtypeloop,
-                    borcatloop =>\@borcatloop,
-                    );
+$template->param(
+	    mimeloop => \@mime,
+	  CGIseplist => $delims,
+	  branchloop => \@branchloop,
+	itemtypeloop => \@itemtypeloop,
+	  borcatloop => \@borcatloop,
+);
 output_html_with_http_headers $input, $cookie, $template->output;
-}
-
-
 
 
 sub calculate {
-    my ($line, $column, $filters) = @_;
-    my @mainloop;
-    my @loopfooter;
+    my ($limit, $column, $filters) = @_;
+
     my @loopcol;
     my @loopline;
     my @looprow;
     my %globalline;
+	my %columns;
     my $grantotal =0;
-# extract parameters
     my $dbh = C4::Context->dbh;
 
-# Filters
 # Checking filters
-#
     my @loopfilter;
+	my @cellmap = (
+		"Issue From",
+		"Issue To",
+		"Return From",
+		"Return To",
+		"Branch",
+		"Doc Type",
+		"Bor Cat",
+		"Day",
+		"Month",
+		"Year"
+	);
     for (my $i=0;$i<=6;$i++) {
         my %cell;
         if ( @$filters[$i] ) {
@@ -208,212 +181,220 @@ sub calculate {
                 $cell{err} = 1 if (@$filters[$i]<@$filters[$i-1]) ;
             }
             # format the dates filters, otherwise just fill as is
-            if ($i>=4) {
-                $cell{filter} .= @$filters[$i];
-            } else {
-                $cell{filter} .= format_date(@$filters[$i]);
-            }
-            $cell{crit} .="Issue From" if ($i==0);
-            $cell{crit} .="Issue To" if ($i==1);
-            $cell{crit} .="Return From" if ($i==2);
-            $cell{crit} .="Return To" if ($i==3);
-            $cell{crit} .="Branch" if ($i==4);
-            $cell{crit} .="Doc Type" if ($i==5);
-            $cell{crit} .="Bor Cat" if ($i==6);
-            $cell{crit} .="Day" if ($i==7);
-            $cell{crit} .="Month" if ($i==8);
-            $cell{crit} .="Year" if ($i==9);
+            $cell{filter} .= ($i>=4) ? @$filters[$i] : format_date(@$filters[$i]);
+			defined ($cellmap[$i]) and
+				$cell{crit} .= $cellmap[$i];
             push @loopfilter, \%cell;
         }
     }
     my $colfield;
     my $colorder;
     if ($column){
-        $column = "issues.".$column if (($column=~/branchcode/) or ($column=~/timestamp/));
+        $column = "old_issues." .$column if (($column=~/branchcode/) or ($column=~/timestamp/));
         $column = "biblioitems.".$column if $column=~/itemtype/;
-        $column = "borrowers.".$column if $column=~/categorycode/;
+        $column = "borrowers."  .$column if $column=~/categorycode/;
         my @colfilter ;
-        $colfilter[0] = @$filters[0] if ($column =~ /timestamp/ )  ;
-        $colfilter[1] = @$filters[1] if ($column =~ /timestamp/ )  ;
-        $colfilter[0] = @$filters[2] if ($column =~ /returndate/ )  ;
-        $colfilter[1] = @$filters[3] if ($column =~ /returndate/ )  ;
-        $colfilter[0] = @$filters[4] if ($column =~ /branch/ )  ;
-        $colfilter[0] = @$filters[5] if ($column =~ /itemtype/ )  ;
-        $colfilter[0] = @$filters[6] if ($column =~ /category/ )  ;
-    # 	$colfilter[0] = @$filters[11] if ($column =~ /sort2/ ) ;
-        $colfilter[0] = @$filters[7] if ($column =~ /timestamp/ ) ;
-        $colfilter[0] = @$filters[8] if ($column =~ /timestamp/ ) ;
-        $colfilter[0] = @$filters[9] if ($column =~ /timestamp/ ) ;
-    #warn "filtre col ".$colfilter[0]." ".$colfilter[1];
+		if ($column =~ /timestamp/) {
+        	$colfilter[0] = @$filters[0];
+       		$colfilter[1] = @$filters[1];
+		} elsif ($column =~ /returndate/) {
+        	$colfilter[0] = @$filters[2];
+        	$colfilter[1] = @$filters[3];
+		} elsif ($column =~ /branchcode/) {
+			$colfilter[0] = @$filters[4];
+		} elsif ($column =~ /itemtype/) {
+			$colfilter[0] = @$filters[5];
+		} elsif ($column =~ /category/) {
+			$colfilter[0] = @$filters[6];
+		} elsif ($column =~ /sort2/   ) {
+			# $colfilter[0] = @$filters[11];
+		}
                                                 
     # loop cols.
         if ($column eq "Day") {
             #Display by day
-            $column = "issues.timestamp";
+            $column = "old_issues.timestamp";
             $colfield .="dayname($column)";  
             $colorder .="weekday($column)";
         } elsif ($column eq "Month") {
             #Display by Month
-            $column = "issues.timestamp";
+            $column = "old_issues.timestamp";
             $colfield .="monthname($column)";  
             $colorder .="month($column)";  
         } elsif ($column eq "Year") {
             #Display by Year
-            $column = "issues.timestamp";
+            $column = "old_issues.timestamp";
             $colfield .="Year($column)";
             $colorder .= $column;
         } else {
             $colfield .= $column;
             $colorder .= $column;
         }  
-        
+
         my $strsth2;
-        $strsth2 .= "select distinctrow $colfield FROM `issues`,borrowers,biblioitems LEFT JOIN items ON (biblioitems.biblioitemnumber=items.biblioitemnumber) WHERE issues.itemnumber=items.itemnumber AND issues.borrowernumber=borrowers.borrowernumber and returndate is not null";
+        $strsth2 .= "SELECT DISTINCTROW $colfield 
+                     FROM `old_issues` 
+                     LEFT JOIN borrowers   ON old_issues.borrowernumber=borrowers.borrowernumber 
+                     LEFT JOIN items       ON old_issues.itemnumber=items.itemnumber 
+                     LEFT JOIN biblioitems ON (biblioitems.biblioitemnumber=items.biblioitemnumber)
+                     WHERE 1";
         if (($column=~/timestamp/) or ($column=~/returndate/)){
-            if ($colfilter[1] and ($colfilter[0])){
-                $strsth2 .= " and $column between '$colfilter[0]' and '$colfilter[1]' " ;
+            if ($colfilter[1] and $colfilter[0]){
+                $strsth2 .= " AND $column between '$colfilter[0]' AND '$colfilter[1]' " ;
             } elsif ($colfilter[1]) {
-                    $strsth2 .= " and $column < '$colfilter[1]' " ;
+                $strsth2 .= " AND $column < '$colfilter[1]' " ;
             } elsif ($colfilter[0]) {
-                $strsth2 .= " and $column > '$colfilter[0]' " ;
+                $strsth2 .= " AND $column > '$colfilter[0]' " ;
             }
         } elsif ($colfilter[0]) {
             $colfilter[0] =~ s/\*/%/g;
-            $strsth2 .= " and $column LIKE '$colfilter[0]' " ;
+            $strsth2 .= " AND $column LIKE '$colfilter[0]' " ;
         }
-        $strsth2 .=" group by $colfield";
-        $strsth2 .=" order by $colorder";
-        warn "". $strsth2;
-        
-        my $sth2 = $dbh->prepare( $strsth2 );
+        $strsth2 .=" GROUP BY $colfield";
+        $strsth2 .=" ORDER BY $colorder";
+
+        $debug and print DEBUG "bor_issues_top (old_issues) SQL: $strsth2\n";
+        my $sth2 = $dbh->prepare($strsth2);
         $sth2->execute;
-    
-        while (my ($celvalue) = $sth2->fetchrow) {
-            my %cell;
-            $cell{'coltitle'} = ($celvalue?$celvalue:"NULL");
-            push @loopcol, \%cell;
+        print DEBUG "rows: ", $sth2->rows, "\n";
+        while (my @row = $sth2->fetchrow) {
+			$columns{($row[0] ||'NULL')}++;
+            push @loopcol, { coltitle => $row[0] || 'NULL' };
         }
-    #	warn "fin des titres colonnes";
-    }
-    
-    my $i=0;
-#	my @totalcol;
-    my $hilighted=-1;
-    
-    #Initialization of cell values.....
-    my @table;
-    
-#	warn "init table";
-    for (my $i=1;$i<=$line;$i++) {
-        foreach my $col ( @loopcol ) {
-#			warn " init table : $row->{rowtitle} / $col->{coltitle} ";
-            $table[$i]->{($col->{coltitle})?$col->{coltitle}:"total"}->{'name'}=0;
+
+		$strsth2 =~ s/old_issues/issues/g;
+        $debug and print DEBUG "bor_issues_top (issues) SQL: $strsth2\n";
+		$sth2 = $dbh->prepare($strsth2);
+        $sth2->execute;
+        $debug and print DEBUG "rows: ", $sth2->rows, "\n";
+        while (my @row = $sth2->fetchrow) {
+			$columns{($row[0] ||'NULL')}++;
+            push @loopcol, { coltitle => $row[0] || 'NULL' };
         }
+		$debug and print DEBUG "full array: ", Dumper(\%columns), "\n";
+    }else{
+        $columns{''} = 1;
     }
 
-
-# preparing calculation
     my $strcalc ;
-    
+
 # Processing average loanperiods
-    $strcalc .= "SELECT  CONCAT(borrowers.surname , \"\\t\",borrowers.firstname),  COUNT(*) AS RANK, borrowers.borrowernumber AS ID";
+    $strcalc .= "SELECT  CONCAT(borrowers.surname , \",\\t\",borrowers.firstname),  COUNT(*) AS RANK, borrowers.borrowernumber AS ID";
     $strcalc .= " , $colfield " if ($colfield);
-    $strcalc .= " FROM `issues`,borrowers,biblioitems LEFT JOIN items ON (biblioitems.biblioitemnumber=items.biblioitemnumber)  WHERE issues.itemnumber=items.itemnumber AND issues.borrowernumber=borrowers.borrowernumber and returndate is not null";
-
-    @$filters[0]=~ s/\*/%/g if (@$filters[0]);
-    $strcalc .= " AND issues.timestamp > '" . @$filters[0] ."'" if ( @$filters[0] );
-    @$filters[1]=~ s/\*/%/g if (@$filters[1]);
-    $strcalc .= " AND issues.timestamp < '" . @$filters[1] ."'" if ( @$filters[1] );
-    @$filters[2]=~ s/\*/%/g if (@$filters[2]);
-    $strcalc .= " AND issues.returndate > '" . @$filters[2] ."'" if ( @$filters[2] );
-    @$filters[3]=~ s/\*/%/g if (@$filters[3]);
-    $strcalc .= " AND issues.returndate < '" . @$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 biblioitems.itemtype like '" . @$filters[5] ."'" if ( @$filters[5] );
-    @$filters[6]=~ s/\*/%/g if (@$filters[6]);
-    $strcalc .= " AND borrowers.categorycode like '" . @$filters[6] ."'" if ( @$filters[6] );
-    @$filters[7]=~ s/\*/%/g if (@$filters[7]);
-    $strcalc .= " AND dayname(issues.timestamp) like '" . @$filters[7]."'" if (@$filters[7]);
-    @$filters[8]=~ s/\*/%/g if (@$filters[8]);
-    $strcalc .= " AND monthname(issues.timestamp) like '" . @$filters[8]."'" if (@$filters[8]);
-    @$filters[9]=~ s/\*/%/g if (@$filters[9]);
-    $strcalc .= " AND year(issues.timestamp) like '" . @$filters[9] ."'" if ( @$filters[9] );
-    
-    $strcalc .= " group by borrowers.borrowernumber";
+    $strcalc .= " FROM `old_issues`
+                  LEFT JOIN  borrowers  USING(borrowernumber)
+                  LEFT JOIN    items    USING(itemnumber)
+                  LEFT JOIN biblioitems USING(biblioitemnumber)
+                  WHERE old_issues.borrowernumber IS NOT NULL
+                  ";
+	my @filterterms = (
+		'old_issues.issuedate >',
+		'old_issues.issuedate <',
+		'old_issues.returndate >',
+		'old_issues.returndate <',
+		'old_issues.branchcode  like',
+		'biblioitems.itemtype   like',
+		'borrowers.categorycode like',
+	);
+    foreach ((@$filters)[0..9]) {
+		my $term = shift @filterterms;	# go through both arrays in step
+		($_) or next;
+		s/\*/%/g;
+		$strcalc .= " AND $term '$_' ";
+	}
+    $strcalc .= " GROUP BY borrowers.borrowernumber";
     $strcalc .= ", $colfield" if ($column);
-    $strcalc .= " order by RANK DESC";
+    $strcalc .= " ORDER BY RANK DESC";
     $strcalc .= ",$colfield " if ($colfield);
-# 	my $max;
-# 	if (@loopcol) {
-# 		$max = $line*@loopcol;
-# 	} else { $max=$line;}
-# 	$strcalc .= " LIMIT 0,$max";
-    warn "SQL :". $strcalc;
-    
+    $strcalc .= " LIMIT $limit" if ($limit);
+
+    $debug and print DEBUG "(old_issues) SQL : $strcalc\n";
     my $dbcalc = $dbh->prepare($strcalc);
     $dbcalc->execute;
-# 	warn "filling table";
-    my $previous_col;
-    my %indice;
-    while (my  @data = $dbcalc->fetchrow) {
-        my ($row, $rank, $id, $col )=@data;
-        $col = "zzEMPTY" if ($col eq undef);
-        $indice{$col}=1 if (not($indice{$col}));
-        $table[$indice{$col}]->{$col}->{'name'}=$row;
-        $table[$indice{$col}]->{$col}->{'count'}=$rank;
-        $table[$indice{$col}]->{$col}->{'link'}=$id;
-#		warn " ".$i." ".$col. " ".$row;
-        $indice{$col}++;
+    $debug and print DEBUG "rows: ", $dbcalc->rows, "\n";
+	my %patrons = ();
+	# DATA STRUCTURE is going to look like this:
+	# 	(2253=> {name=>"John Doe",
+	# 				allcols=>{MAIN=>12, MEDIA_LIB=>3}
+	# 			},
+	# 	)
+    while (my @data = $dbcalc->fetchrow) {
+        my ($row, $rank, $id, $col) = @data;
+        $col = "zzEMPTY" if (!defined($col));
+		unless ($patrons{$id}) {
+			$patrons{$id} = {name=>$row, allcols=>{}, newcols=>{}, oldcols=>{}};
+		}
+		$patrons{$id}->{oldcols}->{$col} = $rank;
     }
+
+	use Data::Dumper;
+
+	$strcalc =~ s/old_issues/issues/g;
+    $debug and print DEBUG "(issues) SQL : $strcalc\n";
+    $dbcalc = $dbh->prepare($strcalc);
+    $dbcalc->execute;
+    $debug and print DEBUG "rows: ", $dbcalc->rows, "\n";
+    while (my @data = $dbcalc->fetchrow) {
+        my ($row, $rank, $id, $col) = @data;
+        $col = "zzEMPTY" if (!defined($col));
+		unless ($patrons{$id}) {
+			$patrons{$id} = {name=>$row, allcols=>{}, newcols=>{}, oldcols=>{}};
+		}
+		$patrons{$id}->{newcols}->{$col} = $rank;
+    }
+
+	foreach my $id (keys %patrons) {
+		my @uniq = keys %{{ %{$patrons{$id}->{newcols}}, %{$patrons{$id}->{oldcols}} }};		# get uniq keys, see perlfaq4
+		foreach (@uniq) {
+			my $count = ($patrons{$id}->{newcols}->{$_} || 0) +
+						($patrons{$id}->{oldcols}->{$_} || 0);
+			$patrons{$id}->{allcols}->{$_} = $count;
+			$patrons{$id}->{total} += $count;
+		}
+	}
+    $debug and print DEBUG "\n\npatrons: ", Dumper(\%patrons);
     
-    push @loopcol,{coltitle => "Global"} if not($column);
-    
-    for ($i=1; $i<=$line;$i++) {
+	my $i = 1;
+	my @cols_in_order = sort keys %columns;		# if you want to order the columns, do something here
+	my @ranked_ids = sort {
+						   $patrons{$b}->{total} <=> $patrons{$a}->{total}
+						|| $patrons{$a}->{name}  cmp $patrons{$b}->{name}
+						} keys %patrons;
+    foreach my $id (@ranked_ids) {
         my @loopcell;
-        warn " $i";
-        #@loborrowersopcol ensures the order for columns is common with column titles
-        # and the number matches the number of columns
-        my $colcount=0;
-        foreach my $col ( @loopcol ) {
-#			warn " colonne :$col->{coltitle}";
-            my $value;
-            my $count=0;
-            my $link;
-            if (@loopcol){
-                $value =$table[$i]->{(($col->{coltitle} eq "NULL") or ($col->{coltitle} eq "Global"))?"zzEMPTY":$col->{coltitle}}->{'name'};
-                $count =$table[$i]->{(($col->{coltitle} eq "NULL") or ($col->{coltitle} eq "Global"))?"zzEMPTY":$col->{coltitle}}->{'count'};
-                $link =$table[$i]->{(($col->{coltitle} eq "NULL") or ($col->{coltitle} eq "Global"))?"zzEMPTY":$col->{coltitle}}->{'link'};
-            } else {
-                $value =$table[$i]->{"zzEMPTY"}->{'name'};
-                $count =$table[$i]->{"zzEMPTY"}->{'count'};
-                $link =$table[$i]->{"zzEMPTY"}->{'link'};
-            }
-#			warn " ".$i ." value:$value count:$count reference:$link";
-            push @loopcell, {value => $value, count =>$count, reference => $link} ;
+        foreach my $key (@cols_in_order) {
+			if($column){
+		      push @loopcell, {
+				value => $patrons{$id}->{name},
+				reference => $id,
+				count => $patrons{$id}->{allcols}->{$key},
+			  };
+			}else{
+			  push @loopcell, {
+				value => $patrons{$id}->{name},
+				reference => $id,
+				count => $patrons{$id}->{total},
+			  };  
+			}
         }
-        push @looprow,{ 'rowtitle' => $i ,
+        push @looprow,{ 'rowtitle' => $i++ ,
                         'loopcell' => \@loopcell,
-                        'hilighted' => ($hilighted >0),
+                        'hilighted' => ($i%2),
                     };
-        $hilighted = -$hilighted;
     }
-# 	
-            
-
+	
     # the header of the table
     $globalline{loopfilter}=\@loopfilter;
     # the core of the table
     $globalline{looprow} = \@looprow;
-    $globalline{loopcol} = \@loopcol;
-# 	# the foot (totals by borrower type)
-    $globalline{loopfooter} = \@loopfooter;
-    $globalline{total}= $grantotal;
-    $globalline{line} = $line;
+    $globalline{loopcol} = [ map {{coltitle=>$_}} @cols_in_order ];
+ 	# the foot (totals by borrower type)
+    $globalline{loopfooter} = [];
+    $globalline{total}= $grantotal;		# FIXME: useless
     $globalline{column} = $column;
-    push @mainloop,\%globalline;
-    return \@mainloop;
+    return [\%globalline];	# reference to a 1 element array: that element is a hashref
 }
 
+$debug and close DEBUG;
 1;
+__END__