<?php

require('../config/conexion.php');

if ($serviceName != 'All Services') {
    $condition = "AND queue = '" . $serviceName . "'";
} else {
    $condition = "AND queue IS NOT NULL";
}

$queryAgentsPerformance = "SELECT agent As Agent,
    COUNT(CASE WHEN verb IN('COMPLETEAGENT', 'COMPLETECALLER') THEN 1 ELSE NULL END) As Answered,
    COUNT(CASE WHEN verb = 'ABANDON' THEN 1 ELSE NULL END) As Abandon,
    SUM(data2) As TotalSec
    FROM asteriskcdrdb.cdr
    INNER JOIN aasterisk.queue_log as que on(que.call_id=cdr.uniqueid)
    WHERE agent != 'NONE'
    AND dstchannel LIKE 'Local/%'
    AND disposition = 'ANSWERED'
    AND lastapp = 'Queue'
    AND verb IN('COMPLETEAGENT', 'COMPLETECALLER', 'ABANDON')
    $condition
    AND FROM_UNIXTIME(time_id) BETWEEN '$dateStart $timeStart' AND '$dateEnd $timeEnd'
    GROUP BY agent
    WITH ROLLUP";

$queryAgentsPerformanceDetail = "SELECT agent As Agent,
    clid As CallerId,
    uniqueid,
    (CASE WHEN (verb = 'COMPLETEAGENT' OR verb = 'COMPLETECALLER') THEN DATE_FORMAT(DATE_SUB(FROM_UNIXTIME(time_id), INTERVAL data2 SECOND), '%Y-%m-%d %H:%i:%S')
        WHEN (verb = 'ABANDON') THEN DATE_SUB(FROM_UNIXTIME(time_id), INTERVAL 0 SECOND) 
         ELSE NULL END) As StartTime,
      FROM_UNIXTIME(time_id) As EndTime,
    (CASE WHEN (verb = 'COMPLETEAGENT' OR verb = 'COMPLETECALLER') THEN data2
    WHEN (verb = 'ABANDON') THEN '0'
    ELSE NULL END) As Duration,
    (CASE WHEN (verb = 'COMPLETEAGENT' OR verb = 'COMPLETECALLER') THEN data1
    WHEN (verb = 'ABANDON') THEN data3
    ELSE NULL END) As WaitTime,
    (CASE WHEN (verb = 'COMPLETEAGENT' OR verb = 'COMPLETECALLER') THEN 'Answered'
    WHEN (verb = 'ABANDON') THEN 'Abandon'
    ELSE verb END) As CallStatus,
    verb As CallCompleted
    FROM asteriskcdrdb.cdr
    INNER JOIN aasterisk.queue_log On(queue_log.call_id=cdr.uniqueid)
    WHERE agent != 'NONE'
    AND lastapp = 'Queue'
    AND verb IN('COMPLETEAGENT', 'COMPLETECALLER', 'ABANDON')
    $condition
    AND FROM_UNIXTIME(time_id) BETWEEN '$dateStart $timeStart' AND '$dateEnd $timeEnd'  
    GROUP BY call_id";

$queryOverallSlaAht = "SELECT accountcode As Area,
    SUM(data2) As TotalTalkTime,
    COUNT(DISTINCT(uniqueid)) As TotalCalls, 
    TRUNCATE((SUM(data2) - SUM(data1)) / COUNT(uniqueid),2)As AvgTalkTime,
    TRUNCATE(SUM(data2)/COUNT(uniqueid),2) As AHT
    FROM asteriskcdrdb.cdr
    INNER JOIN aasterisk.queue_log as que on(que.call_id=cdr.uniqueid)
    WHERE dstchannel LIKE 'Local/%'
    AND disposition = 'ANSWERED'
    AND lastapp = 'Queue'
    AND verb IN('COMPLETEAGENT', 'COMPLETECALLER')
    $condition
    AND FROM_UNIXTIME(time_id) BETWEEN '$dateStart $timeStart' AND '$dateEnd $timeEnd'
    GROUP BY Area";

