package com.vci.server.omd.attribpool.service;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collections;
import java.util.List;

import org.dom4j.DocumentException;

import com.vci.common.log.ServerWithLog4j;
import com.vci.common.utility.ObjectUtility;
import com.vci.corba.common.VCIError;
import com.vci.corba.omd.atm.AttribItem;
import com.vci.omd.utils.AbComparator;
import com.vci.server.base.persistence.dao.HibernateSessionFactory;
import com.vci.server.omd.attribpool.APServiceImplHelper;

public class AttrPoolService {
	
	private static volatile AttrPoolService instance = new AttrPoolService();
	
	private AttrPoolService() {
		
	}
	
	public static AttrPoolService getInstance() {
		if (instance == null) {
			synchronized (AttrPoolService.class) {
				if (instance == null) {
					instance = new AttrPoolService();
				}
			}
		}
		
		return instance;
	}
	
	/**
	 * 查询属性
	 * @throws Exception 
	 */
	public boolean addAttribItem(AttribItem attribItem) throws Exception{
		boolean flag = false;
		String insertSql = "insert into plattribute values(?,?,?,?,?,?,?,?,?,xmltype(?))";
		Connection connection = HibernateSessionFactory.getSessionConnection();
		PreparedStatement pst = connection.prepareStatement(insertSql);
		pst.setString(1, ObjectUtility.getNewObjectID36());
		pst.setString(2, attribItem.name);
		pst.setString(3, attribItem.label);
		pst.setString(4, attribItem.description);
		long time = Calendar.getInstance().getTimeInMillis();
		Timestamp ts = new Timestamp(time);
		pst.setTimestamp(5, ts);
		pst.setString(6, attribItem.creator);
		pst.setTimestamp(7, ts);
		pst.setString(8, attribItem.modifier);
		pst.setTimestamp(9, ts);
		String xmlText = APServiceImplHelper.getInstance().getXmlText(attribItem);
		//CLOB content = APServiceImplHelper.getInstance().getXmlTypeContent(xmlText, connection);
		//pst.setObject(10, content);
		pst.setString(10, xmlText);
		pst.executeUpdate();
		flag = true;
		ServerWithLog4j.logger.info(insertSql);
		pst.close();
		HibernateSessionFactory.getSession().flush();
		return flag;
	}

	/**
	 * 修改属性
	 * @throws Exception 
	 */
	public boolean modifyAbItem(AttribItem attribItem) throws Exception{
		boolean flag = false;
		String sql = "update plattribute t set t.name=?, t.label=?, t.description=?, t.ts=?, t.creator=?, t.createtime=?, t.modifier=?, t.modifytime=?, t.content=xmltype(?) where t.oid=? and t.ts = ?";
		Connection conn = HibernateSessionFactory.getSessionConnection();
		PreparedStatement pst = conn.prepareStatement(sql);
		pst.setString(1, attribItem.name);
		pst.setString(2, attribItem.label);
		pst.setString(3, attribItem.description);
		long time = Calendar.getInstance().getTimeInMillis();
		Timestamp ts = new Timestamp(time);
		pst.setTimestamp(4, ts);
		pst.setString(5, attribItem.creator);
		pst.setTimestamp(6, new Timestamp(attribItem.createTime));
		pst.setString(7, attribItem.modifier);
		pst.setTimestamp(8, ts);
		String xmlText = APServiceImplHelper.getInstance().getXmlText(attribItem);
		//CLOB content = APServiceImplHelper.getInstance().getXmlTypeContent(xmlText, conn);
		//pst.setObject(9, content);
		pst.setString(9, xmlText);
		pst.setString(10, attribItem.oid);
		pst.setTimestamp(11, Timestamp.valueOf(attribItem.ts));
		pst.executeUpdate();
		ServerWithLog4j.logger.info(sql);
		pst.close();
		flag = true;
		return flag;
	}
	
