package com.sitech.ismp.coll.centercoll;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.Vector;

import org.apache.log4j.Logger;

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

public class CollBussinessDataoverWithJDBC {
	private String IP_ADDR = "";
	private String PORT = "";
	private String SID = "";
	private String USERNAME = "";
	// private String USERNAME="";
	private String PASSWORD = "";
	private String PRE_UNIT_ID = "";
	private Logger logger = Logger.getLogger("COLL");
	private Vector result = new Vector();

	public void init(HashMap params) {
		this.IP_ADDR = (String) params.get("IP_ADDR");
		this.PRE_UNIT_ID = (String) params.get("PRE_UNIT_ID");
		this.SID = (String) params.get("SID");
		this.PORT = (String) params.get("PORT");
		this.USERNAME = (String) params.get("USERNAME");
		String pass = (String) params.get("PASSWORD");
		pass = DES3.decrypt(pass);
		this.PASSWORD = pass;
	}

	/**
	 * @author maozr
	 * @date 2006-9-13
	 * @version 1.0
	 * @param sqlStr
	 */
	private Vector executeSQL(String sqlStr) {
		SQLTarget sql = new SQLTarget();
		Connection conn = null;
		ResultSet rs = null;
		PreparedStatement prep = null;
		Vector in = null;
		Vector out = new Vector();
		// Object obj = null;
		try {
			sql.init(this.IP_ADDR, this.PORT, this.SID, this.USERNAME,
					this.PASSWORD);
			conn = sql.getConnection();
			prep = conn.prepareStatement(sqlStr);
			rs = prep.executeQuery();
			int column = rs.getMetaData().getColumnCount();
			while (rs.next()) {
				in = new Vector();
				for (int i = 1; i < column + 1; i++) {
					// System.out.println("i=" + rs.getString(i));
					in.add(rs.getString(i));
				}
				out.add(in);
			}
		} catch (Exception e) {
			e.printStackTrace();
			logger.info("�ɼ�oracle10ִ��sqlʧ��:[" + sqlStr + "]");
		} finally {
			try {
				conn.close();
			} catch (SQLException e1) {
				logger.info("�ɼ�oracle10ִ��sqlʧ��!" + e1.toString());
			}
			sql.release();
		}
		return out;
	}

	private void setResult(String value, String unitID, String kpiID) {
		try {
			TblATO_KPIDETAIL record;
			record = new TblATO_KPIDETAIL();
			record.setCLL_TIME(new java.util.Date());
			record.setKPI_ID(kpiID);
			record.setKPI_VALUE(value);
			record.setUNIT_ID(unitID);
			record.setCLL_TIME(new Date());
			result.add(record);
		} catch (Exception e) {
			logger.error(e);
		}
	}

