fix：sql查询时间调整

cjl

2025-01-06 8490eb11583ffdc586436f165860de5cfc39f915

 screen-manage/src/main/java/com/moral/api/service/impl/OrganizationServiceImpl.java

@@ -1,10 +1,37 @@
package com.moral.api.service.impl;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.conditions.update.UpdateWrapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.moral.api.config.mybatis.wrapper.NullFilterWrapper;
import com.moral.api.entity.MonitorPoint;
import com.moral.api.entity.Organization;
import com.moral.api.entity.User;
import com.moral.api.mapper.OrganizationMapper;
import com.moral.api.mapper.UserMapper;
import com.moral.api.pojo.dto.organization.OrganizationDTO;
import com.moral.api.pojo.dto.organization.OrganizationQueryDTO;
import com.moral.api.pojo.dto.organization.OrganizationQueryNamesDTO;
import com.moral.api.pojo.form.organization.*;
import com.moral.api.service.MonitorPointService;
import com.moral.api.service.OrganizationService;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.moral.api.service.UserService;
import com.moral.api.util.CompareFieldUtils;
import com.moral.api.util.LogUtils;
import com.moral.constant.Constants;
import com.moral.constant.ResponseCodeEnum;
import com.moral.pojo.CompareFieldResult;
import com.moral.util.ConvertUtils;
import com.moral.util.DateUtils;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.ObjectUtils;

import java.util.*;
import java.util.function.Predicate;

/**
 * <p>
@@ -12,9 +39,438 @@
 * </p>
 *
 * @author moral
 * @since 2021-03-09
 * @since 2021-04-06
 */
@Service
public class OrganizationServiceImpl extends ServiceImpl<OrganizationMapper, Organization> implements OrganizationService {

    @Autowired
    OrganizationMapper organizationMapper;

    @Autowired
    UserService userService;

    @Autowired
    UserMapper userMapper;

    @Autowired
    MonitorPointService monitorPointService;

    /**
     * @Description: 添加客户组织
     * @Param: [organizationInsertForm]
     * @return: void
     * @Author: 陈凯裕
     * @Date: 2021/3/22
     */
    @Override
    @Transactional
    public OrganizationDTO insertOrganization(OrganizationInsertForm organizationInsertForm) {
        OrganizationDTO organizationDTO = new OrganizationDTO();
        QueryWrapper<Organization> queryWrapper = new QueryWrapper<>();
        //form转换entity
        Organization organization = organizationInsertForm.formConvertEntity();
        //查询组织名称是否已经存在
        Organization existOrganization = new Organization();
        existOrganization.setName(organization.getName());
        existOrganization.setIsDelete(Constants.NOT_DELETE);
        queryWrapper.setEntity(existOrganization);
        Organization existOrganizationResult = organizationMapper.selectOne(queryWrapper);
        if (!ObjectUtils.isEmpty(existOrganizationResult)) {
            organizationDTO.setCode(ResponseCodeEnum.ORGANIZATION_EXIST.getCode());
            organizationDTO.setMsg(ResponseCodeEnum.ORGANIZATION_EXIST.getMsg());
            return organizationDTO;
        }
        //插入组织
        organizationMapper.insert(organization);

        //封装DTO信息
        organizationDTO.setCode(ResponseCodeEnum.SUCCESS.getCode());
        organizationDTO.setMsg(ResponseCodeEnum.SUCCESS.getMsg());

        //操作插入日志
        insertLog(organization);
        return organizationDTO;
    }

    /**
     * @Description: 更新客户组织
     * @Param: [organizationUpdateForm]
     * @return: com.moral.api.pojo.dto.organization.OrganizationDTO
     * @Author: 陈凯裕
     * @Date: 2021/3/24
     */
    @Override
    @Transactional
    public OrganizationDTO updateOrganization(OrganizationUpdateForm organizationUpdateForm) {
        OrganizationDTO organizationDTO = new OrganizationDTO();
        //form转entity
        Organization organization = organizationUpdateForm.formConvertEntity();

        //查找要更新的组织用于插入日志
        QueryWrapper<Organization> oldWrapper = new QueryWrapper<>();
        Organization oldOrganization = new Organization();
        oldOrganization.setId(organization.getId());
        oldOrganization.setIsDelete(Constants.NOT_DELETE);
        oldWrapper.setEntity(oldOrganization);
        oldOrganization = organizationMapper.selectOne(oldWrapper);
        if (ObjectUtils.isEmpty(oldOrganization)) {
            organizationDTO.setCode(ResponseCodeEnum.ORGANIZATION_NOT_EXIST.getCode());
            organizationDTO.setMsg(ResponseCodeEnum.ORGANIZATION_NOT_EXIST.getMsg());
            return organizationDTO;
        }


        //更新组织
        organizationMapper.updateById(organization);

        //获取更新后的组织
        organization = organizationMapper.selectById(organization.getId());

        //封装DTO信息
        organizationDTO.setCode(ResponseCodeEnum.SUCCESS.getCode());
        organizationDTO.setMsg(ResponseCodeEnum.SUCCESS.getMsg());

        //操作插入日志
        updateLog(oldOrganization, organization);

        return organizationDTO;
    }


