Пример #1
0
 public function beforeDelete()
 {
     $features = StationSensorFeature::model()->selectDb($this->getUseLong())->findAll('sensor_id = :sensor_id', array(':sensor_id' => $this->station_sensor_id));
     foreach ($features as $feature) {
         SensorData::model()->selectDb($this->getUseLong())->deleteAll('sensor_feature_id = :feature_id', array(':feature_id' => $feature->sensor_feature_id));
     }
     return parent::beforeDelete();
 }
Пример #2
0
 public function createExport()
 {
     $return_string = "";
     $sql = "SELECT\n                       `t1`.`measuring_timestamp` AS `TxDateTime`,\n                       `t4`.`station_id_code` AS `StationId`, \n                       `t4`.`display_name` AS `StationDisplayName`,\n                       `t3`.`sensor_id_code` AS `SensorId`, \n                       `t3`.`display_name` AS `SensorDisplayName`, \n                       \n                       `t1`.`period` AS `MeasurementPeriod`,\n                       `t1`.`sensor_feature_value` AS `Value`,\n                       `t5`.`short_name` AS `Metric`,\n                       `t8`.`value` AS `DewPoint`,\n                       `t11`.`value` AS `PressureMSL`,\n                       \n                       `t12`.`handler_id_code`,\n                       `t4`.`magnetic_north_offset`\n\n                 FROM `" . SensorData::model()->tableName() . "` t1\n                 LEFT JOIN `" . StationSensorFeature::model()->tableName() . "`          `t2`    ON `t2`.`sensor_feature_id` = `t1`.`sensor_feature_id`\n                 LEFT JOIN `" . StationSensor::model()->tableName() . "`                 `t3`    ON `t3`.`station_sensor_id` = `t2`.`sensor_id`\n                 LEFT JOIN `" . Station::model()->tableName() . "`                       `t4`    ON `t4`.`station_id` = `t1`.`station_id`\n                 LEFT JOIN `" . RefbookMetric::model()->tableName() . "`                 `t5`    ON `t5`.`metric_id` = `t2`.`metric_id`\n\n                 LEFT JOIN `" . StationCalculation::model()->tableName() . "`            `t6`    ON (`t6`.`station_id` = `t1`.`station_id` AND `t6`.`handler_id` = 1)\n                 LEFT JOIN `" . StationCalculationVariable::model()->tableName() . "`    `t7`    ON (`t7`.`sensor_feature_id` = `t2`.`sensor_feature_id` AND `t7`.`calculation_id` = `t6`.`calculation_id`)\n                 LEFT JOIN `" . StationCalculationData::model()->tableName() . "`        `t8`    ON (`t8`.`calculation_id` = `t7`.`calculation_id` AND `t8`.`listener_log_id` = `t1`.`listener_log_id`)\n\n                 LEFT JOIN `" . StationCalculation::model()->tableName() . "`            `t9`    ON (`t9`.`station_id` = `t1`.`station_id` AND `t9`.`handler_id` = 2)\n                 LEFT JOIN `" . StationCalculationVariable::model()->tableName() . "`    `t10`   ON (`t10`.`sensor_feature_id` = `t2`.`sensor_feature_id` AND `t10`.`calculation_id` = `t9`.`calculation_id`)\n                 LEFT JOIN `" . StationCalculationData::model()->tableName() . "`        `t11`   ON (`t11`.`calculation_id` = `t10`.`calculation_id` AND `t11`.`listener_log_id` = `t1`.`listener_log_id`)\n\n                 LEFT JOIN `" . SensorDBHandler::model()->tableName() . "`               `t12`   ON `t12`.`handler_id` = `t3`.`handler_id`\n\n                 WHERE `t1`.`station_id` IN (" . implode(',', $this->station_id) . ")\n                   AND `t1`.`measuring_timestamp` >= FROM_UNIXTIME(" . $this->start_timestamp . ") \n                   AND `t1`.`measuring_timestamp` <= FROM_UNIXTIME(" . $this->end_timestamp . ")\n                   AND `t2`.`is_main` = 1\n                   AND `t1`.`is_m` = '0'\n                 ORDER BY `t1`.`measuring_timestamp` DESC, `t4`.`station_id_code`, `t3`.`sensor_id_code`";
     $res = CStubActiveRecord::getDbConnect(true)->createCommand($sql)->queryAll();
     if ($res) {
         foreach ($res as $key => $value) {
             $handler_obj = SensorHandler::create($value['handler_id_code']);
             $res[$key]['Value'] = $handler_obj->applyOffset($res[$key]['Value'], $res[$key]['magnetic_north_offset']);
             $res[$key]['Value'] = $handler_obj->formatValue($res[$key]['Value'], $res[$key]['feature_code']);
             unset($res[$key]['magnetic_north_offset']);
             unset($value['handler_id_code']);
             unset($res[$key]['feature_code']);
         }
         $return_string .= "\"AWS Stations:\"\n" . It::prepareStringCSV($res);
     }
     $sql = "SELECT\n                        `t1`.`measuring_timestamp` AS `TxDateTime`,\n                        `t3`.`station_id_code` AS `StationId`, \n                        `t3`.`display_name` AS `StationDisplayName`,\n                        `t2`.`sensor_id_code` AS `SensorId`, \n                        `t2`.`display_name` AS `SensorDisplayName`,  \n                        (`t1`.`sensor_value` * `t1`.`bucket_size`) AS `Value`,\n                        `t4`.`short_name` AS `Metric`                        \n                 FROM `" . SensorDataMinute::model()->tableName() . "` t1\n                 LEFT JOIN `" . StationSensor::model()->tableName() . "` t2 ON t2.station_sensor_id = t1.sensor_id\n                 LEFT JOIN `" . Station::model()->tableName() . "` t3 ON t3.station_id = t1.station_id\n                 LEFT JOIN `" . RefbookMetric::model()->tableName() . "` t4 ON t4.metric_id = t1.metric_id\n                     \n                 WHERE `t1`.`station_id` IN (" . implode(',', $this->station_id) . ")\n                   AND `t1`.`measuring_timestamp` >= FROM_UNIXTIME(" . $this->start_timestamp . ") \n                   AND `t1`.`measuring_timestamp` <= FROM_UNIXTIME(" . $this->end_timestamp . ")\n                   AND `t1`.`is_tmp` = 0\n                 ORDER BY t1.measuring_timestamp DESC, t3.station_id_code, t2.sensor_id_code\n                ";
     $res = CStubActiveRecord::getDbConnect(true)->createCommand($sql)->queryAll();
     if ($res) {
         $return_string .= "\n\n\"RG Stations:\"\n" . It::prepareStringCSV($res);
     }
     It::downloadFile($return_string, 'export__' . date('Y-m-d_Hi') . '.csv', 'text/csv');
 }
 public function prepareFormulaParams()
 {
     $this->formula_params['h_station_above_sea'] = $this->station_obj->altitude;
     // h_barometer_above_station
     $sensor_id_codes = $this->getUsedSensors($this->station_obj->station_id);
     $sensor_id_code = array_shift(preg_grep('/^PR.$/', $sensor_id_codes));
     $qb = new CDbCriteria();
     $qb->with = ['sensor.handler', 'metric'];
     $qb->addCondition('sensor.sensor_id_code LIKE \'' . $sensor_id_code . '\'');
     $qb->addCondition('sensor.station_id = ' . $this->station_obj->station_id);
     $qb->addCondition('handler.handler_id_code LIKE \'Pressure\'');
     $qb->addCondition('t.feature_code LIKE \'height\'');
     $res = StationSensorFeature::model()->find($qb);
     $h_barometer_above_station = 0;
     if ($res && !is_null($res->feature_constant_value) && !is_null($res->metric->code)) {
         $h_barometer_above_station = It::convertMetric($res->feature_constant_value, $res->metric->code, 'meter');
     }
     $this->formula_params['h_barometer_above_station'] = $h_barometer_above_station;
     // coefficient_from_station_gravity
     $station_gravity = floatval($this->station_obj->station_gravity);
     $this->formula_params['coefficient_from_station_gravity'] = ($station_gravity > 0 ? $station_gravity : array_shift(array_keys(yii::app()->params['station_gravity']))) / 0.0065 / 287;
     return parent::prepareFormulaParams();
 }
 public function getMaxMinDayFromDayStart($sensor_id, $measuring_timestamp, $timezone_id)
 {
     $today_start = mktime(0, 0, 0, date('m', $measuring_timestamp), date('d', $measuring_timestamp), date('Y', $measuring_timestamp));
     $sql_groupped_table2 = "SELECT `t1`.`sensor_feature_id`, MAX(CAST(`t1`.`sensor_feature_value` AS DECIMAL(15,4))) AS `MaxVal`, MIN(CAST(`t1`.`sensor_feature_value` AS DECIMAL(15,4))) AS `MinVal` \n                               FROM `" . SensorData::model()->tableName() . "` `t1`\n                               LEFT JOIN " . StationSensorFeature::model()->tableName() . " `t2` ON `t2`.`sensor_feature_id` = `t1`.`sensor_feature_id`\n                               WHERE `t1`.`sensor_id` = '" . $sensor_id . "'\n                                   AND `t1`.`measuring_timestamp` <= '" . date('Y-m-d H:i:s', $measuring_timestamp) . "' \n                                   AND `t1`.`measuring_timestamp` >= '" . date('Y-m-d H:i:s', $today_start) . "'\n                                   AND `t2`.`feature_code` = 'sea_level_mean'";
     $sql = "SELECT CAST(`tt`.`sensor_feature_value` AS DECIMAL(15,4)) as `sensor_feature_value`, `tt`.`measuring_timestamp`\n                FROM `" . SensorData::model()->tableName() . "` `tt`\n\n                INNER JOIN ( {$sql_groupped_table2} ) `groupedtt` ON `tt`.`sensor_feature_id` = `groupedtt`.`sensor_feature_id` AND (`tt`.`sensor_feature_value` = `groupedtt`.`MaxVal` OR `tt`.`sensor_feature_value` = `groupedtt`.`MinVal`)\n                GROUP BY CAST(`tt`.`sensor_feature_value` AS DECIMAL(15,4))\n                ORDER BY CAST(`tt`.`sensor_feature_value` AS DECIMAL(15,4))\n                ";
     $res = Yii::app()->db->createCommand($sql)->queryAll();
     $return = array('sea_level_mean' => array('max24' => $this->formatValue($res[1]['sensor_feature_value'], 'sea_level_mean'), 'max24_time' => $res[1]['measuring_timestamp'], 'min24' => $this->formatValue($res[0]['sensor_feature_value'], 'sea_level_mean'), 'min24_time' => $res[0]['measuring_timestamp']));
     return $return;
 }
