package com.huatek.busi.service.impl;

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.util.ArrayUtil;
import cn.hutool.core.util.NumberUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.dubbo.config.annotation.Reference;
import com.alibaba.dubbo.config.annotation.Service;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.github.pagehelper.Page;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.google.api.client.util.Lists;
import com.google.common.collect.Maps;
import com.huatek.busi.common.DictEnum;
import com.huatek.busi.common.FileTypeEnum;
import com.huatek.busi.common.SiteConstants;
import com.huatek.busi.common.util.DateUtil;
import com.huatek.busi.common.util.GenerateUtil;
import com.huatek.busi.common.util.ImportExcel;
import com.huatek.busi.dto.*;
import com.huatek.busi.entity.*;
import com.huatek.busi.init.SftpInit;
import com.huatek.busi.mapper.LabReportBasisDataMapper;
import com.huatek.busi.service.*;
import com.huatek.busi.service.gis.BusiOfflineGisBrandService;
import com.huatek.busi.service.gis.GisMechanicalPropertiesService;
import com.huatek.busi.service.transformer.BusiOfflineTransformerBrandService;
import com.huatek.busi.util.FTPUtil;
import com.huatek.busi.vo.*;
import com.huatek.busi.where.LabReportBasisDataSelectVo;
import com.huatek.busi.where.LabReportBasisDataStatisticsWhereVo;
import com.huatek.busi.where.LabReportParamDataSelectVo;
import com.huatek.frame.base.entity.Dictionnaire;
import com.huatek.frame.base.service.DictionnaireDetailService;
import com.huatek.frame.base.service.DictionnaireService;
import com.huatek.frame.base.util.Constants;
import com.huatek.frame.base.vo.DictionnaireDetailVO;
import com.huatek.torch.frame.exception.OwlException;
import com.huatek.torch.frame.tools.ConstantUtil;
import com.huatek.torch.frame.tools.ResultUtil;
import com.huatek.torch.sql.beans.LocalUser;
import com.huatek.torch.sql.dao.impl.BaseDao;
import com.huatek.torch.util.tools.UserUtil;
import com.huatek.torch.util.uuid.Uuid;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.http.entity.ContentType;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.mock.web.MockMultipartFile;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import java.io.ByteArrayInputStream;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.stream.Collectors;

@Slf4j
@Component
@Service(interfaceClass = ILabReportBasisDataService.class)
public class              LabReportBasisDataServiceImpl extends BaseDao<LabReportBasisData, String> implements ILabReportBasisDataService {

    @Reference(check = false)
    DictionnaireDetailService dictionnaireDetailService;

    @Reference(check = false)
    IRMasterDataService rMasterDataService;

    @Reference(check = false)
    IRKggDataService rKGGDataService;

    @Reference(check = false)
    IRBlqDataService rBLQDataService;

    @Reference(check = false)
    IRGenDataService rGENDataService;

    @Reference(check = false)
    IRDlDataService rDLDataService;

    @Reference(check = false)
    IRParamBasicDataService rParamBasicDataService;

    @Reference(check = false)
    ILabReportParamDataService labReportParamDataService;

    @Reference(check = false)
    ILabBlqReportParamDataService labBlqReportParamDataService;

    @Reference(check = false)
    ILabDlReportParamDataService labDlReportParamDataService;

    @Reference(check = false)
    ILabKggReportParamDataService labKggReportParamDataService;

    @Reference(check = false)
    ILabGenReportParamDataService labGenReportParamDataService;

    @Reference(check = false)
    ILabReportMarkStatisticService labReportMarkStatisticService;

    @Reference(check = false)
    ILabBlqReportMarkStatisticService labBlqReportMarkStatisticService;

    @Reference(check = false)
    ILabDlReportMarkStatisticService labDlReportMarkStatisticService;

    @Reference(check = false)
    ILabKggReportMarkStatisticService labKggReportMarkStatisticService;

    @Reference(check = false)
    ILabGenReportMarkStatisticService labGenReportMarkStatisticService;

    @Reference(check = false)
    DictionnaireService dictionnaireService;

    @Reference(check = false)
    BusiSiteService busiSiteService;

    @Reference(check = false)
    BusiSiteTreeService busiSiteTreeService;

    @Reference(check = false)
    ISysFileService sysFileService;

    @Reference(check = false)
    IRModelService rModelService;

    @Reference(check = false)
    IServerFilePathService serverFilePathService;

    @Reference(check = false)
    IRParamAttrBasicDataService rParamAttrBasicDataService;

    @Reference(check = false)
    IRMasterDataRecordService rMasterDataRecordService;

    @Reference(check = false)
    IRBlqDataRecordService rBlqDataRecordService;

    @Reference(check = false)
    IRDlDataRecordService rDlDataRecordService;

    @Reference(check = false)
    IRKggDataRecordService rKggDataRecordService;

    @Reference(check = false)
    IRGenDataRecordService rGenDataRecordService;

    @Reference(check = false)
    IRGenBasisDataService rGenBasisDataService;

    @Reference(check = false)
    IRBlqBasisDataService rBlqBasisDataService;

    @Reference(check = false)
    IRKggBasisDataService rKggBasisDataService;

    @Reference(check = false)
    IRDlBasisDataService rDlBasisDataService;

    @Reference(check = false)
    GisMechanicalPropertiesService gisMechanicalPropertiesService;

    @Reference(check = false)
    BusiOfflineGisBrandService busiOfflineGisBrandService;

    @Reference(check = false)
    private BusiOfflineTransformerBrandService busiOfflineTransformerBrandService;

    @Resource
    private LabReportBasisDataMapper labReportBasisDataMapper;

    @Reference(check = false)
    ParameterMakeService parameterMakeService;

    @Reference(check = false)
    private ITransformerEvaluationService transformerEvaluationService;

    @Resource
    private SftpInit sftpInit;

    @Override
    public Map<String, Object> list(LabReportBasisDataSelectVo labReportBasisDataSelectVo) {
        PageHelper.startPage(labReportBasisDataSelectVo.getPage(), labReportBasisDataSelectVo.getLimit());
        if (StrUtil.isEmpty(labReportBasisDataSelectVo.getDeleted())) {
            labReportBasisDataSelectVo.setDeleted(Constants.DEFAULT_NO);
        }
        if (StrUtil.isEmpty(labReportBasisDataSelectVo.getFileType())) {
            labReportBasisDataSelectVo.setFileType(FileTypeEnum.R_TEMPLATE.getKey());
        }
        List<LabReportBasisDataDto> labList = null;
        if (Objects.equals(labReportBasisDataSelectVo.getEquType(), SiteConstants.ExternalTypeEnum.GIS.getValue()) || Objects.equals(labReportBasisDataSelectVo.getEquType(), SiteConstants.ExternalTypeEnum.BYQ.getValue())) {
            labList = labReportBasisDataMapper.list(labReportBasisDataSelectVo);
        } else if (Objects.equals(labReportBasisDataSelectVo.getEquType(), SiteConstants.ExternalTypeEnum.BLQ.getValue())) {
            labList = labReportBasisDataMapper.listBlq(labReportBasisDataSelectVo);
        } else if (Objects.equals(labReportBasisDataSelectVo.getEquType(), SiteConstants.ExternalTypeEnum.DL.getValue())) {
            labList = labReportBasisDataMapper.listDl(labReportBasisDataSelectVo);
        } else if (Objects.equals(labReportBasisDataSelectVo.getEquType(), SiteConstants.ExternalTypeEnum.CW.getValue())) {
            labList = labReportBasisDataMapper.listKgg(labReportBasisDataSelectVo);
        } else if (Objects.equals(labReportBasisDataSelectVo.getEquType(), SiteConstants.ExternalTypeEnum.GEN.getValue())) {
            labList = labReportBasisDataMapper.listGen(labReportBasisDataSelectVo);
        }
        PageInfo<LabReportBasisDataDto> page = new PageInfo<>(labList);
        List<LabReportBasisDataDto> list = page.getList();
        if (null != list || list.size() != 0) {
            for (int i = 0; i < list.size(); i++) {
                String unit = dictionnaireDetailService.findUnit(DictEnum.IEC_O_UNIT.toString(), list.get(i).getUnit());
                list.get(i).setUnit(unit);
            }
        }
        return ResultUtil.put(ConstantUtil.REQUEST_SUCCESS, page.getTotal(), "BUSINESS.QUERY.SUCCESS", page.getList());
    }

    @Override
    public Map<String, Object> findExcelList(LabReportBasisDataSelectVo labReportBasisDataSelectVo) {
        PageHelper.startPage(labReportBasisDataSelectVo.getPage(), labReportBasisDataSelectVo.getLimit());
        if (StrUtil.isEmpty(labReportBasisDataSelectVo.getDeleted())) {
            labReportBasisDataSelectVo.setDeleted(Constants.DEFAULT_NO);
        }
        if (StrUtil.isEmpty(labReportBasisDataSelectVo.getFileType())) {
            labReportBasisDataSelectVo.setFileType(FileTypeEnum.R_TEMPLATE.getKey());
        }
        Page<LabReportBasisDataDto> page = null;
        if (Objects.equals(labReportBasisDataSelectVo.getEquType(), SiteConstants.ExternalTypeEnum.GIS.getValue()) || Objects.equals(labReportBasisDataSelectVo.getEquType(), SiteConstants.ExternalTypeEnum.BYQ.getValue())) {
            page = labReportBasisDataMapper.findExcelList(labReportBasisDataSelectVo);
        } else if (Objects.equals(labReportBasisDataSelectVo.getEquType(), SiteConstants.ExternalTypeEnum.BLQ.getValue())) {
            page = labReportBasisDataMapper.findBlqExcelList(labReportBasisDataSelectVo);
        } else if (Objects.equals(labReportBasisDataSelectVo.getEquType(), SiteConstants.ExternalTypeEnum.DL.getValue())) {
            page = labReportBasisDataMapper.findDlExcelList(labReportBasisDataSelectVo);
        } else if (Objects.equals(labReportBasisDataSelectVo.getEquType(), SiteConstants.ExternalTypeEnum.CW.getValue())) {
            page = labReportBasisDataMapper.findKggExcelList(labReportBasisDataSelectVo);
        } else if (Objects.equals(labReportBasisDataSelectVo.getEquType(), SiteConstants.ExternalTypeEnum.GEN.getValue())) {
            page = labReportBasisDataMapper.findGenExcelList(labReportBasisDataSelectVo);
        }
        return ResultUtil.put(ConstantUtil.REQUEST_SUCCESS, page.getTotal(), "BUSINESS.QUERY.SUCCESS", page.getResult());
    }

    @Override
    public Map<String, Object> findAll(LabReportParamDataSelectVo labReportParamDataSelectVo) {
        return labReportParamDataService.findAll(labReportParamDataSelectVo);
    }

    @Override
    public Map<String, Object> statistics(LabReportBasisDataStatisticsWhereVo labReportBasisDataStatisticsVo) {

        // 查询字典气压相关参数
        List<String> list = Lists.newArrayList();
        List<DictionnaireDetailVO> dictionnaireDetailVOList = dictionnaireDetailService.findByLikeDName("IEC_O_UNIT", "Pa");
        if (CollectionUtil.isNotEmpty(dictionnaireDetailVOList)) {
            for (DictionnaireDetailVO dictionnaireDetailVO : dictionnaireDetailVOList) {
                String dCode = dictionnaireDetailVO.getDcode();
                String dName = dictionnaireDetailVO.getDname();
                list.add(dCode + "-" + dName);
            }
        }

        // 根据离线参数、实验项目、文件类型、主设备类型标识查询
        labReportBasisDataStatisticsVo.setDeleted(Constants.DEFAULT_NO);
        List<LabReportBasisDataStatisticsDto> labReportBasisDataStatisticsDtoList = labReportParamDataService.statistics(labReportBasisDataStatisticsVo);
        if (CollectionUtil.isNotEmpty(labReportBasisDataStatisticsDtoList)) {
            Set<String> set = new HashSet<>();
            for (LabReportBasisDataStatisticsDto labReportBasisDataStatisticsDto : labReportBasisDataStatisticsDtoList) {
                set.add(labReportBasisDataStatisticsDto.getRefId());
            }

            if (CollectionUtil.isNotEmpty(set)) {
                List<String> list1 = new ArrayList<>(set);
                for (String refId : list1) {
                    labReportBasisDataStatisticsVo.setRefId(refId);
                    labReportBasisDataStatisticsDtoList = labReportParamDataService.statistics(labReportBasisDataStatisticsVo);
                    if (CollectionUtil.isNotEmpty(labReportBasisDataStatisticsDtoList)) {
                        for (LabReportBasisDataStatisticsDto labReportBasisDataStatisticsDto : labReportBasisDataStatisticsDtoList) {
                            String dCode = labReportBasisDataStatisticsDto.getDCode();
                            String dName = labReportBasisDataStatisticsDto.getDname();
                            if (CollectionUtil.isNotEmpty(list)) {
                                boolean b = list.contains(dCode + "-" + dName);
                                if (b) {
                                    // TODO 有气压 查询最小值

                                } else {
                                    // TODO 其余查询最大值
                                }
                            }
                        }
                    }
                }
            }
        }
        return ResultUtil.put(ConstantUtil.REQUEST_SUCCESS, "BUSINESS.QUERY.SUCCESS", null);
    }

    @Override
    public int updateByRefIds(List<String> refIds, String eqType) {
        if (Objects.equals(eqType, SiteConstants.ExternalTypeEnum.GIS.getValue()) || Objects.equals(eqType, SiteConstants.ExternalTypeEnum.BYQ.getValue())) {
            return labReportBasisDataMapper.updateByRefIds(refIds);
        } else if (Objects.equals(eqType, SiteConstants.ExternalTypeEnum.BLQ.getValue())) {
            return labReportBasisDataMapper.updateBlqByRefIds(refIds);
        } else if (Objects.equals(eqType, SiteConstants.ExternalTypeEnum.DL.getValue())) {
            return labReportBasisDataMapper.updateDlByRefIds(refIds);
        } else if (Objects.equals(eqType, SiteConstants.ExternalTypeEnum.CW.getValue())) {
            return labReportBasisDataMapper.updateKggByRefIds(refIds);
        } else if (Objects.equals(eqType, SiteConstants.ExternalTypeEnum.GEN.getValue())) {
            return labReportBasisDataMapper.updateGenByRefIds(refIds);
        }
        return 0;
    }


