~whyclxw/CadDrawManager.git

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.PageHelper;
import com.github.pagehelper.PageInfo;
import com.whyc.dto.FileDirPath;
import com.whyc.dto.Response;
import com.whyc.mapper.ECRMapper;
import com.whyc.pojo.ECR;
import com.whyc.util.CommonUtil;
import com.whyc.util.DateUtil;
import com.whyc.util.ExcelUtil;
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.BeanUtils;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;
 
import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.text.ParseException;
import java.util.Calendar;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;
 
@Service
public class ECRService {
 
    @Resource
    private ECRMapper mapper;
 
    @Transactional
    public Response ecrImportByExcel(MultipartFile multipartFile) throws IOException, InvalidFormatException, ParseException {
        InputStream inputStream = multipartFile.getInputStream();
        //存储excel
        String originalFilename = multipartFile.getOriginalFilename();
        Date date = new Date();
        String dateUnion = DateUtil.YYYY_MM_DD_HH_MM_SS_UNION.format(date);
        //存储路径
        String rootFile = CommonUtil.getRootFile();
        String excelDir = rootFile + "ecr";
        File excelDirFile = new File(excelDir);
        if(!excelDirFile.exists()){
            excelDirFile.mkdirs();
        }
        String excelFilePath = excelDir + File.separator + originalFilename.substring(0,originalFilename.lastIndexOf(".")) + "_" + dateUnion +originalFilename.substring(originalFilename.lastIndexOf("."));
        String excelHttpUrl = excelFilePath.substring(excelDir.lastIndexOf("doc_file"+ File.separator + "ecr"));
 
        Workbook workbook = null;
        workbook = WorkbookFactory.create(inputStream);
        inputStream.close();
        //解析excel,获取相关数据
        Sheet sheet = workbook.getSheetAt(0);
        List<ECR> ecrList = new LinkedList<>();
        ECR ecr = new ECR();
        Cell numberCell = sheet.getRow(1).getCell(5);
        if(numberCell == null || !numberCell.getStringCellValue().contains("ECR编号")){
            return new Response().set(1,false,"ECR编号不能为空或单元格格式不规范!");
        }
        String numberCellValue = numberCell.getStringCellValue();
        String number = numberCellValue.substring(6).trim();
        if(number.equals("")){
            return new Response().set(1,false,"ECR编号不能为空!");
        }
 
        Cell codeCell = sheet.getRow(2).getCell(0);
        if(codeCell == null || !codeCell.getStringCellValue().contains("变更料号")){
            return new Response().set(1,false,"变更料号不能为空或单元格格式不规范!");
        }
        String codeCellValue = codeCell.getStringCellValue();
        //变更料号:xxx
        String codeStr = codeCellValue.substring(5).trim();
        String[] codeSplit;
        if(codeStr.equals("")){
            return new Response().set(1,false,"变更料号不能为空!");
        }else{
            codeSplit = codeStr.split("/");
        }
 
        Cell modelCell = sheet.getRow(2).getCell(5);
        if(modelCell == null || !modelCell.getStringCellValue().contains("变更名称/型号")){
            return new Response().set(1,false,"变更型号不能为空或单元格格式不规范!");
        }
        String modelCellValue = modelCell.getStringCellValue();
        //变更名称/型号：xxx
        String modelStr = modelCellValue.substring(8).trim();
        String[] modelSplit;
        if(modelStr.equals("")){
            return new Response().set(1,false,"变更型号不能为空!");
        }else{
            modelSplit = modelStr.split("/");
        }
 
        Cell proposeDateCell = sheet.getRow(2).getCell(14);
        if(proposeDateCell == null || !proposeDateCell.getStringCellValue().contains("申请日期")){
            return new Response().set(1,false,"申请日期不能为空或单元格格式不规范!");
        }
        String proposeDateCellValue = proposeDateCell.getStringCellValue();
        //申请日期：xxx
        String proposeDateStr = proposeDateCellValue.substring(5).trim();
        if(proposeDateStr.equals("")){
            return new Response().set(1,false,"申请日期不能为空!");
        }
        Date proposeDate;
        try {
            if(proposeDateStr.contains(".")){
                proposeDate = DateUtil.YYYY_MM_DD_UNION2.parse(proposeDateStr);
            }else if(proposeDateStr.contains("-")){
                proposeDate = DateUtil.YYYY_MM_DD_UNION3.parse(proposeDateStr);
            }else if(proposeDateStr.contains("/")){
                proposeDate = DateUtil.YYYY_MM_DD_UNION4.parse(proposeDateStr);
            }else {
                proposeDate = DateUtil.YYYY_MM_DD_UNION.parse(proposeDateStr);
            }
        }catch (ParseException e){
            return new Response().set(1,false,"申请日期格式错误!");
        }
        Cell proposerCell = sheet.getRow(2).getCell(22);
        if(proposerCell == null || !proposerCell.getStringCellValue().contains("申请人")){
            return new Response().set(1,false,"申请人不能为空或单元格格式不规范!");
        }
        String proposerCellValue = proposerCell.getStringCellValue();
        //申请人：xxx
        String proposer = proposerCellValue.substring(4).trim();
        if(proposer.equals("")){
            return new Response().set(1,false,"申请人不能为空!");
        }
 
        Cell parentModelCell = sheet.getRow(3).getCell(0);
        if(parentModelCell == null || !parentModelCell.getStringCellValue().contains("变更所属机型/系列")){
            return new Response().set(1,false,"物料所属型号不能为空或单元格格式不规范!");
        }
        String parentModel = parentModelCell.getStringCellValue().substring(10).trim();
        if(parentModel.equals("")){
            return new Response().set(1,false,"物料所属型号不能为空!");
        }
 
        Cell changeDescriptionCell = sheet.getRow(4).getCell(0);
        if(changeDescriptionCell == null || changeDescriptionCell.getStringCellValue().trim().equals("")){
            return new Response().set(1,false,"变更描述不能为空!");
        }
        String changeDescriptionCellValue = changeDescriptionCell.getStringCellValue();
 
        //申请变更归类:index从16-22,被选中的标识为■D.品质改善
        StringBuilder changeTypeBuilder = new StringBuilder();
        String changeTypeA = sheet.getRow(16).getCell(0).getStringCellValue();
        String changeTypeB = sheet.getRow(17).getCell(0).getStringCellValue();
        String changeTypeC = sheet.getRow(18).getCell(0).getStringCellValue();
        String changeTypeD = sheet.getRow(19).getCell(0).getStringCellValue();
        String changeTypeE = sheet.getRow(20).getCell(0).getStringCellValue();
        String changeTypeF = sheet.getRow(21).getCell(0).getStringCellValue();
        String changeTypeG = sheet.getRow(22).getCell(0).getStringCellValue();
        if(changeTypeA.contains("■")){
            changeTypeBuilder.append(changeTypeA.substring(3));
        }
        if(changeTypeB.contains("■")){
            if (changeTypeBuilder.length() == 0) {
                changeTypeBuilder.append(changeTypeB.substring(3));
            } else {
                changeTypeBuilder.append(",").append(changeTypeB.substring(3));
            }
        }
        if(changeTypeC.contains("■")){
            if (changeTypeBuilder.length() == 0) {
                changeTypeBuilder.append(changeTypeC.substring(3));
            } else {
                changeTypeBuilder.append(",").append(changeTypeC.substring(3));
            }
        }
        if(changeTypeD.contains("■")){
            if (changeTypeBuilder.length() == 0) {
                changeTypeBuilder.append(changeTypeD.substring(3));
            }
            else {
                changeTypeBuilder.append(",").append(changeTypeD.substring(3));
            }
        }
        if(changeTypeE.contains("■")){
            if (changeTypeBuilder.length() == 0) {
                changeTypeBuilder.append(changeTypeE.substring(3));
            }
            else {
                changeTypeBuilder.append(",").append(changeTypeE.substring(3));
            }
        }
        if(changeTypeF.contains("■")){
            if (changeTypeBuilder.length() == 0) {
                changeTypeBuilder.append(changeTypeF.substring(3));
            }
            else {
                changeTypeBuilder.append(",").append(changeTypeF.substring(3));
            }
        }
        if(changeTypeG.contains("■")){
            if (changeTypeBuilder.length() == 0) {
                changeTypeBuilder.append(changeTypeG.substring(3));
            }
            else {
                changeTypeBuilder.append(",").append(changeTypeG.substring(3));
            }
        }
        String changeType = changeTypeBuilder.toString();
        String changeDescription = changeDescriptionCellValue + "\n变更原因类别:"+changeType;
 
        //处理方式,处理方式:
        //□1.报废.  □2. 用完为止  □3. 重工(变)
        //■4.与库存品无关.         □5. 转                      成品使用
        //□6.其它处理方式：
        String solutionCellValue = sheet.getRow(23).getCell(0).getStringCellValue();
        String solution = null;
        if(solutionCellValue.contains("■")){
            String solutionStr = solutionCellValue.split("■")[1];
            solution = solutionStr.split("\\.")[1];
            if(solution.contains("□")){
                solution = solution.split("□")[0];
            }
        }else{
            return new Response().set(1,false,"处理方式必须选中一项!");
        }
 
        //先将共同属性赋值给基础对象
        ecr.setNumber(number);
        ecr.setProposeDate(proposeDate);
        ecr.setProposer(proposer);
        ecr.setChangeDescription(changeDescription);
        ecr.setSolution(solution);
        ecr.setCreateTime(date);
        ecr.setExcelFile(excelHttpUrl);
        ecr.setParentModel(parentModel);
 
        //校验料号和型号必须数量一致
        if(codeSplit.length!=modelSplit.length){
            return new Response().set(1,false,"变更料号与型号对应数量不一致!");
        }
        //赋值给具体对象
        for (int i = 0; i < codeSplit.length; i++) {
            ECR ecrTemp = new ECR();
            BeanUtils.copyProperties(ecr,ecrTemp);
            ecrTemp.setSubCode(CommonUtil.codeAutoFill(codeSplit[i]));
            ecrTemp.setSubModel(modelSplit[i]);
            ecrList.add(ecrTemp);
        }
        //提交之前先校验流水号是否存在
        QueryWrapper<ECR> query = Wrappers.query();
        query.eq("number",ecrList.get(0).getNumber()).last(" limit 1");
        ECR ecrNumberObj = mapper.selectOne(query);
        if(ecrNumberObj!= null) {
            return new Response().set(1,false,"ecr编号已存在");
        }
        //存储操作执行
        multipartFile.transferTo(new File(excelFilePath));
        mapper.insertBatchSomeColumn(ecrList);
 
        return new Response().set(1,true,"导入完成");
    }
 
