package com.njcn.gather.plan.service.impl;

import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.result.ExcelImportResult;
import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.io.FileUtil;
import cn.hutool.core.util.CharsetUtil;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.core.util.ZipUtil;
import cn.hutool.extra.spring.SpringUtil;
import cn.hutool.json.JSONConfig;
import cn.hutool.json.JSONUtil;
import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.conditions.update.LambdaUpdateWrapper;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.njcn.common.pojo.enums.common.DataStateEnum;
import com.njcn.common.pojo.enums.response.CommonResponseEnum;
import com.njcn.common.pojo.exception.BusinessException;
import com.njcn.common.pojo.poi.PullDown;
import com.njcn.gather.detection.pojo.po.AdPair;
import com.njcn.gather.detection.service.IAdPariService;
import com.njcn.gather.device.mapper.PqDevMapper;
import com.njcn.gather.device.pojo.enums.*;
import com.njcn.gather.device.pojo.param.PqDevParam;
import com.njcn.gather.device.pojo.po.PqDev;
import com.njcn.gather.device.pojo.po.PqDevSub;
import com.njcn.gather.device.pojo.po.PqStandardDev;
import com.njcn.gather.device.pojo.vo.CNDevExcel;
import com.njcn.gather.device.pojo.vo.ContrastDevExcel;
import com.njcn.gather.device.pojo.vo.PqDevVO;
import com.njcn.gather.device.pojo.vo.ProvinceDevExcel;
import com.njcn.gather.device.service.IPqDevService;
import com.njcn.gather.device.service.IPqDevSubService;
import com.njcn.gather.device.service.IPqStandardDevService;
import com.njcn.gather.err.pojo.po.PqErrSys;
import com.njcn.gather.err.pojo.po.PqErrSysDtls;
import com.njcn.gather.err.service.IPqErrSysDtlsService;
import com.njcn.gather.err.service.IPqErrSysService;
import com.njcn.gather.icd.pojo.po.PqIcdPath;
import com.njcn.gather.icd.service.IPqIcdPathService;
import com.njcn.gather.monitor.pojo.po.PqMonitor;
import com.njcn.gather.monitor.service.IPqMonitorService;
import com.njcn.gather.plan.mapper.AdPlanMapper;
import com.njcn.gather.plan.mapper.AdPlanStandardDevMapper;
import com.njcn.gather.plan.pojo.enums.DataSourceEnum;
import com.njcn.gather.plan.pojo.enums.PlanReportStateEnum;
import com.njcn.gather.plan.pojo.param.AdPlanParam;
import com.njcn.gather.plan.pojo.po.AdPlan;
import com.njcn.gather.plan.pojo.po.AdPlanStandardDev;
import com.njcn.gather.plan.pojo.po.AdPlanTestConfig;
import com.njcn.gather.plan.pojo.vo.*;
import com.njcn.gather.plan.service.IAdPlanService;
import com.njcn.gather.plan.service.IAdPlanSourceService;
import com.njcn.gather.plan.service.IAdPlanStandardDevService;
import com.njcn.gather.plan.service.IAdPlanTestConfigService;
import com.njcn.gather.plan.service.IPqDevCheckHistoryService;
import com.njcn.gather.pojo.enums.DetectionResponseEnum;
import com.njcn.gather.report.pojo.po.PqReport;
import com.njcn.gather.report.service.IPqReportService;
import com.njcn.gather.script.pojo.po.PqScript;
import com.njcn.gather.script.pojo.po.PqScriptDtls;
import com.njcn.gather.script.service.IPqScriptDtlsService;
import com.njcn.gather.script.service.IPqScriptService;
import com.njcn.gather.source.pojo.po.PqSource;
import com.njcn.gather.source.service.IPqSourceService;
import com.njcn.gather.storage.pojo.param.StorageParam;
import com.njcn.gather.storage.pojo.po.SimAndDigBaseResult;
import com.njcn.gather.storage.service.SimAndDigHarmonicService;
import com.njcn.gather.storage.service.SimAndDigNonHarmonicService;
import com.njcn.gather.storage.service.TableGenService;
import com.njcn.gather.system.cfg.pojo.enums.SceneEnum;
import com.njcn.gather.system.cfg.pojo.po.SysTestConfig;
import com.njcn.gather.system.cfg.service.ISysTestConfigService;
import com.njcn.gather.system.config.PathConfig;
import com.njcn.gather.system.dictionary.pojo.po.DictData;
import com.njcn.gather.system.dictionary.pojo.po.DictTree;
import com.njcn.gather.system.dictionary.pojo.po.DictType;
import com.njcn.gather.system.dictionary.service.IDictDataService;
import com.njcn.gather.system.dictionary.service.IDictTreeService;
import com.njcn.gather.system.dictionary.service.IDictTypeService;
import com.njcn.gather.tools.report.model.constant.ReportConstant;
import com.njcn.gather.type.pojo.po.DevType;
import com.njcn.gather.type.service.IDevTypeService;
import com.njcn.gather.user.user.pojo.po.SysUser;
import com.njcn.gather.user.user.service.ISysUserService;
import com.njcn.web.utils.ExcelUtil;
import com.njcn.web.utils.HttpServletUtil;
import com.njcn.web.utils.PoiUtil;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xddf.usermodel.chart.*;
import org.apache.poi.xssf.usermodel.*;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTBarSer;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTPlotArea;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.net.URLEncoder;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @author caozehui
 * @date 2024-12-09
 */
@Slf4j
@Service
@RequiredArgsConstructor
public class AdPlanServiceImpl extends ServiceImpl<AdPlanMapper, AdPlan> implements IAdPlanService {

    private final IPqScriptService pqScriptService;
    private final IPqErrSysService pqErrSysService;
    private final IAdPlanSourceService adPlanSourceService;
    private final IPqDevService pqDevService;
    private final IDictDataService dictDataService;
    private final IPqSourceService pqSourceService;
    private final IPqScriptDtlsService pqScriptDtlsService;
    private final IDictTreeService dictTreeService;
    private final TableGenService tableGenService;
    private final ISysTestConfigService sysTestConfigService;
    private final IDevTypeService devTypeService;
    private final IDictTypeService dictTypeService;
    private final SimAndDigHarmonicService adHarmonicService;
    private final SimAndDigNonHarmonicService adNonHarmonicService;
    private final PqDevMapper pqDevMapper;
    private final IPqDevSubService pqDevSubService;
    private final IAdPlanStandardDevService adPlanStandardDevService;
    private final AdPlanStandardDevMapper adPlanStandardDevMapper;
    private final IAdPlanStandardDevService adPlanContrastStandardDevService;
    private final IPqMonitorService pqMonitorService;
    private final IPqErrSysDtlsService pqErrSysDtlsService;
    private final IPqStandardDevService pqStandardDevService;
    private final IPqIcdPathService pqIcdPathService;
    private final IAdPariService adPairService;
    private final IAdPlanTestConfigService adPlanTestConfigService;
    private final ISysUserService sysUserService;
    private final IPqDevCheckHistoryService pqDevCheckHistoryService;

    private final JdbcTemplate jdbcTemplate;
//    @Value("${report.reportDir}")
//    private String reportPath;
    private final PathConfig pathConfig;

    @Override
    public List<AdPlanVO> listAdPlan(AdPlanParam.QueryParam queryParam) {
//        Page<AdPlan> page1 = this.page(new Page<>(PageFactory.getPageNum(queryParam), PageFactory.getPageSize(queryParam)), );
//        List<AdPlan> adPlans = page1.getRecords();
//        List<AdPlanVO> adPlanVOList = adPlans.stream().map(adPlan -> {
//            AdPlanVO adPlanVO = new AdPlanVO();
//            BeanUtil.copyProperties(adPlan, adPlanVO);
//            this.setAdPlanVO(adPlanVO, adPlan);
//            return adPlanVO;
//        }).collect(Collectors.toList());
//
//        Page<AdPlanVO> page2 = new Page<>(PageFactory.getPageNum(queryParam), PageFactory.getPageSize(queryParam));
//        page2.setTotal(page1.getTotal());
//        page2.setOrders(page1.orders());
//        page2.setPages(page1.getPages());
//        page2.setRecords(adPlanVOList);
        List<AdPlan> adPlanList = this.list(this.getQueryWrapper(queryParam));
        List<AdPlanVO> planVOList = adPlanList.stream().map(adPlan -> {
            AdPlanVO adPlanVO = new AdPlanVO();
            BeanUtil.copyProperties(adPlan, adPlanVO);
            this.setAdPlanVO(adPlanVO, adPlan);
            return adPlanVO;
        }).collect(Collectors.toList());
        return planVOList;
    }

    private void setAdPlanVO(AdPlanVO adPlanVO, AdPlan adPlan) {
        adPlanVO.setCreateTime(adPlanVO.getCreateTime().replace("T", " "));
        adPlanVO.setDatasourceIds(Arrays.asList(adPlan.getDatasourceId().split(StrUtil.COMMA)));
        if (StrUtil.isNotBlank(adPlan.getReportTemplateId())) {
            PqReport report = this.baseMapper.getPqReportById(adPlan.getReportTemplateId());
            adPlanVO.setReportTemplateName(report.getName() + "_" + report.getVersion());
        }
        if (StrUtil.isNotBlank(adPlanVO.getScriptId())) {
            adPlanVO.setScriptName(pqScriptService.getPqScriptById(adPlanVO.getScriptId()).getName());
        }
        adPlanVO.setErrorSysName(pqErrSysService.getPqErrSysById(adPlanVO.getErrorSysId()).getName());

        List<PqSource> pqSourceList = adPlanSourceService.listPqSourceByPlanId(adPlanVO.getId());
        adPlanVO.setSourceIds(pqSourceList.stream().map(PqSource::getId).collect(Collectors.toList()));
        adPlanVO.setSourceName(pqSourceList.stream().map(PqSource::getName).collect(Collectors.toList()));

        PqDevParam.QueryParam queryParam1 = new PqDevParam.QueryParam();
        if (adPlan.getFatherPlanId().equals(CommonEnum.FATHER_ID.getValue())) {
            List<String> planIds = this.lambdaQuery()
                    .eq(AdPlan::getFatherPlanId, adPlan.getId())
                    .eq(AdPlan::getState, DataStateEnum.ENABLE.getCode())
                    .select(AdPlan::getId)
                    .list()
                    .stream().map(AdPlan::getId).collect(Collectors.toList());
            planIds.add(adPlan.getId());
            queryParam1.setPlanIdList(planIds);
        } else {
            queryParam1.setPlanIdList(Collections.singletonList(adPlan.getId()));
        }
        List<PqDevVO> pqDevVOList = pqDevMapper.selectByQueryParam(queryParam1);
        if (CollUtil.isNotEmpty(pqDevVOList)) {
            long count = pqDevVOList.stream().filter(pqDev -> pqDev.getCheckState() != null && pqDev.getCheckState() >= CheckStateEnum.CHECKED.getValue()).count();
            adPlanVO.setProgress((float) count / pqDevVOList.size());
        } else {
            adPlanVO.setProgress(0.0f);
        }
        if (StrUtil.isNotBlank(adPlan.getTestItem())) {
            adPlanVO.setTestItems(Arrays.asList(adPlan.getTestItem().split(StrUtil.COMMA)));
            List<DictTree> dictTreeList = dictTreeService.listByIds(adPlanVO.getTestItems());
            adPlanVO.setTestItemNameStr(dictTreeList.stream().map(DictTree::getName).reduce((a, b) -> a + "," + b).orElse(""));
        }

        List<String> planIdList = new ArrayList<>();
        planIdList.add(adPlan.getId());
        List<PqStandardDev> pqStandardDevs = adPlanStandardDevMapper.listByPlanId(planIdList);
        adPlanVO.setStandardDevMap(pqStandardDevs.stream().collect(Collectors.toMap(PqStandardDev::getId, obj -> 0)));
        adPlanVO.setStandardDevNameStr(pqStandardDevs.stream().map(PqStandardDev::getName).reduce((a, b) -> a + "," + b).orElse(""));

        if (CommonEnum.FATHER_ID.getValue().equals(adPlan.getFatherPlanId())) {
            // 查询检测配置
            AdPlanTestConfig testConfig = adPlanTestConfigService.getOne(new LambdaQueryWrapper<AdPlanTestConfig>().eq(AdPlanTestConfig::getPlanId, adPlan.getId()));
            adPlanVO.setTestConfig(testConfig);
            List<AdPlan> childrenPlan = this.lambdaQuery().eq(AdPlan::getFatherPlanId, adPlan.getId()).eq(AdPlan::getState, DataStateEnum.ENABLE.getCode()).list();
            List<String> childrenPlanIds = childrenPlan.stream().map(AdPlan::getId).collect(Collectors.toList());
            if (CollUtil.isNotEmpty(childrenPlanIds)) {
                pqStandardDevs = adPlanStandardDevMapper.listByPlanId(childrenPlanIds);
            } else {
                pqStandardDevs.clear();
            }
            Map<String, Integer> standardDevIds = adPlanVO.getStandardDevMap();
            standardDevIds.putAll(pqStandardDevs.stream().collect(Collectors.toMap(PqStandardDev::getId, obj -> 1)));
//            adPlanVO.setStandardDevNameStr(adPlanVO.getStandardDevNameStr() + pqStandardDevs.stream().map(PqStandardDev::getName).reduce((a, b) -> a + "," + b).orElse(""));
        } else {
            // 查询父计划检测配置
            AdPlanTestConfig testConfig = adPlanTestConfigService.getOne(new LambdaQueryWrapper<AdPlanTestConfig>().eq(AdPlanTestConfig::getPlanId, adPlan.getFatherPlanId()));
            adPlanVO.setTestConfig(testConfig);
        }
        // 导入的直接显示
        if (adPlan.getImportFlag().equals(DataStateEnum.ENABLE.getCode())) {
            adPlanVO.setLeaderName(adPlan.getLeader());
            adPlanVO.setMembersName(adPlan.getMembers());
        } else {
            if (StrUtil.isNotBlank(adPlan.getLeader())) {
                SysUser leader = sysUserService.getById(adPlan.getLeader());
                if (leader != null) {
                    adPlanVO.setLeaderName(leader.getName());
                }
            }
            if (StrUtil.isNotBlank(adPlan.getMembers())) {
                List<String> memberIds = Arrays.asList(adPlan.getMembers().split(StrUtil.COMMA));
                List<SysUser> memberList = sysUserService.listByIds(memberIds);
                adPlanVO.setMembersName(memberList.stream().map(SysUser::getName).reduce((a, b) -> a + "," + b).orElse(""));
            }
        }

    }