    @Override
    public Map<String, Object> importModel(byte[] bytes, String token, String eqType, String fileName, String rootPath) {

        if (StrUtil.isEmpty(token)) {
            log.error("Token为空！");
            throw new OwlException("Token为空！");
        }

        if (StrUtil.isEmpty(eqType)) {
            log.error("eqType为空！");
            throw new OwlException("eqType为空！");
        }

        if (StringUtils.isEmpty(fileName)) {
            log.error("fileName为空！");
            throw new OwlException("fileName为空！");
        }

        if (ArrayUtil.isEmpty(bytes)) {
            log.error("文件二进制流为空！");
            throw new OwlException("文件二进制流为空！");
        }

        // 获取当前用户
        LocalUser user = UserUtil.getLocalUser(token);

        try {
            InputStream inputStream = new ByteArrayInputStream(bytes);
            MultipartFile file = new MockMultipartFile(ContentType.APPLICATION_OCTET_STREAM.toString(), inputStream);
            inputStream = file.getInputStream();
            Workbook workbook = WorkbookFactory.create(inputStream);
            if (workbook != null && workbook.getNumberOfSheets() > 0) {
                long start = System.currentTimeMillis();

                List<String> list1 = Lists.newArrayList();
                List<String> list2 = null;
                if (Objects.equals(eqType, SiteConstants.ExternalTypeEnum.GIS.getValue())) {
                    list2 = Lists.newArrayList();
                }

                // 非空验证excel单元格数据
//                nonEmptyvalidationData(workbook, eqType, list1);

                // 去除设备名称集合重复数据
//                if (CollectionUtil.isNotEmpty(list1)) {
//                    list1 = list1.stream().distinct().collect(Collectors.toList());
//                    if (list1.size() > 1) {
//                        throw new OwlException("导入EXCEL中Sheet页设备名称值不一致！");
//                    }
//                }
//
//                // 去除gis设备类型集合重复数据
//                if (Objects.equals(eqType, SiteConstants.ExternalTypeEnum.GIS.getValue())) {
//                    if (CollectionUtil.isNotEmpty(list2)) {
//                        list2 = list2.stream().distinct().collect(Collectors.toList());
//                        if (list2.size() > 1) {
//                            throw new OwlException("导入EXCEL中Sheet页设备类型（断路器/开关/快速接地刀闸/pt/ct）值不一致！");
//                        }
//                    }
//                }
                for (int i = 1; i < workbook.getNumberOfSheets(); i++) {
                    // 获取底部标签页
                    Sheet sheet = workbook.getSheetAt(i);

                    JSONObject jsonObject1 = new JSONObject();
                    JSONObject jsonObject2 = new JSONObject();
                    log.info("最后一行【{}】", sheet.getLastRowNum());
                    for (int j = 0; j < sheet.getLastRowNum() + 1; j++) {
                        if (sheet.getRow(j) != null) {
                            Cell cell = sheet.getRow(j).getCell(0);
                            if (cell != null) {
                                String cellValue = cell.toString().trim();
                                if (Objects.equals(cellValue, Constants.NO_DATA) || Objects.equals(cellValue.toLowerCase(), Constants.BEGIN)) {
                                    jsonObject1.put(Constants.FIRST_ROW, j + 1);
                                }
                                if (Objects.equals(cellValue.toLowerCase(), Constants.END) && j != sheet.getLastRowNum()) {
                                    jsonObject2.put(Constants.END_ROW, j + 1);
                                    if (Objects.equals(sheet.getRow(j + 1).getCell(0).toString().trim(), Constants.NO_DATA) || Objects.equals(sheet.getRow(j + 1).getCell(0).toString().trim().toLowerCase(), Constants.BEGIN)) {
                                        continue;
                                    } else {
                                        throw new OwlException("模板中第" + j + "行缺少Begin或/");
                                    }
                                } else if (j == sheet.getLastRowNum()) {
                                    if (Objects.equals(cell.toString().trim(), Constants.END)) {
                                        jsonObject2.put(Constants.END_ROW, j + 1);
                                        continue;
                                    } else {
                                        throw new OwlException("模板中最后一行缺少end行缺少Begin或/");
                                    }
                                }
                            }
                        }
                    }
                    if (jsonObject1.get(Constants.FIRST_ROW) == null || jsonObject1.get(Constants.FIRST_ROW) == "") {
                        throw new OwlException("模板中无Begin或/");
                    } else if (jsonObject2.get(Constants.END_ROW) == null || jsonObject2.get(Constants.END_ROW) == "") {
                        throw new OwlException("模板中无End");
                    }
                }
                inputStream.close();

                //根据设备类型查询 站名及主设备
                List<BusiTreeNodeVo> busiTreeNodeVoList = busiSiteTreeService.findMainEquipmentList(eqType.split(","));
                BusiTreeNodeVo busiTreeNodeVo = busiTreeNodeVoList.get(0);
                // 站编号
                String siteId = busiTreeNodeVo.getSiteId();
                // 变电编号
                String systemCode = busiTreeNodeVo.getSystemCode();
                // 查询变站上传路径配置项
                ServerFilePath serverFilePath = serverFilePathService.findBySiteIdAndSystemCode(siteId, systemCode);
                if (serverFilePath == null) {
                    log.error("<<<<<<<<<<上传路径为空！>>>>>>>>>>");
                    throw new OwlException("上传路径为空！");
                }

                // 站点路径
                String sitePath = serverFilePath.getSitePath();
                // 变电设备路径
                String mainEquPath = File.separator + serverFilePath.getMainEquPath();
                // 文件类型路径
                String fileType = serverFilePath.getFileType();
                String fileTypePath = File.separator + serverFilePath.getFileType();

                // 根据设备类型、上传日期、业务文件类型(离线报告、评估报告、仿真文件夹 --> 字典存储)、删除标识
                Integer count = sysFileService.findMaxCount(eqType, DateUtil.getYYMMDDTime(), fileType);
                if (count == null) {
                    count = 1;
                } else {
                    count += 1;
                }

                // 上传导入excel文件
                String folder = "use";
                String folderPath;
                try {
                    // 服务器根路径/站名路径/变电设备路径/文件类型路径
                    String destDirectory = sitePath + mainEquPath + fileTypePath;
                    folderPath = sftpInit.upload(rootPath, destDirectory, file.getInputStream(), fileName, count, folder);
                    RModelVo rModelVo = new RModelVo();
                    rModelVo.setEquType(eqType);
                    rModelVo.setBusiFileType(fileType);
                    rModelVo.setDate(DateUtil.getYYMMDDTime());
                    rModelVo.setFileName(fileName);
                    String suffix = fileName.substring(fileName.lastIndexOf('.'));
                    rModelVo.setSuffixName(suffix);
                    rModelVo.setCreateUser(user.getUid());
                    rModelVo.setFileSize(file.getSize());
                    rModelVo.setFileType(FileTypeEnum.R_TEMPLATE.getKey());

                    String path = rootPath + File.separator + destDirectory;
                    // 服务器是linux 直接存储该路径
                    rModelVo.setSuffixFilePath(path.replace(File.separator, Constants.NO_DATA));
                    rModelVo.setFilePath(folderPath.replace(File.separator, Constants.NO_DATA));
                    rModelService.saveOrUpdate(rModelVo);
                } catch (Exception e) {
                    log.error("上传失败或异常:{}", e.getMessage());
                    return ResultUtil.put(ConstantUtil.REQUEST_FAIL, "BUSINESS.TEMPLATE.NOTEXIST", "");
                }
                log.info("<<<<<<<<<<<<<<<<<<导入与上传数据花费时间>>>>>>>>>>>>>>>>>>>>>>>>>：{}", DateUtil.formatDuring(System.currentTimeMillis() - start));
            } else {
                return ResultUtil.put(ConstantUtil.REQUEST_FAIL, "BUSINESS.TEMPLATE.NOTEXIST", "");
            }
        } catch (Exception e) {
            log.error("导入异常:{}", e);
            throw new OwlException(e.getMessage());
        }
        return ResultUtil.put(ConstantUtil.REQUEST_SUCCESS, "BUSINESS.IMPORT.SUCCESS", "");
    }


