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

import java.io.File;
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.resource.CommonProperties;
import com.vci.common.utility.ObjectUtility;
import com.vci.corba.common.VCIError;
import com.vci.corba.omd.stm.StatePool;
import com.vci.omd.constants.OmdConstants;
import com.vci.server.base.persistence.dao.HibernateSessionFactory;
import com.vci.server.omd.common.StateHelper;


public class StatePoolService {
	private static StatePoolService instance;
	
	private StatePoolService() {
		
	}
	
	public static StatePoolService getInstance() {
		if (instance == null) {
			instance = new StatePoolService();
		}
		
		return instance;
	}
	public boolean addStatePool(StatePool statePool) throws Exception {
			boolean flag = false;
			String insertSql = "insert into plstatepool values(?,?,?,?,?,?,?,?,?,xmltype(?))";
			Connection connection = HibernateSessionFactory.getSessionConnection();
			PreparedStatement pst = connection.prepareStatement(insertSql);
			pst.setString(1, ObjectUtility.getNewObjectID36());
			pst.setString(2, statePool.name);
			pst.setString(3, statePool.tag);
			pst.setString(4, statePool.description);
			long time = Calendar.getInstance().getTimeInMillis();
			Timestamp ts = new Timestamp(time);
			pst.setTimestamp(5, ts);
			pst.setString(6, statePool.creator);
			pst.setTimestamp(7, ts);
			pst.setString(8, statePool.modifier);
			pst.setTimestamp(9, ts);
			String xmlText = StateHelper.getInstance().getXmlText(statePool);
			//CLOB content = StatePoolDelegate.getInstance().getXmlTypeContent(xmlText, connection);
			//pst.setObject(10, content);
			pst.setString(10, xmlText);
			pst.executeUpdate();
			flag = true;
			ServerWithLog4j.logger.debug(insertSql);
			pst.close();
			return flag;
	}
	
	public boolean modifyStatePool(StatePool statePool) throws Exception {
			boolean flag = false;
			String sql = "update plstatepool 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, statePool.name);
			pst.setString(2, statePool.tag);
			pst.setString(3, statePool.description);
			long time = Calendar.getInstance().getTimeInMillis();
			Timestamp ts = new Timestamp(time);
			pst.setTimestamp(4, ts);
			pst.setString(5, statePool.creator);
			pst.setTimestamp(6, new Timestamp(statePool.createTime));
			pst.setString(7, statePool.modifier);
			pst.setTimestamp(8, ts);
			String xmlText = StateHelper.getInstance().getXmlText(statePool);
			//CLOB content = StatePoolDelegate.getInstance().getXmlTypeContent(xmlText, conn);
			//pst.setObject(9, content);
			pst.setString(9, xmlText);
			pst.setString(10, statePool.oid);
			pst.setTimestamp(11, Timestamp.valueOf(statePool.ts));
			pst.executeUpdate();
			ServerWithLog4j.logger.debug(sql);
			pst.close();
			flag = true;
			return flag;
	}

	public boolean deleteStatePool(StatePool statePool) throws Exception {
			boolean flag = false;
			String sql = "delete from plstatepool where oid = ? and ts = ?";
			Connection connection = HibernateSessionFactory.getSessionConnection();
			PreparedStatement pst = connection.prepareStatement(sql);
			pst.setString(1, statePool.oid);
			String ts = statePool.ts;
			pst.setTimestamp(2, Timestamp.valueOf(ts));
			pst.executeUpdate();
			flag = true;
			ServerWithLog4j.logger.debug(sql);
			pst.close();
			return flag;
	}
	
	/**
	 * 删除statePools
	 * @throws Exception 
	 */

	public boolean deleteStatePools(StatePool[] sps) throws Exception {
		
			for(StatePool sp : sps){
				deleteStatePool(sp);
			}
			return true;
	}
	
