package com.dev.base.data;
|
|
import java.sql.ResultSet;
|
import java.sql.SQLException;
|
import java.util.Date;
|
|
import com.base.Com;
|
import com.sql.MysqlConnPool;
|
import com.sql.Sql_Mysql;
|
|
public class Dynamicload_Task_SQL {
|
|
/**
|
* ´´½¨Ö¸¶¨Ê±¼äµÄÀúʷʵʱÊý¾Ý¿â±í
|
* @param pool
|
* @param dev_id
|
* @param now
|
*/
|
public static void createTb_Dynamicload_realdata(MysqlConnPool pool, int dev_id, Date now) {
|
String sql_str = "CREATE TABLE IF NOT EXISTS "+Sql_Mysql.Tb_Dynamicload_RealData+dev_id+"_"+Com.getDateTimeFormat(now, Com.DTF_Y_M_D)+" (" +
|
" num int(11) NOT NULL AUTO_INCREMENT COMMENT 'Ö÷¼ü'," +
|
" dev_id int(11) NOT NULL DEFAULT '1' COMMENT 'É豸id'," +
|
" record_time datetime NOT NULL DEFAULT '2000-01-01 00:00:00'," +
|
" FZ_b1 int(11) NOT NULL DEFAULT '1' COMMENT 'FZ°´Å¥1'," +
|
" FZ_b2 int(11) NOT NULL DEFAULT '1' COMMENT 'FZ°´Å¥2'," +
|
" FZ_b3 int(11) NOT NULL DEFAULT '1' COMMENT 'FZ°´Å¥3'," +
|
" FZ_b4 int(11) NOT NULL DEFAULT '1' COMMENT 'FZ°´Å¥4'," +
|
" FZ_b5 int(11) NOT NULL DEFAULT '1' COMMENT 'FZ°´Å¥5'," +
|
" FZ_b6 int(11) NOT NULL DEFAULT '1' COMMENT 'FZ°´Å¥6'," +
|
" FZ_b7 int(11) NOT NULL DEFAULT '1' COMMENT 'FZ°´Å¥7'," +
|
" FZ_b8 int(11) NOT NULL DEFAULT '1' COMMENT 'FZ°´Å¥8'," +
|
" FZ_b9 int(11) NOT NULL DEFAULT '1' COMMENT 'FZ°´Å¥9'," +
|
" FZ_b10 int(11) NOT NULL DEFAULT '1' COMMENT 'FZ°´Å¥10'," +
|
" FZ_b11 int(11) NOT NULL DEFAULT '1' COMMENT 'FZ°´Å¥11'," +
|
" FZ_fw1 int(11) NOT NULL DEFAULT '1' COMMENT 'FZ·´À¡°´Å¥1'," +
|
" FZ_fw2 int(11) NOT NULL DEFAULT '1' COMMENT 'FZ·´À¡°´Å¥2'," +
|
" FZ_fw3 int(11) NOT NULL DEFAULT '1' COMMENT 'FZ·´À¡°´Å¥3'," +
|
" FZ_fw4 int(11) NOT NULL DEFAULT '1' COMMENT 'FZ·´À¡°´Å¥4'," +
|
" FZ_fw5 int(11) NOT NULL DEFAULT '1' COMMENT 'FZ·´À¡°´Å¥5'," +
|
" FZ_fw6 int(11) NOT NULL DEFAULT '1' COMMENT 'FZ·´À¡°´Å¥6'," +
|
" FZ_fw7 int(11) NOT NULL DEFAULT '1' COMMENT 'FZ·´À¡°´Å¥7'," +
|
" FZ_fw8 int(11) NOT NULL DEFAULT '1' COMMENT 'FZ·´À¡°´Å¥8'," +
|
" FZ_fw9 int(11) NOT NULL DEFAULT '1' COMMENT 'FZ·´À¡°´Å¥9'," +
|
" FZ_fw10 int(11) NOT NULL DEFAULT '1' COMMENT 'FZ·´À¡°´Å¥10'," +
|
" FZ_fw11 int(11) NOT NULL DEFAULT '1' COMMENT 'FZ·´À¡°´Å¥11'," +
|
" FZ_ex1 int(11) NOT NULL DEFAULT '1' COMMENT 'FZÊä³ö°´Å¥1'," +
|
" FZ_ex2 int(11) NOT NULL DEFAULT '1' COMMENT 'FZÊä³ö°´Å¥2'," +
|
" FZ_ex3 int(11) NOT NULL DEFAULT '1' COMMENT 'FZÊä³ö°´Å¥3'," +
|
" FZ_ex4 int(11) NOT NULL DEFAULT '1' COMMENT 'FZÊä³ö°´Å¥4'," +
|
" FZ_ex5 int(11) NOT NULL DEFAULT '1' COMMENT 'FZÊä³ö°´Å¥5'," +
|
" FZ_ex6 int(11) NOT NULL DEFAULT '1' COMMENT 'FZÊä³ö°´Å¥6'," +
|
" FZ_ex7 int(11) NOT NULL DEFAULT '1' COMMENT 'FZÊä³ö°´Å¥7'," +
|
" FZ_ex8 int(11) NOT NULL DEFAULT '1' COMMENT 'FZÊä³ö°´Å¥8'," +
|
" FZ_ex9 int(11) NOT NULL DEFAULT '1' COMMENT 'FZÊä³ö°´Å¥9'," +
|
" FZ_ex10 int(11) NOT NULL DEFAULT '1' COMMENT 'FZÊä³ö°´Å¥10'," +
|
" FZ_ex11 int(11) NOT NULL DEFAULT '1' COMMENT 'FZÊä³ö°´Å¥11'," +
|
" start_auto int(11) NOT NULL DEFAULT '1' COMMENT 'Æô¶¯×Ô¶¯¼ÓÔØ'," +
|
" stop_auto int(11) NOT NULL DEFAULT '1' COMMENT 'Í£Ö¹×Ô¶¯¼ÓÔØ'," +
|
" sudden_crease int(11) NOT NULL DEFAULT '1' COMMENT 'Í»¼Óͻж'," +
|
" all_open int(11) NOT NULL DEFAULT '1' COMMENT 'È«²¿·ÖÕ¢'," +
|
" actual_set_power float NOT NULL DEFAULT '1' COMMENT 'ʵ¼ÊÉ趨¹¦ÂÊ'," +
|
" output_power float NOT NULL DEFAULT '1' COMMENT 'ÒÑÊä³ö¹¦ÂÊ'," +
|
" set_power float NOT NULL DEFAULT '1' COMMENT 'É趨¹¦ÂÊ'," +
|
" interval_time float NOT NULL DEFAULT '1' COMMENT '¼ä¸ôʱ¼äs'," +
|
" actual_curr float NOT NULL DEFAULT '1' COMMENT 'ʵ¼ÊµçÁ÷'," +
|
" actual_mov float NOT NULL DEFAULT '1' COMMENT 'ʵ¼Êµçѹ'," +
|
" switch_on int(11) NOT NULL DEFAULT '1' COMMENT 'ÊÇ·ñºÏÕ¢'," +
|
" FZ_autostate1 int(11) NOT NULL DEFAULT '1' COMMENT 'FZ×Ô¶¯×´Ì¬1'," +
|
" FZ_autostate2 int(11) NOT NULL DEFAULT '1' COMMENT 'FZ×Ô¶¯×´Ì¬2'," +
|
" FZ_autostate3 int(11) NOT NULL DEFAULT '1' COMMENT 'FZ×Ô¶¯×´Ì¬3'," +
|
" FZ_autostate4 int(11) NOT NULL DEFAULT '1' COMMENT 'FZ×Ô¶¯×´Ì¬4'," +
|
" FZ_autostate5 int(11) NOT NULL DEFAULT '1' COMMENT 'FZ×Ô¶¯×´Ì¬5'," +
|
" react_overtem int(11) NOT NULL DEFAULT '1' COMMENT 'µç¿¹³¬ÎÂ'," +
|
" water_overtem int(11) NOT NULL DEFAULT '1' COMMENT 'ÀäÈ´Ë®³¬ÎÂ'," +
|
" water_level_low int(11) NOT NULL DEFAULT '1' COMMENT 'ÀäȴˮҺλµÍ'," +
|
" water_pressure_high int(11) NOT NULL DEFAULT '1' COMMENT 'Àäȴˮѹ¸ß'," +
|
" local_control int(11) NOT NULL DEFAULT '1' COMMENT '¾ÍµØ¿ØÖÆ'," +
|
" back_control int(11) NOT NULL DEFAULT '1' COMMENT 'ºǫ́¿ØÖÆ'," +
|
" central_control int(11) NOT NULL DEFAULT '1' COMMENT 'ÖпؿØÖÆ'," +
|
" switch_state int(11) NOT NULL DEFAULT '1' COMMENT '¿ª¹Ø¹ñ״̬'," +
|
" inter_volume_M200 int(11) NOT NULL DEFAULT '1' COMMENT 'ÖмäÁ¿M200'," +
|
" fan_button int(11) NOT NULL DEFAULT '1' COMMENT '·ç»ú°´Å¥'," +
|
" fan_output int(11) NOT NULL DEFAULT '1' COMMENT '·ç»úÊä³ö'," +
|
" allow_close int(11) NOT NULL DEFAULT '0' COMMENT 'ÊÇ·ñÔÊÐíºÏÕ¢'," +
|
" note varchar(255) NOT NULL DEFAULT ''," +
|
" PRIMARY KEY (num)," +
|
" KEY dev_id_key (dev_id)," +
|
" 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 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_dynamicsystem' " +
|
" AND TABLE_NAME like 'tb_dynamicload_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_dynamicsystem." + 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 dinf
|
* @param now
|
*/
|
public static void insertTb_dynamicload_realdata(MysqlConnPool pool, Dynamicload_inf dinf, Date now) {
|
Dynamicload_button btn = dinf.button;
|
Dynamicload_state state = dinf.state;
|
String sql_str = "INSERT INTO " + Sql_Mysql.Tb_Dynamicload_RealData+dinf.dev_id+"_"+Com.getDateTimeFormat(now, Com.DTF_Y_M_D) +
|
"(dev_id,record_time,FZ_b1,FZ_b2,FZ_b3,FZ_b4,FZ_b5,FZ_b6,FZ_b7,FZ_b8,FZ_b9,FZ_b10,FZ_b11,FZ_fw1,FZ_fw2,FZ_fw3,FZ_fw4,FZ_fw5,FZ_fw6,FZ_fw7,FZ_fw8,FZ_fw9,FZ_fw10,FZ_fw11,FZ_ex1,FZ_ex2,FZ_ex3,FZ_ex4,FZ_ex5,FZ_ex6,FZ_ex7,FZ_ex8,FZ_ex9,FZ_ex10,FZ_ex11,start_auto,stop_auto,sudden_crease,all_open,actual_set_power,output_power,set_power,interval_time,actual_curr,actual_mov,switch_on,FZ_autostate1,FZ_autostate2,FZ_autostate3,FZ_autostate4,FZ_autostate5,react_overtem,water_overtem,water_level_low,water_pressure_high,local_control,back_control,central_control,switch_state,inter_volume_M200,fan_button,fan_output,allow_close) " +
|
"VALUES("+dinf.dev_id+",'"+Com.getDateTimeFormat(now, Com.DTF_YMDhms)+"',"+btn.FZ_b1+","+btn.FZ_b2+","+btn.FZ_b3+","+btn.FZ_b4+","+btn.FZ_b5+","+btn.FZ_b6+","+btn.FZ_b7+","+btn.FZ_b8+","+btn.FZ_b9+","+btn.FZ_b10+","+btn.FZ_b11+","+btn.FZ_fw1+","+btn.FZ_fw2+","+btn.FZ_fw3+","+btn.FZ_fw4+","+btn.FZ_fw5+","+btn.FZ_fw6+","+btn.FZ_fw7+","+btn.FZ_fw8+","+btn.FZ_fw9+","+btn.FZ_fw10+","+btn.FZ_fw11+","+btn.FZ_ex1+","+btn.FZ_ex2+","+btn.FZ_ex3+","+btn.FZ_ex4+","+btn.FZ_ex5+","+btn.FZ_ex6+","+btn.FZ_ex7+","+btn.FZ_ex8+","+btn.FZ_ex9+","+btn.FZ_ex10+","+btn.FZ_ex11+","+state.start_auto+","+state.stop_auto+","+state.sudden_crease+","+state.all_open+","+state.actual_set_power+","+state.output_power+","+state.set_power+","+state.interval_time+","+state.actual_curr+","+state.actual_mov+","+state.switch_on+","+state.FZ_autostate1+","+state.FZ_autostate2+","+state.FZ_autostate3+","+state.FZ_autostate4+","+state.FZ_autostate5+","+state.react_overtem+","+state.water_overtem+","+state.water_level_low+","+state.water_pressure_high+","+state.local_control+","+state.back_control+","+state.central_control+","+state.switch_state+","+state.inter_volume_M200+","+state.fan_button+","+state.fan_output+","+state.allow_close+");";
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
}
|
