package com.watersystem.monitor;

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;

public class Water_Task_SQL {

	/**
	 * ²éѯËùÓеÄË®Àä¼Ç¼
	 * @param pool
	 * @param electric
	 */
	public static void queryAllWater(MysqlConnPool pool,List<Water_inf> winfs) {
		String sql_str = " select * from " + Sql_Mysql.Tb_water_inf+ " order by water_id;";
		Sql_Mysql sql = new Sql_Mysql(pool.getConn());
		ResultSet res = null;
		try {
			res = sql.sqlMysqlQuery(sql_str);
			while(res.next()) {
				Water_inf winf=new Water_inf();
				winf.setWater_id(res.getInt("water_id"));
				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) {
			e.printStackTrace();
		} finally {
			if(res != null) {
				try {
					res.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			sql.close_con();
		}
	}
	/**
	 * ´´½¨Ë®ÀäϵͳÊý¾Ý¿â
	 * @param pool
	 */
	public static void createDB_WaterSystem(MysqlConnPool pool) {
		Sql_Mysql sql = new Sql_Mysql(pool.getConn());
		try {
			sql.sqlMysqlExecute("CREATE DATABASE IF NOT EXISTS " + Sql_Mysql.DB_Watersystem);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			sql.close_con();
		}
	}
	/**
	 * ´´½¨Ë®ÀäÐÅÏ¢±í
	 * @param pool
	 */
	public static void createTb_water_inf(MysqlConnPool pool) {
		String sql_str = "CREATE TABLE IF NOT EXISTS "+Sql_Mysql.Tb_water_inf+" (" + 
				"  num bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Ö÷¼ü'," + 
				"  water_id int(11) NOT NULL DEFAULT '5001' COMMENT 'Ë®Àäϵͳid'," + 
				"  water_name varchar(255) NOT NULL DEFAULT '' COMMENT 'Ë®ÀäϵͳÃû³Æ'," + 
				"  water_type int(11) NOT NULL DEFAULT '0' COMMENT 'Ë®ÀäϵͳÀàÐÍ(±¸ÓÃ)'," + 
				"  water_ip varchar(255) NOT NULL DEFAULT '127.0.0.1' COMMENT 'Ë®Àäϵͳip(ÖØÒª)'," + 
				"  note varchar(255) NOT NULL DEFAULT ''," + 
				"  PRIMARY KEY (num)," + 
				"  KEY water_id_key (water_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_water_rt(MysqlConnPool pool) {
		String sql_str = "CREATE TABLE IF NOT EXISTS " + Sql_Mysql.Tb_water_rt + " (" + 
				"  num bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Ö÷¼ü'," + 
				"  water_id int(11) NOT NULL DEFAULT '5001' COMMENT 'Ë®Àäϵͳid'," + 
				"  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 'ÍâˮѹÁ¦'," + 
				"  note varchar(255) NOT NULL DEFAULT ''," + 
				"   PRIMARY KEY (num)," + 
				"   UNIQUE KEY water_id_key (water_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_water_brach(MysqlConnPool pool) {
		String sql_str = "CREATE TABLE IF NOT EXISTS " + Sql_Mysql.Tb_water_brach + " (" + 
				"  num bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Ö÷¼ü'," + 
				"  water_id int(11) NOT NULL DEFAULT '5001' COMMENT 'Ë®Àäϵͳid'," + 
				"  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)," + 
				"   UNIQUE KEY water_id_key (water_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_water_state(MysqlConnPool pool) {
		String sql_str = "CREATE TABLE IF NOT EXISTS " + Sql_Mysql.Tb_water_state + " (" + 
				"  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 'Ë®ÀäϵͳÔËÐÐ'," + 
				"  note varchar(255) NOT NULL DEFAULT ''," + 
				"   PRIMARY KEY (num)," + 
				"   UNIQUE KEY water_id_key (water_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();
		}
	}
	//´´½¨Ë®Àä¹ÊÕϱí
	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'," + 
				"  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),"+
				"  index  water_id_key(water_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 init(MysqlConnPool pool) {
		createDB_WaterSystem(pool);
		
		createTb_water_inf(pool);
		
		createTb_water_rt(pool);
		
		createTb_water_brach(pool);
		
		createTb_water_state(pool);
		
		createTb_water_alarm(pool);
	}
	
	//²åÈë»ò¸üÐÂË®ÀäϵͳʵʱÐÅÏ¢±í
	public static void replace_water_rt(MysqlConnPool pool,Water_rt wrt) {
		String sql_str = " replace into "+ Sql_Mysql.Tb_water_rt+"(water_id,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)  "
				+ "  values("+wrt.water_id+","+wrt.cooling_flow+","+wrt.supply_tmp+","+wrt.Supply_pre+","+wrt.back_tem+","+wrt.back_pre+","+wrt.Cooling_ser+","+wrt.Buffer_tank_level+","+wrt.Buffer_tank_pre+","+wrt.Humidity_chamber+","+wrt.Tem_chamber+","+wrt.External_tmp+","+wrt.External_pre+")";
		Sql_Mysql sql = new Sql_Mysql(pool.getConn());
		try {
			sql.sqlMysqlExecute(sql_str);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			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)  "
				+ "  values("+wbrach.water_id+","+wbrach.Branch_flow_1+","+wbrach.Branch_flow_2+","+wbrach.Branch_flow_3+","+wbrach.Branch_flow_4+","+wbrach.Branch_flow_5+","+wbrach.Branch_flow_6+","+wbrach.Branch_flow_7+","+wbrach.Branch_flow_8+","+wbrach.Branch_flow_9
				+","+wbrach.Branch_tmp_1+","+wbrach.Branch_tmp_2+","+wbrach.Branch_tmp_3+","+wbrach.Branch_tmp_4+","+wbrach.Branch_tmp_5+","+wbrach.Branch_tmp_6+","+wbrach.Branch_tmp_7+","+wbrach.Branch_tmp_8+","+wbrach.Branch_tmp_9
				+","+wbrach.Branch_pre_1+","+wbrach.Branch_pre_2+","+wbrach.Branch_pre_3+","+wbrach.Branch_pre_4+","+wbrach.Branch_pre_5+","+wbrach.Branch_pre_6+","+wbrach.Branch_pre_7+","+wbrach.Branch_pre_8+","+wbrach.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();
		}
	}
	
	/**
	 * 	¸üÐÂË®Àäϵͳʵʱ·ÖÖ§ÐÅÏ¢±í
	 * @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)  "
				+ "  values("+wstate.water_id+","+wstate.P01_main_pump+","+wstate.P11_replenish_pump+","+wstate.P12_pretest_pump+","+wstate.P13_replenish_pump+","+wstate.H01_electric_heater+","+wstate.H02_electric_heater+","+wstate.Solenoid_openflag+","+wstate.Solenoid_shutflag+","+wstate.valve_start+","+wstate.Exhaust_start+","+wstate.remote_start+","+wstate.remote_stop
				+","+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();
		} 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 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();
				}
			}
			
		}
		
	}
	
	/**
	 * 	²åÈë»ò¸üе±Ç°É豸µÄÁ¬½Ó״̬
	 * @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 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();
		}
	}
}