package com.sitech.ismp.coll;

import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.text.DecimalFormat;
import java.util.HashMap;
import java.util.Vector;

import com.sitech.ismp.coll.basic.TblATO_KPIDETAIL;
import com.sitech.util.DES3;
import com.sitech.util.Formater;

public class CollDB2 implements CollDB2MBean {
	String KBP_ID = "10-11-32";

	String hostip = null;

	String user = null;

	String passwd = null;

	String kbp_id = "10-11-32";

	private static String SID = null;

	String PORT = "50000";

	String NEATENSID = null;

	public CollDB2() {

	}

	/**
	 * lianlian DB2数据库文件指标 2008-08-26
	 * 
	 * @param
	 * @return
	 * @throws
	 */
	public java.util.Vector getDataFile(String IP_ADDR, String USERNAME,
			String PASSWORD, String KBP_ID, String SID) throws Exception {
		this.KBP_ID = "10-11-32"; // 标识DB2数据库
		this.NEATENSID = Formater.neatenunitid(SID);
		SQLTargetDB2 collectiondbbase = new SQLTargetDB2();
		collectiondbbase.init(IP_ADDR, PORT, SID, USERNAME, PASSWORD);
		getDataFile(collectiondbbase);
		Vector result = collectiondbbase.getKPISet();
		collectiondbbase.release();
		return result;
	}

	/**
	 * lianlian DB2数据库表空间 2008-08-26
	 * 
	 * @param
	 * @return
	 * @throws
	 */
	public java.util.Vector getTableSpaces(String IP_ADDR, String USERNAME,
			String PASSWORD, String KBP_ID, String SID) throws Exception {
		this.KBP_ID = KBP_ID;
		this.NEATENSID = Formater.neatenunitid(SID);
		SQLTargetDB2 collectiondbbase = new SQLTargetDB2();
		collectiondbbase.init(IP_ADDR, PORT, SID, USERNAME, PASSWORD);
		getTableSpaces(collectiondbbase);
		Vector result = collectiondbbase.getKPISet();
		collectiondbbase.release();
		return result;
	}

	/**
	 * lianlian DB2数据系统指标 2008-08-26
	 * 
	 * @param
	 * @return
	 * @throws
	 */
	public java.util.Vector getSystem(String IP_ADDR, String USERNAME,
			String PASSWORD, String KBP_ID, String SID) throws Exception {
		this.KBP_ID = KBP_ID;
		this.NEATENSID = Formater.neatenunitid(SID);
		SQLTargetDB2 collectiondbbase = new SQLTargetDB2();
		collectiondbbase.init(IP_ADDR, PORT, SID, USERNAME, PASSWORD);
		getSystem(collectiondbbase);
		Vector aa = collectiondbbase.getKPISet();
		collectiondbbase.release();
		return aa;
	}

	/**
	 * DB2暂无特殊表采集，不调用。
	 */
	public java.util.Vector getSpecialTable(String IP_ADDR, String USERNAME,
			String PASSWORD, String KBP_ID, String SID, String tableOwner,
			String tableName) throws Exception {
		this.KBP_ID = KBP_ID;
		this.NEATENSID = Formater.neatenunitid(SID);
		SQLTargetDB2 collectiondbbase = new SQLTargetDB2();
		collectiondbbase.init(IP_ADDR, PORT, SID, USERNAME, PASSWORD);
		getSpecialTable(collectiondbbase, tableOwner, tableName);
		Vector aa = collectiondbbase.getKPISet();
		collectiondbbase.release();
		return aa;
	}

	/**
	 * DB2采集暂不调用
	 */
	public java.util.Vector getCMStandKPI(String IP_ADDR, String USERNAME,
			String PASSWORD, String KBP_ID, String SID) throws Exception {
		this.KBP_ID = KBP_ID;
		System.out.println(SID + "****************");
		this.NEATENSID = Formater.neatenunitid(SID);

		SQLTargetDB2 collectiondbbase = new SQLTargetDB2();
		collectiondbbase.init(IP_ADDR, PORT, SID, USERNAME, PASSWORD);

		getCMStandKPI(collectiondbbase);

		Vector aa = collectiondbbase.getKPISet();
		collectiondbbase.release();
		return aa;
	}

