| <?php | <?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'); | |
| function view($sensorID) { | $sth->execute(Array($timeFrom, $timeTo)); |
| $data = Array("sensorID" => $sensorID, | return $sth->fetchAll(PDO::FETCH_ASSOC); |
| ); | |
| $timefrom = 1; | |
| $timeto = 1; | |
| $years = $this->Sensor_model->getSensorDataYears($sensorID, $timefrom, $timeto); | |
| foreach ($years as $year) { | |
| $yearsItem[] = Array("title" => $year['year'], "link" => "#", | |
| "onclick" => "getData('$sensorID'," . mktime(0, 0, 0, 1, 1, $year['year']) . "," . mktime(0, 0, 0, 12, 31, $year['year']) . ");"); | |
| } | |
| $months = $this->Sensor_model->getSensorDataMonths($sensorID, $timefrom, $timeto); | |
| foreach ($months as $month) { | |
| $monthsItem[] = Array("title" => date("F", mktime(0, 0, 0, $month['month'], 10)) . ' ' . $month['year'], "link" => "#", | |
| "onclick" => "getData('$sensorID'," . mktime(0, 0, 0, $month['month'], 1, $month['year']) . "," . strtotime("+1 month", mktime(0, 0, 0, $month['month'], 1, $month['year'])) . ");"); | |
| } | |
| $days = $this->Sensor_model->getSensorDataDays($sensorID, $timefrom, $timeto); | |
| foreach ($days as $day) { | |
| $daysItem[] = Array("title" => date("jS F", mktime(0, 0, 0, $day['month'], $day['day'])), "link" => "#", | |
| "onclick" => "getData('$sensorID'," . mktime(0, 0, 0, $day['month'], $day['day'], $day['year']) . "," . mktime(23, 59, 59, $day['month'], $day['day'], $day['year']) . ");"); | |
| } | |
| $data['sidebar'] = Array("years" => $yearsItem, "months" => $monthsItem, "days" => $daysItem | |
| ); | |
| $this->javascript = Array("flot/jquery.flot.min.js", "flot/jquery.flot.resize.min.js", "flot/jquery.flot.crosshair.min.js"); | |
| $this->content = $this->load->view('pages/sensor/view', $data, true); | |
| $this->render_page(); | |
| } | } |
| function json_graph($sensorID, $timefrom, $timeto) { | function getTGIDValuesByDay($TGID, $dayFrom, $dayTo) |
| $values = $this->Sensor_model->getSensorValuesByHour($sensorID, $timefrom, $timeto); | { |
| $label = $sensorID; | global $conn; |
| $data = Array(); | $sth = $conn->prepare('select min(time) as time, min(value), max(value), avg(value), stddev(value) from sensor_values where sensor_id = ? |
| $tzoffset = $this->get_timezone_offset("UTC"); | group by sensor_id, date_trunc(\'day\', time) order by time'); |
| foreach ($values as $value) { | |
| $data[] = Array((strtotime($value['time']) + $tzoffset) * 1000, intval($value['avg'])); | $sth->execute(Array($TGID)); |
| } | return $sth->fetchAll(PDO::FETCH_ASSOC); |
| $this->output | } |
| ->set_content_type('application/json') | function getTGIDDataYears($TGID, $timeFrom, $timeTo) |
| ->set_output(json_encode(Array("label" => $label, "data" => $data, | { |
| "previous" => Array( | global $conn; |
| "from" => $timefrom - (24 * 60 * 60), | $sth = $conn->prepare("select distinct extract('year' from call_timestamp) as year from recordings where tgid = ? order by year"); |
| "to" => $timefrom) | |
| , | $sth->execute(Array($TGID)); |
| "next" => Array( | return $sth->fetchAll(PDO::FETCH_ASSOC); |
| "to" => $timeto + (24 * 60 * 60), | |
| "from" => $timeto) | |
| ) | |
| )); | |
| } | } |
| function json_data_description($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($TGID)); | |
| return $sth->fetchAll(PDO::FETCH_ASSOC); | |
| } | |
| 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(PDO::FETCH_ASSOC); | |
| } | |
| $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") { | |
| $sth = $conn->prepare('select * from recordings | |
| order by call_timestamp desc limit 100'); | |
| $sth->execute(Array()); | |
| echo json_encode ($sth->fetchAll(PDO::FETCH_ASSOC)); | |
| } | |
| if ($action == "data_description") { | |
| $timefrom = strtotime($timefrom); | $timefrom = strtotime($timefrom); |
| $timeto = strtotime($timeto); | $timeto = strtotime($timeto); |
| $years = $this->Sensor_model->getSensorDataYears($sensorID, $timefrom, $timeto); | $years = getTGIDDataYears($TGID, $timefrom, $timeto); |
| $months = $this->Sensor_model->getSensorDataMonths($sensorID, $timefrom, $timeto); | $months = getTGIDDataMonths($TGID, $timefrom, $timeto); |
| $days = $this->Sensor_model->getSensorDataDays($sensorID, $timefrom, $timeto); | $days = getTGIDDataDays($TGID, $timefrom, $timeto); |
| $this->output | echo json_encode(Array("years" => $years, "months" => $months, "days" => $days |
| ->set_content_type('application/json') | )); |
| ->set_output(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) | |
| ) | |
| ); | |
| } | |
| ?> | ?> |