Feral Electronics SQL

Coljuegos.sql

-- Query Conteo
SELECT
  (SELECT count(1) FROM sch_pbl.maq_casino where estado = 'E') HAB, -- Habilitadas
  (SELECT count(1) FROM sch_pbl.maq_casino where estado = 'E' AND coljuego = 'E') HAB_ENV, -- Habilitadas enviado
  (SELECT count(1) FROM sch_pbl.maq_casino where estado = 'D' AND coljuego = 'E') DESH_ENV, -- Deshabilitadas Enviados
  (SELECT count(1) FROM sch_pbl.maq_casino where estado = 'E' AND numero_serie IS NULL ) N_SERIE_NULL, -- Numero de Serie NO NULL
  (SELECT count(1) FROM sch_pbl.maq_casino where estado = 'E' AND nuid IS NULL ) NUID_NULL, -- Nuid NO NULL
  (SELECT count(1) FROM sch_pbl.maq_casino where estado = 'E' AND porcentaje_teorico IS NULL ) Porcentaje_NULL, -- Porcentaje Teorico NO NULL
  (SELECT count(DISTINCT(cod_dgjcmt)) FROM sch_pbl.maq_casino where estado = 'E' ) NUC_DISTINTOS, -- Nuc que no se Repiten
  (SELECT count(1) FROM sch_pbl.maq_casino where estado = 'E' AND octet_length(cod_dgjcmt) >= 10 ) NUC_Mayor_10Char; -- Nuc mayores a 10 Dig

-- Query Basica
SELECT id_maq_casino, cod_maq_casino, cod_dgjcmt, numero_serie, nuid, porcentaje_teorico, coljuego  FROM sch_pbl.maq_casino where estado = 'E'


-- Query de Update del conteo basico
UPDATE sch_pbl.maq_casino SET coljuego ='E' WHERE estado = 'E' AND coljuego = 'D'
UPDATE sch_pbl.maq_casino SET coljuego = 'D' where estado = 'D' AND coljuego = 'E'
UPDATE sch_pbl.maq_casino SET nuid = '000000' WHERE estado = 'E' AND nuid IS NULL
UPDATE sch_pbl.maq_casino SET porcentaje_teorico = 90.00 WHERE estado = 'E' AND porcentaje_teorico IS NULL

SELECT * FROM sch_pbl.conexion_servidor WHERE estado = 'E' AND dbname = 'col-boyaca_fortuna_club'
UPDATE sch_pbl.conexion_servidor SET estado = 'E' WHERE dbname = 'col-col_ara_diversiones_lucky_bill'


----------------
-- DRAGON SLOT
----------------
SELECT * FROM sch_pbl.fun_get_reporte_diario_metl_x_fecha_2x(51, 'Wed Jul 05 00:00:00 COT 2017', 'USRMST')


-----------
-- INSUMAR
-----------
SELECT * FROM sch_pbl.fun_get_reporte_diario_metl_x_fecha_2x(24, 'Tue Jul 04 00:00:00 COT 2017', 'USRMST')

SELECT cs.*, s.*, e.*, g.*
		FROM
			sch_pbl.conexion_servidor cs,sch_pbl.sala s,sch_pbl.empresa e,sch_pbl.grupo g
		WHERE
			cs.id_sala = s.id_sala AND s.id_empresa = e.id_empresa AND e.id_grupo = g.id_grupo AND cs.estado = 'E'
		   AND cs.id_servicio IN(1,3) AND g.id_grupo =24
		ORDER BY
			cs.nombre


----------
-- INSUBER
----------
SELECT * FROM sch_pbl.fun_get_reporte_diario_metl(33,'USRMST')


----------------
-- COSTA CARIBE
----------------
SELECT * FROM sch_pbl.fun_get_reporte_diario_metl_x_fecha_2x(30, 'Sat Jul 01 00:00:00 COT 2017', 'USRMST')


---------------
-- HAWAI LISTO
---------------
SELECT * FROM sch_pbl.fun_get_reporte_diario_metl(41,'USRMST')

