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

    <resultMap id="InboundOutboundStatisticsResult" type="com.ruoyi.inventory.domain.vo.InboundOutboundStatisticsVO">
        <result column="material_id" property="materialId"/>
        <result column="material_name" property="materialName"/>
        <result column="inbound_count" property="inboundCount"/>
        <result column="outbound_count" property="outboundCount"/>
        <result column="inbound_quantity" property="inboundQuantity"/>
        <result column="outbound_quantity" property="outboundQuantity"/>
        <result column="inbound_amount" property="inboundAmount"/>
        <result column="outbound_amount" property="outboundAmount"/>
        <result column="amount_diff" property="amountDiff"/>
    </resultMap>

    <select id="selectInboundOutboundStatistics"
            parameterType="com.ruoyi.inventory.domain.vo.InboundOutboundStatisticsVO"
            resultMap="InboundOutboundStatisticsResult">
        SELECT
            t.material_id,
            IFNULL(m.material_name, t.material_id) AS material_name,
            SUM(t.inbound_count) AS inbound_count,
            SUM(t.outbound_count) AS outbound_count,
            SUM(t.inbound_quantity) AS inbound_quantity,
            SUM(t.outbound_quantity) AS outbound_quantity,
            SUM(t.inbound_amount) AS inbound_amount,
            SUM(t.outbound_amount) AS outbound_amount,
            SUM(t.inbound_amount) - SUM(t.outbound_amount) AS amount_diff
        FROM (
                 SELECT
                     i.material_id,
                     COUNT(DISTINCT o.id)                        AS inbound_count,
                     0                                           AS outbound_count,
                     IFNULL(SUM(IFNULL(i.actual_quantity, 0)),0) AS inbound_quantity,
                     0                                           AS outbound_quantity,
                     IFNULL(SUM(IFNULL(i.unit_price, 0) * IFNULL(i.actual_quantity, 0)),0) AS inbound_amount,
                     0                                           AS outbound_amount
                 FROM inbound_order_items i
                          LEFT JOIN inbound_orders o ON i.inbound_order_id = o.id
                          LEFT JOIN warehouses w ON w.id = i.warehouse_id
                          LEFT JOIN materials m1 ON m1.id = i.material_id
                 <where>
                     i.is_used = 1
                     <if test="warehouseId != null and warehouseId != ''">
                         AND i.warehouse_id = #{warehouseId}
                     </if>
                     <if test="locationId != null and locationId != ''">
                         AND i.location_id = #{locationId}
                     </if>
                     <if test="materialId != null and materialId != ''">
                         AND (i.material_id = #{materialId}
                             OR m1.material_code LIKE CONCAT('%', #{materialId}, '%')
                             OR m1.material_name LIKE CONCAT('%', #{materialId}, '%'))
                     </if>
                     <if test="warehousesCode != null and warehousesCode != ''">
                         AND w.warehouses_code LIKE CONCAT('%', #{warehousesCode}, '%')
                     </if>
                     <if test="startDate != null">
                         AND o.inbound_date &gt;= #{startDate}
                     </if>
                     <if test="endDate != null">
                         AND o.inbound_date &lt; DATE_ADD(#{endDate}, INTERVAL 1 DAY)
                     </if>
                 </where>
                 GROUP BY i.material_id
                 UNION ALL
                 SELECT
                     oi.material_id,
                     0                                            AS inbound_count,
                     COUNT(DISTINCT oo.id)                        AS outbound_count,
                     0                                            AS inbound_quantity,
                     IFNULL(SUM(IFNULL(oi.actual_quantity, 0)),0) AS outbound_quantity,
                     0                                            AS inbound_amount,
                     IFNULL(SUM(IFNULL(oi.unit_price, 0) * IFNULL(oi.actual_quantity, 0)),0) AS outbound_amount
                 FROM outbound_order_items oi
                          LEFT JOIN outbound_orders oo ON oi.outbound_order_id = oo.id
                          LEFT JOIN warehouses w2 ON w2.id = oi.warehouse_id
                          LEFT JOIN materials m2 ON m2.id = oi.material_id
                 <where>
                     oi.is_used = 1
                     <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="materialId != null and materialId != ''">
                         AND (oi.material_id = #{materialId}
                             OR m2.material_code LIKE CONCAT('%', #{materialId}, '%')
                             OR m2.material_name LIKE CONCAT('%', #{materialId}, '%'))
                     </if>
                     <if test="warehousesCode != null and warehousesCode != ''">
                         AND w2.warehouses_code LIKE CONCAT('%', #{warehousesCode}, '%')
                     </if>
                     <if test="startDate != null">
                         AND oo.inbound_date &gt;= #{startDate}
                     </if>
                     <if test="endDate != null">
                         AND oo.inbound_date &lt; DATE_ADD(#{endDate}, INTERVAL 1 DAY)
                     </if>
                 </where>
                 GROUP BY oi.material_id
             ) t
                 LEFT JOIN materials m ON t.material_id = m.id
        GROUP BY t.material_id, m.material_name
        ORDER BY m.material_name ASC
    </select>
</mapper>

