运营后台首页模块开发以及实有房屋导入接口开发

huanghongfa

2021-10-15 089660d464ee9423dd724177ba5fa82afb9d5920

运营后台首页模块开发以及实有房屋导入接口开发

 springcloud_k8s_panzhihuazhihuishequ/applets_backstage/src/main/java/com/panzhihua/applets_backstage/api/IndexApi.java

@@ -2,7 +2,20 @@

import javax.annotation.Resource;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import com.alibaba.fastjson.JSONArray;
import com.panzhihua.applets_backstage.config.SFTPConfig;
import com.panzhihua.common.excel.CustomSheetWriteHandler;
import com.panzhihua.common.model.dtos.DataKanBansDto;
import com.panzhihua.common.model.vos.LoginUserInfoVO;
import com.panzhihua.common.model.vos.community.IndexUserStatisticsVo;
import com.panzhihua.common.model.vos.community.IndexUserStreetExcelExportVo;
import com.panzhihua.common.utlis.SFTPUtil;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.BeanUtils;
import org.springframework.web.bind.annotation.*;

import com.panzhihua.common.model.vos.IndexDataKanbanVO;
@@ -12,18 +25,28 @@
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

/**
 * @program: springcloud_k8s_panzhihuazhihuishequ
 * @description: 首页接口
 * @author: huang.hongfa weixin hhf9596 qq 959656820
 * @create: 2021-01-05 15:17
 **/
@Slf4j
@Api(tags = {"首页接口"})
@RestController
@RequestMapping("/index/")
public class IndexApi {
    @Resource
    private UserService userService;

    @Resource
    private SFTPConfig sftpConfig;

    @ApiOperation(value = "数据看板", response = IndexDataKanbanVO.class)
    @GetMapping("datakanban")
@@ -36,4 +59,64 @@
    public R dataKanBans(@RequestBody DataKanBansDto dataKanBansDto) {
        return userService.dataKanBans(dataKanBansDto);
    }

    @ApiOperation(value = "社区通统计数据汇总", response = IndexUserStatisticsVo.class)
    @GetMapping("statistics")
    public R communityStatistics() {
        return userService.communityStatistics();
    }

    @ApiOperation(value = "社区通统计数据导出")
    @GetMapping("statistics/export")
    public R communityStatisticsExport() {
        String url = sftpConfig.getExcelUrl();
        String name = "社区通用户统计导出数据.xlsx";
        String ftpUrl = "/mnt/data/web/excel/";
        R r = userService.communityStatisticsExport();
        if (R.isOk(r)) {
            List<IndexUserStreetExcelExportVo> resultList = JSONArray.parseArray(JSONArray.toJSONString(r.getData()), IndexUserStreetExcelExportVo.class);
            try {
                SFTPUtil sftp = new SFTPUtil(sftpConfig.getUserName(), sftpConfig.getPassword(), sftpConfig.getHost(), sftpConfig.getPort());
                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, IndexUserStreetExcelExportVo.class)
                                .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                                .registerWriteHandler(new CustomSheetWriteHandler()).build();
                        WriteSheet writeSheet = EasyExcel.writerSheet("社区通用户统计导出数据").build();
                        excelWriter.write(resultList, 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(url + name);
            } catch (Exception e) {
                e.printStackTrace();
                log.error("文件传输失败【{}】", e.getMessage());
                return R.fail();
            }
        }
        return r;
    }
}

 springcloud_k8s_panzhihuazhihuishequ/applets_backstage/src/main/java/com/panzhihua/applets_backstage/config/SFTPConfig.java

New file
@@ -0,0 +1,30 @@
package com.panzhihua.applets_backstage.config;

import lombok.Data;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Configuration;

/**
 * @Author: llming
 * @Description:
 */
@Configuration
@Data
public class SFTPConfig {
    // FTP 登录用户名
    @Value("${ftp.username}")
    private String userName;
    // FTP 登录密码
    @Value("${ftp.password}")
    private String password;
    // FTP 服务器地址IP地址
    @Value("${ftp.host}")
    private String host;
    // FTP 端口
    @Value("${ftp.port}")
    private int port;
    @Value("${ftp.url}")
    private String url;
    @Value("${excel.userurl}")
    private String excelUrl;
}

 springcloud_k8s_panzhihuazhihuishequ/common/src/main/java/com/panzhihua/common/constants/Constants.java

@@ -158,6 +158,8 @@

    public static final String POPULATION_ERROR_LIST = "POPULATION_ERROR_LIST_";

    public static final String BUILDING_ERROR_LIST = "BUILDING_ERROR_LIST_";

    public static final String DRUG_POPULATION_ERROR_LIST = "DRUG_POPULATION_ERROR_LIST_";

    public static final String CORRECT_POPULATION_ERROR_LIST = "CORRECT_POPULATION_ERROR_LIST_";

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

@@ -4,21 +4,28 @@
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.concurrent.TimeUnit;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.google.common.collect.Lists;
import com.panzhihua.common.constants.Constants;
import com.panzhihua.common.exceptions.ServiceException;
import com.panzhihua.common.model.vos.R;
import com.panzhihua.common.model.vos.community.ComMngBuildingExcelVO;
import com.panzhihua.common.model.vos.community.ComMngPopulationImportErrorVO;
import com.panzhihua.common.service.community.CommunityService;
import com.panzhihua.common.service.grid.GridService;
import com.panzhihua.common.utlis.DateUtils;
import com.panzhihua.common.utlis.Snowflake;
import com.panzhihua.common.utlis.StringUtils;

import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.BeanUtils;
import org.springframework.data.redis.core.StringRedisTemplate;
import org.springframework.data.redis.core.ValueOperations;

