package cc.smtweb.framework.core.util;

import cc.smtweb.framework.core.common.AbstractEnum;
import cc.smtweb.framework.core.common.IntEnum;
import cc.smtweb.framework.core.common.StrEnum;
import cc.smtweb.framework.core.db.EntityHelper;
import org.apache.commons.lang3.StringUtils;

import java.util.List;

/**
 * 注意：where参数表示是否已经加入了where关键字
 *
 * @author : AKzz
 * @version : $Revision:$
 */
@SuppressWarnings("UnusedDeclaration")
public abstract class SqlUtil {
    /**
     * 将sql中的表名替换成schema.table的格式
     *
     * @param sql
     * @return
     */
    public static String replaceTable(String sql) {
        return StringUtil.myReplaceStrEx(sql, "[#", "#]", new StringUtil.IStrHanlder() {
            @Override
            public String work(String src) {
                src = src.toUpperCase();
                return EntityHelper.getSchemaTableName(src);
            }
        });

    }

    /**
     * 补充SQL的字符串的==条件
     *
     * @param where_is_exist 是否已经加入了where关键字
     */
    public static boolean addSql_EQUALE_Expr(boolean where_is_exist, StringBuilder sql, List<Object> args, String db_field_name, int condition_value) {
        return addSql_EQUALE_Expr(where_is_exist, sql, args, db_field_name, String.valueOf(condition_value));
    }

    public static boolean addSql_EQUALE_Expr(boolean where_is_exist, StringBuilder sql, List<Object> args, String db_field_name, long condition_value) {
        return addSql_EQUALE_Expr(where_is_exist, sql, args, db_field_name, String.valueOf(condition_value));
    }

    /**
     * 补充SQL的字符串的==条件
     *
     * @param where_is_exist 是否已经加入了where关键字
     */
    public static boolean addSql_EQUALE_Expr(boolean where_is_exist, StringBuilder sql, List<Object> args, String db_field_name, String condition_value) {
        if (StringUtil.isNotEmpty(condition_value)) {
            if (!where_is_exist) {
                sql.append(" WHERE ");
            } else {
                sql.append(" AND ");
            }
            sql.append(db_field_name).append(" = ? \n");
            args.add(condition_value);
        }
        return true;
    }

    public static boolean addSql_lesser(boolean where_is_exist, StringBuilder sql, List<Object> args, String db_field_name, String condition_value) {
        if (StringUtil.isNotEmpty(condition_value)) {
            if (!where_is_exist) {
                sql.append(" WHERE ");
                where_is_exist = true;
            } else {
                sql.append(" AND ");
            }
            sql.append(db_field_name).append(" <= ? \n");
            args.add(condition_value);
        }
        return where_is_exist;
    }

    public static boolean addSql_greater(boolean where_is_exist, StringBuilder sql, List<Object> args, String db_field_name, String condition_value) {
        if (StringUtil.isNotEmpty(condition_value)) {
            if (!where_is_exist) {
                sql.append(" WHERE ");
                where_is_exist = true;
            } else {
                sql.append(" AND ");
            }
            sql.append(db_field_name).append(" >= ? \n");
            args.add(condition_value);
        }
        return where_is_exist;
    }


    /**
     * 补充SQL的字符串Like条件(仅后匹配)  %s
     *
     * @param where_is_exist 是否已经加入了where关键字
     */
    public static boolean addSql_LIKE_Pre(boolean where_is_exist, StringBuilder sql, List<Object> args, String db_field_name, String condition_value) {
        if (StringUtil.isNotEmpty(condition_value)) {
            //mysq _ 是一个占位符需要加\进行处理
            condition_value = condition_value.replaceAll("_", "\\\\_");
            if (!where_is_exist) {
                sql.append(" WHERE ");
                where_is_exist = true;
            } else {
                sql.append(" AND ");
            }
            sql.append(db_field_name).append(" LIKE ? \n");
            args.add(String.format("%%%s", condition_value));
        }
        return where_is_exist;
    }

