From cf9031711282f4be23e4a639797d0fd178940e1f Mon Sep 17 00:00:00 2001 From: Jonathan Druart Date: Wed, 2 Sep 2015 11:50:04 +0100 Subject: [PATCH] Bug 14435: Add the ability to store result's report At one time it was possible to store the results of a report into the saved_reports table. This allowed the librarians to compare different results, from the Koha interface. This patch is a proof of concept and is not very polished (understood: it cannot be pushed like that). Test plan: Execute the runreport.pl cronjob script with the new --store-results option. This will serialize into json the results and put it into the saved_reports table. On the "Saved report" list, the "Saved results" column is now populated with a date (note that you can have several date for a given report). If you click on this link, the data will be displayed in a simple table. Signed-off-by: Chris Cormack Signed-off-by: Katrin Fischer Signed-off-by: Kyle M Hall --- C4/Reports/Guided.pm | 85 ++++++++----------- .../modules/reports/guided_reports_start.tt | 16 +++- misc/cronjobs/runreport.pl | 32 ++++--- reports/guided_reports.pl | 21 +++-- 4 files changed, 79 insertions(+), 75 deletions(-) diff --git a/C4/Reports/Guided.pm b/C4/Reports/Guided.pm index 631f961bed..62fdd7cb79 100644 --- a/C4/Reports/Guided.pm +++ b/C4/Reports/Guided.pm @@ -20,6 +20,7 @@ package C4::Reports::Guided; use Modern::Perl; use CGI qw ( -utf8 ); use Carp; +use JSON qw( from_json ); use vars qw(@ISA @EXPORT @EXPORT_OK %EXPORT_TAGS); use C4::Context; @@ -27,8 +28,6 @@ use C4::Templates qw/themelanguage/; use C4::Koha; use Koha::DateUtils; use C4::Output; -use XML::Simple; -use XML::Dumper; use C4::Debug; use C4::Log; @@ -536,10 +535,6 @@ sub execute_query { $sth->execute(@$sql_params, $offset, $limit); 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) @@ -594,47 +589,29 @@ sub update_sql { } 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=""; - foreach my $key (keys %$row){ - $htmlrow .= "$row->{$key}"; - } - $htmlrow .= ""; - $row->{'row'} = $htmlrow; - } - $sth->finish; - $query = "SELECT * FROM saved_sql WHERE id = ?"; - $sth = $dbh->prepare($query); - $sth->execute($id); - $data = $sth->fetchrow_hashref(); - return ($perl,$data->{'report_name'},$data->{'notes'}); -} + my ( $id ) = @_; + my $dbh = C4::Context->dbh(); + my ( $report_name, $notes, $json, $date_run ) = $dbh->selectrow_array(q| + SELECT ss.report_name, ss.notes, sr.report, sr.date_run + FROM saved_sql ss + LEFT JOIN saved_reports sr ON sr.report_id = ss.id + WHERE sr.id = ? + |, undef, $id); + return { + report_name => $report_name, + notes => $notes, + results => from_json( $json ), + date_run => $date_run, + }; +} sub delete_report { my (@ids) = @_; @@ -652,10 +629,9 @@ sub delete_report { sub get_saved_reports_base_query { my $area_name_sql_snippet = get_area_name_sql_snippet; return <{date}) { $date = eval { output_pref( { dt => dt_from_string( $date ), dateonly => 1, dateformat => 'iso' }); }; - push @cond, "DATE(date_run) = ? OR - DATE(date_created) = ? OR - DATE(last_modified) = ? OR + push @cond, "DATE(last_modified) = ? OR DATE(last_run) = ?"; - push @args, $date, $date, $date, $date; + push @args, $date, $date, $date; } if (my $author = $filter->{author}) { $author = "%$author%"; @@ -871,6 +845,17 @@ sub get_sql { return $data->{'savedsql'}; } +sub get_results { + my ( $report_id ) = @_; + my $dbh = C4::Context->dbh; + warn $report_id; + return $dbh->selectall_arrayref(q| + SELECT id, report, date_run + FROM saved_reports + WHERE report_id = ? + |, { Slice => {} }, $report_id); +} + sub _get_column_defs { my ($cgi) = @_; my %columns; diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tt index 15703add62..d7b5d89c97 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tt +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tt @@ -360,7 +360,11 @@ canned reports and writing custom SQL reports.

No [% END %] [% IF (usecache) %] [% savedreport.cache_expiry %] [% END %] - [% IF ( savedreport.date_run ) %][% savedreport.date_run %][% END %] + + [% FOR result IN savedreport.results %] + [% result.date_run %] +
+ [% END %]
@@ -920,13 +924,17 @@ Sub report: