X-Git-Url: http://koha-dev.rot13.org:8081/gitweb/?a=blobdiff_plain;f=reports%2Fcatalogue_stats.pl;h=b4d72a384be6bda07a759b917f5b9b9f501e1600;hb=0d7ba6e5201b16f7e7a2332af7204cb6416c289a;hp=551c10ab14fa35774a2ba4096e1c58aa7b1dd2e2;hpb=b8848f6aedfe54ab7d67d9867518cb98c4af16b8;p=koha-ffzg.git diff --git a/reports/catalogue_stats.pl b/reports/catalogue_stats.pl index 551c10ab14..b4d72a384b 100755 --- a/reports/catalogue_stats.pl +++ b/reports/catalogue_stats.pl @@ -18,17 +18,18 @@ # You should have received a copy of the GNU General Public License # along with Koha; if not, see . -use strict; -#use warnings; FIXME - Bug 2505 -use C4::Auth; +use Modern::Perl; +use C4::Auth qw( get_template_and_user ); use CGI qw ( -utf8 ); use C4::Context; -use C4::Branch; # GetBranches -use C4::Output; -use C4::Koha; -use C4::Reports; -use C4::Circulation; -use C4::Biblio qw/GetMarcSubfieldStructureFromKohaField/; +use C4::Output qw( output_html_with_http_headers ); +use C4::Koha qw( GetAuthorisedValues ); +use C4::Reports qw( GetDelimiterChoices ); +use C4::Biblio qw( GetMarcSubfieldStructureFromKohaField ); + +use Koha::AuthorisedValues; +use Koha::DateUtils qw( dt_from_string ); +use Koha::ItemTypes; =head1 NAME @@ -39,18 +40,17 @@ plugin that shows a stats on borrowers =cut our $debug = 0; -my $input = new CGI; +my $input = CGI->new; my $fullreportname = "reports/catalogue_stats.tt"; my $do_it = $input->param('do_it'); my $line = $input->param("Line"); my $column = $input->param("Column"); my $cellvalue = $input->param("Cellvalue"); # one of 'items', 'biblios', 'deleteditems' -my @filters = $input->param("Filter"); +my @filters = $input->multi_param("Filter"); 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'); +our $sep = C4::Context->csv_delimiter(scalar $input->param("sep")); my $item_itype; if(C4::Context->preference('item-level_itypes')) { $item_itype = "items\.itype" @@ -68,9 +68,7 @@ my ($template, $borrowernumber, $cookie) = get_template_and_user({template_name => $fullreportname, query => $input, type => "intranet", - authnotrequired => 0, flagsrequired => {reports => '*'}, - debug => 1, }); $template->param(do_it => $do_it); if ($do_it) { @@ -81,7 +79,7 @@ if ($do_it) { exit; } else { print $input->header( - -type => 'application/vnd.sun.xml.calc', + -type => 'text/csv', -encoding => 'utf-8', -attachment => "$basename.csv", -name => "$basename.csv" @@ -112,25 +110,12 @@ if ($do_it) { } } else { my $dbh = C4::Context->dbh; - my @values; - my %labels; my $count=0; - my $req; - my @select; - - my $itemtypes = GetItemTypes( style => 'array' ); - 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 $itemtypes = Koha::ItemTypes->search_with_localization; - my $locations = GetKohaAuthorisedValues("items.location"); - my @locations; - foreach ( sort keys %$locations ) { - push @locations, { code => $_, description => "$_ - " . $locations->{$_} }; - } + my @authvals = map { { code => $_->{authorised_value}, description => $_->{lib} } } Koha::AuthorisedValues->get_descriptions_by_koha_field( { frameworkcode => '', kohafield => 'items.ccode' }, { order_by => ['description'] } ); + my @locations = map { { code => $_->{authorised_value}, description => $_->{lib} } } Koha::AuthorisedValues->get_descriptions_by_koha_field( { frameworkcode => '', kohafield => 'items.location' }, { order_by => ['description'] } ); foreach my $kohafield (qw(items.notforloan items.materials)) { my $subfield_structure = GetMarcSubfieldStructureFromKohaField($kohafield); @@ -154,7 +139,6 @@ if ($do_it) { $template->param( itemtypes => $itemtypes, - CGIBranch => GetBranchesLoop( C4::Context->userenv->{'branch'} ), locationloop => \@locations, authvals => \@authvals, CGIextChoice => \@mime, @@ -221,20 +205,20 @@ sub calculate { : ( $i == 10 ) ? "Materials" : ( $i == 12 and $filters->[11] == 0 ) ? "Barcode (not like)" : ( $i == 12 and $filters->[11] == 1 ) ? "Barcode (like)" - : ( $i == 13 ) ? "Acquired date from" - : ( $i == 14 ) ? "Acquired date to" - : ( $i == 15 ) ? "Removed date from" - : ( $i == 16 ) ? "Removed date to" + : ( $i == 13 ) ? "Date acquired (item) from" + : ( $i == 14 ) ? "Date acquired (item) to" + : ( $i == 15 ) ? "Date deleted (item) from" + : ( $i == 16 ) ? "Date deleted (item) to" : ''; push @loopfilter, \%cell; } } - @$filters[14] = C4::Dates->new(@$filters[14])->output('iso') if @$filters[14]; - @$filters[15] = C4::Dates->new(@$filters[15])->output('iso') if @$filters[15]; - @$filters[16] = C4::Dates->new(@$filters[16])->output('iso') if @$filters[16]; - @$filters[17] = C4::Dates->new(@$filters[17])->output('iso') if @$filters[17]; + @$filters[13] = dt_from_string(@$filters[13])->date() if @$filters[13]; + @$filters[14] = dt_from_string(@$filters[14])->date() if @$filters[14]; + @$filters[15] = dt_from_string(@$filters[15])->date() if @$filters[15]; + @$filters[16] = dt_from_string(@$filters[16])->date() if @$filters[16]; my @linefilter; $linefilter[0] = @$filters[0] if ( $line =~ /items\.itemcallnumber/ ); @@ -322,7 +306,7 @@ sub calculate { } else { $sth->execute(); } - my $rowauthvals = GetKohaAuthorisedValues($origline); + 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 '') { @@ -385,7 +369,7 @@ sub calculate { } else { $sth2->execute(); } - my $colauthvals = GetKohaAuthorisedValues($origcolumn); + 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 '') { @@ -405,7 +389,6 @@ sub calculate { } my $i = 0; - my @totalcol; my $hilighted = -1; #Initialization of cell values..... @@ -427,84 +410,101 @@ sub calculate { LEFT JOIN biblioitems ON ($itemstable.biblioitemnumber = biblioitems.biblioitemnumber) LEFT JOIN biblio ON (biblioitems.biblionumber = biblio.biblionumber) WHERE 1 "; - $strcalc .= "AND barcode $not like ? " if ($barcodefilter); + + my @sqlargs; + + if ($barcodefilter) { + $strcalc .= "AND barcode $not like ? "; + push @sqlargs, $barcodefilter; + } if ( @$filters[0] ) { + $strcalc .= " AND $itemstable.itemcallnumber >= ? "; @$filters[0] =~ s/\*/%/g; - $strcalc .= " AND $itemstable.itemcallnumber >=" . $dbh->quote( @$filters[0] ); + push @sqlargs, @$filters[0]; } if ( @$filters[1] ) { + $strcalc .= " AND $itemstable.itemcallnumber <= ? "; @$filters[1] =~ s/\*/%/g; - $strcalc .= " AND $itemstable.itemcallnumber <=" . $dbh->quote( @$filters[1] ); + push @sqlargs, @$filters[1]; } if ( @$filters[2] ) { + $strcalc .= " AND " . ( C4::Context->preference('item-level_itypes') ? "$itemstable.itype" : 'biblioitems.itemtype' ) . " LIKE ? "; @$filters[2] =~ s/\*/%/g; - $strcalc .= " AND " . ( C4::Context->preference('item-level_itypes') ? "$itemstable.itype" : 'biblioitems.itemtype' ) . " LIKE '" . @$filters[2] . "'"; + push @sqlargs, @$filters[2]; } if ( @$filters[3] ) { + $strcalc .= " AND biblioitems.publishercode LIKE ? "; @$filters[3] =~ s/\*/%/g; @$filters[3] .= "%" unless @$filters[3] =~ /%/; - $strcalc .= " AND biblioitems.publishercode LIKE \"" . @$filters[3] . "\""; + push @sqlargs, @$filters[3]; } if ( @$filters[4] ) { - @$filters[4] =~ s/\*/%/g; $strcalc .= " AND " . (C4::Context->preference('marcflavour') eq 'UNIMARC' ? 'publicationyear' : 'copyrightdate') - . ">" . @$filters[4]; + . "> ? "; + @$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') - . "<" . @$filters[5]; + . "< ? "; + push @sqlargs, @$filters[5]; } if ( @$filters[6] ) { + $strcalc .= " AND $itemstable.homebranch LIKE ? "; @$filters[6] =~ s/\*/%/g; - $strcalc .= " AND $itemstable.homebranch LIKE '" . @$filters[6] . "'"; + push @sqlargs, @$filters[6]; } if ( @$filters[7] ) { + $strcalc .= " AND $itemstable.location LIKE ? "; @$filters[7] =~ s/\*/%/g; - $strcalc .= " AND $itemstable.location LIKE '" . @$filters[7] . "'"; + push @sqlargs, @$filters[7]; } if ( @$filters[8] ) { + $strcalc .= " AND $itemstable.ccode LIKE ? "; @$filters[8] =~ s/\*/%/g; - $strcalc .= " AND $itemstable.ccode LIKE '" . @$filters[8] . "'"; + push @sqlargs, @$filters[8]; } if ( defined @$filters[9] and @$filters[9] ne '' ) { + $strcalc .= " AND $itemstable.notforloan LIKE ? "; @$filters[9] =~ s/\*/%/g; - $strcalc .= " AND $itemstable.notforloan LIKE '" . @$filters[9] . "'"; + push @sqlargs, @$filters[9]; } if ( defined @$filters[10] and @$filters[10] ne '' ) { + $strcalc .= " AND $itemstable.materials LIKE ? "; @$filters[10] =~ s/\*/%/g; - $strcalc .= " AND $itemstable.materials LIKE '" . @$filters[10] . "'"; + push @sqlargs, @$filters[10]; } if ( @$filters[13] ) { + $strcalc .= " AND $itemstable.dateaccessioned >= ? "; @$filters[13] =~ s/\*/%/g; - $strcalc .= " AND $itemstable.dateaccessioned >= '@$filters[13]' "; + push @sqlargs, @$filters[13]; } if ( @$filters[14] ) { + $strcalc .= " AND $itemstable.dateaccessioned <= ? "; @$filters[14] =~ s/\*/%/g; - $strcalc .= " AND $itemstable.dateaccessioned <= '@$filters[14]' "; + push @sqlargs, @$filters[14]; } if ( $cellvalue eq 'deleteditems' and @$filters[15] ) { + $strcalc .= " AND DATE(deleteditems.timestamp) >= ? "; @$filters[15] =~ s/\*/%/g; - $strcalc .= " AND DATE(deleteditems.timestamp) >= '@$filters[15]' "; + push @sqlargs, @$filters[15]; } if ( $cellvalue eq 'deleteditems' and @$filters[16] ) { @$filters[16] =~ s/\*/%/g; - $strcalc .= " AND DATE(deleteditems.timestamp) <= '@$filters[16]' "; + $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); - if ($barcodefilter) { - $dbcalc->execute($barcodefilter); - } else { - $dbcalc->execute(); - } + $dbcalc->execute(@sqlargs); while ( my ( $row, $col, $value ) = $dbcalc->fetchrow ) {