<?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.OutboundOrdersMapper">

    <resultMap type="com.ruoyi.inventory.domain.OutboundOrders" id="OutboundOrdersResult">
        <result property="id"    column="id"    />
        <result property="orderId"    column="order_id"    />
        <result property="systemNo"    column="system_no"    />
        <result property="orderTypeId"    column="order_type_id"    />
        <result property="orderType"    column="order_type"    />
        <result property="batchCode"    column="batch_code"    />
        <result property="warehouseId"    column="warehouse_id"    />
        <result property="warehouseName"    column="warehouse_name"    />
        <result property="ownerId"    column="owner_id"    />
        <result property="ownerName"    column="owner_name"    />
        <result property="orderStatus"    column="order_status"    />
        <result property="inboundDate"    column="inbound_date"    />
        <result property="destination"    column="destination"    />
        <result property="totalPlannedQuantity"    column="total_planned_quantity"    />
        <result property="totalActualQuantity"    column="total_actual_quantity"    />
        <result property="totalPackages"    column="total_packages"    />
        <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"    />
        <!-- 新增 isImport 字段映射 -->
        <result property="isImport"    column="is_import"    />
    </resultMap>

    <resultMap id="OutboundOrdersOutboundOrderItemsResult" type="com.ruoyi.inventory.domain.OutboundOrders" extends="OutboundOrdersResult">
        <collection
                property="outboundOrderItemsList"
                ofType="com.ruoyi.inventory.domain.OutboundOrderItems"
                column="id"
                select="selectOutboundOrderItemsList" />
    </resultMap>

    <resultMap type="com.ruoyi.inventory.domain.OutboundOrderItems" id="OutboundOrderItemsResult">
        <result property="id" column="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="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" />
        <result property="inboundOrderId" column="inbound_order_id" />
    </resultMap>

    <resultMap id="OutboundTemplateVOMap" type="com.ruoyi.inventory.domain.vo.OutboundTemplateVO">
    <id column="id" property="id"/>
    <result column="inbound_date" property="inboundDate"/>
    <result column="sap_no" property="sapNo"/>
    <result column="material_id" property="materialId"/>
    <result column="material_name" property="materialName"/>
    <result column="ts_code" property="tsCode"/>
    <result column="batch_code" property="batchCode"/>
    <result column="planned_quantity" property="plannedQuantity"/>
    <result column="divisor" property="divisor"/>
    <result column="piece_weight" property="pieceWeight"/>
    <result column="actual_packages" property="actualPackages"/>
    <result column="actual_quantity" property="actualQuantity"/>
    <result column="location_name" property="locationName"/>
    <result column="location_id" property="locationId"/>
    <result column="warehouse_id" property="warehouseId"/>
    <result column="warehouse_name" property="warehouseName"/>
    <result column="remark" property="remark"/>
    <result column="label_color" property="labelColor"/>
    <result column="voucher_number" property="voucherNumber"/>
    <result column="insulation" property="insulation"/>
    <result column="danger_check_type" property="dangerCheckType"/>
    <result column="order_id" property="orderId"/>
    <result column="system_no" property="systemNo"/>
    <result column="order_type" property="orderType"/>
    <result column="owner_id" property="ownerId"/>
    <result column="owner_name" property="ownerName"/>
    <result column="destination" property="destination"/>
    <result column="reservation" property="reservation"/>
    <result column="single_piece_weight" property="singlePieceWeight"/>
    </resultMap>

    <sql id="selectOutboundOrdersVo">
        select
            oo.id,
            oo.order_id,
            oo.system_no,
            oo.order_type_id,
            oo.order_type,
            oo.batch_code,
            oo.warehouse_id,
            w.warehouses_name as warehouse_name,
            oo.owner_id,
            o.owner_name,
            oo.order_status,
            oo.inbound_date,
            oo.destination,
            oo.total_planned_quantity,
            oo.total_actual_quantity,
            oo.total_packages,
            oo.remark,
            oo.is_used,
            oo.sort_no,
            oo.create_time,
            oo.create_user_code,
            oo.update_time,
            oo.update_user_code,
            oo.is_import
        from outbound_orders oo
                 left join owners o on oo.owner_id = o.id and o.is_used = 1
                 left join warehouses w on oo.warehouse_id = w.id and w.is_used = 1 and w.is_enabled = 1
        where oo.is_used = 1
    </sql>

    <select id="selectOutboundOrdersList" parameterType="OutboundOrders" resultMap="OutboundOrdersOutboundOrderItemsResult">
        <include refid="selectOutboundOrdersVo"/>
        <if test="orderId != null  and orderId != ''"> and oo.order_id like concat('%', #{orderId}, '%')</if>
        <if test="systemNo != null  and systemNo != ''"> and oo.system_no like concat('%', #{systemNo}, '%')</if>
        <if test="orderTypeId != null  and orderTypeId != ''"> and oo.order_type_id = #{orderTypeId}</if>
        <if test="orderType != null  and orderType != ''"> and oo.order_type = #{orderType}</if>
        <if test="batchCode != null  and batchCode != ''"> and oo.batch_code like concat('%', #{batchCode}, '%')</if>
        <if test="warehouseId != null  and warehouseId != ''"> and oo.warehouse_id = #{warehouseId}</if>
        <if test="ownerId != null  and ownerId != ''"> and oo.owner_id = #{ownerId}</if>
        <if test="orderStatus != null "> and oo.order_status = #{orderStatus}</if>
        <if test="isImport != null "> and oo.is_import = #{isImport}</if>
        <if test="startDate != null and startDate != ''">
            and DATE(oo.inbound_date) >= #{startDate}
        </if>
        <if test="endDate != null and endDate != ''">
            and DATE(oo.inbound_date) &lt;= #{endDate}
        </if>
        <if test="destination != null  and destination != ''"> and oo.destination = #{destination}</if>
        <if test="totalPlannedQuantity != null "> and oo.total_planned_quantity = #{totalPlannedQuantity}</if>
        <if test="totalActualQuantity != null "> and oo.total_actual_quantity = #{totalActualQuantity}</if>
        <if test="totalPackages != null "> and oo.total_packages = #{totalPackages}</if>
        <if test="sortNo != null "> and oo.sort_no = #{sortNo}</if>
        <if test="createUserCode != null  and createUserCode != ''"> and oo.create_user_code = #{createUserCode}</if>
        <if test="updateUserCode != null  and updateUserCode != ''"> and oo.update_user_code = #{updateUserCode}</if>
        <!-- 按创建时间降序，订单状态升序排序（创建时间最新的在前，订单状态靠前展示） -->
        order by oo.order_status asc,oo.inbound_date desc
    </select>

    <select id="selectOutboundOrdersById" parameterType="String" resultMap="OutboundOrdersOutboundOrderItemsResult">
        select
            oo.id,
            oo.order_id,
            oo.system_no,
            oo.order_type_id,
            oo.order_type,
            oo.batch_code,
            oo.warehouse_id,
            w.warehouses_name as warehouse_name,
            oo.owner_id,
            o.owner_name,
            oo.order_status,
            oo.inbound_date,
            oo.destination,
            oo.total_planned_quantity,
            oo.total_actual_quantity,
            oo.total_packages,
            oo.remark,
            oo.is_used,
            oo.sort_no,
            oo.create_time,
            oo.create_user_code,
            oo.update_time,
            oo.update_user_code,
            oo.is_import
        from outbound_orders oo
                 left join owners o on oo.owner_id = o.id
                 left join warehouses w on oo.warehouse_id = w.id
        where oo.id = #{id} and oo.is_used=1
    </select>

    <select id="selectOutboundOrderItemsList" parameterType="String" resultMap="OutboundOrderItemsResult">
        select
            ooi.id,
            ooi.order_id,
            ooi.material_id,
            m.material_name,
            ooi.batch_code,
            ooi.warehouse_id,
            w.warehouses_name as warehouse_name,
            ooi.location_id,
            sl.location_name,
            ooi.inventory_id,
            ooi.outbound_order_id,
            ooi.unit_price,
            ooi.planned_quantity,
            ooi.actual_quantity,
            ooi.divisor,
            ooi.label_color,
            ooi.voucher_number,
            ooi.item_status,
            ooi.shipped_at,
            ooi.shipped_by,
            ooi.remark,
            ooi.is_used,
            ooi.sort_no,
            ooi.create_time,
            ooi.create_user_code,
            ooi.update_time,
            ooi.update_user_code
        from outbound_order_items ooi
                 left join materials m on ooi.material_id = m.id
                 left join warehouses w on ooi.warehouse_id = w.id
                 left join storage_locations sl on ooi.location_id = sl.id
        where ooi.outbound_order_id = #{id} AND ooi.is_used=1
    </select>

    <insert id="insertOutboundOrders" parameterType="OutboundOrders">
        insert into outbound_orders
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="id != null">id,</if>
            <if test="orderId != null">order_id,</if>
            <if test="systemNo != null">system_no,</if>
            <if test="orderTypeId != null">order_type_id,</if>
            <if test="orderType != null">order_type,</if>
            <if test="batchCode != null">batch_code,</if>
            <if test="warehouseId != null">warehouse_id,</if>
            <if test="ownerId != null">owner_id,</if>
            <if test="orderStatus != null">order_status,</if>
            <if test="inboundDate != null">inbound_date,</if>
            <if test="destination != null">destination,</if>
            <if test="totalPlannedQuantity != null">total_planned_quantity,</if>
            <if test="totalActualQuantity != null">total_actual_quantity,</if>
            <if test="totalPackages != null">total_packages,</if>
            <if test="remark != null">remark,</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>
            <!-- 新增 is_import 字段插入 -->
            <if test="isImport != null">is_import,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="id != null">#{id},</if>
            <if test="orderId != null">#{orderId},</if>
            <if test="systemNo != null">#{systemNo},</if>
            <if test="orderTypeId != null">#{orderTypeId},</if>
            <if test="orderType != null">#{orderType},</if>
            <if test="batchCode != null">#{batchCode},</if>
            <if test="warehouseId != null">#{warehouseId},</if>
            <if test="ownerId != null">#{ownerId},</if>
            <if test="orderStatus != null">#{orderStatus},</if>
            <if test="inboundDate != null">#{inboundDate},</if>
            <if test="destination != null">#{destination},</if>
            <if test="totalPlannedQuantity != null">#{totalPlannedQuantity},</if>
            <if test="totalActualQuantity != null">#{totalActualQuantity},</if>
            <if test="totalPackages != null">#{totalPackages},</if>
            <if test="remark != null">#{remark},</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>
            <!-- 新增 is_import 字段值 -->
            <if test="isImport != null">#{isImport},</if>
        </trim>
    </insert>

    <update id="updateOutboundOrders" parameterType="OutboundOrders">
        update outbound_orders
        <trim prefix="SET" suffixOverrides=",">
            <if test="orderId != null">order_id = #{orderId},</if>
            <if test="systemNo != null">system_no = #{systemNo},</if>
            <if test="orderTypeId != null">order_type_id = #{orderTypeId},</if>
            <if test="orderType != null">order_type = #{orderType},</if>
            <if test="batchCode != null">batch_code = #{batchCode},</if>
            <if test="warehouseId != null">warehouse_id = #{warehouseId},</if>
            <if test="ownerId != null">owner_id = #{ownerId},</if>
            <if test="orderStatus != null">order_status = #{orderStatus},</if>
            <if test="inboundDate != null">inbound_date = #{inboundDate},</if>
            <if test="destination != null">destination = #{destination},</if>
            <if test="totalPlannedQuantity != null">total_planned_quantity = #{totalPlannedQuantity},</if>
            <if test="totalActualQuantity != null">total_actual_quantity = #{totalActualQuantity},</if>
            <if test="totalPackages != null">total_packages = #{totalPackages},</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>
            <!-- 新增 is_import 字段更新 -->
            <if test="isImport != null">is_import = #{isImport},</if>
        </trim>
        where id = #{id}
    </update>

    <update id="deleteOutboundOrdersById" parameterType="String">
        update outbound_orders set is_used=0 where id = #{id}
    </update>

    <update id="deleteOutboundOrdersByIds" parameterType="String">
        update outbound_orders set is_used=0  where id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </update>

    <update id="deleteOutboundOrderItemsByOrderIds" parameterType="String">
        update  outbound_order_items set is_used=0 where outbound_order_id in
        <foreach item="orderId" collection="array" open="(" separator="," close=")">
            #{orderId}
        </foreach>
    </update>

    <delete id="deleteOutboundOrderItemsByOrderId" parameterType="String">
        update outbound_order_items set is_used=0 where outbound_order_id = #{orderId}
    </delete>

    <insert id="batchOutboundOrderItems">
        insert into outbound_order_items(
        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
        ) values
        <foreach item="item" index="index" collection="list" separator=",">
            (
            #{item.id}, #{item.orderId}, #{item.materialId}, #{item.batchCode}, #{item.warehouseId},
            #{item.locationId}, #{item.inventoryId}, #{item.outboundOrderId}, #{item.unitPrice}, #{item.plannedQuantity}, #{item.actualQuantity}, #{item.divisor},
            #{item.labelColor}, #{item.voucherNumber}, #{item.itemStatus}, #{item.shippedAt},
            #{item.shippedBy}, #{item.remark}, #{item.isUsed}, #{item.sortNo}, #{item.createTime},
            #{item.createUserCode}, #{item.updateTime}, #{item.updateUserCode}
            )
        </foreach>
    </insert>

    <select id="SelectOutboundOrdersMaterialsTopTenByQuantity" resultType="java.util.Map">
        select
            m.material_name as name,
            sum(IFNULL(ooi.actual_quantity, 0)) as value
        from materials m
            left join outbound_order_items ooi
        on ooi.material_id = m.id
            and ooi.is_used = 1
            and ooi.item_status=3
            and ooi.shipped_at >= DATE_FORMAT(CURDATE(), '%Y-%m-01 00:00:00')
            and ooi.shipped_at  &lt; DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01 00:00:00')
        where
            m.is_used =1
          and m.is_active =1
        group by m.id, m.material_name
        order by value desc
            limit 10;
    </select>

    <select id="SelectOutboundOrdersMaterialsTopTenByAmount" resultType="java.util.Map">
        select
            m.material_name as name,
            sum(IFNULL(ooi.actual_quantity, 0) * IFNULL(ooi.unit_price, 0)) as value
        from materials m
            left join outbound_order_items ooi
        on ooi.material_id = m.id
            and ooi.is_used = 1
            and ooi.item_status=3
            and ooi.shipped_at >= DATE_FORMAT(CURDATE(), '%Y-%m-01 00:00:00')
            and ooi.shipped_at  &lt; DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01 00:00:00')
        where
            m.is_used =1
          and m.is_active =1
        group by m.id, m.material_name
        order by value desc
            limit 10;
    </select>

    <select id="outboundOrdersCount" resultType="String">
        select count(*) from outbound_orders where is_used = 1 and order_status=2
                                               and inbound_date &gt;= DATE_FORMAT(CURDATE(), '%Y-%m-01')
                                               and inbound_date &lt; DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01')
    </select>

    <update id="batchUpdateInventory">
        <foreach collection="list" item="item" separator=";">
            UPDATE inventory
            SET quantity = #{item.quantity},
            inventory_status = #{item.inventoryStatus},
            update_by = #{item.updateBy},
            update_time = #{item.updateTime}
            WHERE id = #{item.id}
        </foreach>
    </update>

    <select id="selectOutboundOrdersExportList"
            parameterType="OutboundOrders"
            resultMap="OutboundTemplateVOMap">
        select
        oi.id, 
        o.inbound_date,
        m.sap_no,
        m.id as material_id,
        m.material_name,
        m.ts_code,
        oi.batch_code,
        oi.planned_quantity,
        oi.divisor,
        m.package_weight as piece_weight,
        null as actual_packages,
        oi.actual_quantity,
        sl.location_name,
        sl.id as location_id,
        w.id as warehouse_id,
        w.warehouses_name as warehouse_name,
        oi.remark,
        oi.label_color,
        oi.voucher_number,
        m.storage_temperature as insulation,
        m.risk_level as danger_check_type,
        o.order_id,
        o.system_no,
        o.order_type,
        ow.id as owner_id,
        ow.owner_name,
        o.destination,
        null as reservation,
        m.unit_weight as single_piece_weight
        from outbound_order_items oi
        left join outbound_orders o on oi.outbound_order_id = o.id
        left join owners ow on o.owner_id = ow.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>
            <if test="orderId != null  and orderId != ''"> and o.order_id like concat('%', #{orderId}, '%')</if>
            <if test="systemNo != null  and systemNo != ''"> and o.system_no like concat('%', #{systemNo}, '%')</if>
            <if test="orderTypeId != null  and orderTypeId != ''"> and o.order_type_id = #{orderTypeId}</if>
            <if test="orderType != null  and orderType != ''"> and o.order_type = #{orderType}</if>
            <if test="batchCode != null  and batchCode != ''"> and o.batch_code like concat('%', #{batchCode}, '%')</if>
            <if test="warehouseId != null  and warehouseId != ''"> and o.warehouse_id = #{warehouseId}</if>
            <if test="ownerId != null  and ownerId != ''"> and o.owner_id = #{ownerId}</if>
            <if test="orderStatus != null "> and o.order_status = #{orderStatus}</if>
            <if test="isImport != null "> and o.is_import = #{isImport}</if>
            <if test="startDate != null and startDate != ''">
                and DATE(o.inbound_date) >= #{startDate}
            </if>
            <if test="endDate != null and endDate != ''">
                and DATE(o.inbound_date) &lt;= #{endDate}
            </if>
            <if test="destination != null  and destination != ''"> and o.destination = #{destination}</if>
            <if test="totalPlannedQuantity != null "> and o.total_planned_quantity = #{totalPlannedQuantity}</if>
            <if test="totalActualQuantity != null "> and o.total_actual_quantity = #{totalActualQuantity}</if>
            <if test="totalPackages != null "> and o.total_packages = #{totalPackages}</if>
            <if test="sortNo != null "> and o.sort_no = #{sortNo}</if>
            <if test="createUserCode != null  and createUserCode != ''"> and o.create_user_code = #{createUserCode}</if>
            <if test="updateUserCode != null  and updateUserCode != ''"> and o.update_user_code = #{updateUserCode}</if>
        </where>
    </select>
</mapper>