From 235721b4a24fd6cc5037bba78c60723dcf200741 Mon Sep 17 00:00:00 2001
From: gongchunyi <deslre0381@gmail.com>
Date: 星期二, 12 五月 2026 04:30:16 +0800
Subject: [PATCH] fix: 录入人回显姓名,产品出入库数据分析查询错误
---
src/main/resources/mapper/account/AccountIncomeMapper.xml | 92 ++++++++++++++++++++++++++++++++++++++--------
src/main/resources/mapper/stock/StockInventoryMapper.xml | 18 ++++++++-
2 files changed, 92 insertions(+), 18 deletions(-)
diff --git a/src/main/resources/mapper/account/AccountIncomeMapper.xml b/src/main/resources/mapper/account/AccountIncomeMapper.xml
index f2cfbe2..cd1fb08 100644
--- a/src/main/resources/mapper/account/AccountIncomeMapper.xml
+++ b/src/main/resources/mapper/account/AccountIncomeMapper.xml
@@ -4,32 +4,56 @@
<select id="accountIncomeListPage" resultType="com.ruoyi.account.pojo.AccountIncome">
SELECT
- *
- FROM account_income
+ ai.id,
+ ai.business_id,
+ ai.business_type,
+ ai.income_date,
+ ai.income_type,
+ ai.customer_name,
+ ai.income_money,
+ ai.income_described,
+ ai.income_method,
+ ai.invoice_number,
+ ai.note,
+ CASE
+ WHEN TRIM(IFNULL(ai.input_user, '')) REGEXP '^[0-9]+$'
+ THEN COALESCE(ui.nick_name, ai.input_user)
+ ELSE ai.input_user
+ END AS input_user,
+ ai.input_time,
+ ai.create_time,
+ ai.create_user,
+ ai.update_time,
+ ai.update_user,
+ ai.tenant_id
+ FROM account_income ai
+ LEFT JOIN sys_user ui ON TRIM(IFNULL(ai.input_user, '')) REGEXP '^[0-9]+$'
+ AND ui.user_id = CAST(TRIM(ai.input_user) AS UNSIGNED)
+ AND ui.del_flag = '0'
where
1=1
<if test="accountIncome.entryDateStart != null and accountIncome.entryDateStart != '' ">
- AND income_date >= DATE_FORMAT(#{accountIncome.entryDateStart},'%Y-%m-%d')
+ AND ai.income_date >= DATE_FORMAT(#{accountIncome.entryDateStart},'%Y-%m-%d')
</if>
<if test="accountIncome.entryDateEnd != null and accountIncome.entryDateEnd != '' ">
- AND income_date <= DATE_FORMAT(#{accountIncome.entryDateEnd},'%Y-%m-%d')
+ AND ai.income_date <= DATE_FORMAT(#{accountIncome.entryDateEnd},'%Y-%m-%d')
</if>
<if test="accountIncome.incomeType != null and accountIncome.incomeType != '' ">
- AND income_type = #{accountIncome.incomeType}
+ AND ai.income_type = #{accountIncome.incomeType}
</if>
<if test="accountIncome.incomeMethodLabel != null and accountIncome.incomeMethodLabel != ''">
AND (
(
- business_type = 1
- AND income_method in
+ ai.business_type = 1
+ AND ai.income_method in
<foreach collection="accountIncome.receiptPaymentMethodList" item="method" open="(" separator="," close=")">
#{method}
</foreach>
)
OR
(
- business_type is null
- AND income_method in
+ ai.business_type is null
+ AND ai.income_method in
<foreach collection="accountIncome.paymentMethodList" item="method" open="(" separator="," close=")">
#{method}
</foreach>
@@ -37,26 +61,50 @@
)
</if>
<if test="(accountIncome.incomeMethodLabel == null or accountIncome.incomeMethodLabel == '') and accountIncome.incomeMethod != null and accountIncome.incomeMethod != '' ">
- AND income_method = #{accountIncome.incomeMethod}
+ AND ai.income_method = #{accountIncome.incomeMethod}
</if>
</select>
<select id="accountIncomeExport" resultType="com.ruoyi.account.pojo.AccountIncome">
SELECT
- *
- FROM account_income
+ ai.id,
+ ai.business_id,
+ ai.business_type,
+ ai.income_date,
+ ai.income_type,
+ ai.customer_name,
+ ai.income_money,
+ ai.income_described,
+ ai.income_method,
+ ai.invoice_number,
+ ai.note,
+ CASE
+ WHEN TRIM(IFNULL(ai.input_user, '')) REGEXP '^[0-9]+$'
+ THEN COALESCE(ui.nick_name, ai.input_user)
+ ELSE ai.input_user
+ END AS input_user,
+ ai.input_time,
+ ai.create_time,
+ ai.create_user,
+ ai.update_time,
+ ai.update_user,
+ ai.tenant_id
+ FROM account_income ai
+ LEFT JOIN sys_user ui ON TRIM(IFNULL(ai.input_user, '')) REGEXP '^[0-9]+$'
+ AND ui.user_id = CAST(TRIM(ai.input_user) AS UNSIGNED)
+ AND ui.del_flag = '0'
where
1=1
<if test="accountIncome.entryDateStart != null and accountIncome.entryDateStart != '' ">
- AND income_date >= DATE_FORMAT(#{accountIncome.entryDateStart},'%Y-%m-%d')
+ AND ai.income_date >= DATE_FORMAT(#{accountIncome.entryDateStart},'%Y-%m-%d')
</if>
<if test="accountIncome.entryDateEnd != null and accountIncome.entryDateEnd != '' ">
- AND income_date <= DATE_FORMAT(#{accountIncome.entryDateEnd},'%Y-%m-%d')
+ AND ai.income_date <= DATE_FORMAT(#{accountIncome.entryDateEnd},'%Y-%m-%d')
</if>
<if test="accountIncome.incomeType != null and accountIncome.incomeType != '' ">
- AND income_type = #{accountIncome.incomeType}
+ AND ai.income_type = #{accountIncome.incomeType}
</if>
<if test="accountIncome.incomeMethod != null and accountIncome.incomeMethod != '' ">
- AND income_method = #{accountIncome.incomeMethod}
+ AND ai.income_method = #{accountIncome.incomeMethod}
</if>
</select>
<select id="report" resultType="com.ruoyi.account.dto.AccountDto2">
@@ -101,4 +149,16 @@
</select>
+ <update id="syncReceiptIncomeDatesFromReceiptPayment">
+ UPDATE account_income ai
+ INNER JOIN receipt_payment rp ON ai.business_id = rp.id AND ai.business_type = 1
+ SET ai.income_date = DATE(rp.receipt_payment_date),
+ ai.input_time = DATE(rp.receipt_payment_date),
+ ai.create_time = DATE(rp.receipt_payment_date),
+ ai.update_time = DATE(rp.receipt_payment_date)
+ WHERE rp.receipt_payment_date IS NOT NULL
+ AND ai.income_type = '3'
+ AND ai.business_type = 1
+ </update>
+
</mapper>
diff --git a/src/main/resources/mapper/stock/StockInventoryMapper.xml b/src/main/resources/mapper/stock/StockInventoryMapper.xml
index b3b3767..73ae3dc 100644
--- a/src/main/resources/mapper/stock/StockInventoryMapper.xml
+++ b/src/main/resources/mapper/stock/StockInventoryMapper.xml
@@ -380,12 +380,19 @@
</select>
<select id="selectDailyStockInCounts" resultType="java.util.Map">
+ WITH RECURSIVE product_subtree AS (
+ SELECT id FROM product WHERE id = #{rootCategoryId}
+ UNION ALL
+ SELECT pr.id
+ FROM product pr
+ INNER JOIN product_subtree ps ON pr.parent_id = ps.id
+ )
SELECT DATE(sir.create_time) AS date,
SUM(sir.stock_in_num) AS count
FROM stock_in_record sir
JOIN product_model pm ON sir.product_model_id = pm.id
JOIN product p ON pm.product_id = p.id
- WHERE (p.parent_id = #{rootCategoryId} OR p.id = #{rootCategoryId})
+ WHERE p.id IN (SELECT id FROM product_subtree)
AND sir.create_time >= #{startDate}
AND sir.create_time <= #{endDate}
GROUP BY DATE(sir.create_time)
@@ -393,12 +400,19 @@
</select>
<select id="selectDailyStockOutCounts" resultType="java.util.Map">
+ WITH RECURSIVE product_subtree AS (
+ SELECT id FROM product WHERE id = #{rootCategoryId}
+ UNION ALL
+ SELECT pr.id
+ FROM product pr
+ INNER JOIN product_subtree ps ON pr.parent_id = ps.id
+ )
SELECT DATE(sor.create_time) AS date,
SUM(sor.stock_out_num) AS count
FROM stock_out_record sor
JOIN product_model pm ON sor.product_model_id = pm.id
JOIN product p ON pm.product_id = p.id
- WHERE (p.parent_id = #{rootCategoryId} OR p.id = #{rootCategoryId})
+ WHERE p.id IN (SELECT id FROM product_subtree)
AND sor.create_time >= #{startDate}
AND sor.create_time <= #{endDate}
GROUP BY DATE(sor.create_time)
--
Gitblit v1.9.3