package com.whyc.service;
|
|
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
|
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
|
import com.whyc.dto.FileUrlDTO;
|
import com.whyc.dto.Response;
|
import com.whyc.mapper.ProductBomApprovingMapper;
|
import com.whyc.pojo.DocUser;
|
import com.whyc.pojo.ProductBomApproving;
|
import com.whyc.util.*;
|
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.web.multipart.MultipartFile;
|
|
import javax.annotation.Resource;
|
import java.io.*;
|
import java.text.SimpleDateFormat;
|
import java.util.*;
|
import java.util.stream.Collectors;
|
|
@Service
|
public class ProductBomApprovingService {
|
|
@Resource
|
private ProductBomApprovingMapper mapper;
|
|
@Autowired
|
private WorksheetMainService mainService;
|
|
public List<ProductBomApproving> excelParse(InputStream inputStream) throws IOException, InvalidFormatException {
|
List<ProductBomApproving> list = new LinkedList<>();
|
Workbook workbook = null;
|
workbook = WorkbookFactory.create(inputStream);
|
List<? extends PictureData> allPictures = workbook.getAllPictures();
|
inputStream.close();
|
//取第一个sheet表
|
Sheet sheet = workbook.getSheetAt(0);
|
int lastRowNum = sheet.getLastRowNum();
|
//取第三行,并以第三行开始
|
Row row2 = sheet.getRow(1);
|
short lastCellNum = row2.getLastCellNum();
|
for (int i = 2; i < lastRowNum+1; i++) {
|
ProductBomApproving bomApproving = new ProductBomApproving();
|
for (int j = 1; j < lastCellNum; j++) {
|
Row row = sheet.getRow(i);
|
Cell cell = row.getCell(j);
|
String cellValue = null;
|
Double cellValueInt = 0d;
|
if(j == 9){
|
cellValueInt = cell.getNumericCellValue();
|
}else if(j!=15){
|
cellValue = cell.getStringCellValue();
|
}
|
switch (j){
|
case 1:{bomApproving.setParentCode(cellValue);}break;
|
case 2:{bomApproving.setParentName(cellValue);}break;
|
case 3:{bomApproving.setParentModel(cellValue);}break;
|
case 4:{bomApproving.setCategory(cellValue);}break;
|
case 5:{bomApproving.setSubCode(cellValue);}break;
|
case 6:{bomApproving.setSubName(cellValue);}break;
|
case 7:{bomApproving.setSubModel(cellValue);}break;
|
case 8:{bomApproving.setUnit(cellValue);}break;
|
case 9:{bomApproving.setQuantity(cellValueInt.intValue());}break;
|
case 10:{bomApproving.setProducer(cellValue);}break;
|
case 11:{bomApproving.setMaterial(cellValue);}break;
|
case 12:{bomApproving.setThickness(cellValue);}break;
|
case 13:{bomApproving.setSurfaceDetail(cellValue);}break;
|
case 14:{bomApproving.setNotes(cellValue);}break;
|
case 15:{
|
//图片,从0开始,到图片size为止
|
int k = i-2;
|
if(k<allPictures.size()){
|
PictureData pictureData = allPictures.get(k);
|
//图片存储 product_approving/username/2022-07/
|
DocUser user = ActionUtil.getUser();
|
String dateFormat = new SimpleDateFormat("YYYY-MM").format(new Date());
|
String rootFile = CommonUtil.getRootFile();
|
String approvingPath = rootFile + File.separator + "product_approving" + File.separator + user.getName() + File.separator + dateFormat;
|
File provingFile = new File(approvingPath);
|
if(!provingFile.exists()){
|
provingFile.mkdirs();
|
}
|
String suffix = pictureData.suggestFileExtension();
|
String picturePath = approvingPath + File.separator + bomApproving.getSubModel() + "." + suffix;
|
String picturePathFront = "doc_file" + File.separator + "product_approving" + File.separator + user.getName() + File.separator + dateFormat + File.separator + bomApproving.getSubModel() + "." + suffix;
|
byte[] data = pictureData.getData();
|
FileOutputStream fileOutputStream = null;
|
File pictureFile = new File(picturePath);
|
fileOutputStream = new FileOutputStream(pictureFile);
|
fileOutputStream.write(data);
|
|
bomApproving.setPictureUrl(picturePathFront);
|
}
|
}break;
|
}
|
}
|
list.add(bomApproving);
|
}
|
|
return list;
|
}
|
|
public void insert(List<ProductBomApproving> bomList) {
|
mapper.insertBatchSomeColumn(bomList);
|
}
|
|
public Response zipParse(MultipartFile file) throws IOException, InvalidFormatException {
|
List<ProductBomApproving> list = new LinkedList<>();
|
List<ProductBomApproving> 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_approving/${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_approving" + 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();
|
}*/
|
if (!parentFile.exists()) {
|
parentFile.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++) {
|
ProductBomApproving bomApproving = new ProductBomApproving();
|
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:{bomApproving.setId(cellValueInt.intValue());}break;
|
case 1:{bomApproving.setParentCode(cellValue);}break;
|
case 2:{bomApproving.setParentName(cellValue);}break;
|
case 3:{bomApproving.setParentModel(cellValue);}break;
|
case 4:{bomApproving.setCategory(cellValue);}break;
|
case 5:{bomApproving.setSubCode(cellValue); }break;
|
case 6:{bomApproving.setSubName(cellValue);}break;
|
case 7:{
|
bomApproving.setSubModel(cellValue);
|
//判断图纸查看是否存在
|
bomApproving.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())){
|
//存在
|
bomApproving.setDwgExist(1);
|
bomApproving.setDwgUrl(fileUrlDTO.getHttpFileUrl());
|
break;
|
}
|
}
|
}break;
|
case 8:{bomApproving.setUnit(cellValue);}break;
|
case 9:{bomApproving.setQuantity(cellValueInt.intValue());}break;
|
case 10:{bomApproving.setProducer(cellValue);}break;
|
case 11:{bomApproving.setMaterial(cellValue);}break;
|
case 12:{bomApproving.setThickness(cellValue);}break;
|
case 13:{bomApproving.setSurfaceDetail(cellValue);}break;
|
case 14:{bomApproving.setNotes(cellValue);}break;
|
case 15:{
|
//图片,从0开始,到图片size为止
|
int m = k-2;
|
if(m<allPictures.size()){
|
PictureData pictureData = allPictures.get(m);
|
//图片存储 product_approving/username/2022-07/
|
String approvingPath = rootFile + File.separator + "product_approving" + 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 + bomApproving.getSubModel() + "." + suffix;
|
String picturePathFront = "doc_file" + File.separator + "product_approving" + File.separator + user.getName() + File.separator + dateFormat + File.separator + timeStamp + File.separator + bomApproving.getSubModel() + "." + suffix;
|
byte[] data = pictureData.getData();
|
FileOutputStream fileOutputStream = null;
|
File pictureFile = new File(picturePath);
|
fileOutputStream = new FileOutputStream(pictureFile);
|
fileOutputStream.write(data);
|
|
bomApproving.setPictureUrl(picturePathFront);
|
}
|
}break;
|
}
|
}
|
bomList.add(bomApproving);
|
}
|
}
|
/*//这个是子表数据,用做对比校验
|
else if(fileTempUrl.contains(".xls")){
|
File fileTemp = new File(fileTempUrl);
|
Workbook workbook = null;
|
workbook = WorkbookFactory.create(fileTemp);
|
List<? extends PictureData> allPictures = workbook.getAllPictures();
|
//取第一个sheet表
|
Sheet sheet = workbook.getSheetAt(0);
|
int lastRowNum = sheet.getLastRowNum();
|
//取第三行,并以第三行开始
|
Row rowTemp = sheet.getRow(1);
|
short lastCellNum = rowTemp.getLastCellNum();
|
//先单独获取产品型号和版本号/分类
|
Row row = sheet.getRow(1);
|
String type = row.getCell(1).getStringCellValue();
|
|
Row row2 = sheet.getRow(2);
|
String productModel = row2.getCell(5).getStringCellValue();
|
Row row3 = sheet.getRow(3);
|
String productVersion = row3.getCell(5).getStringCellValue();
|
for (int l = 9; l < lastRowNum-1; l++) {
|
ProductBomApproving bomApproving = new ProductBomApproving();
|
bomApproving.setParentModel(productModel);
|
//lxw注释
|
//bomApproving.setParentVersion(productVersion);
|
bomApproving.setType(type);
|
bomApproving.setExcelName(fileTempUrl.substring(fileTempUrl.lastIndexOf(File.separator)+1));
|
|
for (int m = 2; m < lastCellNum; m++) {
|
row = sheet.getRow(l);
|
Cell cell = row.getCell(m);
|
String cellValue = null;
|
int cellValueInt = 0;
|
Double cellValueDouble = null;
|
if(m == 3){
|
cellValueDouble = cell.getNumericCellValue();
|
DecimalFormat decimalFormat = new DecimalFormat("0");
|
cellValue = decimalFormat.format(cellValueDouble);
|
}
|
else if(m ==7){
|
cellValueDouble = cell.getNumericCellValue();
|
if(cellValueDouble.equals(0d)){
|
cellValue = "";
|
}else{
|
cellValue = cellValueDouble.toString();
|
}
|
}
|
else if(m==8){
|
cellValueDouble = cell.getNumericCellValue();
|
}
|
else {
|
cellValue = cell.getStringCellValue();
|
}
|
|
switch (m){
|
case 2:{bomApproving.setCategory(cellValue);}break;
|
case 3:{bomApproving.setSubCode(cellValue);}break;
|
case 4:{bomApproving.setSubName(cellValue);}break;
|
case 5:{
|
bomApproving.setSubModel(cellValue);
|
//判断图纸查看是否存在
|
bomApproving.setDwgExist(0);
|
String dwgFileName = cellValue+".dwg";
|
for (int n = 0; n < dwgExistsList.size(); n++) {
|
FileUrlDTO fileUrlDTO = dwgExistsList.get(n);
|
if(dwgFileName.equals(fileUrlDTO.getFileName())){
|
//存在
|
bomApproving.setDwgExist(1);
|
bomApproving.setDwgUrl(fileUrlDTO.getHttpFileUrl());
|
break;
|
}
|
}
|
}break;
|
case 6:{bomApproving.setMaterial(cellValue);}break;
|
case 7:{bomApproving.setThickness(cellValue);}break;
|
case 8:{bomApproving.setQuantity(cellValueDouble.intValue());}break;
|
case 9:{bomApproving.setSurfaceDetail(cellValue);}break;
|
case 10:{bomApproving.setNotes(cellValue);}break;
|
case 11:{
|
//图片,从0开始,到图片size为止
|
int k = l-9;
|
if(k<allPictures.size()-1){
|
PictureData pictureData = allPictures.get(k+1);
|
//图片存储 doc_file/product_approving/${username}/{dateFormat}/${timeStamp}
|
String suffix = pictureData.suggestFileExtension();
|
String picturePath = filePath + File.separator + bomApproving.getSubModel() + "." + suffix;
|
String picturePathFront = "doc_file" + File.separator + "product_approving" + File.separator + user.getName() + File.separator + dateFormat + File.separator + timeStamp + File.separator + bomApproving.getSubModel() + "." + suffix;
|
byte[] data = pictureData.getData();
|
FileOutputStream fileOutputStream = null;
|
File pictureFile = new File(picturePath);
|
fileOutputStream = new FileOutputStream(pictureFile);
|
fileOutputStream.write(data);
|
|
bomApproving.setPictureUrl(picturePathFront);
|
}
|
}break;
|
}
|
}
|
list.add(bomApproving);
|
}
|
}*/
|
}
|
/*String parentModel = list.get(0).getParentModel();
|
for (int i = 0; i < list.size(); i++) {
|
if(!parentModel.equals(list.get(i).getParentModel())){
|
return new Response().set(1,false,"拒绝解析,一次审批包含不同的产品修改");
|
}
|
}*/
|
//通过包名,查询工单流程审批标题
|
//String nextTitle = mainService.getNextTitle(originalFilename);
|
|
/*//子表,逐条对比bom表,子料编码/子料型号/子料数量
|
for (ProductBomApproving sub:list){
|
String subCode = sub.getSubCode();
|
String subModel = sub.getSubModel();
|
Integer quantity = sub.getQuantity();
|
String excelName = sub.getExcelName();
|
boolean exists = false;
|
for (ProductBomApproving reference:bomList){
|
if(subCode.equals(reference.getSubCode())){
|
exists = true;
|
if(subModel.equals(reference.getSubModel())){
|
if(quantity.intValue() == reference.getQuantity()){
|
break;
|
}else{
|
return response.set(1,false,excelName+"中货品编码:"+subCode+"对应的数量:"+quantity+"在bom表中不正确");
|
}
|
}else{
|
return response.set(1,false,excelName+"中货品编码:"+subCode+"对应的规格型号:"+subModel+"在bom表中不正确");
|
}
|
}
|
}
|
if(!exists){
|
return response.set(1,false,excelName+"中货品编码:"+subCode+"在bom表中不存在");
|
}
|
}*/
|
/*//处理名称重复的子件
|
List<ProductBomApproving> newBomList = new LinkedList<>();
|
Map<String, List<ProductBomApproving>> subNameMap = bomList.stream().collect(Collectors.groupingBy(ProductBomApproving::getSubName));
|
subNameMap.forEach((subName,approvingList)->{
|
if(approvingList.size()>1){
|
for (int i = 0; i < approvingList.size(); i++) {
|
ProductBomApproving bomApproving = approvingList.get(i);
|
bomApproving.setSubName(subName+(i+1));
|
newBomList.add(bomApproving);
|
}
|
}else{
|
newBomList.addAll(approvingList);
|
}
|
});*/
|
List<ProductBomApproving> collect = bomList.stream().sorted(Comparator.comparing(ProductBomApproving::getId)).collect(Collectors.toList());
|
String nextTitle = originalFilename.substring(0,originalFilename.lastIndexOf("."));
|
return response.setIII(1,true,collect,nextTitle,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;
|
}
|
|
/**
|
*
|
* @param dwgUrl 预览dwg图纸文件,后追加预览word文档
|
* @return
|
* @throws IOException
|
*/
|
public Response dwgReview(String dwgUrl) throws IOException {
|
String fileSuffix = dwgUrl.substring(dwgUrl.lastIndexOf(".") + 1);
|
//绝对路径xxx/doc_file
|
String rootFile = CommonUtil.getRootFile();
|
String dwgSubFilePath = dwgUrl.substring(dwgUrl.indexOf("doc_file")+8);
|
String dwgSubFileDirPath = dwgSubFilePath.substring(0,dwgSubFilePath.lastIndexOf(File.separator));
|
File dwgFile = new File(rootFile + dwgSubFilePath);
|
String pdfUrl = "";
|
if(fileSuffix.equals("dwg")) {
|
pdfUrl = dwgFile.getParent() + File.separator + dwgFile.getName().substring(0, dwgFile.getName().lastIndexOf(".")) + "-dwg.pdf";
|
}else if(fileSuffix.contains("doc")){
|
pdfUrl = dwgFile.getParent() + File.separator + dwgFile.getName().substring(0, dwgFile.getName().lastIndexOf(".")) + "-doc.pdf";
|
}
|
File pdfFile = new File(pdfUrl);
|
String pdfFileName = null;
|
if(!pdfFile.exists()) {
|
if(fileSuffix.equals("dwg")) {
|
//pdfFileName = DwgToPdfUtil.dwg2Pdf(dwgFile);
|
DwgToPdfUtil.dwg2Pdf(dwgFile);
|
}else if(fileSuffix.contains("doc")){
|
Word2PdfAsposeUtil.doc2pdf(dwgFile.getAbsolutePath(),pdfUrl);
|
}
|
|
}/*else{
|
pdfFileName = pdfFile.getName();
|
}*/
|
pdfFileName = pdfFile.getName();
|
return new Response().set(1,"doc_file"+dwgSubFileDirPath+File.separator+pdfFileName);
|
}
|
|
public List<ProductBomApproving> getList(Integer productApprovingId) {
|
QueryWrapper<ProductBomApproving> query = Wrappers.query();
|
query.eq("product_approving_id",productApprovingId);
|
return mapper.selectList(query);
|
}
|
}
|
