package com.vci.server.query.delegate;

import java.math.BigDecimal;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.dom4j.DocumentException;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.transform.Transformers;
import org.omg.CORBA.IntHolder;

import com.vci.server.base.exception.ExceptionLocalHandler;
import com.vci.server.base.persistence.dao.HibernateSessionFactory;
import com.vci.server.base.utility.OQueryHelper;
import com.vci.server.base.utility.ServerServiceProvider;
import com.vci.server.cache.ConfigCacheProvider;
import com.vci.server.cache.OMCacheProvider;
import com.vci.server.query.parsers.Parser;
import com.vci.server.query.service.QueryObjectService;
import com.vci.server.query.util.QTSqlUtil;
import com.vci.server.query.util.SecretUtil;
import com.vci.corba.omd.btm.BtmItem;
import com.vci.corba.omd.data.BusinessObject;
import com.vci.corba.omd.data.LinkObject;
import com.vci.corba.omd.ltm.LinkType;
import com.vci.corba.query.data.BOAndLO;
import com.vci.corba.query.data.BtmRefQueryOption;
import com.vci.corba.query.data.KV;
import com.vci.common.exception.VciExceptionTool;
import com.vci.common.log.ServerWithLog4j;
import com.vci.common.qt.object.Condition;
import com.vci.common.qt.object.QTConstants;
import com.vci.common.qt.object.QueryTemplate;
import com.vci.common.resource.CommonProperties;
import com.vci.corba.framework.data.CheckValue;
import com.vci.corba.common.VCIError;

public class ObjectQueryServiceDelegate {
	
	private static ObjectQueryServiceDelegate instance = null;
	
	public static ObjectQueryServiceDelegate getInstance() {
		if (instance == null) {
			instance = new ObjectQueryServiceDelegate();
		}
		return instance;
	}
	
	private DateFormat dFormat = new SimpleDateFormat("yyyy-MM-dd");
	private Date date = Calendar.getInstance().getTime();

	private VCIError getLocalVciError(String key, Throwable e) {
		VCIError error = new VCIError(key, new String[]{VciExceptionTool.getExceptionStr(e), VciExceptionTool.getExceptionDetail(e)});
		VCIError rsError = ExceptionLocalHandler.getInstance().getLocalString(error, "PLMOQ");
		return rsError;
	}
	
