public function getMessagesForTransaction($timestamp, $param)
{
/* get our DB */
$db = $this->getContainer('db');
$db->select_db(DB_CONFIGURATION);
$db->dbconnect();
$trans = array();
$data = array();
$trans['call'] = getVar('call', false, $param['transaction'], 'bool');
$trans['registration'] = getVar('registration', false, $param['transaction'], 'bool');
$trans['rest'] = getVar('rest', false, $param['transaction'], 'bool');
$location = $param['location'];
$time['from'] = getVar('from', round((microtime(true) - 300) * 1000), $timestamp, 'long');
$time['to'] = getVar('to', round(microtime(true) * 1000), $timestamp, 'long');
$time['from_ts'] = floor($time['from'] / 1000);
$time['to_ts'] = round($time['to'] / 1000);
$limit_orig = getVar('limit', 100, $param, 'int');
$record_id = getVar('id', 0, $param['search'], 'int');
$callids = getVar('callid', array(), $param['search'], 'array');
$b2b = getVar('b2b', false, $param['search'], 'bool');
$callwhere = array();
$utils['logic_or'] = getVar('logic', false, array_key_exists('query', $param) ? $param['query'] : array(), 'bool');
$and_or = $utils['logic_or'] ? " OR " : " AND ";
$search = array();
/* make array */
$search['callid'] = implode(";", $callids);
$callwhere = generateWhere($search, $and_or, $db, $b2b);
$nodes = array();
if (SINGLE_NODE == 1) {
$nodes[] = array("dbname" => DB_HOMER, "name" => "single");
} else {
foreach ($lnodes as $lnd) {
$nodes[] = $this->getNode($lnd['name']);
}
}
foreach ($nodes as $node) {
$db->dbconnect_node($node);
$limit = $limit_orig;
$ts = $time['from_ts'];
for ($ts = $time['from_ts']; $ts < $time['to_ts']; $ts += 86400) {
foreach ($this->query_types as $query_type) {
if ($trans[$query_type]) {
if ($limit < 1) {
break;
}
$order = " order by id DESC LIMIT " . $limit;
$table = "sip_capture_" . $query_type . "_" . gmdate("Ymd", $ts);
$query = "SELECT t.*, '" . $query_type . "' as trans,'" . $node['name'] . "' as dbnode, hcs.alias as source_alias, hcd.alias as destination_alias";
$query .= " FROM " . $table . " as t";
$query .= " LEFT JOIN " . DB_CONFIGURATION . ".alias as hcs ON t.source_ip=hcs.ip AND t.source_port=hcs.port";
$query .= " LEFT JOIN " . DB_CONFIGURATION . ".alias as hcd ON t.destination_ip=hcd.ip AND t.destination_port=hcs.port";
$query .= " WHERE (t.date BETWEEN FROM_UNIXTIME(" . $time['from_ts'] . ") AND FROM_UNIXTIME(" . $time['to_ts'] . "))";
if (count($callwhere)) {
$query .= " AND ( " . implode(" AND ", $callwhere) . ")";
}
$noderows = $db->loadObjectArray($query . $order);
$data = array_merge($data, $noderows);
$limit -= count($noderows);
}
}
}
}
/* sorting */
usort($data, create_function('$a, $b', 'return $a["micro_ts"] > $b["micro_ts"] ? 1 : -1;'));
return $data;
}
public function doStatisticUserAgent($timestamp, $param)
{
/* auth */
if (count($adata = $this->getLoggedIn())) {
return $adata;
}
/* get our DB */
$db = $this->getContainer('db');
$db->select_db(DB_STATISTIC);
$db->dbconnect();
$data = array();
$search = array();
$callwhere = array();
$calldata = array();
$arrwhere = "";
foreach ($param['filter'] as $key => $filter) {
$search[$key]['method'] = getVar('method', NULL, $filter, 'string');
$search[$key]['useragent'] = getVar('useragent', NULL, $filter, 'string');
$callwhere = generateWhere($search[$key], 1, $db, 0);
if (count($callwhere)) {
$calldata[] = "(" . implode(" AND ", $callwhere) . ")";
}
}
if (count($calldata)) {
$arrwhere = " AND (" . implode(" OR ", $calldata) . ")";
}
$time['from'] = getVar('from', round((microtime(true) - 300) * 1000), $timestamp, 'long');
$time['to'] = getVar('to', round(microtime(true) * 1000), $timestamp, 'long');
$time['from_ts'] = intval($time['from'] / 1000);
$time['to_ts'] = intval($time['to'] / 1000);
$and_or = getVar('orand', NULL, $param['filter'], 'string');
$limit = getVar('limit', 500, $param, 'int');
$total = getVar('total', false, $param, 'bool');
$order = "";
if ($total) {
$fields = "id, UNIX_TIMESTAMP(`from_date`) as from_ts, UNIX_TIMESTAMP(`to_date`) as to_ts, useragent, method, COUNT(id) as cnt, SUM(total) as total";
$order .= " GROUP BY useragent";
} else {
$fields = "id, UNIX_TIMESTAMP(`from_date`) as from_ts, UNIX_TIMESTAMP(`to_date`) as to_ts, useragent, method, total";
}
$order .= " order by id DESC";
$table = "stats_useragent";
$query = "SELECT " . $fields . " FROM " . $table . " WHERE (`to_date` BETWEEN FROM_UNIXTIME(" . $time['from_ts'] . ") AND FROM_UNIXTIME(" . $time['to_ts'] . "))";
$query .= $arrwhere;
$query .= $order;
$data = $db->loadObjectArray($query);
/* sorting */
//usort($data, create_function('$a, $b', 'return $a["micro_ts"] > $b["micro_ts"] ? 1 : -1;'));
$answer = array();
if (empty($data)) {
$answer['sid'] = session_id();
$answer['auth'] = 'true';
$answer['status'] = 200;
$answer['message'] = 'no data';
$answer['data'] = $data;
$answer['count'] = count($data);
} else {
$answer['status'] = 200;
$answer['sid'] = session_id();
$answer['auth'] = 'true';
$answer['message'] = 'ok';
$answer['data'] = $data;
$answer['count'] = count($data);
}
return $answer;
return $answer;
}
public function doEditNode($param)
{
/* auth */
if (count($adata = $this->getLoggedIn())) {
return $adata;
}
/* get our DB */
$db = $this->getContainer('db');
$db->select_db(DB_CONFIGURATION);
$db->dbconnect();
$data = array();
$search = array();
$callwhere = array();
$calldata = array();
$arrwhere = "";
$update['status'] = getVar('status', true, $param, 'bool');
$update['host'] = getVar('host', '127.0.0.1', $param, 'string');
$update['dbname'] = getVar('dbname', 'homer_data', $param, 'string');
$update['dbport'] = getVar('dbport', 3306, $param, 'int');
$update['dbusername'] = getVar('dbusername', 'homer_user', $param, 'string');
$update['dbpassword'] = getVar('dbpassword', '', $param, 'string');
$update['dbtables'] = getVar('dbtables', '', $param, 'string');
$update['name'] = getVar('name', '', $param, 'string');
$id = getVar('id', 0, $param, 'int');
$exten = "";
$callwhere = generateWhere($update, 1, $db, 0);
if (count($callwhere)) {
$exten .= implode(", ", $callwhere);
}
$table = "node";
$query = "UPDATE " . $table . " SET " . $exten . " WHERE id=" . $id;
$db->executeQuery($query);
$answer = $this->getNode("");
return $answer;
}
public function doQualityReport($id, $timestamp, $param)
{
/* get our DB */
$db = $this->getContainer('db');
$data = array();
$search = array();
$lnodes = array();
$callwhere = array();
//if(array_key_exists('node', $param)) $lnodes = $param['node'];
if (isset($param['location'])) {
$lnodes = $param['location']['node'];
}
$time['from'] = getVar('from', round((microtime(true) - 300) * 1000), $timestamp, 'long');
$time['to'] = getVar('to', round(microtime(true) * 1000), $timestamp, 'long');
$time['from_ts'] = floor($time['from'] / 1000);
$time['to_ts'] = round($time['to'] / 1000);
$time['from_ts'] -= 600;
$time['to_ts'] += 60;
/* search fields */
$node = getVar('node', NULL, $param['search'], 'string');
$type = getVar('type', -1, $param['search'], 'int');
$proto = getVar('proto', -1, $param['search'], 'int');
$family = getVar('family', -1, $param['search'], 'int');
$and_or = getVar('orand', NULL, $param['search'], 'string');
$limit_orig = getVar('limit', 100, $param, 'int');
$callids = getVar('callid', array(), $param['search'], 'array');
$search['correlation_id'] = implode(";", $callids);
$answer = array();
if (empty($callids)) {
$answer['sid'] = session_id();
$answer['auth'] = 'true';
$answer['status'] = 200;
$answer['message'] = 'no data';
$answer['data'] = $data;
$answer['count'] = count($data);
return $answer;
}
$nodes = array();
if (SINGLE_NODE == 1) {
$nodes[] = array("dbname" => DB_HOMER, "name" => "single");
} else {
foreach ($lnodes as $lnd) {
$nodes[] = $this->getNode($lnd['name']);
}
}
foreach ($nodes as $node) {
$db->dbconnect_node($node);
$limit = $limit_orig;
if (empty($callwhere)) {
$callwhere = generateWhere($search, $and_or, $db, 0);
}
for ($ts = $time['from_ts']; $ts < $time['to_ts']; $ts += 86400) {
$table = "report_capture";
$query = "SELECT *, '" . $node['name'] . "' as dbnode FROM " . $table . " WHERE (`date` BETWEEN FROM_UNIXTIME(" . $time['from_ts'] . ") AND FROM_UNIXTIME(" . $time['to_ts'] . "))";
if (count($callwhere)) {
$query .= " AND ( " . implode(" AND ", $callwhere) . ")";
}
$noderows = $db->loadObjectArray($query);
$data = array_merge($data, $noderows);
$limit -= count($noderows);
}
}
/* sorting */
usort($data, create_function('$a, $b', 'return $a["micro_ts"] > $b["micro_ts"] ? 1 : -1;'));
$allowreport = array("LocalAddr", "RemoteAddr", "PacketLoss", "Delay", "QualityEst");
foreach ($data as $key => $row) {
if ($row['type'] == 1 && $id != "raw") {
$ldata = array();
$mas = preg_split("/\r\n\r\n/", $row['msg']);
$data[$key]['msg'] = $mas[1];
$drs = preg_split("/\r\n/", $mas[1]);
foreach ($drs as $k => $d) {
$v = preg_split("/:/", $d);
$a = explode(' ', $v[1]);
/* short report */
if ($id == "short" && !in_array($v[0], $allowreport)) {
continue;
}
$dval = array();
foreach ($a as $are) {
$b = explode('=', $are);
if (!empty($b[0])) {
if ($b[1] == null) {
$b[1] = $b[0];
}
$dval[$b[0]] = $b[1];
}
}
if (!empty($v[0])) {
$ldata[$v[0]] = $dval;
}
}
$data[$key]['msg'] = $ldata;
}
}
if (empty($data)) {
$answer['sid'] = session_id();
$answer['auth'] = 'true';
$answer['status'] = 200;
$answer['message'] = 'no data';
$answer['data'] = $data;
$answer['count'] = count($data);
} else {
$answer['status'] = 200;
$answer['sid'] = session_id();
$answer['auth'] = 'true';
$answer['message'] = 'ok';
$answer['data'] = $data;
$answer['count'] = count($data);
}
return $answer;
}
function updateUser($param)
{
if (!isset($_SESSION['loggedin'])) {
$_SESSION['loggedin'] = '-1';
}
if ($_SESSION['loggedin'] == "-1") {
return array();
}
/* get our DB */
$mydb = $this->getContainer('db');
$mydb->select_db(DB_CONFIGURATION);
$mydb->dbconnect();
$data = array();
$search = array();
$callwhere = array();
$calldata = array();
$update['department'] = getVar('department', '', $param, 'string');
$update['email'] = getVar('email', '', $param, 'string');
$update['firstname'] = getVar('firstname', '', $param, 'string');
$update['lastname'] = getVar('lastname', '', $param, 'string');
$password = getVar('password', '', $param, 'string');
$uid = getVar('uid', 0, $param, 'int');
$exten = "";
$callwhere = generateWhere($update, 1, $mydb, 0);
if (count($callwhere)) {
if (strlen($password) > 0) {
$exten = "`password` = PASSWORD('" . $password . "'),";
}
$exten .= implode(", ", $callwhere);
}
$query = $mydb->makeQuery("UPDATE " . $this->user_table . " SET " . $exten . " WHERE " . $this->id_column . " = ? limit 1;", $_SESSION['loggedin']);
$mydb->executeQuery($query);
return $this->getUser();
}
public function getMessagesForTransaction($timestamp, $param)
{
/* get our DB */
$db = $this->getContainer('db');
$db->select_db(DB_CONFIGURATION);
$db->dbconnect();
$trans = array();
$data = array();
$lnodes = array();
if (isset($param['location'])) {
$lnodes = $param['location']['node'];
}
$trans['call'] = getVar('call', false, $param['transaction'], 'bool');
$trans['registration'] = getVar('registration', false, $param['transaction'], 'bool');
$trans['rest'] = getVar('rest', false, $param['transaction'], 'bool');
/* default transaction */
if (!$trans['call'] && !$trans['registration'] && !$trans['rest']) {
$trans['rest'] = true;
$trans['registration'] = true;
$trans['call'] = true;
}
$location = $param['location'];
$time['from'] = getVar('from', round((microtime(true) - 300) * 1000), $timestamp, 'long');
$time['to'] = getVar('to', round(microtime(true) * 1000), $timestamp, 'long');
$time['from_ts'] = floor($time['from'] / 1000);
$time['to_ts'] = round($time['to'] / 1000);
//workaround for BYE click
$time['from_ts'] -= 600;
$limit_orig = getVar('limit', 100, $param['search'], 'int');
if ($limit_orig <= 0) {
$limit_orig = 100;
}
$record_id = getVar('id', 0, $param['search'], 'int');
$callids = getVar('callid', array(), $param['search'], 'array');
$b2b = getVar('b2b', true, $param['search'], 'bool');
$uniq = getVar('uniq', false, $param['search'], 'bool');
$callwhere = array();
$utils['logic_or'] = getVar('logic', false, array_key_exists('query', $param) ? $param['query'] : array(), 'bool');
$and_or = $utils['logic_or'] ? " OR " : " AND ";
$search = array();
/* make array */
$search['callid'] = implode(";", $callids);
$callwhere = generateWhere($search, $and_or, $db, $b2b);
$nodes = array();
if (SINGLE_NODE == 1) {
$nodes[] = array("dbname" => DB_HOMER, "name" => "single");
} else {
foreach ($lnodes as $lnd) {
$nodes[] = $this->getNode($lnd['name']);
}
}
foreach ($nodes as $node) {
$db->dbconnect_node($node);
$limit = $limit_orig;
$ts = $time['from_ts'];
for ($ts = $time['from_ts']; $ts < $time['to_ts']; $ts += 86400) {
foreach ($this->query_types as $query_type) {
if ($trans[$query_type]) {
if ($limit < 1) {
break;
}
$order = " order by id DESC LIMIT " . $limit;
$table = "sip_capture_" . $query_type;
if (DB_TABLE_ROTATE) {
$table .= "_" . gmdate("Ymd", $ts);
}
$query = "SELECT t.*, '" . $query_type . "' as trans,'" . $node['name'] . "' as dbnode";
if ($uniq) {
$query .= ", MD5(msg) as md5sum";
}
$query .= " FROM " . $table . " as t";
$query .= " WHERE (t.date BETWEEN FROM_UNIXTIME(" . $time['from_ts'] . ") AND FROM_UNIXTIME(" . $time['to_ts'] . "))";
if (count($callwhere)) {
$query .= " AND ( " . implode(" AND ", $callwhere) . ")";
}
$noderows = $db->loadObjectArray($query . $order);
$data = array_merge($data, $noderows);
$limit -= count($noderows);
}
}
}
}
/* apply aliases */
$this->applyAliases($data);
if ($uniq) {
$message = array();
foreach ($data as $key => $row) {
if (isset($message[$row['md5sum']])) {
unset($data[$key]);
} else {
$message[$row['md5sum']] = $row['node'];
}
}
}
/* sorting */
usort($data, create_function('$a, $b', 'return $a["micro_ts"] > $b["micro_ts"] ? 1 : -1;'));
return $data;
}
public function getRTCForTransaction($timestamp, $param)
{
/* get our DB */
$db = $this->getContainer('db');
$db->select_db(DB_CONFIGURATION);
$db->dbconnect();
$data = array();
$search = array();
$lnodes = array();
$answer = array();
$callwhere = array();
//if(array_key_exists('node', $param)) $lnodes = $param['node'];
if (isset($param['location'])) {
$lnodes = $param['location']['node'];
}
$time['from'] = getVar('from', round((microtime(true) - 300) * 1000), $timestamp, 'long');
$time['to'] = getVar('to', round(microtime(true) * 1000), $timestamp, 'long');
$time['from_ts'] = floor($time['from'] / 1000);
$time['to_ts'] = round($time['to'] / 1000);
$time['from_ts'] -= 600;
$time['to_ts'] += 60;
/* search fields */
$type = getVar('uniq', -1, $param['search'], 'int');
$node = getVar('node', NULL, $param['search'], 'string');
$proto = getVar('proto', -1, $param['search'], 'int');
$family = getVar('family', -1, $param['search'], 'int');
$and_or = getVar('orand', NULL, $param['search'], 'string');
$limit_orig = getVar('limit', 100, $param, 'int');
$callids = getVar('callid', array(), $param['search'], 'array');
$mapsCallid = array();
$cn = count($callids);
for ($i = 0; $i < $cn; $i++) {
$mapsCallid[$callids[$i]] = $callids[$i];
if (BLEGCID == "b2b") {
$length = strlen(BLEGTAIL);
if (substr($callids[$i], -$length) == BLEGTAIL) {
$k = substr($callids[$i], 0, -$length);
$mapsCallid[$k] = $k;
} else {
$k = $callids[$i] . BLEGTAIL;
$mapsCallid[$k] = $k;
}
$s = substr($k, 0, -1);
$mapsCallid[$s] = $s;
}
$k = substr($callids[$i], 0, -1);
$mapsCallid[$k] = $k;
}
$answer = array();
if (empty($mapsCallid)) {
$answer['sid'] = session_id();
$answer['auth'] = 'true';
$answer['status'] = 200;
$answer['message'] = 'no data';
$answer['data'] = $data;
$answer['count'] = count($data);
return $answer;
}
$search['correlation_id'] = implode(";", array_keys($mapsCallid));
$nodes = array();
if (SINGLE_NODE == 1) {
$nodes[] = array("dbname" => DB_HOMER, "name" => "single");
} else {
foreach ($lnodes as $lnd) {
$nodes[] = $this->getNode($lnd['name']);
}
}
foreach ($nodes as $node) {
$db->dbconnect_node($node);
$limit = $limit_orig;
if (empty($callwhere)) {
$callwhere = generateWhere($search, $and_or, $db, 0);
}
$table = "webrtc_capture";
$query = "SELECT *, '" . $node['name'] . "' as dbnode, (UNIX_TIMESTAMP(date)) as unixts FROM " . $table . " WHERE (`date` BETWEEN FROM_UNIXTIME(" . $time['from_ts'] . ") AND FROM_UNIXTIME(" . $time['to_ts'] . "))";
if (count($callwhere)) {
$query .= " AND ( " . implode(" AND ", $callwhere) . ")";
}
$noderows = $db->loadObjectArray($query);
$data = array_merge($data, $noderows);
$limit -= count($noderows);
}
/* sorting */
usort($data, create_function('$a, $b', 'return $a["micro_ts"] > $b["micro_ts"] ? 1 : -1;'));
if (empty($data)) {
$answer['sid'] = session_id();
$answer['auth'] = 'true';
$answer['status'] = 200;
$answer['message'] = 'no data';
$answer['data'] = $data;
$answer['count'] = count($data);
} else {
$answer['status'] = 200;
$answer['sid'] = session_id();
$answer['auth'] = 'true';
$answer['message'] = 'ok';
$answer['data'] = $data;
$answer['count'] = count($data);
}
return $data;
}
function generateUpdate($table, $columns, $oriData, $dataPost, $whereCols)
{
unset($oriData["doc"]);
unset($oriData["toc"]);
unset($oriData["tsoc"]);
$sql = "UPDATE {$table} SET ";
$oriKeys = array_keys($oriData);
$wKeys = array_keys($whereCols);
for ($k = 0; $k < sizeof($columns[0]); $k++) {
$val = "";
$col = $columns[0][$k];
if (in_array($col, $wKeys)) {
} elseif (isset($dataPost[$col])) {
$val = $dataPost[$col];
$val = getData($val, $columns[1][$k]);
$sql .= "{$col}={$val}, ";
} elseif (in_array($col, $oriKeys)) {
$val = $oriData[$col];
$val = getData($val, $columns[1][$k]);
$sql .= "{$col}={$val}, ";
}
}
$sql = trim($sql);
if (strpos($sql, ",", strlen($sql) - 1) == strlen($sql) - 1) {
$sql = substr($sql, 0, strlen($sql) - 1);
}
$sqlWhere = generateWhere($whereCols);
if (strlen($sqlWhere) > 0) {
$sql = "{$sql} WHERE {$sqlWhere}";
}
return $sql;
}
