cic-data-reportin/src/main/java/com/bootdo/datas/service/impl/GyDataServiceImpl.java

package com.bootdo.datas.service.impl;

import com.alibaba.fastjson.JSONArray;
import com.bootdo.common.config.BootdoConfig;
import com.bootdo.common.utils.*;
import com.bootdo.datas.domain.ExpertOpinionDO;
import com.bootdo.datas.dao.GyDataDao;
import com.bootdo.datas.dao.GyUnitDao;
import com.bootdo.datas.domain.*;
import com.bootdo.datas.dto.ExamineLogDTO;
import com.bootdo.datas.dto.GyDataImportDTO;
import com.bootdo.datas.service.*;
import com.bootdo.datas.tools.ExcelUtils;
import com.bootdo.datas.tools.gm.FileCryptTool;
import com.bootdo.system.domain.RoleDO;
import com.bootdo.system.domain.UserDO;
import com.bootdo.system.service.RoleService;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.CollectionUtils;
import org.springframework.util.ObjectUtils;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.*;
import java.util.function.Function;
import java.util.stream.Collectors;

@Service
@Transactional
public class GyDataServiceImpl implements GyDataService {

    @Autowired
    private BootdoConfig bootdoConfig;
    @Autowired
    private GyUnitDao gyUnitDao;
    @Autowired
    private GyDataDao gyDataDao;

    @Value("${managerValue}")
    public String managerValue;

    @Value("${deployType:#{null}}")
    public String deployType;

        @Value("${sysProvince:#{null}}")
    public String sysProvince;

    @Value("${sysCity:#{null}}")
    public String sysCity;

    @Autowired
    private ExamineLogService examineLogService;
    @Autowired
    private RoleService roleService;
    @Autowired
    private MessageHuNanService messageHuNanService;
    @Autowired
    private GyDataUnitService gyDataUnitService;

    private static Logger log = LoggerFactory.getLogger(GyDataServiceImpl.class);

    @Override
    public List<GyDataImportDTO> list(Map<String, Object> map) {
        UserDO currUser = ShiroUtils.getUser();
        log.info("currUser:" + currUser.toString());
        if(currUser.getDeptId() == null){
            throw new BDException("当前用户的部门id为空");
        }
        if (!managerValue.contains(currUser.getDeptId().toString())) {
            String province = currUser.getProvince();
            if(StringUtils.isEmpty(province)){
                province = sysProvince;
            }
            if(!ObjectUtils.isEmpty(province)) {
                map.put("unitProvince", province);
            }

            String city = currUser.getCity();
            if(StringUtils.isEmpty(city)){
                city = sysCity;
            }

            String gyUnitId = currUser.getGyUnitId();
            map.put("unitId", gyUnitId);
            if (!ObjectUtils.isEmpty(city)) {
                map.put("unitCity", city);
                if(!map.containsKey("auditStage")){
                    map.put("auditStage", "1");
                }else{
                    // 包含auditStage时检查值
                    String value = (String) map.get("auditStage");
                    if (value == null || value.trim().isEmpty()) {
                        map.put("auditStage", "1");
                    }
                }
            } else {
                if(!map.containsKey("auditStage")){
                    map.put("auditStage", "2");
                }else{
                    // 包含auditStage时检查值
                    String value = (String) map.get("auditStage");
                    if (value == null || value.trim().isEmpty()) {
                        map.put("auditStage", "2");
                    }
                }
            }

        }
        return this.dealDataWithCompany(gyDataDao.list(map));
    }

    /***
     * 处理查询出来的数据绑定企业信息
     * @param list
     * @return
     */
    List<GyDataImportDTO> dealDataWithCompany(List<GyDataImportDTO> list){
        // 使用Stream提取所有dataId
        List<Long> dataIds = list.stream()
                .map(GyDataImportDTO::getId)
                .collect(Collectors.toList());

        if (CollectionUtils.isEmpty(dataIds)){
            return list;
        }

        // 一次性获取所有关联的单位信息并转换为Map以提高查询效率
        Map<Long, GyDataUnitDO> unitMap = gyDataUnitService.getByDataIds(dataIds).stream()
                .collect(Collectors.toMap(GyDataUnitDO::getDataId, Function.identity()));

        // 遍历DTO列表，设置单位信息
        list.forEach(dto -> {
            GyDataUnitDO unitDO = unitMap.get(dto.getId());
            if (unitDO != null) {
                dto.setReporterUnit(unitDO.getReporterUnit());
                dto.setCreditCode(unitDO.getCreditCode());
                dto.setSupervisoryOrg(unitDO.getSupervisoryOrg());
                dto.setUnitCharacter(unitDO.getUnitCharacter());
                dto.setIndustryTypeOne(unitDO.getIndustryTypeOne());
                dto.setIndustryTypeTwo(unitDO.getIndustryTypeTwo());
                dto.setUnitProvince(unitDO.getUnitProvince());
                dto.setUnitCity(unitDO.getUnitCity());
                dto.setUnitMan(unitDO.getUnitMan());
                dto.setUnitOffice(unitDO.getUnitOffice());
                dto.setDataSecurityMan(unitDO.getDataSecurityMan());
                dto.setDataSecurityOffice(unitDO.getDataSecurityOffice());
                dto.setDataSecurityWay(unitDO.getDataSecurityWay());
                dto.setUpdateTime(unitDO.getUpdateTime());
            }
        });
        return list;
    }