	protected QueryTemplate getQTByNamAndText(String qtName, String qtText) throws DocumentException, VCIError{
//		Document doc = DocumentHelper.parseText(qtText);
//		if(doc == null) return null;
		return OQueryHelper.getQTByQTText(qtName, qtText);
	}
	/**
	 * 根据查询模板名, 查询模板内容查询业务对象
	 * @param qtName 查询模板的名称（内部名称）
	 * @param qtText 查询模板结构化内容XML形式的字符串（需要在调用客户端进行转化）
	 * @param count 查询结果的总数，通过 count.value 获取
	 * @return 查询到的业务类型数据  <code>com.vci.corba.omd.BusinessObject</code>
	 * findBTMObjectsV2 能够在返回数据的同时，返回数据总数
	 */
	public BusinessObject[] findBTMObjects(String qtName, String qtText)
			throws VCIError {
		return this.findBTMObjectsV2(qtName, qtText, new IntHolder(0));
	}
	/**
	 * 根据查询模板名, 查询模板内容查询业务对象-V2版
	 * 
	 * @param qtName 查询模板的名称（内部名称）
	 * @param qtText 查询模板结构化内容XML形式的字符串（需要在调用客户端进行转化）
	 * @param count 查询结果的总数，通过 count.value 获取
	 * @return 查询到的业务类型数据  <code>com.vci.corba.omd.BusinessObject</code>
	 * @since v1.1 2017.08.31
	 * @see com.vci.corba.omd.data.BusinessObject
	 */
	public BusinessObject[] findBTMObjectsV2(String qtName, String qtText, IntHolder count)
			throws VCIError {
		try {
			List<BusinessObject> boList = new ArrayList<BusinessObject>();
			QueryTemplate qt = getQTByNamAndText(qtName, qtText);
			if(qt == null){
				return boList.toArray(new BusinessObject[0]);
			}
			return findBTMObjectsV2(qt, count);
		} catch (Throwable e) {
			ServerWithLog4j.logger.error("findBTMObjectsV2 Error", e);
			throw getLocalVciError("P0010QT-00001", e);
		}
	}
	/**
	 * 查询模板数据查询业务对象-v3 : 查BO的同时，根据参照查询选项，查询参照数据
	 * 
	 * @param qtName 查询模板的名称（内部名称）
	 * @param qtText 查询模板结构化内容XML形式的字符串（需要在调用客户端进行转化）
	 * @param count 查询结果的总数，通过 count.value 获取
	 * @param btmRefQueryOptions 参照查询选项参数
	 * @return 查询到的业务类型数据  <code>com.vci.corba.omd.BusinessObject</code>
	 * @since 2017.12.20
	 * @author xiongchao
	 * @see com.vci.corba.omd.data.BusinessObject
	 */
	public BusinessObject[] findBTMObjectsV3(String qtName, String qtText, IntHolder count, BtmRefQueryOption[] btmRefQueryOptions) throws VCIError{
		try {
			List<BusinessObject> boList = new ArrayList<BusinessObject>();
			QueryTemplate qt = getQTByNamAndText(qtName, qtText);
			if(qt == null){
				return boList.toArray(new BusinessObject[0]);
			}
			return findBTMObjectsV3(qt, count, btmRefQueryOptions);
		} catch (Throwable e) {
			ServerWithLog4j.logger.error("findBTMObjectsV3 Error", e);
			throw getLocalVciError("P0010QT-00001", e);
		}
	}
	
	/**
	 * 根据查询模板名, 查询模板内容查询业务对象
	 */
	public BusinessObject[] findBTMObjects(QueryTemplate qt) throws VCIError {
		return this.findBTMObjectsV2(qt, new IntHolder(0));
	}

	/**
	 * 根据查询模板对象查询业务对象-V2版
	 * 
	 * @param qt 查询模板对象
	 * @param count 查询结果的总数，通过 count.value 获取
	 * @return 查询到的业务类型数据  <code>com.vci.corba.omd.BusinessObject</code>
	 * @since v1.1 2017.08.31
	 * @see com.vci.corba.omd.data.BusinessObject
	 */
	public BusinessObject[] findBTMObjectsV2(QueryTemplate qt, IntHolder count) throws VCIError {
		try {
			List<BusinessObject> boList = new ArrayList<BusinessObject>();
			if(qt == null){
				return boList.toArray(new BusinessObject[0]);
			}
			if(count == null){
				count = new IntHolder(0);
			}
			boList = QueryObjectService.getInstance().findBtmObjectsV2(qt, count);
			findBTMObjectChildrenDatas(qt, boList);
			return boList.toArray(new BusinessObject[0]);
		} catch (Throwable e) {
			ServerWithLog4j.logger.error("findBTMObjectsV2 Error", e);
			throw getLocalVciError("P0010QT-00001", e);
		}
	}
	
