feat: 增加销售台账id到客户往来回款记录

yuan

2 天以前 569655a9a25e87e9b5ea985df2e7a214e61d25ad

feat: 增加销售台账id到客户往来回款记录

 doc/20260917_add_sales_ledger_id_to_account_sales_collection.sql

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,26 @@
-- å®¢æˆ·å¾€æ¥å›žæ¬¾ï¼šaccount_sales_collection å¢žåŠ  sales_ledger_id
-- å›žæ¬¾å•åŽŸå…ˆåªèƒ½é€šè¿‡ stock_out_record_ids åæŸ¥é”€å”®å°è´¦ï¼ŒåˆåŒæœªå‘货时无法建立关联，
-- å¯¼è‡´ã€Œæœªå‘货就不能新增回款」。改为直接记录销售台账 id。

ALTER TABLE `account_sales_collection`
    ADD COLUMN `sales_ledger_id` bigint NULL COMMENT '关联销售台账id（销售合同）' AFTER `customer_id`,
    ADD INDEX `idx_asc_sales_ledger_id` (`sales_ledger_id`);

-- å­˜é‡æ•°æ®å›žå¡«ï¼šæŒ‰åŽŸå‡ºåº“单关联反查所属销售台账
UPDATE `account_sales_collection` c
SET c.`sales_ledger_id` = (
    SELECT si.`sales_ledger_id`
    FROM `stock_out_record` sor
    JOIN `shipping_info` si ON sor.`record_id` = si.`id`
    WHERE sor.`record_type` = '13'
      AND si.`sales_ledger_id` IS NOT NULL
      AND FIND_IN_SET(sor.`id`, c.`stock_out_record_ids`) > 0
    LIMIT 1
)
WHERE c.`sales_ledger_id` IS NULL
  AND c.`stock_out_record_ids` IS NOT NULL
  AND c.`stock_out_record_ids` <> '';

-- æ‰§è¡ŒåŽæ ¸å¯¹ï¼Œç»“果应为 0：
-- SELECT COUNT(*) FROM `account_sales_collection`
-- WHERE `sales_ledger_id` IS NULL AND `stock_out_record_ids` IS NOT NULL AND `stock_out_record_ids` <> '';

 src/main/java/com/ruoyi/account/pojo/sales/AccountSalesCollection.java

@@ -46,6 +46,12 @@
    private String stockOutRecordIds;

    /**
     * å…³è”销售台账id（销售合同）
     */
    @ApiModelProperty("关联销售台账id（销售合同）")
    private Long salesLedgerId;

    /**
     * åˆ›å»ºäºº
     */
    @ApiModelProperty("创建人")

 src/main/java/com/ruoyi/account/service/impl/sales/AccountSalesCollectionServiceImpl.java

@@ -122,13 +122,14 @@
        }

        List<Long> stockOutRecordIds = accountSalesCollectionMapper.selectStockOutRecordIdsBySalesLedgerId(salesLedger.getId());
        if (CollectionUtils.isEmpty(stockOutRecordIds)) {
            throw new ServiceException("该合同暂无可回款的销售出库记录");
        }

        AccountSalesCollection collection = new AccountSalesCollection();
        collection.setCustomerId(dto.getCustomerId().intValue());
        collection.setStockOutRecordIds(stockOutRecordIds.stream().map(String::valueOf).collect(Collectors.joining(",")));
        collection.setSalesLedgerId(salesLedger.getId());
        // åˆåŒæœªå‘货时没有出库记录，此时仍允许回款，出库单关联留空
        collection.setStockOutRecordIds(CollectionUtils.isEmpty(stockOutRecordIds)
                ? null
                : stockOutRecordIds.stream().map(String::valueOf).collect(Collectors.joining(",")));
        collection.setCollectionDate(dto.getPaymentDate());
        collection.setCollectionAmount(dto.getPaymentAmount());
        collection.setCollectionMethod(dto.getPaymentMethod());

 src/main/java/com/ruoyi/ai/tools/SalesAgentTools.java

@@ -951,6 +951,11 @@
            if (collection.getId() == null) {
                continue;
            }
            // å›žæ¬¾å•å·²ç›´æŽ¥è®°å½•é”€å”®å°è´¦id，未发货的先期回款也能归属到合同
            if (collection.getSalesLedgerId() != null) {
                result.put(collection.getId(), new LinkedHashSet<>(List.of(collection.getSalesLedgerId())));
                continue;
            }
            List<Long> stockOutRecordIds = parseLongIds(collection.getStockOutRecordIds());
            if (stockOutRecordIds.isEmpty()) {
                continue;

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

@@ -6,6 +6,7 @@
    <resultMap id="BaseResultMap" type="com.ruoyi.account.pojo.sales.AccountSalesCollection">
        <id column="id" property="id" />
        <result column="stock_out_record_ids" property="stockOutRecordIds" />
        <result column="sales_ledger_id" property="salesLedgerId" />
        <result column="create_user" property="createUser" />
        <result column="create_time" property="createTime" />
        <result column="update_user" property="updateUser" />

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

@@ -114,32 +114,9 @@
               c.customer_name,
               T1.contractAmounts,
               IFNULL(T2.receiptPaymentAmount, 0) AS receiptPaymentAmount,
               IFNULL(T3.outboundAmount, 0) - IFNULL(T4.returnAmount, 0) AS receiptableAmount
               IFNULL(T1.contractAmounts, 0) - IFNULL(T2.receiptPaymentAmount, 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!=''">
@@ -155,48 +132,16 @@
               sl.execution_date,
               sl.contract_amount,
               IFNULL(T1.receiptPaymentAmount, 0) AS receiptPaymentAmount,
               IFNULL(T2.outboundAmount, 0) - IFNULL(T3.returnAmount, 0) AS receiptableAmount
               IFNULL(sl.contract_amount, 0) - IFNULL(T1.receiptPaymentAmount, 0) AS receiptableAmount
        from sales_ledger sl
        left join (
            select
                sl.id,
                sum(t.collection_amount) as receiptPaymentAmount
            from (
                select
                    ascc.collection_amount,
                    s.sales_ledger_id
                ascc.sales_ledger_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
                where sor.record_type='13'
                  and sor.approval_status=1
                group by ascc.id, s.sales_ledger_id
            ) t
            left join sales_ledger sl on t.sales_ledger_id = sl.id
            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 ascc.sales_ledger_id is not null
            group by ascc.sales_ledger_id
        )T1 on T1.sales_ledger_id = sl.id
        where sl.customer_id = #{customerId}
        order by sl.id desc
    </select>