Пример #5
0
 public function getUsedSensors($station_id)
 {
     $sql = "SELECT t5.sensor_id_code\n                FROM `" . StationCalculationVariable::model()->tableName() . "` `t1`\n                LEFT JOIN `" . StationCalculation::model()->tableName() . "` t2 ON t2.calculation_id = t1.calculation_id\n                LEFT JOIN `" . CalculationDBHandler::model()->tableName() . "` t3 ON t3.handler_id = t2.handler_id\n                LEFT JOIN `" . StationSensorFeature::model()->tableName() . "` t4 ON t4.sensor_feature_id = t1.sensor_feature_id\n                LEFT JOIN `" . StationSensor::model()->tableName() . "` t5 ON t5.station_sensor_id = t4.sensor_id\n                WHERE `t3`.handler_id_code = ? AND t2.station_id = ? \n                ORDER BY t5.sensor_id_code";
     $res = Yii::app()->db->createCommand($sql)->queryColumn(array($this->handler_id_code, $station_id));
     return $res;
 }
Пример #6
0
 public static function getFullSensorList($station_ids, $handlersDefault)
 {
     $criteria = new CDbCriteria();
     $criteria->with = array('sensor.station', 'metric');
     $criteria->compare('station.station_id', $station_ids);
     $records = StationSensorFeature::model()->findAll($criteria);
     $result = array();
     foreach ($records as $record) {
         if (isset($handlersDefault)) {
             $record->default = $handlersDefault[$record->sensor->handler_id]->features[$record->feature_code];
         }
         $result[$record->feature_code][$record->sensor_id] = $record;
     }
     return $result;
 }
Пример #7
0
 public function getRainMetric()
 {
     if ($this->station_id) {
         $sql = "SELECT `t3`.`html_code`\n                    FROM `" . StationSensorFeature::model()->tableName() . "` `t1`\n                    LEFT JOIN `" . StationSensor::model()->tableName() . "` `t2` ON `t2`.`station_sensor_id` = `t1`.`sensor_id`\n                    LEFT JOIN `" . RefbookMetric::model()->tableName() . "` `t3` ON `t3`.`metric_id` = `t1`.`metric_id`\n                    WHERE `t2`.`station_id` = '" . $this->station_id . "' AND `t1`.`feature_code` = 'rain'\n                    ORDER BY `t2`.`sensor_id_code`";
         return CStubActiveRecord::getDbConnect(true)->createCommand($sql)->queryScalar();
     }
     return 'mm';
 }
