package com.sitech.ismp.coll.database;

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

public class CollSqlServer2008BySql {
	
	private Logger logger = Logger.getLogger(CollSqlServer2008BySql.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 sid;

	private boolean isConnect = false;

	private Connection conn = null;
	
	public CollSqlServer2008BySql(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.sid = params.get("SID");
	}
	
	public Vector<TblATO_KPIDETAIL> getConfig() {
		logger.info("Begin getConfig...");
		CollBase result = new CollBase();
		Statement stmt = null;
		ResultSet rs = null;
		try {
			String sql = "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 WHERE DB_NAME(database_id) = '" + this.sid + "'";
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			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 unitId = this.kbpClass + "-10:" + this.sid + "-" + logicName;
				// 数据库逻辑名@数据设备名
				result.addKPI(unitId, "CM-00-03-006-01", logicName);
				// 物理路径
				result.addKPI(unitId, "CM-00-03-005-03", physicalName);
				// 数据库存储文件大小
				result.addKPI(unitId, "CM-00-03-005-04", size);
				// 数据库状态
				result.addKPI(unitId, "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 + "-10:" + this.sid;
				// 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;
	}

	/**
	 * 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) {
			LogUtil.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;
	}
}