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');
}
protected function addCalculations($file_config)
{
$station_id = $file_config['station']['station_id'];
$this->stations[$station_id];
foreach ($file_config['station_calculation'] as $calc) {
$calc_main_handler_db = CalculationDBHandler::model()->findByAttributes(array('handler_id_code' => $calc['handler']));
$calculation_db = new StationCalculation();
$calculation_db->station_id = $station_id;
$calculation_db->handler_id = $calc_main_handler_db->handler_id;
$calculation_db->formula = $calc['formula'] ? $calc['formula'] : 'default';
$calculation_db->save(false);
foreach ($this->sensors_for_calcs[$calc['handler']][$station_id] as $sensor) {
$calc_variables = new StationCalculationVariable();
$station_sensor_features = $sensor->features();
foreach ($station_sensor_features as $s_feature) {
if (is_object($s_feature)) {
if ($s_feature->is_main) {
$calc_variables->sensor_feature_id = $s_feature->sensor_feature_id;
$calc_variables->variable_name = $s_feature->feature_code;
$calc_variables->calculation_id = $calculation_db->getPrimaryKey();
$calc_variables->save(false);
}
}
}
}
}
}
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;
}
