<?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="inventoryId"    column="inventory_id"    />
        <result property="orderId"        column="order_id"        />
        <result property="materialId"     column="material_id"     />
        <result property="materialName"   column="material_name"   /> <!-- 关联物料表查询 -->
        <result property="batchCode"      column="batch_code"      />
        <result property="warehouseId"    column="warehouse_id"    />
        <result property="warehouseName"  column="warehouse_name"  />
        <result property="locationId"     column="location_id"     />
        <result property="locationName"   column="location_name"   />
        <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>

    <!-- 统计VO结果映射：仅保留表中存在的字段 + 必要计算字段 -->
    <resultMap type="OutboundOrdersSummaryVO" id="OutboundOrdersSummaryVOResult">
        <!-- 物料基础信息 -->
        <result property="materialId"         column="material_id"         />
        <result property="materialName"       column="material_name"       />
        <result property="sapNo"              column="sap_no"              />
        <result property="tsCode"             column="ts_code"             />

        <result property="locationId"     column="location_id"     />
        <result property="locationName"   column="location_name"   />
        <result property="itemStatus"   column="item_status"   />

        <result property="unitPrice"      column="unit_price"      />
        <result property="hazardId"           column="hazard_id"           />
        <result property="specification"      column="specification"       />
        <result property="materialUnit"       column="material_unit"       />
        <result property="unitWeight"         column="unit_weight"         />
        <result property="packageWeight"      column="package_weight"      />
        <result property="totalWeight"        column="total_weight"        />
        <result property="volume"             column="volume"              />
        <result property="shelfLifeDays"      column="shelf_life_days"     />
        <result property="storageTemperature" column="storage_temperature" />
        <result property="specialRequirements" column="special_requirements" />
        <result property="sortNo"             column="sort_no"             />

        <!-- 统计字段 -->
        <result property="plannedQuantity"    column="planned_quantity"    />
        <result property="actualQuantity"     column="actual_quantity"     />
        <result property="totalAmount"        column="total_amount"        />

        <!-- 仓库信息 -->
        <result property="warehouseId"        column="warehouse_id"        />
        <result property="warehouseName"      column="warehouse_name"      />

        <!-- 批次和订单信息 -->
        <result property="batchCode"          column="batch_code"          />
        <result property="orderId"            column="order_id"            />
        <result property="mainOrderId"        column="main_order_id"       />

    </resultMap>

    <!-- 基础查询SQL：仅关联表中存在的字段，保留出库单主表关联（无货主） -->
    <sql id="selectOutboundOrderItemsVo">
        select
            oi.id,
            oi.inventory_id,
            oi.order_id,
            oi.material_id,
            m.material_name,
            oi.batch_code,
            oi.warehouse_id,
            w.warehouses_name as warehouse_name,
            oi.location_id,
            sl.location_name,
            oi.outbound_order_id,
            oi.unit_price,
            oi.planned_quantity,
            oi.actual_quantity,
            oi.divisor,
            oi.label_color,
            oi.voucher_number,
            (oi.unit_price * oi.actual_quantity) as total_amount,
            oi.item_status,
            COALESCE(oi.shipped_at, 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 materials m on oi.material_id = m.id and m.is_used = 1
                 left join warehouses w on oi.warehouse_id = w.id and w.is_enabled = 1
                 left join storage_locations sl on oi.location_id = sl.id and sl.is_enabled = 1
                 left join outbound_orders o on oi.outbound_order_id = o.id and o.is_used = 1
        where oi.is_used = 1
    </sql>

    <!-- 列表查询：仅保留表中存在的查询条件 -->
    <select id="selectOutboundOrderItemsList" parameterType="OutboundOrderItems" resultMap="OutboundOrderItemsResult">
        <include refid="selectOutboundOrderItemsVo"/>
        <if test="orderId != null  and orderId != ''"> and oi.order_id = #{orderId}</if>
        <if test="outboundOrderId != null  and outboundOrderId != ''"> and oi.outbound_order_id = #{outboundOrderId}</if>
        <if test="materialId != null  and materialId != ''"> and oi.material_id = #{materialId}</if>
        <if test="batchCode != null  and batchCode != ''"> and oi.batch_code = #{batchCode}</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="inventoryId != null  and inventoryId != ''"> and oi.inventory_id = #{inventoryId}</if>
        <if test="plannedQuantity != null "> and oi.planned_quantity = #{plannedQuantity}</if>
        <if test="actualQuantity != null "> and oi.actual_quantity = #{actualQuantity}</if>
        <if test="divisor != null "> and oi.divisor = #{divisor}</if>
        <if test="labelColor != null "> and oi.label_color = #{labelColor}</if>
        <if test="voucherNumber != null  and voucherNumber != ''"> and oi.voucher_number = #{voucherNumber}</if>
        <if test="itemStatus != null "> and oi.item_status = #{itemStatus}</if>
        <if test="shippedAt != null "> and COALESCE(oi.shipped_at, o.inbound_date) = #{shippedAt}</if>
        <if test="shippedBy != null  and shippedBy != ''"> and oi.shipped_by = #{shippedBy}</if>
        <if test="sortNo != null "> and oi.sort_no = #{sortNo}</if>
        <if test="createUserCode != null  and createUserCode != ''"> and oi.create_user_code = #{createUserCode}</if>
        <if test="updateUserCode != null  and updateUserCode != ''"> and oi.update_user_code = #{updateUserCode}</if>
    </select>

    <!-- 单条查询：仅关联表中存在字段 -->
    <select id="selectOutboundOrderItemsById" parameterType="String" resultMap="OutboundOrderItemsResult">
        <include refid="selectOutboundOrderItemsVo"/>
        and oi.id = #{id}
    </select>

    <!-- 新增操作：仅保留表中存在的字段，默认is_used=1 -->
    <insert id="insertOutboundOrderItems" parameterType="OutboundOrderItems">
        insert into outbound_order_items
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="id != null">id,</if>
            <if test="inventoryId != null">inventory_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="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="inventoryId != null">#{inventoryId},</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="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="inventoryId != null">inventory_id = #{inventoryId},</if>
            <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="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>

    <select id="selectOutboundOrderItemsStatistics"
            parameterType="OutboundOrdersSummaryVO"
            resultMap="OutboundOrdersSummaryVOResult">
        select
        oi.material_id,
        m.material_name,
        m.sap_no,
        m.ts_code,
        m.hazard_id,
        m.specification,
        m.material_unit,
        m.unit_weight,
        m.package_weight,
        m.total_weight,
        m.volume,
        m.shelf_life_days,
        oi.location_id,
        sl.location_name as location_name,
        m.storage_temperature,
        m.special_requirements,
        m.sort_no,
        sum(oi.planned_quantity) as planned_quantity,
        sum(oi.actual_quantity) as actual_quantity,
        sum(oi.unit_price * oi.actual_quantity) as total_amount,
        oi.warehouse_id,
        w.warehouses_name as warehouse_name,
        oi.batch_code,
        oi.order_id,
        oi.remark,
        oi.unit_price,
        o.order_id as main_order_id,
        oi.item_status
        from outbound_order_items oi
        left join outbound_orders o on oi.outbound_order_id = o.id
        left join materials m on oi.material_id = m.id
        left join warehouses w on oi.warehouse_id = w.id
        left join storage_locations sl on oi.location_id = sl.id
        <where>
            oi.is_used = 1 and o.is_used = 1
            <if test="orderId != null  and orderId != ''"> and oi.order_id like concat('%', #{orderId}, '%')</if>
            <if test="materialId != null  and materialId != ''"> and oi.material_id = #{materialId}</if>
            <if test="batchCode != null  and batchCode != ''"> and oi.batch_code = #{batchCode}</if>
            <if test="startDate != null  and startDate != ''"> and date_format(COALESCE(oi.shipped_at, o.inbound_date),'%Y-%m-%d') &gt;= #{startDate}</if>
            <if test="endDate != null  and endDate != ''"> and date_format(COALESCE(oi.shipped_at, o.inbound_date),'%Y-%m-%d') &lt;= #{endDate}</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 oi.item_status = #{itemStatus}</if>
        </where>
        group by oi.material_id, m.material_name, m.sap_no,
        m.ts_code,
        m.hazard_id,
        m.specification,
        m.material_unit,
        m.unit_weight,
        m.package_weight,
        m.total_weight,
        m.volume,
        m.shelf_life_days,
        m.storage_temperature,
        m.special_requirements,
        m.sort_no,
        oi.warehouse_id,
        w.warehouses_name,
        oi.batch_code,
        oi.order_id,
        o.order_id
        order by sum(oi.unit_price * oi.actual_quantity) desc,m.sap_no, m.sort_no
    </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>

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

    <insert id="batchInsertOutboundOrderItems" parameterType="java.util.List">
        insert into outbound_order_items
        (
        id,
        inventory_id,
        order_id,
        material_id,
        batch_code,
        warehouse_id,
        location_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
        )
        values
        <foreach collection="list" item="item" separator=",">
            (
            <if test="item.id != null">#{item.id}</if><if test="item.id == null">null</if>,
            <if test="item.inventoryId != null">#{item.inventoryId}</if><if test="item.inventoryId == null">null</if>,
            <if test="item.orderId != null">#{item.orderId}</if><if test="item.orderId == null">null</if>,
            <if test="item.materialId != null">#{item.materialId}</if><if test="item.materialId == null">null</if>,
            <if test="item.batchCode != null">#{item.batchCode}</if><if test="item.batchCode == null">null</if>,
            <if test="item.warehouseId != null">#{item.warehouseId}</if><if test="item.warehouseId == null">null</if>,
            <if test="item.locationId != null">#{item.locationId}</if><if test="item.locationId == null">null</if>,
            <if test="item.outboundOrderId != null">#{item.outboundOrderId}</if><if test="item.outboundOrderId == null">null</if>,
            <if test="item.unitPrice != null">#{item.unitPrice}</if><if test="item.unitPrice == null">null</if>,
            <if test="item.plannedQuantity != null">#{item.plannedQuantity}</if><if test="item.plannedQuantity == null">null</if>,
            <if test="item.actualQuantity != null">#{item.actualQuantity}</if><if test="item.actualQuantity == null">null</if>,
            <if test="item.divisor != null">#{item.divisor}</if><if test="item.divisor == null">null</if>,
            <if test="item.labelColor != null">#{item.labelColor}</if><if test="item.labelColor == null">null</if>,
            <if test="item.voucherNumber != null">#{item.voucherNumber}</if><if test="item.voucherNumber == null">null</if>,
            <if test="item.itemStatus != null">#{item.itemStatus}</if><if test="item.itemStatus == null">null</if>,
            <if test="item.shippedAt != null">#{item.shippedAt}</if><if test="item.shippedAt == null">null</if>,
            <if test="item.shippedBy != null">#{item.shippedBy}</if><if test="item.shippedBy == null">null</if>,
            <if test="item.remark != null">#{item.remark}</if><if test="item.remark == null">null</if>,
            <if test="item.isUsed == null">1</if><if test="item.isUsed != null">#{item.isUsed}</if>,
            <if test="item.sortNo != null">#{item.sortNo}</if><if test="item.sortNo == null">null</if>,
            <if test="item.createTime != null">#{item.createTime}</if><if test="item.createTime == null">null</if>,
            <if test="item.createUserCode != null">#{item.createUserCode}</if><if test="item.createUserCode == null">null</if>,
            <if test="item.updateTime != null">#{item.updateTime}</if><if test="item.updateTime == null">null</if>,
            <if test="item.updateUserCode != null">#{item.updateUserCode}</if><if test="item.updateUserCode == null">null</if>
            )
        </foreach>
    </insert>
</mapper>