public function createAction()
{
$my_object = new Formata();
$formData = $this->_request->getParams();
$db = Zend_Registry::get('db');
$config = Zend_Registry::get('config');
$prefix_inout = $config->ambiente->prefix_inout;
$dst_exceptions = $config->ambiente->dst_exceptions;
$init_day = $formData['period']['initDay'];
$final_day = $formData['period']['finalDay'];
$formated_init_day = new Zend_Date($init_day);
$formated_init_day = $formated_init_day->toString('yyyy-MM-dd hh:mm');
$formated_final_day = new Zend_Date($final_day);
$formated_final_day = $formated_final_day->toString('yyyy-MM-dd hh:mm');
$ordernar = $formData['period']['order'];
$groupsrc = $formData['source']['selectSrc'];
if (isset($formData['source']['groupSrc'])) {
$src = $formData['source']['groupSrc'];
} else {
$src = "";
}
if (isset($formData['source']['srctype'])) {
$srctype = $formData['source']['srctype'];
} else {
$srctype = "";
}
$groupdst = $formData['destination']['selectDst'];
if (isset($formData['destination']['groupDst'])) {
$dst = $formData['destination']['groupDst'];
} else {
$dst = "";
}
if (isset($formData['destination']['dsttype'])) {
$dsttype = $formData['destination']['dsttype'];
} else {
$dsttype = "";
}
if (isset($formData['calls']['costs_center'])) {
$contas = $formData['calls']['costs_center'];
}
$duration1 = $formData['calls']['duration_init'];
$duration2 = $formData['calls']['duration_end'];
$status = $formData['calls']['status'];
$status_ans = $status_noa = $status_fai = $status_bus = $status_all = '';
foreach ($status as $stat) {
switch ($stat) {
case 'ANSWERED':
$status_ans = 'ANSWERED';
break;
case 'NOANSWER':
$status_noa = 'NO ANSWER';
break;
case 'FAILED':
$status_fai = 'FAILED';
break;
case 'BUSY':
$status_bus = 'BUSY';
break;
}
}
$call_type = $formData['calls']['type'];
$view_files = $formData['others']['show_records'];
$view_tarif = $formData['others']['charging'];
// $graph_type = $formData['others']['graph_type'];
$rel_type = $formData['others']['report_type'];
$this->view->back = $this->view->translate("Back");
// Default submit
$acao = 'relatorio';
if (key_exists('submit_csv', $formData)) {
$acao = 'csv';
} else {
if (key_exists('submit_graph', $formData)) {
$acao = 'grafico';
}
}
/* Busca os ramais pertencentes ao grupo de ramal de origem selecionado */
$ramaissrc = $ramaisdst = "";
if ($groupsrc) {
$origens = PBX_Usuarios::getByGroup($groupsrc);
if (count($origens) == 0) {
$this->view->error = $this->view->translate("There are no extensions in the selected group.");
$this->_helper->viewRenderer('error');
} else {
$ramalsrc = "";
foreach ($origens as $ramal) {
$num = $ramal->getNumero();
if (is_numeric($num)) {
$ramalsrc .= $num . ',';
}
}
$ramaissrc = " AND src in (" . trim($ramalsrc, ',') . ") ";
}
}
/* Busca os ramais pertencentes ao grupo de ramal de destino selecionado */
if ($groupdst) {
$destinos = PBX_Usuarios::getByGroup($groupdst);
if (count($destinos) == 0) {
$this->view->error = $this->view->translate("There are no extensions in the selected group.");
$this->_helper->viewRenderer('error');
} else {
$ramaldst = "";
foreach ($destinos as $ramal) {
$num = $ramal->getNumero();
if (is_numeric($num)) {
$ramaldst .= $num . ',';
}
}
$ramaisdst = " AND dst in (" . trim($ramaldst, ',') . ") ";
}
}
/* Verificando existencia de vinculos no ramal */
$name = $_SESSION['name_user'];
$sql = "SELECT id_peer, id_vinculado FROM permissoes_vinculos WHERE id_peer ='{$name}'";
$result = $db->query($sql)->fetchObject();
$vinculo_table = "";
$vinculo_where = "";
if ($result) {
$vinculo_table = " ,permissoes_vinculos ";
$vinculo_where = " ( permissoes_vinculos.id_peer='{$result->id_peer}' AND (cdr.src = permissoes_vinculos.id_vinculado OR cdr.dst = permissoes_vinculos.id_vinculado) ) AND ";
}
/* Clausula do where: periodos inicial e final */
$dia_inicial = $formated_init_day;
$dia_final = $formated_final_day;
$date_clause = " ( calldate >= '{$dia_inicial}'";
$date_clause .= " AND calldate <= '{$dia_final}' ) ";
$CONDICAO = $date_clause;
$ORIGENS = '';
// Clausula do where: Origens
if ($src !== "") {
if (strpos($src, ",")) {
$SRC = '';
$arrSrc = explode(",", $src);
foreach ($arrSrc as $srcs) {
$SRC .= ' OR src LIKE \'' . $srcs . '\' ';
}
$SRC = " AND (" . substr($SRC, 3) . ")";
} else {
$CONDICAO = $this->do_field($CONDICAO, $src, substr($srctype, 3), 'src');
}
}
// Clausula do where: Destinos
if ($dst !== "") {
if (strpos($dst, ",")) {
$DST = '';
$arrDst = explode(",", $dst);
foreach ($arrDst as $dsts) {
$DST .= ' OR dst LIKE \'' . $dsts . '\' ';
}
$DST = " AND (" . substr($DST, 3) . ")";
} else {
$CONDICAO = $this->do_field($CONDICAO, $dst, substr($dsttype, 3), 'dst');
}
}
if (isset($ORIGENS)) {
$CONDICAO .= $ORIGENS;
}
if (isset($DST)) {
$CONDICAO .= $DST;
}
if (isset($SRC)) {
if (isset($DST)) {
$CONDICAO .= " OR " . ($SRC = substr($SRC, 4));
} else {
$CONDICAO .= $SRC;
}
}
/* Clausula do where: Duracao da Chamada */
if ($duration1) {
$CONDICAO .= " AND duration >= {$duration1} ";
} else {
$CONDICAO .= " AND duration > 0 ";
}
if ($duration2) {
$CONDICAO .= " AND duration <= {$duration2} ";
}
/* Clausula do where: Filtro de desccarte */
$TMP_COND = "";
$dst_exceptions = explode(";", $dst_exceptions);
foreach ($dst_exceptions as $valor) {
$TMP_COND .= " dst != '{$valor}' ";
$TMP_COND .= " AND ";
}
$CONDICAO .= " AND ( " . substr($TMP_COND, 0, strlen($TMP_COND) - 4) . " ) ";
/* Clausula do where: // Centro de Custos Selecionado(s) */
if (isset($contas) && count($contas) > 0) {
$TMP_COND = "";
foreach ($contas as $valor) {
$TMP_COND .= " accountcode like '" . $valor . "%'";
$TMP_COND .= " OR ";
}
$contas = implode(",", $contas);
if ($TMP_COND != "") {
$CONDICAO .= " AND ( " . substr($TMP_COND, 0, strlen($TMP_COND) - 3) . " ) ";
}
}
/* Clausula do where: Status/Tipo Ligacao */
if ($status_all || $status_ans && $status_noa && $status_bus && $status_fai) {
$CONDICAO .= "";
} else {
if ($status_ans && $status_noa && $status_bus) {
$CONDICAO .= " AND ( disposition = '{$status_ans}' OR disposition = '{$status_noa}' ";
$CONDICAO .= " OR disposition = '{$status_bus}' ) ";
} elseif ($status_ans && $status_noa && $status_fai) {
$CONDICAO .= " AND ( disposition = '{$status_ans}' OR disposition = '{$status_noa}' ";
$CONDICAO .= " OR disposition = '{$status_fai}' ) ";
} elseif ($status_ans && $status_fai && $status_bus) {
$CONDICAO .= " AND ( disposition = '{$status_ans}' OR disposition = '{$status_bus}' ";
} elseif ($status_noa && $status_bus && $status_fai) {
$CONDICAO .= " AND ( disposition = '{$status_noa}' OR disposition = '{$status_bus}' ";
$CONDICAO .= " OR disposition = '{$status_fai}' ) ";
} elseif ($status_ans && $status_noa) {
$CONDICAO .= " AND ( disposition = '{$status_ans}' OR disposition = '{$status_noa}' ) ";
} elseif ($status_ans && $status_bus) {
$CONDICAO .= " AND ( disposition = '{$status_ans}' OR disposition = '{$status_bus}' ) ";
} elseif ($status_ans && $status_fai) {
$CONDICAO .= " AND ( disposition = '{$status_ans}' OR disposition = '{$status_fai}' ) ";
} elseif ($status_noa && $status_bus) {
$CONDICAO .= " AND ( disposition = '{$status_bus}' OR disposition = '{$status_noa}' ) ";
} elseif ($status_fai && $status_noa) {
$CONDICAO .= " AND ( disposition = '{$status_fai}' OR disposition = '{$status_noa}' ) ";
} elseif ($status_bus && $status_fai) {
$CONDICAO .= " AND ( disposition = '{$status_bus}' OR disposition = '{$status_fai}' ) ";
} elseif ($status_ans) {
$CONDICAO .= " AND ( disposition = '{$status_ans}' ) ";
} elseif ($status_noa) {
$CONDICAO .= " AND ( disposition = '{$status_noa}' ) ";
} elseif ($status_bus) {
$CONDICAO .= " AND ( disposition = '{$status_bus}' ) ";
} elseif ($status_fai) {
$CONDICAO .= " AND ( disposition = '{$status_fai}' ) ";
}
}
/* Clausula do where: Tipo de Chamada (Originada/Recebida/Outra)) */
if ($call_type == "S") {
// Chamadas Originadas
$CONDICAO .= " AND (ccustos.tipo = 'S')";
} elseif ($call_type == "E") {
// Chamadas Recebidas
$CONDICAO .= " AND (ccustos.tipo = 'E')";
} elseif ($call_type == "O") {
// Chamadas Outras
$CONDICAO .= " AND (ccustos.tipo = 'O')";
}
/* Clausula do where: Prefixos de Login/Logout */
if (strlen($prefix_inout) > 3) {
$COND_PIO = "";
$array_prefixo = explode(";", $prefix_inout);
foreach ($array_prefixo as $valor) {
$par = explode("/", $valor);
$pio_in = $par[0];
if (!empty($par[1])) {
$pio_out = $par[1];
}
$t_pio_in = strlen($pio_in);
$t_pio_out = strlen($pio_out);
$COND_PIO .= " substr(dst,1,{$t_pio_in}) != '{$pio_in}' ";
if (!$pio_out == '') {
$COND_PIO .= " AND substr(dst,1,{$t_pio_out}) != '{$pio_out}' ";
}
$COND_PIO .= " AND ";
}
if ($COND_PIO != "") {
$CONDICAO .= " AND ( " . substr($COND_PIO, 0, strlen($COND_PIO) - 4) . " ) ";
}
}
$CONDICAO .= " AND ( locate('ZOMBIE',channel) = 0 ) ";
/* Montagem do SELECT de Consulta */
$SELECT = "ccustos.codigo,ccustos.tipo,ccustos.nome, date_format(calldate,\"%d/%m/%Y\") AS key_dia, date_format(calldate,\"%d/%m/%Y %H:%i:%s\") AS dia, src, dst, disposition, duration, billsec, accountcode, userfield, dcontext, amaflags, uniqueid, calldate ";
$tot_tarifado = 0;
/* Consulta de sql para verificar quantidade de registros selecionados e
Montar lista de Totais por tipo de Status */
try {
unset($duration, $billsec);
$sql_ctds = "SELECT " . $SELECT . " FROM cdr, ccustos {$vinculo_table} ";
$sql_ctds .= " WHERE (cdr.accountcode = ccustos.codigo) AND {$vinculo_where} " . $CONDICAO;
$sql_ctds .= ($ramaissrc === null ? '' : $ramaissrc) . ($ramaisdst === null ? '' : $ramaisdst);
$sql_ctds .= " GROUP BY userfield ORDER BY calldate, userfield";
if ($acao == "grafico") {
$tot_fai = $tot_bus = $tot_ans = $tot_noa = $tot_oth = array();
} else {
$tot_fai = $tot_bus = $tot_ans = $tot_noa = $tot_bil = $tot_dur = $tot_oth = 0;
}
$flag_ini = True;
// Flag para controle do 1o. registro lido
$userfield = "XXXXXXX";
// Flag para controle do Userfield
unset($result);
foreach ($db->query($sql_ctds) as $row) {
/* Incializa array se tipo = grafico */
$key_dia = $row['key_dia'];
if ($acao == "grafico") {
$tot_dias[$key_dia] = $key_dia;
$tot_ans[$key_dia] = !array_key_exists($key_dia, $tot_ans) ? 0 : $tot_ans[$key_dia];
$tot_noa[$key_dia] = !array_key_exists($key_dia, $tot_noa) ? 0 : $tot_noa[$key_dia];
$tot_bus[$key_dia] = !array_key_exists($key_dia, $tot_bus) ? 0 : $tot_bus[$key_dia];
$tot_fai[$key_dia] = !array_key_exists($key_dia, $tot_fai) ? 0 : $tot_fai[$key_dia];
$tot_oth[$key_dia] = !array_key_exists($key_dia, $tot_oth) ? 0 : $tot_oth[$key_dia];
}
/* Faz verificacoes para contabilizar valores dentro do mesmo userfield
So vai contabilziar resultados por userfield */
if ($userfield != $row['userfield']) {
if ($flag_ini) {
$result[$row['uniqueid']] = $row;
$userfield = $row['userfield'];
$flag_ini = False;
continue;
}
} else {
$result[$row['uniqueid']] = $row;
continue;
}
if ($row['uniqueid'] == '') {
continue;
}
/* Varre o array da chamada com mesmo userfield */
foreach ($result as $val) {
switch ($val['disposition']) {
case "ANSWERED":
if ($acao == 'grafico') {
$tot_ans[$key_dia]++;
} else {
$tot_ans++;
}
$tot_bil += $val['billsec'];
$tot_dur += $val['duration'];
if ($view_tarif) {
$valor = money_format('%.2n', $my_object->fmt_tarifa(array("a" => $val['dst'], "b" => $val['billsec'], "c" => $val['accountcode'], "d" => $val['calldate']), "A"));
$tot_tarifado += $valor;
}
break;
case "NO ANSWER":
if ($acao == 'grafico') {
$tot_noa[$key_dia]++;
} else {
$tot_noa++;
}
break;
case "BUSY":
if ($acao == 'grafico') {
$tot_bus[$key_dia]++;
} else {
$tot_bus++;
}
break;
case "FAILED":
if ($acao == 'grafico') {
$tot_fai[$key_dia]++;
} else {
$tot_fai++;
}
break;
default:
if ($acao == 'grafico') {
$tot_oth[$key_dia]++;
} else {
$tot_oth++;
}
break;
}
// Fim do Switch
}
// Fim do Foreach do array "result"
unset($result);
$result[$row['uniqueid']] = $row;
$userfield = $row['userfield'];
}
/* Switch a seguir é para pegar um possível último registro */
if (isset($result)) {
foreach ($result as $val) {
switch ($val['disposition']) {
case "ANSWERED":
if ($acao == 'grafico') {
$tot_ans[$key_dia]++;
} else {
$tot_ans++;
$tot_bil += $val['billsec'];
$tot_dur += $val['duration'];
if ($view_tarif) {
$valor = money_format('%.2n', $my_object->fmt_tarifa(array("a" => $val['dst'], "b" => $val['billsec'], "c" => $val['accountcode'], "d" => $val['calldate']), "A"));
$tot_tarifado += $valor;
}
}
break;
case "NO ANSWER":
if ($acao == 'grafico') {
$tot_noa[$key_dia]++;
} else {
$tot_noa++;
}
break;
case "BUSY":
if ($acao == 'grafico') {
$tot_bus[$key_dia]++;
} else {
$tot_bus++;
}
break;
case "FAILED":
if ($acao == 'grafico') {
$tot_fai[$key_dia]++;
} else {
$tot_fai++;
}
break;
default:
if ($acao == 'grafico') {
$tot_oth[$key_dia]++;
} else {
$tot_oth++;
}
break;
}
// Fim do Switch
}
}
// Fim do Foreach do array result para possivel ultimo registro
} catch (Exception $e) {
$this->view->error = $this->view->translate("Error");
$this->_helper->viewRenderer('error');
}
if ($acao == "relatorio") {
if ($tot_fai + $tot_bus + $tot_ans + $tot_noa == 0) {
$this->view->error = $this->view->translate("No entries found!.");
$this->_helper->viewRenderer('error');
}
$tot_wait = $tot_dur - $tot_bil;
$totais = array("answered" => $tot_ans, "notanswer" => $tot_noa, "busy" => $tot_bus, "fail" => $tot_fai, "billsec" => $tot_bil, "duration" => $tot_dur, "espera" => $tot_wait, "oth" => $tot_oth, "tot_tarifado" => $tot_tarifado);
// "tot_tarifado"=>number_format($tot_tarifado,2,",","."));
} else {
if (count($tot_fai) == 0 && count($tot_bus) == 0 && count($tot_ans) == 0 && count($tot_noa) == 0 && count($tot_oth) == 0) {
$this->view->error = $this->view->translate("No entries found!");
$this->_helper->viewRenderer('error');
return;
}
if ($acao != "grafico") {
$totais = array("ans" => $tot_ans, "noa" => $tot_noa, "bus" => $tot_bus, "fai" => $tot_fai, "dias" => $tot_dias, "dur" => $tot_dur, "bil" => $tot_bil);
} else {
$totais = array();
}
}
/* Define um SQL de Exibicao no Template, agrupado e com ctdor de agrupamentos */
$sql_chamadas = "SELECT count(userfield) as qtdade," . $SELECT . " FROM cdr, ccustos {$vinculo_table} ";
$sql_chamadas .= " WHERE (cdr.accountcode = ccustos.codigo) AND {$vinculo_where} " . $CONDICAO;
$sql_chamadas .= ($ramaissrc === null ? '' : $ramaissrc) . ($ramaisdst === null ? '' : $ramaisdst);
switch ($ordernar) {
case "data":
$ordernar = " calldate ";
break;
case "src":
$ordernar = " src, calldate ";
break;
case "dst":
$ordernar = " dst, calldate ";
break;
}
$sql_chamadas .= " GROUP BY userfield ORDER BY {$ordernar} ";
$defaultNS = new Zend_Session_Namespace('call_sql');
$defaultNS->sql = $sql_chamadas;
$defaultNS->totais = $totais;
$defaultNS->view_tarif = $view_tarif;
$defaultNS->view_files = $view_files;
$defaultNS->status = $status;
if (isset($contas)) {
$defaultNS->contas = $contas;
}
$defaultNS->report_type = $rel_type;
$defaultNS->src = $src;
$defaultNS->groupsrc = $groupsrc;
$defaultNS->dst = $dst;
$defaultNS->groupdst = $groupdst;
$defaultNS->sub_title = $this->view->translate($formData['period']['initDay'] . " - " . $formData['period']['initDay']);
$row = $db->query($sql_chamadas)->fetchAll();
for ($i = 0; $i <= count($row) - 1; $i++) {
$row[$i]['id'] = $i + 1;
}
$defaultNS->row = $row;
if (count($defaultNS->row) == 0) {
$this->view->error = $this->view->translate("No entries found!");
$this->_helper->viewRenderer('error');
return;
}
switch ($acao) {
case 'relatorio':
$this->reportAction();
break;
/* case 'grafico':
$this->graphAction();
break;
case 'csv':
$this->csvAction();
break;
*/
}
}
protected function getQuery($data, $ExportCsv = false)
{
$fromDay = $data["period"]["init_day"];
$tillDay = $data["period"]["till_day"];
$fromDay = new Zend_Date($fromDay);
$tillDay = new Zend_Date($tillDay);
$extenList = $data["exten"]["exten_select"];
$extenGroup = $data["exten"]["group_select"];
$services = $data["service"]["serv_select"];
$state = $data["service"]["stat_select"];
$configFile = "./includes/setup.conf";
$config = new Zend_Config_Ini($configFile, null, true);
$srv = '';
if (count($services) > 0) {
foreach ($services as $service) {
$srv .= "'{$service}',";
}
$srv = " AND service IN (" . substr($srv, 0, -1) . ")";
}
$extenSrc = $extenDst = $cond = "";
if ($extenGroup) {
$origins = PBX_Usuarios::getByGroup($extenGroup);
if (count($origins) == 0) {
throw new Zend_Exception('Group not registered');
} else {
foreach ($origins as $ext) {
$extenSrc .= "'{$ext->getNumero()}'" . ',';
}
$extenSrc = " AND peer in (" . trim($extenSrc, ',') . ") ";
}
} else {
if ($extenList) {
$extenList = explode(";", $extenList);
$list = '';
foreach ($extenList as $value) {
$list .= trim($value) . ',';
}
$extenSrc = " AND services_log.peer IN ('" . substr($list, 0, -1) . "') ";
}
}
$state_cnt = count($state);
if ($state_cnt == 2) {
$state = " ";
} else {
if ($state[0] == "D") {
$state = " AND services_log.state = '0' ";
}
if ($state[0] == "A") {
$state = " AND services_log.state = '1' ";
}
}
$dateClause = " ( date >= '{$fromDay->toString('yyyy-MM-dd hh:mm')}'";
$dateClause .= " AND date <= '{$tillDay->toString('yyyy-MM-dd hh:mm')}') ";
//'
$cond .= " {$dateClause} ";
$sql = " SELECT *, DATE_FORMAT(date,'%d/%m/%Y %T') as date FROM services_log WHERE ";
$sql .= $cond . $state;
$sql .= $extenSrc ? $extenSrc : '';
$sql .= $srv ? $srv : '';
$db = Zend_Registry::get('db');
$stmt = $db->query($sql);
$dataTmp = $stmt->fetchAll();
foreach ($dataTmp as $key => $value) {
if (!$ExportCsv) {
if ($value['state'] == 1) {
$dataTmp[$key]['state'] = $this->view->translate(' - Activated');
} else {
$dataTmp[$key]['state'] = $this->view->translate(' - Deactivated');
}
} else {
if ($value['state'] == 1) {
$dataTmp[$key]['state'] = $this->view->translate('Activated');
} else {
$dataTmp[$key]['state'] = $this->view->translate('Deactivated');
}
$dataTmp[$key]['status'] = '"' . $value['status'] . '"';
}
}
return $dataTmp;
}
