package cn.iocoder.yudao.module.mes.service.pro.route;
|
|
import cn.hutool.core.util.StrUtil;
|
import cn.idev.excel.ExcelWriter;
|
import cn.idev.excel.FastExcelFactory;
|
import cn.idev.excel.annotation.ExcelProperty;
|
import cn.idev.excel.write.handler.SheetWriteHandler;
|
import cn.idev.excel.write.metadata.holder.WriteSheetHolder;
|
import cn.idev.excel.write.metadata.holder.WriteWorkbookHolder;
|
import cn.iocoder.yudao.framework.common.util.http.HttpUtils;
|
import cn.iocoder.yudao.framework.common.util.object.BeanUtils;
|
import cn.iocoder.yudao.framework.excel.core.util.ExcelUtils;
|
import cn.iocoder.yudao.module.mes.controller.admin.pro.route.vo.MesProRouteFullExcelVO;
|
import cn.iocoder.yudao.module.mes.controller.admin.pro.route.vo.MesProRouteWorkbookVO;
|
import jakarta.servlet.http.HttpServletResponse;
|
import jakarta.annotation.Resource;
|
import cn.iocoder.yudao.module.mes.dal.mysql.md.item.MesMdItemMapper;
|
import cn.iocoder.yudao.module.mes.dal.mysql.pro.process.MesProProcessMapper;
|
import org.apache.poi.ss.usermodel.DataFormatter;
|
import org.apache.poi.ss.usermodel.WorkbookFactory;
|
import org.springframework.stereotype.Service;
|
import org.springframework.web.multipart.MultipartFile;
|
import java.io.IOException;
|
import java.io.InputStream;
|
import java.util.*;
|
import java.util.stream.Collectors;
|
import static cn.iocoder.yudao.framework.common.exception.util.ServiceExceptionUtil.exception;
|
import static cn.iocoder.yudao.module.mes.enums.ErrorCodeConstants.PRO_ROUTE_FULL_EXCEL_INVALID;
|
|
/** 面向用户的分表格式与既有工艺路线导入模型之间的转换。 */
|
@Service
|
public class MesProRouteWorkbookService {
|
@Resource private MesMdItemMapper itemMapper;
|
@Resource private MesProProcessMapper processMapper;
|
private static final String BASIC = "基本信息";
|
private static final String PRODUCT = "产品与仓位";
|
private static final String PROCESS = "工序";
|
private static final String OUTPUT = "工序产出";
|
private static final String MATERIAL = "工序投料";
|
private static final String CURRENT = "本产品";
|
private static final String COMMON = "通用";
|
|
public void write(HttpServletResponse response, List<MesProRouteFullExcelVO> rows) throws IOException {
|
List<MesProRouteWorkbookVO.Basic> basic = new ArrayList<>();
|
List<MesProRouteWorkbookVO.Product> products = new ArrayList<>();
|
List<MesProRouteWorkbookVO.Process> processes = new ArrayList<>();
|
List<MesProRouteWorkbookVO.Output> outputs = new ArrayList<>();
|
List<MesProRouteWorkbookVO.Material> materials = new ArrayList<>();
|
String referenceCode = rows.stream().filter(r -> "产品".equals(r.getRecordType()) && "是".equals(r.getTemplateReference()))
|
.map(MesProRouteFullExcelVO::getProductCode).findFirst().orElse(null);
|
for (var row : rows) {
|
switch (row.getRecordType()) {
|
case "路线" -> {
|
var item = BeanUtils.toBean(row, MesProRouteWorkbookVO.Basic.class);
|
item.setGuide("复制新产品：在产品与仓位表修改模板产品编码");
|
basic.add(item);
|
}
|
case "产品" -> {
|
var item = BeanUtils.toBean(row, MesProRouteWorkbookVO.Product.class);
|
item.setProductName(itemName(row.getProductCode(), referenceCode)); products.add(item);
|
}
|
case "工序" -> {
|
var item = new MesProRouteWorkbookVO.Process();
|
item.setProcessCode(row.getProcessCode()); item.setProcessName(processName(row.getProcessCode())); item.setProcessSort(row.getProcessSort());
|
item.setKeyFlag(row.getKeyFlag()); item.setCheckFlag(row.getCheckFlag()); item.setBackflushFlag(row.getBackflushFlag());
|
item.setPrepareTime(row.getPrepareTime()); item.setWaitTime(row.getWaitTime());
|
item.setLinkType(linkName(row.getLinkType())); item.setColorCode(row.getColorCode()); item.setProcessRemark(row.getProcessRemark());
|
processes.add(item);
|
}
|
case "产出" -> {
|
var item = BeanUtils.toBean(row, MesProRouteWorkbookVO.Output.class);
|
item.setProcessName(processName(row.getProcessCode())); item.setItemName(itemName(row.getItemCode(), referenceCode));
|
item.setProductCode(toDisplayOwner(row.getProductCode())); item.setItemCode(toDisplayItem(row.getItemCode())); outputs.add(item);
|
}
|
case "投料" -> {
|
var item = BeanUtils.toBean(row, MesProRouteWorkbookVO.Material.class);
|
item.setProcessName(processName(row.getProcessCode())); item.setItemName(itemName(row.getItemCode(), referenceCode));
|
item.setProductCode(toDisplayOwner(row.getProductCode())); materials.add(item);
|
}
|
default -> throw exception(PRO_ROUTE_FULL_EXCEL_INVALID, "未知记录类型：" + row.getRecordType());
|
}
|
}
|
SheetWriteHandler sheetHandler = new SheetWriteHandler() {
|
@Override public void afterSheetCreate(WriteWorkbookHolder workbook, WriteSheetHolder sheet) {
|
sheet.getSheet().createFreezePane(0, 1);
|
sheet.getSheet().setDefaultColumnWidth(20);
|
if (BASIC.equals(sheet.getSheetName())) sheet.getSheet().setColumnWidth(4, 55 * 256);
|
}
|
};
|
ExcelWriter writer = FastExcelFactory.write(response.getOutputStream()).autoCloseStream(false)
|
.registerWriteHandler(sheetHandler).build();
|
try {
|
writer.write(basic, FastExcelFactory.writerSheet(0, BASIC).head(MesProRouteWorkbookVO.Basic.class).build());
|
writer.write(products, FastExcelFactory.writerSheet(1, PRODUCT).head(MesProRouteWorkbookVO.Product.class).build());
|
writer.write(processes, FastExcelFactory.writerSheet(2, PROCESS).head(MesProRouteWorkbookVO.Process.class).build());
|
writer.write(outputs, FastExcelFactory.writerSheet(3, OUTPUT).head(MesProRouteWorkbookVO.Output.class).build());
|
writer.write(materials, FastExcelFactory.writerSheet(4, MATERIAL).head(MesProRouteWorkbookVO.Material.class).build());
|
} finally { writer.finish(); writer.close(); }
|
response.addHeader("Content-Disposition", "attachment;filename=" + HttpUtils.encodeUtf8("工艺流程模板.xlsx"));
|
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8");
|
}
|
|
public List<MesProRouteFullExcelVO> read(MultipartFile file) throws IOException {
|
boolean legacy;
|
try (InputStream in = file.getInputStream(); var book = WorkbookFactory.create(in)) {
|
legacy = book.getNumberOfSheets() == 1 && "工艺流程".equals(book.getSheetName(0));
|
if (!legacy) {
|
validateSheet(book, BASIC, MesProRouteWorkbookVO.Basic.class);
|
validateSheet(book, PRODUCT, MesProRouteWorkbookVO.Product.class);
|
validateSheet(book, PROCESS, MesProRouteWorkbookVO.Process.class);
|
validateSheet(book, OUTPUT, MesProRouteWorkbookVO.Output.class);
|
validateSheet(book, MATERIAL, MesProRouteWorkbookVO.Material.class);
|
}
|
}
|
if (legacy) return ExcelUtils.read(file, MesProRouteFullExcelVO.class);
|
List<MesProRouteWorkbookVO.Basic> basic = readSheet(file, BASIC, MesProRouteWorkbookVO.Basic.class);
|
if (basic.size() != 1 || StrUtil.isBlank(basic.get(0).getRouteCode()))
|
throw exception(PRO_ROUTE_FULL_EXCEL_INVALID, "基本信息表须填写一条路线，且路线编码不能为空");
|
String code = basic.get(0).getRouteCode().trim();
|
List<MesProRouteFullExcelVO> result = new ArrayList<>();
|
var header = BeanUtils.toBean(basic.get(0), MesProRouteFullExcelVO.class);
|
header.setRecordType("路线"); header.setRouteCode(code); result.add(header);
|
for (MesProRouteWorkbookVO.Product item : readSheet(file, PRODUCT, MesProRouteWorkbookVO.Product.class)) {
|
var row = BeanUtils.toBean(item, MesProRouteFullExcelVO.class); row.setRecordType("产品"); row.setRouteCode(code); result.add(row);
|
}
|
for (MesProRouteWorkbookVO.Process item : readSheet(file, PROCESS, MesProRouteWorkbookVO.Process.class)) {
|
var row = new MesProRouteFullExcelVO(); row.setRecordType("工序"); row.setRouteCode(code);
|
row.setProcessCode(item.getProcessCode()); row.setProcessSort(item.getProcessSort());
|
row.setKeyFlag(item.getKeyFlag()); row.setCheckFlag(item.getCheckFlag()); row.setBackflushFlag(item.getBackflushFlag());
|
row.setPrepareTime(item.getPrepareTime()); row.setWaitTime(item.getWaitTime()); row.setLinkType(linkCode(item.getLinkType()));
|
row.setColorCode(item.getColorCode()); row.setProcessRemark(item.getProcessRemark()); result.add(row);
|
}
|
for (MesProRouteWorkbookVO.Output item : readSheet(file, OUTPUT, MesProRouteWorkbookVO.Output.class)) {
|
var row = BeanUtils.toBean(item, MesProRouteFullExcelVO.class); row.setRecordType("产出"); row.setRouteCode(code);
|
row.setProductCode(toImportOwner(item.getProductCode())); row.setItemCode(toImportItem(item.getItemCode())); result.add(row);
|
}
|
for (MesProRouteWorkbookVO.Material item : readSheet(file, MATERIAL, MesProRouteWorkbookVO.Material.class)) {
|
var row = BeanUtils.toBean(item, MesProRouteFullExcelVO.class); row.setRecordType("投料"); row.setRouteCode(code);
|
row.setProductCode(toImportOwner(item.getProductCode())); result.add(row);
|
}
|
return result;
|
}
|
|
private <T> List<T> readSheet(MultipartFile file, String sheet, Class<T> type) throws IOException {
|
try (InputStream in = file.getInputStream()) { return FastExcelFactory.read(in).head(type).sheet(sheet).doReadSync(); }
|
}
|
private void validateSheet(org.apache.poi.ss.usermodel.Workbook workbook, String name, Class<?> type) {
|
var sheet = workbook.getSheet(name);
|
if (sheet == null || sheet.getRow(0) == null) throw exception(PRO_ROUTE_FULL_EXCEL_INVALID, "缺少工作表或表头：" + name);
|
var header = sheet.getRow(0); var formatter = new DataFormatter();
|
Set<String> actual = new HashSet<>();
|
for (var cell : header) {
|
String value = formatter.formatCellValue(cell).trim();
|
if (!value.isEmpty() && !actual.add(value)) throw exception(PRO_ROUTE_FULL_EXCEL_INVALID, name + "工作表的表头重复：" + value);
|
}
|
Set<String> expected = Arrays.stream(type.getDeclaredFields()).map(f -> f.getAnnotation(ExcelProperty.class))
|
.filter(Objects::nonNull).map(a -> a.value()[0]).collect(Collectors.toCollection(LinkedHashSet::new));
|
if (!actual.equals(expected)) throw exception(PRO_ROUTE_FULL_EXCEL_INVALID, name + "工作表的表头已变更，请使用下载的原始模板");
|
}
|
private String itemName(String code, String referenceCode) {
|
String resolved = MesProRouteFullExcelService.REFERENCE.equals(code) ? referenceCode : code;
|
if (StrUtil.isBlank(resolved)) return null;
|
var item = itemMapper.selectByCode(resolved);
|
return item == null ? null : item.getName();
|
}
|
private String processName(String code) {
|
if (StrUtil.isBlank(code)) return null;
|
var process = processMapper.selectByCode(code);
|
return process == null ? null : process.getName();
|
}
|
private String toDisplayOwner(String value) { return StrUtil.isBlank(value) ? COMMON : toDisplayItem(value); }
|
private String toDisplayItem(String value) { return MesProRouteFullExcelService.REFERENCE.equals(value) ? CURRENT : value; }
|
private String toImportOwner(String value) { return StrUtil.isBlank(value) || COMMON.equals(value.trim()) ? null : toImportItem(value); }
|
private String toImportItem(String value) { return StrUtil.isNotBlank(value) && CURRENT.equals(value.trim()) ? MesProRouteFullExcelService.REFERENCE : value; }
|
private String linkName(Integer value) {
|
if (value == null) return null;
|
return switch (value) { case 0 -> "开始-开始"; case 1 -> "结束-结束"; case 2 -> "开始-结束"; case 3 -> "结束-开始"; default -> value.toString(); };
|
}
|
private Integer linkCode(String value) {
|
if (StrUtil.isBlank(value)) return null;
|
return switch (value.trim()) {
|
case "0", "开始-开始" -> 0; case "1", "结束-结束" -> 1; case "2", "开始-结束" -> 2; case "3", "结束-开始" -> 3;
|
default -> throw exception(PRO_ROUTE_FULL_EXCEL_INVALID, "下一工序关系只能填写开始-开始、结束-结束、开始-结束或结束-开始");
|
};
|
}
|
}
|
