X-Git-Url: http://git.rot13.org/?a=blobdiff_plain;f=reports%2Fguided_reports.pl;h=4b45f953252a5f5f6bf54d484d6bfd0df0c734cc;hb=51e3e31627c38f88671f8ab399b470ec4a02aaec;hp=929feea20ac1c9f0486da6db17d44edc65fea9bf;hpb=537c66403855437d2ce52eeb758fc009c7105633;p=koha.git diff --git a/reports/guided_reports.pl b/reports/guided_reports.pl index 929feea20a..4b45f95325 100755 --- a/reports/guided_reports.pl +++ b/reports/guided_reports.pl @@ -4,30 +4,37 @@ # # This file is part of Koha. # -# Koha is free software; you can redistribute it and/or modify it under the -# terms of the GNU General Public License as published by the Free Software -# Foundation; either version 2 of the License, or (at your option) any later -# version. +# Koha is free software; you can redistribute it and/or modify it +# under the terms of the GNU General Public License as published by +# the Free Software Foundation; either version 3 of the License, or +# (at your option) any later version. # -# Koha is distributed in the hope that it will be useful, but WITHOUT ANY -# WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR -# A PARTICULAR PURPOSE. See the GNU General Public License for more details. +# Koha is distributed in the hope that it will be useful, but +# WITHOUT ANY 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., -# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA. - +# You should have received a copy of the GNU General Public License +# along with Koha; if not, see . +use Modern::Perl; use CGI qw/-utf8/; -use Text::CSV; +use Text::CSV::Encoded; +use Encode qw( decode ); use URI::Escape; +use File::Temp; +use File::Basename qw( dirname ); use C4::Reports::Guided; use C4::Auth qw/:DEFAULT get_session/; use C4::Output; -use C4::Dates qw/format_date/; use C4::Debug; -use C4::Branch; # XXX subfield_is_koha_internal_p -use C4::Koha qw/IsAuthorisedValueCategory/; +use C4::Koha qw/GetFrameworksLoop/; +use C4::Branch; +use C4::Context; +use C4::Log; +use Koha::DateUtils qw/dt_from_string output_pref/; +use Koha::AuthorisedValue; +use Koha::AuthorisedValues; =head1 NAME @@ -42,7 +49,7 @@ Script to control the guided report creation my $input = new CGI; my $usecache = C4::Context->ismemcached; -my $phase = $input->param('phase'); +my $phase = $input->param('phase') // ''; my $flagsrequired; if ( $phase eq 'Build new' or $phase eq 'Delete Saved' ) { $flagsrequired = 'create_reports'; @@ -55,7 +62,7 @@ elsif ( $phase eq 'Use saved' ) { my ( $template, $borrowernumber, $cookie ) = get_template_and_user( { - template_name => "reports/guided_reports_start.tmpl", + template_name => "reports/guided_reports_start.tt", query => $input, type => "intranet", authnotrequired => 0, @@ -72,7 +79,7 @@ if ( $input->param("filter_set") ) { $session->param('report_filter', $filter) if $session; $template->param( 'filter_set' => 1 ); } -elsif ($session) { +elsif ($session and not $input->param('clear_filters')) { $filter = $session->param('report_filter'); } @@ -85,12 +92,11 @@ if ( !$phase ) { elsif ( $phase eq 'Build new' ) { # build a new report $template->param( 'build1' => 1 ); - my $areas = get_report_areas(); $template->param( - 'areas' => [map { id => $_->[0], name => $_->[1] }, @$areas], - 'usecache' => $usecache, + 'areas' => get_report_areas(), + 'usecache' => $usecache, 'cache_expiry' => 300, - 'public' => '0', + 'public' => '0', ); } elsif ( $phase eq 'Use saved' ) { @@ -105,14 +111,22 @@ elsif ( $phase eq 'Build new' ) { 'savedreports' => get_saved_reports($filter), 'usecache' => $usecache, 'groups_with_subgroups'=> groups_with_subgroups($group, $subgroup), + filters => $filter, ); } +elsif ( $phase eq 'Delete Multiple') { + my @ids = $input->param('ids'); + delete_report( @ids ); + print $input->redirect("/cgi-bin/koha/reports/guided_reports.pl?phase=Use%20saved"); + exit; +} + elsif ( $phase eq 'Delete Saved') { # delete a report from the saved reports list - my $id = $input->param('reports'); - delete_report($id); + my $ids = $input->param('reports'); + delete_report($ids); print $input->redirect("/cgi-bin/koha/reports/guided_reports.pl?phase=Use%20saved"); exit; } @@ -183,7 +197,7 @@ elsif ( $phase eq 'Update SQL'){ push @errors, {sqlerr => $1}; } elsif ($sql !~ /^(SELECT)/i) { - push @errors, {queryerr => 1}; + push @errors, {queryerr => "No SELECT"}; } if (@errors) { @@ -206,8 +220,6 @@ elsif ( $phase eq 'Update SQL'){ 'group' => $group, 'subgroup' => $subgroup, 'notes' => $notes, - 'cache_expiry' => $cache_expiry, - 'cache_expiry_units' => $cache_expiry_units, 'public' => $public, 'problematic_authvals' => $problematic_authvals, 'warn_authval_problem' => 1, @@ -222,14 +234,21 @@ elsif ( $phase eq 'Update SQL'){ group => $group, subgroup => $subgroup, notes => $notes, - cache_expiry => $cache_expiry, public => $public, + cache_expiry => $cache_expiry, } ); $template->param( 'save_successful' => 1, 'reportname' => $reportname, 'id' => $id, ); + logaction( "REPORTS", "MODIFY", $id, "$reportname | $sql" ) if C4::Context->preference("ReportsLog"); + } + if ( $usecache ) { + $template->param( + cache_expiry => $cache_expiry, + cache_expiry_units => $cache_expiry_units, + ); } } } @@ -303,6 +322,7 @@ elsif ( $phase eq 'Choose these columns' ) { my $type = $input->param('type'); my @columns = $input->param('columns'); my $column = join( ',', @columns ); + $template->param( 'build4' => 1, 'area' => $area, @@ -310,10 +330,15 @@ elsif ( $phase eq 'Choose these columns' ) { 'column' => $column, definitions => get_from_dictionary($area), criteria => get_criteria($area,$input), - 'cache_expiry' => $input->param('cache_expiry'), - 'cache_expiry_units' => $input->param('cache_expiry_units'), 'public' => $input->param('public'), ); + if ( $usecache ) { + $template->param( + cache_expiry => $input->param('cache_expiry'), + cache_expiry_units => $input->param('cache_expiry_units'), + ); + } + } elsif ( $phase eq 'Choose these criteria' ) { @@ -334,9 +359,13 @@ elsif ( $phase eq 'Choose these criteria' ) { my $tovalue = $input->param( "to_" . $crit . "_value" ); # If the range values are dates - if ($fromvalue =~ C4::Dates->regexp('syspref') && $tovalue =~ C4::Dates->regexp('syspref')) { - $fromvalue = C4::Dates->new($fromvalue)->output("iso"); - $tovalue = C4::Dates->new($tovalue)->output("iso"); + my $fromvalue_dt; + $fromvalue_dt = eval { dt_from_string( $fromvalue ); } if ( $fromvalue ); + my $tovalue_dt; + $tovalue_dt = eval { dt_from_string( $tovalue ); } if ($tovalue); + if ( $fromvalue_dt && $tovalue_dt ) { + $fromvalue = output_pref( { dt => dt_from_string( $fromvalue_dt ), dateonly => 1, dateformat => 'iso' } ); + $tovalue = output_pref( { dt => dt_from_string( $tovalue_dt ), dateonly => 1, dateformat => 'iso' } ); } if ($fromvalue && $tovalue) { @@ -346,8 +375,10 @@ elsif ( $phase eq 'Choose these criteria' ) { } else { # If value is a date - if ($value =~ C4::Dates->regexp('syspref')) { - $value = C4::Dates->new($value)->output("iso"); + my $value_dt; + $value_dt = eval { dt_from_string( $value ); } if ( $value ); + if ( $value_dt ) { + $value = output_pref( { dt => dt_from_string( $value_dt ), dateonly => 1, dateformat => 'iso' } ); } # don't escape runtime parameters, they'll be at runtime if ($value =~ /<<.*>>/) { @@ -364,10 +395,14 @@ elsif ( $phase eq 'Choose these criteria' ) { 'column' => $column, 'definition' => $definition, 'criteriastring' => $query_criteria, - 'cache_expiry' => $input->param('cache_expiry'), - 'cache_expiry_units' => $input->param('cache_expiry_units'), 'public' => $input->param('public'), ); + if ( $usecache ) { + $template->param( + cache_expiry => $input->param('cache_expiry'), + cache_expiry_units => $input->param('cache_expiry_units'), + ); + } # get columns my @columns = split( ',', $column ); @@ -546,13 +581,17 @@ elsif ( $phase eq 'Save Report' ) { 'reportname' => $name, 'type' => $type, 'notes' => $notes, - 'cache_expiry' => $cache_expiry, - 'cache_expiry_units' => $cache_expiry_units, 'public' => $public, 'problematic_authvals' => $problematic_authvals, 'warn_authval_problem' => 1, 'phase_save' => 1 ); + if ( $usecache ) { + $template->param( + cache_expiry => $cache_expiry, + cache_expiry_units => $cache_expiry_units, + ); + } } else { # No params problem found or asked to save anyway my $id = save_report( { @@ -567,6 +606,7 @@ elsif ( $phase eq 'Save Report' ) { cache_expiry => $cache_expiry, public => $public, } ); + logaction( "REPORTS", "ADD", $id, "$name | $sql" ) if C4::Context->preference("ReportsLog"); $template->param( 'save_successful' => 1, 'reportname' => $name, @@ -636,6 +676,16 @@ elsif ($phase eq 'Run this report'){ $authorised_lib{$itemtype} = $description; } } + elsif ( $authorised_value eq "biblio_framework" ) { + my $frameworks = GetFrameworksLoop(); + my $default_source = ''; + push @authorised_values,$default_source; + $authorised_lib{$default_source} = 'Default'; + foreach my $framework (@$frameworks) { + push @authorised_values, $framework->{value}; + $authorised_lib{$framework->{value}} = $framework->{description}; + } + } elsif ( $authorised_value eq "cn_source" ) { my $class_sources = GetClassSources(); my $default_source = C4::Context->preference("DefaultClassificationSource"); @@ -657,7 +707,7 @@ elsif ($phase eq 'Run this report'){ #---- "true" authorised value } else { - if ( IsAuthorisedValueCategory($authorised_value) ) { + if ( Koha::AuthorisedValues->search({ category => $authorised_value })->count ) { my $query = ' SELECT authorised_value,lib FROM authorised_values @@ -685,17 +735,12 @@ elsif ($phase eq 'Run this report'){ } $labelid = $text; $labelid =~ s/\W//g; - $input =CGI::scrolling_list( # FIXME: factor out scrolling_list - -name => "sql_params", - -id => "sql_params_".$labelid, - -values => \@authorised_values, -# -default => $value, - -labels => \%authorised_lib, - -override => 1, - -size => 1, - -multiple => 0, - -tabindex => 1, - ); + $input = { + name => "sql_params", + id => "sql_params_".$labelid, + values => \@authorised_values, + labels => \%authorised_lib, + }; } push @tmpl_parameters, {'entry' => $text, 'input' => $input, 'labelid' => $labelid }; @@ -714,9 +759,13 @@ elsif ($phase eq 'Run this report'){ my @split = split /<<|>>/,$sql; my @tmpl_parameters; for(my $i=0;$i<$#split/2;$i++) { - my $quoted = C4::Context->dbh->quote($sql_params[$i]); + my $quoted = $sql_params[$i]; # if there are special regexp chars, we must \ them $split[$i*2+1] =~ s/(\||\?|\.|\*|\(|\)|\%)/\\$1/g; + if ($split[$i*2+1] =~ /\|\s*date\s*$/) { + $quoted = output_pref({ dt => dt_from_string($quoted), dateformat => 'iso', dateonly => 1 }) if $quoted; + } + $quoted = C4::Context->dbh->quote($quoted); $sql =~ s/<<$split[$i*2+1]>>/$quoted/; } my ($sth, $errors) = execute_query($sql, $offset, $limit); @@ -724,7 +773,7 @@ elsif ($phase eq 'Run this report'){ unless ($sth) { die "execute_query failed to return sth for report $report_id: $sql"; } else { - my $headers= header_cell_loop($sth); + my $headers = header_cell_loop($sth); $template->param(header_row => $headers); while (my $row = $sth->fetchrow_arrayref()) { my @cells = map { +{ cell => $_ } } @$row; @@ -735,7 +784,7 @@ elsif ($phase eq 'Run this report'){ my $totpages = int($total/$limit) + (($total % $limit) > 0 ? 1 : 0); my $url = "/cgi-bin/koha/reports/guided_reports.pl?reports=$report_id&phase=Run%20this%20report&limit=$limit"; if (@sql_params) { - $url = join('&sql_params=', $url, map { URI::Escape::uri_escape($_) } @sql_params); + $url = join('&sql_params=', $url, map { URI::Escape::uri_escape_utf8($_) } @sql_params); } $template->param( 'results' => \@rows, @@ -744,7 +793,7 @@ elsif ($phase eq 'Run this report'){ 'execute' => 1, 'name' => $name, 'notes' => $notes, - 'errors' => $errors, + 'errors' => defined($errors) ? [ $errors ] : undef, 'pagination_bar' => pagination_bar($url, $totpages, $input->param('page')), 'unlimited_total' => $total, 'sql_params' => \@sql_params, @@ -757,37 +806,90 @@ elsif ($phase eq 'Run this report'){ } elsif ($phase eq 'Export'){ - binmode STDOUT, ':encoding(UTF-8)'; # export results to tab separated text or CSV my $sql = $input->param('sql'); # FIXME: use sql from saved report ID#, not new user-supplied SQL! my $format = $input->param('format'); + my $reportname = $input->param('reportname'); + my $reportfilename = $reportname ? "$reportname-reportresults.$format" : "reportresults.$format" ; my ($sth, $q_errors) = execute_query($sql); unless ($q_errors and @$q_errors) { - print $input->header( -type => 'application/octet-stream', - -attachment=>"reportresults.$format" - ); + my ( $type, $content ); if ($format eq 'tab') { - print join("\t", header_cell_values($sth)), "\n"; + $type = 'application/octet-stream'; + $content .= join("\t", header_cell_values($sth)) . "\n"; + $content = Encode::decode('UTF-8', $content); while (my $row = $sth->fetchrow_arrayref()) { - print join("\t", @$row), "\n"; + $content .= join("\t", @$row) . "\n"; } } else { - my $csv = Text::CSV->new({binary => 1}); - $csv or die "Text::CSV->new({binary => 1}) FAILED: " . Text::CSV->error_diag(); - if ($csv->combine(header_cell_values($sth))) { - print $csv->string(), "\n"; - } else { - push @$q_errors, { combine => 'HEADER ROW: ' . $csv->error_diag() } ; - } - while (my $row = $sth->fetchrow_arrayref()) { - if ($csv->combine(@$row)) { - print $csv->string(), "\n"; + my $delimiter = C4::Context->preference('delimiter') || ','; + if ( $format eq 'csv' ) { + $type = 'application/csv'; + my $csv = Text::CSV::Encoded->new({ encoding_out => 'UTF-8', sep_char => $delimiter}); + $csv or die "Text::CSV::Encoded->new({binary => 1}) FAILED: " . Text::CSV::Encoded->error_diag(); + if ($csv->combine(header_cell_values($sth))) { + $content .= Encode::decode('UTF-8', $csv->string()) . "\n"; } else { - push @$q_errors, { combine => $csv->error_diag() } ; + push @$q_errors, { combine => 'HEADER ROW: ' . $csv->error_diag() } ; + } + while (my $row = $sth->fetchrow_arrayref()) { + if ($csv->combine(@$row)) { + $content .= $csv->string() . "\n"; + } else { + push @$q_errors, { combine => $csv->error_diag() } ; + } } } + elsif ( $format eq 'ods' ) { + $type = 'application/vnd.oasis.opendocument.spreadsheet'; + my $ods_fh = File::Temp->new( UNLINK => 0 ); + my $ods_filepath = $ods_fh->filename; + + use OpenOffice::OODoc; + my $tmpdir = dirname $ods_filepath; + odfWorkingDirectory( $tmpdir ); + my $container = odfContainer( $ods_filepath, create => 'spreadsheet' ); + my $doc = odfDocument ( + container => $container, + part => 'content' + ); + my $table = $doc->getTable(0); + my @headers = header_cell_values( $sth ); + my $rows = $sth->fetchall_arrayref(); + my ( $nb_rows, $nb_cols ) = ( 0, 0 ); + $nb_rows = @$rows; + $nb_cols = @headers; + $doc->expandTable( $table, $nb_rows + 1, $nb_cols ); + + my $row = $doc->getRow( $table, 0 ); + my $j = 0; + for my $header ( @headers ) { + $doc->cellValue( $row, $j, $header ); + $j++; + } + my $i = 1; + for ( @$rows ) { + $row = $doc->getRow( $table, $i ); + for ( my $j = 0 ; $j < $nb_cols ; $j++ ) { + my $value = Encode::encode( 'UTF8', $rows->[$i - 1][$j] ); + $doc->cellValue( $row, $j, $value ); + } + $i++; + } + $doc->save(); + binmode(STDOUT); + open $ods_fh, '<', $ods_filepath; + $content .= $_ while <$ods_fh>; + unlink $ods_filepath; + } } + print $input->header( + -type => $type, + -attachment=> $reportfilename + ); + print $content; + foreach my $err (@$q_errors, @errors) { print "# ERROR: " . (map {$_ . ": " . $err->{$_}} keys %$err) . "\n"; } # here we print all the non-fatal errors at the end. Not super smooth, but better than nothing. @@ -843,23 +945,18 @@ elsif ($phase eq 'Save Compound'){ # pass $sth, get back an array of names for the column headers sub header_cell_values { my $sth = shift or return (); - my @cols; - foreach my $c (@{$sth->{NAME}}) { - #FIXME apparently DBI still needs a utf8 fix for this? - utf8::decode($c); - push @cols, $c; - } - return @cols; + return '' unless ($sth->{NAME}); + return @{$sth->{NAME}}; } # pass $sth, get back a TMPL_LOOP-able set of names for the column headers sub header_cell_loop { - my @headers = map { +{ cell => $_ } } header_cell_values (shift); + my @headers = map { +{ cell => decode('UTF-8',$_) } } header_cell_values (shift); return \@headers; } foreach (1..6) { - $template->{VARS}->{'build' . $_} and $template->{VARS}->{'buildx' . $_} and last; + $template->{VARS}->{'build' . $_} and last; } $template->param( 'referer' => $input->referer(), ); @@ -903,12 +1000,21 @@ sub create_non_existing_group_and_subgroup { my $report_groups = C4::Reports::Guided::get_report_groups; if (not exists $report_groups->{$group}) { my $groupdesc = $input->param('groupdesc') // $group; - C4::Koha::AddAuthorisedValue('REPORT_GROUP', $group, $groupdesc); + Koha::AuthorisedValue->new({ + category => 'REPORT_GROUP', + authorised_value => $group, + lib => $groupdesc, + })->store; } if (defined $subgroup and $subgroup ne '') { if (not exists $report_groups->{$group}->{subgroups}->{$subgroup}) { my $subgroupdesc = $input->param('subgroupdesc') // $subgroup; - C4::Koha::AddAuthorisedValue('REPORT_SUBGROUP', $subgroup, $subgroupdesc, $group); + Koha::AuthorisedValue->new({ + category => 'REPORT_SUBGROUP', + authorised_value => $subgroup, + lib => $subgroupdesc, + lib_opac => $group, + })->store; } } }