水冷添加历史实时数据记录功能

whyclj

2020-10-17 e163b7b3e4d5acc63cfda2fec2dd9034864ccf7a

 WaterSystem_MonitorServer/src/com/watersystem/monitor/Water_Task_SQL.java

@@ -30,6 +30,11 @@
            winf.setWater_name(res.getString("water_name"));
            winf.setWater_ip(res.getString("water_ip"));
            winf.setWater_type(res.getInt("water_type"));
				
            winf.wrt = new Water_rt(winf.water_id);
            winf.wbrach = new Water_brach(winf.water_id);
            winf.wstate = new Water_state(winf.water_id);
            winf.walarm = new Water_alarm(winf.water_id);
            winfs.add(winf);
         }
      } catch (SQLException e) {
@@ -262,6 +267,40 @@
         sql.close_con();
      }
   }
	
   /**
    *    更新water_rt表
    * @param pool
    * @param wrt
    */
   public static void update_water_rt(MysqlConnPool pool,Water_rt wrt) {
      String sql_str = " update " + Sql_Mysql.Tb_water_rt +" set "
            + "water_id=" + wrt.water_id
            + ",cooling_flow=" +wrt.cooling_flow
            + ",supply_tmp=" + wrt.supply_tmp 
            + ",Supply_pre=" + wrt.Supply_pre 
            + ",back_tem=" + wrt.back_tem
            + ",back_pre=" + wrt.back_pre
            + ",Cooling_ser=" + wrt.Cooling_ser
            + ",Buffer_tank_level=" + wrt.Buffer_tank_level
            + ",Buffer_tank_pre=" + wrt.Buffer_tank_pre
            + ",Humidity_chamber=" + wrt.Humidity_chamber
            + ",Tem_chamber=" + wrt.Tem_chamber
            + ",External_tmp=" + wrt.External_tmp
            + ",External_pre=" + wrt.External_pre
            + " where water_id= " + wrt.water_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_water_brach(MysqlConnPool pool,Water_brach wbrach) {
      String sql_str = " replace into "+ Sql_Mysql.Tb_water_brach+"(water_id,Branch_flow_1,Branch_flow_2,Branch_flow_3,Branch_flow_4,Branch_flow_5,Branch_flow_6,Branch_flow_7,Branch_flow_8,Branch_flow_9,Branch_tmp_1,Branch_tmp_2,Branch_tmp_3,Branch_tmp_4,Branch_tmp_5,Branch_tmp_6,Branch_tmp_7,Branch_tmp_8,Branch_tmp_9,Branch_pre_1,Branch_pre_2,Branch_pre_3,Branch_pre_4,Branch_pre_5,Branch_pre_6,Branch_pre_7,Branch_pre_8,Branch_pre_9)  "
@@ -277,6 +316,57 @@
         sql.close_con();
      }
   }
	
   /**
    *    更新水冷系统实时分支信息表
    * @param pool
    * @param wbrach
    */
   public static void update_water_brach(MysqlConnPool pool,Water_brach wbrach) {
      String sql_str = " update  "+Sql_Mysql.Tb_water_brach +" "
            + " Set water_id=" + wbrach.water_id
            + ",Branch_flow_1=" + wbrach.Branch_flow_1
            + ",Branch_flow_2=" + wbrach.Branch_flow_2
            + ",Branch_flow_3=" + wbrach.Branch_flow_3
            + ",Branch_flow_4=" + wbrach.Branch_flow_4
            + ",Branch_flow_5=" + wbrach.Branch_flow_5
            + ",Branch_flow_6=" + wbrach.Branch_flow_6
            + ",Branch_flow_7=" + wbrach.Branch_flow_7
            + ",Branch_flow_8=" + wbrach.Branch_flow_8
            + ",Branch_flow_9=" + wbrach.Branch_flow_9
            + ",Branch_tmp_1=" + wbrach.Branch_tmp_1
            + ",Branch_tmp_2=" + wbrach.Branch_tmp_2
            + ",Branch_tmp_3=" + wbrach.Branch_tmp_3
            + ",Branch_tmp_4=" + wbrach.Branch_tmp_4
            + ",Branch_tmp_5=" + wbrach.Branch_tmp_5
            + ",Branch_tmp_6=" + wbrach.Branch_tmp_6
            + ",Branch_tmp_7=" + wbrach.Branch_tmp_7
            + ",Branch_tmp_8=" + wbrach.Branch_tmp_8
            + ",Branch_tmp_9=" + wbrach.Branch_tmp_9
            + ",Branch_pre_1=" + wbrach.Branch_pre_1
            + ",Branch_pre_2=" + wbrach.Branch_pre_2
            + ",Branch_pre_3=" + wbrach.Branch_pre_3
            + ",Branch_pre_4=" + wbrach.Branch_pre_4
            + ",Branch_pre_5=" + wbrach.Branch_pre_5
            + ",Branch_pre_6=" + wbrach.Branch_pre_6
            + ",Branch_pre_7=" + wbrach.Branch_pre_7
            + ",Branch_pre_8=" + wbrach.Branch_pre_8
            + ",Branch_pre_9=" + wbrach.Branch_pre_9
            + " Where water_id = " + wbrach.water_id;
		
      Sql_Mysql sql = new Sql_Mysql(pool.getConn());
      try {
         //System.out.println(sql_str);
         sql.sqlMysqlExecute(sql_str);
      } catch (SQLException e) {
         e.printStackTrace();
      } finally {
         sql.close_con();
      }
   }
	
	
	
   //插入或更新水冷系统实时信息表
   public static void replace_water_state(MysqlConnPool pool,Water_state wstate) {
      String sql_str = " replace into "+ Sql_Mysql.Tb_water_state+"(water_id,P01_main_pump,P11_replenish_pump,P12_pretest_pump,P13_replenish_pump,H01_electric_heater,H02_electric_heater,Solenoid_openflag,Solenoid_shutflag,valve_start,Exhaust_start,remote_start,remote_stop,Operation_ooled_devices,Auto_control_state,Manual_control_state,remote_control_state,Water_system_function)  "
@@ -284,6 +374,43 @@
            +","+wstate.Operation_ooled_devices+","+wstate.Auto_control_state+","+wstate.Manual_control_state+","+wstate.remote_control_state+","+wstate.Water_system_function+")";
      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 wstate
    */
   public static void update_water_state(MysqlConnPool pool,Water_state wstate) {
      String sql_str = " update " + Sql_Mysql.Tb_water_state + " "
            + " Set  water_id=" + wstate.water_id
            + ",P01_main_pump=" + wstate.P01_main_pump
            + ",P11_replenish_pump=" + wstate.P11_replenish_pump
            + ",P12_pretest_pump=" + wstate.P12_pretest_pump
            + ",P13_replenish_pump=" + wstate.P13_replenish_pump
            + ",H01_electric_heater=" + wstate.H01_electric_heater
            + ",H02_electric_heater=" + wstate.H02_electric_heater
            + ",Solenoid_openflag=" + wstate.Solenoid_openflag
            + ",Solenoid_shutflag=" + wstate.Solenoid_shutflag
            + ",valve_start=" + wstate.valve_start
            + ",Exhaust_start=" + wstate.Exhaust_start
            + ",remote_start=" + wstate.remote_start
            + ",remote_stop=" + wstate.remote_stop
            + ",Operation_ooled_devices=" + wstate.Operation_ooled_devices
            + ",Auto_control_state=" + wstate.Auto_control_state
            + ",Manual_control_state=" + wstate.Manual_control_state
            + ",remote_control_state=" + wstate.remote_control_state
            + ",Water_system_function=" + wstate.Water_system_function
            + " Where water_id = "  + wstate.water_id;
      Sql_Mysql sql = new Sql_Mysql(pool.getConn());
      try {
         //System.out.println(sql_str);
         sql.sqlMysqlExecute(sql_str);
      } catch (SQLException e) {
         e.printStackTrace();
@@ -337,7 +464,29 @@
      }
      
   }

	
   /**
    * 插入或更新电源故障
    * @param conn_pool
    * @param control
    */
   public static void delClearWater_alarmTable(MysqlConnPool conn_pool,List<Water_alarm> list) {
      if(list!=null&&list.size()>0) {
         for (Water_alarm walarm : list) {
            String sql_str = "DELETE FROM "+Sql_Mysql.Tb_water_alarm+" WHERE water_id = "+walarm.water_id+" AND alarm_id = "+walarm.alarm_id;
            Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
            try {
               sql.sqlMysqlExecute(sql_str);
            } catch (Exception e) {
               e.printStackTrace();
            } finally {
               sql.close_con();
            }
         }
			
      }
		
   }
   
   /**
    *    插入或更新当前设备的连接状态
@@ -371,4 +520,152 @@
         }
      }
   }
	
   /**
    *  创建历史实时数据记录表
    * @param pool
    * @param water_id
    * @param now
    */
   public static void createTb_Water_realdata(MysqlConnPool pool, int water_id, Date now) {
      String sql_str = "CREATE TABLE IF NOT EXISTS "+Sql_Mysql.Tb_water_realdata+water_id+"_"+Com.getDateTimeFormat(now, Com.DTF_Y_M_D)+" (" + 
            "  num bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键'," + 
            "  water_id int(11) NOT NULL DEFAULT '5001' COMMENT '水冷系统id'," + 
            "  record_time datetime NOT NULL DEFAULT '2000-01-01 00:00:00' COMMENT '记录时间'," + 
            "  P01_main_pump int(11) NOT NULL DEFAULT '0' COMMENT 'P01主泵运行'," + 
            "  P11_replenish_pump int(11) NOT NULL DEFAULT '0' COMMENT 'P11补水泵运行'," + 
            "  P12_pretest_pump int(11) NOT NULL DEFAULT '0' COMMENT 'P12试压泵运行'," + 
            "  P13_replenish_pump int(11) NOT NULL DEFAULT '0' COMMENT 'P13补气泵运行'," + 
            "  H01_electric_heater int(11) NOT NULL DEFAULT '0' COMMENT 'H01电加热器运行'," + 
            "  H02_electric_heater int(11) NOT NULL DEFAULT '0' COMMENT 'H02电加热器运行'," + 
            "  Solenoid_openflag int(11) NOT NULL DEFAULT '0' COMMENT '电磁阀开限位'," + 
            "  Solenoid_shutflag int(11) NOT NULL DEFAULT '0' COMMENT '电磁阀关限位'," + 
            "  valve_start int(11) NOT NULL DEFAULT '0' COMMENT '阀打开'," + 
            "  Exhaust_start int(11) NOT NULL DEFAULT '0' COMMENT '排气阀打开'," + 
            "  remote_start int(11) NOT NULL DEFAULT '0' COMMENT '远程启动水冷系统'," + 
            "  remote_stop int(11) NOT NULL DEFAULT '0' COMMENT '远程停止水冷系统'," + 
            "  Operation_ooled_devices int(11) NOT NULL DEFAULT '0' COMMENT '被冷却器件投运'," + 
            "  Auto_control_state int(11) NOT NULL DEFAULT '0' COMMENT '水冷系统自动控制状态'," + 
            "  Manual_control_state int(11) NOT NULL DEFAULT '0' COMMENT '水冷系统手动控制状态'," + 
            "  remote_control_state int(11) NOT NULL DEFAULT '0' COMMENT '水冷系统远程控制状态'," + 
            "  Water_system_function int(11) NOT NULL DEFAULT '0' COMMENT '水冷系统运行'," + 
            "  cooling_flow float NOT NULL DEFAULT '0' COMMENT '冷却水流量'," + 
            "  supply_tmp float NOT NULL DEFAULT '0' COMMENT '供水温度'," + 
            "  Supply_pre float NOT NULL DEFAULT '0' COMMENT '供水压力'," + 
            "  back_tem float NOT NULL DEFAULT '0' COMMENT '回水温度'," + 
            "  back_pre float NOT NULL DEFAULT '0' COMMENT '回水压力'," + 
            "  Cooling_ser float NOT NULL DEFAULT '0' COMMENT '冷却水电导率'," + 
            "  Buffer_tank_level float NOT NULL DEFAULT '0' COMMENT '缓冲罐液位'," + 
            "  Buffer_tank_pre float NOT NULL DEFAULT '0' COMMENT '缓冲罐压力'," + 
            "  Humidity_chamber float NOT NULL DEFAULT '0' COMMENT '阀厅湿度'," + 
            "  Tem_chamber float NOT NULL DEFAULT '0' COMMENT '阀厅温度'," + 
            "  External_tmp float NOT NULL DEFAULT '0' COMMENT '外水温度'," + 
            "  External_pre float NOT NULL DEFAULT '0' COMMENT '外水压力'," + 
            "  Branch_flow_1 float NOT NULL DEFAULT '0' COMMENT '1#支路流量'," + 
            "  Branch_flow_2 float NOT NULL DEFAULT '0' COMMENT '2#支路流量'," + 
            "  Branch_flow_3 float NOT NULL DEFAULT '0' COMMENT '3#支路流量'," + 
            "  Branch_flow_4 float NOT NULL DEFAULT '0' COMMENT '4#支路流量'," + 
            "  Branch_flow_5 float NOT NULL DEFAULT '0' COMMENT '5#支路流量'," + 
            "  Branch_flow_6 float NOT NULL DEFAULT '0' COMMENT '6#支路流量'," + 
            "  Branch_flow_7 float NOT NULL DEFAULT '0' COMMENT '7#支路流量'," + 
            "  Branch_flow_8 float NOT NULL DEFAULT '0' COMMENT '8#支路流量'," + 
            "  Branch_flow_9 float NOT NULL DEFAULT '0' COMMENT '9#支路流量'," + 
            "  Branch_tmp_1 float NOT NULL DEFAULT '0' COMMENT '1#支路温度'," + 
            "  Branch_tmp_2 float NOT NULL DEFAULT '0' COMMENT '2#支路温度'," + 
            "  Branch_tmp_3 float NOT NULL DEFAULT '0' COMMENT '3#支路温度'," + 
            "  Branch_tmp_4 float NOT NULL DEFAULT '0' COMMENT '4#支路温度'," + 
            "  Branch_tmp_5 float NOT NULL DEFAULT '0' COMMENT '5#支路温度'," + 
            "  Branch_tmp_6 float NOT NULL DEFAULT '0' COMMENT '6#支路温度'," + 
            "  Branch_tmp_7 float NOT NULL DEFAULT '0' COMMENT '7#支路温度'," + 
            "  Branch_tmp_8 float NOT NULL DEFAULT '0' COMMENT '8#支路温度'," + 
            "  Branch_tmp_9 float NOT NULL DEFAULT '0' COMMENT '9#支路温度'," + 
            "  Branch_pre_1 float NOT NULL DEFAULT '0' COMMENT '1#支路压力'," + 
            "  Branch_pre_2 float NOT NULL DEFAULT '0' COMMENT '2#支路压力'," + 
            "  Branch_pre_3 float NOT NULL DEFAULT '0' COMMENT '3#支路压力'," + 
            "  Branch_pre_4 float NOT NULL DEFAULT '0' COMMENT '4#支路压力'," + 
            "  Branch_pre_5 float NOT NULL DEFAULT '0' COMMENT '5#支路压力'," + 
            "  Branch_pre_6 float NOT NULL DEFAULT '0' COMMENT '6#支路压力'," + 
            "  Branch_pre_7 float NOT NULL DEFAULT '0' COMMENT '7#支路压力'," + 
            "  Branch_pre_8 float NOT NULL DEFAULT '0' COMMENT '8#支路压力'," + 
            "  Branch_pre_9 float NOT NULL DEFAULT '0' COMMENT '9#支路压力'," + 
            "  note varchar(255) NOT NULL DEFAULT ''," + 
            "  PRIMARY KEY (num)," + 
            "  KEY water_id_key (water_id) USING BTREE," + 
            "  KEY index_record_time (record_time) USING BTREE" + 
            ") 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 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_watersystem' " + 
                         " AND TABLE_NAME like 'tb_water_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_watersystem." + 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 winf
    * @param now
    */
   public static void insertTb_water_realdata(MysqlConnPool pool, Water_inf winf, Date now) {
      Water_brach brach = winf.wbrach;
      Water_rt rt = winf.wrt;
      Water_state state = winf.wstate;
      String sql_str = "INSERT INTO "+Sql_Mysql.Tb_water_realdata+winf.water_id+"_"+Com.getDateTimeFormat(now, Com.DTF_Y_M_D)+"(water_id,record_time,P01_main_pump,P11_replenish_pump,P12_pretest_pump,P13_replenish_pump,H01_electric_heater,H02_electric_heater,Solenoid_openflag,Solenoid_shutflag,valve_start,Exhaust_start,remote_start,remote_stop,Operation_ooled_devices,Auto_control_state,Manual_control_state,remote_control_state,Water_system_function,cooling_flow,supply_tmp,Supply_pre,back_tem,back_pre,Cooling_ser,Buffer_tank_level,Buffer_tank_pre,Humidity_chamber,Tem_chamber,External_tmp,External_pre,Branch_flow_1,Branch_flow_2,Branch_flow_3,Branch_flow_4,Branch_flow_5,Branch_flow_6,Branch_flow_7,Branch_flow_8,Branch_flow_9,Branch_tmp_1,Branch_tmp_2,Branch_tmp_3,Branch_tmp_4,Branch_tmp_5,Branch_tmp_6,Branch_tmp_7,Branch_tmp_8,Branch_tmp_9,Branch_pre_1,Branch_pre_2,Branch_pre_3,Branch_pre_4,Branch_pre_5,Branch_pre_6,Branch_pre_7,Branch_pre_8,Branch_pre_9) "
            + "VALUES("+winf.water_id+",'"+Com.getDateTimeFormat(now, Com.DTF_YMDhms)+"',"+state.P01_main_pump+","+state.P11_replenish_pump+","+state.P12_pretest_pump+","+state.P13_replenish_pump+","+state.H01_electric_heater+","+state.H02_electric_heater+","+state.Solenoid_openflag+","+state.Solenoid_shutflag+","+state.valve_start+","+state.Exhaust_start+","+state.remote_start+","+state.remote_stop+","+state.Operation_ooled_devices+","+state.Auto_control_state+","+state.Manual_control_state+","+state.remote_control_state+","+state.Water_system_function+","+rt.cooling_flow+","+rt.supply_tmp+","+rt.Supply_pre+","+rt.back_tem+","+rt.back_pre+","+rt.Cooling_ser+","+rt.Buffer_tank_level+","+rt.Buffer_tank_pre+","+rt.Humidity_chamber+","+rt.Tem_chamber+","+rt.External_tmp+","+rt.External_pre+","+brach.Branch_flow_1+","+brach.Branch_flow_2+","+brach.Branch_flow_3+","+brach.Branch_flow_4+","+brach.Branch_flow_5+","+brach.Branch_flow_6+","+brach.Branch_flow_7+","+brach.Branch_flow_8+","+brach.Branch_flow_9+","+brach.Branch_tmp_1+","+brach.Branch_tmp_2+","+brach.Branch_tmp_3+","+brach.Branch_tmp_4+","+brach.Branch_tmp_5+","+brach.Branch_tmp_6+","+brach.Branch_tmp_7+","+brach.Branch_tmp_8+","+brach.Branch_tmp_9+","+brach.Branch_pre_1+","+brach.Branch_pre_2+","+brach.Branch_pre_3+","+brach.Branch_pre_4+","+brach.Branch_pre_5+","+brach.Branch_pre_6+","+brach.Branch_pre_7+","+brach.Branch_pre_8+","+brach.Branch_pre_9+"); ";
      Sql_Mysql sql = new Sql_Mysql(pool.getConn());
      try {
         sql.sqlMysqlExecute(sql_str);
      } catch (SQLException e) {
         e.printStackTrace();
      } finally {
         sql.close_con();
      }
   }
}