<?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.areaId != null and params.areaId != ''">
                AND apply_dep_code = #{params.areaId}
            </if>
            <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 approval_status asc,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}
            <if test="params.params.areaId != null">
                AND apply_dep_code = #{params.params.areaId}
            </if>
        </where>
        ORDER BY submit_time DESC
    </select>

    <select id="selectHistoryList" parameterType="map" resultType="map">
        SELECT ba.* FROM jl_key_dm_borrow_application ba
        left join jl_key_dm_user u on ba.applicant_id=u.id
        left join sys_office so on u.office_id=so.id
        <where>
            ba.is_used = 1 and so.area_id = #{params.areaId} and ba.apply_dep_code = #{params.areaId}
            <if test="params.params.applicant_name != null and params.params.applicant_name != ''">
                AND ba.applicant_name LIKE CONCAT('%', #{params.params.applicant_name}, '%')
            </if>
            <if test="params.params.department_name != null and params.params.department_name != ''">
                AND u.department_name LIKE CONCAT('%', #{params.params.department_name}, '%')
            </if>
            <if test="params.params.approval_status != null">
                AND ba.approval_status = #{params.params.approval_status}
            </if>
            <if test="params.params.startDate != null">
                AND ba.submit_time &gt;= #{params.params.startDate}
            </if>
            <if test="params.params.endDate != null">
                AND ba.submit_time &lt;= #{params.params.endDate}
            </if>
        </where>
        ORDER BY ba.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,approver_id,approver_name,
            approval_status,issue_status,is_used,create_by,create_time,order_no,apply_dep_code
        ) VALUES (
            #{id},CONCAT('borrow', REPLACE(UUID(),'-','')),#{applicant_id},#{applicant_name},#{department_id},#{department_name},#{borrow_purpose},#{expected_return_date},#{approver_id},#{approver_name},
            #{approval_status},#{issue_status},1,#{_user.id},NOW(),#{order_no},#{apply_dep_code}
        )
    </insert>

    <update id="update" parameterType="map">
        UPDATE jl_key_dm_borrow_application
        <set>
            <!-- 借用用途：非空时更新 -->
            <if test="borrow_purpose != null">
                borrow_purpose = #{borrow_purpose},
            </if>
            <!-- 审批人ID：非空时更新 -->
            <if test="approver_id != null">
                approver_id = #{approver_id},
            </if>
            <!-- 审批人名称：非空时更新 -->
            <if test="approver_name != null">
                approver_name = #{approver_name},
            </if>
            <!-- 预计归还日期：非空时更新（日期类型只需判断null） -->
            <if test="expected_return_date != null">
                expected_return_date = #{expected_return_date},
            </if>
            <!-- 更新人：强制更新（无需判断，确保操作痕迹） -->
            update_by = #{_user.id},
            <!-- 更新时间：强制更新（数据库自动生成） -->
            update_time = NOW()
        </set>
        WHERE id = #{id}
    </update>

    <update id="submit" parameterType="map">
        UPDATE jl_key_dm_borrow_application
        <set>
            <!-- 审批人ID：非空才更新 -->
            <if test="approver_id != null and approver_id != ''">
                approver_id = #{approver_id},
            </if>
            <!-- 审批人名称：非空才更新 -->
            <if test="approver_name != null and approver_name != ''">
                approver_name = #{approver_name},
            </if>
            <!-- 审批状态：提交时强制设为1，无需判断 -->
            approval_status = 1,
            <!-- 提交时间：强制更新为当前时间 -->
            submit_time = NOW(),
            <!-- 更新人：强制记录 -->
            update_by = #{_user.id},
            <!-- 更新时间：强制更新 -->
            update_time = NOW()
        </set>
        WHERE id = #{id}
    </update>

    <update id="revoke" parameterType="map">
        UPDATE jl_key_dm_borrow_application
        <set>
            <!-- 审批状态：撤销时强制设为0 -->
            approval_status = 0,
            <!-- 更新人：强制记录 -->
            update_by = #{_user.id},
            <!-- 更新时间：强制更新 -->
            update_time = NOW()
        </set>
        WHERE id = #{id}
        AND approval_status = 1 <!-- 仅能撤销“待审批”状态的申请 -->
    </update>

    <update id="approve" parameterType="map">
        UPDATE jl_key_dm_borrow_application
        <set>
            <!-- 审批意见：非空才更新 -->
            <if test="comment != null and comment != ''">
                approval_opinion = #{comment},
            </if>
            <!-- 审批状态：通过时强制设为9 -->
            approval_status = 9,
            <!-- 审批时间：强制更新为当前时间 -->
            approval_time = NOW(),
            <!-- 发放时间：强制更新为当前时间 -->
            issue_time = NOW(),
            <!-- 发放状态：通过时强制设为1 -->
            issue_status = 1,
            <!-- 更新人：强制记录 -->
            update_by = #{_user.id},
            <!-- 更新时间：强制更新 -->
            update_time = NOW()
        </set>
        WHERE id = #{id}
    </update>

    <update id="reject" parameterType="map">
        UPDATE jl_key_dm_borrow_application
        <set>
            <!-- 审批状态：驳回时强制设为-1 -->
            approval_status = -1,
            <!-- 审批时间：强制更新为当前时间 -->
            approval_time = NOW(),
            <!-- 审批意见（approval_opinion）：复用comment，非空才更新 -->
            <if test="comment != null and comment != ''">
                approval_opinion = #{comment},
            </if>
            <!-- 更新人：强制记录 -->
            update_by = #{_user.id},
            <!-- 更新时间：强制更新 -->
            update_time = NOW()
        </set>
        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,apply_dep_code)
        SELECT UUID(), CONCAT('OUT', REPLACE(UUID(),'-','')) , 2, NOW(), applicant_id, applicant_name, department_id, department_name, 1, borrow_purpose, 1, #{_user.id}, NOW(), apply_dep_code
        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}
        <if test="apply_dep_code != null and apply_dep_code != ''">
            AND i.apply_dep_code = #{apply_dep_code}
        </if>

    </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}
        <if test="apply_dep_code != null and apply_dep_code != ''">
            AND apply_dep_code = #{apply_dep_code}
        </if>
    </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}
        <if test="apply_dep_code != null and apply_dep_code != ''">
            AND apply_dep_code = #{apply_dep_code}
        </if>
    </update>


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

    </select>
</mapper>