    @Override
    public Map<String, Object> importFile(byte[] bytes, String token, String eqType, String fileName, String enclosure, List<Map<String, String>> fileList, String rootPath, String equipmentId, String jgId) {

        if (StrUtil.isEmpty(token)) {
            log.error("Token为空！");
            throw new OwlException("Token为空！");
        }

        if (StrUtil.isEmpty(eqType)) {
            log.error("eqType为空！");
            throw new OwlException("eqType为空！");
        }

        if (StringUtils.isEmpty(fileName)) {
            log.error("fileName为空！");
            throw new OwlException("fileName为空！");
        }

        if (ArrayUtil.isEmpty(bytes)) {
            log.error("文件二进制流为空！");
            throw new OwlException("文件二进制流为空！");
        }

        if (StringUtils.isEmpty(equipmentId)) {
            log.error("equipmentId为空！");
            throw new OwlException("equipmentId为空！");
        }

        // 获取当前用户
        LocalUser user = UserUtil.getLocalUser(token);

        try {
            InputStream inputStream = new ByteArrayInputStream(bytes);
            MultipartFile file = new MockMultipartFile(ContentType.APPLICATION_OCTET_STREAM.toString(), inputStream);
            inputStream = file.getInputStream();
            Workbook workbook = WorkbookFactory.create(inputStream);
            if (workbook != null && workbook.getNumberOfSheets() > 0) {
                long start = System.currentTimeMillis();

                List<String> list1 = Lists.newArrayList();
                List<String> list2 = null;
                if (Objects.equals(eqType, SiteConstants.ExternalTypeEnum.GIS.getValue())) {
                    list2 = Lists.newArrayList();
                }

                BusiSiteTreeVo equipmentInfo = null;
                if (Objects.equals(eqType, SiteConstants.ExternalTypeEnum.GIS.getValue()) || Objects.equals(eqType, SiteConstants.ExternalTypeEnum.CW.getValue())) {
                    equipmentInfo = labReportBasisDataMapper.findGisInfo(equipmentId, jgId);
                } else {
                    equipmentInfo = labReportBasisDataMapper.findByqInfo(equipmentId);
                }

                // 非空验证excel单元格数据
                nonEmptyvalidationExcelData(workbook, eqType, list1, list2, equipmentInfo.getSystemCode());
//                String gisType = null;
//                // 去除设备名称集合重复数据
//                if (CollectionUtil.isNotEmpty(list1)) {
//                    list1 = list1.stream().distinct().collect(Collectors.toList());
//                    if (list1.size() > 1) {
//                        throw new OwlException("导入EXCEL中Sheet页设备名称值不一致！");
//                    }
//                }
//
//                // 去除gis设备类型集合重复数据
//                if (Objects.equals(eqType, SiteConstants.ExternalTypeEnum.GIS.getValue())) {
//                    if (CollectionUtil.isNotEmpty(list2)) {
//                        list2 = list2.stream().distinct().collect(Collectors.toList());
//                        gisType = list2.get(0);
//                        if (list2.size() > 1) {
//                            throw new OwlException("导入EXCEL中Sheet页设备类型（断路器/开关/快速接地刀闸/pt/ct）值不一致！");
//                        }
//                    }
//                }

                // 初始化试验项目与试验日期
                Set<String> setTestItem = new HashSet<>();
                Set<String> setTestDate = new HashSet<>();
                initData(setTestItem, setTestDate, workbook);

                // 初始化离线报告数据
                String lastFileName = labReportBasisDataMapper.findLastFile(eqType, FileTypeEnum.R_TEMPLATE.getKey());
                if (Objects.equals(fileName, lastFileName)) {
                    initOfflineReportData(workbook, setTestItem, setTestDate, eqType, equipmentInfo);
                }

                // 离线报告及附件关联编号
                Map<String, Object> map = Maps.newConcurrentMap();
                String uUID = Uuid.getUUID();
                // 循环excel底部全部标签页数量
                for (int i = 1; i < workbook.getNumberOfSheets(); i++) {
                    // 获取底部标签页
                    Sheet sheet = workbook.getSheetAt(i);
                    String sheetName = workbook.getSheetAt(i).getSheetName();
                    // 简析报告第一部分数据
                    Row row1 = sheet.getRow(0);

                    // 设备类型
                    String equTypeId = null;
                    if (Objects.equals(eqType, SiteConstants.ExternalTypeEnum.GIS.getValue())) {
//                        equTypeId = row1.getCell(11).toString().trim().split("-")[0];
                        equTypeId = labReportBasisDataMapper.findEquTypeId(equipmentId);
                    }
                    // 设备名称
//                    String equName = row1.getCell(9).toString().trim().split("-")[1];
                    String equName = equipmentInfo.getName();
                    // 设备名称对应设备编号
//                    String equlittleId = row1.getCell(9).toString().trim().split("-")[0];
                    String equlittleId = equipmentInfo.getId();
                    String sbId = labReportBasisDataMapper.findId(equlittleId);
                    if (sbId == null || sbId == "") {
                        throw new OwlException("该设备已删除或不存在");
                    }
                    LabRMasterDataVo rMasterData = saveOrUpdateRMasterData(row1, user, eqType, uUID, sheetName, equipmentInfo);
                    if (rMasterData != null) {

                        // 清空set集合数据
                        Set<String> setTestItem1 = new HashSet<>();
                        Set<String> setTestDate1 = new HashSet<>();

                        // 获取标签页报告数量
                        JSONObject jsonObject = new JSONObject();
                        for (int j = 0; j < sheet.getLastRowNum() + 1; j++) {
                            if (sheet.getRow(j) != null) {
                                Cell cell = sheet.getRow(j).getCell(0);
                                if (cell != null) {
                                    String cellValue = cell.toString().trim();
                                    if (Objects.equals(cellValue, Constants.NO_DATA) || Objects.equals(cellValue.toLowerCase(), Constants.BEGIN)) {
                                        jsonObject.put(Constants.FIRST_ROW, j + 1);
                                        jsonObject.put(Constants.BEGIN, cellValue);
                                    }
                                    if (Objects.equals(cellValue.toLowerCase(), Constants.END)) {
                                        jsonObject.put(Constants.END_ROW, j + 1);
                                        if (Objects.equals(jsonObject.get(Constants.BEGIN), Constants.NO_DATA)) {
                                            continue;
                                        }
                                        if (jsonObject.get(Constants.FIRST_ROW) != null && jsonObject.get(Constants.END_ROW) != null) {
                                            // 起始行号
                                            int firstRow = Integer.valueOf(jsonObject.get(Constants.FIRST_ROW).toString()).intValue();
                                            // 结束行号
                                            int endRow = Integer.valueOf(jsonObject.get(Constants.END_ROW).toString()).intValue();

                                            if (firstRow > 0 && endRow > 0) {
                                                LabReportBasisData labReportBasisData = new LabReportBasisData();

                                                // 试验项目
                                                String testItem = sheet.getRow(firstRow).getCell(0).toString().trim();
                                                labReportBasisData.setTestItem(testItem);
                                                setTestItem1.add(testItem);

                                                // 试验设备
                                                String testEqu = Constants.NO_DATA;
                                                if (sheet.getRow(firstRow + 1).getCell(1) != null) {
                                                    if (StrUtil.isNotEmpty(sheet.getRow(firstRow + 1).getCell(1).toString())) {
                                                        testEqu = sheet.getRow(firstRow + 1).getCell(1).toString().trim();
                                                    }
                                                }
                                                labReportBasisData.setTestEqu(testEqu);

                                                // 校核者
                                                String checker = Constants.NO_DATA;
                                                if (sheet.getRow(firstRow + 1).getCell(3) != null) {
                                                    if (StrUtil.isNotEmpty(sheet.getRow(firstRow + 1).getCell(3).toString())) {
                                                        checker = sheet.getRow(firstRow + 1).getCell(3).toString().trim();
                                                    }
                                                }
                                                labReportBasisData.setChecker(checker);

                                                // 试验日期
                                                Date date = sheet.getRow(firstRow + 1).getCell(5).getDateCellValue();
                                                LocalDate testDate = date.toInstant().atZone(ZoneId.systemDefault()).toLocalDate();
                                                labReportBasisData.setTestDate(testDate);
                                                String tDate = testDate.format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
                                                setTestDate1.add(tDate);

                                                // 检修单位
                                                String maintenanceUnit = Constants.NO_DATA;
                                                if (sheet.getRow(firstRow + 1).getCell(7) != null) {
                                                    if (StrUtil.isNotEmpty(sheet.getRow(firstRow + 1).getCell(7).toString())) {
                                                        maintenanceUnit = sheet.getRow(firstRow + 1).getCell(7).toString().trim();
                                                    }
                                                }
                                                labReportBasisData.setMaintenanceUnit(maintenanceUnit);

                                                // 试验人员
                                                String testStaff = Constants.NO_DATA;
                                                if (sheet.getRow(firstRow + 1).getCell(9) != null) {
                                                    if (StrUtil.isNotEmpty(sheet.getRow(firstRow + 1).getCell(9).toString())) {
                                                        testStaff = sheet.getRow(firstRow + 1).getCell(9).toString().trim();
                                                    }
                                                }
                                                labReportBasisData.setTestStaff(testStaff);

                                                // 试验结论
                                                String testConclusion = Constants.NO_DATA;
                                                if (sheet.getRow(firstRow + 2).getCell(1) != null) {
                                                    if (StrUtil.isNotEmpty(sheet.getRow(firstRow + 2).getCell(1).toString())) {
                                                        testConclusion = sheet.getRow(firstRow + 2).getCell(1).toString().trim();
                                                    }
                                                }
                                                labReportBasisData.setTestConclusion(testConclusion);

                                                // 关联附件编号
                                                labReportBasisData.setRefId(uUID);
                                                labReportBasisData.setId(Uuid.getUUID());
                                                labReportBasisData.setMId(rMasterData.getId());
                                                labReportBasisData.setCreateUser(user.getUid());
                                                labReportBasisData.setCreateTime(LocalDateTime.now());
                                                labReportBasisData.setDeleted(Constants.DEFAULT_NO);
                                                if (Objects.equals(eqType, SiteConstants.ExternalTypeEnum.GIS.getValue()) || Objects.equals(eqType, SiteConstants.ExternalTypeEnum.BYQ.getValue())) {
                                                    this.add(labReportBasisData);
                                                } else if (Objects.equals(eqType, SiteConstants.ExternalTypeEnum.BLQ.getValue())) {
                                                    rBlqBasisDataService.insert(labReportBasisData);
                                                } else if (Objects.equals(eqType, SiteConstants.ExternalTypeEnum.DL.getValue())) {
                                                    rDlBasisDataService.insert(labReportBasisData);
                                                } else if (Objects.equals(eqType, SiteConstants.ExternalTypeEnum.CW.getValue())) {
                                                    rKggBasisDataService.insert(labReportBasisData);
                                                } else if (Objects.equals(eqType, SiteConstants.ExternalTypeEnum.GEN.getValue())) {
                                                    rGenBasisDataService.insert(labReportBasisData);
                                                }

                                                // 数据内容总条数 = 单个报告结束行号 - 数据内容首行号
                                                int totalRow = endRow - (firstRow + 4 + 1) + 1;
                                                if (totalRow > 0) {
                                                    // 报告编号
                                                    String rId = labReportBasisData.getId();
                                                    String finalTestItem = testItem;

                                                    // Gis相关试验项初始化对象
                                                    Set<Double> set = null;
                                                    JSONArray jsonArray = null;
                                                    Set<Integer> indexSet = null;
                                                    BusiOfflineGisBrand busiOfflineGisBrand = null;
                                                    GisMechanicalPropertiesDto gisMechanicalPropertiesDto = null;
                                                    if (Objects.equals(eqType, SiteConstants.ExternalTypeEnum.GIS.getValue())) {
                                                        if (i == 1 && Objects.equals(finalTestItem, Constants.T_PARAM_TEST)) {
                                                            busiOfflineGisBrand = labReportBasisDataMapper.findBusiOfflineGisBrand(equlittleId);
                                                            if (busiOfflineGisBrand == null) {
                                                                busiOfflineGisBrand = new BusiOfflineGisBrand();
                                                            }

                                                        } else if (i == 3 && Objects.equals(finalTestItem, Constants.G_MACHINE_CHARACTERISTIC_TEST)) {
                                                            set = new HashSet<>();
                                                            jsonArray = new JSONArray();
                                                            gisMechanicalPropertiesDto = new GisMechanicalPropertiesDto();
                                                            indexSet = new HashSet<>();
                                                        }
                                                    }

                                                    // 获取试验项目下列表单元格数据
                                                    for (int total = 0; total < totalRow; total++) {
                                                        // 行索引
                                                        int index = firstRow + 3 + 1 + total;
                                                        // 获取行
                                                        Row row = sheet.getRow(index);
                                                        if (row != null) {
                                                            LabReportParamDataVo labReportParamDataVo = new LabReportParamDataVo();

                                                            labReportParamDataVo.setRefId(uUID);
                                                            labReportParamDataVo.setId(Uuid.getUUID());
                                                            labReportParamDataVo.setRId(rId);

                                                            // 验证第一个单元格是否合并（试验次数）
                                                            String testIntervalMergeValue = ImportExcel.getMergeValue(sheet, index, 0);
                                                            if (StrUtil.isNotEmpty(testIntervalMergeValue)) {
                                                                labReportParamDataVo.setTestInterval(testIntervalMergeValue);
                                                            } else {
                                                                // 结束标志
                                                                if (row.getCell(0) == null) {
                                                                    labReportParamDataVo.setTestInterval(Constants.NO_DATA);
                                                                } else {
                                                                    String testInterval = row.getCell(0).toString().trim();
                                                                    if (index == endRow || Objects.equals(testInterval.toLowerCase(), Constants.END)) {
                                                                        break;
                                                                    } else {
                                                                        if (StrUtil.isNotEmpty(testInterval)) {
                                                                            labReportParamDataVo.setTestInterval(testInterval.trim());
                                                                        } else {
                                                                            labReportParamDataVo.setTestInterval(Constants.NO_DATA);
                                                                        }
                                                                    }
                                                                }
                                                            }

                                                            // 试验类别
                                                            String testTypeMergeValue = ImportExcel.getMergeValue(sheet, index, 1);
                                                            if (StrUtil.isNotBlank(testTypeMergeValue)) {
//                                                                String key = DictEnum.TEST_TYPE.getKey();
//                                                                String value = DictEnum.TEST_TYPE.getValue();
//                                                                DictionnaireDetailVO dictionnaireDetailVO = getDictValue(key, testTypeMergeValue, value);
                                                                labReportParamDataVo.setTestType(testTypeMergeValue);
                                                            } else {
                                                                if (row.getCell(1) == null || StrUtil.isEmpty(row.getCell(1).toString()) || Objects.equals(row.getCell(1).toString(), Constants.NO_DATA)) {
                                                                    labReportParamDataVo.setTestType(Constants.NO_DATA);
                                                                } else {
                                                                    String testType = row.getCell(1).toString().trim();
//                                                                    String key = DictEnum.TEST_TYPE.getKey();
//                                                                    String value = DictEnum.TEST_TYPE.getValue();
//                                                                    DictionnaireDetailVO dictionnaireDetailVO = getDictValue(key, testType.trim(), value);
                                                                    labReportParamDataVo.setTestType(testType);
                                                                }
                                                            }

                                                            // 有效值范围
                                                            if (row.getCell(3) == null) {
                                                                labReportParamDataVo.setValidValueRange(Constants.NO_DATA);
                                                            } else {
                                                                String validValueRange = row.getCell(3).toString();
                                                                if (StrUtil.isNotEmpty(validValueRange)) {
                                                                    labReportParamDataVo.setValidValueRange(validValueRange.trim());
                                                                } else {
                                                                    labReportParamDataVo.setValidValueRange(Constants.NO_DATA);
                                                                }
                                                            }

                                                            // 当前值
                                                            if (row.getCell(4) == null) {
                                                                labReportParamDataVo.setCurrentValue(Constants.NO_DATA);
                                                            } else {
                                                                String currentValue = row.getCell(4).toString();
                                                                if (StrUtil.isNotEmpty(currentValue)) {
                                                                    labReportParamDataVo.setCurrentValue(currentValue.trim());
                                                                } else {
                                                                    labReportParamDataVo.setCurrentValue(Constants.NO_DATA);
                                                                }
                                                            }

                                                            // 评估参数
                                                            if (row.getCell(7) == null || StrUtil.isEmpty(row.getCell(7).toString())) {
                                                                labReportParamDataVo.setEParam(Constants.NO_DATA);
                                                            } else {
                                                                String eParam = row.getCell(7).toString();
                                                                if (StrUtil.isNotEmpty(eParam)) {
                                                                    labReportParamDataVo.setEParam(eParam.trim());
                                                                } else {
                                                                    labReportParamDataVo.setEParam(Constants.NO_DATA);
                                                                }
                                                            }

                                                            // 试验结果
                                                            if (row.getCell(8) == null || StrUtil.isEmpty(row.getCell(8).toString()) || Objects.equals(row.getCell(8).toString().trim(), Constants.NO_DATA)) {
                                                                String testResult = Constants.NO_DATA;
                                                                String key = DictEnum.TEST_RESULT.getKey();
                                                                String value = DictEnum.TEST_RESULT.getValue();
                                                                DictionnaireDetailVO dictionnaireDetailVO = getDictValue(key, testResult, value);
                                                                labReportParamDataVo.setTestResult(dictionnaireDetailVO.getDcode());
                                                            } else {
                                                                String testResult = row.getCell(8).toString().trim();
                                                                String key = DictEnum.TEST_RESULT.getKey();
                                                                String value = DictEnum.TEST_RESULT.getValue();
                                                                DictionnaireDetailVO dictionnaireDetailVO = getDictValue(key, testResult, value);
                                                                labReportParamDataVo.setTestResult(dictionnaireDetailVO.getDcode());
                                                            }

                                                            // 离线参数
                                                            if (row.getCell(2) == null || StrUtil.isEmpty(row.getCell(2).toString())) {
                                                                labReportParamDataVo.setOfflineParam(Constants.NO_DATA);
                                                            } else {
                                                                String offlineParam = row.getCell(2).toString().trim();
                                                                labReportParamDataVo.setOfflineParam(offlineParam);
                                                            }

                                                            // 是否打标
                                                            if (row.getCell(6) == null || StrUtil.isEmpty(row.getCell(6).toString()) || Objects.equals(row.getCell(6).toString().trim(), Constants.NO_DATA)) {
                                                                labReportParamDataVo.setIsMark(Constants.NO_DATA);
                                                                // 单位
                                                                if (row.getCell(5) == null) {
                                                                    labReportParamDataVo.setUnit(Constants.NO_DATA);
                                                                } else {
                                                                    String key = DictEnum.IEC_O_UNIT.getKey();
                                                                    String value = DictEnum.IEC_O_UNIT.getValue();
                                                                    String unit = row.getCell(5).toString();
                                                                    if (StrUtil.isEmpty(unit) || Objects.equals(unit.trim(), Constants.NO_DATA)) {
                                                                        unit = Constants.EMPTY;
                                                                    }
                                                                    DictionnaireDetailVO dictionnaireDetailVO = getDictValue(key, unit.trim(), value);
                                                                    labReportParamDataVo.setUnit(dictionnaireDetailVO.getDcode());
                                                                }
                                                            } else {
                                                                String isMark = row.getCell(6).toString().trim();
                                                                String markParam = row.getCell(7).toString().trim();
                                                                if (Objects.equals(isMark, Constants.RIGHT) && markParam != null && markParam != Constants.NO_DATA && markParam != "") {
                                                                    // 离线参数
                                                                    String offlineParam = labReportParamDataVo.getOfflineParam();
                                                                    if (StrUtil.isEmpty(offlineParam) || Objects.equals(offlineParam, Constants.NO_DATA)) {
                                                                        log.error("<<<<<<<<<<导入EXCEL中，【{}】中的【{}】不正确>>>>>>>>>>", finalTestItem, offlineParam);
                                                                        throw new OwlException("导入EXCEL中，" + finalTestItem + "中的离线参数值有误!!!");
                                                                    }

                                                                    // 当前值
                                                                    String currentValue = labReportParamDataVo.getCurrentValue();
                                                                    if (StrUtil.isEmpty(currentValue) || Objects.equals(currentValue, Constants.NO_DATA)) {
                                                                        log.error("<<<<<<<<<<导入EXCEL中，【{}】中的【{}】不正确>>>>>>>>>>", finalTestItem, currentValue);
                                                                        throw new OwlException("导入EXCEL中，" + finalTestItem + "中的当前值有误!!!");
                                                                    }

                                                                    // 评估参数
                                                                    String eParam = labReportParamDataVo.getEParam();
                                                                    if (StrUtil.isEmpty(eParam) || Objects.equals(eParam, Constants.NO_DATA) && eParam.length() != 9) {
                                                                        log.error("<<<<<<<<<<导入EXCEL中，【{}】中的【{}】不正确>>>>>>>>>>", finalTestItem, eParam);
                                                                        throw new OwlException("导入EXCEL中，" + finalTestItem + "中的评估参数有误!!!");
                                                                    }

                                                                    // 单位
                                                                    String key1 = DictEnum.IEC_O_UNIT.getKey();
                                                                    String value1 = DictEnum.IEC_O_UNIT.getValue();
                                                                    DictionnaireDetailVO dictionnaireDetailVO1;
                                                                    Cell cell1 = row.getCell(5);
                                                                    if (cell1 == null || StrUtil.isEmpty(cell1.toString()) || Objects.equals(cell1.toString().trim(), Constants.NO_DATA)) {
                                                                        dictionnaireDetailVO1 = getDictValue(key1, Constants.NO_DATA, value1);
                                                                    } else {
                                                                        dictionnaireDetailVO1 = getDictValue(key1, cell1.toString().trim(), value1);
                                                                    }
                                                                    labReportParamDataVo.setUnit(dictionnaireDetailVO1.getDcode());

                                                                    // 设备编号
                                                                    String mainEquId = rMasterData.getMainEquId();
                                                                    // 单位
                                                                    String unit = labReportParamDataVo.getUnit();
                                                                    // 根据主设备编号 试验项目 离线参数 单位
                                                                    RParamAttrBasicData rParamAttrBasicData = rParamAttrBasicDataService.findRParamAttrBasicData(mainEquId, finalTestItem, offlineParam, unit);
                                                                    if (rParamAttrBasicData == null) {
                                                                        log.error("<<<<<<<<<<导入EXCEL中，【{}】配置中的【{}】不存在>>>>>>>>>>", finalTestItem, offlineParam);
                                                                        throw new OwlException("导入EXCEL中，" + finalTestItem + "配置中的离线参数【" + offlineParam + "】不存在");
                                                                    }

                                                                    // 新增打标信息
                                                                    LabReportMarkStatisticVo labReportMarkStatisticVo = new LabReportMarkStatisticVo();
                                                                    labReportMarkStatisticVo.setId(Uuid.getUUID());
                                                                    labReportMarkStatisticVo.setMainEquipmentId(rMasterData.getEquLittleId());
                                                                    labReportMarkStatisticVo.setRefId(uUID);
                                                                    labReportMarkStatisticVo.setReportId(rId);
                                                                    labReportMarkStatisticVo.setMark(offlineParam);
                                                                    labReportMarkStatisticVo.setParamId(labReportParamDataVo.getId());
                                                                    labReportMarkStatisticVo.setValidValueRange(labReportParamDataVo.getValidValueRange());
                                                                    labReportMarkStatisticVo.setCurrentValue(labReportParamDataVo.getCurrentValue());
                                                                    labReportMarkStatisticVo.setUnit(labReportParamDataVo.getUnit());
                                                                    labReportMarkStatisticVo.setMarkId(rParamAttrBasicData.getId());

                                                                    labReportMarkStatisticVo.setEParam(eParam);
                                                                    labReportMarkStatisticVo.setEquTypeRule(eParam.substring(0, 1).toUpperCase());
                                                                    labReportMarkStatisticVo.setERule(eParam.substring(1, 5));
                                                                    labReportMarkStatisticVo.setECalculateRule(eParam.substring(5, 8));
                                                                    labReportMarkStatisticVo.setTimeRule(eParam.substring(8, 9).toUpperCase());

                                                                    labReportMarkStatisticVo.setTestInterval(labReportParamDataVo.getTestInterval());
                                                                    labReportMarkStatisticVo.setTestType(labReportParamDataVo.getTestType());
                                                                    labReportMarkStatisticVo.setRemark(labReportParamDataVo.getRemark());
                                                                    labReportMarkStatisticVo.setTestResult(labReportParamDataVo.getTestResult());
                                                                    labReportMarkStatisticVo.setTestDate(labReportBasisData.getTestDate());
                                                                    labReportMarkStatisticVo.setCreateUser(user.getUid());
                                                                    labReportMarkStatisticVo.setCreateTime(LocalDateTime.now());
                                                                    labReportMarkStatisticVo.setDeleted(Constants.DEFAULT_NO);
                                                                    if (Objects.equals(eqType, SiteConstants.ExternalTypeEnum.GIS.getValue()) || Objects.equals(eqType, SiteConstants.ExternalTypeEnum.BYQ.getValue())) {
                                                                        labReportMarkStatisticService.insert(labReportMarkStatisticVo);
                                                                    } else if (Objects.equals(eqType, SiteConstants.ExternalTypeEnum.BLQ.getValue())) {
                                                                        labBlqReportMarkStatisticService.insert(labReportMarkStatisticVo);
                                                                    } else if (Objects.equals(eqType, SiteConstants.ExternalTypeEnum.DL.getValue())) {
                                                                        labDlReportMarkStatisticService.insert(labReportMarkStatisticVo);
                                                                    } else if (Objects.equals(eqType, SiteConstants.ExternalTypeEnum.CW.getValue())) {
                                                                        labKggReportMarkStatisticService.insert(labReportMarkStatisticVo);
                                                                    } else if (Objects.equals(eqType, SiteConstants.ExternalTypeEnum.GEN.getValue())) {
                                                                        labGenReportMarkStatisticService.insert(labReportMarkStatisticVo);
                                                                    }

                                                                    // 新增字典数据
                                                                    String key = DictEnum.IS_MARK.getKey();
                                                                    String value = DictEnum.IS_MARK.getValue();
                                                                    DictionnaireDetailVO dictionnaireDetailVO = getDictValue(key, isMark, value);
                                                                    labReportParamDataVo.setIsMark(dictionnaireDetailVO.getDcode());
                                                                } else if (Objects.equals(isMark, Constants.RIGHT) && (markParam == null || markParam == Constants.NO_DATA || markParam == "")) {
                                                                    // 离线参数
                                                                    String offlineParam = labReportParamDataVo.getOfflineParam();
                                                                    if (StrUtil.isEmpty(offlineParam) || Objects.equals(offlineParam, Constants.NO_DATA)) {
                                                                        log.error("<<<<<<<<<<导入EXCEL中，【{}】中的【{}】不正确>>>>>>>>>>", finalTestItem, offlineParam);
                                                                        throw new OwlException("导入EXCEL中，" + finalTestItem + "中的离线参数值有误!!!");
                                                                    }

                                                                    // 当前值
                                                                    String currentValue = labReportParamDataVo.getCurrentValue();
                                                                    if (StrUtil.isEmpty(currentValue) || Objects.equals(currentValue, Constants.NO_DATA)) {
                                                                        log.error("<<<<<<<<<<导入EXCEL中，【{}】中的【{}】不正确>>>>>>>>>>", finalTestItem, currentValue);
                                                                        throw new OwlException("导入EXCEL中，" + finalTestItem + "中的当前值有误!!!");
                                                                    }

                                                                    // 单位
                                                                    String key1 = DictEnum.IEC_O_UNIT.getKey();
                                                                    String value1 = DictEnum.IEC_O_UNIT.getValue();
                                                                    DictionnaireDetailVO dictionnaireDetailVO1;
                                                                    Cell cell1 = row.getCell(5);
                                                                    if (cell1 == null || StrUtil.isEmpty(cell1.toString()) || Objects.equals(cell1.toString().trim(), Constants.NO_DATA)) {
                                                                        dictionnaireDetailVO1 = getDictValue(key1, Constants.UNIT, value1);
                                                                    } else {
                                                                        dictionnaireDetailVO1 = getDictValue(key1, cell1.toString().trim(), value1);
                                                                    }
                                                                    labReportParamDataVo.setUnit(dictionnaireDetailVO1.getDcode());

                                                                    // 设备编号
                                                                    String mainEquId = rMasterData.getMainEquId();
                                                                    // 单位
                                                                    String unit = labReportParamDataVo.getUnit();
                                                                    // 根据主设备编号 试验项目 离线参数 单位
                                                                    RParamAttrBasicData rParamAttrBasicData = rParamAttrBasicDataService.findRParamAttrBasicData(mainEquId, finalTestItem, offlineParam, unit);
                                                                    if (rParamAttrBasicData == null) {
                                                                        log.error("<<<<<<<<<<导入EXCEL中，【{}】配置中的【{}】不存在>>>>>>>>>>", finalTestItem, offlineParam);
                                                                        throw new OwlException("导入EXCEL中，" + finalTestItem + "配置中的离线参数【" + offlineParam + "】不存在");
                                                                    }

                                                                    // 新增打标信息
                                                                    LabReportMarkStatisticVo labReportMarkStatisticVo = new LabReportMarkStatisticVo();
                                                                    labReportMarkStatisticVo.setId(Uuid.getUUID());
                                                                    labReportMarkStatisticVo.setMainEquipmentId(rMasterData.getEquLittleId());
                                                                    labReportMarkStatisticVo.setRefId(uUID);
                                                                    labReportMarkStatisticVo.setReportId(rId);
                                                                    labReportMarkStatisticVo.setMark(offlineParam);
                                                                    labReportMarkStatisticVo.setParamId(labReportParamDataVo.getId());
                                                                    labReportMarkStatisticVo.setValidValueRange(labReportParamDataVo.getValidValueRange());
                                                                    labReportMarkStatisticVo.setCurrentValue(labReportParamDataVo.getCurrentValue());
                                                                    labReportMarkStatisticVo.setUnit(labReportParamDataVo.getUnit());
                                                                    labReportMarkStatisticVo.setMarkId(rParamAttrBasicData.getId());

                                                                    labReportMarkStatisticVo.setTestInterval(labReportParamDataVo.getTestInterval());
                                                                    labReportMarkStatisticVo.setTestType(labReportParamDataVo.getTestType());
                                                                    labReportMarkStatisticVo.setRemark(labReportParamDataVo.getRemark());
                                                                    labReportMarkStatisticVo.setTestResult(labReportParamDataVo.getTestResult());
                                                                    labReportMarkStatisticVo.setTestDate(labReportBasisData.getTestDate());
                                                                    labReportMarkStatisticVo.setCreateUser(user.getUid());
                                                                    labReportMarkStatisticVo.setCreateTime(LocalDateTime.now());
                                                                    labReportMarkStatisticVo.setDeleted(Constants.DEFAULT_NO);
                                                                    if (Objects.equals(eqType, SiteConstants.ExternalTypeEnum.GIS.getValue()) || Objects.equals(eqType, SiteConstants.ExternalTypeEnum.BYQ.getValue())) {
                                                                        labReportMarkStatisticService.insert(labReportMarkStatisticVo);
                                                                    } else if (Objects.equals(eqType, SiteConstants.ExternalTypeEnum.BLQ.getValue())) {
                                                                        labBlqReportMarkStatisticService.insert(labReportMarkStatisticVo);
                                                                    } else if (Objects.equals(eqType, SiteConstants.ExternalTypeEnum.DL.getValue())) {
                                                                        labDlReportMarkStatisticService.insert(labReportMarkStatisticVo);
                                                                    } else if (Objects.equals(eqType, SiteConstants.ExternalTypeEnum.CW.getValue())) {
                                                                        labKggReportMarkStatisticService.insert(labReportMarkStatisticVo);
                                                                    } else if (Objects.equals(eqType, SiteConstants.ExternalTypeEnum.GEN.getValue())) {
                                                                        labGenReportMarkStatisticService.insert(labReportMarkStatisticVo);
                                                                    }

                                                                    // 新增字典数据
                                                                    String key = DictEnum.IS_MARK.getKey();
                                                                    String value = DictEnum.IS_MARK.getValue();
                                                                    DictionnaireDetailVO dictionnaireDetailVO = getDictValue(key, isMark, value);
                                                                    labReportParamDataVo.setIsMark(dictionnaireDetailVO.getDcode());
                                                                } else {
                                                                    // 单位
                                                                    if (row.getCell(5) == null) {
                                                                        labReportParamDataVo.setUnit(Constants.NO_DATA);
                                                                    } else {
                                                                        String key = DictEnum.IEC_O_UNIT.getKey();
                                                                        String value = DictEnum.IEC_O_UNIT.getValue();
                                                                        String unit = row.getCell(5).toString();
                                                                        if (StrUtil.isEmpty(unit) || Objects.equals(unit.trim(), Constants.NO_DATA)) {
                                                                            unit = Constants.EMPTY;
                                                                        }
                                                                        DictionnaireDetailVO dictionnaireDetailVO = getDictValue(key, unit.trim(), value);
                                                                        labReportParamDataVo.setUnit(dictionnaireDetailVO.getDcode());
                                                                    }
                                                                }
                                                            }

                                                            // 备注
                                                            if (row.getCell(9) == null) {
                                                                labReportParamDataVo.setRemark(Constants.NO_DATA);
                                                            } else {
                                                                String remark = row.getCell(9).toString();
                                                                if (StrUtil.isNotEmpty(remark)) {
                                                                    labReportParamDataVo.setRemark(remark.trim());
                                                                } else {
                                                                    labReportParamDataVo.setRemark(Constants.NO_DATA);
                                                                }
                                                            }

                                                            labReportParamDataVo.setDeleted(Constants.DEFAULT_NO);
                                                            labReportParamDataVo.setCreateUser(user.getUid());
                                                            labReportParamDataVo.setCreateTime(LocalDateTime.now());
                                                            if (Objects.equals(eqType, SiteConstants.ExternalTypeEnum.GIS.getValue()) || Objects.equals(eqType, SiteConstants.ExternalTypeEnum.BYQ.getValue())) {
                                                                labReportParamDataService.insert(labReportParamDataVo);
                                                            } else if (Objects.equals(eqType, SiteConstants.ExternalTypeEnum.BLQ.getValue())) {
                                                                labBlqReportParamDataService.insert(labReportParamDataVo);
                                                            } else if (Objects.equals(eqType, SiteConstants.ExternalTypeEnum.DL.getValue())) {
                                                                labDlReportParamDataService.insert(labReportParamDataVo);
                                                            } else if (Objects.equals(eqType, SiteConstants.ExternalTypeEnum.CW.getValue())) {
                                                                labKggReportParamDataService.insert(labReportParamDataVo);
                                                            } else if (Objects.equals(eqType, SiteConstants.ExternalTypeEnum.GEN.getValue())) {
                                                                labGenReportParamDataService.insert(labReportParamDataVo);
                                                            }

                                                            // 新增标牌信息
                                                            if (i == 1 && Objects.equals(finalTestItem, Constants.T_PARAM_TEST)) {
                                                                String isMark = row.getCell(6).toString().trim();
                                                                if (Objects.equals(isMark, Constants.RIGHT)) {
                                                                    // 评估参数打标规则
                                                                    String eParam = labReportParamDataVo.getEParam().substring(1, 5);
                                                                    String currentValue1 = labReportParamDataVo.getCurrentValue();
                                                                    boolean flag = false;
                                                                    switch (eqType) {
                                                                        case "2":
                                                                            switch (eParam) {
                                                                                case Constants.NOMINAL_VOLTAGE:
                                                                                    busiOfflineGisBrand.setNominalVoltage(currentValue1);
                                                                                    busiOfflineGisBrand.setGisType(equTypeId);
                                                                                    break;
                                                                                case Constants.RATED_FREQUENCY:
                                                                                    busiOfflineGisBrand.setRatedFrequency(currentValue1);
                                                                                    break;
                                                                                case Constants.ELECTRIC_CURRENT:
                                                                                    busiOfflineGisBrand.setElectricCurrent(currentValue1);
                                                                                    break;
                                                                                case Constants.BREAK_TIME:
                                                                                    busiOfflineGisBrand.setBreakTime(currentValue1);
                                                                                    break;
                                                                                case Constants.PRODUCT_MATURITY:
                                                                                    busiOfflineGisBrand.setProductMaturity(currentValue1);
                                                                                    break;
                                                                                case Constants.POPULARITY:
                                                                                    busiOfflineGisBrand.setPopularity(currentValue1);
                                                                                    break;
                                                                                case Constants.FACTORY_TIME:
                                                                                case Constants.PUMPING_TIME:
                                                                                    if (StrUtil.isEmpty(currentValue1) || Objects.equals(currentValue1, Constants.NO_DATA)) {
                                                                                        busiOfflineGisBrand.setFactoryTime(null);
                                                                                    } else {
                                                                                        LocalDateTime localDateTime = DateUtil.formatLocalDateTime(row.getCell(4).getStringCellValue());
                                                                                        if (null == localDateTime) {
                                                                                            throw new OwlException("导入EXCEL中，" + finalTestItem + "中的时间格式不正确");
                                                                                        }else {
                                                                                            if (eParam.equals(Constants.FACTORY_TIME)){
                                                                                                busiOfflineGisBrand.setFactoryTime(localDateTime);
                                                                                            } else if (eParam.equals(Constants.PUMPING_TIME)) {
                                                                                                busiOfflineGisBrand.setTransportTime(localDateTime);
                                                                                            }

                                                                                        }
                                                                                    }
                                                                                    break;
                                                                                case Constants.SERVICE_TIME:
                                                                                    busiOfflineGisBrand.setServiceTime(currentValue1);
                                                                                    break;
                                                                                default:
                                                                                    flag = true;
                                                                            }

                                                                            if (flag) {
                                                                                continue;
                                                                            }
                                                                            break;
                                                                    }
                                                                }
                                                            } else if (i == 3 && Objects.equals(finalTestItem, Constants.G_MACHINE_CHARACTERISTIC_TEST)) {
                                                                switch (eqType) {
                                                                    case "2":
                                                                        // 试验次数
                                                                        String testInterval = labReportParamDataVo.getTestInterval();
                                                                        if (NumberUtil.isNumber(testInterval)) {
                                                                            set.add(Double.valueOf(testInterval));
                                                                            JSONObject jsonObject1 = new JSONObject();
                                                                            jsonObject1.put(Constants.KEY + index + "-" + Double.valueOf(testInterval), labReportParamDataVo);
                                                                            indexSet.add(index);
                                                                            jsonArray.add(jsonObject1);
                                                                        }
                                                                        break;
                                                                }
                                                            }
                                                        }
                                                    }

                                                    if (Objects.equals(eqType, SiteConstants.ExternalTypeEnum.GIS.getValue())) {
                                                        String siteId = rMasterData.getSiteId();
                                                        String equLittleId = rMasterData.getEquLittleId();
                                                        if (i == 1 && Objects.equals(finalTestItem, Constants.T_PARAM_TEST)) {
                                                            if (busiOfflineGisBrand != null) {
                                                                busiOfflineGisBrand.setSiteId(siteId);
                                                                busiOfflineGisBrand.setMainEquipmentId(equLittleId);
                                                                busiOfflineGisBrand.setGisName(equName);
                                                                busiOfflineGisBrand.setEquipmentCode(GenerateUtil.generateCode(17));
                                                                busiOfflineGisBrand.setGisType(equTypeId);
                                                                BusiOfflineGisBrandVo busiOfflineGisBrandVo = busiOfflineGisBrandService.findBusiOfflineGISBrand(siteId, equLittleId, equTypeId);
                                                                if (busiOfflineGisBrandVo == null) {
                                                                    busiOfflineGisBrand.setCreateUser(user.getUid());
                                                                    busiOfflineGisBrandService.insert(busiOfflineGisBrand);
                                                                } else {
                                                                    String id = busiOfflineGisBrandVo.getId();
                                                                    busiOfflineGisBrand.setId(id);
                                                                    busiOfflineGisBrand.setStatus(Constants.DEFAULT_YES);
                                                                    busiOfflineGisBrandService.update(busiOfflineGisBrand);
                                                                }
                                                            }
                                                        } else if (i == 3 && Objects.equals(finalTestItem, Constants.G_MACHINE_CHARACTERISTIC_TEST)) {
                                                            if (CollectionUtil.isNotEmpty(set)) {
                                                                Double max = Collections.max(set);
                                                                if (CollectionUtil.isNotEmpty(jsonArray)) {
                                                                    for (int index = 0; index < jsonArray.size(); index++) {
                                                                        if (CollectionUtil.isNotEmpty(indexSet)) {
                                                                            for (Integer s : indexSet) {
                                                                                Object o = jsonArray.getJSONObject(index).get(Constants.KEY + s + "-" + max);
                                                                                if (o == null) {
                                                                                    continue;
                                                                                } else {
                                                                                    LabReportParamDataVo labReportParamDataVo = (LabReportParamDataVo) o;
                                                                                    String eParam = labReportParamDataVo.getEParam();
                                                                                    if (eParam.length() == 9) {
                                                                                        eParam = eParam.substring(1, 5);
                                                                                        String currentValue = labReportParamDataVo.getCurrentValue();
                                                                                        switch (eParam) {
                                                                                            case Constants.CLOSE_TIME_A:
                                                                                                gisMechanicalPropertiesDto.setAGhSpeed(currentValue);
                                                                                                break;
                                                                                            case Constants.BRANCH_TIME_A:
                                                                                                gisMechanicalPropertiesDto.setAGfSpeend(currentValue);
                                                                                                break;
                                                                                            case Constants.CLOSE_ASYNCHRONOUS_TIME_A:
                                                                                                gisMechanicalPropertiesDto.setAHzBtq(currentValue);
                                                                                                break;
                                                                                            case Constants.BRANCH_ASYNCHRONOUS_TIME_A:
                                                                                                gisMechanicalPropertiesDto.setAFzBtq(currentValue);
                                                                                                break;
                                                                                            case Constants.BREAK_CURRENT_A:
                                                                                                gisMechanicalPropertiesDto.setAOpenCurrent(currentValue);
                                                                                                break;
                                                                                            case Constants.CLOSE_TIME_B:
                                                                                                gisMechanicalPropertiesDto.setBGhSpeed(currentValue);
                                                                                                break;
                                                                                            case Constants.BRANCH_TIME_B:
                                                                                                gisMechanicalPropertiesDto.setBGfSpeend(currentValue);
                                                                                                break;
                                                                                            case Constants.CLOSE_ASYNCHRONOUS_TIME_B:
                                                                                                gisMechanicalPropertiesDto.setBHzBtq(currentValue);
                                                                                                break;
                                                                                            case Constants.BRANCH_ASYNCHRONOUS_TIME_B:
                                                                                                gisMechanicalPropertiesDto.setBFzBtq(currentValue);
                                                                                                break;
                                                                                            case Constants.BREAK_CURRENT_B:
                                                                                                gisMechanicalPropertiesDto.setBOpenCurrent(currentValue);
                                                                                                break;
                                                                                            case Constants.CLOSE_TIME_C:
                                                                                                gisMechanicalPropertiesDto.setCGfSpeend(currentValue);
                                                                                                break;
                                                                                            case Constants.BREAK_TIME_C:
                                                                                                gisMechanicalPropertiesDto.setCGhSpeed(currentValue);
                                                                                                break;
                                                                                            case Constants.CLOSE_ASYNCHRONOUS_TIME_C:
                                                                                                gisMechanicalPropertiesDto.setCHzBtq(currentValue);
                                                                                                break;
                                                                                            case Constants.BRANCH_ASYNCHRONOUS_TIME_C:
                                                                                                gisMechanicalPropertiesDto.setCFzBtq(currentValue);
                                                                                                break;
                                                                                            case Constants.BREAK_CURRENT_C:
                                                                                                gisMechanicalPropertiesDto.setCOpenCurrent(currentValue);
                                                                                                break;
                                                                                            case Constants.JUST_SPLIT_DIFFERENT_TIME:
                                                                                                gisMechanicalPropertiesDto.setGfTime(currentValue);
                                                                                                break;
                                                                                        }
                                                                                    }
                                                                                }
                                                                            }
                                                                        }
                                                                    }

                                                                    if (gisMechanicalPropertiesDto != null) {
                                                                        gisMechanicalPropertiesDto.setSiteId(siteId);
                                                                        gisMechanicalPropertiesDto.setEquipmentId(equLittleId);
                                                                        gisMechanicalPropertiesService.addGisMechanicalProperties(token, gisMechanicalPropertiesDto);
                                                                    }
                                                                }
                                                            }
                                                        }
                                                    }
                                                }
                                            }
                                        }
                                    }
                                }
                            }
                        }

                        // 新增临时表数据
                        RMasterDataRecord rMasterDataRecord = new RMasterDataRecord();
                        rMasterDataRecord.setId(Uuid.getUUID());
                        rMasterDataRecord.setEquipmentType(eqType);
                        rMasterDataRecord.setSiteId(rMasterData.getSiteId());
                        rMasterDataRecord.setMainEquId(rMasterData.getMainEquId());
                        rMasterDataRecord.setEquLittleType(rMasterData.getEquLittleType());
                        rMasterDataRecord.setEquLittleId(rMasterData.getEquLittleId());
                        rMasterDataRecord.setSheetName(rMasterData.getSheetName());
                        rMasterDataRecord.setRefId(rMasterData.getRefId());
                        rMasterDataRecord.setCreateUser(user.getUid());
                        rMasterDataRecord.setCreateTime(LocalDateTime.now());
                        if (Objects.equals(eqType, SiteConstants.ExternalTypeEnum.GIS.getValue())) {
                            if (CollectionUtil.isNotEmpty(setTestItem)) {
                                String testItemStr = "";
                                for (String testItem : setTestItem) {
                                    testItemStr += testItem + ",";
                                }
                                testItemStr = testItemStr.substring(0, testItemStr.length() - 1);
                                rMasterDataRecord.setTestItem(testItemStr);
                            }
                            if (CollectionUtil.isNotEmpty(setTestDate)) {
                                String testDateStr = "";
                                for (String testDate : setTestDate) {
                                    testDateStr += testDate + ",";
                                }
                                testDateStr = testDateStr.substring(0, testDateStr.length() - 1);
                                rMasterDataRecord.setTestDate(testDateStr);
                            }
                            map.put(rMasterDataRecord.getRefId(), rMasterDataRecord);
                        } else {
                            if (CollectionUtil.isNotEmpty(setTestItem1)) {
                                String testItemStr = "";
                                for (String testItem : setTestItem1) {
                                    testItemStr += testItem + ",";
                                }
                                testItemStr = testItemStr.substring(0, testItemStr.length() - 1);
                                rMasterDataRecord.setTestItem(testItemStr);
                            }
                            if (CollectionUtil.isNotEmpty(setTestDate1)) {
                                String testDateStr = "";
                                for (String testDate : setTestDate1) {
                                    testDateStr += testDate + ",";
                                }
                                testDateStr = testDateStr.substring(0, testDateStr.length() - 1);
                                rMasterDataRecord.setTestDate(testDateStr);
                            }
                            if (Objects.equals(eqType, SiteConstants.ExternalTypeEnum.GIS.getValue()) || Objects.equals(eqType, SiteConstants.ExternalTypeEnum.BYQ.getValue())) {
                                rMasterDataRecordService.insert(rMasterDataRecord);
                            } else if (Objects.equals(eqType, SiteConstants.ExternalTypeEnum.BLQ.getValue())) {
                                rBlqDataRecordService.insert(rMasterDataRecord);
                            } else if (Objects.equals(eqType, SiteConstants.ExternalTypeEnum.DL.getValue())) {
                                rDlDataRecordService.insert(rMasterDataRecord);
                            } else if (Objects.equals(eqType, SiteConstants.ExternalTypeEnum.CW.getValue())) {
                                rKggDataRecordService.insert(rMasterDataRecord);
                            } else if (Objects.equals(eqType, SiteConstants.ExternalTypeEnum.GEN.getValue())) {
                                rGenDataRecordService.insert(rMasterDataRecord);
                            }
                        }
                    } else {
                        log.error("<<<<<<<<<<导入EXCEL中，标头中数据二次检查失败，数据库中数据不正确>>>>>>>>>>");
                        throw new OwlException("导入EXCEL中，标头中数据二次检查失败，数据库中数据不正确");
                    }
                }

                // gis取出一条数据
                if (CollectionUtil.isNotEmpty(map)) {
                    for (String key : map.keySet()) {
                        RMasterDataRecord rMasterDataRecord = (RMasterDataRecord) map.get(key);
                        if (rMasterDataRecord != null) {
                            rMasterDataRecordService.insert(rMasterDataRecord);
                        }
                    }
                }

                inputStream.close();

                //根据设备类型查询 站名及主设备
                List<BusiTreeNodeVo> busiTreeNodeVoList = busiSiteTreeService.findMainEquipmentList(eqType.split(","));
                BusiTreeNodeVo busiTreeNodeVo = busiTreeNodeVoList.get(0);
                // 站编号
                String siteId = busiTreeNodeVo.getSiteId();
                // 变电编号
                String systemCode = busiTreeNodeVo.getSystemCode();
                // 查询变站上传路径配置项
                ServerFilePath serverFilePath = serverFilePathService.findBySiteIdAndSystemCode(siteId, systemCode);
                if (serverFilePath == null) {
                    log.error("<<<<<<<<<<上传路径为空！>>>>>>>>>>");
                    throw new OwlException("上传路径为空！");
                }

                // 站点路径
                String sitePath = serverFilePath.getSitePath();
                // 变电设备路径
                String mainEquPath = File.separator + serverFilePath.getMainEquPath();
                // 文件类型路径
                String fileType = serverFilePath.getFileType();
                String fileTypePath = File.separator + serverFilePath.getFileType();

                // 根据设备类型、上传日期、业务文件类型(离线报告、评估报告、仿真文件夹 --> 字典存储)、删除标识
                Integer count = sysFileService.findMaxCount(eqType, DateUtil.getYYMMDDTime(), fileType);
                if (count == null) {
                    count = 1;
                } else {
                    count += 1;
                }

                // 上传导入excel文件
                String folder = "use";
                String folderPath;
                try {
                    // 服务器根路径/站名路径/变电设备路径/文件类型路径
                    String destDirectory = sitePath + mainEquPath + fileTypePath;
                    folderPath = sftpInit.upload(rootPath, destDirectory, file.getInputStream(), fileName, count, folder);
                    SysFileVo sysFileVo = new SysFileVo();
                    sysFileVo.setBusinessId(uUID);
                    sysFileVo.setEquType(eqType);
                    sysFileVo.setBusiFileType(fileType);
                    sysFileVo.setCount(count);
                    sysFileVo.setDate(DateUtil.getYYMMDDTime());
                    sysFileVo.setFileName(fileName);
                    String suffix = fileName.substring(fileName.lastIndexOf('.'));
                    sysFileVo.setSuffixName(suffix);
                    sysFileVo.setFileSize(file.getSize());
                    sysFileVo.setFileType(FileTypeEnum.R_TEMPLATE.getKey());

                    String path = rootPath + File.separator + destDirectory;
                    // 服务器是linux 直接存储该路径
                    sysFileVo.setSuffixFilePath(path.replace(File.separator, Constants.NO_DATA));
                    sysFileVo.setFilePath(folderPath.replace(File.separator, Constants.NO_DATA));
                    sysFileService.saveOrUpdate(sysFileVo);

                    // 上传附件文件
                    Integer finalCount = count;
//                    String[] enclosureArr = enclosure.split(",");
                    for (Map<String, String> fileMap : fileList) {
                        String name = fileMap.get("filename");
                        String byteString = fileMap.get("byte");
                        byte[] bytes1 = Base64.getDecoder().decode(byteString);
//                    for (int n = 0; n < enclosureArr.length; n++) {
//                        String name = enclosureArr[n].split("-")[0];
//                        byte[] bytes1 = Base64.getDecoder().decode(enclosureArr[n].split("-")[1]);
                        MultipartFile multipartFile = new MockMultipartFile(ContentType.APPLICATION_OCTET_STREAM.toString(), new ByteArrayInputStream(bytes1));
                        folderPath = sftpInit.upload(rootPath, destDirectory, multipartFile.getInputStream(), name, finalCount, folder);

                        SysFileVo sysFileVo1 = new SysFileVo();
                        sysFileVo1.setBusinessId(uUID);
                        sysFileVo1.setEquType(eqType);
                        sysFileVo1.setBusiFileType(fileType);
                        sysFileVo1.setCount(finalCount);
                        sysFileVo1.setFileName(name);
                        sysFileVo1.setDate(DateUtil.getYYMMDDTime());
                        String suffix1 = name.substring(name.lastIndexOf('.'));
                        sysFileVo1.setSuffixName(suffix1);
                        sysFileVo1.setFileSize(multipartFile.getSize());
                        sysFileVo1.setFileType(FileTypeEnum.ANNEX.getKey());
                        // 服务器是linux 直接存储该路径
                        sysFileVo1.setSuffixFilePath(path.replace(File.separator, Constants.NO_DATA));
                        sysFileVo1.setFilePath(folderPath.replace(File.separator, Constants.NO_DATA));
                        sysFileService.saveOrUpdate(sysFileVo1);
                    }
                } catch (Exception e) {
                    log.error("上传失败或异常:{}", e.getMessage());
                    return ResultUtil.put(ConstantUtil.REQUEST_FAIL, "BUSINESS.TEMPLATE.NOTEXIST", "");
                }
                if (eqType.equals("1")) {
                    parameterMakeService.insertTransformerParameterMake();
                    //初始化计算数据
                    transformerEvaluationService.initAndSave(token, equipmentId, siteId);
                    //更新变压器标牌信息
                    busiOfflineTransformerBrandService.updateBrand(equipmentId);

                }
                if (eqType.equals("2")) {
                    parameterMakeService.insertGisParameterMake();
                    //更新Gis标牌信息
                    busiOfflineGisBrandService.updateBrand(equipmentId);
                }
                if (eqType.equals("3")) {
                    parameterMakeService.insertSwitchgearParameterMake();
                }
                if (eqType.equals("4")) {
                    parameterMakeService.insertArresterParameterMake();
                }
                if (eqType.equals("6")) {
                    parameterMakeService.insertGeneratorParameterMake();
                }
                if (eqType.equals("7")) {
                    parameterMakeService.insertCableParameterMake();
                }

                log.info("<<<<<<<<<<<<<<<<<<导入与上传数据花费时间>>>>>>>>>>>>>>>>>>>>>>>>>：{}", DateUtil.formatDuring(System.currentTimeMillis() - start));
            } else {
                return ResultUtil.put(ConstantUtil.REQUEST_FAIL, "BUSINESS.TEMPLATE.NOTEXIST", "");
            }
        } catch (Exception e) {
            log.error("导入异常:{}", e);
            throw new OwlException(e.getMessage());
        }
        return ResultUtil.put(ConstantUtil.REQUEST_SUCCESS, "BUSINESS.IMPORT.SUCCESS", "");
    }