    public Response ecrImport(ECR ecr) {
        List<ECR> ecrList = new LinkedList<>();
        String subCode = ecr.getSubCode();
        String subModel = ecr.getSubModel();
 
        //校验料号和型号不能为空
        if(subCode == null ||subCode.isEmpty()
        || subModel == null ||subModel.isEmpty()){
            return new Response().set(1,false,"变更料号及型号不能为空!");
        }
 
        String[] subCodeSplit = subCode.split("/");
        String[] subModelSplit = subModel.split("/");
 
        ecr.setCreateTime(new Date());
        //校验料号和型号必须数量一致
        if(subCodeSplit.length!=subModelSplit.length){
            return new Response().set(1,false,"变更料号与型号对应数量不一致!");
        }
        for (int i = 0; i < subCodeSplit.length; i++) {
            ECR ecrTemp = new ECR();
            BeanUtils.copyProperties(ecr,ecrTemp);
            ecrTemp.setSubCode(subCodeSplit[i]);
            ecrTemp.setSubModel(subModelSplit[i]);
            ecrList.add(ecrTemp);
        }
        //提交之前先校验流水号是否存在
        QueryWrapper<ECR> query = Wrappers.query();
        query.eq("number",ecrList.get(0).getNumber()).last(" limit 1");
        ECR ecrNumberObj = mapper.selectOne(query);
        if(ecrNumberObj!= null) {
            return new Response().set(1,false,"ecr编号已存在");
        }
        mapper.insertBatchSomeColumn(ecrList);
        return new Response().set(1,true,"导入完成");
    }
    //分页查询ECR记录
    public Response searchEcr(String number, String subCode, String subModel, Date createTime, Date createTime1, int pageCurr, int pageSize) {
        PageHelper.startPage(pageCurr,pageSize);
        QueryWrapper wrapper=new QueryWrapper();
        if(number!=null){
            wrapper.like("number",number);
        }
        if(subCode!=null){
            wrapper.like("sub_code",subCode);
        }
        if(subModel!=null){
            wrapper.like("sub_model",subModel);
        }
        wrapper.ge("create_time",createTime);
        wrapper.le("create_time",createTime1);
        wrapper.orderByDesc("create_time");
        List list=mapper.selectList(wrapper);
        PageInfo pageInfo=new PageInfo(list);
        return new Response().setII(1,list.size()>0,pageInfo,"查询ECR记录");
    }
 
