package com.vci.server.query.parsers;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.Collections;
import java.util.List;

import com.vci.omd.objects.OtherInfo;
import com.vci.server.base.persistence.dao.HibernateSessionFactory;
import com.vci.server.base.utility.OmdHelper;
import com.vci.server.cache.ConfigCacheProvider;
import com.vci.server.cache.OMCacheProvider;
import com.vci.server.query.util.QTSqlUtil;
import com.vci.server.query.util.SecretUtil;
import com.vci.common.qt.object.ComparatorOrderInfo;
import com.vci.common.qt.object.Condition;
import com.vci.common.qt.object.Connector;
import com.vci.common.qt.object.Operator;
import com.vci.common.qt.object.OrderInfo;
import com.vci.common.qt.object.PageInfo;
import com.vci.common.qt.object.QTConstants;
import com.vci.common.qt.object.QueryTemplate;
import com.vci.common.qt.object.Symbol;
import com.vci.common.qt.object.Version;
import com.vci.common.resource.CommonProperties;
import com.vci.corba.framework.data.CheckValue;
import com.vci.corba.common.VCIError;
import com.vci.corba.common.data.VCIInvocationInfo;
import com.vci.corba.omd.atm.AttribItem;

/**
 * 
 * 将QueryTemplate定义的OQL解析为SQL并返回
 * 
 * @author Administrator
 *
 */
public class Parser {
	
	private boolean addGrandRightFlag = true;
	/**
	 * 是否增加授权SQL
	 * @return
	 */
	public boolean isAddGrandRight() {
		return addGrandRightFlag;
	}

	/**
	 * 是否增加授权SQL
	 * @param addGrandRightFlag
	 */
	public void setAddGrandRightFlag(boolean addGrandRightFlag) {
		this.addGrandRightFlag = addGrandRightFlag;
	}

	private QueryTemplate queryTemplate = null;
	
	public Parser(QueryTemplate queryTemplate){
		this.queryTemplate = queryTemplate;
		this.setAddGrandRightFlag(queryTemplate.isSecretFlag());//将密级的控制也写入进行
	}

	private boolean isIgnoreFilterCondition(){
		boolean res = false;
		String btmtype = this.queryTemplate.getBtmType();
		// ipsecurity ipstopmanager
		if(this.queryTemplate.getType().equals(QTConstants.TYPE_BTM) && 
				("ipsecurity".equalsIgnoreCase(btmtype) || "ipstopmanager".equalsIgnoreCase(btmtype))){
			res = true;
		} else {
			res = false;
		}
		return res;
	}
	/**
	 * 解析OQL为不同数据库对应的SQL
	 * 数据库: Oracle, SQLServer
	 * @return
	 * @throws Exception 
	 * @throws Throwable 
	 */
	public String parseToSql() {
		
		if(this.queryTemplate.getType().equals(QTConstants.TYPE_LINK)){
			return parseToSqlForLink();
		}
		
		StringBuilder sqlBu = new StringBuilder("");
		sqlBu.append(Symbol.SELECT);
		sqlBu.append(Symbol.SPACE);
		sqlBu.append(getClauses());
		sqlBu.append(Symbol.SPACE);
		sqlBu.append(Symbol.FROM);
		sqlBu.append(Symbol.SPACE);
		sqlBu.append(getQueryType());
		sqlBu.append(Symbol.SPACE);
		sqlBu.append(Symbol.WHERE);
		sqlBu.append(Symbol.SPACE);
		sqlBu.append("1 = 1");
		String conditionSql = null;
		Condition condi = queryTemplate.getCondition();
		if(condi != null && condi.getCIMap().size() > 0){
			sqlBu.append(Symbol.SPACE);
			sqlBu.append(Connector.AND);
			sqlBu.append(Symbol.SPACE);
			//conditionSql = queryTemplate.getCondition().getSql(queryTemplate, addGrandRightFlag);
			conditionSql = QTSqlUtil.getSql(queryTemplate, addGrandRightFlag);
			sqlBu.append(conditionSql);
		}
		
		//加上版本条件
		sqlBu.append(getRevConstraint());
		//加上版次条件
		sqlBu.append(getVerConstraint());
		
		if(!isIgnoreFilterCondition()){
			if(isRightSwitch() && this.queryTemplate.getType().equals(QTConstants.TYPE_BTM)){
				if(queryTemplate.isRightFlag()){
					String additionalSQL = "";
					if(addGrandRightFlag){
						additionalSQL = getGrandRightSQL();
					}
					sqlBu.append(additionalSQL);
				}
			}
			sqlBu.append(getSecretCheckSql());
		}
			
		sqlBu.append(getOrderSQL(queryTemplate.getOrderInfoList()));
		
		PageInfo pageInfo = queryTemplate.getPageInfo();
		if(pageInfo != null){
			String sql = page(sqlBu.toString(), queryTemplate.getPageInfo());
			return sql;
		}else {
			return sqlBu.toString();
		}
		
	}
	
