feat(purchase): 新增供应商往来及往来明细接口功能

zss

6 天以前 3ac318e2f614e8c57a0a9a6d175d2909ba1dbc21

feat(purchase): 新增供应商往来及往来明细接口功能

 src/main/java/com/ruoyi/basic/mapper/SupplierManageMapper.java

@@ -6,6 +6,8 @@
import com.ruoyi.basic.dto.SupplierManageDto;
import com.ruoyi.basic.excel.SupplierManageExcelDto;
import com.ruoyi.basic.pojo.SupplierManage;
import com.ruoyi.purchase.vo.SupplierTransactionsDetailsVo;
import com.ruoyi.purchase.vo.SupplierTransactionsVo;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

@@ -17,4 +19,8 @@
    IPage<SupplierManage> supplierListPage(Page page, @Param("supplierManageDto") SupplierManageDto supplierManageDto);

    List<SupplierManageExcelDto> supplierExportList(@Param("supplierManageDto") SupplierManageDto supplierManageDto);

    IPage<SupplierTransactionsVo> supplierTransactions(Page page, @Param("supplierName") String supplierName);

    IPage<SupplierTransactionsDetailsVo> supplierTransactionsDetails(Page page, @Param("supplierId") Long supplierId);
}

 src/main/java/com/ruoyi/basic/service/ISupplierService.java

@@ -5,10 +5,11 @@
import com.baomidou.mybatisplus.extension.service.IService;
import com.ruoyi.basic.dto.SupplierManageDto;
import com.ruoyi.basic.pojo.SupplierManage;
import com.ruoyi.framework.web.domain.AjaxResult;
import com.ruoyi.purchase.vo.SupplierTransactionsDetailsVo;
import com.ruoyi.purchase.vo.SupplierTransactionsVo;
import jakarta.servlet.http.HttpServletResponse;
import org.springframework.web.multipart.MultipartFile;

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

public interface ISupplierService extends IService<SupplierManage> {
@@ -57,4 +58,20 @@
    void supplierExport(HttpServletResponse response, SupplierManageDto supplierManageDto);

    Boolean importData(MultipartFile file);

    /**
     * ä¾›åº”商往来
     * @param page
     * @param supplierName
     * @return
     */
    IPage<SupplierTransactionsVo> supplierTransactions(Page page, String supplierName);

    /**
     * ä¾›åº”商往来详情
     * @param page
     * @param supplierId
     * @return
     */
    IPage<SupplierTransactionsDetailsVo> supplierTransactionsDetails(Page page, Long supplierId);
}

 src/main/java/com/ruoyi/basic/service/impl/SupplierServiceImpl.java

@@ -14,6 +14,8 @@
import com.ruoyi.common.utils.poi.ExcelUtil;
import com.ruoyi.purchase.mapper.PurchaseLedgerMapper;
import com.ruoyi.purchase.pojo.PurchaseLedger;
import com.ruoyi.purchase.vo.SupplierTransactionsDetailsVo;
import com.ruoyi.purchase.vo.SupplierTransactionsVo;
import jakarta.servlet.http.HttpServletResponse;
import lombok.RequiredArgsConstructor;
import org.springframework.beans.BeanUtils;
@@ -130,4 +132,14 @@
        }
        return false;
    }

    @Override
    public IPage<SupplierTransactionsVo> supplierTransactions(Page page, String supplierName) {
        return supplierMapper.supplierTransactions(page,supplierName);
    }

    @Override
    public IPage<SupplierTransactionsDetailsVo> supplierTransactionsDetails(Page page, Long supplierId) {
        return supplierMapper.supplierTransactionsDetails(page,supplierId);
    }
}

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

@@ -1,9 +1,11 @@
package com.ruoyi.purchase.controller;

import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.ruoyi.basic.service.ISupplierService;
import com.ruoyi.framework.aspectj.lang.annotation.Log;
import com.ruoyi.framework.aspectj.lang.enums.BusinessType;
import com.ruoyi.framework.web.domain.AjaxResult;
import com.ruoyi.framework.web.domain.R;
import io.swagger.v3.oas.annotations.Operation;
import io.swagger.v3.oas.annotations.tags.Tag;
import jakarta.servlet.http.HttpServletResponse;
@@ -18,6 +20,8 @@
@RequestMapping("/purchase/report")
@AllArgsConstructor
public class AccountingReportController {

    private final ISupplierService supplierService;


    @GetMapping("/list")
@@ -44,4 +48,18 @@
    @Operation(summary = "采购报表-增值税比对")
    public void exportTwo(HttpServletResponse response) {
    }

    @GetMapping("/supplierTransactions")
    @Log(title = "供应商往来", businessType = BusinessType.OTHER)
    @Operation(summary = "供应商往来")
    public R supplierTransactions(Page page, String supplierName) {
        return R.ok(supplierService.supplierTransactions(page,supplierName));
    }

    @GetMapping("/supplierTransactionsDetails")
    @Log(title = "供应商往来明细", businessType = BusinessType.OTHER)
    @Operation(summary = "供应商往来明细")
    public R supplierTransactionsDetails(Page page, Long supplierId) {
        return R.ok(supplierService.supplierTransactionsDetails(page,supplierId));
    }
}

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

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