    public static boolean addSql_LIKE_Pre(boolean where_is_exist, StringBuilder sql, List<Object> args, String[] db_field_names, String condition_value) {
        if (StringUtil.isNotEmpty(condition_value)) {
            //mysq _ 是一个占位符需要加\进行处理
            condition_value = condition_value.replaceAll("_", "\\\\_");
            if (!where_is_exist) {
                sql.append(" WHERE ");
                where_is_exist = true;
            } else {
                sql.append(" AND ");
            }
            sql.append("(").append(db_field_names[0]).append(" LIKE ? \n");
            args.add(String.format("%%%s", condition_value));
            for (int i = 1; i < db_field_names.length; ++i) {
                sql.append(" or ").append(db_field_names[i]).append(" LIKE ? \n");
                args.add(String.format("%%%s", condition_value));
            }
            sql.append(")");
        }
        return where_is_exist;
    }

    public static boolean addSql_LIKE_EX(boolean where_is_exist, StringBuilder sql, List<Object> args, String condition_value, String... db_field_name) {
        if (db_field_name == null || db_field_name.length == 0) return where_is_exist;
        if (StringUtil.isNotEmpty(condition_value)) {
            //mysq _ 是一个占位符需要加\进行处理
            condition_value = condition_value.replaceAll("_", "\\\\_");
            if (!where_is_exist) {
                sql.append(" WHERE ");
                where_is_exist = true;
            } else {
                sql.append(" AND ");
            }
            if (db_field_name.length == 1) {
                sql.append(db_field_name[0]).append(" LIKE ? \n");
                args.add(String.format("%%%s%%", condition_value));
                return where_is_exist;
            }
            sql.append(" (");
            for (String db_filed : db_field_name) {
                sql.append(" ").append(db_filed).append(" LIKE ? \nOR");
                args.add(String.format("%%%s%%", condition_value));
            }
            sql.delete(sql.length() - 2, sql.length());
            sql.append(")\n");

        }
        return where_is_exist;
    }


    /**
     * 补充SQL的字符串Like条件(仅后匹配)  s%
     *
     * @param where_is_exist 是否已经加入了where关键字
     */
    public static boolean addSql_LIKE_Expr(boolean where_is_exist, StringBuilder sql, List<Object> args, String db_field_name, String condition_value) {
        if (StringUtil.isNotEmpty(condition_value)) {
            //mysq _ 是一个占位符需要加\进行处理
            condition_value = condition_value.replaceAll("_", "\\\\_");
            if (!where_is_exist) {
                sql.append(" WHERE ");
                where_is_exist = true;
            } else {
                sql.append(" AND ");
            }
            sql.append(db_field_name).append(" LIKE ? \n");
            args.add(String.format("%s%%", condition_value));
        }
        return where_is_exist;
    }


    public static boolean addSql_LIKE_Expr(boolean where_is_exist, StringBuilder sql, List<Object> args, String[] db_field_names, String condition_value) {
        if (StringUtil.isNotEmpty(condition_value)) {
            //mysq _ 是一个占位符需要加\进行处理
            condition_value = condition_value.replaceAll("_", "\\\\_");
            if (!where_is_exist) {
                sql.append(" WHERE ");
                where_is_exist = true;
            } else {
                sql.append(" AND ");
            }
            sql.append("(").append(db_field_names[0]).append(" LIKE ? \n");
            args.add(String.format("%s%%", condition_value));
            for (int i = 1; i < db_field_names.length; ++i) {
                sql.append(" or ").append(db_field_names[i]).append(" LIKE ? \n");
                args.add(String.format("%s%%", condition_value));
            }
            sql.append(")");
        }
        return where_is_exist;
    }

    /**
     * 补充SQL的字符串Like条件(全模糊)  %s%
     *
     * @param where_is_exist 是否已经加入了where关键字
     */
    public static boolean addSql_LIKE(boolean where_is_exist, StringBuilder sql, List<Object> args, String db_field_name, String condition_value) {
        if (StringUtil.isNotEmpty(condition_value)) {
            //mysq _ 是一个占位符需要加\进行处理
            condition_value = condition_value.replaceAll("_", "\\\\_");
            if (!where_is_exist) {
                sql.append(" WHERE ");
                where_is_exist = true;
            } else {
                sql.append(" AND ");
            }
            sql.append(db_field_name).append(" LIKE ? \n");
            args.add(String.format("%%%s%%", condition_value));
        }
        return where_is_exist;
    }

