#!/usr/bin/perl

use DBI;
use Spreadsheet::ParseExcel;
use Spreadsheet::ParseExcel::SaveParser;
#use Spreadsheet::Read;

$date=$ARGV[0];#yyyy-mm-dd
$month=$ARGV[1];#August
$date or $date=`date --date='1 day ago' +%Y-%m-%d`;
$month or $month=`date --date='1 day ago' +%B`;
chomp $month;
chomp $date;
print "Month:$month\n";


$Database_name="Busy_Hour_Report";
$IP_address="xxx.xx.xx.xxx";

$command = "cp /root/prac/packages/B_Mum_Busy_hour_report_fdb.xls /root/prac/packages/B_Mum_Busy_hour_report_fdb_$date.xls";
print $command;
`$command`;


my $dbh = DBI->connect("DBI:mysql:database=$Database_name;host=$IP_address;mysql_socket=/opt/lampstack-5.5.27-0/mysql/tmp/mysql.sock","root","",      {'RaiseError' => 1});

#---------------------------------------------------------------------------------------------------------#
#---------------------------------------------------------------------------------------------------------#
sub parser_excel
{
        my $sql_comm=$_[0];
        my $sheet_no=$_[1];
        #Selecting the data to fetch
        my $sth = $dbh->prepare("$sql_comm");
        $sth->execute() or die $DBI::errstr;
        # Open an existing file with SaveParser
        my $parser   = Spreadsheet::ParseExcel::SaveParser->new();
        my $template = $parser->Parse("B_Mum_Busy_hour_report_fdb_$date.xls");

        # Get the first worksheet.
        my $sheet = $template->worksheet($sheet_no);
        my $row  = 5;
        my $col  = 1;

        while (my @row = $sth->fetchrow_array())
        {
                foreach my $value (@row)
                {
                        $sheet->AddCell( $row, $col, $value );
                        ++$col;
                }
                $row++;
                $col=1;
        }
        $template->SaveAs("B_Mum_Busy_hour_report_fdb_$date.xls");
}


parser_excel("Select * from Busy_Hour where monthname(Date)='$month' ORDER BY Date DESC",0);
parser_excel("Select * from Hourly_Data where monthname(Date)='$month' and Hour in (21,22,23) ORDER BY Date DESC",1);
parser_excel("Select * from Hourly_Data where Date='$date' order by Hour",2);