package com.dev.fgcd.comm;
|
|
import java.sql.ResultSet;
|
import java.sql.SQLException;
|
import java.util.ArrayList;
|
import java.util.Date;
|
import java.util.List;
|
|
import com.battmonitor.sql.Com;
|
import com.battmonitor.sql.MysqlConnPool;
|
import com.battmonitor.sql.Sql_Mysql;
|
import com.dev.fgcd.data.FGCD_TestFBOFile;
|
import com.dev.fgcd.data.FGCD_TestFBOFile.FBOFile;
|
import com.dev.fgcd.fbo.FboData;
|
import com.dev.fgcd.fbo.FboDataInf;
|
import com.mchange.v2.resourcepool.ResourcePoolListener;
|
|
public class FGCD_Task_Thread_SQL {
|
|
/**
|
* ²åÈëFBOÀúÊ·Êý¾Ý
|
* @param pool
|
* @param file
|
* @param fbo
|
* @param datas
|
* @return
|
*/
|
public static boolean insertFBODataIntoTable(MysqlConnPool pool,FBOFile file,FboDataInf fbo,List<FboData> datas,int BattGoupId) {
|
boolean success = false;
|
if(fbo.getTestTimeLong() > 0) {
|
createTb_FboTestData_Table(pool, BattGoupId);
|
int max_test_record_count = quereyBattMaxTestRecordCount(pool, BattGoupId);
|
ArrayList<String> sql_strs = new ArrayList<>();
|
sql_strs.add("INSERT INTO " + Sql_Mysql.Tb_FboTestData_Inf_Table +
|
"(BattGroupId,test_record_count,test_starttime,test_timelong,device,test_type,hourrate,save_interval,monomervol,moncapstd,test_curr,test_cap,monvol_limit,gourpvol_limit,mon_count,monvol_limitcount,stop_type,download_time) " +
|
" VALUES(" + BattGoupId
|
+ "," +max_test_record_count
|
+ ",'" + Com.getDateTimeFormat(fbo.TestStartTime.getFBODateTime(), Com.DTF_YMDhms)
|
+ "'," + fbo.getTestTimeLong()
|
+ "," + fbo.Device
|
+ "," + fbo.DataType
|
+ "," + fbo.HourRate
|
+ "," + fbo.SaveInterval
|
+ "," + fbo.MonomerVol
|
+ "," + fbo.STDCap
|
+ "," + fbo.TestCur
|
+ "," + fbo.TestCap
|
+ "," + fbo.MVLLimit
|
+ "," + fbo.SumVLLimit
|
+ "," + fbo.BattSum
|
+ "," + fbo.MVLLimitCount
|
+ "," + fbo.StopType
|
+ ",'" + Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms)
|
+ "')");
|
for(int i = 0;i<datas.size();i++) {
|
FboData data = datas.get(i);
|
//System.out.println("²âÊÔʱ³¤"+data.m_TestTime.getTestTimeString());
|
String sql_str = "INSERT INTO "+Sql_Mysql.Tb_FboTestData_Table+BattGoupId+"(BattGroupId,test_record_count,test_starttime,test_timelong,record_time,record_num,online_vol,sum_vol,test_curr,test_cap,mon_num,mon_vol) " +
|
" VALUES";
|
Date record_time = new Date(fbo.TestStartTime.getFBODateTime().getTime()+data.m_TestTime.getTestTimeLong()*1000);
|
if(data.BattSum > 0) {
|
//Óе¥Ìåʱ
|
for(int k=0;k<data.BattSum && k<data.SingleVol.length;k++) {
|
sql_strs.add(sql_str + "("
|
+ "" + BattGoupId
|
+ "," + max_test_record_count
|
+ ",'" + Com.getDateTimeFormat(fbo.TestStartTime.getFBODateTime(), Com.DTF_YMDhms)
|
+ "'," + data.m_TestTime.getTestTimeLong()
|
+ ",'" + Com.getDateTimeFormat(record_time, Com.DTF_YMDhms)
|
+ "'," + (i+1)
|
+ "," + data.OnlineVol
|
+ "," + data.SumVoltage
|
+ "," + data.SumCurrent
|
+ "," + data.AllCap
|
+ "," + (k+1)
|
+ "," + data.SingleVol[k]
|
+ ")");
|
}
|
}else {
|
//ûÓе¥ÌåÊý¾Ýʱ
|
sql_strs.add(sql_str + "("
|
+ "" + BattGoupId
|
+ "," + max_test_record_count
|
+ ",'" + Com.getDateTimeFormat(fbo.TestStartTime.getFBODateTime(), Com.DTF_YMDhms)
|
+ "'," + data.m_TestTime.getTestTimeLong()
|
+ ",'" + Com.getDateTimeFormat(record_time, Com.DTF_YMDhms)
|
+ "'," + (i+1)
|
+ "," + data.OnlineVol
|
+ "," + data.SumVoltage
|
+ "," + data.SumCurrent
|
+ "," + data.AllCap
|
+ "," + 0
|
+ "," + 0
|
+ ")");
|
}
|
}
|
Sql_Mysql sql = new Sql_Mysql(pool);
|
ResultSet res = null;
|
try {
|
res = sql.sqlMysqlQuery(" SELECT * " +
|
" FROM " + Sql_Mysql.Tb_FboTestData_Inf_Table +
|
" WHERE BattGroupId = "+BattGoupId+" AND test_starttime = '"+Com.getDateTimeFormat(fbo.TestStartTime.getFBODateTime(), Com.DTF_YMDhms)+"'");
|
if(!res.next()) {
|
success = sql.makeManualCommit(sql_strs);
|
}else {
|
//System.out.println("ÒÑ´æÔÚµ±Ç°²âÊÔÊý¾Ý");
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
if(null != res) {
|
try {
|
res.close();
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
}
|
sql.close_con();
|
}
|
}
|
return success;
|
}
|
//²åÈëFBO¡ª¡ªinfÐÅÏ¢
|
public static boolean insertFBOInfIntoTable(MysqlConnPool pool,FboDataInf fbo,int BattGoupId,int max_test_record_count) {
|
boolean flag=true;
|
if(fbo.getTestTimeLong() > 0) {
|
String sql_str="INSERT INTO " + Sql_Mysql.Tb_FboTestData_Inf_Table +
|
"(BattGroupId,test_record_count,test_starttime,test_timelong,device,test_type,hourrate,save_interval,monomervol,moncapstd,test_curr,test_cap,monvol_limit,gourpvol_limit,mon_count,monvol_limitcount,stop_type,download_time) " +
|
" VALUES(" + BattGoupId
|
+ "," +max_test_record_count
|
+ ",'" + Com.getDateTimeFormat(fbo.TestStartTime.getFBODateTime(), Com.DTF_YMDhms)
|
+ "'," + fbo.getTestTimeLong()
|
+ "," + fbo.Device
|
+ "," + fbo.DataType
|
+ "," + fbo.HourRate
|
+ "," + fbo.SaveInterval
|
+ "," + fbo.MonomerVol
|
+ "," + fbo.STDCap
|
+ "," + fbo.TestCur
|
+ "," + fbo.TestCap
|
+ "," + fbo.MVLLimit
|
+ "," + fbo.SumVLLimit
|
+ "," + fbo.BattSum
|
+ "," + fbo.MVLLimitCount
|
+ "," + fbo.StopType
|
+ ",'" + Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms)
|
+ "')";
|
Sql_Mysql sql = new Sql_Mysql(pool);
|
ResultSet res = null;
|
try {
|
res = sql.sqlMysqlQuery(" SELECT * " +
|
" FROM " + Sql_Mysql.Tb_FboTestData_Inf_Table +
|
" WHERE BattGroupId = "+BattGoupId+" AND test_starttime = '"+Com.getDateTimeFormat(fbo.TestStartTime.getFBODateTime(), Com.DTF_YMDhms)+"'");
|
if(!res.next()) {
|
//Èç¹û²»´æÔÚÔòÌí¼Óinf±í¼Ç¼
|
sql.sqlMysqlExecute(sql_str);
|
}else {
|
//System.out.println("ÒÑ´æÔÚµ±Ç°²âÊÔÊý¾Ý");
|
flag=false;
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
if(null != res) {
|
try {
|
res.close();
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
}
|
sql.close_con();
|
}
|
}
|
return flag;
|
}
|
|
//²åÈëFBOÀúÊ·Êý¾Ý
|
public static boolean insertFBODataIntoTableByOneSql(MysqlConnPool pool,FboDataInf fbo,FboData data,int BattGoupId,int record_num,int max_test_record_count) {
|
boolean success = false;
|
if(fbo.getTestTimeLong() > 0) {
|
createTb_FboTestData_Table(pool, BattGoupId);
|
ArrayList<String> sql_strs = new ArrayList<>();
|
String sql_str = "INSERT INTO "+Sql_Mysql.Tb_FboTestData_Table+BattGoupId+"(BattGroupId,test_record_count,test_starttime,test_timelong,record_time,record_num,online_vol,sum_vol,test_curr,test_cap,mon_num,mon_vol) " +
|
" VALUES";
|
Date record_time = new Date(fbo.TestStartTime.getFBODateTime().getTime()+data.m_TestTime.getTestTimeLong()*1000);
|
if(data.BattSum > 0) {
|
//Óе¥Ìåʱ
|
for(int k=0;k<data.BattSum && k<data.SingleVol.length;k++) {
|
sql_strs.add(sql_str + "("
|
+ "" + BattGoupId
|
+ "," + max_test_record_count
|
+ ",'" + Com.getDateTimeFormat(fbo.TestStartTime.getFBODateTime(), Com.DTF_YMDhms)
|
+ "'," + data.m_TestTime.getTestTimeLong()
|
+ ",'" + Com.getDateTimeFormat(record_time, Com.DTF_YMDhms)
|
+ "'," + record_num
|
+ "," + data.OnlineVol
|
+ "," + data.SumVoltage
|
+ "," + data.SumCurrent
|
+ "," + data.AllCap
|
+ "," + (k+1)
|
+ "," + data.SingleVol[k]
|
+ ")");
|
}
|
}else {
|
//ûÓе¥ÌåÊý¾Ýʱ
|
sql_strs.add(sql_str + "("
|
+ "" + BattGoupId
|
+ "," + max_test_record_count
|
+ ",'" + Com.getDateTimeFormat(fbo.TestStartTime.getFBODateTime(), Com.DTF_YMDhms)
|
+ "'," + data.m_TestTime.getTestTimeLong()
|
+ ",'" + Com.getDateTimeFormat(record_time, Com.DTF_YMDhms)
|
+ "'," +record_num
|
+ "," + data.OnlineVol
|
+ "," + data.SumVoltage
|
+ "," + data.SumCurrent
|
+ "," + data.AllCap
|
+ "," + 0
|
+ "," + 0
|
+ ")");
|
}
|
Sql_Mysql sql = new Sql_Mysql(pool);
|
success = sql.makeManualCommit(sql_strs);
|
sql.close_con();
|
}
|
return success;
|
}
|
|
/**
|
* ´´½¨FBOÀúÊ·Êý¾Ý_ID±í
|
* @param pool
|
* @param BattGroupId
|
*/
|
public static void createTb_FboTestData_Table(MysqlConnPool pool,int BattGroupId) {
|
String sql_str = " CREATE TABLE IF NOT EXISTS "+Sql_Mysql.Tb_FboTestData_Table+BattGroupId+" (" +
|
" `num` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Ö÷¼ü'," +
|
" `BattGroupId` int(11) NOT NULL DEFAULT '0' COMMENT 'µç³Ø×éID'," +
|
" `test_record_count` int(11) NOT NULL DEFAULT '1' COMMENT '²âÊÔ±ÊÊý'," +
|
" `test_starttime` datetime NOT NULL DEFAULT '2000-01-01 00:00:00' COMMENT '²âÊÔ¿ªÊ¼Ê±¼ä'," +
|
" `test_timelong` int(11) NOT NULL," +
|
" `record_time` datetime NOT NULL," +
|
" `record_num` int(11) NOT NULL DEFAULT '1' COMMENT '¼Ç¼±ÊÊý'," +
|
" `online_vol` float NOT NULL DEFAULT '0' COMMENT 'ÔÚÏßµçѹ'," +
|
" `sum_vol` float NOT NULL DEFAULT '0' COMMENT '×ܵçѹ'," +
|
" `test_curr` float NOT NULL DEFAULT '0' COMMENT '²âÊÔµçÁ÷'," +
|
" `test_cap` float NOT NULL DEFAULT '0' COMMENT '²âÊÔÈÝÁ¿'," +
|
" `mon_num` int(11) NOT NULL DEFAULT '0' COMMENT 'µ¥Ìå±àºÅ'," +
|
" `mon_vol` float NOT NULL DEFAULT '0' COMMENT 'µ¥Ìåµçѹ'," +
|
" PRIMARY KEY (`num`)" +
|
") ENGINE=InnoDB DEFAULT CHARSET=utf8; ";
|
Sql_Mysql sql = new Sql_Mysql(pool);
|
try {
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
/**
|
* ²éѯµ±Ç°µç³Ø×鵱ǰ×î´óµÄ¼Ç¼±ÊÊý+1
|
* @param pool
|
* @param BattGroupId
|
* @return
|
*/
|
public static int quereyBattMaxTestRecordCount(MysqlConnPool pool,int BattGroupId) {
|
int test_record_count = 0;
|
String sql_str = " SELECT MAX(test_record_count) as test_record_count " +
|
" FROM " + Sql_Mysql.Tb_FboTestData_Inf_Table +
|
" WHERE BattGroupId = " + BattGroupId;
|
Sql_Mysql sql = new Sql_Mysql(pool);
|
ResultSet res = null;
|
try {
|
res = sql.sqlMysqlQuery(sql_str);
|
if(res.next()) {
|
test_record_count = res.getInt("test_record_count");
|
}
|
} catch (Exception e) {
|
e.printStackTrace();
|
} finally {
|
if(null != res) {
|
try {
|
res.close();
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
}
|
sql.close_con();
|
}
|
return (test_record_count+1);
|
}
|
|
//Îļþµ¼Èë½ø¶ÈÐÞ¸Ä
|
public static void updateFBOGressByOneSql(MysqlConnPool pool,int file_sum,int file_block,FGCD_TestFBOFile fbofile) {
|
String sql_str = " update " + Sql_Mysql.Tb_Fgcd_Filedownload_Table +
|
" set now_data_block1="+file_block+",total_data_block1="+file_sum+" WHERE BattGroupId = " + fbofile.battgroupid+" and dev_id="+fbofile.dev_id;
|
Sql_Mysql sql = new Sql_Mysql(pool);
|
try {
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
public static void updateFBOFIleExportFlag(MysqlConnPool pool, FGCD_TestFBOFile fbofile) {
|
String sql_str = " Update " + Sql_Mysql.Tb_Fgcd_Filedownload_Table +
|
" Set ";
|
for(int i=0;i<fbofile.files.length;i++) {
|
if(i>0) {
|
sql_str += ",";
|
}
|
sql_str += " download_flag"+(i+1)+" = "+fbofile.files[i].download_flag+""
|
+ " ,download_state"+(i+1)+" =" +fbofile.files[i].download_state;
|
}
|
sql_str += " WHERE dev_id = " + fbofile.dev_id;
|
Sql_Mysql sql = new Sql_Mysql(pool);
|
try {
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
/**
|
* ¸üе±Ç°ÏÂÔØ״̬,ÒÔ¼°Òì³£½áÊøÔÒò
|
* @param pool
|
* @param file
|
*/
|
public static void updateFgcd_Filedownload_TableCmdByDevId(MysqlConnPool pool,FGCD_TestFBOFile file) {
|
String sql_str = " UPDATE " + Sql_Mysql.Tb_Fgcd_Filedownload_Table +
|
" SET op_cmd = " + file.op_cmd +
|
" ,stop_reason = " + file.getStop_reason() +
|
" WHERE dev_id = " + file.dev_id;
|
Sql_Mysql sql = new Sql_Mysql(pool);
|
try {
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
/**
|
* ¸üе±Ç°É豸µÄÏÂÔØ״̬
|
* @param pool
|
* @param fbofile
|
*/
|
public static void updateFgcd_FiledownloadState_TableByDevId(MysqlConnPool pool, FGCD_TestFBOFile fbofile) {
|
String sql_str_base = " update " + Sql_Mysql.Tb_Fgcd_Filedownload_Table + " Set stop_reason = " + fbofile.getStop_reason();
|
for(int i=0;i<fbofile.files.length;i++) {
|
FBOFile file = fbofile.files[i];
|
sql_str_base += " ,download_flag"+(i+1) + " = "+file.download_flag+","
|
+ " download_state"+(i+1) + " = "+file.download_state;
|
}
|
sql_str_base += " WHERE dev_id = " + fbofile.dev_id;
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
sql.sqlMysqlExecute(sql_str_base);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
}
|
