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.DES3;
import com.sitech.util.Formater;
import com.sitech.util.LogUtil;

public class CollSybaseASEBySql {
	
	private Logger logger = Logger.getLogger("COLL");
	private String kbpClass = "10-11-31";
	private String ip;
	private String username;
	private String password;
	private String port;
	private String dbserver;
	private boolean isConnect = false;
	private Connection conn = null;

	public CollSybaseASEBySql(HashMap<String, String> params) {
		this.ip = params.get("IP_ADDR");
		this.username = params.get("USER_NAME");
		this.password = DES3.decrypt(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();
		String unitId = this.kbpClass + "-10:" + this.dbserver + "-SYS";
		if (isConnect) {
			// 数据库状态
			result.addKPI(unitId, "FM-00-03-001-01", "UP");
		} else {
			result.addKPI(unitId, "FM-00-03-001-01", "DOWN");
		}
		Statement stmt = null;
		ResultSet rs = null;
		try {
			String sql = "SELECT name, value FROM sysconfigures";
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				String name = rs.getString(1);
				String value = rs.getString(2);
				System.out.println("name = " + name + ";valuse = " + value);
				if (name != null && value != null) {
					// 数据库使用最大内存
					if (name.equals("max memory")) {
						result.addKPI(unitId, "CM-00-03-007-01", value);
					}
					// 配置用户连接数
					if (name.equals("number of user connections")) {
						result.addKPI(unitId, "CM-00-03-007-02", value);
					}
					// 锁数目
					if (name.equals("number of locks")) {
						result.addKPI(unitId, "CM-00-03-007-03", value);
					}
					// 配置最大引擎数
					if (name.equals("max online engines")) {
						result.addKPI(unitId, "CM-00-03-007-04", value);
					}
				}
			}

			String memsql = "select (" +
					"select convert(decimal(10,0),c.value/512.00) from master.dbo.sysconfigures t ,master.dbo.syscurconfigs c " +
					"where t.comment like '%total physical memory%' and c.config=t.config) as fTotalPhyMem, (" +
					"select convert(decimal(10,0),c.value/512.00) from master.dbo.sysconfigures t ,master.dbo.syscurconfigs c " +
					"where t.comment like '%total logical memory%' and c.config=t.config) as fTotalLogicMem, (" +
					"select convert(decimal(10,0),c.value/512.00) from master.dbo.sysconfigures t ,master.dbo.syscurconfigs c " +
					"where t.comment like '%total data cache size%' and c.config=t.config) as fTotalDataCache, (" +
					"select convert(decimal(10,0),c.value/512.00) from master.dbo.sysconfigures t ,master.dbo.syscurconfigs c " +
					"where t.comment like '%Meta-Data Caches%' and c.config=t.config) as fMetadataCache, (" +
					"select convert(decimal(10,0),c.value/512.00) from master.dbo.sysconfigures t ,master.dbo.syscurconfigs c " +
					"where t.comment like '%procedure cache size%' and c.config=t.config) as fProcCache, (" +
					"select convert(decimal(10,0),c.value*100/c.memory_used) from master.dbo.sysconfigures t ,master.dbo.syscurconfigs c " +
					"where t.comment ='total data cache size' and c.config=t.config) as fDataHitRate, " +
					"convert(decimal(10,0),c.value*200/c.memory_used) as fProcHitRate " +
					"from master.dbo.sysconfigures t ,master.dbo.syscurconfigs c " +
					"where t.comment ='procedure cache size' and c.config=t.config";
			rs = stmt.executeQuery(memsql);
			while (rs.next()) {
				String fTotalPhyMem = rs.getString("fTotalPhyMem");
				String fTotalLogicMem = rs.getString("fTotalLogicMem");
				String fTotalDataCache = rs.getString("fTotalDataCache");
				String fMetadataCache = rs.getString("fMetadataCache");
				String fProcCache = rs.getString("fProcCache");
				String fDataHitRate = rs.getString("fDataHitRate");
				String fProcHitRate = rs.getString("fProcHitRate");
				// 共享内存的大小
				result.addKPI(unitId, "CM-00-03-002-01", fTotalPhyMem);
				// 共享内存的读命中率
				result.addKPI(unitId, "PM-00-03-001-02", fDataHitRate);
			}
		} catch (Exception e) {
			logger.error("Exception while getConfig()", e);
		} finally {
			clean(stmt, rs);
		}
		logger.info("End getConfig...");
		return result.KPISet;
	}

