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
29 use Getopt
::Long
qw(:config auto_help auto_version);
37 use vars
qw($VERSION);
40 # find Koha's Perl modules
41 # test carefully before changing this
43 eval { require "$FindBin::Bin/../kohalib.pl" };
49 runreport.pl - Run pre-existing saved reports
53 runreport.pl [ -h | -m ] [ -v ] reportID [ reportID ... ]
56 -h --help brief help message
57 -m --man full documentation, same as --help --verbose
58 -v --verbose verbose output
60 --format=s selects format. Choice of text, html, csv, or tsv
62 -e --email whether to use e-mail (implied by --to or --from)
63 --username username to pass to the SMTP server for authentication
64 --password password to pass to the SMTP server for authentication
65 --method method is the type of authentication. Ie. LOGIN, DIGEST-MD5, etc.
66 --to=s e-mail address to send report to
67 --from=s e-mail address to send report from
68 --subject=s subject for the e-mail
72 reportID report ID Number from saved_sql.id, multiple ID's may be specified
80 Print a brief help message and exits.
84 Prints the manual page and exits.
88 Verbose. Without this flag set, only fatal errors are reported.
92 Current options are text, html, csv, and tsv. At the moment, text and tsv both produce tab-separated tab-separated output.
96 Whether to use e-mail (implied by --to or --from).
100 Username to pass to the SMTP server for authentication
104 Password to pass to the SMTP server for authentication
108 Method is the type of authentication. Ie. LOGIN, DIGEST-MD5, etc.
112 E-mail address to send report to. Defaults to KohaAdminEmailAddress.
116 E-mail address to send report from. Defaults to KohaAdminEmailAddress.
120 Subject for the e-mail message. Defaults to "Koha Saved Report"
126 This script is designed to run existing Saved Reports.
128 =head1 USAGE EXAMPLES
132 In the most basic form, runs the report specified by ID number from
133 saved_sql.id, in this case #16, outputting the results to STDOUT.
135 B<runreport.pl 16 17>
137 Same as above, but also runs report #17.
146 Allow Saved Results option.
153 Reports - Guided Reports
157 # These variables can be set by command line options,
158 # initially set to default values.
167 my $subject = 'Koha Saved Report';
171 my $username = undef;
172 my $password = undef;
173 my $method = 'LOGIN';
178 'verbose' => \$verbose,
179 'format=s' => \$format,
182 'subject=s' => \$subject,
184 'username:s' => \$username,
185 'password:s' => \$password,
186 'method:s' => \$method,
189 pod2usage( -verbose => 2 ) if ($man);
190 pod2usage( -verbose => 2 ) if ($help and $verbose);
191 pod2usage(1) if $help;
196 $verbose and print STDERR "No format specified, assuming 'text'\n";
200 if ($format eq 'tsv' || $format eq 'text') {
205 if ($to or $from or $email) {
207 $from or $from = C4::Context->preference('KohaAdminEmailAddress');
208 $to or $to = C4::Context->preference('KohaAdminEmailAddress');
211 unless (scalar(@ARGV)) {
212 print STDERR "ERROR: No reportID(s) specified\n";
215 ($verbose) and print scalar(@ARGV), " argument(s) after options: " . join(" ", @ARGV) . "\n";
218 foreach my $report_id (@ARGV) {
219 my $report = get_saved_report($report_id);
221 warn "ERROR: No saved report $report_id found";
224 my $sql = $report->{savedsql};
225 my $report_name = $report->{report_name};
226 my $type = $report->{type};
228 $verbose and print "SQL: $sql\n\n";
229 if (defined($report_name) and $report_name ne "")
231 $subject = $report_name ;
235 $subject = 'Koha Saved Report';
237 # my $results = execute_query($sql, undef, 0, 99999, $format, $report_id);
238 my ($sth) = execute_query($sql);
239 # execute_query(sql, , 0, 20, , )
240 my $count = scalar($sth->rows);
242 print "NO OUTPUT: 0 results from execute_query\n";
245 $verbose and print "$count results from execute_query\n";
248 if ($format eq 'html') {
249 my $cgi = CGI->new();
251 while (my $line = $sth->fetchrow_arrayref) {
252 foreach (@$line) { defined($_) or $_ = ''; } # catch undef values, replace w/ ''
253 push @rows, $cgi->TR( join('', $cgi->td($line)) ) . "\n";
255 $message = $cgi->table(join "", @rows);
256 } elsif ($format eq 'csv') {
257 my $csv = Text::CSV_XS->new({
258 quote_char => $quote,
259 sep_char => $separator,
261 while (my $line = $sth->fetchrow_arrayref) {
262 $csv->combine(@$line);
264 # defined($_) or $_ = '';
265 # $_ =~ s/$quote/\\$quote/g;
266 # $_ = "$quote$_$quote";
267 # } # catch undef values, replace w/ ''
268 # $message .= join ($separator, @$line) . "\n";
269 $message .= $csv->string() . "\n";
273 my $email = Koha::Email->new();
275 if ($format eq 'html') {
276 $message = "<html><head><style>tr:nth-child(2n+1) { background-color: #ccc;}</style></head><body>$message</body></html>";
277 %mail = $email->create_message_headers({
280 contenttype => 'text/html',
281 subject => encode('utf8', $subject ),
282 message => encode('utf8', $message )
286 %mail = $email->create_message_headers ({
289 subject => encode('utf8', $subject ),
290 message => encode('utf8', $message )
294 $mail{'Auth'} = {user => $username, pass => $password, method => $method} if $username;
295 sendmail(%mail) or carp 'mail not sent:' . $Mail::Sendmail::error;
299 # my @xmlarray = ... ;
300 # my $url = "/cgi-bin/koha/reports/guided_reports.pl?phase=retrieve%20results&id=$id";
301 # my $xml = XML::Dumper->new()->pl2xml( \@xmlarray );
302 # store_results($id,$xml);