供应商往来和采购台账查询库存数据优化

zss

2026-06-01 b43cffab6311b377628528a2e1c8b3eae14b676d

供应商往来和采购台账查询库存数据优化

 src/main/resources/mapper/basic/SupplierManageMapper.xml

@@ -80,12 +80,22 @@
            SELECT t.supplier_id,
                   SUM(t.inbound_amount) AS InboundAmount
            FROM (
                SELECT sir.stock_in_num * slp.tax_inclusive_unit_price AS inbound_amount, pl.supplier_id
                SELECT sir.stock_in_num * aa.tax_inclusive_unit_price AS inbound_amount, pl.supplier_id
                FROM stock_in_record sir
                INNER JOIN sales_ledger_product slp ON slp.id = sir.record_id
                INNER JOIN purchase_ledger pl ON pl.id = slp.sales_ledger_id
                WHERE sir.approval_status = 1 AND sir.record_type = 7 AND slp.type = 2
                INNER JOIN  (SELECT
                       sales_ledger_id,
                       product_model_id,
                       MIN(tax_inclusive_unit_price) AS tax_inclusive_unit_price
                   FROM sales_ledger_product
                   WHERE type = 2
                   GROUP BY sales_ledger_id, product_model_id) aa
                    ON aa.sales_ledger_id = sir.record_id
                           AND aa.product_model_id=sir.product_model_id
                INNER JOIN purchase_ledger pl ON pl.id = aa.sales_ledger_id
                WHERE sir.approval_status = 1 AND sir.record_type = 7

                UNION ALL

                SELECT
                   sir.stock_in_num * slp_agg.tax_inclusive_unit_price AS inbound_amount,
                   pl2.supplier_id
@@ -106,6 +116,31 @@
                                   ON slp_agg.sales_ledger_id = pl2.id
                                       AND slp_agg.product_model_id = sir.product_model_id
               WHERE sir.approval_status = 1 AND sir.record_type = 10

                UNION ALL

                SELECT
                   sir.stock_in_num * slp_agg.tax_inclusive_unit_price AS inbound_amount,
                   pl2.supplier_id
               FROM stock_in_record sir
                        INNER JOIN quality_unqualified qu
                                   ON qu.id = sir.record_id
                        INNER JOIN quality_inspect qi
                                   ON qi.id = qu.inspect_id
                        INNER JOIN purchase_ledger pl2
                                   ON pl2.id = qi.purchase_ledger_id
                        INNER JOIN (
                   SELECT
                       sales_ledger_id,
                       product_model_id,
                       MIN(tax_inclusive_unit_price) AS tax_inclusive_unit_price
                   FROM sales_ledger_product
                   WHERE type = 2
                   GROUP BY sales_ledger_id, product_model_id
               ) slp_agg
                                   ON slp_agg.sales_ledger_id = pl2.id
                                       AND slp_agg.product_model_id = sir.product_model_id
               WHERE sir.approval_status = 1 AND sir.record_type = 4
            ) t
            GROUP BY t.supplier_id
        ) T3 ON T3.supplier_id = T1.supplier_id
@@ -130,11 +165,22 @@
           SELECT t.sales_ledger_id,
                  SUM(t.inbound_amount) AS InboundAmount
           FROM (
               SELECT sir.stock_in_num * slp.tax_inclusive_unit_price AS inbound_amount, slp.sales_ledger_id
               SELECT sir.stock_in_num * aa.tax_inclusive_unit_price AS inbound_amount, aa.sales_ledger_id
               FROM stock_in_record sir
               INNER JOIN sales_ledger_product slp ON slp.id = sir.record_id
               WHERE sir.approval_status = 1 AND sir.record_type = 7 AND slp.type = 2
               INNER JOIN
                    (SELECT
                         sales_ledger_id,
                         product_model_id,
                         MIN(tax_inclusive_unit_price) AS tax_inclusive_unit_price
                     FROM sales_ledger_product
                     WHERE type = 2
                     GROUP BY sales_ledger_id, product_model_id) aa
                       ON aa.sales_ledger_id = sir.record_id
                              AND aa.product_model_id=sir.product_model_id
               WHERE sir.approval_status = 1 AND sir.record_type = 7

               UNION ALL

               SELECT
                   sir.stock_in_num * slp_agg.tax_inclusive_unit_price AS inbound_amount,
                   slp_agg.sales_ledger_id
@@ -155,6 +201,31 @@
                                   ON slp_agg.sales_ledger_id = pl2.id
                                       AND slp_agg.product_model_id = sir.product_model_id
               WHERE sir.approval_status = 1 AND sir.record_type = 10

               UNION ALL

               SELECT
                   sir.stock_in_num * slp_agg.tax_inclusive_unit_price AS inbound_amount,
                   slp_agg.sales_ledger_id
               FROM stock_in_record sir
                        INNER JOIN quality_unqualified qu
                                   ON qu.id = sir.record_id
                        INNER JOIN quality_inspect qi
                                   ON qi.id = qu.inspect_id
                        INNER JOIN purchase_ledger pl2
                                   ON pl2.id = qi.purchase_ledger_id
                        INNER JOIN (
                   SELECT
                       sales_ledger_id,
                       product_model_id,
                       MIN(tax_inclusive_unit_price) AS tax_inclusive_unit_price
                   FROM sales_ledger_product
                   WHERE type = 2
                   GROUP BY sales_ledger_id, product_model_id
               ) slp_agg
                                   ON slp_agg.sales_ledger_id = pl2.id
                                       AND slp_agg.product_model_id = sir.product_model_id
               WHERE sir.approval_status = 1 AND sir.record_type = 4
           ) t
           GROUP BY t.sales_ledger_id
       ) T2 ON T2.sales_ledger_id = pl.id

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

@@ -88,6 +88,22 @@
                                AND slp.sales_ledger_id = qi.purchase_ledger_id
                                AND slp.product_model_id = qi.product_model_id
                            WHERE sir.approval_status = 1

                            UNION ALL

                            SELECT slp.id AS sales_ledger_product_id,
                                   sir.stock_in_num
                            FROM stock_in_record sir
                            INNER JOIN quality_unqualified qu
                                ON TRIM(sir.record_type) = '4'
                                AND sir.record_id = qu.id
                            INNER JOIN quality_inspect qi
                                ON qi.id = qu.inspect_id
                            INNER JOIN sales_ledger_product slp
                                ON slp.type = 2
                                AND slp.sales_ledger_id = qi.purchase_ledger_id
                                AND slp.product_model_id = qi.product_model_id
                            WHERE sir.approval_status = 1
                        ) rel
                        GROUP BY rel.sales_ledger_product_id
                    ) approved_qty ON approved_qty.sales_ledger_product_id = slp.id

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

@@ -89,6 +89,22 @@
                AND slp.sales_ledger_id = qi.purchase_ledger_id
                AND slp.product_model_id = qi.product_model_id
            WHERE sir.approval_status = 1

            UNION ALL

            SELECT slp.id AS sales_ledger_product_id,
                   sir.stock_in_num
            FROM stock_in_record sir
            INNER JOIN quality_unqualified qu
                ON TRIM(sir.record_type) = '4'
                AND sir.record_id = qu.id
            INNER JOIN quality_inspect qi
                ON qi.id = qu.inspect_id
            INNER JOIN sales_ledger_product slp
                ON slp.type = 2
                AND slp.sales_ledger_id = qi.purchase_ledger_id
                AND slp.product_model_id = qi.product_model_id
            WHERE sir.approval_status = 1
        ) rel
        GROUP BY rel.sales_ledger_product_id
        ) t4 ON t4.sales_ledger_product_id = T1.id