	public String parseToSubSql() {
		
		if(this.queryTemplate.getType().equals(QTConstants.TYPE_LINK)){
			return parseToSqlForLink();
		}
		
		StringBuilder sqlBu = new StringBuilder("");
		sqlBu.append(Symbol.SELECT);
		sqlBu.append(Symbol.SPACE);
		sqlBu.append(getClauses());
		sqlBu.append(Symbol.SPACE);
		sqlBu.append(Symbol.FROM);
		sqlBu.append(Symbol.SPACE);
		sqlBu.append(getQueryType());
		sqlBu.append(Symbol.SPACE);
		sqlBu.append(Symbol.WHERE);
		sqlBu.append(Symbol.SPACE);
		sqlBu.append("1 = 1");
		String conditionSql = null;
		Condition condi = queryTemplate.getCondition();
		if(condi != null && condi.getCIMap().size() > 0){
			sqlBu.append(Symbol.SPACE);
			sqlBu.append(Connector.AND);
			sqlBu.append(Symbol.SPACE);
			//conditionSql = queryTemplate.getCondition().getSql(queryTemplate, addGrandRightFlag);
			conditionSql = QTSqlUtil.getSql(queryTemplate, addGrandRightFlag);
			sqlBu.append(conditionSql);
		}
		
		//加上版本条件
		sqlBu.append(getRevConstraint());
		//加上版次条件
		sqlBu.append(getVerConstraint());
		
		if(!isIgnoreFilterCondition()){
			if(isRightSwitch() && this.queryTemplate.getType().equals(QTConstants.TYPE_BTM)){
				if(queryTemplate.isRightFlag()){
					String additionalSQL = "";
					if(addGrandRightFlag){
						additionalSQL = getGrandRightSQL();
					}
					sqlBu.append(additionalSQL);
				}
			}
			sqlBu.append(getSecretCheckSql());
		}
			
		//sqlBu.append(getOrderSQL(queryTemplate.getOrderInfoList()));
		
		PageInfo pageInfo = queryTemplate.getPageInfo();
		if(pageInfo != null){
			String sql = page(sqlBu.toString(), queryTemplate.getPageInfo());
			return sql;
		}else {
			return sqlBu.toString();
		}
		
	}
	
	/** 用户密级校验和IP密级校验 
	 * @throws Exception */
	public String getSecretCheckSql() {
		StringBuffer sqlBu = new StringBuffer();
		boolean isUserCheckOpen = ConfigCacheProvider.isUserSecurity();//userSecuritySwith();
		if(isUserCheckOpen && this.addGrandRightFlag){//weidy@2018-06-19 针对流程事件中查询数据等场景下，添加密级的控制，默认是查询模板里控制密级的，
			String userFilterSQL = getUserFilterSQL();
			sqlBu.append(userFilterSQL);
			//用户密级校验开启才进行IP密集校验
			boolean isIPCheckOpen = ConfigCacheProvider.isIpSecurity();//ipSecuritySwith();
			if(isIPCheckOpen){
				String ipFilterSQL = getIPFilterSQL();
				sqlBu.append(ipFilterSQL);
			}
		}
		return sqlBu.toString();
	}

	
	public boolean isRightSwitch(){
		//权限开关开启，且rightFlag为TRUE时，加权
		String rightSwitch = "off";
		String rightSwitchKey = "right.switch";
		
		rightSwitch = CommonProperties.getStringProperty(rightSwitchKey);

		return "on".equalsIgnoreCase(rightSwitch);
	}

//	private boolean userSecuritySwith() {
//		try {
//			return ServerServiceProvider.getFrameService().userSecuritySwith();
//		} catch (Exception e) {
//			e.printStackTrace();
//		}
//		return false;
//	}
//	

//	private boolean ipSecuritySwith() {
//		try {
//			return ServerServiceProvider.getFrameService().ipSecuritySwitch();
//		} catch (Exception e) {
//			e.printStackTrace();
//		}
//		return false;
//	}
	
	
	private String getUserFilterSQL()  {
		String where = "";
		CheckValue value = getCheckValue();
		try {
			//where = ServerServiceProvider.getAuth2Service().checkUserSecret(value);
			where = new SecretUtil().checkUserSecret(value);
		} catch (VCIError e) {
			e.printStackTrace();
		}
		return where;
	}
	
