From 19871e8ac9606710b1009f5f86366fb445a7ace8 Mon Sep 17 00:00:00 2001
From: heheng <475597332@qq.com>
Date: 星期三, 09 七月 2025 16:06:20 +0800
Subject: [PATCH] 优化改造sql
---
multi-system/src/main/java/com/gkhy/exam/system/mapper/SysDeptMapper.java | 8 ++
multi-system/src/main/java/com/gkhy/exam/system/service/impl/SysDeptServiceImpl.java | 100 ++++++++++++++++++---------------
multi-system/src/main/java/com/gkhy/exam/system/service/impl/StandardizedTemplateServiceImpl.java | 2
multi-system/src/main/resources/mapper/system/SysDeptMapper.xml | 39 ++++++-------
multi-system/src/main/java/com/gkhy/exam/system/service/ISysDeptService.java | 5 +
multi-admin/src/main/java/com/gkhy/exam/admin/controller/system/SysDeptController.java | 2
multi-system/src/main/java/com/gkhy/exam/system/domain/vo/SysDeptSaveDTOReq.java | 5 +
7 files changed, 89 insertions(+), 72 deletions(-)
diff --git a/multi-admin/src/main/java/com/gkhy/exam/admin/controller/system/SysDeptController.java b/multi-admin/src/main/java/com/gkhy/exam/admin/controller/system/SysDeptController.java
index 45f5d0c..351199a 100644
--- a/multi-admin/src/main/java/com/gkhy/exam/admin/controller/system/SysDeptController.java
+++ b/multi-admin/src/main/java/com/gkhy/exam/admin/controller/system/SysDeptController.java
@@ -81,7 +81,7 @@
@ApiOperation(value = "获取部门列表树状")
public CommonResult treeList(SysDept dept)
{
- List<SysDept> treeSelects = deptService.selectDeptTreeList(dept);
+ List<TreeSelect> treeSelects = deptService.selectDeptTreeList(dept);
return CommonResult.success(treeSelects);
}
diff --git a/multi-system/src/main/java/com/gkhy/exam/system/domain/vo/SysDeptSaveDTOReq.java b/multi-system/src/main/java/com/gkhy/exam/system/domain/vo/SysDeptSaveDTOReq.java
index 8def8ad..cacf0a8 100644
--- a/multi-system/src/main/java/com/gkhy/exam/system/domain/vo/SysDeptSaveDTOReq.java
+++ b/multi-system/src/main/java/com/gkhy/exam/system/domain/vo/SysDeptSaveDTOReq.java
@@ -17,6 +17,7 @@
/** 父部门ID */
@ApiModelProperty("主要负责部门ID无就传0")
+ @NotNull(message = "父部门ID不能为空")
private Long parentId;
/** 祖级列表 */
@@ -51,7 +52,7 @@
@NotBlank(message = "是否智能分配不能为空")
private String responsType;
- @ApiModelProperty("负责部门id")
- private List<Long> childDeptIds;
+// @ApiModelProperty("负责部门id")
+// private List<Long> childDeptIds;
}
diff --git a/multi-system/src/main/java/com/gkhy/exam/system/mapper/SysDeptMapper.java b/multi-system/src/main/java/com/gkhy/exam/system/mapper/SysDeptMapper.java
index dbbfbf4..50fd0f7 100644
--- a/multi-system/src/main/java/com/gkhy/exam/system/mapper/SysDeptMapper.java
+++ b/multi-system/src/main/java/com/gkhy/exam/system/mapper/SysDeptMapper.java
@@ -63,6 +63,14 @@
public List<SysDept> selectChildrenDeptById(Long deptId);
/**
+ * 过滤掉本身和子级部门
+ * @param deptId
+ * @return
+ */
+
+ List<SysDept> getParentInfo(Long deptId);
+
+ /**
* 根据ID查询所有子部门（正常状态）
*
* @param deptId 部门ID
diff --git a/multi-system/src/main/java/com/gkhy/exam/system/service/ISysDeptService.java b/multi-system/src/main/java/com/gkhy/exam/system/service/ISysDeptService.java
index 5563f66..5d0eb69 100644
--- a/multi-system/src/main/java/com/gkhy/exam/system/service/ISysDeptService.java
+++ b/multi-system/src/main/java/com/gkhy/exam/system/service/ISysDeptService.java
@@ -36,7 +36,10 @@
* @param dept 部门信息
* @return 部门树信息集合
*/
- public List<SysDept> selectDeptTreeList(SysDept dept);
+ public List<TreeSelect> selectDeptTreeList(SysDept dept);
+
+
+ List<SysDept> getParentInfo(Long deptId);
/**
* 构建前端所需要树结构
diff --git a/multi-system/src/main/java/com/gkhy/exam/system/service/impl/StandardizedTemplateServiceImpl.java b/multi-system/src/main/java/com/gkhy/exam/system/service/impl/StandardizedTemplateServiceImpl.java
index 1340450..cc57162 100644
--- a/multi-system/src/main/java/com/gkhy/exam/system/service/impl/StandardizedTemplateServiceImpl.java
+++ b/multi-system/src/main/java/com/gkhy/exam/system/service/impl/StandardizedTemplateServiceImpl.java
@@ -173,7 +173,7 @@
//部门
SysDept sysDept = new SysDept();
sysDept.setCompanyId(companyId.longValue());
- List<SysDept> treeSelects = iSysDeptService.selectDeptTreeList(sysDept);
+ List<TreeSelect> treeSelects = iSysDeptService.selectDeptTreeList(sysDept);
//公司概况
List<CompanySummary> companySummaries = companySummaryMapper.selectCompanySummaryList(companyId);
diff --git a/multi-system/src/main/java/com/gkhy/exam/system/service/impl/SysDeptServiceImpl.java b/multi-system/src/main/java/com/gkhy/exam/system/service/impl/SysDeptServiceImpl.java
index 8f934d1..f9ccb38 100644
--- a/multi-system/src/main/java/com/gkhy/exam/system/service/impl/SysDeptServiceImpl.java
+++ b/multi-system/src/main/java/com/gkhy/exam/system/service/impl/SysDeptServiceImpl.java
@@ -119,56 +119,30 @@
* @return 部门树信息集合
*/
@Override
- public List<SysDept> selectDeptTreeList(SysDept dept)
+ public List<TreeSelect> selectDeptTreeList(SysDept dept)
{
List<SysDept> depts = SpringUtils.getAopProxy(this).getOutDeptList(dept);
- List<SysDeptManage> allManage = deptMapper.getAllManage(dept.getCompanyId());
- // 构建父部门到子部门的映射
- Map<Long, List<Long>> parentToChildrenMap = new HashMap<>();
- for (SysDeptManage responsibility : allManage) {
- parentToChildrenMap.computeIfAbsent(responsibility.getDeptId(), k -> new ArrayList<>())
- .add(responsibility.getSubDeptId());
- }
-
- // 构建部门ID到部门对象的映射
- Map<Long, SysDept> deptMap = depts.stream()
- .collect(Collectors.toMap(SysDept::getDeptId, Function.identity()));
+// List<SysDeptManage> allManage = deptMapper.getAllManage(dept.getCompanyId());
+// // 构建父部门到子部门的映射
+// Map<Long, List<Long>> parentToChildrenMap = new HashMap<>();
+// for (SysDeptManage responsibility : allManage) {
+// parentToChildrenMap.computeIfAbsent(responsibility.getDeptId(), k -> new ArrayList<>())
+// .add(responsibility.getSubDeptId());
+// }
+//
+// // 构建部门ID到部门对象的映射
+// Map<Long, SysDept> deptMap = depts.stream()
+// .collect(Collectors.toMap(SysDept::getDeptId, Function.identity()));
- return buildDeptTree(depts,parentToChildrenMap, deptMap);
+ return buildDeptTreeSelect(depts);
}
- public List<SysDept> buildDeptTree(List<SysDept> allDepts, Map<Long, List<Long>> parentToChildrenMap, Map<Long, SysDept> deptMap) {
- List<SysDept> rootNodes = new ArrayList<>();
-
- for (SysDept dept : allDepts) {
- if (!deptMap.values().stream()
- .anyMatch(d -> parentToChildrenMap.containsValue(dept.getDeptId()))) {
- // 如果没有其他部门将它作为子部门，则为根节点
- rootNodes.add(dept);
- }
- }
-
- for (SysDept root : rootNodes) {
- buildChildren(root, parentToChildrenMap, deptMap);
- }
-
- return rootNodes;
+ @Override
+ public List<SysDept> getParentInfo(Long deptId) {
+ return deptMapper.getParentInfo(deptId);
}
-
- private void buildChildren(SysDept parent, Map<Long, List<Long>> parentToChildrenMap, Map<Long, SysDept> deptMap) {
- List<Long> childIds = parentToChildrenMap.getOrDefault(parent.getDeptId(), Collections.emptyList());
-
- List<SysDept> children = childIds.stream()
- .map(deptMap::get)
- .filter(Objects::nonNull)
- .peek(child -> buildChildren(child, parentToChildrenMap, deptMap))
- .collect(Collectors.toList());
-
- parent.setChildren(children);
- }
-
/**
* 构建前端所需要树结构
@@ -346,7 +320,7 @@
}
@Override
- @Transactional
+ @Transactional(rollbackFor = RuntimeException.class)
public int saveDept(SysDeptSaveDTOReq dept) {
// Long companyId = SecurityUtils.getCompanyId();
// if (!companyId.equals(dept.getCompanyId())){
@@ -358,11 +332,29 @@
if (!b){
throw new ApiException("部门名称已存在！");
}
+ SysDept newParentDept = deptMapper.selectDeptById(dept.getParentId());
+
int i = 0;
if (sysDept.getDeptId() == null){
+ if (ObjectUtil.isNotNull(newParentDept)){
+ String newAncestors = newParentDept.getAncestors() + "," + newParentDept.getDeptId();
+ dept.setAncestors(newAncestors);
+ }
sysDept.setCreateBy(SecurityUtils.getUsername());
i = deptMapper.insertDept(sysDept);
}else {
+ if (sysDept.getParentId().equals(sysDept.getDeptId())){
+ throw new ApiException("上级部门不能是自己！");
+ }
+
+ SysDept oldDept = deptMapper.selectDeptById(dept.getDeptId());
+ if (ObjectUtil.isNotNull(newParentDept) && ObjectUtil.isNotNull(oldDept))
+ {
+ String newAncestors = newParentDept.getAncestors() + "," + newParentDept.getDeptId();
+ String oldAncestors = oldDept.getAncestors();
+ dept.setAncestors(newAncestors);
+ updateDeptChildren(dept.getDeptId(), newAncestors, oldAncestors);
+ }
sysDept.setUpdateBy(SecurityUtils.getUsername());
sysDept.setUpdateTime(LocalDateTime.now());
i = deptMapper.updateDept(sysDept);
@@ -376,8 +368,6 @@
//处理条款
batchSaveCaluse(sysDept.getDeptId(), dept.getCompanyId(), dept.getCaluseVO1List());
- List<Long> childDeptIds = dept.getChildDeptIds();
- bacthInsertManager(sysDept.getDeptId(), childDeptIds, dept.getCompanyId());
}
return sysDept.getDeptId().byteValue();
@@ -451,7 +441,18 @@
List<DeptVo> deptVos = deptMapper.selectDeptList(sysDept);
if (deptVos != null && !deptVos.isEmpty()){
List<SysFunctionalDistribution> sysFunctionalDistributions = new ArrayList<>();
+ Map<String, CaluseVO1> clauseNumMap = new HashMap<>();
for (DeptVo deptVo : deptVos) {
+ List<CaluseVO1> caluseVO1List = deptVo.getCaluseVO1List();
+ if (ObjectUtil.isNotEmpty(caluseVO1List)){
+ // 转成 Map<String, CaluseVO1>，key 是 clauseNum
+ clauseNumMap = caluseVO1List.stream()
+ .collect(Collectors.toMap(
+ CaluseVO1::getClauseNum, // key
+ Function.identity(), // value
+ (existing, replacement) -> existing // 如果有重复 key，保留现有的值
+ ));
+ }
for (String s : DEPT_ROOT) {
SysFunctionalDistribution sysFunctionalDistribution = new SysFunctionalDistribution();
sysFunctionalDistribution.setDeptId(deptVo.getDeptId());
@@ -459,7 +460,14 @@
sysFunctionalDistribution.setCreateBy(SecurityUtils.getUsername());
sysFunctionalDistribution.setCreateTime(LocalDateTime.now());
sysFunctionalDistribution.setClauseNum(s);
- sysFunctionalDistribution.setChooseLab(0);
+ if (clauseNumMap.isEmpty()){
+ sysFunctionalDistribution.setChooseLab(0);
+ }else if (clauseNumMap.containsKey(s)){
+ sysFunctionalDistribution.setChooseLab(1);
+ }else {
+ sysFunctionalDistribution.setChooseLab(0);
+ }
+
sysFunctionalDistributions.add(sysFunctionalDistribution);
}
diff --git a/multi-system/src/main/resources/mapper/system/SysDeptMapper.xml b/multi-system/src/main/resources/mapper/system/SysDeptMapper.xml
index a3c6567..f39e55c 100644
--- a/multi-system/src/main/resources/mapper/system/SysDeptMapper.xml
+++ b/multi-system/src/main/resources/mapper/system/SysDeptMapper.xml
@@ -34,12 +34,6 @@
<result property="clauseId" column="clause_id" />
<result property="id" column="data_id" />
</collection>
- <collection ofType="com.gkhy.exam.system.domain.vo.SysDeptManageVo" property="sysDeptManageVoList">
- <result property="subDeptId" column="sub_dept_id" />
- <result property="subDeptName" column="sub_dept_name" />
- <result property="deptId" column="p_dept_id" />
-
- </collection>
</resultMap>
<sql id="selectDeptVo">
@@ -52,14 +46,11 @@
<select id="selectDeptList" parameterType="com.gkhy.exam.common.domain.entity.SysDept" resultMap="DeptVoResult">
select d.dept_id, d.parent_id, d.ancestors, d.dept_name, d.order_num, d.leader_user_id, d.company_id, d.status, d.del_flag, d.create_by, d.create_time,d.person_num,d.internal_auditors,
- d.order_num,d.responsibilities,d.dept_type,d.respons_type,u.name as leader_name,d2.dept_name as parent_name, dr.content ,dr.clause_num,dr.id as data_id,dr.clause_id,
- sm.sub_dept_id ,sm2.dept_name sub_dept_name,sm.dept_id as p_dept_id
+ d.order_num,d.responsibilities,d.dept_type,d.respons_type,u.name as leader_name,d2.dept_name as parent_name, dr.content ,dr.clause_num,dr.id as data_id,dr.clause_id
from sys_dept d
left join sys_user u on d.leader_user_id = u.id
left join sys_dept d2 on d.parent_id = d2.dept_id
left join sys_dept_responsibility dr on d.dept_id = dr.dept_id and dr.del_flag = '0' and data_type = 2
- left join sys_dept_manage sm on d.dept_id = sm.dept_id
- left join sys_dept sm2 on sm2.dept_id = sm.sub_dept_id
where d.del_flag = '0'
<if test="companyId != null and companyId != 0">
AND d.company_id = #{companyId}
@@ -91,12 +82,12 @@
<result property="clauseId" column="clause_id" />
<result property="id" column="id" />
</collection>
- <collection ofType="com.gkhy.exam.system.domain.vo.SysDeptManageVo" property="sysDeptManageVoList" column="deptId = dept_id" select="getSysDeptManageVo">
- <result property="subDeptId" column="sub_dept_id" />
- <result property="subDeptName" column="sub_dept_name" />
- <result property="deptId" column="dept_id" />
+<!-- <collection ofType="com.gkhy.exam.system.domain.vo.SysDeptManageVo" property="sysDeptManageVoList" column="deptId = dept_id" select="getSysDeptManageVo">-->
+<!-- <result property="subDeptId" column="sub_dept_id" />-->
+<!-- <result property="subDeptName" column="sub_dept_name" />-->
+<!-- <result property="deptId" column="dept_id" />-->
- </collection>
+<!-- </collection>-->
</resultMap>
<select id="selectDeptPageList" parameterType="com.gkhy.exam.system.domain.req.SysDeptPageReq" resultMap="DeptVoPageResult">
@@ -135,11 +126,11 @@
</select>
- <select id="getSysDeptManageVo" resultType="com.gkhy.exam.system.domain.vo.SysDeptManageVo">
- select sm.sub_dept_id ,sm2.dept_name as sub_dept_name,sm.dept_id from sys_dept_manage sm
- left join sys_dept sm2 on sm2.dept_id = sm.sub_dept_id
- where sm.dept_id = #{deptId}
- </select>
+<!-- <select id="getSysDeptManageVo" resultType="com.gkhy.exam.system.domain.vo.SysDeptManageVo">-->
+<!-- select sm.sub_dept_id ,sm2.dept_name as sub_dept_name,sm.dept_id from sys_dept_manage sm-->
+<!-- left join sys_dept sm2 on sm2.dept_id = sm.sub_dept_id-->
+<!-- where sm.dept_id = #{deptId}-->
+<!-- </select>-->
<select id="selectDeptListCount" parameterType="com.gkhy.exam.common.domain.entity.SysDept" resultType="int">
@@ -225,7 +216,13 @@
<select id="selectChildrenDeptById" parameterType="Long" resultMap="SysDeptResult">
select * from sys_dept where find_in_set(#{deptId}, ancestors)
</select>
-
+
+ <select id="getParentInfo" parameterType="Long" resultMap="SysDeptResult">
+ SELECT * FROM sys_dept
+ WHERE dept_id != #{deptId}
+ AND FIND_IN_SET(#{deptId}, ancestors) = 0
+ </select>
+
<select id="selectNormalChildrenDeptById" parameterType="Long" resultType="int">
select count(*) from sys_dept where status = 0 and del_flag = '0' and find_in_set(#{deptId}, ancestors)
</select>
--
Gitblit v1.9.2