- ";
- $strcalc .= "LEFT JOIN items ON statistics.itemnumber=items.itemnumber "
- if ($linefield =~ /^items\./ or $colfield =~ /^items\./ or ($colsource eq 'items')
- ||@$filters[5]||@$filters[6]||@$filters[7]||@$filters[8]);
-
- $strcalc .= "WHERE 1=1 ";
- @$filters = map {defined($_) and s/\*/%/g; $_} @$filters;
- $strcalc .= " AND statistics.datetime > '" . @$filters[0] ."'" if (@$filters[0] );
- $strcalc .= " AND statistics.datetime < '" . @$filters[1] ."'" if (@$filters[1] );
- $strcalc .= " AND borrowers.categorycode LIKE '" . @$filters[2] ."'" if (@$filters[2] );
- $strcalc .= " AND statistics.itemtype LIKE '" . @$filters[3] ."'" if (@$filters[3] );
- $strcalc .= " AND statistics.branch LIKE '" . @$filters[4] ."'" if (@$filters[4] );
- $strcalc .= " AND items.ccode LIKE '" . @$filters[5] ."'" if (@$filters[5] );
- $strcalc .= " AND items.location LIKE '" . @$filters[6] ."'" if (@$filters[6] );
- $strcalc .= " AND items.itemcallnumber >='" . @$filters[7] ."'" if (@$filters[7] );
- $strcalc .= " AND items.itemcallnumber <'" . @$filters[8] ."'" if (@$filters[8] );
- $strcalc .= " AND borrowers.sort1 LIKE '" . @$filters[9] ."'" if (@$filters[9] );
- $strcalc .= " AND borrowers.sort2 LIKE '" . @$filters[10]."'" if (@$filters[10]);
- $strcalc .= " AND dayname(datetime) LIKE '" . $daysel ."'" if ($daysel );
- $strcalc .= " AND monthname(datetime) LIKE '" . $monthsel ."'" if ($monthsel);
- $strcalc .= " AND statistics.type LIKE '" . $type ."'" if ($type );
-
- $strcalc .= " GROUP BY $linefield, $colfield order by $lineorder,$colorder";
- ($debug) and warn $strcalc;
- my $dbcalc = $dbh->prepare($strcalc);
- push @loopfilter, {crit=>'SQL =', sql=>1, filter=>$strcalc};
- $dbcalc->execute;
- my ($emptycol,$emptyrow);
- while (my ($row, $col, $value) = $dbcalc->fetchrow) {
- ($debug) and warn "filling table $row / $col / $value ";
- unless (defined $col) {
- $emptycol = 1;
- $col = "zzEMPTY" ;
- }
- unless (defined $row) {
- $emptyrow = 1;
- $row = "zzEMPTY";
- }
- $table{$row}->{$col} += $value;
- $table{$row}->{totalrow} += $value;
- $grantotal += $value;
- }
- push @loopcol, {coltitle => "NULL", coltitle_display => 'NULL'} if ($emptycol);
- push @loopline,{rowtitle => "NULL", rowtitle_display => 'NULL'} if ($emptyrow);
-
- foreach my $row (@loopline) {
- 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{null_to_zzempty($row->{rowtitle})}->{null_to_zzempty($col->{coltitle})};
- push @loopcell, {value => $value};
- }
- my $rowtitle = ($row->{rowtitle} eq "NULL") ? "zzEMPTY" : $row->{rowtitle};
- push @looprow, {
- 'rowtitle_display' => $row->{rowtitle_display},
- 'rowtitle' => $rowtitle,
- 'loopcell' => \@loopcell,
- 'totalrow' => $table{$rowtitle}->{totalrow}
- };
- }
- for my $col ( @loopcol ) {
- my $total = 0;
- foreach my $row (@looprow) {
- $total += $table{null_to_zzempty($row->{rowtitle})}->{null_to_zzempty($col->{coltitle})};
- $debug and warn "value added ".$table{$row->{rowtitle}}->{$col->{coltitle}}. "for line ".$row->{rowtitle};
- }
- push @loopfooter, {'totalcol' => $total};
- }
+ ";
+ foreach (keys %$attribute_filters) {
+ if(
+ ($line_attribute_type and $line_attribute_type eq $_)
+ or $column_attribute_type and $column_attribute_type eq $_
+ or $attribute_filters->{$_}
+ ) {
+ $strcalc .= " LEFT JOIN borrower_attributes AS attribute_$_ ON (statistics.borrowernumber = attribute_$_.borrowernumber AND attribute_$_.code = '$_') ";
+ }
+ }
+ $strcalc .= "LEFT JOIN items ON statistics.itemnumber=items.itemnumber "
+ if ( $linefield =~ /^items\./
+ or $colfield =~ /^items\./
+ or $process == 5
+ or ( $colsource eq 'items' ) || @$filters[5] || @$filters[6] || @$filters[7] || @$filters[8] || @$filters[9] || @$filters[10] || @$filters[11] || @$filters[12] || @$filters[13] );
+
+ $strcalc .= "WHERE 1=1 ";
+ @$filters = map { defined($_) and s/\*/%/g; $_ } @$filters;
+ $strcalc .= " AND statistics.datetime >= '" . @$filters[0] . "'" if ( @$filters[0] );
+ $strcalc .= " AND statistics.datetime <= '" . @$filters[1] . " 23:59:59'" if ( @$filters[1] );
+ $strcalc .= " AND borrowers.categorycode LIKE '" . @$filters[2] . "'" if ( @$filters[2] );
+ $strcalc .= " AND statistics.itemtype LIKE '" . @$filters[3] . "'" if ( @$filters[3] );
+ $strcalc .= " AND statistics.branch LIKE '" . @$filters[4] . "'" if ( @$filters[4] );
+ $strcalc .= " AND items.ccode LIKE '" . @$filters[5] . "'" if ( @$filters[5] );
+ $strcalc .= " AND items.location LIKE '" . @$filters[6] . "'" if ( @$filters[6] );
+ $strcalc .= " AND items.itemcallnumber >='" . @$filters[7] . "'" if ( @$filters[7] );
+ $strcalc .= " AND items.itemcallnumber <'" . @$filters[8] . "'" if ( @$filters[8] );
+ $strcalc .= " AND borrowers.sort1 LIKE '" . @$filters[9] . "'" if ( @$filters[9] );
+ $strcalc .= " AND borrowers.sort2 LIKE '" . @$filters[10] . "'" if ( @$filters[10] );
+ $strcalc .= " AND items.homebranch LIKE '" . @$filters[11] . "'" if ( @$filters[11] );
+ $strcalc .= " AND items.holdingbranch LIKE '" . @$filters[12] . "'" if ( @$filters[12] );
+ $strcalc .= " AND borrowers.branchcode LIKE '" . @$filters[13] . "'" if ( @$filters[13] );
+ $strcalc .= " AND dayname(datetime) LIKE '" . $daysel . "'" if ($daysel);
+ $strcalc .= " AND monthname(datetime) LIKE '" . $monthsel . "'" if ($monthsel);
+ $strcalc .= " AND statistics.type LIKE '" . $type . "'" if ($type);
+ foreach (keys %$attribute_filters) {
+ if($attribute_filters->{$_}) {
+ $strcalc .= " AND attribute_$_.attribute LIKE '" . $attribute_filters->{$_} . "'";
+ }
+ }
+
+ $strcalc .= " GROUP BY ";
+ if($line_attribute_type) {
+ $strcalc .= " line_attribute, ";
+ } else {
+ $strcalc .= " $linefield, ";
+ }
+ if($column_attribute_type) {
+ $strcalc .= " column_attribute ";
+ } else {
+ $strcalc .= " $colfield ";
+ }
+
+ $strcalc .= " ORDER BY ";
+ if($line_attribute_type) {
+ $strcalc .= " line_attribute, ";
+ } else {
+ $strcalc .= " $lineorder, ";
+ }
+ if($column_attribute_type) {
+ $strcalc .= " column_attribute ";
+ } else {
+ $strcalc .= " $colorder ";
+ }
+
+ ($debug) and warn $strcalc;
+ my $dbcalc = $dbh->prepare($strcalc);
+ push @loopfilter, { crit => 'SQL =', sql => 1, filter => $strcalc };
+ $dbcalc->execute;
+ my ( $emptycol, $emptyrow );
+ while ( my ( $row, $col, $value ) = $dbcalc->fetchrow ) {
+ ($debug) and warn "filling table $row / $col / $value ";
+ unless ( defined $col ) {
+ $emptycol = 1;
+ }
+ unless ( defined $row ) {
+ $emptyrow = 1;
+ }
+ table_inc(\%table, $row, $col, $value);
+ table_inc(\%table, $row, 'totalrow', $value);
+ $grantotal += $value;
+ }
+ push @loopcol, { coltitle => "NULL", coltitle_display => 'NULL' } if ($emptycol);
+ push @loopline, { rowtitle => "NULL", rowtitle_display => 'NULL' } if ($emptyrow);
+
+ foreach my $row (@loopline) {
+ 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_get(\%table, $row->{rowtitle}, $col->{coltitle});
+ push @loopcell, { value => $value };
+ }
+ push @looprow,
+ { 'rowtitle_display' => $row->{rowtitle_display},
+ 'rowtitle' => $row->{rowtitle},
+ 'loopcell' => \@loopcell,
+ 'totalrow' => table_get(\%table, $row->{rowtitle}, 'totalrow'),
+ };
+ }
+ for my $col (@loopcol) {
+ my $total = 0;
+ foreach my $row (@looprow) {
+ $total += table_get(\%table, $row->{rowtitle}, $col->{coltitle}) || 0;
+ $debug and warn "value added " . table_get(\%table, $row->{rowtitle}, $col->{coltitle}) . "for line " . $row->{rowtitle};
+ }
+ push @loopfooter, { 'totalcol' => $total };
+ }
+
+ # the header of the table
+ $globalline{loopfilter} = \@loopfilter;
+
+ # the core of the table
+ $globalline{looprow} = \@looprow;
+ $globalline{loopcol} = \@loopcol;