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

import java.io.File;
import java.io.IOException;
import java.math.BigDecimal;
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.Arrays;
import java.util.Calendar;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.Element;
import org.dom4j.io.SAXReader;
import org.hibernate.HibernateException;
import org.hibernate.SQLQuery;
import org.hibernate.Session;

import com.vci.common.log.ServerWithLog4j;
import com.vci.common.resource.CommonProperties;
import com.vci.common.utility.ObjectUtility;
import com.vci.corba.omd.atm.AttribItem;
import com.vci.corba.omd.btm.BtmAndApName;
import com.vci.corba.omd.btm.BtmItem;
import com.vci.corba.omd.data.SystemModelData;
import com.vci.omd.constants.OmdConstants;
import com.vci.server.base.persistence.dao.HibernateSessionFactory;
import com.vci.server.base.utility.OmdHelper;
import com.vci.server.cache.OMCacheProvider;
import com.vci.server.omd.common.OmdViewTool;
import com.vci.server.omd.ddlTool.DDLHelper;
import com.vci.server.omd.delegate.SystemConfDelegate;
import com.vci.corba.common.VCIError;

@SuppressWarnings("deprecation")
public class BTMService {
	private static BTMService instance;
	
	/**
	 * 业务类型的系统属性
	 */
//	private static String otherFieldBt = "\n\tOID VARCHAR2(36) not null," +
//			"\n\tREVISIONOID VARCHAR2(36),\n\tNAMEOID VARCHAR2(36),\n\tBtmName VARCHAR2(36)," +
//			"\n\tISLastR CHAR(1),\n\tISFirstR CHAR(1),\n\tISLastV CHAR(1),\n\tISFirstV CHAR(1)," +
//			"\n\tCreator VARCHAR2(36)," + 
//			"\n\tCreateTime TIMESTAMP,\n\tLastModifier VARCHAR2(36),\n\tLastModifyTime TIMESTAMP," + 
//			"\n\tRevisionRule VARCHAR2(36),\n\tVersionRule VARCHAR2(36),\n\tRevisionSeq NUMBER," + 
//			"\n\tRevisionValue VARCHAR2(10),\n\tVersionSeq NUMBER,\n\tVersionValue VARCHAR2(10)," + 
//			"\n\tLCTID VARCHAR2(36),\n\tLCStatus VARCHAR2(36),\n\tTS TIMESTAMP," +
//			"\n\tID VARCHAR2(36),\n\tNAME VARCHAR2(128),\n\tDESCRIPTION VARCHAR2(255)," +
//			"\n\tOWNER VARCHAR2(36),\n\tCHECKINBY VARCHAR2(36),\n\tCHECKINTIME TIMESTAMP," +
//			"\n\tCHECKOUTBY VARCHAR2(36),\n\tCHECKOUTTIME TIMESTAMP,\n\tCOPYFROMVERSION VARCHAR2(36),\n\t";

	private BTMService() {

	}

	public static BTMService getInstance() {
		if (instance == null) {
			instance = new BTMService();
		}
		return instance;
	}

	/**
	 * 获取名字以filter开始的业务类型(for 查询) 获取全部业务类型:filter为""
	 * 
	 * @throws Exception
	 */

	public BtmItem[] getAllBtmItem(String filter) throws Exception {
		// String sql = "select oid, name, label, description, ts, creator, createtime,
		// modifier, modifytime, t.content.getclobval() content from plbtmtype t";

		String sql = "";
		switch (HibernateSessionFactory.getDbType()) {
		case DM8:
			sql = "select oid, name, label, description, ts, creator, createtime, modifier, modifytime, content from plbtmtype t";
			break;
		case ORACL:
		default:
			sql = "select oid, name, label, description, ts, creator, createtime, modifier, modifytime, t.content.getclobval() content from plbtmtype t";
			break;
		}

		if (!filter.equals("")) {
			sql = sql + " where t.name like '" + filter + "%'";
		}
		sql = sql + " order by name ";
		Connection connection = HibernateSessionFactory.getSessionConnection();
		PreparedStatement pst = connection.prepareStatement(sql);
		ResultSet rs = pst.executeQuery();
		ServerWithLog4j.logger.debug(sql);
		List<BtmItem> bts = new ArrayList<BtmItem>();
		while (rs.next()) {
			BtmItem bt = getBT(rs);
			bts.add(bt);
		}
		rs.close();
		pst.close();
		return bts.toArray(new BtmItem[0]);
	}

	/**
	 * 获取当前业务类型下所有子业务类型的定义
	 * 
	 * @param btmName
	 * @return
	 * @throws Exception
	 */
	public BtmItem[] getChildrenBtms(String btmName) throws Exception {
//		String sql = "select oid, name, label, description, ts, creator, createtime, modifier, modifytime, t.content.getclobval() content from plbtmtype t"
//				+ " where extractvalue(content, '/btm/fName') = ?";

		String sql = "";
		switch (HibernateSessionFactory.getDbType()) {
		case DM8:
			sql = "select oid, name, label, description, ts, creator, createtime, modifier, modifytime, content from plbtmtype t"
					+ " where extractvalue(content, '/btm/fName') = ?";
			break;
		case ORACL:
		default:
			sql = "select oid, name, label, description, ts, creator, createtime, modifier, modifytime, t.content.getclobval() content from plbtmtype t"
					+ " where extractvalue(content, '/btm/fName') = ?";
			break;
		}

		Connection connection = HibernateSessionFactory.getSessionConnection();
		PreparedStatement pst = connection.prepareStatement(sql);
		pst.setString(1, btmName);
		ResultSet rs = pst.executeQuery();
		ServerWithLog4j.logger.debug(sql);
		List<BtmItem> bts = new ArrayList<BtmItem>();
		while (rs.next()) {
			BtmItem bt = getBT(rs);
			bts.add(bt);
		}
		rs.close();
		pst.close();
		return bts.toArray(new BtmItem[0]);
	}