$queryStatistics = "SELECT Date(FROM_UNIXTIME(time_id)) As Date,
    COUNT(DISTINCT(uniqueid)) As Offered,
    COUNT(DISTINCT CASE WHEN verb = 'ABANDON' AND data3 <= 40 THEN uniqueid ELSE NULL END) As AbndL40S,
    COUNT(DISTINCT CASE WHEN verb = 'ABANDON' AND data3 > 40 THEN uniqueid ELSE NULL END) As AbndG40S,
    COUNT(DISTINCT CASE WHEN verb = 'ABANDON' THEN uniqueid ELSE NULL END) As Abandon,
    COUNT(DISTINCT CASE WHEN verb IN('COMPLETEAGENT', 'COMPLETECALLER') AND data2 < 360 THEN uniqueid ELSE NULL END) As AnsL6M,
    COUNT(DISTINCT CASE WHEN verb IN('COMPLETEAGENT', 'COMPLETECALLER') AND data2 >= 360 THEN uniqueid ELSE NULL END) As AnsG6M,
    COUNT(DISTINCT CASE WHEN verb IN('COMPLETEAGENT', 'COMPLETECALLER') AND data1 <= 40 THEN uniqueid ELSE NULL END) As AnsL40S,
    COUNT(DISTINCT CASE WHEN verb IN('COMPLETEAGENT', 'COMPLETECALLER') AND data1 > 40 THEN uniqueid ELSE NULL END) As AnsG40S,
    COUNT(DISTINCT CASE WHEN verb IN('COMPLETEAGENT', 'COMPLETECALLER') THEN uniqueid ELSE NULL END) As Answered,
    COUNT(DISTINCT CASE WHEN accountcode LIKE 'NIV-%' THEN uniqueid ELSE NULL END) As NIV,
    COUNT(DISTINCT CASE WHEN accountcode LIKE 'NIV-%' THEN uniqueid ELSE NULL END)/COUNT(DISTINCT(uniqueid))'NIV%',
    COUNT(DISTINCT CASE WHEN accountcode LIKE 'IV-%' THEN uniqueid ELSE NULL END) As IV,
    COUNT(DISTINCT CASE WHEN accountcode LIKE 'IV-%' THEN uniqueid ELSE NULL END)/COUNT(DISTINCT(uniqueid))'IV%',
    TIME_FORMAT(SEC_TO_TIME(AVG(CASE WHEN accountcode LIKE 'NIV-%' THEN data2  ELSE '00:00:00' END)), '%H:%i:%S') As NIVAvg,
    TIME_FORMAT(SEC_TO_TIME(AVG(CASE WHEN accountcode LIKE 'IV-%' THEN data2  ELSE '00:00:00' END)), '%H:%i:%S') As IVAvg,
    TIME_FORMAT((SEC_TO_TIME(AVG(data2))),'%H:%i:%S') As Avg
    FROM asteriskcdrdb.cdr
    INNER JOIN aasterisk.queue_log On(queue_log.call_id=cdr.uniqueid)
    WHERE lastapp = 'Queue'
    AND verb IN('COMPLETEAGENT', 'COMPLETECALLER', 'ABANDON')
    $condition
    AND FROM_UNIXTIME(time_id) BETWEEN '$dateStart $timeStart' AND '$dateEnd $timeEnd'
    GROUP BY Date
    WITH ROLLUP";

