<?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.keyDmInbound" name="入库记录">

    <select id="selectList" parameterType="page" resultType="map">
        SELECT * FROM jl_key_dm_inbound_record
        <where>
            AND is_used = 1
            <if test="params.areaId != null and params.areaId != ''">
                AND apply_dep_code = #{params.areaId}
            </if>
            <if test="params.params.inbound_no != null">
                AND inbound_no LIKE CONCAT('%', #{params.params.inbound_no}, '%')
            </if>
            <if test="params.params.batch_no != null">
                AND batch_no LIKE CONCAT('%', #{params.params.batch_no}, '%')
            </if>
            <if test="params.params.startDate != null">
                AND inbound_date &gt;= #{params.params.startDate}
            </if>
            <if test="params.params.endDate != null">
                AND inbound_date &lt;= #{params.params.endDate}
            </if>
            <if test="params.params.inbound_type != null">
                AND inbound_type = #{params.params.inbound_type}
            </if>
            <if test="params.params.inbound_status != null">
                AND inbound_status = #{params.params.inbound_status}
            </if>
        </where>
        ORDER BY inbound_date DESC,create_time desc
    </select>

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

    <insert id="insert" parameterType="map">
        INSERT INTO jl_key_dm_inbound_record(
            id,inbound_no,batch_no,inbound_date,inbound_type,storage_location,inbound_status,remark,is_used,create_by,create_time,order_no,apply_dep_code
        ) VALUES (
            #{id},#{inbound_no},#{batch_no},#{inbound_date},#{inbound_type},#{storage_location},#{inbound_status},#{remark},1,#{_user.id},NOW(),#{order_no},#{apply_dep_code}
        )
    </insert>

    <update id="update" parameterType="map">
        UPDATE jl_key_dm_inbound_record
        SET inbound_no=#{inbound_no},batch_no=#{batch_no},inbound_date=#{inbound_date},storage_location=#{storage_location},remark=#{remark},update_by=#{_user.id},update_time=NOW()
        WHERE id = #{id}
    </update>

    <update id="delete" parameterType="map">
        UPDATE jl_key_dm_inbound_record SET is_used = 0, update_by = #{_user.id}, update_time = NOW() WHERE id = #{id}
    </update>

    <update id="doInbound" parameterType="map">
        UPDATE jl_key_dm_inbound_record SET inbound_status = 1, inbound_date = NOW(), update_by = #{_user.id}, update_time = NOW() WHERE id = #{id} AND inbound_status = 0
    </update>

    <!-- select inventory list for page -->
    <select id="selectInventoryList" parameterType="page" resultType="map">
        SELECT * FROM jl_key_dm_inventory
        <where>
            AND is_used = 1
            <if test="params.reaId != null and params.areaId != ''">
                AND apply_dep_code = #{params.areaId}
            </if>
            <if test="params.params.material_name != null">
                AND material_name LIKE CONCAT('%', #{params.params.material_name}, '%')
            </if>
            <if test="params.params.material_code != null">
                AND material_code LIKE CONCAT('%', #{params.params.material_code}, '%')
            </if>
        </where>
        ORDER BY material_name ASC
    </select>


    <!-- 入库主表添加归还入库单 -->
    <insert id="insertInboundByReturn" parameterType="map">
        INSERT INTO jl_key_dm_inbound_record(id,inbound_no,batch_no,inbound_date,inbound_type,storage_location,inbound_status,remark,is_used,create_by,create_time,apply_dep_code)
        SELECT #{inbound_id}, CONCAT('INR', REPLACE(UUID(),'-','')), '', NOW(), 2, '', 1, '归还入库', 1, #{_user.id}, NOW(), #{apply_dep_code}
        FROM jl_key_dm_borrow_application WHERE id = #{id}
    </insert>
    <!-- 入库明细添加 -->
    <insert id="insertInboundDetailByReturn" parameterType="map">
        insert into jl_key_dm_inbound_detail(id,inbound_id,material_id,inbound_type,inbound_quantity,unit_price,total_amount,production_date,expiry_date,is_used,create_by,create_time)
        select UUID(), #{inbound_id}, material_id,2,apply_quantity,0,0,null,null,1,#{_user.id},now()
        from jl_key_dm_borrow_application_detail where application_id = #{id}
    </insert>
    <!-- 修改申领明细表归还数量-->
    <update id="updateBorrowDetailReturnedQuantity" parameterType="map">
        update jl_key_dm_borrow_application_detail
        set returned_quantity = returned_quantity + #{apply_quantity},damaged_quantity = damaged_quantity + #{damaged_quantity}
        ,update_by = #{_user.id},update_time = NOW()
        where application_id = #{application_id} and material_id = #{material_id}
    </update>
    <update id="updateInventoryBorrowed" parameterType="map">
        update jl_key_dm_inventory
        set borrowed_quantity = borrowed_quantity - #{apply_quantity}
        ,damaged_quantity = damaged_quantity + #{damaged_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>

    <!-- update inventory by inbound (placeholder) -->
    <insert id="updateInventoryByInbound" parameterType="map">
        <!-- 库存UPSERT：material_id存在则更新可用量/总量，不存在则插入 -->
        <!-- 批量库存UPSERT：支持多物料同时更新/插入 -->
        INSERT INTO jl_key_dm_inventory (
        id,
        material_id,
        material_code,
        material_name,
        total_quantity,
        available_quantity,
        borrowed_quantity,
        damaged_quantity,
        is_used,
        create_by,
        create_time,
        update_by,
        update_time,
        order_no,
        apply_dep_code
        )
        VALUES
        <foreach collection="details" item="item" separator=",">
            (
            #{item.id},
            #{item.material_id},
            #{item.material_code},
            #{item.material_name},
            #{item.inbound_quantity},
            0,
            0,
            0,
            #{item.is_used},
            #{_user.id},
            NOW(),
            #{_user.id},
            NOW(),
            0,
            #{_user.areaId}
            )
        </foreach>
        ON DUPLICATE KEY UPDATE
        material_code = VALUES(material_code),
        material_name = VALUES(material_name),
        total_quantity = total_quantity + VALUES(total_quantity),
        available_quantity = available_quantity + VALUES(available_quantity),
        borrowed_quantity = borrowed_quantity + VALUES(borrowed_quantity),
        damaged_quantity = damaged_quantity + VALUES(damaged_quantity),
        is_used = VALUES(is_used),
        update_by = VALUES(update_by),
        update_time = NOW(),
        order_no = VALUES(order_no);
    </insert>

    <select id="selectLogsByInboundId" parameterType="map" resultType="map">
        SELECT ml.*,m.material_name
        FROM jl_key_dm_material_log as ml
        left join jl_key_dm_material as m
        on m.id = ml.material_id
        WHERE ml.relation_id = #{id} AND ml.is_used = 1
        <if test="apply_dep_code != null and apply_dep_code != ''">
            AND ml.apply_dep_code = #{apply_dep_code}
        </if>
        ORDER BY ml.create_time ASC
    </select>

    <select id="selectPendingReturnList" parameterType="map" resultType="map">
        select ba.id,ba.applicant_id,ba.application_no,ba.applicant_name,ba.department_id,
        ba.department_name,ba.issue_status,ba.expected_return_date,ba.issue_time,ba.approval_time,
        ba.borrow_purpose,ba.submit_time
        from jl_key_dm_borrow_application as ba
        left join jl_key_dm_borrow_application_detail as bad on bad.application_id = ba.id
        left join jl_key_dm_material as m on bad.material_id = m.id
        <where>
            m.can_borrow = 1 and ba.issue_status = 1
            and bad.returned_quantity + bad.damaged_quantity != bad.apply_quantity
            <if test="params.areaId != null and params.areaId != ''">
                AND ba.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 department_name like concat('%',#{params.params.department_name},'%')
            </if>
        </where>
        group by ba.id
        order by submit_time desc
    </select>

    <select id="selectPendingReturnDetailsByApplicationId" parameterType="map" resultType="map">
        select ba.expected_return_date,ba.issue_time,
        bad.material_id,bad.material_code,bad.material_name,bad.apply_quantity,bad.returned_quantity,
        bad.damaged_quantity,bad.return_remark,bad.is_overdue,bad.overdue_days
        from jl_key_dm_borrow_application_detail as bad
        left join jl_key_dm_material as m on bad.material_id = m.id
        left join jl_key_dm_borrow_application as ba on bad.application_id = ba.id
        where ba.id = #{id} and m.can_borrow = 1
    </select>
<!--    将物料code为键做成字典-->
    <select id="getMaterialMap" resultType="map">
        select *
        from jl_key_dm_material
        where is_used = 1
    </select>

    <insert id="batchInsertInboundMasters" parameterType="map">
        INSERT INTO jl_key_dm_inbound_record (
        id,
        inbound_no,
        batch_no,
        inbound_type,
        inbound_status,
        inbound_date,
        is_used,
        storage_location,
        remark,
        create_by,
        create_time,
        apply_dep_code
        ) VALUES
        <foreach collection="list" item="item" separator=",">
            (
            #{item.id},
            #{item.inbound_no},
            #{item.batch_no},
            #{item.inbound_type},
            #{item.inbound_status},
            #{item.inbound_date},
            #{item.is_used},
            #{item.storage_location},
            #{item.remark},
            #{item.create_by},
            #{item.create_time},
            #{item.apply_dep_code}
            )
        </foreach>
    </insert>
    <insert id="batchInsertInboundDetails" parameterType="map">
        INSERT INTO jl_key_dm_inbound_detail (
        id,
        inbound_id,
        material_id,
        inbound_type,
        inbound_quantity,
        unit_price,
        total_amount,
        production_date,
        expiry_date,
        is_used,
        create_by,
        create_time
        ) VALUES
        <foreach collection="list" item="item" separator=",">
            (
            #{item.id},
            #{item.inbound_id},
            #{item.material_id},
            #{item.inbound_type},
            #{item.inbound_quantity},
            #{item.unit_price},
            #{item.total_amount},
            #{item.production_date},
            #{item.expiry_date},
            #{item.is_used},
            #{item.create_by},
            #{item.create_time}
            )
        </foreach>
    </insert>
    <insert id="batchUpdateMaterialStock" parameterType="map">
        INSERT INTO jl_key_dm_inventory (id, material_id, material_code, material_name, total_quantity, available_quantity, borrowed_quantity, damaged_quantity, is_used, create_by, create_time, order_no, apply_dep_code)
        VALUES
        <foreach collection="list" item="item" separator=",">
            (UUID(), #{item.material_id}, #{item.material_code}, #{item.material_name}, #{item.inbound_quantity},0 ,0, 0, 1, #{_user.id}, NOW(),0, #{_user.areaId})
        </foreach>
        ON DUPLICATE KEY UPDATE
        total_quantity = total_quantity + VALUES(total_quantity),
        borrowed_quantity = borrowed_quantity + VALUES(borrowed_quantity),
        damaged_quantity = damaged_quantity + VALUES(damaged_quantity),
        update_by = #{_user.id},
        update_time = NOW()
    </insert>
    <insert id="insertInboundMaster" parameterType="map">
        INSERT INTO jl_key_dm_inbound_record (
        id,
        inbound_no,
        batch_no,
        inbound_type,
        inbound_status,
        inbound_date,
        is_used,
        storage_location,
        remark,
        create_by,
        create_time
        ) VALUES (
        #{id},
        #{inbound_no},
        #{batch_no},
        #{inbound_type},
        #{inbound_status},
        #{inbound_date},
        #{is_used},
        #{storage_location},
        #{remark},
        #{create_by},
        #{create_time}
        )
    </insert>
    <insert id="insertInboundDetail" parameterType="map">
        INSERT INTO jl_key_dm_inbound_detail (
        id,
        inbound_id,
        material_id,
        inbound_type,
        inbound_quantity,
        unit_price,
        total_amount,
        production_date,
        expiry_date,
        create_by,
        create_time
        ) VALUES (
        #{id},
        #{inbound_id},
        #{material_id},
        #{inbound_type},
        #{inbound_quantity},
        #{unit_price},
        #{total_amount},
        #{production_date},
        #{expiry_date},
        #{create_by},
        #{create_time}
        )
    </insert>

<!--    details-->
    <insert id="insertDetail" parameterType="map">
        INSERT INTO jl_key_dm_inbound_detail(
        id,inbound_id,material_id,inbound_type,inbound_quantity,unit_price,total_amount,production_date,expiry_date,is_used,create_by,create_time
        ) VALUES (
        #{id},#{inbound_id},#{material_id},#{inbound_type},#{inbound_quantity},#{unit_price},#{total_amount},#{production_date},#{expiry_date},1,#{_user.id},NOW()
        )
    </insert>

    <select id="selectDetailsByInboundId" parameterType="map" resultType="map">
        SELECT i.*,
        m.material_code, m.material_name, m.category_id
        FROM jl_key_dm_inbound_detail as i
        left join jl_key_dm_material as m
        on i.material_id = m.id
        WHERE i.inbound_id = #{id} AND i.is_used = 1 ORDER BY i.create_time ASC
    </select>

    <!--    <delete id="deleteDetailsByInboundId" parameterType="map">-->
    <!--        DELETE FROM jl_key_dm_inbound_detail WHERE inbound_id = #{id}-->
    <!--    </delete>-->

    <update id="deleteDetailsByInboundId" parameterType="map">
        UPDATE jl_key_dm_inbound_detail SET is_used = 0, update_by = #{_user.id}, update_time = NOW() WHERE inbound_id = #{id}
    </update>
</mapper>


