function getStation() { $station = Station::model()->findByPk($this->message_obj->station_id); if ($station) { $this->_station = $station; } }
public function init() { $this->getFromMemory(); $criteria = new CDbCriteria(); $criteria->order = "communication_port asc, communication_type asc"; $stations = Station::model()->findAll($criteria); if (count($stations) > 0) { foreach ($stations as $station) { switch ($station->communication_type) { case 'direct': case 'sms': $connection_type = $station->communication_port; break; case 'tcpip': $connection_type = $station->communication_esp_ip . ':' . $station->communication_esp_port; break; case 'gprs': $connection_type = 'poller:' . $station->station_id_code; break; case 'server': $connection_type = 'tcp:' . $station->communication_esp_ip . ':' . $station->communication_esp_port; break; } $this->all_sources[$connection_type] = $connection_type; } } return parent::init(); }
public function checkAccumulationPeriod() { if ($this->hasErrors()) { return false; } /** Sensor validate */ if (isset($this->accumulation_period) && $this->accumulation_period != 0) { foreach ($this->getSensorFeatureCode() as $feature_code) { if (!in_array($feature_code, $this->accumulate_features)) { $this->addError('accumulation_period', 'Please fix the following input errors: Accumulation only applies to rain, sun duration and solar radiation. If you want to see accumulated rain or sun, make sure you do not select other features. Please select \'Rain\', \'Sun Radiation\' or \'Sun Duration\' and click \'Filter\' again'); return false; } } } /** station validate */ $qb = new CDbCriteria(); $qb->select = ['event_message_period', 'station_id_code']; $qb->addInCondition('station_id', $this->station_id); if (isset($this->accumulation_period) && $this->accumulation_period != 0 && ($stations = Station::model()->findAll($qb))) { $stations_error = array(); foreach ($stations as $station) { if ($station->event_message_period > $this->accumulation_period) { $stations_error[] = $station->station_id_code; } } if (count($stations_error)) { $this->addError('event_message_period', 'The period you selected is smaller than the weather message interval in stations ' . implode(',', $stations_error) . '. Please increase the period and click \'Filter\' again.'); } } if ($this->hasErrors('accumulation_period')) { return false; } else { return true; } }
public function init() { $sql = "SELECT `station_id`, CONCAT(`station_id_code`, ' - ', `display_name`) AS `name`\n FROM `" . Station::model()->tableName() . "`\n WHERE `station_type` = 'rain' ORDER BY `station_id_code` "; $stations = CStubActiveRecord::getDbConnect(true)->createCommand($sql)->queryAll(); if ($stations) { $this->stations = CHtml::listData($stations, 'station_id', 'name'); } $this->getFromMemory(); return parent::init(); }
public function generateMessage() { $station = Station::model()->findByPk($this->station_id); $command = 'C'; $command .= $station->station_id_code; $command .= $this->sms_command_code; $command .= implode($this->sms_command_params ? $this->sms_command_params : []); $command .= It::prepareCRC($command); $command = '@' . $command . '$'; return $command; }
public function prepareTypes() { $types = array(); $types[0] = 'Messages from AWS or RG ("$....@")'; $station_types = array(); $station_timezones = array(); $sql = "SELECT * FROM `" . Station::model()->tableName() . "` WHERE `station_type` = 'rain' ORDER BY `station_id_code` "; $res = Yii::app()->db->createCommand($sql)->queryAll(); if ($res) { foreach ($res as $key => $value) { $types[$value['station_id']] = "LOG from RG: " . $value['station_id_code'] . " (" . $value['display_name'] . ")"; $station_types[$value['station_id']] = $value['station_type']; $station_timezones[$value['station_id']] = $value['timezone_id']; } } return array('source_types' => $types, 'station_types' => $station_types, 'station_timezones' => $station_timezones); }
function actionIndex() { ini_set('memory_limit', '-1'); print 'Checking `listener_log` table upgrade....<br/>'; $sql = "SHOW COLUMNS FROM `" . ListenerLog::model()->tableName() . "` LIKE 'is_last'"; $res = Yii::app()->db->createCommand($sql)->queryAll(); if (!$res) { print '<br/><br/>Checked - requires update.<br/><br/>Attention! Script going to make small update to your database.... Please, be patient, don\'t use system before script is completed.'; $sql = "ALTER TABLE `" . ListenerLog::model()->tableName() . "` ADD `is_last` tinyint(1) NOT NULL DEFAULT '0' AFTER `is_processed`"; Yii::app()->db->createCommand($sql)->query(); $sql = "SELECT * FROM `" . Station::model()->tableName() . "`"; $stations = Yii::app()->db->createCommand($sql)->queryAll(); if ($stations) { foreach ($stations as $key => $value) { ListenerLog::updateIsLastForStation($value['station_id']); } } print '<br><br>.....<br><br>Done!. <br/><br/>You can continue work with <a href="' . It::baseUrl() . '">Delairco</a>'; } else { print '<br/>Checked - doesn\'t require update. <br/><br/>You can continue work with <a href="' . It::baseUrl() . '">Delairco</a>'; } }
public function init() { preg_match('/^POLLER\\:([a-zA-Z0-9]{1,5})$/i', $this->source, $matches); $station = Station::model()->findByAttributes(array('station_id_code' => strtoupper($matches[1]))); if (is_null($station)) { $this->_logger->log(__CLASS__ . ' ' . __METHOD__ . ': Station is not found', array('station_code' => strtoupper($matches[1]))); return false; } else { $this->_connector = new TcpIpPollerConnector($this->_logger, $station, 60); if (Yii::app()->params['sms_params']['enabled'] === true) { $phpSerial = new PhpSerial($this->_logger); $this->_smsMessageSender = new SmsMessageSender($this->_logger, $this->listener, $phpSerial, Yii::app()->params['com_connect_params'], Yii::app()->params['sms_params']['serial_port'], $station->phone_number, $station->sms_message); } else { $this->_logger->log(__CLASS__ . ' ' . __METHOD__ . ' Reset SMS message is disabled'); } } // $this->_connector = new TcpIpServerConnector($this->_logger, $matches[1], $matches[2], $matches[3]); $this->_logger->log(__CLASS__ . ' ' . __METHOD__); $this->settings = Settings::model()->find(); $this->synchronization = new Synchronization(); return true; }
/** * 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 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'); }
public function prepareList($page_size = 10) { $stations = $this->getAllStations(); if ($stations) { $sql_where = array(); //---------------- Start groupping $use_field = ''; if ($this->rate_volume == 1) { $use_field = 'sensor_value'; } else { if ($this->rate_volume == 5) { $use_field = '5min_sum'; $tmp = array('00', '05', '10', '15', '20', '25', '30', '35', '40', '45', '50', '55'); } else { if ($this->rate_volume == 10) { $use_field = '10min_sum'; $tmp = array('00', '10', '20', '30', '40', '50'); } else { if ($this->rate_volume == 20) { $use_field = '20min_sum'; $tmp = array('00', '20', '40'); } else { if ($this->rate_volume == 30) { $use_field = '30min_sum'; $tmp = array('00', '30'); } else { if ($this->rate_volume == 60) { $use_field = '60min_sum'; $tmp = array('00'); } } } } } $sql_where[] = "DATE_FORMAT(`sd`.`measuring_timestamp`, '%i') IN ('" . implode("','", $tmp) . "')"; } $sql_where[] = "`sd`.`" . $use_field . "` > 0"; //---------------- End groupping //---------------- Start date filter if ($this->date_from) { $sql_where[] = "`sd`.`measuring_timestamp` >= '" . date('Y-m-d H:i:s', strtotime($this->date_from . ' ' . $this->time_from)) . "'"; } if ($this->date_to) { $sql_where[] = "`sd`.`measuring_timestamp` <= '" . date('Y-m-d H:i:s', strtotime($this->date_to . ' ' . $this->time_to)) . "'"; } //---------------- End date filter //---------------- Start Station filter if ($this->station_id) { $sql_where[] = "`sd`.`station_id` = '" . $this->station_id . "'"; } else { $sql_groupped_table = "SELECT `sensor_id`, MAX(`measuring_timestamp`) AS `MaxDateTime` FROM `" . SensorDataMinute::model()->tableName() . "` WHERE `" . $use_field . "` > 0 "; if ($this->date_from) { $sql_groupped_table .= " AND `measuring_timestamp` >= '" . date('Y-m-d H:i:s', strtotime($this->date_from . ' ' . $this->time_from)) . "' "; } if ($this->date_to) { $sql_groupped_table .= " AND `measuring_timestamp` <= '" . date('Y-m-d H:i:s', strtotime($this->date_to . ' ' . $this->time_to)) . "' "; } $sql_groupped_table .= " GROUP BY `sensor_id` "; $sql = "SELECT `tt`.`sensor_data_id`\n FROM `" . SensorDataMinute::model()->tableName() . "` `tt`\n INNER JOIN ( {$sql_groupped_table} ) `groupedtt` ON `tt`.`sensor_id` = `groupedtt`.`sensor_id` AND `tt`.`measuring_timestamp` = `groupedtt`.`MaxDateTime`"; $last_values = CStubActiveRecord::getDbConnect(true)->createCommand($sql)->queryColumn(); if (!$last_values) { $last_values = array(0); } $sql_where[] = "`sd`.`station_id` IN (" . implode(',', array_keys($stations)) . ") AND `sd`.`sensor_data_id` IN (" . implode(',', $last_values) . ")"; } //---------------- End Station filter if ($page_size > 0) { $sql = "SELECT COUNT(*)\n FROM `" . SensorDataMinute::model()->tableName() . "` `sd`\n WHERE " . implode(' AND ', $sql_where); $total = CStubActiveRecord::getDbConnect(true)->createCommand($sql)->queryScalar(); $pages = new CPagination($total); $pages->pageSize = $page_size; //$pages->applyLimit($criteria); } if ($this->order_field == 'date') { $sql_order = "`sd`.`measuring_timestamp` " . $this->order_direction; } elseif ($this->order_field == 'name') { $sql_order = "`st`.`display_name` " . $this->order_direction; } elseif ($this->order_field == 'lasttx') { $sql_order = "`sd`.`" . $use_field . "` " . $this->order_direction; } elseif ($this->order_field == 'lasthr') { $sql_order = "`sd`.`60min_sum` " . $this->order_direction; } elseif ($this->order_field == 'last24hr') { $sql_order = "`sd`.`1day_sum` " . $this->order_direction; } $sql = "SELECT `st`.`display_name`,\n `st`.`station_id_code`,\n `st`.`station_id`,\n\n `ll`.`message`,\n `ll`.`log_id`,\n\n `sd`.`sensor_data_id`,\n `sd`.`battery_voltage`,\n `sd`.`sensor_id`,\n `sd`.`measuring_timestamp`,\n DATE_FORMAT(`sd`.`measuring_timestamp`, '%m/%d/%Y') AS `tx_date_formatted`,\n DATE_FORMAT(`sd`.`measuring_timestamp`, '%H:%i') AS `tx_time_formatted`,\n `sd`.`sensor_value`,\n `sd`.`5min_sum`,\n `sd`.`10min_sum`,\n `sd`.`20min_sum`,\n `sd`.`30min_sum`,\n `sd`.`60min_sum`,\n `sd`.`1day_sum`,\n\n `sd`.`bucket_size`,\n `sd`.`1day_sum` AS `day_value_mm`,\n `sd`.`60min_sum` AS `hour_value_mm`\n\n FROM `" . SensorDataMinute::model()->tableName() . "` `sd`\n LEFT JOIN `" . ListenerLog::model()->tableName() . "` `ll` ON `sd`.`listener_log_id` = `ll`.`log_id`\n LEFT JOIN `" . Station::model()->tableName() . "` `st` ON `st`.`station_id` = `sd`.`station_id`\n\n WHERE " . implode(' AND ', $sql_where) . "\n ORDER BY {$sql_order} "; if ($page_size) { $sql .= " LIMIT " . $pages->currentPage * $pages->pageSize . ", " . $pages->pageSize; } $res = CStubActiveRecord::getDbConnect(true)->createCommand($sql)->queryAll(); if ($res) { $total_found = count($res); foreach ($res as $key => $value) { $res[$key]['battery_voltage_formatted'] = $value['battery_voltage'] / 10; $res[$key]['tx_value_mm'] = $value[$use_field] * $value['bucket_size']; $res[$key]['tx_value_rate_mm'] = $value[$use_field] * $value['bucket_size'] * 60 / $this->rate_volume; $res[$key]['day_value_mm'] = $value['day_value_mm'] * $value['bucket_size']; $res[$key]['hour_value_mm'] = $value['hour_value_mm'] * $value['bucket_size']; $res[$key]['period'] = $this->rate_volume; $hour_value_id = date('YmdH', strtotime($value['measuring_timestamp'])); $res[$key]['hour_value_id'] = $hour_value_id; $res[$key]['hour_value_rate_mm'] = 0; if ($stations[$value['station_id']]['filter_limit_max'] > 0) { if ($res[$key]['tx_value_mm'] >= $stations[$value['station_id']]['filter_limit_max']) { $res[$key]['filter_errors'][] = "R >= <b>" . $stations[$value['station_id']]['filter_limit_max'] . "</b> "; } } if ($stations[$value['station_id']]['filter_limit_min'] > 0) { if ($res[$key]['tx_value_mm'] <= $stations[$value['station_id']]['filter_limit_min']) { $res[$key]['filter_errors'][] = "R <= <b>" . $stations[$value['station_id']]['filter_limit_min'] . "</b> "; } } if ($stations[$value['station_id']]['filter_limit_diff'] > 0) { if ($key != 0 && abs($res[$key]['tx_value_mm'] - $res[$key - 1]['tx_value_mm']) >= $stations[$value['station_id']]['filter_limit_diff']) { $res[$key]['filter_errors'][] = "|R - R0| >= <b>" . $stations[$value['station_id']]['filter_limit_diff'] . "</b> "; } } } foreach ($res as $key => $value) { if ($key != 0 && $res[$key]['hour_value_id'] != $res[$key - 1]['hour_value_id']) { $res[$key - 1]['hour_value_rate_mm'] = $res[$key - 1]['hour_value_mm']; } elseif ($key == $total_found - 1) { $res[$key]['hour_value_rate_mm'] = $res[$key]['hour_value_mm']; } } } } return array('list' => $res, 'pages' => $pages); }
/** * Returns the data model based on the primary key given in the GET variable. * If the data model is not found, an HTTP exception will be raised. * @param integer $id the ID of the model to be loaded * @return Station the loaded model * @throws CHttpException */ public function loadModel($id) { $model = Station::model()->findByPk($id); if ($model === null) { throw new CHttpException(404, 'The requested page does not exist.'); } return $model; }
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)); } }
/** * create statistics now * @param null $report */ private function ini($report = null) { date_default_timezone_set("UTC"); if (is_null($report)) { $report = new HeartbeatReport(); $report->created = date("Y-m-d H:i:s"); $report->period = date("Y-m-d H:i:s", time() - 24 * 60 * 60); } $minute_in_period = (strtotime($report->created) - strtotime($report->period)) / 60; $databases = array('db' => false, 'db_long' => true); $config_file = array('db' => 'db_params', 'db_long' => 'db_long_params'); $sql = "SELECT TABLE_NAME, `DATA_LENGTH`+`INDEX_LENGTH` as 'TABLE_SIZE', TABLE_ROWS\r\n FROM information_schema.TABLES\r\n WHERE `table_schema` LIKE :db;" . ""; foreach ($databases as $database => $use_long) { /* * $db_stat * $db_long_stat */ $res = CStubActiveRecord::getDbConnect($use_long)->createCommand("SHOW STATUS;")->queryAll(); foreach ($res as $val) { $this->{$database . '_stat'}[$val['Variable_name']] = $val['Value']; } /* * $db_tables_size * $db_long_tables_size * $db_tables_rows * $db_long_tables_rows */ // $db_conf = new ConfigManager($config_file[$database]); // $db_name = $db_conf::getConfigSection('database', 'dbname'); $db_name = Yii::app()->params[$config_file[$database]]['dbname']; $res = CStubActiveRecord::getDbConnect($use_long)->createCommand($sql)->bindValue(':db', $db_name)->queryAll(); $allSize = $allCount = 0; foreach ($res as $table) { $allSize += $this->{$database . '_tables_size'}[$table['TABLE_NAME']] = number_format($table['TABLE_SIZE'] / 1024 / 1024, 2); $allCount += $this->{$database . '_tables_rows'}[$table['TABLE_NAME']] = $table['TABLE_ROWS']; } $this->{$database . '_tables_size'}['All'] = $allSize; $this->{$database . '_tables_rows'}['All'] = $allCount; if ($use_long) { /* * $stations * $stations_logger * $stations_communication_type * $stations_message_interval * $stations_message_count * $stations_message_expected * $stations_message_error * $stations_message_is_processing * $stations_message_last * $stations_schedule_synop * $stations_schedule_bufr * $stations_schedule_speci * $stations_schedule_metar * $stations_schedule_data_export */ $criteria = new CDbCriteria(); $criteria->index = 'station_id'; $criteria->with = array('messages' => array('together' => false, 'select' => 'messages.measuring_timestamp, messages.failed, is_processing', 'condition' => 'messages.measuring_timestamp > \'' . $report->period . '\'', 'order' => 'messages.measuring_timestamp DESC'), 'schedule' => array('select' => 'schedule.report_type, schedule.period', 'together' => false, 'with' => array('processed' => array('select' => 'processed.updated', 'together' => false, 'condition' => 'processed.updated > \'' . $report->period . '\'')))); $stations = Station::model()->long()->findAll($criteria); foreach ($stations as $station_id => $station) { $this->stations[$station_id] = $station['display_name']; $this->stations_logger[$station_id] = $station['logger_type']; $this->stations_communication_type[$station_id] = $station['communication_type']; $this->stations_message_interval[$station_id] = $station['event_message_period']; $this->stations_message_count[$station_id] = count($station->messages); $this->stations_message_expected[$station_id] = $minute_in_period / $station['event_message_period']; foreach ($station->messages as $message) { $this->stations_message_error[$station_id] += $message['failed']; $this->stations_message_is_processing[$station_id] += $message['is_processing']; } $this->stations_message_last[$station_id] = $station->messages[0]['measuring_timestamp']; $logIds[] = $station->messages[0]['log_id']; foreach ($station->schedule as $schedule) { $schedule_count = count($schedule->processed); if ($schedule_count) { if ($schedule->report_type == 'speci') { $this->stations_schedule_speci[$station_id]['gen'] += $schedule_count; } else { $this->{'stations_schedule_' . $schedule->report_type}[$station_id]['gen'] += $schedule_count; $this->{'stations_schedule_' . $schedule->report_type}[$station_id]['sch'] += number_format($minute_in_period / $schedule->period); } } } } foreach ($this as $key => $arr) { if (stripos($key, 'stations_schedule_') !== false && !is_null($arr)) { foreach ($arr as $station_id => $val) { if (!is_null($val['gen']) && !is_null($val['sch'])) { $this->{$key}[$station_id] = $val['sch'] . ' / ' . $val['gen'] . ' = ' . number_format($val['gen'] / $val['sch'] * 100) . '%'; } elseif (stripos($key, 'speci') === false) { $this->{$key}[$station_id] = $val['sch'] . ' / ' . $val['gen']; } else { $this->{$key}[$station_id] = $val['gen']; } } } } /* * $stations_bv_last */ $criteria = new CDbCriteria(); $criteria->with = array('sensor_feature' => array('select' => false, 'joinType' => 'INNER JOIN', 'condition' => 'sensor_feature.feature_code = \'battery_voltage\'')); $criteria->index = 'station_id'; $criteria->compare('listener_log_id', $logIds); $bv_data = SensorData::model()->long()->findAll($criteria); foreach ($stations as $station_id => $station) { $this->stations_sensor_bv[$station_id] = $bv_data[$station_id]['sensor_feature_value']; } } } /* * $system */ $this->system['disk_free_space'] = number_format(disk_free_space('/') / pow(10, 9), 2) . 'Gb'; $this->system['disk_total_space'] = number_format(disk_total_space('/') / pow(10, 9), 2) . 'Gb'; }
public function getExport($station_id) { $station = Station::model()->with(array('sensors.handler' => array('alias' => 'hh'), 'sensors.features.metric', 'station_calculation.station_calculation_variable.sensor_feature.sensor' => array('alias' => 'feat'), 'station_calculation.handler.metric' => array('alias' => 'metr')))->findbyPk($station_id); if (!is_null($station)) { $stationParams = $station->getAttributes(); $file_name = $station->station_id . '_' . $station->station_id_code . '_' . $station->updated . '.conf'; $file_content['station'] = $stationParams; if (count($station['sensors'])) { foreach ($station['sensors'] as $sensor) { $sensor_to_add = array(); $sensor_to_add['display_name'] = $sensor->display_name; $sensor_to_add['handler'] = $sensor['handler']->handler_id_code; $sensor_to_add['calculation']['DewPoint'] = $sensor->hasCalculation('DewPoint'); $sensor_to_add['calculation']['PressureSeaLevel'] = $sensor->hasCalculation('PressureSeaLevel'); $sensor_to_add['features'] = array(); if (count($sensor['features'])) { foreach ($sensor['features'] as $feature) { $featuresToAdd = array(); $featuresToAdd['feature_constant_value'] = $feature->feature_constant_value; $featuresToAdd['feature_display_name'] = $feature->feature_display_name; $featuresToAdd['feature_code'] = $feature->feature_code; $featuresToAdd['metric'] = array(); if ($feature['metric']) { $featuresToAdd['metric'] = $feature['metric']->getAttributes(); } $sensor_to_add['features'][] = $featuresToAdd; } } $file_content['sensors'][] = $sensor_to_add; } } if (count($station['station_calculation'])) { foreach ($station['station_calculation'] as $key => $calc) { if (is_object($calc)) { $file_content['station_calculation'][$key]['formula'] = $calc->formula; $file_content['station_calculation'][$key]['handler'] = $calc['handler']->handler_id_code; $file_content['station_calculation'][$key]['metric_id'] = $calc['handler']->metric_id; } } } $file_content = json_encode($file_content, 1); return array('file_content' => $file_content, 'file_name' => $file_name); } return array('file_name' => 'There_is_no_station'); }
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)); } }
protected function getStationByCode($code) { return Station::model()->findAllByAttributes(array('communication_type' => 'gprs', 'station_id_code' => $code)); }
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 getStationByCode($station_id_code, $relations) { $criteria = new CDbCriteria(); $criteria->addCondition("station_id_code=:station_id_code"); $criteria->params = array("station_id_code" => $station_id_code); $criteria->with = $relations; return Station::model()->find($criteria); }
public function m_0_4_1() { @apache_setenv('no-gzip', 1); @ini_set('zlib.output_compression', 0); @ini_set('implicit_flush', 1); ini_set('memory_limit', '-1'); //ob_start(); $this->flushNotification('...Please wait... Updating is going on... DO NOT LEAVE THIS PAGE!'); $this->flushNotification('<br/>...Going to add "is_last" fields to `listener_log` table...'); $res = Yii::app()->db->createCommand("SHOW COLUMNS FROM `" . ListenerLog::model()->tableName() . "` LIKE 'is_last'")->queryAll(); if (!$res) { Yii::app()->db->createCommand("ALTER TABLE `" . ListenerLog::model()->tableName() . "` ADD `is_last` tinyint(1) NOT NULL DEFAULT '0' AFTER `is_processed`")->query(); Yii::app()->db->createCommand("COMMIT")->query(); $stations = Yii::app()->db->createCommand("SELECT * FROM `" . Station::model()->tableName() . "`")->queryAll(); if ($stations) { foreach ($stations as $key => $value) { ListenerLog::updateIsLastForStation($value['station_id']); } } $this->flushNotification(' ... done'); } else { $this->flushNotification(' ... not need'); } $this->flushNotification('<br/>...Going to add "is_processing" column to `listener_log` table...'); $res = Yii::app()->db->createCommand("SHOW COLUMNS FROM `" . ListenerLog::model()->tableName() . "` LIKE 'is_processing'")->queryAll(); if (!$res) { Yii::app()->db->createCommand("ALTER TABLE `" . ListenerLog::model()->tableName() . "` ADD `is_processing` TINYINT(1) NOT NULL DEFAULT '0' AFTER `is_processed`")->query(); Yii::app()->db->createCommand("COMMIT")->query(); $this->flushNotification(' ... done'); } else { $this->flushNotification(' ... not need'); } // station $this->flushNotification('<br/><br/>Station table:'); $this->flushNotification('<br/>...Going to update "national_aws_number" field at `station` table...'); $res = Yii::app()->db->createCommand("SHOW COLUMNS FROM `" . Station::model()->tableName() . "` LIKE 'national_aws_number'")->queryAll(); if ($res[0]['Null'] == 'NO') { Yii::app()->db->createCommand("ALTER TABLE `station` CHANGE `national_aws_number` `national_aws_number` int(11) DEFAULT '0'")->query(); Yii::app()->db->createCommand("COMMIT")->query(); $this->flushNotification(' ... done'); } else { $this->flushNotification(' ... not need'); } $this->flushNotification('<br/>...Going to add new column "country_id" to `station` table...'); $res = Yii::app()->db->createCommand("SHOW COLUMNS FROM `" . Station::model()->tableName() . "` LIKE 'country_id'")->queryAll(); if (!$res) { Yii::app()->db->createCommand("ALTER TABLE `station` ADD `country_id` int(11) NOT NULL DEFAULT '0' AFTER `magnetic_north_offset`")->query(); Yii::app()->db->createCommand("COMMIT")->query(); $this->flushNotification(' ... done'); } else { $this->flushNotification(' ... already exists'); } $this->flushNotification('<br/>...Going to add new column "city_id" to `station` table...'); $res = Yii::app()->db->createCommand("SHOW COLUMNS FROM `" . Station::model()->tableName() . "` LIKE 'city_id'")->queryAll(); if (!$res) { Yii::app()->db->createCommand("ALTER TABLE `station` ADD `city_id` int(11) NOT NULL DEFAULT '0' AFTER `country_id`")->query(); Yii::app()->db->createCommand("COMMIT")->query(); $this->flushNotification(' ... done'); } else { $this->flushNotification(' ... already exists'); } $this->flushNotification('<br/>...Going to add new column "timezone_offset" to `station` table...'); $res = Yii::app()->db->createCommand("SHOW COLUMNS FROM `" . Station::model()->tableName() . "` LIKE 'timezone_offset'")->queryAll(); if (!$res) { Yii::app()->db->createCommand("ALTER TABLE `station` ADD `timezone_offset` varchar(20) NOT NULL AFTER `timezone_id`")->query(); Yii::app()->db->createCommand("COMMIT")->query(); $this->flushNotification(' ... done'); } else { $this->flushNotification(' ... already exists'); } $this->flushNotification('<br/>...Going to update "timezone_offset" data in `station` table...'); $sql = "SELECT `station_id`, `timezone_id` FROM `" . Station::model()->tableName() . "` WHERE `timezone_offset` = ''"; $res = Yii::app()->db->createCommand($sql)->queryAll(); if ($res) { foreach ($res as $key => $value) { $sql = "UPDATE `" . Station::model()->tableName() . "` SET `timezone_offset` = '" . TimezoneWork::getOffsetFromUTC($value['timezone_id'], 1) . "' WHERE `station_id` = '" . $value['station_id'] . "'"; Yii::app()->db->createCommand($sql)->query(); } Yii::app()->db->createCommand("COMMIT")->query(); $this->flushNotification(' ... done'); } else { $this->flushNotification(' ... not need'); } $this->flushNotification('<br/>...Going to add new column "awos_msg_source_folder" to `station` table...'); $res = Yii::app()->db->createCommand("SHOW COLUMNS FROM `" . Station::model()->tableName() . "` LIKE 'awos_msg_source_folder'")->queryAll(); if (!$res) { Yii::app()->db->createCommand("ALTER TABLE `station` ADD `awos_msg_source_folder` TEXT CHARACTER SET ucs2 COLLATE ucs2_general_ci NOT NULL AFTER `city_id`")->query(); Yii::app()->db->createCommand("COMMIT")->query(); $this->flushNotification(' ... done'); } else { $this->flushNotification(' ... already exists'); } // Sensor Data $this->flushNotification('<br/>...Going to update `sensor_data` table\'s data...'); Yii::app()->db->createCommand("UPDATE `sensor_data` SET `period` = 1440 WHERE `period` = 86400")->query(); Yii::app()->db->createCommand("COMMIT")->query(); $this->flushNotification(' ... done'); //Schedule report $this->flushNotification('<br/>...Going to create `schedule_report` table...'); $tables = array(); $res = Yii::app()->db->createCommand("SHOW TABLES")->queryAll(); if ($res) { foreach ($res as $key => $value) { foreach ($value as $k1 => $v1) { $tables[] = $v1; } } } if (!in_array('schedule_report', $tables)) { $sql = "CREATE TABLE `schedule_report` (\n `schedule_id` int(11) NOT NULL AUTO_INCREMENT,\n `report_type` varchar(50) NOT NULL DEFAULT 'synop' COMMENT 'synop, bufr',\n `station_id` int(11) NOT NULL,\n `period` int(11) NOT NULL DEFAULT '60' COMMENT 'in minutes',\n `method` varchar(20) NOT NULL DEFAULT 'email' COMMENT 'email, ftp',\n `destination_email` varchar(255) NOT NULL,\n `destination_ip` varchar(15) NOT NULL,\n `destination_ip_port` int(5) NOT NULL DEFAULT '21',\n `destination_ip_folder` varchar(255) NOT NULL DEFAULT '/',\n `destination_ip_user` varchar(255) NOT NULL,\n `destination_ip_password` varchar(255) NOT NULL,\n `report_format` varchar(20) NOT NULL DEFAULT 'csv' COMMENT 'txt, csv',\n `last_scheduled_run_fact` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',\n `last_scheduled_run_planned` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',\n `created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',\n `updated` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',\n PRIMARY KEY (`schedule_id`)\n ) ENGINE=InnoDB DEFAULT CHARSET=utf8;"; Yii::app()->db->createCommand($sql)->query(); Yii::app()->db->createCommand("COMMIT")->query(); $this->flushNotification(' ... done'); } else { $this->flushNotification(' ... already exists'); } $this->flushNotification('<br/>...Going to create `schedule_report_processed` table...'); if (!in_array('schedule_report_processed', $tables)) { $sql = "CREATE TABLE `schedule_report_processed` (\n `schedule_processed_id` int(11) NOT NULL AUTO_INCREMENT,\n `schedule_id` int(11) NOT NULL,\n `listener_log_id` int(11) NOT NULL,\n `is_processed` tinyint(1) NOT NULL DEFAULT '0',\n `report_string_initial` text NOT NULL,\n `report_string_changed` text NOT NULL,\n `serialized_report_problems` text NOT NULL,\n `serialized_report_errors` text NOT NULL,\n `serialized_report_explanations` text NOT NULL,\n `is_last` tinyint(1) NOT NULL DEFAULT '0',\n `created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',\n `updated` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',\n PRIMARY KEY (`schedule_processed_id`),\n KEY `schedule_id` (`schedule_id`),\n KEY `listener_log_id` (`listener_log_id`),\n CONSTRAINT `schedule_report_processed_fk` FOREIGN KEY (`schedule_id`) REFERENCES `schedule_report` (`schedule_id`) ON DELETE CASCADE ON UPDATE NO ACTION,\n CONSTRAINT `schedule_report_processed_ibfk_1` FOREIGN KEY (`listener_log_id`) REFERENCES `listener_log` (`log_id`) ON DELETE CASCADE ON UPDATE NO ACTION\n ) ENGINE=InnoDB DEFAULT CHARSET=utf8;"; Yii::app()->db->createCommand($sql)->query(); Yii::app()->db->createCommand("COMMIT")->query(); $this->flushNotification(' ... done'); } else { $this->flushNotification(' ... already exists'); } // metrics $this->flushNotification('<br/><br/>New Metrics:'); $this->flushNotification('<br/>...Going to add new metric "kJ/sq.m" ...'); Yii::app()->db->createCommand("INSERT INTO `refbook_metric` (`metric_id`, `html_code`, `short_name`, `full_name`, `code`) VALUES ('21', 'kJ/sq.m', 'kJ/sq.m', 'Kilo Joule per square meter', 'kjoule_per_sq_meter')")->query(); Yii::app()->db->createCommand("COMMIT")->query(); $this->flushNotification(' ... done'); $this->flushNotification('<br/>...Going to add new metric "feet"...'); Yii::app()->db->createCommand("INSERT INTO `refbook_metric` (`metric_id`, `html_code`, `short_name`, `full_name`, `code`) VALUES ('22', 'ft', 'ft', 'Feet', 'feet')")->query(); Yii::app()->db->createCommand("COMMIT")->query(); $this->flushNotification(' ... done'); $this->flushNotification('<br/>...Going to add new metric "km"...'); Yii::app()->db->createCommand("INSERT INTO `refbook_metric` (`metric_id`,`html_code`,`short_name`,`full_name`,`code`) VALUES (23 , 'km', 'km', 'Kilometer', 'kilometer')")->query(); Yii::app()->db->createCommand("COMMIT")->query(); $this->flushNotification(' ... done'); $this->flushNotification('<br/>...Going to add new relation between "solar radiation" and "kj/sq.m"...'); RefbookMeasurementTypeMetric::model()->deleteByPk(23); Yii::app()->db->createCommand("INSERT INTO `refbook_measurement_type_metric` (`measurement_type_metric_id`, `measurement_type_id`, `metric_id`, `is_main`) VALUES ('23', '9', '21', '0')")->query(); Yii::app()->db->createCommand("COMMIT")->query(); $this->flushNotification(' ... done'); $this->flushNotification('<br/>...Going to add new measuring type "Cloud Vertical Visibility"...'); Yii::app()->db->createCommand("INSERT INTO `refbook_measurement_type` (`measurement_type_id`, `display_name`, `code`, `ord`) VALUES (16 ,'Cloud Vertical Visibility', 'cloud_vertical_visibility', '14')")->query(); Yii::app()->db->createCommand("COMMIT")->query(); $this->flushNotification(' ... done'); $this->flushNotification('<br/>...Going to add new measuring type "Cloud Height" ...'); Yii::app()->db->createCommand("INSERT INTO `refbook_measurement_type` (`measurement_type_id`, `display_name`, `code`, `ord`) VALUES (17, 'Cloud Height', 'cloud_height', 15)")->query(); Yii::app()->db->createCommand("COMMIT")->query(); $this->flushNotification(' ... done'); $this->flushNotification('<br/>...Going to add new measuring type "Sea Level" ...'); Yii::app()->db->createCommand("INSERT INTO `refbook_measurement_type` (`measurement_type_id`, `display_name`, `code`, `ord`) VALUES (18 , 'Sea Level (Mean, Sigma, Wave Hight)', 'sea_level', '16')")->query(); Yii::app()->db->createCommand("COMMIT")->query(); $this->flushNotification(' ... done'); $this->flushNotification('<br/>...Going to add relation between "Cloud Vertical Visibility" and "ft" ...'); Yii::app()->db->createCommand("INSERT INTO `refbook_measurement_type_metric` (`measurement_type_metric_id`, `measurement_type_id`, `metric_id`, `is_main`) VALUES (24,16,22,1)")->query(); Yii::app()->db->createCommand("COMMIT")->query(); $this->flushNotification(' ... done'); $this->flushNotification('<br/>...Going to add new relation between "Cloud Vertical Visibility" and "meter"...'); Yii::app()->db->createCommand("INSERT INTO `refbook_measurement_type_metric` (`measurement_type_metric_id`, `measurement_type_id`, `metric_id`, `is_main`) VALUES (25,16,11,0)")->query(); Yii::app()->db->createCommand("COMMIT")->query(); $this->flushNotification(' ... done'); $this->flushNotification('<br/>...Going to add new relation between "Cloud Height" and "ft"...'); Yii::app()->db->createCommand("INSERT INTO `refbook_measurement_type_metric` (`measurement_type_metric_id`, `measurement_type_id`, `metric_id`, `is_main`) VALUES (26,17,22,1)")->query(); Yii::app()->db->createCommand("COMMIT")->query(); $this->flushNotification(' ... done'); $this->flushNotification('<br/>...Going to add new relation between "Cloud Height" and "meter"...'); Yii::app()->db->createCommand("INSERT INTO `refbook_measurement_type_metric` (`measurement_type_metric_id`, `measurement_type_id`, `metric_id`, `is_main`) VALUES (27,17,11,0)")->query(); Yii::app()->db->createCommand("COMMIT")->query(); $this->flushNotification(' ... done'); $this->flushNotification('<br/>...Going to add new relation between "Visibility" and "meter"...'); Yii::app()->db->createCommand("INSERT INTO `refbook_measurement_type_metric` (`measurement_type_metric_id`, `measurement_type_id`, `metric_id`, `is_main`) VALUES (28, 11, 11, 1)")->query(); Yii::app()->db->createCommand("COMMIT")->query(); $this->flushNotification(' ... done'); $this->flushNotification('<br/>...Going to add new relation between "Sea Level" and "meter"...'); Yii::app()->db->createCommand("INSERT INTO `refbook_measurement_type_metric` (`measurement_type_metric_id`, `measurement_type_id`, `metric_id`, `is_main`) VALUES (29, 18, 11, 1)")->query(); Yii::app()->db->createCommand("COMMIT")->query(); $this->flushNotification(' ... done'); // sensor handler $this->flushNotification('<br/>...Going to add new column "awos_station_uses" to `sensor_handler` table...'); $res = Yii::app()->db->createCommand("SHOW COLUMNS FROM `sensor_handler` LIKE 'awos_station_uses'")->queryAll(); if (!$res) { $res = Yii::app()->db->createCommand("ALTER TABLE `sensor_handler` ADD `awos_station_uses` TINYINT( 1 ) NOT NULL DEFAULT '0'")->query(); Yii::app()->db->createCommand("COMMIT")->query(); $this->flushNotification(' ... done'); } else { $this->flushNotification(' ... already exists'); } $this->flushNotification('<br/>...Going to update `sensor_handler` table...'); $sql = "UPDATE `sensor_handler` SET \n `handler_id_code` = 'SeaLevelAWS',\n `display_name` = 'Sea Level and Tide Data',\n `description` = 'Handler \"Sea Level and Tide Data\" : Processes string like \"SL1XXXXYYYYZZZZ\", where <br/>SL1 - device Id; <br/>XXXX - Mean value;<br/>YYYY - Sigma value; <br/>ZZZZ - Wave Height <br/>Example: SL1179017900140 = SL1 sensor sent data: Mean value = 1.79, Sigma value = 1.79, Wave height = 140m.',\n `default_prefix` = 'SL',\n `aws_station_uses` = 1,\n `rain_station_uses` = 0,\n `awos_station_uses` = 0,\n `aws_single_group` = 'sea_level'\n WHERE `handler_id` = 13"; Yii::app()->db->createCommand($sql)->query(); Yii::app()->db->createCommand("COMMIT")->query(); $sql = "UPDATE `sensor_handler` SET \n `handler_id_code` = 'VisibilityAWS',\n `display_name` = 'Visibility',\n `description` = 'Handler \"Visibility\"',\n `default_prefix` = 'VI',\n `aws_station_uses` = 1,\n `rain_station_uses` = 0,\n `awos_station_uses` = 0,\n `aws_single_group` = 'visibility'\n WHERE `handler_id` = 14"; Yii::app()->db->createCommand($sql)->query(); Yii::app()->db->createCommand("COMMIT")->query(); $sql = "UPDATE `sensor_handler` SET `handler_id_code` = 'CloudHeightAWS',\n `display_name` = 'Cloud Height',\n `description` = 'Handler \"Cloud Height\"',\n `default_prefix` = 'CH',\n `aws_station_uses` = 1,\n `rain_station_uses` = 0,\n `awos_station_uses` = 0,\n `aws_single_group` = 'clouds'\n WHERE `handler_id` = 15"; Yii::app()->db->createCommand($sql)->query(); Yii::app()->db->createCommand("COMMIT")->query(); $this->flushNotification(' ... done'); $this->flushNotification('<br/>...Going to update calculation_handler table...'); $res = Yii::app()->db->createCommand("UPDATE `calculation_handler` SET `display_name` = 'Pressure Adjusted to MSL' WHERE `handler_id` = 2"); Yii::app()->db->createCommand("COMMIT")->query(); $this->flushNotification(' ... done'); $bat_path = dirname(Yii::app()->request->scriptFile) . DIRECTORY_SEPARATOR . 'files' . DIRECTORY_SEPARATOR . 'at' . DIRECTORY_SEPARATOR . 'schedule.bat'; $schedule_bat_content = getConfigValue('php_exe_path') . " -f " . dirname(Yii::app()->request->scriptFile) . DIRECTORY_SEPARATOR . "console.php schedule"; file_put_contents($bat_path, $schedule_bat_content); exec('schtasks /create /sc minute /mo 1 /F /ru "SYSTEM" /tn delaircoScheduleScript /tr ' . $bat_path, $output); $values = getConfigValue('schedule'); $values['each_minute_process_id'] = 'delaircoScheduleScript'; InstallConfig::setConfigSection('schedule', $values); $values = getConfigValue('path'); $values['site_url_for_console'] = It::baseUrl(); InstallConfig::setConfigSection('path', $values); It::memStatus('update__success'); $this->flushNotification('<script type="text/javascript"> setTimeout(function(){document.location.href="' . Yii::app()->controller->createUrl('update/index') . '"}, 10000)</script>'); }
public function prepareList() { $result = ['series_names' => [], 'series_data' => []]; date_default_timezone_set('UTC'); $start_datetime = strtotime($this->date_from . ' ' . $this->time_from); $end_datetime = strtotime($this->date_to . ' ' . $this->time_to); $series_names = array(); $series_data = array(); if (!$this->hasErrors() && $this->station_id) { $stationResult = Station::model()->getStationsWithSensorsFeatures($this->station_id); foreach ($this->sensor_feature_code as $group_code => $group) { if (!empty($group)) { $i = 0; foreach ($stationResult as $station) { $colorWalker = new Color($station->color); foreach ($group as $sensor_feature_code) { //sensors data foreach ($station->sensors as $sensor) { foreach ($sensor->features as $sensorFeature) { if ($sensor_feature_code == $sensorFeature->feature_code) { $series_names[$i]['name'] = $station->station_id_code . ', ' . $sensor->sensor_id_code . ' ' . $this->getGroupSensorsFeaturesList()[$sensor->handler->handler_id_code]['sensor_features'][$sensorFeature->feature_code]; $series_names[$i]['params']['color'] = '#' . $colorWalker->getHex(); // $colorWalker->mix('888888'); $colorWalker->darken(); $qb = new CDbCriteria(); $qb->select = 't.sensor_feature_normalized_value, t.sensor_feature_value, t.measuring_timestamp'; $qb->addCondition('t.sensor_feature_id = :sensor_feature_id'); $qb->addBetweenCondition('t.measuring_timestamp', date('Y-m-d H:i:s', $start_datetime), date('Y-m-d H:i:s', $end_datetime)); $qb->order = 't.measuring_timestamp ASC'; $qb->params[':sensor_feature_id'] = $sensorFeature->sensor_feature_id; $found_data = SensorData::model()->long()->findAll($qb); $tmp = array(); if ($this->accumulation_period != 0) { foreach ($found_data as $data) { $period = $start_datetime + (intval((strtotime($data->measuring_timestamp) - $start_datetime) / ($this->accumulation_period * 60)) + 1) * $this->accumulation_period * 60; $period = $period > $end_datetime ? $end_datetime : $period; $period *= 1000; $tmp[$period] = ['x' => $period, 'y' => $tmp[$period] ? $tmp[$period]['y'] : 0 + floatval($data->sensor_feature_value)]; } } else { foreach ($found_data as $data) { $period = strtotime($data->measuring_timestamp) * 1000; $tmp[$period] = ['x' => $period, 'y' => floatval($data->sensor_feature_value)]; } } $series_data[$i] = array_values($tmp); $i++; } } } //calculation data foreach ($station->station_calculation as $stationCalculation) { if ($stationCalculation->handler_id == $this->calc_handlers[$sensor_feature_code]) { $colorWalker = new Color($station->color); $series_names[$i]['name'] = $station->station_id_code . ', ' . $stationCalculation->handler->display_name; $series_names[$i]['params']['color'] = '#' . $colorWalker->darken(); $start_datetime = strtotime($this->date_from . ' ' . $this->time_from); $end_datetime = strtotime($this->date_to . ' ' . $this->time_to); $qb = new CDbCriteria(); $qb->with = ['ListenerLog' => ['select' => 'ListenerLog.measuring_timestamp', 'condition' => "ListenerLog.measuring_timestamp BETWEEN '" . date('Y-m-d H:i:s', $start_datetime) . "' AND '" . date('Y-m-d H:i:s', $end_datetime) . "'"]]; $qb->select = 't.calculation_id, t.value'; $qb->order = 'ListenerLog.measuring_timestamp ASC'; $qb->condition = 't.calculation_id = ?'; $qb->params = [$stationCalculation->calculation_id]; $found_data = StationCalculationData::model()->long()->findAll($qb); $tmp = array(); foreach ($found_data as $data) { $tmp[] = ['x' => strtotime($data->ListenerLog->measuring_timestamp) * 1000, 'y' => floatval($data->value)]; } $series_data[$i] = $tmp; } } //custom_wind_rose if (in_array($sensor_feature_code, array_keys($this->custom_sensor_features))) { switch ($sensor_feature_code) { case 'custom_wind_rose': return $this->prepareCustomWindRose(); default: return array(); } } } $i++; } } } } $result['series_names'] = array_values($series_names); $result['series_data'] = array_values($series_data); /** * depracated */ if (!$this->hasErrors() && $this->station_id && true == false) { foreach ($this->sensor_feature_code as $group_code => $group) { if (!empty($group)) { foreach ($group as $sensor_feature_code) { if (in_array($sensor_feature_code, array_keys($this->calc_handlers))) { $data = $this->prepareCalculationList($this->calc_handlers[$sensor_feature_code]); } elseif (in_array($sensor_feature_code, array_keys($this->custom_sensor_features))) { switch ($sensor_feature_code) { case 'custom_wind_rose': return $this->prepareCustomWindRose(); default: return array(); } } else { $data = $this->prepareSensorList($sensor_feature_code, $group_code); } $result['series_names'] = array_merge($result['series_names'], $data['series_names']); $result['series_data'] = array_merge($result['series_data'], $data['series_data']); } } } } if (empty($result['series_names'])) { return array(); } return $result; }
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); }
/** * Gets station information * * @param string $station_id_code * @param int $station_id * @return boolean */ function getStation($station_id_code = '', $station_id = 0) { $this->_logger->log(__METHOD__, array('station_id_code' => $station_id_code, 'station_id' => $station_id)); if ($station_id_code) { $station = Station::model()->find('station_id_code = :station_id_code', array(':station_id_code' => $station_id_code)); } else { if ($station_id) { $station = Station::model()->find('station_id = :station_id', array(':station_id' => $station_id)); } } if (!$station) { $this->pushError('unknown_station', 'Can not find station for station_id_code="' . $station_id_code . '" in the DB, station_id="' . $station_id . '"'); } else { $this->message_obj->station_id = $station->station_id; $this->_station = $station; $this->_logger->log(__METHOD__ . ' Station detected', array('id' => $station->station_id, 'code' => $station->station_id_code, 'timezone' => $station->timezone_id)); return true; } return false; }
public function prepareList($page_size = 50) { $sql_where_str = $this->_prepareSqlCondition(); $sql_order_str = $this->_prepareSqlOrder(); if ($page_size) { $total = $this->total; } if ($total || !$page_size) { if ($page_size) { $pages = new CPagination($total); $pages->pageSize = $page_size; } $sql = "SELECT `t1`.`log_id`, `t1`.`updated`,`t1`.`measuring_timestamp`, `t1`.`created`, `t1`.`message`, `t1`.`fail_description`, t3.station_id_code, t3.display_name, t3.station_type, `t2`.`process_error_id`, `t1`.`is_processed`\n FROM `" . ListenerLog::model()->tableName() . "` t1\n LEFT JOIN `" . ListenerLogProcessError::model()->tableName() . "` `t2` ON `t2`.`log_id` = `t1`.`log_id`\n LEFT JOIN `" . Station::model()->tableName() . "` `t3` ON `t3`.`station_id` = `t1`.`station_id`\n {$sql_where_str}\n GROUP BY `t1`.`log_id`\n {$sql_order_str}"; if ($page_size) { $sql .= " LIMIT " . $pages->currentPage * $pages->pageSize . ", " . $pages->pageSize; } $res = CStubActiveRecord::getDbConnect(true)->createCommand($sql)->queryAll(); if ($res) { $ids = array(); foreach ($res as $key => $value) { $ids[] = $value['log_id']; if ($value['fail_description']) { $value['errors'] = explode(',', $value['fail_description']); } $list[$value['log_id']] = $value; } $sql = "SELECT * FROM `" . ListenerLogProcessError::model()->tableName() . "` WHERE `log_id` IN (" . implode(',', $ids) . ")"; $res2 = CStubActiveRecord::getDbConnect(true)->createCommand($sql)->queryAll(); if ($res2) { foreach ($res2 as $key => $value) { if ($value['type'] == 'error') { $list[$value['log_id']]['errors'][] = $value['description']; } else { if ($value['type'] == 'warning') { $list[$value['log_id']]['warnings'][] = $value['description']; } } } } } } return array('list' => $list, 'pages' => $pages); }