	/**
	 * 根据查询模板对象查询业务对象-V2版
	 * 
	 * @param qt 查询模板对象
	 * @param count 查询结果的总数，通过 count.value 获取
	 * @param btmRefQueryOptions 参照查询选项参数 
	 * @return 查询到的业务类型数据  <code>com.vci.corba.omd.BusinessObject</code>
	 * @since 2017.12.20
	 * @author xiongchao
	 * @see com.vci.corba.omd.data.BusinessObject
	 */
	public BusinessObject[] findBTMObjectsV3(QueryTemplate qt, IntHolder count, BtmRefQueryOption[] btmRefQueryOptions) throws VCIError {
		try {
			List<BusinessObject> boList = new ArrayList<BusinessObject>();
			if(qt == null){
				return boList.toArray(new BusinessObject[0]);
			}
			if(count == null){
				count = new IntHolder(0);
			}
			boList = QueryObjectService.getInstance().findBtmObjectsV3(qt, count, btmRefQueryOptions);
			findBTMObjectChildrenDatas(qt, boList);
			return boList.toArray(new BusinessObject[0]);
		} catch (Throwable e) {
			ServerWithLog4j.logger.error("findBTMObjectsV3 Error", e);
			throw getLocalVciError("P0010QT-00001", e);
		}
	}
	protected void findBTMObjectChildrenDatas(QueryTemplate qt, List<BusinessObject> boList) throws Throwable{
		if(qt.isQueryChildren()){
			//查询子类型
			BtmItem[] btms = ServerServiceProvider.getOMDService().getBTMService().getChildrenBtms(qt.getBtmType());
			for(int i = 0; i < btms.length; i++){
				qt.setBtmType(btms[i].name);
				boList.addAll(QueryObjectService.getInstance().findBtmObjects(qt));
			}
		}
	}

	/**
	 * 根据查询模板名, 查询模板内容查询链接对象
	 */
	public LinkObject[] findLTObjects(String qtName, String qtText)
			throws VCIError {
		return findLTObjectsV2(qtName, qtText, new IntHolder(0));
	}
	/**
	 * 根据查询模板名, 查询模板内容查询链接对象-V2版
	 * 
	 * @param qtName 查询模板的名称（内部名称）
	 * @param qtText 查询模板结构化内容XML形式的字符串（需要在调用客户端进行转化）
	 * @param count 查询结果的总数，通过 count.value 获取
	 * @return 查询到的业务类型数据  <code>com.vci.corba.omd.LinkObject</code>
	 * @since v1.1 2017.08.31
	 * @see com.vci.corba.omd.data.LinkObject
	 */
	public LinkObject[] findLTObjectsV2(String qtName, String qtText, IntHolder count)
			throws VCIError {
		try {
			List<LinkObject> loList = new ArrayList<LinkObject>();
			QueryTemplate qt = getQTByNamAndText(qtName, qtText);
			if(qt == null){
				return loList.toArray(new LinkObject[0]);
			}
			//查询一层, 分查询是否有有下一级和不查询是否有有下一级
			if(qt.getLevel() == 1){
				loList = QueryObjectService.getInstance().findLinkObjectOneLevelV2(qt, count);
				//查询多层, 或者没有层数限制
			}else{
				loList = QueryObjectService.getInstance().findLinkObjectV2(qt, count);
				int recReturnMode = qt.getRecReturnMode();
				//对查询结果进行过滤
				if(recReturnMode == QTConstants.RECRETURNMODE_FILTER){
					loList = QueryObjectService.getInstance().filterLos(loList);
				}
			}
			
			return loList.toArray(new LinkObject[0]);
		} catch (Throwable e) {
			ServerWithLog4j.logger.error("findLTObjectsV2 Error", e);
			throw getLocalVciError("P0010QT-00002", e);
		}
	}
	


	/**
	 * 根据查询模板获取linkObject 和 关联的businessObject
	 */
	public BOAndLO[] getBOAndLOS(String qtName, String qtText, String btmOId) throws VCIError {
		try{
//			Document doc = DocumentHelper.parseText(qtText);
//			if(doc == null){
//				return new BOAndLO[0];
//			}
			QueryTemplate qt = OQueryHelper.getQTByQTText(qtName, qtText);
			if(qt == null){
				return new BOAndLO[0];
			}
			List<BOAndLO> list = null;
			//查询一层, 分查询是否有有下一级和不查询是否有有下一级
			if(qt.getLevel() == 1){
				list = QueryObjectService.getInstance().findBOAndLOByViewOneLevel(qt);
			//查询多层, 或者没有层数限制
			}else{
				list = QueryObjectService.getInstance().findBOAndLOByView(qt,btmOId);
				//对查询结果进行过滤
				int recReturnMode = qt.getRecReturnMode();
				if(recReturnMode == QTConstants.RECRETURNMODE_FILTER){
					list = QueryObjectService.getInstance().filterLoAndBOs(list);
				}
			}
			return list.toArray(new BOAndLO[0]);
		}catch(Throwable e){
			ServerWithLog4j.logger.error("getBOAndLOS Error", e);
			throw getLocalVciError("P0010QT-00007", e);
		}
	}
	