	public StatePool[] getStatePools() throws Exception {
			//String sql = "select oid, name, label, description, ts, creator, createtime, modifier, modifytime, t.content.getclobval() content from plstatepool t order by name";
		
			String sql = "";
			switch (HibernateSessionFactory.getDbType()) {
				case DM8:
					sql = "select oid, name, label, description, ts, creator, createtime, modifier, modifytime, content from plstatepool t order by name";
					break;
				case ORACL:
				default:
					sql = "select oid, name, label, description, ts, creator, createtime, modifier, modifytime, t.content.getclobval() content from plstatepool t order by name";
					break;
			}		
			Connection connection = HibernateSessionFactory.getSessionConnection();
			PreparedStatement pst = connection.prepareStatement(sql);
			ResultSet rs = pst.executeQuery();
			ServerWithLog4j.logger.debug(sql);
			List<StatePool> sps = new ArrayList<StatePool>();
			while(rs.next()){
				StatePool sp = getLCStateObj(rs);
				sps.add(sp);
			}
			rs.close();
			pst.close();
			return sps.toArray(new StatePool[0]);	
	}

	
	public String[] getImagePaths() throws Throwable {
		
			//return StatePoolDelegate.getInstance().getImagePaths();
			String imagePath = CommonProperties.getStringProperty("imagePath");
			File imageFolder = new File(imagePath);
			File[] files = imageFolder.listFiles();
			ArrayList<String> pathList = new ArrayList<String>();
			if(files != null && files.length>0){
				for(File file: files){
					if(file != null){
						String name = file.getName();
						pathList.add("images/" + name);
					}		
				}
			}
				
			
			return pathList.toArray(new String[0]);
	}

	
	public StatePool getStatePool(String name) throws Exception {
		
			//String sql = "select oid, name, label, description, ts, creator, createtime, modifier, modifytime, t.content.getclobval() content from plstatepool t where t.name =?";
		
			String sql = "";
			switch (HibernateSessionFactory.getDbType()) {
				case DM8:
					sql = "select oid, name, label, description, ts, creator, createtime, modifier, modifytime, content from plstatepool t where t.name =?";
					break;
				case ORACL:
				default:
					sql = "select oid, name, label, description, ts, creator, createtime, modifier, modifytime, t.content.getclobval() content from plstatepool t where t.name =?";
					break;
			}		
			Connection connection = HibernateSessionFactory.getSessionConnection();
			PreparedStatement pst = connection.prepareStatement(sql);
			pst.setString(1, name);
			ResultSet rs = pst.executeQuery();
			ServerWithLog4j.logger.debug(sql);
			StatePool sp = null;
			while(rs.next()){
				sp = getLCStateObj(rs);
			}
			rs.close();
			pst.close();
			if(sp == null){
				sp = new StatePool();
			}
			return sp;
	}

	public boolean xml2DB(String userName) throws VCIError {
//		List<StatePool> news = Xml2DBDelegate.getInstance().getNews(userName);
//		if(news == null){
//			return true;
//		}
//		for(StatePool o : news){
//			try{
//				addStatePool(o);
//			}catch(Throwable e){
//				e.printStackTrace();
//				ServerWithLog4j.logger.warn(o.name + "迁移失败, 状态池的迁移中止!");
//				return false;
//			}
//		}
		return true;	
			
	}
	//根据id查询状态
	public StatePool getStatePoolByOid(String oid) throws Exception {
		//String sql = "select oid, name, label, description, ts, creator, createtime, modifier, modifytime, t.content.getclobval() content from plstatepool t where t.oid =?";
		
		String sql = "";
		switch (HibernateSessionFactory.getDbType()) {
			case DM8:
				sql = "select oid, name, label, description, ts, creator, createtime, modifier, modifytime, content from plstatepool t where t.oid =?";
				break;
			case ORACL:
			default:
				sql = "select oid, name, label, description, ts, creator, createtime, modifier, modifytime, t.content.getclobval() content from plstatepool t where t.oid =?";
				break;
		}
		
		Connection connection = HibernateSessionFactory.getSessionConnection();
		PreparedStatement pst = connection.prepareStatement(sql);
		pst.setString(1, oid);
		ResultSet rs = pst.executeQuery();
		ServerWithLog4j.logger.debug(sql);
		StatePool sp = null;
		while(rs.next()){
			sp = getLCStateObj(rs);
		}
		rs.close();
		pst.close();
		/*if(sp == null){
			sp = new StatePool();
		}*/
		return sp;
	}
	
	/**
	 * 将查询的clob对象转换成xml的element, 便于解析
	 * @param clob
	 * @return
	 * @throws SQLException
	 * @throws IOException
	 * @throws DocumentException
	 */
	public Element getSPDetails(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;
	}
	
	public StatePool getLCStateObj(ResultSet rs) throws SQLException, DocumentException {
		StatePool sp = new StatePool();
		String value = rs.getString("oid");
		sp.oid = (value == null ? "" : value);
		value = rs.getString("name");
		sp.name = (value == null ? "" : value);
		value = rs.getString("label");
		sp.tag = (value == null ? "" : value);
		value = rs.getString("description");
		sp.description = (value == null ? "" : value);
		sp.ts = OmdConstants.tsDF.format(rs.getTimestamp("ts"));
		value = rs.getString("creator");
		sp.creator = (value == null ? "" : value);
		sp.createTime = rs.getTimestamp("createTime").getTime();
		value = rs.getString("modifier");
		sp.modifier = (value == null ? "" : value);
		sp.modifyTime = rs.getTimestamp("modifyTime").getTime();
		//CLOB clob = (CLOB) rs.getClob("content");
		Clob clob = rs.getClob("content");
		Element spDetails = getSPDetails(clob);
		StateHelper.getInstance().setSPValueFormDoc(sp, spDetails);
		return sp;
	}
}