From aa62314c186de81906dc26289edb2c1865482a2e Mon Sep 17 00:00:00 2001
From: guoshilong <123456>
Date: Fri, 23 Sep 2022 11:02:44 +0800
Subject: [PATCH] 数据统计sql加上if判断，巡检人员多查询一个字段手机号
---
src/main/java/org/springblade/modules/userstatistics/mapper/UserStatisticsMapper.java | 2 +-
src/main/java/org/springblade/modules/taskinfo/mapper/TaskplanUserMapper.xml | 2 +-
src/main/java/org/springblade/modules/userstatistics/mapper/UserStatisticsMapper.xml | 32 ++++++++++++++++++++++++--------
src/main/java/org/springblade/modules/taskinfo/vo/TaskplanUserVO.java | 2 ++
4 files changed, 28 insertions(+), 10 deletions(-)
diff --git a/src/main/java/org/springblade/modules/taskinfo/mapper/TaskplanUserMapper.xml b/src/main/java/org/springblade/modules/taskinfo/mapper/TaskplanUserMapper.xml
index 587fe02..96dd2b1 100644
--- a/src/main/java/org/springblade/modules/taskinfo/mapper/TaskplanUserMapper.xml
+++ b/src/main/java/org/springblade/modules/taskinfo/mapper/TaskplanUserMapper.xml
@@ -14,7 +14,7 @@
<select id="selectTaskplanUserPage" resultMap="taskplanUserResultMap">
- select itu.*,bu.name
+ select itu.*,bu.name,bu.phone
from ins_taskplan_user itu
LEFT JOIN blade_user bu ON itu.user_id = bu.id
where itu.is_deleted = 0
diff --git a/src/main/java/org/springblade/modules/taskinfo/vo/TaskplanUserVO.java b/src/main/java/org/springblade/modules/taskinfo/vo/TaskplanUserVO.java
index 3aef612..2ef7a3b 100644
--- a/src/main/java/org/springblade/modules/taskinfo/vo/TaskplanUserVO.java
+++ b/src/main/java/org/springblade/modules/taskinfo/vo/TaskplanUserVO.java
@@ -33,4 +33,6 @@
private static final long serialVersionUID = 1L;
private String name;
+
+ private String phone;
}
diff --git a/src/main/java/org/springblade/modules/userstatistics/mapper/UserStatisticsMapper.java b/src/main/java/org/springblade/modules/userstatistics/mapper/UserStatisticsMapper.java
index bf173bf..eeb0a95 100644
--- a/src/main/java/org/springblade/modules/userstatistics/mapper/UserStatisticsMapper.java
+++ b/src/main/java/org/springblade/modules/userstatistics/mapper/UserStatisticsMapper.java
@@ -10,6 +10,6 @@
import java.util.Map;
@Repository
-public interface UserStatisticsMapper extends BaseMapper {
+public interface UserStatisticsMapper {
List<Map<String,String>> getRepairTask(IPage page,@Param("startTime") String startTime, @Param("endTime") String endTime);
}
diff --git a/src/main/java/org/springblade/modules/userstatistics/mapper/UserStatisticsMapper.xml b/src/main/java/org/springblade/modules/userstatistics/mapper/UserStatisticsMapper.xml
index 05a7a04..63a5b20 100644
--- a/src/main/java/org/springblade/modules/userstatistics/mapper/UserStatisticsMapper.xml
+++ b/src/main/java/org/springblade/modules/userstatistics/mapper/UserStatisticsMapper.xml
@@ -14,26 +14,42 @@
LEFT JOIN blade_role role ON user.role_id = role.id
LEFT JOIN (
SELECT DISTINCT re.to_user_id,count(*) as num FROM ins_repairsorder re WHERE re.is_deleted = 0 AND re.state = 1 -- 状态state = 1 派单的数据
- AND DATE_FORMAT(re.create_time,'%Y-%m-%d') >= #{startTime}
- AND DATE_FORMAT(re.create_time,'%Y-%m-%d') <= #{endTime}
+ <if test="startTime !=null and startTime !=''">
+ AND DATE_FORMAT(re.create_time,'%Y-%m-%d') >= #{startTime}
+ </if>
+ <if test="endTime != null and endTime !='' ">
+ AND DATE_FORMAT(re.create_time,'%Y-%m-%d') <= #{endTime}
+ </if>
GROUP BY re.to_user_id
) a ON user.id = a.to_user_id
LEFT JOIN (
SELECT DISTINCT re.to_user_id,count(*) as num FROM ins_repairsorder re WHERE re.is_deleted = 0 AND re.state = 2 -- 状态state = 2 完成派单
- AND DATE_FORMAT(re.create_time,'%Y-%m-%d') >= #{startTime}
- AND DATE_FORMAT(re.create_time,'%Y-%m-%d') <= #{endTime}
+ <if test="startTime !=null and startTime !=''">
+ AND DATE_FORMAT(re.create_time,'%Y-%m-%d') >= #{startTime}
+ </if>
+ <if test="endTime != null and endTime !='' ">
+ AND DATE_FORMAT(re.create_time,'%Y-%m-%d') <= #{endTime}
+ </if>
GROUP BY re.to_user_id
) b ON user.id = b.to_user_id
LEFT JOIN (
SELECT DISTINCT sk.to_user_id,count(*) as num FROM ins_taskinfo sk WHERE sk.is_deleted = 0 AND sk.state = 0
- AND DATE_FORMAT(sk.create_time,'%Y-%m-%d') >= #{startTime}
- AND DATE_FORMAT(sk.create_time,'%Y-%m-%d') <= #{endTime}
+ <if test="startTime !=null and startTime !=''">
+ AND DATE_FORMAT(re.create_time,'%Y-%m-%d') >= #{startTime}
+ </if>
+ <if test="endTime != null and endTime !='' ">
+ AND DATE_FORMAT(re.create_time,'%Y-%m-%d') <= #{endTime}
+ </if>
GROUP BY sk.to_user_id
) c ON user.id = c.to_user_id
LEFT JOIN (
SELECT DISTINCT sk.to_user_id,count(*) as num FROM ins_taskinfo sk WHERE sk.is_deleted = 0 AND sk.state = 2
- AND DATE_FORMAT(sk.create_time,'%Y-%m-%d') >= #{startTime}
- AND DATE_FORMAT(sk.create_time,'%Y-%m-%d') <= #{endTime}
+ <if test="startTime !=null and startTime !=''">
+ AND DATE_FORMAT(re.create_time,'%Y-%m-%d') >= #{startTime}
+ </if>
+ <if test="endTime != null and endTime !='' ">
+ AND DATE_FORMAT(re.create_time,'%Y-%m-%d') <= #{endTime}
+ </if>
GROUP BY sk.to_user_id
) d ON user.id = d.to_user_id
WHERE (role.role_alias = 'wx' or role.role_alias = 'xj') and user.is_deleted = 0
--
Gitblit v1.9.3