	/**
	 * 根据查询模板获取linkObject 和 关联的businessObject
	 */
	public BOAndLO[] getBOAndLOS(QueryTemplate qt, String btmOId) throws VCIError {
		try{
			if(qt == null){
				return new BOAndLO[0];
			}
			List<BOAndLO> list = null;
			//查询一层, 分查询是否有有下一级和不查询是否有有下一级
			if(qt.getLevel() == 1){
				list = QueryObjectService.getInstance().findBOAndLOByViewOneLevel(qt);
			//查询多层, 或者没有层数限制
			}else{
				list = QueryObjectService.getInstance().findBOAndLOByView(qt,btmOId);
				//对查询结果进行过滤
				int recReturnMode = qt.getRecReturnMode();
				if(recReturnMode == QTConstants.RECRETURNMODE_FILTER){
					list = QueryObjectService.getInstance().filterLoAndBOs(list);
				}
			}
			return list.toArray(new BOAndLO[0]);
		}catch(Throwable e){
			ServerWithLog4j.logger.error("getBOAndLOS Error", e);
			throw getLocalVciError("P0010QT-00007", e);
		}
	}

	/**
	 * 根据业务类型名, 版本条件查询业务对象  
	 * version: 0:所有版次; 1:当前版本当前版次; 2:当前版本最新版次; 3:最新版本最新版次.
	 */
	public BusinessObject[] findBTMObjectsByTypeNameAndVersion(String typeName,
			int version) throws VCIError {
		try{
			List<BusinessObject> boList = new ArrayList<BusinessObject>();
			QueryTemplate qt = new QueryTemplate();
			qt.setType(QTConstants.TYPE_BTM);
			qt.setBtmType(typeName);
			List<String> clauseList = new ArrayList<String>();
			clauseList.add("*");
			qt.setClauseList(clauseList);
			qt.setVersion(version);
			boList = QueryObjectService.getInstance().findBtmObjects(qt);
			return boList.toArray(new BusinessObject[0]);
		}catch(Throwable e){
			ServerWithLog4j.logger.error("findBTMObjectsByTypeNameAndVersion Error", e);
			throw getLocalVciError("P0010QT-00010", e);
		}
	}
	
