fix：sql查询时间调整

cjl

2025-01-06 8490eb11583ffdc586436f165860de5cfc39f915

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

@@ -2,31 +2,48 @@

import com.alibaba.fastjson.JSON;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.conditions.update.UpdateWrapper;
import com.baomidou.mybatisplus.core.toolkit.ObjectUtils;
import com.baomidou.mybatisplus.extension.conditions.query.LambdaQueryChainWrapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.moral.api.entity.Device;
import com.moral.api.entity.GovMonitorPoint;
import com.moral.api.entity.Organization;
import com.moral.api.entity.SysArea;
import com.moral.api.exception.BusinessException;
import com.moral.api.mapper.DeviceMapper;
import com.moral.api.mapper.GovMonitorPointMapper;
import com.moral.api.mapper.SysAreaMapper;
import com.moral.api.service.DeviceService;
import com.moral.api.service.GovMonitorPointService;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.moral.api.service.OrganizationService;
import com.moral.api.util.LogUtils;
import com.moral.constant.Constants;
import com.moral.constant.RedisConstants;
import com.moral.constant.ResponseCodeEnum;
import com.moral.constant.ResultMessage;
import com.moral.util.RegionCodeUtils;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;

import java.text.SimpleDateFormat;
import java.util.*;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.stream.Collectors;

