--- a/calls.json.php +++ b/calls.json.php @@ -1,70 +1,78 @@ 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 group by tgid, date_trunc(\'hour\', call_timestamp) order by time'); - $sth->execute( Array($sensorID, $timeFrom, $timeTo)); - return $sth->fetchAll(); + $sth->execute( ); + //Array($TGID, $timeFrom, $timeTo) + return $sth->fetchAll(); + + } -function getSensorValuesByDay($sensorID, $dayFrom, $dayTo) { +function getTGIDValuesByDay($TGID, $dayFrom, $dayTo) { global $conn; $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'); - $sth->execute( Array($sensorID)); + $sth->execute( Array($TGID)); return $sth->fetchAll(); } -function getSensorDataYears($sensorID, $timeFrom, $timeTo) { +function getTGIDDataYears($TGID, $timeFrom, $timeTo) { global $conn; $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(); } -function getSensorDataMonths($sensorID, $timeFrom, $timeTo) { +function getTGIDDataMonths($TGID, $timeFrom, $timeTo) { 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->execute(Array($sensorID)); + $sth->execute(Array($TGID)); return $sth->fetchAll(); } -function getSensorDataDays($sensorID, $timeFrom, $timeTo) { +function getTGIDDataDays($TGID, $timeFrom, $timeTo) { 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->execute(Array($sensorID)); + $sth->execute(Array($TGID)); return $sth->fetchAll(); } -$action = $_REQUEST['action']; - $sensorID = $_REQUEST['tgid']; -$timefrom = $_REQUEST['from']; -$timeto = $_REQUEST['to']; -if ($action = "data_description") { +$action = (isset($_REQUEST['action']) ? $_REQUEST['action'] : ''); +$TGID = (isset($_REQUEST['tgid']) ? $_REQUEST['tgid'] : ''); +$timefrom = (isset($_REQUEST['from']) ? $_REQUEST['from'] : ''); +$timeto = (isset($_REQUEST['to']) ? $_REQUEST['to'] : ''); + +if ($action == "data_description") { $timefrom = strtotime($timefrom); $timeto = strtotime($timeto); - $years = getSensorDataYears($sensorID, $timefrom, $timeto); + $years = getTGIDDataYears($TGID, $timefrom, $timeto); - $months = getSensorDataMonths($sensorID, $timefrom, $timeto); - $days = getSensorDataDays($sensorID, $timefrom, $timeto); + $months = getTGIDDataMonths($TGID, $timefrom, $timeto); + $days = getTGIDDataDays($TGID, $timefrom, $timeto); echo json_encode(Array("years" => $years, "months" => $months, "days" => $days )); } -if ($action = "graph") { - $values = getSensorValuesByHour($sensorID, $timefrom, $timeto); - $label = $sensorID; +if (strpos($action,"graph") !== false) { + $values = getTGIDValuesByHour($TGID, $timefrom, $timeto); + $label = $TGID; $data = Array(); $tzoffset = get_timezone_offset("UTC"); 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, "previous" => Array( @@ -78,5 +86,7 @@ ); } + + ?>