private function checkIntegrity()
{
$this->getStation();
if (!$this->_station) {
$this->integrity_errors[] = 'unknown_station_id__' . $this->message_obj->station_id;
return false;
}
// Temporary stub
if ($this->_station->station_type != 'rain') {
$this->integrity_errors[] = 'import_works_only_for_rain';
return false;
}
if ($this->_station->station_type == 'rain') {
$parts = str_getcsv($this->message_obj->message);
if (count($parts) != 4) {
$this->integrity_errors[] = 'incorrect_data_format';
return false;
}
$date_pattern = "/^(\\d{1,2})\\/(\\d{1,2})\\/(\\d{1,2})\$/";
$time_pattern = "/^(\\d{1,2}):(\\d{1,2})\$/";
$voltage_pattern = "/^(\\d{3})\$/";
if (!preg_match($date_pattern, $parts[0]) || !preg_match($time_pattern, $parts[1]) || !preg_match($voltage_pattern, $parts[2])) {
$this->integrity_errors[] = 'incorrect_data_format';
return false;
}
$sensor = StationSensor::model()->find('station_id = :station_id', array(':station_id' => $this->message_obj->station_id));
if (!$sensor) {
$this->integrity_errors[] = 'station_dont_have_sensors';
} else {
$this->_sensor = $sensor;
return true;
}
} else {
// TO DO
}
}
protected function sensorValidate($stationId, $sensorParamArray)
{
$handler = SensorDBHandler::model()->with('features')->findAllByAttributes(array('handler_id_code' => $sensorParamArray['handler']));
$handler = $handler[0];
if (!is_object($handler)) {
$this->addError('sensor', 'handler was not found');
}
$sensor = new StationSensor();
$sensor->station_id = $stationId;
$sensor->handler_id = $handler->handler_id;
$sensor->display_name = $sensorParamArray['display_name'];
$sql = "SELECT UPPER(`sensor_id_code`) FROM `" . StationSensor::model()->tableName() . "` WHERE `station_id` = ? AND `sensor_id_code` <> ?";
$used_code_id = Yii::app()->db->createCommand($sql)->queryColumn(array($stationId, $sensor->sensor_id_code ? $sensor->sensor_id_code : ''));
for ($i = 1; $i <= 9; $i++) {
$code = $handler->default_prefix . $i;
if (!$used_code_id || !in_array($code, $used_code_id)) {
$sensor->sensor_id_code = $code;
break;
}
}
if (!$sensor->sensor_id_code) {
$this->addError('sensor', 'all numbers for sensor are busy');
}
$sensorHandler = SensorHandler::create($handler->handler_id_code);
$sensorFeatures = array();
$ft_1 = $sensorHandler->getFeatures();
$ft_2 = $sensorHandler->getExtraFeatures();
if ($ft_2) {
foreach ($ft_2 as $key => $value) {
$ft_2[$key]['is_extra'] = 1;
}
}
$handler_sensor_features = array_merge($ft_1, $ft_2);
if ($handler_sensor_features) {
foreach ($handler_sensor_features as $value) {
$sf = new StationSensorFeature();
$default = $handler->features[$value['feature_code']];
$metric = RefbookMeasurementType::model()->with('metricMain')->findByAttributes(array('code' => $value['measurement_type_code']));
$sf->feature_constant_value = isset($value['default']) ? $value['default'] : null;
if ($default) {
$sf->feature_constant_value = $default->feature_constant_value;
$sf->metric_id = $default->metric_id;
$sf->filter_max = $default->filter_max;
$sf->filter_min = $default->filter_min;
$sf->filter_diff = $default->filter_diff;
}
foreach ($sensorParamArray['features'] as $sensorParamFeature) {
if ($sensorParamFeature['feature_code'] == $value['feature_code']) {
$sf->feature_constant_value = $sensorParamFeature['feature_constant_value'];
}
}
$sf->metric_id = $metric->metricMain->metric_id;
$sf->feature_code = $value['feature_code'];
$sf->feature_display_name = $value['feature_name'];
$sf->is_constant = isset($value['is_extra']) ? 1 : 0;
$sf->comment = isset($value['comment']) ? $value['comment'] : null;
$sf->measurement_type_code = $value['measurement_type_code'];
$sf->is_cumulative = $value['is_cumulative'];
$sf->is_main = $value['is_main'];
$sf->has_filter_min = $value['has_filter_min'];
$sf->has_filter_max = $value['has_filter_max'];
$sf->has_filter_diff = $value['has_filter_diff'];
$sensorFeatures[] = $sf;
}
}
$validated = $sensor->validate();
if ($validated) {
$this->errors[] = $sensor->getErrors();
}
if ($validated and $sensorFeatures) {
foreach ($sensorFeatures as $feature) {
$feature->sensor_id = 1;
if (!$feature->validate()) {
$this->errors[] = $feature->getErrors();
}
}
}
// sensor Save Fail
}
public function generate()
{
$this->_logger->log(__METHOD__);
if ($this->errors) {
$this->_logger->log(__METHOD__, array('errors' => $this->errors));
return false;
}
$current_user_timezone = date_default_timezone_get();
$timezone_id = 'UTC';
if ($timezone_id != $current_user_timezone) {
TimezoneWork::set($timezone_id);
}
$this->report_parts = array();
$this->explanations = array();
// get sensors' values for all messages received in reporting period
$sql = "SELECT `t5`.`listener_log_id`,\r\n `t5`.`measuring_timestamp`,\r\n `t1`.`station_sensor_id`, `t1`.`sensor_id_code`, \r\n \r\n `t3`.`feature_code`, `t3`.`feature_constant_value`,\r\n `t4`.`code` AS `metric_code`, \r\n `t5`.`sensor_feature_value`, \r\n `t5`.`is_m`,\r\n `t5`.`period` AS `sensor_feature_period`,\r\n `t6`.`code` AS `value_metric_code`,\r\n `t7`.`handler_id_code`\r\n\r\n FROM `" . SensorData::model()->tableName() . "` `t5`\r\n LEFT JOIN `" . StationSensor::model()->tableName() . "` `t1` ON t1.station_sensor_id = t5.sensor_id\r\n LEFT JOIN `" . StationSensorFeature::model()->tableName() . "` `t3` ON (`t3`.`sensor_feature_id` = `t5`.`sensor_feature_id`)\r\n LEFT JOIN `" . RefbookMetric::model()->tableName() . "` `t4` ON `t4`.`metric_id` = `t3`.`metric_id`\r\n LEFT JOIN `" . RefbookMetric::model()->tableName() . "` `t6` ON `t6`.`metric_id` = `t5`.`metric_id`\r\n LEFT JOIN `" . SensorDBHandler::model()->tableName() . "` `t7` ON t7.handler_id = t1.handler_id\r\n WHERE `t5`.`station_id` = '" . $this->station_info->station_id . "' AND `t5`.`listener_log_id` IN (" . $this->schedule_process_info->listener_log_ids . ")\r\n ORDER BY `t5`.`measuring_timestamp` DESC, `t1`.`sensor_id_code` ASC, `t3`.`feature_code` ASC";
$sensor_data = Yii::app()->db->createCommand($sql)->queryAll();
$data = array();
if ($sensor_data) {
// get calculation values for all messages received in reporting period
$sql = "SELECT `t1`.`listener_log_id`,\r\n `t1`.`value`,\r\n `t3`.`handler_id_code`\r\n FROM `" . StationCalculationData::model()->tableName() . "` `t1`\r\n LEFT JOIN `" . StationCalculation::model()->tableName() . "` `t2` ON t2.calculation_id = t1.calculation_id\r\n LEFT JOIN `" . CalculationDBHandler::model()->tableName() . "` `t3` ON `t3`.`handler_id` = `t2`.`handler_id`\r\n WHERE `t2`.`station_id` = '" . $this->station_info->station_id . "' AND `t1`.`listener_log_id` IN (" . $this->schedule_process_info->listener_log_ids . ")\r\n ORDER BY `t3`.`handler_id_code`";
$res2 = Yii::app()->db->createCommand($sql)->queryAll();
if ($res2) {
foreach ($res2 as $key => $value) {
$calculations[$value['listener_log_id']][] = $value;
}
}
foreach ($sensor_data as $key => $value) {
$data[$value['listener_log_id']][] = $value;
}
// prepare $result_item array, where each line represents line in report.
foreach ($data as $key => $value) {
$result_item = array('StationId', $this->station_info->station_id_code, 'WMO AWS #', $this->station_info->wmo_block_number . $this->station_info->station_number, 'National AWS #', $this->station_info->national_aws_number, 'Tx DateTime', date('m/d/Y H:i', strtotime($value[0]['measuring_timestamp'])));
foreach ($value as $key2 => $value2) {
$handler_obj = SensorHandler::create($value2['handler_id_code']);
if (in_array($value2['handler_id_code'], array('BatteryVoltage', 'Humidity', 'Pressure', 'Temperature'))) {
$sensor_id_code = $value2['sensor_id_code'];
} else {
$sensor_id_code = $value2['sensor_id_code'] . ' (' . $handler_obj->getFeatureName($value2['feature_code']) . ')';
}
$result_item[] = $sensor_id_code;
if ($value2['is_m']) {
$result_item[] = '-';
} else {
$value2['sensor_feature_value'] = $handler_obj->applyOffset($value2['sensor_feature_value'], $this->station_info->magnetic_north_offset);
$result_item[] = str_replace(',', ' ', $handler_obj->formatValue($value2['sensor_feature_value'], $value2['feature_code']));
}
}
if (isset($calculations[$key])) {
foreach ($calculations[$key] as $key2 => $value2) {
if ($value2['handler_id_code'] === 'DewPoint') {
$result_item[] = 'DP';
} else {
if ($value2['handler_id_code'] === 'PressureSeaLevel') {
$result_item[] = 'MSL';
} else {
$result_item[] = 'Unknown calculation';
}
}
$result_item[] = str_replace(',', ' ', number_format(round($value2['value'], 1), 1));
}
}
$this->report_parts[] = $result_item;
}
}
if ($timezone_id != $current_user_timezone) {
TimezoneWork::set($current_user_timezone);
}
$this->_logger->log(__METHOD__ . ' Export generation completed.');
return true;
}
public static function addSensorsForStations(&$stations)
{
$station_ids = array_keys($stations);
$station_sensor_ids = array();
$criteria = new CDbCriteria();
$criteria->with = array('handler');
$criteria->compare('t.station_id', $station_ids);
$criteria->order = "handler.aws_panel_display_position asc, t.sensor_id_code asc";
$sensors = StationSensor::model()->findAll($criteria);
foreach ($sensors as $sensor) {
$stations[$sensor->station_id]['sensors'][$sensor->station_sensor_id] = $sensor;
$station_sensor_ids[] = $sensor->station_sensor_id;
}
return $station_sensor_ids;
}
public function getUsedSensors($station_id)
{
$sql = "SELECT t5.sensor_id_code\n FROM `" . StationCalculationVariable::model()->tableName() . "` `t1`\n LEFT JOIN `" . StationCalculation::model()->tableName() . "` t2 ON t2.calculation_id = t1.calculation_id\n LEFT JOIN `" . CalculationDBHandler::model()->tableName() . "` t3 ON t3.handler_id = t2.handler_id\n LEFT JOIN `" . StationSensorFeature::model()->tableName() . "` t4 ON t4.sensor_feature_id = t1.sensor_feature_id\n LEFT JOIN `" . StationSensor::model()->tableName() . "` t5 ON t5.station_sensor_id = t4.sensor_id\n WHERE `t3`.handler_id_code = ? AND t2.station_id = ? \n ORDER BY t5.sensor_id_code";
$res = Yii::app()->db->createCommand($sql)->queryColumn(array($this->handler_id_code, $station_id));
return $res;
}
public function getRainMetric()
{
if ($this->station_id) {
$sql = "SELECT `t3`.`html_code`\n FROM `" . StationSensorFeature::model()->tableName() . "` `t1`\n LEFT JOIN `" . StationSensor::model()->tableName() . "` `t2` ON `t2`.`station_sensor_id` = `t1`.`sensor_id`\n LEFT JOIN `" . RefbookMetric::model()->tableName() . "` `t3` ON `t3`.`metric_id` = `t1`.`metric_id`\n WHERE `t2`.`station_id` = '" . $this->station_id . "' AND `t1`.`feature_code` = 'rain'\n ORDER BY `t2`.`sensor_id_code`";
return CStubActiveRecord::getDbConnect(true)->createCommand($sql)->queryScalar();
}
return 'mm';
}
public function prepareSensorsInfo($listener_log_id)
{
$this->_logger->log(__METHOD__);
$this->_logger->log(__METHOD__ . " listener_log_id " . $listener_log_id);
$this->_logger->log(__METHOD__ . " this->schedule_process_info->ScheduleReportToStation->realStation->station_id " . $this->schedule_process_info->ScheduleReportToStation->realStation->station_id);
$sensors = array();
if (!is_null($this->schedule_process_info->ScheduleReportToStation->realStation->station_id)) {
// get sensors
$sql = "SELECT `t1`.`station_sensor_id`, `t1`.`sensor_id_code`, \r\n `t2`.`handler_id_code`, \r\n `t3`.`feature_code`, `t3`.`feature_constant_value`,\r\n `t3`.`sensor_feature_id`,\r\n `t4`.`code` AS `metric_code`, \r\n `t5`.`sensor_feature_value`, \r\n `t5`.`period` AS `sensor_feature_period`,\r\n `t6`.`code` AS `value_metric_code`,\r\n `t5`.`is_m`\r\n FROM `" . StationSensor::model()->tableName() . "` `t1`\r\n LEFT JOIN `" . SensorDBHandler::model()->tableName() . "` `t2` ON `t2`.`handler_id` = `t1`.`handler_id`\r\n LEFT JOIN `" . StationSensorFeature::model()->tableName() . "` `t3` ON (`t3`.`sensor_id` = `t1`.`station_sensor_id`)\r\n LEFT JOIN `" . RefbookMetric::model()->tableName() . "` `t4` ON `t4`.`metric_id` = `t3`.`metric_id`\r\n LEFT JOIN `" . SensorData::model()->tableName() . "` `t5` ON (`t5`.`sensor_feature_id` = `t3`.`sensor_feature_id` AND `t5`.`listener_log_id` = '" . $listener_log_id . "')\r\n LEFT JOIN `" . RefbookMetric::model()->tableName() . "` `t6` ON `t6`.`metric_id` = `t5`.`metric_id`\r\n WHERE `t1`.`station_id` = '" . $this->schedule_process_info->ScheduleReportToStation->realStation->station_id . "'\r\n ORDER BY `t1`.`sensor_id_code` ASC";
$sensors_info = Yii::app()->db->createCommand($sql)->queryAll();
if (!$sensors_info) {
$this->errors[] = 'Station has no sensors';
return;
}
$sensors = array();
foreach ($sensors_info as $value) {
if ($value['feature_code'] === 'height' && $value['handler_id_code'] === 'Pressure') {
$sensors['pressure_height']['height'] = $value['feature_constant_value'];
$sensors['pressure_height']['height_metric_code'] = $value['metric_code'];
continue;
}
$value['metric_code'] = isset($value['value_metric_code']) ? $value['value_metric_code'] : $value['metric_code'];
unset($value['value_metric_code']);
if (!isset($sensors[$value['feature_code']])) {
$sensors[$value['feature_code']] = $value;
}
}
}
return $sensors;
}
/**
* Update general tables in backup database.
* Actual Data in these tables are required to support integrity of database and store sensors values
* @return string
*/
private function updateMainStationInformation()
{
$tables = array(Station::model()->tableName(), Settings::model()->tableName(), StationSensor::model()->tableName(), StationSensorFeature::model()->tableName(), StationCalculation::model()->tableName(), StationCalculationVariable::model()->tableName());
$result_sql = array();
foreach ($tables as $table) {
$sql = "SELECT * FROM `" . $table . "`";
$res = Yii::app()->db->createCommand($sql)->queryAll();
$total = count($res);
It::debug("updateMainStationInformation: Table = " . $table . ", TOTAL = " . $total, 'backup_database');
if ($res) {
$fields = array();
foreach ($res[0] as $key2 => $value2) {
$fields[] = $key2;
}
$sql_header = "INSERT IGNORE INTO `" . $table . "` (`" . implode('`,`', $fields) . "`) VALUES ";
$res_sql = $sql_header;
foreach ($res as $key => $value) {
$res_sql .= "('" . implode("','", $value) . "')";
if ($key + 1 < $total) {
$res_sql .= ", ";
}
}
$result_sql[] = $res_sql;
}
}
It::debug("updateMainStationInformation: DONE", 'backup_database');
return $result_sql;
}
public function actionAwsSingle()
{
$criteria = new CDbCriteria();
$criteria->compare('station_type', array('aws', 'awos'));
$criteria->order = 'station_id_code asc';
$stations = Station::model()->findAll($criteria);
if (count($stations) > 0) {
$session = Yii::app()->session;
$id = isset($_REQUEST['station_id']) ? intval($_REQUEST['station_id']) : (isset($session['single_aws']['station_id']) ? $session['single_aws']['station_id'] : '');
$station = null;
if ($id) {
foreach ($stations as $st) {
if ($id == $st->station_id) {
$station = $st;
break;
}
}
}
$station = is_null($station) ? $stations[0] : $station;
$log_id = isset($_REQUEST['log_id']) ? intval($_REQUEST['log_id']) : null;
$last2Messages = ListenerLog::getAllLast2Messages(array($station->station_id), $log_id);
if (array_key_exists($station->station_id, $last2Messages) && count($last2Messages[$station->station_id]) > 0) {
$station->lastMessage = date('m/d/Y H:i', strtotime($last2Messages[$station->station_id][0]->measuring_timestamp));
$next_expected = strtotime($last2Messages[$station->station_id][0]->measuring_timestamp) + $station->event_message_period * 60 + 300;
$station->nextMessageExpected = date('m/d/Y H:i', $next_expected);
if ($next_expected < time()) {
$station->nextMessageIsLates = 1;
}
} else {
$station->lastMessage = 'Unknown';
}
if (count($last2Messages[$station->station_id]) > 1) {
$station->previousMessage = date('m/d/Y H:i', strtotime($last2Messages[$station->station_id][1]->measuring_timestamp));
} else {
$station->previousMessage = 'Unknown';
}
$list = StationSensor::getSensorsForAWSDisplay(array($station->station_id), 'aws_single');
$sensors = isset($list[$station->station_id]) ? $list[$station->station_id] : null;
$handler_sensor = array();
if (!is_null($sensors)) {
$sensorPairs = array();
foreach ($sensors as $sensor) {
$sensorPairs[$sensor->handler->handler_id_code][] = array('station_id' => $station->station_id, 'sensor_id' => $sensor->station_sensor_id, 'last_logs' => isset($last2Messages[$station->station_id]) ? $last2Messages[$station->station_id] : array(), 'aws_single_group' => $sensor->handler->aws_single_group);
}
$handlersDefault = array();
SensorDBHandler::handlerWithFeature($handlersDefault, 'single');
$sensorList = SensorHandler::getFullSensorList(array($station->station_id), $handlersDefault);
$sensorData = SensorData::getSensorData($last2Messages, $sensorList);
foreach ($sensorPairs as $handler_id_code => $data) {
$handler_obj = SensorHandler::create($handler_id_code);
$res = $handler_obj->getInfoForAwsPanel($data, $sensorList, $sensorData, 'single');
if ($res) {
foreach ($res as $value_sensors) {
foreach ($value_sensors as $value_sensor_data) {
if ($handler_id_code === 'WindDirection') {
if ($session['single_aws']['chosen_wind_direction'] == 2) {
$chosen_direction = '10minute_average';
} else {
if ($session['single_aws']['chosen_wind_direction'] == 1) {
$chosen_direction = '2minute_average';
} else {
$chosen_direction = 'last';
}
}
$value_sensor_data['chosen_wind_direction'] = $chosen_direction;
$radians = ($value_sensor_data[$chosen_direction] - 90) / 180 * M_PI;
//$value_sensor_data['chosen_wind_coordinates']['x'] = round(86.5+ cos($radians)*70);
//$value_sensor_data['chosen_wind_coordinates']['y'] = round(86.5+ sin($radians)*70);
$value_sensor_data['chosen_wind_coordinates']['x'] = round(83 + cos($radians) * 70);
$value_sensor_data['chosen_wind_coordinates']['y'] = round(83 + sin($radians) * 70);
}
$value_sensor_data['handler_id_code'] = $handler_id_code;
$handler_sensor[$data[0]['aws_single_group']][$handler_id_code][] = $value_sensor_data;
}
}
}
}
}
$handlers = StationCalculation::getStationCalculationHandlers(array($station->station_id));
$calculation = array();
if (array_key_exists($station->station_id, $handlers)) {
foreach ($handlers[$station->station_id] as $value) {
$handler = CalculationHandler::create($value->handler->handler_id_code);
$used_sensors = $handler->getUsedSensors($station->station_id);
if ($used_sensors) {
foreach ($handler_sensor as $group => &$v2) {
foreach ($v2 as $handler_id_code => &$value_sensor_data) {
if ($value->handler->handler_id_code == 'DewPoint' && in_array($handler_id_code, array('Temperature', 'TemperatureSoil', 'TemperatureWater')) || $value->handler->handler_id_code == 'PressureSeaLevel' && in_array($handler_id_code, array('Pressure'))) {
foreach ($value_sensor_data as $k4 => &$v4) {
if (in_array($v4['sensor_id_code'], $used_sensors)) {
$calculation[$group][$value->handler->handler_id_code] = 1;
$v4[$value->handler->handler_id_code] = $handler->getCalculatedValue($station->station_id, $last2Messages[$station->station_id]);
}
}
}
}
}
}
}
}
$session['single_aws'] = array('station_id' => $station->station_id, 'chosen_wind_direction' => $session['single_aws']['chosen_wind_direction']);
}
$speciReport = null;
if (count($last2Messages[$station->station_id]) > 0) {
$criteria = new CDbCriteria();
$criteria->with = array('ScheduleReportToStation.schedule_report');
$criteria->compare('report_type', 'speci');
$criteria->compare('ScheduleReportToStation.station_id', $station->station_id);
// $criteria->compare('is_last', 1);
$criteria->compare('listener_log_id', $last2Messages[$station->station_id][0]->log_id);
// $criteria->order = 't.updated desc';
$criteria->order = 't.created desc';
$criteria->limit = 1;
$speciReport = ScheduleReportProcessed::model()->find($criteria);
if (is_null($speciReport)) {
$reportRecord = ScheduleReport::model()->findByAttributes(array('report_type' => 'speci', 'station_id' => $station->station_id));
if (!is_null($reportRecord)) {
$speciReport = new ScheduleReportProcessed();
$speciReport->schedule_id = $reportRecord->schedule_id;
$speciReport->listener_log_id = $last2Messages[$station->station_id][0]->log_id;
$speciReport->is_processed = 0;
$speciReport->check_period_start = $last2Messages[$station->station_id][0]->measuring_timestamp;
$speciReport->check_period_end = $last2Messages[$station->station_id][0]->measuring_timestamp;
$speciReport->save();
}
}
}
// echo "<pre>";
// print_r($handler_sensor);
// echo "</pre>";exit;
$template = 'aws_single';
$render_data = array('stations' => $stations, 'station' => $station, 'last_logs' => $last2Messages[$station->station_id], 'handler_sensor' => $handler_sensor, 'calculation' => $calculation, 'speciReport' => $speciReport);
if (Yii::app()->request->isAjaxRequest) {
$this->renderPartial($template, array('render_data' => $render_data));
} else {
$this->render('autorefresh_aws_single', array('render_data' => $render_data, 'template' => $template));
}
}
public function prepareList($station_id = 0)
{
if ($this->hasErrors()) {
return ['prepared_header' => [], 'prepared_data' => []];
}
$prepared_header = array();
$prepared_data = array();
$sensor_feature_code = $this->getSensorFeatureCode();
$handler_code = array_keys($this->getSelectedGroupSensorFeatureCode());
$search_features = array();
$search_calcs = array();
foreach ($sensor_feature_code as $key) {
if (in_array($key, array_keys($this->calc_handlers))) {
$search_calcs[] = $this->calc_handlers[$key];
} else {
$search_features[] = $key;
}
}
if (count($this->station_id) > 1) {
$sql_part = "`t2`.`station_id` IN (" . implode(',', $this->station_id) . ") ";
} else {
$sql_part = "`t2`.`station_id` = '" . $this->station_id[0] . "' ";
}
// 1.a) GET FEATURES
if (count($search_features) > 0) {
$sql = "SELECT `t1`.`sensor_feature_id`,\n `t1`.`feature_code`,\n `t1`.`sensor_id`,\n `t3`.`station_id_code`,\n `t2`.`sensor_id_code`,\n `t2`.`station_id`,\n `t4`.`handler_id_code`,\n `t3`.`magnetic_north_offset`\n FROM `" . StationSensorFeature::model()->tableName() . "` `t1`\n LEFT JOIN `" . StationSensor::model()->tableName() . "` `t2` ON `t1`.`sensor_id` = `t2`.`station_sensor_id`\n LEFT JOIN `" . SensorDBHandler::model()->tableName() . "` `t4` ON `t4`.`handler_id` = `t2`.`handler_id`\n LEFT JOIN `" . Station::model()->tableName() . "` `t3` ON `t3`.`station_id` = `t2`.`station_id`\n WHERE " . $sql_part . " AND `t1`.`feature_code` IN ('" . implode("','", $search_features) . "') AND `t4`.`handler_id_code` IN ('" . implode("','", $handler_code) . "')\n ORDER BY `t1`.`feature_code`, `t3`.`station_id_code`, `t2`.`sensor_id_code`";
$found_sensors = CStubActiveRecord::getDbConnect(true)->createCommand($sql)->queryAll();
$total_found_sensors = count($found_sensors);
}
// 1.b) GET CALCS
if (count($search_calcs) > 0) {
$sql = "SELECT `t1`.`calculation_id`,\n `t1`.`handler_id`,\n `t2`.`station_id_code`,\n `t2`.`station_id`,\n IF(`t1`.`handler_id` = 1, 'DP', 'MSL') AS `sensor_id_code`,\n IF(`t1`.`handler_id` = 1, 'Dew Point', 'Pressure MSL') AS `feature_code`,\n IF(`t1`.`handler_id` = 1, 'DewPoint', 'PressureSeaLevel') AS `handler_id_code`\n FROM `" . StationCalculation::model()->tableName() . "` `t1`\n LEFT JOIN `" . Station::model()->tableName() . "` `t2` ON `t2`.`station_id` = `t1`.`station_id`\n WHERE " . $sql_part . " AND `t1`.`handler_id` IN (" . implode(',', $search_calcs) . ")\n ORDER BY `t1`.`handler_id`, `t2`.`station_id_code`";
$found_calcs = CStubActiveRecord::getDbConnect(true)->createCommand($sql)->queryAll();
$total_found_calcs = count($found_calcs);
}
$start_datetime = strtotime($this->date_from . ' ' . $this->time_from);
$end_datetime = strtotime($this->date_to . ' ' . $this->time_to);
$features_set = array();
// 2.a) PREPARE HEADER
if (is_array($found_sensors) && $total_found_sensors > 0) {
$sensor_feature_ids = array();
for ($i = 0; $i < $total_found_sensors; $i++) {
$key = $found_sensors[$i]['handler_id_code'] . $found_sensors[$i]['feature_code'];
if (!isset($prepared_header[$key])) {
$prepared_header[$key] = array('sensor_feature_code' => $found_sensors[$i]['feature_code'], 'handler_id_code' => $found_sensors[$i]['handler_id_code'], 'sensors' => array(), 'station_sensors' => array());
}
$sensor_feature_ids[] = $found_sensors[$i]['sensor_feature_id'];
$prepared_header[$key]['sensors'][] = array('station_id' => $found_sensors[$i]['station_id'], 'sensor_id_code' => $found_sensors[$i]['sensor_id_code']);
if (isset($prepared_header[$key]['station_sensors'][$found_sensors[$i]['station_id']])) {
$prepared_header[$key]['station_sensors'][$found_sensors[$i]['station_id']]++;
} else {
$prepared_header[$key]['station_sensors'][$found_sensors[$i]['station_id']] = 1;
}
$features_set[$found_sensors[$i]['sensor_feature_id']] = array('station_id' => $found_sensors[$i]['station_id'], 'station_id_code' => $found_sensors[$i]['station_id_code'], 'value' => '-', 'sensor_id' => $found_sensors[$i]['sensor_id'], 'sensor_id_code' => $found_sensors[$i]['sensor_id_code'], 'sensor_feature_code' => $found_sensors[$i]['feature_code'], 'handler_id_code' => $found_sensors[$i]['handler_id_code'], 'magnetic_north_offset' => $found_sensors[$i]['magnetic_north_offset']);
}
}
// 2.b) PREPARE HEADER
if (count($search_calcs) > 0) {
$sql = "SELECT `t1`.`calculation_id`,\n `t1`.`handler_id`,\n `t2`.`station_id_code`,\n `t2`.`station_id`,\n IF(`t1`.`handler_id` = 1, 'DP', 'MSL') AS `sensor_id_code`,\n IF(`t1`.`handler_id` = 1, 'Dew Point', 'Pressure MSL') AS `feature_code`,\n IF(`t1`.`handler_id` = 1, 'DewPoint', 'PressureSeaLevel') AS `handler_id_code`\n FROM `" . StationCalculation::model()->tableName() . "` `t1`\n LEFT JOIN `" . Station::model()->tableName() . "` `t2` ON `t2`.`station_id` = `t1`.`station_id`\n WHERE " . $sql_part . " AND `t1`.`handler_id` IN (" . implode(',', $search_calcs) . ")\n ORDER BY `t1`.`handler_id`, `t2`.`station_id`";
$found_calcs = CStubActiveRecord::getDbConnect(true)->createCommand($sql)->queryAll();
if (is_array($found_calcs) && count($found_calcs) > 0) {
$calculation_ids = array();
for ($i = 0; $i < count($found_calcs); $i++) {
$key = 'calc_' . $found_calcs[$i]['handler_id'];
if (!isset($prepared_header[$key])) {
$prepared_header[$key] = array('sensor_feature_code' => $key, 'sensors' => array(), 'station_sensors' => array());
}
$calculation_ids[] = $found_calcs[$i]['calculation_id'];
$prepared_header[$key]['sensors'][] = array('station_id' => $found_calcs[$i]['station_id'], 'sensor_id_code' => $found_calcs[$i]['sensor_id_code']);
if (isset($prepared_header[$key]['station_sensors'][$found_calcs[$i]['station_id']])) {
$prepared_header[$key]['station_sensors'][$found_calcs[$i]['station_id']]++;
} else {
$prepared_header[$key]['station_sensors'][$found_calcs[$i]['station_id']] = 1;
}
$features_set['calc_' . $found_calcs[$i]['calculation_id']] = array('station_id' => $found_calcs[$i]['station_id'], 'station_id_code' => $found_calcs[$i]['station_id_code'], 'value' => '-', 'calculation_id' => $found_calcs[$i]['calculation_id'], 'sensor_id_code' => $found_calcs[$i]['sensor_id_code'], 'sensor_feature_code' => $found_calcs[$i]['feature_code'], 'handler_id_code' => $found_calcs[$i]['handler_id_code']);
}
}
}
// 3.a) PREPARE DATA
if (is_array($found_sensors) && $total_found_sensors) {
$qb = new CDbCriteria();
$qb->select = ['sensor_data_id', 'station_id', 'sensor_id', 'sensor_feature_id', 'sensor_feature_normalized_value', 'is_m', 'measuring_timestamp'];
$qb->addInCondition('sensor_feature_id', $sensor_feature_ids);
$qb->addBetweenCondition('measuring_timestamp', date('Y-m-d H:i:s', $start_datetime), date('Y-m-d H:i:s', $end_datetime));
$qb->order = 'measuring_timestamp DESC';
$found_values = SensorData::model()->long()->findAll($qb);
$total_found_values = count($found_values);
if (is_array($found_values) && $total_found_values > 0) {
if ($this->accumulation_period == 0) {
for ($j = 0; $j < $total_found_values; $j++) {
$f_id = $found_values[$j]['sensor_feature_id'];
$f_time = $found_values[$j]['measuring_timestamp'];
$f_code = $features_set[$f_id]['sensor_feature_code'];
$magnetic_north_offset = $features_set[$f_id]['magnetic_north_offset'];
$st_id = $found_values[$j]['station_id'];
if (!isset($prepared_data[$f_time])) {
$prepared_data[$f_time] = array();
$prepared_data[$f_time]['stations'] = array();
}
if (!isset($prepared_data[$f_time]['data'])) {
$prepared_data[$f_time]['data'] = $features_set;
}
$handler_obj = SensorHandler::create($features_set[$f_id]['handler_id_code']);
if ($found_values[$j]['is_m'] == 1) {
$prepared_data[$f_time]['data'][$f_id]['value'] = '-';
} else {
$found_values[$j]['sensor_feature_normalized_value'] = $handler_obj->applyOffset($found_values[$j]['sensor_feature_normalized_value'], $magnetic_north_offset);
$prepared_data[$f_time]['data'][$f_id]['value'] = $handler_obj->formatValue($found_values[$j]['sensor_feature_normalized_value'], $f_code);
}
if (!in_array($st_id, $prepared_data[$f_time]['stations'])) {
$prepared_data[$f_time]['stations'][] = $st_id;
}
}
} else {
for ($j = 0; $j < $total_found_values; $j++) {
$f_id = $found_values[$j]['sensor_feature_id'];
$f_time = $found_values[$j]['measuring_timestamp'];
$f_code = $features_set[$f_id]['sensor_feature_code'];
$magnetic_north_offset = $features_set[$f_id]['magnetic_north_offset'];
$st_id = $found_values[$j]['station_id'];
$period = $start_datetime + (intval((strtotime($f_time) - $start_datetime) / ($this->accumulation_period * 60)) + 1) * $this->accumulation_period * 60;
$period = $period > $end_datetime ? $end_datetime : $period;
$period = date('Y-m-d H:i:s', $period);
if (!isset($prepared_data[$period])) {
$prepared_data[$period] = array();
$prepared_data[$period]['stations'] = array();
}
if (!isset($prepared_data[$period]['data'])) {
$prepared_data[$period]['data'] = $features_set;
}
$handler_obj = SensorHandler::create($features_set[$f_id]['handler_id_code']);
if ($found_values[$j]['is_m'] == 1) {
$prepared_data[$period]['data'][$f_id]['value'] = '-';
} else {
$found_values[$j]['sensor_feature_normalized_value'] = $handler_obj->applyOffset($found_values[$j]['sensor_feature_normalized_value'], $magnetic_north_offset);
$prepared_data[$period]['data'][$f_id]['value'] = ($prepared_data[$period]['data'][$f_id]['value'] ? $prepared_data[$period]['data'][$f_id]['value'] : 0) + $handler_obj->formatValue($found_values[$j]['sensor_feature_normalized_value'], $f_code);
}
if (!in_array($st_id, $prepared_data[$period]['stations'])) {
$prepared_data[$period]['stations'][] = $st_id;
}
}
}
}
}
// 3.b) PREPARE DATA
if (is_array($found_calcs) && $total_found_calcs > 0) {
$sql = "SELECT `t2`.`station_id`,\n `t1`.`calculation_id`,\n `t1`.`value`,\n `t2`.`measuring_timestamp`\n FROM `" . StationCalculationData::model()->tableName() . "` `t1`\n LEFT JOIN `" . ListenerLog::model()->tableName() . "` `t2` ON `t2`.`log_id` = `t1`.`listener_log_id`\n WHERE `t1`.`calculation_id` IN (" . implode(',', $calculation_ids) . ")\n AND `t2`.`measuring_timestamp` >= '" . date('Y-m-d H:i:s', $start_datetime) . "'\n AND `t2`.`measuring_timestamp` <= '" . date('Y-m-d H:i:s', $end_datetime) . "'\n ORDER BY `t2`.`measuring_timestamp` DESC";
$found_values = CStubActiveRecord::getDbConnect(true)->createCommand($sql)->queryAll();
$total_found_values = count($found_values);
if (is_array($found_values) && $total_found_values) {
for ($j = 0; $j < $total_found_values; $j++) {
$f_id = 'calc_' . $found_values[$j]['calculation_id'];
$f_time = $found_values[$j]['measuring_timestamp'];
$st_id = $found_values[$j]['station_id'];
if (!$prepared_data[$f_time]) {
$prepared_data[$f_time] = array();
$prepared_data[$f_time]['stations'] = array();
}
if (!$prepared_data[$f_time]['data']) {
$prepared_data[$f_time]['data'] = $features_set;
}
$prepared_data[$f_time]['data'][$f_id]['value'] = CalculationHandler::formatValue($found_values[$j]['value']);
$prepared_data[$f_time]['data'][$f_id]['station_id'] = $st_id;
if (!in_array($st_id, $prepared_data[$f_time]['stations'])) {
$prepared_data[$f_time]['stations'][] = $st_id;
}
}
}
}
//need sort
krsort($prepared_data);
//print_r(array(
// 'prepared_header' => $prepared_header,
// 'prepared_data' => $prepared_data,
// ));exit;
return array('prepared_header' => $prepared_header, 'prepared_data' => $prepared_data);
}
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 process($path)
{
if (!file_exists($path)) {
throw new Exception('Can\'t find file ' . $path);
}
$pathinfo = pathinfo($path);
$base_filename = $pathinfo['basename'];
$xml_content = file_get_contents($path);
if (!strlen($xml_content)) {
throw new Exception($base_filename . " is empty");
}
libxml_use_internal_errors(true);
$sxe = simplexml_load_string($xml_content);
$error_str = "";
if ($sxe === false) {
foreach (libxml_get_errors() as $error) {
$error_str .= "\n" . $error->message;
}
throw new Exception($error_str);
}
// XML must contain 1 RUNWAY tag
if (count($sxe->RUNWAY) > 1) {
throw new Exception('XML ' . $base_filename . ' contains ' . count($sxe->RUNWAY) . ' RUNWAY tags');
}
if (count($sxe->RUNWAY) == 0) {
throw new Exception('XML ' . $base_filename . ' doesn\'t contain RUNWAY tags');
}
// RUNWAY's "NAME" attribute must be "08/26"
if ($sxe->RUNWAY['NAME'] != '08/26') {
throw new Exception('XML ' . $base_filename . ' RUNWAY name = "' . $sxe->RUNWAY['NAME'] . '", "08/26" was expected');
}
// RUNWAY must contain at least 1 ZONE tag
if (count($sxe->RUNWAY->ZONE) == 0) {
throw new Exception('XML ' . $base_filename . ' doesn\'t contain ZONE tags');
}
// XML must contain "UNITS" tag
if (!isset($sxe->UNITS)) {
throw new Exception('XML ' . $base_filename . ' doesn\'t contain UNITS section');
}
$str = '';
$possible_units = array('WIND' => 'kt', 'VISBILITY' => 'meters', 'RVR' => 'meters', 'ALTIMETER' => 'hpa');
foreach ($possible_units as $key => $value) {
if (!isset($sxe->UNITS->{$key})) {
$str .= ($str ? '; ' : '') . ' UNITS[' . $key . '] is missed';
} else {
if ($sxe->UNITS->{$key} != $value) {
$str .= ($str ? '; ' : '') . ' unknown metric "' . $sxe->UNITS->{$key} . '" in UNITS[' . $key . ']';
}
}
}
if ($str) {
throw new Exception($str);
}
$result = array();
$messages = array();
for ($key = 0; $key < count($sxe->RUNWAY->ZONE); $key++) {
// Get's Station ID
if ($sxe->RUNWAY->ZONE[$key]['NAME'] == '08') {
$messages[$key]['station_id_code'] = 'AWS08';
} else {
if ($sxe->RUNWAY->ZONE[$key]['NAME'] == '26') {
$messages[$key]['station_id_code'] = 'AWS26';
} else {
continue;
}
}
//Gets sensor's data from tags:
// WIND SPEED
if (isset($sxe->RUNWAY->ZONE[$key]->WSPD_5SEC)) {
$messages[$key]['sensors']['WindSpeed'][0]['wind_speed_1'] = (string) $sxe->RUNWAY->ZONE[$key]->WSPD_5SEC;
}
if (isset($sxe->RUNWAY->ZONE[$key]->WSPD_2MIN)) {
$messages[$key]['sensors']['WindSpeed'][0]['wind_speed_2'] = (string) $sxe->RUNWAY->ZONE[$key]->WSPD_2MIN;
}
// WIND DIRECTION
if (isset($sxe->RUNWAY->ZONE[$key]->WDIR_5SEC)) {
$messages[$key]['sensors']['WindDirection'][0]['wind_direction_1'] = (string) $sxe->RUNWAY->ZONE[$key]->WDIR_5SEC;
}
if (isset($sxe->RUNWAY->ZONE[$key]->WDIR_2MIN)) {
$messages[$key]['sensors']['WindDirection'][0]['wind_direction_2'] = (string) $sxe->RUNWAY->ZONE[$key]->WDIR_2MIN;
}
// TEMPERATURE
if (isset($sxe->RUNWAY->ZONE[$key]->TEMP_5MIN)) {
$messages[$key]['sensors']['Temperature'][0]['temperature'] = (string) $sxe->RUNWAY->ZONE[$key]->TEMP_5MIN;
}
// HUMIDITY
if (isset($sxe->RUNWAY->ZONE[$key]->HUM_5MIN)) {
$messages[$key]['sensors']['Humidity'][0]['humidity'] = (string) $sxe->RUNWAY->ZONE[$key]->HUM_5MIN;
}
// PRESSURE
if (isset($sxe->RUNWAY->ZONE[$key]->PRESSURE1)) {
$messages[$key]['sensors']['Pressure'][0]['pressure'] = (string) $sxe->RUNWAY->ZONE[$key]->PRESSURE1;
}
if (isset($sxe->RUNWAY->ZONE[$key]->PRESSURE2)) {
$messages[$key]['sensors']['Pressure'][1]['pressure'] = (string) $sxe->RUNWAY->ZONE[$key]->PRESSURE2;
}
if (isset($sxe->RUNWAY->ZONE[$key]->PRESSURE3)) {
$messages[$key]['sensors']['Pressure'][2]['pressure'] = (string) $sxe->RUNWAY->ZONE[$key]->PRESSURE3;
}
// CLOUD
if (isset($sxe->RUNWAY->ZONE[$key]->CLOUDRANGE)) {
$messages[$key]['sensors']['CloudHeightAWS'][0]['cloud_measuring_range'] = (string) $sxe->RUNWAY->ZONE[$key]->CLOUDRANGE;
}
if (isset($sxe->RUNWAY->ZONE[$key]->CLOUDVV)) {
$messages[$key]['sensors']['CloudHeightAWS'][0]['cloud_vertical_visibility'] = (string) $sxe->RUNWAY->ZONE[$key]->CLOUDVV;
}
if (isset($sxe->RUNWAY->ZONE[$key]->CLOUDH1)) {
$messages[$key]['sensors']['CloudHeightAWS'][0]['cloud_height_height_1'] = (string) $sxe->RUNWAY->ZONE[$key]->CLOUDH1;
}
if (isset($sxe->RUNWAY->ZONE[$key]->CLOUDH2)) {
$messages[$key]['sensors']['CloudHeightAWS'][0]['cloud_height_height_2'] = (string) $sxe->RUNWAY->ZONE[$key]->CLOUDH2;
}
if (isset($sxe->RUNWAY->ZONE[$key]->CLOUDH3)) {
$messages[$key]['sensors']['CloudHeightAWS'][0]['cloud_height_height_3'] = (string) $sxe->RUNWAY->ZONE[$key]->CLOUDH3;
}
if (isset($sxe->RUNWAY->ZONE[$key]->CLOUDD1)) {
$messages[$key]['sensors']['CloudHeightAWS'][0]['cloud_height_depth_1'] = (string) $sxe->RUNWAY->ZONE[$key]->CLOUDD1;
}
if (isset($sxe->RUNWAY->ZONE[$key]->CLOUDD2)) {
$messages[$key]['sensors']['CloudHeightAWS'][0]['cloud_height_depth_2'] = (string) $sxe->RUNWAY->ZONE[$key]->CLOUDD2;
}
if (isset($sxe->RUNWAY->ZONE[$key]->CLOUDD3)) {
$messages[$key]['sensors']['CloudHeightAWS'][0]['cloud_height_depth_3'] = (string) $sxe->RUNWAY->ZONE[$key]->CLOUDD3;
}
// VISIBILITY
$vis = (string) $sxe->RUNWAY->ZONE[$key]->EXC;
if (isset($sxe->RUNWAY->ZONE[$key]->EXC) && is_numeric($vis) && $vis != 0) {
//P = (1/σ) x ln (1/0.05)
//where ln is the log to base e or the natural logarithm. σ is the extinction cooefficient.
//This number will be in km, so we will need to multiply by 1000.
$messages[$key]['sensors']['VisibilityAWS'][0]['visibility_1'] = 1 / $vis * log(20) * 1000;
}
// SOLAR
if (isset($sxe->RUNWAY->ZONE[$key]->SOLAR_1MIN)) {
$messages[$key]['sensors']['SolarRadiation'][0]['solar_radiation_in_period'] = (string) $sxe->RUNWAY->ZONE[$key]->SOLAR_1MIN;
}
// Rain fall
if (isset($sxe->RUNWAY->ZONE[$key]->PRECIP_ACCUM)) {
$messages[$key]['sensors']['RainAws'][0]['period'] = 5;
$messages[$key]['sensors']['RainAws'][0]['rain_in_period'] = (string) $sxe->RUNWAY->ZONE[$key]->PRECIP_ACCUM;
}
// Sunshine duration
if (isset($sxe->RUNWAY->ZONE[$key]->SUN_ACCUM)) {
$messages[$key]['sensors']['SunshineDuration'][0]['period'] = 5;
$messages[$key]['sensors']['SunshineDuration'][0]['sun_duration_in_period'] = (string) $sxe->RUNWAY->ZONE[$key]->SUN_ACCUM;
}
}
if (!$messages) {
$result[] = $base_filename . " : No datasets found";
return implode("\n", $result);
}
$result[] = $base_filename . " : " . count($messages) . ' datasets were found';
$sql = "SELECT * \n FROM `" . Station::model()->tableName() . "` `t1`\n WHERE `t1`.`station_id_code` IN ('AWS08', 'AWS26')";
$res = Yii::app()->db->createCommand($sql)->queryAll();
if (!$res) {
$result[] = "AWS08 and AWS26 are not exist in database, no sense to convert XML into messages";
return implode("\n", $result);
}
// for each stationID looks for sensors and features of this station
$stations = array();
foreach ($res as $key => $value) {
$stations[$value['station_id_code']] = $value;
$sql = "SELECT `t1`.`sensor_id_code`,\n `t1`.`station_id`,\n `t2`.`feature_code`,\n `t3`.`code` AS `metric_code`,\n `t4`.`handler_id_code`\n FROM `" . StationSensor::model()->tableName() . "` `t1`\n LEFT JOIN `" . StationSensorFeature::model()->tableName() . "` `t2` ON `t1`.`station_sensor_id` = `t2`.`sensor_id`\n LEFT JOIN `" . RefbookMetric::model()->tableName() . "` `t3` ON `t3`.`metric_id` = `t2`.`metric_id`\n LEFT JOIN `" . SensorDBHandler::model()->tableName() . "` `t4` ON `t4`.`handler_id` = `t1`.`handler_id`\n WHERE `t1`.`station_id` = '" . $value['station_id'] . "'\n ORDER BY `t4`.`handler_id_code` ASC, `t1`.`sensor_id_code` ASC";
$res2 = Yii::app()->db->createCommand($sql)->queryAll();
if ($res2) {
$tmp = array();
foreach ($res2 as $value2) {
$tmp[$value2['handler_id_code']][$value2['sensor_id_code']][$value2['feature_code']] = $value2['metric_code'];
}
foreach ($tmp as $key_handler => $value_sensors) {
foreach ($value_sensors as $key_sensor => $value_features) {
$stations[$value['station_id_code']]['sensors'][$key_handler][] = array('sensor_id_code' => $key_sensor, 'features' => $value_features);
}
}
}
}
$date_parsed = date_parse_from_format("D, j M Y H:i:s", $sxe->DATE);
$date_prepared = mktime($date_parsed['hour'], $date_parsed['minute'], $date_parsed['second'], $date_parsed['month'], $date_parsed['day'], $date_parsed['year']);
// convert parsed XML data into regular message
// for this kind of messages we have an agreement to put X instead of D at the beginning of message.
foreach ($messages as $key => $value) {
if (!$stations[$value['station_id_code']]) {
$result[] = $value['station_id_code'] . " station is not exists in database, no sense to convert RNWY part into message";
continue;
}
$result_message_body = 'X' . $value['station_id_code'];
$result_message_body .= date('ymdHi', $date_prepared);
$result_message_body .= '00';
// we need last_log for this satation to calculate period of measurement (some sensors' strings should contain this period
$last_logs = ListenerLog::getLast2Messages($stations[$value['station_id_code']]['station_id']);
if (isset($value['sensors'])) {
foreach ($value['sensors'] as $key_handler => $value_sensors) {
if ($value_sensors) {
foreach ($value_sensors as $key_sensor => $value2) {
if (isset($stations[$value['station_id_code']]['sensors'][$key_handler][$key_sensor])) {
// create handler for each sensor (we parsed new data for)
$handler = SensorHandler::create($key_handler);
if ($key_handler == 'SolarRadiation' && $last_logs[0]['log_id']) {
if ($last_logs[0]['log_id']) {
$total_minutes = round(abs($date_prepared - strtotime($last_logs[0]['measuring_timestamp'])) / 60);
$value2['period'] = $total_minutes;
} else {
$value2['period'] = 1;
}
if ($value2['solar_radiation_in_period'][0] != 'M') {
$value2['solar_radiation_in_period'] = $value2['solar_radiation_in_period'] * $value2['period'] * 60;
}
}
// each handler has it's own implementation of preparing sensors string for message basing on XML data
$res = $handler->prepareXMLValue($value2, $stations[$value['station_id_code']]['sensors'][$key_handler][$key_sensor]['features']);
$result_message_body .= $stations[$value['station_id_code']]['sensors'][$key_handler][$key_sensor]['sensor_id_code'] . $res;
}
}
}
}
$result_message_body .= It::prepareCRC($result_message_body);
$result_message_body = '@' . $result_message_body . '$';
// add new message into database. It will be processed later as all newcame messages
$log_id = ListenerLog::addNew($result_message_body, 0, 1);
$result[] = $base_filename . " : New message #" . $log_id . " was added";
}
}
// return some comments created during convertation
return implode("\n", $result);
}
public static function getList($type = '', $key_to_id = true)
{
$sql_where = '';
if ($type != '' && $type != 'all') {
$sql_where = "WHERE `station_type` IN (" . $type . ")";
}
if ($type == 'rain') {
$sql = "SELECT `st`.*,\n `sn`.`station_sensor_id`\n FROM `" . Station::model()->tableName() . "` `st`\n LEFT JOIN `" . StationSensor::model()->tableName() . "` `sn` ON `sn`.`station_id` = `st`.`station_id`\n WHERE `st`.`station_type` = 'rain'\n GROUP BY `st`.`station_id`\n ORDER BY `st`.`station_id_code`";
// $sql = "SELECT `st`.*,
// `sn`.`station_sensor_id`,
// `t3`.`filter_max` AS `filter_limit_max`,
// `t3`.`filter_min` AS `filter_limit_min`,
// `t3`.`filter_diff` AS `filter_limit_diff`
// FROM `".Station::model()->tableName()."` `st`
// LEFT JOIN `".StationSensor::model()->tableName()."` `sn` ON `sn`.`station_id` = `st`.`station_id`
// LEFT JOIN `".StationSensorFeature::model()->tableName()."` `t3` ON `t3`.`sensor_id` = `sn`.`station_sensor_id` AND `t3`.`feature_code` = 'rain'
// WHERE `st`.`station_type` = 'rain'
// GROUP BY `st`.`station_id`
// ORDER BY `st`.`station_id_code`";
} else {
$sql = "SELECT * \n FROM `" . Station::model()->tableName() . "`\n {$sql_where}\n ORDER BY `station_id_code` ";
}
$res = Yii::app()->db->createCommand($sql)->queryAll();
$stations = array();
if ($key_to_id) {
if ($res) {
foreach ($res as $value) {
$stations[$value['station_id']] = $value;
}
}
} else {
$stations = $res;
}
return $stations;
}
public function m_0_4_18()
{
@apache_setenv('no-gzip', 1);
@ini_set('zlib.output_compression', 0);
@ini_set('implicit_flush', 1);
ini_set('memory_limit', '-1');
$this->flushNotification("\n...Please wait... Updating is going on... DO NOT LEAVE THIS PAGE!");
$this->flushNotification("<br/><br/>...Add new measurement....");
$sql = "INSERT INTO `refbook_measurement_type` (`measurement_type_id` ,`display_name` ,`code` ,`ord`)\n VALUES ('21', 'Cloud Measuring Range', 'cloud_measuring_range', '18')";
Yii::app()->db->createCommand($sql)->query();
Yii::app()->db->createCommand("COMMIT")->query();
$sql = "INSERT INTO `refbook_measurement_type_metric` (`measurement_type_metric_id` ,`measurement_type_id` ,`metric_id` ,`is_main`)\n VALUES ('32', '21', '11', '1'), ('33', '21', '22', '0')";
Yii::app()->db->createCommand($sql)->query();
Yii::app()->db->createCommand("COMMIT")->query();
$sql = "ALTER TABLE `sensor_data` ADD `is_m` TINYINT( 1 ) NOT NULL DEFAULT '0' AFTER `sensor_feature_value` ";
Yii::app()->db->createCommand($sql)->query();
Yii::app()->db->createCommand("COMMIT")->query();
$sql = "ALTER TABLE `sensor_data` DROP INDEX `sensor_data__feature_measuuring_index` ,\n ADD INDEX `sensor_data__feature_measuuring_index` ( `sensor_feature_id` , `measuring_timestamp` , `is_m` )";
Yii::app()->db->createCommand($sql)->query();
Yii::app()->db->createCommand("COMMIT")->query();
$sql = "SELECT * FROM `" . StationSensor::model()->tableName() . "` WHERE `handler_id` = 15";
$res = Yii::app()->db->createCommand($sql)->queryAll();
if ($res) {
foreach ($res as $key => $value) {
$sql = "SELECT * \n FROM `" . StationSensorFeature::model()->tableName() . "` \n WHERE sensor_id = '" . $value['station_sensor_id'] . "' AND `feature_code` IN ('cloud_height_height_1', 'cloud_measuring_range')\n ORDER BY `feature_code`";
$res2 = Yii::app()->db->createCommand($sql)->queryAll();
if ($res2 && count($res2) == 1) {
$obj = new StationSensorFeature();
$obj->sensor_id = $res2[0]['sensor_id'];
$obj->feature_code = 'cloud_measuring_range';
$obj->feature_display_name = 'Measuring Range';
$obj->feature_constant_value = 0;
$obj->measurement_type_code = 'cloud_measuring_range';
$obj->metric_id = $res2[0]['metric_id'];
$obj->is_main = 0;
$obj->has_filters = 1;
$obj->has_filter_min = 1;
$obj->has_filter_max = 1;
$obj->has_filter_diff = 1;
$obj->is_constant = 0;
$obj->is_cumulative = 0;
$obj->save();
}
}
}
$this->flushNotification("<br/><br/>...Updated ....");
It::memStatus('update__success');
$this->flushNotification('<script type="text/javascript"> setTimeout(function(){document.location.href="' . Yii::app()->controller->createUrl('update/index') . '"}, 5000)</script>');
}
public function createExport()
{
$return_string = "";
$sql = "SELECT\n `t1`.`measuring_timestamp` AS `TxDateTime`,\n `t4`.`station_id_code` AS `StationId`, \n `t4`.`display_name` AS `StationDisplayName`,\n `t3`.`sensor_id_code` AS `SensorId`, \n `t3`.`display_name` AS `SensorDisplayName`, \n \n `t1`.`period` AS `MeasurementPeriod`,\n `t1`.`sensor_feature_value` AS `Value`,\n `t5`.`short_name` AS `Metric`,\n `t8`.`value` AS `DewPoint`,\n `t11`.`value` AS `PressureMSL`,\n \n `t12`.`handler_id_code`,\n `t4`.`magnetic_north_offset`\n\n FROM `" . SensorData::model()->tableName() . "` t1\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` = `t2`.`sensor_id`\n LEFT JOIN `" . Station::model()->tableName() . "` `t4` ON `t4`.`station_id` = `t1`.`station_id`\n LEFT JOIN `" . RefbookMetric::model()->tableName() . "` `t5` ON `t5`.`metric_id` = `t2`.`metric_id`\n\n LEFT JOIN `" . StationCalculation::model()->tableName() . "` `t6` ON (`t6`.`station_id` = `t1`.`station_id` AND `t6`.`handler_id` = 1)\n LEFT JOIN `" . StationCalculationVariable::model()->tableName() . "` `t7` ON (`t7`.`sensor_feature_id` = `t2`.`sensor_feature_id` AND `t7`.`calculation_id` = `t6`.`calculation_id`)\n LEFT JOIN `" . StationCalculationData::model()->tableName() . "` `t8` ON (`t8`.`calculation_id` = `t7`.`calculation_id` AND `t8`.`listener_log_id` = `t1`.`listener_log_id`)\n\n LEFT JOIN `" . StationCalculation::model()->tableName() . "` `t9` ON (`t9`.`station_id` = `t1`.`station_id` AND `t9`.`handler_id` = 2)\n LEFT JOIN `" . StationCalculationVariable::model()->tableName() . "` `t10` ON (`t10`.`sensor_feature_id` = `t2`.`sensor_feature_id` AND `t10`.`calculation_id` = `t9`.`calculation_id`)\n LEFT JOIN `" . StationCalculationData::model()->tableName() . "` `t11` ON (`t11`.`calculation_id` = `t10`.`calculation_id` AND `t11`.`listener_log_id` = `t1`.`listener_log_id`)\n\n LEFT JOIN `" . SensorDBHandler::model()->tableName() . "` `t12` ON `t12`.`handler_id` = `t3`.`handler_id`\n\n WHERE `t1`.`station_id` IN (" . implode(',', $this->station_id) . ")\n AND `t1`.`measuring_timestamp` >= FROM_UNIXTIME(" . $this->start_timestamp . ") \n AND `t1`.`measuring_timestamp` <= FROM_UNIXTIME(" . $this->end_timestamp . ")\n AND `t2`.`is_main` = 1\n AND `t1`.`is_m` = '0'\n ORDER BY `t1`.`measuring_timestamp` DESC, `t4`.`station_id_code`, `t3`.`sensor_id_code`";
$res = CStubActiveRecord::getDbConnect(true)->createCommand($sql)->queryAll();
if ($res) {
foreach ($res as $key => $value) {
$handler_obj = SensorHandler::create($value['handler_id_code']);
$res[$key]['Value'] = $handler_obj->applyOffset($res[$key]['Value'], $res[$key]['magnetic_north_offset']);
$res[$key]['Value'] = $handler_obj->formatValue($res[$key]['Value'], $res[$key]['feature_code']);
unset($res[$key]['magnetic_north_offset']);
unset($value['handler_id_code']);
unset($res[$key]['feature_code']);
}
$return_string .= "\"AWS Stations:\"\n" . It::prepareStringCSV($res);
}
$sql = "SELECT\n `t1`.`measuring_timestamp` AS `TxDateTime`,\n `t3`.`station_id_code` AS `StationId`, \n `t3`.`display_name` AS `StationDisplayName`,\n `t2`.`sensor_id_code` AS `SensorId`, \n `t2`.`display_name` AS `SensorDisplayName`, \n (`t1`.`sensor_value` * `t1`.`bucket_size`) AS `Value`,\n `t4`.`short_name` AS `Metric` \n FROM `" . SensorDataMinute::model()->tableName() . "` t1\n LEFT JOIN `" . StationSensor::model()->tableName() . "` t2 ON t2.station_sensor_id = t1.sensor_id\n LEFT JOIN `" . Station::model()->tableName() . "` t3 ON t3.station_id = t1.station_id\n LEFT JOIN `" . RefbookMetric::model()->tableName() . "` t4 ON t4.metric_id = t1.metric_id\n \n WHERE `t1`.`station_id` IN (" . implode(',', $this->station_id) . ")\n AND `t1`.`measuring_timestamp` >= FROM_UNIXTIME(" . $this->start_timestamp . ") \n AND `t1`.`measuring_timestamp` <= FROM_UNIXTIME(" . $this->end_timestamp . ")\n AND `t1`.`is_tmp` = 0\n ORDER BY t1.measuring_timestamp DESC, t3.station_id_code, t2.sensor_id_code\n ";
$res = CStubActiveRecord::getDbConnect(true)->createCommand($sql)->queryAll();
if ($res) {
$return_string .= "\n\n\"RG Stations:\"\n" . It::prepareStringCSV($res);
}
It::downloadFile($return_string, 'export__' . date('Y-m-d_Hi') . '.csv', 'text/csv');
}
/**
* @param $sensor_id
*
* @return StationSensor
*/
protected function getSensor($sensor_id)
{
if (is_null($this->sensor)) {
$this->sensor = StationSensor::model()->with(['ConstantFeature.metric'])->findByPk($sensor_id);
}
return $this->sensor;
}
/**
* However Rain Datalogger can have more than 1 sensor.
* Message from Rain Datalogger's log has information about only 1 sensor.
* This function is used to get information about first sensor of this station
*
* @access protected
* @return array
*/
protected function getFirstRGSensor()
{
$criteria = new CDbCriteria();
$criteria->with = array('handler', 'first_sensor_feature');
$criteria->compare('station_id', $this->_station->station_id);
$criteria->compare('first_sensor_feature.sensor_feature_id', '>0');
$sensor = StationSensor::model()->find($criteria);
if (is_null($sensor)) {
$this->pushWarning('cant_find_rain_sensor', 'Can not find rain sensor for RG station "' . $this->_station->station_id_code . '" in the DB. Sensor value was not saved.');
return false;
} else {
return array('sensor' => $sensor, 'features' => StationSensorFeature::getInfoForHandler($sensor->station_sensor_id));
}
}
public function actionLoadSensors()
{
$res = StationSensor::getList(isset($_REQUEST['id']) ? intval($_REQUEST['id']) : null);
print json_encode($res);
CApplication::end();
}
