#!/usr/bin/perl
use strict;
use IO::Scalar;
use DBI;
use CGI;
use Spreadsheet::WriteExcel;

use constant C_HEADING => 0;
use constant C_WIDTH   => 1;
my @columns = (
    	['USUARIO', 32],
    	['EN_PROGRESO', 10],
    	['DEVOLUCION', 10],
    	['RESUELTO', 10],
	['CLIENTE_NOTIFICADO', 10],
	['COMPLETADO',10],
	['TOTAL', 10],
);


my $dbh = DBI->connect("dbi:mysql:dbname=otrs;host=localhost", "root", "12345") ||
die "Can't connect to database.\n";

#$cgi = new CGI;
#print $cgi->header("application/excel");



my $name = "ticketmonitor_rpt.xls";
print "Content-disposition: attachment; filename=$name\n";
print "Content-type: application/vnd.ms-excel\n\n";




#====================================================
#====================================================
#my $xls_str;
#tie *XLS, 'IO::Scalar', \$xls_str;

#my $workbook = Spreadsheet::WriteExcel->new(\*XLS);
my $workbook = Spreadsheet::WriteExcel->new("-");
my $worksheet =    $workbook->add_worksheet("Datos");



my $default_format  = $workbook->add_format(num_format => '@'); $default_format->set_font('Verdana'); $default_format->set_border(1);
my $bold_format     = $workbook->add_format(); $bold_format->set_font('Verdana'); $bold_format->set_bold(); $bold_format->set_border(1);

$worksheet->write(0,$_,$columns[$_]->[C_HEADING], $bold_format) for (0..$#columns);
$worksheet->set_column($_, $_, $columns[$_]->[C_WIDTH]) for (0..$#columns);
$worksheet->autofilter('A1:M1');


#$worksheet->write(13, 3,  "Hi Excel!");


my $sth = $dbh->prepare("SELECT  CONCAT(u.FIRST_NAME, ' ',u.LAST_NAME) AS Usuario  ,  SUM(En_Progreso) AS 'En_Progreso' ,SUM(Devolucion) AS 'Devolucion', SUM(Resuelto) AS 'Resuelto'
,SUM(Cliente_notificado) AS 'Cliente_notificado' , SUM(Completado) AS 'Completado', SUM(TOTAL) AS 'TOTAL'
FROM (
SELECT  t.responsible_user_id AS Usuario , COUNT(TICKET_STATE_ID ) AS 'En_Progreso'   ,0 AS 'Devolucion', 0  AS 'Resuelto'
,0 AS 'Cliente_notificado',0 AS 'Completado', 0 AS 'TOTAL'
 FROM ticket t
WHERE t.TICKET_STATE_ID = 17 AND customer_id = 'crediq' GROUP BY t.responsible_user_id
UNION
SELECT t.responsible_user_id AS Usuario , 0 AS 'En_Progreso' ,COUNT(TICKET_STATE_ID ) AS 'Devolucion', 0  AS 'Resuelto'
,0 AS 'Cliente_notificado',0 AS 'Completado', 0 AS 'TOTAL'
 FROM ticket t
WHERE  t.TICKET_STATE_ID = 18 AND customer_id = 'crediq'  GROUP BY t.responsible_user_id
UNION
SELECT t.responsible_user_id AS Usuario , 0 AS 'En_Progreso'  ,0 AS 'Devolucion', COUNT(TICKET_STATE_ID )  AS 'Resuelto'
,0 AS 'Cliente_notificado',0 AS 'Completado', 0 AS 'TOTAL'
 FROM ticket t
WHERE   t.TICKET_STATE_ID = 16 AND customer_id = 'crediq' GROUP BY t.responsible_user_id
UNION
SELECT t.responsible_user_id AS Usuario , 0 AS 'En_Progreso' , 0 AS 'Devolucion', 0 AS 'Resuelto'
,COUNT(TICKET_STATE_ID ) AS 'Cliente_notificado',0 AS 'Completado', 0 AS 'TOTAL'
 FROM ticket t
WHERE t.TICKET_STATE_ID = 20 AND  customer_id = 'crediq' GROUP BY t.responsible_user_id
UNION
SELECT  t.responsible_user_id AS Usuario , 0 AS 'En Progreso'  , 0 AS 'Devolucion', 0  AS 'Resuelto'
,0 AS 'Cliente_notificado',COUNT(TICKET_STATE_ID ) AS 'Completado', 0 AS 'TOTAL'
 FROM ticket t
WHERE  t.TICKET_STATE_ID = 19 AND  customer_id = 'crediq' GROUP BY t.responsible_user_id
UNION
SELECT  t.responsible_user_id AS Usuario , 0 AS 'En_Progreso' ,  0 AS 'Devolucion', 0  AS 'Resuelto'
,0 AS 'Cliente_notificado',0 AS 'Completado', COUNT(TICKET_STATE_ID )  AS 'TOTAL'
 FROM ticket t
WHERE  customer_id = 'crediq'  AND t.TICKET_STATE_ID IN (17,18,16,19,20)  GROUP BY t.responsible_user_id
)z JOIN users u on u.ID = z.Usuario GROUP BY z.Usuario  order by z.Usuario ");
$sth->execute();
my $i = 1;
my $row;
while ( $row = $sth->fetchrow_arrayref ) {
    $worksheet->write_string($i,$_,$row->[$_], $default_format) for (0..$#$row);
    $i++;
}
$worksheet->activate();
$sth->finish();
$dbh->disconnect();

$workbook->close();
#====================================================