- 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 ) {
- my $value =$table{$row}->{($col->{coltitle} eq "NULL")?"zzEMPTY":$col->{coltitle}};
- push @loopcell, {value => $value } ;
- }
- push @looprow,{ 'rowtitle' => ($row eq "zzEMPTY")?"NULL":$row,
- 'loopcell' => \@loopcell,
- 'hilighted' => ($hilighted >0),
- 'totalrow' => $table{$row}->{totalrow}
- };
- $hilighted = -$hilighted;
- }
-
-# warn "footer processing";
- foreach my $col ( @loopcol ) {
- my $total=0;
- 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};
- }
-
+ # warn "fin des titres colonnes";
+
+ my $i = 0;
+ my @totalcol;
+ my $hilighted = -1;
+
+ #Initialization of cell values.....
+ my %table;
+
+# warn "init table...\n";
+ foreach my $row (@loopline) {
+ foreach my $col (@loopcol) {
+ #warn " init table : $row->{rowtitle} / $col->{coltitle} \n";
+ $table{ $row->{rowtitle} }->{ $col->{coltitle} } = 0;
+ }
+ $table{ $row->{rowtitle} }->{totalrow} = 0;
+ }
+
+ # preparing calculation
+ my $strcalc;
+ $strcalc .= "SELECT $linefield, $colfield, ";
+ $strcalc .= "SUM( aqorders.quantity ) " if ( $process == 1 );
+ $strcalc .= "SUM( aqorders.quantity * aqorders.listprice ) "
+ if ( $process == 2 );
+ $strcalc .= "FROM (aqorders, aqbasket,aqorderbreakdown)
+ LEFT JOIN items ON (aqorders.biblionumber= items.biblionumber)
+ LEFT JOIN biblioitems ON (aqorders.biblionumber= biblioitems.biblionumber)
+ LEFT JOIN aqbooksellers ON (aqbasket.booksellerid=aqbooksellers.id) WHERE (aqorders.basketno=aqbasket.basketno)
+ AND (aqorderbreakdown.ordernumber=aqorders.ordernumber) ";
+
+# LEFT JOIN aqorderdelivery ON (aqorders.ordernumber =aqorderdelivery.ordernumber )
+
+ @$filters[0] =~ s/\*/%/g if ( @$filters[0] );
+ $strcalc .= " AND aqbasket.closedate >= '" . @$filters[0] . "'"
+ if ( @$filters[0] );
+ @$filters[1] =~ s/\*/%/g if ( @$filters[1] );
+ $strcalc .= " AND aqbasket.closedate <= '" . @$filters[1] . "'"
+ if ( @$filters[1] );
+ @$filters[2] =~ s/\*/%/g if ( @$filters[2] );
+ $strcalc .= " AND aqorders.datereceived >= '" . @$filters[2] . "'"
+ if ( @$filters[2] );
+ @$filters[3] =~ s/\*/%/g if ( @$filters[3] );
+ $strcalc .= " AND aqorders.datereceived <= '" . @$filters[3] . "'"
+ if ( @$filters[3] );
+# @$filters[4] =~ s/\*/%/g if ( @$filters[4] );
+# $strcalc .= " AND aqbasket.closedate >= '" . @$filters[4] . "'"
+# if ( @$filters[4] );
+# @$filters[5] =~ s/\*/%/g if ( @$filters[5] );
+# $strcalc .= " AND aqbasket.closedate <= '" . @$filters[5] . "'"
+# if ( @$filters[5] );
+ @$filters[4] =~ s/\*/%/g if ( @$filters[4] );
+ $strcalc .= " AND aqbooksellers.name 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 aqorderbreakdown.bookfundid LIKE '" . @$filters[6] . "'"
+ if ( @$filters[6] );
+ @$filters[7] =~ s/\*/%/g if ( @$filters[7] );
+ $strcalc .= " AND aqorders.sort1 LIKE '" . @$filters[7] . "'"
+ if ( @$filters[7] );
+ @$filters[8] =~ s/\*/%/g if ( @$filters[8] );
+ $strcalc .= " AND aqorders.sort2 LIKE '" . @$filters[8] . "'"
+ if ( @$filters[8] );
+ $strcalc .= " GROUP BY $linefield, $colfield ORDER BY $linefield,$colfield";
+
+# warn $strcalc . "\n";
+ my $dbcalc = $dbh->prepare($strcalc);
+ $dbcalc->execute;
+
+ # warn "filling table";
+ my $emptycol;
+ while ( my ( $row, $col, $value ) = $dbcalc->fetchrow ) {
+ next if ($row eq undef || $col eq undef);
+ #warn "filling table $row / $col / $value ";
+ $emptycol = 1 if ( !defined($col) );
+ $col = "zzEMPTY" if ( !defined($col) );
+ $row = "zzEMPTY" if ( !defined($row) );
+
+ $table{$row}->{$col} += $value;
+ $table{$row}->{totalrow} += $value;
+ $grantotal += $value;
+ }
+
+ 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) {
+ my $value = $table{$row}->{ ( $col->{coltitle} eq "NULL" ) ? "zzEMPTY" : $col->{coltitle} };
+ push @loopcell, { value => $value };
+ }
+ push @looprow,
+ {
+ 'rowtitle' => ( $row eq "zzEMPTY" ) ? "NULL" : $row,
+ 'loopcell' => \@loopcell,
+ 'hilighted' => ( $hilighted > 0 ),
+ 'totalrow' => $table{$row}->{totalrow}
+ };
+ $hilighted = -$hilighted;
+ }
+
+ # warn "footer processing";
+ foreach my $col (@loopcol) {
+ my $total = 0;
+ 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 };
+ }