/** * List sensor feature are grouped * * @return array|null $this->group_sensor_features */ public function getGroupSensorsFeaturesList() { if (!$this->group_sensor_features) { $handlers = SensorDBHandler::getHandlers('aws'); $rs_data = array(); if ($handlers) { $measurement_codes = array(); foreach ($handlers as $handler) { $sensor_features = SensorHandler::create($handler->handler_id_code)->getAwsGraphFeatures(); foreach ($sensor_features as $v) { if ($v['measurement_type_code']) { $measurement_codes[] = $v['measurement_type_code']; } } // get stations $stations = array(); $station_ids = array(); $cssClass = ""; if (count($handler->sensors)) { foreach ($handler->sensors as $sensor) { $station = array(); $station['station_id_code'] = $sensor->station->station_id_code; $station['station_id'] = $sensor->station->station_id; $station['color'] = $sensor->station->color; $stations[$station['station_id']] = $station; $station_ids[$station['station_id']] = $station['station_id']; } $stations = array_values($stations); $station_ids = array_values($station_ids); $cssClass = implode('-station ', $station_ids); $cssClass .= '-station '; array_multisort($stations, SORT_STRING, $stations); } $this->group_sensor_features[$handler->handler_id_code] = ['name' => $handler->display_name, 'sensor_features' => $sensor_features, 'stations' => $stations, 'class' => $cssClass]; } // Calculates $this->group_sensor_features['Temperature']['sensor_features']['calc_1'] = ['feature_name' => 'Dew Point, C degree']; $this->group_sensor_features['Pressure']['sensor_features']['calc_2'] = ['feature_name' => 'Pressure MSL, hPa']; // Measurement $sql = "SELECT `t3`.code, `t5`.`short_name`, `t5`.`full_name`\n FROM `" . RefbookMeasurementType::model()->tableName() . "` `t3`\n LEFT JOIN `" . RefbookMeasurementTypeMetric::model()->tableName() . "` `t4` ON `t4`.`measurement_type_id` = `t3`.`measurement_type_id` AND `t4`.`is_main` = 1\n LEFT JOIN `" . RefbookMetric::model()->tableName() . "` `t5` ON `t5`.`metric_id` = `t4`.`metric_id`\n WHERE `t3`.`code` IN ('" . implode("','", $measurement_codes) . "')"; $rs = CStubActiveRecord::getDbConnect(true)->createCommand($sql)->queryAll(); if ($rs) { $rs_data = CHtml::listData($rs, 'code', 'short_name'); } $wd_ws_station_params['stations'] = array(); $wd_ws_station_params['class'] = ''; foreach ($this->group_sensor_features as $handler_id_code => &$group) { if (in_array($handler_id_code, ['WindDirection', 'WindSpeed']) && in_array('WindSpeed', array_keys($this->group_sensor_features)) && in_array('WindDirection', array_keys($this->group_sensor_features))) { $station_ids_temp = array(); foreach ($wd_ws_station_params['stations'] as $wd_ws_station) { $station_ids_temp[] = $wd_ws_station['station_id']; } foreach ($group['stations'] as $station) { if (!in_array($station['station_id'], $station_ids_temp)) { $wd_ws_station_params['stations'][] = $station; } } $station_ids = array(); foreach ($wd_ws_station_params['stations'] as $wd_ws_station) { $station_ids[] = $wd_ws_station['station_id']; } $wd_ws_station_params['class'] = implode('-station ', $station_ids) . '-station '; } if (in_array($handler_id_code, ['TemperatureWater', 'TemperatureSoil'])) { foreach ($group['sensor_features'] as $key => $value) { $group['sensor_features'][$key] = $group['name'] . ($rs_data[$value['measurement_type_code']] ? ', ' . $rs_data[$value['measurement_type_code']] : ''); } } else { foreach ($group['sensor_features'] as $key => $value) { $group['sensor_features'][$key] = $value['feature_name'] . ($rs_data[$value['measurement_type_code']] ? ', ' . $rs_data[$value['measurement_type_code']] : ''); } } } } /** * Load custom sensor feature */ if (count($this->custom_sensor_features)) { $this->group_sensor_features['custom'] = ['name' => 'Custom', 'sensor_features' => $this->custom_sensor_features, 'stations' => $wd_ws_station_params['stations'], 'class' => $wd_ws_station_params['class']]; } } return (array) $this->group_sensor_features; }
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 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($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 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'); }
/** * create statistics now * @param null $report */ private function ini($report = null) { date_default_timezone_set("UTC"); if (is_null($report)) { $report = new HeartbeatReport(); $report->created = date("Y-m-d H:i:s"); $report->period = date("Y-m-d H:i:s", time() - 24 * 60 * 60); } $minute_in_period = (strtotime($report->created) - strtotime($report->period)) / 60; $databases = array('db' => false, 'db_long' => true); $config_file = array('db' => 'db_params', 'db_long' => 'db_long_params'); $sql = "SELECT TABLE_NAME, `DATA_LENGTH`+`INDEX_LENGTH` as 'TABLE_SIZE', TABLE_ROWS\r\n FROM information_schema.TABLES\r\n WHERE `table_schema` LIKE :db;" . ""; foreach ($databases as $database => $use_long) { /* * $db_stat * $db_long_stat */ $res = CStubActiveRecord::getDbConnect($use_long)->createCommand("SHOW STATUS;")->queryAll(); foreach ($res as $val) { $this->{$database . '_stat'}[$val['Variable_name']] = $val['Value']; } /* * $db_tables_size * $db_long_tables_size * $db_tables_rows * $db_long_tables_rows */ // $db_conf = new ConfigManager($config_file[$database]); // $db_name = $db_conf::getConfigSection('database', 'dbname'); $db_name = Yii::app()->params[$config_file[$database]]['dbname']; $res = CStubActiveRecord::getDbConnect($use_long)->createCommand($sql)->bindValue(':db', $db_name)->queryAll(); $allSize = $allCount = 0; foreach ($res as $table) { $allSize += $this->{$database . '_tables_size'}[$table['TABLE_NAME']] = number_format($table['TABLE_SIZE'] / 1024 / 1024, 2); $allCount += $this->{$database . '_tables_rows'}[$table['TABLE_NAME']] = $table['TABLE_ROWS']; } $this->{$database . '_tables_size'}['All'] = $allSize; $this->{$database . '_tables_rows'}['All'] = $allCount; if ($use_long) { /* * $stations * $stations_logger * $stations_communication_type * $stations_message_interval * $stations_message_count * $stations_message_expected * $stations_message_error * $stations_message_is_processing * $stations_message_last * $stations_schedule_synop * $stations_schedule_bufr * $stations_schedule_speci * $stations_schedule_metar * $stations_schedule_data_export */ $criteria = new CDbCriteria(); $criteria->index = 'station_id'; $criteria->with = array('messages' => array('together' => false, 'select' => 'messages.measuring_timestamp, messages.failed, is_processing', 'condition' => 'messages.measuring_timestamp > \'' . $report->period . '\'', 'order' => 'messages.measuring_timestamp DESC'), 'schedule' => array('select' => 'schedule.report_type, schedule.period', 'together' => false, 'with' => array('processed' => array('select' => 'processed.updated', 'together' => false, 'condition' => 'processed.updated > \'' . $report->period . '\'')))); $stations = Station::model()->long()->findAll($criteria); foreach ($stations as $station_id => $station) { $this->stations[$station_id] = $station['display_name']; $this->stations_logger[$station_id] = $station['logger_type']; $this->stations_communication_type[$station_id] = $station['communication_type']; $this->stations_message_interval[$station_id] = $station['event_message_period']; $this->stations_message_count[$station_id] = count($station->messages); $this->stations_message_expected[$station_id] = $minute_in_period / $station['event_message_period']; foreach ($station->messages as $message) { $this->stations_message_error[$station_id] += $message['failed']; $this->stations_message_is_processing[$station_id] += $message['is_processing']; } $this->stations_message_last[$station_id] = $station->messages[0]['measuring_timestamp']; $logIds[] = $station->messages[0]['log_id']; foreach ($station->schedule as $schedule) { $schedule_count = count($schedule->processed); if ($schedule_count) { if ($schedule->report_type == 'speci') { $this->stations_schedule_speci[$station_id]['gen'] += $schedule_count; } else { $this->{'stations_schedule_' . $schedule->report_type}[$station_id]['gen'] += $schedule_count; $this->{'stations_schedule_' . $schedule->report_type}[$station_id]['sch'] += number_format($minute_in_period / $schedule->period); } } } } foreach ($this as $key => $arr) { if (stripos($key, 'stations_schedule_') !== false && !is_null($arr)) { foreach ($arr as $station_id => $val) { if (!is_null($val['gen']) && !is_null($val['sch'])) { $this->{$key}[$station_id] = $val['sch'] . ' / ' . $val['gen'] . ' = ' . number_format($val['gen'] / $val['sch'] * 100) . '%'; } elseif (stripos($key, 'speci') === false) { $this->{$key}[$station_id] = $val['sch'] . ' / ' . $val['gen']; } else { $this->{$key}[$station_id] = $val['gen']; } } } } /* * $stations_bv_last */ $criteria = new CDbCriteria(); $criteria->with = array('sensor_feature' => array('select' => false, 'joinType' => 'INNER JOIN', 'condition' => 'sensor_feature.feature_code = \'battery_voltage\'')); $criteria->index = 'station_id'; $criteria->compare('listener_log_id', $logIds); $bv_data = SensorData::model()->long()->findAll($criteria); foreach ($stations as $station_id => $station) { $this->stations_sensor_bv[$station_id] = $bv_data[$station_id]['sensor_feature_value']; } } } /* * $system */ $this->system['disk_free_space'] = number_format(disk_free_space('/') / pow(10, 9), 2) . 'Gb'; $this->system['disk_total_space'] = number_format(disk_total_space('/') / pow(10, 9), 2) . 'Gb'; }
public function prepareList($page_size = 50) { $sql_where_str = $this->_prepareSqlCondition(); $sql_order_str = $this->_prepareSqlOrder(); if ($page_size) { $total = $this->total; } if ($total || !$page_size) { if ($page_size) { $pages = new CPagination($total); $pages->pageSize = $page_size; } $sql = "SELECT `t1`.`log_id`, `t1`.`updated`,`t1`.`measuring_timestamp`, `t1`.`created`, `t1`.`message`, `t1`.`fail_description`, t3.station_id_code, t3.display_name, t3.station_type, `t2`.`process_error_id`, `t1`.`is_processed`\n FROM `" . ListenerLog::model()->tableName() . "` t1\n LEFT JOIN `" . ListenerLogProcessError::model()->tableName() . "` `t2` ON `t2`.`log_id` = `t1`.`log_id`\n LEFT JOIN `" . Station::model()->tableName() . "` `t3` ON `t3`.`station_id` = `t1`.`station_id`\n {$sql_where_str}\n GROUP BY `t1`.`log_id`\n {$sql_order_str}"; if ($page_size) { $sql .= " LIMIT " . $pages->currentPage * $pages->pageSize . ", " . $pages->pageSize; } $res = CStubActiveRecord::getDbConnect(true)->createCommand($sql)->queryAll(); if ($res) { $ids = array(); foreach ($res as $key => $value) { $ids[] = $value['log_id']; if ($value['fail_description']) { $value['errors'] = explode(',', $value['fail_description']); } $list[$value['log_id']] = $value; } $sql = "SELECT * FROM `" . ListenerLogProcessError::model()->tableName() . "` WHERE `log_id` IN (" . implode(',', $ids) . ")"; $res2 = CStubActiveRecord::getDbConnect(true)->createCommand($sql)->queryAll(); if ($res2) { foreach ($res2 as $key => $value) { if ($value['type'] == 'error') { $list[$value['log_id']]['errors'][] = $value['description']; } else { if ($value['type'] == 'warning') { $list[$value['log_id']]['warnings'][] = $value['description']; } } } } } } return array('list' => $list, 'pages' => $pages); }