use C4::Interface::CGI::Output;
use C4::Circulation::Circ2;
use Date::Manip;
+use C4::Members;
=head1 NAME
my $results = calculate($limit, $column, \@filters);
if ($output eq "screen"){
# Printing results to screen
- $template->param(mainloop => $results);
+ $template->param(mainloop => $results,
+ limit => $limit);
output_html_with_http_headers $input, $cookie, $template->output;
exit(1);
} else {
-values => \@dels,
-size => 1,
-multiple => 0 );
+ #branch
+ my $branches = getallbranches;
+ my @branchloop;
+ foreach my $thisbranch (keys %$branches) {
+# my $selected = 1 if $thisbranch eq $branch;
+ my %row =(value => $thisbranch,
+# selected => $selected,
+ branchname => $branches->{$thisbranch}->{'branchname'},
+ );
+ push @branchloop, \%row;
+ }
+
+ #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;
+ }
+
+ #borcat
+ my ($codes,$labels) = borrowercategories;
+ 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;
+ }
+ #Day
+ #Month
$template->param(
CGIextChoice => $CGIextChoice,
- CGIsepChoice => $CGIsepChoice
+ CGIsepChoice => $CGIsepChoice,
+ branchloop =>\@branchloop,
+ itemtypeloop =>\@itemtypeloop,
+ borcatloop =>\@borcatloop,
);
output_html_with_http_headers $input, $cookie, $template->output;
}
while (my ($celvalue) = $sth2->fetchrow) {
my %cell;
- # my %ft;
- # warn "coltitle :".$celvalue;
- $cell{coltitle} = $celvalue;
- # $ft{totalcol} = 0;
+ $cell{coltitle} = ($celvalue?$celvalue:"NULL");
push @loopcol, \%cell;
}
# warn "fin des titres colonnes";
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"}=0;
+ $table[$i]->{($col->{coltitle})?$col->{coltitle}:"total"}->{'name'}=0;
}
}
my $strcalc ;
# Processing average loanperiods
- $strcalc .= "SELECT CONCAT( biblioitems.biblioitemnumber) , COUNT(*) AS RANK";
+ $strcalc .= "SELECT DISTINCT biblio.title, COUNT(biblio.biblionumber) AS RANK, biblio.biblionumber AS ID";
$strcalc .= " , $colfield " if ($colfield);
- $strcalc .= " FROM `issues`,borrowers,biblioitems LEFT JOIN items ON (biblioitems.biblioitemnumber=items.biblioitemnumber) LEFT JOIN issuingrules ON (issuingrules.branchcode=issues.branchcode AND issuingrules.itemtype=biblioitems.itemtype AND issuingrules.categorycode=borrowers.categorycode) WHERE issues.itemnumber=items.itemnumber AND issues.borrowernumber=borrowers.borrowernumber and returndate is not null";
+ $strcalc .= " FROM `issues`,borrowers,(items LEFT JOIN biblioitems ON biblioitems.biblioitemnumber=items.biblioitemnumber) LEFT JOIN biblio ON (biblio.biblionumber=items.biblionumber) 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[9]=~ s/\*/%/g if (@$filters[9]);
$strcalc .= " AND year(issues.timestamp) like '" . @$filters[9] ."'" if ( @$filters[9] );
- $strcalc .= " group by borrowers.borrowernumber";
+ $strcalc .= " group by biblio.biblionumber";
$strcalc .= ", $colfield" if ($column);
- $strcalc .= " order by ";
- $strcalc .= "$colfield, " if ($colfield);
- $strcalc .= "RANK DESC ";
- my $max;
- if (@loopcol) {
- $max = $line*@loopcol;
- } else { $max=$line;}
- $strcalc .= " LIMIT 0,$max";
+ $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;
my $dbcalc = $dbh->prepare($strcalc);
$dbcalc->execute;
# warn "filling table";
my $previous_col;
- my $i=1;
+ my %indice;
while (my @data = $dbcalc->fetchrow) {
- my ($row, $rank, $col )=@data;
-# warn "filling table $row / $col / $issuedate / $returndate /$weight";
+ my ($row, $rank, $id, $col )=@data;
$col = "zzEMPTY" if ($col eq undef);
- $table[$i]->{$col}=$row;
- warn " ".$i." ".$col. " ".$row;
- $i++;
- $i=1 if (($previous_col) and not($col eq $previous_col));
- $previous_col=$col;
-# $table{$row}->{totalrow}+=$weight*$loanlength;
+ $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}++;
}
- push @loopcol,{coltitle => "Global"};
+ push @loopcol,{coltitle => "Global"} if not($column);
for ($i=1; $i<=$line;$i++) {
- warn " ".$i;
my @loopcell;
+ warn " $i";
#@loopcol 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){
- #warn " test ".(($col->{coltitle} eq "NULL") or ($col->{coltitle} eq "Global"))?"zzEMPTY":$col->{coltitle};
- $value =$table[$i]->{(($col->{coltitle} eq "NULL") or ($col->{coltitle} eq "Global"))?"zzEMPTY":$col->{coltitle}};
+ $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"};
+ $value =$table[$i]->{"zzEMPTY"}->{'name'};
+ $count =$table[$i]->{"zzEMPTY"}->{'count'};
+ $link =$table[$i]->{"zzEMPTY"}->{'link'};
}
-# $table{$row}->{(($col->{coltitle} eq "NULL")or ($col->{coltitle} eq ""))?"zzEMPTY":$col->{coltitle}} = $value;
- #$table{$row}->{totalrow}+=$value;
- #warn "row : $row col:$col $cnttable{$row}->{(($col->{coltitle} eq \"NULL\")or ($col->{coltitle} eq \"\"))?\"zzEMPTY\":$col->{coltitle}}";
-# $colcount+=$cnttable{$row}->{(($col->{coltitle} eq "NULL")or ($col->{coltitle} eq ""))?"zzEMPTY":$col->{coltitle}};
- push @loopcell, {value => $value} ;
+# warn " ".$i ." value:$value count:$count reference:$link";
+ push @loopcell, {value => $value, count =>$count, reference => $link} ;
}
#warn "row : $row colcount:$colcount";
#my $total = $table[$i]->{totalrow}/$colcount if ($colcount>0);