sop相关接口编写

whycxzp

2023-06-21 65100d3de76abb1e23501a079070059ba6519a9e

sop相关接口编写

 src/main/java/com/whyc/controller/SOPController.java

New file
@@ -0,0 +1,39 @@
package com.whyc.controller;

import com.whyc.dto.Response;
import com.whyc.service.SOPService;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.text.ParseException;

@RequestMapping("sop")
@RestController
@Api(tags = "SOP")
public class SOPController {

    @Autowired
    private SOPService service;

    @ApiOperation("excel解析")
    @PostMapping("excelParse")
    public Response excelParse(@RequestParam MultipartFile multipartFile) throws IOException, InvalidFormatException, ParseException {
        Response<Object> response = new Response<>();
        String name=multipartFile.getOriginalFilename();
        assert name != null;
        if(!name.substring(name.length()-4).equals(".xls") && !name.substring(name.length()-5).equals(".xlsx")){
            response.set(1,false,"文件解析错误:上传格式非excel格式");
        }else{
            response =  service.excelParse(multipartFile.getInputStream());
        }
        return response;
    }
}

 src/main/java/com/whyc/mapper/SOPMapper.java

New file
@@ -0,0 +1,6 @@
package com.whyc.mapper;

import com.whyc.pojo.SOP;

public interface SOPMapper extends CustomMapper<SOP> {
}

 src/main/java/com/whyc/pojo/SOP.java

New file
@@ -0,0 +1,146 @@
package com.whyc.pojo;

import com.baomidou.mybatisplus.annotation.TableField;
import com.fasterxml.jackson.annotation.JsonFormat;

import java.util.Date;
import java.util.List;

/**
 * SOP:操作指导书
 */
public class SOP {

    private Integer id;
    /**文件名*/
    private String fileName;
    //11,12,13,14
    //21,22,23,24
    private Integer fileType;
    /**文件版本*/
    private String fileVersion;
    /**文件关联版本*/
    private String fileRelatedVersion;
    /**编辑者*/
    private String editor;
    /**审核者*/
    private String auditor;
    /**发布时间*/
    @JsonFormat(pattern = "yyyy-MM-dd",timezone = "GMT+8")
    private Date releaseDate;
    /**发布说明*/
    private String releaseNotes;
    /**创建时间*/
    private Date createTime;
    /**上传用户*/
    private String uploadUser;
    /**文件url*/
    private String fileUrl;

    @TableField(exist = false)
    private List<SOPProduct> sopProductList;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getFileName() {
        return fileName;
    }

    public void setFileName(String fileName) {
        this.fileName = fileName;
    }

    public Integer getFileType() {
        return fileType;
    }

    public void setFileType(Integer fileType) {
        this.fileType = fileType;
    }

    public String getFileVersion() {
        return fileVersion;
    }

    public void setFileVersion(String fileVersion) {
        this.fileVersion = fileVersion;
    }

    public String getFileRelatedVersion() {
        return fileRelatedVersion;
    }

    public void setFileRelatedVersion(String fileRelatedVersion) {
        this.fileRelatedVersion = fileRelatedVersion;
    }

    public String getEditor() {
        return editor;
    }

    public void setEditor(String editor) {
        this.editor = editor;
    }

    public String getAuditor() {
        return auditor;
    }

    public void setAuditor(String auditor) {
        this.auditor = auditor;
    }

    public Date getReleaseDate() {
        return releaseDate;
    }

    public void setReleaseDate(Date releaseDate) {
        this.releaseDate = releaseDate;
    }

    public String getReleaseNotes() {
        return releaseNotes;
    }

    public void setReleaseNotes(String releaseNotes) {
        this.releaseNotes = releaseNotes;
    }

    public Date getCreateTime() {
        return createTime;
    }

    public void setCreateTime(Date createTime) {
        this.createTime = createTime;
    }

    public String getUploadUser() {
        return uploadUser;
    }

    public void setUploadUser(String uploadUser) {
        this.uploadUser = uploadUser;
    }

    public List<SOPProduct> getSopProductList() {
        return sopProductList;
    }

    public void setSopProductList(List<SOPProduct> sopProductList) {
        this.sopProductList = sopProductList;
    }

    public String getFileUrl() {
        return fileUrl;
    }

    public void setFileUrl(String fileUrl) {
        this.fileUrl = fileUrl;
    }
}

 src/main/java/com/whyc/pojo/SOPProduct.java

New file
@@ -0,0 +1,46 @@
package com.whyc.pojo;

/**
 * SOP适用的产品
 */
public class SOPProduct {
    private Integer id;
    /**编码*/
    private String code;
    /**型号*/
    private String model;
    /**sop id*/
    private Integer sopId;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getCode() {
        return code;
    }

    public void setCode(String code) {
        this.code = code;
    }

    public String getModel() {
        return model;
    }

    public void setModel(String model) {
        this.model = model;
    }

    public Integer getSopId() {
        return sopId;
    }

    public void setSopId(Integer sopId) {
        this.sopId = sopId;
    }
}

 src/main/java/com/whyc/service/SOPService.java

New file
@@ -0,0 +1,149 @@
package com.whyc.service;

import com.whyc.dto.Response;
import com.whyc.mapper.SOPMapper;
import com.whyc.pojo.SOP;
import com.whyc.pojo.SOPProduct;
import com.whyc.util.CommonUtil;
import com.whyc.util.DateUtil;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.springframework.stereotype.Service;

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.stream.Collectors;

@Service
public class SOPService {

    @Resource
    private SOPMapper mapper;


    public Response<Object> excelParse(InputStream inputStream) throws IOException, InvalidFormatException {
        SOP sop = new SOP();
        List<SOPProduct> sopProductList = new LinkedList<>();

        sop.setCreateTime(new Date());
        Workbook workbook = null;
        workbook = WorkbookFactory.create(inputStream);
        inputStream.close();
        //取第一个sheet表
        Sheet sheet = workbook.getSheetAt(0);
        int lastRowNum = sheet.getLastRowNum();
        //校准lastRowNum
        for (int i = 9; i <= lastRowNum; i++) {
            if(sheet.getRow(i).getCell(1).getStringCellValue().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());

        sop.setEditor(sheet.getRow(6).getCell(2).getStringCellValue());
        sop.setAuditor(sheet.getRow(6).getCell(7).getStringCellValue());

        String releaseTimeStr = sheet.getRow(7).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行截止
        int applyModelNum = lastRowNum-9;
        for (int i = 0; i < applyModelNum; i++) {
            SOPProduct product = new SOPProduct();
            //取第3列,第5列
            Cell cell = sheet.getRow(9 + 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(9 + 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文件

            sopProductList.add(product);
        }

        sopProductList = sopProductList.stream().filter(product -> !product.getCode().equals("")).collect(Collectors.toList());
        sop.setSopProductList(sopProductList);

        return new Response().setII(1,true,sop,"文件解析成功");
    }
}