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);
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
71 reportID report ID Number from saved_sql.id, multiple ID's may be specified
79 Print a brief help message and exits.
83 Prints the manual page and exits.
87 Verbose. Without this flag set, only fatal errors are reported.
91 Current options are text, html, csv, and tsv. At the moment, text and tsv both produce tab-separated tab-separated output.
95 Whether to use e-mail (implied by --to or --from).
99 Username to pass to the SMTP server for authentication
103 Password to pass to the SMTP server for authentication
107 Method is the type of authentication. Ie. LOGIN, DIGEST-MD5, etc.
111 E-mail address to send report to. Defaults to KohaAdminEmailAddress.
115 E-mail address to send report from. Defaults to KohaAdminEmailAddress.
119 Subject for the e-mail message. Defaults to "Koha Saved Report"
125 This script is designed to run existing Saved Reports.
127 =head1 USAGE EXAMPLES
131 In the most basic form, runs the report specified by ID number from
132 saved_sql.id, in this case #16, outputting the results to STDOUT.
134 B<runreport.pl 16 17>
136 Same as above, but also runs report #17.
145 Allow Saved Results option.
152 Reports - Guided Reports
156 # These variables can be set by command line options,
157 # initially set to default values.
171 my $username = undef;
172 my $password = undef;
173 my $method = 'LOGIN';
178 'verbose' => \$verbose,
179 'format=s' => \$format,
182 'subject=s' => \$subject,
184 'a|attachment' => \$attachment,
185 'username:s' => \$username,
186 'password:s' => \$password,
187 'method:s' => \$method,
190 pod2usage( -verbose => 2 ) if ($man);
191 pod2usage( -verbose => 2 ) if ($help and $verbose);
192 pod2usage(1) if $help;
197 $verbose and print STDERR "No format specified, assuming 'text'\n";
201 if ($format eq 'tsv' || $format eq 'text') {
206 if ($to or $from or $email) {
208 $from or $from = C4::Context->preference('KohaAdminEmailAddress');
209 $to or $to = C4::Context->preference('KohaAdminEmailAddress');
212 unless (scalar(@ARGV)) {
213 print STDERR "ERROR: No reportID(s) specified\n";
216 ($verbose) and print scalar(@ARGV), " argument(s) after options: " . join(" ", @ARGV) . "\n";
218 my $today = dt_from_string();
219 my $date = $today->ymd();
221 foreach my $report_id (@ARGV) {
222 my $report = get_saved_report($report_id);
224 warn "ERROR: No saved report $report_id found";
227 my $sql = $report->{savedsql};
228 my $report_name = $report->{report_name};
229 my $type = $report->{type};
231 $verbose and print "SQL: $sql\n\n";
232 if ( $subject eq "" )
234 if ( defined($report_name) and $report_name ne "")
236 $subject = $report_name ;
240 $subject = 'Koha Saved Report';
243 # my $results = execute_query($sql, undef, 0, 99999, $format, $report_id);
244 my ($sth) = execute_query($sql);
245 # execute_query(sql, , 0, 20, , )
246 my $count = scalar($sth->rows);
248 print "NO OUTPUT: 0 results from execute_query\n";
251 $verbose and print "$count results from execute_query\n";
254 if ($format eq 'html') {
255 my $cgi = CGI->new();
257 while (my $line = $sth->fetchrow_arrayref) {
258 foreach (@$line) { defined($_) or $_ = ''; } # catch undef values, replace w/ ''
259 push @rows, $cgi->TR( join('', $cgi->td($line)) ) . "\n";
261 $message = $cgi->table(join "", @rows);
262 } elsif ($format eq 'csv') {
263 my $csv = Text::CSV_XS->new({
264 quote_char => $quote,
265 sep_char => $separator,
267 while (my $line = $sth->fetchrow_arrayref) {
268 $csv->combine(@$line);
270 # defined($_) or $_ = '';
271 # $_ =~ s/$quote/\\$quote/g;
272 # $_ = "$quote$_$quote";
273 # } # catch undef values, replace w/ ''
274 # $message .= join ($separator, @$line) . "\n";
275 $message .= $csv->string() . "\n";
280 my $args = { to => $to, from => $from, subject => $subject };
281 if ( $format eq 'html' ) {
282 $message = "<html><head><style>tr:nth-child(2n+1) { background-color: #ccc;}</style></head><body>$message</body></html>";
283 $args->{contenttype} = 'text/html';
285 my $email = Koha::Email->new();
286 my %mail = $email->create_message_headers($args);
287 $mail{Data} = $message;
288 $mail{Auth} = { user => $username, pass => $password, method => $method } if $username;
290 my $msg = MIME::Lite->new(%mail);
293 Type => "text/$format",
294 Data => encode( 'utf8', $message ),
295 Filename => "report$report_id-$date.$format",
296 Disposition => 'attachment',
300 carp "Mail not sent" unless $msg->last_send_successful();