/* Vemos las operaciones que hay en el iqueue por procesar */
SELECT COUNT(id),mid FROM master_replica_iqueue GROUP BY mid;
 
/* La primera que devuelva la consulta es la que falla */
SELECT * FROM master_replica_iqueue WHERE mid='317' ORDER BY id ASC;
 
/* Ordenando descendentemente vemos los últimos "errores" que pueda tener el dispositivo. */
SELECT * FROM master_replica_errorlog WHERE mid='317' ORDER BY id DESC;

/* ********************************* */
/*           SELECTIVIDAD            */
/* ********************************* */
 
/* Vemos las operaciones que faltan por analizar la selectividad */
SELECT (SELECT MAX(id) FROM master_replica_queue)-lastcid AS pendientes,mid, dirip 
FROM master_replica_slave WHERE pendiente=0 ORDER BY pendientes DESC;

/* Estado de operaciones en un MYSQL con borrado de operaciones del SQUEUE */
SELECT sl.MID, cmd.CMDTIME AS 'ULTIMA_CONEXION', sl.SINCRONISMO, 
(SELECT TIMESTAMP FROM master_replica_queue WHERE mid=sl.mid ORDER BY id DESC LIMIT 1) AS 'ULTIMA_OPER_DESDE_DISPOSITIVO', 
x.num AS 'OPER_PEND_DESCARGA',sl.Batchsize FROM master_replica_slave sl 
LEFT JOIN master_replica_cmdlog cmd ON sl.MID=cmd.MID 
LEFT JOIN (SELECT COUNT(*) AS num,mid FROM master_replica_squeue 
GROUP BY mid) x ON sl.MID=x.MID WHERE sl.pendiente=0 AND sl.MID>1 
ORDER BY x.num DESC;
 
 
/* Estado de operaciones en un SQLSERVER si se van borrando las operaciones del SQUEUE cuando los dispositivos replican */
SELECT sl.MID, cmd.CMDTIME AS 'ULTIMA_CONEXION', sl.SINCRONISMO, 
(SELECT top 1 TIMESTAMP FROM master_replica_queue WHERE mid=sl.mid ORDER BY id DESC) AS 'ULTIMA_OPER_DESDE_DISPOSITIVO', 
x.num AS 'OPER_PEND_DESCARGA',sl.Batchsize FROM master_replica_slave sl 
LEFT JOIN master_replica_cmdlog cmd ON sl.MID=cmd.MID 
LEFT JOIN (SELECT COUNT(*) AS num,mid FROM master_replica_squeue GROUP BY mid) x ON sl.MID=x.MID WHERE sl.pendiente=0 AND sl.MID>1 ORDER BY x.num DESC;
 
 
/* Estado de operaciones en un SQLSERVER SIN borrado de operaciones del SQUEUE (cuando replican se pone el SENT=1) */
SELECT sl.MID, cmd.CMDTIME AS 'ULTIMA_CONEXION', sl.SINCRONISMO, 
(SELECT top 1 TIMESTAMP FROM master_replica_queue WHERE mid=sl.mid ORDER BY id DESC) AS 'ULTIMA_OPER_DESDE_DISPOSITIVO', 
x.num AS 'OPER_PEND_DESCARGA',sl.Batchsize FROM master_replica_slave sl 
LEFT JOIN master_replica_cmdlog cmd ON sl.MID=cmd.MID 
LEFT JOIN (SELECT COUNT(*) AS num,mid FROM master_replica_squeue 
WHERE sent IS NULL OR sent=0 GROUP BY mid) x ON sl.MID=x.MID WHERE sl.pendiente=0 AND sl.MID>1 ORDER BY x.num DESC;

/* Eliminar operaciones de log */
 
DELETE FROM adm_xonelive_logs WHERE STR_TO_DATE(TIMESTAMP, '%d/%m/%Y')<=DATE_SUB(CURDATE(),INTERVAL 15 DAY);  -- En ORACLE
DELETE FROM adm_xonelive_errorlog WHERE STR_TO_DATE(TIMESTAMP, '%d/%m/%Y')<=DATE_SUB(CURDATE(),INTERVAL 15 DAY);
 
DELETE FROM adm_provisioning_task WHERE STATUS=2 AND STR_TO_DATE(started, '%d/%m/%Y')<=DATE_SUB(CURDATE(),INTERVAL 15 DAY);
DELETE FROM adm_provisioning_logs WHERE STR_TO_DATE(logcreated, '%d/%m/%Y')<=DATE_SUB(CURDATE(),INTERVAL 15 DAY);
DELETE FROM interclient_error_log WHERE STR_TO_DATE(errortime, '%d/%m/%Y')<=DATE_SUB(CURDATE(),INTERVAL 15 DAY);
 
TRUNCATE TABLE master_replica_errorlog;
 
/* Ajustar al numero de operaciones diarias que tenga el proyecto */
INSERT INTO MASTER_REPLICA_QUEUE_HISTORY (ROWID,OPERID,TIMESTAMP,OPER,MID,CGSSQL,DMID,IDORIGINAL,TBL) 
SELECT ROWID,OPERID,TIMESTAMP,OPER,MID,CGSSQL,DMID,ID,TBL FROM MASTER_REPLICA_QUEUE WHERE  DMID IS NULL AND 
id NOT IN (SELECT QID FROM MASTER_REPLICA_SQUEUE) AND ID<(SELECT MIN(LASTCID) FROM MASTER_REPLICA_SLAVE WHERE CONDITIONAL=1 AND PENDIENTE=0);
 
DELETE FROM MASTER_REPLICA_QUEUE WHERE DMID IS NULL AND
ID NOT IN (SELECT QID FROM MASTER_REPLICA_SQUEUE) AND ID<(SELECT MIN(LASTCID) FROM MASTER_REPLICA_SLAVE WHERE CONDITIONAL=1 AND PENDIENTE=0);
 
/* Borramos las operaciones del SQUEUE de los dispositivos que estén desactivados */
DELETE FROM MASTER_REPLICA_SQUEUE WHERE MID IN (SELECT S.MID FROM MASTER_REPLICA_SLAVE S WHERE S.PENDIENTE=1);
 
/* Borramos del SQUEUE las operaciones que ya se hayan enviado a los dispositivos */
DELETE FROM MASTER_REPLICA_SQUEUE WHERE SENT=1;