package com.dec.fbs9100;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import com.base.Com;
import com.battdata_rt.BattData_RT;

public class BTS61850_Task_Thread_SQL {

	/**
	 *	 判断当前历史实时数据表是否存在
	 * @param conn_pool
	 * @param dev_id
	 * @param date
	 * @return	true:已存在	false:不存在 
	 */
	public static boolean checkHistoryIfNotExistsTable(MysqlConnPool conn_pool,int BattGroupId) {
		Sql_Mysql sql = new Sql_Mysql(conn_pool);
		String tname;
		try {
			sql.sqlMysqlUseDB(Sql_Mysql.DB_BATT_HISTORY);
			tname = Sql_Mysql.Tb_Batt_RealData + BattGroupId + "_" + Com.getDateTimeFormat(new Date(), Com.DTF_YM);
			return sql.sqlMysqlCheckIfTableExist(tname);
		} catch (SQLException e) {
			sql.logger.error("checkHistoryIfNotExistsTable():" + e.toString(), e);
		} finally {
			sql.close_con();
		}
		return false;
	}
	
	/**
	 * 创建指定的电池组历史数据记录表
	 * @param pool
	 * @param BattGroupId	电池组id
	 * @param time 记录时间
	 */
	public static void CreateTb_BattRealDataTable(MysqlConnPool pool,int BattGroupId,Date time) {
		String  sql_str = "CREATE TABLE IF NOT EXISTS " + Sql_Mysql.Tb_Batt_RealData+BattGroupId+"_"+Com.getDateTimeFormat(time, Com.DTF_YM) + " (" + 
				"  `num` bigint(20) NOT NULL AUTO_INCREMENT," + 
				"  `BattGroupId` int(11) NOT NULL DEFAULT '1'," + 
				"  `recrod_time` datetime NOT NULL DEFAULT '2000-01-01 00:00:00'," + 
				"  `group_vol` float NOT NULL DEFAULT '0'," + 
				"  `online_vol` float NOT NULL DEFAULT '0'," + 
				"  `group_curr` float NOT NULL DEFAULT '0'," + 
				"  `group_tmp` float NOT NULL DEFAULT '0'," + 
				"  `batt_state` int(11) NOT NULL DEFAULT '0'," + 
				"  `batt_test_type` int(11) NOT NULL DEFAULT '0'," + 
				"  `batt_test_starttime` datetime NOT NULL DEFAULT '2000-01-01 00:00:00'," + 
				"  `batt_test_tlong` int(11) NOT NULL DEFAULT '0'," + 
				"  `batt_test_cap` float NOT NULL DEFAULT '0'," + 
				"  `mon_num` int(11) NOT NULL DEFAULT '1'," + 
				"  `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'," + 
				"  `mon_JH_curr` float NOT NULL DEFAULT '0'," + 
				"  `note` varchar(255) NOT NULL DEFAULT ''," + 
				"  PRIMARY KEY (`num`)," + 
				"  KEY `index_battgroupid` (`BattGroupId`) USING BTREE," + 
				"  KEY `index_record_time` (`recrod_time`) USING BTREE" + 
				") ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;";
		Sql_Mysql sql = new Sql_Mysql(pool);
		try {
			sql.sqlMysqlExecute(sql_str);
		} catch (SQLException e) {
			sql.logger.error("CreateTb_BattRealDataTable():" + e.toString(), e);
		} finally {
			sql.close_con();
		}
	}
	
	/**
	 * 	删除修改时间超时的历史数据表[没有修改时间的表不会有应影响]
	 */
	public static void deleteHistoryData(MysqlConnPool conn_pool,Date deldate) {
		String sql_select_strs = " select TABLE_NAME,UPDATE_TIME " + 
								 " from information_schema.tables " + 
								 " where table_schema='db_batt_history' " + 
								 " AND TABLE_NAME like 'tb_batt_realdata_%' " + 
								 " AND CREATE_TIME <= '"+Com.getDateTimeFormat(deldate, Com.DTF_YMDhms)+"';" ;
		String sql_delete_strs = " DROP TABLE IF EXISTS ";
		Sql_Mysql sql = new Sql_Mysql(conn_pool);
		ResultSet res = null;
		int count = 0;
		try {
			res = sql.sqlMysqlTotalQuery(sql_select_strs);
			while(res.next()) {
				if(count > 0) {
					sql_delete_strs += ",";
				}
				
				sql_delete_strs += "db_batt_history." + res.getString("TABLE_NAME");
				sql.logger.warn("删除："+res.getString("TABLE_NAME")+"\t at "+Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms));
				count++;
			}
			if(count >0) {
				sql.sqlMysqlTotalExecute(sql_delete_strs);
			}
			
		} catch (SQLException e) {
			sql.logger.error("deleteHistoryData():" + e.toString(), e);
		} finally {
			if(null != res) {
				try {
					res.close();
				} catch (SQLException e) {
					sql.logger.error("deleteHistoryData():" + e.toString(), e);
				}
			}
			sql.close_con();
		}
	}
	
	/**
	 * 	更新电池设备的Ukey信息
	 * @param m_ConnPool
	 * @param param
	 */
	public static void UpdateBattInfTable(MysqlConnPool m_ConnPool,FBS9100_StatAndParam param) {
		String sql_str = " UPDATE " + Sql_Mysql.BattInf_Table + ""
				+ " SET PublicKeyX = '"+ param.publicKeyX +"',KeyID = '" + param.keyID + "',SerialNumber = '"+param.serialNumber+"'"
				+ " WHERE FBSDeviceId = " + param.dev_id;
		
		Sql_Mysql sql = new Sql_Mysql(m_ConnPool);
		try {
			sql.sqlMysqlExecute(sql_str);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			sql.close_con();
		}
	}

	public static void updateBattRtState_Table(MysqlConnPool pool,FBS9100_VCData vc_data,BattData_RT[] battdata) {
		for(int i=0;i<battdata.length;i++) {
			if(null == battdata[i]) {
				continue;
			}
			String sql_str = " UPDATE " + Sql_Mysql.BattRsAlarm_Table + ""
					+ " SET record_time = '"+Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms)+"'"
					+ ",groupvol_alm = " + vc_data.groupvolalm[i]
					+ ",test_end_cap=" + vc_data.test_end_cap[i]
					+ ",test_end_vol=" + vc_data.test_end_vol[i]
					+ ",test_timelong= " + vc_data.test_timelong[i] +
					" WHERE BattGroupId = " + battdata[i].BattGroupId;
			Sql_Mysql sql = new Sql_Mysql(pool);
			try {
				sql.sqlMysqlExecute(sql_str);
			} catch (SQLException e) {
				sql.logger.error("BTS61850_Task_Thread_SQL.updateBattRtState_Table():" + e.toString(), e);
			} finally {
				sql.close_con();
			}
			
		}
	}
}