[新增]新增下载模板

manailin

2021-06-14 b1eb51c980efb4db207223ce86ddc459916dff7c

[新增]新增下载模板

 springcloud_k8s_panzhihuazhihuishequ/common/pom.xml

@@ -140,6 +140,17 @@
            <artifactId>reflectasm</artifactId>
            <version>1.11.9</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>4.1.0</version>
            <exclusions>
                <exclusion>
                    <groupId>com.google.guava</groupId>
                    <artifactId>guava</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
    </dependencies>



 springcloud_k8s_panzhihuazhihuishequ/common/src/main/java/com/panzhihua/common/model/dtos/community/ComMngPopulationTagCardNoDTO.java

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

/**
 *  实有人口DTO
 * @author manailin
 */
@Data
public class ComMngPopulationTagCardNoDTO implements Serializable {

 springcloud_k8s_panzhihuazhihuishequ/common/src/main/java/com/panzhihua/common/model/dtos/community/KeyPersonInfoDTO.java

@@ -26,7 +26,7 @@
    /**
     * 姓名
     */
    @ExcelProperty(value = "姓名" ,index = 2)
    @ExcelProperty(value = "姓名(必填项)" ,index = 0)
    private String name;
    /**
     * 性别(取字典表国家编码)
@@ -35,66 +35,67 @@
    /**
     * 性别名称
     */
    @ExcelProperty(value = "性别名称" ,index = 3)
    @ExcelProperty(value = "性别名称" ,index = 1)
    private String sexName;
    /**
     * 身份证号码
     */
    @ExcelProperty(value = "身份证号码" ,index = 4)
    @ExcelProperty(value = "身份证号码" ,index = 2)
    private String cardNo;
    /**
     * 联系方式
     */
    @ExcelProperty(value = "联系方式" ,index = 5)
    @ExcelProperty(value = "联系方式" ,index = 3)
    private String phone;
    /**
     * 民族
     */
    @ExcelProperty(value = "民族" ,index = 6)
    @ExcelProperty(value = "民族" ,index = 4)
    private String nation;
    /**
     * 户口所在地
     */
    @ExcelProperty(value = "户口所在地" ,index = 7)
    @ExcelProperty(value = "户口所在地" ,index = 5)
    private String censusRegister;
    /**
     * 居住地址
     */
    @ExcelProperty(value = "居住地址" ,index = 8)
    @ExcelProperty(value = "居住地址" ,index = 6)
    private String address;
    /**
     * 地址精度纬度信息（逗号隔开）
     */
    @ExcelProperty(value = "地址精度纬度信息（逗号隔开）" ,index = 9)
    @ExcelProperty(value = "地址精度纬度信息（逗号隔开）" ,index = 7)
    private String visiterAddressLatLng;
    /**
     * 人员类型1吸毒2严重精神障碍3刑释人员4社区矫正人员
     */
    @ExcelProperty(value = "人员类型1吸毒2严重精神障碍3刑释人员4社区矫正人员" ,index = 10)
    private Integer visiterType;
    @ExcelProperty(value = "人员类型1吸毒2严重精神障碍3刑释人员4社区矫正人员" ,index = 8)
    private String visiterType;
    /**
     * (法轮功类填写)基本情况
     */
    @ExcelProperty(value = "(法轮功类填写)基本情况" ,index = 9)
    private String basicInfo;
    /**
     * 是否有效
     */
    @ExcelProperty(value = "是否有效" ,index = 11)
    @ExcelProperty(value = "是否有效" ,index = 10)
    private Boolean invalid;
    /**
     * 备注
     */
    @ExcelProperty(value = "备注" ,index = 12)
    @ExcelProperty(value = "备注" ,index = 11)
    private String note;
    /**
     * 文化程度(取字典表国家编码))
     */
    @ExcelProperty(value = "文化程度" ,index = 13)
    @ExcelProperty(value = "文化程度" ,index = 12)
    private String cultureLevel;
    /**
     * 经济状态
     */
    @ExcelProperty(value = "经济状态" ,index = 14)
    @ExcelProperty(value = "经济状态" ,index = 13)
    private String economicLevel;
    /**
     * 修改人
@@ -111,7 +112,7 @@
    /**
     * （精神类人群填写）目前诊断
     */
    @ExcelProperty(value = "目前诊断" ,index = 18)
    @ExcelProperty(value = "目前诊断" ,index = 14)
    private String diagnose;
    /**
     * 上报提交时间
@@ -120,17 +121,17 @@
    /**
     * （精神类人群填写）监护人名称
     */
    @ExcelProperty(value = "监护人名称" ,index = 20)
    @ExcelProperty(value = "监护人名称" ,index = 15)
    private String guardian;
    /**
     * （精神类人群填写）监护人电话
     */
    @ExcelProperty(value = "监护人电话" ,index = 21)
    @ExcelProperty(value = "监护人电话" ,index = 16)
    private String guardianPhone;
    /**
     * （精神类人群填写）监护人关系
     */
    @ExcelProperty(value = "监护人关系" ,index = 22)
    @ExcelProperty(value = "监护人关系" ,index = 17)
    private String guardianRelation;
    /**
     * 创建时间

 springcloud_k8s_panzhihuazhihuishequ/common/src/main/java/com/panzhihua/common/service/community/CommunityService.java

@@ -17,10 +17,7 @@
import com.panzhihua.common.model.dtos.shop.PageComShopStoreDTO;
import com.panzhihua.common.model.dtos.user.PageInputUserDTO;
import com.panzhihua.common.model.dtos.vaccines.*;
import com.panzhihua.common.model.vos.BcDictionaryVO;
import com.panzhihua.common.model.vos.IPageVO;
import com.panzhihua.common.model.vos.R;
import com.panzhihua.common.model.vos.SystemmanagementConfigVO;
import com.panzhihua.common.model.vos.*;
import com.panzhihua.common.model.vos.community.*;
import com.panzhihua.common.model.vos.community.questnaire.EditComActQuestnaireVo;
import com.panzhihua.common.model.vos.community.questnaire.QuestnaireVO;
@@ -34,6 +31,7 @@
import com.panzhihua.common.model.vos.shop.ShopStoreVO;
import com.panzhihua.common.model.vos.user.UserElectronicFileVO;
import com.panzhihua.common.model.vos.user.UserPhoneVO;
import com.panzhihua.common.utlis.ExcelSelectObject;
import org.springframework.cloud.openfeign.FeignClient;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.*;
@@ -2745,7 +2743,7 @@
     */
    @GetMapping("/dictionary/listDictionaryByKey")
    R<List<BcDictionaryVO>> listDictionaryByKey(@RequestParam("key") String key);
    R listSaveBuildingExcelVO(@RequestBody List<ComMngBuildingExcelVO> newVoList,@RequestParam("communityId") Long communityId,@RequestParam("streetId")Long streetId);
  //  R listSaveBuildingExcelVO(@RequestBody List<ComMngBuildingExcelVO> newVoList,@RequestParam("communityId") Long communityId,@RequestParam("streetId")Long streetId);

