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
72 reportID report ID Number from saved_sql.id, multiple ID's may be specified
80 Print a brief help message and exits.
84 Prints the manual page and exits.
88 Verbose. Without this flag set, only fatal errors are reported.
92 Current options are text, html, csv, and tsv. At the moment, text and tsv both produce tab-separated tab-separated output.
96 Whether to use e-mail (implied by --to or --from).
100 Username to pass to the SMTP server for authentication
104 Password to pass to the SMTP server for authentication
108 Method is the type of authentication. Ie. LOGIN, DIGEST-MD5, etc.
112 E-mail address to send report to. Defaults to KohaAdminEmailAddress.
116 E-mail address to send report from. Defaults to KohaAdminEmailAddress.
120 Subject for the e-mail message. Defaults to "Koha Saved Report"
122 =item B<--store-results>
124 Store the result of the report into the saved_reports DB table.
126 To access the results, go on Reports > Guided reports > Saved report.
132 This script is designed to run existing Saved Reports.
134 =head1 USAGE EXAMPLES
138 In the most basic form, runs the report specified by ID number from
139 saved_sql.id, in this case #16, outputting the results to STDOUT.
141 B<runreport.pl 16 17>
143 Same as above, but also runs report #17.
152 Allow Saved Results option.
159 Reports - Guided Reports
163 # These variables can be set by command line options,
164 # initially set to default values.
177 my $store_results = 0;
179 my $username = undef;
180 my $password = undef;
181 my $method = 'LOGIN';
186 'verbose' => \
$verbose,
187 'format=s' => \
$format,
190 'subject=s' => \
$subject,
192 'a|attachment' => \
$attachment,
193 'username:s' => \
$username,
194 'password:s' => \
$password,
195 'method:s' => \
$method,
196 'store-results' => \
$store_results,
199 pod2usage
( -verbose
=> 2 ) if ($man);
200 pod2usage
( -verbose
=> 2 ) if ($help and $verbose);
201 pod2usage
(1) if $help;
206 $verbose and print STDERR
"No format specified, assuming 'text'\n";
210 if ($format eq 'tsv' || $format eq 'text') {
215 if ($to or $from or $email) {
217 $from or $from = C4
::Context
->preference('KohaAdminEmailAddress');
218 $to or $to = C4
::Context
->preference('KohaAdminEmailAddress');
221 unless (scalar(@ARGV)) {
222 print STDERR
"ERROR: No reportID(s) specified\n";
225 ($verbose) and print scalar(@ARGV), " argument(s) after options: " . join(" ", @ARGV) . "\n";
227 my $today = dt_from_string
();
228 my $date = $today->ymd();
230 foreach my $report_id (@ARGV) {
231 my $report = get_saved_report
($report_id);
233 warn "ERROR: No saved report $report_id found";
236 my $sql = $report->{savedsql
};
237 my $report_name = $report->{report_name
};
238 my $type = $report->{type
};
240 $verbose and print "SQL: $sql\n\n";
241 if ( $subject eq "" )
243 if ( defined($report_name) and $report_name ne "")
245 $subject = $report_name ;
249 $subject = 'Koha Saved Report';
252 # my $results = execute_query($sql, undef, 0, 99999, $format, $report_id);
253 my ($sth) = execute_query
($sql);
254 my $count = scalar($sth->rows);
256 print "NO OUTPUT: 0 results from execute_query\n";
259 $verbose and print "$count results from execute_query\n";
263 if ($format eq 'html') {
264 my $cgi = CGI
->new();
266 while (my $line = $sth->fetchrow_arrayref) {
267 foreach (@
$line) { defined($_) or $_ = ''; } # catch undef values, replace w/ ''
268 push @rows, $cgi->TR( join('', $cgi->td($line)) ) . "\n";
269 push @rows_to_store, [@
$line] if $store_results;
271 $message = $cgi->table(join "", @rows);
272 } elsif ($format eq 'csv') {
273 my $csv = Text
::CSV_XS
->new({
274 quote_char
=> $quote,
275 sep_char
=> $separator,
277 while (my $line = $sth->fetchrow_arrayref) {
278 $csv->combine(@
$line);
279 $message .= $csv->string() . "\n";
280 push @rows_to_store, [@
$line] if $store_results;
283 if ( $store_results ) {
284 my $json = to_json
( \
@rows_to_store );
285 C4
::Reports
::Guided
::store_results
( $report_id, $json );
288 my $args = { to
=> $to, from
=> $from, subject
=> $subject };
289 if ( $format eq 'html' ) {
290 $message = "<html><head><style>tr:nth-child(2n+1) { background-color: #ccc;}</style></head><body>$message</body></html>";
291 $args->{contenttype
} = 'text/html';
293 my $email = Koha
::Email
->new();
294 my %mail = $email->create_message_headers($args);
295 $mail{Data
} = $message;
296 $mail{Auth
} = { user
=> $username, pass
=> $password, method
=> $method } if $username;
298 my $msg = MIME
::Lite
->new(%mail);
301 Type
=> "text/$format",
302 Data
=> encode
( 'utf8', $message ),
303 Filename
=> "report$report_id-$date.$format",
304 Disposition
=> 'attachment',
308 carp
"Mail not sent" unless $msg->last_send_successful();