From 1bf80aaf74322307f0dadd7023b0802b7858b30c Mon Sep 17 00:00:00 2001
From: guoshilong <123456>
Date: Tue, 07 Nov 2023 15:27:50 +0800
Subject: [PATCH] 查询列表
---
src/main/java/org/springblade/modules/house/vo/HouseRentalTenantVO.java | 25 ++++++++
src/main/java/org/springblade/modules/house/vo/HouseRentalStatistics.java | 19 ++++++
src/main/java/org/springblade/modules/house/mapper/HouseRentalMapper.java | 7 ++
src/main/java/org/springblade/modules/house/service/IHouseRentalService.java | 6 +
src/main/java/org/springblade/modules/house/mapper/HouseRentalMapper.xml | 69 ++++++++++++++++++++++
src/main/java/org/springblade/modules/house/service/impl/HouseRentalServiceImpl.java | 11 ++-
src/main/java/org/springblade/modules/house/controller/HouseRentalController.java | 10 ++
src/main/java/org/springblade/modules/task/mapper/TaskReportForRepairsMapper.xml | 26 +-------
8 files changed, 143 insertions(+), 30 deletions(-)
diff --git a/src/main/java/org/springblade/modules/house/controller/HouseRentalController.java b/src/main/java/org/springblade/modules/house/controller/HouseRentalController.java
index ac3b169..8686fcd 100644
--- a/src/main/java/org/springblade/modules/house/controller/HouseRentalController.java
+++ b/src/main/java/org/springblade/modules/house/controller/HouseRentalController.java
@@ -29,6 +29,7 @@
import org.springblade.core.secure.utils.AuthUtil;
import org.springblade.core.tool.api.R;
import org.springblade.core.tool.utils.Func;
+import org.springblade.modules.house.vo.HouseRentalTenantVO;
import org.springframework.web.bind.annotation.*;
import com.baomidou.mybatisplus.core.metadata.IPage;
import org.springblade.modules.house.entity.HouseRentalEntity;
@@ -80,8 +81,8 @@
@GetMapping("/page")
@ApiOperationSupport(order = 3)
@ApiOperation(value = "分页", notes = "传入houseRental")
- public R<IPage<HouseRentalVO>> page(HouseRentalVO houseRental, Query query) {
- IPage<HouseRentalVO> pages = houseRentalService.selectHouseRentalPage(Condition.getPage(query), houseRental);
+ public R<IPage<HouseRentalTenantVO>> page(HouseRentalTenantVO houseRental, Query query) {
+ IPage<HouseRentalTenantVO> pages = houseRentalService.selectHouseRentalPage(Condition.getPage(query), houseRental);
return R.data(pages);
}
@@ -157,5 +158,10 @@
return R.status(houseRentalService.removeHouseRental(id));
}
+ @GetMapping("/getStatistics")
+ public R getStatistics(){
+ return R.data(houseRentalService.getStatistics());
+ }
+
}
diff --git a/src/main/java/org/springblade/modules/house/mapper/HouseRentalMapper.java b/src/main/java/org/springblade/modules/house/mapper/HouseRentalMapper.java
index 5a48599..9a94a8c 100644
--- a/src/main/java/org/springblade/modules/house/mapper/HouseRentalMapper.java
+++ b/src/main/java/org/springblade/modules/house/mapper/HouseRentalMapper.java
@@ -16,8 +16,11 @@
*/
package org.springblade.modules.house.mapper;
+import liquibase.pro.packaged.P;
import org.apache.ibatis.annotations.Param;
import org.springblade.modules.house.entity.HouseRentalEntity;
+import org.springblade.modules.house.vo.HouseRentalStatistics;
+import org.springblade.modules.house.vo.HouseRentalTenantVO;
import org.springblade.modules.house.vo.HouseRentalVO;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
@@ -38,7 +41,7 @@
* @param houseRental
* @return
*/
- List<HouseRentalVO> selectHouseRentalPage(IPage page, HouseRentalVO houseRental);
+ List<HouseRentalTenantVO> selectHouseRentalPage(IPage page, @Param("vo") HouseRentalTenantVO houseRental);
/**
* 查询房屋出租情况
@@ -46,4 +49,6 @@
* @return
*/
List<HouseRentalVO> getHouseRentalListByCode(@Param("code") String code);
+
+ List<HouseRentalStatistics> getStatistics();
}
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 55acd6b..11a8af9 100644
--- a/src/main/java/org/springblade/modules/house/mapper/HouseRentalMapper.xml
+++ b/src/main/java/org/springblade/modules/house/mapper/HouseRentalMapper.xml
@@ -32,8 +32,29 @@
</resultMap>
- <select id="selectHouseRentalPage" resultMap="houseRentalResultMap">
- select * from jczz_house_rental where is_deleted = 0
+ <select id="selectHouseRentalPage" resultType="org.springblade.modules.house.vo.HouseRentalTenantVO">
+ SELECT jhr.*,jda.address_name as houseName,b.tenantName,b.phone
+ FROM jczz_house_rental jhr
+ LEFT JOIN jczz_doorplate_address jda ON jda.address_code = jhr.house_code
+ LEFT JOIN (
+ SELECT jht.housing_rental_id,jht.name as tenantName,jht.phone
+ FROM jczz_house_tenant jht RIGHT JOIN (
+ SELECT MAX(ID) as id,housing_rental_id
+ FROM jczz_house_tenant
+ WHERE is_deleted = 0
+ <if test="vo.tenantName != null and vo.tenantName != ''">
+ AND name LIKE CONCAT('%',#{vo.tenantName},'%')
+ </if>
+ GROUP BY housing_rental_id
+ ) a ON a.id = jht.id
+ ) b ON b.housing_rental_id = jhr.id
+ WHERE jhr.is_deleted = 0
+ <if test="vo.auditStatus != null and vo.auditStatus != '' or vo.auditStatus == 0 ">
+ AND jhr.audit_status = #{vo.auditStatus}
+ </if>
+ <if test="vo.tenantName != null and vo.tenantName != ''">
+ AND b.tenantName LIKE CONCAT('%',#{vo.tenantName},'%')
+ </if>
</select>
<!--查询房屋出租情况-->
@@ -49,5 +70,49 @@
and house_code = #{code}
</select>
+ <select id="getStatistics" resultType="org.springblade.modules.house.vo.HouseRentalStatistics">
+ SELECT
+ 'longTerm' AS term,COUNT( 1 ) total,temp.personNum
+ FROM(
+ SELECT COUNT( 1 ) personNum
+ FROM jczz_house_rental jhr
+ LEFT JOIN jczz_house_tenant jht ON jht.housing_rental_id = jhr.id
+ WHERE
+ TIMESTAMPDIFF( MONTH, jhr.rental_time, jhr.due_time )>= 8
+ AND jhr.is_deleted = 0 and jht.is_deleted = 0
+ GROUP BY jht.housing_rental_id
+ ) TEMP
+ GROUP BY TEMP.personNum
+
+ UNION ALL
+
+ SELECT 'middleTerm' AS term,COUNT( 1 ) total, temp.personNum
+ FROM(
+ SELECT COUNT( 1 ) personNum
+ FROM jczz_house_rental jhr
+ LEFT JOIN jczz_house_tenant jht ON jht.housing_rental_id = jhr.id
+ WHERE
+ 4 <= TIMESTAMPDIFF( MONTH, rental_time, due_time )
+ AND TIMESTAMPDIFF( MONTH, rental_time, due_time )<=8
+ AND jhr.is_deleted = 0 and jht.is_deleted = 0
+ GROUP BY jht.housing_rental_id
+ ) TEMP
+ GROUP BY TEMP.personNum
+
+ UNION ALL
+
+ SELECT 'shortTerm' AS term,COUNT( 1 ) total,temp.personNum
+ FROM(
+ SELECT COUNT( 1 ) personNum
+ FROM jczz_house_rental jhr
+ LEFT JOIN jczz_house_tenant jht ON jht.housing_rental_id = jhr.id
+ WHERE
+ TIMESTAMPDIFF( MONTH, rental_time, due_time )<4
+ AND jhr.is_deleted = 0 and jht.is_deleted = 0
+ GROUP BY jht.housing_rental_id
+ ) TEMP
+ GROUP BY TEMP.personNum
+ </select>
+
</mapper>
diff --git a/src/main/java/org/springblade/modules/house/service/IHouseRentalService.java b/src/main/java/org/springblade/modules/house/service/IHouseRentalService.java
index 2a62088..eef8008 100644
--- a/src/main/java/org/springblade/modules/house/service/IHouseRentalService.java
+++ b/src/main/java/org/springblade/modules/house/service/IHouseRentalService.java
@@ -18,6 +18,8 @@
import com.baomidou.mybatisplus.extension.service.IService;
import org.springblade.modules.house.entity.HouseRentalEntity;
+import org.springblade.modules.house.vo.HouseRentalStatistics;
+import org.springblade.modules.house.vo.HouseRentalTenantVO;
import org.springblade.modules.house.vo.HouseRentalVO;
import org.springblade.core.mp.base.BaseService;
import com.baomidou.mybatisplus.core.metadata.IPage;
@@ -39,7 +41,7 @@
* @param houseRental
* @return
*/
- IPage<HouseRentalVO> selectHouseRentalPage(IPage<HouseRentalVO> page, HouseRentalVO houseRental);
+ IPage<HouseRentalTenantVO> selectHouseRentalPage(IPage<HouseRentalTenantVO> page, HouseRentalTenantVO houseRental);
/**
* 查询房屋出租情况
@@ -68,4 +70,6 @@
* @return
*/
Boolean updateHouseRental(HouseRentalVO houseRental);
+
+ List<HouseRentalStatistics> getStatistics();
}
diff --git a/src/main/java/org/springblade/modules/house/service/impl/HouseRentalServiceImpl.java b/src/main/java/org/springblade/modules/house/service/impl/HouseRentalServiceImpl.java
index 71195c3..ce1ebb2 100644
--- a/src/main/java/org/springblade/modules/house/service/impl/HouseRentalServiceImpl.java
+++ b/src/main/java/org/springblade/modules/house/service/impl/HouseRentalServiceImpl.java
@@ -22,10 +22,11 @@
import org.springblade.modules.house.entity.HouseRentalEntity;
import org.springblade.modules.house.entity.HouseTenantEntity;
import org.springblade.modules.house.service.IHouseTenantService;
+import org.springblade.modules.house.vo.HouseRentalStatistics;
+import org.springblade.modules.house.vo.HouseRentalTenantVO;
import org.springblade.modules.house.vo.HouseRentalVO;
import org.springblade.modules.house.mapper.HouseRentalMapper;
import org.springblade.modules.house.service.IHouseRentalService;
-import org.springblade.core.mp.base.BaseServiceImpl;
import org.springblade.modules.house.vo.HouseTenantVO;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
@@ -34,7 +35,6 @@
import java.util.ArrayList;
import java.util.Date;
-import java.util.Iterator;
import java.util.List;
import java.util.stream.Collectors;
@@ -57,7 +57,7 @@
* @return
*/
@Override
- public IPage<HouseRentalVO> selectHouseRentalPage(IPage<HouseRentalVO> page, HouseRentalVO houseRental) {
+ public IPage<HouseRentalTenantVO> selectHouseRentalPage(IPage<HouseRentalTenantVO> page, HouseRentalTenantVO houseRental) {
return page.setRecords(baseMapper.selectHouseRentalPage(page, houseRental));
}
@@ -188,4 +188,9 @@
// 返回
return update && addFlag && updateFlag && removeFlag;
}
+
+ @Override
+ public List<HouseRentalStatistics> getStatistics() {
+ return baseMapper.getStatistics();
+ }
}
diff --git a/src/main/java/org/springblade/modules/house/vo/HouseRentalStatistics.java b/src/main/java/org/springblade/modules/house/vo/HouseRentalStatistics.java
new file mode 100644
index 0000000..bd3318b
--- /dev/null
+++ b/src/main/java/org/springblade/modules/house/vo/HouseRentalStatistics.java
@@ -0,0 +1,19 @@
+package org.springblade.modules.house.vo;
+
+import lombok.Data;
+
+/**
+ * 房屋出租统计
+ */
+@Data
+public class HouseRentalStatistics {
+
+ //周期
+ private String term;
+
+ //户数
+ private Integer total;
+
+ //一户的人数
+ private Integer personNum;
+}
diff --git a/src/main/java/org/springblade/modules/house/vo/HouseRentalTenantVO.java b/src/main/java/org/springblade/modules/house/vo/HouseRentalTenantVO.java
new file mode 100644
index 0000000..652e48b
--- /dev/null
+++ b/src/main/java/org/springblade/modules/house/vo/HouseRentalTenantVO.java
@@ -0,0 +1,25 @@
+package org.springblade.modules.house.vo;
+
+import lombok.Data;
+import org.springblade.modules.house.entity.HouseRentalEntity;
+import org.springblade.modules.house.entity.HouseTenantEntity;
+
+import java.util.List;
+
+/**
+ * 房屋租户VO
+ */
+@Data
+public class HouseRentalTenantVO extends HouseRentalEntity {
+
+ //租户名
+ private String tenantName;
+
+ //房屋名
+ private String houseName;
+
+ //电话
+ private String phone;
+
+
+}
diff --git a/src/main/java/org/springblade/modules/task/mapper/TaskReportForRepairsMapper.xml b/src/main/java/org/springblade/modules/task/mapper/TaskReportForRepairsMapper.xml
index 98a861d..f24235e 100644
--- a/src/main/java/org/springblade/modules/task/mapper/TaskReportForRepairsMapper.xml
+++ b/src/main/java/org/springblade/modules/task/mapper/TaskReportForRepairsMapper.xml
@@ -30,28 +30,12 @@
</select>
<select id="getStatisticsCount" resultType="org.springblade.modules.task.vo.TaskReportStatistics">
SELECT
- CASE
- TEMP.TOTAL
- WHEN TEMP.TOTAL != 0 THEN
- TEMP.TOTAL ELSE 0
- END AS TOTAL,
- CASE
- TEMP.HANDLE
- WHEN TEMP.HANDLE != 0 THEN
- TEMP.HANDLE ELSE 0
- END AS HANDLE
+ COUNT( 1 ) AS total,
+ SUM( CASE WHEN STATUS = 10 THEN 1 ELSE 0 END ) AS handle
FROM
- (
- SELECT
- COUNT( 1 ) AS total,
- SUM( CASE WHEN STATUS = 10 THEN 1 ELSE 0 END ) AS handle
- FROM
- jczz_task_report_for_repairs
- WHERE
- is_deleted = 0
- ) TEMP
-
-
+ jczz_task_report_for_repairs
+ WHERE
+ is_deleted = 0
</select>
--
Gitblit v1.9.3