<?php

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

function GetQryStatusChamadas($useAgt = true) {
    $matricula = $_SESSION["SSmatriculaUser"];
    $dac = $_SESSION[AGT_DAC_CONECT];
    $query = "SELECT fila
                      ,ABANDONADAS
                      ,ATENDIDAS_PA
                      ,ESPERA
                      ,round( CASE WHEN(ESPERA = 0)THEN 0 ELSE (TEMPO_ESPERA / ESPERA) END ) * INTERVAL '1 SECOND' AS TME
                      ,round( CASE WHEN(ATENDIDAS_PA = 0)THEN 0 ELSE (TEMPO_ATENDIMENTO / ATENDIDAS_PA)END) * INTERVAL '1 SECOND' AS TMA
                      ,TEMPO_ESPERA
                      ,TEMPO_ATENDIMENTO
                      ,TEMPO_ABANDONO
                    FROM (
                    SELECT fila
                    ,SUM (CASE WHEN EVENTO = 'ABANDON'    THEN 1 ELSE 0 END) AS ABANDONADAS
                    ,SUM (CASE WHEN EVENTO IN ('CONNECT') THEN 1 ELSE 0 END) AS ATENDIDAS_PA
                    ,SUM (CASE WHEN EVENTO IN ('CONNECT') AND to_number(param1,'999999999') > '3' THEN 1 ELSE 0 END) AS ESPERA
                    ,SUM (CASE WHEN EVENTO IN ('CONNECT') AND to_number(param1,'999999999') > '1' THEN to_number(param1,'999999999') ELSE 0 END) AS TEMPO_ESPERA
                    ,SUM (CASE WHEN EVENTO IN ('COMPLETEAGENT','COMPLETECALLER') AND to_number(param2,'999999999') > '1' THEN to_number(param2,'999999999') ELSE 0 END) AS TEMPO_ATENDIMENTO
                    ,SUM (CASE WHEN( (EVENTO = 'ABANDON') AND  (to_number(param2,'999999999') > '1') ) THEN to_number(param3,'999999999') ELSE 0 END) AS TEMPO_ABANDONO
                    FROM (
                    SELECT
                     a.calldate,b.fila,b.evento,b.param1,b.param2,b.param3,b.param4
                    FROM ast_bilhetes a
                    INNER JOIN ast_eventos_dacs b  on b.uid2 = a.uniqueid
                    WHERE b.evento in ('ABANDON','COMPLETEAGENT','COMPLETECALLER','CONNECT','ENTERQUEUE')
                    AND a.lastapp <> 'Transferred Call'
                    AND a.data_bilhete = cast(now() as date) ";
    if ($useAgt)
        $query .= "  AND substring(b.agente, 7,4) = '$matricula' ";
    $query .= " AND b.fila = (select nome from pbx_dacs  where id = $dac)
                    ) AS DADOS
                      GROUP BY FILA
                    ) AS DADOS
                    ";
    return $query;
}

function GetQryInfoGeral() {
    $dac = $_SESSION[AGT_DAC_CONECT_DESC];
    return "select abandonadas, espera from pbx_supervisor_dacs where upper(trim(dac)) = upper(trim('$dac')) ";
    //atendidas, abandonadas, espera, (tempo_espera * (interval '1 second')) as tempo_espera, cast(percentual_nivel_servico as varchar) || '%'  as tempo_nivel_servico
}

function GetQryInfoChamadasCorrente() {
    $matricula = $_SESSION["SSmatriculaUser"];
    //$dac = $_SESSION[AGT_DAC_CONECT_DESC];
    return "select ramal,  matricula,  nome, (LOCALTIMESTAMP(0) - tempo_login) as tempo_login,  modo_atendimento,   origem_destino, status, (LOCALTIMESTAMP(0) - duracao) as duracao,
            uniqueid, protocolo  from pbx_supervisor_agentes
            where  matricula = '$matricula' ";
}

?>