public function saveDataPairs($params)
{
if ($this->prepared_pairs) {
$bucket_size = $this->_findFeatureConstantValue('bucket_size', $params['sensor_features']);
$total = count($this->prepared_pairs);
for ($i = 0; $i < $total; $i++) {
$pair = $this->prepared_pairs[$i];
$measuring_timestamp = date('Y-m-d H:i:s', isset($pair['measuring_timestamp']) ? $pair['measuring_timestamp'] : $this->incoming_measuring_timestamp);
$criteria = new CDbCriteria();
$criteria->compare('DATE_FORMAT(measuring_timestamp, "%Y-%m-%d %H:%i:%s")', $measuring_timestamp);
$criteria->compare('sensor_id', $params['sensor']->station_sensor_id);
$sensor_data = SensorDataMinute::model()->find($criteria);
if (!$sensor_data || $sensor_data->is_tmp || $params['rewrite_prev_values']) {
if (!$sensor_data) {
$sensor_data = new SensorDataMinute();
}
$sensor_data->sensor_id = $params['sensor']->station_sensor_id;
$sensor_data->station_id = $params['sensor']->station_id;
$sensor_data->sensor_value = $pair['value'];
$sensor_data->metric_id = $pair['metric_id'];
$sensor_data->sensor_feature_normalized_value = $pair['normilized_value'];
$sensor_data->bucket_size = $bucket_size;
$sensor_data->listener_log_id = $params['listener_log_id'];
$sensor_data->measuring_timestamp = $measuring_timestamp;
$sensor_data->battery_voltage = $params['battery_voltage'];
$sensor_data->is_tmp = 0;
$sensor_data->save();
}
}
}
}
public function saveDataPairs($params)
{
if ($this->prepared_pairs) {
foreach ($params['sensor_features'] as $key => $value) {
if ($value['feature_code'] == 'bucket_size') {
$bucket_size = $value['feature_constant_value'];
}
}
foreach ($this->prepared_pairs as $key => $pair) {
$measuring_timestamp = date('Y-m-d H:i:s', isset($pair['measuring_timestamp']) ? $pair['measuring_timestamp'] : $this->incoming_measuring_timestamp);
$criteria = new CDbCriteria();
$criteria->compare('DATE_FORMAT(measuring_timestamp, "%Y-%m-%d %H:%i:%s")', $measuring_timestamp);
$criteria->compare('sensor_id', $params['sensor']->station_sensor_id);
$sensor_data = SensorDataMinute::model()->find($criteria);
if (!$sensor_data || $sensor_data->is_tmp || $params['rewrite_prev_values']) {
if (!$sensor_data) {
$sensor_data = new SensorDataMinute();
}
$sensor_data->sensor_id = $params['sensor']->station_sensor_id;
$sensor_data->station_id = $params['sensor']->station_id;
$sensor_data->sensor_value = $pair['value'];
$sensor_data->metric_id = $pair['metric_id'];
$sensor_data->sensor_feature_normalized_value = $pair['normilized_value'];
$sensor_data->bucket_size = $bucket_size;
$sensor_data->listener_log_id = $params['listener_log_id'];
$sensor_data->measuring_timestamp = $measuring_timestamp;
$sensor_data->battery_voltage = $params['battery_voltage'];
$sensor_data->is_tmp = 0;
$sensor_data->save();
}
}
}
}
private function logRainMessage()
{
$parts = str_getcsv($this->message_obj->message);
$date = explode('/', $parts[0]);
$time = explode(':', $parts[1]);
$measuring_timestamp = mktime($time[0], $time[1], 0, $date[1], $date[2], '20' . $date[0]);
$criteria = new CDbCriteria();
$criteria->condition = "DATE_FORMAT(measuring_timestamp, '%Y-%m-%d %H:%i:%s') = :measuring_timestamp AND sensor_id = :sensor_id";
$criteria->params = array(':measuring_timestamp' => date('Y-m-d H:i:s', $measuring_timestamp), ':sensor_id' => $this->_sensor->station_sensor_id);
$sensor_data = SensorDataMinute::model()->find($criteria);
if (!$sensor_data || $sensor_data->is_tmp || $this->message_obj->rewrite_prev_values) {
if (!$sensor_data) {
$sensor_data = new SensorDataMinute();
$sensor_data->sensor_id = $this->_sensor->station_sensor_id;
$sensor_data->station_id = $this->message_obj->station_id;
}
$sensor_data->sensor_value = $parts[3];
$sensor_data->bucket_size = $this->_sensor->bucket_size;
$sensor_data->listener_log_id = $this->message_obj->log_id;
$sensor_data->measuring_timestamp = date('Y-m-d H:i:s', $measuring_timestamp);
$sensor_data->battery_voltage = $parts[2];
$sensor_data->is_tmp = 0;
$sensor_data->save();
}
}
public function prepareList($total_ticks = 60)
{
if ($this->station_id) {
$first_date = strtotime($this->date_from . ' ' . $this->time_from);
$last_date = strtotime($this->date_to . ' ' . $this->time_to);
$series_data = array();
$sql_where = array();
$series_stations = array($this->station_id);
$sql_where[] = "`sd`.`station_id` IN (" . implode(',', $series_stations) . ") ";
$sql_where[] = "`sd`.`measuring_timestamp` >= '" . date('Y-m-d H:i:s', $first_date) . "'";
$sql_where[] = "`sd`.`measuring_timestamp` <= '" . date('Y-m-d H:i:s', $last_date) . "'";
$sql = "SELECT `sd`.`station_id`,\n `sd`.`sensor_id`,\n `sd`.`measuring_timestamp`,\n `sd`.`sensor_value`,\n `sd`.`bucket_size`\n FROM `" . SensorDataMinute::model()->tableName() . "` `sd`\n WHERE " . implode(' AND ', $sql_where) . "\n ORDER BY `sd`.`measuring_timestamp` ASC ";
$res = CStubActiveRecord::getDbConnect(true)->createCommand($sql)->queryAll();
if ($res) {
$total_found = count($res);
for ($i = 0; $i < $total_found; $i++) {
$time = strtotime($res[$i]['measuring_timestamp']) * 1000;
$rain_value = $res[$i]['sensor_value'] * $res[$i]['bucket_size'];
$series_data[0][] = array($time, $rain_value);
}
}
$series_names = array();
$series_names[0] = $this->stations[$this->station_id];
}
return array('series_names' => $series_names, 'series_data' => $series_data, 'total_ticks' => count($series_data[0]), 'min_tick' => date('Y-m-d H:i', $first_date), 'max_tick' => date('Y-m-d H:i', $last_date));
}
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);
}
public function actionRgPanel()
{
$stations = Station::getList('rain');
$handlers = array();
SensorDBHandler::handlerWithFeature($handlers, 'rg');
$features = array_shift($handlers)->features;
if ($stations) {
foreach ($stations as $key => &$station) {
$station['sensor_details'] = array('last_msg' => '-', 'amount' => '-', 'period' => '-', 'rate' => '-', '1hr_total' => '-', 'batt_volt' => '-', '24hr_total' => '-');
$period = $station['event_message_period'];
if ($period == 5) {
$use_field = '5min_sum';
} else {
if ($period == 10) {
$use_field = '10min_sum';
} else {
if ($period == 20) {
$use_field = '20min_sum';
} else {
if ($period == 30) {
$use_field = '30min_sum';
} else {
$use_field = '60min_sum';
}
}
}
}
$station['filter_limit_max'] = round($features['rain']->filter_max / 60 * $period, 2);
$station['filter_limit_min'] = round($features['rain']->filter_min / 60 * $period, 2);
$station['filter_limit_diff'] = round($features['rain']->filter_diff / 60 * $period, 2);
$sql_groupped_table = "SELECT `station_id`, MAX(`measuring_timestamp`) AS `MaxDateTime`\n FROM `" . SensorDataMinute::model()->tableName() . "`\n WHERE `{$use_field}` > 0 AND `station_id` = '" . $station['station_id'] . "' AND `is_tmp` = 0";
$sql = "SELECT `tt`.*, `t2`.`html_code` AS `metric_html_code`\n FROM `" . SensorDataMinute::model()->tableName() . "` `tt`\n INNER JOIN `" . RefbookMetric::model()->tableName() . "` t2 ON t2.metric_id = tt.metric_id\n INNER JOIN ( {$sql_groupped_table} ) `groupedtt` ON `tt`.`station_id` = `groupedtt`.`station_id` AND `tt`.`measuring_timestamp` = `groupedtt`.`MaxDateTime`";
$res = Yii::app()->db->createCommand($sql)->queryRow();
$last_logs = ListenerLog::getLast2Messages($station['station_id']);
if ($res) {
$station['sensor_details'] = array('sensor_data_id' => $res['sensor_data_id'], 'last_msg' => date('Y-m-d H:i', strtotime($res['measuring_timestamp'])), 'amount' => $res[$use_field] * $res['bucket_size'], 'period' => $period, 'rate' => $res[$use_field] * (60 / $period) * $res['bucket_size'], '1hr_total' => $res['60min_sum'] * $res['bucket_size'], 'batt_volt' => $res['battery_voltage'] / 10, '24hr_total' => $res['1day_sum'] * $res['bucket_size'], 'metric' => $res['metric_html_code']);
if (count($last_logs) > 0) {
$station['last_tx'] = date('m/d/Y H:i', strtotime($last_logs[0]['measuring_timestamp']));
$next_expected = strtotime($last_logs[0]['measuring_timestamp']) + $period + 300;
$station['sensor_details']['next_expected'] = date('m/d/Y H:i', $next_expected);
if ($next_expected < time()) {
$station['sensor_details']['next_lates'] = 1;
}
}
if ($station['filter_limit_max'] > 0) {
if ($station['sensor_details']['amount'] >= $station['filter_limit_max']) {
$station['filter_errors'][] = "R >= " . $station['filter_limit_max'];
}
}
if ($station['filter_limit_min'] > 0) {
if ($station['sensor_details']['amount'] <= $station['filter_limit_min']) {
$station['filter_errors'][] = "R <= " . $station['filter_limit_min'];
}
}
}
}
}
$template = 'index';
$render_data = array('stations' => $stations);
if (Yii::app()->request->isAjaxRequest) {
$this->renderPartial($template, array('render_data' => $render_data));
} else {
$this->render('autorefresh', array('render_data' => $render_data, 'template' => $template));
}
}
public function actionSetup()
{
$criteria = new CDbCriteria();
$criteria->condition = "ord > 0";
$criteria->order = "ord ASC";
$meas_types = RefbookMeasurementType::model()->findAll($criteria);
if ($meas_types) {
foreach ($meas_types as $key => $value) {
$sql = "SELECT `t1`.`metric_id`, CONCAT(`t2`.`html_code`, ' (', `t2`.`full_name`, ')') AS `name`, `t1`.`is_main`, `t1`.`measurement_type_metric_id`\n FROM `" . RefbookMeasurementTypeMetric::model()->tableName() . "` `t1`\n LEFT JOIN `" . RefbookMetric::model()->tableName() . "` `t2` ON `t2`.`metric_id` = `t1`.`metric_id`\n WHERE `t1`.`measurement_type_id` = '" . $value->measurement_type_id . "'";
$meas_types[$key]->metrics_list = Yii::app()->db->createCommand($sql)->queryAll();
}
}
if (Yii::app()->request->isPostRequest && isset($_POST['main_metric'])) {
foreach ($_POST['main_metric'] as $key => $value) {
if ($meas_types[$key]->metrics_list) {
foreach ($meas_types[$key]->metrics_list as $v1) {
$update = array('is_main' => $v1['metric_id'] == $value ? 1 : 0);
RefbookMeasurementTypeMetric::model()->updateByPk($v1['measurement_type_metric_id'], $update);
}
}
}
StationSensorFeature::updateMetric();
$DB = array('db' => CStubActiveRecord::getDbConnect(), 'db_long' => CStubActiveRecord::getDbConnect(true));
foreach ($DB as $db) {
$db->createCommand("DELETE FROM `" . ScheduleReportProcessed::model()->tableName() . "`")->query();
$db->createCommand("DELETE FROM `" . ForwardedMessage::model()->tableName() . "`")->query();
$db->createCommand("DELETE FROM `" . StationCalculationData::model()->tableName() . "`")->query();
$db->createCommand("DELETE FROM `" . SeaLevelTrend::model()->tableName() . "`")->query();
$db->createCommand("DELETE FROM `" . SensorDataMinute::model()->tableName() . "`")->query();
$db->createCommand("DELETE FROM `" . SensorData::model()->tableName() . "`")->query();
$db->createCommand("DELETE FROM `" . ListenerLog::model()->tableName() . "`")->query();
}
It::memStatus('admin_metrics_saved');
$this->redirect($this->createUrl('admin/setup'));
}
$this->render('setup', array('meas_types' => $meas_types));
}
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');
}
private function prepareListenerLogDependantInserts($listener_log_ids)
{
$tables = array(ListenerLogProcessError::model()->tableName() => 'log_id', SensorData::model()->tableName() => 'listener_log_id', SensorDataMinute::model()->tableName() => 'listener_log_id', SeaLevelTrend::model()->tableName() => 'log_id', StationCalculationData::model()->tableName() => 'listener_log_id');
$result_sql = array();
foreach ($tables as $table => $log_id_field) {
$sql = "SELECT * \n FROM `" . $table . "`\n WHERE `" . $log_id_field . "` IN (" . implode(',', $listener_log_ids) . ")";
$res = Yii::app()->db->createCommand($sql)->queryAll();
$total = count($res);
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;
$cnt = 0;
foreach ($res as $key => $value) {
$res_sql .= "('" . implode("','", $value) . "')";
if ($key + 1 < $total) {
$res_sql .= ", ";
}
$cnt++;
}
$result_sql[] = $res_sql;
$this->addBackupLog("Prepared " . $cnt . " inserts for " . $table);
}
}
return $result_sql;
}
public function processSums($meas_datetime, $sensor_id, $station_id, $listener_log_id, $bucket_size, $battery_voltage)
{
//$meas_datetime = $date.' '.$time;
$meas_timestamp = strtotime($meas_datetime);
$meas_hour = date('H', $meas_timestamp);
$meas_min = date('i', $meas_timestamp);
$meas_month = date('m', $meas_timestamp);
$meas_day = date('d', $meas_timestamp);
$meas_year = date('Y', $meas_timestamp);
$minute = date('i', $meas_timestamp);
$tmp = array('00', '05', '10', '15', '20', '25', '30', '35', '40', '45', '50', '55');
foreach ($tmp as $key => $value) {
$tmp_time = $meas_year . '-' . $meas_month . '-' . $meas_day . ' ' . $meas_hour . ':' . $value;
$sql = "SELECT `sensor_data_id` FROM `" . SensorDataMinute::model()->tableName() . "` WHERE DATE_FORMAT(`measuring_timestamp`, '%Y-%m-%d %H:%i') = '" . $tmp_time . "' AND sensor_id = '" . $sensor_id . "'";
$res = Yii::app()->db->createCommand($sql)->queryScalar();
if (!$res) {
$sql = "INSERT INTO `" . SensorDataMinute::model()->tableName() . "` (`sensor_id`, `station_id`, `listener_log_id`, `bucket_size`, `measuring_timestamp`, `is_tmp`, `battery_voltage`) VALUES ('" . $sensor_id . "', '" . $station_id . "', '" . $listener_log_id . "', '" . $bucket_size . "', '" . $tmp_time . "', '1', '" . $battery_voltage . "') ";
$res = Yii::app()->db->createCommand($sql)->query();
}
}
$tmp_time = $meas_year . '-' . $meas_month . '-' . $meas_day . ' 00:00';
$sql = "SELECT `sensor_data_id` FROM `" . SensorDataMinute::model()->tableName() . "` WHERE DATE_FORMAT(`measuring_timestamp`, '%Y-%m-%d %H:%i') = '" . $tmp_time . "' AND sensor_id = '" . $sensor_id . "'";
$res = Yii::app()->db->createCommand($sql)->queryScalar();
if (!$res) {
$sql = "INSERT INTO `" . SensorDataMinute::model()->tableName() . "` (`sensor_id`, `station_id`, `listener_log_id`, `bucket_size`, `measuring_timestamp`, `is_tmp`, `battery_voltage`) VALUES ('" . $sensor_id . "', '" . $station_id . "', '" . $listener_log_id . "', '" . $bucket_size . "', '" . $tmp_time . "', '1', '" . $battery_voltage . "') ";
$res = Yii::app()->db->createCommand($sql)->query();
}
$tmp_time = date('Y-m-d H:i', mktime(0, 0, 0, $meas_month, $meas_day + 1, $meas_year));
$sql = "SELECT `sensor_data_id` FROM `" . SensorDataMinute::model()->tableName() . "` WHERE DATE_FORMAT(`measuring_timestamp`, '%Y-%m-%d %H:%i') = '" . $tmp_time . "' AND sensor_id = '" . $sensor_id . "'";
$res = Yii::app()->db->createCommand($sql)->queryScalar();
if (!$res) {
$sql = "INSERT INTO `" . SensorDataMinute::model()->tableName() . "` (`sensor_id`, `station_id`, `listener_log_id`, `bucket_size`, `measuring_timestamp`, `is_tmp`, `battery_voltage`) VALUES ('" . $sensor_id . "', '" . $station_id . "', '" . $listener_log_id . "', '" . $bucket_size . "', '" . $tmp_time . "', '1', '" . $battery_voltage . "') ";
$res = Yii::app()->db->createCommand($sql)->query();
}
$to_update = array();
//-----------------------------------------------------
$start = floor($minute / 5) * 5;
$shift = $start == $minute ? 5 : 0;
$to_update['5min_sum'] = array(date('Y-m-d H:i:s', mktime($meas_hour, $start - $shift + 1, 0, $meas_month, $meas_day, $meas_year)), date('Y-m-d H:i:s', mktime($meas_hour, $start - $shift + 5, 0, $meas_month, $meas_day, $meas_year)));
//-----------------------------------------------------
$start = floor($minute / 10) * 10;
$shift = $start == $minute ? 10 : 0;
$to_update['10min_sum'] = array(date('Y-m-d H:i:s', mktime($meas_hour, $start - $shift + 1, 0, $meas_month, $meas_day, $meas_year)), date('Y-m-d H:i:s', mktime($meas_hour, $start - $shift + 10, 0, $meas_month, $meas_day, $meas_year)));
//-----------------------------------------------------
$start = floor($minute / 20) * 20;
$shift = $start == $minute ? 20 : 0;
$to_update['20min_sum'] = array(date('Y-m-d H:i:s', mktime($meas_hour, $start - $shift + 1, 0, $meas_month, $meas_day, $meas_year)), date('Y-m-d H:i:s', mktime($meas_hour, $start - $shift + 20, 0, $meas_month, $meas_day, $meas_year)));
//-----------------------------------------------------
$start = floor($minute / 30) * 30;
$shift = $start == $minute ? 30 : 0;
$to_update['30min_sum'] = array(date('Y-m-d H:i:s', mktime($meas_hour, $start - $shift + 1, 0, $meas_month, $meas_day, $meas_year)), date('Y-m-d H:i:s', mktime($meas_hour, $start - $shift + 30, 0, $meas_month, $meas_day, $meas_year)));
//-----------------------------------------------------
$start = floor($minute / 60) * 60;
$shift = $start == $minute ? 60 : 0;
$to_update['60min_sum'] = array(date('Y-m-d H:i:s', mktime($meas_hour, $start - $shift + 1, 0, $meas_month, $meas_day, $meas_year)), date('Y-m-d H:i:s', mktime($meas_hour, $start - $shift + 60, 0, $meas_month, $meas_day, $meas_year)));
$to_update['1day_sum'] = array(date('Y-m-d H:i:s', mktime(0, 0, 1, $meas_month, $meas_day, $meas_year)), date('Y-m-d H:i:s', mktime(0, 0, 0, $meas_month, $meas_day + 1, $meas_year)));
//-----------------------------------------------------
foreach ($to_update as $field_name => $rangs) {
$sql = "UPDATE `" . SensorDataMinute::model()->tableName() . "` `t1` SET `t1`.`" . $field_name . "` = (\n SELECT SUM(`t2`.`sensor_value`)\n FROM (\n SELECT *\n FROM `" . SensorDataMinute::model()->tableName() . "` `t3`\n WHERE `t3`.`sensor_id` = '" . $sensor_id . "' AND `t3`.`measuring_timestamp` >= '" . $rangs[0] . "' AND `t3`.`measuring_timestamp` <= '" . $rangs[1] . "'\n ) `t2`\n WHERE `t2`.`measuring_timestamp` <= `t1`.`measuring_timestamp`\n )\n WHERE t1.sensor_id = '" . $sensor_id . "' AND `t1`.`measuring_timestamp` >= '" . $meas_datetime . "' AND `t1`.`measuring_timestamp` <= '" . $rangs[1] . "' ";
Yii::app()->db->createCommand($sql)->query();
}
}
