package com.sitech.ismp.coll;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.HashMap;
import java.util.Vector;

import org.apache.log4j.Logger;

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

public class CollOracle10iBySql implements CollOracle10iMBean {
	private static Logger logger = Logger.getLogger("LOGER");
	
	private String kbpClass = "10-11-30";
	
	private String ipAddr = null;
	private String username = null;
	private String port = "1521";
	private String password = "";
	private String sid = null;
	private String deviceId = null;
	
	private Vector<TblATO_KPIDETAIL> result = new Vector<TblATO_KPIDETAIL>();
	
	private SQLTarget target;

	/**
	 */
	public Vector<TblATO_KPIDETAIL> coll(HashMap<String,String> params) {
		CollBase collBase = new CollBase();
		init(params);
		
		String unitId = this.kbpClass + "-10:" + this.deviceId + "-perf";
		
		String status = "UP";
		
		try {
			getDBState();
			getSys();
			getFileSys();
			getTableSpace();
			getDataFile();
		} catch (Exception e) {
			status = e.getMessage();
			LogUtil.error("Exception while collect Oracle!", e);
		} finally {
			collBase.addKPI(unitId, "PM-00-03-900-01", status);
			target.releaseResources();
		}
		collBase.addKPISet(this.result);
		return collBase.KPISet;
	}

	private void getSys() {
		_SYS_collParameter();
		_SYS_collPerformance();
	}

