package com.whyc.service;
|
|
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
|
import com.github.pagehelper.PageHelper;
|
import com.github.pagehelper.PageInfo;
|
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"));
|
multipartFile.transferTo(new File(excelFilePath));
|
|
Workbook workbook = null;
|
workbook = WorkbookFactory.create(inputStream);
|
inputStream.close();
|
//解析excel,获取相关数据
|
Sheet sheet = workbook.getSheetAt(0);
|
List<ECR> ecrList = new LinkedList<>();
|
ECR ecr = new ECR();
|
String numberCellValue = sheet.getRow(1).getCell(5).getStringCellValue();
|
String number = numberCellValue.substring(numberCellValue.indexOf("BG"));
|
|
Cell codeCell = sheet.getRow(2).getCell(0);
|
if(codeCell == null){
|
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){
|
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("/");
|
}
|
|
String proposeDateCellValue = sheet.getRow(2).getCell(14).getStringCellValue();
|
//申请日期：xxx
|
String proposeDateStr = proposeDateCellValue.substring(5).trim();
|
Date proposeDate = DateUtil.YYYY_MM_DD_UNION.parse(proposeDateStr);
|
|
String proposerCellValue = sheet.getRow(2).getCell(22).getStringCellValue();
|
//申请人：xxx
|
String proposer = proposerCellValue.substring(4).trim();
|
|
String parentModel = sheet.getRow(3).getCell(0).getStringCellValue().substring(10).trim();
|
|
String changeDescriptionCellValue = sheet.getRow(4).getCell(0).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];
|
}
|
|
//先将共同属性赋值给基础对象
|
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);
|
}
|
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);
|
}
|
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.orderByAsc("number");
|
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<ECR> ecrList = mapper.selectList(null);
|
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_SS.format(new Date());
|
ExcelUtil.exportExcel("变更记录清单_"+dateFormat,"清单",title,values,null,response);
|
}
|
}
|