Пример #8
0
 public function prepareSensorsInfo($listener_log_id)
 {
     $this->_logger->log(__METHOD__);
     $this->_logger->log(__METHOD__ . " listener_log_id " . $listener_log_id);
     $this->_logger->log(__METHOD__ . " this->schedule_process_info->ScheduleReportToStation->realStation->station_id " . $this->schedule_process_info->ScheduleReportToStation->realStation->station_id);
     $sensors = array();
     if (!is_null($this->schedule_process_info->ScheduleReportToStation->realStation->station_id)) {
         // get sensors
         $sql = "SELECT `t1`.`station_sensor_id`, `t1`.`sensor_id_code`, \r\n                           `t2`.`handler_id_code`, \r\n                           `t3`.`feature_code`, `t3`.`feature_constant_value`,\r\n                           `t3`.`sensor_feature_id`,\r\n                           `t4`.`code` AS `metric_code`, \r\n                           `t5`.`sensor_feature_value`, \r\n                           `t5`.`period` AS `sensor_feature_period`,\r\n                           `t6`.`code` AS `value_metric_code`,\r\n                           `t5`.`is_m`\r\n                    FROM `" . StationSensor::model()->tableName() . "`             `t1`\r\n                    LEFT JOIN `" . SensorDBHandler::model()->tableName() . "`      `t2` ON `t2`.`handler_id` = `t1`.`handler_id`\r\n                    LEFT JOIN `" . StationSensorFeature::model()->tableName() . "` `t3` ON (`t3`.`sensor_id` = `t1`.`station_sensor_id`)\r\n                    LEFT JOIN `" . RefbookMetric::model()->tableName() . "`        `t4` ON `t4`.`metric_id` = `t3`.`metric_id`\r\n                    LEFT JOIN `" . SensorData::model()->tableName() . "`           `t5` ON (`t5`.`sensor_feature_id` = `t3`.`sensor_feature_id` AND `t5`.`listener_log_id` = '" . $listener_log_id . "')\r\n                    LEFT JOIN `" . RefbookMetric::model()->tableName() . "`        `t6` ON `t6`.`metric_id` = `t5`.`metric_id`\r\n                    WHERE `t1`.`station_id` = '" . $this->schedule_process_info->ScheduleReportToStation->realStation->station_id . "'\r\n                    ORDER BY `t1`.`sensor_id_code` ASC";
         $sensors_info = Yii::app()->db->createCommand($sql)->queryAll();
         if (!$sensors_info) {
             $this->errors[] = 'Station has no sensors';
             return;
         }
         $sensors = array();
         foreach ($sensors_info as $value) {
             if ($value['feature_code'] === 'height' && $value['handler_id_code'] === 'Pressure') {
                 $sensors['pressure_height']['height'] = $value['feature_constant_value'];
                 $sensors['pressure_height']['height_metric_code'] = $value['metric_code'];
                 continue;
             }
             $value['metric_code'] = isset($value['value_metric_code']) ? $value['value_metric_code'] : $value['metric_code'];
             unset($value['value_metric_code']);
             if (!isset($sensors[$value['feature_code']])) {
                 $sensors[$value['feature_code']] = $value;
             }
         }
     }
     return $sensors;
 }
Пример #9
0
 /**
  * Prepare sensor list
  *
  * @param $sensor_feature_code
  * @param $handler_id_code
  *
  * @return array
  */
 private function prepareSensorList($sensor_feature_code, $handler_id_code)
 {
     date_default_timezone_set('UTC');
     $start_datetime = strtotime($this->date_from . ' ' . $this->time_from);
     $end_datetime = strtotime($this->date_to . ' ' . $this->time_to);
     $qb = new CDbCriteria();
     $qb->with = ['sensor.station' => ['select' => array('station.station_id_code', 'station.color'), 'condition' => 'station.station_id IN (' . implode(',', $this->station_id) . ')'], 'sensor.handler' => ['select' => false, 'condition' => "handler.handler_id_code LIKE '" . $handler_id_code . "'"]];
     $qb->select = ['t.feature_code', 't.feature_display_name'];
     $qb->addCondition("t.feature_code LIKE '{$sensor_feature_code}'");
     $features = StationSensorFeature::model()->long()->findAll($qb);
     if ($features) {
         $i = 0;
         foreach ($features as $feature) {
             $colorWorker = new Color($feature->sensor->station->color);
             $series_names[$i] = array();
             $series_names[$i]['name'] = $feature->sensor->station->station_id_code . ', ' . $feature->sensor->sensor_id_code . ', ' . $this->getGroupSensorsFeaturesList()[$handler_id_code]['sensor_features'][$feature->feature_code];
             $series_names[$i]['params']['color'] = '#' . $colorWorker->getHex();
             $i++;
             $colorWorker = new Color($feature->sensor->station->color);
         }
         $qb = new CDbCriteria();
         $qb->select = 't.sensor_feature_normalized_value, t.sensor_feature_value, t.measuring_timestamp';
         $qb->addCondition('t.sensor_feature_id = :sensor_feature_id');
         $qb->addBetweenCondition('t.measuring_timestamp', date('Y-m-d H:i:s', $start_datetime), date('Y-m-d H:i:s', $end_datetime));
         $qb->order = 't.measuring_timestamp ASC';
         foreach ($features as $feature) {
             $qb->params[':sensor_feature_id'] = $feature->sensor_feature_id;
             $found_data = SensorData::model()->long()->findAll($qb);
             $tmp = array();
             if ($this->accumulation_period != 0) {
                 foreach ($found_data as $data) {
                     $period = $start_datetime + (intval((strtotime($data->measuring_timestamp) - $start_datetime) / ($this->accumulation_period * 60)) + 1) * $this->accumulation_period * 60;
                     $period = $period > $end_datetime ? $end_datetime : $period;
                     $period *= 1000;
                     $tmp[$period] = ['x' => $period, 'y' => $tmp[$period] ? $tmp[$period]['y'] : 0 + floatval($data->sensor_feature_value)];
                 }
             } else {
                 foreach ($found_data as $data) {
                     $period = strtotime($data->measuring_timestamp) * 1000;
                     $tmp[$period] = ['x' => $period, 'y' => floatval($data->sensor_feature_value)];
                 }
             }
             $series_data[] = array_values($tmp);
         }
     }
     return ['series_names' => isset($series_names) ? $series_names : [], 'series_data' => isset($series_data) ? $series_data : []];
 }
