From 8db070c290fb5d176ef372cd9132ab70783e609d Mon Sep 17 00:00:00 2001
From: zhongrj <646384940@qq.com>
Date: Fri, 17 Nov 2023 17:56:04 +0800
Subject: [PATCH] 房屋，租赁导出修改
---
src/main/java/org/springblade/modules/house/mapper/HouseRentalMapper.java | 7 +
src/main/java/org/springblade/modules/house/mapper/HouseMapper.java | 7 +
src/main/java/org/springblade/modules/house/service/IHouseRentalService.java | 7 +
src/main/java/org/springblade/modules/house/excel/HouseExcel.java | 24 ----
src/main/java/org/springblade/modules/house/excel/HouseRentalExcel.java | 77 +++++++--------
src/main/java/org/springblade/modules/house/mapper/HouseRentalMapper.xml | 89 +++++++++++++----
src/main/java/org/springblade/modules/house/service/impl/HouseRentalServiceImpl.java | 7 +
src/main/java/org/springblade/modules/house/controller/HouseRentalController.java | 10 -
src/main/java/org/springblade/modules/house/mapper/HouseMapper.xml | 48 ++++-----
9 files changed, 154 insertions(+), 122 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 092beec..22cdc98 100644
--- a/src/main/java/org/springblade/modules/house/controller/HouseRentalController.java
+++ b/src/main/java/org/springblade/modules/house/controller/HouseRentalController.java
@@ -189,14 +189,12 @@
}
/**
- * 导出用户
+ * 导出租赁信息
*/
- @GetMapping("export-house")
- @ApiOperationSupport(order = 13)
- @ApiOperation(value = "导出出租房屋", notes = "传入user")
- public void exportUser(HouseRentalVO houseRentalVO, HttpServletResponse response) {
+ @GetMapping("export-houseRental")
+ public void exportHouseRental(HouseRentalTenantVO houseRentalVO, HttpServletResponse response) {
List<HouseRentalExcel> list = houseRentalService.export(houseRentalVO);
- ExcelUtil.export(response, "房屋数据" + DateUtil.time(), "房屋数据表", list, HouseRentalExcel.class);
+ ExcelUtil.export(response, "出租屋数据" + DateUtil.time(), "出租屋数据表", list, HouseRentalExcel.class);
}
diff --git a/src/main/java/org/springblade/modules/house/excel/HouseExcel.java b/src/main/java/org/springblade/modules/house/excel/HouseExcel.java
index 816e25b..3231913 100644
--- a/src/main/java/org/springblade/modules/house/excel/HouseExcel.java
+++ b/src/main/java/org/springblade/modules/house/excel/HouseExcel.java
@@ -75,29 +75,7 @@
/** 幢编号 */
@ExcelProperty( "幢编号")
- private Integer buildingNo;
-
- /** 图片URLS */
- @ExcelProperty( "图片URLS")
- private String imageUrls;
-
- /** 创建人 */
- @ExcelProperty( "创建人")
- private String createUser;
-
- /** 创建时间 */
- @ExcelProperty( "创建时间")
- @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
- private Date createTime;
-
- /** 更新人 */
- @ExcelProperty( "更新人")
- private String updateUser;
-
- /** 更新时间 */
- @ExcelProperty( "更新时间")
- @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
- private Date updateTime;
+ private String buildingNo;
/** 备注 */
@ExcelProperty( "备注")
diff --git a/src/main/java/org/springblade/modules/house/excel/HouseRentalExcel.java b/src/main/java/org/springblade/modules/house/excel/HouseRentalExcel.java
index 6a19822..1467d87 100644
--- a/src/main/java/org/springblade/modules/house/excel/HouseRentalExcel.java
+++ b/src/main/java/org/springblade/modules/house/excel/HouseRentalExcel.java
@@ -6,6 +6,8 @@
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.Data;
+import org.springblade.common.excel.ExcelDictConverter;
+import org.springblade.common.excel.ExcelDictItem;
import java.io.Serializable;
import java.util.Date;
@@ -23,68 +25,61 @@
private static final long serialVersionUID = 2L;
- /** 主键ID */
- @ExcelProperty( "主键ID")
- private Long id;
-
- /** 门牌地址编码 */
- @ExcelProperty( "门牌地址编码")
- private String houseCode;
+ /** 房屋 */
+ @ExcelProperty( "房屋")
+ @ColumnWidth(25)
+ private String address;
/** 租客关系 1: 同一户 2：不同一户 */
- @ExcelProperty( "租客关系 1: 同一户 2：不同一户")
- private Integer tenantRelationship;
+ @ColumnWidth(15)
+ @ExcelProperty( value = "租客关系",converter = ExcelDictConverter.class)
+ @ExcelDictItem(type = "partyEmberType")
+ private String tenantRelationship;
/** 租房时间 */
@ExcelProperty( "租房时间")
+ @ColumnWidth(15)
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
private Date rentalTime;
/** 到期时间 */
@ExcelProperty( "到期时间")
+ @ColumnWidth(15)
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
private Date dueTime;
- /** 终止时间 */
- @ExcelProperty( "终止时间")
- @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
- private Date terminationTime;
+// /** 终止时间 */
+// @ExcelProperty( "终止时间")
+// @ColumnWidth(15)
+// @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
+// private Date terminationTime;
/** 房屋状态 1：部分出租 2：全部出租 */
- @ExcelProperty( "房屋状态 1：部分出租 2：全部出租")
- private Integer houseStatus;
+ @ColumnWidth(15)
+ @ExcelProperty( value = "房屋状态",converter = ExcelDictConverter.class)
+ @ExcelDictItem(type = "houseStatusType")
+ private String houseStatus;
/** 租房用途 1：仓库 2：办公 3：商用 4：居住 */
- @ExcelProperty( "租房用途 1：仓库 2：办公 3：商用 4：居住")
- private Integer rentalUse;
+ @ColumnWidth(15)
+ @ExcelProperty( value = "租房用途",converter = ExcelDictConverter.class)
+ @ExcelDictItem(type = "rentalUseType")
+ private String rentalUse;
- /** 审核状态 0: 待审核 1: 审核通过 2: 审核不通过 10未到期； 20即将到期 ；30 已到期 */
- @ExcelProperty( "审核状态 0: 待审核 1: 审核通过 2: 审核不通过 10未到期； 20即将到期 ；30 已到期")
- private Integer auditStatus;
+ /** 审核状态 0: 待确认 1: 已确认 */
+ @ColumnWidth(15)
+ @ExcelProperty( value = "审核状态",converter = ExcelDictConverter.class)
+ @ExcelDictItem(type = "confirmStatus")
+ private String auditStatus;
- /** 合同附件URL */
- @ExcelProperty( "合同附件URL")
- private String fileUrls;
-
- /** 创建人 */
- @ExcelProperty( "创建人")
- private Long createUser;
-
- /** 创建时间 */
- @ExcelProperty( "创建时间")
- @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
- private Date createTime;
-
- /** 更新人 */
- @ExcelProperty( "更新人")
- private Long updateUser;
-
- /** 更新时间 */
- @ExcelProperty( "更新时间")
- @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
- private Date updateTime;
+ /** 租赁期限 */
+ @ColumnWidth(15)
+ @ExcelProperty( value = "租赁期限",converter = ExcelDictConverter.class)
+ @ExcelDictItem(type = "dldType")
+ private String dldType;
/** 备注 */
+ @ColumnWidth(15)
@ExcelProperty( "备注")
private String remark;
diff --git a/src/main/java/org/springblade/modules/house/mapper/HouseMapper.java b/src/main/java/org/springblade/modules/house/mapper/HouseMapper.java
index 024a6bc..ab5fc56 100644
--- a/src/main/java/org/springblade/modules/house/mapper/HouseMapper.java
+++ b/src/main/java/org/springblade/modules/house/mapper/HouseMapper.java
@@ -53,7 +53,12 @@
*/
HouseVO getHouseDetail(@Param("house") HouseVO house);
- List<HouseExcel> export(HouseVO household);
+ /**
+ * 导出房屋数据
+ * @param house
+ * @return
+ */
+ List<HouseExcel> export(@Param("house") HouseVO house);
/**
* 查询房屋树
diff --git a/src/main/java/org/springblade/modules/house/mapper/HouseMapper.xml b/src/main/java/org/springblade/modules/house/mapper/HouseMapper.xml
index e5f9420..aa3fb5d 100644
--- a/src/main/java/org/springblade/modules/house/mapper/HouseMapper.xml
+++ b/src/main/java/org/springblade/modules/house/mapper/HouseMapper.xml
@@ -117,33 +117,29 @@
and jh.house_code = #{house.houseCode}
</select>
-
+ <!--房屋数据导出-->
<select id="export" resultType="org.springblade.modules.house.excel.HouseExcel">
- <include refid="selectHouse"/>
- <where>
- <if test="id != null "> and id = #{id}</if>
- <if test="houseCode != null and houseCode != ''"> and house_code = #{houseCode}</if>
- <if test="districtCode != null and districtCode != ''"> and district_code = #{districtCode}</if>
- <if test="districtName != null and districtName != ''"> and district_name = #{districtName}</if>
- <if test="houseName != null and houseName != ''"> and house_name = #{houseName}</if>
- <if test="phone != null and phone != ''"> and phone = #{phone}</if>
- <if test="area != null "> and area = #{area}</if>
- <if test="propertyPrice != null "> and property_price = #{propertyPrice}</if>
- <if test="serviceDue != null "> and service_due = #{serviceDue}</if>
- <if test="floor != null "> and floor = #{floor}</if>
- <if test="building != null and building != ''"> and building = #{building}</if>
- <if test="unit != null and unit != ''"> and unit = #{unit}</if>
- <if test="room != null and room != ''"> and room = #{room}</if>
- <if test="buildingNo != null "> and building_no = #{buildingNo}</if>
- <if test="imageUrls != null and imageUrls != ''"> and image_urls = #{imageUrls}</if>
- <if test="createUser != null and createUser != ''"> and create_user = #{createUser}</if>
- <if test="createTime != null "> and create_time = #{createTime}</if>
- <if test="updateUser != null and updateUser != ''"> and update_user = #{updateUser}</if>
- <if test="updateTime != null "> and update_time = #{updateTime}</if>
- <if test="remark != null and remark != ''"> and remark = #{remark}</if>
- <if test="isDeleted != null "> and is_deleted = #{isDeleted}</if>
- </where>
-
+ select
+ *,
+ concat(building," ",unit," ",room) as address
+ from jczz_house
+ where is_deleted = 0
+ <if test="house.id != null "> and id = #{house.id}</if>
+ <if test="house.houseCode != null and house.houseCode != ''"> and house_code = #{house.houseCode}</if>
+ <if test="house.districtCode != null and house.districtCode != ''"> and district_code = #{house.districtCode}</if>
+ <if test="house.districtName != null and house.districtName != ''">
+ and district_name like concat('%',#{house.districtName},'%')
+ </if>
+ <if test="house.houseName != null and house.houseName != ''"> and house_name = #{house.houseName}</if>
+ <if test="house.phone != null and house.phone != ''"> and phone = #{house.phone}</if>
+ <if test="house.area != null "> and area = #{house.area}</if>
+ <if test="house.propertyPrice != null "> and property_price = #{house.propertyPrice}</if>
+ <if test="house.serviceDue != null "> and service_due = #{house.serviceDue}</if>
+ <if test="house.floor != null "> and floor = #{house.floor}</if>
+ <if test="house.building != null and house.building != ''"> and building = #{house.building}</if>
+ <if test="house.unit != null and house.unit != ''"> and unit = #{house.unit}</if>
+ <if test="house.room != null and house.room != ''"> and room = #{house.room}</if>
+ <if test="house.buildingNo != null "> and building_no = #{house.buildingNo}</if>
</select>
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 6550d65..458ec1f 100644
--- a/src/main/java/org/springblade/modules/house/mapper/HouseRentalMapper.java
+++ b/src/main/java/org/springblade/modules/house/mapper/HouseRentalMapper.java
@@ -60,7 +60,12 @@
List<HouseRentalStatistics> getStatistics(@Param("vo") HouseRentalTenantVO houseRental,
@Param("list") List<String> list);
- List<HouseRentalExcel> export(HouseRentalVO houseRentalVO);
+ /**
+ * 导出租赁信息
+ * @param houseRental
+ * @return
+ */
+ List<HouseRentalExcel> export(@Param("vo") HouseRentalTenantVO houseRental);
Integer getStatisticsCount(Long userId);
}
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 f666ffd..65bc733 100644
--- a/src/main/java/org/springblade/modules/house/mapper/HouseRentalMapper.xml
+++ b/src/main/java/org/springblade/modules/house/mapper/HouseRentalMapper.xml
@@ -206,29 +206,74 @@
<include refid="filterHouseGrid"/>
</select>
-
+ <!--导出租赁信息-->
<select id="export" resultType="org.springblade.modules.house.excel.HouseRentalExcel">
-
- <include refid="selectHouseRental"/>
- <where>
- <if test="id != null "> and id = #{id}</if>
- <if test="houseCode != null and houseCode != ''"> and house_code = #{houseCode}</if>
- <if test="tenantRelationship != null "> and tenant_relationship = #{tenantRelationship}</if>
- <if test="rentalTime != null "> and rental_time = #{rentalTime}</if>
- <if test="dueTime != null "> and due_time = #{dueTime}</if>
- <if test="terminationTime != null "> and termination_time = #{terminationTime}</if>
- <if test="houseStatus != null "> and house_status = #{houseStatus}</if>
- <if test="rentalUse != null "> and rental_use = #{rentalUse}</if>
- <if test="auditStatus != null "> and audit_status = #{auditStatus}</if>
- <if test="fileUrls != null and fileUrls != ''"> and file_urls = #{fileUrls}</if>
- <if test="createUser != null "> and create_user = #{createUser}</if>
- <if test="createTime != null "> and create_time = #{createTime}</if>
- <if test="updateUser != null "> and update_user = #{updateUser}</if>
- <if test="updateTime != null "> and update_time = #{updateTime}</if>
- <if test="remark != null and remark != ''"> and remark = #{remark}</if>
- <if test="isDeleted != null "> and is_deleted = #{isDeleted}</if>
- </where>
-
+ SELECT
+ jhr.tenant_relationship,jhr.rental_time,jhr.due_time,jhr.house_status,
+ jhr.rental_use,jhr.audit_status,jhr.remark,
+ jda.address_name as houseName,b.tenantName,b.phone,
+ concat(jh.district_name," ",jh.building," ",unit," ",room) as address,
+ case when TIMESTAMPDIFF( MONTH, jhr.rental_time, jhr.due_time )>= 8 then 1
+ when TIMESTAMPDIFF( MONTH, jhr.rental_time, jhr.due_time )<4 then 3
+ else 2 end as dldType
+ FROM jczz_house_rental jhr
+ 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 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 != ''">
+ <if test="vo.auditStatus ==1">
+ AND jhr.audit_status = 1
+ </if>
+ <if test="vo.auditStatus ==2">
+ AND jhr.audit_status = 0
+ </if>
+ <if test="vo.auditStatus ==10">
+ AND date_format(jhr.due_time,'%Y-%m-%d')>= date_format(now(),'%Y-%m-%d')
+ </if>
+ <if test="vo.auditStatus ==20">
+ AND TIMESTAMPDIFF( day, now(), jhr.due_time )<30
+ </if>
+ <if test="vo.auditStatus ==30">
+ AND date_format(jhr.due_time,'%Y-%m-%d')< date_format(now(),'%Y-%m-%d')
+ </if>
+ </if>
+ <if test="vo.tenantName != null and vo.tenantName != ''">
+ AND b.tenantName LIKE CONCAT('%',#{vo.tenantName},'%')
+ </if>
+ <if test="vo.tenantRelationship != null">
+ AND jhr.tenant_relationship = #{vo.tenantRelationship}
+ </if>
+ <if test="vo.houseStatus != null">
+ AND jhr.house_status = #{vo.houseStatus}
+ </if>
+ <if test="vo.rentalUse != null">
+ AND jhr.rental_use = #{vo.rentalUse}
+ </if>
+ <if test="vo.dldType != null">
+ <if test="vo.dldType ==1 ">
+ AND TIMESTAMPDIFF( MONTH, jhr.rental_time, jhr.due_time )>= 8
+ </if>
+ <if test="vo.dldType ==2 ">
+ AND 4 <= TIMESTAMPDIFF( MONTH, jhr.rental_time, jhr.due_time )
+ AND TIMESTAMPDIFF( MONTH, jhr.rental_time, jhr.due_time )<=8
+ </if>
+ <if test="vo.dldType ==3 ">
+ AND TIMESTAMPDIFF( MONTH, jhr.rental_time, jhr.due_time )<4
+ </if>
+ </if>
+ <include refid="filterHouseGrid"/>
</select>
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 6f7f165..ab06eb4 100644
--- a/src/main/java/org/springblade/modules/house/service/IHouseRentalService.java
+++ b/src/main/java/org/springblade/modules/house/service/IHouseRentalService.java
@@ -83,7 +83,12 @@
*/
Boolean confirmHouseRental(HouseRentalVO houseRental);
- List<HouseRentalExcel> export(HouseRentalVO houseRentalVO);
+ /**
+ * 导出租赁信息
+ * @param houseRentalVO
+ * @return
+ */
+ List<HouseRentalExcel> export(HouseRentalTenantVO houseRentalVO);
Integer getStatisticsCount(HouseRentalTenantVO houseRental);
}
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 d545b9a..1e02036 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
@@ -238,8 +238,13 @@
return updateById(houseRental);
}
+ /**
+ * 导出租赁信息
+ * @param houseRentalVO
+ * @return
+ */
@Override
- public List<HouseRentalExcel> export(HouseRentalVO houseRentalVO) {
+ public List<HouseRentalExcel> export(HouseRentalTenantVO houseRentalVO) {
List<HouseRentalExcel> houseRentalExcels = baseMapper.export(houseRentalVO);
return houseRentalExcels;
}
--
Gitblit v1.9.3