编辑采购台账报错异常+供应商往来查询入库金额sql调整优化

zss

4 天以前 ea531a6f0efba09ca671fee7e406e42955d1f0d1

编辑采购台账报错异常+供应商往来查询入库金额sql调整优化

 src/main/java/com/ruoyi/sales/service/impl/SalesLedgerProductServiceImpl.java

@@ -215,22 +215,20 @@
     */
    public void addProductionData(SalesLedgerProduct salesLedgerProduct) {
        //先判断该产品是否需要生产
        if (!salesLedgerProduct.getIsProduction()) {
            return;
        if (salesLedgerProduct.getIsProduction()) {
            SalesLedger salesLedger = salesLedgerMapper.selectById(salesLedgerProduct.getSalesLedgerId());
            ProductionPlan productionPlan = new ProductionPlan();
            productionPlan.setSalesLedgerId(salesLedgerProduct.getSalesLedgerId());
            productionPlan.setSalesLedgerProductId(salesLedgerProduct.getId());
            productionPlan.setMpsNo(generateNextPlanNo(LocalDate.now().format(DateTimeFormatter.ofPattern("yyyyMMdd"))));
            productionPlan.setProductModelId(salesLedgerProduct.getProductModelId());
            productionPlan.setQtyRequired(salesLedgerProduct.getQuantity());
            productionPlan.setSource("销售");
            productionPlan.setStatus(0);
            productionPlan.setRequiredDate(salesLedger.getDeliveryDate());//需求日期=交货日期
            productionPlan.setPromisedDeliveryDate(salesLedger.getDeliveryDate());//承诺日期=交货日期
            productionPlanMapper.insert(productionPlan);
        }
        SalesLedger salesLedger = salesLedgerMapper.selectById(salesLedgerProduct.getSalesLedgerId());
        ProductionPlan productionPlan = new ProductionPlan();
        productionPlan.setSalesLedgerId(salesLedgerProduct.getSalesLedgerId());
        productionPlan.setSalesLedgerProductId(salesLedgerProduct.getId());
        productionPlan.setMpsNo(generateNextPlanNo(LocalDate.now().format(DateTimeFormatter.ofPattern("yyyyMMdd"))));
        productionPlan.setProductModelId(salesLedgerProduct.getProductModelId());
        productionPlan.setQtyRequired(salesLedgerProduct.getQuantity());
        productionPlan.setSource("销售");
        productionPlan.setStatus(0);
        productionPlan.setRequiredDate(salesLedger.getDeliveryDate());//需求日期=交货日期
        productionPlan.setPromisedDeliveryDate(salesLedger.getDeliveryDate());//承诺日期=交货日期
        productionPlanMapper.insert(productionPlan);

    }

    /**

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

@@ -68,7 +68,7 @@
            </if>
        </where>
    </select>
    

    <select id="supplierTransactions" resultType="com.ruoyi.purchase.vo.SupplierTransactionsVo">
        SELECT T1.supplier_id,
               sm.supplier_name,
@@ -86,12 +86,26 @@
                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
                UNION ALL
                SELECT sir.stock_in_num * slp.tax_inclusive_unit_price AS inbound_amount, pl.supplier_id
                FROM stock_in_record sir
                INNER JOIN quality_inspect qi ON qi.id = sir.record_id
                INNER JOIN purchase_ledger pl ON pl.id = qi.purchase_ledger_id
                INNER JOIN sales_ledger_product slp ON slp.sales_ledger_id = pl.id AND slp.product_model_id = sir.product_model_id
                WHERE sir.approval_status = 1 AND sir.record_type = 10 AND slp.type = 2
                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_inspect qi
                                   ON qi.id = sir.record_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 = 10
            ) t
            GROUP BY t.supplier_id
        ) T3 ON T3.supplier_id = T1.supplier_id
@@ -121,12 +135,26 @@
               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
               UNION ALL
               SELECT sir.stock_in_num * slp.tax_inclusive_unit_price AS inbound_amount, slp.sales_ledger_id
               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_inspect qi ON qi.id = sir.record_id
               INNER JOIN purchase_ledger pl2 ON pl2.id = qi.purchase_ledger_id
               INNER JOIN sales_ledger_product slp ON slp.sales_ledger_id = pl2.id AND slp.product_model_id = sir.product_model_id
               WHERE sir.approval_status = 1 AND sir.record_type = 10 AND slp.type = 2
                        INNER JOIN quality_inspect qi
                                   ON qi.id = sir.record_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 = 10
           ) t
           GROUP BY t.sales_ledger_id
       ) T2 ON T2.sales_ledger_id = pl.id