src/main/java/org/springblade/modules/house/controller/HouseholdController.java
@@ -36,7 +36,9 @@ import org.springblade.core.tool.utils.DateUtil; import org.springblade.modules.house.entity.HouseholdEntity; import org.springblade.modules.house.excel.ExportHouseholdExcel; import org.springblade.modules.house.excel.HouseTenantExcel; import org.springblade.modules.house.excel.ImportHouseholdExcel; import org.springblade.modules.house.excel.ImportTenantHouseholdExcel; import org.springblade.modules.house.service.IHouseholdService; import org.springblade.modules.house.vo.HouseholdVO; import org.springblade.modules.house.wrapper.HouseholdWrapper; @@ -245,7 +247,7 @@ @ApiOperation(value = "导出住户", notes = "传入user") public void exportUser(HouseholdVO household, HttpServletResponse response) { List<ExportHouseholdExcel> list = householdService.export(household); ExcelUtil.export(response, "住户户数据" + DateUtil.time(), "住户数据表", list, ExportHouseholdExcel.class); ExcelUtil.export(response, "租户户数据" + DateUtil.time(), "住户数据表", list, ExportHouseholdExcel.class); } /** @@ -298,6 +300,44 @@ /** * 导出住户 */ @GetMapping("export-tenant-household") @ApiOperationSupport(order = 13) @ApiOperation(value = "导出住户", notes = "传入user") public void exporttenantUser(HouseholdVO household, HttpServletResponse response) { List<HouseTenantExcel> list = householdService.exportTenant(household); ExcelUtil.export(response, "住户户数据" + DateUtil.time(), "住户数据表", list, HouseTenantExcel.class); } /** * 模板下載 */ @GetMapping("/export-tenant-template") @ApiOperationSupport(order = 15) @ApiOperation(value = "模板下載", notes = "传入response") public void exportTenantUser(HttpServletResponse response, @RequestParam(value = "isTenant", required = false) String isTenant) { List<ImportTenantHouseholdExcel> list = new ArrayList<>(); ImportTenantHouseholdExcel houseHoldExcel = new ImportTenantHouseholdExcel(); // 设置模板数据 houseHoldExcel.setHouseCode("Afdsfdasf5421********411214"); houseHoldExcel.setName("张三"); houseHoldExcel.setGender("1"); houseHoldExcel.setPhoneNumber("13112341234"); houseHoldExcel.setBirthday("2001-01-01"); houseHoldExcel.setIdCard("360*************0001"); if (StringUtils.isBlank(isTenant)) { houseHoldExcel.setRelationship("1"); } else { houseHoldExcel.setRelationship("18"); } houseHoldExcel.setEthnicity("1"); houseHoldExcel.setHukouRegistration("江西上饶****"); list.add(houseHoldExcel); ExcelUtil.export(response, "租户数据模板", "租户数据表", list, ImportTenantHouseholdExcel.class); } /** * 住户对应物业，网格，公安负责人查询 * * @param household src/main/java/org/springblade/modules/house/excel/HouseTenantExcel.java
@@ -20,50 +20,18 @@ private static final long serialVersionUID = 2L; /** * 出租屋ID * 门牌地址编码 */ @ExcelProperty(value = "地址编码") @ColumnWidth(25) @ExcelProperty( "门牌地址编码*") private String houseCode; /** * 姓名 */ @ExcelProperty(value = "姓名") @ColumnWidth(25) /** 姓名 */ @ColumnWidth(15) @ExcelProperty( "姓名*") 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) @@ -72,6 +40,29 @@ @ExcelDictItem(type = "sex") private String gender; /** 手机号 */ @ColumnWidth(15) @ExcelProperty( "手机号*") private String phoneNumber; /** 生日 */ @ColumnWidth(15) @ExcelProperty( "生日") private String birthday; /** 身份证 */ @ColumnWidth(15) @ExcelProperty( "身份证号码") private String idCard; /** 与业主关系 */ @ColumnWidth(15) @ExcelProperty( value = "与业主关系*(业主/妻子/丈夫/女儿/儿子/母亲/父亲/.../租户/其他)",converter = ExcelDictConverter.class) @ExcelDictItemLabel(type = "roleRelation") @ExcelDictItem(type = "roleRelation") private String relationship; /** 民族 */ @ColumnWidth(15) @ExcelProperty( value = "民族",converter = ExcelDictConverter.class) @@ -79,4 +70,17 @@ @ExcelDictItem(type = "nationType") private String ethnicity; /** 户籍地址 */ @ColumnWidth(15) @ExcelProperty( "户籍地址") private String hukouRegistration; /** 工作单位 */ @ColumnWidth(15) @ExcelProperty( "工作单位") private String employer; } src/main/java/org/springblade/modules/house/excel/ImportTenantHouseholdExcel.java
New file @@ -0,0 +1,90 @@ 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 lombok.Data; import org.springblade.common.excel.ExcelDictConverter; import org.springblade.common.excel.ExcelDictItem; import org.springblade.common.excel.ExcelDictItemLabel; import java.io.Serializable; /** * ImportHouseHoldExcel * * @author Chill */ @Data @ColumnWidth(25) @HeadRowHeight(20) @ContentRowHeight(18) public class ImportTenantHouseholdExcel implements Serializable { private static final long serialVersionUID = 2L; /** * 门牌地址编码 */ @ColumnWidth(25) @ExcelProperty( "门牌地址编码*") private String houseCode; /** 姓名 */ @ColumnWidth(15) @ExcelProperty( "姓名*") private String name; /** 性别 1: 男 0：女 2: 未知 */ @ColumnWidth(15) @ExcelProperty( value = "性别",converter = ExcelDictConverter.class) @ExcelDictItemLabel(type = "sex") @ExcelDictItem(type = "sex") private String gender; /** 手机号 */ @ColumnWidth(15) @ExcelProperty( "手机号*") private String phoneNumber; /** 生日 */ @ColumnWidth(15) @ExcelProperty( "生日") private String birthday; /** 身份证 */ @ColumnWidth(15) @ExcelProperty( "身份证号码") private String idCard; /** 与业主关系 */ @ColumnWidth(15) @ExcelProperty( value = "与业主关系*(业主/妻子/丈夫/女儿/儿子/母亲/父亲/.../租户/其他)",converter = ExcelDictConverter.class) @ExcelDictItemLabel(type = "roleRelation") @ExcelDictItem(type = "roleRelation") private String relationship; /** 民族 */ @ColumnWidth(15) @ExcelProperty( value = "民族",converter = ExcelDictConverter.class) @ExcelDictItemLabel(type = "nationType") @ExcelDictItem(type = "nationType") private String ethnicity; /** 户籍地址 */ @ColumnWidth(15) @ExcelProperty( "户籍地址") private String hukouRegistration; /** 工作单位 */ @ColumnWidth(15) @ExcelProperty( "工作单位") private String employer; }
@@ -24,6 +24,7 @@ import org.springblade.common.node.TreeStringNode; import org.springblade.modules.house.entity.HouseholdEntity; import org.springblade.modules.house.excel.ExportHouseholdExcel; import org.springblade.modules.house.excel.HouseTenantExcel; import org.springblade.modules.house.excel.ImportHouseholdExcel; import org.springblade.modules.house.vo.HouseholdOtherVO; import org.springblade.modules.house.vo.HouseholdVO; @@ -185,4 +186,9 @@ * @return */ List<HouseholdVO> getAllList(@Param("i") int i,@Param("size") int size); List<HouseTenantExcel> exportTenant(@Param("household") HouseholdVO household, @Param("isAdministrator") Integer isAdministrator, @Param("regionChildCodesList") List<String> regionChildCodesList, @Param("gridCodeList") List<String> gridCodeList); } src/main/java/org/springblade/modules/house/mapper/HouseholdMapper.xml
@@ -1453,4 +1453,192 @@ limit #{i},#{size} </select> <select id="exportTenant" resultType="org.springblade.modules.house.excel.HouseTenantExcel"> SELECT jh.house_code, jh.NAME, jh.phone_number, jh.relationship, jh.residential_status, jh.birthday, jh.id_card, jh.card_type, jh.ethnicity, jh.education, jh.resident_type, jh.hukou_registration, jh.resident_adcode, jh.native_place_adcode, jh.religious_belief, jh.health_status, jh.disease_name, jh.work_status, jh.employer, jh.occupation, jh.cmpy_reg_addr, jh.go_out_addr, jh.go_out_where, jh.go_out_time, jh.go_out_reason, jh.marital_status, jh.card_number FROM jczz_household jh LEFT JOIN jczz_house jhs ON jh.house_code = jhs.house_code and jhs.is_deleted = 0 LEFT JOIN jczz_doorplate_address jda ON jda.address_code = jh.house_code LEFT JOIN jczz_grid jg on jg.grid_code = jhs.grid_code and jg.is_deleted = 0 LEFT JOIN jczz_police_affairs_grid jpag on jhs.jw_grid_code= jpag.jw_grid_code and jpag.is_deleted = 0 LEFT JOIN ( select household_id,GROUP_CONCAT(label_name) as label_name from jczz_user_house_label where lable_type = 1 and household_id is not null GROUP BY household_id ) juhl on juhl.household_id = jh.id <where> <if test="household.labelIdList !=null and household.labelIdList.size() >0 "> EXISTS ( SELECT * FROM jczz_user_house_label juhl LEFT JOIN jczz_label jl ON juhl.label_id = jl.id where juhl.household_id = jh.id and juhl.lable_type = 1 AND juhl.household_id IS NOT NULL AND jl.id in <foreach collection="household.labelIdList" item="code" open="(" close=")" separator=","> #{code} </foreach> ) </if> <if test="household.userId!=null"> AND jg.grid_code IN ( SELECT DISTINCT jgm.grid_code FROM jczz_gridman jgm WHERE jgm.user_id = #{household.userId} ) </if> <if test="household.searchKey!=null and household.searchKey!=''"> and CONCAT(jh.name,jh.phone_number) like CONCAT ('%', #{household.searchKey},'%') </if> <if test="household.name!=null and household.name !=''"> and jh.name like concat('%',#{household.name},'%') </if> <if test="household.volunteerOrg != null and household.volunteerOrg != ''"> and jh.volunteer_org like concat('%',#{household.volunteerOrg},'%') </if> <if test="household.houseCode!=null and household.houseCode !=''"> and jh.house_code = #{household.houseCode} </if> <if test="household.communityCode!=null and household.communityCode !=''"> and jg.community_code = #{household.communityCode} </if> <!-- 等于18 就是租户 --> <if test="household.relationship!=null and household.relationship == 18"> and jh.relationship = #{household.relationship} <if test="household.minors!=null and household.minors ==2"> and TIMESTAMPDIFF(year, substring(jh.id_card, 7, 8), now()) < 18 </if> <if test="household.minors!=null and household.minors ==1"> and TIMESTAMPDIFF(year, substring(jh.id_card, 7, 8), now()) >= 18 </if> </if> <!-- 不等于18 就是非租户 --> <if test="household.relationship!=null and household.relationship != 18"> and jh.relationship != #{household.relationship} </if> <if test="household.phoneNumber!=null and household.phoneNumber !=''"> and jh.phone_number like concat('%',#{household.phoneNumber},'%') </if> <if test="household.idCard!=null and household.idCard !=''"> and jh.id_card like concat('%',#{household.idCard},'%') </if> <if test="household.aoiName!=null and household.aoiName !=''"> and jhs.district_name like concat('%',#{household.aoiName},'%') </if> <if test="household.confirmFlag != null "> and jh.confirm_flag = #{household.confirmFlag} </if> <if test="household.townStreetName!=null and household.townStreetName!=''"> and jda.town_street_name like concat('%',#{household.townStreetName},'%') </if> <if test="household.neiName!=null and household.neiName!=''"> and jda.nei_name like concat('%',#{household.neiName},'%') </if> <if test="household.housingRentalId != null "> and jh.housing_rental_id = #{household.housingRentalId} </if> <if test="household.startTime != null and household.startTime != '' and household.endTime != null and household.endTime != '' "> AND jh.create_time BETWEEN #{household.startTime} and #{household.endTime} </if> <if test="isAdministrator==2"> <choose> <when test="household.roleName != null and household.roleName != ''"> <if test="household.roleName=='wgy'"> <choose> <when test="gridCodeList !=null and gridCodeList.size()>0"> and jhs.grid_code in <foreach collection="gridCodeList" item="code" open="(" close=")" separator=","> #{code} </foreach> </when> <otherwise> and jhs.grid_code in ('') </otherwise> </choose> </if> <if test="household.roleName=='mj'"> <choose> <when test="regionChildCodesList !=null and regionChildCodesList.size()>0"> and jpag.community_code in <foreach collection="regionChildCodesList" item="code" open="(" close=")" separator=","> #{code} </foreach> </when> <otherwise> and jpag.community_code in ('') </otherwise> </choose> </if> </when> <otherwise> <choose> <when test="regionChildCodesList !=null and regionChildCodesList.size()>0"> and ( jg.grid_code in <foreach collection="regionChildCodesList" item="code" open="(" close=")" separator=","> #{code} </foreach> or jpag.community_code in <foreach collection="regionChildCodesList" item="code" open="(" close=")" separator=","> #{code} </foreach> ) </when> <otherwise> </otherwise> </choose> </otherwise> </choose> </if> <if test="household.building!=null and household.building!=''"> and jhs.building like concat(#{household.building},'%') </if> <if test="household.unit!=null and household.unit!=''"> and jhs.unit like concat(#{household.unit},'%') </if> <if test="household.aoiCode!=null and household.aoiCode!=''"> and jhs.district_code = #{household.aoiCode} </if> and jh.is_deleted = 0 </where> order by jh.create_time desc limit 0,10000 </select> </mapper> src/main/java/org/springblade/modules/house/service/IHouseholdService.java
@@ -22,6 +22,7 @@ import org.springblade.common.node.TreeStringNode; import org.springblade.modules.house.entity.HouseholdEntity; import org.springblade.modules.house.excel.ExportHouseholdExcel; import org.springblade.modules.house.excel.HouseTenantExcel; import org.springblade.modules.house.excel.ImportHouseholdExcel; import org.springblade.modules.house.vo.HouseholdVO; @@ -152,4 +153,6 @@ * @return */ List<HouseholdVO> getAllList(int i, int size); List<HouseTenantExcel> exportTenant(HouseholdVO household); } src/main/java/org/springblade/modules/house/service/impl/HouseholdServiceImpl.java
@@ -36,6 +36,7 @@ import org.springblade.modules.house.entity.HouseholdEntity; import org.springblade.modules.house.entity.UserHouseLabelEntity; import org.springblade.modules.house.excel.ExportHouseholdExcel; import org.springblade.modules.house.excel.HouseTenantExcel; import org.springblade.modules.house.excel.ImportHouseholdExcel; import org.springblade.modules.house.mapper.HouseholdMapper; import org.springblade.modules.house.service.IHouseRentalService; @@ -374,8 +375,20 @@ if (houseRentalEntity != null) { householdEntity.setHousingRentalId(houseRentalEntity.getId()); } else { errorNum++; continue; // 新建出租屋 HouseRentalEntity houseRentalEntity1 = new HouseRentalEntity(); houseRentalEntity1.setHouseCode(householdEntity.getHouseCode()); houseRentalEntity1.setHouseStatus(1); houseRentalEntity1.setAuditStatus(0); houseRentalEntity1.setRentalUse(4); houseRentalEntity1.setTenantRelationship(1); houseRentalEntity1.setRentalTime(new Date()); houseRentalEntity1.setDueTime(new Date()); houseRentalEntity1.setTerminationTime(new Date()); iHouseRentalService.save(houseRentalEntity1); householdEntity.setHousingRentalId(houseRentalEntity1.getId()); // errorNum++; // continue; } } /// 是否主要联系人 @@ -780,12 +793,24 @@ /** * 查询对应的住户集合 * * @param i * @param size * @return */ @Override public List<HouseholdVO> getAllList(int i, int size) { return baseMapper.getAllList(i,size); return baseMapper.getAllList(i, size); } @Override public List<HouseTenantExcel> exportTenant(HouseholdVO household) { CommonParamSet commonParamSet = new CommonParamSet().invoke(HouseholdVO.class, household); List<HouseTenantExcel> houseTenantExcels = baseMapper.exportTenant(household, commonParamSet.getIsAdministrator(), commonParamSet.getRegionChildCodesList(), commonParamSet.getGridCodeList() ); return houseTenantExcels; } }
