<?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"    /> <!-- 新增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"    />
    </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>

    <sql id="selectOutboundOrdersVo">
        select
            oo.id,
            oo.order_id,
            oo.system_no,
            oo.order_type_id,
            oo.order_type, -- 新增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
        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="OutboundOrdersResult">
        <include refid="selectOutboundOrdersVo"/>
        <if test="orderId != null  and orderId != ''"> and oo.order_id = #{orderId}</if>
        <if test="systemNo != null  and systemNo != ''"> and oo.system_no = #{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> <!-- 新增order_type查询条件 -->
        <if test="batchCode != null  and batchCode != ''"> and oo.batch_code = #{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="inboundDate != null "> and oo.inbound_date = #{inboundDate}</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>
    </select>

    <select id="selectOutboundOrdersById" parameterType="String" resultMap="OutboundOrdersOutboundOrderItemsResult">
        select
            oo.id,
            oo.order_id,
            oo.system_no,
            oo.order_type_id,
            oo.order_type, -- 新增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
        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> <!-- 新增order_type字段插入 -->
            <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>
        </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> <!-- 新增order_type值插入 -->
            <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>
        </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> <!-- 新增order_type字段更新 -->
            <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>
        </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(ooi.actual_quantity) as value
        from outbound_order_items ooi
            left join materials m on ooi.material_id = m.id
        where ooi.is_used = 1 and ooi.shipped_at &gt;= DATE_FORMAT(CURDATE(), '%Y-%m-01')
          and ooi.shipped_at &lt; DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01')
        group by m.material_name
        order by sum(ooi.actual_quantity) desc
            limit 10;
    </select>

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

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