Last active
February 15, 2016 17:05
-
-
Save racsonp/25b73cd9dd923c423335 to your computer and use it in GitHub Desktop.
Cantida de ticket en relacion de Queue y Estado
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 = "QUEUES - ESTADOS - ".$datestring; | |
| my $linkActuliza = "http://192.168.56.102/reportes/queues.pl"; | |
| my $linkExcel = "http://192.168.56.102/reportes/queues_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 q.Name AS QUEUE , 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.queue_id AS queue_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.queue_id | |
| UNION | |
| SELECT t.queue_id AS queue_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.queue_id | |
| UNION | |
| SELECT t.queue_id AS queue_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.queue_id | |
| UNION | |
| SELECT t.queue_id AS queue_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.queue_id | |
| UNION | |
| SELECT t.queue_id AS queue_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.queue_id | |
| UNION | |
| SELECT t.queue_id AS queue_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.queue_id | |
| )z | |
| JOIN queue q on q.ID = z.queue_id GROUP BY z.queue_id order by q.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"; | |
| # PONER ANTES DE LA TABLA! print "<div class=\"CSSTableGenerator\"> \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