	/**
	 * 获取属性名以及对应的业务类型名
	 * 
	 * @throws Throwable
	 */
	public BtmAndApName[] getBtmAndApNameArray(String btmName) throws Throwable {

		ArrayList<BtmAndApName> btmAndeApNameList = new ArrayList<BtmAndApName>();
		while (!btmName.equals("")) {
			BtmItem bt = getBtmItemByName(btmName);
			BtmAndApName btmAndApName = new BtmAndApName();
			btmAndApName.btmName = btmName;
			btmAndApName.apName = bt.apNameArray;
			btmAndeApNameList.add(btmAndApName);
			btmName = bt.fName;
		}
		return btmAndeApNameList.toArray(new BtmAndApName[0]);
	}

	/**
	 * 删除业务类型
	 * 
	 * @throws Exception
	 */
	public boolean deleteBtmItem(BtmItem bt) throws Exception {

		boolean flag = false;
		Connection connection = HibernateSessionFactory.getSessionConnection();

		String tableName = OmdHelper.getBTTableName(bt.name);
		
		// 删除表
		DDLHelper.dropTable(tableName);

		// 删除对象类型记录
		String sql = "delete from plbtmtype where oid = ? and ts = ?";
		PreparedStatement pst = connection.prepareStatement(sql);
		pst.setString(1, bt.oid);
		pst.setTimestamp(2, new Timestamp(bt.ts));
		pst.executeUpdate();
		ServerWithLog4j.logger.debug(sql);
		pst.close();
		flag = true;
		return flag;

	}

	public boolean deleteBtmItemNoCache(BtmItem bt) throws Exception {

		boolean flag = false;
		String sql = "delete from plbtmtype where oid = ? and ts = ?";
		Connection connection = HibernateSessionFactory.getSessionConnection();
		PreparedStatement pst = connection.prepareStatement(sql);
		pst.setString(1, bt.oid);
		pst.setTimestamp(2, new Timestamp(bt.ts));
		pst.executeUpdate();
		ServerWithLog4j.logger.debug(sql);
		pst.close();
		flag = true;
		return flag;
	}

	/**
	 * 删除业务类型
	 * 
	 * @throws Exception
	 */

	public boolean deleteBtmItems(BtmItem[] bts) throws Exception {

		boolean flag = false;
		for (BtmItem bt : bts) {
			deleteBtmItem(bt);
		}
		flag = true;
		return flag;

	}

	/**
	 * 检查要插入的记录是否存在
	 * 
	 * @throws Exception
	 */

	public boolean checkRowIsExists(String name) throws Exception {

		String sql = "select count(name) count from plbtmtype t where t.name = ?";
		Connection connection = HibernateSessionFactory.getSessionConnection();
		PreparedStatement pst = connection.prepareStatement(sql);
		pst.setString(1, name);
		ResultSet rs = pst.executeQuery();
		ServerWithLog4j.logger.debug(sql);
		while (rs.next()) {
			int count = rs.getInt("count");
			if (count > 0) {
				return true;
			} else {
				return false;
			}
		}
		rs.close();
		pst.close();
		return false;

	}

	/**
	 * 增加业务类型
	 * 
	 * @throws Exception
	 */

	public boolean addBtmItem(BtmItem bt) throws Exception {

		String insertSql = "insert into plbtmtype (OID, NAME, LABEL, DESCRIPTION, TS, CREATOR, CREATETIME, MODIFIER, MODIFYTIME, CONTENT) values(?,?,?,?,?,?,?,?,?,xmltype(?))";

		long time = Calendar.getInstance().getTimeInMillis();
		Timestamp ts = new Timestamp(time);

		Connection connection = HibernateSessionFactory.getSessionConnection();
		PreparedStatement pst = connection.prepareStatement(insertSql);
		bt.oid = ObjectUtility.getNewObjectID36();
		
		int index = 1;
		pst.setString(index++, bt.oid);
		pst.setString(index++, bt.name);
		pst.setString(index++, bt.label);
		pst.setString(index++, bt.description);
		pst.setTimestamp(index++, ts);
		pst.setString(index++, bt.creator);
		pst.setTimestamp(index++, ts);
		pst.setString(index++, bt.modifier);
		pst.setTimestamp(index++, ts);
		String xmlText = getXmlText(bt);
		// CLOB content = BTMServiceImplHelper.getInstance().getXmlTypeContent(xmlText,
		// connection);
		// pst.setObject(10, content);
		pst.setString(index++, xmlText);
		int size = pst.executeUpdate();
		ServerWithLog4j.logger.debug(insertSql);
		pst.close();
		
		if (size > 0) {
			bt.createTime = time;
			bt.modifyTime = time;
			bt.ts = time;
		}

		return size > 0;
	}

	/**
	 * 更改业务类型
	 * 
	 * @throws Exception
	 */