Пример #10
0
 public function actionAwsFiltered()
 {
     $delete = isset($_REQUEST['delete']) ? intval($_REQUEST['delete']) : null;
     if ($delete) {
         $obj = SensorData::model()->findByPk($delete);
         if ($obj) {
             $obj->delete();
             It::memStatus('admin_suspicious_value_was_deleted');
         }
     }
     $session = new CHttpSession();
     $session->open();
     $sess_name = 'awsfiltered_filter1';
     $fparams = $session[$sess_name];
     $fparams['showdata'] = false;
     if ($fparams['redirect'] == true or isset($_GET['page'])) {
         $fparams['showdata'] = true;
     }
     $stations = Station::getList("'aws','awos'", false);
     $time_pattern = "/^(\\d{1,2}):(\\d{1,2})\$/";
     if (!$fparams || isset($_POST['clear']) || isset($_POST['filter'])) {
         $cur_time = time();
         $some_time_ago = mktime(0, 0, 0, date('m', $cur_time), date('d', $cur_time), date('Y', $cur_time));
         $fparams = array('station_id' => $stations[0]['station_id'], 'date_from' => date('m/d/Y', $some_time_ago), 'date_to' => date('m/d/Y', $cur_time), 'time_from' => '00:00', 'time_to' => '23:59', 'order_field' => 'date', 'order_direction' => 'DESC');
     }
     if (isset($_POST['filter'])) {
         $fparams['station_id'] = intval($_POST['search']['station_id']);
         $fparams['date_from'] = $_POST['search']['date_from'];
         $fparams['date_to'] = $_POST['search']['date_to'];
         if (preg_match($time_pattern, $_POST['search']['time_from'])) {
             $fparams['time_from'] = $_POST['search']['time_from'];
         }
         if (preg_match($time_pattern, $_POST['search']['time_to'])) {
             $fparams['time_to'] = $_POST['search']['time_to'];
         }
     }
     if (isset($_REQUEST['of']) && in_array($_REQUEST['of'], array('stationid', 'date', 'sensorid', 'value'))) {
         if ($_REQUEST['of'] == $fparams['order_field']) {
             $fparams['order_direction'] = $fparams['order_direction'] == 'ASC' ? 'DESC' : 'ASC';
         } else {
             $fparams['order_direction'] = 'ASC';
         }
         $fparams['order_field'] = $_REQUEST['of'];
     }
     $session[$sess_name] = $fparams;
     if ($_POST || $_REQUEST['of']) {
         $fparams['showdata'] = true;
         $fparams['redirect'] = true;
         $session[$sess_name] = $fparams;
         $this->redirect($this->createUrl('admin/awsfiltered') . (isset($_GET['page']) ? 'page/' . $_GET['page'] : ''));
     } else {
         $fparams['redirect'] = false;
         $session[$sess_name] = $fparams;
     }
     /*----------- filter prepare -------------*/
     $sql_where = array();
     $sql_where[] = "`t1`.`is_m` = '0'";
     if ($fparams['date_from']) {
         $sql_where[] = "`t1`.`measuring_timestamp` >= '" . date('Y-m-d H:i:s', strtotime($fparams['date_from'] . ' ' . $fparams['time_from'])) . "'";
     }
     if ($fparams['date_to']) {
         $sql_where[] = "`t1`.`measuring_timestamp` <= '" . date('Y-m-d H:i:s', strtotime($fparams['date_to'] . ' ' . $fparams['time_to'])) . "'";
     }
     if ($fparams['station_id']) {
         $sql_where[] = "t1.station_id = '" . $fparams['station_id'] . "'";
     }
     $sql_where_str = count($sql_where) ? " AND " . implode(' AND ', $sql_where) . " " : "";
     if ($fparams['order_field'] == 'date') {
         $sql_order = "`t1`.`measuring_timestamp` " . $fparams['order_direction'];
     } elseif ($fparams['order_field'] == 'stationid') {
         $sql_order = "`t1`.`station_id` " . $fparams['order_direction'];
     } elseif ($fparams['order_field'] == 'sensorid') {
         $sql_order = "`t1`.`sensor_id` " . $fparams['order_direction'];
     } elseif ($fparams['order_field'] == 'value') {
         $sql_order = "CAST(`t1`.`sensor_feature_value` AS DECIMAL(15,4)) " . $fparams['order_direction'];
     } else {
         $sql_order = "`t1`.`measuring_timestamp` ";
     }
     /*----------- /end filter prepare --------*/
     if ($fparams['showdata']) {
         $sql_groupped = "SELECT `sensor_data_id`, `sensor_feature_id`, `measuring_timestamp`, CAST(`sensor_feature_value` AS DECIMAL(15,4)) as `sensor_feature_value`, `listener_log_id`\n                         FROM `" . SensorData::model()->tableName() . "`\n                         ORDER BY `measuring_timestamp` DESC\n                         LIMIT 1000";
         $sql = "SELECT `t1`.`sensor_data_id`\n                FROM " . SensorData::model()->tableName() . " `t1`\n                LEFT JOIN ({$sql_groupped}) `gt` ON `gt`.`sensor_feature_id` = `t1`.`sensor_feature_id` AND `gt`.`measuring_timestamp` < `t1`.`measuring_timestamp`\n                LEFT JOIN `" . StationSensorFeature::model()->tableName() . "` t2 ON t2.sensor_feature_id = t1.sensor_feature_id\n                LEFT JOIN `" . SensorDBHandlerDefaultFeature::model()->tableName() . "` t3 ON t3.feature_code LIKE t2.feature_code\n                WHERE (\n                        (t2.has_filter_max AND CAST(`t1`.`sensor_feature_value` AS DECIMAL(15,4)) >  (t3.filter_max * IF(t2.is_cumulative, t1.period/60, 1) ) )\n                        OR (t2.has_filter_min AND CAST(`t1`.`sensor_feature_value` AS DECIMAL(15,4)) < (t3.filter_min * IF(t2.is_cumulative, t1.period/60, 1) ))\n                        OR (t2.has_filter_diff AND `gt`.`sensor_data_id` > 0 AND ABS(CAST(`gt`.`sensor_feature_value` AS DECIMAL(15,4)) - CAST(`t1`.`sensor_feature_value` AS DECIMAL(15,4))) > (t3.filter_diff * IF(t2.is_cumulative, t1.period/60, 1)))\n                       )\n                  {$sql_where_str}\n                GROUP BY `t1`.`sensor_data_id`\n                LIMIT 1000";
         $total = count(Yii::app()->db->createCommand($sql)->queryColumn());
         $pages = new CPagination($total);
         $pages->pageSize = 20;
         //$pages->applyLimit($criteria);
         $sql = "SELECT `t1`.`sensor_data_id`, `t1`.`sensor_feature_id`, CAST(`t1`.`sensor_feature_value` AS DECIMAL(15,4)) as `sensor_feature_value`, `t1`.`measuring_timestamp`, t1.period,\n                       `gt`.`measuring_timestamp` AS `prev_measuring_timestamp`, `gt`.`listener_log_id` AS `prev_listener_log_id`,  CAST(`gt`.`sensor_feature_value` AS DECIMAL(15,4)) as `prev_sensor_feature_value`, `gt`.`sensor_data_id` as `prev_sensor_data_id`,\n                       `t5`.`filter_max`, `t5`.`filter_min`, `t5`.`filter_diff`, t2.has_filter_max, t2.has_filter_min, t2.has_filter_diff, t2.is_cumulative,\n                       `t3`.`sensor_id_code`,\n                       `t4`.`station_id_code`\n                FROM " . SensorData::model()->tableName() . " `t1`\n                LEFT JOIN ({$sql_groupped}) `gt` ON `gt`.`sensor_feature_id` = `t1`.`sensor_feature_id` AND `gt`.`measuring_timestamp` < `t1`.`measuring_timestamp`\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 = t1.sensor_id\n                LEFT JOIN `" . Station::model()->tableName() . "` t4 ON t4.station_id = t1.station_id\n                LEFT JOIN `" . SensorDBHandlerDefaultFeature::model()->tableName() . "` t5 ON t5.feature_code LIKE t2.feature_code\n                WHERE (\n                       (t2.has_filter_max AND CAST(`t1`.`sensor_feature_value` AS DECIMAL(15,4)) > (t5.filter_max * IF(t2.is_cumulative, t1.period/60, 1) ) )\n                       OR (t2.has_filter_min AND CAST(`t1`.`sensor_feature_value` AS DECIMAL(15,4)) < (t5.filter_min * IF(t2.is_cumulative, t1.period/60, 1) ))\n                       OR (t2.has_filter_diff AND `gt`.`sensor_data_id` > 0 AND ABS(CAST(`gt`.`sensor_feature_value` AS DECIMAL(15,4)) - CAST(`t1`.`sensor_feature_value` AS DECIMAL(15,4))) > (t5.filter_diff * IF(t2.is_cumulative, t1.period/60, 1) ))\n                      )\n                  {$sql_where_str}\n                GROUP BY `t1`.`sensor_data_id`\n                 HAVING  (\n                        (t2.has_filter_max AND CAST(`sensor_feature_value` AS DECIMAL(15,4)) > (t5.filter_max * IF(t2.is_cumulative, t1.period/60, 1) ) )\n                        OR (t2.has_filter_min AND CAST(`sensor_feature_value` AS DECIMAL(15,4)) < (t5.filter_min * IF(t2.is_cumulative, t1.period/60, 1) ))\n                        OR (t2.has_filter_diff AND prev_sensor_data_id > 0 AND ABS(CAST(`prev_sensor_feature_value` AS DECIMAL(15,4)) - CAST(`sensor_feature_value` AS DECIMAL(15,4))) > (t5.filter_diff * IF(t2.is_cumulative, t1.period/60, 1) ))\n\t\t\t            )\n                ORDER BY {$sql_order}\n                LIMIT " . $pages->currentPage * $pages->pageSize . ", " . $pages->pageSize;
         $list = Yii::app()->db->createCommand($sql)->queryAll();
         if ($list) {
             foreach ($list as $key => &$value) {
                 $multiplyer_str = '';
                 $multiplyer = 1;
                 if ($value['is_cumulative']) {
                     $multiplyer_str = $value['period'] != 60 ? ' * ' . $value['period'] . 'min/60' : '';
                     $multiplyer = $value['period'] / 60;
                 }
                 if (isset($value['has_filter_max']) && $value['sensor_feature_value'] > $value['filter_max'] * $multiplyer) {
                     $value['filter_reason'][] = array('main' => 'T1 > ' . $value['filter_max'] . $multiplyer_str);
                 }
                 if (isset($value['has_filter_min']) && $value['sensor_feature_value'] < $value['filter_min'] * $multiplyer) {
                     $value['filter_reason'][] = array('main' => 'T1 < ' . $value['filter_min'] . $multiplyer_str);
                 }
                 if (isset($value['prev_sensor_feature_value']) && isset($value['has_filter_diff']) && abs($value['sensor_feature_value'] - $value['prev_sensor_feature_value']) > $value['filter_diff'] * $multiplyer) {
                     $value['filter_reason'][] = array('main' => '|T1 - T0| > ' . $value['filter_diff'] . $multiplyer_str, 'extra' => '(Previous value: ' . $value['prev_sensor_feature_value'] . ' on ' . $value['prev_measuring_timestamp'] . ')');
                 }
             }
         }
         $this->render('awsfiltered', array('list' => $list, 'clean_page' => false, 'pages' => $pages, 'stations' => $stations, 'fparams' => $fparams));
     } else {
         $this->render('awsfiltered', array('list' => $list, 'clean_page' => true, 'stations' => $stations, 'fparams' => $fparams));
     }
 }