$queryStatistics1 = "SELECT type,
    Date(FROM_UNIXTIME(time_id)) As Date,
    COUNT(DISTINCT(uniqueid)) As Offered,
    COUNT(DISTINCT CASE WHEN verb = 'ABANDON' AND data3 <= 40 THEN uniqueid ELSE NULL END) As AbndL40S,
    COUNT(DISTINCT CASE WHEN verb = 'ABANDON' AND data3 > 40 THEN uniqueid ELSE NULL END) As AbndG40S,
    COUNT(DISTINCT CASE WHEN verb = 'ABANDON' THEN uniqueid ELSE NULL END) As Abandon,
    COUNT(DISTINCT CASE WHEN verb IN('COMPLETEAGENT', 'COMPLETECALLER') AND data2 < 360 THEN uniqueid ELSE NULL END) As AnsL6M,
    COUNT(DISTINCT CASE WHEN verb IN('COMPLETEAGENT', 'COMPLETECALLER') AND data2 >= 360 THEN uniqueid ELSE NULL END) As AnsG6M,
    COUNT(DISTINCT CASE WHEN verb IN('COMPLETEAGENT', 'COMPLETECALLER') AND data1 <= 40 THEN uniqueid ELSE NULL END) As AnsL40S,
    COUNT(DISTINCT CASE WHEN verb IN('COMPLETEAGENT', 'COMPLETECALLER') AND data1 > 40 THEN uniqueid ELSE NULL END) As AnsG40S,
    COUNT(DISTINCT CASE WHEN verb IN('COMPLETEAGENT', 'COMPLETECALLER') THEN uniqueid ELSE NULL END) As Answered,
    COUNT(DISTINCT CASE WHEN accountcode LIKE 'NIV-%' THEN uniqueid ELSE NULL END) As NIV,
    COUNT(DISTINCT CASE WHEN accountcode LIKE 'NIV-%' THEN uniqueid ELSE NULL END)/COUNT(DISTINCT(uniqueid))'NIV%',
    COUNT(DISTINCT CASE WHEN accountcode LIKE 'IV-%' THEN uniqueid ELSE NULL END) As IV,
    COUNT(DISTINCT CASE WHEN accountcode LIKE 'IV-%' THEN uniqueid ELSE NULL END)/COUNT(DISTINCT(uniqueid))'IV%',
    AVG(DISTINCT CASE WHEN accountcode LIKE 'NIV-%' THEN data2  ELSE '00:00:00' END) As NIVAvg,
    AVG(DISTINCT CASE WHEN accountcode LIKE 'IV-%' THEN data2  ELSE '00:00:00' END) As IVAvg,
    AVG(data2) As Avg
    FROM asteriskcdrdb.cdr
    INNER JOIN aasterisk.queue_log On(queue_log.call_id=cdr.uniqueid)
    WHERE lastapp = 'Queue'
    AND verb IN('COMPLETEAGENT', 'COMPLETECALLER', 'ABANDON')
    $condition
    AND FROM_UNIXTIME(time_id) BETWEEN '$dateStart $timeStart' AND '$dateEnd $timeEnd'
    GROUP BY Date"; 


$queryAnsweredAvgWT = "SELECT Date(FROM_UNIXTIME(time_id)) As Date,
    TIME_FORMAT((SEC_TO_TIME(AVG(data1))), '%H:%i:%S') As AnsAvgWT
    FROM asteriskcdrdb.cdr
    INNER JOIN aasterisk.queue_log On(queue_log.call_id=cdr.uniqueid)
    WHERE lastapp = 'Queue'
    AND verb IN('COMPLETEAGENT', 'COMPLETECALLER')
    $condition
    AND FROM_UNIXTIME(time_id) BETWEEN '$dateStart $timeStart' AND '$dateEnd $timeEnd'
    GROUP BY Date
    WITH ROLLUP";

$queryAbandonedAvgWT = "SELECT Date(FROM_UNIXTIME(time_id)) As Date,
    TIME_FORMAT((SEC_TO_TIME(AVG(data3))), '%H:%i:%S') As AbndAvgWT,
    IFNULL(TIME_FORMAT((SEC_TO_TIME(AVG(CASE WHEN data3 <= 40 THEN data3 ELSE NULL END))), '%H:%i:%S'), '00:00:00') As AbndAvgL40s,
    IFNULL(TIME_FORMAT((SEC_TO_TIME(AVG(CASE WHEN data3 > 40 THEN data3 ELSE NULL END))), '%H:%i:%S'), '00:00:00') As AnsAvgG40s
    FROM asteriskcdrdb.cdr
    INNER JOIN aasterisk.queue_log On(queue_log.call_id=cdr.uniqueid)
    WHERE lastapp = 'Queue'
    AND verb = 'ABANDON'
    $condition
    AND FROM_UNIXTIME(time_id) BETWEEN '$dateStart $timeStart' AND '$dateEnd $timeEnd'
    GROUP BY Date
    WITH ROLLUP";

