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

import java.io.IOException;
import java.sql.Clob;
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.List;

import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.Element;
import org.dom4j.io.SAXReader;

import com.vci.common.log.ServerWithLog4j;
import com.vci.common.utility.ObjectUtility;
import com.vci.corba.common.VCIError;
import com.vci.corba.omd.vrm.VersionRule;
import com.vci.omd.constants.OmdConstants;
import com.vci.server.base.persistence.dao.HibernateSessionFactory;
import com.vci.server.omd.common.VersionRuleHelper;
import com.vci.server.omd.versionrule.cache.VRServerCacheUtil;

public class VRService {

	private static VRService instance = null;

	private VRService() {
		
	}
	
	public static VRService getInstance() {
		if (instance == null) {
			instance = new VRService();
		}
		
		return instance;
	}
	
	public boolean addVersionRule(VersionRule vr) throws VCIError, SQLException, IOException {
		boolean flag = false;
		String insertSql = "insert into plversionrule values(?,?,?,?,?,?,?,?,?,xmltype(?))";
		Connection connection = HibernateSessionFactory.getSessionConnection();
		PreparedStatement pst = connection.prepareStatement(insertSql);
		pst.setString(1, ObjectUtility.getNewObjectID36());
		pst.setString(2, vr.name);
		pst.setString(3, vr.tag);
		pst.setString(4, vr.description);
		long time = Calendar.getInstance().getTimeInMillis();
		Timestamp ts = new Timestamp(time);
		pst.setTimestamp(5, ts);
		pst.setString(6, vr.creator);
		pst.setTimestamp(7, ts);
		pst.setString(8, vr.modifier);
		pst.setTimestamp(9, ts);
		String xmlText = VersionRuleHelper.getInstance().getXmlText(vr);
		//CLOB content = VersionRuleServerDelegate.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 modifyVersionRule(VersionRule vr)
			throws VCIError, SQLException, IOException {
		boolean flag = false;
		String sql = "update plversionrule 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, vr.name);
		pst.setString(2, vr.tag);
		pst.setString(3, vr.description);
		long time = Calendar.getInstance().getTimeInMillis();
		Timestamp ts = new Timestamp(time);
		pst.setTimestamp(4, ts);
		pst.setString(5, vr.creator);
		pst.setTimestamp(6, new Timestamp(vr.createTime));
		pst.setString(7, vr.modifier);
		pst.setTimestamp(8, ts);
		String xmlText = VersionRuleHelper.getInstance().getXmlText(vr);
		//CLOB content = VersionRuleServerDelegate.getInstance().getXmlTypeContent(xmlText, conn);
		//pst.setObject(9, content);
		pst.setString(9, xmlText);
		pst.setString(10, vr.oid);
		pst.setTimestamp(11, Timestamp.valueOf(vr.ts));
		pst.executeUpdate();
		ServerWithLog4j.logger.info(sql);
		pst.close();
		flag = true;
		return flag;
	}

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

	/**
	 * 删除VersionRules
	 * @throws SQLException 
	 */
	public boolean deleteVersionRules(VersionRule[] vrs) throws VCIError, SQLException {
		for(VersionRule vr : vrs){
			deleteVersionRule(vr);
		}
		return true;
	}
	
	public VersionRule[] getVersionRules() throws VCIError, SQLException, IOException, DocumentException {
		//String sql = "select oid, name, label, description, ts, creator, createtime, modifier, modifytime, t.content.getclobval() content from plversionrule t";
		
		String sql = "";
		switch (HibernateSessionFactory.getDbType()) {
			case DM8:
				sql = "select oid, name, label, description, ts, creator, createtime, modifier, modifytime, content from plversionrule t";
				break;
			case ORACL:
			default:
				sql = "select oid, name, label, description, ts, creator, createtime, modifier, modifytime, t.content.getclobval() content from plversionrule t";
				break;
		}
		
		Connection connection = HibernateSessionFactory.getSessionConnection();
		PreparedStatement pst = connection.prepareStatement(sql);
		ResultSet rs = pst.executeQuery();
		ServerWithLog4j.logger.info(sql);
		List<VersionRule> sps = new ArrayList<VersionRule>();
		while(rs.next()){
			VersionRule vr = getVR(rs);
			sps.add(vr);
		}
		rs.close();
		pst.close();
		return sps.toArray(new VersionRule[0]);
	}

	public VersionRule getVersionRule(String name) throws VCIError, SQLException, IOException, DocumentException {
		//String sql = "select oid, name, label, description, ts, creator, createtime, modifier, modifytime, t.content.getclobval() content from plversionrule t where t.name =?";
		
		String sql = "";
		switch (HibernateSessionFactory.getDbType()) {
			case DM8:
				sql = "select oid, name, label, description, ts, creator, createtime, modifier, modifytime, content from plversionrule t where t.name =?";
				break;
			case ORACL:
			default:
				sql = "select oid, name, label, description, ts, creator, createtime, modifier, modifytime, t.content.getclobval() content from plversionrule t where t.name =?";
				break;
		}
		
		Connection connection = HibernateSessionFactory.getSessionConnection();
		PreparedStatement pst = connection.prepareStatement(sql);
		pst.setString(1, name);
		ResultSet rs = pst.executeQuery();
		ServerWithLog4j.logger.info(sql);
		VersionRule vr = null;
		while(rs.next()){
			vr = getVR(rs);
		}
		rs.close();
		pst.close();
		if(vr == null){
			vr = new VersionRule();
		}
		return vr;
	}

	public boolean xml2DB(String userName) throws VCIError {
//		List<VersionRule> news = Xml2DBDelegate.getInstance().getNews(userName);
//		if(news == null){
//			return true;
//		}
//		for(VersionRule o : news){
//			try{
//				addVersionRule(o);
//			}catch(Throwable e){
//				e.printStackTrace();
//				ServerWithLog4j.logger.warn(o.name + "迁移失败, 版本管理的迁移中止!");
//				return false;
//			}
//		}
		return true;
	}
	
	
	
	public VersionRule getVersionRuleByOid(String oid) throws VCIError, SQLException, IOException, DocumentException {
//		String sql = "select oid, name, label, description, ts, creator, createtime, modifier, modifytime, " +
//				"t.content.getclobval() content from plversionrule t where t.oid =?";
		
		String sql = "";
		switch (HibernateSessionFactory.getDbType()) {
			case DM8:
				sql = "select oid, name, label, description, ts, creator, createtime, modifier, modifytime, " +
						"content from plversionrule t where t.oid =?";
				break;
			case ORACL:
			default:
				sql = "select oid, name, label, description, ts, creator, createtime, modifier, modifytime, " +
						"t.content.getclobval() content from plversionrule t where t.oid =?";
				break;
		}
		
		Connection connection = HibernateSessionFactory.getSessionConnection();
		PreparedStatement pst = connection.prepareStatement(sql);
		pst.setString(1, oid);
		ResultSet rs = pst.executeQuery();
		VersionRule vr = null;
		while(rs.next()){
			vr = getVR(rs);
		}
		rs.close();
		pst.close();
		if(vr == null){
			vr = new VersionRule();
		}
		return vr;
	}
	
	/**
	 * 将一条数据库中的记录转化成btmitem
	 * @param rs
	 * @return
	 * @throws SQLException
	 */
	public VersionRule getVR(ResultSet rs) throws SQLException, IOException, DocumentException{
		VersionRule vr = new VersionRule();
		String value = rs.getString("oid");
		vr.oid = (value == null ? "" : value);
		value = rs.getString("name");
		vr.name = (value == null ? "" : value);
		value = rs.getString("label");
		vr.tag = (value == null ? "" : value);
		value = rs.getString("description");
		vr.description = (value == null ? "" : value);
		vr.ts = OmdConstants.tsDF.format(rs.getTimestamp("ts"));
		value = rs.getString("creator");
		vr.creator = (value == null ? "" : value);
		vr.createTime = rs.getTimestamp("createTime").getTime();
		value = rs.getString("modifier");
		vr.modifier = (value == null ? "" : value);
		vr.modifyTime = rs.getTimestamp("modifyTime").getTime();
		//CLOB clob = (CLOB) rs.getClob("content");
		Clob clob = rs.getClob("content");
		Element vrDetails = getVRDetails(clob);
		VersionRuleHelper.getInstance().setVRValueFormDoc(vr, vrDetails);
		return vr;
	}
	

	/**
	 * 将查询的clob对象转换成xml的element, 便于解析
	 * @param clob
	 * @return
	 * @throws SQLException
	 * @throws IOException
	 * @throws DocumentException
	 */
	public Element getVRDetails(Clob clob) throws DocumentException, SQLException{
		SAXReader saxReader = new SAXReader();
		//Document document = saxReader.read(clob.characterStreamValue());
		Document document = saxReader.read(clob.getCharacterStream());
		Element root = document.getRootElement();
		return root;
	}
}