Created
February 15, 2016 19:16
-
-
Save racsonp/10dada2ca469896500a6 to your computer and use it in GitHub Desktop.
Service - Estados OTRS
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 | |
use DBI; | |
use DBD::mysql; | |
use CGI qw(:standard); | |
use warnings; | |
my $host = "localhost"; | |
my $database = "otrs"; | |
my $port = 3306; | |
my $tablename = "user"; | |
my $user = "root"; | |
my $pw = "12345"; | |
use POSIX qw(strftime); | |
$datestring = strftime "%a %e %b %Y %r", localtime; | |
##$datestring = strftime "%a %e %b %Y %H:%M", localtime; | |
##my $date = strftime "%d-%m-%Y %H:%M", localtime; | |
my $titulo = "SERVICES - ESTADOS - ".$datestring; | |
my $linkActuliza = "http://192.168.56.102/reportes/service.pl"; | |
my $linkExcel = "http://192.168.56.102/reportes/service_rpt_xls.pl"; | |
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 s.Name AS SERVICE , 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.service_id AS service_id , 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.service_id | |
UNION | |
SELECT t.service_id AS service_id , 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.service_id | |
UNION | |
SELECT t.service_id AS service_id , 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.service_id | |
UNION | |
SELECT t.service_id AS service_id , 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.service_id | |
UNION | |
SELECT t.service_id AS service_id , 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.service_id | |
UNION | |
SELECT t.service_id AS service_id , 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.service_id | |
)z | |
JOIN service s on s.ID = z.service_id GROUP BY z.service_id order by s.Name"; | |
# 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 rel=\"stylesheet\" href=\"/css/submenu.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"; | |
print "<h3>$titulo</h3> \n"; | |
# ---------------------------------------------------------------------- | |
# ---------------------------------------------------------------------- | |
#print "<div class=\"submenu\"> \n"; | |
print "<nav id=\"main-menu\"> \n"; | |
print "<ul class=\"nav-bar\"> \n"; | |
print " <li class=\"nav-button-refresh\"><a href=$linkActuliza>REFRESH</a></li> \n"; | |
print " <li class=\"nav-button-excel\"><a href=$linkExcel >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 "</section> \n"; | |
#<!--////////////////////////////////////Footer--> | |
#print "<footer class=\"zerogrid\"> \n"; | |
#print "<div class=\"zerogrid\"> \n"; | |
#print "<div class=\"wrap-footer\"> \n"; | |
#print "<div class=\"row\"> \n"; | |
#print "<div class=\"col-1-2\"> \n"; | |
#print "<div class=\"wrap-col\"> \n"; | |
#print "<span>Admin: rgomez@gbm.net</span><br/> \n"; | |
#print "</div> \n"; | |
#print " </div>\n"; | |
#print "</div> \n"; | |
#print "</div> \n"; | |
#print "</div> \n"; | |
#print "</footer> \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