	/**
	 * @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;
	}

	public Vector collDataOverstock(HashMap params) {
		init(params);
		this.result.clear();
		String sql = "";

		String table_desc = "";
		if (params.get("TABLE_DESC") != null)
			table_desc = (String) params.get("TABLE_DESC");

		String alerm_bonnet = "";
		if (params.get("ALERM_BONNET") != null)
			alerm_bonnet = (String) params.get("ALERM_BONNET");

		String prefix = "";
		String postfix = "";
		String table_name = (String) params.get("TABLE_NAME");
		String table_name_hole = "";

		String condition = "";
		if (params.get("CONDITION") != null
				&& !((String) params.get("CONDITION")).equals("")) {
			condition = (String) params.get("CONDITION");
			condition = " where " + condition;
		}

		String link_char = "";

		try {
			if (params.get("PREFIX") != null
					&& !((String) params.get("PREFIX")).equals("")) {
				prefix = (String) params.get("PREFIX");
				if (prefix.toLowerCase().indexOf("yyyy") > -1
						|| prefix.toLowerCase().indexOf("mm") > -1
						|| prefix.toLowerCase().indexOf("dd") > -1) {
					prefix = prefix.replaceAll("mm", "MM");
					SimpleDateFormat bartDateFormat = new SimpleDateFormat(
							prefix);
					Date date = new Date();
					String todayDate = bartDateFormat.format(date);
					table_name_hole = todayDate + table_name;
					sql = "select count(*) from " + table_name_hole + condition;
					reportKPI(sql, table_name_hole, table_desc, alerm_bonnet);
				} else if (prefix.indexOf("-") > -1) {
					String[] limits = prefix.split("-");
					if (!isInteger(limits[1])) {
						link_char = limits[1].substring(limits[1].length() - 1,
								limits[1].length());
						limits[1] = limits[1].substring(0,
								limits[1].length() - 1);
					}
					int begin = new Integer(limits[0]).intValue();
					int end = new Integer(limits[1]).intValue();

					for (int i = begin, j = 0; i < end + 1; i++, j++) {
						if (begin < 10 && end < 10) {
							table_name_hole = i + link_char + table_name;
						} else if (begin < 10 && end > 9 && end < 100) {
							if (i < 10)
								table_name_hole = "0" + i + link_char
										+ table_name;
							else
								table_name_hole = i + link_char + table_name;
						} else
							table_name_hole = i + link_char + table_name;

						sql = "select count(*) from " + table_name_hole
								+ condition;
						reportKPI(sql, table_name_hole, table_desc,
								alerm_bonnet);
					}
				}
			} else if (params.get("POSTFIX") != null
					&& !((String) params.get("POSTFIX")).equals("")) {
				postfix = (String) params.get("POSTFIX");
				if (postfix.toLowerCase().indexOf("yyyy") > -1
						|| postfix.toLowerCase().indexOf("mm") > -1
						|| postfix.toLowerCase().indexOf("dd") > -1) {
					postfix = postfix.replaceAll("mm", "MM");
					SimpleDateFormat bartDateFormat = new SimpleDateFormat(
							postfix);
					Date date = new Date();
					String todayDate = bartDateFormat.format(date);
					table_name_hole = table_name + todayDate;
					sql = "select count(*) from " + table_name_hole + condition;
					reportKPI(sql, table_name_hole, table_desc, alerm_bonnet);
				} else if (postfix.indexOf("-") > -1) {
					String[] limits = postfix.split("-");
					if (!isInteger(limits[1])) {
						link_char = limits[1].substring(0, 1);
						limits[1] = limits[1].substring(1, limits[1].length());
					}
					int begin = new Integer(limits[0]).intValue();
					int end = new Integer(limits[1]).intValue();

					for (int i = begin, j = 0; i < end + 1; i++, j++) {
						if (begin < 10 && end < 10) {
							if (limits[0].length() == 1)
								table_name_hole = table_name + link_char + i;
							else if (limits[0].length() == 2)
								table_name_hole = table_name + link_char + "0"
										+ i;
						} else if (begin < 10 && end > 9 && end < 100) {
							if (i < 10)
								table_name_hole = table_name + link_char + "0"
										+ i;
							else
								table_name_hole = table_name + link_char + i;
						} else
							table_name_hole = table_name + link_char + i;

						sql = "select count(*) from " + table_name_hole
								+ condition;
						reportKPI(sql, table_name_hole, table_desc,
								alerm_bonnet);
					}
				}
			} else {
				sql = "select count(*) from " + table_name + condition;
				reportKPI(sql, table_name, table_desc, alerm_bonnet);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}

		return this.result;
	}

	public void reportKPI(String sql, String table_name, String table_desc,
			String alerm_bonnet) {
		logger.info("Executing sql:[ " + sql + "]");
		logger.info("Database :[ " + IP_ADDR + ":" + PORT + ":" + SID + "]");
		Vector over_count = this.executeSQL(sql);
		Vector vv = (Vector) over_count.get(0);
		String count = (String) vv.get(0);
		String isNormal = "";
		String unit_id = PRE_UNIT_ID + ":" + SID + "-" + table_name;
		setResult(table_name, unit_id, "CM-00-03-004-01");
		setResult(table_desc, unit_id, "CM-01-03-004-01");
		try {
			if (alerm_bonnet.equals("0")) {
				if (!count.equals("0")) {
					Thread.sleep(5);
					over_count = this.executeSQL(sql);
					vv = (Vector) over_count.get(0);
					count = (String) vv.get(0);
				}
				if (!count.equals("0")) {
					Thread.sleep(5);
					over_count = this.executeSQL(sql);
					vv = (Vector) over_count.get(0);
					count = (String) vv.get(0);
				}
			}
		} catch (InterruptedException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		if (new Integer(alerm_bonnet).intValue() < new Integer(count)
				.intValue())
			isNormal = "NO";
		else
			isNormal = "YES";
		setResult(count, unit_id, "PM-01-04-004-01");
		setResult(isNormal, unit_id, "FM-01-04-004-01");
	}

	public static boolean isInteger(String value) {
		try {
			Integer.parseInt(value);
			return true;
		} catch (NumberFormatException e) {
			e.printStackTrace();
			return false;
		}
	}

	public static void main(String[] argv) {
		CollBussinessDataoverWithJDBC coll = new CollBussinessDataoverWithJDBC();
		HashMap params = new HashMap();
		params.put("CONDITION", "");
		params.put("IP_ADDR", "10.153.171.67");
		params.put("PRE_UNIT_ID", "11-30-31-32");
		params.put("PASSWORD",
				"d7e0f13b4a856aeade81f165fe38d001e0fcf8f0134aaffc");
		params.put("PORT", "1521");
		params.put("POSTFIX", "");
		params.put("PREFIX", "");
		params.put("SID", "wcrmb1");
		params.put("TABLE_DESC", "��ʷ��¼");
		params.put("TABLE_NAME", "spmsordercomplete");
		params.put("USERNAME", "bnms");
		coll.collDataOverstock(params);
		for (int i = 0; i < coll.result.size(); i++) {
			TblATO_KPIDETAIL record = (TblATO_KPIDETAIL) coll.result.get(i);
			System.out.println(record.UNIT_ID + "|" + record.KPI_ID + "|"
					+ record.KPI_VALUE);
		}
	}
}