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 83 84 85 86 87 88 89 90 91 92 | <?php include('common.inc.php'); function getTGIDValuesByHour($TGID, $timeFrom, $timeTo) { global $conn; $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'); $sth->execute(Array($timeFrom, $timeTo)); return $sth->fetchAll(); } 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($TGID)); return $sth->fetchAll(); } 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($TGID)); return $sth->fetchAll(); } 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($TGID)); return $sth->fetchAll(); } 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($TGID)); return $sth->fetchAll(); } $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 = getTGIDDataYears($TGID, $timefrom, $timeto); $months = getTGIDDataMonths($TGID, $timefrom, $timeto); $days = getTGIDDataDays($TGID, $timefrom, $timeto); echo json_encode(Array("years" => $years, "months" => $months, "days" => $days )); } if (strpos($action,"graph") !== false) { $values = getTGIDValuesByHour($TGID, $timefrom, $timeto); $label = $TGID; $data = Array(); $tzoffset = get_timezone_offset("UTC"); foreach ($values as $value) { 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( "from" => $timefrom - (24 * 60 * 60), "to" => $timefrom) , "next" => Array( "to" => $timeto + (24 * 60 * 60), "from" => $timeto) ) ); } ?> |