    public static boolean addSql_LIKE(boolean where_is_exist, StringBuilder sql, List<Object> args, String[] db_field_names, String condition_value) {
        if (StringUtil.isNotEmpty(condition_value)) {
            //mysq _ 是一个占位符需要加\进行处理
            condition_value = condition_value.replaceAll("_", "\\\\_");
            if (!where_is_exist) {
                sql.append(" WHERE ");
                where_is_exist = true;
            } else {
                sql.append(" AND ");
            }
            sql.append("(").append(db_field_names[0]).append(" LIKE ? ");
            args.add(String.format("%%%s%%", condition_value));
            for (int i = 1; i < db_field_names.length; ++i) {
                sql.append(" OR ").append(db_field_names[i]).append(" LIKE ? ");
                args.add(String.format("%%%s%%", condition_value));
            }
            sql.append(")");
        }
        return where_is_exist;
    }

    /**
     * 补充SQL字符串的IN条件
     *
     * @param where_is_exist 是否已经加入了where关键字
     */
    public static boolean addSql_IN_Expr(boolean where_is_exist, StringBuilder sql, String db_field_name, Object... expr) {
        if (expr != null && expr.length > 0) {
            if (!where_is_exist) {
                sql.append(" WHERE ");
                where_is_exist = true;
            } else {
                sql.append(" AND ");
            }
            sql.append(db_field_name).append(" IN (").append(StringUtils.join(expr, ',')).append(")\n");
        }
        return where_is_exist;
    }

    /**
     * 补充SQL字符串的not IN条件
     *
     * @param where_is_exist 是否已经加入了where关键字
     */
    public static boolean addSql_NOTIN_Expr(boolean where_is_exist, StringBuilder sql, String db_field_name, Object... expr) {
        if (expr != null && expr.length > 0) {
            if (!where_is_exist) {
                sql.append(" WHERE ");
                where_is_exist = true;
            } else {
                sql.append(" AND  ");
            }
            sql.append(db_field_name).append(" NOT IN (").append(StringUtils.join(expr, ',')).append(")\n");
        }
        return where_is_exist;
    }

    /**
     * 增加字符串字段条件，可能多选
     *
     * @param field 查询的字段名
     * @param value 值，包含','为多选
     * @param sql   sql
     * @param paras 参数
     */
    public static void addStrConMulti(String field, String value, StringBuilder sql, List<Object> paras) {
        if (StringUtil.isEmpty(value))
            return;
        if (value.contains(",")) {//多选
            sql.append(" and ").append(field).append(" in ('").append(value.replaceAll(",", "','")).append("')");
        } else if (!Consts.NULL_STRING.equals(value)) {//单
            sql.append(" and ").append(field).append("=?");
            paras.add(value);
        }
    }

    /**
     * 增加字符串字段条件，不判断多选
     *
     * @param field 查询的字段名
     * @param value 值
     * @param paras 参数
     */
    public static void addStrConSingle(String field, String value, StringBuilder sb, List<Object> paras) {
        addStrConSingle(field, "=", value, sb, paras);
    }

    /**
     * 增加字符串字段条件，不判断多选
     *
     * @param field 查询的字段名
     * @param opt   逻辑运算符
     * @param value 值
     * @param paras 参数
     */
    public static void addStrConSingle(String field, String opt, String value, StringBuilder sb, List<Object> paras) {
        if (StringUtil.isNotEmpty(value) && !Consts.NULL_STRING.equals(value)) {//单
            sb.append(" and ").append(field).append(" ").append(opt).append(" ? \n");
            paras.add(value);
        }
    }

    /**
     * 增加字符串字段条件，包含
     *
     * @param field 查询的字段名
     * @param value 值
     * @param paras 参数
     */
    public static void addStrConSingleLike(String field, String value, StringBuilder sb, List<Object> paras) {
        if (StringUtil.isNotEmpty(value) && !Consts.NULL_STRING.equals(value)) {//单
            sb.append(" and ").append(field).append(" like ? \n");
            paras.add("%" + value + "%");
        }
    }

    /**
     * 增加字符串字段条件并且不区分大小写，包含
     *
     * @param field 查询的字段名
     * @param value 值
     * @param paras 参数
     */
    public static void addStrConSingleLikeIgnoreCase(String field, String value, StringBuilder sb, List<Object> paras) {
        if (StringUtil.isNotEmpty(value) && !Consts.NULL_STRING.equals(value)) {
            String valueLower = value.toLowerCase();
            sb.append(" and ").append("lower(").append(field).append(")").append(" like ? \n");
            paras.add("%" + valueLower + "%");
        }
    }