	public Vector<TblATO_KPIDETAIL> getVersion() {
		logger.info("Begin getVersion...");
		CollBase result = new CollBase();
		String unitId = this.kbpClass + "-10:" + this.dbserver + "-SYS";
		Statement stmt = null;
		ResultSet rs = null;
		try {
			String sql = "select (SELECT @@SERVERNAME) as fSvrName, (SELECT @@VERSION) as fDBVersion, (SELECT @@CPU_BUSY) as fCpuWorkTime, (SELECT @@IDLE) as fDBFreeTime, (select count(*) from master.dbo.sysdevices) as fDiskCount, (select count(*) from master.dbo.sysdatabases) as fDBCount, (select count(*) from master.dbo.sysengines) as fEngineCount, (select count(e.status) from master.dbo.sysengines e where status = 'online') as fEngineCountOnline, (select count(*) from master.dbo.sysservers) as fSvrCount, (select count(*)from master..syslocks l,master..spt_values v,master..sysprocesses p where l.type = v.number and v.type = 'L' and l.spid = p.spid) as fLockCount, count(*) as fTranCount from master.dbo.systransactions";
			// Adaptive Server Enterprise/15.7.0/EBF 20371 SMP ESD#02 /P/NT
			// (IX86)/Windows 2008 R2/ase157esd2/3109/32-bit/OPT/Sat Jul 07
			// 01:14:05 2012
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				String servername = rs.getString(1);
				String line = rs.getString(2);
				if (line == null && servername == null) {
					continue;
				}
				// 数据库服务名 CM-00-03-001-08
				result.addKPI(unitId, "CM-00-03-001-08", servername);

				String[] temp = line.split("/");
				// 数据库名 CM-00-03-001-01
				result.addKPI(unitId, "CM-00-03-001-01", dbserver);
				// 服务器模式 CM-00-03-002-04
				result.addKPI(unitId, "CM-00-03-002-04", temp[0]);
				// 数据库版本 CM-00-03-001-02
				result.addKPI(unitId, "CM-00-03-001-02", temp[1]);
				// 数据库位数 CM-00-03-001-03
				result
						.addKPI(unitId, "CM-00-03-001-03", temp[8]/*
																	 * .substring(0,
																	 * 2)
																	 */);
			}
		} catch (Exception e) {
			logger.error("Exception while getVersion()", e);
		} finally {
			clean(stmt, rs);
		}
		logger.info("End getVersion...");
		return result.KPISet;
	}

	public Vector<TblATO_KPIDETAIL> getConnNum() {
		logger.info("Begin getConnNum...");
		CollBase result = new CollBase();
		String unitId = this.kbpClass + "-10:" + this.dbserver + "-SESSION";
		Statement stmt = null;
		ResultSet rs = null;
		try {
			int locknum = 0;
			String sql = "sp_who";
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				String cmd = rs.getString("cmd");
				System.out.println(cmd);
				if (cmd != null) {
					locknum++;
				}
			}
			// 会话数量 PM-00-03-009-03 // 数据连接数
			result.addKPI(unitId, "PM-00-03-009-03", String.valueOf(locknum));

			String netIO = "select (SELECT @@pack_received) as fNetPackReads, (SELECT @@pack_sent) as fNetPackWrites, (SELECT @@PACKET_ERRORS) as fNetPackErrs, (SELECT @@IO_BUSY) as fIoTime, (SELECT @@TOTAL_READ) as fIoReads, (SELECT @@TOTAL_WRITE) as fIoWrites, @@TOTAL_ERRORS as fIoErrs";
			rs = stmt.executeQuery(netIO);
			String fNetPackReads = "";
			String fNetPackWrites = "";
			String fIoReads = "";
			String fIoWrites = "";
			while (rs.next()) {
				fNetPackReads = rs.getString("fNetPackReads");
				fNetPackWrites = rs.getString("fNetPackWrites");
				fIoReads = rs.getString("fIoReads");
				fIoWrites = rs.getString("fIoWrites");
			}
			// 自启动后从网络上读取的输入数据包数目
			result.addKPI(unitId, "PM-00-03-005-09", fNetPackReads);
			// 自上次启动后写到网络上的输出数据包数目
			result.addKPI(unitId, "PM-00-03-005-11", fNetPackWrites);
			// 自启动后读取磁盘的次数
			result.addKPI(unitId, "PM-00-03-005-15", fIoReads);
			// 自启动后写入磁盘的次数
			result.addKPI(unitId, "PM-00-03-005-17", fIoWrites);
		} catch (Exception e) {
			logger.error("Exception while getVersion()", e);
		} finally {
			clean(stmt, rs);
		}

		logger.info("End getConnNum...");
		return result.KPISet;
	}

	public Vector<TblATO_KPIDETAIL> getLockNum() {
		logger.info("Begin getLockNum...");
		CollBase result = new CollBase();
		Statement stmt = null;
		ResultSet rs = null;
		int n = 0;
		try {
			String sql = "sp_lock";
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				String str1 = rs.getString(1);
				System.out.println(rs.getString(1) + "<>" + rs.getString(2)
						+ "<>" + rs.getString(3) + "<>" + rs.getString(4)
						+ "<>" + rs.getString(5) + "<>" + rs.getString(6)
						+ "<>" + rs.getString(7) + "<>" + rs.getString(8)
						+ "<>" + rs.getString(9));
				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 getVersion()", e);
		} finally {
			clean(stmt, rs);
		}

		logger.info("End getLockNum...");
		return result.KPISet;
	}

	public Vector<TblATO_KPIDETAIL> getTableSpaces() {
		logger.info("Begin getTableSpaces...");
		CollBase result = new CollBase();
		Statement stmt = null;
		ResultSet rs = null;
		try {
			String sql = "sp_helpdb";
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
            //数据库页面大小 单位(B)
            ResultSet rs5 = getResultSet(conn, stmt, "select @@maxpagesize/1024");
            //rs5 结果 单位(KB)
            int pageSize = 0;
            while(rs5.next()){
                pageSize = rs5.getInt(1);
            }
			while (rs.next()) {
//				int aSize = 0;
//				int fLogSize = 0;
//				int aLogSize = 0;
				int fSize = 0;
				double sizeUse = 0;
				// double logUse = 0;
				String cDatebase = rs.getString("name");
				String dbid = rs.getString("dbid").trim();
				String dbsize = rs.getString("db_size");
				if (!cDatebase.trim().equals("master")
						&& !cDatebase.trim().equals("model")
						&& !cDatebase.trim().equals("sybsystemdb")
						&& !cDatebase.trim().equals("sybsystemprocs")
						&& !cDatebase.trim().equals("tempdb")
						&& !cDatebase.trim().equals("sybmgmtdb")) {
					System.out.print("[" + cDatebase + "][" + dbid + "][");
//					String sql1 = "select lct_admin('logsegment_freepages', "
//							+ dbid + ")";
//					String sql2 = "SELECT sum(size) FROM master.dbo.sysusages WHERE dbid = "
//							+ dbid + " and segmap != 4";
					String sql3 = "select device_fragments = v.name, str((curunreservedpgs(dbid, u.lstart,u.unreservedpgs) * "+pageSize+"), 16) from master.dbo.sysusages u, master.dbo.sysdevices v where u.dbid = "
							+ dbid
							+ " and u.vdevno = v.vdevno and v.low <= u.size + vstart and v.high >= u.size + vstart - 1 and v.status & 2 = 2";
//					String sql4 = "SELECT sum(size) FROM master.dbo.sysusages WHERE dbid = "
//							+ dbid + " AND segmap = 4";

//					ResultSet rs1 = getResultSet(conn, stmt, sql1);
//					ResultSet rs2 = getResultSet(conn, stmt, sql2);
					ResultSet rs3 = getResultSet(conn, stmt, sql3);
//					ResultSet rs4 = getResultSet(conn, stmt, sql4);

//					while (rs1.next()) {
//						String mid1 = rs1.getString(1);
//						if (mid1 != null && !mid1.equals("")) {
////							fLogSize = Integer.parseInt(mid1) * 2 / 1024;
//						}
//					}
//					while (rs2.next()) {
//						String mid2 = rs2.getString(1);
//						if (mid2 != null && !mid2.equals("")) {
//							aSize = Integer.parseInt(mid2) * 2 / 1024;
//						}
//					}
					while (rs3.next()) {
						String midDatebaseSize = rs3.getString(2).trim();
						fSize = fSize + Integer.parseInt(midDatebaseSize);
					}
					fSize = fSize / 1024;
//					while (rs4.next()) {
//						String mid4 = rs4.getString(1);
//						if (mid4 != null && !mid4.equals("")) {
//							aLogSize = Integer.parseInt(mid4) * 2 / 1024;
//						}
//					}
//					if (aSize != 0) {
						// sizeUse = ((double) aSize - (double) fSize) /
						// (double) aSize * 100;
						// sizeUse = ((double) aSize) / ((double) aSize +
						// (double) fSize) * 100;
						sizeUse = ((Double.parseDouble(dbsize.substring(0,
                                dbsize.length() - 3).trim()))-(double)fSize)
								/ (Double.parseDouble(dbsize.substring(0,
										dbsize.length() - 3).trim())) * 100;
						String unitId = this.kbpClass + "-11:" + this.dbserver
								+ "-" + cDatebase;
//						if (aLogSize != 0) {
							// logUse = ((double) aLogSize - (double) fLogSize)
							// / (double) aLogSize * 100;
//						}
//						System.out.println(aSize + "][" + fSize + "]{"
//								+ sizeUse + "}");
						// 数据库实例名
						// SaveKpiDetail.add(new TblATO_KPIDETAIL("", kbp_class
						// + cPid, new Date(), cDatebase));
						result.addKPI(unitId, "CM-00-03-003-01", String
								.valueOf(cDatebase));

						// 表空间大小
						result.addKPI(unitId, "CM-00-03-003-02", dbsize
								.substring(0, dbsize.length() - 3).trim());

						// 已使用数据空间大小
						// SaveKpiDetail.add(new TblATO_KPIDETAIL("", kbp_class
						// + cPid, new Date(), aSize + ""));
						// result.addKPI(unitId, "PM-00-03-009-03",
						// String.valueOf(aSize));
						// 剩余数据空间大小
						// SaveKpiDetail.add(new TblATO_KPIDETAIL("", kbp_class
						// + cPid, new Date(), fSize + ""));
						result.addKPI(unitId, "PM-00-03-904-01", String
								.valueOf(fSize));
						// 数据空间使用率
						// SaveKpiDetail.add(new TblATO_KPIDETAIL("", kbp_class
						// + cPid, new Date(), sizeUse + ""));
						result.addKPI(unitId, "PM-00-03-004-01", Formater
								.formatDecimalByScale(String.valueOf(sizeUse),
										4));
						// 已使用日志空间大小
						// SaveKpiDetail.add(new TblATO_KPIDETAIL("", kbp_class
						// + cPid, new Date(), aLogSize + ""));
						// result.addKPI(unitId, "PM-00-03-009-03",
						// String.valueOf(aLogSize));
						// 剩余日志空间大小
						// SaveKpiDetail.add(new TblATO_KPIDETAIL("", kbp_class
						// + cPid, new Date(), fLogSize + ""));
						// result.addKPI(unitId, "PM-00-03-009-03",
						// String.valueOf(fLogSize));
						// 日志空间使用率
						// SaveKpiDetail.add(new TblATO_KPIDETAIL("", kbp_class
						// + cPid, new Date(), logUse + ""));
						// result.addKPI(unitId, "PM-00-03-009-03",
						// String.valueOf(logUse));
//					}
				}
			}
		} catch (Exception e) {
			logger.error("Exception while getVersion()", e);
		} finally {
			clean(stmt, rs);
		}

		logger.info("End getTableSpaces...");
		return result.KPISet;
	}

	public Vector<TblATO_KPIDETAIL> getDatabaseSpace() {
		logger.info("Begin getTablespace...");
		CollBase result = new CollBase();
		Statement stmt = null;
		ResultSet rs = null;
		try {
			String sql = "sp_helpdb";
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				// String line = rs.getString(1);
				String name = rs.getString(1);
				String dbid = rs.getString(4).trim();
				System.out.println(name + "<>" + dbid);
				result.addKPISet(getDatabaseSpaceDetail(name, dbid));
			}
		} catch (Exception e) {
			logger.error("Exception while getVersion()", e);
		} finally {
			clean(stmt, rs);
		}

		logger.info("End getVersion...");
		return result.KPISet;
	}

	private Vector<TblATO_KPIDETAIL> getDatabaseSpaceDetail(String name,
			String dbid) {
		CollBase result = new CollBase();
		String unitId = this.kbpClass + "-11:" + this.dbserver + "-SYS";
		// if (!name.equals("master") && !name.equals("model") &&
		// !name.equals("sybsystemdb") && !name.equals("sybsystemprocs") &&
		// !name.equals("tempdb")) {
		Statement stmt = null;
		CallableStatement stmtp = null;
		ResultSet rs = null;
		try {
			String sql = "use " + name;
			stmt = conn.createStatement();
			stmt.execute(sql);

			stmtp = conn.prepareCall("sp_spaceused");
			rs = stmtp.executeQuery();

			while (rs.next()) {
				String subinfo1 = rs.getString("database_name");
				String subinfo2 = rs.getString("database_size");
				String subinfo3 = rs.getString(1);
				logger.debug(" subinfo is: " + subinfo1);
				logger.debug(" subinfo is: " + subinfo2);
				logger.debug(" subinfo is: " + subinfo3);

				// if (arrayinfo.length < 4) {
				// // ptms 150.0 MB
				// // 当前数据库的名称 当前数据库的大小
				// // 当前数据库的大小
				// System.out.println(" 当前数据库的大小 is: " + arrayinfo[1]);
				// SaveKpiDetail.add(new TblATO_KPIDETAIL("", kbp_class +
				// "total", new Date(), arrayinfo[1]));
				// } else {
				// // 1240 KB 116 KB 94 KB 1030 KB
				// // 保留的空间总量 数据使用的空间总量 索引使用的空间 未用的空间量
				// // 保留的空间总量
				// System.out.println(" 保留的空间总量 is: " + arrayinfo[0]);
				// SaveKpiDetail.add(new TblATO_KPIDETAIL("", kbp_class +
				// "total", new Date(), arrayinfo[0]));
				// // 数据使用的空间总量
				// System.out.println(" 数据使用的空间总量 is: " + arrayinfo[1]);
				// SaveKpiDetail.add(new TblATO_KPIDETAIL("", kbp_class +
				// "total", new Date(), arrayinfo[1]));
				// // 索引使用的空间
				// System.out.println(" 索引使用的空间 is: " + arrayinfo[2]);
				// SaveKpiDetail.add(new TblATO_KPIDETAIL("", kbp_class +
				// "total", new Date(), arrayinfo[2]));
				// // 未用的空间量
				// System.out.println(" 未用的空间量 is: " + arrayinfo[3]);
				// SaveKpiDetail.add(new TblATO_KPIDETAIL("", kbp_class +
				// "total", new Date(), arrayinfo[3]));
				// }
			}
		} catch (Exception e) {
			logger.error("Exception while getVersion()", e);
		} finally {
			clean(stmtp, rs);

			clean(stmt, rs);
		}
		// }
		return result.KPISet;
	}

	public ResultSet getResultSet(Connection conn, Statement stmt, String sql)
			throws SQLException {
		ResultSet rs = null;
		stmt = conn.createStatement();
		rs = stmt.executeQuery(sql);
		return rs;
	}

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

	public boolean init() {
		String url = "jdbc:sybase:Tds:" + this.ip + ":" + this.port/*
																	 * + "/" +
																	 * this.sid
																	 */
				+ "?charset=cp936&jconnect_version=3";
		String driver = "com.sybase.jdbc3.jdbc.SybDriver";
		try {
			Class.forName(driver);
			conn = DriverManager.getConnection(url, username, password);
			logger.info("Connect Sybase Success: " + url);
			isConnect = true;
		} catch (Exception e) {
			LogUtil.error("Exception while get Sybase 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 Sybase connection.", e);
			}
		}
		conn = null;
	}

}