	private String getIPFilterSQL()  {
		String where = "";
		CheckValue value = getCheckValue();
		try {
			//where = ServerServiceProvider.getAuth2Service().checkIPSecret(value);
			where = new SecretUtil().checkIPSecret(value);
		} catch (VCIError e) {
			e.printStackTrace();
		}
		return where;
	}
	
	
	/**
	 * 增加链接查询的解析sql方法
	 * 加入递归查询处理:
	 * 	根节点条件， 对子节点无效，即：start  with [普通条件] + [版本版次条件]
	 * 	连接条件(子节点条件)，对根节点无效， 即：connect  by [普通条件 - f_oid/t_oid条件] + [版本版次条件] + (level <= level + 1)
	 * 	层次条件： where level <= n，当n = -1时不加层次限制
	 * 注：当只查询link类型时, 版本版次条件应为当前版本当前版次, 此时getRevConstraint(), getVerConstraint() 都为"";
	 * @return
	 * @throws Exception 
	 */
	private String parseToSqlForLink() {
		StringBuilder sqlBu = new StringBuilder("");
		sqlBu.append(Symbol.SELECT);
		sqlBu.append(Symbol.SPACE);
		sqlBu.append(getClauses());
		sqlBu.append(Symbol.SPACE);
		sqlBu.append(Symbol.FROM);
		sqlBu.append(Symbol.SPACE);
		sqlBu.append(getQueryType());
		sqlBu.append(Symbol.SPACE);
		String levelConstraint = getLevelConstraint();
		if(!levelConstraint.equals("")){
			sqlBu.append(Symbol.WHERE);
			sqlBu.append(Symbol.SPACE);
			sqlBu.append(levelConstraint);
			sqlBu.append(Symbol.SPACE);
		}
		sqlBu.append(Symbol.START);
		sqlBu.append(Symbol.SPACE);
		sqlBu.append(Symbol.WITH);
		sqlBu.append(Symbol.SPACE);
		sqlBu.append("1 = 1");
		//start  with [普通条件]
		String conditionSql = null;
		Condition condi = queryTemplate.getCondition();
		if(condi != null && condi.getCIMap().size() > 0){
			sqlBu.append(Symbol.SPACE);
			sqlBu.append(Connector.AND);
			sqlBu.append(Symbol.SPACE);
			//conditionSql = queryTemplate.getCondition().getSql(queryTemplate, addGrandRightFlag);
			conditionSql = QTSqlUtil.getSql(queryTemplate, addGrandRightFlag);
			sqlBu.append(conditionSql);
		}
		
		//获取 connect by prior
		sqlBu.append(getConnectBy());
		if(conditionSql != null && !conditionSql.equals("")){
			//[普通条件 - f_oid/t_oid条件]
			conditionSql = conditionSql.replaceAll(".F_OID", ".f_oid");
			conditionSql = conditionSql.replaceAll(".T_OID", ".t_oid");
			String tableName = OmdHelper.getLTTableName(queryTemplate.getLinkType());
			if(this.queryTemplate.getDirection().equals(QTConstants.DIRECTION_POSITIVE)){
				if(conditionSql.matches(".+.f_oid = '[a-zA-Z0-9-]+'.+")){/*如果是嵌套查询时没必要重复添加条件。zhangjie,2015.7.3
					sqlBu.append(Symbol.SPACE);
					sqlBu.append(Connector.AND);
					sqlBu.append(Symbol.SPACE);
					sqlBu.append(conditionSql.replaceAll(tableName + ".f_oid = '[a-zA-Z0-9-]+'", "1 = 1"));
				*/}else if(conditionSql.matches(".+.f_oid in \\(['[a-zA-Z0-9-]+',* *]+\\).+")){
					sqlBu.append(Symbol.SPACE);
					sqlBu.append(Connector.AND);
					sqlBu.append(Symbol.SPACE);
					sqlBu.append(conditionSql.replaceAll(tableName + ".f_oid in \\(['[a-zA-Z0-9-]+',* *]+\\)", "1 = 1"));
				}else if(!conditionSql.contains(".f_oid")){
					sqlBu.append(Symbol.SPACE);
					sqlBu.append(Connector.AND);
					sqlBu.append(Symbol.SPACE);
					sqlBu.append(conditionSql);
				}
			}else{
				if(conditionSql.matches(".+.t_oid = '[a-zA-Z0-9-]+'.+")){
					sqlBu.append(Symbol.SPACE);
					sqlBu.append(Connector.AND);
					sqlBu.append(Symbol.SPACE);
					sqlBu.append(conditionSql.replaceAll(tableName + ".t_oid = '[a-zA-Z0-9-]+'", "1 = 1"));
				}else if(conditionSql.matches(".+.t_oid in \\(['[a-zA-Z0-9-]+',* *]+\\).+")){
					sqlBu.append(Symbol.SPACE);
					sqlBu.append(Connector.AND);
					sqlBu.append(Symbol.SPACE);
					sqlBu.append(conditionSql.replaceAll(tableName + ".t_oid in \\(['[a-zA-Z0-9-]+',* *]+\\)", "1 = 1"));
				}else if(!conditionSql.contains(".t_oid")){
					sqlBu.append(Symbol.SPACE);
					sqlBu.append(Connector.AND);
					sqlBu.append(Symbol.SPACE);
					sqlBu.append(conditionSql);
				}
			}
		}
		//加上level <= level + 1
		String connLevel = getConnectByLevelConstraint();
		if(!connLevel.equals("")){
			sqlBu.append(Symbol.SPACE);
			sqlBu.append(Connector.AND);
			sqlBu.append(Symbol.SPACE);
			sqlBu.append(connLevel);
		}
		
		sqlBu.append(getOrderSQL(queryTemplate.getOrderInfoList()));
		
		PageInfo pageInfo = queryTemplate.getPageInfo();
		if(pageInfo != null){
			String sql = page(sqlBu.toString(), queryTemplate.getPageInfo());
			return sql;
		}else {
			return sqlBu.toString();
		}
	}
	
