feat: 采购业务汇总统计接口

gongchunyi

11 小时以前 2c38fb7ab234af82a0575c828e7c2b696f74c441

feat: 采购业务汇总统计接口

 src/main/java/com/ruoyi/purchase/controller/ProcurementBusinessSummaryController.java

@@ -8,7 +8,7 @@
import com.ruoyi.framework.web.controller.BaseController;
import com.ruoyi.framework.web.domain.AjaxResult;
import com.ruoyi.purchase.dto.ProcurementBusinessSummaryDto;
import com.ruoyi.purchase.pojo.PurchaseLedger;
import com.ruoyi.purchase.dto.ProcurementBusinessSummaryStatisticsDto;
import com.ruoyi.purchase.service.impl.ProcurementBusinessSummaryServiceImpl;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
@@ -19,7 +19,6 @@
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletResponse;
import java.util.List;

/**
 * @author :yys
@@ -35,8 +34,15 @@
    private ProcurementBusinessSummaryServiceImpl procurementBusinessSummaryService;

    @GetMapping("/listPage")
    public AjaxResult listPage(Page page, ProcurementBusinessSummaryDto procurementBusinessSummaryDto) {
    public AjaxResult listPage(Page<?> page, ProcurementBusinessSummaryDto procurementBusinessSummaryDto) {
        return AjaxResult.success(procurementBusinessSummaryService.listPage(page, procurementBusinessSummaryDto));
    }

    @ApiOperation("采购业务汇总统计（采购总额/商品种类/退款总额）")
    @GetMapping("/statistics")
    public AjaxResult statistics(ProcurementBusinessSummaryDto procurementBusinessSummaryDto) {
        ProcurementBusinessSummaryStatisticsDto stats = procurementBusinessSummaryService.statistics(procurementBusinessSummaryDto);
        return AjaxResult.success(stats);
    }

    /**
@@ -45,7 +51,7 @@
    @Log(title = "导出采购报表", businessType = BusinessType.EXPORT)
    @PostMapping("/export")
    public void export(HttpServletResponse response, ProcurementBusinessSummaryDto procurementBusinessSummaryDto) {
        Page page = new Page(-1,-1);
        Page<?> page = new Page<>(-1, -1);
        IPage<ProcurementBusinessSummaryDto> list = procurementBusinessSummaryService.listPage(page, procurementBusinessSummaryDto);
        ExcelUtil<ProcurementBusinessSummaryDto> util = new ExcelUtil<ProcurementBusinessSummaryDto>(ProcurementBusinessSummaryDto.class);
        util.exportExcel(response, list.getRecords(), "采购报表");

 src/main/java/com/ruoyi/purchase/dto/ProcurementBusinessSummaryStatisticsDto.java

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,22 @@
package com.ruoyi.purchase.dto;

import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

import java.math.BigDecimal;

@Data
@ApiModel("采购业务数据统计")
public class ProcurementBusinessSummaryStatisticsDto {

    @ApiModelProperty("采购总额")
    private BigDecimal purchaseTotalAmount;

    @ApiModelProperty("商品品类（商品分类）")
    private Integer productCategoryCount;

    @ApiModelProperty("退货总额")
    private BigDecimal returnTotalAmount;
}


 src/main/java/com/ruoyi/purchase/service/impl/ProcurementBusinessSummaryServiceImpl.java

@@ -2,10 +2,9 @@

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.ruoyi.framework.web.domain.AjaxResult;
import com.ruoyi.purchase.dto.ProcurementBusinessSummaryDto;
import com.ruoyi.purchase.dto.ProcurementBusinessSummaryStatisticsDto;
import com.ruoyi.sales.mapper.SalesLedgerProductMapper;
import com.ruoyi.sales.pojo.SalesLedgerProduct;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
@@ -21,7 +20,11 @@
    @Autowired
    private SalesLedgerProductMapper salesLedgerProductMapper;

    public IPage<ProcurementBusinessSummaryDto> listPage(Page page, ProcurementBusinessSummaryDto procurementBusinessSummaryDto) {
    public IPage<ProcurementBusinessSummaryDto> listPage(Page<?> page, ProcurementBusinessSummaryDto procurementBusinessSummaryDto) {
        return salesLedgerProductMapper.procurementBusinessSummaryListPage(page, procurementBusinessSummaryDto);
    }

    public ProcurementBusinessSummaryStatisticsDto statistics(ProcurementBusinessSummaryDto procurementBusinessSummaryDto) {
        return salesLedgerProductMapper.procurementBusinessSummaryStatistics(procurementBusinessSummaryDto);
    }
}

 src/main/java/com/ruoyi/sales/mapper/SalesLedgerProductMapper.java

@@ -3,8 +3,8 @@
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.ruoyi.common.config.MyBaseMapper;
import com.ruoyi.production.dto.ProductStructureDto;
import com.ruoyi.purchase.dto.ProcurementBusinessSummaryDto;
import com.ruoyi.purchase.dto.ProcurementBusinessSummaryStatisticsDto;
import com.ruoyi.sales.dto.LossProductModelDto;
import com.ruoyi.sales.dto.SalesLedgerProductDto;
import com.ruoyi.sales.pojo.SalesLedgerProduct;
@@ -28,11 +28,13 @@

    SalesLedgerProduct selectSalesLedgerProductByMainId(@Param("productMainId") Long productMainId);

    IPage<SalesLedgerProductDto> listPage(Page page, @Param("req") SalesLedgerProductDto salesLedgerProduct);
    IPage<SalesLedgerProductDto> listPage(Page<?> page, @Param("req") SalesLedgerProductDto salesLedgerProduct);

    IPage<SalesLedgerProductDto> listPagePurchaseLedger(Page page, @Param("req") SalesLedgerProductDto salesLedgerProduct);
    IPage<SalesLedgerProductDto> listPagePurchaseLedger(Page<?> page, @Param("req") SalesLedgerProductDto salesLedgerProduct);

    IPage<ProcurementBusinessSummaryDto> procurementBusinessSummaryListPage(Page page, @Param("req") ProcurementBusinessSummaryDto procurementBusinessSummaryDto);
    IPage<ProcurementBusinessSummaryDto> procurementBusinessSummaryListPage(Page<?> page, @Param("req") ProcurementBusinessSummaryDto procurementBusinessSummaryDto);

    ProcurementBusinessSummaryStatisticsDto procurementBusinessSummaryStatistics(@Param("req") ProcurementBusinessSummaryDto procurementBusinessSummaryDto);

    List<LossProductModelDto> selectProductBomStructure(@Param("salesLedegerId") Long salesLedegerId);


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

@@ -104,42 +104,88 @@
        </where>
        order by slp.register_date desc
    </select>

    <select id="procurementBusinessSummaryListPage"
            resultType="com.ruoyi.purchase.dto.ProcurementBusinessSummaryDto">
        SELECT
        slp.product_category AS productCategory,
        slp.specification_model AS specificationModel,
        sl.supplier_name AS supplierName,
        SUM(slp.quantity) AS purchaseNum,
        SUM(slp.tax_inclusive_total_price) AS purchaseAmount,
        COUNT(DISTINCT slp.sales_ledger_id) AS purchaseTimes,
        <!-- å¹³å‡å•ä»· = æ€»é‡‡è´­é‡‘额/总采购数量，保留2位小数，避免除0 -->
        ROUND(IF(SUM(slp.quantity) = 0, 0, SUM(slp.tax_inclusive_total_price) / SUM(slp.quantity)), 2) AS averagePrice,
        <!-- è¯¥äº§å“å¤§ç±»ä¸‹æœ€åŽä¸€ä¸ªå½•å…¥æ—¥æœŸï¼ˆå–台账主表的entry_date） -->
        MAX(sl.entry_date) AS entryDate,
        COALESCE(NULLIF(SUM(t1.return_quantity), 0), 0) AS return_quantity,
        COALESCE(SUM(t2.total_amount), 0) AS return_amount,
        pm.thickness
            slp.product_category AS productCategory,
            slp.specification_model AS specificationModel,
            sl.supplier_name AS supplierName,
            SUM(slp.quantity) AS purchaseNum,
            SUM(slp.tax_inclusive_total_price) AS purchaseAmount,
            COUNT(DISTINCT slp.sales_ledger_id) AS purchaseTimes,
            <!-- å¹³å‡å•ä»· = æ€»é‡‡è´­é‡‘额/总采购数量，保留2位小数，避免除0 -->
            ROUND(IF(SUM(slp.quantity) = 0, 0, SUM(slp.tax_inclusive_total_price) / SUM(slp.quantity)), 2) AS averagePrice,
            <!-- è¯¥åˆ†ç»„下最后一个录入日期（取台账主表的entry_date） -->
            MAX(sl.entry_date) AS entryDate,
            COALESCE(SUM(rop.return_quantity), 0) AS return_quantity,
            COALESCE(SUM(rop.allocated_return_amount), 0) AS return_amount,
            pm.thickness
        FROM sales_ledger_product slp
        <!-- å…³è”台账主表：获取录入日期entry_date -->
        LEFT JOIN purchase_ledger sl ON slp.sales_ledger_id = sl.id
        left join purchase_return_order_products as t1 on t1.sales_ledger_product_id = slp.id
        left join purchase_return_orders as t2 on t2.id = t1.purchase_return_order_id
        left join product_model pm on pm.id = slp.product_model_id
            <!-- å…³è”台账主表：获取录入日期entry_date -->
            LEFT JOIN purchase_ledger sl ON slp.sales_ledger_id = sl.id
            LEFT JOIN (
                SELECT
                    t1.sales_ledger_product_id,
                    t1.return_quantity,
                    IF(q.sum_qty = 0 OR q.sum_qty IS NULL, 0, (t2.total_amount * (t1.return_quantity / q.sum_qty))) AS allocated_return_amount
                FROM purchase_return_order_products t1
                    INNER JOIN purchase_return_orders t2 ON t2.id = t1.purchase_return_order_id
                    INNER JOIN (
                        SELECT purchase_return_order_id, SUM(return_quantity) AS sum_qty
                        FROM purchase_return_order_products
                        GROUP BY purchase_return_order_id
                    ) q ON q.purchase_return_order_id = t1.purchase_return_order_id
            ) rop ON rop.sales_ledger_product_id = slp.id
            LEFT JOIN product_model pm ON pm.id = slp.product_model_id
        WHERE slp.type = 2 <!-- å›ºå®šç­›é€‰ï¼šé‡‡è´­å°è´¦ï¼ˆtype=2） -->
        <!-- é‡‡è´­æ—¥æœŸç­›é€‰ï¼šå¯é€‰æ¡ä»¶ -->
        <if test="req.entryDateStart != null and req.entryDateEnd != null">
            AND sl.entry_date BETWEEN #{req.entryDateStart} AND #{req.entryDateEnd} <!-- æ—¶é—´èŒƒå›´ï¼šéžç©ºæœ‰æ•ˆ -->
        </if>
        <!-- äº§å“å¤§ç±»ç­›é€‰ï¼šå¯é€‰æ¡ä»¶ -->
        <if test="req.productCategory != null and req.productCategory != ''">
            AND slp.product_category = #{req.productCategory}
        </if>
        <!-- æŒ‰äº§å“å¤§ç±»åˆ†ç»„聚合 -->
        GROUP BY slp.product_category
        <!-- æŒ‰äº§å“å¤§ç±»æŽ’序 -->
        ORDER BY slp.product_category
            <!-- é‡‡è´­æ—¥æœŸç­›é€‰ï¼šå¯é€‰æ¡ä»¶ -->
            <if test="req.entryDateStart != null and req.entryDateEnd != null">
                AND sl.entry_date BETWEEN #{req.entryDateStart} AND #{req.entryDateEnd} <!-- æ—¶é—´èŒƒå›´ï¼šéžç©ºæœ‰æ•ˆ -->
            </if>
            <!-- äº§å“å¤§ç±»ç­›é€‰ï¼šå¯é€‰æ¡ä»¶ -->
            <if test="req.productCategory != null and req.productCategory != ''">
                AND slp.product_category = #{req.productCategory}
            </if>
        <!-- æŒ‰åˆ†ç»„维度聚合（避免 ONLY_FULL_GROUP_BY é”™è¯¯ä¸Žä¸ç¡®å®šå€¼ï¼‰ -->
        GROUP BY
            slp.product_category,
            slp.specification_model,
            sl.supplier_name,
            pm.thickness
        ORDER BY
            slp.product_category
    </select>

    <select id="procurementBusinessSummaryStatistics"
            resultType="com.ruoyi.purchase.dto.ProcurementBusinessSummaryStatisticsDto">
        SELECT
            COALESCE(SUM(slp.tax_inclusive_total_price), 0) AS purchaseTotalAmount,
            COALESCE(COUNT(DISTINCT slp.product_category), 0) AS productCategoryCount,
            COALESCE(SUM(rop.allocated_return_amount), 0) AS returnTotalAmount
        FROM sales_ledger_product slp
            LEFT JOIN purchase_ledger sl ON slp.sales_ledger_id = sl.id
            LEFT JOIN (
                SELECT
                    t1.sales_ledger_product_id,
                    IF(q.sum_qty = 0 OR q.sum_qty IS NULL, 0, (t2.total_amount * (t1.return_quantity / q.sum_qty))) AS allocated_return_amount
                FROM purchase_return_order_products t1
                    INNER JOIN purchase_return_orders t2 ON t2.id = t1.purchase_return_order_id
                    INNER JOIN (
                        SELECT purchase_return_order_id, SUM(return_quantity) AS sum_qty
                        FROM purchase_return_order_products
                        GROUP BY purchase_return_order_id
                    ) q ON q.purchase_return_order_id = t1.purchase_return_order_id
            ) rop ON rop.sales_ledger_product_id = slp.id
        WHERE slp.type = 2
            <if test="req.entryDateStart != null and req.entryDateEnd != null">
                AND sl.entry_date BETWEEN #{req.entryDateStart} AND #{req.entryDateEnd}
            </if>
            <if test="req.productCategory != null and req.productCategory != ''">
                AND slp.product_category = #{req.productCategory}
            </if>
    </select>

    <select id="selectProductBomStructure" resultType="com.ruoyi.sales.dto.LossProductModelDto">
        select
            a.model,