	private boolean getParams(java.util.HashMap params) {
		hostip = (String) params.get("IP_ADDR");
		user = (String) params.get("USERNAME");
		passwd = DES3.decrypt((String) params.get("PASSWORD"));
		// kbp_id = (String)params.get("KBP_ID");
		SID = (String) params.get("SID");
		// this.SID = (String) params.get("SID");
		if (params.get("PORT") == null) {
			PORT = "50000";
		} else {
			PORT = (String) params.get("PORT");
		}

		System.out.println("hostip=" + hostip);
		System.out.println("user=" + user);
		System.out.println("passwd=" + passwd);
		System.out.println("SID=" + SID);
		System.out.println("PORT=" + PORT);
		return true;
	}

	public java.util.Vector getDataFile(java.util.HashMap params)
			throws Exception {
		getParams(params);
		return getDataFile(hostip, user, passwd, kbp_id, SID);
	}

	public java.util.Vector getTableSpaces(java.util.HashMap params)
			throws Exception {
		getParams(params);
		return getTableSpaces(hostip, user, passwd, kbp_id, SID);
	}

	public java.util.Vector getSystem(java.util.HashMap params)
			throws Exception {
		getParams(params);
		return getSystem(hostip, user, passwd, kbp_id, SID);
	}

	public java.util.Vector getSpecialTable(java.util.HashMap params)
			throws Exception {
		String TABLEOWNER = (String) params.get("TABLEOWNER");
		String TABLENAME = (String) params.get("TABLENAME");
		getParams(params);
		return getSpecialTable(hostip, user, passwd, kbp_id, SID, TABLEOWNER,
				TABLENAME);
	}

	public java.util.Vector getCMStandKPI(java.util.HashMap params)
			throws Exception {
		getParams(params);
		return getCMStandKPI(hostip, user, passwd, kbp_id, SID);
	}

	public java.util.Vector getExtendKPI(java.util.HashMap params)
			throws Exception {
		getParams(params);
		return getExtendKPI(hostip, user, passwd, kbp_id, SID);
	}

	public java.util.Vector getExtendKPI(String IP_ADDR, String USERNAME,
			String PASSWORD, String KBP_ID, String SID) throws Exception {
		this.KBP_ID = KBP_ID;
		this.NEATENSID = Formater.neatenunitid(SID);

		SQLTargetDB2 collectiondbbase = new SQLTargetDB2();
		collectiondbbase.init(IP_ADDR, PORT, SID, USERNAME, PASSWORD);

		getExtendKPI(collectiondbbase);

		Vector aa = collectiondbbase.getKPISet();
		collectiondbbase.release();
		return aa;
	}

	/**
	 * maozr 新扩，数据库状态采集 2008-05-15; lianlian 修改，DB2数据库状态采集 2008-08-26
	 * 
	 * @param params
	 * @return
	 * @throws Exception
	 */
	public java.util.Vector getStatus(java.util.HashMap params)
			throws Exception {
		getParams(params);
		return getStatus(hostip, user, passwd, kbp_id, SID);
	}

	/**
	 * lianlian 数据库状态采集
	 * 
	 * @param IP_ADDR
	 * @param USERNAME
	 * @param PASSWORD
	 * @param KBP_ID
	 * @param SID
	 * @return
	 * @throws Exception
	 */
	private java.util.Vector getStatus(String IP_ADDR, String USERNAME,
			String PASSWORD, String KBP_ID, String SID) throws Exception {
		this.KBP_ID = KBP_ID;
		this.NEATENSID = Formater.neatenunitid(SID);
		System.out.println("--采集数据库状态--");
		// KBP_ID = KBP_ID + "-10:" + SID.trim() + "-SYS";
		KBP_ID = KBP_ID + "-10:" + this.hostip + "_" + SID.trim() + "-SYS";
		SQLTargetDB2 collectiondbbase = new SQLTargetDB2();
		collectiondbbase.init(IP_ADDR, PORT, SID, USERNAME, PASSWORD);

		if (collectiondbbase.getState()) {
			collectiondbbase.addKPI(KBP_ID, "FM-00-03-001-04", "ok");// 数据库可连性
			System.out.println("----db--- " + SID + "--state--ok----");
		} else {
			System.out.println("----db--- " + SID + "--state--err----");
			collectiondbbase.addKPI(KBP_ID, "FM-00-03-001-04", "error");
		}
		Vector kpi_value = collectiondbbase.getKPISet();
		collectiondbbase.release();
		return kpi_value;
	}

