package com.power.alarm;
|
|
import java.sql.ResultSet;
|
import java.sql.SQLException;
|
import java.util.ArrayList;
|
import java.util.Date;
|
import java.util.List;
|
|
import com.base.Com;
|
import com.power.mysql.MysqlConnPool;
|
import com.power.mysql.Sql_Mysql;
|
|
|
public class PwrDevHistoryAlm_SubtableThread_SQL {
|
|
|
|
/**
|
* ²éѯ½ñÄê֮ǰµÄµÄµçÔ´ÀúÊ·¸æ¾¯¼Ç¼
|
* @param pool
|
* @param battAlarms
|
*/
|
public static void queryBattHistoryAlarm(MysqlConnPool pool,List<PwrDeviceAlarm_Data> pwrAlarms) {
|
Date firstTime = new Date(Com.getYearFirstDay(new Date())); //±¾Äê1ºÅ0µãʱ¼ä
|
String sql_str = " SELECT * FROM " + Sql_Mysql.PWRDEV_ALARM_HISTORY_TABLE +
|
" WHERE alm_start_time < '" + Com.getDateTimeFormat(firstTime, Com.DTF_YMDhms) + "'";
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
ResultSet res = null;
|
try {
|
//System.err.println(sql_str);
|
res = sql.sqlMysqlQuery(sql_str);
|
while(res.next()) {
|
PwrDeviceAlarm_Data alm = new PwrDeviceAlarm_Data(res.getInt("alm_type"));
|
|
alm.pwrdev_id = res.getInt("PowerDeviceId");
|
alm.RecId = res.getLong("record_id");
|
alm.alm_type = res.getInt("alm_type"); //¸æ¾¯id
|
alm.alm_cleared_type = res.getInt("alm_cleared_type"); //¸æ¾¯ÀàÐÍ 0:ʵʱ¸æ¾¯ 1:ÀúÊ·¸æ¾¯ 2:È¡Ïû¸æ¾¯
|
alm.alm_vlaue = res.getFloat("alm_value"); //¸æ¾¯Öµ
|
alm.alm_level = res.getInt("alm_level"); //¸æ¾¯µÈ¼¶
|
alm.alm_start_time = res.getTimestamp("alm_start_time"); //¸æ¾¯¿ªÊ¼Ê±¼ä
|
alm.alm_end_time = res.getTimestamp("alm_end_time"); //¸æ¾¯½áÊøʱ¼ä
|
alm.alm_trigger = res.getInt("alm_trigger"); //¸æ¾¯À´Ô´[0-À´Ô´ÓÚµã±í£»1-À´Ô´ÓÚµçÔ´¸æ¾¯²ÎÊý]
|
alm.alm_severity = res.getInt("alm_severity"); //¸æ¾¯ÖØÒªµÈ¼¶[0:ÖØÒª£»1:½ô¼±]
|
|
pwrAlarms.add(alm);
|
}
|
} catch (SQLException e) {
|
sql.logger.error(e.toString(), e);
|
} finally {
|
if(null != res) {
|
try {
|
res.close();
|
} catch (SQLException e) {
|
sql.logger.error(e.toString(), e);
|
}
|
}
|
sql.close_con();
|
}
|
|
}
|
|
|
|
/**
|
* ½«½ñÄê֮ǰµÄµç³Ø¸æ¾¯¼Ç¼´æÈë¸æ¾¯·Ö±í
|
* @param pool
|
* @param alm
|
*/
|
public static void changePwrAlarmToHistoryTable(MysqlConnPool pool,PwrDeviceAlarm_Data alm) {
|
createPwrHistoryTable(pool, alm); //´´½¨·Ö±í¼Ç¼±í
|
|
String sql_str_ins = " INSERT INTO " + Sql_Mysql.PWRDEV_ALARM_HISTORYSUB_TABLE + Com.getDateTimeFormat(alm.alm_start_time, Com.DTF_Y) + "(record_id,PowerDeviceId,alm_type,alm_level,alm_start_time,alm_end_time,alm_value,alm_is_confirmed,alm_confirmed_time,alm_cleared_type,alm_trigger,alm_severity) (SELECT record_id,PowerDeviceId,alm_type,alm_level,alm_start_time,alm_end_time,alm_value,alm_is_confirmed,alm_confirmed_time,alm_cleared_type,alm_trigger,alm_severity from "+Sql_Mysql.PWRDEV_ALARM_HISTORY_TABLE+" WHERE PowerDeviceId = " + alm.pwrdev_id + " and alm_type = " + alm.alm_type + " and alm_start_time = '" + Com.getDateTimeFormat(alm.alm_start_time, Com.DTF_YMDhms) + "')";
|
|
|
String sql_str_del = " DELETE FROM " + Sql_Mysql.PWRDEV_ALARM_HISTORY_TABLE +" WHERE PowerDeviceId = "+ alm.pwrdev_id + " and alm_type = " + alm.alm_type + " and alm_start_time = '" + Com.getDateTimeFormat(alm.alm_start_time, Com.DTF_YMDhms) + "'";
|
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
ArrayList<String> sql_strs = new ArrayList<String>();
|
//System.err.println(sql_str_ins);
|
//System.err.println(sql_str_del);
|
sql_strs.add(sql_str_ins);
|
sql_strs.add(sql_str_del);
|
sql.makeManualCommit(sql_strs);
|
} catch (Exception e) {
|
sql.logger.error(e.toString(), e);
|
} finally {
|
sql.close_con();
|
}
|
|
}
|
|
/**
|
* ´´½¨µçÔ´ÀúÊ·¸æ¾¯·Ö±í
|
* @param pool
|
* @param alm
|
*/
|
public static void createPwrHistoryTable(MysqlConnPool pool,PwrDeviceAlarm_Data alm) {
|
String sql_str = " CREATE TABLE IF NOT EXISTS " + Sql_Mysql.PWRDEV_ALARM_HISTORYSUB_TABLE + Com.getDateTimeFormat(alm.alm_start_time, Com.DTF_Y) + "(" +
|
" `num` bigint(20) NOT NULL AUTO_INCREMENT," +
|
" `record_id` bigint(20) NOT NULL DEFAULT '0'," +
|
" `PowerDeviceId` bigint(20) NOT NULL DEFAULT '0'," +
|
" `alm_type` int(11) NOT NULL DEFAULT '0'," +
|
" `alm_level` int(11) NOT NULL DEFAULT '0'," +
|
" `alm_source` int(11) NOT NULL DEFAULT '0'," +
|
" `alm_index` int(11) NOT NULL DEFAULT '0'," +
|
" `alm_start_time` datetime NOT NULL DEFAULT '1982-01-01 00:00:00'," +
|
" `alm_end_time` datetime NOT NULL DEFAULT '1982-01-01 00:00:00'," +
|
" `alm_value` float NOT NULL DEFAULT '0'," +
|
" `alm_is_confirmed` tinyint(1) NOT NULL DEFAULT '0'," +
|
" `alm_confirmed_time` datetime NOT NULL DEFAULT '1982-01-01 00:00:00'," +
|
" `alm_cleared_type` int(11) NOT NULL DEFAULT '0'," +
|
" `usr_Id` int(11) NOT NULL DEFAULT '0'," +
|
" `alm_trigger` int(11) NOT NULL DEFAULT '0' COMMENT '¸æ¾¯´¥·¢À´Ô´[0-À´Ô´ÓÚµã±í£»1-À´Ô´ÓÚµçÔ´¸æ¾¯²ÎÊý]'," +
|
" `alm_severity` int(11) NOT NULL DEFAULT '0' COMMENT '¸æ¾¯ÖØÒªµÈ¼¶[0:ÖØÒª£»1:½ô¼±]'," +
|
" PRIMARY KEY (`num`)," +
|
" KEY `index_record_id` (`record_id`)," +
|
" KEY `index_pwrdev_id` (`PowerDeviceId`)," +
|
" KEY `index_alm_type` (`alm_type`)," +
|
" KEY `index_alm_start_time` (`alm_start_time`)," +
|
" KEY `index_alm_cleared_type` (`alm_cleared_type`)" +
|
") ENGINE=InnoDB AUTO_INCREMENT=4796 DEFAULT CHARSET=utf8;" ;
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException e) {
|
sql.logger.error(e.toString(), e);
|
} finally{
|
sql.close_con();
|
}
|
|
}
|
|
/**
|
* ɾ³ý´´½¨Ê±¼ä³¬¹ýÈýÄêµÄµçÔ´ÀúÊ·¸æ¾¯·Ö±í
|
* @param pool
|
* @param deldate
|
*/
|
public static void delBattAlarmData(MysqlConnPool pool,Date deldate) {
|
String sql_str_sel = " select TABLE_NAME,CREATE_TIME " +
|
" FROM information_schema.tables " +
|
" where table_schema='db_pwrdev_alarm' " +
|
" AND TABLE_NAME like 'tb_pwrdev_alarm_history_%' " +
|
" AND CREATE_TIME <= '"+Com.getDateTimeFormat(deldate, Com.DTF_YMDhms)+"';" ;
|
|
ResultSet res = null;
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
res = sql.sqlMysqlTotalQuery(sql_str_sel);
|
int count = 0;
|
StringBuffer sql_delete_strs = new StringBuffer(" DROP TABLE IF EXISTS ");
|
try {
|
while(res.next()) {
|
if(count > 0) {
|
sql_delete_strs.append(",");
|
}
|
|
sql_delete_strs.append("db_pwrdev_alarm." + res.getString("TABLE_NAME"));
|
sql.logger.info("×Ô¶¯É¾³ý³¬¹ý3ÄêµÄ¸æ¾¯±í : db_pwrdev_alarm."+res.getString("TABLE_NAME")+"\t at "+Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms));
|
count++;
|
}
|
if(count >0) {
|
//System.out.println(sql_delete_strs.toString());
|
sql.sqlTotalMysqlExecute(sql_delete_strs.toString());
|
}
|
} catch (SQLException e) {
|
sql.logger.error(e.toString(), e);
|
} finally {
|
if(null != res) {
|
try {
|
res.close();
|
} catch (SQLException e) {
|
sql.logger.error(e.toString(), e);
|
}
|
}
|
sql.close_con();
|
}
|
}
|
|
}
|
