package com.sitech.ismp.check.util;

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

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

import org.apache.log4j.Logger;

import com.sitech.ismp.messageObject.cc.CommandParameters;

/**
 * 
 */
public class SqlCollThread {
	
	private static Logger logger = Logger.getLogger("BUSI_COLL");
	
	/** 采集参数 */
	private HashMap<String, String> params;

	/** 主键对应的列名 */
	String pkColumnName = "";

	private Connection conn = null;

	public SqlCollThread(HashMap<String, String> params) {
		this.params = params;
	}
	
	public SqlCollThread(CommandParameters cps) {
		HashMap<String, String> _params = new HashMap<String, String>();
		_params.put("CLASSNAME", cps.getDB_DRIVER());
		_params.put("URL", cps.getDB_URL());
		_params.put("USERNAME", cps.getDB_USERNAME());
		_params.put("PASSWORD", cps.getDB_PASSWORD());
		_params.put("CONTENT", cps.getCONTENT());
		this.params = _params;
	}

	public String run() {
		logger.info("-- [SQL_COLL] Begin exe collBySql...");
		String queryRes = "";
		try {
			conn = getJdbcConnection();
			if (conn == null) {
				return "连接数据库失败";
			}
			String collSql = params.get("CONTENT");
			queryRes = collBySql(collSql);
		} catch (Exception e1) {
			logger.error("Exception while getJdbcConnection", e1);
		} finally {
			if (null != conn) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		logger.info("-- [SQL_COLL] End collBySql...");
		return queryRes;
	}

	private String collBySql(String collSql) throws SQLException {
		Statement stmt = null;
		ResultSet rs = null;
		String res = "";
		try {
			stmt = conn.createStatement();
			logger.info("待执行的SQL语句：" + collSql);
			rs = stmt.executeQuery(collSql);
			while (rs.next()) {
				for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
//					if (i != rs.getMetaData().getColumnCount()) {
					res = res + rs.getMetaData().getColumnName(i) + "=" + rs.getString(i) + "\n";
//					} else {
//						res = res + rs.getMetaData().getColumnName(i) + "=" + rs.getString(i) + "\n";
//					}
				}
			}
		} catch (Exception e) {
			res = "执行SQL语句失败：" + e.getMessage();
//			e.printStackTrace();
			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);
			}
		}
		return res;
	}
	
	public String createProc() {
		logger.info("-- [PROCEDURE_COLL] Begin exe createProc...");
		String createRes = "";
		try {
			conn = getJdbcConnection();
			if (conn == null) {
				return "FAIL";
			}
			String createProc = params.get("CONTENT");
			createRes = collByCreateResProcedure(createProc);
		} catch (Exception e1) {
			logger.error("Exception while getJdbcConnection", e1);
		} finally {
			if (null != conn) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		logger.info("-- [PROCEDURE_COLL] End createProc...");
		return createRes;
	}

	private String collByCreateResProcedure(String createProc) throws SQLException {
		Statement stmt = null;
		String res = "";
		try {
			String checkProc = "DROP PROCEDURE IF EXISTS INSERT_ABCD;";
			stmt = conn.createStatement();
//			stmt.executeUpdate(checkProc);
			stmt.executeUpdate(createProc);
			res = "存储过程成功完成执行";
		} catch (Exception e) {
			res = "存储过程执行失败结束";
			System.out.println(e.getMessage());
			logger.error("-- [PROCEDURE_COLL]: Exception while collByProcedurel.", e);
		} finally {
			try {
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException e) {
				logger.error("-- [PROCEDURE_COLL]: Exception while collByProcedurel.", e);
			}
		}
		return res;
	}
	
	public String execute() {
		logger.info("-- [PROCEDURE_COLL] Begin exe collByProcedurel...");
		String excuteRes = "";
		try {
			conn = getJdbcConnection();
			if (conn == null) {
				return "FAIL";
			}
			String collProc = params.get("CONTENT");
			String _collProc = "{call #proc_name#()}".replace("#proc_name#", collProc);
			excuteRes = collByProcedure(_collProc);
		} catch (Exception e1) {
			logger.error("Exception while getJdbcConnection", e1);
		} finally {
			if (null != conn) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		logger.info("-- [PROCEDURE_COLL] End collByProcedurel...");
		return excuteRes;
	}

	private String collByProcedure(String collProcedure) throws SQLException {
		CallableStatement proc = null;
		String res = "";
		try {
			proc = conn.prepareCall(collProcedure);
			proc.execute();
			res = "存储过程成功完成执行";
		} catch (Exception e) {
			res = "存储过程执行失败结束";
			logger.error("-- [PROCEDURE_COLL]: Exception while collByProcedurel.", e);
		} finally {
			try {
				if (proc != null) {
					proc.close();
				}
			} catch (SQLException e) {
				logger.error("-- [PROCEDURE_COLL]: Exception while collByProcedurel.", e);
			}
		}
		return res;
	}

	/**
	 * 返回最终的采集SQL
	 * 
	 * @throws Exception
	 */
	private String getCollSql(Map<String, String> paramSqlMap) throws Exception {
		String collSql = this.params.get("SQL");
		if (collSql.endsWith(";")) {
			collSql = collSql.substring(0, collSql.length() - 1);
		}

		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");
		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);
			}
		}
		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);
		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;
			}
			logger.info("PK:" + pkColumnName);
			if (kpiId == null || kpiId.equals("")) {
				continue;
			}
			kpiMap.put(columnName, kpiId);
		}
		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);

		String linkName = SameGUID.getSameGUID(url + userName);

		TblDBLinkInfo dbLinkInfo = new TblDBLinkInfo();
		dbLinkInfo.setLinkName(linkName);
		dbLinkInfo.setClassName(className);
		dbLinkInfo.setUrl(url);
		dbLinkInfo.setUserid(userName);
		dbLinkInfo.setPasswd(password);
		dbLinkInfo.setMinPoolSize(2);
		dbLinkInfo.setMaxPoolSize(5);
		dbLinkInfo.setMaxStatements(10);
		dbLinkInfo.setMaxIdleTime(60);

		try {
			JDBCPoolManager.getInstance().createPool(dbLinkInfo);
			conn = JDBCPoolManager.getInstance().getConnection(dbLinkInfo.getLinkName());
			return conn;
		} catch (Exception e) {
			logger.error("Exception while getJdbcConnection.", e);
			return null;
		}
	}

	/**
	 * 执行SQL查询变量的值
	 * 
	 * @throws SQLException
	 * @throws
	 * @since Ver 1.1
	 */
	private String exe(String sql) throws SQLException {
		Statement stmt = null;
		ResultSet rs = null;
		if (conn == null) {
			return null;
		}

		try {
			stmt = this.conn.createStatement();
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				return rs.getString(1);
			}
		} 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);
				}
			}
		}
		return null;
	}

	public static void main(String[] args) {
		String kpis = "[{columnName:'AAA',columnAttr:'1',kpiName:'测试SQL_KPI',kpiId:'PM-10-00-00-07'},{columnName:'BBB',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("KBPCLASS", "11-15-19");
		params.put("KPIS", kpis);
		params.put("PASSWORD", "2b71c7b672499ed7b764792a49aa82819463e1a7a5d1d0ed");
		params.put("SQL", "select 'aaa','bbb','#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 SqlColl().collBySql(params);
//		for (TblATO_KPIDETAIL d : rst) {
//			System.out.println(d.toString());
//		}
//
//		System.out.println("------------------------------------------");
//
//		Vector<TblATO_KPIDETAIL> rst1 = new SqlColl().collBySql(params);
//		for (TblATO_KPIDETAIL d : rst1) {
//			System.out.println(d.toString());
//		}
		String collProc = "insert_abcd";
		System.out.println("{call #proc_name#()}".replace("#proc_name#", collProc));
	}
}