product/downloadProductHistory 历史产品下载(产品id和版本<下载的版本>)

lxw

2022-08-19 e6ef7e1cc20fbab6f4b70bff815d343950ffada7

product/downloadProductHistory 历史产品下载(产品id和版本<下载的版本>)

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

@@ -1,11 +1,7 @@
package com.whyc.controller;

import com.whyc.dto.Response;
import com.whyc.pojo.ProductHistory;
import com.whyc.service.MaterialService;
import com.whyc.service.ProductBomService;
import com.whyc.service.ProductHistoryService;
import com.whyc.service.ProductService;
import com.whyc.service.*;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.springframework.beans.factory.annotation.Autowired;
@@ -29,6 +25,9 @@

    @Autowired
    private ProductBomService bomService;

    @Autowired
    private ProductBomHistoryService bomHistoryService;

    @Autowired
    private MaterialService meterService;
@@ -55,9 +54,14 @@
    public Response getBomAndMaterial( @RequestParam int productId, @RequestParam int version){
        return bomService.getBomAndMaterial(productId,version);
    }
    @ApiOperation(value = "产品下载(产品id和版本<历史版本待定>)",notes = "8.17修改后使用")
    @ApiOperation(value = "产品下载(产品id和版本<当前最新版本>)",notes = "8.17修改后使用")
    @GetMapping("downloadProduct")
    public void downloadProduct(HttpServletRequest req, HttpServletResponse resp, @RequestParam int productId, @RequestParam int version){
         bomService.downloadProduct(req,resp,productId,version);
    }
    @ApiOperation(value = "历史产品下载(产品id和版本<下载的版本>)",notes = "8.17修改后使用")
    @GetMapping("downloadProductHistory")
    public void downloadProductHistory(HttpServletRequest req, HttpServletResponse resp, @RequestParam int productId, @RequestParam int version){
        bomHistoryService.downloadProductHistory(req,resp,productId,version);
    }
}

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

@@ -7,5 +7,6 @@

public interface ProductBomHistoryMapper extends CustomMapper<ProductBomHistory> {
    void updateVersionBatch(List<ProductBomHistory> newVersionCurrentHistoryList);

    //读取指定版本产品的子件和关联的物料信息
    List<ProductBomHistory> getBomHistoryAndMaterial(int productId, int version);
}

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

@@ -61,6 +61,7 @@
    private String dwgUrl;

    @TableField(exist = false)
    @ApiModelProperty(value = "关联的物料集合")
    private List<Material> materials;



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

@@ -6,6 +6,7 @@
import org.apache.ibatis.type.Alias;

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