    /**
     * description 批量保存重点人群人员信息
@@ -2757,4 +2755,5 @@
     */
    @PostMapping("/key_person/batch/save")
    R saveBatchKeyPerson(@RequestBody List<KeyPersonInfoDTO> list);

}

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

New file
@@ -0,0 +1,67 @@
package com.panzhihua.common.utlis;

import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.ss.usermodel.Name;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddressList;

public final class ExcelSelectListUtil {
    private ExcelSelectListUtil() {
        throw new IllegalStateException("Utility class");
    }
    /**
     * firstRow 開始行號 根据此项目，默认为2(下标0开始)
     * lastRow  根据此项目，默认为最大65535
     * firstCol 区域中第一个单元格的列号 (下标0开始)
     * lastCol 区域中最后一个单元格的列号
     * strings 下拉内容
     * */
    public static void selectList(Workbook workbook, int firstCol, int lastCol, String[] strings){
        int charLenth = stringArrayLength(strings);
        int arraylLenth=strings.length;
        if(charLenth+arraylLenth>=255){
        Sheet sheet = workbook.getSheetAt(0);
        //解决下拉超过255个字符的问题
        String hiddenSheet="hidden"+firstCol;
        Sheet category1Hidden = workbook.createSheet(hiddenSheet);
        for (int i = 0, length = strings.length; i < length; i++) {
            // 循环赋值（为了防止下拉框的行数与隐藏域的行数相对应来获取>=选中行数的数组，将隐藏域加到结束行之后）
            category1Hidden.createRow(i).createCell(0).setCellValue(strings[i]);
        }
        Name category1Name = workbook.createName();
        category1Name.setNameName(hiddenSheet);
        category1Name.setRefersToFormula(hiddenSheet+"!$A$1:$A$" + strings.length);
        // A1:A代表隐藏域创建第?列createCell(?)时。以A1列开始A行数据获取下拉数组
        //  生成下拉列表
        //  只对(x，x)单元格有效
        CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(2, 65535, firstCol, lastCol);
        //  生成下拉框内容
        DVConstraint dvConstraint = DVConstraint.createFormulaListConstraint(hiddenSheet);
        HSSFDataValidation dataValidation = new HSSFDataValidation(cellRangeAddressList, dvConstraint);
        //  对sheet页生效
        workbook.setSheetHidden(1,true);
        sheet.addValidationData(dataValidation);
        }else{
            Sheet sheet = workbook.getSheetAt(0);
            //  生成下拉列表
            //  只对(x，x)单元格有效
            CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(2, 65535, firstCol, lastCol);
            //  生成下拉框内容
            DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(strings);
            HSSFDataValidation dataValidation = new HSSFDataValidation(cellRangeAddressList, dvConstraint);
            //  对sheet页生效
            sheet.addValidationData(dataValidation);
        }
    }