$queryAnswered = "SELECT accountcode As Area,
    COUNT(DISTINCT(uniqueid)) As Answered
    FROM asteriskcdrdb.cdr
    INNER JOIN aasterisk.queue_log as que on(que.call_id=cdr.uniqueid)
    WHERE dstchannel LIKE 'Local/%'
    AND disposition = 'ANSWERED'
    AND lastapp = 'Queue'
    AND verb IN('COMPLETEAGENT', 'COMPLETECALLER')
    $condition
    AND calldate >= '$dateStart $timeStart' AND calldate <= '$dateEnd $timeEnd'
    GROUP BY Area
    WITH ROLLUP ";

$queryAbandoned = "SELECT accountcode As Area,
    COUNT(DISTINCT(uniqueid)) As Abandoned
    FROM asteriskcdrdb.cdr 
    INNER JOIN aasterisk.queue_log On(queue_log.call_id=cdr.uniqueid)
    WHERE lastapp = 'Queue'
    AND (disposition = 'ANSWERED' OR disposition='NO ANSWER')
    AND verb='ABANDON'
    $condition
    AND calldate >= '$dateStart $timeStart' AND calldate <= '$dateEnd $timeEnd'
    GROUP BY Area
    WITH ROLLUP";

$queryAnsweredIn60 = "SELECT accountcode As Area,
    COUNT(DISTINCT(uniqueid)) As AnsweredIn60
    FROM asteriskcdrdb.cdr as cdr
    INNER JOIN aasterisk.queue_log as que on(que.call_id=cdr.uniqueid)
    WHERE dstchannel LIKE 'Local/%'
    AND disposition = 'ANSWERED'
    AND lastapp = 'Queue'
    AND verb IN('COMPLETEAGENT', 'COMPLETECALLER')
    AND data1 <= 60
    $condition
    AND calldate >= '$dateStart $timeStart' AND calldate <= '$dateEnd $timeEnd'
    GROUP BY Area
    WITH ROLLUP";

$queryOffered = "SELECT accountcode As Area,
    COUNT(DISTINCT(uniqueid)) As Offered
    FROM asteriskcdrdb.cdr
    INNER JOIN aasterisk.queue_log On(queue_log.call_id=cdr.uniqueid)
    WHERE lastapp = 'Queue'
    AND verb IN('COMPLETEAGENT', 'COMPLETECALLER', 'ABANDON')
    $condition
    AND FROM_UNIXTIME(time_id) BETWEEN '$dateStart $timeStart' AND '$dateEnd $timeEnd'
    GROUP BY Area
    WITH ROLLUP";

$queryAnsweredDetail = "SELECT accountcode As Area,
    clid As CallerId,
    uniqueid, 
    DATE_FORMAT(DATE_SUB(FROM_UNIXTIME(time_id), INTERVAL data2 SECOND), '%Y-%m-%d %H:%i:%S') As StartTime,
    FROM_UNIXTIME(time_id) As EndTime,
    data2 As Duration
    FROM asteriskcdrdb.cdr 
    INNER JOIN aasterisk.queue_log On(queue_log.call_id=cdr.uniqueid)
    WHERE dstchannel LIKE 'Local/%'
    AND disposition = 'ANSWERED'
    AND lastapp = 'Queue'
    AND verb IN('COMPLETEAGENT', 'COMPLETECALLER')
    $condition
    AND calldate >= '$dateStart $timeStart' AND calldate <= '$dateEnd $timeEnd'
    GROUP BY uniqueid
    ORDER BY StartTime";

$queryAbandonedDetail = "SELECT accountcode As Area,
    clid As CallerId,
    uniqueid,
    DATE_SUB(FROM_UNIXTIME(time_id), INTERVAL 0 SECOND) As StartTime,
    FROM_UNIXTIME(time_id) As EndTime,
    0 As Duration,
    data3 As WaitTime
    FROM asteriskcdrdb.cdr 
    INNER JOIN aasterisk.queue_log On(queue_log.call_id=cdr.uniqueid)
    WHERE lastapp = 'Queue'
    AND (disposition = 'ANSWERED' OR disposition='NO ANSWER')
    AND verb='ABANDON'
    $condition
    AND calldate >= '$dateStart $timeStart' AND calldate <= '$dateEnd $timeEnd'
    GROUP BY uniqueid
    ORDER BY StartTime";