	/**
	 * 获取链接查询模板的只查询链接对象的sql
	 * @return
	 * @throws Exception 
	 */
	public String getLinkQuerySql() throws Exception{
		StringBuilder sqlBu = new StringBuilder("");
		sqlBu.append(Symbol.SELECT);
		sqlBu.append(Symbol.SPACE);
		sqlBu.append(getClauses());
		sqlBu.append(Symbol.SPACE);
		sqlBu.append(Symbol.FROM);
		sqlBu.append(Symbol.SPACE);
		sqlBu.append(OmdHelper.getLTTableName(this.queryTemplate.getLinkType()));
		sqlBu.append(Symbol.SPACE);
		String levelConstraint = getLevelConstraint();
		if(!levelConstraint.equals("")){
			sqlBu.append(Symbol.WHERE);
			sqlBu.append(Symbol.SPACE);
			sqlBu.append(levelConstraint);
			sqlBu.append(Symbol.SPACE);
		}
		sqlBu.append(Symbol.START);
		sqlBu.append(Symbol.SPACE);
		sqlBu.append(Symbol.WITH);
		sqlBu.append(Symbol.SPACE);
		sqlBu.append("1 = 1");
		//start  with [普通条件] + [版本版次条件]
		String conditionSql = null;
		Condition condi = queryTemplate.getCondition();
		if(condi != null && condi.getCIMap().size() > 0){
			//conditionSql = queryTemplate.getCondition().getLinkConditionSql(queryTemplate, addGrandRightFlag);
			conditionSql = QTSqlUtil.getLinkConditionSql(queryTemplate, addGrandRightFlag);
			if(conditionSql != null && !conditionSql.equals("")){
				sqlBu.append(Symbol.SPACE);
				sqlBu.append(Connector.AND);
				sqlBu.append(Symbol.SPACE);
				sqlBu.append(conditionSql);
			}
		}
		//获取 connect by prior
		sqlBu.append(getConnectBy());
		if(conditionSql != null && !conditionSql.equals("")){
			//[普通条件 - f_oid/t_oid条件]
			conditionSql = conditionSql.replaceAll(".F_OID", ".f_oid");
			conditionSql = conditionSql.replaceAll(".T_OID", ".t_oid");
			String tableName = OmdHelper.getLTTableName(queryTemplate.getLinkType());
			if(this.queryTemplate.getDirection().equals(QTConstants.DIRECTION_POSITIVE)){
				if(conditionSql.matches(".+.f_oid = '[a-zA-Z0-9-]+'.+")){
					sqlBu.append(Symbol.SPACE);
					sqlBu.append(Connector.AND);
					sqlBu.append(Symbol.SPACE);
					sqlBu.append(conditionSql.replaceAll(tableName + ".f_oid = '[a-zA-Z0-9-]+'", "1 = 1"));
				}else if(conditionSql.matches(".+.f_oid in \\(['[a-zA-Z0-9-]+',* *]+\\).+")){
					sqlBu.append(Symbol.SPACE);
					sqlBu.append(Connector.AND);
					sqlBu.append(Symbol.SPACE);
					sqlBu.append(conditionSql.replaceAll(tableName + ".f_oid in \\(['[a-zA-Z0-9-]+',* *]+\\)", "1 = 1"));
				}else if(!conditionSql.contains(".f_oid")){
					sqlBu.append(Symbol.SPACE);
					sqlBu.append(Connector.AND);
					sqlBu.append(Symbol.SPACE);
					sqlBu.append(conditionSql);
				}
			}else{
				if(conditionSql.matches(".+.t_oid = '[a-zA-Z0-9-]+'.+")){
					sqlBu.append(Symbol.SPACE);
					sqlBu.append(Connector.AND);
					sqlBu.append(Symbol.SPACE);
					sqlBu.append(conditionSql.replaceAll(tableName + ".t_oid = '[a-zA-Z0-9-]+'", "1 = 1"));
				}else if(conditionSql.matches(".+.t_oid in \\(['[a-zA-Z0-9-]+',* *]+\\).+")){
					sqlBu.append(Symbol.SPACE);
					sqlBu.append(Connector.AND);
					sqlBu.append(Symbol.SPACE);
					sqlBu.append(conditionSql.replaceAll(tableName + ".t_oid in \\(['[a-zA-Z0-9-]+',* *]+\\)", "1 = 1"));
				}else if(!conditionSql.contains(".t_oid")){
					sqlBu.append(Symbol.SPACE);
					sqlBu.append(Connector.AND);
					sqlBu.append(Symbol.SPACE);
					sqlBu.append(conditionSql);
				}
			}
		}
		//加上level <= level + 1
		String connLevel = getConnectByLevelConstraint();
		if(!connLevel.equals("")){
			sqlBu.append(Symbol.SPACE);
			sqlBu.append(Connector.AND);
			sqlBu.append(Symbol.SPACE);
			sqlBu.append(connLevel);
		}
		sqlBu.append(getOrderSQL(queryTemplate.getOrderInfoList()));
		PageInfo pageInfo = queryTemplate.getPageInfo();
		if(pageInfo != null){
			String sql = page(sqlBu.toString(), queryTemplate.getPageInfo());
			return sql;
		}else {
			return sqlBu.toString();
		}
	}
	
	/**
	 * 获取链接查询模板的只查询业务对象的sql
	 * @return
	 * @throws Exception 
	 */
	public String getBtmQuerySql() throws Exception{
		StringBuilder sqlBu = new StringBuilder("");
		sqlBu.append(Symbol.SELECT);
		sqlBu.append(Symbol.SPACE);
		sqlBu.append(getClauses());
		sqlBu.append(Symbol.SPACE);
		sqlBu.append(Symbol.FROM);
		sqlBu.append(Symbol.SPACE);
		sqlBu.append(OmdHelper.getBTTableName(this.queryTemplate.getBtmType()));
		sqlBu.append(Symbol.SPACE);
		sqlBu.append(Symbol.WHERE);
		sqlBu.append(Symbol.SPACE);
		sqlBu.append("1 = 1");
		String conditionSql = null;
		Condition condi = queryTemplate.getCondition();
		if(condi != null && condi.getCIMap().size() > 0){
			//conditionSql = queryTemplate.getCondition().getBtmConditionSql(queryTemplate, addGrandRightFlag);
			conditionSql = QTSqlUtil.getBtmConditionSql(queryTemplate, addGrandRightFlag);
			if(conditionSql != null && !conditionSql.equals("")){
				sqlBu.append(Symbol.SPACE);
				sqlBu.append(Connector.AND);
				sqlBu.append(Symbol.SPACE);
				sqlBu.append(conditionSql);
			}
		}
		//加上版本条件
		sqlBu.append(getRevConstraint());
		//加上版次条件
		sqlBu.append(getVerConstraint());
		
		return sqlBu.toString();
	}
	
