水冷将replace改成update

whyclj

2020-09-24 06eea2578a9f80002a87c90e389231e703fd2d4d

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

@@ -3,8 +3,11 @@
import java.security.interfaces.RSAKey;
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;

@@ -208,28 +211,15 @@
   public static void createTb_water_alarm(MysqlConnPool pool) {
      String sql_str = "CREATE TABLE IF NOT EXISTS " + Sql_Mysql.Tb_water_alarm + " (" + 
            "  num bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键'," + 
            "  water_id int(11) NOT NULL DEFAULT '5001' COMMENT '水冷系统id'," + 
            "  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 '水冷系统运行'," + 
            "  water_id int(11) NOT NULL DEFAULT '5001' COMMENT '设备id'," + 
            "  alarm_id int(11) NOT NULL DEFAULT '12930' 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)," + 
            "   UNIQUE KEY water_id_key (water_id) USING BTREE" + 
            "  PRIMARY KEY (num),"+
            "  index  water_id_key(water_id) " + 
            ") ENGINE=InnoDB DEFAULT CHARSET=utf8;";
      Sql_Mysql sql = new Sql_Mysql(pool.getConn());
      try {
@@ -255,6 +245,8 @@
      createTb_water_brach(pool);
      
      createTb_water_state(pool);
		
      createTb_water_alarm(pool);
   }
   
   //插入或更新水冷系统实时信息表
@@ -270,6 +262,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)  "
@@ -285,6 +311,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)  "
@@ -299,4 +376,121 @@
         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();
      } finally {
         sql.close_con();
      }
   }
    
   /**
    * 插入或更新电源故障
    * @param conn_pool
    * @param control
    */
   public static void insertOrUpdateWater_alarmTable(MysqlConnPool conn_pool,List<Water_alarm> list) {
      if(list!=null&&list.size()>0) {
         for (Water_alarm walarm : list) {
            String sql_str_sel = " select * from " + Sql_Mysql.Tb_water_alarm + " where water_id = " + walarm.getWater_id()+" and alarm_id="+walarm.getAlarm_id();
            String sql_str_ins = " INSERT INTO " + Sql_Mysql.Tb_water_alarm + "(water_id,alarm_id,alarm_starttime,alarm_endtime,alarm_confirm,alarm_confirmtime) "
                           +" VALUES("+walarm.water_id+","+walarm.alarm_id+",'"+Com.getDateTimeFormat(walarm.alarm_starttime, Com.DTF_YMDhms)+"','"+Com.getDateTimeFormat(walarm.alarm_endtime, Com.DTF_YMDhms)+"',"+walarm.alarm_confirm+",'"+Com.getDateTimeFormat(walarm.alarm_confirmtime, Com.DTF_YMDhms)+"');";
            String sql_str_update= " update " + Sql_Mysql.Tb_water_alarm + 
                                 " set alarm_starttime='"+Com.getDateTimeFormat(walarm.alarm_starttime, Com.DTF_YMDhms)+
                                 "',alarm_endtime='"+Com.getDateTimeFormat(walarm.alarm_endtime, Com.DTF_YMDhms)+
                                 "',alarm_confirm="+walarm.alarm_confirm+
                                 ",alarm_confirmtime='"+Com.getDateTimeFormat(walarm.alarm_confirmtime, Com.DTF_YMDhms)+
                                 "'  where water_id="+walarm.water_id+
                                 "  and alarm_id="+walarm.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 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();
         }
      }
   }
}