jnpf-java-boot-v6x/jnpf-system/jnpf-system-biz/target/classes/jnpf/base/util/dataSet/DataSetConfigUtil.java

package jnpf.base.util.dataSet;

import jnpf.base.model.dataset.*;
import jnpf.constant.DsKeyConst;
import jnpf.database.model.superQuery.SuperQueryJsonModel;
import jnpf.database.source.DbBase;
import jnpf.database.util.DbTypeUtil;
import jnpf.emnus.DsJoinTypeEnum;
import jnpf.emnus.SearchMethodEnum;
import jnpf.model.visualJson.FieLdsModel;
import jnpf.util.DateUtil;
import jnpf.util.JsonUtil;
import jnpf.util.StringUtil;
import org.apache.commons.collections4.CollectionUtils;

import java.util.*;

/**
 * 配置式工具类
 *
 * @author JNPF开发平台组
 * @version v5.1.0
 * @copyright 引迈信息技术有限公司
 * @date 2024/9/9 17:34:11
 */
public class DataSetConfigUtil {
    //字段别名
    public static final String FIELD_RENAME = "%s.%s AS %s ";
    //普通查询
    public static final String SELECT_FROM = "SELECT %s FROM %s";
    //最基础的查询别名
    public static final String SELECT_UNIT = "(SELECT %s FROM %s) %s";
    //基础的上级查询
    public static final String SELECT_UPPER = "(%s) %s";
    public static final String SELECT_BRACKET = "(%s)";

    public static final String SIGN_EMPTY = "";
    public static final String SIGN_BLANK = " ";
    public static final String SIGN_COMMA = ",";
    public static final String SIGN_EQUALS = " = ";
    public static final String SIGN_ON = " ON ";
    public static final String SIGN_AND = " AND ";
    public static final String SIGN_WHERE = " WHERE ";
    public static final String SIGN_OR = " OR ";
    public static final String SIGN_UNDERLINE = "_";
    public static final String SIGN_BAR = "-";
    public static final String SIGN_PER = "%";
    public static final String SIGN_BETWEEN = " BETWEEN ";
    public static final String SIGN_NULL = " IS NULL ";
    public static final String SIGN_NOT_NULL = " IS NOT NULL ";
    public static final String SIGN_NEQ = " <> ";
    public static final String SIGN_GREATER = " > ";
    public static final String SIGN_LESS = " < ";
    public static final String SIGN_GREATER_EQ = " >= ";
    public static final String SIGN_LESS_EQ = " <= ";
    public static final String SIGN_LIKE = " LIKE ";
    public static final String SIGN_NOT_LIKE = " NOT LIKE ";
    public static final String SIGN_JNPFNULLLIST = "jnpfNullList";
    public static final String SIGN_QUESTION = "?";
    public static final String SIGN_TAMESTAMP = "TO_TIMESTAMP(?,'yyyy-mm-dd hh24:mi:ss')";


    /**
     * 递归获取全部字段
     *
     * @param configList
     * @param list
     */
    public static void getAllFields(List<DsConfigModel> configList, List<DsConfigFields> list) {
        if (configList == null || CollectionUtils.isEmpty(configList)) return;
        for (DsConfigModel item : configList) {
            if (item.getFieldList() != null && item.getFieldList().size() > 0) {
                list.addAll(item.getFieldList());
            }
            getAllFields(item.getChildren(), list);
        }
    }

    /**
     * 单表查询字段别名
     *
     * @param list
     * @return
     */
    public static String getTableFieldsAlias(List<DsConfigFields> list, boolean onlyField) {
        if (list == null || CollectionUtils.isEmpty(list)) return SIGN_EMPTY;
        StringJoiner result = new StringJoiner(SIGN_COMMA);
        for (DsConfigFields item : list) {
            String tableAlias = item.getTable();
            String fieldAlias = item.getFieldAlias();
            String field = item.getField();
            if (StringUtil.isNotEmpty(fieldAlias)) {
                if (onlyField) {
                    //只有别名字段
                    result.add(fieldAlias);
                } else {
                    //表名.字段 AS 别名
                    result.add(String.format(FIELD_RENAME, tableAlias, field, fieldAlias));
                }
            } else {
                //只有原字段
                result.add(field);
            }
        }
        return result.toString();
    }

