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;
use C4::Koha;
use Koha::DateUtils;
use C4::Output;
-use XML::Simple;
-use XML::Dumper;
use C4::Debug;
use C4::Log;
$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)
}
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) = @_;
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)
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%";
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;
# 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;
use CGI qw ( -utf8 );
use Carp;
use Encode;
+use JSON qw( to_json );
BEGIN {
# find Koha's Perl modules
-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
--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:
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
my $subject = "";
my $separator = ',';
my $quote = '"';
+my $store_results = 0;
my $username = undef;
my $password = undef;
'username:s' => \$username,
'password:s' => \$password,
'method:s' => \$method,
+ 'store-results' => \$store_results,
) or pod2usage(2);
pod2usage( -verbose => 2 ) if ($man);
}
# 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";
$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') {
});
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' ) {
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,
}
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},
);
}
projects / koha.git / commitdiff