	public boolean deleteAbItem(AttribItem att) throws Exception {
		boolean flag = false;
		String sql = "delete from plattribute where oid = ? and ts = ?";
		Connection connection = HibernateSessionFactory.getSessionConnection();
		PreparedStatement pst = connection.prepareStatement(sql);
		pst.setString(1, att.oid);
		String ts = att.ts;
		pst.setTimestamp(2, Timestamp.valueOf(ts));
		pst.executeUpdate();
		flag = true;
		ServerWithLog4j.logger.info(sql);
		pst.close();
		return flag;
	}

	public boolean xml2DB(String userName) throws VCIError {
//		List<AttribItem> news = Xml2DBDelegate.getInstance().getNews(userName);
//		if(news == null){
//			return true;
//		}
//		for(AttribItem o : news){
//			try{
//				addAttribItemNoCache(o);
//			}catch(Throwable e){
//				//e.printStackTrace();
//				ServerWithLog4j.logger.error(o.name + "迁移失败, 属性池的迁移中止!", e);
//				return false;
//			}
//		}
		return true;
	}
	
	public boolean addAttribItemNoCache(AttribItem attribItem) throws VCIError, SQLException, IOException{
		boolean flag = false;
		String insertSql = "insert into plattribute values(?,?,?,?,?,?,?,?,?,xmltype(?))";
		Connection connection = HibernateSessionFactory.getSessionConnection();
		PreparedStatement pst = connection.prepareStatement(insertSql);
		pst.setString(1, ObjectUtility.getNewObjectID36());
		pst.setString(2, attribItem.name);
		pst.setString(3, attribItem.label);
		pst.setString(4, attribItem.description);
		long time = Calendar.getInstance().getTimeInMillis();
		Timestamp ts = new Timestamp(time);
		pst.setTimestamp(5, ts);
		pst.setString(6, attribItem.creator);
		pst.setTimestamp(7, ts);
		pst.setString(8, attribItem.modifier);
		pst.setTimestamp(9, ts);
		String xmlText = APServiceImplHelper.getInstance().getXmlText(attribItem);
		//CLOB content = APServiceImplHelper.getInstance().getXmlTypeContent(xmlText, connection);
		//pst.setObject(10, content);
		pst.setString(10, xmlText);
		pst.executeUpdate();
		flag = true;
		ServerWithLog4j.logger.info(insertSql);
		pst.close();
		return flag;
	}

	public boolean deleteAbItemNoCache(AttribItem att) throws VCIError, SQLException {
		boolean flag = false;
		String sql = "delete from plattribute where oid = ? and ts = ?";
		Connection connection = HibernateSessionFactory.getSessionConnection();
		PreparedStatement pst = connection.prepareStatement(sql);
		pst.setString(1, att.oid);
		String ts = att.ts;
		pst.setTimestamp(2, Timestamp.valueOf(ts));
		pst.executeUpdate();
		flag = true;
		ServerWithLog4j.logger.info(sql);
		pst.close();
		return flag;
	}

	public AttribItem[] getAttribItems(String filter, int start,
			int rows) throws VCIError, SQLException, IOException, DocumentException{
		//String sql = "select oid, name, label, description, ts, creator, createtime, modifier, modifytime, t.content.getclobval() content from plattribute t";
		
		String sql = "";
		switch (HibernateSessionFactory.getDbType()) {
			case DM8:
				sql = "select oid, name, label, description, ts, creator, createtime, modifier, modifytime, content from plattribute t";
				break;
			case ORACL:
			default:
				sql = "select oid, name, label, description, ts, creator, createtime, modifier, modifytime, t.content.getclobval() content from plattribute t";
				break;
		}
		
		if(filter != null && !filter.equals("")){
			sql = sql + " where t.name like '" + filter + "%'";
		}
		sql += " ORDER BY name";
		
		Connection connection = HibernateSessionFactory.getSessionConnection();
		PreparedStatement pst = connection.prepareStatement(sql);
		ResultSet rs = pst.executeQuery();
		List<AttribItem> atts = new ArrayList<AttribItem>();
		while(rs.next()){
			AttribItem att = APServiceImplHelper.getInstance().getAttribute(rs);
			atts.add(att);
		}
		rs.close();
		pst.close();
		return atts.toArray(new AttribItem[0]);
	}
	
