package com.electrical.TwoKW;
|
|
import java.sql.ResultSet;
|
import java.sql.SQLException;
|
import java.util.Date;
|
import java.util.List;
|
|
import com.base.Com;
|
import com.modbus.data.MyModbusMaster;
|
import com.sql.MysqlConnPool;
|
import com.sql.Sql_Mysql;
|
|
public class Electrical_2KWTask_SQL {
|
|
/**
|
* ³õʼ»¯Êý¾Ý¿â±í
|
* @param pool
|
*/
|
public static void init(MysqlConnPool pool) {
|
createDB_Electric2MWSystem(pool);
|
|
createTb_electric2MW_inf(pool);
|
|
//initTb_electric2MW_inf(pool);
|
|
createTb_electric2MW_rt(pool);
|
|
createTb_electric2MW_control(pool);
|
|
|
createTb_electric2MW_state(pool);
|
|
createTb_Device_ConnectState(pool);
|
|
createTb_Electric_Alarm(pool);
|
|
createTb_Electric_Alarm_History(pool);
|
}
|
|
|
/**
|
* ³õʼ»¯¸ºÔØÐÅÏ¢±í
|
* @param pool
|
*/
|
public static void initTb_electric2MW_inf(MysqlConnPool pool) {
|
String sql_str= " replace INTO " + Sql_Mysql.Tb_electric2MW_inf + "(electric2KW_id,electric2KW_ip,electric2KW_name,electric2KW_type) VALUES(6001,'127.0.0.1','2MW','1'),(6002,'127.0.0.1','2MW','1')";
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
/**
|
* ´´½¨2MWµçԴϵͳÊý¾Ý¿â
|
* @param pool
|
*/
|
public static void createDB_Electric2MWSystem(MysqlConnPool pool) {
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
sql.sqlMysqlExecute("CREATE DATABASE IF NOT EXISTS " + Sql_Mysql.DB_Electric2MWSystem);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
/**
|
* ´´½¨2MWÐÅÏ¢±í
|
* @param pool
|
*/
|
public static void createTb_electric2MW_inf(MysqlConnPool pool) {
|
String sql_str = "CREATE TABLE IF NOT EXISTS " + Sql_Mysql.Tb_electric2MW_inf + " (" +
|
" num bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Ö÷¼ü'," +
|
" electric2KW_id int(11) NOT NULL DEFAULT '5001' COMMENT 'µçÔ´id'," +
|
" electric2KW_ip varchar(255) NOT NULL DEFAULT '127.0.0.1' COMMENT 'µçÔ´ip'," +
|
" electric2KW_name varchar(255) NOT NULL DEFAULT 'A×é' COMMENT 'µçÔ´Ãû³Æ'," +
|
" electric2KW_type int(11) NOT NULL DEFAULT '2' COMMENT 'µçÔ´ÀàÐÍ'," +
|
" note varchar(255) NOT NULL DEFAULT ''," +
|
" PRIMARY KEY (num)," +
|
" UNIQUE KEY electric2KW_id_key (electric2KW_id) USING BTREE " +
|
") ENGINE=InnoDB 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();
|
}
|
}
|
/**
|
* ´´½¨2MWʵʱÐÅÏ¢±í
|
* @param pool
|
*/
|
public static void createTb_electric2MW_rt(MysqlConnPool pool) {
|
String sql_str = "CREATE TABLE IF NOT EXISTS " + Sql_Mysql.Tb_electric2MW_rt + " (" +
|
" num bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Ö÷¼ü'," +
|
" electric2KW_id int(11) NOT NULL DEFAULT '5001' COMMENT 'µçÔ´id'," +
|
" upper_pulse_block int(11) NOT NULL DEFAULT '0' COMMENT 'ÉÏλ»úÂö³å·âËø'," +
|
" consvolcurrstatus int(11) NOT NULL DEFAULT '0' COMMENT 'ºãѹºãÁ÷ģʽ״̬'," +
|
" constantvolflag int(11) NOT NULL DEFAULT '0' COMMENT '4²¢»ò2´®2²¢ºãѹ±ê־λ'," +
|
" main_circuit_connmode int(11) NOT NULL DEFAULT '0' COMMENT 'Ö÷µç·Á¬½Óģʽ'," +
|
" remote_start int(11) NOT NULL DEFAULT '0' COMMENT 'Ô¶³ÌÆô¶¯'," +
|
" fault_reset int(11) NOT NULL DEFAULT '0' COMMENT '¹ÊÕϸ´Î»'," +
|
" conscurrswitch int(11) NOT NULL DEFAULT '0' COMMENT 'ºãÁ÷Çл»'," +
|
" constantvol float NOT NULL DEFAULT '0' COMMENT 'A»ú×é4²¢»ò2´®2²¢ºãѹ'," +
|
" volset_confirm int(11) NOT NULL DEFAULT '0' COMMENT 'µçѹ¸ø¶¨È·ÈÏ'," +
|
" currset_confirm int(11) NOT NULL DEFAULT '0' COMMENT 'µçÁ÷¸ø¶¨È·ÈÏ'," +
|
" remote_stop int(11) NOT NULL DEFAULT '0' COMMENT 'Ô¶³ÌÍ£Ö¹'," +
|
" start_screen_set int(11) NOT NULL DEFAULT '0' COMMENT 'Æô¶¯´¥ÃþÆÁ¸ø¶¨'," +
|
" start_upper_set int(11) NOT NULL DEFAULT '0' COMMENT 'Æô¶¯ÉÏλ»ú¸ø¶¨'," +
|
" consvolswitch int(11) NOT NULL DEFAULT '0' COMMENT 'ºãѹÇл»'," +
|
" othermode int(11) NOT NULL DEFAULT '0' COMMENT '4²¢»ò2´®2²¢ÒÔÍâģʽ'," +
|
" consvol_distribution int(11) NOT NULL DEFAULT '0' COMMENT 'ËÄ´®ºãѹµçѹ·ÖÅäλ'," +
|
" local_remote_control int(11) NOT NULL DEFAULT '0' COMMENT '¾ÍµØÔ¶³Ì¿ØÖÆ״̬'," +
|
|
" pulse_blocking int(11) NOT NULL DEFAULT '0' COMMENT 'Âö³å·âËø'," +
|
" dc_voltage_state int(11) NOT NULL DEFAULT '0' COMMENT '¸ßѹ¿ª¹Ø״̬'," +
|
|
" curve_charge_mode int(11) NOT NULL DEFAULT '0' COMMENT 'ÇúÏß³äµçģʽ״̬'," +
|
" start_curve_charge_mode int(11) NOT NULL DEFAULT '0' COMMENT 'Æô¶¯ÇúÏß³äµçģʽ'," +
|
" stop_curve_charge_mode int(11) NOT NULL DEFAULT '0' COMMENT 'Í£Ö¹ÇúÏß³äµçģʽ'," +
|
" note varchar(255) NOT NULL DEFAULT ''," +
|
" PRIMARY KEY (num)," +
|
" UNIQUE KEY electric2KW_id_key (electric2KW_id) USING BTREE " +
|
") ENGINE=InnoDB 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();
|
}
|
}
|
|
/**
|
* ´´½¨2MWʵʱÐÅϢ״̬±í
|
* @param pool
|
*/
|
public static void createTb_electric2MW_state(MysqlConnPool pool) {
|
String sql_str = "CREATE TABLE IF NOT EXISTS " + Sql_Mysql.Tb_electric2MW_state + " (" +
|
" num bigint(20) NOT NULL AUTO_INCREMENT," +
|
" electric2KW_id int(11) NOT NULL," +
|
" brachdcvol float NOT NULL DEFAULT '0' COMMENT '·ÖÖ±Á÷µçѹ'," +
|
" brachdccurr float NOT NULL DEFAULT '0' COMMENT '·ÖÖ±Á÷µçÁ÷'," +
|
" alldcvol float NOT NULL DEFAULT '0' COMMENT '×ÜÖ±Á÷µçѹ'," +
|
" alldccurr float NOT NULL DEFAULT '0' COMMENT '×ÜÖ±Á÷µçÁ÷'," +
|
" stopchargevol_one float NOT NULL DEFAULT '0' COMMENT 'µÚ1½×¶Î³äµçÖÕÖ¹µçѹ'," +
|
" stopchargevol_two float NOT NULL DEFAULT '0' COMMENT 'µÚ2½×¶Î³äµçÖÕÖ¹µçѹ'," +
|
" stopchargevol_three float NOT NULL DEFAULT '0' COMMENT 'µÚ3½×¶Î³äµçÖÕÖ¹µçѹ'," +
|
" stopchargevol_four float NOT NULL DEFAULT '0' COMMENT 'µÚ4½×¶Î³äµçÖÕÖ¹µçѹ'," +
|
" stopchargevol_five float NOT NULL DEFAULT '0' COMMENT 'µÚ5½×¶Î³äµçÖÕÖ¹µçѹ'," +
|
" controlangle float NOT NULL DEFAULT '0' COMMENT '¿ØÖƽÇ'," +
|
" localgivevol float NOT NULL DEFAULT '0' COMMENT 'µ±Ç°¸ø¶¨µçѹ'," +
|
" volset float NOT NULL DEFAULT '0' COMMENT 'µçѹÉ趨ֵ'," +
|
" localgivecurr float NOT NULL DEFAULT '0' COMMENT 'µ±Ç°¸ø¶¨µçÁ÷'," +
|
" currset float NOT NULL DEFAULT '0' COMMENT 'µçÁ÷É趨ֵ'," +
|
" chargecurr_one float NOT NULL DEFAULT '0' COMMENT 'µÚ1½×¶Î³äµçµçÁ÷Öµ'," +
|
" chargecurr_two float NOT NULL DEFAULT '0' COMMENT 'µÚ2½×¶Î³äµçµçÁ÷Öµ'," +
|
" chargecurr_three float NOT NULL DEFAULT '0' COMMENT 'µÚ3½×¶Î³äµçµçÁ÷Öµ'," +
|
" chargecurr_four float NOT NULL DEFAULT '0' COMMENT 'µÚ4½×¶Î³äµçµçÁ÷Öµ'," +
|
" chargecurr_five float NOT NULL DEFAULT '0' COMMENT 'µÚ5½×¶Î³äµçµçÁ÷Öµ'," +
|
" consvolmode_chargecurr float NOT NULL DEFAULT '0' COMMENT 'תΪºãѹģʽ³äµçµçѹֵ'," +
|
" note varchar(255) NOT NULL DEFAULT '' COMMENT '±¸ÓÃ'," +
|
" PRIMARY KEY (num)," +
|
" UNIQUE KEY index_electric2KW_id (electric2KW_id) USING BTREE" +
|
") ENGINE=InnoDB 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();
|
}
|
}
|
|
|
|
|
/**
|
* ´´½¨2MW¿ØÖÆÐÅÏ¢±í
|
* @param pool
|
*/
|
public static void createTb_electric2MW_control(MysqlConnPool pool) {
|
String sql_str = "CREATE TABLE IF NOT EXISTS " + Sql_Mysql.Tb_electric2MW_control + " (" +
|
" num bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Ö÷¼ü'," +
|
" electric2KW_id int(11) NOT NULL DEFAULT '5001' COMMENT 'µçÔ´id'," +
|
" consvolmode_chargecurr float NOT NULL DEFAULT '0' COMMENT 'תΪºãѹģʽ³äµçµçѹֵ'," +
|
" chargecurr_five float NOT NULL DEFAULT '0' COMMENT 'µÚ5½×¶Î³äµçµçÁ÷Öµ'," +
|
" chargecurr_four float NOT NULL DEFAULT '0' COMMENT 'µÚ4½×¶Î³äµçµçÁ÷Öµ'," +
|
" chargecurr_three float NOT NULL DEFAULT '0' COMMENT 'µÚ3½×¶Î³äµçµçÁ÷Öµ'," +
|
" chargecurr_two float NOT NULL DEFAULT '0' COMMENT 'µÚ2½×¶Î³äµçµçÁ÷Öµ'," +
|
" chargecurr_one float NOT NULL DEFAULT '0' COMMENT 'µÚ1½×¶Î³äµçµçÁ÷Öµ'," +
|
" currset float NOT NULL DEFAULT '0' COMMENT 'µçÁ÷É趨ֵ'," +
|
" volset float NOT NULL DEFAULT '0' COMMENT 'µçѹÉ趨ֵ'," +
|
" stopchargevol_five float NOT NULL DEFAULT '0' COMMENT 'µÚ5½×¶Î³äµçÖÕÖ¹µçѹ'," +
|
" stopchargevol_four float NOT NULL DEFAULT '0' COMMENT 'µÚ4½×¶Î³äµçÖÕÖ¹µçѹ'," +
|
" stopchargevol_three float NOT NULL DEFAULT '0' COMMENT 'µÚ3½×¶Î³äµçÖÕÖ¹µçѹ'," +
|
" stopchargevol_two float NOT NULL DEFAULT '0' COMMENT 'µÚ2½×¶Î³äµçÖÕÖ¹µçѹ'," +
|
" stopchargevol_one float NOT NULL DEFAULT '0' COMMENT 'µÚ1½×¶Î³äµçÖÕÖ¹µçѹ'," +
|
" upper_pulse_block int(11) NOT NULL DEFAULT '0' COMMENT 'ÉÏλ»úÂö³å·âËø'," +
|
" remote_start int(11) NOT NULL DEFAULT '0' COMMENT 'Ô¶³ÌÆô¶¯'," +
|
" fault_reset int(11) NOT NULL DEFAULT '0' COMMENT '¹ÊÕϸ´Î»'," +
|
" conscurrswitch int(11) NOT NULL DEFAULT '0' COMMENT 'ºãÁ÷Çл»'," +
|
" constantvol int(11) NOT NULL DEFAULT '0' COMMENT '4²¢»ò2´®2²¢ºãѹ'," +
|
" volset_confirm int(11) NOT NULL DEFAULT '0' COMMENT 'µçѹ¸ø¶¨È·ÈÏ'," +
|
" currset_confirm int(11) NOT NULL DEFAULT '0' COMMENT 'µçÁ÷¸ø¶¨È·ÈÏ'," +
|
" remote_stop int(11) NOT NULL DEFAULT '0' COMMENT 'Ô¶³ÌÍ£Ö¹'," +
|
" start_screen_set int(11) NOT NULL DEFAULT '0' COMMENT 'Æô¶¯´¥ÃþÆÁ¸ø¶¨'," +
|
" start_upper_set int(11) NOT NULL DEFAULT '0' COMMENT 'Æô¶¯ÉÏλ»ú¸ø¶¨'," +
|
" consvolswitch int(11) NOT NULL DEFAULT '0' COMMENT 'ºãѹÇл»'," +
|
" othermode int(11) NOT NULL DEFAULT '0' COMMENT '4²¢»ò2´®2²¢ÒÔÍâģʽ'," +
|
" consvol_distribution int(11) NOT NULL DEFAULT '0' COMMENT 'ËÄ´®ºãѹµçѹ·ÖÅäλ'," +
|
" PRIMARY KEY (num)," +
|
" UNIQUE KEY electric2KW_id_key (electric2KW_id) USING BTREE" +
|
") ENGINE=InnoDB 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
|
*/
|
private static void createTb_Device_ConnectState(MysqlConnPool pool) {
|
String sql_str = " CREATE TABLE IF NOT EXISTS "+Sql_Mysql.Tb_Device_ConnectState+" (" +
|
" num bigint(20) NOT NULL AUTO_INCREMENT," +
|
" dev_id int(11) NOT NULL DEFAULT '0' COMMENT 'É豸id'," +
|
" connect_en int(11) NOT NULL DEFAULT '0' COMMENT 'Á¬½Ó״̬[0:ͨѶ¹ÊÕÏ£»1ͨѶÕý³£]'," +
|
" record_time datetime NOT NULL DEFAULT '2000-01-01 00:00:00' COMMENT '¼Ç¼ʱ¼ä'," +
|
" note varchar(255) NOT NULL DEFAULT '' COMMENT '±¸ÓÃ×Ö¶Î'," +
|
" PRIMARY KEY (num)," +
|
" UNIQUE KEY index_dev_id (dev_id) USING BTREE" +
|
") ENGINE=InnoDB 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();
|
}
|
}
|
|
/**
|
* ²éѯËùÓеÄ2mwµçԴϵͳ
|
* @param pool
|
* @param electric
|
*/
|
public static void queryAllElectrical2MW(MysqlConnPool pool,List<Electric_inf_2kw> einfs) {
|
String sql_str = " select * from " + Sql_Mysql.Tb_electric2MW_inf+ " order by electric2KW_id asc ";
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
ResultSet res = null;
|
try {
|
res = sql.sqlMysqlQuery(sql_str);
|
while(res.next()) {
|
Electric_inf_2kw einf=new Electric_inf_2kw();
|
einf.setElectric2KW_id(res.getInt("electric2kw_id"));
|
einf.setElectric2KW_ip(res.getString("electric2kw_ip"));
|
einf.setElectric2KW_name(res.getString("electric2kw_name"));
|
einf.setElectric2KW_type(res.getInt("electric2kw_type"));
|
einfs.add(einf);
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
if(res != null) {
|
try {
|
res.close();
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
}
|
sql.close_con();
|
}
|
}
|
|
//²åÈë»ò¸üÐÂ2mwµçԴϵͳʵʱÐÅÏ¢±í
|
public static void replace_electric2MW_rt(MysqlConnPool pool,Electric_Rt_2kw ert) {
|
String sql_str = " replace into "+ Sql_Mysql.Tb_electric2MW_rt+"(electric2KW_id) "
|
+ " values("+ert.electric2KW_id+")";
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
//²åÈë»ò¸üÐÂ2mwµçԴϵͳʵʱÐÅÏ¢±í
|
public static void replace_electric2MW_state(MysqlConnPool pool,Electric_State_2kw state) {
|
String sql_str = " replace into "+ Sql_Mysql.Tb_electric2MW_state+"(electric2KW_id) "
|
+ " values("+state.electric2KW_id+")";
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
//²åÈë»ò¸üÐÂ2mwµçԴϵͳÐÅÏ¢±í
|
public static void replace_electric2MW_inf(MysqlConnPool pool,Electric_inf_2kw einf) {
|
String sql_str = " replace into "+ Sql_Mysql.Tb_electric2MW_inf+"(electric2KW_id) "
|
+ " values("+einf.electric2KW_id+")";
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
//²åÈë»ò¸üÐÂ2mwµçÔ´¿ØÖÆÐÅÏ¢±í
|
public static void replace_electric2MW_Control(MysqlConnPool pool,Electric_Control_Single_2kw econtrol) {
|
String sql_str = " replace into "+ Sql_Mysql.Tb_electric2MW_control+"(electric2KW_id) "
|
+ " values("+econtrol.electric2KW_id+")";
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
//¸üÐÂ2mwµçԴʵʱÐÅÏ¢±í
|
public static void update_Electric_Rt_2KW(MysqlConnPool pool,Electric_Rt_2kw ert) {
|
String sql_str = " UPDATE "+ Sql_Mysql.Tb_electric2MW_rt +""
|
+ " SET upper_pulse_block = "+ert.upper_pulse_block+","
|
+ " consvolcurrstatus = "+ert.consvolcurrstatus+","
|
+ " constantvolflag = "+ert.constantvolflag+","
|
+ " main_circuit_connmode = "+ert.main_circuit_connmode+","
|
+ " remote_start = "+ert.remote_start+","
|
+ " fault_reset = "+ert.fault_reset+","
|
+ " conscurrswitch = "+ert.conscurrswitch+","
|
+ " constantvol = "+ert.constantvol+","
|
+ " volset_confirm = "+ert.volset_confirm+","
|
+ " currset_confirm = "+ert.currset_confirm+","
|
+ " remote_stop = "+ert.remote_stop+","
|
+ " start_screen_set = "+ert.start_screen_set+","
|
+ " start_upper_set = "+ert.start_upper_set+","
|
+ " consvolswitch = "+ert.consvolswitch+","
|
+ " othermode = "+ert.othermode+","
|
+ " consvol_distribution = "+ert.consvol_distribution+","
|
+ " local_remote_control = "+ert.local_remote_control+","
|
|
+ " pulse_blocking = "+ert.pulse_blocking+"," //Âö³å·âËø
|
+ " dc_voltage_state = "+ert.dc_voltage_state+"," //¸ßѹ¿ª¹Ø״̬
|
|
+ " curve_charge_mode = "+ert.curve_charge_mode+","
|
+ " start_curve_charge_mode = "+ert.start_curve_charge_mode+","
|
+ " stop_curve_charge_mode = "+ert.stop_curve_charge_mode+" "
|
+ " WHERE electric2KW_id = " + ert.electric2KW_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
|
*/
|
public static void createTb_Electric_Alarm(MysqlConnPool pool) {
|
String sql_str = " CREATE TABLE IF NOT EXISTS " + Sql_Mysql.Tb_Electric_2MW_alarm+ " (" +
|
" num bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Ö÷¼ü'," +
|
" electric_id int(11) NOT NULL DEFAULT '4001' COMMENT 'É豸id'," +
|
" alarm_id int(11) NOT NULL DEFAULT '10001' COMMENT '¸æ¾¯id'," +
|
" alarm_starttime datetime NOT NULL DEFAULT '2000-01-01 00:00:00' COMMENT '¸æ¾¯¿ªÊ¼Ê±¼ä'," +
|
" alarm_endtime datetime NOT NULL DEFAULT '2000-01-01 00:00:00' COMMENT '¸æ¾¯½áÊøʱ¼ä'," +
|
" alarm_confirm int(11) NOT NULL DEFAULT '0' COMMENT '¸æ¾¯È·ÈÏ'," +
|
" alarm_confirmtime datetime NOT NULL DEFAULT '2000-01-01 00:00:00' COMMENT '¸æ¾¯È·ÈÏʱ¼ä'," +
|
" note varchar(255) NOT NULL DEFAULT ''," +
|
" PRIMARY KEY (num),"+
|
" index electric_id_key(electric_id) " +
|
") ENGINE=InnoDB 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 createTb_Electric_Alarm_History(MysqlConnPool pool) {
|
String sql_str = " CREATE TABLE IF NOT EXISTS " + Sql_Mysql.Tb_Electric_2MW_alarm_history+ " (" +
|
" num bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Ö÷¼ü'," +
|
" electric_id int(11) NOT NULL DEFAULT '4001' COMMENT 'É豸id'," +
|
" alarm_id int(11) NOT NULL DEFAULT '10001' COMMENT '¸æ¾¯id'," +
|
" alarm_starttime datetime NOT NULL DEFAULT '2000-01-01 00:00:00' COMMENT '¸æ¾¯¿ªÊ¼Ê±¼ä'," +
|
" alarm_endtime datetime NOT NULL DEFAULT '2000-01-01 00:00:00' COMMENT '¸æ¾¯½áÊøʱ¼ä'," +
|
" alarm_confirm int(11) NOT NULL DEFAULT '0' COMMENT '¸æ¾¯È·ÈÏ'," +
|
" alarm_confirmtime datetime NOT NULL DEFAULT '2000-01-01 00:00:00' COMMENT '¸æ¾¯È·ÈÏʱ¼ä'," +
|
" note varchar(255) NOT NULL DEFAULT ''," +
|
" PRIMARY KEY (num),"+
|
" index electric_id_key(electric_id) " +
|
") ENGINE=InnoDB 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 electric_id
|
*/
|
public static void createTb_electric2mw_realdata(MysqlConnPool pool,int electric_id,Date time) {
|
String time_str = Com.getDateTimeFormat(time, Com.DTF_Y_M_D);
|
String sql_str = "CREATE TABLE IF NOT EXISTS "+Sql_Mysql.Tb_electric2MW_realdata+electric_id+"_"+time_str+" (" +
|
" num bigint(20) NOT NULL AUTO_INCREMENT," +
|
" electric2KW_id int(11) NOT NULL DEFAULT '0' COMMENT 'id'," +
|
" record_time datetime NOT NULL DEFAULT '2000-01-01 00:00:00' COMMENT '²âÊÔ½áÊøʱ¼ä'," +
|
" brachdcvol float NOT NULL DEFAULT '0' COMMENT '·ÖÖ±Á÷µçѹ'," +
|
" brachdccurr float NOT NULL DEFAULT '0' COMMENT '·ÖÖ±Á÷µçÁ÷'," +
|
" alldcvol float NOT NULL DEFAULT '0' COMMENT '×ÜÖ±Á÷µçѹ'," +
|
" alldccurr float NOT NULL DEFAULT '0' COMMENT '×ÜÖ±Á÷µçÁ÷'," +
|
" controlangle float NOT NULL DEFAULT '0' COMMENT '¿ØÖƽÇ'," +
|
" localgivevol float NOT NULL DEFAULT '0' COMMENT 'µ±Ç°¸ø¶¨µçѹ'," +
|
" localgivecurr float NOT NULL DEFAULT '0' COMMENT 'µ±Ç°¸ø¶¨µçÁ÷'," +
|
" note varchar(255) NOT NULL DEFAULT '' COMMENT '±¸ÓÃ'," +
|
" PRIMARY KEY (num)," +
|
" KEY index_electric2Kw_id (electric2KW_id) USING BTREE," +
|
" KEY index_record_time (record_time) USING BTREE" +
|
") ENGINE=InnoDB 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 einf
|
* @param time
|
*/
|
public static void insertTb_electric2mw_realdata(MysqlConnPool pool,Electric_inf_2kw einf,Date time) {
|
Electric_State_2kw state = einf.state;
|
String sql_str = " INSERT INTO "+Sql_Mysql.Tb_electric2MW_realdata+einf.electric2KW_id+"_"+Com.getDateTimeFormat(time, Com.DTF_Y_M_D)+"(electric2KW_id,record_time,brachdcvol,brachdccurr,alldcvol,alldccurr,controlangle,localgivevol,localgivecurr) " +
|
" VALUES("
|
+ einf.electric2KW_id+",'"
|
+ Com.getDateTimeFormat(time, Com.DTF_YMDhms)+ "',"
|
+ state.brachdcvol +","
|
+ state.brachdccurr +","
|
+ state.alldcvol +","
|
+ state.alldccurr +","
|
+ state.controlangle +","
|
+ state.localgivevol +","
|
+ state.localgivecurr +""
|
+ ");";
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
|
}
|
|
//¸üÐÂ2mwµçÔ´¿ØÖÆÐÅÏ¢±í
|
public static void update_Electric_Control_2KW(MysqlConnPool pool,Electric_Control_Single_2kw econ) {
|
String sql_str = " UPDATE "+ Sql_Mysql.Tb_electric2MW_control +""
|
+ " SET consvolmode_chargecurr = "+econ.consvolmode_chargecurr+","
|
|
+ " upper_pulse_block = "+econ.upper_pulse_block+","
|
+ " remote_start = "+econ.remote_start+","
|
+ " fault_reset = "+econ.fault_reset+","
|
+ " conscurrswitch = "+econ.conscurrswitch+","
|
+ " constantvol = "+econ.constantvol+","
|
+ " volset_confirm = "+econ.chargecurr_five+","
|
+ " currset_confirm = "+econ.currset_confirm+","
|
+ " remote_stop = "+econ.remote_stop+","
|
+ " start_screen_set = "+econ.start_screen_set+","
|
+ " start_upper_set = "+econ.start_upper_set+","
|
+ " consvolswitch = "+econ.consvolswitch+","
|
+ " othermode = "+econ.othermode+","
|
+ " consvol_distribution = "+econ.consvol_distribution+","
|
|
+ " chargecurr_five = "+econ.chargecurr_five+","
|
+ " chargecurr_four = "+econ.chargecurr_four+","
|
+ " chargecurr_three = "+econ.chargecurr_three+","
|
+ " chargecurr_two = "+econ.chargecurr_two+","
|
+ " chargecurr_one = "+econ.chargecurr_one+","
|
+ " currset = "+econ.currset+","
|
+ " volset = "+econ.volset+","
|
+ " stopchargevol_five = "+econ.stopchargevol_five+","
|
+ " stopchargevol_four = "+econ.stopchargevol_four+","
|
+ " stopchargevol_three = "+econ.stopchargevol_three+","
|
+ " stopchargevol_two = "+econ.stopchargevol_two+","
|
+ " stopchargevol_one = "+econ.stopchargevol_one+" "
|
+ " WHERE electric2KW_id = " + econ.electric2KW_id;
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
/**
|
* ¸üÐÂÄ£ÄâÁ¿×´Ì¬±í
|
* @param conn_pool
|
* @param state
|
*/
|
public static void update_Electric_State_2KW(MysqlConnPool conn_pool, Electric_State_2kw state) {
|
String sql_str = " UPDATE "+Sql_Mysql.Tb_electric2MW_state +
|
" Set brachdcvol= " + state.brachdcvol +
|
",brachdccurr=" + state.brachdccurr +
|
",alldcvol=" + state.alldcvol +
|
",alldccurr=" + state.alldccurr +
|
",stopchargevol_one=" + state.stopchargevol_one +
|
",stopchargevol_two=" + state.stopchargevol_two +
|
",stopchargevol_three=" + state.stopchargevol_three +
|
",stopchargevol_four=" + state.stopchargevol_four +
|
",stopchargevol_five=" + state.stopchargevol_five +
|
",controlangle=" + state.controlangle +
|
",localgivevol=" + state.localgivevol +
|
",volset=" + state.volset +
|
",localgivecurr=" + state.localgivecurr +
|
",currset=" + state.currset +
|
",chargecurr_one=" + state.chargecurr_one +
|
",chargecurr_two=" + state.chargecurr_two +
|
",chargecurr_three=" + state.chargecurr_three +
|
",chargecurr_four=" + state.chargecurr_four +
|
",chargecurr_five=" + state.chargecurr_five +
|
",consvolmode_chargecurr=" +state.consvolmode_chargecurr+
|
" Where electric2KW_id = " + state.electric2KW_id;
|
Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
|
try {
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
/**
|
* ²éѯµ¥µã¿ØÖƱí
|
* @param conn_pool
|
* @param control
|
*/
|
public static void queryElectric_Control_2KW(MysqlConnPool conn_pool,Electric_Control_Single_2kw control) {
|
String sql_str = "SELECT * FROM "+Sql_Mysql.Tb_electric2MW_control+" Where electric2KW_id = " + control.electric2KW_id;
|
ResultSet res = null;
|
Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
|
try {
|
//System.out.println(sql_str);
|
res = sql.sqlMysqlQuery(sql_str);
|
if(res.next()) {
|
control.setUpper_pulse_block(res.getInt("upper_pulse_block")); //ÉÏλ»úÂö³å·âËø
|
control.setRemote_start(res.getInt("remote_start")); //Ô¶³ÌÆô¶¯
|
control.setFault_reset(res.getInt("fault_reset")); //¹ÊÕϸ´Î»
|
control.setConscurrswitch(res.getInt("conscurrswitch")); //ºãÁ÷Çл»
|
control.setConstantvol(res.getInt("constantvol")); //4²¢»ò2´®2²¢ºãѹ
|
control.setVolset_confirm(res.getInt("volset_confirm")); //µçѹ¸ø¶¨È·ÈÏ
|
control.setCurrset_confirm(res.getInt("currset_confirm")); //µçÁ÷¸ø¶¨È·ÈÏ
|
control.setRemote_stop(res.getInt("remote_stop")); //Ô¶³ÌÍ£Ö¹
|
control.setStart_screen_set(res.getInt("start_screen_set"));; //Æô¶¯´¥ÃþÆÁ¸ø¶¨
|
control.setStart_upper_set(res.getInt("start_upper_set"));; //Æô¶¯ÉÏλ»ú¸ø¶¨
|
control.setConsvolswitch( res.getInt("consvolswitch"));; //ºãѹÇл»
|
control.setOthermode(res.getInt("othermode"));; //4²¢»ò2´®2²¢ÒÔÍâģʽ
|
control.setConsvol_distribution(res.getInt("consvol_distribution"));; //ËÄ´®ºãѹµçѹ·ÖÅäλ
|
|
control.setConsvolmode_chargecurr(res.getFloat("consvolmode_chargecurr")); ; // 'תΪºãѹģʽ³äµçµçѹֵ',
|
control.setChargecurr_one(res.getFloat("chargecurr_one")); ; // 'µÚ1½×¶Î³äµçµçÁ÷Öµ',
|
control.setChargecurr_two(res.getFloat("chargecurr_two")); ; // 'µÚ2½×¶Î³äµçµçÁ÷Öµ',
|
control.setChargecurr_three(res.getFloat("chargecurr_three")); ; // 'µÚ3½×¶Î³äµçµçÁ÷Öµ',
|
control.setChargecurr_four(res.getFloat("chargecurr_four")); ; // 'µÚ4½×¶Î³äµçµçÁ÷Öµ',
|
control.setChargecurr_five(res.getFloat("chargecurr_five")); ; // 'µÚ5½×¶Î³äµçµçÁ÷Öµ',
|
control.setCurrset(res.getFloat("currset")); ; // 'µçÁ÷É趨ֵ',
|
|
control.setVolset(res.getFloat("volset")); ; // 'µçѹÉ趨ֵ',
|
control.setStopchargevol_one(res.getFloat("stopchargevol_one")); ; // 'µÚ1½×¶Î³äµçÖÕÖ¹µçѹ',
|
control.setStopchargevol_two(res.getFloat("stopchargevol_two")); ; // 'µÚ2½×¶Î³äµçÖÕÖ¹µçѹ',
|
control.setStopchargevol_three( res.getFloat("stopchargevol_three")); ; // 'µÚ3½×¶Î³äµçÖÕÖ¹µçѹ',
|
control.setStopchargevol_four(res.getFloat("stopchargevol_four")); ; // 'µÚ4½×¶Î³äµçÖÕÖ¹µçѹ',
|
control.setStopchargevol_five(res.getFloat("stopchargevol_five")); ; // 'µÚ5½×¶Î³äµçÖÕÖ¹µçѹ',
|
//System.err.println(control);
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
if(res != null) {
|
try {
|
res.close();
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
}
|
sql.close_con();
|
}
|
}
|
|
/**
|
* ¸üе±Ç°×´Ì¬±í
|
* @param pool
|
* @param control
|
*/
|
public static void updateElectrical_2KW_Control(MysqlConnPool pool,Electric_Control_Single_2kw control) {
|
String sql_str = " update " + Sql_Mysql.Tb_electric2MW_control +
|
" Set consvolmode_chargecurr=" + control.consvolmode_chargecurr +
|
",chargecurr_five=" + control.chargecurr_five +
|
",chargecurr_four=" + control.chargecurr_four +
|
",chargecurr_three=" + control.chargecurr_three +
|
",chargecurr_two=" + control.chargecurr_two +
|
",chargecurr_one=" + control.chargecurr_one +
|
",currset=" + control.currset +
|
",volset=" + control.volset +
|
",stopchargevol_five=" + control.stopchargevol_five +
|
",stopchargevol_four=" + control.stopchargevol_four +
|
",stopchargevol_three=" + control.stopchargevol_three +
|
",stopchargevol_two=" + control.stopchargevol_two +
|
",stopchargevol_one=" + control.stopchargevol_one +
|
",upper_pulse_block=" + control.upper_pulse_block +
|
",remote_start=" + control.remote_start +
|
",fault_reset=" + control.fault_reset +
|
",conscurrswitch=" + control.conscurrswitch +
|
",constantvol=" + control.constantvol +
|
",volset_confirm=" + control.volset_confirm +
|
",currset_confirm=" + control.currset_confirm +
|
",remote_stop=" + control.remote_stop +
|
",start_screen_set=" + control.start_screen_set +
|
",start_upper_set=" + control.start_upper_set +
|
",consvolswitch=" + control.consvolswitch +
|
",othermode=" + control.othermode +
|
",consvol_distribution=" +control.consvol_distribution +
|
"Where electric2KW_id=" +control.electric2KW_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 dev_id
|
* @param master
|
*/
|
public static void insertOrUpdateDeviceConnectState(MysqlConnPool pool,int dev_id,MyModbusMaster master) {
|
String sql_str_sel = " SELECT * FROM "+Sql_Mysql.Tb_Device_ConnectState+" WHERE dev_id = " + dev_id;
|
String sql_str_ins = " INSERT INTO "+Sql_Mysql.Tb_Device_ConnectState+"(dev_id,connect_en,record_time) values("+dev_id+","+master.getConnectState()+",'"+Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms)+"');";
|
String sql_str_upd = " UPDATE "+Sql_Mysql.Tb_Device_ConnectState+" SET connect_en = "+master.getConnectState()+",record_time='"+Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms)+"' WHERE dev_id = " + dev_id;
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
ResultSet res = null;
|
try {
|
res = sql.sqlMysqlQuery(sql_str_sel);
|
if(res.next()) {
|
sql.sqlMysqlExecute(sql_str_upd);
|
}else {
|
sql.sqlMysqlExecute(sql_str_ins);
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
if(res != null) {
|
try {
|
res.close();
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
sql.close_con();
|
}
|
}
|
}
|
|
//¸üÐÂ2MWµçÔ´¹ÊÕÏÊý¾Ý¿â
|
public static void insertOrUpdateEle_alarmTable(MysqlConnPool conn_pool, List<Electric_2MW_alarm> list) {
|
if(list!=null&&list.size()>0) {
|
for (Electric_2MW_alarm ealarm : list) {
|
String sql_str_sel = " select * from " + Sql_Mysql.Tb_Electric_2MW_alarm + " where electric_id = " + ealarm.getElectric_id()+" and alarm_id="+ealarm.getAlarm_id();
|
String sql_str_ins = " INSERT INTO " + Sql_Mysql.Tb_Electric_2MW_alarm + "(electric_id,alarm_id,alarm_starttime,alarm_endtime,alarm_confirm,alarm_confirmtime) "
|
+" VALUES("+ealarm.electric_id+","+ealarm.alarm_id+",'"+Com.getDateTimeFormat(ealarm.alarm_starttime,Com.DTF_YMDhms)+"','"+Com.getDateTimeFormat(ealarm.getAlarm_endtime(),Com.DTF_YMDhms)+"',"+ealarm.alarm_confirm+",'"+Com.getDateTimeFormat(ealarm.alarm_confirmtime,Com.DTF_YMDhms)+"');";
|
String sql_str_update= " update " + Sql_Mysql.Tb_Electric_2MW_alarm +
|
" set alarm_starttime='"+Com.getDateTimeFormat(ealarm.alarm_starttime,Com.DTF_YMDhms)+
|
"',alarm_endtime='"+Com.getDateTimeFormat(ealarm.getAlarm_endtime(),Com.DTF_YMDhms)+
|
"',alarm_confirm="+ealarm.alarm_confirm+
|
//",alarm_confirmtime='"+Com.getDateTimeFormat(ealarm.getAlarm_confirmtime(),Com.DTF_YMDhms)+"'"+
|
" where electric_id="+ealarm.electric_id+
|
" and alarm_id="+ealarm.alarm_id;
|
|
ResultSet res = null;
|
Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
|
try {
|
res = sql.sqlMysqlQuery(sql_str_sel);
|
if(res.next()) {
|
//¸üе±Ç°¿ØÖÆ״̬
|
sql.sqlMysqlExecute(sql_str_update);
|
}else {
|
sql.sqlMysqlExecute(sql_str_ins);
|
}
|
} catch (Exception e) {
|
e.printStackTrace();
|
} finally {
|
if(res != null) {
|
try {
|
res.close();
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
}
|
sql.close_con();
|
}
|
}
|
|
}
|
}
|
|
|
/**
|
* ɾ³ý´´½¨Ê±¼ä³¬Ê±µÄÀúʷʵʱ¼Ç¼±í
|
* @param conn_pool
|
* @param deldate
|
*/
|
public static void deleteHistoryData(MysqlConnPool conn_pool,Date deldate) {
|
String sql_select_strs = " select TABLE_NAME,UPDATE_TIME,CREATE_TIME " +
|
" from information_schema.tables " +
|
" where table_schema='db_electric2mwsystem' " +
|
" AND TABLE_NAME like 'tb_electric2mw_realdata_%' " +
|
" AND CREATE_TIME <= '"+Com.getDateTimeFormat(deldate, Com.DTF_YMDhms)+"';" ;
|
String sql_delete_strs = " DROP TABLE IF EXISTS ";
|
Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
|
ResultSet res = null;
|
int count = 0;
|
res = sql.sqlMysqlQuery(sql_select_strs);
|
try {
|
while(res.next()) {
|
if(count > 0) {
|
sql_delete_strs += ",";
|
}
|
|
sql_delete_strs += "db_electric2mwsystem." + res.getString("TABLE_NAME");
|
System.out.println("ɾ³ý£º"+res.getString("TABLE_NAME")+"\t at "+Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms));
|
count++;
|
}
|
if(count >0) {
|
sql.sqlMysqlExecute(sql_delete_strs);
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
if(res != null) {
|
try {
|
res.close();
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
}
|
sql.close_con();
|
}
|
}
|
|
/**
|
* ²éѯµ±Ç°É豸ÉÏÒ»´ÎµÄÀúÊ·¸æ¾¯¼Ç¼±í
|
* @param pool
|
* @param alarm
|
*/
|
public static void searchLastDeviceAlarm(MysqlConnPool pool,Electric_2MW_alarm alarm) {
|
String sql_str = "SELECT * FROM "+Sql_Mysql.Tb_Electric_2MW_alarm+" WHERE electric_id = " + alarm.electric_id;
|
ResultSet res = null;
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
res = sql.sqlMysqlQuery(sql_str);
|
while(res.next()) {
|
//¼ÓÔص±Ç°É豸֮ǰµÄʵʱ¸æ¾¯¼Ç¼
|
alarm.setAlarmStateByAlarm_id(res.getInt("alarm_id"));
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
if(res != null) {
|
try {
|
res.close();
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
}
|
sql.close_con();
|
}
|
}
|
}
|
