<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//oos.juyouhx.com//DTD Mapper 3.0//EN" "http://oss.juyouhx.com/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.scpyun.platform.standard.jilinsscgsdp.keyDmBorrowDetail" name="领用申请明细">

    <insert id="insertDetail" parameterType="map">
        INSERT INTO jl_key_dm_borrow_application_detail(
            id,application_id,material_id,material_code,material_name,apply_quantity,unit,issue_remark,returned_quantity,damaged_quantity,is_used,create_by,create_time
        ) VALUES (
            UUID(),#{application_id},#{material_id},#{material_code},#{material_name},#{apply_quantity},#{unit},#{issue_remark},COALESCE(#{returned_quantity},0),COALESCE(#{damaged_quantity},0),1,#{_user.id},NOW()
        )
    </insert>

    <select id="selectDetailsByApplicationId" parameterType="map" resultType="map">
        SELECT bd.*,m.category_id,(COALESCE(i.total_quantity,0) - COALESCE(i.borrowed_quantity) - COALESCE(i.damaged_quantity)) as available_quantity
        FROM jl_key_dm_borrow_application_detail as bd
        left join jl_key_dm_material as m
        on m.id = bd.material_id
        left join jl_key_dm_inventory as i
        on bd.material_id = i.material_id
        WHERE bd.application_id = #{id} AND bd.is_used = 1 ORDER BY bd.create_time ASC
    </select>

</mapper>


