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 C4
::Reports
::Guided
; # 0.12
30 use Getopt
::Long
qw(:config auto_help auto_version);
33 use Text
::CSV
::Encoded
;
37 use JSON
qw( to_json );
40 # find Koha's Perl modules
41 # test carefully before changing this
43 eval { require "$FindBin::Bin/../kohalib.pl" };
48 runreport.pl - Run pre-existing saved reports
52 runreport.pl [ -h | -m ] [ -v ] reportID [ reportID ... ]
55 -h --help brief help message
56 -m --man full documentation, same as --help --verbose
57 -v --verbose verbose output
59 --format=s selects format. Choice of text, html, csv or tsv
61 -e --email whether to use e-mail (implied by --to or --from)
62 -a --attachment additionally attach the report as a file. cannot be used with html format
63 --username username to pass to the SMTP server for authentication
64 --password password to pass to the SMTP server for authentication
65 --method method is the type of authentication. Ie. LOGIN, DIGEST-MD5, etc.
66 --to=s e-mail address to send report to
67 --from=s e-mail address to send report from
68 --subject=s subject for the e-mail
69 --store-results store the result of the report
70 --csv-header add column names as first line of csv output
74 reportID report ID Number from saved_sql.id, multiple ID's may be specified
82 Print a brief help message and exits.
86 Prints the manual page and exits.
90 Verbose. Without this flag set, only fatal errors are reported.
94 Current options are text, html, csv, and tsv. At the moment, text and tsv both produce tab-separated tab-separated output.
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"
124 =item B<--store-results>
126 Store the result of the report into the saved_reports DB table.
128 To access the results, go on Reports > Guided reports > Saved report.
134 This script is designed to run existing Saved Reports.
136 =head1 USAGE EXAMPLES
140 In the most basic form, runs the report specified by ID number from
141 saved_sql.id, in this case #16, outputting the results to STDOUT.
143 B<runreport.pl 16 17>
145 Same as above, but also runs report #17.
154 Allow Saved Results option.
161 Reports - Guided Reports
165 # These variables can be set by command line options,
166 # initially set to default values.
179 my $store_results = 0;
182 my $username = undef;
183 my $password = undef;
184 my $method = 'LOGIN';
189 'verbose' => \
$verbose,
190 'format=s' => \
$format,
193 'subject=s' => \
$subject,
195 'a|attachment' => \
$attachment,
196 'username:s' => \
$username,
197 'password:s' => \
$password,
198 'method:s' => \
$method,
199 'store-results' => \
$store_results,
200 'csv-header' => \
$csv_header,
203 pod2usage
( -verbose
=> 2 ) if ($man);
204 pod2usage
( -verbose
=> 2 ) if ($help and $verbose);
205 pod2usage
(1) if $help;
210 $verbose and print STDERR
"No format specified, assuming 'text'\n";
214 if ($format eq 'tsv' || $format eq 'text') {
219 if ($to or $from or $email) {
221 $from or $from = C4
::Context
->preference('KohaAdminEmailAddress');
222 $to or $to = C4
::Context
->preference('KohaAdminEmailAddress');
225 unless (scalar(@ARGV)) {
226 print STDERR
"ERROR: No reportID(s) specified\n";
229 ($verbose) and print scalar(@ARGV), " argument(s) after options: " . join(" ", @ARGV) . "\n";
231 my $today = dt_from_string
();
232 my $date = $today->ymd();
234 foreach my $report_id (@ARGV) {
235 my $report = Koha
::Reports
->find( $report_id );
237 warn "ERROR: No saved report $report_id found";
240 my $sql = $report->savedsql;
241 my $report_name = $report->report_name;
242 my $type = $report->type;
244 $verbose and print "SQL: $sql\n\n";
245 if ( $subject eq "" )
247 if ( defined($report_name) and $report_name ne "")
249 $subject = $report_name ;
253 $subject = 'Koha Saved Report';
256 my ($sth) = execute_query
( $sql, undef, undef, undef, $report_id );
257 my $count = scalar($sth->rows);
259 print "NO OUTPUT: 0 results from execute_query\n";
262 $verbose and print "$count results from execute_query\n";
266 if ($format eq 'html') {
267 my $cgi = CGI
->new();
269 while (my $line = $sth->fetchrow_arrayref) {
270 foreach (@
$line) { defined($_) or $_ = ''; } # catch undef values, replace w/ ''
271 push @rows, $cgi->TR( join('', $cgi->td($line)) ) . "\n";
272 push @rows_to_store, [@
$line] if $store_results;
274 $message = $cgi->table(join "", @rows);
275 } elsif ($format eq 'csv') {
276 my $csv = Text
::CSV
::Encoded
->new({
277 encoding_out
=> 'utf8',
279 quote_char
=> $quote,
280 sep_char
=> $separator,
284 my @fields = map { decode
( 'utf8', $_ ) } @
{ $sth->{NAME
} };
285 $csv->combine( @fields );
286 $message .= $csv->string() . "\n";
287 push @rows_to_store, [@fields] if $store_results;
290 while (my $line = $sth->fetchrow_arrayref) {
291 $csv->combine(@
$line);
292 $message .= $csv->string() . "\n";
293 push @rows_to_store, [@
$line] if $store_results;
296 if ( $store_results ) {
297 my $json = to_json
( \
@rows_to_store );
298 C4
::Reports
::Guided
::store_results
( $report_id, $json );
301 my $args = { to
=> $to, from
=> $from, subject
=> $subject };
302 if ( $format eq 'html' ) {
303 $message = "<html><head><style>tr:nth-child(2n+1) { background-color: #ccc;}</style></head><body>$message</body></html>";
304 $args->{contenttype
} = 'text/html';
306 my $email = Koha
::Email
->new();
307 my %mail = $email->create_message_headers($args);
308 $mail{Data
} = $message;
309 $mail{Auth
} = { user
=> $username, pass
=> $password, method
=> $method } if $username;
311 my $msg = MIME
::Lite
->new(%mail);
314 Type
=> "text/$format",
315 Data
=> encode
( 'utf8', $message ),
316 Filename
=> "report$report_id-$date.$format",
317 Disposition
=> 'attachment',
321 carp
"Mail not sent" unless $msg->last_send_successful();