package com.yuanchu.mom.utils;


import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.baomidou.mybatisplus.core.toolkit.ObjectUtils;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.yuanchu.mom.excel.HuTiaoData;
import com.yuanchu.mom.excel.TianXianData;
import com.yuanchu.mom.exception.ErrorException;
import com.yuanchu.mom.mapper.InsOrderStateMapper;
import com.yuanchu.mom.mapper.InsProductMapper;
import com.yuanchu.mom.mapper.InsProductResult2Mapper;
import com.yuanchu.mom.mapper.InsSampleMapper;
import com.yuanchu.mom.pojo.*;
import com.yuanchu.mom.service.InsOrderPlanService;
import com.yuanchu.mom.service.InsProductResult2Service;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import java.io.*;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

@Component
public class DianLuUtils {

    @Value("${wordUrl}")
    private String wordUrl;

    @Resource
    private InsProductMapper insProductMapper;

    @Resource
    private InsProductResult2Mapper insProductResult2Mapper;

    @Resource
    private InsProductResult2Service insProductResult2Service;

    @Resource
    private InsOrderStateMapper insOrderStateMapper;

    //读取互调的文件
    public void readDianLuFile1(Integer sampleId, InsOrderFile insOrderFile) {
        List<InsProductResult2> insProductResult2s = new ArrayList<>();
        String excelFilePath = wordUrl + "/" + insOrderFile.getFileUrl(); // 更新为你的文件路径
        List<HuTiaoData> huTiaoData = readExcelData1(excelFilePath);
        List<InsProduct> insProducts = insProductMapper.selectList(Wrappers.<InsProduct>lambdaQuery()
                .eq(InsProduct::getState, 1)
                .eq(InsProduct::getInsSampleId, sampleId));
        //查询该样品的互调项目id
        List<InsProduct> products = insProducts.stream().filter(insProduct -> insProduct.getInspectionItemSubclass().equals("互调")).collect(Collectors.toList());
        if (ObjectUtils.isEmpty(products)) {
            throw new ErrorException("该样品没有互调项目");
        }
        //查询检验任务id
        List<InsOrderState> orderStates = insOrderStateMapper.selectList(Wrappers.<InsOrderState>lambdaQuery()
                .eq(InsOrderState::getInsSampleId, sampleId)
                .eq(InsOrderState::getLaboratory, "电路试验"));
        //查询原有检验数据
        List<InsProductResult2> result2s = insProductResult2Mapper.selectList(Wrappers.<InsProductResult2>lambdaQuery()
                .eq(InsProductResult2::getNum, orderStates.get(0).getNum())
                .in(InsProductResult2::getInsProductId, insProducts.stream().distinct().map(InsProduct::getId).collect(Collectors.toList())));
        //根据频点分类
        Map<String, List<HuTiaoData>> collect = huTiaoData.stream()
                .collect(Collectors.groupingBy(HuTiaoData::getOften, LinkedHashMap::new, Collectors.toList()));
        for (Map.Entry<String, List<HuTiaoData>> stringListEntry : collect.entrySet()) {
            InsProductResult2 insProductResult2 = new InsProductResult2();
            insProductResult2.setOften(stringListEntry.getKey() + "(M)");//频点
            //根据下倾角进行分类
            Map<String, List<HuTiaoData>> collect1 = stringListEntry.getValue().stream().collect(Collectors.groupingBy(HuTiaoData::getAngle, LinkedHashMap::new, Collectors.toList()));
            String angles = collect1.entrySet().stream().map(stringListEntry1 -> stringListEntry1.getKey() + "°").collect(Collectors.joining(","));
            insProductResult2.setAngle(angles); //下倾角
            //根据端口号进行分类
            Map<String, List<HuTiaoData>> collect2 = stringListEntry.getValue().stream().collect(Collectors.groupingBy(HuTiaoData::getPort, LinkedHashMap::new, Collectors.toList()));
            String ports = collect2.entrySet().stream().map(Map.Entry::getKey).collect(Collectors.joining(","));
            insProductResult2.setPort(ports); //端口号
            //通过同一端口号同一下倾角获取最大检验值
            List<String> string = new ArrayList<>();
            for (Map.Entry<String, List<HuTiaoData>> listEntry : collect1.entrySet()) {
                Map<String, List<HuTiaoData>> collect3 = listEntry.getValue().stream().collect(Collectors.groupingBy(HuTiaoData::getPort, LinkedHashMap::new, Collectors.toList()));
                List<String> stringList = new ArrayList<>();
                for (Map.Entry<String, List<HuTiaoData>> entry : collect3.entrySet()) {
                    OptionalDouble max = entry.getValue().stream().mapToDouble(value -> Double.parseDouble(value.getValue())).max();
                    stringList.add(String.valueOf(max.getAsDouble()));
                }
                string.add("[" + stringList.stream().collect(Collectors.joining(",")) + "]");
            }
            String value = "[" + string.stream().collect(Collectors.joining(",")) + "]";
            insProductResult2.setValue(value);
            insProductResult2.setInsProductId(products.get(0).getId());
            //次数
            insProductResult2.setNum(orderStates.get(0).getNum());
            //频段(先判断原有数据是否有)
            if (ObjectUtils.isNotEmpty(result2s)) {
                List<String> list = result2s.stream().map(InsProductResult2::getFrequency).distinct().collect(Collectors.toList());
                for (String s : list) {
                    String[] mHzs = s.split("MHz")[0].split("-");
                    //再判断这个频点是否在频段的范围内
                    if (stringListEntry.getKey().equals("700")){
                        insProductResult2.setFrequency("703-803MHz");
                        break;
                    }
                    else if (Integer.parseInt(stringListEntry.getKey()) <= Integer.parseInt(mHzs[1]) && Integer.parseInt(stringListEntry.getKey()) >= Integer.parseInt(mHzs[0])) {
                        insProductResult2.setFrequency(s);
                        break;
                    }
                }
            }
            if (ObjectUtils.isEmpty(insProductResult2.getFrequency())) {
                throw new ErrorException("没有找到" + stringListEntry.getKey() + "对应的频段信息,请先选择频段信息进行保存");
            }
            insProductResult2s.add(insProductResult2);
        }
        insProductResult2Service.saveBatch(insProductResult2s);
    }