    private static int stringArrayLength(String[] args){
        int rs=0;
        for( int i=0 ; i<args.length ; i++ ){
            rs+=args[i].length();
        }
        return rs;
    }
}


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

New file
@@ -0,0 +1,32 @@
package com.panzhihua.common.utlis;

import lombok.Data;

/**
 * description  ExcelSelectObject Excel 下拉类别传递对象
 *
 * @author manailin
 * @date 2019/7/10 10:03
 */
@Data
public class ExcelSelectObject {

     /**
      * firstCol 区域中第一个单元格的列号 (下标0开始)
      */
    private int firstCol;
    /**
     * lastCol 区域中最后一个单元格的列号
     */
    private int lastCol;
    /**
     * strings 下拉内容
     */
    private String[] strings;

    public ExcelSelectObject(final int firstCol, final int lastCol, final String[] strings) {
        this.firstCol = firstCol;
        this.lastCol = lastCol;
        this.strings = strings;
    }
}

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

New file
@@ -0,0 +1,155 @@
package com.panzhihua.common.utlis;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.Collections;
import java.util.List;
import java.util.Map;

/**
 * Excel工具类
 *
 * @author manailin
 * @date 2019/1/6
 */
@Slf4j
public class ExcelUtil {
    private ExcelUtil() {
        throw new IllegalStateException("Utility class");
    }

    /**
     * 导出
     *
     * @param list           数据列表
     * @param title          标题
     * @param sheetName      sheet名称
     * @param pojoClass      元素类型
     * @param fileName       文件名
     * @param isCreateHeader 是否创建列头
     * @param response
     * @throws IOException
     */
    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName
            , boolean isCreateHeader, HttpServletResponse response) throws IOException {
        ExportParams exportParams = new ExportParams(title, sheetName);
        exportParams.setCreateHeadRows(isCreateHeader);
        defaultExport(list, pojoClass, fileName, response, exportParams);

    }


    /**
     * 导出
     *
     * @param list      数据列表
     * @param title     标题
     * @param sheetName sheet名称
     * @param pojoClass 元素类型
     * @param fileName  文件名
     * @param response
     * @throws IOException
     */
    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName
            , HttpServletResponse response) throws IOException {
        defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName));
    }

    /**
     * 导出excel 带下拉列表的
     *
     * @param list      数据列表
     * @param title     标题
     * @param sheetName sheet名称
     * @param pojoClass 元素类型
     * @param fileName  文件名
     * @param response
     * @throws IOException
     */
    public static void exportExcelForSelectList(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName
            , List<ExcelSelectObject> selectListObject, HttpServletResponse response) throws IOException {
        Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams(title, sheetName), pojoClass, list);
        //这里是自己加的 带下拉框的代码
        selectListObject.forEach(selectList ->
            ExcelSelectListUtil.selectList(workbook, selectList.getFirstCol(), selectList.getLastCol(),selectList.getStrings())
        );
        for(int i=2;i<selectListObject.size();i++) {
            Sheet sheet = workbook.getSheetAt(0);
            Row row = sheet.createRow(i);
            CellStyle style = workbook.createCellStyle();
            DataFormat format = workbook.createDataFormat();
            style.setDataFormat(format.getFormat("@"));
            row.setRowStyle(style);
        }
        if (workbook != null) {
            downLoadExcel(fileName, response, workbook);
        }
    }
    /**
     * 导出
     *
     * @param list     数据列表(元素是Map)
     * @param fileName 文件名
     * @param response
     * @throws IOException
     */
    public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response) throws IOException {
        defaultExport(list, fileName, response);
    }

    private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName
            , HttpServletResponse response, ExportParams exportParams) throws IOException {
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
        if (workbook != null) {
            downLoadExcel(fileName, response, workbook);
        }
    }

    private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) throws IOException {
        Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.XSSF);
        if (workbook != null) {
            downLoadExcel(fileName, response, workbook);
        }
    }

    private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) throws IOException {
        response.setCharacterEncoding("UTF-8");
        response.setHeader("content-Type", "application/vnd.ms-excel");
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
        workbook.write(response.getOutputStream());
    }

    public static <T> List<T> importExcel(String filePath, Integer titleRows, Integer headerRows, Class<T> pojoClass) {
        if (StringUtils.isBlank(filePath)) {
            return Collections.emptyList();
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        return ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
    }

    public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass) throws Exception {
        if (file == null) {
            return Collections.emptyList();
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        return ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
    }


}

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

