物料查询

lxw

2022-08-26 8acbad7f6074f48b22c703736dfe62c7a24cbff7

物料查询

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

@@ -10,6 +10,7 @@

import java.io.Serializable;
import java.util.Date;
import java.util.List;

/**
 * <p>
@@ -45,5 +46,7 @@

    private Integer version;

    @TableField(exist = false)
    private List materialIds;

}

 src/main/resources/mapper/MaterialMapper.xml

@@ -188,10 +188,24 @@
          and tb_product.custom_code=tb_material_product_history.custom_code
          and tb_material_product_history.sub_code=#{subCode}
    </select>
    <select id="selectMaterialInProduct"  resultType="com.whyc.pojo.Product">
        select DISTINCT tb_product.*  FROM db_doc.tb_material_product_history,db_doc.tb_product, db_doc.tb_product_bom
    <resultMap id="productIds" type="product">
        <id property="id" column="id"></id>
        <result property="parentCode" column="parent_code"></result>
        <result property="parentModel" column="parent_model"></result>
        <result property="notes" column="notes"></result>
        <result property="customCode" column="custom_code"></result>
        <result property="createTime" column="create_time"></result>
        <result property="version" column="version"></result>
        <collection property="materialIds" ofType="java.lang.Integer" >
            <result property="materialId" column="material_id"></result>
        </collection>
    </resultMap>
    <select id="selectMaterialInProduct"  resultMap="productIds">
        select DISTINCT tb_product.*,tb_material_product_history.material_id  FROM db_doc.tb_material_product_history,db_doc.tb_product, db_doc.tb_product_bom
        where  tb_product.id=tb_product_bom.product_id
          and tb_material_product_history.sub_code=tb_product_bom.sub_code
          and tb_material_product_history.parent_code=tb_product.parent_code
          and tb_material_product_history.custom_code=tb_product.custom_code
          and tb_product.version>=tb_material_product_history.s_version and tb_product.version&lt;=tb_material_product_history.e_version
          and tb_material_product_history.sub_code=#{subCode}
    </select>