package com.battdata_rt;

import java.io.File;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;

import main.main_MonitorServer_FBS9009S;

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

public class BattData_RT_RamDB_Thread_SQL {
	
	/**
	 * ´´½¨tb_batt_rtdata±í²¢ÇÒ¸ù¾Ý¼¯ºÏbd_al£¬Ïò±íÖÐÌí¼Óµç³Ø×éidºÍmon_num Öµ
	 * @param conn_pool
	 * @param bd_al   
	 */
	public static void createBattData_RT_RamDB_Table(MysqlConnPool conn_pool, ArrayList<BattData_RT> bd_al)
	{
		String sql_str0 = "DROP TABLE IF EXISTS " + Sql_Mysql.BattRtData_Table + "; ";
		String sql_str1 = "CREATE TABLE IF NOT EXISTS " + Sql_Mysql.BattRtData_Table + " "
						+ "( `num` BIGINT NOT NULL AUTO_INCREMENT, "
						+ "`BattGroupId` INT NOT NULL DEFAULT 0, "
						+ "`rec_datetime` DATETIME NOT NULL DEFAULT '1982-01-01 00:00:00', "
						+ "`mon_num` INT NOT NULL DEFAULT 0, "
						+ "`mon_vol` FLOAT NOT NULL DEFAULT 0, "
						+ "`mon_tmp` FLOAT NOT NULL DEFAULT 0, "
						+ "`mon_res` FLOAT NOT NULL DEFAULT 0, "
						+ "`mon_ser` FLOAT NOT NULL DEFAULT 0, "
						+ "`mon_conn_res` FLOAT NOT NULL DEFAULT 0, "
						+ "`mon_cap` FLOAT NOT NULL DEFAULT 0, "
						+ "INDEX index_BattGroupId (`BattGroupId`), "
						+ "PRIMARY KEY (`num`)) "
						+ "ENGINE=InnoDB DEFAULT CHARSET=utf8;";
		
		String sql_str2 = "INSERT INTO " + Sql_Mysql.BattRtData_Table 
						+ "(BattGroupId,"
						+ "mon_num)" 
						+ " VALUES ";
		int cnt = 0;
		for(int n=0; n<bd_al.size(); n++) {
			BattData_RT brt = bd_al.get(n);
			for(int mc=0; mc<brt.MonCount; mc++) {
				if(cnt > 0) {
					sql_str2 += ",";
				}
				sql_str2 += "(" + brt.BattGroupId + "," + (mc+1) + ")";
				cnt++;
			}
		}
		
		Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
		try {
			sql.sqlMysqlExecute(sql_str0);
			sql.sqlMysqlExecute(sql_str1);
			sql.sqlMysqlExecute(sql_str2);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			sql.close_con();
		}
	}
	
	
	/**
	 * ¸üÐÂtb_batt_rtdata±íÖеÄÊý¾Ý
	 * @param conn_pool
	 * @param bd_al
	 */
	public static void updateBattData_RT_RamDB_Table(MysqlConnPool conn_pool, ArrayList<BattData_RT> bd_al)
	{
		Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
		try 
		{
			for(int index=0; index<bd_al.size(); index++) {
				
				BattData_RT brt = bd_al.get(index);
				String sql_str = "UPDATE " + Sql_Mysql.BattRtData_Table 
							+ " SET "
							+ " rec_datetime='" + Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms) + "'";
				
				float tmp_dt = 0;
				String sql_str_vol = " ";
				for(int n=0; n<brt.MonCount; n++) {
					tmp_dt = brt.al_MonVol.get(n).monVol;
					
					//System.out.println(index+"===="+tmp_dt+"%%%%%%%%%%%%%%%%%%%%%%%%");
					
					if(brt.al_MonVol_RtOld.get(n).monVol != tmp_dt) {
						brt.al_MonVol_RtOld.get(n).monVol = tmp_dt;
						sql_str_vol += " WHEN " + (n+1) + " THEN " + String.format("%1.3f", tmp_dt);
					}
				}
				if(sql_str_vol.trim().length() > 1) {
					sql_str_vol = ", mon_vol = CASE mon_num " + sql_str_vol + " ELSE mon_vol END ";
				}
				
				String sql_str_tmp = " ";
				for(int n=0; n<brt.MonCount; n++) {
					tmp_dt = brt.al_MonVol.get(n).monTmp;
					if(brt.al_MonVol_RtOld.get(n).monTmp != tmp_dt) {
						brt.al_MonVol_RtOld.get(n).monTmp = tmp_dt;
						sql_str_tmp += " WHEN " + (n+1) + " THEN " + String.format("%1.3f", tmp_dt);
					}
				}
				if(sql_str_tmp.trim().length() > 1) {
					sql_str_tmp = ", mon_tmp = CASE mon_num " + sql_str_tmp + " ELSE mon_tmp END ";
				}
				
				String sql_str_res = " ";
				for(int n=0; n<brt.MonCount; n++) {
					tmp_dt = brt.al_MonVol.get(n).monRes;
					if(brt.al_MonVol_RtOld.get(n).monRes != tmp_dt) {
						brt.al_MonVol_RtOld.get(n).monRes = tmp_dt;
						sql_str_res += " WHEN " + (n+1) + " THEN " + String.format("%1.3f", tmp_dt);
					}
				}
				if(sql_str_res.trim().length() > 1) {
					sql_str_res = ", mon_res = CASE mon_num " + sql_str_res + " ELSE mon_res END ";
				}
				
				String sql_str_ser = " ";
				for(int n=0; n<brt.MonCount; n++) {
					tmp_dt = brt.al_MonVol.get(n).monSer;
					if(brt.al_MonVol_RtOld.get(n).monSer != tmp_dt) {
						brt.al_MonVol_RtOld.get(n).monSer = tmp_dt;
						sql_str_ser += " WHEN " + (n+1) + " THEN " + String.format("%1.3f", tmp_dt);
					}
				}
				if(sql_str_ser.trim().length() > 1) {
					sql_str_ser = ", mon_ser = CASE mon_num " + sql_str_ser + " ELSE mon_ser END ";
				}
				
				String sql_str_2 = sql_str_vol + sql_str_tmp + sql_str_res + sql_str_ser;
				if(sql_str_2.trim().length() > 1) {
					sql_str_2 += " WHERE BattGroupId=" + brt.BattGroupId;
					/*
							  + " AND mon_num IN (";
					for(int n=0; n<brt.MonCount; n++) {
						if(n > 0) {
							sql_str_2 += ",";
						}
						sql_str_2 += (n+1);
					}
					sql_str_2 += ")";
					*/
				} else {
					sql_str_2 = " WHERE BattGroupId=" + brt.BattGroupId;
				}
				
				sql_str += sql_str_2;
				System.out.println(sql_str);
				sql.sqlMysqlExecute(sql_str);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			sql.close_con();
		}
	}
	/************************************************************************************************/
	/************************************************************************************************/
	/************************************************************************************************/
	/************************************************************************************************/
	/**
	 * ´´½¨tb_batt_rtstateÊý¾Ý¿â±í£¬²¢ÇÒ½«bd_alÊý×éÖеÄBattGroupIdÊý¾Ý²åÈëµ½±íÖÐ
	 * @param conn_pool
	 * @param bd_al
	 */
	public static void createBattState_RT_RamDB_Table(MysqlConnPool conn_pool, ArrayList<BattData_RT> bd_al)
	{
		String sql_str0 = "DROP TABLE IF EXISTS " + Sql_Mysql.BattRtState_Table + "; ";
		String sql_str1 = "CREATE TABLE IF NOT EXISTS " + Sql_Mysql.BattRtState_Table + " "
						+ "( `num` BIGINT NOT NULL AUTO_INCREMENT, "
						+ "`BattGroupId` INT NOT NULL DEFAULT 0, "
						+ "`batt_count` INT NOT NULL DEFAULT 0, "
						+ "`rec_datetime` DATETIME NOT NULL DEFAULT '1982-01-01 00:00:00', "
						+ "`online_vol` FLOAT NOT NULL DEFAULT 0, "
						+ "`group_vol` FLOAT NOT NULL DEFAULT 0, "
						+ "`group_tmp` FLOAT NOT NULL DEFAULT 0, "
						+ "`group_curr` FLOAT NOT NULL DEFAULT 0, "
						+ "`batt_state` INT NOT NULL DEFAULT 0, "
						+ "`batt_test_type` INT NOT NULL DEFAULT 0, "
						+ "`batt_test_starttime` DATETIME NOT NULL DEFAULT '1982-01-01 00:00:00', "
						+ "`batt_test_recordtime` DATETIME NOT NULL DEFAULT '1982-01-01 00:00:00', "
						+ "`batt_test_tlong` INT NOT NULL DEFAULT 0, "
						+ "`batt_test_cap` FLOAT NOT NULL DEFAULT 0, "
						+ "`batt_real_cap` FLOAT NOT NULL DEFAULT 0, "
						+ "`batt_rest_cap` FLOAT NOT NULL DEFAULT 0, "
						+ "`batt_rest_power1_time` INT NOT NULL DEFAULT 0, "
						+ "`batt_rest_power2_time` INT NOT NULL DEFAULT 0, "
						+ "INDEX index_BattGroupId (`BattGroupId`), "
						+ "PRIMARY KEY (`num`)) "
						+ "ENGINE=InnoDB DEFAULT CHARSET=utf8;";
		
		String sql_str2 = "INSERT INTO " + Sql_Mysql.BattRtState_Table 
						+ "(BattGroupId)" 
						+ " VALUES ";
		for(int n=0; n<bd_al.size(); n++) {
			BattData_RT brt = bd_al.get(n);
			if(n > 0) {
				sql_str2 += ",";
			}
			sql_str2 += "(" + brt.BattGroupId + ")";
		}
		
		Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
		try {
			sql.sqlMysqlExecute(sql_str0);
			sql.sqlMysqlExecute(sql_str1);
			sql.sqlMysqlExecute(sql_str2);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			sql.close_con();
		}
	}
	
