防火防汛接口开发

tangxiaobao

2021-07-29 a8bb61ea692006fc3ad8747c0ac0832c7ec8f3be

 springcloud_k8s_panzhihuazhihuishequ/common/src/main/java/com/panzhihua/common/listen/ComMngPopulationServeExcelListen.java

@@ -1,21 +1,37 @@
package com.panzhihua.common.listen;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.google.common.collect.Lists;
import com.panzhihua.common.constants.BcDictionaryConstants;
import com.panzhihua.common.enums.*;
import com.panzhihua.common.excel.CustomSheetWriteHandler;
import com.panzhihua.common.exceptions.ServiceException;
import com.panzhihua.common.model.vos.BcDictionaryVO;
import com.panzhihua.common.model.vos.LoginUserInfoVO;
import com.panzhihua.common.model.vos.R;
import com.panzhihua.common.model.vos.community.ComMngPopulationImportErrorVO;
import com.panzhihua.common.model.vos.community.ComMngPopulationServeExcelVO;
import com.panzhihua.common.model.vos.community.*;
import com.panzhihua.common.model.vos.partybuilding.PartyBuildingMemberVO;
import com.panzhihua.common.service.community.CommunityService;
import com.panzhihua.common.utlis.*;
import io.swagger.models.auth.In;
import lombok.Data;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.util.ObjectUtils;
import org.springframework.web.bind.annotation.RequestBody;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
@@ -35,10 +51,25 @@

    private Map<Integer, String> headData;

    private String userName;
    // FTP 登录密码
    private String password;
    // FTP 服务器地址IP地址
    private String host;
    // FTP 端口
    private int port;
    private String excelUrl;

    public ComMngPopulationServeExcelListen(CommunityService communityService, Long communityId) {
    public static String loadUrl;

    public ComMngPopulationServeExcelListen(CommunityService communityService, Long communityId, String userName, String password, String host, int port, String excelUrl) {
        this.communityService = communityService;
        this.communityId = communityId;
        this.userName = userName;
        this.password = password;
        this.host = host;
        this.port = port;
        this.excelUrl = excelUrl;
    }

    /**
@@ -91,25 +122,34 @@
        try {
            ArrayList<ComMngPopulationServeExcelVO> voList = Lists.newArrayList();
            List<ComMngPopulationImportErrorVO> populationImportErrorVOList = new ArrayList<>();
            ArrayList<ComMngPopulationMistakeExcelVO> mistakes = Lists.newArrayList();
            for (Map<Integer, String> oneData : list) {
                ComMngPopulationServeExcelVO vo = new ComMngPopulationServeExcelVO();
                //姓名和身份证都为空,为空户，无需操作,否则就解析年龄，性别，出生年月日
                if(StringUtils.isNotEmpty(oneData.get(0)) || StringUtils.isNotEmpty(oneData.get(1))){
                    if(StringUtils.isEmpty(oneData.get(0))){
                        ComMngPopulationMistakeExcelVO mistake = new ComMngPopulationMistakeExcelVO();
                        ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
                        importErrorVO.setErrorPosition("第" + index + "行，第1列");
                        importErrorVO.setErrorMsg("名字不可为空，请填写姓名");
                        populationImportErrorVOList.add(importErrorVO);
                        index++;
                        setMistake(oneData, mistake);
                        mistake.setMistake("名字不可为空，请填写姓名");
                        mistakes.add(mistake);
                        continue;
                    }
                    vo.setName(oneData.get(0));
                    if(StringUtils.isEmpty(oneData.get(1))){
                        ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
                        ComMngPopulationMistakeExcelVO mistake = new ComMngPopulationMistakeExcelVO();
                        importErrorVO.setErrorPosition("第" + index + "行，第2列"+oneData.get(1));
                        importErrorVO.setErrorMsg("身份证号不可为空，请填写身份证号");
                        populationImportErrorVOList.add(importErrorVO);
                        index++;
                        setMistake(oneData, mistake);
                        mistake.setMistake("身份证号不可为空，请填写身份证号");
                        mistakes.add(mistake);
                        continue;
                    }

@@ -120,6 +160,10 @@
                        importErrorVO.setErrorMsg("身份证号位数有误，请检查身份证号码是否正确");
                        populationImportErrorVOList.add(importErrorVO);
                        index++;
                        ComMngPopulationMistakeExcelVO mistake = new ComMngPopulationMistakeExcelVO();
                        setMistake(oneData, mistake);
                        mistake.setMistake("身份证号位数有误，请检查身份证号码是否正确");
                        mistakes.add(mistake);
                        continue;
                    }
                    vo.setCardNo(oneData.get(1).toUpperCase());
@@ -155,7 +199,20 @@
                    vo.setNation(oneData.get(2));
                }
                if(StringUtils.isNotEmpty(oneData.get(3))){
                    vo.setPoliticalOutlook(PopulPoliticalOutlookEnum.getCodeByName(oneData.get(3)));
                    Integer isOk = PopulPoliticalOutlookEnum.getCodeByName(oneData.get(3));
                    if(isOk.equals(-1)){
                        ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
                        importErrorVO.setErrorPosition("第" + index + "行，第4列");
                        importErrorVO.setErrorMsg("您填写的政治面貌有误");
                        populationImportErrorVOList.add(importErrorVO);
                        index++;
                        ComMngPopulationMistakeExcelVO mistake = new ComMngPopulationMistakeExcelVO();
                        setMistake(oneData, mistake);
                        mistake.setMistake("您填写的政治面貌有误");
                        mistakes.add(mistake);
                        continue;
                    }
                    vo.setPoliticalOutlook(isOk);
                }
                if(StringUtils.isNotEmpty(oneData.get(4))){
                    Integer isOk = PopulHouseUseEnum.getCodeByName(oneData.get(4));
@@ -165,6 +222,10 @@
                        importErrorVO.setErrorMsg("您填写的是否租住有误");
                        populationImportErrorVOList.add(importErrorVO);
                        index++;
                        ComMngPopulationMistakeExcelVO mistake = new ComMngPopulationMistakeExcelVO();
                        setMistake(oneData, mistake);
                        mistake.setMistake("您填写的是否租住有误");
                        mistakes.add(mistake);
                        continue;
                    }
                    vo.setIsRent(isOk);
@@ -197,6 +258,10 @@
                        importErrorVO.setErrorMsg("您填写的与户主关系有误");
                        populationImportErrorVOList.add(importErrorVO);
                        index++;
                        ComMngPopulationMistakeExcelVO mistake = new ComMngPopulationMistakeExcelVO();
                        setMistake(oneData, mistake);
                        mistake.setMistake("您填写的与户主关系有误");
                        mistakes.add(mistake);
                        continue;
                    }else{
                        vo.setRelation(isOk);
@@ -208,6 +273,10 @@
                    importErrorVO.setErrorMsg("街路巷不可为空");
                    populationImportErrorVOList.add(importErrorVO);
                    index++;
                    ComMngPopulationMistakeExcelVO mistake = new ComMngPopulationMistakeExcelVO();
                    setMistake(oneData, mistake);
                    mistake.setMistake("街路巷不可为空");
                    mistakes.add(mistake);
                    continue;
                }
                vo.setRoad(oneData.get(6));
@@ -217,6 +286,10 @@
                    importErrorVO.setErrorMsg("小区号不可为空");
                    populationImportErrorVOList.add(importErrorVO);
                    index++;
                    ComMngPopulationMistakeExcelVO mistake = new ComMngPopulationMistakeExcelVO();
                    setMistake(oneData, mistake);
                    mistake.setMistake("小区号不可为空");
                    mistakes.add(mistake);
                    continue;
                }
                vo.setDoorNo(oneData.get(7).trim());
@@ -226,6 +299,10 @@
                    importErrorVO.setErrorMsg("楼排号不可为空");
                    populationImportErrorVOList.add(importErrorVO);
                    index++;
                    ComMngPopulationMistakeExcelVO mistake = new ComMngPopulationMistakeExcelVO();
                    setMistake(oneData, mistake);
                    mistake.setMistake("楼排号不可为空");
                    mistakes.add(mistake);
                    continue;
                }
                vo.setFloor(oneData.get(8).trim());
@@ -235,6 +312,10 @@
                    importErrorVO.setErrorMsg("单元号不可为空");
                    populationImportErrorVOList.add(importErrorVO);
                    index++;
                    ComMngPopulationMistakeExcelVO mistake = new ComMngPopulationMistakeExcelVO();
                    setMistake(oneData, mistake);
                    mistake.setMistake("单元号不可为空");
                    mistakes.add(mistake);
                    continue;
                }
                vo.setUnitNo(oneData.get(9).trim());
@@ -244,6 +325,10 @@
                    importErrorVO.setErrorMsg("户室不可为空");
                    populationImportErrorVOList.add(importErrorVO);
                    index++;
                    ComMngPopulationMistakeExcelVO mistake = new ComMngPopulationMistakeExcelVO();
                    setMistake(oneData, mistake);
                    mistake.setMistake("户室不可为空");
                    mistakes.add(mistake);
                    continue;
                }
                vo.setHouseNo(oneData.get(10).trim());
@@ -261,6 +346,10 @@
                        importErrorVO.setErrorMsg("您填写的房屋状态有误");
                        populationImportErrorVOList.add(importErrorVO);
                        index++;
                        ComMngPopulationMistakeExcelVO mistake = new ComMngPopulationMistakeExcelVO();
                        setMistake(oneData, mistake);
                        mistake.setMistake("您填写的房屋状态有误");
                        mistakes.add(mistake);
                        continue;
                    }
                    vo.setHouseStatus(isOk);
@@ -273,6 +362,10 @@
                        importErrorVO.setErrorMsg("您填写的房屋用途有误");
                        populationImportErrorVOList.add(importErrorVO);
                        index++;
                        ComMngPopulationMistakeExcelVO mistake = new ComMngPopulationMistakeExcelVO();
                        setMistake(oneData, mistake);
                        mistake.setMistake("您填写的房屋用途有误");
                        mistakes.add(mistake);
                        continue;
                    }
                    vo.setHousePurpose(PopulHousePurposeEnum.getCodeByName(oneData.get(14).trim()));
@@ -285,6 +378,10 @@
                        importErrorVO.setErrorMsg("您填写的管控状态有误");
                        populationImportErrorVOList.add(importErrorVO);
                        index++;
                        ComMngPopulationMistakeExcelVO mistake = new ComMngPopulationMistakeExcelVO();
                        setMistake(oneData, mistake);
                        mistake.setMistake("您填写的管控状态有误");
                        mistakes.add(mistake);
                        continue;
                    }
                    vo.setControlStatus(isOk);
@@ -297,7 +394,20 @@
                    vo.setNativePlace(oneData.get(17).trim());
                }
                if(StringUtils.isNotEmpty(oneData.get(18))){
                    vo.setCultureLevel(PopulCultureLevelEnum.getCodeByName(oneData.get(18)));
                    Integer isOk = PopulCultureLevelEnum.getCodeByName(oneData.get(18));
                    if(isOk.equals(-1)){
                        ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
                        importErrorVO.setErrorPosition("第" + index + "行，第19列");
                        importErrorVO.setErrorMsg("您填写的文化程度有误");
                        populationImportErrorVOList.add(importErrorVO);
                        index++;
                        ComMngPopulationMistakeExcelVO mistake = new ComMngPopulationMistakeExcelVO();
                        setMistake(oneData, mistake);
                        mistake.setMistake("您填写的文化程度有误");
                        mistakes.add(mistake);
                        continue;
                    }
                    vo.setCultureLevel(isOk);
                }
                if(StringUtils.isNotEmpty(oneData.get(19))){
//                    String ma = convertMarriage(oneData.get(19));
@@ -309,6 +419,10 @@
                        importErrorVO.setErrorMsg("您填写的婚姻状况有误");
                        populationImportErrorVOList.add(importErrorVO);
                        index++;
                        ComMngPopulationMistakeExcelVO mistake = new ComMngPopulationMistakeExcelVO();
                        setMistake(oneData, mistake);
                        mistake.setMistake("您填写的婚姻状况有误");
                        mistakes.add(mistake);
                        continue;
                    }else{
                        vo.setMarriage(isOk);
@@ -339,6 +453,10 @@
                            importErrorVO.setErrorMsg("您填写的本地/外地有误");
                            populationImportErrorVOList.add(importErrorVO);
                            index++;
                            ComMngPopulationMistakeExcelVO mistake = new ComMngPopulationMistakeExcelVO();
                            setMistake(oneData, mistake);
                            mistake.setMistake("您填写的本地/外地有误");
                            mistakes.add(mistake);
                            continue;
                        }
                        vo.setOutOrLocal(isOk);
@@ -361,6 +479,10 @@
                        importErrorVO.setErrorMsg("您填写的人员类型有误");
                        populationImportErrorVOList.add(importErrorVO);
                        index++;
                        ComMngPopulationMistakeExcelVO mistake = new ComMngPopulationMistakeExcelVO();
                        setMistake(oneData, mistake);
                        mistake.setMistake("您填写的人员类型有误");
                        mistakes.add(mistake);
                        continue;
                    }
                    vo.setPersonType(isOk);
@@ -439,14 +561,29 @@
            List<ComMngPopulationServeExcelVO> newVoList = voList.stream().filter(ListUtils.distinctByKey(ComMngPopulationServeExcelVO::getDistinctPass)).collect(Collectors.toList());
            R r = communityService.listSavePopulationServeExcelVO(newVoList, communityId);
            if (!R.isOk(r)) {
                List<ComMngPopulationMistakeExcelVO> list = JSONArray.parseArray(JSONArray.toJSONString(r.getData()), ComMngPopulationMistakeExcelVO.class);
                String errMsg = r.getMsg();
                List<ComMngPopulationImportErrorVO> errorList = JSON.parseArray(errMsg,ComMngPopulationImportErrorVO.class);
                if(!errorList.isEmpty()){
                    populationImportErrorVOList.addAll(errorList);
                }
                mistakes.addAll(list);
                R result = mistakeExportPopulation(mistakes);
                loadUrl = (String)result.getData();
                ComMngPopulationImportErrorVO comMngPopulationImportErrorVO = new ComMngPopulationImportErrorVO();
                comMngPopulationImportErrorVO.setErrorPosition("loadUrl");
                comMngPopulationImportErrorVO.setErrorMsg(loadUrl);
                populationImportErrorVOList.add(comMngPopulationImportErrorVO);
                throw new ServiceException("500", JSON.toJSONString(populationImportErrorVOList));
            }else{
//                mistakes.addAll((List<ComMngPopulationMistakeExcelVO>)r.getData());
                R result = mistakeExportPopulation(mistakes);
                loadUrl = (String)result.getData();
                if(!populationImportErrorVOList.isEmpty()){
                    ComMngPopulationImportErrorVO comMngPopulationImportErrorVO = new ComMngPopulationImportErrorVO();
                    comMngPopulationImportErrorVO.setErrorPosition("loadUrl");
                    comMngPopulationImportErrorVO.setErrorMsg(loadUrl);
                    populationImportErrorVOList.add(comMngPopulationImportErrorVO);
                    throw new ServiceException("500", JSON.toJSONString(populationImportErrorVOList));
                }
            }
@@ -469,6 +606,96 @@
        }
    }

    private void setMistake(Map<Integer, String> map, ComMngPopulationMistakeExcelVO vo){
        vo.setName(map.get(0));
        vo.setCardNo(map.get(1));
        vo.setNation(map.get(2));
        vo.setPoliticalOutlook(map.get(3));
        vo.setIsRent(map.get(4));
        vo.setRelation(map.get(5));
        vo.setRoad(map.get(6));
        vo.setDoorNo(map.get(7));
        vo.setFloor(map.get(8));
        vo.setUnitNo(map.get(9));
        vo.setHouseNo(map.get(10));
        vo.setBuildPurpose(map.get(11));
        vo.setBuildArea(map.get(12));
        vo.setHouseStatus(map.get(13));
        vo.setHousePurpose(map.get(14));
        vo.setControlStatus(map.get(15));
        vo.setPhone(map.get(16));
        vo.setNativePlace(map.get(17));
        vo.setCultureLevel(map.get(18));
        vo.setMarriage(map.get(19));
        vo.setHealthy(map.get(20));
        vo.setBloodType(map.get(21));
        vo.setReligion(map.get(22));
        vo.setProfession(map.get(23));
        vo.setWorkCompany(map.get(24));
        vo.setOutOrLocal(map.get(25));
        vo.setCensusRegister(map.get(26));
        vo.setResidence(map.get(27));
//        vo.setAddress(map.get(28));
        vo.setPersonType(map.get(28));
        vo.setCountry(map.get(29));
        vo.setStringOfDeparture(map.get(30));
        vo.setPersonStatus(map.get(31));
        vo.setMonthlyIncome(map.get(32));
        vo.setFamilyStatus(map.get(33));
        vo.setGoalInChina(map.get(34));
        vo.setStringOfArrival(map.get(35));
        vo.setRemark(map.get(36));
        vo.setIdCardPositive(map.get(37));
        vo.setIdCardBack(map.get(38));
        vo.setHouseHold(map.get(39));
        vo.setDeath(map.get(40));
    }

    private R mistakeExportPopulation(List<ComMngPopulationMistakeExcelVO> vo) {
        //生成动态模板excel通过ftp工具上传到主节点，然后返回模板下载地址
        String ftpUrl = "/mnt/data/web/excel/";
        String name = "实有人口错误数据.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, ComMngPopulationMistakeExcelVO.class).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).registerWriteHandler(new CustomSheetWriteHandler()).build();
                    WriteSheet writeSheet = EasyExcel.writerSheet("实有人口错误数据").build();
                    excelWriter.write(vo, 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();
        }
    }

    private String convertMarriage(String ma) {

        if(Objects.equals("已",ma) || Objects.equals("一",ma)){