From 4e8892f75a0d9b76e147cccdb1f188c272aed4c5 Mon Sep 17 00:00:00 2001
From: rain <167982779@qq.com>
Date: Wed, 31 Jul 2024 17:15:28 +0800
Subject: [PATCH] 图斑数据导出，优化面状图斑的数据搜索
---
src/main/java/com/dji/sample/patches/service/impl/GetPatchesServiceImpl.java | 4
src/main/java/com/dji/sample/media/service/impl/FileServiceImpl.java | 111 +++++++++++----------------
src/main/java/com/dji/sample/media/controller/FileController.java | 3
src/main/java/com/dji/sample/media/service/IFileService.java | 2
src/main/java/com/dji/sample/patches/service/GetPatchesService.java | 2
src/main/java/com/dji/sample/patches/utils/ExcelUtil.java | 74 ++++++++++++++++++
pom.xml | 12 +++
src/main/java/com/dji/sample/patches/controller/PatchesController.java | 30 ++++++-
8 files changed, 165 insertions(+), 73 deletions(-)
diff --git a/pom.xml b/pom.xml
index 09254a2..f11da81 100644
--- a/pom.xml
+++ b/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>
diff --git a/src/main/java/com/dji/sample/media/controller/FileController.java b/src/main/java/com/dji/sample/media/controller/FileController.java
index 66db157..09c9b03 100644
--- a/src/main/java/com/dji/sample/media/controller/FileController.java
+++ b/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));
}
diff --git a/src/main/java/com/dji/sample/media/service/IFileService.java b/src/main/java/com/dji/sample/media/service/IFileService.java
index b6e6b48..03354a4 100644
--- a/src/main/java/com/dji/sample/media/service/IFileService.java
+++ b/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);
/**
diff --git a/src/main/java/com/dji/sample/media/service/impl/FileServiceImpl.java b/src/main/java/com/dji/sample/media/service/impl/FileServiceImpl.java
index f50a47f..5837292 100644
--- a/src/main/java/com/dji/sample/media/service/impl/FileServiceImpl.java
+++ b/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)
diff --git a/src/main/java/com/dji/sample/patches/controller/PatchesController.java b/src/main/java/com/dji/sample/patches/controller/PatchesController.java
index f6e26b9..2d9d74d 100644
--- a/src/main/java/com/dji/sample/patches/controller/PatchesController.java
+++ b/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);
+ }
+ }
}
diff --git a/src/main/java/com/dji/sample/patches/service/GetPatchesService.java b/src/main/java/com/dji/sample/patches/service/GetPatchesService.java
index 5914139..dcb1c35 100644
--- a/src/main/java/com/dji/sample/patches/service/GetPatchesService.java
+++ b/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);
diff --git a/src/main/java/com/dji/sample/patches/service/impl/GetPatchesServiceImpl.java b/src/main/java/com/dji/sample/patches/service/impl/GetPatchesServiceImpl.java
index 82a1267..4cdb880 100644
--- a/src/main/java/com/dji/sample/patches/service/impl/GetPatchesServiceImpl.java
+++ b/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){
diff --git a/src/main/java/com/dji/sample/patches/utils/ExcelUtil.java b/src/main/java/com/dji/sample/patches/utils/ExcelUtil.java
new file mode 100644
index 0000000..1f0d644
--- /dev/null
+++ b/src/main/java/com/dji/sample/patches/utils/ExcelUtil.java
@@ -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());
+ }
+
+}
--
Gitblit v1.9.3