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 getUsedSensors($station_id) { $sql = "SELECT t5.sensor_id_code\n FROM `" . StationCalculationVariable::model()->tableName() . "` `t1`\n LEFT JOIN `" . StationCalculation::model()->tableName() . "` t2 ON t2.calculation_id = t1.calculation_id\n LEFT JOIN `" . CalculationDBHandler::model()->tableName() . "` t3 ON t3.handler_id = t2.handler_id\n LEFT JOIN `" . StationSensorFeature::model()->tableName() . "` t4 ON t4.sensor_feature_id = t1.sensor_feature_id\n LEFT JOIN `" . StationSensor::model()->tableName() . "` t5 ON t5.station_sensor_id = t4.sensor_id\n WHERE `t3`.handler_id_code = ? AND t2.station_id = ? \n ORDER BY t5.sensor_id_code"; $res = Yii::app()->db->createCommand($sql)->queryColumn(array($this->handler_id_code, $station_id)); return $res; }
public function actionCalculationSave() { $station_id = intval($_REQUEST['station_id']); if (!$station_id) { $this->redirect($this->createUrl('admin/Stations')); } $handler_id = intval($_REQUEST['handler_id']); if (!$handler_id) { $this->redirect($this->createUrl('admin/Sensors', array('station_id' => $station_id))); } $station = Station::model()->findByPk($station_id); $handler_db = CalculationDBHandler::model()->findByPk($handler_id); if (!$station || !$handler_db) { $this->redirect($this->createUrl('admin/Sensors', array('station_id' => $station_id))); } $handler = CalculationHandler::create($handler_db->handler_id_code); $measurements = $handler->getMeasurements(); $formulas = $handler->getFormulas(); $calculation_db = StationCalculation::model()->find('handler_id = :handler_id AND station_id = :station_id', array(':handler_id' => $handler_db->handler_id, ':station_id' => $station->station_id)); if (!$calculation_db) { $calculation_db = new StationCalculation(); $calculation_db->station_id = $station->station_id; $calculation_db->handler_id = $handler_db->handler_id; $calculation_db->formula = $formulas ? $formulas[0] : 'default'; } if ($calculation_db->calculation_id) { foreach ($measurements as $key => $value) { $measurements[$key]['object'] = StationCalculationVariable::model()->find('calculation_id = :calculation_id AND variable_name = :variable_name', array(':calculation_id' => $calculation_db->calculation_id, ':variable_name' => $value['variable_name'])); } } foreach ($measurements as $key => $value) { if (!$measurements[$key]['object']->calculation_variable_id) { $measurements[$key]['object'] = new StationCalculationVariable(); $measurements[$key]['object']->variable_name = $value['variable_name']; } } if ($measurements) { $sql = "SELECT t2.sensor_id_code, t1.sensor_feature_id\n FROM `" . StationSensorFeature::model()->tableName() . "` t1\n LEFT JOIN `" . StationSensor::model()->tableName() . "` t2 ON t2.station_sensor_id = t1.sensor_id\n WHERE t2.station_id = ? AND t1.feature_code = ?\n ORDER BY t2.sensor_id_code"; foreach ($measurements as $key => $value) { $measurements[$key]['sensors'] = Yii::app()->db->createCommand($sql)->queryAll(true, array($station->station_id, $value['variable_name'])); } } $formulas_act = array(); if ($formulas) { foreach ($formulas as $key => $value) { $formulas_act[$value] = $value; } } $validated = true; if (Yii::app()->request->isPostRequest) { $calculation_db->attributes = $_POST['StationCalculation']; foreach ($measurements as $key => $value) { $measurements[$key]['object']->sensor_feature_id = $_POST['StationCalculationVariable'][$key]['sensor_feature_id']; if ($measurements[$key]['required'] == 1 && !$measurements[$key]['object']->sensor_feature_id) { $measurements[$key]['object']->addError('sensor_feature_id', $measurements[$key]['display_name'] . ' is required.'); $validated = false; } if ($measurements[$key]['required'] == 0 && $_POST['StationCalculationVariable'][$key]['sensor_feature_id'] == 0) { if (is_object($measurements[$key]['object']) && !$measurements[$key]['object']->isNewRecord) { $measurements[$key]['object']->delete(); } unset($measurements[$key]); } } $validated = $validated & $calculation_db->validate(); if ($validated) { $calculation_db->save(); foreach ($measurements as $key => $value) { $measurements[$key]['object']->calculation_id = $calculation_db->calculation_id; $measurements[$key]['object']->save(); } It::memStatus('admin_station_calculation_saved'); $this->redirect($this->createUrl('admin/sensors', array('station_id' => $station->station_id))); } } $this->render('station_calculation', array('station' => $station, 'handler_db' => $handler_db, 'measurements' => $measurements, 'formulas' => $formulas_act, 'calculation_db' => $calculation_db)); }
/** * 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; }