| <?php | <?php |
| include('common.inc.php'); | |
| function getSensorValuesByHour($sensorID, $timeFrom, $timeTo) { | |
| global $conn; | |
| $sth = $conn->prepare( 'select 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(); | |
| } | |
| function getSensorValuesByDay($sensorID, $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)); | |
| return $sth->fetchAll(); | |
| } | |
| function getSensorDataYears($sensorID, $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)); | |
| return $sth->fetchAll(); | |
| } | |
| function getSensorDataMonths($sensorID, $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)); | |
| return $sth->fetchAll(); | |
| } | |
| function getSensorDataDays($sensorID, $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"); | |
| function json_graph($sensorID, $timefrom, $timeto) { | $sth->execute(Array($sensorID)); |
| $values = getSensorValuesByHour($sensorID, $timefrom, $timeto); | return $sth->fetchAll(); |
| $label = $sensorID; | |
| $data = Array(); | |
| $tzoffset = $this->get_timezone_offset("UTC"); | |
| foreach ($values as $value) { | |
| $data[] = Array((strtotime($value['time']) + $tzoffset) * 1000, intval($value['avg'])); | |
| } | |
| $this->output | |
| ->set_content_type('application/json') | |
| ->set_output(json_encode(Array("label" => $label, "data" => $data, | |
| "previous" => Array( | |
| "from" => $timefrom - (24 * 60 * 60), | |
| "to" => $timefrom) | |
| , | |
| "next" => Array( | |
| "to" => $timeto + (24 * 60 * 60), | |
| "from" => $timeto) | |
| ) | |
| )); | |
| } | } |
| $action = $_REQUEST['action']; | |
| function json_data_description($sensorID, $timefrom, $timeto) { | $sensorID = $_REQUEST['tgid']; |
| $timefrom = $_REQUEST['from']; | |
| $timeto = $_REQUEST['to']; | |
| if ($action = "data_description") { | |
| $timefrom = strtotime($timefrom); | $timefrom = strtotime($timefrom); |
| $timeto = strtotime($timeto); | $timeto = strtotime($timeto); |
| $years = getSensorDataYears($sensorID, $timefrom, $timeto); | $years = getSensorDataYears($sensorID, $timefrom, $timeto); |
| $months = getSensorDataMonths($sensorID, $timefrom, $timeto); | $months = getSensorDataMonths($sensorID, $timefrom, $timeto); |
| $days = getSensorDataDays($sensorID, $timefrom, $timeto); | $days = getSensorDataDays($sensorID, $timefrom, $timeto); |
| return json_encode(Array("years" => $years, "months" => $months, "days" => $days | echo json_encode(Array("years" => $years, "months" => $months, "days" => $days |
| )); | )); |
| } | |
| if ($action = "graph") { | |
| $values = getSensorValuesByHour($sensorID, $timefrom, $timeto); | |
| $label = $sensorID; | |
| $data = Array(); | |
| $tzoffset = get_timezone_offset("UTC"); | |
| foreach ($values as $value) { | |
| $data[] = Array((strtotime($value['time']) + $tzoffset) * 1000, intval($value['avg'])); | |
| } | |
| echo json_encode(Array("label" => $label, "data" => $data, | |
| "previous" => Array( | |
| "from" => $timefrom - (24 * 60 * 60), | |
| "to" => $timefrom) | |
| , | |
| "next" => Array( | |
| "to" => $timeto + (24 * 60 * 60), | |
| "from" => $timeto) | |
| ) | |
| ); | |
| } | } |
| ?> | ?> |