    @Override
    @Transactional
    public boolean addAdPlan(AdPlanParam param) {
        param.setName(param.getName().trim());
        this.checkRepeat(param, false, CommonEnum.FATHER_ID.getValue());
        AdPlan adPlan = new AdPlan();
        BeanUtil.copyProperties(param, adPlan);
        String planId = UUID.randomUUID().toString().replaceAll("-", "");
        adPlan.setId(planId);
        adPlan.setDatasourceId(String.join(StrUtil.COMMA, param.getDatasourceIds()));

        adPlan.setState(DataStateEnum.ENABLE.getCode());
        boolean addTestConfig = true;
        boolean addTable = true;
        if (StrUtil.isBlank(param.getFatherPlanId())) {
            // 默认为顶级检测计划
            adPlan.setFatherPlanId(CommonEnum.FATHER_ID.getValue());
        } else {
            AdPlan plan = this.getById(param.getFatherPlanId());
            adPlan.setFatherPlanId(param.getFatherPlanId());
            adPlan.setOrigin(plan.getName());
            addTestConfig = false;
            addTable = false;
        }
        adPlan.setTestState(CheckStateEnum.UNCHECKED.getValue());
        adPlan.setReportState(PlanReportStateEnum.REPORT_STATE_NOT_GENERATED.getValue());
        adPlan.setResult(CheckResultEnum.UNCHECKED.getValue());
        adPlan.setCode(this.generateCode());

        if (param.getAssociateReport() != null && param.getAssociateReport() == 1) {
            String reportTemplateName = param.getReportTemplateName();
            String[] split = reportTemplateName.split("_");
            String reportId = this.baseMapper.getReportIdByNameAndVersion(split[0], split[1]);
            if (StrUtil.isNotBlank(reportId)) {
                adPlan.setReportTemplateId(reportId);
            }
        }
        // 绑定被检设备
        if (ObjectUtil.isNotEmpty(param.getDevIds())) {
            // 新增时，绑定设备
            pqDevService.bind(planId, param.getDevIds());

            // 守时检测
            pqDevService.updatePqDevTimeCheckResult(param.getDevIds(), TimeCheckResultEnum.UNKNOWN);
        }

        //检测项
        if (CollUtil.isNotEmpty(param.getTestItems())) {
            adPlan.setTestItem(String.join(StrUtil.COMMA, param.getTestItems()));
        }
        if (CollUtil.isNotEmpty(param.getMemberIds())) {
            adPlan.setMembers(String.join(StrUtil.COMMA, param.getMemberIds()));
        }
        this.save(adPlan);

        DictData dictData = dictDataService.getDictDataById(param.getPattern());
        if (PatternEnum.CONTRAST.getValue().equals(dictData.getCode())) {
            // 关联标准设备
            if (CollUtil.isNotEmpty(param.getStandardDevIds())) {
                adPlanStandardDevService.deleteAdPlanStandardDev(param.getFatherPlanId(), param.getStandardDevIds());
                adPlanStandardDevService.addAdPlanStandardDev(planId, param.getStandardDevIds());
            }
            if (addTestConfig) {
                // 添加检测配置
                AdPlanTestConfig testConfig = param.getTestConfig();
                testConfig.setPlanId(planId);
                adPlanTestConfigService.save(testConfig);
            }
        } else {
            // 关联检测源
            adPlanSourceService.addAdPlanSource(planId, param.getSourceIds());
        }
        if (addTable) {
            tableGenService.deleteTable(Collections.singletonList(adPlan.getCode().toString()));
            tableGenService.genTable(adPlan.getCode().toString(), PatternEnum.CONTRAST.getValue().equals(dictData.getCode()));
        }

        return true;
    }

    @Override
    @Transactional
    public boolean updateAdPlan(AdPlanParam.UpdateParam param) {
        param.setName(param.getName().trim());
        AdPlan plan1 = this.getById(param.getId());

        // 更新子计划
        String fatherPlanId = plan1.getFatherPlanId();
        if (StrUtil.isNotEmpty(fatherPlanId) && !StrUtil.equals(fatherPlanId, CommonEnum.FATHER_ID.getValue())) {
            plan1.setName(param.getName().trim());
            if (CollUtil.isNotEmpty(param.getDatasourceIds())) {
                plan1.setDatasourceId(String.join(StrUtil.COMMA, param.getDatasourceIds()));
            } else {
                plan1.setDatasourceId(null);
            }
            if (CollUtil.isNotEmpty(param.getMemberIds())) {
                plan1.setMembers(String.join(StrUtil.COMMA, param.getMemberIds()));
            } else {
                plan1.setMembers(null);
            }
            this.updateBindStandardDev(param.getId(), param.getStandardDevIds());
            List<AdPlan> list = new ArrayList<>();
            // 原始绑定被检设备
            List<PqDev> oldDevList = pqDevService.list(new QueryWrapper<PqDev>().eq("Plan_Id", param.getId()).eq("State", DataStateEnum.ENABLE.getCode()));
            List<String> oldDevIds = oldDevList.stream().map(PqDev::getId).collect(Collectors.toList());
            List<String> newDevIds = param.getDevIds();
            if (CollUtil.isNotEmpty(newDevIds)) {
                plan1.setTestState(pqDevService.bind(param.getId(), newDevIds));
                // 获取并集
                List<String> unionDevIds = new ArrayList<>(CollUtil.union(oldDevIds, newDevIds));
                // 排除
                newDevIds.forEach(unionDevIds::remove);
                List<PqDev> fatherNowDevList = pqDevService.list(new QueryWrapper<PqDev>().eq("Plan_Id", fatherPlanId).eq("State", DataStateEnum.ENABLE.getCode()));
                List<String> fatherNowDevIds = fatherNowDevList.stream().map(PqDev::getId).collect(Collectors.toList());

                // 把排除绑定到主计划
                fatherNowDevIds.addAll(unionDevIds);
                pqDevService.bind(fatherPlanId, fatherNowDevIds);

            } else {
                if (CollUtil.isNotEmpty(oldDevIds)) {
                    List<PqDev> fatherNowDevList = pqDevService.list(new QueryWrapper<PqDev>().eq("Plan_Id", fatherPlanId).eq("State", DataStateEnum.ENABLE.getCode()));
                    List<String> fatherNowDevIds = fatherNowDevList.stream().map(PqDev::getId).collect(Collectors.toList());
                    fatherNowDevIds.addAll(oldDevIds);
                    // 把原始都绑定到主计划
                    pqDevService.bind(fatherPlanId, fatherNowDevIds);
                }
            }
            // 非未检测状态
            if (!plan1.getTestState().equals(CheckStateEnum.UNCHECKED.getValue())) {
                PqDevParam.QueryParam queryParam = new PqDevParam.QueryParam();
                queryParam.setPlanIdList(Collections.singletonList(plan1.getId()));
                List<PqDevVO> pqDevVOS = pqDevService.listByPlanId(queryParam);
                boolean allUnchecked = pqDevVOS.stream().allMatch(pqDevVO -> CheckStateEnum.UNCHECKED.getValue().equals(pqDevVO.getCheckState()));
                boolean allArchived = pqDevVOS.stream().allMatch(pqDevVO -> pqDevVO.getCheckState() != null && pqDevVO.getCheckState() >= CheckStateEnum.DOCUMENTED.getValue());
                if (allUnchecked) {
                    plan1.setTestState(CheckStateEnum.UNCHECKED.getValue());
                } else if (allArchived && !pqDevVOS.isEmpty()) {
                    plan1.setTestState(CheckStateEnum.CHECKED.getValue());
                } else {
                    plan1.setTestState(CheckStateEnum.CHECKING.getValue());
                }
            }


            list.add(plan1);
            return this.updateBatchById(list);
        }
        this.checkRepeat(param, true, CommonEnum.FATHER_ID.getValue());
        AdPlan plan2 = new AdPlan();

        if (param.getAssociateReport() != null) {
            if (param.getAssociateReport() == 1) {
                String reportTemplateName = param.getReportTemplateName();
                String[] split = reportTemplateName.split("_");
                String reportId = this.baseMapper.getReportIdByNameAndVersion(split[0], split[1]);
                if (StrUtil.isNotBlank(reportId)) {
                    plan2.setReportTemplateId(reportId);
                }
            } else {
                plan2.setReportTemplateId(null);
            }
        }

        if (plan1.getTestState() != CheckStateEnum.UNCHECKED.getValue()) {
            if (!plan1.getScriptId().equals(param.getScriptId())) {
                throw new BusinessException(DetectionResponseEnum.CANNOT_CHANGE_SCRIPT_WHEN_CHECKING);
            }
            if (!plan1.getErrorSysId().equals(param.getErrorSysId())) {
                throw new BusinessException(DetectionResponseEnum.CANNOT_CHANGE_ERROR_SYS_WHEN_CHECKING);
            }
        }

        BeanUtil.copyProperties(param, plan2);
        plan2.setDatasourceId(String.join(StrUtil.COMMA, param.getDatasourceIds()));
        if (CollUtil.isNotEmpty(param.getTestItems())) {
            plan2.setTestItem(String.join(StrUtil.COMMA, param.getTestItems()));
        }
        if (CollUtil.isNotEmpty(param.getMemberIds())) {
            plan2.setMembers(String.join(StrUtil.COMMA, param.getMemberIds()));
        }
        DictData dictData = dictDataService.getDictDataById(param.getPattern());
        if (PatternEnum.CONTRAST.getValue().equals(dictData.getCode())) {
            List<AdPlan> childrenPlan = this.lambdaQuery().eq(AdPlan::getFatherPlanId, param.getId()).list();
            List<String> childrenPlanIds = childrenPlan.stream().map(AdPlan::getId).collect(Collectors.toList());

            // 修改与标准设备的关联关系
            List<AdPlanStandardDev> adPlanStandardDevList = new ArrayList<>();
            if (CollUtil.isNotEmpty(childrenPlanIds)) {
                adPlanStandardDevList.addAll(adPlanStandardDevService.lambdaQuery().in(AdPlanStandardDev::getPlanId, childrenPlanIds).list());
            }

//            if (CollUtil.isNotEmpty(adPlanStandardDevList)) {
//                throw new BusinessException(DetectionResponseEnum.PLEASE_UNASSIGN_STANDARD_DEV);
//            }
            List<String> standardDevIds = adPlanStandardDevList.stream().map(AdPlanStandardDev::getStandardDevId).collect(Collectors.toList());
            param.getStandardDevIds().removeAll(standardDevIds);
            adPlanStandardDevService.updateAdPlanStandardDev(param.getId(), param.getStandardDevIds());

            // 修改与被检设备的关联关系
            PqDevParam.QueryParam param1 = new PqDevParam.QueryParam();
            param1.setPlanIdList(childrenPlanIds);
            List<PqDevVO> pqDevVOList = pqDevService.listByPlanId(param1);

//            if (CollUtil.isNotEmpty(pqDevVOList)) {
//                throw new BusinessException(DetectionResponseEnum.PLEASE_UNASSIGN_DEVICE);
//            }

            List<String> devIds = pqDevVOList.stream().map(PqDevVO::getId).collect(Collectors.toList());
            param.getDevIds().removeAll(devIds);
            // 更新检测配置
            AdPlanTestConfig testConfig = param.getTestConfig();
            adPlanTestConfigService.update(testConfig, new LambdaUpdateWrapper<AdPlanTestConfig>().eq(AdPlanTestConfig::getPlanId, testConfig.getPlanId()));

        } else {
            // 修改检测计划、检测源关联
            adPlanSourceService.updateAdPlanSource(param.getId(), param.getSourceIds());
        }
        plan2.setTestState(pqDevService.bind(param.getId(), param.getDevIds()));
        // 若修改了名称，则修改相关子计划的来源字段
        if (!plan1.getName().equals(param.getName()) && CommonEnum.FATHER_ID.getValue().equals(plan1.getFatherPlanId())) {
            this.lambdaUpdate().in(AdPlan::getFatherPlanId, plan1.getId()).eq(AdPlan::getState, DataStateEnum.ENABLE.getCode())
                    .set(AdPlan::getOrigin, param.getName()).update();
        }

        return this.updateById(plan2);
    }

    @Override
    @Transactional
    public boolean deleteAdPlan(List<String> ids, String pattern) {
        for (String id : ids) {
            PqDevParam.QueryParam queryParam = new PqDevParam.QueryParam();
            queryParam.setPlanIdList(Collections.singletonList(id));
            if (ObjectUtils.isNotEmpty(pqDevService.listByPlanId(queryParam))) {
                throw new BusinessException(DetectionResponseEnum.PLAN_HAS_DEVICE_BIND);
            }
        }

        DictData dictData = dictDataService.getDictDataById(pattern);
        if (PatternEnum.CONTRAST.getValue().equals(dictData.getCode())) {
            List<AdPlan> planList = this.lambdaQuery().in(AdPlan::getFatherPlanId, ids).eq(AdPlan::getState, DataStateEnum.ENABLE.getCode()).list();
            if (CollUtil.isNotEmpty(planList)) {
                throw new BusinessException(DetectionResponseEnum.PLAN_HAS_CHILDREN);
            }

            // 删除检测计划、标准设备关联
            for (String id : ids) {
                AdPlan plan = this.getById(id);
                if (plan.getFatherPlanId().equals(CommonEnum.FATHER_ID.getValue())) {
                    adPlanStandardDevService.deleteAdPlanStandardDev(ids);
                } else {
                    adPlanStandardDevService.lambdaUpdate().set(AdPlanStandardDev::getPlanId, plan.getFatherPlanId()).eq(AdPlanStandardDev::getPlanId, id).update();
                }
            }

            // 删除检测配置
            adPlanTestConfigService.remove(new LambdaQueryWrapper<AdPlanTestConfig>().in(AdPlanTestConfig::getPlanId, ids));
        } else {
            // 删除相关检测表格
            List<String> codeList = this.listByIds(ids).stream().map(plan -> String.valueOf(plan.getCode())).collect(Collectors.toList());
            tableGenService.deleteTable(codeList);
            // 删除检测计划、检测源关联
            adPlanSourceService.deleteAdPlanSourceByPlanIds(ids);
        }

        return this.lambdaUpdate().in(AdPlan::getId, ids).set(AdPlan::getState, DataStateEnum.DELETED.getCode()).update();
    }

    @Override
    public List<Map<String, Object>> listByPattern(String pattern) {
        List<AdPlan> adPlanList = this.lambdaQuery().eq(AdPlan::getPattern, pattern).eq(AdPlan::getState, DataStateEnum.ENABLE.getCode()).orderByDesc(AdPlan::getCreateTime).list();
        Map<Integer, List<AdPlan>> map1 = adPlanList.stream().collect(Collectors.groupingBy(AdPlan::getTestState));

        List<Map<String, Object>> result = new ArrayList<>();

        for (CheckStateEnum checkStateEnum : CheckStateEnum.values()) {
            // 检测计划的检测状态中没有 归档 状态
            if (checkStateEnum.getValue() == CheckStateEnum.DOCUMENTED.getValue()) {
                continue;
            }
            Map<String, Object> map = new HashMap<>();
            map.put("name", checkStateEnum.getMsg());
            List<Map<String, Object>> children = new ArrayList<>();
            if (map1.containsKey(checkStateEnum.getValue())) {
                map1.get(checkStateEnum.getValue()).forEach(adPlan -> {
                    Map<String, Object> child = new HashMap<>();
                    child.put("id", adPlan.getId());
                    child.put("scriptId", adPlan.getScriptId());
                    child.put("errorSysId", adPlan.getErrorSysId());
                    child.put("code", adPlan.getCode());
                    child.put("pid", adPlan.getFatherPlanId());
                    child.put("name", adPlan.getName());
                    child.put("timeCheck", adPlan.getTimeCheck());
                    child.put("dataRule", adPlan.getDataRule());
                    child.put("testState", adPlan.getTestState());

                    List<PqStandardDev> pqStandardDevs = adPlanStandardDevMapper.listByPlanId(Collections.singletonList(adPlan.getId()));
                    List<String> devTypeIdList = pqStandardDevs.stream().map(PqStandardDev::getDevType).collect(Collectors.toList());
                    if (devTypeIdList.size() > 0) {
                        List<DevType> devTypeList = devTypeService.listByIds(devTypeIdList);
                        pqStandardDevs.forEach(pqStandardDev -> {
                            DevType devType1 = devTypeList.stream().filter(devType -> devType.getId().equals(pqStandardDev.getDevType())).findFirst().get();
                            pqStandardDev.setDevType(devType1.getName());
                        });
                    }
                    child.put("standardDevs", pqStandardDevs);
                    children.add(child);
                });
            }
            map.put("children", children);
            result.add(map);
        }

        return result;
    }