/**
 * @description: 社区楼栋导入监听
@@ -30,12 +37,14 @@
    private static int headSize = 0;
    List<Map<Integer, String>> list = new ArrayList<Map<Integer, String>>();
    private CommunityService communityService;
    private StringRedisTemplate stringRedisTemplate;
    private GridService gridService;
    private Long communityId;
    private Map<Integer, String> headData;
    public ComMngBuildingExcelListen(CommunityService communityService, Long communityId) {
    public ComMngBuildingExcelListen(CommunityService communityService, Long communityId, StringRedisTemplate stringRedisTemplate) {
        this.communityService = communityService;
        this.communityId = communityId;
        this.stringRedisTemplate = stringRedisTemplate;
    }

    @Override
@@ -71,103 +80,123 @@
            int index = 2;
            ArrayList<ComMngBuildingExcelVO> voList = Lists.newArrayList();
            List<ComMngPopulationImportErrorVO> populationImportErrorVOList = new ArrayList<>();
            for (int i = 1; i < list.size(); i++) {
            ValueOperations<String, String> valueOperations = stringRedisTemplate.opsForValue();
            String key = Constants.BUILDING_ERROR_LIST + communityId;
            for (int i = 0; i < list.size(); i++) {
                Map<Integer, String> oneData = list.get(i);
                ComMngBuildingExcelVO vo = new ComMngBuildingExcelVO();
                vo.setId(Snowflake.getId());
                vo.setUseType(oneData.get(1));
                vo.setBuildType(oneData.get(2));
                if (StringUtils.isEmpty(oneData.get(oneData.get(3)))) {
                    ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
//                vo.setUseType(oneData.get(0));
//                vo.setBuildType(oneData.get(1));
                if (StringUtils.isEmpty(oneData.get(0))) {
                    ComMngPopulationImportErrorVO importErrorVO = setError(oneData);
                    importErrorVO.setErrorMsg("街路巷不能为空");
                    importErrorVO.setErrorPosition("第" + index + "行");
                    populationImportErrorVOList.add(importErrorVO);
                    vo.setAlley(oneData.get(3));
                    continue;
                }

                vo.setVillageName(oneData.get(4));
                vo.setDoorNo(oneData.get(5));
                vo.setName(oneData.get(6));
                try {
                    vo.setUnitTotal(oneData.get(7) == null ? 0 : Integer.valueOf(oneData.get(7)));
                } catch (Exception e) {
                    if (StringUtils.isEmpty(oneData.get(oneData.get(5)))) {
                        ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
                        importErrorVO.setErrorMsg("门牌号不能为空");
                        importErrorVO.setErrorPosition("第" + index + "行");
                        populationImportErrorVOList.add(importErrorVO);
                        vo.setDoorNo(oneData.get(5));
                    }

                    if (StringUtils.isEmpty(oneData.get(oneData.get(5)))) {
                        ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
                        importErrorVO.setErrorMsg("楼栋号不能为空");
                        importErrorVO.setErrorPosition("第" + index + "行");
                        populationImportErrorVOList.add(importErrorVO);
                        vo.setName(oneData.get(6));
                    }

                    try {
                        vo.setUnitTotal(oneData.get(7) == null ? 0 : Integer.valueOf(oneData.get(7)));
                    } catch (Exception e1) {
                        vo.setUnitTotal(0);
                    }
                    try {
                        vo.setBuildFloorSum(oneData.get(8) == null ? 0 : Integer.valueOf(oneData.get(8)));
                    } catch (Exception e1) {
                        vo.setBuildFloorSum(0);
                    }

                    try {
                        vo.setEveryFloorSum(oneData.get(9) == null ? 0 : Integer.valueOf(oneData.get(9)));
                    } catch (Exception e1) {
                        vo.setEveryFloorSum(0);
                    }

                    try {
                        vo.setBuildUserSum(oneData.get(10) == null ? 0 : Integer.valueOf(oneData.get(10)));
                    } catch (Exception e1) {
                        vo.setBuildUserSum(0);
                    }
                    try {
                        vo.setElevatorTotal(oneData.get(11) == null ? 0 : Integer.valueOf(oneData.get(11)));
                    } catch (Exception e1) {
                        vo.setElevatorTotal(0);
                    }

                    vo.setPropertyCompanyName(oneData.get(12));
                    try {
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy");
                        vo.setBuildDate(sdf.parse(oneData.get(13)));
                    } catch (Exception e1) {

                    }
                    vo.setRemark(oneData.get(14));
                    vo.setGridId(oneData.get(15));
                    vo.setGridName(oneData.get(15));
                    voList.add(vo);
                    index++;
                vo.setAlley(oneData.get(0));
                vo.setVillageName(oneData.get(1));
                if (StringUtils.isEmpty(oneData.get(2))) {
                    ComMngPopulationImportErrorVO importErrorVO = setError(oneData);
                    importErrorVO.setErrorMsg("门牌号不能为空");
                    populationImportErrorVOList.add(importErrorVO);
                    continue;
                }
                vo.setDoorNo(oneData.get(2));

                R r = communityService.listSaveBuildingExcelVO(voList, communityId);
                if (!R.isOk(r)) {
                    String errMsg = r.getMsg();
                    List<ComMngPopulationImportErrorVO> errorList =
                        JSON.parseArray(errMsg, ComMngPopulationImportErrorVO.class);
                    if (!errorList.isEmpty()) {
                        populationImportErrorVOList.addAll(errorList);
                    }
                    throw new ServiceException("500", JSON.toJSONString(populationImportErrorVOList));
                } else {
                    if (!populationImportErrorVOList.isEmpty()) {
                        throw new ServiceException("500", JSON.toJSONString(populationImportErrorVOList));
                    }
                if (StringUtils.isEmpty(oneData.get(3))) {
                    ComMngPopulationImportErrorVO importErrorVO = setError(oneData);
                    importErrorVO.setErrorMsg("楼栋号不能为空");
                    populationImportErrorVOList.add(importErrorVO);
                    continue;
                }
                vo.setName(oneData.get(3));

//                try {
//                    vo.setUnitTotal(oneData.get(6) == null ? 0 : Integer.valueOf(oneData.get(6)));
//                } catch (Exception e1) {
//                    vo.setUnitTotal(0);
//                }
//                try {
//                    vo.setBuildFloorSum(oneData.get(7) == null ? 0 : Integer.valueOf(oneData.get(7)));
//                } catch (Exception e1) {
//                    vo.setBuildFloorSum(0);
//                }
//
//                try {
//                    vo.setEveryFloorSum(oneData.get(8) == null ? 0 : Integer.valueOf(oneData.get(8)));
//                } catch (Exception e1) {
//                    vo.setEveryFloorSum(0);
//                }
//
//                try {
//                    vo.setBuildUserSum(oneData.get(9) == null ? 0 : Integer.valueOf(oneData.get(9)));
//                } catch (Exception e1) {
//                    vo.setBuildUserSum(0);
//                }
//                try {
//                    vo.setElevatorTotal(oneData.get(10) == null ? 0 : Integer.valueOf(oneData.get(10)));
//                } catch (Exception e1) {
//                    vo.setElevatorTotal(0);
//                }
//
//                vo.setPropertyCompanyName(oneData.get(11));
//                try {
//                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy");
//                    vo.setBuildDate(sdf.parse(oneData.get(12)));
//                } catch (Exception e1) {
//                    vo.setBuildDate(null);
//                }
//                vo.setRemark(oneData.get(13));
//                vo.setGridId(oneData.get(14));
//                vo.setGridName(oneData.get(14));
                voList.add(vo);
                index++;
            }

            R r = communityService.listSaveBuildingExcelVO(voList, communityId);
            if (!R.isOk(r)) {
                List<ComMngPopulationImportErrorVO> errorList =
                        JSONArray.parseArray(JSONArray.toJSONString(r.getData()), ComMngPopulationImportErrorVO.class);
                if (!errorList.isEmpty()) {
                    populationImportErrorVOList.addAll(errorList);
                }
                log.info("将错误数据存入redis中");
                valueOperations.set(key, JSONArray.toJSONString(populationImportErrorVOList), 1, TimeUnit.HOURS);
                log.info("将错误数据存入redis中成功");
                throw new ServiceException("500", key);
            } else {
                if (!populationImportErrorVOList.isEmpty()) {
                    log.info("将错误数据存入redis中");
                    valueOperations.set(key, JSONArray.toJSONString(populationImportErrorVOList), 1, TimeUnit.HOURS);
                    log.info("将错误数据存入redis中成功");
                    throw new ServiceException("500", key);
                }
            }
        } catch (NumberFormatException e) {
            e.printStackTrace();
        }
    }

    private ComMngPopulationImportErrorVO setError(Map<Integer, String> oneData){
        ComMngPopulationImportErrorVO result = new ComMngPopulationImportErrorVO();
        if(oneData != null){
//            result.setUseType(oneData.get(0));
//            result.setBuildType(oneData.get(1));
            result.setAlley(oneData.get(0));
            result.setVillageName(oneData.get(1));
            result.setDoorNo(oneData.get(2));
            result.setName(oneData.get(3));
//            result.setUnitTotal(Integer.parseInt(oneData.get(6)));
//            result.setBuildFloorSum(Integer.parseInt(oneData.get(7)));
//            result.setEveryFloorSum(Integer.parseInt(oneData.get(8)));
//            result.setBuildUserSum(Integer.parseInt(oneData.get(9)));
//            result.setElevatorTotal(Integer.parseInt(oneData.get(10)));
//            result.setPropertyCompanyName(oneData.get(11));
//            result.setBuildDate(DateUtils.stringToDate(oneData.get(12),DateUtils.format_yyyy));
//            result.setRemark(oneData.get(13));
//            result.setGridId(oneData.get(14));
        }
        return result;
    }
}

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

@@ -95,54 +95,54 @@
            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))) {
                        ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
                        importErrorVO.setErrorPosition("第" + index + "行，第1列");
                        importErrorVO.setErrorMsg("名字不可为空，请填写姓名");
                        populationImportErrorVOList.add(importErrorVO);
                        index++;
                        continue;
                    }
                    vo.setName(oneData.get(0));
                    if (StringUtils.isEmpty(oneData.get(1))) {
                        ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
                        importErrorVO.setErrorPosition("第" + index + "行，第2列" + oneData.get(1));
                        importErrorVO.setErrorMsg("身份证号不可为空，请填写身份证号");
                        populationImportErrorVOList.add(importErrorVO);
                        index++;
                        continue;
                    }

                    // 判断身份证号码位数
                    if (oneData.get(1).length() != 18) {
                        ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
                        importErrorVO.setErrorPosition("第" + index + "行，第2列" + oneData.get(1));
                        importErrorVO.setErrorMsg("身份证号位数有误，请检查身份证号码是否正确");
                        populationImportErrorVOList.add(importErrorVO);
                        index++;
                        continue;
                    }
                    vo.setCardNo(oneData.get(1));
                    // 根据身份证号码解析年龄以及性别
                    // 获取用户生日
                    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(0)) || StringUtils.isNotEmpty(oneData.get(1))) {
//                    if (StringUtils.isEmpty(oneData.get(0))) {
//                        ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
//                        importErrorVO.setErrorPosition("第" + index + "行，第1列");
//                        importErrorVO.setErrorMsg("名字不可为空，请填写姓名");
//                        populationImportErrorVOList.add(importErrorVO);
//                        index++;
//                        continue;
//                    }
//                    vo.setName(oneData.get(0));
//                    if (StringUtils.isEmpty(oneData.get(1))) {
//                        ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
//                        importErrorVO.setErrorPosition("第" + index + "行，第2列" + oneData.get(1));
//                        importErrorVO.setErrorMsg("身份证号不可为空，请填写身份证号");
//                        populationImportErrorVOList.add(importErrorVO);
//                        index++;
//                        continue;
//                    }
//
//                    // 判断身份证号码位数
//                    if (oneData.get(1).length() != 18) {
//                        ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
//                        importErrorVO.setErrorPosition("第" + index + "行，第2列" + oneData.get(1));
//                        importErrorVO.setErrorMsg("身份证号位数有误，请检查身份证号码是否正确");
//                        populationImportErrorVOList.add(importErrorVO);
//                        index++;
//                        continue;
//                    }
//                    vo.setCardNo(oneData.get(1));
//                    // 根据身份证号码解析年龄以及性别
//                    // 获取用户生日
//                    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(2))) {
                    // String nation = oneData.get(2);
                    // if(!nation.contains("族")){
@@ -158,18 +158,18 @@
                if (StringUtils.isNotEmpty(oneData.get(3))) {
                    vo.setPoliticalOutlook(PopulPoliticalOutlookEnum.getCodeByName(oneData.get(3)));
                }
                if (StringUtils.isNotEmpty(oneData.get(4))) {
                    Integer isOk = PopulHouseUseEnum.getCodeByName(oneData.get(4));
                    if (isOk.equals(-1)) {
                        ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
                        importErrorVO.setErrorPosition("第" + index + "行，第5列");
                        importErrorVO.setErrorMsg("您填写的是否租住有误");
                        populationImportErrorVOList.add(importErrorVO);
                        index++;
                        continue;
                    }
                    vo.setIsRent(isOk);
                }
//                if (StringUtils.isNotEmpty(oneData.get(4))) {
//                    Integer isOk = PopulHouseUseEnum.getCodeByName(oneData.get(4));
//                    if (isOk.equals(-1)) {
//                        ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
//                        importErrorVO.setErrorPosition("第" + index + "行，第5列");
//                        importErrorVO.setErrorMsg("您填写的是否租住有误");
//                        populationImportErrorVOList.add(importErrorVO);
//                        index++;
//                        continue;
//                    }
//                    vo.setIsRent(isOk);
//                }
                if (StringUtils.isNotEmpty(oneData.get(5))) {

                    // String ra = convertRelation(oneData.get(5));
@@ -201,50 +201,50 @@
                    }

                }
                if (StringUtils.isEmpty(oneData.get(6))) {
                    ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
                    importErrorVO.setErrorPosition("第" + index + "行，第7列");
                    importErrorVO.setErrorMsg("街路巷不可为空");
                    populationImportErrorVOList.add(importErrorVO);
                    index++;
                    continue;
                }
                vo.setRoad(oneData.get(6));
                if (StringUtils.isEmpty(oneData.get(7))) {
                    ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
                    importErrorVO.setErrorPosition("第" + index + "行，第8列");
                    importErrorVO.setErrorMsg("小区号不可为空");
                    populationImportErrorVOList.add(importErrorVO);
                    index++;
                    continue;
                }
                vo.setDoorNo(oneData.get(7).trim());
                if (StringUtils.isEmpty(oneData.get(8))) {
                    ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
                    importErrorVO.setErrorPosition("第" + index + "行，第9列");
                    importErrorVO.setErrorMsg("楼排号不可为空");
                    populationImportErrorVOList.add(importErrorVO);
                    index++;
                    continue;
                }
                vo.setFloor(oneData.get(8).trim());
                if (StringUtils.isEmpty(oneData.get(9))) {
                    ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
                    importErrorVO.setErrorPosition("第" + index + "行，第10列");
                    importErrorVO.setErrorMsg("单元号不可为空");
                    populationImportErrorVOList.add(importErrorVO);
                    index++;
                    continue;
                }
                vo.setUnitNo(oneData.get(9).trim());
                if (StringUtils.isEmpty(oneData.get(10))) {
                    ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
                    importErrorVO.setErrorPosition("第" + index + "行，第11列");
                    importErrorVO.setErrorMsg("户室不可为空");
                    populationImportErrorVOList.add(importErrorVO);
                    index++;
                    continue;
                }
//                if (StringUtils.isEmpty(oneData.get(6))) {
//                    ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
//                    importErrorVO.setErrorPosition("第" + index + "行，第7列");
//                    importErrorVO.setErrorMsg("街路巷不可为空");
//                    populationImportErrorVOList.add(importErrorVO);
//                    index++;
//                    continue;
//                }
//                vo.setRoad(oneData.get(6));
//                if (StringUtils.isEmpty(oneData.get(7))) {
//                    ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
//                    importErrorVO.setErrorPosition("第" + index + "行，第8列");
//                    importErrorVO.setErrorMsg("小区号不可为空");
//                    populationImportErrorVOList.add(importErrorVO);
//                    index++;
//                    continue;
//                }
//                vo.setDoorNo(oneData.get(7).trim());
//                if (StringUtils.isEmpty(oneData.get(8))) {
//                    ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
//                    importErrorVO.setErrorPosition("第" + index + "行，第9列");
//                    importErrorVO.setErrorMsg("楼排号不可为空");
//                    populationImportErrorVOList.add(importErrorVO);
//                    index++;
//                    continue;
//                }
//                vo.setFloor(oneData.get(8).trim());
//                if (StringUtils.isEmpty(oneData.get(9))) {
//                    ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
//                    importErrorVO.setErrorPosition("第" + index + "行，第10列");
//                    importErrorVO.setErrorMsg("单元号不可为空");
//                    populationImportErrorVOList.add(importErrorVO);
//                    index++;
//                    continue;
//                }
//                vo.setUnitNo(oneData.get(9).trim());
//                if (StringUtils.isEmpty(oneData.get(10))) {
//                    ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
//                    importErrorVO.setErrorPosition("第" + index + "行，第11列");
//                    importErrorVO.setErrorMsg("户室不可为空");
//                    populationImportErrorVOList.add(importErrorVO);
//                    index++;
//                    continue;
//                }
                vo.setHouseNo(oneData.get(10).trim());
                if (StringUtils.isNotEmpty(oneData.get(11))) {
                    vo.setBuildPurpose(oneData.get(11).trim());
@@ -252,42 +252,42 @@
                if (StringUtils.isNotEmpty(oneData.get(12))) {
                    vo.setBuildArea(oneData.get(12).trim());
                }
                if (StringUtils.isNotEmpty(oneData.get(13))) {
                    Integer isOk = PopulHouseStatusEnum.getCodeByName(oneData.get(13).trim());
                    if (isOk.equals(-1)) {
                        ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
                        importErrorVO.setErrorPosition("第" + index + "行，第14列");
                        importErrorVO.setErrorMsg("您填写的房屋状态有误");
                        populationImportErrorVOList.add(importErrorVO);
                        index++;
                        continue;
                    }
                    vo.setHouseStatus(isOk);
                }
                if (StringUtils.isNotEmpty(oneData.get(14))) {
                    Integer isOk = PopulHousePurposeEnum.getCodeByName(oneData.get(14).trim());
                    if (isOk.equals(-1)) {
                        ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
                        importErrorVO.setErrorPosition("第" + index + "行，第15列");
                        importErrorVO.setErrorMsg("您填写的房屋用途有误");
                        populationImportErrorVOList.add(importErrorVO);
                        index++;
                        continue;
                    }
                    vo.setHousePurpose(PopulHousePurposeEnum.getCodeByName(oneData.get(14).trim()));
                }
                if (StringUtils.isNotEmpty(oneData.get(15))) {
                    Integer isOk = PopulHouseControlStatusEnum.getCodeByName(oneData.get(15));
                    if (isOk.equals(-1)) {
                        ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
                        importErrorVO.setErrorPosition("第" + index + "行，第16列");
                        importErrorVO.setErrorMsg("您填写的管控状态有误");
                        populationImportErrorVOList.add(importErrorVO);
                        index++;
                        continue;
                    }
                    vo.setControlStatus(isOk);
                }
//                if (StringUtils.isNotEmpty(oneData.get(13))) {
//                    Integer isOk = PopulHouseStatusEnum.getCodeByName(oneData.get(13).trim());
//                    if (isOk.equals(-1)) {
//                        ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
//                        importErrorVO.setErrorPosition("第" + index + "行，第14列");
//                        importErrorVO.setErrorMsg("您填写的房屋状态有误");
//                        populationImportErrorVOList.add(importErrorVO);
//                        index++;
//                        continue;
//                    }
//                    vo.setHouseStatus(isOk);
//                }
//                if (StringUtils.isNotEmpty(oneData.get(14))) {
//                    Integer isOk = PopulHousePurposeEnum.getCodeByName(oneData.get(14).trim());
//                    if (isOk.equals(-1)) {
//                        ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
//                        importErrorVO.setErrorPosition("第" + index + "行，第15列");
//                        importErrorVO.setErrorMsg("您填写的房屋用途有误");
//                        populationImportErrorVOList.add(importErrorVO);
//                        index++;
//                        continue;
//                    }
//                    vo.setHousePurpose(PopulHousePurposeEnum.getCodeByName(oneData.get(14).trim()));
//                }
//                if (StringUtils.isNotEmpty(oneData.get(15))) {
//                    Integer isOk = PopulHouseControlStatusEnum.getCodeByName(oneData.get(15));
//                    if (isOk.equals(-1)) {
//                        ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
//                        importErrorVO.setErrorPosition("第" + index + "行，第16列");
//                        importErrorVO.setErrorMsg("您填写的管控状态有误");
//                        populationImportErrorVOList.add(importErrorVO);
//                        index++;
//                        continue;
//                    }
//                    vo.setControlStatus(isOk);
//                }

                if (StringUtils.isNotEmpty(oneData.get(16))) {
                    vo.setPhone(oneData.get(16).trim());
@@ -295,34 +295,34 @@
                if (StringUtils.isNotEmpty(oneData.get(17))) {
                    vo.setNativePlace(oneData.get(17).trim());
                }
                if (StringUtils.isNotEmpty(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++;
                        continue;
                    }
                    vo.setCultureLevel(isOk);
                }
                if (StringUtils.isNotEmpty(oneData.get(19))) {
                    // String ma = convertMarriage(oneData.get(19));
                    Integer isOk = PopulMarriageEnum.getCodeByName(oneData.get(19));
                    if (isOk.equals(-1)) {
                        // vo.setMarriageStr(ma);
                        ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
                        importErrorVO.setErrorPosition("第" + index + "行，第20列");
                        importErrorVO.setErrorMsg("您填写的婚姻状况有误");
                        populationImportErrorVOList.add(importErrorVO);
                        index++;
                        continue;
                    } else {
                        vo.setMarriage(isOk);
                    }

                }
//                if (StringUtils.isNotEmpty(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++;
//                        continue;
//                    }
//                    vo.setCultureLevel(isOk);
//                }
//                if (StringUtils.isNotEmpty(oneData.get(19))) {
//                    // String ma = convertMarriage(oneData.get(19));
//                    Integer isOk = PopulMarriageEnum.getCodeByName(oneData.get(19));
//                    if (isOk.equals(-1)) {
//                        // vo.setMarriageStr(ma);
//                        ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
//                        importErrorVO.setErrorPosition("第" + index + "行，第20列");
//                        importErrorVO.setErrorMsg("您填写的婚姻状况有误");
//                        populationImportErrorVOList.add(importErrorVO);
//                        index++;
//                        continue;
//                    } else {
//                        vo.setMarriage(isOk);
//                    }
//
//                }
                if (StringUtils.isNotEmpty(oneData.get(20))) {
                    vo.setHealthy(oneData.get(20).trim());
                }
@@ -339,18 +339,18 @@
                if (StringUtils.isNotEmpty(oneData.get(24))) {
                    vo.setWorkCompany(oneData.get(24).trim());
                }
                if (StringUtils.isNotEmpty(oneData.get(25))) {
                    Integer isOk = PopulOutOrLocalEnum.getCodeByName(oneData.get(25));
                    if (isOk.equals(-1)) {
                        ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
                        importErrorVO.setErrorPosition("第" + index + "行，第26列");
                        importErrorVO.setErrorMsg("您填写的本地/外地有误");
                        populationImportErrorVOList.add(importErrorVO);
                        index++;
                        continue;
                    }
                    vo.setOutOrLocal(isOk);
                }
//                if (StringUtils.isNotEmpty(oneData.get(25))) {
//                    Integer isOk = PopulOutOrLocalEnum.getCodeByName(oneData.get(25));
//                    if (isOk.equals(-1)) {
//                        ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
//                        importErrorVO.setErrorPosition("第" + index + "行，第26列");
//                        importErrorVO.setErrorMsg("您填写的本地/外地有误");
//                        populationImportErrorVOList.add(importErrorVO);
//                        index++;
//                        continue;
//                    }
//                    vo.setOutOrLocal(isOk);
//                }
                if (StringUtils.isNotEmpty(oneData.get(26))) {
                    vo.setCensusRegister(oneData.get(26).trim());
                }
@@ -359,18 +359,18 @@
                } else {
                    vo.setResidence(PopulIsOkEnum.getCodeByName(oneData.get(27).trim()));
                }
                if (StringUtils.isNotEmpty(oneData.get(28))) {
                    Integer isOk = PopulPersonTypeEnum.getCodeByName(oneData.get(28));
                    if (isOk.equals(-1)) {
                        ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
                        importErrorVO.setErrorPosition("第" + index + "行，第30列");
                        importErrorVO.setErrorMsg("您填写的人员类型有误");
                        populationImportErrorVOList.add(importErrorVO);
                        index++;
                        continue;
                    }
                    vo.setPersonType(isOk);
                }
//                if (StringUtils.isNotEmpty(oneData.get(28))) {
//                    Integer isOk = PopulPersonTypeEnum.getCodeByName(oneData.get(28));
//                    if (isOk.equals(-1)) {
//                        ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
//                        importErrorVO.setErrorPosition("第" + index + "行，第30列");
//                        importErrorVO.setErrorMsg("您填写的人员类型有误");
//                        populationImportErrorVOList.add(importErrorVO);
//                        index++;
//                        continue;
//                    }
//                    vo.setPersonType(isOk);
//                }
                if (StringUtils.isNotEmpty(oneData.get(29))) {
                    vo.setCountry(oneData.get(29).trim());
                }
@@ -464,7 +464,7 @@
            e.printStackTrace();
            List<ComMngPopulationImportErrorVO> populationImportErrorVOList = new ArrayList<>();
            ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
            importErrorVO.setErrorPosition("第" + index + "行");
//            importErrorVO.setErrorPosition("第" + index + "行");
            importErrorVO.setErrorMsg("数据格式有误，请检查文档内数据");
            populationImportErrorVOList.add(importErrorVO);
            throw new ServiceException("500", JSON.toJSONString(populationImportErrorVOList));
@@ -472,7 +472,7 @@
            e1.printStackTrace();
            List<ComMngPopulationImportErrorVO> populationImportErrorVOList = new ArrayList<>();
            ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
            importErrorVO.setErrorPosition("第" + index + "行");
//            importErrorVO.setErrorPosition("第" + index + "行");
            importErrorVO.setErrorMsg("数据格式有误，请检查文档内数据");
            populationImportErrorVOList.add(importErrorVO);
            throw new ServiceException("500", JSON.toJSONString(populationImportErrorVOList));

 springcloud_k8s_panzhihuazhihuishequ/common/src/main/java/com/panzhihua/common/model/vos/community/ComMngBuildingExcelVO.java

@@ -17,49 +17,49 @@
    private static final long serialVersionUID = 1719392023800643085L;

    private Long id;
    @ExcelProperty(value = "楼房属性", index = 1)
    private String useType;
    @ExcelProperty(value = "建筑类型", index = 2)
    private String buildType;
//    @ExcelProperty(value = "楼房属性", index = 0)
//    private String useType;
//    @ExcelProperty(value = "建筑类型", index = 1)
//    private String buildType;

    @ExcelProperty(value = "街路巷", index = 3)
    @ExcelProperty(value = "街路巷", index = 0)
    private String alley;

    @ExcelProperty(value = "小区名称", index = 4)
    @ExcelProperty(value = "小区名称", index = 1)
    private String villageName;

    @ExcelProperty(value = "门牌号", index = 5)
    @ExcelProperty(value = "门牌号", index = 2)
    private String doorNo;

    @ExcelProperty(value = "楼栋号/楼栋名称", index = 6)
    @ExcelProperty(value = "楼栋号/楼栋名称", index = 3)
    private String name;

    @ExcelProperty(value = "单元数", index = 7)
    private Integer unitTotal;

    @ExcelProperty(value = "楼层数", index = 8)
    private Integer buildFloorSum;

    @ExcelProperty(value = "每层户数", index = 9)
    private Integer everyFloorSum;

    @ExcelProperty(value = "总户数", index = 10)
    private Integer buildUserSum;

    @ExcelProperty(value = "电梯", index = 11)
    private Integer elevatorTotal;

    @ExcelProperty(value = "物业公司名称", index = 12)
    private String propertyCompanyName;

    @ExcelProperty(value = "建筑年代", index = 13)
    private Date buildDate;

    @ExcelProperty(value = "备注", index = 14)
    private String remark;

    @ExcelProperty(value = "网格划分", index = 15)
    private String gridId;
//    @ExcelProperty(value = "单元数", index = 6)
//    private Integer unitTotal;
//
//    @ExcelProperty(value = "楼层数", index = 7)
//    private Integer buildFloorSum;
//
//    @ExcelProperty(value = "每层户数", index = 8)
//    private Integer everyFloorSum;
//
//    @ExcelProperty(value = "总户数", index = 9)
//    private Integer buildUserSum;
//
//    @ExcelProperty(value = "电梯", index = 10)
//    private Integer elevatorTotal;
//
//    @ExcelProperty(value = "物业公司名称", index = 11)
//    private String propertyCompanyName;
//
//    @ExcelProperty(value = "建筑年代", index = 12)
//    private Date buildDate;
//
//    @ExcelProperty(value = "备注", index = 13)
//    private String remark;
//
//    @ExcelProperty(value = "网格划分", index = 14)
//    private String gridId;

    private String gridName;
}

 springcloud_k8s_panzhihuazhihuishequ/common/src/main/java/com/panzhihua/common/model/vos/community/ComMngPopulationImportErrorVO.java

@@ -1,6 +1,7 @@
package com.panzhihua.common.model.vos.community;

import java.io.Serializable;
import java.util.Date;

import com.alibaba.excel.annotation.ExcelProperty;

@@ -9,10 +10,51 @@
@Data
public class ComMngPopulationImportErrorVO implements Serializable {

    @ExcelProperty(value = "错误位置", index = 0)
    private String errorPosition;
//    @ExcelProperty(value = "楼房属性", index = 0)
//    private String useType;
//    @ExcelProperty(value = "建筑类型", index = 1)
//    private String buildType;

    @ExcelProperty(value = "错误原因", index = 0)
    @ExcelProperty(value = "街路巷", index = 0)
    private String alley;

    @ExcelProperty(value = "小区名称", index = 1)
    private String villageName;

    @ExcelProperty(value = "门牌号", index = 2)
    private String doorNo;

    @ExcelProperty(value = "楼栋号/楼栋名称", index = 3)
    private String name;

//    @ExcelProperty(value = "单元数", index = 6)
//    private Integer unitTotal;
//
//    @ExcelProperty(value = "楼层数", index = 7)
//    private Integer buildFloorSum;
//
//    @ExcelProperty(value = "每层户数", index = 8)
//    private Integer everyFloorSum;
//
//    @ExcelProperty(value = "总户数", index = 9)
//    private Integer buildUserSum;
//
//    @ExcelProperty(value = "电梯", index = 10)
//    private Integer elevatorTotal;
//
//    @ExcelProperty(value = "物业公司名称", index = 11)
//    private String propertyCompanyName;
//
//    @ExcelProperty(value = "建筑时间", index = 12)
//    private Date buildDate;
//
//    @ExcelProperty(value = "备注", index = 13)
//    private String remark;
//
//    @ExcelProperty(value = "网格划分", index = 14)
//    private String gridId;

    @ExcelProperty(value = "错误原因", index = 4)
    private String errorMsg;

}

 springcloud_k8s_panzhihuazhihuishequ/common/src/main/java/com/panzhihua/common/model/vos/community/IndexUserCommunityVo.java

New file
@@ -0,0 +1,24 @@
package com.panzhihua.common.model.vos.community;

import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

/**
 * @author lyq
 * 运营后台-首页社区通用户街道下社区统计汇总
 */
