package com.electrical.FourKW;
|
|
import java.security.interfaces.RSAKey;
|
import java.sql.ResultSet;
|
import java.sql.SQLException;
|
import java.util.Date;
|
import java.util.Iterator;
|
import java.util.List;
|
|
import com.base.Com;
|
import com.modbus.data.MyModbusMaster;
|
import com.mysql.jdbc.EscapeTokenizer;
|
import com.sql.MysqlConnPool;
|
import com.sql.Sql_Mysql;
|
|
public class Electrical_Task_SQL {
|
|
|
|
/**
|
* ²éѯËùÓеÄÉ豸¼Ç¼
|
* @param pool
|
* @param electric
|
*/
|
public static String queryAllElectric(MysqlConnPool pool,List<Electric_inf> electrics) {
|
String PLC_ip="";
|
String sql_str = " select * from " + Sql_Mysql.Tb_Electric_Inf + " order by electric_id;";
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
ResultSet res = null;
|
try {
|
res = sql.sqlMysqlQuery(sql_str);
|
Electric_inf elect = null;
|
while(res.next()) {
|
elect = new Electric_inf(res.getInt("electric_id"));
|
elect.setElectric_name(res.getString("electric_name"));
|
elect.setElectric_type(res.getInt("electric_type"));
|
elect.setElectric_ip(res.getString("electric_ip"));
|
electrics.add(elect);
|
PLC_ip=elect.getElectric_ip();
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
if(res != null) {
|
try {
|
res.close();
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
}
|
sql.close_con();
|
}
|
return PLC_ip;
|
}
|
|
/**
|
* ³õʼ»¯Êý¾Ý¿â±í
|
* @param pool
|
*/
|
public static void init(MysqlConnPool pool) {
|
|
createDB_ElectricSystem(pool);
|
|
createTb_Electric_inf(pool);
|
|
//createTb_Electric_Switch(pool);
|
|
createTb_Electric_State(pool);
|
|
createTb_Electric_Rt(pool);
|
|
//createTb_Electric_Power(pool);
|
|
createTb_Electric_Control_Single(pool);
|
|
createTb_Electric_Alarm(pool);
|
|
createTb_Electric_Alarm_History(pool);
|
|
createTb_Electric_Control_Conn(pool);
|
}
|
|
|
|
|
/**
|
* ´´½¨4KWµçÔ´Êý¾Ý¿â
|
* @param pool
|
*/
|
public static void createDB_ElectricSystem(MysqlConnPool pool) {
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
sql.sqlMysqlExecute("CREATE DATABASE IF NOT EXISTS " + Sql_Mysql.DB_ElectricSystem);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
|
|
|
|
/**
|
* ´´½¨µçÔ´ÐÅÏ¢±í
|
* @param pool
|
*/
|
public static void createTb_Electric_inf(MysqlConnPool pool) {
|
String sql_str = "CREATE TABLE IF NOT EXISTS "+Sql_Mysql.Tb_Electric_Inf+" (" +
|
" num bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Ö÷¼ü'," +
|
" electric_id int(11) NOT NULL DEFAULT '3001' COMMENT 'É豸id'," +
|
" electric_name varchar(255) NOT NULL DEFAULT '' COMMENT 'µçÔ´Ãû³Æ'," +
|
" electric_type int(11) NOT NULL DEFAULT '0' COMMENT 'µçÔ´ÀàÐÍ(±¸ÓÃ)'," +
|
" electric_ip varchar(255) NOT NULL DEFAULT '127.0.0.1' COMMENT 'µçÔ´É豸ip(ÖØÒª)'," +
|
" note varchar(255) NOT NULL DEFAULT ''," +
|
" PRIMARY KEY (num)," +
|
" KEY electric_id_key (electric_id)" +
|
") ENGINE=InnoDB AUTO_INCREMENT=1 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_Switch(MysqlConnPool pool) {
|
String sql_str = "CREATE TABLE IF NOT EXISTS " + Sql_Mysql.Tb_Electric_Switch + " (" +
|
" num bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Ö÷¼ü'," +
|
" PLC_ip varchar(255) NOT NULL DEFAULT '127.0.0.1' COMMENT 'PLC_ip', "+
|
" km1_switchon_one int(11) NOT NULL DEFAULT '0' COMMENT 'Ò»ºÅת»»¿ª¹Ø¹ñKM1ºÏÕ¢Ö¸Áî'," +
|
" km1_switchoff_one int(11) NOT NULL DEFAULT '0' COMMENT 'Ò»ºÅת»»¿ª¹Ø¹ñKM1·ÖÕ¢Ö¸Áî'," +
|
" km2_switchon_one int(11) NOT NULL DEFAULT '0' COMMENT 'Ò»ºÅת»»¿ª¹Ø¹ñKM2ºÏÕ¢Ö¸Áî'," +
|
" km2_switchoff_one int(11) NOT NULL DEFAULT '0' COMMENT 'Ò»ºÅת»»¿ª¹Ø¹ñKM2·ÖÕ¢Ö¸Áî'," +
|
" km3_switchon_one int(11) NOT NULL DEFAULT '0' COMMENT 'Ò»ºÅת»»¿ª¹Ø¹ñKM3ºÏÕ¢Ö¸Áî'," +
|
" km3_switchoff_one int(11) NOT NULL DEFAULT '0' COMMENT 'Ò»ºÅת»»¿ª¹Ø¹ñKM3·ÖÕ¢Ö¸Áî'," +
|
" km4_switchon_one int(11) NOT NULL DEFAULT '0' COMMENT 'Ò»ºÅת»»¿ª¹Ø¹ñKM4ºÏÕ¢Ö¸Áî'," +
|
" km4_switchoff_one int(11) NOT NULL DEFAULT '0' COMMENT 'Ò»ºÅת»»¿ª¹Ø¹ñKM4·ÖÕ¢Ö¸Áî'," +
|
" km1_switchon_two int(11) NOT NULL DEFAULT '0' COMMENT '¶þºÅת»»¿ª¹Ø¹ñKM1ºÏÕ¢Ö¸Áî'," +
|
" km1_switchoff_two int(11) NOT NULL DEFAULT '0' COMMENT '¶þºÅת»»¿ª¹Ø¹ñKM1·ÖÕ¢Ö¸Áî'," +
|
" km2_switchon_two int(11) NOT NULL DEFAULT '0' COMMENT '¶þºÅת»»¿ª¹Ø¹ñKM2ºÏÕ¢Ö¸Áî'," +
|
" km2_switchoff_two int(11) NOT NULL DEFAULT '0' COMMENT '¶þºÅת»»¿ª¹Ø¹ñKM2·ÖÕ¢Ö¸Áî'," +
|
" km3_switchon_two int(11) NOT NULL DEFAULT '0' COMMENT '¶þºÅת»»¿ª¹Ø¹ñKM3ºÏÕ¢Ö¸Áî'," +
|
" km3_switchoff_two int(11) NOT NULL DEFAULT '0' COMMENT '¶þºÅת»»¿ª¹Ø¹ñKM3·ÖÕ¢Ö¸Áî'," +
|
" km4_switchon_two int(11) NOT NULL DEFAULT '0' COMMENT '¶þºÅת»»¿ª¹Ø¹ñKM4ºÏÕ¢Ö¸Áî'," +
|
" km4_switchoff_two int(11) NOT NULL DEFAULT '0' COMMENT '¶þºÅת»»¿ª¹Ø¹ñKM4·ÖÕ¢Ö¸Áî'," +
|
" note varchar(255) NOT NULL DEFAULT ''," +
|
" PRIMARY KEY (num),"+
|
" UNIQUE KEY PLC_ip_key (PLC_ip) 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
|
*/
|
public static void createTb_Electric_State(MysqlConnPool pool) {
|
String sql_str = "CREATE TABLE IF NOT EXISTS " + Sql_Mysql.Tb_Electric_State + " (" +
|
" num bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Ö÷¼ü'," +
|
" electric_id int(11) NOT NULL DEFAULT '3001' COMMENT 'É豸id'," +
|
" case_trip_output int(11) NOT NULL DEFAULT '0' COMMENT '»úÏäÌøÕ¢Êä³ö'," +
|
" fault_reset_signal int(11) NOT NULL DEFAULT '0' COMMENT '¹ÊÕϸ´Î»ÐźÅ'," +
|
" high_vol_switch_status int(11) NOT NULL DEFAULT '0' COMMENT '¸ßѹ¿ª¹Ø״̬'," +
|
" stop_button int(11) NOT NULL DEFAULT '0' COMMENT '¼±Í£°´Å¥'," +
|
" main_cabinet_fan_status int(11) NOT NULL DEFAULT '0' COMMENT 'Ö÷¹ñ·ç»ú״̬'," +
|
" trans_cabinet_door_lock int(11) NOT NULL DEFAULT '0' COMMENT '±äѹÆ÷¹ñÃÅÁ¬Ëø'," +
|
" trans_cabinet_overtem_alarm int(11) NOT NULL DEFAULT '0' COMMENT '±äѹÆ÷³¬Î±¨¾¯'," +
|
" trans_cabinet_overtem_trip int(11) NOT NULL DEFAULT '0' COMMENT '±äѹÆ÷³¬ÎÂÌøÕ¢'," +
|
" qs1_state int(11) NOT NULL DEFAULT '0' COMMENT 'QS1״̬'," +
|
" qs2_state int(11) NOT NULL DEFAULT '0' COMMENT 'QS2״̬'," +
|
" switch_cabinet_door_status int(11) NOT NULL DEFAULT '0' COMMENT 'ת»»¿ª¹Ø¹ñÃÅ״̬'," +
|
" alldcvol float NOT NULL DEFAULT '0' COMMENT '×ÜÖ±Á÷µçѹ'," +
|
" alldccurr float NOT NULL DEFAULT '0' COMMENT '×ÜÖ±Á÷µçÁ÷'," +
|
" conn_flag int(11) NOT NULL DEFAULT '0' COMMENT '´®/²¢Áª×´Ì¬'," +
|
" note varchar(255) NOT NULL DEFAULT ''," +
|
" PRIMARY KEY (num)," +
|
" UNIQUE KEY electric_id_key (electric_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
|
*/
|
public static void createTb_Electric_Rt(MysqlConnPool pool) {
|
String sql_str = "CREATE TABLE IF NOT EXISTS " + Sql_Mysql.Tb_Electric_Rt + " (" +
|
" num bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Ö÷¼ü'," +
|
" electric_id int(11) NOT NULL DEFAULT '3001' COMMENT 'É豸id'," +
|
" upsetcurr float NOT NULL DEFAULT '0' COMMENT 'ÉÏλ»úÉ趨µçÁ÷'," +
|
" upsetvol float NOT NULL DEFAULT '0' COMMENT 'ÉÏλ»úÉ趨µçѹ'," +
|
" convolcontrol int(11) NOT NULL DEFAULT '0' COMMENT 'ºãѹ¿ØÖÆ'," +
|
" concurrcontrol int(11) NOT NULL DEFAULT '0' COMMENT 'ºãÁ÷¿ØÖÆ'," +
|
" othermodes int(11) NOT NULL DEFAULT '0' COMMENT 'ÆäËûģʽÔËÐÐ'," +
|
" parallelmodes int(11) NOT NULL DEFAULT '0' COMMENT '²¢ÁªÎÈѹÔËÐÐ'," +
|
" startposition int(11) NOT NULL DEFAULT '0' COMMENT 'Æô¶¯Î»'," +
|
" stopposition int(11) NOT NULL DEFAULT '0' COMMENT 'ֹͣλ'," +
|
" resetposition int(11) NOT NULL DEFAULT '0' COMMENT '¸´Î»'," +
|
" fanstart int(11) NOT NULL DEFAULT '0' COMMENT '·ç»úÆô¶¯'," +
|
" fanstop int(11) NOT NULL DEFAULT '0' COMMENT '·ç»úÍ£Ö¹'," +
|
" dcvol float NOT NULL DEFAULT '0' COMMENT 'Ö±Á÷µçѹ '," +
|
" dccurr float NOT NULL DEFAULT '0' COMMENT 'Ö±Á÷µçÁ÷'," +
|
" controlangle int(11) NOT NULL DEFAULT '0' COMMENT '¿ØÖƽÇ'," +
|
" loopflag int(11) NOT NULL DEFAULT '0' COMMENT '¿ª±Õ»·±ê־루0¿ª»·£¬1±Õ»·£©'," +
|
" remoteflag int(11) NOT NULL DEFAULT '0' COMMENT '¾ÍµØÔ¶³Ì±ê־루0Ô¶³Ì£¬1¾ÍµØ£©'," +
|
" orderflag int(11) NOT NULL DEFAULT '0' COMMENT 'ÕýÐòÄæÐò±ê־루0ÕýÐò£¬1ÄæÐò£©'," +
|
" consvolcurrflag int(11) NOT NULL DEFAULT '0' COMMENT 'ºãѹºãÁ÷±ê־루0ºãѹ£¬1ºãÁ÷£©'," +
|
" powerstartflag int(11) NOT NULL DEFAULT '0' COMMENT 'µçÔ´Æô¶¯±ê־루0Í£Ö¹£¬1Æô¶¯£© '," +
|
" workmodelflag int(11) NOT NULL DEFAULT '0' COMMENT '¹¤×÷ģʽ±ê־루0ÆäËûģʽ£¬1²¢ÁªÎÈѹ£©'," +
|
|
" switchon_1 int(11) NOT NULL DEFAULT '0' COMMENT '1ºÅ¿ª¹Ø¿ª'," +
|
" switchoff_1 int(11) NOT NULL DEFAULT '0' COMMENT '1ºÅ¿ª¹Ø¹Ø'," +
|
" switchon_2 int(11) NOT NULL DEFAULT '0' COMMENT '2ºÅ¿ª¹Ø¿ª '," +
|
" switchoff_2 int(11) NOT NULL DEFAULT '0' COMMENT '2ºÅ¿ª¹Ø¹Ø'," +
|
|
" comm_state int(11) NOT NULL DEFAULT '0' COMMENT 'ͨѶ״̬'," +
|
" screen_reset int(11) NOT NULL DEFAULT '0' COMMENT '´¥ÃþÆÁ¸´Î»'," +
|
" fan_control int(11) NOT NULL DEFAULT '0' COMMENT '·ç»ú¿ØÖÆ'," +
|
" emergency_trip int(11) NOT NULL DEFAULT '0' COMMENT '×ܿعñ½ô¼±ÌøÕ¢'," +
|
|
" note varchar(255) NOT NULL DEFAULT ''," +
|
" PRIMARY KEY (num)," +
|
" UNIQUE KEY electric_id_key (electric_id) USING BTREE " +
|
") ENGINE=InnoDB DEFAULT CHARSET=utf8;";
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
sql.sqlMysqlExecute(sql_str);
|
//Ìí¼Ó comm_state ͨѶ״̬×Ö¶Î
|
ResultSet res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
|
+ " WHERE table_schema='db_electricsystem'"
|
+ " AND table_name='tb_electric_rt'"
|
+ " AND column_name='comm_state'");
|
if(false == res.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.Tb_Electric_Rt
|
+ " ADD COLUMN comm_state int(11) NOT NULL DEFAULT '0' COMMENT 'ͨѶ״̬' after workmodelflag;");
|
}
|
//Ìí¼Ó screen_reset ´¥ÃþÆÁ¸´Î»
|
res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
|
+ " WHERE table_schema='db_electricsystem'"
|
+ " AND table_name='tb_electric_rt'"
|
+ " AND column_name='screen_reset'");
|
if(false == res.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.Tb_Electric_Rt
|
+ " ADD COLUMN screen_reset int(11) NOT NULL DEFAULT '0' COMMENT 'ͨѶ״̬' after comm_state;");
|
}
|
//Ìí¼Ó fan_control ·ç»ú¿ØÖÆ
|
res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
|
+ " WHERE table_schema='db_electricsystem'"
|
+ " AND table_name='tb_electric_rt'"
|
+ " AND column_name='fan_control'");
|
if(false == res.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.Tb_Electric_Rt
|
+ " ADD COLUMN fan_control int(11) NOT NULL DEFAULT '0' COMMENT 'ͨѶ״̬' after screen_reset;");
|
}
|
//Ìí¼Ó emergency_trip ×ܿعñ½ô¼±ÌøÕ¢
|
res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
|
+ " WHERE table_schema='db_electricsystem'"
|
+ " AND table_name='tb_electric_rt'"
|
+ " AND column_name='emergency_trip'");
|
if(false == res.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.Tb_Electric_Rt
|
+ " ADD COLUMN emergency_trip int(11) NOT NULL DEFAULT '0' COMMENT 'ͨѶ״̬' after fan_control;");
|
}
|
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
|
/**
|
* ´´½¨4Ì×µçԴ״̬±í
|
* @param pool
|
*/
|
public static void createTb_Electric_Power(MysqlConnPool pool) {
|
String sql_str = " CREATE TABLE IF NOT EXISTS " + Sql_Mysql.Tb_Electric_Power + " (" +
|
" num bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Ö÷¼ü'," +
|
" PLC_ip varchar(255) NOT NULL DEFAULT '127.0.0.1' COMMENT 'PLC_ip', "+
|
" start_a int(11) NOT NULL DEFAULT '0' COMMENT 'AÌ×Æô¶¯'," +
|
" stop_a int(11) NOT NULL DEFAULT '0' COMMENT 'AÌ×Í£Ö¹'," +
|
" start_b int(11) NOT NULL DEFAULT '0' COMMENT 'BÌ×Æô¶¯'," +
|
" stop_b int(11) NOT NULL DEFAULT '0' COMMENT 'BÌ×Í£Ö¹'," +
|
" start_c int(11) NOT NULL DEFAULT '0' COMMENT 'CÌ×Æô¶¯'," +
|
" stop_c int(11) NOT NULL DEFAULT '0' COMMENT 'CÌ×Í£Ö¹'," +
|
" start_d int(11) NOT NULL DEFAULT '0' COMMENT 'DÌ×Æô¶¯'," +
|
" stop_d int(11) NOT NULL DEFAULT '0' COMMENT 'DÌ×Í£Ö¹'," +
|
" abconn_start int(11) NOT NULL DEFAULT '0' COMMENT 'AÌ×BÌײ¢ÁªÔËÐÐÆô¶¯'," +
|
" abconn_stop int(11) NOT NULL DEFAULT '0' COMMENT 'AÌ×BÌײ¢ÁªÔËÐÐÍ£Ö¹'," +
|
" cdconn_start int(11) NOT NULL DEFAULT '0' COMMENT 'CÌ×DÌײ¢ÁªÔËÐÐÆô¶¯'," +
|
" cdconn_stop int(11) NOT NULL DEFAULT '0' COMMENT 'CÌ×DÌײ¢ÁªÔËÐÐÍ£Ö¹'," +
|
" allconn_start int(11) NOT NULL DEFAULT '0' COMMENT 'ËÄÌײ¢ÁªÔËÐÐÆô¶¯'," +
|
" allconn_stop int(11) NOT NULL DEFAULT '0' COMMENT 'ËÄÌײ¢ÁªÔËÐÐÍ£Ö¹'," +
|
" abconn_flag int(11) NOT NULL DEFAULT '0' COMMENT 'AB²¢´®Áª±ê־λ'," +
|
" cdconn_flag int(11) NOT NULL DEFAULT '0' COMMENT 'CD²¢´®Áª±ê־λ'," +
|
" abconn_volset int(11) NOT NULL DEFAULT '0' COMMENT 'AB²¢Áªµçѹ¸ø¶¨'," +
|
" abconn_currset int(11) NOT NULL DEFAULT '0' COMMENT 'AB²¢ÁªµçÁ÷¸ø¶¨'," +
|
" cdconn_volset int(11) NOT NULL DEFAULT '0' COMMENT 'CD²¢Áªµçѹ¸ø¶¨'," +
|
" cdconn_currset int(11) NOT NULL DEFAULT '0' COMMENT 'CD²¢ÁªµçÁ÷¸ø¶¨'," +
|
" note varchar(255) NOT NULL DEFAULT ''," +
|
" PRIMARY KEY (num)," +
|
" UNIQUE KEY PLC_ip_key (PLC_ip) 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
|
*/
|
public static void createTb_Electric_Control_Single(MysqlConnPool pool) {
|
String sql_str = " CREATE TABLE IF NOT EXISTS " + Sql_Mysql.Tb_Electric_Control_Single + " (" +
|
" num bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Ö÷¼ü'," +
|
" electric_id int(11) NOT NULL DEFAULT '3001' COMMENT 'µçÔ´id'," +
|
" upsetcurr float NOT NULL DEFAULT '0' COMMENT 'ÉÏλ»úÉ趨µçÁ÷'," +
|
" upsetvol float NOT NULL DEFAULT '0' COMMENT 'ÉÏλ»úÉ趨µçѹ'," +
|
" convolcontrol int(11) NOT NULL DEFAULT '0' COMMENT 'ºãѹ¿ØÖÆ'," +
|
" concurrcontrol int(11) NOT NULL DEFAULT '0' COMMENT 'ºãÁ÷¿ØÖÆ'," +
|
" othermodes int(11) NOT NULL DEFAULT '0' COMMENT 'ÆäËûģʽÔËÐÐ'," +
|
" parallelmodes int(11) NOT NULL DEFAULT '0' COMMENT '²¢ÁªÎÈѹÔËÐÐ'," +
|
" startposition int(11) NOT NULL DEFAULT '0' COMMENT 'Æô¶¯Î»'," +
|
" stopposition int(11) NOT NULL DEFAULT '0' COMMENT 'ֹͣλ'," +
|
" resetposition int(11) NOT NULL DEFAULT '0' COMMENT '¸´Î»'," +
|
" fanstart int(11) NOT NULL DEFAULT '0' COMMENT '·ç»úÆô¶¯'," +
|
" fanstop int(11) NOT NULL DEFAULT '0' COMMENT '·ç»úÍ£Ö¹'," +
|
" dcvol float NOT NULL DEFAULT '0' COMMENT 'Ö±Á÷µçѹ'," +
|
" dccurr float NOT NULL DEFAULT '0' COMMENT 'Ö±Á÷µçÁ÷'," +
|
" controlangle float NOT NULL DEFAULT '0' COMMENT '¿ØÖƽÇ'," +
|
" switchon int(11) NOT NULL DEFAULT '0' COMMENT '¿ª¹ØºÏÕ¢'," +
|
" switchoff int(11) NOT NULL DEFAULT '0' COMMENT '¿ª¹Ø·ÖÕ¢'," +
|
" PRIMARY KEY (num),"+
|
" UNIQUE KEY electric_id_key (electric_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
|
*/
|
public static void createTb_Electric_Control_Conn(MysqlConnPool pool) {
|
String sql_str = " CREATE TABLE IF NOT EXISTS " + Sql_Mysql.Tb_Electric_Control_Conn + " (" +
|
" num bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Ö÷¼ü'," +
|
" electric_id int NOT NULL DEFAULT '4001' COMMENT '×éºÏ½çÃæÖ÷Òªid'," +
|
" conn_name VARCHAR(225) NOT NULL DEFAULT 'AB' COMMENT '×éºÏÃû³Æ'," +
|
" convolcontrol int(11) NOT NULL DEFAULT '0' COMMENT 'ºãѹ¿ØÖÆ'," +
|
" concurrcontrol int(11) NOT NULL DEFAULT '0' COMMENT 'ºãÁ÷¿ØÖÆ'," +
|
" conn_flag int(11) NOT NULL DEFAULT '0' COMMENT '²¢ÁªÄ£Ê½'," +
|
" divide_flag int(11) NOT NULL DEFAULT '0' COMMENT '´®ÁªÄ£Ê½'," +
|
" volset int(11) NOT NULL DEFAULT '0' COMMENT 'µçѹ¸ø¶¨'," +
|
" currset int(11) NOT NULL DEFAULT '0' COMMENT 'µçÁ÷¸ø¶¨'," +
|
" givevol float NOT NULL DEFAULT '0' COMMENT '¸ø¶¨µçѹ'," +
|
" givecurr float NOT NULL DEFAULT '0' COMMENT '¸ø¶¨µçÁ÷'," +
|
" switchon1 int(11) NOT NULL DEFAULT '0' COMMENT 'Êä³öºÏÕ¢1'," +
|
" switchon2 int(11) NOT NULL DEFAULT '0' COMMENT 'Êä³öºÏÕ¢2'," +
|
" switchoff1 int(11) NOT NULL DEFAULT '0' COMMENT 'Êä³ö·ÖÕ¢1 '," +
|
" switchoff2 int(11) NOT NULL DEFAULT '0' COMMENT 'Êä³ö·ÖÕ¢2'," +
|
" conn_start int(11) NOT NULL DEFAULT '0' COMMENT '²¢ÁªÔËÐÐÆô¶¯'," +
|
" conn_stop int(11) NOT NULL DEFAULT '0' COMMENT '²¢ÁªÔËÐÐÍ£Ö¹'," +
|
" PRIMARY KEY (num),"+
|
" UNIQUE KEY electric_id_key (electric_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
|
*/
|
public static void createTb_Electric_Alarm(MysqlConnPool pool) {
|
String sql_str = " CREATE TABLE IF NOT EXISTS " + Sql_Mysql.Tb_Electric_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_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();
|
}
|
}
|
//²åÈë»ò¸üеçÔ´µ¥µã¿ØÖƱí
|
public static void replace_Electric_Control_Conn(MysqlConnPool pool,Electric_control_conn econn) {
|
if(econn.electric_id==4001) {
|
econn.conn_name="AB×éºÏ";
|
}else {
|
econn.conn_name="CD×éºÏ";
|
}
|
String sql_str = " replace into "+ Sql_Mysql.Tb_Electric_Control_Conn+"(electric_id,conn_name,convolcontrol,concurrcontrol,conn_flag,volset,currset,givevol,givecurr,switchon1,switchon2,switchoff1,switchoff2,conn_start,conn_stop) "
|
+ " values("+econn.electric_id+",'"+econn.conn_name+"',"+econn.convolcontrol+","+econn.concurrcontrol+","+econn.conn_flag+","+econn.volset+","+econn.currset+","+econn.givevol+","+econn.givecurr+","+econn.switchon1+","+econn.switchon2+","+econn.switchoff1+","+econn.switchoff2+","+econn.conn_start+","+econn.conn_stop+")";
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
//¸üеçÔ´µ¥µã¿ØÖƱí
|
public static void update_Electric_Control_Conn(MysqlConnPool pool,Electric_control_conn econn) {
|
if(econn.electric_id==4001) {
|
econn.conn_name="AB×éºÏ";
|
}else {
|
econn.conn_name="CD×éºÏ";
|
}
|
String sql_str = " UPDATE "+ Sql_Mysql.Tb_Electric_Control_Conn+" "
|
+ " Set conn_name = '"+econn.conn_name+"',"
|
+ " convolcontrol = "+econn.convolcontrol+","
|
+ " concurrcontrol = "+econn.concurrcontrol+","
|
+ " conn_flag = "+econn.conn_flag+","
|
+ " divide_flag = "+econn.divide_flag+","
|
+ " volset= "+econn.volset+","
|
+ " currset = "+econn.currset+","
|
+ " givevol = "+econn.givevol+","
|
+ " givecurr = "+econn.givecurr+","
|
+ " switchon1 = "+econn.switchon1+","
|
+ " switchon2 = "+econn.switchon2+","
|
+ " switchoff1 = "+econn.switchoff1+","
|
+ " switchoff2 = "+econn.switchoff2+","
|
+ " conn_start = "+econn.conn_start+","
|
+ " conn_stop = "+econn.conn_stop
|
+ " WHERE electric_id = " + econn.electric_id;
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
//²åÈë»ò¸üеçԴʵʱÐÅÏ¢±í
|
public static void replace_Electric_Rt(MysqlConnPool pool,Electric_Rt ert) {
|
String sql_str = " replace into "+ Sql_Mysql.Tb_Electric_Rt +"(electric_id,upsetcurr,upsetvol,convolcontrol,concurrcontrol,othermodes,parallelmodes,startposition,stopposition,resetposition,fanstart,fanstop,dcvol,dccurr,controlangle,loopflag,remoteflag,orderflag,consvolcurrflag,powerstartflag,workmodelflag,switchon_1,switchoff_1,switchon_2,switchoff_2,comm_state,screen_reset,fan_control,emergency_trip) "
|
+ " values("+ert.electric_id+","+ert.upsetcurr+","+ert.upsetvol+","+ert.convolcontrol+","+ert.concurrcontrol+","+ert.othermodes+","+ert.parallelmodes+","+ert.startposition+","+ert.stopposition+","+ert.resetposition+","+ert.fanstart+","+ert.fanstop+","+ert.dcvol+","+ert.dccurr+","+ert.controlangle+","+ert.loopflag+","+ert.remoteflag+","+ert.orderflag+","+ert.consvolcurrflag+","+ert.powerstartflag+","+ert.workmodelflag+","+ert.switchon_1+","+ert.switchoff_1+","+ert.switchon_2+","+ert.switchoff_2+","+ert.comm_state+","+ert.screen_reset+","+ert.fan_control+","+ert.emergency_trip+")";
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
//¸üеçԴʵʱÐÅÏ¢±í
|
public static void update_Electric_Rt(MysqlConnPool pool,Electric_Rt ert) {
|
String sql_str = " UPDATE "+ Sql_Mysql.Tb_Electric_Rt +""
|
+ " SET upsetcurr = "+ert.upsetcurr+","
|
+ " upsetvol = "+ert.upsetvol+","
|
+ " convolcontrol = "+ert.convolcontrol+","
|
+ " concurrcontrol = "+ert.concurrcontrol+","
|
+ " othermodes = "+ert.othermodes+","
|
+ " parallelmodes = "+ert.parallelmodes+","
|
+ " startposition = "+ert.startposition+","
|
+ " stopposition = "+ert.stopposition+","
|
+ " resetposition = "+ert.resetposition+","
|
+ " fanstart = "+ert.fanstart+","
|
+ " fanstop = "+ert.fanstop+","
|
+ " dcvol = "+ert.dcvol+","
|
+ " dccurr = "+ert.dccurr+","
|
+ " controlangle = "+ert.controlangle+","
|
+ " loopflag = "+ert.loopflag+","
|
+ " remoteflag = "+ert.remoteflag+","
|
+ " orderflag = "+ert.orderflag+","
|
+ " consvolcurrflag = "+ert.consvolcurrflag+","
|
+ " powerstartflag = "+ert.powerstartflag+","
|
+ " workmodelflag = "+ert.workmodelflag+","
|
+ " switchon_1 = "+ert.switchon_1+","
|
+ " switchoff_1 = "+ert.switchoff_1+","
|
+ " switchon_2 = "+ert.switchon_2+","
|
+ " switchoff_2 = "+ert.switchoff_2+","
|
+ " comm_state = "+ert.comm_state+","
|
+ " screen_reset = "+ert.screen_reset+","
|
+ " fan_control = "+ert.fan_control+","
|
+ " emergency_trip="+ert.emergency_trip+" "
|
+ " WHERE electric_id = " + ert.electric_id;
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
//²åÈë»ò¸üеçԴʵʱ״̬±í
|
public static void replace_Electric_State(MysqlConnPool pool,Electric_State estate) {
|
String sql_str = " replace into "+ Sql_Mysql.Tb_Electric_State +"(electric_id,case_trip_output,fault_reset_signal,high_vol_switch_status,stop_button,main_cabinet_fan_status,trans_cabinet_door_lock,trans_cabinet_overtem_alarm,trans_cabinet_overtem_trip,qs1_state,qs2_state,switch_cabinet_door_status,alldcvol,alldccurr,conn_flag) "
|
+ " values("+estate.electric_id+","+estate.case_trip_output+","+estate.fault_reset_signal+","+estate.high_vol_switch_status+","+estate.stop_button+","+estate.main_cabinet_fan_status+","+estate.trans_cabinet_door_lock+","+estate.trans_cabinet_overtem_alarm+","+estate.trans_cabinet_overtem_trip+","+estate.qs1_state+","+estate.qs2_state+","+estate.switch_cabinet_door_status+","+estate.alldcvol+","+estate.alldccurr+","+estate.conn_flag+")";
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
//¸üеçԴʵʱ״̬±í
|
public static void update_Electric_State(MysqlConnPool pool,Electric_State estate) {
|
String sql_str = " update "+ Sql_Mysql.Tb_Electric_State +" "
|
+ " SET case_trip_output = "+estate.case_trip_output+","
|
+ " fault_reset_signal = "+estate.fault_reset_signal+","
|
+ " high_vol_switch_status = "+estate.high_vol_switch_status+","
|
+ " stop_button = "+estate.stop_button+","
|
+ " main_cabinet_fan_status = "+estate.main_cabinet_fan_status+","
|
+ " trans_cabinet_door_lock = "+estate.trans_cabinet_door_lock+","
|
+ " trans_cabinet_overtem_alarm = "+estate.trans_cabinet_overtem_alarm+","
|
+ " trans_cabinet_overtem_trip = "+estate.trans_cabinet_overtem_trip+","
|
+ " qs1_state = "+estate.qs1_state+","
|
+ " qs2_state = "+estate.qs2_state+","
|
+ " switch_cabinet_door_status = "+estate.switch_cabinet_door_status+","
|
+ " alldcvol = "+estate.alldcvol+","
|
+ " alldccurr = "+estate.alldccurr+","
|
+ " conn_flag = "+estate.conn_flag+" "
|
+ " WHERE electric_id ="+estate.electric_id;
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
//²åÈë»ò¸üеçÔ´×Ü¿ª¹Ø״̬±í
|
public static void replace_Electric_Switch(MysqlConnPool pool,Electric_Switch eswitch) {
|
String sql_str = " replace into "+ Sql_Mysql.Tb_Electric_Switch +"(PLC_ip,km1_switchon_one,km1_switchoff_one,km2_switchon_one,km2_switchoff_one,km3_switchon_one,km3_switchoff_one,km4_switchon_one,km4_switchoff_one,km1_switchon_two,km1_switchoff_two,km2_switchon_two,km2_switchoff_two,km3_switchon_two,km3_switchoff_two,km4_switchon_two,km4_switchoff_two) "
|
+ " values('"+eswitch.PLC_ip+"',"+eswitch.km1_switchon_one+","+eswitch.km1_switchoff_one+","+eswitch.km2_switchon_one+","+eswitch.km2_switchoff_one+","+eswitch.km3_switchon_one+","+eswitch.km3_switchoff_one+","+eswitch.km4_switchon_one+","+eswitch.km4_switchoff_one+","+eswitch.km1_switchon_two+","+eswitch.km1_switchoff_two+","+eswitch.km2_switchon_two+","+eswitch.km2_switchoff_two+","+eswitch.km3_switchon_two+","+eswitch.km3_switchoff_two+","+eswitch.km4_switchon_two+","+eswitch.km4_switchoff_two+")";
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
|
//²åÈë»ò¸üÐÂ4Ì×µçԴ״̬±í
|
public static void replace_Electric_Power(MysqlConnPool pool,Electric_Power epower) {
|
String sql_str = " replace into "+ Sql_Mysql.Tb_Electric_Power +"(PLC_ip,start_a,stop_a,start_b,stop_b,start_c,stop_c,start_d,stop_d,abconn_start,abconn_stop,cdconn_start,cdconn_stop,allconn_start,allconn_stop,abconn_flag,cdconn_flag,abconn_volset,abconn_currset,cdconn_volset,cdconn_currset) "
|
+ " values('"+epower.PLC_ip+"',"+epower.start_a+","+epower.stop_a+","+epower.start_b+","+epower.stop_b+","+epower.start_c+","+epower.stop_c+","+epower.start_d+","+epower.stop_d+","+epower.abconn_start+","+epower.abconn_stop+","+epower.cdconn_start+","+epower.cdconn_stop
|
+","+epower.allconn_start+","+epower.allconn_stop+","+epower.abconn_flag+","+epower.cdconn_flag+","+epower.abconn_volset+","+epower.abconn_currset+","+epower.cdconn_volset+","+epower.abconn_currset+")";
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
//²éѯµçÔ´µ¥µã¿ØÖƱíÖÐÊý¾Ý
|
public static void queryElectric_Control_Single(MysqlConnPool conn_pool,Electric_Control_Single ecs) {
|
String sql_str = " select * from " + Sql_Mysql.Tb_Electric_Control_Single+" Where electric_id = " +ecs.getElectric_id();
|
ResultSet res = null;
|
Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
|
try {
|
res = sql.sqlMysqlQuery(sql_str);
|
if(res.next()) {
|
ecs.setElectric_id(res.getInt("electric_id"));
|
ecs.setUpsetcurr(res.getFloat("upsetcurr"));
|
ecs.setUpsetvol(res.getFloat("upsetvol"));
|
ecs.setConvolcontrol(res.getInt("convolcontrol"));
|
ecs.setConcurrcontrol(res.getInt("concurrcontrol"));
|
//ecs.setOthermodes(res.getInt("othermodes"));
|
//ecs.setParallelmodes(res.getInt("parallelmodes"));
|
ecs.setStartposition(res.getInt("startposition"));
|
ecs.setStopposition(res.getInt("stopposition"));
|
ecs.setResetposition(res.getInt("resetposition"));
|
//ecs.setFanstart(res.getInt("fanstart"));
|
//ecs.setFanstop(res.getInt("fanstop"));
|
ecs.setDcvol(res.getFloat("dcvol"));
|
ecs.setDccurr(res.getFloat("dccurr"));
|
ecs.setControlangle(res.getFloat("controlangle"));
|
ecs.setSwitchon(res.getInt("switchon"));
|
ecs.setSwitchoff(res.getInt("switchoff"));
|
//System.out.println(ecs.toString());
|
}
|
} catch (Exception e) {
|
e.printStackTrace();
|
} finally {
|
if(res != null) {
|
try {
|
res.close();
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
}
|
sql.close_con();
|
}
|
}
|
//²éѯµçÔ´×éºÏ¿ØÖƱíÖÐÊý¾Ý
|
public static void queryElectric_Control_Conn(MysqlConnPool conn_pool,Electric_control_conn econn) {
|
String sql_str = " select * from " + Sql_Mysql.Tb_Electric_Control_Conn+" Where electric_id = " +econn.getElectric_id();
|
ResultSet res = null;
|
Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
|
try {
|
res = sql.sqlMysqlQuery(sql_str);
|
if(res.next()) {
|
econn.setElectric_id(res.getInt("electric_id"));
|
econn.setConn_name(res.getString("conn_name"));
|
econn.setConvolcontrol(res.getInt("convolcontrol"));
|
econn.setConcurrcontrol(res.getInt("concurrcontrol"));
|
econn.setConn_flag(res.getInt("conn_flag"));
|
econn.setDivide_flag(res.getInt("divide_flag"));
|
econn.setVolset(res.getInt("volset"));
|
econn.setCurrset(res.getInt("currset"));
|
econn.setGivecurr(res.getFloat("givecurr"));
|
econn.setGivevol(res.getFloat("givevol"));
|
econn.setSwitchon1(res.getInt("switchon1"));
|
econn.setSwitchon2(res.getInt("switchon2"));
|
econn.setSwitchoff1(res.getInt("switchoff1"));
|
econn.setSwitchoff2(res.getInt("switchoff2"));
|
econn.setConn_start(res.getInt("conn_start"));
|
econn.setConn_stop(res.getInt("conn_stop"));
|
}
|
} catch (Exception e) {
|
e.printStackTrace();
|
} finally {
|
if(res != null) {
|
try {
|
res.close();
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
}
|
sql.close_con();
|
}
|
}
|
/**
|
* ²åÈë»ò¸üеçÔ´¹ÊÕÏ
|
* @param conn_pool
|
* @param control
|
*/
|
public static void insertOrUpdateElectrical_alarmTable(MysqlConnPool conn_pool,List<Electric_alarm> list) {
|
if(list!=null&&list.size()>0) {
|
for (Electric_alarm ealarm : list) {
|
String sql_str_sel = " select * from " + Sql_Mysql.Tb_Electric_alarm + " where electric_id = " + ealarm.getElectric_id()+" and alarm_id="+ealarm.getAlarm_id();
|
String sql_str_ins = " INSERT INTO " + Sql_Mysql.Tb_Electric_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_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"+ealarm.getAlarm_confirmtime()+
|
" 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();
|
}
|
}
|
|
}
|
|
}
|
//²åÈë»ò¸üеçÔ´µ¥µã¿ØÖƱí
|
public static void replace_Electric_Control_Single(MysqlConnPool pool,Electric_Control_Single ecs) {
|
String sql_str = " replace into "+ Sql_Mysql.Tb_Electric_Control_Single +"(electric_id,upsetcurr,upsetvol,convolcontrol,concurrcontrol,othermodes,parallelmodes,startposition,stopposition,resetposition,fanstart,fanstop,dcvol,dccurr,controlangle) "
|
+ " values("+ecs.electric_id+","+ecs.upsetcurr+","+ecs.upsetvol+","+ecs.convolcontrol+","+ecs.concurrcontrol+","+ecs.othermodes+","+ecs.parallelmodes+","+ecs.startposition+","+ecs.stopposition+","+ecs.resetposition+","+ecs.fanstart+","+ecs.fanstop+","+ecs.dcvol+","+ecs.dccurr+","+ecs.controlangle+")";
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
//¸üеçÔ´µ¥µã¿ØÖƱí
|
public static void update_Electric_Control_Single(MysqlConnPool pool,Electric_Control_Single ecs) {
|
//System.out.println(ecs.toString());
|
String sql_str = " UPDATE "+ Sql_Mysql.Tb_Electric_Control_Single
|
+ " set upsetcurr = "+ecs.upsetcurr+","
|
+ " upsetvol = "+ecs.upsetvol+","
|
+ " convolcontrol = "+ecs.convolcontrol+","
|
+ " concurrcontrol= "+ecs.concurrcontrol+","
|
+ " othermodes = "+ecs.othermodes+","
|
+ " parallelmodes ="+ecs.parallelmodes+","
|
+ " startposition ="+ecs.startposition+","
|
+ " stopposition ="+ecs.stopposition+","
|
+ " resetposition ="+ecs.resetposition+","
|
+ " fanstart = "+ecs.fanstart+","
|
+ " fanstop = "+ecs.fanstop+","
|
+ " dcvol = "+ecs.dcvol+","
|
+ " dccurr = "+ecs.dccurr+","
|
+ " switchon = "+ecs.switchon+","
|
+ " switchoff = "+ecs.switchoff+","
|
+ " controlangle="+ecs.controlangle+" "
|
+ " WHERE electric_id = " + ecs.electric_id;
|
//System.out.println(sql_str);
|
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_Electric_Readhist_DataTable(MysqlConnPool pool,int electric_id,Date time) {
|
String sql_str = "CREATE TABLE IF NOT EXISTS " + Sql_Mysql.Tb_Electric_Readhist_Data + electric_id+"_"+Com.getDateTimeFormat(time, Com.DTF_Y_M_D) + " (" +
|
" num bigint(20) NOT NULL AUTO_INCREMENT," +
|
" electric_id int(11) NOT NULL DEFAULT '0' COMMENT 'µçÔ´id'," +
|
" record_time datetime NOT NULL DEFAULT '2000-01-01 00:00:00' COMMENT '¼Ç¼ʱ¼ä'," +
|
" dcvol float NOT NULL DEFAULT '0' COMMENT 'Ö±Á÷µçѹ'," +
|
" dccurr float NOT NULL DEFAULT '0' COMMENT 'Ö±Á÷µçÁ÷'," +
|
" controlangle int(11) NOT NULL DEFAULT '0' COMMENT '¿ØÖƽÇ'," +
|
" note varchar(255) NOT NULL DEFAULT '' COMMENT '±¸ÓÃ'," +
|
" PRIMARY KEY (num)" +
|
") ENGINE=InnoDB AUTO_INCREMENT=1 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();
|
}
|
}
|
|
/**
|
* ¶ÁÈ¡µ±Ç°É豸µÄ״̬ÐÅÏ¢
|
*/
|
public static void queryElectric_RtById(Electric_Rt rt,MysqlConnPool pool) {
|
String sql_str = " SELECT * FROM db_electricsystem.tb_electric_rt WHERE electric_id = " + rt.electric_id;
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
ResultSet res = null;
|
try {
|
res = sql.sqlMysqlQuery(sql_str);
|
while(res.next()) {
|
rt.upsetcurr = res.getFloat("upsetcurr"); //ÉÏλ»úÉ趨µçÁ÷
|
rt.upsetvol = res.getFloat("upsetvol"); //ÉÏλ»úÉ趨µçѹ
|
rt.convolcontrol = res.getInt("convolcontrol"); //ºãѹ¿ØÖÆ
|
rt.concurrcontrol = res.getInt("concurrcontrol"); //ºãÁ÷¿ØÖÆ
|
rt.othermodes = res.getInt("othermodes"); //ÆäËûģʽÔËÐÐ
|
rt.parallelmodes = res.getInt("parallelmodes"); //²¢ÁªÎÈѹÔËÐÐ
|
rt.startposition = res.getInt("startposition"); //Æô¶¯Î»
|
rt.stopposition = res.getInt("stopposition"); //ֹͣλ
|
rt.resetposition = res.getInt("resetposition"); //¸´Î»
|
rt.fanstart = res.getInt("fanstart"); //·ç»úÆô¶¯
|
rt.fanstop = res.getInt("fanstop"); //·ç»úÍ£Ö¹
|
rt.dcvol = res.getFloat("dcvol"); //Ö±Á÷µçѹ
|
rt.dccurr = res.getFloat("dccurr"); //Ö±Á÷µçÁ÷
|
rt.controlangle = res.getInt("controlangle"); //¿ØÖƽÇ
|
rt.loopflag = res.getInt("loopflag"); //¿ª±Õ»·±ê־λ¡¾0¿ª»·£»1±Õ»·¡¿
|
rt.remoteflag = res.getInt("remoteflag"); //¾ÍµØÔ¶³Ì±ê־λ¡¾0Ô¶³Ì£»1¾ÍµØ¡¿
|
rt.orderflag = res.getInt("orderflag"); //ÕýÐòÄæÐò±ê־λ¡¾0ÕýÐò£»1ÄæÐò¡¿
|
rt.consvolcurrflag = res.getInt("consvolcurrflag"); //ºãѹºáÁ÷±ê־λ¡¾0ºãѹ£»1ºãÁ÷¡¿
|
rt.powerstartflag = res.getInt("powerstartflag"); //µçÔ´Æô¶¯±ê־λ¡¾0Í£Ö¹£»1Æô¶¯¡¿
|
rt.workmodelflag = res.getInt("workmodelflag"); //¹¤×÷ģʽ±ê־λ¡¾0ÆäËûģʽ£»1²¢ÁªÎÈѹ¡¿
|
rt.switchon_1 = res.getInt("switchon_1"); //¿ª¹Ø1
|
rt.switchoff_1 = res.getInt("switchoff_1"); //¿ª¹Ø1
|
rt.switchon_2 = res.getInt("switchon_2"); //¿ª¹Ø2
|
rt.switchoff_2 = res.getInt("switchoff_2"); //¿ª¹Ø2
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
if(res != null) {
|
try {
|
res.close();
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
sql.close_con();
|
}
|
}
|
}
|
|
/**
|
* ¼Ç¼ÀúʷʵʱÊý¾Ý
|
* @param pool
|
* @param rt
|
*/
|
public static void recordHistoryData(MysqlConnPool pool, Electric_Rt rt) {
|
String sql_str = " INSERT INTO " + Sql_Mysql.Tb_Electric_Readhist_Data + rt.electric_id + "(electric_id,record_time,dcvol,dccurr,controlangle) VALUES("+rt.electric_id+",'"+Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms)+"',"+rt.dcvol+","+rt.dccurr+","+rt.controlangle+");";
|
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 rt
|
*/
|
public static void recordHistoryData(MysqlConnPool pool, Electric_Rt rt,Date time) {
|
String sql_str = " INSERT INTO " + Sql_Mysql.Tb_Electric_Readhist_Data + rt.electric_id+ "_" + Com.getDateTimeFormat(time, Com.DTF_Y_M_D) + "(electric_id,record_time,dcvol,dccurr,controlangle) VALUES("+rt.electric_id+",'"+Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms)+"',"+rt.dcvol+","+rt.dccurr+","+rt.controlangle+");";
|
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();
|
}
|
}
|
}
|
|
/**
|
* ɾ³ý´´½¨Ê±¼ä³¬Ê±µÄÀúʷʵʱ¼Ç¼±í
|
* @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_electricsystem' " +
|
" AND TABLE_NAME like 'tb_electric_realhist_data_%' " +
|
" 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_electricsystem." + 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();
|
}
|
}
|
}
|
