protected function getData()
{
$this->_logger->log(__METHOD__ . ' $this->start_time: ' . print_r($this->start_time, 1));
$this->_logger->log(__METHOD__ . ' $this->end_time: ' . print_r($this->end_time, 1));
$result = ListenerLog::model()->getAllDataInType($this->station_type, $this->start_time, $this->end_time);
$this->_logger->log(__METHOD__ . ' $result COUNT: ' . print_r(COUNT($result), 1));
if (!is_null($result)) {
// $this->schedule_type_report->updateLastReportedMessageData($result[0]->created);
$this->data = $result;
return true;
} else {
$this->data = array();
// $this->schedule_type_report->updateLastReportedMessageData($this->end_time);
return false;
}
}
function actionIndex()
{
ini_set('memory_limit', '-1');
print 'Checking `listener_log` table upgrade....<br/>';
$sql = "SHOW COLUMNS FROM `" . ListenerLog::model()->tableName() . "` LIKE 'is_last'";
$res = Yii::app()->db->createCommand($sql)->queryAll();
if (!$res) {
print '<br/><br/>Checked - requires update.<br/><br/>Attention! Script going to make small update to your database.... Please, be patient, don\'t use system before script is completed.';
$sql = "ALTER TABLE `" . ListenerLog::model()->tableName() . "` ADD `is_last` tinyint(1) NOT NULL DEFAULT '0' AFTER `is_processed`";
Yii::app()->db->createCommand($sql)->query();
$sql = "SELECT * FROM `" . Station::model()->tableName() . "`";
$stations = Yii::app()->db->createCommand($sql)->queryAll();
if ($stations) {
foreach ($stations as $key => $value) {
ListenerLog::updateIsLastForStation($value['station_id']);
}
}
print '<br><br>.....<br><br>Done!. <br/><br/>You can continue work with <a href="' . It::baseUrl() . '">Delairco</a>';
} else {
print '<br/>Checked - doesn\'t require update. <br/><br/>You can continue work with <a href="' . It::baseUrl() . '">Delairco</a>';
}
}
public static function getHistory($schedule_id, $page_size = 15)
{
if ($page_size) {
$sql = "SELECT COUNT(t1.schedule_processed_id)\n FROM `" . ScheduleReportProcessed::model()->tableName() . "` t1\n WHERE `t1`.`schedule_id` = '" . $schedule_id . "'";
$total = Yii::app()->db->createCommand($sql)->queryScalar();
}
if ($total || !$page_size) {
if ($page_size) {
$pages = new CPagination($total);
$pages->pageSize = $page_size;
}
$sql = "SELECT \n\t\t\t\t\t\tt1.*, t2.message, t2.measuring_timestamp, t3.report_type \n FROM \n\t\t\t\t\t\t`" . ScheduleReportProcessed::model()->tableName() . "` t1\n LEFT JOIN \n\t\t\t\t\t\t`" . ListenerLog::model()->tableName() . "` t2 ON t2.log_id = t1.listener_log_id\n LEFT JOIN \n\t\t\t\t\t\t`" . ScheduleReport::model()->tableName() . "` t3 ON t3.schedule_id = t1.schedule_id\n WHERE \n\t\t\t\t\t\t`t1`.`schedule_id` = '" . $schedule_id . "'\n ORDER BY \n\t\t\t\t\t\t`t1`.`created` DESC";
if ($page_size) {
$sql .= " LIMIT " . $pages->currentPage * $pages->pageSize . ", " . $pages->pageSize;
}
$res = Yii::app()->db->createCommand($sql)->queryAll();
if ($res) {
$files_path = dirname(Yii::app()->request->scriptFile) . DIRECTORY_SEPARATOR . "files" . DIRECTORY_SEPARATOR . "schedule_reports";
foreach ($res as $key => $value) {
if ($value['serialized_report_errors']) {
$res[$key]['report_errors'] = unserialize($value['serialized_report_errors']);
}
if ($value['serialized_report_explanations']) {
$res[$key]['report_explanations'] = unserialize($value['serialized_report_explanations']);
}
if (in_array($value['report_type'], array('synop', 'metar', 'speci')) && file_exists($files_path . DIRECTORY_SEPARATOR . $value['schedule_processed_id'])) {
$res[$key]['report_string_initial'] = file_get_contents($files_path . DIRECTORY_SEPARATOR . $value['schedule_processed_id']);
}
if ($value['listener_log_ids']) {
$sql = "SELECT t2.log_id, t2.message, t2.measuring_timestamp \n FROM `" . ListenerLog::model()->tableName() . "` t2\n WHERE t2.log_id IN (" . $value['listener_log_ids'] . ")\n ORDER BY `t2`.`measuring_timestamp` DESC";
$res[$key]['logs'] = Yii::app()->db->createCommand($sql)->queryAll();
}
}
}
}
return array('list' => $res, 'pages' => $pages);
}
public function run($args)
{
if (!Yii::app()->mutex->lock('ScheduleCommand', 3600)) {
Yii::app()->end();
}
$synchronization = new Synchronization();
if (!$synchronization->isMaster() and $synchronization->isProcessed()) {
return;
}
$generationTime = time();
$proper_periods = ScheduleCommand::getProperPeriods($generationTime);
if (count($proper_periods) === 0) {
self::$_logger->log(__METHOD__ . ' Exiting. No proper periods found.' . "\n\n");
// Yii::app()->mutex->unlock();
// Yii::app()->end();
}
$criteria = new CDbCriteria();
$criteria->select = array('schedule_id', 'report_type', 'station_id', 'report_format', 'period', 'last_scheduled_run_planned', 'last_scheduled_run_fact', '(`last_scheduled_run_planned` + INTERVAL `period` MINUTE) AS nextScheduleTime', 'UNIX_TIMESTAMP(`last_scheduled_run_planned` + INTERVAL `period` MINUTE) AS nextScheduleUnixTime');
$criteria->with = array('station');
$criteria->compare('period', '>0');
$criteria->compare('period', $proper_periods);
$criteria->addCondition('(UNIX_TIMESTAMP(`last_scheduled_run_planned` + INTERVAL `period` MINUTE) <= UNIX_TIMESTAMP()
OR
`last_scheduled_run_planned` = "0000-00-00 00:00:00")');
/** @var array|ScheduleReport[] $scheduledReports */
$scheduledReports = ScheduleReport::model()->findAll($criteria);
if (count($scheduledReports) === 0) {
self::$_logger->log(__METHOD__ . ' Exiting. No proper reports found.' . "\n\n");
Yii::app()->mutex->unlock();
Yii::app()->end();
}
self::$_logger->log(__METHOD__ . ' New scheduled reports', array('report count' => count($scheduledReports)));
$reportProcesses = array();
foreach ($scheduledReports as $scheduledReport) {
self::$_logger->log("\n");
self::$_logger->log(__METHOD__ . ' Check scheduled report', array('schedule_id' => $scheduledReport->schedule_id));
$check_period = ScheduleCommand::getCheckPeriod($generationTime, $scheduledReport->period);
if ($scheduledReport->report_type === 'data_export') {
self::$_logger->log(__METHOD__ . ' scheduledReport->report_type = data_export');
// add record about schedule running to process afterwards
for ($i = 0; $i < count($scheduledReport->station); $i++) {
$schedule_report_process = new ScheduleReportProcessed();
$schedule_report_process->sr_to_s_id = $scheduledReport->station[$i]->id;
$schedule_report_process->check_period_start = $check_period[3];
$schedule_report_process->check_period_end = $check_period[4];
$schedule_report_process->save();
$scheduledReport->last_scheduled_run_fact = $check_period[1];
$scheduledReport->last_scheduled_run_planned = $check_period[2];
if ($scheduledReport->validate()) {
$scheduledReport->save(false);
} else {
self::$_logger->log(__METHOD__ . ' Schedule report not saved ', array('schedule_error' => $scheduledReport->getErrors()));
}
$reportProcesses[$scheduledReport->station[$i]->id] = array('schedule_id' => $scheduledReport->schedule_id, 'schedule_processed_id' => $schedule_report_process->schedule_processed_id, 'schedule_info' => $scheduledReport, 'check_period_start' => $check_period[3], 'check_period_end' => $check_period[4]);
}
} else {
$logRecords = array();
foreach ($scheduledReport->station as $station) {
$criteria = new CDbCriteria();
$criteria->compare('station_id', $station->station_id);
$criteria->compare('failed', '0');
$criteria->compare('measuring_timestamp', '>' . $check_period[0]);
$criteria->compare('measuring_timestamp', '<=' . $check_period[1]);
$criteria->order = 'measuring_timestamp desc, log_id desc';
$criteria->limit = '1';
$logRecord = ListenerLog::model()->find($criteria);
if (!is_null($logRecord)) {
$logRecords[] = $logRecord;
}
}
// add record about schedule running to process afterwards (only in case system received base message)
if (count($logRecords) > 0) {
for ($i = 0; $i < count($scheduledReport->station); $i++) {
$schedule_report_process = new ScheduleReportProcessed();
$continue = false;
foreach ($logRecords as $logRecord) {
if ($logRecord->station_id == $scheduledReport->station[$i]->station_id) {
$schedule_report_process->listener_log_id = $logRecord->log_id;
$listener_log_id = $logRecord->log_id;
$continue = false;
break;
} else {
$continue = true;
}
}
if ($continue) {
continue;
}
$schedule_report_process->sr_to_s_id = $scheduledReport->station[$i]->id;
$schedule_report_process->check_period_start = $check_period[3];
$schedule_report_process->check_period_end = $check_period[4];
$schedule_report_process->save();
$scheduledReport->last_scheduled_run_fact = $check_period[1];
$scheduledReport->last_scheduled_run_planned = $check_period[2];
if ($scheduledReport->validate()) {
$scheduledReport->save(false);
} else {
self::$_logger->log(__METHOD__ . ' Schedule report not saved ', array('schedule_error' => $scheduledReport->getErrors()));
}
$reportProcesses[$scheduledReport->station[$i]->id] = array('log_id' => $listener_log_id, 'schedule_id' => $scheduledReport->schedule_id, 'schedule_processed_id' => $schedule_report_process->schedule_processed_id, 'schedule_info' => $scheduledReport, 'check_period_start' => $check_period[3], 'check_period_end' => $check_period[4]);
}
}
}
}
if (count($reportProcesses) > 0) {
$total = count($reportProcesses);
$i = 1;
foreach ($reportProcesses as $reportProcess) {
$weatherReport = null;
switch (strtolower($reportProcess['schedule_info']->report_type)) {
case 'synop':
$weatherReport = WeatherReport::create('Synop', self::$_logger);
break;
case 'bufr':
$weatherReport = WeatherReport::create('Bufr', self::$_logger);
break;
case 'metar':
$weatherReport = WeatherReport::create('Metar', self::$_logger);
break;
case 'odss':
$weatherReport = WeatherReport::create('ODSS', self::$_logger);
break;
default:
$weatherReport = WeatherReport::create('Export', self::$_logger);
break;
}
try {
$weatherReport->load($reportProcess['schedule_processed_id']);
$weatherReport->generate();
$weatherReport->saveProcess();
$weatherReport->deliverReport();
} catch (Exteption $e) {
self::$_logger->log(__METHOD__ . ' Error ', array('err' => $e->getMessage()));
}
self::$_logger->log(__METHOD__ . ' Completed', array('num' => $i++, 'total' => $total));
}
}
//send report from sttions together
$scheduleProcessedIdArray_schedule_id = array();
foreach ($reportProcesses as $reportProcess) {
$scheduleProcessedIdArray_schedule_id[$reportProcess['schedule_id']][] = $reportProcess['schedule_processed_id'];
}
foreach ($scheduleProcessedIdArray_schedule_id as $scheduleProcessedIdArray) {
new WeatherReportMailSender($scheduleProcessedIdArray);
}
self::$_logger->log(__METHOD__ . ' Schedule report completed' . "\n\n\n\n\n\n\n\n\n");
Yii::app()->mutex->unlock();
}
public static function getMessageWithTime($station_id, $timestamp, $timestamp_start = '')
{
if (!isset(self::$_messageCache[$station_id][$timestamp][empty($timestamp_start) ? 'empty' : $timestamp_start])) {
$criteria = new CDbCriteria();
$criteria->compare('failed', 0);
$criteria->compare('station_id', $station_id);
$criteria->compare('measuring_timestamp', '<=' . $timestamp);
$criteria->compare('measuring_timestamp', '>' . $timestamp_start);
$criteria->order = 'log_id desc';
$criteria->limit = 1;
self::$_messageCache[$station_id][$timestamp][empty($timestamp_start) ? 'empty' : $timestamp_start] = ListenerLog::model()->find($criteria);
}
return self::$_messageCache[$station_id][$timestamp][empty($timestamp_start) ? 'empty' : $timestamp_start];
}
public function getCalculatedValue($station_id, $last_logs)
{
$last_logs_ids = array();
if (is_array($last_logs)) {
foreach ($last_logs as $value) {
if (count($value) > 0) {
$last_logs_ids[] = $value->log_id;
}
}
}
$last_logs_ids[] = 0;
$sql = "SELECT t1.listener_log_id, t1.value\n FROM `" . StationCalculationData::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 `" . ListenerLog::model()->tableName() . "` t4 ON t4.log_id = t1.listener_log_id\n WHERE `t3`.handler_id_code = ? AND t2.station_id = ? AND t1.listener_log_id IN (" . implode(',', $last_logs_ids) . ")\n ORDER BY t4.measuring_timestamp DESC\n LIMIT 2 ";
$res = Yii::app()->db->createCommand($sql)->queryAll(true, array($this->handler_id_code, $station_id));
$return = array('last' => '-', 'change' => 'no', 'metric_html_code' => $this->metric_html_code, 'display_name' => $this->display_name);
if (!$res) {
return $return;
}
if ($res[0]['listener_log_id'] && $res[0]['listener_log_id'] == $last_logs_ids[0]) {
$return['last'] = number_format(round($res[0]['value'], 1), 1);
if ($res[1]['listener_log_id']) {
if ($res[0]['value'] > $res[1]['value']) {
$return['change'] = 'up';
} else {
if ($res[0]['value'] < $res[1]['value']) {
$return['change'] = 'down';
}
}
}
}
return $return;
}
public function prepareList($page_size = 10)
{
$stations = $this->getAllStations();
if ($stations) {
$sql_where = array();
//---------------- Start groupping
$use_field = '';
if ($this->rate_volume == 1) {
$use_field = 'sensor_value';
} else {
if ($this->rate_volume == 5) {
$use_field = '5min_sum';
$tmp = array('00', '05', '10', '15', '20', '25', '30', '35', '40', '45', '50', '55');
} else {
if ($this->rate_volume == 10) {
$use_field = '10min_sum';
$tmp = array('00', '10', '20', '30', '40', '50');
} else {
if ($this->rate_volume == 20) {
$use_field = '20min_sum';
$tmp = array('00', '20', '40');
} else {
if ($this->rate_volume == 30) {
$use_field = '30min_sum';
$tmp = array('00', '30');
} else {
if ($this->rate_volume == 60) {
$use_field = '60min_sum';
$tmp = array('00');
}
}
}
}
}
$sql_where[] = "DATE_FORMAT(`sd`.`measuring_timestamp`, '%i') IN ('" . implode("','", $tmp) . "')";
}
$sql_where[] = "`sd`.`" . $use_field . "` > 0";
//---------------- End groupping
//---------------- Start date filter
if ($this->date_from) {
$sql_where[] = "`sd`.`measuring_timestamp` >= '" . date('Y-m-d H:i:s', strtotime($this->date_from . ' ' . $this->time_from)) . "'";
}
if ($this->date_to) {
$sql_where[] = "`sd`.`measuring_timestamp` <= '" . date('Y-m-d H:i:s', strtotime($this->date_to . ' ' . $this->time_to)) . "'";
}
//---------------- End date filter
//---------------- Start Station filter
if ($this->station_id) {
$sql_where[] = "`sd`.`station_id` = '" . $this->station_id . "'";
} else {
$sql_groupped_table = "SELECT `sensor_id`, MAX(`measuring_timestamp`) AS `MaxDateTime` FROM `" . SensorDataMinute::model()->tableName() . "` WHERE `" . $use_field . "` > 0 ";
if ($this->date_from) {
$sql_groupped_table .= " AND `measuring_timestamp` >= '" . date('Y-m-d H:i:s', strtotime($this->date_from . ' ' . $this->time_from)) . "' ";
}
if ($this->date_to) {
$sql_groupped_table .= " AND `measuring_timestamp` <= '" . date('Y-m-d H:i:s', strtotime($this->date_to . ' ' . $this->time_to)) . "' ";
}
$sql_groupped_table .= " GROUP BY `sensor_id` ";
$sql = "SELECT `tt`.`sensor_data_id`\n FROM `" . SensorDataMinute::model()->tableName() . "` `tt`\n INNER JOIN ( {$sql_groupped_table} ) `groupedtt` ON `tt`.`sensor_id` = `groupedtt`.`sensor_id` AND `tt`.`measuring_timestamp` = `groupedtt`.`MaxDateTime`";
$last_values = CStubActiveRecord::getDbConnect(true)->createCommand($sql)->queryColumn();
if (!$last_values) {
$last_values = array(0);
}
$sql_where[] = "`sd`.`station_id` IN (" . implode(',', array_keys($stations)) . ") AND `sd`.`sensor_data_id` IN (" . implode(',', $last_values) . ")";
}
//---------------- End Station filter
if ($page_size > 0) {
$sql = "SELECT COUNT(*)\n FROM `" . SensorDataMinute::model()->tableName() . "` `sd`\n WHERE " . implode(' AND ', $sql_where);
$total = CStubActiveRecord::getDbConnect(true)->createCommand($sql)->queryScalar();
$pages = new CPagination($total);
$pages->pageSize = $page_size;
//$pages->applyLimit($criteria);
}
if ($this->order_field == 'date') {
$sql_order = "`sd`.`measuring_timestamp` " . $this->order_direction;
} elseif ($this->order_field == 'name') {
$sql_order = "`st`.`display_name` " . $this->order_direction;
} elseif ($this->order_field == 'lasttx') {
$sql_order = "`sd`.`" . $use_field . "` " . $this->order_direction;
} elseif ($this->order_field == 'lasthr') {
$sql_order = "`sd`.`60min_sum` " . $this->order_direction;
} elseif ($this->order_field == 'last24hr') {
$sql_order = "`sd`.`1day_sum` " . $this->order_direction;
}
$sql = "SELECT `st`.`display_name`,\n `st`.`station_id_code`,\n `st`.`station_id`,\n\n `ll`.`message`,\n `ll`.`log_id`,\n\n `sd`.`sensor_data_id`,\n `sd`.`battery_voltage`,\n `sd`.`sensor_id`,\n `sd`.`measuring_timestamp`,\n DATE_FORMAT(`sd`.`measuring_timestamp`, '%m/%d/%Y') AS `tx_date_formatted`,\n DATE_FORMAT(`sd`.`measuring_timestamp`, '%H:%i') AS `tx_time_formatted`,\n `sd`.`sensor_value`,\n `sd`.`5min_sum`,\n `sd`.`10min_sum`,\n `sd`.`20min_sum`,\n `sd`.`30min_sum`,\n `sd`.`60min_sum`,\n `sd`.`1day_sum`,\n\n `sd`.`bucket_size`,\n `sd`.`1day_sum` AS `day_value_mm`,\n `sd`.`60min_sum` AS `hour_value_mm`\n\n FROM `" . SensorDataMinute::model()->tableName() . "` `sd`\n LEFT JOIN `" . ListenerLog::model()->tableName() . "` `ll` ON `sd`.`listener_log_id` = `ll`.`log_id`\n LEFT JOIN `" . Station::model()->tableName() . "` `st` ON `st`.`station_id` = `sd`.`station_id`\n\n WHERE " . implode(' AND ', $sql_where) . "\n ORDER BY {$sql_order} ";
if ($page_size) {
$sql .= " LIMIT " . $pages->currentPage * $pages->pageSize . ", " . $pages->pageSize;
}
$res = CStubActiveRecord::getDbConnect(true)->createCommand($sql)->queryAll();
if ($res) {
$total_found = count($res);
foreach ($res as $key => $value) {
$res[$key]['battery_voltage_formatted'] = $value['battery_voltage'] / 10;
$res[$key]['tx_value_mm'] = $value[$use_field] * $value['bucket_size'];
$res[$key]['tx_value_rate_mm'] = $value[$use_field] * $value['bucket_size'] * 60 / $this->rate_volume;
$res[$key]['day_value_mm'] = $value['day_value_mm'] * $value['bucket_size'];
$res[$key]['hour_value_mm'] = $value['hour_value_mm'] * $value['bucket_size'];
$res[$key]['period'] = $this->rate_volume;
$hour_value_id = date('YmdH', strtotime($value['measuring_timestamp']));
$res[$key]['hour_value_id'] = $hour_value_id;
$res[$key]['hour_value_rate_mm'] = 0;
if ($stations[$value['station_id']]['filter_limit_max'] > 0) {
if ($res[$key]['tx_value_mm'] >= $stations[$value['station_id']]['filter_limit_max']) {
$res[$key]['filter_errors'][] = "R >= <b>" . $stations[$value['station_id']]['filter_limit_max'] . "</b> ";
}
}
if ($stations[$value['station_id']]['filter_limit_min'] > 0) {
if ($res[$key]['tx_value_mm'] <= $stations[$value['station_id']]['filter_limit_min']) {
$res[$key]['filter_errors'][] = "R <= <b>" . $stations[$value['station_id']]['filter_limit_min'] . "</b> ";
}
}
if ($stations[$value['station_id']]['filter_limit_diff'] > 0) {
if ($key != 0 && abs($res[$key]['tx_value_mm'] - $res[$key - 1]['tx_value_mm']) >= $stations[$value['station_id']]['filter_limit_diff']) {
$res[$key]['filter_errors'][] = "|R - R0| >= <b>" . $stations[$value['station_id']]['filter_limit_diff'] . "</b> ";
}
}
}
foreach ($res as $key => $value) {
if ($key != 0 && $res[$key]['hour_value_id'] != $res[$key - 1]['hour_value_id']) {
$res[$key - 1]['hour_value_rate_mm'] = $res[$key - 1]['hour_value_mm'];
} elseif ($key == $total_found - 1) {
$res[$key]['hour_value_rate_mm'] = $res[$key]['hour_value_mm'];
}
}
}
}
return array('list' => $res, 'pages' => $pages);
}
protected function _prepareListenerLogInfo()
{
$this->_logger->log(__METHOD__);
if (!is_null($this->schedule_process_info)) {
$criteria = new CDbCriteria();
$criteria->compare('station_id', $this->schedule_info->station_id);
$criteria->compare('failed', '0');
$criteria->compare('measuring_timestamp', '>=' . $this->schedule_process_info->check_period_start);
$criteria->compare('measuring_timestamp', '<=' . $this->schedule_process_info->check_period_end);
$criteria->order = 'measuring_timestamp desc, log_id desc';
$this->_logger->log(__METHOD__ . 'measuring_timestamp' . '>=' . $this->schedule_process_info->check_period_start);
$this->_logger->log(__METHOD__ . 'measuring_timestamp' . '<=' . $this->schedule_process_info->check_period_end);
if (in_array($this->schedule_info->report_type, array('bufr', 'synop', 'metar', 'speci'))) {
$criteria->limit = '1';
$logRecord = ListenerLog::model()->find($criteria);
// $this->schedule_process_info->listener_log_id = is_null($logRecord) ? 0 : $logRecord->log_id;
// $this->schedule_process_info->save();
$this->listener_log_info = $logRecord;
$this->_logger->log(__METHOD__ . ' $this->listener_log_info' . print_r($this->listener_log_info, 1));
if (is_null($this->listener_log_info)) {
$this->errors[] = 'Listener log info (#' . $this->schedule_process_info->listener_log_id . ') can not be found';
return false;
}
} else {
$logRecords = ListenerLog::model()->findAll($criteria);
if (count($logRecords) > 0) {
$logIds = array();
foreach ($logRecords as $logRecord) {
$logIds[] = $logRecord->log_id;
}
$this->listener_log_info = $logRecords;
$this->schedule_process_info->listener_log_ids = implode(',', $logIds);
$this->schedule_process_info->save();
} else {
$this->listener_log_info = null;
$this->schedule_process_info->listener_log_ids = '';
$this->schedule_process_info->save();
$this->errors[] = 'Listener log info (#' . $this->schedule_process_info->listener_log_id . ') can not be found';
return false;
}
}
}
return true;
}
public function actionScheduleStationHistory()
{
$station_to_report_id = isset($_REQUEST['station_to_report_id']) ? intval($_REQUEST['station_to_report_id']) : null;
if (!$station_to_report_id) {
$this->redirect($this->createUrl('site/schedule'));
}
$criteria = new CDbCriteria();
$criteria->condition = 'sr_to_s_id = :sr_to_s_id';
$criteria->params = array(':sr_to_s_id' => $station_to_report_id);
$criteria->order = 'schedule_processed_id DESC';
$count = ScheduleReportProcessed::model()->count($criteria);
$pages = new CPagination($count);
$pages->pageSize = 15;
$pages->applyLimit($criteria);
$scheduleProcessed = ScheduleReportProcessed::model()->with('listenerLog', 'ScheduleReportToStation.schedule_report', 'ScheduleReportToStation.realStation')->findAll($criteria);
$files_path = dirname(Yii::app()->request->scriptFile) . DIRECTORY_SEPARATOR . "files" . DIRECTORY_SEPARATOR . "schedule_reports";
$scheduleProcessedFormatted = array();
foreach ($scheduleProcessed as $key => $value) {
$scheduleProcessedFormatted[$key] = $value->attributes;
$scheduleProcessedFormatted[$key]['report_type'] = $value->ScheduleReportToStation['schedule_report']['report_type'];
$scheduleProcessedFormatted[$key]['report_format'] = $value->ScheduleReportToStation['schedule_report']['report_format'];
$scheduleProcessedFormatted[$key]['station_id'] = $value->ScheduleReportToStation->station_id;
$scheduleProcessedFormatted[$key]['measuring_timestamp'] = $value->listenerLog['measuring_timestamp'];
$scheduleProcessedFormatted[$key]['message'] = $value->listenerLog->message;
if ($scheduleProcessedFormatted[$key]['serialized_report_errors']) {
$scheduleProcessedFormatted[$key]['report_errors'] = unserialize($scheduleProcessedFormatted[$key]['serialized_report_errors']);
}
if ($scheduleProcessedFormatted[$key]['serialized_report_explanations']) {
$scheduleProcessedFormatted[$key]['report_explanations'] = unserialize($scheduleProcessedFormatted[$key]['serialized_report_explanations']);
}
if (in_array($value->ScheduleReportToStation['schedule_report']['report_type'], array('synop', 'metar', 'speci')) && file_exists($files_path . DIRECTORY_SEPARATOR . $scheduleProcessedFormatted[$key]['schedule_processed_id'])) {
$scheduleProcessedFormatted[$key]['report_string_initial'] = file_get_contents($files_path . DIRECTORY_SEPARATOR . $scheduleProcessedFormatted[$key]['schedule_processed_id']);
}
if ($scheduleProcessedFormatted[$key]['listener_log_ids']) {
$sql = "SELECT t2.log_id, t2.message, t2.measuring_timestamp\n FROM `" . ListenerLog::model()->tableName() . "` t2\n WHERE t2.log_id IN (" . $scheduleProcessedFormatted[$key]['listener_log_ids'] . ")\n ORDER BY `t2`.`measuring_timestamp` DESC";
$scheduleProcessedFormatted[$key]['logs'] = Yii::app()->db->createCommand($sql)->queryAll();
}
}
$reportInfo = $scheduleProcessed[0]->ScheduleReportToStation['schedule_report']->attributes;
$stationInfo = $scheduleProcessed[0]->ScheduleReportToStation->realStation->attributes;
$this->render('schedule_report_history', array('scheduleProcessed' => $scheduleProcessedFormatted, 'stationInfo' => $stationInfo, 'reportInfo' => $reportInfo, 'pages' => $pages));
}
public function prepareList($station_id = 0)
{
if ($this->hasErrors()) {
return ['prepared_header' => [], 'prepared_data' => []];
}
$prepared_header = array();
$prepared_data = array();
$sensor_feature_code = $this->getSensorFeatureCode();
$handler_code = array_keys($this->getSelectedGroupSensorFeatureCode());
$search_features = array();
$search_calcs = array();
foreach ($sensor_feature_code as $key) {
if (in_array($key, array_keys($this->calc_handlers))) {
$search_calcs[] = $this->calc_handlers[$key];
} else {
$search_features[] = $key;
}
}
if (count($this->station_id) > 1) {
$sql_part = "`t2`.`station_id` IN (" . implode(',', $this->station_id) . ") ";
} else {
$sql_part = "`t2`.`station_id` = '" . $this->station_id[0] . "' ";
}
// 1.a) GET FEATURES
if (count($search_features) > 0) {
$sql = "SELECT `t1`.`sensor_feature_id`,\n `t1`.`feature_code`,\n `t1`.`sensor_id`,\n `t3`.`station_id_code`,\n `t2`.`sensor_id_code`,\n `t2`.`station_id`,\n `t4`.`handler_id_code`,\n `t3`.`magnetic_north_offset`\n FROM `" . StationSensorFeature::model()->tableName() . "` `t1`\n LEFT JOIN `" . StationSensor::model()->tableName() . "` `t2` ON `t1`.`sensor_id` = `t2`.`station_sensor_id`\n LEFT JOIN `" . SensorDBHandler::model()->tableName() . "` `t4` ON `t4`.`handler_id` = `t2`.`handler_id`\n LEFT JOIN `" . Station::model()->tableName() . "` `t3` ON `t3`.`station_id` = `t2`.`station_id`\n WHERE " . $sql_part . " AND `t1`.`feature_code` IN ('" . implode("','", $search_features) . "') AND `t4`.`handler_id_code` IN ('" . implode("','", $handler_code) . "')\n ORDER BY `t1`.`feature_code`, `t3`.`station_id_code`, `t2`.`sensor_id_code`";
$found_sensors = CStubActiveRecord::getDbConnect(true)->createCommand($sql)->queryAll();
$total_found_sensors = count($found_sensors);
}
// 1.b) GET CALCS
if (count($search_calcs) > 0) {
$sql = "SELECT `t1`.`calculation_id`,\n `t1`.`handler_id`,\n `t2`.`station_id_code`,\n `t2`.`station_id`,\n IF(`t1`.`handler_id` = 1, 'DP', 'MSL') AS `sensor_id_code`,\n IF(`t1`.`handler_id` = 1, 'Dew Point', 'Pressure MSL') AS `feature_code`,\n IF(`t1`.`handler_id` = 1, 'DewPoint', 'PressureSeaLevel') AS `handler_id_code`\n FROM `" . StationCalculation::model()->tableName() . "` `t1`\n LEFT JOIN `" . Station::model()->tableName() . "` `t2` ON `t2`.`station_id` = `t1`.`station_id`\n WHERE " . $sql_part . " AND `t1`.`handler_id` IN (" . implode(',', $search_calcs) . ")\n ORDER BY `t1`.`handler_id`, `t2`.`station_id_code`";
$found_calcs = CStubActiveRecord::getDbConnect(true)->createCommand($sql)->queryAll();
$total_found_calcs = count($found_calcs);
}
$start_datetime = strtotime($this->date_from . ' ' . $this->time_from);
$end_datetime = strtotime($this->date_to . ' ' . $this->time_to);
$features_set = array();
// 2.a) PREPARE HEADER
if (is_array($found_sensors) && $total_found_sensors > 0) {
$sensor_feature_ids = array();
for ($i = 0; $i < $total_found_sensors; $i++) {
$key = $found_sensors[$i]['handler_id_code'] . $found_sensors[$i]['feature_code'];
if (!isset($prepared_header[$key])) {
$prepared_header[$key] = array('sensor_feature_code' => $found_sensors[$i]['feature_code'], 'handler_id_code' => $found_sensors[$i]['handler_id_code'], 'sensors' => array(), 'station_sensors' => array());
}
$sensor_feature_ids[] = $found_sensors[$i]['sensor_feature_id'];
$prepared_header[$key]['sensors'][] = array('station_id' => $found_sensors[$i]['station_id'], 'sensor_id_code' => $found_sensors[$i]['sensor_id_code']);
if (isset($prepared_header[$key]['station_sensors'][$found_sensors[$i]['station_id']])) {
$prepared_header[$key]['station_sensors'][$found_sensors[$i]['station_id']]++;
} else {
$prepared_header[$key]['station_sensors'][$found_sensors[$i]['station_id']] = 1;
}
$features_set[$found_sensors[$i]['sensor_feature_id']] = array('station_id' => $found_sensors[$i]['station_id'], 'station_id_code' => $found_sensors[$i]['station_id_code'], 'value' => '-', 'sensor_id' => $found_sensors[$i]['sensor_id'], 'sensor_id_code' => $found_sensors[$i]['sensor_id_code'], 'sensor_feature_code' => $found_sensors[$i]['feature_code'], 'handler_id_code' => $found_sensors[$i]['handler_id_code'], 'magnetic_north_offset' => $found_sensors[$i]['magnetic_north_offset']);
}
}
// 2.b) PREPARE HEADER
if (count($search_calcs) > 0) {
$sql = "SELECT `t1`.`calculation_id`,\n `t1`.`handler_id`,\n `t2`.`station_id_code`,\n `t2`.`station_id`,\n IF(`t1`.`handler_id` = 1, 'DP', 'MSL') AS `sensor_id_code`,\n IF(`t1`.`handler_id` = 1, 'Dew Point', 'Pressure MSL') AS `feature_code`,\n IF(`t1`.`handler_id` = 1, 'DewPoint', 'PressureSeaLevel') AS `handler_id_code`\n FROM `" . StationCalculation::model()->tableName() . "` `t1`\n LEFT JOIN `" . Station::model()->tableName() . "` `t2` ON `t2`.`station_id` = `t1`.`station_id`\n WHERE " . $sql_part . " AND `t1`.`handler_id` IN (" . implode(',', $search_calcs) . ")\n ORDER BY `t1`.`handler_id`, `t2`.`station_id`";
$found_calcs = CStubActiveRecord::getDbConnect(true)->createCommand($sql)->queryAll();
if (is_array($found_calcs) && count($found_calcs) > 0) {
$calculation_ids = array();
for ($i = 0; $i < count($found_calcs); $i++) {
$key = 'calc_' . $found_calcs[$i]['handler_id'];
if (!isset($prepared_header[$key])) {
$prepared_header[$key] = array('sensor_feature_code' => $key, 'sensors' => array(), 'station_sensors' => array());
}
$calculation_ids[] = $found_calcs[$i]['calculation_id'];
$prepared_header[$key]['sensors'][] = array('station_id' => $found_calcs[$i]['station_id'], 'sensor_id_code' => $found_calcs[$i]['sensor_id_code']);
if (isset($prepared_header[$key]['station_sensors'][$found_calcs[$i]['station_id']])) {
$prepared_header[$key]['station_sensors'][$found_calcs[$i]['station_id']]++;
} else {
$prepared_header[$key]['station_sensors'][$found_calcs[$i]['station_id']] = 1;
}
$features_set['calc_' . $found_calcs[$i]['calculation_id']] = array('station_id' => $found_calcs[$i]['station_id'], 'station_id_code' => $found_calcs[$i]['station_id_code'], 'value' => '-', 'calculation_id' => $found_calcs[$i]['calculation_id'], 'sensor_id_code' => $found_calcs[$i]['sensor_id_code'], 'sensor_feature_code' => $found_calcs[$i]['feature_code'], 'handler_id_code' => $found_calcs[$i]['handler_id_code']);
}
}
}
// 3.a) PREPARE DATA
if (is_array($found_sensors) && $total_found_sensors) {
$qb = new CDbCriteria();
$qb->select = ['sensor_data_id', 'station_id', 'sensor_id', 'sensor_feature_id', 'sensor_feature_normalized_value', 'is_m', 'measuring_timestamp'];
$qb->addInCondition('sensor_feature_id', $sensor_feature_ids);
$qb->addBetweenCondition('measuring_timestamp', date('Y-m-d H:i:s', $start_datetime), date('Y-m-d H:i:s', $end_datetime));
$qb->order = 'measuring_timestamp DESC';
$found_values = SensorData::model()->long()->findAll($qb);
$total_found_values = count($found_values);
if (is_array($found_values) && $total_found_values > 0) {
if ($this->accumulation_period == 0) {
for ($j = 0; $j < $total_found_values; $j++) {
$f_id = $found_values[$j]['sensor_feature_id'];
$f_time = $found_values[$j]['measuring_timestamp'];
$f_code = $features_set[$f_id]['sensor_feature_code'];
$magnetic_north_offset = $features_set[$f_id]['magnetic_north_offset'];
$st_id = $found_values[$j]['station_id'];
if (!isset($prepared_data[$f_time])) {
$prepared_data[$f_time] = array();
$prepared_data[$f_time]['stations'] = array();
}
if (!isset($prepared_data[$f_time]['data'])) {
$prepared_data[$f_time]['data'] = $features_set;
}
$handler_obj = SensorHandler::create($features_set[$f_id]['handler_id_code']);
if ($found_values[$j]['is_m'] == 1) {
$prepared_data[$f_time]['data'][$f_id]['value'] = '-';
} else {
$found_values[$j]['sensor_feature_normalized_value'] = $handler_obj->applyOffset($found_values[$j]['sensor_feature_normalized_value'], $magnetic_north_offset);
$prepared_data[$f_time]['data'][$f_id]['value'] = $handler_obj->formatValue($found_values[$j]['sensor_feature_normalized_value'], $f_code);
}
if (!in_array($st_id, $prepared_data[$f_time]['stations'])) {
$prepared_data[$f_time]['stations'][] = $st_id;
}
}
} else {
for ($j = 0; $j < $total_found_values; $j++) {
$f_id = $found_values[$j]['sensor_feature_id'];
$f_time = $found_values[$j]['measuring_timestamp'];
$f_code = $features_set[$f_id]['sensor_feature_code'];
$magnetic_north_offset = $features_set[$f_id]['magnetic_north_offset'];
$st_id = $found_values[$j]['station_id'];
$period = $start_datetime + (intval((strtotime($f_time) - $start_datetime) / ($this->accumulation_period * 60)) + 1) * $this->accumulation_period * 60;
$period = $period > $end_datetime ? $end_datetime : $period;
$period = date('Y-m-d H:i:s', $period);
if (!isset($prepared_data[$period])) {
$prepared_data[$period] = array();
$prepared_data[$period]['stations'] = array();
}
if (!isset($prepared_data[$period]['data'])) {
$prepared_data[$period]['data'] = $features_set;
}
$handler_obj = SensorHandler::create($features_set[$f_id]['handler_id_code']);
if ($found_values[$j]['is_m'] == 1) {
$prepared_data[$period]['data'][$f_id]['value'] = '-';
} else {
$found_values[$j]['sensor_feature_normalized_value'] = $handler_obj->applyOffset($found_values[$j]['sensor_feature_normalized_value'], $magnetic_north_offset);
$prepared_data[$period]['data'][$f_id]['value'] = ($prepared_data[$period]['data'][$f_id]['value'] ? $prepared_data[$period]['data'][$f_id]['value'] : 0) + $handler_obj->formatValue($found_values[$j]['sensor_feature_normalized_value'], $f_code);
}
if (!in_array($st_id, $prepared_data[$period]['stations'])) {
$prepared_data[$period]['stations'][] = $st_id;
}
}
}
}
}
// 3.b) PREPARE DATA
if (is_array($found_calcs) && $total_found_calcs > 0) {
$sql = "SELECT `t2`.`station_id`,\n `t1`.`calculation_id`,\n `t1`.`value`,\n `t2`.`measuring_timestamp`\n FROM `" . StationCalculationData::model()->tableName() . "` `t1`\n LEFT JOIN `" . ListenerLog::model()->tableName() . "` `t2` ON `t2`.`log_id` = `t1`.`listener_log_id`\n WHERE `t1`.`calculation_id` IN (" . implode(',', $calculation_ids) . ")\n AND `t2`.`measuring_timestamp` >= '" . date('Y-m-d H:i:s', $start_datetime) . "'\n AND `t2`.`measuring_timestamp` <= '" . date('Y-m-d H:i:s', $end_datetime) . "'\n ORDER BY `t2`.`measuring_timestamp` DESC";
$found_values = CStubActiveRecord::getDbConnect(true)->createCommand($sql)->queryAll();
$total_found_values = count($found_values);
if (is_array($found_values) && $total_found_values) {
for ($j = 0; $j < $total_found_values; $j++) {
$f_id = 'calc_' . $found_values[$j]['calculation_id'];
$f_time = $found_values[$j]['measuring_timestamp'];
$st_id = $found_values[$j]['station_id'];
if (!$prepared_data[$f_time]) {
$prepared_data[$f_time] = array();
$prepared_data[$f_time]['stations'] = array();
}
if (!$prepared_data[$f_time]['data']) {
$prepared_data[$f_time]['data'] = $features_set;
}
$prepared_data[$f_time]['data'][$f_id]['value'] = CalculationHandler::formatValue($found_values[$j]['value']);
$prepared_data[$f_time]['data'][$f_id]['station_id'] = $st_id;
if (!in_array($st_id, $prepared_data[$f_time]['stations'])) {
$prepared_data[$f_time]['stations'][] = $st_id;
}
}
}
}
//need sort
krsort($prepared_data);
//print_r(array(
// 'prepared_header' => $prepared_header,
// 'prepared_data' => $prepared_data,
// ));exit;
return array('prepared_header' => $prepared_header, 'prepared_data' => $prepared_data);
}
public function actionSetup()
{
$criteria = new CDbCriteria();
$criteria->condition = "ord > 0";
$criteria->order = "ord ASC";
$meas_types = RefbookMeasurementType::model()->findAll($criteria);
if ($meas_types) {
foreach ($meas_types as $key => $value) {
$sql = "SELECT `t1`.`metric_id`, CONCAT(`t2`.`html_code`, ' (', `t2`.`full_name`, ')') AS `name`, `t1`.`is_main`, `t1`.`measurement_type_metric_id`\n FROM `" . RefbookMeasurementTypeMetric::model()->tableName() . "` `t1`\n LEFT JOIN `" . RefbookMetric::model()->tableName() . "` `t2` ON `t2`.`metric_id` = `t1`.`metric_id`\n WHERE `t1`.`measurement_type_id` = '" . $value->measurement_type_id . "'";
$meas_types[$key]->metrics_list = Yii::app()->db->createCommand($sql)->queryAll();
}
}
if (Yii::app()->request->isPostRequest && isset($_POST['main_metric'])) {
foreach ($_POST['main_metric'] as $key => $value) {
if ($meas_types[$key]->metrics_list) {
foreach ($meas_types[$key]->metrics_list as $v1) {
$update = array('is_main' => $v1['metric_id'] == $value ? 1 : 0);
RefbookMeasurementTypeMetric::model()->updateByPk($v1['measurement_type_metric_id'], $update);
}
}
}
StationSensorFeature::updateMetric();
$DB = array('db' => CStubActiveRecord::getDbConnect(), 'db_long' => CStubActiveRecord::getDbConnect(true));
foreach ($DB as $db) {
$db->createCommand("DELETE FROM `" . ScheduleReportProcessed::model()->tableName() . "`")->query();
$db->createCommand("DELETE FROM `" . ForwardedMessage::model()->tableName() . "`")->query();
$db->createCommand("DELETE FROM `" . StationCalculationData::model()->tableName() . "`")->query();
$db->createCommand("DELETE FROM `" . SeaLevelTrend::model()->tableName() . "`")->query();
$db->createCommand("DELETE FROM `" . SensorDataMinute::model()->tableName() . "`")->query();
$db->createCommand("DELETE FROM `" . SensorData::model()->tableName() . "`")->query();
$db->createCommand("DELETE FROM `" . ListenerLog::model()->tableName() . "`")->query();
}
It::memStatus('admin_metrics_saved');
$this->redirect($this->createUrl('admin/setup'));
}
$this->render('setup', array('meas_types' => $meas_types));
}
public function m_0_4_1()
{
@apache_setenv('no-gzip', 1);
@ini_set('zlib.output_compression', 0);
@ini_set('implicit_flush', 1);
ini_set('memory_limit', '-1');
//ob_start();
$this->flushNotification('...Please wait... Updating is going on... DO NOT LEAVE THIS PAGE!');
$this->flushNotification('<br/>...Going to add "is_last" fields to `listener_log` table...');
$res = Yii::app()->db->createCommand("SHOW COLUMNS FROM `" . ListenerLog::model()->tableName() . "` LIKE 'is_last'")->queryAll();
if (!$res) {
Yii::app()->db->createCommand("ALTER TABLE `" . ListenerLog::model()->tableName() . "` ADD `is_last` tinyint(1) NOT NULL DEFAULT '0' AFTER `is_processed`")->query();
Yii::app()->db->createCommand("COMMIT")->query();
$stations = Yii::app()->db->createCommand("SELECT * FROM `" . Station::model()->tableName() . "`")->queryAll();
if ($stations) {
foreach ($stations as $key => $value) {
ListenerLog::updateIsLastForStation($value['station_id']);
}
}
$this->flushNotification(' ... done');
} else {
$this->flushNotification(' ... not need');
}
$this->flushNotification('<br/>...Going to add "is_processing" column to `listener_log` table...');
$res = Yii::app()->db->createCommand("SHOW COLUMNS FROM `" . ListenerLog::model()->tableName() . "` LIKE 'is_processing'")->queryAll();
if (!$res) {
Yii::app()->db->createCommand("ALTER TABLE `" . ListenerLog::model()->tableName() . "` ADD `is_processing` TINYINT(1) NOT NULL DEFAULT '0' AFTER `is_processed`")->query();
Yii::app()->db->createCommand("COMMIT")->query();
$this->flushNotification(' ... done');
} else {
$this->flushNotification(' ... not need');
}
// station
$this->flushNotification('<br/><br/>Station table:');
$this->flushNotification('<br/>...Going to update "national_aws_number" field at `station` table...');
$res = Yii::app()->db->createCommand("SHOW COLUMNS FROM `" . Station::model()->tableName() . "` LIKE 'national_aws_number'")->queryAll();
if ($res[0]['Null'] == 'NO') {
Yii::app()->db->createCommand("ALTER TABLE `station` CHANGE `national_aws_number` `national_aws_number` int(11) DEFAULT '0'")->query();
Yii::app()->db->createCommand("COMMIT")->query();
$this->flushNotification(' ... done');
} else {
$this->flushNotification(' ... not need');
}
$this->flushNotification('<br/>...Going to add new column "country_id" to `station` table...');
$res = Yii::app()->db->createCommand("SHOW COLUMNS FROM `" . Station::model()->tableName() . "` LIKE 'country_id'")->queryAll();
if (!$res) {
Yii::app()->db->createCommand("ALTER TABLE `station` ADD `country_id` int(11) NOT NULL DEFAULT '0' AFTER `magnetic_north_offset`")->query();
Yii::app()->db->createCommand("COMMIT")->query();
$this->flushNotification(' ... done');
} else {
$this->flushNotification(' ... already exists');
}
$this->flushNotification('<br/>...Going to add new column "city_id" to `station` table...');
$res = Yii::app()->db->createCommand("SHOW COLUMNS FROM `" . Station::model()->tableName() . "` LIKE 'city_id'")->queryAll();
if (!$res) {
Yii::app()->db->createCommand("ALTER TABLE `station` ADD `city_id` int(11) NOT NULL DEFAULT '0' AFTER `country_id`")->query();
Yii::app()->db->createCommand("COMMIT")->query();
$this->flushNotification(' ... done');
} else {
$this->flushNotification(' ... already exists');
}
$this->flushNotification('<br/>...Going to add new column "timezone_offset" to `station` table...');
$res = Yii::app()->db->createCommand("SHOW COLUMNS FROM `" . Station::model()->tableName() . "` LIKE 'timezone_offset'")->queryAll();
if (!$res) {
Yii::app()->db->createCommand("ALTER TABLE `station` ADD `timezone_offset` varchar(20) NOT NULL AFTER `timezone_id`")->query();
Yii::app()->db->createCommand("COMMIT")->query();
$this->flushNotification(' ... done');
} else {
$this->flushNotification(' ... already exists');
}
$this->flushNotification('<br/>...Going to update "timezone_offset" data in `station` table...');
$sql = "SELECT `station_id`, `timezone_id` FROM `" . Station::model()->tableName() . "` WHERE `timezone_offset` = ''";
$res = Yii::app()->db->createCommand($sql)->queryAll();
if ($res) {
foreach ($res as $key => $value) {
$sql = "UPDATE `" . Station::model()->tableName() . "` SET `timezone_offset` = '" . TimezoneWork::getOffsetFromUTC($value['timezone_id'], 1) . "' WHERE `station_id` = '" . $value['station_id'] . "'";
Yii::app()->db->createCommand($sql)->query();
}
Yii::app()->db->createCommand("COMMIT")->query();
$this->flushNotification(' ... done');
} else {
$this->flushNotification(' ... not need');
}
$this->flushNotification('<br/>...Going to add new column "awos_msg_source_folder" to `station` table...');
$res = Yii::app()->db->createCommand("SHOW COLUMNS FROM `" . Station::model()->tableName() . "` LIKE 'awos_msg_source_folder'")->queryAll();
if (!$res) {
Yii::app()->db->createCommand("ALTER TABLE `station` ADD `awos_msg_source_folder` TEXT CHARACTER SET ucs2 COLLATE ucs2_general_ci NOT NULL AFTER `city_id`")->query();
Yii::app()->db->createCommand("COMMIT")->query();
$this->flushNotification(' ... done');
} else {
$this->flushNotification(' ... already exists');
}
// Sensor Data
$this->flushNotification('<br/>...Going to update `sensor_data` table\'s data...');
Yii::app()->db->createCommand("UPDATE `sensor_data` SET `period` = 1440 WHERE `period` = 86400")->query();
Yii::app()->db->createCommand("COMMIT")->query();
$this->flushNotification(' ... done');
//Schedule report
$this->flushNotification('<br/>...Going to create `schedule_report` table...');
$tables = array();
$res = Yii::app()->db->createCommand("SHOW TABLES")->queryAll();
if ($res) {
foreach ($res as $key => $value) {
foreach ($value as $k1 => $v1) {
$tables[] = $v1;
}
}
}
if (!in_array('schedule_report', $tables)) {
$sql = "CREATE TABLE `schedule_report` (\n `schedule_id` int(11) NOT NULL AUTO_INCREMENT,\n `report_type` varchar(50) NOT NULL DEFAULT 'synop' COMMENT 'synop, bufr',\n `station_id` int(11) NOT NULL,\n `period` int(11) NOT NULL DEFAULT '60' COMMENT 'in minutes',\n `method` varchar(20) NOT NULL DEFAULT 'email' COMMENT 'email, ftp',\n `destination_email` varchar(255) NOT NULL,\n `destination_ip` varchar(15) NOT NULL,\n `destination_ip_port` int(5) NOT NULL DEFAULT '21',\n `destination_ip_folder` varchar(255) NOT NULL DEFAULT '/',\n `destination_ip_user` varchar(255) NOT NULL,\n `destination_ip_password` varchar(255) NOT NULL,\n `report_format` varchar(20) NOT NULL DEFAULT 'csv' COMMENT 'txt, csv',\n `last_scheduled_run_fact` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',\n `last_scheduled_run_planned` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',\n `created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',\n `updated` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',\n PRIMARY KEY (`schedule_id`)\n ) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
Yii::app()->db->createCommand($sql)->query();
Yii::app()->db->createCommand("COMMIT")->query();
$this->flushNotification(' ... done');
} else {
$this->flushNotification(' ... already exists');
}
$this->flushNotification('<br/>...Going to create `schedule_report_processed` table...');
if (!in_array('schedule_report_processed', $tables)) {
$sql = "CREATE TABLE `schedule_report_processed` (\n `schedule_processed_id` int(11) NOT NULL AUTO_INCREMENT,\n `schedule_id` int(11) NOT NULL,\n `listener_log_id` int(11) NOT NULL,\n `is_processed` tinyint(1) NOT NULL DEFAULT '0',\n `report_string_initial` text NOT NULL,\n `report_string_changed` text NOT NULL,\n `serialized_report_problems` text NOT NULL,\n `serialized_report_errors` text NOT NULL,\n `serialized_report_explanations` text NOT NULL,\n `is_last` tinyint(1) NOT NULL DEFAULT '0',\n `created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',\n `updated` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',\n PRIMARY KEY (`schedule_processed_id`),\n KEY `schedule_id` (`schedule_id`),\n KEY `listener_log_id` (`listener_log_id`),\n CONSTRAINT `schedule_report_processed_fk` FOREIGN KEY (`schedule_id`) REFERENCES `schedule_report` (`schedule_id`) ON DELETE CASCADE ON UPDATE NO ACTION,\n CONSTRAINT `schedule_report_processed_ibfk_1` FOREIGN KEY (`listener_log_id`) REFERENCES `listener_log` (`log_id`) ON DELETE CASCADE ON UPDATE NO ACTION\n ) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
Yii::app()->db->createCommand($sql)->query();
Yii::app()->db->createCommand("COMMIT")->query();
$this->flushNotification(' ... done');
} else {
$this->flushNotification(' ... already exists');
}
// metrics
$this->flushNotification('<br/><br/>New Metrics:');
$this->flushNotification('<br/>...Going to add new metric "kJ/sq.m" ...');
Yii::app()->db->createCommand("INSERT INTO `refbook_metric` (`metric_id`, `html_code`, `short_name`, `full_name`, `code`) VALUES ('21', 'kJ/sq.m', 'kJ/sq.m', 'Kilo Joule per square meter', 'kjoule_per_sq_meter')")->query();
Yii::app()->db->createCommand("COMMIT")->query();
$this->flushNotification(' ... done');
$this->flushNotification('<br/>...Going to add new metric "feet"...');
Yii::app()->db->createCommand("INSERT INTO `refbook_metric` (`metric_id`, `html_code`, `short_name`, `full_name`, `code`) VALUES ('22', 'ft', 'ft', 'Feet', 'feet')")->query();
Yii::app()->db->createCommand("COMMIT")->query();
$this->flushNotification(' ... done');
$this->flushNotification('<br/>...Going to add new metric "km"...');
Yii::app()->db->createCommand("INSERT INTO `refbook_metric` (`metric_id`,`html_code`,`short_name`,`full_name`,`code`) VALUES (23 , 'km', 'km', 'Kilometer', 'kilometer')")->query();
Yii::app()->db->createCommand("COMMIT")->query();
$this->flushNotification(' ... done');
$this->flushNotification('<br/>...Going to add new relation between "solar radiation" and "kj/sq.m"...');
RefbookMeasurementTypeMetric::model()->deleteByPk(23);
Yii::app()->db->createCommand("INSERT INTO `refbook_measurement_type_metric` (`measurement_type_metric_id`, `measurement_type_id`, `metric_id`, `is_main`) VALUES ('23', '9', '21', '0')")->query();
Yii::app()->db->createCommand("COMMIT")->query();
$this->flushNotification(' ... done');
$this->flushNotification('<br/>...Going to add new measuring type "Cloud Vertical Visibility"...');
Yii::app()->db->createCommand("INSERT INTO `refbook_measurement_type` (`measurement_type_id`, `display_name`, `code`, `ord`) VALUES (16 ,'Cloud Vertical Visibility', 'cloud_vertical_visibility', '14')")->query();
Yii::app()->db->createCommand("COMMIT")->query();
$this->flushNotification(' ... done');
$this->flushNotification('<br/>...Going to add new measuring type "Cloud Height" ...');
Yii::app()->db->createCommand("INSERT INTO `refbook_measurement_type` (`measurement_type_id`, `display_name`, `code`, `ord`) VALUES (17, 'Cloud Height', 'cloud_height', 15)")->query();
Yii::app()->db->createCommand("COMMIT")->query();
$this->flushNotification(' ... done');
$this->flushNotification('<br/>...Going to add new measuring type "Sea Level" ...');
Yii::app()->db->createCommand("INSERT INTO `refbook_measurement_type` (`measurement_type_id`, `display_name`, `code`, `ord`) VALUES (18 , 'Sea Level (Mean, Sigma, Wave Hight)', 'sea_level', '16')")->query();
Yii::app()->db->createCommand("COMMIT")->query();
$this->flushNotification(' ... done');
$this->flushNotification('<br/>...Going to add relation between "Cloud Vertical Visibility" and "ft" ...');
Yii::app()->db->createCommand("INSERT INTO `refbook_measurement_type_metric` (`measurement_type_metric_id`, `measurement_type_id`, `metric_id`, `is_main`) VALUES (24,16,22,1)")->query();
Yii::app()->db->createCommand("COMMIT")->query();
$this->flushNotification(' ... done');
$this->flushNotification('<br/>...Going to add new relation between "Cloud Vertical Visibility" and "meter"...');
Yii::app()->db->createCommand("INSERT INTO `refbook_measurement_type_metric` (`measurement_type_metric_id`, `measurement_type_id`, `metric_id`, `is_main`) VALUES (25,16,11,0)")->query();
Yii::app()->db->createCommand("COMMIT")->query();
$this->flushNotification(' ... done');
$this->flushNotification('<br/>...Going to add new relation between "Cloud Height" and "ft"...');
Yii::app()->db->createCommand("INSERT INTO `refbook_measurement_type_metric` (`measurement_type_metric_id`, `measurement_type_id`, `metric_id`, `is_main`) VALUES (26,17,22,1)")->query();
Yii::app()->db->createCommand("COMMIT")->query();
$this->flushNotification(' ... done');
$this->flushNotification('<br/>...Going to add new relation between "Cloud Height" and "meter"...');
Yii::app()->db->createCommand("INSERT INTO `refbook_measurement_type_metric` (`measurement_type_metric_id`, `measurement_type_id`, `metric_id`, `is_main`) VALUES (27,17,11,0)")->query();
Yii::app()->db->createCommand("COMMIT")->query();
$this->flushNotification(' ... done');
$this->flushNotification('<br/>...Going to add new relation between "Visibility" and "meter"...');
Yii::app()->db->createCommand("INSERT INTO `refbook_measurement_type_metric` (`measurement_type_metric_id`, `measurement_type_id`, `metric_id`, `is_main`) VALUES (28, 11, 11, 1)")->query();
Yii::app()->db->createCommand("COMMIT")->query();
$this->flushNotification(' ... done');
$this->flushNotification('<br/>...Going to add new relation between "Sea Level" and "meter"...');
Yii::app()->db->createCommand("INSERT INTO `refbook_measurement_type_metric` (`measurement_type_metric_id`, `measurement_type_id`, `metric_id`, `is_main`) VALUES (29, 18, 11, 1)")->query();
Yii::app()->db->createCommand("COMMIT")->query();
$this->flushNotification(' ... done');
// sensor handler
$this->flushNotification('<br/>...Going to add new column "awos_station_uses" to `sensor_handler` table...');
$res = Yii::app()->db->createCommand("SHOW COLUMNS FROM `sensor_handler` LIKE 'awos_station_uses'")->queryAll();
if (!$res) {
$res = Yii::app()->db->createCommand("ALTER TABLE `sensor_handler` ADD `awos_station_uses` TINYINT( 1 ) NOT NULL DEFAULT '0'")->query();
Yii::app()->db->createCommand("COMMIT")->query();
$this->flushNotification(' ... done');
} else {
$this->flushNotification(' ... already exists');
}
$this->flushNotification('<br/>...Going to update `sensor_handler` table...');
$sql = "UPDATE `sensor_handler` SET \n `handler_id_code` = 'SeaLevelAWS',\n `display_name` = 'Sea Level and Tide Data',\n `description` = 'Handler \"Sea Level and Tide Data\" : Processes string like \"SL1XXXXYYYYZZZZ\", where <br/>SL1 - device Id; <br/>XXXX - Mean value;<br/>YYYY - Sigma value; <br/>ZZZZ - Wave Height <br/>Example: SL1179017900140 = SL1 sensor sent data: Mean value = 1.79, Sigma value = 1.79, Wave height = 140m.',\n `default_prefix` = 'SL',\n `aws_station_uses` = 1,\n `rain_station_uses` = 0,\n `awos_station_uses` = 0,\n `aws_single_group` = 'sea_level'\n WHERE `handler_id` = 13";
Yii::app()->db->createCommand($sql)->query();
Yii::app()->db->createCommand("COMMIT")->query();
$sql = "UPDATE `sensor_handler` SET \n `handler_id_code` = 'VisibilityAWS',\n `display_name` = 'Visibility',\n `description` = 'Handler \"Visibility\"',\n `default_prefix` = 'VI',\n `aws_station_uses` = 1,\n `rain_station_uses` = 0,\n `awos_station_uses` = 0,\n `aws_single_group` = 'visibility'\n WHERE `handler_id` = 14";
Yii::app()->db->createCommand($sql)->query();
Yii::app()->db->createCommand("COMMIT")->query();
$sql = "UPDATE `sensor_handler` SET `handler_id_code` = 'CloudHeightAWS',\n `display_name` = 'Cloud Height',\n `description` = 'Handler \"Cloud Height\"',\n `default_prefix` = 'CH',\n `aws_station_uses` = 1,\n `rain_station_uses` = 0,\n `awos_station_uses` = 0,\n `aws_single_group` = 'clouds'\n WHERE `handler_id` = 15";
Yii::app()->db->createCommand($sql)->query();
Yii::app()->db->createCommand("COMMIT")->query();
$this->flushNotification(' ... done');
$this->flushNotification('<br/>...Going to update calculation_handler table...');
$res = Yii::app()->db->createCommand("UPDATE `calculation_handler` SET `display_name` = 'Pressure Adjusted to MSL' WHERE `handler_id` = 2");
Yii::app()->db->createCommand("COMMIT")->query();
$this->flushNotification(' ... done');
$bat_path = dirname(Yii::app()->request->scriptFile) . DIRECTORY_SEPARATOR . 'files' . DIRECTORY_SEPARATOR . 'at' . DIRECTORY_SEPARATOR . 'schedule.bat';
$schedule_bat_content = getConfigValue('php_exe_path') . " -f " . dirname(Yii::app()->request->scriptFile) . DIRECTORY_SEPARATOR . "console.php schedule";
file_put_contents($bat_path, $schedule_bat_content);
exec('schtasks /create /sc minute /mo 1 /F /ru "SYSTEM" /tn delaircoScheduleScript /tr ' . $bat_path, $output);
$values = getConfigValue('schedule');
$values['each_minute_process_id'] = 'delaircoScheduleScript';
InstallConfig::setConfigSection('schedule', $values);
$values = getConfigValue('path');
$values['site_url_for_console'] = It::baseUrl();
InstallConfig::setConfigSection('path', $values);
It::memStatus('update__success');
$this->flushNotification('<script type="text/javascript"> setTimeout(function(){document.location.href="' . Yii::app()->controller->createUrl('update/index') . '"}, 10000)</script>');
}
private function prepareListenerLogInserts($limit_timestamp)
{
$result_sql = "";
$sql = "SELECT * \n FROM `" . ListenerLog::model()->tableName() . "`\n WHERE `created` <= '" . $limit_timestamp . "' \n LIMIT 0, 50";
$res = Yii::app()->db->createCommand($sql)->queryAll();
$total = count($res);
$ids = array();
if ($res) {
$fields = array();
foreach ($res[0] as $key2 => $value2) {
$fields[] = $key2;
}
$sql_header = "INSERT IGNORE INTO `" . ListenerLog::model()->tableName() . "` (`" . implode('`,`', $fields) . "`) VALUES ";
$result_sql = $sql_header;
foreach ($res as $key => $value) {
$ids[] = $value['log_id'];
$result_sql .= "('" . implode("','", $value) . "')";
if ($key + 1 < $total) {
$result_sql .= ", ";
}
}
$this->addBackupLog(count($ids) . " inserts for ListenerLog");
}
return array($ids, $result_sql);
}
public function prepareList($page_size = 50)
{
$sql_where_str = $this->_prepareSqlCondition();
$sql_order_str = $this->_prepareSqlOrder();
if ($page_size) {
$total = $this->total;
}
if ($total || !$page_size) {
if ($page_size) {
$pages = new CPagination($total);
$pages->pageSize = $page_size;
}
$sql = "SELECT `t1`.`log_id`, `t1`.`updated`,`t1`.`measuring_timestamp`, `t1`.`created`, `t1`.`message`, `t1`.`fail_description`, t3.station_id_code, t3.display_name, t3.station_type, `t2`.`process_error_id`, `t1`.`is_processed`\n FROM `" . ListenerLog::model()->tableName() . "` t1\n LEFT JOIN `" . ListenerLogProcessError::model()->tableName() . "` `t2` ON `t2`.`log_id` = `t1`.`log_id`\n LEFT JOIN `" . Station::model()->tableName() . "` `t3` ON `t3`.`station_id` = `t1`.`station_id`\n {$sql_where_str}\n GROUP BY `t1`.`log_id`\n {$sql_order_str}";
if ($page_size) {
$sql .= " LIMIT " . $pages->currentPage * $pages->pageSize . ", " . $pages->pageSize;
}
$res = CStubActiveRecord::getDbConnect(true)->createCommand($sql)->queryAll();
if ($res) {
$ids = array();
foreach ($res as $key => $value) {
$ids[] = $value['log_id'];
if ($value['fail_description']) {
$value['errors'] = explode(',', $value['fail_description']);
}
$list[$value['log_id']] = $value;
}
$sql = "SELECT * FROM `" . ListenerLogProcessError::model()->tableName() . "` WHERE `log_id` IN (" . implode(',', $ids) . ")";
$res2 = CStubActiveRecord::getDbConnect(true)->createCommand($sql)->queryAll();
if ($res2) {
foreach ($res2 as $key => $value) {
if ($value['type'] == 'error') {
$list[$value['log_id']]['errors'][] = $value['description'];
} else {
if ($value['type'] == 'warning') {
$list[$value['log_id']]['warnings'][] = $value['description'];
}
}
}
}
}
}
return array('list' => $list, 'pages' => $pages);
}