	/**
	 * 获取查询列
	 * 到查询列中包含*时, 覆盖其他查询列
	 * @return
	 * @throws Exception 
	 */
	private String getClauses()  {
		String countOverAlias = QTConstants.COUNT_OVER_ALIAS;
		StringBuilder clausesBu = new StringBuilder("");

		List<String> clauseList = queryTemplate.getClauseList();
		for (int i = 0; i < clauseList.size(); i++) {
			String val = clauseList.get(i);
			// add by xchao 2017.09.04 BEGIN
			// 如果查询的列名是查询总数的特殊标记，则使用特殊的查询查总数
			// add by xchao 2017.09.04 END
			if (countOverAlias.equals(val)) {
				val = QTConstants.COUNT_OVER_QUERY;
			}
			clausesBu.append(val);
			if (i < clauseList.size() - 1) {
				clausesBu.append(",");
			}
		}
		// 参照排序
		List<OrderInfo> orderinfos = queryTemplate.getOrderInfoList();
		if (orderinfos != null) {
			for (int i = 0; i < orderinfos.size(); i++) {
				OrderInfo orderinfo = orderinfos.get(i);
				String orderfield = orderinfo.getOrderField();
				if (orderfield.contains(".")) {
					String[] fields = orderfield.split("\\.");
					String refAbName = fields[0];
					//AttribItem refAb = ServerServiceProvider.getOMDService().getAttributeService().getAttribItemByName(refAbName);
					AttribItem refAb = OMCacheProvider.getAttribute(refAbName);
					OtherInfo otherInfo = OtherInfo.getOtherInfoByText(refAb.other);
					int refFlag = otherInfo.getRefFlag();
					String type = otherInfo.getRefTypeName();

					clausesBu.append(",platformbtm_");
					String asfield = fields[0] + "mes" + fields[1];
					if (asfield.length() > 30) {
						asfield = asfield.substring(0, 30);
					}
					clausesBu.append(type + "." + fields[1] + " as " + asfield);
				}
			}
		}
		return clausesBu.toString();
	}
	
	
	/**
	 * 获取版本约束
	 * @return
	 */
	public String getRevConstraint(){
		int version = queryTemplate.getVersion();
		StringBuilder strb = new StringBuilder("");
		switch(version){
			case Version.allVer:
			case Version.currentRevCurrentVer:
			case Version.currentRevLastVer:
			case Version.currentVer:
			case Version.currentRev:
			case Version.currentName:
				break;
			case Version.lastRevLastVer:
				strb.append(Symbol.SPACE);
				strb.append(Connector.AND);
				strb.append(Symbol.SPACE);
				strb.append("ISLastR");
				strb.append(Symbol.SPACE);
				strb.append(Operator.EQUAL);
				strb.append(Symbol.SPACE);
				strb.append("'1'");
				break;
			default:
				break;
				
		}
		return strb.toString();
	}
	
	/**
	 * 获取版次约束
	 * @return
	 */
	public String getVerConstraint(){
		int version = queryTemplate.getVersion();
		StringBuilder strb = new StringBuilder("");
		switch(version){
		case Version.allVer:
		case Version.currentRevCurrentVer:
		case Version.currentVer:
		case Version.currentRev:
		case Version.currentName:
			break;
		case Version.currentRevLastVer:
		case Version.lastRevLastVer:
			strb.append(Symbol.SPACE);
			strb.append(Connector.AND);
			strb.append(Symbol.SPACE);
			strb.append("ISLastV");
			strb.append(Symbol.SPACE);
			strb.append(Operator.EQUAL);
			strb.append(Symbol.SPACE);
			strb.append("'1'");
			break;
		default:
			break;
			
	}
		return strb.toString();
	}
	
	/**
	 * 获取递归层次约束
	 * @return
	 */
	private String getLevelConstraint(){
		int level = this.queryTemplate.getLevel();
		if(level < 0){
			return "";
		}
		StringBuilder stb = new StringBuilder();
		stb.append(Symbol.LEVEL);
		stb.append(Symbol.SPACE);
		stb.append(Operator.LTE);
		stb.append(Symbol.SPACE);
		stb.append(level);
		return stb.toString();
	}
	
