package com.vci.server.query.util;

import java.util.Map;

import org.apache.commons.lang3.StringUtils;

import com.vci.common.qt.object.Condition;
import com.vci.common.qt.object.ConditionItem;
import com.vci.common.qt.object.Connector;
import com.vci.common.qt.object.LeafInfo;
import com.vci.common.qt.object.LeafValue;
import com.vci.common.qt.object.Operator;
import com.vci.common.qt.object.QTConstants;
import com.vci.common.qt.object.QueryTemplate;
import com.vci.common.qt.object.Symbol;
import com.vci.server.base.utility.AttributeHelper;
import com.vci.server.base.utility.OmdHelper;

public final class QTSqlUtil {
	

	/**
	 * 将查询条件解析成sql
	 * @param qt
	 * @param addGrandRightFlag 是否加上权限的条件, 默认应为true
	 * 	当权限开关开启(right.switch=on), 并且addGrandRightFlag=true时才会加权限条件
	 * @return
	 */
	public static String getSql(QueryTemplate qt, boolean addGrandRightFlag){
		StringBuilder strb = new StringBuilder("");
		
		Condition condition = qt.getCondition();

		Map<String, ConditionItem> ciMap = condition.getCIMap();
		ConditionItem rootCI = ciMap.get(condition.getRootCIName());
		if(rootCI.isLeaf()){
			strb.append(getConditionItemSql(qt, rootCI, addGrandRightFlag));
		}else{
			ConditionItem root = rootCI;
			ConditionItem left = ciMap.get(root.getChildrenInfo().getLeftCIName());
			ConditionItem right = ciMap.get(root.getChildrenInfo().getRightCIName());
			String treeSql = parseTree(ciMap, left, root, right, qt, addGrandRightFlag);
			strb.append(treeSql);
		}
		
		return strb.toString();
	}
	
	/**
	 * 将查询条件中的link条件解析成sql
	 * @param qt
	 * @return
	 */
	public static String getLinkConditionSql(QueryTemplate qt, boolean addGrandRightFlag){
		StringBuilder stb = new StringBuilder("");
		
		Condition condition = qt.getCondition();
		
		Map<String, ConditionItem> ciMap = condition.getCIMap();

		ConditionItem rootCI = ciMap.get(condition.getRootCIName());
		if(rootCI.isLeaf()){
			if(!isBtmContidionItem(ciMap, rootCI)){
				return getConditionItemSql(qt, rootCI, addGrandRightFlag);
			}else{
				return stb.toString();
			}
		}else{
			ConditionItem root = rootCI;
			ConditionItem left = ciMap.get(root.getChildrenInfo().getLeftCIName());
			ConditionItem right = ciMap.get(root.getChildrenInfo().getRightCIName());
			if(!isBtmContidionItem(ciMap, left) && !isBtmContidionItem(ciMap, right)){
				String treeSql = parseTree(ciMap, left, root, right, qt, addGrandRightFlag);
				stb.append(treeSql);
			}else if(!isBtmContidionItem(ciMap, left)){
				String rootCIName = condition.getRootCIName();
				condition.setRootCIName(left.getId());
				String sql = getSql(qt, addGrandRightFlag);
				condition.setRootCIName(rootCIName);
				return sql;
			}else if(!isBtmContidionItem(ciMap, right)){
				String rootCIName = condition.getRootCIName();
				condition.setRootCIName(right.getId());
				String sql = getSql(qt, addGrandRightFlag);
				condition.setRootCIName(rootCIName);
				return sql;
			}else{
				return stb.toString();
			}
		}
		
		return stb.toString();
	}
	