    /**
     * 解析json
     *
     * @param configListJson
     * @return
     */
    public static List<DsConfigModel> analyzeJson(String configListJson, String dbType) {
        List<DsConfigModel> configList = JsonUtil.getJsonToList(configListJson, DsConfigModel.class);
        if (CollectionUtils.isEmpty(configList)) {
            return new ArrayList<>();
        }
        //单个表不别名
        if (CollectionUtils.isEmpty(configList.get(0).getChildren())) {
            return configList;
        }

        int startIndex = 0;

        //别名小写
        if (DbTypeUtil.needToLowerCase(dbType)) {
            startIndex = 32;
        }
        analyzeDsConfigModel(configList, startIndex);
        return configList;
    }

    /**
     * 递归解析json
     *
     * @param configList
     * @param letterNum
     */
    private static void analyzeDsConfigModel(List<DsConfigModel> configList, int letterNum) {
        if (CollectionUtils.isNotEmpty(configList)) {
            for (DsConfigModel configModel : configList) {
                String tableLetter = getLetter(letterNum) + SIGN_UNDERLINE;
                String tableAlias = tableLetter + configModel.getTable();
                configModel.setTableAlias(tableAlias);
                //子表配置
                List<DsConfigModel> children = configModel.getChildren();
                if (CollectionUtils.isNotEmpty(children)) {
                    letterNum++;
                    analyzeDsConfigModel(children, letterNum);
                }
                //字段别名设置
                List<DsConfigFields> fieldList = configModel.getFieldList();
                if (CollectionUtils.isNotEmpty(fieldList)) {
                    for (DsConfigFields field : fieldList) {
                        field.setFieldAlias(tableLetter + field.getField());
                        field.setTable(configModel.getTable());
                    }
                }
                letterNum++;
            }
        }
    }

    /**
     * 组装sql
     *
     * @param configList
     * @return
     */
    public static String assembleSql(List<DsConfigModel> configList, DsParamModel dsParamModel) {
        String sql = "";
        if (CollectionUtils.isEmpty(configList)) {
            return null;
        }
        List<DsConfigFields> listDF = new ArrayList<>();
        DataSetConfigUtil.getAllFields(configList, listDF);

        String fieldsAlias = getTableFieldsAlias(listDF, true);

        DsConfigModel configModel = configList.get(0);

        StringBuilder result = new StringBuilder();
        //递归组装
        if (CollectionUtils.isNotEmpty(configModel.getChildren())) {
            StringBuilder childStr = new StringBuilder();
            DataSetConfigUtil.recurAssSql(configModel, childStr, dsParamModel);
            result.append(childStr);
        } else {
            String where = DataSetConfigUtil.recurAssWhere(configModel, dsParamModel, false);
            String tableAndWhere = "";
            if (!where.equals("()")){
               tableAndWhere = configModel.getTable() + (StringUtil.isNotEmpty(where) ? SIGN_WHERE + where : "");
                result.append(String.format(SELECT_FROM, fieldsAlias, tableAndWhere));
            }else {
                result.append(String.format(SELECT_FROM, fieldsAlias,configModel.getTable()));
            }

        }
        sql = result.toString();
        //组装最外层过滤
        if (StringUtil.isNotEmpty(dsParamModel.getFilterConfigJson())) {
            DsConfigModel relationModel = JsonUtil.getJsonToBean(dsParamModel.getFilterConfigJson(), DsConfigModel.class);
            if (CollectionUtils.isNotEmpty(relationModel.getRuleList())) {
                StringBuilder filterResult = new StringBuilder();
                String filterTable = StringUtil.isNotEmpty(configModel.getTableAlias()) ? configModel.getTableAlias() : configModel.getTable();
                filterResult.append(String.format(SELECT_FROM, fieldsAlias, String.format(SELECT_UPPER, sql, filterTable)));
                relationModel.setTable(configModel.getTable());
                relationModel.setFieldList(configModel.getFieldList());
                relationModel.setChildren(configModel.getChildren());
                String recurAssWhere = recurAssWhere(relationModel, dsParamModel, true);
                if (StringUtil.isNotEmpty(recurAssWhere)) {
                    filterResult.append(SIGN_WHERE);
                    filterResult.append(recurAssWhere);
                }
                sql = filterResult.toString();
            }
        }
        return sql;
    }

