public function beforeDelete() { $cr_sd = new CDbCriteria(); $cr_sd->addCondition('station_id = ' . $this->station_id); $cr_cd = new CDbCriteria(); $cr_cd->with = ['calculation' => ['select' => false, 'condition' => 'calculation.station_id = ' . $this->station_id]]; $cr_slt = new CDbCriteria(); $cr_slt->with = ['sensor' => ['select' => false, 'condition' => 'sensor.station_id = ' . $this->station_id]]; if ($this->getUseLong()) { SensorData::model()->long()->deleteAll($cr_sd); StationCalculationData::model()->long()->deleteAll($cr_cd); SeaLevelTrend::model()->long()->deleteAll($cr_slt); } else { SensorData::model()->deleteAll($cr_sd); StationCalculationData::model()->deleteAll($cr_cd); SeaLevelTrend::model()->long()->deleteAll($cr_slt); } return parent::beforeDelete(); }
public function repairTrend($params) { $measuring_timestamp1 = date('Y-m-d H:i:s', $params['incoming_measuring_timestamp']); $measuring_timestamp2 = date('Y-m-d H:i:s', $params['incoming_measuring_timestamp'] - $params['feature_treshold_period'] * 60); $measuring_timestamp3 = date('Y-m-d H:i:s', $params['incoming_measuring_timestamp'] - $params['feature_treshold_period'] * 2 * 60); $measuring_timestamp4 = date('Y-m-d H:i:s', $params['incoming_measuring_timestamp'] - 18000); $criteria = new CDbCriteria(); $criteria->compare('log_id', $params['log_id']); $criteria->compare('sensor_id', $params['sensor_id']); $sea_trend = SeaLevelTrend::model()->find($criteria); if (!$sea_trend) { $sea_trend = new SeaLevelTrend(); $sea_trend->log_id = $params['log_id']; $sea_trend->sensor_id = $params['sensor_id']; } $sea_trend->measuring_timestamp = $measuring_timestamp1; $minutes = array('00'); for ($i = 1; $i <= 60; $i++) { if ($i % $params['feature_treshold_period'] == 0) { $minutes[] = $i == 60 ? '00' : str_pad($i, 2, '0', STR_PAD_LEFT); $minutes[] = $i == 60 ? '01' : str_pad($i + 1, 2, '0', STR_PAD_LEFT); } } $criteria = new CDbCriteria(); $criteria->compare('sensor_id', $params['sensor_id']); $criteria->compare('measuring_timestamp', '<' . $measuring_timestamp1); $criteria->order = 'measuring_timestamp desc'; $criteria->limit = '1'; $last_trend = SeaLevelTrend::model()->find($criteria); if (!in_array(date('i', $params['incoming_measuring_timestamp']), $minutes)) { if (is_null($last_trend)) { $sea_trend->trend = 'unknown'; $sea_trend->is_significant = 0; $sea_trend->last_high = 0; $sea_trend->last_low = 0; $sea_trend->last_high_timestamp = '0000-00-00 00:00:00'; $sea_trend->last_low_timestamp = '0000-00-00 00:00:00'; } else { $sea_trend->trend = $last_trend->trend; $sea_trend->is_significant = $last_trend->is_significant; $sea_trend->last_high = $last_trend->last_high; $sea_trend->last_low = $last_trend->last_low; $sea_trend->last_high_timestamp = $last_trend->last_high_timestamp; $sea_trend->last_low_timestamp = $last_trend->last_low_timestamp; } $sea_trend->save(); return true; } $sql = "SELECT SUM(CAST(`t1`.`sensor_feature_value` AS DECIMAL(15,4))) AS `sum`, COUNT(`t1`.`sensor_feature_id`) AS `cnt`\n FROM `" . SensorData::model()->tableName() . "` `t1`\n WHERE `t1`.`sensor_feature_id` = ?\n AND `t1`.`measuring_timestamp` > ? \n AND `t1`.`measuring_timestamp` <= ?\n AND `t1`.`is_m` = '0'\n GROUP BY `t1`.`sensor_feature_id`"; $p = array($params['sensor_feature_id'], $measuring_timestamp2, $measuring_timestamp1); $res_last = Yii::app()->db->createCommand($sql)->queryRow(true, $p); $p = array($params['sensor_feature_id'], $measuring_timestamp3, $measuring_timestamp2); $res_prev = Yii::app()->db->createCommand($sql)->queryRow(true, $p); if (!$res_prev) { if (is_null($last_trend)) { $sea_trend->is_significant = 0; $sea_trend->last_high = 0; $sea_trend->last_low = 0; $sea_trend->last_high_timestamp = '0000-00-00 00:00:00'; $sea_trend->last_low_timestamp = '0000-00-00 00:00:00'; } else { $sea_trend->trend = $last_trend->trend; $sea_trend->is_significant = $last_trend->is_significant; $sea_trend->last_high = $last_trend->last_high; $sea_trend->last_low = $last_trend->last_low; $sea_trend->last_high_timestamp = $last_trend->last_high_timestamp; $sea_trend->last_low_timestamp = $last_trend->last_low_timestamp; } } else { $t0 = $res_last['sum'] / $res_last['cnt']; $t1 = $res_prev['sum'] / $res_prev['cnt']; $difference = $t0 - $t1; if ($difference > 0) { $sea_trend->trend = 'up'; } else { if ($difference < 0) { $sea_trend->trend = 'down'; } else { $sea_trend->trend = $last_trend->trend; } } $sea_trend->is_significant = abs($difference) >= $params['feature_treshold'] ? 1 : 0; $sea_trend->last_high = $last_trend->last_high; $sea_trend->last_low = $last_trend->last_low; $sea_trend->last_high_timestamp = $last_trend->last_high_timestamp; $sea_trend->last_low_timestamp = $last_trend->last_low_timestamp; if ($sea_trend->is_significant && ($last_trend->trend != $sea_trend->trend || !$last_trend->is_significant)) { if ($sea_trend->trend === 'down') { $sql_groupped_table = "SELECT `sensor_feature_id`, MAX(CAST(`sensor_feature_value` AS DECIMAL(15,4))) AS `MaxValue` \n FROM `" . SensorData::model()->tableName() . "` \n WHERE `sensor_feature_id` = ?\n AND `measuring_timestamp` > ? \n AND `measuring_timestamp` <= ?\n AND `is_m` = '0'"; $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 INNER JOIN ( {$sql_groupped_table} ) `gr` ON `tt`.`sensor_feature_id` = `gr`.`sensor_feature_id` AND `tt`.`sensor_feature_value` = `gr`.`MaxValue`\n WHERE `tt`.`measuring_timestamp` > ? AND `tt`.`measuring_timestamp` <= ?"; $p = array($params['sensor_feature_id'], $measuring_timestamp4, $measuring_timestamp1, $measuring_timestamp4, $measuring_timestamp1); $extr = Yii::app()->db->createCommand($sql)->queryRow(true, $p); if ($extr) { $sea_trend->last_high = $extr['sensor_feature_value']; $sea_trend->last_high_timestamp = $extr['measuring_timestamp']; } } else { $sql_groupped_table = "SELECT `sensor_feature_id`, MIN(CAST(`sensor_feature_value` AS DECIMAL(15,4))) AS `MinValue` \n FROM `" . SensorData::model()->tableName() . "` \n WHERE `sensor_feature_id` = ?\n AND `measuring_timestamp` > ? \n AND `measuring_timestamp` <= ?\n AND `is_m` = '0'"; $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 INNER JOIN ( {$sql_groupped_table} ) `gr` ON `tt`.`sensor_feature_id` = `gr`.`sensor_feature_id` AND `tt`.`sensor_feature_value` = `gr`.`MinValue`\n WHERE `tt`.`measuring_timestamp` > ? AND `tt`.`measuring_timestamp` <= ?"; $p = array($params['sensor_feature_id'], $measuring_timestamp4, $measuring_timestamp1, $measuring_timestamp4, $measuring_timestamp1); $extr = Yii::app()->db->createCommand($sql)->queryRow(true, $p); if ($extr) { $sea_trend->last_low = $extr['sensor_feature_value']; $sea_trend->last_low_timestamp = $extr['measuring_timestamp']; } } } } $sea_trend->save(); return true; }
public function actionMsgHistory() { $form = new MessagesLogFilterForm(); $ids_to_delete = array(); if (Yii::app()->request->isPostRequest && isset($_POST['delete_checked'])) { if (isset($_POST['log_id'])) { $ids_to_delete = $_POST['log_id']; } } if (isset($_REQUEST['delete'])) { $id = intval($_REQUEST['delete']); if ($id) { $ids_to_delete[] = $id; } } if ($ids_to_delete) { if (!Yii::app()->user->isSuperAdmin()) { It::memStatus('only_admin_can_delete_message_history_log_messages'); } else { $cnt = 0; foreach ($ids_to_delete as $id) { $sensorData = SensorData::model()->findall('listener_log_id=:log_id', array(':log_id' => $id)); if ($sensorData) { foreach ($sensorData as $item) { $item->delete(); } } $seaLevelTrend = SeaLevelTrend::model()->findall('log_id=:log_id', array(':log_id' => $id)); if ($seaLevelTrend) { foreach ($seaLevelTrend as $item) { $item->delete(); } } $calcData = StationCalculationData::model()->findall('listener_log_id=:log_id', array(':log_id' => $id)); if ($calcData) { foreach ($calcData as $item) { $item->delete(); } } $log = ListenerLog::model()->find('log_id=:log_id', array(':log_id' => $id)); if ($log) { $log->delete(); $cnt++; } $sensorData = SensorData::model()->selectDb(true)->findall('listener_log_id=:log_id', array(':log_id' => $id)); if ($sensorData) { foreach ($sensorData as $item) { $item->selectDb(true)->delete(); } } $seaLevelTrend = SeaLevelTrend::model()->selectDb(true)->findall('log_id=:log_id', array(':log_id' => $id)); if ($seaLevelTrend) { foreach ($seaLevelTrend as $item) { $item->selectDb(true)->delete(); } } $calcData = StationCalculationData::model()->selectDb(true)->findall('listener_log_id=:log_id', array(':log_id' => $id)); if ($calcData) { foreach ($calcData as $item) { $item->selectDb(true)->delete(); } } $log = new ListenerLog(); $log = $log->selectDb(true)->find('log_id=:log_id', array(':log_id' => $id)); if ($log) { $log->selectDb(true)->delete(); $cnt++; } } It::memStatus($cnt ? 'message_history_log_was_deleted' : 'message_history_log_not_found'); } $this->redirect($this->createUrl('site/msghistory')); } if (Yii::app()->request->isPostRequest && isset($_POST['clear']) or isset($_GET['clear'])) { $form->clearMemory(); $this->redirect($this->createUrl('site/msghistory')); } if (Yii::app()->request->isPostRequest && isset($_POST['filter'])) { $form->attributes = $_POST['MessagesLogFilterForm']; if ($form->validate()) { $this->redirect($this->createUrl('site/msghistory')); } } if (isset($_REQUEST['of'])) { $form->setOrders($_REQUEST['of']); $this->redirect($this->createUrl('site/msghistory')); } if (Yii::app()->request->isPostRequest && isset($_POST['export'])) { $form->attributes = $_POST['MessagesLogFilterForm']; if ($form->validate()) { $res = $form->makeExport(); } } $res = $form->prepareList(); $this->render('messages_history', array('list' => $res['list'], 'pages' => $res['pages'], 'form' => $form)); }
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)); }
private function prepareListenerLogDependantInserts($listener_log_ids) { $tables = array(ListenerLogProcessError::model()->tableName() => 'log_id', SensorData::model()->tableName() => 'listener_log_id', SensorDataMinute::model()->tableName() => 'listener_log_id', SeaLevelTrend::model()->tableName() => 'log_id', StationCalculationData::model()->tableName() => 'listener_log_id'); $result_sql = array(); foreach ($tables as $table => $log_id_field) { $sql = "SELECT * \n FROM `" . $table . "`\n WHERE `" . $log_id_field . "` IN (" . implode(',', $listener_log_ids) . ")"; $res = Yii::app()->db->createCommand($sql)->queryAll(); $total = count($res); if ($res) { $fields = array(); foreach ($res[0] as $key2 => $value2) { $fields[] = $key2; } $sql_header = "INSERT IGNORE INTO `" . $table . "` (`" . implode('`,`', $fields) . "`) VALUES "; $res_sql = $sql_header; $cnt = 0; foreach ($res as $key => $value) { $res_sql .= "('" . implode("','", $value) . "')"; if ($key + 1 < $total) { $res_sql .= ", "; } $cnt++; } $result_sql[] = $res_sql; $this->addBackupLog("Prepared " . $cnt . " inserts for " . $table); } } return $result_sql; }