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 under the
9 # terms of the GNU General Public License as published by the Free Software
10 # Foundation; either version 2 of the License, or (at your option) any later
13 # Koha is distributed in the hope that it will be useful, but WITHOUT ANY
14 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
15 # A PARTICULAR PURPOSE. See the GNU General Public License for more details.
17 # You should have received a copy of the GNU General Public License along
18 # with Koha; if not, write to the Free Software Foundation, Inc.,
19 # 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
24 use C4::Reports::Guided; # 0.12
28 use Getopt::Long qw(:config auto_help auto_version);
36 use vars qw($VERSION);
39 # find Koha's Perl modules
40 # test carefully before changing this
42 eval { require "$FindBin::Bin/../kohalib.pl" };
48 runreport.pl - Run pre-existing saved reports
52 runreport.pl [ -h | -m ] [ -v ] reportID [ reportID ... ]
55 -h --help brief help message
56 -m --man full documentation, same as --help --verbose
57 -v --verbose verbose output
59 --format=s selects format. Choice of text, html, csv, or tsv
61 -e --email whether to use e-mail (implied by --to or --from)
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.
166 my $subject = 'Koha Saved Report';
170 my $username = undef;
171 my $password = undef;
172 my $method = 'LOGIN';
177 'verbose' => \$verbose,
178 'format=s' => \$format,
181 'subject=s' => \$subject,
183 'username:s' => \$username,
184 'password:s' => \$password,
185 'method:s' => \$method,
188 pod2usage( -verbose => 2 ) if ($man);
189 pod2usage( -verbose => 2 ) if ($help and $verbose);
190 pod2usage(1) if $help;
193 $verbose and print STDERR "No format specified, assuming 'text'\n";
197 if ($format eq 'tsv' || $format eq 'text') {
202 if ($to or $from or $email) {
204 $from or $from = C4::Context->preference('KohaAdminEmailAddress');
205 $to or $to = C4::Context->preference('KohaAdminEmailAddress');
208 unless (scalar(@ARGV)) {
209 print STDERR "ERROR: No reportID(s) specified\n";
212 ($verbose) and print scalar(@ARGV), " argument(s) after options: " . join(" ", @ARGV) . "\n";
215 foreach my $report_id (@ARGV) {
216 my $report = get_saved_report($report_id);
218 warn "ERROR: No saved report $report_id found";
221 my $sql = $report->{savedsql};
222 my $report_name = $report->{report_name};
223 my $type = $report->{type};
225 $verbose and print "SQL: $sql\n\n";
226 if (defined($report_name) and $report_name ne "")
228 $subject = $report_name ;
232 $subject = 'Koha Saved Report';
234 # my $results = execute_query($sql, undef, 0, 99999, $format, $report_id);
235 my ($sth) = execute_query($sql);
236 # execute_query(sql, , 0, 20, , )
237 my $count = scalar($sth->rows);
239 print "NO OUTPUT: 0 results from execute_query\n";
242 $verbose and print "$count results from execute_query\n";
245 if ($format eq 'html') {
246 my $cgi = CGI->new();
248 while (my $line = $sth->fetchrow_arrayref) {
249 foreach (@$line) { defined($_) or $_ = ''; } # catch undef values, replace w/ ''
250 push @rows, $cgi->TR( join('', $cgi->td($line)) ) . "\n";
252 $message = $cgi->table(join "", @rows);
253 } elsif ($format eq 'csv') {
254 my $csv = Text::CSV_XS->new({
255 quote_char => $quote,
256 sep_char => $separator,
258 while (my $line = $sth->fetchrow_arrayref) {
259 $csv->combine(@$line);
261 # defined($_) or $_ = '';
262 # $_ =~ s/$quote/\\$quote/g;
263 # $_ = "$quote$_$quote";
264 # } # catch undef values, replace w/ ''
265 # $message .= join ($separator, @$line) . "\n";
266 $message .= $csv->string() . "\n";
270 my $email = Koha::Email->new();
272 if ($format eq 'html') {
273 $message = "<html><head><style>tr:nth-child(2n+1) { background-color: #ccc;}</style></head><body>$message</body></html>";
274 %mail = $email->create_message_headers({
277 contenttype => 'text/html',
278 subject => encode('utf8', $subject ),
279 message => encode('utf8', $message )
283 %mail = $email->create_message_headers ({
286 subject => encode('utf8', $subject ),
287 message => encode('utf8', $message )
291 $mail{'Auth'} = {user => $username, pass => $password, method => $method} if $username;
292 sendmail(%mail) or carp 'mail not sent:' . $Mail::Sendmail::error;
296 # my @xmlarray = ... ;
297 # my $url = "/cgi-bin/koha/reports/guided_reports.pl?phase=retrieve%20results&id=$id";
298 # my $xml = XML::Dumper->new()->pl2xml( \@xmlarray );
299 # store_results($id,$xml);