    //读取天线的文件
    public void readDianLuFile2(Integer sampleId, InsOrderFile insOrderFile) {
        List<InsProductResult2> insProductResult2s = new ArrayList<>();
        String excelFilePath = wordUrl + "/" + insOrderFile.getFileUrl(); // 更新为你的文件路径
        List<TianXianData> tianXianData = readExcelData2(excelFilePath);
        //查询检验任务id
        List<InsOrderState> orderStates = insOrderStateMapper.selectList(Wrappers.<InsOrderState>lambdaQuery()
                .eq(InsOrderState::getInsSampleId, sampleId)
                .eq(InsOrderState::getLaboratory, "电路试验"));
        List<InsProduct> products = insProductMapper.selectList(Wrappers.<InsProduct>lambdaQuery()
                .eq(InsProduct::getState, 1)
                .eq(InsProduct::getInsSampleId, sampleId));
        //查询该样品的电压驻波比项目id
        List<InsProduct> products1 = products.stream().filter(insProduct -> insProduct.getInspectionItemSubclass().equals("电压驻波比")).collect(Collectors.toList());
        //查询该样品的同极化隔离度项目id
        List<InsProduct> products2 = products.stream().filter(insProduct -> insProduct.getInspectionItemSubclass().equals("同极化隔离度")).collect(Collectors.toList());
        //查询该样品的隔离度项目id
        List<InsProduct> products6 = products.stream().filter(insProduct -> insProduct.getInspectionItemSubclass().equals("同列隔离度")).collect(Collectors.toList());
        //查询该样品的端口间隔离度项目id
        List<InsProduct> products7 = products.stream().filter(insProduct -> insProduct.getInspectionItemSubclass().equals("端口间隔离度")).collect(Collectors.toList());
        //查询该样品的异极化隔离度项目id
        List<InsProduct> products3 = products.stream().filter(insProduct -> insProduct.getInspectionItemSubclass().equals("异极化隔离度")).collect(Collectors.toList());
        //查询该样品的幅度偏差项目id
        List<InsProduct> products4 = products.stream().filter(insProduct -> insProduct.getInspectionItemSubclass().equals("幅度偏差")).collect(Collectors.toList());
        //查询该样品的最大相位偏差项目id
        List<InsProduct> products5 = products.stream().filter(insProduct -> insProduct.getInspectionItemSubclass().equals("最大相位偏差")).collect(Collectors.toList());
        //获取所有频段,根据频段进行分类
        Map<String, List<TianXianData>> map = tianXianData.stream().collect(Collectors.groupingBy(TianXianData::frequency, LinkedHashMap::new, Collectors.toList()));
        for (Map.Entry<String, List<TianXianData>> listEntry : map.entrySet()) {
            //获取信息,根据检验项目进行分类
            Map<String, List<TianXianData>> collect = listEntry.getValue().stream().collect(Collectors.groupingBy(TianXianData::getName, LinkedHashMap::new, Collectors.toList()));
            for (Map.Entry<String, List<TianXianData>> entry : collect.entrySet()) {
                InsProductResult2 insProductResult2 = new InsProductResult2();
                if (entry.getKey().contains("驻波")) {
                    if (products1.size() <= 0) {
                        throw new ErrorException("电压驻波比项目未找到");
                    }
                    insProductResult2.setInsProductId(products1.get(0).getId());//电压驻波比
                    insProductResult2.setFrequency(listEntry.getKey());//频段
                    insProductResult2.setAngle("0°");//角度
                    insProductResult2.setNum(orderStates.get(0).getNum());//次数
                    //获取端口
                    String port = entry.getValue().stream().map(TianXianData::getPort).collect(Collectors.joining(","));
                    insProductResult2.setPort(port);//端口
                    //获取检验值
                    String value = entry.getValue().stream().map(tianXianData1 -> "\"" + tianXianData1.getValue() + "\"").collect(Collectors.joining(","));
                    insProductResult2.setValue("[[" + value + "]]");//值
                    insProductResult2s.add(insProductResult2);
                } else if (entry.getKey().contains("同列隔离")) {
                    if (products6.size() <= 0) {
                        throw new ErrorException("隔离度项目未找到");
                    }
                    insProductResult2.setInsProductId(products6.get(0).getId());//同列隔离
                    insProductResult2.setFrequency(listEntry.getKey());//频段
                    insProductResult2.setAngle("0°");//角度
                    insProductResult2.setNum(orderStates.get(0).getNum());//次数
                    //获取端口
                    String port = entry.getValue().stream().map(TianXianData::getPort).collect(Collectors.joining(","));
                    insProductResult2.setPort(port);//端口
                    //获取检验值
                    String value = entry.getValue().stream().map(tianXianData1 -> "\"" + tianXianData1.getValue() + "\"").collect(Collectors.joining(","));
                    insProductResult2.setValue("[[" + value + "]]");//值
                    insProductResult2s.add(insProductResult2);
                } else if (entry.getKey().contains("端口间隔离")) {
                    if (products7.size() <= 0) {
                        throw new ErrorException("端口间隔离项目未找到");
                    }
                    insProductResult2.setInsProductId(products7.get(0).getId());//端口间隔离
                    insProductResult2.setFrequency(listEntry.getKey());//频段
                    insProductResult2.setAngle("0°");//角度
                    insProductResult2.setNum(orderStates.get(0).getNum());//次数
                    //获取端口
                    String port = entry.getValue().stream().map(TianXianData::getPort).collect(Collectors.joining(","));
                    insProductResult2.setPort(port);//端口
                    //获取检验值
                    String value = entry.getValue().stream().map(tianXianData1 -> "\"" + tianXianData1.getValue() + "\"").collect(Collectors.joining(","));
                    insProductResult2.setValue("[[" + value + "]]");//值
                    insProductResult2s.add(insProductResult2);
                }
                else if (entry.getKey().contains("同极化隔离")) {
                    if (products2.size() <= 0) {
                        throw new ErrorException("同极化隔离项目未找到");
                    }
                    insProductResult2.setInsProductId(products2.get(0).getId());//同极化隔离
                    insProductResult2.setFrequency(listEntry.getKey());//频段
                    insProductResult2.setAngle("0°");//角度
                    insProductResult2.setNum(orderStates.get(0).getNum());//次数
                    //获取端口
                    String port = entry.getValue().stream().map(TianXianData::getPort).collect(Collectors.joining(","));
                    insProductResult2.setPort(port);//端口
                    //获取检验值
                    String value = entry.getValue().stream().map(tianXianData1 -> "\"" + tianXianData1.getValue() + "\"").collect(Collectors.joining(","));
                    insProductResult2.setValue("[[" + value + "]]");//值
                    insProductResult2s.add(insProductResult2);
                } else if (entry.getKey().contains("异极化隔离")) {
                    if (products3.size() <= 0) {
                        throw new ErrorException("异极化隔离项目未找到");
                    }
                    insProductResult2.setInsProductId(products3.get(0).getId());//异极化隔离
                    insProductResult2.setFrequency(listEntry.getKey());//频段
                    insProductResult2.setAngle("0°");//角度
                    insProductResult2.setNum(orderStates.get(0).getNum());//次数
                    //获取端口
                    String port = entry.getValue().stream().map(TianXianData::getPort).collect(Collectors.joining(","));
                    insProductResult2.setPort(port);//端口
                    //获取检验值
                    String value = entry.getValue().stream().map(tianXianData1 -> "\"" + tianXianData1.getValue() + "\"").collect(Collectors.joining(","));
                    insProductResult2.setValue("[[" + value + "]]");//值
                    insProductResult2s.add(insProductResult2);
                }
            }
        }
        /*幅度和相位需要另外写功能*/
        //先过滤出端口号一栏为偏差值的数据
        List<TianXianData> dataList = tianXianData.stream().filter(tianXianData1 -> tianXianData1.getPort().equals("偏差值")).collect(Collectors.toList());
        //再根据检验项目进行分组
        Map<String, List<TianXianData>> collect = dataList.stream().collect(Collectors.groupingBy(TianXianData::getName, LinkedHashMap::new, Collectors.toList()));
        for (Map.Entry<String, List<TianXianData>> entry : collect.entrySet()) {
            if (entry.getKey().contains("幅度")) {
                if (products4.size() <= 0) {
                    throw new ErrorException("幅度偏差项目未找到");
                }
                Map<String, List<TianXianData>> listHashMap = new HashMap<>();
                for (TianXianData xianData : entry.getValue()) {
                    //获取频段(先根据start字段判断这个频点在哪个频段之间)
                    List<String> strings = map.keySet().stream().filter(s ->
                            !s.split("MHz")[0].split("-")[0].equals(s.split("MHz")[0].split("-")[1])
                    ).collect(Collectors.toList());
                    for (String s : strings) {
                        String[] mHzs = s.split("MHz")[0].split("-");
                        //再判断这个频点是否在频段的范围内
                        if (Double.parseDouble(xianData.getStart()) <= Double.parseDouble(mHzs[1]) && Double.parseDouble(xianData.getStart()) >= Double.parseDouble(mHzs[0])) {
                            // 检查是否已经存在这个键的条目
                            List<TianXianData> xianDataList = listHashMap.getOrDefault(s, new ArrayList<>());
                            // 将数据添加到列表中，而不是覆盖旧值
                            xianDataList.add(xianData);
                            // 将更新后的列表放回map中
                            listHashMap.put(s, xianDataList);
                            break;
                        }
                    }
                }
                for (Map.Entry<String, List<TianXianData>> listEntry : listHashMap.entrySet()) {
                    InsProductResult2 insProductResult2 = new InsProductResult2();
                    insProductResult2.setInsProductId(products4.get(0).getId());//幅度偏差
                    insProductResult2.setAngle("0°");//角度
                    insProductResult2.setNum(orderStates.get(0).getNum());//次数
                    insProductResult2.setFrequency(listEntry.getKey());//频段
                    //获取端口
                    String port = listEntry.getValue().stream().map(tianXianData1 -> String.valueOf((int) Double.parseDouble(tianXianData1.getStart()))).collect(Collectors.joining(","));
                    insProductResult2.setPort(port);//端口
                    //获取检验值
                    String value = listEntry.getValue().stream().map(tianXianData1 -> "\"" + tianXianData1.getValue() + "\"").collect(Collectors.joining(","));
                    insProductResult2.setValue("[[" + value + "]]");//值
                    insProductResult2s.add(insProductResult2);
                }
            } else if (entry.getKey().contains("相位")) {
                if (products5.size() <= 0) {
                    throw new ErrorException("最大相位偏差项目未找到");
                }
                Map<String, List<TianXianData>> listHashMap = new HashMap<>();
                for (TianXianData xianData : entry.getValue()) {
                    //获取频段(先根据start字段判断这个频点在哪个频段之间)
                    List<String> strings = map.keySet().stream().filter(s ->
                            !s.split("MHz")[0].split("-")[0].equals(s.split("MHz")[0].split("-")[1])
                    ).collect(Collectors.toList());
                    for (String s : strings) {
                        String[] mHzs = s.split("MHz")[0].split("-");
                        //再判断这个频点是否在频段的范围内
                        if (Double.parseDouble(xianData.getStart()) <= Double.parseDouble(mHzs[1]) && Double.parseDouble(xianData.getStart()) >= Double.parseDouble(mHzs[0])) {
                            // 检查是否已经存在这个键的条目
                            List<TianXianData> xianDataList = listHashMap.getOrDefault(s, new ArrayList<>());
                            // 将数据添加到列表中，而不是覆盖旧值
                            xianDataList.add(xianData);
                            // 将更新后的列表放回map中
                            listHashMap.put(s, xianDataList);
                        }
                    }
                }
                for (Map.Entry<String, List<TianXianData>> listEntry : listHashMap.entrySet()) {
                    InsProductResult2 insProductResult2 = new InsProductResult2();
                    insProductResult2.setInsProductId(products5.get(0).getId());//最大相位偏差
                    insProductResult2.setAngle("0°");//角度
                    insProductResult2.setNum(orderStates.get(0).getNum());//次数
                    insProductResult2.setFrequency(listEntry.getKey());//频段
                    //获取端口
                    String port = listEntry.getValue().stream().map(tianXianData1 -> String.valueOf((int) Double.parseDouble(tianXianData1.getStart()))).collect(Collectors.joining(","));
                    insProductResult2.setPort(port);//端口
                    //获取检验值
                    String value = listEntry.getValue().stream().map(tianXianData1 -> "\"" + tianXianData1.getValue() + "\"").collect(Collectors.joining(","));
                    insProductResult2.setValue("[[" + value + "]]");//值
                    insProductResult2s.add(insProductResult2);
                }
            }
        }

        Map<String, List<InsProductResult2>> groupedMap = insProductResult2s.stream()
                .collect(Collectors.groupingBy(item -> item.getInsProductId() + "_" + item.getFrequency()));
        // 过滤出分组后数量等于1的组
        List<InsProductResult2> filteredList = groupedMap.values().stream()
                .filter(list -> list.size() == 1)
                .flatMap(List::stream)
                .collect(Collectors.toList());
        for (Map.Entry<String, List<InsProductResult2>> entry : groupedMap.entrySet()) {
            if (entry.getValue().size() > 1) {
                InsProductResult2 result2 = new InsProductResult2();
                result2.setInsProductId(Integer.parseInt(entry.getKey().split("_")[0]));
                result2.setFrequency(entry.getKey().split("_")[1]);
                result2.setPort(entry.getValue().stream().map(InsProductResult2::getPort).collect(Collectors.joining(",")));
                String value = entry.getValue().stream().map(insProductResult2 -> {
                    return insProductResult2.getValue().replace("[[", "").replace("]]", "");
                }).collect(Collectors.joining(","));
                result2.setValue("[[" + value + "]]");
                result2.setAngle("0°");
                result2.setOften(entry.getValue().stream().map(InsProductResult2::getOften).collect(Collectors.joining(",")));
                filteredList.add(result2);
            }
        }
        insProductResult2Service.saveBatch(filteredList);
    }