	public boolean updateBtmItem(BtmItem bt) throws Exception {
		String sql = "update plbtmtype t set t.name=?, t.label=?, t.description=?, t.ts=?, t.modifier=?, t.modifytime=?, t.content=xmltype(?) where t.oid=? and t.ts = ?";

		Connection conn = HibernateSessionFactory.getSessionConnection();
		PreparedStatement pst = conn.prepareStatement(sql);

		long time = Calendar.getInstance().getTimeInMillis();
		Timestamp ts = new Timestamp(time);

		int index = 1;
		pst.setString(index++, bt.name);
		pst.setString(index++, bt.label);
		pst.setString(index++, bt.description);
		pst.setTimestamp(index++, ts);
		pst.setString(index++, bt.modifier);
		pst.setTimestamp(index++, ts);
		String xmlText = getXmlText(bt);
		// CLOB content = BTMServiceImplHelper.getInstance().getXmlTypeContent(xmlText,
		// conn);
		// pst.setObject(9, content);
		pst.setString(index++, xmlText);
		pst.setString(index++, bt.oid);
		pst.setTimestamp(index++, new Timestamp(bt.ts));
		int size = pst.executeUpdate();
		ServerWithLog4j.logger.debug(sql);
		pst.close();
		
		if (size > 0) {
			bt.modifyTime = time;
			bt.ts = time;
		}

		return size > 0;
	}

	/**
	 * 获取业务类型的属性名数组
	 * 
	 * @throws Exception
	 */

	public String[] getBtmApNameArray(String btmName) throws Exception {

		List<String> list = new ArrayList<String>();
		// String sql = "select oid, name, label, description, ts, creator, createtime,
		// modifier, modifytime, t.content.getclobval() content from plbtmtype t start
		// with name = ? connect by prior extractvalue(content, '/btm/fName') = name";

		String sql = "";
		switch (HibernateSessionFactory.getDbType()) {
		case DM8:
			sql = "select oid, name, label, description, ts, creator, createtime, modifier, modifytime, t.content.getclobval() content from plbtmtype t "
					+ "start with name = ? connect by prior extractvalue(content, '/btm/fName') = name";
			break;
		case ORACL:
		default:
			sql = "select oid, name, label, description, ts, creator, createtime, modifier, modifytime, t.content.getclobval() content from plbtmtype t "
					+ "start with name = ? connect by prior extractvalue(content, '/btm/fName') = name";
			break;
		}

		Connection connection = HibernateSessionFactory.getSessionConnection();
		PreparedStatement pst = connection.prepareStatement(sql);
		pst.setString(1, btmName);
		ResultSet rs = pst.executeQuery();
		ServerWithLog4j.logger.debug(sql);
		while (rs.next()) {
			BtmItem bt = getBT(rs);
			// _list为固定大小的列表, 不支持add/remove
			List<String> _list = Arrays.asList(bt.apNameArray);
			list.addAll(_list);
		}
		rs.close();
		pst.close();
		return list.toArray(new String[0]);

	}

	/**
	 * 根据属性名获取使用该属性名的全部业务类型名
	 * 
	 * @throws Exception
	 */

	public String[] getBTMNamesByAPName(String apName) throws Exception {
		String sql = "select t.name from plbtmtype t start with extractvalue(content, '/btm/apNameArray') like ? or extractvalue(content, '/btm/apNameArray') like ?"
				+ "or extractvalue(content, '/btm/apNameArray') like ? or extractvalue(content, '/btm/apNameArray') = ? connect by prior t.name = extractvalue(content, '/btm/fName')";
		Connection conn = HibernateSessionFactory.getSessionConnection();
		PreparedStatement pst = conn.prepareStatement(sql);
		pst.setString(1, apName + ",%");
		pst.setString(2, "%," + apName);
		pst.setString(3, "%," + apName + ",%");
		pst.setString(4, apName);
		ResultSet rs = pst.executeQuery();
		ServerWithLog4j.logger.debug(sql);
		Set<String> set = new HashSet<String>();
		while (rs.next()) {
			set.add(rs.getString("name"));
		}
		rs.close();
		pst.close();
		return set.toArray(new String[0]);
	}

	/**
	 * (对外)提供业务类型的数据文件数据
	 */
	public String getBTMData() throws VCIError {
		return "";
	}

	/**
	 * 根据版本规则名获取使用该版本规则的全部业务类型名
	 * 
	 * @throws Exception
	 */

	public String[] getBTMNamesByVerName(String verName) throws Exception {
		String sql = "select t.name from plbtmtype t where extractvalue(content, '/btm/revRuleName') = ?";
		Connection conn = HibernateSessionFactory.getSessionConnection();
		PreparedStatement pst = conn.prepareStatement(sql);
		pst.setString(1, verName);
		ResultSet rs = pst.executeQuery();
		ServerWithLog4j.logger.debug(sql);
		List<String> list = new ArrayList<String>();
		while (rs.next()) {
			list.add(rs.getString("name"));
		}
		rs.close();
		pst.close();
		return list.toArray(new String[0]);
	}

	/**
	 * 根据生命周期名获取使用该生命周期的全部业务类型名
	 * 
	 * @throws Exception
	 */

	public String[] getBTMNamesByLCyName(String lCyName) throws Exception {
		String sql = "select t.name from plbtmtype t where extractvalue(content, '/btm/lifeCycle') = ?";
		Connection conn = HibernateSessionFactory.getSessionConnection();
		PreparedStatement pst = conn.prepareStatement(sql);
		pst.setString(1, lCyName);
		ResultSet rs = pst.executeQuery();
		ServerWithLog4j.logger.debug(sql);
		List<String> list = new ArrayList<String>();
		while (rs.next()) {
			list.add(rs.getString("name"));
		}
		rs.close();
		pst.close();
		return list.toArray(new String[0]);
	}

	/**
	 * 获取业务类型的子类型名
	 * 
	 * @throws Exception
	 */
	public String[] getChildrenNames(String btmName) throws Exception {
		String sql = "select t.name from plbtmtype t start with t.name = ? connect by prior t.name = extractvalue(content, '/btm/fName')";
		Connection conn = HibernateSessionFactory.getSessionConnection();
		PreparedStatement pst = conn.prepareStatement(sql);
		pst.setString(1, btmName);
		ResultSet rs = pst.executeQuery();
		ServerWithLog4j.logger.debug(sql);
		Set<String> set = new HashSet<String>();
		while (rs.next()) {
			set.add(rs.getString("name"));
		}
		set.remove(btmName);
		rs.close();
		pst.close();
		return set.toArray(new String[0]);
	}


