springcloud_k8s_panzhihuazhihuishequ/common/src/main/java/com/panzhihua/common/constants/Constants.java
@@ -169,6 +169,8 @@ public static final String VETERANS_POPULATION_ERROR_LIST = "VETERANS_POPULATION_ERROR_LIST_"; public static final String DISABILITY_POPULATION_ERROR_LIST = "DISABILITY_POPULATION_ERROR_LIST_"; /** * 高德地图获取天气url */ springcloud_k8s_panzhihuazhihuishequ/common/src/main/java/com/panzhihua/common/enums/PopulRegiterNatureEnum.java
New file @@ -0,0 +1,45 @@ package com.panzhihua.common.enums; /** 实有房屋使用类型 * @author xyh * @date 2021/6/11 9:33 */ public enum PopulRegiterNatureEnum { CZHK(1,"城镇户口"), NCHK(0,"农村户口"), ; private final Integer code; private final String name; PopulRegiterNatureEnum(Integer code, String name){ this.code = code; this.name = name; } public Integer getCode() { return code; } public String getName() { return name; } public static int getCodeByName(String name) { for (PopulRegiterNatureEnum item : PopulRegiterNatureEnum.values()) { if (item.getName().equals(name)) { return item.getCode(); } } return -1; } public static String getCnDescByName(Integer code) { for (PopulRegiterNatureEnum item : PopulRegiterNatureEnum.values()) { if (item.getCode().equals(code)) { return item.getName(); } } return "其他"; } } springcloud_k8s_panzhihuazhihuishequ/common/src/main/java/com/panzhihua/common/listen/ComMngPopulationDisabilityExcelListen.java
New file @@ -0,0 +1,516 @@ package com.panzhihua.common.listen; import com.alibaba.excel.context.AnalysisContext; import com.alibaba.excel.event.AnalysisEventListener; import com.alibaba.fastjson.JSONArray; import com.google.common.collect.Lists; import com.panzhihua.common.constants.BcDictionaryConstants; import com.panzhihua.common.constants.Constants; import com.panzhihua.common.enums.*; import com.panzhihua.common.exceptions.ServiceException; import com.panzhihua.common.model.vos.BcDictionaryVO; import com.panzhihua.common.model.vos.R; import com.panzhihua.common.model.vos.community.ComMngPopulationDisabilityExcelVO; import com.panzhihua.common.model.vos.community.ComMngPopulationDisabilityMistakeExcelVO; import com.panzhihua.common.service.community.CommunityService; import com.panzhihua.common.utlis.ListUtils; import com.panzhihua.common.utlis.PayUtil; import com.panzhihua.common.utlis.StringUtils; import lombok.extern.slf4j.Slf4j; import org.springframework.data.redis.core.StringRedisTemplate; import org.springframework.data.redis.core.ValueOperations; import java.text.ParseException; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.List; import java.util.Map; import java.util.Objects; import java.util.concurrent.TimeUnit; import java.util.stream.Collectors; /** * @description: 残疾人导入监听 * @author: txb */ @Slf4j public class ComMngPopulationDisabilityExcelListen extends AnalysisEventListener<Map<Integer, String>> { private CommunityService communityService; private Long communityId; private static int headSize = 0; private Map<Integer, String> headData; private String userName; // FTP 登录密码 private String password; // FTP 服务器地址IP地址 private String host; // FTP 端口 private int port; private String excelUrl; private StringRedisTemplate stringRedisTemplate; public static String loadUrl; public ComMngPopulationDisabilityExcelListen(CommunityService communityService, Long communityId, String userName, String password, String host, int port, String excelUrl, StringRedisTemplate stringRedisTemplate) { this.communityService = communityService; this.communityId = communityId; this.userName = userName; this.password = password; this.host = host; this.port = port; this.excelUrl = excelUrl; this.stringRedisTemplate = stringRedisTemplate; } /** * 每隔5条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收 */ private static final int BATCH_COUNT = 5000; List<Map<Integer, String>> list = new ArrayList<Map<Integer, String>>(); @Override public void invoke(Map<Integer, String> data, AnalysisContext context) { list.add(data); if (list.size() >= BATCH_COUNT) { saveData(); list.clear(); } } /** * 这里会一行行的返回头 * * @param headMap * @param context */ @Override public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) { headSize = headMap.size(); headData = headMap; } @Override public void doAfterAllAnalysed(AnalysisContext context) { saveData(); log.info("所有数据解析完成！"); } /** * 不是固定的列只能手动处理 */ private void saveData() { R<List<BcDictionaryVO>> dictionaryR = communityService.listDictionaryByKey(BcDictionaryConstants.FAMILY); log.info("开始导入残疾人数据"); log.info("表格总数据：" + list.size()); if (list.size() == 0) { throw new ServiceException("500", "导入数据为空！"); } ValueOperations<String, String> valueOperations = stringRedisTemplate.opsForValue(); String Disability = Constants.DISABILITY_POPULATION_ERROR_LIST + communityId; Map<String,String> dictMap = dictionaryR.getData().stream().collect(Collectors.toMap(BcDictionaryVO::getDictName,BcDictionaryVO::getDictValue)); // Map<String,Integer> dictMap = new HashMap<>(); // for(BcDictionaryVO vo : dictionaryR.getData()){ // dictMap.put(vo.getDictName(),Integer.valueOf(vo.getDictValue())); // } int index = 2; try { ArrayList<ComMngPopulationDisabilityExcelVO> voList = Lists.newArrayList(); // List<ComMngPopulationImportErrorVO> populationImportErrorVOList = new ArrayList<>(); ArrayList<ComMngPopulationDisabilityMistakeExcelVO> mistakes = Lists.newArrayList(); for (Map<Integer, String> oneData : list) { ComMngPopulationDisabilityExcelVO vo = new ComMngPopulationDisabilityExcelVO(); if (StringUtils.isNotEmpty(oneData.get(0))){ vo.setSerialNumber(oneData.get(0)); } //姓名和身份证都为空,为空户，无需操作,否则就解析年龄，性别，出生年月日 if(StringUtils.isNotEmpty(oneData.get(1)) || StringUtils.isNotEmpty(oneData.get(2))){ if(StringUtils.isEmpty(oneData.get(1))){ ComMngPopulationDisabilityMistakeExcelVO mistake = new ComMngPopulationDisabilityMistakeExcelVO(); index++; setDisabilityMistake(oneData, mistake); mistake.setMistake("名字不可为空，请填写姓名"); mistakes.add(mistake); continue; } vo.setName(oneData.get(1)); if(StringUtils.isEmpty(oneData.get(2))){ ComMngPopulationDisabilityMistakeExcelVO mistake = new ComMngPopulationDisabilityMistakeExcelVO(); index++; setDisabilityMistake(oneData, mistake); mistake.setMistake("身份证号不可为空，请填写身份证"); mistakes.add(mistake); continue; } //判断身份证号码位数 if(oneData.get(2).length() != 18){ ComMngPopulationDisabilityMistakeExcelVO mistake = new ComMngPopulationDisabilityMistakeExcelVO(); index++; setDisabilityMistake(oneData, mistake); mistake.setMistake("身份证号位数有误，请检查身份证号码是否正确"); mistakes.add(mistake); continue; } vo.setCardNo(oneData.get(2).toUpperCase()); //根据身份证号码解析年龄以及性别 //获取用户生日 String birthday = vo.getCardNo().substring(6, 14); if(StringUtils.isNotEmpty(birthday)){ String year = birthday.substring(0,4); String month = birthday.substring(4,6); String day = birthday.substring(6,8); vo.setBirthday(year + "-" + month + "-" + day); } // //设置用户年龄 // vo.setAge(AgeUtils.getAgeFromBirthTime(birthday)); //获取用户性别 int sex = Integer.parseInt(vo.getCardNo().substring(16, 17)); if(sex%2 == 1){ vo.setSex(PopulSexEnum.nan.getCode()); }else{ vo.setSex(PopulSexEnum.nv.getCode()); } } if(StringUtils.isNotEmpty(oneData.get(3))){ vo.setCertificateSituation(oneData.get(3)); } if(StringUtils.isNotEmpty(oneData.get(4))){ Integer isOk = PopulCultureLevelEnum.getCodeByName(oneData.get(4)); if(isOk.equals(-1)){ ComMngPopulationDisabilityMistakeExcelVO mistake = new ComMngPopulationDisabilityMistakeExcelVO(); index++; setDisabilityMistake(oneData, mistake); mistake.setMistake("您填写的文化程度有误"); mistakes.add(mistake); continue; } vo.setCultureLevel(isOk); } if(StringUtils.isNotEmpty(oneData.get(5))){ vo.setNation(oneData.get(5)); } if(StringUtils.isNotEmpty(oneData.get(6))){ vo.setDisType(oneData.get(6)); } if(StringUtils.isNotEmpty(oneData.get(7))){ vo.setDisLevel(oneData.get(7)); } if(StringUtils.isNotEmpty(oneData.get(8))){ vo.setPhone(oneData.get(8)); } if(StringUtils.isEmpty(oneData.get(9))){ index++; ComMngPopulationDisabilityMistakeExcelVO mistake = new ComMngPopulationDisabilityMistakeExcelVO(); setDisabilityMistake(oneData, mistake); mistake.setMistake("街路巷不可为空"); mistakes.add(mistake); continue; } vo.setRoad(oneData.get(9)); if(StringUtils.isEmpty(oneData.get(10))){ index++; ComMngPopulationDisabilityMistakeExcelVO mistake = new ComMngPopulationDisabilityMistakeExcelVO(); setDisabilityMistake(oneData, mistake); mistake.setMistake("小区号不可为空"); mistakes.add(mistake); continue; } vo.setDoorNo(oneData.get(10).trim()); if(StringUtils.isEmpty(oneData.get(11))){ index++; ComMngPopulationDisabilityMistakeExcelVO mistake = new ComMngPopulationDisabilityMistakeExcelVO(); setDisabilityMistake(oneData, mistake); mistake.setMistake("楼排号不可为空"); mistakes.add(mistake); continue; } vo.setFloor(oneData.get(11).trim()); if(StringUtils.isEmpty(oneData.get(12))){ index++; ComMngPopulationDisabilityMistakeExcelVO mistake = new ComMngPopulationDisabilityMistakeExcelVO(); setDisabilityMistake(oneData, mistake); mistake.setMistake("单元号不可为空"); mistakes.add(mistake); continue; } vo.setUnitNo(oneData.get(12).trim()); if(StringUtils.isEmpty(oneData.get(13))){ index++; ComMngPopulationDisabilityMistakeExcelVO mistake = new ComMngPopulationDisabilityMistakeExcelVO(); setDisabilityMistake(oneData, mistake); mistake.setMistake("户室不可为空"); mistakes.add(mistake); continue; } vo.setHouseNo(oneData.get(13).trim()); if(StringUtils.isNotEmpty(oneData.get(14))){ Integer isOk = PopulHouseUseEnum.getCodeByName(oneData.get(14)); if(isOk.equals(-1)){ ComMngPopulationDisabilityMistakeExcelVO mistake = new ComMngPopulationDisabilityMistakeExcelVO(); index++; setDisabilityMistake(oneData, mistake); mistake.setMistake("您填写的是否租住有误"); mistakes.add(mistake); continue; } vo.setIsRent(isOk); }else { vo.setIsRent(PopulHouseUseEnum.getCodeByName("否")); } if(StringUtils.isNotEmpty(oneData.get(15))){ Integer isOk = PopulHouseStatusEnum.getCodeByName(oneData.get(15).trim()); if(isOk.equals(-1)){ index++; ComMngPopulationDisabilityMistakeExcelVO mistake = new ComMngPopulationDisabilityMistakeExcelVO(); setDisabilityMistake(oneData, mistake); mistake.setMistake("您填写的房屋状态有误"); mistakes.add(mistake); continue; } vo.setHouseStatus(isOk); } if(StringUtils.isNotEmpty(oneData.get(16))){ Integer isOk = PopulHousePurposeEnum.getCodeByName(oneData.get(16).trim()); if(isOk.equals(-1)){ index++; ComMngPopulationDisabilityMistakeExcelVO mistake = new ComMngPopulationDisabilityMistakeExcelVO(); setDisabilityMistake(oneData, mistake); mistake.setMistake("您填写的房屋用途有误"); mistakes.add(mistake); continue; } vo.setHousePurpose(PopulHousePurposeEnum.getCodeByName(oneData.get(16).trim())); } if(StringUtils.isNotEmpty(oneData.get(17))){ Integer isOk = PopulHouseControlStatusEnum.getCodeByName(oneData.get(17)); if(isOk.equals(-1)){ index++; ComMngPopulationDisabilityMistakeExcelVO mistake = new ComMngPopulationDisabilityMistakeExcelVO(); setDisabilityMistake(oneData, mistake); mistake.setMistake("您填写的管控状态有误"); mistakes.add(mistake); continue; } vo.setControlStatus(isOk); } if(StringUtils.isNotEmpty(oneData.get(18))){ Integer isOk = PopulOutOrLocalEnum.getCodeByName(oneData.get(18)); if(isOk.equals(-1)){ index++; ComMngPopulationDisabilityMistakeExcelVO mistake = new ComMngPopulationDisabilityMistakeExcelVO(); setDisabilityMistake(oneData, mistake); mistake.setMistake("您填写的本地/外地有误"); mistakes.add(mistake); continue; } vo.setOutOrLocal(isOk); }else { vo.setOutOrLocal(PopulOutOrLocalEnum.getCodeByName("本地")); } if(StringUtils.isNotEmpty(oneData.get(19))){ vo.setCensusRegister(oneData.get(19).trim()); } if(StringUtils.isNotEmpty(oneData.get(20))){ vo.setRemark(oneData.get(20).trim()); } vo.getUserTagStr().add("残疾人"); for (int i = 21; i < headSize; i++) { if (oneData.get(i) != null && oneData.get(i).equals("是")) { vo.getUserTagStr().add(headData.get(i).substring(0,headData.get(i).indexOf("("))); } } //将重复的数据进行MD5加密实现去重 String distinct = vo.getName() + vo.getCardNo() + vo.getRoad() + vo.getDoorNo() + vo.getFloor() + vo.getUnitNo() + vo.getHouseNo(); try { String distinctPass = PayUtil.MD5(distinct); if(StringUtils.isNotEmpty(distinctPass)){ vo.setDistinctPass(distinctPass); } }catch (Exception e){ log.error("组装MD5加密字段失败，数据表格行数：" + index); continue; } voList.add(vo); index++; } //客户需要暂时注释，等客户处理完成需要恢复 // if(populationImportErrorVOList.isEmpty()){ // R r = communityService.listSavePopulationServeExcelVO(voList, communityId); // if (!R.isOk(r)) { // throw new ServiceException(r.getMsg()); // } // }else{ // throw new ServiceException("500", JSON.toJSONString(populationImportErrorVOList)); // } //根据list中的IdCard城市来去重 List<ComMngPopulationDisabilityExcelVO> newVoList = voList.stream().filter(ListUtils.distinctByKey(ComMngPopulationDisabilityExcelVO::getDistinctPass)).collect(Collectors.toList()); log.info("开始进入业务层处理逻辑"); R r = communityService.listSavePopulationDisabilityExcelVO(newVoList, communityId); log.info("业务层处理逻辑完成"); if (!R.isOk(r)) { log.info("业务层处理成功"); List<ComMngPopulationDisabilityMistakeExcelVO> list = JSONArray.parseArray(JSONArray.toJSONString(r.getData()), ComMngPopulationDisabilityMistakeExcelVO.class); // String errMsg = r.getMsg(); // List<ComMngPopulationImportErrorVO> errorList = JSON.parseArray(errMsg,ComMngPopulationImportErrorVO.class); // if(!errorList.isEmpty()){ // populationImportErrorVOList.addAll(errorList); // } mistakes.addAll(list); log.info("将错误数据存入redis中"); valueOperations.set(Disability,JSONArray.toJSONString(mistakes),1, TimeUnit.HOURS); log.info("将错误数据存入redis中成功"); throw new ServiceException("500", Disability); }else{ log.info("业务层处理逻辑失败"); if(!mistakes.isEmpty()){ log.info("业务层处理逻辑失败，将错误数据缓存到redis中"); valueOperations.set(Disability,JSONArray.toJSONString(mistakes),1, TimeUnit.HOURS); log.info("业务层处理逻辑失败，将错误数据缓存到redis中成功"); throw new ServiceException("500", Disability); } } } catch (NumberFormatException e) { log.info("处理数据时失败"); e.printStackTrace(); // List<ComMngPopulationImportErrorVO> populationImportErrorVOList = new ArrayList<>(); // ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO(); // importErrorVO.setErrorPosition("第" + index + "行"); // importErrorVO.setErrorMsg("数据格式有误，请检查文档内数据"); // populationImportErrorVOList.add(importErrorVO); log.error("数据格式有误，第" + index + "行"); throw new ServiceException("500", "导入失败111"); } } private void setDisabilityMistake(Map<Integer, String> map, ComMngPopulationDisabilityMistakeExcelVO vo){ vo.setSerialNumber(map.get(0)); vo.setName(map.get(1)); vo.setCardNo(map.get(2)); vo.setCertificateSituation(map.get(3)); vo.setCultureLevel(map.get(4)); vo.setNation(map.get(5)); vo.setDisType(map.get(6)); vo.setDisLevel(map.get(7)); vo.setPhone(map.get(8)); vo.setRoad(map.get(9)); vo.setDoorNo(map.get(10)); vo.setFloor(map.get(11)); vo.setUnitNo(map.get(12)); vo.setHouseNo(map.get(13)); vo.setIsRent(map.get(14)); vo.setHouseStatus(map.get(15)); vo.setHousePurpose(map.get(16)); vo.setControlStatus(map.get(17)); vo.setOutOrLocal(map.get(18)); vo.setCensusRegister(map.get(19)); vo.setRemark(map.get(20)); } private String convertMarriage(String ma) { if(Objects.equals("已",ma) || Objects.equals("一",ma)){ ma = "已婚"; }else if(Objects.equals("未",ma) || Objects.equals("未婚婚",ma)){ ma = "未婚"; }else if(Objects.equals("初",ma)){ ma = "初婚"; }else if(Objects.equals("再",ma)){ ma = "再婚"; }else if(Objects.equals("复",ma)){ ma = "复婚"; }else if(Objects.equals("丧",ma) || Objects.equals("丧偶偶",ma)){ ma = "丧偶"; }else if(Objects.equals("离",ma) || Objects.equals("离异",ma) || Objects.equals("离异婚",ma)){ ma = "离婚"; }else if(Objects.equals("分",ma)){ ma = "分居"; } return ma; } private String convertRelation(String ra) { if(ra.contains("户主")){ ra = "户主"; }else if(Objects.equals(ra,"女儿")){ ra = "女"; }else if(Objects.equals(ra,"儿子") || Objects.equals(ra,"儿")){ ra = "子"; }else if(Objects.equals(ra,"大女")){ ra = "长女"; }else if(Objects.equals(ra,"二女") || Objects.equals(ra,"此女")){ ra = "次女"; }else if(Objects.equals(ra,"二子")){ ra = "次子"; }else if(Objects.equals(ra,"长男") || ra.contains("长子")){ ra = "长子"; }else if(Objects.equals(ra,"非亲属") || ra.contains("女友") || ra.contains("女朋友") || ra.contains("男友") || ra.contains("男朋友")){ ra = "其他"; }else if(Objects.equals(ra,"姐")){ ra = "姐姐"; }else if(Objects.equals(ra,"户主")){ ra = "本人"; }else if(Objects.equals(ra,"妻子") || Objects.equals(ra,"媳妇")){ ra = "妻"; }else if(Objects.equals(ra,"哥") || Objects.equals(ra,"哥哥")){ ra = "兄"; }else if(Objects.equals(ra,"丈夫")){ ra = "夫"; }else if(Objects.equals(ra,"丈母娘")){ ra = "岳母"; }else if(Objects.equals(ra,"继子") || Objects.equals(ra,"养子")){ ra = "养子或继子"; }else if(Objects.equals(ra,"继女") || Objects.equals(ra,"养女")){ ra = "养女或继女"; }else if(ra.contains("儿媳")||ra.contains("长媳")){ ra = "儿媳"; }else if(Objects.equals(ra,"三姨") || Objects.equals(ra,"姨姨")){ ra = "姨母"; }else if(Objects.equals(ra,"二外孙女")){ ra = "外孙女"; }else if(Objects.equals(ra,"外孙")){ ra = "外孙子"; }else if(Objects.equals(ra,"侄儿")){ ra = "侄子"; }else if(ra.contains("表")){ ra = "表兄弟、表姐妹"; }else if(Objects.equals(ra,"继父") || Objects.equals(ra,"养父")){ ra = "继父或养父"; }else if(Objects.equals(ra,"父")){ ra = "父亲"; }else if(Objects.equals(ra,"弟弟")){ ra = "弟"; }else if(Objects.equals(ra,"孙")){ ra = "孙子"; }else if(Objects.equals(ra,"孙媳妇") || Objects.equals(ra,"外孙媳妇")){ ra = "孙媳妇或外孙媳妇"; }else if(Objects.equals(ra,"妹")){ ra = "妹妹"; }else if(Objects.equals(ra,"祖父母")){ ra = "祖父母或外祖父母"; }else if(Objects.equals(ra,"姐妹") || Objects.equals(ra,"兄弟")){ ra = "兄弟姐妹"; }else if(Objects.equals(ra,"其他姐妹") || Objects.equals(ra,"其他兄弟")){ ra = "其他兄弟姐妹"; }else if(Objects.equals(ra,"奶奶")){ ra = "祖母"; }else if(Objects.equals(ra,"爷爷")){ ra = "祖父"; }else if(Objects.equals(ra,"爷爷")){ ra = "祖父"; }else if(ra.contains("堂")){ ra = "堂兄弟、堂姐妹"; } return ra; } } springcloud_k8s_panzhihuazhihuishequ/common/src/main/java/com/panzhihua/common/listen/ComMngPopulationVeteransExcelListen.java
@@ -1,5 +1,6 @@ package com.panzhihua.common.listen; import cn.hutool.core.date.DateUtil; import com.alibaba.excel.context.AnalysisContext; import com.alibaba.excel.event.AnalysisEventListener; import com.alibaba.fastjson.JSONArray; @@ -10,6 +11,8 @@ import com.panzhihua.common.exceptions.ServiceException; import com.panzhihua.common.model.vos.BcDictionaryVO; import com.panzhihua.common.model.vos.R; import com.panzhihua.common.model.vos.community.ComMngPopulationVeteransExcelVO; import com.panzhihua.common.model.vos.community.ComMngPopulationVeteransMistakeExcelVO; import com.panzhihua.common.model.vos.community.ComMngPopulationVeteransExcelVO; import com.panzhihua.common.model.vos.community.ComMngPopulationVeteransMistakeExcelVO; import com.panzhihua.common.service.community.CommunityService; @@ -121,8 +124,602 @@ int index = 2; try { String[] parsePatterns = new String[]{"yyyy-MM-dd", "yyyy-M-dd", "yyyy-MM-d", "yyyy-M-d", "yyyy/MM/dd", "yyyy/M/dd", "yyyy/MM/d", "yyyy/M/d"}; ArrayList<ComMngPopulationVeteransExcelVO> voList = Lists.newArrayList(); // List<ComMngPopulationImportErrorVO> populationImportErrorVOList = new ArrayList<>(); ArrayList<ComMngPopulationVeteransMistakeExcelVO> mistakes = Lists.newArrayList(); for (Map<Integer, String> oneData : list) { ComMngPopulationVeteransExcelVO vo = new ComMngPopulationVeteransExcelVO(); if (StringUtils.isNotEmpty(oneData.get(0))){ vo.setSerialNumber(oneData.get(0)); } //姓名和身份证都为空,为空户，无需操作,否则就解析年龄，性别，出生年月日 if(StringUtils.isNotEmpty(oneData.get(3)) || StringUtils.isNotEmpty(oneData.get(4))){ if(StringUtils.isEmpty(oneData.get(3))){ ComMngPopulationVeteransMistakeExcelVO mistake = new ComMngPopulationVeteransMistakeExcelVO(); index++; setVeteransMistake(oneData, mistake); mistake.setMistake("名字不可为空，请填写姓名"); mistakes.add(mistake); continue; } vo.setName(oneData.get(3)); if(StringUtils.isEmpty(oneData.get(4))){ ComMngPopulationVeteransMistakeExcelVO mistake = new ComMngPopulationVeteransMistakeExcelVO(); index++; setVeteransMistake(oneData, mistake); mistake.setMistake("身份证号不可为空，请填写身份证"); mistakes.add(mistake); continue; } //判断身份证号码位数 if(oneData.get(4).length() != 18){ ComMngPopulationVeteransMistakeExcelVO mistake = new ComMngPopulationVeteransMistakeExcelVO(); index++; setVeteransMistake(oneData, mistake); mistake.setMistake("身份证号位数有误，请检查身份证号码是否正确"); mistakes.add(mistake); continue; } vo.setCardNo(oneData.get(4).toUpperCase()); //根据身份证号码解析年龄以及性别 //获取用户生日 String birthday = vo.getCardNo().substring(6, 14); if(StringUtils.isNotEmpty(birthday)){ String year = birthday.substring(0,4); String month = birthday.substring(4,6); String day = birthday.substring(6,8); vo.setBirthday(year + "-" + month + "-" + day); } // //设置用户年龄 // vo.setAge(AgeUtils.getAgeFromBirthTime(birthday)); //获取用户性别 int sex = Integer.parseInt(vo.getCardNo().substring(16, 17)); if(sex%2 == 1){ vo.setSex(PopulSexEnum.nan.getCode()); }else{ vo.setSex(PopulSexEnum.nv.getCode()); } } if(StringUtils.isNotEmpty(oneData.get(1))){ vo.setPersonStatus(oneData.get(1)); } if(StringUtils.isNotEmpty(oneData.get(2))){ vo.setOrganization(oneData.get(2)); } if(StringUtils.isNotEmpty(oneData.get(5))){ vo.setPhone(oneData.get(5)); } if(StringUtils.isNotEmpty(oneData.get(6))){ vo.setPhoto(oneData.get(6)); } if(StringUtils.isNotEmpty(oneData.get(7))){ Integer isOk = PopulRegiterNatureEnum.getCodeByName(oneData.get(7).trim()); if(isOk.equals(-1)){ index++; ComMngPopulationVeteransMistakeExcelVO mistake = new ComMngPopulationVeteransMistakeExcelVO(); setVeteransMistake(oneData, mistake); mistake.setMistake("您填写的户籍性质有误"); mistakes.add(mistake); continue; } vo.setRegiterNature(isOk); } if(StringUtils.isNotEmpty(oneData.get(8))){ vo.setCensusRegister(oneData.get(8).trim()); } if(StringUtils.isNotEmpty(oneData.get(9))){ Integer isOk = PopulOutOrLocalEnum.getCodeByName(oneData.get(9)); if(isOk.equals(-1)){ index++; ComMngPopulationVeteransMistakeExcelVO mistake = new ComMngPopulationVeteransMistakeExcelVO(); setVeteransMistake(oneData, mistake); mistake.setMistake("您填写的本地/外地有误"); mistakes.add(mistake); continue; } vo.setOutOrLocal(isOk); }else { vo.setOutOrLocal(PopulOutOrLocalEnum.getCodeByName("本地")); } if(StringUtils.isEmpty(oneData.get(10))){ index++; ComMngPopulationVeteransMistakeExcelVO mistake = new ComMngPopulationVeteransMistakeExcelVO(); setVeteransMistake(oneData, mistake); mistake.setMistake("街路巷不可为空"); mistakes.add(mistake); continue; } vo.setRoad(oneData.get(10)); if(StringUtils.isEmpty(oneData.get(11))){ index++; ComMngPopulationVeteransMistakeExcelVO mistake = new ComMngPopulationVeteransMistakeExcelVO(); setVeteransMistake(oneData, mistake); mistake.setMistake("小区号不可为空"); mistakes.add(mistake); continue; } vo.setDoorNo(oneData.get(11).trim()); if(StringUtils.isEmpty(oneData.get(12))){ index++; ComMngPopulationVeteransMistakeExcelVO mistake = new ComMngPopulationVeteransMistakeExcelVO(); setVeteransMistake(oneData, mistake); mistake.setMistake("楼排号不可为空"); mistakes.add(mistake); continue; } vo.setFloor(oneData.get(12).trim()); if(StringUtils.isEmpty(oneData.get(13))){ index++; ComMngPopulationVeteransMistakeExcelVO mistake = new ComMngPopulationVeteransMistakeExcelVO(); setVeteransMistake(oneData, mistake); mistake.setMistake("单元号不可为空"); mistakes.add(mistake); continue; } vo.setUnitNo(oneData.get(13).trim()); if(StringUtils.isEmpty(oneData.get(14))){ index++; ComMngPopulationVeteransMistakeExcelVO mistake = new ComMngPopulationVeteransMistakeExcelVO(); setVeteransMistake(oneData, mistake); mistake.setMistake("户室不可为空"); mistakes.add(mistake); continue; } vo.setHouseNo(oneData.get(14).trim()); if(StringUtils.isNotEmpty(oneData.get(15))){ Integer isOk = PopulHouseUseEnum.getCodeByName(oneData.get(15)); if(isOk.equals(-1)){ ComMngPopulationVeteransMistakeExcelVO mistake = new ComMngPopulationVeteransMistakeExcelVO(); index++; setVeteransMistake(oneData, mistake); mistake.setMistake("您填写的是否租住有误"); mistakes.add(mistake); continue; } vo.setIsRent(isOk); }else { vo.setIsRent(PopulHouseUseEnum.getCodeByName("否")); } if(StringUtils.isNotEmpty(oneData.get(16))){ Integer isOk = PopulHouseStatusEnum.getCodeByName(oneData.get(16).trim()); if(isOk.equals(-1)){ index++; ComMngPopulationVeteransMistakeExcelVO mistake = new ComMngPopulationVeteransMistakeExcelVO(); setVeteransMistake(oneData, mistake); mistake.setMistake("您填写的房屋状态有误"); mistakes.add(mistake); continue; } vo.setHouseStatus(isOk); } if(StringUtils.isNotEmpty(oneData.get(17))){ Integer isOk = PopulHousePurposeEnum.getCodeByName(oneData.get(17).trim()); if(isOk.equals(-1)){ index++; ComMngPopulationVeteransMistakeExcelVO mistake = new ComMngPopulationVeteransMistakeExcelVO(); setVeteransMistake(oneData, mistake); mistake.setMistake("您填写的房屋用途有误"); mistakes.add(mistake); continue; } vo.setHousePurpose(PopulHousePurposeEnum.getCodeByName(oneData.get(14).trim())); } if(StringUtils.isNotEmpty(oneData.get(18))){ Integer isOk = PopulHouseControlStatusEnum.getCodeByName(oneData.get(18)); if(isOk.equals(-1)){ index++; ComMngPopulationVeteransMistakeExcelVO mistake = new ComMngPopulationVeteransMistakeExcelVO(); setVeteransMistake(oneData, mistake); mistake.setMistake("您填写的管控状态有误"); mistakes.add(mistake); continue; } vo.setControlStatus(isOk); } if(StringUtils.isNotEmpty(oneData.get(19))){ vo.setEnlistDate(DateUtil.parse(oneData.get(19))); } if(StringUtils.isNotEmpty(oneData.get(20))){ vo.setRetireDate(DateUtil.parse(oneData.get(20))); } if(StringUtils.isNotEmpty(oneData.get(21))){ vo.setPersonCategory(oneData.get(21).trim()); } if(StringUtils.isNotEmpty(oneData.get(22))){ vo.setNation(oneData.get(22).trim()); } if(StringUtils.isNotEmpty(oneData.get(23))){ vo.setHealthy(oneData.get(23).trim()); } if(StringUtils.isNotEmpty(oneData.get(24))){ Integer isOk = PopulPoliticalOutlookEnum.getCodeByName(oneData.get(24)); if(isOk.equals(-1)){ index++; ComMngPopulationVeteransMistakeExcelVO mistake = new ComMngPopulationVeteransMistakeExcelVO(); setVeteransMistake(oneData, mistake); mistake.setMistake("您填写的政治面貌有误"); mistakes.add(mistake); continue; } vo.setPoliticalOutlook(isOk); } if(StringUtils.isNotEmpty(oneData.get(25))){ Integer isOk = PopulMarriageEnum.getCodeByName(oneData.get(25)); if(isOk.equals(-1)){ index++; ComMngPopulationVeteransMistakeExcelVO mistake = new ComMngPopulationVeteransMistakeExcelVO(); setVeteransMistake(oneData, mistake); mistake.setMistake("您填写的政治面貌有误"); mistakes.add(mistake); continue; } vo.setMarriage(isOk); } if(StringUtils.isNotEmpty(oneData.get(26))){ vo.setHouseSituation(oneData.get(26).trim()); } if(StringUtils.isNotEmpty(oneData.get(27))){ vo.setHouseSituationOther(oneData.get(27).trim()); } if(StringUtils.isNotEmpty(oneData.get(28))){ vo.setBuildArea(oneData.get(28).trim()); } if(StringUtils.isNotEmpty(oneData.get(29))){ vo.setEmploymentSituation(oneData.get(29).trim()); } if(StringUtils.isNotEmpty(oneData.get(30))){ vo.setIncumbencyType(oneData.get(30).trim()); } if(StringUtils.isNotEmpty(oneData.get(31))){ vo.setEmploymentSituationOther(oneData.get(31).trim()); } if(StringUtils.isNotEmpty(oneData.get(32))){ vo.setEndowmentInsurance(oneData.get(32).trim()); } if(StringUtils.isNotEmpty(oneData.get(33))){ vo.setMedicalInsurance(oneData.get(33).trim()); } if(StringUtils.isNotEmpty(oneData.get(34))){ vo.setAnnualIncome(oneData.get(34).trim()); } if(StringUtils.isNotEmpty(oneData.get(35))){ vo.setFatherHealthy(oneData.get(35).trim()); } if(StringUtils.isNotEmpty(oneData.get(36))){ vo.setMotherHealthy(oneData.get(36).trim()); } if(StringUtils.isNotEmpty(oneData.get(37))){ vo.setSpouseHealthy(oneData.get(37).trim()); } if(StringUtils.isNotEmpty(oneData.get(38))){ vo.setSonHealthy(oneData.get(38).trim()); } if(StringUtils.isNotEmpty(oneData.get(39))){ vo.setDaughterHealthy(oneData.get(39).trim()); } if(StringUtils.isNotEmpty(oneData.get(40))){ vo.setMainDifficulty(oneData.get(40).trim()); } if(StringUtils.isNotEmpty(oneData.get(41))){ vo.setOtherDifficulty(oneData.get(41).trim()); } if(StringUtils.isNotEmpty(oneData.get(42))){ vo.setMainDemand(oneData.get(42).trim()); } if(StringUtils.isNotEmpty(oneData.get(43))){ vo.setMainDemandOther(oneData.get(43).trim()); } if(StringUtils.isNotEmpty(oneData.get(44))){ vo.setAcademicTraining(oneData.get(44).trim()); } if(StringUtils.isNotEmpty(oneData.get(45))){ Integer isOk = PopulHouseUseEnum.getCodeByName(oneData.get(45)); if(isOk.equals(-1)){ index++; ComMngPopulationVeteransMistakeExcelVO mistake = new ComMngPopulationVeteransMistakeExcelVO(); setVeteransMistake(oneData, mistake); mistake.setMistake("您填写的是否已参加退役军人培训（政府性质）有误"); mistakes.add(mistake); continue; } vo.setIsVeteransTraining(isOk); }else { vo.setIsVeteransTraining(PopulHouseUseEnum.getCodeByName("否")); } if(StringUtils.isNotEmpty(oneData.get(46))){ vo.setEducationBeforeEnlistment(oneData.get(46).trim()); } if(StringUtils.isNotEmpty(oneData.get(47))){ vo.setReEducation(oneData.get(47).trim()); } if(StringUtils.isNotEmpty(oneData.get(48))){ vo.setMajor(oneData.get(48).trim()); } if(StringUtils.isNotEmpty(oneData.get(49))){ vo.setMajorOther(oneData.get(49).trim()); } if(StringUtils.isNotEmpty(oneData.get(50))){ vo.setOnceEngagedIndustry(oneData.get(50).trim()); } if(StringUtils.isNotEmpty(oneData.get(51))){ vo.setOnceEngagedIndustryOther(oneData.get(51).trim()); } if(StringUtils.isNotEmpty(oneData.get(52))){ vo.setIntendedPlaceOfEmployment(oneData.get(52).trim()); } if(StringUtils.isNotEmpty(oneData.get(53))){ vo.setIntendedPlaceOfEmploymentOther(oneData.get(53).trim()); } if(StringUtils.isNotEmpty(oneData.get(54))){ vo.setUnemploymedEngagedIndustry(oneData.get(54).trim()); } if(StringUtils.isNotEmpty(oneData.get(55))){ vo.setUnemploymedEngagedIndustryOther(oneData.get(55).trim()); } if(StringUtils.isNotEmpty(oneData.get(56))){ Integer isOk = PopulHouseUseEnum.getCodeByName(oneData.get(56)); if(isOk.equals(-1)){ index++; ComMngPopulationVeteransMistakeExcelVO mistake = new ComMngPopulationVeteransMistakeExcelVO(); setVeteransMistake(oneData, mistake); mistake.setMistake("您填写的是否有创业意愿有误"); mistakes.add(mistake); continue; } vo.setIsBusiness(isOk); }else { vo.setIsBusiness(PopulHouseUseEnum.getCodeByName("否")); } if(StringUtils.isNotEmpty(oneData.get(57))){ vo.setBusinessDesire(oneData.get(57).trim()); } if(StringUtils.isNotEmpty(oneData.get(58))){ vo.setRemark(oneData.get(58).trim()); } if(StringUtils.isNotEmpty(oneData.get(59))){ vo.setFillUnit(oneData.get(59).trim()); } if(StringUtils.isNotEmpty(oneData.get(60))){ vo.setFillPerson(oneData.get(50).trim()); } if(StringUtils.isNotEmpty(oneData.get(61))){ vo.setFillPersonPhone(oneData.get(61).trim()); } vo.getUserTagStr().add("退役军人"); for (int i = 62; i < headSize; i++) { if (oneData.get(i) != null && oneData.get(i).equals("是")) { vo.getUserTagStr().add(headData.get(i).substring(0,headData.get(i).indexOf("("))); } } //将重复的数据进行MD5加密实现去重 String distinct = vo.getName() + vo.getCardNo() + vo.getRoad() + vo.getDoorNo() + vo.getFloor() + vo.getUnitNo() + vo.getHouseNo(); try { String distinctPass = PayUtil.MD5(distinct); if(StringUtils.isNotEmpty(distinctPass)){ vo.setDistinctPass(distinctPass); } }catch (Exception e){ log.error("组装MD5加密字段失败，数据表格行数：" + index); continue; } voList.add(vo); index++; } //客户需要暂时注释，等客户处理完成需要恢复 // if(populationImportErrorVOList.isEmpty()){ // R r = communityService.listSavePopulationServeExcelVO(voList, communityId); // if (!R.isOk(r)) { // throw new ServiceException(r.getMsg()); // } // }else{ // throw new ServiceException("500", JSON.toJSONString(populationImportErrorVOList)); // } //根据list中的IdCard城市来去重 List<ComMngPopulationVeteransExcelVO> newVoList = voList.stream().filter(ListUtils.distinctByKey(ComMngPopulationVeteransExcelVO::getDistinctPass)).collect(Collectors.toList()); log.info("开始进入业务层处理逻辑"); R r = communityService.listSavePopulationVeteransExcelVO(newVoList, communityId); log.info("业务层处理逻辑完成"); if (!R.isOk(r)) { log.info("业务层处理成功"); List<ComMngPopulationVeteransMistakeExcelVO> list = JSONArray.parseArray(JSONArray.toJSONString(r.getData()), ComMngPopulationVeteransMistakeExcelVO.class); // String errMsg = r.getMsg(); // List<ComMngPopulationImportErrorVO> errorList = JSON.parseArray(errMsg,ComMngPopulationImportErrorVO.class); // if(!errorList.isEmpty()){ // populationImportErrorVOList.addAll(errorList); // } mistakes.addAll(list); log.info("将错误数据存入redis中"); valueOperations.set(Veterans,JSONArray.toJSONString(mistakes),1, TimeUnit.HOURS); log.info("将错误数据存入redis中成功"); throw new ServiceException("500", Veterans); }else{ log.info("业务层处理逻辑失败"); if(!mistakes.isEmpty()){ log.info("业务层处理逻辑失败，将错误数据缓存到redis中"); valueOperations.set(Veterans,JSONArray.toJSONString(mistakes),1, TimeUnit.HOURS); log.info("业务层处理逻辑失败，将错误数据缓存到redis中成功"); throw new ServiceException("500", Veterans); } } } catch (NumberFormatException e) { log.info("处理数据时失败"); e.printStackTrace(); // List<ComMngPopulationImportErrorVO> populationImportErrorVOList = new ArrayList<>(); // ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO(); // importErrorVO.setErrorPosition("第" + index + "行"); // importErrorVO.setErrorMsg("数据格式有误，请检查文档内数据"); // populationImportErrorVOList.add(importErrorVO); log.error("数据格式有误，第" + index + "行"); throw new ServiceException("500", "导入失败111"); } } private void setVeteransMistake(Map<Integer, String> map, ComMngPopulationVeteransMistakeExcelVO vo){ vo.setSerialNumber(map.get(0)); vo.setPersonStatus(map.get(1)); vo.setOrganization(map.get(2)); vo.setName(map.get(3)); vo.setCardNo(map.get(4)); vo.setPhone(map.get(5)); vo.setPhoto(map.get(6)); vo.setRegiterNature(map.get(7)); vo.setCensusRegister(map.get(8)); vo.setOutOrLocal(map.get(9)); vo.setRoad(map.get(10)); vo.setDoorNo(map.get(11)); vo.setFloor(map.get(12)); vo.setUnitNo(map.get(13)); vo.setHouseNo(map.get(14)); vo.setIsRent(map.get(15)); vo.setHouseStatus(map.get(16)); vo.setHousePurpose(map.get(17)); vo.setControlStatus(map.get(18)); vo.setEnlistDate(map.get(19)); vo.setRetireDate(map.get(20)); vo.setPersonCategory(map.get(21)); vo.setNation(map.get(22)); vo.setHealthy(map.get(23)); vo.setPoliticalOutlook(map.get(24)); vo.setMarriage(map.get(25)); vo.setHouseSituation(map.get(26)); vo.setHouseSituationOther(map.get(27)); vo.setBuildArea(map.get(28)); vo.setEmploymentSituation(map.get(29)); vo.setIncumbencyType(map.get(30)); vo.setEmploymentSituationOther(map.get(31)); vo.setEndowmentInsurance(map.get(32)); vo.setMedicalInsurance(map.get(33)); vo.setAnnualIncome(map.get(34)); vo.setFatherHealthy(map.get(35)); vo.setMotherHealthy(map.get(36)); vo.setSpouseHealthy(map.get(37)); vo.setSonHealthy(map.get(38)); vo.setDaughterHealthy(map.get(39)); vo.setMainDifficulty(map.get(40)); vo.setOtherDifficulty(map.get(41)); vo.setMainDemand(map.get(42)); vo.setMainDemandOther(map.get(43)); vo.setAcademicTraining(map.get(44)); vo.setIsVeteransTraining(map.get(45)); vo.setEducationBeforeEnlistment(map.get(46)); vo.setReEducation(map.get(47)); vo.setMajor(map.get(48)); vo.setMajorOther(map.get(49)); vo.setOnceEngagedIndustry(map.get(50)); vo.setOnceEngagedIndustryOther(map.get(51)); vo.setIntendedPlaceOfEmployment(map.get(52)); vo.setIntendedPlaceOfEmploymentOther(map.get(53)); vo.setUnemploymedEngagedIndustry(map.get(54)); vo.setUnemploymedEngagedIndustryOther(map.get(55)); vo.setIsBusiness(map.get(56)); vo.setBusinessDesire(map.get(57)); vo.setRemark(map.get(58)); vo.setFillUnit(map.get(59)); vo.setFillPerson(map.get(60)); vo.setFillPersonPhone(map.get(61)); } private String convertMarriage(String ma) { if(Objects.equals("已",ma) || Objects.equals("一",ma)){ ma = "已婚"; }else if(Objects.equals("未",ma) || Objects.equals("未婚婚",ma)){ ma = "未婚"; }else if(Objects.equals("初",ma)){ ma = "初婚"; }else if(Objects.equals("再",ma)){ ma = "再婚"; }else if(Objects.equals("复",ma)){ ma = "复婚"; }else if(Objects.equals("丧",ma) || Objects.equals("丧偶偶",ma)){ ma = "丧偶"; }else if(Objects.equals("离",ma) || Objects.equals("离异",ma) || Objects.equals("离异婚",ma)){ ma = "离婚"; }else if(Objects.equals("分",ma)){ ma = "分居"; } return ma; } private String convertRelation(String ra) { if(ra.contains("户主")){ ra = "户主"; }else if(Objects.equals(ra,"女儿")){ ra = "女"; }else if(Objects.equals(ra,"儿子") || Objects.equals(ra,"儿")){ ra = "子"; }else if(Objects.equals(ra,"大女")){ ra = "长女"; }else if(Objects.equals(ra,"二女") || Objects.equals(ra,"此女")){ ra = "次女"; }else if(Objects.equals(ra,"二子")){ ra = "次子"; }else if(Objects.equals(ra,"长男") || ra.contains("长子")){ ra = "长子"; }else if(Objects.equals(ra,"非亲属") || ra.contains("女友") || ra.contains("女朋友") || ra.contains("男友") || ra.contains("男朋友")){ ra = "其他"; }else if(Objects.equals(ra,"姐")){ ra = "姐姐"; }else if(Objects.equals(ra,"户主")){ ra = "本人"; }else if(Objects.equals(ra,"妻子") || Objects.equals(ra,"媳妇")){ ra = "妻"; }else if(Objects.equals(ra,"哥") || Objects.equals(ra,"哥哥")){ ra = "兄"; }else if(Objects.equals(ra,"丈夫")){ ra = "夫"; }else if(Objects.equals(ra,"丈母娘")){ ra = "岳母"; }else if(Objects.equals(ra,"继子") || Objects.equals(ra,"养子")){ ra = "养子或继子"; }else if(Objects.equals(ra,"继女") || Objects.equals(ra,"养女")){ ra = "养女或继女"; }else if(ra.contains("儿媳")||ra.contains("长媳")){ ra = "儿媳"; }else if(Objects.equals(ra,"三姨") || Objects.equals(ra,"姨姨")){ ra = "姨母"; }else if(Objects.equals(ra,"二外孙女")){ ra = "外孙女"; }else if(Objects.equals(ra,"外孙")){ ra = "外孙子"; }else if(Objects.equals(ra,"侄儿")){ ra = "侄子"; }else if(ra.contains("表")){ ra = "表兄弟、表姐妹"; }else if(Objects.equals(ra,"继父") || Objects.equals(ra,"养父")){ ra = "继父或养父"; }else if(Objects.equals(ra,"父")){ ra = "父亲"; }else if(Objects.equals(ra,"弟弟")){ ra = "弟"; }else if(Objects.equals(ra,"孙")){ ra = "孙子"; }else if(Objects.equals(ra,"孙媳妇") || Objects.equals(ra,"外孙媳妇")){ ra = "孙媳妇或外孙媳妇"; }else if(Objects.equals(ra,"妹")){ ra = "妹妹"; }else if(Objects.equals(ra,"祖父母")){ ra = "祖父母或外祖父母"; }else if(Objects.equals(ra,"姐妹") || Objects.equals(ra,"兄弟")){ ra = "兄弟姐妹"; }else if(Objects.equals(ra,"其他姐妹") || Objects.equals(ra,"其他兄弟")){ ra = "其他兄弟姐妹"; }else if(Objects.equals(ra,"奶奶")){ ra = "祖母"; }else if(Objects.equals(ra,"爷爷")){ ra = "祖父"; }else if(Objects.equals(ra,"爷爷")){ ra = "祖父"; }else if(ra.contains("堂")){ ra = "堂兄弟、堂姐妹"; } return ra; } } springcloud_k8s_panzhihuazhihuishequ/common/src/main/java/com/panzhihua/common/model/vos/community/ComMngPopulationDisabilityExcelVO.java
New file @@ -0,0 +1,115 @@ package com.panzhihua.common.model.vos.community; import com.alibaba.excel.annotation.ExcelProperty; import com.google.common.collect.Lists; import com.panzhihua.common.model.helper.encrypt.EncryptDecryptClass; import com.panzhihua.common.model.helper.encrypt.EncryptDecryptField; import lombok.Data; import java.io.Serializable; import java.util.Date; import java.util.List; /** * @description: 批量导入残疾人 * @author: txb */ @Data @EncryptDecryptClass public class ComMngPopulationDisabilityExcelVO implements Serializable { @ExcelProperty(value = "序号", index = 0) private String serialNumber; @ExcelProperty(value = "姓名", index = 1) private String name; @ExcelProperty(value = "身份证号码", index = 2) @EncryptDecryptField private String cardNo; @ExcelProperty(value = "办证状况", index = 3) private String certificateSituation; @ExcelProperty(value = "文化程度" ,index = 4) private Integer cultureLevel; @ExcelProperty(value = "民族", index = 5) private String nation; @ExcelProperty(value = "残疾类别", index = 6) private String disType; @ExcelProperty(value = "残疾等级", index = 7) private String disLevel; @ExcelProperty(value = "联系电话", index = 8) private String phone; @ExcelProperty(value = "街/路/巷", index = 9) private String road; @ExcelProperty(value = "小区号", index = 10) private String doorNo; @ExcelProperty(value = "楼排号", index = 11) private String floor; @ExcelProperty(value = "单元号", index = 12) private String unitNo; @ExcelProperty(value = "户室（房间号）", index = 13) private String houseNo; @ExcelProperty(value = "是否租住", index = 14) private Integer isRent; @ExcelProperty(value = "房屋状态(1.自住 2.租住 3.其他)", index = 15) private Integer houseStatus; @ExcelProperty(value = "房屋用途(1.住宅 2.公寓 3.宿舍 4.仓库 5.其他）", index = 16) private Integer housePurpose; @ExcelProperty(value = "管控状态(1.常规 2.关注 3.管控)", index = 17) private Integer controlStatus; @ExcelProperty(value = "外地/本地（1.本地 2.外地）", index = 18) private Integer outOrLocal; @ExcelProperty(value = "户口所在地", index = 19) private String censusRegister; @ExcelProperty(value = "备注", index = 20) private String remark; /** * 去重字段，使用（姓名+身份证号+街路巷+小区号+楼牌号+单元号+户室）组合字段进行MD5加密实现去重 */ private String distinctPass; /** * 年龄 */ private Integer age; /** * 性别（1.男 2.女） */ private Integer sex; /** * 出生年月日 */ private String birthday; /** * 扩展字段用逗号隔开 */ private List<String> userTagStr = Lists.newArrayList(); private Long houseId; /** * 地址 */ private String address; } springcloud_k8s_panzhihuazhihuishequ/common/src/main/java/com/panzhihua/common/model/vos/community/ComMngPopulationDisabilityMistakeExcelVO.java
New file @@ -0,0 +1,86 @@ package com.panzhihua.common.model.vos.community; import com.alibaba.excel.annotation.ExcelProperty; import com.google.common.collect.Lists; import com.panzhihua.common.model.helper.encrypt.EncryptDecryptClass; import com.panzhihua.common.model.helper.encrypt.EncryptDecryptField; import lombok.Data; import java.io.Serializable; import java.util.List; /** * @description: 批量导入残疾人 * @author: txb */ @Data @EncryptDecryptClass public class ComMngPopulationDisabilityMistakeExcelVO implements Serializable { @ExcelProperty(value = "序号", index = 0) private String serialNumber; @ExcelProperty(value = "姓名", index = 1) private String name; @ExcelProperty(value = "身份证号码", index = 2) @EncryptDecryptField private String cardNo; @ExcelProperty(value = "办证状况", index = 3) private String certificateSituation; @ExcelProperty(value = "文化程度" ,index = 4) private String cultureLevel; @ExcelProperty(value = "民族", index = 5) private String nation; @ExcelProperty(value = "残疾类别", index = 6) private String disType; @ExcelProperty(value = "残疾等级", index = 7) private String disLevel; @ExcelProperty(value = "联系电话", index = 8) private String phone; @ExcelProperty(value = "街/路/巷", index = 9) private String road; @ExcelProperty(value = "小区号", index = 10) private String doorNo; @ExcelProperty(value = "楼排号", index = 11) private String floor; @ExcelProperty(value = "单元号", index = 12) private String unitNo; @ExcelProperty(value = "户室（房间号）", index = 13) private String houseNo; @ExcelProperty(value = "是否租住", index = 14) private String isRent; @ExcelProperty(value = "房屋状态(1.自住 2.租住 3.其他)", index = 15) private String houseStatus; @ExcelProperty(value = "房屋用途(1.住宅 2.公寓 3.宿舍 4.仓库 5.其他）", index = 16) private String housePurpose; @ExcelProperty(value = "管控状态(1.常规 2.关注 3.管控)", index = 17) private String controlStatus; @ExcelProperty(value = "外地/本地（1.本地 2.外地）", index = 18) private String outOrLocal; @ExcelProperty(value = "户口所在地", index = 19) private String censusRegister; @ExcelProperty(value = "备注", index = 20) private String remark; @ExcelProperty(value = "错误信息", index = 21) private String mistake; } springcloud_k8s_panzhihuazhihuishequ/common/src/main/java/com/panzhihua/common/model/vos/community/ComMngPopulationVeteransMistakeExcelVO.java
@@ -76,10 +76,10 @@ private String controlStatus; @ExcelProperty(value = "入伍时间", index = 19) private String enlistString; private String enlistDate; @ExcelProperty(value = "入伍时间", index = 20) private String retireString; private String retireDate; @ExcelProperty(value = "人员类别", index = 21) private String personCategory; springcloud_k8s_panzhihuazhihuishequ/common/src/main/java/com/panzhihua/common/service/community/CommunityService.java
@@ -1773,6 +1773,26 @@ R listSavePopulationSentenceExcelVO(@RequestBody List<ComMngPopulationSentenceExcelVO> list, @RequestParam(value = "communityId") Long communityId); /** * 批量导入退役军人 * * @param list 退役军人数据 * @param communityId 社区id * @return 导入结果 */ @PostMapping("/common/data/population/importVeterans") R listSavePopulationVeteransExcelVO(@RequestBody List<ComMngPopulationVeteransExcelVO> list, @RequestParam(value = "communityId") Long communityId); /** * 批量导入残疾人 * * @param list 残疾人数据 * @param communityId 社区id * @return 导入结果 */ @PostMapping("/common/data/population/importDisability") R listSavePopulationDisabilityExcelVO(@RequestBody List<ComMngPopulationDisabilityExcelVO> list, @RequestParam(value = "communityId") Long communityId); /** * 确认导入实有人口（有则更新，无则新建） * * @param list 用户信息 springcloud_k8s_panzhihuazhihuishequ/community_backstage/src/main/java/com/panzhihua/community_backstage/api/PopulationApi.java
@@ -248,6 +248,24 @@ return R.ok(); } @ApiOperation(value = "excel导入残疾人") @PostMapping(value = "/serve/importDisability", consumes = "multipart/*", headers = "content-type=multipart/form-date") public R importDisability(@RequestParam MultipartFile file, HttpServletRequest request) { //获取文件名 String fileName = file.getOriginalFilename(); log.info("传入文件名字【{}】", fileName); InputStream inputStream = null; try { inputStream = file.getInputStream(); ComMngPopulationDisabilityExcelListen mngPopulationDisabilityExcelListen = new ComMngPopulationDisabilityExcelListen(communityService, this.getCommunityId(),userName,password,host,port,excelUrl,stringRedisTemplate); EasyExcel.read(inputStream, null, mngPopulationDisabilityExcelListen).sheet().doRead(); } catch (IOException e) { log.error("导入模板失败【{}】", e.getMessage()); e.printStackTrace(); } return R.ok(); } /** * 确认导入实有人口（有则更新，无则新建） * @@ -2628,4 +2646,116 @@ return R.fail(); } } @ApiOperation(value = "下载导入失败退役军人数据") @PostMapping("/download/error/populationVeterans") public R downloadErrorVeteransPopulation(@RequestParam(value = "key") String key) { List<ComMngPopulationVeteransMistakeExcelVO> list = new ArrayList<>(); Boolean isExits = stringRedisTemplate.hasKey(key); ValueOperations<String, String> valueOperations = stringRedisTemplate.opsForValue(); if(isExits){ String json = valueOperations.get(key); list= JSONArray.parseArray(json,ComMngPopulationVeteransMistakeExcelVO.class); } // List<ComMngPopulationMistakeExcelVO> list = JSON.parseArray(mistakes,ComMngPopulationMistakeExcelVO.class); //生成动态模板excel通过ftp工具上传到主节点，然后返回模板下载地址 String ftpUrl = "/mnt/data/web/excel/"; String nowDate = DateUtils.getCurrentDateString(); String name = "退役军人错误数据" + nowDate +".xlsx"; try { SFTPUtil sftp = new SFTPUtil(userName, password, host, port); sftp.login(); boolean existDir = sftp.isExistDir(ftpUrl + name); if (!existDir) { String property = System.getProperty("user.dir"); String fileName = property + File.separator + name; // 这里 需要指定写用哪个class去写 ExcelWriter excelWriter = null; InputStream inputStream = null; try { excelWriter = EasyExcel.write(fileName, ComMngPopulationVeteransMistakeExcelVO.class).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).registerWriteHandler(new CustomSheetWriteHandler()).build(); WriteSheet writeSheet = EasyExcel.writerSheet("退役军人错误数据").build(); excelWriter.write(list, writeSheet); excelWriter.finish(); File file = new File(fileName); inputStream = new FileInputStream(file); sftp.uploadMore(ftpUrl, name, inputStream); sftp.logout(); inputStream.close(); String absolutePath = file.getAbsolutePath(); boolean delete = file.delete(); log.info("删除excel【{}】结果【{}】", absolutePath, delete); } finally { // 千万别忘记finish 会帮忙关闭流 if (inputStream != null) { inputStream.close(); } if (excelWriter != null) { excelWriter.finish(); } } } return R.ok(excelUrl + name); } catch (Exception e) { e.printStackTrace(); log.error("文件传输失败【{}】", e.getMessage()); return R.fail(); } } @ApiOperation(value = "下载导入失败残疾人数据") @PostMapping("/download/error/populationDisability") public R downloadErrorDisabilityPopulation(@RequestParam(value = "key") String key) { List<ComMngPopulationDisabilityMistakeExcelVO> list = new ArrayList<>(); Boolean isExits = stringRedisTemplate.hasKey(key); ValueOperations<String, String> valueOperations = stringRedisTemplate.opsForValue(); if(isExits){ String json = valueOperations.get(key); list= JSONArray.parseArray(json,ComMngPopulationDisabilityMistakeExcelVO.class); } // List<ComMngPopulationMistakeExcelVO> list = JSON.parseArray(mistakes,ComMngPopulationMistakeExcelVO.class); //生成动态模板excel通过ftp工具上传到主节点，然后返回模板下载地址 String ftpUrl = "/mnt/data/web/excel/"; String nowDate = DateUtils.getCurrentDateString(); String name = "残疾人错误数据" + nowDate +".xlsx"; try { SFTPUtil sftp = new SFTPUtil(userName, password, host, port); sftp.login(); boolean existDir = sftp.isExistDir(ftpUrl + name); if (!existDir) { String property = System.getProperty("user.dir"); String fileName = property + File.separator + name; // 这里 需要指定写用哪个class去写 ExcelWriter excelWriter = null; InputStream inputStream = null; try { excelWriter = EasyExcel.write(fileName, ComMngPopulationDisabilityMistakeExcelVO.class).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).registerWriteHandler(new CustomSheetWriteHandler()).build(); WriteSheet writeSheet = EasyExcel.writerSheet("残疾人错误数据").build(); excelWriter.write(list, writeSheet); excelWriter.finish(); File file = new File(fileName); inputStream = new FileInputStream(file); sftp.uploadMore(ftpUrl, name, inputStream); sftp.logout(); inputStream.close(); String absolutePath = file.getAbsolutePath(); boolean delete = file.delete(); log.info("删除excel【{}】结果【{}】", absolutePath, delete); } finally { // 千万别忘记finish 会帮忙关闭流 if (inputStream != null) { inputStream.close(); } if (excelWriter != null) { excelWriter.finish(); } } } return R.ok(excelUrl + name); } catch (Exception e) { e.printStackTrace(); log.error("文件传输失败【{}】", e.getMessage()); return R.fail(); } } } springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/api/CommonDataApi.java
@@ -421,6 +421,32 @@ } /** * 批量导入退役军人 * * @param list 退役军人数据 * @param communityId 社区id * @return 导入结果 */ @Transactional(rollbackFor = Exception.class) @PostMapping("population/importVeterans") public R listSavePopulationVeteransExcelVO(@RequestBody List<ComMngPopulationVeteransExcelVO> list, @RequestParam(value = "communityId") Long communityId){ return comMngPopulationService.listSaveVeteransPopulation(list, communityId); } /** * 批量导入残疾人 * * @param list 残疾人数据 * @param communityId 社区id * @return 导入结果 */ @Transactional(rollbackFor = Exception.class) @PostMapping("population/importDisability") public R listSavePopulationDisabilityExcelVO(@RequestBody List<ComMngPopulationDisabilityExcelVO> list, @RequestParam(value = "communityId") Long communityId){ return comMngPopulationService.listSaveDisabilityPopulation(list, communityId); } /** * 确认导入实有人口（有则更新，无则新建） * * @param list 用户信息 springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/service/ComMngPopulationService.java
@@ -143,6 +143,23 @@ */ R listSaveSentencePopulation(List<ComMngPopulationSentenceExcelVO> list, Long communityId); /** * 批量导入退役军人 * * @param list 退役军人数据 * @param communityId 社区id * @return 导入结果 */ R listSaveVeteransPopulation(List<ComMngPopulationVeteransExcelVO> list, Long communityId); /** * 批量导入残疾人 * * @param list 残疾人数据 * @param communityId 社区id * @return 导入结果 */ R listSaveDisabilityPopulation(List<ComMngPopulationDisabilityExcelVO> list, Long communityId); /** * 确认导入实有人口（有则更新，无则新建） springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/service/impl/ComMngPopulationServiceImpl.java
@@ -1981,6 +1981,412 @@ return R.ok(mistakes); } @Override @Transactional(rollbackFor = Exception.class) public R listSaveVeteransPopulation(List<ComMngPopulationVeteransExcelVO> list, Long communityId) { //需要新增的房屋集合 List<ComMngPopulationHouseDO> houseList = new ArrayList<>(); //需要新增的人口集合 List<ComMngPopulationDO> saveList = new ArrayList<>(); //需要修改的人口集合 List<ComMngPopulationDO> updateList = new ArrayList<>(); //需要新增的人口与房屋关系集合 List<ComMngPopulationHouseUserDO> houseUserList = new ArrayList<>(); log.info("开始处理导入数据"); List<ComMngPopulationVeteransMistakeExcelVO> mistakes = new ArrayList<>(); try { //查询社区信息 log.info("开始查询社区信息数据"); ComPopulationActVO populationActVO = comActDAO.getPopulationActById(communityId); if(populationActVO == null){ log.error("未查询到社区信息"); return R.fail("未查询到社区信息"); } log.info("开始查询社区信息数据完成"); //查询当前社区标签列表 List<String> labelList = new ArrayList<>(); List<ComMngUserTagDO> comMngUserTagDOS = comMngUserTagDAO.selectList(new QueryWrapper<ComMngUserTagDO>().eq("sys_flag", 1).or().eq("community_id", communityId)); if (!ObjectUtils.isEmpty(comMngUserTagDOS)) { labelList = comMngUserTagDOS.stream().map(comMngUserTagDO -> comMngUserTagDO.getTagName()).collect(Collectors.toList()); } StringBuilder areaPath = new StringBuilder(); areaPath.append(populationActVO.getProvinceName()).append(">").append(populationActVO.getCityName()).append(">").append(populationActVO.getDistrictName()).append(">"); //处理实有人口信息 Integer nub = 1; //查询所有人口数据放入HashMap中 List<ComMngPopulationDO> populationList = this.baseMapper.selectList(null); HashMap<String,Object> populationMap = new HashMap<>(); populationList.forEach(population -> { String key = population.getCardNo(); populationMap.put(key,population); }); //查询所有房屋信息放入到HashMap中 List<ComMngPopulationHouseDO> houseLists = comMngPopulationHouseDAO.selectList(null); HashMap<String,Object> houseMap = new HashMap<>(); houseLists.forEach(house -> { String key = house.getCommunityId() + house.getVillageId() + house.getFloor() + house.getUnitNo() + house.getHouseNo(); houseMap.put(key,house); }); List<ComMngVillageDO> villageList = comActVillageDAO.selectList(null); HashMap<String,Object> villageMap = new HashMap<>(); villageList.forEach(village -> { String key = village.getCommunityId() + village.getAlley() + village.getHouseNum(); villageMap.put(key,village); }); List<ComMngPopulationHouseUserDO> houseUserLists = comMngPopulationHouseUserDAO.selectList(null); HashMap<String,Object> houseUserMap = new HashMap<>(); houseUserLists.forEach(houseUser -> { String key = houseUser.getPopulId() + houseUser.getHouseId() + ""; houseUserMap.put(key,houseUser); }); for (ComMngPopulationVeteransExcelVO vo : list) { if (vo.getDoorNo().contains("号")) { vo.setDoorNo(vo.getDoorNo().replace("号","")); } if(vo.getFloor().contains("栋")){ vo.setFloor(vo.getFloor().replace("栋","")); } if(vo.getUnitNo().contains("单元")){ vo.setUnitNo(vo.getUnitNo().replace("单元","")); } if(vo.getHouseNo().contains("号")){ vo.setHouseNo(vo.getHouseNo().replace("号","")); } log.info("开始查询小区街路巷是否存在"); //查询小区街路巷是否存在 ComMngVillageDO comMngVillageDO = null; String villageKey = communityId + vo.getRoad() + vo.getDoorNo(); if(!isOnly(villageKey,villageMap)){ comMngVillageDO = (ComMngVillageDO)villageMap.get(villageKey); }else{ ComMngPopulationVeteransMistakeExcelVO mistake = new ComMngPopulationVeteransMistakeExcelVO(); BeanUtils.copyProperties(vo,mistake); setVeteransMistake(mistake, vo); mistake.setMistake("街路巷或小区号不存在，请先在“小区管理”中添加该小区:街路巷："+ vo.getRoad() +"，小区号:" + vo.getDoorNo()); mistakes.add(mistake); log.info("未查询到街路巷："+ vo.getRoad() +"，小区号:" + vo.getDoorNo()); continue; } StringBuilder address = new StringBuilder(); address.append(populationActVO.getProvinceName()).append(populationActVO.getCityName()).append(populationActVO.getDistrictName()) .append(populationActVO.getStreetName()).append(comMngVillageDO.getAlley()).append(vo.getDoorNo()).append("号").append(vo.getFloor()).append("栋") .append(vo.getUnitNo()).append("单元").append(vo.getHouseNo()).append("号"); vo.setAddress(address.toString()); log.info("开始查询小区街路巷是否存在完成"); log.info("开始查询房屋是否存在"); //先判断房屋是否存在 ComMngPopulationHouseDO populationHouseDO = null; String houseKey = communityId + comMngVillageDO.getVillageId() + vo.getFloor() + vo.getUnitNo() + vo.getHouseNo(); if(isOnly(houseKey,houseMap)){ if(!houseList.isEmpty()){ for (ComMngPopulationHouseDO house:houseList) { if(house.getVillageId().equals(comMngVillageDO.getVillageId()) && house.getCommunityId().equals(communityId) && house.getFloor().equals(vo.getFloor()) && house.getUnitNo().equals(vo.getUnitNo()) && house.getHouseNo().equals(vo.getHouseNo())){ populationHouseDO = house; break; } } } if(populationHouseDO == null){ //房屋信息不存在建立房屋信息 populationHouseDO = saveVeteransPopulationHouse(vo, comMngVillageDO, communityId, areaPath, populationActVO.getName()); houseList.add(populationHouseDO); } }else{ populationHouseDO = (ComMngPopulationHouseDO)houseMap.get(houseKey); } vo.setHouseId(populationHouseDO.getId()); log.info("开始查询房屋是否存在完成"); if (StringUtils.isEmpty(vo.getName()) && StringUtils.isEmpty(vo.getCardNo())) { //空户处理完房屋信息，直接返回 continue; } String cardNoAES = AESUtil.encrypt128(vo.getCardNo(), aesKey); //判断实有人口是否已存在 log.info("开始查询实有人口是否已存在"); ComMngPopulationDO populationDO = null; String populationKey = vo.getCardNo(); if(!isOnly(populationKey,populationMap)){ //存在实有人口信息，则更新 populationDO = (ComMngPopulationDO)populationMap.get(populationKey); ComMngPopulationDO updatePopulationDO = updateVeteransPopulationDO(vo,populationDO,labelList); updateList.add(updatePopulationDO); }else{ //不存在实有人口,则新增 populationDO = saveVeteransPopulationDO(vo, populationActVO, comMngVillageDO,labelList); saveList.add(populationDO); } log.info("开始查询实有人口是否已存在完成"); log.info("开始查询实有人口房屋居住信息"); //处理实有人口房屋居住信息 if (populationDO != null) { ComMngPopulationHouseUserDO populationHouseUserDO = null; String houseUserKey = populationDO.getId() + populationHouseDO.getId() + ""; if(isOnly(houseUserKey,houseUserMap)){ populationHouseUserDO = new ComMngPopulationHouseUserDO(); populationHouseUserDO.setId(Snowflake.getId()); populationHouseUserDO.setHouseId(populationHouseDO.getId()); populationHouseUserDO.setPopulId(populationDO.getId()); // populationHouseUserDO.setRelation(vo.getRelation()); populationHouseUserDO.setRelationId(vo.getIsRent()); // populationHouseUserDO.setResidence(vo.getResidence()); houseUserList.add(populationHouseUserDO); } } log.info("开始查询实有人口房屋居住信息完成"); } }catch (Exception e){ log.info("出现错误，错误原因：" + e.getMessage()); } log.info("处理完成导入数据"); log.info("开始执行数据库导入"); if(!houseList.isEmpty()){ log.info("执行数据库导入房屋"); comMngPopulationHouseDAO.insertAll(houseList); log.info("执行数据库导入房屋完成"); } if(!saveList.isEmpty()){ log.info("执行数据库导入人口"); this.baseMapper.insertAll(saveList); log.info("执行数据库导入人口完成"); } if(!updateList.isEmpty()){ log.info("执行数据库更新人口"); this.baseMapper.updateAll(updateList); // this.updateBatchById(updateList); log.info("执行数据库更新人口完成"); } if(!houseUserList.isEmpty()){ log.info("执行数据库导入人口房屋关系"); comMngPopulationHouseUserService.saveBatch(houseUserList); log.info("执行数据库导入人口房屋关系完成"); } log.info("执行数据库导入完成"); if (!mistakes.isEmpty()) { log.info("返回错误数据"); return R.fail(mistakes); } return R.ok(mistakes); } @Override @Transactional(rollbackFor = Exception.class) public R listSaveDisabilityPopulation(List<ComMngPopulationDisabilityExcelVO> list, Long communityId) { //需要新增的房屋集合 List<ComMngPopulationHouseDO> houseList = new ArrayList<>(); //需要新增的人口集合 List<ComMngPopulationDO> saveList = new ArrayList<>(); //需要修改的人口集合 List<ComMngPopulationDO> updateList = new ArrayList<>(); //需要新增的人口与房屋关系集合 List<ComMngPopulationHouseUserDO> houseUserList = new ArrayList<>(); log.info("开始处理导入数据"); List<ComMngPopulationDisabilityMistakeExcelVO> mistakes = new ArrayList<>(); try { //查询社区信息 log.info("开始查询社区信息数据"); ComPopulationActVO populationActVO = comActDAO.getPopulationActById(communityId); if(populationActVO == null){ log.error("未查询到社区信息"); return R.fail("未查询到社区信息"); } log.info("开始查询社区信息数据完成"); //查询当前社区标签列表 List<String> labelList = new ArrayList<>(); List<ComMngUserTagDO> comMngUserTagDOS = comMngUserTagDAO.selectList(new QueryWrapper<ComMngUserTagDO>().eq("sys_flag", 1).or().eq("community_id", communityId)); if (!ObjectUtils.isEmpty(comMngUserTagDOS)) { labelList = comMngUserTagDOS.stream().map(comMngUserTagDO -> comMngUserTagDO.getTagName()).collect(Collectors.toList()); } StringBuilder areaPath = new StringBuilder(); areaPath.append(populationActVO.getProvinceName()).append(">").append(populationActVO.getCityName()).append(">").append(populationActVO.getDistrictName()).append(">"); //处理实有人口信息 Integer nub = 1; //查询所有人口数据放入HashMap中 List<ComMngPopulationDO> populationList = this.baseMapper.selectList(null); HashMap<String,Object> populationMap = new HashMap<>(); populationList.forEach(population -> { String key = population.getCardNo(); populationMap.put(key,population); }); //查询所有房屋信息放入到HashMap中 List<ComMngPopulationHouseDO> houseLists = comMngPopulationHouseDAO.selectList(null); HashMap<String,Object> houseMap = new HashMap<>(); houseLists.forEach(house -> { String key = house.getCommunityId() + house.getVillageId() + house.getFloor() + house.getUnitNo() + house.getHouseNo(); houseMap.put(key,house); }); List<ComMngVillageDO> villageList = comActVillageDAO.selectList(null); HashMap<String,Object> villageMap = new HashMap<>(); villageList.forEach(village -> { String key = village.getCommunityId() + village.getAlley() + village.getHouseNum(); villageMap.put(key,village); }); List<ComMngPopulationHouseUserDO> houseUserLists = comMngPopulationHouseUserDAO.selectList(null); HashMap<String,Object> houseUserMap = new HashMap<>(); houseUserLists.forEach(houseUser -> { String key = houseUser.getPopulId() + houseUser.getHouseId() + ""; houseUserMap.put(key,houseUser); }); for (ComMngPopulationDisabilityExcelVO vo : list) { if (vo.getDoorNo().contains("号")) { vo.setDoorNo(vo.getDoorNo().replace("号","")); } if(vo.getFloor().contains("栋")){ vo.setFloor(vo.getFloor().replace("栋","")); } if(vo.getUnitNo().contains("单元")){ vo.setUnitNo(vo.getUnitNo().replace("单元","")); } if(vo.getHouseNo().contains("号")){ vo.setHouseNo(vo.getHouseNo().replace("号","")); } log.info("开始查询小区街路巷是否存在"); //查询小区街路巷是否存在 ComMngVillageDO comMngVillageDO = null; String villageKey = communityId + vo.getRoad() + vo.getDoorNo(); if(!isOnly(villageKey,villageMap)){ comMngVillageDO = (ComMngVillageDO)villageMap.get(villageKey); }else{ ComMngPopulationDisabilityMistakeExcelVO mistake = new ComMngPopulationDisabilityMistakeExcelVO(); BeanUtils.copyProperties(vo,mistake); setDisabilityMistake(mistake, vo); mistake.setMistake("街路巷或小区号不存在，请先在“小区管理”中添加该小区:街路巷："+ vo.getRoad() +"，小区号:" + vo.getDoorNo()); mistakes.add(mistake); log.info("未查询到街路巷："+ vo.getRoad() +"，小区号:" + vo.getDoorNo()); continue; } StringBuilder address = new StringBuilder(); address.append(populationActVO.getProvinceName()).append(populationActVO.getCityName()).append(populationActVO.getDistrictName()) .append(populationActVO.getStreetName()).append(comMngVillageDO.getAlley()).append(vo.getDoorNo()).append("号").append(vo.getFloor()).append("栋") .append(vo.getUnitNo()).append("单元").append(vo.getHouseNo()).append("号"); vo.setAddress(address.toString()); log.info("开始查询小区街路巷是否存在完成"); log.info("开始查询房屋是否存在"); //先判断房屋是否存在 ComMngPopulationHouseDO populationHouseDO = null; String houseKey = communityId + comMngVillageDO.getVillageId() + vo.getFloor() + vo.getUnitNo() + vo.getHouseNo(); if(isOnly(houseKey,houseMap)){ if(!houseList.isEmpty()){ for (ComMngPopulationHouseDO house:houseList) { if(house.getVillageId().equals(comMngVillageDO.getVillageId()) && house.getCommunityId().equals(communityId) && house.getFloor().equals(vo.getFloor()) && house.getUnitNo().equals(vo.getUnitNo()) && house.getHouseNo().equals(vo.getHouseNo())){ populationHouseDO = house; break; } } } if(populationHouseDO == null){ //房屋信息不存在建立房屋信息 populationHouseDO = saveDisabilityPopulationHouse(vo, comMngVillageDO, communityId, areaPath, populationActVO.getName()); houseList.add(populationHouseDO); } }else{ populationHouseDO = (ComMngPopulationHouseDO)houseMap.get(houseKey); } vo.setHouseId(populationHouseDO.getId()); log.info("开始查询房屋是否存在完成"); if (StringUtils.isEmpty(vo.getName()) && StringUtils.isEmpty(vo.getCardNo())) { //空户处理完房屋信息，直接返回 continue; } String cardNoAES = AESUtil.encrypt128(vo.getCardNo(), aesKey); //判断实有人口是否已存在 log.info("开始查询实有人口是否已存在"); ComMngPopulationDO populationDO = null; String populationKey = vo.getCardNo(); if(!isOnly(populationKey,populationMap)){ //存在实有人口信息，则更新 populationDO = (ComMngPopulationDO)populationMap.get(populationKey); ComMngPopulationDO updatePopulationDO = updateDisabilityPopulationDO(vo,populationDO,labelList); updateList.add(updatePopulationDO); }else{ //不存在实有人口,则新增 populationDO = saveDisabilityPopulationDO(vo, populationActVO, comMngVillageDO,labelList); saveList.add(populationDO); } log.info("开始查询实有人口是否已存在完成"); log.info("开始查询实有人口房屋居住信息"); //处理实有人口房屋居住信息 if (populationDO != null) { ComMngPopulationHouseUserDO populationHouseUserDO = null; String houseUserKey = populationDO.getId() + populationHouseDO.getId() + ""; if(isOnly(houseUserKey,houseUserMap)){ populationHouseUserDO = new ComMngPopulationHouseUserDO(); populationHouseUserDO.setId(Snowflake.getId()); populationHouseUserDO.setHouseId(populationHouseDO.getId()); populationHouseUserDO.setPopulId(populationDO.getId()); // populationHouseUserDO.setRelation(vo.getRelation()); populationHouseUserDO.setRelationId(vo.getIsRent()); // populationHouseUserDO.setResidence(vo.getResidence()); houseUserList.add(populationHouseUserDO); } } log.info("开始查询实有人口房屋居住信息完成"); } }catch (Exception e){ log.info("出现错误，错误原因：" + e.getMessage()); } log.info("处理完成导入数据"); log.info("开始执行数据库导入"); if(!houseList.isEmpty()){ log.info("执行数据库导入房屋"); comMngPopulationHouseDAO.insertAll(houseList); log.info("执行数据库导入房屋完成"); } if(!saveList.isEmpty()){ log.info("执行数据库导入人口"); this.baseMapper.insertAll(saveList); log.info("执行数据库导入人口完成"); } if(!updateList.isEmpty()){ log.info("执行数据库更新人口"); this.baseMapper.updateAll(updateList); // this.updateBatchById(updateList); log.info("执行数据库更新人口完成"); } if(!houseUserList.isEmpty()){ log.info("执行数据库导入人口房屋关系"); comMngPopulationHouseUserService.saveBatch(houseUserList); log.info("执行数据库导入人口房屋关系完成"); } log.info("执行数据库导入完成"); if (!mistakes.isEmpty()) { log.info("返回错误数据"); return R.fail(mistakes); } return R.ok(mistakes); } private ComMngPopulationDO updatePopulationDO(ComMngPopulationServeExcelVO vo, ComMngPopulationDO populationDO,List<String> labelList){ BeanUtils.copyProperties(vo, populationDO); @@ -2130,6 +2536,47 @@ } private ComMngPopulationDO updateSentencePopulationDO(ComMngPopulationSentenceExcelVO vo, ComMngPopulationDO populationDO,List<String> labelList){ BeanUtils.copyProperties(vo, populationDO); List<String> userTag = vo.getUserTagStr().stream().map(userTagStr -> userTagStr.split("\\(")[0]).collect(Collectors.toList()); //如果导入数据标签中有当前社区标签列表中不包含的标签，则删除 Iterator<String> iterator = userTag.iterator(); while (iterator.hasNext()) { String s = iterator.next(); if (!labelList.contains(s)) iterator.remove(); } String cardNoAES = populationDO.getCardNo(); try { cardNoAES = AESUtil.encrypt128(vo.getCardNo(), aesKey); }catch (Exception e){ log.error("身份证加密失败"); } populationDO.setLabel(Joiner.on(",").join(userTag)); populationDO.setCardNo(cardNoAES); return populationDO; } private ComMngPopulationDO updateVeteransPopulationDO(ComMngPopulationVeteransExcelVO vo, ComMngPopulationDO populationDO,List<String> labelList){ BeanUtils.copyProperties(vo, populationDO); List<String> userTag = vo.getUserTagStr().stream().map(userTagStr -> userTagStr.split("\\(")[0]).collect(Collectors.toList()); //如果导入数据标签中有当前社区标签列表中不包含的标签，则删除 Iterator<String> iterator = userTag.iterator(); while (iterator.hasNext()) { String s = iterator.next(); if (!labelList.contains(s)) iterator.remove(); } String cardNoAES = populationDO.getCardNo(); try { cardNoAES = AESUtil.encrypt128(vo.getCardNo(), aesKey); }catch (Exception e){ log.error("身份证加密失败"); } populationDO.setLabel(Joiner.on(",").join(userTag)); populationDO.setCardNo(cardNoAES); return populationDO; } private ComMngPopulationDO updateDisabilityPopulationDO(ComMngPopulationDisabilityExcelVO vo, ComMngPopulationDO populationDO,List<String> labelList){ BeanUtils.copyProperties(vo, populationDO); List<String> userTag = vo.getUserTagStr().stream().map(userTagStr -> userTagStr.split("\\(")[0]).collect(Collectors.toList()); //如果导入数据标签中有当前社区标签列表中不包含的标签，则删除 @@ -2383,6 +2830,70 @@ } private ComMngPopulationDO saveSentencePopulationDO(ComMngPopulationSentenceExcelVO vo, ComPopulationActVO comActDO, ComMngVillageDO comMngVillageDO,List<String> labelList) { ComMngPopulationDO populationDO = new ComMngPopulationDO(); BeanUtils.copyProperties(vo, populationDO); populationDO.setId(Snowflake.getId()); List<String> userTag = vo.getUserTagStr().stream().map(userTagStr -> userTagStr.split("\\(")[0]).collect(Collectors.toList()); //如果导入数据标签中有当前社区标签列表中不包含的标签，则删除 Iterator<String> iterator = userTag.iterator(); while (iterator.hasNext()) { String s = iterator.next(); if (!labelList.contains(s)) iterator.remove(); } populationDO.setVillageId(comMngVillageDO.getVillageId()); populationDO.setActId(comActDO.getCommunityId()); populationDO.setStreetId(comActDO.getStreetId()); populationDO.setLabel(Joiner.on(",").join(userTag)); populationDO.setVillageName(comMngVillageDO.getGroupAt()); populationDO.setCardNoStr(vo.getCardNo()); populationDO.setUpdateAt(new Date()); //新增的时候默认绑定房屋id // if ((vo.getIsRent() != null && vo.getIsRent().equals(PopulHouseUseEnum.SELF.getCode())) || // (vo.getIsResidence() != null && vo.getIsResidence().intValue() == 1)) { // populationDO.setHouseId(vo.getHouseId()); // }else{ // populationDO.setHouseId(null); // } // this.baseMapper.insert(populationDO); return populationDO; } private ComMngPopulationDO saveVeteransPopulationDO(ComMngPopulationVeteransExcelVO vo, ComPopulationActVO comActDO, ComMngVillageDO comMngVillageDO,List<String> labelList) { ComMngPopulationDO populationDO = new ComMngPopulationDO(); BeanUtils.copyProperties(vo, populationDO); populationDO.setId(Snowflake.getId()); List<String> userTag = vo.getUserTagStr().stream().map(userTagStr -> userTagStr.split("\\(")[0]).collect(Collectors.toList()); //如果导入数据标签中有当前社区标签列表中不包含的标签，则删除 Iterator<String> iterator = userTag.iterator(); while (iterator.hasNext()) { String s = iterator.next(); if (!labelList.contains(s)) iterator.remove(); } populationDO.setVillageId(comMngVillageDO.getVillageId()); populationDO.setActId(comActDO.getCommunityId()); populationDO.setStreetId(comActDO.getStreetId()); populationDO.setLabel(Joiner.on(",").join(userTag)); populationDO.setVillageName(comMngVillageDO.getGroupAt()); populationDO.setCardNoStr(vo.getCardNo()); populationDO.setUpdateAt(new Date()); //新增的时候默认绑定房屋id // if ((vo.getIsRent() != null && vo.getIsRent().equals(PopulHouseUseEnum.SELF.getCode())) || // (vo.getIsResidence() != null && vo.getIsResidence().intValue() == 1)) { // populationDO.setHouseId(vo.getHouseId()); // }else{ // populationDO.setHouseId(null); // } // this.baseMapper.insert(populationDO); return populationDO; } private ComMngPopulationDO saveDisabilityPopulationDO(ComMngPopulationDisabilityExcelVO vo, ComPopulationActVO comActDO, ComMngVillageDO comMngVillageDO,List<String> labelList) { ComMngPopulationDO populationDO = new ComMngPopulationDO(); BeanUtils.copyProperties(vo, populationDO); populationDO.setId(Snowflake.getId()); @@ -2702,6 +3213,88 @@ } private ComMngPopulationHouseDO saveSentencePopulationHouse(ComMngPopulationSentenceExcelVO vo, ComMngVillageDO comMngVillageDO, Long communityId,StringBuilder areaPath, String actName) { //查询该房屋未建立，执行建立房屋信息 ComMngPopulationHouseDO populationHouseDO = new ComMngPopulationHouseDO(); populationHouseDO.setId(Snowflake.getId()); populationHouseDO.setStreetId(comMngVillageDO.getStreetId()); populationHouseDO.setVillageId(comMngVillageDO.getVillageId()); populationHouseDO.setAlley(vo.getRoad()); populationHouseDO.setHouseNum(vo.getDoorNo()); populationHouseDO.setStatus(vo.getIsRent()); populationHouseDO.setCommunityId(communityId); populationHouseDO.setFloor(vo.getFloor()); populationHouseDO.setUnitNo(vo.getUnitNo()); populationHouseDO.setHouseNo(vo.getHouseNo()); populationHouseDO.setCode(vo.getHouseNo()); populationHouseDO.setAddress(vo.getAddress()); populationHouseDO.setUpdateAt(new Date()); // populationHouseDO.setConstructPurpose(vo.getHousePurpose()); StringBuilder housePath = new StringBuilder(); housePath.append(populationHouseDO.getAlley()).append(">").append(actName).append(">").append(comMngVillageDO.getName()).append(">").append(vo.getAddress()); populationHouseDO.setPath(areaPath.toString() + housePath.toString()); // try { // populationHouseDO.setConstructArea(new BigDecimal(vo.getBuildArea())); // } catch (Exception e) { // } if (StringUtils.isEmpty(vo.getName()) && StringUtils.isEmpty(vo.getCardNo())) { populationHouseDO.setIsEmpty(PopulIsOksEnum.YES.getCode()); } if (vo.getHouseStatus() != null) { populationHouseDO.setStatus(vo.getHouseStatus()); } if (vo.getHousePurpose() != null) { populationHouseDO.setPurpose(vo.getHousePurpose()); } if (vo.getControlStatus() != null) { populationHouseDO.setControlStatus(vo.getControlStatus()); } // comMngPopulationHouseDAO.insert(populationHouseDO); return populationHouseDO; } private ComMngPopulationHouseDO saveVeteransPopulationHouse(ComMngPopulationVeteransExcelVO vo, ComMngVillageDO comMngVillageDO, Long communityId,StringBuilder areaPath, String actName) { //查询该房屋未建立，执行建立房屋信息 ComMngPopulationHouseDO populationHouseDO = new ComMngPopulationHouseDO(); populationHouseDO.setId(Snowflake.getId()); populationHouseDO.setStreetId(comMngVillageDO.getStreetId()); populationHouseDO.setVillageId(comMngVillageDO.getVillageId()); populationHouseDO.setAlley(vo.getRoad()); populationHouseDO.setHouseNum(vo.getDoorNo()); populationHouseDO.setStatus(vo.getIsRent()); populationHouseDO.setCommunityId(communityId); populationHouseDO.setFloor(vo.getFloor()); populationHouseDO.setUnitNo(vo.getUnitNo()); populationHouseDO.setHouseNo(vo.getHouseNo()); populationHouseDO.setCode(vo.getHouseNo()); populationHouseDO.setAddress(vo.getAddress()); populationHouseDO.setUpdateAt(new Date()); // populationHouseDO.setConstructPurpose(vo.getHousePurpose()); StringBuilder housePath = new StringBuilder(); housePath.append(populationHouseDO.getAlley()).append(">").append(actName).append(">").append(comMngVillageDO.getName()).append(">").append(vo.getAddress()); populationHouseDO.setPath(areaPath.toString() + housePath.toString()); // try { // populationHouseDO.setConstructArea(new BigDecimal(vo.getBuildArea())); // } catch (Exception e) { // } if (StringUtils.isEmpty(vo.getName()) && StringUtils.isEmpty(vo.getCardNo())) { populationHouseDO.setIsEmpty(PopulIsOksEnum.YES.getCode()); } if (vo.getHouseStatus() != null) { populationHouseDO.setStatus(vo.getHouseStatus()); } if (vo.getHousePurpose() != null) { populationHouseDO.setPurpose(vo.getHousePurpose()); } if (vo.getControlStatus() != null) { populationHouseDO.setControlStatus(vo.getControlStatus()); } // comMngPopulationHouseDAO.insert(populationHouseDO); return populationHouseDO; } private ComMngPopulationHouseDO saveDisabilityPopulationHouse(ComMngPopulationDisabilityExcelVO vo, ComMngVillageDO comMngVillageDO, Long communityId,StringBuilder areaPath, String actName) { //查询该房屋未建立，执行建立房屋信息 ComMngPopulationHouseDO populationHouseDO = new ComMngPopulationHouseDO(); populationHouseDO.setId(Snowflake.getId()); @@ -4190,6 +4783,44 @@ // mvo.setDeath(PopulOutOrLocalEnum.getCnDescByName(vo.getDeath())); } private void setVeteransMistake(ComMngPopulationVeteransMistakeExcelVO mvo, ComMngPopulationVeteransExcelVO vo){ mvo.setRegiterNature(PopulRegiterNatureEnum.getCnDescByName(vo.getRegiterNature())); mvo.setPoliticalOutlook(PopulPoliticalOutlookEnum.getCnDescByName(vo.getPoliticalOutlook())); mvo.setIsRent(PopulHouseUseEnum.getCnDescByName(vo.getIsRent())); // mvo.setPatientRelation(PopulRelationEnum.getCnDescByName(vo.getPatientRelation())); mvo.setHouseStatus(PopulHouseStatusEnum.getCnDescByName(vo.getHouseStatus())); mvo.setHousePurpose(PopulHousePurposeEnum.getCnDescByName(vo.getHousePurpose())); mvo.setControlStatus(PopulHouseControlStatusEnum.getCnDescByName(vo.getControlStatus())); // mvo.setCultureLevel(PopulCultureLevelEnum.getCnDescByName(vo.getCultureLevel())); mvo.setMarriage(PopulMarriageEnum.getCnDescByName(vo.getMarriage())); mvo.setOutOrLocal(PopulOutOrLocalEnum.getCnDescByName(vo.getOutOrLocal())); mvo.setIsVeteransTraining(PopulHouseUseEnum.getCnDescByName(vo.getIsVeteransTraining())); mvo.setIsBusiness(PopulHouseUseEnum.getCnDescByName(vo.getIsBusiness())); // mvo.setIsRehab(PopulHouseUseEnum.getCnDescByName(vo.getIsRehab())); // mvo.setResidence(PopulRelationEnum.getCnDescByName(vo.getResidence())); // mvo.setPersonType(PopulPersonTypeEnum.getCnDescByName(vo.getPersonType())); // mvo.setDeath(PopulOutOrLocalEnum.getCnDescByName(vo.getDeath())); } private void setDisabilityMistake(ComMngPopulationDisabilityMistakeExcelVO mvo, ComMngPopulationDisabilityExcelVO vo){ // mvo.setRegiterNature(PopulRegiterNatureEnum.getCnDescByName(vo.getRegiterNature())); // mvo.setPoliticalOutlook(PopulPoliticalOutlookEnum.getCnDescByName(vo.getPoliticalOutlook())); mvo.setIsRent(PopulHouseUseEnum.getCnDescByName(vo.getIsRent())); // mvo.setPatientRelation(PopulRelationEnum.getCnDescByName(vo.getPatientRelation())); mvo.setHouseStatus(PopulHouseStatusEnum.getCnDescByName(vo.getHouseStatus())); mvo.setHousePurpose(PopulHousePurposeEnum.getCnDescByName(vo.getHousePurpose())); mvo.setControlStatus(PopulHouseControlStatusEnum.getCnDescByName(vo.getControlStatus())); mvo.setCultureLevel(PopulCultureLevelEnum.getCnDescByName(vo.getCultureLevel())); // mvo.setMarriage(PopulMarriageEnum.getCnDescByName(vo.getMarriage())); mvo.setOutOrLocal(PopulOutOrLocalEnum.getCnDescByName(vo.getOutOrLocal())); // mvo.setIsVeteransTraining(PopulHouseUseEnum.getCnDescByName(vo.getIsVeteransTraining())); // mvo.setIsBusiness(PopulHouseUseEnum.getCnDescByName(vo.getIsBusiness())); // mvo.setIsRehab(PopulHouseUseEnum.getCnDescByName(vo.getIsRehab())); // mvo.setResidence(PopulRelationEnum.getCnDescByName(vo.getResidence())); // mvo.setPersonType(PopulPersonTypeEnum.getCnDescByName(vo.getPersonType())); // mvo.setDeath(PopulOutOrLocalEnum.getCnDescByName(vo.getDeath())); } /** * 判重方法 * @param key 主键 springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/service/impl/ComSwRotaServiceImpl.java
@@ -285,9 +285,9 @@ @Override public R listSaveSwRotaExcelVO(List<ComSwRotaExcelVO> list, Long communityId) { String[] parsePatterns = new String[]{"yyyy-MM-dd", "yyyy-M-dd", "yyyy-MM-d", "yyyy-M-d", "yyyy/MM/dd", "yyyy/M/dd", "yyyy/MM/d", "yyyy/M/d"}; List<ComSwRotaDO> comSwRotaDOS = new ArrayList<>(); for (ComSwRotaExcelVO comSwRotaExcelVO : list){ String[] parsePatterns = new String[]{"yyyy-MM-dd", "yyyy-M-dd", "yyyy-MM-d", "yyyy-M-d", "yyyy/MM/dd", "yyyy/M/dd", "yyyy/MM/d", "yyyy/M/d"}; Date date = DateUtil.parse(comSwRotaExcelVO.getRotaDate(),parsePatterns); Integer count = comSwRotaDAO.selectCount(new QueryWrapper<ComSwRotaDO>().lambda().eq(ComSwRotaDO::getRotaDate, date)); if (count > 0) {
