From dde162ffb34f2cc0ece4ca9cfcd09e2cb42ace6d Mon Sep 17 00:00:00 2001
From: linwe <872216996@qq.com>
Date: Fri, 17 May 2024 18:13:11 +0800
Subject: [PATCH] 租户导入导出
---
src/main/java/org/springblade/modules/house/mapper/HouseTenantMapper.java | 8 +
src/main/java/org/springblade/modules/house/service/impl/HouseTenantServiceImpl.java | 74 +++++++++++-
src/main/java/org/springblade/modules/house/excel/HouseTenantExcel.java | 82 +++++++++++++
src/main/java/org/springblade/modules/house/vo/HouseTenantVO.java | 3
src/main/java/org/springblade/modules/house/controller/HouseTenantController.java | 51 +++++++-
src/main/java/org/springblade/modules/house/mapper/HouseTenantMapper.xml | 23 +++
src/main/java/org/springblade/modules/house/service/IHouseTenantService.java | 12 ++
src/main/java/org/springblade/modules/house/excel/ExportHouseTenantExcel.java | 93 +++++++++++++++
8 files changed, 331 insertions(+), 15 deletions(-)
diff --git a/src/main/java/org/springblade/modules/house/controller/HouseTenantController.java b/src/main/java/org/springblade/modules/house/controller/HouseTenantController.java
index 3608b30..3c3d46e 100644
--- a/src/main/java/org/springblade/modules/house/controller/HouseTenantController.java
+++ b/src/main/java/org/springblade/modules/house/controller/HouseTenantController.java
@@ -16,25 +16,33 @@
*/
package org.springblade.modules.house.controller;
+import com.baomidou.mybatisplus.core.metadata.IPage;
+import com.github.xiaoymin.knife4j.annotations.ApiOperationSupport;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import io.swagger.annotations.ApiParam;
-import com.github.xiaoymin.knife4j.annotations.ApiOperationSupport;
import lombok.AllArgsConstructor;
-import javax.validation.Valid;
-
-import org.springblade.core.secure.BladeUser;
+import org.springblade.core.boot.ctrl.BladeController;
+import org.springblade.core.excel.util.ExcelUtil;
import org.springblade.core.mp.support.Condition;
import org.springblade.core.mp.support.Query;
import org.springblade.core.tool.api.R;
+import org.springblade.core.tool.utils.DateUtil;
import org.springblade.core.tool.utils.Func;
-import org.springframework.web.bind.annotation.*;
-import com.baomidou.mybatisplus.core.metadata.IPage;
import org.springblade.modules.house.entity.HouseTenantEntity;
+import org.springblade.modules.house.excel.ExportHouseTenantExcel;
+import org.springblade.modules.house.excel.HouseTenantExcel;
+import org.springblade.modules.house.excel.ImportHouseholdExcel;
+import org.springblade.modules.house.service.IHouseTenantService;
import org.springblade.modules.house.vo.HouseTenantVO;
import org.springblade.modules.house.wrapper.HouseTenantWrapper;
-import org.springblade.modules.house.service.IHouseTenantService;
-import org.springblade.core.boot.ctrl.BladeController;
+import org.springframework.web.bind.annotation.*;
+import org.springframework.web.multipart.MultipartFile;
+
+import javax.servlet.http.HttpServletResponse;
+import javax.validation.Valid;
+import java.util.ArrayList;
+import java.util.List;
/**
* 租户管理 控制器
@@ -122,5 +130,32 @@
return R.status(houseTenantService.removeByIds(Func.toLongList(ids)));
}
+ @GetMapping("downLoadTenantTemplate")
+ @ApiOperationSupport(order = 14)
+ @ApiOperation(value = "导出模板")
+ public void downLoadTenantTemplate(HttpServletResponse response) {
+ List<HouseTenantExcel> list = new ArrayList<>();
+ ExcelUtil.export(response, "租客数据模板", "租客数据表", list, HouseTenantExcel.class);
+ }
+
+ @GetMapping("export-tenant")
+ @ApiOperationSupport(order = 15)
+ @ApiOperation(value = "导出租客")
+ public void exportTenant(HttpServletResponse response) {
+ List<ExportHouseTenantExcel> list = houseTenantService.export();
+ ExcelUtil.export(response, "租客数据" + DateUtil.time(), "租客数据表", list, ExportHouseTenantExcel.class);
+ }
+
+ /**
+ * 导入住户
+ */
+ @PostMapping("import-tenant")
+ @ApiOperationSupport(order = 12)
+ @ApiOperation(value = "导入住户", notes = "传入excel")
+ public R importUser(MultipartFile file, Integer isCovered) {
+ String data = houseTenantService.importUserTenant(ExcelUtil.read(file, HouseTenantExcel.class),isCovered==1);
+ return R.data(200,data,null);
+ }
+
}
diff --git a/src/main/java/org/springblade/modules/house/excel/ExportHouseTenantExcel.java b/src/main/java/org/springblade/modules/house/excel/ExportHouseTenantExcel.java
new file mode 100644
index 0000000..0fed67a
--- /dev/null
+++ b/src/main/java/org/springblade/modules/house/excel/ExportHouseTenantExcel.java
@@ -0,0 +1,93 @@
+package org.springblade.modules.house.excel;
+
+import com.alibaba.excel.annotation.ExcelProperty;
+import com.alibaba.excel.annotation.write.style.ColumnWidth;
+import com.alibaba.excel.annotation.write.style.ContentRowHeight;
+import com.alibaba.excel.annotation.write.style.HeadRowHeight;
+import com.baomidou.mybatisplus.annotation.FieldFill;
+import com.baomidou.mybatisplus.annotation.TableField;
+import com.fasterxml.jackson.annotation.JsonFormat;
+import io.swagger.annotations.ApiModelProperty;
+import lombok.Data;
+import org.springblade.common.excel.ExcelDictConverter;
+import org.springblade.common.excel.ExcelDictItem;
+
+import java.io.Serializable;
+import java.util.Date;
+
+@Data
+@ColumnWidth(25)
+@HeadRowHeight(20)
+@ContentRowHeight(18)
+public class ExportHouseTenantExcel implements Serializable {
+
+ private static final long serialVersionUID = 2L;
+
+ /**
+ * 出租屋ID
+ */
+ @ExcelProperty(value = "房屋地址")
+ @ColumnWidth(25)
+ private String addressName;
+ /**
+ * 出租屋ID
+ */
+ @ExcelProperty(value = "地址编码")
+ @ColumnWidth(25)
+ private String houseCode;
+ /**
+ * 姓名
+ */
+ @ExcelProperty(value = "姓名")
+ @ColumnWidth(25)
+ private String name;
+ /**
+ * 联系电话
+ */
+ @ExcelProperty(value = "联系电话")
+ @ColumnWidth(25)
+ private String phone;
+ /**
+ * 身份证
+ */
+ @ExcelProperty(value = "身份证")
+ @ColumnWidth(25)
+ private String idCard;
+ /**
+ * 户籍
+ */
+ @ExcelProperty(value = "户籍")
+ @ColumnWidth(25)
+ private String domicile;
+ /**
+ * 工作单位
+ */
+ @ExcelProperty(value = "工作单位")
+ @ColumnWidth(25)
+ private String workUnit;
+
+ /**
+ * 备注
+ */
+ @ExcelProperty(value = "备注")
+ @ColumnWidth(25)
+ private String remark;
+
+
+ /** 性别 */
+ @ExcelProperty(value = "性别",converter = ExcelDictConverter.class)
+ @ExcelDictItem(type = "sex")
+ @ColumnWidth(25)
+ private Integer gender;
+
+ /** 民族 */
+ @ExcelProperty(value = "民族",converter = ExcelDictConverter.class)
+ @ExcelDictItem(type = "nationType")
+ private Integer ethnicity;
+
+ /** 创建时间 */
+ @ExcelProperty(value = "创建时间")
+ @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
+ private Date createTime;
+
+}
diff --git a/src/main/java/org/springblade/modules/house/excel/HouseTenantExcel.java b/src/main/java/org/springblade/modules/house/excel/HouseTenantExcel.java
new file mode 100644
index 0000000..011d0fe
--- /dev/null
+++ b/src/main/java/org/springblade/modules/house/excel/HouseTenantExcel.java
@@ -0,0 +1,82 @@
+package org.springblade.modules.house.excel;
+
+import com.alibaba.excel.annotation.ExcelProperty;
+import com.alibaba.excel.annotation.write.style.ColumnWidth;
+import com.alibaba.excel.annotation.write.style.ContentRowHeight;
+import com.alibaba.excel.annotation.write.style.HeadRowHeight;
+import com.baomidou.mybatisplus.annotation.TableField;
+import lombok.Data;
+import org.springblade.common.excel.ExcelDictConverter;
+import org.springblade.common.excel.ExcelDictItem;
+import org.springblade.common.excel.ExcelDictItemLabel;
+
+import java.io.Serializable;
+
+@Data
+@ColumnWidth(25)
+@HeadRowHeight(20)
+@ContentRowHeight(18)
+public class HouseTenantExcel implements Serializable {
+
+ private static final long serialVersionUID = 2L;
+
+ /**
+ * 出租屋ID
+ */
+ @ExcelProperty(value = "地址编码")
+ @ColumnWidth(25)
+ private String houseCode;
+ /**
+ * 姓名
+ */
+ @ExcelProperty(value = "姓名")
+ @ColumnWidth(25)
+ private String name;
+ /**
+ * 联系电话
+ */
+ @ExcelProperty(value = "联系电话")
+ @ColumnWidth(25)
+ private String phone;
+ /**
+ * 身份证
+ */
+ @ExcelProperty(value = "身份证")
+ @ColumnWidth(25)
+ private String idCard;
+ /**
+ * 户籍
+ */
+ @ExcelProperty(value = "户籍")
+ @ColumnWidth(25)
+ private String domicile;
+ /**
+ * 工作单位
+ */
+ @ExcelProperty(value = "工作单位")
+ @ColumnWidth(25)
+ private String workUnit;
+
+ /**
+ * 备注
+ */
+ @ExcelProperty(value = "备注")
+ @ColumnWidth(25)
+ private String remark;
+
+
+ /** 性别 1: 男 0：女 2: 未知 */
+ @ColumnWidth(15)
+ @ExcelProperty( value = "性别",converter = ExcelDictConverter.class)
+ @ExcelDictItemLabel(type = "sex")
+ @ExcelDictItem(type = "sex")
+ private String gender;
+
+ /** 民族 */
+ @ColumnWidth(15)
+ @ExcelProperty( value = "民族",converter = ExcelDictConverter.class)
+ @ExcelDictItemLabel(type = "nationType")
+ @ExcelDictItem(type = "nationType")
+ private String ethnicity;
+
+}
diff --git a/src/main/java/org/springblade/modules/house/mapper/HouseTenantMapper.java b/src/main/java/org/springblade/modules/house/mapper/HouseTenantMapper.java
index 5e384e8..b166abe 100644
--- a/src/main/java/org/springblade/modules/house/mapper/HouseTenantMapper.java
+++ b/src/main/java/org/springblade/modules/house/mapper/HouseTenantMapper.java
@@ -18,6 +18,8 @@
import org.apache.ibatis.annotations.Param;
import org.springblade.modules.house.entity.HouseTenantEntity;
+import org.springblade.modules.house.excel.ExportHouseTenantExcel;
+import org.springblade.modules.house.excel.HouseTenantExcel;
import org.springblade.modules.house.vo.HouseTenantVO;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
@@ -46,4 +48,10 @@
* @return
*/
int removeByHousingRentalId(@Param("housingRentalId") Long housingRentalId);
+
+ /**
+ * 导出租客
+ * @return
+ */
+ List<ExportHouseTenantExcel> export();
}
diff --git a/src/main/java/org/springblade/modules/house/mapper/HouseTenantMapper.xml b/src/main/java/org/springblade/modules/house/mapper/HouseTenantMapper.xml
index ff7e231..ebda7c2 100644
--- a/src/main/java/org/springblade/modules/house/mapper/HouseTenantMapper.xml
+++ b/src/main/java/org/springblade/modules/house/mapper/HouseTenantMapper.xml
@@ -55,6 +55,29 @@
</where>
</select>
+
+ <select id="export" resultType="org.springblade.modules.house.excel.ExportHouseTenantExcel">
+ SELECT
+ jhr.house_code,
+ jda.address_name,
+ jht.NAME,
+ jht.phone,
+ jht.id_card,
+ jht.domicile,
+ jht.work_unit,
+ jht.remark,
+ jht.gender,
+ jht.ethnicity,
+ jht.create_time
+ FROM
+ jczz_house_tenant jht
+ LEFT JOIN jczz_house_rental jhr ON jht.housing_rental_id = jhr.id
+ LEFT JOIN jczz_doorplate_address jda on jda.address_code=jhr.house_code
+ WHERE
+ jht.is_deleted = 0
+
+ </select>
+
<!--根据租房id删除租户信息-->
<update id="removeByHousingRentalId">
update jczz_house_tenant set is_deleted = 1 where housing_rental_id = #{housingRentalId}
diff --git a/src/main/java/org/springblade/modules/house/service/IHouseTenantService.java b/src/main/java/org/springblade/modules/house/service/IHouseTenantService.java
index af3311a..6afdbdb 100644
--- a/src/main/java/org/springblade/modules/house/service/IHouseTenantService.java
+++ b/src/main/java/org/springblade/modules/house/service/IHouseTenantService.java
@@ -18,9 +18,13 @@
import com.baomidou.mybatisplus.extension.service.IService;
import org.springblade.modules.house.entity.HouseTenantEntity;
+import org.springblade.modules.house.excel.ExportHouseTenantExcel;
+import org.springblade.modules.house.excel.HouseTenantExcel;
import org.springblade.modules.house.vo.HouseTenantVO;
import org.springblade.core.mp.base.BaseService;
import com.baomidou.mybatisplus.core.metadata.IPage;
+
+import java.util.List;
/**
* 租户管理 服务类
@@ -45,4 +49,12 @@
* @return
*/
int removeByHousingRentalId(Long id);
+
+ /**
+ * 导出租客
+ * @return
+ */
+ List<ExportHouseTenantExcel> export();
+
+ String importUserTenant(List<HouseTenantExcel> read, boolean b);
}
diff --git a/src/main/java/org/springblade/modules/house/service/impl/HouseTenantServiceImpl.java b/src/main/java/org/springblade/modules/house/service/impl/HouseTenantServiceImpl.java
index 7215c76..b370382 100644
--- a/src/main/java/org/springblade/modules/house/service/impl/HouseTenantServiceImpl.java
+++ b/src/main/java/org/springblade/modules/house/service/impl/HouseTenantServiceImpl.java
@@ -16,14 +16,25 @@
*/
package org.springblade.modules.house.service.impl;
-import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
-import org.springblade.modules.house.entity.HouseTenantEntity;
-import org.springblade.modules.house.vo.HouseTenantVO;
-import org.springblade.modules.house.mapper.HouseTenantMapper;
-import org.springblade.modules.house.service.IHouseTenantService;
-import org.springblade.core.mp.base.BaseServiceImpl;
-import org.springframework.stereotype.Service;
import com.baomidou.mybatisplus.core.metadata.IPage;
+import com.baomidou.mybatisplus.core.toolkit.Wrappers;
+import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
+import org.apache.logging.log4j.util.Strings;
+import org.springblade.core.tool.utils.BeanUtil;
+import org.springblade.modules.house.entity.HouseRentalEntity;
+import org.springblade.modules.house.entity.HouseTenantEntity;
+import org.springblade.modules.house.excel.ExportHouseTenantExcel;
+import org.springblade.modules.house.excel.HouseTenantExcel;
+import org.springblade.modules.house.mapper.HouseTenantMapper;
+import org.springblade.modules.house.service.IHouseRentalService;
+import org.springblade.modules.house.service.IHouseTenantService;
+import org.springblade.modules.house.vo.HouseTenantVO;
+import org.springframework.beans.factory.annotation.Autowired;
+import org.springframework.stereotype.Service;
+
+import java.util.ArrayList;
+import java.util.List;
+import java.util.Objects;
/**
* 租户管理 服务实现类
@@ -34,6 +45,9 @@
@Service
public class HouseTenantServiceImpl extends ServiceImpl<HouseTenantMapper, HouseTenantEntity> implements IHouseTenantService {
+ @Autowired
+ private IHouseRentalService iHouseRentalService;
+
@Override
public IPage<HouseTenantVO> selectHouseTenantPage(IPage<HouseTenantVO> page, HouseTenantVO houseTenant) {
return page.setRecords(baseMapper.selectHouseTenantPage(page, houseTenant));
@@ -41,6 +55,7 @@
/**
* 根据租房id删除租户信息
+ *
* @param id
* @return
*/
@@ -48,4 +63,49 @@
public int removeByHousingRentalId(Long id) {
return baseMapper.removeByHousingRentalId(id);
}
+
+ /**
+ * 导出租客
+ *
+ * @return
+ */
+ @Override
+ public List<ExportHouseTenantExcel> export() {
+ return baseMapper.export();
+ }
+
+ @Override
+ public String importUserTenant(List<HouseTenantExcel> data, boolean b) {
+ List<HouseTenantEntity> HouseTenantList = new ArrayList<>();
+ int totalNum = data.size();
+ int importNum = 0;
+ int updateNum = 0;
+ int errorNum = 0;
+ for (HouseTenantExcel houseHoldExcel : data) {
+ HouseTenantVO houseTenantVO = Objects.requireNonNull(BeanUtil.copy(houseHoldExcel, HouseTenantVO.class));
+ HouseRentalEntity houseRentalEntity = iHouseRentalService.getOne(Wrappers.<HouseRentalEntity>lambdaQuery()
+ .eq(HouseRentalEntity::getHouseCode, houseTenantVO.getHouseCode()));
+ if (houseRentalEntity != null) {
+ // 性别
+ if (!Strings.isBlank(houseHoldExcel.getGender())) {
+ houseTenantVO.setGender(Integer.valueOf(houseHoldExcel.getGender()));
+ }
+ houseTenantVO.setHousingRentalId(houseRentalEntity.getId());
+ HouseTenantList.add(houseTenantVO);
+ } else {
+ errorNum++;
+ }
+ }
+ importNum = saveBatch(HouseTenantList) ? HouseTenantList.size() : 0;
+ StringBuilder builder = new StringBuilder("导入完成!");
+ builder.append("其中本次表格共有 ").append(totalNum).append(" 条数据，")
+ .append("成功导入 ").append(importNum).append(" 条数据。");
+ // if (updateNum > 0) {
+ // builder.append("其中有 ").append(updateNum).append(" 条数据已存在。");
+ // }
+ if (errorNum > 0) {
+ builder.append("共有 ").append(errorNum).append(" 条数据由于查询不到出租屋信息未导入!");
+ }
+ return builder.toString();
+ }
}
diff --git a/src/main/java/org/springblade/modules/house/vo/HouseTenantVO.java b/src/main/java/org/springblade/modules/house/vo/HouseTenantVO.java
index 87961f0..fa41c84 100644
--- a/src/main/java/org/springblade/modules/house/vo/HouseTenantVO.java
+++ b/src/main/java/org/springblade/modules/house/vo/HouseTenantVO.java
@@ -16,6 +16,7 @@
*/
package org.springblade.modules.house.vo;
+import io.swagger.annotations.ApiModelProperty;
import org.springblade.modules.house.entity.HouseTenantEntity;
import org.springblade.core.tool.node.INode;
import lombok.Data;
@@ -32,4 +33,6 @@
public class HouseTenantVO extends HouseTenantEntity {
private static final long serialVersionUID = 1L;
+ @ApiModelProperty(value = "地址编码")
+ private String houseCode;
}
--
Gitblit v1.9.3