    @Override
    public void visualize(List<AdPlan> planList) {
        planList.forEach(adPlan -> {
//            if (StrUtil.isNotBlank(adPlan.getPattern())) {
//                adPlan.setPattern(dictDataService.getDictDataById(adPlan.getPattern()).getName());
//            }
//            if (CommonEnum.FATHER_ID.getValue().equals(adPlan.getFatherPlanId())) {
//                adPlan.setFatherPlanId(CommonEnum.FATHER_ID.getMsg());
//            } else {
//                adPlan.setFatherPlanId(this.getById(adPlan.getFatherPlanId()).getName());
//            }
            if (StrUtil.isNotBlank(adPlan.getDatasourceId())) {
                String[] datasourceIds = adPlan.getDatasourceId().split(StrUtil.COMMA);
                adPlan.setDatasourceId(Arrays.stream(datasourceIds).map(id -> DataSourceEnum.getMsgByValue(id)).collect(Collectors.joining(StrUtil.COMMA)));
            }
            if (StrUtil.isNotBlank(adPlan.getScriptId())) {
                String[] scriptIds = adPlan.getScriptId().split(StrUtil.COMMA);
                adPlan.setScriptId(Arrays.stream(scriptIds).map(id -> pqScriptService.getPqScriptById(id).getName()).collect(Collectors.joining(StrUtil.COMMA)));
            }
            if (StrUtil.isNotBlank(adPlan.getErrorSysId())) {
                adPlan.setErrorSysId(pqErrSysService.getPqErrSysById(adPlan.getErrorSysId()).getName());
            }
            if (StrUtil.isNotBlank(adPlan.getDataRule())) {
                String dataRule = adPlan.getDataRule();
                adPlan.setDataRule(dictDataService.getDictDataById(dataRule).getName());
            }
            if (StrUtil.isNotBlank(adPlan.getReportTemplateId())) {
                PqReport report = this.baseMapper.getPqReportById(adPlan.getReportTemplateId());
                adPlan.setReportTemplateId(report.getName() + "_" + report.getVersion());
            }
            if (StrUtil.isNotBlank(adPlan.getTestItem())) {
                String[] split = adPlan.getTestItem().split(StrUtil.COMMA);
                List<DictTree> dictTreeList = dictTreeService.list(new QueryWrapper<DictTree>().in("id", split).eq("state", DataStateEnum.DELETED.getCode()));
                String testItemStr = dictTreeList.stream().map(dictTree -> dictTree.getName()).reduce((a, b) -> a + "," + b).orElse("");
                adPlan.setTestItem(testItemStr);
            }
        });
    }

    @Override
    public List<Map<String, String>> getBigTestItem(Integer reCheckType, String planId, List<String> devIds, String patternId, String scriptType) {
        List<Map<String, String>> result = new ArrayList<>();
        AdPlan adPlan = this.getById(planId);
        if (ObjectUtil.isNull(adPlan)) {
            throw new BusinessException(DetectionResponseEnum.PLAN_NOT_EXIST);
        }
        DictData dictData = dictDataService.getById(patternId);
        if (PatternEnum.CONTRAST.getValue().equals(dictData.getCode())) {
            String datasourceId = adPlan.getDatasourceId();
            if (datasourceId.contains(DataSourceEnum.REAL_DATA.getValue())
                    || datasourceId.contains(DataSourceEnum.MINUTE_STATISTICS_CP95.getValue())
                    || datasourceId.contains(DataSourceEnum.MINUTE_STATISTICS_AVG.getValue())
                    || datasourceId.contains(DataSourceEnum.MINUTE_STATISTICS_MAX.getValue())
                    || datasourceId.contains(DataSourceEnum.MINUTE_STATISTICS_MIN.getValue())) {
                String[] split = adPlan.getTestItem().split(StrUtil.COMMA);
                List<DictTree> dictTreeList = dictTreeService.list(new QueryWrapper<DictTree>().in("id", split).eq("state", DataStateEnum.DELETED.getCode()).orderByAsc("sort"));

                for (DictTree dictTree : dictTreeList) {
                    Map<String, String> map = new HashMap<>();
                    map.put("id", dictTree.getId());
                    map.put("code", dictTree.getCode());
                    map.put("scriptName", dictTree.getName());
                    result.add(map);
                }
            }

            if (StrUtil.isBlank(scriptType)) {
                if (adPlan.getDatasourceId().contains(DataSourceEnum.WAVE_DATA.getValue())) {
                    Map<String, String> map = new HashMap<>();
                    map.put("id", DataSourceEnum.WAVE_DATA.getValue());
                    map.put("code", DataSourceEnum.WAVE_DATA.getValue());
                    map.put("scriptName", DataSourceEnum.WAVE_DATA.getMsg());
                    result.add(map);
                }
            }
        } else {
            String scriptId = adPlan.getScriptId();
            List<PqScriptDtls> scriptDtlsList = pqScriptDtlsService.listPqScriptDtlByScriptId(scriptId);

            // 不合格项复检
            if (reCheckType == 0) {
//            List<SimAndDigBaseResult> allResultList = new ArrayList<>();
//            allResultList.addAll(adHarmonicService.get(scriptId, null, devId, "-1", adPlan.getCode()));
//            allResultList.addAll(adNonHarmonicService.get(scriptId, null, devId, "-1", adPlan.getCode()));
//
//            if (ObjectUtil.isNotEmpty(allResultList)) {
//                List<SimAndDigBaseResult> passList = allResultList.stream().filter(obj -> obj.getResultFlag() == 1).collect(Collectors.toList());
//                TreeSet<Integer> sortSet = passList.stream().map(SimAndDigBaseResult::getSort).collect(Collectors.toCollection(TreeSet::new));
//
//                scriptDtlsList = scriptDtlsList.stream().filter(obj -> !sortSet.contains(obj.getScriptIndex())).collect(Collectors.toList());
//            }
                Set<Integer> indexes = new HashSet<>();
                StorageParam storageParam = new StorageParam();
                storageParam.setCode(adPlan.getCode() + "");
                storageParam.setScriptId(adPlan.getScriptId());
                devIds.forEach(devId -> {
                    storageParam.setDevId(devId);
                    indexes.addAll(adHarmonicService.getIndex(storageParam));
                });
                scriptDtlsList = scriptDtlsList.stream().filter(obj -> indexes.contains(obj.getScriptIndex())).collect(Collectors.toList());
            }

            Map<String, List<PqScriptDtls>> collect = scriptDtlsList.stream()
                    .sorted(Comparator.comparing(PqScriptDtls::getScriptIndex))
                    .collect(Collectors.groupingBy(PqScriptDtls::getScriptType, LinkedHashMap::new, Collectors.toList()));

            collect.forEach((key, value) -> {
                Map<String, String> map = new HashMap<>();
                map.put("id", key);
                DictTree dictTree = dictTreeService.getById(key);
                if (ObjectUtil.isNotNull(dictTree)) {
                    map.put("code", dictTree.getCode());
                    map.put("scriptName", dictTree.getName());
                } else {
                    throw new BusinessException(DetectionResponseEnum.SCRIPT_RELATE_DICT_NOT_EXIST);
                }
                result.add(map);
            });
        }


        return result;
    }

//    @Override
//    public boolean updateTestState(String planId, List<String> devIds) {
//        if (CollUtil.isNotEmpty(devIds)) {
//            pqDevService.update(new LambdaUpdateWrapper<PqDev>()
//                    .set(PqDev::getCheckState, CheckStateEnum.CHECKING.getValue())
//                    .in(PqDev::getId, devIds));
//            return this.update(new LambdaUpdateWrapper<AdPlan>()
//                    .set(AdPlan::getTestState, CheckStateEnum.CHECKING.getValue())
//                    .eq(AdPlan::getId, planId));
//        }
//        return true;
//    }

    @Override
    public boolean updateBackTestState(String planId, List<String> devIds) {
        if (CollUtil.isNotEmpty(devIds)) {
            //当前如果第二次进行测试，则需要将判断之前状态是否不是非检测状态
            List<PqDevVO> pqDevVOList = pqDevMapper.listByDevIds(devIds);

            if (CollUtil.isNotEmpty(pqDevVOList)) {
                for (PqDevVO pqDevVO : pqDevVOList) {
                    if (!pqDevVO.getCheckResult().equals(CheckResultEnum.UNCHECKED.getValue())) {
                        pqDevSubService.update(new LambdaUpdateWrapper<PqDevSub>()
                                .set(PqDevSub::getCheckState, CheckStateEnum.CHECKED.getValue())
                                .in(PqDevSub::getDevId, devIds));
                    } else {
                        pqDevSubService.update(new LambdaUpdateWrapper<PqDevSub>()
                                .set(PqDevSub::getCheckState, CheckStateEnum.UNCHECKED.getValue())
                                .in(PqDevSub::getDevId, devIds));
                    }
                }
            }
            PqDevParam.QueryParam queryParam = new PqDevParam.QueryParam();
            queryParam.setPlanIdList(Collections.singletonList(planId));
            List<PqDevVO> pqDevVOList1 = pqDevMapper.selectByQueryParam(queryParam);
            List<PqDev> collect = pqDevVOList1.stream().filter(x -> !x.getCheckState().equals(CheckStateEnum.UNCHECKED.getValue())).collect(Collectors.toList());
            if (CollUtil.isEmpty(collect)) {
                return this.update(new LambdaUpdateWrapper<AdPlan>()
                        .set(AdPlan::getTestState, CheckStateEnum.UNCHECKED.getValue())
                        .eq(AdPlan::getId, planId));
            }
        }
        return false;
    }

    @Override
    public void finishPlan(String planId) {
        this.lambdaUpdate().eq(AdPlan::getId, planId)
                .set(AdPlan::getTestState, CheckStateEnum.CHECKED.getValue());
    }

    @Override
    public void analyse(List<String> ids) {
        SysTestConfig config = sysTestConfigService.getOneConfig();
        if (ObjectUtil.isNotNull(config)) {
            Integer maxTime = config.getMaxTime();
            List<AdPlan> planList = this.listByIds(ids);

            planList.forEach(plan -> {
                if (!CheckStateEnum.CHECKED.getValue().equals(plan.getTestState())) {
                    throw new BusinessException(DetectionResponseEnum.NOT_CHECKED_PLAN_CANNOT_ANALYSE);
                }
            });

            Map<String, List<String[]>> sheets = new TreeMap<>(new Comparator<String>() {
                @Override
                public int compare(String o1, String o2) {
                    if (o1.equals("概览")) {
                        return -1;
                    } else {
                        return o1.compareTo(o2);
                    }
                }
            });

            if (planList.size() > 1) {
                StringBuffer allNote = new StringBuffer();
                List<PqDevVO> allDevList = new ArrayList<>();
                for (AdPlan plan : planList) {
                    PqDevParam.QueryParam queryParam = new PqDevParam.QueryParam();
                    queryParam.setPlanIdList(Collections.singletonList(plan.getId()));
                    List<PqDevVO> pqDevVOList = pqDevMapper.selectByQueryParam(queryParam);
                    pqDevVOList = pqDevVOList.stream().filter(x -> !x.getCheckResult().equals(CheckResultEnum.UNCHECKED.getValue())).collect(Collectors.toList());
                    allDevList.addAll(pqDevVOList);

                    allNote.append(plan.getName() + "计划" + pqDevVOList.size() + "台，");
                    if (plan.getName().equals("概览")) {
                        sheets.put(plan.getName() + "检测计划", getRows(plan.getName(), pqDevVOList, maxTime, getNote(plan, pqDevVOList)));
                    } else {
                        sheets.put(plan.getName(), getRows(plan.getName(), pqDevVOList, maxTime, getNote(plan, pqDevVOList)));
                    }
                }
                allNote.insert(0, "说明：总计" + allDevList.size() + "台设备，");
                allNote.replace(allNote.length() - 1, allNote.length(), "。");
                sheets.put("概览", getRows("数据分析结果-所有计划", allDevList, maxTime, allNote.toString()));
            } else {
                AdPlan plan = planList.get(0);
                PqDevParam.QueryParam queryParam = new PqDevParam.QueryParam();
                queryParam.setPlanIdList(Collections.singletonList(plan.getId()));
                List<PqDevVO> pqDevVOList = pqDevMapper.selectByQueryParam(queryParam);
                pqDevVOList = pqDevVOList.stream().filter(x -> !x.getCheckResult().equals(CheckResultEnum.UNCHECKED.getValue())).collect(Collectors.toList());

                sheets.put(plan.getName(), getRows(plan.getName(), pqDevVOList, maxTime, getNote(plan, pqDevVOList)));
            }

            exportPassRateExcel("数据分析结果.xlsx", maxTime, sheets);
        }
    }

