fix：sql查询时间调整

cjl

2025-01-06 8490eb11583ffdc586436f165860de5cfc39f915

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

@@ -1,22 +1,40 @@
package com.moral.api.service.impl;

import lombok.extern.slf4j.Slf4j;
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.Date;
import java.util.List;
import java.util.Map;
import com.alibaba.fastjson.JSONArray;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.moral.api.entity.ManageAccount;
import com.moral.api.entity.ManageCoordinate;
import com.moral.api.entity.ManageCoordinateDetail;
import com.moral.api.entity.SpecialDevice;
import com.moral.api.mapper.HistorySecondCruiserMapper;
import com.moral.api.mapper.ManageCoordinateDetailMapper;
import com.moral.api.mapper.SpecialDeviceMapper;
import com.moral.api.pojo.dto.cruiser.CruiserDTO;
import com.moral.api.pojo.redisBean.AccountInfoDTO;
import com.moral.api.service.ManageCoordinateDetailService;
import com.moral.api.util.RoadUtils;
import com.moral.constant.RedisConstants;
import com.moral.util.DateUtils;
import com.moral.util.TokenUtils;
import lombok.extern.slf4j.Slf4j;
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.util.CollectionUtils;
import org.springframework.util.ObjectUtils;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;
import javax.servlet.http.HttpServletRequest;

import java.awt.geom.Point2D;
import java.util.*;
import java.util.concurrent.TimeUnit;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;


