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