图斑数据导出，优化面状图斑的数据搜索

rain

2024-07-31 4e8892f75a0d9b76e147cccdb1f188c272aed4c5

图斑数据导出，优化面状图斑的数据搜索

 pom.xml

@@ -300,6 +300,18 @@
            <artifactId>xmpcore</artifactId>
            <version>5.1.3</version>
        </dependency>

        <!--excel表格数据导出-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>5.2.3</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>5.2.3</version>
        </dependency>
    </dependencies>



 src/main/java/com/dji/sample/media/controller/FileController.java

@@ -112,10 +112,11 @@
                                       @RequestParam(name = "updateStart", required = false) Long photoStart,
                                       @RequestParam(name = "updateEnd", required = false) Long photoEnd,
                                       @RequestParam(name = "jobName", required = false) String jobName,
                                       @RequestParam(name = "jobId", required = false) String jobId,
                                       @RequestParam(required = false) String type
    ) {

        return ResponseResult.success(fileService.mediaQuery(page, pageSize, updateStart, updateEnd, photoStart, photoEnd, jobName, workspaceId, type));
        return ResponseResult.success(fileService.mediaQuery(page, pageSize, updateStart, updateEnd, photoStart, photoEnd, jobName, workspaceId, type,jobId));

    }


 src/main/java/com/dji/sample/media/service/IFileService.java