    @Override
    public int countTotal(Map<String, Object> map) {

        UserDO currUser = ShiroUtils.getUser();
        log.info("currUser:" + currUser.toString());
        if (!managerValue.contains(currUser.getDeptId().toString())) {

            String province = currUser.getProvince();
            if(StringUtils.isEmpty(province)){
                province = sysProvince;
            }
            if(!ObjectUtils.isEmpty(province)) {
                map.put("unitProvince", province);
            }

            String city = currUser.getCity();
            if(StringUtils.isEmpty(city)){
                city = sysCity;
            }
            String gyUnitId = currUser.getGyUnitId();
            map.put("unitId", gyUnitId);
            if (!ObjectUtils.isEmpty(city)) {
                map.put("unitCity", city);
                map.put("auditStage", "1");
            } else {
                map.put("auditStage", "2");
            }
        }

        return gyDataDao.countTotal(map);
    }

    /**
     * @param file
     * @Description: 文件解密
     * @Author: wangp
     * @Date: 2022/3/9 15:07
     * @Return: boolean
     */
    @Override
    public String dec(MultipartFile file) {
        String decFile = "";
        FileInputStream srcStream = null;
        try {
            String fileName = file.getOriginalFilename();
            // 生成临时文件
            String fileNames = FileUtil.renameToUUID(fileName);
            String temFile = bootdoConfig.getUploadPath() + fileNames;
            MultipartFileToFile(file, temFile);
            // FileUtil.uploadFile(file.getBytes(), bootdoConfig.getUploadPath(), fileName);
            // 解密文件路径
            decFile = bootdoConfig.getUploadPath() + fileName;
            log.info("temFile:" + temFile + ",decFile:" + decFile);
            srcStream = new FileInputStream(temFile);
            log.info("srcStream:" + srcStream.toString());
            log.info("EncPrival:" + bootdoConfig.getEncPrival());
            FileCryptTool.decryptFile(bootdoConfig.getEncPrival(), temFile, decFile);
            // 清除数据
            srcStream.close();
            FileUtil.deleteFile(temFile);// 临时文件
        } catch (Exception e) {
            log.error("文件解密异常", e);
            return "";
        }
        return decFile;
    }

