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()]); }