-# 2nd, loop cols.
- my $colfield;
- my $colorder;
- if ($column =~ /datetime/) {
- #Display by Day, Month or Year (1,2,3 respectively)
- $colfield = ($dsp == 1) ? " dayname($column)" :
- ($dsp == 2) ? "monthname($column)" :
- ($dsp == 3) ? " Year($column)" :
- 'date_format(`datetime`,"%Y-%m-%d")'; # Probably should be left alone or passed through C4::Dates
- } else {
- $colfield = $column;
- }
- $colorder = ($colfield =~ /dayname/) ? "weekday($line)" :
- ($colfield =~ /^month/ ) ? " month($line)" : $colfield;
- my $strsth2 = "SELECT distinctrow $colfield FROM statistics, ";
- # get stats on items if ccode or location, otherwise borrowers.
- $strsth2 .= ($colsource eq 'items' ) ?
- "items WHERE (statistics.itemnumber=items.itemnumber) " :
- "borrowers WHERE (statistics.borrowernumber=borrowers.borrowernumber) ";
- $strsth2 .= " AND $column IS NOT NULL ";
-
- if ($column =~ /datetime/){
- if (($colfilter[1]) and ($colfilter[0])){
- $strsth2 .= " AND $column BETWEEN ? AND ? " ;
- } elsif ($colfilter[1]) {
- $strsth2 .= " AND $column < ? " ;
- } elsif ($colfilter[0]) {
- $strsth2 .= " AND $column > ? " ;
+ 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[0]) and ($linefilter[1]) ) {
+ $sth->execute( $linefilter[0], $linefilter[1] . " 23:59:59" );
+ } elsif ( $linefilter[1] ) {
+ $sth->execute( $linefilter[1] . " 23:59:59" );
+ } elsif ( $linefilter[0] ) {
+ $sth->execute( $linefilter[0] );
+ } else {
+ $sth->execute;
+ }
+
+ my $itemtypes_map = { map { $_->{itemtype} => $_ } @{ $itemtypes } };
+ 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_map->{$celvalue}->{translated_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 my $patron_category ( @patron_categories ) {
+ ($celvalue eq $patron_category->categorycode) or next;
+ $cell{rowtitle_display} = $patron_category->description and last;
+ }
+ }
+ push @loopline, \%cell;
+ }
+
+ # 2nd, loop cols.
+ my $colfield;
+ my $colorder;
+ if ( $column =~ /datetime/ ) {
+
+ #Display by Day, Month or Year (1,2,3 respectively)
+ $colfield =
+ ( $dsp == 1 ) ? " dayname($column)"
+ : ( $dsp == 2 ) ? "monthname($column)"
+ : ( $dsp == 3 ) ? " Year($column)"
+ : ( $dsp == 4 ) ? "extract(hour from $column)"
+ : 'date_format(`datetime`,"%Y-%m-%d")'; # Probably should be left alone or passed through Koha::Dates
+ } else {
+ $colfield = $column;
+ }
+ $colorder =
+ ( $colfield =~ /dayname/ ) ? "weekday($column)"
+ : ( $colfield =~ /^month/ ) ? " month($column)"
+ : $colfield;
+ my $strsth2;
+ if($column_attribute_type) {
+ $strsth2 = "SELECT attribute FROM borrower_attributes WHERE code = '$column_attribute_type' ";
+ } else {
+ $strsth2 = "SELECT distinctrow $colfield FROM statistics ";
+
+ # get stats on items if ccode or location, otherwise borrowers.
+ $strsth2 .=
+ ( $colsource eq 'items' )
+ ? "LEFT JOIN items ON (statistics.itemnumber = items.itemnumber) "
+ : "LEFT JOIN borrowers ON (statistics.borrowernumber = borrowers.borrowernumber) ";
+ $strsth2 .= " WHERE $column IS NOT NULL AND $column != '' ";
+ }
+
+ if ( $column =~ /datetime/ ) {
+ if ( ( $colfilter[1] ) and ( $colfilter[0] ) ) {
+ $strsth2 .= " AND $column BETWEEN ? AND ? ";
+ } elsif ( $colfilter[1] ) {
+ $strsth2 .= " AND $column <= ? ";
+ } elsif ( $colfilter[0] ) {
+ $strsth2 .= " AND $column >= ? ";