    /**
     * 增加数字字段条件，可能多选
     *
     * @param field 查询的字段名
     * @param value 值，包含','为多选
     * @param paras 参数
     */
    public static void addNumberConMulti(String field, String value, StringBuilder sb, List<Object> paras) {
        if (StringUtil.isEmpty(value))
            return;
        if (value.contains(",")) {//多选
            sb.append(" and ").append(field).append(" in (").append(value).append(") \n");
        } else if (!Consts.NULL_STRING.equals(value)) {//单
            sb.append(" and ").append(field).append("=? \n");
            paras.add(value);
        }
    }

    /**
     * 增加数字字段条件，单选
     *
     * @param field 查询的字段名
     * @param value 值
     * @param paras 参数
     */
    public static void addNumConSingle(String field, Number value, StringBuilder sb, List<Object> paras) {
        addNumConSingle(field, "=", value, sb, paras);
    }

    /**
     * 增加数字字段条件，单选
     *
     * @param field 查询的字段名
     * @param opt   运算符
     * @param value 值
     * @param paras 参数
     */
    public static void addNumConSingle(String field, String opt, Number value, StringBuilder sb, List<Object> paras) {
        sb.append(" and ").append(field).append(opt).append("? \n");
        paras.add(value);
    }

    /**
     * 增加日期字段条件，可能多选
     *
     * @param field 查询的字段名
     * @param value 值
     * @param paras 参数
     */
    @Deprecated
    public static void addDateCon(String field, String value, StringBuilder sb, List<Object> paras) {
        if (StringUtil.isEmpty(value))
            return;
        String[] st = value.split(",");
        if (st.length == 0 || Consts.NULL_STRING.equals(st[0])) {//无
            return;
        }
        String s = DateUtil.nowDateString();
        if (st.length > 0) {
            switch (st[0]) {
                case "D": //当天  考虑日期时间的情况
                    sb.append(" and ").append(field).append(">='").append(s).append("' and ").append(field).append("<'").append(DateUtil.getAfterDays(s, 1)).append("' \n");
                    break;
                case "W": //本周
                    s = DateUtil.toStdDateString(DateUtil.getWeekStart());
                    sb.append(" and ").append(field).append(">='").append(s).append("' and ").append(field).append("<'").append(DateUtil.getAfterDays(s, 8)).append("' \n");
                    break;
                case "M": //本月
                    sb.append(" and ").append(field).append(">='").append(DateUtil.getMonthStart()).append("' and ").append(field).append("<='").append(DateUtil.getMonthEnd()).append(" 23:59:59' \n");
                    break;
                case "Q": //本季度
                    sb.append(" and ").append(field).append(">='").append(DateUtil.getSeasonFirstDay()).append("' and ").append(field).append("<='").append(DateUtil.getSeasonLastDay()).append(" 23:59:59' \n");
                    break;
                case "Y": //本年
                    sb.append(" and ").append(field).append(">='").append(DateUtil.getYearFirstDay()).append("' and ").append(field).append("<='").append(DateUtil.getYearLastDay()).append(" 23:59:59' \n");
                    break;
                default: //自定义
                    String df, de;
                    if (st.length > 1)
                        df = st[1];
                    else
                        df = "";
                    if (st.length > 2)
                        de = st[2];
                    else
                        de = "";
                    if (StringUtil.isEmpty(df) && StringUtil.isEmpty(de))
                        return;
                    if (StringUtil.isEmpty(df)) {
                        sb.append(" and ").append(field).append("<=?");
                        paras.add(de);
                    } else if (StringUtil.isEmpty(de)) {
                        sb.append(" and ").append(field).append(">=?");
                        paras.add(df);
                    } else {
                        sb.append(" and ").append(field).append(">=? and ").append(field).append("<=? \n");
                        paras.add(df);
                        paras.add(de);
                    }
                    break;
            }
        }
    }

