# 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 Koha::Script -cron;
+use C4::Reports::Guided qw( store_results execute_query );
+use Koha::Reports;
use C4::Context;
+use C4::Log qw( cronlogaction );
use Koha::Email;
-use C4::Log;
+use Koha::DateUtils qw( dt_from_string );
+use Koha::SMTP::Servers;
-use Getopt::Long qw(:config auto_help auto_version);
-use Pod::Usage;
-use Mail::Sendmail;
-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;
-
-use vars qw($VERSION);
+use Carp qw( carp );
+use Encode qw( decode );
+use JSON qw( to_json );
+use Try::Tiny qw( catch try );
BEGIN {
# find Koha's Perl modules
# test carefully before changing this
- use FindBin;
+ use FindBin ();
eval { require "$FindBin::Bin/../kohalib.pl" };
- $VERSION = 0.22;
}
=head1 NAME
-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
--username username to pass to the SMTP server for authentication
--password password to pass to the SMTP server for authentication
--method method is the type of authentication. Ie. LOGIN, DIGEST-MD5, etc.
--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:
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
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;
'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);
$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');
}
}
($verbose) and print scalar(@ARGV), " argument(s) after options: " . join(" ", @ARGV) . "\n";
+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 "" )
$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";
$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 ($email){
- my $email = Koha::Email->new();
- my %mail;
- if ($format eq 'html') {
- $message = "<html><head><style>tr:nth-child(2n+1) { background-color: #ccc;}</style></head><body>$message</body></html>";
- %mail = $email->create_message_headers({
- to => $to,
- from => $from,
- contenttype => 'text/html',
- subject => encode('utf8', $subject ),
- message => encode('utf8', $message )
- }
- );
- } else {
- %mail = $email->create_message_headers ({
- to => $to,
- from => $from,
- subject => encode('utf8', $subject ),
- message => encode('utf8', $message )
- }
- );
+ if ($send_email) {
+
+ my $email = Koha::Email->new(
+ {
+ to => $to,
+ from => $from,
+ subject => $subject,
+ }
+ );
+
+ if ( $format eq 'html' ) {
+ $message = "<html><head><style>tr:nth-child(2n+1) { background-color: #ccc;}</style></head><body>$message</body></html>";
+ $email->html_body($message);
}
- $mail{'Auth'} = {user => $username, pass => $password, method => $method} if $username;
- sendmail(%mail) or carp 'mail not sent:' . $Mail::Sendmail::error;
- } else {
+ else {
+ $email->text_body($message);
+ }
+
+ $email->attach(
+ Encode::encode_utf8($message),
+ content_type => "text/$format",
+ name => "report$report_id-$date.$format",
+ disposition => 'attachment',
+ ) if $attachment;
+
+ 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;
}
- # 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);
}
projects / srvgit / blobdiff