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
29 use Getopt
::Long
qw(:config auto_help auto_version);
36 use JSON
qw( to_json );
39 # find Koha's Perl modules
40 # test carefully before changing this
42 eval { require "$FindBin::Bin/../kohalib.pl" };
47 runreport.pl - Run pre-existing saved reports
51 runreport.pl [ -h | -m ] [ -v ] reportID [ reportID ... ]
54 -h --help brief help message
55 -m --man full documentation, same as --help --verbose
56 -v --verbose verbose output
58 --format=s selects format. Choice of text, html, csv or tsv
60 -e --email whether to use e-mail (implied by --to or --from)
61 -a --attachment additionally attach the report as a file. cannot be used with html format
62 --username username to pass to the SMTP server for authentication
63 --password password to pass to the SMTP server for authentication
64 --method method is the type of authentication. Ie. LOGIN, DIGEST-MD5, etc.
65 --to=s e-mail address to send report to
66 --from=s e-mail address to send report from
67 --subject=s subject for the e-mail
68 --store-results store the result of the report
69 --csv-header add column names as first line of csv output
73 reportID report ID Number from saved_sql.id, multiple ID's may be specified
81 Print a brief help message and exits.
85 Prints the manual page and exits.
89 Verbose. Without this flag set, only fatal errors are reported.
93 Current options are text, html, csv, and tsv. At the moment, text and tsv both produce tab-separated tab-separated output.
97 Whether to use e-mail (implied by --to or --from).
101 Username to pass to the SMTP server for authentication
105 Password to pass to the SMTP server for authentication
109 Method is the type of authentication. Ie. LOGIN, DIGEST-MD5, etc.
113 E-mail address to send report to. Defaults to KohaAdminEmailAddress.
117 E-mail address to send report from. Defaults to KohaAdminEmailAddress.
121 Subject for the e-mail message. Defaults to "Koha Saved Report"
123 =item B<--store-results>
125 Store the result of the report into the saved_reports DB table.
127 To access the results, go on Reports > Guided reports > Saved report.
133 This script is designed to run existing Saved Reports.
135 =head1 USAGE EXAMPLES
139 In the most basic form, runs the report specified by ID number from
140 saved_sql.id, in this case #16, outputting the results to STDOUT.
142 B<runreport.pl 16 17>
144 Same as above, but also runs report #17.
153 Allow Saved Results option.
160 Reports - Guided Reports
164 # These variables can be set by command line options,
165 # initially set to default values.
178 my $store_results = 0;
181 my $username = undef;
182 my $password = undef;
183 my $method = 'LOGIN';
188 'verbose' => \
$verbose,
189 'format=s' => \
$format,
192 'subject=s' => \
$subject,
194 'a|attachment' => \
$attachment,
195 'username:s' => \
$username,
196 'password:s' => \
$password,
197 'method:s' => \
$method,
198 'store-results' => \
$store_results,
199 'csv-header' => \
$csv_header,
202 pod2usage
( -verbose
=> 2 ) if ($man);
203 pod2usage
( -verbose
=> 2 ) if ($help and $verbose);
204 pod2usage
(1) if $help;
209 $verbose and print STDERR
"No format specified, assuming 'text'\n";
213 if ($format eq 'tsv' || $format eq 'text') {
218 if ($to or $from or $email) {
220 $from or $from = C4
::Context
->preference('KohaAdminEmailAddress');
221 $to or $to = C4
::Context
->preference('KohaAdminEmailAddress');
224 unless (scalar(@ARGV)) {
225 print STDERR
"ERROR: No reportID(s) specified\n";
228 ($verbose) and print scalar(@ARGV), " argument(s) after options: " . join(" ", @ARGV) . "\n";
230 my $today = dt_from_string
();
231 my $date = $today->ymd();
233 foreach my $report_id (@ARGV) {
234 my $report = get_saved_report
($report_id);
236 warn "ERROR: No saved report $report_id found";
239 my $sql = $report->{savedsql
};
240 my $report_name = $report->{report_name
};
241 my $type = $report->{type
};
243 $verbose and print "SQL: $sql\n\n";
244 if ( $subject eq "" )
246 if ( defined($report_name) and $report_name ne "")
248 $subject = $report_name ;
252 $subject = 'Koha Saved Report';
255 my ($sth) = execute_query
( $sql, undef, undef, undef, $report_id );
256 my $count = scalar($sth->rows);
258 print "NO OUTPUT: 0 results from execute_query\n";
261 $verbose and print "$count results from execute_query\n";
265 if ($format eq 'html') {
266 my $cgi = CGI
->new();
268 while (my $line = $sth->fetchrow_arrayref) {
269 foreach (@
$line) { defined($_) or $_ = ''; } # catch undef values, replace w/ ''
270 push @rows, $cgi->TR( join('', $cgi->td($line)) ) . "\n";
271 push @rows_to_store, [@
$line] if $store_results;
273 $message = $cgi->table(join "", @rows);
274 } elsif ($format eq 'csv') {
275 my $csv = Text
::CSV_XS
->new({
276 quote_char
=> $quote,
277 sep_char
=> $separator,
281 my $fields = $sth->{NAME
};
282 $csv->combine( @
$fields );
283 $message .= $csv->string() . "\n";
284 push @rows_to_store, [@
$fields] if $store_results;
287 while (my $line = $sth->fetchrow_arrayref) {
288 $csv->combine(@
$line);
289 $message .= $csv->string() . "\n";
290 push @rows_to_store, [@
$line] if $store_results;
293 if ( $store_results ) {
294 my $json = to_json
( \
@rows_to_store );
295 C4
::Reports
::Guided
::store_results
( $report_id, $json );
298 my $args = { to
=> $to, from
=> $from, subject
=> $subject };
299 if ( $format eq 'html' ) {
300 $message = "<html><head><style>tr:nth-child(2n+1) { background-color: #ccc;}</style></head><body>$message</body></html>";
301 $args->{contenttype
} = 'text/html';
303 my $email = Koha
::Email
->new();
304 my %mail = $email->create_message_headers($args);
305 $mail{Data
} = $message;
306 $mail{Auth
} = { user
=> $username, pass
=> $password, method
=> $method } if $username;
308 my $msg = MIME
::Lite
->new(%mail);
311 Type
=> "text/$format",
312 Data
=> encode
( 'utf8', $message ),
313 Filename
=> "report$report_id-$date.$format",
314 Disposition
=> 'attachment',
318 carp
"Mail not sent" unless $msg->last_send_successful();