feat(customer): 新增客户往来及往来明细查询功能

zss

6 天以前 c3435615d9503fa65130b84cc456a16e8df54482

feat(customer): 新增客户往来及往来明细查询功能

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

@@ -6,6 +6,8 @@
import com.ruoyi.basic.dto.CustomerDto;
import com.ruoyi.basic.pojo.Customer;
import com.ruoyi.basic.vo.CustomerVo;
import com.ruoyi.sales.vo.CustomerTransactionsDetailsVo;
import com.ruoyi.sales.vo.CustomerTransactionsVo;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

@@ -13,7 +15,7 @@

/**
 * å®¢æˆ·æ¡£æ¡ˆMapper接口
 * 
 *
 * @author ruoyi
 * @date 2025-05-07
 */
@@ -22,7 +24,7 @@
{
    /**
     * æŸ¥è¯¢å®¢æˆ·æ¡£æ¡ˆ
     * 
     *
     * @param id å®¢æˆ·æ¡£æ¡ˆä¸»é”®
     * @return å®¢æˆ·æ¡£æ¡ˆ
     */
@@ -30,7 +32,7 @@

    /**
     * æŸ¥è¯¢å®¢æˆ·æ¡£æ¡ˆåˆ—表
     * 
     *
     * @param customer å®¢æˆ·æ¡£æ¡ˆ
     * @return å®¢æˆ·æ¡£æ¡ˆé›†åˆ
     */
@@ -38,7 +40,7 @@

    /**
     * æ–°å¢žå®¢æˆ·æ¡£æ¡ˆ
     * 
     *
     * @param customer å®¢æˆ·æ¡£æ¡ˆ
     * @return ç»“æžœ
     */
@@ -46,7 +48,7 @@

    /**
     * ä¿®æ”¹å®¢æˆ·æ¡£æ¡ˆ
     * 
     *
     * @param customer å®¢æˆ·æ¡£æ¡ˆ
     * @return ç»“æžœ
     */
@@ -54,7 +56,7 @@

    /**
     * åˆ é™¤å®¢æˆ·æ¡£æ¡ˆ
     * 
     *
     * @param id å®¢æˆ·æ¡£æ¡ˆä¸»é”®
     * @return ç»“æžœ
     */
@@ -62,7 +64,7 @@

    /**
     * æ‰¹é‡åˆ é™¤å®¢æˆ·æ¡£æ¡ˆ
     * 
     *
     * @param ids éœ€è¦åˆ é™¤çš„数据主键集合
     * @return ç»“æžœ
     */
@@ -71,4 +73,8 @@
    IPage<CustomerVo> listPage(Page<CustomerDto> page, @Param("c") CustomerDto customer, @Param("loginUserId") Long loginUserId);

    List<CustomerVo> list(@Param("c") CustomerDto customer, @Param("loginUserId") Long loginUserId);
}

    IPage<CustomerTransactionsVo> customewTransactions(Page page, @Param("customerName") String customerName);

    IPage<CustomerTransactionsDetailsVo> customewTransactionsDetails(Page page, @Param("customerId") Long customerId);
}

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

@@ -7,6 +7,8 @@
import com.ruoyi.basic.pojo.Customer;
import com.ruoyi.basic.vo.CustomerVo;
import com.ruoyi.framework.web.domain.R;
import com.ruoyi.sales.vo.CustomerTransactionsDetailsVo;
import com.ruoyi.sales.vo.CustomerTransactionsVo;
import org.springframework.web.multipart.MultipartFile;

import java.util.List;
@@ -93,4 +95,20 @@
    void together(CustomerDto customerDto);

    Boolean back(Long id);

    /**
     * æŸ¥è¯¢å®¢æˆ·å¾€æ¥åˆ—表
     * @param page
     * @param customerName
     * @return
     */
    IPage<CustomerTransactionsVo> customewTransactions(Page page, String customerName);

    /**
     * æŸ¥è¯¢å®¢æˆ·å¾€æ¥æ˜Žç»†åˆ—表
     * @param page
     * @param customerId
     * @return
     */
    IPage<CustomerTransactionsDetailsVo> customewTransactionsDetails(Page page, Long customerId);
}

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

