X-Git-Url: http://koha-dev.rot13.org:8081/gitweb/?a=blobdiff_plain;f=C4%2FReports%2FGuided.pm;h=6611393ca0566704acc8a9eafd5ac542638adf52;hb=9d6d641d1f8b77271800f43bc027b651f9aea52b;hp=631f961bed8b6d344715a3da409a2e23bd08800d;hpb=3bb1578354e75ae160830a410340c6fbb9faaf3a;p=srvgit diff --git a/C4/Reports/Guided.pm b/C4/Reports/Guided.pm index 631f961bed..6611393ca0 100644 --- a/C4/Reports/Guided.pm +++ b/C4/Reports/Guided.pm @@ -19,36 +19,42 @@ package C4::Reports::Guided; use Modern::Perl; use CGI qw ( -utf8 ); -use Carp; +use Carp qw( carp croak ); +use JSON qw( from_json ); -use vars qw(@ISA @EXPORT @EXPORT_OK %EXPORT_TAGS); use C4::Context; use C4::Templates qw/themelanguage/; -use C4::Koha; -use Koha::DateUtils; +use C4::Koha qw( GetAuthorisedValues ); +use Koha::DateUtils qw( dt_from_string output_pref ); +use Koha::Patrons; +use Koha::Reports; use C4::Output; -use XML::Simple; -use XML::Dumper; -use C4::Debug; -use C4::Log; +use C4::Log qw( logaction ); +use Koha::Notice::Templates; +use Koha::Logger; use Koha::AuthorisedValues; use Koha::Patron::Categories; +use Koha::SharedContent; +our (@ISA, @EXPORT_OK); BEGIN { require Exporter; @ISA = qw(Exporter); - @EXPORT = qw( + @EXPORT_OK = qw( get_report_types get_report_areas get_report_groups get_columns build_query get_criteria - save_report get_saved_reports execute_query get_saved_report create_compound run_compound + save_report get_saved_reports execute_query get_column_type get_distinct_values save_dictionary get_from_dictionary - delete_definition delete_report format_results get_sql + delete_definition delete_report store_results format_results get_sql get_results nb_rows update_sql + strip_limit + convert_sql GetReservedAuthorisedValues GetParametersFromSQL IsAuthorisedValueValid ValidateSQLParameters nb_rows update_sql + EmailReport ); } @@ -414,10 +420,48 @@ sub get_criteria { sub nb_rows { my $sql = shift or return; - my $sth = C4::Context->dbh->prepare($sql); - $sth->execute(); - my $rows = $sth->fetchall_arrayref(); - return scalar (@$rows); + + my $derived_name = 'xxx'; + # make sure the derived table name is not already used + while ( $sql =~ m/$derived_name/ ) { + $derived_name .= 'x'; + } + + + my $dbh = C4::Context->dbh; + my $sth; + my $n = 0; + + my $RaiseError = $dbh->{RaiseError}; + my $PrintError = $dbh->{PrintError}; + $dbh->{RaiseError} = 1; + $dbh->{PrintError} = 0; + eval { + $sth = $dbh->prepare(qq{ + SELECT COUNT(*) FROM + ( $sql ) $derived_name + }); + + $sth->execute(); + }; + $dbh->{RaiseError} = $RaiseError; + $dbh->{PrintError} = $PrintError; + if ($@) { # To catch "Duplicate column name" caused by the derived table, or any other syntax error + eval { + $sth = $dbh->prepare($sql); + $sth->execute; + }; + warn $@ if $@; + # Loop through the complete results, fetching 1,000 rows at a time. This + # lowers memory requirements but increases execution time. + while (my $rows = $sth->fetchall_arrayref(undef, 1000)) { + $n += @$rows; + } + return $n; + } + + my $results = $sth->fetch; + return $results ? $results->[0] : 0; } =head2 execute_query @@ -497,7 +541,7 @@ sub strip_limit { sub execute_query { - my ( $sql, $offset, $limit, $sql_params ) = @_; + my ( $sql, $offset, $limit, $sql_params, $report_id ) = @_; $sql_params = [] unless defined $sql_params; @@ -508,20 +552,33 @@ sub execute_query { } $offset = 0 unless $offset; $limit = 999999 unless $limit; - $debug and print STDERR "execute_query($sql, $offset, $limit)\n"; - if ($sql =~ /;?\W?(UPDATE|DELETE|DROP|INSERT|SHOW|CREATE)\W/i) { - return (undef, { sqlerr => $1} ); - } elsif ($sql !~ /^\s*SELECT\b\s*/i) { - return (undef, { queryerr => 'Missing SELECT'} ); + + Koha::Logger->get->debug("Report - execute_query($sql, $offset, $limit)"); + + my ( $is_sql_valid, $errors ) = Koha::Report->new({ savedsql => $sql })->is_sql_valid; + return (undef, @{$errors}[0]) unless $is_sql_valid; + + foreach my $sql_param ( @$sql_params ){ + if ( $sql_param =~ m/\n/ ){ + my @list = split /\n/, $sql_param; + my @quoted_list; + foreach my $item ( @list ){ + $item =~ s/\r//; + push @quoted_list, C4::Context->dbh->quote($item); + } + $sql_param = "(".join(",",@quoted_list).")"; + } } my ($useroffset, $userlimit); # Grab offset/limit from user supplied LIMIT and drop the LIMIT so we can control pagination ($sql, $useroffset, $userlimit) = strip_limit($sql); - $debug and warn sprintf "User has supplied (OFFSET,) LIMIT = %s, %s", - $useroffset, - (defined($userlimit ) ? $userlimit : 'UNDEF'); + + Koha::Logger->get->debug( + sprintf "User has supplied (OFFSET,) LIMIT = %s, %s", + $useroffset, ( defined($userlimit) ? $userlimit : 'UNDEF' ) ); + $offset += $useroffset; if (defined($userlimit)) { if ($offset + $limit > $userlimit ) { @@ -532,14 +589,18 @@ sub execute_query { } $sql .= " LIMIT ?, ?"; - my $sth = C4::Context->dbh->prepare($sql); - $sth->execute(@$sql_params, $offset, $limit); + my $dbh = C4::Context->dbh; + + $dbh->do( 'UPDATE saved_sql SET last_run = NOW() WHERE id = ?', undef, $report_id ) if $report_id; + + my $sth = $dbh->prepare($sql); + eval { + $sth->execute(@$sql_params, $offset, $limit); + }; + warn $@ if $@; + return ( $sth, { queryerr => $sth->errstr } ) if ($sth->err); return ( $sth ); - # my @xmlarray = ... ; - # my $url = "/cgi-bin/koha/reports/guided_reports.pl?phase=retrieve%20results&id=$id"; - # my $xml = XML::Dumper->new()->pl2xml( \@xmlarray ); - # store_results($id,$xml); } =head2 save_report($sql,$name,$type,$notes) @@ -559,17 +620,29 @@ sub save_report { my $area = $fields->{area}; my $group = $fields->{group}; my $subgroup = $fields->{subgroup}; - my $cache_expiry = $fields->{cache_expiry} || 300; + my $cache_expiry = $fields->{cache_expiry}; my $public = $fields->{public}; - my $dbh = C4::Context->dbh(); $sql =~ s/(\s*\;\s*)$//; # removes trailing whitespace and /;/ - my $query = "INSERT INTO saved_sql (borrowernumber,date_created,last_modified,savedsql,report_name,report_area,report_group,report_subgroup,type,notes,cache_expiry,public) VALUES (?,now(),now(),?,?,?,?,?,?,?,?,?)"; - $dbh->do($query, undef, $borrowernumber, $sql, $name, $area, $group, $subgroup, $type, $notes, $cache_expiry, $public); + my $now = dt_from_string; + my $report = Koha::Report->new( + { + borrowernumber => $borrowernumber, + date_created => $now, # Must be moved to Koha::Report->store + last_modified => $now, # Must be moved to Koha::Report->store + savedsql => $sql, + report_name => $name, + report_area => $area, + report_group => $group, + report_subgroup => $subgroup, + type => $type, + notes => $notes, + cache_expiry => $cache_expiry, + public => $public, + } + )->store; - my $id = $dbh->selectrow_array("SELECT max(id) FROM saved_sql WHERE borrowernumber=? AND report_name=?", undef, - $borrowernumber, $name); - return $id; + return $report->id; } sub update_sql { @@ -583,65 +656,55 @@ sub update_sql { my $cache_expiry = $fields->{cache_expiry}; my $public = $fields->{public}; + $sql =~ s/(\s*\;\s*)$//; # removes trailing whitespace and /;/ + my $report = Koha::Reports->find($id); + $report->last_modified(dt_from_string); + $report->savedsql($sql); + $report->report_name($name); + $report->notes($notes); + $report->report_group($group); + $report->report_subgroup($subgroup); + $report->cache_expiry($cache_expiry) if defined $cache_expiry; + $report->public($public); + $report->store(); if( $cache_expiry >= 2592000 ){ - die "Please specify a cache expiry less than 30 days\n"; + die "Please specify a cache expiry less than 30 days\n"; # That's a bit harsh } - my $dbh = C4::Context->dbh(); - $sql =~ s/(\s*\;\s*)$//; # removes trailing whitespace and /;/ - my $query = "UPDATE saved_sql SET savedsql = ?, last_modified = now(), report_name = ?, report_group = ?, report_subgroup = ?, notes = ?, cache_expiry = ?, public = ? WHERE id = ? "; - $dbh->do($query, undef, $sql, $name, $group, $subgroup, $notes, $cache_expiry, $public, $id ); + return $report; } sub store_results { - my ($id,$xml)=@_; - my $dbh = C4::Context->dbh(); - my $query = "SELECT * FROM saved_reports WHERE report_id=?"; - my $sth = $dbh->prepare($query); - $sth->execute($id); - if (my $data=$sth->fetchrow_hashref()){ - my $query2 = "UPDATE saved_reports SET report=?,date_run=now() WHERE report_id=?"; - my $sth2 = $dbh->prepare($query2); - $sth2->execute($xml,$id); - } - else { - my $query2 = "INSERT INTO saved_reports (report_id,report,date_run) VALUES (?,?,now())"; - my $sth2 = $dbh->prepare($query2); - $sth2->execute($id,$xml); - } + my ( $id, $json ) = @_; + my $dbh = C4::Context->dbh(); + $dbh->do(q| + INSERT INTO saved_reports ( report_id, report, date_run ) VALUES ( ?, ?, NOW() ); + |, undef, $id, $json ); } sub format_results { - my ($id) = @_; - my $dbh = C4::Context->dbh(); - my $query = "SELECT * FROM saved_reports WHERE report_id = ?"; - my $sth = $dbh->prepare($query); - $sth->execute($id); - my $data = $sth->fetchrow_hashref(); - my $dump = new XML::Dumper; - my $perl = $dump->xml2pl( $data->{'report'} ); - foreach my $row (@$perl) { - my $htmlrow="