src/main/java/org/springblade/modules/house/controller/HouseholdController.java
@@ -34,6 +34,7 @@ import org.springblade.core.tool.jackson.JsonUtil; 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.ImportHouseholdExcel; import org.springblade.modules.house.excel.HouseHoldImporter; import org.springblade.modules.house.service.IHouseholdService; @@ -242,8 +243,8 @@ @ApiOperationSupport(order = 13) @ApiOperation(value = "导出住户", notes = "传入user") public void exportUser(HouseholdVO household, HttpServletResponse response) { List<ImportHouseholdExcel> list = householdService.export(household); ExcelUtil.export(response, "住户户数据" + DateUtil.time(), "住户数据表", list, ImportHouseholdExcel.class); List<ExportHouseholdExcel> list = householdService.export(household); ExcelUtil.export(response, "住户户数据" + DateUtil.time(), "住户数据表", list, ExportHouseholdExcel.class); } /** src/main/java/org/springblade/modules/house/excel/ExportHouseholdExcel.java
New file @@ -0,0 +1,237 @@ 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; /** * ExportHouseholdExcel * 导出住户信息 * @author Chill */ @Data @ColumnWidth(25) @HeadRowHeight(20) @ContentRowHeight(18) public class ExportHouseholdExcel 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( "其他联系方式") private String otherContact; /** 所属街道 */ @ColumnWidth(15) @ExcelProperty( "所属街道") private String townName; /** 所属社区 */ @ColumnWidth(15) @ExcelProperty( "所属社区") private String communityName; /** 所属网格 */ @ColumnWidth(15) @ExcelProperty( "所属网格") private String gridName; /** 小区名称 */ @ColumnWidth(15) @ExcelProperty( "小区名称") private String aoiName; /** 与业主关系 */ @ColumnWidth(15) @ExcelProperty( value = "与业主关系*(业主/妻子/丈夫/女儿/儿子/母亲/父亲/.../租户/其他)",converter = ExcelDictConverter.class) @ExcelDictItemLabel(type = "roleRelation") @ExcelDictItem(type = "roleRelation") private String relationship; /** 是否主要联系人 1：是 0：否 */ @ColumnWidth(15) @ExcelProperty( value = "是否主要联系人",converter = ExcelDictConverter.class) @ExcelDictItemLabel(type = "primaryContactType") @ExcelDictItem(type = "primaryContactType") private String isPrimaryContact; /** 居住状态 1: 是 0：否 */ @ColumnWidth(15) @ExcelProperty( value = "居住状态",converter = ExcelDictConverter.class) @ExcelDictItemLabel(type = "residentialStatusType") @ExcelDictItem(type = "residentialStatusType") private String residentialStatus; /** 民族 */ @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 currentAddress; /** 是否党员 1：党员 2：群众 */ @ColumnWidth(15) @ExcelProperty( value = "是否党员（党员/群众）",converter = ExcelDictConverter.class) @ExcelDictItemLabel(type = "partyEmberType") @ExcelDictItem(type = "partyEmberType") private String partyEmber; /** 学历 */ @ColumnWidth(15) @ExcelProperty( value = "学历",converter = ExcelDictConverter.class) @ExcelDictItemLabel(type = "educationType") @ExcelDictItem(type = "educationType") private String education; /** * 职业类别 */ @ColumnWidth(15) @ExcelProperty( "职业类别") private String occupation; /** 工作单位 */ @ColumnWidth(15) @ExcelProperty( "工作单位") private String employer; /** * 工作单位地址 */ @ExcelProperty( "工作单位地址") private String cmpyRegAddr; /** 工作状态 */ @ColumnWidth(15) @ExcelProperty( value = "工作状态（在职/离职/退休/其他）",converter = ExcelDictConverter.class) @ExcelDictItemLabel(type = "workStatusType") @ExcelDictItem(type = "workStatusType") private String workStatus; /** 婚姻状态 */ @ColumnWidth(15) @ExcelProperty( value = "婚姻状态（未婚/已婚/离异/丧偶/其他）",converter = ExcelDictConverter.class) @ExcelDictItemLabel(type = "marriageStatusType") @ExcelDictItem(type = "marriageStatusType") private String maritalStatus; /** * 宗教信仰 */ @ColumnWidth(15) @ExcelProperty( "宗教信仰") private String religiousBelief; /** * 健康状况 业务字典 healthStatus */ @ColumnWidth(15) @ExcelProperty( value = "健康状况(健康/良好/较差)",converter = ExcelDictConverter.class) @ExcelDictItemLabel(type = "healthStatus") @ExcelDictItem(type = "healthStatus") private String healthStatus; /** * 疾病名称 */ @ColumnWidth(15) @ExcelProperty( "疾病名称") private String diseaseName; /** * 外出去向 */ @ColumnWidth(15) @ExcelProperty( "外出去向") private String goOutWhere; /** * 外出原因 */ @ColumnWidth(15) @ExcelProperty( "外出原因") private String goOutReason; /** * 外出时间 */ @ColumnWidth(15) @ExcelProperty( "外出时间") private String goOutTime; /** * 外出详址 */ @ExcelProperty( "外出详址") private String goOutAddr; /** 车牌号 */ @ColumnWidth(15) @ExcelProperty( "车牌号") private String cardNumber; /** * 自愿者组织 */ @ColumnWidth(15) @ExcelProperty( "自愿者组织") private String volunteerOrg; /** 备注 */ @ColumnWidth(15) @ExcelProperty( "备注") private String remark; /** 备注 */ @ColumnWidth(15) @ExcelProperty( "标签") private String labelName; }
@@ -23,6 +23,7 @@ import org.springblade.common.node.TreeIntegerNode; 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.ImportHouseholdExcel; import org.springblade.modules.house.vo.HouseholdOtherVO; import org.springblade.modules.house.vo.HouseholdVO; @@ -85,12 +86,15 @@ HouseholdVO getHouseholdListById(@Param("household") HouseholdEntity household); /** * 导出 * 导出住户信息 * * @param household * @return */ List<ImportHouseholdExcel> export(@Param("household") HouseholdVO household); List<ExportHouseholdExcel> export(@Param("household") HouseholdVO household, @Param("isAdministrator") Integer isAdministrator, @Param("regionChildCodesList") List<String> regionChildCodesList, @Param("gridCodeList") List<String> gridCodeList); Integer statistics(@Param("household") HouseholdVO household, @Param("isAdministrator") Integer isAdministrator, src/main/java/org/springblade/modules/house/mapper/HouseholdMapper.xml
@@ -557,32 +557,212 @@ </select> <!--导出数据--> <select id="export" resultType="org.springblade.modules.house.excel.ImportHouseholdExcel"> select jh.house_code houseCode, jh.name,jh.phone_number phoneNumber,jh.role_type roleType,jh.relationship relationship, jh.is_primary_contact isPrimaryContact,jh.residential_status residentialStatus,jh.gender, jh.birthday,jh.id_card idCard,jh.ethnicity,jh.education, jh.hukou_registration hukouRegistration,jh.work_status workStatus,jh.employer,jh.marital_status maritalStatus, jh.card_number cardNumber,jh.other_contact otherContact,jh.current_address currentAddress,jh.volunteer_org, jh.disability_cert disabilityCert,jh.party_ember partyEmber,jh.remark, <select id="export" resultType="org.springblade.modules.house.excel.ExportHouseholdExcel"> SELECT jh.id, jh.house_code, jh.NAME, jh.phone_number, jh.relationship, jh.is_primary_contact, jh.residential_status, jh.birthday, jh.id_card, jh.card_type, ifnull( jh.gender, CASE WHEN substring( jh.id_card, 17, 1 )% 2 = 1 THEN 1 ELSE 0 END ) AS gender, 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, jh.other_contact, IF ( jda.id IS NOT NULL, jda.address_name, jh.current_address ) AS current_address, jh.disability_cert, jh.party_ember, jh.remark, jhs.district_name aoiName, concat(jhs.building," ",unit," ",room) as address from jczz_household jh join jczz_house jhs on jh.house_code = jhs.house_code and jhs.is_deleted = 0 where jh.is_deleted = 0 <if test="household.name!=null and household.name !=''"> and jh.name like concat('%',#{household.name},'%') </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> jda.town_street_name AS townName, jda.nei_name AS communityName, jg.grid_name, jhs.building, jh.volunteer_org, case when TIMESTAMPDIFF(year, substring(jh.id_card, 7, 8), now()) < 18 then 2 when TIMESTAMPDIFF(year, substring(jh.id_card, 7, 8), now()) >= 18 then 1 end as minors, jhs.unit, juhl.label_name 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>
@@ -21,6 +21,7 @@ import org.springblade.common.node.TreeIntegerNode; 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.ImportHouseholdExcel; import org.springblade.modules.house.vo.HouseholdVO; @@ -71,7 +72,12 @@ */ Object getDetail(HouseholdEntity household); List<ImportHouseholdExcel> export(HouseholdVO household); /** * 导出住户信息 * @param household * @return */ List<ExportHouseholdExcel> export(HouseholdVO household); String importUserHouseHold(List<ImportHouseholdExcel> data, Boolean isCovered);
@@ -35,6 +35,7 @@ import org.springblade.core.tool.utils.Func; 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.ImportHouseholdExcel; import org.springblade.modules.house.mapper.HouseholdMapper; import org.springblade.modules.house.service.IHouseService; @@ -300,9 +301,23 @@ return baseMapper.getHouseholdListById(household); } /** * 导出住户信息 * @param household * @return */ @Override public List<ImportHouseholdExcel> export(HouseholdVO household) { List<ImportHouseholdExcel> userHouseHoldExcels = baseMapper.export(household); public List<ExportHouseholdExcel> export(HouseholdVO household) { CommonParamSet commonParamSet = new CommonParamSet().invoke(HouseholdVO.class, household); // 重点人员与关注人员 if (Objects.nonNull(household.getLabelType())) { extracted(household); } List<ExportHouseholdExcel> userHouseHoldExcels = baseMapper.export(household, commonParamSet.getIsAdministrator(), commonParamSet.getRegionChildCodesList(), commonParamSet.getGridCodeList() ); return userHouseHoldExcels; }
