<?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.ruoyi.inventory.mapper.OutboundOrderItemsMapper">

    <resultMap type="OutboundOrderItems" id="OutboundOrderItemsResult">
        <result property="id"    column="id"    />
        <result property="orderId"    column="order_id"    />
        <result property="materialId"    column="material_id"    />
        <result property="batchCode"    column="batch_code"    />
        <result property="warehouseId"    column="warehouse_id"    />
        <result property="locationId"    column="location_id"    />
        <result property="inventoryId"    column="inventory_id"    />
        <result property="outboundOrderId"    column="outbound_order_id"    />
        <result property="unitPrice"    column="unit_price"    />
        <result property="plannedQuantity"    column="planned_quantity"    />
        <result property="actualQuantity"    column="actual_quantity"    />
        <result property="divisor"    column="divisor"    />
        <result property="labelColor"    column="label_color"    />
        <result property="voucherNumber"    column="voucher_number"    />
        <result property="itemStatus"    column="item_status"    />
        <result property="shippedAt"    column="shipped_at"    />
        <result property="shippedBy"    column="shipped_by"    />
        <result property="remark"    column="remark"    />
        <result property="isUsed"    column="is_used"    />
        <result property="sortNo"    column="sort_no"    />
        <result property="createTime"    column="create_time"    />
        <result property="createUserCode"    column="create_user_code"    />
        <result property="updateTime"    column="update_time"    />
        <result property="updateUserCode"    column="update_user_code"    />
    </resultMap>

    <resultMap type="OutboundOrderItemsStatisticsVo" id="OutboundOrderItemsStatisticsVoResult">
        <result property="id"    column="id"    />
        <result property="orderId"    column="order_id"    />
        <result property="materialId"    column="material_id"    />
        <result property="batchCode"    column="batch_code"    />
        <result property="warehouseId"    column="warehouse_id"    />
        <result property="locationId"    column="location_id"    />
        <result property="inventoryId"    column="inventory_id"    />
        <result property="outboundOrderId"    column="outbound_order_id"    />
        <result property="unitPrice"    column="unit_price"    />
        <result property="plannedQuantity"    column="planned_quantity"    />
        <result property="actualQuantity"    column="actual_quantity"    />
        <result property="divisor"    column="divisor"    />
        <result property="labelColor"    column="label_color"    />
        <result property="voucherNumber"    column="voucher_number"    />
        <result property="itemStatus"    column="item_status"    />
        <result property="shippedAt"    column="shipped_at"    />
        <result property="shippedBy"    column="shipped_by"    />
        <result property="amount"    column="amount"    />
        <result property="remark"    column="remark"    />
        <result property="isUsed"    column="is_used"    />
        <result property="sortNo"    column="sort_no"    />
        <result property="createTime"    column="create_time"    />
        <result property="createUserCode"    column="create_user_code"    />
        <result property="updateTime"    column="update_time"    />
        <result property="updateUserCode"    column="update_user_code"    />
    </resultMap>

    <sql id="selectOutboundOrderItemsVo">
        select id, order_id, material_id, batch_code, warehouse_id, location_id, inventory_id, outbound_order_id, unit_price, planned_quantity, actual_quantity, divisor, label_color, voucher_number, item_status, shipped_at, shipped_by, remark, is_used, sort_no, create_time, create_user_code, update_time, update_user_code from outbound_order_items
    </sql>

    <select id="selectOutboundOrderItemsList" parameterType="OutboundOrderItems" resultMap="OutboundOrderItemsResult">
        <include refid="selectOutboundOrderItemsVo"/>
        <where>
            <if test="isUsed == null">
                is_used = 1
            </if>
            <if test="isUsed != null">
                is_used = #{isUsed}
            </if>
            <if test="orderId != null  and orderId != ''"> and order_id = #{orderId}</if>
            <if test="materialId != null  and materialId != ''"> and material_id = #{materialId}</if>
            <if test="batchCode != null  and batchCode != ''"> and batch_code = #{batchCode}</if>
            <if test="warehouseId != null  and warehouseId != ''"> and warehouse_id = #{warehouseId}</if>
            <if test="locationId != null  and locationId != ''"> and location_id = #{locationId}</if>
            <if test="inventoryId != null  and inventoryId != ''"> and inventory_id = #{inventoryId}</if>
            <if test="plannedQuantity != null "> and planned_quantity = #{plannedQuantity}</if>
            <if test="actualQuantity != null "> and actual_quantity = #{actualQuantity}</if>
            <if test="divisor != null "> and divisor = #{divisor}</if>
            <if test="labelColor != null "> and label_color = #{labelColor}</if>
            <if test="voucherNumber != null  and voucherNumber != ''"> and voucher_number = #{voucherNumber}</if>
            <if test="itemStatus != null "> and item_status = #{itemStatus}</if>
            <if test="shippedAt != null "> and shipped_at = #{shippedAt}</if>
            <if test="shippedBy != null  and shippedBy != ''"> and shipped_by = #{shippedBy}</if>
            <if test="sortNo != null "> and sort_no = #{sortNo}</if>
            <if test="createUserCode != null  and createUserCode != ''"> and create_user_code = #{createUserCode}</if>
            <if test="updateUserCode != null  and updateUserCode != ''"> and update_user_code = #{updateUserCode}</if>
        </where>
    </select>

    <!-- 单条查询：默认查使用中，若手动传isUsed则覆盖 -->
    <select id="selectOutboundOrderItemsById" parameterType="String" resultMap="OutboundOrderItemsResult">
        <include refid="selectOutboundOrderItemsVo"/>
        where id = #{id}
        <!-- 增加is_used过滤，避免查询已逻辑删除的数据 -->
        <if test="isUsed == null">
            and is_used = 1
        </if>
        <if test="isUsed != null">
            and is_used = #{isUsed}
        </if>
    </select>

    <insert id="insertOutboundOrderItems" parameterType="OutboundOrderItems">
        insert into outbound_order_items
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="id != null">id,</if>
            <if test="orderId != null">order_id,</if>
            <if test="materialId != null">material_id,</if>
            <if test="batchCode != null">batch_code,</if>
            <if test="warehouseId != null">warehouse_id,</if>
            <if test="locationId != null">location_id,</if>
            <if test="inventoryId != null">inventory_id,</if>
            <if test="outboundOrderId != null">outbound_order_id,</if>
            <if test="unitPrice != null">unit_price,</if>
            <if test="plannedQuantity != null">planned_quantity,</if>
            <if test="actualQuantity != null">actual_quantity,</if>
            <if test="divisor != null">divisor,</if>
            <if test="labelColor != null">label_color,</if>
            <if test="voucherNumber != null">voucher_number,</if>
            <if test="itemStatus != null">item_status,</if>
            <if test="shippedAt != null">shipped_at,</if>
            <if test="shippedBy != null">shipped_by,</if>
            <if test="remark != null">remark,</if>
            <if test="isUsed == null">is_used,</if>
            <if test="isUsed != null">is_used,</if>
            <if test="sortNo != null">sort_no,</if>
            <if test="createTime != null">create_time,</if>
            <if test="createUserCode != null">create_user_code,</if>
            <if test="updateTime != null">update_time,</if>
            <if test="updateUserCode != null">update_user_code,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="id != null">#{id},</if>
            <if test="orderId != null">#{orderId},</if>
            <if test="materialId != null">#{materialId},</if>
            <if test="batchCode != null">#{batchCode},</if>
            <if test="warehouseId != null">#{warehouseId},</if>
            <if test="locationId != null">#{locationId},</if>
            <if test="inventoryId != null">#{inventoryId},</if>
            <if test="outboundOrderId != null">#{outboundOrderId},</if>
            <if test="unitPrice != null">#{unitPrice},</if>
            <if test="plannedQuantity != null">#{plannedQuantity},</if>
            <if test="actualQuantity != null">#{actualQuantity},</if>
            <if test="divisor != null">#{divisor},</if>
            <if test="labelColor != null">#{labelColor},</if>
            <if test="voucherNumber != null">#{voucherNumber},</if>
            <if test="itemStatus != null">#{itemStatus},</if>
            <if test="shippedAt != null">#{shippedAt},</if>
            <if test="shippedBy != null">#{shippedBy},</if>
            <if test="remark != null">#{remark},</if>
            <if test="isUsed == null">1,</if>
            <if test="isUsed != null">#{isUsed},</if>
            <if test="sortNo != null">#{sortNo},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="createUserCode != null">#{createUserCode},</if>
            <if test="updateTime != null">#{updateTime},</if>
            <if test="updateUserCode != null">#{updateUserCode},</if>
        </trim>
    </insert>

    <update id="updateOutboundOrderItems" parameterType="OutboundOrderItems">
        update outbound_order_items
        <trim prefix="SET" suffixOverrides=",">
            <if test="orderId != null">order_id = #{orderId},</if>
            <if test="materialId != null">material_id = #{materialId},</if>
            <if test="batchCode != null">batch_code = #{batchCode},</if>
            <if test="warehouseId != null">warehouse_id = #{warehouseId},</if>
            <if test="locationId != null">location_id = #{locationId},</if>
            <if test="inventoryId != null">inventory_id = #{inventoryId},</if>
            <if test="outboundOrderId != null">outbound_order_id = #{outboundOrderId},</if>
            <if test="unitPrice != null">unit_price = #{unitPrice},</if>
            <if test="plannedQuantity != null">planned_quantity = #{plannedQuantity},</if>
            <if test="actualQuantity != null">actual_quantity = #{actualQuantity},</if>
            <if test="divisor != null">divisor = #{divisor},</if>
            <if test="labelColor != null">label_color = #{labelColor},</if>
            <if test="voucherNumber != null">voucher_number = #{voucherNumber},</if>
            <if test="itemStatus != null">item_status = #{itemStatus},</if>
            <if test="shippedAt != null">shipped_at = #{shippedAt},</if>
            <if test="shippedBy != null">shipped_by = #{shippedBy},</if>
            <if test="remark != null">remark = #{remark},</if>
            <if test="isUsed != null">is_used = #{isUsed},</if>
            <if test="sortNo != null">sort_no = #{sortNo},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="createUserCode != null">create_user_code = #{createUserCode},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
            <if test="updateUserCode != null">update_user_code = #{updateUserCode},</if>
        </trim>
        where id = #{id}
    </update>

    <!-- 单条删除：逻辑删除 -->
    <update id="deleteOutboundOrderItemsById" parameterType="String">
        update outbound_order_items
        set is_used = 0
        where id = #{id}
    </update>

    <!-- 统计查询：默认查is_used=1，支持手动传isUsed覆盖 -->
    <select id="selectOutboundOrderItemsStatistics"
            parameterType="OutboundOrderItemsStatisticsVO"
            resultMap="OutboundOrderItemsStatisticsVoResult">
        SELECT
        oi.id,
        o.order_id,
        oi.material_id,
        oi.batch_code,
        oi.warehouse_id,
        oi.inventory_id,
        oi.location_id,
        oi.planned_quantity,
        oi.actual_quantity,
        oi.divisor,
        oi.label_color,
        oi.voucher_number,
        oi.unit_price,
        oi.unit_price*oi.actual_quantity AS amount,
        oi.item_status,
        o.inbound_date AS shipped_at,
        oi.shipped_by,
        oi.remark,
        oi.is_used,
        oi.sort_no,
        oi.create_time,
        oi.create_user_code,
        oi.update_time,
        oi.update_user_code
        FROM outbound_order_items oi
        LEFT JOIN outbound_orders o ON oi.outbound_order_id = o.id
        <where>
            <if test="isUsed == null">
                oi.is_used = 1
            </if>
            <if test="isUsed != null">
                oi.is_used = #{isUsed}
            </if>
            <if test="orderId != null and orderId != ''">
                AND o.order_id LIKE CONCAT('%', #{orderId}, '%')
            </if>
            <if test="startDate != null">
                AND o.inbound_date >= #{startDate}
            </if>
            <if test="endDate != null">
                AND o.inbound_date &lt;= DATE_ADD(#{endDate}, INTERVAL 1 DAY)
            </if>
            <if test="materialId != null and materialId != ''">
                AND oi.material_id LIKE CONCAT('%', #{materialId}, '%')
            </if>
            <if test="warehouseId != null and warehouseId != ''">
                AND oi.warehouse_id = #{warehouseId}
            </if>
            <if test="locationId != null and locationId != ''">
                AND oi.location_id = #{locationId}
            </if>
            <if test="itemStatus != null and itemStatus != ''">
                AND oi.item_status = #{itemStatus}
            </if>
        </where>
        ORDER BY oi.create_time DESC
    </select>

    <!-- 批量删除：逻辑删除 -->
    <update id="deleteOutboundOrderItemsByIds" parameterType="String">
        update outbound_order_items
        set is_used = 0
        where id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </update>

</mapper>
