";
echo "
Note:
";
echo "
Prices are in default currency
";
echo "
Timezone is $tz
";
echo "
Data is refreshed every minute
";
echo "
";
date_default_timezone_set($tz);
echo "Current date: " . date('Y-m-d H:i:s') . "
";
// Create connection
$conn = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$originator = 'device';
$terminator = 'device';
$selected_originator = -2;
$selected_terminator = -2;
$total_calls = 0;
$total_answered_calls = 0;
$total_billsec = 0;
$total_originator_billsec = 0;
$total_terminator_billsec = 0;
$total_originator_price = 0;
$total_terminator_price = 0;
$clear = 0;
$prefix = '';
$original_prefix = '';
$prefix_wildcard = 0;
$originator_type_user_name = "OP User";
if ($system == "mor") {
$originator_type_user_name = "User";
}
$originator_type_device_name = "Origination Point";
if ($system == "mor") {
$originator_type_device_name = "Device";
}
$terminator_type_user_name = "TP User";
if ($system == "mor") {
$terminator_type_user_name = "Terminator";
}
$terminator_type_device_name = "Termination Point";
if ($system == "mor") {
$terminator_type_device_name = "Provider";
}
$timezone_offset = date("P");
$date_from = date("Y-m-d H:00:00P");
$date_till = date("Y-m-d H:59:59P");
if (isset($_POST['originator_id'])) {
$selected_originator = $_POST['originator_id'] + 0;
}
if (isset($_POST['terminator_id'])) {
$selected_terminator = $_POST['terminator_id'] + 0;
}
if (isset($_POST['date_from'])) {
$date_from = $_POST['date_from'] . $timezone_offset;
}
if (isset($_POST['date_till'])) {
$date_till = $_POST['date_till'] . $timezone_offset;
}
if (isset($_POST['terminator_type'])) {
if ($_POST['terminator_type'] == "1") {
$terminator = "device";
} else {
$terminator = "user";
}
}
if (isset($_POST['originator_type'])) {
if ($_POST['originator_type'] == "1") {
$originator = "device";
} else {
$originator = "user";
}
}
if (isset($_GET['clear'])) {
$clear = 1;
}
if (isset($_POST['prefix'])) {
$prefix = $_POST['prefix'];
$original_prefix = $_POST['prefix'];
}
if (strlen($prefix)) {
if ($prefix[strlen($prefix) - 1] == "%") {
$prefix_wildcard = 1;
$prefix = substr($prefix, 0, strlen($prefix) - 1);
}
}
$date_from = str_replace(" ", "T", $date_from);
$date_till = str_replace(" ", "T", $date_till);
$originator_usernames = array();
$terminator_usernames = array();
$terminator_providers = array();
$originator_usernames['0'] = '-';
$originator_usernames['-1'] = '-';
$originator_usernames['-2'] = '-';
$terminator_usernames['0'] = '-';
$terminator_usernames['-1'] = '-';
$terminator_usernames['-2'] = '-';
$terminator_providers['0'] = '0';
$terminator_providers['-1'] = '-1';
$terminator_providers['-2'] = '-2';
$min_date = "2014-01-01 00:00:00";
$max_date = "2016-01-01 00:00:00";
$data = array();
$result = $conn->query("SELECT MIN(calldate) AS mindate, MAX(calldate) AS maxdate FROM calls");
if (mysqli_num_rows($result) > 0) {
while ($row = mysqli_fetch_assoc($result)) {
$min_date = $row["mindate"];
$max_date = $row["maxdate"];
}
}
$result = $conn->query("SELECT id, terminator_id FROM providers");
if (mysqli_num_rows($result) > 0) {
while ($row = mysqli_fetch_assoc($result)) {
$terminator_providers[$row["id"]] = $row["terminator_id"];
}
}
$sql = "";
if ($originator == 'user') {
$sql = "SELECT id, username as 'originator_name', id as 'user_id' FROM users ORDER BY originator_name ASC";
} else if ($originator == 'device') {
if ($system == "m2") {
$sql = "SELECT id, description as 'originator_name', user_id FROM devices WHERE op = 1 ORDER BY originator_name ASC";
} else {
$sql = "SELECT id, description as 'originator_name', user_id FROM devices ORDER BY originator_name ASC";
}
}
$result = $conn->query($sql);
echo "";
if ($clear == 0) {
$query_filter = "";
$query_separator = "";
$field_originator = "user_id";
$field_terminator = "provider_id";
if ($originator == 'device') {
$field_originator = "src_device_id";
}
if ($terminator == 'device') {
if ($system == "m2") {
$field_terminator = "dst_device_id";
} else {
$field_terminator = "provider_id";
}
}
$query_filter = $query_filter . "{ \"range\" : { \"calldate\" : { \"gte\" : \"$date_from\", \"lte\" : \"$date_till\" } } }";
$query_separator = ", ";
if ($selected_originator >= 0) {
$query_filter = $query_filter . "$query_separator{ \"term\" : { \"$field_originator\" : \"$selected_originator\" } }";
$query_separator = ", ";
}
if (!($system == "mor" && $terminator == "user")) {
if ($selected_terminator >= 0) {
$query_filter = $query_filter . "$query_separator{ \"term\" : { \"$field_terminator\" : \"$selected_terminator\" } }";
$query_separator = ", ";
}
}
if (strlen($prefix) || $prefix_wildcard) {
if ($prefix_wildcard) {
if (strlen($prefix)) {
$query_filter = $query_filter . "$query_separator{ \"prefix\" : { \"prefix\" : \"$prefix\" } }";
$query_separator = ", ";
}
} else {
$query_filter = $query_filter . "$query_separator{ \"term\" : { \"prefix\" : \"$prefix\" } }";
$query_separator = ", ";
}
}
if ($selected_originator >= -1 && $selected_terminator < -1) {
$field_terminator = $field_originator;
} else if ($selected_originator < -1 && $selected_terminator >= -1) {
$field_originator = $field_terminator;
}
$field_prefix = $field_terminator;
if (strlen($prefix) || $prefix_wildcard) {
$field_prefix = "prefix";
}
if ($selected_originator == -2 && $selected_terminator == -2 && (strlen($prefix) || $prefix_wildcard)) {
$field_originator = "prefix";
$field_terminator = "prefix";
}
$json = "
{
\"size\" : 0,
\"query\" : {
\"filtered\" : {
\"filter\" : {
\"bool\" : {
\"must\" : [
$query_filter
]
}
}
}
},
\"aggregations\" : {
\"agg_result_1\" : {
\"terms\" : {
\"field\" : \"$field_originator\",
\"order\" : { \"_term\" : \"asc\" },
\"size\" : 0
},
\"aggregations\" : {
\"agg_result_2\" : {
\"terms\" : {
\"field\" : \"$field_terminator\",
\"order\" : { \"_term\" : \"asc\" },
\"size\" : 0
},
\"aggregations\" : {
\"agg_result_3\" : {
\"terms\" : {
\"field\" : \"$field_prefix\",
\"order\" : { \"_term\" : \"asc\" },
\"size\" : 0
},
\"aggregations\" : {
\"agg_result_4\" : {
\"filter\" : {
\"term\" : {
\"hangupcause\" : 16
}
},
\"aggregations\" : {
\"total_originator_price\" : {
\"sum\" : {
\"field\" : \"user_price\"
}
},
\"total_billsec\" : {
\"sum\" : {
\"field\" : \"billsec\"
}
},
\"total_originator_billsec\" : {
\"sum\" : {
\"field\" : \"user_billsec\"
}
},
\"total_terminator_billsec\" : {
\"sum\" : {
\"field\" : \"provider_billsec\"
}
},
\"total_terminator_price\" : {
\"sum\" : {
\"field\" : \"provider_price\"
}
}
}
}
}
}
}
}
}
}
}
}
";
$es_query = $json;
if ($selected_terminator >= -1 || $selected_originator >= -1 || strlen($prefix) || $prefix_wildcard) {
$i = 0;
$count = 0;
$es_result = json_decode(shell_exec("curl -XGET 'http://$eshost:$esport/$dbname/calls/_search?search_type=count&query_cache=true&pretty' -d '$es_query'"), true);
if ($debug) {
echo "Request
";
echo "" . "curl -XGET 'http://$eshost:$esport/$dbname/calls/_search?search_type=count&query_cache=true&pretty' -d '$es_query'" . "
";
echo "Response
";
echo print_r($es_result) . "
";
}
if (isset($es_result['hits']['total'])) {
$total_records = $es_result['hits']['total'];
echo "Took: " . $es_result['took'] . " ms
";
echo "
Aggregations
";
while (isset($es_result['aggregations']['agg_result_1']['buckets'][$i])) {
$j = 0;
while (isset($es_result['aggregations']['agg_result_1']['buckets'][$i]['agg_result_2']['buckets'][$j])) {
$k = 0;
while (isset($es_result['aggregations']['agg_result_1']['buckets'][$i]['agg_result_2']['buckets'][$j]['agg_result_3']['buckets'][$k])) {
$originator_id = $es_result['aggregations']['agg_result_1']['buckets'][$i]['key'];
$terminator_id = $es_result['aggregations']['agg_result_1']['buckets'][$i]['agg_result_2']['buckets'][$j]['key'];
$data_prefix = $es_result['aggregations']['agg_result_1']['buckets'][$i]['agg_result_2']['buckets'][$j]['agg_result_3']['buckets'][$k]['key'];
$calls = $es_result['aggregations']['agg_result_1']['buckets'][$i]['agg_result_2']['buckets'][$j]['agg_result_3']['buckets'][$k]['doc_count'];
$billsec = $es_result['aggregations']['agg_result_1']['buckets'][$i]['agg_result_2']['buckets'][$j]['agg_result_3']['buckets'][$k]['agg_result_4']['total_billsec']['value'];
$originator_billsec = $es_result['aggregations']['agg_result_1']['buckets'][$i]['agg_result_2']['buckets'][$j]['agg_result_3']['buckets'][$k]['agg_result_4']['total_originator_billsec']['value'];
$terminator_billsec = $es_result['aggregations']['agg_result_1']['buckets'][$i]['agg_result_2']['buckets'][$j]['agg_result_3']['buckets'][$k]['agg_result_4']['total_terminator_billsec']['value'];
$originator_price = $es_result['aggregations']['agg_result_1']['buckets'][$i]['agg_result_2']['buckets'][$j]['agg_result_3']['buckets'][$k]['agg_result_4']['total_originator_price']['value'];
$terminator_price = $es_result['aggregations']['agg_result_1']['buckets'][$i]['agg_result_2']['buckets'][$j]['agg_result_3']['buckets'][$k]['agg_result_4']['total_terminator_price']['value'];
$answered_calls = $es_result['aggregations']['agg_result_1']['buckets'][$i]['agg_result_2']['buckets'][$j]['agg_result_3']['buckets'][$k]['agg_result_4']['doc_count'];
if ($selected_originator == -2) $originator_id = -2;
if ($selected_terminator == -2) $terminator_id = -2;
if ($system == "mor" && $terminator == "user" && $selected_terminator != -2) {
$terminator_id = $terminator_providers[$terminator_id];
}
if ($system == "mor" && $terminator == "user" && $selected_terminator > -1) {
if ($terminator_id != $selected_terminator) {
$k++;
continue;
}
}
$data[$count]['originator_id'] = $originator_id;
$data[$count]['terminator_id'] = $terminator_id;
$data[$count]['prefix'] = $data_prefix;
$data[$count]['billsec'] = $billsec;
$data[$count]['originator_billsec'] = $originator_billsec;
$data[$count]['terminator_billsec'] = $terminator_billsec;
$data[$count]['originator_price'] = $originator_price;
$data[$count]['terminator_price'] = $terminator_price;
$data[$count]['calls'] = $calls;
$data[$count]['answered_calls'] = $answered_calls;
$count++;
$k++;
}
$j++;
}
$i++;
}
} else {
echo "
Aggregations
";
}
if (isset($data[0])) {
echo "";
$reported_too_many = 0;
$j = 0;
while (isset($data[$j])) {
$asr = 0;
$acd = 0;
if ($j < $max_records) {
if ($data[$j]['calls']) $asr = ($data[$j]['answered_calls'] / $data[$j]['calls']) * 100;
if ($data[$j]['answered_calls']) $acd = $data[$j]['billsec'] / $data[$j]['answered_calls'];
if (strlen($prefix) || $prefix_wildcard) {
echo "" . $data[$j]['prefix'] . "
";
} else {
echo "-
";
}
if (isset($originator_usernames[$data[$j]['originator_id']])) {
echo "" . substr($originator_usernames[$data[$j]['originator_id']], 0, 18) . "
";
} else {
echo "Unkown
";
}
if (isset($terminator_usernames[$data[$j]['terminator_id']])) {
echo "" . substr($terminator_usernames[$data[$j]['terminator_id']], 0, 18) . "
";
} else {
echo "Unkown
";
}
echo "" . minsec($data[$j]['billsec']) . "
";
echo "" . minsec($data[$j]['originator_billsec']) . "
";
echo "" . minsec($data[$j]['terminator_billsec']) . "
";
echo "" . round($data[$j]['originator_price'], 2) . "
";
echo "" . round($data[$j]['terminator_price'], 2) . "
";
echo "" . $data[$j]['answered_calls'] . "
";
echo "" . $data[$j]['calls'] . "
";
echo "" . round($asr, 2) . " %
";
echo "" . minsec($acd) . "
";
} else {
if ($reported_too_many == 0) {
echo "
Result truncated, too many matching records found. Please use more specific request.
";
$reported_too_many = 1;
}
}
$asr = 0;
$acd = 0;
if ($calls) $asr = round(($answered_calls / $calls));
if ($answered_calls) $acd = round(($originator_billsec / $answered_calls));
$total_calls += $data[$j]['calls'];
$total_answered_calls += $data[$j]['answered_calls'];
$total_billsec += $data[$j]['billsec'];
$total_originator_billsec += $data[$j]['originator_billsec'];
$total_terminator_billsec += $data[$j]['terminator_billsec'];
$total_originator_price += $data[$j]['originator_price'];
$total_terminator_price += $data[$j]['terminator_price'];
$j++;
}
$total_asr = 0;
$total_acd = 0;
if ($total_calls) $total_asr = ($total_answered_calls / $total_calls) * 100;
if ($total_answered_calls) $total_acd = $total_billsec / $total_answered_calls;
echo "
";
} else {
echo "No results found...";
}
} else {
echo "
Aggregations
";
echo "No results found...";
}
}
$conn->close();
?>