@Data
@ApiModel("运营后台-首页社区通用户街道下社区统计汇总")
public class IndexUserCommunityVo {

    @ApiModelProperty(value = "社区id")
    private Long communityId;

    @ApiModelProperty(value = "社区下注册用户数量")
    private Integer userNum;

    @ApiModelProperty(value = "社区名称")
    private String name;
    
}

 springcloud_k8s_panzhihuazhihuishequ/common/src/main/java/com/panzhihua/common/model/vos/community/IndexUserStatisticsVo.java

New file
@@ -0,0 +1,19 @@
package com.panzhihua.common.model.vos.community;

import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

import java.util.List;

/**
 * @author lyq
 * 运营后台-首页社区通用户统计汇总
 */
@Data
@ApiModel("运营后台-首页社区通用户统计汇总")
public class IndexUserStatisticsVo {

    @ApiModelProperty(value = "街道统计数据")
    private List<IndexUserStreetVo> streetList;
}

 springcloud_k8s_panzhihuazhihuishequ/common/src/main/java/com/panzhihua/common/model/vos/community/IndexUserStreetExcelExportVo.java

New file
@@ -0,0 +1,30 @@
package com.panzhihua.common.model.vos.community;

import com.alibaba.excel.annotation.ExcelProperty;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

import java.util.List;

