package com.whyc.service;
|
|
import com.github.pagehelper.PageInfo;
|
import com.whyc.dto.Response;
|
import com.whyc.dto.SopDto;
|
import com.whyc.mapper.SOPMapper;
|
import com.whyc.pojo.SOP;
|
import com.whyc.pojo.SOPFileType;
|
import com.whyc.pojo.SOPProduct;
|
import com.whyc.util.*;
|
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
|
import org.apache.poi.ss.usermodel.Cell;
|
import org.apache.poi.ss.usermodel.Sheet;
|
import org.apache.poi.ss.usermodel.Workbook;
|
import org.apache.poi.ss.usermodel.WorkbookFactory;
|
import org.springframework.beans.factory.annotation.Autowired;
|
import org.springframework.stereotype.Service;
|
import org.springframework.transaction.annotation.Transactional;
|
import org.springframework.web.multipart.MultipartFile;
|
|
import javax.annotation.Resource;
|
import java.io.IOException;
|
import java.io.InputStream;
|
import java.text.ParseException;
|
import java.util.*;
|
import java.util.stream.Collectors;
|
|
@Service
|
public class SOPService {
|
|
@Resource
|
private SOPMapper mapper;
|
|
@Autowired
|
private SOPProductService productService;
|
|
@Autowired
|
private SOPFileTypeService fileTypeService;
|
|
|
@Transactional
|
public Response<Object> excelParse(MultipartFile multipartFile) throws IOException, InvalidFormatException {
|
SOP sop = new SOP();
|
String userName = ActionUtil.getUser().getName();
|
sop.setUploadUser(userName);
|
List<SOPProduct> sopProductList = new LinkedList<>();
|
|
sop.setCreateTime(new Date());
|
Workbook workbook = null;
|
InputStream inputStream = multipartFile.getInputStream();
|
workbook = WorkbookFactory.create(inputStream);
|
inputStream.close();
|
//取第一个sheet表
|
Sheet sheet = workbook.getSheetAt(0);
|
int lastRowNum = sheet.getLastRowNum();
|
//文件名
|
sop.setFileName(sheet.getRow(2).getCell(2).getStringCellValue());
|
//下面的row是动态的,因为文件类型的行数是动态拓展的
|
//锚点:文件版本
|
int fileVersionIndex = 0;
|
int fileSuitableProductIndex = 0;
|
//校准lastRowNum
|
for (int i = 4; i <= lastRowNum; i++) {
|
String stringCellValue = sheet.getRow(i).getCell(1).getStringCellValue();
|
if(stringCellValue.equals("文件版本")){
|
fileVersionIndex = i;
|
continue;
|
}else if(stringCellValue.equals("文件适用产品")){
|
fileSuitableProductIndex = i;
|
continue;
|
}
|
if(stringCellValue.equals("发布说明")){
|
lastRowNum = i;
|
break;
|
}
|
}
|
|
|
List<Map<String,List<String>>> fileTypeList = new LinkedList<>();
|
StringBuilder fileTypeSb = new StringBuilder();
|
//文件类型开始index
|
int fileTypeStartIndex = 3;
|
for (int i = fileTypeStartIndex; i < fileVersionIndex; i++) { //这里面的都是文件类型相关内容
|
//遍历每一行,index = 2,6列,搜索所有的大阶段
|
String cellValue = sheet.getRow(i).getCell(2).getStringCellValue();
|
if(!cellValue.contains("£")&& !cellValue.contains("R")&& !cellValue.equals("")){ //确定为大阶段
|
Map<String,List<String>> map = new LinkedHashMap<>();
|
List<String> cellList = new LinkedList<>();
|
for (int j = i+1; j < fileVersionIndex; j++) {
|
Cell cell2 = sheet.getRow(j).getCell(2);
|
String cellValue2 = cell2.getStringCellValue();
|
if(!cellValue2.contains("£")&& !cellValue2.contains("R")&& !cellValue2.equals("")){ //确定为下一个大阶段
|
break;
|
}else{
|
if(cellValue2.equals("")){
|
break;
|
}
|
if(cellValue2.contains("R")){
|
fileTypeSb.append(cellValue).append("-").append(cellValue2.substring(1)).append(";");
|
}
|
cellList.add(cellValue2.substring(1));
|
|
}
|
|
//
|
Cell cell3 = sheet.getRow(j).getCell(3);
|
String cellValue3 = cell3.getStringCellValue();
|
if(!cellValue3.contains("£")&& !cellValue3.contains("R")&& !cellValue3.equals("")){ //确定为下一个大阶段
|
break;
|
}else{
|
if(cellValue3.equals("")){
|
break;
|
}
|
if(cellValue3.contains("R")){
|
fileTypeSb.append(cellValue).append("-").append(cellValue3.substring(1)).append(";");
|
}
|
cellList.add(cellValue3.substring(1));
|
|
}
|
|
//
|
Cell cell4 = sheet.getRow(j).getCell(4);
|
String cellValue4 = cell4.getStringCellValue();
|
if(!cellValue4.contains("£")&& !cellValue4.contains("R")&& !cellValue4.equals("")){ //确定为下一个大阶段
|
break;
|
}else{
|
if(cellValue4.equals("")){
|
break;
|
}
|
if(cellValue4.contains("R")){
|
fileTypeSb.append(cellValue).append("-").append(cellValue4.substring(1)).append(";");
|
}
|
cellList.add(cellValue4.substring(1));
|
|
}
|
|
//
|
Cell cell5 = sheet.getRow(j).getCell(5);
|
String cellValue5 = cell5.getStringCellValue();
|
if(!cellValue5.contains("£")&& !cellValue5.contains("R")&& !cellValue5.equals("")){ //确定为下一个大阶段
|
break;
|
}else{
|
if(cellValue5.equals("")){
|
break;
|
}
|
if(cellValue5.contains("R")){
|
fileTypeSb.append(cellValue).append("-").append(cellValue5.substring(1)).append(";");
|
}
|
cellList.add(cellValue5.substring(1));
|
|
}
|
}
|
map.put(cellValue,cellList);
|
fileTypeList.add(map);
|
}
|
|
//可以提取方法
|
String cellValueRight = sheet.getRow(i).getCell(6).getStringCellValue();
|
if(!cellValueRight.contains("£")&& !cellValueRight.contains("R")&& !cellValueRight.equals("")){ //确定为大阶段
|
Map<String,List<String>> map = new LinkedHashMap<>();
|
List<String> cellList = new LinkedList<>();
|
for (int j = i+1; j < fileVersionIndex; j++) {
|
Cell cell2 = sheet.getRow(j).getCell(6);
|
String cellValue2 = cell2.getStringCellValue();
|
if(!cellValue2.contains("£")&& !cellValue2.contains("R")&& !cellValue2.equals("")){ //确定为下一个大阶段
|
break;
|
}else{
|
if(cellValue2.equals("")){
|
break;
|
}
|
if(cellValue2.contains("R")){
|
fileTypeSb.append(cellValueRight).append("-").append(cellValue2.substring(1)).append(";");
|
}
|
cellList.add(cellValue2.substring(1));
|
|
}
|
|
//
|
Cell cell3 = sheet.getRow(j).getCell(7);
|
String cellValue3 = cell3.getStringCellValue();
|
if(!cellValue3.contains("£")&& !cellValue3.contains("R")&& !cellValue3.equals("")){ //确定为下一个大阶段
|
break;
|
}else{
|
if(cellValue3.equals("")){
|
break;
|
}
|
if(cellValue3.contains("R")){
|
fileTypeSb.append(cellValueRight).append("-").append(cellValue3.substring(1)).append(";");
|
}
|
cellList.add(cellValue3.substring(1));
|
|
}
|
|
//
|
Cell cell4 = sheet.getRow(j).getCell(8);
|
String cellValue4 = cell4.getStringCellValue();
|
if(!cellValue4.contains("£")&& !cellValue4.contains("R")&& !cellValue4.equals("")){ //确定为下一个大阶段
|
break;
|
}else{
|
if(cellValue4.equals("")){
|
break;
|
}
|
if(cellValue4.contains("R")){
|
fileTypeSb.append(cellValueRight).append("-").append(cellValue4.substring(1)).append(";");
|
}
|
cellList.add(cellValue4.substring(1));
|
|
}
|
|
//
|
Cell cell5 = sheet.getRow(j).getCell(9);
|
String cellValue5 = cell5.getStringCellValue();
|
if(!cellValue5.contains("£")&& !cellValue5.contains("R")&& !cellValue5.equals("")){ //确定为下一个大阶段
|
break;
|
}else{
|
if(cellValue5.equals("")){
|
break;
|
}
|
if(cellValue5.contains("R")){
|
fileTypeSb.append(cellValueRight).append("-").append(cellValue5.substring(1)).append(";");
|
}
|
cellList.add(cellValue5.substring(1));
|
|
}
|
}
|
map.put(cellValueRight,cellList);
|
fileTypeList.add(map);
|
}
|
}
|
|
sop.setFileTypeList(fileTypeList);
|
|
sop.setFileType(fileTypeSb.toString());
|
|
sop.setFileVersion(sheet.getRow(fileVersionIndex).getCell(2).getStringCellValue());
|
sop.setFileRelatedVersion(sheet.getRow(fileVersionIndex).getCell(7).getStringCellValue());
|
|
sop.setEditor(sheet.getRow(fileVersionIndex+1).getCell(2).getStringCellValue());
|
sop.setAuditor(sheet.getRow(fileVersionIndex+1).getCell(7).getStringCellValue());
|
|
String releaseTimeStr = sheet.getRow(fileVersionIndex+2).getCell(2).getStringCellValue();
|
Date releaseTime;
|
try {
|
if(releaseTimeStr.contains(".")){
|
releaseTime = DateUtil.YYYY_MM_DD_UNION2.parse(releaseTimeStr);
|
}else if(releaseTimeStr.contains("-")){
|
releaseTime = DateUtil.YYYY_MM_DD_UNION3.parse(releaseTimeStr);
|
}else if(releaseTimeStr.contains("/")){
|
releaseTime = DateUtil.YYYY_MM_DD_UNION4.parse(releaseTimeStr);
|
}else {
|
releaseTime = DateUtil.YYYY_MM_DD_UNION.parse(releaseTimeStr);
|
}
|
}catch (ParseException e){
|
return new Response().set(1,false,"申请日期格式错误!");
|
}
|
sop.setReleaseDate(releaseTime);
|
|
String releaseNotes = sheet.getRow(lastRowNum).getCell(2).getStringCellValue();
|
sop.setReleaseNotes(releaseNotes);
|
|
//第10行开始,倒数第2行截止
|
for (int i = fileSuitableProductIndex+1; i < lastRowNum; i++) {
|
SOPProduct product = new SOPProduct();
|
//取第3列,第5列
|
Cell cell = sheet.getRow(i).getCell(2);
|
cell.setCellType(Cell.CELL_TYPE_STRING);
|
String code = cell.getStringCellValue();
|
String codeAutoFill;
|
if(code.equals("")){
|
codeAutoFill = "";
|
}else {
|
codeAutoFill = CommonUtil.codeAutoFill(code);
|
}
|
|
product.setCode(codeAutoFill);
|
String model = sheet.getRow(i).getCell(7).getStringCellValue();
|
if((code.equals("") && !model.equals("")) || (!code.equals("") && model.equals(""))){
|
return new Response<>().set(1,false,"物料编码和型号必须同时为空");
|
}else{
|
product.setModel(model);
|
}
|
if((code.equals("") && model.equals(""))){
|
sop.setCurrentFlag(1);//通用
|
}
|
sopProductList.add(product);
|
}
|
|
//存储excel文件,/sop_submit/xxx_202306250506.xlsx(改，不存粗excel了)
|
/*String originalFilename = multipartFile.getOriginalFilename();
|
String[] fileNameSplit = originalFilename.split("\\.");
|
String dateFormat = DateUtil.YYYY_MM_DD_HH_MM_SS_UNION.format(new Date());
|
String newFileName = fileNameSplit[0]+"_"+dateFormat+"."+fileNameSplit[1];
|
|
String fileUrl = FileUtil.saveFile(multipartFile,"/sop_submit/"+newFileName);
|
sop.setFileUrl(fileUrl);*/
|
|
sopProductList = sopProductList.stream().filter(product -> !product.getCode().equals("")).collect(Collectors.toList());
|
sop.setSopProductList(sopProductList);
|
|
return new Response().setII(1,true,sop,"文件解析成功");
|
}
|
|
@Transactional
|
public Response add(SOP sop,MultipartFile multipartFile) throws IOException {
|
List<SOPProduct> sopProductList = sop.getSopProductList();
|
String originalFilename = multipartFile.getOriginalFilename();
|
String[] fileNameSplit = originalFilename.split("\\.");
|
String dateFormat = DateUtil.YYYY_MM_DD_HH_MM_SS_UNION.format(new Date());
|
String newFileName = fileNameSplit[0]+"_"+dateFormat+"."+fileNameSplit[1];
|
String fileUrlTemp = FileUtil.saveFile(multipartFile,"/sop/"+newFileName);
|
//String fileUrlTemp = sop.getFileUrl();
|
//fileUrlTemp = fileUrlTemp.replace("sop_submit", "sop");
|
sop.setFileUrl(fileUrlTemp);
|
mapper.insert(sop);
|
if(sopProductList.size()>0) {
|
sopProductList.forEach(product -> product.setSopId(sop.getId()));
|
productService.insertBatch(sopProductList);
|
}
|
//文件转移
|
/*String fileUrl = sop.getFileUrl();
|
String projectDir = CommonUtil.getProjectDir();
|
String filePathFrom = projectDir + File.separator + fileUrl.replace("sop","sop_submit");
|
File fileFrom = new File(filePathFrom);
|
String filePathTo = filePathFrom.replace("sop_submit","sop");
|
File fileTo = new File(filePathTo);
|
if(!fileTo.getParentFile().exists()){
|
fileTo.getParentFile().mkdirs();
|
}
|
FileCopyUtils.copy(fileFrom,fileTo);*/
|
|
//新增的文件类型,追加到表中
|
List<SOPFileType> typeList = new LinkedList<>();
|
List<Map<String, List<String>>> fileTypeList = sop.getFileTypeList();
|
for (int i = 0; i < fileTypeList.size(); i++) {
|
Map<String, List<String>> map = fileTypeList.get(i);
|
Set<String> type1Set = map.keySet();
|
for (String type1 : type1Set) {
|
List<String> type2List = map.get(type1);
|
for (String type2 : type2List) {
|
SOPFileType fileType = new SOPFileType();
|
fileType.setType1(type1);
|
fileType.setType2(type2);
|
typeList.add(fileType);
|
}
|
}
|
}
|
List<SOPFileType> recordListInDB = fileTypeService.getAllInDB(typeList);
|
if(typeList.size()!=recordListInDB.size()) {
|
List<SOPFileType> newRecordList = new LinkedList<>();
|
|
List<String> uploadTypeStrList = typeList.stream().map(type -> type.getType1() + ";" + type.getType2()).collect(Collectors.toList());
|
List<String> dbTypeStrList = recordListInDB.stream().map(type -> type.getType1() + ";" + type.getType2()).collect(Collectors.toList());
|
Date now = new Date();
|
for (int i = 0; i < uploadTypeStrList.size(); i++) {
|
String uploadTypeStr = uploadTypeStrList.get(i);
|
if (!dbTypeStrList.contains(uploadTypeStr)) {
|
SOPFileType sopFileType = new SOPFileType();
|
sopFileType.setType1(uploadTypeStr.split(";")[0]);
|
sopFileType.setType2(uploadTypeStr.split(";")[1]);
|
sopFileType.setCreateTime(now);
|
newRecordList.add(sopFileType);
|
}
|
}
|
if (newRecordList.size() > 0) {
|
fileTypeService.addBatch(newRecordList);
|
}
|
}
|
|
return new Response().setII(1,"上传完成");
|
}
|
|
//查询sop信息
|
public Response getSopInfo(List<SopDto> sqlList, String code, String model,int pageCurr,int pageSize) {
|
String sqlStr=" ";
|
if(sqlList!=null&&sqlList.size()>0){
|
for (SopDto dto:sqlList) {
|
if(dto.getChileType().size()>0){
|
for (int i=0;i<dto.getChileType().size();i++){
|
sqlStr+=" and file_type like "+"'%"+dto.getParentType()+"-"+dto.getChileType().get(i)+"%' ";
|
}
|
}
|
}
|
}
|
List<SOP> list=mapper.getSopInfo(sqlStr,code,model);
|
PageInfo pageInfo= PageInfoUtils.list2PageInfo(list,pageCurr,pageSize);
|
|
return new Response().setII(1,list.size()>0,pageInfo,"查询sop信息");
|
}
|
|
|
}
|