SELECT cs.*, cs.*, s.*, e.*, g.*
		FROM
			sch_pbl.conexion_servidor cs,sch_pbl.sala s,sch_pbl.empresa e,sch_pbl.grupo g
		WHERE
			cs.id_sala = s.id_sala AND s.id_empresa = e.id_empresa AND e.id_grupo = g.id_grupo AND cs.estado = 'E'
		   AND cs.id_servicio IN(1,3) AND g.id_grupo =41
		ORDER BY
			cs.nombre


---------------
-- Multijuegos
---------------
SELECT * FROM sch_pbl.fun_get_reporte_diario_metl_x_fecha_2x(28, 'Sat Jul 01 00:00:00 COT 2017', 'USRMST')

SELECT cs.* ,cs.*, s.*, e.*, g.*
		FROM
			sch_pbl.conexion_servidor cs,sch_pbl.sala s,sch_pbl.empresa e,sch_pbl.grupo g
		WHERE
			cs.id_sala = s.id_sala AND s.id_empresa = e.id_empresa AND e.id_grupo = g.id_grupo AND cs.estado = 'E'
		   AND cs.id_servicio IN(1,3) AND g.id_grupo =28
		ORDER BY
			cs.nombre


----------------------
-- Punto Cinco  LISTO
----------------------
SELECT * FROM sch_pbl.fun_get_reporte_diario_metl_x_fecha_2x(23, 'Wed Jul 05 00:00:00 COT 2017', 'USRMST')


-------------------------------
-- Inver Hnos Rodriguez  LISTO
-------------------------------
SELECT * FROM sch_pbl.fun_get_reporte_diario_metl_x_fecha_2x(16, 'Thu Jul 06 00:00:00 COT 2017', 'USRMST') 190.70.233.27

SELECT cs.* ,cs.*, s.*, e.*, g.*
		FROM
			sch_pbl.conexion_servidor cs,sch_pbl.sala s,sch_pbl.empresa e,sch_pbl.grupo g
		WHERE
			cs.id_sala = s.id_sala AND s.id_empresa = e.id_empresa AND e.id_grupo = g.id_grupo AND cs.estado = 'E'
		   AND cs.id_servicio IN(1,3) AND g.id_grupo =16
		ORDER BY
			cs.nombre
      

-----------------
--   Cortez
-----------------
SELECT * FROM sch_pbl.fun_get_reporte_diario_metl_x_fecha_2x(26, 'Tue Jul 04 00:00:00 COT 2017', 'USRMST')


--------------
-- INVERSIETE
--------------

SELECT * FROM sch_pbl.fun_get_reporte_diario_metl_x_fecha_2x(20, 'Tue Jul 04 00:00:00 COT 2017', 'USRMST');

SELECT cs.* ,cs.*, s.*, e.*, g.*
		FROM
			sch_pbl.conexion_servidor cs,sch_pbl.sala s,sch_pbl.empresa e,sch_pbl.grupo g
		WHERE
			cs.id_sala = s.id_sala AND s.id_empresa = e.id_empresa AND e.id_grupo = g.id_grupo AND cs.estado = 'E'
		   AND cs.id_servicio IN(1,3) AND g.id_grupo =20
		ORDER BY
			cs.nombre


-----------
-- Tayrona
----------- 
SELECT * FROM sch_pbl.fun_get_reporte_diario_metl_x_fecha_2x(42, 'Wed Jul 05 00:00:00 COT 2017', 'USRMST')

-- SELECT cs.* ,cs.*, s.*, e.*, g.*
-- 		FROM
-- 			sch_pbl.conexion_servidor cs,sch_pbl.sala s,sch_pbl.empresa e,sch_pbl.grupo g
-- 		WHERE
-- 			cs.id_sala = s.id_sala AND s.id_empresa = e.id_empresa AND e.id_grupo = g.id_grupo AND cs.estado = 'E'
-- 		   AND cs.id_servicio IN(1,3) AND g.id_grupo =42
-- 		ORDER BY
-- 			cs.nombre


