package com.dev_fbs9600s.data;
|
|
import java.sql.ResultSet;
|
import java.sql.SQLException;
|
import java.util.ArrayList;
|
import java.util.Date;
|
import java.util.List;
|
|
import com.battmonitor.base.Com;
|
import com.battmonitor.data.BattAlarmData;
|
import com.battmonitor.data.BattAlarmParam;
|
import com.battmonitor.data.BattData_RT;
|
import com.battmonitor.sql.MysqlConnPool;
|
import com.battmonitor.sql.Sql_Mysql;
|
|
public class FBS9600S_BattAlarm_Thread_SQL {
|
|
public final static String BattAlarmParamTableName = "tb_alarm_param"; //µç³Ø¸æ¾¯²ÎÊý±í
|
public final static String BattAlarmDataTableName = "tb_battalarm_data"; //µç³Ø¸æ¾¯Êý¾Ý±í
|
public final static String DevAlarmDataTableName = "tb_dev_param";
|
/**
|
* ¶ÁÈ¡µç³Ø×éµÄ¸æ¾¯²ÎÊý
|
* @param pool
|
* @param param
|
*/
|
public static void queryBattAlarmParam(MysqlConnPool pool, BattAlarmParam param) {
|
String sql_str = "SELECT * from "+Sql_Mysql.Batt_Alm_Param_Table+" WHERE param_id = 2020001";
|
ResultSet res = null;
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
res = sql.sqlMysqlQuery(sql_str);
|
if(res.next()) {
|
float val_pam = param.alm_MonVol.alm_High;
|
param.alm_MonVol.alm_High = res.getFloat("mon_vol_high");
|
if(val_pam != param.alm_MonVol.alm_High) {
|
param.update_en = true;
|
}
|
val_pam = param.alm_MonVol.alm_Low;
|
param.alm_MonVol.alm_Low = res.getFloat("mon_vol_low");
|
if(val_pam != param.alm_MonVol.alm_Low) {
|
param.update_en = true;
|
}
|
val_pam = param.alm_MonTmp.alm_High;
|
param.alm_MonTmp.alm_High = res.getFloat("mon_tmp_high");
|
if(val_pam != param.alm_MonTmp.alm_High) {
|
param.update_en = true;
|
}
|
val_pam = param.alm_MonTmp.alm_Low;
|
param.alm_MonTmp.alm_Low = res.getFloat("mon_tmp_low");
|
if(val_pam != param.alm_MonTmp.alm_Low) {
|
param.update_en = true;
|
}
|
val_pam = param.alm_MonRes.alm_High;
|
param.alm_MonRes.alm_High = res.getFloat("mon_res_high");
|
if(val_pam != param.alm_MonRes.alm_High) {
|
param.update_en = true;
|
}
|
val_pam = param.alm_MonRes.alm_Low;
|
param.alm_MonRes.alm_Low = res.getFloat("mon_res_low");
|
if(val_pam != param.alm_MonRes.alm_Low) {
|
param.update_en = true;
|
}
|
}
|
} catch (Exception e) {
|
e.printStackTrace();
|
} finally {
|
if(null != res) {
|
try {
|
res.close();
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
}
|
sql.close_con();
|
}
|
}
|
|
/**
|
* ²éѯÆô¶¯Ç°µ±Ç°µç³Ø×éµÄʵʱ¸æ¾¯(¼Èë³ÌÐòÆô¶¯Ç°µÄ¸æ¾¯¼Ç¼)
|
* @return
|
*/
|
public static void searchLastHistoryBattAlarm(MysqlConnPool pool,BattData_RT battdata){
|
List<BattAlarmData> alarms = new ArrayList<BattAlarmData>();
|
String sql_str = " SELECT * FROM " + BattAlarmDataTableName + " "
|
+ " WHERE (alm_cleared_type = 0 OR alm_cleared_type = 2) AND alm_id NOT IN(119010,119011) AND BattGroupId= " + battdata.BattGroupId;
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
sql.sqlMysqlUseDB(Sql_Mysql.DB_ALARM);
|
ResultSet rs = sql.sqlMysqlQuery(sql_str);
|
BattAlarmData alarm = null;
|
while(rs.next()) {
|
alarm = new BattAlarmData();
|
alarm.battgroup_id = rs.getInt("BattGroupId");
|
alarm.alm_id = rs.getInt("alm_id");
|
alarm.alm_signal_id = rs.getInt("alm_signal_id");
|
alarm.alm_cleared_type = rs.getInt("alm_cleared_type");
|
alarm.mon_num = rs.getInt("MonNum");
|
alarm.record_id = rs.getLong("Record_Id");
|
alarms.add(alarm);
|
//System.out.println(alarm.record_id);
|
}
|
//System.out.println("Àúʷʵʱ¸æ¾¯Êý:"+alarms.size()+"##################");
|
if(alarms.size()>0) {
|
for(int i=0;i<alarms.size();i++) {
|
alarm = alarms.get(i);
|
switch(alarm.alm_id) {
|
//µç³Øµ¥Ìåµçѹ
|
case FBS9600S_BattAlarm_Thread.ALM_TYPE_MonVol_High_ID:
|
case FBS9600S_BattAlarm_Thread.ALM_TYPE_MonVol_Low_ID:{
|
if(alarm.mon_num > battdata.al_MonVol.size()) {
|
continue;
|
}
|
battdata.al_MonVol.get(alarm.mon_num-1).monVol_AlarmData.alm_id = alarm.alm_id;
|
battdata.al_MonVol.get(alarm.mon_num-1).monVol_AlarmData.alm_id_high = FBS9600S_BattAlarm_Thread.ALM_TYPE_MonVol_High_ID;
|
battdata.al_MonVol.get(alarm.mon_num-1).monVol_AlarmData.alm_id_low = FBS9600S_BattAlarm_Thread.ALM_TYPE_MonVol_Low_ID;
|
battdata.al_MonVol.get(alarm.mon_num-1).monVol_AlarmData.alm_cleared_type = alarm.alm_cleared_type;
|
battdata.al_MonVol.get(alarm.mon_num-1).monVol_AlarmData.alm_signal_id = alarm.alm_signal_id;
|
}break;
|
//µç³Øµ¥ÌåζÈ
|
case FBS9600S_BattAlarm_Thread.ALM_TYPE_MonTmp_High_ID:
|
case FBS9600S_BattAlarm_Thread.ALM_TYPE_MonTmp_Low_ID:{
|
if(alarm.mon_num > battdata.al_MonVol.size()) {
|
continue;
|
}
|
battdata.al_MonVol.get(alarm.mon_num-1).monTmp_AlarmData.alm_id = alarm.alm_id;
|
battdata.al_MonVol.get(alarm.mon_num-1).monTmp_AlarmData.alm_id_high = FBS9600S_BattAlarm_Thread.ALM_TYPE_MonTmp_High_ID;
|
battdata.al_MonVol.get(alarm.mon_num-1).monTmp_AlarmData.alm_id_low = FBS9600S_BattAlarm_Thread.ALM_TYPE_MonTmp_Low_ID;
|
battdata.al_MonVol.get(alarm.mon_num-1).monTmp_AlarmData.alm_cleared_type = alarm.alm_cleared_type;
|
battdata.al_MonVol.get(alarm.mon_num-1).monTmp_AlarmData.alm_signal_id = alarm.alm_signal_id;
|
}break;
|
//µ¥ÌåÄÚ×è
|
case FBS9600S_BattAlarm_Thread.ALM_TYPE_MonRes_High_ID:
|
case FBS9600S_BattAlarm_Thread.ALM_TYPE_MonRes_Low_ID:{
|
if(alarm.mon_num > battdata.al_MonVol.size()) {
|
continue;
|
}
|
battdata.al_MonVol.get(alarm.mon_num-1).monRes_AlarmData.alm_id = alarm.alm_id;
|
battdata.al_MonVol.get(alarm.mon_num-1).monRes_AlarmData.alm_id_high = FBS9600S_BattAlarm_Thread.ALM_TYPE_MonRes_High_ID;
|
battdata.al_MonVol.get(alarm.mon_num-1).monRes_AlarmData.alm_id_low = FBS9600S_BattAlarm_Thread.ALM_TYPE_MonRes_Low_ID;
|
battdata.al_MonVol.get(alarm.mon_num-1).monRes_AlarmData.alm_cleared_type = alarm.alm_cleared_type;
|
battdata.al_MonVol.get(alarm.mon_num-1).monRes_AlarmData.alm_signal_id = alarm.alm_signal_id;
|
}break;
|
}
|
}
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
/**
|
* ¸ù¾Ý¸æ¾¯¶ÔÏóÌí¼Óеĸ澯¼Ç¼»òÕßÈ¡Ïû¸æ¾¯¼Ç¼
|
* @param m_Conn_Pool.getConn()
|
* @param alm_data
|
*/
|
public static void insertOrUpdateBattAlarmDataToTable(MysqlConnPool m_Conn_Pool, BattAlarmData alm_data)
|
{
|
Sql_Mysql sql = new Sql_Mysql(m_Conn_Pool.getConn());
|
try {
|
sql.sqlMysqlUseDB(Sql_Mysql.DB_ALARM);
|
if(true == sql.sqlMysqlCheckIfTableExist(BattAlarmDataTableName))
|
{
|
|
//System.out.println("alm_id:"+alm_data.alm_id+"\t alm_signal_id:"+alm_data.alm_signal_id+"\t clear_type:"+alm_data.alm_cleared_type);
|
//вúÉúµÄ¸æ¾¯¼Ç¼
|
if(BattAlarmData.ALM_CLEARED_TYPE_NULL == alm_data.alm_cleared_type)
|
{
|
BattAlarmData last_alarm = searchBattLastHistory(m_Conn_Pool,alm_data);
|
if(last_alarm != null && last_alarm.alm_id == alm_data.alm_id && last_alarm.mon_num == alm_data.mon_num && last_alarm.alm_cleared_type == BattAlarmData.ALM_CLEARED_TYPE_NULL) {
|
//µ±Ç°¸æ¾¯¼Ç¼ÒѾÔÚµç³Øʵʱ¸æ¾¯±íÖдæÔÚ
|
if(last_alarm.alm_signal_id != alm_data.alm_signal_id ) {
|
//Èç¹û´æÔÚÏàͬÖÖÀ಻ͬÀàÐ͵ĸ澯 (¸æ¾¯Ãû³ÆÒ»ÖÂ;¸æ¾¯ÉÏÏÂÏÞ²»Í¬)
|
//System.out.println("È¡Ïû֮ǰµÄ¸æ¾¯£¬Ìí¼Óеĸ澯");
|
//System.out.println("µç³Ø×éid"+alm_data.battgroup_id+"\t¸æ¾¯Öµ:"+alm_data.alm_value);
|
last_alarm.alm_cleared_type = BattAlarmData.ALM_CLEARED_TYPE_AUTO;
|
chageRealToHistoryAlarm(m_Conn_Pool, last_alarm); //½«Ö®Ç°ÏàͬÖÖÀ಻ͬÀàÐ͵ĸ澯±ä³ÉÀúÊ·¸æ¾¯
|
|
insertNewBattRealAlarm(m_Conn_Pool, alm_data); //²åÈ뵱ǰеĸ澯
|
}
|
}else{
|
//System.out.println("и澯¼Ç¼"+alm_data.alm_cleared_type);
|
insertNewBattRealAlarm(m_Conn_Pool, alm_data); //²åÈëеĸ澯¼Ç¼
|
//System.out.println("Ö±½ÓÌí¼Óеĸ澯¼Ç¼"+alm_data.alm_cleared_type+"\t¸æ¾¯Öµ£º"+alm_data.alm_value+"\t record_id"+alm_data.record_id);
|
}
|
} else {
|
//¸æ¾¯ÑÓ³Ù½áÊøµÄ¸æ¾¯¼Ç¼
|
if(BattAlarmData.ALM_CLEARED_TYPE_AUTO == alm_data.alm_cleared_type) {
|
//System.out.println("Õý³£¸æ¾¯Ïûʧ");
|
chageRealToHistoryAlarm(m_Conn_Pool, alm_data); //½«ÊµÊ±¸æ¾¯±ä³ÉÀúÊ·¸æ¾¯
|
}
|
}
|
}
|
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
//System.out.println("sql.close_con()");
|
}
|
}
|
|
/**
|
* ²éѯµ±Ç°µç³Ø×é֮ǰÊÇ·ñ´æÔÚµ±Ç°ÖÖÀàµÄ¸æ¾¯
|
* @param alm_data
|
* @return
|
*/
|
public static BattAlarmData searchBattLastHistory(MysqlConnPool conn_pool,BattAlarmData alm_data) {
|
BattAlarmData battalarm = null;
|
String sql_str = " SELECT * FROM db_alarm.tb_battalarm_data WHERE BattGroupId = " +alm_data.battgroup_id+ " AND alm_id = " + alm_data.alm_id + " AND alm_cleared_type != 1 AND MonNum = "+alm_data.mon_num;
|
Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
|
ResultSet rs = null;
|
try {
|
rs = sql.sqlMysqlQuery(sql_str);
|
if(rs.next()){
|
battalarm = new BattAlarmData();
|
battalarm.battgroup_id = rs.getInt("BattGroupId");
|
battalarm.mon_num = rs.getInt("MonNum");
|
battalarm.record_id = rs.getLong("Record_Id");
|
battalarm.alm_id = rs.getInt("alm_id");
|
battalarm.alm_signal_id = rs.getInt("alm_signal_id");
|
battalarm.alm_signal_level = rs.getInt("alm_level");
|
battalarm.alm_start_time = rs.getTimestamp("alm_start_time");
|
battalarm.alm_end_time = rs.getTimestamp("alm_end_time");
|
battalarm.alm_value = rs.getFloat("alm_value");
|
battalarm.alm_is_confirmed = rs.getBoolean("alm_is_confirmed");
|
battalarm.alm_confirmed_Time = rs.getTimestamp("alm_confirmed_time");
|
battalarm.alm_cleared_type = rs.getInt("alm_cleared_type");
|
battalarm.usr_id = rs.getInt("usr_Id");
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}finally {
|
sql.close_con();
|
}
|
return battalarm;
|
}
|
|
|
/**
|
* ½«µ±Ç°¸æ¾¯±ä³ÉÀúÊ·¸æ¾¯
|
* @param m_Conn_Pool
|
* @param alm_data
|
*/
|
public static void chageRealToHistoryAlarm(MysqlConnPool m_Conn_Pool, BattAlarmData alm_data) {
|
String sql_str = " UPDATE " + BattAlarmDataTableName
|
+ " SET "
|
+ " alm_end_time='" + Com.getDateTimeFormat(alm_data.alm_end_time, Com.DTF_YMDhms) + "', "
|
+ " alm_cleared_type=" + alm_data.alm_cleared_type
|
+ " WHERE MonNum ="+alm_data.mon_num+" AND BattGroupId ="+alm_data.battgroup_id+" AND alm_id ="+alm_data.alm_id + " AND alm_signal_id = " + alm_data.alm_signal_id;
|
Sql_Mysql sql = new Sql_Mysql(m_Conn_Pool.getConn());
|
try {
|
sql.sqlMysqlUseDB(Sql_Mysql.DB_ALARM);
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
public static void insertNewBattRealAlarm(MysqlConnPool m_Conn_Pool, BattAlarmData alm_data) {
|
Sql_Mysql sql = new Sql_Mysql(m_Conn_Pool.getConn());
|
|
String sql_str = " INSERT INTO " + BattAlarmDataTableName + " "
|
+ "(BattGroupId, "
|
+ "MonNum, "
|
+ "Record_Id, "
|
+ "alm_id, "
|
+ "alm_signal_id, "
|
+ "alm_level, "
|
+ "alm_start_time, "
|
+ "alm_end_time, "
|
+ "alm_value, "
|
+ "alm_is_confirmed, "
|
+ "alm_confirmed_time, "
|
+ "alm_cleared_type, "
|
+ "usr_Id) "
|
+ " VALUES "
|
+ "("
|
+ alm_data.battgroup_id + ", "
|
+ alm_data.mon_num + ", "
|
+ alm_data.record_id + ", "
|
+ alm_data.alm_id + ", "
|
+ alm_data.alm_signal_id + ", "
|
+ alm_data.alm_signal_level + ", "
|
+ "'" + Com.getDateTimeFormat(alm_data.alm_start_time, Com.DTF_YMDhms) + "', "
|
+ "'" + Com.getDateTimeFormat(alm_data.alm_end_time, Com.DTF_YMDhms) + "', "
|
+ alm_data.alm_value + ", "
|
+ alm_data.alm_is_confirmed + ", "
|
+ "'" + Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms) + "', "
|
+ alm_data.alm_cleared_type + ", "
|
+ alm_data.usr_id
|
+ ")";
|
try {
|
sql.sqlMysqlUseDB(Sql_Mysql.DB_ALARM);
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
/**
|
* ²éѯʵʱ¸æ¾¯±íÖеÄËùÓбä³ÉÀúÊ·¸æ¾¯µÄ¼Ç¼
|
* @param alm_data
|
* @return
|
*/
|
public static List<BattAlarmData> searchAllHistAlarmFromReal(MysqlConnPool conn_pool) {
|
List<BattAlarmData> alarms = new ArrayList<BattAlarmData>();
|
String sql_str = " SELECT * FROM db_alarm.tb_battalarm_data WHERE alm_id NOT IN(119010,119011) AND alm_cleared_type = 1";
|
Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
|
ResultSet rs = null;
|
try {
|
rs = sql.sqlMysqlQuery(sql_str);
|
BattAlarmData battalarm;
|
if(rs.next()){
|
battalarm = new BattAlarmData();
|
battalarm.battgroup_id = rs.getInt("BattGroupId");
|
battalarm.mon_num = rs.getInt("MonNum");
|
battalarm.record_id = rs.getLong("Record_Id");
|
battalarm.alm_id = rs.getInt("alm_id");
|
battalarm.alm_signal_id = rs.getInt("alm_signal_id");
|
battalarm.alm_signal_level = rs.getInt("alm_level");
|
battalarm.alm_start_time = rs.getTimestamp("alm_start_time");
|
battalarm.alm_end_time = rs.getTimestamp("alm_end_time");
|
battalarm.alm_value = rs.getFloat("alm_value");
|
battalarm.alm_is_confirmed = rs.getBoolean("alm_is_confirmed");
|
battalarm.alm_confirmed_Time = rs.getTimestamp("alm_confirmed_time");
|
battalarm.alm_cleared_type = rs.getInt("alm_cleared_type");
|
battalarm.usr_id = rs.getInt("usr_Id");
|
alarms.add(battalarm);
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}finally {
|
sql.close_con();
|
}
|
return alarms;
|
}
|
|
/**
|
* Åжϵ±Ç°¸æ¾¯Ö®Ç°ÊÇ·ñ´æÔÚµ±Ç°µç³Ø×鵱ǰÖÐÀàµÄ¸æ¾¯,´æÔÚÔòÈ¡Ïûµ±Ç°µÄʵʱ¸æ¾¯,±ä³ÉÀúÊ·¸æ¾¯
|
* @param m_Conn_Pool.getConn()
|
* @param alm_data
|
*/
|
public static void checkBattAlarmData(MysqlConnPool m_Conn_Pool, BattAlarmData alm_data)
|
{
|
//²éѯʵʱ¼Ç¼±íÖÐÊÇ·ñ´æÔÚµ±Ç°ÖÖÀàµÄʵʱ¸æ¾¯¼Ç¼
|
String sql_str0 = "SELECT * FROM tb_battalarm_data "
|
+ " WHERE alm_id NOT IN(119010,119011) AND MonNum ="+alm_data.mon_num+" "
|
+ " AND BattGroupId ="+alm_data.battgroup_id+" AND alm_cleared_type != 0 "
|
+ " AND alm_id ="+alm_data.alm_id;
|
|
|
//½«µ±Ç°µÄµç³Øʵʱ¸æ¾¯±ä³ÉÀúÊ·¸æ¾¯
|
String sql_str1 = " UPDATE tb_battalarm_data "
|
+ " SET alm_cleared_type = 1, "
|
+ " alm_end_time='" + Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms) + "' "
|
+ " WHERE alm_id NOT IN(119010,119011) AND MonNum ="+alm_data.mon_num+" AND alm_cleared_type = 1"
|
+ " AND BattGroupId ="+alm_data.battgroup_id+" AND alm_id ="+alm_data.alm_id;
|
//½«µ±Ç°µÄʵʱ¸æ¾¯¼Ç¼²åÈëµ½ÀúÊ·¸æ¾¯±íÖÐ (²»°üÀ¨ÈÝÁ¿¸æ¾¯¼Ç¼)
|
String sql_str2 = "INSERT INTO tb_battalarm_data_history"
|
+ "(BattGroupId,MonNum,Record_Id,alm_id,alm_signal_id,alm_level,alm_start_time,alm_end_time,alm_value,alm_is_confirmed,alm_confirmed_time,alm_cleared_type,usr_Id) "
|
+ "(SELECT BattGroupId,MonNum,Record_Id,alm_id,alm_signal_id,alm_level,alm_start_time,alm_end_time,alm_value,alm_is_confirmed,alm_confirmed_time,alm_cleared_type,usr_Id from tb_battalarm_data "
|
+ " WHERE alm_id NOT IN(119010,119011) AND MonNum ="+alm_data.mon_num+" AND BattGroupId ="+alm_data.battgroup_id+" "
|
+ " AND alm_cleared_type = 1 AND alm_id ="+alm_data.alm_id+ " AND alm_signal_id = " + alm_data.alm_signal_id
|
+ ")";
|
//½«ÊµÊ±¸æ¾¯±íÖеĸ澯¼Ç¼Çå¿Õ(²»°üÀ¨ÈÝÁ¿¸æ¾¯¼Ç¼)
|
String sql_str3 = "DELETE FROM tb_battalarm_data "
|
+ " WHERE alm_id NOT IN(119010,119011) AND MonNum ="+alm_data.mon_num + " "
|
+ " AND BattGroupId ="+alm_data.battgroup_id+" AND alm_cleared_type =1 "
|
+ " AND alm_id ="+alm_data.alm_id + " AND alm_signal_id = " + alm_data.alm_signal_id;
|
Sql_Mysql sql = new Sql_Mysql(m_Conn_Pool.getConn());
|
try {
|
sql.sqlMysqlUseDB(Sql_Mysql.DB_ALARM);
|
//ResultSet rs = sql.sqlMysqlQuery(sql_str0);
|
//if(rs.next()){
|
//System.out.println("¸æ¾¯ÀàÐÍ:"+alm_data.alm_id+"\tµç³Ø×éid"+alm_data.battgroup_id+"\t µ¥Ìå±àºÅ"+alm_data.mon_num);
|
//Èç¹û´æÔÚµ±Ç°µç³Ø×é,µ±Ç°ÖÖÀàµÄ¸æ¾¯¼Ç¼,¾Í½«Ö®Ç°µÄ¸æ¾¯¼Ç¼´æÈëÀúÊ·¸æ¾¯¼Ç¼,²¢É¾³ýʵʱ¸æ¾¯Öеĸ澯¼Ç¼
|
ArrayList<String> sql_strs = new ArrayList<String>();
|
//sql_strs.add(sql_str1);
|
sql_strs.add(sql_str2);
|
sql_strs.add(sql_str3);
|
sql.makeManualCommit(sql_strs);
|
//sql.sqlMysqlExecute(sql_str1);
|
//sql.sqlMysqlExecute(sql_str2);
|
//sql.sqlMysqlExecute(sql_str3);
|
|
//}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
}
|