    /**
     * 增加日期字段条件，可能多选(huangxl:日期格式YYYYMMDD)
     *
     * @param field 查询的字段名
     * @param value 值
     * @param paras 参数
     */
    public static void addDateConEx(String field, String value, StringBuilder sb, List<Object> paras) {
        if (StringUtil.isEmpty(value))
            return;
        String[] st = value.split(",");
        if (st.length == 0 || Consts.NULL_STRING.equals(st[0])) {//无
            return;
        }
        String s = DateUtil.nowDateString();
        if (st.length > 0) {
            switch (st[0]) {
                case "D": //当天
                    sb.append(" and ").append(field).append("=").append(DateUtil.getDateLong(s)).append(" \n");
                    break;
                case "W": //本周
                    s = DateUtil.toStdDateString(DateUtil.getWeekStart());
                    sb.append(" and ").append(field).append(" between ").append(DateUtil.getDateLong(s)).append(" and ").append(DateUtil.getDateLong(DateUtil.getAfterDays(s, 7))).append(" \n");
                    break;
                case "M": //本月
                    sb.append(" and ").append(field).append(" between ").append(DateUtil.getDateLong(DateUtil.getMonthStart())).append(" and ").append(DateUtil.getDateLong(DateUtil.getMonthEnd())).append(" \n");
                    break;
                case "Q": //本季度
                    sb.append(" and ").append(field).append(" between ").append(DateUtil.getDateLong(DateUtil.getSeasonFirstDay())).append(" and ").append(DateUtil.getDateLong(DateUtil.getSeasonLastDay())).append("  \n");
                    break;
                case "Y": //本年
                    sb.append(" and ").append(field).append(" between ").append(DateUtil.getDateLong(DateUtil.getYearFirstDay())).append(" and ").append(DateUtil.getDateLong(DateUtil.getYearLastDay())).append("  \n");
                    break;
                default: //自定义
                    String df, de;
                    if (st.length > 1)
                        df = st[1];
                    else
                        df = "";
                    if (st.length > 2)
                        de = st[2];
                    else
                        de = "";
                    if (StringUtil.isEmpty(df) && StringUtil.isEmpty(de))
                        return;
                    if (StringUtil.isEmpty(df)) {
                        sb.append(" and ").append(field).append("<=?");
                        paras.add(DateUtil.getDateLong(de));
                    } else if (StringUtil.isEmpty(de)) {
                        sb.append(" and ").append(field).append(">=?");
                        paras.add(DateUtil.getDateLong(df));
                    } else {
                        sb.append(" and ").append(field).append(" between ? and ? \n");
                        paras.add(DateUtil.getDateLong(df));
                        paras.add(DateUtil.getDateLong(de));
                    }
                    break;
            }
        }
    }

    /**
     * 增加期间查询日期字段条件，可能多选(huangxl:日期格式YYYYMMDD)
     *
     * @param field 查询的字段名
     * @param value 值
     * @param paras 参数
     */
    public static void addPeriodDateConEx(String field, String value, StringBuilder sb, List<Object> paras) {
        if (StringUtil.isEmpty(value))
            return;
        String[] st = value.split(",");
        if (st.length == 0 || Consts.NULL_STRING.equals(st[0])) {//无
            return;
        }
        String df, de;
        if (st.length > 1)
            df = st[1];
        else
            df = "";
        if (st.length > 2)
            de = st[2];
        else
            de = "";

        sb.append(" and ").append(field).append(" between ? and ? \n");
        paras.add(df + "01");
        paras.add(de + "31");
    }