    @Override
    public PlanStatisticsVO statistics(AdPlanParam.StatisticsParam param) {
        String planId = param.getPlanId();
        AdPlan plan = this.getById(planId);
        if (ObjectUtil.isNull(plan)) {
            throw new BusinessException(DetectionResponseEnum.PLAN_NOT_EXIST);
        }

        List<PqDevVO> allPlanDevices = listPlanDevices(plan.getId(), null, null);
        List<PqDevVO> planDevices = filterPlanDevices(allPlanDevices, param.getManufacturer(), param.getDevType());
        List<PqDevVO> checkedDevices = planDevices.stream()
                .filter(this::isCheckedDevice)
                .collect(Collectors.toList());
        int uncheckedDeviceCount = (int) planDevices.stream()
                .filter(dev -> Objects.equals(dev.getCheckState(), CheckStateEnum.UNCHECKED.getValue()))
                .count();
        int checkingDeviceCount = (int) planDevices.stream()
                .filter(dev -> Objects.equals(dev.getCheckState(), CheckStateEnum.CHECKING.getValue()))
                .count();
        PlanStatisticsVO statistics = new PlanStatisticsVO();
        statistics.setPlanId(plan.getId());
        statistics.setPlanName(plan.getName());
        statistics.setManufacturerOptions(buildManufacturerOptions(allPlanDevices, param.getDevType()));
        statistics.setDevTypeOptions(buildDevTypeOptions(allPlanDevices, param.getManufacturer()));
        statistics.setUncheckedDeviceCount(uncheckedDeviceCount);
        statistics.setCheckingDeviceCount(checkingDeviceCount);
        statistics.setCheckedDeviceCount(checkedDevices.size());
        statistics.setTotalCheckCount(checkedDevices.stream().mapToInt(dev -> defaultZero(dev.getRecheckNum())).sum());

        int firstQualifiedCount = (int) checkedDevices.stream()
                .filter(dev -> Objects.equals(dev.getRecheckNum(), 1))
                .filter(dev -> Objects.equals(dev.getCheckResult(), CheckResultEnum.ACCORD.getValue()))
                .count();
        int secondQualifiedCount = (int) checkedDevices.stream()
                .filter(dev -> Objects.equals(dev.getRecheckNum(), 2))
                .filter(dev -> Objects.equals(dev.getCheckResult(), CheckResultEnum.ACCORD.getValue()))
                .count();
        int thirdOrMoreQualifiedCount = (int) checkedDevices.stream()
                .filter(dev -> defaultZero(dev.getRecheckNum()) >= 3)
                .filter(dev -> Objects.equals(dev.getCheckResult(), CheckResultEnum.ACCORD.getValue()))
                .count();
        int unqualifiedDeviceCount = (int) checkedDevices.stream()
                .filter(dev -> Objects.equals(dev.getCheckResult(), CheckResultEnum.NOT_ACCORD.getValue()))
                .count();

        statistics.setFirstQualifiedDeviceCount(firstQualifiedCount);
        statistics.setSecondQualifiedDeviceCount(secondQualifiedCount);
        statistics.setThirdOrMoreQualifiedDeviceCount(thirdOrMoreQualifiedCount);
        statistics.setQualifiedDeviceCount(firstQualifiedCount + secondQualifiedCount + thirdOrMoreQualifiedCount);
        statistics.setUnqualifiedDeviceCount(unqualifiedDeviceCount);
        statistics.setFirstPassRate(rate(firstQualifiedCount, checkedDevices.size()));
        statistics.setSecondPassRate(rate(secondQualifiedCount, checkedDevices.size()));
        statistics.setThirdOrMorePassRate(rate(thirdOrMoreQualifiedCount, checkedDevices.size()));
        statistics.setUnqualifiedRate(rate(unqualifiedDeviceCount, checkedDevices.size()));

        pqDevCheckHistoryService.backfillPlanHistoryIfEmpty(plan, checkedDevices);
        List<PlanStatisticsItemVO> itemDistributions = pqDevCheckHistoryService.listItemDistributions(plan.getId(), param.getManufacturer(), param.getDevType());
        statistics.setItemDistributions(itemDistributions);
        statistics.setUnqualifiedItemCount(itemDistributions.stream()
                .mapToInt(item -> defaultZero(item.getUnqualifiedCount()))
                .sum());
        return statistics;
    }

    private List<PqDevVO> listPlanDevices(String planId, String manufacturer, String devType) {
        PqDevParam.QueryParam queryParam = new PqDevParam.QueryParam();
        queryParam.setPlanIdList(Collections.singletonList(planId));
        List<PqDevVO> pqDevVOList = pqDevMapper.selectByQueryParam(queryParam);
        if (CollUtil.isEmpty(pqDevVOList)) {
            return Collections.emptyList();
        }
        return filterPlanDevices(pqDevVOList, manufacturer, devType);
    }

    private List<PqDevVO> filterPlanDevices(List<PqDevVO> devices, String manufacturer, String devType) {
        if (CollUtil.isEmpty(devices)) {
            return Collections.emptyList();
        }
        return devices.stream()
                .filter(dev -> StrUtil.isBlank(manufacturer) || Objects.equals(dev.getManufacturer(), manufacturer))
                .filter(dev -> StrUtil.isBlank(devType) || Objects.equals(dev.getDevType(), devType))
                .collect(Collectors.toList());
    }

    private List<PlanStatisticsOptionVO> buildManufacturerOptions(List<PqDevVO> devices, String devType) {
        if (CollUtil.isEmpty(devices)) {
            return Collections.emptyList();
        }
        List<String> ids = devices.stream()
                .filter(dev -> StrUtil.isBlank(devType) || Objects.equals(dev.getDevType(), devType))
                .map(PqDevVO::getManufacturer)
                .filter(StrUtil::isNotBlank)
                .distinct()
                .collect(Collectors.toList());
        Map<String, String> nameMap = ids.isEmpty()
                ? Collections.emptyMap()
                : dictDataService.listByIds(ids).stream()
                .collect(Collectors.toMap(DictData::getId, DictData::getName, (left, right) -> left));
        return ids.stream()
                .map(id -> new PlanStatisticsOptionVO(id, nameMap.getOrDefault(id, id)))
                .collect(Collectors.toList());
    }

    private List<PlanStatisticsOptionVO> buildDevTypeOptions(List<PqDevVO> devices, String manufacturer) {
        if (CollUtil.isEmpty(devices)) {
            return Collections.emptyList();
        }
        List<String> ids = devices.stream()
                .filter(dev -> StrUtil.isBlank(manufacturer) || Objects.equals(dev.getManufacturer(), manufacturer))
                .map(PqDevVO::getDevType)
                .filter(StrUtil::isNotBlank)
                .distinct()
                .collect(Collectors.toList());
        Map<String, String> nameMap = ids.isEmpty()
                ? Collections.emptyMap()
                : devTypeService.listByIds(ids).stream()
                .collect(Collectors.toMap(DevType::getId, DevType::getName, (left, right) -> left));
        return ids.stream()
                .map(id -> new PlanStatisticsOptionVO(id, nameMap.getOrDefault(id, id)))
                .collect(Collectors.toList());
    }

    private boolean isCheckedDevice(PqDevVO dev) {
        Integer checkState = dev.getCheckState();
        return checkState != null && checkState >= CheckStateEnum.CHECKED.getValue();
    }

    private List<PlanStatisticsItemVO> buildItemDistributions(AdPlan plan, List<PqDevVO> checkedDevices) {
        if (CollUtil.isEmpty(checkedDevices) || StrUtil.isBlank(plan.getScriptId()) || ObjectUtil.isNull(plan.getCode())) {
            return Collections.emptyList();
        }

        Map<Integer, ScriptItemInfo> scriptItemInfoMap = getScriptItemInfoMap(plan.getScriptId());
        if (CollUtil.isEmpty(scriptItemInfoMap)) {
            return Collections.emptyList();
        }

        Map<String, PlanStatisticsItemAccumulator> itemAccumulatorMap = new TreeMap<>();
        for (PqDevVO device : checkedDevices) {
            List<SimAndDigBaseResult> deviceResultList = new ArrayList<>();
            deviceResultList.addAll(adNonHarmonicService.listSimAndDigBaseResult(plan.getScriptId(), plan.getCode() + "", device.getId()));
            deviceResultList.addAll(adHarmonicService.listAllResultData(plan.getScriptId(), plan.getCode() + "", device.getId()));
            if (CollUtil.isEmpty(deviceResultList)) {
                continue;
            }

            Map<String, List<SimAndDigBaseResult>> deviceItemResultMap = deviceResultList.stream()
                    .filter(result -> ObjectUtil.isNotNull(result.getSort()))
                    .map(result -> new AbstractMap.SimpleEntry<>(scriptItemInfoMap.get(result.getSort()), result))
                    .filter(entry -> ObjectUtil.isNotNull(entry.getKey()))
                    .collect(Collectors.groupingBy(
                            entry -> entry.getKey().getItemId(),
                            TreeMap::new,
                            Collectors.mapping(Map.Entry::getValue, Collectors.toList())
                    ));

            deviceItemResultMap.forEach((itemId, results) -> {
                boolean hasUnqualified = results.stream().anyMatch(result -> isUnqualifiedResultFlag(result.getResultFlag()));
                boolean hasQualified = results.stream().anyMatch(result -> Objects.equals(result.getResultFlag(), 1));
                if (!hasUnqualified && !hasQualified) {
                    return;
                }

                ScriptItemInfo itemInfo = scriptItemInfoMap.get(results.get(0).getSort());
                PlanStatisticsItemAccumulator accumulator = itemAccumulatorMap.computeIfAbsent(
                        itemId,
                        key -> new PlanStatisticsItemAccumulator(itemId, itemInfo.getItemName())
                );
                accumulator.totalCount++;
                if (hasUnqualified) {
                    accumulator.unqualifiedCount++;
                } else {
                    accumulator.qualifiedCount++;
                }
            });
        }

        return itemAccumulatorMap.values().stream()
                .map(PlanStatisticsItemAccumulator::toVO)
                .collect(Collectors.toList());
    }

    private Map<Integer, ScriptItemInfo> getScriptItemInfoMap(String scriptId) {
        List<PqScriptDtls> scriptDtlsList = pqScriptDtlsService.list(new LambdaQueryWrapper<PqScriptDtls>()
                .eq(PqScriptDtls::getScriptId, scriptId)
                .ne(PqScriptDtls::getScriptIndex, -1)
                .eq(PqScriptDtls::getEnable, DataStateEnum.ENABLE.getCode())
        );
        if (CollUtil.isEmpty(scriptDtlsList)) {
            return Collections.emptyMap();
        }

        List<String> scriptTypeIds = scriptDtlsList.stream()
                .map(PqScriptDtls::getScriptType)
                .filter(StrUtil::isNotBlank)
                .distinct()
                .collect(Collectors.toList());
        Map<String, DictTree> dictTreeMap = Collections.emptyMap();
        if (CollUtil.isNotEmpty(scriptTypeIds)) {
            dictTreeMap = dictTreeService.getDictTreeById(scriptTypeIds).stream()
                    .collect(Collectors.toMap(DictTree::getId, dictTree -> dictTree, (left, right) -> left));
        }

        Map<String, DictTree> finalDictTreeMap = dictTreeMap;
        return scriptDtlsList.stream()
                .collect(Collectors.groupingBy(PqScriptDtls::getScriptIndex, TreeMap::new, Collectors.toList()))
                .entrySet()
                .stream()
                .collect(Collectors.toMap(
                        Map.Entry::getKey,
                        entry -> resolveScriptItemInfo(entry.getKey(), entry.getValue(), finalDictTreeMap),
                        (left, right) -> left,
                        TreeMap::new
                ));
    }

    private ScriptItemInfo resolveScriptItemInfo(Integer sort, List<PqScriptDtls> dtlsList, Map<String, DictTree> dictTreeMap) {
        if (CollUtil.isEmpty(dtlsList)) {
            return new ScriptItemInfo(String.valueOf(sort), "检测项" + sort);
        }
        PqScriptDtls first = dtlsList.get(0);
        DictTree dictTree = dictTreeMap.get(first.getScriptType());
        if (ObjectUtil.isNotNull(dictTree) && StrUtil.isNotBlank(dictTree.getName())) {
            return new ScriptItemInfo(dictTree.getId(), dictTree.getName());
        }
        if (StrUtil.isNotBlank(first.getScriptType())) {
            return new ScriptItemInfo(first.getScriptType(), "检测项" + sort);
        }
        return new ScriptItemInfo(String.valueOf(sort), "检测项" + sort);
    }

    private boolean isUnqualifiedResultFlag(Integer resultFlag) {
        return ObjectUtil.isNotNull(resultFlag)
                && !Objects.equals(resultFlag, 1)
                && !Objects.equals(resultFlag, 4)
                && !Objects.equals(resultFlag, 5);
    }

    private Integer defaultZero(Integer value) {
        return ObjectUtil.isNull(value) ? 0 : value;
    }

    private BigDecimal rate(Integer numerator, Integer denominator) {
        if (ObjectUtil.isNull(denominator) || denominator == 0) {
            return BigDecimal.ZERO.setScale(2, RoundingMode.HALF_UP);
        }
        return BigDecimal.valueOf(defaultZero(numerator))
                .multiply(BigDecimal.valueOf(100))
                .divide(BigDecimal.valueOf(denominator), 2, RoundingMode.HALF_UP);
    }

    private class PlanStatisticsItemAccumulator {
        private final String itemId;
        private final String itemName;
        private int totalCount;
        private int qualifiedCount;
        private int unqualifiedCount;

        private PlanStatisticsItemAccumulator(String itemId, String itemName) {
            this.itemId = itemId;
            this.itemName = itemName;
        }

        private PlanStatisticsItemVO toVO() {
            PlanStatisticsItemVO itemVO = new PlanStatisticsItemVO();
            itemVO.setItemId(itemId);
            itemVO.setItemName(itemName);
            itemVO.setUnqualifiedCount(unqualifiedCount);
            return itemVO;
        }
    }

    private static class ScriptItemInfo {
        private final String itemId;
        private final String itemName;

        private ScriptItemInfo(String itemId, String itemName) {
            this.itemId = itemId;
            this.itemName = itemName;
        }

        private String getItemId() {
            return itemId;
        }

        private String getItemName() {
            return itemName;
        }
    }

    @Override
    public void exportPlan(AdPlanParam.QueryParam queryParam) {
        DictData dictData = dictDataService.getDictDataById(queryParam.getPatternId());
        if (PatternEnum.CONTRAST.getValue().equals(dictData.getCode())) {
            this.exportContrastPlan(queryParam);
        } else {
            String currrentScene = sysTestConfigService.getCurrrentScene();
            SceneEnum sceneEnum = SceneEnum.getSceneEnum(currrentScene);
            switch (sceneEnum) {
                case PROVINCE_PLATFORM:
                    this.exportProvincePlan(queryParam);
                    break;
                case LEAVE_FACTORY_TEST:
                    this.exportCNPlan(queryParam);
                    break;
                case SELF_TEST:
                    break;
                default:
                    break;
            }
        }
    }

    private void exportContrastPlan(AdPlanParam.QueryParam queryParam) {
        List<AdPlan> planList = this.list(this.getQueryWrapper(queryParam));
        this.visualize(planList);
        List<ContrastPlanExcel> planExcelList = BeanUtil.copyToList(planList, ContrastPlanExcel.class);

        List<Map<String, Object>> sheetsList = new ArrayList<>();
        planExcelList.forEach(plan -> {
            Map<String, Object> sheet = new HashMap<>();
            ExportParams exportParams = new ExportParams();
            exportParams.setSheetName(plan.getName());
            sheet.put("title", exportParams);
            sheet.put("entity", ContrastDevExcel.class);

            PqDevParam.QueryParam param = new PqDevParam.QueryParam();
            param.setPlanIdList(Collections.singletonList(plan.getId()));
            List<PqDevVO> pqDevVOList = pqDevService.listByPlanId(param);
            List<ContrastDevExcel> exportContrastDevData = pqDevService.getExportContrastDevData(pqDevVOList);
            sheet.put("data", exportContrastDevData);
            sheetsList.add(sheet);

            String standardDevNameStrByPlanId = adPlanContrastStandardDevService.getStandardDevNameStrByPlanId(plan.getId());
            plan.setStandardDev(standardDevNameStrByPlanId);
        });

        Map<String, Object> sheet1 = new HashMap<>();
        ExportParams exportParams1 = new ExportParams();
        exportParams1.setSheetName("检测计划");
        sheet1.put("title", exportParams1);
        sheet1.put("entity", ContrastPlanExcel.class);
        sheet1.put("data", planExcelList);
        sheetsList.add(0, sheet1);

        ExcelUtil.exportExcel("检测计划导出数据.xlsx", sheetsList);
    }

