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.Collection;
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;

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

	private String kbpClass = "10-11-34"; // *M-00-03-005
	private String ip;
	private String username;
	private String password;
	private String port;
	private String dbserver;

	private boolean isConnect = false;

	private Connection conn = null;
	
	public CollSqlServerBySql(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");
	}
	
	public Vector<TblATO_KPIDETAIL> getConfig() {
		logger.info("Begin getConfig...");
		CollBase result = new CollBase();
		Statement stmt = null;
		ResultSet rs = null;
		try {
			stmt = conn.createStatement();
			// 数据库名
			result.addKPI(this.kbpClass + "-10:" + this.dbserver + "-SYS", "CM-00-03-001-01", dbserver);
			
			String sqlTotal = "SELECT @@version Version";
			System.out.println(sqlTotal);
			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";
				// 数据库版本
				result.addKPI(unitIdTotal, "CM-00-03-001-02", _version);
			}
			
			String sqlOther = "SELECT DB_NAME(database_id) AS DatabaseName, Name AS Logical_Name, Physical_Name, " +
					"CAST(size AS decimal(30,0))*8 AS Size, state_desc FROM sys.master_files";
			System.out.println(sqlOther);
			rs = stmt.executeQuery(sqlOther);
			while (rs.next()) {
//				String dbName = rs.getString("DatabaseName");
				String logicName = rs.getString("Logical_Name");
				String physicalName = rs.getString("Physical_Name");
				String size = rs.getString("Size");
				String stateDesc = rs.getString("state_desc");
				String unitIdOther = this.kbpClass + "-10:" + this.dbserver + "-" + logicName;
				// 数据库逻辑名@数据设备名
				result.addKPI(unitIdOther, "CM-00-03-006-01", logicName);
				// 物理路径
				result.addKPI(unitIdOther, "CM-00-03-005-03", physicalName);
				// 数据库存储文件大小
				result.addKPI(unitIdOther, "CM-00-03-005-04", size);
				// 数据库状态
				result.addKPI(unitIdOther, "FM-00-03-001-01", stateDesc);
			}			
		} catch (Exception e) {
			logger.error("Exception while getVersion()", e);
		} finally {
			clean(stmt, rs);
		}
		logger.info("End getConfig...");
		return result.KPISet;
	}

	public Collection<TblATO_KPIDETAIL> getPerform() {
		logger.info("Begin getConfig...");
		CollBase result = new CollBase();
		Statement stmt = null;
		ResultSet rs = null;
		try {
			String sql = "select last_run = lastrun, " + // 上次运行 sp_monitor 的时间
					"current_run = getdate(), " + // 本次运行 sp_monitor 的时间
					"seconds = datediff(ss, lastrun, getdate()), " + // sp_monitor 自运行以来所经过的秒数
					// cpu_busy 服务器计算机的 CPU 处理 SQL Server 工作所用的秒数
					"cpu_busy_total = convert(int, ((@@cpu_busy * convert(int, @@timeticks / 1000.0)) / 1000)), " +
					"cpu_busy_current = convert(int, (((@@cpu_busy - cpu_busy) * convert(int, @@timeticks / 1000.0)) / 1000)), " +
					"cpu_busy_percentage = convert(int, ((((@@cpu_busy - cpu_busy) * convert(int, @@timeticks / 1000.0)) / 1000) * 100) / datediff(ss, lastrun, getdate())), " +
					// io_busy SQL Server 在输入和输出操作上花费的秒数
					"io_busy_total = convert(int, ((@@io_busy * convert(int, @@timeticks / 1000.0)) / 1000)), " +
					"io_busy_current = convert(int, (((@@io_busy - io_busy) * convert(int, @@timeticks / 1000.0)) / 1000)), " +
					"io_busy_percentage = convert(int, ((((@@io_busy - io_busy) * convert(int, @@timeticks / 1000.0)) / 1000) * 100) / datediff(ss, lastrun, getdate())), " +
					// idle SQL Server 已空闲的秒数
					"idle_total = convert(int, ((convert(bigint, @@idle) * convert(int, @@timeticks / 1000.0)) / 1000)), " +
					//"idle_current = convert(int, (((@@idle - idle) * convert(int, @@timeticks / 1000.0)) / 1000)), " +
					//"idle_percentage = convert(int, ((((@@idle - idle) * convert(int, @@timeticks / 1000.0)) / 1000) * 100) / datediff(ss, lastrun, getdate())), " +
					// packets_received SQL Server 读取的输入数据包数
					"packets_received_total = @@pack_received, " +
					"packets_received_current = @@pack_received - pack_received, " +
					// packets_sent SQL Server 已写入的输出数据包数
					"packets_sent_total = @@pack_sent, " +
					"packets_sent_current = @@pack_sent - pack_sent, " +
					// packet_errors SQL Server 在读取和写入数据包时遇到的错误数
					"packet_errors_total = @@packet_errors, " +
					"packet_errors_current = @@packet_errors - pack_errors, " +
					"total_read = @@total_read, " + // SQL Server 读取的次数
					"current_read = @@total_read - total_read, " +
					"total_write = @@total_write, " + // SQL Server 写入的次数
					"current_write = @@total_write - total_write, " +
					"total_errors = @@total_errors, " + // SQL Server 在读取和写入时遇到的错误数
					"current_errors = @@total_errors - total_errors, " +
					// connections 登录或尝试登录 SQL Server 的次数
					"connections_total = @@connections, " +
					"connections_current = @@connections - connections " +
					"from master.dbo.spt_monitor";
			System.out.println(sql);
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				String cpu_busy_total = rs.getString("cpu_busy_total");
				String cpu_busy_current = rs.getString("cpu_busy_current");
				String cpu_busy_percentage = rs.getString("cpu_busy_percentage");
				String io_busy_total = rs.getString("io_busy_total");
				String io_busy_current = rs.getString("io_busy_current");
				String io_busy_percentage = rs.getString("io_busy_percentage");
				String idle_total = rs.getString("idle_total");
//				String idle_current = rs.getString("idle_current");
//				String idle_percentage = rs.getString("idle_percentage");
				String packets_received_total = rs.getString("packets_received_total");
				String packets_received_current = rs.getString("packets_received_current");
				String packets_sent_total = rs.getString("packets_sent_total");
				String packets_sent_current = rs.getString("packets_sent_current");
				String packet_errors_total = rs.getString("packet_errors_total");
				String packet_errors_current = rs.getString("packet_errors_current");
				String total_read = rs.getString("total_read");
				String current_read = rs.getString("current_read");
				String total_write = rs.getString("total_write");
				String current_write = rs.getString("current_write");
				String total_errors = rs.getString("total_errors");
				String current_errors = rs.getString("current_errors");
				String connections_total = rs.getString("connections_total");
				String connections_current = rs.getString("connections_current");
				String unitId = this.kbpClass + "-11:" + this.dbserver;
				// CPU处理的秒数（重启后）
				result.addKPI(unitId, "PM-00-03-005-03", cpu_busy_total);
				// CPU处理的秒数（上次统计后）
				result.addKPI(unitId, "PM-00-03-005-04", cpu_busy_current);
				// 
//				result.addKPI(unitId, "CM-00-03-009-033", cpu_busy_percentage);
				// I/O花费的秒数（重启后）
				result.addKPI(unitId, "PM-00-03-005-05", io_busy_total);
				// I/O花费的秒数（上次统计后）
				result.addKPI(unitId, "PM-00-03-005-06", io_busy_current);
				// 
//				result.addKPI(unitId, "CM-00-03-009-036", io_busy_percentage);
				// 已空闲的秒数（重启后）
				result.addKPI(unitId, "PM-00-03-005-07", idle_total);
				// 已空闲的秒数（上次统计后）
//				result.addKPI(unitId, "PM-00-03-005-08", idle_current);
				// 
//				result.addKPI(unitId, "CM-00-03-009-039", idle_percentage);
				// 读取的输入数据包数（重启后）
				result.addKPI(unitId, "PM-00-03-005-09", packets_received_total);
				// 读取的输入数据包数（上次统计后）
				result.addKPI(unitId, "PM-00-03-005-10", packets_received_current);
				// 写入的输出数据包数（重启后）
				result.addKPI(unitId, "PM-00-03-005-11", packets_sent_total);
				// 写入的输出数据包数（上次统计后）
				result.addKPI(unitId, "PM-00-03-005-12", packets_sent_current);
				// 数据包错误数（重启后）
				result.addKPI(unitId, "PM-00-03-005-13", packet_errors_total);
				// 数据包错误数（上次统计后）
				result.addKPI(unitId, "PM-00-03-005-14", packet_errors_current);
				// 读取的次数（重启后）
				result.addKPI(unitId, "PM-00-03-005-15", total_read);
				// 读取的次数（上次统计后）
				result.addKPI(unitId, "PM-00-03-005-16", current_read);
				// 写入的次数（重启后）
				result.addKPI(unitId, "PM-00-03-005-17", total_write);
				// 写入的次数（上次统计后）
				result.addKPI(unitId, "PM-00-03-005-18", current_write);
				// I/O的错误数（重启后）
				result.addKPI(unitId, "PM-00-03-005-19", total_errors);
				// I/O的错误数（上次统计后）
				result.addKPI(unitId, "PM-00-03-005-20", current_errors);
				// 登录的次数（重启后）
				result.addKPI(unitId, "PM-00-03-005-21", connections_total);
				// 登录的次数（上次统计后）
				result.addKPI(unitId, "PM-00-03-005-22", connections_current);
			}
		} catch (Exception e) {
			logger.error("Exception while getVersion()", e);
		} finally {
			clean(stmt, rs);
		}
		logger.info("End getConfig...");
		return result.KPISet;
	}
	
	public Vector<TblATO_KPIDETAIL> getHelpdb() {
		logger.info("Begin getHelpdb...");
		CollBase result = new CollBase();
		Statement stmt = null;
		CallableStatement stmtp = null;
		CallableStatement stmtpdbsize = null;
		ResultSet rs = null;
		ResultSet rsdbsize = 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 unitId = this.kbpClass + "-11:" + this.dbserver + "-" + name;
				
				try {
					String sqll = "use " + name;
					stmt = conn.createStatement();
					stmt.execute(sqll);
					String sql = "sp_spaceused";
					stmtpdbsize = conn.prepareCall(sql);
					rsdbsize = stmtpdbsize.executeQuery();
					while (rsdbsize.next()) {
						String unallocatedspace = rsdbsize.getString("unallocated space");
						unallocatedspace = unallocatedspace.substring(0, unallocatedspace.length() - 3);
						// 表空间剩余大小
						result.addKPI(unitId, "PM-00-03-904-01", unallocatedspace);
						String spaceused = Formater.dev2IntIntoFloat(String.valueOf(dbsize), String.valueOf(Float.valueOf(dbsize) + Float.valueOf(unallocatedspace)), 2);
						// 表空间使用率
						result.addKPI(unitId, "PM-00-03-004-01", spaceused);
					}
				} catch (Exception ex) {
					logger.error("Exception while getVersion()", ex);
				} finally {
					clean(stmtpdbsize, rsdbsize);
				}
				
				// 表空间名
				result.addKPI(unitId, "CM-00-03-003-01", name);
				// 表空间大小
				result.addKPI(unitId, "CM-00-03-003-02", dbsize.trim());
				// 数据库所有者
				result.addKPI(unitId, "CM-00-03-005-05", owner);
				// dbid
				result.addKPI(unitId, "CM-00-03-005-06", dbid);
				// 创建时间
				result.addKPI(unitId, "CM-00-03-001-07", created);
			}
		} catch (Exception e) {
			logger.error("Exception while getVersion()", e);
		} finally {
			clean(stmtp, rs);
		}
		logger.info("End getHelpdb...");
		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()) {
				String spid = rs.getString("spid");
				String dbid = rs.getString("dbid");
				String objid = rs.getString("ObjId");
				String indid = rs.getString("IndId");
				String type = rs.getString("Type");
				String resource = rs.getString("Resource");
				String mode = rs.getString("Mode");
				String status = rs.getString("Status");
				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> 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()) {
				String spid = rs.getString("spid");
				String ecid = rs.getString("ecid");
				String status = rs.getString("status");
				String loginame = rs.getString("loginame");
				String hostname = rs.getString("hostname");
				String blk = rs.getString("blk");
				String dbname = rs.getString("dbname");
				String cmd = rs.getString("cmd");
				String requestid = rs.getString("request_id");
				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;
	}

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

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

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