<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.hw.mes.mapper.MesMaterialBomMapper">

    <resultMap type="MesMaterialBom" id="MesMaterialBomResult">
        <result property="materialBomId" column="material_bom_id"/>
        <result property="parentId" column="parent_id"/>
        <result property="ancestors" column="ancestors"/>
        <result property="materialId" column="material_id"/>
        <result property="materialName" column="material_name"/>
        <result property="standardAmount" column="standard_amount"/>
        <result property="topFlag" column="top_flag"/>
        <result property="checkType" column="check_type"/>
        <result property="projectId" column="project_id"/>
        <result property="activeFlag" column="active_flag"/>
        <result property="remark" column="remark"/>
        <result property="createBy" column="create_by"/>
        <result property="createTime" column="create_time"/>
        <result property="updateBy" column="update_by"/>
        <result property="updateTime" column="update_time"/>
        <result property="materialCode" column="material_code"/>
        <result property="materialBomDesc" column="material_bom_desc"/>
        <result property="materialNameDesc" column="materialNameDesc"/>
        <result property="assembleTime" column="assemble_time"/>
    </resultMap>

    <sql id="selectMesMaterialBomVo">
        select material_bom_id,
               parent_id,
               ancestors,
               material_id,
               material_name,
               material_bom_desc,
               standard_amount,
               top_flag,
               check_type,
               project_id,
               active_flag,
               remark,
               create_by,
               create_time,
               update_by,
               update_time,
               assemble_time,
               CONCAT(material_name, '-', material_bom_desc) materialNameDesc
        from mes_material_bom
    </sql>

    <select id="selectMesMaterialBomList" parameterType="MesMaterialBom" resultMap="MesMaterialBomResult">
        <include refid="selectMesMaterialBomVo"/>
        <where>
            <if test="parentId != null ">and parent_id = #{parentId}</if>
            <if test="ancestors != null  and ancestors != ''">and ancestors like concat('%', #{ancestors},'%')</if>
            <if test="materialId != null ">and material_id = #{materialId}</if>
            <if test="materialName != null  and materialName != ''">and material_name like concat('%', #{materialName},
                '%')
            </if>
            <if test="standardAmount != null ">and standard_amount = #{standardAmount}</if>
            <if test="materialBomDesc != null ">and material_bom_desc = #{materialBomDesc}</if>
            <if test="topFlag != null ">and top_flag = #{topFlag}</if>
            <if test="checkType != null  and checkType != ''">and check_type = #{checkType}</if>
            <if test="projectId != null ">and project_id = #{projectId}</if>
            <if test="activeFlag != null  and activeFlag != ''">and active_flag = #{activeFlag}</if>
        </where>
        order by material_id, create_time desc
    </select>

    <select id="selectMesMaterialBomByMaterialBomId" parameterType="Long" resultMap="MesMaterialBomResult">
        <include refid="selectMesMaterialBomVo"/>
        where material_bom_id = #{materialBomId}
    </select>

    <insert id="insertMesMaterialBom" parameterType="MesMaterialBom" useGeneratedKeys="true"
            keyProperty="materialBomId">
        insert into mes_material_bom
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="parentId != null">parent_id,</if>
            <if test="ancestors != null">ancestors,</if>
            <if test="materialId != null">material_id,</if>
            <if test="materialName != null">material_name,</if>
            <if test="materialBomDesc != null">material_bom_desc,</if>
            <if test="standardAmount != null">standard_amount,</if>
            <if test="topFlag != null">top_flag,</if>
            <if test="checkType != null">check_type,</if>
            <if test="projectId != null">project_id,</if>
            <if test="activeFlag != null and activeFlag != ''">active_flag,</if>
            <if test="remark != null">remark,</if>
            <if test="createBy != null">create_by,</if>
            <if test="createTime != null">create_time,</if>
            <if test="updateBy != null">update_by,</if>
            <if test="updateTime != null">update_time,</if>
            <if test="assembleTime != null">assemble_time,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="parentId != null">#{parentId},</if>
            <if test="ancestors != null">#{ancestors},</if>
            <if test="materialId != null">#{materialId},</if>
            <if test="materialName != null">#{materialName},</if>
            <if test="materialBomDesc != null">#{materialBomDesc},</if>
            <if test="standardAmount != null">#{standardAmount},</if>
            <if test="topFlag != null">#{topFlag},</if>
            <if test="checkType != null">#{checkType},</if>
            <if test="projectId != null">#{projectId},</if>
            <if test="activeFlag != null and activeFlag != ''">#{activeFlag},</if>
            <if test="remark != null">#{remark},</if>
            <if test="createBy != null">#{createBy},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="updateBy != null">#{updateBy},</if>
            <if test="updateTime != null">#{updateTime},</if>
            <if test="assembleTime != null">#{assembleTime},</if>
        </trim>
    </insert>

    <update id="updateMesMaterialBom" parameterType="MesMaterialBom">
        update mes_material_bom
        <trim prefix="SET" suffixOverrides=",">
            <if test="parentId != null">parent_id = #{parentId},</if>
            <if test="ancestors != null">ancestors = #{ancestors},</if>
            <if test="materialId != null">material_id = #{materialId},</if>
            <if test="materialName != null">material_name = #{materialName},</if>
            <if test="materialBomDesc != null">material_bom_desc = #{materialBomDesc},</if>
            <if test="standardAmount != null">standard_amount = #{standardAmount},</if>
            <if test="topFlag != null">top_flag = #{topFlag},</if>
            <if test="checkType != null">check_type = #{checkType},</if>
            <if test="projectId != null">project_id = #{projectId},</if>
            <if test="activeFlag != null and activeFlag != ''">active_flag = #{activeFlag},</if>
            <if test="remark != null">remark = #{remark},</if>
            <if test="createBy != null">create_by = #{createBy},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="updateBy != null">update_by = #{updateBy},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
            <if test="assembleTime != null">assemble_time = #{assembleTime},</if>
        </trim>
        where material_bom_id = #{materialBomId}
    </update>

    <delete id="deleteMesMaterialBomByMaterialBomId" parameterType="Long">
        delete
        from mes_material_bom
        where material_bom_id = #{materialBomId}
    </delete>

    <delete id="deleteMesMaterialBomByMaterialBomIds" parameterType="String">
        delete from mes_material_bom where material_bom_id in
        <foreach item="materialBomId" collection="array" open="(" separator="," close=")">
            #{materialBomId}
        </foreach>
    </delete>


    <select id="selectMesMaterialBomJoinList" parameterType="MesMaterialBom" resultMap="MesMaterialBomResult">
        select mmb.material_id,mbmi.material_code,mbmi.material_name,mmb.standard_amount
        from mes_material_bom mmb left join mes_base_material_info mbmi on mmb.material_id=mbmi.material_id
        <where>
            <if test="parentId != null ">and mmb.parent_id = #{parentId}</if>
            <if test="ancestors != null  and ancestors != ''">and mmb.ancestors like concat(#{ancestors}, '%')</if>
            <if test="materialId != null ">and mmb.material_id = #{materialId}</if>
            <if test="materialName != null  and materialName != ''">and mbmi.material_name like concat('%',
                #{materialName}, '%')
            </if>
            <if test="standardAmount != null ">and mmb.standard_amount = #{standardAmount}</if>
            <if test="topFlag != null ">and mmb.top_flag = #{topFlag}</if>
            <if test="checkType != null  and checkType != ''">and mmb.check_type = #{checkType}</if>
            <if test="projectId != null ">and mmb.project_id = #{projectId}</if>
            <if test="activeFlag != null  and activeFlag != ''">and mmb.active_flag = #{activeFlag}</if>
        </where>
    </select>


</mapper>