    //读取csv的文件(无源器件)
    public void readDianLuFile3(Integer sampleId, InsOrderFile insOrderFile) {
        List<InsProductResult2> insProductResult2s = new ArrayList<>();
        String excelFilePath = wordUrl + "/" + insOrderFile.getFileUrl(); // 更新为你的文件路径
        List<TianXianData> tianXianData = readExcelData2(excelFilePath);
        //查询检验任务id
        List<InsOrderState> orderStates = insOrderStateMapper.selectList(Wrappers.<InsOrderState>lambdaQuery()
                .eq(InsOrderState::getInsSampleId, sampleId)
                .eq(InsOrderState::getLaboratory, "电路试验"));
        List<InsProduct> products = insProductMapper.selectList(Wrappers.<InsProduct>lambdaQuery()
                .eq(InsProduct::getState, 1)
                .eq(InsProduct::getInsSampleId, sampleId));
        //查询该样品的电压驻波比项目id
        List<InsProduct> products1 = products.stream().filter(insProduct -> insProduct.getInspectionItemSubclass().equals("电压驻波比")).collect(Collectors.toList());
        //查询该样品的无源器件隔离度项目id
        List<InsProduct> products2 = products.stream().filter(insProduct -> insProduct.getInspectionItemSubclass().equals("无源器件隔离度")).collect(Collectors.toList());
        //查询该样品的插入损耗项目id
        List<InsProduct> products3 = products.stream().filter(insProduct -> insProduct.getInspectionItemSubclass().equals("插入损耗")).collect(Collectors.toList());
        //查询该样品的带内波动项目id
        List<InsProduct> products4 = products.stream().filter(insProduct -> insProduct.getInspectionItemSubclass().equals("带内波动")).collect(Collectors.toList());
        //查询该样品的带外抑制项目id
        List<InsProduct> products5 = products.stream().filter(insProduct -> insProduct.getInspectionItemSubclass().equals("带外抑制")).collect(Collectors.toList());
        //查询该样品的最大耦合度项目id
        List<InsProduct> products6 = products.stream().filter(insProduct -> insProduct.getInspectionItemSubclass().equals("最大耦合度")).collect(Collectors.toList());
        //查询该样品的最小耦合度项目id
        List<InsProduct> products7 = products.stream().filter(insProduct -> insProduct.getInspectionItemSubclass().equals("最小耦合度")).collect(Collectors.toList());
        //获取所有频段,根据频段进行分类
        Map<String, List<TianXianData>> map = tianXianData.stream().collect(Collectors.groupingBy(TianXianData::frequency, LinkedHashMap::new, Collectors.toList()));
        for (Map.Entry<String, List<TianXianData>> listEntry : map.entrySet()) {
            //获取信息,根据检验项目进行分类
            Map<String, List<TianXianData>> collect = listEntry.getValue().stream().collect(Collectors.groupingBy(TianXianData::getName, LinkedHashMap::new, Collectors.toList()));
            for (Map.Entry<String, List<TianXianData>> entry : collect.entrySet()) {
                InsProductResult2 insProductResult2 = new InsProductResult2();
                if (entry.getKey().contains("驻波")) {
                    if (products1.size() <= 0) {
                        throw new ErrorException("电压驻波比项目未找到");
                    }
                    insProductResult2.setInsProductId(products1.get(0).getId());//电压驻波比
                    insProductResult2.setFrequency(listEntry.getKey());//频段
                    insProductResult2.setAngle("0°");//角度
                    insProductResult2.setNum(orderStates.get(0).getNum());//次数
                    //获取端口
                    String port = entry.getValue().stream().map(TianXianData::getPort).collect(Collectors.joining(","));
                    insProductResult2.setPort(port);//端口
                    //获取检验值
                    String value = entry.getValue().stream().map(tianXianData1 -> "\"" + tianXianData1.getValue() + "\"").collect(Collectors.joining(","));
                    insProductResult2.setValue("[[" + value + "]]");//值
                    insProductResult2s.add(insProductResult2);
                }
                else if (entry.getKey().contains("隔离度")) {
                    if (products2.size() <= 0) {
                        throw new ErrorException("隔离度项目未找到");
                    }
                    insProductResult2.setInsProductId(products2.get(0).getId());//无源器件隔离度
                    insProductResult2.setFrequency(listEntry.getKey());//频段
                    insProductResult2.setAngle("0°");//角度
                    insProductResult2.setNum(orderStates.get(0).getNum());//次数
                    //获取端口
                    String port = entry.getValue().stream().map(TianXianData::getPort).collect(Collectors.joining(","));
                    insProductResult2.setPort(port);//端口
                    //获取检验值
                    String value = entry.getValue().stream().map(tianXianData1 -> "\"" + tianXianData1.getValue() + "\"").collect(Collectors.joining(","));
                    insProductResult2.setValue("[[" + value + "]]");//值
                    insProductResult2s.add(insProductResult2);
                }
                else if (entry.getKey().contains("插入损耗")) {
                    if (products3.size() <= 0) {
                        throw new ErrorException("插入损耗项目未找到");
                    }
                    insProductResult2.setInsProductId(products3.get(0).getId());//插入损耗
                    insProductResult2.setFrequency(listEntry.getKey());//频段
                    insProductResult2.setAngle("0°");//角度
                    insProductResult2.setNum(orderStates.get(0).getNum());//次数
                    //获取端口
                    String port = entry.getValue().stream().map(TianXianData::getPort).collect(Collectors.joining(","));
                    insProductResult2.setPort(port);//端口
                    //获取检验值
                    String value = entry.getValue().stream().map(tianXianData1 -> "\"" + tianXianData1.getValue() + "\"").collect(Collectors.joining(","));
                    insProductResult2.setValue("[[" + value + "]]");//值
                    insProductResult2s.add(insProductResult2);
                }
                else if (entry.getKey().contains("带内波动")) {
                    if (products4.size() <= 0) {
                        throw new ErrorException("带内波动项目未找到");
                    }
                    insProductResult2.setInsProductId(products4.get(0).getId());//带内波动
                    insProductResult2.setFrequency(listEntry.getKey());//频段
                    insProductResult2.setAngle("0°");//角度
                    insProductResult2.setNum(orderStates.get(0).getNum());//次数
                    //获取端口
                    String port = entry.getValue().stream().map(TianXianData::getPort).collect(Collectors.joining(","));
                    insProductResult2.setPort(port);//端口
                    //获取检验值
                    String value = entry.getValue().stream().map(tianXianData1 -> "\"" + tianXianData1.getValue() + "\"").collect(Collectors.joining(","));
                    insProductResult2.setValue("[[" + value + "]]");//值
                    insProductResult2s.add(insProductResult2);
                }
                else if (entry.getKey().contains("带外抑制")) {
                    if (products5.size() <= 0) {
                        throw new ErrorException("带外抑制项目未找到");
                    }
                    insProductResult2.setInsProductId(products5.get(0).getId());//带外抑制
                    insProductResult2.setFrequency(listEntry.getKey());//频段
                    insProductResult2.setAngle("0°");//角度
                    insProductResult2.setNum(orderStates.get(0).getNum());//次数
                    //获取端口
                    String port = entry.getValue().stream().map(TianXianData::getPort).collect(Collectors.joining(","));
                    insProductResult2.setPort(port);//端口
                    //获取检验值
                    String value = entry.getValue().stream().map(tianXianData1 -> "\"" + tianXianData1.getValue() + "\"").collect(Collectors.joining(","));
                    insProductResult2.setValue("[[" + value + "]]");//值
                    insProductResult2s.add(insProductResult2);
                }
                else if (entry.getKey().contains("耦合度")) {
                    if (products6.size() <= 0 || products7.size()<=0) {
                        throw new ErrorException("耦合度项目未找到");
                    }
                    insProductResult2.setFrequency(listEntry.getKey());//频段
                    insProductResult2.setAngle("0°");//角度
                    insProductResult2.setNum(orderStates.get(0).getNum());//次数
                    //获取端口
                    String port = entry.getValue().stream().map(TianXianData::getPort).collect(Collectors.joining(","));
                    insProductResult2.setPort(port);//端口

                    /*最大耦合度*/
                    insProductResult2.setInsProductId(products6.get(0).getId());//最大耦合度
                    //获取检验值
                    String value = entry.getValue().stream().map(tianXianData1 -> {
                        String input = tianXianData1.getValue().replace("[", "").replace("]", "");
                        String[] parts = input.split("--");
                        return "\"" + "-"+parts[1] + "\"" ; }).collect(Collectors.joining(","));
                    insProductResult2.setValue("[[" + value + "]]");//值
                    insProductResult2s.add(insProductResult2);
                    /*最小耦合度*/
                    insProductResult2.setInsProductId(products7.get(0).getId());//最小耦合度
                    String value1 = entry.getValue().stream().map(tianXianData1 -> {
                        String input = tianXianData1.getValue().replace("[", "").replace("]", "");
                        String[] parts = input.split("--");
                        return "\"" +parts[0] + "\"" ; }).collect(Collectors.joining(","));
                    insProductResult2.setValue("[[" + value1 + "]]");//值
                    insProductResult2s.add(insProductResult2);
                }
            }
        }

        Map<String, List<InsProductResult2>> groupedMap = insProductResult2s.stream()
                .collect(Collectors.groupingBy(item -> item.getInsProductId() + "_" + item.getFrequency()));
        // 过滤出分组后数量等于1的组
        List<InsProductResult2> filteredList = groupedMap.values().stream()
                .filter(list -> list.size() == 1)
                .flatMap(List::stream)
                .collect(Collectors.toList());
        for (Map.Entry<String, List<InsProductResult2>> entry : groupedMap.entrySet()) {
            if (entry.getValue().size() > 1) {
                InsProductResult2 result2 = new InsProductResult2();
                result2.setInsProductId(Integer.parseInt(entry.getKey().split("_")[0]));
                result2.setFrequency(entry.getKey().split("_")[1]);
                result2.setPort(entry.getValue().stream().map(InsProductResult2::getPort).collect(Collectors.joining(",")));
                String value = entry.getValue().stream().map(insProductResult2 -> {
                    return insProductResult2.getValue().replace("[[", "").replace("]]", "");
                }).collect(Collectors.joining(","));
                result2.setValue("[[" + value + "]]");
                result2.setAngle("0°");
                result2.setOften(entry.getValue().stream().map(InsProductResult2::getOften).collect(Collectors.joining(",")));
                filteredList.add(result2);
            }
        }
        insProductResult2Service.saveBatch(filteredList);
    }