    @Override
    public void downloadTemplate(String patternId) {
        String currrentScene = sysTestConfigService.getCurrrentScene();
        SceneEnum sceneEnum = SceneEnum.getSceneEnum(currrentScene);
        switch (sceneEnum) {
            case PROVINCE_PLATFORM:
                ExcelUtil.exportExcelPullDown(new ExportParams(), "检测计划模板.xlsx", 2, this.getPullDownList(SceneEnum.PROVINCE_PLATFORM, patternId), ProvincePlanExcel.class, Collections.emptyList());
                break;
            case LEAVE_FACTORY_TEST:
                ExcelUtil.exportExcelPullDown(new ExportParams(), "检测计划模板.xlsx", 2, this.getPullDownList(SceneEnum.LEAVE_FACTORY_TEST, patternId), CNPlanExcel.class, Collections.emptyList());
                break;
            case SELF_TEST:
                break;
            default:
                break;
        }
    }

    @Override
    public boolean importPlan(MultipartFile file, String patternId, HttpServletResponse response) {
        String currrentScene = sysTestConfigService.getCurrrentScene();
        SceneEnum sceneEnum = SceneEnum.getSceneEnum(currrentScene);
        switch (sceneEnum) {
            case PROVINCE_PLATFORM:
                return this.importProvincePlan(file, patternId, response);
            case LEAVE_FACTORY_TEST:
                return this.importCNPlan(file, patternId, response);
            case SELF_TEST:
                break;
            default:
                break;
        }
        return false;
    }

    @Override
    public List<PqStandardDev> getUnBoundStandardDev(String fatherPlanId) {
        return adPlanStandardDevMapper.listByPlanId(Collections.singletonList(fatherPlanId));
    }

    @Override
    public List<PqStandardDev> getBoundStandardDev(String planId, Integer all) {
        List<String> planIds = new ArrayList<>();
        if (ObjectUtil.isNotNull(all) && all == 1) {
            AdPlan plan = this.getById(planId);
            String fatherPlanId = plan.getFatherPlanId();
            if (StrUtil.isNotBlank(fatherPlanId)) {
                planIds.add(fatherPlanId);
                List<String> childPlanIds = this.lambdaQuery().eq(AdPlan::getFatherPlanId, fatherPlanId).list()
                        .stream().map(AdPlan::getId).collect(Collectors.toList());
                planIds.addAll(childPlanIds);
            }
        } else {
            planIds.add(planId);
        }

        return adPlanStandardDevMapper.listByPlanId(planIds);
    }

    @Override
    @Transactional
    public boolean updateSubPlanName(String planId, String name) {
        AdPlan plan = this.getById(planId);
        plan.setName(name);

        return this.updateById(plan);
    }

    @Override
    public boolean updateBindDev(PqDevParam.BindPlanParam param) {
        AdPlan plan = this.getById(param.getPlanId());
        // 0-解绑、1-绑定
        if (param.getBindFlag().equals(1)) {
            pqDevService.lambdaUpdate().set(PqDev::getPlanId, plan.getId()).in(PqDev::getId, param.getDevIds()).update();
        } else {
            pqDevService.lambdaUpdate().set(PqDev::getPlanId, plan.getFatherPlanId()).in(PqDev::getId, param.getDevIds()).update();
        }
        PqDevParam.QueryParam queryParam = new PqDevParam.QueryParam();
        queryParam.setPlanIdList(Collections.singletonList(plan.getId()));
        List<PqDevVO> pqDevVOS = pqDevService.listByPlanId(queryParam);
        boolean allUnchecked = pqDevVOS.stream().allMatch(pqDevVO -> CheckStateEnum.UNCHECKED.getValue().equals(pqDevVO.getCheckState()));
        boolean allArchived = pqDevVOS.stream().allMatch(pqDevVO -> pqDevVO.getCheckState() != null && pqDevVO.getCheckState() >= CheckStateEnum.DOCUMENTED.getValue());
        if (allUnchecked) {
            plan.setTestState(CheckStateEnum.UNCHECKED.getValue());
        } else if (allArchived && !pqDevVOS.isEmpty()) {
            plan.setTestState(CheckStateEnum.CHECKED.getValue());
        } else {
            plan.setTestState(CheckStateEnum.CHECKING.getValue());
        }
        this.updateById(plan);
        return true;
    }

    @Override
    @Transactional
    public boolean updateBindStandardDev(String planId, List<String> standardDevIds) {
        // 处理已有的
        List<AdPlanStandardDev> adPlanStandardDevs = adPlanStandardDevService.listByPlanId(Collections.singletonList(planId));
        // 同时修改父计划的绑定
        AdPlan plan = this.getById(planId);

        List<String> standardDevIds1 = adPlanStandardDevs.stream().map(AdPlanStandardDev::getStandardDevId).collect(Collectors.toList());
        adPlanStandardDevService.addAdPlanStandardDev(plan.getFatherPlanId(), standardDevIds1);
        adPlanStandardDevService.deleteAdPlanStandardDev(plan.getFatherPlanId(), standardDevIds);

        adPlanStandardDevService.updateAdPlanStandardDev(planId, standardDevIds);
        return true;
    }

    /**
     * 省级平台导出检测计划数据
     *
     * @param queryParam
     */
    private void exportProvincePlan(AdPlanParam.QueryParam queryParam) {
        List<AdPlan> planList = this.list(this.getQueryWrapper(queryParam));
        this.visualize(planList);
        List<ProvincePlanExcel> planExcelList = BeanUtil.copyToList(planList, ProvincePlanExcel.class);
        for (int i = 0; i < planList.size(); i++) {
            List<PqSource> pqSources = adPlanSourceService.listPqSourceByPlanId(planList.get(i).getId());
            planExcelList.get(i).setSource(pqSources.stream().map(PqSource::getName).collect(Collectors.joining(StrUtil.COMMA)));

            PqDevParam.QueryParam queryParam1 = new PqDevParam.QueryParam();
            queryParam1.setPlanIdList(Collections.singletonList(planList.get(i).getId()));
            List<PqDevVO> pqDevVOList = pqDevMapper.selectByQueryParam(queryParam1);
            pqDevService.visualizeProvinceDev(pqDevVOList);
            List<ProvinceDevExcel> deviceExportData = BeanUtil.copyToList(pqDevVOList, ProvinceDevExcel.class);
            planExcelList.get(i).setDevices(deviceExportData);
        }
        ExcelUtil.exportExcelPullDown(new ExportParams(), "检测计划导出数据.xlsx", 2, this.getPullDownList(SceneEnum.PROVINCE_PLATFORM, queryParam.getPatternId()), ProvincePlanExcel.class, planExcelList);
    }

    /**
     * 二楼导出检测计划数据
     *
     * @param queryParam 查询参数
     */
    private void exportCNPlan(AdPlanParam.QueryParam queryParam) {
        List<AdPlan> planList = this.list(this.getQueryWrapper(queryParam));
        this.visualize(planList);
        List<CNPlanExcel> planExcelList = BeanUtil.copyToList(planList, CNPlanExcel.class);
        for (int i = 0; i < planList.size(); i++) {
            List<PqSource> pqSources = adPlanSourceService.listPqSourceByPlanId(planList.get(i).getId());
            planExcelList.get(i).setSource(pqSources.stream().map(PqSource::getName).collect(Collectors.joining(StrUtil.COMMA)));

            PqDevParam.QueryParam queryParam1 = new PqDevParam.QueryParam();
            queryParam1.setPlanIdList(Collections.singletonList(planList.get(i).getId()));
            List<PqDevVO> pqDevs = pqDevMapper.selectByQueryParam(queryParam1);
            pqDevService.visualizeCNDev(pqDevs);
            List<CNDevExcel> deviceExportData = BeanUtil.copyToList(pqDevs, CNDevExcel.class);
            planExcelList.get(i).setDevices(deviceExportData);
        }
        ExcelUtil.exportExcelPullDown(new ExportParams(), "检测计划导出数据.xlsx", 2, this.getPullDownList(SceneEnum.LEAVE_FACTORY_TEST, queryParam.getPatternId()), CNPlanExcel.class, planExcelList);
    }

    @Transactional
    public boolean importCNPlan(MultipartFile file, String patternId, HttpServletResponse response) {
        ImportParams params = new ImportParams();
        params.setStartSheetIndex(0);
        params.setSheetNum(1);
        params.setHeadRows(2);
        params.setNeedVerify(true);

        List<CNPlanExcel> planExcelList = null;
        try {
            ExcelImportResult<CNPlanExcel> excelImportResult = ExcelImportUtil.importExcelMore(file.getInputStream(), CNPlanExcel.class, params);
            if (excelImportResult.isVerifyFail()) {
                // 此处前端要做特殊处理，具体可以参考技术监督的数据导入
                Workbook failWorkbook = excelImportResult.getFailWorkbook();
                PoiUtil.exportFileByWorkbook(failWorkbook, "非法检测计划数据.xlsx", response);
            } else {
                planExcelList = excelImportResult.getList();
            }
        } catch (Exception e) {
            throw new BusinessException(DetectionResponseEnum.IMPORT_DATA_FAIL);
        }
        if (ObjectUtil.isNotEmpty(planExcelList)) {
            return this.importCNPlan(planExcelList, patternId);
        }
        return false;
    }

    /**
     * 二楼导入检测计划
     *
     * @param planExcelList
     * @param patternId
     */
    @Transactional
    public boolean importCNPlan(List<CNPlanExcel> planExcelList, String patternId) {
        List<AdPlan> adPlans = BeanUtil.copyToList(planExcelList, AdPlan.class);

        String patternCode = dictDataService.getDictDataById(patternId).getCode();
        for (int i = 0; i < adPlans.size(); i++) {
            CNPlanExcel planExcel = planExcelList.get(i);
            AdPlanParam adPlanParam = new AdPlanParam();
            adPlanParam.setName(planExcel.getName());
            adPlanParam.setPattern(patternId);
            checkRepeat(adPlanParam, false, CommonEnum.FATHER_ID.getValue());

            String planId = UUID.randomUUID().toString().replaceAll("-", "");
            adPlans.get(i).setId(planId);
            adPlans.get(i).setPattern(patternId);
            Integer code = this.generateCode();
            adPlans.get(i).setCode(code);

            String source = planExcel.getSource();
            String[] sourceNames = source.split(StrUtil.COMMA);
            // 若非比对模式，检测源、数据源有2个以上时，不导入
            if (!PatternEnum.CONTRAST.getValue().equals(patternCode) && sourceNames.length > 1) {
                throw new BusinessException(DetectionResponseEnum.IMPORT_SOURCE_ERROR);
            }

            String datasource = planExcel.getDatasourceId();
            String[] datasourceIds = datasource.split(StrUtil.COMMA);
            // 若非比对模式，数据源有2个以上时，不导入
            if (!PatternEnum.CONTRAST.getValue().equals(patternCode) && datasourceIds.length > 1) {
                throw new BusinessException(DetectionResponseEnum.IMPORT_DATASOURCE_ERROR);
            }
            planExcel.setAssociateReport(0);
            planExcel.setReportTemplateId(null);

            List<String> sourceIds = pqSourceService.listPqSourceIdByName(sourceNames);
            adPlanSourceService.addAdPlanSource(planId, sourceIds);

            List<CNDevExcel> cnDevExcelList = planExcel.getDevices();
            pqDevService.importCNDev(cnDevExcelList, patternId, planId);

            tableGenService.deleteTable(Collections.singletonList(code.toString()));
            tableGenService.genTable(code.toString(), PatternEnum.CONTRAST.getValue().equals(patternCode));
        }
        // 逆向可视化
        this.reverseVisualize(adPlans);
        return this.saveBatch(adPlans);
    }

    @Transactional
    public boolean importProvincePlan(MultipartFile file, String patternId, HttpServletResponse response) {
        ImportParams params = new ImportParams();
        params.setStartSheetIndex(0);
        params.setSheetNum(1);
        params.setHeadRows(2);
        params.setNeedVerify(true);

        List<ProvincePlanExcel> planExcelList = null;
        try {
            ExcelImportResult<ProvincePlanExcel> excelImportResult = ExcelImportUtil.importExcelMore(file.getInputStream(), ProvincePlanExcel.class, params);
            if (excelImportResult.isVerifyFail()) {
                // 此处前端要做特殊处理，具体可以参考技术监督的数据导入
                Workbook failWorkbook = excelImportResult.getFailWorkbook();
                PoiUtil.exportFileByWorkbook(failWorkbook, "非法检测计划数据.xlsx", response);
            } else {
                planExcelList = excelImportResult.getList();
            }
        } catch (Exception e) {
            throw new BusinessException(DetectionResponseEnum.IMPORT_DATA_FAIL);
        }
        if (ObjectUtil.isNotEmpty(planExcelList)) {
            return this.importProvincePlan(planExcelList, patternId);
        }
        return false;
    }

    /**
     * 省级平台导入检测计划
     *
     * @param planExcelList
     * @param patternId
     */
    public boolean importProvincePlan(List<ProvincePlanExcel> planExcelList, String patternId) {
        List<AdPlan> adPlans = BeanUtil.copyToList(planExcelList, AdPlan.class);

        String patternCode = dictDataService.getDictDataById(patternId).getCode();
        for (int i = 0; i < adPlans.size(); i++) {
            ProvincePlanExcel planExcel = planExcelList.get(i);
            AdPlanParam adPlanParam = new AdPlanParam();
            adPlanParam.setName(planExcel.getName());
            adPlanParam.setPattern(patternId);
            checkRepeat(adPlanParam, false, CommonEnum.FATHER_ID.getValue());

            String planId = UUID.randomUUID().toString().replaceAll("-", "");
            adPlans.get(i).setId(planId);
            adPlans.get(i).setPattern(patternId);
            Integer code = this.generateCode();
            adPlans.get(i).setCode(code);

            tableGenService.deleteTable(Collections.singletonList(code.toString()));
            tableGenService.genTable(code.toString(), PatternEnum.CONTRAST.getValue().equals(patternCode));

            String source = planExcel.getSource();
            String[] sourceNames = source.split(StrUtil.COMMA);
            // 若非比对模式，检测源、数据源有2个以上时，不导入
            if (!PatternEnum.CONTRAST.getValue().equals(patternCode) && sourceNames.length > 1) {
                throw new BusinessException(DetectionResponseEnum.IMPORT_SOURCE_ERROR);
            }

            String datasource = planExcel.getDatasourceId();
            String[] datasourceIds = datasource.split(StrUtil.COMMA);
            // 若非比对模式，数据源有2个以上时，不导入
            if (!PatternEnum.CONTRAST.getValue().equals(patternCode) && datasourceIds.length > 1) {
                throw new BusinessException(DetectionResponseEnum.IMPORT_DATASOURCE_ERROR);
            }
            if ((planExcel.getAssociateReport().equals(1) && "".equals(planExcel.getReportTemplateId()))) {
                throw new BusinessException(DetectionResponseEnum.IMPORT_REPORT_TEMPLATE_ERROR);
            }

            List<String> sourceIds = pqSourceService.listPqSourceIdByName(sourceNames);
            adPlanSourceService.addAdPlanSource(planId, sourceIds);

            List<ProvinceDevExcel> cnDevExcelList = planExcel.getDevices();
            ProvinceDevExcel provinceDevExcel = cnDevExcelList.get(0);
            if (ObjectUtil.isNull(provinceDevExcel.getName())) {
                cnDevExcelList.remove(0);
            }
            pqDevService.importProvinceDev(cnDevExcelList, patternId, planId);
        }
        // 逆向可视化
        this.reverseVisualize(adPlans);
        return this.saveBatch(adPlans);
    }