	/**
	 * DB2扩展KPI指标采集
	 * 
	 * @param collectiondbbase
	 */
	private void getExtendKPI(SQLTargetDB2 collectiondbbase) {
		// String KBP_ID = this.KBP_ID + "-10" + ":" + NEATENSID + "-SYS";
		String KBP_ID = this.KBP_ID + "-10" + ":" + this.hostip + "_"
				+ NEATENSID + "-SYS";

		String sql_lock_num = "SELECT * FROM TABLE(SNAPSHOT_LOCKWAIT('"
				+ this.NEATENSID + "', -2)) as SNAPSHOT_LOCKWAIT";
		Vector v_lock_num = collectiondbbase.getSQLKPIResult(sql_lock_num);
		collectiondbbase.addKPI(KBP_ID, "PM-00-03-008-01", String
				.valueOf(v_lock_num.size()));// 锁数量

		String dbstate = collectiondbbase
				.getSQLKPIInfo("SELECT ROLLED_UP_ALERT_STATE_DETAIL FROM TABLE(HEALTH_DB_INFO('"
						+ this.NEATENSID + "',-2)) as HEALTH_DB_INFO");
		if (dbstate.equals("") || dbstate == null) {
			dbstate = "Unknown";
		}

		collectiondbbase.addKPI(KBP_ID, "FM-00-03-001-01", dbstate);// 数据库状态

		// 连接数及详细情况
		String sqlSession = "SELECT * FROM TABLE( SNAPSHOT_APPL_INFO( '"
				+ this.NEATENSID + "', -2)) as SNAPSHOT_APPL_INFO";
		Vector sessionDetail = collectiondbbase.getSQLKPIResult(sqlSession);
		collectiondbbase.addKPI(KBP_ID, "PM-00-03-009-03", String
				.valueOf(sessionDetail.size()));// 会话数量
	}

	public void getKPI(HashMap params) {
		getParams(params);
		getKPI(hostip, SID, user, passwd, kbp_id);
	}

	public void getKPI(String hostip, String sid, String user, String passwd,
			String kbp_id) {

		this.KBP_ID = kbp_id;
		this.NEATENSID = Formater.neatenunitid(SID);
		SQLTargetDB2 collectiondbbase = new SQLTargetDB2();
		collectiondbbase.init(hostip, PORT, sid, user, passwd);
		getSystem(collectiondbbase);
		getTableSpaces(collectiondbbase);
		getDataFile(collectiondbbase);
		collectiondbbase.release();
	}

	/**
	 * lianlian 执行sehll命令
	 * 
	 * @param shellCommand
	 * @return
	 */
	private String execute(String shellCommand) {
		System.out.println("cmd:" + shellCommand);
		String sResult = "";
		try {
			StringBuffer sb = new StringBuffer();
			Process process = Runtime.getRuntime().exec(shellCommand);
			BufferedReader reader = null;
			reader = new BufferedReader(new InputStreamReader(process
					.getInputStream()));
			String line = reader.readLine();
			while (line != null) {
				sb.append(line);
				line = reader.readLine();

			}
			reader.close();
			sResult = sb.toString();
			reader.close();
			return sResult;
		} catch (Exception e) {
			System.err.println("在本机上可能没有 " + shellCommand + " 命令!");
		} finally {
			Runtime.getRuntime().freeMemory();
			Runtime.getRuntime().gc();
		}
		return sResult;
	}

	private String getDb2Level() {
		String result = "";
		String shellCommand = "db2level";
		result = execute(shellCommand);
		return result;
	}

