X-Git-Url: http://koha-dev.rot13.org:8081/gitweb/?a=blobdiff_plain;f=reports%2Fcatalogue_stats.pl;h=c8e21940f494058fd4a7645e81c20f670876bee5;hb=82e8a7a155807f50edae4e5f2b46791479c9b92d;hp=47ef65af2df011ddb9547bfbe492049c945ba4e6;hpb=60262720f0d371e79d2eb1ef1d5ebc1b8c56eb87;p=koha_fer diff --git a/reports/catalogue_stats.pl b/reports/catalogue_stats.pl index 47ef65af2d..c8e21940f4 100755 --- a/reports/catalogue_stats.pl +++ b/reports/catalogue_stats.pl @@ -14,17 +14,19 @@ # 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 C4::Branch; # GetBranches use C4::Output; use C4::Koha; +use C4::Reports; use C4::Circulation; =head1 NAME @@ -49,15 +51,21 @@ 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"); +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 => {reports => 1}, + flagsrequired => {reports => '*'}, debug => 1, }); $template->param(do_it => $do_it); @@ -66,7 +74,7 @@ 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', -encoding => 'utf-8', @@ -74,7 +82,6 @@ if ($do_it) { -name=>"$basename.csv" ); my $cols = @$results[0]->{loopcol}; my $lines = @$results[0]->{looprow}; - my $sep = C4::Context->preference("delimiter"); print @$results[0]->{line} ."/". @$results[0]->{column} .$sep; foreach my $col ( @$cols ) { print $col->{coltitle}.$sep; @@ -95,7 +102,7 @@ if ($do_it) { print $sep.$col->{totalcol}; } print $sep.@$results[0]->{total}; - exit(1); + exit; } } else { my $dbh = C4::Context->dbh; @@ -108,133 +115,61 @@ if ($do_it) { # $req = $dbh->prepare("select count(dewey) from biblioitems "); # $req->execute; my $hasdewey = 0; -# push @select,""; -# while (my ($value) =$req->fetchrow) { -# if (($value>2) and (! $hasdewey)) { -# $hasdewey =1; -# $count++; -# } -# push @select, $value; -# } -# my $CGIdewey=CGI::scrolling_list( -name => 'Filter', -# -id => 'Filter', -# -values => \@select, -# -size => 1, -# -multiple => 0 ); - - $req = $dbh->prepare( "select count(lccn) from biblioitems "); - $req->execute; -# undef @select; -# push @select,""; - my $haslccn; - my $hlghtlccn; - while (my ($value) =$req->fetchrow) { - $hlghtlccn = !($hasdewey); - $haslccn =1 if (($value>2) and (! $haslccn)); - $count++ if (($value) and (! $haslccn)); + +# (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 count(itemcallnumber) from items"); - $req->execute; -# undef @select; -# push @select,""; - my $hascote; - my $hlghtcote; - while (my ($value) =$req->fetchrow) { - $hascote =1 if (($value>2) 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 ); - my $hglghtDT = ++$count % 2; - my $hglghtPub = ++$count % 2; - my $hglghtPY = ++$count % 2; - my $hglghtHB = ++$count % 2; - my $hglghtLOC = ++$count % 2; -# warn "highlightDT " .$hglghtDT; -# warn "highlightPub " .$hglghtPub; -# warn "highlightPY " .$hglghtPY; -# warn "highlightHB " .$hglghtHB; -# warn "highlightLOC " .$hglghtLOC; - - + +# 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; my $CGIitemtype = $req->fetchall_arrayref({}); my $authvals = GetKohaAuthorisedValues("items.ccode"); my @authvals; - foreach (keys %$authvals) { + foreach (sort {$authvals->{$a} cmp $authvals->{$b} || $a cmp $b} keys %$authvals) { push @authvals, { code => $_, description => $authvals->{$_} }; } -# $req = $dbh->prepare("select distinctrow left(publishercode,75) from biblioitems order by publishercode"); -# $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 $branches=GetBranches(); - my @branchloop; - foreach (keys %$branches) { - my $thisbranch = ''; # FIXME: populate $thisbranch to preselect one - my %row = (branchcode => $_, - selected => ($thisbranch eq $_ ? 1 : 0), - branchname => $branches->{$_}->{'branchname'}, - ); - push @branchloop, \%row; + my $locations = GetKohaAuthorisedValues("items.location"); + my @locations; + foreach (sort keys %$locations) { + push @locations, { code => $_, description => "$_ - " . $locations->{$_} }; } - - $req = $dbh->prepare("select distinctrow location from items order by location"); - $req->execute; - my $locationloop = $req->fetchall_arrayref({}); - my @mime = ( map { +{type =>$_} } (split /[;:]/,C4::Context->preference("MIME")) ); - my @delim = ( map { +{delim=>$_} } (split //,C4::Context->preference("delimiter")) ); + 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 => \@branchloop, - locationloop => $locationloop, - authvals => \@authvals, + CGIBranch => GetBranchesLoop(C4::Context->userenv->{'branch'}), + locationloop => \@locations, + authvals => \@authvals, CGIextChoice => \@mime, - CGIsepChoice => \@delim, + CGIsepChoice => GetDelimiterChoices, + item_itype => $item_itype ); } @@ -252,9 +187,23 @@ 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 # @@ -271,15 +220,15 @@ sub calculate { ($i== 1) ? "Dewey Classification To" : ($i== 2) ? "Lccn Classification From" : ($i== 3) ? "Lccn Classification To" : - ($i== 4) ? "Cote Classification From" : - ($i== 5) ? "Cote Classification To" : - ($i== 6) ? "Document type" : + ($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) ? "Branch :" : - ($i==11) ? "Location :" : - ($i==12) ? "Catalog Code :" : ''; + ($i==10) ? "Library :" : + ($i==11) ? "Shelving Location :" : + ($i==12) ? "Collection Code :" : ''; push @loopfilter, \%cell; } } @@ -293,7 +242,11 @@ sub calculate { $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/ ) ; + 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/ ) ; @@ -308,7 +261,11 @@ sub calculate { $colfilter[1] = @$filters[3] if ($column =~ /lccn/ ) ; $colfilter[0] = @$filters[4] if ($column =~ /items\.itemcallnumber/ ) ; $colfilter[1] = @$filters[5] if ($column =~ /items\.itemcallnumber/ ) ; - $colfilter[0] = @$filters[6] if ($column =~ /itemtype/ ) ; + 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/ ) ; @@ -328,27 +285,31 @@ sub calculate { $linefield .= $line; } - my $strsth; - $strsth .= "select distinctrow $linefield from biblioitems left join items on (items.biblioitemnumber = biblioitems.biblioitemnumber) where $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"; + $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; @@ -376,24 +337,28 @@ sub calculate { my $strsth2 = " SELECT distinctrow $colfield FROM biblioitems - LEFT JOIN items - ON (items.biblioitemnumber = biblioitems.biblioitemnumber) - WHERE $column IS NOT NULL "; + 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"; + $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; @@ -423,7 +388,9 @@ sub calculate { } # preparing calculation - my $strcalc .= "SELECT $linefield, $colfield, count(*) FROM biblioitems LEFT JOIN items ON (items.biblioitemnumber = biblioitems.biblioitemnumber) WHERE 1"; + 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]; @@ -453,7 +420,7 @@ sub calculate { if (@$filters[6]){ @$filters[6]=~ s/\*/%/g; $strcalc .= " AND " . - (C4::Context::preference('Item-level_itypes') ? 'items.itype' : 'biblioitems.itemtype') + (C4::Context->preference('item-level_itypes') ? 'items.itype' : 'biblioitems.itemtype') . " LIKE '" . @$filters[6] ."'"; } @@ -486,15 +453,19 @@ sub calculate { $strcalc .= " group by $linefield, $colfield order by $linefield,$colfield"; $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 "; - $emptycol = 1 if ($col eq undef); - $col = "zzEMPTY" if ($col eq undef); - $row = "zzEMPTY" if ($row eq undef); + $emptycol = 1 if (!defined($col)); + $col = "zzEMPTY" if (!defined($col)); + $row = "zzEMPTY" if (!defined($row)); $table{$row}->{$col}+=$value; $table{$row}->{totalrow}+=$value;