-----------
-- Acertar Empresarial S.A.S
-----------

SELECT * FROM sch_pbl.fun_get_reporte_diario_metl_x_fecha_2x(71, 'Wed Jul 05 00:00:00 COT 2017', 'USRMST')

SELECT cs.* ,cs.*, s.*, e.*, g.*
		FROM
			sch_pbl.conexion_servidor cs,sch_pbl.sala s,sch_pbl.empresa e,sch_pbl.grupo g
		WHERE
			cs.id_sala = s.id_sala AND s.id_empresa = e.id_empresa AND e.id_grupo = g.id_grupo AND cs.estado = 'E'
		   AND cs.id_servicio IN(1,3) AND g.id_grupo =71
		ORDER BY
			cs.nombre


-------------------------
-- V.i.p Mundijuegos SAS
-------------------------

SELECT * FROM sch_pbl.fun_get_reporte_diario_metl_x_fecha_2x(37, 'Tue Jul 04 00:00:00 COT 2017', 'USRMST')


---------------------
-- Diversiones Farao
---------------------

SELECT * FROM sch_pbl.fun_get_reporte_diario_metl_x_fecha_2x(18, 'Wed Jul 05 00:00:00 COT 2017', 'USRMST')


---------------
-- Golden Game
---------------
SELECT * FROM sch_pbl.fun_get_reporte_diario_metl_x_fecha_2x(19, 'Tue Jul 04 00:00:00 COT 2017', 'USRMST')


----------------
-- Colombia Star
----------------
SELECT * FROM sch_pbl.fun_get_reporte_diario_metl_x_fecha_2x(64, 'Mon Jul 24 00:00:00 COT 2017', 'USRMST')


------------
-- Alianza
------------
SELECT * FROM sch_pbl.fun_get_reporte_diario_metl_x_fecha_2x(13, 'Sat Aug 19 00:00:00 COT 2017', 'USRMST')

SELECT cs.*
		FROM
			sch_pbl.conexion_servidor cs,sch_pbl.sala s,sch_pbl.empresa e,sch_pbl.grupo g
		WHERE
			cs.id_sala = s.id_sala AND s.id_empresa = e.id_empresa AND e.id_grupo = g.id_grupo AND cs.estado = 'E'
		   AND cs.id_servicio IN(1,3) AND g.id_grupo =13
		ORDER BY
			cs.nombre


--------
-- olas
--------
SELECT * FROM sch_pbl.fun_get_reporte_diario_metl_x_fecha_2x(15, 'Tue Aug 01 00:00:00 COT 2017', 'USRMST')


----------------------
-- Medios electrinicos
----------------------

Mincetur.sql

-- Query de buscar Maquinas
SELECT id_maq_casino, cod_maq_casino from sch_pbl.maq_casino where cod_maq_casino in (2625, 3407, 3408, 3409, 3418, 3419, 3420) AND estado = 'E' ORDER BY cod_maq_casino


-- Query con Modificacion de Tablas Javier Mendez
select
(select distinct(count(id_maq_casino)) from sch_pbl.maq_casino where estado = 'E') totales,
(select distinct(count(id_maq_casino)) from sch_pbl.maq_casino where estado = 'E' and fuera_de_servicio = 0 and fecha_salida_sala is null) activas,
(select distinct(count(id_maq_casino)) from sch_pbl.maq_casino_activas where fecha_operativa = '2017-06-08') maq_casino_activas, -- dia menos
(select distinct(count(id_maq_casino)) from sch_pbl.valor_param_mincetur where fecha_creacion::date = '2017-06-09') generadas,
(select distinct(count(id_maq_casino)) from sch_pbl.valor_param_mincetur where fecha_creacion::date = '2017-06-09' and envio_mincetur = 2) enviados,
(select distinct(count(id_maq_casino)) from sch_pbl.valor_param_mincetur where fecha_creacion::date = '2017-06-09' and envio_mincetur = 1) no_enviados;