Пример #11
0
 public function process($path)
 {
     if (!file_exists($path)) {
         throw new Exception('Can\'t find file ' . $path);
     }
     $pathinfo = pathinfo($path);
     $base_filename = $pathinfo['basename'];
     $xml_content = file_get_contents($path);
     if (!strlen($xml_content)) {
         throw new Exception($base_filename . " is empty");
     }
     libxml_use_internal_errors(true);
     $sxe = simplexml_load_string($xml_content);
     $error_str = "";
     if ($sxe === false) {
         foreach (libxml_get_errors() as $error) {
             $error_str .= "\n" . $error->message;
         }
         throw new Exception($error_str);
     }
     // XML must contain 1 RUNWAY tag
     if (count($sxe->RUNWAY) > 1) {
         throw new Exception('XML ' . $base_filename . ' contains ' . count($sxe->RUNWAY) . ' RUNWAY tags');
     }
     if (count($sxe->RUNWAY) == 0) {
         throw new Exception('XML ' . $base_filename . ' doesn\'t contain RUNWAY tags');
     }
     // RUNWAY's "NAME" attribute must be "08/26"
     if ($sxe->RUNWAY['NAME'] != '08/26') {
         throw new Exception('XML ' . $base_filename . ' RUNWAY name = "' . $sxe->RUNWAY['NAME'] . '", "08/26" was expected');
     }
     // RUNWAY must contain at least 1 ZONE tag
     if (count($sxe->RUNWAY->ZONE) == 0) {
         throw new Exception('XML ' . $base_filename . ' doesn\'t contain ZONE tags');
     }
     // XML must contain "UNITS" tag
     if (!isset($sxe->UNITS)) {
         throw new Exception('XML ' . $base_filename . ' doesn\'t contain UNITS section');
     }
     $str = '';
     $possible_units = array('WIND' => 'kt', 'VISBILITY' => 'meters', 'RVR' => 'meters', 'ALTIMETER' => 'hpa');
     foreach ($possible_units as $key => $value) {
         if (!isset($sxe->UNITS->{$key})) {
             $str .= ($str ? '; ' : '') . ' UNITS[' . $key . '] is missed';
         } else {
             if ($sxe->UNITS->{$key} != $value) {
                 $str .= ($str ? '; ' : '') . ' unknown metric "' . $sxe->UNITS->{$key} . '" in UNITS[' . $key . ']';
             }
         }
     }
     if ($str) {
         throw new Exception($str);
     }
     $result = array();
     $messages = array();
     for ($key = 0; $key < count($sxe->RUNWAY->ZONE); $key++) {
         // Get's Station ID
         if ($sxe->RUNWAY->ZONE[$key]['NAME'] == '08') {
             $messages[$key]['station_id_code'] = 'AWS08';
         } else {
             if ($sxe->RUNWAY->ZONE[$key]['NAME'] == '26') {
                 $messages[$key]['station_id_code'] = 'AWS26';
             } else {
                 continue;
             }
         }
         //Gets sensor's data from tags:
         // WIND SPEED
         if (isset($sxe->RUNWAY->ZONE[$key]->WSPD_5SEC)) {
             $messages[$key]['sensors']['WindSpeed'][0]['wind_speed_1'] = (string) $sxe->RUNWAY->ZONE[$key]->WSPD_5SEC;
         }
         if (isset($sxe->RUNWAY->ZONE[$key]->WSPD_2MIN)) {
             $messages[$key]['sensors']['WindSpeed'][0]['wind_speed_2'] = (string) $sxe->RUNWAY->ZONE[$key]->WSPD_2MIN;
         }
         // WIND DIRECTION
         if (isset($sxe->RUNWAY->ZONE[$key]->WDIR_5SEC)) {
             $messages[$key]['sensors']['WindDirection'][0]['wind_direction_1'] = (string) $sxe->RUNWAY->ZONE[$key]->WDIR_5SEC;
         }
         if (isset($sxe->RUNWAY->ZONE[$key]->WDIR_2MIN)) {
             $messages[$key]['sensors']['WindDirection'][0]['wind_direction_2'] = (string) $sxe->RUNWAY->ZONE[$key]->WDIR_2MIN;
         }
         // TEMPERATURE
         if (isset($sxe->RUNWAY->ZONE[$key]->TEMP_5MIN)) {
             $messages[$key]['sensors']['Temperature'][0]['temperature'] = (string) $sxe->RUNWAY->ZONE[$key]->TEMP_5MIN;
         }
         // HUMIDITY
         if (isset($sxe->RUNWAY->ZONE[$key]->HUM_5MIN)) {
             $messages[$key]['sensors']['Humidity'][0]['humidity'] = (string) $sxe->RUNWAY->ZONE[$key]->HUM_5MIN;
         }
         // PRESSURE
         if (isset($sxe->RUNWAY->ZONE[$key]->PRESSURE1)) {
             $messages[$key]['sensors']['Pressure'][0]['pressure'] = (string) $sxe->RUNWAY->ZONE[$key]->PRESSURE1;
         }
         if (isset($sxe->RUNWAY->ZONE[$key]->PRESSURE2)) {
             $messages[$key]['sensors']['Pressure'][1]['pressure'] = (string) $sxe->RUNWAY->ZONE[$key]->PRESSURE2;
         }
         if (isset($sxe->RUNWAY->ZONE[$key]->PRESSURE3)) {
             $messages[$key]['sensors']['Pressure'][2]['pressure'] = (string) $sxe->RUNWAY->ZONE[$key]->PRESSURE3;
         }
         // CLOUD
         if (isset($sxe->RUNWAY->ZONE[$key]->CLOUDRANGE)) {
             $messages[$key]['sensors']['CloudHeightAWS'][0]['cloud_measuring_range'] = (string) $sxe->RUNWAY->ZONE[$key]->CLOUDRANGE;
         }
         if (isset($sxe->RUNWAY->ZONE[$key]->CLOUDVV)) {
             $messages[$key]['sensors']['CloudHeightAWS'][0]['cloud_vertical_visibility'] = (string) $sxe->RUNWAY->ZONE[$key]->CLOUDVV;
         }
         if (isset($sxe->RUNWAY->ZONE[$key]->CLOUDH1)) {
             $messages[$key]['sensors']['CloudHeightAWS'][0]['cloud_height_height_1'] = (string) $sxe->RUNWAY->ZONE[$key]->CLOUDH1;
         }
         if (isset($sxe->RUNWAY->ZONE[$key]->CLOUDH2)) {
             $messages[$key]['sensors']['CloudHeightAWS'][0]['cloud_height_height_2'] = (string) $sxe->RUNWAY->ZONE[$key]->CLOUDH2;
         }
         if (isset($sxe->RUNWAY->ZONE[$key]->CLOUDH3)) {
             $messages[$key]['sensors']['CloudHeightAWS'][0]['cloud_height_height_3'] = (string) $sxe->RUNWAY->ZONE[$key]->CLOUDH3;
         }
         if (isset($sxe->RUNWAY->ZONE[$key]->CLOUDD1)) {
             $messages[$key]['sensors']['CloudHeightAWS'][0]['cloud_height_depth_1'] = (string) $sxe->RUNWAY->ZONE[$key]->CLOUDD1;
         }
         if (isset($sxe->RUNWAY->ZONE[$key]->CLOUDD2)) {
             $messages[$key]['sensors']['CloudHeightAWS'][0]['cloud_height_depth_2'] = (string) $sxe->RUNWAY->ZONE[$key]->CLOUDD2;
         }
         if (isset($sxe->RUNWAY->ZONE[$key]->CLOUDD3)) {
             $messages[$key]['sensors']['CloudHeightAWS'][0]['cloud_height_depth_3'] = (string) $sxe->RUNWAY->ZONE[$key]->CLOUDD3;
         }
         // VISIBILITY
         $vis = (string) $sxe->RUNWAY->ZONE[$key]->EXC;
         if (isset($sxe->RUNWAY->ZONE[$key]->EXC) && is_numeric($vis) && $vis != 0) {
             //P = (1/σ) x ln (1/0.05)
             //where ln is the log to base e or the natural logarithm. σ  is the extinction cooefficient.
             //This number will be in km, so we will need to multiply by 1000.
             $messages[$key]['sensors']['VisibilityAWS'][0]['visibility_1'] = 1 / $vis * log(20) * 1000;
         }
         // SOLAR
         if (isset($sxe->RUNWAY->ZONE[$key]->SOLAR_1MIN)) {
             $messages[$key]['sensors']['SolarRadiation'][0]['solar_radiation_in_period'] = (string) $sxe->RUNWAY->ZONE[$key]->SOLAR_1MIN;
         }
         // Rain fall
         if (isset($sxe->RUNWAY->ZONE[$key]->PRECIP_ACCUM)) {
             $messages[$key]['sensors']['RainAws'][0]['period'] = 5;
             $messages[$key]['sensors']['RainAws'][0]['rain_in_period'] = (string) $sxe->RUNWAY->ZONE[$key]->PRECIP_ACCUM;
         }
         // Sunshine duration
         if (isset($sxe->RUNWAY->ZONE[$key]->SUN_ACCUM)) {
             $messages[$key]['sensors']['SunshineDuration'][0]['period'] = 5;
             $messages[$key]['sensors']['SunshineDuration'][0]['sun_duration_in_period'] = (string) $sxe->RUNWAY->ZONE[$key]->SUN_ACCUM;
         }
     }
     if (!$messages) {
         $result[] = $base_filename . " : No datasets found";
         return implode("\n", $result);
     }
     $result[] = $base_filename . " : " . count($messages) . ' datasets were found';
     $sql = "SELECT * \n                FROM `" . Station::model()->tableName() . "` `t1`\n                WHERE `t1`.`station_id_code` IN ('AWS08', 'AWS26')";
     $res = Yii::app()->db->createCommand($sql)->queryAll();
     if (!$res) {
         $result[] = "AWS08 and AWS26 are not exist in database, no sense to convert XML into messages";
         return implode("\n", $result);
     }
     // for each stationID looks for sensors and features of this station
     $stations = array();
     foreach ($res as $key => $value) {
         $stations[$value['station_id_code']] = $value;
         $sql = "SELECT `t1`.`sensor_id_code`,\n                           `t1`.`station_id`,\n                           `t2`.`feature_code`,\n                           `t3`.`code` AS `metric_code`,\n                           `t4`.`handler_id_code`\n                    FROM `" . StationSensor::model()->tableName() . "` `t1`\n                    LEFT JOIN `" . StationSensorFeature::model()->tableName() . "` `t2` ON `t1`.`station_sensor_id` = `t2`.`sensor_id`\n                    LEFT JOIN `" . RefbookMetric::model()->tableName() . "`        `t3` ON `t3`.`metric_id` = `t2`.`metric_id`\n                    LEFT JOIN `" . SensorDBHandler::model()->tableName() . "`      `t4` ON `t4`.`handler_id` = `t1`.`handler_id`\n                    WHERE `t1`.`station_id` = '" . $value['station_id'] . "'\n                    ORDER BY `t4`.`handler_id_code` ASC, `t1`.`sensor_id_code` ASC";
         $res2 = Yii::app()->db->createCommand($sql)->queryAll();
         if ($res2) {
             $tmp = array();
             foreach ($res2 as $value2) {
                 $tmp[$value2['handler_id_code']][$value2['sensor_id_code']][$value2['feature_code']] = $value2['metric_code'];
             }
             foreach ($tmp as $key_handler => $value_sensors) {
                 foreach ($value_sensors as $key_sensor => $value_features) {
                     $stations[$value['station_id_code']]['sensors'][$key_handler][] = array('sensor_id_code' => $key_sensor, 'features' => $value_features);
                 }
             }
         }
     }
     $date_parsed = date_parse_from_format("D, j M Y H:i:s", $sxe->DATE);
     $date_prepared = mktime($date_parsed['hour'], $date_parsed['minute'], $date_parsed['second'], $date_parsed['month'], $date_parsed['day'], $date_parsed['year']);
     // convert parsed XML data into regular message
     // for this kind of messages we have an agreement to put X instead of D at the beginning of message.
     foreach ($messages as $key => $value) {
         if (!$stations[$value['station_id_code']]) {
             $result[] = $value['station_id_code'] . " station is not exists in database, no sense to convert RNWY part into message";
             continue;
         }
         $result_message_body = 'X' . $value['station_id_code'];
         $result_message_body .= date('ymdHi', $date_prepared);
         $result_message_body .= '00';
         // we need last_log for this satation to calculate period of measurement (some sensors' strings should contain this period
         $last_logs = ListenerLog::getLast2Messages($stations[$value['station_id_code']]['station_id']);
         if (isset($value['sensors'])) {
             foreach ($value['sensors'] as $key_handler => $value_sensors) {
                 if ($value_sensors) {
                     foreach ($value_sensors as $key_sensor => $value2) {
                         if (isset($stations[$value['station_id_code']]['sensors'][$key_handler][$key_sensor])) {
                             // create handler for each sensor (we parsed new data for)
                             $handler = SensorHandler::create($key_handler);
                             if ($key_handler == 'SolarRadiation' && $last_logs[0]['log_id']) {
                                 if ($last_logs[0]['log_id']) {
                                     $total_minutes = round(abs($date_prepared - strtotime($last_logs[0]['measuring_timestamp'])) / 60);
                                     $value2['period'] = $total_minutes;
                                 } else {
                                     $value2['period'] = 1;
                                 }
                                 if ($value2['solar_radiation_in_period'][0] != 'M') {
                                     $value2['solar_radiation_in_period'] = $value2['solar_radiation_in_period'] * $value2['period'] * 60;
                                 }
                             }
                             // each handler has it's own implementation of preparing sensors string for message basing on XML data
                             $res = $handler->prepareXMLValue($value2, $stations[$value['station_id_code']]['sensors'][$key_handler][$key_sensor]['features']);
                             $result_message_body .= $stations[$value['station_id_code']]['sensors'][$key_handler][$key_sensor]['sensor_id_code'] . $res;
                         }
                     }
                 }
             }
             $result_message_body .= It::prepareCRC($result_message_body);
             $result_message_body = '@' . $result_message_body . '$';
             // add new message into database. It will be processed later as all newcame messages
             $log_id = ListenerLog::addNew($result_message_body, 0, 1);
             $result[] = $base_filename . " : New message #" . $log_id . " was added";
         }
     }
     // return some comments created during convertation
     return implode("\n", $result);
 }