    /**
     * @Description: 删除客户组织
     * @Param: [organizationDeleteForm]
     * @return: com.moral.api.pojo.dto.organization.OrganizationDTO
     * @Author: 陈凯裕
     * @Date: 2021/3/25
     */
    @Override
    @Transactional
    public OrganizationDTO deleteOrganization(OrganizationDeleteForm form) {
        OrganizationDTO dto = new OrganizationDTO();
        //取参
        Integer id = form.getOrganizationId();

        //判断组织是否含有站点
        List<MonitorPoint> monitorPoints = monitorPointService.getMonitorPointsByOrganizationId(id);
        if (!ObjectUtils.isEmpty(monitorPoints)) {
            dto.setCode(ResponseCodeEnum.ORGANIZATION_EXIST_MONITORPOINT.getCode());
            dto.setMsg(ResponseCodeEnum.ORGANIZATION_EXIST_MONITORPOINT.getMsg());
            return dto;
        }

        //查询要删除的组织用于插入日志
        Organization existOrganization = new Organization();
        existOrganization.setIsDelete(Constants.NOT_DELETE);
        existOrganization.setId(id);
        QueryWrapper queryExistWrapper = new QueryWrapper();
        queryExistWrapper.setEntity(existOrganization);
        existOrganization = organizationMapper.selectOne(queryExistWrapper);
        if (ObjectUtils.isEmpty(existOrganization)) {
            dto.setCode(ResponseCodeEnum.ORGANIZATION_NOT_EXIST.getCode());
            dto.setMsg(ResponseCodeEnum.ORGANIZATION_NOT_EXIST.getMsg());
            return dto;
        }

        //判断是否含有子组织
        List<Organization> children = getAllChildrenOrganization(existOrganization.getId());
        if (!ObjectUtils.isEmpty(children)) {
            //判断是否删除所有子组织，如果不删除子组织则所有子组织全部变为无父组织，孙子组织不变
            UpdateWrapper updateWrapper = new UpdateWrapper();
            if (form.getDeleteChildren().equals(Constants.DELETE_CHILDREN_ORG)) {//删除所有子组织
                //获取所有子组织的id封装为集合
                List<Integer> childrenId = new ArrayList<>();
                children.forEach(value -> {
                    childrenId.add(value.getId());
                });
                //判断子组织是否含有站点，有一个子组织含有站点则不能删除
                for (Integer childId : childrenId) {
                    List<MonitorPoint> childMonitorPoints = monitorPointService.getMonitorPointsByOrganizationId(childId);
                    if (!ObjectUtils.isEmpty(childMonitorPoints)) {
                        dto.setCode(ResponseCodeEnum.CHILDREN_ORGANIZATION_EXIST_MONITORPOINT.getCode());
                        dto.setMsg(ResponseCodeEnum.CHILDREN_ORGANIZATION_EXIST_MONITORPOINT.getMsg());
                        return dto;
                    }
                }
                //进行删除
                updateWrapper.in("id", childrenId);
                updateWrapper.set("is_delete", Constants.DELETE);
                organizationMapper.update(null, updateWrapper);
                //删除所有子组织账号
                childrenId.forEach(value -> userService.deleteUsersByOrganizationId(value));

            } else {//不删除
                //提取所有直属子组织id
                List<Integer> childrenId = new ArrayList<>();
                children.forEach(value -> {
                    if (value.getParentId().equals(id))
                        childrenId.add(value.getId());
                });
                //进行更新
                updateWrapper.in("id", childrenId);
                updateWrapper.set("parent_id", 0);
                organizationMapper.update(null, updateWrapper);
            }
        }

        //逻辑删除组织
        UpdateWrapper deleteWrapper = new UpdateWrapper();
        deleteWrapper.eq("id", id);
        deleteWrapper.set("is_delete", Constants.DELETE);
        organizationMapper.update(null, deleteWrapper);

        //删除组织账号
        userService.deleteUsersByOrganizationId(id);

        //封装返回结果
        dto.setCode(ResponseCodeEnum.SUCCESS.getCode());
        dto.setMsg(ResponseCodeEnum.SUCCESS.getMsg());
        //操作插入日志
        StringBuilder content = new StringBuilder();
        content.append("删除了组织：").append(existOrganization.getName());
        if (form.getDeleteChildren().equals(Constants.DELETE_CHILDREN_ORG))
            content.append("以及所有子组织;");
        else
            content.append(";");
        LogUtils.saveOperationForManage(content.toString(), Constants.DELETE_OPERATE_TYPE);

        return dto;
    }

