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(); } }