    public static List<HuTiaoData> readExcelData1(String filePath) {
        List<HuTiaoData> dataList = new ArrayList<>();

        // 初始化监听器
        AnalysisEventListener<HuTiaoData> listener = new AnalysisEventListener<HuTiaoData>() {
            @Override
            public void invoke(HuTiaoData data, AnalysisContext context) {
                // 处理每行数据，这里简单地添加到数据列表中
                dataList.add(data);
            }

            @Override
            public void doAfterAllAnalysed(AnalysisContext context) {
                // 所有数据解析完成后的操作，这里暂不实现
            }
        };

        // 读取Excel文件
        EasyExcel.read(filePath, HuTiaoData.class, listener).sheet().doRead();

        return dataList;
    }

    public static List<TianXianData> readExcelData2(String filePath) {
        List<TianXianData> dataList = new ArrayList<>();

        // 初始化监听器
        AnalysisEventListener<TianXianData> listener = new AnalysisEventListener<TianXianData>() {
            @Override
            public void invoke(TianXianData data, AnalysisContext context) {
                // 处理每行数据，这里简单地添加到数据列表中
                dataList.add(data);
            }

            @Override
            public void doAfterAllAnalysed(AnalysisContext context) {
                // 所有数据解析完成后的操作，这里暂不实现
            }
        };

        try (BufferedReader reader = new BufferedReader(new InputStreamReader(new FileInputStream(filePath), "GB2312"));
             Workbook workbook = new XSSFWorkbook();
             FileOutputStream fileOut = new FileOutputStream(filePath.replace("csv", "xlsx"))) {
            Sheet sheet = workbook.createSheet("Sheet1");
            String line;
            int rowNum = 0;
            while ((line = reader.readLine()) != null) {
                Row row = sheet.createRow(rowNum++);
                String[] columns = line.split(",");
                for (int i = 0; i < columns.length; i++) {
                    Cell cell = row.createCell(i);
                    cell.setCellValue(columns[i]);
                }
            }
            workbook.write(fileOut);
            System.out.println("CSV 文件已成功转换为 Excel 文件");
        } catch (IOException e) {
            e.printStackTrace();
        }

        // 读取Excel文件
        EasyExcel.read(filePath.replace("csv", "xlsx"), TianXianData.class, listener).sheet().headRowNumber(-1).doRead();
        return dataList;
    }

}