/**
 * @author lyq
 * 运营后台-首页社区通用户街道统计汇总
 */
@Data
@ApiModel("运营后台-首页社区通用户街道统计汇总")
public class IndexUserStreetExcelExportVo {

    @ExcelProperty(value = "街道名称", index = 0)
    private String streetName;

    @ExcelProperty(value = "社区名称", index = 1)
    private String communityName;

    @ExcelProperty(value = "街道下注册用户数量", index = 2)
    private Integer streetNum;

    @ExcelProperty(value = "社区下注册用户数量", index = 3)
    private Integer userNum;

}

 springcloud_k8s_panzhihuazhihuishequ/common/src/main/java/com/panzhihua/common/model/vos/community/IndexUserStreetVo.java

New file
@@ -0,0 +1,29 @@
package com.panzhihua.common.model.vos.community;

import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

import java.util.List;

/**
 * @author lyq
 * 运营后台-首页社区通用户街道统计汇总
 */
@Data
@ApiModel("运营后台-首页社区通用户街道统计汇总")
public class IndexUserStreetVo {

    @ApiModelProperty(value = "街道下社区统计数据")
    private List<IndexUserCommunityVo> communityList;

    @ApiModelProperty(value = "街道id")
    private Long streetId;

    @ApiModelProperty(value = "街道下注册用户数量")
    private Integer userNum;

