#!/usr/bin/perl

#use strict;
use Spreadsheet::WriteExcel;
use DBI;
use warnings;

## Global Variables

my $host = "localhost";
my $user = "user";
my $password = "password";
my $database = "customer";

##
# Create Excel Layout
##

my $workbook  = Spreadsheet::WriteExcel->new("firstreport.xls");
my $worksheet = $workbook->add_worksheet(WebFilter-Blocked);
my $worksheet1 = $workbook->add_worksheet(Traffic);
my $worksheet2 = $workbook->add_worksheet(Virus);
my $ipssheet = $workbook->add_worksheet(IPS);

my $alignc = $workbook->add_format(); #simple center only format
    $alignc->set_align('center');
    $alignc->set_align('vcenter');


$worksheet->write_string(0, 1, "Time");
$worksheet->write_string(0, 2, "Source");
$worksheet->write_string(0, 3, "Type");
$worksheet->write_string(0, 4, "Website");
$worksheet->write_string(0, 5, "Catergory");
$worksheet->write_string(0, 6, "Stauts");

$ipssheet->write_string(0, 1, "Time");
$ipssheet->write_string(0, 2, "Severity");
$ipssheet->write_string(0, 3, "Source");
$ipssheet->write_string(0, 4, "Destination");
$ipssheet->write_string(0, 5, "Reference");
$ipssheet->write_string(0, 6, "Attack Message");

##
# Database Part
##

my $dbh = DBI->connect("DBI:mysql:$database:$host", $user, $password);
my $sth= $dbh->prepare("SELECT itime,src,service,hostname,cat_desc,status FROM FG100A3907507179_webfilter WHERE `status` = 'blocked'");
my $attack_q= $dbh->prepare("SELECT itime,severity,src,dst,ref,msg  FROM FG100A3907507179_attacks");
$sth->execute();
$attack_q->execute();

#capture output into array and variablize the results

         while ( @row = $sth->fetchrow_array ) {
        ($time,$src,$service,$website,$cat_desc,$status) = @row;

# Write data into Webfilter Workbook

            my $workbook  = Spreadsheet::WriteExcel->new("firstreport.xls");

                $worksheet->write($row+2, 1, $time,$alignc);
                $worksheet->write($row+2, 2, $src,$alignc);
                $worksheet->write($row+2, 3, $service,$alignc);
                $worksheet->write_string($row+2, 4, $website,$alignc);
                $worksheet->write_string($row+2, 5, $cat_desc,$alignc);
                $worksheet->write_string($row+2, 6, $status,$alignc);
                $row++;
        }

while ( @attacks = $attack_q->fetchrow_array ) {
        my ($time,$severity,$src,$dst,$ref,$msg) = @attacks;

                $ipssheet->write($row+2, 1, $time,$alignc);
                $ipssheet->write($row+2, 2, $severiy,$alignc);
                $ipssheet->write($row+2, 3, $src,$alignc);
                $ipssheet->write_string($row+2, 4, $dst,$alignc);
                $ipssheet->write_string($row+2, 5, $ref,$alignc);
                $ipssheet->write_string($row+2, 6, $msg,$alignc);
                $row++;
                }