fix(库存): 库存报表合并出入库明细并修正进出存统计

gaoluyang

6 天以前 9a30c5cc13afe9e8bea880cd39cb74470f682d10

fix(库存): 库存报表合并出入库明细并修正进出存统计

- 日报/月报查询库存流水时合并 stock_in_record 与 stock_out_record，返回 direction/出库批次/出库数量
- 进出存报表支持 startDate/endDate 日期范围
- 月报、进出存报表结束日期按当天包含（DATE_ADD 1 天）
- 进出存报表按产品型号 id 分组，避免同名型号被合并

Co-Authored-By: Claude Code <noreply@anthropic.com>

 src/main/java/com/ruoyi/stock/dto/StockInRecordDto.java

@@ -4,6 +4,8 @@
import io.swagger.v3.oas.annotations.media.Schema;
import lombok.Data;

import java.math.BigDecimal;
import java.time.LocalDate;
import java.util.List;


@@ -43,4 +45,16 @@
    @Schema(description = "源单号")
    private String sourceOrderNo;

    @Schema(description = "出入库方向：in=入库，out=出库")
    private String direction;

    @Schema(description = "出库批次")
    private String outboundBatches;

    @Schema(description = "出库数量")
    private BigDecimal stockOutNum;

    @Schema(description = "出库日期")
    private LocalDate outDate;

}

 src/main/java/com/ruoyi/stock/dto/StockInventoryDto.java

@@ -75,6 +75,14 @@
    @JsonFormat(pattern = "yyyy-MM-dd")
    private LocalDate endMonth;

    //库存进出存报表查询字段（按日期范围）
    @DateTimeFormat(pattern = "yyyy-MM-dd")
    @JsonFormat(pattern = "yyyy-MM-dd")
    private LocalDate startDate;
    @DateTimeFormat(pattern = "yyyy-MM-dd")
    @JsonFormat(pattern = "yyyy-MM-dd")
    private LocalDate endDate;

    private BigDecimal totalStockIn;
    private BigDecimal totalStockOut;
    private BigDecimal currentStock;

 src/main/resources/mapper/stock/StockInventoryMapper.xml

