X-Git-Url: http://git.rot13.org/?a=blobdiff_plain;f=reports%2Fguided_reports.pl;h=cb85f39420d9aafc0c3e158ed7c6d58d9dff252d;hb=e7971380e8ff699b57d0c02b2406eca254dfd04a;hp=037029ce62ce79d5b90acd26132a91a3d52aa460;hpb=14be4400d84b28369d095b3b0bfa79c3396f44d4;p=koha.git diff --git a/reports/guided_reports.pl b/reports/guided_reports.pl index 037029ce62..cb85f39420 100755 --- a/reports/guided_reports.pl +++ b/reports/guided_reports.pl @@ -13,19 +13,21 @@ # 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., 59 Temple Place, -# Suite 330, Boston, MA 02111-1307 USA +# 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. use strict; -# use warnings; # FIXME +#use warnings; FIXME - Bug 2505 use CGI; use Text::CSV; +use URI::Escape; use C4::Reports::Guided; -use C4::Auth; +use C4::Auth qw/:DEFAULT get_session/; use C4::Output; use C4::Dates; use C4::Debug; +use C4::Branch; # XXX subfield_is_koha_internal_p =head1 NAME @@ -35,26 +37,46 @@ guided_reports.pl Script to control the guided report creation -=over2 - =cut my $input = new CGI; +my $phase = $input->param('phase'); +my $flagsrequired; +if ( $phase eq 'Build new' or $phase eq 'Delete Saved' ) { + $flagsrequired = 'create_reports'; +} +elsif ( $phase eq 'Use saved' ) { + $flagsrequired = 'execute_reports'; +} else { + $flagsrequired = '*'; +} + my ( $template, $borrowernumber, $cookie ) = get_template_and_user( { template_name => "reports/guided_reports_start.tmpl", query => $input, type => "intranet", authnotrequired => 0, - flagsrequired => { reports => 1 }, + flagsrequired => { reports => $flagsrequired }, debug => 1, } ); +my $session = $cookie ? get_session($cookie->value) : undef; + +my $filter; +if ( $input->param("filter_set") ) { + $filter = {}; + $filter->{$_} = $input->param("filter_$_") foreach qw/date author keyword/; + $session->param('report_filter', $filter) if $session; + $template->param( 'filter_set' => 1 ); +} +elsif ($session) { + $filter = $session->param('report_filter'); +} - my @errors = (); -my $phase = $input->param('phase'); +my @errors = (); if ( !$phase ) { $template->param( 'start' => 1 ); # show welcome page @@ -67,8 +89,15 @@ elsif ( $phase eq 'Build new' ) { elsif ( $phase eq 'Use saved' ) { # use a saved report # get list of reports and display them - $template->param( 'saved1' => 1 ); - $template->param( 'savedreports' => get_saved_reports() ); + $template->param( + 'saved1' => 1, + 'savedreports' => get_saved_reports($filter), + ); + if ($filter) { + while ( my ($k, $v) = each %$filter ) { + $template->param( "filter_$k" => $v ) if $v; + } + } } elsif ( $phase eq 'Delete Saved') { @@ -83,13 +112,58 @@ elsif ( $phase eq 'Delete Saved') { elsif ( $phase eq 'Show SQL'){ my $id = $input->param('reports'); - my $sql = get_sql($id); + my ($sql,$type,$reportname,$notes) = get_saved_report($id); $template->param( - 'sql' => $sql, + 'id' => $id, + 'reportname' => $reportname, + 'notes' => $notes, + 'sql' => $sql, 'showsql' => 1, ); } +elsif ( $phase eq 'Edit SQL'){ + + my $id = $input->param('reports'); + my ($sql,$type,$reportname,$notes) = get_saved_report($id); + $template->param( + 'sql' => $sql, + 'reportname' => $reportname, + 'notes' => $notes, + 'id' => $id, + 'editsql' => 1, + ); +} + +elsif ( $phase eq 'Update SQL'){ + my $id = $input->param('id'); + my $sql = $input->param('sql'); + my $reportname = $input->param('reportname'); + my $notes = $input->param('notes'); + my @errors; + if ($sql =~ /;?\W?(UPDATE|DELETE|DROP|INSERT|SHOW|CREATE)\W/i) { + push @errors, {sqlerr => $1}; + } + elsif ($sql !~ /^(SELECT)/i) { + push @errors, {queryerr => 1}; + } + if (@errors) { + $template->param( + 'errors' => \@errors, + 'sql' => $sql, + ); + } + else { + update_sql( $id, $sql, $reportname, $notes ); + $template->param( + 'save_successful' => 1, + 'reportname' => $reportname, + 'id' => $id, + ); + } + +} + elsif ($phase eq 'retrieve results') { my $id = $input->param('id'); my ($results,$name,$notes) = format_results($id); @@ -154,11 +228,36 @@ elsif ( $phase eq 'Choose these criteria' ) { my $query_criteria; foreach my $crit (@criteria) { my $value = $input->param( $crit . "_value" ); - ($value) or next; - if ($value =~ C4::Dates->regexp('syspref')) { - $value = C4::Dates->new($value)->output("iso"); + + # If value is not defined, then it may be range values + if (!defined $value) { + + my $fromvalue = $input->param( "from_" . $crit . "_value" ); + 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"); + } + + if ($fromvalue && $tovalue) { + $query_criteria .= " AND $crit >= '$fromvalue' AND $crit <= '$tovalue'"; + } + + } else { + + # If value is a date + if ($value =~ C4::Dates->regexp('syspref')) { + $value = C4::Dates->new($value)->output("iso"); + } + # don't escape runtime parameters, they'll be at runtime + if ($value =~ /<<.*>>/) { + $query_criteria .= " AND $crit=$value"; + } else { + $query_criteria .= " AND $crit='$value'"; } - $query_criteria .= " AND $crit='$value'"; + } } $template->param( @@ -234,8 +333,7 @@ elsif ( $phase eq 'Build Report' ) { my $column = $input->param('column'); my $crit = $input->param('criteria'); my $totals = $input->param('totals'); - my $definition = $input->param('definition'); -# my @criteria = split( ',', $crit ); + my $definition = $input->param('definition'); my $query_criteria=$crit; # split the columns up by , my @columns = split( ',', $column ); @@ -295,9 +393,11 @@ elsif ( $phase eq 'Save Report' ) { ); } else { - save_report( $sql, $name, $type, $notes ); + my $id = save_report( $borrowernumber, $sql, $name, $type, $notes ); $template->param( 'save_successful' => 1, + 'reportname' => $name, + 'id' => $id, ); } } @@ -307,6 +407,7 @@ elsif ($phase eq 'Run this report'){ my $limit = 20; # page size. # TODO: move to DB or syspref? my $offset = 0; my $report = $input->param('reports'); + my @sql_params = $input->param('sql_params'); # offset algorithm if ($input->param('page')) { $offset = ($input->param('page') - 1) * $limit; @@ -316,57 +417,166 @@ elsif ($phase eq 'Run this report'){ push @errors, {no_sql_for_id=>$report}; } my @rows = (); - my ($sth, $errors) = execute_query($sql, $offset, $limit); - my $total = select_2_select_count_value($sql) || 0; - unless ($sth) { - die "execute_query failed to return sth for report $report: $sql"; + # 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; + for(my $i=0;$i<($#split/2);$i++) { + my ($text,$authorised_value) = split /\|/,$split[$i*2+1]; + my $input; + if ($authorised_value eq "date") { + $input = 'date'; + } + elsif ($authorised_value) { + my $dbh=C4::Context->dbh; + my @authorised_values; + 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}; + } + } + elsif ( $authorised_value eq "itemtypes" ) { + my $sth = $dbh->prepare("SELECT itemtype,description FROM itemtypes ORDER BY description"); + $sth->execute; + while ( my ( $itemtype, $description ) = $sth->fetchrow_array ) { + push @authorised_values, $itemtype; + $authorised_lib{$itemtype} = $description; + } + } + elsif ( $authorised_value eq "cn_source" ) { + my $class_sources = GetClassSources(); + my $default_source = C4::Context->preference("DefaultClassificationSource"); + foreach my $class_source (sort keys %$class_sources) { + next unless $class_sources->{$class_source}->{'used'} or + ($class_source eq $default_source); + push @authorised_values, $class_source; + $authorised_lib{$class_source} = $class_sources->{$class_source}->{'description'}; + } + } + 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 + } + else { + my $authorised_values_sth = $dbh->prepare("SELECT authorised_value,lib FROM authorised_values WHERE category=? ORDER BY lib"); + + $authorised_values_sth->execute( $authorised_value); + + while ( my ( $value, $lib ) = $authorised_values_sth->fetchrow_array ) { + push @authorised_values, $value; + $authorised_lib{$value} = $lib; + # For item location, we show the code and the libelle + $authorised_lib{$value} = $lib; + } + } + $input =CGI::scrolling_list( # FIXME: factor out scrolling_list + -name => "sql_params", + -values => \@authorised_values, +# -default => $value, + -labels => \%authorised_lib, + -override => 1, + -size => 1, + -multiple => 0, + -tabindex => 1, + ); + + } else { + $input = ""; + } + push @tmpl_parameters, {'entry' => $text, 'input' => $input }; + } + $template->param('sql' => $sql, + 'name' => $name, + 'sql_params' => \@tmpl_parameters, + 'enter_params' => 1, + 'reports' => $report, + ); } else { - my $headref = $sth->{NAME} || []; - my @headers = map { +{ cell => $_ } } @$headref; - $template->param(header_row => \@headers); - while (my $row = $sth->fetchrow_arrayref()) { - my @cells = map { +{ cell => $_ } } @$row; - push @rows, { cells => \@cells }; + # 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 $total = nb_rows($sql) || 0; + unless ($sth) { + die "execute_query failed to return sth for report $report: $sql"; + } else { + my $headref = $sth->{NAME} || []; + my @headers = map { +{ cell => $_ } } @$headref; + $template->param(header_row => \@headers); + while (my $row = $sth->fetchrow_arrayref()) { + my @cells = map { +{ cell => $_ } } @$row; + push @rows, { cells => \@cells }; + } } - } - my $totpages = int($total/$limit) + (($total % $limit) > 0 ? 1 : 0); - my $url = "/cgi-bin/koha/reports/guided_reports.pl?reports=$report&phase=Run%20this%20report"; - $template->param( - 'results' => \@rows, - 'sql' => $sql, - 'execute' => 1, - 'name' => $name, - 'notes' => $notes, - 'errors' => $errors, - 'pagination_bar' => pagination_bar($url, $totpages, $input->param('page')), - 'unlimited_total' => $total, - ); -} + my $totpages = int($total/$limit) + (($total % $limit) > 0 ? 1 : 0); + my $url = "/cgi-bin/koha/reports/guided_reports.pl?reports=$report&phase=Run%20this%20report"; + if (@sql_params) { + $url = join('&sql_params=', $url, map { URI::Escape::uri_escape($_) } @sql_params); + } + $template->param( + 'results' => \@rows, + 'sql' => $sql, + 'id' => $report, + 'execute' => 1, + 'name' => $name, + 'notes' => $notes, + 'errors' => $errors, + 'pagination_bar' => pagination_bar($url, $totpages, $input->param('page')), + 'unlimited_total' => $total, + ); + } +} elsif ($phase eq 'Export'){ - binmode STDOUT, ':utf8'; + binmode STDOUT, ':encoding(UTF-8)'; - # export results to tab separated text + # 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 ($sth, $q_errors) = execute_query($sql); unless ($q_errors and @$q_errors) { print $input->header( -type => 'application/octet-stream', - -attachment=>'reportresults.csv' + -attachment=>"reportresults.$format" ); - 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"; + if ($format eq 'tab') { + print join("\t", header_cell_values($sth)), "\n"; + while (my $row = $sth->fetchrow_arrayref()) { + print join("\t", @$row), "\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 $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 => $csv->error_diag() } ; + push @$q_errors, { combine => 'HEADER ROW: ' . $csv->error_diag() } ; + } + while (my $row = $sth->fetchrow_arrayref()) { + if ($csv->combine(@$row)) { + print $csv->string(), "\n"; + } else { + push @$q_errors, { combine => $csv->error_diag() } ; + } } } foreach my $err (@$q_errors, @errors) { @@ -412,19 +622,19 @@ elsif ($phase eq 'Save Compound'){ } # pass $sth, get back an array of names for the column headers -sub header_cell_values ($) { +sub header_cell_values { my $sth = shift or return (); return @{$sth->{NAME}}; } # pass $sth, get back a TMPL_LOOP-able set of names for the column headers -sub header_cell_loop ($) { +sub header_cell_loop { my @headers = map { +{ cell => $_ } } header_cell_values (shift); return \@headers; } foreach (1..6) { - $template->param('build' . $_) and $template->param(buildx => $_) and last; + $template->{VARS}->{'build' . $_} and $template->{VARS}->{'buildx' . $_} and last; } $template->param( 'referer' => $input->referer(), 'DHTMLcalendar_dateformat' => C4::Dates->DHTMLcalendar(),