采购退货时，对应批次库存已被销售发货出去一批（比如销售发货时，选择的采购入库的批次库存，剩下38个，但采购退货时，可退货数量还是显示150个。）

huminmin

18 小时以前 4f5133a1aa9ae7842fa808be6d082a036e408f12

采购退货时，对应批次库存已被销售发货出去一批（比如销售发货时，选择的采购入库的批次库存，剩下38个，但采购退货时，可退货数量还是显示150个。）

 src/main/java/com/ruoyi/purchase/vo/PurchaseReturnOrderProductsDetailVo.java

@@ -47,6 +47,9 @@
    @Schema(description = "已退货数量")
    private BigDecimal totalReturnNum;

    @Schema(description = "销售发货数量")
    private BigDecimal saleOutQuantity;

    @Schema(description = "含税单价")
    private BigDecimal taxInclusiveUnitPrice;


 src/main/java/com/ruoyi/purchase/vo/PurchaseStockInProductVo.java

@@ -42,6 +42,9 @@
    @Schema(description = "退货总数")
    private BigDecimal totalReturnNum;

    @Schema(description = "销售发货数量")
    private BigDecimal saleOutQuantity;

    @Schema(description = "含税单价")
    private BigDecimal taxInclusiveUnitPrice;


 src/main/java/com/ruoyi/sales/controller/SalesLedgerProductController.java

@@ -55,6 +55,8 @@
                    availableQuality = item.getApprovedStockInNum() == null
                            ? java.math.BigDecimal.ZERO
                            : item.getApprovedStockInNum();
                    availableQuality = availableQuality
                            .subtract(item.getShippedQuantity() == null ? java.math.BigDecimal.ZERO : item.getShippedQuantity());
                }
                item.setAvailableQuality(availableQuality.subtract(returnQuantity));
            });

 src/main/java/com/ruoyi/sales/pojo/SalesLedgerProduct.java

@@ -187,6 +187,10 @@
    private BigDecimal returnQuantity;

    @TableField(exist = false)
    @Schema(description = "销售发货数量")
    private BigDecimal shippedQuantity;

    @TableField(exist = false)
    @Schema(description = "已审核入库数量")
    private BigDecimal approvedStockInNum;


 src/main/resources/mapper/purchase/PurchaseReturnOrdersMapper.xml

@@ -100,8 +100,14 @@
            sir.stock_in_num,
            sir.batch_no,
            slp.tax_inclusive_unit_price,
            GREATEST(sir.stock_in_num - COALESCE(rs.total_return_num, 0), 0) AS un_quantity,
            COALESCE(rs.total_return_num, 0) AS total_return_num
            GREATEST(
                sir.stock_in_num
                - COALESCE(rs.total_return_num, 0)
                - COALESCE(so.total_sale_out_num, 0),
                0
            ) AS un_quantity,
            COALESCE(rs.total_return_num, 0) AS total_return_num,
            COALESCE(so.total_sale_out_num, 0) AS saleOutQuantity
            FROM stock_in_record sir
            LEFT JOIN quality_inspect qi ON sir.record_type = 10 AND sir.record_id = qi.id
            LEFT JOIN purchase_ledger pl
@@ -119,6 +125,23 @@
                WHERE 1=1
                GROUP BY stock_in_record_id
            ) rs ON rs.stock_in_record_id = sir.id
            LEFT JOIN (
                SELECT
                    inv.batch_no,
                    inv.product_model_id,
                    SUM(spd.quantity) AS total_sale_out_num
                FROM shipping_product_detail spd
                INNER JOIN shipping_info si
                    ON si.id = spd.shipping_info_id
                INNER JOIN stock_out_record sor
                    ON sor.record_id = si.id
                   AND sor.record_type = '13'
                   AND sor.approval_status IN (0, 1, 3)
                INNER JOIN stock_inventory inv
                    ON inv.id = spd.stock_inventory_id
                GROUP BY inv.batch_no, inv.product_model_id
            ) so ON so.product_model_id = sir.product_model_id
               AND (so.batch_no = sir.batch_no OR (so.batch_no IS NULL AND sir.batch_no IS NULL))
        WHERE sir.approval_status = 1
        AND sir.record_type IN ('7','10')
         and pl.id = #{purchaseLedgerId}
@@ -138,8 +161,14 @@
           slp.tax_inclusive_unit_price,
           prop.return_quantity,
           prop.purchase_return_order_id,
           GREATEST(sir.stock_in_num - COALESCE(rs1.total_return_num1, 0), 0) AS un_quantity,
           COALESCE(rs.total_return_num, 0)                             AS total_return_num
           GREATEST(
               sir.stock_in_num
               - COALESCE(rs1.total_return_num1, 0)
               - COALESCE(so.total_sale_out_num, 0),
               0
           ) AS un_quantity,
           COALESCE(rs.total_return_num, 0)                             AS total_return_num,
           COALESCE(so.total_sale_out_num, 0)                           AS saleOutQuantity
    from purchase_return_order_products prop
    left join purchase_return_orders pro on prop.purchase_return_order_id = pro.id
    LEFT JOIN stock_in_record sir ON prop.stock_in_record_id = sir.id and sir.record_type in ('7','10')