    @ApiModelProperty(value = "街道名称")
    private String name;

}

 springcloud_k8s_panzhihuazhihuishequ/common/src/main/java/com/panzhihua/common/service/user/UserService.java

@@ -1070,4 +1070,18 @@
     */
    @PostMapping("datakanbans")
    R dataKanBans(@RequestBody DataKanBansDto dataKanBansDto);

    /**
     * 运营后台-用户数据统计汇总
     * @return  用户数据统计汇总
     */
    @GetMapping("community/statistics")
    R communityStatistics();

    /**
     * 运营后台-用户数据统计导出
     * @return  用户数据
     */
    @GetMapping("community/statistics/export")
    R communityStatisticsExport();
}

 springcloud_k8s_panzhihuazhihuishequ/common/src/main/java/com/panzhihua/common/utlis/DateUtils.java

@@ -25,6 +25,7 @@
    public static DateTimeFormatter format_ymd_String = DateTimeFormatter.ofPattern("yyMMdd");
    public static SimpleDateFormat yyyyMM_format = new SimpleDateFormat("yyyy-MM");
    public static SimpleDateFormat format_yyymmdd = new SimpleDateFormat("yyyyMMdd");
    public static SimpleDateFormat format_yyyy = new SimpleDateFormat("yyyy");
    private static DateTimeFormatter format_ymdhmssss = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss.SSS");
    private static DateTimeFormatter format_ymds = DateTimeFormatter.ofPattern("yyyyMMdd");
    private static DateTimeFormatter format_yms = DateTimeFormatter.ofPattern("yyyyMM");

 springcloud_k8s_panzhihuazhihuishequ/community_backstage/src/main/java/com/panzhihua/community_backstage/api/BuildingApi.java