	/**
	 * 加强连接过滤条件, 提高效率
	 * connect by 递归层次约束
	 * level <= level + 1
	 * +1: 保证在表中是否是叶子节点的正确性
	 * @return
	 */
	private String getConnectByLevelConstraint(){
		int level = this.queryTemplate.getLevel();
		if(level < 0){
			return "";
		}
		StringBuilder stb = new StringBuilder();
		stb.append(Symbol.LEVEL);
		stb.append(Symbol.SPACE);
		stb.append(Operator.LTE);
		stb.append(Symbol.SPACE);
		stb.append(level + 1);
		return stb.toString();
	}
	/**
	 * 根据版本版次信息， 选择oid, revisionOId, nameOId做连接，具体为：
	 *	正向查询时：connect by prior viewName.oid = f_OId；
	 * 	反向查询时：connect by prior viewName.oid = t_OId；
	 * @return
	 */
	private String getConnectBy(){
		String direction = this.queryTemplate.getDirection();
		StringBuilder stb = new StringBuilder();
		stb.append(Symbol.SPACE);
		stb.append(Symbol.CONNECT);
		stb.append(Symbol.SPACE);
		stb.append(Symbol.BY);
		stb.append(Symbol.SPACE);
		stb.append(Symbol.NOCYCLE);
		stb.append(Symbol.SPACE);
		stb.append(Symbol.PRIOR);
		stb.append(Symbol.SPACE);
		String viewName = null;
		String linkType = this.queryTemplate.getLinkType();
		String btmType = queryTemplate.getBtmType();
		if (btmType != null && btmType.equals("*")) {
			if(queryTemplate.getDirection().equals(QTConstants.DIRECTION_POSITIVE)){
				viewName = OmdHelper.getToViewName(linkType);
			}else{
				viewName = OmdHelper.getFromViewName(linkType);
			}
		} else if (isSingleBtm(btmType)) {
			viewName = OmdHelper.getBTTableName(btmType);
		}
		
		if(direction.equals(QTConstants.DIRECTION_POSITIVE)){
			if(btmType != null && !btmType.equals("")){
				stb.append(viewName + ".OID = F_OID");
			}else{
				stb.append("T_OID = F_OID");
			}
			
		}else{
			if(btmType != null && !btmType.equals("")){
				stb.append(viewName + ".OID = T_OID");
			}else{
				stb.append("F_OID = T_OID");
			}
		}
		return stb.toString();
	}
	/**
	 * 处理查询类型
	 * @return
	 * @throws Exception 
	 */
	public String getQueryType() {
		String type = queryTemplate.getType();
		int version = queryTemplate.getVersion();
		String linkType = queryTemplate.getLinkType();
		String btmType = queryTemplate.getBtmType();
		StringBuilder strb = new StringBuilder("");
		//业务类型查询
		if(type.equals(QTConstants.TYPE_BTM)){
			String btmTable = OmdHelper.getBTTableName(btmType);
			strb.append(btmTable);
			//参照排序
			List<OrderInfo>  orderinfos=queryTemplate.getOrderInfoList();
			if (orderinfos != null) {
				for (int i = 0; i < orderinfos.size(); i++) {
					OrderInfo orderinfo = orderinfos.get(i);
					String orderfield = orderinfo.getOrderField();
					if (!orderfield.contains(".")) 
						continue;
						
					String[] fields = orderfield.split("\\.");
					String refAbName = fields[0];
					AttribItem refAb;
					//refAb = ServerServiceProvider.getOMDService().getAttributeService().getAttribItemByName(refAbName);
					refAb = OMCacheProvider.getAttribute(refAbName);
					OtherInfo otherInfo = OtherInfo.getOtherInfoByText(refAb.other);
					int refFlag = otherInfo.getRefFlag();
					String type2 = otherInfo.getRefTypeName();

					String strjoin = " join platformbtm_" + type2 + " on " + btmTable + "." + fields[0]
							+ " =  platformbtm_" + type2 + ".oid ";
					if (!strb.toString().contains(strjoin)) {
						strb.append(strjoin);
					}
				}
			}
			if(version == Version.lastReleasedRev){
				strb.append(Symbol.SPACE);
				strb.append(Symbol.JOIN);
				strb.append(Symbol.SPACE);
				strb.append(QTConstants.PLRELEASEDOBJ);
				strb.append(Symbol.SPACE);
				strb.append(Symbol.ON);
				strb.append(Symbol.SPACE);
				strb.append(btmTable + ".OID");
				strb.append(Symbol.SPACE);
				strb.append(Operator.EQUAL);
				strb.append(Symbol.SPACE);
				strb.append(QTConstants.PLRELEASEDOBJ + ".OID");
			}
		//链接查询, 只查链接对象
		}else if(type.equals(QTConstants.TYPE_LINK) && (btmType == null || btmType.equals(""))){
			String ltTable = OmdHelper.getLTTableName(linkType);
			strb.append(ltTable);
		//链接查询, 既查链接对象又查链接的业务对象, 根据版本版次信息链接这两个表
		}else{
			//正向查询
			boolean positiveFlag = true;
			if(queryTemplate.getDirection().equals(QTConstants.DIRECTION_OPPOSITE)){
				positiveFlag = false;
			}
			String ltTable = OmdHelper.getLTTableName(linkType);
			String viewName = null;
			if (isSingleBtm(btmType)) {
				viewName = OmdHelper.getBTTableName(btmType);
			} else {
				if(positiveFlag){
					viewName = OmdHelper.getToViewName(linkType);
				}else{
					viewName = OmdHelper.getFromViewName(linkType);
				}
			}
			
			//当查询的业务类型不是全部（*）时，连接加上过滤条件
			//形如：linkTable join viewName on ... 
			//and viewName.btmName in ('btmName1', 'btmName2')
			String btmCons_ = null;
			StringBuilder btmCons = new StringBuilder("in (");
			if(!btmType.equals("*")){
				String[] btmNames = btmType.split(",");
				for(String btmName : btmNames){
					btmCons.append("'" + btmName + "'");
					btmCons.append(",");
				}
				btmCons_ = btmCons.substring(0, btmCons.lastIndexOf(",")) + ")";
			}
			switch(version){
				case Version.currentRevCurrentVer:
					strb.append(ltTable);
					strb.append(Symbol.SPACE);
					strb.append(Symbol.JOIN);
					strb.append(Symbol.SPACE);
					strb.append(viewName);
					strb.append(Symbol.SPACE);
					strb.append(Symbol.ON);
					strb.append(Symbol.SPACE);
					if(positiveFlag){
						strb.append(ltTable + ".t_oid");
					}else{
						strb.append(ltTable + ".f_oid");
					}
					strb.append(Symbol.SPACE);
					strb.append(Operator.EQUAL);
					strb.append(Symbol.SPACE);
					strb.append(viewName + ".oid");
					if(!isSingleBtm(btmType) && !btmType.equals("*")){
						strb.append(Symbol.SPACE);
						strb.append(Connector.AND);
						strb.append(Symbol.SPACE);
						strb.append(viewName + ".btmName");
						strb.append(Symbol.SPACE);
						strb.append(btmCons_);
					}
					break;
				case Version.currentRevLastVer:
					strb.append(ltTable);
					strb.append(Symbol.SPACE);
					strb.append(Symbol.JOIN);
					strb.append(Symbol.SPACE);
					strb.append(viewName);
					strb.append(Symbol.SPACE);
					strb.append(Symbol.ON);
					strb.append(Symbol.SPACE);
					if(positiveFlag){
						strb.append(ltTable + ".t_revisionoid");
					}else{
						strb.append(ltTable + ".f_revisionoid");
					}
					strb.append(Symbol.SPACE);
					strb.append(Operator.EQUAL);
					strb.append(Symbol.SPACE);
					strb.append(viewName + ".revisionoid");
					strb.append(Symbol.SPACE);
					strb.append(Connector.AND);
					strb.append(Symbol.SPACE);
					strb.append(viewName + ".ISLASTV = 1");
					if(!isSingleBtm(btmType) && !btmType.equals("*")){
						strb.append(Symbol.SPACE);
						strb.append(Connector.AND);
						strb.append(Symbol.SPACE);
						strb.append(viewName + ".btmName");
						strb.append(Symbol.SPACE);
						strb.append(btmCons_);
					}
					break;
				case Version.allVer:
				case Version.lastRevLastVer:
					strb.append(ltTable);
					strb.append(Symbol.SPACE);
					strb.append(Symbol.JOIN);
					strb.append(Symbol.SPACE);
					strb.append(viewName);
					strb.append(Symbol.SPACE);
					strb.append(Symbol.ON);
					strb.append(Symbol.SPACE);
					if(positiveFlag){
						strb.append(ltTable + ".t_nameoid");
					}else{
						strb.append(ltTable + ".f_nameoid");
					}
					strb.append(Symbol.SPACE);
					strb.append(Operator.EQUAL);
					strb.append(Symbol.SPACE);
					strb.append(viewName + ".nameoid");
					strb.append(Symbol.SPACE);
					strb.append(Connector.AND);
					strb.append(Symbol.SPACE);
					strb.append(viewName + ".ISLASTV = 1");
					strb.append(Symbol.SPACE);
					strb.append(Connector.AND);
					strb.append(Symbol.SPACE);
					strb.append(viewName + ".ISLASTR = 1");
					if(!isSingleBtm(btmType) && !btmType.equals("*")){
						strb.append(Symbol.SPACE);
						strb.append(Connector.AND);
						strb.append(Symbol.SPACE);
						strb.append(viewName + ".btmName");
						strb.append(Symbol.SPACE);
						strb.append(btmCons_);
					}
					break;
				case Version.currentVer:
					strb.append(ltTable);
					strb.append(Symbol.SPACE);
					strb.append(Symbol.JOIN);
					strb.append(Symbol.SPACE);
					strb.append(viewName);
					strb.append(Symbol.SPACE);
					strb.append(Symbol.ON);
					strb.append(Symbol.SPACE);
					strb.append(ltTable + ".f_oid");
					strb.append(Symbol.SPACE);
					strb.append(Operator.EQUAL);
					strb.append(Symbol.SPACE);
					strb.append(viewName + ".oid");
					if(!isSingleBtm(btmType) && !btmType.equals("*")){
						strb.append(Symbol.SPACE);
						strb.append(Connector.AND);
						strb.append(Symbol.SPACE);
						strb.append(viewName + ".btmName");
						strb.append(Symbol.SPACE);
						strb.append(btmCons_);
					}
					break;
				case Version.currentRev:
					strb.append(ltTable);
					strb.append(Symbol.SPACE);
					strb.append(Symbol.JOIN);
					strb.append(Symbol.SPACE);
					strb.append(viewName);
					strb.append(Symbol.SPACE);
					strb.append(Symbol.ON);
					strb.append(Symbol.SPACE);
					strb.append(ltTable + ".f_oid");
					strb.append(Symbol.SPACE);
					strb.append(Operator.EQUAL);
					strb.append(Symbol.SPACE);
					strb.append(viewName + ".oid");
					if(!isSingleBtm(btmType) && !btmType.equals("*")){
						strb.append(Symbol.SPACE);
						strb.append(Connector.AND);
						strb.append(Symbol.SPACE);
						strb.append(viewName + ".btmName");
						strb.append(Symbol.SPACE);
						strb.append(btmCons_);
					}
					break;
				case Version.currentName:
					strb.append(ltTable);
					strb.append(Symbol.SPACE);
					strb.append(Symbol.JOIN);
					strb.append(Symbol.SPACE);
					strb.append(viewName);
					strb.append(Symbol.SPACE);
					strb.append(Symbol.ON);
					strb.append(Symbol.SPACE);
					strb.append(ltTable + ".f_oid");
					strb.append(Symbol.SPACE);
					strb.append(Operator.EQUAL);
					strb.append(Symbol.SPACE);
					strb.append(viewName + ".oid");
					if(!isSingleBtm(btmType) && !btmType.equals("*")){
						strb.append(Symbol.SPACE);
						strb.append(Connector.AND);
						strb.append(Symbol.SPACE);
						strb.append(viewName + ".btmName");
						strb.append(Symbol.SPACE);
						strb.append(btmCons_);
					}
					break;
				case Version.lastReleasedRev:
					strb.append(ltTable);
					strb.append(Symbol.SPACE);
					strb.append(Symbol.JOIN);
					strb.append(Symbol.SPACE);
					strb.append(QTConstants.PLRELEASEDOBJ);
					strb.append(Symbol.SPACE);
					strb.append(Symbol.ON);
					strb.append(Symbol.SPACE);
					if(positiveFlag){
						strb.append(ltTable + ".t_nameoid");
					}else{
						strb.append(ltTable + ".f_nameoid");
					}
					strb.append(Symbol.SPACE);
					strb.append(Operator.EQUAL);
					strb.append(Symbol.SPACE);
					strb.append(QTConstants.PLRELEASEDOBJ + ".nameoid");
					strb.append(Symbol.SPACE);
					strb.append(Symbol.JOIN);
					strb.append(Symbol.SPACE);
					strb.append(viewName);
					strb.append(Symbol.SPACE);
					strb.append(Symbol.ON);
					strb.append(Symbol.SPACE);
					strb.append(QTConstants.PLRELEASEDOBJ + ".OID");
					strb.append(Symbol.SPACE);
					strb.append(Operator.EQUAL);
					strb.append(Symbol.SPACE);
					strb.append(viewName + ".OID");
					if(!isSingleBtm(btmType) && !btmType.equals("*")){
						strb.append(Symbol.SPACE);
						strb.append(Connector.AND);
						strb.append(Symbol.SPACE);
						strb.append(viewName + ".btmName");
						strb.append(Symbol.SPACE);
						strb.append(btmCons_);
					}
					break;
				default:
					break;
			}
		}
		return strb.toString();
	}
	
