fix: 客户往来及明细查询修改

gongchunyi

3 天以前 60acc8dd204aea9a435ae44205bae7b97feda4b3

fix: 客户往来及明细查询修改

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

@@ -12,6 +12,7 @@
import org.apache.ibatis.annotations.Param;

import java.util.List;
import java.util.Map;

/**
 * 客户档案Mapper接口
@@ -20,55 +21,7 @@
 * @date 2025-05-07
 */
@Mapper
public interface CustomerMapper extends BaseMapper<Customer>
{
    /**
     * 查询客户档案
     *
     * @param id 客户档案主键
     * @return 客户档案
     */
    Customer selectCustomerById(Long id);

    /**
     * 查询客户档案列表
     *
     * @param customer 客户档案
     * @return 客户档案集合
     */
    List<Customer> selectCustomerList(Customer customer);

    /**
     * 新增客户档案
     *
     * @param customer 客户档案
     * @return 结果
     */
    int insertCustomer(Customer customer);

    /**
     * 修改客户档案
     *
     * @param customer 客户档案
     * @return 结果
     */
    int updateCustomer(Customer customer);

    /**
     * 删除客户档案
     *
     * @param id 客户档案主键
     * @return 结果
     */
    int deleteCustomerById(Long id);

    /**
     * 批量删除客户档案
     *
     * @param ids 需要删除的数据主键集合
     * @return 结果
     */
    int deleteCustomerByIds(Long[] ids);
public interface CustomerMapper extends BaseMapper<Customer> {

    IPage<CustomerVo> listPage(Page<CustomerDto> page, @Param("c") CustomerDto customer, @Param("loginUserId") Long loginUserId);

@@ -77,4 +30,8 @@
    IPage<CustomerTransactionsVo> customewTransactions(Page page, @Param("customerName") String customerName);

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

    List<Map<String, Object>> getShippedAmountByCustomerIds(@Param("customerIds") List<Long> customerIds);

    List<Map<String, Object>> getShippedAmountBySalesLedgerIds(@Param("salesLedgerIds") List<Long> salesLedgerIds);
}

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

@@ -34,6 +34,7 @@
import org.springframework.util.CollectionUtils;
import org.springframework.web.multipart.MultipartFile;

import java.math.BigDecimal;
import java.time.ZoneId;
import java.util.*;
import java.util.stream.Collectors;
@@ -383,12 +384,56 @@

    @Override
    public IPage<CustomerTransactionsVo> customewTransactions(Page page, String customerName) {
        return customerMapper.customewTransactions(page, customerName);
        IPage<CustomerTransactionsVo> p = customerMapper.customewTransactions(page, customerName);
        List<CustomerTransactionsVo> records = p.getRecords();
        if (!CollectionUtils.isEmpty(records)) {
            List<Long> customerIds = records.stream().map(CustomerTransactionsVo::getCustomerId).collect(Collectors.toList());
            List<Map<String, Object>> shippedAmountList = customerMapper.getShippedAmountByCustomerIds(customerIds);
            Map<Long, BigDecimal> shippedAmountMap = new HashMap<>();
            for (Map<String, Object> map : shippedAmountList) {
                if (map.get("outboundAmount") != null) {
                    shippedAmountMap.put(((Number) map.get("customerId")).longValue(), new BigDecimal(String.valueOf(map.get("outboundAmount"))));
                }
            }
            for (CustomerTransactionsVo vo : records) {
                BigDecimal outboundAmount = shippedAmountMap.getOrDefault(vo.getCustomerId(), BigDecimal.ZERO);
                vo.setShippedAmount(outboundAmount);
                BigDecimal contractAmounts = vo.getContractAmounts() == null ? BigDecimal.ZERO : vo.getContractAmounts();
                BigDecimal unshippedAmount = contractAmounts.subtract(outboundAmount);
                if (unshippedAmount.compareTo(BigDecimal.ZERO) < 0) {
                    unshippedAmount = BigDecimal.ZERO;
                }
                vo.setUnshippedAmount(unshippedAmount);
            }
        }
        return p;
    }

    @Override
    public IPage<CustomerTransactionsDetailsVo> customewTransactionsDetails(Page page, Long customerId) {
        return customerMapper.customewTransactionsDetails(page, customerId);
        IPage<CustomerTransactionsDetailsVo> p = customerMapper.customewTransactionsDetails(page, customerId);
        List<CustomerTransactionsDetailsVo> records = p.getRecords();
        if (!CollectionUtils.isEmpty(records)) {
            List<Long> salesLedgerIds = records.stream().map(CustomerTransactionsDetailsVo::getSalesLedgerId).collect(Collectors.toList());
            List<Map<String, Object>> shippedAmountList = customerMapper.getShippedAmountBySalesLedgerIds(salesLedgerIds);
            Map<Long, BigDecimal> shippedAmountMap = new HashMap<>();
            for (Map<String, Object> map : shippedAmountList) {
                if (map.get("outboundAmount") != null) {
                    shippedAmountMap.put(((Number) map.get("salesLedgerId")).longValue(), new BigDecimal(String.valueOf(map.get("outboundAmount"))));
                }
            }
            for (CustomerTransactionsDetailsVo vo : records) {
                BigDecimal outboundAmount = shippedAmountMap.getOrDefault(vo.getSalesLedgerId(), BigDecimal.ZERO);
                vo.setShippedAmount(outboundAmount);
                BigDecimal contractAmount = vo.getContractAmount() == null ? BigDecimal.ZERO : vo.getContractAmount();
                BigDecimal unshippedAmount = contractAmount.subtract(outboundAmount);
                if (unshippedAmount.compareTo(BigDecimal.ZERO) < 0) {
                    unshippedAmount = BigDecimal.ZERO;
                }
                vo.setUnshippedAmount(unshippedAmount);
            }
        }
        return p;
    }

    /**

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

@@ -112,23 +112,8 @@
    <select id="customewTransactions" resultType="com.ruoyi.sales.vo.CustomerTransactionsVo">
        select T1.customer_id,
               c.customer_name,
               T1.contractAmounts,
               IFNULL(T3.outboundAmount, 0) AS shippedAmount,
               GREATEST(T1.contractAmounts - IFNULL(T3.outboundAmount, 0), 0) AS unshippedAmount
               T1.contractAmounts
        from (select customer_id, sum(contract_amount) as contractAmounts from sales_ledger group by customer_id) T1
        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 customer c on T1.customer_id = c.id
        <where>
            <if test="customerName!=null and customerName!=''">
@@ -142,24 +127,45 @@
        select sl.id salesLedgerId,
               sl.sales_contract_no,
               sl.execution_date,
               sl.contract_amount,
               IFNULL(T2.outboundAmount, 0) AS shippedAmount,
               GREATEST(sl.contract_amount - IFNULL(T2.outboundAmount, 0), 0) AS unshippedAmount
               sl.contract_amount
        from sales_ledger sl
        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
        where sl.customer_id = #{customerId}
        order by sl.execution_date
    </select>

    <select id="getShippedAmountByCustomerIds" resultType="java.util.Map">
        SELECT
            sl.customer_id as customerId,
            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
            and sl.customer_id IN
            <foreach collection="customerIds" item="customerId" open="(" separator="," close=")">
                #{customerId}
            </foreach>
        group by sl.customer_id
    </select>

    <select id="getShippedAmountBySalesLedgerIds" resultType="java.util.Map">
        SELECT
            sl.id as salesLedgerId,
            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
            and sl.id IN
            <foreach collection="salesLedgerIds" item="id" open="(" separator="," close=")">
                #{id}
            </foreach>
        group by sl.id
    </select>
</mapper>