package com.battmonitor.data;
|
|
import java.sql.ResultSet;
|
import java.sql.SQLException;
|
import java.util.Date;
|
|
import com.battmonitor.base.Com;
|
import com.battmonitor.sql.MysqlConnPool;
|
import com.battmonitor.sql.Sql_Mysql;
|
|
public class BattData_RT_SQL {
|
/**
|
* ´´½¨tb_batt_rtdata±í²¢ÇÒ¸ù¾Ý¼¯ºÏbd_al£¬Ïò±íÖÐÌí¼Óµç³Ø×éidºÍmon_num Öµ
|
* @param conn_pool
|
* @param bd_al
|
*/
|
public static void createBattData_RT_RamDB_Table(MysqlConnPool conn_pool, int bg_id, int batt_cnt)
|
{
|
String tb_name = "`db_ram_batt_rt`.`tb_batt_rtdata_" + bg_id + "`";
|
String sql_str0 = "DELETE FROM " + tb_name + " WHERE " + tb_name + ".num>0";
|
String sql_str1 = "CREATE TABLE IF NOT EXISTS " + tb_name + " "
|
+ "( `num` BIGINT NOT NULL AUTO_INCREMENT, "
|
+ "`BattGroupId` INT NOT NULL DEFAULT 0, "
|
+ "`rec_datetime` DATETIME NOT NULL DEFAULT '1982-01-01 00:00:00', "
|
+ "`mon_num` INT NOT NULL DEFAULT 0, "
|
+ "`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, "
|
|
+ "`batt_count` INT NOT NULL DEFAULT 0, "
|
+ "`online_vol` FLOAT NOT NULL DEFAULT 0, "
|
+ "`group_vol` FLOAT NOT NULL DEFAULT 0, "
|
+ "`group_tmp` FLOAT NOT NULL DEFAULT 0, "
|
+ "`group_curr` FLOAT NOT NULL DEFAULT 0, "
|
+ "`batt_state` INT NOT NULL DEFAULT 0, "
|
+ "`batt_test_type` INT NOT NULL DEFAULT 0, "
|
+ "`batt_test_starttime` DATETIME NOT NULL DEFAULT '1982-01-01 00:00:00', "
|
+ "`batt_test_recordtime` DATETIME NOT NULL DEFAULT '1982-01-01 00:00:00', "
|
+ "`batt_test_tlong` INT NOT NULL DEFAULT 0, "
|
+ "`batt_test_cap` FLOAT NOT NULL DEFAULT 0, "
|
+ "`batt_real_cap` FLOAT NOT NULL DEFAULT 0, "
|
+ "`batt_rest_cap` FLOAT NOT NULL DEFAULT 0, "
|
+ "`batt_rest_power1_time` INT NOT NULL DEFAULT 0, "
|
+ "`batt_rest_power2_time` INT NOT NULL DEFAULT 0, "
|
+ "INDEX index_BattGroupId (`BattGroupId`), "
|
+ "PRIMARY KEY (`num`)) "
|
+ "ENGINE=MEMORY DEFAULT CHARSET=utf8;";
|
|
String sql_str2 = "INSERT INTO " + tb_name + " "
|
+ "(BattGroupId,"
|
+ "mon_num)"
|
+ " VALUES ";
|
int cnt = 0;
|
for(int mc=0; mc<batt_cnt; mc++) {
|
if(cnt > 0) {
|
sql_str2 += ",";
|
}
|
sql_str2 += "(" + bg_id + "," + (mc+1) + ")";
|
cnt++;
|
}
|
|
Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
|
try {
|
sql.sqlMysqlExecute(sql_str1);
|
ResultSet res_t = sql.sqlMysqlQuery("SELECT COUNT(*) FROM " + tb_name);
|
if(res_t.next()) {
|
int cnt_row = res_t.getInt(1);
|
if(cnt_row != batt_cnt) {
|
sql.sqlMysqlExecute(sql_str0);
|
sql.sqlMysqlExecute(sql_str2);
|
}
|
} else {
|
sql.sqlMysqlExecute(sql_str2);
|
}
|
} catch (SQLException e) {
|
sql.logger.error("BattData_RT_SQL.createBattData_RT_RamDB_Table():" + e.toString(), e);
|
} finally {
|
sql.close_con();
|
}
|
}
|
/************************************************************************************************/
|
/************************************************************************************************/
|
public static void updateBattState_RT_RamDB_Table(MysqlConnPool conn_pool, BattData_RT bd_rt)
|
{
|
Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
|
try
|
{
|
Date dt_test_Start = new Date();
|
Date dt_record = new Date();
|
dt_test_Start.setTime(bd_rt.getTestStartTime());
|
dt_record.setTime(bd_rt.getTestRecordTime());
|
String sql_str = "UPDATE " + Sql_Mysql.BattRtState_Table
|
+ " SET "
|
+ " rec_datetime='" + Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms) + "',"
|
+ " batt_count=" + bd_rt.MonCount + ","
|
+ " online_vol=" + String.format("%1.2f",bd_rt.getOnlineVol()) + ","
|
+ " group_vol=" + String.format("%1.2f",bd_rt.getGroupVol()) + ","
|
+ " group_tmp=" + bd_rt.getGroupTmp() + ","
|
+ " group_curr=" + String.format("%1.2f",bd_rt.getTestCurr()) + ","
|
+ " batt_state=" + bd_rt.getBattState() + ","
|
+ " batt_test_type=" + bd_rt.getBattTestType() + ","
|
+ " test_loadertype=" + bd_rt.mTestData.getLoaderType() + ","
|
+ " test_recordnum=" + bd_rt.mTestData.getTestRecordNum() + ","
|
+ " batt_test_starttime='" + Com.getDateTimeFormat(dt_test_Start, Com.DTF_YMDhms) + "',"
|
+ " batt_test_recordtime='" + Com.getDateTimeFormat(dt_record, Com.DTF_YMDhms) + "',"
|
+ " batt_test_tlong=" + bd_rt.getTestTimeLong() + ","
|
+ " batt_test_cap=" + bd_rt.getTestCap() + ","
|
+ " batt_real_cap=" + bd_rt.getBattRealCap() + ","
|
+ " batt_rest_cap=" + bd_rt.getBattRestCap() + ","
|
+ " a059_num=" + bd_rt.getA059_num() + ","
|
+ " batt_rest_power1_time=" + bd_rt.getBattRestTime()
|
+ " WHERE BattGroupId=" + bd_rt.BattGroupId;
|
|
//System.out.println(sql_str);
|
sql.sqlMysqlExecute(sql_str);
|
bd_rt.mTestData.updateNewDataRCTime(new Date());
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
sql.logger.error("BattData_RT_SQL.updateBattState_RT_RamDB_Table():" + e.toString(), e);
|
} finally {
|
sql.close_con();
|
}
|
}
|
/************************************************************************************************/
|
/************************************************************************************************/
|
public static void queryBattState_From_RT_Table(MysqlConnPool conn_pool, BattData_RT bd_rt)
|
{
|
Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
|
try
|
{
|
String sql_str = "SELECT * FROM " + Sql_Mysql.BattRtState_Table + " WHERE BattGroupId=" + bd_rt.BattGroupId;
|
//System.out.println(sql_str);
|
ResultSet rs_t = sql.sqlMysqlQuery(sql_str);
|
if(rs_t.next()) {
|
bd_rt.mTestData.battTestState = rs_t.getByte("batt_test_type");
|
if((BattStatData.BATTDATA_DISCHARGE == bd_rt.mTestData.battTestState)
|
|| (BattStatData.BATTDATA_CHARGE == bd_rt.mTestData.battTestState)) {
|
bd_rt.mTestData.battTestType_For_DataSave = bd_rt.mTestData.battTestState;
|
|
bd_rt.mTestData.testLoaderType = rs_t.getByte("test_loadertype");
|
bd_rt.mTestData.recordNum = rs_t.getInt("test_recordnum");
|
|
bd_rt.mTestData.onlineVol = rs_t.getFloat("online_vol");
|
bd_rt.mTestData.groupVol = rs_t.getFloat("group_vol");
|
bd_rt.mTestData.groupTmp = rs_t.getFloat("group_tmp");
|
|
bd_rt.mTestData.testCurr = rs_t.getFloat("group_curr");
|
bd_rt.mTestData.TestCurr_RT = bd_rt.mTestData.testCurr;
|
|
bd_rt.mTestData.battState = rs_t.getByte("batt_state");
|
bd_rt.mTestData.setStartTestTime(rs_t.getTimestamp("batt_test_starttime").getTime());
|
bd_rt.mTestData.setRecordTime(rs_t.getTimestamp("batt_test_recordtime").getTime());
|
bd_rt.mTestData.testTimeLong = rs_t.getInt("batt_test_tlong");
|
bd_rt.mTestData.testCap = rs_t.getFloat("batt_test_cap");
|
bd_rt.mTestData.battRealCap = rs_t.getFloat("batt_real_cap");
|
bd_rt.mTestData.battRestCap = rs_t.getFloat("batt_rest_cap");
|
bd_rt.mTestData.battRestTime = rs_t.getInt("batt_rest_power1_time");
|
}
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
sql.logger.error("BattData_RT_SQL.queryBattState_From_RT_Table():" + e.toString(), e);
|
} finally {
|
sql.close_con();
|
}
|
}
|
/************************************************************************************************/
|
/************************************************************************************************/
|
public static int queryTestRecordCountMax_From_tb_batttestdata_inf(MysqlConnPool conn_pool, int bg_id)
|
{
|
int rec_count = 1;
|
Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
|
try
|
{
|
String sql_str = "SELECT MAX(test_record_count) FROM " + Sql_Mysql.BattTestDataInf_Table
|
+ " WHERE BattGroupId=" + bg_id;
|
//System.out.println(sql_str);
|
ResultSet rs_t = sql.sqlMysqlQuery(sql_str);
|
if(rs_t.next()) {
|
rec_count = rs_t.getInt(1);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
sql.logger.error("BattData_RT_SQL.queryTestRecordCountMax_From_tb_batttestdata_inf():" + e.toString(), e);
|
} finally {
|
sql.close_con();
|
}
|
|
return rec_count;
|
}
|
/************************************************************************************************/
|
/**
|
* ¸üÐÂtb_batt_rtdata±íÖеÄÊý¾Ý
|
* @param conn_pool
|
* @param bd_al
|
*/
|
public static void updateBattData_RT_RamDB_Table(MysqlConnPool conn_pool, BattData_RT bd_rt)
|
{
|
Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
|
try
|
{
|
String sql_str = "UPDATE " + Sql_Mysql.BattRtData_Table
|
+ " SET "
|
+ " rec_datetime='" + Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms) + "'";
|
|
float tmp_dt = 0;
|
String sql_str_vol = " ";
|
for(int n=0; n<bd_rt.MonCount; n++) {
|
tmp_dt = bd_rt.al_MonVol.get(n).monVol;
|
//if(bd_rt.al_MonVol_RtOld.get(n).monVol != tmp_dt) {
|
bd_rt.al_MonVol_RtOld.get(n).monVol = tmp_dt;
|
sql_str_vol += " WHEN " + (n+1) + " THEN " + String.format("%1.3f", tmp_dt);
|
//}
|
}
|
if(sql_str_vol.trim().length() > 1) {
|
sql_str_vol = ", mon_vol = CASE mon_num " + sql_str_vol + " ELSE mon_vol END ";
|
}
|
|
String sql_str_tmp = " ";
|
for(int n=0; n<bd_rt.MonCount; n++) {
|
tmp_dt = bd_rt.al_MonVol.get(n).monTmp;
|
if(bd_rt.al_MonVol_RtOld.get(n).monTmp != tmp_dt) {
|
bd_rt.al_MonVol_RtOld.get(n).monTmp = tmp_dt;
|
sql_str_tmp += " WHEN " + (n+1) + " THEN " + String.format("%1.3f", tmp_dt);
|
}
|
}
|
if(sql_str_tmp.trim().length() > 1) {
|
sql_str_tmp = ", mon_tmp = CASE mon_num " + sql_str_tmp + " ELSE mon_tmp END ";
|
}
|
|
String sql_str_res = " ";
|
for(int n=0; n<bd_rt.MonCount; n++) {
|
tmp_dt = bd_rt.al_MonVol.get(n).monRes;
|
if(bd_rt.al_MonVol_RtOld.get(n).monRes != tmp_dt) {
|
bd_rt.al_MonVol_RtOld.get(n).monRes = tmp_dt;
|
sql_str_res += " WHEN " + (n+1) + " THEN " + String.format("%1.3f", tmp_dt);
|
}
|
}
|
if(sql_str_res.trim().length() > 1) {
|
sql_str_res = ", mon_res = CASE mon_num " + sql_str_res + " ELSE mon_res END ";
|
}
|
|
String sql_str_ser = " ";
|
for(int n=0; n<bd_rt.MonCount; n++) {
|
tmp_dt = bd_rt.al_MonVol.get(n).monSer;
|
if(bd_rt.al_MonVol_RtOld.get(n).monSer != tmp_dt) {
|
bd_rt.al_MonVol_RtOld.get(n).monSer = tmp_dt;
|
sql_str_ser += " WHEN " + (n+1) + " THEN " + String.format("%1.3f", tmp_dt);
|
}
|
}
|
if(sql_str_ser.trim().length() > 1) {
|
sql_str_ser = ", mon_ser = CASE mon_num " + sql_str_ser + " ELSE mon_ser END ";
|
}
|
|
String sql_str_jh_curr = " ";
|
for(int n=0; n<bd_rt.MonCount; n++) {
|
tmp_dt = bd_rt.al_MonVol.get(n).monJHcurr;
|
if(bd_rt.al_MonVol_RtOld.get(n).monJHcurr != tmp_dt) {
|
bd_rt.al_MonVol_RtOld.get(n).monJHcurr = tmp_dt;
|
sql_str_jh_curr += " WHEN " + (n+1) + " THEN " + String.format("%1.0f", tmp_dt);
|
}
|
}
|
if(sql_str_jh_curr.trim().length() > 1) {
|
sql_str_jh_curr = ", mon_JH_curr = CASE mon_num " + sql_str_jh_curr + " ELSE mon_JH_curr END ";
|
}
|
|
String sql_str_2 = sql_str_vol + sql_str_tmp + sql_str_res + sql_str_ser + sql_str_jh_curr;
|
if(sql_str_2.trim().length() > 1) {
|
sql_str_2 += " WHERE BattGroupId=" + bd_rt.BattGroupId;
|
/*
|
+ " AND mon_num IN (";
|
for(int n=0; n<brt.MonCount; n++) {
|
if(n > 0) {
|
sql_str_2 += ",";
|
}
|
sql_str_2 += (n+1);
|
}
|
sql_str_2 += ")";
|
*/
|
} else {
|
sql_str_2 = " WHERE BattGroupId=" + bd_rt.BattGroupId;
|
}
|
|
sql_str += sql_str_2;
|
//System.out.println(sql_str);
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
sql.logger.error("BattData_RT_SQL.updateBattData_RT_RamDB_Table():" + e.toString(), e);
|
} finally {
|
sql.close_con();
|
}
|
}
|
/************************************************************************************************/
|
public static void checkTbFbs9100StateIntegrity(MysqlConnPool conn_pool, long dev_id_t) {
|
Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
|
try {
|
boolean recreate_data_tag = false;
|
//-----------------------Sql_Mysql.FBS9100State_Table-------------------------//
|
String sql_str = "SELECT dev_id FROM " + Sql_Mysql.FBS9100State_Table
|
+ " WHERE dev_id=" + dev_id_t;
|
ResultSet res = sql.sqlMysqlQuery(sql_str);
|
if(false == res.next()) {
|
recreate_data_tag = true;
|
}
|
if(true == recreate_data_tag) {
|
sql_str = "INSERT INTO " + Sql_Mysql.FBS9100State_Table
|
+ " (dev_id) VALUES (" + dev_id_t + ")";
|
sql.logger.debug("BattData_RT_SQL.checkTbFbs9100StateIntegrity(): " + sql_str);
|
sql.sqlMysqlExecute(sql_str);
|
} else {
|
sql.logger.debug("BattData_RT_SQL.checkTbFbs9100StateIntegrity(): "
|
+ Sql_Mysql.FBS9100State_Table + ".dev_id:" + dev_id_t + " is exist");
|
}
|
} catch (SQLException e) {
|
sql.logger.error("BattData_RT_SQL.checkTbFbs9100StateIntegrity():" + e.toString(), e);
|
} finally {
|
sql.close_con();
|
}
|
}
|
/************************************************************************************************/
|
public static void checkTbBattDataIntegrity(MysqlConnPool conn_pool, int bg_id, int batt_cnt_t) {
|
Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
|
try {
|
boolean recreate_data_tag = false;
|
//------------------------Sql_Mysql.BattRtState_Table-------------------------//
|
String sql_str = "SELECT BattGroupId FROM " + Sql_Mysql.BattRtState_Table
|
+ " WHERE BattGroupId=" + bg_id;
|
ResultSet res = sql.sqlMysqlQuery(sql_str);
|
if(false == res.next()) {
|
recreate_data_tag = true;
|
}
|
if(true == recreate_data_tag) {
|
sql_str = "INSERT INTO " + Sql_Mysql.BattRtState_Table
|
+ " (BattGroupId) VALUES (" + bg_id + ")";
|
sql.logger.debug("BattData_RT_SQL.checkTbBattDataIntegrity(): " + sql_str);
|
sql.sqlMysqlExecute(sql_str);
|
} else {
|
sql.logger.debug("BattData_RT_SQL.checkTbBattDataIntegrity(): "
|
+ Sql_Mysql.BattRtState_Table + ".dev_id:" + bg_id + " is exist");
|
}
|
//----------------------------------------------------------------------------//
|
recreate_data_tag = false;
|
//------------------------Sql_Mysql.BattRtData_Table--------------------------//
|
sql_str = "SELECT COUNT(mon_num) FROM " + Sql_Mysql.BattRtData_Table
|
+ " WHERE BattGroupId=" + bg_id;
|
res = sql.sqlMysqlQuery(sql_str);
|
int len = 0;
|
if(res.next()) {
|
len = res.getInt(1);
|
if((len<1) || (len != batt_cnt_t)) {
|
recreate_data_tag = true;
|
}
|
}
|
|
if(false == recreate_data_tag) {
|
sql_str = "SELECT mon_num FROM " + Sql_Mysql.BattRtData_Table
|
+ " WHERE BattGroupId=" + bg_id
|
+ " ORDER BY mon_num ASC";
|
res = sql.sqlMysqlQuery(sql_str);
|
int index_num = 1;
|
while(res.next()) {
|
if(res.getInt(1) != index_num) {
|
recreate_data_tag = true;
|
break;
|
} else {
|
index_num += 1;
|
continue;
|
}
|
}
|
}
|
|
if(true == recreate_data_tag) {
|
sql_str = "DELETE FROM " + Sql_Mysql.BattRtData_Table
|
+ " WHERE BattGroupId=" + bg_id;
|
sql.logger.debug("BattData_RT_SQL.checkTbBattDataIntegrity(): " + sql_str);
|
sql.sqlMysqlExecute(sql_str);
|
|
int batt_cnt = batt_cnt_t;
|
if(batt_cnt > 500) {
|
batt_cnt = 500;
|
}
|
if(batt_cnt > 0) {
|
sql_str = "INSERT INTO " + Sql_Mysql.BattRtData_Table
|
+ " (BattGroupId, mon_num) VALUES ";
|
for(int n=0; n<batt_cnt; n++) {
|
sql_str += "(" + bg_id + "," + (n+1) + ")";
|
if(n < (batt_cnt-1)) {
|
sql_str += ",";
|
}
|
}
|
sql.logger.debug("BattData_RT_SQL.checkTbBattDataIntegrity(): " + sql_str);
|
sql.sqlMysqlExecute(sql_str);
|
}
|
} else {
|
sql.logger.debug("BattData_RT_SQL.checkTbBattDataIntegrity(): "
|
+ Sql_Mysql.BattRtData_Table + ".dev_id:" + bg_id + " is exist");
|
}
|
//----------------------------------------------------------------------------//
|
} catch (SQLException e) {
|
sql.logger.error("BattData_RT_SQL.checkTableIntegrity():" + e.toString(), e);
|
} finally {
|
sql.close_con();
|
}
|
}
|
/************************************************************************************************/
|
}
|
/************************************************************************************************/
|
