<?php |
<?php |
include('common.inc.php'); |
include('common.inc.php'); |
function getSensorValuesByHour($sensorID, $timeFrom, $timeTo) { |
function getTGIDValuesByHour($TGID, $timeFrom, $timeTo) |
|
{ |
global $conn; |
global $conn; |
$sth = $conn->prepare( 'select min(call_timestamp) as time, count(*), min(length), max(length), avg(length), stddev(length) from recordings |
$sth = $conn->prepare('select tgid, min(call_timestamp) as time, count(*), min(length), max(length), avg(length), stddev(length) from recordings |
|
where call_timestamp between to_timestamp(?) and to_timestamp(?) |
group by tgid, date_trunc(\'hour\', call_timestamp) order by time'); |
group by tgid, date_trunc(\'hour\', call_timestamp) order by time'); |
|
|
$sth->execute( Array($sensorID, $timeFrom, $timeTo)); |
$sth->execute(Array($timeFrom, $timeTo)); |
return $sth->fetchAll(); |
return $sth->fetchAll(); |
|
|
|
|
} |
} |
|
|
function getSensorValuesByDay($sensorID, $dayFrom, $dayTo) { |
function getTGIDValuesByDay($TGID, $dayFrom, $dayTo) |
|
{ |
global $conn; |
global $conn; |
$sth = $conn->prepare('select min(time) as time, min(value), max(value), avg(value), stddev(value) from sensor_values where sensor_id = ? |
$sth = $conn->prepare('select min(time) as time, min(value), max(value), avg(value), stddev(value) from sensor_values where sensor_id = ? |
group by sensor_id, date_trunc(\'day\', time) order by time'); |
group by sensor_id, date_trunc(\'day\', time) order by time'); |
|
|
$sth->execute( Array($sensorID)); |
$sth->execute(Array($TGID)); |
return $sth->fetchAll(); |
return $sth->fetchAll(); |
} |
} |
function getSensorDataYears($sensorID, $timeFrom, $timeTo) { |
function getTGIDDataYears($TGID, $timeFrom, $timeTo) |
|
{ |
global $conn; |
global $conn; |
$sth = $conn->prepare("select distinct extract('year' from call_timestamp) as year from recordings where tgid = ? order by year"); |
$sth = $conn->prepare("select distinct extract('year' from call_timestamp) as year from recordings where tgid = ? order by year"); |
|
|
$sth->execute(Array($sensorID)); |
$sth->execute(Array($TGID)); |
return $sth->fetchAll(); |
return $sth->fetchAll(); |
} |
} |
|
|
function getSensorDataMonths($sensorID, $timeFrom, $timeTo) { |
function getTGIDDataMonths($TGID, $timeFrom, $timeTo) |
|
{ |
global $conn; |
global $conn; |
$sth = $conn->prepare("select distinct extract('month' from call_timestamp) as month, extract('year' from call_timestamp) as year from recordings where tgid = ? order by year, month"); |
$sth = $conn->prepare("select distinct extract('month' from call_timestamp) as month, extract('year' from call_timestamp) as year from recordings where tgid = ? order by year, month"); |
|
|
$sth->execute(Array($sensorID)); |
$sth->execute(Array($TGID)); |
return $sth->fetchAll(); |
return $sth->fetchAll(); |
} |
} |
|
|
function getSensorDataDays($sensorID, $timeFrom, $timeTo) { |
function getTGIDDataDays($TGID, $timeFrom, $timeTo) |
|
{ |
global $conn; |
global $conn; |
$sth = $conn->prepare("select distinct extract('day' from call_timestamp) as day, extract('month' from call_timestamp) as month, extract('year' from call_timestamp) as year from recordings where tgid = ? order by year,month,day"); |
$sth = $conn->prepare("select distinct extract('day' from call_timestamp) as day, extract('month' from call_timestamp) as month, extract('year' from call_timestamp) as year from recordings where tgid = ? order by year,month,day"); |
|
|
|
|
$sth->execute(Array($sensorID)); |
$sth->execute(Array($TGID)); |
return $sth->fetchAll(); |
return $sth->fetchAll(); |
} |
} |
$action = $_REQUEST['action']; |
$action = (isset($_REQUEST['action']) ? $_REQUEST['action'] : ''); |
$sensorID = $_REQUEST['tgid']; |
$TGID = (isset($_REQUEST['tgid']) ? $_REQUEST['tgid'] : ''); |
$timefrom = $_REQUEST['from']; |
$timefrom = (isset($_REQUEST['from']) ? $_REQUEST['from'] : ''); |
$timeto = $_REQUEST['to']; |
$timeto = (isset($_REQUEST['to']) ? $_REQUEST['to'] : ''); |
if ($action = "data_description") { |
|
|
if ($action == "data_description") { |
$timefrom = strtotime($timefrom); |
$timefrom = strtotime($timefrom); |
$timeto = strtotime($timeto); |
$timeto = strtotime($timeto); |
$years = getSensorDataYears($sensorID, $timefrom, $timeto); |
$years = getTGIDDataYears($TGID, $timefrom, $timeto); |
|
|
$months = getSensorDataMonths($sensorID, $timefrom, $timeto); |
$months = getTGIDDataMonths($TGID, $timefrom, $timeto); |
$days = getSensorDataDays($sensorID, $timefrom, $timeto); |
$days = getTGIDDataDays($TGID, $timefrom, $timeto); |
|
|
echo json_encode(Array("years" => $years, "months" => $months, "days" => $days |
echo json_encode(Array("years" => $years, "months" => $months, "days" => $days |
)); |
)); |
} |
} |
|
|
|
|
if ($action = "graph") { |
if (strpos($action, "graph") !== false) { |
$values = getSensorValuesByHour($sensorID, $timefrom, $timeto); |
$values = getTGIDValuesByHour($TGID, $timefrom, $timeto); |
$label = $sensorID; |
$label = $TGID; |
$data = Array(); |
$data = Array(); |
$tzoffset = get_timezone_offset("UTC"); |
$tzoffset = get_timezone_offset("UTC"); |
foreach ($values as $value) { |
foreach ($values as $value) { |
$data[] = Array((strtotime($value['time']) + $tzoffset) * 1000, intval($value['avg'])); |
if ($action == "graphlength") { |
|
$data[$value['tgid']][] = Array((strtotime($value['time']) + $tzoffset) * 1000, intval($value['avg'])); |
|
} else if ($action == "graphcount") { |
|
$data[$value['tgid']][] = Array((strtotime($value['time']) + $tzoffset) * 1000, intval($value['count'])); |
|
} |
} |
} |
echo json_encode(Array("label" => $label, "data" => $data, |
echo json_encode(Array("label" => $label, "data" => $data, |
"previous" => Array( |
"previous" => Array( |
"from" => $timefrom - (24 * 60 * 60), |
"from" => $timefrom - (24 * 60 * 60), |
"to" => $timefrom) |
"to" => $timefrom) |
, |
, |
"next" => Array( |
"next" => Array( |
"to" => $timeto + (24 * 60 * 60), |
"to" => $timeto + (24 * 60 * 60), |
"from" => $timeto) |
"from" => $timeto) |
) |
) |
); |
); |
} |
} |
|
|
|
|
|
|
?> |
?> |
|
|