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