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); }
@@ -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( "备注") 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;
@@ -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); /** * 查询房屋树 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>
@@ -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); } 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>
@@ -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); } 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; }