@@ -6,20 +6,24 @@
import com.panzhihua.common.model.vos.LoginUserInfoVO;
import com.panzhihua.common.model.vos.R;
import com.panzhihua.common.service.community.CommunityService;
import com.panzhihua.common.service.user.UserService;
import com.panzhihua.common.utlis.ExcelSelectObject;
import com.panzhihua.common.utlis.ExcelUtil;
import com.panzhihua.community_backstage.biz.KeyPersonBizServiceImpl;
import com.panzhihua.community_backstage.listen.KeyPersonInfoExcelListen;
import com.panzhihua.community_backstage.model.dto.KeyPersonExcelExportDto;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import lombok.extern.slf4j.Slf4j;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.util.Collections;
import java.util.List;

/**
 * program 攀枝花智慧社区项目
@@ -38,7 +42,7 @@
    private CommunityService communityService;

    @Resource
    private UserService userService;
    private KeyPersonBizServiceImpl keyPersonBizService;

    /**
     * description 批量保存重点人群人员信息
@@ -50,15 +54,12 @@
     */
    @PostMapping(value = "mainMember/import", consumes = "multipart/*", headers = "content-type=multipart/form-date")
    public R importExcl(@RequestParam MultipartFile file, HttpServletRequest request) {
        String fileName = file.getOriginalFilename();  //获取文件名
        //获取文件名
        String fileName = file.getOriginalFilename();
        log.info("传入文件名字【{}】", fileName);
        InputStream inputStream = null;
        try {
            inputStream = file.getInputStream();
            // LoginUserInfoVO loginUserInfo = this.getLoginUserInfo();
            LoginUserInfoVO loginUserInfo = new LoginUserInfoVO();
            loginUserInfo.setUserId(2323L);
            loginUserInfo.setCommunityId(888L);
            InputStream inputStream = file.getInputStream();
            LoginUserInfoVO loginUserInfo = this.getLoginUserInfo();
            EasyExcel.read(inputStream, KeyPersonInfoDTO.class, new KeyPersonInfoExcelListen(communityService, loginUserInfo)).sheet().doRead();
        } catch (IOException e) {
            e.printStackTrace();
@@ -67,4 +68,18 @@
        return R.ok();
    }

    /**
     * description exportUser 导出用于导入的模板EXCEL
     *
     * @author manailin
     * @date 2021/6/10 17:00
     */
    @ApiOperation(value = "导出用于导入的模板EXCEL", notes = "导出用于导入的模板EXCEL")
    @GetMapping("/export/model")
    public void exportUser(HttpServletResponse response) throws IOException {
        List<ExcelSelectObject> selectListObject = keyPersonBizService.defineExcelModelData();
        //导出操作
        ExcelUtil.exportExcelForSelectList(Collections.emptyList(), "特殊人员信息导入模版", "特殊人员信息导入模版", KeyPersonExcelExportDto.class, "特殊人员信息导入模版.xls", selectListObject, response);
    }

}

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

