<select id="selectLatestByOrderIds" resultType="com.hvlink.entity.dto.order.PurchaseOrderMainDTO">
SELECT
m.id,
m.company_code,
m.purchase_order_no,
m.publish_date,
s.supplier_code,
s.supplier_name,
f.factory_code,
f.factory_name,
w.warehouse_code,
w.warehouse_name,
m.serial_version_num,
m.order_type
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY purchase_order_no, company_code ORDER BY serial_version_num DESC) as rn
FROM tb_order_main
WHERE is_deleted = 0
AND id IN
<foreach collection="orderIds" item="id" open="(" close=")" separator=",">
#{id}
</foreach>
) m
LEFT JOIN tm_supplier s ON m.supplier_code = s.supplier_code AND m.company_code = s.company_code
LEFT JOIN tm_factory f ON m.factory_code = f.factory_code AND m.company_code = f.company_code
LEFT JOIN tm_warehouse w ON m.warehouse_code = w.warehouse_code AND m.company_code = w.company_code
WHERE m.rn = 1
ORDER BY m.create_time DESC
</select>
package com.hvlink.service.impl;
import cn.hutool.core.collection.CollUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.merge.AbstractMergeStrategy;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.baomidou.mybatisplus.core.toolkit.CollectionUtils;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.hvlink.entity.dto.order.CompositeKeyDTO;
import com.hvlink.entity.dto.order.PurchaseOrderDetailsDTO;
import com.hvlink.entity.dto.order.PurchaseOrderMainDTO;
import com.hvlink.entity.param.order.PurchaseOrderParam;
import com.hvlink.entity.po.master.CompanyPO;
import com.hvlink.entity.po.master.FactoryPO;
import com.hvlink.entity.po.master.PartPO;
import com.hvlink.entity.po.order.TbOrderMainPO;
import com.hvlink.entity.vo.order.PurchaseOrderDetailsVO;
import com.hvlink.entity.vo.order.PurchaseOrderExportVO;
import com.hvlink.entity.vo.order.PurchaseOrderMainVO;
import com.hvlink.enums.PurchaseOrderPlanTypeEnum;
import com.hvlink.enums.PurchaseOrderTypeEnum;
import com.hvlink.mapper.master.CompanyMapper;
import com.hvlink.mapper.master.FactoryMapper;
import com.hvlink.mapper.master.PartMapper;
import com.hvlink.mapper.order.TbOrderDetailMapper;
import com.hvlink.mapper.order.TbOrderMainMapper;
import com.hvlink.pagination.PageResult;
import com.hvlink.service.IPurchaseOrderService;
import com.hvlink.utils.BeanCopyUtils;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.core.io.buffer.DataBuffer;
import org.springframework.http.ContentDisposition;
import org.springframework.http.MediaType;
import org.springframework.http.server.reactive.ServerHttpResponse;
import org.springframework.stereotype.Service;
import reactor.core.publisher.Mono;
import java.io.ByteArrayOutputStream;
import java.nio.charset.StandardCharsets;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.function.BinaryOperator;
import java.util.function.Function;
import java.util.stream.Collectors;
@Service
@RequiredArgsConstructor
@Slf4j
public class PurchaseOrderServiceImpl extends ServiceImpl<TbOrderMainMapper, TbOrderMainPO> implements IPurchaseOrderService {
private final TbOrderMainMapper orderMainMapper;
private final TbOrderDetailMapper orderDetailMapper;
private final PartMapper partMapper;
private final CompanyMapper companyMapper;
private final FactoryMapper factoryMapper;
@Override
public PageResult<PurchaseOrderMainVO> queryPage(PurchaseOrderParam purchaseOrderParam) {
// 查询主表数据
Page<PurchaseOrderMainDTO> page = new Page<>(purchaseOrderParam.getPageIndex(), purchaseOrderParam.getPageSize());
Page<PurchaseOrderMainDTO> mainPage = orderMainMapper.queryPurchaseOrderPage(page, purchaseOrderParam);
if (CollectionUtils.isEmpty(mainPage.getRecords())) {
return null;
}
// 构建复合键列表用于查询明细
List<CompositeKeyDTO> compositeKeys = mainPage.getRecords().stream()
.map(main -> new CompositeKeyDTO(
main.getPurchaseOrderNo(),
main.getCompanyCode(),
main.getSerialVersionNum()
))
.collect(Collectors.toList());
// 查询明细数据
List<PurchaseOrderDetailsDTO> detailList = orderDetailMapper.queryDetailsByCompositeKeys(compositeKeys);
// 按复合键分组明细数据
Map<String, List<PurchaseOrderDetailsDTO>> detailMap = detailList.stream()
.collect(Collectors.groupingBy(detail ->
detail.getPurchaseOrderNo() + "#" +
detail.getCompanyCode() + "#" +
detail.getSerialVersionNum()
));
// 转换主表数据到VO
List<PurchaseOrderMainVO> mainVOList = mainPage.getRecords().stream().map(mainDTO -> {
PurchaseOrderMainVO mainVO = BeanCopyUtils.copyBean(mainDTO, PurchaseOrderMainVO.class);
mainVO.setSpliceSupplierName(mainDTO.getSupplierCode() + " - " + mainDTO.getSupplierName());
mainVO.setSpliceFactoryName(mainDTO.getFactoryCode() + " - " + mainDTO.getFactoryName());
mainVO.setSpliceWarehouseName(mainDTO.getWarehouseCode() + " - " + mainDTO.getWarehouseName());
// 设置订单类型描述
if (mainDTO.getOrderType() != null) {
PurchaseOrderTypeEnum typeEnum = Arrays.stream(PurchaseOrderTypeEnum.values())
.filter(e -> e.getTypeCode().equals(mainDTO.getOrderType()))
.findFirst()
.orElse(null);
if (typeEnum != null) {
mainVO.setType(typeEnum.getTypeCode());
}
}
// 设置明细数据 - 使用复合键
String compositeKey = mainDTO.getPurchaseOrderNo() + "#" +
mainDTO.getCompanyCode() + "#" +
mainDTO.getSerialVersionNum();
List<PurchaseOrderDetailsVO> detailVOList = new ArrayList<>();
if (detailMap.containsKey(compositeKey)) {
detailVOList = detailMap.get(compositeKey).stream().map(detail -> {
PurchaseOrderDetailsVO detailVO = BeanCopyUtils.copyBean(detail, PurchaseOrderDetailsVO.class);
// 设置类型名称
if (detail.getType() != null) {
PurchaseOrderPlanTypeEnum planTypeEnum = Arrays.stream(PurchaseOrderPlanTypeEnum.values())
.filter(e -> e.getTypeCode().equals(detail.getType()))
.findFirst()
.orElse(null);
if (planTypeEnum != null) {
detailVO.setTypeName(planTypeEnum.getTypeDesc());
}
}
return detailVO;
}).collect(Collectors.toList());
}
mainVO.setDetailsList(detailVOList);
return mainVO;
}).collect(Collectors.toList());
// 构建分页结果
PageResult<PurchaseOrderMainVO> result = new PageResult<>();
result.setRecords(mainVOList);
result.setTotal(mainPage.getTotal());
result.setPageIndex(purchaseOrderParam.getPageIndex());
result.setPageSize(purchaseOrderParam.getPageSize());
return result;
}
/**
* 导出
* @param response
* @param exportQuery
* @return
*/
public Mono<Void> exportPurchaseOrderData(ServerHttpResponse response,PurchaseOrderParam exportQuery) {
return Mono.fromCallable(() -> {
// 获取基础数据字典
Map<String, PartPO> partMap = partMapper.selectList(Wrappers.emptyWrapper())
.stream()
.collect(Collectors.toMap(PartPO::getPartCode, Function.identity(),
BinaryOperator.maxBy(Comparator.comparing(PartPO::getCreateTime))));
Map<String, CompanyPO> companyMap = companyMapper.selectList(Wrappers.emptyWrapper())
.stream()
.collect(Collectors.toMap(CompanyPO::getCompanyCode, Function.identity(),
BinaryOperator.maxBy(Comparator.comparing(CompanyPO::getCreateTime))));
Map<String, FactoryPO> factoryMap = factoryMapper.selectList(Wrappers.emptyWrapper())
.stream()
.collect(Collectors.toMap(FactoryPO::getFactoryCode, Function.identity(),
BinaryOperator.maxBy(Comparator.comparing(FactoryPO::getCreateTime))));
return prepareExportData(exportQuery, partMap, companyMap, factoryMap);
})
.flatMap(exportData -> {
if (exportData == null || CollUtil.isEmpty(exportData.getExportList())) {
log.warn("没有找到符合条件的采购订单数据用于导出");
return Mono.empty();
}
return writeExcelToResponse(exportData, response);
})
.onErrorResume(e -> {
log.error("导出采购订单数据失败", e);
return Mono.error(new RuntimeException("导出失败:" + e.getMessage(), e));
});
}
/**
* 准备导出数据
*/
private ExportData prepareExportData(PurchaseOrderParam exportQuery,
Map<String, PartPO> partMap,
Map<String, CompanyPO> companyMap,
Map<String, FactoryPO> factoryMap) {
List<PurchaseOrderMainDTO> mainList;
// 判断是否选择了特定订单
if (CollectionUtils.isNotEmpty(exportQuery.getSelectedOrderIds())) {
// 根据 selectedOrderIds 查询主表中对应的最新版本订单
mainList = orderMainMapper.selectLatestByOrderIds(exportQuery.getSelectedOrderIds());
} else {
// 否则走原分页查询逻辑,但导出时获取所有匹配项
Page<PurchaseOrderMainDTO> page = new Page<>(1, Integer.MAX_VALUE, true);
Page<PurchaseOrderMainDTO> mainPage = orderMainMapper.queryPurchaseOrderPage(page, exportQuery);
mainList = mainPage.getRecords();
}
if (CollUtil.isEmpty(mainList)) {
return null;
}
// 构建复合键查询明细
List<CompositeKeyDTO> compositeKeys = mainList.stream()
.map(main -> new CompositeKeyDTO(
main.getPurchaseOrderNo(),
main.getCompanyCode(),
main.getSerialVersionNum()
))
.collect(Collectors.toList());
// 查询明细数据 - 分批处理避免参数过多
List<PurchaseOrderDetailsDTO> detailList = new ArrayList<>();
int batchSize = 500; // 每批处理的数量
for (int i = 0; i < compositeKeys.size(); i += batchSize) {
int end = Math.min(i + batchSize, compositeKeys.size());
List<CompositeKeyDTO> batchKeys = compositeKeys.subList(i, end);
List<PurchaseOrderDetailsDTO> batchDetails = orderDetailMapper.queryDetailsByCompositeKeys(batchKeys);
detailList.addAll(batchDetails);
}
// 按复合键分组明细数据
Map<String, List<PurchaseOrderDetailsDTO>> detailMap = detailList.stream()
.collect(Collectors.groupingBy(detail ->
detail.getPurchaseOrderNo() + "#" +
detail.getCompanyCode() + "#" +
detail.getSerialVersionNum()
));
// 准备导出数据
List<PurchaseOrderExportVO> exportList = new ArrayList<>();
Map<String, Integer> mergeInfoMap = new HashMap<>(); // 用于合并单元格
// 先按合并键分组,确保合并信息准确
Map<String, List<PurchaseOrderDetailsDTO>> detailsByMergeKey = new HashMap<>();
for (PurchaseOrderMainDTO mainDTO : mainList) {
String compositeKey = mainDTO.getPurchaseOrderNo() + "#" +
mainDTO.getCompanyCode() + "#" +
mainDTO.getSerialVersionNum();
List<PurchaseOrderDetailsDTO> details = detailMap.getOrDefault(compositeKey, new ArrayList<>());
// 使用更稳定的合并键(只包含订单号和版本号)
String mergeKey = mainDTO.getPurchaseOrderNo() + "#" + mainDTO.getSerialVersionNum();
// 确保同一个合并键的数据连续排列
if (!detailsByMergeKey.containsKey(mergeKey)) {
detailsByMergeKey.put(mergeKey, new ArrayList<>());
}
detailsByMergeKey.get(mergeKey).addAll(details);
// 记录合并信息
mergeInfoMap.put(mergeKey, details.size());
}
// 按合并键顺序填充导出列表,确保同一组合并数据连续
for (Map.Entry<String, List<PurchaseOrderDetailsDTO>> entry : detailsByMergeKey.entrySet()) {
String mergeKey = entry.getKey();
List<PurchaseOrderDetailsDTO> details = entry.getValue();
// 从mergeKey中解析出订单号和版本号
String[] keyParts = mergeKey.split("#");
String purchaseOrderNo = keyParts[0];
String serialVersionNum = keyParts[1];
// 找到对应的主表信息
PurchaseOrderMainDTO mainDTO = mainList.stream()
.filter(m -> m.getPurchaseOrderNo().equals(purchaseOrderNo)
&& m.getSerialVersionNum().equals(serialVersionNum))
.findFirst()
.orElse(null);
if (mainDTO == null) continue;
// 获取订单类型描述
String orderTypeDesc = Optional.ofNullable(mainDTO.getOrderType())
.flatMap(type -> Arrays.stream(PurchaseOrderTypeEnum.values())
.filter(e -> e.getTypeCode().equals(type))
.findFirst())
.map(PurchaseOrderTypeEnum::getTypeDesc)
.orElse("");
for (PurchaseOrderDetailsDTO detail : details) {
PurchaseOrderExportVO exportVO = BeanCopyUtils.copyBean(detail, PurchaseOrderExportVO.class);
// 填充主表信息
exportVO.setPublishDate(mainDTO.getPublishDate());
exportVO.setPurchaseOrderNo(mainDTO.getPurchaseOrderNo());
exportVO.setSupplier(mainDTO.getSupplierCode() + " - " + mainDTO.getSupplierName());
exportVO.setFactory(mainDTO.getFactoryCode() + " - " + mainDTO.getFactoryName());
exportVO.setWarehouse(mainDTO.getWarehouseCode() + " - " + mainDTO.getWarehouseName());
exportVO.setSerialVersionNum(mainDTO.getSerialVersionNum());
exportVO.setType(Optional.ofNullable(detail.getType())
.flatMap(t -> Arrays.stream(PurchaseOrderPlanTypeEnum.values())
.filter(e -> e.getTypeCode().equals(t))
.findFirst())
.map(PurchaseOrderPlanTypeEnum::getTypeDesc)
.orElse(detail.getType()));
exportList.add(exportVO);
}
}
return new ExportData(exportList, mergeInfoMap);
}
/**
* 将数据写入Excel并输出到响应
*/
private Mono<Void> writeExcelToResponse(ExportData exportData, ServerHttpResponse response) {
return Mono.fromCallable(() -> {
try {
// 单元格样式
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
HorizontalCellStyleStrategy styleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
// 创建自定义合并策略类
CustomMergeStrategy mergeStrategy = new CustomMergeStrategy(exportData.getExportList(), exportData.getMergeInfoMap());
// 写入Excel到字节数组
ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
EasyExcel.write(outputStream, PurchaseOrderExportVO.class)
.registerWriteHandler(styleStrategy)
.registerWriteHandler(mergeStrategy)
.sheet("采购订单数据")
.doWrite(exportData.getExportList());
return outputStream.toByteArray();
} catch (Exception e) {
log.error("生成Excel文件失败", e);
throw new RuntimeException("生成Excel文件失败:" + e.getMessage(), e);
}
}).flatMap(bytes -> {
try {
// 设置响应头 - 修复文件名编码问题
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
String fileName = "采购订单数据_" + sdf.format(new Date()) + ".xlsx";
// 更安全的文件名编码处理
String encodedFileName;
try {
// 优先尝试URL编码
encodedFileName = java.net.URLEncoder.encode(fileName, StandardCharsets.UTF_8.name())
.replaceAll("\\+", "%20");
} catch (Exception e) {
// 备用方案:直接使用ISO-8859-1编码
encodedFileName = new String(fileName.getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1);
}
// 清除可能存在的旧headers
response.getHeaders().clear();
// 设置Content-Type
response.getHeaders().setContentType(MediaType.APPLICATION_OCTET_STREAM);
// 设置Content-Disposition - 使用RFC 5987标准
response.getHeaders().set("Content-Disposition",
"attachment; filename=\"" + encodedFileName + "\"; " +
"filename*=UTF-8''" + encodedFileName);
// 设置缓存控制
response.getHeaders().setCacheControl("no-cache, no-store, must-revalidate");
response.getHeaders().setPragma("no-cache");
response.getHeaders().setExpires(0L);
// 设置内容长度
response.getHeaders().setContentLength(bytes.length);
DataBuffer buffer = response.bufferFactory().wrap(bytes);
return response.writeWith(Mono.just(buffer));
} catch (Exception e) {
log.error("设置响应头失败", e);
return Mono.error(e);
}
});
}
/**
* 自定义合并策略类 - 修复版(适配新版EasyExcel)
*/
private static class CustomMergeStrategy extends AbstractMergeStrategy {
private final List<PurchaseOrderExportVO> exportList;
private final Map<String, Integer> mergeInfoMap;
public CustomMergeStrategy(List<PurchaseOrderExportVO> exportList, Map<String, Integer> mergeInfoMap) {
this.exportList = exportList;
this.mergeInfoMap = mergeInfoMap;
}
@Override
protected void merge(Sheet sheet, Cell cell, Head head, int relativeRowIndex) {
// 数据行索引(从第0行数据开始,relativeRowIndex是相对于数据起始行的索引)
int dataRowIndex = relativeRowIndex;
// 检查是否超出导出数据范围
if (dataRowIndex >= exportList.size()) {
return;
}
// 只对前6列进行合并(如:订单号、供应商、工厂等主表信息)
if (cell.getColumnIndex() >= 6) {
return;
}
// 获取当前行对应的数据对象
PurchaseOrderExportVO currentVO = exportList.get(dataRowIndex);
String mergeKey = currentVO.getPurchaseOrderNo() + "#" + currentVO.getSerialVersionNum();
// 判断是否存在该合并组
if (!mergeInfoMap.containsKey(mergeKey)) {
return;
}
int rowCount = mergeInfoMap.get(mergeKey);
// 如果只有一行,则无需合并
if (rowCount <= 1) {
return;
}
// 检查是否是当前合并组的第一行
boolean isFirstRow = true;
if (dataRowIndex > 0) {
PurchaseOrderExportVO previousVO = exportList.get(dataRowIndex - 1);
String prevKey = previousVO.getPurchaseOrderNo() + "#" + previousVO.getSerialVersionNum();
if (mergeKey.equals(prevKey)) {
isFirstRow = false;
}
}
// 只有当前合并组的第一行才执行合并操作
if (!isFirstRow) {
return;
}
// 验证接下来的 rowCount 行都属于同一组
for (int i = 1; i < rowCount; i++) {
if (dataRowIndex + i >= exportList.size()) {
return; // 数据不足,不合并
}
PurchaseOrderExportVO nextVO = exportList.get(dataRowIndex + i);
String nextKey = nextVO.getPurchaseOrderNo() + "#" + nextVO.getSerialVersionNum();
if (!mergeKey.equals(nextKey)) {
return; // 后续行不属于同一组,不合并
}
}
// 执行合并操作
// 注意:EasyExcel中,表头占1行,所以数据行从第1行开始
int firstRow = 1 + dataRowIndex; // 表头占第0行,数据从第1行开始
int lastRow = firstRow + rowCount - 1;
int column = cell.getColumnIndex();
CellRangeAddress region = new CellRangeAddress(firstRow, lastRow, column, column);
sheet.addMergedRegion(region);
// 设置合并区域样式(保持与原始单元格一致)
setMergedRegionStyle(sheet, region, cell);
}
/**
* 设置合并区域样式,保持与原始单元格一致
*/
private void setMergedRegionStyle(Sheet sheet, CellRangeAddress region, Cell originalCell) {
for (int i = region.getFirstRow(); i <= region.getLastRow(); i++) {
org.apache.poi.ss.usermodel.Row row = sheet.getRow(i);
if (row == null) {
row = sheet.createRow(i);
}
for (int j = region.getFirstColumn(); j <= region.getLastColumn(); j++) {
Cell cell = row.getCell(j);
if (cell == null) {
cell = row.createCell(j);
}
// 复制原始单元格的样式
cell.setCellStyle(originalCell.getCellStyle());
}
}
}
}
/**
* 导出数据封装类
*/
@Data
@AllArgsConstructor
private static class ExportData {
private List<PurchaseOrderExportVO> exportList;
private Map<String, Integer> mergeInfoMap;
}
}我只传了一个id,为什么导出的不是一条数据?
最新发布