$queryOfferedDetail = "SELECT SUBSTRING_INDEX(lastdata,',',1) As Area,
    clid,
    SUBSTR(clid,2,6) As PIN, 
    SUBSTRING(SUBSTRING_INDEX(clid,'>',1),11,20) As PhoneNumber, 
    (CASE WHEN (verb = 'COMPLETEAGENT' OR verb = 'COMPLETECALLER') THEN DATE_SUB(FROM_UNIXTIME(time_id), INTERVAL data2 SECOND)
                    WHEN (verb = 'ABANDON') THEN DATE_SUB(FROM_UNIXTIME(time_id), INTERVAL 0 SECOND) 
                     ELSE NULL END) As StartTime,
                FROM_UNIXTIME(time_id) As EndTime,
    (CASE WHEN (verb = 'COMPLETEAGENT' OR verb = 'COMPLETECALLER') THEN data2
                    WHEN (verb = 'ABANDON') THEN '0' 
                     ELSE NULL END) As Duration,
    (CASE WHEN (verb = 'COMPLETEAGENT' OR verb = 'COMPLETECALLER') THEN data1
                    WHEN (verb = 'ABANDON') THEN data3 
                     ELSE NULL END) As WaitTime,
    (CASE WHEN (verb = 'COMPLETEAGENT' OR verb = 'COMPLETECALLER') THEN 'Answered' 
                    WHEN (verb = 'ABANDON') THEN 'Abandoned' 
                     ELSE verb END) As CallStatus,
    (CASE WHEN (verb = 'ABANDON') THEN src
                      WHEN (verb = 'COMPLETEAGENT') THEN agent
    ELSE verb END) As CallCompleted
    FROM asteriskcdrdb.cdr
    INNER JOIN aasterisk.queue_log On(queue_log.call_id=cdr.uniqueid)
    WHERE lastapp = 'Queue'
    $condition
    AND lastdata like '%Oracle%'
    AND verb IN('COMPLETEAGENT', 'COMPLETECALLER', 'ABANDON')
    AND FROM_UNIXTIME(time_id) BETWEEN '$dateStart $timeStart' AND '$dateEnd $timeEnd'
    GROUP BY uniqueid";

$queryAgentOfferedDetail="SELECT clid As CallerIdAgent,
    MarcacionesRapidas.Descripcion As Agent
    FROM asteriskcdrdb.cdr 
    INNER JOIN aasterisk.queue_log On(queue_log.call_id=cdr.uniqueid)
    INNER JOIN xeryus.MarcacionesRapidas on(MarcacionesRapidas.NumeroCorto = SUBSTR(agent,7, 6))
    WHERE lastapp = 'Queue'
    AND verb IN('COMPLETEAGENT', 'COMPLETECALLER', 'ABANDON')
    AND lastdata like '%Oracle%'
    AND FROM_UNIXTIME(time_id) BETWEEN '$dateStart $timeStart' AND '$dateEnd $timeEnd'
    GROUP BY uniqueid";

$queryAgentsPerformanceChart = "SELECT agent As Agent,
    COUNT(CASE WHEN verb IN('COMPLETEAGENT', 'COMPLETECALLER') THEN 1 ELSE NULL END) As Answered,
    COUNT(CASE WHEN verb = 'ABANDON' THEN 1 ELSE NULL END) As Abandon
    FROM asteriskcdrdb.cdr
    INNER JOIN aasterisk.queue_log as que on(que.call_id=cdr.uniqueid)
    WHERE agent != 'NONE'
    AND dstchannel LIKE 'Local/%'
    AND disposition = 'ANSWERED'
    AND lastapp = 'Queue'
    $condition
    AND verb IN('COMPLETEAGENT', 'COMPLETECALLER', 'ABANDON')
    AND FROM_UNIXTIME(time_id) BETWEEN '$dateStart $timeStart' AND '$dateEnd $timeEnd'
    GROUP BY agent";