@@ -422,38 +422,111 @@
        group by batch_no, product_model_id, model, unit, product_name, product_id
    </select>
    <select id="stockInventoryPage" resultType="com.ruoyi.stock.dto.StockInRecordDto">
        select sir.*,si.qualitity as current_stock,
        select t.id,
        t.inbound_batches,
        t.outbound_batches,
        t.batch_no,
        t.stock_in_num,
        t.stock_out_num,
        t.record_type,
        t.record_id,
        t.product_model_id,
        t.remark,
        t.direction,
        t.out_date,
        t.create_time,
        t.create_user,
        t.type,
        si.current_stock,
        pm.model,
        pm.unit,
        p.product_name,
        su.nick_name as create_by
        from
        stock_in_record sir
        from (
            select sir.id,
                   sir.inbound_batches,
                   null as outbound_batches,
                   sir.batch_no,
                   sir.stock_in_num,
                   null as stock_out_num,
                   sir.record_type,
                   sir.record_id,
                   sir.product_model_id,
                   sir.remark,
                   'in' as direction,
                   null as out_date,
                   sir.create_time,
                   sir.create_user,
                   sir.type
            from stock_in_record sir
            <where>
                <if test="ew.reportDate != null">
                    and sir.create_time &gt;= #{ew.reportDate}
                    and sir.create_time &lt; DATE_ADD(#{ew.reportDate}, INTERVAL 1 DAY)
                </if>
                <if test="ew.startMonth != null">
                    and sir.create_time &gt;= #{ew.startMonth}
                </if>
                <if test="ew.endMonth != null">
                    and sir.create_time &lt; DATE_ADD(#{ew.endMonth}, INTERVAL 1 DAY)
                </if>
                <if test="ew.startDate != null">
                    and sir.create_time &gt;= #{ew.startDate}
                </if>
                <if test="ew.endDate != null">
                    and sir.create_time &lt; DATE_ADD(#{ew.endDate}, INTERVAL 1 DAY)
                </if>
            </where>
            union all
            select sor.id,
                   null as inbound_batches,
                   sor.outbound_batches,
                   sor.batch_no,
                   null as stock_in_num,
                   sor.stock_out_num,
                   sor.record_type,
                   sor.record_id,
                   sor.product_model_id,
                   sor.remark,
                   'out' as direction,
                   sor.out_date,
                   sor.create_time,
                   sor.create_user,
                   sor.type
            from stock_out_record sor
            <where>
                <if test="ew.reportDate != null">
                    and sor.create_time &gt;= #{ew.reportDate}
                    and sor.create_time &lt; DATE_ADD(#{ew.reportDate}, INTERVAL 1 DAY)
                </if>
                <if test="ew.startMonth != null">
                    and sor.create_time &gt;= #{ew.startMonth}
                </if>
                <if test="ew.endMonth != null">
                    and sor.create_time &lt; DATE_ADD(#{ew.endMonth}, INTERVAL 1 DAY)
                </if>
                <if test="ew.startDate != null">
                    and sor.create_time &gt;= #{ew.startDate}
                </if>
                <if test="ew.endDate != null">
                    and sor.create_time &lt; DATE_ADD(#{ew.endDate}, INTERVAL 1 DAY)
                </if>
            </where>
        ) t
        left join (
            select product_model_id, sum(qualitity) as qualitity
            select product_model_id, sum(qualitity) as current_stock
            from stock_inventory
            group by product_model_id
        ) si on sir.product_model_id = si.product_model_id
        left join product_model pm on sir.product_model_id = pm.id
        ) si on t.product_model_id = si.product_model_id
        left join product_model pm on t.product_model_id = pm.id
        left join product p on pm.product_id = p.id
        left join sys_user su on sir.create_user = su.user_id
        <where>
            <if test="ew.reportDate != null">
                and sir.create_time >= #{ew.reportDate}
                and sir.create_time &lt; DATE_ADD(#{ew.reportDate}, INTERVAL 1 DAY)
            </if>
            <if test="ew.startMonth != null">
                and sir.create_time &gt;= #{ew.startMonth}
            </if>
            <if test="ew.endMonth != null">
                and sir.create_time &lt;= #{ew.endMonth}
            </if>
        </where>
        order by sir.id desc
        left join sys_user su on t.create_user = su.user_id
        order by t.create_time desc, t.id desc
    </select>

    <select id="stockInAndOutRecord" resultType="com.ruoyi.stock.dto.StockInventoryDto">
        SELECT
        combined_data.product_model_id,
        pm.model,
        pm.unit,
        p.product_name,
@@ -483,7 +556,13 @@
                and stock_in_record.create_time &gt;= #{ew.startMonth}
            </if>
            <if test="ew.endMonth != null">
                and stock_in_record.create_time &lt;= #{ew.endMonth}
                and stock_in_record.create_time &lt; DATE_ADD(#{ew.endMonth}, INTERVAL 1 DAY)
            </if>
            <if test="ew.startDate != null">
                and stock_in_record.create_time &gt;= #{ew.startDate}
            </if>
            <if test="ew.endDate != null">
                and stock_in_record.create_time &lt; DATE_ADD(#{ew.endDate}, INTERVAL 1 DAY)
            </if>
        </where>
        GROUP BY product_model_id
@@ -502,7 +581,13 @@
                and stock_out_record.create_time &gt;= #{ew.startMonth}
            </if>
            <if test="ew.endMonth != null">
                and stock_out_record.create_time &lt;= #{ew.endMonth}
                and stock_out_record.create_time &lt; DATE_ADD(#{ew.endMonth}, INTERVAL 1 DAY)
            </if>
            <if test="ew.startDate != null">
                and stock_out_record.create_time &gt;= #{ew.startDate}
            </if>
            <if test="ew.endDate != null">
                and stock_out_record.create_time &lt; DATE_ADD(#{ew.endDate}, INTERVAL 1 DAY)
            </if>
        </where>
        GROUP BY product_model_id
@@ -518,6 +603,7 @@
            </if>
        </where>
        GROUP BY
        combined_data.product_model_id,
        pm.model,
        pm.unit,
        p.product_name