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 += 5;
			}
			++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) {
		String[] testSql = new String[] {
			"select actionFrom t,(select * as 'fromCActio', dd as 'seelctsele\'ctd' from t) from www where 223",
			"SELECT wxorder_order.refund_way refundWay,wxorder_order.refund_delivery_sn refundDeliverySn, (SELECT sum(wxorder_compensate.`ware_refund_money`)  from `wxorder_compensate`  where wxorder_compensate.`order_id`  = wxorder_order.id) as wareRefundMoney,(SELECT sum(wxorder_compensate.`refund_money`)  from `wxorder_compensate`  where wxorder_compensate.`order_id`  = wxorder_order.id) as refundMoney,  wxorder_order.operate_refund_time applyCompensateTime,wxorder_order.refund_instructions refundInstructions, wxorder_order.channel_id channelId, wxorder_channel.name channelName, wxorder_channel.platform_code platformCode,  wxorder_order.pre_control_status as refundPreStatus, wxorder_order.ware_id wareId, wxorder_ware.name wareName,  wxorder_order.id, wxorder_order.order_sn orderSn, wxorder_order.upload_sn uploadSn, wxorder_order.money_paid moneyPaid,  wxorder_order.consignee, wxorder_order.mobile, wxorder_order.province_name provinceName, wxorder_order.city_name cityName, wxorder_order.district_name districtName, wxorder_order.address,  wxorder_order.channel_remark channelRemark, wxorder_order.customer_remark customerRemark, wxorder_order.platform_customer_remark platformCustomerRemark,  wxorder_order.sender_name senderName, wxorder_order.sender_mobile senderMobile,  date_format(wxorder_order.create_time, '%Y-%m-%d %H:%i:%s') createTime, date_format(wxorder_order.real_delivery_time, '%Y-%m-%d %H:%i:%s') realDeliveryTime,  wxorder_order.progress_distribute progressDistribute, wxorder_order.progress_delivery progressDelivery, wxorder_order.control_status controlStatus,  wxorder_order.express_name expressName, wxorder_order.express_number expressNumber,wxorder_order.shipping_price shippingPrice, wxorder_order.customer_network_name ,(select count(*) from wxorder_compensate WHERE wxorder_compensate.order_id = wxorder_order.id) as compensateCountAll,(select count(*) from wxorder_compensate WHERE wxorder_compensate.order_id = wxorder_order.id and wxorder_compensate.progress_status = 1) as compensateCountDealed ,(select count(*) from wxorder_channel_refund_payment where wxorder_channel_refund_payment.order_id = wxorder_order.id) as channelRefundPaymentStatus,(select count(*) from wxorder_ware_refund_payment where wxorder_ware_refund_payment.order_id = wxorder_order.id) as wareRefundPaymentStatus  FROM wxorder_order wxorder_order  INNER JOIN wxorder_channel wxorder_channel ON wxorder_order.channel_id=wxorder_channel.id and wxorder_channel.tenant_id=54 INNER JOIN wxorder_ware wxorder_ware ON wxorder_order.ware_id=wxorder_ware.id and wxorder_ware.tenant_id=54 INNER JOIN wxorder_order_goods wxorder_order_goods ON wxorder_order.id=wxorder_order_goods.order_id and wxorder_order_goods.tenant_id=54 WHERE 1=1  AND wxorder_order.tenant_id='54' AND wxorder_order.control_status='4' GROUP BY wxorder_order.id",
			"select wdd, fromType from www where 223",
		};
		try {
			for(int i=0; i<testSql.length; ++i) {
				String item = testSql[i];
				System.out.println("================testSql["+i+"]===============");
				String[] data = splitCoreSql(item);
				System.out.println(data[0]);
				System.out.println(data[1]);
			}			
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}