racsonp

#!/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 = (

racsonp

#<meta http-equiv="refresh" content="120">
print start_html(-title => 'Test',
                       -head  => meta({-http_equiv => 'refresh',
                                       -content => '10'}));
                                       
http://www.ebay.com/itm/Pioneer-A-10-S-Integrated-Amplifier-2x-50-Watt-Silver-GENUINE-NEW-/281859988605?hash=item41a02a1c7d:g:6iIAAOSwFqJWrSsw


---------- CONFIGURACION DE LINUX SERVER

racsonp

#!/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);

racsonp

#!/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);

racsonp

SELECT t.tn as 'Ticket',  nc.NombreCliente AS 'Nombre Cliente' , Prestamo As 'No. Prestamo',  tc.Telefono AS 'Telefono', ec.email  AS ' Correo Electronico'
FROM  ticket t
JOIN (
SELECT DISTINCT  t.ID , t.tn as 'Ticket',  f.VALUE_TEXT  AS 'NombreCliente'
FROM  ticket t
left outer JOIN  dynamic_field_value  f on f.field_id = 18 AND t.ID =  f.OBJECT_ID
WHERE t.CUSTOMER_ID = 'crediq' AND t.ticket_state_id = 19
) nc on nc.Id = t.Id
JOIN  (
SELECT DISTINCT  t.ID, t.tn ,  f.VALUE_TEXT  AS 'Telefono'

racsonp

---Historial de Ticket por cambio de owner (OwnerUpdate = 23 )
---Version con el calculo de tiempos corregidos 02 Feb. 2016


SELECT  
USUARIO  AS 'USUARIO', TN as 'TICKET'
,CASE  	WHEN TIEMPO < 59 THEN  CONCAT( CAST(TIEMPO AS CHAR) , ' Minutos') 
		WHEN TIEMPO BETWEEN  60 AND  1440  THEN CONCAT( CAST(ROUND((TIEMPO/60),1) AS CHAR) , '  Horas')  
		WHEN TIEMPO > 1440 THEN CONCAT( CAST( ROUND(((TIEMPO/60)/24),1) AS CHAR) , '  Dias')    
ELSE ''  END AS 'TIEMPO'

racsonp

--http://190.242.151.179/otrs/index.pl

SELECT  NumeroPrestamo , Numero, T AS 'Numero de Ticket', t.create_time AS 'Fecha Creación', t.Usuario AS 'Responsable', t.Estado AS 'Estado'
FROM 
(
SELECT  f.VALUE_TEXT  AS 'NumeroPrestamo' ,  COUNT(t.tn)  AS 'Numero' FROM  ticket t 
JOIN  dynamic_field_value  f on f.field_id = 17 AND t.ID =  f.OBJECT_ID
WHERE  t.create_time BETWEEN CAST('2016-01-09' AS DATE) AND CAST('2016-01-30' AS DATE)
AND CUSTOMER_ID = 'crediq' AND t.ticket_state_id <> 11
GROUP BY  f.VALUE_TEXT  

racsonp

SELECT y.Ticket, y.Servicio, y.Estado, y.Fecha, y.Tiempo, y.Queue , y.Propietario 
,CASE  WHEN y.estadoId = 19 THEN  (select a.A_BODY from article a where a.TICKET_ID =x.Tid  order  by a.CREATE_TIME desc LIMIT 1)
ELSE '' END AS 'Notas'
FROM
(
SELECT t.tn, s.name ,t.id as 'Tid', customer_id, t.ticket_state_id, 1 AS 'estadoId'
from ticket t 
JOIN ticket_state s on s.id = t.ticket_state_id
where t.ticket_state_id = 19 AND customer_id = 'crediq'
) x

racsonp

--Reporte OTRS: Consulta Usuarios-Ticket “Actuales” con el tiempo que llevan determinado estado y queue
--Filtro: OwnerUpdate = 23
--Nota: Para especificar el periodo en que se quiere la información se debe Modificar los valores "Fecha Desde" y "Fecha Hasta"

SELECT USUARIO  AS 'USUARIO', TN as 'TICKET'
,CASE  	WHEN TIEMPO < 59 THEN  CONCAT( CAST(TIEMPO AS CHAR) , ' Minutos') 
		WHEN TIEMPO BETWEEN  60 AND  1440  THEN CONCAT( CAST(ROUND((TIEMPO/60),1) AS CHAR) , '  Horas')  
		WHEN TIEMPO > 1441 THEN CONCAT( CAST( ROUND(((TIEMPO/60)/24),1) AS CHAR) , '  Dias')    
ELSE ''  END AS 'TIEMPO'
,ESTADO, QUEUE 

racsonp

--Reporte OTRS, Consulta Tiempos de tickets en un determinado estado
--Filtros: 27= StateUpdate  & 1 = NewTicket
--Nota: Para especificar el periodo en que se quiere la información se debe Modificar los valores "Fecha Desde" y "Fecha Hasta"


SELECT TN AS 'Ticket', service AS 'Servicio',Name AS 'Estado', CREATE_TIME AS 'Fecha'
,CASE  
WHEN TIEMPO < 59 THEN  CONCAT( CAST(TIEMPO AS CHAR) , ' Minutos' ) 
WHEN TIEMPO BETWEEN  60 AND  1440  THEN CONCAT( CAST(ROUND((TIEMPO/60),1) AS CHAR) , '  Horas' )  
WHEN TIEMPO > 1441 THEN CONCAT( CAST( ROUND(((TIEMPO/60)/24),1) AS CHAR) , '  Dias' )