	/**
	 * lianlian 获取DB2版本信息
	 * 
	 * @return
	 */
	private String getVersion() {
		String result = "";
		result = this.getDb2Level();
		result = result.substring(
				((result.indexOf("DB2 v") + "DB2 v".length())), result
						.indexOf(",") - 1);
		return result;
	}

	/**
	 * lianlian 获取数据库位数信息
	 * 
	 * @return
	 */
	private String getDBbit() {
		String result = "";
		result = this.getDb2Level();
		result = result.substring(
				((result.indexOf("uses") + "uses".length() + 2)), result
						.indexOf("bits") - 2);
		result = result + "bit";
		return result;
	}

	private void getSystem(SQLTargetDB2 collectiondbbase) {
		// String KBP_ID = this.KBP_ID + "-10" + ":" + NEATENSID + "-SYS";
		String KBP_ID = this.KBP_ID + "-10" + ":" + this.hostip + "_"
				+ NEATENSID + "-SYS";

		collectiondbbase
				.addKPI(
						KBP_ID,
						"CM-00-03-001-01",
						collectiondbbase
								.getSQLKPIInfo("SELECT DB_NAME FROM TABLE( HEALTH_DB_INFO('', -2)) as HEALTH_DB_INFO"));// 数据库名称
		collectiondbbase.addKPI(KBP_ID, "CM-00-03-001-02", this.getVersion());// 数据库版本
		collectiondbbase.addKPI(KBP_ID, "CM-00-03-001-03", this.getDBbit());// 数据库位数
	}

	/**
	 * select TABLESPACE_NAME,SUM(TOTAL_PAGES)*PAGE_SIZE/1024/1024
	 * TOTAL_PAGES,SUM(FREE_PAGES)*PAGE_SIZE/1024/1024 FREE_PAGES from
	 * table(SNAPSHOT_TBS_CFG('bsstest', -2)) as SNAPSHOT_TBS_CFG GROUP BY
	 * TABLESPACE_NAME,PAGE_SIZE
	 */
	private void getTableSpaces(SQLTargetDB2 collectiondbbase) {
		// String KBP_ID = this.KBP_ID + "-11" + ":" + NEATENSID +
		// "-TABLESPACES";
		String KBP_ID = this.KBP_ID + "-11" + ":" + this.hostip + "_"
				+ NEATENSID + "-TABLESPACES";

		String sql = "select TABLESPACE_NAME,SUM(TOTAL_PAGES)*PAGE_SIZE/1024/1024 TOTAL_PAGES,SUM(FREE_PAGES)*PAGE_SIZE/1024/1024 FREE_PAGES from table(SNAPSHOT_TBS_CFG('"
				+ this.NEATENSID
				+ "', -2)) as SNAPSHOT_TBS_CFG GROUP BY TABLESPACE_NAME,PAGE_SIZE";

		Vector tablespaceinfo = collectiondbbase.getSQLKPIResult(sql);

		for (int i = 0; i < tablespaceinfo.size(); i++) {
			Vector v_tbs_info = (Vector) tablespaceinfo.elementAt(i);
			String tbs_name = (String) v_tbs_info.get(0);
			String tbs_total_size = (String) v_tbs_info.get(1);
			String tbs_free_size = (String) v_tbs_info.get(2);

			float rate = 0;
			if (!tbs_total_size.equals("0")) {
				rate = 100F - Float.parseFloat(tbs_free_size)
						/ Float.parseFloat(tbs_total_size) * 100F;
			}

			collectiondbbase.addKPI(KBP_ID + "-" + tbs_name, "CM-00-03-003-01",
					tbs_name);// 表空间名称
			collectiondbbase.addKPI(KBP_ID + "-" + tbs_name, "CM-00-03-003-02",
					tbs_total_size); // 表空间大小
			collectiondbbase.addKPI(KBP_ID + "-" + tbs_name, "PM-00-03-004-01",
					Formater.getByScale(String.valueOf(rate), 2)); // 表空间利用率
			collectiondbbase.addKPI(KBP_ID + "-" + tbs_name, "PM-10-12-11-12",
					tbs_free_size);// 表空间剩余大小
		}
	}