Пример #12
0
 public function m_0_4_18()
 {
     @apache_setenv('no-gzip', 1);
     @ini_set('zlib.output_compression', 0);
     @ini_set('implicit_flush', 1);
     ini_set('memory_limit', '-1');
     $this->flushNotification("\n...Please wait... Updating is going on... DO NOT LEAVE THIS PAGE!");
     $this->flushNotification("<br/><br/>...Add new measurement....");
     $sql = "INSERT INTO `refbook_measurement_type` (`measurement_type_id` ,`display_name` ,`code` ,`ord`)\n                VALUES ('21', 'Cloud Measuring Range', 'cloud_measuring_range', '18')";
     Yii::app()->db->createCommand($sql)->query();
     Yii::app()->db->createCommand("COMMIT")->query();
     $sql = "INSERT INTO `refbook_measurement_type_metric` (`measurement_type_metric_id` ,`measurement_type_id` ,`metric_id` ,`is_main`)\n                VALUES ('32', '21', '11', '1'), ('33', '21', '22', '0')";
     Yii::app()->db->createCommand($sql)->query();
     Yii::app()->db->createCommand("COMMIT")->query();
     $sql = "ALTER TABLE `sensor_data` ADD `is_m` TINYINT( 1 ) NOT NULL DEFAULT '0' AFTER `sensor_feature_value` ";
     Yii::app()->db->createCommand($sql)->query();
     Yii::app()->db->createCommand("COMMIT")->query();
     $sql = "ALTER TABLE `sensor_data` DROP INDEX `sensor_data__feature_measuuring_index` ,\n                ADD INDEX `sensor_data__feature_measuuring_index` ( `sensor_feature_id` , `measuring_timestamp` , `is_m` )";
     Yii::app()->db->createCommand($sql)->query();
     Yii::app()->db->createCommand("COMMIT")->query();
     $sql = "SELECT * FROM `" . StationSensor::model()->tableName() . "` WHERE `handler_id` = 15";
     $res = Yii::app()->db->createCommand($sql)->queryAll();
     if ($res) {
         foreach ($res as $key => $value) {
             $sql = "SELECT * \n                        FROM `" . StationSensorFeature::model()->tableName() . "` \n                        WHERE sensor_id = '" . $value['station_sensor_id'] . "' AND `feature_code` IN ('cloud_height_height_1', 'cloud_measuring_range')\n                        ORDER BY `feature_code`";
             $res2 = Yii::app()->db->createCommand($sql)->queryAll();
             if ($res2 && count($res2) == 1) {
                 $obj = new StationSensorFeature();
                 $obj->sensor_id = $res2[0]['sensor_id'];
                 $obj->feature_code = 'cloud_measuring_range';
                 $obj->feature_display_name = 'Measuring Range';
                 $obj->feature_constant_value = 0;
                 $obj->measurement_type_code = 'cloud_measuring_range';
                 $obj->metric_id = $res2[0]['metric_id'];
                 $obj->is_main = 0;
                 $obj->has_filters = 1;
                 $obj->has_filter_min = 1;
                 $obj->has_filter_max = 1;
                 $obj->has_filter_diff = 1;
                 $obj->is_constant = 0;
                 $obj->is_cumulative = 0;
                 $obj->save();
             }
         }
     }
     $this->flushNotification("<br/><br/>...Updated ....");
     It::memStatus('update__success');
     $this->flushNotification('<script type="text/javascript"> setTimeout(function(){document.location.href="' . Yii::app()->controller->createUrl('update/index') . '"}, 5000)</script>');
 }
