--- a/calls.json.php +++ b/calls.json.php @@ -1,48 +1,53 @@ prepare( 'select tgid, 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'); $sth->execute(Array($timeFrom, $timeTo)); - return $sth->fetchAll(); + return $sth->fetchAll(); } -function getTGIDValuesByDay($TGID, $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($TGID)); - return $sth->fetchAll(); + $sth->execute(Array($TGID)); + return $sth->fetchAll(); } -function getTGIDDataYears($TGID, $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($TGID)); - return $sth->fetchAll(); + return $sth->fetchAll(); } -function getTGIDDataMonths($TGID, $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($TGID)); - return $sth->fetchAll(); + return $sth->fetchAll(); } -function getTGIDDataDays($TGID, $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($TGID)); - return $sth->fetchAll(); + return $sth->fetchAll(); } $action = (isset($_REQUEST['action']) ? $_REQUEST['action'] : ''); $TGID = (isset($_REQUEST['tgid']) ? $_REQUEST['tgid'] : ''); @@ -58,23 +63,23 @@ $days = getTGIDDataDays($TGID, $timefrom, $timeto); echo json_encode(Array("years" => $years, "months" => $months, "days" => $days - )); + )); } -if (strpos($action,"graph") !== false) { +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'])); + $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( "from" => $timefrom - (24 * 60 * 60), "to" => $timefrom)