@@ -1,23 +1,37 @@
package com.panzhihua.community_backstage.api;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import javax.annotation.Resource;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import com.alibaba.fastjson.JSONArray;
import com.panzhihua.common.model.dtos.community.building.admin.*;
import com.panzhihua.common.model.vos.community.ComMngPopulationImportErrorVO;
import com.panzhihua.common.model.vos.community.ComMngVillageVO;
import com.panzhihua.common.model.vos.community.building.admin.BuildingDetailAdminVo;
import com.panzhihua.common.model.vos.community.building.admin.BuildingListHeaderStatisticsAdminVo;
import com.panzhihua.common.model.vos.community.building.admin.BuildingUnitHouseListAdminVo;
import com.panzhihua.common.model.vos.community.building.admin.PageBuildingListAdminVo;
import com.panzhihua.common.model.vos.grid.ComMngVillageBuildingHouseDetailVO;
import com.panzhihua.common.model.vos.grid.ComMngVillageBuildingHouseVO;
import com.panzhihua.common.model.vos.grid.ComMngVillageBuildingVO;
import com.panzhihua.common.utlis.DateUtils;
import com.panzhihua.common.utlis.SFTPUtil;
import com.panzhihua.community_backstage.excel.CustomSheetWriteHandler;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.data.redis.core.StringRedisTemplate;
import org.springframework.data.redis.core.ValueOperations;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import com.alibaba.excel.EasyExcel;
import com.panzhihua.common.controller.BaseController;
import com.panzhihua.common.listen.ComMngBuildingExcelListen;
import com.panzhihua.common.model.vos.R;
@@ -39,6 +53,23 @@

    @Resource
    private CommunityService communityService;
    @Resource
    private StringRedisTemplate stringRedisTemplate;

    // FTP 登录用户名
    @Value("${ftp.username}")
    private String userName;
    // FTP 登录密码
    @Value("${ftp.password}")
    private String password;
    // FTP 服务器地址IP地址
    @Value("${ftp.host}")
    private String host;
    // FTP 端口
    @Value("${ftp.port}")
    private int port;
    @Value("${excel.userurl}")
    private String excelUrl;

    @ApiOperation(value = "excel导入楼栋信息--注：以前的接口，不需要对接")
    @PostMapping(value = "/import", consumes = "multipart/*", headers = "content-type=multipart/form-date")
@@ -47,7 +78,7 @@
        try {
            inputStream = file.getInputStream();
            ComMngBuildingExcelListen comMngBuildingExcelListen =
                new ComMngBuildingExcelListen(communityService, this.getCommunityId());
                new ComMngBuildingExcelListen(communityService, this.getCommunityId(),stringRedisTemplate);
            EasyExcel.read(inputStream, null, comMngBuildingExcelListen).sheet().doRead();
        } catch (IOException e) {
            log.error("导入模板失败【{}】", e.getMessage());
@@ -172,4 +203,124 @@
        return communityService.deleteBuildingHousePopulationAdmin(housePopulationDto);
    }

    @ApiOperation(value = "下载导入楼栋模板")
    @GetMapping("/export/template")
    public R exportTemplate() {
        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 {
                    List<List<String>> list = headDrugDataFilling();
//                    List<List<Object>> dataList = new ArrayList<>();

                    EasyExcel.write(fileName).head(list).sheet("楼栋导入模板").doWrite(null);
                    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();
                    }
                }
            }
            return R.ok(excelUrl + "楼栋导入模板.xlsx");
        } catch (Exception e) {
            e.printStackTrace();
            log.error("文件传输失败【{}】", e.getMessage());
            return R.fail();
        }
    }

    /**
     * 楼栋导入模板拼接
     * @return  导入模板
     */
    private List<List<String>> headDrugDataFilling() {
        List<List<String>> list = new ArrayList<List<String>>();
        List<String> head2 = new ArrayList<String>();
        head2.add("*街路巷");
        List<String> head3 = new ArrayList<String>();
        head3.add("小区名称");
        List<String> head4 = new ArrayList<String>();
        head4.add("*街路巷号");
        List<String> head5 = new ArrayList<String>();
        head5.add("*楼栋号");
        list.add(head2);
        list.add(head3);
        list.add(head4);
        list.add(head5);
        return list;
    }

    @ApiOperation(value = "下载导入失败楼栋数据")
    @PostMapping("/download/error")
    public R downloadError(@RequestParam(value = "key") String key) {
        List<ComMngPopulationImportErrorVO> 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, ComMngPopulationImportErrorVO.class);
        }
        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, ComMngPopulationImportErrorVO.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/service/impl/ComActBuildingServiceImpl.java

