protected function setHandlers() { $criteria = new CDbCriteria(); // 1 or 2: AWS or Rain station $criteria->addCondition('flags & 3 > 0'); $criteria->order = "display_name"; $criteria->index = 'handler_id'; $this->handlers = SensorDBHandler::model()->findAll($criteria); }
protected function setHandlers() { $criteria = new CDbCriteria(); // 1 or 2: AWS or Rain station $criteria->addCondition('flags & 3 > 0'); $criteria->order = "display_name"; $criteria->index = 'handler_id'; $this->handlers = SensorDBHandler::model()->with('features.metric')->findAll($criteria); foreach ($this->handlers as $handler) { $this->sensor_handlers[$handler->handler_id_code] = SensorHandler::create($handler->handler_id_code); } // echo "<pre>"; // print_r($this->sensor_handlers); // print_r($this->handlers); // echo "</pre>"; // exit; }
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 static function handlerWithFeature(&$handlers, $for) { $criteria = new CDbCriteria(); $criteria->with = array('features'); $criteria->index = 'handler_id'; switch ($for) { case 'aws_panel': $criteria->compare('features.aws_panel_show', 1); $criteria->compare('t.aws_station_uses', 1); $criteria->addCondition('t.aws_panel_show > 0'); $criteria->order = "t.aws_panel_display_position asc"; break; case 'aws_table': case 'aws_graph': $criteria->addCondition('flags & 9 = 9 OR flags & 17 = 17'); $criteria->order = 'display_name asc'; break; case 'rg': $criteria->compare('t.rain_station_uses', 1); break; } $handlers = SensorDBHandler::model()->findAll($criteria); return array_keys($handlers); }
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 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; }
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 actionMsgGeneration() { ini_set('memory_limit', '-1'); $form = new GenerateMessageForm(); $messages = array(); $sensors = array(); if (Yii::app()->request->isPostRequest && (isset($_POST['generate']) || isset($_POST['import']))) { $form->attributes = $_POST['GenerateMessageForm']; if ($form->validate()) { $sensors = array(); if ($form->sensor_id) { $sql = "SELECT `t1`.`station_sensor_id`, `t1`.`sensor_id_code`, `t2`.`handler_id_code`, `t3`.`feature_code`, `t4`.`code` AS `metric_code` \n FROM `" . StationSensor::model()->tableName() . "` `t1`\n LEFT JOIN `" . SensorDBHandler::model()->tableName() . "` `t2` ON `t2`.`handler_id` = `t1`.`handler_id`\n LEFT JOIN `" . StationSensorFeature::model()->tableName() . "` `t3` ON `t3`.`sensor_id` = `t1`.`station_sensor_id`\n LEFT JOIN `" . RefbookMetric::model()->tableName() . "` `t4` ON `t4`.`metric_id` = `t3`.`metric_id`\n WHERE `t1`.`station_id` = '" . $form->station_id . "' AND `t1`.`station_sensor_id` IN (" . implode(',', $form->sensor_id) . ")"; $res = Yii::app()->db->createCommand($sql)->queryAll(); if ($res) { foreach ($res as $key => $value) { if (!isset($sensors[$value['station_sensor_id']])) { $sensors[$value['station_sensor_id']] = array('station_sensor_id' => $value['station_sensor_id'], 'sensor_id_code' => $value['sensor_id_code'], 'handler_id_code' => $value['handler_id_code']); } $sensors[$value['station_sensor_id']]['features'][$value['feature_code']] = $value['metric_code']; } } } $i = $form->start_timestamp; while ($i <= $form->end_timestamp) { $messages[$i]['timestamp'] = $i; $i = $i + $form->interval * 60; } foreach ($messages as $key => $value) { if ($form->choosed_station['station_type'] === 'rain') { $messages[$key]['parts'][] = 'D'; $messages[$key]['parts'][] = $form->choosed_station['station_id_code']; $messages[$key]['parts'][] = date('ymd', $key); $messages[$key]['parts'][] = date('Hi', $key); $messages[$key]['parts'][] = str_pad(rand(100, 135), 3, "0", STR_PAD_LEFT); $messages[$key]['parts'][] = '00'; } else { $messages[$key]['parts'][] = 'D'; $messages[$key]['parts'][] = $form->choosed_station['station_id_code']; $messages[$key]['parts'][] = date('ymd', $key); $messages[$key]['parts'][] = date('Hi', $key); $messages[$key]['parts'][] = '00'; } $sensors_values = array(); if ($sensors) { foreach ($sensors as $k1 => $v1) { $handler = SensorHandler::create($v1['handler_id_code']); $random_value = $handler->getRandomValue($v1['features']); $sensors_values[] = $v1['sensor_id_code'] . $random_value; } shuffle($sensors_values); foreach ($sensors_values as $k1 => $v1) { $messages[$key]['parts'][] = $v1; } } $crc = It::prepareCRC(implode('', $messages[$key]['parts'])); $messages[$key]['parts'][] = $crc; array_push($messages[$key]['parts'], '$'); array_unshift($messages[$key]['parts'], '@'); } } } $messages_display = array(); $messages_copy = array(); foreach ($messages as $key => $value) { $messages_display[] = implode(' ', $value['parts']); $messages_copy[] = implode('', $value['parts']); } $station_sensors = StationSensor::getList($form->station_id); if ($station_sensors) { foreach ($station_sensors as $key => $value) { $station_sensors[$key]['checked'] = 1; } } if (isset($_POST['GenerateMessageForm']['sensor_id'])) { if ($station_sensors) { foreach ($station_sensors as $key => $value) { $station_sensors[$key]['checked'] = in_array($value['station_sensor_id'], $_POST['GenerateMessageForm']['sensor_id']) ? 1 : 0; } } } $this->render('msg_generation', array('form' => $form, 'messages_display' => $messages_display, 'messages_copy' => $messages_copy, 'station_sensors' => $station_sensors)); }
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); }