@@ -, +, @@
---
C4/Reports/Guided.pm | 87 +++++++++-------------
.../en/modules/reports/guided_reports_start.tt | 16 +++-
misc/cronjobs/runreport.pl | 33 +++++---
reports/guided_reports.pl | 21 +++---
4 files changed, 81 insertions(+), 76 deletions(-)
--- a/C4/Reports/Guided.pm
+++ a/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($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
use C4::Context;
@@ -27,11 +28,7 @@ use C4::Templates qw/themelanguage/;
use C4::Koha;
use Koha::DateUtils;
use C4::Output;
-use XML::Simple;
-use XML::Dumper;
use C4::Debug;
-# use Smart::Comments;
-# use Data::Dumper;
use C4::Log;
BEGIN {
@@ -537,10 +534,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)
@@ -595,47 +588,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) = @_;
@@ -653,10 +628,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%";
@@ -872,6 +844,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;
--- a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tt
+++ a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tt
@@ -343,7 +343,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 %]
|
@@ -925,13 +929,17 @@ Sub report: |