    /**
     * @Description: 查询客户组织
     * @Param: [organizationQueryForm]
     * @return: com.moral.api.pojo.dto.organization.OrganizationQueryDTO
     * @Author: 陈凯裕
     * @Date: 2021/3/25
     */
    @Override
    public OrganizationQueryDTO queryOrganization(OrganizationQueryForm organizationQueryForm) {
        OrganizationQueryDTO dto = new OrganizationQueryDTO();
        //取参
        Integer pageCount = organizationQueryForm.getPage();
        Integer size = organizationQueryForm.getSize();
        Integer parentId = organizationQueryForm.getParentId();
        String name = organizationQueryForm.getName();
        Integer provinceCode = organizationQueryForm.getProvinceCode();
        Integer cityCode = organizationQueryForm.getCityCode();
        Integer areaCode = organizationQueryForm.getAreaCode();
        String phone = organizationQueryForm.getPhone();
        String email = organizationQueryForm.getEmail();
        String wechat = organizationQueryForm.getWechat();
        String isDelete = organizationQueryForm.getIsDelete();
        String order = organizationQueryForm.getOrder();
        String orderType = organizationQueryForm.getOrderType();
        Date createStartTime = organizationQueryForm.getCreateStartTime();
        Date createEndTime = DateUtils.getDateOfDay(organizationQueryForm.getCreateEndTime(), 1);
        Date expireStartTime = organizationQueryForm.getExpireStartTime();
        Date expireEndTime = DateUtils.getDateOfDay(organizationQueryForm.getExpireEndTime(), 1);

        //查询条件
        Page<Organization> page = new Page<>(pageCount, size);
        NullFilterWrapper<Organization> queryWrapper = new NullFilterWrapper<>();

        queryWrapper.eq("parent_id", parentId);
        queryWrapper.like("name", name);
        queryWrapper.eq("province_code", provinceCode);
        queryWrapper.eq("city_code", cityCode);
        queryWrapper.eq("area_code", areaCode);
        queryWrapper.like("phone", phone);
        queryWrapper.like("email", email);
        queryWrapper.like("wechat", wechat);
        queryWrapper.between("create_time", createStartTime, createEndTime);
        queryWrapper.between("expire_time", expireStartTime, expireEndTime);

        if (!ObjectUtils.isEmpty(isDelete)) {
            queryWrapper.eq("is_delete", isDelete);
        } else {
            queryWrapper.eq("is_delete", Constants.NOT_DELETE);
        }

        //排序顺序
        if (!ObjectUtils.isEmpty(order)) {
            if (!ObjectUtils.isEmpty(orderType)) {
                if (orderType.equals(Constants.ORDER_ASC))
                    queryWrapper.orderByAsc(ConvertUtils.toLine(order));
                else
                    queryWrapper.orderByDesc(ConvertUtils.toLine(order));
            }
        }

        //查询结果
        Page<Organization> resultPage = organizationMapper.selectPage(page, queryWrapper);
        List<Organization> organizations = resultPage.getRecords();
        List<OrganizationDTO> organizationDTOS = new ArrayList<>();
        //查找所有组织的父组织和admin账号并且封装organization到DTO中
        for (Organization organization : organizations) {
            OrganizationDTO resultDto = new OrganizationDTO();
            //查找父组织
            Organization parent = organizationMapper.selectById(organization.getParentId());
            resultDto.setOrganization(organization);
            resultDto.setParentOrganization(parent);
            //查找admin账号
            QueryWrapper userWrapper = new QueryWrapper();
            User adminUser = new User();
            adminUser.setIsAdmin(true);
            adminUser.setOrganizationId(organization.getId());
            adminUser.setIsDelete(Constants.NOT_DELETE);
            userWrapper.setEntity(adminUser);
            adminUser = userMapper.selectOne(userWrapper);
            resultDto.setAdminUser(adminUser);
            organizationDTOS.add(resultDto);
        }

        dto.setCode(ResponseCodeEnum.SUCCESS.getCode());
        dto.setMsg(ResponseCodeEnum.SUCCESS.getMsg());
        dto.setOrganizationDTOS(organizationDTOS);
        dto.setCurrent(page.getCurrent());
        dto.setPages(page.getPages());
        dto.setSize(page.getSize());
        dto.setTotal(page.getTotal());
        return dto;
    }

