1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 | <?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"); $sth->execute(Array($sensorID)); return $sth->fetchAll(); } $action = $_REQUEST['action']; $sensorID = $_REQUEST['tgid']; $timefrom = $_REQUEST['from']; $timeto = $_REQUEST['to']; if ($action = "data_description") { $timefrom = strtotime($timefrom); $timeto = strtotime($timeto); $years = getSensorDataYears($sensorID, $timefrom, $timeto); $months = getSensorDataMonths($sensorID, $timefrom, $timeto); $days = getSensorDataDays($sensorID, $timefrom, $timeto); 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) ) ); } ?> |