Bug 14365 - Populate never used saved_sql column last_run when execute_query is called

Signed-off-by: Cab Vinton <director@plaistowlibrary.com>

Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com>

Signed-off-by: Jonathan Druart <jonathan.druart@bugs.koha-community.org>

Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com>

diff --git a/C4/Reports/Guided.pm b/C4/Reports/Guided.pm
index 901edc4..ac5633a 100644 (file)
--- 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 370c479..1648f4d 100755 (executable)
--- 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 d2f953d..f83b12e 100755 (executable)
--- 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 a5e15be..3e69e16 100755 (executable)
--- 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 7f60ffb..0188eaa 100755 (executable)
--- 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 c11d6c9..78058db 100644 (file)
--- 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 {