    /**
     * 增加日期时间字段条件
     *
     * @param field 查询的字段名
     * @param value 值
     * @param paras 参数
     */
    public static void addDateTimeConEx(String field, String value, StringBuilder sb, List<Object> paras) {
        if (StringUtil.isEmpty(value))
            return;
        String[] st = value.split(",");
        if (st.length == 0 || Consts.NULL_STRING.equals(st[0])) {//无
            return;
        }
        String s = DateUtil.nowDateString();
        if (st.length > 0) {
            switch (st[0]) {
                case "D": //当天
                    sb.append(" and ").append(field).append(" between ").append(DateUtil.getDateTimeLong(s)).append(" and ").append(DateUtil.getEndDateTimeLong(s)).append(" \n");
                    break;
                case "W": //本周
                    s = DateUtil.toStdDateString(DateUtil.getWeekStart());
                    sb.append(" and ").append(field).append(" between ").append(DateUtil.getDateTimeLong(s)).append(" and ").append(DateUtil.getEndDateTimeLong(DateUtil.getAfterDays(s, 6))).append(" \n");
                    break;
                case "M": //本月
                    sb.append(" and ").append(field).append(" between ").append(DateUtil.getDateTimeLong(DateUtil.getMonthStart())).append(" and ").append(DateUtil.getEndDateTimeLong(DateUtil.getMonthEnd())).append(" \n");
                    break;
                case "Q": //本季度
                    sb.append(" and ").append(field).append(" between ").append(DateUtil.getDateTimeLong(DateUtil.getSeasonFirstDay())).append(" and ").append(DateUtil.getEndDateTimeLong(DateUtil.getSeasonLastDay())).append("  \n");
                    break;
                case "Y": //本年
                    sb.append(" and ").append(field).append(" between ").append(DateUtil.getDateTimeLong(DateUtil.getYearFirstDay())).append(" and ").append(DateUtil.getEndDateTimeLong(DateUtil.getYearLastDay())).append("  \n");
                    break;
                default: //自定义
                    String df, de;
                    if (st.length > 1)
                        df = st[1];
                    else
                        df = "";
                    if (st.length > 2)
                        de = st[2];
                    else
                        de = "";
                    if (StringUtil.isEmpty(df) && StringUtil.isEmpty(de))
                        return;
                    if (StringUtil.isEmpty(df)) {
                        sb.append(" and ").append(field).append("<=?");
                        paras.add(DateUtil.getEndDateTimeLong(de));
                    } else if (StringUtil.isEmpty(de)) {
                        sb.append(" and ").append(field).append(">=?");
                        paras.add(DateUtil.getDateTimeLong(df));
                    } else {
                        sb.append(" and ").append(field).append(" between ? and ? \n");
                        paras.add(DateUtil.getDateTimeLong(df));
                        paras.add(DateUtil.getEndDateTimeLong(de));
                    }
                    break;
            }
        }
    }

    /**
     * 枚举状态字段，in方式
     *
     * @param where_is_exist sql是否已包括where
     * @param field          sql字段名
     * @param value          前端传回来的查询值，格式:1,1,1,1
     * @param intEnum        枚举类型
     * @param sql            待构建的sql
     */
    public static void addIntEnumCon(boolean where_is_exist, String field, String value, IntEnum intEnum, StringBuilder sql) {
        if (StringUtil.isNotEmpty(value) && value.contains("1") && value.contains("0")) {
            if (!where_is_exist) {
                sql.append(" WHERE ");
                where_is_exist = true;
            } else {
                sql.append(" AND ");
            }
            String s = "";
            String[] ss = value.split(",");
            int i = 0;
            for (AbstractEnum.IntEnumBean b : intEnum.values()) {
                if (ss.length > i && "1".equals(ss[i])) s += "," + b.value;
                i++;
            }
            sql.append(field + " in (" + s.substring(1) + ")");
        }
    }

    /**
     * 枚举状态字段，in方式
     *
     * @param where_is_exist sql是否已包括where
     * @param field          sql字段名
     * @param value          前端传回来的查询值，格式:1,1,1,1
     * @param intEnum        枚举类型
     * @param sql            待构建的sql
     */
    public static void addStrEnumCon(boolean where_is_exist, String field, String value, StrEnum intEnum, StringBuilder sql) {
        if (StringUtil.isNotEmpty(value) && value.contains("1") && value.contains("0")) {
            if (!where_is_exist) {
                sql.append(" WHERE ");
                where_is_exist = true;
            } else {
                sql.append(" AND ");
            }
            String s = "";
            String[] ss = value.split(",");
            int i = 0;
            for (AbstractEnum.StrEnumBean b : intEnum.values()) {
                if (ss.length > i && "1".equals(ss[i])) s += ",'" + b.value + "'";
                i++;
            }
            sql.append(field + " in (" + s.substring(1) + ")");
        }
    }

    public static void addTreeStateCon(String curPrefix, String value, String tableName, StringBuilder sb, List<Object> paras) {
        if (StringUtil.isNotEmpty(value) && !Consts.NULL_STRING.equals(value) && !value.contains(",")) {
            int v = NumberUtil.getIntIgnoreErr(value);
            if (v == 0) {//查启用 自己启用，且父亲未停用
                sb.append(" and ").append(curPrefix).append(".f_status=0 and not exists(select 1 from ").append(tableName).append(" p__ where p__.f_status=1 and ").append(curPrefix).append(".f_level_code like p__.f_level_code || '-%')");
            } else {//查停用 自己停用 或父亲被停用
                sb.append(" and (").append(curPrefix).append(".f_status=1 or exists(select 1 from ").append(tableName).append(" p__ where p__.f_status=1 and ").append(curPrefix).append(".f_level_code like p__.f_level_code || '-%'))");
            }
        }
    }

