#!/usr/bin/perl
-
# Copyright 2000-2002 Katipo Communications
#
# This file is part of Koha.
use C4::Koha;
use C4::Circulation;
use C4::Members;
+use C4::Debug;
use C4::Dates qw(format_date format_date_in_iso);
=head1 NAME
=cut
+$debug = 1;
+$debug and open DEBUG, ">/tmp/bor_issues_top.debug.log";
+
my $input = new CGI;
my $fullreportname = "reports/bor_issues_top.tmpl";
my $do_it = $input->param('do_it');
my $limit = $input->param("Limit");
my $column = $input->param("Criteria");
my @filters = $input->param("Filter");
-for (0..3) {
- $filters[$_]=format_date_in_iso($filters[$_]);
+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 $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,
# 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(1);
+}
- #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 /[;:]/,C4::Context->preference("MIME")) );
+my @delim = ( map { +{delim=>$_} } (split //,C4::Context->preference("delimiter")) );
+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 %row =(value => $thisborcat,
- 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,
+ seploop => \@delim,
+ 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 @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",
$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{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 = "old_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/ ) ;
+ 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];
+ }
+ # $colfilter[0] = @$filters[7] if ($column =~ /timestamp/ ) ; FIXME This can't be right.
+ # $colfilter[0] = @$filters[8] if ($column =~ /timestamp/ ) ; FIXME
+ # $colfilter[0] = @$filters[9] if ($column =~ /timestamp/ ) ; FIXME Only this line would have effect.
+
#warn "filtre col ".$colfilter[0]." ".$colfilter[1];
# loop cols.
$colfield .= $column;
$colorder .= $column;
}
-
+
my $strsth2;
$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 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])){
+ 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 .=" GROUP BY $colfield";
$strsth2 .=" ORDER BY $colorder";
- warn "". $strsth2;
-
- my $sth2 = $dbh->prepare( $strsth2 );
+
+ $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' };
+ }
+
+ $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' };
}
- # warn "fin des titres colonnes";
+ $debug and print DEBUG "full array: ", Dumper(\%columns), "\n";
}
-
- 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;
+ foreach (keys %columns) {
+# warn " init table : $row->{rowtitle} / $_ ";
+ $table[$i]->{ $_ || "total" }->{'name'}=0;
}
}
-
-# 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 `old_issues`
- LEFT JOIN borrowers ON borrowers.borrowernumber
- LEFT JOIN items ON items.itemnumber=old_issues.itemnumber
+ LEFT JOIN borrowers ON old_issues.borrowernumber=borrowers.borrowernumber
+ LEFT JOIN items ON items.itemnumber=old_issues.itemnumber
LEFT JOIN biblioitems ON (biblioitems.biblioitemnumber=items.biblioitemnumber)
WHERE 1";
-
- @$filters[0]=~ s/\*/%/g if (@$filters[0]);
- $strcalc .= " AND old_issues.timestamp > '" . @$filters[0] ."'" if ( @$filters[0] );
- @$filters[1]=~ s/\*/%/g if (@$filters[1]);
- $strcalc .= " AND old_issues.timestamp < '" . @$filters[1] ."'" if ( @$filters[1] );
- @$filters[2]=~ s/\*/%/g if (@$filters[2]);
- $strcalc .= " AND old_issues.returndate > '" . @$filters[2] ."'" if ( @$filters[2] );
- @$filters[3]=~ s/\*/%/g if (@$filters[3]);
- $strcalc .= " AND old_issues.returndate < '" . @$filters[3] ."'" if ( @$filters[3] );
- @$filters[4]=~ s/\*/%/g if (@$filters[4]);
- $strcalc .= " AND old_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(old_issues.timestamp) like '" . @$filters[7]."'" if (@$filters[7]);
- @$filters[8]=~ s/\*/%/g if (@$filters[8]);
- $strcalc .= " AND monthname(old_issues.timestamp) like '" . @$filters[8]."'" if (@$filters[8]);
- @$filters[9]=~ s/\*/%/g if (@$filters[9]);
- $strcalc .= " AND year(old_issues.timestamp) like '" . @$filters[9] ."'" if ( @$filters[9] );
+ my @filterterms = (
+ 'old_issues.timestamp >',
+ 'old_issues.timestamp <',
+ 'old_issues.returndate >',
+ 'old_issues.returndate <',
+ 'old_issues.branchcode like',
+ 'biblioitems.itemtype like',
+ 'borrowers.categorycode like',
+ 'dayname(old_issues.timestamp) like',
+ 'monthname(old_issues.timestamp) like',
+ 'monthname(old_issues.timestamp) like',
+ 'year(old_issues.timestamp) 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);
# $max = $line*@loopcol;
# } else { $max=$line;}
# $strcalc .= " LIMIT 0,$max";
- warn "SQL :". $strcalc;
-
+
+ $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;
+ $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 ($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}++;
+ 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 ($col eq undef);
+ 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;
- my $key = ((!(@loopcol)) or ($col->{coltitle} eq "NULL") or ($col->{coltitle} eq "Global"))
- ? "zzEMPTY" : $col->{coltitle};
- $value =$table[$i]->{$key}->{'name'};
- $count =$table[$i]->{$key}->{'count'};
- $link =$table[$i]->{$key}->{'link'};
-# warn " ".$i ." value:$value count:$count reference:$link";
- push @loopcell, {value => $value, count =>$count, reference => $link} ;
+ foreach my $key (@cols_in_order) {
+ push @loopcell, {
+ value => $patrons{$id}->{name},
+ count => $patrons{$id}->{allcols}->{$key},
+ reference => $id,
+ };
}
- 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{loopcol} = [ map {+{coltitle=>$_}} @cols_in_order ];
+ # the foot (totals by borrower type)
+ $globalline{loopfooter} = [];
+ $globalline{total}= $grantotal; # FIXME: useless
$globalline{line} = $line;
$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__
projects / koha.git / commitdiff