+ $cell{filter} = format_date(@$filters[$i]);
+ }
+ $cell{crit} =
+ ( $i == 0 ) ? "Period From"
+ : ( $i == 1 ) ? "Period To"
+ : ( $i == 2 ) ? "Patron Category ="
+ : ( $i == 3 ) ? "Item Type ="
+ : ( $i == 4 ) ? "Library ="
+ : ( $i == 5 ) ? "Collection ="
+ : ( $i == 6 ) ? "Location ="
+ : ( $i == 7 ) ? "Item callnumber >="
+ : ( $i == 8 ) ? "Item callnumber <"
+ : ( $i == 9 ) ? "sort1 ="
+ : ( $i == 10 ) ? "sort2 ="
+ : ( $i == 11 ) ? "Home library ="
+ : ( $i == 12 )? "Holding library ="
+ : "UNKNOWN FILTER ($i)";
+
+ # FIXME - no translation mechanism !
+ push @loopfilter, \%cell;
+ }
+ push @loopfilter,{crit=>"Event", filter=>$type };
+ push @loopfilter,{crit=>"Display by", filter=>$dsp } if ($dsp);
+ push @loopfilter,{crit=>"Select Day", filter=>$daysel } if ($daysel);
+ push @loopfilter,{crit=>"Select Month",filter=>$monthsel} if ($monthsel);
+
+ my @linefilter;
+ $debug and warn "filtres ". join "|", @filters;
+ my ($colsource, $linesource);
+ $linefilter[1] = @$filters[1] if ($line =~ /datetime/);
+ $linefilter[0] =
+ ( $line =~ /datetime/ ) ? @$filters[0]
+ : ( $line =~ /category/ ) ? @$filters[2]
+ : ( $line =~ /itemtype/ ) ? @$filters[3]
+ : ( $line =~ /^branch/ ) ? @$filters[4]
+ : ( $line =~ /ccode/ ) ? @$filters[5]
+ : ( $line =~ /location/ ) ? @$filters[6]
+ : ( $line =~ /sort1/ ) ? @$filters[9]
+ : ( $line =~ /sort2/ ) ? @$filters[10]
+ : ( $line =~ /homebranch/) ? @$filters[11]
+ : ( $line =~ /holdingbranch/) ? @$filters[12]
+ : undef;
+
+ if ( $line =~ /ccode/ or $line =~ /location/ or $line =~ /homebranch/ or $line =~ /holdingbranch/ ) {
+ $linesource = 'items';
+ }
+
+ my @colfilter;
+ $colfilter[1] = @$filters[1] if ($column =~ /datetime/);
+ $colfilter[0] =
+ ( $column =~ /datetime/ ) ? @$filters[0]
+ : ( $column =~ /category/ ) ? @$filters[2]
+ : ( $column =~ /itemtype/ ) ? @$filters[3]
+ : ( $column =~ /^branch/ ) ? @$filters[4]
+ : ( $column =~ /ccode/ ) ? @$filters[5]
+ : ( $column =~ /location/ ) ? @$filters[6]
+ : ( $column =~ /sort1/ ) ? @$filters[9]
+ : ( $column =~ /sort1/ ) ? @$filters[10]
+ : ( $column =~ /homebranch/) ? @$filters[11]
+ : ( $column =~ /holdingbranch/) ? @$filters[12]
+ : undef;
+
+ if ( $column =~ /ccode/ or $column =~ /location/ or $column =~ /homebranch/ or $column =~ /holdingbranch/ ) {
+ $colsource = 'items';
+ }
+# 1st, loop rows.
+ my $linefield;
+ if ($line =~ /datetime/) {
+ # by Day, Month or Year (1,2,3 respectively)
+ $linefield = ($dsp == 1) ? " dayname($line)" :
+ ($dsp == 2) ? "monthname($line)" :
+ ($dsp == 3) ? " Year($line)" :
+ 'date_format(`datetime`,"%Y-%m-%d")'; # Probably should be left alone or passed through C4::Dates
+ } else {
+ $linefield = $line;
+ }
+ my $lineorder = ($linefield =~ /dayname/) ? "weekday($line)" :
+ ($linefield =~ /^month/ ) ? " month($line)" : $linefield;
+
+ my $strsth = "SELECT distinctrow $linefield FROM statistics, ";
+ # get stats on items if ccode or location, otherwise borrowers.
+ $strsth .= ($linesource eq 'items' ) ?
+ " items WHERE (statistics.itemnumber=items.itemnumber) " :
+ " borrowers WHERE (statistics.borrowernumber=borrowers.borrowernumber) ";
+ $strsth .= " AND $line is not null ";
+
+ if ($line =~ /datetime/) {
+ if ($linefilter[1] and ($linefilter[0])) {
+ $strsth .= " AND $line between ? AND ? ";
+ } elsif ($linefilter[1]) {
+ $strsth .= " AND $line < ? ";
+ } elsif ($linefilter[0]) {
+ $strsth .= " AND $line > ? ";
+ }
+ $strsth .= " AND type ='".$type."' " if $type;
+ $strsth .= " AND dayname(datetime) ='". $daysel ."' " if $daysel;
+ $strsth .= " AND monthname(datetime) ='". $monthsel ."' " if $monthsel;
+ } elsif ($linefilter[0]) {
+ $linefilter[0] =~ s/\*/%/g;
+ $strsth .= " AND $line LIKE ? ";
+ }
+ $strsth .=" group by $linefield order by $lineorder ";
+ $debug and warn $strsth;
+ push @loopfilter, {crit=>'SQL =', sql=>1, filter=>$strsth};
+ my $sth = $dbh->prepare( $strsth );
+ if ((@linefilter) and ($linefilter[1])){
+ $sth->execute($linefilter[0],$linefilter[1]);
+ } elsif ($linefilter[0]) {
+ $sth->execute($linefilter[0]);
+ } else {
+ $sth->execute;
+ }
+
+ while (my ($celvalue) = $sth->fetchrow) {
+ my %cell = (rowtitle => $celvalue, totalrow => 0); # we leave 'rowtitle' as hash key (used when filling the table), and add coltitle_display
+ $cell{rowtitle_display} =
+ ($line =~ /ccode/ ) ? $ccodes->{$celvalue} :
+ ($line =~ /location/) ? $locations->{$celvalue} :
+ ($line =~ /itemtype/) ? $itemtypes->{$celvalue}->{description} :
+ $celvalue; # default fallback
+ if ($line =~ /sort1/) {
+ foreach (@$Bsort1) {
+ ($celvalue eq $_->{authorised_value}) or next;
+ $cell{rowtitle_display} = $_->{lib} and last;
+ }
+ } elsif ($line =~ /sort2/) {
+ foreach (@$Bsort2) {
+ ($celvalue eq $_->{authorised_value}) or next;
+ $cell{rowtitle_display} = $_->{lib} and last;
+ }
+ } elsif ($line =~ /category/) {
+ foreach (@$categoryloop) {
+ ($celvalue eq $_->{categorycode}) or next;
+ $cell{rowtitle_display} = $_->{description} and last;
+ }
+ }
+ push @loopline, \%cell;
+ }