    /**
     * @Description: 如果form中id为空，则查询所有的组织
     * 如果id不为空，则查询该id和该id所有子组织之外的所有组织
     * @Param: [from]
     * @return: com.moral.api.pojo.dto.organization.OrganizationQueryNamesDTO
     * @Author: 陈凯裕
     * @Date: 2021/5/7
     */
    @Override
    public OrganizationQueryNamesDTO queryNames(OrganizationQueryNamesForm form) {
        OrganizationQueryNamesDTO dto = new OrganizationQueryNamesDTO();
        //取参
        Integer id = form.getId();
        //构造查询条件
        QueryWrapper<Organization> queryWrapper = new QueryWrapper();
        queryWrapper.eq("is_delete", Constants.NOT_DELETE);
        //查询所有组织
        List<Organization> organizations = organizationMapper.selectList(queryWrapper);
        //判断form是否含有id,如果有则对其所有子组织进行过滤
        if (!ObjectUtils.isEmpty(id)) {
            List<Organization> children = getAllChildrenOrganization(id);
            List<Integer> thisAndchildrenIds = new ArrayList<>();//该id以及其所有子组织的id集合
            thisAndchildrenIds.add(id);
            for (Organization child : children) {
                thisAndchildrenIds.add(child.getId());
            }
            organizations.removeIf(new Predicate<Organization>() {//过滤
                @Override
                public boolean test(Organization organization) {
                    if (thisAndchildrenIds.contains(organization.getId()))
                        return true;
                    return false;
                }
            });
        }
        //封装返回对象
        dto.setCode(ResponseCodeEnum.SUCCESS.getCode());
        dto.setMsg(ResponseCodeEnum.SUCCESS.getMsg());
        dto.setOrganizations(organizations);
        return dto;
    }


    /**
     * @Description: 根据父组织获取所有子组织
     * @Param: []
     * @return: java.util.List<com.moral.api.entity.Organization>
     * @Author: 陈凯裕
     * @Date: 2021/4/14
     */
    @Override
    public List<Organization> getAllChildrenOrganization(Integer parentId) {
        List<Organization> children = new ArrayList<>();
        recursionQueryChildren(parentId, children);
        return children;
    }

    /**
     * @Description: 通过父组织查询下面所有的子组织放到children中
     * @Param: [parent, children]
     * @return: void
     * @Author: 陈凯裕
     * @Date: 2021/4/14
     */
    private void recursionQueryChildren(Integer parentId, List<Organization> children) {
        QueryWrapper<Organization> queryWrapper = new QueryWrapper();
        queryWrapper.eq("is_delete", Constants.NOT_DELETE);
        queryWrapper.eq("parent_id", parentId);
        List<Organization> organizations = organizationMapper.selectList(queryWrapper);
        if (!ObjectUtils.isEmpty(organizations)) {
            children.addAll(organizations);
            for (Organization organization : organizations) {
                recursionQueryChildren(organization.getId(), children);
            }
        } else {
            return;
        }
    }

    /**
     * @Description: 插入操作插入日志
     * @Param: [organization]
     * @return: void
     * @Author: 陈凯裕
     * @Date: 2021/8/25
     */
    private void insertLog(Organization organization) {
        StringBuilder content = new StringBuilder();
        content.append("添加了组织：").append(organization.getName() + ";");
        LogUtils.saveOperationForManage(content.toString(), Constants.INSERT_OPERATE_TYPE);
    }

    public void updateLog(Organization oldOrganization, Organization newOrganization) {
        List<CompareFieldResult> results = CompareFieldUtils.compare(Organization.class, oldOrganization, newOrganization);
        for (CompareFieldResult result : results) {
            //改变父组织特殊处理
            if (result.getFieldName().equals("parentId")) {
                //父组织id转换为名称
                String oldData = result.getOldData();
                String newData = result.getNewData();

                if (!oldData.equals("0"))
                    oldData = organizationMapper.selectById(Integer.parseInt(oldData)).getName();
                else
                    oldData = "null";

                if (!newData.equals("0"))
                    newData = organizationMapper.selectById(Integer.parseInt(newData)).getName();
                else
                    newData = "null";

                result.setNewData(newData);
                result.setOldData(oldData);
                result.setFieldAnnoName("父组织");
            }

            //过期时间格式转换
            if (result.getFieldName().equals("expireTime")) {
                //DateToString的时间格式改为yyyy-MM-dd
                String oldData = result.getOldData();
                String newData = result.getNewData();

                if (oldData != null) {
                    Date oldDate = DateUtils.dateStringToDate(oldData);
                    oldData = DateUtils.dateToDateString(oldDate, "yyyy-MM-dd");
                }

                if (newData != null) {
                    Date newDate = DateUtils.dateStringToDate(newData);
                    newData = DateUtils.dateToDateString(newDate, "yyyy-MM-dd");
                }

                result.setNewData(newData);
                result.setOldData(oldData);
                result.setFieldAnnoName("过期时间");
            }
        }

        String content = CompareFieldUtils.resultsConvertContent(results, "修改了组织;组织名称：" + oldOrganization.getName());
        LogUtils.saveOperationForManage(content, Constants.UPDATE_OPERATE_TYPE);
    }

}