public function init()
{
$cur_time = strtotime(date('Y-m-d H:i:s'));
$some_time_ago = $cur_time - 7200;
$this->all_stations = Station::getList('all', false);
$this->date_from = date('m/d/Y', $some_time_ago);
$this->date_to = date('m/d/Y', $cur_time);
$this->time_from = date('H:i', $some_time_ago);
$this->time_to = date('H:i', $cur_time);
$this->interval = 30;
if ($this->all_stations) {
foreach ($this->all_stations as $key => $value) {
$this->stations[$value['station_id']] = $value['station_id_code'] . ' ' . $value['display_name'];
}
}
$this->do_import = 0;
$this->station_id = $this->all_stations[0]['station_id'];
return parent::init();
}
public function init()
{
$this->getFromMemory();
$this->all_stations = Station::getList('rain');
$handler = array();
SensorDBHandler::handlerWithFeature($handler, 'rg');
$features = array_shift($handler)->features;
if ($this->all_stations) {
foreach ($this->all_stations as $key => $value) {
$this->all_stations[$key]['filter_limit_max'] = round($features['rain']->filter_max / 60 * $this->rate_volume, 2);
$this->all_stations[$key]['filter_limit_min'] = round($features['rain']->filter_min / 60 * $this->rate_volume, 2);
$this->all_stations[$key]['filter_limit_diff'] = round($features['rain']->filter_diff / 60 * $this->rate_volume, 2);
// $this->all_stations[$key]['filter_limit_max'] = round(($value['filter_limit_max']/60) * $this->rate_volume,2);
// $this->all_stations[$key]['filter_limit_min'] = round(($value['filter_limit_min']/60) * $this->rate_volume,2);
// $this->all_stations[$key]['filter_limit_diff'] = round(($value['filter_limit_diff']/60) * $this->rate_volume,2);
$this->stations[$value['station_id']] = $value['station_id_code'] . ' - ' . $value['display_name'];
}
}
return parent::init();
}
public static function getStationsList()
{
$res = Station::getList("'aws','awos'", false);
$return = array();
if ($res) {
foreach ($res as $value) {
$return[$value['station_id']] = $value['display_name'] . ', ' . $value['station_id_code'];
}
}
return $return;
}
public function actionRgPanel()
{
$stations = Station::getList('rain');
$handlers = array();
SensorDBHandler::handlerWithFeature($handlers, 'rg');
$features = array_shift($handlers)->features;
if ($stations) {
foreach ($stations as $key => &$station) {
$station['sensor_details'] = array('last_msg' => '-', 'amount' => '-', 'period' => '-', 'rate' => '-', '1hr_total' => '-', 'batt_volt' => '-', '24hr_total' => '-');
$period = $station['event_message_period'];
if ($period == 5) {
$use_field = '5min_sum';
} else {
if ($period == 10) {
$use_field = '10min_sum';
} else {
if ($period == 20) {
$use_field = '20min_sum';
} else {
if ($period == 30) {
$use_field = '30min_sum';
} else {
$use_field = '60min_sum';
}
}
}
}
$station['filter_limit_max'] = round($features['rain']->filter_max / 60 * $period, 2);
$station['filter_limit_min'] = round($features['rain']->filter_min / 60 * $period, 2);
$station['filter_limit_diff'] = round($features['rain']->filter_diff / 60 * $period, 2);
$sql_groupped_table = "SELECT `station_id`, MAX(`measuring_timestamp`) AS `MaxDateTime`\n FROM `" . SensorDataMinute::model()->tableName() . "`\n WHERE `{$use_field}` > 0 AND `station_id` = '" . $station['station_id'] . "' AND `is_tmp` = 0";
$sql = "SELECT `tt`.*, `t2`.`html_code` AS `metric_html_code`\n FROM `" . SensorDataMinute::model()->tableName() . "` `tt`\n INNER JOIN `" . RefbookMetric::model()->tableName() . "` t2 ON t2.metric_id = tt.metric_id\n INNER JOIN ( {$sql_groupped_table} ) `groupedtt` ON `tt`.`station_id` = `groupedtt`.`station_id` AND `tt`.`measuring_timestamp` = `groupedtt`.`MaxDateTime`";
$res = Yii::app()->db->createCommand($sql)->queryRow();
$last_logs = ListenerLog::getLast2Messages($station['station_id']);
if ($res) {
$station['sensor_details'] = array('sensor_data_id' => $res['sensor_data_id'], 'last_msg' => date('Y-m-d H:i', strtotime($res['measuring_timestamp'])), 'amount' => $res[$use_field] * $res['bucket_size'], 'period' => $period, 'rate' => $res[$use_field] * (60 / $period) * $res['bucket_size'], '1hr_total' => $res['60min_sum'] * $res['bucket_size'], 'batt_volt' => $res['battery_voltage'] / 10, '24hr_total' => $res['1day_sum'] * $res['bucket_size'], 'metric' => $res['metric_html_code']);
if (count($last_logs) > 0) {
$station['last_tx'] = date('m/d/Y H:i', strtotime($last_logs[0]['measuring_timestamp']));
$next_expected = strtotime($last_logs[0]['measuring_timestamp']) + $period + 300;
$station['sensor_details']['next_expected'] = date('m/d/Y H:i', $next_expected);
if ($next_expected < time()) {
$station['sensor_details']['next_lates'] = 1;
}
}
if ($station['filter_limit_max'] > 0) {
if ($station['sensor_details']['amount'] >= $station['filter_limit_max']) {
$station['filter_errors'][] = "R >= " . $station['filter_limit_max'];
}
}
if ($station['filter_limit_min'] > 0) {
if ($station['sensor_details']['amount'] <= $station['filter_limit_min']) {
$station['filter_errors'][] = "R <= " . $station['filter_limit_min'];
}
}
}
}
}
$template = 'index';
$render_data = array('stations' => $stations);
if (Yii::app()->request->isAjaxRequest) {
$this->renderPartial($template, array('render_data' => $render_data));
} else {
$this->render('autorefresh', array('render_data' => $render_data, 'template' => $template));
}
}
public function actionAwsFiltered()
{
$delete = isset($_REQUEST['delete']) ? intval($_REQUEST['delete']) : null;
if ($delete) {
$obj = SensorData::model()->findByPk($delete);
if ($obj) {
$obj->delete();
It::memStatus('admin_suspicious_value_was_deleted');
}
}
$session = new CHttpSession();
$session->open();
$sess_name = 'awsfiltered_filter1';
$fparams = $session[$sess_name];
$fparams['showdata'] = false;
if ($fparams['redirect'] == true or isset($_GET['page'])) {
$fparams['showdata'] = true;
}
$stations = Station::getList("'aws','awos'", false);
$time_pattern = "/^(\\d{1,2}):(\\d{1,2})\$/";
if (!$fparams || isset($_POST['clear']) || isset($_POST['filter'])) {
$cur_time = time();
$some_time_ago = mktime(0, 0, 0, date('m', $cur_time), date('d', $cur_time), date('Y', $cur_time));
$fparams = array('station_id' => $stations[0]['station_id'], 'date_from' => date('m/d/Y', $some_time_ago), 'date_to' => date('m/d/Y', $cur_time), 'time_from' => '00:00', 'time_to' => '23:59', 'order_field' => 'date', 'order_direction' => 'DESC');
}
if (isset($_POST['filter'])) {
$fparams['station_id'] = intval($_POST['search']['station_id']);
$fparams['date_from'] = $_POST['search']['date_from'];
$fparams['date_to'] = $_POST['search']['date_to'];
if (preg_match($time_pattern, $_POST['search']['time_from'])) {
$fparams['time_from'] = $_POST['search']['time_from'];
}
if (preg_match($time_pattern, $_POST['search']['time_to'])) {
$fparams['time_to'] = $_POST['search']['time_to'];
}
}
if (isset($_REQUEST['of']) && in_array($_REQUEST['of'], array('stationid', 'date', 'sensorid', 'value'))) {
if ($_REQUEST['of'] == $fparams['order_field']) {
$fparams['order_direction'] = $fparams['order_direction'] == 'ASC' ? 'DESC' : 'ASC';
} else {
$fparams['order_direction'] = 'ASC';
}
$fparams['order_field'] = $_REQUEST['of'];
}
$session[$sess_name] = $fparams;
if ($_POST || $_REQUEST['of']) {
$fparams['showdata'] = true;
$fparams['redirect'] = true;
$session[$sess_name] = $fparams;
$this->redirect($this->createUrl('admin/awsfiltered') . (isset($_GET['page']) ? 'page/' . $_GET['page'] : ''));
} else {
$fparams['redirect'] = false;
$session[$sess_name] = $fparams;
}
/*----------- filter prepare -------------*/
$sql_where = array();
$sql_where[] = "`t1`.`is_m` = '0'";
if ($fparams['date_from']) {
$sql_where[] = "`t1`.`measuring_timestamp` >= '" . date('Y-m-d H:i:s', strtotime($fparams['date_from'] . ' ' . $fparams['time_from'])) . "'";
}
if ($fparams['date_to']) {
$sql_where[] = "`t1`.`measuring_timestamp` <= '" . date('Y-m-d H:i:s', strtotime($fparams['date_to'] . ' ' . $fparams['time_to'])) . "'";
}
if ($fparams['station_id']) {
$sql_where[] = "t1.station_id = '" . $fparams['station_id'] . "'";
}
$sql_where_str = count($sql_where) ? " AND " . implode(' AND ', $sql_where) . " " : "";
if ($fparams['order_field'] == 'date') {
$sql_order = "`t1`.`measuring_timestamp` " . $fparams['order_direction'];
} elseif ($fparams['order_field'] == 'stationid') {
$sql_order = "`t1`.`station_id` " . $fparams['order_direction'];
} elseif ($fparams['order_field'] == 'sensorid') {
$sql_order = "`t1`.`sensor_id` " . $fparams['order_direction'];
} elseif ($fparams['order_field'] == 'value') {
$sql_order = "CAST(`t1`.`sensor_feature_value` AS DECIMAL(15,4)) " . $fparams['order_direction'];
} else {
$sql_order = "`t1`.`measuring_timestamp` ";
}
/*----------- /end filter prepare --------*/
if ($fparams['showdata']) {
$sql_groupped = "SELECT `sensor_data_id`, `sensor_feature_id`, `measuring_timestamp`, CAST(`sensor_feature_value` AS DECIMAL(15,4)) as `sensor_feature_value`, `listener_log_id`\n FROM `" . SensorData::model()->tableName() . "`\n ORDER BY `measuring_timestamp` DESC\n LIMIT 1000";
$sql = "SELECT `t1`.`sensor_data_id`\n FROM " . SensorData::model()->tableName() . " `t1`\n LEFT JOIN ({$sql_groupped}) `gt` ON `gt`.`sensor_feature_id` = `t1`.`sensor_feature_id` AND `gt`.`measuring_timestamp` < `t1`.`measuring_timestamp`\n LEFT JOIN `" . StationSensorFeature::model()->tableName() . "` t2 ON t2.sensor_feature_id = t1.sensor_feature_id\n LEFT JOIN `" . SensorDBHandlerDefaultFeature::model()->tableName() . "` t3 ON t3.feature_code LIKE t2.feature_code\n WHERE (\n (t2.has_filter_max AND CAST(`t1`.`sensor_feature_value` AS DECIMAL(15,4)) > (t3.filter_max * IF(t2.is_cumulative, t1.period/60, 1) ) )\n OR (t2.has_filter_min AND CAST(`t1`.`sensor_feature_value` AS DECIMAL(15,4)) < (t3.filter_min * IF(t2.is_cumulative, t1.period/60, 1) ))\n OR (t2.has_filter_diff AND `gt`.`sensor_data_id` > 0 AND ABS(CAST(`gt`.`sensor_feature_value` AS DECIMAL(15,4)) - CAST(`t1`.`sensor_feature_value` AS DECIMAL(15,4))) > (t3.filter_diff * IF(t2.is_cumulative, t1.period/60, 1)))\n )\n {$sql_where_str}\n GROUP BY `t1`.`sensor_data_id`\n LIMIT 1000";
$total = count(Yii::app()->db->createCommand($sql)->queryColumn());
$pages = new CPagination($total);
$pages->pageSize = 20;
//$pages->applyLimit($criteria);
$sql = "SELECT `t1`.`sensor_data_id`, `t1`.`sensor_feature_id`, CAST(`t1`.`sensor_feature_value` AS DECIMAL(15,4)) as `sensor_feature_value`, `t1`.`measuring_timestamp`, t1.period,\n `gt`.`measuring_timestamp` AS `prev_measuring_timestamp`, `gt`.`listener_log_id` AS `prev_listener_log_id`, CAST(`gt`.`sensor_feature_value` AS DECIMAL(15,4)) as `prev_sensor_feature_value`, `gt`.`sensor_data_id` as `prev_sensor_data_id`,\n `t5`.`filter_max`, `t5`.`filter_min`, `t5`.`filter_diff`, t2.has_filter_max, t2.has_filter_min, t2.has_filter_diff, t2.is_cumulative,\n `t3`.`sensor_id_code`,\n `t4`.`station_id_code`\n FROM " . SensorData::model()->tableName() . " `t1`\n LEFT JOIN ({$sql_groupped}) `gt` ON `gt`.`sensor_feature_id` = `t1`.`sensor_feature_id` AND `gt`.`measuring_timestamp` < `t1`.`measuring_timestamp`\n LEFT JOIN `" . StationSensorFeature::model()->tableName() . "` t2 ON t2.sensor_feature_id = t1.sensor_feature_id\n LEFT JOIN `" . StationSensor::model()->tableName() . "` t3 ON t3.station_sensor_id = t1.sensor_id\n LEFT JOIN `" . Station::model()->tableName() . "` t4 ON t4.station_id = t1.station_id\n LEFT JOIN `" . SensorDBHandlerDefaultFeature::model()->tableName() . "` t5 ON t5.feature_code LIKE t2.feature_code\n WHERE (\n (t2.has_filter_max AND CAST(`t1`.`sensor_feature_value` AS DECIMAL(15,4)) > (t5.filter_max * IF(t2.is_cumulative, t1.period/60, 1) ) )\n OR (t2.has_filter_min AND CAST(`t1`.`sensor_feature_value` AS DECIMAL(15,4)) < (t5.filter_min * IF(t2.is_cumulative, t1.period/60, 1) ))\n OR (t2.has_filter_diff AND `gt`.`sensor_data_id` > 0 AND ABS(CAST(`gt`.`sensor_feature_value` AS DECIMAL(15,4)) - CAST(`t1`.`sensor_feature_value` AS DECIMAL(15,4))) > (t5.filter_diff * IF(t2.is_cumulative, t1.period/60, 1) ))\n )\n {$sql_where_str}\n GROUP BY `t1`.`sensor_data_id`\n HAVING (\n (t2.has_filter_max AND CAST(`sensor_feature_value` AS DECIMAL(15,4)) > (t5.filter_max * IF(t2.is_cumulative, t1.period/60, 1) ) )\n OR (t2.has_filter_min AND CAST(`sensor_feature_value` AS DECIMAL(15,4)) < (t5.filter_min * IF(t2.is_cumulative, t1.period/60, 1) ))\n OR (t2.has_filter_diff AND prev_sensor_data_id > 0 AND ABS(CAST(`prev_sensor_feature_value` AS DECIMAL(15,4)) - CAST(`sensor_feature_value` AS DECIMAL(15,4))) > (t5.filter_diff * IF(t2.is_cumulative, t1.period/60, 1) ))\n\t\t\t )\n ORDER BY {$sql_order}\n LIMIT " . $pages->currentPage * $pages->pageSize . ", " . $pages->pageSize;
$list = Yii::app()->db->createCommand($sql)->queryAll();
if ($list) {
foreach ($list as $key => &$value) {
$multiplyer_str = '';
$multiplyer = 1;
if ($value['is_cumulative']) {
$multiplyer_str = $value['period'] != 60 ? ' * ' . $value['period'] . 'min/60' : '';
$multiplyer = $value['period'] / 60;
}
if (isset($value['has_filter_max']) && $value['sensor_feature_value'] > $value['filter_max'] * $multiplyer) {
$value['filter_reason'][] = array('main' => 'T1 > ' . $value['filter_max'] . $multiplyer_str);
}
if (isset($value['has_filter_min']) && $value['sensor_feature_value'] < $value['filter_min'] * $multiplyer) {
$value['filter_reason'][] = array('main' => 'T1 < ' . $value['filter_min'] . $multiplyer_str);
}
if (isset($value['prev_sensor_feature_value']) && isset($value['has_filter_diff']) && abs($value['sensor_feature_value'] - $value['prev_sensor_feature_value']) > $value['filter_diff'] * $multiplyer) {
$value['filter_reason'][] = array('main' => '|T1 - T0| > ' . $value['filter_diff'] . $multiplyer_str, 'extra' => '(Previous value: ' . $value['prev_sensor_feature_value'] . ' on ' . $value['prev_measuring_timestamp'] . ')');
}
}
}
$this->render('awsfiltered', array('list' => $list, 'clean_page' => false, 'pages' => $pages, 'stations' => $stations, 'fparams' => $fparams));
} else {
$this->render('awsfiltered', array('list' => $list, 'clean_page' => true, 'stations' => $stations, 'fparams' => $fparams));
}
}
public function actionConvert()
{
$process = new ConvertDataloggerCsvToMessages();
$allStations = Station::getList('all', false);
$stations = array();
if ($allStations) {
foreach ($allStations as $key => $value) {
$stations[$value['station_id_code']] = $value['station_id_code'] . ' - ' . $value['display_name'];
}
}
$process->setSource(isset($_POST['source']) ? $_POST['source'] : '');
$process->setStation(isset($_POST['station']) ? $_POST['station'] : '');
$this->render('datalogger_convert', ['source' => $process->getSource(), 'station' => $process->getStation(), 'stations' => $stations, 'convert' => $process->getConvert()]);
}
