Last active
February 13, 2016 20:50
-
-
Save racsonp/7fc2ea244ecb4384586e to your computer and use it in GitHub Desktop.
genera reporte EXCEL para asignaciones.pl
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/usr/bin/perl -w | |
# DBI is the standard database interface for Perl | |
# DBD is the Perl module that we use to connect to the <a href="http://mysql.com/" />MySQL</a> database | |
use DBI; | |
use DBD::mysql; | |
# we use CGI since this will be executed in a browser | |
use CGI qw(:standard); | |
use warnings; | |
my $host = "localhost"; | |
my $database = "otrs"; | |
my $port = 3306; | |
my $tablename = "user"; | |
my $user = "root"; | |
my $pw = "12345"; | |
my $titulo = "Asignaciones Usuario"; | |
my $dbh = DBI->connect("DBI:mysql:database=$database;host=$host;port=$port", | |
$user, $pw) | |
or die "Cannot connect to MySQL server: $DBI::errstr"; | |
my $consula = "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"; | |
# prepare SQL statement | |
my $sth = $dbh->prepare($consula) | |
or die "prepare statement failed: $dbh->errstr()"; | |
$sth->execute() or die "execution failed: $dbh->errstr()"; | |
# ---------------------------------------------------------------------- | |
# print the header | |
# ---------------------------------------------------------------------- | |
# ---------------------------------------------------------------------- | |
print "Content-Type: text/html; charset=ISO-8859-1\n"; | |
print "\n"; | |
print "\n"; | |
print "<!DOCTYPE html \n"; | |
print " PUBLIC \"-//W3C//DTD XHTML 1.0 Transitional//EN\" \n"; | |
print " \"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd\"> \n"; | |
print "<html xmlns=\"http://www.w3.org/1999/xhtml\" lang=\"en-US\" xml:lang=\"en-US\"> \n"; | |
print "<meta charset=\"utf-8\"> \n"; | |
print "<title>Reporte OTRS</title> \n"; | |
print "<meta name=\"viewport\" content=\"width=device-width, initial-scale=1, maximum-scale=1\"> \n"; | |
print "<link rel=\"stylesheet\" href=\"/css/zerogrid.css\"> \n"; | |
print "<link rel=\"stylesheet\" href=\"/css/style.css\"> \n"; | |
print "<link rel=\"stylesheet\" href=\"/css/lightbox.css\"> \n"; | |
print "<link rel=\"stylesheet\" href=\"/css/tablas.css\"> \n"; | |
print "<link href=\"/font-awesome/css/font-awesome.min.css\" rel=\"stylesheet\" type=\"text/css\"> \n"; | |
print "<link rel=\"stylesheet\" href=\"/css/menu.css\"> \n"; | |
print "<script src=\"/js/jquery1111.min.js\" type=\"text/javascript\"></script> \n"; | |
print "<script src=\"/js/script.js\"></script> \n"; | |
print "</head> \n"; | |
print "<body> \n"; | |
#<!--////////////////////////////////////Header--> | |
print "<div class=\"wrap-body\"> \n"; | |
print "<header class=\"zerogrid\"> \n"; | |
print "<div class=\"logo\"><img src=\"/images/logo3.png\" alt=\"\"/></div> \n"; | |
print "<div id='cssmenu' class=\"align-center\"> \n"; | |
print "<ul> \n"; | |
print "<li><a href='/index.html'><span>Home</span></a></li> \n"; | |
print "</ul> \n"; | |
print "</div> \n"; | |
print "</header> \n"; | |
print " \n"; | |
print " \n"; | |
#<!--////////////////////////////////////Container--> | |
print "<section id=\"container\"> \n"; | |
print "<div class=\"wrap-container\"> \n"; | |
print "<div id=\"main-container\" class=\"zerogrid\"> \n"; | |
# PONER ANTES DE LA TABLA! print "<div class=\"CSSTableGenerator\"> \n"; | |
print "<h3>$titulo</h3> \n"; | |
# ---------------------------------------------------------------------- | |
# ---------------------------------------------------------------------- | |
#print "<div class=\"CSSTableGenerator\"> \n"; | |
print "<nav id=\"main-menu\"> \n"; | |
print "<ul class=\"nav-bar\"> \n"; | |
print " <li class=\"nav-button-home\"><a href=\"http://192.168.56.102/reportes/ticketmonitor.pl\">REFRESH</a></li> \n"; | |
print " <li class=\"nav-button-excel\"><a href=\"http://192.168.56.102/reportes/ticketmonitor_rpt_xls.pl\">GENERAR EXCEL</a></li> \n"; | |
print " </ul>\n"; | |
print " </nav>\n"; | |
#print "<A HREF=\"http://192.168.56.102/reportes/asignaciones.pl\"> REFRESH </A>\n"; | |
#print "<br/>\n"; | |
#print "<A HREF=\"http://192.168.56.102/reportes/asignaciones_rpt_xls.pl\"> GENERAR EXCEL </A>\n"; | |
#print "</div> \n"; | |
# HTML for the beginning of the table | |
# we are putting a border around the table for effect | |
print "<div class=\"CSSTableGenerator\"> \n"; | |
print "<table > \n"; | |
# print your table column headers | |
# print your table column headers | |
print "<tr><td>Nombre</td><td>En Progreso</td><td>Devolucion</td><td>Resuelto</td><td>Cliente Notificado</td><td>Completado</td><td>Total</td></tr>\n"; | |
# retrieve the values returned from executing your SQL statement | |
while (@data = $sth->fetchrow_array()) { | |
$name_first = $data[0]; | |
$name_last = $data[1]; | |
$column3 = $data[2]; | |
$column4 = $data[3]; | |
$column5 = $data[4]; | |
$column6 = $data[5]; | |
$column7 = $data[6]; | |
# print your table rows | |
print "<tr><td>$name_first</td><td>$name_last</td><td>$column3</td><td>$column4</td><td>$column5</td><td>$column6</td><td>$column7</td></tr>\n"; | |
} | |
$sth->finish(); | |
$dbh->disconnect(); | |
# ---------------------------------------------------------------------- | |
print "</div> \n"; | |
print "</div> \n"; | |
print "</div> \n"; | |
print "</section> \n"; | |
print "</body> \n"; | |
print "</html> \n"; | |
# ---------------------------------------------------------------------- | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment