验收报告查询修改

whyclxw

3 天以前 12154b62b42df29173cdc54d7fd35d02d9a6422b

 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();
        //取第一个sheet表
        Sheet sheet = workbook.getSheetAt(0);
        int lastRowNum = sheet.getLastRowNum();
        //文件名
        String fileName = sheet.getRow(2).getCell(2).getStringCellValue().trim();
        sop.setFileName(fileName);
        //下面的row是动态的,因为文件类型的行数是动态拓展的
        //锚点:文件版本
        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();
        //文件类型开始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));

        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,"文件版本已存在,请确认SOP版本是否正确!");
            }
        }
        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,20 +292,180 @@
            }

            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(改，不存粗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 {
        //是否存在旧版本,存在则把所有旧版本状态更新为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历史");
    }
}