	private void getFileSys() {
		String unitId = kbpClass + "-13:" + deviceId + "-filesys";
		String sql2 = "select event,count(*) from v$session_wait where event not like 'SQL%' and event not like '%time%' and event not like '%message%' group by event";
		String sql3 = "select owner,segment_name,segment_type,max_extents-extents from dba_segments where max_extents-extents<10";
		String sql4 = "select count(*) from dba_segments where max_extents-extents<10";
		String sql5 = "select owner,segment_name,segment_type,extents from dba_segments where extents>4000";

		String sql6 = "select count(*) from dba_segments where extents>4000";

		try {
			Vector waitevent = this.executeSQL(sql2);
			for (int i = 0; i < waitevent.size(); i++) {
				Vector event = (Vector) waitevent.get(i);
				setResult((String) event.get(1), unitId + "-"
						+ (String) event.get(0), "CM-10-12-10-27");
			}
			Vector extentsize = this.executeSQL(sql3);
			for (int i = 0; i < extentsize.size(); i++) {
				Vector evesize = (Vector) extentsize.get(i);
				setResult((String) evesize.get(3), unitId + "-"
						+ (String) evesize.get(0), "PM-10-12-10-28");
			}
			Vector extrecord = this.executeSQL(sql4);
			setResult(getMsg(extrecord), unitId, "PM-10-12-10-29");
			Vector extentNum = this.executeSQL(sql5);
			for (int i = 0; i < extentNum.size(); i++) {
				Vector exNum = (Vector) extentNum.get(i);
				setResult((String) exNum.get(3), unitId + "-"
						+ (String) exNum.get(1), "PM-10-12-10-30");
			}
			Vector extentRecord = this.executeSQL(sql6);
			setResult(getMsg(extentRecord), unitId, "PM-10-12-10-31");
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	private void _SYS_collParameter() {
		System.out.println("coll sys parameter start!_SYS_collParameter");
		
		String KBP_ID = kbpClass + "-10:" + deviceId + "-SYS";
		
		String sql = "select NAME from v$database";
		Vector vdbname = this.executeSQL(sql);

		for (int i = 0; i < vdbname.size(); i++) {
			Vector vv = (Vector) vdbname.get(i);
			String dbname = (String) vv.get(0);
			this.setResult(dbname, KBP_ID, "CM-00-03-001-01");
		}
		sql = "select instance_name from v$instance";
		Vector instanceName = this.executeSQL(sql);
		if(instanceName.size()>0){
			instanceName = (Vector)instanceName.get(0);
			String theName = (String) instanceName.get(0);
//			System.out.println("instance name:"+theName);
			this.setResult(theName, KBP_ID, "CM-00-03-001-010");
		}
//		String sql11 = "select value from v$parameter where name='compatible'";
		String sql11 = "SELECT version FROM product_component_version"+
		 				" WHERE SUBSTR(product,1,6)='Oracle'";
		Vector versionMsg = this.executeSQL(sql11);
		String version = getMsg(versionMsg);
		setResult(version, KBP_ID, "CM-00-03-001-02");
		System.out.println("oracle10G version:"+version);
		
		String sql13 = "select OPEN_MODE from v$database";
		Vector install = this.executeSQL(sql13);
		setResult(getMsg(install), KBP_ID, "CM-00-03-001-04");
		
		String sql14 = "select LOG_MODE from v$database";
		Vector logmethod = this.executeSQL(sql14);
		setResult(getMsg(logmethod), KBP_ID, "CM-00-03-001-05");
		
//		String sql15 = "select name||' value '||value from v$parameter where name = 'log_archive_dest'";
		String sql15 = "SELECT SUBSTR(value,10,LENGTH(value)) FROM v$parameter WHERE name LIKE 'log_archive_dest%' and value LIKE 'location=%'";
		Vector logdir = this.executeSQL(sql15);
		System.out.println("oracle10G log:"+getMsg(logdir));
		setResult(getMsg(logdir), KBP_ID, "CM-00-03-001-06");
		
		this.setResult(this.ipAddr, KBP_ID, "CM-00-03-001-11");
		
		String sql18 = "select VALUE/1024/1024  from v$parameter where NAME = 'shared_pool_size'";
		Vector oraclesharemem = this.executeSQL(sql18);
		setResult(getMsg(oraclesharemem), KBP_ID, "CM-00-03-002-01");
		
		String sql16 = "select sum(value/1024/1024) from v$sga";
		Vector ossharemem = this.executeSQL(sql16);
		setResult(getMsg(ossharemem), KBP_ID, "CM-00-03-002-02");
		
		Vector oraclePGA = this.executeSQL(sql18);
		setResult(getMsg(oraclePGA), KBP_ID, "CM-00-03-002-03");
		
		String sql22 = "select value  from v$parameter where NAME = 'undo_tablespace'";
		Vector rallname = this.executeSQL(sql22);
		String[] rallnames = getMsg(rallname, 2);
//		System.out.println("zifuchuanshuzu size:"+rallname.size());
		for (int i = 0; i < rallnames.length; i++) {
			setResult(rallnames[i], KBP_ID, "CM-00-03-005-01");
		}
		
		String sql23 = "SELECT sum(bytes/1024/1024) FROM dba_data_files where tablespace_name='UNDOTBS1'";
		Vector rallsize = this.executeSQL(sql23);
		setResult(getMsg(rallsize), KBP_ID, "CM-00-03-005-02");
		

		String sql1 = "select count(*) from v$session"; 
		Vector sessionMax = this.executeSQL(sql1);
		setResult(getMsg(sessionMax), KBP_ID, "PM-00-03-009-03");

        //add start 活动会话数量
        String sqlActiveSession = "SELECT COUNT(*) FROM v$session WHERE STATUS = 'ACTIVE'";
        Vector actSessionMax = this.executeSQL(sqlActiveSession);
        setResult(getMsg(actSessionMax), KBP_ID, "PM-00-03-009-04");
        //add end
		
		String sql2 = "select (value/1024/1024) as value from v$sga  where name='Variable Size'";
		Vector sgaMax = this.executeSQL(sql2);
		setResult(getMsg(sgaMax), KBP_ID, "CM-00-03-010-02");
		
		String sql3 = "select (value/1024/1024) as value from v$parameter where NAME = 'sga_max_size'";
		Vector sgaAutomanagerMax = this.executeSQL(sql3);
		setResult(getMsg(sgaAutomanagerMax), KBP_ID, "CM-00-03-010-03");
		
		String sql5 = "select (value/1024/1024) as value from v$parameter where name = 'db_cache_size'";
		Vector dbcache = this.executeSQL(sql5);
		setResult(getMsg(dbcache), KBP_ID, "CM-00-03-010-05");
		
		String sql6 = "select (value/1024/1024) as value from v$parameter where name like 'log_buffer'";
		Vector logcache = this.executeSQL(sql6);
		setResult(getMsg(logcache), KBP_ID, "CM-00-03-010-06");
		
		String sql7 = "select value from v$parameter where name = 'cpu_count'";
		Vector cpucount = this.executeSQL(sql7);
		setResult(getMsg(cpucount), KBP_ID, "CM-00-03-010-07");
		
		String sql20 = "select sum(a.bytes/1024/1024) from dba_data_files a";
		Vector dbsum = this.executeSQL(sql20);
		setResult(getMsg(dbsum), KBP_ID, "PM-10-12-10-25");
		
		String sql21 = "select sum(bytes/1024/1024) from dba_free_space";
		Vector dbfreesum = this.executeSQL(sql21);
		setResult(getMsg(dbfreesum), KBP_ID, "PM-10-12-10-26");
		
		String sql12 = "SELECT status FROM product_component_version  WHERE SUBSTR(product,1,6)='Oracle'";
		
//		String sql12 = "select substr(BANNER,60) from v$version where BANNER like 'Oracle%'";
		Vector oraclebit = this.executeSQL(sql12);
		System.out.println("oracle10G bit:"+getMsg(oraclebit));
		setResult(getMsg(oraclebit), KBP_ID, "CM-00-03-001-03");
		
/**
		String sql8 = "select value from v$parameter where name = 'buffer_pool_keep'";// buffer_pool_keep
		String sql9 = "select value from v$parameter where name = 'buffer_pool_recycle'";// buffer_pool_recycle
		String sql10 = "select BANNER from v$version"; // compatible
*/

	}

	private void _SYS_collPerformance() {
		String KBP_ID = kbpClass + "-10:" + deviceId + "-perf";
		
		/*String db_state = getDBState(); 
		setResult(db_state, KBP_ID, "PM-00-03-900-01");*/
		
		String sql17 = "SELECT status FROM v$instance";
		Vector instance_status = this.executeSQL(sql17);
		setResult(getMsg(instance_status), KBP_ID, "FM-00-03-001-01");
		
		String sql19 = "select count(*) from v$locked_object";
		Vector lockNUM = this.executeSQL(sql19);
		setResult(getMsg(lockNUM), KBP_ID, "PM-00-03-008-01");
		
		String sql24 = "select/*+ rule */ count(*) from v$lock where request<>0";
		Vector lockwaits = this.executeSQL(sql24);
		setResult(getMsg(lockwaits), KBP_ID, "PM-00-03-008-02");
		
		String sql25 = "SELECT (1 - phy.value  / (cur.value + con.value))*100 ";
		sql25 += " \"CACHE HIT RATIO\" FROM   v$sysstat cur, v$sysstat con, v$sysstat phy ";
		sql25 += "WHERE  cur.name = 'db block gets' AND    con.name = 'consistent gets' AND phy.name = 'physical reads'";
		Vector dbbuffer = this.executeSQL(sql25);
		setResult(Formater.getByScale(getMsg(dbbuffer),2), KBP_ID, "PM-10-12-10-14");
		
		String sql26 = "select (1-( sum(getmisses/1024/1024)/ sum(gets/1024/1024)))*100 from v$rowcache ";
		Vector probabily2 = this.executeSQL(sql26);
		setResult(Formater.getByScale(getMsg(probabily2),2), KBP_ID + "-pool", "PM-10-12-10-12");
		
		String sql27 = "select gethitratio*100 from v$librarycache where namespace in ('SQL AREA','TABL/PROCEDURE')";
		Vector probabily = this.executeSQL(sql27);
		String[] args = this.getMsg(probabily, 2);
		for (int i = 0; i < args.length; i++) {
			setResult(Formater.getByScale(args[i],2), KBP_ID + "-pool" + i, "PM-10-12-10-13");
		}
		

	}
	/**
	 *
	 * @2006-9-18
	 * 
	 */
	private void _SYS_collHistoryevent() {
		String KBP_ID = kbpClass + "-10:" + deviceId + "-datafile";
		// //db file scattered read
	}
	public void getTableSpace(HashMap param) {
		this.init(param);
		getTableSpace();
	}
	/**
	 *
	 */
	public void getTableSpace() {
		String KBP_ID = kbpClass + "-11:" + deviceId + "-TABLESPACES";

		String sql = "SELECT upper(d.tablespace_name), "+
			      " round(d.total_bytes, 3), "+
			      " round(d.total_bytes - f.total_free_bytes, 3), "+
			      " round(f.total_free_bytes, 3), "+
			      " round((d.total_bytes - f.total_free_bytes) / d.total_bytes * 100, 2) "+
			      " FROM (SELECT tablespace_name, SUM(bytes) / (1024 * 1024) total_free_bytes "+
			      " FROM dba_free_space "+
			      " GROUP BY tablespace_name) f, "+
			      " (SELECT dd.tablespace_name, SUM(dd.bytes) / (1024 * 1024) total_bytes "+
			      "  FROM dba_data_files dd "+
			      " GROUP BY dd.tablespace_name) d "+
			      " WHERE d.tablespace_name = f.tablespace_name "+
			      " ORDER BY upper(d.tablespace_name) ";
		Vector tablespaceDetail = this.executeSQL(sql);
		System.out.println("table size:"+tablespaceDetail.size());
		if(tablespaceDetail.size()>0){
			for(int i=0;i<tablespaceDetail.size();i++){
				Vector everyRecordDetail = (Vector) tablespaceDetail.get(i);
//				System.out.println("Vector value:"+everyRecordDetail.toString());
				String tableSpaceName = "";
				String tableSpaceSize = "";
				String tableSpaceUsed = "";
				String tableSpaceFree = "";
				String tableSpaceUsedRate = "";
//				System.out.println("record size:"+everyRecordDetail.size());
				if(everyRecordDetail.size()>4){
					
					tableSpaceName = (String) everyRecordDetail.get(0);
					tableSpaceSize = (String) everyRecordDetail.get(1);
					tableSpaceUsed = (String) everyRecordDetail.get(2);
					tableSpaceFree = (String) everyRecordDetail.get(3);
					tableSpaceUsedRate = (String) everyRecordDetail.get(4);
					if(tableSpaceUsed.indexOf(".")==0){
						tableSpaceUsed = "0"+tableSpaceUsed;
					}
					if(tableSpaceSize.indexOf(".")==0){
						tableSpaceSize = "0"+tableSpaceSize;
					}
					if(tableSpaceFree.indexOf(".")==0){
						tableSpaceFree = "0"+tableSpaceFree;
					}
					if(tableSpaceUsedRate.indexOf(".")==0){
						tableSpaceUsedRate = "0"+tableSpaceUsedRate;
					}
				}
				String tablespace_name = Formater.neatenunitid(tableSpaceName);
				setResult(tableSpaceName, KBP_ID + "-" +tablespace_name, "CM-00-03-003-01");
				setResult(tableSpaceSize, KBP_ID + "-" + tablespace_name,"CM-00-03-003-02");
				setResult(tableSpaceUsed, KBP_ID + "-" + tablespace_name, "PM-00-03-003-03");
				setResult(tableSpaceFree, KBP_ID + "-" + tablespace_name, "PM-00-03-904-01");
				setResult(Formater.getByScale(String.valueOf(tableSpaceUsedRate), 2), KBP_ID + "-" + tablespace_name,"PM-00-03-004-01");
				System.out.println("tablename:"+tablespace_name+",tablesize:"+tableSpaceSize+",tableused:"+tableSpaceUsed+",tablefree:"+tableSpaceFree+",tablerate:"+tableSpaceUsedRate);
			}
		}
	}

	/**
	 */

	public void getDataFile() {
		String KBP_ID = kbpClass + "-12:" + deviceId + "-DATAFILE";
		//String sql_all = "SELECT df.file_name,df.status,round((df.bytes-sum(fs.bytes))/df.bytes*100,2) FROM dba_free_space fs,dba_data_files df WHERE fs.file_id=df.file_id GROUP BY df.file_name,df.bytes,df.status ORDER BY df.file_name";
		String sql_all = "SELECT df.file_name,df.status FROM dba_data_files df  GROUP BY df.file_name,df.bytes,df.status ORDER BY df.file_name";
		
		System.out.println("befor execute Sql");
		Vector CM = this.executeSQL(sql_all);
		System.out.println("end execute Sql");
		
		for (int i = 0; i < CM.size(); i++) {
			Vector row = (Vector) CM.get(i);
			String filename = getFileName((String) row.get(0));
			setResult((String) row.get(0), KBP_ID + "-" + filename,
			"CM-00-03-006-01");
			setResult((String) row.get(1), KBP_ID + "-" + filename,
			"FM-10-12-12-10");			
		}
	}
	/**
	 * @param tbn
	 * @param owner
	 */

	public void getSpecialTable(String tbn, String owner) {
		String KBP_ID = kbpClass + "-10:" + deviceId + "-datafile";
		String sql = "select table_name, initial_extent,next_extent,max_extents ,PARTITIONED from dba_tables where owner='"
				+ owner + "'" + " and table_name='" + tbn + "'";
		String sql1 = "select EXTENTS from  DBA_SEGMENTS where owner='" + owner
				+ "'" + "" + " and segment_name='" + tbn + "'"
				+ " and segment_type='TABLE'";
		Vector spec = this.executeSQL(sql);
		for (int i = 0; i < spec.size(); i++) {
			Vector vec = (Vector) spec.get(i);
			setResult((String) vec.get(0), KBP_ID, "CM-00-03-004-01");
			setResult((String) vec.get(1), KBP_ID, "CM-00-03-004-02");
			setResult((String) vec.get(2), KBP_ID, "CM-00-03-004-03");
			setResult((String) vec.get(3), KBP_ID, "CM-00-03-004-04");
			setResult((String) vec.get(4), KBP_ID, "CM-00-03-004-05");
		}
		// )չ����
		Vector ext = this.executeSQL(sql1);
		setResult(getMsg(ext), KBP_ID, "PM-00-03-002-01");

	}

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

	/**
	 * 初始化采集参数
	 * @param params
	 */
	private void init(HashMap<String, String> params) {
		ipAddr = params.get("IP_ADDR");
		sid = params.get("SID");
		username = params.get("USER_NAME");
		port = params.get("PORT");
		password = DES3.decrypt(params.get("PASSOWRD"));

		deviceId = params.get("DEVICE_ID");
		if (deviceId == null || deviceId.equals("")) {
			deviceId = sid;
		}
		deviceId = Formater.neatenunitid(deviceId);
		
		this.target = new SQLTarget(ipAddr, port, sid, username, password);
	}

	/**
	 * 执行SQL
	 * @param sqlStr
	 * @return
	 */
	private Vector<Vector<String>> executeSQL(String sqlStr) {
		logger.info("Excute sql:" + sqlStr);
		Connection conn = null;
		ResultSet rs = null;
		PreparedStatement prep = null;
		Vector<String> in = null;
		Vector<Vector<String>> out = new Vector<Vector<String>>();

		try {
			conn = this.target.getConnection();
			prep = conn.prepareStatement(sqlStr);
			rs = prep.executeQuery();
			int column = rs.getMetaData().getColumnCount();

			while (rs.next()) {
				in = new Vector<String>();
				String line;
				for (int i = 1; i < column + 1; i++) {
					in.add(rs.getString(i));
				}
				out.add(in);
			}
		} catch (Exception e) {
			logger.error("Exception while executeSQL:" + sqlStr, e);
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
				}
			}
			if (prep != null) {
				try {
					prep.close();
				} catch (SQLException e) {
				}
			}
		}
		return out;
	}

