package main;

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

import com.base.Com;
import com.jnrsmcu.sdk.netdevice.NodeData;
import com.sql.MysqlConnPool;
import com.sql.Sql_Mysql;

public class TmpHum_Task_SQL {
	/**
	 * ³õʼ»¯Êý¾Ý¿â±í
	 * @param pool
	 */
	public static void init(MysqlConnPool pool) {
		createDB_TmpHumSystem(pool);
		
		createTb_TmpHum_rt(pool);
		
		createTb_TmpHum_history(pool);
	}
	/**
	 * ´´½¨ÎÂʪ¶ÈÊý¾Ý¿â
	 * @param pool
	 */
	public static void createDB_TmpHumSystem(MysqlConnPool pool) {
		Sql_Mysql sql = new Sql_Mysql(pool.getConn());
		try {
			sql.sqlMysqlExecute("CREATE DATABASE IF NOT EXISTS " + Sql_Mysql.DB_TmpHum);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			sql.close_con();
		}
	}
	/**
	 * ´´½¨ÎÂʪ¶ÈʵʱÐÅÏ¢±í
	 * @param pool
	 */
	public static void createTb_TmpHum_rt(MysqlConnPool pool) {
		String sql_str = "CREATE TABLE IF NOT EXISTS "+Sql_Mysql.Tb_tmphum_rt+" (" + 
				"  num bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Ö÷¼ü'," + 
				"  tmphum_id  int(11) NOT NULL DEFAULT '5001' COMMENT 'É豸id'," + 
				"  tmphum_tmp float NOT NULL DEFAULT '30' COMMENT 'ζÈ'," + 
				"  tmphum_hum float NOT NULL DEFAULT '45' COMMENT 'ʪ¶È'," + 
				"  record_time datetime NOT NULL DEFAULT '2020-09-01 00:00:00' COMMENT '¼Ç¼ʱ¼ä'," + 
				"  tmphum_node int(11) NOT NULL DEFAULT '1' COMMENT '½Úµã'," + 
				"  note varchar(255) NOT NULL DEFAULT ''," + 
				"  PRIMARY KEY (num)," + 
				"  UNIQUE KEY tmphum_id_key (tmphum_id) 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 pool
	 */
	public static void createTb_TmpHum_history(MysqlConnPool pool) {
		String sql_str = "CREATE TABLE IF NOT EXISTS "+Sql_Mysql.Tb_tmphum_history+" (" + 
				"  num bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Ö÷¼ü'," + 
				"  tmphum_id  int(11) NOT NULL DEFAULT '5001' COMMENT 'É豸id'," + 
				"  tmphum_tmp float NOT NULL DEFAULT '30' COMMENT 'ζÈ'," + 
				"  tmphum_hum float NOT NULL DEFAULT '45' COMMENT 'ʪ¶È'," + 
				"  record_time datetime NOT NULL DEFAULT '2020-09-01 00:00:00' COMMENT '¼Ç¼ʱ¼ä'," + 
				"  tmphum_node int(11) NOT NULL DEFAULT '1' COMMENT '½Úµã'," + 
				"  note varchar(255) NOT NULL DEFAULT ''," + 
				"  PRIMARY KEY (num) " + 
				") 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();
		}
	}
	//²åÈëʵʱÊý¾Ý
	public static void replace_TmpHum_rt(MysqlConnPool pool,NodeData node,int dev_id) {
		String sql_str = " replace into "+ Sql_Mysql.Tb_tmphum_rt+"(tmphum_id,tmphum_tmp,tmphum_hum,record_time,tmphum_node)  "
				+ "  values("+dev_id+","+node.getTem()+","+node.getHum()+",'"+Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms)+"',"+node.getNodeId()+")";
		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 TmpHum_history
	 */
	public static void insertTmpHum_history(MysqlConnPool conn_pool,NodeData node,int dev_id) {
				String sql_str_ins = " INSERT INTO " + Sql_Mysql.Tb_tmphum_history + "(tmphum_id,tmphum_tmp,tmphum_hum,record_time,tmphum_node) "
									+" VALUES("+dev_id+","+node.getTem()+","+node.getHum()+",'"+Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms)+"',"+node.getNodeId()+");";
				ResultSet res = null;
				Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
				try {
					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();
				}
	  }
}