<?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(); |
); |
|
$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(); |
$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(); |
"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(); |
|
} |
|
|
|
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); |
$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) |
|
) |
|
); |
|
} |
|
|
|
|
|
|
?> |
?> |
|
|