package com.sitech.ismp.coll.busi;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Map;
import java.util.Vector;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import org.apache.log4j.Logger;

import com.sitech.ismp.coll.CollBase;
import com.sitech.ismp.coll.basic.TblATO_KPIDETAIL;
import com.sitech.util.DES3;
import com.sitech.util.JSONUtil;

/** 
 * ClassName:SqlCollThread
 * Description: SQL采集，采集结果写SWAP文件
 *
 * @author   Linxc
 * @version  
 * @since    Ver 1.1
 * @Date	 2012	Feb 17, 2012		12:25:53 PM
 */
public class JXSqlCollThread implements Runnable {
	private static Logger logger = Logger.getLogger("BUSI_COLL");
	/** 采集参数 */
	private HashMap<String, String> params;
	
	/** 主键对应的列名 */
	String pkColumnName = "";
	
	private Connection conn = null;

	public JXSqlCollThread(HashMap<String, String> params) {
		this.params = params;
	}

	public void run() {
		logger.info("-- [SQL_COLL] Begin exe collBySql...");

		try {
			this.conn = getJdbcConnection();
			if (this.conn == null) {
				return;
			} else {
				logger.info("-- [SQL_COLL] Get JDBC Connection Success!");
			}

			// key: ColumnName, value: KPI_ID
//			Map<String, String> kpiMap = getKpiMap();

			// key:变量名, value: 变量sql
			Map<String, String> paramSqlMap = getParamSqlMap();

			String collSql = getCollSql(paramSqlMap);
			logger.info("-- [SQL_COLL] Collect By Sql:\n" + collSql);
//			collBySql(collSql, kpiMap);
			collBySql(collSql);
		} catch (Exception e1) {
			logger.error("Exception while excute SqlCollThread", e1);
		} finally {
			if (null != conn) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}		
		logger.info("-- [SQL_COLL] End collBySql...");
	}

//	private void collBySql(String collSql, Map<String, String> kpiMap) {
	private void collBySql(String collSql) {
		CollBase collResult = new CollBase();
		String unitId = params.get("UNIT_ID");
		String kpiId = params.get("KPI_ID");
		String kpiValue = "";
		String interval = params.get("COLL_INTERVAL");
		
		Statement stmt = null;
		ResultSet rs = null;
		try {
			stmt = conn.createStatement();	
			rs = stmt.executeQuery(collSql);
			while (rs.next()) {
				kpiValue = rs.getString(1);
				collResult.addKPI(unitId, kpiId, kpiValue, interval);
				logger.info(unitId + "	" + kpiId + "	" + kpiValue);			
			}
			
			collResult.saveKPI2File();
		} catch (Exception e) {
			logger.error("-- [SQL_COLL]: Exception while collBySql.", e);
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException e) {
				logger.error("-- [SQL_COLL]: Exception while collBySql.", e);
			}
		}

	}

	/**
	 * 返回最终的采集SQL
	 * @throws Exception 
	 */
	private String getCollSql(Map<String, String> paramSqlMap) throws Exception {
		String collSql = this.params.get("SQL");
		
		collSql = collSql.replace(";", "");
		
		if (paramSqlMap != null && paramSqlMap.size() > 0) {
			for (String paramName : paramSqlMap.keySet()) {
				String sql = paramSqlMap.get(paramName);
				String value = exe(sql);
				collSql = collSql.replaceAll(paramName, value);
			}
		}
		return collSql;
	}
	
	/**
	 * 返回变量与变量对应的查询SQL
	 * key: 变量名,value: 查询SQL
	 */
	private Map<String, String> getParamSqlMap() {
		Map<String, String> paramSqlMap = new HashMap<String, String>();
		String subSqlJson = params.get("PARAMS");
		String logInfo = "ParamSqlMap:\nParam_Name\t\tParam_Sql";
		if (subSqlJson != null && !subSqlJson.equals("")) {
			JSONArray jsonArray1 = (JSONArray) JSONUtil.fromJSON(subSqlJson);
			for (int i = 0; i < jsonArray1.size(); i++) {
				JSONObject temp = (JSONObject) jsonArray1.get(i);
				// 变量名
				String columnName = "#" + temp.getString("varName") + "#";
				// 变量sql
				String columnAttr = temp.getString("value");
				
				paramSqlMap.put(columnName, columnAttr);
				
				logInfo += "\n"+columnName+"\t\t" + columnAttr;
			}
		}
		
		logger.info(logInfo);
		return paramSqlMap;
	}

