<?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.keyDmBorrow" name="领用申请">

    <select id="selectList" parameterType="map" resultType="map">
        SELECT * FROM jl_key_dm_borrow_application
        <where>
            is_used = 1
            and applicant_id = #{params.user.id}
            <if test="params.params.applicant_name != null and params.params.applicant_name != ''">
                AND applicant_name LIKE CONCAT('%', #{params.params.applicant_name}, '%')
            </if>
            <if test="params.params.department_name != null and params.params.department_name != ''">
                AND department_name LIKE CONCAT('%', #{params.params.department_name}, '%')
            </if>
            <if test="params.params.approval_status != null">
                AND approval_status = #{params.params.approval_status}
            </if>
            <if test="params.params.startDate != null">
                AND submit_time &gt;= #{params.params.startDate}
            </if>
            <if test="params.params.endDate != null">
                AND submit_time &lt;= #{params.params.endDate}
            </if>
        </where>
        ORDER BY create_time DESC
    </select>

    <select id="selectPendingList" parameterType="page" resultType="map">
        SELECT * FROM jl_key_dm_borrow_application
        <where>
            is_used = 1
            AND approval_status = 1
            and approver_id = #{params.params.id}
        </where>
        ORDER BY submit_time DESC
    </select>

    <select id="selectHistoryList" parameterType="map" resultType="map">
        SELECT * FROM jl_key_dm_borrow_application
        <where>
            is_used = 1
            <if test="params.params.applicant_name != null and params.params.applicant_name != ''">
                AND applicant_name LIKE CONCAT('%', #{params.params.applicant_name}, '%')
            </if>
            <if test="params.params.department_name != null and params.params.department_name != ''">
                AND department_name LIKE CONCAT('%', #{params.params.department_name}, '%')
            </if>
            <if test="params.params.approval_status != null">
                AND approval_status = #{params.params.approval_status}
            </if>
            <if test="params.params.startDate != null">
                AND submit_time &gt;= #{params.params.startDate}
            </if>
            <if test="params.params.endDate != null">
                AND submit_time &lt;= #{params.params.endDate}
            </if>
        </where>
        ORDER BY create_time DESC
    </select>

    <select id="getById" parameterType="map" resultType="map">
        SELECT * FROM jl_key_dm_borrow_application WHERE id = #{id} LIMIT 1
    </select>

    <insert id="insert" parameterType="map">
        INSERT INTO jl_key_dm_borrow_application(
            id,application_no,applicant_id,applicant_name,department_id,department_name,borrow_purpose,expected_return_date,
            approval_status,issue_status,is_used,create_by,create_time,order_no
        ) VALUES (
            #{id},CONCAT('borrow', REPLACE(UUID(),'-','')),#{applicant_id},#{applicant_name},#{department_id},#{department_name},#{borrow_purpose},#{expected_return_date},
            #{approval_status},#{issue_status},1,#{_user.id},NOW(),#{order_no}
        )
    </insert>

    <update id="update" parameterType="map">
        UPDATE jl_key_dm_borrow_application
        SET borrow_purpose = #{borrow_purpose}, expected_return_date = #{expected_return_date}, update_by = #{_user.id}, update_time = NOW()
        WHERE id = #{id}
    </update>

    <update id="submit" parameterType="map">
        UPDATE jl_key_dm_borrow_application SET approver_id = #{approver_id}, approver_name = #{approver_name}, approval_status = 1, submit_time = NOW(), update_by = #{_user.id}, update_time = NOW() WHERE id = #{id}
    </update>

    <update id="revoke" parameterType="map">
        UPDATE jl_key_dm_borrow_application SET approval_status = 0, update_by = #{_user.id}, update_time = NOW() WHERE id = #{id} AND approval_status = 1
    </update>

    <update id="approve" parameterType="map">
        UPDATE jl_key_dm_borrow_application SET approval_status = 9, approval_time = NOW(), issue_time = NOW(), issue_status = 1, update_by = #{_user.id}, update_time = NOW() WHERE id = #{id}
    </update>

    <update id="reject" parameterType="map">
        UPDATE jl_key_dm_borrow_application SET approval_status = -1, approval_time = NOW(), approval_opinion = #{comment}, update_by = #{_user.id}, update_time = NOW() WHERE id = #{id}
    </update>

    <!-- 删除所有明细 -->
    <delete id="deleteDetailsByApplicationId" parameterType="map">
        DELETE FROM jl_key_dm_borrow_application_detail WHERE application_id = #{id}
    </delete>

    <!-- 生成出库记录与明细（批量由 mapper 内部实现） -->
    <insert id="insertOutboundByApplication" parameterType="map">
        <!-- mapper implementation should insert outbound record and details based on application id -->
        INSERT INTO jl_key_dm_outbound_record(id,outbound_no,outbound_type,outbound_date,receiver_id,receiver_name,receiver_department_id,receiver_department_name,outbound_status,remark,is_used,create_by,create_time)
        SELECT UUID(), CONCAT('OUT', REPLACE(UUID(),'-','')) , 2, NOW(), applicant_id, applicant_name, department_id, department_name, 1, borrow_purpose, 1, #{_user.id}, NOW()
        FROM jl_key_dm_borrow_application WHERE id = #{id}
    </insert>

    <!-- 生成出库记录与明细（批量由 mapper 内部实现） -->
    <insert id="insertOutboundDetail" parameterType="map">
        <!-- mapper implementation should insert outbound record and details based on application id -->
        INSERT INTO jl_key_dm_outbound_detail(id,outbound_id,material_id,outbound_type,outbound_quantity,unit_price,total_amount,borrow_application_detail_id,is_used,create_by,create_time)
        SELECT UUID(), CONCAT('OUT', REPLACE(UUID(),'-','')),bad.material_id,2,bad.apply_quantity,m.unit_price,(m.unit_price * bad.apply_quantity) as total_amount,bad.id,1,#{_user.id},NOW()
        FROM jl_key_dm_borrow_application_detail as bad
        left join jl_key_dm_material as m on m.id = bad.material_id
        WHERE application_id = #{id}
    </insert>

    <select id="selectInventoryByMaterialId" parameterType="map" resultType="map">
        select i.*,m.can_borrow
        from jl_key_dm_inventory as i
        left join jl_key_dm_material as m
        on m.id = i.material_id
        where material_id = #{material_id}

    </select>

    <!-- 更新库存：根据申请明细扣减可用库存（mapper 内部实现以事务执行） -->
    <update id="updateInventoryTotalQuantity" parameterType="map">
        <!-- 简化：调用存储过程或在 mapper 中实现逐条更新 -->
        <!-- 实际实现依赖于业务，留给 mapper/服务调用具体 SQL -->
        update jl_key_dm_inventory
        set total_quantity = total_quantity - #{apply_quantity}
        where material_id = #{material_id}
    </update>

    <!-- 更新库存：根据申请明细扣减可用库存（mapper 内部实现以事务执行） -->
    <update id="updateInventoryBorrowedQuantity" parameterType="map">
        <!-- 简化：调用存储过程或在 mapper 中实现逐条更新 -->
        <!-- 实际实现依赖于业务，留给 mapper/服务调用具体 SQL -->
        update jl_key_dm_inventory
        set borrowed_quantity = borrowed_quantity + #{apply_quantity}
        where material_id = #{material_id}
    </update>


    <select id="selectLogsByApplicationId" parameterType="map">

    </select>
</mapper>


