优化改造sql

heheng

6 天以前 ab795dcf9b4783682fbb85c37d5c20b2b9006a86

 multi-system/src/main/java/com/gkhy/exam/system/service/impl/SysDeptServiceImpl.java

@@ -3,23 +3,37 @@

import cn.hutool.core.convert.Convert;
import cn.hutool.core.util.ObjectUtil;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.update.LambdaUpdateWrapper;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.gkhy.exam.common.api.CommonPage;
import com.gkhy.exam.common.constant.UserConstant;
import com.gkhy.exam.common.domain.TreeSelect;
import com.gkhy.exam.common.domain.entity.SysDept;
import com.gkhy.exam.common.domain.entity.SysRole;
import com.gkhy.exam.common.domain.entity.SysUser;
import com.gkhy.exam.common.exception.ApiException;
import com.gkhy.exam.common.utils.NaturalOrderComparator;
import com.gkhy.exam.common.utils.PageUtils;
import com.gkhy.exam.common.utils.SecurityUtils;
import com.gkhy.exam.common.utils.SpringUtils;
import com.gkhy.exam.system.domain.SysDeptManage;
import com.gkhy.exam.system.domain.SysDeptResponsibility;
import com.gkhy.exam.system.domain.SysFunctionalDistribution;
import com.gkhy.exam.system.domain.req.SysDeptPageReq;
import com.gkhy.exam.system.domain.vo.*;
import com.gkhy.exam.system.mapper.SysDeptMapper;
import com.gkhy.exam.system.mapper.SysDeptResponsibilityMapper;
import com.gkhy.exam.system.mapper.SysFunctionalDistributionMapper;
import com.gkhy.exam.system.mapper.SysRoleMapper;
import com.gkhy.exam.system.service.ISysDeptService;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.time.LocalDateTime;
import java.util.*;
import java.util.function.Function;
import java.util.stream.Collectors;

/**
@@ -36,6 +50,37 @@
    @Autowired
    private SysRoleMapper roleMapper;

    @Autowired
    private SysDeptResponsibilityMapper deptResponsibilityMapper;

    @Autowired
    private SysFunctionalDistributionMapper sysFunctionalDistributionMapper;
    public static final String[] DEPT_ROOT = {"4.1",  "4.2", "4.3", "4.4", "5.1", "5.2", "5.3","6.1", "6.2", "6.3",
            "7.1.1",  "7.1.2", "7.1.3", "7.1.4", "7.1.5", "7.1.6","7.2", "7.3", "7.4", "7.5", "7.6","8.1","8.2","8.2.1","8.2.2", "8.2.3", "8.2.4", "8.3", "8.3.1", "8.3.2", "8.3.3", "8.3.4", "8.3.5", "8.3.6", "8.3.7", "8.3.8", "8.4",
    "8.4.1", "8.4.2", "8.4.3", "8.5", "8.5.1", "8.5.2", "8.5.3", "8.5.4", "8.5.5", "8.5.6", "8.5.7", "8.6","8.7","9.1","9.1.1","9.1.2","9.1.3","9.2", "9.3","10.1"
            ,"10.2", "10.3"};

    @Override
    public CommonPage selectDeptPageList(SysDeptPageReq req) {

        PageUtils.startPage();

        List<DeptVo> deptVos = deptMapper.selectDeptPageList(req);
        if (ObjectUtil.isNotEmpty(deptVos)){
            for (DeptVo deptVo : deptVos) {
                List<CaluseVO1> caluseVO1List = deptVo.getCaluseVO1List();
                if (ObjectUtil.isNotEmpty(caluseVO1List)){
                    caluseVO1List.sort(Comparator.comparing(CaluseVO1::getClauseNum, new NaturalOrderComparator()));
                }

            }
        }

        CommonPage<DeptVo> deptVoCommonPage = CommonPage.restPage(deptVos);
        return deptVoCommonPage;

    }

    /**
     * 查询部门管理数据
     * 
@@ -44,9 +89,22 @@
     */
    @Override

    public List<SysDept> selectDeptList(SysDept dept)
    public List<DeptVo> selectDeptList(SysDept dept)
    {
        return deptMapper.selectDeptList(dept);
        if (dept.getCompanyId() == null){
            dept.setCompanyId(SecurityUtils.getCompanyId());
        }
        List<DeptVo> deptVos = deptMapper.selectDeptList(dept);
        if (ObjectUtil.isNotEmpty(deptVos)){
            for (DeptVo deptVo : deptVos) {
                List<CaluseVO1> caluseVO1List = deptVo.getCaluseVO1List();
                if (ObjectUtil.isNotEmpty(caluseVO1List)){
                    caluseVO1List.sort(Comparator.comparing(CaluseVO1::getClauseNum, new NaturalOrderComparator()));
                }

            }
        }
        return deptVos;
    }

    @Override
