From 27aba12a2708699d5bab06db3d6f508da595b7b7 Mon Sep 17 00:00:00 2001
From: linwe <872216996@qq.com>
Date: Wed, 06 Dec 2023 15:47:56 +0800
Subject: [PATCH] 查询网格 bug 修改
---
src/main/java/org/springblade/modules/house/mapper/HouseholdMapper.xml | 17 ++++----
src/main/java/org/springblade/modules/house/mapper/HouseRentalMapper.xml | 100 ++++++++++++++++++++++++++------------------------
2 files changed, 60 insertions(+), 57 deletions(-)
diff --git a/src/main/java/org/springblade/modules/house/mapper/HouseRentalMapper.xml b/src/main/java/org/springblade/modules/house/mapper/HouseRentalMapper.xml
index a149486..a018fd9 100644
--- a/src/main/java/org/springblade/modules/house/mapper/HouseRentalMapper.xml
+++ b/src/main/java/org/springblade/modules/house/mapper/HouseRentalMapper.xml
@@ -156,54 +156,58 @@
</sql>
<select id="getStatistics" resultType="org.springblade.modules.house.vo.HouseRentalStatistics">
- SELECT
- 'longTerm' as term,count(1) total,ifnull(sum(num),0) as personNum
- FROM jczz_house_rental jhr
- LEFT JOIN
- (
- select housing_rental_id,count(*) num from jczz_house_tenant
- where is_deleted = 0
- GROUP BY housing_rental_id
- ) jht
- ON jht.housing_rental_id = jhr.id
- LEFT JOIN jczz_doorplate_address jda ON jda.address_code = jhr.house_code
- WHERE jhr.is_deleted = 0
- AND TIMESTAMPDIFF( MONTH, jhr.rental_time, jhr.due_time )>= 8
- <if test="vo.auditStatus != null and vo.auditStatus != '' or vo.auditStatus == 0 ">
- AND jhr.audit_status = #{vo.auditStatus}
- </if>
- <include refid="filterHouseGrid"/>
+ SELECT
+ 'longTerm' as term,count(1) total,ifnull(sum(num),0) as personNum
+ FROM jczz_house_rental jhr
+ LEFT JOIN
+ (
+ select housing_rental_id,count(*) num from jczz_household
+ where is_deleted = 0 and housing_rental_id is not null
+ GROUP BY housing_rental_id
+ ) jht
+ ON jht.housing_rental_id = jhr.id
+ LEFT JOIN jczz_doorplate_address jda ON jda.address_code = jhr.house_code
+ WHERE jhr.is_deleted = 0
+ AND TIMESTAMPDIFF( MONTH, jhr.rental_time, jhr.due_time )>= 8
+ <if test="vo.auditStatus != null and vo.auditStatus != '' or vo.auditStatus == 0 ">
+ AND jhr.audit_status = #{vo.auditStatus}
+ </if>
+ <include refid="filterHouseGrid"/>
- UNION ALL
+ UNION ALL
- SELECT 'middleTerm' AS term,count(1) total,ifnull(sum(num),0) as personNum
- FROM jczz_house_rental jhr
- LEFT JOIN
- (
- select housing_rental_id,count(*) num from jczz_house_tenant where is_deleted = 0 GROUP BY housing_rental_id
- ) jht
- ON jht.housing_rental_id = jhr.id
- LEFT JOIN jczz_doorplate_address jda ON jda.address_code = jhr.house_code
- WHERE jhr.is_deleted = 0
- AND 4 <= TIMESTAMPDIFF( MONTH, rental_time, due_time ) AND TIMESTAMPDIFF( MONTH, rental_time, due_time )<=8
- <if test="vo.auditStatus != null and vo.auditStatus != '' or vo.auditStatus == 0 ">
- AND jhr.audit_status = #{vo.auditStatus}
- </if>
- <include refid="filterHouseGrid"/>
+ SELECT 'middleTerm' AS term,count(1) total,ifnull(sum(num),0) as personNum
+ FROM jczz_house_rental jhr
+ LEFT JOIN
+ (
+ select housing_rental_id,count(*) num from jczz_household
+ where is_deleted = 0 and housing_rental_id is not null
+ GROUP BY housing_rental_id
+ ) jht
+ ON jht.housing_rental_id = jhr.id
+ LEFT JOIN jczz_doorplate_address jda ON jda.address_code = jhr.house_code
+ WHERE jhr.is_deleted = 0
+ AND 4 <= TIMESTAMPDIFF( MONTH, rental_time, due_time ) AND TIMESTAMPDIFF( MONTH, rental_time, due_time )<=8
+ <if test="vo.auditStatus != null and vo.auditStatus != '' or vo.auditStatus == 0 ">
+ AND jhr.audit_status = #{vo.auditStatus}
+ </if>
+ <include refid="filterHouseGrid"/>
- UNION ALL
+ UNION ALL
- SELECT 'shortTerm' AS term,count(1) total,ifnull(sum(num),0) as personNum
- FROM jczz_house_rental jhr
- LEFT JOIN
- (
- select housing_rental_id,count(*) num from jczz_house_tenant where is_deleted = 0 GROUP BY housing_rental_id
- ) jht
- ON jht.housing_rental_id = jhr.id
- LEFT JOIN jczz_doorplate_address jda ON jda.address_code = jhr.house_code
- WHERE jhr.is_deleted = 0
- AND TIMESTAMPDIFF( MONTH, rental_time, due_time )<4
- <if test="vo.auditStatus != null and vo.auditStatus != '' or vo.auditStatus == 0 ">
+ SELECT 'shortTerm' AS term,count(1) total,ifnull(sum(num),0) as personNum
+ FROM jczz_house_rental jhr
+ LEFT JOIN
+ (
+ select housing_rental_id,count(*) num from jczz_household
+ where is_deleted = 0 and housing_rental_id is not null
+ GROUP BY housing_rental_id
+ ) jht
+ ON jht.housing_rental_id = jhr.id
+ LEFT JOIN jczz_doorplate_address jda ON jda.address_code = jhr.house_code
+ WHERE jhr.is_deleted = 0
+ AND TIMESTAMPDIFF( MONTH, rental_time, due_time )<4
+ <if test="vo.auditStatus != null and vo.auditStatus != '' or vo.auditStatus == 0 ">
AND jhr.audit_status = #{vo.auditStatus}
</if>
<include refid="filterHouseGrid"/>
@@ -223,11 +227,11 @@
JOIN jczz_doorplate_address jda ON jda.address_code = jhr.house_code
JOIN jczz_house jh ON jh.house_code = jhr.house_code and jh.is_deleted = 0
LEFT JOIN (
- SELECT jht.housing_rental_id,jht.name as tenantName,jht.phone
- FROM jczz_house_tenant jht RIGHT JOIN (
+ SELECT jht.housing_rental_id,jht.name as tenantName,jht.phoneNumber phone
+ FROM jczz_household jht RIGHT JOIN (
SELECT MAX(ID) as id,housing_rental_id
- FROM jczz_house_tenant
- WHERE is_deleted = 0
+ FROM jczz_household
+ WHERE is_deleted = 0 and house_rental_id is not null
<if test="vo.tenantName != null and vo.tenantName != ''">
AND name LIKE CONCAT('%',#{vo.tenantName},'%')
</if>
diff --git a/src/main/java/org/springblade/modules/house/mapper/HouseholdMapper.xml b/src/main/java/org/springblade/modules/house/mapper/HouseholdMapper.xml
index 752192d..2cd2896 100644
--- a/src/main/java/org/springblade/modules/house/mapper/HouseholdMapper.xml
+++ b/src/main/java/org/springblade/modules/house/mapper/HouseholdMapper.xml
@@ -215,15 +215,14 @@
<!--查询网格-->
<select id="getGrid" resultType="org.springblade.modules.house.vo.HouseholdOtherVO">
- SELECT
- jg.id as code,
- jgm.gridman_name as name,jgm.mobile as phone
- FROM
- jczz_grid_range jgr
- LEFT JOIN jczz_grid jg ON jg.id = jgr.grid_id and jg.is_deleted = 0
- LEFT JOIN jczz_gridman jgm ON jg.id = jgm.grid_id and jgm.is_deleted = 0
- WHERE 1=1
- AND jgr.house_code = #{household.houseCode}
+ SELECT jg.id as code,
+ jgm.gridman_name as name,
+ jgm.mobile as phone
+ FROM jczz_grid_range jgr
+ LEFT JOIN jczz_grid jg ON jg.id = jgr.grid_id and jg.is_deleted = 0
+ LEFT JOIN jczz_gridman jgm ON jg.id = jgm.grid_id and jgm.is_deleted = 0
+ WHERE 1 = 1
+ AND jgr.house_code = #{household.houseCode} limit 1
</select>
<!--查询公安信息-->
--
Gitblit v1.9.3