<?php
ini_set('display_errors', 1);
error_reporting(E_ALL ^ E_NOTICE);
include ("../admin/config/cfg.php");
global $cwlang, $cwUserId, $cwUserInfo;
$get_params = filter_input_array(INPUT_GET);
$access_type = $cwUserInfo["access_type"];
if ($access_type) {
$access_objects = getUserAccess($cwUserId, $access_type);
}
switch ($get_params["action"]) {
case "getReport":
$output = getReport($get_params);
echo $output;
break;
case "get_list":
default:
//$output = getCalls($get_params);
header("Content-type: text/json");
echo json_encode($output);
break;
case "xls":
$date = explode(" - ", $get_params["filterDate"]);
$dateFrom = $date[0];
$dateTo = $date[1];
$filename = "Общий_отчет_$dateFrom-$dateTo.xls";
$val = getReport($get_params);
header('Content-Type: text/html; charset=windows-1251');
header('P3P: CP="NOI ADM DEV PSAi COM NAV OUR OTRo STP IND DEM"');
header('Expires: Mon, 26 Jul 1997 05:00:00 GMT');
header('Cache-Control: no-store, no-cache, must-revalidate');
header('Cache-Control: post-check=0, pre-check=0', FALSE);
header('Pragma: no-cache');
header('Content-transfer-encoding: binary');
header('Content-Disposition: attachment; filename=' . $filename);
header('Content-Type: application/x-unknown');
echo iconv("utf-8", "windows-1251", $val);
break;
}
function formatNumber($number) {
$result = $number;
if ($number) {
if (strlen($number) == 7) {
$result = "8342$number";
}
elseif (strlen($number) ==
{
$result = "8342" . substr($number, 1);
}
elseif (strlen($number) == 10) {
$result = "8$number";
}
elseif (strlen($number) == 11) {
$result = substr_replace($number, "8", 0, 1);
}
elseif (strlen($number) == 12) {
$result = substr($number, 1);
}
}
return $result;
}
function getReport($params) {
global $db, $access_objects, $access_type;
$table = "calls_v2";
$table_join = "cdr";
$html = "";
$calls = array();
$date = explode(" - ", $params["filterDate"]);
$dateFrom = new DateTime($date[0]);
$dateTo = new DateTime($date[1]);
$dateTo->modify('+1 day');
$dateFrom = date_format($dateFrom, "Y-m-d H:i:s");
$dateTo = date_format($dateTo, "Y-m-d H:i:s");
$filterGroup = trim($params["filterGroup"]);
/*
$sql = "SELECT `$table`.`type`, `$table`.`calldate`, `$table`.`src`, `$table`.`dst`, `$table`.`group` AS `group_name`
FROM `$table`
WHERE (`$table`.`calldate` BETWEEN '$dateFrom' AND '$dateTo' ) ";
*/
$sql = "SELECT `$table`.`type`, `$table`.`calldate`, `$table`.`src`, `$table`.`dst`, `$table`.`group` AS `group_name`, `$table_join`.`userfield`
FROM `$table`
LEFT JOIN `$table_join` /* слияние таблиц */
ON `$table`.`linkedid` = `$table_join`.`linkedid` /* условие слияния - одинаковые поля linkedid */
WHERE (`$table`.`calldate` BETWEEN '$dateFrom' AND '$dateTo' ) && (`$table_join`.`calldate` BETWEEN '$dateFrom' AND '$dateTo' ) ";
if (!empty($params["filterGroup"])) {
$sql .= " AND $table.`group` LIKE'%{$filterGroup}%'";
}
$sql .= " AND `$table`.`type` = 'incoming'
AND `$table`.`disposition` <> 'ANSWERED'
AND `$table`.`src` <> 'Неизвестный'";
if ($access_objects) {
$objects = implode("|", $access_objects);
switch ($access_type) {
case "groups":
$sql .= " AND `group` REGEXP '^$objects$'";
break;
case "numbers":
$sql .= " AND (`src` REGEXP '^$objects$' OR `dst` REGEXP '^$objects$' OR `dst_answer` REGEXP '^$objects$' OR `last` REGEXP '^$objects$' )";
break;
default :
break;
}
}
$sql .= " ORDER BY `$table`.`calldate` DESC";
$incoming = $db->GetAll($sql);
$sql = "SELECT `$table`.`type`, `$table`.`calldate`, `$table`.`src`, `$table`.`dst`, `$table`.`group` AS `group_name`
FROM `$table`
WHERE (`$table`.`calldate` BETWEEN '$dateFrom' AND NOW() )";
if (!empty($params["filterGroup"])) {
$sql .= " AND $table.`group` LIKE '%{$filterGroup}%'";
}
if ($access_objects) {
$objects = implode("|", $access_objects);
switch ($access_type) {
case "groups":
$sql .= " AND `group` REGEXP '^$objects$'";
break;
case "numbers":
$sql .= " AND (`src` REGEXP '^$objects$' OR `dst` REGEXP '^$objects$' OR `dst_answer` REGEXP '^$objects$' OR `last` REGEXP '^$objects$' )";
break;
default :
break;
}
}
$sql .= " AND `$table`.`type` = 'incoming'
AND `$table`.`src` <> 'Неизвестный'
AND `$table`.`disposition` = 'ANSWERED'";
$sql .= " ORDER BY `$table`.`calldate` DESC";
$dblincoming = $db->GetAll($sql);
$sql = "SELECT $table.type, $table.calldate, $table.src, $table.dst, $table.disposition, `$table`.`group` AS `group_name`
FROM $table
WHERE ($table.calldate BETWEEN '$dateFrom' AND NOW() )
AND $table.type = 'outgoing'
AND $table.disposition = 'ANSWERED'";
/*if ($access_objects) {
$objects = implode("|", $access_objects);
switch ($access_type) {
case "groups":
$sql .= " AND `group` REGEXP '^$objects$'";
break;
case "numbers":
$sql .= " AND (`src` REGEXP '^$objects$' OR `dst` REGEXP '^$objects$' OR `dst_answer` REGEXP '^$objects$' OR `last` REGEXP '^$objects$' )";
break;
default :
break;
}
}*/
$sql .= " ORDER BY $table.calldate DESC";
$outgoing = $db->GetAll($sql);
foreach ($incoming as $row) {
$number = formatNumber($row["src"]);
if ($row["group_name"]) {
$group = explode(",", $row["group_name"]);
for ($i = 0; $i < count($group); $i++) {
if (!empty($params["filterGroup"]) && trim($group[$i]) != $filterGroup) {
continue;
}
else {
/* DE edit -----------------------------------------------------------
$calls[$number][] = array("calldate" => $row["calldate"], "group" => $group[$i], "dst" => $row["dst"]);
---------------------------------------------------------------DE edit*/
$calls[$number][] = array("calldate" => $row["calldate"], "group" => $group[$i], "dst" => $row["dst"], "userfield" => $row["userfield"] );
}
}
}
else {
/* DE edit -------------------------------------------------------------------- Добавил запись поля userfield в массив
$calls[$number][] = array("calldate" => $row["calldate"], "group" => $row["group_name"], "dst" => $row["dst"]);
------------------------------------------------------------------------DE edit */
$calls[$number][] = array("calldate" => $row["calldate"], "group" => $group[$i], "dst" => $row["dst"], "userfield" => $row["userfield"] );
}
}
foreach ($dblincoming as $row) {
$number = formatNumber($row["src"]);
if ($row["group_name"]) {
$group = explode(",", $row["group_name"]);
for ($i = 0; $i < count($group); $i++) {
if (!empty($params["filterGroup"]) && trim($group[$i]) != $filterGroup) {
continue;
}
else {
$dblcalls[$number][] = array("calldate" => $row["calldate"], "group" => $group[$i], "dst" => $row["dst"]);
}
}
}
$dblcalls[$number][] = array("calldate" => $row["calldate"], "group" => $row["group_name"], "dst" => $row["dst"]);
}
foreach ($outgoing as $row) {
$number = formatNumber($row["dst"]);
$recalls[$number][] = $row["calldate"];
}
foreach ($calls as $key => $value) {
if ($dblcalls[$key]) {
foreach ($value as $k => $v) {
foreach ($dblcalls[$key] as $dblc) {
if (strtotime($dblc["calldate"]) > strtotime($v["calldate"]) && ( $v["group"] == "" || $dblc["group"] == $v["group"])) {
//if (strtotime($dblc["calldate"]) > strtotime($v["calldate"]) && $dblc["group"] == $v["group"]) {
unset($calls[$key][$k]);
}
if (count($calls[$key]) == 0) {
unset($calls[$key]);
}
}
}
}
}
foreach ($calls as $key => $value) {
if ($recalls[$key]) {
$reDate = strtotime(array_shift($recalls[$key]));
foreach ($value as $k => $v) {
if ($reDate > strtotime($v["calldate"])) {
unset($calls[$key][$k]);
}
if (count($calls[$key]) == 0) {
unset($calls[$key]);
}
}
}
}
if ($calls) {
$html .= "<table border='1' class=\"b-noanswer_report\" >";
$html .= "<thead>";
$html .= "<tr>";
$html .= "<th>Дата звонка</th>";
$html .= "<th>Время звонка</th>";
$html .= "<th>Внеш. номер</th>";
$html .= "<th>Группа</th>";
$html .= "<th>Не отвеченных</th>";
$html .= "<th>Комментарий</th>"; // добавление еще одной колонки для userfield
$html .= "</tr>";
$html .= "<tr>";
$html .= "<th colspan=2 style='text-align:center'>Номер звонящего</th>";
$html .= "<th></th>";
$html .= "<th></th>";
$html .= "<th></th>";
$html .= "<th></th>"; // добавление еще одной колонки для userfield
$html .= "</tr>";
$html .= "</thead>";
$html .= "<tbody>";
foreach ($calls as $key => $value) {
$html .= "<tr>";
$html .= "<td colspan=2 style='text-align:center; background:#FABF8F'><b>" . $key . "</b></td>";
$html .= "<td></td>";
$html .= "<td></td>";
$html .= "<td style='text-align:right;color:red'><b>" . count($calls[$key]) . "</b></td>";
$html .= "<td></td>"; // добавление еще одной ячейки для userfield
$html .= "</tr>";
foreach ($value as $k => $v) {
$html .= "<tr>";
$html .= "<td style='text-align:center; background:#FDE9D9'>" . date("d.m.Y", strtotime($v["calldate"])) . "</td>";
$html .= "<td style='text-align:center; background:#FDE9D9'>" . date("H:i:s", strtotime($v["calldate"])) . "</td>";
$html .= "<td><b>" . $v["dst"] . "</b></td>";
$html .= "<td>" . $v["group"] . "</td>";
$html .= "<td></td>";
$html .= "<td>" . $v["userfield"] . "</td>"; // добавление еще одой ячейки под userfield
$html .= "</tr>";
}
}
$html .= "</tbody>";
$html .= "</table>";
}
else {
$html .= "<p>Нет пропущенных звонков</p>";
}
return $html;
}