    /**
     * 获取字典参数编码
     *
     * @param key
     * @param value
     * @param title
     * @return
     */
    private DictionnaireDetailVO getDictValue(String key, String value, String title) {
        Dictionnaire dictionnaire = dictionnaireService.initDictionnaire(key, title);
        log.info("key:{},value:{},title:{}", key, value, title);
        return dictionnaireDetailService.initDictionnaireDetail(dictionnaire, value);
    }

    /**
     * 简析报告第一部分数据
     *
     * @param user
     * @return
     */
    private LabRMasterDataVo saveOrUpdateRMasterData(Row row, LocalUser user, String eqType, String uUID, String sheetName, BusiSiteTreeVo busiSiteTreeVo) {
        Map<String, Object> map = Maps.newConcurrentMap();
        try {
            // 站点名称
//            String siteName = row.getCell(1).toString().trim();
//            String siteId = busiSiteService.findBySiteName(siteName).getId();
            String siteId = busiSiteTreeVo.getSiteId();

            // 变电名称
//            String mvaName = row.getCell(3).toString().trim();
            String mvaName = busiSiteTreeVo.getMvaName();

            // 设备小类
            String dCode = row.getCell(5).toString();
            if (Objects.equals(eqType, SiteConstants.ExternalTypeEnum.GIS.getValue()) || Objects.equals(eqType, SiteConstants.ExternalTypeEnum.CW.getValue()) || Objects.equals(eqType, SiteConstants.ExternalTypeEnum.BLQ.getValue())) {
//                dCode = row.getCell(5).toString().trim().split("-")[0];
                dCode = busiSiteTreeVo.getParentId();
//            } else {
//                String key = DictEnum.EQU_LITTLE_TYPE.getKey();
//                String value = DictEnum.EQU_LITTLE_TYPE.getValue();
//                DictionnaireDetailVO dictionnaireDetailVO = getDictValue(key, dCode.trim(), value.toUpperCase());
//                dCode = dictionnaireDetailVO.getDcode();
            }

            // 设备编号
//            String SystemCode = row.getCell(7).toString().trim();
            String SystemCode = busiSiteTreeVo.getSystemCode();
            // 设备名称
//            String equId = row.getCell(9).toString().trim().split("-")[0];
            String equId = busiSiteTreeVo.getId();

            map.put("siteId", siteId);
            map.put("mvaId", SystemCode);
            map.put("mvaName", mvaName);
            map.put("dCode", dCode);
            map.put("sheetName", sheetName);
            map.put("equId", equId);
            map.put("eqType", eqType);
            map.put("refId", uUID);
            map.put("deleted", Constants.DEFAULT_NO);
            if (Objects.equals(eqType, SiteConstants.ExternalTypeEnum.GIS.getValue()) || Objects.equals(eqType, SiteConstants.ExternalTypeEnum.BYQ.getValue())) {
                return rMasterDataService.saveOrUpdateRMasterData(map, user);
            } else if (Objects.equals(eqType, SiteConstants.ExternalTypeEnum.CW.getValue())) {
                return rKGGDataService.saveOrUpdateRMasterData(map, user);
            } else if (Objects.equals(eqType, SiteConstants.ExternalTypeEnum.BLQ.getValue())) {
                return rBLQDataService.saveOrUpdateRMasterData(map, user);
            } else if (Objects.equals(eqType, SiteConstants.ExternalTypeEnum.GEN.getValue())) {
                return rGENDataService.saveOrUpdateRMasterData(map, user);
            } else if (Objects.equals(eqType, SiteConstants.ExternalTypeEnum.DL.getValue())) {
                return rDLDataService.saveOrUpdateRMasterData(map, user);
            }
        } catch (Exception e) {
            log.error("异常：{}", e);
        }
        return null;
    }