//	/**
//	 * 返回每列的列名与其对应的KPI_ID
//	 * key: ColumnName,value: KPI_ID
//	 */
//	private Map<String, String> getKpiMap() {
//		Map<String, String> kpiMap = new HashMap<String, String>();
//		
//		String kpis = params.get("KPIS");
//		JSONArray jsonArray = (JSONArray) JSONUtil.fromJSON(kpis);
//		
//		String logInfo = "KPI_MAP:\nColumn_Name\t\tKPI_ID\t\tKEY";
//
//		for (int i = 0; i < jsonArray.size(); i++) {
//			JSONObject temp = (JSONObject) jsonArray.get(i);
//			// SQL执行结果中的列名
//			String columnName = temp.getString("columnName");
//
//			// 主键列名
//			String columnAttr = temp.getString("columnAttr");
//
//			// 指标ID
//			String kpiId = temp.getString("kpiId");
//
//			if (columnName.equals(columnAttr)) {
//				pkColumnName = columnName;
//				logInfo += "\n" + columnName + "\t\t" + columnAttr + "\t\tY";
//			} else {
//				logInfo += "\n" + columnName + "\t\t" + columnAttr + "\t\tN";
//			}
//
//			if (kpiId == null || kpiId.equals("")) {
//				continue;
//			}
//			kpiMap.put(columnName, kpiId);
//		}
//		logger.info(logInfo);
//		return kpiMap;
//	}

	/**
	 * 初始化JDBC连接
	 * @throws 
	 * @since　Ver 1.1
	 */
	private Connection getJdbcConnection() throws Exception {		
		String className = params.get("CLASSNAME");
		String url = params.get("URL");
		String userName = params.get("USERNAME");
		String password = params.get("PASSWORD");
		password = DES3.decrypt(password);
		
		Class.forName(className);
		this.conn = DriverManager.getConnection(url, userName, password);
		
		return this.conn;
	}
	
	/**
	 * 执行SQL查询变量的值
	 * @throws SQLException 
	 * @throws 
	 * @since　Ver 1.1
	 */
	private String exe(String sql) throws SQLException {
		Statement stmt = null;
		ResultSet rs = null;
		
		String paramValue = null;
		try {
			stmt = this.conn.createStatement();
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				paramValue = rs.getString(1);
				break;
			}
		} catch (SQLException e) {
			logger.error("-- [SQL_COLL]: Exception while execute query sql: " + sql, e);
			throw e;
		} finally {
			if(rs != null){
				try {
					rs.close();
				} catch (SQLException e) {
					logger.error("-- [SQL_COLL]: Exception while close jdbc ResultSet.", e);
				}
			}			
			if(stmt != null){
				try {
					stmt.close();
				} catch (SQLException e) {
					logger.error("-- [SQL_COLL]: Exception while close jdbc Statement.", e);
				}
			}
		}
		
		logger.info("-- [SQL_COLL]: Execute sql: " + sql + "\nResult is: [" + paramValue + "]");
		return paramValue;
	}
	
	
	public static void main(String[] args) {
//		String kpis = "[{columnName:'A',columnAttr:'A',kpiName:'测试SQL_KPI',kpiId:'PM-10-00-00-07'},{columnName:'B',columnAttr:'0',kpiName:'323232',kpiId:'PM-01-2332'},{columnName:'DATE1',columnAttr:'0',kpiName:'aa',kpiId:'PM-10-00-99'}]";
		HashMap<String, String> params = new HashMap<String, String>();
		params.put("CLASSNAME", "oracle.jdbc.driver.OracleDriver");
		params.put("UNIT_ID", "11-15-19");
		params.put("KPI_ID", "PM-01");
		params.put("PASSWORD", "2b71c7b672499ed7b764792a49aa82819463e1a7a5d1d0ed");

		params.put("SQL", "select 'aaa' a ,'bbb' b,'#DATE#' as DATE1 from dual");
		params.put("URL", "jdbc:oracle:thin:@172.21.0.77:1527:bnms");
		
		params.put("USERNAME", "bnms15");
		
		params.put("PARAMS", "[{varName:'DATE',value:'select sysdate from dual'}]");
		
		Vector<TblATO_KPIDETAIL> rst = new JXSqlColl().collBySql(params);
		for(TblATO_KPIDETAIL d:  rst){
			System.out.println(d.toString());
		}
	}
}