	/**
	 * 将服务端image是路径下的文件 , 以images/fileName的形式返回
	 * 
	 * @throws VCIError
	 */

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

	/**
	 * 根据传入的业务对象名称，获取对象建模的所有数据（参数为空时，获取所有建模数据）
	 * 
	 * @throws Throwable
	 */

	public SystemModelData getAllConfigurationData(String[] boTypes) throws Throwable {
		return SystemConfDelegate.getInstance().getAllConfigurationData(boTypes);
	}

	/**
	 * 业务类型与数据库一致性检查
	 * 
	 * @throws Exception
	 */

	public String[] btmConsistencyCheck() throws Exception {
		List<String> infoList = new ArrayList<String>();

//		AttribItem[] attrs = AttrPoolService.getInstance().getAttribItems(null, 0, 0);
//		Map<String, AttribItem> mapAttr = new HashMap<String, AttribItem>();
//		for (AttribItem attr : attrs) {
//			mapAttr.put(attr.name.toLowerCase(), attr);
//		}

		BtmItem[] btms = getAllBtmItem("");
		List<String> tableNameList = getAllBtmTables();
		// modify by weidy@2021-10-27
		// 可能这个东西是视图
		List<String> viewNameList = getAllBtmViews();

		Map<String, List<String>> map = getTableAndCols();

//		List<String> lstLackAttr = new ArrayList<String>();

		StringBuilder info = new StringBuilder();
		for (int i = 0; i < btms.length; i++) {
			info.setLength(0);

			BtmItem btm = btms[i];
			String tableName = OmdHelper.getBTTableName(btm.name).toLowerCase();
			// 视图的话，就不管sql了
			if (viewNameList.contains(tableName)) {
				continue;
			}
			if (!tableNameList.contains(tableName)) {
				info.append(btm.name).append("/DML_CREATE");
				infoList.add(info.toString());
				continue;
			}

			//String[] abNames = BtmHelper.getAbNames(btm.name);
			String[] abNames = OMCacheProvider.getBTAttributes(btm.name);
			List<String> colList = map.get(tableName);
			List<String> addList = new ArrayList<String>();
			List<String> dropList = new ArrayList<String>();
			for (int k = 0; k < abNames.length; k++) {
				String abName = abNames[k].toLowerCase();
//				if (!mapAttr.containsKey(abName)) {
//					lstLackAttr.add(abName);
//				}
				if (colList == null || !colList.contains(abName)) {
					addList.add(abName);
				} else {
					colList.remove(abName);
				}
			}

			if (colList != null) {
				dropList = colList;
			}
			if (addList.size() > 0) {
				info.append(btm.name).append("/DML_ADD(");
				for (int k = 0; k < addList.size(); k++) {
					String abName = addList.get(k);
					info.append(abName).append(",");
				}

				info.setCharAt(info.lastIndexOf(","), ')');
			}

			if (dropList.size() > 0) {
				if (info.length() == 0) {
					info.append(btm.name).append("/DML_DROP(");
				} else {
					info.append(";_DROP(");
				}
				for (int k = 0; k < dropList.size(); k++) {
					String abName = dropList.get(k);
					info.append(abName).append(",");
				}
				info.setCharAt(info.lastIndexOf(","), ')');
			}
			if (info.length() > 0) {
				infoList.add(info.toString());
			}
		}

//		info.setLength(0);
//		if (lstLackAttr.size() > 0) {
//			for (String attr : lstLackAttr) {
//				if (info.length() == 0)
//					info.append("attribute/DML_LACK(").append(attr);
//				else
//					info.append(",").append(attr);
//			}
//			info.append(")");
//			infoList.add(info.toString());
//		}

		return infoList.toArray(new String[0]);
	}

	/**
	 * 执行修复的sql, 并返回修复结果 修复完全成功的, 返回类型名 修复部分成功的, 返回类型名+成功的部分信息
	 * 
	 * @throws Throwable
	 */

	public String[] executeRepair(String[] sqlArray) throws Throwable {
		List<String> list = new ArrayList<String>();
		for (int i = 0; i < sqlArray.length; i++) {
			String[] sqlInfo = sqlArray[i].split("/DML");
			String type = sqlInfo[0];
			String sql = sqlInfo[1];
			if (sql.contains(";")) {
				String[] sqls = sql.split(";");
				String addSql = parseToSql(type, sqls[0]);
				String dropSql = parseToSql(type, sqls[1]);
				boolean addFlag = DDLHelper.executeSql(addSql);
				boolean dropFlag = DDLHelper.executeSql(dropSql);
				if (addFlag && dropFlag) {
					list.add(type);
				} else if (addFlag) {
					list.add(type + "_ADD");
				} else if (dropFlag) {
					list.add(type + "_DROP");
				}
			} else {
				String sql_ = parseToSql(type, sql);
				boolean flag = DDLHelper.executeSql(sql_);

				if (sql_.toLowerCase().contains("create table " + OmdHelper.getBTTableName(type).toLowerCase())) {
					String consSql = getAddPKSql(type);
					boolean flag_ = DDLHelper.executeSql(consSql);
					if (flag && flag_) {
						list.add(type);
					}
				} else if (flag) {
					list.add(type);
				}
			}
		}
		return list.toArray(new String[0]);
	}

