Bug 18417: Advanced Editor (Rancor) add shortcuts for copyright symbols (C) (P)
[koha.git] / misc / cronjobs / runreport.pl
blob1648f4d72acfb38b6f2cfe2bf5d0feefe68dab14
1 #!/usr/bin/perl
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>.
21 use Modern::Perl;
23 use C4::Reports::Guided; # 0.12
24 use C4::Context;
25 use C4::Log;
26 use Koha::Email;
27 use Koha::DateUtils;
29 use Getopt::Long qw(:config auto_help auto_version);
30 use Pod::Usage;
31 use MIME::Lite;
32 use Text::CSV_XS;
33 use CGI qw ( -utf8 );
34 use Carp;
35 use Encode;
36 use JSON qw( to_json );
38 BEGIN {
39 # find Koha's Perl modules
40 # test carefully before changing this
41 use FindBin;
42 eval { require "$FindBin::Bin/../kohalib.pl" };
45 =head1 NAME
47 runreport.pl - Run pre-existing saved reports
49 =head1 SYNOPSIS
51 runreport.pl [ -h | -m ] [ -v ] reportID [ reportID ... ]
53 Options:
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
71 Arguments:
72 reportID report ID Number from saved_sql.id, multiple ID's may be specified
74 =head1 OPTIONS
76 =over
78 =item B<--help>
80 Print a brief help message and exits.
82 =item B<--man>
84 Prints the manual page and exits.
86 =item B<-v>
88 Verbose. Without this flag set, only fatal errors are reported.
90 =item B<--format>
92 Current options are text, html, csv, and tsv. At the moment, text and tsv both produce tab-separated tab-separated output.
94 =item B<--email>
96 Whether to use e-mail (implied by --to or --from).
98 =item B<--username>
100 Username to pass to the SMTP server for authentication
102 =item B<--password>
104 Password to pass to the SMTP server for authentication
106 =item B<--method>
108 Method is the type of authentication. Ie. LOGIN, DIGEST-MD5, etc.
110 =item B<--to>
112 E-mail address to send report to. Defaults to KohaAdminEmailAddress.
114 =item B<--from>
116 E-mail address to send report from. Defaults to KohaAdminEmailAddress.
118 =item B<--subject>
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.
128 =back
130 =head1 DESCRIPTION
132 This script is designed to run existing Saved Reports.
134 =head1 USAGE EXAMPLES
136 B<runreport.pl 16>
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.
145 =head1 TO DO
147 =over
150 =item *
152 Allow Saved Results option.
155 =back
157 =head1 SEE ALSO
159 Reports - Guided Reports
161 =cut
163 # These variables can be set by command line options,
164 # initially set to default values.
166 my $help = 0;
167 my $man = 0;
168 my $verbose = 0;
169 my $email = 0;
170 my $attachment = 0;
171 my $format = "text";
172 my $to = "";
173 my $from = "";
174 my $subject = "";
175 my $separator = ',';
176 my $quote = '"';
177 my $store_results = 0;
179 my $username = undef;
180 my $password = undef;
181 my $method = 'LOGIN';
183 GetOptions(
184 'help|?' => \$help,
185 'man' => \$man,
186 'verbose' => \$verbose,
187 'format=s' => \$format,
188 'to=s' => \$to,
189 'from=s' => \$from,
190 'subject=s' => \$subject,
191 'email' => \$email,
192 'a|attachment' => \$attachment,
193 'username:s' => \$username,
194 'password:s' => \$password,
195 'method:s' => \$method,
196 'store-results' => \$store_results,
198 ) or pod2usage(2);
199 pod2usage( -verbose => 2 ) if ($man);
200 pod2usage( -verbose => 2 ) if ($help and $verbose);
201 pod2usage(1) if $help;
203 cronlogaction();
205 unless ($format) {
206 $verbose and print STDERR "No format specified, assuming 'text'\n";
207 $format = 'text';
210 if ($format eq 'tsv' || $format eq 'text') {
211 $format = 'csv';
212 $separator = "\t";
215 if ($to or $from or $email) {
216 $email = 1;
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";
223 pod2usage(1);
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);
232 unless ($report) {
233 warn "ERROR: No saved report $report_id found";
234 next;
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 ;
247 else
249 $subject = 'Koha Saved Report';
252 my ($sth) = execute_query( $sql, undef, undef, undef, $report_id );
253 my $count = scalar($sth->rows);
254 unless ($count) {
255 print "NO OUTPUT: 0 results from execute_query\n";
256 next;
258 $verbose and print "$count results from execute_query\n";
260 my $message;
261 my @rows_to_store;
262 if ($format eq 'html') {
263 my $cgi = CGI->new();
264 my @rows;
265 while (my $line = $sth->fetchrow_arrayref) {
266 foreach (@$line) { defined($_) or $_ = ''; } # catch undef values, replace w/ ''
267 push @rows, $cgi->TR( join('', $cgi->td($line)) ) . "\n";
268 push @rows_to_store, [@$line] if $store_results;
270 $message = $cgi->table(join "", @rows);
271 } elsif ($format eq 'csv') {
272 my $csv = Text::CSV_XS->new({
273 quote_char => $quote,
274 sep_char => $separator,
276 while (my $line = $sth->fetchrow_arrayref) {
277 $csv->combine(@$line);
278 $message .= $csv->string() . "\n";
279 push @rows_to_store, [@$line] if $store_results;
282 if ( $store_results ) {
283 my $json = to_json( \@rows_to_store );
284 C4::Reports::Guided::store_results( $report_id, $json );
286 if ($email) {
287 my $args = { to => $to, from => $from, subject => $subject };
288 if ( $format eq 'html' ) {
289 $message = "<html><head><style>tr:nth-child(2n+1) { background-color: #ccc;}</style></head><body>$message</body></html>";
290 $args->{contenttype} = 'text/html';
292 my $email = Koha::Email->new();
293 my %mail = $email->create_message_headers($args);
294 $mail{Data} = $message;
295 $mail{Auth} = { user => $username, pass => $password, method => $method } if $username;
297 my $msg = MIME::Lite->new(%mail);
299 $msg->attach(
300 Type => "text/$format",
301 Data => encode( 'utf8', $message ),
302 Filename => "report$report_id-$date.$format",
303 Disposition => 'attachment',
304 ) if $attachment;
306 $msg->send();
307 carp "Mail not sent" unless $msg->last_send_successful();
309 else {
310 print $message;