package com.dev.fbs9100;
|
|
import java.sql.ResultSet;
|
import java.sql.SQLException;
|
|
import javax.net.ssl.HandshakeCompletedEvent;
|
|
import com.sql.MysqlConnPool;
|
import com.sql.Sql_Mysql;
|
|
public class FBS9100_Task_Thread_SQL {
|
|
/**
|
* ´´½¨ tb_fbs9100_state ±í²¢ÇÒ½«É豸idºÍÉ豸hip²åÈëµ½±íÖÐ
|
* @param con_pool
|
* @param al_param
|
*/
|
public static void createFBS9100StateTableOnRam(MysqlConnPool con_pool)
|
{
|
String str1 = "DROP TABLE IF EXISTS " + Sql_Mysql.FBS9100State_Table;
|
String str2 = "CREATE TABLE IF NOT EXISTS " + Sql_Mysql.FBS9100State_Table
|
+ " ( `num` BIGINT NOT NULL AUTO_INCREMENT, "
|
+ "`dev_ip` varchar(20) NOT NULL DEFAULT '127.0.0.0', "
|
+ "`dev_version` varchar(20) NOT NULL DEFAULT '0', "
|
+ "`dev_id` BIGINT NOT NULL DEFAULT 0, "
|
+ "`op_cmd` INT NOT NULL DEFAULT 0, "
|
+ "`record_datetime` varchar(20) NOT NULL DEFAULT '2015-12-02 12:10:00', "
|
+ "`dev_datetime` varchar(20) NOT NULL DEFAULT '2015-12-02 12:10:00', "
|
+ "`dev_testtype` INT NOT NULL DEFAULT 0, "
|
+ "`dev_testgroupnum` INT NOT NULL DEFAULT 0, "
|
+ "`dev_workstate` INT NOT NULL DEFAULT 0, "
|
+ "`dev_alarmstate` INT NOT NULL DEFAULT 0, "
|
+ "`dev_temp` float NOT NULL DEFAULT 0, "
|
+ "`dev_res_test_state` INT NOT NULL DEFAULT 0, " //ÄÚ×è²âÊÔ״̬
|
+ "`dev_onlinevollow` BOOLEAN NOT NULL DEFAULT false, "
|
+ "`dev_eachgroup_battsum` INT NOT NULL DEFAULT 0, "
|
+ "`dev_captest_onlinevol` float NOT NULL DEFAULT 0, "
|
+ "`dev_captest_groupvol` float NOT NULL DEFAULT 0, "
|
+ "`dev_captest_curr` float NOT NULL DEFAULT 0, "
|
+ "`dev_captest_cap` float NOT NULL DEFAULT 0, "
|
+ "`dev_captest_timelong` INT NOT NULL DEFAULT 0, "
|
+ "`dev_restest_count` INT NOT NULL DEFAULT 0, "
|
+ "`dev_restest_moncount` INT NOT NULL DEFAULT 0, "
|
+ "`dev_restest_monindex` INT NOT NULL DEFAULT 0, "
|
+ "`dev_commcount` INT NOT NULL DEFAULT 0, "
|
+ "`dev_errcommcount` INT NOT NULL DEFAULT 0, "
|
+ "`dev_rxnullerrcount` INT NOT NULL DEFAULT 0, "
|
|
+ "`dev_last_captest_stop_type` INT NOT NULL DEFAULT 0, "
|
+ "`dev_concurr` float NOT NULL DEFAULT 0, "
|
+ "`dev_condvoldp` float NOT NULL DEFAULT 0, "
|
+ "`dev_conresist` float NOT NULL DEFAULT 0, "
|
+ "`dev_condcurr` float NOT NULL DEFAULT 0, "
|
+ "`dev_61850alarms` varchar(64) NOT NULL DEFAULT '', "
|
|
+ "`dev_batt_xuhang_tlong` INT NOT NULL DEFAULT 0, " //Ðøº½Ê±³¤
|
+ "`dev_station_poff_tlong` INT NOT NULL DEFAULT 0, " //Í£µçʱ³¤
|
+ "`dev_station_poff_cnt` INT NOT NULL DEFAULT 0, " //Í£µç´ÎÊý
|
+ "`dev_data_flowsum` BIGINT NOT NULL DEFAULT 0, " //»ú·¿Á÷Á¿
|
+ "`batt_online_state` INT NOT NULL DEFAULT 0, " //µç³Ø״̬(0:±ÕºÏ,1£º¶Ï¿ª)
|
|
+ " UNIQUE INDEX index_dev_id_uniq (`dev_id`), "
|
+ " PRIMARY KEY (`num`) ) "
|
+ " ENGINE=InnoDB DEFAULT CHARSET=utf8";
|
/*
|
String str3 = "INSERT INTO " + Sql_Mysql.FBS9100State_Table + " (dev_ip, dev_id) ";
|
str3 += " SELECT dev_ip, dev_id FROM " + Sql_Mysql.BattInf_Table;
|
*/
|
Sql_Mysql sql = new Sql_Mysql(con_pool.getConn());
|
try {
|
sql.sqlMysqlExecute(str1);
|
sql.sqlMysqlExecute(str2);
|
//sql.sqlMysqlExecute(str3);
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
/**
|
* ´´½¨ tb_fbs9100s_dcdc_data ±í²¢ÇÒ½«É豸idºÍÉ豸ip²åÈëµ½±íÖÐ
|
* @param con_pool
|
* @param al_param
|
*/
|
public static void createFBS9100S_DCDC_DataTableOnRam(MysqlConnPool con_pool)
|
{
|
String str1 = "DROP TABLE IF EXISTS " + Sql_Mysql.FBS9100S_DcDcState_Table;
|
String str2 = "CREATE TABLE IF NOT EXISTS " + Sql_Mysql.FBS9100S_DcDcState_Table
|
+ " ( `num` BIGINT NOT NULL AUTO_INCREMENT, "
|
+ "`dev_id` BIGINT NOT NULL DEFAULT 0, "
|
+ "`dcdc_num` BIGINT NOT NULL DEFAULT 0, "
|
+ "`dcdc_Vsys` float NOT NULL DEFAULT 0, "
|
+ "`dcdc_Vout` float NOT NULL DEFAULT 0, "
|
+ "`dcdc_Vbat` float NOT NULL DEFAULT 0, "
|
+ "`dcdc_Iout` float NOT NULL DEFAULT 0, "
|
+ "`dcdc_Ibuck` float NOT NULL DEFAULT 0, "
|
+ "`dcdc_Tmp_Dis` float NOT NULL DEFAULT 0, "
|
+ "`dcdc_Tmp_Chr` float NOT NULL DEFAULT 0, "
|
+ "`dcdc_SMR_Alarm1` INT NOT NULL DEFAULT 0, "
|
+ "`dcdc_SMR_Alarm2` INT NOT NULL DEFAULT 0, "
|
+ "`dcdc_version` INT NOT NULL DEFAULT 0, "
|
+ "`dcdc_ModeSetComm` INT NOT NULL DEFAULT 0, "
|
+ "`dcdc_RunSetComm` INT NOT NULL DEFAULT 0, "
|
+ "`dcdc_BuckISet` float NOT NULL DEFAULT 0, "
|
+ "`dcdc_DisVolSet` float NOT NULL DEFAULT 0, "
|
+ "`dcdc_DisCurrSet` float NOT NULL DEFAULT 0, "
|
+ "`dcdc_CommTxCnt` INT NOT NULL DEFAULT 0, "
|
+ "`dcdc_CommRxCnt` INT NOT NULL DEFAULT 0, "
|
+ " UNIQUE INDEX index_dcdc_num_uniq (`dcdc_num`), "
|
+ " PRIMARY KEY (`num`) ) "
|
+ " ENGINE=MEMORY DEFAULT CHARSET=utf8";
|
|
Sql_Mysql sql = new Sql_Mysql(con_pool.getConn());
|
try {
|
sql.sqlMysqlExecute(str1);
|
sql.sqlMysqlExecute(str2);
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
/**
|
* ´´½¨ tb_fbs9100_setparam ±í£¬²¢ÇÒ½«É豸idºÍÉ豸ip²åÈëµ½±íÖÐ
|
* @param con_pool
|
* @param al_param
|
*/
|
public static void createFBS9100SetTestParamTableOnRam(MysqlConnPool con_pool)
|
{
|
String str1 = "DROP TABLE IF EXISTS " + Sql_Mysql.FBS9100SetParam_Table;
|
String str2 = "CREATE TABLE IF NOT EXISTS " + Sql_Mysql.FBS9100SetParam_Table
|
+ " ( `num` BIGINT NOT NULL AUTO_INCREMENT, "
|
+ "`dev_ip` varchar(20) NOT NULL DEFAULT '127.0.0.0', "
|
+ "`dev_id` BIGINT NOT NULL DEFAULT 0, "
|
+ "`op_cmd` INT NOT NULL DEFAULT 0, "
|
+ "`TestCmd` INT NOT NULL DEFAULT 0, "
|
+ "`HourRate` INT NOT NULL DEFAULT 0, "
|
+ "`DisCurr` FLOAT NOT NULL DEFAULT 0, "
|
+ "`DisCap` FLOAT NOT NULL DEFAULT 0, "
|
+ "`DisTime` INT NOT NULL DEFAULT 0, "
|
+ "`GroupVol_LOW` FLOAT NOT NULL DEFAULT 0, "
|
+ "`MonomerVol_LOW` FLOAT NOT NULL DEFAULT 0, "
|
+ "`MonomerLowCount` INT NOT NULL DEFAULT 0, "
|
+ "`BattGroupNum` INT NOT NULL DEFAULT 0, "
|
+ "`OnlineVolLowAction` INT NOT NULL DEFAULT 0, "
|
+ "`DCVolHighLimit` FLOAT NOT NULL DEFAULT 0, "
|
+ "`ChargeCurrSet` FLOAT NOT NULL DEFAULT 0, "
|
+ "`MonomerTmp_High` FLOAT NOT NULL DEFAULT 0, "
|
+ "`AutoTestStartVol` float NOT NULL DEFAULT 0, " //Æðʼµçѹ
|
+ " UNIQUE INDEX index_dev_id_uniq (`dev_id`), "
|
+ " PRIMARY KEY (`num`) ) "
|
+ " ENGINE=MEMORY DEFAULT CHARSET=utf8";
|
|
Sql_Mysql sql = new Sql_Mysql(con_pool.getConn());
|
try {
|
sql.sqlMysqlExecute(str1);
|
sql.sqlMysqlExecute(str2);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
/**
|
* ´´½¨ Sql_Mysql.RamDB_CMCC_POWER_Data ±í²¢ÇÒ½«É豸id²åÈëµ½±íÖÐ
|
* @param con_pool
|
* @param al_param
|
*/
|
public static void createFBS9100CmccPowerDataOnRam(MysqlConnPool con_pool)
|
{
|
String str1 = "DROP TABLE IF EXISTS " + Sql_Mysql.RamDB_CMCC_POWER_Data;
|
String str2 = "CREATE TABLE IF NOT EXISTS " + Sql_Mysql.RamDB_CMCC_POWER_Data
|
+ " ( `num` BIGINT NOT NULL AUTO_INCREMENT, "
|
+ "`dev_id` BIGINT NOT NULL DEFAULT 0, "
|
+ "`vol1` float NOT NULL DEFAULT 0, "
|
+ "`vol2` float NOT NULL DEFAULT 0, "
|
+ "`vol3` float NOT NULL DEFAULT 0, "
|
+ "`vol4` float NOT NULL DEFAULT 0, "
|
+ "`vol5` float NOT NULL DEFAULT 0, "
|
+ "`curr1` float NOT NULL DEFAULT 0, "
|
+ "`curr2` float NOT NULL DEFAULT 0, "
|
+ "`curr3` float NOT NULL DEFAULT 0, "
|
+ "`curr4` float NOT NULL DEFAULT 0, "
|
+ "`curr5` float NOT NULL DEFAULT 0, "
|
+ " UNIQUE INDEX index_dev_id_uniq (`dev_id`), "
|
+ " PRIMARY KEY (`num`) ) "
|
+ " ENGINE=InnoDB DEFAULT CHARSET=utf8";
|
|
Sql_Mysql sql = new Sql_Mysql(con_pool.getConn());
|
try {
|
sql.sqlMysqlExecute(str1);
|
sql.sqlMysqlExecute(str2);
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
/***********************************************************************************/
|
|
/**
|
*
|
* @param gB_MysqlConnPool
|
*/
|
public static void createBTSStationState_TableOnRam(MysqlConnPool con_pool,boolean recreate_tb) {
|
String str1 = "DROP TABLE IF EXISTS " + Sql_Mysql.BTSStationState_Table;
|
String str2 = "CREATE TABLE IF NOT EXISTS " + Sql_Mysql.BTSStationState_Table
|
+ " ( `num` BIGINT NOT NULL AUTO_INCREMENT, "
|
+ "`station_id` BIGINT NOT NULL DEFAULT 0, "
|
+ "`dev_id` BIGINT NOT NULL DEFAULT 0, "
|
+ "`record_datetime` varchar(20) NOT NULL DEFAULT '2015-12-02 12:10:00', "
|
+ "`station_stat` INT NOT NULL DEFAULT 0, "
|
+ "`station_poff_start_time` varchar(20) NOT NULL DEFAULT '2015-12-02 12:10:00', "
|
//+ "`station_poff_tlong` INT NOT NULL DEFAULT 0, "
|
+ "`station_fadian_start_time` varchar(20) NOT NULL DEFAULT '2015-12-02 12:10:00', "
|
//+ "`station_fadian_tlong` INT NOT NULL DEFAULT 0, "
|
+ "`station_diaozhan_start_time` varchar(20) NOT NULL DEFAULT '2015-12-02 12:10:00', "
|
//+ "`station_diaozhan_tlong` INT NOT NULL DEFAULT 0, "
|
+ "`station_temp` float NOT NULL DEFAULT 0, " //ζÈ
|
+ "`station_damp` float NOT NULL DEFAULT 0, " //ʪ¶È
|
+ "`station_dooropen_start_time` varchar(20) NOT NULL DEFAULT '2015-12-02 12:10:00', "
|
//+ "`station_dooropen_tlong` INT NOT NULL DEFAULT 0, "
|
+ "`station_xuhang_start_time` varchar(20) NOT NULL DEFAULT '2015-12-02 12:10:00', "
|
+ "`station_xuhang_end_time` varchar(20) NOT NULL DEFAULT '2015-12-02 12:10:00', "
|
+ " UNIQUE INDEX index_station_id_uniq (`station_id`), "
|
+ " PRIMARY KEY (`num`) ) "
|
+ " ENGINE=InnoDB DEFAULT CHARSET=utf8";
|
Sql_Mysql sql = new Sql_Mysql(con_pool.getConn());
|
try {
|
if(recreate_tb) {
|
sql.sqlMysqlExecute(str1);
|
}
|
sql.sqlMysqlExecute(str2);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
/**
|
* ²åÈëÔÚ»ú·¿×´Ì¬±íÖÐδÌí¼Ó»ú·¿×´Ì¬µÄ¼Ç¼
|
* @param gB_MysqlConnPool
|
*/
|
public static void insertBTSStationState_TableOnRam(MysqlConnPool con_pool) {
|
//²éѯûÓÐÌí¼Ó»ú·¿×´Ì¬µÄ»ú·¿¼Ç¼
|
String sql_str1 = " SELECT DISTINCT StationId,FBSDeviceId " +
|
" FROM " + Sql_Mysql.BattInf_Table +
|
" WHERE StationId NOT IN( " +
|
" SELECT station_id " +
|
" FROM "+ Sql_Mysql.BTSStationState_Table +
|
")";
|
//Ìí¼Ó»ú·¿×´Ì¬µÄ¼Ç¼
|
String sql_str2 = " INSERT INTO " + Sql_Mysql.BTSStationState_Table + "(dev_id,station_id) VALUES ";
|
|
Sql_Mysql sql = new Sql_Mysql(con_pool.getConn());
|
ResultSet rs = null;
|
try {
|
rs= sql.sqlMysqlQuery(sql_str1);
|
//System.out.println(sql_str1);
|
boolean hasInsertStation = false;;
|
while(rs.next()) {
|
int dev_id = rs.getInt("FBSDeviceId");
|
int stationid = rs.getInt("StationId");
|
if( !hasInsertStation ) {
|
sql_str2 = sql_str2 + "(" + dev_id + "," + stationid + ")";
|
hasInsertStation = true;
|
}else {
|
sql_str2 = sql_str2 + ",(" + dev_id + "," + stationid + ")";
|
}
|
}
|
if(hasInsertStation) {
|
//System.out.println(sql_str2);
|
sql.sqlMysqlExecute(sql_str2);
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
/**
|
* ´´½¨É豸µÄϵͳ²ÎÊý±í
|
* @param con_pool
|
* @param recreate_tb
|
*/
|
public static void createFBS9100SysParam_TableOnRam(MysqlConnPool con_pool,boolean recreate_tb) {
|
String sql_str0 = "DROP TABLE IF EXISTS " + Sql_Mysql.FBS9100SysParam_Table + "; ";
|
String sql_str1 = "CREATE TABLE IF NOT EXISTS " + Sql_Mysql.FBS9100SysParam_Table
|
+ " ( `num` BIGINT NOT NULL AUTO_INCREMENT, "
|
+ "`dev_ip` varchar(20) NOT NULL DEFAULT '127.0.0.0', "
|
+ "`dev_id` BIGINT NOT NULL DEFAULT 0, "
|
+ "`op_cmd` INT NOT NULL DEFAULT 0, "
|
+ "`IPADDR` varchar(20) NOT NULL DEFAULT '127.0.0.0', "
|
+ "`SubIPADDR` varchar(20) NOT NULL DEFAULT '127.0.0.0', "
|
+ "`NetGateADDR` varchar(20) NOT NULL DEFAULT '127.0.0.0', "
|
+ "`GroupVolRange` INT NOT NULL DEFAULT 0, "
|
+ "`GroupVolSorce` INT NOT NULL DEFAULT 0, "
|
+ "`CurrentRange` INT NOT NULL DEFAULT 0, "
|
+ "`CurrentTyte` INT NOT NULL DEFAULT 0, "
|
+ "`MonomerOrder` INT NOT NULL DEFAULT 0, "
|
+ "`BackLightTime` INT NOT NULL DEFAULT 0, "
|
+ "`MACADDR` varchar(30) NOT NULL DEFAULT '0.0.0.0.0.0', "
|
+ "`LoaderCount` INT NOT NULL DEFAULT 0, "
|
+ "`DtCardCount` INT NOT NULL DEFAULT 0, "
|
+ "`PowerBreakChargeCurr` FLOAT NOT NULL DEFAULT 0, "
|
+ "`MajorBattGroupNum` INT NOT NULL DEFAULT 0, "
|
+ "`POF_BG_Boost_Cnt` INT NOT NULL DEFAULT 0, "
|
+ "`POF_BG_Boost_VolStop` FLOAT NOT NULL DEFAULT 0, "
|
+ "`POF_BG_Boost_VolStart` FLOAT NOT NULL DEFAULT 0, "
|
+ " UNIQUE INDEX index_dev_id_uniq (`dev_id`), "
|
+ " PRIMARY KEY (`num`) ) "
|
+ " ENGINE=MEMORY DEFAULT CHARSET=utf8";
|
Sql_Mysql sql = new Sql_Mysql(con_pool.getConn());
|
try {
|
if(true == recreate_tb) {
|
sql.sqlMysqlExecute(sql_str0);
|
} else {
|
//sql.sqlMysqlExecute("DELETE FROM " + Sql_Mysql.FBS9100SysParam_Table);
|
}
|
sql.sqlMysqlExecute(sql_str1);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
}
|