/**
 * 产品bom表历史记录
@@ -53,12 +54,8 @@
    private String dwgUrl;

    @TableField(exist = false)
    @ApiModelProperty("替换关系中替换件的原子件名称")
    private String oldSubName;

    @TableField(exist = false)
    @ApiModelProperty("是否是替换件：0：原子件，1：替换件")
    private int replaceStatus;
    @ApiModelProperty(value = "关联的物料集合")
    private List<Material> materials;

    public Integer getId() {
        return id;
@@ -220,19 +217,11 @@
        this.version = version;
    }

    public String getOldSubName() {
        return oldSubName;
    public List<Material> getMaterials() {
        return materials;
    }

    public void setOldSubName(String oldSubName) {
        this.oldSubName = oldSubName;
    }

    public int getReplaceStatus() {
        return replaceStatus;
    }

    public void setReplaceStatus(int replaceStatus) {
        this.replaceStatus = replaceStatus;
    public void setMaterials(List<Material> materials) {
        this.materials = materials;
    }
}

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

@@ -8,8 +8,9 @@
import com.whyc.dto.ZipUtils;
import com.whyc.mapper.MaterialMapper;
import com.whyc.mapper.ProductBomHistoryMapper;
import com.whyc.pojo.DocUser;
import com.whyc.pojo.ProductBomHistory;
import com.whyc.mapper.ProductHistoryMapper;
import com.whyc.mapper.ProductMapper;
import com.whyc.pojo.*;
import com.whyc.util.ActionUtil;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.ClientAnchor;
@@ -22,6 +23,7 @@
import javax.servlet.http.HttpServletResponse;
import java.awt.image.BufferedImage;
import java.io.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@@ -29,6 +31,10 @@
public class ProductBomHistoryService {
    @Autowired(required = false)
    private ProductBomHistoryMapper mapper;

    @Autowired(required = false)
    private ProductHistoryMapper pHistoryMapper;

    @Autowired(required = false)
    private MaterialMapper cponentMapper;

@@ -81,23 +87,28 @@
        }
        return  new Response().setII(1,list.size()>0?true:false,list,"");
    }*/
   /* //下载指定版本的产品
    public void downloaByVersion(HttpServletRequest req, HttpServletResponse resp, String parentModel, int version) {
    //历史产品下载(产品id和版本<下载的版本>)
    public void downloadProductHistory(HttpServletRequest req, HttpServletResponse resp, int productId, int version) {
        //读取产品信息
        QueryWrapper wrapper=new QueryWrapper();
        wrapper.eq("parent_model",parentModel);
        wrapper.le("s_version",version);
        wrapper.ge("e_version",version);
        List<ProductBomHistory> list=mapper.selectList(wrapper);
        //查询出存在替换关系得散装件
        List<ProductBomHistory> replaceBomHis=cponentMapper.getReplaceHisMaterial(parentModel,version);
        //两个集合合并才是最终的BOM
        if(list!=null&&list.size()>0){
            if(replaceBomHis!=null&&replaceBomHis.size()>0){
                replaceBomHis.stream().forEach(replaceBom->{
                    list.add(replaceBom);
        wrapper.eq("id",productId);
        wrapper.eq("version",version);
        wrapper.last("limit 1");
        ProductHistory pHistory=pHistoryMapper.selectOne(wrapper);
        //读取指定版本产品的子件和关联的物料信息
        List<ProductBomHistory> list=mapper.getBomHistoryAndMaterial(productId,version);
        //处理存在关联关系的物料
        List<ProductBomHistory> endList=new ArrayList<>();
        list.stream().forEach(bomHistory -> {
            endList.add(bomHistory);
            if(bomHistory.getMaterials()!=null&&bomHistory.getMaterials().size()>0){
                for (Material m:bomHistory.getMaterials()) {
                    //将物料存放为一个bom为下载做准备
                    ProductBomHistory copyBomHis=copyMaterialToBomHis(m);
                    endList.add(copyBomHis);
                }
            }
                });
            }
        }
        HSSFWorkbook wb = new HSSFWorkbook();
        //字体格式-加粗
        HSSFCellStyle cellStyle = wb.createCellStyle();
@@ -105,21 +116,21 @@
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        cellStyle.setFont(font);
        //生成excel并将dwg文件放在同一报下压缩
        creatBomHsitoryExcel(req,resp,list,wb,version);
        creatBomHsitoryExcel(req,resp,pHistory,list,wb);
        //记录日志
        DocUser docUser= ActionUtil.getUser();
        String operationDetail="具体产品母料型号为:"+parentModel;
        String opreationMsg="执行了最新版产品下载操作";
        String operationDetail="具体产品为:"+pHistory.toString();
        String opreationMsg="执行了"+pHistory.getVersion()+"版本产品的下载操作";
        String terminalIp=req.getRemoteAddr();
        logService.recordOperationLog(docUser.getId(),docUser.getName(), UserOperation.TYPE_DOWNLOAD.getType(),new Date(),terminalIp,opreationMsg,operationDetail);
    }*/
    }
    //根据产品信息创建excel表格并存放在指定目录
    public void creatBomHsitoryExcel(HttpServletRequest req, HttpServletResponse resp,List<ProductBomHistory> list, HSSFWorkbook wb,int version){
    public void creatBomHsitoryExcel(HttpServletRequest req, HttpServletResponse resp,ProductHistory pHistory,List<ProductBomHistory> list, HSSFWorkbook wb){
        String fileDirName = FileDirPath.getFileDirName();
        String rootFace="";
        String excelName="";
        //创建单个sheet
        HSSFSheet sheet = wb.createSheet("bom_"+version+"信息");
        HSSFSheet sheet = wb.createSheet("bom_"+pHistory.getVersion()+"信息");
        sheet.setColumnWidth(1,5000);
        sheet.setColumnWidth(2,5000);
        sheet.setColumnWidth(3,5000);
@@ -149,19 +160,19 @@
        sheet.getRow(rownum).createCell(14).setCellValue("表面处理/物料详情");
        sheet.getRow(rownum).createCell(15).setCellValue("备注");
        sheet.getRow(rownum).createCell(16).setCellValue("图片");
        //将选中的文件存入指定目录下打包下载
        if(list!=null&&list.size()>0){
            for (int i=0;i<list.size();i++) {
                ProductBomHistory bomHistory= (ProductBomHistory) list.get(i);
                String dwgUrl=bomHistory.getDwgUrl();
                if(i==0){
                    //excelName=bomHistory.getParentCode()+"_"+bomHistory.getParentModel()+"_"+version;

        excelName=pHistory.getParentCode()+"_"+pHistory.getParentModel()+"_"+pHistory.getVersion();
                    rootFace=fileDirName+ File.separator+excelName;
                    File destfile = new File(rootFace);
                    if(!destfile.exists()) {
                        destfile.mkdir();
                    }
                }
        //将选中的文件存入指定目录下打包下载
        if(list!=null&&list.size()>0){
            for (int i=0;i<list.size();i++) {
                ProductBomHistory bomHistory= (ProductBomHistory) list.get(i);
                String dwgUrl=bomHistory.getDwgUrl();

                if((dwgUrl!=null)&&(!dwgUrl.isEmpty())){
                    File sourceFile=new File(fileDirName+ File.separator+dwgUrl);
                    bomService.copyFile(sourceFile,rootFace);
@@ -169,16 +180,12 @@
                Row row=sheet.createRow(rownum+i+1);
                //row.setHeight((short)(1500));
                sheet.getRow(rownum+i+1).createCell(1).setCellValue(i+1);
                //sheet.getRow(rownum+i+1).createCell(2).setCellValue(bomHistory.getParentCode());
                //sheet.getRow(rownum+i+1).createCell(3).setCellValue(bomHistory.getParentName());
                //sheet.getRow(rownum+i+1).createCell(4).setCellValue(bomHistory.getParentModel());
                sheet.getRow(rownum+i+1).createCell(2).setCellValue(pHistory.getParentCode());
                sheet.getRow(rownum+i+1).createCell(3).setCellValue(pHistory.getParentName());
                sheet.getRow(rownum+i+1).createCell(4).setCellValue(pHistory.getParentModel());
                sheet.getRow(rownum+i+1).createCell(5).setCellValue(bomHistory.getCategory());
                sheet.getRow(rownum+i+1).createCell(6).setCellValue(bomHistory.getSubCode());
                if(bomHistory.getOldSubName()!=null&&!bomHistory.getOldSubName().isEmpty()){
                    sheet.getRow(rownum+i+1).createCell(7).setCellValue(bomHistory.getSubName()+"("+bomHistory.getOldSubName()+")");
                }else{
                    sheet.getRow(rownum+i+1).createCell(7).setCellValue(bomHistory.getSubName());
                }
                sheet.getRow(rownum+i+1).createCell(8).setCellValue(bomHistory.getSubModel());
                sheet.getRow(rownum+i+1).createCell(9).setCellValue(bomHistory.getUnit()==null?"":bomHistory.getUnit());
                sheet.getRow(rownum+i+1).createCell(10).setCellValue(bomHistory.getQuantity());
@@ -254,4 +261,26 @@
            e.printStackTrace();
        }
    }

    //将物料存放为一个bomHistory为下载做准备
    private ProductBomHistory copyMaterialToBomHis(Material m) {
        ProductBomHistory bomHis=new ProductBomHistory();
        bomHis.setType(m.getType());
        bomHis.setCategory(m.getCategory());
        bomHis.setSubCode(m.getSubCode());
        bomHis.setSubName(m.getSubName());
        bomHis.setSubModel(m.getSubModel());
        bomHis.setUnit(m.getUnit());
        bomHis.setQuantity(m.getQuantity());
        bomHis.setProducer(m.getProducer());
        bomHis.setMaterial(m.getMaterial());
        bomHis.setThickness(m.getThickness());
        bomHis.setSurfaceDetail(m.getSurfaceDetail());
        bomHis.setNotes(m.getNotes());
        bomHis.setPictureUrl(m.getPictureUrl());
        bomHis.setDwgUrl(m.getDwgUrl());
        bomHis.setFileUrl(m.getFileUrl());
        bomHis.setCreateDate(m.getCreateDate());
        return  bomHis;
    }
}

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

@@ -199,6 +199,7 @@
        //读取产品信息
        QueryWrapper wrapper=new QueryWrapper();
        wrapper.eq("id",productId);
        wrapper.eq("version",version);
        wrapper.last("limit 1");
        Product product=productMapper.selectOne(wrapper);
        //读取产品的子件和关联的物料信息
@@ -282,23 +283,22 @@
                    File sourceFile=new File(fileDirName+ File.separator+dwgUrl);
                    copyFile(sourceFile,rootFace);
                }
                rownum=rownum+i+1;
                Row row=sheet.createRow(rownum);
                sheet.getRow(rownum).createCell(1).setCellValue(i+1);
                sheet.getRow(rownum).createCell(2).setCellValue(product.getParentCode());
                sheet.getRow(rownum).createCell(3).setCellValue(product.getParentName());
                sheet.getRow(rownum).createCell(4).setCellValue(product.getParentModel());
                sheet.getRow(rownum).createCell(5).setCellValue(bom.getCategory());
                sheet.getRow(rownum).createCell(6).setCellValue(bom.getSubCode());
                sheet.getRow(rownum).createCell(7).setCellValue(bom.getSubName());
                sheet.getRow(rownum).createCell(8).setCellValue(bom.getSubModel());
                sheet.getRow(rownum).createCell(9).setCellValue(bom.getUnit()==null?"":bom.getUnit());
                sheet.getRow(rownum).createCell(10).setCellValue(bom.getQuantity());
                sheet.getRow(rownum).createCell(11).setCellValue(bom.getProducer()==null?"":bom.getProducer());
                sheet.getRow(rownum).createCell(12).setCellValue(bom.getMaterial());
                sheet.getRow(rownum).createCell(13).setCellValue(bom.getThickness());
                sheet.getRow(rownum).createCell(14).setCellValue(bom.getSurfaceDetail());
                sheet.getRow(rownum).createCell(15).setCellValue(bom.getNotes());
                Row row=sheet.createRow(rownum+i+1);
                sheet.getRow(rownum+i+1).createCell(1).setCellValue(i+1);
                sheet.getRow(rownum+i+1).createCell(2).setCellValue(product.getParentCode());
                sheet.getRow(rownum+i+1).createCell(3).setCellValue(product.getParentName());
                sheet.getRow(rownum+i+1).createCell(4).setCellValue(product.getParentModel());
                sheet.getRow(rownum+i+1).createCell(5).setCellValue(bom.getCategory());
                sheet.getRow(rownum+i+1).createCell(6).setCellValue(bom.getSubCode());
                sheet.getRow(rownum+i+1).createCell(7).setCellValue(bom.getSubName());
                sheet.getRow(rownum+i+1).createCell(8).setCellValue(bom.getSubModel());
                sheet.getRow(rownum+i+1).createCell(9).setCellValue(bom.getUnit()==null?"":bom.getUnit());
                sheet.getRow(rownum+i+1).createCell(10).setCellValue(bom.getQuantity());
                sheet.getRow(rownum+i+1).createCell(11).setCellValue(bom.getProducer()==null?"":bom.getProducer());
                sheet.getRow(rownum+i+1).createCell(12).setCellValue(bom.getMaterial());
                sheet.getRow(rownum+i+1).createCell(13).setCellValue(bom.getThickness());
                sheet.getRow(rownum+i+1).createCell(14).setCellValue(bom.getSurfaceDetail());
                sheet.getRow(rownum+i+1).createCell(15).setCellValue(bom.getNotes());
                if((bom.getPictureUrl()!=null)&&(!bom.getPictureUrl().isEmpty())){
                    ByteArrayOutputStream byteArrayOut = null;
                    try {
@@ -306,7 +306,7 @@
                        BufferedImage bufferImg = ImageIO.read(new FileInputStream(new File(fileDirName+File.separator+bom.getPictureUrl())));
                        ImageIO.write(bufferImg, "png", byteArrayOut);
                        //anchor主要用于设置图片的属性
                        HSSFClientAnchor anchor = new HSSFClientAnchor(50, 20, 1000, 230,(short) 16, rownum, (short) 16, rownum);
                        HSSFClientAnchor anchor = new HSSFClientAnchor(50, 20, 1000, 230,(short) 16, rownum, (short) 16, rownum+i+1);
                        anchor.setAnchorType(ClientAnchor.MOVE_AND_RESIZE);
                        //插入图片
                        patriarch.createPicture(anchor, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
@@ -372,8 +372,8 @@
        List<ProductBom> list=mapper.getAllSubWithOutMaterial(MaterialId);
        return new Response().setII(1,list.size()>0?true:false,list,"返回数据");
    }
    /*//根据母料型号查询子件信息及有最新版本关联的散装件信息
    public Response getSubByMaterialProduct(String parentModel) {
   //根据母料型号查询子件信息及有最新版本关联的散装件信息
     /*public Response getSubByMaterialProduct(String parentModel) {
        List<ProductBom> list=mapper.getSubByMaterialProduct(parentModel);
        //查询出存在替换关系得散装件
        List<ProductBom> replaceBoms=cponentMapper.getReplaceMaterial(parentModel);

 src/main/resources/mapper/ProductBomHistoryMapper.xml

@@ -9,4 +9,45 @@
        </foreach>
    </update>

    <resultMap id="MaterialList" type="productBomHistory">
        <id property="id" column="id"></id>
        <result property="type" column="type"></result>
        <result property="category" column="category"></result>
        <result property="subCode" column="sub_code"></result>
        <result property="subName" column="sub_name"></result>
        <result property="subModel" column="sub_model"></result>
        <result property="unit" column="unit"></result>
        <result property="quantity" column="quantity"></result>
        <result property="producer" column="producer"></result>
        <result property="material" column="material"></result>
        <result property="thickness" column="thickness"></result>
        <result property="surfaceDetail" column="surface_detail"></result>
        <result property="notes" column="notes"></result>
        <result property="pictureUrl" column="picture_url"></result>
        <result property="fileUrl" column="file_url"></result>
        <result property="upUserId" column="up_user_id"></result>
        <result property="createDate" column="create_date"></result>
        <result property="version" column="version"></result>
        <result property="dwgUrl" column="dwg_url"></result>
        <collection property="materials" javaType="java.util.ArrayList" ofType="com.whyc.pojo.Material" column="{productId=product_id,subCode=sub_code,version=version}" select="selectMaterial">
        </collection>
    </resultMap>
    <select id="getBomHistoryAndMaterial" resultType="com.whyc.pojo.ProductBomHistory">
        select distinct *  from  db_doc.tb_product_bom_history
        <where>
            product_id = #{productId}
            and
            version = #{version}
        </where>
        order by id asc
    </select>
    <select id="selectMaterial"  resultType="com.whyc.pojo.Material">
        select DISTINCT tb_Material.*  FROM db_doc.tb_Material,db_doc.tb_Material_product_history
        where tb_Material.id=tb_Material_product_history.Material_id
        and tb_Material_product_history.product_id=#{productId}
          and tb_Material_product_history.sub_code=#{subCode}
        and tb_Material_product_history.version=#{version}
    </select>


</mapper>