package com.battdata_rt;

import com.base.Com;
import com.sql.MysqlConnPool;
import com.sql.Sql_Mysql;
import java.io.File;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;

public class BattData_RT_RamDB_Thread_SQL {
	public static void createBattData_RT_RamDB_Table(MysqlConnPool conn_pool, ArrayList<BattData_RT> bd_al) {
		String sql_str0 = "DROP TABLE IF EXISTS `db_ram_db`.`tb_batt_rtdata`; ";
		String sql_str1 = "CREATE TABLE IF NOT EXISTS `db_ram_db`.`tb_batt_rtdata` ( `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 `db_ram_db`.`tb_batt_rtdata`(BattGroupId,mon_num) VALUES ";

		int cnt = 0;
		for (int n = 0; n < bd_al.size(); n++) {
			BattData_RT brt = (BattData_RT) bd_al.get(n);
			for (int mc = 0; mc < brt.MonCount; mc++) {
				if (cnt > 0) {
					sql_str2 = sql_str2 + ",";
				}
				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();
		}
	}

	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 = (BattData_RT) bd_al.get(index);
				String sql_str = "UPDATE `db_ram_db`.`tb_batt_rtdata` SET  rec_datetime='"
						+ Com.getDateTimeFormat(new Date(), "yyyy-MM-dd HH:mm:ss") + "'";

				float tmp_dt = 0.0F;
				String sql_str_vol = " ";
				for (int n = 0; n < brt.MonCount; n++) {
					tmp_dt = ((MonVolData) brt.al_MonVol.get(n)).monVol;

					if (((MonVolData) brt.al_MonVol_RtOld.get(n)).monVol != tmp_dt) {
						((MonVolData) brt.al_MonVol_RtOld.get(n)).monVol = tmp_dt;
						sql_str_vol = sql_str_vol + " WHEN " + (n + 1) + " THEN "
								+ String.format("%1.3f", new Object[] { Float.valueOf(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 = ((MonVolData) brt.al_MonVol.get(n)).monTmp;
					if (((MonVolData) brt.al_MonVol_RtOld.get(n)).monTmp != tmp_dt) {
						((MonVolData) brt.al_MonVol_RtOld.get(n)).monTmp = tmp_dt;
						sql_str_tmp = sql_str_tmp + " WHEN " + (n + 1) + " THEN "
								+ String.format("%1.3f", new Object[] { Float.valueOf(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 = ((MonVolData) brt.al_MonVol.get(n)).monRes;
					if (((MonVolData) brt.al_MonVol_RtOld.get(n)).monRes != tmp_dt) {
						((MonVolData) brt.al_MonVol_RtOld.get(n)).monRes = tmp_dt;
						sql_str_res = sql_str_res + " WHEN " + (n + 1) + " THEN "
								+ String.format("%1.3f", new Object[] { Float.valueOf(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 = ((MonVolData) brt.al_MonVol.get(n)).monSer;
					if (((MonVolData) brt.al_MonVol_RtOld.get(n)).monSer != tmp_dt) {
						((MonVolData) brt.al_MonVol_RtOld.get(n)).monSer = tmp_dt;
						sql_str_ser = sql_str_ser + " WHEN " + (n + 1) + " THEN "
								+ String.format("%1.3f", new Object[] { Float.valueOf(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 = sql_str_2 + " WHERE BattGroupId=" + brt.BattGroupId;
				} else {
					sql_str_2 = " WHERE BattGroupId=" + brt.BattGroupId;
				}

				sql_str = sql_str + sql_str_2;

				sql.sqlMysqlExecute(sql_str);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			sql.close_con();
		}
	}

	public static void createBattState_RT_RamDB_Table(MysqlConnPool conn_pool, ArrayList<BattData_RT> bd_al) {
		String sql_str0 = "DROP TABLE IF EXISTS `db_ram_db`.`tb_batt_rtstate`; ";
		String sql_str1 = "CREATE TABLE IF NOT EXISTS `db_ram_db`.`tb_batt_rtstate` ( `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 `db_ram_db`.`tb_batt_rtstate`(BattGroupId) VALUES ";

		for (int n = 0; n < bd_al.size(); n++) {
			BattData_RT brt = (BattData_RT) bd_al.get(n);
			if (n > 0) {
				sql_str2 = sql_str2 + ",";
			}
			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();
		}
	}

	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 `db_ram_db`.`tb_batt_rtstate` SET  rec_datetime='"
					+ Com.getDateTimeFormat(new Date(), "yyyy-MM-dd HH:mm:ss") + "',";

			sql_str = sql_str + " batt_count = CASE BattGroupId ";
			for (int n = 0; n < bd_al.size(); n++) {
				BattData_RT brt = (BattData_RT) bd_al.get(n);
				sql_str = sql_str + " WHEN " + brt.BattGroupId + " THEN " + brt.MonCount;
			}

			sql_str = sql_str + " END,  online_vol = CASE BattGroupId ";
			for (int n = 0; n < bd_al.size(); n++) {
				BattData_RT brt = (BattData_RT) bd_al.get(n);
				sql_str = sql_str + " WHEN " + brt.BattGroupId + " THEN " + brt.getOnlineVol();
			}

			sql_str = sql_str + " END,  group_vol = CASE BattGroupId ";
			for (int n = 0; n < bd_al.size(); n++) {
				BattData_RT brt = (BattData_RT) bd_al.get(n);
				sql_str = sql_str + " WHEN " + brt.BattGroupId + " THEN " + brt.getGroupVol();
			}

			sql_str = sql_str + " END,  group_tmp = CASE BattGroupId ";
			for (int n = 0; n < bd_al.size(); n++) {
				BattData_RT brt = (BattData_RT) bd_al.get(n);
				sql_str = sql_str + " WHEN " + brt.BattGroupId + " THEN " + brt.getGroupTmp();
			}

			sql_str = sql_str + " END,  group_curr = CASE BattGroupId ";
			for (int n = 0; n < bd_al.size(); n++) {
				BattData_RT brt = (BattData_RT) bd_al.get(n);
				sql_str = sql_str + " WHEN " + brt.BattGroupId + " THEN " + brt.getTestCurr();
			}

			sql_str = sql_str + " END,  batt_state = CASE BattGroupId ";
			for (int n = 0; n < bd_al.size(); n++) {
				BattData_RT brt = (BattData_RT) bd_al.get(n);
				sql_str = sql_str + " WHEN " + brt.BattGroupId + " THEN " + brt.getBattState();
			}

			sql_str = sql_str + " END,  batt_test_type = CASE BattGroupId ";
			for (int n = 0; n < bd_al.size(); n++) {
				BattData_RT brt = (BattData_RT) bd_al.get(n);
				sql_str = sql_str + " WHEN " + brt.BattGroupId + " THEN " + brt.getBattTestType();
			}

			sql_str = sql_str + " END,  batt_test_starttime = CASE BattGroupId ";
			Date dt = new Date();
			for (int n = 0; n < bd_al.size(); n++) {
				BattData_RT brt = (BattData_RT) bd_al.get(n);
				dt.setTime(brt.getTestStartTime());
				sql_str = sql_str + " WHEN " + brt.BattGroupId + " THEN '"
						+ Com.getDateTimeFormat(dt, "yyyy-MM-dd HH:mm:ss") + "'";
			}

			sql_str = sql_str + " END,  batt_test_recordtime = CASE BattGroupId ";
			for (int n = 0; n < bd_al.size(); n++) {
				BattData_RT brt = (BattData_RT) bd_al.get(n);
				dt.setTime(brt.getTestRecordTime());
				sql_str = sql_str + " WHEN " + brt.BattGroupId + " THEN '"
						+ Com.getDateTimeFormat(dt, "yyyy-MM-dd HH:mm:ss") + "'";
			}

			sql_str = sql_str + " END,  batt_test_tlong = CASE BattGroupId ";
			for (int n = 0; n < bd_al.size(); n++) {
				BattData_RT brt = (BattData_RT) bd_al.get(n);
				sql_str = sql_str + " WHEN " + brt.BattGroupId + " THEN " + brt.getTestTimeLong();
			}

			sql_str = sql_str + " END,  batt_test_cap = CASE BattGroupId ";
			for (int n = 0; n < bd_al.size(); n++) {
				BattData_RT brt = (BattData_RT) bd_al.get(n);
				sql_str = sql_str + " WHEN " + brt.BattGroupId + " THEN " + brt.getTestCap();
			}

			sql_str = sql_str + " END,  batt_real_cap = CASE BattGroupId ";
			for (int n = 0; n < bd_al.size(); n++) {
				BattData_RT brt = (BattData_RT) bd_al.get(n);
				sql_str = sql_str + " WHEN " + brt.BattGroupId + " THEN " + brt.getBattRealCap();
			}

			sql_str = sql_str + " END,  batt_rest_cap = CASE BattGroupId ";
			for (int n = 0; n < bd_al.size(); n++) {
				BattData_RT brt = (BattData_RT) bd_al.get(n);
				sql_str = sql_str + " WHEN " + brt.BattGroupId + " THEN " + brt.getBattRestCap();
			}

			sql_str = sql_str + " END,  batt_rest_power1_time = CASE BattGroupId ";
			for (int n = 0; n < bd_al.size(); n++) {
				BattData_RT brt = (BattData_RT) bd_al.get(n);
				sql_str = sql_str + " WHEN " + brt.BattGroupId + " THEN " + brt.getBattRestTime();
			}

			sql_str = sql_str + " END WHERE BattGroupId IN (";
			for (int n = 0; n < bd_al.size(); n++) {
				BattData_RT brt = (BattData_RT) bd_al.get(n);
				if (n > 0) {
					sql_str = sql_str + ",";
				}
				sql_str = sql_str + brt.BattGroupId;
			}
			sql_str = sql_str + ")";

			sql.sqlMysqlExecute(sql_str);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			sql.close_con();
		}
	}

	public static void createServerState_RamDB_Table(MysqlConnPool conn_pool) {
		String sql_str0 = "DROP TABLE IF EXISTS `db_ram_db`.`tb_server_state`; ";
		String sql_str1 = "CREATE TABLE IF NOT EXISTS `db_ram_db`.`tb_server_state` ( `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 `db_ram_db`.`tb_server_state`(server_version) VALUES (1.101)";

		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();
		}
	}

	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() / 1048576L;
		long usablePatitionSpace = diskPartition.getUsableSpace() / 1048576L;

		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 `db_ram_db`.`tb_server_state` SET server_datetime='"
					+ Com.getDateTimeFormat(new Date(), "yyyy-MM-dd HH:mm:ss") + "'," + "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 (sql.sqlMysqlCheckIfTableExist("tb_process_survey")) {
				String sql_str_1 = "UPDATE tb_process_survey SET  ProcessTime='"
						+ Com.getDateTimeFormat(new Date(), "yyyy-MM-dd HH:mm:ss") + "' " + " WHERE ProcessId=11001";
				sql.sqlMysqlExecute(sql_str_1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			sql.close_con();
		}
	}

	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 (sql.sqlMysqlCheckIfTableExist("tb_process_survey")) {
				String sql_str_1 = "UPDATE tb_process_survey SET  Process_starttime='"
						+ Com.getDateTimeFormat(new Date(), "yyyy-MM-dd HH:mm:ss") + "', " + " ProcessVersion='"
						+ String.format("V%1.3f", new Object[] { Double.valueOf(ver) }) + "' "
						+ " WHERE ProcessId=11001";
				sql.sqlMysqlExecute(sql_str_1);
				res = true;
			}
		} catch (SQLException e) {
			res = false;
			e.printStackTrace();
		} finally {
			sql.close_con();
		}

		return res;
	}
}

/*
 * Location:
 * C:\Users\LiJun\Desktop\公司各种设备资料\9600显示模块相关文件\后台程序\2018-09-07\BattFBS9600XSP.
 * jar Qualified Name: com.battdata_rt.BattData_RT_RamDB_Thread_SQL JD-Core
 * Version: 0.6.2
 */