    /**
     * 递归组装
     *
     * @param configModel
     * @param result
     * @return
     */
    private static void recurAssSql(DsConfigModel configModel, StringBuilder result, DsParamModel dsParamModel) {
        List<DsConfigModel> children = configModel.getChildren();
        //递归获取全部字段
        List<DsConfigFields> listDF = new ArrayList<>();
        DataSetConfigUtil.getAllFields(new ArrayList() {{
            add(configModel);
        }}, listDF);
        String tableAlias = configModel.getTableAlias();
        String table = configModel.getTable();

        List<DsConfigFields> fieldList = configModel.getFieldList();
        String mainFields = DataSetConfigUtil.getTableFieldsAlias(fieldList, false);

        //当前查询条件
        String where = DataSetConfigUtil.recurAssWhere(configModel, dsParamModel, false);
        String tableAndWhere = table + (StringUtil.isNotEmpty(where) ? SIGN_WHERE + where : "");
        //查询当前表语句 - 有子表往里塞，没有直接抛出
        String tableSelect = String.format(SELECT_UNIT, mainFields, tableAndWhere, tableAlias);
        if (CollectionUtils.isNotEmpty(children)) {
            //查询结果集
            String allFields = DataSetConfigUtil.getTableFieldsAlias(listDF, true);
            StringBuilder childBuilder = new StringBuilder();

            //查询主表字段
            childBuilder.append(tableSelect);
            //查询连表
            for (DsConfigModel item : children) {
                DsRelationConfig relationConfig = item.getRelationConfig();
                List<DsConfigFields> fieldItem = item.getFieldList();
                String codeByType = DsJoinTypeEnum.getCodeByType(relationConfig.getType());
                childBuilder.append(SIGN_BLANK);
                childBuilder.append(codeByType);
                StringBuilder itemBuilder = new StringBuilder();
                DataSetConfigUtil.recurAssSql(item, itemBuilder, dsParamModel);
                if (CollectionUtils.isNotEmpty(item.getChildren())) {
                    childBuilder.append(String.format(SELECT_UPPER, itemBuilder, item.getTableAlias()));
                } else {
                    childBuilder.append(itemBuilder);
                }
                //关联关系拼接-必须有关联关系
                List<DsRelationModel> relationList = relationConfig.getRelationList();
                childBuilder.append(SIGN_ON);
                StringJoiner relationJoiner = new StringJoiner(SIGN_AND);
                for (DsRelationModel relationModel : relationList) {
                    DsConfigFields pFields = fieldList.stream().filter(t -> t.getField().equals(relationModel.getPField())).findFirst().orElse(null);
                    DsConfigFields cFields = fieldItem.stream().filter(t -> t.getField().equals(relationModel.getField())).findFirst().orElse(null);
                    relationJoiner.add(pFields.getFieldAlias() + SIGN_EQUALS + cFields.getFieldAlias());
                }
                childBuilder.append(relationJoiner);
            }
            //拼接所有表的关联关系
            StringJoiner relationWhereJoiner = new StringJoiner(SIGN_AND);
            for (DsConfigModel item : children) {
                DsRelationConfig relationConfig = item.getRelationConfig();
                //连表条件拼接
                DsConfigModel relationModel = new DsConfigModel();
                relationModel.setTable(table);
                relationModel.setFieldList(fieldList);
                relationModel.setChildren(children);
                relationModel.setMatchLogic(relationConfig.getMatchLogic());
                relationModel.setRuleList(relationConfig.getRuleList());
                String relationWhere = DataSetConfigUtil.recurAssWhere(relationModel, dsParamModel, true);
                if (StringUtil.isNotEmpty(relationWhere)) {
                    relationWhereJoiner.add(relationWhere);
                }
            }
            if (relationWhereJoiner.length() > 0) {
                childBuilder.append(SIGN_WHERE);
                childBuilder.append(relationWhereJoiner);
            }
            result.append(String.format(SELECT_FROM, allFields, childBuilder));
        } else {
            result.append(tableSelect);
        }

    }

