X-Git-Url: http://git.rot13.org/?a=blobdiff_plain;f=reports%2Fcatalogue_stats.pl;h=c8e21940f494058fd4a7645e81c20f670876bee5;hb=c15ba915b107621961f145aa46e0ca9869b7c4da;hp=87344b489042674663568dded769cf4e8cecebcc;hpb=c81e2b2fd6d1ffb143459ea4124698005fa4ee3f;p=koha.git

diff --git a/reports/catalogue_stats.pl b/reports/catalogue_stats.pl
index 87344b4890..c8e21940f4 100755
--- a/reports/catalogue_stats.pl
+++ b/reports/catalogue_stats.pl
@@ -1,6 +1,5 @@
 #!/usr/bin/perl
 
-# $Id$
 
 # Copyright 2000-2002 Katipo Communications
 #
@@ -15,20 +14,20 @@
 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
 # A PARTICULAR PURPOSE.  See the GNU General Public License for more details.
 #
-# You should have received a copy of the GNU General Public License along with
-# Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place,
-# Suite 330, Boston, MA  02111-1307 USA
+# You should have received a copy of the GNU General Public License along
+# with Koha; if not, write to the Free Software Foundation, Inc.,
+# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
 
 use strict;
+#use warnings; FIXME - Bug 2505
 use C4::Auth;
 use CGI;
 use C4::Context;
-use HTML::Template;
-use C4::Search;
+use C4::Branch; # GetBranches
 use C4::Output;
 use C4::Koha;
-use C4::Interface::CGI::Output;
-use C4::Circulation::Circ2;
+use C4::Reports;
+use C4::Circulation;
 
 =head1 NAME
 
@@ -36,31 +35,37 @@ plugin that shows a stats on borrowers
 
 =head1 DESCRIPTION
 
-
-=over2
+=over 2
 
 =cut
 
+our $debug = 0;
 my $input = new CGI;
-my $do_it=$input->param('do_it');
 my $fullreportname = "reports/catalogue_stats.tmpl";
-my $line = $input->param("Line");
-my $column = $input->param("Column");
-my @filters = $input->param("Filter");
+my $do_it       = $input->param('do_it');
+my $line        = $input->param("Line");
+my $column      = $input->param("Column");
+my @filters     = $input->param("Filter");
 my $deweydigits = $input->param("deweydigits");
-my $lccndigits = $input->param("lccndigits");
-my $cotedigits = $input->param("cotedigits");
-my $output = $input->param("output");
-my $basename = $input->param("basename");
-my $mime = $input->param("MIME");
-my $del = $input->param("sep");
+my $lccndigits  = $input->param("lccndigits");
+my $cotedigits  = $input->param("cotedigits");
+my $output      = $input->param("output");
+my $basename    = $input->param("basename");
+our $sep        = $input->param("sep");
+$sep = "\t" if ($sep eq 'tabulation');
+my $item_itype;
+if(C4::Context->preference('item-level_itypes')) {
+	$item_itype = "items\.itype"
+} else {
+	$item_itype = "itemtype";
+}
 
 my ($template, $borrowernumber, $cookie)
 	= get_template_and_user({template_name => $fullreportname,
 				query => $input,
 				type => "intranet",
 				authnotrequired => 0,
-				flagsrequired => {editcatalogue => 1},
+				flagsrequired => {reports => '*'},
 				debug => 1,
 				});
 $template->param(do_it => $do_it);