@@ -154,6 +183,23 @@
               FROM purchase_return_order_products
               WHERE 1 = 1 and purchase_return_order_id = #{id}
               GROUP BY stock_in_record_id) rs1 ON rs1.stock_in_record_id = sir.id
    LEFT JOIN (
        SELECT
            inv.batch_no,
            inv.product_model_id,
            SUM(spd.quantity) AS total_sale_out_num
        FROM shipping_product_detail spd
        INNER JOIN shipping_info si
            ON si.id = spd.shipping_info_id
        INNER JOIN stock_out_record sor
            ON sor.record_id = si.id
           AND sor.record_type = '13'
           AND sor.approval_status IN (0, 1, 3)
        INNER JOIN stock_inventory inv
            ON inv.id = spd.stock_inventory_id
        GROUP BY inv.batch_no, inv.product_model_id
    ) so ON so.product_model_id = sir.product_model_id
       AND (so.batch_no = sir.batch_no OR (so.batch_no IS NULL AND sir.batch_no IS NULL))
    where pro.id = #{id}
    </select>
</mapper>

 src/main/resources/mapper/sales/SalesLedgerProductMapper.xml

@@ -34,6 +34,7 @@
        ELSE 0
        END as has_sufficient_stock,
        (IFNULL(T1.quantity, 0) - IFNULL(t3.shipped_quantity, 0) - IFNULL(t5.pending_approval_quantity, 0)) as no_quantity,
        IFNULL(t3.shipped_quantity, 0) as shipped_quantity,
        IFNULL(t6.return_quantity, 0) as return_quantity,
        IFNULL(t4.approved_stock_in_num, 0) as approved_stock_in_num,
        IFNULL(t5.pending_approval_quantity, 0) as pending_approval_quantity,
@@ -57,17 +58,44 @@
        GROUP BY product_model_id
        ) t2 ON T1.product_model_id = t2.product_model_id
        LEFT JOIN (
        SELECT si.sales_ledger_product_id, IFNULL(SUM(spd.quantity), 0) as shipped_quantity
        FROM shipping_info si
        INNER JOIN shipping_product_detail spd ON si.id = spd.shipping_info_id
        WHERE si.status IN ('审核通过', '已发货')
          AND EXISTS (
            SELECT 1 FROM stock_out_record sor
            WHERE sor.record_id = si.id
              AND TRIM(sor.record_type) = '13'
              AND sor.approval_status = 1
          )
        GROUP BY si.sales_ledger_product_id
        SELECT
            slp.id AS sales_ledger_product_id,
            IFNULL(SUM(so.total_sale_out_num), 0) AS shipped_quantity
        FROM sales_ledger_product slp
        LEFT JOIN (
            SELECT
                pl.id AS purchase_ledger_id,
                sir.product_model_id,
                SUM(spd.quantity) AS total_sale_out_num
            FROM shipping_product_detail spd
            INNER JOIN shipping_info si
                ON si.id = spd.shipping_info_id
            INNER JOIN stock_out_record sor
                ON sor.record_id = si.id
               AND TRIM(sor.record_type) = '13'
               AND sor.approval_status IN (0, 1, 3)
            INNER JOIN stock_inventory inv
                ON inv.id = spd.stock_inventory_id
            INNER JOIN stock_in_record sir
                ON sir.product_model_id = inv.product_model_id
               AND (
                    (sir.batch_no IS NOT NULL AND sir.batch_no = inv.batch_no)
                    OR (sir.batch_no IS NULL AND inv.batch_no IS NULL)
               )
               AND sir.approval_status = 1
               AND sir.record_type IN ('7', '10')
            LEFT JOIN quality_inspect qi
                ON sir.record_type = '10'
               AND sir.record_id = qi.id
            LEFT JOIN purchase_ledger pl
                ON pl.id = IF(sir.record_type = '7', sir.record_id, qi.purchase_ledger_id)
            WHERE si.status IN ('审核通过', '已发货')
              AND pl.id IS NOT NULL
            GROUP BY pl.id, sir.product_model_id
        ) so ON so.purchase_ledger_id = slp.sales_ledger_id
            AND so.product_model_id = slp.product_model_id
        WHERE slp.type = 2
        GROUP BY slp.id
        ) t3 ON t3.sales_ledger_product_id = T1.id
        LEFT JOIN (
        SELECT rel.sales_ledger_product_id,
@@ -116,14 +144,13 @@
        GROUP BY si.sales_ledger_product_id
        ) t5 ON t5.sales_ledger_product_id = T1.id
        LEFT JOIN (
        SELECT
            si.sales_ledger_product_id,
            SUM( CASE WHEN rsp.STATUS = 1 AND sir.approval_status = 1 THEN rsp.num ELSE 0 END ) AS return_quantity
            FROM shipping_info si
            left join return_management rm on rm.shipping_id = si.id
            left join return_sale_product rsp on rm.id = rsp.return_management_id
            left join stock_in_record sir on rsp.id = sir.record_id
            GROUP BY si.sales_ledger_product_id
            SELECT
                prop.sales_ledger_product_id,
                IFNULL(SUM(prop.return_quantity), 0) AS return_quantity
            FROM purchase_return_order_products prop
            LEFT JOIN purchase_return_orders pro
                ON pro.id = prop.purchase_return_order_id
            GROUP BY prop.sales_ledger_product_id
        ) t6 ON t6.sales_ledger_product_id = T1.id
        left join product_model pm ON T1.product_model_id = pm.id
        left join product p ON pm.product_id = p.id