@@ -59,7 +59,7 @@
     */
    List<MediaFileDTO> getAllFilesByWorkspaceId(String workspaceId);

    PaginationData<MediaFileEntity> mediaQuery(Integer page, Integer pageSize, Long updateStart, Long updateEnd, Long photoStart, Long photoEnd, String jobName,String worksapceId,String isVedio);
    PaginationData<MediaFileEntity> mediaQuery(Integer page, Integer pageSize, Long updateStart, Long updateEnd, Long photoStart, Long photoEnd, String jobName,String worksapceId,String isVedio,String jobId);

    PaginationData<MediaFileNailEntity> mediaNailQuery(Integer page, Integer pageSize, Long updateStart, Long updateEnd, Long photoStart, Long photoEnd, String jobName, String workspaceId, String type);
    /**

 src/main/java/com/dji/sample/media/service/impl/FileServiceImpl.java

@@ -245,7 +245,7 @@
    }

    @Override
    public PaginationData<MediaFileEntity> mediaQuery(Integer page, Integer pageSize, Long updateStart, Long updateEnd, Long photoStart, Long photoEnd, String jobName, String workspaceId, String type) {
    public PaginationData<MediaFileEntity> mediaQuery(Integer page, Integer pageSize, Long updateStart, Long updateEnd, Long photoStart, Long photoEnd, String jobName, String workspaceId, String type,String jobId) {
        // 创建查询条件对象
        LambdaQueryWrapper<MediaFileEntity> queryWrapper = new LambdaQueryWrapper<>();

@@ -266,6 +266,9 @@
                return new PaginationData<>(Collections.emptyList(), new Pagination(new Page<>(page, pageSize)));
            }
            queryWrapper.in(MediaFileEntity::getJobId, jobIds);
        }
        if (jobId != null && !jobId.isEmpty()) {
            queryWrapper.eq(MediaFileEntity::getJobId,jobId);
        }

        if (type != null && !type.isEmpty()) {
@@ -932,72 +935,52 @@
        }
    }

    @Override

    public PaginationData<MediaFileEntity> getJobId(int pageNum, int pageSize, String workspaceId) {
        List<MediaFileEntity> allRecords = new ArrayList<>();
        int currentPageNum = 1;  // 从第一页开始获取记录
        // 分页查询
        Page<MediaFileEntity> page = new Page<>(pageNum, pageSize);

        // 用于存储去重后的记录
        Map<String, MediaFileEntity> uniqueFilesMap = new HashMap<>();
        // 使用 QueryWrapper 来执行 DISTINCT 查询，并且选择 payload 和 create_time 字段
        QueryWrapper<MediaFileEntity> queryWrapper = new QueryWrapper<>();
        queryWrapper
                .select("DISTINCT job_id", "payload", "create_time")  // 选择 job_id, payload 和 create_time 字段
                .eq("workspace_id", workspaceId)
                .orderByDesc("create_time");

        while (true) {
            // 分页查询
            Page<MediaFileEntity> page = new Page<>(currentPageNum, pageSize);
            Page<MediaFileEntity> resultPage = mapper.selectPage(page, new LambdaQueryWrapper<MediaFileEntity>()
                    .eq(MediaFileEntity::getWorkspaceId, workspaceId));
        Page<MediaFileEntity> resultPage = mapper.selectPage(page, queryWrapper);
        List<MediaFileEntity> result = resultPage.getRecords();

            List<MediaFileEntity> result = resultPage.getRecords();

            // 检查是否还有更多记录
            if (result.isEmpty()) {
                break;  // 没有更多记录了，停止请求
            }

            // 根据 fileName 字段进行去重并设置 JobName
            result.stream()
                    .peek(mediaFile -> {
                        String taskNameResult = waylineJobService.getName(mediaFile.getJobId());
                        mediaFile.setJobName(taskNameResult);
                    })
                    .forEach(mediaFile -> uniqueFilesMap.putIfAbsent(mediaFile.getFileName(), mediaFile));

            // 增加当前页数以获取更多记录
            currentPageNum++;
        // 为每个 MediaFileEntity 设置 jobName
        for (MediaFileEntity mediaFile : result) {
            String taskNameResult = waylineJobService.getName(mediaFile.getJobId());
            mediaFile.setJobName(taskNameResult);
        }

        // 获取去重后的实际总数
        List<MediaFileEntity> uniqueFiles = new ArrayList<>(uniqueFilesMap.values());
        int uniqueTotal = uniqueFiles.size();

        // 计算当前页的起始和结束索引
        int fromIndex = Math.min((pageNum - 1) * pageSize, uniqueTotal);
        int toIndex = Math.min(fromIndex + pageSize, uniqueTotal);

        // 获取当前页的数据
        List<MediaFileEntity> pagedUniqueFiles = uniqueFiles.subList(fromIndex, toIndex);

        // 创建新的分页对象，使用去重后的总数
        Page<MediaFileEntity> uniquePage = new Page<>(pageNum, pageSize, uniqueTotal);

        // 使用新的分页对象构造 Pagination
        Pagination pagination = new Pagination(uniquePage);

        return new PaginationData<>(pagedUniqueFiles, pagination);
        // 使用分页对象构造 Pagination
        Pagination pagination = new Pagination(resultPage);
        return new PaginationData<>(result, pagination);
    }





    @Override
    public PaginationData<MediaFileEntity> getPhotoByJobId(int pageNum, int pageSize, String workspaceId, String jobId) {

        // 分页查询
        Page<MediaFileEntity> page = new Page<>(pageNum, pageSize);
        Page<MediaFileEntity> resultPage = mapper.selectPage(page, new LambdaQueryWrapper<MediaFileEntity>()
                .eq(MediaFileEntity::getJobId, jobId)
                .eq(MediaFileEntity::getWorkspaceId, workspaceId));

        // 使用 QueryWrapper 来执行查询
        LambdaQueryWrapper<MediaFileEntity> queryWrapper = new LambdaQueryWrapper<>();

        // 添加查询条件
        queryWrapper.eq(MediaFileEntity::getWorkspaceId, workspaceId);
        queryWrapper.eq(MediaFileEntity::getJobId,jobId);
        queryWrapper.last("ORDER BY JSON_EXTRACT(metadata, '$.createdTime') DESC");
        Page<MediaFileEntity> resultPage = mapper.selectPage(page, queryWrapper);
        List<MediaFileEntity> result = resultPage.getRecords();

        // 根据 fileName 字段进行去重
        // 根据 file_name 字段进行去重
        Map<String, MediaFileEntity> uniqueFilesMap = result.stream()
                .collect(Collectors.toMap(
                        MediaFileEntity::getFileName,
@@ -1007,24 +990,24 @@

        List<MediaFileEntity> uniqueFiles = new ArrayList<>(uniqueFilesMap.values());

        // 使用去重后的列表重新计算总数
        int uniqueTotal = uniqueFiles.size();
        // 为每个 MediaFileEntity 设置 jobName
        for (MediaFileEntity mediaFile : uniqueFiles) {
            String taskNameResult = waylineJobService.getName(mediaFile.getJobId());
            mediaFile.setJobName(taskNameResult);
        }

        // 创建新的分页对象，使用去重后的总数
        Page<MediaFileEntity> uniquePage = new Page<>(pageNum, pageSize, uniqueTotal);
        // 使用分页对象构造 Pagination
        Page<MediaFileEntity> newPage = new Page<>(pageNum, pageSize);
        newPage.setRecords(uniqueFiles);
        newPage.setTotal(uniqueFiles.size()); // 设置总记录数为去重后的记录数

        // 截取当前页的数据
        int fromIndex = Math.min((pageNum - 1) * pageSize, uniqueTotal);
        int toIndex = Math.min(fromIndex + pageSize, uniqueTotal);
        List<MediaFileEntity> pagedUniqueFiles = uniqueFiles.subList(fromIndex, toIndex);

        // 使用新的分页对象构造 Pagination
        Pagination pagination = new Pagination(uniquePage);

        return new PaginationData<>(pagedUniqueFiles, pagination);
        Pagination pagination = new Pagination(newPage);
        return new PaginationData<>(uniqueFiles, pagination);
    }




    public List<MediaFileEntity> getMedia(String jobId) {
        return mapper.selectList(new LambdaQueryWrapper<MediaFileEntity>()
                .eq(MediaFileEntity::getJobId, jobId)

 src/main/java/com/dji/sample/patches/controller/PatchesController.java

@@ -10,19 +10,21 @@
import com.dji.sample.patches.model.entity.LotInfo;
import com.dji.sample.patches.service.GetPatchesService;
import com.dji.sample.patches.service.ShpToDataSourceService;
import com.dji.sample.patches.utils.DistrictCodeUtils;
import com.dji.sample.patches.utils.GeoToolsUtil;
import com.dji.sample.patches.utils.PointPO;
import com.dji.sample.patches.utils.TimerUtil;
import com.dji.sample.patches.utils.*;
import com.dji.sample.territory.service.impl.TbDkjbxxServiceImpl;
import com.dji.sample.wayline.model.entity.WaylineFileEntity;
import com.dji.sample.wayline.service.IWaylineFileService;
import com.dji.sample.wayline.service.IWaylineJobService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.net.URL;
import java.sql.SQLException;
@@ -269,4 +271,24 @@
        }
        return ResponseResult.success(infoMap);
    }

    @GetMapping("/getExcel")
    public ResponseEntity<byte[]> exportExcel(@RequestParam String workspaceId) {
        List<LotInfo> lotInfos = getPatchesService.listLotinfo(workspaceId);
        try {
            ByteArrayInputStream excelStream = ExcelUtil.exportLotInfoToExcel(lotInfos);
            byte[] excelData = excelStream.readAllBytes();
            excelStream.close();

            HttpHeaders headers = new HttpHeaders();
            headers.add(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=lot_info_data.xlsx");
            headers.add(HttpHeaders.CONTENT_TYPE, MediaType.APPLICATION_OCTET_STREAM_VALUE);
            headers.setContentLength(excelData.length);

            return new ResponseEntity<>(excelData, headers, HttpStatus.OK);
        } catch (IOException e) {
            e.printStackTrace();
            return new ResponseEntity<>(("Failed to generate Excel file.").getBytes(), HttpStatus.INTERNAL_SERVER_ERROR);
        }
    }
}

 src/main/java/com/dji/sample/patches/service/GetPatchesService.java

@@ -44,7 +44,7 @@
     */
     LotInfo getLotinfo(String dkbh,String workspaceId);

    List<LotInfo>listLotinfo();
    List<LotInfo>listLotinfo(String workspaceID);

    void insertLotinfo(List<LotInfo> list);


 src/main/java/com/dji/sample/patches/service/impl/GetPatchesServiceImpl.java

