3 # Copyright 2008 Liblime
4 # Copyright 2014 Foundations Bible College, Inc.
6 # This file is part of Koha.
8 # Koha is free software; you can redistribute it and/or modify it
9 # under the terms of the GNU General Public License as published by
10 # the Free Software Foundation; either version 3 of the License, or
11 # (at your option) any later version.
13 # Koha is distributed in the hope that it will be useful, but
14 # WITHOUT ANY WARRANTY; without even the implied warranty of
15 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
16 # GNU General Public License for more details.
18 # You should have received a copy of the GNU General Public License
19 # along with Koha; if not, see <http://www.gnu.org/licenses>.
23 use Koha::Script -cron;
24 use C4::Reports::Guided qw( store_results execute_query );
27 use C4::Log qw( cronlogaction );
29 use Koha::DateUtils qw( dt_from_string );
30 use Koha::SMTP::Servers;
32 use Getopt::Long qw( GetOptions );
33 use Pod::Usage qw( pod2usage );
34 use Text::CSV::Encoded;
37 use Encode qw( decode );
38 use JSON qw( to_json );
39 use Try::Tiny qw( catch try );
43 runreport.pl - Run pre-existing saved reports
47 runreport.pl [ -h | -m ] [ -v ] reportID [ reportID ... ]
50 -h --help brief help message
51 -m --man full documentation, same as --help --verbose
52 -v --verbose verbose output
54 --format=s selects format. Choice of text, html, csv or tsv
56 -e --email whether to use e-mail (implied by --to or --from)
57 -a --attachment additionally attach the report as a file. cannot be used with html format
58 --username username to pass to the SMTP server for authentication
59 --password password to pass to the SMTP server for authentication
60 --method method is the type of authentication. Ie. LOGIN, DIGEST-MD5, etc.
61 --to=s e-mail address to send report to
62 --from=s e-mail address to send report from
63 --subject=s subject for the e-mail
64 --param=s parameters for the report
65 --store-results store the result of the report
66 --csv-header add column names as first line of csv output
70 reportID report ID Number from saved_sql.id, multiple ID's may be specified
78 Print a brief help message and exits.
82 Prints the manual page and exits.
86 Verbose. Without this flag set, only fatal errors are reported.
90 Current options are text, html, csv, and tsv. At the moment, text and tsv both produce tab-separated output.
94 Separator character, only for csv format. Default to comma.
98 Whether to use e-mail (implied by --to or --from).
102 Username to pass to the SMTP server for authentication
106 Password to pass to the SMTP server for authentication
110 Method is the type of authentication. Ie. LOGIN, DIGEST-MD5, etc.
114 E-mail address to send report to. Defaults to KohaAdminEmailAddress.
118 E-mail address to send report from. Defaults to KohaAdminEmailAddress.
122 Subject for the e-mail message. Defaults to "Koha Saved Report"
126 Repeatable, should provide one param per param requested for the report.
127 Report params are not combined as on the staff side, so you may need to repeat
130 =item B<--store-results>
132 Store the result of the report into the saved_reports DB table.
134 To access the results, go on Reports > Guided reports > Saved report.
140 This script is designed to run existing Saved Reports.
142 =head1 USAGE EXAMPLES
146 In the most basic form, runs the report specified by ID number from
147 saved_sql.id, in this case #16, outputting the results to STDOUT.
149 B<runreport.pl 16 17>
151 Same as above, but also runs report #17.
160 Allow Saved Results option.
167 Reports - Guided Reports
171 binmode STDOUT, ":encoding(UTF-8)";
173 # These variables can be set by command line options,
174 # initially set to default values.
188 my $store_results = 0;
190 my $csv_separator = "";
192 my $username = undef;
193 my $password = undef;
194 my $method = 'LOGIN';
196 my $command_line_options = join(" ",@ARGV);
201 'verbose' => \$verbose,
202 'format=s' => \$format,
203 'separator=s' => \$csv_separator,
206 'subject=s' => \$subject,
207 'param=s' => \@params,
208 'email' => \$send_email,
209 'a|attachment' => \$attachment,
210 'username:s' => \$username,
211 'password:s' => \$password,
212 'method:s' => \$method,
213 'store-results' => \$store_results,
214 'csv-header' => \$csv_header,
217 pod2usage( -verbose => 2 ) if ($man);
218 pod2usage( -verbose => 2 ) if ($help and $verbose);
219 pod2usage(1) if $help;
221 cronlogaction({ info => $command_line_options });
224 $verbose and print STDERR "No format specified, assuming 'text'\n";
228 if ($csv_separator) {
229 if ( $format eq 'csv' ) {
230 $separator = "$csv_separator";
232 print STDERR "Cannot specify separator if not using CSV format\n";
236 if ($format eq 'tsv' || $format eq 'text') {
241 if ($to or $from or $send_email) {
243 $from or $from = C4::Context->preference('KohaAdminEmailAddress');
244 $to or $to = C4::Context->preference('KohaAdminEmailAddress');
247 unless (scalar(@ARGV)) {
248 print STDERR "ERROR: No reportID(s) specified\n";
251 ($verbose) and print scalar(@ARGV), " argument(s) after options: " . join(" ", @ARGV) . "\n";
253 my $today = dt_from_string();
254 my $date = $today->ymd();
256 foreach my $report_id (@ARGV) {
257 my $report = Koha::Reports->find( $report_id );
259 warn "ERROR: No saved report $report_id found";
262 my $sql = $report->savedsql;
263 my $report_name = $report->report_name;
264 my $type = $report->type;
266 $verbose and print "SQL: $sql\n\n";
267 if ( $subject eq "" )
269 if ( defined($report_name) and $report_name ne "")
271 $subject = $report_name ;
275 $subject = 'Koha Saved Report';
279 # convert SQL parameters to placeholders
280 my $params_needed = ( $sql =~ s/(<<[^>]+>>)/\?/g );
281 die("You supplied ". scalar @params . " parameter(s) and $params_needed are required by the report") if scalar @params != $params_needed;
283 my ($sth) = execute_query(
286 sql_params => \@params,
287 report_id => $report_id,
290 my $count = scalar($sth->rows);
292 print "NO OUTPUT: 0 results from execute_query\n";
295 $verbose and print "$count results from execute_query\n";
299 if ($format eq 'html') {
300 my $cgi = CGI->new();
302 while (my $line = $sth->fetchrow_arrayref) {
303 foreach (@$line) { defined($_) or $_ = ''; } # catch undef values, replace w/ ''
304 push @rows, $cgi->TR( join('', $cgi->td($line)) ) . "\n";
305 push @rows_to_store, [@$line] if $store_results;
307 $message = $cgi->table(join "", @rows);
308 } elsif ($format eq 'csv') {
309 my $csv = Text::CSV::Encoded->new({
310 encoding_out => 'utf8',
312 quote_char => $quote,
313 sep_char => $separator,
317 my @fields = map { decode( 'utf8', $_ ) } @{ $sth->{NAME} };
318 $csv->combine( @fields );
319 $message .= $csv->string() . "\n";
320 push @rows_to_store, [@fields] if $store_results;
323 while (my $line = $sth->fetchrow_arrayref) {
324 $csv->combine(@$line);
325 $message .= $csv->string() . "\n";
326 push @rows_to_store, [@$line] if $store_results;
328 $message = Encode::decode_utf8($message);
330 if ( $store_results ) {
331 my $json = to_json( \@rows_to_store );
332 C4::Reports::Guided::store_results( $report_id, $json );
336 my $email = Koha::Email->new(
344 if ( $format eq 'html' ) {
345 $message = "<html><head><style>tr:nth-child(2n+1) { background-color: #ccc;}</style></head><body>$message</body></html>";
346 $email->html_body($message);
349 $email->text_body($message);
353 Encode::encode_utf8($message),
354 content_type => "text/$format",
355 name => "report$report_id-$date.$format",
356 disposition => 'attachment',
359 my $smtp_server = Koha::SMTP::Servers->get_default;
362 user_name => $username,
363 password => $password,
368 $email->transport( $smtp_server->transport );
373 carp "Mail not sent: $_";
381 cronlogaction({ action => 'End', info => "COMPLETED" });