Bugzilla – Attachment 51987 Details for
Bug 14435
Recover feature to store and access results of a report
Home
|
New
|
Browse
|
Search
|
[?]
|
Reports
|
Help
|
New Account
|
Log In
[x]
|
Forgot Password
Login:
[x]
[patch]
Bug 14435: Add the ability to store result's report
Bug-14435-Add-the-ability-to-store-results-report.patch (text/plain), 12.97 KB, created by
Chris Cormack
on 2016-06-03 11:03:11 UTC
(
hide
)
Description:
Bug 14435: Add the ability to store result's report
Filename:
MIME Type:
Creator:
Chris Cormack
Created:
2016-06-03 11:03:11 UTC
Size:
12.97 KB
patch
obsolete
>From 00f2ddf2c4ebb00422742ad099cdc4d5a18e3cc0 Mon Sep 17 00:00:00 2001 >From: Jonathan Druart <jonathan.druart@bugs.koha-community.org> >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 <chris@bigballofwax.co.nz> >--- > C4/Reports/Guided.pm | 87 +++++++++------------- > .../en/modules/reports/guided_reports_start.tt | 16 +++- > misc/cronjobs/runreport.pl | 32 +++++--- > reports/guided_reports.pl | 21 +++--- > 4 files changed, 79 insertions(+), 77 deletions(-) > >diff --git a/C4/Reports/Guided.pm b/C4/Reports/Guided.pm >index c55d54d..957a433 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,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; > > use Koha::AuthorisedValues; >@@ -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="<tr>"; >- foreach my $key (keys %$row){ >- $htmlrow .= "<td>$row->{$key}</td>"; >- } >- $htmlrow .= "</tr>"; >- $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 <<EOQ; >-SELECT s.*, r.report, r.date_run, $area_name_sql_snippet, av_g.lib AS groupname, av_sg.lib AS subgroupname, >+SELECT s.*, $area_name_sql_snippet, av_g.lib AS groupname, av_sg.lib AS subgroupname, > b.firstname AS borrowerfirstname, b.surname AS borrowersurname > FROM saved_sql s >-LEFT JOIN saved_reports r ON r.report_id = s.id > LEFT OUTER JOIN authorised_values av_g ON (av_g.category = 'REPORT_GROUP' AND av_g.authorised_value = s.report_group) > LEFT OUTER JOIN authorised_values av_sg ON (av_sg.category = 'REPORT_SUBGROUP' AND av_sg.lib_opac = s.report_group AND av_sg.authorised_value = s.report_subgroup) > LEFT OUTER JOIN borrowers b USING (borrowernumber) >@@ -676,11 +650,9 @@ sub get_saved_reports { > if ($filter) { > if (my $date = $filter->{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; >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 16752cc..af974e5 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 >@@ -357,7 +357,11 @@ canned reports and writing custom SQL reports.</p> > <td>No</td> > [% END %] > [% IF (usecache) %] <td>[% savedreport.cache_expiry %]</td> [% END %] >- <td>[% IF ( savedreport.date_run ) %]<a href="/cgi-bin/koha/reports/guided_reports.pl?phase=retrieve%20results&id=[% savedreport.id %]">[% savedreport.date_run %]</a>[% END %] >+ <td> >+ [% FOR result IN savedreport.results %] >+ <a href="/cgi-bin/koha/reports/guided_reports.pl?phase=retrieve%20results&id=[% result.id %]">[% result.date_run %]</a> >+ <br/> >+ [% END %] > </td> > <td> > <div class="dropup"> >@@ -915,13 +919,17 @@ Sub report:<select name="subreport"> > [% END %] > > >-[% IF ( retresults ) %] >+[% IF saved_results %] > <h1>Saved report results</h1> > <h2>[% name %]</h2> > <p>[% notes %]</p> > <table> >-[% FOREACH result IN results %] >-[% result.row %] >+[% FOREACH rows IN saved_results %] >+<tr> >+[% FOREACH col IN rows %] >+<td>[% col %]</td> >+[% END %] >+<tr> > [% END %] > </table> > [% END %] >diff --git a/misc/cronjobs/runreport.pl b/misc/cronjobs/runreport.pl >index 4602fe7..370c479 100755 >--- a/misc/cronjobs/runreport.pl >+++ b/misc/cronjobs/runreport.pl >@@ -18,8 +18,7 @@ > # You should have received a copy of the GNU General Public License > # along with Koha; if not, see <http://www.gnu.org/licenses>. > >-use strict; >-use warnings; >+use Modern::Perl; > > use C4::Reports::Guided; # 0.12 > use C4::Context; >@@ -34,6 +33,7 @@ use Text::CSV_XS; > use CGI qw ( -utf8 ); > use Carp; > use Encode; >+use JSON qw( to_json ); > > BEGIN { > # find Koha's Perl modules >@@ -55,7 +55,7 @@ runreport.pl [ -h | -m ] [ -v ] reportID [ reportID ... ] > -m --man full documentation, same as --help --verbose > -v --verbose verbose output > >- --format=s selects format. Choice of text, html, csv, or tsv >+ --format=s selects format. Choice of text, html, csv or tsv > > -e --email whether to use e-mail (implied by --to or --from) > -a --attachment additionally attach the report as a file. cannot be used with html format >@@ -65,6 +65,7 @@ runreport.pl [ -h | -m ] [ -v ] reportID [ reportID ... ] > --to=s e-mail address to send report to > --from=s e-mail address to send report from > --subject=s subject for the e-mail >+ --store-results store the result of the report > > > Arguments: >@@ -118,6 +119,12 @@ E-mail address to send report from. Defaults to KohaAdminEmailAddress. > > Subject for the e-mail message. Defaults to "Koha Saved Report" > >+=item B<--store-results> >+ >+Store the result of the report into the saved_reports DB table. >+ >+To access the results, go on Reports > Guided reports > Saved report. >+ > =back > > =head1 DESCRIPTION >@@ -167,6 +174,7 @@ my $from = ""; > my $subject = ""; > my $separator = ','; > my $quote = '"'; >+my $store_results = 0; > > my $username = undef; > my $password = undef; >@@ -185,6 +193,7 @@ GetOptions( > 'username:s' => \$username, > 'password:s' => \$password, > 'method:s' => \$method, >+ 'store-results' => \$store_results, > > ) or pod2usage(2); > pod2usage( -verbose => 2 ) if ($man); >@@ -242,7 +251,6 @@ foreach my $report_id (@ARGV) { > } > # my $results = execute_query($sql, undef, 0, 99999, $format, $report_id); > my ($sth) = execute_query($sql); >- # execute_query(sql, , 0, 20, , ) > my $count = scalar($sth->rows); > unless ($count) { > print "NO OUTPUT: 0 results from execute_query\n"; >@@ -251,12 +259,14 @@ foreach my $report_id (@ARGV) { > $verbose and print "$count results from execute_query\n"; > > my $message; >+ my @rows_to_store; > if ($format eq 'html') { > my $cgi = CGI->new(); >- my @rows = (); >+ my @rows; > while (my $line = $sth->fetchrow_arrayref) { > foreach (@$line) { defined($_) or $_ = ''; } # catch undef values, replace w/ '' > push @rows, $cgi->TR( join('', $cgi->td($line)) ) . "\n"; >+ push @rows_to_store, [@$line] if $store_results; > } > $message = $cgi->table(join "", @rows); > } elsif ($format eq 'csv') { >@@ -266,16 +276,14 @@ foreach my $report_id (@ARGV) { > }); > while (my $line = $sth->fetchrow_arrayref) { > $csv->combine(@$line); >-# foreach (@$line) { >-# defined($_) or $_ = ''; >-# $_ =~ s/$quote/\\$quote/g; >-# $_ = "$quote$_$quote"; >-# } # catch undef values, replace w/ '' >-# $message .= join ($separator, @$line) . "\n"; > $message .= $csv->string() . "\n"; >+ push @rows_to_store, [@$line] if $store_results; > } > } >- >+ if ( $store_results ) { >+ my $json = to_json( \@rows_to_store ); >+ C4::Reports::Guided::store_results( $report_id, $json ); >+ } > if ($email) { > my $args = { to => $to, from => $from, subject => $subject }; > if ( $format eq 'html' ) { >diff --git a/reports/guided_reports.pl b/reports/guided_reports.pl >index 75040f4..73ec9a4 100755 >--- a/reports/guided_reports.pl >+++ b/reports/guided_reports.pl >@@ -106,9 +106,13 @@ elsif ( $phase eq 'Build new' ) { > my $subgroup = $input->param('subgroup'); > $filter->{group} = $group; > $filter->{subgroup} = $subgroup; >+ my $reports = get_saved_reports($filter); >+ for my $report ( @$reports ) { >+ $report->{results} = C4::Reports::Guided::get_results( $report->{id} ); >+ } > $template->param( > 'saved1' => 1, >- 'savedreports' => get_saved_reports($filter), >+ 'savedreports' => $reports, > 'usecache' => $usecache, > 'groups_with_subgroups'=> groups_with_subgroups($group, $subgroup), > filters => $filter, >@@ -254,14 +258,13 @@ elsif ( $phase eq 'Update SQL'){ > } > > elsif ($phase eq 'retrieve results') { >- my $id = $input->param('id'); >- my ($results,$name,$notes) = format_results($id); >- # do something >- $template->param( >- 'retresults' => 1, >- 'results' => $results, >- 'name' => $name, >- 'notes' => $notes, >+ my $id = $input->param('id'); >+ my $result = format_results( $id ); >+ $template->param( >+ report_name => $result->{report_name}, >+ notes => $result->{notes}, >+ saved_results => $result->{results}, >+ date_run => $result->{date_run}, > ); > } > >-- >2.1.4
You cannot view the attachment while viewing its details because your browser does not support IFRAMEs.
View the attachment on a separate page
.
View Attachment As Diff
View Attachment As Raw
Actions:
View
|
Diff
|
Splinter Review
Attachments on
bug 14435
:
40488
|
40489
|
40753
|
40796
|
40797
|
40929
|
40930
|
42196
|
44767
|
44795
|
51987
|
55814