$queryAnsweredChart = "SELECT accountcode As Area,
    COUNT(DISTINCT(uniqueid)) As Answered
    FROM asteriskcdrdb.cdr
    INNER JOIN aasterisk.queue_log as que on(que.call_id=cdr.uniqueid)
    WHERE dstchannel LIKE 'Local/%'
    AND disposition = 'ANSWERED'
    AND lastapp = 'Queue'
    AND verb IN('COMPLETEAGENT', 'COMPLETECALLER')
    $condition
    AND calldate >= '$dateStart $timeStart' AND calldate <= '$dateEnd $timeEnd'
    GROUP BY Area";

$queryAbandonedChart = "SELECT accountcode As Area1,
    COUNT(DISTINCT(uniqueid)) As Abandoned
    FROM asteriskcdrdb.cdr 
    INNER JOIN aasterisk.queue_log On(queue_log.call_id=cdr.uniqueid)
    WHERE lastapp = 'Queue'
    AND (disposition = 'ANSWERED' OR disposition='NO ANSWER')
    AND verb='ABANDON'
    $condition
    AND calldate >= '$dateStart $timeStart' AND calldate <= '$dateEnd $timeEnd'
    GROUP BY Area1";

$queryAnsweredChart60 = "SELECT accountcode As Area2,
    COUNT(DISTINCT(uniqueid)) As AnsweredIn60
    FROM asteriskcdrdb.cdr as cdr
    INNER JOIN aasterisk.queue_log as que on(que.call_id=cdr.uniqueid)
    WHERE dstchannel LIKE 'Local/%'
    AND disposition = 'ANSWERED'
    AND lastapp = 'Queue'
    AND verb IN('COMPLETEAGENT', 'COMPLETECALLER')
    AND data1 <= 60
    $condition
    AND calldate >= '$dateStart $timeStart' AND calldate <= '$dateEnd $timeEnd'
    GROUP BY Area2";

$queryOfferedChart = "SELECT accountcode As Area3,
    COUNT(DISTINCT(uniqueid)) As Offered
    FROM asteriskcdrdb.cdr
    INNER JOIN aasterisk.queue_log On(queue_log.call_id=cdr.uniqueid)
    WHERE lastapp = 'Queue'
    AND verb IN('COMPLETEAGENT', 'COMPLETECALLER', 'ABANDON')
    $condition
    AND FROM_UNIXTIME(time_id) BETWEEN '$dateStart $timeStart' AND '$dateEnd $timeEnd'
    GROUP BY Area3";

$queryBreakTimes = "SELECT DATE(ta.HoraInicio) AS Date,
    a.Alias AS Agent,
    SEC_TO_TIME(SUM(TIME_TO_SEC(Tiempo))) AS Time
    FROM 
        xeryus.TiemposAusencia ta
    JOIN 
        xeryus.Agentes a ON a.Numero = ta.Agente
    JOIN 
        xeryus.TiposAccionAgente aa ON ta.Permiso = aa.Id
    WHERE 
        ta.HoraInicio  BETWEEN '$dateStart $timeStart' AND '$dateEnd $timeEnd'
        GROUP BY Agent, Date
    ORDER BY Date, Agent"; 

$queryAgentBreaks = "SELECT 
DATE(ta.HoraInicio) AS Date,
a.Alias AS Agent,
CASE aa.Nombre
    WHEN 'BANO' THEN 'WC'
    WHEN 'COMIDA' THEN 'Comida'
    WHEN 'COMISION' THEN 'Comision'
    ELSE aa.Nombre
END AS Permission,
ta.HoraInicio AS StartTime,
ta.HoraTermino AS EndTime,
ta.Tiempo AS Time
FROM 
    xeryus.TiemposAusencia ta
JOIN 
    xeryus.Agentes a ON a.Numero = ta.Agente
JOIN 
    xeryus.TiposAccionAgente aa ON ta.Permiso = aa.Id
WHERE 
    ta.HoraInicio BETWEEN '$dateStart $timeStart' AND '$dateEnd $timeEnd'
ORDER BY 
    ta.HoraInicio";