    /**
     * 初始化试验项目与试验日期
     *
     * @param setTestItem
     * @param setTestDate
     * @param workbook
     */
    private void initData(Set<String> setTestItem, Set<String> setTestDate, Workbook workbook) {
        for (int i = 1; i < workbook.getNumberOfSheets(); i++) {
            Sheet sheet = workbook.getSheetAt(i);
            JSONObject jsonObject = new JSONObject();
            for (int j = 0; j < sheet.getLastRowNum() + 1; j++) {
                if (sheet.getRow(j) != null) {
                    Cell cell = sheet.getRow(j).getCell(0);
                    if (cell != null) {
                        String cellValue = cell.toString().trim();
                        if (Objects.equals(cellValue, Constants.NO_DATA) || Objects.equals(cellValue.toLowerCase(), Constants.BEGIN)) {
                            jsonObject.put(Constants.FIRST_ROW, j + 1);
                            jsonObject.put(Constants.BEGIN, cellValue);
                        }
                        if (Objects.equals(cellValue.toLowerCase(), Constants.END)) {
                            jsonObject.put(Constants.END_ROW, j + 1);
                            if (Objects.equals(jsonObject.get(Constants.BEGIN), Constants.NO_DATA)) {
                                continue;
                            }
                            if (jsonObject.get(Constants.FIRST_ROW) != null && jsonObject.get(Constants.END_ROW) != null) {
                                // 起始行号索引
                                int firstRow = Integer.valueOf(jsonObject.get(Constants.FIRST_ROW).toString()).intValue();
                                // 结束行号索引
                                int endRow = Integer.valueOf(jsonObject.get(Constants.END_ROW).toString()).intValue();
                                if (firstRow > 0 && endRow > 0) {
                                    // 试验项目
                                    String testItem = sheet.getRow(firstRow).getCell(0).toString().trim();
                                    setTestItem.add(testItem);

                                    // 试验日期
                                    Date date = sheet.getRow(firstRow + 1).getCell(5).getDateCellValue();
                                    LocalDate testDate = date.toInstant().atZone(ZoneId.systemDefault()).toLocalDate();
                                    String testDate1 = testDate.format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
                                    setTestDate.add(testDate1);

                                    // 结束行
                                    Row eRow = sheet.getRow(endRow);
                                    if (eRow == null) {
                                        break;
                                    }
                                    Cell cell1 = eRow.getCell(0);
                                    if (cell1 != null) {
                                        if (Objects.equals(cell1.toString().trim().toLowerCase(), Constants.END)) {
                                            continue;
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }
    }

    /**
     * Sheet页第一行非空验证
     *
     * @param workbook
     * @param eqType
     * @param list1
     */
    private void nonEmptyvalidationData(Workbook workbook, String eqType, List<String> list1) {
        for (int i = 1; i < workbook.getNumberOfSheets(); i++) {
            Sheet sheet = workbook.getSheetAt(i);
            String sheetName = workbook.getSheetName(i);
            if (sheet == null) {
                log.error("<<<<<<<<<<导入EXCEL中无【{}】Sheet页！>>>>>>>>>>", sheetName);
                throw new OwlException("导入EXCEL中无" + sheetName + "Sheet页！");
            }

            // 验证第一行所有单元格
            Row row = sheet.getRow(0);
            if (row == null) {
                log.error("<<<<<<<<<<导入EXCEL中【{}】Sheet页第一行不存在！>>>>>>>>>>", sheetName);
                throw new OwlException("导入EXCEL中【" + sheetName + "】Sheet页第一行不存在！");
            }
            // 第一单元格 站点
            Cell firstCell = row.getCell(0);
            if (firstCell == null) {
                log.error("<<<<<<<<<<导入EXCEL中【{}】Sheet页站点为空！>>>>>>>>>>", sheetName);
                throw new OwlException("导入EXCEL中【" + sheetName + "】Sheet页站点为空！");
            }
            String firstCellStr = firstCell.toString().trim();
            if (!Objects.equals(firstCellStr, Constants.SITE)) {
                log.error("<<<<<<<<<<导入EXCEL中【{}】Sheet页站点不正确！>>>>>>>>>>", sheetName);
                throw new OwlException("导入EXCEL中【" + sheetName + "】Sheet页站点不正确！");
            }

            // 第三单元格 变电名称
            Cell threeCell = row.getCell(2);
            if (threeCell == null) {
                log.error("<<<<<<<<<<导入EXCEL中【{}】Sheet页变电名称为空！>>>>>>>>>>", sheetName);
                throw new OwlException("导入EXCEL中【" + sheetName + "】Sheet页变电名称为空！");
            }
            String threeCellStr = threeCell.toString().trim();
            if (!Objects.equals(threeCellStr, Constants.SUBSTATION_NAME)) {
                log.error("<<<<<<<<<<导入EXCEL中【{}】Sheet页变电名称不正确！>>>>>>>>>>", sheetName);
                throw new OwlException("导入EXCEL中【" + sheetName + "】Sheet页变电名称不正确！");
            }

            // 第五单元格 设备小类
            Cell fiveCell = row.getCell(4);
            if (fiveCell == null) {
                log.error("<<<<<<<<<<导入EXCEL中【{}】Sheet页设备小类为空！>>>>>>>>>>", sheetName);
                throw new OwlException("导入EXCEL中【" + sheetName + "】Sheet页设备小类为空！");
            }
            String fiveCellStr = fiveCell.toString().trim();
            if (Objects.equals(eqType, SiteConstants.ExternalTypeEnum.BYQ.getValue())) {
                if (!Objects.equals(fiveCellStr, Constants.T_EQU_LITTLE_TYPE)) {
                    log.error("<<<<<<<<<<导入EXCEL中【{}】Sheet页设备小类不正确！>>>>>>>>>>", sheetName);
                    throw new OwlException("导入EXCEL中【" + sheetName + "】Sheet页设备小类不正确！");
                }
            } else if (Objects.equals(eqType, SiteConstants.ExternalTypeEnum.GIS.getValue()) || Objects.equals(eqType, SiteConstants.ExternalTypeEnum.CW.getValue()) || Objects.equals(eqType, SiteConstants.ExternalTypeEnum.BLQ.getValue())) {
                if (!Objects.equals(fiveCellStr, Constants.G_EQU_LITTLE_TYPE)) {
                    log.error("导入EXCEL中【{}】Sheet页设备间隔不正确！>>>>>>>>>>", sheetName);
                    throw new OwlException("导入EXCEL中【" + sheetName + "】Sheet页设备间隔不正确！");
                }
            } else {
                if (!Objects.equals(fiveCellStr, Constants.A_EQU_LITTLE_TYPE)) {
                    log.error("导入EXCEL中【{}】Sheet页报告类别不正确！>>>>>>>>>>", sheetName);
                    throw new OwlException("导入EXCEL中【" + sheetName + "】Sheet页报告类别不正确！");
                }
            }

            // 第七单元格 设备编号
            Cell sevenCell = row.getCell(6);
            if (sevenCell == null) {
                log.error("<<<<<<<<<<导入EXCEL中【{}】Sheet页设备编号为空！>>>>>>>>>>", sheetName);
                throw new OwlException("导入EXCEL中【" + sheetName + "】Sheet页设备编号为空！");
            }
            String sevenCellStr = sevenCell.toString().trim();
            if (!Objects.equals(sevenCellStr, Constants.EQU_ID)) {
                log.error("<<<<<<<<<<导入EXCEL中【{}】Sheet页设备编号不正确！>>>>>>>>>>", sheetName);
                throw new OwlException("导入EXCEL中【" + sheetName + "】Sheet页设备编号不正确！");
            }

            // 第九单元格 设备名称
            Cell nineCell = row.getCell(8);
            if (nineCell == null) {
                log.error("<<<<<<<<<<导入EXCEL中【{}】Sheet页设备名称为空！>>>>>>>>>>", sheetName);
                throw new OwlException("导入EXCEL中【" + sheetName + "】Sheet页设备名称为空！");
            }
            String nineCellStr = nineCell.toString().trim();
            if (!Objects.equals(nineCellStr, Constants.EQU_NAME)) {
                log.error("<<<<<<<<<<导入EXCEL中【{}】Sheet页设备名称不正确！>>>>>>>>>>", sheetName);
                throw new OwlException("导入EXCEL中【" + sheetName + "】Sheet页设备名称不正确！");
            }
            // 第十单元格
            Cell tenCell = row.getCell(9);
            if (tenCell == null && StrUtil.isEmpty(tenCell.toString())) {
                log.error("<<<<<<<<<<导入EXCEL中【{}】Sheet页设备名称值为空！>>>>>>>>>>", sheetName);
                throw new OwlException("导入EXCEL中【" + sheetName + "】Sheet页设备名称值为空！");
            }
            String[] tenCellArr = tenCell.toString().trim().split("-");
            BusiSiteTree busiSiteTree = busiSiteTreeService.findByPK(tenCellArr[0]);
            if (busiSiteTree == null) {
                log.error("<<<<<<<<<<导入EXCEL中【{}】Sheet页设备名称值不正确！>>>>>>>>>>", sheetName);
                throw new OwlException("导入EXCEL中【" + sheetName + "】Sheet页设备名称值不正确！");
            }

            // 第十一单元格
            if (Objects.equals(eqType, SiteConstants.ExternalTypeEnum.GIS.getValue())) {
                Cell elevenCell = row.getCell(10);
                if (elevenCell == null) {
                    log.error("<<<<<<<<<<导入EXCEL中【{}】Sheet页设备类型（断路器/开关/快速接地刀闸/pt/ct）为空！>>>>>>>>>>", sheetName);
                    throw new OwlException("导入EXCEL中【" + sheetName + "】Sheet页设备类型（断路器/开关/快速接地刀闸/pt/ct）为空！");
                }
                String elevenCellStr = elevenCell.toString().trim();
                if (!Objects.equals(elevenCellStr, Constants.G_EQU_TYPE)) {
                    log.error("<<<<<<<<<<导入EXCEL中【{}】Sheet页设备类型（断路器/开关/快速接地刀闸/pt/ct）不正确！>>>>>>>>>>", sheetName);
                    throw new OwlException("导入EXCEL中【" + sheetName + "】Sheet页设备类型（断路器/开关/快速接地刀闸/pt/ct）不正确！");
                }
            }


            // 验证变每个sheet标签压器设备名称是否一致
            if (Objects.equals(eqType, SiteConstants.ExternalTypeEnum.BYQ.getValue()) || Objects.equals(eqType, SiteConstants.ExternalTypeEnum.GEN.getValue()) || Objects.equals(eqType, SiteConstants.ExternalTypeEnum.DL.getValue())) {
                list1.add(tenCell.toString().trim());
            }
        }
    }

    /**
     * 非空验证excel单元格数据
     *
     * @param workbook
     * @param eqType
     * @param list1
     * @param list2
     */
    private void nonEmptyvalidationExcelData(Workbook workbook, String eqType, List<String> list1, List<String> list2, String systemCode) {
        for (int i = 1; i < workbook.getNumberOfSheets(); i++) {
            Sheet sheet = workbook.getSheetAt(i);
            String sheetName = workbook.getSheetName(i);
            if (sheet == null) {
                log.error("<<<<<<<<<<导入EXCEL中无【{}】Sheet页！>>>>>>>>>>", sheetName);
                throw new OwlException("导入EXCEL中无" + sheetName + "Sheet页！");
            }

//            // 验证第一行所有单元格
//            Row row = sheet.getRow(0);
//            if (row == null) {
//                log.error("<<<<<<<<<<导入EXCEL中【{}】Sheet页第一行不存在！>>>>>>>>>>", sheetName);
//                throw new OwlException("导入EXCEL中【" + sheetName + "】Sheet页第一行不存在！");
//            }
//            // 第一单元格 站点
//            Cell firstCell = row.getCell(0);
//            if (firstCell == null) {
//                log.error("<<<<<<<<<<导入EXCEL中【{}】Sheet页站点为空！>>>>>>>>>>", sheetName);
//                throw new OwlException("导入EXCEL中【" + sheetName + "】Sheet页站点为空！");
//            }
//            String firstCellStr = firstCell.toString().trim();
//            if (!Objects.equals(firstCellStr, Constants.SITE)) {
//                log.error("<<<<<<<<<<导入EXCEL中【{}】Sheet页站点不正确！>>>>>>>>>>", sheetName);
//                throw new OwlException("导入EXCEL中【" + sheetName + "】Sheet页站点不正确！");
//            }
//            // 第二单元格 站点值
//            Cell twoCell = row.getCell(1);
//            if (twoCell == null && StrUtil.isEmpty(twoCell.toString())) {
//                log.error("<<<<<<<<<<导入EXCEL中【{}】Sheet页站点值为空！>>>>>>>>>>", sheetName);
//                throw new OwlException("导入EXCEL中【" + sheetName + "】Sheet页站点值为空！");
//            }
//
//            // 根据站点名称查询站点信息
//            String siteName = twoCell.toString().trim();
//            BusiSiteDto busiSiteDto = busiSiteService.findBySiteName(siteName);
//            if (busiSiteDto == null) {
//                log.error("<<<<<<<<<<导入EXCEL中【{}】Sheet页站点值不正确！>>>>>>>>>>", sheetName);
//                throw new OwlException("导入EXCEL中【" + sheetName + "】Sheet页站点值不正确！");
//            }
//
//            // 第三单元格 变电名称
//            Cell threeCell = row.getCell(2);
//            if (threeCell == null) {
//                log.error("<<<<<<<<<<导入EXCEL中【{}】Sheet页变电名称为空！>>>>>>>>>>", sheetName);
//                throw new OwlException("导入EXCEL中【" + sheetName + "】Sheet页变电名称为空！");
//            }
//            String threeCellStr = threeCell.toString().trim();
//            if (!Objects.equals(threeCellStr, Constants.SUBSTATION_NAME)) {
//                log.error("<<<<<<<<<<导入EXCEL中【{}】Sheet页变电名称不正确！>>>>>>>>>>", sheetName);
//                throw new OwlException("导入EXCEL中【" + sheetName + "】Sheet页变电名称不正确！");
//            }
//            // 第四单元格
//            String equName;
//            Cell fourCell = row.getCell(3);
//            if (fourCell == null && StrUtil.isEmpty(fourCell.toString())) {
//                log.error("<<<<<<<<<<导入EXCEL中【{}】Sheet页变电名称值为空！>>>>>>>>>>", sheetName);
//                throw new OwlException("导入EXCEL中【" + sheetName + "】Sheet页变电名称值为空！");
//            } else {
//                equName = fourCell.toString().trim();
//                BusiTreeNodeVo busiTreeNodeVo = busiSiteTreeService.findByNameAndEquType(equName, eqType);
//                if (busiTreeNodeVo == null) {
//                    log.error("<<<<<<<<<<导入EXCEL中【{}】Sheet页变电名称值不正确！>>>>>>>>>>", sheetName);
//                    throw new OwlException("导入EXCEL中【" + sheetName + "】Sheet页变电名称值不正确！");
//                }
//            }
//
//            // 第五单元格 设备小类
//            Cell fiveCell = row.getCell(4);
//            if (fiveCell == null) {
//                log.error("<<<<<<<<<<导入EXCEL中【{}】Sheet页设备小类为空！>>>>>>>>>>", sheetName);
//                throw new OwlException("导入EXCEL中【" + sheetName + "】Sheet页设备小类为空！");
//            }
//            String fiveCellStr = fiveCell.toString().trim();
//            if (Objects.equals(eqType, SiteConstants.ExternalTypeEnum.BYQ.getValue())) {
//                if (!Objects.equals(fiveCellStr, Constants.T_EQU_LITTLE_TYPE)) {
//                    log.error("<<<<<<<<<<导入EXCEL中【{}】Sheet页设备小类不正确！>>>>>>>>>>", sheetName);
//                    throw new OwlException("导入EXCEL中【" + sheetName + "】Sheet页设备小类不正确！");
//                }
//            } else if (Objects.equals(eqType, SiteConstants.ExternalTypeEnum.GIS.getValue()) || Objects.equals(eqType, SiteConstants.ExternalTypeEnum.CW.getValue()) || Objects.equals(eqType, SiteConstants.ExternalTypeEnum.BLQ.getValue())) {
//                if (!Objects.equals(fiveCellStr, Constants.G_EQU_LITTLE_TYPE)) {
//                    log.error("导入EXCEL中【{}】Sheet页设备间隔不正确！>>>>>>>>>>", sheetName);
//                    throw new OwlException("导入EXCEL中【" + sheetName + "】Sheet页设备间隔不正确！");
//                }
//            } else {
//                if (!Objects.equals(fiveCellStr, Constants.A_EQU_LITTLE_TYPE)) {
//                    log.error("导入EXCEL中【{}】Sheet页报告类别不正确！>>>>>>>>>>", sheetName);
//                    throw new OwlException("导入EXCEL中【" + sheetName + "】Sheet页报告类别不正确！");
//                }
//            }
//            // 第六单元格
//            Cell sixCell = row.getCell(5);
//            if (sixCell == null && StrUtil.isEmpty(sixCell.toString())) {
//                log.error("<<<<<<<<<<导入EXCEL中【{}】Sheet页设备小类值为空！>>>>>>>>>>", sheetName);
//                throw new OwlException("导入EXCEL中【" + sheetName + "】Sheet页设备小类值为空！");
//            }
//            // 验证gis设备间隔值是否正确
//            if (Objects.equals(eqType, SiteConstants.ExternalTypeEnum.GIS.getValue()) || Objects.equals(eqType, SiteConstants.ExternalTypeEnum.CW.getValue()) || Objects.equals(eqType, SiteConstants.ExternalTypeEnum.BLQ.getValue())) {
//                String[] sixCellStr = sixCell.toString().trim().split("-");
//                BusiSiteTree busiSiteTree = busiSiteTreeService.findByPK(sixCellStr[0]);
//                if (busiSiteTree == null) {
//                    log.error("导入EXCEL中【{}】Sheet页设备间隔值不正确！>>>>>>>>>>", sheetName);
//                    throw new OwlException("导入EXCEL中【" + sheetName + "】Sheet页设备间隔值不正确！");
//                }
//                list1.add(sixCell.toString().trim());
//            }
//
//            // 第七单元格 设备编号
//            Cell sevenCell = row.getCell(6);
//            if (sevenCell == null) {
//                log.error("<<<<<<<<<<导入EXCEL中【{}】Sheet页设备编号为空！>>>>>>>>>>", sheetName);
//                throw new OwlException("导入EXCEL中【" + sheetName + "】Sheet页设备编号为空！");
//            }
//            String sevenCellStr = sevenCell.toString().trim();
//            if (!Objects.equals(sevenCellStr, Constants.EQU_ID)) {
//                log.error("<<<<<<<<<<导入EXCEL中【{}】Sheet页设备编号不正确！>>>>>>>>>>", sheetName);
//                throw new OwlException("导入EXCEL中【" + sheetName + "】Sheet页设备编号不正确！");
//            }
//            // 第八单元格
//            Cell eightCell = row.getCell(7);
//            if (eightCell == null && StrUtil.isEmpty(eightCell.toString())) {
//                log.error("<<<<<<<<<<导入EXCEL中【{}】Sheet页设备编号值为空！>>>>>>>>>>", sheetName);
//                throw new OwlException("导入EXCEL中【" + sheetName + "】Sheet页设备编号值为空！");
//            }
//            // 设备系统编码
//            String systemCode = eightCell.toString().trim();
//            BusiTreeNodeVo busiTreeNodeVo = busiSiteTreeService.findByEquNumAndequNameAndEquType(systemCode, equName, eqType);
//            if (busiTreeNodeVo == null) {
//                log.error("<<<<<<<<<<导入EXCEL中【{}】Sheet页设备编号值不正确！>>>>>>>>>>", sheetName);
//                throw new OwlException("导入EXCEL中【" + sheetName + "】Sheet页设备编号值不正确！");
//            }
//
//            // 第九单元格 设备名称
//            Cell nineCell = row.getCell(8);
//            if (nineCell == null) {
//                log.error("<<<<<<<<<<导入EXCEL中【{}】Sheet页设备名称为空！>>>>>>>>>>", sheetName);
//                throw new OwlException("导入EXCEL中【" + sheetName + "】Sheet页设备名称为空！");
//            }
//            String nineCellStr = nineCell.toString().trim();
//            if (!Objects.equals(nineCellStr, Constants.EQU_NAME)) {
//                log.error("<<<<<<<<<<导入EXCEL中【{}】Sheet页设备名称不正确！>>>>>>>>>>", sheetName);
//                throw new OwlException("导入EXCEL中【" + sheetName + "】Sheet页设备名称不正确！");
//            }
//            // 第十单元格
//            Cell tenCell = row.getCell(9);
//            if (tenCell == null && StrUtil.isEmpty(tenCell.toString())) {
//                log.error("<<<<<<<<<<导入EXCEL中【{}】Sheet页设备名称值为空！>>>>>>>>>>", sheetName);
//                throw new OwlException("导入EXCEL中【" + sheetName + "】Sheet页设备名称值为空！");
//            }
//            String[] tenCellArr = tenCell.toString().trim().split("-");
//            BusiSiteTree busiSiteTree = busiSiteTreeService.findByPK(tenCellArr[0]);
//            if (busiSiteTree == null) {
//                log.error("<<<<<<<<<<导入EXCEL中【{}】Sheet页设备名称值不正确！>>>>>>>>>>", sheetName);
//                throw new OwlException("导入EXCEL中【" + sheetName + "】Sheet页设备名称值不正确！");
//            }
//
//            // 第十一单元格
//            if (Objects.equals(eqType, SiteConstants.ExternalTypeEnum.GIS.getValue())) {
//                Cell elevenCell = row.getCell(10);
//                if (elevenCell == null) {
//                    log.error("<<<<<<<<<<导入EXCEL中【{}】Sheet页设备类型（断路器/开关/快速接地刀闸/pt/ct）为空！>>>>>>>>>>", sheetName);
//                    throw new OwlException("导入EXCEL中【" + sheetName + "】Sheet页设备类型（断路器/开关/快速接地刀闸/pt/ct）为空！");
//                }
//                String elevenCellStr = elevenCell.toString().trim();
//                if (!Objects.equals(elevenCellStr, Constants.G_EQU_TYPE)) {
//                    log.error("<<<<<<<<<<导入EXCEL中【{}】Sheet页设备类型（断路器/开关/快速接地刀闸/pt/ct）不正确！>>>>>>>>>>", sheetName);
//                    throw new OwlException("导入EXCEL中【" + sheetName + "】Sheet页设备类型（断路器/开关/快速接地刀闸/pt/ct）不正确！");
//                }
//                // 第十二单元格 设备类型
//                Cell twelveCell = row.getCell(11);
//                String[] twelveCellArr = twelveCell.toString().trim().split("-");
//                if (twelveCell == null && StrUtil.isEmpty(twelveCell.toString())) {
//                    log.error("<<<<<<<<<<导入EXCEL中【{}】Sheet页设备类型（断路器/开关/快速接地刀闸/pt/ct）值为空！>>>>>>>>>>", sheetName);
//                    throw new OwlException("导入EXCEL中【" + sheetName + "】Sheet页设备类型（断路器/开关/快速接地刀闸/pt/ct）值为空！");
//                }
//                list2.add(twelveCellArr[0]);
//            }
//
//
//            // 验证变每个sheet标签压器设备名称是否一致
//            if (Objects.equals(eqType, SiteConstants.ExternalTypeEnum.BYQ.getValue()) || Objects.equals(eqType, SiteConstants.ExternalTypeEnum.DL.getValue()) || Objects.equals(eqType, SiteConstants.ExternalTypeEnum.GEN.getValue())) {
//                list1.add(tenCell.toString().trim());
//            }

            // 验证实验项目是否为空
            JSONObject jsonObject = new JSONObject();
            for (int j = 0; j < sheet.getLastRowNum() + 1; j++) {
                int testItemNum = i + 1;
                if (sheet.getRow(j) != null) {
                    Cell cell = sheet.getRow(j).getCell(0);
                    if (cell != null) {
                        String cellValue = cell.toString().trim();
                        if (Objects.equals(cellValue, Constants.NO_DATA) || Objects.equals(cellValue.toLowerCase(), Constants.BEGIN)) {
                            jsonObject.put(Constants.FIRST_ROW, j + 1);
                            jsonObject.put(Constants.BEGIN, cellValue);
                        }
                        if (Objects.equals(cellValue.toLowerCase(), Constants.END)) {
                            jsonObject.put(Constants.END_ROW, j + 1);
                            if (Objects.equals(jsonObject.get(Constants.BEGIN), Constants.NO_DATA)) {
                                continue;
                            }
                            if (jsonObject.get(Constants.FIRST_ROW) != null && jsonObject.get(Constants.END_ROW) != null) {
                                // 起始行号索引
                                int firstRow = Integer.valueOf(jsonObject.get(Constants.FIRST_ROW).toString()).intValue();
                                // 结束行号索引
                                int endRow = Integer.valueOf(jsonObject.get(Constants.END_ROW).toString()).intValue();
                                if (firstRow > 0 && endRow > 0) {
                                    // 试验项目
                                    Row r = sheet.getRow(firstRow);
                                    if (r == null) {
                                        log.error("<<<<<<<<<<导入EXCEL中【{}】Sheet页，第【{}】试验项目无项目名称标头！>>>>>>>>>>", sheetName, testItemNum);
                                        throw new OwlException("导入EXCEL中【" + sheetName + "】第" + testItemNum + "试验项目无项目名称标头!");
                                    }

                                    String testItem;
                                    if (r.getCell(0) == null && StrUtil.isEmpty(r.getCell(0).toString())) {
                                        log.error("<<<<<<<<<<导入EXCEL中【{}】Sheet页，第【{}】试验项目，项目名称为空！>>>>>>>>>>", sheetName, testItemNum);
                                        throw new OwlException("导入EXCEL中【" + sheetName + "】第" + testItemNum + "试验项目，项目名称为空!");
                                    } else {
                                        // 试验项目
                                        testItem = r.getCell(0).toString();
                                        // 根据试验项目与变电编号查询
                                        RParamBasicDataDto rParamBasicDataDto = rParamBasicDataService.findBySystemCodeAndTestItem(systemCode, testItem);
                                        if (rParamBasicDataDto == null) {
                                            log.error("<<<<<<<<<<导入EXCEL中【{}】Sheet页，第【{}】试验项目，字典中无该项目！>>>>>>>>>>", sheetName, testItemNum);
                                            throw new OwlException("导入EXCEL中【" + sheetName + "】第" + testItemNum + "试验项目，字典中无该项目！");
                                        }
                                    }

                                    // 试验日期
                                    Row testDateRow = sheet.getRow(firstRow + 1);
                                    // excel行号
                                    int RowNum = firstRow + 1 + 1;
                                    if (testDateRow == null) {
                                        log.error("<<<<<<<<<<导入EXCEL中【{}】Sheet页，【{}】第【{}】行为空！>>>>>>>>>>", sheetName, testItem, RowNum);
                                        throw new OwlException("导入EXCEL中【" + sheetName + "】Sheet页" + testItem + "第" + RowNum + "行为空!");
                                    }
                                    if (testDateRow.getCell(5) == null && StrUtil.isEmpty(testDateRow.getCell(5).toString())) {
                                        log.error("<<<<<<<<<<导入EXCEL中【{}】Sheet页，【{}】第【{}】行，第【{}】列缺少试验日期！>>>>>>>>>>", sheetName, testItem, RowNum, 5 + 1);
                                        throw new OwlException("导入EXCEL中【" + sheetName + "】Sheet页" + testItem + "第" + RowNum + "第" + (5 + 1) + "列缺少试验日期!");
                                    }
                                    // 验证是否日期格式
                                    if (!HSSFDateUtil.isCellDateFormatted(testDateRow.getCell(5))) {
                                        log.error("<<<<<<<<<<导入EXCEL中【{}】Sheet页，【{}】第【{}】行，第【{}】列日期格式不正确！>>>>>>>>>>", sheetName, testItem, RowNum, 5 + 1);
                                        throw new OwlException("导入EXCEL中【" + sheetName + "】Sheet页" + testItem + "第" + RowNum + "第" + (5 + 1) + "列日期格式不正确!");
                                    }

                                    // 结束行
                                    Row eRow = sheet.getRow(endRow);
                                    if (eRow == null) {
                                        break;
                                    }
                                    Cell cell1 = eRow.getCell(0);
                                    if (cell1 != null) {
                                        if (Objects.equals(cell1.toString().trim().toLowerCase(), Constants.END)) {
                                            continue;
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }
    }

    /**
     * 初始化离线报告数据
     *
     * @param setTestItem
     * @param setTestDate
     */
    private void initOfflineReportData(Workbook workbook, Set<String> setTestItem, Set<String> setTestDate, String eqType, BusiSiteTreeVo busiSiteTree) {
        if (CollectionUtil.isNotEmpty(setTestItem) && CollectionUtil.isNotEmpty(setTestDate)) {
            String testItemStr = "";
            for (String testItem : setTestItem) {
                testItemStr += testItem + ",";
            }
            testItemStr = testItemStr.substring(0, testItemStr.length() - 1);

            String testDateStr = "";
            for (String testDate : setTestDate) {
                testDateStr += testDate + ",";
            }
            testDateStr = testDateStr.substring(0, testDateStr.length() - 1);

            Set<String> setTestItem1 = new HashSet<>();
            Set<String> setTestDate1 = new HashSet<>();
            RMasterDataRecordDto rMasterDataRecord = null;
            Set<String> refIdSet = new HashSet<>();
            for (int i = 1; i < workbook.getNumberOfSheets(); i++) {
                Sheet sheet = workbook.getSheetAt(i);
                String sheetName = workbook.getSheetAt(i).getSheetName();
                Row row = sheet.getRow(0);

                // 站点名称
//                String siteName = row.getCell(1).toString().trim();
//                String siteId = busiSiteService.findBySiteName(siteName).getId();

                String siteId = busiSiteTree.getSiteId();

                // 设备小类
                String dCode = row.getCell(5).toString();
//                if (Objects.equals(eqType, SiteConstants.ExternalTypeEnum.BYQ.getValue())) {
//                    String key = DictEnum.EQU_LITTLE_TYPE.getKey();
//                    String value = DictEnum.EQU_LITTLE_TYPE.getValue();
//                    DictionnaireDetailVO dictionnaireDetailVO = getDictValue(key, dCode.trim(), value.toUpperCase());
//                    dCode = dictionnaireDetailVO.getDcode();
//                } else if (Objects.equals(eqType, SiteConstants.ExternalTypeEnum.GIS.getValue())) {
//                    dCode = row.getCell(5).toString().trim().split("-")[0];
//                }

                if (Objects.equals(eqType, SiteConstants.ExternalTypeEnum.GIS.getValue()) || Objects.equals(eqType, SiteConstants.ExternalTypeEnum.CW.getValue())) {
                    dCode = busiSiteTree.getParentId();
//                } else {
//                    String key = DictEnum.EQU_LITTLE_TYPE.getKey();
//                    String value = DictEnum.EQU_LITTLE_TYPE.getValue();
//                    DictionnaireDetailVO dictionnaireDetailVO = getDictValue(key, dCode.trim(), value.toUpperCase());
//                    dCode = dictionnaireDetailVO.getDcode();
                }

                // 设备编号
//                String SystemCode = row.getCell(7).toString().trim();
                String SystemCode = busiSiteTree.getSystemCode();
                // 设备名称
//                String equId = row.getCell(9).toString().trim().split("-")[0];
                String equId = busiSiteTree.getId();

                RMasterData rMasterData = new RMasterData();
                rMasterData.setSiteId(siteId);
                rMasterData.setMainEquId(SystemCode);
                rMasterData.setEquLittleType(dCode);
                rMasterData.setEquLittleId(equId);
                rMasterData.setEquipmentType(eqType);
                rMasterData.setSheetName(sheetName);
                if (Objects.equals(eqType, SiteConstants.ExternalTypeEnum.GIS.getValue()) || Objects.equals(eqType, SiteConstants.ExternalTypeEnum.CW.getValue())) {
                    rMasterDataRecord = rMasterDataRecordService.getRMasterDataRecord(rMasterData);
                    break;
                } else {
                    rMasterDataRecord = rMasterDataRecordService.getRMasterDataRecord(rMasterData);
                    if (rMasterDataRecord != null) {
                        String testDate1 = rMasterDataRecord.getTestDate();
                        String testItem1 = rMasterDataRecord.getTestItem();
                        setTestDate1.add(testDate1);
                        setTestItem1.add(testItem1);
                        refIdSet.add(rMasterDataRecord.getRefId());
                        continue;
                    }
                }
            }

            if (Objects.equals(eqType, SiteConstants.ExternalTypeEnum.GIS.getValue()) || Objects.equals(eqType, SiteConstants.ExternalTypeEnum.CW.getValue())) {
                if (rMasterDataRecord != null) {
                    // 数据库数据
                    String testDate1 = rMasterDataRecord.getTestDate();
                    String testItem1 = rMasterDataRecord.getTestItem();
                    String refId = rMasterDataRecord.getRefId();

                    Set<String> testDateSet = new HashSet<>(Arrays.asList(testDate1.split(",")));
                    Set<String> testDateStrSet = new HashSet<>(Arrays.asList(testDateStr.split(",")));
                    Set<String> testItemSet = new HashSet<>(Arrays.asList(testItem1.split(",")));
                    Set<String> testItemStrSet = new HashSet<>(Arrays.asList(testItemStr.split(",")));

                    // 清除数据
                    clearData(eqType, testDateSet, testDateStrSet, testItemSet, testItemStrSet, refId);
                }
            } else {
                if (CollectionUtil.isNotEmpty(setTestItem1) && CollectionUtil.isNotEmpty(setTestDate1)) {
                    String testItemStr1 = "";
                    for (String testItem : setTestItem1) {
                        testItemStr1 += testItem + ",";
                    }
                    testItemStr1 = testItemStr1.substring(0, testItemStr1.length() - 1);

                    String testDateStr1 = "";
                    for (String testDate : setTestDate1) {
                        testDateStr1 += testDate + ",";
                    }
                    testDateStr1 = testDateStr1.substring(0, testDateStr1.length() - 1);

                    Set<String> testDateSet = new HashSet<>(Arrays.asList(testDateStr1.split(",")));
                    Set<String> testDateStrSet = new HashSet<>(Arrays.asList(testDateStr.split(",")));
                    Set<String> testItemSet = new HashSet<>(Arrays.asList(testItemStr1.split(",")));
                    Set<String> testItemStrSet = new HashSet<>(Arrays.asList(testItemStr.split(",")));

                    String refId = "";
                    if (refIdSet.size() == 1) {
                        for (String s : refIdSet) {
                            refId = s;
                        }

                        // 清除数据
                        clearData(eqType, testDateSet, testDateStrSet, testItemSet, testItemStrSet, refId);
                    }
                }
            }
        }
    }

    /**
     * 清空离线报告数据
     *
     * @param eqType
     * @param testDateSet
     * @param testDateStrSet
     * @param testItemSet
     * @param testItemStrSet
     * @param refId
     */
    private void clearData(String eqType, Set<String> testDateSet, Set<String> testDateStrSet, Set<String> testItemSet, Set<String> testItemStrSet, String refId) {
        if (testDateSet.containsAll(testDateStrSet) && testItemSet.containsAll(testItemStrSet)) {
            // 初始化离线报告主数据
            rMasterDataService.updateByIdAndEquType(eqType, refId);
            if (Objects.equals(eqType, SiteConstants.ExternalTypeEnum.GIS.getValue()) || Objects.equals(eqType, SiteConstants.ExternalTypeEnum.BYQ.getValue())) {
                // 初始化试验项目数据
                labReportBasisDataMapper.delete(refId);
            } else if (Objects.equals(eqType, SiteConstants.ExternalTypeEnum.BLQ.getValue())) {
                // 初始化试验项目数据
                labReportBasisDataMapper.deleteBlq(refId);
            } else if (Objects.equals(eqType, SiteConstants.ExternalTypeEnum.DL.getValue())) {
                // 初始化试验项目数据
                labReportBasisDataMapper.deleteDl(refId);
            } else if (Objects.equals(eqType, SiteConstants.ExternalTypeEnum.CW.getValue())) {
                // 初始化试验项目数据
                labReportBasisDataMapper.deleteKgg(refId);
            } else if (Objects.equals(eqType, SiteConstants.ExternalTypeEnum.GEN.getValue())) {
                // 初始化试验项目数据
                labReportBasisDataMapper.deleteGen(refId);
            }
            // 初始化离线参数
            labReportParamDataService.delete(eqType, refId);
            // 初始化离线参数打标数据
            labReportMarkStatisticService.deleteByRIdAndTestDate(eqType, refId);
            // 初始化文件信息
            sysFileService.updateByRefIdsAndEquTypes(eqType, refId);
            // 清除临时表离线报告数据记录
            rMasterDataRecordService.delete(eqType, refId);
        }
    }

    @Override
    public List<DeviceSubclassOrTestItemListVo> deviceSubclassList(String equType) {
        List<DeviceSubclassOrTestItemListVo> deviceSubclassList = rMasterDataService.deviceSubclassList(equType);
        return deviceSubclassList;
    }

    @Override
    public List<DeviceSubclassOrTestItemListVo> testItemList(String equType) {
        List<DeviceSubclassOrTestItemListVo> list = rParamBasicDataService.testItemList(equType);
        return list;
    }
}