	/**
	 * 根据查询模板名, 查询模板内容返回查询总数
	 */
	public int findTotalCount(String qtName, String qtText) throws VCIError {
		StringBuilder sql = new StringBuilder();
		try {
			int totalCount = 0;
			//Document doc = DocumentHelper.parseText(qtText);
			QueryTemplate qt = OQueryHelper.getQTByQTText(qtName, qtText);
			if(qt.getType().equals(QTConstants.TYPE_LINK) && qt.getBtmType().equals("*")){
				String ltName = qt.getLinkType();
				//LinkType lt = ServerServiceProvider.getOMDService().getLinkTypeService().getLinkType(ltName);
				LinkType lt = OMCacheProvider.getLinkType(ltName);
				String[] btmNames = null;
				if(qt.getDirection().equals(QTConstants.DIRECTION_OPPOSITE)){
					btmNames = lt.btmItemsFrom;
				}else{
					btmNames = lt.btmItemsTo;
				}
				for(int i = 0; i < btmNames.length; i++){
					sql.setLength(0);
					qt.setBtmType(btmNames[i]);
					sql.append("select count(*) from ");
					
					Parser parser = new Parser(qt);
					String queryType = parser.getQueryType();
					sql.append(queryType);
					sql.append(" where 1 = 1 ");
					Condition condition = qt.getCondition();
					if(condition != null){
						sql.append("and ");
						sql.append(QTSqlUtil.getSql(qt, true));
						//sql.append(condition.getSql(qt, true));
					}
					sql.append(parser.getRevConstraint());
					sql.append(parser.getVerConstraint());
					//String rightSwitch = CommonProperties.getStringProperty("right.switch");
					if (parser.isRightSwitch() && qt.getType().equals(QTConstants.TYPE_BTM)){
						if(qt.isRightFlag()){
							sql.append(parser.getGrandRightSQL());
						}
					}
					sql.append(getSecretCheckSql(parser));
					
					Session session = HibernateSessionFactory.getSession();
					SQLQuery sqlQuery = session.createSQLQuery(sql.toString());
					List list = sqlQuery.list();
					totalCount += ((BigDecimal)list.get(0)).intValue();
				}
				return totalCount;
			}else{
				//StringBuilder sql = new StringBuilder();
				sql.append("select count(*) from ");
				
				Parser parser = new Parser(qt);
				String queryType = parser.getQueryType();
				sql.append(queryType);
				sql.append(" where 1 = 1 ");
				Condition condition = qt.getCondition();
				if(condition != null){
					sql.append("and ");
					sql.append(QTSqlUtil.getSql(qt, true));
					//sql.append(condition.getSql(qt, true));
				}
				sql.append(parser.getRevConstraint());
				sql.append(parser.getVerConstraint());
				//String rightSwitch = CommonProperties.getStringProperty("right.switch");
				if(parser.isRightSwitch() && qt.getType().equals(QTConstants.TYPE_BTM)){
					if(qt.isRightFlag()){
						sql.append(parser.getGrandRightSQL());
					}
				}
				sql.append(getSecretCheckSql(parser));
				
				Session session = HibernateSessionFactory.getSession();
				SQLQuery sqlQuery = session.createSQLQuery(sql.toString());
				List list = sqlQuery.list();
				totalCount = ((BigDecimal)list.get(0)).intValue();
				return totalCount;
			}
		}catch(Throwable e){
			ServerWithLog4j.logger.error("findTotalCount Error", e);
			throw getLocalVciError("P0010QT-00012", e);
		}
	}
	
	/** 用户密级校验和IP密级校验 
	 * @param parser 
	 * @throws Exception */
	public String getSecretCheckSql(Parser parser) throws Exception{
		StringBuffer sqlBu = new StringBuffer();
		boolean isUserCheckOpen = ConfigCacheProvider.isUserSecurity();//isCheckOpen("userSecuritySwith");
		if(isUserCheckOpen){
			String userFilterSQL = getUserFilterSQL(parser);
			sqlBu.append(userFilterSQL);
			//用户密级校验开启才进行IP密集校验
			boolean isIPCheckOpen = ConfigCacheProvider.isIpSecurity();//isCheckOpen("ipSecuritySwitch");
			if(isIPCheckOpen){
				String ipFilterSQL = getIPFilterSQL(parser);
				sqlBu.append(ipFilterSQL);
			}
		}
		return sqlBu.toString();
	}
	

//	private boolean isCheckOpen(String keyName){
//		UserEntityInfo userEnt = new UserEntityInfo();
//		
//		VCIInvocationInfo info = HibernateSessionFactory.getVciSessionInfo();
//		userEnt.userName = info.userName;
//		userEnt.modules = "QT";
//		userEnt.ip = info.clientIPInfo;
//		
//		String value = AppConfigProvider.getAppConfig(keyName);
//		if(value != null && "on".equals(value)){
//			return true;
//		}
//		
////		AppConfigDetailInfo cfgDetail;
////		try {
////			cfgDetail = ServerServiceProvider.getFrameService().getAppConfigDetailByKey(keyName, userEnt);
////			if(cfgDetail != null && "on".equals(cfgDetail.value)){
////				return true;
////			}
////		} catch (VCIError e) {
////			// TODO Auto-generated catch block
////			e.printStackTrace();
////		}
//		return false;
//	}

