#!/usr/bin/perl
#
# Copyright 2008 Liblime
+# Copyright 2014 Foundations Bible College, Inc.
#
# This file is part of Koha.
#
-# Koha is free software; you can redistribute it and/or modify it under the
-# terms of the GNU General Public License as published by the Free Software
-# Foundation; either version 2 of the License, or (at your option) any later
-# version.
+# Koha is free software; you can redistribute it and/or modify it
+# under the terms of the GNU General Public License as published by
+# the Free Software Foundation; either version 3 of the License, or
+# (at your option) any later version.
#
-# Koha is distributed in the hope that it will be useful, but WITHOUT ANY
-# WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
-# A PARTICULAR PURPOSE. See the GNU General Public License for more details.
+# Koha is distributed in the hope that it will be useful, but
+# WITHOUT ANY WARRANTY; without even the implied warranty of
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+# GNU General Public License for more details.
#
-# You should have received a copy of the GNU General Public License along
-# with Koha; if not, write to the Free Software Foundation, Inc.,
-# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
+# You should have received a copy of the GNU General Public License
+# along with Koha; if not, see <http://www.gnu.org/licenses>.
-use strict;
-use warnings;
+use Modern::Perl;
use C4::Reports::Guided; # 0.12
use C4::Context;
+use C4::Log;
+use Koha::Email;
+use Koha::DateUtils;
use Getopt::Long qw(:config auto_help auto_version);
use Pod::Usage;
-use Mail::Sendmail;
+use MIME::Lite;
use Text::CSV_XS;
-use CGI;
+use CGI qw ( -utf8 );
use Carp;
-
-use vars qw($VERSION);
+use Encode;
+use JSON qw( to_json );
BEGIN {
# find Koha's Perl modules
# test carefully before changing this
use FindBin;
eval { require "$FindBin::Bin/../kohalib.pl" };
- $VERSION = 0.22;
}
=head1 NAME
-m --man full documentation, same as --help --verbose
-v --verbose verbose output
+ --format=s selects format. Choice of text, html, csv or tsv
+
+ -e --email whether to use e-mail (implied by --to or --from)
+ -a --attachment additionally attach the report as a file. cannot be used with html format
+ --username username to pass to the SMTP server for authentication
+ --password password to pass to the SMTP server for authentication
+ --method method is the type of authentication. Ie. LOGIN, DIGEST-MD5, etc.
+ --to=s e-mail address to send report to
+ --from=s e-mail address to send report from
+ --subject=s subject for the e-mail
+ --store-results store the result of the report
+
+
Arguments:
reportID report ID Number from saved_sql.id, multiple ID's may be specified
=over
-=item B<-help>
+=item B<--help>
Print a brief help message and exits.
-=item B<-man>
+=item B<--man>
Prints the manual page and exits.
Verbose. Without this flag set, only fatal errors are reported.
+=item B<--format>
+
+Current options are text, html, csv, and tsv. At the moment, text and tsv both produce tab-separated tab-separated output.
+
+=item B<--email>
+
+Whether to use e-mail (implied by --to or --from).
+
+=item B<--username>
+
+Username to pass to the SMTP server for authentication
+
+=item B<--password>
+
+Password to pass to the SMTP server for authentication
+
+=item B<--method>
+
+Method is the type of authentication. Ie. LOGIN, DIGEST-MD5, etc.
+
+=item B<--to>
+
+E-mail address to send report to. Defaults to KohaAdminEmailAddress.
+
+=item B<--from>
+
+E-mail address to send report from. Defaults to KohaAdminEmailAddress.
+
+=item B<--subject>
+
+Subject for the e-mail message. Defaults to "Koha Saved Report"
+
+=item B<--store-results>
+
+Store the result of the report into the saved_reports DB table.
+
+To access the results, go on Reports > Guided reports > Saved report.
+
=back
=head1 DESCRIPTION
=over
-=item *
-
-Complete testing for Sendmail related options: --email, --to, and --from.
=item *
Allow Saved Results option.
-=item *
-
-Possible --format option for CSV or tab-delimited output.
=back
my $man = 0;
my $verbose = 0;
my $email = 0;
-my $format = "";
+my $attachment = 0;
+my $format = "text";
my $to = "";
my $from = "";
-my $subject = 'Koha Saved Report';
+my $subject = "";
+my $separator = ',';
+my $quote = '"';
+my $store_results = 0;
+
+my $username = undef;
+my $password = undef;
+my $method = 'LOGIN';
GetOptions(
- 'help|?' => \$help,
- 'man' => \$man,
- 'verbose' => \$verbose,
- 'format' => \$format,
- 'to' => \$to,
- 'from' => \$from,
- 'email' => \$email,
+ 'help|?' => \$help,
+ 'man' => \$man,
+ 'verbose' => \$verbose,
+ 'format=s' => \$format,
+ 'to=s' => \$to,
+ 'from=s' => \$from,
+ 'subject=s' => \$subject,
+ 'email' => \$email,
+ 'a|attachment' => \$attachment,
+ 'username:s' => \$username,
+ 'password:s' => \$password,
+ 'method:s' => \$method,
+ 'store-results' => \$store_results,
+
) or pod2usage(2);
pod2usage( -verbose => 2 ) if ($man);
pod2usage( -verbose => 2 ) if ($help and $verbose);
pod2usage(1) if $help;
+cronlogaction();
+
unless ($format) {
$verbose and print STDERR "No format specified, assuming 'text'\n";
- $format = '';
- # $format = 'text';
+ $format = 'text';
+}
+
+if ($format eq 'tsv' || $format eq 'text') {
+ $format = 'csv';
+ $separator = "\t";
}
if ($to or $from or $email) {
}
($verbose) and print scalar(@ARGV), " argument(s) after options: " . join(" ", @ARGV) . "\n";
+my $today = dt_from_string();
+my $date = $today->ymd();
-foreach my $report (@ARGV) {
- my ($sql, $type) = get_saved_report($report);
- unless ($sql) {
- carp "ERROR: No saved report $report found";
+foreach my $report_id (@ARGV) {
+ my $report = get_saved_report($report_id);
+ unless ($report) {
+ warn "ERROR: No saved report $report_id found";
next;
}
+ my $sql = $report->{savedsql};
+ my $report_name = $report->{report_name};
+ my $type = $report->{type};
+
$verbose and print "SQL: $sql\n\n";
- # my $results = execute_query($sql, undef, 0, 99999, $format, $report);
- my ($sth) = execute_query($sql);
- # execute_query(sql, , 0, 20, , )
+ if ( $subject eq "" )
+ {
+ if ( defined($report_name) and $report_name ne "")
+ {
+ $subject = $report_name ;
+ }
+ else
+ {
+ $subject = 'Koha Saved Report';
+ }
+ }
+ my ($sth) = execute_query( $sql, undef, undef, undef, $report_id );
my $count = scalar($sth->rows);
unless ($count) {
print "NO OUTPUT: 0 results from execute_query\n";
}
$verbose and print "$count results from execute_query\n";
- my $cgi = CGI->new();
- my @rows = ();
- while (my $line = $sth->fetchrow_arrayref) {
- foreach (@$line) { defined($_) or $_ = ''; } # catch undef values, replace w/ ''
- push @rows, $cgi->TR( join('', $cgi->td($line)) ) . "\n";
+ my $message;
+ my @rows_to_store;
+ if ($format eq 'html') {
+ my $cgi = CGI->new();
+ my @rows;
+ while (my $line = $sth->fetchrow_arrayref) {
+ foreach (@$line) { defined($_) or $_ = ''; } # catch undef values, replace w/ ''
+ push @rows, $cgi->TR( join('', $cgi->td($line)) ) . "\n";
+ push @rows_to_store, [@$line] if $store_results;
+ }
+ $message = $cgi->table(join "", @rows);
+ } elsif ($format eq 'csv') {
+ my $csv = Text::CSV_XS->new({
+ quote_char => $quote,
+ sep_char => $separator,
+ });
+ while (my $line = $sth->fetchrow_arrayref) {
+ $csv->combine(@$line);
+ $message .= $csv->string() . "\n";
+ push @rows_to_store, [@$line] if $store_results;
+ }
+ }
+ if ( $store_results ) {
+ my $json = to_json( \@rows_to_store );
+ C4::Reports::Guided::store_results( $report_id, $json );
+ }
+ if ($email) {
+ my $args = { to => $to, from => $from, subject => $subject };
+ if ( $format eq 'html' ) {
+ $message = "<html><head><style>tr:nth-child(2n+1) { background-color: #ccc;}</style></head><body>$message</body></html>";
+ $args->{contenttype} = 'text/html';
+ }
+ my $email = Koha::Email->new();
+ my %mail = $email->create_message_headers($args);
+ $mail{Data} = $message;
+ $mail{Auth} = { user => $username, pass => $password, method => $method } if $username;
+
+ my $msg = MIME::Lite->new(%mail);
+
+ $msg->attach(
+ Type => "text/$format",
+ Data => encode( 'utf8', $message ),
+ Filename => "report$report_id-$date.$format",
+ Disposition => 'attachment',
+ ) if $attachment;
+
+ $msg->send();
+ carp "Mail not sent" unless $msg->last_send_successful();
}
- my $message = $cgi->table(join "", @rows);
-
- if ($email){
- my %mail = (
- To => $to,
- From => $from,
- Subject => $subject,
- Message => $message
- );
- sendmail(%mail) or carp 'mail not sent:' . $Mail::Sendmail::error;
- } else {
+ else {
print $message;
}
- # my @xmlarray = ... ;
- # my $url = "/cgi-bin/koha/reports/guided_reports.pl?phase=retrieve%20results&id=$id";
- # my $xml = XML::Dumper->new()->pl2xml( \@xmlarray );
- # store_results($id,$xml);
}