	/**
	 * 检查要插入的记录是否存在
	 * @throws SQLException 
	 */
	public boolean checkRowIsExists(String name) throws VCIError, SQLException{
		String sql = "select count(name) count from plattribute t where t.name = ?";
		Connection connection = HibernateSessionFactory.getSessionConnection();
		PreparedStatement pst = connection.prepareStatement(sql);
		pst.setString(1, name);
		ResultSet rs = pst.executeQuery();
		ServerWithLog4j.logger.info(sql);
		while(rs.next()){
			int count = rs.getInt("count");
			if(count > 0){
				return true;
			}else{
				return false;
			}
		}
		rs.close();
		pst.close();
		return false;
	}

	/**
	 * 批量删除属性: abItems
	 * @throws Exception 
	 */
	public boolean deleteAbItems(AttribItem[] abItems) throws Exception{
		for(AttribItem att : abItems){
			deleteAbItem(att);
		}
		return true;
	}

	/**
	 * 根据属性名获取属性
	 * @throws SQLException 
	 * @throws DocumentException 
	 * @throws IOException 
	 */
	public AttribItem[] getAttribItemsByNames(String[] attNames) throws VCIError, SQLException, IOException, DocumentException{
		if (attNames == null || attNames.length == 0) {
			return new AttribItem[0];
		}
		
		List<AttribItem> atts = new ArrayList<AttribItem>();
		//StringBuilder sql = new StringBuilder("select oid, name, label, description, ts, creator, createtime, modifier, modifytime, t.content.getclobval() content from plattribute t where ");
		StringBuilder sql = null;
		switch (HibernateSessionFactory.getDbType()) {
			case DM8:
				sql = new StringBuilder("select oid, name, label, description, ts, creator, createtime, modifier, modifytime, content from plattribute t where ");
				break;
			case ORACL:
			default:
				sql = new StringBuilder("select oid, name, label, description, ts, creator, createtime, modifier, modifytime, t.content.getclobval() content from plattribute t where ");
				break;
		}
		
		if(attNames.length > 0){
			for(String attName : attNames){
				sql.append("name = '" + attName + "'");
				sql.append(" or ");
			}
		} else {
			sql.append(" 1=1 or ");
		}
		
		sql.append(" ORDER BY name");
		
		String sql_ = sql.substring(0, sql.lastIndexOf(" or "));
		Connection connection = HibernateSessionFactory.getSessionConnection();
		PreparedStatement pst = connection.prepareStatement(sql_);
		ServerWithLog4j.logger.info(sql_);
		ResultSet rs = pst.executeQuery();
		while(rs.next()){
			AttribItem att = APServiceImplHelper.getInstance().getAttribute(rs);
			atts.add(att);
		}
		rs.close();
		pst.close();
		Collections.sort(atts, new AbComparator());
		
		return atts.toArray(new AttribItem[0]);
	}

	/**
	 * 根据属性名返回属性
	 * @throws Throwable 
	 */
	public AttribItem getAttribItemByName(String abName) throws Throwable{
		return APServiceImplHelper.getInstance().getAttribItemByName(abName);
	}

	/**
	 * 根据属性名获取属性数据类型
	 * @throws Throwable 
	 */
	public String getAttribItemDataType(String abName) throws Throwable{
		AttribItem att = getAttribItemByName(abName);
		return att.vtDataType;
	}

	/**
	 * 提供属性池的数据文件数据
	 */
	public String getAPData() throws VCIError{
		return "";
	}

	

