From c2c3c110053803a486f89cee8cae2a9b24088f7f Mon Sep 17 00:00:00 2001 From: Kyle M Hall Date: Tue, 7 Feb 2017 16:01:12 +0000 Subject: [PATCH] Bug 14365 - Populate never used saved_sql column last_run when execute_query is called Signed-off-by: Cab Vinton Signed-off-by: Kyle M Hall Signed-off-by: Jonathan Druart Signed-off-by: Kyle M Hall --- C4/Reports/Guided.pm | 9 +++++++-- misc/cronjobs/runreport.pl | 3 +-- opac/svc/report | 2 +- reports/guided_reports.pl | 2 +- svc/report | 2 +- t/db_dependent/Reports/Guided.t | 30 +++++++++++++++++++++++++++++- 6 files changed, 40 insertions(+), 8 deletions(-) diff --git a/C4/Reports/Guided.pm b/C4/Reports/Guided.pm index 901edc4913..ac5633a121 100644 --- a/C4/Reports/Guided.pm +++ b/C4/Reports/Guided.pm @@ -496,7 +496,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; @@ -531,8 +531,13 @@ sub execute_query { } $sql .= " LIMIT ?, ?"; - my $sth = C4::Context->dbh->prepare($sql); + 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); $sth->execute(@$sql_params, $offset, $limit); + return ( $sth, { queryerr => $sth->errstr } ) if ($sth->err); return ( $sth ); } diff --git a/misc/cronjobs/runreport.pl b/misc/cronjobs/runreport.pl index 370c479749..1648f4d72a 100755 --- a/misc/cronjobs/runreport.pl +++ b/misc/cronjobs/runreport.pl @@ -249,8 +249,7 @@ foreach my $report_id (@ARGV) { $subject = 'Koha Saved Report'; } } - # my $results = execute_query($sql, undef, 0, 99999, $format, $report_id); - my ($sth) = execute_query($sql); + my ($sth) = execute_query( $sql, undef, undef, undef, $report_id ); my $count = scalar($sth->rows); unless ($count) { print "NO OUTPUT: 0 results from execute_query\n"; diff --git a/opac/svc/report b/opac/svc/report index d2f953dc9e..f83b12e70e 100755 --- a/opac/svc/report +++ b/opac/svc/report @@ -61,7 +61,7 @@ unless ($json_text) { $sql =~ s/(<<.*?>>)/\?/g; my ( $sth, $errors ) = - execute_query( $sql, $offset, $limit, \@sql_params ); + execute_query( $sql, $offset, $limit, \@sql_params, $report_id ); if ($sth) { my $lines; if ($report_annotation) { diff --git a/reports/guided_reports.pl b/reports/guided_reports.pl index a5e15be889..3e69e16f3b 100755 --- a/reports/guided_reports.pl +++ b/reports/guided_reports.pl @@ -772,7 +772,7 @@ elsif ($phase eq 'Run this report'){ $quoted = C4::Context->dbh->quote($quoted); $sql =~ s/<<$split[$i*2+1]>>/$quoted/; } - my ($sth, $errors) = execute_query($sql, $offset, $limit); + 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"; diff --git a/svc/report b/svc/report index 7f60ffbff0..0188eaadd7 100755 --- a/svc/report +++ b/svc/report @@ -65,7 +65,7 @@ unless ($json_text) { # convert SQL parameters to placeholders $sql =~ s/(<<.*?>>)/\?/g; - my ( $sth, $errors ) = execute_query( $sql, $offset, $limit, \@sql_params ); + my ( $sth, $errors ) = execute_query( $sql, $offset, $limit, \@sql_params, $report_id ); if ($sth) { my $lines; if ($report_annotation) { diff --git a/t/db_dependent/Reports/Guided.t b/t/db_dependent/Reports/Guided.t index c11d6c9180..78058db2c8 100644 --- a/t/db_dependent/Reports/Guided.t +++ b/t/db_dependent/Reports/Guided.t @@ -18,7 +18,7 @@ use Modern::Perl; -use Test::More tests => 7; +use Test::More tests => 8; use Test::Warn; use t::lib::TestBuilder; @@ -260,6 +260,34 @@ subtest 'get_saved_reports' => sub { "get_report_areas returns the correct array of report areas"); }; +subtest 'Ensure last_run is populated' => sub { + plan tests => 3; + + my $rs = Koha::Database->new()->schema()->resultset('SavedSql'); + + my $report = $rs->new( + { + report_name => 'Test Report', + savedsql => 'SELECT * FROM branches', + notes => undef, + } + )->insert(); + + is( $report->last_run, undef, 'Newly created report has null last_run ' ); + + execute_query( $report->savedsql, undef, undef, undef, $report->id ); + $report->discard_changes(); + + isnt( $report->last_run, undef, 'First run of report populates last_run' ); + + my $previous_last_run = $report->last_run; + sleep(1); # last_run is stored to the second, so we need to ensure at least one second has passed between runs + execute_query( $report->savedsql, undef, undef, undef, $report->id ); + $report->discard_changes(); + + isnt( $report->last_run, $previous_last_run, 'Second run of report updates last_run' ); +}; + $schema->storage->txn_rollback; sub trim { -- 2.39.5