package com.taover.repository;

import java.util.HashMap;
import java.util.Map;

import org.springframework.util.StringUtils;

public class UtilsSql {
	
	public static String[] splitCoreSql(String coreSql) throws Exception {		
		//去除''内的信息
		String coreSqlRemoveQuate = replaceSelectAndFromBetweenTopCornerMark(coreSql.toUpperCase());
		int fromIndex = calcFromIndex(coreSqlRemoveQuate);
		if(fromIndex > -1) {
			return new String[] {coreSql.substring(0, fromIndex), coreSql.substring(fromIndex, coreSql.length())};
		}else {
			throw new Exception("未找到FROM子句"); 
		}
	}
	
	private static int calcFromIndex(String coreSqlUpper) {
		//计算位置
		int selectSubSqlNum = 0;
		int currWindowIndex = 0;		
		for(currWindowIndex=0; (currWindowIndex+6)<coreSqlUpper.length(); ) {
			String currSubStr = coreSqlUpper.substring(currWindowIndex, currWindowIndex+6);			
			if("SELECT".equals(currSubStr)) {
				++selectSubSqlNum;
				currWindowIndex += 6;
			}else if(currSubStr.startsWith("FROM")) {
				if(selectSubSqlNum == 1) {
					return currWindowIndex;
				}
				--selectSubSqlNum;
				currWindowIndex += 4;
			}
			++currWindowIndex;
		}
		return -1;
	}
	
	private static String replaceSelectAndFromBetweenTopCornerMark(String sql) {
		StringBuffer result = new StringBuffer();
		boolean isBetweenMark = false;
		int iMark = 0;		
		int currIndex = 0;
		for(currIndex=0; currIndex<sql.length(); ++currIndex) {
			char currChar = sql.charAt(currIndex);
			if(currChar == '\'') {
				if(!isBetweenMark) {
					result.append(sql.substring(iMark, currIndex));	
					isBetweenMark = true;
				}else {
					result.append(sql.substring(iMark, currIndex).replaceAll("SELECT", "******").replaceAll("FROM", "****"));
					isBetweenMark = false;
				}
				iMark = currIndex;
			}else if(currChar == '\\') {
				++currIndex;
			}
		}
		if(iMark > -1) {
			result.append(sql.substring(iMark, currIndex));
		}
		return result.toString();
	}
	
	/**
	 * 获取排序字符串
	 * @param sort
	 * @param order
	 * @param columnPreffix
	 * @return
	 */
	public static String getSortCondition(String sort, String order, String columnPreffix){		
		String underScoreSort = UtilsString.underscoreName(sort).toLowerCase();
		if(StringUtils.isEmpty(columnPreffix)){
			columnPreffix = "";
		}
		String sortCondition = " ";
		if(!StringUtils.isEmpty(underScoreSort)){
			sortCondition = " order by ";
			String[] sortArr = underScoreSort.split(",");
			if(StringUtils.isEmpty(order)){
				sortCondition += columnPreffix+sortArr[0]+" DESC ";
				for(int i=1; i<sortArr.length; ++i){
					sortCondition += ","+columnPreffix+sortArr[i]+" DESC ";	
				}					
			}else{
				String[] orderArr = order.split(",");
				sortCondition += sortArr[0]+" "+orderArr[0]+" ";
				for(int i=1; i<sortArr.length; ++i){
					if(i < orderArr.length){
						sortCondition = ","+columnPreffix+sortArr[i]+" "+orderArr[i]+" ";	
					}else{
						sortCondition = ","+columnPreffix+sortArr[i]+" DESC ";
					}						
				}				
			}			
		}
		return sortCondition;
	}
	
	/**
	 * 获取分页
	 * @param sort
	 * @param order
	 * @param columnPreffix
	 * @return
	 */
	public static String getLimitCondition(int page, int pageSize){
		String sql = "";
		if(page < -1){
			page = 0;
			if(pageSize < 1){
				pageSize = 0;
			}
			sql += " limit "+pageSize*(page-1)+","+pageSize;
		}else if(page == -1){
			sql = "";
		}else{
			if(pageSize < 1){
				pageSize = 0;
			}
			sql += " limit "+pageSize*(page-1)+","+pageSize;
		}
		return sql;
	}
	
	/**
	 * 创建分页返回
	 * @param page
	 * @param rows
	 * @param data
	 * @return
	 */
	public static Map<String, Object> createPage(int page, int size, int total, Object data){
		Map<String, Object> pageData = new HashMap<String, Object>();
		pageData.put("page", page);
		pageData.put("rows", data);
		pageData.put("size", size);
		pageData.put("total", total);
		return pageData;
	}
	
	public static void main(String[] args) {
		try {
			String[] data = splitCoreSql("select t,(select * as 'fromCActio', dd as 'seelctsele\'ctd' from t) from www where 223");
			System.out.println(data[0]);
			System.out.println(data[1]);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}