	/**
	 * 如果一个业务类型包含子类型， 创建业务类型视图
	 * 
	 * @throws Throwable
	 */

	public boolean createTable(String btName) throws Throwable {
		BtmItem btItem = getBtmItemByName(btName);

		String sqlCreate = getCreateBTMTableSql(btItem);
		String sqlAlter = getAddPKSql(btItem.name);

		Session session = HibernateSessionFactory.getSession();
		SQLQuery query;

		if (!sqlCreate.equals("")) {
			query = session.createSQLQuery(sqlCreate);
			int re = query.executeUpdate();
			if (re < 0) {
				return false;
			}
		}

		if (!sqlAlter.equals("")) {
			query = session.createSQLQuery(sqlAlter);
			int re = query.executeUpdate();
			if (re < 0) {
				return false;
			}
		}

		return true;
	}

	/**
	 * 如果一个业务类型包含子类型， 创建业务类型视图
	 * 
	 * @throws Exception
	 */

	public boolean createView() throws Exception {
		Session session = HibernateSessionFactory.getSession();
		SQLQuery createSQLQuery;
		BtmItem[] allBtmItem = getAllBtmItem("");
		for (BtmItem bt : allBtmItem) {
			String[] childrenNames = getChildrenNames(bt.name);
			if (childrenNames.length > 0) {
				List<String> asList = Arrays.asList(childrenNames);
				asList.add(bt.name);
				String vName = OmdViewTool.getBtViewName(bt.name);
				String vSql = OmdViewTool.getBTSViewSql(asList.toArray(new String[0]), vName);
				if (!vSql.equals("")) {
					createSQLQuery = session.createSQLQuery(vSql);
					int re = createSQLQuery.executeUpdate();
					if (re < 0) {
						return false;
					}
				}
			}
		}
		return true;
	}



	/**
	 * 清空业务表， 业务类型
	 * 
	 * @throws Exception
	 */
	public boolean deleteBtsAndTables(BtmItem[] bts) throws Exception {
		deleteBtmItems(bts);
		Session session = HibernateSessionFactory.getSession();
		for (BtmItem bt : bts) {
			String tableName = OmdHelper.getBTTableName(bt.name);
			String sql = "drop table " + tableName;
			try {
				session.createSQLQuery(sql).executeUpdate();
				// 处理HibernateException, 保证一条sql不成功时, 继续执行下一条.
			} catch (HibernateException e) {
				e.printStackTrace();
			}
		}
		return true;
	}

	/**
	 * 获取表中fields中不能 删除的字段
	 * 
	 * @throws Throwable
	 */

	public String[] getUnRemovableFields(String tableName, String[] fields) throws Throwable {
		try{
			List<String> list = new ArrayList<String>();
			Session session = HibernateSessionFactory.getSession();
			for(String field : fields){
				String sql = "select count(oid) from " + tableName + " where " + field + " is not null";
				int count = ((BigDecimal)session.createSQLQuery(sql).list().get(0)).intValue();
				if(count > 0){
					list.add(field);
				}
			}
			return list.toArray(new String[0]);
		}catch(Throwable e){
			throw e;
		}
	}

	public boolean xml2DB(String userName) throws VCIError {
//		List<BtmItem> news = Xml2DBDelegate.getInstance().getNews(userName);
//		if (news == null) {
//			return true;
//		}
//		for (BtmItem o : news) {
//			try {
//				addBtmItemNoCache(o);
//			} catch (Throwable e) {
//				e.printStackTrace();
//				ServerWithLog4j.logger.info(o.name + "迁移失败, 业务类型的迁移中止!");
//				return false;
//			}
//		}
		return true;
	}

//	public boolean addBtmItemNoCache(BtmItem btmItem) throws Exception {
//		boolean flag = false;
//		String insertSql = "insert into plbtmtype values(?,?,?,?,?,?,?,?,?,xmltype(?))";
//		Connection connection = HibernateSessionFactory.getSessionConnection();
//		PreparedStatement pst = connection.prepareStatement(insertSql);
//		pst.setString(1, ObjectUtility.getNewObjectID36());
//		pst.setString(2, btmItem.name);
//		pst.setString(3, btmItem.label);
//		pst.setString(4, btmItem.description);
//		long time = Calendar.getInstance().getTimeInMillis();
//		Timestamp ts = new Timestamp(time);
//		pst.setTimestamp(5, ts);
//		pst.setString(6, btmItem.creator);
//		pst.setTimestamp(7, ts);
//		pst.setString(8, btmItem.modifier);
//		pst.setTimestamp(9, ts);
//		String xmlText = BTMServiceHelper.getInstance().getXmlText(btmItem);
//		// CLOB content = BTMServiceImplHelper.getInstance().getXmlTypeContent(xmlText,
//		// connection);
//		// pst.setObject(10, content);
//		pst.setString(10, xmlText);
//		pst.executeUpdate();
//		ServerWithLog4j.logger.debug(insertSql);
//		pst.close();
//		flag = true;
//		return flag;
//	}

	/**
	 * 若表tableName不存在，则用createSql创建表
	 */