    /**
     * @description:  密文导入
     * @param:
     * @return: int
     * @author xhl
     * @date: 2022/9/7 14:28
     */
    @Override
    public String cipherTextImport(String decFlg,int type) throws Exception {
        File file = new File(decFlg);
        String result = null;

        InputStream is = null;
        try {
            is = new FileInputStream(file);
            result = imp(is,type);
            // 清除临时数据
            FileUtil.deleteFile(decFlg);
        } finally {
            try {
                if (is != null) {
                    is.close();
                }
                // xssfWorkbook.close();
            } catch (IOException e) {
                e.printStackTrace();
            }

            return result;
        }
    }
    /**
     * @description:  明文导入
     * @param: file
     * @return: java.lang.String
     * @author xhl
     * @date: 2022/9/7 14:23
     */
    @Override
    public String plainTextImport(MultipartFile file,int type) throws Exception {

        String result = null;
        InputStream is = null;
        try {
            is = file.getInputStream();
            result = imp(is,type);
        } finally {
            try {
                is.close();
                // xssfWorkbook.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

        return result;
    }

    private String imp(InputStream is,int type) throws Exception {
        int otherProvince = 0;
        int otherCity = 0;
        int auditPass = 0;
        int unitNoAudit = 0;
        int nonData = 0;
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
        XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);
        // 企业信息
        List<GyUnitDO> gyUnitList = new ArrayList();
        // 数据目录
        List<GyDataDO> dataList = new ArrayList();

        // 用户信息
        UserDO currUser = ShiroUtils.getUser();
        log.info("currUser:" + currUser.toString());
        if(null == currUser){
            throw new BDException("当前用户没登录");
        }
        String userProvince = null;
        String userCity= null;

        if (managerValue.contains(currUser.getDeptId().toString())) {
            userProvince = sysProvince;
            userCity = sysCity;
        } else {
            userProvince = currUser.getProvince();
            if(StringUtils.isEmpty(userProvince)){
                userProvince = sysProvince;
            }
            userCity = currUser.getCity();
            if(StringUtils.isEmpty(userCity)){
                userCity = sysCity;
            }
        }
        log.info("===========1=============");
        //判断是哪种格式的
        Row firstRow = xssfSheet.getRow(0);
        Cell firstRowCell = firstRow.getCell(0);
        int beginDataRowIndex = 0;
        if("序号".equals(firstRowCell.getStringCellValue())){
            beginDataRowIndex = 1;
        } else {
            beginDataRowIndex = 4;
        }
        log.info("===========2=============");

        if(xssfSheet.getLastRowNum() < beginDataRowIndex){ //getLastRowNum返回最后一行的索引，即 比行总数小1
            throw new BDException("excel文件无数据");
        }
        log.info("===========3=============");
        log.info("===========3=============");
        // 遍历
        for (int i = beginDataRowIndex; i <= xssfSheet.getLastRowNum(); i++) {
            log.info("userProvince: {}, i: {}", userProvince, i);

            Row row = xssfSheet.getRow(i);
            if (row.getCell(1) == null) {
                break;
            }
            if(row.getLastCellNum()<52){ //getLastCellNum返回的是最后一列的列数，即 等于总列数
                throw new BDException("excel文件列数不正确");
            }
            log.info("===========4============="+i);
            //所属省份
            String dataProvince = this.getCellValue_String(row,18);
            String dataCity = this.getCellValue_String(row,19);

            // 当非管理员导入的数据为非本省的数据的时候  跳过当前数据  并记录
            if (StringUtils.isEmpty(dataProvince) || (!StringUtils.isEmpty(userProvince)) && !dataProvince.equals(userProvince)) {
                log.info("当前用户省份: {}, 当前数据省份: {}, 数据名称: {}", userProvince, dataProvince, this.getCellValue_String(row,1));
                otherProvince++;
                continue;
            }
            log.info("===========5============="+i);

            if (StringUtils.isEmpty(dataCity) || (!StringUtils.isEmpty(userCity)) && !dataCity.equals(userCity)) {
                log.info("当前用户地市: {}, 当前数据地市: {}, 数据名称: {}", userCity, dataCity, this.getCellValue_String(row,1));
                otherCity++;
                continue;
            }

            log.info("===========6============="+i);

            // 企业单位
            GyUnitDO gyUnitDO = new GyUnitDO();
            // 数据信息
            GyDataDO gyDataDO = new GyDataDO();

            //代码报错：java.lang.IllegalStateException: Cannot get a STRING value from a NUMERIC cell
            //GyUnitDO tmpGyUnitDO = gyUnitDao.getGyUnitByCreditCode(row.getCell(17).getStringCellValue());

            //获取单元格的字符出信息
            String creditCode = this.getCellValue_String(row,17);
            log.info("===========6.5============creditCode="+creditCode);
            // 使用获取到的信用代码
            GyUnitDO tmpGyUnitDO = gyUnitDao.getGyUnitByCreditCode(creditCode);

            //判断是否已经在gu_unit表中添加企业信息
            if (ObjectUtils.isEmpty(tmpGyUnitDO) ) {
                //没有添加企业信息，   需要进行企业信息的添加，审核状态为【未审核】
                log.info("===========7============="+i);
                try {
                    this.createGyUnitDO(gyUnitDO, row);
                    gyUnitDO.setCreateTime(new Date());
                    gyUnitDO.setAuditStatus("0");
                    gyUnitDao.save(gyUnitDO);
                    log.info("========first_load_data======create_gy_unit======sucess:"+gyUnitDO.toString());
                } catch (Exception e) {
                    log.error("error enterprise-only : {}", e.getMessage());
                }
                unitNoAudit++;
                break;
            } else {
                log.info("===========8============="+i);
                if ( !"1".equals(tmpGyUnitDO.getAuditStatus()) ){
                    unitNoAudit++;
                    break;
                }
            }

            log.info("===========9============="+i);
            //无数据导出
            if ("/".equals(this.getCellValue_String(row,1)) && this.getCellValue_String(row,16) !=null && !"/".equals(this.getCellValue_String(row,16))) {

                log.info("===========10============="+i);
                int count = gyUnitDao.getGyUnit(this.createGyUnitDO(gyUnitDO,row));
                if (count == 0) {
                    try {
                        gyUnitDO.setCreateTime(new Date());
                        gyUnitDO.setAuditStatus("0");
                        gyUnitDao.save(gyUnitDO);
                    } catch (Exception e) {
                        log.error("error enterprise-only : {}", e.getMessage());
                    }
                }
                nonData++;
                break;
            }

            gyDataDO.setDataName(this.getCellValue_String(row,1));//数据名称
            gyDataDO.setDataTypeBase(this.getCellValue_String(row,2));//数据分类分级规范
            gyDataDO.setDataTypeOne(this.getCellValue_String(row,3));//数据一级类别
            gyDataDO.setDataTypeTwo(this.getCellValue_String(row,4));//数据二级类别
            gyDataDO.setDataTypeThree(row.getCell(5)==null?null:this.getCellValue_String(row,5));//数据三级类别
            gyDataDO.setDataTypeFour(row.getCell(6)==null?null:this.getCellValue_String(row,6));//数据四级类别
            gyDataDO.setDataLevel(this.getCellValue_String(row,7));//数据级别
            gyDataDO.setDataCarrier(this.getCellValue_String(row,8));//数据载体
            gyDataDO.setDataSource(this.getCellValue_String(row,9));//数据来源
            gyDataDO.setDataNumGb(row.getCell(10).getNumericCellValue());//数据数量(非电子数据)
            gyDataDO.setDataNum((int) row.getCell(11).getNumericCellValue());//数据数量(电子数据)
            gyDataDO.setDataScope(this.getCellValue_String(row,12));//覆盖类型
            gyDataDO.setDataProportion(this.getCellValue_String(row,13));//覆盖占比
            gyDataDO.setDataPrecision(this.getCellValue_String(row,14));//数据精度
            gyDataDO.setDataPrecisionDes(this.getCellValue_String(row,15));//数据精度描述

            log.info("===========11============="+i);
            //添加企业信息
            gyUnitList.add(this.createGyUnitDO(gyUnitDO,row));

            gyDataDO.setDataHandleType(this.getCellValue_String(row,26));//数据处理方式
            gyDataDO.setDataResult(this.getCellValue_String(row,27));//数据目的
            gyDataDO.setIsAlgorithmHandle(this.getCellValue_String(row,28));//是否涉及算法处理
            gyDataDO.setIsCross(this.getCellValue_String(row,29));//是否出境
            gyDataDO.setCrossAcceptName(this.getCellValue_String(row,30));//跨境接收方名称
            gyDataDO.setDataCrossType(this.getCellValue_String(row,31));//出境方式
            gyDataDO.setIsSecurityAss(this.getCellValue_String(row,32));//是否安全评估
            gyDataDO.setAssessResult(this.getCellValue_String(row,33));//评估结果
            gyDataDO.setIsCrossMain(this.getCellValue_String(row,34));//是否对外共享
            gyDataDO.setCrossmainAcceptName(this.getCellValue_String(row,35));//数据对外共享接收方
            gyDataDO.setDataCrossmainType(this.getCellValue_String(row,36));//数据对外共享方式
            gyDataDO.setIsForeignData(this.getCellValue_String(row,37));//是否为涉外数据
            gyDataDO.setIsCrossmainFlow(this.getCellValue_String(row,38));//是否涉及跨主体流动
            gyDataDO.setInfoSystemName(this.getCellValue_String(row,39));//信息系统名称
            gyDataDO.setInfoSystemIpAddress(this.getCellValue_String(row,40));//信息系统ip地址
            gyDataDO.setInfoSystemDomainName(this.getCellValue_String(row,41));//信息系统域名
            gyDataDO.setInfoSystemType(this.getCellValue_String(row,42));//信息系统类型
            gyDataDO.setSecurityCognizance(this.getCellValue_String(row,43));//网络安全等级保护认定情况
            gyDataDO.setComSecurityCognizance(this.getCellValue_String(row,44));//通信网络安全防护定级备案情况
            gyDataDO.setIsKetSystem(this.getCellValue_String(row,45));//是否为关键信息基础设施
            gyDataDO.setIsKeydataAss(this.getCellValue_String(row,46));//是否开展重要数据安全风险评估
            gyDataDO.setAssessOrg(this.getCellValue_String(row,47));//评估机构
            gyDataDO.setAssessBase(this.getCellValue_String(row,48));//评估依据的规范
            gyDataDO.setAssessTime(this.getCellValue_String(row,49));//评估时间
            gyDataDO.setDataAssessResult(this.getCellValue_String(row,50));//重要数据安全风险评估结论
            gyDataDO.setDataDesc(this.getCellValue_String(row,51));//备注
            log.info("===========12============="+i);

            gyDataDO.setGyUnitId(gyUnitDO.getCreditCode());//关联填报企业

            log.info("===========12.1============="+i+"===type=="+type);
            if (type == 1) { //导入列表导入
                log.info("===========13============="+i);
                gyDataDO.setSendVerify("0");// 送审状态 0:未送审 1：已送审
                gyDataDO.setDataStatus("0"); // 状态 0:待审核 1:删除 2:已上报 3:通过审核 4:驳回
                gyDataDO.setDeleteStatus("0"); // 状态 0:正常 1:删除
                log.info("===========14============="+i+"===deployType===="+deployType);
                if("1".equals(deployType)){ //市级单独部署
                    gyDataDO.setAuditStage("1");
                } else if("2".equals(deployType)){ //省级单独部署
                    gyDataDO.setAuditStage("2");
                }  else if("3".equals(deployType)){ //省市一体部署
                    if(StringUtils.isNotEmpty(currUser.getCity())){ //市级人员操作
                        gyDataDO.setAuditStage("1");
                    } else { //省级人员
                        if (managerValue.contains(currUser.getDeptId().toString())) { //机构是管理员
                            gyDataDO.setAuditStage("1");
                        } else { //一般省级人员
                            gyDataDO.setAuditStage("2");
                        }
                    }
                }
            } else if(type == 2){ //数据目录导入
                gyDataDO.setSendVerify("1");// 送审状态 0:未送审 1：已送审
                gyDataDO.setDataStatus("3"); // 状态 0:待审核 1:删除 2:已上报 3:通过审核 4:驳回
                gyDataDO.setDeleteStatus("0"); // 状态 0:正常 1:删除
                if("1".equals(deployType)){ //市级单独部署
                    throw new BDException("不支持该操作，请到“报送到省级”列表操作");
                } else if("2".equals(deployType)){ //省级单独部署
                    gyDataDO.setAuditStage("2");
                }  else if("3".equals(deployType)){ //省市一体部署
                    if(StringUtils.isNotEmpty(currUser.getCity())){ //市级人员操作
                        throw new BDException("市级人员无权操作");
                    } else {
                        gyDataDO.setAuditStage("2");
                    }

                }
            } else { //上报到省级导入
                log.info("===========15============="+i);
                gyDataDO.setSendVerify("1");// 送审状态 0:未送审 1：已送审
                gyDataDO.setDataStatus("3"); // 状态 0:待审核 1:删除 2:已上报 3:通过审核 4:驳回
                gyDataDO.setDeleteStatus("0"); // 状态 0:正常 1:删除
                if("1".equals(deployType)){ //市级单独部署
                    gyDataDO.setAuditStage("1");
                } else if("2".equals(deployType)){ //省级单独部署
                    throw new BDException("不支持该操作，请到“数据目录”列表操作");
                }  else if("3".equals(deployType)){ //省市一体部署
                    if(StringUtils.isNotEmpty(currUser.getCity())){ //市级人员操作
                        gyDataDO.setAuditStage("1");
                    } else {
                        if (managerValue.contains(currUser.getDeptId().toString())) { //机构是管理员
                            gyDataDO.setAuditStage("1");
                        } else { //一般省级人员
                            throw new BDException("省级人员无权操作");
                        }
                    }
                }
            }
            gyDataDO.setUploadTime(new Date());
            gyDataDO.setUpdateTime(gyDataDO.getUploadTime());
            log.info("dataDO:" + gyDataDO.toString());
            Map<String, Object> params = new HashMap<>();
            params.put("dataName", gyDataDO.getDataName());// 数据名称
            params.put("gyUnitId", gyDataDO.getGyUnitId());// 单位
            //判断数据是否已经上传过
            List<GyDataImportDTO> listInfo = gyDataDao.listId(params);
            log.info("============16=======listInfo======"+listInfo);
            if (listInfo !=null && listInfo.size()>0) {
                GyDataImportDTO gyDataImportDTO = listInfo.get(0);
                if("3".equals(gyDataImportDTO.getDataStatus())){ //审核通过数据不导入
                    log.info("审核通过数据id：" + gyDataImportDTO.getId());
                    auditPass++;
                    continue;
                } else{
                    // 删除数据
                    log.info("删除数据id：" + gyDataImportDTO.getId());
                    gyDataDao.remove(gyDataImportDTO.getId());
                }

            }

            dataList.add(gyDataDO);

        }
        // 企业单位批量导入
        log.info("unitList size:" + gyUnitList.size());
        if (gyUnitList != null && gyUnitList.size() > 0) {
            for (GyUnitDO gyUnitDO : gyUnitList) {
                log.info("unitList size:" + gyUnitList.size());

                int count = gyUnitDao.getGyUnit(gyUnitDO);
                if (count == 0) {
                    try {
                        gyUnitDO.setCreateTime(new Date());
                        gyUnitDO.setAuditStatus("0");
                        gyUnitDao.save(gyUnitDO);
                    } catch (Exception e) {
                        log.error("error enterprise-only : {}", e.getMessage());
                    }
                }

            }
        }
        // 数据批量导入
        if (dataList != null && dataList.size() > 0) {
            gyDataDao.batchSave(dataList);
        }
        String result = null;
        if ((otherProvince + otherCity + auditPass + unitNoAudit + nonData) > 0) {
            StringBuilder sb = new StringBuilder();
            if (otherProvince > 0) {
                sb.append(otherProvince).append("条非本省数据（已忽略导入）! <br/>");
            }
            if (otherCity > 0) {
                sb.append(otherCity).append("条非本市数据（已忽略导入）! <br/>");
            }
            if (auditPass > 0) {
                sb.append(auditPass).append("条已经审核通过数据（已忽略导入）! <br/>");
            }
            if (unitNoAudit > 0) {
                sb.append(unitNoAudit).append("条所属企业未注册或还没通过审核（已忽略导入）! <br/>");
            }
            if (nonData > 0) {
                sb.append(nonData).append("条仅导入企业（无数据导入）! <br/>");
            }
            result = sb.toString();
        }

        return result;
    }

    //获取单元格的字符出信息
    private String getCellValue_String(Row row,int i) {
        // 获取单元格
        Cell cell = row.getCell(i);
        String result = null;

        if (cell != null) {
            // 根据 POI 3.17 版本的单元格类型常量处理
            switch (cell.getCellType()) {
                case Cell.CELL_TYPE_STRING:
                    result = cell.getStringCellValue();
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    // 数值类型转换为字符串
                    result = String.valueOf(cell.getNumericCellValue());
                    // 处理小数点后为0的情况（如12345.0 -> 12345）
                    if (result.endsWith(".0")) {
                        result = result.substring(0, result.length() - 2);
                    }
                    break;
                case Cell.CELL_TYPE_BLANK:
                    result = "";
                    break;
                default:
                    // 处理公式、布尔等其他类型
                    result = String.valueOf(cell);
            }
        }
        return result;
    }


    private GyUnitDO createGyUnitDO(GyUnitDO gyUnitDO, Row row){
        gyUnitDO.setReporterUnit(this.getCellValue_String(row,16));// 数据处理者名称
        gyUnitDO.setCreditCode(this.getCellValue_String(row,17));// 统一社会信用代码(机构代码 )
        gyUnitDO.setUnitProvince(this.getCellValue_String(row,18));//所在地区（省）
        gyUnitDO.setUnitCity(this.getCellValue_String(row,19));//所在地区（市）
        gyUnitDO.setUnitCharacter(this.getCellValue_String(row,20));//数据处理者性质
        gyUnitDO.setIndustryTypeOne(this.getCellValue_String(row,21));//所属行业
        gyUnitDO.setIndustryTypeTwo(this.getCellValue_String(row,22));//主营业务范围
        gyUnitDO.setDataSecurityMan(this.getCellValue_String(row,23));//数据安全负责人姓名
        gyUnitDO.setDataSecurityOffice(this.getCellValue_String(row,24));//数据安全负责人职务
        gyUnitDO.setDataSecurityWay(this.getCellValue_String(row,25));//数据安全负责人联系方式
        return gyUnitDO;
    }


    @Override
    public GyDataImportDTO get(Long id) {
        return gyDataDao.get(id);
    }

    @Override
    public GyDataExcelDO getDataExcel(Long id) {
        return gyDataDao.getDataExcel(id);
    }

    @Override
    public GbDataExcelDO getGbDataExcel(Long id) {
        return gyDataDao.getGbDataExcel(id);
    }

    @Override
    public void updateStatus(GyDataImportDTO data,String opinion) {
        int effect = gyDataDao.updateStatus(data);

        if ("2".equalsIgnoreCase(data.getDataStatus()) && effect > 0) {
            GyDataImportDTO gyDataImportDTO = get(data.getId());
            RoleDO roleDO = roleService.get(gyDataImportDTO.getRoleId());
            if (roleDO != null) {
                ExamineLogDTO dto = new ExamineLogDTO();
                dto.setUniqueKey(String.valueOf(data.getId()));
                dto.setDataType("DI_OPER");
                dto.setMessage("数据已送审至: " + roleDO.getRoleName());
                examineLogService.save(dto);
            }
        } else if ("3".equalsIgnoreCase(data.getDataStatus()) && effect > 0) {
            GyDataImportDTO gyDataImportDTO = get(data.getId());
            RoleDO roleDO = roleService.get(gyDataImportDTO.getRoleId());
            if (roleDO != null) {
                ExamineLogDTO dto = new ExamineLogDTO();
                dto.setUniqueKey(String.valueOf(data.getId()));
                dto.setDataType("DI_OPER");
                dto.setMessage("数据已被(" + roleDO.getRoleName() + ")审核通过");
                examineLogService.save(dto);
            }

        } else if ("4".equalsIgnoreCase(data.getDataStatus()) && effect > 0) {
            GyDataImportDTO gyDataImportDTO = get(data.getId());
            RoleDO roleDO = roleService.get(gyDataImportDTO.getRoleId());
            if (roleDO != null) {
                ExamineLogDTO dto = new ExamineLogDTO();
                dto.setUniqueKey(String.valueOf(data.getId()));
                dto.setDataType("DI_OPER");
                dto.setMessage("数据已被(" + roleDO.getRoleName() + ")审核驳回, 请核对");
                dto.setOpinion(opinion);
                examineLogService.save(dto);
            }
            if("湖南".equals(this.sysProvince)){
                //通过数据id，查询数据信息
                GyDataImportDTO importDTO = gyDataDao.get(data.getId());
                if(Objects.nonNull(importDTO)){
                    //发送短息
                    if("2".equals(importDTO.getAuditStage())){
                        log.info("=====================发送短信了===数据信息查询==="+importDTO.toString());
                        //审核阶段：   1:市级，2：省级
                        GyUnitDO gyUnitDO = gyUnitDao.getGyUnitByCreditCode(importDTO.getGyUnitId());
                        log.info("=====================发送短信了===企业信息查询==="+gyUnitDO.toString());
                        if(Objects.nonNull(gyUnitDO)){
                            try {
                                log.info("=====================发送短信了===start==="+gyUnitDO.toString());
                                messageHuNanService.sendMessage(gyUnitDO.getDataSecurityWay()+","+gyUnitDO.getMobilePhone(),
                                        importDTO.getDataName());
                                log.info("=====================发送短信了===end==="+gyUnitDO.toString());
                            } catch (Exception e) {
                                log.error("=============messageHuNanService.sendMessage=======error======="+e.getMessage());
                                e.printStackTrace();
                            }
                        }else {
                            log.info("=========企业的组织机构代码："+importDTO.getGyUnitId()+",查询不到对应的企业信息===========");
                        }
                    }
                }else {
                    log.error("=========数据id："+data.getId()+",查询不到对应的数据信息===========");
                }
            }
        }
    }

    @Override
    public void addExpertOpinion(ExpertOpinionDO expertOpinionDO){
        GyDataImportDTO gyDataImportDTO = get(expertOpinionDO.getId());
        RoleDO roleDO = roleService.get(gyDataImportDTO.getRoleId());
        if (roleDO != null) {
            ExamineLogDTO dto = new ExamineLogDTO();
            dto.setUniqueKey(String.valueOf(expertOpinionDO.getId()));
            dto.setDataType("DI_OPER");
            dto.setMessage(roleDO.getRoleName() + "的专家意见为： " + expertOpinionDO.getOpinion());
            examineLogService.save(dto);
        }
    }


    @Override
    public void addUnionOpinion(UnionOpinionDO unionOpinionDO) {
        GyDataImportDTO gyDataImportDTO = get(unionOpinionDO.getId());
        RoleDO roleDO = roleService.get(gyDataImportDTO.getRoleId());
        if (roleDO != null) {
            ExamineLogDTO dto = new ExamineLogDTO();
            dto.setUniqueKey(String.valueOf(unionOpinionDO.getId()));
            dto.setDataType("DI_OPER");
            dto.setMessage(roleDO.getRoleName() + "的联合审核意见为： " + unionOpinionDO.getOpinion());
            examineLogService.save(dto);
        }
    }


    @Override
    public void batchRemove(Long[] ids) {
        gyDataDao.batchRemove(ids);
    }

    @Override
    public int countTotal4Del(Map<String, Object> map) {
        return gyDataDao.countTotal4Del(map);
    }

    @Override
    public List<GyDataImportDTO> listRecover(Map<String, Object> map) {
        UserDO currUser = ShiroUtils.getUser();
        log.info("currUser:" + currUser.toString());
        if (!managerValue.contains(currUser.getDeptId().toString())) {

            String province = currUser.getProvince();
            if(StringUtils.isEmpty(province)){
                province = sysProvince;
            }
            if(!ObjectUtils.isEmpty(province)) {
                map.put("unitProvince", province);
            }

            String city = currUser.getCity();
            if(StringUtils.isEmpty(city)){
                city = sysCity;
            }

            String gyUnitId = currUser.getGyUnitId();
            map.put("unitId", gyUnitId);
            if (!ObjectUtils.isEmpty(city)) {
                map.put("unitCity", city);
                map.put("auditStage", "1");
            } else {
                map.put("auditStage", "2");
            }
        }

        return gyDataDao.listRecover(map);
    }

    @Override
    public int countRecover(Map<String, Object> map) {
        UserDO currUser = ShiroUtils.getUser();
        log.info("currUser:" + currUser.toString());
        if (!managerValue.contains(currUser.getDeptId().toString())) {

            String province = currUser.getProvince();
            if(StringUtils.isEmpty(province)){
                province = sysProvince;
            }
            if(!ObjectUtils.isEmpty(province)) {
                map.put("unitProvince", province);
            }

            String city = currUser.getCity();
            if(StringUtils.isEmpty(city)){
                city = sysCity;
            }
            String gyUnitId = currUser.getGyUnitId();
            map.put("unitId", gyUnitId);
            if (!ObjectUtils.isEmpty(city)) {
                map.put("unitCity", city);
                map.put("auditStage", "1");
            } else {
                map.put("auditStage", "2");
            }
        }

        return gyDataDao.countRecover(map);
    }

    @Transactional
    @Override
    public int recover(Long id) {
        GyDataImportDTO gyDataImportDTO = gyDataDao.getRecoverData(id);
        if("1".equals(gyDataImportDTO.getDeleteStatus())){ //属于删除数据
            Map<String, Object> params = new HashMap<>();
            params.put("dataName", gyDataImportDTO.getDataName());// 数据名称
            params.put("gyUnitId", gyDataImportDTO.getGyUnitId());// 单位
            int countTotal = gyDataDao.countTotal(params);
            if (countTotal > 0) {
                // //未删除的数据存在企业机构编码和数据名一样的，不允许恢复
                return 0;
            }
        }
        UserDO currUser = ShiroUtils.getUser();
        String auditStage =null;
        if("1".equals(deployType)){ //市级单独部署
            auditStage = "1";
        } else if("2".equals(deployType)){ //省级单独部署
            auditStage = "2";
        }  else if("3".equals(deployType)){ //省市一体部署
            if (managerValue.contains(currUser.getDeptId().toString())) { //机构是管理员
                auditStage = "1";
            } else {
                String city = currUser.getCity();
                if(StringUtils.isEmpty(city)){ //一般省级人员
                    auditStage = "2";
                } else { //一般市级人员
                    auditStage = "1";
                }
            }
        }
        int result = gyDataDao.recover(id, auditStage);
        gyDataUnitService.deleteByDataId(id);
        return result;
    }

    @Override
    public int batchPhysicalDeletion(Long id) {

        Long[] longArray = new Long[]{id};
        return gyDataDao.batchPhysicalDeletion(longArray);
    }

    @Override
    public void encryptExports(HttpServletResponse responses, String idValue) throws Exception {
        List<GyDataExcelDO> list = new ArrayList<>();
        JSONArray json = (JSONArray)JSONArray.parse(idValue);
        for (int i = 0; i < json.size(); i++) {
            String id = String.valueOf(json.get(i));
            log.info("批量导出 id:" + json.get(i));
            GyDataExcelDO data = new GyDataExcelDO();
            data = gyDataDao.getDataExcel(Long.parseLong(id));
            data.setNo(i + 1); //序号
            list.add(data);
        }
        log.info("list size:" + list.size());
        String decFile = bootdoConfig.getUploadPath() + "数据备案导出信息.xlsx";
        String encFile = bootdoConfig.getUploadPath() + "数据备案加密导出信息.xlsx";

        ExcelUtils.encryptExport(responses, EncryptExcelDO.class,list,bootdoConfig.getCrtPrival(),decFile,encFile);
    }

    @Override
    public int countAaAssData(Long id){
        return gyDataDao.countAaAssData(id);
    }

    @Override
    public List<GyDataImportDTO> dataEntryList(Map<String, Object> map) {
        UserDO currUser = ShiroUtils.getUser();
        log.info("currUser:" + currUser.toString());
        if (!managerValue.contains(currUser.getDeptId().toString())) {
            String province = currUser.getProvince();
            if (StringUtils.isEmpty(province)) {
                province = sysProvince;
            }
            if (!ObjectUtils.isEmpty(province)) {
                map.put("unitProvince", province);
            }

            String city = currUser.getCity();
            if (StringUtils.isEmpty(city)) {
                city = sysCity;
            }

            String gyUnitId = currUser.getGyUnitId();
            map.put("unitId", gyUnitId);
            if (!ObjectUtils.isEmpty(city)) {
                map.put("unitCity", city);
            }

        }
        return this.dealDataWithCompany(gyDataDao.list(map));
    }

    @Override
    public int dataEntryCountTotal(Map<String, Object> map) {
        UserDO currUser = ShiroUtils.getUser();
        log.info("currUser:" + currUser.toString());
        if (!managerValue.contains(currUser.getDeptId().toString())) {

            String province = currUser.getProvince();
            if (StringUtils.isEmpty(province)) {
                province = sysProvince;
            }
            if (!ObjectUtils.isEmpty(province)) {
                map.put("unitProvince", province);
            }

            String city = currUser.getCity();
            if (StringUtils.isEmpty(city)) {
                city = sysCity;
            }
            String gyUnitId = currUser.getGyUnitId();
            map.put("unitId", gyUnitId);
            if (!ObjectUtils.isEmpty(city)) {
                map.put("unitCity", city);
                map.put("auditStage", "1");
            } else {
                map.put("auditStage", "2");
            }
        }
        return gyDataDao.countTotal(map);
    }

    /**
     * @Description: MultipartFile转file
     * @Author: wangp
     * @Date: 2022/2/25 9:15
     * @param multiFile
     * @param fileName
     * @Return: File
     */
    public static File MultipartFileToFile(MultipartFile multiFile, String fileName) {
        // 获取文件后缀
        String prefix = fileName.substring(fileName.lastIndexOf("."));
        log.info("fileName:" + fileName + ",prefix:" + prefix);
        // 若须要防止生成的临时文件重复,能够在文件名后添加随机码
        try {
            File file = new File(fileName);
            boolean res = file.createNewFile();
            if (!res) {
                log.info("创建失败！");
            } else {
                multiFile.transferTo(file);
                file.setExecutable(true);// 设置可执行权限
                file.setReadable(true);// 设置可读权限
                file.setWritable(true);// 设置可写权限
            }
            return file;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }



    public static void main(String[] args) {
        FileCryptTool.encryptFile("F:/dev-dys/sm2/sm2_client_enc_cert.crt", "F:/dev-dys/res/测试导入-河南.xlsx",
                "F:/dev-dys/res/enc-测试导入加密文件-河南.xlsx");
        FileCryptTool.encryptFile("F:/dev-dys/sm2/sm2_client_enc_cert.crt", "F:/dev-dys/res/测试导入-河北.xlsx",
                "F:/dev-dys/res/enc-测试导入加密文件-河北.xlsx");
        FileCryptTool.encryptFile("F:/dev-dys/sm2/sm2_client_enc_cert.crt", "F:/dev-dys/res/测试导入-四川.xlsx",
                "F:/dev-dys/res/enc-测试导入加密文件-四川.xlsx");
    }
}