    /**
     * 组装where条件
     *
     * @param configModel
     * @param dsParamModel
     * @param fieldAlias
     * @return
     */
    public static String recurAssWhere(DsConfigModel configModel, DsParamModel dsParamModel, boolean fieldAlias) {
        List<DsConfigFields> fieldList = configModel.getFieldList();
        Map<String, List<DsConfigFields>> fieldsMap = new HashMap<>();
        fieldsMap.put(configModel.getTable(), fieldList);
        List<DsConfigModel> children = configModel.getChildren();
        for (DsConfigModel child : children) {
            fieldsMap.put(child.getTable(), child.getFieldList());
        }
        String matchLogic = SearchMethodEnum.And.getSymbol().equals(configModel.getMatchLogic()) ? SIGN_AND : SIGN_OR;
        List<SuperQueryJsonModel> ruleList = configModel.getRuleList();
        StringJoiner matchJoiner = new StringJoiner(matchLogic);
        int m = 0;
        if (CollectionUtils.isNotEmpty(ruleList)) {
            for (SuperQueryJsonModel ruleModel : ruleList) {
                String logic = SearchMethodEnum.And.getSymbol().equals(ruleModel.getLogic()) ? SIGN_AND : SIGN_OR;
                StringJoiner logicJoiner = new StringJoiner(logic);
                List<FieLdsModel> groups = ruleModel.getGroups();
                int n = 0;
                for (FieLdsModel fieLdsModel : groups) {
                    DsConfigFields configFields = null;
                    String thisField;
                    if (fieLdsModel.getField().contains(SIGN_BAR)) {
                        String thisTable = fieLdsModel.getField().split(SIGN_BAR)[0];
                        thisField = fieLdsModel.getField().split(SIGN_BAR)[1];
                        List<DsConfigFields> dsConfigFields = fieldsMap.get(thisTable);
                        if (CollectionUtils.isNotEmpty(dsConfigFields)) {
                            configFields = dsConfigFields.stream().filter(t -> t.getField().equals(thisField)).findFirst().orElse(null);
                        }
                    } else {
                        thisField = fieLdsModel.getField();
                        configFields = fieldList.stream().filter(t -> t.getField().equals(thisField)).findFirst().orElse(null);
                    }
                    if (configFields != null && StringUtil.isNotEmpty(configFields.getFieldAlias())) {
                        fieLdsModel.setFieldAlias(configFields.getFieldAlias());
                    } else {
                        fieLdsModel.setFieldAlias(thisField);
                    }
                    String condition = getCondition(fieLdsModel, dsParamModel, fieldAlias);
                    logicJoiner.add(condition);
                    n++;
                }
                if (logicJoiner.length() > 0) {
                    String logicSql = logicJoiner.toString();
                    if (n > 1) {
                        logicSql = String.format(SELECT_BRACKET, logicSql);
                    }
                    matchJoiner.add(logicSql);
                    m++;
                }

            }
        }
        if (matchJoiner.length() > 0) {
            String matchSql = matchJoiner.toString();
            if (m > 1) {
                matchSql = String.format(SELECT_BRACKET, matchSql);
            }
            return matchSql;
        }
        return "";
    }

    /**
     * 拼接条件
     *
     * @param fieLdsModel
     * @param dsParamModel
     * @param fieldAlias
     * @return
     */
    public static String getCondition(FieLdsModel fieLdsModel, DsParamModel dsParamModel, boolean fieldAlias) {
        List<Object> values = dsParamModel.getValues();
        String dbType = dsParamModel.getDbType();
        boolean isOracleOrPostgre = DbBase.ORACLE.equalsIgnoreCase(dbType) || DbBase.POSTGRE_SQL.equalsIgnoreCase(dbType);
        boolean isSqlServer = DbBase.SQL_SERVER.equalsIgnoreCase(dbType);
        String field = fieldAlias ? fieLdsModel.getFieldAlias() : fieLdsModel.getField();
        String dataType = fieLdsModel.getDataType();
        SearchMethodEnum symbol = SearchMethodEnum.getSearchMethod(fieLdsModel.getSymbol());

        String mark = SIGN_QUESTION;
        if (DsKeyConst.DateSelect.contains(dataType) && isOracleOrPostgre) {
            mark = SIGN_TAMESTAMP;
        }
        //获取条件值
        swapValue(fieLdsModel, dsParamModel);
        Object fieldValueOne = fieLdsModel.getFieldValueOne();
        Object fieldValueTwo = fieLdsModel.getFieldValueTwo();
        if (isSqlServer && fieldValueOne instanceof String) {
            fieldValueOne = String.valueOf(fieldValueOne).replaceAll("\\[", "[[]");
        }

        StringBuilder sqlBuilder = new StringBuilder();
        switch (symbol) {
            case IsNull:
                sqlBuilder.append(field + SIGN_NULL);
                break;
            case IsNotNull:
                sqlBuilder.append(field + SIGN_NOT_NULL);
                break;
            case Equal:
                sqlBuilder.append(field + SIGN_EQUALS + mark);
                values.add(fieldValueOne);
                break;
            case NotEqual:
                sqlBuilder.append(field + SIGN_NEQ + mark);
                values.add(fieldValueOne);
                break;
            case GreaterThan:
                sqlBuilder.append(field + SIGN_GREATER + mark);
                values.add(fieldValueOne);
                break;
            case LessThan:
                sqlBuilder.append(field + SIGN_LESS + mark);
                values.add(fieldValueOne);
                break;
            case GreaterThanOrEqual:
                sqlBuilder.append(field + SIGN_GREATER_EQ + mark);
                values.add(fieldValueOne);
                break;
            case LessThanOrEqual:
                sqlBuilder.append(field + SIGN_LESS_EQ + mark);
                values.add(fieldValueOne);
                break;
            case Like:
                sqlBuilder.append(field + SIGN_LIKE + mark);
                values.add(SIGN_PER + fieldValueOne + SIGN_PER);
                break;
            case NotLike:
                sqlBuilder.append(field + SIGN_NOT_LIKE + mark);
                values.add(SIGN_PER + fieldValueOne + SIGN_PER);
                break;
            case Between:
                sqlBuilder.append(field + SIGN_BETWEEN + mark + SIGN_AND + mark);
                values.add(fieldValueOne);
                values.add(fieldValueTwo);
                break;
            case Included:
            case NotIncluded:
                List<String> dataList = new ArrayList<>();
                if (fieldValueOne == null || StringUtil.isEmpty(fieldValueOne.toString())) {
                    dataList.add(SIGN_JNPFNULLLIST);
                } else if (fieldValueOne instanceof List) {
                    dataList.addAll((List) fieldValueOne);
                } else {
                    try {
                        dataList.addAll(JsonUtil.getJsonToList(fieldValueOne.toString(), String.class));
                    } catch (Exception e) {
                        dataList.add(fieldValueOne.toString());
                    }
                }
                StringJoiner included = new StringJoiner(SIGN_OR);
                StringJoiner notIncluded = new StringJoiner(SIGN_AND);
                for (String s : dataList) {
                    if (symbol == SearchMethodEnum.Included) {
                        included.add(field + SIGN_LIKE + mark);
                        values.add(SIGN_PER + s + SIGN_PER);
                    } else {
                        notIncluded.add(field + SIGN_NOT_LIKE + mark);
                        values.add(SIGN_PER + s + SIGN_PER);
                    }
                }
                if (SearchMethodEnum.Included.equals(symbol)){
                    sqlBuilder.append(String.format(SELECT_BRACKET, included));
                }else {
                    sqlBuilder.append(String.format(SELECT_BRACKET, notIncluded));
                }

                break;
        }
        return sqlBuilder.toString();
    }