-- Query Consultar Maquinas Activas y Contadores Generados, Enviados, No Enviados para una Fecha
SELECT
(select distinct(count(id_maq_casino)) from sch_pbl.maq_casino where estado = 'E' and fuera_de_servicio = 0 and fecha_salida_sala is null) activas,
(select distinct(count(id_maq_casino)) from sch_pbl.valor_param_mincetur where fecha_creacion::date = '2017-06-24') generadas,
(select distinct(count(id_maq_casino)) from sch_pbl.valor_param_mincetur where fecha_creacion::date = '2017-06-24' and envio_mincetur = 2) enviados,
(select distinct(count(id_maq_casino)) from sch_pbl.valor_param_mincetur where fecha_creacion::date = '2017-06-24' and envio_mincetur = 1) no_enviados;


-- Query Consultar Maquinas Activas y Contadores Generados, Enviados, No Enviados Wissar
SELECT
(select count(id_maq_casino) from sch_pbl.maq_casino where estado = 'E') TOTAL,
(select count(id_maq_casino) from sch_pbl.maq_casino where estado = 'E' and fuera_de_servicio = 0) ACTIVAS,
(SELECT COUNT(DISTINCT id_maq_casino)
FROM sch_pbl.valor_param_mincetur 
WHERE id_empresa = 1 
AND id_casino = 1 
AND fecha_creacion >= (case when extract (hour from current_timestamp) >= 8 then current_date + interval '8 hours'
else current_date - interval '1 day' + interval '8 hours' end)) GENERADOS,
(SELECT COUNT(DISTINCT id_maq_casino)  
FROM sch_pbl.valor_param_mincetur 
WHERE id_empresa = 1 
AND id_casino = 1 
AND fecha_creacion >= (case when extract (hour from current_timestamp) >= 8 then current_date + interval '8 hours' else current_date - interval '1 day' + interval '8 hours' end)
AND f_h_envio IS NOT NULL) ENVIADOS,
(select COUNT(DISTINCT cer.id_maq_casino) 
from sch_pbl.contadores_envio_remoto cer,
     sch_pbl.maq_casino mc
where cer.id_maq_casino not in (select id_maq_casino from sch_pbl.valor_param_mincetur where fecha_creacion >= (case when extract (hour from current_timestamp) >= 8 then current_date + interval '8 hours' else current_date - interval '1 day' + interval '8 hours' end)  and f_h_envio is not null)
and cer.id_maq_casino = mc.id_maq_casino
and mc.estado = 'E'
and mc.fecha_salida_sala is null
and fuera_de_servicio = 0) SIN_GENERAR,
(select count(id_maq_casino) from sch_pbl.maq_casino where estado = 'E' and fuera_de_servicio = 1) FUERA_DE_SERVICIO;


-- Generar registro a mincetur
SELECT * FROM sch_pwo.fun_ins_valor_param_mincetur()


-- Generar registro a mincetur otro dia
SELECT * FROM sch_pwo.fun_ins_valor_param_mincetur('2017-08-02 08:00:00'::timestamp without time zone)


-- Borrar registro a mincetur del dia pra regenerar
delete from sch_pbl.valor_param_mincetur where fecha_creacion::date = '2017-07-11' and f_h_medicion::date = '2017-07-11'


-- Verificar  y Cambiar estado de envios
select count(1) from sch_pbl.valor_param_mincetur where fecha_creacion::date = '2017-07-01' and envio_mincetur = 1
UPDATE sch_pbl.valor_param_mincetur SET envio_mincetur = 1 where fecha_creacion::date = '2017-07-01'

-- Error de duplicate key
UPDATE sch_pbl.valor_param_mincetur SET f_h_medicion = '2017-06-26 07:30:07' WHERE (id_empresa, id_casino, id_maq_casino, f_h_medicion)=(1, 1, 15, '2017-06-26 07:30:08')