    /**
     * 获取查询条件wrapper
     *
     * @param queryParam 查询条件
     * @return
     */
    private Wrapper getQueryWrapper(AdPlanParam.QueryParam queryParam) {
        QueryWrapper<AdPlan> queryWrapper = new QueryWrapper<>();
        if (ObjectUtil.isNotNull(queryParam)) {
            queryWrapper.eq(StrUtil.isNotBlank(queryParam.getPatternId()), "ad_plan.pattern", queryParam.getPatternId())
                    .like(StrUtil.isNotBlank(queryParam.getName()), "ad_plan.name", queryParam.getName())
                    .eq(ObjectUtil.isNotNull(queryParam.getTestState()), "ad_plan.Test_State", queryParam.getTestState())
                    .eq(ObjectUtil.isNotNull(queryParam.getReportState()), "ad_plan.Report_State", queryParam.getReportState())
                    .eq(ObjectUtil.isNotNull(queryParam.getResult()), "ad_plan.result", queryParam.getResult());
        }
        queryWrapper.eq("ad_plan.state", DataStateEnum.ENABLE.getCode()).orderByDesc("Create_Time");
        return queryWrapper;
    }

    /**
     * 获取备注信息
     *
     * @param plan    检测计划
     * @param devList 设备列表
     * @return 备注信息
     */
    private String getNote(AdPlan plan, List<PqDevVO> devList) {
        StringBuffer note = new StringBuffer();
        note.append("说明：本计划共有" + devList.size() + "台设备，");
        Map<String, List<PqDev>> devTypeMap = devList.stream().collect(Collectors.groupingBy(PqDev::getDevType, Collectors.toList()));
        devTypeMap.forEach((key, value) -> {
            DevType devType = devTypeService.getById(key);
            if (ObjectUtil.isNotNull(devType)) {
                note.append(devType.getName() + "型号" + value.size() + "台，");
            }
        });
        PqErrSys errSys = pqErrSysService.getById(plan.getErrorSysId());
        DictData dictData1 = dictDataService.getDictDataById(plan.getDataRule());
        if (ObjectUtil.isNotNull(dictData1) && ObjectUtil.isNotNull(errSys)) {
            note.append("测试结果采用\"" + errSys.getName() + "\"误差体系结合\"" + dictData1.getName() + "\"数据原则计算得出。");
        }
        return note.toString();
    }

    /**
     * 获取行数据
     *
     * @param header  表头
     * @param devList 设备列表
     * @param maxTime 最大复检次数
     * @param note    备注
     * @return
     */
    private List<String[]> getRows(String header, List<PqDevVO> devList, Integer maxTime, String note) {
        List<String[]> rows = new ArrayList<>();
        String[] row1 = new String[]{header};

        String[] row2 = new String[maxTime + 1];
        row2[0] = "\\";
        String[] row3 = new String[maxTime + 1];
        row3[0] = "合格数量";
        String[] row4 = new String[maxTime + 1];
        row4[0] = "总数量";
        String[] row5 = new String[maxTime + 1];
        row5[0] = "合格率";
        String[] row6 = new String[maxTime + 1];
        row6[0] = note;

        long total = devList.size();

        for (int i = 1; i <= maxTime; i++) {
            row2[i] = i + "次检测";
            int tempI = i;
            List<PqDevVO> tempDevList = devList.stream().filter(dev -> dev.getRecheckNum() == tempI).collect(Collectors.toList());
            long passCount = tempDevList.stream().filter(dev -> dev.getCheckResult() == CheckResultEnum.ACCORD.getValue()).count();
            row3[i] = passCount + "";
            row4[i] = total + "";

            if (total != 0) {
                row5[i] = String.format("%.2f%%", (float) passCount / total * 100);
            } else {
                row5[i] = "/";
            }
        }
        rows.add(row1);
        rows.add(row2);
        rows.add(row3);
        rows.add(row4);
        rows.add(row5);
        rows.add(row6);

        return rows;
    }

    /**
     * 逆向可视化
     *
     * @param planList 检测计划列表
     */
    private void reverseVisualize(List<AdPlan> planList) {
        planList.forEach(adPlan -> {
            String[] datasourceIds = adPlan.getDatasourceId().split(StrUtil.COMMA);
            adPlan.setDatasourceId(Arrays.stream(datasourceIds).map(id -> {
                String value = DataSourceEnum.getValueByMsg(id);
                if (StrUtil.isBlank(value)) {
                    throw new BusinessException(DetectionResponseEnum.DATA_SOURCE_NOT_EXIST);
                } else {
                    return value;
                }
            }).collect(Collectors.joining(StrUtil.COMMA)));

            PqScript pqScript = pqScriptService.getPqScriptByName(adPlan.getScriptId());
            if (ObjectUtil.isNull(pqScript)) {
                throw new BusinessException(DetectionResponseEnum.SCRIPT_NOT_EXIST);
            } else {
                adPlan.setScriptId(pqScript.getId());
            }

            PqErrSys pqErrSys = pqErrSysService.getPqErrSysByName(adPlan.getErrorSysId());
            if (ObjectUtil.isNull(pqErrSys)) {
                throw new BusinessException(DetectionResponseEnum.ERROR_SYSTEM_NOT_EXIST);
            } else {
                adPlan.setErrorSysId(pqErrSys.getId());
            }

            DictData dataRuleDictData = dictDataService.getDictDataByName(adPlan.getDataRule());
            if (ObjectUtil.isNull(dataRuleDictData)) {
                throw new BusinessException(DetectionResponseEnum.DATA_RULE_NOT_EXIST);
            } else {
                adPlan.setDataRule(dataRuleDictData.getId());
            }
            if (adPlan.getAssociateReport().equals(0)) {
                adPlan.setReportTemplateId(null);
            } else if (StrUtil.isNotBlank(adPlan.getReportTemplateId())) {
                String[] split = adPlan.getReportTemplateId().split("_");
                String reportId = this.baseMapper.getReportIdByNameAndVersion(split[0], split[1]);
                if (ObjectUtil.isNull(reportId)) {
                    throw new BusinessException(DetectionResponseEnum.REPORT_TEMPLATE_NOT_EXIST);
                } else {
                    adPlan.setReportTemplateId(reportId);
                }
            } else {
                throw new BusinessException(DetectionResponseEnum.LACK_REPORT_TEMPLATE_ID);
            }
            adPlan.setCode(this.generateCode());
            adPlan.setState(DataStateEnum.ENABLE.getCode());
            // 默认为顶级检测计划
            adPlan.setFatherPlanId(CommonEnum.FATHER_ID.getValue());
            adPlan.setTestState(CheckStateEnum.UNCHECKED.getValue());
            adPlan.setReportState(PlanReportStateEnum.REPORT_STATE_NOT_GENERATED.getValue());
            adPlan.setResult(CheckResultEnum.UNCHECKED.getValue());
        });
    }

    /**
     * 生成code
     *
     * @return
     */
    private Integer generateCode() {
        AdPlan plan = this.lambdaQuery().select(AdPlan::getCode).orderByDesc(AdPlan::getCode).last("LIMIT 1").one();
        if (ObjectUtil.isNotNull(plan)) {
            return plan.getCode() + 1;
        }
        return 1;
    }

    /**
     * 检查计划是否重复
     *
     * @param param         计划参数
     * @param isExcludeSelf 是否排除自己
     */
    private void checkRepeat(AdPlanParam param, boolean isExcludeSelf, String fatherPlanId) {
        QueryWrapper<AdPlan> queryWrapper = new QueryWrapper<>();
        queryWrapper.eq("state", DataStateEnum.ENABLE.getCode())
                .eq("Name", param.getName())
                .eq("Pattern", param.getPattern())
                .eq("Father_Plan_Id", fatherPlanId);
        if (isExcludeSelf) {
            if (param instanceof AdPlanParam.UpdateParam) {
                queryWrapper.ne("id", ((AdPlanParam.UpdateParam) param).getId());
            }
        }
        int count = this.count(queryWrapper);
        if (count > 0) {
            if (!CommonEnum.FATHER_ID.getValue().equals(fatherPlanId)) {
                throw new BusinessException(DetectionResponseEnum.PLAN_REPEATED_IN_SAME_LEVEL);
            } else {
                throw new BusinessException(DetectionResponseEnum.PLAN_REPEATED);
            }
        }
    }

