package com.whyc.service;
|
|
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
|
import com.baomidou.mybatisplus.core.conditions.update.UpdateWrapper;
|
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
|
import com.github.pagehelper.PageHelper;
|
import com.github.pagehelper.PageInfo;
|
import com.whyc.dto.FileUrlDTO;
|
import com.whyc.dto.Response;
|
import com.whyc.mapper.ProductMapper;
|
import com.whyc.pojo.*;
|
import com.whyc.util.ActionUtil;
|
import com.whyc.util.CommonUtil;
|
import com.whyc.util.FileUtil;
|
import com.whyc.util.Zip4jUtil;
|
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
|
import org.apache.poi.ss.usermodel.*;
|
import org.springframework.beans.factory.annotation.Autowired;
|
import org.springframework.stereotype.Service;
|
import org.springframework.util.FileCopyUtils;
|
import org.springframework.web.multipart.MultipartFile;
|
|
import java.io.*;
|
import java.text.SimpleDateFormat;
|
import java.util.ArrayList;
|
import java.util.Date;
|
import java.util.LinkedList;
|
import java.util.List;
|
import java.util.stream.Collectors;
|
|
@Service
|
public class ProductService {
|
@Autowired(required = false)
|
private ProductMapper mapper;
|
|
@Autowired
|
private ProductHistoryService phService;
|
|
@Autowired
|
private MaterialProductHistoryService mphService;
|
|
@Autowired
|
private ProductBomService pbService;
|
|
@Autowired
|
private ProductBomHistoryService pbhService;
|
|
@Autowired
|
private MaterialService mService;
|
|
//查询出所有的产品信息(分页加模糊查询<产品的编码，型号，名字，定制表编号>
|
public Response getAllProduct(String parentCode, String parentName, String parentModel, String customCode, int pageCurr, int pageSize) {
|
PageHelper.startPage(pageCurr,pageSize);
|
QueryWrapper wrapper=new QueryWrapper();
|
if(parentCode!=null){
|
wrapper.like("parent_code",parentCode);
|
}
|
if(parentName!=null){
|
wrapper.like("parent_name",parentName);
|
}
|
if(parentModel!=null){
|
wrapper.like("parent_model",parentModel);
|
}
|
if(customCode!=null){
|
if(customCode.isEmpty()){
|
wrapper.eq("custom_code",customCode);
|
}else{
|
wrapper.like("custom_code",customCode);
|
}
|
}
|
wrapper.orderByAsc("id");
|
List list=mapper.selectList(wrapper);
|
PageInfo pageInfo=new PageInfo(list);
|
return new Response().setII(1,list.size()>0,pageInfo,"返回产品信息");
|
}
|
|
public Product getVersion(String parentCode, String customCode) {
|
QueryWrapper<Product> query = Wrappers.query();
|
query.eq("parent_code",parentCode).eq("custom_code",customCode).last(" limit 1");
|
return mapper.selectOne(query);
|
}
|
|
public Product getVersion(Integer productId) {
|
QueryWrapper<Product> query = Wrappers.query();
|
query.eq("product_id",productId);
|
return mapper.selectOne(query);
|
}
|
|
public int updateVersion(String parentCode, String customCode, Integer nextVersion) {
|
UpdateWrapper<Product> update = Wrappers.update();
|
update.set("version",nextVersion).eq("parent_code",parentCode).eq("custom_code",customCode);
|
mapper.update(null,update);
|
|
QueryWrapper<Product> query = Wrappers.query();
|
query.select("id").eq("parent_code",parentCode).eq("custom_code",customCode);
|
return mapper.selectOne(query).getId();
|
}
|
|
public Product getById(Integer id) {
|
return mapper.selectById(id);
|
}
|
|
public void deleteByParentCodeAndCustomCode(String parentCode, String customCode) {
|
UpdateWrapper<Product> update = Wrappers.update();
|
update.eq("parent_code",parentCode).eq("custom_code",customCode);
|
mapper.delete(update);
|
}
|
|
public void insert(Product product) {
|
mapper.insert(product);
|
}
|
|
public Response zipParse(MultipartFile file) throws IOException, InvalidFormatException {
|
List<ProductBom> list = new LinkedList<>();
|
|
Product product = new Product();
|
List<ProductBom> bomList = new LinkedList<>();
|
Response response = new Response();
|
//检查是否为zip
|
boolean isZip = Zip4jUtil.checkZipFileParam(file);
|
if (!isZip) {
|
return response.set(1, false, "上传的文件格式不是zip");
|
}
|
//zip存储路径:doc_file/product_submit/${username}/{dateFormat}/${timeStamp}
|
String rootFile = CommonUtil.getRootFile();
|
DocUser user = ActionUtil.getUser();
|
String dateFormat = new SimpleDateFormat("YYYY-MM").format(new Date());
|
long timeStamp = System.currentTimeMillis();
|
String filePath = rootFile + File.separator + "product_submit" + File.separator + user.getName() + File.separator + dateFormat + File.separator + timeStamp;
|
File parentFile = new File(filePath);
|
String originalFilename = file.getOriginalFilename();
|
File zipFile = new File(filePath + File.separator + originalFilename);
|
if (!zipFile.exists()) {
|
zipFile.mkdirs();
|
}
|
file.transferTo(zipFile);
|
//解压文件夹
|
Zip4jUtil.unPackZip(zipFile, null, filePath);
|
//遍历解压后的文件夹路径,解析excel
|
System.out.println(filePath);
|
List<String> fileList = new ArrayList<>();
|
//图纸筛选
|
fileList = FileUtil.getStaticFilePath(parentFile, fileList);
|
List<FileUrlDTO> dwgExistsList = getDwgList(fileList);
|
for (int i = 0; i < fileList.size(); i++) {
|
String fileTempUrl = fileList.get(i);
|
//查询需上传的bom数据
|
if (fileTempUrl.contains(".xls") && fileTempUrl.contains("(BOM)")) {
|
Workbook workbook = null;
|
InputStream inputStream = new FileInputStream(new File(fileTempUrl));
|
workbook = WorkbookFactory.create(inputStream);
|
inputStream.close();
|
List<? extends PictureData> allPictures = workbook.getAllPictures();
|
//取第一个sheet表
|
Sheet sheet = workbook.getSheetAt(0);
|
int lastRowNum = sheet.getLastRowNum();
|
//取第三行,并以第三行开始
|
Row row2 = sheet.getRow(1);
|
short lastCellNum = row2.getLastCellNum();
|
for (int k = 2; k < lastRowNum + 1; k++) {
|
if(k==2){
|
product.setParentCode(sheet.getRow(2).getCell(1).getStringCellValue());
|
product.setParentName(sheet.getRow(2).getCell(2).getStringCellValue());
|
product.setParentModel(sheet.getRow(2).getCell(3).getStringCellValue());
|
}
|
ProductBom bom = new ProductBom();
|
for (int j = 0; j < lastCellNum; j++) {
|
Row row = sheet.getRow(k);
|
Cell cell = row.getCell(j);
|
String cellValue = null;
|
Double cellValueInt = 0d;
|
if (j == 9 || j == 0) {
|
cellValueInt = cell.getNumericCellValue();
|
} else if (j != 15) {
|
cellValue = cell.getStringCellValue();
|
}
|
switch (j) {
|
case 0: {
|
bom.setId(cellValueInt.intValue());
|
}
|
break;
|
case 4: {
|
bom.setCategory(cellValue);
|
}
|
break;
|
case 5: {
|
bom.setSubCode(cellValue);
|
}
|
break;
|
case 6: {
|
bom.setSubName(cellValue);
|
}
|
break;
|
case 7: {
|
bom.setSubModel(cellValue);
|
//判断图纸查看是否存在
|
bom.setDwgExist(0);
|
String dwgFileName = cellValue + ".dwg";
|
for (int n = 0; n < dwgExistsList.size(); n++) {
|
FileUrlDTO fileUrlDTO = dwgExistsList.get(n);
|
if (dwgFileName.toUpperCase().equals(fileUrlDTO.getFileName().toUpperCase())) {
|
//存在
|
bom.setDwgExist(1);
|
bom.setDwgUrl(fileUrlDTO.getHttpFileUrl());
|
break;
|
}
|
}
|
}
|
break;
|
case 8: {
|
bom.setUnit(cellValue);
|
}
|
break;
|
case 9: {
|
bom.setQuantity(cellValueInt.intValue());
|
}
|
break;
|
case 10: {
|
bom.setProducer(cellValue);
|
}
|
break;
|
case 11: {
|
bom.setMaterial(cellValue);
|
}
|
break;
|
case 12: {
|
bom.setThickness(cellValue);
|
}
|
break;
|
case 13: {
|
bom.setSurfaceDetail(cellValue);
|
}
|
break;
|
case 14: {
|
bom.setNotes(cellValue);
|
}
|
break;
|
case 15: {
|
//图片,从0开始,到图片size为止
|
int m = k - 2;
|
if (m < allPictures.size()) {
|
PictureData pictureData = allPictures.get(m);
|
//图片存储 product_submit/username/2022-07/
|
String approvingPath = rootFile + File.separator + "product_submit" + File.separator + user.getName() + File.separator + dateFormat + File.separator + timeStamp;
|
File provingFile = new File(approvingPath);
|
if (!provingFile.exists()) {
|
provingFile.mkdirs();
|
}
|
String suffix = pictureData.suggestFileExtension();
|
String picturePath = approvingPath + File.separator + bom.getSubModel() + "." + suffix;
|
String picturePathFront = "doc_file" + File.separator + "product_submit" + File.separator + user.getName() + File.separator + dateFormat + File.separator + timeStamp + File.separator + bom.getSubModel() + "." + suffix;
|
byte[] data = pictureData.getData();
|
FileOutputStream fileOutputStream = null;
|
File pictureFile = new File(picturePath);
|
fileOutputStream = new FileOutputStream(pictureFile);
|
fileOutputStream.write(data);
|
|
bom.setPictureUrl(picturePathFront);
|
}
|
}
|
break;
|
}
|
}
|
bomList.add(bom);
|
}
|
product.setBomList(bomList);
|
}
|
}
|
return response.setII(1, true, product, filePath);
|
|
}
|
|
private List<FileUrlDTO> getDwgList(List<String> fileList) {
|
List<FileUrlDTO> list = new LinkedList<>();
|
fileList.forEach(fileUrl->{
|
if(fileUrl.substring(fileUrl.lastIndexOf(".")+1).equals("dwg")) {
|
FileUrlDTO dto = new FileUrlDTO();
|
dto.setFileName(fileUrl.substring(fileUrl.lastIndexOf(File.separator) + 1));
|
dto.setHttpFileUrl(fileUrl.substring(fileUrl.lastIndexOf("doc_file" + File.separator + "product_approving")));
|
list.add(dto);
|
}
|
});
|
return list;
|
}
|
|
public Response add(Product product) {
|
String parentCode = product.getParentCode();
|
String customCode = product.getCustomCode();
|
String parentModel = product.getParentModel();
|
List<ProductBom> bomList = product.getBomList();
|
String fileUrl = product.getFileUrl();
|
//查询产品最新的版本号
|
ProductHistory latestProduct = phService.getLatestVersion(parentCode, customCode);
|
ProductHistory enabledProduct = phService.getEnabledByParentCodeAndCustomCode(parentCode, customCode);
|
int currentVersion = 0;
|
if (latestProduct != null) {
|
currentVersion = latestProduct.getVersion();
|
}
|
Integer nextVersion = currentVersion + 1;
|
//产品物料关系迁移
|
//查询生效版本的关联关系
|
if(latestProduct!=null &&enabledProduct!=null) {
|
List<MaterialProductHistory> mpList = mphService.getListByParentCodeAndCustomCodeAndVersion(parentCode, customCode, enabledProduct.getVersion());
|
if (latestProduct.getVersion().intValue() == enabledProduct.getVersion()) {
|
//最新版本生效,关联关系版本连着的
|
mphService.updateVersionBatch(mpList);
|
} else {
|
//旧版本生效,关联关系版本不连着
|
mpList.forEach(mp -> {
|
mp.setSVersion(nextVersion);
|
mp.setEVersion(nextVersion);
|
});
|
mphService.insertBatch(mpList);
|
}
|
}
|
|
//将产品文件复制至正式路径
|
//文件转移,未跟子件挂钩的所有图纸图片转移到产品版本下:doc_file/product/{产品型号}/standard或者{customCode}}/{version}/
|
//跟子件挂钩的转移到子件图纸下:doc_file/material/
|
File file = new File(fileUrl);
|
List<String> fileUrlList = new LinkedList<>();
|
List<String> dwgUrlList = null;
|
List<String> picUrlList = null;
|
//存于物料下,bom内有对应
|
List<String> materialUrlList = new LinkedList<>();
|
//存于产品下,bom内没对应
|
List<String> productUrlList = new LinkedList<>();
|
|
//List<Material> materialDwgUrlNameList = new LinkedList<>();
|
|
fileUrlList = FileUtil.getStaticFilePath(file,fileUrlList);
|
//图纸dwg 子件/产品
|
dwgUrlList = fileUrlList.stream().filter(url->url.contains(".dwg")).collect(Collectors.toList());
|
picUrlList = fileUrlList.stream().filter(url->url.contains(".png") || url.contains(".jpeg")).collect(Collectors.toList());
|
|
dwgUrlList.forEach(dwgUrl->{
|
boolean existFlag = false;
|
for (ProductBom bom :bomList){
|
String filename = dwgUrl.substring(dwgUrl.lastIndexOf(File.separator) + 1, dwgUrl.length() - 4);
|
String fileFullName = dwgUrl.substring(dwgUrl.lastIndexOf(File.separator) + 1);
|
if(bom.getSubModel().toUpperCase().equals(filename.toUpperCase())){
|
materialUrlList.add(dwgUrl);
|
existFlag = true;
|
|
|
bom.setDwgUrl("doc_file" + File.separator + "material"
|
+ File.separator + fileFullName);
|
break;
|
}
|
}
|
if(!existFlag) {
|
productUrlList.add(dwgUrl);
|
}
|
});
|
|
//一定是有对应物料的,从bom内剥离的
|
picUrlList.forEach(picUrl->{
|
for (ProductBom bom :bomList){
|
String filename = picUrl.substring(picUrl.lastIndexOf(File.separator) + 1,picUrl.lastIndexOf("."));
|
String fileFullName = picUrl.substring(picUrl.lastIndexOf(File.separator) + 1);
|
if(bom.getSubModel().toUpperCase().equals(filename.toUpperCase())){
|
bom.setPictureUrl("doc_file" + File.separator + "material"
|
+ File.separator + fileFullName);
|
break;
|
}
|
}
|
});
|
|
//转移路径
|
String projectDir = CommonUtil.getProjectDir();
|
//doc_file/product/{产品型号}/standard或者{customCode}}/{version}/
|
//跟子件挂钩的转移到子件图纸下:doc_file/material/
|
String customCodeString = null;
|
if(customCode!=null && !customCode.equals("")) {
|
customCodeString = customCode;
|
}else{
|
customCodeString = "standard";
|
}
|
String productDir = projectDir + File.separator + "doc_file" + File.separator + "product" + File.separator + parentModel
|
+ File.separator + customCodeString + File.separator +nextVersion;
|
String materialDir = projectDir + File.separator + "doc_file" + File.separator + "material";
|
File productDirFile = new File(productDir);
|
File materialFile = new File(materialDir);
|
if(!productDirFile.exists()){
|
productDirFile.mkdirs();
|
}
|
if(!materialFile.exists()){
|
materialFile.mkdirs();
|
}
|
productUrlList.forEach(productUrl->{
|
String dwgName = productUrl.substring(productUrl.lastIndexOf(File.separator) + 1, productUrl.length() - 4);
|
|
try {
|
FileCopyUtils.copy(new File(productUrl),new File(productDir+File.separator+dwgName+".dwg"));
|
} catch (IOException e) {
|
e.printStackTrace();
|
}
|
});
|
|
materialUrlList.forEach(materialUrl->{
|
String dwgName = materialUrl.substring(materialUrl.lastIndexOf(File.separator) + 1, materialUrl.length() - 4);
|
try {
|
FileCopyUtils.copy(new File(materialUrl),new File(materialDir+File.separator+dwgName+".dwg"));
|
} catch (IOException e) {
|
e.printStackTrace();
|
}
|
});
|
|
picUrlList.forEach(picUrl->{
|
String picFullName = picUrl.substring(picUrl.lastIndexOf(File.separator) + 1);
|
try {
|
FileCopyUtils.copy(new File(picUrl),new File(materialDir+File.separator+picFullName));
|
} catch (IOException e) {
|
e.printStackTrace();
|
}
|
});
|
|
//物料表中不存在的(依据:物料编码+物料型号),则添加到物料表中去(包含product这个物料)
|
List<Material> materialExistList = mService.getListByCodeAndModelList2(bomList);
|
List<String> subCodeList = materialExistList.stream().map(Material::getSubCode).collect(Collectors.toList());
|
List<Material> materialList = new LinkedList<>();
|
bomList.forEach(bom->{
|
if(!subCodeList.contains(bom.getSubCode())){
|
Material material = new Material();
|
material.setCategory(bom.getCategory());
|
material.setCreateDate(bom.getCreateDate());
|
material.setDwgUrl(bom.getDwgUrl());
|
material.setFileUrl(bom.getFileUrl());
|
material.setMaterial(bom.getMaterial());
|
material.setNotes(bom.getNotes());
|
material.setPictureUrl(bom.getPictureUrl());
|
material.setProducer(bom.getProducer());
|
//TODO quantity
|
//material.setQuantity(bom.getQuantity());
|
// TODO 是否要更新同物料编码的老物料状态为0?
|
material.setStatus(1);
|
material.setSubCode(bom.getSubCode());
|
material.setSubModel(bom.getSubModel());
|
material.setSubName(bom.getSubName());
|
material.setSurfaceDetail(bom.getSurfaceDetail());
|
material.setThickness(bom.getThickness());
|
material.setType(bom.getType());
|
material.setUnit(bom.getUnit());
|
|
materialList.add(material);
|
}
|
});
|
//母料是否存在
|
Material materialDB = mService.getByCodeAndModel(product.getParentCode(),product.getParentModel());
|
if(materialDB==null) {
|
Material material = new Material();
|
material.setSubCode(product.getParentCode());
|
material.setSubName(product.getParentName());
|
material.setSubModel(product.getParentModel());
|
|
materialList.add(material);
|
}
|
mService.insertBatch(materialList);
|
|
//更新product/product_bom/product_history/product_bom_history
|
//根据编码和型号确定物料id,存入数据库
|
List<Material> bomMaterialList = mService.getListByCodeAndModelList2(bomList);
|
// -> product
|
product.setCreateTime(new Date());
|
product.setVersion(nextVersion);
|
deleteAndInsert(product);
|
// -> product_history
|
ProductHistory productHistory = new ProductHistory();
|
productHistory.setParentCode(product.getParentCode());
|
productHistory.setParentName(product.getParentName());
|
productHistory.setParentModel(product.getParentModel());
|
productHistory.setNotes(product.getNotes());
|
productHistory.setCustomCode(product.getCustomCode());
|
productHistory.setCreateTime(product.getCreateTime());
|
productHistory.setVersion(product.getVersion());
|
productHistory.setEnabled(1);
|
phService.insertAndUpdateEnabled(productHistory);
|
// -> product_bom
|
List<ProductBom> productBomList = new LinkedList<>();
|
bomMaterialList.forEach(bomMaterial->{
|
ProductBom bom = new ProductBom();
|
bom.setProductId(product.getId());
|
bom.setMaterialId(bomMaterial.getId());
|
bom.setSubVersion(1);
|
bom.setCreateDate(new Date());
|
|
productBomList.add(bom);
|
});
|
pbService.insertBatch(productBomList);
|
// -> product_bom_history
|
List<ProductBomHistory> bomHistoryList = new LinkedList<>();
|
bomMaterialList.forEach(bomMaterial->{
|
ProductBomHistory bomHistory = new ProductBomHistory();
|
bomHistory.setProductId(productHistory.getId());
|
bomHistory.setMaterialId(bomMaterial.getId());
|
bomHistory.setSubSVersion(1);
|
bomHistory.setSubEVersion(1);
|
bomHistory.setCreateDate(new Date());
|
|
bomHistoryList.add(bomHistory);
|
});
|
pbhService.insertBatch(bomHistoryList);
|
|
return new Response().setII(1,"新增完成");
|
}
|
|
private void deleteAndInsert(Product product) {
|
Product productDB = getByProductCodeAndCustomCode(product.getParentCode(), product.getCustomCode());
|
mapper.deleteById(productDB.getId());
|
pbService.deleteByProductId(productDB.getId());
|
mapper.insert(product);
|
}
|
|
private Product getByProductCodeAndCustomCode(String parentCode, String customCode) {
|
QueryWrapper<Product> query = Wrappers.query();
|
query.eq("parent_code",parentCode).eq("custom_code",customCode).last(" limit 1");
|
return mapper.selectOne(query);
|
}
|
//查询出所有的产品无参不分页（产品上传的是用）
|
public Response getUpBomUseProduct() {
|
List list=mapper.selectList(null);
|
return new Response().setII(1,list.size()>0,list,"");
|
}
|
}
|