@@ -1,6 +1,5 @@
package com.panzhihua.service_community.service.impl;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
@@ -27,7 +26,6 @@
import org.springframework.beans.BeanUtils;
import org.springframework.stereotype.Service;

import com.alibaba.fastjson.JSON;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.google.common.collect.Lists;
@@ -65,22 +63,24 @@
        ArrayList<ComMngBuildingDO> list = Lists.newArrayList();
        List<GridBuildRelationDO> keyList = new ArrayList<>();
        List<ComMngPopulationImportErrorVO> errorList = new ArrayList<>();
        ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
        importErrorVO.setErrorMsg("重复楼栋");
        StringBuilder sb = new StringBuilder();
        for (ComMngBuildingExcelVO vo : newVoList) {
            ComMngVillageDO comMngVillageDO = comActVillageDAO.selectOne(new QueryWrapper<ComMngVillageDO>()
                .eq("alley", vo.getAlley()).eq("house_num", vo.getDoorNo()).eq("community_id", communityId));
            if (comMngVillageDO == null) {
                ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
                BeanUtils.copyProperties(vo,importErrorVO);
                importErrorVO.setErrorMsg("该社区不存在此街路巷");
                errorList.add(importErrorVO);
                continue;
            }
            vo.setName(vo.getName().replace("栋", ""));
            List<ComMngBuildingDO> buildingDOList = this.baseMapper.selectList(new QueryWrapper<ComMngBuildingDO>()
                .eq("street_id", comMngVillageDO.getStreetId()).eq("act_id", communityId)
                .eq("village_id", comMngVillageDO.getVillageId()).eq("name", vo.getName()));
                .eq("act_id", communityId).eq("village_id", comMngVillageDO.getVillageId()).eq("name", vo.getName()));
            if (buildingDOList.size() > 0) {
                sb.append("街路巷:").append(vo.getAlley()).append(",门牌号:").append(vo.getDoorNo()).append(",楼栋号:")
                    .append(vo.getName()).append(";");
                ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
                BeanUtils.copyProperties(vo,importErrorVO);
                importErrorVO.setErrorMsg("该楼栋已存在");
                errorList.add(importErrorVO);
                continue;
            }
            ComMngBuildingDO comMngBuildingDO = new ComMngBuildingDO();
@@ -88,12 +88,13 @@
            comMngBuildingDO.setActId(comMngVillageDO.getCommunityId());
            comMngBuildingDO.setStreetId(comMngVillageDO.getStreetId());
            comMngBuildingDO.setVillageId(comMngVillageDO.getVillageId());
//            comMngBuildingDO.setElevatorTotal(vo.getElevatorTotal());
            ComActDO actDO = comActDAO.selectById(comMngVillageDO.getCommunityId());
            StringBuilder path = new StringBuilder();
            path.append(vo.getAlley()).append(">").append(actDO.getName()).append(comMngVillageDO.getName()).append(">")
                .append(vo.getName());
            comMngBuildingDO.setPath(path.toString());
            comMngBuildingDO.setGridId(vo.getGridId());
//            comMngBuildingDO.setGridId(vo.getGridId());
            list.add(comMngBuildingDO);
            GridBuildRelationDO gridBuildRelation =
                getGridBuildRelationDO(communityId, comMngVillageDO.getStreetId(), vo, comMngBuildingDO);
@@ -102,13 +103,13 @@

        if (list.size() > 0) {
            this.saveBatch(list);
            if(keyList.size() > 0){
                gridBuildRelationService.saveBatchGridBuildRelation(keyList);
            }
        }
        if (sb.length() > 0 && !StringUtils.isEmpty(sb.toString())) {
            errorList.add(importErrorVO);
            return R.fail(JSON.toJSONString(errorList));
        if (errorList.size() > 0) {
            return R.fail(errorList);
        }
        gridBuildRelationService.saveBatchGridBuildRelation(keyList);
        this.saveBatch(list);
        return R.ok();
    }

@@ -121,9 +122,9 @@
        gridBuildRelation.setVillageId(comMngBuildingDO.getVillageId());
        gridBuildRelation.setGridCommunityId(communityId);
        gridBuildRelation.setGridStreetId(streetId);
        if (vo.getGridId() != null) {
            gridBuildRelation.setGridId(Long.valueOf(vo.getGridId()));
        }
//        if (vo.getGridId() != null) {
//            gridBuildRelation.setGridId(Long.valueOf(vo.getGridId()));
//        }
        gridBuildRelation.setGridName(vo.getGridName());
        gridBuildRelation.setType(1);
        gridBuildRelation.setCreateAt(new Date());

 springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/service/impl/ComCvtServeServiceImpl.java

@@ -204,7 +204,7 @@
                    ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
                    importErrorVO.setErrorMsg(String.format("导入失败：商家【%s】下已有服务【%s】",
                        comCvtServeExcelVO.getBusinessName(), comCvtServeExcelVO.getServiceName()));
                    importErrorVO.setErrorPosition("第" + index + "行");
//                    importErrorVO.setErrorPosition("第" + index + "行");
                    populationImportErrorVOList.add(importErrorVO);
                    log.error(String.format("导入失败：商家【%s】下已有服务【%s】", comCvtServeExcelVO.getBusinessName(),
                        comCvtServeExcelVO.getServiceName()));
@@ -213,14 +213,14 @@
                if (!categoryMap.containsKey(comCvtServeExcelVO.getCategoryName())) {
                    ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
                    importErrorVO.setErrorMsg("导入失败: 服务分类" + comCvtServeExcelVO.getCategoryName() + "不存在");
                    importErrorVO.setErrorPosition("第" + index + "行，第1列");
//                    importErrorVO.setErrorPosition("第" + index + "行，第1列");
                    populationImportErrorVOList.add(importErrorVO);
                    // errorInfo.add(comCvtServeExcelVO.getCategoryName() +"导入失败: 服务分类不存在");
                }
                if (!businessMap.containsKey(comCvtServeExcelVO.getBusinessName())) {
                    ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
                    importErrorVO.setErrorMsg("导入失败: 便民服务商家" + comCvtServeExcelVO.getBusinessName() + "不存在");
                    importErrorVO.setErrorPosition("第" + index + "行，第5列");
//                    importErrorVO.setErrorPosition("第" + index + "行，第5列");
                    populationImportErrorVOList.add(importErrorVO);
                }
            }

 springcloud_k8s_panzhihuazhihuishequ/service_user/src/main/java/com/panzhihua/service_user/api/UserApi.java

