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
27 use Getopt::Long qw(:config auto_help auto_version);
35 use vars qw($VERSION);
38 # find Koha's Perl modules
39 # test carefully before changing this
41 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 --username username to pass to the SMTP server for authentication
62 --password password to pass to the SMTP server for authentication
63 --method method is the type of authentication. Ie. LOGIN, DIGEST-MD5, etc.
64 --to=s e-mail address to send report to
65 --from=s e-mail address to send report from
66 --subject=s subject for the e-mail
70 reportID report ID Number from saved_sql.id, multiple ID's may be specified
78 Print a brief help message and exits.
82 Prints the manual page and exits.
86 Verbose. Without this flag set, only fatal errors are reported.
90 Current options are text, html, csv, and tsv. At the moment, text and tsv both produce tab-separated tab-separated output.
94 Whether to use e-mail (implied by --to or --from).
98 Username to pass to the SMTP server for authentication
102 Password to pass to the SMTP server for authentication
106 Method is the type of authentication. Ie. LOGIN, DIGEST-MD5, etc.
110 E-mail address to send report to. Defaults to KohaAdminEmailAddress.
114 E-mail address to send report from. Defaults to KohaAdminEmailAddress.
118 Subject for the e-mail message. Defaults to "Koha Saved Report"
124 This script is designed to run existing Saved Reports.
126 =head1 USAGE EXAMPLES
130 In the most basic form, runs the report specified by ID number from
131 saved_sql.id, in this case #16, outputting the results to STDOUT.
133 B<runreport.pl 16 17>
135 Same as above, but also runs report #17.
144 Allow Saved Results option.
151 Reports - Guided Reports
155 # These variables can be set by command line options,
156 # initially set to default values.
165 my $subject = 'Koha Saved Report';
169 my $username = undef;
170 my $password = undef;
171 my $method = 'LOGIN';
176 'verbose' => \$verbose,
177 'format=s' => \$format,
180 'subject=s' => \$subject,
182 'username:s' => \$username,
183 'password:s' => \$password,
184 'method:s' => \$method,
187 pod2usage( -verbose => 2 ) if ($man);
188 pod2usage( -verbose => 2 ) if ($help and $verbose);
189 pod2usage(1) if $help;
192 $verbose and print STDERR "No format specified, assuming 'text'\n";
196 if ($format eq 'tsv' || $format eq 'text') {
201 if ($to or $from or $email) {
203 $from or $from = C4::Context->preference('KohaAdminEmailAddress');
204 $to or $to = C4::Context->preference('KohaAdminEmailAddress');
207 unless (scalar(@ARGV)) {
208 print STDERR "ERROR: No reportID(s) specified\n";
211 ($verbose) and print scalar(@ARGV), " argument(s) after options: " . join(" ", @ARGV) . "\n";
214 foreach my $report_id (@ARGV) {
215 my $report = get_saved_report($report_id);
217 warn "ERROR: No saved report $report_id found";
220 my $sql = $report->{savedsql};
221 my $report_name = $report->{report_name};
222 my $type = $report->{type};
224 $verbose and print "SQL: $sql\n\n";
225 if (defined($report_name) and $report_name ne "")
227 $subject = $report_name ;
231 $subject = 'Koha Saved Report';
233 # my $results = execute_query($sql, undef, 0, 99999, $format, $report_id);
234 my ($sth) = execute_query($sql);
235 # execute_query(sql, , 0, 20, , )
236 my $count = scalar($sth->rows);
238 print "NO OUTPUT: 0 results from execute_query\n";
241 $verbose and print "$count results from execute_query\n";
244 if ($format eq 'html') {
245 my $cgi = CGI->new();
247 while (my $line = $sth->fetchrow_arrayref) {
248 foreach (@$line) { defined($_) or $_ = ''; } # catch undef values, replace w/ ''
249 push @rows, $cgi->TR( join('', $cgi->td($line)) ) . "\n";
251 $message = $cgi->table(join "", @rows);
252 } elsif ($format eq 'csv') {
253 my $csv = Text::CSV_XS->new({
254 quote_char => $quote,
255 sep_char => $separator,
257 while (my $line = $sth->fetchrow_arrayref) {
258 $csv->combine(@$line);
260 # defined($_) or $_ = '';
261 # $_ =~ s/$quote/\\$quote/g;
262 # $_ = "$quote$_$quote";
263 # } # catch undef values, replace w/ ''
264 # $message .= join ($separator, @$line) . "\n";
265 $message .= $csv->string() . "\n";
270 if ($format eq 'html') {
271 $message = "<html><head><style>tr:nth-child(2n+1) { background-color: #ccc;}</style></head><body>$message</body></html>";
275 'Content-Type' => 'text/html',
276 Subject => encode('utf8', $subject ),
277 Message => encode('utf8', $message )
283 Subject => encode('utf8', $subject ),
284 Message => encode('utf8', $message )
287 $mail{'Auth'} = {user => $username, pass => $password, method => $method} if $username;
288 sendmail(%mail) or carp 'mail not sent:' . $Mail::Sendmail::error;
292 # my @xmlarray = ... ;
293 # my $url = "/cgi-bin/koha/reports/guided_reports.pl?phase=retrieve%20results&id=$id";
294 # my $xml = XML::Dumper->new()->pl2xml( \@xmlarray );
295 # store_results($id,$xml);