$queryAgentTimes = "SELECT 
    DATE(FROM_UNIXTIME(time_id)) AS Date,
    a.Alias AS Agent,
    MIN(CASE WHEN verb = 'ADDMEMBER' THEN TIME(FROM_UNIXTIME(time_id)) END) AS StartShift,
    MAX(CASE WHEN verb = 'REMOVEMEMBER' THEN TIME(FROM_UNIXTIME(time_id)) END) AS EndShift,
    TIMEDIFF(
        MAX(CASE WHEN verb = 'REMOVEMEMBER' THEN FROM_UNIXTIME(time_id) END), 
        MIN(CASE WHEN verb = 'ADDMEMBER' THEN FROM_UNIXTIME(time_id) END)
    ) AS ConectionTime,
    c.Nombre AS Queue 
    FROM 
    aasterisk.queue_log AS ql
    JOIN 
    xeryus.Agentes AS a ON ql.agent = a.Nombre
    JOIN 
    xeryus.Colas AS c ON ql.queue = c.Nombre
    WHERE 
    verb IN ('ADDMEMBER', 'REMOVEMEMBER')
    AND FROM_UNIXTIME(time_id) BETWEEN '$dateStart $timeStart' AND '$dateEnd $timeEnd'
    GROUP BY 
    Agent, Date
    ORDER BY Date, Agent";

$queryAvgAttentionTime = "SELECT 
    DATE(c.timestamp) AS Date,
    a.Alias AS Agent,
    SEC_TO_TIME(CAST(AVG(c.callduration) AS UNSIGNED)) AS AvgTime,
    c.queue AS Queue
    FROM aasterisk.call_status c
    JOIN xeryus.Agentes a ON a.Nombre = c.agent
    JOIN xeryus.Colas co ON co.Nombre = c.queue
    WHERE c.status IN ('COMPLETECALLER', 'COMPLETEAGENT')
    AND timestamp BETWEEN '$dateStart $timeStart' AND '$dateEnd $timeEnd'
    GROUP BY Date, Agent, Queue";

$queryAvgAbsenceTime = "SELECT 
    DATE(ta.HoraInicio) AS Date, 
    a.Alias AS Agent, 
    SEC_TO_TIME(ROUND(AVG(TIME_TO_SEC(ta.Tiempo)))) AS AvgAbsenceTime 
    FROM xeryus.TiemposAusencia ta
    JOIN xeryus.Agentes a ON a.Numero = ta.Agente
    WHERE HoraInicio BETWEEN '$dateStart $timeStart' AND '$dateEnd $timeEnd'
    GROUP BY Date, Agent";

$queryOperationPanel2_old = "SELECT timestamp As Date,
    agentId As Agente,
    MarcacionesRapidas.Descripcion As Agent,
    MarcacionesRapidas.NumeroCorto As SpeedDial,
    queue As Area
    FROM xeryus.Agentes,
    xeryus.MarcacionesRapidas,
    xeryus.AgentesColas,
    xeryus.MarcacionesRapidasColas,
    aasterisk.agent_status
    INNER JOIN xeryus.Colas On (Colas.Nombre=agent_status.queue)
    WHERE (agentId=Agentes.Nombre Or substring_index(agentId, '/',-1) = MarcacionesRapidas.NumeroCorto)
    AND (AgentesColas.Cola=xeryus.Colas.Id Or MarcacionesRapidasColas.Cola=Colas.Id)
    AND Agentes.Numero=AgentesColas.Agente
    AND agentStatus='READY'
    AND pauseStatus='READY'
    AND Agentes.Asociado=1
    AND queue LIKE '%Oracle%'
    AND agentId LIKE 'Agent/%'
    GROUP BY Agent
    ORDER BY Date"; 

    $queryOperationPanel2 = "SELECT 
        agent_status.`timestamp` AS Date,
        MarcacionesRapidas.Descripcion AS Agent,
        Agentes.Password,
        Agentes.Rotacion
    FROM 
        aasterisk.agent_status
    INNER JOIN xeryus.MarcacionesRapidas
        ON SUBSTRING_INDEX(agent_status.agentId, '/', -1) = MarcacionesRapidas.NumeroCorto
    INNER JOIN xeryus.Agentes
        ON MarcacionesRapidas.Descripcion = Agentes.Alias
    WHERE 
        agent_status.agentStatus = 'READY'
        AND agent_status.pauseStatus = 'READY'
        AND Agentes.Asociado = 1
        AND Agentes.Rotacion IS NOT NULL
        AND agent_status.agentId LIKE 'Agent/%'
    GROUP BY 
        Agent
    ORDER BY 
        Date";
    