@@ -23,6 +23,8 @@
import com.ruoyi.framework.web.domain.R;
import com.ruoyi.sales.mapper.SalesLedgerMapper;
import com.ruoyi.sales.pojo.SalesLedger;
import com.ruoyi.sales.vo.CustomerTransactionsDetailsVo;
import com.ruoyi.sales.vo.CustomerTransactionsVo;
import lombok.AllArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.BeanUtils;
@@ -379,6 +381,16 @@
        return this.updateById(customer);
    }

    @Override
    public IPage<CustomerTransactionsVo> customewTransactions(Page page, String customerName) {
        return customerMapper.customewTransactions(page, customerName);
    }

    @Override
    public IPage<CustomerTransactionsDetailsVo> customewTransactionsDetails(Page page, Long customerId) {
        return customerMapper.customewTransactionsDetails(page, customerId);
    }

    /**
     * ä¸‹åˆ’线命名转驼峰命名
     */

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

@@ -1,11 +1,16 @@
package com.ruoyi.sales.controller;

import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.ruoyi.basic.service.ICustomerService;
import com.ruoyi.framework.aspectj.lang.annotation.Log;
import com.ruoyi.framework.aspectj.lang.enums.BusinessType;
import com.ruoyi.framework.web.controller.BaseController;
import com.ruoyi.framework.web.domain.AjaxResult;
import com.ruoyi.framework.web.domain.R;
import com.ruoyi.sales.dto.StatisticsTableDto;
import com.ruoyi.sales.service.impl.MetricStatisticsServiceImpl;
import io.swagger.v3.oas.annotations.tags.Tag;
import io.swagger.v3.oas.annotations.Operation;
import io.swagger.v3.oas.annotations.tags.Tag;
import lombok.AllArgsConstructor;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
@@ -21,7 +26,8 @@
@AllArgsConstructor
public class MetricStatisticsController extends BaseController {

    private MetricStatisticsServiceImpl metricStatisticsService;
    private final MetricStatisticsServiceImpl metricStatisticsService;
    private final ICustomerService customerService;

    @Operation(summary = "头部总计")
    @GetMapping("/total")
@@ -35,4 +41,18 @@
        return metricStatisticsService.statisticsTable(statisticsTableDto);
    }

    @GetMapping("/customewTransactions")
    @Log(title = "客户往来", businessType = BusinessType.OTHER)
    @Operation(summary = "客户往来")
    public R customewTransactions(Page page, String customerName) {
        return R.ok(customerService.customewTransactions(page,customerName));
    }

    @GetMapping("/customewTransactionsDetails")
    @Log(title = "客户往来明细", businessType = BusinessType.OTHER)
    @Operation(summary = "客户往来明细")
    public R customewTransactionsDetails(Page page, Long customerId) {
        return R.ok(customerService.customewTransactionsDetails(page,customerId));
    }

}

 src/main/java/com/ruoyi/sales/vo/CustomerTransactionsDetailsVo.java

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

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

import java.math.BigDecimal;
import java.time.LocalDate;

@Data
@Schema(name = "CustomerTransactionsDetailsVo", description = "营销管理--客户往来明细(返回)")
public class CustomerTransactionsDetailsVo {

    @Schema(description = "销售单ID")
    private Long salesLedgerId;

    @Schema(description = "销售合同号")
    private String salesContractNo;

    @Schema(description = "销售合同签订日期")
    @JsonFormat(pattern = "yyyy-MM-dd")
    private LocalDate executionDate;

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

    @Schema(description = "收款金额")
    private BigDecimal receiptPaymentAmount;

    @Schema(description = "应收金额")
    private BigDecimal receiptableAmount;

}

 src/main/java/com/ruoyi/sales/vo/CustomerTransactionsVo.java

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

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

import java.math.BigDecimal;

@Data
@Schema(name = "CustomerTransactionsVo", description = "营销管理--客户往来(返回)")
public class CustomerTransactionsVo {

    @Schema(description = "客户ID")
    private Long customerId;

    @Schema(description = "客户名称")
    private String customerName;

    @Schema(description = "合同总金额")
    //该客户销售合同累计金额
    private BigDecimal contractAmounts;