@@ -69,18 +74,19 @@ if ($do_it) {
 	if ($output eq "screen"){
 		$template->param(mainloop => $results);
 		output_html_with_http_headers $input, $cookie, $template->output;
-		exit(1);
+		exit;
 	} else {
-		print $input->header(-type => 'application/vnd.sun.xml.calc', -name=>"$basename.csv" );
-		my $cols = @$results[0]->{loopcol};
+		print $input->header(-type => 'application/vnd.sun.xml.calc',
+                                     -encoding    => 'utf-8',
+							 -attachment=>"$basename.csv",
+							 -name=>"$basename.csv" );
+		my $cols  = @$results[0]->{loopcol};
 		my $lines = @$results[0]->{looprow};
-		my $sep;
-		$sep =C4::Context->preference("delimiter");
 		print @$results[0]->{line} ."/". @$results[0]->{column} .$sep;
 		foreach my $col ( @$cols ) {
 			print $col->{coltitle}.$sep;
 		}
-		print "\n";
+		print "Total\n";
 		foreach my $line ( @$lines ) {
 			my $x = $line->{loopcell};
 			print $line->{rowtitle}.$sep;
@@ -96,7 +102,7 @@ if ($do_it) {
 			print $sep.$col->{totalcol};
 		}
 		print $sep.@$results[0]->{total};
-		exit(1);
+		exit;
 	}
 } else {
 	my $dbh = C4::Context->dbh;
@@ -104,185 +110,72 @@ if ($do_it) {
 	my %labels;
 	my $count=0;
 	my $req;
-	$req = $dbh->prepare("select distinctrow left(dewey,3) from biblioitems order by dewey");
-	$req->execute;
-	my $hasdewey;
 	my @select;
-	push @select,"";
-	while (my ($value) =$req->fetchrow) {
-		$hasdewey =1 if (($value) and (! $hasdewey));
-		$count++ if (($value) and (! $hasdewey));
-		push @select, $value;
-	}
-	my $CGIdewey=CGI::scrolling_list( -name     => 'Filter',
-				-id => 'Filter',
-				-values   => \@select,
-				-size     => 1,
-				-multiple => 0 );
-	
-	$req = $dbh->prepare( "select distinctrow left(lccn,3) from biblioitems order by lccn");
-	$req->execute;
-	undef @select;
-	push @select,"";
-	my $haslccn;
-	my $hlghtlccn;
-	while (my ($value) =$req->fetchrow) {
-		$hlghtlccn = !($hasdewey);
-		$haslccn =1 if (($value) and (! $haslccn));
-		$count++ if (($value) and (! $haslccn));
-		push @select, $value;
-	}
-	my $CGIlccn=CGI::scrolling_list( -name     => 'Filter',
-				-id => 'Filter',
-				-values   => \@select,
-				-size     => 1,
-				-multiple => 0 );
-	
-	$req = $dbh->prepare("select distinctrow left(itemcallnumber,5) from items order by itemcallnumber");
-	$req->execute;
-	undef @select;
-	push @select,"";
-	my $hascote;
-	my $hlghtcote;
-	while (my ($value) =$req->fetchrow) {
-		$hascote =1 if (($value) and (! $hascote));
-		$count++ if (($value) and (! $hascote));
-		$hlghtcote = (($hasdewey) and ($haslccn)) or (!($hasdewey) and !($haslccn));
-		push @select, $value;
-	}
-	my $CGIcote=CGI::scrolling_list( -name     => 'Filter',
-				-id => 'Filter',
-				-values   => \@select,
-				-size     => 1,
-				-multiple => 0 );
-	$count++;
-	my $hglghtDT =$count % 2;
-#	warn "highlightDT ".$hglghtDT;
-	$count++;
-	my $hglghtPub =$count % 2;
-#	warn "highlightPub ".$hglghtPub;
-	$count++;
-	my $hglghtPY =$count % 2;
-#	warn "highlightPY ".$hglghtPY;
-	$count++;
-	my $hglghtHB =$count % 2;
-#	warn "highlightHB ".$hglghtHB;
-	$count++;
-	my $hglghtLOC =$count % 2;
-#	warn "highlightLOC ".$hglghtLOC;
-	
-# 	undef @select;
-# 	push @select,"";
-# 	for (my $i=1950;$i<=2050;$i++) {
-# 		push @select, $i;
-# 	}
-# 	my $CGIpublicationyear=CGI::scrolling_list( -name     => 'Filter',
+	# FIXME: no such field "dewey"
+	# $req = $dbh->prepare("select count(dewey) from biblioitems ");
+	# $req->execute;
+	my $hasdewey = 0;
+
+# (rch) biblioitems.lccn is mapped to lccn MARC21 010$a in default framework.
+# This is not the LC Classification.  It's the Control Number.
+# So I'm just going to remove this bit.  Call Number is handled in itemcallnumber.
+#
+	my $haslccn = 0;
+#	$req = $dbh->prepare( "select count(lccn) from biblioitems ");
+#	$req->execute;
+#	my $hlghtlccn;
+#	while (my ($value) =$req->fetchrow) {
+#		$hlghtlccn = !($hasdewey);
+#		$haslccn =1 if (($value>2) and (! $haslccn));
+#		$count++ if (($value) and (! $haslccn));
+#		push @select, $value;
+#	}
+# 	my $CGIlccn=CGI::scrolling_list( -name     => 'Filter',
 # 				-id => 'Filter',
 # 				-values   => \@select,
 # 				-size     => 1,
 # 				-multiple => 0 );
-	
-	$req = $dbh->prepare("select distinctrow itemtype from biblioitems order by itemtype");
-	$req->execute;
-	undef @select;
-	push @select,"";
-	while (my ($value) =$req->fetchrow) {
-		push @select, $value;
-	}
-	my $CGIitemtype=CGI::scrolling_list( -name     => 'Filter',
-				-id => 'Filter',
-				-values   => \@select,
-				-size     => 1,
-				-multiple => 0 );
-	
-	$req = $dbh->prepare("select distinctrow publishercode from biblioitems order by publishercode");
+
+# No need to test for data here.  If you don't have itemcallnumbers, you probably know it.
+# FIXME: Hardcoding to 5 chars on itemcallnum. 
+#
+    my $hascote = 1;
+    my $highcote = 5;
+
+	$req = $dbh->prepare("select itemtype, description from itemtypes order by description");
 	$req->execute;
-	undef @select;
-	push @select,"";
-	while (my ($value) =$req->fetchrow) {
-		push @select, $value;
-	}
-	my $CGIpublisher=CGI::scrolling_list( -name     => 'Filter',
-				-id => 'Filter',
-				-values   => \@select,
-				-size     => 1,
-				-multiple => 0 );
+	my $CGIitemtype = $req->fetchall_arrayref({});
 
-	undef @select;
-	push @select,"";
-	my $branches=getbranches();
-	my %select_branches;
-	$select_branches{""} = "";
-	foreach my $branch (keys %$branches) {
-		push @select, $branch;
-		$select_branches{$branch} = $branches->{$branch}->{'branchname'};
+	my $authvals = GetKohaAuthorisedValues("items.ccode");
+	my @authvals;
+	foreach (sort {$authvals->{$a} cmp $authvals->{$b} || $a cmp $b} keys %$authvals) {
+		push @authvals, { code => $_, description => $authvals->{$_} };
 	}
-	my $CGIbranch=CGI::scrolling_list( -name     => 'Filter',
-				-id => 'Filter',
-				-values   => \@select,
-				-labels   => \%select_branches,
-				-size     => 1,
-				-multiple => 0 );
-	
-	$req = $dbh->prepare("select distinctrow location from items order by location");
-	$req->execute;
-	undef @select;
-	push @select,"";
-	my $CGIlocation=CGI::scrolling_list( -name     => 'Filter',
-				-id => 'Filter',
-				-values   => \@select,
-				-size     => 1,
-				-multiple => 0 );
 	
-	my @mime = ( C4::Context->preference("MIME") );
-	foreach my $mime (@mime){
-#		warn "".$mime;
+	my $locations = GetKohaAuthorisedValues("items.location");
+	my @locations;
+	foreach (sort keys %$locations) {
+		push @locations, { code => $_, description => "$_ - " . $locations->{$_} };
 	}
 	
-	my $CGIextChoice=CGI::scrolling_list(
-				-name => 'MIME',
-				-id => 'MIME',
-				-values   => \@mime,
-				-size     => 1,
-				-multiple => 0 );
-	
-	my @dels = ( C4::Context->preference("delimiter") );
-	my $CGIsepChoice=CGI::scrolling_list(
-				-name => 'sep',
-				-id => 'sep',
-				-values   => \@dels,
-				-size     => 1,
-				-multiple => 0 );
+	my @mime  = ( map { +{type =>$_} } (split /[;:]/, 'CSV') ); # FIXME translation
 	
 	$template->param(hasdewey=>$hasdewey,
-					CGIFromDeweyClass => $CGIdewey,
-					CGIToDeweyClass => $CGIdewey,
-					haslccn=> $haslccn,
-					hlghtlccn => $hlghtlccn,
-					CGIFromLoCClass => $CGIlccn,
-					CGIToLoCClass => $CGIlccn,
-					hascote=> $hascote,
-					hlghtcote => $hlghtcote,
-					hglghtDT => $hglghtDT,
-					hglghtPub => $hglghtPub,
-					hglghtPY => $hglghtPY,
-					hglghtHB => $hglghtHB,
-					hglghtLOC => $hglghtLOC,
-					CGIFromCoteClass => $CGIcote,
-					CGIToCoteClass => $CGIcote,
+					haslccn   => $haslccn,
+					hascote   => $hascote,
 					CGIItemType => $CGIitemtype,
-# 					CGIFromPublicationYear => $CGIpublicationyear,
-# 					CGIToPublicationYear => $CGIpublicationyear,
-					CGIPublisher => $CGIpublisher,
-					CGIBranch => $CGIbranch,
-					CGILocation => $CGIlocation,
-					CGIextChoice => $CGIextChoice,
-					CGIsepChoice => $CGIsepChoice
+					CGIBranch    => GetBranchesLoop(C4::Context->userenv->{'branch'}),
+					locationloop => \@locations,
+					authvals     => \@authvals,
+					CGIextChoice => \@mime,
+					CGIsepChoice => GetDelimiterChoices,
+					item_itype => $item_itype
 					);
 
 }
 output_html_with_http_headers $input, $cookie, $template->output;
 
+## End of Main Body
 
 
 sub calculate {
@@ -294,68 +187,91 @@ sub calculate {
 	my @looprow;
 	my %globalline;
 	my $grantotal =0;
+    my $barcodelike   = @$filters[13];
+    my $barcodefilter = @$filters[14];
+    my $not;
+    
 # extract parameters
 	my $dbh = C4::Context->dbh;
 
+# if barcodefilter is empty set as %
+if($barcodefilter){
+    # Check if barcodefilter is "like" or "not like"
+    if(!$barcodelike){
+        $not = "not";
+    }
+    # Change * to %
+    $barcodefilter =~ s/\*/%/g;
+}
+
 # Filters
 # Checking filters
 #
 	my @loopfilter;
-	for (my $i=0;$i<=11;$i++) {
+	for (my $i=0;$i<=12;$i++) {
 		my %cell;
 		if ( @$filters[$i] ) {
 			if ((($i==1) or ($i==3) or ($i==5) or ($i==9)) and (@$filters[$i-1])) {
 				$cell{err} = 1 if (@$filters[$i]<@$filters[$i-1]) ;
 			}
 			$cell{filter} .= @$filters[$i];
-			$cell{crit} .="Dewey Classification From" if ($i==0);
-			$cell{crit} .="Dewey Classification To" if ($i==1);
-			$cell{crit} .="Lccn Classification From" if ($i==2);
-			$cell{crit} .="Lccn Classification To" if ($i==3);
-			$cell{crit} .="Cote Classification From" if ($i==4);
-			$cell{crit} .="Cote Classification To" if ($i==5);
-			$cell{crit} .="Document type" if ($i==6);
-			$cell{crit} .="Publisher" if ($i==7);
-			$cell{crit} .="Publication year From" if ($i==8);
-			$cell{crit} .="Publication year To" if ($i==9);
-			$cell{crit} .="Branch :" if ($i==10);
-			$cell{crit} .="Location:" if ($i==11);
+			$cell{crit} .=
+				($i== 0) ? "Dewey Classification From" :
+				($i== 1) ? "Dewey Classification To"   :
+				($i== 2) ? "Lccn Classification From"  :
+				($i== 3) ? "Lccn Classification To"    :
+				($i== 4) ? "Item CallNumber From"  :
+				($i== 5) ? "Item CallNumber To"    :
+				($i== 6) ? "Item type"             :
+				($i== 7) ? "Publisher"                 :
+				($i== 8) ? "Publication year From"     :
+				($i== 9) ? "Publication year To"       :
+				($i==10) ? "Library :"                  :
+				($i==11) ? "Shelving Location :"                :
+				($i==12) ? "Collection Code :"            : '';
 			push @loopfilter, \%cell;
 		}
 	}
 	
+#	warn map {"filtres $_\n"} @filters[0..3];
+
 	my @linefilter;
-#	warn "filtres ".@filters[0];
-#	warn "filtres ".@filters[1];
-#	warn "filtres ".@filters[2];
-#	warn "filtres ".@filters[3];
-	
  	$linefilter[0] = @$filters[0] if ($line =~ /dewey/ )  ;
  	$linefilter[1] = @$filters[1] if ($line =~ /dewey/ )  ;
  	$linefilter[0] = @$filters[2] if ($line =~ /lccn/ )  ;
  	$linefilter[1] = @$filters[3] if ($line =~ /lccn/ )  ;
- 	$linefilter[0] = @$filters[4] if ($line =~ /items.itemcallnumber/ )  ;
- 	$linefilter[1] = @$filters[5] if ($line =~ /items.itemcallnumber/ )  ;
- 	@linefilter[0] = @$filters[6] if ($line =~ /itemtype/ )  ;
- 	@linefilter[0] = @$filters[7] if ($line =~ /publishercode/ ) ;
+ 	$linefilter[0] = @$filters[4] if ($line =~ /items\.itemcallnumber/ )  ;
+ 	$linefilter[1] = @$filters[5] if ($line =~ /items\.itemcallnumber/ )  ;
+	if (C4::Context->preference('item-level_itypes')) {
+ 		$linefilter[0] = @$filters[6] if ($line =~ /items\.itype/ )  ;
+	} else {
+ 		$linefilter[0] = @$filters[6] if ($line =~ /itemtype/ )  ;
+	}
+ 	$linefilter[0] = @$filters[7] if ($line =~ /publishercode/ ) ;
  	$linefilter[0] = @$filters[8] if ($line =~ /publicationyear/ ) ;
  	$linefilter[1] = @$filters[9] if ($line =~ /publicationyear/ ) ;
- 	@linefilter[0] = @$filters[10] if ($line =~ /items.homebranch/ ) ;
- 	@linefilter[0] = @$filters[11] if ($line =~ /items.location/ ) ;
-# 
+ 	$linefilter[0] = @$filters[10] if ($line =~ /items\.homebranch/ ) ;
+ 	$linefilter[0] = @$filters[11] if ($line =~ /items\.location/ ) ;
+ 	$linefilter[0] = @$filters[12] if ($line =~ /items\.ccode/ ) ;
+
  	my @colfilter ;
  	$colfilter[0] = @$filters[0] if ($column =~ /dewey/ )  ;
  	$colfilter[1] = @$filters[1] if ($column =~ /dewey/ )  ;
  	$colfilter[0] = @$filters[2] if ($column =~ /lccn/ )  ;
  	$colfilter[1] = @$filters[3] if ($column =~ /lccn/ )  ;
- 	$colfilter[0] = @$filters[4] if ($column =~ /itemcallnumber/ )  ;
- 	$colfilter[1] = @$filters[5] if ($column =~ /itemcallnumber/ )  ;
- 	@colfilter[0] = @$filters[6] if ($column =~ /itemtype/ )  ;
- 	@colfilter[0] = @$filters[7] if ($column =~ /publishercode/ ) ;
+ 	$colfilter[0] = @$filters[4] if ($column =~ /items\.itemcallnumber/ )  ;
+ 	$colfilter[1] = @$filters[5] if ($column =~ /items\.itemcallnumber/ )  ;
+	if (C4::Context->preference('item-level_itypes')) {
+ 		$colfilter[0] = @$filters[6] if ($column =~ /items\.itype/ )  ;
+	} else {
+ 		$colfilter[0] = @$filters[6] if ($column =~ /itemtype/ )  ;
+	}
+ 	$colfilter[0] = @$filters[7] if ($column =~ /publishercode/ ) ;
  	$colfilter[0] = @$filters[8] if ($column =~ /publicationyear/ ) ;
  	$colfilter[1] = @$filters[9] if ($column =~ /publicationyear/ ) ;
- 	@colfilter[0] = @$filters[10] if ($column =~ /items.homebranch/ ) ;
- 	@colfilter[0] = @$filters[11] if ($column =~ /items.location/ ) ;
+ 	$colfilter[0] = @$filters[10] if ($column =~ /items\.homebranch/ ) ;
+ 	$colfilter[0] = @$filters[11] if ($column =~ /items\.location/ ) ;
+ 	$colfilter[0] = @$filters[12] if ($column =~ /items\.ccode/ ) ;
 
 # 1st, loop rows.
 	my $linefield;
@@ -368,36 +284,39 @@ sub calculate {
 	}else {
 		$linefield .= $line;
 	}
-	
-	
-	my $strsth;
-	$strsth .= "select distinctrow $linefield from biblioitems, items where (items.biblioitemnumber = biblioitems.biblioitemnumber) and $line is not null ";
+
+	my $strsth = "SELECT DISTINCTROW $linefield FROM biblioitems 
+	                INNER JOIN items USING (biblioitemnumber)
+	              WHERE $line IS NOT NULL ";
+    $strsth .= " AND barcode $not LIKE ? " if ($barcodefilter);
 	if ( @linefilter ) {
 		if ($linefilter[1]){
-			$strsth .= " and $line >= ? " ;
-			$strsth .= " and $line <= ? " ;
+			$strsth .= " AND $line >= ? " ;
+			$strsth .= " AND $line <= ? " ;
 		} elsif ($linefilter[0]) {
 			$linefilter[0] =~ s/\*/%/g;
-			$strsth .= " and $line LIKE ? " ;
+			$strsth .= " AND $line LIKE ? " ;
 		}
 	}
-	$strsth .=" order by $linefield";
-#	warn "". $strsth;
-	
+	$strsth .=" ORDER BY $linefield";
+	$debug and print STDERR "catalogue_stats SQL: $strsth\n";
+
 	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;
+		$sth->execute($barcodefilter,$linefilter[0],$linefilter[1]);
+	} elsif ($barcodefilter,$linefilter[0]) {
+		$sth->execute($barcodefilter,$linefilter[0]);
+	} elsif ($barcodefilter) {
+		$sth->execute($barcodefilter);
+	}else{
+	    $sth->execute();
 	}
  	while ( my ($celvalue) = $sth->fetchrow) {
  		my %cell;
 		if ($celvalue) {
 			$cell{rowtitle} = $celvalue;
-		} else {
-			$cell{rowtitle} = "";
+#		} else {
+#			$cell{rowtitle} = "";
 		}
  		$cell{totalrow} = 0;
 		push @loopline, \%cell;
@@ -406,42 +325,53 @@ sub calculate {
 # 2nd, loop cols.
 	my $colfield;
 	if (($column =~/dewey/)  and ($deweydigits)) {
-		$colfield .="left($column,$deweydigits)";
+		$colfield = "left($column,$deweydigits)";
 	}elsif (($column=~/lccn/) and ($lccndigits)) {
-		$colfield .="left($column,$lccndigits)";
+		$colfield = "left($column,$lccndigits)";
 	}elsif (($column=~/itemcallnumber/) and ($cotedigits)) {
-		$colfield .="left($column,$cotedigits)";
+		$colfield = "left($column,$cotedigits)";
 	}else {
-		$colfield .= $column;
+		$colfield = $column;
 	}
 	
-	my $strsth2;
-	$strsth2 .= "select distinctrow $colfield from biblioitems, items where (items.biblioitemnumber = biblioitems.biblioitemnumber) and $column is not null ";
+	my $strsth2 = "
+	SELECT distinctrow $colfield
+	FROM   biblioitems
+	INNER JOIN items
+		USING (biblioitemnumber)
+	WHERE $column IS NOT NULL ";
+	$strsth2 .= " AND barcode $not LIKE ?" if $barcodefilter;
+	
 	if (( @colfilter ) and ($colfilter[1])) {
-		$strsth2 .= " and $column> ? and $column< ?";
+		$strsth2 .= " AND $column> ? AND $column< ?";
 	}elsif ($colfilter[0]){
 		$colfilter[0] =~ s/\*/%/g;
-		$strsth2 .= " and $column LIKE ? ";
+		$strsth2 .= " AND $column LIKE ? ";
 	} 
-	$strsth2 .= " order by $colfield";
-#	warn "". $strsth2;
+	$strsth2 .= " ORDER BY $colfield";
+	$debug and print STDERR "SQL: $strsth2";
 	my $sth2 = $dbh->prepare( $strsth2 );
 	if ((@colfilter) and ($colfilter[1])) {
-		$sth2->execute($colfilter[0],$colfilter[1]);
+		$sth2->execute($barcodefilter,$colfilter[0],$colfilter[1]);
 	} elsif ($colfilter[0]){
-		$sth2->execute($colfilter[0]);
+		$sth2->execute($barcodefilter,$colfilter[0]);
+	} elsif ($barcodefilter){
+	    $sth2->execute($barcodefilter);
 	} else {
-		$sth2->execute;
+		$sth2->execute();
 	}
  	while (my ($celvalue) = $sth2->fetchrow) {
  		my %cell;
 		my %ft;
-		$cell{coltitle} = $celvalue;
+		if ($celvalue) {
+			$cell{coltitle} = $celvalue;
+#		} else {
+#			$cell{coltitle} = "";
+		}
  		$ft{totalcol} = 0;
 		push @loopcol, \%cell;
  	}
 	
-
 	my $i=0;
 	my @totalcol;
 	my $hilighted=-1;
@@ -458,62 +388,117 @@ sub calculate {
 	}
 
 # preparing calculation
-	my $strcalc .= "SELECT $linefield, $colfield, count( * ) FROM biblioitems, items WHERE (items.biblioitemnumber = biblioitems.biblioitemnumber) AND $line is not null AND $column is not null";
-	@$filters[0]=~ s/\*/%/g if (@$filters[0]);
-	$strcalc .= " AND dewey >" . @$filters[0] ."" if ( @$filters[0] );
-	@$filters[1]=~ s/\*/%/g if (@$filters[1]);
-	$strcalc .= " AND dewey <" . @$filters[1] ."" if ( @$filters[1] );
-	@$filters[2]=~ s/\*/%/g if (@$filters[2]);
-	$strcalc .= " AND lccn >" . @$filters[2] ."" if ( @$filters[2] );
-	@$filters[3]=~ s/\*/%/g if (@$filters[3]);
-	$strcalc .= " AND lccn <" . @$filters[3] ."" if ( @$filters[3] );
-	@$filters[4]=~ s/\*/%/g if (@$filters[4]);
-	$strcalc .= " AND items.itemcallnumber >" . @$filters[4] ."" if ( @$filters[4] );
-	@$filters[5]=~ s/\*/%/g if (@$filters[5]);
-	$strcalc .= " AND items.itemcallnumber <" . @$filters[5] ."" if ( @$filters[5] );
-	@$filters[6]=~ s/\*/%/g if (@$filters[6]);
-	$strcalc .= " AND biblioitems.itemtype like '" . @$filters[6] ."'" if ( @$filters[6] );
-	@$filters[7]=~ s/\*/%/g if (@$filters[7]);
-	$strcalc .= " AND biblioitems.publishercode like '" . @$filters[7] ."'" if ( @$filters[7] );
-	@$filters[8]=~ s/\*/%/g if (@$filters[8]);
-	$strcalc .= " AND publicationyear >" . @$filters[8] ."" if ( @$filters[8] );
-	@$filters[9]=~ s/\*/%/g if (@$filters[9]);
-	$strcalc .= " AND publicationyear <" . @$filters[9] ."" if ( @$filters[9] );
-	@$filters[10]=~ s/\*/%/g if (@$filters[10]);
-	$strcalc .= " AND items.homebranch like '" . @$filters[10] ."'" if ( @$filters[10] );
-	@$filters[11]=~ s/\*/%/g if (@$filters[11]);
-	$strcalc .= " AND items.location like '" . @$filters[11] ."'" if ( @$filters[11] );
+	my $strcalc = "SELECT $linefield, $colfield, count(*) FROM biblioitems INNER JOIN  items ON (items.biblioitemnumber = biblioitems.biblioitemnumber) WHERE 1 ";
+	$strcalc .= "AND barcode $not like ? " if ($barcodefilter); 
+	
+	if (@$filters[0]){
+		@$filters[0]=~ s/\*/%/g;
+		$strcalc .= " AND dewey >" . @$filters[0];
+	}
+	if (@$filters[1]){
+		@$filters[1]=~ s/\*/%/g ;
+		$strcalc .= " AND dewey <" . @$filters[1];
+	}
+	if (@$filters[2]){
+		@$filters[2]=~ s/\*/%/g ;
+		$strcalc .= " AND lccn >" . @$filters[2];
+	}
+	if (@$filters[3]){
+		@$filters[3]=~ s/\*/%/g;
+		$strcalc .= " AND lccn <" . @$filters[3];
+	}
+	if (@$filters[4]){
+		@$filters[4]=~ s/\*/%/g ;
+		$strcalc .= " AND items.itemcallnumber >=" . $dbh->quote(@$filters[4]);
+	}
+	
+	if (@$filters[5]){
+		@$filters[5]=~ s/\*/%/g;
+		$strcalc .= " AND items.itemcallnumber <=" . $dbh->quote(@$filters[5]);
+	}
+	
+	if (@$filters[6]){
+		@$filters[6]=~ s/\*/%/g;
+		$strcalc .= " AND " . 
+			(C4::Context->preference('item-level_itypes') ? 'items.itype' : 'biblioitems.itemtype')
+			. " LIKE '" . @$filters[6] ."'";
+	}
+	
+	if (@$filters[7]){
+		@$filters[7]=~ s/\*/%/g;
+		@$filters[7].="%" unless @$filters[7]=~/%/;
+		$strcalc .= " AND biblioitems.publishercode LIKE \"" . @$filters[7] ."\"";
+	}
+	if (@$filters[8]){
+		@$filters[8]=~ s/\*/%/g;
+		$strcalc .= " AND publicationyear >" . @$filters[8];
+	}
+	if (@$filters[9]){
+		@$filters[9]=~ s/\*/%/g;
+		$strcalc .= " AND publicationyear <" . @$filters[9];
+	}
+	if (@$filters[10]){
+		@$filters[10]=~ s/\*/%/g;
+		$strcalc .= " AND items.homebranch LIKE '" . @$filters[10] ."'";
+	}
+	if (@$filters[11]){
+		@$filters[11]=~ s/\*/%/g;
+		$strcalc .= " AND items.location LIKE '" . @$filters[11] ."'";
+	}
+	if (@$filters[12]){
+		@$filters[12]=~ s/\*/%/g;
+		$strcalc .= " AND items.ccode  LIKE '" . @$filters[12] ."'";
+	}
+	
 	$strcalc .= " group by $linefield, $colfield order by $linefield,$colfield";
-#	warn "". $strcalc;
+	$debug and warn "SQL: $strcalc";
 	my $dbcalc = $dbh->prepare($strcalc);
-	$dbcalc->execute;
+	if($barcodefilter){
+	   $dbcalc->execute($barcodefilter);
+	}else{
+	   $dbcalc->execute();
+	}
 #	warn "filling table";
+	
+	my $emptycol; 
 	while (my ($row, $col, $value) = $dbcalc->fetchrow) {
 #		warn "filling table $row / $col / $value ";
-		$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;
 	}
+
+# 	my %cell = {rowtitle => 'zzROWEMPTY'};
+# 	push @loopline,\%cell;
+# 	undef %cell;
+# 	my %cell;
+# 	%cell = {coltitle => "zzEMPTY"};
+ 	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 ) {
-			push @loopcell, {value => $table{$row}->{$col->{coltitle}}} ;
+			my $value =$table{$row}->{($col->{coltitle} eq "NULL")?"zzEMPTY":$col->{coltitle}};
+			push @loopcell, {value => $value  } ;
 		}
-		push @looprow,{ 'rowtitle' => $row,
+		push @looprow,{ 'rowtitle' => ($row eq "zzEMPTY")?"NULL":$row,
 						'loopcell' => \@loopcell,
-						'hilighted' => 1 ,
+						'hilighted' => ($hilighted *= -1 > 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}}->{$col->{coltitle}};
+			$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;
@@ -535,4 +520,4 @@ sub calculate {
 	return \@mainloop;
 }
 
-1;
\ No newline at end of file
+1;