package com.dev_speedcollect;
|
|
import java.sql.ResultSet;
|
import java.sql.SQLException;
|
import java.util.ArrayList;
|
import java.util.Date;
|
|
import com.battmonitor.base.Com;
|
import com.battmonitor.sql.MysqlConnPool;
|
import com.battmonitor.sql.Sql_Mysql;
|
import com.dev_fbs9600s.data.FBS9600S_ComBase;
|
|
public class FBS9600S_SpeedCollect_Task_SQL {
|
|
/**
|
* ´´½¨ÀúÊ·Êý¾ÝÐÅÏ¢±í
|
* @param pool
|
*/
|
public static void createBattCurrDataInf(MysqlConnPool pool) {
|
String sql_str = "CREATE TABLE IF NOT EXISTS "+Sql_Mysql.BattCurrDataInf_Table+" (" +
|
" num bigint(20) NOT NULL AUTO_INCREMENT," +
|
" BattGroupId int(11) NOT NULL DEFAULT '100001' COMMENT 'µç³Ø×éid'," +
|
" test_record_count int(11) NOT NULL DEFAULT '1' COMMENT '¼Ç¼±ÊÊý'," +
|
" test_starttime datetime(3) NOT NULL DEFAULT '2000-01-01 00:00:00.000' COMMENT '¼Ç¼ʱ¼ä'," +
|
" record_num int(11) NOT NULL DEFAULT '0' COMMENT '¼Ç¼±ÊÊý'," +
|
" start_curr float NOT NULL DEFAULT '0' COMMENT '¿ªÊ¼µçÁ÷'," +
|
" end_curr float NOT NULL DEFAULT '0' COMMENT '½áÊøµçÁ÷'," +
|
" note varchar(64) NOT NULL DEFAULT ''," +
|
" PRIMARY KEY (num)," +
|
" KEY index_battgroupid (BattGroupId) USING BTREE," +
|
" KEY index_test_record_count (test_record_count) USING BTREE" +
|
") ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;";
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
/**
|
* ´´½¨ÀúÊ·ÏêϸÐÅÏ¢±í
|
* @param pool
|
*/
|
public static void createBattCurrData(MysqlConnPool pool,int BattGroupId) {
|
String sql_str = "CREATE TABLE IF NOT EXISTS "+Sql_Mysql.BattCurrData_Table+BattGroupId+" (" +
|
" num bigint(20) NOT NULL AUTO_INCREMENT," +
|
" BattGroupId int(11) NOT NULL DEFAULT '10001' COMMENT 'µç³Ø×éid'," +
|
" group_curr float NOT NULL DEFAULT '0' COMMENT '×é¶ËµçÁ÷'," +
|
" test_record_count int(11) NOT NULL DEFAULT '1' COMMENT '¼Ç¼±ÊÊý'," +
|
" test_start_time datetime(3) NOT NULL DEFAULT '2000-01-01 00:00:00.000' COMMENT '²âÊÔ¿ªÊ¼Ê±¼ä'," +
|
" record_time datetime(3) NOT NULL DEFAULT '2000-01-01 00:00:00.000' COMMENT '¼Ç¼ʱ¼ä'," +
|
" record_num int(11) NOT NULL DEFAULT '1' COMMENT 'µ±Ç°¼Ç¼±ÊÊý'," +
|
" note varchar(32) NOT NULL DEFAULT '' COMMENT '±¸ÓÃ'," +
|
" PRIMARY KEY (num)," +
|
" KEY index_battgroupid (BattGroupId) USING BTREE," +
|
" KEY index_test_record_count (test_record_count) USING BTREE" +
|
") ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;";
|
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 data
|
*/
|
public static void insertBattCurrData(MysqlConnPool pool,CurrData data) {
|
//²éѯÀúʷʼþ´ÎÊý
|
String sql_str_sel = "SELECT MAX(test_record_count) as test_record_count FROM db_batt_testdata.tb_battcurrdata_inf WHERE BattGroupId = " + data.BattGroupId + " FOR UPDATE ";
|
ResultSet res = null;
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
res = sql.sqlMysqlQuery(sql_str_sel);
|
int max_test_record_count = 1;
|
try {
|
if(res.next()) {
|
max_test_record_count = res.getInt("test_record_count")+1;
|
}
|
Date test_starttime = new Date(data.record_time.getTime()-3000);
|
System.out.println("test_record_count :"+max_test_record_count);
|
String sql_str_inf = "INSERT INTO "+Sql_Mysql.BattCurrDataInf_Table+"(BattGroupId,test_record_count,test_starttime,record_num,start_curr,end_curr) VALUES("+data.BattGroupId+","+max_test_record_count+",'"+Com.getDateTimeFormat(test_starttime, Com.DTF_YMDhmsS)+"',"+6000+","+data.currs[0]+","+data.currs[data.currs.length-1]+");";
|
String sql_str_id = " INSERT INTO " + Sql_Mysql.BattCurrData_Table + data.BattGroupId+"(BattGroupId,group_curr,test_record_count,test_start_time,record_time,record_num) VALUES ";
|
for(int i = 0;i<data.currs.length;i++) {
|
if(i>0) {
|
sql_str_id += ",";
|
}
|
Date now = new Date(test_starttime.getTime() + i*50);
|
sql_str_id +="("+data.BattGroupId+","+data.currs[i]+","+max_test_record_count+",'"+Com.getDateTimeFormat(test_starttime, Com.DTF_YMDhmsS)+"','"+Com.getDateTimeFormat(now, Com.DTF_YMDhmsS)+"',"+(i+1)+")";
|
}
|
|
ArrayList<String> sql_strs = new ArrayList<String>();
|
sql_strs.add(sql_str_inf);
|
sql_strs.add(sql_str_id);
|
sql.makeManualCommit(sql_strs);
|
} catch (SQLException 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 querySpeedCollectParam(MysqlConnPool pool,SpeedCollectParam param) {
|
String sql_str = "SELECT * FROM " + Sql_Mysql.FBS9100SetParam_Table + " WHERE dev_id = " + param.dev_id;
|
ResultSet res = null;
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
res = sql.sqlMysqlQuery(sql_str);
|
try {
|
if(res.next()) {
|
param.op_cmd = res.getInt("op_cmd");
|
param.CurrClampRange = res.getInt("MonomerLowCount"); //»ô¶ûÁ¿³Ì
|
param.DeltaCurrLimit = res.getInt("MonomerVol_LOW"); //µçÁ÷±ä»¯·§Öµ
|
}
|
} catch (SQLException 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 updateSpeedCollectParam(MysqlConnPool pool,SpeedCollectParam param) {
|
if(
|
param.op_cmd == FBS9600S_ComBase.OP_CMD_GetSpeedParam ||
|
param.op_cmd == FBS9600S_ComBase.OP_CMD_GetSpeedParam_ACK ||
|
param.op_cmd == FBS9600S_ComBase.OP_CMD_SetSpeedParam ||
|
param.op_cmd == FBS9600S_ComBase.OP_CMD_SetSpeedParam_ACK
|
) {
|
String sql_str = "UPDATE " + Sql_Mysql.FBS9100SetParam_Table + " SET op_cmd = "+param.op_cmd+",MonomerLowCount = "+param.CurrClampRange+",MonomerVol_LOW ="+param.DeltaCurrLimit+" WHERE dev_id = " + param.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 param
|
*/
|
public static void InsertOrUpdateSpeedCollectParam(MysqlConnPool pool,SpeedCollectParam param) {
|
String sql_str_sel = " SELECT * FROM " + Sql_Mysql.FBS9100SetParam_Table + " WHERE dev_id = " + param.dev_id;
|
String sql_str_upd = " UPDATE " + Sql_Mysql.FBS9100SetParam_Table + " SET op_cmd = "+param.op_cmd+",MonomerLowCount = "+param.CurrClampRange+",MonomerVol_LOW ="+param.DeltaCurrLimit+" WHERE dev_id = " + param.dev_id;;
|
String sql_str_ins = " INSERT INTO " + Sql_Mysql.FBS9100SetParam_Table + "(dev_id) VALUE("+param.dev_id+")";
|
ResultSet res = null;
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
res = sql.sqlMysqlQuery(sql_str_sel);
|
try {
|
if(res.next()) {
|
sql.sqlMysqlExecute(sql_str_upd);
|
}else {
|
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();
|
}
|
}
|
}
|
