package com.dev_concentrator.data;
|
|
import java.sql.ResultSet;
|
import java.sql.SQLException;
|
import java.util.Date;
|
|
import com.battmonitor.base.Com;
|
import com.battmonitor.data.BattData_RT;
|
import com.battmonitor.data.BattData_RT_Array;
|
import com.battmonitor.data.MonVolData;
|
import com.battmonitor.sql.MysqlConnPool;
|
import com.battmonitor.sql.Sql_Mysql;
|
|
public class FBS9800_SocketThread_SQL {
|
|
/**
|
* ²åÈëĬÈÏÊý¾Ý
|
* @param pool
|
* @param param
|
*/
|
public static void insertFBS9100SetParam_Table(MysqlConnPool pool,FBS9800_SetParam 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+")";
|
String sql_str_upd = " UPDATE " + Sql_Mysql.FBS9100SetParam_Table + " SET op_cmd = 0 WHERE dev_id = "+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);
|
}else {
|
sql.sqlMysqlExecute(sql_str_upd);
|
}
|
} catch (Exception e) {
|
sql.logger.error(e.toString(), e);
|
} finally {
|
if(null != res) {
|
try {
|
res.close();
|
} catch (SQLException e) {
|
sql.logger.error(e.toString(), e);
|
}
|
}
|
sql.close_con();
|
}
|
}
|
|
/**
|
* ²éѯ¶ÔÓ¦µÄ²ÎÊý
|
* @param pool
|
* @param param
|
*/
|
public static void queryFBS9100SetParam_Table(MysqlConnPool pool,FBS9800_SetParam param) {
|
String sql_str_sel = " SELECT * FROM " + Sql_Mysql.FBS9100SetParam_Table + " where dev_id = " + param.dev_id;
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
ResultSet res = null;
|
try {
|
res = sql.sqlMysqlQuery(sql_str_sel);
|
if(res.next()) {
|
param.setOp_cmd(res.getInt("op_cmd"));
|
param.setBattGroupNum(res.getInt("battGroupNum"));
|
param.setTestCmd(res.getInt("testCmd"));
|
}
|
} catch (Exception e) {
|
e.printStackTrace();
|
} finally {
|
if(null != res) {
|
try {
|
res.close();
|
} catch (SQLException e) {
|
sql.logger.error(e.toString(), e);
|
}
|
}
|
sql.close_con();
|
}
|
}
|
|
/**
|
* ¸üл㼯¿ØÖÆ״̬
|
* @param pool
|
* @param param
|
*/
|
public static void updateFBS9100SetParam_Table(MysqlConnPool pool,FBS9800_SetParam 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) {
|
sql.logger.error(e.toString(), e);
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
public static BattData_RT_Array queryDeviceFromDataBase(MysqlConnPool pool,int dev_id) {
|
BattData_RT_Array dataArray = null;
|
String sql_str1 = " SELECT DISTINCT " +
|
" FBSDeviceId,FBSDeviceIp,BattGroupName6," +
|
" BattGroupNum,BattFloatCurrent,FloatVolLevel,MonVolStd " +
|
" FROM " + Sql_Mysql.BattInf_Table +
|
" WHERE FBSDeviceId = " + dev_id;
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
ResultSet res = null;
|
res = sql.sqlMysqlQuery(sql_str1);
|
try {
|
if(res.next()) {
|
dataArray = new BattData_RT_Array();
|
|
String sql_str2 = "SELECT * " +
|
" FROM " + Sql_Mysql.BattInf_Table +
|
" WHERE FBSDeviceId = " + dev_id +
|
" ORDER BY GroupIndexInFBSDevice ";
|
|
res = sql.sqlMysqlQuery(sql_str2);
|
while(res.next()) {
|
BattData_RT battData = new BattData_RT();
|
battData.setM_Conn_Pool(pool);
|
battData.FBSDeviceId = res.getInt("FBSDeviceId");
|
battData.BattGroupId = res.getInt("BattGroupId");
|
battData.BattGroupNum = res.getInt("BattGroupNum");
|
battData.StationId = res.getInt("StationId");
|
battData.MonStdVol = res.getFloat("MonVolStd");
|
battData.MonStdCap = res.getFloat("MonCapStd");
|
battData.MonCount = res.getInt("MonCount");
|
battData.GroupIndexInFBSDevice = res.getInt("GroupIndexInFBSDevice");
|
if(battData.MonCount >0) {
|
for(int i = 0;i<battData.MonCount;i++) {
|
battData.al_MonVol.add(new MonVolData());
|
battData.al_MonVol_RtOld.add(new MonVolData());
|
battData.al_MonVol_History.add(new MonVolData());
|
|
}
|
}
|
|
dataArray.addItem(battData);
|
}
|
}
|
} catch (SQLException e) {
|
sql.logger.error(e.toString(), e);
|
} finally {
|
if(res != null) {
|
try {
|
res.close();
|
} catch (SQLException e) {
|
sql.logger.error(e.toString(), e);
|
}
|
}
|
sql.close_con();
|
}
|
return dataArray;
|
}
|
|
|
/**
|
* ¸ù¾ÝÉ豸id¸üРtb_fbs9100_state ±í
|
* @param con_pool
|
* @param v_data
|
* @param dev_id
|
*/
|
public static void insertOrupdateFbs9100StateByDev_Id(MysqlConnPool con_pool, FBS9800_SysState sysState)
|
{
|
|
String sql_str_query = "SELECT * FROM db_ram_db.tb_fbs9100_state WHERE dev_id = "+sysState.getDev_id();
|
String sql_str_base = Sql_Mysql.FBS9100State_Table
|
+ " SET "
|
+ "dev_id=" + sysState.getDev_id() + ", "
|
+ "dev_version='V" + sysState.sysversion + "', "
|
+ "dev_workstate=" + sysState.getSysstate() + ", "
|
+ "dev_restest_count=" + sysState.dev_restest_count + ", "
|
+ "dev_res_test_state=" + sysState.dev_res_test_state + ", "
|
+ "dev_ip='" + sysState.getDev_ip() + "', "
|
+ "record_datetime="
|
+ "'" + Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms) + "',"
|
+ "dev_commcount=" + sysState.getDev_commcount() + ", "
|
+ "dev_errcommcount=" + sysState.getDev_errcommcount();
|
|
|
//String sql_str_replace = "REPLACE INTO " + sql_str_base;
|
String sql_str_update = " UPDATE " + sql_str_base + " WHERE dev_id = "+sysState.getDev_id();
|
String sql_str_insert = " INSERT INTO "+Sql_Mysql.FBS9100State_Table
|
+"(dev_id,dev_ip,record_datetime,dev_commcount,dev_errcommcount) "
|
+ "VALUES("+sysState.getDev_id()+",'"+sysState.getDev_ip()+"','"+Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms)+"',"+sysState.getDev_commcount()+","+sysState.getDev_errcommcount()+"); ";
|
Sql_Mysql sql = new Sql_Mysql(con_pool.getConn());
|
ResultSet res = sql.sqlMysqlQuery(sql_str_query);
|
try {
|
if(res != null && res.next()) {
|
//¸üÐÂÊý¾Ý
|
sql.sqlMysqlExecute(sql_str_update);
|
}else {
|
//²åÈëÊý¾Ý
|
sql.sqlMysqlExecute(sql_str_insert);
|
}
|
//System.out.println(sql_str);
|
} catch (SQLException e) {
|
sql.logger.error(e.toString(), e);
|
} finally {
|
if(res != null) {
|
try {
|
res.close();
|
} catch (SQLException e) {
|
sql.logger.error(e.toString(), e);
|
}
|
}
|
sql.close_con();
|
}
|
}
|
|
//²åÈë»ò¸üÐÂFBSÉ豸״̬(½öÊÊÓÃÓÚMYSQL)
|
public static void insertOrUpdateFBS9600StateByDevId(MysqlConnPool con_pool, FBS9800_SysState sysState) {
|
String sql_str = "INSERT INTO " +Sql_Mysql.FBS9100State_Table
|
+ " (dev_id,dev_ip,record_datetime,dev_commcount,dev_errcommcount) "
|
+ " VALUES ("+sysState.getDev_id()+",'"+sysState.getDev_ip()+"','"+Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms)+"',"+sysState.getDev_commcount()+","+sysState.getDev_errcommcount()+") "
|
+ " ON DUPLICATE KEY UPDATE dev_id=values(dev_id),dev_ip=values(dev_ip),record_datetime=values(record_datetime),dev_commcount = values(dev_commcount),dev_errcommcount = values(dev_errcommcount)";
|
//System.out.println(sql_str);
|
Sql_Mysql sql = new Sql_Mysql(con_pool.getConn());
|
try {
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException e) {
|
sql.logger.error(e.toString(), e);
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
|
//²é¿´µ±Ç°Êý¾Ý¿âÖÐÊÇ·ñ´æÔÚµ±Ç°³ÌÐòµÄ½ø³Ì£¬²¢½øÐгõʼ»¯
|
public static void initConcentratorThreadSQL(MysqlConnPool conn_pool) {
|
Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
|
//Ìí¼ÓFBS»ã¼¯Æ÷ͨѶ½ø³Ì¼Ç¼
|
ResultSet res = sql.sqlMysqlQuery("SELECT * FROM "+ Sql_Mysql.ProcessSurvey_Table + " WHERE ProcessId = 11030");
|
try {
|
if(false == res.next()) {
|
sql.sqlMysqlExecute("INSERT INTO " + Sql_Mysql.ProcessSurvey_Table + "(ProcessId,ProcessName,ProcessTime,Process_starttime,ServerName,ServerFlag,ProcessVersion,note,OutTime) VALUES ('11030', 'BMS_FBS9800_DISPLAY', '1970-01-01 00:00:00', '1970-01-01 00:00:00', 'BMS_FBS9800_DISPLAY', '2', 'v1.101', 'FBS9800»ã¼¯Æ÷ÏÔʾÆÁͨѶÏß³Ì', '300');");
|
}
|
} catch (SQLException e) {
|
sql.logger.error(e.toString(), e);
|
} finally {
|
if(res != null) {
|
try {
|
res.close();
|
} catch (SQLException e) {
|
sql.logger.error(e.toString(), e);
|
}
|
}
|
sql.close_con();
|
}
|
}
|
|
//¸üÐÂÏ̼߳à¿ØµÄÆô¶¯Ê±¼ä
|
public static void updateConcentratorThread_StartTime(MysqlConnPool conn_pool,String version){
|
Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
|
try {
|
sql.sqlMysqlUseDB(Sql_Mysql.WEB_Site);
|
String sql_str = "UPDATE tb_process_survey "
|
+ "SET Process_starttime = '"+Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms)+"'"
|
+ ",ProcessVersion = '"+version+"'"
|
+ " WHERE processid = 11030 ";
|
//System.out.println(sql_str);
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException e) {
|
sql.logger.error(e.toString(), e);
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
//¸üл㼯Æ÷ͨѶÏ̵߳Äʱ¼ä´Á
|
public static void updateConcentratorThread_Time(MysqlConnPool conn_pool){
|
Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
|
try {
|
sql.sqlMysqlUseDB(Sql_Mysql.WEB_Site);
|
String sql_str = "UPDATE tb_process_survey SET ProcessTime = '"+Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms)
|
+ "' WHERE processid = 11030";
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException e) {
|
sql.logger.error(e.toString(), e);
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
/*public static void main(String[] args) {
|
Concentrator_SysState sys = new Concentrator_SysState();
|
sys.setDev_id(960500001);
|
MysqlConnPool pool = new MysqlConnPool("127.0.0.1", 3360, 10);
|
insertOrUpdateFBS9600StateByDevId(pool, sys);
|
}*/
|
}
|