    /**
     * 增加年月字段条件
     *
     * @param field 查询的字段名
     * @param value 值
     * @param paras 参数
     */
    public static void addYmCon(String field, String value, StringBuilder sb, List<Object> paras) {
        addYmConEx(field, field, value, sb, paras);
    }

    public static void addYmConEx(String field_begin, String field_end, String value, StringBuilder sb, List<Object> paras) {
        if (StringUtil.isEmpty(value))
            return;
        String[] st = value.split(",");
        if (st.length == 0 || Consts.NULL_STRING.equals(st[0])) {//无
            return;
        }
        if (st.length > 1) {
            switch (st[0]) {
                case "M": //本月
                    String nowYm = DateUtil.getNowYm();
                    sb.append(" and ").append(field_end).append(">='").append(DateUtil.getNowYm()).append("' and ").append(field_begin).append("<='").append(nowYm).append("'");
                    break;
                case "Q": //本季度
                    sb.append(" and ").append(field_end).append(">='").append(DateUtil.getSeasonBeginYm()).append("' and ").append(field_begin).append("<='").append(DateUtil.getSeasonEndYm()).append("'");
                    break;
                case "Y": //本年
                    sb.append(" and ").append(field_end).append(">='").append(DateUtil.getYearBeginYm()).append("' and ").append(field_begin).append("<='").append(DateUtil.getYearEndYm()).append("'");
                    break;
                default: //自定义
                    String df, de;
                    if (st.length > 1)
                        df = st[1];
                    else
                        df = "";
                    if (st.length > 2)
                        de = st[2];
                    else
                        de = "";
                    if (StringUtil.isEmpty(df) && StringUtil.isEmpty(de))
                        return;
                    if (StringUtil.isEmpty(df)) {
                        sb.append(" and ").append(field_begin).append("<=?");
                        paras.add(de);
                    } else if (StringUtil.isEmpty(de)) {
                        sb.append(" and ").append(field_end).append(">=?");
                        paras.add(df);
                    } else {
                        sb.append(" and ").append(field_end).append(">=? and ").append(field_begin).append("<=?");
                        paras.add(df);
                        paras.add(de);
                    }
                    break;
            }
        } else if (st.length == 1) {
            sb.append(" and ").append(field_end).append(">='").append(value).append("' and ").append(field_begin).append("<='").append(value).append("'");
        }
    }

    //构建年月范围的sql
    public static String getYmConSql(String field, String value) {
        return getYmConSqlEx(field, field, value);
    }

    public static String getYmConSqlEx(String field_begin, String field_end, String value) {
        if (StringUtil.isEmpty(value)) return "";
        String[] st = value.split(",");
        if (st.length == 0 || Consts.NULL_STRING.equals(st[0])) return "";
        StringBuilder sb = new StringBuilder(128);

        switch (st[0]) {
            case "M": //本月
                String nowYm = DateUtil.getNowYm();
                sb.append(" and ").append(field_end).append(">='").append(DateUtil.getNowYm()).append("' and ").append(field_begin).append("<='").append(nowYm).append("'");
                break;
            case "Q": //本季度
                sb.append(" and ").append(field_end).append(">='").append(DateUtil.getSeasonBeginYm()).append("' and ").append(field_begin).append("<='").append(DateUtil.getSeasonEndYm()).append("'");
                break;
            case "Y": //本年
                sb.append(" and ").append(field_end).append(">='").append(DateUtil.getYearBeginYm()).append("' and ").append(field_begin).append("<='").append(DateUtil.getYearEndYm()).append("'");
                break;
            default: //自定义
                String df, de;
                if (st.length > 1)
                    df = st[1];
                else
                    df = "";
                if (st.length > 2)
                    de = st[2];
                else
                    de = "";
                if (StringUtil.isEmpty(df) && StringUtil.isEmpty(de))
                    return "";
                if (StringUtil.isEmpty(df)) {
                    sb.append(" and ").append(field_begin).append("<='").append(de).append("'");
                } else if (StringUtil.isEmpty(de)) {
                    sb.append(" and ").append(field_end).append(">='").append(df).append("'");
                } else {
                    sb.append(" and ").append(field_end).append(">='").append(df).append("' and ").append(field_begin).append("<='").append(de).append("'");
                }
                break;
        }
        return sb.substring(5);
    }
}
