package com.data.upload;
|
|
import java.io.File;
|
import java.sql.ResultSet;
|
import java.sql.SQLException;
|
import java.util.ArrayList;
|
import java.util.Calendar;
|
import java.util.Date;
|
|
import com.base.BattTestData;
|
import com.base.Com;
|
import com.data.fbo.FboData;
|
import com.data.fbo.FboDataInf;
|
import com.data.idc.IdcData;
|
import com.sql.MysqlConnPool;
|
import com.sql.Sql_Mysql;
|
|
public class FboDataUpload_SQL {
|
|
public static int checkAndInsertFboData(MysqlConnPool conn_pool, int bg_id, int uid, String file_name)
|
{
|
int data_reason_type = 0;
|
|
File f = new File(file_name);
|
if(false == f.exists()) {
|
data_reason_type = 16;
|
return data_reason_type;
|
}
|
|
FboDataInf data_inf = new FboDataInf();
|
ArrayList<FboData> al_fbo_data = new ArrayList<FboData>();
|
|
if(f.getName().toUpperCase().endsWith(".FBO")) {
|
FboData.checkFboFile(f, data_inf, al_fbo_data);
|
} else if (f.getName().toUpperCase().endsWith(".IDC")) {
|
IdcData.checkIdcFile(f, data_inf, al_fbo_data);
|
}
|
f.delete();
|
|
Calendar mcld = Calendar.getInstance();
|
mcld.set(Calendar.YEAR, 2000+data_inf.TestStartTime.year);
|
mcld.set(Calendar.MONTH, data_inf.TestStartTime.month);
|
mcld.set(Calendar.DATE, data_inf.TestStartTime.day);
|
mcld.set(Calendar.HOUR_OF_DAY, data_inf.TestStartTime.hour);
|
mcld.set(Calendar.MINUTE, data_inf.TestStartTime.minute);
|
mcld.set(Calendar.SECOND, data_inf.TestStartTime.second);
|
|
int batt_cap = 0;
|
int mon_count = 0;
|
float mon_vol = 0;
|
|
String sql_bginf = "SELECT DISTINCT MonCapStd,MonCount,MonVolStd FROM "
|
+ Sql_Mysql.BattInf_Table + " WHERE BattGroupId=" + bg_id;
|
String sql_str = "SELECT DISTINCT test_record_count FROM " + Sql_Mysql.BattTestDataInf_Table
|
+ " WHERE BattGroupId=" + bg_id
|
+ " AND test_starttime='" + Com.getDateTimeFormat(mcld.getTime(), Com.DTF_YMDhms) + "'";
|
|
Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
|
try {
|
ResultSet res = sql.sqlMysqlQuery(sql_bginf);
|
if(res.next()) {
|
batt_cap = (int) (res.getFloat("MonCapStd"));
|
mon_count = res.getInt("MonCount");
|
mon_vol = res.getFloat("MonVolStd");
|
}
|
|
res = sql.sqlMysqlQuery(sql_str);
|
if(res.next()) {
|
data_reason_type = 1;
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
|
if(0 == data_reason_type) {
|
if(batt_cap != data_inf.STDCap) {
|
data_reason_type = 2;
|
} else if(mon_count != data_inf.BattSum) {
|
data_reason_type = 3;
|
} else if(mon_vol != data_inf.MonomerVol) {
|
data_reason_type = 4;
|
}
|
}
|
|
if(0 == data_reason_type ) {
|
if(false == insertFboData(conn_pool, bg_id, uid, data_inf, al_fbo_data)) {
|
data_reason_type = 15;
|
}
|
}
|
|
return data_reason_type;
|
}
|
|
private static boolean insertFboData(MysqlConnPool conn_pool, int bg_id, int uid,
|
FboDataInf data_inf, ArrayList<FboData> al_fbo_data)
|
{
|
boolean upload_res = false;
|
|
Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
|
int testRecordCount = sql.getBattTestRecordCountNew(bg_id, Sql_Mysql.BattTestDataInf_Table);
|
Calendar ald = Calendar.getInstance();
|
ald.set(Calendar.YEAR, 2000+data_inf.TestStartTime.year);
|
ald.set(Calendar.MONTH, data_inf.TestStartTime.month);
|
ald.set(Calendar.DATE, data_inf.TestStartTime.day);
|
ald.set(Calendar.HOUR_OF_DAY, data_inf.TestStartTime.hour);
|
ald.set(Calendar.MINUTE, data_inf.TestStartTime.minute);
|
ald.set(Calendar.SECOND, data_inf.TestStartTime.second);
|
int data_type = BattTestData.BATTSTATE_DISCHARGE;
|
if(0xFD != data_inf.DataType) {
|
data_type = BattTestData.BATTSTATE_CHARGE;
|
}
|
String tb_name = "`db_batt_testdata`.`tb_BattTestData_" + bg_id + "` ";
|
String tb_stop_name = "`db_batt_testdata`.`tb_BattTestDataStop_" + bg_id + "` ";
|
String tmp_tb_name = "`db_batt_testdata`.`tb_BattTestData_FBO` ";
|
|
try {
|
String sql_str = "CREATE TABLE IF NOT EXISTS " + tb_name
|
+ "( `num` BIGINT NOT NULL AUTO_INCREMENT, "
|
+ "`BattGroupId` INT NOT NULL DEFAULT 0, "
|
+ "`test_record_count` INT NOT NULL DEFAULT 0, "
|
+ "`test_type` INT NOT NULL DEFAULT 0, "
|
+ "`data_new` BOOLEAN NOT NULL DEFAULT false, "
|
+ "`data_available` BOOLEAN NOT NULL DEFAULT false, "
|
+ "`record_num` INT NOT NULL DEFAULT 0, "
|
+ "`test_starttime` DATETIME NOT NULL DEFAULT '1982-01-01 00:00:00', "
|
+ "`record_time` DATETIME NOT NULL DEFAULT '1982-01-01 00:00:00', "
|
+ "`test_timelong` INT NOT NULL DEFAULT 0, "
|
+ "`group_vol` FLOAT NOT NULL DEFAULT 0, "
|
+ "`test_curr` FLOAT NOT NULL DEFAULT 0, "
|
+ "`test_cap` FLOAT NOT NULL DEFAULT 0, "
|
+ "`mon_num` INT NOT NULL DEFAULT 0, "
|
+ "`mon_vol` FLOAT NOT NULL DEFAULT 0, "
|
+ "INDEX index_test_record_count (`test_record_count`), "
|
+ "PRIMARY KEY (`num`));";
|
sql.sqlMysqlExecute(sql_str);
|
|
sql_str = "CREATE TABLE IF NOT EXISTS " + tb_stop_name
|
+ "( `num` BIGINT NOT NULL AUTO_INCREMENT, "
|
+ "`BattGroupId` INT NOT NULL DEFAULT 0, "
|
+ "`test_record_count` INT NOT NULL DEFAULT 0, "
|
+ "`test_type` INT NOT NULL DEFAULT 0, "
|
+ "`data_new` BOOLEAN NOT NULL DEFAULT false, "
|
+ "`data_available` BOOLEAN NOT NULL DEFAULT false, "
|
+ "`record_num` INT NOT NULL DEFAULT 0, "
|
+ "`test_starttime` DATETIME NOT NULL DEFAULT '1982-01-01 00:00:00', "
|
+ "`record_time` DATETIME NOT NULL DEFAULT '1982-01-01 00:00:00', "
|
+ "`test_timelong` INT NOT NULL DEFAULT 0, "
|
+ "`group_vol` FLOAT NOT NULL DEFAULT 0, "
|
+ "`test_curr` FLOAT NOT NULL DEFAULT 0, "
|
+ "`test_cap` FLOAT NOT NULL DEFAULT 0, "
|
+ "`mon_num` INT NOT NULL DEFAULT 0, "
|
+ "`mon_vol` FLOAT NOT NULL DEFAULT 0, "
|
+ "INDEX index_test_record_count (`test_record_count`), "
|
+ "PRIMARY KEY (`num`));";
|
sql.sqlMysqlExecute(sql_str);
|
|
sql_str = "DROP TABLE IF EXISTS " + tmp_tb_name;
|
sql.sqlMysqlExecute(sql_str);
|
|
sql_str = "CREATE TEMPORARY TABLE IF NOT EXISTS " + tmp_tb_name
|
+ "( `num` BIGINT NOT NULL AUTO_INCREMENT, "
|
+ "`BattGroupId` INT NOT NULL DEFAULT 0, "
|
+ "`test_record_count` INT NOT NULL DEFAULT 0, "
|
+ "`test_type` INT NOT NULL DEFAULT 0, "
|
+ "`data_new` BOOLEAN NOT NULL DEFAULT false, "
|
+ "`data_available` BOOLEAN NOT NULL DEFAULT false, "
|
+ "`record_num` INT NOT NULL DEFAULT 0, "
|
+ "`test_starttime` DATETIME NOT NULL DEFAULT '1982-01-01 00:00:00', "
|
+ "`record_time` DATETIME NOT NULL DEFAULT '1982-01-01 00:00:00', "
|
+ "`test_timelong` INT NOT NULL DEFAULT 0, "
|
+ "`group_vol` FLOAT NOT NULL DEFAULT 0, "
|
+ "`test_curr` FLOAT NOT NULL DEFAULT 0, "
|
+ "`test_cap` FLOAT NOT NULL DEFAULT 0, "
|
+ "`mon_num` INT NOT NULL DEFAULT 0, "
|
+ "`mon_vol` FLOAT NOT NULL DEFAULT 0, "
|
+ "INDEX index_test_record_count (`test_record_count`), "
|
+ "PRIMARY KEY (`num`));";
|
sql.sqlMysqlExecute(sql_str);
|
|
int test_time_long = 0;
|
FboData tmp_data = null;
|
for(int cnt=0; cnt<al_fbo_data.size(); cnt++)
|
{
|
tmp_data = al_fbo_data.get(cnt);
|
test_time_long = tmp_data.m_TestTime.hour;
|
test_time_long = test_time_long*60 + tmp_data.m_TestTime.minute;
|
test_time_long = test_time_long*60 + tmp_data.m_TestTime.second;
|
|
if(cnt > 0)
|
{
|
for(int sn=0; sn<data_inf.BattSum; sn++)
|
{
|
if(tmp_data.SingleVol[sn] < 0.1)
|
{
|
tmp_data.SingleVol[sn] = al_fbo_data.get(cnt-1).SingleVol[sn];
|
}
|
}
|
}
|
|
sql_str = "INSERT INTO " + tmp_tb_name + " "
|
+ "(BattGroupId, "
|
+ "test_record_count, "
|
+ "test_type, "
|
+ "data_new, "
|
+ "data_available, "
|
+ "record_num, "
|
+ "test_starttime, "
|
+ "record_time, "
|
+ "test_timelong, "
|
+ "group_vol, "
|
+ "test_curr, "
|
+ "test_cap, "
|
+ "mon_num, "
|
+ "mon_vol)"
|
+ " VALUES ";
|
for(int n=0; n<data_inf.BattSum; n++)
|
{
|
if(n > 0)
|
sql_str += ", ";
|
|
sql_str += "("+ bg_id + ", "
|
+ testRecordCount + ", "
|
+ data_type + ", "
|
+ true + ", "
|
+ true + ", "
|
+ (cnt + 1) + ", "
|
+ "'" + Com.getDateTimeFormat(ald.getTime(), Com.DTF_YMDhms) + "', "
|
+ "'" + Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms) + "', "
|
+ test_time_long + ", "
|
+ tmp_data.SumVoltage + ", "
|
+ tmp_data.SubCurrent[0] + ", "
|
+ tmp_data.SubCap[0] + ", "
|
+ (n+1) + ", "
|
+ tmp_data.SingleVol[n] + ")";
|
}
|
//System.out.println(sql_str);
|
sql.sqlMysqlExecute(sql_str);
|
}
|
|
//-----------------------------------------------------------------//
|
double test_ah = tmp_data.SubCap[0];
|
test_ah = (test_ah*3600) / test_time_long; //ƽ¾ù²âÊÔµçÁ÷
|
tmp_data.SubCurrent[0] = (float) test_ah;
|
//-----------------------------------------------------------------//
|
if(al_fbo_data.size() > 0)
|
{
|
ArrayList<String> al_sql_str = new ArrayList<String>();
|
sql_str = "INSERT INTO " + tb_stop_name
|
+ " (BattGroupId, "
|
+ "test_record_count, "
|
+ "test_type, "
|
+ "data_new, "
|
+ "data_available, "
|
+ "record_num, "
|
+ "test_starttime, "
|
+ "record_time, "
|
+ "test_timelong, "
|
+ "group_vol, "
|
+ "test_curr, "
|
+ "test_cap, "
|
+ "mon_num, "
|
+ "mon_vol)"
|
+ " VALUES ";
|
for(int n=0; n<data_inf.BattSum; n++)
|
{
|
if(n > 0)
|
sql_str += ", ";
|
|
sql_str = sql_str
|
+ "("+ bg_id + ", "
|
+ testRecordCount + ", "
|
+ data_type + ", "
|
+ true + ", "
|
+ true + ", "
|
+ al_fbo_data.size() + ", "
|
+ "'" + Com.getDateTimeFormat(ald.getTime(), Com.DTF_YMDhms) + "', "
|
+ "'" + Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms) + "', "
|
+ test_time_long + ", "
|
+ tmp_data.SumVoltage + ", "
|
+ tmp_data.SubCurrent[0] + ", "
|
+ tmp_data.SubCap[0] + ", "
|
+ (n+1) + ", "
|
+ tmp_data.SingleVol[n] + ")";
|
}
|
al_sql_str.add(sql_str);
|
|
String sql_str1 = "INSERT INTO " + Sql_Mysql.BattTestDataInf_Table
|
+ " (BattGroupId, "
|
+ "test_record_count, "
|
+ "test_type, "
|
+ "record_time_interval, "
|
+ "data_new, "
|
+ "data_available, "
|
+ "record_num, "
|
+ "test_starttime, "
|
+ "test_starttype, "
|
+ "record_time, "
|
+ "test_timelong, "
|
+ "test_stoptype,"
|
+ "group_vol, "
|
+ "test_curr, "
|
+ "test_cap, "
|
+ "max_monnum, "
|
+ "max_monvol, "
|
+ "min_monnum, "
|
+ "min_monvol, "
|
+ "upload_usr_id, "
|
+ "upload_data_ext, "
|
+ "upload_client_type ) "
|
+ " VALUES "
|
+ "("+ bg_id + ", "
|
+ testRecordCount + ", "
|
+ data_type + ", "
|
+ data_inf.SaveInterval * 60 + ", "
|
+ true + ", "
|
+ true + ", "
|
+ al_fbo_data.size() + ", "
|
+ "'" + Com.getDateTimeFormat(ald.getTime(), Com.DTF_YMDhms) + "', "
|
+ data_inf.Device + ", "
|
+ "'" + Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms) + "', "
|
+ test_time_long + ", "
|
+ data_inf.StopType + ", "
|
+ tmp_data.SumVoltage + ", "
|
+ tmp_data.SubCurrent[0] + ", "
|
+ tmp_data.SubCap[0] + ", "
|
+ (data_inf.SMaxIndex[0]+1) + ", "
|
+ data_inf.SMaxVol[0] + ", "
|
+ (data_inf.SMinIndex[0]+1) + ", "
|
+ data_inf.SMinVol[0] + ", "
|
+ uid + ", "
|
+ data_inf.SourceDataExt + ", "
|
+ Com.UploadData_ClientType_CS_SVR + ") ";
|
if(1 == testRecordCount) {
|
sql_str1 = "UPDATE " + Sql_Mysql.BattTestDataInf_Table + " SET "
|
+ "test_type=" + data_type + ", "
|
+ "record_time_interval=" + data_inf.SaveInterval * 60 + ", "
|
+ "data_new=" + true + ", "
|
+ "data_available=" + true + ", "
|
+ "record_num=" + al_fbo_data.size() + ", "
|
+ "test_starttime='" + Com.getDateTimeFormat(ald.getTime(), Com.DTF_YMDhms) + "', "
|
+ "test_starttype=" + data_inf.Device + ", "
|
+ "record_time='" + Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms) + "', "
|
+ "test_timelong=" + test_time_long + ", "
|
+ "test_stoptype=" + data_inf.StopType + ", "
|
+ "group_vol=" + tmp_data.SumVoltage + ", "
|
+ "test_curr=" + tmp_data.SubCurrent[0] + ", "
|
+ "test_cap=" + tmp_data.SubCap[0] + ", "
|
+ "max_monnum=" + (data_inf.SMaxIndex[0]+1) + ", "
|
+ "max_monvol=" + data_inf.SMaxVol[0] + ", "
|
+ "min_monnum=" + (data_inf.SMinIndex[0]+1) + ", "
|
+ "min_monvol=" + data_inf.SMinVol[0] + ", "
|
+ "upload_usr_id=" + uid + ", "
|
+ "upload_data_ext=" + data_inf.SourceDataExt + ", "
|
+ "upload_client_type=" + Com.UploadData_ClientType_CS_SVR + " "
|
+ " WHERE BattGroupId=" + bg_id
|
+ " AND test_record_count=" + testRecordCount;
|
}
|
al_sql_str.add(sql_str1);
|
|
String sql_str2 = "INSERT INTO " + tb_name + " "
|
+ " (BattGroupId, "
|
+ "test_record_count, "
|
+ "test_type, "
|
+ "data_new, "
|
+ "data_available, "
|
+ "record_num, "
|
+ "test_starttime, "
|
+ "record_time, "
|
+ "test_timelong, "
|
+ "group_vol, "
|
+ "test_curr, "
|
+ "test_cap, "
|
+ "mon_num, "
|
+ "mon_vol) "
|
+ " SELECT "
|
+ " BattGroupId, "
|
+ "test_record_count, "
|
+ "test_type, "
|
+ "data_new, "
|
+ "data_available, "
|
+ "record_num, "
|
+ "test_starttime, "
|
+ "record_time, "
|
+ "test_timelong, "
|
+ "group_vol, "
|
+ "test_curr, "
|
+ "test_cap, "
|
+ "mon_num, "
|
+ "mon_vol "
|
+ " FROM " + tmp_tb_name;
|
al_sql_str.add(sql_str2);
|
|
upload_res = sql.makeManualCommit(al_sql_str);
|
}
|
} catch (Exception e) {
|
e.printStackTrace();
|
upload_res = false;
|
} finally {
|
sql.close_con();
|
}
|
|
return upload_res;
|
}
|
}
|