	/**
	 * 将查询条件中的btm条件解析成sql
	 * @param qt
	 * @return
	 */
	public static String getBtmConditionSql(QueryTemplate qt, boolean addGrandRightFlag){
		Condition condition = qt.getCondition();
		Map<String, ConditionItem> ciMap = condition.getCIMap();
		
		StringBuilder stb = new StringBuilder("");
		ConditionItem rootCI = ciMap.get(condition.getRootCIName());
		if(rootCI.isLeaf()){
			if(isBtmContidionItem(ciMap, rootCI)){
				return getConditionItemSql(qt, rootCI, addGrandRightFlag);
			}else{
				return stb.toString();
			}
		}else{
			ConditionItem root = rootCI;
			ConditionItem left = ciMap.get(root.getChildrenInfo().getLeftCIName());
			ConditionItem right = ciMap.get(root.getChildrenInfo().getRightCIName());
			if(isBtmContidionItem(ciMap, left) && isBtmContidionItem(ciMap, right)){
				String treeSql = parseTree(ciMap, left, root, right, qt, addGrandRightFlag);
				stb.append(treeSql);
			}else if(isBtmContidionItem(ciMap, left)){
				String rootCIName = condition.getRootCIName();
				condition.setRootCIName(left.getId());
				String sql = getSql(qt, addGrandRightFlag);
				condition.setRootCIName(rootCIName);
				return sql;
			}else if(isBtmContidionItem(ciMap, right)){
				String rootCIName = condition.getRootCIName();
				condition.setRootCIName(right.getId());
				String sql = getSql(qt, addGrandRightFlag);
				condition.setRootCIName(rootCIName);
				return sql;
			}else{
				return stb.toString();
			}
		}
		
		return stb.toString();
	}
	
	/**
	 * 递归解析ConditionItem树结构
	 * @param left
	 * @param root
	 * @param right
	 * @return
	 */
	private static String parseTree(Map<String, ConditionItem> ciMap, ConditionItem left, ConditionItem root, ConditionItem right, QueryTemplate qt, boolean addGrandRightFlag){
		StringBuilder strb = new StringBuilder("");
		strb.append(Symbol.LEFT_PAREN);
		if(left.isLeaf()){
			strb.append(getConditionItemSql(qt, left, addGrandRightFlag));
		}else{
			ConditionItem left_ = ciMap.get(left.getChildrenInfo().getLeftCIName());
			ConditionItem root_ = left;
			ConditionItem right_ = ciMap.get(left.getChildrenInfo().getRightCIName());
			String treeSql = parseTree(ciMap, left_, root_, right_, qt, addGrandRightFlag);
			strb.append(treeSql);
		}
		strb.append(Symbol.SPACE);
		String connector = root.getChildrenInfo().getConnector();
		if(connector.equals(Connector.ANDCN)){
			connector = Connector.AND;
		}
		if(connector.equals(Connector.ORCN)){
			connector = Connector.OR;
		}
		strb.append(connector);
		strb.append(Symbol.SPACE);
		if(right.isLeaf()){
			strb.append(getConditionItemSql(qt, right, addGrandRightFlag));
		}else{
			ConditionItem left_ = ciMap.get(right.getChildrenInfo().getLeftCIName());
			ConditionItem root_ = right;
			ConditionItem right_ = ciMap.get(right.getChildrenInfo().getRightCIName());
			String treeSql = parseTree(ciMap, left_, root_, right_, qt, addGrandRightFlag);
			strb.append(treeSql);
		}
		strb.append(Symbol.RIGHT_PAREN);
		return strb.toString();
	}
	
	/**
	 * 在链接查询的查询条件中
	 * 判断查询条件项是否是约束业务对象的
	 * @return
	 */
	private static boolean isBtmContidionItem(Map<String, ConditionItem> ciMap, ConditionItem ci){
		while(!ci.isLeaf()){
			ci = ciMap.get(ci.getChildrenInfo().getRightCIName());
		}
		String clause = ci.getLeafInfo().getClause();
		if(clause.startsWith("T_OID.") || clause.startsWith("F_OID.")){
			return true;
		}
		return false;
	}
	
	
	
