";
$grafDac = "![](\"index.php?idProg=126&tipoGraph=dacAtu&dac=$idDac&tipoDac=$tpOper\")
";
$smarty->assign('nomeDac', $nomeDac);
$smarty->assign('matricula', $matricula);
$smarty->assign("apelido", $apelido);
$smarty->assign("nome", $nome);
$smarty->assign("ramal", $ramal);
$smarty->assign("chamadasAtendidas", $chamadasAtendidas);
$smarty->assign("chamadasOriginadas", $chamadasOriginadas);
$smarty->assign("tma", !($chamadasAtendidas + $chamadasOriginadas) ? SecondToStrTime(0) : SecondToStrTime(round($tempoAtendimento / ($chamadasAtendidas + $chamadasOriginadas), 0)));
$smarty->assign("login", $horaLogin);
$smarty->assign("tempoLogado", SecondToStrTime($tempoLogin));
$linkPausa = !$numPausa ? "" : sprintf("(detalhar)%s", $matricula, $numPausa);
$smarty->assign("numeroPausas", $linkPausa);
$smarty->assign("graficoAgente", $grafAgt);
$smarty->assign("graficoDac", $grafDac);
GetTemplate($smarty, $tpl);
}
function FetchDados($dbcon, $tipo, $dac, $matricula) {
$dados = GetDados($dbcon, $tipo, $dac, $matricula);
if (!$dados)
$dados = GetDados($dbcon, $tipo, $dac, $matricula, 1);
if ($tipo != 'agt') {
$numAgt = IsNull($dados["num_agente"], 1);
$tempoAtendimento = !$numAgt ? 0 : round(IsNull($dados["tempo_atendimento"], 0) / $numAgt, 0);
$chamadasOriginadas = !$numAgt ? 0 : $dados["chamadas_originadas"];
$chamadasAtendidas = !$numAgt ? 0 : round(IsNull($dados["chamadas_atendidas"] + $chamadasOriginadas, 0) / $numAgt, 0);
$tempoLogin = !$numAgt ? 0 : round(IsNull($dados["tempo_login"], 0) / $numAgt, 0);
$tempoPausa = !$numAgt ? 0 : round(IsNull($dados["tempo_pausa"], 0) / $numAgt, 0);
$tempoAcw = !$numAgt ? 0 : round(IsNull($dados["pos_atendimento"], 0) / $numAgt, 0);
} else {
$tempoAtendimento = IsNull($dados["tempo_atendimento"], 0);
$chamadasAtendidas = IsNull($dados["chamadas_atendidas"], 0);
$tempoLogin = IsNull($dados["tempo_login"], 0);
$tempoPausa = IsNull($dados["tempo_pausa"], 0);
$tempoAcw = IsNull($dados["pos_atendimento"], 0);
}
$tempoLivre = $tempoLogin - ($tempoAtendimento + $tempoPausa + $tempoAcw);
$tempoTotal = $tempoLivre + $tempoPausa + $tempoAcw + $tempoAtendimento;
$perc = round(!$tempoTotal ? 0 : ((($tempoLivre * 100 ) / $tempoTotal)), 0);
$legLivre = SecondToStrTime($tempoLivre); // . " $perc%%";
$perc = round(!$tempoTotal ? 0 : ((($tempoPausa * 100 ) / $tempoTotal)), 0);
$legPausa = SecondToStrTime($tempoPausa); // . " $perc%%";
$perc = round(!$tempoTotal ? 0 : ((($tempoAcw * 100 ) / $tempoTotal)), 0);
$legAcw = SecondToStrTime($tempoAcw); // . " $perc%%";
$perc = round(!$tempoTotal ? 0 : ((($tempoAtendimento * 100 ) / $tempoTotal)), 0);
$legAtend = SecondToStrTime($tempoAtendimento); // . " $perc%%";
/*
* $legLivre = round( (!$tempoTotal ? 0 : (($tempoLivre * 100 ) /$tempoTotal)) , 0 );
$legPausa = SecondToStrTime( round( (!$tempoTotal ? 0 : (($tempoPausa * 100 ) /$tempoTotal)) , 0 ));
$legAcw = SecondToStrTime( round( (!$tempoTotal ? 0 : (($tempoAcw * 100 ) /$tempoTotal)) , 0 ));
$legAtend = SecondToStrTime( round( (!$tempoTotal ? 0 : (($tempoAtendimento * 100 ) /$tempoTotal)) , 0 ));
*/
$_SESSION["SSlegendAgtGraphPie"] = array("Livre: " . $legLivre, "Pausa: " . $legPausa, "Pausa-Produtiva: " . $legAcw, "Ocupado: " . $legAtend);
$dadosInfoAgt = array($tempoLivre, $tempoPausa, $tempoAcw, $tempoAtendimento);
$_SESSION["SSdadosAgtGraphPie"] = $dadosInfoAgt;
return $dados;
}
function GetDados($dbcon, $tipo, $dac, $matricula, $tpQuery = '') {
$query = ($tipo == 'agt') ? GetQueryAgt($dac, $matricula, $tpQuery) : GetQueryDac($dac);
$result = pg_query($dbcon, $query);
//echo $query;
if ($result && pg_num_rows($result)) {
$numReg = pg_num_rows($result);
$dados = pg_fetch_array($result);
$dadosGraph = array();
if ($numReg) {
foreach ($dados as $key => $value) {
$dadosGraph[$key] = $value;
}
}
$query = ($tipo == 'agt') ? GetQueryAgtComplemento($dados["id_dac"], $matricula) : GetQueryDacComplemento($dados["id_dac"]);
$result = pg_query($dbcon, $query);
if (pg_num_rows($result)) {
$numReg = pg_num_rows($result);
$dados = pg_fetch_array($result);
if ($numReg) {
foreach ($dados as $key => $value) {
$dadosGraph[$key] = $value;
}
}
}
return $dadosGraph;
}
}
function GetQueryAgt($dac, $matricula, $sql = '') {
if ($sql) {
$queryDac = "SELECT e.ramal,
e.matricula,
e.nome as apelido,
c.id as id_dac,
d.nome,
e.dac
FROM pbx_dacs c, pbx_usuarios d, pbx_supervisor_agentes e
where e.matricula = d.matricula
and c.nome = '$dac'
and d.matricula = '$matricula'
GROUP BY 1,2,3,4,5,6";
} else {
$queryCampanha = "SELECT e.ramal,
e.matricula,
e.nome as apelido,
c.cmp_id as id_dac,
d.nome,
e.dac,
sum(
CASE
WHEN b.evento = 'CONNECT' THEN 1
ELSE 0
END) AS chamadas_atendidas,
sum(
CASE
WHEN b.evento = 'COMPLETECALLER' OR b.evento = 'COMPLETEAGENT' OR b.evento = 'TRANSFER' THEN
CASE
WHEN b.evento = 'TRANSFER' THEN(case when(coalesce(param4,'') = '')then 0 else b.param4::int end)
ELSE (case when(coalesce(param2,'') = '')then 0 else b.param2::int end)
END
ELSE 0
END) AS tempo_atendimento
FROM pbx_supervisor_agentes e
inner join pbx_campanha c on e.dac = c.cmp_descricao
inner join pbx_usuarios d on e.matricula = d.matricula
left outer join ast_eventos_dacs b on c.cmp_descricao = b.fila and e.matricula = substring(b.agente, 7, 4) and cast(to_timestamp(uid1::int) as date) in (cast(e.tempo_login as date),cast(now() as date))
left outer join ast_bilhetes a on b.uid2 = a.accountcode and a.lastapp = 'ResetCDR' and a.dcontext = 'ext-campanha'
WHERE cast(tempo_login as date) = now()::date
and c.cmp_descricao = '$dac'
and d.matricula = '$matricula'
GROUP BY 1,2,3,4,5,6 ";
$queryDac = "SELECT e.ramal,
e.matricula,
e.nome as apelido,
c.id as id_dac,
d.nome,
e.dac,
sum(
CASE
WHEN b.evento in('COMPLETAAGENT','COMPLETACALLER','TRANSFERORIG') THEN 1
ELSE 0
END) AS chamadas_originadas,
sum(
CASE
WHEN b.evento in('COMPLETECALLER', 'COMPLETEAGENT' , 'TRANSFER') THEN 1
ELSE 0
END) AS chamadas_atendidas,
sum(
CASE
WHEN(b.evento in('COMPLETAAGENT','COMPLETACALLER','TRANSFERORIG','COMPLETECALLER', 'COMPLETEAGENT' , 'TRANSFER'))THEN
CASE
WHEN b.evento in('TRANSFER', 'TRANSFERORIG')THEN (case when(coalesce(param4,'') = '')then 0 else b.param4::int end)
ELSE (case when(coalesce(param2,'') = '')then 0 else b.param2::int end)
END
ELSE 0
END) AS tempo_atendimento
FROM ast_bilhetes a, ast_eventos_dacs b, pbx_dacs c, pbx_usuarios d, pbx_supervisor_agentes e
where b.uid2 = a.uniqueid
AND c.nome = b.fila
and d.matricula = substring(b.agente,7,4)
and e.matricula = d.matricula
and a.calldate::date in(e.tempo_login::date, now()::date)
and c.nome = '$dac'
and d.matricula = '$matricula'
and lastapp <> 'Transferred Call'
GROUP BY 1,2,3,4,5,6";
}
if ($GLOBALS["tpOper"] == OPERACAO_ATIVO) {
return $queryCampanha;
}
//if(IsAdmin()) echo $queryDac;
return $queryDac;
}
function GetQueryAgtComplemento($dac, $matricula) {
return "select count(entrada_pausa) as numPausa,
min(a.login) as login,
sum( case when(a.pausa_produtiva = 1)then EXTRACT(epoch from (saida_pausa - entrada_pausa)) else 0 end ) as pos_atendimento,
sum( case when(a.pausa_produtiva = 0)then EXTRACT(epoch from (saida_pausa - entrada_pausa)) else 0 end ) as tempo_pausa,
sum(EXTRACT(epoch from (logoff - login))) as tempo_login
from pbx_eventos_agentes a
where coalesce(entrada_pausa, login)::date = now()::date
and matricula = '$matricula'
and id_dac = '$dac' ";
$queryDac = ""; // left outer join pbx_motivos_pausas b on b.id = a.id_motivo_pausa
if ($GLOBALS["tpOper"] == OPERACAO_ATIVO) {
return $queryCampanha;
}
return $queryDac;
}
function GetQueryDac($dac) {
$queryCampanha = "SELECT c.cmp_id as id_dac,
count(distinct d.matricula) as num_agente,
sum(
CASE
WHEN b.evento = 'CONNECT' THEN 1
ELSE 0
END) AS chamadas_atendidas,
sum(
CASE
WHEN b.evento = 'COMPLETECALLER' OR b.evento = 'COMPLETEAGENT' OR b.evento = 'TRANSFER' THEN
CASE
WHEN b.evento = 'TRANSFER' THEN (case when(coalesce(param4,'') = '')then 0 else b.param4::int end)
ELSE (case when(coalesce(param2,'') = '')then 0 else b.param2::int end)
END
ELSE 0
END) AS tempo_atendimento
FROM pbx_supervisor_agentes e
inner join pbx_campanha c on e.dac = c.cmp_descricao
inner join pbx_usuarios d on e.matricula = d.matricula
left outer join ast_eventos_dacs b on c.cmp_descricao = b.fila and e.matricula = substring(b.agente, 7, 4) and cast(to_timestamp(uid1::int) as date) in (cast(e.tempo_login as date),cast(now() as date))
left outer join ast_bilhetes a on b.uid2 = a.accountcode and a.lastapp = 'ResetCDR' and a.dcontext = 'ext-campanha'
WHERE cast(tempo_login as date) = now()::date
and c.cmp_id = '$dac'
group by 1";
$queryDac = "SELECT c.id as id_dac,
count(distinct d.matricula) as num_agente,
sum(
CASE
WHEN (b.evento in('COMPLETECALLER', 'COMPLETEAGENT' , 'TRANSFER','COMPLETAAGENT','COMPLETACALLER','TRANSFERORIG'))THEN 1
ELSE 0
END) AS chamadas_atendidas,
sum(
CASE
WHEN(b.evento in('COMPLETECALLER', 'COMPLETEAGENT' , 'TRANSFER','COMPLETAAGENT','COMPLETACALLER','TRANSFERORIG'))THEN
CASE
WHEN( b.evento in('TRANSFER', 'TRANSFERORIG'))THEN( case when(coalesce(param4,'') = '')then 0 else b.param4::int end )ELSE(case when(coalesce(param2,'') = '')then 0 else b.param2::int end)
END
ELSE 0
END) AS tempo_atendimento
FROM pbx_supervisor_agentes e
inner join pbx_dacs c on e.dac = c.nome
inner join pbx_usuarios d on e.matricula = d.matricula
left outer join ast_eventos_dacs b on c.nome = b.fila and e.matricula = substring(b.agente, 7, 4) and cast(to_timestamp(uid1::int) as date) in (cast(e.tempo_login as date),cast(now() as date))
left outer join ast_bilhetes a on b.uid2 = a.accountcode and a.lastapp = 'ResetCDR'
WHERE c.id = '$dac'
group by 1";
if ($GLOBALS["tpOper"] == OPERACAO_ATIVO) {
return $queryCampanha;
}
return $queryDac;
}
function GetQueryDacComplemento($dac) {
return "select count(distinct matricula) as num_agente,
count(entrada_pausa) as numPausa,
min(a.login) as login,
sum( case when(a.pausa_produtiva = 1)then EXTRACT(epoch from (saida_pausa - entrada_pausa)) else 0 end ) as pos_atendimento,
sum( case when(a.pausa_produtiva = 0)then EXTRACT(epoch from (saida_pausa - entrada_pausa)) else 0 end ) as tempo_pausa,
sum(EXTRACT(epoch from (logoff - login))) as tempo_login
from pbx_eventos_agentes a
where coalesce(entrada_pausa, login)::date = now()::date
and coalesce(entrada_pausa, login)::date = now()::date and id_dac = '$dac'";
}
?>