	/**
	 * 将SQL包装成分页SQL
	 * select * from (select A.*, rownum RN from ( sql ) A where rownum < endRow ) where RN >= startRow
	 * @param sql
	 * @param pageInfo
	 * @return
	 */
	private String page(String sql, PageInfo pageInfo){
		int startRow = (pageInfo.getPageNO() - 1) * pageInfo.getRowCount() + 1;
		int endRow = startRow + pageInfo.getRowCount();
		StringBuilder strb = new StringBuilder();
		strb.append("select * from (select A.* , rownum RN from (");
		strb.append(sql);
		strb.append(") A where rownum < ");
		strb.append(endRow);
		strb.append(") where RN >= ");
		strb.append(startRow);
		
		return strb.toString();
	}
	
	/**
	 * 获取授权SQL
	 * : 权限
	 * @param btmType
	 * @return
	 * @throws Exception 
	 * @throws Throwable 
	 */
	public String getGrandRightSQL() {
		String where = "";
		CheckValue value = getCheckValue();
		try {
			//where = ServerServiceProvider.getAuth2Service().checkRight(value);
			where = new SecretUtil().checkRight(value);
		} catch (VCIError e) {
			e.printStackTrace();
		}
		return where;
	}
	
	public CheckValue getCheckValue(){
		CheckValue value = new CheckValue();
		value.businesstype = queryTemplate.getBtmType();
		value.opname = "query";

		String users = "";
		String[] roleNames = new String[0];
		String[] groupNames = new String[0];
		String[] extAttribs = new String[0];
		ArrayList<String> arExtAttr = new ArrayList<String>();

		VCIInvocationInfo vcii = HibernateSessionFactory.getVciSessionInfo();
		if (vcii != null) {
			users = vcii.userName;
			roleNames = vcii.roleNames;
			groupNames = vcii.groupNames;
			extAttribs = vcii.extAttribs;
			
			
			arExtAttr.add("CURRENTUSER.ID=" + vcii.userID);
			arExtAttr.add("CURRENTUSER.NAME=" + vcii.userName);
			arExtAttr.add("CURRENTUSER.SECRETGRADE=" + vcii.secretGrade);
			if (vcii.groupNames != null && vcii.groupNames.length > 0)
				arExtAttr.add("CURRENTUSER.GROUPNUM=" + vcii.groupNames[0]);
		}
		
		if (extAttribs != null)
			Collections.addAll(arExtAttr, extAttribs);
//		extAttribs = arExtAttr.toArray(new String[0]);

		if (users != null)
			value.users = users;
		else
			value.users = "";
		
		if (roleNames != null)
			value.roles = String.join(",", roleNames);
		else
			value.roles = "";
		
		if(groupNames != null && groupNames.length > 0){
			value.userGroups = groupNames[0];
		}
		value.paramValues = String.join(",", arExtAttr);
		return value;
	}
	
//	private String getStringFormArray(Collection<String> array){
//		if (array == null)
//			return "";
//		
//		return String.join(",", array);
//		
////		StringBuilder sb = new StringBuilder();
////		for (int i = 0; i < array.size(); i++) {
////			sb.append(array.);
////			if(i != array.length - 1){
////				sb.append(",");
////			}
////		}
////		return sb.toString();
//	}
	