	private void getDataFile(SQLTargetDB2 collectiondbbase) {
		// String KBP_ID = this.KBP_ID + "-12" + ":" + NEATENSID + "-DATAFILE";
		String KBP_ID = this.KBP_ID + "-12" + ":" + this.hostip + "_"
				+ NEATENSID + "-DATAFILE";

		// HashMap hm_page_size = new HashMap();
		// HashMap hm_data_file_state = new HashMap();

		String sql_data_file_new = "SELECT SNAPSHOT_CONTAINER.CONTAINER_NAME,SNAPSHOT_CONTAINER.TABLESPACE_NAME,SNAPSHOT_TBS_CFG.PAGE_SIZE*SNAPSHOT_CONTAINER.TOTAL_PAGES/1024/1024 TOTAL_SIZE,SNAPSHOT_CONTAINER.TOTAL_PAGES,SNAPSHOT_CONTAINER.USABLE_PAGES,TABLESPACE_INFO.ROLLED_UP_ALERT_STATE_DETAIL FROM TABLE( SNAPSHOT_CONTAINER('"
				+ this.NEATENSID
				+ "', -2)) as SNAPSHOT_CONTAINER,TABLE(SNAPSHOT_TBS_CFG('"
				+ this.NEATENSID
				+ "', -2)) as SNAPSHOT_TBS_CFG,TABLE(HEALTH_CONT_INFO('"
				+ this.NEATENSID
				+ "',-2)) as TABLESPACE_INFO WHERE SNAPSHOT_CONTAINER.TABLESPACE_NAME=SNAPSHOT_TBS_CFG.TABLESPACE_NAME AND TABLESPACE_INFO.CONTAINER_NAME=SNAPSHOT_CONTAINER.CONTAINER_NAME AND TABLESPACE_INFO.TABLESPACE_NAME=SNAPSHOT_CONTAINER.TABLESPACE_NAME";

		Vector v_sql_data_file_new = collectiondbbase
				.getSQLKPIResult(sql_data_file_new);
		for (int i = 0; i < v_sql_data_file_new.size(); i++) {
			Vector row = (Vector) v_sql_data_file_new.elementAt(i);
			String filename = (String) row.get(0);
			String tbs_name = (String) row.get(1);
			String total_size = (String) row.get(2);
			String total_pages = (String) row.get(3);
			String usable_pages = (String) row.get(4);
			String state = (String) row.get(5);
			// System.out.println(total_size);

			collectiondbbase.addKPI(KBP_ID + "-" + filename, "CM-00-03-006-01",
					filename);// 数据设备名
			collectiondbbase.addKPI(KBP_ID + "-" + filename, "FM-10-12-12-10",
					state);// 数据文件状态

			collectiondbbase.addKPI(KBP_ID + "-" + filename, "CM-10-12-12-11",
					tbs_name);// 表空间名
			collectiondbbase.addKPI(KBP_ID + "-" + filename, "PM-10-12-12-10",
					total_size);// 数据文件大小

			String rate = "0.00";
			DecimalFormat myFormatter = new DecimalFormat("##.##");
			if (!total_pages.equals("0")) {
				rate = myFormatter
						.format((Float.parseFloat(total_pages) - Float
								.parseFloat(usable_pages))
								/ Float.parseFloat(total_pages));
				if (rate.equals("0")) {
					rate = "0.00";
				}
			}
			collectiondbbase.addKPI(KBP_ID + "-" + filename, "PM-00-03-007-01",
					rate);// 利用情况
		}
	}

	String getFileName(String filepath) {
		String[] ss = Formater.split2Array(filepath, "/");
		return ss[ss.length - 1];
	}