	public boolean checkTable(String tableName, String createSql) throws VCIError {
		try {
			String sql = "select count(table_name) count from user_tables where table_name = ?";
			Connection connection = HibernateSessionFactory.getSessionConnection();
			PreparedStatement pst = connection.prepareStatement(sql);
			pst.setString(1, tableName.toUpperCase());
			ResultSet rs = pst.executeQuery();
			ServerWithLog4j.logger.debug(sql);
			int count = 0;
			while (rs.next()) {
				count = rs.getInt("count");
			}
			rs.close();
			pst.close();

			if (count < 1) {
				pst = connection.prepareStatement(createSql);
				pst.executeUpdate();
				ServerWithLog4j.logger.debug(createSql);
				pst.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	// 根据id查询状态
	public BtmItem getBTMByOid(String oid) throws Exception {
		// String sql = "select oid, name, label, description, ts, creator, createtime,
		// modifier, modifytime, t.content.getclobval() content from plbtmtype t where
		// t.oid =?";

		String sql = "";
		switch (HibernateSessionFactory.getDbType()) {
		case DM8:
			sql = "select oid, name, label, description, ts, creator, createtime, modifier, modifytime, content from plbtmtype t where t.oid =?";
			break;
		case ORACL:
		default:
			sql = "select oid, name, label, description, ts, creator, createtime, modifier, modifytime, t.content.getclobval() content from plbtmtype 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);
		BtmItem bt = null;
		while (rs.next()) {
			bt = getBT(rs);
		}
		rs.close();
		pst.close();
		return bt;
	}

	/**
	 * 生成创建业务类型的DDL
	 * @param btm
	 * @return
	 */
	private String getCreateBTMTableSql(BtmItem btm){
		String btmName = btm.name;
		String btmTableName = OmdHelper.getBTTableName(btmName);
		String sql = "create Table " + btmTableName + "(" + DDLHelper.getBTSysFields();
		String[] apNames = btm.apNameArray;

		if(apNames != null && apNames.length != 0){
			for(int k = 0; k < apNames.length; k++){
				String abName = apNames[k];
				AttribItem abItem = null;

				abItem = OMCacheProvider.getAttribute(abName);

				String abSql = DDLHelper.getAbSql(abItem);
				sql += abSql;
			}
		}
		
		sql = sql.substring(0, sql.lastIndexOf(","));
		sql += "\n)";
		return sql;
	}
	
	/**
	 * 获取增加主键sql
	 * @param typeName
	 * @return
	 */
	private String getAddPKSql(String typeName){
		return "alter table " + OmdHelper.getBTTableName(typeName) + " add constraint PKBTM_" + typeName + " primary key (OID)\n";
	}
	
	/**
	 * 获取业务类型属性总集合
	 * 
	 * @param btNames
	 * @return
	 */
//	private Set<String> getUserAttrNameSet(List<String> btNames) {
//		Set<String> set = new LinkedHashSet<String>();
//		try {
//			for (String btName : btNames) {
//				String[] abNames = getBtmApNameArray(btName);
//				for (String abName : abNames) {
//					set.add(abName);
//				}
//			}
//		} catch (Throwable e) {
//			e.printStackTrace();
//		}
//		return set;
//	}

	/**
	 * 获取各个业务类型查询属性总集合的sql 当业务业务类型中不包含某个属性attrA时, '' as attrA
	 * 
	 * @param allAttrName
	 * @param btNames
	 * @return
	 * @throws Exception
	 */
//	private Map<String, StringBuilder> getBTSqlMap(Set<String> allAttrName, List<String> btNames) throws Exception {
//		// 记录业务类型和与其对应的属性列表
//		Map<String, List<String>> btAttrMap = new HashMap<String, List<String>>();
//		// 记录业务类型和与其对应的查询sql
//		Map<String, StringBuilder> btSqlMap = new HashMap<String, StringBuilder>();
//		String[] sysAbItems = OmdTools.getBtSysANames();
//		String querySysAttrSql = "select " + StringUtils.join(sysAbItems, ",");
//		for (String btName : btNames) {
//			String[] abNames = getBtmApNameArray(btName);
//			btAttrMap.put(btName, Arrays.asList(abNames));
//			btSqlMap.put(btName, new StringBuilder(querySysAttrSql));
//		}
//
//		for (String attrName : allAttrName) {
//			for (String btName : btNames) {
//				btSqlMap.get(btName).append(",");
//				if (btAttrMap.get(btName).contains(attrName)) {
//					btSqlMap.get(btName).append(attrName);
//				} else {
//					btSqlMap.get(btName).append("null as " + attrName);
//				}
//			}
//		}
//
//		for (String btName : btNames) {
//			btSqlMap.get(btName).append(" from " + OmdTools.getBTMTableName(btName));
//		}
//		return btSqlMap;
//	}

	public boolean hasData(String btName) {
		boolean flag = false;
		Session session = HibernateSessionFactory.getSession();

		String table = OmdHelper.getBTTableName(btName);
		// 判断表是否存在, 表不存在则该表无数据
		String sql_ = "select count(1) from user_tables where TABLE_NAME = '" + table + "'";
		List<?> list_ = session.createSQLQuery(sql_).list();
		// 当list.get(i)中Object数量为1时, list.get(i)为Object
		// 当list.get(i)中Object数量 > 1时, list.get(i)为Object
		Object obj_ = list_.get(0);
		int count_ = ((BigDecimal) obj_).intValue();
		if (count_ < 1) {
			return false;
		}
		String sql = "select count(*) from " + table;
		List<?> list = session.createSQLQuery(sql).list();
		// 当list.get(i)中Object数量为1时, list.get(i)为Object
		// 当list.get(i)中Object数量 > 1时, list.get(i)为Object
		Object obj = list.get(0);
		int count = ((BigDecimal) obj).intValue();
		if (count > 0) {
			flag = true;
		}
		return flag;
	}

	//=========================================================================
	
	/**
	 * 获取指定的业务类型
	 * @throws Throwable 
	 */
	public BtmItem getBtmItemByName(String btmName) throws Throwable{
		//String sql = "select oid, name, label, description, ts, creator, createtime, modifier, modifytime, t.content.getclobval() content from plbtmtype t where t.name =?";
		
		String sql = "";
		switch (HibernateSessionFactory.getDbType()) {
			case DM8:
				sql = "select oid, name, label, description, ts, creator, createtime, modifier, modifytime, content from plbtmtype t where t.name =?";
				break;
			case ORACL:
			default:
				sql = "select oid, name, label, description, ts, creator, createtime, modifier, modifytime, t.content.getclobval() content from plbtmtype t where t.name =?";
				break;
		}
		
		Connection connection = HibernateSessionFactory.getSessionConnection();
		PreparedStatement pst = connection.prepareStatement(sql);
		pst.setString(1, btmName);
		ResultSet rs = pst.executeQuery();
		BtmItem bt = null;
		while(rs.next()){
			bt = getBT(rs);
		}
		rs.close();
		pst.close();
		if(bt == null){
			bt = new BtmItem();
			bt.apNameArray = new String[0];
			bt.lifeCycles = new String[0];
		}
		return bt;
	}
	
	
	public BtmItem[] getBtmItems(String[] btNames) throws SQLException, IOException, DocumentException {
		//StringBuilder sql = new StringBuilder("select oid, name, label, description, ts, creator, createtime, modifier, modifytime, t.content.getclobval() content from plbtmtype 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 plbtmtype t where ");
				break;
			case ORACL:
			default:
				sql = new StringBuilder("select oid, name, label, description, ts, creator, createtime, modifier, modifytime, t.content.getclobval() content from plbtmtype t where ");
				break;
		}
		
		List<BtmItem> bts = new ArrayList<BtmItem>();
		if(btNames != null && btNames.length > 0){
			for(String btName : btNames){
				sql.append("t.name='" + btName + "'");
				sql.append(" or ");
			}
		} else {
			sql.append(" 1 = 1 or ");
		}
		String sql_ = sql.substring(0, sql.lastIndexOf(" or "));
		Connection connection = HibernateSessionFactory.getSessionConnection();
		PreparedStatement pst = connection.prepareStatement(sql_);
		ResultSet rs = pst.executeQuery();
		
		ServerWithLog4j.logger.info(sql);
		
		//System.out.println(sql);
		while(rs.next()){
			BtmItem bt = getBT(rs);
			bts.add(bt);
		}
		rs.close();
		pst.close();
		return bts.toArray(new BtmItem[0]);
	}
	
	/**
	 * tableName and columns Map
	 * @return
	 */
	public Map<String, List<String>> getTableAndCols(){
		Map<String, List<String>> map = new HashMap<String, List<String>>();
		int sysAbLength = OmdHelper.getBTSysANames().length;
		String sql = "select t.TABLE_NAME, t.COLUMN_NAME from user_tab_columns t where t.TABLE_NAME like 'PLATFORMBTM_%' and t.COLUMN_ID > " +
				sysAbLength + " order by t.TABLE_NAME";
		Session session = HibernateSessionFactory.getSession();
		List<?> list = session.createSQLQuery(sql).list();
		for(int i = 0; i < list.size(); i++){
			Object[] o = (Object[]) list.get(i);
			String tableName = ((String) o[0]).toLowerCase();
			String colName = ((String) o[1]).toLowerCase();
			List<String> colList = map.get(tableName);
			if(colList == null){
				colList = new ArrayList<String>();
				colList.add(colName);
				map.put(tableName, colList);
			}else{
				colList.add(colName);
			}
		}
		return map;
	}
	
	/**
	 * 获取所有业务类型表
	 * @return
	 */
	public List<String> getAllBtmTables(){
		List<String> tableNameList = new ArrayList<String>();
		String sql = "select t.TABLE_NAME from user_tables t where t.TABLE_NAME like 'PLATFORMBTM_%'";
		Session session = HibernateSessionFactory.getSession();
		List<?> list = session.createSQLQuery(sql).list();
		for(int i = 0; i < list.size(); i++){
			String tableName = (String) list.get(i);
			tableNameList.add(tableName.toLowerCase());
		}
		return tableNameList;
	}
	
	/**
	 * 获取所有的视图（平台创建的）
	 * @return 视图的名字
	 */
	public List<String> getAllBtmViews(){
		List<String> viewNameList = new ArrayList<String>();
		String sql = "select t.view_name from user_views t where t.view_name like 'PLATFORMBTM_%'";
		Session session = HibernateSessionFactory.getSession();
		List<?> list = session.createSQLQuery(sql).list();
		for(int i = 0; i < list.size(); i++){
			String tableName = (String) list.get(i);
			viewNameList.add(tableName.toLowerCase());
		}
		return viewNameList;
	
	}
	
	/**
	 * 
	 * @param type
	 * @param sql:_ADD(cols)/_DROP(cols)
	 * @return
	 * @throws Throwable 
	 */
	public String parseToSql(String type, String sql) throws Throwable{
		String operator = null;
		if(sql.contains("_ADD")){
			operator = "add";
		}else if(sql.contains("_DROP")){
			operator = "drop";
		}else if(sql.contains("_CREATE")){
			operator = "create";
		}else{
			return null;
		}
		
		if(operator.equals("create")){
			return getCreateBTMTableSql(getBtmItemByName(type));
		}else{
			StringBuilder stb = new StringBuilder("alter table ");
			stb.append(OmdHelper.getBTTableName(type));
			stb.append(" ");
			stb.append(operator);
			stb.append("(");
			sql = sql.substring(sql.indexOf("(") + 1, sql.indexOf(")"));
			String[] abNames = sql.split(",");
			if(operator.equals("add")){
				for(int i = 0; i < abNames.length; i++){
					String abName = abNames[i];
					AttribItem abItem = OMCacheProvider.getAttribute(abName);
					String abSql_ = DDLHelper.getAbSql(abItem);
					stb.append(abSql_);
				}
			}else if(operator.equals("drop")){
				for(int i = 0; i < abNames.length; i++){
					String abName = abNames[i];
					stb.append(abName);
					if(i != abNames.length-1){
					     stb.append(",");
					}
				}
			}
			//modify by weidy@2020-12-16
			//增加逗号的判断
			String temp = stb.toString();
			if(temp.endsWith(",")){
				temp = temp.substring(0,temp.length()-1);
			}
			if(temp.endsWith(",\n\t")){
				temp = temp.substring(0,temp.length()-",\n\t".length());
			}
			return temp + ")";
		}
	}


	/**
	 * 将bt转化成xmltext
	 * @param bt
	 * @return
	 */
	private String getXmlText(BtmItem bt){

		String apNameArray = arrayTOString(bt.apNameArray);
		String lifeCycles = arrayTOString(bt.lifeCycles);
		StringBuilder stb = new StringBuilder("<btm>");
		stb.append("<name>" + bt.name + "</name>");
		stb.append("<label>" + bt.label + "</label>");
		stb.append("<description>" + bt.description + "</description>");
		stb.append("<isAbstract>" + bt.isAbstract + "</isAbstract>");
		stb.append("<shape>" + bt.shape + "</shape>");
		stb.append("<implClass>" + bt.implClass + "</implClass>");
		stb.append("<fName>" + bt.fName + "</fName>");
		stb.append("<lifeCycle>" + bt.lifeCycle + "</lifeCycle>");
		stb.append("<imageName>" + bt.imageName + "</imageName>");
		stb.append("<revLevel>" + bt.revLevel + "</revLevel>");
		stb.append("<revRuleName>" + bt.revRuleName + "</revRuleName>");
		stb.append("<revInput>" + bt.revInput + "</revInput>");
		stb.append("<delimiter>" + bt.delimiter + "</delimiter>");
		stb.append("<verRuleName>" + bt.verRuleName + "</verRuleName>");
		stb.append("<apNameArray>" + apNameArray + "</apNameArray>");
		stb.append("<lifeCycles>" + lifeCycles + "</lifeCycles>");
		stb.append("</btm>");
		return stb.toString();
	}
	
	/**
	 * Array-->String
	 * @param array
	 * @return
	 */
	private String arrayTOString(String[] array){
		String str = "";
		if(array != null && array.length > 0){
			for(int i = 0; i < array.length; i++){
				str += array[i];
				str += ",";
			}
			str = str.substring(0, str.lastIndexOf(","));
		}
		return str;
	}
	

	/**
	 * 将查询的clob对象转换成xml的element, 便于解析
	 * @param clob
	 * @return
	 * @throws SQLException
	 * @throws IOException
	 * @throws DocumentException
	 */
	private Element getBtDetails(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;
	}
	
	/**
	 * 将一条数据库中的记录转化成btmitem
	 * @param rs
	 * @return
	 * @throws SQLException
	 */
	private BtmItem getBT(ResultSet rs) throws SQLException, IOException, DocumentException{
		BtmItem bt = new BtmItem();
		String value = rs.getString("oid");
		bt.oid = (value == null ? "" : value);
		value = rs.getString("name");
		bt.name = (value == null ? "" : value);
		value = rs.getString("label");
		bt.label = (value == null ? "" : value);
		value = rs.getString("description");
		bt.description = (value == null ? "" : value);
		bt.ts = rs.getTimestamp("ts").getTime();
		value = rs.getString("creator");
		bt.creator = (value == null ? "" : value);
		bt.createTime = rs.getTimestamp("createTime").getTime();
		value = rs.getString("modifier");
		bt.modifier = (value == null ? "" : value);
		bt.modifyTime = rs.getTimestamp("modifyTime").getTime();
		//CLOB clob = (CLOB) rs.getClob("content");
		Clob clob = rs.getClob("content");
		Element btDetails = getBtDetails(clob);
		setBTValueFormDoc(bt, btDetails);
		return bt;
	}
	
	/**
	 * 设置EnumItem存在
	 * @param att
	 * @param element
	 */
	private void setBTValueFormDoc(BtmItem bt, Element element){
		String value = element.elementText("isAbstract");
		bt.isAbstract = (value == null ? false : Boolean.valueOf(value));
		value = element.elementText("fName");
		bt.fName = (value == null ? "" : value);
		value = element.elementText("implClass");
		bt.implClass = (value == null ? "" : value);
		value = element.elementText("shape");
		bt.shape = (value == null ? "" : value);
		value = element.elementText("lifeCycle");
		bt.lifeCycle = (value == null ? "" : value);
		value = element.elementText("lifeCycles");
		if(value != null && !value.equals("")){
			bt.lifeCycles = value.trim().split(",");
		}else{
			bt.lifeCycles = new String[0];
		}
		value = element.elementText("imageName");
		bt.imageName = (value == null ? "" : value);
		value = element.elementText("revLevel");
		bt.revLevel = (value == null ? 0 : Short.valueOf(value));
		value = element.elementText("revRuleName");
		bt.revRuleName = (value == null ? "" : value);
		value = element.elementText("revInput");
		bt.revInput = (value == null ? false : Boolean.valueOf(value));
		value = element.elementText("delimiter");
		bt.delimiter = (value == null ? "" : value);
		value = element.elementText("verRuleName");
		bt.verRuleName = (value == null ? 0 : Short.valueOf(value));
		value = element.elementText("apNameArray");
		if(value != null && !value.equals("")){
			bt.apNameArray = value.trim().split(",");
		}else{
			bt.apNameArray = new String[0];
		}
	}
	

}