From 64decfa5272a6b068c3afb6ea5f071a0fdbb9b21 Mon Sep 17 00:00:00 2001
From: ludc
Date: 星期五, 16 六月 2023 17:50:46 +0800
Subject: [PATCH] 修改bug，添加高级查询SQL拼接方法
---
Source/UBCS-WEB/.gitignore | 2
Source/UBCS/ubcs-service-api/ubcs-util-api/src/main/java/com/vci/ubcs/starter/util/UBCSSqlKeyword.java | 7 +
Source/UBCS/ubcs-service/ubcs-code/src/main/java/com/vci/ubcs/code/service/impl/MdmEngineServiceImpl.java | 102 ++++++++++++++++++++++++++++++---
Source/UBCS/ubcs-service/ubcs-code/src/main/java/com/vci/ubcs/code/service/impl/CodeClassifyTemplateAttrServiceImpl.java | 21 +-----
4 files changed, 101 insertions(+), 31 deletions(-)
diff --git a/Source/UBCS-WEB/.gitignore b/Source/UBCS-WEB/.gitignore
index 07c8fe4..fdd7d45 100644
--- a/Source/UBCS-WEB/.gitignore
+++ b/Source/UBCS-WEB/.gitignore
@@ -1,6 +1,6 @@
.DS_Store
node_modules
-/dist
+# /dist
/tests/e2e/videos/
/tests/e2e/screenshots/
diff --git a/Source/UBCS/ubcs-service-api/ubcs-util-api/src/main/java/com/vci/ubcs/starter/util/UBCSSqlKeyword.java b/Source/UBCS/ubcs-service-api/ubcs-util-api/src/main/java/com/vci/ubcs/starter/util/UBCSSqlKeyword.java
index e9e9392..71ae42e 100644
--- a/Source/UBCS/ubcs-service-api/ubcs-util-api/src/main/java/com/vci/ubcs/starter/util/UBCSSqlKeyword.java
+++ b/Source/UBCS/ubcs-service-api/ubcs-util-api/src/main/java/com/vci/ubcs/starter/util/UBCSSqlKeyword.java
@@ -2,6 +2,7 @@
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.github.yulichang.wrapper.MPJLambdaWrapper;
+import com.vci.ubcs.starter.web.util.VciDateUtil;
import org.springblade.core.tool.utils.DateUtil;
import org.springblade.core.tool.utils.Func;
import org.springblade.core.tool.utils.StringUtil;
@@ -35,6 +36,7 @@
private static final String IS_NULL = "_null";
private static final String NOT_NULL = "_notnull";
private static final String IGNORE = "_ignore";
+
public UBCSSqlKeyword() {
}
@@ -234,13 +236,14 @@
* @param keyword
* @return
*/
- private static String getColumn(String column, String keyword) {
+ public static String getColumn(String column, String keyword) {
//娌℃湁杞笅鍒掔嚎鏄犲皠
- return StringUtil.removeSuffix(column, keyword).toLowerCase(Locale.ROOT);
+ return StringUtil.removeSuffix(column, keyword).toLowerCase(Locale.ROOT).trim();
}
public static String filter(String param) {
return param == null ? null : param.replaceAll("(?i)'|%|--|insert|delete|select|count|group|union|drop|truncate|alter|grant|execute|exec|xp_cmdshell|call|declare|sql", "");
}
+
}
diff --git a/Source/UBCS/ubcs-service/ubcs-code/src/main/java/com/vci/ubcs/code/service/impl/CodeClassifyTemplateAttrServiceImpl.java b/Source/UBCS/ubcs-service/ubcs-code/src/main/java/com/vci/ubcs/code/service/impl/CodeClassifyTemplateAttrServiceImpl.java
index fe590ad..68256fb 100644
--- a/Source/UBCS/ubcs-service/ubcs-code/src/main/java/com/vci/ubcs/code/service/impl/CodeClassifyTemplateAttrServiceImpl.java
+++ b/Source/UBCS/ubcs-service/ubcs-code/src/main/java/com/vci/ubcs/code/service/impl/CodeClassifyTemplateAttrServiceImpl.java
@@ -69,11 +69,6 @@
@Resource
private CodeClassifyTemplateAttrMapper codeClassifyTemplateAttrMapper;
- // @Resource
- // CodeClstempattrMapper codeClstempattrMapper;
- // @Resource
- // ICodeTempphaseService codeTempphaseService;
-
@Resource
IDictBizClient iDictBizClient;
@@ -723,8 +718,8 @@
}
//杩欎釜妯℃澘涓嬪凡缁忔湁鐨勫睘鎬�
-// VciQueryWrapperForDO queryWrapper = new VciQueryWrapperForDO(CodeClassifyTemplateAttrDO.class);
-// queryWrapper.addQueryMap("CLASSIFYTEMPLATEOID",templateAttrOid);
+ // VciQueryWrapperForDO queryWrapper = new VciQueryWrapperForDO(CodeClassifyTemplateAttrDO.class);
+ // queryWrapper.addQueryMap("CLASSIFYTEMPLATEOID",templateAttrOid);
Map<String,Object> condition = new HashMap<>(1);
condition.put("CLASSIFYTEMPLATEOID",templateAttrOid);
List<CodeClassifyTemplateAttr> codeClassifyTemplateAttrDOList = baseMapper.selectByMap(condition);
@@ -779,21 +774,13 @@
CodeClassifyTemplate codeClassifyTemplateDO = codeClassifyTemplateMapper.selectById(templateAttrOid);
//杩欎釜涓氬姟绫诲瀷涓嬬殑鎵�鏈夊睘鎬�
- // TODO 寰呭畬鍠�
- List<BtmTypeAttributeVO> boAttrs = null; // (List<BtmTypeAttributeVO>) attributeClient.selectById(codeClassifyTemplateDO.getBtmTypeId());//this.btmService.listAttributeByBtmIdHasDefault(codeClassifyTemplateDO.getBtmTypeId());
-// codeOsbtmtypeattributeMapper.selectById(codeClassifyTemplateDO.getBtmTypeId());
-// BeanUtils.copyProperties(codeOsbtmtypeattributeMapper.selectById(codeClassifyTemplateDO.getBtmTypeId()),boAttrs);
+ // TODO 宸插畬鍠�
+ List<BtmTypeAttributeVO> boAttrs = (List<BtmTypeAttributeVO>) btmTypeClient.getAllAttributeByBtmId(codeClassifyTemplateDO.getBtmTypeId());
//杩欎釜妯℃澘涓嬪凡缁忔湁鐨勫睘鎬�
//鎶婇粯璁ょ殑灞炴�т篃娣诲姞鍒癰oAttrs
if(boAttrs == null){
boAttrs = new ArrayList<>();
}
-// VciQueryWrapperForDO queryWrapper = new VciQueryWrapperForDO(CodeClassifyTemplateAttrDO.class);
-// queryWrapper.addQueryMap("CLASSIFYTEMPLATEOID",templateAttrOid);
-
-// Map<String,Object> condition = new HashMap<>(1);
-// condition.put("CLASSIFYTEMPLATEOID",templateAttrOid);
-
QueryWrapper<CodeClassifyTemplateAttr> wrapper=new QueryWrapper<>();
wrapper.eq("CLASSIFYTEMPLATEOID",templateAttrOid);
diff --git a/Source/UBCS/ubcs-service/ubcs-code/src/main/java/com/vci/ubcs/code/service/impl/MdmEngineServiceImpl.java b/Source/UBCS/ubcs-service/ubcs-code/src/main/java/com/vci/ubcs/code/service/impl/MdmEngineServiceImpl.java
index fc99a8c..c4d9f6d 100644
--- a/Source/UBCS/ubcs-service/ubcs-code/src/main/java/com/vci/ubcs/code/service/impl/MdmEngineServiceImpl.java
+++ b/Source/UBCS/ubcs-service/ubcs-code/src/main/java/com/vci/ubcs/code/service/impl/MdmEngineServiceImpl.java
@@ -18,6 +18,7 @@
import com.vci.ubcs.code.mapper.CodeWupinMapper;
import com.vci.ubcs.code.mapper.CommonsMapper;
import com.vci.ubcs.code.service.*;
+import com.vci.ubcs.code.util.ClientBusinessObject;
import com.vci.ubcs.code.vo.CodeKeyAttrRepeatVO;
import com.vci.ubcs.code.vo.pagemodel.UITableFieldVO;
import com.vci.ubcs.code.vo.pagemodel.UITablePageVO;
@@ -36,6 +37,7 @@
import com.vci.ubcs.starter.revision.model.TreeQueryObject;
import com.vci.ubcs.starter.revision.model.TreeWrapperOptions;
import com.vci.ubcs.starter.revision.service.RevisionModelUtil;
+import com.vci.ubcs.starter.util.UBCSSqlKeyword;
import com.vci.ubcs.starter.web.constant.QueryOptionConstant;
import com.vci.ubcs.starter.web.constant.RegExpConstant;
import com.vci.ubcs.starter.web.enumpck.BooleanEnum;
@@ -60,6 +62,8 @@
import org.springblade.core.log.exception.ServiceException;
import org.springblade.core.secure.utils.AuthUtil;
import org.springblade.core.tool.api.R;
+import org.springblade.core.tool.utils.DateUtil;
+import org.springblade.core.tool.utils.Func;
import org.springblade.core.tool.utils.StringPool;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.cache.Cache;
@@ -326,6 +330,7 @@
}
return comboboxKVs;
}
+
/**
* 淇敼鐘舵��
*
@@ -1711,10 +1716,10 @@
});
andCondtionMap.forEach((k, v) -> {
- andSql.add(getConditionSql(k.toLowerCase(), v, referFieldMap, attrVOMap));
+ andSql.add(getConditionSql(k.toLowerCase(), v, referFieldMap, attrVOMap,btmType));
});
orConditionMap.forEach((k, v) -> {
- orSql.add(getConditionSql(k.toLowerCase(), v, referFieldMap, attrVOMap));
+ orSql.add(getConditionSql(k.toLowerCase(), v, referFieldMap, attrVOMap,btmType));
});
}
//缁勫悎璧锋潵
@@ -1786,7 +1791,65 @@
* @param attrVOMap 灞炴�х殑鏄犲皠
* @return Sql璇彞
*/
- private String getConditionSql(String key, String value, Map<String/**鍙傜収鐨勫睘鎬�**/, String/**瀹為檯鐨勫睘鎬�**/> referFieldMap, Map<String, CodeClassifyTemplateAttrVO> attrVOMap) {
+ private String getConditionSql(String key, String value, Map<String/**鍙傜収鐨勫睘鎬�**/, String/**瀹為檯鐨勫睘鎬�**/> referFieldMap, Map<String, CodeClassifyTemplateAttrVO> attrVOMap,String btmType) {
+ /*if (!Func.hasEmpty(new Object[]{key,value}) && !key.endsWith("_ignore")) {
+ if (key.endsWith("_like")) {
+ String field = UBCSSqlKeyword.getColumn(key, "_like");
+ if (referFieldMap.containsKey(field)) {
+ //璇存槑杩樻槸鍙傜収閲岄潰鐨勶紝鎴戜滑榛樿杩欑鎯呭喌涓嬮兘鏄瓧绗︿覆鍚э紝鍥犱负鍙傜収鐨勫睘鎬т笉涓�瀹氱敤鐨勫钩鍙扮殑灞炴�ф睜閲岀殑锛屾墍浠ュぇ閮ㄥ垎鎯呭喌涓嬶紝鏄剧ず鐨勫睘鎬ч兘鏄瓧绗︿覆鍚�
+ return referFieldMap.get(field) + SPACE + "LIKE" + SPACE + "concat(%,"+"concat('" + value + "'" + SPACE+",%)";
+ } else {
+ return (field.contains(".") ? "" : "t.") + field + SPACE + " <= " + getStringValueInWhere(field, value, attrVOMap);
+ }
+ } else if (key.endsWith("_notequal")) {
+ qw.ne(UBCSSqlKeyword.getColumn(key, "_notequal"), v);
+ } else if (key.endsWith("_likeleft")) {
+ qw.likeLeft(UBCSSqlKeyword.getColumn(key, "_likeleft"), v);
+ } else if (key.endsWith("_likeright")) {
+ qw.likeRight(UBCSSqlKeyword.getColumn(key, "_likeright"), v);
+ } else if (key.endsWith("_notlike")) {
+ qw.notLike(UBCSSqlKeyword.getColumn(key, "_notlike"), v);
+ } else if (key.endsWith("_ge")) {
+ String field = UBCSSqlKeyword.getColumn(key, "_ge");
+ if (referFieldMap.containsKey(field)) {
+ //璇存槑杩樻槸鍙傜収閲岄潰鐨勶紝鎴戜滑榛樿杩欑鎯呭喌涓嬮兘鏄瓧绗︿覆鍚э紝鍥犱负鍙傜収鐨勫睘鎬т笉涓�瀹氱敤鐨勫钩鍙扮殑灞炴�ф睜閲岀殑锛屾墍浠ュぇ閮ㄥ垎鎯呭喌涓嬶紝鏄剧ず鐨勫睘鎬ч兘鏄瓧绗︿覆鍚�
+ return referFieldMap.get(field) + SPACE + " >= '" + value + "'" + SPACE;
+ } else {
+ return (field.contains(".") ? "" : "t.") + field + SPACE + " >= " + getStringValueInWhere(value, field, attrVOMap);
+ }
+ } else if (key.endsWith("_le")) {
+ //璇存槑鏄�<=鐨勩�傛垜浠渶瑕佸厛鑾峰彇涓�涓�
+ String field = UBCSSqlKeyword.getColumn(key, "_le");
+ if (referFieldMap.containsKey(field)) {
+ //璇存槑杩樻槸鍙傜収閲岄潰鐨勶紝鎴戜滑榛樿杩欑鎯呭喌涓嬮兘鏄瓧绗︿覆鍚э紝鍥犱负鍙傜収鐨勫睘鎬т笉涓�瀹氱敤鐨勫钩鍙扮殑灞炴�ф睜閲岀殑锛屾墍浠ュぇ閮ㄥ垎鎯呭喌涓嬶紝鏄剧ず鐨勫睘鎬ч兘鏄瓧绗︿覆鍚�
+ return referFieldMap.get(field) + SPACE + " <= '" + value + "'" + SPACE;
+ } else {
+ return (field.contains(".") ? "" : "t.") + field + SPACE + " <= " + getStringValueInWhere(field, value, attrVOMap);
+ }
+ } else if (key.endsWith("_gt")) {
+ qw.gt(UBCSSqlKeyword.getColumn(key, "_gt"), v);
+ } else if (key.endsWith("_lt")) {
+ qw.lt(UBCSSqlKeyword.getColumn(key, "_lt"), v);
+ } else if (key.endsWith("_datege")) {
+ qw.ge(UBCSSqlKeyword.getColumn(key, "_datege"), DateUtil.parse(String.valueOf(v), "yyyy-MM-dd HH:mm:ss"));
+ } else if (key.endsWith("_dategt")) {
+ qw.gt(UBCSSqlKeyword.getColumn(key, "_dategt"), DateUtil.parse(String.valueOf(v), "yyyy-MM-dd HH:mm:ss"));
+ } else if (key.endsWith("_dateequal")) {
+ qw.eq(UBCSSqlKeyword.getColumn(key, "_dateequal"), DateUtil.parse(String.valueOf(v), "yyyy-MM-dd HH:mm:ss"));
+ } else if (key.endsWith("_datele")) {
+ qw.le(UBCSSqlKeyword.getColumn(key, "_datele"), DateUtil.parse(String.valueOf(v), "yyyy-MM-dd HH:mm:ss"));
+ } else if (key.endsWith("_datelt")) {
+ qw.lt(UBCSSqlKeyword.getColumn(key, "_datelt"), DateUtil.parse(String.valueOf(v), "yyyy-MM-dd HH:mm:ss"));
+ } else if (key.endsWith("_null")) {
+ qw.isNull(UBCSSqlKeyword.getColumn(key, "_null"));
+ } else if (key.endsWith("_notnull")) {
+ qw.isNotNull(UBCSSqlKeyword.getColumn(key, "_notnull"));
+ } else {
+ qw.eq(UBCSSqlKeyword.getColumn(key, "_equal"), v);
+ }
+
+ }*/
+
if (key.endsWith("_begin")) {
//璇存槑鏄�>=鐨勩�傛垜浠渶瑕佸厛鑾峰彇涓�涓�
String field = (key.substring(0, key.length() - 6).toLowerCase().trim());
@@ -1809,12 +1872,11 @@
if (referFieldMap.containsKey(key)) {
//璇存槑鏄弬鐓х殑锛屾垜浠弬鐓х殑鏌ヨ閮借涓烘槸瀛楃涓诧紝濡傛灉鏄椂闂存牸寮忕殑鏌ヨ鑲畾鏈夐棶棰橈紝
String selectKey = referFieldMap.get(key);
- return getSqlByValue(selectKey, value, null);
+ return getSqlByValue(selectKey, value, null,btmType);
} else {
- return getSqlByValue(key, value, attrVOMap);
+ return getSqlByValue(key, value, attrVOMap,btmType);
}
}
-
}
/**
@@ -1873,6 +1935,24 @@
}
/**
+ * 鏌ョ湅鏄惁榛樿灞炴��
+ * @param selectKey
+ * @param btmType
+ * @return
+ */
+ private boolean isDefaultAttr(String selectKey, String btmType){
+ if(StringUtils.isBlank(btmType)){
+ return false;
+ }
+ R<BtmTypeVO> r = btmTypeClient.getDefaultAttrByBtmId(btmType);
+ List<BtmTypeAttributeVO> attributes = r.getData().getAttributes();
+ if(r.getCode() != 200 || attributes.isEmpty()){
+ return false;
+ }
+ return attributes.stream().anyMatch(item->item.getId().equals(selectKey));
+ }
+
+ /**
* 灏佽鏈�缁堢殑sql璇彞涓殑鍊奸儴鍒�
*
* @param selectKey 鏌ヨ鐨勫瓧娈�
@@ -1880,12 +1960,12 @@
* @param attrVOMap 灞炴�х殑鏄剧ず瀵硅薄鏄犲皠
* @return sql閲岀殑鍊�
*/
- private String getSqlByValue(String selectKey, String value, Map<String, CodeClassifyTemplateAttrVO> attrVOMap) {
+ private String getSqlByValue(String selectKey, String value, Map<String, CodeClassifyTemplateAttrVO> attrVOMap,String btmType) {
StringBuilder sql = new StringBuilder();
- // TODO 寰呭畬鍠�
- // if (!selectKey.contains(".") && (attrVOMap.containsKey(selectKey.toLowerCase(Locale.ROOT)) || attributeService.isDefaultAttr(selectKey) || selectKey.matches(RegExpConstant.LETTER))) {
- // sql.append("t.");
- // }
+ // TODO 宸插畬鍠�
+ if (!selectKey.contains(".") && (attrVOMap.containsKey(selectKey.toLowerCase(Locale.ROOT)) || isDefaultAttr(selectKey,btmType) || selectKey.matches(RegExpConstant.LETTER))) {
+ sql.append("t.");
+ }
if (value.startsWith(QueryOptionConstant.IN)) {
sql.append(selectKey)
.append(SPACE)
--
Gitblit v1.9.3