package com.dev.fg;
|
|
import com.base.Com;
|
import com.sql.MysqlConnPool;
|
import com.sql.Sql_Mysql;
|
import java.sql.ResultSet;
|
import java.sql.SQLException;
|
import java.util.ArrayList;
|
import java.util.Date;
|
|
public class FBS9600_State_SQL {
|
|
/**
|
* 查询当前是否有控制操作
|
* @param pool
|
* @param state
|
*/
|
public static void queryFBS9600SetParamTable(MysqlConnPool pool,FBS9600_State state) {
|
String sql_str = " SELECT op_cmd FROM db_ram_db.tb_fbs9600_state WHERE dev_id = " + state.dev_id;
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
ResultSet res = null;
|
try {
|
res = sql.sqlMysqlQuery(sql_str);
|
if(res.next()) {
|
state.setFBS9600Cmd(res.getInt("op_cmd"));
|
//state.BattGroupNum = res.getInt("BattGroupNum");
|
state.BattGroupNum = 0;
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
if(null != res) {
|
try {
|
res.close();
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
}
|
sql.close_con();
|
}
|
}
|
|
/**
|
* 更新当前操作返回状态
|
* @param pool
|
* @param state
|
*/
|
public static void updateFBS9600SetParamTable(MysqlConnPool pool,FBS9600_State state) {
|
String sql_str = " UPDATE db_ram_db.tb_fbs9600_state SET op_cmd = "+state.getFBS9600Cmd()+" WHERE dev_id = " + state.dev_id;
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
|
/**
|
* 插入设备状态表
|
* @param pool
|
* @param state
|
*/
|
public static void insertFBS9100State_Table(MysqlConnPool pool,FBS9600_State state) {
|
String sql_str_sel = " SELECT op_cmd FROM db_ram_db.tb_fbs9100_state WHERE dev_id = " + state.dev_id;
|
String sql_str_ins = " INSERT INTO db_ram_db.tb_fbs9100_state(dev_id,dev_ip) VALUES("+state.dev_id+",0);";
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
ResultSet res = null;
|
try {
|
res = sql.sqlMysqlQuery(sql_str_sel);
|
if(!res.next()) {
|
sql.sqlMysqlExecute(sql_str_ins);
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
if(null != res) {
|
try {
|
res.close();
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
}
|
sql.close_con();
|
}
|
}
|
|
public static void createFBS9600StateTableOnRam(MysqlConnPool con_pool, ArrayList<FBS9600_State> al_stat) {
|
String str1 = "DROP TABLE IF EXISTS `db_ram_db`.`tb_fbs9600_state`";
|
String str2 = "CREATE TABLE IF NOT EXISTS `db_ram_db`.`tb_fbs9600_state` ( `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, `dev_workstate` INT NOT NULL DEFAULT 0, `dev_alarmstate` INT NOT NULL DEFAULT 0, `dev_commcount` INT NOT NULL DEFAULT 0, `dev_errcommcount` INT NOT NULL DEFAULT 0, PRIMARY KEY (`num`) ) ENGINE=MEMORY DEFAULT CHARSET=utf8";
|
|
String str3 = "";
|
if (al_stat.size() > 0) {
|
str3 = "INSERT INTO `db_ram_db`.`tb_fbs9600_state` (dev_ip, dev_id) VALUES";
|
}
|
|
for (int n = 0; n < al_stat.size(); n++) {
|
if (n > 0) {
|
str3 = str3 + ", ";
|
}
|
str3 = str3 + " ('" + ((FBS9600_State) al_stat.get(n)).ip_addr + "',"
|
+ ((FBS9600_State) al_stat.get(n)).dev_id + ")";
|
}
|
|
Sql_Mysql sql = new Sql_Mysql(con_pool.getConn());
|
try {
|
sql.sqlMysqlExecute(str1);
|
sql.sqlMysqlExecute(str2);
|
if (str3.length() > 0)
|
sql.sqlMysqlExecute(str3);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
public static void updateFbs9600State(MysqlConnPool con_pool, ArrayList<FBS9600_State> al_stat) {
|
String sql_str = "UPDATE `db_ram_db`.`tb_fbs9600_state` SET dev_workstate = CASE dev_id ";
|
|
for (int n = 0; n < al_stat.size(); n++) {
|
sql_str = sql_str + " WHEN " + ((FBS9600_State) al_stat.get(n)).dev_id + " THEN "
|
+ ((FBS9600_State) al_stat.get(n)).work_state;
|
}
|
sql_str = sql_str + " END, dev_commcount = CASE dev_id ";
|
for (int n = 0; n < al_stat.size(); n++) {
|
sql_str = sql_str + " WHEN " + ((FBS9600_State) al_stat.get(n)).dev_id + " THEN "
|
+ ((FBS9600_State) al_stat.get(n)).com_count;
|
}
|
sql_str = sql_str + " END, dev_errcommcount = CASE dev_id ";
|
for (int n = 0; n < al_stat.size(); n++) {
|
sql_str = sql_str + " WHEN " + ((FBS9600_State) al_stat.get(n)).dev_id + " THEN "
|
+ ((FBS9600_State) al_stat.get(n)).com_err_count;
|
}
|
sql_str = sql_str + " END WHERE dev_id IN (";
|
for (int n = 0; n < al_stat.size(); n++) {
|
if (n > 0) {
|
sql_str = sql_str + ",";
|
}
|
sql_str = sql_str + ((FBS9600_State) al_stat.get(n)).dev_id;
|
}
|
sql_str = sql_str + ")";
|
Sql_Mysql sql = new Sql_Mysql(con_pool.getConn());
|
String sql_str2 = "INSERT IGNORE INTO `db_ram_db`.`tb_fbs9600_state` ( dev_ip, dev_id, op_cmd, dev_workstate, dev_alarmstate, dev_commcount, dev_errcommcount) VALUES('127.0.0',"
|
+ ((FBS9600_State) al_stat.get(0)).dev_id + "," + '0' + "," + '0' + "," + '0' + "," + '0' + "," + '0'
|
+ ")";
|
try {
|
sql.sqlMysqlExecute(sql_str2);
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException localSQLException) {
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
public static void updateFbs9100State(MysqlConnPool con_pool, ArrayList<FBS9600_State> al_stat) {
|
|
|
|
String sql_str = "UPDATE "+Sql_Mysql.FBS9100State_Table+" SET dev_workstate = CASE dev_id ";
|
|
for (int n = 0; n < al_stat.size(); n++) {
|
sql_str = sql_str + " WHEN " + ((FBS9600_State) al_stat.get(n)).dev_id + " THEN "
|
+ ((FBS9600_State) al_stat.get(n)).work_state;
|
}
|
sql_str = sql_str + " END, record_datetime = CASE dev_id ";
|
for (int n = 0; n < al_stat.size(); n++) {
|
sql_str = sql_str + " WHEN " + ((FBS9600_State) al_stat.get(n)).dev_id + " THEN '"+Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms)+"'";
|
}
|
sql_str = sql_str + " END, dev_commcount = CASE dev_id ";
|
|
for (int n = 0; n < al_stat.size(); n++) {
|
sql_str = sql_str + " WHEN " + ((FBS9600_State) al_stat.get(n)).dev_id + " THEN "
|
+ ((FBS9600_State) al_stat.get(n)).com_count;
|
}
|
sql_str = sql_str + " END, dev_errcommcount = CASE dev_id ";
|
for (int n = 0; n < al_stat.size(); n++) {
|
sql_str = sql_str + " WHEN " + ((FBS9600_State) al_stat.get(n)).dev_id + " THEN "
|
+ ((FBS9600_State) al_stat.get(n)).com_err_count;
|
}
|
sql_str = sql_str + " END WHERE dev_id IN (";
|
for (int n = 0; n < al_stat.size(); n++) {
|
if (n > 0) {
|
sql_str = sql_str + ",";
|
}
|
sql_str = sql_str + ((FBS9600_State) al_stat.get(n)).dev_id;
|
}
|
sql_str = sql_str + ")";
|
Sql_Mysql sql = new Sql_Mysql(con_pool.getConn());
|
String sql_str2 = "INSERT IGNORE INTO "+Sql_Mysql.FBS9100State_Table+" ( dev_ip, dev_id, op_cmd, dev_workstate, dev_alarmstate, dev_commcount, dev_errcommcount) VALUES('127.0.0',"
|
+ ((FBS9600_State) al_stat.get(0)).dev_id + "," + '0' + "," + '0' + "," + '0' + "," + '0' + "," + '0'
|
+ ")";
|
try {
|
sql.sqlMysqlExecute(sql_str2);
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException e) {
|
//System.err.println(e);
|
} finally {
|
//System.out.println("############");
|
sql.close_con();
|
}
|
}
|
|
public static void queryFbs9600UsrCmd(MysqlConnPool con_pool, ArrayList<FBS9600_State> al_stat) {
|
String sql_str = "SELECT dev_id,op_cmd FROM " + Sql_Mysql.FBS9100SetParam_Table;
|
Sql_Mysql sql = new Sql_Mysql(con_pool.getConn());
|
ResultSet res = sql.sqlMysqlQuery(sql_str);
|
try {
|
while (res.next()) {
|
int id = res.getInt("dev_id");
|
int cmd = res.getInt("op_cmd");
|
for (int n = 0; n < al_stat.size(); n++)
|
if (id == ((FBS9600_State) al_stat.get(n)).dev_id) {
|
if ((((FBS9600_State) al_stat.get(n)).getFBS9600Cmd() != 0)
|
&& (82 != ((FBS9600_State) al_stat.get(n)).getFBS9600Cmd()))
|
break;
|
((FBS9600_State) al_stat.get(n)).setFBS9600CmdAckFromDev(false);
|
((FBS9600_State) al_stat.get(n)).setFBS9600Cmd(cmd);
|
|
break;
|
}
|
}
|
} catch (SQLException localSQLException) {
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
public static void updateFbs9600UsrCmdAck(MysqlConnPool con_pool, ArrayList<FBS9600_State> al_stat) {
|
String sql_str = "UPDATE "+Sql_Mysql.FBS9100SetParam_Table+" SET ";
|
|
sql_str = sql_str + " op_cmd = CASE dev_id ";
|
for (int n = 0; n < al_stat.size(); n++) {
|
FBS9600_State stat = (FBS9600_State) al_stat.get(n);
|
if ((stat.getFBS9600Cmd() != 0) && (stat.getFBS9600CmdAckFromDev())) {
|
sql_str = sql_str + " WHEN " + ((FBS9600_State) al_stat.get(n)).dev_id + " THEN " + 82;
|
}
|
}
|
sql_str = sql_str + " END WHERE dev_id IN (";
|
int count = 0;
|
for (int n = 0; n < al_stat.size(); n++) {
|
if (count > 0) {
|
sql_str = sql_str + ",";
|
}
|
FBS9600_State stat = (FBS9600_State) al_stat.get(n);
|
if ((stat.getFBS9600Cmd() != 0) && (stat.getFBS9600CmdAckFromDev())) {
|
sql_str = sql_str + ((FBS9600_State) al_stat.get(n)).dev_id;
|
count++;
|
}
|
}
|
sql_str = sql_str + ")";
|
|
if (count > 0) {
|
Sql_Mysql sql = new Sql_Mysql(con_pool.getConn());
|
try {
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException localSQLException) {
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
for (int n = 0; n < al_stat.size(); n++) {
|
FBS9600_State stat = (FBS9600_State) al_stat.get(n);
|
if ((stat.getFBS9600Cmd() != 0) && (stat.getFBS9600CmdAckFromDev())) {
|
stat.setFBS9600Cmd(0);
|
stat.setFBS9600CmdAckFromDev(false);
|
}
|
}
|
}
|
|
public static void insertFBS9100SetParam_Table(MysqlConnPool pool,int dev_id) {
|
String sql_str_sel = " select dev_id from " + Sql_Mysql.FBS9100SetParam_Table + " Where dev_id = " + dev_id;
|
String sql_str_ins = " insert into " + Sql_Mysql.FBS9100SetParam_Table + "(dev_id) values("+dev_id+")";
|
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
ResultSet res = null;
|
try {
|
res = sql.sqlMysqlQuery(sql_str_sel);
|
if(!res.next()) {
|
sql.sqlMysqlExecute(sql_str_ins);
|
}
|
} catch (Exception e) {
|
e.printStackTrace();
|
} finally {
|
if(null != res) {
|
try {
|
res.close();
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
}
|
sql.close_con();
|
}
|
}
|
|
public static void updateFBS9100State_Table(MysqlConnPool pool, FBS9600_State state) {
|
String sql_str = "UPDATE db_ram_db.tb_fbs9100_state "
|
+ " SET record_datetime = '"+Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms)+"',"
|
+ " dev_workstate = "+state.getWorkState()+","
|
+ " dev_captest_groupvol="+state.groupVol[0]+","
|
+ " dev_captest_curr = "+state.groupCurr[0]+","
|
+ " dev_res_test_state = "+state.ResState[0]+","
|
+ " dev_restest_count = "+state.batt_res_test_cnt+","
|
+ " dev_commcount = "+state.com_count+","
|
+ " dev_errcommcount= "+state.com_err_count
|
+ " WHERE dev_id= " + state.dev_id;
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
/**
|
* 更新程序启动时间以及对应程序的版本号
|
* @param pool
|
* @param version
|
*/
|
public static void updateProcess_StartTime(MysqlConnPool pool,double version) {
|
String sql_str = " UPDATE " + Sql_Mysql.Tb_Process_SurveyTable
|
+ " SET Process_starttime='"+Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms)+"'"
|
+ " ,ProcessTime='"+Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms)+"'"
|
+ " ,ProcessVersion='V"+version+"' "
|
+ " WHERE ProcessId = 11011";
|
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
/**
|
* 更新程序启动时间以及对应程序的版本号
|
* @param pool
|
* @param version
|
*/
|
public static void updateProcess_ProcessTime(MysqlConnPool pool) {
|
String sql_str = " UPDATE " + Sql_Mysql.Tb_Process_SurveyTable
|
+ " SET ProcessTime='"+Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms)+"'"
|
+ " WHERE ProcessId = 11011 ";
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
/**
|
* 插入默认数据
|
* @param pool
|
* @param param
|
*/
|
public static void insertFBS9100SetParam_Table(MysqlConnPool pool,FBS9600_State param) {
|
String sql_str_sel = " SELECT * FROM " + Sql_Mysql.FBS9100SetParam_Table + " where dev_id = " + param.dev_id;
|
String sql_str_ins = " INSERT INTO " + Sql_Mysql.FBS9100SetParam_Table + "(dev_id) values("+param.dev_id+")";
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
ResultSet res = null;
|
try {
|
res = sql.sqlMysqlQuery(sql_str_sel);
|
if(!res.next()) {
|
sql.sqlMysqlExecute(sql_str_ins);
|
}
|
} catch (Exception e) {
|
e.printStackTrace();
|
} finally {
|
if(null != res) {
|
try {
|
res.close();
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
}
|
sql.close_con();
|
}
|
}
|
|
/**
|
* 查询对应的参数
|
* @param pool
|
* @param param
|
*/
|
public static void queryFBS9100SetParam_Table(MysqlConnPool pool,FBS9600_State state) {
|
String sql_str_sel = " SELECT * FROM " + Sql_Mysql.FBS9100SetParam_Table + " where dev_id = " + state.dev_id;
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
ResultSet res = null;
|
try {
|
res = sql.sqlMysqlQuery(sql_str_sel);
|
if(res.next()) {
|
state.setOp_cmd(res.getInt("op_cmd"));
|
state.setBattGroupNum(res.getInt("battGroupNum"));
|
state.setTest_cmd(res.getInt("testCmd"));
|
}
|
} catch (Exception e) {
|
e.printStackTrace();
|
} finally {
|
if(null != res) {
|
try {
|
res.close();
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
}
|
sql.close_con();
|
}
|
}
|
|
/**
|
* 更新汇集控制状态
|
* @param pool
|
* @param param
|
*/
|
public static void updateFBS9100SetParam_Table(MysqlConnPool pool,FBS9600_State param) {
|
String sql_str_upd = " UPDATE " + Sql_Mysql.FBS9100SetParam_Table + " SET op_cmd = "+param.getOp_cmd()+" where dev_id = " + param.dev_id;
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
sql.sqlMysqlExecute(sql_str_upd);
|
} catch (Exception e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
/**
|
* 启动是模拟每个电池组的内阻数据值
|
* @param gB_MysqlConnPool
|
*/
|
public static void MakeMonResDataRandom(MysqlConnPool pool) {
|
String sql_str_sel = "SELECT * FROM " + Sql_Mysql.BattRtData_Table;
|
ResultSet res = null;
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
res = sql.sqlMysqlQuery(sql_str_sel);
|
while(res.next()) {
|
int battgroupid = res.getInt("BattGroupId");
|
int mon_num = res.getInt("mon_num");
|
|
sql.sqlMysqlExecute("UPDATE " + Sql_Mysql.BattRtData_Table + " SET mon_res = " + String.format("%1.3f", (float)(0.101+Math.random()*0.298)) + " WHERE BattGroupId = " + battgroupid + " AND mon_num = " + mon_num);
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
if(null != res) {
|
try {
|
res.close();
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
}
|
sql.close_con();
|
}
|
}
|
|
public static void main(String[] args) {
|
MysqlConnPool pool = new MysqlConnPool("127.0.0.1", 3360, 5);
|
MakeMonResDataRandom(pool);
|
|
// for(int i = 0;i< 100000;i++) {
|
// float ran = (float)(0.101+Math.random()*0.298);
|
// if(ran < 0.101 || ran > 0.4 ) {
|
// System.err.println(ran);
|
// }
|
// System.out.println(String.format("%1.3f", ran));
|
// }
|
}
|
}
|