	private void getSpecialTable(SQLTargetDB2 collectiondbbase,
			String tableOwner, String tableName) {

		// String KBP_ID = this.KBP_ID + "-14" + ":" + NEATENSID + "-TABLES";
		String KBP_ID = this.KBP_ID + "-14" + ":" + this.hostip + "_"
				+ NEATENSID + "-TABLES";
		// String KBP_ID = this.KBP_ID + ":TABLES"+":"+tableName;
		String sql1 = "select table_name, initial_extent,next_extent,max_extents ,PARTITIONED from dba_tables "
				+ " where owner='"
				+ tableOwner
				+ "'"
				+ " and table_name='"
				+ tableName + "'";

		String sql2 = "select EXTENTS from  DBA_SEGMENTS  " + " where owner='"
				+ tableOwner + "'" + " and segment_name='" + tableName + "'"
				+ " and segment_type='TABLE'";
		Vector CM10 = collectiondbbase.getKPISet(sql1);
		Vector PM10 = collectiondbbase.getKPISet(sql2);

		collectiondbbase
				.addKPI(KBP_ID, "CM-00-03-004-01", (String) CM10.get(0));
		collectiondbbase
				.addKPI(KBP_ID, "CM-00-03-004-02", (String) CM10.get(1));
		collectiondbbase
				.addKPI(KBP_ID, "CM-00-03-004-03", (String) CM10.get(2));
		collectiondbbase
				.addKPI(KBP_ID, "CM-00-03-004-04", (String) CM10.get(3));
		collectiondbbase
				.addKPI(KBP_ID, "CM-00-03-004-05", (String) CM10.get(4));
		collectiondbbase
				.addKPI(KBP_ID, "PM-00-03-002-01", (String) PM10.get(0));
	}

	private void getCMStandKPI(SQLTargetDB2 collectiondbbase) {

	}

	public static void main(String args[]) throws Exception {

		if (args.length < 5) {
			System.out
					.println("please input params <ip_addr> <username> <password> <port> <sid>");
			System.exit(0);
		}
		String ip_addr = args[0];
		String username = args[1];
		String password = args[2];
		String port = args[3];
		String sid = args[4];
		password = DES3.encrypt(password);

		// USERNAME/PASSWORD/PORT/SID/IP_ADDR
		HashMap params = new HashMap();
		params.put("IP_ADDR", ip_addr);
		params.put("USERNAME", username);
		params.put("PASSWORD", password);
		params.put("PORT", port);
		params.put("SID", sid);

		CollDB2 coll = new CollDB2();
		System.out.println("DB2 Prot: " + coll.PORT);
		Vector dataVector = coll.getDataFile(params);

		System.out.println("===============Data File==============");
		for (int i = 0; i < dataVector.size(); i++) {
			TblATO_KPIDETAIL tblato_kpidetail = (TblATO_KPIDETAIL) dataVector
					.get(i);
			System.out.println(tblato_kpidetail.UNIT_ID + " | "
					+ tblato_kpidetail.KPI_ID + " | "
					+ tblato_kpidetail.KPI_VALUE);
		}

		System.out.println("================Table Spaces==============");
		Vector tableSpacesVector = coll.getTableSpaces(params);
		for (int i = 0; i < tableSpacesVector.size(); i++) {
			TblATO_KPIDETAIL tblato_kpidetail = (TblATO_KPIDETAIL) tableSpacesVector
					.get(i);
			System.out.println(tblato_kpidetail.UNIT_ID + " | "
					+ tblato_kpidetail.KPI_ID + " | "
					+ tblato_kpidetail.KPI_VALUE);
		}

		System.out.println("================System==============");
		Vector sysVector = coll.getSystem(params);
		for (int i = 0; i < sysVector.size(); i++) {
			TblATO_KPIDETAIL tblato_kpidetail = (TblATO_KPIDETAIL) sysVector
					.get(i);
			System.out.println(tblato_kpidetail.UNIT_ID + " | "
					+ tblato_kpidetail.KPI_ID + " | "
					+ tblato_kpidetail.KPI_VALUE);
		}

		System.out.println("================Status==============");
		Vector statusVector = coll.getStatus(params);
		for (int i = 0; i < statusVector.size(); i++) {
			TblATO_KPIDETAIL tblato_kpidetail = (TblATO_KPIDETAIL) statusVector
					.get(i);
			System.out.println(tblato_kpidetail.UNIT_ID + " | "
					+ tblato_kpidetail.KPI_ID + " | "
					+ tblato_kpidetail.KPI_VALUE);
		}
	}
}