    //详情种加上ECR信息：根据subCode/subModel查询
    public List searchDetailBysub( String subCode, String subModel) {
        QueryWrapper wrapper=new QueryWrapper();
        wrapper.like("sub_code",subCode);
        wrapper.like("sub_model",subModel);
        wrapper.orderByAsc("number");
        List list=mapper.selectList(wrapper);
        return list;
    }
 
    public void exportExcel(HttpServletResponse response, List<Integer> ids) {
        List<ECR> ecrList;
        if(ids == null) {
            //查询所有
            ecrList = mapper.selectList(null);
        }else{
            //查询ids的记录
            QueryWrapper<ECR> query = Wrappers.query();
            query.in("id",ids);
            ecrList = mapper.selectList(query);
        }
        int size = ecrList.size();
        //ECR编号,申请日期,申请人,变更描述,变更料号,变更型号,变更所属型号,处理方式,创建时间
        String[] title = new String[]{"年份","ECR编号","申请日期","申请人","变更描述","变更料号","变更型号","变更所属型号","处理方式","创建时间"};
        String[][] values = new String[size][]; //size行
        for (int i = 0; i < size; i++) {
            values[i] = new String[10];
            ECR ecr = ecrList.get(i);
            Date proposeDate = ecr.getProposeDate();
            Calendar instance = Calendar.getInstance();
            instance.setTime(proposeDate);
            values[i][0] = String.valueOf(instance.get(Calendar.YEAR));
            values[i][1] = ecr.getNumber();
            values[i][2] = DateUtil.YYYY_MM_DD_UNION.format(proposeDate);
            values[i][3] = ecr.getProposer();
            values[i][4] = ecr.getChangeDescription();
            values[i][5] = ecr.getSubCode();
            values[i][6] = ecr.getSubModel();
            values[i][7] = ecr.getParentModel();
            values[i][8] = ecr.getSolution();
            values[i][9] = DateUtil.YYYY_MM_DD_HH_MM_SS.format(ecr.getCreateTime());
        }
        String dateFormat = DateUtil.YYYY_MM_DD_HH_MM_SS2.format(new Date());
        ExcelUtil.exportExcel("变更记录清单_"+dateFormat,"清单",title,values,null,response);
    }
    //删除ECR记录
    public Response deleteEcr(String number, String filePath) {
        if(!(filePath==null||filePath.isEmpty())){
            String fileDirName = FileDirPath.getFileDirName();
            File file = new File(fileDirName+File.separator+filePath);
            if(!file.isDirectory()){
                file.delete();
            }
        }
        //删除表记录
        UpdateWrapper wrapper=new UpdateWrapper();
        wrapper.eq("number",number);
        int delFlag=mapper.delete(wrapper);
        return new Response().set(1,delFlag>0,"删除ECR");
    }
    //反馈下拉查询ECR记录
    public Response searchEcrInFeedback() {
        QueryWrapper wrapper=new QueryWrapper();
        wrapper.select("distinct number");
        wrapper.orderByDesc("create_time");
        List list=mapper.selectList(wrapper);
        return new Response().setII(1,list.size()>0,list,"查询ECR记录");
    }
}