    private void exportPassRateExcel(String fileName, Integer maxTime, Map<String, List<String[]>> sheets) {

        HttpServletResponse response = HttpServletUtil.getResponse();
        XSSFWorkbook wb = new XSSFWorkbook();

        try (ServletOutputStream outputStream = response.getOutputStream()) {
            fileName = URLEncoder.encode(fileName, CharsetUtil.UTF_8);
            response.reset();
            response.setHeader("Content-Disposition", "attachment; filename=\"" + fileName + "\"");
            response.setContentType("application/octet-stream;charset=UTF-8");

            sheets.forEach((sheetName, rows) -> {
                XSSFSheet sheet = wb.createSheet(sheetName);

                // 数据行
                CellStyle cellStyle = wb.createCellStyle();
                cellStyle.setAlignment(HorizontalAlignment.CENTER);
                cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                XSSFFont headerFont = wb.createFont();
                headerFont.setFontName("Microsoft YaHei");
                headerFont.setBold(true);
                headerFont.setFontHeightInPoints((short) 14);
                cellStyle.setFont(headerFont);
                Row row0 = sheet.createRow(0);
                Cell row0Cell0 = row0.createCell(0);
                row0Cell0.setCellStyle(cellStyle);
                row0Cell0.setCellValue(rows.get(0)[0]);
                CellRangeAddress mergedRegion = new CellRangeAddress(0, 0, 0, 2 * maxTime + 1);
                sheet.addMergedRegion(mergedRegion);

                CellStyle commoncellStyle = wb.createCellStyle();
                commoncellStyle.setAlignment(HorizontalAlignment.CENTER);
                commoncellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

                for (int i = 1; i < rows.size() - 1; i++) {
                    String[] row = rows.get(i);
                    Row createRow = sheet.createRow(i);
                    for (int j = 0; j < row.length; j++) {
                        Cell createCell = createRow.createCell(j);
                        if (j == 0 || i == 1) {
                            headerFont.setFontHeightInPoints((short) 11);
                            createCell.setCellStyle(cellStyle);
                        } else {
                            createCell.setCellStyle(commoncellStyle);
                        }
                        if ("\\".equals(row[j])) {
                            CreationHelper helper = wb.getCreationHelper();
                            XSSFDrawing drawing = sheet.createDrawingPatriarch();
                            ClientAnchor anchor = helper.createClientAnchor();
                            // 设置斜线的开始位置
                            anchor.setCol1(j);
                            anchor.setRow1(i);
                            // 设置斜线的结束位置
                            anchor.setCol2(j + 1);
                            anchor.setRow2(i + 1);
                            XSSFSimpleShape shape = drawing.createSimpleShape((XSSFClientAnchor) anchor);

                            // 设置形状类型为线型
                            shape.setShapeType(ShapeTypes.LINE);
                            // 设置线宽
                            shape.setLineWidth(0.5);
                            // 设置线的风格
                            shape.setLineStyle(0);
                            // 设置线的颜色
                            shape.setLineStyleColor(0, 0, 0);
                        } else {
                            createCell.setCellValue(row[j]);
                        }
                    }
                }

                Row note = sheet.createRow(rows.size() - 1);
                Cell noteCell0 = note.createCell(0);
                commoncellStyle.setWrapText(true);
                noteCell0.setCellStyle(commoncellStyle);
                noteCell0.setCellValue(rows.get(rows.size() - 1)[0]);

                mergedRegion = new CellRangeAddress(rows.size() - 1, rows.size() + 2, 0, maxTime);
                sheet.addMergedRegion(mergedRegion);

                // 条形图
                XSSFDrawing drawing = sheet.createDrawingPatriarch();
                // 前四个默认0，[0,5]：从0列5行开始;[6,16]:到6列16行结束
                // 默认宽度(14-8)*12
                XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, maxTime + 1, 1, 2 * maxTime + 2, 25);
                // 创建一个chart对象
                XSSFChart chart = drawing.createChart(anchor);
                // 标题
                chart.setTitleText("检测结果分析");
                // 标题覆盖
                chart.setTitleOverlay(false);
                // 图例位置
                XDDFChartLegend legend = chart.getOrAddLegend();
                legend.setPosition(LegendPosition.TOP);
                // 分类轴标(X轴),标题位置
                XDDFCategoryAxis bottomAxis = chart.createCategoryAxis(AxisPosition.BOTTOM);
                bottomAxis.setTitle("检测次数");
                // 值(Y轴)轴,标题位置
                XDDFValueAxis leftAxis = chart.createValueAxis(AxisPosition.LEFT);
                leftAxis.setTitle("合格率(%)");

                // CellRangeAddress(起始行号，终止行号， 起始列号，终止列号）
                // 分类轴标(X轴)数据，单元格范围位置[1, 1]到[1, maxTime]
                XDDFDataSource<String> checkTimes = XDDFDataSourcesFactory.fromStringCellRange(sheet, new CellRangeAddress(1, 1, 1, maxTime));
                // 数据
                String[] rateStrArr = rows.get(rows.size() - 2);
                Double[] rateArr = new Double[maxTime];
                for (int i = 1; i < rateStrArr.length; i++) {
                    if ("/".equals(rateStrArr[i])) {
                        rateArr[i - 1] = 0.00;
                    } else {
                        rateArr[i - 1] = Double.parseDouble(rateStrArr[i].replace("%", ""));
                    }
                }
                XDDFNumericalDataSource<Double> rate = XDDFDataSourcesFactory.fromArray(rateArr);

                // bar：条形图，
                XDDFBarChartData bar = (XDDFBarChartData) chart.createData(ChartTypes.BAR, bottomAxis, leftAxis);

                leftAxis.setCrossBetween(AxisCrossBetween.BETWEEN);
                // 设置为可变颜色
                bar.setVaryColors(true);// 这里可变颜色要设置成true，如果需要设置成自己想要的颜色，则置为false，然后在下面设置颜色
                // 条形图方向，纵向/横向：纵向
                bar.setBarDirection(BarDirection.COL);

                // 图表加载数据，条形图1
                bar.addSeries(checkTimes, rate);

                CTPlotArea plotArea = chart.getCTChart().getPlotArea();

                CTBarSer serArray = plotArea.getBarChartArray(0).getSerArray(0);
                serArray.addNewDLbls();
                serArray.getDLbls().addNewShowVal().setVal(true);
                serArray.getDLbls().addNewShowLegendKey().setVal(false);
                serArray.getDLbls().addNewShowCatName().setVal(false);
                serArray.getDLbls().addNewShowSerName().setVal(false);

                // 打印图表的xml
                //System.out.println(chart.getCTChart());

                // 绘制
                chart.plot(bar);
            });

            wb.write(outputStream);
            wb.close();
        } catch (IOException e) {
            log.error(">>> 导出数据异常：{}", e.getMessage());
        }
    }

    private List<PullDown> getPullDownList(SceneEnum scene, String patternId) {
        List<PullDown> pullDowns = new ArrayList<>();

        PullDown pullDown = null;
        DictData dictData = dictDataService.getDictDataById(patternId);
        if (PatternEnum.CONTRAST.getValue().equals(dictData.getCode())) {

        } else {
            List<Map<String, Object>> maps = pqSourceService.listAllPqSource(patternId);
            if (ObjectUtil.isNotEmpty(maps)) {
                pullDown = new PullDown();
                pullDown.setFirstCol(1);
                pullDown.setLastCol(1);
                pullDown.setStrings(maps.stream().map(m -> (String) m.get("name")).collect(Collectors.toList()));
                pullDowns.add(pullDown);
            }

            // 检测脚本
            List<PqScript> pqScriptList = pqScriptService.listAllPqScript(null);
            List<String> scriptNameList = pqScriptList.stream().map(m -> m.getName()).collect(Collectors.toList());
            pullDown = new PullDown();
            pullDown.setFirstCol(3);
            pullDown.setLastCol(3);
            pullDown.setStrings(scriptNameList);
            pullDowns.add(pullDown);
        }

        // 数据源
        DictType dictType = dictTypeService.getByCode("Datasource");
        if (ObjectUtil.isNotNull(dictType)) {
            List<DictData> dictDataList = dictDataService.getDictDataByTypeId(dictType.getId());

            pullDown = new PullDown();
            pullDown.setFirstCol(2);
            pullDown.setLastCol(2);
            pullDown.setStrings(dictDataList.stream().map(DictData::getName).collect(Collectors.toList()));
            pullDowns.add(pullDown);
        }

        // 误差体系
        List<Map<String, Object>> maps2 = pqErrSysService.listAllPqErrSys();
        List<String> errSysNameList = maps2.stream().map(m -> (String) m.get("name")).collect(Collectors.toList());
        pullDown = new PullDown();
        pullDown.setFirstCol(4);
        pullDown.setLastCol(4);
        pullDown.setStrings(errSysNameList);
        pullDowns.add(pullDown);

        // 数据处理原则
        dictType = dictTypeService.getByCode("Data_Rule");
        if (ObjectUtil.isNotNull(dictType)) {
            List<DictData> dictDataList = dictDataService.getDictDataByTypeId(dictType.getId());

            pullDown = new PullDown();
            pullDown.setFirstCol(5);
            pullDown.setLastCol(5);
            pullDown.setStrings(dictDataList.stream().map(DictData::getName).collect(Collectors.toList()));
            pullDowns.add(pullDown);
        }

        // 是否做守时检测
        pullDown = new PullDown();
        pullDown.setFirstCol(6);
        pullDown.setLastCol(6);
        pullDown.setStrings(Arrays.asList("是", "否"));
        pullDowns.add(pullDown);

        // 是否关联报告模板
        pullDown = new PullDown();
        pullDown.setFirstCol(7);
        pullDown.setLastCol(7);
        pullDown.setStrings(Arrays.asList("是", "否"));
        pullDowns.add(pullDown);

        // 报告模板
        List<String> strings = SpringUtil.getBean(IPqReportService.class).listOptions(patternId).stream()
                .map(option -> option.getDisplayName())
                .collect(Collectors.toList());
        pullDown = new PullDown();
        pullDown.setFirstCol(8);
        pullDown.setLastCol(8);
        pullDown.setStrings(strings);
        pullDowns.add(pullDown);

        switch (scene) {
            case PROVINCE_PLATFORM:
                List<PullDown> provinceDevPullDownList = null;
                if (PatternEnum.CONTRAST.getValue().equals(dictData.getCode())) {
                    provinceDevPullDownList = pqDevService.getContrastDevPullDownList(9);
                } else {
                    provinceDevPullDownList = pqDevService.getProvinceDevPullDownList(9);
                }
                pullDowns.addAll(provinceDevPullDownList);
                break;
            case LEAVE_FACTORY_TEST:
                List<PullDown> cnDevPullDownList = pqDevService.getCNDevPullDownList(9);
                pullDowns.addAll(cnDevPullDownList);
                break;
            case SELF_TEST:
                break;
            default:
                break;
        }
        return pullDowns;
    }

    @Override
    public void exportSubPlanDataZip(String planId, HttpServletResponse response) {
        AdSubPlanMetaDataVO subPlanMetaData = new AdSubPlanMetaDataVO();
        // 获取所有字典数据
        AdSubPlanMetaDataVO.Dict dict = new AdSubPlanMetaDataVO.Dict();
        List<DictType> dictTypeList = dictTypeService.list();
        List<DictData> dictDataList = dictDataService.list();
        List<DictTree> dictTreeList = dictTreeService.list();
        dict.setTypeList(dictTypeList);
        dict.setDataList(dictDataList);
        dict.setTreeList(dictTreeList);
        subPlanMetaData.setDict(dict);

        // 获取误差体系数据
        List<PqErrSys> errSysList = pqErrSysService.list();
        subPlanMetaData.setErrSysList(errSysList);
        List<PqErrSysDtls> errSysDtlsList = pqErrSysDtlsService.list();
        subPlanMetaData.setErrSysDtlsList(errSysDtlsList);

        // 获取检测计划基本数据
        AdPlan subPlan = this.getById(planId);
        if (StrUtil.isNotBlank(subPlan.getLeader())) {
            SysUser leader = sysUserService.getById(subPlan.getLeader());
            if (leader != null) {
                subPlan.setLeader(leader.getName());
            }
        }
        if (StrUtil.isNotBlank(subPlan.getMembers())) {
            List<String> memberIds = StrUtil.split(subPlan.getMembers(), StrUtil.COMMA);
            List<SysUser> members = sysUserService.listByIds(memberIds);
            subPlan.setMembers(members.stream().map(SysUser::getName).collect(Collectors.joining(StrUtil.COMMA)));
        }
        subPlanMetaData.setPlan(subPlan);
        // 获取检测配置
        AdPlanTestConfig testConfig = adPlanTestConfigService.lambdaQuery().eq(AdPlanTestConfig::getPlanId, subPlan.getFatherPlanId()).one();
        subPlanMetaData.setTestConfig(testConfig);
        // 获取检设备类型数据
        List<DevType> devTypeList = devTypeService.list();
        subPlanMetaData.setDevTypeList(devTypeList);
        List<PqIcdPath> pqIcdPathList = pqIcdPathService.list();
        subPlanMetaData.setIcdPathList(pqIcdPathList);

        // 获取检测计划绑定的被检设备数据
        List<PqDev> devList = pqDevService.list(new LambdaQueryWrapper<PqDev>().eq(PqDev::getPlanId, planId));
        if (CollUtil.isNotEmpty(devList)) {
            List<String> devIds = devList.stream().map(PqDev::getId).collect(Collectors.toList());
            // 被检设备监测点
            List<PqMonitor> pqMonitorList = pqMonitorService.lambdaQuery().in(PqMonitor::getDevId, devIds).list();
            Map<String, List<PqMonitor>> monitorListMap = pqMonitorList.stream().collect(Collectors.groupingBy(PqMonitor::getDevId));
            devList.forEach(dev -> {
                dev.setMonitorList(monitorListMap.getOrDefault(dev.getId(), new ArrayList<>()));
            });
        }
        subPlanMetaData.setDevList(devList);

        // 获取检测计划绑定的标准设备数据
        List<PqStandardDev> standardDevList = adPlanStandardDevMapper.listByPlanId(Collections.singletonList(planId));
        subPlanMetaData.setStandardDevList(standardDevList);


        // 获取检测计划关联的检测报告模板数据以及文件
        PqReport reportTemplate = SpringUtil.getBean(IPqReportService.class).getById(subPlan.getReportTemplateId());
        subPlanMetaData.setReportTemplate(reportTemplate);
        String basePath = reportTemplate.getBasePath();
        String detailPath = reportTemplate.getDetailPath();

        // 导出数据.zip文件
        String jsonStr = JSONUtil.toJsonStr(subPlanMetaData);
        try {
            // 创建临时目录
            File tempDir = FileUtil.mkdir(FileUtil.getTmpDirPath() + "export_" + System.currentTimeMillis() + "/");

            // 创建 JSON 文件
            String jsonFileName = subPlan.getName() + ".json";
            File jsonFile = FileUtil.file(tempDir, jsonFileName);
            FileUtil.writeUtf8String(jsonStr, jsonFile);

            // 创建 ZIP 文件
            String zipFileName = URLEncoder.encode(subPlan.getName() + ".zip", "UTF-8");
            File zipFile = FileUtil.file(tempDir, zipFileName);

            // 先将json文件添加到zip中（使用UTF-8编码）
            ZipUtil.zip(zipFile, CharsetUtil.CHARSET_UTF_8, false, jsonFile);


            // 创建一个临时目录存放两个文件
            File tempZipDir = FileUtil.mkdir(FileUtil.getTmpDirPath() + "temp_plan_" + System.currentTimeMillis() + "/");

            // 复制json文件到临时目录
            FileUtil.copy(jsonFile, tempZipDir, true);
            // 如果basePath存在，则将basePath中的文件添加到已有的zip文件中
            if (FileUtil.exist(basePath)) {
                File baseFile = new File(basePath);
                // 复制baseFile到临时目录
                FileUtil.copy(baseFile, tempZipDir, true);
            }
            // 如果detailPath存在，则将detailPath中的文件添加到已有的zip文件中
            if (FileUtil.exist(detailPath)) {
                File detailFile = new File(detailPath);
                // 复制detailFile到临时目录
                FileUtil.copy(detailFile, tempZipDir, true);
            }


            // 重新创建zip文件，包含所有文件，使用UTF-8编码
            ZipUtil.zip(zipFile, CharsetUtil.CHARSET_UTF_8, false, FileUtil.file(tempZipDir));

            // 删除临时目录
            FileUtil.del(tempZipDir);
            // 设置响应头
            response.reset();
            response.setContentType("application/octet-stream;charset=UTF-8");
            response.setHeader("Content-Disposition", "attachment; filename=\"" + zipFileName + "\"");

            // 将 ZIP 文件写入响应
            ServletOutputStream os = response.getOutputStream();
            FileUtil.writeToStream(zipFile, os);
            os.flush();
            os.close();

            // 删除临时文件
            FileUtil.del(tempDir);
        } catch (IOException e) {
            log.error("导出子计划.zip文件失败: ", e);
            throw new BusinessException(CommonResponseEnum.FAIL);
        }
    }

    @Transactional
    @Override
    public boolean importSubPlanDataZip(MultipartFile file, String patternId, HttpServletResponse response) {
        try {
            // 创建临时目录用于解压文件
            File tempDir = FileUtil.mkdir(FileUtil.getTmpDirPath() + "import_plan_" + System.currentTimeMillis() + "/");

            // 将上传的zip文件保存到临时目录
            File zipFile = FileUtil.file(tempDir, file.getOriginalFilename());
            file.transferTo(zipFile);

            // 解压zip文件，使用UTF-8编码
            File unzipDir = FileUtil.mkdir(FileUtil.file(tempDir, "unzip"));
            ZipUtil.unzip(zipFile, unzipDir, CharsetUtil.CHARSET_UTF_8);

            // 查找解压目录中的json文件
            File[] files = unzipDir.listFiles();
            AdSubPlanMetaDataVO subPlanMetaDataVO = null;
            if (files != null) {
                for (File f : files) {
                    if (f.isFile() && f.getName().endsWith(".json")) {
                        // 读取json文件内容
                        String jsonStr = FileUtil.readUtf8String(f);
                        subPlanMetaDataVO = JSONUtil.toBean(jsonStr, AdSubPlanMetaDataVO.class);
                        break;
                    }
                }
            }

            if (subPlanMetaDataVO == null) {
                FileUtil.del(tempDir);
                throw new BusinessException(CommonResponseEnum.FAIL, "ZIP文件中未找到JSON文件");
            }
            AdPlan plan = subPlanMetaDataVO.getPlan();
            if (plan == null) {
                FileUtil.del(tempDir);
                throw new BusinessException(CommonResponseEnum.FAIL, "ZIP文件中未找到检测计划信息");
            }
            // 检查导入的子计划是否属于当前模式
            if (!StrUtil.equals(plan.getPattern(), patternId)) {
                FileUtil.del(tempDir);
                throw new BusinessException(CommonResponseEnum.FAIL, "该检修计划当前模式不支持导入");
            }

            // 更新检测计划信息
            plan.setImportFlag(1);
            plan.setFatherPlanId(CommonEnum.FATHER_ID.getValue());
            AdPlan oldPlan = this.getById(plan.getId());
            // 不存在，则入库
            if (oldPlan == null) {
                this.save(plan);
            }


            List<PqDev> devList = subPlanMetaDataVO.getDevList();
            List<PqStandardDev> standardDevList = subPlanMetaDataVO.getStandardDevList();
            if (CollUtil.isEmpty(devList) || CollUtil.isEmpty(standardDevList)) {
                FileUtil.del(tempDir);
                throw new BusinessException(CommonResponseEnum.FAIL, "该检修计划未找到被检设备或标准设备信息");
            }

            // 批量更新被检设备信息
            // 设置导入标志为 1
            devList.forEach(dev -> dev.setImportFlag(1));
            // 排除已存在的，再入库
            List<String> devIds = devList.stream().map(PqDev::getId).collect(Collectors.toList());
            List<PqDevSub> pqDevSubs = pqDevSubService.lambdaQuery().in(PqDevSub::getDevId, devIds).list();
            Map<String, Integer> hadDevMaps = pqDevSubs.stream().collect(Collectors.toMap(PqDevSub::getDevId, PqDevSub::getCheckState));
            List<PqDev> updateDevList = new ArrayList<>();
            // 更新被检设备
            for (Map.Entry<String, Integer> entry : hadDevMaps.entrySet()) {
                String devId = entry.getKey();
                Integer checkState = entry.getValue();
                if (checkState.equals(CheckStateEnum.UNCHECKED.getValue())) {
                    devList.stream().filter(dev -> dev.getId().equals(devId))
                            .findFirst().ifPresent(dev -> updateDevList.add(dev));
                }
            }
            if (CollUtil.isNotEmpty(updateDevList)) {
                pqDevService.updateBatchById(updateDevList);
                // 更新监测点数据
                for (PqDev dev : updateDevList) {
                    pqMonitorService.saveOrUpdateBatch(dev.getMonitorList());
                }
            }
            // 新增被检设备
            List<PqDev> saveDevList = devList.stream().filter(dev -> !hadDevMaps.keySet().contains(dev.getId())).collect(Collectors.toList());
            if (CollUtil.isNotEmpty(saveDevList)) {
                pqDevService.saveBatch(saveDevList);
                saveDevList.forEach(dev -> {
                    pqMonitorService.saveBatch(dev.getMonitorList());
                    // 新增时默认设置
                    PqDevSub pqDevSub = new PqDevSub();
                    pqDevSub.setDevId(dev.getId());
                    pqDevSub.setTimeCheckResult(TimeCheckResultEnum.UNKNOWN.getValue());
                    pqDevSub.setFactorCheckResult(FactorCheckResultEnum.UNKNOWN.getValue());
                    pqDevSub.setCheckState(CheckStateEnum.UNCHECKED.getValue());
                    pqDevSub.setReportState(DevReportStateEnum.UNCHECKED.getValue());
                    pqDevSub.setCheckResult(CheckResultEnum.UNCHECKED.getValue());
                    pqDevSubService.save(pqDevSub);
                });
            }

            // 移除未检测的被检设备
            List<String> uncheckedDevIds = pqDevSubs.stream()
                    .filter(pqDevSub -> pqDevSub.getCheckState().equals(CheckStateEnum.UNCHECKED.getValue()))
                    .map(PqDevSub::getDevId)
                    .collect(Collectors.toList());
            List<String> removeDevIds = uncheckedDevIds.stream()
                    .filter(devId -> !devIds.contains(devId))
                    .collect(Collectors.toList());

            if (CollUtil.isNotEmpty(removeDevIds)) {
                pqDevService.removeByIds(removeDevIds);
                pqDevSubService.remove(new LambdaQueryWrapper<PqDevSub>().in(PqDevSub::getDevId, removeDevIds));
                pqMonitorService.remove(new LambdaQueryWrapper<PqMonitor>().in(PqMonitor::getDevId, removeDevIds));
            }
            // 批量更新标准设备信息
            pqStandardDevService.saveOrUpdateBatch(standardDevList);
            List<String> standardDevIds = standardDevList.stream().map(PqStandardDev::getId).collect(Collectors.toList());
            adPlanStandardDevService.updateAdPlanStandardDev(plan.getId(), standardDevIds);

            // 批量更新字典数据
            AdSubPlanMetaDataVO.Dict dict = subPlanMetaDataVO.getDict();
            if (dict != null) {
                List<DictType> typeList = dict.getTypeList();
                if (CollUtil.isNotEmpty(typeList)) {
                    dictTypeService.saveOrUpdateBatch(typeList);
                    List<DictData> dataList = dict.getDataList();
                    if (CollUtil.isNotEmpty(dataList)) {
                        dictDataService.saveOrUpdateBatch(dataList);
                    }
                }
                List<DictTree> treeList = dict.getTreeList();
                if (treeList != null) {
                    dictTreeService.saveOrUpdateBatch(treeList);
                }
            }
            // 更新检测配置
            AdPlanTestConfig testConfig = subPlanMetaDataVO.getTestConfig();
            testConfig.setPlanId(plan.getId());
            Integer count = adPlanTestConfigService.lambdaQuery().eq(AdPlanTestConfig::getPlanId, plan.getId()).count();
            if (count.intValue() == 0) {
                adPlanTestConfigService.save(testConfig);
            } else {
                adPlanTestConfigService.update(testConfig, new LambdaUpdateWrapper<AdPlanTestConfig>().eq(AdPlanTestConfig::getPlanId, plan.getId()));
            }
            testConfig.setPlanId(plan.getId());

            // 批量更新误差体系
            List<PqErrSys> errSysList = subPlanMetaDataVO.getErrSysList();
            if (CollUtil.isNotEmpty(errSysList)) {
                pqErrSysService.saveOrUpdateBatch(errSysList);
                List<PqErrSysDtls> errSysDtlsList = subPlanMetaDataVO.getErrSysDtlsList();
                if (CollUtil.isNotEmpty(errSysDtlsList)) {
                    pqErrSysDtlsService.saveOrUpdateBatch(errSysDtlsList);
                }
            }
            // 批量更新设备类型数据
            List<DevType> devTypeList = subPlanMetaDataVO.getDevTypeList();
            if (CollUtil.isNotEmpty(devTypeList)) {
                devTypeService.saveOrUpdateBatch(devTypeList);
                // 批量更新ICD路径数据
                List<PqIcdPath> icdPathList = subPlanMetaDataVO.getIcdPathList();
                if (CollUtil.isNotEmpty(icdPathList)) {
                    pqIcdPathService.saveOrUpdateBatch(icdPathList);
                }
            }
            tableGenService.deleteTable(Collections.singletonList(plan.getCode().toString()));
            tableGenService.genTable(plan.getCode().toString(), true);

            // 更新报告模版
            PqReport reportTemplate = subPlanMetaDataVO.getReportTemplate();
            SpringUtil.getBean(IPqReportService.class).saveOrUpdate(reportTemplate);
            // 模版文件复制到指定位置
            for (File f : files) {
                if (f.isFile() && f.getName().endsWith(".docx")) {
                    String basePath = reportTemplate.getBasePath();
                    // 将文件复制到basePath所在的目录下
                    File baseFile = new File(basePath);
                    File baseDir = baseFile.getParentFile();
                    if (!baseDir.exists()) {
                        baseDir.mkdirs();
                    }
                    FileUtil.copy(f, baseDir, true);
                }
            }

            // 删除临时目录
            FileUtil.del(tempDir);
            return true;
        } catch (Exception e) {
            log.error("导入子计划元信息zip失败: ", e);
            throw new BusinessException(CommonResponseEnum.FAIL, "导入失败");
        }
    }

    @Override
    public void exportPlanCheckDataZip(String planId, List<String> devIds, Integer report, HttpServletResponse response) {
        AdPlanCheckDataVO planCheckDataVO = new AdPlanCheckDataVO();
        // 获取检测计划基本数据
        AdPlan plan = this.getById(planId);
        planCheckDataVO.setPlan(plan);
        // 获取检测计划绑定的被检设备数据
        List<PqDev> devList = pqDevService.list(new LambdaQueryWrapper<PqDev>().eq(PqDev::getPlanId, planId).in(PqDev::getId, devIds));
        if (CollUtil.isEmpty(devList)) {
            throw new BusinessException(CommonResponseEnum.FAIL, "选择的被检设备不存在");
        }
        planCheckDataVO.setDevList(devList);
        List<String> devIdList = devList.stream().map(PqDev::getId).collect(Collectors.toList());
        // 被检设备状态统计
        List<PqDevSub> devSubList = pqDevSubService.list(new LambdaQueryWrapper<PqDevSub>().in(PqDevSub::getDevId, devIdList));
        planCheckDataVO.setDevSubList(devSubList);
        // 被检设备监测点信息
        List<PqMonitor> monitorList = pqMonitorService.list(new LambdaQueryWrapper<PqMonitor>().in(PqMonitor::getDevId, devIdList));
        planCheckDataVO.setMonitorList(monitorList);
        // devMonitorId = 被检设备ID+通道号
        List<String> devMonitorIds = new ArrayList<>();
        for (PqDev dev : devList) {
            List<String> channelNoList = StrUtil.split(dev.getInspectChannel(), StrUtil.COMMA);
            for (String channelNo : channelNoList) {
                devMonitorIds.add(dev.getId() + StrUtil.UNDERLINE + channelNo);
            }
        }
        planCheckDataVO.setDevMonitorIds(devMonitorIds);
        // 设备通道匹对关系
        List<AdPair> pairList = adPairService.list(new LambdaQueryWrapper<AdPair>().eq(AdPair::getPlanId, planId).in(AdPair::getDevMonitorId, devMonitorIds));
        planCheckDataVO.setPairList(pairList);
        // 获取计划检测结果数据表以及数据
        Integer code = plan.getCode();
        List<String> dataTableNames = CollUtil.newArrayList("ad_harmonic_" + code, "ad_non_harmonic_" + code, "ad_harmonic_result_" + code, "ad_non_harmonic_result_" + code);

        // 创建临时目录用于存储txt文件
        File tempDataDir = FileUtil.mkdir(FileUtil.getTmpDirPath() + "plan_data_" + System.currentTimeMillis() + "/");
        List<File> dataFiles = new ArrayList<>();
        int dataBatch = 0;
        if (CollUtil.isNotEmpty(pairList)) {
            for (String dataTableName : dataTableNames) {
                // 创建数据文件
                String fileName = dataTableName.replace("_" + code, "") + ".txt";
                File dataFile = FileUtil.file(tempDataDir, fileName);
                // 确保文件存在
                FileUtil.touch(dataFile);

                // 初始化写入标志，用于判断是否已写入字段名
                boolean isFirstWrite = true;

                // 分页查询，避免一次性加载大量数据
                int pageSize = 10000; // 每页查询10000条记录
                int offset = 0;
                List<Map<String, Object>> pageData;
                do {
                    dataBatch += 1;
                    String paginatedSql = buildPaginatedQuery(dataTableName, devMonitorIds, pageSize, offset);
                    pageData = jdbcTemplate.queryForList(paginatedSql);

                    // 将当前页数据追加到文件中
                    if (CollUtil.isNotEmpty(pageData)) {
                        StringBuilder content = new StringBuilder();

                        // 如果是第一次写入，先写入字段名
                        if (isFirstWrite) {
                            // 获取字段名
                            Map<String, Object> firstRow = pageData.get(0);
                            List<String> fieldNames = new ArrayList<>(firstRow.keySet());

                            // 写入字段名作为第一行
                            content.append(StrUtil.join("\t", fieldNames)).append(System.lineSeparator());
                            isFirstWrite = false;
                        }

                        // 写入数据行
                        for (Map<String, Object> data : pageData) {
                            List<Object> values = new ArrayList<>(data.values());
                            content.append(StrUtil.join("\t", values)).append(System.lineSeparator());
                        }

                        // 追加内容到文件
                        FileUtil.appendUtf8String(content.toString(), dataFile);
                    }
                    offset += pageSize;
                } while (pageData.size() == pageSize); // 如果查询结果少于pageSize，说明已经查询完所有数据

                // 如果文件存在且不为空，则添加到数据文件列表中
                if (FileUtil.exist(dataFile) && FileUtil.size(dataFile) > 0) {
                    dataFiles.add(dataFile);
                }
            }
        }
        planCheckDataVO.setDataBatch(dataBatch);

        // 导出数据.zip文件
        String jsonStr = JSONUtil.toJsonStr(planCheckDataVO, new JSONConfig().setIgnoreNullValue(false));
        try {
            // 创建临时目录
            File tempDir = FileUtil.mkdir(FileUtil.getTmpDirPath() + "export_" + System.currentTimeMillis() + "/");

            // 创建 JSON 文件
            String jsonFileName = plan.getName() + ".json";
            File jsonFile = FileUtil.file(tempDir, jsonFileName);
            FileUtil.writeUtf8String(jsonStr, jsonFile);

            // 创建 ZIP 文件
            String zipFileName = URLEncoder.encode(plan.getName() + "_检测数据.zip", "UTF-8");
            File zipFile = FileUtil.file(tempDir, zipFileName);

            // 创建一个临时目录存放所有文件
            File tempZipDir = FileUtil.mkdir(FileUtil.getTmpDirPath() + "temp_plan_check_data_" + System.currentTimeMillis() + "/");
            // 复制json文件到临时目录
            FileUtil.copy(jsonFile, tempZipDir, true);

            // 复制数据txt文件到临时目录
            for (File dataFile : dataFiles) {
                FileUtil.copy(dataFile, tempZipDir, true);
            }

            // 添加检测报告文件
            if (ObjectUtil.isNotNull(report) && report.equals(1)) {
                for (PqDev dev : devList) {
                    DevType devType = devTypeService.getById(dev.getDevType());
                    String dirPath = pathConfig.getReportPath().concat(File.separator).concat(devType.getName());
                    File reportFile = new File(dirPath.concat(File.separator).concat(dev.getCreateId()).concat(ReportConstant.DOCX));
                    // 如果reportFile存在，则将reportFile中的文件添加到已有的zip文件中
                    if (FileUtil.exist(reportFile)) {
                        // 复制reportFile到临时目录
                        FileUtil.copy(reportFile, tempZipDir, true);
                    }

                }
            }

            // 重新创建zip文件，包含所有文件，使用UTF-8编码
            ZipUtil.zip(zipFile, CharsetUtil.CHARSET_UTF_8, false, FileUtil.file(tempZipDir));

            // 删除临时目录
            FileUtil.del(tempZipDir);
            FileUtil.del(tempDataDir);

            // 设置响应头
            response.reset();
            response.setContentType("application/octet-stream;charset=UTF-8");
            response.setHeader("Content-Disposition", "attachment; filename=\"" + zipFileName + "\"");

            // 将 ZIP 文件写入响应
            ServletOutputStream os = response.getOutputStream();
            FileUtil.writeToStream(zipFile, os);
            os.flush();
            os.close();

            // 删除临时文件
            FileUtil.del(tempDir);
        } catch (IOException e) {
            log.error("导出计划检测数据.zip文件失败: ", e);
            throw new BusinessException(CommonResponseEnum.FAIL);
        }
    }

    /**
     * 比对模式下计划的检测项获取
     *
     * @param planId 计划ID
     * @return 检测项
     */
    @Override
    public List<String> getScriptListContrast(String planId) {
        List<String> scriptList = new ArrayList<>();
        AdPlan adPlan = this.baseMapper.selectById(planId);
        String pattern = adPlan.getPattern();
        DictData dictData = dictDataService.getDictDataById(pattern);
        if (ObjectUtil.isNotNull(dictData)) {
            if (dictData.getCode().equalsIgnoreCase("Contrast")) {
                String[] items = adPlan.getTestItem().split(",");
                scriptList = new ArrayList<>(Arrays.asList(items));
            }
        }
        return scriptList;
    }

    // 构建分页查询SQL
    private String buildPaginatedQuery(String tableName, List<String> devMonitorIds, int limit, int offset) {
        StringBuilder sql = new StringBuilder("SELECT * FROM " + tableName);

        sql.append(" WHERE Dev_Monitor_Id IN (");
        for (int i = 0; i < devMonitorIds.size(); i++) {
            sql.append("'").append(devMonitorIds.get(i)).append("'");
            if (i < devMonitorIds.size() - 1) {
                sql.append(",");
            }
        }
        sql.append(")");

        // 添加分页限制
        sql.append(" LIMIT ").append(limit).append(" OFFSET ").append(offset);

        return sql.toString();
    }


}