$queryAgentOperationPanel2 = "SELECT Nombre, Alias, Password, Rotacion FROM xeryus.Agentes";

$queryLogin = "SELECT Fecha, Agente, Accion, Estado, Rotacion
    FROM xeryus.AccionesAgente
    WHERE Accion=1
    AND Estado=1
    AND Agente='$pass'
    AND Rotacion='$rota'
    ORDER BY Fecha Desc
    LIMIT 1";

$queryArea = "SELECT MarcacionesRapidas.Descripcion As Descripcion, 
    MarcacionesRapidasColas.MarcacionRapida,
    RotacionesMarcacionesRapidas.Rotacion As Rotacion, 
    Colas.Nombre As Area
    FROM xeryus.MarcacionesRapidas,
    xeryus.RotacionesMarcacionesRapidas, 
    xeryus.MarcacionesRapidasColas
    INNER JOIN xeryus.Colas On(Colas.Id=MarcacionesRapidasColas.Cola)
    WHERE Colas.Nombre LIKE '%Oracle%'
    AND MarcacionesRapidasColas.MarcacionRapida=MarcacionesRapidas.NumeroCorto
    AND MarcacionesRapidas.Id = RotacionesMarcacionesRapidas.MarcacionRapida
    AND MarcacionesRapidas.Descripcion = '$agent'
    AND RotacionesMarcacionesRapidas.Rotacion='$rota'
    ORDER BY Area";

$queryArea2 = "SELECT MarcacionesRapidas.Descripcion As Descripcion, 
    MarcacionesRapidasColas.MarcacionRapida,
    RotacionesMarcacionesRapidas.Rotacion As Rotacion, 
    Colas.Nombre As Area,
    Colas.Descripcion As Client
    FROM xeryus.MarcacionesRapidas,
    xeryus.RotacionesMarcacionesRapidas, 
    xeryus.MarcacionesRapidasColas
    INNER JOIN xeryus.Colas On(Colas.Id=MarcacionesRapidasColas.Cola)
    WHERE Colas.Nombre LIKE '%Oracle%'
    AND MarcacionesRapidasColas.MarcacionRapida=MarcacionesRapidas.NumeroCorto
    AND MarcacionesRapidas.Id = RotacionesMarcacionesRapidas.MarcacionRapida
    AND MarcacionesRapidas.Descripcion = '$agent'
    AND RotacionesMarcacionesRapidas.Rotacion='$rota'
    ORDER BY Area";

$queryOperationPanel2Detail = "SELECT FROM_UNIXTIME(time_id) As Date,
    TIMEDIFF(NOW(),FROM_UNIXTIME(time_id)) As Sec,
    Descripcion As Agent,
    verb As Status,
    queue_log.agent As Agente,
    NumeroCorto As SpeedDial,
    queue_log.queue As Area,
    unique_row_count
    FROM aasterisk.queue_log,
    xeryus.MarcacionesRapidas
    WHERE substring_index(agent, '/',-1) = MarcacionesRapidas.NumeroCorto
    AND FROM_UNIXTIME(time_id) BETWEEN '$dateStart $timeStart' AND '$dateEnd $timeEnd'
    AND verb IN ('ADDMEMBER', 'REMOVEMEMBER')
    AND queue_log.queue LIKE '%Oracle%'";

$queryAgentOperationPanel2Detail = "SELECT MarcacionesRapidas.Descripcion As Descripcion,
    MarcacionesRapidasColas.MarcacionRapida As SpeedDial,
    RotacionesMarcacionesRapidas.Rotacion As Rotacion,
    Colas.Nombre As Area
    FROM xeryus.MarcacionesRapidas,
    xeryus.RotacionesMarcacionesRapidas,
    xeryus.MarcacionesRapidasColas
    INNER JOIN xeryus.Colas On(Colas.Id=MarcacionesRapidasColas.Cola)
    WHERE Colas.Nombre LIKE '%Oracle%'
    AND MarcacionesRapidasColas.MarcacionRapida=MarcacionesRapidas.NumeroCorto
    AND MarcacionesRapidas.Id = RotacionesMarcacionesRapidas.MarcacionRapida";

?>