    /**
     * 值转换
     *
     * @param fieLdsModel
     * @param dsParamModel
     */
    public static void swapValue(FieLdsModel fieLdsModel, DsParamModel dsParamModel) {
        Map<String, String> systemParam = dsParamModel.getSystemParam();
        String fieldValue = fieLdsModel.getFieldValue();
        String dataType = fieLdsModel.getDataType();

        String valueOne = null;
        String valueTwo = null;
        Object fieldValueOne = fieldValue;
        Object fieldValueTwo = fieldValue;
        String fieldValueType = fieLdsModel.getFieldValueType();
        List<String> data = new ArrayList<>();
        if (DsKeyConst.BetweenSelect.contains(dataType)) {
            try {
                data.addAll(JsonUtil.getJsonToList(fieldValue, String.class));
                if (data.size() == 0) return;
                if (data.size() == 1) data.add(data.get(0));
            } catch (Exception e) {
                data.add(fieldValue);
                data.add(fieldValue);
            }
            valueOne = data.get(0);
            valueTwo = data.get(1);
        }
        switch (dataType) {
            case DsKeyConst.DOUBLE:
                fieldValueOne = valueOne != null ? new Double(valueOne) : valueOne;
                fieldValueTwo = valueTwo != null ? new Double(valueTwo) : valueTwo;
                break;
            case DsKeyConst.BIGINT:
                fieldValueOne = valueOne != null ? new Long(valueOne) : valueOne;
                fieldValueTwo = valueTwo != null ? new Long(valueTwo) : valueTwo;
                break;
            case DsKeyConst.DATE:
            case DsKeyConst.TIME:
                if (valueOne != null) {
                    fieldValueOne = DateUtil.dateFormat(new Date(Long.valueOf(valueOne)));
                }

                if (valueTwo != null) {
                    fieldValueTwo = DateUtil.dateFormat(new Date(Long.valueOf(valueTwo)));
                }
                break;
            default:
                //TEXT 1-自定义，2-系统参数
                if (Objects.equals(fieldValueType, "2")) {
                    fieldValueOne = systemParam.get(fieldValue);
                }
                break;
        }
        fieLdsModel.setFieldValueOne(fieldValueOne);
        fieLdsModel.setFieldValueTwo(fieldValueTwo);
    }

    /**
     * asc码获取字母
     *
     * @param letterNum
     * @return
     */
    public static String getLetter(int letterNum) {
        char letter = (char) (65 + letterNum);
        return String.valueOf(letter);
    }
}