From bfda179b25eb3f121cc827485ae2b8be1fee3449 Mon Sep 17 00:00:00 2001
From: liyong <18434998025@163.com>
Date: 星期一, 11 五月 2026 18:00:28 +0800
Subject: [PATCH] refactor(database): 重构产品信息查询以使用关联表结构
---
src/main/resources/mapper/sales/SalesLedgerProductMapper.xml | 39 +++++++++++++++++++
src/main/resources/mapper/stock/StockOutRecordMapper.xml | 2
src/main/resources/mapper/account/SalesRefundAmountOrderMapper.xml | 8 ++-
src/main/resources/mapper/sales/ShippingInfoMapper.xml | 3 +
src/main/resources/mapper/stock/StockInRecordMapper.xml | 2
src/main/resources/mapper/procurementrecord/ReturnSaleProductMapper.xml | 8 ++-
6 files changed, 52 insertions(+), 10 deletions(-)
diff --git a/src/main/resources/mapper/account/SalesRefundAmountOrderMapper.xml b/src/main/resources/mapper/account/SalesRefundAmountOrderMapper.xml
index 05617b1..a41c8c1 100644
--- a/src/main/resources/mapper/account/SalesRefundAmountOrderMapper.xml
+++ b/src/main/resources/mapper/account/SalesRefundAmountOrderMapper.xml
@@ -18,9 +18,9 @@
<select id="pageSalesRefundAmountOrderDto" resultType="com.ruoyi.account.bean.dto.SalesRefundAmountOrderDto">
select sl.sales_contract_no,
sl.customer_contract_no,
- slp.specification_model,
- slp.product_category as product_name,
- slp.unit,
+ pm.model as specification_model,
+ p.product_name ,
+ pm.unit,
sl.customer_name,
rm.return_no as return_management_no,
srao.*
@@ -28,6 +28,8 @@
left join return_management rm on srao.return_management_id = rm.id
left join return_sale_product rs on rm.id = rs.return_management_id
left join sales_ledger_product slp on rs.return_sales_ledger_product_id = slp.id
+ left join product_model pm on slp.product_model_id = pm.id
+ left join product p on pm.product_id = p.id
left join sales_ledger sl on slp.sales_ledger_id = sl.id
<where>
<if test="ew.salesContractNo != null and ew.salesContractNo !=''">
diff --git a/src/main/resources/mapper/procurementrecord/ReturnSaleProductMapper.xml b/src/main/resources/mapper/procurementrecord/ReturnSaleProductMapper.xml
index 55cba43..52345e3 100644
--- a/src/main/resources/mapper/procurementrecord/ReturnSaleProductMapper.xml
+++ b/src/main/resources/mapper/procurementrecord/ReturnSaleProductMapper.xml
@@ -11,9 +11,9 @@
<result column="status" property="status" />
</resultMap>
<select id="listReturnSaleProductDto" resultType="com.ruoyi.procurementrecord.dto.ReturnSaleProductDto">
- SELECT slp.product_category as product_name,
- slp.specification_model as model,
- slp.unit as unit,
+ SELECT p.product_name as product_name,
+ pm.model as model,
+ pm.unit as unit,
rsp.*,
GREATEST(slp.quantity - COALESCE(rs.total_return_num, 0), 0) AS un_quantity,
COALESCE(rs.total_return_num, 0) AS total_return_num
@@ -21,6 +21,8 @@
LEFT JOIN return_management rm ON rm.id = rsp.return_management_id
LEFT JOIN shipping_info si ON si.id = rm.shipping_id
LEFT JOIN sales_ledger_product slp ON si.sales_ledger_product_id = slp.id and slp.type = 1
+ left join product_model pm on slp.product_model_id = pm.id
+ LEFT JOIN product p on pm.product_id = p.id
LEFT JOIN (SELECT return_sales_ledger_product_id,
SUM(num) AS total_return_num
diff --git a/src/main/resources/mapper/sales/SalesLedgerProductMapper.xml b/src/main/resources/mapper/sales/SalesLedgerProductMapper.xml
index 49689a1..f3fa32d 100644
--- a/src/main/resources/mapper/sales/SalesLedgerProductMapper.xml
+++ b/src/main/resources/mapper/sales/SalesLedgerProductMapper.xml
@@ -6,7 +6,42 @@
<select id="selectSalesLedgerProductList" resultType="com.ruoyi.sales.pojo.SalesLedgerProduct">
SELECT
- T1.*,
+ T1.id,
+ T1.sales_ledger_id,
+ T1.warn_num,
+ T1.speculative_trading_name,
+ T1.quantity,
+ T1.min_stock,
+ T1.tax_rate,
+ T1.tax_inclusive_unit_price,
+ T1.tax_inclusive_total_price,
+ T1.tax_exclusive_total_price,
+ T1.invoice_type,
+ T1.type,
+ T1.tickets_num,
+ T1.tickets_amount,
+ T1.future_tickets,
+ T1.future_tickets_amount,
+ T1.invoice_num,
+ T1.no_invoice_num,
+ T1.invoice_amount,
+ T1.no_invoice_amount,
+ T1.product_id,
+ T1.product_model_id,
+ T1.register,
+ T1.register_date,
+ T1.approve_status,
+ T1.pending_invoice_total,
+ T1.invoice_total,
+ T1.pending_tickets_total,
+ T1.tickets_total,
+ T1.is_checked,
+ T1.is_production,
+ T1.create_user,
+ T1.dept_id,
+ p.product_name as product_category,
+ pm.model as specification_model,
+ pm.unit as unit,
CASE
WHEN (IFNULL(t2.qualitity, 0) - IFNULL(t2.locked_quantity, 0)) >0 THEN 1
ELSE 0
@@ -29,6 +64,8 @@
LEFT JOIN shipping_product_detail spd ON si.id = spd.shipping_info_id
GROUP BY sales_ledger_product_id
) t3 ON t3.sales_ledger_product_id = T1.id
+ left join product_model pm ON T1.product_model_id = pm.id
+ left join product p ON pm.product_id = p.id
<where>
<if test="salesLedgerProduct.salesLedgerId != null">
AND T1.sales_ledger_id = #{salesLedgerProduct.salesLedgerId}
diff --git a/src/main/resources/mapper/sales/ShippingInfoMapper.xml b/src/main/resources/mapper/sales/ShippingInfoMapper.xml
index 048bb23..ee384ed 100644
--- a/src/main/resources/mapper/sales/ShippingInfoMapper.xml
+++ b/src/main/resources/mapper/sales/ShippingInfoMapper.xml
@@ -19,7 +19,8 @@
s.update_user,
s.tenant_id,
sl.sales_contract_no,
- slp.specification_model,
+ pm.model as specification_model,
+ pm.unit,
p.product_name,
sl.customer_name
FROM shipping_info s
diff --git a/src/main/resources/mapper/stock/StockInRecordMapper.xml b/src/main/resources/mapper/stock/StockInRecordMapper.xml
index ec25d21..05f8c89 100644
--- a/src/main/resources/mapper/stock/StockInRecordMapper.xml
+++ b/src/main/resources/mapper/stock/StockInRecordMapper.xml
@@ -77,7 +77,7 @@
pl.supplier_name,
DATE(sir.create_time) AS inboundDate,
p.product_name,
- slp.specification_model,
+ pm.model as specification_model,
sor.stock_in_num * slp.tax_inclusive_unit_price AS InboundAmount,
pl.purchase_contract_number
FROM stock_in_record sir
diff --git a/src/main/resources/mapper/stock/StockOutRecordMapper.xml b/src/main/resources/mapper/stock/StockOutRecordMapper.xml
index b4619d8..9e32e21 100644
--- a/src/main/resources/mapper/stock/StockOutRecordMapper.xml
+++ b/src/main/resources/mapper/stock/StockOutRecordMapper.xml
@@ -93,7 +93,7 @@
sl.customer_name,
s.shipping_date,
p.product_name,
- slp.specification_model,
+ pm.model as specification_model,
sor.stock_out_num * slp.tax_inclusive_unit_price as outboundAmount,
s.shipping_no,
sl.sales_contract_no
--
Gitblit v1.9.3