springcloud_k8s_panzhihuazhihuishequ/common/src/main/java/com/panzhihua/common/listen/ComMngPopulationServeExcelListen.java
@@ -3,59 +3,113 @@ import com.alibaba.excel.context.AnalysisContext; import com.alibaba.excel.event.AnalysisEventListener; import com.alibaba.fastjson.JSONObject; import com.google.common.collect.Lists; import com.panzhihua.common.exceptions.ServiceException; import com.panzhihua.common.model.vos.R; import com.panzhihua.common.model.vos.community.ComMngPopulationServeExcelVO; import com.panzhihua.common.model.vos.community.ComMngVillageServeExcelVO; import com.panzhihua.common.service.community.CommunityService; import lombok.extern.slf4j.Slf4j; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import java.util.ArrayList; import java.util.List; import java.util.Map; import static com.sun.xml.internal.ws.spi.db.BindingContextFactory.LOGGER; /** * @description: 实有人口导入监听 * @author: llming */ @Slf4j public class ComMngPopulationServeExcelListen extends AnalysisEventListener<ComMngPopulationServeExcelVO> { public class ComMngPopulationServeExcelListen extends AnalysisEventListener<Map<Integer, String>> { private CommunityService communityService; private Long communityId; private static int headSize = 0; private Map<Integer, String> headData; public ComMngPopulationServeExcelListen(CommunityService communityService, Long communityId) { this.communityService = communityService; this.communityId = communityId; } /** * 每隔5条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收 */ private static final int BATCH_COUNT = 100; private List<ComMngPopulationServeExcelVO> list = new ArrayList<>(); List<Map<Integer, String>> list = new ArrayList<Map<Integer, String>>(); @Override public void invoke(ComMngPopulationServeExcelVO comMngPopulationServeExcelVO, AnalysisContext analysisContext) { list.add(comMngPopulationServeExcelVO); // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM public void invoke(Map<Integer, String> data, AnalysisContext context) { list.add(data); if (list.size() >= BATCH_COUNT) { log.info("excel导入数据【{}】", JSONObject.toJSONString(list)); R r = communityService.listSavePopulationServeExcelVO(list, this.communityId); if (!R.isOk(r)) { throw new ServiceException(r.getMsg()); } //清空list saveData(); list.clear(); } } /** * 这里会一行行的返回头 * * @param headMap * @param context */ @Override public void doAfterAllAnalysed(AnalysisContext analysisContext) { log.info("excel导入数据【{}】", JSONObject.toJSONString(list)); //确保最后遗留的数据保存在数据库中 R r = this.communityService.listSavePopulationServeExcelVO(list, this.communityId); if (!R.isOk(r)) { throw new ServiceException(r.getMsg()); } public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) { headSize = headMap.size(); headData = headMap; } @Override public void doAfterAllAnalysed(AnalysisContext context) { saveData(); LOGGER.info("所有数据解析完成！"); } /** * 不是固定的列只能手动处理 */ private void saveData() { int index = 2; try { ArrayList<ComMngPopulationServeExcelVO> voList = Lists.newArrayList(); for (Map<Integer, String> oneData : list) { ComMngPopulationServeExcelVO vo = new ComMngPopulationServeExcelVO(); vo.setName(oneData.get(0)); vo.setSex(Integer.valueOf(oneData.get(1))); vo.setAge(Integer.valueOf(oneData.get(2))); vo.setIsRent(Integer.valueOf(oneData.get(3))); vo.setRoad(oneData.get(4)); vo.setDoorNo(Integer.valueOf(oneData.get(5))); vo.setFloor(Integer.valueOf(oneData.get(6))); vo.setUnitNo(Integer.valueOf(oneData.get(7))); vo.setHouseNo(Integer.valueOf(oneData.get(8))); vo.setNation(oneData.get(9)); vo.setPoliticalOutlook(oneData.get(10)); vo.setCardNo(oneData.get(11)); vo.setPhone(oneData.get(12)); vo.setNativePlace(oneData.get(13)); vo.setWorkCompany(oneData.get(14)); for (int i = 15; i < headSize; i++) { if (oneData.get(i).equals("是")) { vo.getUserTagStr().add(headData.get(i)); } } voList.add(vo); index++; } R r = communityService.listSavePopulationServeExcelVO(voList, communityId); if (!R.isOk(r)) { throw new ServiceException(r.getMsg()); } } catch (NumberFormatException e) { throw new ServiceException("500", "填写数据类型错误:第" + index + "行" + e.getMessage()); } } } springcloud_k8s_panzhihuazhihuishequ/common/src/main/java/com/panzhihua/common/model/vos/community/ComMngPopulationServeExcelVO.java
@@ -1,9 +1,11 @@ package com.panzhihua.common.model.vos.community; import com.alibaba.excel.annotation.ExcelProperty; import com.google.common.collect.Lists; import lombok.Data; import java.io.Serializable; import java.util.List; /** * @description: 批量导入实有人口 @@ -12,48 +14,52 @@ @Data public class ComMngPopulationServeExcelVO implements Serializable { @ExcelProperty(value = "家庭成员（姓名）" ,index = 0) @ExcelProperty(value = "家庭成员（姓名）", index = 0) private String name; @ExcelProperty(value = "性别（1.男 2.女 3.未知）" ,index = 1) @ExcelProperty(value = "性别（1.男 2.女 3.未知）", index = 1) private Integer sex; @ExcelProperty(value = "年龄" ,index = 2) @ExcelProperty(value = "年龄", index = 2) private Integer age; @ExcelProperty(value = "是否租住" ,index = 3) private Integer adsf; @ExcelProperty(value = "是否租住", index = 3) private Integer isRent; @ExcelProperty(value = "街路巷" ,index = 4) @ExcelProperty(value = "街路巷", index = 4) private String road; @ExcelProperty(value = "门牌号" ,index = 5) @ExcelProperty(value = "门牌号", index = 5) private Integer doorNo; @ExcelProperty(value = "楼排号" ,index = 6) private String floor; @ExcelProperty(value = "楼排号", index = 6) private Integer floor; @ExcelProperty(value = "单元号" ,index = 7) @ExcelProperty(value = "单元号", index = 7) private Integer unitNo; @ExcelProperty(value = "户室（房间号）" ,index = 8) @ExcelProperty(value = "户室（房间号）", index = 8) private Integer houseNo; @ExcelProperty(value = "民族" ,index = 9) @ExcelProperty(value = "民族", index = 9) private String nation; @ExcelProperty(value = "政治面貌(1.中共党员2.中共预备党员3.共青团员4.民革党员5.民盟盟员6.民建会员7.8.农工党党员9.致公党党员10.九三学社社员11.台盟盟员12.无党派人士13.群众)" ,index = 10) private Integer politicalOutlook; @ExcelProperty(value = "政治面貌(1.中共党员2.中共预备党员3.共青团员4.民革党员5.民盟盟员6.民建会员7.8.农工党党员9.致公党党员10.九三学社社员11.台盟盟员12.无党派人士13.群众)", index = 10) private String politicalOutlook; @ExcelProperty(value = "身份证号码" ,index = 11) @ExcelProperty(value = "身份证号码", index = 11) private String cardNo; @ExcelProperty(value = "联系方式" ,index = 12) @ExcelProperty(value = "联系方式", index = 12) private String phone; @ExcelProperty(value = "籍贯" ,index = 13) @ExcelProperty(value = "籍贯", index = 13) private String nativePlace; @ExcelProperty(value = "工作单位" ,index = 14) @ExcelProperty(value = "工作单位", index = 14) private String workCompany; /** * 扩展字段用逗号隔开 */ private List<String> userTagStr = Lists.newArrayList(); } springcloud_k8s_panzhihuazhihuishequ/common/src/main/java/com/panzhihua/common/service/community/CommunityService.java
@@ -1576,6 +1576,6 @@ * @param communityId * @return */ @PostMapping("/population/import") R listSavePopulationServeExcelVO(List<ComMngPopulationServeExcelVO> list,@RequestParam(value = "communityId") Long communityId); @PostMapping("/common/data/population/import") R listSavePopulationServeExcelVO(@RequestBody List<ComMngPopulationServeExcelVO> list,@RequestParam(value = "communityId") Long communityId); } springcloud_k8s_panzhihuazhihuishequ/community_backstage/src/main/java/com/panzhihua/community_backstage/api/CommonDataApi.java
@@ -19,6 +19,7 @@ import com.panzhihua.common.model.vos.user.InputUserInfoVO; import com.panzhihua.common.service.community.CommunityService; import com.panzhihua.common.service.user.UserService; import com.panzhihua.common.utlis.FileUtil; import com.panzhihua.common.utlis.SFTPUtil; import com.panzhihua.common.validated.AddGroup; import com.panzhihua.community_backstage.easyexcel.UploadEexcelUserDTOListener; @@ -68,7 +69,7 @@ private String companyUrl = "http://panzhihua.nhys.cdnhxx.com/web/%e5%ae%9e%e6%9c%89%e5%8d%95%e4%bd%8d%e5%af%bc%e5%85%a5%e6%a8%a1%e6%9d%bf.xlsx"; @Value("${excel.assetsUrl}") private String assetsUrl = "http://panzhihua.nhys.cdnhxx.com/web/%e5%ae%9e%e6%9c%89%e5%8d%95%e4%bd%8d%e5%af%bc%e5%85%a5%e6%a8%a1%e6%9d%bf.xlsx"; private String excelPopulationUrl="http://panzhihua.nhys.cdnhxx.com/web/实有人口导入.xlsx"; private String excelPopulationUrl = "http://panzhihua.nhys.cdnhxx.com/web/实有人口导入.xlsx"; // FTP 登录用户名 @Value("${ftp.username}") @@ -693,7 +694,7 @@ inputStream = file.getInputStream(); LoginUserInfoVO loginUserInfo = this.getLoginUserInfo(); ComMngPopulationServeExcelListen comMngPopulationServeExcelListen = new ComMngPopulationServeExcelListen(communityService, loginUserInfo.getCommunityId()); EasyExcel.read(inputStream, ComMngPopulationServeExcelVO.class, comMngPopulationServeExcelListen).sheet().doRead(); EasyExcel.read(inputStream, null, comMngPopulationServeExcelListen).sheet().doRead(); } catch (IOException e) { log.error("导入模板失败【{}】", e.getMessage()); e.printStackTrace(); @@ -701,5 +702,5 @@ return R.ok(); } } springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/model/dos/ComMngPopulationDO.java
@@ -22,15 +22,15 @@ /** * 街道id */ private Integer streetId; private Long streetId; /** * 社区id */ private Integer actId; private Long actId; /** * 小区id（实有房屋id） */ private Integer villageId; private Long villageId; /** * 家庭成员（姓名） */ @@ -117,5 +117,13 @@ @TableField(exist = false) private String villageName; /** * 性别（1.男 2.女 3.未知） */ public interface sex{ int nan = 1; int nv = 2; int no = 3; } } springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/service/impl/ComMngPopulationServiceImpl.java
@@ -1,40 +1,43 @@ package com.panzhihua.service_community.service.impl; import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper; import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper; import com.baomidou.mybatisplus.core.metadata.IPage; import com.baomidou.mybatisplus.extension.plugins.pagination.Page; import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl; import com.google.common.base.Joiner; import com.google.common.collect.Lists; import com.panzhihua.common.exceptions.ServiceException; import com.panzhihua.common.model.dtos.community.ComMngPopulationDTO; import com.panzhihua.common.model.dtos.community.PageComActDTO; import com.panzhihua.common.model.vos.R; import com.panzhihua.common.model.vos.community.*; import com.panzhihua.common.model.vos.user.ComMngFamilyInfoVO; import com.panzhihua.service_community.dao.ComActActivityDAO; import com.panzhihua.service_community.dao.ComActDAO; import com.panzhihua.service_community.dao.ComActVillageDAO; import com.panzhihua.service_community.dao.ComMngPopulationDAO; import com.panzhihua.service_community.model.dos.ComActActivityDO; import com.panzhihua.service_community.model.dos.ComActDO; import com.panzhihua.service_community.model.dos.ComMngPopulationDO; import com.panzhihua.service_community.model.dos.ComMngVillageDO; import com.panzhihua.service_community.service.ComMngPopulationService; import org.springframework.beans.BeanUtils; import org.springframework.security.crypto.bcrypt.BCryptPasswordEncoder; import org.springframework.stereotype.Service; import org.springframework.util.ObjectUtils; import javax.annotation.Resource; import java.util.ArrayList; import java.util.Date; import java.util.List; /** * 实有人口Service实现类 */ @Service public class ComMngPopulationServiceImpl implements ComMngPopulationService { public class ComMngPopulationServiceImpl extends ServiceImpl<ComMngPopulationDAO, ComMngPopulationDO> implements ComMngPopulationService { @Resource private ComMngPopulationDAO populationDAO; @Resource private ComActDAO comActDAO; @Resource private ComActVillageDAO comActVillageDAO; /** @@ -175,64 +178,89 @@ @Override public R listSavePopulation(List<ComMngPopulationServeExcelVO> list, Long communityId) { /* //思路：实有房屋（小区）当前是存在重复人口,打印已经存在的重复数据 List<ComMngPopulationDO> comMngPopulationDOS = populationDAO.selectList(new QueryWrapper<ComMngPopulationDO>().lambda().eq(ComMngPopulationDO::getActId, communityId)); if (list.size() == 0) { return R.fail("数据为空！"); } judgeEmpty(list); ComActDO comActDO = comActDAO.selectById(communityId); ArrayList<ComMngVillageDO> comMngVillageDOS = Lists.newArrayList(); list.forEach(vo -> { ComMngVillageDO comMngVillageDO = new ComMngVillageDO(); BeanUtils.copyProperties(vo, comMngVillageDO); comMngVillageDO.setCommunityId(comActDO.getCommunityId()); comMngVillageDO.setStreetId(comActDO.getStreetId()); comMngVillageDOS.add(comMngVillageDO); }); this.saveBatch(comMngVillageDOS); return R.ok("共计导入实有房屋数量：" + comMngVillageDOS.size());*/ return null; } /** * excel数据校验 * * @param list */ private void judgeEmpty(List<ComMngPopulationServeExcelVO> list) { /*int index = 2; List<ComMngPopulationDO> comMngPopulationDOS = populationDAO.selectList(new QueryWrapper<ComMngPopulationDO>().lambda().eq(ComMngPopulationDO::getActId, communityId)); //查询该社区所有(实有房屋)小区 List<ComMngVillageDO> villageDOList = comActVillageDAO.selectList(new QueryWrapper<ComMngVillageDO>().lambda().eq(ComMngVillageDO::getCommunityId, communityId)); int index = 2; for (ComMngPopulationServeExcelVO vo : list) { ComMngPopulationServeExcelVO comMngPopulationServeExcelVO = new ComMngPopulationServeExcelVO(); comMngPopulationServeExcelVO.setName(""); comMngPopulationServeExcelVO.setSex(0); comMngPopulationServeExcelVO.setAge(0); comMngPopulationServeExcelVO.setAdsf(0); comMngPopulationServeExcelVO.setRoad(""); comMngPopulationServeExcelVO.setDoorNo(0); comMngPopulationServeExcelVO.setFloor(""); comMngPopulationServeExcelVO.setUnitNo(0); comMngPopulationServeExcelVO.setHouseNo(0); comMngPopulationServeExcelVO.setNation(""); comMngPopulationServeExcelVO.setPoliticalOutlook(0); comMngPopulationServeExcelVO.setCardNo(""); comMngPopulationServeExcelVO.setPhone(""); comMngPopulationServeExcelVO.setNativePlace(""); comMngPopulationServeExcelVO.setWorkCompany(""); if (vo.get() == null) { return R.fail("门牌号第" + index + "行为空!"); if (vo.getName() == null) { return R.fail("家庭成员（姓名）第" + index + "行为空!"); } if (vo.getAlley() == null) { return R.fail("街路巷第" + index + "行为空!"); if (vo.getSex() == null) { return R.fail("性别第" + index + "行为空!"); } if (vo.getAge() == null) { return R.fail("年龄" + index + "行为空!"); } if (vo.getIsRent() == null) { return R.fail("是否租住，第" + index + "行为空!"); } if (vo.getRoad() == null) { return R.fail("街路巷，第" + index + "行为空!"); } if (vo.getDoorNo() == null) { return R.fail("门牌号，第" + index + "行为空!"); } if (vo.getFloor() == null) { return R.fail("楼排号，第" + index + "行为空!"); } if (vo.getUnitNo() == null) { return R.fail("单元号，第" + index + "行为空!"); } if (vo.getHouseNo() == null) { return R.fail("户室，第" + index + "行为空!"); } if (vo.getNation() == null) { return R.fail("民族，第" + index + "行为空!"); } if (vo.getPoliticalOutlook() == null) { return R.fail("政治面貌，第" + index + "行为空!"); } if (vo.getCardNo() == null) { return R.fail("身份证号码，第" + index + "行为空!"); } if (vo.getPhone() == null) { return R.fail("联系方式，第" + index + "行为空!"); } //判断DB和exel数据重复判断 boolean result = comMngVillageDOs.stream().allMatch(village -> village.getAlley().equals(vo.getAlley()) && village.getHouseNum().equals(vo.getHouseNum())); boolean result = comMngPopulationDOS.stream().anyMatch(population -> population.getCardNo().equals(vo.getCardNo())); if (result) { return R.fail("导入街路巷已存在(" + vo.getAlley() + ")"); return R.fail("导入实有人口已存在(" + vo.getCardNo() + ")"); } index++; }*/ } ComActDO comActDO = comActDAO.selectById(communityId); ArrayList<ComMngPopulationDO> populationDOList = Lists.newArrayList(); list.forEach(vo -> { ComMngPopulationDO comMngPopulationDO = new ComMngPopulationDO(); ComMngVillageDO comMngVillageDO = villageDOList.stream().filter(village -> village.getAlley().equals(vo.getRoad()) && village.getHouseNum().equals(Integer.valueOf(vo.getDoorNo()))).findFirst().orElse(null); BeanUtils.copyProperties(vo, comMngPopulationDO); if (comMngVillageDO == null) { throw new ServiceException("街道巷:" + vo.getRoad() + "不存在！"); } comMngPopulationDO.setActId(comMngVillageDO.getVillageId()); comMngPopulationDO.setActId(comActDO.getCommunityId()); comMngPopulationDO.setStreetId(comActDO.getStreetId()); comMngPopulationDO.setLabel(Joiner.on(",").join(vo.getUserTagStr())); comMngPopulationDO.setVillageName(comMngVillageDO.getGroupAt()); populationDOList.add(comMngPopulationDO); }); //this.saveBatch(populationDOList); return R.ok("共计导入实有人口数量：" + populationDOList.size()); } public boolean isNumeric(String str) { for (int i = 0; i < str.length(); i++) { System.out.println(str.charAt(i)); if (!Character.isDigit(str.charAt(i))) { return false; } } return true; } }