/**
 * <p>
 *  服务实现类
 * 服务实现类
 * </p>
 *
 * @author moral
@@ -44,8 +61,14 @@
    @Autowired(required = false)
    private RedisTemplate redisTemplate;

    @Autowired
    private LogUtils logUtils;
    @Resource
    private OrganizationService organizationService;

    @Resource
    private DeviceService deviceService;

    @Autowired(required = false)
    private DeviceMapper deviceMapper;

    /*
     * 从redis获取设备信息
@@ -70,86 +93,192 @@

    @Override
    public Map<String, Object> getDataByCondition(Map map) {
        Map<String,Object> resultMap = new HashMap<>();
        if (!map.containsKey("current")||!map.containsKey("size")){
            resultMap.put("code",ResponseCodeEnum.PARAMETERS_IS_MISSING.getCode());
            resultMap.put("msg",ResponseCodeEnum.PARAMETERS_IS_MISSING.getMsg());
        Map<String, Object> resultMap = new HashMap<>();
        if (!map.containsKey("current") || !map.containsKey("size")) {
            resultMap.put("code", ResponseCodeEnum.PARAMETERS_IS_MISSING.getCode());
            resultMap.put("msg", ResponseCodeEnum.PARAMETERS_IS_MISSING.getMsg());
            return resultMap;
        }
        int current = Integer.parseInt(map.get("current").toString());
        int size = Integer.parseInt(map.get("size").toString());
        Page<GovMonitorPoint> page = new Page<>(current,size);
        Page<GovMonitorPoint> page = new Page<>(current, size);
        QueryWrapper<GovMonitorPoint> wrapper_Condition = new QueryWrapper<>();
        wrapper_Condition.eq("is_delete",Constants.NOT_DELETE);
        if (!ObjectUtils.isEmpty(map.get("name"))){
            wrapper_Condition.like("name",map.get("name").toString());
        wrapper_Condition.eq("is_delete", Constants.NOT_DELETE);
        if (!ObjectUtils.isEmpty(map.get("name"))) {
            wrapper_Condition.like("name", map.get("name").toString());
        }
        if (!ObjectUtils.isEmpty(map.get("stationLevel"))){
            wrapper_Condition.like("station_level",map.get("stationLevel").toString());
        if (!ObjectUtils.isEmpty(map.get("stationLevel"))) {
            wrapper_Condition.like("station_level", map.get("stationLevel").toString());
        }
        wrapper_Condition.orderByDesc("create_time");
        Page resultPage = govMonitorPointMapper.selectPage(page,wrapper_Condition);
        Page resultPage = govMonitorPointMapper.selectPage(page, wrapper_Condition);
        int totalNumber = govMonitorPointMapper.selectCount(wrapper_Condition);
        List<GovMonitorPoint> govMonitorPoints = resultPage.getRecords();
        SimpleDateFormat SDF = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        List<Map<String,Object>> govMonitorPointList = new ArrayList<>();
        for (GovMonitorPoint govMonitorPoint:govMonitorPoints) {
            Map govMonitorPointMap = JSON.parseObject(JSON.toJSONString(govMonitorPoint),Map.class);
        List<Map<String, Object>> govMonitorPointList = new ArrayList<>();
        for (GovMonitorPoint govMonitorPoint : govMonitorPoints) {
            Map govMonitorPointMap = JSON.parseObject(JSON.toJSONString(govMonitorPoint), Map.class);
            String createTime = SDF.format(govMonitorPoint.getCreateTime());
            String updateTime = SDF.format(govMonitorPoint.getUpdateTime());
            govMonitorPointMap.put("createTime",createTime);
            govMonitorPointMap.put("updateTime",updateTime);
            govMonitorPointMap.put("createTime", createTime);
            govMonitorPointMap.put("updateTime", updateTime);
            String position = "";
            if (!ObjectUtils.isEmpty(govMonitorPoint.getProvinceCode())){
            if (!ObjectUtils.isEmpty(govMonitorPoint.getProvinceCode())) {
                QueryWrapper<SysArea> wrapper_province = new QueryWrapper<>();
                wrapper_province.eq("area_code",govMonitorPoint.getProvinceCode());
                wrapper_province.eq("area_code", govMonitorPoint.getProvinceCode());
                SysArea sysArea_provice = sysAreaMapper.selectOne(wrapper_province);
                if (!ObjectUtils.isEmpty(sysArea_provice)){
                    position = position+sysArea_provice.getAreaName();
                    if (!ObjectUtils.isEmpty(govMonitorPoint.getCityCode())){
                if (!ObjectUtils.isEmpty(sysArea_provice)) {
                    position = position + sysArea_provice.getAreaName();
                    if (!ObjectUtils.isEmpty(govMonitorPoint.getCityCode())) {
                        QueryWrapper<SysArea> wrapper_city = new QueryWrapper<>();
                        wrapper_city.eq("area_code",govMonitorPoint.getCityCode());
                        wrapper_city.eq("area_code", govMonitorPoint.getCityCode());
                        SysArea sysArea_city = sysAreaMapper.selectOne(wrapper_city);
                        if (!ObjectUtils.isEmpty(sysArea_city)){
                            position = position+"/"+sysArea_city.getAreaName();
                            if (!ObjectUtils.isEmpty(govMonitorPoint.getAreaCode())){
                        if (!ObjectUtils.isEmpty(sysArea_city)) {
                            position = position + "/" + sysArea_city.getAreaName();
                            if (!ObjectUtils.isEmpty(govMonitorPoint.getAreaCode())) {
                                QueryWrapper<SysArea> wrapper_area = new QueryWrapper<>();
                                wrapper_area.eq("area_code",govMonitorPoint.getAreaCode());
                                wrapper_area.eq("area_code", govMonitorPoint.getAreaCode());
                                SysArea sysArea_area = sysAreaMapper.selectOne(wrapper_area);
                                if (!ObjectUtils.isEmpty(sysArea_area)){
                                    position = position+"/"+sysArea_area.getAreaName();
                                if (!ObjectUtils.isEmpty(sysArea_area)) {
                                    position = position + "/" + sysArea_area.getAreaName();
                                }
                            }
                        }
                    }
                }
            }
            govMonitorPointMap.put("position",position);
            govMonitorPointMap.put("position", position);
            govMonitorPointList.add(govMonitorPointMap);
        }
        resultMap.put("govMonitorPointList",govMonitorPointList);
        resultMap.put("totalNumber",totalNumber);
        resultMap.put("current",current);
        int totalPageNumber = totalNumber/size;
        if(totalNumber%size != 0){
        resultMap.put("govMonitorPointList", govMonitorPointList);
        resultMap.put("totalNumber", totalNumber);
        resultMap.put("current", current);
        int totalPageNumber = totalNumber / size;
        if (totalNumber % size != 0) {
            totalPageNumber += 1;
        }
        resultMap.put("totalPageNumber",totalPageNumber);
        resultMap.put("totalPageNumber", totalPageNumber);
        return resultMap;
    }

    @Transactional
    @Override
    public void insert(GovMonitorPoint govMonitorPoint) {
        int count = govMonitorPointMapper.insert(govMonitorPoint);
        //数据插入redis
        setGovMonitorPointInfoToRedis(govMonitorPoint.getId().toString(),selectGovMonitorPointInfoById(govMonitorPoint.getId()));
        if (count > 0){
        setGovMonitorPointInfoToRedis(govMonitorPoint.getId().toString(), selectGovMonitorPointInfoById(govMonitorPoint.getId()));
        if (count > 0) {
            //操作日志记录
            HttpServletRequest request = ((ServletRequestAttributes) Objects.requireNonNull(RequestContextHolder.getRequestAttributes())).getRequest();
            StringBuilder content = new StringBuilder();
            content.append("添加了政府站点：").append("id:").append(govMonitorPoint.getId()+";").append(govMonitorPoint.getName()+";").append(":").append("guid：").append(govMonitorPoint.getGuid()+";");
            logUtils.saveOperationForManage(request, content.toString(), Constants.INSERT_OPERATE_TYPE);
            content.append("添加了政府站点：").append("id:").append(govMonitorPoint.getId() + ";").append(govMonitorPoint.getName() + ";").append(":").append("guid：").append(govMonitorPoint.getGuid() + ";");
            LogUtils.saveOperationForManage(request, content.toString(), Constants.INSERT_OPERATE_TYPE);
        }
    }

    @Transactional
    @Override
    public void update(GovMonitorPoint govMonitorPoint) {
        Integer id = govMonitorPoint.getId();
        GovMonitorPoint oldGovMonitorPoint = govMonitorPointMapper.selectById(id);
        govMonitorPointMapper.updateById(govMonitorPoint);
        //删除redis
        delGovMonitorPointInfoFromRedis(id.toString());
        //更新redis
        setGovMonitorPointInfoToRedis(id.toString(), selectGovMonitorPointInfoById(id));
        //操作日志记录
        HttpServletRequest request = ((ServletRequestAttributes) Objects.requireNonNull(RequestContextHolder.getRequestAttributes())).getRequest();
        StringBuilder content = new StringBuilder();
        content.append("修改了政府站点：").append("id:").append(govMonitorPoint.getId() + ";");
        if (!ObjectUtils.isEmpty(govMonitorPoint.getGuid())) {
            content.append("guid:").append(oldGovMonitorPoint.getGuid()).append("->").append(govMonitorPoint.getGuid()).append(";");
        }
        if (!ObjectUtils.isEmpty(govMonitorPoint.getName())) {
            content.append("name:").append(oldGovMonitorPoint.getName()).append("->").append(govMonitorPoint.getName()).append(";");
        }
        if (!ObjectUtils.isEmpty(govMonitorPoint.getLongitude())) {
            content.append("longitude:").append(oldGovMonitorPoint.getLongitude()).append("->").append(govMonitorPoint.getLongitude()).append(";");
        }
        if (!ObjectUtils.isEmpty(govMonitorPoint.getLatitude())) {
            content.append("latitude:").append(oldGovMonitorPoint.getLatitude()).append("->").append(govMonitorPoint.getLatitude()).append(";");
        }
        if (!ObjectUtils.isEmpty(govMonitorPoint.getProvinceCode())) {
            content.append("provinceCode:").append(oldGovMonitorPoint.getProvinceCode()).append("->").append(govMonitorPoint.getProvinceCode()).append(";");
        }
        if (!ObjectUtils.isEmpty(govMonitorPoint.getCityCode())) {
            content.append("cityCode:").append(oldGovMonitorPoint.getCityCode()).append("->").append(govMonitorPoint.getCityCode()).append(";");
        }
        if (!ObjectUtils.isEmpty(govMonitorPoint.getAreaCode())) {
            content.append("areaCode:").append(oldGovMonitorPoint.getAreaCode()).append("->").append(govMonitorPoint.getAreaCode()).append(";");
        }
        if (!ObjectUtils.isEmpty(govMonitorPoint.getStationLevel())) {
            content.append("stationLevel:").append(oldGovMonitorPoint.getStationLevel()).append("->").append(govMonitorPoint.getStationLevel()).append(";");
        }
        if (!ObjectUtils.isEmpty(govMonitorPoint.getDesc())) {
            content.append("desc:").append(oldGovMonitorPoint).append("->").append(govMonitorPoint.getDesc()).append(";");
        }
        LogUtils.saveOperationForManage(request, content.toString(), Constants.UPDATE_OPERATE_TYPE);
    }

    @Override
    @Transactional
    public void updateList(Integer id, String guid) {
        ExecutorService executorService = Executors.newFixedThreadPool(2);
        QueryWrapper<GovMonitorPoint> wrapper_govMonitorPoint = new QueryWrapper<>();
        wrapper_govMonitorPoint.eq("is_delete", Constants.NOT_DELETE);
        wrapper_govMonitorPoint.eq("id", id);
        List<GovMonitorPoint> govMonitorPoints = govMonitorPointMapper.selectList(wrapper_govMonitorPoint);
        if (govMonitorPoints.size() == 0) {
            throw new BusinessException("站点不存在！");
        }
        GovMonitorPoint govMonitorPoint = govMonitorPoints.get(0);
        String oldGuid = govMonitorPoint.getGuid();
        LambdaQueryChainWrapper<Device> wrapper = deviceService.lambdaQuery();
        wrapper.eq(Device::getIsDelete,0);
        wrapper.eq(Device::getGuid,oldGuid);
        List<Device> list = wrapper.list();
        list.forEach(it->it.setGuid(guid));

        deviceService.updateBatchById(list);

        govMonitorPoint.setGuid(guid);
        govMonitorPointMapper.updateById(govMonitorPoint);
        //删除redis
        delGovMonitorPointInfoFromRedis(id.toString());
        //更新redis
        setGovMonitorPointInfoToRedis(id.toString(), selectGovMonitorPointInfoById(id));
        //操作日志记录
        HttpServletRequest request = ((ServletRequestAttributes) Objects.requireNonNull(RequestContextHolder.getRequestAttributes())).getRequest();
        StringBuilder content = new StringBuilder();
        content.append("修改了政府站点：").append("id:").append(govMonitorPoint.getId() + ";");
        if (!ObjectUtils.isEmpty(govMonitorPoint.getGuid())) {
            content.append("guid:").append(oldGuid).append("->").append(guid).append(";");
        }
        LogUtils.saveOperationForManage(request, content.toString(), Constants.UPDATE_OPERATE_TYPE);
        for(Device d : list){
            executorService.submit(()->{
                redisTemplate.opsForHash().delete(RedisConstants.DEVICE, d.getMac());
                Map<String, Object> deviceInfo = deviceService.selectDeviceInfoById(d.getId());
                redisTemplate.opsForHash().put(RedisConstants.DEVICE, d.getMac(), deviceInfo);

            });
        }
        // 关闭线程池
        executorService.shutdown();
    }

    @Override
    public void delete(Integer id) {
        UpdateWrapper<GovMonitorPoint> wrapper_delete = new UpdateWrapper<>();
        wrapper_delete.eq("id", id).set("is_delete", Constants.DELETE);
        govMonitorPointMapper.update(null, wrapper_delete);
        //删除redis
        delGovMonitorPointInfoFromRedis(id.toString());
        //操作日志记录
        HttpServletRequest request = ((ServletRequestAttributes) Objects.requireNonNull(RequestContextHolder.getRequestAttributes())).getRequest();
        StringBuilder content = new StringBuilder();
        content.append("删除了设备：").append("id:").append(id).append(";");
        LogUtils.saveOperationForManage(request, content.toString(), Constants.DELETE_OPERATE_TYPE);
    }

    @Override
@@ -157,4 +286,33 @@
        GovMonitorPoint govMonitorPoint = govMonitorPointMapper.selectById(id);
        return govMonitorPoint;
    }

    @Override
    public List<Map<String, Object>> selectGovMonitorPoints(String regionCode) {
        QueryWrapper<GovMonitorPoint> queryWrapper = new QueryWrapper<>();
        queryWrapper.select("guid", "name").eq("is_delete", Constants.NOT_DELETE);
        if (!ObjectUtils.isEmpty(regionCode)) {
            String regionName = RegionCodeUtils.regionCodeConvertToName(Integer.parseInt(regionCode));
            queryWrapper.eq(regionName, regionCode);
        }
        return govMonitorPointMapper.selectMaps(queryWrapper);
    }

    @Override
    public List<GovMonitorPoint> selectGovMonitorPointsByOrgid(Map map) {
        //根据组织id获取子组织
        List<Organization> organizations = organizationService.getAllChildrenOrganization(Integer.parseInt(map.get("organization_id").toString()));
        Set<Integer> organization_ids = organizations.stream().map(organization -> organization.getId()).collect(Collectors.toSet());
        //先获取组织下所有设备
        QueryWrapper<Device> wrapper_device = new QueryWrapper<>();
        wrapper_device.in("organization_id",organization_ids).eq("is_delete",Constants.NOT_DELETE);
        List<Device> devices = deviceMapper.selectList(wrapper_device);
        //用集合存放所有设备的id
        Set<String> guids = devices.stream().map(device -> device.getGuid()).collect(Collectors.toSet());
        //获取所有政府站点信息
        QueryWrapper<GovMonitorPoint> wrapper_govMonitorPoint = new QueryWrapper<>();
        wrapper_govMonitorPoint.eq("is_delete",Constants.NOT_DELETE).in("guid",guids);
        List<GovMonitorPoint> govMonitorPointList = govMonitorPointMapper.selectList(wrapper_govMonitorPoint);
        return govMonitorPointList;
    }
}