package com.sitech.ismp.coll.database;

import java.sql.CallableStatement;
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.Vector;

import org.apache.log4j.Logger;

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

public class CollSqlServer2000BySql {
	
	private Logger logger = Logger.getLogger(CollSqlServer2000BySql.class);

	private String kbpClass = "10-11-34";
	private String ip;
	private String username;
	private String password;
	private String port;
	private String dbserver;

	private boolean isConnect = false;

	private Connection conn = null;

	public CollSqlServer2000BySql(HashMap<String, String> params) {
		this.ip = params.get("IP_ADDR");
		this.username = params.get("USER_NAME");
		this.password = params.get("PASSWORD");
		this.port = params.get("PORT");
		this.dbserver = params.get("DBSERVER");
	}

	/**
	 * jdbc:sqlserver://172.21.3.100:1433
	 * @return
	 */
	public boolean init() {
		String url = "jdbc:sqlserver://" + this.ip + ":" + this.port;
		String driver = "com.microsoft.jdbc.sqlserver.SQLServerDriver";
		try {
			Class.forName(driver);
			conn = DriverManager.getConnection(url, username, password);
			logger.info("Connect SQLServer 2000 Success: " + url);
			isConnect = true;
		} catch (Exception e) {
			LogUtil.error("Exception while get SQLServer 2000 connection, URL=" + url, e);
			isConnect = false;
		}
		return isConnect;
	}

	public void destroy() {
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				logger.error("Exception while close SQLServer 2008 connection.", e);
			}
		}
		conn = null;
	}

	private void clean(Statement stmt, ResultSet rs) {
		try {
			if (rs != null) {
				rs.close();
			}
			if (stmt != null) {
				stmt.close();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public Vector<TblATO_KPIDETAIL> getConfig() {
		logger.info("Begin getConfig...");
		CollBase result = new CollBase();
		Statement stmt = null;
		ResultSet rs = null;
		try {
			stmt = conn.createStatement();
			// 数据库名 @数据库服务名 CM-00-03-001-08
			result.addKPI(this.kbpClass + "-10:" + this.dbserver + "-SYS", "CM-00-03-001-08", dbserver);
			String sqlTotal = "SELECT @@version Version";
			rs = stmt.executeQuery(sqlTotal);
			while (rs.next()) {
				String version = rs.getString("Version");
				String _version = version.substring(0, version.indexOf("\n"));
				String unitIdTotal = this.kbpClass + "-10:" + this.dbserver + "-SYS";
				// 数据库版本 CM-00-03-001-02
				result.addKPI(unitIdTotal, "CM-00-03-001-02", _version.trim());
			}
			
			String sqlOther = "select dbid, name Logical_Name, crdate, filename, version from sysdatabases";
			rs = stmt.executeQuery(sqlOther);
			while (rs.next()) {
				String logicName = rs.getString("Logical_Name");
				String createDate = rs.getString("crdate");
				String physicalName = rs.getString("filename");
				String unitIdOther = this.kbpClass + "-11:" + this.dbserver + "-" + logicName;
				// 数据库逻辑名@数据设备名
				result.addKPI(unitIdOther, "CM-00-03-003-01", logicName);
				// 数据库创建时间
				result.addKPI(unitIdOther, "CM-00-03-001-07", createDate);
				// 数据库物理文件路径
				result.addKPI(unitIdOther, "CM-00-03-005-03", physicalName);
			}
		} catch (Exception e) {
			logger.error("Exception while getVersion()", e);
		} finally {
			clean(stmt, rs);
		}
		logger.info("End getConfig...");
		return result.KPISet;
	}

	public Vector<TblATO_KPIDETAIL> getSession() {
		logger.info("Begin getSession...");
		CollBase result = new CollBase();
		CallableStatement stmtp = null;
		ResultSet rs = null;
		int n = 0;
		try {
			stmtp = conn.prepareCall("sp_who");
			rs = stmtp.executeQuery();
			while (rs.next()) {
				n++;
			}
			String unitId = this.kbpClass + "-10:" + this.dbserver + "-SESSION";
			// 会话数量 PM-00-03-009-03
			result.addKPI(unitId, "PM-00-03-009-03", String.valueOf(n));
		} catch (Exception e) {
			logger.error("Exception while getSession()", e);
		} finally {
			clean(stmtp, rs);
		}
		logger.info("End getSession...");
		return result.KPISet;
	}

	public Vector<TblATO_KPIDETAIL> getLock() {
		logger.info("Begin getLock...");
		CollBase result = new CollBase();
		CallableStatement stmtp = null;
		ResultSet rs = null;
		int n = 0;
		try {
			stmtp = conn.prepareCall("sp_lock");
			rs = stmtp.executeQuery();
			while (rs.next()) {
				n++;
			}
			String unitId = this.kbpClass + "-10:" + this.dbserver + "-LOCK";
			// 锁数量 PM-00-03-008-01
			result.addKPI(unitId, "PM-00-03-008-01", String.valueOf(n));
		} catch (Exception e) {
			logger.error("Exception while getLock()", e);
		} finally {
			clean(stmtp, rs);
		}
		logger.info("End getLock...");
		return result.KPISet;
	}

	public Vector<TblATO_KPIDETAIL> getHelpdb() {
		logger.info("Begin getHelpdb...");
		CollBase result = new CollBase();
		Statement stmt = null;
		CallableStatement stmtp = null;
		ResultSet rs = null;
		ResultSet spaceusedrs = null;
		try {
			stmtp = conn.prepareCall("sp_helpdb");
			rs = stmtp.executeQuery();
			while (rs.next()) {
				String name = rs.getString("name");
				String dbsize = rs.getString("db_size");
				dbsize = dbsize.substring(0, dbsize.length() - 3);
				String owner = rs.getString("owner");
				String dbid = rs.getString("dbid");
				String created = rs.getString("created");
				String status = rs.getString("status");
				String unitId = this.kbpClass + "-11:" + this.dbserver + "-" + name;
				
				try {
					String sqlUse = "use " + name;
					stmt = conn.createStatement();
					stmt.execute(sqlUse);
					String sql = "select size / 128 as used, name, filename, case maxsize / 128 when 0 then 'no limit' else cast(maxsize / 128 as varchar(10)) end as 'total(M)' from sysfiles where fileid = 1";
					spaceusedrs = stmt.executeQuery(sql);
					while (spaceusedrs.next()) {
						String usedspace = spaceusedrs.getString("used");
						Float unallocatedspace = Float.parseFloat(dbsize.trim()) - Float.parseFloat(usedspace.trim());
						// 数据库空间剩余大小
						result.addKPI(unitId, "PM-00-03-904-01", unallocatedspace + "");
						String spaceused = Formater.dev2IntIntoFloat(usedspace.trim(), dbsize.trim(), 2);
						// 数据库空间使用率
						result.addKPI(unitId, "PM-00-03-004-01", spaceused);
					}
				} catch (Exception ex) {
					logger.error("Exception while getVersion()", ex);
				} finally {
					clean(stmt, spaceusedrs);
				}
				
				// 数据库空间大小
				result.addKPI(unitId, "CM-00-03-003-02", dbsize.trim());
			}
		} catch (Exception e) {
			logger.error("Exception while getVersion()", e);
		} finally {
			clean(stmtp, rs);
		}
		logger.info("End getHelpdb...");
		return result.KPISet;
	}

}