package com.dev.data;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;

import com.base.Com;
import com.sql.MysqlConnPool;
import com.sql.Sql_Mysql;

public class Dev_UDPServer_Thread_SQL {
	
	public void initDB(MysqlConnPool conn_pool) {
		
		CreateDb_Equipstand(conn_pool);				//´´½¨db_equipstandÊý¾Ý¿â
		
		CreateDb_Equip_History(conn_pool);			//´´½¨ÀúʷʵʱÊý¾Ý¼Ç¼Êý¾Ý¿â
		
		CreateDb_Equip_Alarm(conn_pool); 			//´´½¨¸æ¾¯Êý¾Ý¿â
		
		CreateTb_device_inf(conn_pool);				//´´½¨É豸ÐÅÏ¢±í 
		
		createTb_DevData_RtTable(conn_pool);
		
		
	}
	
	/**
	   *     ´´½¨Á¬½Ó״̬±í
	 * @param conn_pool
	 */
	public void createTb_DevData_RtTable(MysqlConnPool conn_pool) {
		String sql_str = " CREATE TABLE IF NOT EXISTS "+Sql_Mysql.Tb_DevData_RtTable+" ( " + 
				"  num bigint(20) NOT NULL AUTO_INCREMENT," + 
				"  dev_id int(11) NOT NULL DEFAULT '0'," + 
				"  record_time datetime NOT NULL DEFAULT '2000-01-01 00:00:00'," + 
				"  note varchar(255) NOT NULL DEFAULT ''," + 
				"  PRIMARY KEY (`num`)," + 
				"  UNIQUE KEY `index_dev_id` (`dev_id`) USING BTREE" + 
				") ENGINE=InnoDB DEFAULT CHARSET=utf8;";
		
		Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
		try {
			sql.sqlMysqlExecute(sql_str);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			sql.close_con();
		}
	}
	