	public String getUserFilterSQL(Parser parser) throws Exception {
		String where = "";
		CheckValue value = parser.getCheckValue();
		try {
			//where = ServerServiceProvider.getAuth2Service().checkUserSecret(value);
			where = new SecretUtil().checkUserSecret(value);
		} catch (VCIError e) {
			ServerWithLog4j.logger.error("getUserFilterSQL Error", e);
		}
		return where;
	}
	
	public String getIPFilterSQL(Parser parser) throws Exception {
		String where = "";
		CheckValue value = parser.getCheckValue();
		try {
			//where = ServerServiceProvider.getAuth2Service().checkIPSecret(value);
			where = new SecretUtil().checkIPSecret(value);
		} catch (VCIError e) {
			ServerWithLog4j.logger.error("getIPFilterSQL Error", e);
		}
		return where;
	}


	/**
	 * 用标准sql查询数据
	 */
	public KV[][] queryBySql(String sql) throws VCIError {
		try {
			Session session = HibernateSessionFactory.getSession();
			SQLQuery sqlQuery = session.createSQLQuery(sql);
			sqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
			List list = sqlQuery.list();
			List<KV[]> listKVs = new ArrayList<KV[]>();
			for(int i = 0; i < list.size(); i++){
				List<KV> listKV = new ArrayList<KV>();
				Map<String, Object> map = (Map<String, Object>)list.get(i);
				for(String key : map.keySet()){
					Object value = map.get(key);
					
					listKV.add(new KV(key, getValueString(value)));
				}
				listKVs.add(listKV.toArray(new KV[0]));
			}
			return listKVs.toArray(new KV[0][0]);
		} catch (Throwable e) {
			ServerWithLog4j.logger.error("queryBySql Error", e);
			throw getLocalVciError("P0010QT-00014", e);
		}
	}

	private String getValueString(Object value) {
		if (value == null) {
			return "";
		} else if (value instanceof java.sql.Timestamp) {
			java.sql.Timestamp ts = (java.sql.Timestamp)value;
			return String.valueOf(ts.getTime());
		} else if (value instanceof java.sql.Date) {
			java.sql.Date ts = (java.sql.Date)value;
			return String.valueOf(ts.getTime());
		} else	
			return value.toString();
	}

	/**
	 * 用标准sql查询数据
	 */
	public String[][] queryBySqlWithoutKey(String sql) throws VCIError {
		return queryBySqlWithoutKeyExec(sql, null);
	}

	/**
	 * 使用标准sql查询数据（基于绑定变量的实现）
	 */
	public String[][] queryBySqlAndValuesWithoutKey(String sql, String[] paramValues) throws VCIError{
		return queryBySqlWithoutKeyExec(sql, paramValues);
	}
	
	protected String[][] queryBySqlWithoutKeyExec(String sql, String[] paramValues) throws VCIError {
		try {
			Session session = HibernateSessionFactory.getSession();
			SQLQuery sqlQuery = session.createSQLQuery(sql);
			if(paramValues != null && paramValues.length > 0){
				for (int i = 0; i < paramValues.length; i++) {
					sqlQuery.setString(i, paramValues[i]);
				}
			}
			List re = sqlQuery.list();
			List<String[]> lists = new ArrayList<String[]>();
			if(re.size() > 0){
				if(re.get(0) instanceof Object[]){
					for(Object obj : re){
						Object[] objs = (Object[]) obj;
						List<String> list = new ArrayList<String>();
						for(Object obj_ : objs){
							list.add(getValueString(obj));
						}
						lists.add(list.toArray(new String[0]));
					}
				}else{
					for(Object obj : re){
						List<String> list = new ArrayList<String>();
						list.add(getValueString(obj));
						lists.add(list.toArray(new String[0]));
					}
				}
			}
			return lists.toArray(new String[0][0]);
		} catch (Throwable e) {
			ServerWithLog4j.logger.error("queryBySqlWithoutKeyExec Error", e);
			throw getLocalVciError("P0010QT-00014", e);
		}
	}

}