package com.battdata_rt;
|
|
import java.math.BigDecimal;
|
import java.sql.Connection;
|
import java.sql.ResultSet;
|
import java.sql.SQLException;
|
import java.text.DecimalFormat;
|
import java.util.ArrayList;
|
import java.util.Date;
|
import java.util.List;
|
|
import com.base.Com;
|
import com.sql.MysqlConnPool;
|
import com.sql.Sql_Mysql;
|
|
public class StationCruisingPower_Thread_SQL {
|
private static String BATT_ENDURANCENAME = "tb_batt_endurance";
|
|
/**
|
* ´´½¨»ú·¿Ðøº½±í
|
* @param pool
|
*/
|
public static void createTable_batt_endurance(MysqlConnPool pool){
|
String sql_str = "CREATE TABLE IF NOT EXISTS `"+BATT_ENDURANCENAME+"` ("
|
+ " `num` int(11) NOT NULL AUTO_INCREMENT,"
|
+ " `deviceid` int(11) NOT NULL DEFAULT '0' COMMENT 'É豸id',"
|
+ " `stationid` varchar(64) NOT NULL DEFAULT '0' COMMENT '»ú·¿id',"
|
+ " `real_cap` float NOT NULL DEFAULT '0' COMMENT 'ʵ¼ÊÈÝÁ¿',"
|
+ " `moncapstd` float NOT NULL DEFAULT '0' COMMENT '±ê³ÆÈÝÁ¿',"
|
+ " `real_curr` float NOT NULL DEFAULT '0' COMMENT 'ʵ¼ÊµçÁ÷',"
|
+ " `endurance_theory_timelong` float NOT NULL DEFAULT '0' COMMENT 'ÀíÂÛÐøº½Ê±³¤(·ÖÖÓ)'," //ÀíÂÛÐøº½Ê±³¤
|
+ " `endurance_actual_timelong` float NOT NULL DEFAULT '0' COMMENT 'ƽ¾ùʵ¼ÊÐøº½Ê±³¤(·ÖÖÓ)'," //ʵ¼ÊÐøº½Ê±³¤
|
+ " `endurance_actual_timelong_min` float NOT NULL DEFAULT '0' COMMENT '×îСʵ¼ÊÐøº½Ê±³¤(·ÖÖÓ)'," //×îСʵ¼ÊÐøº½Ê±³¤
|
+ " `endurance_actual_timelong_max` float NOT NULL DEFAULT '0' COMMENT '×î´óʵ¼ÊÐøº½Ê±³¤(·ÖÖÓ)'," //×î´óʵ¼ÊÐøº½Ê±³¤
|
+ " `is_out_stand` tinyint NOT NULL DEFAULT '0' COMMENT 'ÊÇ·ñµôÕ¾'," //ÊÇ·ñµôÕ¾
|
+ " `note` varchar(64) NOT NULL DEFAULT '' COMMENT '±¸×¢',"
|
+ " PRIMARY KEY (`num`),"
|
+ " UNIQUE KEY `unique_index_deviceid` (`deviceid`),"
|
+ " KEY `index_stationid` (`stationid`)"
|
+ ") ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;";
|
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
sql.sqlMysqlUseDB(Sql_Mysql.WEB_Site);
|
sql.sqlMysqlExecute(sql_str);
|
|
/************************** Ìí¼Ó×î´óÐøº½Ê±³¤ÁÐ **************************************/
|
String sql_str1 = " SELECT * FROM information_schema.columns "
|
+ " WHERE table_schema= 'web_site' "
|
+ " AND table_name='tb_batt_endurance' "
|
+ " AND column_name='endurance_actual_timelong_max'";
|
//System.out.println(sql_str3);
|
ResultSet rs = sql.sqlMysqlQuery(sql_str1);
|
if(false == rs.next()) {
|
sql.sqlMysqlExecute(" ALTER TABLE " + BATT_ENDURANCENAME
|
+ " ADD COLUMN `endurance_actual_timelong_max` int(11) NOT NULL DEFAULT '0' COMMENT '×î´óʵ¼ÊÐøº½Ê±³¤(·ÖÖÓ)' AFTER `endurance_actual_timelong`;");
|
}
|
|
/************************** Ìí¼Ó×îСÐøº½Ê±³¤ÁÐ **************************************/
|
String sql_str2 = " SELECT * FROM information_schema.columns "
|
+ " WHERE table_schema= 'web_site' "
|
+ " AND table_name='tb_batt_endurance' "
|
+ " AND column_name='endurance_actual_timelong_min'";
|
//System.out.println(sql_str3);
|
rs = sql.sqlMysqlQuery(sql_str2);
|
if(false == rs.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + BATT_ENDURANCENAME
|
+ " ADD COLUMN `endurance_actual_timelong_min` int(11) NOT NULL DEFAULT '0' COMMENT '×îСʵ¼ÊÐøº½Ê±³¤(·ÖÖÓ)' AFTER `endurance_actual_timelong`;");
|
}
|
|
/************************** Ìí¼Ó µç³Ø×é1ʵ¼ÊÈÝÁ¿ **************************************/
|
String sql_str01 = " SELECT * FROM information_schema.columns "
|
+ " WHERE table_schema= 'web_site' "
|
+ " AND table_name='tb_batt_endurance' "
|
+ " AND column_name='real_cap_group1'";
|
//System.out.println(sql_str3);
|
rs = sql.sqlMysqlQuery(sql_str01);
|
if(false == rs.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + BATT_ENDURANCENAME
|
+ " ADD COLUMN `real_cap_group1` float NOT NULL DEFAULT '0' COMMENT 'µç³Ø×é1ʵ¼ÊÈÝÁ¿';");
|
}
|
/************************** Ìí¼Ó µç³Ø×é2ʵ¼ÊÈÝÁ¿ **************************************/
|
String sql_str02 = " SELECT * FROM information_schema.columns "
|
+ " WHERE table_schema= 'web_site' "
|
+ " AND table_name='tb_batt_endurance' "
|
+ " AND column_name='real_cap_group2'";
|
//System.out.println(sql_str3);
|
rs = sql.sqlMysqlQuery(sql_str02);
|
if(false == rs.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + BATT_ENDURANCENAME
|
+ " ADD COLUMN `real_cap_group2` float NOT NULL DEFAULT '0' COMMENT 'µç³Ø×é2ʵ¼ÊÈÝÁ¿';");
|
}
|
/************************** Ìí¼Ó µç³Ø×é3ʵ¼ÊÈÝÁ¿ **************************************/
|
String sql_str03 = " SELECT * FROM information_schema.columns "
|
+ " WHERE table_schema= 'web_site' "
|
+ " AND table_name='tb_batt_endurance' "
|
+ " AND column_name='real_cap_group3'";
|
//System.out.println(sql_str3);
|
rs = sql.sqlMysqlQuery(sql_str03);
|
if(false == rs.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + BATT_ENDURANCENAME
|
+ " ADD COLUMN `real_cap_group3` float NOT NULL DEFAULT '0' COMMENT 'µç³Ø×é3ʵ¼ÊÈÝÁ¿';");
|
}
|
/************************** Ìí¼Ó µç³Ø×é4ʵ¼ÊÈÝÁ¿ **************************************/
|
String sql_str04 = " SELECT * FROM information_schema.columns "
|
+ " WHERE table_schema= 'web_site' "
|
+ " AND table_name='tb_batt_endurance' "
|
+ " AND column_name='real_cap_group4'";
|
//System.out.println(sql_str3);
|
rs = sql.sqlMysqlQuery(sql_str04);
|
if(false == rs.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + BATT_ENDURANCENAME
|
+ " ADD COLUMN `real_cap_group4` float NOT NULL DEFAULT '0' COMMENT 'µç³Ø×é4ʵ¼ÊÈÝÁ¿';");
|
}
|
/************************** Ìí¼Ó µç³Ø×éµÄ±ê³ÆÈÝÁ¿ **************************************/
|
String sql_str05 = " SELECT * FROM information_schema.columns "
|
+ " WHERE table_schema= 'web_site' "
|
+ " AND table_name='tb_batt_endurance' "
|
+ " AND column_name='batts_moncapstd'";
|
//System.out.println(sql_str3);
|
rs = sql.sqlMysqlQuery(sql_str05);
|
if(false == rs.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + BATT_ENDURANCENAME
|
+ " ADD COLUMN `batts_moncapstd` varchar(64) NOT NULL DEFAULT '' COMMENT 'µç³Ø×éµÄ±ê³ÆÈÝÁ¿';");
|
}
|
/************************** Ìí¼Ó µç³Ø×éµÄºËÈÝʱ¼ä **************************************/
|
String sql_str06 = " SELECT * FROM information_schema.columns "
|
+ " WHERE table_schema= 'web_site' "
|
+ " AND table_name='tb_batt_endurance' "
|
+ " AND column_name='batts_teststarttime'";
|
rs = sql.sqlMysqlQuery(sql_str06);
|
if(false == rs.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + BATT_ENDURANCENAME
|
+ " ADD COLUMN `batts_teststarttime` varchar(250) NOT NULL DEFAULT '' COMMENT 'µç³Ø×éµÄºËÈÝʱ¼ä';");
|
}
|
/************************** Ìí¼Ó µç³Ø×éµÄ×éÊý **************************************/
|
String sql_str07 = " SELECT * FROM information_schema.columns "
|
+ " WHERE table_schema= 'web_site' "
|
+ " AND table_name='tb_batt_endurance' "
|
+ " AND column_name='groupcount'";
|
rs = sql.sqlMysqlQuery(sql_str07);
|
if(false == rs.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + BATT_ENDURANCENAME
|
+ " ADD COLUMN `groupcount` int NOT NULL DEFAULT '0' COMMENT 'µç³Ø×éµÄºËÈÝʱ¼ä';");
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
/**
|
* ²éѯËùÓеĻú·¿µç³Ø×é¼Ç¼
|
* @param pool
|
* @return
|
*/
|
public static List<Station_inf> searchAllStationInfo(MysqlConnPool pool){
|
List<Station_inf> stations = new ArrayList<Station_inf>();
|
String sql_str = " SELECT DISTINCT StationId,StationIP,StationName,StationName1," +
|
" StationName2,StationName3,StationName4,StationIp, " +
|
" FBSDeviceId,FBSDeviceIp,FBSDeviceName,Load_curr " +
|
" FROM " + Sql_Mysql.BattInf_Table +"";
|
//" WHERE station_install=1";
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
ResultSet rs = null;
|
try {
|
rs = sql.sqlMysqlQuery(sql_str);
|
Station_inf station = null;
|
while (rs.next()) {
|
if((rs.getInt("FBSDeviceId")/100000 == 9100) || (rs.getInt("FBSDeviceId")/10000 == 9100)) {
|
|
|
station = new Station_inf();
|
station.stationId = rs.getString("StationId"); //»ú·¿id
|
station.StationName = rs.getString("StationName"); //»ú·¿Ãû³Æ
|
station.StationName1 = rs.getString("StationName1"); //Ê¡
|
station.StationName2 = rs.getString("StationName2"); //ÊÐ
|
station.StationName3 = rs.getString("StationName3"); //»ú·¿Ãû³Æ
|
station.StationName4 = rs.getString("StationName4"); //É豸Ãû³Æ
|
station.stationIp = rs.getString("StationIP"); //»ú·¿ip
|
station.FBSDeviceId = rs.getInt("FBSDeviceId"); //É豸id
|
station.FBSDeviceIp = rs.getString("FBSDeviceIp"); //É豸ip
|
station.FBSDeviceName = rs.getString("FBSDeviceName"); //É豸Ãû³Æ
|
station.station_RealCurr = rs.getFloat("Load_curr"); //É豸ʵ¼ÊµçÁ÷ //¸ºÔصçÁ÷
|
//station.station_StdCap = rs.getFloat("MonCapStd"); //±ê³ÆÈÝÁ¿
|
|
String sql_str0 = " SELECT DISTINCT BattGroupId,FBSDeviceId,StationId,StationName,BattGroupName,GroupIndexInFBSDevice, " +
|
" BattModel,FloatVolLevel,OfflineVolLevel,BattFloatCurrent,MonCapStd,MonVolStd,MonResStd,MonCount, " +
|
" MonSerStd,MonTmpStd,MonVolLowToAvg,BattProducer,BattGuarantDayCount,SignalId,Load_curr,SignalName " +
|
" FROM db_battinf.tb_battinf " +
|
" WHERE FBSDeviceId = " + station.FBSDeviceId +
|
" ORDER BY GroupIndexInFBSDevice ASC ";
|
ResultSet rs0 = sql.sqlMysqlQuery(sql_str0); //²éѯµ±Ç°»ú·¿Öеĵç³Ø×é
|
int index = 0;
|
while(rs0.next()) {
|
Batt_inf batt = new Batt_inf();
|
batt.dev_id = rs0.getInt("FBSDeviceId"); //É豸id
|
batt.stationId = rs0.getString("StationId"); //»ú·¿id
|
batt.stationName = rs0.getString("StationName"); //»ú·¿Ãû³Æ
|
batt.battGroupId = rs0.getInt("BattGroupId"); //µç³Ø×éid
|
batt.battGroupName = rs0.getString("BattGroupName"); //µç³Ø×éÃû³Æ
|
batt.groupIndexInFBSDevice = rs0.getInt("GroupIndexInFBSDevice"); //»ú·¿ÄÚµç³Ø×éµÄË÷Òý
|
batt.battModel = rs0.getString("BattModel"); //µç³ØÐͺÅ
|
batt.floatVolLevel = rs0.getFloat("FloatVolLevel"); //¸¡³äµçѹ·§Öµ
|
batt.offLineVolLevel = rs0.getFloat("OfflineVolLevel"); //ÀëÏßµçѹ·§Öµ
|
batt.battFloatCurrent = rs0.getFloat("BattFloatCurrent"); //¸¡³äµçÁ÷·§Öµ
|
batt.monCapStd = rs0.getFloat("MonCapStd"); //±ê³ÆÈÝÁ¿
|
batt.monVolStd = rs0.getFloat("MonVolStd"); //±ê³Æµ¥Ìåµçѹ
|
batt.monResStd = rs0.getFloat("MonResStd"); //±ê³ÆÄÚ×è
|
batt.monSerStd = rs0.getFloat("MonSerStd"); //±ê³Æµçµ¼
|
batt.monTmpStd = rs0.getFloat("MonTmpStd"); //±ê³ÆζÈ
|
batt.monVolLowToAvg = rs0.getFloat("MonVolLowToAvg"); //µ¥Ìåµçѹ´ÎµÍÆ«ÒÆÁ¿
|
batt.battProducer = rs0.getString("BattProducer"); //µç³ØÆ·ÅÆ
|
batt.battGuarantDayCount = rs0.getInt("BattGuarantDayCount"); //µç³Ø±£ÐÞÌìÊý
|
batt.signalId = rs0.getString("SignalId");
|
batt.load_curr = rs0.getFloat("Load_curr"); //¸ºÔصçÁ÷
|
batt.signalName = rs0.getString("SignalName"); //
|
batt.moncount = rs0.getInt("MonCount"); //µ¥Ìå¸öÊý
|
station.battMonCapStds = station.battMonCapStds + ((index>0?"/":"") + rs0.getInt("MonCapStd")); //ÿ¸öµç³Ø×éµÄ±ê³ÆÈÝÁ¿
|
station.batts.add(batt);
|
index++;
|
}
|
station.setGroupcount(station.getBatts().size());
|
stations.add(station);
|
}
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
if(rs != null) {
|
try {
|
rs.close();
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
}
|
sql.close_con();
|
}
|
return stations;
|
}
|
|
/**
|
* ²éѯ»ú·¿µÄËùÓеÄÍ£µç¼Ç¼
|
* @param pool
|
* @return
|
*/
|
public static void searchStationPowerOffRecord(MysqlConnPool pool,Station_inf station){
|
String sql_str = " SELECT dev_id,station_id,record_datetime,station_event_type,station_event_trig " +
|
" FROM db_alarm.tb_bts_station_event " +
|
" WHERE station_event_type = "+StationState.STATION_POFF+" AND station_id = " + station.stationId + " AND dev_id = " + station.FBSDeviceId +
|
" ORDER BY record_datetime ASC ";
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
ResultSet rs = null;
|
try {
|
rs = sql.sqlMysqlQuery(sql_str);
|
StationState state = null;
|
int flag = 0;
|
while(rs.next()) {
|
int station_trig = rs.getInt("station_event_trig"); //µ±Ç°Ê¼þµÄÀàÐÍ
|
if(station_trig == StationState.STATIONSTATE_START) {
|
//¿ªÊ¼
|
state = new StationState();
|
state.setDev_id(rs.getInt("dev_id"));
|
state.setStation_id(rs.getInt("station_id"));
|
state.setStationState(rs.getInt("station_event_type"));
|
state.setStartTime(rs.getTimestamp("record_datetime"));
|
flag = 1;
|
}else if(station_trig == StationState.STATIONSTATE_STOP) {
|
if(flag == 1) {
|
//״̬½áÊø
|
state.setStopTime(rs.getTimestamp("record_datetime"));
|
station.getPoffStatte().add(state);
|
}
|
flag = 0;
|
}else {
|
flag = 0;
|
}
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
if(rs != null) {
|
try {
|
rs.close();
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
}
|
sql.close_con();
|
}
|
}
|
|
/**
|
* ²éѯ»ú·¿ÔÚÖ¸¶¨Ê±¼ä¶Î¼äµÄÐøº½Êý¾Ý
|
* @param pool
|
* @return
|
*/
|
public static List<StationState> searchStationXuhangRecord(MysqlConnPool pool,StationState offstate){
|
List<StationState> stationstates = new ArrayList<StationState>();
|
String sql_str = " SELECT dev_id,station_id,record_datetime,station_event_trig,station_event_type " +
|
" FROM db_alarm.tb_bts_station_event " +
|
" WHERE dev_id = "+ offstate.getDev_id() +" AND station_id = " + offstate.getStation_id() +
|
" AND record_datetime >= '" + Com.getDateTimeFormat(offstate.getStartTime(), Com.DTF_YMDhms) +"' " +
|
" AND record_datetime <= '" + Com.getDateTimeFormat(offstate.getStopTime(), Com.DTF_YMDhms) +"' " +
|
" AND station_event_type = " + StationState.STATION_XUHANG +
|
" ORDER BY record_datetime ASC ";
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
ResultSet rs = null;
|
try {
|
rs = sql.sqlMysqlQuery(sql_str);
|
StationState state = null;
|
int flag = 0;
|
while(rs.next()) {
|
int station_trig = rs.getInt("station_event_trig"); //µ±Ç°Ê¼þµÄÀàÐÍ
|
if(station_trig == StationState.STATIONSTATE_START) {
|
//¿ªÊ¼
|
state = new StationState();
|
state.setStationState(rs.getInt("station_event_type"));
|
state.setStartTime(rs.getTimestamp("record_datetime"));
|
flag = 1;
|
}else if(station_trig == StationState.STATIONSTATE_STOP) {
|
if(flag == 1) {
|
//״̬½áÊø
|
state.setStopTime(rs.getTimestamp("record_datetime"));
|
if(state.getTimeLong() < offstate.timeLong) {
|
//Ðøº½Ê±³¤Ð¡ÓÚÍ£µçʱ³¤Ê±²Å²ÎÓë¼ÆËã
|
stationstates.add(state);
|
}
|
}
|
flag = 0;
|
}else {
|
flag = 0;
|
}
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
if(rs != null) {
|
try {
|
rs.close();
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
}
|
sql.close_con();
|
}
|
return stationstates;
|
}
|
|
|
/**
|
* ²éѯµ±Ç°µç³Ø×î½üµÄÒ»´Î·Åµç¼Ç¼
|
* @param pool
|
* @param batt
|
* @return
|
*/
|
public static BattTestData searchBattHistoryData(MysqlConnPool pool, Batt_inf batt) {
|
BattTestData testData = null;
|
//float cap_low = searchCapLow(pool); //²éѯÈÝÁ¿·§Öµ
|
String sql_str =
|
" SELECT * FROM db_batt_testdata.tb_batttestdata_inf "
|
+ " WHERE BattGroupId = " + batt.battGroupId
|
+ " AND data_available=1 AND data_new = 1 "
|
+ " AND test_type = 3 AND test_starttype = 3 " //ÅжÏÊǽÚÄܷŵç
|
+ " AND ((abs(test_cap) > " + batt.monCapStd*0.1 +") or (test_stoptype = 10))" //ÅжÏ×é¶ËÏÂÏÞµ½»òÕß²âÊÔÈÝÁ¿´óÓÚ±ê³ÆÈÝÁ¿10% ÅжϲâÊÔÊý¾ÝÈÝÁ¿ÊÇ·ñÓÐЧ
|
+ " ORDER BY test_record_count DESC LIMIT 1 ";
|
ResultSet rs = null;
|
//System.out.println(sql_str);
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
sql.sqlMysqlUseDB(Sql_Mysql.DB_BATT_TESTDATA);
|
rs = sql.sqlMysqlQuery(sql_str);
|
if(rs.next()){
|
testData = new BattTestData();
|
testData.setBattGroupId(rs.getInt("battGroupId"));
|
testData.setData_available(rs.getInt("data_available"));
|
testData.setData_new(rs.getInt("data_new"));
|
testData.setGroup_vol(rs.getFloat("group_vol"));
|
testData.setMax_monnum(rs.getInt("max_monnum"));
|
testData.setMax_monvol(rs.getFloat("max_monvol"));
|
testData.setMin_monnum(rs.getInt("min_monnum"));
|
testData.setMin_monvol(rs.getFloat("min_monvol"));
|
testData.setMon_num(rs.getInt("mon_num"));
|
testData.setMon_vol(rs.getFloat("mon_vol"));
|
testData.setRecord_num(rs.getInt("record_num"));
|
testData.setRecord_time(rs.getTimestamp("record_time"));
|
testData.setRecord_time_interval(rs.getInt("record_time_interval"));
|
testData.setTest_cap(rs.getFloat("test_cap"));
|
testData.setTest_curr(rs.getFloat("test_curr"));
|
testData.setTest_record_count(rs.getInt("test_record_count"));
|
testData.setTest_starttime(rs.getTimestamp("test_starttime"));
|
testData.setTest_starttype(rs.getInt("test_starttype"));
|
testData.setTest_stoptype(rs.getInt("test_stoptype"));
|
testData.setTest_timelong(rs.getInt("test_timelong"));
|
testData.setTest_type(rs.getInt("test_type"));
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
return testData;
|
}
|
|
/**
|
* ²åÈë»òÕ߸üлú·¿µÄÐøº½Ê±³¤
|
* @param pool
|
* @param station
|
*/
|
public static void insertOrUpdateStationCruisingPower(MysqlConnPool pool, Station_inf station) {
|
String sql_str = "";
|
if(!cheakSatationRecordIsExist(pool, station)) {
|
//¸üÐÂÊý¾Ý
|
sql_str = " INSERT INTO " + Sql_Mysql.BATTENDURANCETABLE
|
+ " ( "
|
+ "deviceid,"
|
+ "stationid,"
|
+ "real_cap,"
|
+ "moncapstd,"
|
+ "real_curr,"
|
+ "endurance_theory_timelong,"
|
+ "endurance_actual_timelong,"
|
+ "endurance_actual_timelong_min,"
|
+ "endurance_actual_timelong_max,"
|
+ "real_cap_group1,"
|
+ "real_cap_group2,"
|
+ "real_cap_group3,"
|
+ "real_cap_group4,"
|
+ "is_out_stand,"
|
+ "batts_moncapstd,"
|
+ "batts_teststarttime,"
|
+ "groupcount"
|
+ ") values("
|
+ station.FBSDeviceId + ","
|
+ station.stationId + ","
|
+ toFixed(station.station_RealCap,1) + ","
|
+ station.station_StdCap + ","
|
+ station.station_RealCurr + ","
|
+ toFixed(station.station_TimeLong,2) +","
|
+ toFixed(station.station_RealTimeLong, 2) + ","
|
+ toFixed(station.station_RealTimeLong_min, 2) + ","
|
+ toFixed(station.station_RealTimeLong_max, 2) +","
|
+ station.batts_realcaps[0]+ ","
|
+ station.batts_realcaps[1]+ ","
|
+ station.batts_realcaps[2]+ ","
|
+ station.batts_realcaps[3]+ ","
|
+ (station.station_RealTimeLong<=(3*60)) +","
|
+ "'"+station.battMonCapStds+"',"
|
+ "'"+station.batts_teststarttime+"',"
|
+ "'"+station.groupcount+"'"
|
+ "); ";
|
}else {
|
//²åÈëÊý¾Ý
|
sql_str = "UPDATE " + Sql_Mysql.BATTENDURANCETABLE
|
+ "SET real_cap = '" + toFixed(station.station_RealCap,1)
|
+ "',moncapstd = '" + station.station_StdCap
|
+ "',stationid = '" + station.stationId
|
+ "',real_curr='" + station.station_RealCurr
|
+ "',endurance_theory_timelong= '" + toFixed(station.station_TimeLong,2)
|
+ "',endurance_actual_timelong = '" + toFixed(station.station_RealTimeLong,2)
|
+ "',endurance_actual_timelong_min = '" + toFixed(station.station_RealTimeLong_min,2)
|
+ "' ,endurance_actual_timelong_max = '" + toFixed(station.station_RealTimeLong_max,2)
|
+ "' ,is_out_stand = " + (station.station_RealTimeLong<=(3*60))
|
+ " ,real_cap_group1 = " + (station.batts_realcaps[0])
|
+ " ,real_cap_group2 = " + (station.batts_realcaps[1])
|
+ " ,real_cap_group3 = " + (station.batts_realcaps[2])
|
+ " ,real_cap_group4 = " + (station.batts_realcaps[3])
|
+ " ,batts_moncapstd = '" + (station.battMonCapStds)
|
+ "' ,batts_teststarttime = '" + (station.batts_teststarttime)
|
+ "' ,groupcount = '" + (station.groupcount)
|
+ "' WHERE deviceid = " + station.FBSDeviceId;
|
}
|
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 station
|
* @return
|
*/
|
public static boolean cheakSatationRecordIsExist(MysqlConnPool pool,Station_inf station) {
|
boolean flag = false;
|
String sql_str = " SELECT * FROM " +Sql_Mysql.BATTENDURANCETABLE+ " WHERE deviceid = " + station.getFBSDeviceId();
|
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
ResultSet rs = null;
|
try {
|
rs = sql.sqlMysqlQuery(sql_str);
|
if(rs.next()) {
|
flag = true;
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
return flag;
|
}
|
|
/**
|
* ¼à²âµ±Ç°Ï̵߳ÄÖ´ÐÐ״̬
|
* @return
|
*/
|
public static Thread_Util searchThreadState(MysqlConnPool pool,Thread_Util thread) {
|
String sql_str = " SELECT thread_en "
|
+ " from web_site.tb_thread_util "
|
+ " WHERE thread_id = " + thread.thread_id;
|
ResultSet rs = null;
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
sql.sqlMysqlUseDB(Sql_Mysql.WEB_Site);
|
rs = sql.sqlMysqlQuery(sql_str);
|
while (rs.next()) {
|
thread.setThread_en(rs.getInt("thread_en"));
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
return thread;
|
}
|
|
/**
|
* Ð޸ĵ±Ç°Ïß³ÌÖеÄ״̬
|
* @return
|
*/
|
public static void UpdateThreadState(MysqlConnPool pool,Thread_Util thread) {
|
String sql_str = " UPDATE web_site.tb_thread_util "
|
+ " SET thread_en = " + thread.getThread_en()
|
+ " WHERE thread_id = " + thread.thread_id;
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
|
|
|
/**
|
* ²éѯÈÝÁ¿·§Öµ²ÎÊý(ĬÈÏ0.5)
|
* @param pool
|
* @param station
|
* @return
|
*/
|
public static float searchCapLow(MysqlConnPool pool){
|
float cap_low = 0.1f;
|
String sql_str = "select num,low_type,low_nametype,low_value,low_method from web_site.tb_batt_param_low where low_type=2 and low_nametype=1";
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
ResultSet rs = null;
|
try {
|
sql.sqlMysqlUseDB(Sql_Mysql.WEB_Site);
|
rs = sql.sqlMysqlQuery(sql_str);
|
if(rs.next()){
|
cap_low = rs.getFloat("low_value");
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally{
|
sql.close_con();
|
}
|
return cap_low;
|
}
|
|
//¸ù¾Ý±ê³ÆµçѹºÍ²âÊÔÈÝÁ¿²éѯ³öµ±Ç°²âÊÔÈÝÁ¿¶ÔÓ¦±ê×¼ÇúÏߵĵ¥Ìåµçѹ
|
public static double searchBattStandardVol(double monvolstd,double moncapstd,float test_cap,MysqlConnPool pool) {
|
double cap = 0;
|
if(monvolstd == 12) {
|
cap=150;
|
}else {
|
if(moncapstd <= 300) {
|
cap = 300;
|
}else {
|
cap=500;
|
}
|
}
|
//²éѯָ¶¨²âÊÔÈÝÁ¿Ö¸¶¨µÄµ¥Ìåµçѹ
|
String sql_str ="select mon_vol as standard_mon_vol from db_battinf.tb_battmon_testcap where monvolstd="+monvolstd+" and abs(test_cap)<="+toFixed((Math.abs(test_cap)),1)+" and moncapstd = "+cap+" ORDER BY test_cap ASC limit 1";
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
double standard_monvol=0f;
|
ResultSet rs = null;
|
try {
|
rs = sql.sqlMysqlQuery(sql_str);
|
while(rs.next()){
|
standard_monvol=rs.getDouble("standard_mon_vol");
|
}
|
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}finally{
|
try {
|
if(rs != null){
|
rs.close();
|
}
|
sql.close_con();
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
}
|
//System.err.println("test_cap:"+test_cap+"\t max_cap:"+max_testcap+"\t"+standard_monvol);
|
//System.err.println("****** monvolstd:"+monvolstd+"\t moncapstd:"+moncapstd+"\t test_cap:"+test_cap+"\t standard_monvol:"+standard_monvol);
|
return standard_monvol;
|
}
|
|
/**
|
* @param monvolstd ±ê³Æµçѹ
|
* @param moncapstd ±ê³ÆÈÝÁ¿
|
* @param pool Á¬½Ó³Ø
|
* @return
|
*/
|
public static float searchMaxTestCap(double monvolstd,double moncapstd,MysqlConnPool pool) {
|
double cap = 0;
|
if(monvolstd == 12) {
|
cap=150;
|
}else {
|
if(moncapstd <= 300) {
|
cap = 300;
|
}else {
|
cap=500;
|
}
|
}
|
float max_testcap = 0;
|
String sql_str = "select max(abs(test_cap)) as max_testcap from db_battinf.tb_battmon_testcap where monvolstd=" + monvolstd + " and moncapstd = "+cap;
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
ResultSet rs = sql.sqlMysqlQuery(sql_str);
|
try {
|
if(rs.next()) {
|
max_testcap = rs.getFloat("max_testcap");
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
try {
|
if(rs != null){
|
rs.close();
|
}
|
sql.close_con();
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
}
|
return max_testcap;
|
}
|
|
|
//²åÈëÉ豸Ðøº½Í³¼ÆÏ̵߳Äʱ¼ä´Á
|
public static void insertDeviceAlarm_Time(MysqlConnPool conn_pool,float version){
|
Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
|
String sql_str_query = " SELECT * FROM "+Sql_Mysql.Tb_Process_SurveyTable+" WHERE ProcessId = 11013 ";
|
try {
|
|
ResultSet res = sql.sqlMysqlQuery(sql_str_query);
|
String sql_str = "";
|
if(res.next()) {
|
sql_str = " UPDATE tb_process_survey SET ProcessTime = '"+Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms)+"'"
|
+ ",Process_starttime='" + Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms) + "',ProcessVersion= 'V" + version + "' "
|
+ " WHERE ProcessId = 11013 ";
|
//System.out.println("#######"+sql_str);
|
}else {
|
sql_str = " INSERT INTO " + Sql_Mysql.Tb_Process_SurveyTable +
|
"(ProcessId,ProcessName,ProcessTime,Process_starttime,ServerFlag,ServerName,ProcessVersion,note,OutTime) " +
|
" VALUES(11013,'BMS_BTS_ENDURANCE','" + Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms) + "','" + Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms) + "',1,'BMS_BTS_ENDURANCE','V" + version + "','BTSÐøº½Í³¼ÆÏß³Ì',300); ";
|
}
|
sql.sqlMysqlUseDB(Sql_Mysql.WEB_Site);
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
//¸üÐÂÉ豸Ðøº½Í³¼ÆÏ̵߳Äʱ¼ä´Á
|
public static void updateThreadRun_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 ProcessName = 'BMS_BTS_ENDURANCE' "
|
+ " AND ProcessId=11013 ";
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
|
/**
|
* ¸ñʽ»¯ double ÀàÐÍÊý¾Ý ËÄÉáÎåÈëÒÔ¼°±£ÁôÖ¸¶¨Î»Êý
|
* @param f ÔʼÊý¾Ý
|
* @param digit λÊý
|
* @return
|
*/
|
public static double toFixed(double f , int digit) {
|
BigDecimal bg = new BigDecimal(f);
|
return bg.setScale(digit, BigDecimal.ROUND_HALF_UP).doubleValue();
|
}
|
|
public static void main(String[] args) {
|
double f = 1.96f;
|
System.out.println(toFixed(f, 0));
|
double h = -1;
|
}
|
|
}
|