	/**
	 * 将叶子节点解析为sql
	 * @return
	 */
	private static String getConditionItemSql(QueryTemplate qt, ConditionItem ci, boolean addGrandRightFlag){
		try{
			StringBuilder strb = new StringBuilder("");
			strb.append(getLeafSql(qt, ci.getLeafInfo(),addGrandRightFlag));
			return strb.toString();
		}catch(Exception e){
			e.printStackTrace();
		}
		return "";
		
	}
	
	/**
	 * 叶子节点sql
	 */
	private static String getLeafSql(QueryTemplate qt, LeafInfo leaf, boolean addGrandRightFlag){
		StringBuilder strb = new StringBuilder("");
		String clause = leaf.getClause();
		if(qt != null && qt.getType().equals(QTConstants.TYPE_LINK)){
			String btmType = qt.getBtmType();
			if(btmType != null && !btmType.equals("")){
				String linkType = qt.getLinkType();
				String viewName = null;
				if (btmType.equals("*")) {
					if(qt.getDirection().equals(QTConstants.DIRECTION_POSITIVE)){
						viewName = OmdHelper.getToViewName(linkType);
					}else{
						viewName = OmdHelper.getFromViewName(linkType);
					}
				} else {
					viewName = OmdHelper.getBTTableName(btmType);
				}
				
				if(clause.toUpperCase().contains("T_OID.") || clause.toUpperCase().contains("F_OID.")){
					clause = clause.substring(6);
					strb.append(viewName + "." + clause);
				}else{
					String tableName = OmdHelper.getLTTableName(linkType);
					strb.append(tableName + "." + clause);
				}
			}else{
				strb.append(clause);
			}
		}else{
			if(clause.toUpperCase().contains("T_OID.")){
				clause = clause.substring(6);
				strb.append("PLRELEASEDOBJ" + "." + clause);
			}else{
				//addGrandRightFlag为false
				//表示此解析为授权时要保存sql片段(where之后), 因链接类型也走此逻辑, 故不能加表名. 
//				if(!addGrandRightFlag){
//					strb.append(clause);
//				}else{
					String btmType = qt.getBtmType();
					strb.append(OmdHelper.getBTTableName(btmType) + "." + clause);
				//}
			}
		}
		
		String dataType = AttributeHelper.getAbItemDataType(clause);
		strb.append(Symbol.SPACE);
		String operator = leaf.getOperator();
		if(operator.equals(Operator.CONTAINS)){
			operator = Operator.LIKE;
		}
		strb.append(operator);
		strb.append(Symbol.SPACE);
		LeafValue value = leaf.getValue();
		
		String valueSQL = value.getSql(addGrandRightFlag);
		
		if(!(operator.equals(Operator.IN) || operator.equals(Operator.NOTIN)) && valueSQL.contains("'")){
			//add by caill start 2016.3.25在拼凑sql语句时，对于查询模板中的查询条件中含有单引号的，在sql语句中要变成双引号
			valueSQL = valueSQL.replaceAll("\'", "\"");
			//valueSQL = valueSQL.replaceAll("'", "");
			//add by caill end
		}
		
		if(value.getQueryTemplate() == null){
			if ((operator.equals(Operator.IN) || operator.equals(Operator.NOTIN)) && !StringUtils.isEmpty(valueSQL)) {
				strb.append(valueSQL);
			} else if(dataType.equalsIgnoreCase("VTSTRING")){
				if(operator.equals(Operator.LIKE) || operator.equals(Operator.NOTLIKE)){
					if(valueSQL.equals("*")){
						strb.append("'%'");
					}else if(valueSQL.startsWith("*") && valueSQL.endsWith("*")){
						valueSQL = valueSQL.substring(1, valueSQL.length() - 1);
						strb.append("'%" + valueSQL + "%'");
					}else if(valueSQL.startsWith("*")){
						valueSQL = valueSQL.substring(1, valueSQL.length());
						strb.append("'%" + valueSQL + "'");
					}else if(valueSQL.endsWith("*")){
						valueSQL = valueSQL.substring(0, valueSQL.length() - 1);
						strb.append("'" + valueSQL + "%'");
					}else{
						strb.append("'%" + valueSQL + "%'");
					}
				} else {
					if (valueSQL.equals("") || valueSQL.equalsIgnoreCase("null")) {
						if (operator.equals(Operator.EQUAL)) {
							strb.deleteCharAt(strb.indexOf("="));
							strb.append("is null ");
						} else if (operator.equals(Operator.UNEQUAL)) {
							strb.delete(strb.indexOf("!="), strb.length());
							strb.append("is not null ");
						}
					//操作符为in时 不对value加''
					}else if(operator.equals(Operator.IN) || operator.equals(Operator.NOTIN)){
						strb.append(valueSQL);
					} else {
						strb.append("'" + valueSQL + "'");
					}
				}
			//boolean 在oracle中存为varchar2(8), 解析成bool = 'false'
			}else if(dataType.equalsIgnoreCase("VTBOOLEAN")){
				if(valueSQL.equalsIgnoreCase("false")){
					valueSQL = "false";
				}else if(valueSQL.equalsIgnoreCase("true")){
					valueSQL = "true";
				}
				strb.append("'" + valueSQL + "'");
			}else if(dataType.equalsIgnoreCase("VTDATETIME") 
					|| dataType.toUpperCase().equals("VTTIME")){
				if (valueSQL.equals("") || valueSQL.equalsIgnoreCase("null")) {
					if (operator.equals(Operator.EQUAL)) {
						strb.deleteCharAt(strb.indexOf("="));
						strb.append("is null ");
					} else if (operator.equals(Operator.UNEQUAL)) {
						strb.delete(strb.indexOf("!="), strb.length());
						strb.append("is not null ");
					}
				} else {
					valueSQL = "to_timestamp('" + valueSQL + "', 'syyyy-mm-dd hh24:mi:ss.ff')";
					strb.append(valueSQL);
				}
			}else if (dataType.equalsIgnoreCase("VTDATE")){
				if (valueSQL.equals("") || valueSQL.equalsIgnoreCase("null")) {
					if (operator.equals(Operator.EQUAL)) {
						strb.deleteCharAt(strb.indexOf("="));
						strb.append("is null ");
					} else if (operator.equals(Operator.UNEQUAL)) {
						strb.delete(strb.indexOf("!="), strb.length());
						strb.append("is not null ");
					}
				} else {
					valueSQL = "to_date('" + valueSQL + "', 'syyyy-mm-dd hh24:mi:ss')";
					strb.append(valueSQL);
				}
			}else{
				if(operator.equals(Operator.LIKE)){
					if(valueSQL.startsWith("*") && valueSQL.endsWith("*")){
						valueSQL = valueSQL.substring(1, valueSQL.length() - 1);
						strb.append("'%" + valueSQL + "%'");
					}else if(valueSQL.startsWith("*")){
						valueSQL = valueSQL.substring(1, valueSQL.length());
						strb.append("'%" + valueSQL + "'");
					}else if(valueSQL.endsWith("*")){
						valueSQL = valueSQL.substring(0, valueSQL.length() - 1);
						strb.append("'" + valueSQL + "%'");
					}else{
						strb.append("'%" + valueSQL + "%'");
					}
				}else{
					if(valueSQL.equals("") || valueSQL.equalsIgnoreCase("null")){
						if (operator.equals(Operator.EQUAL)) {
							strb.deleteCharAt(strb.indexOf("="));
							strb.append("is null ");
						} else if (operator.equals(Operator.UNEQUAL)) {
							strb.deleteCharAt(strb.indexOf("!="));
							strb.append("is not null ");
						}
					}else{
						strb.append("'" + valueSQL + "'");
					}
				}
			}
		}else{
			strb.append(valueSQL);
		}
		
		
		return strb.toString();
		
	}
}