@@ -61,11 +119,56 @@
     * @return 部门树信息集合
     */
    @Override
    public List<TreeSelect> selectDeptTreeList(SysDept dept)
    public List<SysDept> selectDeptTreeList(SysDept dept)
    {
        List<SysDept> depts = SpringUtils.getAopProxy(this).selectDeptList(dept);
        return buildDeptTreeSelect(depts);
        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()));


        return buildDeptTree(depts,parentToChildrenMap, deptMap);
    }

    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;
    }

    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);
    }


    /**
     * 构建前端所需要树结构
@@ -127,9 +230,22 @@
     * @return 部门信息
     */
    @Override
    public SysDept selectDeptById(Long deptId)
    public DeptDetialVo selectDeptById(Long deptId)
    {
        return deptMapper.selectDeptById(deptId);
        SysDept sysDept = deptMapper.selectDeptById(deptId);
        DeptDetialVo deptDetialVo = new DeptDetialVo();
        BeanUtils.copyProperties(sysDept, deptDetialVo);
        LambdaQueryWrapper<SysDeptResponsibility> lambdaQueryWrapper = Wrappers.<SysDeptResponsibility>lambdaQuery()
                .eq(SysDeptResponsibility::getDeptId, deptId)
                .eq(SysDeptResponsibility::getDelFlag, UserConstant.ENABLE)
                .orderByAsc(SysDeptResponsibility::getClauseNum);
        List<SysDeptResponsibility> sysDeptResponsibilities = deptResponsibilityMapper.selectList(lambdaQueryWrapper);
        if (ObjectUtil.isNotEmpty(sysDeptResponsibilities)){
            sysDeptResponsibilities.sort(Comparator.comparing(SysDeptResponsibility::getClauseNum, new NaturalOrderComparator()));
        }
        deptDetialVo.setSysDeptResponsibilitys(sysDeptResponsibilities);

        return deptDetialVo ;
    }

    /**
@@ -180,7 +296,9 @@
    public boolean checkDeptNameUnique(SysDept dept)
    {
        Long deptId = ObjectUtil.isNull(dept.getDeptId()) ? -1L : dept.getDeptId();
        SysDept info = deptMapper.checkDeptNameUnique(dept.getDeptName(), dept.getParentId());
        Long companyId = SecurityUtils.getCompanyId();

        SysDept info = deptMapper.checkDeptNameUnique(companyId,dept.getDeptName(), dept.getParentId());
        if (ObjectUtil.isNotNull(info) && info.getDeptId().longValue() != deptId.longValue())
        {
            return UserConstant.NOT_UNIQUE;
@@ -196,11 +314,11 @@
    @Override
    public void checkDeptDataScope(Long deptId)
    {
        if (!SysUser.isAdmin(SecurityUtils.getUserId()) && ObjectUtil.isNotNull(deptId))
        if (!SecurityUtils.adminUser() && ObjectUtil.isNotNull(deptId))
        {
            SysDept dept = new SysDept();
            dept.setDeptId(deptId);
            List<SysDept> depts = SpringUtils.getAopProxy(this).selectDeptList(dept);
            List<SysDept> depts = SpringUtils.getAopProxy(this).getOutDeptList(dept);
            if (ObjectUtil.isEmpty(depts))
            {
                throw new ApiException("没有权限访问部门数据！");
@@ -227,6 +345,251 @@
        return deptMapper.insertDept(dept);
    }

    @Override
    @Transactional
    public int saveDept(SysDeptSaveDTOReq dept) {
//        Long companyId = SecurityUtils.getCompanyId();
//        if (!companyId.equals(dept.getCompanyId())){
//            throw new ApiException("无权操作！");
//        }
        SysDept sysDept = new SysDept();
        BeanUtils.copyProperties(dept, sysDept);
        boolean b = checkDeptNameUnique(sysDept);
        if (!b){
            throw new ApiException("部门名称已存在！");
        }
        int i = 0;
        if (sysDept.getDeptId() == null){
            sysDept.setCreateBy(SecurityUtils.getUsername());
             i = deptMapper.insertDept(sysDept);
        }else {
            sysDept.setUpdateBy(SecurityUtils.getUsername());
            sysDept.setUpdateTime(LocalDateTime.now());
            i = deptMapper.updateDept(sysDept);
        }
        if (i > 0){
            List<Long> delCaluseIds = dept.getDelCaluseIds();
            if (delCaluseIds != null && !delCaluseIds.isEmpty())
            {
                delCaluse(sysDept.getDeptId(), delCaluseIds);
            }
            //处理条款
            batchSaveCaluse(sysDept.getDeptId(), dept.getCompanyId(), dept.getCaluseVO1List());

            List<Long> childDeptIds = dept.getChildDeptIds();
            bacthInsertManager(sysDept.getDeptId(), childDeptIds, dept.getCompanyId());
        }

        return sysDept.getDeptId().byteValue();
    }

    private void bacthInsertManager(Long deptId, List<Long> managerIds,Long companyId) {
        deptMapper.deleteMangeBatch(deptId);
        if (managerIds != null && !managerIds.isEmpty())
        {
            List<SysDeptManage> sysDeptManagers = new ArrayList<>();
            for (Long managerId : managerIds)
            {
                SysDeptManage sysDeptManage = new SysDeptManage();
                sysDeptManage.setDeptId(deptId);
                sysDeptManage.setSubDeptId(managerId);
                sysDeptManage.setCompanyId(companyId);
                sysDeptManagers.add(sysDeptManage);
            }
            deptMapper.insetMangeBatch(sysDeptManagers);
        }
    }

    @Override
    @Transactional
    public int saveDeptResponsibility(SysDeptResponsibilityReqVo reqVo) {
//        Long companyId = SecurityUtils.getCompanyId();
//        if (!companyId.equals(reqVo.getCompanyId())){
//            throw new ApiException("无权操作！");
//        }
        SysDept sysDept = deptMapper.selectDeptById(reqVo.getDeptId());
        if (null == sysDept){
            throw new ApiException("部门不存在！");
        }
        if (!reqVo.getCompanyId().equals(sysDept.getCompanyId())){
            throw new ApiException("无权操作！");
        }
        SysDept dept = new SysDept();
        dept.setDeptId(reqVo.getDeptId());
        dept.setPersonNum(reqVo.getPersonNum());
        dept.setInternalAuditors(reqVo.getInternalAuditors());
        dept.setResponsibilities(reqVo.getResponsibilities());
        dept.setUpdateBy(SecurityUtils.getUsername());
        dept.setUpdateTime(LocalDateTime.now());
        int i = deptMapper.updateDept(dept);
        if (i < 1){
            throw new ApiException("编辑失败！");
        }

        batchSaveRespon(reqVo);
        return 1;
    }

    @Override
    public List<SysFunctionalDistribution> getFunctionalDistributionList(Long companyId) {

       return sysFunctionalDistributionMapper.selectListVo(companyId);
    }

    @Override
    @Transactional
    public int initFunctionalDistribution(Long companyId) {

//        if (!companyId.equals(SecurityUtils.getCompanyId())){
//            throw new ApiException("无权操作！");
//        }

        sysFunctionalDistributionMapper.delByCompanyId(companyId);
        SysDept sysDept = new SysDept();
        sysDept.setCompanyId(companyId);
        sysDept.setResponsType("1");
        List<DeptVo> deptVos = deptMapper.selectDeptList(sysDept);
        if (deptVos != null && !deptVos.isEmpty()){
            List<SysFunctionalDistribution> sysFunctionalDistributions = new ArrayList<>();
            for (DeptVo deptVo : deptVos) {
                for (String s : DEPT_ROOT) {
                    SysFunctionalDistribution sysFunctionalDistribution = new SysFunctionalDistribution();
                    sysFunctionalDistribution.setDeptId(deptVo.getDeptId());
                    sysFunctionalDistribution.setCompanyId(companyId);
                    sysFunctionalDistribution.setCreateBy(SecurityUtils.getUsername());
                    sysFunctionalDistribution.setCreateTime(LocalDateTime.now());
                    sysFunctionalDistribution.setClauseNum(s);
                    sysFunctionalDistribution.setChooseLab(0);
                    sysFunctionalDistributions.add(sysFunctionalDistribution);
                }

            }
            int i = sysFunctionalDistributionMapper.batchInsert(sysFunctionalDistributions);
            if (i < 1){
                throw new ApiException("初始化失败！");
            }
        }

        return 1;
    }

    @Override
    public int saveFunctionalDistribution(FunctionalDistributionVo reqVo) {
//        Long companyId = SecurityUtils.getCompanyId();
//        if (!companyId.equals(reqVo.getCompanyId())){
//            throw new ApiException("无权操作！");
//        }
        List<SysFunctionalDistribution> list = reqVo.getList();
        if (list != null && !list.isEmpty())
        {
            for (SysFunctionalDistribution sysFunctionalDistribution : list) {
                sysFunctionalDistribution.setCompanyId(reqVo.getCompanyId());
                sysFunctionalDistribution.setUpdateBy(SecurityUtils.getUsername());
                sysFunctionalDistribution.setUpdateTime(LocalDateTime.now());
            }
        }
        int i = sysFunctionalDistributionMapper.batchUpdate(list);

        return 1;
    }


    private void batchSaveRespon(SysDeptResponsibilityReqVo reqVo) {
        List<Long> delData = reqVo.getDelData();
        if (delData != null && !delData.isEmpty())
        {
            deptResponsibilityMapper
                    .update(new SysDeptResponsibility(),
                            new LambdaUpdateWrapper<SysDeptResponsibility>().set(SysDeptResponsibility::getDelFlag, UserConstant.DEPT_DISABLE)
                                    .set(SysDeptResponsibility::getUpdateTime, LocalDateTime.now()).set(SysDeptResponsibility::getUpdateBy, SecurityUtils.getUsername())
                                    .in(SysDeptResponsibility::getId, delData)
                    );
        }
        List<SysDeptResponsibility> sysDeptResponsibilityList = reqVo.getSysDeptResponsibilityList();
        List<String> emptyIdClauseNums = sysDeptResponsibilityList.stream()
                .map(SysDeptResponsibility::getClauseNum)
                .collect(Collectors.toList());
        Set<String> emptyIdClauseNumSet = new HashSet<>(emptyIdClauseNums);
        if (emptyIdClauseNumSet.size() !=  emptyIdClauseNums.size()){
            throw new ApiException("部门条款编码重复！");
        }
        List<Long> emptyIdClauseIds = sysDeptResponsibilityList.stream()
                .filter(item -> item.getId() != null)
                .map(SysDeptResponsibility::getId)
                .collect(Collectors.toList());

        if (!emptyIdClauseNums.isEmpty()){
            List<SysDeptResponsibility> sysDeptResponsibilities = deptResponsibilityMapper.selectList(new LambdaQueryWrapper<SysDeptResponsibility>()
                    .eq(SysDeptResponsibility::getDeptId, reqVo.getDeptId())
                    .in(SysDeptResponsibility::getClauseNum, emptyIdClauseNums)
                    .notIn(SysDeptResponsibility::getId, emptyIdClauseIds).eq(SysDeptResponsibility::getDelFlag, "0"));
            if (!sysDeptResponsibilities.isEmpty()){
                throw new ApiException("部门条款编码重复！");
            }
        }
        for (SysDeptResponsibility sysDeptResponsibility : sysDeptResponsibilityList) {
            if (sysDeptResponsibility.getId() == null){
                sysDeptResponsibility.setCreateBy(SecurityUtils.getUsername());
                sysDeptResponsibility.setCreateTime(LocalDateTime.now());
                sysDeptResponsibility.setDeptId(reqVo.getDeptId());
                sysDeptResponsibility.setCompanyId(reqVo.getCompanyId());
                deptResponsibilityMapper.insert(sysDeptResponsibility);
            }else {
                sysDeptResponsibility.setUpdateBy(SecurityUtils.getUsername());
                sysDeptResponsibility.setUpdateTime(LocalDateTime.now());
                deptResponsibilityMapper.updateById(sysDeptResponsibility);
            }

        }
    }


    private void delCaluse(Long deptId,  List<Long> delCaluseIds)
    {
        deptResponsibilityMapper
                .update(new SysDeptResponsibility(),
                        new LambdaUpdateWrapper<SysDeptResponsibility>().set(SysDeptResponsibility::getDelFlag, UserConstant.DEPT_DISABLE)
                                .set(SysDeptResponsibility::getUpdateTime, LocalDateTime.now()).set(SysDeptResponsibility::getUpdateBy, SecurityUtils.getUsername())
                                .eq(SysDeptResponsibility::getDeptId, deptId).in(SysDeptResponsibility::getId, delCaluseIds));
    }

    private void batchSaveCaluse(Long deptId,Long companyId, List<CaluseVO1> caluseVO1List) {

        List<String> emptyIdClauseNums = caluseVO1List.stream()
                .filter(item -> item.getId() == null)
                .map(CaluseVO1::getClauseNum)
                .collect(Collectors.toList());
        if (!emptyIdClauseNums.isEmpty()){
            List<SysDeptResponsibility> sysDeptResponsibilities = deptResponsibilityMapper.selectList(new LambdaQueryWrapper<SysDeptResponsibility>()
                    .eq(SysDeptResponsibility::getDeptId, deptId).in(SysDeptResponsibility::getClauseNum, emptyIdClauseNums)
                    .eq(SysDeptResponsibility::getCompanyId, companyId).eq(SysDeptResponsibility::getDelFlag, "0"));
            if (!sysDeptResponsibilities.isEmpty()){
                throw new ApiException("部门条款编码重复！");
            }
        }

        for (CaluseVO1 caluseVO1 : caluseVO1List) {
            SysDeptResponsibility sysDeptResponsibility = new SysDeptResponsibility();
            BeanUtils.copyProperties(caluseVO1, sysDeptResponsibility);
            sysDeptResponsibility.setCompanyId(companyId);
            sysDeptResponsibility.setDeptId(deptId);


            if (sysDeptResponsibility.getId() == null){
                sysDeptResponsibility.setCreateBy(SecurityUtils.getUsername());
                sysDeptResponsibility.setCreateTime(LocalDateTime.now());
                sysDeptResponsibility.setDataType("2");
                deptResponsibilityMapper.insert(sysDeptResponsibility);
            }else {
                sysDeptResponsibility.setUpdateBy(SecurityUtils.getUsername());
                sysDeptResponsibility.setUpdateTime(LocalDateTime.now());
                deptResponsibilityMapper.updateById(sysDeptResponsibility);
            }

        }

    }

    /**
     * 修改保存部门信息
     *