	/**
	 *  采集数据库状态
	 * @throws 
	 * @since　Ver 1.1
	 */
	public void getDBState() throws Exception {
		Connection conn = this.target.getConnection();
		if (conn == null || conn.isClosed()) {
			throw new Exception(
					"Collect params is wrong or Database is Down: " + ipAddr + "--" + username + "--" + password);
		} else {
			logger.info("Init db connection : " + ipAddr + "--" + username + "--" + password);
		}
	}

	public String executeShell(String cmd) {
		InputStream in = null;
		BufferedReader buf = null;
		String result = "";
		try {
			Process proc = Runtime.getRuntime().exec(cmd);
			in = proc.getInputStream();
			buf = new BufferedReader(new InputStreamReader(in));
			String line = "";
			while ((line = buf.readLine()) != null) {
				result = line;
				// System.out.println(line);
			}
		} catch (IOException e) {

			e.printStackTrace();
		} finally {
			try {
				in.close();
			} catch (IOException e1) {

				e1.printStackTrace();
			}
			try {
				buf.close();
			} catch (IOException e2) {

				e2.printStackTrace();
			}
		}

		return cmd;
	}

	/**
	 * @author maozr
	 * @date 2006-9-16
	 * @param result
	 */
	private String getMsg(Vector result) {
		String value = "";
		for (int i = 0; i < result.size(); i++) {
			Vector vv = (Vector) result.get(i);
			value = (String) vv.get(0);
		}
		return value;
	}

