From ccc9d4533bc7b696f6d539720a6052abc44ff7a5 Mon Sep 17 00:00:00 2001
From: yuan <123@>
Date: 星期日, 11 十月 2026 23:46:17 +0800
Subject: [PATCH] fix: 添加设备名称字段并修复多个查询的排序和分组问题
---
src/main/resources/mapper/production/ProductWorkOrderMapper.xml | 2
src/main/resources/mapper/equipmentenergyconsumption/EquipmentEnergyConsumptionMapper.xml | 11 ++
src/main/resources/mapper/account/AccountExpenseMapper.xml | 4
src/main/resources/mapper/production/SalesLedgerProductionAccountingMapper.xml | 6
src/main/resources/mapper/account/AccountIncomeMapper.xml | 4
src/main/resources/mapper/quality/QualityUnqualifiedMapper.xml | 2
src/main/resources/mapper/sales/ReceiptPaymentMapper.xml | 34 +++++--
src/main/java/com/ruoyi/measuringinstrumentledger/dto/SparePartsDto.java | 8 ++
src/main/resources/mapper/production/ProductOrderMapper.xml | 2
src/main/resources/mapper/sales/SalesLedgerProductMapper.xml | 2
src/main/resources/mapper/measuringinstrumentledger/SparePartsMapper.xml | 9 ++
src/main/resources/mapper/production/SalesLedgerSchedulingMapper.xml | 2
src/main/resources/mapper/sales/InvoiceLedgerMapper.xml | 65 ++++++++++------
src/main/resources/mapper/production/ProductionProductMainMapper.xml | 3
src/main/resources/mapper/projectManagement/InfoMapper.xml | 7 +
src/main/resources/mapper/procurementrecord/ProcurementRecordMapper.xml | 2
src/main/resources/mapper/purchase/PaymentRegistrationMapper.xml | 34 ++++++--
17 files changed, 138 insertions(+), 59 deletions(-)
diff --git a/src/main/java/com/ruoyi/measuringinstrumentledger/dto/SparePartsDto.java b/src/main/java/com/ruoyi/measuringinstrumentledger/dto/SparePartsDto.java
index cf9ac86..91c8582 100644
--- a/src/main/java/com/ruoyi/measuringinstrumentledger/dto/SparePartsDto.java
+++ b/src/main/java/com/ruoyi/measuringinstrumentledger/dto/SparePartsDto.java
@@ -12,4 +12,12 @@
*/
private String parentName;
private List<SparePartsDto> children;
+
+ /**
+ * 缁戝畾璁惧鐨勫悕绉帮紙澶氫釜鐢ㄩ�楀彿鍒嗛殧锛夈��
+ * 2026-10-12 鏂板锛氬墠绔浠跺垪琛ㄧ涓�鍒楀彇鐨勫氨鏄� deviceNameStr锛屼箣鍓� DTO 閲屾病鏈夎繖涓瓧娈碉紝
+ * 瀵艰嚧銆岃澶囧悕绉般�嶅垪姘歌繙绌虹櫧锛岀湅璧锋潵鍍�"澶囦欢娌＄粦瀹氳澶�"銆�
+ * 鐢� SparePartsMapper.listPage 閫氳繃 spare_parts.device_ids 鍙嶆煡 device_ledger 鎷煎嚭銆�
+ */
+ private String deviceNameStr;
}
diff --git a/src/main/resources/mapper/account/AccountExpenseMapper.xml b/src/main/resources/mapper/account/AccountExpenseMapper.xml
index 1a8d34b..6d0399b 100644
--- a/src/main/resources/mapper/account/AccountExpenseMapper.xml
+++ b/src/main/resources/mapper/account/AccountExpenseMapper.xml
@@ -19,6 +19,8 @@
<if test="accountExpense.expenseMethod != null and accountExpense.expenseMethod != '' ">
AND expense_method = #{accountExpense.expenseMethod}
</if>
+ <!-- 2026-10-12 闇�姹傦細鏀嚭绠＄悊鎸夋敮鍑烘棩鏈熷�掑簭 -->
+ ORDER BY expense_date DESC, id DESC
</select>
<select id="accountExpenseExport" resultType="com.ruoyi.account.pojo.AccountExpense">
SELECT
@@ -53,7 +55,7 @@
<if test="dateQueryDto.entryDateEnd != null and dateQueryDto.entryDateEnd != '' ">
AND expense_date <= DATE_FORMAT(#{dateQueryDto.entryDateEnd},'%Y-%m-%d')
</if>
- group by expense_type
+ group by expense_type, sdd.dict_label
</select>
<select id="report1" resultType="java.math.BigDecimal">
diff --git a/src/main/resources/mapper/account/AccountIncomeMapper.xml b/src/main/resources/mapper/account/AccountIncomeMapper.xml
index e4f3361..4da002a 100644
--- a/src/main/resources/mapper/account/AccountIncomeMapper.xml
+++ b/src/main/resources/mapper/account/AccountIncomeMapper.xml
@@ -20,6 +20,8 @@
<if test="accountIncome.incomeMethod != null and accountIncome.incomeMethod != '' ">
AND income_method = #{accountIncome.incomeMethod}
</if>
+ <!-- 2026-10-12 闇�姹傦細鏀跺叆绠＄悊鎸夋敹鍏ユ棩鏈熷�掑簭 -->
+ ORDER BY income_date DESC, id DESC
</select>
<select id="accountIncomeExport" resultType="com.ruoyi.account.pojo.AccountIncome">
SELECT
@@ -54,7 +56,7 @@
<if test="dateQueryDto.entryDateEnd != null and dateQueryDto.entryDateEnd != '' ">
AND income_date <= DATE_FORMAT(#{dateQueryDto.entryDateEnd},'%Y-%m-%d')
</if>
- group by income_type
+ group by income_type, sdd.dict_label
</select>
<select id="report1" resultType="java.math.BigDecimal">
SELECT
diff --git a/src/main/resources/mapper/equipmentenergyconsumption/EquipmentEnergyConsumptionMapper.xml b/src/main/resources/mapper/equipmentenergyconsumption/EquipmentEnergyConsumptionMapper.xml
index eda1827..ed7ab63 100644
--- a/src/main/resources/mapper/equipmentenergyconsumption/EquipmentEnergyConsumptionMapper.xml
+++ b/src/main/resources/mapper/equipmentenergyconsumption/EquipmentEnergyConsumptionMapper.xml
@@ -9,6 +9,15 @@
and `name` like concat('%',#{req.name},'%')
</if>
</select>
+ <!--
+ 2026-10-11 淇锛堥�犳暟椤圭洰 R07锛岃 docs/handoff/R07-璁惧涓庤兘鑰�.md 搂7.1锛夛細
+ 鍘� SQL 鏈熬甯� `GROUP BY `code``锛屽湪鏈満 sql_mode 鍚� ONLY_FULL_GROUP_BY 鏃舵姏
+ ER_WRONG_FIELD_WITH_GROUP: Expression #1 of SELECT list is not in GROUP BY clause ...
+ ... 'id' is not functionally dependent on columns in GROUP BY clause
+ 瀵艰嚧銆岃兘鑰楃鐞� 鈫� 鑳芥簮瓒嬪娍銆嶉〉闈㈠繀鐒� 500銆�
+ 鐢变簬 WHERE 宸叉妸 run_date 閿佹涓哄悓涓�澶┿�佷笖 (code, run_date) 澶╃劧鍞竴锛�
+ `GROUP BY code` 鍦ㄨ涔変笂鏄┖鎿嶄綔 鈬� 鐩存帴绉婚櫎锛岃繑鍥炵粨鏋滈泦涓嶅彉銆�
+ -->
<select id="listPageByTrend" resultType="com.ruoyi.equipmentenergyconsumption.pojo.EquipmentEnergyConsumption">
SELECT
*,
@@ -20,8 +29,6 @@
<if test="req.name != null and req.name != ''">
and `name` like concat('%',#{req.name},'%')
</if>
- GROUP BY
- `code`
ORDER BY
update_time DESC;
</select>
diff --git a/src/main/resources/mapper/measuringinstrumentledger/SparePartsMapper.xml b/src/main/resources/mapper/measuringinstrumentledger/SparePartsMapper.xml
index 611d51e..ec6b013 100644
--- a/src/main/resources/mapper/measuringinstrumentledger/SparePartsMapper.xml
+++ b/src/main/resources/mapper/measuringinstrumentledger/SparePartsMapper.xml
@@ -2,7 +2,14 @@
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.ruoyi.measuringinstrumentledger.mapper.SparePartsMapper">
<select id="listPage" resultType="com.ruoyi.measuringinstrumentledger.dto.SparePartsDto">
- select sp.*,sp1.name as parentName
+ select sp.*, sp1.name as parentName,
+ <!-- 2026-10-12 淇锛氬墠绔垪琛ㄧ涓�鍒楀彇鐨勬槸 deviceNameStr锛屼絾 DTO 鍜� SQL 閮芥病鏈夎繖涓瓧娈碉紝
+ 瀵艰嚧銆岃澶囧悕绉般�嶅垪姘歌繙绌虹櫧锛堢湅璧锋潵鍍�"娌＄粦瀹氳澶�"锛夈��
+ 杩欓噷鎸� spare_parts.device_ids锛堥�楀彿鍒嗛殧锛夊弽鏌� device_ledger 鎷煎嚭璁惧鍚嶃�� -->
+ (select group_concat(d.device_name order by d.id separator ',')
+ from device_ledger d
+ where sp.device_ids is not null and sp.device_ids <> ''
+ and find_in_set(d.id, sp.device_ids)) as deviceNameStr
from spare_parts sp
left join spare_parts sp1 on sp1.id = sp.parent_id
<where>
diff --git a/src/main/resources/mapper/procurementrecord/ProcurementRecordMapper.xml b/src/main/resources/mapper/procurementrecord/ProcurementRecordMapper.xml
index b0166ca..193f93f 100644
--- a/src/main/resources/mapper/procurementrecord/ProcurementRecordMapper.xml
+++ b/src/main/resources/mapper/procurementrecord/ProcurementRecordMapper.xml
@@ -20,7 +20,7 @@
t2.tax_exclusive_total_price,
t3.inbound_num as quantityStock
from purchase_ledger t1
- left join sales_ledger_product t2 on t1.id = t2.sales_ledger_id and slp.type = 2
+ left join sales_ledger_product t2 on t1.id = t2.sales_ledger_id and t2.type = 2
left join procurement_record_storage t3 on t2.id = t3.sales_ledger_product_id
where t1.purchase_contract_number = #{req.purchaseContractNumber}
<if test="req.id != null and req.id != ''">
diff --git a/src/main/resources/mapper/production/ProductOrderMapper.xml b/src/main/resources/mapper/production/ProductOrderMapper.xml
index 6b993bf..0826265 100644
--- a/src/main/resources/mapper/production/ProductOrderMapper.xml
+++ b/src/main/resources/mapper/production/ProductOrderMapper.xml
@@ -64,6 +64,8 @@
and po.create_time between #{c.startTime} and #{c.endTime}
</if>
</where>
+ <!-- 2026-10-12 闇�姹傦細鐢熶骇妯″潡鎸夋椂闂村�掑簭锛屾渶鏂扮殑鎺掑湪鍓嶉潰 -->
+ order by po.create_time desc, po.id desc
</select>
<select id="listProcessRoute" resultType="com.ruoyi.production.pojo.ProcessRoute">
select pr.*
diff --git a/src/main/resources/mapper/production/ProductWorkOrderMapper.xml b/src/main/resources/mapper/production/ProductWorkOrderMapper.xml
index 14b883e..b06b827 100644
--- a/src/main/resources/mapper/production/ProductWorkOrderMapper.xml
+++ b/src/main/resources/mapper/production/ProductWorkOrderMapper.xml
@@ -48,6 +48,8 @@
<if test="c.productOrderId != null and c.productOrderId != ''">
and pwo.product_order_id = #{c.productOrderId}
</if>
+ <!-- 2026-10-12 闇�姹傦細鐢熶骇妯″潡鎸夋椂闂村�掑簭锛屾渶鏂扮殑鎺掑湪鍓嶉潰 -->
+ order by pwo.create_time desc, pwo.id desc
</select>
<select id="getProductWorkOrderFlowCard" resultType="com.ruoyi.production.dto.ProductWorkOrderDto">
SELECT
diff --git a/src/main/resources/mapper/production/ProductionProductMainMapper.xml b/src/main/resources/mapper/production/ProductionProductMainMapper.xml
index a122e90..a7c02e0 100644
--- a/src/main/resources/mapper/production/ProductionProductMainMapper.xml
+++ b/src/main/resources/mapper/production/ProductionProductMainMapper.xml
@@ -49,7 +49,8 @@
and ppm.status = #{c.status}
</if>
</where>
- order by ppm.id
+ <!-- 2026-10-12 闇�姹傦細鐢熶骇妯″潡鎸夋椂闂村�掑簭锛屾渶鏂扮殑鎺掑湪鍓嶉潰 -->
+ order by ppm.create_time desc, ppm.id desc
</select>
<select id="getOrderByMainId" resultType="com.ruoyi.production.pojo.ProductOrder">
diff --git a/src/main/resources/mapper/production/SalesLedgerProductionAccountingMapper.xml b/src/main/resources/mapper/production/SalesLedgerProductionAccountingMapper.xml
index d9ddde7..f0dd9d9 100644
--- a/src/main/resources/mapper/production/SalesLedgerProductionAccountingMapper.xml
+++ b/src/main/resources/mapper/production/SalesLedgerProductionAccountingMapper.xml
@@ -22,7 +22,7 @@
FROM
sales_ledger_production_accounting t4
LEFT JOIN sales_ledger T1 ON T1.id = t4.sales_ledger_id
- left join sales_ledger_product t3 on t4.sales_ledger_product_id = t3.id and slp.type = 1
+ left join sales_ledger_product t3 on t4.sales_ledger_product_id = t3.id and t3.type = 1
<where>
t3.type = 1
<if test="salesLedgerDto.schedulingUserName != null and salesLedgerDto.schedulingUserName != '' ">
@@ -84,7 +84,7 @@
</if>
</where>
- GROUP BY slpa.scheduling_user_name
+ GROUP BY slpa.scheduling_user_name, slpa.scheduling_user_id
</select>
@@ -115,7 +115,7 @@
left join product_process pp on pp.name = slpa.process
where slpa.scheduling_user_id = #{ew.userId}
and slpa.scheduling_date like concat(#{ew.date}, '%')
- group by slpa.scheduling_user_id
+ group by slpa.scheduling_user_id, pp.type
</select>
diff --git a/src/main/resources/mapper/production/SalesLedgerSchedulingMapper.xml b/src/main/resources/mapper/production/SalesLedgerSchedulingMapper.xml
index 4cff920..cee9f55 100644
--- a/src/main/resources/mapper/production/SalesLedgerSchedulingMapper.xml
+++ b/src/main/resources/mapper/production/SalesLedgerSchedulingMapper.xml
@@ -91,7 +91,7 @@
FROM
sales_ledger_scheduling T2
LEFT JOIN sales_ledger T1 ON T1.id = T2.sales_ledger_id
- left join sales_ledger_product t3 on T2.sales_ledger_product_id = t3.id and slp.type = 1
+ left join sales_ledger_product t3 on T2.sales_ledger_product_id = t3.id and t3.type = 1
<where>
t3.type = 1
<if test="salesLedgerDto.status != null and salesLedgerDto.status != '' ">
diff --git a/src/main/resources/mapper/projectManagement/InfoMapper.xml b/src/main/resources/mapper/projectManagement/InfoMapper.xml
index e101797..9a98a92 100644
--- a/src/main/resources/mapper/projectManagement/InfoMapper.xml
+++ b/src/main/resources/mapper/projectManagement/InfoMapper.xml
@@ -74,7 +74,12 @@
and t1.review_status = #{vo.reviewStatus.code}
</if>
<if test="vo.stage != null">
- and t1.stage = #{vo.stage.code}
+ <!-- 2026-10-12 淇锛歱roject_management_info 娌℃湁 stage 鍒楋紙鍏冩暟鎹噷鍙� plan_stage锛屼笖鏄� JSON 鏍煎紡锛夛紝
+ 鍘� `and t1.stage = #{vo.stage.code}` 涓�閫�"璁″垝鐘舵��"灏辨姏
+ ER_BAD_FIELD_ERROR Unknown column 't1.stage'銆�
+ 鍓嶇璇ョ瓫閫夐」鍙栫殑鏄瓧鍏� plan_status(1寰呭紑濮�/2杩涜涓�/3宸茬粨鏉�)锛�
+ 鑰� t1.status 姝ｅソ瀛樼殑灏辨槸 1/2/3锛屾晠鏀规寚 t1.status銆� -->
+ and t1.status = #{vo.stage.code}
</if>
order by t1.create_time desc
</select>
diff --git a/src/main/resources/mapper/purchase/PaymentRegistrationMapper.xml b/src/main/resources/mapper/purchase/PaymentRegistrationMapper.xml
index c5c007e..8de052d 100644
--- a/src/main/resources/mapper/purchase/PaymentRegistrationMapper.xml
+++ b/src/main/resources/mapper/purchase/PaymentRegistrationMapper.xml
@@ -181,32 +181,45 @@
AND t4.purchase_contract_number LIKE CONCAT('%',#{params.purchaseContractNumber},'%')
</if>
</where>
- ORDER BY T1.payment_date,T1.create_time DESC
+ <!-- 2026-10-13 闇�姹傦細浠樻娴佹按鎸変粯娆炬棩鏈熷�掑簭锛堝師鏉ュ彧鍐欎簡 payment_date 娌″啓鏂瑰悜锛岄粯璁ゅ崌搴忥紝鏈�鏃╃殑鍦ㄦ渶涓婇潰锛� -->
+ ORDER BY T1.payment_date DESC, T1.id DESC
</select>
<select id="supplierNameListPage" resultType="com.ruoyi.purchase.dto.PaymentRegistrationDto">
SELECT
T1.supplier_id,
T1.supplier_name,
- SUM(contract_amount) AS invoiceAmount,
- IFNULL( SUM(T2.current_payment_amount) , 0 ) AS paymentAmount,
- IFNULL((IFNULL(SUM(contract_amount),0) - IFNULL(SUM(T2.current_payment_amount),0)),0) AS payableAmount
+ IFNULL(SUM(T1.contract_amount), 0) AS invoiceAmount,
+ IFNULL(SUM(T2.paid), 0) AS paymentAmount,
+ IFNULL(SUM(T1.contract_amount), 0) - IFNULL(SUM(T2.paid), 0) AS payableAmount
FROM purchase_ledger T1
- LEFT JOIN payment_registration T2 ON T1.id = T2.purchase_ledger_id
+ <!-- 2026-10-13 淇锛氬師鏉ョ洿鎺� LEFT JOIN payment_registration 鍐� SUM(contract_amount)锛�
+ 涓�绗旈噰璐悎鍚屾湁鍑犵瑪浠樻锛屽悎鍚岄灏辫閲嶅绱姞鍑犳锛堜緵搴斿晢寰�鏉ラ〉銆屽悎鍚岄噾棰�/搴斾粯閲戦銆嶅洜姝よ櫄楂橈級銆�
+ 鏀规垚鍏堟寜鍙拌处姹囨�讳粯娆惧啀 JOIN銆� -->
+ LEFT JOIN (
+ SELECT purchase_ledger_id, SUM(current_payment_amount) AS paid
+ FROM payment_registration WHERE purchase_ledger_id IS NOT NULL GROUP BY purchase_ledger_id
+ ) T2 ON T2.purchase_ledger_id = T1.id
<where>
<if test="req.supplierName != null and req.supplierName != '' ">
T1.supplier_name LIKE CONCAT ('%',#{req.supplierName},'%')
</if>
</where>
- GROUP BY T1.supplier_name
+ GROUP BY T1.supplier_id, T1.supplier_name
</select>
<select id="supplierNameListPageDetails" resultType="com.ruoyi.purchase.dto.PaymentRegistrationDto">
SELECT
T1.supplier_id,
T1.supplier_name,
- SUM(contract_amount) AS invoiceAmount,
- IFNULL( SUM(T2.current_payment_amount) , 0 ) AS paymentAmount,
- IFNULL((IFNULL(SUM(contract_amount),0) - IFNULL(SUM(T2.current_payment_amount),0)),0) AS payableAmount,
+ T1.contract_amount AS invoiceAmount,
+ IFNULL(SUM(T2.current_payment_amount), 0) AS paymentAmount,
+ <!-- 2026-10-13 淇锛氬師鏉ュ啓鐨勬槸銆屽悎鍚岄 鈭� 鏈棩鏈熻繖涓�绗斾粯娆俱�嶏紝鍐嶅彔鍔� GROUP BY payment_date锛�
+ 涓�绗斿悎鍚屾湁鍑犵瑪浠樻灏辨媶鎴愬嚑琛屻�佹瘡琛屼竴涓亣鐨�"搴斾粯"锛堜緥濡� CG20260523002 宸插叏棰濅粯娆撅紝
+ 鍗存樉绀哄簲浠� 18524.07锛夈�傚簲浠橀噾棰濆簲褰撴槸**璇ュ悎鍚屽綋鍓嶇湡姝ｇ殑鏈粯娆鹃 = 鍚堝悓棰� 鈭� 鍏ㄩ儴浠樻**銆� -->
+ GREATEST(T1.contract_amount - IFNULL((
+ SELECT SUM(T3.current_payment_amount) FROM payment_registration T3
+ WHERE T3.purchase_ledger_id = T1.id
+ ), 0), 0) AS payableAmount,
T1.purchase_contract_number,
T2.payment_date
FROM purchase_ledger T1
@@ -217,7 +230,8 @@
T1.supplier_name LIKE CONCAT ('%',#{req.supplierName},'%')
</if>
</where>
- GROUP BY T1.purchase_contract_number,T2.payment_date
+ GROUP BY T1.id, T1.supplier_id, T1.supplier_name, T1.contract_amount, T1.purchase_contract_number, T2.payment_date
+ ORDER BY T1.entry_date ASC, T2.payment_date ASC
</select>
</mapper>
\ No newline at end of file
diff --git a/src/main/resources/mapper/quality/QualityUnqualifiedMapper.xml b/src/main/resources/mapper/quality/QualityUnqualifiedMapper.xml
index 5bda4f6..b9f385e 100644
--- a/src/main/resources/mapper/quality/QualityUnqualifiedMapper.xml
+++ b/src/main/resources/mapper/quality/QualityUnqualifiedMapper.xml
@@ -43,6 +43,8 @@
<if test="qualityUnqualified.entryDateEnd != null and qualityUnqualified.entryDateEnd != '' ">
AND check_time <= DATE_FORMAT(#{qualityUnqualified.entryDateEnd},'%Y-%m-%d')
</if>
+ <!-- 2026-10-12 闇�姹傦細涓嶅悎鏍肩鐞嗘寜妫�娴嬫椂闂村�掑簭 -->
+ ORDER BY qu.check_time DESC, qu.id DESC
</select>
<select id="qualityUnqualifiedExport" resultType="com.ruoyi.quality.pojo.QualityUnqualified">
SELECT
diff --git a/src/main/resources/mapper/sales/InvoiceLedgerMapper.xml b/src/main/resources/mapper/sales/InvoiceLedgerMapper.xml
index 2874f48..acda784 100644
--- a/src/main/resources/mapper/sales/InvoiceLedgerMapper.xml
+++ b/src/main/resources/mapper/sales/InvoiceLedgerMapper.xml
@@ -6,12 +6,12 @@
<select id="invoiceLedgerPage" resultType="com.ruoyi.sales.dto.InvoiceLedgerDto">
SELECT
T1.id ,
- T1.sales_ledger_id ,
- T1.sales_contract_no ,
- T1.customer_id ,
+ T5.sales_ledger_id AS sales_ledger_id,
+ T3.sales_contract_no AS sales_contract_no,
+ T3.customer_id AS customer_id,
T1.invoice_no ,
- T1.invoice_amount ,
- T1.tax_rate ,
+ T1.invoice_total AS invoice_amount,
+ T5.tax_rate AS tax_rate,
T1.invoice_person ,
T1.invoice_date ,
T1.create_time ,
@@ -24,8 +24,12 @@
T3.salesman,
T4.invoiceFileName
FROM invoice_ledger T1
- LEFT JOIN customer T2 ON T1.customer_id = T2.id
- LEFT JOIN sales_ledger T3 ON T1.sales_ledger_id = T3.id
+ <!-- 2026-10-12 淇锛歩nvoice_ledger 琛ㄩ噷娌℃湁 sales_ledger_id / sales_contract_no / customer_id /
+ invoice_amount / tax_rate 杩� 5 鍒楋紙瀹冧滑鍦� invoice_registration_product 涓婏級锛�
+ 鍘熸潵鐨� T1.xxx 蹇呯劧鎶� ER_BAD_FIELD_ERROR銆傛敼涓烘寜鐪熷疄缁撴瀯鍏宠仈銆� -->
+ LEFT JOIN invoice_registration_product T5 ON T5.id = T1.invoice_registration_product_id
+ LEFT JOIN sales_ledger T3 ON T3.id = T5.sales_ledger_id
+ LEFT JOIN customer T2 ON T2.id = T3.customer_id
LEFT JOIN (
SELECT
invoice_ledger_id,
@@ -48,12 +52,12 @@
<select id="invoiceLedgerList" resultType="com.ruoyi.sales.dto.InvoiceLedgerDto">
SELECT
T1.id ,
- T1.sales_ledger_id ,
- T1.sales_contract_no ,
- T1.customer_id ,
+ T5.sales_ledger_id AS sales_ledger_id,
+ T3.sales_contract_no AS sales_contract_no,
+ T3.customer_id AS customer_id,
T1.invoice_no ,
- T1.invoice_amount ,
- T1.tax_rate ,
+ T1.invoice_total AS invoice_amount,
+ T5.tax_rate AS tax_rate,
T1.invoice_person ,
T1.invoice_date ,
T1.create_time ,
@@ -66,8 +70,10 @@
T3.customer_contract_no,
T4.invoiceFileName
FROM invoice_ledger T1
- LEFT JOIN customer T2 ON T1.customer_id = T2.id
- LEFT JOIN sales_ledger T3 ON T1.sales_ledger_id = T3.id
+ <!-- 2026-10-12 淇锛氬悓 invoiceLedgerPage锛屾敼涓烘寜鐪熷疄琛ㄧ粨鏋勫叧鑱� -->
+ LEFT JOIN invoice_registration_product T5 ON T5.id = T1.invoice_registration_product_id
+ LEFT JOIN sales_ledger T3 ON T3.id = T5.sales_ledger_id
+ LEFT JOIN customer T2 ON T2.id = T3.customer_id
LEFT JOIN (
SELECT
invoice_ledger_id,
@@ -90,12 +96,12 @@
<select id="invoiceLedgerInfo" resultType="com.ruoyi.sales.dto.InvoiceLedgerDto">
SELECT
T1.id ,
- T1.sales_ledger_id ,
- T1.sales_contract_no ,
- T1.customer_id ,
+ T5.sales_ledger_id AS sales_ledger_id,
+ T3.sales_contract_no AS sales_contract_no,
+ T3.customer_id AS customer_id,
T1.invoice_no ,
- T1.invoice_amount ,
- T1.tax_rate ,
+ T1.invoice_total AS invoice_amount,
+ T5.tax_rate AS tax_rate,
T1.invoice_person ,
T1.invoice_date ,
T1.create_time ,
@@ -105,7 +111,10 @@
T1.tenant_id ,
T2.customer_name
FROM invoice_ledger T1
- LEFT JOIN customer T2 ON T1.customer_id = T2.id
+ <!-- 2026-10-12 淇锛氬悓 invoiceLedgerPage锛屾敼涓烘寜鐪熷疄琛ㄧ粨鏋勫叧鑱� -->
+ LEFT JOIN invoice_registration_product T5 ON T5.id = T1.invoice_registration_product_id
+ LEFT JOIN sales_ledger T3 ON T3.id = T5.sales_ledger_id
+ LEFT JOIN customer T2 ON T2.id = T3.customer_id
WHERE T1.id = #{id}
</select>
@@ -113,17 +122,23 @@
SELECT
T1.customer_id as id,
T1.customer_name,
- SUM(contract_amount) AS invoice_total,
- IFNULL( SUM(T2.receipt_payment_amount) , 0 ) AS receipt_payment_amount,
- IFNULL((IFNULL(SUM(contract_amount),0) - IFNULL(SUM(T2.receipt_payment_amount),0)),0) AS unReceipt_payment_amount
+ IFNULL(SUM(T1.contract_amount), 0) AS invoice_total,
+ IFNULL(SUM(T2.paid), 0) AS receipt_payment_amount,
+ IFNULL(SUM(T1.contract_amount), 0) - IFNULL(SUM(T2.paid), 0) AS unReceipt_payment_amount
FROM sales_ledger T1
- LEFT JOIN receipt_payment T2 ON T1.id = T2.sales_ledger_id
+ <!-- 2026-10-13 淇锛氬師鏉ョ洿鎺� LEFT JOIN receipt_payment 鍐� SUM(contract_amount)锛�
+ 涓�绗斿彴璐︽湁鍑犵瑪鍥炴锛屽悎鍚岄灏辫閲嶅绱姞鍑犳锛堝鎴峰線鏉ラ〉鐨勩�屽悎鍚岄噾棰�/搴旀敹閲戦銆嶅洜姝よ櫄楂橈紝
+ 渚嬪瀹㈡埛 1108 瀹為檯鍚堝悓 15.37 涓囪绠楁垚 78.35 涓囷級銆傛敼鎴愬厛鎸夊彴璐︽眹鎬诲洖娆惧啀 JOIN銆� -->
+ LEFT JOIN (
+ SELECT sales_ledger_id, SUM(receipt_payment_amount) AS paid
+ FROM receipt_payment WHERE sales_ledger_id IS NOT NULL GROUP BY sales_ledger_id
+ ) T2 ON T2.sales_ledger_id = T1.id
<where>
<if test="invoiceLedgerDto.searchText != null and invoiceLedgerDto.searchText != '' ">
T1.customer_name LIKE CONCAT ('%',#{invoiceLedgerDto.searchText},'%')
</if>
</where>
- GROUP BY T1.customer_name
+ GROUP BY T1.customer_id, T1.customer_name
</select>
<select id="invoiceLedgerProductInfo" resultType="com.ruoyi.sales.dto.InvoiceRegistrationProductDto">
diff --git a/src/main/resources/mapper/sales/ReceiptPaymentMapper.xml b/src/main/resources/mapper/sales/ReceiptPaymentMapper.xml
index 975d120..2693625 100644
--- a/src/main/resources/mapper/sales/ReceiptPaymentMapper.xml
+++ b/src/main/resources/mapper/sales/ReceiptPaymentMapper.xml
@@ -36,11 +36,11 @@
SELECT
T1.id ,
T1.sales_ledger_id ,
- T1.sales_contract_no ,
- T1.customer_id ,
- T1.invoice_no ,
- T1.invoice_amount ,
- T1.tax_rate ,
+ T3.sales_contract_no AS sales_contract_no,
+ T3.customer_id AS customer_id,
+ T4.invoice_no AS invoice_no,
+ T4.invoice_total AS invoice_amount,
+ T5.tax_rate AS tax_rate,
T1.receipt_payment_type ,
T1.receipt_payment_amount ,
T1.registrant ,
@@ -54,8 +54,13 @@
T3.customer_contract_no,
T2.customer_name
FROM receipt_payment T1
- LEFT JOIN customer T2 ON T1.customer_id = T2.id
- LEFT JOIN sales_ledger T3 ON T1.sales_ledger_id = T3.id
+ <!-- 2026-10-12 淇锛歳eceipt_payment 琛ㄩ噷娌℃湁 sales_contract_no / customer_id / invoice_no /
+ invoice_amount / tax_rate 杩� 5 鍒楋紝鍘熸潵鐨� T1.xxx 蹇呯劧鎶� ER_BAD_FIELD_ERROR銆�
+ 瀹㈡埛涓庡悎鍚屽彿浠� sales_ledger 鍙栵紝鍙戠エ淇℃伅浠� invoice_ledger / invoice_registration_product 鍙栥�� -->
+ LEFT JOIN sales_ledger T3 ON T3.id = T1.sales_ledger_id
+ LEFT JOIN customer T2 ON T2.id = T3.customer_id
+ LEFT JOIN invoice_ledger T4 ON T4.id = T1.invoice_ledger_id
+ LEFT JOIN invoice_registration_product T5 ON T5.id = T4.invoice_registration_product_id
WHERE T1.id = #{id}
</select>
@@ -90,7 +95,7 @@
FROM invoice_ledger_file
GROUP BY invoice_ledger_id
) T4 ON T4.invoice_ledger_id = T1.id
- LEFT JOIN sales_ledger_product T5 ON T2.sales_ledger_product_id = T5.id and slp.type = 1
+ LEFT JOIN sales_ledger_product T5 ON T2.sales_ledger_product_id = T5.id and T5.type = 1
LEFT JOIN (
SELECT
SUM(receipt_payment_amount) AS receipt_payment_amount_total,
@@ -378,7 +383,7 @@
GROUP_CONCAT( name ORDER BY id ASC SEPARATOR ' | ') AS invoiceFileName
FROM invoice_ledger_file GROUP BY invoice_ledger_id
) T4 ON T4.invoice_ledger_id = T1.id
- LEFT JOIN sales_ledger_product T5 ON T2.sales_ledger_product_id = T5.id and slp.type = 1
+ LEFT JOIN sales_ledger_product T5 ON T2.sales_ledger_product_id = T5.id and T5.type = 1
LEFT JOIN (
SELECT SUM(receipt_payment_amount) AS receipt_payment_amount_total,invoice_ledger_id FROM receipt_payment GROUP
BY invoice_ledger_id
@@ -414,7 +419,7 @@
GROUP_CONCAT( name ORDER BY id ASC SEPARATOR ' | ') AS invoiceFileName
FROM invoice_ledger_file GROUP BY invoice_ledger_id
) T4 ON T4.invoice_ledger_id = T1.id
- LEFT JOIN sales_ledger_product T5 ON T2.sales_ledger_product_id = T5.id and slp.type = 1
+ LEFT JOIN sales_ledger_product T5 ON T2.sales_ledger_product_id = T5.id and T5.type = 1
LEFT JOIN (
SELECT SUM(receipt_payment_amount) AS receipt_payment_amount_total,invoice_ledger_id FROM receipt_payment GROUP
BY invoice_ledger_id
@@ -438,7 +443,13 @@
T1.sales_contract_no,
T1.contract_amount AS invoice_total,
IFNULL(SUM(T2.receipt_payment_amount), 0) AS receipt_payment_amount,
- IFNULL((T1.contract_amount - IFNULL(SUM(T2.receipt_payment_amount), 0)), 0) AS unReceipt_payment_amount,
+ <!-- 2026-10-13 淇锛氬師鏉ュ啓鐨勬槸銆屽悎鍚岄 鈭� 鏈棩鏈熻繖涓�绗斿洖娆俱�嶏紝鍐嶅彔鍔� GROUP BY receipt_payment_date锛�
+ 涓�绗斿彴璐︽湁鍑犵瑪鍥炴灏变細鎷嗘垚鍑犺锛屾瘡琛岄兘鏄剧ず涓�涓亣鐨�"搴旀敹"锛堜緥濡� XS202605001 宸插叏棰濆洖娆撅紝
+ 鍗存樉绀哄簲鏀� 1544.40锛夈�傚簲鏀堕噾棰濆簲褰撴槸**璇ュ悎鍚屽綋鍓嶇湡姝ｇ殑鏈洖娆鹃 = 鍚堝悓棰� 鈭� 鍏ㄩ儴鍥炴**銆� -->
+ GREATEST(T1.contract_amount - IFNULL((
+ SELECT SUM(T3.receipt_payment_amount) FROM receipt_payment T3
+ WHERE T3.sales_ledger_id = T1.id
+ ), 0), 0) AS unReceipt_payment_amount,
T2.receipt_payment_date
FROM sales_ledger T1
INNER JOIN receipt_payment T2 ON T1.id = T2.sales_ledger_id
@@ -449,6 +460,7 @@
</if>
</where>
GROUP BY T1.id, T1.sales_contract_no, T1.contract_amount, T2.receipt_payment_date
+ ORDER BY T1.entry_date ASC, T2.receipt_payment_date ASC
</select>
</mapper>
\ No newline at end of file
diff --git a/src/main/resources/mapper/sales/SalesLedgerProductMapper.xml b/src/main/resources/mapper/sales/SalesLedgerProductMapper.xml
index 712124a..1098719 100644
--- a/src/main/resources/mapper/sales/SalesLedgerProductMapper.xml
+++ b/src/main/resources/mapper/sales/SalesLedgerProductMapper.xml
@@ -132,7 +132,7 @@
AND slp.product_category = #{req.productCategory}
</if>
<!-- 鎸変骇鍝佸ぇ绫诲垎缁勮仛鍚� -->
- GROUP BY slp.product_category
+ GROUP BY slp.product_category, slp.specification_model, sl.supplier_name
<!-- 鎸変骇鍝佸ぇ绫绘帓搴� -->
ORDER BY slp.product_category
</select>
--
Gitblit v1.9.3