import com.fasterxml.jackson.annotation.JsonFormat;
import io.swagger.v3.oas.annotations.media.Schema;
import lombok.Data;

import java.math.BigDecimal;
import java.util.Date;

@Data
@Schema(name = "SupplierTransactionsDetailsVo", description = "采购管理--供应商往来明细(返回)")
public class SupplierTransactionsDetailsVo {

    @Schema(description = "采购单ID")
    private Long purchaseLedgerId;

    @Schema(description = "采购合同号")
    private String purchaseContractNumber;

    @Schema(description = "采购合同签订日期")
    @JsonFormat(pattern = "yyyy-MM-dd")
    private Date executionDate;

    @Schema(description = "合同金额")
    private BigDecimal contractAmount;

    @Schema(description = "付款金额")
    private BigDecimal paymentAmount;

    @Schema(description = "应付金额")
    private BigDecimal payableAmount;

}

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

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

import io.swagger.v3.oas.annotations.media.Schema;
import lombok.Data;

import java.math.BigDecimal;

@Data
@Schema(name = "SupplierTransactionsVo", description = "采购管理--供应商往来(返回)")
public class SupplierTransactionsVo {

    @Schema(description = "供应商ID")
    private Long supplierId;

    @Schema(description = "供应商名称")
    private String supplierName;

    @Schema(description = "合同总金额")
    //该供应商采购合同累计金额
    private BigDecimal contractAmounts;

    @Schema(description = "付款金额")
    //该供应商采购付款累计金额
    private BigDecimal paymentAmount;

    @Schema(description = "应付金额")
    //该供应商采购应付累计金额=财务(入库-退货)
    private BigDecimal payableAmount;

}

 src/main/resources/mapper/account/purchase/AccountPurchasePaymentMapper.xml