    @Schema(description = "收款金额")
    //该客户销售收款累计金额
    private BigDecimal receiptPaymentAmount;

    @Schema(description = "应收金额")
    //该客户销售应收累计金额=财务(出库-退货)
    private BigDecimal receiptableAmount;

}

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

@@ -107,4 +107,87 @@
            </if>
        </where>
    </select>
    <select id="customewTransactions" resultType="com.ruoyi.sales.vo.CustomerTransactionsVo">
        select T1.customer_id,
               c.customer_name,
               T1.contractAmounts,
               IFNULL(T2.receiptPaymentAmount, 0) AS receiptPaymentAmount,
               IFNULL(T3.outboundAmount, 0) - IFNULL(T4.returnAmount, 0) AS receiptableAmount
        from (select customer_id, sum(contract_amount) as contractAmounts from sales_ledger group by customer_id) T1
        left join (select customer_id, sum(collection_amount) as receiptPaymentAmount from account_sales_collection group by customer_id) T2 on T1.customer_id = T2.customer_id
        left join (
            SELECT
                sl.customer_id,
                sum(sor.stock_out_num * slp.tax_inclusive_unit_price) as outboundAmount
            FROM stock_out_record sor
            LEFT join shipping_info s on sor.record_id = s.id
            LEFT JOIN sales_ledger sl ON s.sales_ledger_id = sl.id
            LEFT JOIN sales_ledger_product slp ON s.sales_ledger_product_id = slp.id
            WHERE sor.record_type='13'
                and sor.approval_status=1
                and slp.type = 1
            group by sl.customer_id
        ) T3 on T3.customer_id=T1.customer_id
        left join (
            select
                sl.customer_id,
                sum(rm.refund_amount) as returnAmount
            from return_management rm
            left join shipping_info si on rm.shipping_id = si.id
            left join sales_ledger sl on si.sales_ledger_id = sl.id
            where rm.status=1
            group by sl.customer_id
        ) T4 on T4.customer_id=T1.customer_id
        left join customer c on T1.customer_id = c.id
        <where>
            <if test="customerName!=null and customerName!=''">
                AND c.customer_name LIKE CONCAT('%', #{customerName}, '%')
            </if>
        </where>
    </select>
    <select id="customewTransactionsDetails"
            resultType="com.ruoyi.sales.vo.CustomerTransactionsDetailsVo">
        select sl.id salesLedgerId,
               sl.sales_contract_no,
               sl.execution_date,
               sl.contract_amount,
               IFNULL(T1.receiptPaymentAmount, 0) AS receiptPaymentAmount,
               IFNULL(T2.outboundAmount, 0) - IFNULL(T3.returnAmount, 0) AS receiptableAmount
        from sales_ledger sl
        left join (
            select
                sl.id,
                sum(ascc.collection_amount) as receiptPaymentAmount
            from account_sales_collection ascc
            left join stock_out_record sor on FIND_IN_SET(sor.id, ascc.stock_out_record_ids) > 0
            left join shipping_info s on sor.record_id = s.id
            LEFT JOIN sales_ledger sl ON s.sales_ledger_id = sl.id
            WHERE sor.record_type='13'
              and sor.approval_status=1
            group by  sl.id
        )T1 on T1.id = sl.id
        left join (
            SELECT
                sl.id,
                sum(sor.stock_out_num * slp.tax_inclusive_unit_price) as outboundAmount
            FROM stock_out_record sor
                     left join shipping_info s on sor.record_id = s.id
                     LEFT JOIN sales_ledger sl ON s.sales_ledger_id = sl.id
                     LEFT JOIN sales_ledger_product slp ON s.sales_ledger_product_id = slp.id
            WHERE sor.record_type='13'
              and sor.approval_status=1
              and slp.type = 1
            group by  sl.id
        )T2 on T2.id = sl.id
        left join (
            select sl.id,
                   sum(rm.refund_amount) as returnAmount
            from return_management rm
                     left join shipping_info si on rm.shipping_id = si.id
                     left join sales_ledger sl on si.sales_ledger_id = sl.id
            where rm.status=1
            group by sl.id
        )T3 on T3.id = sl.id
        where sl.customer_id = #{customerId}
    </select>
</mapper>