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