X-Git-Url: http://koha-dev.rot13.org:8081/gitweb/?a=blobdiff_plain;f=misc%2Fcronjobs%2Frunreport.pl;h=b3ce925350215cf3dad3d751a8802ab47d1230d1;hb=3e2b55f64ced64cab71065de8562b998209c3522;hp=4602fe73047be967128d930f2d18af3be10990e0;hpb=798d38e4c7de0c920070b5db2249030cc9cb742c;p=srvgit diff --git a/misc/cronjobs/runreport.pl b/misc/cronjobs/runreport.pl index 4602fe7304..b3ce925350 100755 --- a/misc/cronjobs/runreport.pl +++ b/misc/cronjobs/runreport.pl @@ -18,29 +18,25 @@ # You should have received a copy of the GNU General Public License # along with Koha; if not, see . -use strict; -use warnings; +use Modern::Perl; -use C4::Reports::Guided; # 0.12 +use Koha::Script -cron; +use C4::Reports::Guided qw( store_results execute_query ); +use Koha::Reports; use C4::Context; -use C4::Log; +use C4::Log qw( cronlogaction ); use Koha::Email; -use Koha::DateUtils; +use Koha::DateUtils qw( dt_from_string ); +use Koha::SMTP::Servers; -use Getopt::Long qw(:config auto_help auto_version); -use Pod::Usage; -use MIME::Lite; -use Text::CSV_XS; +use Getopt::Long qw( GetOptions ); +use Pod::Usage qw( pod2usage ); +use Text::CSV::Encoded; use CGI qw ( -utf8 ); -use Carp; -use Encode; - -BEGIN { - # find Koha's Perl modules - # test carefully before changing this - use FindBin; - eval { require "$FindBin::Bin/../kohalib.pl" }; -} +use Carp qw( carp ); +use Encode qw( decode ); +use JSON qw( to_json ); +use Try::Tiny qw( catch try ); =head1 NAME @@ -55,7 +51,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 +61,9 @@ 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 + --param=s parameters for the report + --store-results store the result of the report + --csv-header add column names as first line of csv output Arguments: @@ -118,6 +117,18 @@ E-mail address to send report from. Defaults to KohaAdminEmailAddress. Subject for the e-mail message. Defaults to "Koha Saved Report" +=item B<--param> + +Repeatable, should provide one param per param requested for the report. +Report params are not combined as on the staff side, so you may need to repeat +params. + +=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 @@ -159,14 +170,17 @@ Reports - Guided Reports my $help = 0; my $man = 0; my $verbose = 0; -my $email = 0; +my $send_email = 0; my $attachment = 0; my $format = "text"; my $to = ""; my $from = ""; my $subject = ""; +my @params = (); my $separator = ','; my $quote = '"'; +my $store_results = 0; +my $csv_header = 0; my $username = undef; my $password = undef; @@ -180,11 +194,14 @@ GetOptions( 'to=s' => \$to, 'from=s' => \$from, 'subject=s' => \$subject, - 'email' => \$email, + 'param=s' => \@params, + 'email' => \$send_email, 'a|attachment' => \$attachment, 'username:s' => \$username, 'password:s' => \$password, 'method:s' => \$method, + 'store-results' => \$store_results, + 'csv-header' => \$csv_header, ) or pod2usage(2); pod2usage( -verbose => 2 ) if ($man); @@ -203,8 +220,8 @@ if ($format eq 'tsv' || $format eq 'text') { $separator = "\t"; } -if ($to or $from or $email) { - $email = 1; +if ($to or $from or $send_email) { + $send_email = 1; $from or $from = C4::Context->preference('KohaAdminEmailAddress'); $to or $to = C4::Context->preference('KohaAdminEmailAddress'); } @@ -219,14 +236,14 @@ my $today = dt_from_string(); my $date = $today->ymd(); foreach my $report_id (@ARGV) { - my $report = get_saved_report($report_id); + my $report = Koha::Reports->find( $report_id ); unless ($report) { warn "ERROR: No saved report $report_id found"; next; } - my $sql = $report->{savedsql}; - my $report_name = $report->{report_name}; - my $type = $report->{type}; + my $sql = $report->savedsql; + my $report_name = $report->report_name; + my $type = $report->type; $verbose and print "SQL: $sql\n\n"; if ( $subject eq "" ) @@ -240,9 +257,12 @@ 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); - # execute_query(sql, , 0, 20, , ) + + # convert SQL parameters to placeholders + my $params_needed = ( $sql =~ s/(<<[^>]+>>)/\?/g ); + die("You supplied ". scalar @params . " parameter(s) and $params_needed are required by the report") if scalar @params != $params_needed; + + my ($sth) = execute_query( $sql, undef, undef, \@params, $report_id ); my $count = scalar($sth->rows); unless ($count) { print "NO OUTPUT: 0 results from execute_query\n"; @@ -251,53 +271,83 @@ 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') { - my $csv = Text::CSV_XS->new({ + my $csv = Text::CSV::Encoded->new({ + encoding_out => 'utf8', + binary => 1, quote_char => $quote, sep_char => $separator, }); + + if ( $csv_header ) { + my @fields = map { decode( 'utf8', $_ ) } @{ $sth->{NAME} }; + $csv->combine( @fields ); + $message .= $csv->string() . "\n"; + push @rows_to_store, [@fields] if $store_results; + } + 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; } + $message = Encode::decode_utf8($message); + } + if ( $store_results ) { + my $json = to_json( \@rows_to_store ); + C4::Reports::Guided::store_results( $report_id, $json ); } + if ($send_email) { + + my $email = Koha::Email->new( + { + to => $to, + from => $from, + subject => $subject, + } + ); - if ($email) { - my $args = { to => $to, from => $from, subject => $subject }; if ( $format eq 'html' ) { $message = "$message"; - $args->{contenttype} = 'text/html'; + $email->html_body($message); + } + else { + $email->text_body($message); } - my $email = Koha::Email->new(); - my %mail = $email->create_message_headers($args); - $mail{Data} = $message; - $mail{Auth} = { user => $username, pass => $password, method => $method } if $username; - - my $msg = MIME::Lite->new(%mail); - - $msg->attach( - Type => "text/$format", - Data => encode( 'utf8', $message ), - Filename => "report$report_id-$date.$format", - Disposition => 'attachment', + + $email->attach( + Encode::encode_utf8($message), + content_type => "text/$format", + name => "report$report_id-$date.$format", + disposition => 'attachment', ) if $attachment; - $msg->send(); - carp "Mail not sent" unless $msg->last_send_successful(); + my $smtp_server = Koha::SMTP::Servers->get_default; + $smtp_server->set( + { + user_name => $username, + password => $password, + } + ) + if $username; + + $email->transport( $smtp_server->transport ); + try { + $email->send_or_die; + } + catch { + carp "Mail not sent: $_"; + }; } else { print $message;