	/**
	 * @author maozr
	 * @date 2006-9-15
	 * @param result
	 * @param flag
	 */
	private String[] getMsg(Vector result, int flag) {
		String[] value = new String[result.size()];
		for (int i = 0; i < result.size(); i++) {
			Vector vv = (Vector) result.get(i);
			value[i] = (String) vv.get(0);

		}
		return value;
	}

	private void setResult(String value, String unitID, String kpiID) {
		try {
			if(value == null || value.equals("")){
				return;
			}
			TblATO_KPIDETAIL record;
			record = new TblATO_KPIDETAIL();
			record.setCLL_TIME(new java.util.Date()); // �ɼ�����ĵ�ʱ��
			record.setKPI_ID(kpiID); // kpi���
			record.setKPI_VALUE(value); // ����ɼ��õ���ֵ��Ϣ
			record.setUNIT_ID(unitID); // unit
			record.setCLL_TIME(new Date()); // ����
			result.add(record);
		} catch (Exception e) {
			logger.error(e);
		}
	}
	
	public static void main(String[] args) {
		
		HashMap<String,String> params = new HashMap<String, String>();
		params.put("IP_ADDR", "172.21.0.77");
		params.put("SID", "bnms");
		params.put("USER_NAME", "bnms4");
		params.put("PORT", "1527");
		params.put("PASSOWRD", DES3.encrypt("bnms4"));
		params.put("DEVICE_ID", "bnms");
		CollOracle10iBySql col = new CollOracle10iBySql();
		col.coll(params);
	}
}