@@ -187,8 +187,8 @@
        queryWrapper.in(LotInfo::getId, ids);
        return mapper.selectList(queryWrapper);
    }
    public List<LotInfo> listLotinfo() {
        return mapper.selectList(null);
    public List<LotInfo> listLotinfo(String workspaceId) {
        return mapper.selectList(new LambdaQueryWrapper<LotInfo>().eq(LotInfo::getWorkspaceId,workspaceId));
    }
    @Override
    public LotInfo getPatchesFromId(String patchesId){

 src/main/java/com/dji/sample/patches/utils/ExcelUtil.java

New file
@@ -0,0 +1,74 @@
package com.dji.sample.patches.utils;

import com.dji.sample.patches.model.entity.LotInfo;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.List;

public class ExcelUtil {

        public static ByteArrayInputStream exportLotInfoToExcel(List<LotInfo> lotInfos) throws IOException {
            // 创建 Excel 工作簿和工作表
            Workbook workbook = new XSSFWorkbook();
            Sheet sheet = workbook.createSheet("LotInfo");

            // 写入表头
            Row headerRow = sheet.createRow(0);
            String[] headers = {
                    "ID", "Workspace ID", "BSM", "DKLX", "XZQDM", "XMC", "DKBH", "DKMC", "DKMJ", "SFBHZDK",
                    "XZB", "YZB", "BZ", "KZXX", "DKFW", "SJLX", "Is Plan", "Task Name", "Task ID", "Investigate",
                    "Is Push", "User Name", "Create Time", "Update Time"
            };
            for (int i = 0; i < headers.length; i++) {
                Cell cell = headerRow.createCell(i);
                cell.setCellValue(headers[i]);
            }

            // 日期格式化
            SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

            // 写入数据
            int rowCount = 1;
            for (LotInfo lotInfo : lotInfos) {
                Row row = sheet.createRow(rowCount++);
                row.createCell(0).setCellValue(lotInfo.getId());
                row.createCell(1).setCellValue(lotInfo.getWorkspaceId());
                row.createCell(2).setCellValue(lotInfo.getBsm());
                row.createCell(3).setCellValue(lotInfo.getDklx());
                row.createCell(4).setCellValue(lotInfo.getXzqdm());
                row.createCell(5).setCellValue(lotInfo.getXmc());
                row.createCell(6).setCellValue(lotInfo.getDkbh());
                row.createCell(7).setCellValue(lotInfo.getDkmc());
                row.createCell(8).setCellValue(lotInfo.getDkmj() != null ? lotInfo.getDkmj() : 0.0);
                row.createCell(9).setCellValue(lotInfo.getSfbhzdk());
                row.createCell(10).setCellValue(lotInfo.getXzb() != null ? lotInfo.getXzb() : 0.0);
                row.createCell(11).setCellValue(lotInfo.getYzb() != null ? lotInfo.getYzb() : 0.0);
                row.createCell(12).setCellValue(lotInfo.getBz());
                row.createCell(13).setCellValue(lotInfo.getKzxx());
                row.createCell(14).setCellValue(lotInfo.getDkfw());
                row.createCell(15).setCellValue(lotInfo.getSjlx());
                row.createCell(16).setCellValue(lotInfo.getIsPlan());
                row.createCell(17).setCellValue(lotInfo.getTaskName());
                row.createCell(18).setCellValue(lotInfo.getTaskId());
                row.createCell(19).setCellValue(lotInfo.getInvestigate());
                row.createCell(20).setCellValue(lotInfo.getIsPush());
                row.createCell(21).setCellValue(lotInfo.getUserName());
                row.createCell(22).setCellValue(lotInfo.getCreateTime() != null ? dateFormat.format(lotInfo.getCreateTime()) : "");
                row.createCell(23).setCellValue(lotInfo.getUpdateTime() != null ? dateFormat.format(lotInfo.getUpdateTime()) : "");
            }

            // 写入到 ByteArrayOutputStream
            ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
            workbook.write(outputStream);
            workbook.close();
            outputStream.close();

            return new ByteArrayInputStream(outputStream.toByteArray());
        }

}