X-Git-Url: http://koha-dev.rot13.org:8081/gitweb/?a=blobdiff_plain;f=reports%2Fguided_reports.pl;h=77c19b3cb4626b86424288765a56a044ab6d814f;hb=9d6d641d1f8b77271800f43bc027b651f9aea52b;hp=a3009b2062b3fc7b4ca73d9122f086d11b5fe96c;hpb=fe986573dd6a6b10dc0456be2609ee29cb459ac8;p=srvgit diff --git a/reports/guided_reports.pl b/reports/guided_reports.pl index a3009b2062..77c19b3cb4 100755 --- a/reports/guided_reports.pl +++ b/reports/guided_reports.pl @@ -4,18 +4,18 @@ # # 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/; @@ -23,14 +23,21 @@ 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 GetFrameworksLoop/; +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( 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 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 qw( generate_ods ); =head1 NAME @@ -42,17 +49,22 @@ Script to control the guided report creation =cut -my $input = new CGI; -my $usecache = C4::Context->ismemcached; +my $input = CGI->new; +my $usecache = Koha::Caches->get_instance->memcached_cache; -my $phase = $input->param('phase'); +my $phase = $input->param('phase') // ''; my $flagsrequired; -if ( $phase eq 'Build new' or $phase eq 'Delete Saved' ) { +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' ) { $flagsrequired = 'execute_reports'; -} else { +} +elsif ( $phase eq 'Delete Saved' ) { + $flagsrequired = 'delete_reports'; +} +else { $flagsrequired = '*'; } @@ -61,24 +73,23 @@ 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; my $filter; -if ( $input->param("filter_set") ) { +if ( $input->param("filter_set") or $input->param('clear_filters') ) { $filter = {}; $filter->{$_} = $input->param("filter_$_") foreach qw/date author keyword group subgroup/; $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'); } +my $op = $input->param('op') || q||; my @errors = (); if ( !$phase ) { @@ -96,22 +107,55 @@ elsif ( $phase eq 'Build new' ) { ); } elsif ( $phase eq 'Use saved' ) { + if ( $op eq 'convert' ) { + my $report_id = $input->param('report_id'); + my $report = Koha::Reports->find($report_id); + if ($report) { + my $updated_sql = C4::Reports::Guided::convert_sql( $report->savedsql ); + C4::Reports::Guided::update_sql( + $report_id, + { + sql => $updated_sql, + name => $report->report_name, + group => $report->report_group, + subgroup => $report->report_subgroup, + notes => $report->notes, + public => $report->public, + cache_expiry => $report->cache_expiry, + } + ); + $template->param( report_converted => $report->report_name ); + } + } + # use a saved report # get list of reports and display them my $group = $input->param('group'); my $subgroup = $input->param('subgroup'); $filter->{group} = $group; $filter->{subgroup} = $subgroup; + my $reports = get_saved_reports($filter); + my $has_obsolete_reports; + for my $report ( @$reports ) { + $report->{results} = C4::Reports::Guided::get_results( $report->{id} ); + if ( $report->{savedsql} =~ m|biblioitems| and $report->{savedsql} =~ m|marcxml| ) { + $report->{seems_obsolete} = 1; + $has_obsolete_reports++; + } + } $template->param( - 'saved1' => 1, - 'savedreports' => get_saved_reports($filter), - 'usecache' => $usecache, - 'groups_with_subgroups'=> groups_with_subgroups($group, $subgroup), + 'manamsg' => $input->param('manamsg') || '', + 'saved1' => 1, + 'savedreports' => $reports, + 'usecache' => $usecache, + 'groups_with_subgroups' => groups_with_subgroups( $group, $subgroup ), + filters => $filter, + has_obsolete_reports => $has_obsolete_reports, ); } elsif ( $phase eq 'Delete Multiple') { - my @ids = $input->param('ids'); + my @ids = $input->multi_param('ids'); delete_report( @ids ); print $input->redirect("/cgi-bin/koha/reports/guided_reports.pl?phase=Use%20saved"); exit; @@ -129,31 +173,37 @@ elsif ( $phase eq 'Delete Saved') { elsif ( $phase eq 'Show SQL'){ my $id = $input->param('reports'); - my $report = get_saved_report($id); + my $report = Koha::Reports->find($id); $template->param( 'id' => $id, - 'reportname' => $report->{report_name}, - 'notes' => $report->{notes}, - 'sql' => $report->{savedsql}, - 'showsql' => 1, + 'reportname' => $report->report_name, + 'notes' => $report->notes, + 'sql' => $report->savedsql, + 'showsql' => 1, + 'mana_success' => $input->param('mana_success'), + 'mana_success' => scalar $input->param('mana_success'), + 'mana_id' => $report->{mana_id}, + 'mana_comments' => $report->{comments} ); } elsif ( $phase eq 'Edit SQL'){ my $id = $input->param('reports'); - my $report = get_saved_report($id); - my $group = $report->{report_group}; - my $subgroup = $report->{report_subgroup}; + my $report = Koha::Reports->find($id); + my $group = $report->report_group; + my $subgroup = $report->report_subgroup; $template->param( - 'sql' => $report->{savedsql}, - 'reportname' => $report->{report_name}, + 'sql' => $report->savedsql, + 'reportname' => $report->report_name, 'groups_with_subgroups' => groups_with_subgroups($group, $subgroup), - 'notes' => $report->{notes}, + 'notes' => $report->notes, 'id' => $id, - 'cache_expiry' => $report->{cache_expiry}, - 'public' => $report->{public}, + 'cache_expiry' => $report->cache_expiry, + 'public' => $report->public, 'usecache' => $usecache, 'editsql' => 1, + 'mana_id' => $report->{mana_id}, + 'mana_comments' => $report->{comments} ); } @@ -168,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 @@ -181,19 +230,15 @@ elsif ( $phase eq 'Update SQL'){ $cache_expiry *= 86400; # 60 * 60 * 24 } } - # check $cache_expiry isnt too large, Memcached::set requires it to be less than 30 days or it will be treated as if it were an absolute time stamp + # check $cache_expiry isn't too large, Memcached::set requires it to be less than 30 days or it will be treated as if it were an absolute time stamp if( $cache_expiry >= 2592000 ){ push @errors, {cache_expiry => $cache_expiry}; } 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 => 1}; - } + 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( @@ -230,12 +275,21 @@ elsif ( $phase eq 'Update SQL'){ subgroup => $subgroup, notes => $notes, public => $public, + cache_expiry => $cache_expiry, } ); $template->param( 'save_successful' => 1, 'reportname' => $reportname, 'id' => $id, + 'editsql' => 1, + 'sql' => $sql, + 'groups_with_subgroups' => groups_with_subgroups($group, $subgroup), + 'notes' => $notes, + 'cache_expiry' => $cache_expiry, + 'public' => $public, + 'usecache' => $usecache, ); + logaction( "REPORTS", "MODIFY", $id, "$reportname | $sql" ) if C4::Context->preference("ReportsLog"); } if ( $usecache ) { $template->param( @@ -247,14 +301,13 @@ elsif ( $phase eq 'Update SQL'){ } elsif ($phase eq 'retrieve results') { - my $id = $input->param('id'); - my ($results,$name,$notes) = format_results($id); - # do something - $template->param( - 'retresults' => 1, - 'results' => $results, - 'name' => $name, - 'notes' => $notes, + my $id = $input->param('id'); + my $result = format_results( $id ); + $template->param( + report_name => $result->{report_name}, + notes => $result->{notes}, + saved_results => $result->{results}, + date_run => $result->{date_run}, ); } @@ -270,7 +323,7 @@ elsif ( $phase eq 'Report on this Area' ) { } elsif( $cache_expiry_units eq "days" ){ $cache_expiry *= 86400; # 60 * 60 * 24 } - # check $cache_expiry isnt too large, Memcached::set requires it to be less than 30 days or it will be treated as if it were an absolute time stamp + # check $cache_expiry isn't too large, Memcached::set requires it to be less than 30 days or it will be treated as if it were an absolute time stamp if( $cache_expiry >= 2592000 ){ # oops, over the limit of 30 days # report error to user $template->param( @@ -279,16 +332,16 @@ elsif ( $phase eq 'Report on this Area' ) { 'areas' => get_report_areas(), 'cache_expiry' => $cache_expiry, 'usecache' => $usecache, - 'public' => $input->param('public'), + 'public' => scalar $input->param('public'), ); } else { - # they have choosen a new report and the area to report on + # they have chosen a new report and the area to report on $template->param( 'build2' => 1, - 'area' => $input->param('area'), + 'area' => scalar $input->param('area'), 'types' => get_report_types(), 'cache_expiry' => $cache_expiry, - 'public' => $input->param('public'), + 'public' => scalar $input->param('public'), ); } } @@ -303,8 +356,8 @@ elsif ( $phase eq 'Choose this type' ) { 'area' => $area, 'type' => $type, columns => get_columns($area,$input), - 'cache_expiry' => $input->param('cache_expiry'), - 'public' => $input->param('public'), + 'cache_expiry' => scalar $input->param('cache_expiry'), + 'public' => scalar $input->param('public'), ); } @@ -313,7 +366,7 @@ elsif ( $phase eq 'Choose these columns' ) { # next step is the constraints my $area = $input->param('area'); my $type = $input->param('type'); - my @columns = $input->param('columns'); + my @columns = $input->multi_param('columns'); my $column = join( ',', @columns ); $template->param( @@ -323,12 +376,12 @@ elsif ( $phase eq 'Choose these columns' ) { 'column' => $column, definitions => get_from_dictionary($area), criteria => get_criteria($area,$input), - 'public' => $input->param('public'), + 'public' => scalar $input->param('public'), ); if ( $usecache ) { $template->param( - cache_expiry => $input->param('cache_expiry'), - cache_expiry_units => $input->param('cache_expiry_units'), + cache_expiry => scalar $input->param('cache_expiry'), + cache_expiry_units => scalar $input->param('cache_expiry_units'), ); } @@ -338,9 +391,9 @@ elsif ( $phase eq 'Choose these criteria' ) { my $area = $input->param('area'); my $type = $input->param('type'); my $column = $input->param('column'); - my @definitions = $input->param('definition'); + my @definitions = $input->multi_param('definition'); my $definition = join (',',@definitions); - my @criteria = $input->param('criteria_column'); + my @criteria = $input->multi_param('criteria_column'); my $query_criteria; foreach my $crit (@criteria) { my $value = $input->param( $crit . "_value" ); @@ -352,9 +405,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) { @@ -364,8 +421,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 =~ /<<.*>>/) { @@ -382,12 +441,12 @@ elsif ( $phase eq 'Choose these criteria' ) { 'column' => $column, 'definition' => $definition, 'criteriastring' => $query_criteria, - 'public' => $input->param('public'), + 'public' => scalar $input->param('public'), ); if ( $usecache ) { $template->param( - cache_expiry => $input->param('cache_expiry'), - cache_expiry_units => $input->param('cache_expiry_units'), + cache_expiry => scalar $input->param('cache_expiry'), + cache_expiry_units => scalar $input->param('cache_expiry_units'), ); } @@ -414,7 +473,7 @@ elsif ( $phase eq 'Choose these operations' ) { my $column = $input->param('column'); my $criteria = $input->param('criteria'); my $definition = $input->param('definition'); - my @total_by = $input->param('total_by'); + my @total_by = $input->multi_param('total_by'); my $totals; foreach my $total (@total_by) { my $value = $input->param( $total . "_tvalue" ); @@ -429,8 +488,8 @@ elsif ( $phase eq 'Choose these operations' ) { 'criteriastring' => $criteria, 'totals' => $totals, 'definition' => $definition, - 'cache_expiry' => $input->param('cache_expiry'), - 'public' => $input->param('public'), + 'cache_expiry' => scalar $input->param('cache_expiry'), + 'public' => scalar $input->param('public'), ); # get columns @@ -461,7 +520,7 @@ elsif ( $phase eq 'Build report' ) { my $query_criteria=$crit; # split the columns up by , my @columns = split( ',', $column ); - my @order_by = $input->param('order_by'); + my @order_by = $input->multi_param('order_by'); my $query_orderby; foreach my $order (@order_by) { @@ -482,14 +541,14 @@ elsif ( $phase eq 'Build report' ) { 'area' => $area, 'sql' => $sql, 'type' => $type, - 'cache_expiry' => $input->param('cache_expiry'), - 'public' => $input->param('public'), + 'cache_expiry' => scalar $input->param('cache_expiry'), + 'public' => scalar $input->param('public'), ); } elsif ( $phase eq 'Save' ) { # Save the report that has just been built - my $area = $input->param('area'); + my $area = $input->param('area'); my $sql = $input->param('sql'); my $type = $input->param('type'); $template->param( @@ -497,8 +556,8 @@ elsif ( $phase eq 'Save' ) { 'area' => $area, 'sql' => $sql, 'type' => $type, - 'cache_expiry' => $input->param('cache_expiry'), - 'public' => $input->param('public'), + 'cache_expiry' => scalar $input->param('cache_expiry'), + 'public' => scalar $input->param('public'), 'groups_with_subgroups' => groups_with_subgroups($area), # in case we have a report group that matches area ); } @@ -528,20 +587,15 @@ elsif ( $phase eq 'Save Report' ) { $cache_expiry *= 86400; # 60 * 60 * 24 } } - # check $cache_expiry isnt too large, Memcached::set requires it to be less than 30 days or it will be treated as if it were an absolute time stamp + # check $cache_expiry isn't too large, Memcached::set requires it to be less than 30 days or it will be treated as if it were an absolute time stamp if( $cache_expiry && $cache_expiry >= 2592000 ){ push @errors, {cache_expiry => $cache_expiry}; } 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( @@ -593,21 +647,42 @@ 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, 'id' => $id, + 'editsql' => 1, + 'sql' => $sql, + 'groups_with_subgroups' => groups_with_subgroups($group, $subgroup), + 'notes' => $notes, + 'cache_expiry' => $cache_expiry, + 'public' => $public, + 'usecache' => $usecache, ); } } } +elsif ($phase eq 'Share'){ + my $lang = $input->param('mana_language') || ''; + my $reportid = $input->param('reportid'); + my $result = Koha::SharedContent::send_entity($lang, $borrowernumber, $reportid, 'report'); + if ( $result ) { + print $input->redirect("/cgi-bin/koha/reports/guided_reports.pl?phase=Use%20saved&manamsg=".$result->{msg}); + }else{ + print $input->redirect("/cgi-bin/koha/reports/guided_reports.pl?phase=Use%20saved&manamsg=noanswer"); + } +} elsif ($phase eq 'Run this report'){ # execute a saved report my $limit = $input->param('limit') || 20; my $offset = 0; my $report_id = $input->param('reports'); - my @sql_params = $input->param('sql_params'); + 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')) { $offset = ($input->param('page') - 1) * $limit; @@ -618,21 +693,28 @@ elsif ($phase eq 'Run this report'){ 'report_id' => $report_id, ); - my ( $sql, $type, $name, $notes ); - if (my $report = get_saved_report($report_id)) { - $sql = $report->{savedsql}; - $name = $report->{report_name}; - $notes = $report->{notes}; + my ( $sql, $original_sql, $type, $name, $notes ); + if (my $report = Koha::Reports->find($report_id)) { + $sql = $original_sql = $report->savedsql; + $name = $report->report_name; + $notes = $report->notes; my @rows = (); + my @allrows = (); # if we have at least 1 parameter, and it's not filled, then don't execute but ask for parameters if ($sql =~ /<>/,$sql; my @tmpl_parameters; my @authval_errors; + my %uniq_params; for(my $i=0;$i<($#split/2);$i++) { - my ($text,$authorised_value) = split /\|/,$split[$i*2+1]; + 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_all} = "$i"; } + my ($authorised_value, $all) = split /:/, $authorised_value_all; my $input; my $labelid; if ( not defined $authorised_value ) { @@ -641,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; @@ -648,10 +733,10 @@ elsif ($phase eq 'Run this report'){ my %authorised_lib; # builds list, depending on authorised value... if ( $authorised_value eq "branches" ) { - my $branches = GetBranchesLoop(); - foreach my $thisbranch (@$branches) { - push @authorised_values, $thisbranch->{value}; - $authorised_lib{$thisbranch->{value}} = $thisbranch->{branchname}; + my $libraries = Koha::Libraries->search( {}, { order_by => ['branchname'] } ); + while ( my $library = $libraries->next ) { + push @authorised_values, $library->branchcode; + $authorised_lib{$library->branchcode} = $library->branchname; } } elsif ( $authorised_value eq "itemtypes" ) { @@ -663,13 +748,13 @@ elsif ($phase eq 'Run this report'){ } } elsif ( $authorised_value eq "biblio_framework" ) { - my $frameworks = GetFrameworksLoop(); + my @frameworks = Koha::BiblioFrameworks->search({}, { order_by => ['frameworktext'] }); 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}; + foreach my $framework (@frameworks) { + push @authorised_values, $framework->frameworkcode; + $authorised_lib{$framework->frameworkcode} = $framework->frameworktext; } } elsif ( $authorised_value eq "cn_source" ) { @@ -683,17 +768,12 @@ elsif ($phase eq 'Run this report'){ } } elsif ( $authorised_value eq "categorycode" ) { - my $sth = $dbh->prepare("SELECT categorycode, description FROM categories ORDER BY description"); - $sth->execute; - while ( my ( $categorycode, $description ) = $sth->fetchrow_array ) { - push @authorised_values, $categorycode; - $authorised_lib{$categorycode} = $description; - } - - #---- "true" authorised value + my @patron_categories = Koha::Patron::Categories->search({}, { order_by => ['description']}); + %authorised_lib = map { $_->categorycode => $_->description } @patron_categories; + push @authorised_values, $_->categorycode for @patron_categories; } else { - if ( IsAuthorisedValueCategory($authorised_value) ) { + if ( Koha::AuthorisedValues->search({ category => $authorised_value })->count ) { my $query = ' SELECT authorised_value,lib FROM authorised_values @@ -721,20 +801,15 @@ 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 }; + push @tmpl_parameters, {'entry' => $text, 'input' => $input, 'labelid' => $labelid, 'name' => $text.$sep.$authorised_value_all, 'include_all' => $all }; } $template->param('sql' => $sql, 'name' => $name, @@ -744,46 +819,51 @@ elsif ($phase eq 'Run this report'){ 'reports' => $report_id, ); } else { - # OK, we have parameters, or there are none, we run the report - # if there were parameters, replace before running - # split on ??. Each odd (2,4,6,...) entry should be a parameter to fill - my @split = split /<<|>>/,$sql; - my @tmpl_parameters; - for(my $i=0;$i<$#split/2;$i++) { - my $quoted = C4::Context->dbh->quote($sql_params[$i]); - # if there are special regexp chars, we must \ them - $split[$i*2+1] =~ s/(\||\?|\.|\*|\(|\)|\%)/\\$1/g; - $sql =~ s/<<$split[$i*2+1]>>/$quoted/; - } - my ($sth, $errors) = execute_query($sql, $offset, $limit); + 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 $total = nb_rows($sql) || 0; 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; push @rows, { 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); + } if (@sql_params) { $url = join('&sql_params=', $url, map { URI::Escape::uri_escape_utf8($_) } @sql_params); } + $template->param( 'results' => \@rows, + 'allresults' => \@allrows, 'sql' => $sql, + original_sql => $original_sql, 'id' => $report_id, 'execute' => 1, 'name' => $name, 'notes' => $notes, 'errors' => defined($errors) ? [ $errors ] : undef, - 'pagination_bar' => pagination_bar($url, $totpages, $input->param('page')), + 'pagination_bar' => pagination_bar($url, $totpages, scalar $input->param('page')), 'unlimited_total' => $total, 'sql_params' => \@sql_params, + 'param_names' => \@param_names, ); } } @@ -795,25 +875,35 @@ elsif ($phase eq 'Run this report'){ elsif ($phase eq 'Export'){ # 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 $report_id = $input->param('report_id'); + my $report = Koha::Reports->find($report_id); + my $sql = $report->savedsql; + my @param_names = $input->multi_param('param_name'); + my @sql_params = $input->multi_param('sql_params'); + my $format = $input->param('format'); + my $reportname = $input->param('reportname'); + my $reportfilename = $reportname ? "$reportname-reportresults.$format" : "reportresults.$format" ; + + ($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 ); if ($format eq 'tab') { $type = 'application/octet-stream'; $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'; - my $csv = Text::CSV::Encoded->new({ encoding_out => 'utf8', sep_char => $delimiter}); + 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 .= $csv->string(). "\n"; + $content .= Encode::decode('UTF-8', $csv->string()) . "\n"; } else { push @$q_errors, { combine => 'HEADER ROW: ' . $csv->error_diag() } ; } @@ -829,39 +919,26 @@ elsif ($phase eq 'Export'){ $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 ); + my $ods_content; + + # First line is headers + my @headers = header_cell_values($sth); + push @$ods_content, \@headers; + + # Other line in Unicode + my $sql_rows = $sth->fetchall_arrayref(); + foreach my $sql_row ( @$sql_rows ) { + my @content_row; + foreach my $sql_cell ( @$sql_row ) { + push @content_row, Encode::encode( 'UTF8', $sql_cell ); } - $i++; + push @$ods_content, \@content_row; } - $doc->save(); + + # Process + generate_ods($ods_filepath, $ods_content); + + # Output binmode(STDOUT); open $ods_fh, '<', $ods_filepath; $content .= $_ while <$ods_fh>; @@ -870,7 +947,7 @@ elsif ($phase eq 'Export'){ } print $input->header( -type => $type, - -attachment=>"reportresults.$format" + -attachment=> $reportfilename ); print $content; @@ -888,58 +965,53 @@ 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' => $input->param('sql') // '', - 'reportname' => $input->param('reportname') // '', - 'notes' => $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, - ); -} - -elsif ($phase eq 'Create Compound Report'){ - $template->param( 'savedreports' => get_saved_reports(), - 'compound' => 1, - ); -} -elsif ($phase eq 'Save Compound'){ - my $master = $input->param('master'); - my $subreport = $input->param('subreport'); - my ($mastertables,$subtables) = create_compound($master,$subreport); - $template->param( 'save_compound' => 1, - master=>$mastertables, - subsql=>$subtables - ); + ); } # pass $sth, get back an array of names for the column headers sub header_cell_values { my $sth = shift or return (); + 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(), ); @@ -978,18 +1050,33 @@ sub groups_with_subgroups { sub create_non_existing_group_and_subgroup { my ($input, $group, $subgroup) = @_; - if (defined $group and $group ne '') { 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; + my $cache_key = "AuthorisedValues-REPORT_GROUP-0-".C4::Context->userenv->{"branch"}; + my $cache = Koha::Caches->get_instance(); + my $result = $cache->clear_from_cache($cache_key); } 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; + my $cache_key = "AuthorisedValues-REPORT_SUBGROUP-0-".C4::Context->userenv->{"branch"}; + my $cache = Koha::Caches->get_instance(); + my $result = $cache->clear_from_cache($cache_key); } } } } +