From 569655a9a25e87e9b5ea985df2e7a214e61d25ad Mon Sep 17 00:00:00 2001
From: yuan <123@>
Date: 星期四, 17 九月 2026 15:42:54 +0800
Subject: [PATCH] feat: 增加销售台账id到客户往来回款记录
---
src/main/java/com/ruoyi/account/service/impl/sales/AccountSalesCollectionServiceImpl.java | 9 ++--
src/main/resources/mapper/basic/CustomerMapper.xml | 71 ++++-------------------------------
src/main/resources/mapper/account/sales/AccountSalesCollectionMapper.xml | 1
src/main/java/com/ruoyi/account/pojo/sales/AccountSalesCollection.java | 6 +++
src/main/java/com/ruoyi/ai/tools/SalesAgentTools.java | 5 ++
doc/20260917_add_sales_ledger_id_to_account_sales_collection.sql | 26 +++++++++++++
6 files changed, 51 insertions(+), 67 deletions(-)
diff --git a/doc/20260917_add_sales_ledger_id_to_account_sales_collection.sql b/doc/20260917_add_sales_ledger_id_to_account_sales_collection.sql
new file mode 100644
index 0000000..020f6e6
--- /dev/null
+++ b/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 '鍏宠仈閿�鍞彴璐d锛堥攢鍞悎鍚岋級' 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` <> '';
diff --git a/src/main/java/com/ruoyi/account/pojo/sales/AccountSalesCollection.java b/src/main/java/com/ruoyi/account/pojo/sales/AccountSalesCollection.java
index 40ea9a8..cfd6818 100644
--- a/src/main/java/com/ruoyi/account/pojo/sales/AccountSalesCollection.java
+++ b/src/main/java/com/ruoyi/account/pojo/sales/AccountSalesCollection.java
@@ -46,6 +46,12 @@
private String stockOutRecordIds;
/**
+ * 鍏宠仈閿�鍞彴璐d锛堥攢鍞悎鍚岋級
+ */
+ @ApiModelProperty("鍏宠仈閿�鍞彴璐d锛堥攢鍞悎鍚岋級")
+ private Long salesLedgerId;
+
+ /**
* 鍒涘缓浜�
*/
@ApiModelProperty("鍒涘缓浜�")
diff --git a/src/main/java/com/ruoyi/account/service/impl/sales/AccountSalesCollectionServiceImpl.java b/src/main/java/com/ruoyi/account/service/impl/sales/AccountSalesCollectionServiceImpl.java
index 20374ae..fbd2734 100644
--- a/src/main/java/com/ruoyi/account/service/impl/sales/AccountSalesCollectionServiceImpl.java
+++ b/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());
diff --git a/src/main/java/com/ruoyi/ai/tools/SalesAgentTools.java b/src/main/java/com/ruoyi/ai/tools/SalesAgentTools.java
index 0f7e586..f87f8ed 100644
--- a/src/main/java/com/ruoyi/ai/tools/SalesAgentTools.java
+++ b/src/main/java/com/ruoyi/ai/tools/SalesAgentTools.java
@@ -951,6 +951,11 @@
if (collection.getId() == null) {
continue;
}
+ // 鍥炴鍗曞凡鐩存帴璁板綍閿�鍞彴璐d锛屾湭鍙戣揣鐨勫厛鏈熷洖娆句篃鑳藉綊灞炲埌鍚堝悓
+ 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;
diff --git a/src/main/resources/mapper/account/sales/AccountSalesCollectionMapper.xml b/src/main/resources/mapper/account/sales/AccountSalesCollectionMapper.xml
index 66ae5e4..4b03773 100644
--- a/src/main/resources/mapper/account/sales/AccountSalesCollectionMapper.xml
+++ b/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" />
diff --git a/src/main/resources/mapper/basic/CustomerMapper.xml b/src/main/resources/mapper/basic/CustomerMapper.xml
index ea1fab5..afda2fb 100644
--- a/src/main/resources/mapper/basic/CustomerMapper.xml
+++ b/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
- 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
+ ascc.sales_ledger_id,
+ sum(ascc.collection_amount) as receiptPaymentAmount
+ from account_sales_collection ascc
+ 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>
--
Gitblit v1.9.3