misc/cronjobs/runreport.pl

   1 #!/usr/bin/perl
   2 #
   3 # Copyright 2008 Liblime
   4 # Copyright 2014 Foundations Bible College, Inc.
   5 #
   6 # This file is part of Koha.
   7 #
   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.
  12 #
  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.
  17 #
  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>.
  20
  21 use strict;
  22 use warnings;
  23
  24 use C4::Reports::Guided; # 0.12
  25 use C4::Context;
  26 use C4::Log;
  27 use Koha::Email;
  28 use Koha::DateUtils;
  29
  30 use Getopt::Long qw(:config auto_help auto_version);
  31 use Pod::Usage;
  32 use MIME::Lite;
  33 use Text::CSV_XS;
  34 use CGI qw ( -utf8 );
  35 use Carp;
  36 use Encode;
  37
  38 use vars qw($VERSION);
  39
  40 BEGIN {
  41     # find Koha's Perl modules
  42     # test carefully before changing this
  43     use FindBin;
  44     eval { require "$FindBin::Bin/../kohalib.pl" };
  45     $VERSION = 0.22;
  46 }
  47
  48 =head1 NAME
  49
  50 runreport.pl - Run pre-existing saved reports
  51
  52 =head1 SYNOPSIS
  53
  54 runreport.pl [ -h | -m ] [ -v ] reportID [ reportID ... ]
  55
  56  Options:
  57    -h --help       brief help message
  58    -m --man        full documentation, same as --help --verbose
  59    -v --verbose    verbose output
  60
  61    --format=s      selects format. Choice of text, html, csv, or tsv
  62
  63    -e --email      whether to use e-mail (implied by --to or --from)
  64    -a --attachment additionally attach the report as a file. cannot be used with html format
  65    --username      username to pass to the SMTP server for authentication
  66    --password      password to pass to the SMTP server for authentication
  67    --method        method is the type of authentication. Ie. LOGIN, DIGEST-MD5, etc.
  68    --to=s          e-mail address to send report to
  69    --from=s        e-mail address to send report from
  70    --subject=s     subject for the e-mail
  71
  72
  73  Arguments:
  74    reportID        report ID Number from saved_sql.id, multiple ID's may be specified
  75
  76 =head1 OPTIONS
  77
  78 =over
  79
  80 =item B<--help>
  81
  82 Print a brief help message and exits.
  83
  84 =item B<--man>
  85
  86 Prints the manual page and exits.
  87
  88 =item B<-v>
  89
  90 Verbose. Without this flag set, only fatal errors are reported.
  91
  92 =item B<--format>
  93
  94 Current options are text, html, csv, and tsv. At the moment, text and tsv both produce tab-separated tab-separated output.
  95
  96 =item B<--email>
  97
  98 Whether to use e-mail (implied by --to or --from).
  99
 100 =item B<--username>
 101
 102 Username to pass to the SMTP server for authentication
 103
 104 =item B<--password>
 105
 106 Password to pass to the SMTP server for authentication
 107
 108 =item B<--method>
 109
 110 Method is the type of authentication. Ie. LOGIN, DIGEST-MD5, etc.
 111
 112 =item B<--to>
 113
 114 E-mail address to send report to. Defaults to KohaAdminEmailAddress.
 115
 116 =item B<--from>
 117
 118 E-mail address to send report from. Defaults to KohaAdminEmailAddress.
 119
 120 =item B<--subject>
 121
 122 Subject for the e-mail message. Defaults to "Koha Saved Report"
 123
 124 =back
 125
 126 =head1 DESCRIPTION
 127
 128 This script is designed to run existing Saved Reports.
 129
 130 =head1 USAGE EXAMPLES
 131
 132 B<runreport.pl 16>
 133
 134 In the most basic form, runs the report specified by ID number from
 135 saved_sql.id, in this case #16, outputting the results to STDOUT.
 136
 137 B<runreport.pl 16 17>
 138
 139 Same as above, but also runs report #17.
 140
 141 =head1 TO DO
 142
 143 =over
 144
 145
 146 =item *
 147
 148 Allow Saved Results option.
 149
 150
 151 =back
 152
 153 =head1 SEE ALSO
 154
 155 Reports - Guided Reports
 156
 157 =cut
 158
 159 # These variables can be set by command line options,
 160 # initially set to default values.
 161
 162 my $help    = 0;
 163 my $man     = 0;
 164 my $verbose = 0;
 165 my $email   = 0;
 166 my $attachment = 0;
 167 my $format  = "text";
 168 my $to      = "";
 169 my $from    = "";
 170 my $subject = "";
 171 my $separator = ',';
 172 my $quote = '"';
 173
 174 my $username = undef;
 175 my $password = undef;
 176 my $method = 'LOGIN';
 177
 178 GetOptions(
 179     'help|?'            => \$help,
 180     'man'               => \$man,
 181     'verbose'           => \$verbose,
 182     'format=s'          => \$format,
 183     'to=s'              => \$to,
 184     'from=s'            => \$from,
 185     'subject=s'         => \$subject,
 186     'email'             => \$email,
 187     'a|attachment'      => \$attachment,
 188     'username:s'        => \$username,
 189     'password:s'        => \$password,
 190     'method:s'          => \$method,
 191
 192 ) or pod2usage(2);
 193 pod2usage( -verbose => 2 ) if ($man);
 194 pod2usage( -verbose => 2 ) if ($help and $verbose);
 195 pod2usage(1) if $help;
 196
 197 cronlogaction();
 198
 199 unless ($format) {
 200     $verbose and print STDERR "No format specified, assuming 'text'\n";
 201     $format = 'text';
 202 }
 203
 204 if ($format eq 'tsv' || $format eq 'text') {
 205     $format = 'csv';
 206     $separator = "\t";
 207 }
 208
 209 if ($to or $from or $email) {
 210     $email = 1;
 211     $from or $from = C4::Context->preference('KohaAdminEmailAddress');
 212     $to   or $to   = C4::Context->preference('KohaAdminEmailAddress');
 213 }
 214
 215 unless (scalar(@ARGV)) {
 216     print STDERR "ERROR: No reportID(s) specified\n";
 217     pod2usage(1);
 218 }
 219 ($verbose) and print scalar(@ARGV), " argument(s) after options: " . join(" ", @ARGV) . "\n";
 220
 221 my $today = dt_from_string();
 222 my $date = $today->ymd();
 223
 224 foreach my $report_id (@ARGV) {
 225     my $report = get_saved_report($report_id);
 226     unless ($report) {
 227         warn "ERROR: No saved report $report_id found";
 228         next;
 229     }
 230     my $sql         = $report->{savedsql};
 231     my $report_name = $report->{report_name};
 232     my $type        = $report->{type};
 233
 234     $verbose and print "SQL: $sql\n\n";
 235     if ( $subject eq "" )
 236     {
 237         if ( defined($report_name) and $report_name ne "")
 238         {
 239             $subject = $report_name ;
 240         }
 241         else
 242         {
 243             $subject = 'Koha Saved Report';
 244         }
 245     }
 246     # my $results = execute_query($sql, undef, 0, 99999, $format, $report_id);
 247     my ($sth) = execute_query($sql);
 248     # execute_query(sql, , 0, 20, , )
 249     my $count = scalar($sth->rows);
 250     unless ($count) {
 251         print "NO OUTPUT: 0 results from execute_query\n";
 252         next;
 253     }
 254     $verbose and print "$count results from execute_query\n";
 255
 256     my $message;
 257     if ($format eq 'html') {
 258         my $cgi = CGI->new();
 259         my @rows = ();
 260         while (my $line = $sth->fetchrow_arrayref) {
 261             foreach (@$line) { defined($_) or $_ = ''; }    # catch undef values, replace w/ ''
 262             push @rows, $cgi->TR( join('', $cgi->td($line)) ) . "\n";
 263         }
 264         $message = $cgi->table(join "", @rows);
 265     } elsif ($format eq 'csv') {
 266         my $csv = Text::CSV_XS->new({
 267             quote_char  => $quote,
 268             sep_char    => $separator,
 269             });
 270         while (my $line = $sth->fetchrow_arrayref) {
 271             $csv->combine(@$line);
 272 #            foreach (@$line) {
 273 #                defined($_) or $_ = '';
 274 #                $_ =~ s/$quote/\\$quote/g;
 275 #                $_ = "$quote$_$quote";
 276 #            }    # catch undef values, replace w/ ''
 277 #            $message .= join ($separator, @$line) . "\n";
 278             $message .= $csv->string() . "\n";
 279         }
 280     }
 281
 282     if ($email) {
 283         my $args = { to => $to, from => $from, subject => $subject };
 284         if ( $format eq 'html' ) {
 285             $message = "<html><head><style>tr:nth-child(2n+1) { background-color: #ccc;}</style></head><body>$message</body></html>";
 286             $args->{contenttype} = 'text/html';
 287         }
 288         my $email = Koha::Email->new();
 289         my %mail  = $email->create_message_headers($args);
 290         $mail{Data} = $message;
 291         $mail{Auth} = { user => $username, pass => $password, method => $method } if $username;
 292
 293         my $msg = MIME::Lite->new(%mail);
 294
 295         $msg->attach(
 296             Type        => "text/$format",
 297             Data        => encode( 'utf8', $message ),
 298             Filename    => "report$report_id-$date.$format",
 299             Disposition => 'attachment',
 300         ) if $attachment;
 301
 302         $msg->send();
 303         carp "Mail not sent" unless $msg->last_send_successful();
 304     }
 305     else {
 306         print $message;
 307     }
 308 }