	/**
	 * ¸ù¾Ýbd_al¸üÐÂtb_batt_rtstate±í
	 * @param conn_pool
	 * @param bd_al
	 */
	public static void updateBattState_RT_RamDB_Table(MysqlConnPool conn_pool, ArrayList<BattData_RT> bd_al)
	{
		Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
		try 
		{
			String sql_str = "UPDATE " + Sql_Mysql.BattRtState_Table 
					+ " SET "
					+ " rec_datetime='" + Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms) + "',";
			
			sql_str += " batt_count = CASE BattGroupId ";
			for(int n=0; n<bd_al.size(); n++) {
				BattData_RT brt = bd_al.get(n);
				sql_str += " WHEN " + brt.BattGroupId + " THEN " + brt.MonCount;
			}
			
			sql_str += " END, " + " online_vol = CASE BattGroupId ";
			for(int n=0; n<bd_al.size(); n++) {
				BattData_RT brt = bd_al.get(n);
				sql_str += " WHEN " + brt.BattGroupId + " THEN " + brt.getOnlineVol();
			}
			
			sql_str += " END, " + " group_vol = CASE BattGroupId ";
			for(int n=0; n<bd_al.size(); n++) {
				BattData_RT brt = bd_al.get(n);
				sql_str += " WHEN " + brt.BattGroupId + " THEN " + brt.getGroupVol();
			}
			
			sql_str += " END, " + " group_tmp = CASE BattGroupId ";
			for(int n=0; n<bd_al.size(); n++) {
				BattData_RT brt = bd_al.get(n);
				sql_str += " WHEN " + brt.BattGroupId + " THEN " + brt.getGroupTmp();
			}
			
			sql_str += " END, " + " group_curr = CASE BattGroupId ";
			for(int n=0; n<bd_al.size(); n++) {
				BattData_RT brt = bd_al.get(n);
				sql_str += " WHEN " + brt.BattGroupId + " THEN " + brt.getTestCurr();
			}
			
			sql_str += " END, " + " batt_state = CASE BattGroupId ";
			for(int n=0; n<bd_al.size(); n++) {
				BattData_RT brt = bd_al.get(n);
				sql_str += " WHEN " + brt.BattGroupId + " THEN " + brt.getBattState();
			}
			
			sql_str += " END, " + " batt_test_type = CASE BattGroupId ";
			for(int n=0; n<bd_al.size(); n++) {
				BattData_RT brt = bd_al.get(n);
				sql_str += " WHEN " + brt.BattGroupId + " THEN " + brt.getBattTestType();
			}
			
			sql_str += " END, " + " batt_test_starttime = CASE BattGroupId ";
			Date dt = new Date();
			for(int n=0; n<bd_al.size(); n++) {
				BattData_RT brt = bd_al.get(n);
				dt.setTime(brt.getTestStartTime());
				sql_str += " WHEN " + brt.BattGroupId + " THEN '" + Com.getDateTimeFormat(dt, Com.DTF_YMDhms) + "'";
			}
			
			sql_str += " END, " + " batt_test_recordtime = CASE BattGroupId ";
			for(int n=0; n<bd_al.size(); n++) {
				BattData_RT brt = bd_al.get(n);
				dt.setTime(brt.getTestRecordTime());
				sql_str += " WHEN " + brt.BattGroupId + " THEN '" + Com.getDateTimeFormat(dt, Com.DTF_YMDhms) + "'";
			}
			
			sql_str += " END, " + " batt_test_tlong = CASE BattGroupId ";
			for(int n=0; n<bd_al.size(); n++) {
				BattData_RT brt = bd_al.get(n);
				sql_str += " WHEN " + brt.BattGroupId + " THEN " + brt.getTestTimeLong();
			}
			
			sql_str += " END, " + " batt_test_cap = CASE BattGroupId ";
			for(int n=0; n<bd_al.size(); n++) {
				BattData_RT brt = bd_al.get(n);
				sql_str += " WHEN " + brt.BattGroupId + " THEN " + brt.getTestCap();
			}
			
			sql_str += " END, " + " batt_real_cap = CASE BattGroupId ";
			for(int n=0; n<bd_al.size(); n++) {
				BattData_RT brt = bd_al.get(n);
				sql_str += " WHEN " + brt.BattGroupId + " THEN " + brt.getBattRealCap();
			}
			
			sql_str += " END, " + " batt_rest_cap = CASE BattGroupId ";
			for(int n=0; n<bd_al.size(); n++) {
				BattData_RT brt = bd_al.get(n);
				sql_str += " WHEN " + brt.BattGroupId + " THEN " + brt.getBattRestCap();
			}
			
			sql_str += " END, " + " batt_rest_power1_time = CASE BattGroupId ";
			for(int n=0; n<bd_al.size(); n++) {
				BattData_RT brt = bd_al.get(n);
				sql_str += " WHEN " + brt.BattGroupId + " THEN " + brt.getBattRestTime();
			}
			
			sql_str += " END WHERE BattGroupId IN (";
			for(int n=0; n<bd_al.size(); n++) {
				BattData_RT brt = bd_al.get(n);
				if(n > 0) {
					sql_str += ",";
				}
				sql_str += brt.BattGroupId;
			}
			sql_str += ")";
			System.out.println(sql_str);
			sql.sqlMysqlExecute(sql_str);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			sql.close_con();
		}
	}
	/************************************************************************************************/
	/************************************************************************************************/
	/**
	 * ½¨tb_server_state±í²¢ÉèÖÃÖµ
	 * @param conn_pool
	 */
	public static void createServerState_RamDB_Table(MysqlConnPool conn_pool)
	{
		String sql_str0 = "DROP TABLE IF EXISTS " + Sql_Mysql.ServerState_Table + "; ";
		String sql_str1 = "CREATE TABLE IF NOT EXISTS " + Sql_Mysql.ServerState_Table + " "
						+ "( `num` BIGINT NOT NULL AUTO_INCREMENT, "
						+ "`server_version` FLOAT NOT NULL DEFAULT 0, "
						+ "`server_datetime` DATETIME NOT NULL DEFAULT '1982-01-01 00:00:00', "
						+ "`max_mem` BIGINT NOT NULL DEFAULT 0, "
						+ "`total_mem` BIGINT NOT NULL DEFAULT 0, "
						+ "`free_men` BIGINT NOT NULL DEFAULT 0, "
						
						+ "`total_disc_space` BIGINT NOT NULL DEFAULT 0, "
						+ "`free_disc_space` BIGINT NOT NULL DEFAULT 0, "
						
						+ "`db_conn_max` INT NOT NULL DEFAULT 0, "
						+ "`db_conn_count` INT NOT NULL DEFAULT 0, "
						
						+ "PRIMARY KEY (`num`)) "
						+ "ENGINE=MEMORY DEFAULT CHARSET=utf8;";
		
		String sql_str2 = "INSERT INTO " + Sql_Mysql.ServerState_Table 
						+ "(server_version) VALUES (" + main_MonitorServer_FBS9009S.m_VersionNum + ")";
		
		Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
		try {
			sql.sqlMysqlExecute(sql_str0);
			sql.sqlMysqlExecute(sql_str1);
			sql.sqlMysqlExecute(sql_str2);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			sql.close_con();
		}
	}
	
	/**
	 * ¸üÐÂtb_server_state±íÖеÄÖµ,ÒÔ¼°¸üÐÂÏ̵߳Äʱ¼ä´Á
	 * @param conn_pool
	 */
	public static void updateServerState_RamDB_Table(MysqlConnPool conn_pool)
	{
		Runtime rt = Runtime.getRuntime();
		String path = System.getProperty("user.dir");
		File diskPartition = new File(path.substring(0, path.indexOf(":")+1));
        long totalCapacity = diskPartition.getTotalSpace() / (1024*1024);
        long usablePatitionSpace = diskPartition.getUsableSpace() / (1024*1024);
        
		int conn_max = 0;
		int conn_count = 0;
		Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
		try {
			ResultSet res = sql.sqlMysqlQuery("show variables like 'max_connections'");
			if(res.next()) {
				conn_max = res.getInt(2);
			}
			res = sql.sqlMysqlQuery("show status like 'Threads_connected'");
			if(res.next()) {
				conn_count = res.getInt(2);
			}
			
			String sql_str = "UPDATE " + Sql_Mysql.ServerState_Table 
					+ " SET "
					+ "server_datetime='" + Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms) + "',"
					+ "max_mem=" + rt.maxMemory() + ","
					+ "total_mem=" + rt.totalMemory() + ","
					+ "free_men=" + rt.freeMemory() + ","
					
					+ "total_disc_space=" + totalCapacity + ","
					+ "free_disc_space=" + usablePatitionSpace + ","
					+ "db_conn_max=" + conn_max + ","
					+ "db_conn_count=" + conn_count;
			
			sql.sqlMysqlExecute(sql_str);
			
			sql.sqlMysqlUseDB("web_site");
			if(true == sql.sqlMysqlCheckIfTableExist("tb_process_survey")) {
				String sql_str_1 = "UPDATE " + "tb_process_survey" + " SET " 
									+ " ProcessTime='" + Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms) + "' "
									+ " WHERE ProcessId=11001";
				sql.sqlMysqlExecute(sql_str_1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			sql.close_con();
		}
	}
	/************************************************************************************************/
	/**
	 * ¼Ç¼µ±Ç°Ï̵߳ÄÆô¶¯Ê±¼äÒÔ¼°°æ±¾ºÅ
	 * @param conn_pool
	 * @param ver
	 * @return
	 */
	public static boolean updateServerStartInfTo_RamDB_Table(MysqlConnPool conn_pool, double ver)
	{
		boolean res = false;
		Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
		try {
			sql.sqlMysqlUseDB("web_site");
			if(true == sql.sqlMysqlCheckIfTableExist("tb_process_survey")) {
				String sql_str_1 = "UPDATE " + "tb_process_survey" + " SET " 
									+ " Process_starttime='" + Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms) + "', "
									+ " ProcessVersion='" + String.format("V%1.3f", ver) + "' "
									+ " WHERE ProcessId=11001";
				sql.sqlMysqlExecute(sql_str_1);
				res = true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			res = false;
			e.printStackTrace();
		} finally {
			sql.close_con();
		}
		
		return res;
	}
	/************************************************************************************************/
	/************************************************************************************************/
}