package com.engine.kq.wfset.attendance.framework;

import java.math.BigDecimal;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import weaver.common.StringUtil;
import weaver.conn.ConnectionPool;
import weaver.conn.RecordSet;
import weaver.general.BaseBean;

/**
 * 
 * @author wcd
 * @version 1.0 2015-05-15
 */
public class BaseConnection extends BaseBean {

	protected String dbType = "";
	
	protected static final String SQL_WHERE = " where";
	
	protected static final String SQL_AND = " and";

	public BaseConnection() {
    RecordSet rs = new RecordSet();
		this.dbType = rs.getDBType();
	}
	
	public boolean isOracle() {
		return this.dbType.equalsIgnoreCase("oracle");
	}
	
	public boolean isSqlServer() {
		return this.dbType.equalsIgnoreCase("sqlserver");
	}
	
	public List<Map<String, Object>> executeQuery(String sql) {
		return executeQuery(sql, ConnectionPool.getInstance().getConnection());
	}
	
	public List<Map<String, Object>> executeQuery(String sql, Connection conn) {
		PreparedStatement psmt = null;
		ResultSet rs = null;
		
		List<Map<String, Object>> result = new ArrayList<Map<String, Object>>();
		if(conn == null) return result;
		try {
			psmt = conn.prepareStatement(sql);
			rs = psmt.executeQuery();
			List<String> columnNames = new ArrayList<String>();
			ResultSetMetaData rsMetaData = rs.getMetaData();
			int columnCount = rsMetaData.getColumnCount();
			for (int i = 0; i < columnCount; ++i) columnNames.add(rsMetaData.getColumnName(i+1));
			Map<String, Object> columnMap = null;
			while (rs.next()){
				columnMap = new HashMap<String, Object>();
				for (String name : columnNames) columnMap.put(name, rs.getObject(name));
				result.add(columnMap);
			}
		} catch (SQLException e) {
			writeLog(e);
		} finally {
			close(rs, psmt, conn);
		}
		return result;
	}
	
	public void executeUpdate(String sql){
		executeUpdate(sql, ConnectionPool.getInstance().getConnection(), new Object[]{});
	}
	
	public void executeUpdate(String sql, Connection conn, Object... obj){
		if(conn == null) return;
		PreparedStatement psmt = null;
		try {
			psmt = initPreparedStatement(conn.prepareStatement(sql), obj);
			psmt.executeUpdate();
		} catch (SQLException e) {
			writeLog(e);
		} finally {
			close(null, psmt, conn);
		}
	}
	
	private PreparedStatement initPreparedStatement(PreparedStatement ps, Object... obj) {
		if(obj == null) return ps;
		try {
			int i = 1;
			for (Object param : obj) {
				if (param instanceof Integer) {
					ps.setInt(i, StringUtil.parseToInt("" + param));
				} else if (param instanceof Long) {
					ps.setLong(i, StringUtil.parseToLong("" + param));
				} else if ((param instanceof Float)) {
					ps.setFloat(i, StringUtil.parseToFloat("" + param));
				} else if ((param instanceof Double)) {
					ps.setDouble(i, StringUtil.parseToDouble("" + param));
				} else if (param instanceof BigDecimal) {
					ps.setBigDecimal(i, (param instanceof BigDecimal) ? (BigDecimal) param : new BigDecimal(StringUtil.parseToDouble("" + param)));
				} else if ((param instanceof java.sql.Date)) {
					ps.setDate(i, (java.sql.Date) param);
				} else if (((param instanceof String)) || ((param instanceof Character))) {
					ps.setString(i, "" + param);
				} else if (param == null) {
					ps.setNull(i, 0);
				} else if ((param instanceof Clob)) {
					ps.setClob(i, (Clob) param);
				} else if ((param instanceof Blob)) {
					ps.setBlob(i, (Blob) param);
				} else {
					ps.setObject(i, param);
				}
				i++;
			}
		} catch (Exception e) {
		}
		return ps;
	}
	
	public boolean executeSql(String sql) {
    RecordSet rs = new RecordSet();
		return rs.executeQuery(sql, new Object[]{});
	}
	
	private void close(ResultSet rs, PreparedStatement pstmt, Connection conn){
		if(conn != null) try {conn.close();} catch (SQLException e) {}
		if(pstmt != null) try {pstmt.close();} catch (SQLException e) {}
		if(rs != null) try {rs.close();} catch (SQLException e) {}
	}
	
	protected String getJoinStr(String str) {
		return str.equals(SQL_WHERE) ? SQL_AND : str;
	}

	/**
	 * Oracle环境下 in 条件超过1000个即报错，所以选用in时，统一调用此方法
	 * @param ids 
	 * @param fieldname 字段名
	 * @return
	 */
	protected static String getSQLIn(String ids, String fieldname) {
		if (StringUtil.isNull(ids)) return "";

		String[] id = StringUtil.split(ids, ",");
		int len = id.length;
		if (len < 1000) return "and " + fieldname + " in (" + ids + ")";

		int count = 1000;
		int size = len % count;
		if (size == 0) {
			size = len / count;
		} else {
			size = (len / count) + 1;
		}

		StringBuilder builder = new StringBuilder();
		List ls = Arrays.asList(id);
		for (int i = 0; i < size; i++) {
			int fromIndex = i * count;
			int toIndex = Math.min(fromIndex + count, len);
			List tmpls = ls.subList(fromIndex, toIndex);
			if (i != 0)
				builder.append(" or ");
			builder.append(fieldname + " in (").append(
					tmpls.toString().replace("[", "").replace("]", "")).append(
					")");
		}
		return "and (" + builder.toString() + ")";
	}
}