Пример #13
0
 public function actionMetrics()
 {
     $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('superadmin/metrics'));
     }
     $this->render('metrics', array('meas_types' => $meas_types));
 }
Пример #14
0
 /**
  * Update station_sensor_feature from sensor_handler_default_feature
  * Page Admin/SetupSensor
  *
  * @param $handler SensorDBHandler
  *                 Only for handler_id
  * @param $feature SensorDBHandlerDefaultFeature
  *
  * @return int
  */
 public static function updateByDefault($handler, $feature)
 {
     $qb = new CDbCriteria();
     $qb->with = ['sensor' => ['select' => false, 'condition' => "sensor.handler_id = {$handler->handler_id}"]];
     $qb->condition = "feature_code = '{$feature->feature_code}'";
     return StationSensorFeature::model()->updateAll(['metric_id' => $feature->metric_id, 'filter_max' => $feature->filter_max, 'filter_min' => $feature->filter_min, 'filter_diff' => $feature->filter_diff, 'feature_constant_value' => $feature->feature_constant_value], $qb);
 }
Пример #15
0
 public function generate()
 {
     $this->_logger->log(__METHOD__);
     if ($this->errors) {
         $this->_logger->log(__METHOD__, array('errors' => $this->errors));
         return false;
     }
     $current_user_timezone = date_default_timezone_get();
     $timezone_id = 'UTC';
     if ($timezone_id != $current_user_timezone) {
         TimezoneWork::set($timezone_id);
     }
     $this->report_parts = array();
     $this->explanations = array();
     // get sensors' values for all messages received in reporting period
     $sql = "SELECT `t5`.`listener_log_id`,\r\n                       `t5`.`measuring_timestamp`,\r\n                       `t1`.`station_sensor_id`, `t1`.`sensor_id_code`, \r\n                       \r\n                       `t3`.`feature_code`, `t3`.`feature_constant_value`,\r\n                       `t4`.`code` AS `metric_code`, \r\n                       `t5`.`sensor_feature_value`, \r\n                       `t5`.`is_m`,\r\n                       `t5`.`period` AS `sensor_feature_period`,\r\n                       `t6`.`code` AS `value_metric_code`,\r\n                       `t7`.`handler_id_code`\r\n\r\n                FROM `" . SensorData::model()->tableName() . "`  `t5`\r\n                LEFT JOIN `" . StationSensor::model()->tableName() . "`        `t1` ON t1.station_sensor_id = t5.sensor_id\r\n                LEFT JOIN `" . StationSensorFeature::model()->tableName() . "` `t3` ON (`t3`.`sensor_feature_id` = `t5`.`sensor_feature_id`)\r\n                LEFT JOIN `" . RefbookMetric::model()->tableName() . "`        `t4` ON `t4`.`metric_id` = `t3`.`metric_id`\r\n                LEFT JOIN `" . RefbookMetric::model()->tableName() . "`        `t6` ON `t6`.`metric_id` = `t5`.`metric_id`\r\n                LEFT JOIN `" . SensorDBHandler::model()->tableName() . "`      `t7` ON t7.handler_id = t1.handler_id\r\n                WHERE `t5`.`station_id` = '" . $this->station_info->station_id . "' AND `t5`.`listener_log_id` IN (" . $this->schedule_process_info->listener_log_ids . ")\r\n                ORDER BY `t5`.`measuring_timestamp` DESC, `t1`.`sensor_id_code` ASC, `t3`.`feature_code` ASC";
     $sensor_data = Yii::app()->db->createCommand($sql)->queryAll();
     $data = array();
     if ($sensor_data) {
         // get calculation values for all messages received in reporting period
         $sql = "SELECT `t1`.`listener_log_id`,\r\n                           `t1`.`value`,\r\n                           `t3`.`handler_id_code`\r\n                    FROM `" . StationCalculationData::model()->tableName() . "`    `t1`\r\n                    LEFT JOIN `" . StationCalculation::model()->tableName() . "`   `t2` ON t2.calculation_id = t1.calculation_id\r\n                    LEFT JOIN `" . CalculationDBHandler::model()->tableName() . "` `t3` ON `t3`.`handler_id` = `t2`.`handler_id`\r\n                    WHERE `t2`.`station_id` = '" . $this->station_info->station_id . "' AND `t1`.`listener_log_id` IN (" . $this->schedule_process_info->listener_log_ids . ")\r\n                    ORDER BY `t3`.`handler_id_code`";
         $res2 = Yii::app()->db->createCommand($sql)->queryAll();
         if ($res2) {
             foreach ($res2 as $key => $value) {
                 $calculations[$value['listener_log_id']][] = $value;
             }
         }
         foreach ($sensor_data as $key => $value) {
             $data[$value['listener_log_id']][] = $value;
         }
         // prepare $result_item array, where each line represents line in report.
         foreach ($data as $key => $value) {
             $result_item = array('StationId', $this->station_info->station_id_code, 'WMO AWS #', $this->station_info->wmo_block_number . $this->station_info->station_number, 'National AWS #', $this->station_info->national_aws_number, 'Tx DateTime', date('m/d/Y H:i', strtotime($value[0]['measuring_timestamp'])));
             foreach ($value as $key2 => $value2) {
                 $handler_obj = SensorHandler::create($value2['handler_id_code']);
                 if (in_array($value2['handler_id_code'], array('BatteryVoltage', 'Humidity', 'Pressure', 'Temperature'))) {
                     $sensor_id_code = $value2['sensor_id_code'];
                 } else {
                     $sensor_id_code = $value2['sensor_id_code'] . ' (' . $handler_obj->getFeatureName($value2['feature_code']) . ')';
                 }
                 $result_item[] = $sensor_id_code;
                 if ($value2['is_m']) {
                     $result_item[] = '-';
                 } else {
                     $value2['sensor_feature_value'] = $handler_obj->applyOffset($value2['sensor_feature_value'], $this->station_info->magnetic_north_offset);
                     $result_item[] = str_replace(',', ' ', $handler_obj->formatValue($value2['sensor_feature_value'], $value2['feature_code']));
                 }
             }
             if (isset($calculations[$key])) {
                 foreach ($calculations[$key] as $key2 => $value2) {
                     if ($value2['handler_id_code'] === 'DewPoint') {
                         $result_item[] = 'DP';
                     } else {
                         if ($value2['handler_id_code'] === 'PressureSeaLevel') {
                             $result_item[] = 'MSL';
                         } else {
                             $result_item[] = 'Unknown calculation';
                         }
                     }
                     $result_item[] = str_replace(',', ' ', number_format(round($value2['value'], 1), 1));
                 }
             }
             $this->report_parts[] = $result_item;
         }
     }
     if ($timezone_id != $current_user_timezone) {
         TimezoneWork::set($current_user_timezone);
     }
     $this->_logger->log(__METHOD__ . ' Export generation completed.');
     return true;
 }