	/**
	 * 	²åÈëÉ豸״̬¼Ç¼±í
	 */
	public static void insertOrUpdateTb_DevData_RtTable(MysqlConnPool conn_pool,List<Device_inf> devices) {
		String sql_str = " REPLACE INTO " + Sql_Mysql.Tb_DevData_RtTable + "(dev_id,record_time) values ";
		for(int i = 0;i<devices.size();i++) {
			if(i>0) {
				sql_str += ",";
			}
			sql_str += "("+devices.get(i).dev_id+",'"+Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms)+"')";
		}
		Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
		try {
			sql.sqlMysqlExecute(sql_str);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			sql.close_con();
		}		
	}

	/**
	 * 	¸üÐÂÉ豸µÄ¼Ç¼ʱ¼ä
	 * @param conn_pool
	 * @param dev
	 */
	public static void updateDeviceDate(MysqlConnPool conn_pool,Device_inf dev) {
		String sql_str = " UPDATE "+Sql_Mysql.Tb_DevData_RtTable+" SET record_time = '"+Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms)+"' WHERE dev_id = " + dev.dev_id;
		Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
		try {
			sql.sqlMysqlExecute(sql_str);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			sql.close_con();
		}
		
	}
	
	
	/**
	 * 	²åÈëÉ豸ÐÅÏ¢
	 * @param conn_pool
	 */
	public void insertTb_device_inf(MysqlConnPool conn_pool,List<Device_inf> devices) {
		String sql_str = " INSERT INTO " + Sql_Mysql.Tb_Device_InfTable + "(dev_name,dev_id,sys_type,sys_name,analog_count,switch_count,state_count,param_count,note) VALUES";
		Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
		Device_inf device = null;
		//System.out.println("===="+max_dev_id+"====");
		int count = 0;
		for(int i=0;i<devices.size();i++) {
			device = devices.get(i);
			Device_inf temp = isExistsDevice(conn_pool, device.getDev_id());
			if(null != temp) {
				device.setDev_id(temp.getDev_id());
				device.setConfigsDevId(temp.getDev_id());
				try {
					//µ±Ç°É豸ÒÑ´æÔÚ£»¸üе±Ç°É豸Öи÷¸ö±äÁ¿ÔÚÊý¾Ý¿âÖеÄÊýÁ¿
					String sql_str_update = " update " +Sql_Mysql.Tb_Device_InfTable
							+ " SET analog_count="+device.getAnalog_count()+","
							+ " switch_count="+device.getSwitch_count()+","
							+ " state_count="+device.getState_count()+","
							+ " param_count="+device.data_count+","
							+ " sys_type = '"+device.sys_type+"',"
							+ " sys_name = '"+device.sys_name+"',"
							+ " note='' "
							+ " WHERE dev_id = '"+device.getDev_id()+"';";
					if(device.dev_id < 2000) {
						//×Óվϵͳ
						for(int k = 0;k<device.datamean.size();k++) {
							sql_str_update = " update " +Sql_Mysql.Tb_Device_InfTable
									+ " SET analog_count="+device.getAnalog_count()+","
									+ " switch_count="+device.getSwitch_count()+","
									+ " state_count="+device.getState_count()+","
									+ " param_count="+device.data_count+","
									+ " sys_type = '"+device.sys_type+"',"
									+ " sys_name = '"+device.sys_name+"',"
									+ " match_type = "+device.datamean.get(k).match_type+","
									+ " match_str = '"+device.datamean.get(k).match_str+"',"
									+ " note='' "
									+ " WHERE dev_name = '"+device.datamean.get(k).dev_name+"' AND dev_id = "+device.dev_id;
							sql.sqlMysqlExecute(sql_str_update);
						}
					}else {						
						//System.out.println(sql_str_update);
						sql.sqlMysqlExecute(sql_str_update);
					}
				
				} catch (SQLException e) {
					e.printStackTrace();
				} finally {
					
				}
				continue;
			}
			
			if(count > 0) {
				sql_str += ",";
			}
			if(device.dev_id < 2000) {
				//×Óվϵͳ
				for(int k = 0;k<device.datamean.size();k++) {
					if(count > 0 && k >0) {
						sql_str += ",";
					}
					sql_str += "("
							+ "'" + device.datamean.get(k).dev_name + "',"
							+ "" + device.datamean.get(k).dev_id + ","
							+ "" + device.getSys_type() + ","
							+ "'" + device.getSys_name() + "',"
							+ "" + device.getAnalog_count() + ","
							+ "" + + device.getSwitch_count() + ","
							+ "" + + device.getState_count() + ","
							+ "" + + device.data_count + ","
							+ "''"
							+ ")";	
					count++;
				}
			}else {				
				sql_str += "("
						+ "'" + device.getDev_name() + "',"
						+ "" + device.getDev_id() + ","
						+ "" + device.getSys_type() + ","
						+ "'" + device.getSys_name() + "',"
						+ "" + device.getAnalog_count() + ","
						+ "" + + device.getSwitch_count() + ","
						+ "" + + device.getState_count() + ","
						+ "" + + device.data_count + ","
						+ "''"
						+ ")";
			}
			count ++;
		}
		try {
			if(count > 0) {
				System.out.println(sql_str);
				sql.sqlMysqlExecute(sql_str);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			sql.close_con();
		}
	}
	
	/**
	 * 	´´½¨É豸ÐÅÏ¢±í
	 * @param conn_pool
	 */
	public void CreateTb_device_inf(MysqlConnPool conn_pool) {
		String sql_str1 = " CREATE TABLE IF NOT EXISTS  " +Sql_Mysql.Tb_Device_InfTable+ " (" + 
				"  num bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Ö÷¼ü'," + 
				"  dev_name varchar(126) NOT NULL DEFAULT '' COMMENT 'É豸ÀàÐÍ'," + 
				"  dev_id int(11) NOT NULL DEFAULT '1' COMMENT 'É豸id'," + 
				"  sys_type int(11) NOT NULL DEFAULT '0' COMMENT 'ϵͳÀàÐÍ'," + 
				"  sys_name varchar(64) NOT NULL DEFAULT '' COMMENT 'ϵͳÃû³Æ'," +
				"  match_type int(11) NOT NULL DEFAULT '0' COMMENT 'Æ¥ÅäÀàÐÍ0:ÎÞ¹æÔò;1:µÈÓÚÆ¥Å䣻2²»µÈÓÚÆ¥Åä'," + 
			    "  match_str  varchar(64) NOT NULL DEFAULT '' COMMENT 'Æ¥Åä×Ö·û´®'," + 
				"  analog_count int(11) NOT NULL DEFAULT '0' COMMENT 'Ä£ÄâÁ¿ÊýÁ¿'," + 
				"  switch_count int(11) NOT NULL DEFAULT '0' COMMENT '¿ª¹ØÁ¿ÊýÁ¿'," + 
				"  state_count int(11) NOT NULL DEFAULT '0' COMMENT '״̬Á¿ÊýÁ¿'," + 
				"  param_count int(11) NOT NULL DEFAULT '0' COMMENT '²ÎÊý×ÜÊýÁ¿'," + 
				"  note varchar(255) NOT NULL DEFAULT ''," +
				"  dev_str varchar(32) NOT NULL DEFAULT ''," + 
				"  PRIMARY KEY (`num`)" + 
				") ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;";
		
		Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
		ResultSet res = null;
		try {
			sql.sqlMysqlExecute(sql_str1);
			//Ìí¼ÓΨһË÷Òý
			res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
					+ " WHERE table_schema='db_equipstand'"
					+ " AND table_name='tb_device_inf'"
					+ " AND column_name='param_count'");
			if(false == res.next()) {
				sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.Tb_Device_InfTable
								+ " ADD COLUMN param_count int(11) NOT NULL DEFAULT '0' COMMENT '²ÎÊý×ÜÊýÁ¿';");
			}
			//Ìí¼ÓÆ¥Åä¹æÔò
			res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
					+ " WHERE table_schema='db_equipstand'"
					+ " AND table_name='tb_device_inf'"
					+ " AND column_name='match_type'");
			if(false == res.next()) {
				sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.Tb_Device_InfTable
								+ " ADD COLUMN match_type int(11) NOT NULL DEFAULT '0' COMMENT 'Æ¥ÅäÀàÐÍ0:ÎÞ¹æÔò;1:µÈÓÚÆ¥Å䣻2²»µÈÓÚÆ¥Åä';");
			}
			
			//Ìí¼ÓÆ¥Åä×Ö·û´®
			res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
					+ " WHERE table_schema='db_equipstand'"
					+ " AND table_name='tb_device_inf'"
					+ " AND column_name='match_str'");
			if(false == res.next()) {
				sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.Tb_Device_InfTable
								+ " ADD COLUMN match_str  varchar(64) NOT NULL DEFAULT '' COMMENT 'Æ¥Åä×Ö·û´®';");
			}
			
			//Ìí¼Ó±êʶ×Ö·û´®
			res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
					+ " WHERE table_schema='db_equipstand'"
					+ " AND table_name='tb_device_inf'"
					+ " AND column_name='dev_str'");
			if(false == res.next()) {
				sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.Tb_Device_InfTable
								+ " ADD COLUMN dev_str varchar(32) NOT NULL DEFAULT '';");
			}
		} catch (SQLException e) {
			//e.printStackTrace();
		} finally {
			if(res != null) {
				try {
					res.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			sql.close_con();
		}
	}
	
	/**
	 * 	´´½¨db_equipstandÊý¾Ý¿â
	 * @param conn_pool
	 */
	public void CreateDb_Equipstand(MysqlConnPool conn_pool) {
		Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
		try {
			sql.sqlMysqlExecute("CREATE DATABASE IF NOT EXISTS " + Sql_Mysql.DB_Equipstand);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			sql.close_con();
		}
	}
	
	
	/**
	 * 	´´½¨db_equip_history ÀúʷʵʱÊý¾Ý¿â
	 * @param conn_pool
	 */
	public void CreateDb_Equip_History(MysqlConnPool conn_pool) {
		Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
		try {
			sql.sqlMysqlExecute("CREATE DATABASE IF NOT EXISTS " + Sql_Mysql.DB_Equip_History);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			sql.close_con();
		}
	}
	
	/**
	 * 	´´½¨db_equip_history É豸¸æ¾¯Êý¾Ý¿â
	 * @param conn_pool
	 */
	public void CreateDb_Equip_Alarm(MysqlConnPool conn_pool) {
		Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
		try {
			sql.sqlMysqlExecute("CREATE DATABASE IF NOT EXISTS " + Sql_Mysql.DB_Equip_Alarm);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			sql.close_con();
		}
	}
	
	/**
	 * 	Åжϵ±Ç°É豸ÊÇ·ñÔÚÊý¾Ý¿âÖдæÔÚ
	 * @param conn_pool
	 * @param dev_name
	 * @return	
	 */
	public Device_inf isExistsDevice(MysqlConnPool conn_pool,int dev_id) {
		String sql_str = " SELECT * FROM db_equipstand.tb_device_inf  WHERE dev_id = '" + dev_id + "';";
		Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
		ResultSet res = sql.sqlMysqlQuery(sql_str);
		Device_inf dev = null;
		try {
			if(res.next()) {
				dev = new Device_inf();
				dev.dev_id = res.getInt("dev_id");			//É豸ID
				
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if(res != null) {
				try {
					res.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			sql.close_con();
		}
		return dev;
	}
	
	
	/**
	 * 	»ñÈ¡Êý¾Ý¿âÖÐ×î´óµÄÉ豸ID+1
	 * @param conn_pool
	 * @return
	 */
	public int getMaxDev_Id(MysqlConnPool conn_pool) {
		int max_dev_id = 1001;
		String sql_str = " SELECT MAX(dev_id) as dev_id FROM " + Sql_Mysql.Tb_Device_InfTable;
		Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
		ResultSet res = sql.sqlMysqlQuery(sql_str);
		try {
			if(res.next()) {
				int dev_id = res.getInt("dev_id");
				if(dev_id > 0) {
					max_dev_id = dev_id+1;
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if(res != null) {
				try {
					res.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				sql.close_con();
			}
		}
		return max_dev_id;
	}
	
	/**
	 * 	»ñÈ¡Êý¾Ý¿âÖÐ×î´óµÄÉ豸ID+1
	 * @param conn_pool
	 * @return
	 */
	public int getMaxSys_type(MysqlConnPool conn_pool) {
		int max_sys_type = 1;
		String sql_str = " SELECT MAX(sys_type) as sys_type FROM " + Sql_Mysql.Tb_Device_InfTable;
		Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
		ResultSet res = sql.sqlMysqlQuery(sql_str);
		try {
			if(res.next()) {
				max_sys_type = res.getInt("sys_type")+1;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if(res != null) {
				try {
					res.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				sql.close_con();
			}
		}
		return max_sys_type;
	}

	/**
	 * 	´´½¨É豸ʵʱ״̬±í,ÒÔ¼°´´½¨×´Ì¬¶ÔÓ¦±í
	 * @param gB_MysqlConnPool
	 * @param devices
	 */
	public void initRtDataCfgTable(MysqlConnPool conn_pool, List<Device_inf> devices) {
		for(int i=0;i<devices.size();i++) {
			Device_inf dev = devices.get(i);
			//´´½¨É豸״̬±í
			String sql_str = "CREATE TABLE IF NOT EXISTS "+Sql_Mysql.Tb_DevData_Rt+dev.dev_id+" (" + 
					"  num bigint(20) NOT NULL AUTO_INCREMENT," + 
					"  dev_id int NOT NULL DEFAULT '"+dev.dev_id+"'," + 
					"  data_type int NOT NULL DEFAULT '0'," + 
					"  param_count int NOT NULL DEFAULT '0'," + 
					"  record_time datetime NOT NULL DEFAULT '2000-01-01 00:00:00',"; 
					
			for(int j=0;j<dev.cfgdatas.size();j++) {
				sql_str += " state"+(j+1)+" float NOT NULL DEFAULT '"+dev.cfgdatas.get(j).defalutval+"',";
			}
			sql_str += "  PRIMARY KEY (`num`)" + 
					") ENGINE=myisam ROW_FORMAT=DYNAMIC CHARSET=utf8;";
			//´´½¨ÅäÖÃÐÅÏ¢±í
			String sql_str_cfg = " CREATE TABLE IF NOT EXISTS " +Sql_Mysql.Tb_DevData_Rt_Cfg+ "(" + 
					"  num bigint(20) NOT NULL AUTO_INCREMENT," + 
					"  dev_id int NOT NULL DEFAULT '1001'," + 
					"  data_type int NOT NULL DEFAULT '1'," + 
					"  param_count int NOT NULL DEFAULT '0'," + 
					"  record_time varchar(20) NOT NULL DEFAULT '2000-01-01 00:00:00'," + 
					"  PRIMARY KEY (`num`)" + 
					") ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;";
			Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
			ResultSet res = null;
			try {
				//´´½¨É豸״̬±í(ÒÔ¼°²¹È«×Ö¶Î)
				sql.sqlMysqlExecute(sql_str);
				//´´½¨É豸ÅäÖÃÐÅÏ¢±í
				sql.sqlMysqlExecute(sql_str_cfg);
				//Ìí¼Ódata_type×Ö¶Î
				res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
						+ " WHERE table_schema='db_equipstand'"
						+ " AND table_name='tb_devdata_rt_"+(dev.dev_id)+"'"
						+ " AND column_name='data_type'");
				if(false == res.next()) {
					sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.Tb_DevData_Rt+dev.dev_id
									+ " ADD COLUMN data_type int NOT NULL DEFAULT '0'  after dev_id");
				}
				res = null;
				//Ìí¼Ódata_type×Ö¶Î
				res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
						+ " WHERE table_schema='db_equipstand'"
						+ " AND table_name='tb_devdata_rt_"+(dev.dev_id)+"'"
						+ " AND column_name='param_count'");
				if(false == res.next()) {
					sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.Tb_DevData_Rt+dev.dev_id
									+ " ADD COLUMN param_count int NOT NULL DEFAULT '0'  after data_type");
				}
				for(int j=0;j<dev.cfgdatas.size();j++) {
					res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
							+ " WHERE table_schema='db_equipstand'"
							+ " AND table_name='tb_devdata_rt_"+(dev.dev_id)+"'"
							+ " AND column_name='state"+(j+1)+"'");
					if(false == res.next()) {
						sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.Tb_DevData_Rt+dev.dev_id
										+ " ADD COLUMN `state"+(j+1)+"` float DEFAULT '"+dev.cfgdatas.get(j).defalutval+"'");
					}
				}
				res = sql.sqlMysqlQuery("SELECT dev_id FROM "+Sql_Mysql.Tb_DevData_Rt+dev.dev_id+" WHERE dev_id = '" + dev.dev_id + "' AND data_type = 2");
				if(!res.next()) {
					//²»´æÔÚ״̬ÐÅϢʱ²åÈëĬÈÏÊý¾Ý
					sql.sqlMysqlExecute("INSERT INTO "+Sql_Mysql.Tb_DevData_Rt+dev.dev_id+"(data_type,param_count,dev_id,record_time) VALUES('2','"+dev.data_count+"',"+dev.dev_id+",'"+Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms)+"');");;
				}
				
				
				/******¿ªÊ¼´´½¨ÅäÖñí******************************************************************************************************************************/
				//Ìí¼Ódata_type×Ö¶Î
				res = sql.sqlMysqlQuery(" SELECT * FROM information_schema.columns "
						+ " WHERE table_schema='db_equipstand'"
						+ " AND table_name='tb_devdata_rt_cfg'"
						+ " AND column_name='data_type'");
				if(false == res.next()) {
					sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.Tb_DevData_Rt_Cfg
									+ " ADD COLUMN data_type int NOT NULL DEFAULT '0'  after dev_id");
				}
				res = null;
				//Ìí¼Ódata_type×Ö¶Î
				res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
						+ " WHERE table_schema='db_equipstand'"
						+ " AND table_name='tb_devdata_rt_cfg'"
						+ " AND column_name='param_count'");
				if(false == res.next()) {
					sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.Tb_DevData_Rt_Cfg
									+ " ADD COLUMN param_count int NOT NULL DEFAULT '0'  after data_type");
				}
				for(int j=0;j<dev.cfgdatas.size();j++) {
					res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
							+ " WHERE table_schema='db_equipstand'"
							+ " AND table_name='tb_devdata_rt_cfg'"
							+ " AND column_name='state"+(j+1)+"'");
					if(false == res.next()) {
						String data_type = "varchar(30)";
						if(j>400) {
							data_type = "varchar(15)";
						}
						
						sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.Tb_DevData_Rt_Cfg
										+ " ADD COLUMN `state"+(j+1)+"` "+data_type+" NOT NULL DEFAULT ''");
					}
				}
				
				
				
				//´´½¨É豸״̬±í(ÒÔ¼°²¹È«×Ö¶Î)
				res = sql.sqlMysqlQuery(" SELECT dev_id FROM "+Sql_Mysql.Tb_DevData_Rt_Cfg+" WHERE dev_id = '" + dev.dev_id + "' AND data_type = " + ChildStoodParse_Thread.Config_DataType);
				if(res.next()) {
					//´æÔÚÅäÖÃ(¸üÐÂÅäÖÃÐÅÏ¢)
					sql_str = " UPDATE " + Sql_Mysql.Tb_DevData_Rt_Cfg +
							  " SET record_time = '¼Ç¼ʱ¼ä' "+",param_count='"+dev.data_count+"'";
					for(int k = 0;k<dev.cfgdatas.size();k++) {
						ConfigData cfg = dev.cfgdatas.get(k);
						sql_str += ",state"+(k+1)+"='"+dev.cfgdatas.get(k).data_value+"&&"+cfg.vartype+(cfg.vartype == 1?"&&"+cfg.unit:"")+"'";
					}
					sql.sqlMysqlExecute(sql_str+" WHERE dev_id = " + dev.dev_id + " AND data_type = "+ChildStoodParse_Thread.Config_DataType);
				}else {
					//²»´æÔÚÅäÖÃÐÅÏ¢
					sql_str = "INSERT INTO "+Sql_Mysql.Tb_DevData_Rt_Cfg+"(data_type,param_count,dev_id,record_time";
					String sql_str_end = " VALUES('1','"+dev.data_count+"','"+dev.dev_id+"','¼Ç¼ʱ¼ä'";
					for(int k = 0;k<dev.cfgdatas.size();k++) {
						ConfigData cfg = dev.cfgdatas.get(k);
						sql_str += ",state"+(k+1);
						sql_str_end +=",'"+dev.cfgdatas.get(k).data_value+"&&"+cfg.vartype+(cfg.vartype == 1?"&&"+cfg.unit:"")+"'";
					}
					sql.sqlMysqlExecute(sql_str+") "+sql_str_end+");");					
				}	
				
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				if(res != null) {
					try {
						res.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
				sql.close_con();
			}
		}		
	}
	
	
	/**
	 * 	´´½¨É豸ʵʱ״̬±í,ÒÔ¼°´´½¨×´Ì¬¶ÔÓ¦±í
	 * @param gB_MysqlConnPool
	 * @param devices
	 */
	public void initRtDataTable(MysqlConnPool conn_pool, List<Device_inf> devices) {
		for(int i=0;i<devices.size();i++) {
			Device_inf dev = devices.get(i);
			//System.err.println(dev.dev_id+":"+dev.cfgdatas.size());
			String sql_str = "CREATE TABLE IF NOT EXISTS "+Sql_Mysql.Tb_DevData_Rt+dev.dev_id+" (" + 
					"  num bigint(20) NOT NULL AUTO_INCREMENT," + 
					"  dev_id int NOT NULL DEFAULT '"+dev.dev_id+"'," + 
					"  record_time varchar(20) NOT NULL DEFAULT '2000-01-01 00:00:00',"; 
					
			for(int j=0;j<dev.cfgdatas.size();j++) {
				sql_str += " state"+(j+1)+" varchar(8) NOT NULL DEFAULT '"+dev.cfgdatas.get(j).defalutval+"',";
			}
			sql_str += "  PRIMARY KEY (`num`)" + 
					") ENGINE=myisam ROW_FORMAT=DYNAMIC CHARSET=utf8;";
			Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
			ResultSet res = null;
			try {
				//´´½¨É豸״̬±í(ÒÔ¼°²¹È«×Ö¶Î)
				sql.sqlMysqlExecute(sql_str);
				for(int j=0;j<dev.cfgdatas.size();j++) {
					res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
							+ " WHERE table_schema='db_equipstand'"
							+ " AND table_name='tb_devdata_rt_"+(dev.dev_id)+"'"
							+ " AND column_name='state"+(j+1)+"'");
					if(false == res.next()) {
						sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.Tb_DevData_Rt+dev.dev_id
										+ " ADD COLUMN `state"+(j+1)+"` varchar(8) DEFAULT '"+dev.cfgdatas.get(j).defalutval+"'");
					}
					
				}
				res = sql.sqlMysqlQuery("SELECT dev_id FROM "+Sql_Mysql.Tb_DevData_Rt+dev.dev_id+" WHERE dev_id = '" + dev.dev_id + "'");
				if(!res.next()) {
					//²»´æÔÚʱ²åÈëĬÈÏÊý¾Ý
					sql.sqlMysqlExecute("INSERT INTO "+Sql_Mysql.Tb_DevData_Rt+dev.dev_id+"(dev_id,record_time) VALUES("+dev.dev_id+",'"+Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms)+"');");;
				}
				
				/******¿ªÊ¼´´½¨ÅäÖñí******************************************************************************************************************************/
				sql_str = "CREATE TABLE IF NOT EXISTS "+Sql_Mysql.Tb_DevData_Rt_Cfg+dev.dev_id+" (" + 
						"  num bigint(20) NOT NULL AUTO_INCREMENT," + 
						"  dev_id varchar(12) NOT NULL DEFAULT '"+dev.dev_id+"'," + 
						"  record_time varchar(12) NOT NULL DEFAULT '¼Ç¼ʱ¼ä',"; 
						
				for(int j=0;j<dev.cfgdatas.size();j++) {
					sql_str += "  state"+(j+1)+" varchar(20) NOT NULL DEFAULT '"+dev.cfgdatas.get(j).data_value+"',";
				}
				sql_str += "  PRIMARY KEY (`num`)" + 
						") ENGINE=myisam DEFAULT CHARSET=utf8;";
				//´´½¨É豸״̬±í(ÒÔ¼°²¹È«×Ö¶Î)
				sql.sqlMysqlExecute(sql_str);
				for(int j=0;j<dev.cfgdatas.size();j++) {
					res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
							+ " WHERE table_schema='db_equipstand'"
							+ " AND table_name='tb_devdata_rt_cfg_"+(dev.dev_id)+"'"
							+ " AND column_name='state"+(j+1)+"'");
					if(false == res.next()) {
						sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.Tb_DevData_Rt_Cfg+dev.dev_id
										+ " ADD COLUMN `state"+(j+1)+"` varchar(32) DEFAULT '"+dev.cfgdatas.get(j).data_value+"'");
					}
					
				}
				res = sql.sqlMysqlQuery("SELECT dev_id FROM "+Sql_Mysql.Tb_DevData_Rt_Cfg+dev.dev_id+" WHERE dev_id = '" + dev.dev_id + "'");
				if(res.next()) {
					//´æÔÚÅäÖÃ(¸üÐÂÅäÖÃÐÅÏ¢)
					sql_str = " UPDATE " + Sql_Mysql.Tb_DevData_Rt_Cfg + dev.dev_id +
							  " SET record_time = '¼Ç¼ʱ¼ä' ";
					for(int k = 0;k<dev.cfgdatas.size();k++) {
						sql_str += ",state"+(k+1)+"='"+dev.cfgdatas.get(k).data_value+"'";
					}
					sql.sqlMysqlExecute(sql_str+" WHERE dev_id = " + dev.dev_id);
				}else {
					//²»´æÔÚÅäÖÃÐÅÏ¢
					sql_str = " INSERT INTO " + Sql_Mysql.Tb_DevData_Rt_Cfg + dev.dev_id + "(dev_id,record_time";
					String sql_str_end = " VALUES('"+dev.dev_id+"','¼Ç¼ʱ¼ä'";
					for(int k = 0;k<dev.cfgdatas.size();k++) {
						sql_str += ",state"+(k+1);
						sql_str_end +=",'"+dev.cfgdatas.get(k).data_value+"'";
					}
					sql.sqlMysqlExecute(sql_str+") "+sql_str_end+");");					
				}				
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				if(res != null) {
					try {
						res.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
				sql.close_con();
			}
		}		
	}	
	
	/**
	 * 	´´½¨ÀúʷʵʱÊý¾Ý±í
	 * @param conn_pool
	 * @param devices
	 */
	public static void createDevData_HistoryTable(MysqlConnPool conn_pool,Device_inf dev,String date_str) {
		String tname = "tb_devdata_history_" + date_str + "_" + dev.dev_id;
		String sql_str = " CREATE TABLE IF NOT EXISTS " + Sql_Mysql.Tb_DevData_historyTable+date_str + "_" + dev.dev_id + "(" + 
					"  `num` bigint(20) NOT NULL AUTO_INCREMENT," + 
					"  `dev_id` int NOT NULL DEFAULT '1004'," + 
					"  `data_type` int NOT NULL DEFAULT '0'," + 
					"  `param_count` int NOT NULL DEFAULT '0'," + 
					"  `record_time` datetime NOT NULL DEFAULT '1970-01-01 00:00:00'," + 
					"  PRIMARY KEY (`num`)" + 
				") ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC; ";
		Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
		ResultSet res = null;
		try {
			sql.sqlMysqlExecute(sql_str);
			//²¹È«ËùÓÐ×Ö¶Î
			sql_str = "";
			int colcount = 0;
			sql_str += "ALTER TABLE " + Sql_Mysql.Tb_DevData_historyTable+date_str + "_" + dev.dev_id;
			for(int j=0;j<dev.cfgdatas.size();j++) {
				res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
						+ " WHERE table_schema='db_equip_history'"
						+ " AND table_name='"+tname+"'"
						+ " AND column_name='state"+(j+1)+"'");
				if(false == res.next()) {
					if(colcount > 0) {
						sql_str += ",";
					}
					sql_str += " ADD COLUMN `state"+(j+1)+"` float NOT NULL DEFAULT '"+dev.cfgdatas.get(j).defalutval+"'";
					colcount ++;
					//sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.Tb_DevData_historyTable+date_str + "_" + dev.dev_id
					//	+ " ADD COLUMN `state"+(j+1)+"` float DEFAULT '"+dev.cfgdatas.get(j).defalutval+"';");
				}
			}
			if(colcount > 0) {
				//System.out.println(sql_str);
				sql.sqlMysqlExecute(sql_str);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if(res != null) {
				try {
					res.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			sql.close_con();
		}
	}
	
	/**
	 *	 Åжϵ±Ç°ÀúʷʵʱÊý¾Ý±íÊÇ·ñ´æÔÚ
	 * @param conn_pool
	 * @param dev_id
	 * @param date
	 * @return	true:ÒÑ´æÔÚ	false:²»´æÔÚ 
	 */
	public static boolean checkHistoryIfNotExistsTable(MysqlConnPool conn_pool,int dev_id,Date date) {
		Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
		String tname;
		try {
			sql.sqlMysqlUseDB(Sql_Mysql.DB_Equip_History);
			tname = Sql_Mysql.Tb_DevData_historyTable + Com.getDateTimeFormat(date, Com.DTF_Y_M_D) + "_" + dev_id;
			return sql.sqlMysqlCheckIfTableExist(tname);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			sql.close_con();
		}
		return false;
	}
	
	/**
	 * 	´´½¨É豸ʵʱºÍÀúÊ·¸æ¾¯¼Ç¼±í
	 * @param conn_pool
	 * @param device
	 */
	public static void createDevAlarmDataTable(MysqlConnPool conn_pool,Device_inf device) {
		String sql_str_alarm = " CREATE TABLE IF NOT EXISTS " +Sql_Mysql.Tb_DevAlarm_Data+device.dev_id+ " (" + 
    			"  num bigint(20) NOT NULL AUTO_INCREMENT," + 
    			"  dev_id int(11) NOT NULL DEFAULT '0' COMMENT 'É豸id'," + 
    			"  alarm_name varchar(24) NOT NULL DEFAULT '' COMMENT '×Ö¶ÎÃû³Æ'," + 
    			"  alarm_value float NOT NULL DEFAULT '0' COMMENT '¸æ¾¯Öµ'," + 
    			"  alarm_type int(11) NOT NULL DEFAULT '0' COMMENT '¸æ¾¯ÀàÐÍ'," + 
    			"  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_index int(11) NOT NULL DEFAULT '0' COMMENT '¸æ¾¯Ï±ê,¶ÔÓ¦stateX'," + 
    			"  note varchar(24) NOT NULL DEFAULT '' COMMENT '±¸ÓÃ×Ö¶Î'," + 
    			"  PRIMARY KEY (`num`)," +
    			"  KEY `index_dev_id` (`dev_id`) USING BTREE " + 
    			") ENGINE=InnoDB DEFAULT CHARSET=utf8;";			//¸æ¾¯ÊµÊ±¼Ç¼±í
		String str_sql_alm_his = "CREATE TABLE IF NOT EXISTS " +Sql_Mysql.Tb_DevAlarm_Data_History+device.dev_id+ " (" + 
				"  num bigint(20) NOT NULL AUTO_INCREMENT," + 
				"  dev_id int(11) NOT NULL DEFAULT '0' COMMENT 'É豸id'," + 
				"  alarm_name varchar(24) NOT NULL DEFAULT '' COMMENT '×Ö¶ÎÃû³Æ'," + 
				"  alarm_value float NOT NULL DEFAULT '0' COMMENT '¸æ¾¯Öµ'," + 
				"  alarm_type int(11) NOT NULL DEFAULT '0' COMMENT '¸æ¾¯ÀàÐÍ'," + 
				"  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_index int(11) NOT NULL DEFAULT '0' COMMENT '¸æ¾¯Ï±ê,¶ÔÓ¦stateX'," + 
				"  note varchar(24) NOT NULL DEFAULT '' COMMENT '±¸ÓÃ×Ö¶Î'," + 
				"  PRIMARY KEY (`num`)," + 
				"  KEY `index_dev_id` (`dev_id`) USING BTREE " + 
				") ENGINE=InnoDB DEFAULT CHARSET=utf8;";			//¸æ¾¯ÀúÊ·¼Ç¼±í
		
		Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
		try {
			//System.out.println(sql_str_alarm);
			sql.sqlMysqlExecute(sql_str_alarm);
			sql.sqlMysqlExecute(str_sql_alm_his);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			sql.close_con();
		}
	}
	
}