@@ -1203,8 +1203,27 @@
        userService.judgeCommunityTeam(openid);
    }

    /**
     * 数据看板升级版
     *
     * @return 运营后台数据看板升级版
     */
    @PostMapping("datakanbans")
    public R dataKanBans(@RequestBody DataKanBansDto dataKanBansDto) {
        return userService.dataKanBans(dataKanBansDto);
    }

    /**
     * 运营后台-用户数据统计汇总
     * @return  用户数据统计汇总
     */
    @GetMapping("community/statistics")
    public R communityStatistics() {
        return userService.communityStatistics();
    }

    @GetMapping("community/statistics/export")
    public R communityStatisticsExport() {
        return userService.communityStatisticsExport();
    }
}

 springcloud_k8s_panzhihuazhihuishequ/service_user/src/main/java/com/panzhihua/service_user/dao/UserDao.java

@@ -4,6 +4,7 @@
import java.util.List;
import java.util.Map;

import com.panzhihua.common.model.vos.community.*;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.*;
@@ -24,8 +25,6 @@
import com.panzhihua.common.model.vos.IndexDataKanbanVO;
import com.panzhihua.common.model.vos.IndexDataVO;
import com.panzhihua.common.model.vos.LoginUserInfoVO;
import com.panzhihua.common.model.vos.community.ComActVO;
import com.panzhihua.common.model.vos.community.ComMngVolunteerMngVO;
import com.panzhihua.common.model.vos.community.screen.event.EventGridMemberVO;
import com.panzhihua.common.model.vos.grid.GridMemberVO;
import com.panzhihua.common.model.vos.partybuilding.ComPbServiceTeamWishVO;
@@ -192,4 +191,23 @@
     * @return  用户数量
     */
    Integer getCommunityTeamCount(@Param("phone") String phone);

    /**
     * 查询西区所有街道列表以及统计人数
     * @return  西区所有街道列表以及统计人数
     */
    List<IndexUserStreetVo> getUserStreetList();

    /**
     * 查询街道下社区列表以及统计人数
     * @param streetId  街道id
     * @return  社区列表以及统计人数
     */
    List<IndexUserCommunityVo> getUserCommunityList(@Param("streetId") Long streetId);

    /**
     * 查询街道下社区注册人数统计数据导出
     * @return  社区注册人数统计数据导出
     */
    List<IndexUserStreetExcelExportVo> getUserCommunityExcelExport();
}

 springcloud_k8s_panzhihuazhihuishequ/service_user/src/main/java/com/panzhihua/service_user/service/UserService.java

@@ -735,5 +735,18 @@
     */
    void judgeCommunityTeam(String openid);

    /**
     * 数据看板升级版
     *
     * @return 运营后台数据看板升级版
     */
    R dataKanBans(DataKanBansDto dataKanBansDto);

    /**
     * 运营后台-用户数据统计汇总
     * @return  用户数据统计汇总
     */
    R communityStatistics();

    R communityStatisticsExport();
}

 springcloud_k8s_panzhihuazhihuishequ/service_user/src/main/java/com/panzhihua/service_user/service/impl/UserServiceImpl.java

@@ -16,6 +16,7 @@

import cn.hutool.core.util.IdcardUtil;
import com.panzhihua.common.model.dtos.DataKanBansDto;
import com.panzhihua.common.model.vos.community.*;
import com.panzhihua.service_user.dao.*;
import com.panzhihua.service_user.model.dos.*;
import org.apache.commons.lang.time.DateFormatUtils;
@@ -58,8 +59,6 @@
import com.panzhihua.common.model.vos.LoginUserInfoVO;
import com.panzhihua.common.model.vos.R;
import com.panzhihua.common.model.vos.SystemmanagementConfigVO;
import com.panzhihua.common.model.vos.community.ComActVO;
import com.panzhihua.common.model.vos.community.ComMngVolunteerMngVO;
import com.panzhihua.common.model.vos.grid.GridMemberVO;
import com.panzhihua.common.model.vos.grid.admin.GridMemberBuildingVO;
import com.panzhihua.common.model.vos.partybuilding.ActivityManagerVO;
@@ -2677,6 +2676,11 @@
        }
    }

    /**
     * 数据看板升级版
     *
     * @return 运营后台数据看板升级版
     */
    @Override
    public R dataKanBans(DataKanBansDto dataKanBansDto){
        IndexDataKanbanVO indexDataKanbanVO = userDao.selectIndexDataKanban();
@@ -2700,7 +2704,7 @@
            });
        }

        // 获取最近七天数据
        // 获取最近二十天数据
        List<Date> serverndays = com.panzhihua.common.utlis.DateUtils.getOldDays();
        serverndays.forEach(date -> {
            // 查询社区活动数量
@@ -2716,4 +2720,27 @@
        indexDataKanbanVO.setCommunityUserY(communityUserY);
        return R.ok(indexDataKanbanVO);
    }

    /**
     * 运营后台-用户数据统计汇总
     * @return  用户数据统计汇总
     */
    @Override
    public R communityStatistics(){
        //返回结果数据
        IndexUserStatisticsVo userStatisticsVo = new IndexUserStatisticsVo();
        //查询所有街道
        List<IndexUserStreetVo> streetList = userDao.getUserStreetList();
        streetList.forEach(street -> {
            List<IndexUserCommunityVo> communityList = userDao.getUserCommunityList(street.getStreetId());
            street.setCommunityList(communityList);
        });
        userStatisticsVo.setStreetList(streetList);
        return R.ok(userStatisticsVo);
    }

    @Override
    public R communityStatisticsExport(){
        return R.ok(userDao.getUserCommunityExcelExport());
    }
}

 springcloud_k8s_panzhihuazhihuishequ/service_user/src/main/resources/mapper/UserDao.xml

@@ -456,4 +456,67 @@
        select count(id) from com_pb_service_team where is_reg = 2 and phone = #{phone}
    </select>

    <select id="getUserStreetList" resultType="com.panzhihua.common.model.vos.community.IndexUserStreetVo">
        SELECT
            street_id,
            `name`,(
            SELECT
                count( user_id )
            FROM
                sys_user AS su
                LEFT JOIN com_act AS ca ON ca.community_id = su.community_id
            WHERE
                type = 1
                AND ca.street_id = cs.street_id
            ) AS userNum
        FROM
            `com_street` AS cs
            order by userNum desc
    </select>

    <select id="getUserCommunityList" resultType="com.panzhihua.common.model.vos.community.IndexUserCommunityVo">
        SELECT
            ca.community_id,
            ca.`name`,
            count( user_id ) as userNum
        FROM
            sys_user as su
            left join com_act as ca on ca.community_id = su.community_id
        WHERE
            type = 1
            AND ca.street_id = #{streetId}
            group by ca.community_id
            order by userNum desc
    </select>

    <select id="getUserCommunityExcelExport" resultType="com.panzhihua.common.model.vos.community.IndexUserStreetExcelExportVo">
        SELECT
            cs.`name` AS streetName,
            ca.`name` AS communityName,
            (
            SELECT
                count( user_id )
            FROM
                sys_user AS su1
                LEFT JOIN com_act AS ca1 ON ca1.community_id = su1.community_id
            WHERE
                su1.type = 1
                AND su1.community_id IS NOT NULL
                AND ca1.street_id = ca.street_id
            ) AS streetNum,
            count( user_id ) AS userNum
        FROM
            sys_user AS su
            LEFT JOIN com_act AS ca ON ca.community_id = su.community_id
            LEFT JOIN com_street AS cs ON cs.street_id = ca.street_id
        WHERE
            su.type = 1
            AND su.community_id IS NOT NULL
        GROUP BY
            ca.community_id
        ORDER BY
            ca.street_id DESC,
            userNum DESC
    </select>

</mapper>