From 60262720f0d371e79d2eb1ef1d5ebc1b8c56eb87 Mon Sep 17 00:00:00 2001 From: Joe Atzberger Date: Thu, 10 Apr 2008 23:07:40 -0500 Subject: [PATCH] Item-level_itypes fix, addition of ccode vector and filter. Signed-off-by: Joshua Ferraro --- .../en/modules/reports/catalogue_stats.tmpl | 59 +++- reports/catalogue_stats.pl | 269 ++++++++---------- 2 files changed, 172 insertions(+), 156 deletions(-) diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/catalogue_stats.tmpl b/koha-tmpl/intranet-tmpl/prog/en/modules/reports/catalogue_stats.tmpl index 8e6d11ebf1..79573d1764 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/catalogue_stats.tmpl +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/reports/catalogue_stats.tmpl @@ -150,7 +150,13 @@ Document Type - + + Publisher @@ -168,23 +174,60 @@ Home Branch - + + Location - + + + + + Catalogue Code + + + +
Output -
    2. -
  2. -
+
    2. +
  2. + + + + + +
    3. +
@@ -200,4 +243,4 @@ - \ No newline at end of file + diff --git a/reports/catalogue_stats.pl b/reports/catalogue_stats.pl index 3ac0216950..47ef65af2d 100755 --- a/reports/catalogue_stats.pl +++ b/reports/catalogue_stats.pl @@ -37,19 +37,20 @@ plugin that shows a stats on borrowers =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"); +my $del = $input->param("sep"); my ($template, $borrowernumber, $cookie) = get_template_and_user({template_name => $fullreportname, @@ -71,10 +72,9 @@ if ($do_it) { -encoding => 'utf-8', -attachment=>"$basename.csv", -name=>"$basename.csv" ); - my $cols = @$results[0]->{loopcol}; + my $cols = @$results[0]->{loopcol}; my $lines = @$results[0]->{looprow}; - my $sep; - $sep =C4::Context->preference("delimiter"); + my $sep = C4::Context->preference("delimiter"); print @$results[0]->{line} ."/". @$results[0]->{column} .$sep; foreach my $col ( @$cols ) { print $col->{coltitle}.$sep; @@ -103,16 +103,19 @@ if ($do_it) { my %labels; my $count=0; my $req; - $req = $dbh->prepare("select count(dewey) from biblioitems "); - $req->execute; - my $hasdewey; my @select; + # FIXME: no such field "dewey" + # $req = $dbh->prepare("select count(dewey) from biblioitems "); + # $req->execute; + my $hasdewey = 0; # push @select,""; - while (my ($value) =$req->fetchrow) { - $hasdewey =1 if (($value>2) and (! $hasdewey)); - $count++ if (($value>2) and (! $hasdewey)); +# 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, @@ -154,35 +157,27 @@ if ($do_it) { # -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; + 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; - $req = $dbh->prepare("select itemtype from itemtypes order by itemtype"); + $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 $CGIitemtype = $req->fetchall_arrayref({}); + + my $authvals = GetKohaAuthorisedValues("items.ccode"); + my @authvals; + foreach (keys %$authvals) { + push @authvals, { code => $_, description => $authvals->{$_} }; } - my $CGIitemtype=CGI::scrolling_list( -name => 'Filter', - -id => 'itemtype', - -values => \@select, - -size => 1, - -multiple => 0 ); # $req = $dbh->prepare("select distinctrow left(publishercode,75) from biblioitems order by publishercode"); # $req->execute; @@ -197,54 +192,23 @@ if ($do_it) { # -size => 1, # -multiple => 0 ); - 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 @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 $CGIbranch=CGI::scrolling_list( -name => 'Filter', - -id => 'branch', - -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,""; - while (my ($value) =$req->fetchrow) { - push @select, $value; - } - my $CGIlocation=CGI::scrolling_list( -name => 'Filter', - -id => 'location', - -values => \@select, - -size => 1, - -multiple => 0 ); + my $locationloop = $req->fetchall_arrayref({}); - my @mime = ( C4::Context->preference("MIME") ); - foreach my $mime (@mime){ -# warn "".$mime; - } - - 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")) ); + my @delim = ( map { +{delim=>$_} } (split //,C4::Context->preference("delimiter")) ); $template->param(hasdewey=>$hasdewey, # CGIFromDeweyClass => $CGIdewey, @@ -266,15 +230,17 @@ if ($do_it) { # CGIFromPublicationYear => $CGIpublicationyear, # CGIToPublicationYear => $CGIpublicationyear, # CGIPublisher => $CGIpublisher, - CGIBranch => $CGIbranch, - CGILocation => $CGIlocation, - CGIextChoice => $CGIextChoice, - CGIsepChoice => $CGIsepChoice + CGIBranch => \@branchloop, + locationloop => $locationloop, + authvals => \@authvals, + CGIextChoice => \@mime, + CGIsepChoice => \@delim, ); } output_html_with_http_headers $input, $cookie, $template->output; +## End of Main Body sub calculate { @@ -293,61 +259,62 @@ sub calculate { # 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) ? "Cote Classification From" : + ($i== 5) ? "Cote Classification To" : + ($i== 6) ? "Document type" : + ($i== 7) ? "Publisher" : + ($i== 8) ? "Publication year From" : + ($i== 9) ? "Publication year To" : + ($i==10) ? "Branch :" : + ($i==11) ? "Location :" : + ($i==12) ? "Catalog 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[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[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[4] if ($column =~ /items\.itemcallnumber/ ) ; + $colfilter[1] = @$filters[5] if ($column =~ /items\.itemcallnumber/ ) ; $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; @@ -360,8 +327,7 @@ sub calculate { }else { $linefield .= $line; } - - + my $strsth; $strsth .= "select distinctrow $linefield from biblioitems left join items on (items.biblioitemnumber = biblioitems.biblioitemnumber) where $line is not null "; if ( @linefilter ) { @@ -374,7 +340,7 @@ sub calculate { } } $strsth .=" order by $linefield"; - warn "". $strsth; + $debug and print STDERR "catalogue_stats SQL: $strsth\n"; my $sth = $dbh->prepare( $strsth ); if (( @linefilter ) and ($linefilter[1])){ @@ -398,17 +364,21 @@ 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 left join items on (items.biblioitemnumber = biblioitems.biblioitemnumber) where $column is not null "; + my $strsth2 = " + SELECT distinctrow $colfield + FROM biblioitems + LEFT JOIN items + ON (items.biblioitemnumber = biblioitems.biblioitemnumber) + WHERE $column IS NOT NULL "; if (( @colfilter ) and ($colfilter[1])) { $strsth2 .= " and $column> ? and $column< ?"; }elsif ($colfilter[0]){ @@ -416,7 +386,7 @@ sub calculate { $strsth2 .= " and $column LIKE ? "; } $strsth2 .= " order by $colfield"; - warn "". $strsth2; + $debug and print STDERR "SQL: $strsth2"; my $sth2 = $dbh->prepare( $strsth2 ); if ((@colfilter) and ($colfilter[1])) { $sth2->execute($colfilter[0],$colfilter[1]); @@ -437,7 +407,6 @@ sub calculate { push @loopcol, \%cell; } - my $i=0; my @totalcol; my $hilighted=-1; @@ -454,63 +423,68 @@ 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 LEFT JOIN items ON (items.biblioitemnumber = biblioitems.biblioitemnumber) WHERE 1"; if (@$filters[0]){ @$filters[0]=~ s/\*/%/g; - $strcalc .= " AND dewey >" . @$filters[0] .""; + $strcalc .= " AND dewey >" . @$filters[0]; } if (@$filters[1]){ @$filters[1]=~ s/\*/%/g ; - $strcalc .= " AND dewey <" . @$filters[1] .""; - + $strcalc .= " AND dewey <" . @$filters[1]; } if (@$filters[2]){ @$filters[2]=~ s/\*/%/g ; - $strcalc .= " AND lccn >" . @$filters[2] ."" ; + $strcalc .= " AND lccn >" . @$filters[2]; } if (@$filters[3]){ @$filters[3]=~ s/\*/%/g; - $strcalc .= " AND lccn <" . @$filters[3] ."" ; + $strcalc .= " AND lccn <" . @$filters[3]; } if (@$filters[4]){ @$filters[4]=~ s/\*/%/g ; - $strcalc .= " AND items.itemcallnumber >=" . $dbh->quote(@$filters[4]) ."" ; + $strcalc .= " AND items.itemcallnumber >=" . $dbh->quote(@$filters[4]); } if (@$filters[5]){ @$filters[5]=~ s/\*/%/g; - $strcalc .= " AND items.itemcallnumber <=" . $dbh->quote(@$filters[5]) ."" ; + $strcalc .= " AND items.itemcallnumber <=" . $dbh->quote(@$filters[5]); } if (@$filters[6]){ @$filters[6]=~ s/\*/%/g; - $strcalc .= " AND biblioitems.itemtype like '" . @$filters[6] ."'"; + $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] ."\""; + $strcalc .= " AND biblioitems.publishercode LIKE \"" . @$filters[7] ."\""; } if (@$filters[8]){ @$filters[8]=~ s/\*/%/g; - $strcalc .= " AND publicationyear >" . @$filters[8] ."" ; + $strcalc .= " AND publicationyear >" . @$filters[8]; } if (@$filters[9]){ @$filters[9]=~ s/\*/%/g; - $strcalc .= " AND publicationyear <" . @$filters[9] .""; + $strcalc .= " AND publicationyear <" . @$filters[9]; } if (@$filters[10]){ @$filters[10]=~ s/\*/%/g; - $strcalc .= " AND items.homebranch like '" . @$filters[10] ."'"; + $strcalc .= " AND items.homebranch LIKE '" . @$filters[10] ."'"; } if (@$filters[11]){ @$filters[11]=~ s/\*/%/g; - $strcalc .= " AND items.location like '" . @$filters[11] ."'" if ( @$filters[11] ); + $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; # warn "filling table"; @@ -518,7 +492,7 @@ sub calculate { my $emptycol; while (my ($row, $col, $value) = $dbcalc->fetchrow) { # warn "filling table $row / $col / $value "; - $emptycol = 1 if ($col eq undef); + $emptycol = 1 if ($col eq undef); $col = "zzEMPTY" if ($col eq undef); $row = "zzEMPTY" if ($row eq undef); @@ -544,10 +518,9 @@ sub calculate { } push @looprow,{ 'rowtitle' => ($row eq "zzEMPTY")?"NULL":$row, 'loopcell' => \@loopcell, - 'hilighted' => ($hilighted >0), + 'hilighted' => ($hilighted *= -1 > 0), 'totalrow' => $table{$row}->{totalrow} }; - $hilighted = -$hilighted; } # warn "footer processing"; -- 2.39.5