@@ -47,7 +47,7 @@
        SELECT DATE_FORMAT(payment_date, #{dateFormat}) AS dateStr,
               IFNULL(SUM(payment_amount), 0)            AS amount
        FROM account_purchase_payment
        WHERE payment_date BETWEEN #{startDate} AND #{endDate}
        WHERE payment_date BETWEEN #{startStr} AND #{endStr}
        GROUP BY dateStr
        ORDER BY dateStr
    </select>

 src/main/resources/mapper/account/sales/AccountSalesCollectionMapper.xml

@@ -89,7 +89,7 @@
         SELECT DATE_FORMAT(collection_date, #{dateFormat}) AS dateStr,
                IFNULL(SUM(collection_amount), 0)            AS amount
         FROM account_sales_collection
         WHERE collection_date BETWEEN #{startDate} AND #{endDate}
         WHERE collection_date BETWEEN #{startStr} AND #{endStr}
         GROUP BY dateStr
         ORDER BY dateStr
    </select>

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

@@ -68,5 +68,95 @@
            </if>
        </where>
    </select>
    <select id="supplierTransactions" resultType="com.ruoyi.purchase.vo.SupplierTransactionsVo">
        select T1.supplier_id,
               sm.supplier_name,
               T1.contractAmounts,
               IFNULL(T2.paymentAmount, 0) AS paymentAmount,
               IFNULL(T3.InboundAmount, 0) - IFNULL(T4.returnAmount, 0) AS payableAmount
        from (select supplier_id, sum(contract_amount) as contractAmounts from purchase_ledger group by supplier_id) T1
        left join (select supplier_id, sum(payment_amount) as paymentAmount from account_purchase_payment group by supplier_id) T2 on T1.supplier_id = T2.supplier_id
        left join (
            SELECT
                pl.supplier_id,
                sum(sir.stock_in_num * slp.tax_inclusive_unit_price) AS InboundAmount
            FROM stock_in_record sir
                     -- 10 ç±»åž‹æ‰å…³è”质检表
                     LEFT JOIN quality_inspect qi ON sir.record_type = 10 AND sir.record_id = qi.id
                -- åŠ¨æ€å…³è”采购（自动适配 7 å’Œ 10）
                     LEFT JOIN purchase_ledger pl
                               ON pl.id = IF(sir.record_type = 7, sir.record_id, qi.purchase_ledger_id)
                -- äº§å“å…³è”不动
                     LEFT JOIN sales_ledger_product slp ON pl.id = slp.sales_ledger_id
            -- æ¡ä»¶
            WHERE sir.approval_status = 1 AND slp.type = 2
              AND sir.record_type IN ('7','10')
            group by pl.supplier_id
        ) T3 on T3.supplier_id=T1.supplier_id
        left join (
            select
                supplier_id,
                sum(total_amount) as returnAmount
            from purchase_return_orders pro
            group by supplier_id
        ) T4 on T4.supplier_id=T1.supplier_id
        left join supplier_manage sm on T1.supplier_id = sm.id
        <where>
            <if test="supplierName!=null and supplierName!=''">
                AND sm.supplier_name LIKE CONCAT('%',#{supplierName},'%')
            </if>
        </where>
    </select>
    <select id="supplierTransactionsDetails"
            resultType="com.ruoyi.purchase.vo.SupplierTransactionsDetailsVo">
       select pl.id  purchaseLedgerId,
              pl.purchase_contract_number,
              pl.execution_date,
              pl.contract_amount,
              IFNULL(T1.paymentAmount, 0) AS paymentAmount,
              IFNULL(T2.InboundAmount, 0) - IFNULL(T3.returnAmount, 0) AS payableAmount
       from purchase_ledger pl
       left join (
           select
               pl.id,
               sum(app.payment_amount) as paymentAmount
           from account_purchase_payment app
           left join account_payment_application apa on app.account_payment_application_id = apa.id
           left join stock_in_record sir on FIND_IN_SET(sir.id, apa.stock_in_record_ids) > 0
               -- 10 ç±»åž‹æ‰å…³è”质检表
           LEFT JOIN quality_inspect qi ON sir.record_type = 10 AND sir.record_id = qi.id
               -- åŠ¨æ€å…³è”采购（自动适配 7 å’Œ 10）
           LEFT JOIN purchase_ledger pl
                     ON pl.id = IF(sir.record_type = 7, sir.record_id, qi.purchase_ledger_id)
           WHERE sir.approval_status = 1
             AND sir.record_type IN ('7','10')
           group by pl.id
       )T1 on T1.id = pl.id
       left join (
           SELECT
               pl.id,
               sum(sir.stock_in_num * slp.tax_inclusive_unit_price) AS InboundAmount
           FROM stock_in_record sir
                    -- 10 ç±»åž‹æ‰å…³è”质检表
                    LEFT JOIN quality_inspect qi ON sir.record_type = 10 AND sir.record_id = qi.id
               -- åŠ¨æ€å…³è”采购（自动适配 7 å’Œ 10）
                    LEFT JOIN purchase_ledger pl
                              ON pl.id = IF(sir.record_type = 7, sir.record_id, qi.purchase_ledger_id)
               -- äº§å“å…³è”不动
                    LEFT JOIN sales_ledger_product slp ON pl.id = slp.sales_ledger_id
           -- æ¡ä»¶
           WHERE sir.approval_status = 1 AND slp.type = 2
             AND sir.record_type IN ('7','10')
           group by pl.id
       )T2 on T2.id = pl.id
       left join (
           select pl.id,
                  sum(pro.total_amount) as returnAmount
           from purchase_return_orders pro
                    left join purchase_ledger pl on pro.purchase_ledger_id = pl.id
           group by pl.id
       )T3 on T3.id = pl.id
       where pl.supplier_id = #{supplierId}
    </select>

</mapper>
</mapper>

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

@@ -16,6 +16,59 @@
        GROUP BY dateStr
    </select>

    <select id="selectPurchaseLedgerListPage" resultType="com.ruoyi.purchase.dto.PurchaseLedgerDto">
        SELECT
        pl.id,
        pl.purchase_contract_number,
        pl.sales_contract_no,
        pl.supplier_id,
        pl.supplier_name,
        pl.project_name,
        pl.contract_amount,
        pl.entry_date,
        pl.execution_date,
        pl.recorder_id,
        pl.recorder_name,
        pl.template_name,
        pl.approve_user_ids,
        sm.is_white,
        pl.approval_status,
        pl.payment_method,
        pl.remarks
        FROM purchase_ledger pl
        LEFT JOIN supplier_manage sm ON pl.supplier_id = sm.id
        <where>
            <if test="c.purchaseContractNumber != null and c.purchaseContractNumber != ''">
                AND pl.purchase_contract_number LIKE CONCAT('%', #{c.purchaseContractNumber}, '%')
            </if>
            <if test="c.approvalStatus != null and c.approvalStatus != ''">
                AND pl.approval_status = #{c.approvalStatus}
            </if>
            <if test="c.supplierName != null and c.supplierName != ''">
                AND pl.supplier_name LIKE CONCAT('%', #{c.supplierName}, '%')
            </if>
            <if test="c.salesContractNo != null and c.salesContractNo != ''">
                AND pl.sales_contract_no LIKE CONCAT('%', #{c.salesContractNo}, '%')
            </if>
            <if test="c.projectName != null and c.projectName != ''">
                AND pl.project_name LIKE CONCAT('%', #{c.projectName}, '%')
            </if>
            <if test="c.entryDateStart != null and c.entryDateStart != ''">
                AND pl.entry_date &gt;= #{c.entryDateStart}
            </if>
            <if test="c.entryDateEnd != null and c.entryDateEnd != ''">
                AND pl.entry_date &lt;= #{c.entryDateEnd}
            </if>
            <if test="c.supplierId != null">
                AND pl.supplier_id = #{c.supplierId}
            </if>
            <if test="c.approvalStatus != null">
                AND pl.approval_status = #{c.approvalStatus}
            </if>
        </where>
        ORDER BY pl.entry_date DESC
    </select>

    <select id="selectTotalPurchaseAmount" resultType="java.math.BigDecimal">
        SELECT IFNULL(SUM(contract_amount), 0)
        FROM purchase_ledger