AdminAreaSqlUtil.java

package com.jiuqi.lbsinterface.utils;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.SortedMap;
import java.util.TreeMap;

import com.jiuqi.lbsinterface.bean.AdminAreaUtilBean;
import com.jiuqi.lbsinterface.bean.MedianBean;


public class AdminAreaSqlUtil {
	/**
	 * 不满足拼接条件，返回null
	 * <p>
	 * 所有下级
	 * 
	 * @param field
	 * @param v
	 * @return
	 */
	public static String getSql(String field, Object v) {
		if (null != v && v instanceof Long) {
			if (v.equals(0l) || v.equals(0)) {
				return null;
			}
			long area = (Long) v;
			while (0 == area % 10) {
				area = area / 10;
			}
			if(String.valueOf(area).length()==1||String.valueOf(area).length()==3||String.valueOf(area).length()==5){
				area=(Long)area*10;
			}
			int pre = ("" + area).length();
			if (pre > 9) {
				return String.format(" %s=%s ", field, area);
			}
			return String.format(" LEFT(%s,%s)=%s ", field, pre, area);
		}
		return null;
	}

	/**
	 * 获得直属下级sql
	 * 
	 * @param field
	 * @param v
	 * @return
	 */
	public static String getDirectlySql(String field, Long v) {
		if (null != v && v instanceof Long) {
			long area = (Long) v;
			if (0 == area) {
				area = 0;
			} else {
				while (0 == area % 10) {
					area = area / 10;
				}
			}
			// 2,4,6,9,12
			int pre = ("" + area).length();
			if (pre > 9) {
				return String.format(" %s=%s ", field, area);
			} else if (pre > 6) {
				return String.format(" (LEFT(%s,%s)=%s) ", field, pre, area);
			} else if (pre > 4) {
				return String.format(" (LEFT(%s,%s)=%s and %s", field, pre,
						area, field) + "%" + 1000 + "=0) ";
			} else if (pre > 2) {
				return String.format(" (LEFT(%s,%s)=%s and %s", field, pre,
						area, field) + "%" + 1000000 + "=0) ";
			} else {
				return String.format(" (LEFT(%s,%s)=%s and %s", field, pre,
						area, field) + "%" + 100000000 + "=0) ";
			}
		}
		return null;
	}

	/**
	 * 内部车辆分布专用(用的时候详细看内容 谨慎使用)
	 * 
	 * @auth lizh
	 * @Date 2013-9-16上午11:08:54
	 * @method getSql
	 * @fileName AdminAreaSqlUtil
	 * @package com.jiuqi.mobile.nigo.biz.core.util
	 * @project snigo
	 * @param field
	 * @param v
	 * @return
	 * @return String
	 */
	public static MedianBean getMoreCodeSql(String field,
			List<AdminAreaUtilBean> v) {
		StringBuilder builder = new StringBuilder();
		MedianBean median = new MedianBean();
		Map<Integer, List<Long>> map = new HashMap<Integer, List<Long>>();
		for (AdminAreaUtilBean bean : v) {
			Long o = bean.getCode();
			if (null != o && o instanceof Long) {
				if (o.equals(0l) || o.equals(0)) {
					return null;
				}
				long area = (Long) o;
				while (0 == area % 10) {
					area = area / 10;
				}
				int pre = ("" + area).length();
				if (map.containsKey(pre)) {
					map.get(pre).add(area);
				} else {
					List<Long> list = new ArrayList<Long>();
					list.add(area);
					map.put(pre, list);
				}
				bean.setCodeMedian(area);
			}
		}
		builder.append("(");
		SortedMap<Integer, Integer> treeMap = new TreeMap<Integer, Integer>();
		for (Map.Entry<Integer, List<Long>> entry : map.entrySet()) {
			int key = entry.getKey();
			List<Long> list = entry.getValue();
			treeMap.put(list.size(), key);
			if (key > 9) {
				if (!"(".equals(builder.toString()))
					builder.append(" or ");
				builder.append(field);
				builder.append(" in ");
				builder.append("(");
				for (int i = 0; i < list.size(); i++) {
					builder.append("'");
					builder.append(list.get(i));
					builder.append("'");
					if (i != list.size() - 1)
						builder.append(",");
				}
				builder.append(") ");
			} else {
				if (!"(".equals(builder.toString()))
					builder.append(" or ");
				builder.append(" LEFT(");
				builder.append(field);
				builder.append(",");
				builder.append(key);
				builder.append(")");
				builder.append(" in ");
				builder.append("(");
				for (int i = 0; i < list.size(); i++) {
					builder.append("'");
					builder.append(list.get(i));
					builder.append("'");
					if (i != list.size() - 1)
						builder.append(",");
				}
				builder.append(") ");
			}
		}
		// 如果有多种移位 选取移位最多的一个 
		int key = treeMap.get(treeMap.lastKey());
		if (key > 9)
			median.setMedianFiled(field);
		else
			median.setMedianFiled(" LEFT(" + field + "," + key + ")");
		builder.append(")");
		median.setSql(builder.toString());
		return median;
	}

	public static void main(String[] args) {
		List<Object> list = new ArrayList<Object>();
		list.add(110000000000l);
		// list.add(360000000000l);
		// list.add(310000000000l);
		// list.add(111100000000l);
		// list.add(111122000000l);
		// list.add(111122222000l);
		// list.add(111122222123l);
		System.out.println(getSql("aa", 110000000000l));
	}
}