From cf9a5039e6db9d1d5963e3fe1a37d00169ec2ef7 Mon Sep 17 00:00:00 2001
From: whyclxw <810412026@qq.com>
Date: 星期三, 25 六月 2025 11:18:22 +0800
Subject: [PATCH] 验收报告修改
---
src/main/java/com/whyc/service/SOPService.java | 446 +++++++++++++++++++++++++++++++++++++++++++++++--------
1 files changed, 382 insertions(+), 64 deletions(-)
diff --git a/src/main/java/com/whyc/service/SOPService.java b/src/main/java/com/whyc/service/SOPService.java
index 2d57383..9c6eed5 100644
--- a/src/main/java/com/whyc/service/SOPService.java
+++ b/src/main/java/com/whyc/service/SOPService.java
@@ -1,22 +1,32 @@
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.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.SOPLockLog;
import com.whyc.pojo.SOPProduct;
-import com.whyc.util.CommonUtil;
-import com.whyc.util.DateUtil;
+import com.whyc.util.*;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
-import org.apache.poi.ss.usermodel.*;
+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.Date;
-import java.util.LinkedList;
-import java.util.List;
+import java.util.*;
import java.util.stream.Collectors;
@Service
@@ -25,76 +35,228 @@
@Resource
private SOPMapper mapper;
+ @Autowired
+ private SOPProductService productService;
- public Response<Object> excelParse(InputStream inputStream) throws IOException, InvalidFormatException {
+ @Autowired
+ private SOPFileTypeService fileTypeService;
+
+ @Autowired
+ private SOPLockLogService sopLockLogService;
+
+
+ @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();
//鍙栫涓�涓猻heet琛�
Sheet sheet = workbook.getSheetAt(0);
int lastRowNum = sheet.getLastRowNum();
+ //鏂囦欢鍚�
+ String fileName = sheet.getRow(2).getCell(2).getStringCellValue().trim();
+ sop.setFileName(fileName);
+ //涓嬮潰鐨剅ow鏄姩鎬佺殑,鍥犱负鏂囦欢绫诲瀷鐨勮鏁版槸鍔ㄦ�佹嫇灞曠殑
+ //閿氱偣:鏂囦欢鐗堟湰
+ int fileVersionIndex = 0;
+ int fileSuitableProductIndex = 0;
//鏍″噯lastRowNum
- for (int i = 9; i <= lastRowNum; i++) {
- if(sheet.getRow(i).getCell(1).getStringCellValue().equals("鍙戝竷璇存槑")){
+ 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;
}
}
- //鍥哄畾5鍒�
- short cellNum = 5;
- //鏂囦欢鍚�
- sop.setFileName(sheet.getRow(2).getCell(2).getStringCellValue());
- //鏂囦欢绫诲瀷
- Row rowType = sheet.getRow(4);
- String typeStr1 = rowType.getCell(2).getStringCellValue();
- String typeStr2 = rowType.getCell(3).getStringCellValue();
- String typeStr3 = rowType.getCell(4).getStringCellValue();
- String typeStr4 = rowType.getCell(5).getStringCellValue();
- String typeStr5 = rowType.getCell(6).getStringCellValue();
- String typeStr6 = rowType.getCell(7).getStringCellValue();
- String typeStr7 = rowType.getCell(8).getStringCellValue();
- String typeStr8 = rowType.getCell(9).getStringCellValue();
- int fileType;
- if(typeStr1.contains("R")){
- fileType = 11;
- }
- else if(typeStr2.contains("R")){
- fileType = 12;
- }
- else if(typeStr3.contains("R")){
- fileType = 13;
- }
- else if(typeStr4.contains("R")){
- fileType = 14;
- }
- else if(typeStr5.contains("R")){
- fileType = 21;
- }
- else if(typeStr6.contains("R")){
- fileType = 22;
- }
- else if(typeStr7.contains("R")){
- fileType = 23;
- }
- else if(typeStr8.contains("R")){
- fileType = 24;
- }else{
- return new Response<>().set(1,false,"鏂囦欢绫诲瀷鏈嬀閫�");
- }
- sop.setFileType(fileType);
- sop.setFileVersion(sheet.getRow(5).getCell(2).getStringCellValue());
- sop.setFileRelatedVersion(sheet.getRow(5).getCell(7).getStringCellValue());
+ List<Map<String,List<String>>> fileTypeList = new LinkedList<>();
+ StringBuilder fileTypeSb = new StringBuilder();
+ //鏂囦欢绫诲瀷寮�濮媔ndex
+ 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));
- sop.setEditor(sheet.getRow(6).getCell(2).getStringCellValue());
- sop.setAuditor(sheet.getRow(6).getCell(7).getStringCellValue());
+ }
- String releaseTimeStr = sheet.getRow(7).getCell(2).getStringCellValue();
+ //
+ 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());
+ //鏍￠獙鏂囦欢鐗堟湰鏄惁宸茬粡瀛樺湪
+ String fileVersion = sheet.getRow(fileVersionIndex).getCell(2).getStringCellValue().trim();
+ QueryWrapper<SOP> query = Wrappers.query();
+ query.select("file_version").eq("file_name",fileName);
+ List<SOP> sopListExists = mapper.selectList(query);
+ for (int i = 0; i < sopListExists.size(); i++) {
+ SOP sopExists = sopListExists.get(i);
+ if(sopExists.getFileVersion().equals(fileVersion)){
+ return new Response().set(1,false,"鏂囦欢鐗堟湰宸插瓨鍦�,璇风‘璁OP鐗堟湰鏄惁姝ｇ‘!");
+ }
+ }
+ sop.setFileVersion(fileVersion);
+ 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(".")){
@@ -115,11 +277,11 @@
sop.setReleaseNotes(releaseNotes);
//绗�10琛屽紑濮�,鍊掓暟绗�2琛屾埅姝�
- int applyModelNum = lastRowNum-9;
- for (int i = 0; i < applyModelNum; i++) {
+ int currentFlag=0; //閫氱敤琛ㄧず(0:闈為�氱敤,1:閫氱敤)
+ for (int i = fileSuitableProductIndex+1; i < lastRowNum; i++) {
SOPProduct product = new SOPProduct();
//鍙栫3鍒�,绗�5鍒�
- Cell cell = sheet.getRow(9 + i).getCell(2);
+ Cell cell = sheet.getRow(i).getCell(2);
cell.setCellType(Cell.CELL_TYPE_STRING);
String code = cell.getStringCellValue();
String codeAutoFill;
@@ -130,16 +292,26 @@
}
product.setCode(codeAutoFill);
- String model = sheet.getRow(9 + i).getCell(7).getStringCellValue();
+ 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);
}
- //TODO 瀛樺偍excel鏂囦欢
-
+ if((!code.equals("") && !model.equals(""))){
+ currentFlag+=1;
+ }
sopProductList.add(product);
}
+ sop.setCurrentFlag(currentFlag>0?0:1);//閫氱敤
+ //瀛樺偍excel鏂囦欢,/sop_submit/xxx_202306250506.xlsx(鏀癸紝涓嶅瓨绮梕xcel浜�)
+ /*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);
@@ -147,7 +319,153 @@
return new Response().setII(1,true,sop,"鏂囦欢瑙ｆ瀽鎴愬姛");
}
- public Response add(SOP sop) {
- return null;
+ @Transactional
+ public Response add(SOP sop,MultipartFile multipartFile) throws IOException {
+ //鏄惁瀛樺湪鏃х増鏈�,瀛樺湪鍒欐妸鎵�鏈夋棫鐗堟湰鐘舵�佹洿鏂颁负0
+ String userName = ActionUtil.getUser().getName();
+ Date now = new Date();
+ QueryWrapper<SOP> query = Wrappers.query();
+ query.eq("file_name",sop.getFileName()).eq("status",1);
+ List<SOP> sopListExists = mapper.selectList(query);
+ if(sopListExists.size()>0){
+ for (SOP sopExists : sopListExists) {
+ sopExists.setStatus(0);
+ mapper.updateById(sopExists);
+
+ SOPLockLog lockLog = new SOPLockLog();
+ lockLog.setSopId(sopExists.getId());
+ lockLog.setReason("鏂扮増鏈笂浼�,绯荤粺鑷姩閿佸畾鏃х増鏈�");
+ lockLog.setUserName(userName);
+ lockLog.setCreateTime(now);
+ lockLog.setStatus(0);
+ sopLockLogService.insert(lockLog);
+ }
+ }
+
+ List<SOPProduct> sopProductList = sop.getSopProductList();
+ String originalFilename = multipartFile.getOriginalFilename();
+ String[] fileNameSplit = originalFilename.split("\\.");
+ String dateFormat = DateUtil.YYYY_MM_DD_HH_MM_SS_UNION.format(now);
+ 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);
+ sop.setStatus(1);
+ 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());
+ 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, Integer status,String fileName, 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)+"%' ";
+ }
+ }else{
+ sqlStr+=" and file_type like "+"'%"+dto.getParentType()+"%' ";
+ }
+ }
+ }
+ if(status!=null) {
+ sqlStr += " and status = "+status;
+ }
+ if(fileName!=null) {
+ sqlStr += " and file_name like "+"'%"+fileName+"%' ";
+ }
+ 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淇℃伅");
+ }
+
+ //鏇存柊sop璇存槑锛堜笉鍚攣淇℃伅锛�
+ @Transactional
+ public Response updateSop(SOP sop) {
+ List<SOPProduct> sopProductList = sop.getSopProductList();
+ mapper.updateById(sop);
+ if(sopProductList.size()>0) {
+ sopProductList.forEach(product -> product.setSopId(sop.getId()));
+ productService.updateBySid(sopProductList,sop.getId());
+ }
+ return new Response().setII(1,"鏇存柊sop璇存槑瀹屾垚");
+ }
+
+ @Transactional
+ public Response updateStatusById(SOPLockLog lockLog) {
+ Integer id = lockLog.getSopId();
+ Integer status = lockLog.getStatus();
+ UpdateWrapper<SOP> update = Wrappers.update();
+ update.set("status",status).eq("id",id);
+ mapper.update(null,update);
+
+ //娣诲姞鏃ュ織
+ String name = ActionUtil.getUser().getName();
+ lockLog.setUserName(name);
+ lockLog.setCreateTime(new Date());
+ sopLockLogService.insert(lockLog);
+
+ return new Response().setII(1,"璁剧疆瀹屾垚");
+ }
+ //鏌ヨsop鍘嗗彶
+ public Response getSopHis(String fileName) {
+ List<SOP> list=mapper.getSopHis(fileName);
+ return new Response().setII(1,list.size()>0,list,"鏌ヨsop鍘嗗彶");
}
}
--
Gitblit v1.9.1