material/getMaterialLimit 查询所有物料(分页，模糊查询) 修改带上关联关系和包含物料编码的产品

lxw

2022-08-19 d88e0057c906d3aa2cef697cc96041c8d750a8da

material/getMaterialLimit  查询所有物料(分页，模糊查询)
修改带上关联关系和包含物料编码的产品

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

@@ -17,25 +17,26 @@
    @Autowired
    private MaterialService service;

    @ApiOperation(value = "查询所有的散装件信息分页（带上关联的子件信息，status状态不做处理)）")
    /*@ApiOperation(value = "查询所有的散装件信息分页（带上关联的子件信息，status状态不做处理)）")
    @GetMapping("getAllMaterial")
    public Response getAllMaterial(@RequestParam(required = false) String category,@RequestParam(required = false) String subCode,@RequestParam(required = false) String subName, @RequestParam(required = false) String subModel,@RequestParam int pageCurr, @RequestParam int pageSize){
        return service.getAllMaterial(category,subCode,subName,subModel,pageCurr,pageSize);
    }
    @ApiOperation(tags = "产品管理",value = "查询所有的散装件信息不分页(不包含被最新版本关联的散装件，status=1可用)")
    }*/
   /* @ApiOperation(tags = "产品管理",value = "查询所有的散装件信息不分页(不包含被最新版本关联的散装件，status=1可用)")
    @GetMapping("getMaterialWithoutSub")
    public Response getMaterialWithoutSub(@RequestParam String parentModel, @RequestParam String subName, @RequestParam int version){
        return service.getMaterialWithoutSub(parentModel,subName,version);
    }
    @ApiOperation(value = "删除散装件（将散装件标识为不可用）")
    }*/
    /*@ApiOperation(value = "删除散装件（将散装件标识为不可用）")
    @GetMapping("deleteMaterial")
    public Response deleteMaterial(@RequestParam int id){
        return service.deleteMaterial(id);
    }
    }*/

    @ApiOperation(value = "查询所有物料(分页，模糊查询)",notes = "8.17修改后使用")
    @GetMapping("getMaterialLimit")
    public Response getMaterialLimit(@RequestParam(required = false) String subCode,@RequestParam(required = false) String subName, @RequestParam(required = false) String subModel,@RequestParam int pageCurr, @RequestParam int pageSize){
        return service.getMaterialLimit(subCode,subName,subModel,pageCurr,pageSize);
    }

}

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

@@ -25,16 +25,17 @@
    public Response getBomHistoryByPModelAndSName(@RequestParam String pmodel, @RequestParam String sname){
        return service.getBomHistoryByPModelAndSName(pmodel,sname);
    }

   /*
    @ApiOperation(tags="产品管理",value = "查询具体某一版本产品信息")
    @GetMapping("getBoomByVersion")
    public Response getBoomByVersion(@RequestParam String parentModel,@RequestParam int version){
        return service.getBoomByVersion(parentModel,version);
    }
    }*/

    /*
    @ApiOperation(tags="产品管理",value = "下载指定版本的产品")
    @GetMapping("downloaByVersion")
    public void downloaByVersion(HttpServletRequest req, HttpServletResponse resp, @RequestParam String parentModel, @RequestParam int version){
        service.downloaByVersion(req,resp,parentModel,version);
    }
    }*/
}

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

@@ -55,7 +55,7 @@
    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);

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

@@ -8,11 +8,13 @@

public interface MaterialMapper extends CustomMapper<Material>{
    //查询所有的散装件信息不分页
    List<Material> getMaterialWithoutSub(String parentModel, String subName,int version);
   /* List<Material> getMaterialWithoutSub(String parentModel, String subName,int version);*/
    //查询所有的散装件信息加设置了替换关系的子件信息
    List<Material> getAllMaterial(String category,String subCode,String subName,String subModel);
    /*List<Material> getAllMaterial(String category,String subCode,String subName,String subModel);*/
    /*//查询出存在替换关系得散装件
    List<ProductBom> getReplaceMaterial(String parentModel);*/
    //历史版本下载查询出存在替换关系得散装件
    List<ProductBomHistory> getReplaceHisMaterial(String parentModel,int version);
    /*//历史版本下载查询出存在替换关系得散装件
    List<ProductBomHistory> getReplaceHisMaterial(String parentModel,int version);*/
    //查询所有物料(分页，模糊查询)
    List<Material> getMaterialLimit(String subCode, String subName, String subModel);
}

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