@Service
@@ -29,38 +47,63 @@

    @Autowired
    private HistorySecondCruiserMapper historySecondCruiserMapper;

    @Autowired
    private RedisTemplate redisTemplate;

    @Autowired
    private SpecialDeviceMapper specialDeviceMapper;
    /**
     * 新增经纬度
     * @param manageCoordinateDetail
     * @param params
     * @return
     */
    @Override
    public Integer insertCoordinate(ManageCoordinateDetail manageCoordinateDetail) {
        double[] doubles = RoadUtils.transformBD09ToWGS84(manageCoordinateDetail.getLongitude(), manageCoordinateDetail.getLatitude());
        log.info(doubles[0]+"_"+doubles[1]);
        QueryWrapper<ManageCoordinateDetail> queryWrapper = new QueryWrapper<>();
        queryWrapper.eq("coordinate_id",manageCoordinateDetail.getCoordinateId());
        queryWrapper.eq("longitude",doubles[0]);
        queryWrapper.eq("latitude",doubles[1]);
        Integer integer = manageCoordinateDetailMapper.selectCount(queryWrapper);
        if (integer<1){
            manageCoordinateDetail.setCreateTime(new Date());
            manageCoordinateDetail.setUpdateTime(new Date());
            manageCoordinateDetail.setLongitude(doubles[0]);
            manageCoordinateDetail.setLatitude(doubles[1]);
            int insert = manageCoordinateDetailMapper.insert(manageCoordinateDetail);
            return insert;
        }
        return null;
    }
    @Transactional
    public Integer insertCoordinate(Map<String,Object> params) {
        HttpServletRequest request = ((ServletRequestAttributes) Objects.requireNonNull(RequestContextHolder.getRequestAttributes())).getRequest();
        String token = request.getHeader("token");
        AccountInfoDTO accountInfoDTO = (AccountInfoDTO) TokenUtils.getUserInfoByToken(token);
        ManageAccount manageAccount = accountInfoDTO.getAccount();
        Integer id = manageAccount.getId();
        String userName = manageAccount.getUserName();

    /**
     * 删除经纬度点
     * @param id
     */
    @Override
    public void deleteCoordinate(Integer id) {
        manageCoordinateDetailMapper.deleteById(id);
        int coordinateId = Integer.parseInt(params.get("coordinateId").toString());
        List<Map<String, Object>> data = (List<Map<String, Object>>) params.get("data");

        for (Map<String, Object> datum : data) {

            String code = datum.get("code").toString();
            String state = datum.get("state").toString();

            if (state.equals("2")){
                ManageCoordinateDetail manageCoordinateDetail = new ManageCoordinateDetail();
                String[] rs = code.split("_");
                manageCoordinateDetail.setCoordinateId(coordinateId);
                manageCoordinateDetail.setLongitude(Double.parseDouble(rs[0]));
                manageCoordinateDetail.setLatitude(Double.parseDouble(rs[1]));
                manageCoordinateDetail.setState("2");
                manageCoordinateDetail.setUpdateUserId(id);
                manageCoordinateDetail.setUpdayeUserName(userName);
                QueryWrapper<ManageCoordinateDetail> wrapper = new QueryWrapper<>();
                wrapper.eq("coordinate_id",coordinateId);
                wrapper.eq("longitude",manageCoordinateDetail.getLongitude());
                wrapper.eq("latitude",manageCoordinateDetail.getLatitude());
                Integer integer = manageCoordinateDetailMapper.selectCount(wrapper);
                if (integer>=1){
                    return 1;
                }
                manageCoordinateDetailMapper.insert(manageCoordinateDetail);
            }else {
                Object id1 = datum.get("id");
                if (ObjectUtils.isEmpty(id1)){
                    return 2;
                }
                manageCoordinateDetailMapper.deleteById(Integer.parseInt(id1.toString()));

            }
        }
        return 200;
    }


@@ -70,33 +113,83 @@
     * @return
     */
    @Override
    public Map<String, Object> selectCoordinate(Map<String, Object> params) {
    public Map<String,Object> selectCoordinate(Map<String, Object> params) {
        HashMap<String,Object> rsMap = new HashMap<>();
        String mac = params.get("mac").toString();
        String time1 = params.get("time1").toString();
        ArrayList<ManageCoordinateDetail> rsList1 = new ArrayList<>();
        params.put("dateFormat", "%Y-%m-%d %H:%i:%s");
        String coordinateId = params.get("coordinateId").toString();
        QueryWrapper<ManageCoordinateDetail> queryWrapper = new QueryWrapper<>();
        queryWrapper.eq("coordinate_id",coordinateId);
        List<ManageCoordinateDetail> manageCoordinateDetails = manageCoordinateDetailMapper.selectList(queryWrapper);
        ArrayList<String> list1 = new ArrayList<>();
        ArrayList<String> list2 = new ArrayList<>();
        for (ManageCoordinateDetail manageCoordinateDetail : manageCoordinateDetails) {
            Double latitude = manageCoordinateDetail.getLatitude();
            Double longitude = manageCoordinateDetail.getLongitude();
            String s = longitude + "_" + latitude;
            list1.add(s);
        }
        List<CruiserDTO> cruiserInfo = historySecondCruiserMapper.getCruiserInfo(params);
        for (CruiserDTO cruiserDTO : cruiserInfo) {
            String s = cruiserDTO.getFlyLat().toString();
            String s1 = cruiserDTO.getFlyLon().toString();
            String rs = s1 + "_" + s;
            list2.add(rs);
        }
        //取交集
        list1.retainAll(list2);
        //取差集
        list2.removeAll(list1);
        List<CruiserDTO> cruiserInfo = (List<CruiserDTO>) redisTemplate.opsForHash().get(RedisConstants.DATE_COORDINATE, mac + time1);
        if (ObjectUtils.isEmpty(cruiserInfo)){
            cruiserInfo = historySecondCruiserMapper.getCruiserInfo(params);
            cruiserInfo = cruiserInfo.stream().distinct().collect(Collectors.toList());
            cruiserInfo.removeIf(o->{
                    if (ObjectUtils.isEmpty(o.getFlyLon()) || ObjectUtils.isEmpty(o.getFlyLat())) {
                        return true;
                    }
                    double lon = o.getFlyLon();
                    double lat = o.getFlyLat();
                    double[] doubles = RoadUtils.transformWGS84ToBD09(lon, lat);
                    Matcher matcher = Pattern.compile("\\d*\\.\\d{8}").matcher(""+doubles[0]);
                    matcher.find();
                    String s = matcher.group();
                    Matcher matcher1 = Pattern.compile("\\d*\\.\\d{8}").matcher(""+doubles[1]);
                    matcher1.find();
                    String s1 = matcher1.group();
                    o.setFlyLon(Double.parseDouble(s));
                    o.setFlyLat(Double.parseDouble(s1));
                    o.setData(lon+"_"+lat);
//            o.setFlyLon(doubles[0]);
//            o.setFlyLat(doubles[1]);
                    if (lon < 70 || lon > 150 || lat < 20 || lat > 60) {
                        return true;
                    }
                    return false;
                });

        return null;
                redisTemplate.opsForHash().put(RedisConstants.DATE_COORDINATE,mac+time1,cruiserInfo);
                //设置过期时间
                redisTemplate.opsForHash().getOperations().expire(RedisConstants.DATE_COORDINATE,6000, TimeUnit.SECONDS);

        }
//        List<CruiserDTO> cruiserInfo = historySecondCruiserMapper.getCruiserInfo(params);
        if (ObjectUtils.isEmpty(manageCoordinateDetails)){
            rsMap.put("rsData",cruiserInfo);
            rsMap.put("data",rsList1);
            return rsMap;
        }
        for (int i=cruiserInfo.size()-1;i>0;i--) {
            CruiserDTO cruiserDTO = cruiserInfo.get(i);
            String data = cruiserDTO.getData();
            String[] rs = data.split("_");
            String flyLon = rs[0];
            String flyLat = rs[1];
//            double lonDouble = Double.parseDouble(flyLon);
//            double latDouble = Double.parseDouble(flyLat);
            for (ManageCoordinateDetail manageCoordinateDetail : manageCoordinateDetails) {
                String latitude = manageCoordinateDetail.getLatitude().toString();
                String longitude = manageCoordinateDetail.getLongitude().toString();
//                double lonDouble1 = Double.parseDouble(longitude);
//                double latDouble1 = Double.parseDouble(latitude);
//                if (latDouble1==latDouble && lonDouble==lonDouble1){
                if (flyLon.equals(longitude) && flyLat.equals(latitude)){
                    manageCoordinateDetail.setCode(data);
                    manageCoordinateDetail.setLongitude(cruiserDTO.getFlyLon());
                    manageCoordinateDetail.setLatitude(cruiserDTO.getFlyLat());
                    rsList1.add(manageCoordinateDetail);
                    cruiserInfo.remove(i);
                    break;
                }
            }

        }
        rsMap.put("rsData",cruiserInfo);
        rsMap.put("data",rsList1);
        return rsMap;
    }


@@ -109,28 +202,237 @@
    @Transactional
    public Integer batchAll(Map<String, Object> params) {
        params.put("dateFormat", "%Y-%m-%d %H:%i:%s");
        String coordinateId = params.get("coordinateId").toString();
        String type = params.get("type").toString();
        int coordinateId = Integer.parseInt(params.get("coordinateId").toString());
        QueryWrapper<ManageCoordinateDetail> queryWrapper = new QueryWrapper<>();
        queryWrapper.eq("coordinate_id",coordinateId);
        int j =0;
        List<ManageCoordinateDetail> manageCoordinateDetails = manageCoordinateDetailMapper.selectList(queryWrapper);
        ArrayList<String> list1 = new ArrayList<>();
        ArrayList<String> list2 = new ArrayList<>();
        for (ManageCoordinateDetail manageCoordinateDetail : manageCoordinateDetails) {
            Double latitude = manageCoordinateDetail.getLatitude();
            Double longitude = manageCoordinateDetail.getLongitude();
            String s = longitude + "_" + latitude;
            list1.add(s);
        }
        List<CruiserDTO> cruiserInfo = historySecondCruiserMapper.getCruiserInfo(params);
        for (CruiserDTO cruiserDTO : cruiserInfo) {
            String s = cruiserDTO.getFlyLat().toString();
            String s1 = cruiserDTO.getFlyLon().toString();
            String rs = s1 + "_" + s;
            list2.add(rs);
        }
        //取差集
        list2.removeAll(list1);
//        List<CruiserDTO> cruiserInfo = (List<CruiserDTO>) params.get("list");
        List<CruiserDTO> cruiserInfo;
        //区域选择
        if (type.equals("1")){
            List<HashMap<String, Object>> list = (List<HashMap<String, Object>>) params.get("list");
             cruiserInfo = list.stream().distinct()
                    .map(map -> {

        return null;
                        CruiserDTO p = new CruiserDTO();
                        Double flylat = (Double) map.get("flylat");
                        Double flylon = (Double) map.get("flylon");
                        p.setFlyLat(flylat);
                        p.setFlyLon(flylon);
                        return p;
                    })
                    .collect(Collectors.toList());

        }else {
            //时间段选择
            String time1 = params.remove("time1").toString();
            String time2 = params.remove("time2").toString();
            long l = DateUtils.compareDateStrDay(time1, time2);
            if (l>0){
                params.put("time1",time1);
                params.put("time2",time2);
            }else {
                params.put("time1",time2);
                params.put("time2",time1);
            }
            cruiserInfo = historySecondCruiserMapper.getCruiserInfo(params);
            cruiserInfo = cruiserInfo.stream().distinct().collect(Collectors.toList());
        }
        if (ObjectUtils.isEmpty(manageCoordinateDetails)){
            for (CruiserDTO cruiserDTO : cruiserInfo) {
                Double flyLat = cruiserDTO.getFlyLat();
                Double flyLon = cruiserDTO.getFlyLon();
                if (ObjectUtils.isEmpty(flyLat) || ObjectUtils.isEmpty(flyLon)){
                    continue;
                }
                ManageCoordinateDetail rsDTO = new ManageCoordinateDetail();
                rsDTO.setLatitude(flyLat);
                rsDTO.setLongitude(flyLon);
                rsDTO.setState("2");
                rsDTO.setCreateTime(new Date());
                rsDTO.setUpdateTime(new Date());
                rsDTO.setCoordinateId(coordinateId);
                manageCoordinateDetailMapper.insert(rsDTO);
                j++;
                log.info(j+"");
            }
        }else {
            Map<String,Integer> map = new HashMap<>(manageCoordinateDetails.size()+50);
            manageCoordinateDetails.forEach(it->map.put(it.getLatitude()+"_"+it.getLongitude(),it.getId()));
            List<ManageCoordinateDetail> result = new ArrayList<>();
            for (CruiserDTO cruiserDTO : cruiserInfo) {
                String fly = cruiserDTO.getFlyLat()+"_"+cruiserDTO.getFlyLon();
               if(!map.containsKey(fly)){
                   ManageCoordinateDetail rsDTO = new ManageCoordinateDetail();
                   rsDTO.setLatitude(cruiserDTO.getFlyLat());
                   rsDTO.setLongitude(cruiserDTO.getFlyLon());
                   rsDTO.setState("2");
                   rsDTO.setCreateTime(new Date());
                   rsDTO.setUpdateTime(new Date());
                   rsDTO.setCoordinateId(coordinateId);
                   result.add(rsDTO);
               }
            }
            if(!CollectionUtils.isEmpty(result)){
                this.saveBatch(result);
            }
        }
        return 200;
    }

    /**
     * 所有数据批量添加
     *
     * @param params
     */
    @Override
    public void queryAll(Map<String, Object> params) {
        //获取路段信息
        List<ManageCoordinate> coordinates = specialDeviceMapper.selectCoordinate(params.get("mac").toString());
        //获取走航车数据
        List<CruiserDTO> cruiserInfo = historySecondCruiserMapper.getCruiserInfo(params);
        cruiserInfo = cruiserInfo.stream().distinct().collect(Collectors.toList());
        List<ManageCoordinateDetail> result = new ArrayList<>();
            for (ManageCoordinate coordinate : coordinates) {
                String value = coordinate.getValue();
                if (!ObjectUtils.isEmpty(value)){
                    List<Map<String, Object>> parse = (List<Map<String, Object>>) JSONArray.parse(value);

                    for (int i = 0; i < cruiserInfo.size(); i++) {
                        CruiserDTO cruiserDTO = cruiserInfo.get(i);
                        if (ObjectUtils.isEmpty(cruiserDTO)){
                            continue;
                        }
                        Double flyLat = cruiserDTO.getFlyLat();
                        Double flyLon = cruiserDTO.getFlyLon();
                        if (ObjectUtils.isEmpty(flyLat)||ObjectUtils.isEmpty(flyLon)){
                            continue;
                        }
                        boolean inPolygon = isInPolygon(flyLon, flyLat, parse);
                       if (inPolygon){
                           LambdaQueryWrapper<ManageCoordinateDetail> wr = new LambdaQueryWrapper<>();
                           wr.eq(ManageCoordinateDetail::getLongitude,flyLon);
                           wr.eq(ManageCoordinateDetail::getLatitude,flyLat);
                           List<ManageCoordinateDetail> manageCoordinateDetails = manageCoordinateDetailMapper.selectList(wr);
                           if (ObjectUtils.isEmpty(manageCoordinateDetails)){
                               ManageCoordinateDetail rsDTO = new ManageCoordinateDetail();
                               rsDTO.setLatitude(flyLat);
                               rsDTO.setLongitude(flyLon);
                               rsDTO.setState("2");
                               rsDTO.setCreateTime(new Date());
                               rsDTO.setUpdateTime(new Date());
                               rsDTO.setCoordinateId(coordinate.getCoordinateId());
                               result.add(rsDTO);
                               cruiserInfo.remove(i);
                               i--;
                           }
                       }
                    }

                }
          }
        if(!CollectionUtils.isEmpty(result)){
            this.saveBatch(result);
        }



    }


    /**
     * 判断当前位置是否在多边形区域内
     * @param
     * @param mapList 区域顶点
     * @return
     */
    public static boolean isInPolygon(Double X,Double Y,List<Map<String, Object>> mapList){


        Point2D.Double point = new Point2D.Double(X, Y);
        List<Point2D.Double> pointList= new ArrayList<Point2D.Double>();

        for (Map<String, Object> param : mapList) {
            //纬度
            String lat = param.get("lat").toString();
            //经度
            String lng = param.get("lng").toString();
            Point2D.Double polygonPoint = new Point2D.Double(Double.parseDouble(lng),Double.parseDouble(lat));
            pointList.add(polygonPoint);
        }
        return IsPtInPoly(point,pointList);

    }

    /**
     * 判断点是否在多边形内，如果点位于多边形的顶点或边上，也算做点在多边形内，直接返回true
     * @param point 检测点
     * @param pts   多边形的顶点
     * @return      点在多边形内返回true,否则返回false
     */
    public static boolean IsPtInPoly(Point2D.Double point, List<Point2D.Double> pts){

        int N = pts.size();
        boolean boundOrVertex = true; //如果点位于多边形的顶点或边上，也算做点在多边形内，直接返回true
        int intersectCount = 0;//cross points count of x
        double precision = 2e-10; //浮点类型计算时候与0比较时候的容差
        Point2D.Double p1, p2;//neighbour bound vertices
        Point2D.Double p = point; //当前点

        p1 = pts.get(0);//left vertex
        for(int i = 1; i <= N; ++i){//check all rays
            if(p.equals(p1)){
                return boundOrVertex;//p is an vertex
            }

            p2 = pts.get(i % N);
            if(p.x < Math.min(p1.x, p2.x) || p.x > Math.max(p1.x, p2.x)){
                p1 = p2;
                continue;
            }

            if(p.x > Math.min(p1.x, p2.x) && p.x < Math.max(p1.x, p2.x)){
                if(p.y <= Math.max(p1.y, p2.y)){
                    if(p1.x == p2.x && p.y >= Math.min(p1.y, p2.y)){
                        return boundOrVertex;
                    }

                    if(p1.y == p2.y){
                        if(p1.y == p.y){
                            return boundOrVertex;
                        }else{//before ray
                            ++intersectCount;
                        }
                    }else{
                        double xinters = (p.x - p1.x) * (p2.y - p1.y) / (p2.x - p1.x) + p1.y;
                        if(Math.abs(p.y - xinters) < precision){
                            return boundOrVertex;
                        }

                        if(p.y < xinters){
                            ++intersectCount;
                        }
                    }
                }
            }else{
                if(p.x == p2.x && p.y <= p2.y){
                    Point2D.Double p3 = pts.get((i+1) % N);
                    if(p.x >= Math.min(p1.x, p3.x) && p.x <= Math.max(p1.x, p3.x)){
                        ++intersectCount;
                    }else{
                        intersectCount += 2;
                    }
                }
            }
            p1 = p2;
        }

        if(intersectCount % 2 == 0){//偶数在多边形外
            return false;
        } else { //奇数在多边形内
            return true;
        }
    }
}