Пример #16
0
 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);
 }
Пример #17
0
 /**
  * However Rain Datalogger can have more than 1 sensor.
  * Message from Rain Datalogger's log has information about only 1 sensor. 
  * This function is used to get information about first sensor of this station
  * 
  *  @access protected
  *  @return array 
  */
 protected function getFirstRGSensor()
 {
     $criteria = new CDbCriteria();
     $criteria->with = array('handler', 'first_sensor_feature');
     $criteria->compare('station_id', $this->_station->station_id);
     $criteria->compare('first_sensor_feature.sensor_feature_id', '>0');
     $sensor = StationSensor::model()->find($criteria);
     if (is_null($sensor)) {
         $this->pushWarning('cant_find_rain_sensor', 'Can not find rain sensor for RG station "' . $this->_station->station_id_code . '" in the DB. Sensor value was not saved.');
         return false;
     } else {
         return array('sensor' => $sensor, 'features' => StationSensorFeature::getInfoForHandler($sensor->station_sensor_id));
     }
 }
Пример #18
0
 /**
  * Update general tables in backup database.
  * Actual Data in these tables are required to support integrity of database and store sensors values
  * @return string 
  */
 private function updateMainStationInformation()
 {
     $tables = array(Station::model()->tableName(), Settings::model()->tableName(), StationSensor::model()->tableName(), StationSensorFeature::model()->tableName(), StationCalculation::model()->tableName(), StationCalculationVariable::model()->tableName());
     $result_sql = array();
     foreach ($tables as $table) {
         $sql = "SELECT * FROM `" . $table . "`";
         $res = Yii::app()->db->createCommand($sql)->queryAll();
         $total = count($res);
         It::debug("updateMainStationInformation: Table = " . $table . ", TOTAL = " . $total, 'backup_database');
         if ($res) {
             $fields = array();
             foreach ($res[0] as $key2 => $value2) {
                 $fields[] = $key2;
             }
             $sql_header = "INSERT IGNORE INTO `" . $table . "` (`" . implode('`,`', $fields) . "`) VALUES ";
             $res_sql = $sql_header;
             foreach ($res as $key => $value) {
                 $res_sql .= "('" . implode("','", $value) . "')";
                 if ($key + 1 < $total) {
                     $res_sql .= ", ";
                 }
             }
             $result_sql[] = $res_sql;
         }
     }
     It::debug("updateMainStationInformation: DONE", 'backup_database');
     return $result_sql;
 }