X-Git-Url: http://koha-dev.rot13.org:8081/gitweb/?a=blobdiff_plain;f=reports%2Fcatalogue_stats.pl;h=220157c98478493033c43a187deb429801e0eab5;hb=9a43a47755c43a58c8cf6303cf7bfb678bb017d6;hp=49b272ab336e85b040c262722d83f23519b2cbd0;hpb=7106531fef65f468d20fe214097bcd05f16ea3d7;p=koha_fer diff --git a/reports/catalogue_stats.pl b/reports/catalogue_stats.pl index 49b272ab33..220157c984 100755 --- a/reports/catalogue_stats.pl +++ b/reports/catalogue_stats.pl @@ -1,6 +1,5 @@ #!/usr/bin/perl -# $Id$ # Copyright 2000-2002 Katipo Communications # @@ -20,15 +19,15 @@ # Suite 330, Boston, MA 02111-1307 USA use strict; +# use warnings; # FIXME 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,38 @@ 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"); +my $mime = $input->param("MIME"); +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); @@ -71,16 +77,17 @@ if ($do_it) { output_html_with_http_headers $input, $cookie, $template->output; exit(1); } 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; @@ -102,148 +109,74 @@ if ($do_it) { my $dbh = C4::Context->dbh; my @values; my %labels; + my $count=0; my $req; - $req = $dbh->prepare("select distinctrow left(dewey,3) from biblioitems"); - $req->execute; my @select; - push @select,""; - while (my ($value) =$req->fetchrow) { - 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"); - $req->execute; - undef @select; - push @select,""; - while (my ($value) =$req->fetchrow) { - push @select, $value; - } - my $CGIlccn=CGI::scrolling_list( -name => 'Filter', - -id => 'Filter', - -values => \@select, - -size => 1, - -multiple => 0 ); - - $req = $dbh->prepare("select distinctrow left(itemcolnumber,5) from items"); - $req->execute; - undef @select; - push @select,""; - while (my ($value) =$req->fetchrow) { - push @select, $value; - } - my $CGIcote=CGI::scrolling_list( -name => 'Filter', - -id => 'Filter', - -values => \@select, - -size => 1, - -multiple => 0 ); - - undef @select; - push @select,""; - for (my $i=1950;$i<=2050;$i++) { - push @select, $i; - } - my $CGIpublicationyear=CGI::scrolling_list( -name => 'Filter', - -id => 'Filter', - -values => \@select, - -size => 1, - -multiple => 0 ); - - $req = $dbh->prepare("select distinctrow itemtype from biblioitems"); - $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"); + # 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 ); + +# 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"); - $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 /[;:]/,C4::Context->preference("MIME")) ); - $template->param(CGIFromDeweyClass => $CGIdewey, - CGIToDeweyClass => $CGIdewey, - CGIFromLoCClass => $CGIlccn, - CGIToLoCClass => $CGIlccn, - CGIFromCoteClass => $CGIcote, - CGIToCoteClass => $CGIcote, + $template->param(hasdewey=>$hasdewey, + 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 { @@ -255,68 +188,93 @@ 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; +}else{ + $barcodefilter = "%"; +} + # 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; } } - my $linefilter = ""; -# warn "filtres ".@filters[0]; -# warn "filtres ".@filters[1]; -# warn "filtres ".@filters[2]; -# warn "filtres ".@filters[3]; - - $linefilter = @$filters[0] if ($line =~ /dewey/ ) ; - $linefilter = @$filters[1] if ($line =~ /dewey/ ) ; - $linefilter = @$filters[2] if ($line =~ /lccn/ ) ; - $linefilter = @$filters[3] if ($line =~ /lccn/ ) ; - $linefilter = @$filters[4] if ($line =~ /itemcolnumber/ ) ; - $linefilter = @$filters[5] if ($line =~ /itemcolnumber/ ) ; - $linefilter = @$filters[6] if ($line =~ /itemtype/ ) ; - $linefilter = @$filters[7] if ($line =~ /publishercode/ ) ; - $linefilter = @$filters[8] if ($line =~ /publicationyear/ ) ; - $linefilter = @$filters[9] if ($line =~ /publicationyear/ ) ; - $linefilter = @$filters[10] if ($line =~ /items.homebranch/ ) ; - $linefilter = @$filters[11] if ($line =~ /items.location/ ) ; -# - my $colfilter = ""; - $colfilter = @$filters[0] if ($column =~ /dewey/ ) ; - $colfilter = @$filters[1] if ($column =~ /dewey/ ) ; - $colfilter = @$filters[2] if ($column =~ /lccn/ ) ; - $colfilter = @$filters[3] if ($column =~ /lccn/ ) ; - $colfilter = @$filters[4] if ($column =~ /itemcolnumber/ ) ; - $colfilter = @$filters[5] if ($column =~ /itemcolnumber/ ) ; - $colfilter = @$filters[6] if ($column =~ /itemtype/ ) ; - $colfilter = @$filters[7] if ($column =~ /publishercode/ ) ; - $colfilter = @$filters[8] if ($column =~ /publicationyear/ ) ; - $colfilter = @$filters[9] if ($column =~ /publicationyear/ ) ; - $colfilter = @$filters[10] if ($column =~ /items.homebranch/ ) ; - $colfilter = @$filters[11] if ($column =~ /items.location/ ) ; +# warn map {"filtres $_\n"} @filters[0..3]; + + my @linefilter; + $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/ ) ; + 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[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 =~ /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[12] if ($column =~ /items\.ccode/ ) ; # 1st, loop rows. my $linefield; @@ -324,34 +282,40 @@ sub calculate { $linefield .="left($line,$deweydigits)"; } elsif (($line=~/lccn/) and ($lccndigits)) { $linefield .="left($line,$lccndigits)"; - } elsif (($line=~/itemcolnumber/) and ($cotedigits)) { + } elsif (($line=~/items.itemcallnumber/) and ($cotedigits)) { $linefield .="left($line,$cotedigits)"; }else { $linefield .= $line; } - - + my $strsth; - $strsth .= "select distinctrow $linefield from biblioitems, items where (items.biblioitemnumber = biblioitems.biblioitemnumber) and $line is not null "; - $linefilter =~ s/\*/%/g; - if ( $linefilter ) { - $strsth .= " and $linefield LIKE ? " ; + $strsth .= "select distinctrow $linefield from biblioitems left join items on (items.biblioitemnumber = biblioitems.biblioitemnumber) where barcode $not LIKE ? AND $line is not null "; + if ( @linefilter ) { + if ($linefilter[1]){ + $strsth .= " and $line >= ? " ; + $strsth .= " and $line <= ? " ; + } elsif ($linefilter[0]) { + $linefilter[0] =~ s/\*/%/g; + $strsth .= " and $line LIKE ? " ; + } } $strsth .=" order by $linefield"; - warn "". $strsth; + $debug and print STDERR "catalogue_stats SQL: $strsth\n"; my $sth = $dbh->prepare( $strsth ); - if ( $linefilter ) { - $sth->execute($linefilter); + if (( @linefilter ) and ($linefilter[1])){ + $sth->execute($barcodefilter,$linefilter[0],$linefilter[1]); + } elsif ($barcodefilter,$linefilter[0]) { + $sth->execute($barcodefilter,$linefilter[0]); } else { - $sth->execute; + $sth->execute($barcodefilter); } while ( my ($celvalue) = $sth->fetchrow) { my %cell; if ($celvalue) { $cell{rowtitle} = $celvalue; - } else { - $cell{rowtitle} = ""; +# } else { +# $cell{rowtitle} = ""; } $cell{totalrow} = 0; push @loopline, \%cell; @@ -360,38 +324,49 @@ 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)"; - }elsif (($column=~/itemcolnumber/) and ($cotedigits)) { - $colfield .="left($column,$cotedigits)"; + $colfield = "left($column,$lccndigits)"; + }elsif (($column=~/itemcallnumber/) and ($cotedigits)) { + $colfield = "left($column,$cotedigits)"; }else { - $colfield .= $column; + $colfield = $column; } - my $strsth2; - $colfilter =~ s/\*/%/g; - $strsth2 .= "select distinctrow $colfield from biblioitems, items where (items.biblioitemnumber = biblioitems.biblioitemnumber) and $column is not null "; - if ( $colfilter ) { - $strsth2 .= " and $colfield LIKE ? "; + my $strsth2 = " + SELECT distinctrow $colfield + FROM biblioitems + LEFT JOIN items + ON (items.biblioitemnumber = biblioitems.biblioitemnumber) + WHERE barcode $not LIKE ? AND $column IS NOT NULL "; + if (( @colfilter ) and ($colfilter[1])) { + $strsth2 .= " and $column> ? and $column< ?"; + }elsif ($colfilter[0]){ + $colfilter[0] =~ s/\*/%/g; + $strsth2 .= " and $column LIKE ? "; } $strsth2 .= " order by $colfield"; - warn "". $strsth2; + $debug and print STDERR "SQL: $strsth2"; my $sth2 = $dbh->prepare( $strsth2 ); - if ($colfilter) { - $sth2->execute($colfilter); + if ((@colfilter) and ($colfilter[1])) { + $sth2->execute($barcodefilter,$colfilter[0],$colfilter[1]); + } elsif ($colfilter[0]){ + $sth2->execute($barcodefilter,$colfilter[0]); } else { - $sth2->execute; + $sth2->execute($barcodefilter); } 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; @@ -408,62 +383,114 @@ 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.itemcolnumber >" . @$filters[4] ."" if ( @$filters[4] ); - @$filters[5]=~ s/\*/%/g if (@$filters[5]); - $strcalc .= " AND items.itemcolnumber <" . @$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 LEFT JOIN items ON (items.biblioitemnumber = biblioitems.biblioitemnumber) WHERE 1 AND barcode $not like ? "; + 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; + $dbcalc->execute($barcodefilter); # 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 ( @loopline ) { - $total += $table{$row->{rowtitle}}->{$col->{coltitle}}; + foreach my $row ( @looprow ) { + $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; push @loopfooter, {'totalcol' => $total}; } @@ -482,4 +509,4 @@ sub calculate { return \@mainloop; } -1; \ No newline at end of file +1;