+ my $strsth = "SELECT DISTINCTROW $linefield FROM $itemstable
+ LEFT JOIN biblioitems USING (biblioitemnumber)
+ LEFT JOIN biblio ON (biblioitems.biblionumber = biblio.biblionumber)
+ WHERE 1 ";
+ $strsth .= " AND barcode $not LIKE ? " if ($barcodefilter);
+ if (@linefilter) {
+ if ( $linefilter[1] ) {
+ $strsth .= " AND $line >= ? ";
+ $strsth .= " AND $line <= ? ";
+ } elsif ( defined $linefilter[0] and $linefilter[0] ne '' ) {
+ $linefilter[0] =~ s/\*/%/g;
+ $strsth .= " AND $line LIKE ? ";
+ }
+ }
+ $strsth .= " ORDER BY $linefield";
+ $debug and print STDERR "catalogue_stats SQL: $strsth\n";
+
+ my $sth = $dbh->prepare($strsth);
+ if ( $barcodefilter and (@linefilter) and ( $linefilter[1] ) ) {
+ $sth->execute( $barcodefilter, $linefilter[0], $linefilter[1] );
+ } elsif ( (@linefilter) and ( $linefilter[1] ) ) {
+ $sth->execute( $linefilter[0], $linefilter[1] );
+ } elsif ( $barcodefilter and $linefilter[0] ) {
+ $sth->execute( $barcodefilter, $linefilter[0] );
+ } elsif ( $linefilter[0] ) {
+ $sth->execute($linefilter[0]);
+ } elsif ($barcodefilter) {
+ $sth->execute($barcodefilter);
+ } else {
+ $sth->execute();
+ }
+ my $rowauthvals = { map { $_->{authorised_value} => $_->{lib} } Koha::AuthorisedValues->get_descriptions_by_koha_field( { frameworkcode => '', kohafield => $origline } ) };
+ while ( my ($celvalue) = $sth->fetchrow ) {
+ my %cell;
+ if (defined $celvalue and $celvalue ne '') {
+ if($rowauthvals and $rowauthvals->{$celvalue}) {
+ $cell{rowtitle} = $rowauthvals->{$celvalue};
+ } else {
+ $cell{rowtitle} = $celvalue;
+ }
+ $cell{value} = $celvalue;
+ }
+ else {
+ $cell{rowtitle} = "NULL";
+ $cell{value} = "zzEMPTY";
+ }
+ $cell{totalrow} = 0;
+ push @loopline, \%cell;
+ }
+
+ # 2nd, loop cols.
+ my $origcolumn = $column;
+ $column =~ s/^items\./deleteditems./ if($cellvalue eq "deleteditems");
+ my $colfield;
+ if ( ( $column =~ /itemcallnumber/ ) and ($cotedigits) ) {
+ $colfield = "left($column,$cotedigits)";
+ } elsif ( $column =~ /^deleteditems\.timestamp$/ ) {
+ $colfield = "DATE($column)";
+ } else {
+ $colfield = $column;
+ }
+
+ my $strsth2 = "
+ SELECT distinctrow $colfield
+ FROM $itemstable
+ LEFT JOIN biblioitems
+ USING (biblioitemnumber)
+ LEFT JOIN biblio
+ ON (biblioitems.biblionumber = biblio.biblionumber)
+ WHERE 1 ";
+ $strsth2 .= " AND barcode $not LIKE ?" if $barcodefilter;
+
+ if ( (@colfilter) and ( $colfilter[1] ) ) {
+ $strsth2 .= " AND $column >= ? AND $column <= ?";
+ } elsif ( defined $colfilter[0] and $colfilter[0] ne '' ) {
+ $colfilter[0] =~ s/\*/%/g;
+ $strsth2 .= " AND $column LIKE ? ";
+ }
+ $strsth2 .= " ORDER BY $colfield";
+ $debug and print STDERR "SQL: $strsth2";
+ my $sth2 = $dbh->prepare($strsth2);
+ if ( $barcodefilter and (@colfilter) and ( $colfilter[1] ) ) {
+ $sth2->execute( $barcodefilter, $colfilter[0], $colfilter[1] );
+ } elsif ( (@colfilter) and ( $colfilter[1] ) ) {
+ $sth2->execute( $colfilter[0], $colfilter[1] );
+ } elsif ( $barcodefilter && $colfilter[0] ) {
+ $sth2->execute( $barcodefilter , $colfilter[0] );
+ } elsif ( $colfilter[0]) {
+ $sth2->execute( $colfilter[0] );
+ } elsif ($barcodefilter) {
+ $sth2->execute($barcodefilter);
+ } else {
+ $sth2->execute();
+ }
+ my $colauthvals = { map { $_->{authorised_value} => $_->{lib} } Koha::AuthorisedValues->get_descriptions_by_koha_field( { frameworkcode => '', kohafield => $origcolumn } ) };
+ while ( my ($celvalue) = $sth2->fetchrow ) {
+ my %cell;
+ if (defined $celvalue and $celvalue ne '') {
+ if($colauthvals and $colauthvals->{$celvalue}) {
+ $cell{coltitle} = $colauthvals->{$celvalue};
+ } else {
+ $cell{coltitle} = $celvalue;
+ }
+ $cell{value} = $celvalue;
+ }
+ else {
+ $cell{coltitle} = "NULL";
+ $cell{value} = "zzEMPTY";
+ }
+ $cell{totalcol} = 0;
+ push @loopcol, \%cell;
+ }
+
+ my $i = 0;
+ my $hilighted = -1;
+
+ #Initialization of cell values.....
+ my %table;
+
+ foreach my $row (@loopline) {
+ foreach my $col (@loopcol) {
+ $table{ $row->{value} }->{ $col->{value} } = 0;
+ }
+ $table{ $row->{value} }->{totalrow} = 0;
+ }
+
+ # preparing calculation
+ my $select_cellvalue = " COUNT(*) ";
+ $select_cellvalue = " COUNT(DISTINCT biblioitems.biblionumber) " if($cellvalue eq 'biblios');
+ my $strcalc = "
+ SELECT $linefield, $colfield, $select_cellvalue
+ FROM $itemstable
+ LEFT JOIN biblioitems ON ($itemstable.biblioitemnumber = biblioitems.biblioitemnumber)
+ LEFT JOIN biblio ON (biblioitems.biblionumber = biblio.biblionumber)
+ WHERE 1 ";
+
+ my @sqlargs;
+
+ if ($barcodefilter) {
+ $strcalc .= "AND barcode $not like ? ";
+ push @sqlargs, $barcodefilter;
+ }
+
+ if ( @$filters[0] ) {
+ $strcalc .= " AND $itemstable.itemcallnumber >= ? ";
+ @$filters[0] =~ s/\*/%/g;
+ push @sqlargs, @$filters[0];
+ }
+
+ if ( @$filters[1] ) {
+ $strcalc .= " AND $itemstable.itemcallnumber <= ? ";
+ @$filters[1] =~ s/\*/%/g;
+ push @sqlargs, @$filters[1];
+ }
+
+ if ( @$filters[2] ) {
+ $strcalc .= " AND " . ( C4::Context->preference('item-level_itypes') ? "$itemstable.itype" : 'biblioitems.itemtype' ) . " LIKE ? ";
+ @$filters[2] =~ s/\*/%/g;
+ push @sqlargs, @$filters[2];
+ }
+
+ if ( @$filters[3] ) {
+ $strcalc .= " AND biblioitems.publishercode LIKE ? ";
+ @$filters[3] =~ s/\*/%/g;
+ @$filters[3] .= "%" unless @$filters[3] =~ /%/;
+ push @sqlargs, @$filters[3];
+ }
+ if ( @$filters[4] ) {
+ $strcalc .= " AND " .
+ (C4::Context->preference('marcflavour') eq 'UNIMARC' ? 'publicationyear' : 'copyrightdate')
+ . "> ? ";
+ @$filters[4] =~ s/\*/%/g;
+ push @sqlargs, @$filters[4];
+ }
+ if ( @$filters[5] ) {
+ @$filters[5] =~ s/\*/%/g;
+ $strcalc .= " AND " .
+ (C4::Context->preference('marcflavour') eq 'UNIMARC' ? 'publicationyear' : 'copyrightdate')
+ . "< ? ";
+ push @sqlargs, @$filters[5];
+ }
+ if ( @$filters[6] ) {
+ $strcalc .= " AND $itemstable.homebranch LIKE ? ";
+ @$filters[6] =~ s/\*/%/g;
+ push @sqlargs, @$filters[6];
+ }
+ if ( @$filters[7] ) {
+ $strcalc .= " AND $itemstable.location LIKE ? ";
+ @$filters[7] =~ s/\*/%/g;
+ push @sqlargs, @$filters[7];
+ }
+ if ( @$filters[8] ) {
+ $strcalc .= " AND $itemstable.ccode LIKE ? ";
+ @$filters[8] =~ s/\*/%/g;
+ push @sqlargs, @$filters[8];
+ }
+ if ( defined @$filters[9] and @$filters[9] ne '' ) {
+ $strcalc .= " AND $itemstable.notforloan LIKE ? ";
+ @$filters[9] =~ s/\*/%/g;
+ push @sqlargs, @$filters[9];
+ }
+ if ( defined @$filters[10] and @$filters[10] ne '' ) {
+ $strcalc .= " AND $itemstable.materials LIKE ? ";
+ @$filters[10] =~ s/\*/%/g;
+ push @sqlargs, @$filters[10];
+ }
+ if ( @$filters[13] ) {
+ $strcalc .= " AND $itemstable.dateaccessioned >= ? ";
+ @$filters[13] =~ s/\*/%/g;
+ push @sqlargs, @$filters[13];
+ }
+ if ( @$filters[14] ) {
+ $strcalc .= " AND $itemstable.dateaccessioned <= ? ";
+ @$filters[14] =~ s/\*/%/g;
+ push @sqlargs, @$filters[14];
+ }
+ if ( $cellvalue eq 'deleteditems' and @$filters[15] ) {
+ $strcalc .= " AND DATE(deleteditems.timestamp) >= ? ";
+ @$filters[15] =~ s/\*/%/g;
+ push @sqlargs, @$filters[15];
+ }
+ if ( $cellvalue eq 'deleteditems' and @$filters[16] ) {
+ @$filters[16] =~ s/\*/%/g;
+ $strcalc .= " AND DATE(deleteditems.timestamp) <= ?";
+ push @sqlargs, @$filters[16];
+ }
+ $strcalc .= " group by $linefield, $colfield order by $linefield,$colfield";
+ $debug and warn "SQL: $strcalc";
+ my $dbcalc = $dbh->prepare($strcalc);
+ $dbcalc->execute(@sqlargs);
+
+ while ( my ( $row, $col, $value ) = $dbcalc->fetchrow ) {
+
+ $col = "zzEMPTY" if ( !defined($col) );
+ $row = "zzEMPTY" if ( !defined($row) );
+
+ $table{$row}->{$col} += $value;
+ $table{$row}->{totalrow} += $value;
+ $grantotal += $value;
+ }
+
+ 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{$row->{value}}->{ $col->{value} };
+ push @loopcell, { value => $value };
+ }
+ push @looprow,
+ { 'rowtitle' => $row->{rowtitle},
+ 'value' => $row->{value},
+ 'loopcell' => \@loopcell,
+ 'hilighted' => ( $hilighted *= -1 > 0 ),
+ 'totalrow' => $table{$row->{value}}->{totalrow}
+ };
+ }
+
+ foreach my $col (@loopcol) {
+ my $total = 0;
+ foreach my $row (@looprow) {
+ $total += $table{ $row->{value} }->{ $col->{value} };
+ }
+
+ push @loopfooter, { 'totalcol' => $total };
+ }
+
+ # the header of the table
+ $globalline{loopfilter} = \@loopfilter;
+
+ # the core of the table
+ $globalline{looprow} = \@looprow;
+ $globalline{loopcol} = \@loopcol;
+
+ # the foot (totals by borrower type)
+ $globalline{loopfooter} = \@loopfooter;
+ $globalline{total} = $grantotal;
+ $globalline{line} = $line;
+ $globalline{column} = $column;
+ push @mainloop, \%globalline;
+ return \@mainloop;
+}