根据产品id和版本查询子件及其关联的物料信息

lxw

2022-08-27 22c1d9b490098749ed799161075fc4ac24e56c77

根据产品id和版本查询子件及其关联的物料信息

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

@@ -64,6 +64,13 @@
    @ApiModelProperty(value = "关联的物料集合")
    private List<Material> materials;

    @TableField(exist = false)
    @ApiModelProperty(value = "母料编号")
    private String parentCode;

    @TableField(exist = false)
    @ApiModelProperty(value = "定制单号")
    private String customCode;

    public Integer getId() {
        return id;
@@ -241,4 +248,19 @@
        this.materials = materials;
    }

    public String getParentCode() {
        return parentCode;
    }

    public void setParentCode(String parentCode) {
        this.parentCode = parentCode;
    }

    public String getCustomCode() {
        return customCode;
    }

    public void setCustomCode(String customCode) {
        this.customCode = customCode;
    }
}

 src/main/resources/mapper/ProductBomMapper.xml

@@ -133,23 +133,31 @@
        <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">
        <result property="parentCode" column="parent_code"></result>
        <result property="customCode" column="custom_code"></result>
        <collection property="materials" javaType="java.util.ArrayList" ofType="com.whyc.pojo.Material" column="{parentCode=parent_code,customCode=custom_code,subCode=sub_code,version=version}" select="selectMaterial">
        </collection>
    </resultMap>
    <select id="getBomAndMaterial" resultMap="MaterialList" parameterType="java.lang.Integer">
        select distinct *  from  db_doc.tb_product_bom
        select distinct tb_product_bom.*,tb_product.parent_code,tb_product.custom_code  from  db_doc.tb_product_bom,db_doc.tb_product
        <where>
            product_id = #{productId}
            tb_product_bom.product_id = tb_product.id
            and
            version = #{version}
            tb_product_bom.version = tb_product.version
            and
            tb_product.id = #{productId}
            and
            tb_product.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.parent_code=#{parentCode}
        and tb_material_product_history.custom_code=#{customCode}
        and tb_material_product_history.sub_code=#{subCode}
        and tb_material_product_history.version=#{version}
        and tb_material_product_history.s_version&lt;=#{version}
        and tb_material_product_history.e_version>=#{version}
    </select>
</mapper>