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; # 0.12
31 use Getopt::Long qw(:config auto_help auto_version);
34 use Text::CSV::Encoded;
38 use JSON qw( to_json );
41 # find Koha's Perl modules
42 # test carefully before changing this
44 eval { require "$FindBin::Bin/../kohalib.pl" };
49 runreport.pl - Run pre-existing saved reports
53 runreport.pl [ -h | -m ] [ -v ] reportID [ reportID ... ]
56 -h --help brief help message
57 -m --man full documentation, same as --help --verbose
58 -v --verbose verbose output
60 --format=s selects format. Choice of text, html, csv or tsv
62 -e --email whether to use e-mail (implied by --to or --from)
63 -a --attachment additionally attach the report as a file. cannot be used with html format
64 --username username to pass to the SMTP server for authentication
65 --password password to pass to the SMTP server for authentication
66 --method method is the type of authentication. Ie. LOGIN, DIGEST-MD5, etc.
67 --to=s e-mail address to send report to
68 --from=s e-mail address to send report from
69 --subject=s subject for the e-mail
70 --store-results store the result of the report
71 --csv-header add column names as first line of csv output
75 reportID report ID Number from saved_sql.id, multiple ID's may be specified
83 Print a brief help message and exits.
87 Prints the manual page and exits.
91 Verbose. Without this flag set, only fatal errors are reported.
95 Current options are text, html, csv, and tsv. At the moment, text and tsv both produce tab-separated tab-separated output.
99 Whether to use e-mail (implied by --to or --from).
103 Username to pass to the SMTP server for authentication
107 Password to pass to the SMTP server for authentication
111 Method is the type of authentication. Ie. LOGIN, DIGEST-MD5, etc.
115 E-mail address to send report to. Defaults to KohaAdminEmailAddress.
119 E-mail address to send report from. Defaults to KohaAdminEmailAddress.
123 Subject for the e-mail message. Defaults to "Koha Saved Report"
125 =item B<--store-results>
127 Store the result of the report into the saved_reports DB table.
129 To access the results, go on Reports > Guided reports > Saved report.
135 This script is designed to run existing Saved Reports.
137 =head1 USAGE EXAMPLES
141 In the most basic form, runs the report specified by ID number from
142 saved_sql.id, in this case #16, outputting the results to STDOUT.
144 B<runreport.pl 16 17>
146 Same as above, but also runs report #17.
155 Allow Saved Results option.
162 Reports - Guided Reports
166 # These variables can be set by command line options,
167 # initially set to default values.
180 my $store_results = 0;
183 my $username = undef;
184 my $password = undef;
185 my $method = 'LOGIN';
190 'verbose' => \$verbose,
191 'format=s' => \$format,
194 'subject=s' => \$subject,
196 'a|attachment' => \$attachment,
197 'username:s' => \$username,
198 'password:s' => \$password,
199 'method:s' => \$method,
200 'store-results' => \$store_results,
201 'csv-header' => \$csv_header,
204 pod2usage( -verbose => 2 ) if ($man);
205 pod2usage( -verbose => 2 ) if ($help and $verbose);
206 pod2usage(1) if $help;
211 $verbose and print STDERR "No format specified, assuming 'text'\n";
215 if ($format eq 'tsv' || $format eq 'text') {
220 if ($to or $from or $email) {
222 $from or $from = C4::Context->preference('KohaAdminEmailAddress');
223 $to or $to = C4::Context->preference('KohaAdminEmailAddress');
226 unless (scalar(@ARGV)) {
227 print STDERR "ERROR: No reportID(s) specified\n";
230 ($verbose) and print scalar(@ARGV), " argument(s) after options: " . join(" ", @ARGV) . "\n";
232 my $today = dt_from_string();
233 my $date = $today->ymd();
235 foreach my $report_id (@ARGV) {
236 my $report = Koha::Reports->find( $report_id );
238 warn "ERROR: No saved report $report_id found";
241 my $sql = $report->savedsql;
242 my $report_name = $report->report_name;
243 my $type = $report->type;
245 $verbose and print "SQL: $sql\n\n";
246 if ( $subject eq "" )
248 if ( defined($report_name) and $report_name ne "")
250 $subject = $report_name ;
254 $subject = 'Koha Saved Report';
257 my ($sth) = execute_query( $sql, undef, undef, undef, $report_id );
258 my $count = scalar($sth->rows);
260 print "NO OUTPUT: 0 results from execute_query\n";
263 $verbose and print "$count results from execute_query\n";
267 if ($format eq 'html') {
268 my $cgi = CGI->new();
270 while (my $line = $sth->fetchrow_arrayref) {
271 foreach (@$line) { defined($_) or $_ = ''; } # catch undef values, replace w/ ''
272 push @rows, $cgi->TR( join('', $cgi->td($line)) ) . "\n";
273 push @rows_to_store, [@$line] if $store_results;
275 $message = $cgi->table(join "", @rows);
276 } elsif ($format eq 'csv') {
277 my $csv = Text::CSV::Encoded->new({
278 encoding_out => 'utf8',
280 quote_char => $quote,
281 sep_char => $separator,
285 my @fields = map { decode( 'utf8', $_ ) } @{ $sth->{NAME} };
286 $csv->combine( @fields );
287 $message .= $csv->string() . "\n";
288 push @rows_to_store, [@fields] if $store_results;
291 while (my $line = $sth->fetchrow_arrayref) {
292 $csv->combine(@$line);
293 $message .= $csv->string() . "\n";
294 push @rows_to_store, [@$line] if $store_results;
297 if ( $store_results ) {
298 my $json = to_json( \@rows_to_store );
299 C4::Reports::Guided::store_results( $report_id, $json );
302 my $args = { to => $to, from => $from, subject => $subject };
303 if ( $format eq 'html' ) {
304 $message = "<html><head><style>tr:nth-child(2n+1) { background-color: #ccc;}</style></head><body>$message</body></html>";
305 $args->{contenttype} = 'text/html';
307 my $email = Koha::Email->new();
308 my %mail = $email->create_message_headers($args);
309 $mail{Data} = $message;
310 $mail{Auth} = { user => $username, pass => $password, method => $method } if $username;
312 my $msg = MIME::Lite->new(%mail);
315 Type => "text/$format",
316 Data => encode( 'utf8', $message ),
317 Filename => "report$report_id-$date.$format",
318 Disposition => 'attachment',
322 carp "Mail not sent" unless $msg->last_send_successful();