X-Git-Url: http://koha-dev.rot13.org:8081/gitweb/?a=blobdiff_plain;f=reports%2Fguided_reports.pl;h=77c19b3cb4626b86424288765a56a044ab6d814f;hb=9d6d641d1f8b77271800f43bc027b651f9aea52b;hp=aad04181455a12b49c4f81c523d861701a9f5720;hpb=6a17bf77853a433c71938a1344486932c30d899a;p=srvgit diff --git a/reports/guided_reports.pl b/reports/guided_reports.pl index aad0418145..77c19b3cb4 100755 --- a/reports/guided_reports.pl +++ b/reports/guided_reports.pl @@ -23,22 +23,21 @@ use Text::CSV::Encoded; use Encode qw( decode ); use URI::Escape; use File::Temp; -use C4::Reports::Guided; +use C4::Reports::Guided qw( delete_report get_report_areas convert_sql update_sql get_saved_reports get_results ValidateSQLParameters format_results get_report_types get_columns get_from_dictionary get_criteria build_query save_report execute_query nb_rows get_report_groups ); use Koha::Reports; -use C4::Auth qw/:DEFAULT get_session/; -use C4::Output; -use C4::Debug; +use C4::Auth qw( get_template_and_user get_session ); +use C4::Output qw( pagination_bar output_html_with_http_headers ); use C4::Context; use Koha::Caches; -use C4::Log; -use Koha::DateUtils qw/dt_from_string output_pref/; +use C4::Log qw( logaction ); +use Koha::DateUtils qw( dt_from_string output_pref ); use Koha::AuthorisedValue; use Koha::AuthorisedValues; use Koha::BiblioFrameworks; use Koha::Libraries; use Koha::Patron::Categories; use Koha::SharedContent; -use Koha::Util::OpenDocument; +use Koha::Util::OpenDocument qw( generate_ods ); =head1 NAME @@ -50,12 +49,13 @@ Script to control the guided report creation =cut -my $input = new CGI; +my $input = CGI->new; my $usecache = Koha::Caches->get_instance->memcached_cache; my $phase = $input->param('phase') // ''; my $flagsrequired; -if ( ( $phase eq 'Build new' ) || ( $phase eq 'Create report from SQL' ) || ( $phase eq 'Edit SQL' ) ){ +if ( ( $phase eq 'Build new' ) || ( $phase eq 'Create report from SQL' ) || ( $phase eq 'Edit SQL' ) + || ( $phase eq 'Build new from existing' ) ) { $flagsrequired = 'create_reports'; } elsif ( $phase eq 'Use saved' ) { @@ -73,9 +73,7 @@ my ( $template, $borrowernumber, $cookie ) = get_template_and_user( template_name => "reports/guided_reports_start.tt", query => $input, type => "intranet", - authnotrequired => 0, flagsrequired => { reports => $flagsrequired }, - debug => 1, } ); my $session = $cookie ? get_session($cookie->value) : undef; @@ -220,7 +218,6 @@ elsif ( $phase eq 'Update SQL'){ my $cache_expiry_units = $input->param('cache_expiry_units'); my $public = $input->param('public'); my $save_anyway = $input->param('save_anyway'); - my @errors; # if we have the units, then we came from creating a report from SQL and thus need to handle converting units @@ -240,12 +237,8 @@ elsif ( $phase eq 'Update SQL'){ create_non_existing_group_and_subgroup($input, $group, $subgroup); - if ($sql =~ /;?\W?(UPDATE|DELETE|DROP|INSERT|SHOW|CREATE)\W/i) { - push @errors, {sqlerr => $1}; - } - elsif ($sql !~ /^(SELECT)/i) { - push @errors, {queryerr => "No SELECT"}; - } + my ( $is_sql_valid, $validation_errors ) = Koha::Report->new({ savedsql => $sql })->is_sql_valid; + push(@errors, @$validation_errors) unless $is_sql_valid; if (@errors) { $template->param( @@ -601,12 +594,8 @@ elsif ( $phase eq 'Save Report' ) { create_non_existing_group_and_subgroup($input, $group, $subgroup); ## FIXME this is AFTER entering a name to save the report under - if ($sql =~ /;?\W?(UPDATE|DELETE|DROP|INSERT|SHOW|CREATE)\W/i) { - push @errors, {sqlerr => $1}; - } - elsif ($sql !~ /^(SELECT)/i) { - push @errors, {queryerr => "No SELECT"}; - } + my ( $is_sql_valid, $validation_errors ) = Koha::Report->new({ savedsql => $sql })->is_sql_valid; + push(@errors, @$validation_errors) unless $is_sql_valid; if (@errors) { $template->param( @@ -692,6 +681,7 @@ elsif ($phase eq 'Run this report'){ my $report_id = $input->param('reports'); my @sql_params = $input->multi_param('sql_params'); my @param_names = $input->multi_param('param_name'); + my $want_full_chart = $input->param('want_full_chart') || 0; # offset algorithm if ($input->param('page')) { @@ -719,11 +709,12 @@ elsif ($phase eq 'Run this report'){ my @authval_errors; my %uniq_params; for(my $i=0;$i<($#split/2);$i++) { - my ($text,$authorised_value) = split /\|/,$split[$i*2+1]; - my $sep = $authorised_value ? "|" : ""; - if( defined $uniq_params{$text.$sep.$authorised_value} ){ + my ($text,$authorised_value_all) = split /\|/,$split[$i*2+1]; + my $sep = $authorised_value_all ? "|" : ""; + if( defined $uniq_params{$text.$sep.$authorised_value_all} ){ next; - } else { $uniq_params{$text.$sep.$authorised_value} = "$i"; } + } else { $uniq_params{$text.$sep.$authorised_value_all} = "$i"; } + my ($authorised_value, $all) = split /:/, $authorised_value_all; my $input; my $labelid; if ( not defined $authorised_value ) { @@ -732,6 +723,9 @@ elsif ($phase eq 'Run this report'){ } elsif ( $authorised_value eq "date" ) { # require a date, provide a date picker $input = 'date'; + } elsif ( $authorised_value eq "list" ) { + # require a list, provide a textarea + $input = 'textarea'; } else { # defined $authorised_value, and not 'date' my $dbh=C4::Context->dbh; @@ -815,7 +809,7 @@ elsif ($phase eq 'Run this report'){ }; } - push @tmpl_parameters, {'entry' => $text, 'input' => $input, 'labelid' => $labelid, 'name' => $text.$sep.$authorised_value }; + push @tmpl_parameters, {'entry' => $text, 'input' => $input, 'labelid' => $labelid, 'name' => $text.$sep.$authorised_value_all, 'include_all' => $all }; } $template->param('sql' => $sql, 'name' => $name, @@ -825,10 +819,9 @@ elsif ($phase eq 'Run this report'){ 'reports' => $report_id, ); } else { - my ($sql,$header_types) = get_prepped_report( $sql, \@param_names, \@sql_params); + my ($sql,$header_types) = $report->prep_report( \@param_names, \@sql_params ); $template->param(header_types => $header_types); my ( $sth, $errors ) = execute_query( $sql, $offset, $limit, undef, $report_id ); - my ($sth2, $errors2) = execute_query($sql); my $total = nb_rows($sql) || 0; unless ($sth) { die "execute_query failed to return sth for report $report_id: $sql"; @@ -839,14 +832,17 @@ elsif ($phase eq 'Run this report'){ my @cells = map { +{ cell => $_ } } @$row; push @rows, { cells => \@cells }; } - while (my $row = $sth2->fetchrow_arrayref()) { - my @cells = map { +{ cell => $_ } } @$row; - push @allrows, { cells => \@cells }; + if( $want_full_chart ){ + my ($sth2, $errors2) = execute_query($sql); + while (my $row = $sth2->fetchrow_arrayref()) { + my @cells = map { +{ cell => $_ } } @$row; + push @allrows, { cells => \@cells }; + } } } 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"; + my $url = "/cgi-bin/koha/reports/guided_reports.pl?reports=$report_id&phase=Run%20this%20report&limit=$limit&want_full_chart=$want_full_chart"; if (@param_names) { $url = join('&param_name=', $url, map { URI::Escape::uri_escape_utf8($_) } @param_names); } @@ -888,7 +884,7 @@ elsif ($phase eq 'Export'){ my $reportname = $input->param('reportname'); my $reportfilename = $reportname ? "$reportname-reportresults.$format" : "reportresults.$format" ; - ($sql, undef) = get_prepped_report( $sql, \@param_names, \@sql_params ); + ($sql, undef) = $report->prep_report( \@param_names, \@sql_params ); my ($sth, $q_errors) = execute_query($sql); unless ($q_errors and @$q_errors) { my ( $type, $content ); @@ -897,10 +893,10 @@ elsif ($phase eq 'Export'){ $content .= join("\t", header_cell_values($sth)) . "\n"; $content = Encode::decode('UTF-8', $content); while (my $row = $sth->fetchrow_arrayref()) { - $content .= join("\t", @$row) . "\n"; + $content .= join("\t", map { $_ // '' } @$row) . "\n"; } } else { - my $delimiter = C4::Context->preference('delimiter') || ','; + my $delimiter = C4::Context->preference('CSVDelimiter') || ','; if ( $format eq 'csv' ) { $delimiter = "\t" if $delimiter eq 'tabulation'; $type = 'application/csv'; @@ -969,25 +965,35 @@ elsif ($phase eq 'Export'){ ); } -elsif ( $phase eq 'Create report from SQL' ) { +elsif ( $phase eq 'Create report from SQL' || $phase eq 'Create report from existing' ) { - my ($group, $subgroup); - # allow the user to paste in sql + my ($group, $subgroup, $sql, $reportname, $notes); if ( $input->param('sql') ) { - $group = $input->param('report_group'); - $subgroup = $input->param('report_subgroup'); - $template->param( - 'sql' => scalar $input->param('sql') // '', - 'reportname' => scalar $input->param('reportname') // '', - 'notes' => scalar $input->param('notes') // '', - ); + $group = $input->param('report_group'); + $subgroup = $input->param('report_subgroup'); + $sql = $input->param('sql') // ''; + $reportname = $input->param('reportname') // ''; + $notes = $input->param('notes') // ''; } + elsif ( my $report_id = $input->param('report_id') ) { + my $report = Koha::Reports->find($report_id); + $group = $report->report_group; + $subgroup = $report->report_subgroup; + $sql = $report->savedsql // ''; + $reportname = $report->report_name // ''; + $notes = $report->notes // ''; + } + $template->param( + sql => $sql, + reportname => $reportname, + notes => $notes, 'create' => 1, 'groups_with_subgroups' => groups_with_subgroups($group, $subgroup), 'public' => '0', 'cache_expiry' => 300, 'usecache' => $usecache, + ); } @@ -1074,34 +1080,3 @@ sub create_non_existing_group_and_subgroup { } } -# pass $sth and sql_params, get back an executable query -sub get_prepped_report { - my ($sql, $param_names, $sql_params ) = @_; - - # First we split out the placeholders - my @split = split /\[\[|\]\]/,$sql; - my $headers; - for(my $i=0;$i<$#split/2;$i++){ #The placeholders are always the odd elements of the array - my ($type,$name) = split /\|/,$split[$i*2+1]; # We split them on '|' - $headers->{$name} = $type; # Store as a lookup for the template - $split[$i*2+1] =~ s/(\||\?|\.|\*|\(|\)|\%)/\\$1/g; #Quote any special characters so we can replace the placeholders - $name = C4::Context->dbh->quote($name); - $sql =~ s/\[\[$split[$i*2+1]\]\]/$type AS $name/; # Remove placeholders from SQL - } - - my %lookup; - @lookup{@$param_names} = @$sql_params; - @split = split /<<|>>/,$sql; - my @tmpl_parameters; - for(my $i=0;$i<$#split/2;$i++) { - my $quoted = @$param_names ? $lookup{ $split[$i*2+1] } : @$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/; - } - return $sql,$headers; -}