	/**
	 * 获取使用指定枚举名的属性名列表
	 * @throws SQLException 
	 */
	public String[] getAPNamesByEMName(String emName) throws VCIError, SQLException{
		ArrayList<String> apNameList = new ArrayList<String>();
		String sql = "select name from plattribute t where extractvalue(content, '/attribute/other') like ?";
		Connection conn = HibernateSessionFactory.getSessionConnection();
		PreparedStatement pst = conn.prepareStatement(sql);
		pst.setString(1, "%enumName = " + emName + "%");
		ResultSet rs = pst.executeQuery();
		while(rs.next()){
			apNameList.add(rs.getString("name"));
		}
		ServerWithLog4j.logger.info(sql);
		rs.close();
		pst.close();
		return apNameList.toArray(new String[0]);
		
	}

	/**
	 * 获取不在参数列表中的属性项
	 * @throws DocumentException 
	 * @throws IOException 
	 * @throws SQLException 
	 */
	public AttribItem[] getAttribItemsOutNames(String[] abNameArray,String text)
			throws VCIError, SQLException, IOException, DocumentException {
		List<AttribItem> atts = new ArrayList<AttribItem>();
		//StringBuilder sql = new StringBuilder("select oid, name, label, description, ts, creator, createtime, modifier, modifytime, t.content.getclobval() content from plattribute t ");
		
		StringBuilder sql = null;
		switch (HibernateSessionFactory.getDbType()) {
			case DM8:
				sql = new StringBuilder("select oid, name, label, description, ts, creator, createtime, modifier, modifytime, content from plattribute t ");
				break;
			case ORACL:
			default:
				sql = new StringBuilder("select oid, name, label, description, ts, creator, createtime, modifier, modifytime, t.content.getclobval() content from plattribute t ");
				break;
		}
		
		Connection connection = HibernateSessionFactory.getSessionConnection();
		PreparedStatement pst = null;
		//add by caill 给sql语句添加like条件
		sql.append(" where t.name like '" + text + "%'");
		if(abNameArray.length > 0){ 
			sql.append("and t.name not in (");
			for(String attName : abNameArray){
				sql.append("'" + attName + "'");
				sql.append(",");
			}
			String sql_ = sql.substring(0, sql.lastIndexOf(","));
			sql_ = sql_ + ")";
			
			pst = connection.prepareStatement(sql_);
		}else{
			pst = connection.prepareStatement(sql.toString());
		}
		ResultSet rs = pst.executeQuery();
		while(rs.next()){
			AttribItem att = APServiceImplHelper.getInstance().getAttribute(rs);
			atts.add(att);
		}
		rs.close();
		pst.close();
		Collections.sort(atts, new AbComparator());
		return atts.toArray(new AttribItem[0]);
	}
	
	public AttribItem getAttribItemByOid(String oid) throws VCIError, SQLException, IOException, DocumentException {
//		String sql = "select oid, name, label, description, ts, creator, createtime, modifier, modifytime, t.content.getclobval() content " +
//				"from plattribute t where t.oid =?";
		
		String sql = "";
		switch (HibernateSessionFactory.getDbType()) {
			case DM8:
				sql = "select oid, name, label, description, ts, creator, createtime, modifier, modifytime, content " +
						"from plattribute t where t.oid =?";
				break;
			case ORACL:
			default:
				sql = "select oid, name, label, description, ts, creator, createtime, modifier, modifytime, t.content.getclobval() content " +
						"from plattribute t where t.oid =?";
				break;
		}
		
		Connection connection = HibernateSessionFactory.getSessionConnection();
		PreparedStatement pst = connection.prepareStatement(sql);
		pst.setString(1, oid);
		ResultSet rs = pst.executeQuery();
		AttribItem ap = null;
		while(rs.next()){
			ap = APServiceImplHelper.getInstance().getAttribute(rs);
		}
		rs.close();
		pst.close();
		if(ap == null){
			ap = new AttribItem();
		}
		return ap;
	}
}