use CGI qw(:standard);
use CGI::Carp qw(fatalsToBrowser);
use CGI::Carp qw(warningsToBrowser);
use CGI::Pretty;
use Date::Manip;
use DBI;
use Net::SMTP;
use MIME::Lite;
use Spreadsheet::WriteExcel;
use strict;
use warnings;

my $results_ref;
my $boundary = 'aecar'.time;

# Get the form contents

my $HTML_GEN = new CGI;
        my $request_user =      $HTML_GEN -> param('request_user');
        my $raw_user    =       $HTML_GEN -> param('user');
        my $raw_date1   =       $HTML_GEN -> param('date1');
        my $raw_date2   =       $HTML_GEN -> param('date2');
        my $raw_time1   =       $HTML_GEN -> param('time1');
        my $raw_time2   =       $HTML_GEN -> param('time2');
        my $raw_search  =       $HTML_GEN -> param('search');
        my $flag        =       '0';

# Set MySQL variables:
        my $host        =       "db_server";
        my $database    =       "database";
        my $tablename   =       "table1";
        my $dbuser      =       "db_user";
        my $dbpw        =       "db_password";

# Lower case the username, search:
        $raw_user       =~      tr/A-Z/a-z/;
        $raw_search     =~      tr/A-Z/a-z/;

# Clean up username, search:
        $raw_search     =~      /^([\w.:\/]*)$/;
        my $search      =       $1;
        $raw_user       =~      /^([\w ]*)$/;
        my $user        =       $1;

# Add underscore if we were accidentally passed a space:
        $user =~ tr/ /_/;

# fix the dates to MySQL format:
        my $date1       =       UnixDate($raw_date1,"%Y-%m-%d");
        my $date2       =       UnixDate($raw_date2,"%Y-%m-%d");
        my $time1       =       UnixDate($raw_time1,"%H:%M:%S");
        my $time2       =       UnixDate($raw_time2,"%H:%M:%S");

# Sanity check on dates, times to keep them in order:
$flag = Date_Cmp($date1,$date2);
        if ($flag>0) {
                ($date1,$date2) = ($date2,$date1);
                ($time1,$time2) = ($time2,$time1);
        };

# At this point, all user input is sanitized to some level, right? A s
+mart user can probably still drop the db though.  :(

# MySQL connection:
my $db_use  = DBI->connect("DBI:mysql:database=$database;host=$host;po
+rt=3306", $dbuser, $dbpw) or die "Database connection not made: $DBI:
+:errstr";

# MySQL query syntax:

my $user_sql=qq(SELECT date, time, INET_NTOA(ip), url, bytes, authuser
+ FROM $tablename WHERE authuser = ? AND url LIKE ? AND date BETWEEN ?
+ and ? AND DATE_ADD(date, INTERVAL time HOUR_SECOND) BETWEEN CONCAT(?
+,' ',?) AND CONCAT(?,' ',?) ORDER BY date, time);
my $user_query= $db_use->prepare( $user_sql ) or die($db_use->errstr);

$user_query->execute($user, "%$search%", $date1, $date2, $date1, $time
+1, $date2, $time2) or die($user_query->errstr);

# Start an HTML table with column headers:
print $HTML_GEN->header();
print $HTML_GEN->start_html();
print $PROXY_GEN->p("An Excel spreadsheet with the requested informati
+on will be mailed to $request_user\@domain");
print $PROXY_GEN->end_html;

# Fill the spreadsheet data with fetchrow:

my $Workbook = Spreadsheet::WriteExcel -> new("/tmp/$user.xls");
my $Worksheet = $Workbook -> add_worksheet("$user");
my $cntr = 1;                                   #(start at 1 because 0
+ is the header row)

#Create the header row, one cell at a time:
$Worksheet -> write (0, 0, "Date");
$Worksheet -> write (0, 1, "Time");
$Worksheet -> write (0, 2, "IP Address");
$Worksheet -> write (0, 3, "URL");
$Worksheet -> write (0, 4, "Bytes");
$Worksheet -> write (0, 5, "User");

while (my @results = $user_query->fetchrow()) {
        $results_ref = \@results;               #Needed to avoid writi
+ng out individual elements
        $Worksheet -> write ($cntr, 0, $results_ref);
        $cntr++;
}
$Workbook -> close;

$db_use->disconnect;

my $message = MIME::Lite->new(
        From    => "$request_user\@domain",
        To      => "$request_user\@domain",
        Subject => "Report for $user",
        Type    => "TEXT",
        Data    => "Report for $user from $date1 $time1 to $date2 $tim
+e2.\n"
        );

$message->attach(
        Type    => "application/vnd.ms-excel",
        Path    => "/tmp/$user.xls",
        Filename => "$user.xls"
        );

$message->send('smtp', '199.58.55.173', Timeout=>60);
[download]