	/**
	 * 获取排序条件
	 * @param orderInfoList
	 * @return
	 */
	private Object getOrderSQL(List<OrderInfo> orderInfoList) {
		StringBuilder strb = new StringBuilder();
		if(orderInfoList == null || orderInfoList.size() < 1){
			return strb.toString();
		}
		//根据orderInfo.level降排序
		Collections.sort(orderInfoList, new ComparatorOrderInfo());
		String linkType = queryTemplate.getLinkType();
		String btmType = queryTemplate.getBtmType();
		String viewName = null;
		if (isSingleBtm(btmType)) {
			viewName = OmdHelper.getBTTableName(btmType);
		} else {
			if(queryTemplate.getDirection().equals(QTConstants.DIRECTION_POSITIVE)){
				viewName = OmdHelper.getToViewName(linkType);
			}else{
				viewName = OmdHelper.getFromViewName(linkType);
			}
		}
		
		for(int i = 0; i < orderInfoList.size(); i++){
			OrderInfo orderInfo = orderInfoList.get(i);
			if(orderInfo == null){
				continue;
			}
			strb.append(" ");
			String field = orderInfo.getOrderField();
			
			//参照排序
			if(field.contains(".")&&!(field.startsWith("T_OID.") || field.startsWith("F_OID."))){
				field=field.replace(".", "mes");
				if(field.length()>30){
					field=field.substring(0,30);
				}
			}
			
			
			if(queryTemplate.getType().equals(QTConstants.TYPE_LINK)){
				if(field.startsWith("T_OID.") || field.startsWith("F_OID.")){
					field = field.substring(6);
					field = viewName + "." + field;
				}
			}
			strb.append(field);
			strb.append(" ");
			strb.append(orderInfo.getOrderMode());
			strb.append(",");
		}
		//当strb不等于"" 时, 为其加上前缀ORDER BY
		String str = strb.toString();
		if(!str.equals("")){
			str = str.substring(0, str.length() - 1);
			return " order by" + str;
		}
		return str;
	}

//	private static boolean isClientIvoke(){
//		return (HibernateSessionFactory.getVciSessionInfo() == null);
//	}
	
	public QueryTemplate getQueryTemplate() {
		return queryTemplate;
	}

	public void setQueryTemplate(QueryTemplate queryTemplate) {
		this.queryTemplate = queryTemplate;
	}
	
	/**
	 * 是否只有一个业务类型
	 * @param btmType
	 * @return
	 */
	public boolean isSingleBtm(String btmType){
		if(btmType != null && !btmType.equals("*") && !btmType.contains(",")){
			return true;
		}
		return false;
	}
}