package com.dec.fbs9100;
|
|
import java.sql.ResultSet;
|
import java.sql.SQLException;
|
import java.text.SimpleDateFormat;
|
import java.util.ArrayList;
|
import java.util.Date;
|
import java.util.List;
|
|
import com.base.Com;
|
import com.battdata_rt.BattData_RT;
|
|
public class FBS9100_Task_Thread_SQL {
|
|
/**
|
* ������������ tb_fbs9100_state ��������������������id������������ip������������������������
|
* @param con_pool
|
* @param al_param
|
*/
|
public static void createFBS9100StateTableOnRam(MysqlConnPool con_pool, ArrayList<FBS9100_StatAndParam> al_param)
|
{
|
String str1 = "DROP TABLE IF EXISTS " + Sql_Mysql.FBS9100State_Table;
|
String str2 = "CREATE TABLE IF NOT EXISTS " + Sql_Mysql.FBS9100State_Table
|
+ " ( `num` BIGINT NOT NULL AUTO_INCREMENT, "
|
+ "`dev_ip` varchar(20) NOT NULL DEFAULT '127.0.0.0', "
|
+ "`dev_version` varchar(20) NOT NULL DEFAULT '0', "
|
+ "`dev_id` BIGINT NOT NULL DEFAULT 0, "
|
+ "`op_cmd` INT NOT NULL DEFAULT 0, "
|
+ "`record_datetime` varchar(20) NOT NULL DEFAULT '2015-12-02 12:10:00', "
|
+ "`dev_datetime` varchar(20) NOT NULL DEFAULT '2015-12-02 12:10:00', "
|
+ "`dev_testtype` INT NOT NULL DEFAULT 0, "
|
+ "`dev_testgroupnum` INT NOT NULL DEFAULT 0, "
|
+ "`dev_workstate` INT NOT NULL DEFAULT 0, "
|
+ "`dev_alarmstate` INT NOT NULL DEFAULT 0, "
|
+ "`dev_onlinevollow` BOOLEAN NOT NULL DEFAULT false, "
|
+ "`dev_eachgroup_battsum` INT NOT NULL DEFAULT 0, "
|
+ "`dev_captest_onlinevol` float NOT NULL DEFAULT 0, "
|
+ "`dev_captest_groupvol` float NOT NULL DEFAULT 0, "
|
+ "`dev_captest_curr` float NOT NULL DEFAULT 0, "
|
+ "`dev_captest_cap` float NOT NULL DEFAULT 0, "
|
+ "`dev_captest_timelong` INT NOT NULL DEFAULT 0, "
|
+ "`dev_restest_moncount` INT NOT NULL DEFAULT 0, "
|
+ "`dev_restest_monindex` INT NOT NULL DEFAULT 0, "
|
+ "`dev_commcount` INT NOT NULL DEFAULT 0, "
|
+ "`dev_errcommcount` INT NOT NULL DEFAULT 0, "
|
+ "`dev_rxnullerrcount` INT NOT NULL DEFAULT 0, "
|
+ "`dev_conresist` float NOT NULL DEFAULT 0, "
|
+ "`dev_concurr` float NOT NULL DEFAULT 0, "
|
+ "`dev_condvoldp` float NOT NULL DEFAULT 0, "
|
+ "`dev_condcurr` float NOT NULL DEFAULT 0, "
|
+ "`dev_61850alarms` varchar(20) NOT NULL DEFAULT '', "
|
+ " UNIQUE INDEX index_dev_id_uniq (`dev_id`), "
|
+ " PRIMARY KEY (`num`) ) "
|
+ " ENGINE=InnoDB DEFAULT CHARSET=utf8";
|
|
String str3 = "";
|
if(al_param.size() > 0) {
|
str3 = "INSERT INTO " + Sql_Mysql.FBS9100State_Table
|
+ " (dev_ip, dev_id) VALUES";
|
}
|
for(int n=0; n<al_param.size(); n++)
|
{
|
if(n > 0) {
|
str3 += ", ";
|
}
|
str3 += " ('" + al_param.get(n).dev_ipaddr + "'," + al_param.get(n).dev_id + ")";
|
}
|
|
Sql_Mysql sql = new Sql_Mysql(con_pool);
|
try {
|
sql.sqlMysqlExecute(str1);
|
sql.sqlMysqlExecute(str2);
|
if(str3.length() > 0) {
|
sql.sqlMysqlExecute(str3);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
sql.logger.error("FBS9100_Task_Thread_SQL.createFBS9100StateTableOnRam():" + e.toString(), e);
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
/**
|
* ������������ tb_fbs9100s_dcdc_data ��������������������id������������ip������������������������
|
* @param con_pool
|
* @param al_param
|
*/
|
public static void createFBS9100S_DCDC_DataTableOnRam(MysqlConnPool con_pool, ArrayList<FBS9100_StatAndParam> al_param)
|
{
|
String str1 = "DROP TABLE IF EXISTS " + Sql_Mysql.FBS9100S_DcDcState_Table;
|
String str2 = "CREATE TABLE IF NOT EXISTS " + Sql_Mysql.FBS9100S_DcDcState_Table
|
+ " ( `num` BIGINT NOT NULL AUTO_INCREMENT, "
|
+ "`dev_id` BIGINT NOT NULL DEFAULT 0, "
|
+ "`dcdc_num` BIGINT NOT NULL DEFAULT 0, "
|
+ "`dcdc_Vsys` float NOT NULL DEFAULT 0, "
|
+ "`dcdc_Vout` float NOT NULL DEFAULT 0, "
|
+ "`dcdc_Vbat` float NOT NULL DEFAULT 0, "
|
+ "`dcdc_Iout` float NOT NULL DEFAULT 0, "
|
+ "`dcdc_Ibuck` float NOT NULL DEFAULT 0, "
|
+ "`dcdc_Tmp_Dis` float NOT NULL DEFAULT 0, "
|
+ "`dcdc_Tmp_Chr` float NOT NULL DEFAULT 0, "
|
+ "`dcdc_SMR_Alarm1` INT NOT NULL DEFAULT 0, "
|
+ "`dcdc_SMR_Alarm2` INT NOT NULL DEFAULT 0, "
|
+ "`dcdc_version` INT NOT NULL DEFAULT 0, "
|
+ "`dcdc_ModeSetComm` INT NOT NULL DEFAULT 0, "
|
+ "`dcdc_RunSetComm` INT NOT NULL DEFAULT 0, "
|
+ "`dcdc_BuckISet` float NOT NULL DEFAULT 0, "
|
+ "`dcdc_DisVolSet` float NOT NULL DEFAULT 0, "
|
+ "`dcdc_DisCurrSet` float NOT NULL DEFAULT 0, "
|
+ "`dcdc_CommTxCnt` INT NOT NULL DEFAULT 0, "
|
+ "`dcdc_CommRxCnt` INT NOT NULL DEFAULT 0, "
|
+ " UNIQUE INDEX index_dcdc_num_uniq (`dcdc_num`), "
|
+ " PRIMARY KEY (`num`) ) "
|
+ " ENGINE=MEMORY DEFAULT CHARSET=utf8";
|
|
String str3 = "";
|
if(al_param.size() > 0) {
|
str3 = "INSERT INTO " + Sql_Mysql.FBS9100S_DcDcState_Table
|
+ " (dev_id) VALUES";
|
}
|
for(int n=0; n<al_param.size(); n++)
|
{
|
if(n > 0) {
|
str3 += ", ";
|
}
|
str3 += " (" + al_param.get(n).dev_id + ")";
|
}
|
|
Sql_Mysql sql = new Sql_Mysql(con_pool);
|
try {
|
sql.sqlMysqlExecute(str1);
|
sql.sqlMysqlExecute(str2);
|
if(str3.length() > 0) {
|
sql.sqlMysqlExecute(str3);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
sql.logger.error("FBS9100_Task_Thread_SQL.createFBS9100S_DCDC_DataTableOnRam():" + e.toString(), e);
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
/**
|
* ������������id������������ip�������������������������������������������
|
* @param con_pool
|
* @param al_param
|
*/
|
/*
|
public static void insertorupdateFBS9100StateTableOnRam(MysqlConnPool con_pool, FBS9100_StatAndParam param)
|
{
|
String str1 = "";
|
String str2 = "";
|
String str3 = "";
|
if(param != null) {
|
str1 = "SELECT num,dev_id,dev_errcommcount,dev_commcount from " +Sql_Mysql.FBS9100State_Table+ " where dev_id = " + param.dev_id;
|
str2 = "INSERT INTO " + Sql_Mysql.FBS9100State_Table
|
+ " (dev_ip, dev_id) VALUES";
|
str2 += " ('" + param.dev_ipaddr + "'," + param.dev_id + ")";
|
|
str3 = "UPDATE " +Sql_Mysql.FBS9100State_Table+ " SET dev_ip = '"+param.dev_ipaddr+"' where dev_id = " + param.dev_id;
|
}
|
|
Sql_Mysql sql = new Sql_Mysql(con_pool.getConn());
|
try {
|
if(param != null){
|
ResultSet rs = sql.sqlMysqlQuery(str1);
|
if(rs.next()){
|
param.m_FBS_VCData.m_SysState.ErrCommCount = rs.getInt("dev_errcommcount");
|
param.m_FBS_VCData.m_SysState.CommCount = rs.getInt("dev_commcount");
|
sql.sqlMysqlExecute(str3);
|
}else{
|
sql.sqlMysqlExecute(str2);
|
}
|
}
|
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
*/
|
/**
|
* ������������ tb_fbs9100_setparam ��������������������������id������������ip������������������������
|
* @param con_pool
|
* @param al_param
|
*/
|
public static void createFBS9100SetTestParamTableOnRam(MysqlConnPool con_pool, ArrayList<FBS9100_StatAndParam> al_param)
|
{
|
String str1 = "DROP TABLE IF EXISTS " + Sql_Mysql.FBS9100SetParam_Table;
|
String str2 = "CREATE TABLE IF NOT EXISTS " + Sql_Mysql.FBS9100SetParam_Table
|
+ " ( `num` BIGINT NOT NULL AUTO_INCREMENT, "
|
+ "`dev_ip` varchar(20) NOT NULL DEFAULT '127.0.0.0', "
|
+ "`dev_id` BIGINT NOT NULL DEFAULT 0, "
|
+ "`op_cmd` INT NOT NULL DEFAULT 0, "
|
+ "`TestCmd` INT NOT NULL DEFAULT 0, "
|
+ "`HourRate` INT NOT NULL DEFAULT 0, "
|
+ "`DisCurr` FLOAT NOT NULL DEFAULT 0, "
|
+ "`DisCap` FLOAT NOT NULL DEFAULT 0, "
|
+ "`DisTime` INT NOT NULL DEFAULT 0, "
|
+ "`GroupVol_LOW` FLOAT NOT NULL DEFAULT 0, "
|
+ "`MonomerVol_LOW` FLOAT NOT NULL DEFAULT 0, "
|
+ "`MonomerLowCount` INT NOT NULL DEFAULT 0, "
|
+ "`BattGroupNum` INT NOT NULL DEFAULT 0, "
|
+ "`OnlineVolLowAction` INT NOT NULL DEFAULT 0, "
|
+ "`DCVolHighLimit` FLOAT NOT NULL DEFAULT 0, "
|
+ "`ChargeCurrSet` FLOAT NOT NULL DEFAULT 0, "
|
+ " UNIQUE INDEX index_dev_id_uniq (`dev_id`), "
|
+ " PRIMARY KEY (`num`) ) "
|
+ " ENGINE=MEMORY DEFAULT CHARSET=utf8";
|
|
String str3 = "";
|
if(al_param.size() > 0) {
|
str3 = "INSERT INTO " + Sql_Mysql.FBS9100SetParam_Table
|
+ " (dev_ip, dev_id) VALUES";
|
}
|
for(int n=0; n<al_param.size(); n++)
|
{
|
if(n > 0) {
|
str3 += ", ";
|
}
|
str3 += " ('" + al_param.get(n).dev_ipaddr + "'," + al_param.get(n).dev_id + ")";
|
}
|
|
Sql_Mysql sql = new Sql_Mysql(con_pool);
|
try {
|
sql.sqlMysqlExecute(str1);
|
sql.sqlMysqlExecute(str2);
|
if(str3.length() > 0) {
|
sql.sqlMysqlExecute(str3);
|
}
|
} catch (SQLException e) {
|
sql.logger.error("FBS9100_Task_Thread_SQL.createFBS9100SetTestParamTableOnRam():" + e.toString(), e);
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
/**
|
* ������������id������������ip�������������������������������������������
|
* @param con_pool
|
* @param al_param
|
*/
|
/*
|
public static void insertorupdateFBS9100SetTestParamTableOnRam(MysqlConnPool con_pool, FBS9100_StatAndParam param)
|
{
|
String str1 = "";
|
String str2 = "";
|
String str3 = "";
|
if(param != null) {
|
str1 = "SELECT num,dev_id from " +Sql_Mysql.FBS9100SetParam_Table+ " where dev_id = " + param.dev_id;
|
|
str2 = "INSERT INTO " + Sql_Mysql.FBS9100SetParam_Table
|
+ " (dev_ip, dev_id) VALUES";
|
|
str2 += " ('" + param.dev_ipaddr + "'," + param.dev_id + ")";
|
|
str3 = "UPDATE " +Sql_Mysql.FBS9100SetParam_Table+ " SET dev_ip = '"+param.dev_ipaddr+"' where dev_id = " + param.dev_id;
|
}
|
|
|
Sql_Mysql sql = new Sql_Mysql(con_pool.getConn());
|
try {
|
if(param != null){
|
ResultSet rs = sql.sqlMysqlQuery(str1);
|
if(rs.next()){
|
sql.sqlMysqlExecute(str3);
|
}else{
|
sql.sqlMysqlExecute(str2);
|
}
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
*/
|
|
|
/*******************************website*************************************/
|
public static void updateAppStartTime(MysqlConnPool con_pool,double version){
|
Sql_Mysql sql = new Sql_Mysql(con_pool);
|
|
Date day=new Date();
|
SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
|
String str1 = " UPDATE `web_site`.`tb_process_survey` SET ProcessTime="+"'"
|
+df.format(day)+"'"+", Process_starttime="+"'"+df.format(day)+"'"
|
+ ", ProcessVersion='NW_V" + version + "' WHERE "
|
+ "ProcessName='BMS_FBS61850_BATT' AND "
|
+ "ServerName='BMS_FBS61850_BATT'";
|
try {
|
sql.sqlMysqlUseDB("web_site");
|
|
sql.sqlMysqlUpdate(str1);
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
sql.logger.error("FBS9100_Task_Thread_SQL.updateAppStartTime():" + e.toString(), e);
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
public static void runinsertime(MysqlConnPool con_pool){ //record runtime
|
Sql_Mysql sql = new Sql_Mysql(con_pool);
|
String str2 = " UPDATE web_site.tb_process_survey SET ProcessTime=" + "'" +
|
Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms)+"'"
|
+ " WHERE ProcessName='BMS_FBS61850_BATT' AND "
|
+ "ServerName='BMS_FBS61850_BATT'";
|
try {
|
sql.sqlMysqlUseDB("web_site");
|
sql.sqlMysqlUpdate(str2);
|
} catch (SQLException e) {
|
//ConsoleClient.connection(args);
|
sql.logger.error("FBS9100_Task_Thread_SQL.runinsertime():" + e.toString(), e);
|
}finally{
|
sql.close_con();
|
}
|
}
|
/**
|
* ������������ Sql_Mysql.RamDB_CMCC_POWER_Data ��������������������id������������������������
|
* @param con_pool
|
* @param al_param
|
*/
|
public static void createFBS9100CmccPowerDataOnRam(MysqlConnPool con_pool, ArrayList<FBS9100_StatAndParam> al_param)
|
{
|
String str1 = "DROP TABLE IF EXISTS " + Sql_Mysql.RamDB_CMCC_POWER_Data;
|
String str2 = "CREATE TABLE IF NOT EXISTS " + Sql_Mysql.RamDB_CMCC_POWER_Data
|
+ " ( `num` BIGINT NOT NULL AUTO_INCREMENT, "
|
+ "`dev_id` BIGINT NOT NULL DEFAULT 0, "
|
+ "`vol1` float NOT NULL DEFAULT 0, "
|
+ "`vol2` float NOT NULL DEFAULT 0, "
|
+ "`vol3` float NOT NULL DEFAULT 0, "
|
+ "`vol4` float NOT NULL DEFAULT 0, "
|
+ "`vol5` float NOT NULL DEFAULT 0, "
|
+ "`curr1` float NOT NULL DEFAULT 0, "
|
+ "`curr2` float NOT NULL DEFAULT 0, "
|
+ "`curr3` float NOT NULL DEFAULT 0, "
|
+ "`curr4` float NOT NULL DEFAULT 0, "
|
+ "`curr5` float NOT NULL DEFAULT 0, "
|
+ " UNIQUE INDEX index_dev_id_uniq (`dev_id`), "
|
+ " PRIMARY KEY (`num`) ) "
|
+ " ENGINE=InnoDB DEFAULT CHARSET=utf8";
|
|
String str3 = "";
|
if(al_param.size() > 0) {
|
str3 = "INSERT INTO " + Sql_Mysql.RamDB_CMCC_POWER_Data
|
+ " (dev_id) VALUES";
|
}
|
for(int n=0; n<al_param.size(); n++)
|
{
|
if(n > 0) {
|
str3 += ", ";
|
}
|
str3 += " (" + al_param.get(n).dev_id + ")";
|
}
|
|
Sql_Mysql sql = new Sql_Mysql(con_pool);
|
try {
|
sql.sqlMysqlExecute(str1);
|
sql.sqlMysqlExecute(str2);
|
if(str3.length() > 0) {
|
sql.sqlMysqlExecute(str3);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
sql.logger.error("FBS9100_Task_Thread_SQL.createFBS9100CmccPowerDataOnRam():" + e.toString(), e);
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
/**
|
* ������������id������������ip�������������������������������������������
|
* @param con_pool
|
* @param al_param
|
*/
|
/*
|
public static void insertorupdateFBS9100CmccPowerDataTableOnRam(MysqlConnPool con_pool, FBS9100_StatAndParam param)
|
{
|
String str1 = "";
|
String str2 = "";
|
if(param != null) {
|
str1 = "SELECT num,dev_id from " +Sql_Mysql.RamDB_CMCC_POWER_Data+ " where dev_id = " + param.dev_id;
|
str2 = "INSERT INTO " + Sql_Mysql.RamDB_CMCC_POWER_Data
|
+ " (dev_id) VALUES";
|
str2 += " (" + param.dev_id + ")";
|
}
|
|
Sql_Mysql sql = new Sql_Mysql(con_pool.getConn());
|
try {
|
if(param != null) {
|
ResultSet rs = sql.sqlMysqlQuery(str1);
|
if(false == rs.next()) {
|
sql.sqlMysqlExecute(str2);
|
}
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
*/
|
/***********************************************************************************/
|
/***********************************************************************************/
|
|
/**
|
* ������������������id������������ tb_fbs9100_state ������
|
* @param con_pool
|
* @param v_data
|
* @param dev_id
|
*/
|
/*
|
public static void updateFbs9100CmccPowerDataByDev_Id(MysqlConnPool con_pool, FBS9100_CMCC_Power cmcc_data, int dev_id)
|
{
|
String sql_str_base = Sql_Mysql.RamDB_CMCC_POWER_Data
|
+ " SET "
|
+ "dev_id=" + dev_id + ", "
|
+ "vol1=" + cmcc_data.Vol[0] + ", "
|
+ "vol2=" + cmcc_data.Vol[1] + ", "
|
+ "vol3=" + cmcc_data.Vol[2] + ", "
|
+ "vol4=" + cmcc_data.Vol[3] + ", "
|
+ "vol5=" + cmcc_data.Vol[4] + ", "
|
+ "curr1=" + cmcc_data.Curr[0] + ", "
|
+ "curr2=" + cmcc_data.Curr[1] + ", "
|
+ "curr3=" + cmcc_data.Curr[2] + ", "
|
+ "curr4=" + cmcc_data.Curr[3] + ", "
|
+ "curr5=" + cmcc_data.Curr[4];
|
//+ " WHERE dev_id=" + dev_id;
|
//String sql_str_update = "UPDATE " + sql_str_base;
|
String sql_str_replace = "REPLACE INTO " + sql_str_base;
|
Sql_Mysql sql = new Sql_Mysql(con_pool.getConn());
|
try {
|
//System.out.println(sql_str);
|
sql.sqlMysqlExecute(sql_str_replace);
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
//e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
*/
|
/***********************************************************************************/
|
|
/**
|
* 更新数据库表 tb_fbs9100_state 更新61850设备工作状态
|
* @param con_pool
|
* @param v_data
|
* @param dev_id
|
*/
|
public static void updateFbs9100StateByDev_Id(MysqlConnPool con_pool, FBS9100_StatAndParam m_pm)
|
{
|
FBS9100_VCData v_data = m_pm.m_FBS_VCData;
|
int TestGroup_index = v_data.m_SysState.TestGroupNum;
|
if(TestGroup_index > 0) {
|
TestGroup_index -= 1;
|
}
|
if((TestGroup_index>=FBS9100_ComBase.BattGroupCountMax) || (TestGroup_index<0)){
|
TestGroup_index = 0;
|
}
|
|
String sql_str_base = Sql_Mysql.FBS9100State_Table
|
+ " SET "
|
+ "dev_id=" + m_pm.dev_id + ", "
|
+ "dev_ip='" + m_pm.dev_ipaddr + "', "
|
+ "dev_version='" + m_pm.m_DevVersion + "', "
|
+ "record_datetime="
|
+ "'" + Com.getDateTimeFormat(v_data.m_SysState.Record_DateTime, Com.DTF_YMDhms) + "',"
|
+ "dev_datetime='" + v_data.m_SysState.DTime.year + "-"
|
+ v_data.m_SysState.DTime.month + "-"
|
+ v_data.m_SysState.DTime.day + " "
|
+ v_data.m_SysState.DTime.hour + ":"
|
+ v_data.m_SysState.DTime.minute + ":"
|
+ v_data.m_SysState.DTime.second + "', "
|
+ "dev_testtype=" + v_data.m_SysState.TestType+ ", "
|
+ "dev_testgroupnum=" + v_data.m_SysState.TestGroupNum + ", "
|
+ "dev_workstate=" + v_data.m_SysState.WorkState + ", "
|
+ "dev_alarmstate=" + v_data.m_SysState.AlarmState + ", "
|
+ "dev_temp=" + v_data.m_SysState.temp + ", "
|
+ "dev_onlinevollow=" + v_data.m_SysState.OnlineVolLow + ", "
|
+ "dev_eachgroup_battsum=" +v_data.m_SysState.eachgroup_battsum + ", "
|
+ "dev_captest_onlinevol=" + v_data.onlinevol[TestGroup_index] + ", "
|
+ "dev_captest_groupvol=" + v_data.groupvol[TestGroup_index] + ", "
|
+ "dev_captest_curr=" + v_data.battcurr[TestGroup_index] + ", "
|
+ "dev_captest_cap=" + v_data.battcap[TestGroup_index] + ", "
|
+ "dev_captest_timelong=" + v_data.testTime.getSecondCount() + ", "
|
+ "dev_restest_monindex=" + v_data.m_SysState.restest_monindex + ", "
|
+ "dev_restest_moncount=" + v_data.m_SysState.restest_moncount + ", "
|
+ "dev_restest_count=" + v_data.m_SysState.resttest_count + ", "
|
+ "dev_commcount=" + v_data.m_SysState.CommCount + ", "
|
+ "dev_errcommcount=" + v_data.m_SysState.ErrCommCount + ", "
|
+ "dev_rxnullerrcount=" + v_data.m_SysState.RxNullErrCount + ", "
|
+ "dev_last_captest_stop_type=" + v_data.m_SysState.CapTestStopType + ", "
|
+ "dev_conresist=" + v_data.m_SysState.conresist + ", "
|
+ "dev_concurr=" + v_data.m_SysState.concurr + ", "
|
+ "dev_condvoldp=" + v_data.m_SysState.condvoldp + ", "
|
+ "dev_condcurr=" + v_data.m_SysState.condcurr + ", "
|
+ "dev_61850alarms= '" +v_data.m_SysState.dev_61850alam+"' ";
|
|
|
String sql_str_update = "UPDATE " + sql_str_base + " WHERE dev_id=" + m_pm.dev_id;
|
String sql_str_replace = "REPLACE INTO " + sql_str_base;
|
|
if(v_data.m_SysState.CapTestStopType>=0 && v_data.m_SysState.CapTestStopType<=256) {
|
Sql_Mysql sql = new Sql_Mysql(con_pool);
|
ResultSet res = null;
|
try {
|
res = sql.sqlMysqlQuery("SELECT num FROM " + Sql_Mysql.FBS9100State_Table + " WHERE dev_id = " + m_pm.dev_id);
|
|
if(res.next()) {
|
//System.out.println(sql_str_update);
|
sql.sqlMysqlExecute(sql_str_update);
|
} else {
|
sql.sqlMysqlExecute(sql_str_replace);
|
}
|
} catch (SQLException e) {
|
sql.logger.error("FBS9100_Task_Thread_SQL.updateFbs9100StateByDev_Id():" + e.toString(), e);
|
} finally {
|
if(null != res) {
|
try {
|
res.close();
|
} catch (SQLException e) {
|
sql.logger.error("FBS9100_Task_Thread_SQL.updateFbs9100StateByDev_Id():" + e.toString(), e);
|
}
|
}
|
sql.close_con();
|
}
|
}
|
}
|
/***********************************************************************************/
|
|
|
/*
|
* 更新指定设备除硫模块的工作状态
|
*/
|
public static void inseartOrUpdateFBS9100SFODState(MysqlConnPool con_pool, FBS9100_StatAndParam m_pm,FBS9100s_Fod_State fodstate){
|
int dev_id = m_pm.dev_id;
|
String sql_str_base = Sql_Mysql.FBS9100sFodState_Table
|
+ " SET "
|
+ "dev_id=" + dev_id + ", "
|
+ "BattGroupId=" + m_pm.BattGroupId + ", "
|
+ "record_time='" + Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms) + "', "
|
+ "RestTime_one=" + fodstate.RestTime_one + ", "
|
+ "WorkState_one=" + fodstate.WorkState_one + ", "
|
+ "VGroupVol_one=" + fodstate.VGroupVol_one + ", "
|
+ "VpeakVol_one=" + fodstate.VpeakVol_one + ","
|
+ "RestTime_two=" + fodstate.RestTime_two + ", "
|
+ "WorkState_two=" + fodstate.WorkState_two + ", "
|
+ "VGroupVol_two=" + fodstate.VGroupVol_two + ", "
|
+ "VpeakVol_two=" + fodstate.VpeakVol_two + ","
|
+ "RestTime_three=" + fodstate.RestTime_three + ", "
|
+ "WorkState_three=" + fodstate.WorkState_three + ", "
|
+ "VGroupVol_three=" + fodstate.VGroupVol_three + ", "
|
+ "VpeakVol_three=" + fodstate.VpeakVol_three + ","
|
+ "RestTime_four=" + fodstate.RestTime_four + ", "
|
+ "WorkState_four=" + fodstate.WorkState_four + ", "
|
+ "VGroupVol_four=" + fodstate.VGroupVol_four + ", "
|
+ "VpeakVol_four=" + fodstate.VpeakVol_four;
|
|
String sql_str_update = "UPDATE " + sql_str_base + " WHERE dev_id=" + m_pm.dev_id;
|
String sql_str_replace = "REPLACE INTO " + sql_str_base;
|
|
Sql_Mysql sql = new Sql_Mysql(con_pool);
|
ResultSet res = null;
|
try {
|
res = sql.sqlMysqlQuery("SELECT num FROM " + Sql_Mysql.FBS9100sFodState_Table + " WHERE dev_id = " + m_pm.dev_id);
|
|
if(res.next()) {
|
sql.sqlMysqlExecute(sql_str_update);
|
} else {
|
sql.sqlMysqlExecute(sql_str_replace);
|
}
|
} catch (SQLException e) {
|
sql.logger.error("FBS9100_Task_Thread_SQL.inseartOrUpdateFBS9100SFODState():" + e.toString(), e);
|
} finally {
|
if(null != res) {
|
try {
|
res.close();
|
} catch (SQLException e) {
|
// e.printStackTrace();
|
sql.logger.error("FBS9100_Task_Thread_SQL.inseartOrUpdateFBS9100SFODState():" + e.toString(), e);
|
}
|
}
|
sql.close_con();
|
}
|
}
|
|
/***********************************************************************************/
|
/**
|
* ������������������id������������ tb_fbs9100s_dcdc_data ������
|
* @param con_pool
|
* @param v_data
|
* @param dev_id
|
*/
|
/*
|
public static void updateFbs9100S_DCDC_Data_ByDev_Id(MysqlConnPool con_pool,
|
FBS9100S_NT_DCDC_Data[] dcdc_data, int dev_id)
|
{
|
for(int n=0; n<dcdc_data.length; n++) {
|
if(0 == dcdc_data[n].Version) {
|
continue;
|
}
|
|
String sql_str_base = Sql_Mysql.FBS9100S_DcDcState_Table
|
+ " SET "
|
+ "dev_id=" + dev_id + ", "
|
+ "dcdc_num=" + ((dev_id%1000000)*10 + (n+1)) + ", "
|
+ "dcdc_Vsys=" + dcdc_data[n].Vsys + ", "
|
+ "dcdc_Vout=" + dcdc_data[n].Vout + ", "
|
+ "dcdc_Vbat=" + dcdc_data[n].Vbat + ", "
|
+ "dcdc_Iout=" + dcdc_data[n].Iout + ", "
|
+ "dcdc_Ibuck=" + dcdc_data[n].Ibuck + ", "
|
+ "dcdc_Tmp_Dis=" + dcdc_data[n].Tmp_Dis + ", "
|
+ "dcdc_Tmp_Chr=" + dcdc_data[n].Tmp_Chr + ", "
|
+ "dcdc_SMR_Alarm1=" + dcdc_data[n].SMR_Alarm1 + ", "
|
+ "dcdc_SMR_Alarm2=" + dcdc_data[n].SMR_Alarm2 + ", "
|
+ "dcdc_version=" + dcdc_data[n].Version + ", "
|
+ "dcdc_ModeSetComm=" + dcdc_data[n].ModeSetComm + ", "
|
+ "dcdc_RunSetComm=" + dcdc_data[n].RunSetComm + ", "
|
+ "dcdc_BuckISet=" + dcdc_data[n].SetBuckI + ", "
|
+ "dcdc_DisVolSet=" + dcdc_data[n].SetDisVol + ", "
|
+ "dcdc_DisCurrSet=" + dcdc_data[n].SetDisCurr + ", "
|
+ "dcdc_CommTxCnt=" + dcdc_data[n].CommTxCnt + ", "
|
+ "dcdc_CommRxCnt=" + dcdc_data[n].CommRxCnt;
|
|
String sql_str_replace = "REPLACE INTO " + sql_str_base;
|
|
Sql_Mysql sql = new Sql_Mysql(con_pool.getConn());
|
try {
|
//System.out.println(sql_str);
|
sql.sqlMysqlExecute(sql_str_replace);
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
//e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
}
|
*/
|
/***********************************************************************************/
|
/***********************************************************************************/
|
/**
|
* ������������tb_fbs9100_setparam���������������al_param������������
|
* @param con_pool
|
* @param al_param
|
*/
|
public static void queryFbs9100SetParam(MysqlConnPool con_pool, ArrayList<FBS9100_StatAndParam> al_param)
|
{
|
String sql_str = "SELECT * FROM " + Sql_Mysql.FBS9100SetParam_Table;
|
Sql_Mysql sql = new Sql_Mysql(con_pool);
|
ResultSet res = null;
|
try {
|
res = sql.sqlMysqlQuery(sql_str);
|
while(res.next())
|
{
|
FBS9100_StatAndParam param = null;
|
int devid = res.getInt("dev_id");
|
|
for(int n=0; n<al_param.size(); n++) {
|
param = al_param.get(n);
|
if(devid == param.dev_id) {
|
break;
|
}
|
}
|
|
if(null != param) {//使用结果集装载数据，可以改为返回LIST，使用LIST模型装载数据。
|
param.m_FBS_DiscParam.op_cmd = res.getInt("op_cmd");
|
param.m_FBS_DiscParam.BattGroupNum = res.getInt("BattGroupNum");
|
param.m_FBS_DiscParam.test_cmd = res.getInt("TestCmd");
|
param.m_FBS_DiscParam.HourRate = res.getInt("HourRate");
|
param.m_FBS_DiscParam.DisCurr = res.getFloat("DisCurr");
|
param.m_FBS_DiscParam.DisCap = res.getFloat("DisCap");
|
param.m_FBS_DiscParam.DisTime = res.getInt("DisTime");
|
param.m_FBS_DiscParam.GroupVol_LOW = res.getFloat("GroupVol_LOW");
|
param.m_FBS_DiscParam.MonomerVol_LOW = res.getFloat("MonomerVol_LOW");
|
param.m_FBS_DiscParam.MonomerLowCount = res.getInt("MonomerLowCount");
|
param.m_FBS_DiscParam.MonomerTmp_High = res.getFloat("MonomerTmp_High");
|
//System.out.println( "TMP:"+res.getFloat("MonomerTmp_High"));
|
param.m_FBS_DiscParam.OnlineVolLowAction = res.getInt("OnlineVolLowAction");
|
param.m_FBS_DiscParam.DCVolHighLimit = res.getFloat("DCVolHighLimit");
|
param.m_FBS_DiscParam.ChargeCurrSet = res.getFloat("ChargeCurrSet");
|
}
|
}
|
} catch (SQLException e) {
|
sql.logger.error("FBS9100_Task_Thread_SQL.queryFbs9100SetParam():" + e.toString(), e);
|
} finally {
|
if(null != res) {
|
try {
|
res.close();
|
} catch (SQLException e) {
|
sql.logger.error("FBS9100_Task_Thread_SQL.queryFbs9100SetParam():" + e.toString(), e);
|
}
|
}
|
sql.close_con();
|
}
|
}
|
|
|
/**
|
* ������������tb_fbs9100_setparam���������������al_param������������
|
* @param con_pool
|
* @param al_param
|
*/
|
public static void queryFbs9100SetParamBydev_id(MysqlConnPool con_pool, FBS9100_StatAndParam param)
|
{
|
String sql_str = "SELECT * FROM " + Sql_Mysql.FBS9100SetParam_Table + " where dev_id = " + param.dev_id;
|
Sql_Mysql sql = new Sql_Mysql(con_pool);
|
ResultSet res = null;
|
try {
|
res = sql.sqlMysqlQuery(sql_str);
|
if(res.next())
|
{
|
if(null != param) {
|
param.m_FBS_DiscParam.op_cmd = res.getInt("op_cmd");
|
param.m_FBS_DiscParam.BattGroupNum = res.getInt("BattGroupNum");
|
param.m_FBS_DiscParam.test_cmd = res.getInt("TestCmd");
|
param.m_FBS_DiscParam.HourRate = res.getInt("HourRate");
|
param.m_FBS_DiscParam.DisCurr = res.getFloat("DisCurr");
|
param.m_FBS_DiscParam.DisCap = res.getFloat("DisCap");
|
param.m_FBS_DiscParam.DisTime = res.getInt("DisTime");
|
param.m_FBS_DiscParam.GroupVol_LOW = res.getFloat("GroupVol_LOW");
|
param.m_FBS_DiscParam.MonomerVol_LOW = res.getFloat("MonomerVol_LOW");
|
param.m_FBS_DiscParam.MonomerLowCount = res.getInt("MonomerLowCount");
|
//System.out.println( "LOW:"+res.getFloat("GroupVol_LOW"));
|
|
param.m_FBS_DiscParam.MonomerTmp_High = res.getFloat("MonomerTmp_High");
|
//System.out.println( "TMP:"+res.getFloat("MonomerTmp_High"));
|
|
param.m_FBS_DiscParam.OnlineVolLowAction = res.getInt("OnlineVolLowAction");
|
param.m_FBS_DiscParam.DCVolHighLimit = res.getFloat("DCVolHighLimit");
|
param.m_FBS_DiscParam.ChargeCurrSet = res.getFloat("ChargeCurrSet");
|
|
param.m_FBS_DiscParam.OffLineYH_Cycle = res.getInt("OffLineYH_Cycle");
|
param.m_FBS_DiscParam.OnLineVol_Low = res.getFloat("OnLineVol_Low");
|
|
param.m_FBS_DiscParam.OffLineYHstarttime = res.getTimestamp("OffLineYHstarttime");
|
param.m_FBS_DiscParam.OffLineYHTimes = res.getInt("OffLineYHTimes");
|
param.m_FBS_DiscParam.OffLineYHOnceCycle = res.getInt("OffLineYHOnceCycle");
|
|
}
|
}
|
} catch (SQLException e) {
|
sql.logger.error("FBS9100_Task_Thread_SQL.queryFbs9100SetParamBydev_id():" + e.toString(), e);
|
} finally {
|
if(null != res) {
|
try {
|
res.close();
|
} catch (SQLException e) {
|
sql.logger.error("FBS9100_Task_Thread_SQL.queryFbs9100SetParamBydev_id():" + e.toString(), e);
|
}
|
}
|
sql.close_con();
|
}
|
}
|
|
/****************************************************************************************************/
|
public static void inseartOrUpdateFBS9100SFODParam(MysqlConnPool con_pool,CL_Param fodparam,FBS9100_StatAndParam param){
|
String sql_str_base = Sql_Mysql.FBS9100SetDLParam_Table
|
+ " SET "
|
+ "dev_id=" + param.dev_id + ", "
|
//+ "op_cmd=" + fodparam.op_cmd + ", "
|
+ "GroupNum=" + fodparam.GroupNum + ", "
|
+ "STDCap_one=" + fodparam.STDCap_one + ", "
|
+ "FloatVol_one=" + fodparam.FloatVol_one + ", "
|
+ "Strength_one=" + fodparam.Strength_one + ", "
|
+ "YHStrength_one=" + fodparam.YHStrength_one + ", "
|
+ "TimeLong_one=" + fodparam.TimeLong_one + ", "
|
+ "STDCap_two=" + fodparam.STDCap_two + ", "
|
+ "FloatVol_two=" + fodparam.FloatVol_two + ", "
|
+ "Strength_two=" + fodparam.Strength_two + ", "
|
+ "YHStrength_two=" + fodparam.YHStrength_two + ", "
|
+ "TimeLong_two=" + fodparam.TimeLong_two + ","
|
+ "STDCap_three=" + fodparam.STDCap_three + ", "
|
+ "FloatVol_three=" + fodparam.FloatVol_three + ", "
|
+ "Strength_three=" + fodparam.Strength_three + ", "
|
+ "YHStrength_three=" + fodparam.YHStrength_three + ", "
|
+ "TimeLong_three=" + fodparam.TimeLong_three + ","
|
+ "STDCap_four=" + fodparam.STDCap_four + ", "
|
+ "FloatVol_four=" + fodparam.FloatVol_four + ", "
|
+ "Strength_four=" + fodparam.Strength_four + ", "
|
+ "YHStrength_four=" + fodparam.YHStrength_four + ", "
|
+ "TimeLong_four=" + fodparam.TimeLong_four;
|
|
String sql_str_update = "UPDATE " + sql_str_base + " WHERE dev_id=" + param.dev_id;
|
String sql_str_replace = "REPLACE INTO " + sql_str_base;
|
|
Sql_Mysql sql = new Sql_Mysql(con_pool);
|
ResultSet res = null;
|
try {
|
res = sql.sqlMysqlQuery("SELECT num FROM " + Sql_Mysql.FBS9100SetDLParam_Table
|
+ " WHERE dev_id = " + param.dev_id);
|
|
if(res.next()) {
|
sql.sqlMysqlExecute(sql_str_update);
|
} else {
|
sql.sqlMysqlExecute(sql_str_replace);
|
}
|
} catch (SQLException e) {
|
sql.logger.error("FBS9100_Task_Thread_SQL.inseartOrUpdateFBS9100SFODParam():" + e.toString(), e);
|
} finally {
|
if(null != res) {
|
try {
|
res.close();
|
} catch (SQLException e) {
|
sql.logger.error("FBS9100_Task_Thread_SQL.inseartOrUpdateFBS9100SFODParam():" + e.toString(), e);
|
}
|
}
|
sql.close_con();
|
}
|
}
|
/****************************************************************************************************/
|
public static void queryFbsCL9100SetParamBydev_id(MysqlConnPool con_pool, FBS9100_StatAndParam param)
|
{
|
//System.out.println(param.dev_id);
|
String sql_str = "SELECT * FROM " + Sql_Mysql.FBS9100SetDLParam_Table + " where dev_id = " + param.dev_id;
|
Sql_Mysql sql = new Sql_Mysql(con_pool);
|
ResultSet res = null;
|
try {
|
res = sql.sqlMysqlQuery(sql_str);
|
if(res.next())
|
{
|
if(null != param) {
|
param.clParam.op_cmd = res.getInt("op_cmd");
|
param.clParam.test_cmd = res.getInt("test_cmd");
|
param.clParam.GroupNum =res.getInt("GroupNum");
|
param.clParam.STDCap_one = res.getInt("STDCap_one");
|
param.clParam.FloatVol_one = res.getFloat("FloatVol_one");
|
param.clParam.Strength_one = res.getFloat("Strength_one");
|
param.clParam.YHStrength_one = res.getFloat("YHStrength_one");
|
param.clParam.TimeLong_one= res.getInt("TimeLong_one");
|
|
param.clParam.STDCap_two = res.getInt("STDCap_two");
|
param.clParam.Strength_two = res.getFloat("Strength_two");
|
param.clParam.YHStrength_two = res.getFloat("YHStrength_two");
|
param.clParam.TimeLong_two= res.getInt("TimeLong_two");
|
param.clParam.FloatVol_two = res.getFloat("FloatVol_two");
|
|
param.clParam.STDCap_three = res.getInt("STDCap_three");
|
param.clParam.FloatVol_three = res.getFloat("FloatVol_three");
|
param.clParam.Strength_three = res.getFloat("Strength_three");
|
param.clParam.YHStrength_three = res.getFloat("YHStrength_three");
|
param.clParam.TimeLong_three= res.getInt("TimeLong_three");
|
|
param.clParam.STDCap_four = res.getInt("STDCap_four");
|
param.clParam.FloatVol_four = res.getFloat("FloatVol_four");
|
param.clParam.Strength_four = res.getFloat("Strength_four");
|
param.clParam.YHStrength_four = res.getFloat("YHStrength_four");
|
param.clParam.TimeLong_four= res.getInt("TimeLong_four");
|
}
|
}
|
} catch (SQLException e) {
|
sql.logger.error("FBS9100_Task_Thread_SQL.queryFbsCL9100SetParamBydev_id():" + e.toString(), e);
|
} finally {
|
if(null != res) {
|
try {
|
res.close();
|
} catch (SQLException e) {
|
sql.logger.error("FBS9100_Task_Thread_SQL.queryFbsCL9100SetParamBydev_id():" + e.toString(), e);
|
}
|
}
|
sql.close_con();
|
}
|
}
|
/***********************************************************************************/
|
/**
|
* ��������������������������������id ���������� tb_fbs9100_setparam ��������op_cmd��
|
*/
|
public static void updateFbs9100SetParamCmd(MysqlConnPool con_pool, ArrayList<FBS9100_StatAndParam> al_param)
|
{
|
for(int n=0; n<al_param.size(); n++) {
|
FBS9100_StatAndParam param = al_param.get(n);
|
if((param.op_cmd == FBS9100_ComBase.CMD_StartAck)
|
|| (param.op_cmd == FBS9100_ComBase.CMD_StopAck)
|
|| (param.op_cmd == FBS9100_ComBase.CMD_SetDischargeParmAck))
|
{
|
String sql_str_base = Sql_Mysql.FBS9100SetParam_Table + " SET "
|
+ "dev_id=" + param.dev_id + ", "
|
+ "op_cmd=" + param.op_cmd + ", "
|
+ "TestCmd=" + param.m_FBS_DiscParam.test_cmd;
|
//String sql_str_update = "UPDATE " + sql_str_base;
|
String sql_str_replace = "REPLACE INTO " + sql_str_base;
|
Sql_Mysql sql = new Sql_Mysql(con_pool);
|
try {
|
sql.sqlMysqlExecute(sql_str_replace);
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
sql.logger.error("FBS9100_Task_Thread_SQL.updateFbs9100SetParamCmd():" + e.toString(), e);
|
} finally {
|
sql.close_con();
|
}
|
|
param.m_FBS_DiscParam.op_cmd = param.op_cmd;
|
param.op_cmd = FBS9100_ComBase.CMD_NULL;
|
}
|
}
|
}
|
|
/**
|
* ��������������������������������id ���������� tb_fbs9100_setparam ��������op_cmd��
|
*/
|
public static void updateFbs9100SetParamCmdBydev_id(MysqlConnPool con_pool, FBS9100_StatAndParam param)
|
{
|
if((param.op_cmd == FBS9100_ComBase.CMD_StartAck)
|
|| (param.op_cmd == FBS9100_ComBase.CMD_StopAck)
|
|| (param.op_cmd == FBS9100_ComBase.CMD_SetDischargeParmAck)
|
|| (param.op_cmd == FBS9100_ComBase.CMD_SetIpAck)
|
|| (param.op_cmd == FBS9100_ComBase.CMD_SetOffLineYHParam_ACK)
|
|| (param.op_cmd == FBS9100_ComBase.CMD_StartOffLineYH_ACK)
|
|| (param.op_cmd == FBS9100_ComBase.CMD_StopOffLineYH_ACK)
|
|| (param.op_cmd == FBS9100_ComBase.CMD_ResetSystemAck)
|
|| (param.op_cmd == FBS9100_ComBase.CMD_UpdateXZTimeAck)
|
|| (param.op_cmd == FBS9100_ComBase.CMD_FAIL)
|
)
|
{
|
String sql_str_base = Sql_Mysql.FBS9100SetParam_Table + " SET "
|
+ "dev_id=" + param.dev_id + ", "
|
+ "op_cmd=" + param.op_cmd + ", "
|
+ "TestCmd=" + param.m_FBS_DiscParam.test_cmd;
|
//String sql_str_update = "UPDATE " + sql_str_base;
|
String sql_str_replace = "REPLACE INTO " + sql_str_base;
|
//System.out.println(sql_str_replace);
|
Sql_Mysql sql = new Sql_Mysql(con_pool);
|
try {
|
sql.sqlMysqlExecute(sql_str_replace);
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
sql.logger.error("FBS9100_Task_Thread_SQL.updateFbs9100SetParamCmdBydev_id():" + e.toString(), e);
|
} finally {
|
sql.close_con();
|
}
|
|
param.m_FBS_DiscParam.op_cmd = param.op_cmd;
|
param.op_cmd = FBS9100_ComBase.CMD_NULL;
|
}
|
}
|
|
|
public static void updateCL9100SetParamCmdBydev_id(MysqlConnPool con_pool, FBS9100_StatAndParam param)
|
{
|
if((param.op_cmd == FBS9100_ComBase.CMD_ReadFodParamSus)
|
|| (param.op_cmd == FBS9100_ComBase.CMD_WriteFodParamSus)
|
|| (param.op_cmd == FBS9100_ComBase.CMD_Start_FODCL_testSus )
|
|| (param.op_cmd == FBS9100_ComBase.CMD_Start_FODYH_testSus )
|
|| (param.op_cmd == FBS9100_ComBase.CMD_FAIL )
|
|| (param.op_cmd == FBS9100_ComBase.CMD_Stop_FOD_testSus))
|
{
|
String sql_str_base = Sql_Mysql.FBS9100SetDLParam_Table + " SET "
|
+ " dev_id=" + param.dev_id + ", "
|
+ " op_cmd=" + param.op_cmd + " "
|
+ " where dev_id = "+param.dev_id;
|
String sql_str_update = "UPDATE " + sql_str_base;
|
//String sql_str_replace = "REPLACE INTO " + sql_str_base;
|
//System.out.println(sql_str_replace);
|
Sql_Mysql sql = new Sql_Mysql(con_pool);
|
try {
|
sql.sqlMysqlExecute(sql_str_update);
|
} catch (SQLException e) {
|
sql.logger.error("FBS9100_Task_Thread_SQL.updateCL9100SetParamCmdBydev_id():" + e.toString(), e);
|
} finally {
|
sql.close_con();
|
}
|
|
param.clParam.op_cmd = param.op_cmd;
|
param.op_cmd = FBS9100_ComBase.CMD_NULL;
|
}
|
}
|
/***********************************************************************************/
|
/**
|
* ������������ ������������������������������tb_fbs9100_setparam����������������������������������������
|
* @param con_pool
|
* @param al_param
|
*/
|
public static void updateFbs9100SetParam(MysqlConnPool con_pool, ArrayList<FBS9100_StatAndParam> al_param)
|
{
|
for(int n=0; n<al_param.size(); n++)
|
{
|
FBS9100_StatAndParam param = al_param.get(n);
|
if(param.op_cmd == FBS9100_ComBase.CMD_GetDischargeParmAck)
|
{
|
String sql_str_base = Sql_Mysql.FBS9100SetParam_Table
|
+ " SET "
|
+ "dev_id=" + param.dev_id + ", "
|
+ "op_cmd=" + FBS9100_ComBase.CMD_GetDischargeParmAck + ", "
|
+ "TestCmd=" + param.m_FBS_DiscParam.test_cmd + ", "
|
+ "HourRate=" + param.m_FBS_DiscParamFromDev.HourRate + ", "
|
+ "DisCurr=" + param.m_FBS_DiscParamFromDev.DisCurr + ", "
|
+ "DisCap=" + param.m_FBS_DiscParamFromDev.DisCap + ", "
|
+ "DisTime=" + param.m_FBS_DiscParamFromDev.DisTime + ", "
|
+ "GroupVol_LOW=" + param.m_FBS_DiscParamFromDev.GroupVol_LOW + ", "
|
+ "MonomerVol_LOW=" + param.m_FBS_DiscParamFromDev.MonomerVol_LOW + ", "
|
+ "MonomerLowCount=" + param.m_FBS_DiscParamFromDev.MonomerLowCount + ", "
|
+ "BattGroupNum=" + param.m_FBS_DiscParamFromDev.BattGroupNum + ", "
|
+ "MonomerTmp_High=" +param.m_FBS_DiscParamFromDev.MonomerTmp_High + ", "
|
+ "OnlineVolLowAction=" + param.m_FBS_DiscParamFromDev.OnlineVolLowAction + ", "
|
+ "DCVolHighLimit=" + param.m_FBS_DiscParamFromDev.DCVolHighLimit + ", "
|
+ "ChargeCurrSet=" + param.m_FBS_DiscParamFromDev.ChargeCurrSet;
|
//+ " WHERE dev_id=" + param.dev_id;
|
//String sql_str_update = "UPDATE " + sql_str_base;
|
String sql_str_replace = "REPLACE INTO " + sql_str_base;
|
Sql_Mysql sql = new Sql_Mysql(con_pool);
|
try {
|
//System.out.println(sql_str_replace);
|
sql.sqlMysqlExecute(sql_str_replace);
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
sql.logger.error("FBS9100_Task_Thread_SQL.updateFbs9100SetParam():" + e.toString(), e);
|
} finally {
|
sql.close_con();
|
}
|
|
param.m_FBS_DiscParam.op_cmd = FBS9100_ComBase.CMD_GetDischargeParmAck;
|
param.op_cmd = FBS9100_ComBase.CMD_NULL;
|
}
|
}
|
}
|
|
/**
|
* ������������ �����������������������������id��������tb_fbs9100_setparam����������������������������������������
|
* @param con_pool
|
* @param al_param
|
*/
|
public static void updateFbs9100SetParamBydev_id(MysqlConnPool con_pool, FBS9100_StatAndParam param)
|
{
|
if(param.op_cmd == FBS9100_ComBase.CMD_GetDischargeParmAck)
|
{
|
String sql_str_base = Sql_Mysql.FBS9100SetParam_Table
|
+ " SET "
|
+ "dev_id=" + param.dev_id + ", "
|
+ "op_cmd=" + FBS9100_ComBase.CMD_GetDischargeParmAck + ", "
|
+ "TestCmd=" + param.m_FBS_DiscParam.test_cmd + ", "
|
+ "HourRate=" + param.m_FBS_DiscParamFromDev.HourRate + ", "
|
+ "DisCurr=" + param.m_FBS_DiscParamFromDev.DisCurr + ", "
|
+ "DisCap=" + param.m_FBS_DiscParamFromDev.DisCap + ", "
|
+ "DisTime=" + param.m_FBS_DiscParamFromDev.DisTime + ", "
|
+ "GroupVol_LOW=" + param.m_FBS_DiscParamFromDev.GroupVol_LOW + ", "
|
+ "MonomerVol_LOW=" + param.m_FBS_DiscParamFromDev.MonomerVol_LOW + ", "
|
+ "MonomerLowCount=" + param.m_FBS_DiscParamFromDev.MonomerLowCount + ", "
|
+ "BattGroupNum=" + param.m_FBS_DiscParamFromDev.BattGroupNum + ", "
|
+ "MonomerTmp_High=" + param.m_FBS_DiscParamFromDev.MonomerTmp_High + ", "
|
+ "OnlineVolLowAction=" + param.m_FBS_DiscParamFromDev.OnlineVolLowAction + ", "
|
+ "DCVolHighLimit=" + param.m_FBS_DiscParamFromDev.DCVolHighLimit + ", "
|
+ "ChargeCurrSet=" + param.m_FBS_DiscParamFromDev.ChargeCurrSet;
|
//String sql_str_update = "UPDATE " + sql_str_base;
|
String sql_str_replace = "REPLACE INTO " + sql_str_base;
|
Sql_Mysql sql = new Sql_Mysql(con_pool);
|
try {
|
//System.out.println(sql_str_replace);
|
sql.sqlMysqlExecute(sql_str_replace);
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
sql.logger.error("FBS9100_Task_Thread_SQL.updateFbs9100SetParamBydev_id():" + e.toString(), e);
|
} finally {
|
sql.close_con();
|
}
|
|
param.m_FBS_DiscParam.op_cmd = FBS9100_ComBase.CMD_GetDischargeParmAck;
|
param.op_cmd = FBS9100_ComBase.CMD_NULL;
|
}
|
}
|
/***********************************************************************************/
|
public static void updateBattState_RT_RamDB_Table(MysqlConnPool conn_pool, ArrayList<BattData_RT> bd_al)
|
{
|
Sql_Mysql sql = new Sql_Mysql(conn_pool);
|
try
|
{
|
String sql_str = "UPDATE " + Sql_Mysql.BattRtState_Table
|
+ " SET "
|
+ " rec_datetime='" + Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms) + "',";
|
|
sql_str += " batt_count = CASE BattGroupId ";
|
for(int n=0; n<bd_al.size(); n++) {
|
BattData_RT brt = bd_al.get(n);
|
sql_str += " WHEN " + brt.BattGroupId + " THEN " + brt.MonCount;
|
}
|
|
sql_str += " END, " + " online_vol = CASE BattGroupId ";
|
for(int n=0; n<bd_al.size(); n++) {
|
BattData_RT brt = bd_al.get(n);
|
sql_str += " WHEN " + brt.BattGroupId + " THEN " + brt.getOnlineVol();
|
}
|
|
sql_str += " END, " + " group_vol = CASE BattGroupId ";
|
for(int n=0; n<bd_al.size(); n++) {
|
BattData_RT brt = bd_al.get(n);
|
sql_str += " WHEN " + brt.BattGroupId + " THEN " + brt.getGroupVol();
|
}
|
|
sql_str += " END, " + " group_tmp = CASE BattGroupId ";
|
for(int n=0; n<bd_al.size(); n++) {
|
BattData_RT brt = bd_al.get(n);
|
sql_str += " WHEN " + brt.BattGroupId + " THEN " + brt.getGroupTmp();
|
}
|
|
sql_str += " END, " + " group_curr = CASE BattGroupId ";
|
for(int n=0; n<bd_al.size(); n++) {
|
BattData_RT brt = bd_al.get(n);
|
sql_str += " WHEN " + brt.BattGroupId + " THEN " + brt.getTestCurr();
|
}
|
|
sql_str += " END, " + " batt_state = CASE BattGroupId ";
|
for(int n=0; n<bd_al.size(); n++) {
|
BattData_RT brt = bd_al.get(n);
|
sql_str += " WHEN " + brt.BattGroupId + " THEN " + brt.getBattState();
|
}
|
|
sql_str += " END, " + " batt_test_type = CASE BattGroupId ";
|
for(int n=0; n<bd_al.size(); n++) {
|
BattData_RT brt = bd_al.get(n);
|
System.out.println(brt.getBattTestType());
|
sql_str += " WHEN " + brt.BattGroupId + " THEN " + brt.getBattTestType();
|
}
|
|
sql_str += " END, " + " batt_test_starttime = CASE BattGroupId ";
|
Date dt = new Date();
|
for(int n=0; n<bd_al.size(); n++) {
|
BattData_RT brt = bd_al.get(n);
|
dt.setTime(brt.getTestStartTime());
|
sql_str += " WHEN " + brt.BattGroupId + " THEN '" + Com.getDateTimeFormat(dt, Com.DTF_YMDhms) + "'";
|
}
|
|
sql_str += " END, " + " batt_test_recordtime = CASE BattGroupId ";
|
for(int n=0; n<bd_al.size(); n++) {
|
BattData_RT brt = bd_al.get(n);
|
dt.setTime(brt.getTestRecordTime());
|
sql_str += " WHEN " + brt.BattGroupId + " THEN '" + Com.getDateTimeFormat(dt, Com.DTF_YMDhms) + "'";
|
}
|
|
sql_str += " END, " + " batt_test_tlong = CASE BattGroupId ";
|
for(int n=0; n<bd_al.size(); n++) {
|
BattData_RT brt = bd_al.get(n);
|
sql_str += " WHEN " + brt.BattGroupId + " THEN " + brt.getTestTimeLong();
|
}
|
|
sql_str += " END, " + " batt_test_cap = CASE BattGroupId ";
|
for(int n=0; n<bd_al.size(); n++) {
|
BattData_RT brt = bd_al.get(n);
|
sql_str += " WHEN " + brt.BattGroupId + " THEN " + brt.getTestCap();
|
}
|
|
sql_str += " END, " + " batt_real_cap = CASE BattGroupId ";
|
for(int n=0; n<bd_al.size(); n++) {
|
BattData_RT brt = bd_al.get(n);
|
sql_str += " WHEN " + brt.BattGroupId + " THEN " + brt.getBattRealCap();
|
}
|
|
sql_str += " END, " + " batt_rest_cap = CASE BattGroupId ";
|
for(int n=0; n<bd_al.size(); n++) {
|
BattData_RT brt = bd_al.get(n);
|
sql_str += " WHEN " + brt.BattGroupId + " THEN " + brt.getBattRestCap();
|
}
|
|
sql_str += " END, " + " batt_rest_power1_time = CASE BattGroupId ";
|
for(int n=0; n<bd_al.size(); n++) {
|
BattData_RT brt = bd_al.get(n);
|
sql_str += " WHEN " + brt.BattGroupId + " THEN " + brt.getBattRestTime();
|
}
|
|
sql_str += " END WHERE BattGroupId IN (";
|
for(int n=0; n<bd_al.size(); n++) {
|
BattData_RT brt = bd_al.get(n);
|
if(n > 0) {
|
sql_str += ",";
|
}
|
sql_str += brt.BattGroupId;
|
}
|
sql_str += ")";
|
//System.out.println(sql_str);
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
sql.logger.error("FBS9100_Task_Thread_SQL.updateBattState_RT_RamDB_Table():" + e.toString(), e);
|
} finally {
|
sql.close_con();
|
}
|
}
|
/************************************************************************************************/
|
/***********************************************************************************/
|
public static void insertFBSDevStatChangeInfToTable(MysqlConnPool conn_pool, int fbsdev_id, int last_stat,
|
int now_stat, int stat_change_reason)
|
{
|
Sql_Mysql sql = new Sql_Mysql(conn_pool);
|
try
|
{
|
String sql_str = "INSERT INTO " + Sql_Mysql.FBSDevStateChangeInf_Table
|
+ " (dev_id,rec_time,last_stat,now_stat,state_change_reason) "
|
+ " VALUES (" +
|
+ fbsdev_id + ","
|
+ "'" + Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms) + "',"
|
+ last_stat + ","
|
+ now_stat + ","
|
+ stat_change_reason + ")";
|
|
//System.out.println(sql_str);
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
sql.logger.error("FBS9100_Task_Thread_SQL.insertFBSDevStatChangeInfToTable():" + e.toString(), e);
|
} finally {
|
sql.close_con();
|
}
|
}
|
/************************************************************************************************/
|
/**
|
* 插入或更新均衡供电模块参数状态信息
|
* @param conn_pool
|
* @param param
|
*/
|
public static void insertOrUpdateJhStateTable(MysqlConnPool conn_pool,FBS9100_StatAndParam param) {
|
String sql_str_base = Sql_Mysql.FBS9100_61850_jhstate_Table +
|
"SET dev_id = "+param.dev_id+"," +
|
"dev_version = '"+param.fbs9100_jhpowerState.getDev_version()+"'," +
|
"input_vol_total = "+param.fbs9100_jhpowerState.getInput_vol_total()+"," +
|
"output_vol_total="+param.fbs9100_jhpowerState.getOutput_vol_total()+"," +
|
"input_curr_total="+param.fbs9100_jhpowerState.getInput_curr_total()+"," +
|
"output_curr_total="+param.fbs9100_jhpowerState.getOutput_curr_total()+"," +
|
"output_vol_one="+param.fbs9100_jhpowerState.getOutput_vol_one()+"," +
|
"output_vol_two="+param.fbs9100_jhpowerState.getOutput_vol_two()+"," +
|
"output_vol_three="+param.fbs9100_jhpowerState.getOutput_vol_three()+"," +
|
"output_vol_four="+param.fbs9100_jhpowerState.getOutput_vol_four()+"," +
|
"output_vol_five="+param.fbs9100_jhpowerState.getOutput_vol_five()+"," +
|
"output_vol_six="+param.fbs9100_jhpowerState.getOutput_vol_six()+"," +
|
"output_vol_seven="+param.fbs9100_jhpowerState.getOutput_vol_seven()+"," +
|
"output_vol_eight="+param.fbs9100_jhpowerState.getOutput_vol_eight()+"," +
|
"output_vol_nine="+param.fbs9100_jhpowerState.getOutput_vol_nine()+"," +
|
"output_vol_ten="+param.fbs9100_jhpowerState.getOutput_vol_ten()+"," +
|
"dev_temp="+param.fbs9100_jhpowerState.getDev_temp();
|
|
String sql_str_update = "UPDATE " + sql_str_base + " WHERE dev_id=" + param.dev_id;
|
String sql_str_replace = "REPLACE INTO " + sql_str_base;
|
Sql_Mysql sql = new Sql_Mysql(conn_pool);
|
ResultSet res = null;
|
try {
|
res = sql.sqlMysqlQuery("SELECT num FROM " + Sql_Mysql.FBS9100_61850_jhstate_Table
|
+ " WHERE dev_id = " + param.dev_id);
|
if(res.next()) {
|
sql.sqlMysqlExecute(sql_str_update);
|
} else {
|
sql.sqlMysqlExecute(sql_str_replace);
|
}
|
} catch (SQLException e) {
|
sql.logger.error("FBS9100_Task_Thread_SQL.insertOrUpdateJhStateTable():" + e.toString(), e);
|
} finally {
|
if(null != res) {
|
try {
|
res.close();
|
} catch (SQLException e) {
|
sql.logger.error("FBS9100_Task_Thread_SQL.insertOrUpdateJhStateTable():" + e.toString(), e);
|
}
|
}
|
sql.close_con();
|
}
|
}
|
|
/**
|
* 修改设备得离线养护参数
|
* @param m_ConnPool
|
* @param m_StatAndParam
|
*/
|
public static void updateFbs9100SetParamOffLineYHParamBydev_id(MysqlConnPool con_pool,
|
FBS9100_StatAndParam param) {
|
// TODO Auto-generated method stub
|
if(param.op_cmd == FBS9100_ComBase.CMD_GetOffLineYHParam_ACK)
|
{
|
String sql_str_base = Sql_Mysql.FBS9100SetParam_Table
|
+ " SET "
|
+ " dev_id=" + param.dev_id + ", "
|
+ " op_cmd=" + FBS9100_ComBase.CMD_GetOffLineYHParam_ACK + ", "
|
+ " MonomerTmp_High =" + param.m_FBS_DiscParamFromDev.MonomerTmp_High + ", "
|
+ " OnLineVol_Low =" + param.m_FBS_DiscParamFromDev.OnLineVol_Low + ", "
|
+ " OffLineYH_Cycle=" + param.m_FBS_DiscParamFromDev.OffLineYH_Cycle + ","
|
+ " OffLineYHstarttime = '" + Com.getDateTimeFormat(param.m_FBS_DiscParamFromDev.OffLineYHstarttime, Com.DTF_YMDhms) + "',"
|
+ " OffLineYHTimes=" + param.m_FBS_DiscParamFromDev.OffLineYHTimes + ","
|
+ " OffLineYHOnceCycle=" + param.m_FBS_DiscParamFromDev.OffLineYHOnceCycle
|
+ " where dev_id = " + param.dev_id;
|
String sql_str_update = "UPDATE " + sql_str_base;
|
Sql_Mysql sql = new Sql_Mysql(con_pool);
|
try {
|
//System.out.println(sql_str_replace);
|
sql.sqlMysqlExecute(sql_str_update);
|
} catch (SQLException e) {
|
sql.logger.error("FBS9100_Task_Thread_SQL.updateFbs9100SetParamOffLineYHParamBydev_id():" + e.toString(), e);
|
} finally {
|
sql.close_con();
|
}
|
|
param.m_FBS_DiscParam.op_cmd = FBS9100_ComBase.CMD_GetOffLineYHParam_ACK;
|
param.op_cmd = FBS9100_ComBase.CMD_NULL;
|
}
|
}
|
/****************************************************************************************/
|
/**
|
* 查询设备的系统参数
|
* @param con_pool
|
* @param param
|
*/
|
public static void queryFBS9100SysParamBydev_id(MysqlConnPool con_pool, FBS9100_StatAndParam param) {
|
String sql_str = "SELECT * FROM " + Sql_Mysql.FBS9100SysParam_Table + " where dev_id = " + param.dev_id;
|
Sql_Mysql sql = new Sql_Mysql(con_pool);
|
ResultSet res = null;
|
try {
|
res = sql.sqlMysqlQuery(sql_str);
|
if(res.next())
|
{
|
if(null != param) {
|
param.m_FBS_SysParam.op_cmd = res.getInt("op_cmd");
|
param.m_FBS_SysParam.dev_ip = res.getString("dev_ip");
|
param.m_FBS_SysParam.dev_id = res.getInt("dev_id");
|
param.m_FBS_SysParam.IPADDR = res.getString("IPADDR");
|
param.m_FBS_SysParam.SubIPADDR = res.getString("SubIPADDR");
|
param.m_FBS_SysParam.NetGateADDR = res.getString("NetGateADDR");
|
param.m_FBS_SysParam.GroupVolRange = res.getInt("GroupVolRange");
|
param.m_FBS_SysParam.GroupVolSorce = res.getInt("GroupVolSorce");
|
param.m_FBS_SysParam.CurrentRange = res.getInt("CurrentRange");
|
param.m_FBS_SysParam.CurrentTyte = res.getInt("CurrentTyte");
|
param.m_FBS_SysParam.MonomerOrder = res.getInt("MonomerOrder");
|
param.m_FBS_SysParam.BackLightTime = res.getInt("BackLightTime");
|
param.m_FBS_SysParam.MACADDR = res.getString("MACADDR");
|
param.m_FBS_SysParam.LoaderCount = res.getInt("LoaderCount");
|
param.m_FBS_SysParam.DtCardCount = res.getInt("DtCardCount");
|
param.m_FBS_SysParam.PowerBreakChargeCurr = res.getFloat("PowerBreakChargeCurr");
|
param.m_FBS_SysParam.MajorBattGroupNum = res.getInt("MajorBattGroupNum");
|
param.m_FBS_SysParam.POF_BG_Boost_Cnt = res.getInt("POF_BG_Boost_Cnt");
|
param.m_FBS_SysParam.POF_BG_Boost_VolStop = res.getFloat("POF_BG_Boost_VolStop");
|
param.m_FBS_SysParam.POF_BG_Boost_VolStart = res.getFloat("POF_BG_Boost_VolStart");
|
}
|
}
|
} catch (SQLException e) {
|
sql.logger.error("FBS9100_Task_Thread_SQL.queryFBS9100SysParamBydev_id():" + e.toString(), e);
|
} finally {
|
if(null != res) {
|
try {
|
res.close();
|
} catch (SQLException e) {
|
sql.logger.error("FBS9100_Task_Thread_SQL.queryFBS9100SysParamBydev_id():" + e.toString(), e);
|
}
|
}
|
sql.close_con();
|
}
|
}
|
|
/**
|
* 修改设备的系统参数
|
* @param m_ConnPool
|
* @param m_StatAndParam
|
*/
|
public static void updateFbs9100SysParamParamBydev_id(MysqlConnPool con_pool,
|
FBS9100_StatAndParam param) {
|
if(param.op_cmd == FBS9100_ComBase.CMD_GetSYSSetParamAck)
|
{
|
|
FBS9100_SysParam sysparam = param.m_FBS_SysParam;
|
String sql_str_base = Sql_Mysql.FBS9100SysParam_Table
|
+ " SET "
|
+ " dev_id=" + param.dev_id + ", "
|
+ " op_cmd=" + FBS9100_ComBase.CMD_GetSYSSetParamAck + ", "
|
+ " GroupVolSorce =" + sysparam.GroupVolSorce + ", " //在线电压低阀值
|
+ " CurrentTyte =" + sysparam.CurrentTyte + ", " //浮充电流阀值
|
+ " PowerBreakChargeCurr =" + sysparam.PowerBreakChargeCurr + ", " //电流异常阀值
|
+ " POF_BG_Boost_VolStop =" + sysparam.POF_BG_Boost_VolStop + ", " //电压过高阀值
|
+ " POF_BG_Boost_VolStart =" + sysparam.POF_BG_Boost_VolStart + ", " //电压过低阀值
|
+ " MonomerOrder =" + sysparam.MonomerOrder + ", " //主从机模式
|
+ " LoaderCount =" + sysparam.LoaderCount + ", " //主从机使能
|
+ " DtCardCount =" + sysparam.DtCardCount + " "; //干接点使能
|
//+ " where dev_id = " + param.dev_id;
|
//String sql_str_update = "UPDATE " + sql_str_base;
|
String sql_str_replace = " REPLACE INTO " + sql_str_base;
|
Sql_Mysql sql = new Sql_Mysql(con_pool);
|
try {
|
//System.out.println(sql_str_replace);
|
sql.sqlMysqlExecute(sql_str_replace);
|
} catch (SQLException e) {
|
sql.logger.error("FBS9100_Task_Thread_SQL.queryFBS9100SysParamBydev_id():" + e.toString(), e);
|
} finally {
|
sql.close_con();
|
}
|
|
param.m_FBS_SysParam.op_cmd = FBS9100_ComBase.CMD_GetSYSSetParamAck;
|
param.op_cmd = FBS9100_ComBase.CMD_NULL;
|
}
|
}
|
|
|
/**
|
* 修改当前返回状态
|
* @param con_pool
|
* @param param
|
*/
|
public static void updateFbs9100SysParamCmdBydev_id(MysqlConnPool con_pool, FBS9100_StatAndParam param)
|
{
|
if(param.op_cmd == FBS9100_ComBase.CMD_ClearSysAlarm_ACK ||
|
param.op_cmd == FBS9100_ComBase.CMD_SetSYSSetParamAck ||
|
param.op_cmd == FBS9100_ComBase.CMD_GetJuHengParamAck ||
|
param.op_cmd == FBS9100_ComBase.CMD_FAIL ||
|
param.op_cmd == FBS9100_ComBase.CMD_SetJuHengParamAck )
|
{
|
String sql_str_base = Sql_Mysql.FBS9100SysParam_Table + " SET "
|
+ "dev_id=" + param.dev_id + ", "
|
+ "op_cmd=" + param.op_cmd + " ";
|
String sql_str_update = "UPDATE " + sql_str_base + " Where dev_id = " + param.dev_id;;
|
//String sql_str_replace = "REPLACE INTO " + sql_str_base;
|
//System.out.println(sql_str_replace);
|
Sql_Mysql sql = new Sql_Mysql(con_pool);
|
try {
|
sql.sqlMysqlExecute(sql_str_update);
|
//sql.sqlMysqlExecute(sql_str_replace);
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
sql.logger.error("FBS9100_Task_Thread_SQL.updateFbs9100SysParamCmdBydev_id():" + e.toString(), e);
|
} finally {
|
sql.close_con();
|
}
|
param.m_FBS_DiscParam.op_cmd = param.op_cmd;
|
param.op_cmd = FBS9100_ComBase.CMD_NULL;
|
}
|
}
|
|
/**
|
* 创建历史事件记录表
|
*/
|
public static void createTbBtsStateChange_infTable(MysqlConnPool pool) {
|
String sql_str = "CREATE TABLE IF NOT EXISTS " + Sql_Mysql.Tb_BtsStateChange_inf + " (" +
|
" `num` bigint(20) NOT NULL AUTO_INCREMENT," +
|
" `dev_id` int(11) NOT NULL DEFAULT '1'," +
|
" `record_time` datetime NOT NULL DEFAULT '2000-01-01 00:00:00'," +
|
" `last_state` int(11) NOT NULL DEFAULT '0'," +
|
" `now_state` int(11) NOT NULL DEFAULT '0'," +
|
" `event_type` int(11) NOT NULL DEFAULT '0'," +
|
" `note` varchar(255) NOT NULL DEFAULT ''," +
|
" PRIMARY KEY (`num`),"
|
+ "KEY `dev_id` (`dev_id`) 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("FBS9100_Task_Thread_SQL.createTbBtsStateChange_infTable():" + e.toString(), e);
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
/**
|
* 创建指定的电池组历史数据记录表
|
* @param pool
|
* @param BattGroupId
|
*/
|
public static void CreateTb_BattRealDataTable(MysqlConnPool pool,int BattGroupId) {
|
String sql_str = "CREATE TABLE IF NOT EXISTS " + Sql_Mysql.Tb_Batt_RealData+BattGroupId + " (" +
|
" `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" +
|
") 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("FBS9100_Task_Thread_SQL.CreateTb_BattRealDataTable():" + e.toString(), e);
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
/**
|
* 记录历史事件和告警
|
* @param m_ConnPool
|
* @param events
|
*/
|
public static void insertBTSEventRecordTable(MysqlConnPool m_ConnPool, List<BTS61850_EventRecord> events) {
|
String sql_str = "INSERT INTO " + Sql_Mysql.Tb_BtsStateChange_inf + "(dev_id,record_time,last_state,now_state,event_type) VALUES";
|
if(events != null && events.size() > 0) {
|
for(int i = 0 ; i < events.size() ; i++) {
|
BTS61850_EventRecord event = events.get(i);
|
if(i > 0) {
|
sql_str += ",";
|
}
|
sql_str += "("
|
+ event.dev_id + ","
|
+"'" + Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms) + "',"
|
+ event.last_state +","
|
+ event.now_state +","
|
+ event.event_type + ""
|
+ ")";
|
|
}
|
Sql_Mysql sql = new Sql_Mysql(m_ConnPool);
|
try {
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException e) {
|
sql.logger.error("FBS9100_Task_Thread_SQL.insertBTSEventRecordTable():" + e.toString(), e);
|
} finally {
|
sql.close_con();
|
}
|
}
|
}
|
|
/**
|
* 读取当前设备的离线养护计划
|
* @param m_ConnPool
|
* @param param
|
*/
|
public static void queryBTS61850OffLineyhPlanOnRam(MysqlConnPool m_ConnPool, FBS9100_StatAndParam param) {
|
String sql_str = " SELECT * FROM " + Sql_Mysql.FBS9100_offlineyhplan + " WHERE dev_id = " + param.dev_id ;
|
Sql_Mysql sql = new Sql_Mysql(m_ConnPool);
|
BTS61850_OffLineYHPlan plan = param.bts61850_OffLineYHPlan;
|
ResultSet res = null;
|
try {
|
res = sql.sqlMysqlQuery(sql_str);
|
if(res.next()) {
|
plan.OffLineYH_Cycle = res.getInt("OffLineYH_Cycle");
|
plan.OffLineYHOnceCycle = res.getInt("OffLineYHOnceCycle");
|
plan.OffLineYHTimes = res.getInt("OffLineYHTimes");
|
plan.OffLineYHstarttime = res.getTimestamp("OffLineYHstarttime");
|
} else {
|
String sql_str_insert = " INSERT INTO " + Sql_Mysql.FBS9100_offlineyhplan
|
+ "(dev_id,OffLineYH_Cycle,OffLineYHstarttime,OffLineYHTimes,OffLineYHOnceCycle) "
|
+ " VALUES (" + param.dev_id + ", " + plan.OffLineYH_Cycle + ", '" + Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms) + "',"+ plan.OffLineYHTimes +", " + plan.OffLineYHOnceCycle +")";
|
sql.sqlMysqlExecute(sql_str_insert);
|
}
|
} catch (SQLException e) {
|
sql.logger.error("FBS9100_Task_Thread_SQL.queryBTS61850OffLineyhPlanOnRam():" + e.toString(), e);
|
} finally {
|
if(null != res) {
|
try {
|
res.close();
|
} catch (SQLException e) {
|
sql.logger.error("FBS9100_Task_Thread_SQL.queryBTS61850OffLineyhPlanOnRam():" + e.toString(), e);
|
}
|
}
|
sql.close_con();
|
}
|
}
|
|
/**
|
* 更新养护除硫参数
|
* @param m_ConnPool
|
* @param param
|
*/
|
public static void updateBTS61850OffLineyhPlanOnRam(MysqlConnPool m_ConnPool, FBS9100_StatAndParam param) {
|
Sql_Mysql sql = new Sql_Mysql(m_ConnPool);
|
BTS61850_OffLineYHPlan plan = param.bts61850_OffLineYHPlan;
|
String sql_str_insert = " UPDATE " + Sql_Mysql.FBS9100_offlineyhplan +
|
" SET OffLineYH_Cycle = " + plan.OffLineYH_Cycle + ",OffLineYHstarttime='" + Com.getDateTimeFormat(plan.OffLineYHstarttime, Com.DTF_YMDhms) + "',OffLineYHTimes="+ plan.OffLineYHTimes +",OffLineYHOnceCycle= " + plan.OffLineYHOnceCycle +
|
" WHERE dev_id = " + param.dev_id;
|
try {
|
|
sql.sqlMysqlExecute(sql_str_insert);
|
} catch (SQLException e) {
|
sql.logger.error("FBS9100_Task_Thread_SQL.updateBTS61850OffLineyhPlanOnRam():" + e.toString(), e);
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
public static void insert_BTS_BattGroupState_Table(MysqlConnPool con_pool, int dev_id, int bg_id,
|
int bg_index, int mon_cnt, float f_vol, float f_curr,
|
String reg_code)
|
{
|
String sql_str_insert = "INSERT INTO " + Sql_Mysql.AppDevBattGroup_Table
|
+ " ( "
|
+ "dev_id,"
|
+ "battgroup_id,"
|
+ "battgroup_index,"
|
+ "battgroup_mon_cnt,"
|
+ "battgroup_float_vol,"
|
+ "battgroup_float_curr,"
|
+ "battgroup_register_code"
|
+ " ) "
|
+ " VALUES "
|
+ " ( "
|
+ dev_id + ", "
|
+ bg_id + ", "
|
+ bg_index + ", "
|
+ mon_cnt + ", "
|
+ f_vol + ", "
|
+ f_curr + ", "
|
+ "'" + reg_code + "'"
|
+ " ) ";
|
Sql_Mysql sql = new Sql_Mysql(con_pool);
|
try {
|
sql.sqlMysqlExecute(sql_str_insert);
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
sql.logger.error("FBS9100_Task_Thread_SQL.insert_BTS_BattGroupState_Table():" + e.toString(), e);
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
public static void update_BTS_BattGroupState_Table(MysqlConnPool con_pool, int dev_id, int bg_id,
|
int bg_index, int mon_cnt, float f_vol, float f_curr,
|
String reg_code)
|
{
|
String sql_str_insert = "UPDATE " + Sql_Mysql.AppDevBattGroup_Table
|
+ " SET "
|
+ "battgroup_index=" + bg_index + ","
|
+ "battgroup_mon_cnt=" + mon_cnt + ","
|
+ "battgroup_float_vol=" + f_vol + ","
|
+ "battgroup_float_curr=" + f_curr + ","
|
+ "battgroup_register_code='" + reg_code + "'"
|
+ " WHERE "
|
+ " dev_id=" + dev_id + " AND battgroup_id=" + bg_id;
|
Sql_Mysql sql = new Sql_Mysql(con_pool);
|
try {
|
//System.out.println(sql_str_insert);
|
sql.sqlMysqlExecute(sql_str_insert);
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
sql.logger.error("FBS9100_Task_Thread_SQL.update_BTS_BattGroupState_Table():" + e.toString(), e);
|
} finally {
|
sql.close_con();
|
}
|
}
|
/*********************************************************************************
|
public static void createAppLog4jTable(MysqlConnPool con_pool, String db_tb) {
|
String str1 = "CREATE TABLE " + db_tb + " ( "
|
+ "`num` bigint(20) NOT NULL AUTO_INCREMENT, "
|
+ "`level` varchar(100) DEFAULT NULL, "
|
+ "`category` varchar(200) DEFAULT NULL, "
|
+ "`thread` varchar(200) DEFAULT NULL, "
|
+ "`time` varchar(50) DEFAULT NULL, "
|
+ "`location` varchar(200) DEFAULT NULL, "
|
+ "`note` varchar(2048) DEFAULT NULL, "
|
+ "PRIMARY KEY (`num`), "
|
+ "KEY `level_index` (`level`) USING BTREE, "
|
+ "KEY `time_index` (`time`) USING BTREE "
|
+ ") ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8; ";
|
|
Sql_Mysql sql = new Sql_Mysql(con_pool);
|
try {
|
sql.sqlMysqlExecute(str1);
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
sql.logger.error("FBS9100_Task_Thread_SQL.createAppLog4jTable():" + e.toString(), e);
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
public static void createAppLog4j2_Table(MysqlConnPool con_pool, String db_tb) {
|
String str1 = "DROP TABLE IF EXISTS " + db_tb;
|
String str2 = "CREATE TABLE " + db_tb + " ( "
|
+ " `num` bigint(20) NOT NULL AUTO_INCREMENT, "
|
+ " `log_date` datetime NOT NULL COMMENT '日期', "
|
+ " `log_file` varchar(255) DEFAULT NULL COMMENT '文件名', "
|
+ " `log_line` varchar(255) DEFAULT NULL COMMENT '行号', "
|
+ " `log_thread`varchar(255) DEFAULT NULL COMMENT '线程', "
|
+ " `log_level` varchar(255) DEFAULT NULL COMMENT 'Log级别 info warndebug error等', "
|
+ " `log_message` varchar(2048) DEFAULT NULL COMMENT 'Log信息', "
|
+ " PRIMARY KEY (`log_id`), "
|
+ " KEY `INDEX_LOG_DATE` (`log_date`) USING BTREE "
|
+ " ) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
|
|
Sql_Mysql sql = new Sql_Mysql(con_pool);
|
try {
|
sql.sqlMysqlExecute(str1);
|
sql.sqlMysqlExecute(str2);
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
sql.logger.error("FBS9100_Task_Thread_SQL.createAppLog4j2_Table():" + e.toString(), e);
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
public static void deleteAppLog4jTableOldData(MysqlConnPool con_pool, String db_tb) {
|
String str1 = "SELECT num FROM " + db_tb + " ORDER BY num DESC LIMIT 50000,1";
|
String str2 = "DELETE FROM " + db_tb + " WHERE num<";
|
|
Sql_Mysql sql = new Sql_Mysql(con_pool);
|
try {
|
sql.sqlMysqlExecute(str1);
|
ResultSet res = sql.sqlMysqlQuery(str1);
|
if(res.next()) {
|
long num = res.getLong("num");
|
sql.sqlMysqlExecute(str2 + num);
|
}
|
Thread.sleep(500);
|
} catch (SQLException | InterruptedException e) {
|
// TODO Auto-generated catch block
|
sql.logger.error("FBS9100_Task_Thread_SQL.deleteAppLog4jTableOldData():" + e.toString(), e);
|
} finally {
|
sql.close_con();
|
}
|
}
|
***************************************************************************************/
|
public static void main(String[] args) {
|
MysqlConnPool pool = new MysqlConnPool("127.0.0.1", 3360, 10);
|
//BTS61850_EventRecord event = new BTS61850_EventRecord(618500001, 1, 0, BTS61850_EventRecord.Event_CONNCET_Error_type);
|
|
//List<BTS61850_EventRecord> events = new ArrayList<BTS61850_EventRecord>();
|
//events.add(event);
|
//insertBTSEventRecordTable(pool, events);
|
|
|
//CreateTb_BattRealDataTable(pool, 1000001);
|
|
FBS9100_StatAndParam param = new FBS9100_StatAndParam("127.0.0.1", 618500001);
|
queryBTS61850OffLineyhPlanOnRam(pool, param);
|
|
}
|
|
/**
|
* 查询设备的告警参数
|
* @param con_pool
|
* @param param
|
*/
|
public static void queryDev6185AlmParamBydev_id(MysqlConnPool con_pool, FBS9100_StatAndParam param) {
|
String sql_str = "SELECT * FROM " + Sql_Mysql.Dev_6185_AlmParam + " where dev_id = " + param.dev_id;
|
Sql_Mysql sql = new Sql_Mysql(con_pool);
|
ResultSet res = null;
|
|
try {
|
res = sql.sqlMysqlQuery(sql_str);
|
if(res.next())
|
{
|
if(null != param) {
|
param.m_FBS_AlmParam.op_cmd = res.getInt("op_cmd");
|
param.m_FBS_AlmParam.groupvol_high_val = res.getFloat("groupvol_high_val");; //'组端电压高阈值',
|
param.m_FBS_AlmParam.groupvol_low_val = res.getFloat("groupvol_low_val"); //'组端电压低阈值',
|
param.m_FBS_AlmParam.monvol_high_val = res.getFloat("monvol_high_val"); //'单体电压高阈值',
|
param.m_FBS_AlmParam.monvol_low_val = res.getFloat("monvol_low_val"); //'单体电压低阈值',
|
param.m_FBS_AlmParam.montmp_high_val = res.getFloat("montmp_high_val"); //'单体温度高阈值',
|
param.m_FBS_AlmParam.montmp_low_val = res.getFloat("montmp_low_val"); //'单体温度低阈值',
|
param.m_FBS_AlmParam.monres_high_val = res.getFloat("monres_high_val"); //'单体内阻高阈值',
|
param.m_FBS_AlmParam.monres_low_val = res.getFloat("monres_low_val"); //'单体内阻低阈值',
|
param.m_FBS_AlmParam.moncap_high_val = res.getFloat("moncap_high_val"); //'单体容量高阈值',
|
param.m_FBS_AlmParam.moncap_low_val = res.getFloat("moncap_low_val"); //'单体容量低阈值',
|
}
|
}
|
} catch (SQLException e) {
|
sql.logger.error("FBS9100_Task_Thread_SQL.queryDev6185AlmParamBydev_id():" + e.toString(), e);
|
} finally {
|
if(null != res) {
|
try {
|
res.close();
|
} catch (SQLException e) {
|
sql.logger.error("FBS9100_Task_Thread_SQL.queryDev6185AlmParamBydev_id():" + e.toString(), e);
|
}
|
}
|
sql.close_con();
|
}
|
}
|
|
public static void UpdateDev61850AlmParam(MysqlConnPool con_pool,FBS9100_AlarmParam almparam,FBS9100_StatAndParam param){
|
String sql_str_base = Sql_Mysql.Dev_6185_AlmParam
|
+ " SET "
|
+ "dev_id=" + param.dev_id + ", "
|
+ "groupvol_high_val=" + almparam.groupvol_high_val + ", "
|
+ "groupvol_low_val=" + almparam.groupvol_low_val + ", "
|
+ "monvol_high_val=" + almparam.monvol_high_val + ", "
|
+ "monvol_low_val=" + almparam.monvol_low_val + ", "
|
+ "montmp_high_val=" + almparam.montmp_high_val + ", "
|
+ "montmp_low_val=" + almparam.montmp_low_val + ", "
|
+ "monres_high_val=" + almparam.monres_high_val + ", "
|
+ "monres_low_val=" + almparam.monres_low_val + ", "
|
+ "moncap_high_val=" + almparam.moncap_high_val + ", "
|
+ "moncap_low_val=" + almparam.moncap_low_val ;
|
String sql_str_update = "UPDATE " + sql_str_base + " WHERE dev_id=" + param.dev_id;
|
|
Sql_Mysql sql = new Sql_Mysql(con_pool);
|
try {
|
sql.sqlMysqlExecute(sql_str_update);
|
} catch (SQLException e) {
|
sql.logger.error("FBS9100_Task_Thread_SQL.inseartOrUpdateFBS9100SFODParam():" + e.toString(), e);
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
public static void updateDev61850AlmParamCmdBydev_id(MysqlConnPool con_pool, FBS9100_StatAndParam param)
|
{
|
if((param.op_cmd == FBS9100_ComBase.CMD_ReadAlarmParam_ACK)
|
|| (param.op_cmd == FBS9100_ComBase.CMD_FAIL)
|
|| (param.op_cmd == FBS9100_ComBase.CMD_WriteAlarmParam_ACK))
|
{
|
String sql_str_base = Sql_Mysql.Dev_6185_AlmParam + " SET "
|
+ " dev_id=" + param.dev_id + ", "
|
+ " op_cmd=" + param.op_cmd + " "
|
+ " where dev_id = "+param.dev_id;
|
String sql_str_update = "UPDATE " + sql_str_base;
|
Sql_Mysql sql = new Sql_Mysql(con_pool);
|
try {
|
sql.sqlMysqlExecute(sql_str_update);
|
} catch (SQLException e) {
|
sql.logger.error("FBS9100_Task_Thread_SQL.updateDev61850AlmParamCmdBydev_id():" + e.toString(), e);
|
} finally {
|
sql.close_con();
|
}
|
|
param.m_FBS_AlmParam.op_cmd = param.op_cmd;
|
param.op_cmd = FBS9100_ComBase.CMD_NULL;
|
}
|
}
|
|
/****************************************************************************************************/
|
public static void inseartDev_6185_AlmParam(MysqlConnPool con_pool,int dev_id){
|
String sql_str_ins = " insert into " + Sql_Mysql.Dev_6185_AlmParam+ "(dev_id) values(" + dev_id +");";
|
|
Sql_Mysql sql = new Sql_Mysql(con_pool);
|
ResultSet res = null;
|
try {
|
res = sql.sqlMysqlQuery("SELECT num FROM " + Sql_Mysql.Dev_6185_AlmParam
|
+ " WHERE dev_id = " + dev_id);
|
|
if(!res.next()) {
|
sql.sqlMysqlExecute(sql_str_ins);
|
}
|
} catch (SQLException e) {
|
sql.logger.error("FBS9100_Task_Thread_SQL.inseartDev_6185_AlmParam():" + e.toString(), e);
|
} finally {
|
if(null != res) {
|
try {
|
res.close();
|
} catch (SQLException e) {
|
sql.logger.error("FBS9100_Task_Thread_SQL.inseartOrUpdateFBS9100SFODParam():" + e.toString(), e);
|
}
|
}
|
sql.close_con();
|
}
|
}
|
}
|
