package com.dec.fbs9100;
|
|
import java.sql.ResultSet;
|
import java.sql.SQLException;
|
import java.util.Date;
|
import com.base.Com;
|
import com.battdata_rt.BattData_RT;
|
|
public class BTS61850_Task_Thread_SQL {
|
|
/**
|
* 判断当前历史实时数据表是否存在
|
* @param conn_pool
|
* @param dev_id
|
* @param date
|
* @return true:已存在 false:不存在
|
*/
|
public static boolean checkHistoryIfNotExistsTable(MysqlConnPool conn_pool,int BattGroupId) {
|
Sql_Mysql sql = new Sql_Mysql(conn_pool);
|
String tname;
|
try {
|
sql.sqlMysqlUseDB(Sql_Mysql.DB_BATT_HISTORY);
|
tname = Sql_Mysql.Tb_Batt_RealData + BattGroupId + "_" + Com.getDateTimeFormat(new Date(), Com.DTF_YM);
|
return sql.sqlMysqlCheckIfTableExist(tname);
|
} catch (SQLException e) {
|
sql.logger.error("checkHistoryIfNotExistsTable():" + e.toString(), e);
|
} finally {
|
sql.close_con();
|
}
|
return false;
|
}
|
|
/**
|
* 创建指定的电池组历史数据记录表
|
* @param pool
|
* @param BattGroupId 电池组id
|
* @param time 记录时间
|
*/
|
public static void CreateTb_BattRealDataTable(MysqlConnPool pool,int BattGroupId,Date time) {
|
String sql_str = "CREATE TABLE IF NOT EXISTS " + Sql_Mysql.Tb_Batt_RealData+BattGroupId+"_"+Com.getDateTimeFormat(time, Com.DTF_YM) + " (" +
|
" `num` bigint(20) NOT NULL AUTO_INCREMENT," +
|
" `BattGroupId` int(11) NOT NULL DEFAULT '1'," +
|
" `recrod_time` datetime NOT NULL DEFAULT '2000-01-01 00:00:00'," +
|
" `group_vol` float NOT NULL DEFAULT '0'," +
|
" `online_vol` float NOT NULL DEFAULT '0'," +
|
" `group_curr` float NOT NULL DEFAULT '0'," +
|
" `group_tmp` float NOT NULL DEFAULT '0'," +
|
" `batt_state` int(11) NOT NULL DEFAULT '0'," +
|
" `batt_test_type` int(11) NOT NULL DEFAULT '0'," +
|
" `batt_test_starttime` datetime NOT NULL DEFAULT '2000-01-01 00:00:00'," +
|
" `batt_test_tlong` int(11) NOT NULL DEFAULT '0'," +
|
" `batt_test_cap` float NOT NULL DEFAULT '0'," +
|
" `mon_num` int(11) NOT NULL DEFAULT '1'," +
|
" `mon_vol` float NOT NULL DEFAULT '0'," +
|
" `mon_tmp` float NOT NULL DEFAULT '0'," +
|
" `mon_res` float NOT NULL DEFAULT '0'," +
|
" `mon_ser` float NOT NULL DEFAULT '0'," +
|
" `mon_conn_res` float NOT NULL DEFAULT '0'," +
|
" `mon_cap` float NOT NULL DEFAULT '0'," +
|
" `mon_JH_curr` float NOT NULL DEFAULT '0'," +
|
" `note` varchar(255) NOT NULL DEFAULT ''," +
|
" PRIMARY KEY (`num`)," +
|
" KEY `index_battgroupid` (`BattGroupId`) USING BTREE," +
|
" KEY `index_record_time` (`recrod_time`) USING BTREE" +
|
") ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;";
|
Sql_Mysql sql = new Sql_Mysql(pool);
|
try {
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException e) {
|
sql.logger.error("CreateTb_BattRealDataTable():" + e.toString(), e);
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
/**
|
* 删除修改时间超时的历史数据表[没有修改时间的表不会有应影响]
|
*/
|
public static void deleteHistoryData(MysqlConnPool conn_pool,Date deldate) {
|
String sql_select_strs = " select TABLE_NAME,UPDATE_TIME " +
|
" from information_schema.tables " +
|
" where table_schema='db_batt_history' " +
|
" AND TABLE_NAME like 'tb_batt_realdata_%' " +
|
" AND CREATE_TIME <= '"+Com.getDateTimeFormat(deldate, Com.DTF_YMDhms)+"';" ;
|
String sql_delete_strs = " DROP TABLE IF EXISTS ";
|
Sql_Mysql sql = new Sql_Mysql(conn_pool);
|
ResultSet res = null;
|
int count = 0;
|
try {
|
res = sql.sqlMysqlTotalQuery(sql_select_strs);
|
while(res.next()) {
|
if(count > 0) {
|
sql_delete_strs += ",";
|
}
|
|
sql_delete_strs += "db_batt_history." + res.getString("TABLE_NAME");
|
sql.logger.warn("删除："+res.getString("TABLE_NAME")+"\t at "+Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms));
|
count++;
|
}
|
if(count >0) {
|
sql.sqlMysqlTotalExecute(sql_delete_strs);
|
}
|
|
} catch (SQLException e) {
|
sql.logger.error("deleteHistoryData():" + e.toString(), e);
|
} finally {
|
if(null != res) {
|
try {
|
res.close();
|
} catch (SQLException e) {
|
sql.logger.error("deleteHistoryData():" + e.toString(), e);
|
}
|
}
|
sql.close_con();
|
}
|
}
|
|
/**
|
* 更新电池设备的Ukey信息
|
* @param m_ConnPool
|
* @param param
|
*/
|
public static void UpdateBattInfTable(MysqlConnPool m_ConnPool,FBS9100_StatAndParam param) {
|
String sql_str = " UPDATE " + Sql_Mysql.BattInf_Table + ""
|
+ " SET PublicKeyX = '" + param.publicKeyX /* +"',KeyID = '" + param.keyID */ + "',SerialNumber = '"+param.serialNumber+"'"
|
+ " WHERE FBSDeviceId = " + param.dev_id;
|
|
Sql_Mysql sql = new Sql_Mysql(m_ConnPool);
|
try {
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException e) {
|
sql.logger.error("BTS61850_Task_Thread_SQL.UpdateBattInfTable():" + e.toString(), e);
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
public static void updateBattRtState_Table(MysqlConnPool pool,FBS9100_VCData vc_data,BattData_RT[] battdata) {
|
for(int i=0;i<battdata.length;i++) {
|
if(null == battdata[i]) {
|
continue;
|
}
|
String sql_str = " UPDATE " + Sql_Mysql.BattRsAlarm_Table + ""
|
+ " SET record_time = '"+Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms)+"'"
|
+ ",groupvol_alm = " + vc_data.groupvolalm[i]
|
+ ",test_end_cap=" + vc_data.test_end_cap[i]
|
+ ",test_end_vol=" + vc_data.test_end_vol[i]
|
+ ",test_timelong= " + vc_data.test_timelong[i] +
|
" WHERE BattGroupId = " + battdata[i].BattGroupId;
|
Sql_Mysql sql = new Sql_Mysql(pool);
|
try {
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException e) {
|
sql.logger.error("BTS61850_Task_Thread_SQL.updateBattRtState_Table():" + e.toString(), e);
|
} finally {
|
sql.close_con();
|
}
|
|
}
|
}
|
|
/**
|
* 查询设备的uKeyID
|
* @param m_ConnPool
|
* @param dev_id
|
* @return
|
*/
|
public static String queryDeviceUkeyID(MysqlConnPool m_ConnPool, int dev_id) {
|
String ukeyID = "";
|
String sql_str = "SELECT KeyID FROM " + Sql_Mysql.BattInf_Table+ " WHERE FBSDeviceId = " + dev_id + " LIMIT 1";
|
Sql_Mysql sql = new Sql_Mysql(m_ConnPool);
|
ResultSet res = null;
|
try {
|
res = sql.sqlMysqlQuery(sql_str);
|
if(res.next()) {
|
ukeyID = res.getString("KeyID").trim();
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
if(null != res) {
|
try {
|
res.close();
|
} catch (SQLException e) {
|
sql.logger.error("BTS61850_Task_Thread_SQL.queryDeviceUkeyID():" + e.toString(), e);
|
}
|
}
|
sql.close_con();
|
}
|
return ukeyID;
|
}
|
|
/**
|
* 查询电源实时状态
|
* @param pool
|
* @param param
|
*/
|
public static void queryPwrdev_Acdcdata_Table(MysqlConnPool pool, FBS9100_StatAndParam param) {
|
String sql_str = "SELECT is_acIn1_powerdown,is_acIn2_powerdown,PowerDeviceId FROM " + Sql_Mysql.Pwrdev_Acdcdata_Table + " WHERE PowerDeviceId = " + param.PowerDeviceId + " LIMIT 1";
|
Sql_Mysql sql = new Sql_Mysql(pool);
|
ResultSet res = null;
|
try {
|
res = sql.sqlMysqlQuery(sql_str);
|
if(res.next()) {
|
boolean powerdown1 = res.getBoolean("is_acIn1_powerdown");
|
boolean powerdown2 = res.getBoolean("is_acIn2_powerdown");
|
if(powerdown1 || powerdown2) {
|
param.setPowerDown(true);
|
}else {
|
param.setPowerDown(false);
|
}
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
if(null != res) {
|
try {
|
res.close();
|
} catch (SQLException e) {
|
sql.logger.error("BTS61850_Task_Thread_SQL.queryPowerDataInf():" + e.toString(), e);
|
}
|
}
|
sql.close_con();
|
}
|
}
|
|
/**
|
* 查询电源信息
|
* @param pool
|
* @param param
|
*/
|
public static void queryPowerDataInf(MysqlConnPool pool, FBS9100_StatAndParam param) {
|
String sql_str = "SELECT PowerDeviceId FROM " + Sql_Mysql.Pwrdev_Inf_Table + " WHERE StationId = " + param.StationId + " LIMIT 1";
|
Sql_Mysql sql = new Sql_Mysql(pool);
|
ResultSet res = null;
|
try {
|
res = sql.sqlMysqlQuery(sql_str);
|
if(res.next()) {
|
param.PowerDeviceId = res.getInt("PowerDeviceId");
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
if(null != res) {
|
try {
|
res.close();
|
} catch (SQLException e) {
|
sql.logger.error("BTS61850_Task_Thread_SQL.queryPowerDataInf():" + e.toString(), e);
|
}
|
}
|
sql.close_con();
|
}
|
}
|
|
public static void main(String[] args) {
|
MysqlConnPool pool = new MysqlConnPool("192.168.10.79", 3360, 5);
|
|
FBS9100_StatAndParam param = new FBS9100_StatAndParam("127.0.0.1", 618500059);
|
param.StationId = "42010149";
|
|
queryPowerDataInf(pool, param);
|
queryPwrdev_Acdcdata_Table(pool, param);
|
|
|
System.out.println("PowerDeviceId:" + param.PowerDeviceId + " : " + param.isPowerOff);
|
}
|
}
|