@@ -96,10 +96,7 @@
        InputStream inputStream = null;
        try {
            inputStream = file.getInputStream();
           // LoginUserInfoVO loginUserInfo = this.getLoginUserInfo();
            LoginUserInfoVO loginUserInfo = new LoginUserInfoVO();
            loginUserInfo.setUserId(2323L);
            loginUserInfo.setCommunityId(2L);
            LoginUserInfoVO loginUserInfo = this.getLoginUserInfo();
            ComMngVillageServeExcelListen comMngVillageServeExcelListen = new ComMngVillageServeExcelListen(communityService, loginUserInfo);
            EasyExcel.read(inputStream, ComMngVillageServeExcelVO.class, comMngVillageServeExcelListen).sheet().doRead();
        } catch (IOException e) {

 springcloud_k8s_panzhihuazhihuishequ/community_backstage/src/main/java/com/panzhihua/community_backstage/biz/KeyPersonBizServiceImpl.java

New file
@@ -0,0 +1,61 @@
package com.panzhihua.community_backstage.biz;

import com.panzhihua.common.model.vos.BcDictionaryVO;
import com.panzhihua.common.service.community.CommunityService;
import com.panzhihua.common.utlis.ExcelSelectObject;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;

/**
 * program 攀枝花智慧社区项目
 * description 特殊人群导入模板处理service
 *
 * @author manailin
 * Date 2021-06-14 15:30
 **/
@Slf4j
@Service
public class KeyPersonBizServiceImpl {
    /**
     * 初始化全局处理类
     */
    private static final String split = ":";

    @Resource
    private CommunityService communityService;


    /**
     * description exportUser 定义导出特殊人群下拉选项值
     *
     * @return List<ExcelSelectObject>   下拉选项值
     * @author manailin
     * @date 2021/6/10 17:00
     */
    public List<ExcelSelectObject> defineExcelModelData() {
        HashMap map = new HashMap(5);
        String[] invalid = {"0", "1"};
        String[] economicLevel = {"贫困", "非贫困"};
        List<String> dictNames = Arrays.asList("sex", "family", "education", "nation", "key_person_type");
        dictNames.forEach(dictName -> {
            List<BcDictionaryVO> dictionaryItem = communityService.listDictionaryByKey(dictName).getData();
            String[] dataMap = dictionaryItem.stream().map(dictionaryVO -> dictionaryVO.getDictName() + split + dictionaryVO.getDictValue()).distinct().toArray(String[]::new);
            map.put(dictName, dataMap);
        });
        List<ExcelSelectObject> excelSelectObjectList = new ArrayList<>();
        excelSelectObjectList.add(new ExcelSelectObject(1, 1, (String[]) map.get("sex")));
        excelSelectObjectList.add(new ExcelSelectObject(4, 4, (String[]) map.get("nation")));
        excelSelectObjectList.add(new ExcelSelectObject(8, 8, (String[]) map.get("key_person_type")));
        excelSelectObjectList.add(new ExcelSelectObject(10, 10, invalid));
        excelSelectObjectList.add(new ExcelSelectObject(12, 12, (String[]) map.get("education")));
        excelSelectObjectList.add(new ExcelSelectObject(13, 13, economicLevel));
        excelSelectObjectList.add(new ExcelSelectObject(17, 17, (String[]) map.get("family")));
        return excelSelectObjectList;
    }
}

 springcloud_k8s_panzhihuazhihuishequ/community_backstage/src/main/java/com/panzhihua/community_backstage/listen/KeyPersonInfoExcelListen.java

@@ -3,7 +3,9 @@
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSONObject;
import com.panzhihua.common.constants.Constants;
import com.panzhihua.common.exceptions.PartyBuildingMemberException;
import com.panzhihua.common.exceptions.ServiceException;
import com.panzhihua.common.model.dtos.community.KeyPersonInfoDTO;
import com.panzhihua.common.model.vos.LoginUserInfoVO;
import com.panzhihua.common.model.vos.R;
@@ -17,10 +19,11 @@
import java.util.List;

/**
 * @program: springcloud_k8s_panzhihuazhihuishequ
 * @description: 特殊人群入监听
 * @author: huang.hongfa weixin hhf9596 qq 959656820
 * @create: 2020-11-30 15:12
 * program 攀枝花智慧社区项目
 * description 特殊人群入监听
 *
 * @author manailin
 * Date 2021-01-22 15:30
 **/
@Slf4j
public class KeyPersonInfoExcelListen extends AnalysisEventListener<KeyPersonInfoDTO> {
@@ -42,39 +45,64 @@
    @Override
    public void invoke(KeyPersonInfoDTO keyPersonInfoDTO, AnalysisContext analysisContext) {
        list.add(keyPersonInfoDTO);
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (list.size() >= BATCH_COUNT) {
            log.info("excel导入数据【{}】", JSONObject.toJSONString(list));
            list.forEach(keyPersonInfo -> {
                keyPersonInfo.setId(Snowflake.getId());
                keyPersonInfo.setActId(loginUserInfo.getCommunityId());
                keyPersonInfo.setCreateBy(loginUserInfo.getUserId());
                keyPersonInfo.setCreateAt(new Date());
                keyPersonInfo.setUpdateBy(loginUserInfo.getUserId());
                keyPersonInfo.setUpdateAt(new Date());
                buildKeyPersonInfo(keyPersonInfo);
            });
            R r = this.communityService.saveBatchKeyPerson(list);
            if (!R.isOk(r)) {
                throw new PartyBuildingMemberException(r.getMsg());
            }
            list.clear();           //清空list
            list.clear();
        }
    }

    private void buildKeyPersonInfo(KeyPersonInfoDTO keyPersonInfo) {
        if (keyPersonInfo.getSexName() != null) {
            keyPersonInfo.setSex(splitData(keyPersonInfo.getSexName(),1));
            keyPersonInfo.setSexName(splitData(keyPersonInfo.getSexName(),0));
        }
        if (keyPersonInfo.getNation() != null) {
            keyPersonInfo.setNation(splitData(keyPersonInfo.getNation(),1));
        }
        if (keyPersonInfo.getVisiterType() != null) {
            keyPersonInfo.setVisiterType(splitData(keyPersonInfo.getVisiterType(),1));
        }
        if (keyPersonInfo.getCultureLevel() != null) {
            keyPersonInfo.setCultureLevel(splitData(keyPersonInfo.getCultureLevel(),1));
        }
        if (keyPersonInfo.getGuardianRelation() != null) {
            keyPersonInfo.setGuardianRelation(splitData(keyPersonInfo.getGuardianRelation(),1));
        }
        keyPersonInfo.setId(Snowflake.getId());
        keyPersonInfo.setActId(loginUserInfo.getCommunityId());
        keyPersonInfo.setCreateBy(loginUserInfo.getUserId());
        keyPersonInfo.setUpdateBy(loginUserInfo.getUserId());

    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        log.info("excel导入数据【{}】", JSONObject.toJSONString(list));
        list.forEach(keyPersonInfo -> {
            keyPersonInfo.setId(Snowflake.getId());
            keyPersonInfo.setActId(loginUserInfo.getCommunityId());
            keyPersonInfo.setCreateBy(loginUserInfo.getUserId());
            keyPersonInfo.setCreateAt(new Date());
            keyPersonInfo.setUpdateBy(loginUserInfo.getUserId());
            keyPersonInfo.setUpdateAt(new Date());
            buildKeyPersonInfo(keyPersonInfo);
        });
        R r = this.communityService.saveBatchKeyPerson(list);//确保最后遗留的数据保存在数据库中
        R r = this.communityService.saveBatchKeyPerson(list);
        if (!R.isOk(r)) {
            throw new PartyBuildingMemberException(r.getMsg());
        }
    }

    private String splitData(String key,int position) {
        try {
            if (!key.isEmpty()) {
                return key.split(":")[position];
            } else {
                return null;
            }
        } catch (NumberFormatException e) {
            throw new ServiceException("导入数据格式错误！", "请不要更改下拉选项的值");
        }
    }
}

 springcloud_k8s_panzhihuazhihuishequ/community_backstage/src/main/java/com/panzhihua/community_backstage/model/dto/KeyPersonExcelExportDto.java

New file
@@ -0,0 +1,125 @@
package com.panzhihua.community_backstage.model.dto;

import cn.afterturn.easypoi.excel.annotation.Excel;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;

import java.util.Date;
/**
 * program 攀枝花智慧社区项目
 * description 特殊人群导出excel模板对象
 *
 * @author manailin
 * Date 2021-6-13 15:30
 **/
@Data
public class KeyPersonExcelExportDto {

    /**
     * 姓名
     */
    @ExcelProperty(value = "姓名" ,index = 2)
    @Excel(name = "姓名(必填项)", width = 20, isImportField = "true_st")
    private String name;
    /**
     * 性别名称
     */
    @ExcelProperty(value = "性别名称" ,index = 3)
    @Excel(name = "性别名称(必填项)", width = 10, isImportField = "true_st")
    private String sexName;
    /**
     * 身份证号码
     */
    @ExcelProperty(value = "身份证号码" ,index = 4)
    @Excel(name = "身份证号码(必填项)", width = 30, isImportField = "true_st")
    private String cardNo;
    /**
     * 联系方式
     */
    @ExcelProperty(value = "联系方式" ,index = 5)
    @Excel(name = "联系方式(必填项)", width = 30, isImportField = "true_st")
    private String phone;
    /**
     * 民族
     */
    @ExcelProperty(value = "民族" ,index = 6)
    @Excel(name = "民族(必填项)", width = 20, isImportField = "true_st")
    private String nation;
    /**
     * 户口所在地
     */
    @ExcelProperty(value = "户口所在地" ,index = 7)
    @Excel(name = "户口所在地(必填项)", width = 30, isImportField = "true_st")
    private String censusRegister;
    /**
     * 居住地址
     */
    @ExcelProperty(value = "居住地址" ,index = 8)
    @Excel(name = "居住地址(必填项)", width = 30, isImportField = "true_st")
    private String address;
    /**
     * 地址精度纬度信息（逗号隔开）
     */
    @ExcelProperty(value = "地址精度纬度信息（逗号隔开）" ,index = 9)
    @Excel(name = "地址精度纬度信息(必填项)", width = 30, isImportField = "true_st")
    private String visiterAddressLatLng;
    /**
     * 人员类型1吸毒2严重精神障碍3刑释人员4社区矫正人员
     */
    @ExcelProperty(value = "人员类型1吸毒2严重精神障碍3刑释人员4社区矫正人员" ,index = 10)
    @Excel(name = "人员类型(必填项)", width = 10, isImportField = "true_st")
    private Integer visiterType;
    /**
     * (法轮功类填写)基本情况
     */
    @Excel(name = "(法轮功类填写)基本情况(必填项)", width = 30, isImportField = "true_st")
    private String basicInfo;
    /**
     * 是否有效
     */
    @ExcelProperty(value = "是否有效" ,index = 11)
    @Excel(name = "是否有效(必填项)1是有效，0是无效", width = 10, isImportField = "true_st")
    private Boolean invalid;
    /**
     * 备注
     */
    @ExcelProperty(value = "备注" ,index = 12)
    @Excel(name = "备注(必填项)", width = 30, isImportField = "true_st")
    private String note;
    /**
     * 文化程度(取字典表国家编码))
     */
    @ExcelProperty(value = "文化程度" ,index = 13)
    @Excel(name = "文化程度(必填项)", width = 20, isImportField = "true_st")
    private String cultureLevel;
    /**
     * 经济状态
     */
    @ExcelProperty(value = "经济状态" ,index = 14)
    @Excel(name = "经济状态(必填项)", width = 20, isImportField = "true_st")
    private String economicLevel;
    /**
     * （精神类人群填写）目前诊断
     */
    @ExcelProperty(value = "目前诊断" ,index = 18)
    @Excel(name = "目前诊断(必填项)", width = 30, isImportField = "true_st")
    private String diagnose;
    /**
     * （精神类人群填写）监护人名称
     */
    @ExcelProperty(value = "监护人名称" ,index = 20)
    @Excel(name = "监护人名称(必填项)", width = 30, isImportField = "true_st")
    private String guardian;
    /**
     * （精神类人群填写）监护人电话
     */
    @ExcelProperty(value = "监护人电话" ,index = 21)
    @Excel(name = "监护人电话(必填项)", width = 30, isImportField = "true_st")
    private String guardianPhone;
    /**
     * （精神类人群填写）监护人关系
     */
    @ExcelProperty(value = "监护人关系" ,index = 22)
    @Excel(name = "监护人关系(必填项)", width = 20, isImportField = "true_st")
    private String guardianRelation;
}

 springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/model/dos/KeyPersonInfoDO.java

@@ -1,9 +1,7 @@
package com.panzhihua.service_community.model.dos;

import com.alibaba.excel.annotation.ExcelProperty;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import com.baomidou.mybatisplus.annotation.*;
import com.panzhihua.common.model.helper.encrypt.EncryptDecryptField;
import lombok.Data;

import java.io.Serializable;
@@ -22,7 +20,7 @@
    /**
     * 主键
     */
    @TableId(value = "id",type = IdType.INPUT)
    @TableId(value = "id", type = IdType.INPUT)
    private Long id;
    /**
     * 街道id
@@ -43,10 +41,12 @@
    /**
     * 身份证号码
     */
    @EncryptDecryptField
    private String cardNo;
    /**
     * 联系方式
     */
    @EncryptDecryptField
    private String phone;
    /**
     * 民族
@@ -55,10 +55,12 @@
    /**
     * 户口所在地
     */
    @EncryptDecryptField
    private String censusRegister;
    /**
     * 居住地址
     */
    @EncryptDecryptField
    private String address;
    /**
     * 地址精度纬度信息（逗号隔开）
@@ -71,6 +73,7 @@
    /**
     * (法轮功类填写)基本情况
     */
    @EncryptDecryptField
    private String basicInfo;
    /**
     * 是否有效
@@ -79,6 +82,7 @@
    /**
     * 备注
     */
    @EncryptDecryptField
    private String note;
    /**
     * 文化程度(取字典表国家编码))
@@ -95,6 +99,7 @@
    /**
     * 修改时间
     */
    @TableField(fill = FieldFill.INSERT_UPDATE)
    private Date updateAt;
    /**
     * 创建人
@@ -103,10 +108,12 @@
    /**
     * （精神类人群填写）目前诊断
     */
    @EncryptDecryptField
    private String diagnose;
    /**
     * 上报提交时间
     */
    @TableField(fill = FieldFill.INSERT)
    private Date submitDate;
    /**
     * （精神类人群填写）监护人名称
@@ -123,6 +130,7 @@
    /**
     * 创建时间
     */
    @TableField(fill = FieldFill.INSERT_UPDATE)
    private Date createAt;
    /**
     * 社区id

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

@@ -5,16 +5,25 @@
import com.panzhihua.common.enums.KeyPersonLabelEnum;
import com.panzhihua.common.model.dtos.community.ComMngPopulationTagCardNoDTO;
import com.panzhihua.common.model.dtos.community.KeyPersonInfoDTO;
import com.panzhihua.common.model.helper.AESUtil;
import com.panzhihua.common.model.vos.R;
import com.panzhihua.service_community.dao.KeyPersonInfoDAO;
import com.panzhihua.service_community.model.dos.ComMngPopulationDO;
import com.panzhihua.service_community.model.dos.KeyPersonInfoDO;
import com.panzhihua.service_community.service.ComMngPopulationService;
import com.panzhihua.service_community.service.KeyPersonInfoService;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import javax.crypto.BadPaddingException;
import javax.crypto.IllegalBlockSizeException;
import javax.crypto.NoSuchPaddingException;
import java.io.UnsupportedEncodingException;
import java.security.InvalidKeyException;
import java.security.NoSuchAlgorithmException;
import java.util.List;

/**
@@ -30,6 +39,8 @@
    @Resource
    private ComMngPopulationService comMngPopulationService;

    @Value("${domain.aesKey:}")
    private String aesKey;
    /**
     * description 批量保存重点人群人员信息
     * 处理逻辑：同一个人的信息可以被多个社区重复录入
@@ -54,10 +65,22 @@
                comMngPopulationTagCardNoDTO.setCardNo(dbKeyPerson.getCardNo());
                comMngPopulationTagCardNoDTO.setLabel(calculateLabel(dbKeyPerson));
                comMngPopulationService.editTagPopulationByCardNo(comMngPopulationTagCardNoDTO);
                ComMngPopulationDO population=comMngPopulationService.getPopulationByCardNo(keyPersonInfoDO.getCardNo());
                if(population !=null){
                    keyPersonInfoDO.setUserId(population.getId());
                }
                keyPersonInfoDO.setVisiterType(Integer.parseInt(keyPersonInfoDTO.getVisiterType()));
                keyPersonInfoDO.setInvalid(false);
                updateById(keyPersonInfoDO);
            } else {
                KeyPersonInfoDO keyPersonInfoDO = new KeyPersonInfoDO();
                BeanUtils.copyProperties(keyPersonInfoDTO, keyPersonInfoDO);
                ComMngPopulationDO population=comMngPopulationService.getPopulationByCardNo(keyPersonInfoDO.getCardNo());
                if(population !=null){
                    keyPersonInfoDO.setUserId(population.getId());
                }
                keyPersonInfoDO.setVisiterType(Integer.parseInt(keyPersonInfoDTO.getVisiterType()));
                keyPersonInfoDO.setInvalid(false);
                save(keyPersonInfoDO);
            }
        });
@@ -82,11 +105,17 @@
    }

    private KeyPersonInfoDO checkExistFromDb(KeyPersonInfoDTO keyPersonInfoDTO) {
        return baseMapper.selectOne(
                new QueryWrapper<KeyPersonInfoDO>()
                        .eq("act_id", keyPersonInfoDTO.getActId())
                        .eq("card_no", keyPersonInfoDTO.getCardNo())
                        .eq("visiter_type", keyPersonInfoDTO.getVisiterType())
        );
        try {
            String aesCardNo = AESUtil.encrypt128(keyPersonInfoDTO.getCardNo(), aesKey);
            return baseMapper.selectOne(
                    new QueryWrapper<KeyPersonInfoDO>()
                            .eq("act_id", keyPersonInfoDTO.getActId())
                            .eq("card_no",aesCardNo)
                            .eq("visiter_type", keyPersonInfoDTO.getVisiterType())
            );
        } catch (Exception e) {
            log.error("根据身份证查询特殊人群业务-加密证件信息出错");
        }
        return null;
    }
}