#-----------------------------------------------------------------#
#--                                                             --#
#--
#--
#--
#-----------------------------------------------------------------

#!/usr/bin/perl
#use strict;
#use warnings;

use DBI;
use Excel::Writer::XLSX;
use POSIX qw(strftime);


#-variables
$today = "2016-10-18";
$yesterday = "2016-10-18";
$tomorrow = "";

#-database parameters
$hostname = "localhost";
$username = "root";
$password = "Sjo*z800";
$database ="asterisk";

$dbh = DBI->connect("DBI:mysql:$database:$hostname", $username, $password)
	or die "Unable to connect to MySQL Server: $DBI::errstr\n";


$workbook  = Excel::Writer::XLSX->new( '/home/laguilar/HHRR_v3.xlsx' );
$worksheet = $workbook->add_worksheet();

#-common formats
$titles = $workbook->add_format(
	border  => 0,
	bold    => 1,
	size	=> 10,
	valign  => 'vcenter',
        align   => 'center',);
$headers = $workbook->add_format(
	border => 0,
	bold   => 1,
	size   => 9,
	valign => 'vcenter',
	align  => 'center',);
$cells = $workbook->add_format(
        border => 0,
        bold   => 0,
        size   => 9,
        valign => 'vcenter',
        align  => 'center',);
$cellsr = $workbook->add_format(
        border => 0,
        bold   => 0,
        size   => 9,
        valign => 'vcenter',
        align  => 'rigth',);
$cellsl = $workbook->add_format(
        border => 0,
        bold   => 0,
        size   => 9,
        valign => 'vcenter',
        align  => 'left',);



#-set headers
$worksheet->merge_range("B2:C2", "AGENT INFO", $titles);
$worksheet->write("B3", "CODE", $headers);
$worksheet->write("C3", "NAME", $headers);
$worksheet->set_column('B3:C3', 15);

$worksheet->merge_range("E2:H2", "SCHEDULE INFO", $titles);
$worksheet->write("E3", "LOG-IN", $headers);
$worksheet->write("F3", "LOG-OUT", $headers);
$worksheet->write("G3", "TYPE", $headers);
$worksheet->write("H3", "HOURS", $headers);
$worksheet->set_column('E3:F3', 20);

#$worksheet->merge_range("J2:O2", "TIME CONDITIONS", $titles);
#$worksheet->write("J3", "CALLS", $headers);
#$worksheet->write("K3", "TALK", $headers);
#$worksheet->write("L3", "WAIT", $headers);
#$worksheet->write("M3", "DISPO", $headers);
#$worksheet->write("N3", "DEAD", $headers);
#$worksheet->write("O3", "PAUSE", $headers);


#$worksheet->write("Q3", "LOGIN", $cells);
#$worksheet->write("R3", "", $cells);


#$query = "select distinct(pause_code) from vicidial_pause_codes order by pause_code asc;";

$query = "SELECT distinct(val.user),full_name FROM vicidial_agent_log val,vicidial_users vu WHERE event_time>='2016-10-18 00:00:00' AND event_time<='2016-10-18 23:59:59' AND val.user=vu.user AND val.user_group IN ('GDigital') ORDER BY vu.full_name ASC;";

$sqlQuery = $dbh->prepare($query)
	or die "Can't prepare MySQL Query $query: $dbh->errstr\n";

$rv = $sqlQuery->execute
	or die "Can't excecute the query: $sqlQuery->errstr\n";

$row = 3;
$agent = "";
while (@row= $sqlQuery->fetchrow_array()) {
	$agent=$row[0];
	$worksheet->write($row,1,"$row[0]",$cells);
	$worksheet->write($row,2, $row[1], $cellsl);

	#-login information
	$queryLI = $dbh->prepare("SELECT pause_epoch FROM vicidial_agent_log WHERE event_time>='$today 00:00:00' AND event_time<='$today 23:59:59' AND user='$agent' LIMIT 1;");
	$rsetLI = $queryLI->execute()	or die "Unable to execute sql: $queryLI->errstr";
	while (($login) = $queryLI->fetchrow_array()) {
		$login = strftime '%Y/%m/%d %H:%M:%S', localtime $login;
		$worksheet->write($row,4,$login,$cells);
	}

	#-logout information
	$queryLO = $dbh->prepare("SELECT pause_epoch,pause_sec,wait_sec,talk_sec,dispo_sec FROM vicidial_agent_log WHERE event_time>='$today 00:00:00' AND event_time<='$today 23:59:59' AND user='$agent' ORDER BY event_time DESC LIMIT 1;");
	$rsetLO = $queryLO->execute()  or die "Unable to execute sql: $queryLO->errstr";
        while (($logout) = $queryLO->fetchrow_array()) {
		$logout = strftime '%Y/%m/%d %H:%M:%S', localtime $logout;
                $worksheet->write($row,5,$logout,$cells);
        }

	#-schedule type
	$type = 'D';
	if ($logout > '$today 20:00:00' && $logout < 'tomorrow 00:59:59') {
		$type = 'M';
	}
	if($logout >= 'tomorrow 00:59:59') {
        	$type = 'N';
        }
	$worksheet->write($row,6,$type,$cells);

	#-working hours



	$row++;
}

$rv = $sqlQuery->finish;
#exit(0);


$workbook->close;