@@ -55,7 +55,13 @@
    private String dwgUrl;

    @TableField(exist = false)
    private List<MaterialProductHistory> mProductHistorys;
    @ApiModelProperty(value = "物料与产品的关联关系")
    private List<MaterialProductHistory> mproductHistorys;

    @TableField(exist = false)
    @ApiModelProperty(value = "包含物料编码的产品")
    private List<Product> products;


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

    public List<MaterialProductHistory> getMProductHistorys() {
        return mProductHistorys;
    public List<MaterialProductHistory> getMproductHistorys() {
        return mproductHistorys;
    }

    public void setMProductHistorys(List<MaterialProductHistory> mProductHistorys) {
        this.mProductHistorys = mProductHistorys;
    public void setMproductHistorys(List<MaterialProductHistory> mproductHistorys) {
        this.mproductHistorys = mproductHistorys;
    }

    public List<Product> getProducts() {
        return products;
    }

    public void setProducts(List<Product> products) {
        this.products = products;
    }
}

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

@@ -1,7 +1,6 @@
package com.whyc.service;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.conditions.update.UpdateWrapper;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.whyc.dto.Response;
@@ -17,28 +16,28 @@
    @Autowired(required = false)
    private MaterialMapper mapper;
    //查询所有的散装件信息加设置了替换关系的子件信息
    public Response getAllMaterial(String category,String subCode,String subName,String subModel,int pageCurr,int pageSize) {
    /*public Response getAllMaterial(String category,String subCode,String subName,String subModel,int pageCurr,int pageSize) {
        PageHelper.startPage(pageCurr,pageSize);
        /*QueryWrapper wrapper=new QueryWrapper();
        *//*QueryWrapper wrapper=new QueryWrapper();
        wrapper.orderByAsc("id");
        List<Material> list=mapper.selectList(wrapper);*/
        List<Material> list=mapper.selectList(wrapper);*//*
        List<Material> list=mapper.getAllMaterial(category,subCode,subName,subModel);
        PageInfo pageInfo=new PageInfo(list);
        return new Response().setII(1,list.size()>0?true:false,pageInfo,"返回所有的散装件");
    }
    }*/
    //查询所有的散装件信息不分页
    public Response getMaterialWithoutSub(String parentModel, String subName,int version) {
    /*public Response getMaterialWithoutSub(String parentModel, String subName,int version) {
        List<Material> list=mapper.getMaterialWithoutSub(parentModel,subName,version);
        return new Response().setII(1,list.size()>0?true:false,list,"返回子件没有添加过联系的散装件");
    }
    }*/
    //删除散装件（将散装件标识为不可用）
    public Response deleteMaterial(int id) {
    /*public Response deleteMaterial(int id) {
        UpdateWrapper wrapper=new UpdateWrapper();
        wrapper.set("status",0);
        wrapper.eq("id",id);
        int bl=mapper.update(null,wrapper);
        return new Response().setII(1,bl>0?true:false,bl,"将散装件标识为不可用");
    }
    }*/

    public List<Material> getList() {
        return mapper.selectList(null);
@@ -56,17 +55,7 @@
    //查询所有物料(分页，模糊查询)
    public Response getMaterialLimit(String subCode, String subName, String subModel, int pageCurr, int pageSize) {
        PageHelper.startPage(pageCurr,pageSize);
        QueryWrapper wrapper=new QueryWrapper();
        if(subCode!=null&&!subCode.isEmpty()){
            wrapper.like("sub_code",subCode);
        }
        if(subName!=null&&!subName.isEmpty()){
            wrapper.like("sub_name",subName);
        }
        if(subModel!=null&&!subModel.isEmpty()){
            wrapper.like("sub_model",subModel);
        }
        List<Material> list=mapper.selectList(wrapper);
        List<Material> list=mapper.getMaterialLimit(subCode,subName,subModel);
        PageInfo pageInfo=new PageInfo(list);
        return new Response().setII(1,list.size()>0?true:false,pageInfo,"查询所有物料(分页，模糊查询)");
    }

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

@@ -61,7 +61,7 @@
    public void updateVersionBatch(List<ProductBomHistory> newVersionCurrentHistoryList) {
        mapper.updateVersionBatch(newVersionCurrentHistoryList);
    }
    //查询具体某一版本产品信息
  /*  //查询具体某一版本产品信息
    public Response getBoomByVersion(String parentModel, int version) {
        QueryWrapper wrapper=new QueryWrapper();
        wrapper.eq("parent_model",parentModel);
@@ -80,8 +80,8 @@
            }
        }
        return  new Response().setII(1,list.size()>0?true:false,list,"");
    }
    //下载指定版本的产品
    }*/
   /* //下载指定版本的产品
    public void downloaByVersion(HttpServletRequest req, HttpServletResponse resp, String parentModel, int version) {
        QueryWrapper wrapper=new QueryWrapper();
        wrapper.eq("parent_model",parentModel);
@@ -112,7 +112,7 @@
        String opreationMsg="执行了最新版产品下载操作";
        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){
        String fileDirName = FileDirPath.getFileDirName();

 src/main/resources/mapper/MaterialMapper.xml

@@ -3,16 +3,16 @@
<mapper namespace="com.whyc.mapper.MaterialMapper">


    <select id="getMaterialWithoutSub" resultType="com.whyc.pojo.Material">
    <!--<select id="getMaterialWithoutSub" resultType="com.whyc.pojo.Material">
        select * from  db_doc.tb_Material  b
        where (select count(1) as num from  db_doc.tb_Material_product_history  c
               where b.id= c.Material_id   and c.sub_name=#{subName} and parent_model=#{parentModel}
               and c.e_version=#{version}
             ) = 0
        and b.status=1;
    </select>
    </select>-->

    <resultMap id="productBomList" type="Material">
    <!--<resultMap id="productBomList" type="Material">
        <id property="id" column="id"></id>
        <result property="type" column="type"></result>
        <result property="category" column="category"></result>
@@ -32,11 +32,11 @@
        <result property="createDate" column="create_date"></result>
        <result property="updateDate" column="update_date"></result>
        <result property="dwgUrl" column="dwg_url"></result>
        <collection property="mProductHistorys" javaType="java.util.ArrayList" ofType="com.whyc.pojo.MaterialProductHistory" column="{id=id}" select="selectMaterialProductHistory">
        <collection property="mproductHistorys" javaType="java.util.ArrayList" ofType="com.whyc.pojo.MaterialProductHistory" column="{id=id}" select="selectMaterialProductHistory">
        </collection>
    </resultMap>

    <select id="getAllMaterial" resultMap="productBomList">
-->
    <!--<select id="getAllMaterial" resultMap="productBomList">
        select distinct * from  db_doc.tb_Material
        <where>
        1=1
@@ -54,19 +54,19 @@
            </if>
        </where>
        order by id asc
    </select>
    <select id="selectMaterialProductHistory"  resultType="com.whyc.pojo.MaterialProductHistory">
    </select>-->
    <!--<select id="selectMaterialProductHistory"  resultType="com.whyc.pojo.MaterialProductHistory">
        select DISTINCT tb_Material_product_history.*  FROM db_doc.tb_product_bom,db_doc.tb_Material_product_history
        where  tb_product_bom.version=tb_Material_product_history.e_version
        and tb_product_bom.parent_model=tb_Material_product_history.parent_model and tb_Material_product_history.Material_id=#{id}
    </select>
    </select>-->


    <resultMap id="replaceBom" type="productBom">
       <!-- <result property="parentCode" column="parent_code"></result>
    <!--<resultMap id="replaceBom" type="productBom">
       &lt;!&ndash; <result property="parentCode" column="parent_code"></result>
        <result property="parentModel" column="parent_model"></result>
        <result property="parentName" column="parent_name"></result>-->
        <result property="oldSubName" column="old_subName"></result>
        <result property="parentName" column="parent_name"></result>
        <result property="oldSubName" column="old_subName"></result>&ndash;&gt;
        <result property="type" column="type"></result>
        <result property="category" column="category"></result>
        <result property="subCode" column="sub_code"></result>
@@ -83,11 +83,11 @@
        <result property="fileUrl" column="file_url"></result>
        <result property="upUserId" column="up_user_id"></result>
        <result property="createDate" column="create_date"></result>
        <!--<result property="updateDate" column="update_date"></result>-->
        &lt;!&ndash;<result property="updateDate" column="update_date"></result>&ndash;&gt;
        <result property="dwgUrl" column="dwg_url"></result>
        <result property="version" column="version"></result>
        <!--<result property="parentVersion" column="parent_version"></result>-->
    </resultMap>
        &lt;!&ndash;<result property="parentVersion" column="parent_version"></result>&ndash;&gt;
    </resultMap>-->
    <!--<select id="getReplaceMaterial" resultMap="replaceBom">
        select distinct tb_Material.*,tb_Material_product_history.sub_name as old_subName
             ,tb_product_bom.parent_model,tb_product_bom.parent_code,tb_product_bom.parent_name
@@ -99,10 +99,10 @@
        and tb_Material_product_history.parent_model=#{parentModel}
        and tb_Material_product_history.link_type=2
    </select>-->
    <resultMap id="replaceHisBom" type="productBomHistory">
        <!--<result property="parentCode" column="parent_code"></result>
   <!-- <resultMap id="replaceHisBom" type="productBomHistory">
        &lt;!&ndash;<result property="parentCode" column="parent_code"></result>
        <result property="parentModel" column="parent_model"></result>
        <result property="parentName" column="parent_name"></result>-->
        <result property="parentName" column="parent_name"></result>&ndash;&gt;
        <result property="oldSubName" column="old_subName"></result>
        <result property="type" column="type"></result>
        <result property="category" column="category"></result>
@@ -121,9 +121,9 @@
        <result property="upUserId" column="up_user_id"></result>
        <result property="createDate" column="create_date"></result>
        <result property="dwgUrl" column="dwg_url"></result>
       <!-- <result property="parentVersion" column="parent_version"></result>-->
    </resultMap>
    <select id="getReplaceHisMaterial" resultMap="replaceHisBom">
       &lt;!&ndash; <result property="parentVersion" column="parent_version"></result>&ndash;&gt;
    </resultMap>-->
    <!--<select id="getReplaceHisMaterial" resultMap="replaceHisBom">
        select distinct tb_Material.*,tb_Material_product_history.sub_name as old_subName
                      ,tb_product_bom.parent_model,tb_product_bom.parent_code,tb_product_bom.parent_name
        from db_doc.tb_Material,db_doc.tb_Material_product_history,tb_product_bom
@@ -133,5 +133,59 @@
          and tb_Material_product_history.link_type=2
          and tb_Material_product_history.s_version&lt;=#{version}
          and tb_Material_product_history.e_version>=#{version}
    </select>-->
    <resultMap id="materialList" type="Material">
        <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="updateDate" column="update_date"></result>
        <result property="dwgUrl" column="dwg_url"></result>
        <collection property="mproductHistorys" javaType="java.util.ArrayList" ofType="com.whyc.pojo.MaterialProductHistory" column="{id=id}" select="selectMaterialProductHistory">
        </collection>
        <collection property="products" javaType="java.util.ArrayList" ofType="com.whyc.pojo.Product" column="{subCode=sub_code}" select="selectMaterialInProduct">
        </collection>
    </resultMap>

    <select id="getMaterialLimit" resultMap="materialList">
         select * from tb_Material
         <where>
             1=1
             <if test="subCode!=null&amp;&amp;subCode!=''">
                 and sub_code like '%${subCode}%'
             </if>
             <if test="subName!=null&amp;&amp;subName!=''">
                 and sub_name like '%${subName}%'
             </if>
             <if test="subModel!=null&amp;&amp;subModel!=''">
                 and sub_name like '%${subModel}%'
             </if>
         </where>
         order by id asc
    </select>
    <select id="selectMaterialProductHistory"  resultType="com.whyc.pojo.MaterialProductHistory">
        select DISTINCT tb_Material_product_history.*  FROM db_doc.tb_material_product_history,db_doc.tb_product
        where  tb_product.version=tb_Material_product_history.version
          and tb_product.id=tb_Material_product_history.product_id
          and tb_Material_product_history.material_id=#{id}
    </select>
    <select id="selectMaterialInProduct"  resultType="com.whyc.pojo.Product">
        select DISTINCT tb_product.*  FROM db_doc.tb_material,db_doc.tb_product, db_doc.tb_product_bom
        where  tb_product.id=tb_product_bom.product_id
          and tb_material.sub_code=tb_product_bom.sub_code
          and tb_material.sub_code=#{subCode}
    </select>
</mapper>