package com.dev.fbs9009;

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

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

public class MyModBusUpdateThread_SQL {

	
	/**
	 * ´´½¨Éý¼¶LD9É豸Ïà¹ØµÄ±í
	 * @param con_pool
	 */
	public static void createLD9_UpdateStatue_TableOnRam(MysqlConnPool con_pool)
	{
		String str1 = "DROP TABLE IF EXISTS " + Sql_Mysql.RamLD9_UpdateStatue_Table;
		String str2 = "CREATE TABLE IF NOT EXISTS " + Sql_Mysql.RamLD9_UpdateStatue_Table
					+ " ( `num` BIGINT NOT NULL AUTO_INCREMENT, "
					+ "`dev_id` BIGINT NOT NULL DEFAULT 0, "							//É豸id
					+ "`update_file` varchar(200) NOT NULL DEFAULT '', "				//¸üÐÂÎļþÃû³Æ
					+ "`update_en` BOOLEAN NOT NULL DEFAULT false, "					//¸üÐÂʹÄÜ			ÊÇ·ñÐèÒª¸üР    0:²»ÐèÒª¸üР      1:ÐèÒª¸üÐÂ
					+ "`send_upfile_ok` BOOLEAN NOT NULL DEFAULT false, "				//ÊÇ·ñÍê³É
					+ "`stopreason` INT NOT NULL DEFAULT 0, "							//Í£Ö¹Ô­Òò
					+ "`countpackage` INT NOT NULL DEFAULT 0,"								//Êý¾Ý°üµÄ¸öÊý
					+ "`nowpackagenum` INT NOT NULL DEFAULT 0,"							//µ±Ç°´«ÊäµÄ¸öÊý
					+ "`updatetime` DATETIME NOT NULL DEFAULT '1982-01-01 00:00:00',"	//¸üÐÂʱ¼ä
					+ "`note` varchar(200) NOT NULL DEFAULT '', "						//±¸×¢
					+ " UNIQUE INDEX index_dev_id_uniq (`dev_id`), "
					+ " PRIMARY KEY (`num`) ) "
					+ " ENGINE=MEMORY DEFAULT CHARSET=utf8";
		Sql_Mysql sql = new Sql_Mysql(con_pool.getConn());
		try {
			sql.sqlMysqlExecute(str1);
			sql.sqlMysqlExecute(str2);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			sql.close_con();
		}
	}
	
	/**
	 * »ñÈ¡µ±Ç°É豸µÄϵͳÉý¼¶µÄ״̬
	 * @param con_pool
	 * @param dev_id
	 * @param dfu_t
	 */
	public static void queryLD9_Update_StateByDev_id(MysqlConnPool con_pool,LD_updateState upstate)
	{
		String sql_str = "SELECT * FROM " + Sql_Mysql.RamLD9_UpdateStatue_Table + " WHERE dev_id = " + upstate.getDev_id();
		Sql_Mysql sql = new Sql_Mysql(con_pool.getConn());
		ResultSet res = sql.sqlMysqlQuery(sql_str);
		try {
			if(res.next())
			{
				if(null != upstate) {					
					upstate.setUpfileName(res.getString("update_file"));
					upstate.setUpdate_en(res.getBoolean("update_en"));	
					upstate.setUpdatetime(res.getTimestamp("updatetime"));;
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			sql.close_con();
		}
	}
	
	/**
	 * ¸ù¾ÝÉ豸idÐÞ¸ÄÉ豸¸üеÄ״̬
	 */
	public static void updateLD9_Update_StateByDev_id(MysqlConnPool con_pool,LD_updateState upstate){
		String sql_str = "UPDATE "+Sql_Mysql.RamLD9_UpdateStatue_Table+" "
				+ " SET update_en= "+upstate.isUpdate_en()+","
				+ " send_upfile_ok="+upstate.isSend_upfile_ok()+","
				+ " stopreason="+upstate.getStopreason()+","
				+ " countpackage="+upstate.getCountpackage()+","
				+ " nowpackagenum="+upstate.getNowpackagenum()+", "
				+ " updatetime='"+Com.getDateTimeFormat(upstate.getUpdatetime(), Com.DTF_YMDhms)+"' "
				+ " WHERE dev_id="+upstate.getDev_id();
		Sql_Mysql sql = new Sql_Mysql(con_pool.getConn());
		try {
			//System.out.println(sql_str);
			sql.sqlMysqlExecute(sql_str);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			sql.close_con();
		}		
	}	
	
	/**
	 * ¸ù¾ÝÉ豸id¸üе±Ç°É豸´«ÊäÎļþµÄ½ø¶È
	 */
	public static void updateLD9_Update_ProgressByDev_id(MysqlConnPool con_pool,LD_updateState upstate){
		String sql_str = "UPDATE "+Sql_Mysql.RamLD9_UpdateStatue_Table+" "
				+ " SET countpackage="+upstate.getCountpackage()+","
				+ " send_upfile_ok="+upstate.isSend_upfile_ok()+","
				+ " stopreason="+upstate.getStopreason()+","
				+ " nowpackagenum="+upstate.getNowpackagenum()+", "
				+ " starttime='"+Com.getDateTimeFormat(upstate.getStarttime(), Com.DTF_YMDhms)+"', "
				+ " updatetime='"+Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms)+"' "
				+ " WHERE dev_id="+upstate.getDev_id();
		Sql_Mysql sql = new Sql_Mysql(con_pool.getConn());
		try {
			//System.out.println(sql_str);
			sql.sqlMysqlExecute(sql_str);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			sql.close_con();
		}
	}
	
	public static void main(String args[]){
		MysqlConnPool con_pool = new MysqlConnPool("127.0.0.1", 3360, 10);
		createLD9_UpdateStatue_TableOnRam(con_pool);
		
		
	}
}