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>.
24 use C4::Reports::Guided; # 0.12
30 use Getopt::Long qw(:config auto_help auto_version);
38 use vars qw($VERSION);
41 # find Koha's Perl modules
42 # test carefully before changing this
44 eval { require "$FindBin::Bin/../kohalib.pl" };
50 runreport.pl - Run pre-existing saved reports
54 runreport.pl [ -h | -m ] [ -v ] reportID [ reportID ... ]
57 -h --help brief help message
58 -m --man full documentation, same as --help --verbose
59 -v --verbose verbose output
61 --format=s selects format. Choice of text, html, csv, or tsv
63 -e --email whether to use e-mail (implied by --to or --from)
64 -a --attachment additionally attach the report as a file. cannot be used with html format
65 --username username to pass to the SMTP server for authentication
66 --password password to pass to the SMTP server for authentication
67 --method method is the type of authentication. Ie. LOGIN, DIGEST-MD5, etc.
68 --to=s e-mail address to send report to
69 --from=s e-mail address to send report from
70 --subject=s subject for the e-mail
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"
128 This script is designed to run existing Saved Reports.
130 =head1 USAGE EXAMPLES
134 In the most basic form, runs the report specified by ID number from
135 saved_sql.id, in this case #16, outputting the results to STDOUT.
137 B<runreport.pl 16 17>
139 Same as above, but also runs report #17.
148 Allow Saved Results option.
155 Reports - Guided Reports
159 # These variables can be set by command line options,
160 # initially set to default values.
174 my $username = undef;
175 my $password = undef;
176 my $method = 'LOGIN';
181 'verbose' => \$verbose,
182 'format=s' => \$format,
185 'subject=s' => \$subject,
187 'a|attachment' => \$attachment,
188 'username:s' => \$username,
189 'password:s' => \$password,
190 'method:s' => \$method,
193 pod2usage( -verbose => 2 ) if ($man);
194 pod2usage( -verbose => 2 ) if ($help and $verbose);
195 pod2usage(1) if $help;
200 $verbose and print STDERR "No format specified, assuming 'text'\n";
204 if ($format eq 'tsv' || $format eq 'text') {
209 if ($to or $from or $email) {
211 $from or $from = C4::Context->preference('KohaAdminEmailAddress');
212 $to or $to = C4::Context->preference('KohaAdminEmailAddress');
215 unless (scalar(@ARGV)) {
216 print STDERR "ERROR: No reportID(s) specified\n";
219 ($verbose) and print scalar(@ARGV), " argument(s) after options: " . join(" ", @ARGV) . "\n";
221 my $today = dt_from_string();
222 my $date = $today->ymd();
224 foreach my $report_id (@ARGV) {
225 my $report = get_saved_report($report_id);
227 warn "ERROR: No saved report $report_id found";
230 my $sql = $report->{savedsql};
231 my $report_name = $report->{report_name};
232 my $type = $report->{type};
234 $verbose and print "SQL: $sql\n\n";
235 if ( $subject eq "" )
237 if ( defined($report_name) and $report_name ne "")
239 $subject = $report_name ;
243 $subject = 'Koha Saved Report';
246 # my $results = execute_query($sql, undef, 0, 99999, $format, $report_id);
247 my ($sth) = execute_query($sql);
248 # execute_query(sql, , 0, 20, , )
249 my $count = scalar($sth->rows);
251 print "NO OUTPUT: 0 results from execute_query\n";
254 $verbose and print "$count results from execute_query\n";
257 if ($format eq 'html') {
258 my $cgi = CGI->new();
260 while (my $line = $sth->fetchrow_arrayref) {
261 foreach (@$line) { defined($_) or $_ = ''; } # catch undef values, replace w/ ''
262 push @rows, $cgi->TR( join('', $cgi->td($line)) ) . "\n";
264 $message = $cgi->table(join "", @rows);
265 } elsif ($format eq 'csv') {
266 my $csv = Text::CSV_XS->new({
267 quote_char => $quote,
268 sep_char => $separator,
270 while (my $line = $sth->fetchrow_arrayref) {
271 $csv->combine(@$line);
273 # defined($_) or $_ = '';
274 # $_ =~ s/$quote/\\$quote/g;
275 # $_ = "$quote$_$quote";
276 # } # catch undef values, replace w/ ''
277 # $message .= join ($separator, @$line) . "\n";
278 $message .= $csv->string() . "\n";
283 my $args = { to => $to, from => $from, subject => $subject };
284 if ( $format eq 'html' ) {
285 $message = "<html><head><style>tr:nth-child(2n+1) { background-color: #ccc;}</style></head><body>$message</body></html>";
286 $args->{contenttype} = 'text/html';
288 my $email = Koha::Email->new();
289 my %mail = $email->create_message_headers($args);
290 $mail{Data} = $message;
291 $mail{Auth} = { user => $username, pass => $password, method => $method } if $username;
293 my $msg = MIME::Lite->new(%mail);
296 Type => "text/$format",
297 Data => encode( 'utf8', $message ),
298 Filename => "report$report_id-$date.$format",
299 Disposition => 'attachment',
303 carp "Mail not sent" unless $msg->last_send_successful();