package com.qianhe.zqcz.xmrylsjl.service.impl;

import com.qianhe.common.enums.zqcz.ZqczRyflEnum;
import com.qianhe.common.enums.zqcz.ZqczXmlbEnum;
import com.qianhe.common.enums.zqcz.ZqczXmzyEnum;
import com.qianhe.common.exception.BusinessException;
import com.qianhe.common.utils.MtTimeUtil;
import com.qianhe.common.utils.MtUtils;
import com.qianhe.common.utils.SecurityUtils;
import com.qianhe.common.utils.StringUtils;
import com.qianhe.zqcz.pwxx.domain.ZqczPwxxQuery;
import com.qianhe.zqcz.pwxx.domain.ZqczPwxxVo;
import com.qianhe.zqcz.pwxx.mapper.ZqczPwxxMapper;
import com.qianhe.zqcz.ryxx.domain.ZqczRyxxQuery;
import com.qianhe.zqcz.ryxx.domain.ZqczRyxxVo;
import com.qianhe.zqcz.ryxx.mapper.ZqczRyxxMapper;
import com.qianhe.zqcz.xmgl.domain.ZqczXmglQuery;
import com.qianhe.zqcz.xmgl.domain.ZqczXmglVo;
import com.qianhe.zqcz.xmgl.mapper.ZqczXmglMapper;
import com.qianhe.zqcz.xmrylsjl.domain.XccjqktjVo;
import com.qianhe.zqcz.xmrylsjl.domain.ZqczXmRyLsjl;
import com.qianhe.zqcz.xmrylsjl.domain.ZqczXmRyLsjlQuery;
import com.qianhe.zqcz.xmrylsjl.domain.ZqczXmRyLsjlVo;
import com.qianhe.zqcz.xmrylsjl.mapper.ZqczXmRyLsjlMapper;
import com.qianhe.zqcz.xmrylsjl.service.ZqczXmRyLsjlService;
import com.qianhe.zqcz.zsrw.domain.ZqczZsrwVo;
import com.qianhe.zqcz.zsrw.mapper.ZqczZsrwMapper;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.ss.formula.functions.T;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.time.LocalDateTime;
import java.util.*;
import java.util.stream.Collectors;

@Slf4j
@Service
public class ZqczXmRyLsjlServiceImpl implements ZqczXmRyLsjlService {
    @Autowired
    private ZqczXmRyLsjlMapper zqczXmRyLsjlMapper;
    @Autowired
    private ZqczXmglMapper zqczXmglMapper;
    @Autowired
    private ZqczRyxxMapper zqczRyxxMapper;
    @Autowired
    private ZqczPwxxMapper zqczPwxxMapper;
    @Autowired
    private ZqczZsrwMapper zqczZsrwMapper;

    /**
     * 条件查询
     */
    @Override
    public List<ZqczXmRyLsjlVo> list(ZqczXmRyLsjlQuery zqczXmRyLsjlQuery) {
        List<ZqczXmRyLsjlVo> dataList = zqczXmRyLsjlMapper.list(zqczXmRyLsjlQuery);
        return dataList;
    }

    /**
     * 开始抽检 参数校验
     *
     * @param zqczXmRyLsjlQuery
     */
    private void spotCheckAddCheck(ZqczXmRyLsjlQuery zqczXmRyLsjlQuery) {
        if (zqczXmRyLsjlQuery.getZsrwid() == null) {
            throw new BusinessException("直属任务ID不能为空");
        }
        if (zqczXmRyLsjlQuery.getJcrwid() == null) {
            throw new BusinessException("基层任务ID不能为空");
        }
        if (zqczXmRyLsjlQuery.getJcdwid() == null) {
            throw new BusinessException("基层单位ID不能为空");
        }
        if (zqczXmRyLsjlQuery.getXmzy() == null) {
            throw new BusinessException("项目专业不能为空");
        }
        if (!ZqczXmzyEnum.getAllCodes().contains(zqczXmRyLsjlQuery.getXmzy())) {
            throw new BusinessException(String.format("项目专业必须为%s, 当前值 %s", ZqczXmzyEnum.getAllCodeNameStrWithSeparator(), zqczXmRyLsjlQuery.getXmzy()));
        }
    }

    /**
     * 根据基层任务ID 开始项目抽检
     */
    @Transactional(rollbackFor = Exception.class)
    @Override
    public int spotCheckAdd(ZqczXmRyLsjlQuery zqczXmRyLsjlQuery) {
        // 数据校验
        spotCheckAddCheck(zqczXmRyLsjlQuery);

        // 项目字段
        Long zsrwid = zqczXmRyLsjlQuery.getZsrwid();  // 直属任务ID
        Long jcrwid = zqczXmRyLsjlQuery.getJcrwid();  // 基层任务ID
        Long jcdwid = zqczXmRyLsjlQuery.getJcdwid();  // 基层单位ID
        String xmzy = zqczXmRyLsjlQuery.getXmzy();  // 项目专业 1-采油 2-集输

        // 根据基层任务ID 删除已经抽取的项目
        zqczXmRyLsjlMapper.deleteByJcrwid(jcrwid);

        // 查询评委信息
        List<ZqczPwxxVo> pwxxList = zqczPwxxMapper.list(ZqczPwxxQuery.builder().jcrwid(jcrwid).build());
        if (Optional.ofNullable(pwxxList).orElse(Collections.emptyList()).isEmpty()) {
            throw new BusinessException("未录入评委信息");
        }

        // 1. 查询人员信息（姓名、单位、分类）
        List<ZqczRyxxVo> ryxxList = zqczRyxxMapper.list(ZqczRyxxQuery.builder().jcrwid(jcrwid).build());
        if (Optional.ofNullable(ryxxList).orElse(Collections.emptyList()).isEmpty()) {
            throw new BusinessException("未录入在岗人员信息");
        }

        // 2. 根据项目专业 查询项目信息
        List<ZqczXmglVo> xmglList = zqczXmglMapper.list(ZqczXmglQuery.builder().xmzy(xmzy).build());
        if (Optional.ofNullable(xmglList).orElse(Collections.emptyList()).isEmpty()) {
            throw new BusinessException("未录入项目信息");
        }

        // 3. 处理人员信息，按照人员分类 分组
        Map<String, List<ZqczRyxxVo>> ryxxGroupByRyflMap = ryxxList.stream().collect(Collectors.groupingBy(ZqczRyxxVo::getRyfl));
        List<ZqczRyxxVo> ldbzRyxxList = ryxxGroupByRyflMap.get(ZqczRyflEnum.LINGDAO_BANZI.getCode());  // 领导班子 人员信息
        List<ZqczRyxxVo> ssyzxRyxxList = ryxxGroupByRyflMap.get(ZqczRyflEnum.SANSHI_YIZHONGXIN.getCode());  // 三室一中心 人员信息
        List<ZqczRyxxVo> bzRyxxList = ryxxGroupByRyflMap.get(ZqczRyflEnum.BANZHAN.getCode());  // 班站 人员信息

        List<String> bzDistinctList = new ArrayList<>(); // 获取所有班站单位(去重)
        if (!Optional.ofNullable(bzRyxxList).orElse(Collections.emptyList()).isEmpty()) {
            bzDistinctList = bzRyxxList.stream().map(ZqczRyxxVo::getDwmc).distinct().collect(Collectors.toList());// 获取所有班站单位（去重）
        }

        // 4. 处理项目信息，按照项目类别 分组
        Map<String, List<ZqczXmglVo>> xmglGroupByXmlbMap = xmglList.stream().collect(Collectors.groupingBy(ZqczXmglVo::getXmlb));
        List<ZqczXmglVo> gryjXmglList = xmglGroupByXmlbMap.get(ZqczXmlbEnum.GEREN_YINGJI.getCode());  // 个人应急项目
        List<ZqczXmglVo> tdyjXmglList = xmglGroupByXmlbMap.get(ZqczXmlbEnum.TUANDUI_YINGJI.getCode());  // 团队应急项目
        List<ZqczXmglVo> drsrXmglList = xmglGroupByXmlbMap.get(ZqczXmlbEnum.DANREN_SHUANGREN.getCode());  // 单人/双人操作项目
        List<ZqczXmglVo> tdczXmglList = xmglGroupByXmlbMap.get(ZqczXmlbEnum.TUANDUI_CAOZUO.getCode());  // 团队操作项目

        List<ZqczRyxxVo> desigRyxxList = new ArrayList<>();  // 抽取指定班站 人员信息
        if (!Optional.ofNullable(bzDistinctList).orElse(Collections.emptyList()).isEmpty()) {
            if (ZqczXmzyEnum.CAIYOU_ZHUANYE.getCode().equals(xmzy)) {
                String lockbzName = MtUtils.getRandomList(bzDistinctList, 1).get(0);
                if (!Optional.ofNullable(bzRyxxList).orElse(Collections.emptyList()).isEmpty()) {
                    desigRyxxList = bzRyxxList.stream().filter(item -> lockbzName.equals(item.getDwmc())).collect(Collectors.toList());// 从指定班站中抽取人员信息
                }
            } else {
                List<String> spotBzList = MtUtils.getRandomList(bzDistinctList, 3);
                if (!Optional.ofNullable(bzRyxxList).orElse(Collections.emptyList()).isEmpty()) {
                    desigRyxxList = bzRyxxList.stream().filter(item -> spotBzList.contains(item.getDwmc())).collect(Collectors.toList());// 从指定班站中抽取人员信息
                }
            }
        }

        List<ZqczXmRyLsjl> finalList = new ArrayList<>();

        if (ZqczXmzyEnum.CAIYOU_ZHUANYE.getCode().equals(xmzy)) {  // 采油专业 项目抽取
            // 5. 处理 个人应急项目（从领导班子中抽取一人、抽三室一中心抽取两人、从班站中抽取一人） 先抽人，在抽项目
            List<ZqczXmRyLsjl> finalCyGryjxmList = spotCheckCyGryjxm(zsrwid, jcrwid, jcdwid, ldbzRyxxList, ssyzxRyxxList, desigRyxxList, gryjXmglList);
            if (!Optional.ofNullable(finalCyGryjxmList).orElse(Collections.emptyList()).isEmpty()) {
                finalList.addAll(finalCyGryjxmList);
            }

            // 6. 处理 标准化操作项目 （根据项目人数从班站中随机抽取指定人数）先抽项目，在抽人
            ZqczXmRyLsjl finalCyBzhczxm = spotCheckCyBzhczxm(zsrwid, jcrwid, jcdwid, desigRyxxList, drsrXmglList, tdczXmglList);
            if (finalCyBzhczxm != null) {
                finalList.add(finalCyBzhczxm);
            }

            // 7. 处理 团队应急项目（从领导班子中抽取一人、抽三室一中心抽取两人、在根据剩余项目人数从班站中抽取）先抽项目，在抽人
            ZqczXmRyLsjl finalCjTdyjxm = spotCheckCyTdyjxm(zsrwid, jcrwid, jcdwid, ldbzRyxxList, ssyzxRyxxList, desigRyxxList, tdyjXmglList);
            if (finalCjTdyjxm != null) {
                finalList.add(finalCjTdyjxm);
            }
        } else {  // 集输专业 项目抽取
            // 1. 抽检个人应急项目（从领导班子中抽取一人、抽三室一中心抽取两人、从选中的3个班站中各抽取一人） 先抽人，在抽项目
            List<ZqczXmRyLsjl> finalJsGryjxmList = spotCheckJsGryjxm(zsrwid, jcrwid, jcdwid, ldbzRyxxList, ssyzxRyxxList, desigRyxxList, gryjXmglList);
            if (!Optional.ofNullable(finalJsGryjxmList).orElse(Collections.emptyList()).isEmpty()) {
                finalList.addAll(finalJsGryjxmList);
            }

            // 2. 抽检 标准化操作项目（从选中的3个班站中各抽取2人） 先抽人，在抽项目
            List<ZqczXmRyLsjl> finalJsBzhczxmList = spotCheckJsBzhczxm(zsrwid, jcrwid, jcdwid, desigRyxxList, drsrXmglList, tdczXmglList);
            if (!Optional.ofNullable(finalJsBzhczxmList).orElse(Collections.emptyList()).isEmpty()) {
                finalList.addAll(finalJsBzhczxmList);
            }

            // 3. 抽检 团队应急项目 （第一个班站从领导班子中抽取一人、抽三室一中心抽取一人、班站成员自行选择，默认情况下选择所有的班站成员，其余班站随机抽取项目，班站人员默认全部）
            List<ZqczXmRyLsjl> finalJsTdyjxmList = spotCheckJsTdyjxm(zsrwid, jcrwid, jcdwid, ldbzRyxxList, ssyzxRyxxList, desigRyxxList, tdyjXmglList);
            if (!Optional.ofNullable(finalJsTdyjxmList).orElse(Collections.emptyList()).isEmpty()) {
                finalList.addAll(finalJsTdyjxmList);
            }
        }

        return zqczXmRyLsjlMapper.batchAdd(finalList);
    }

    /**
     * 修改
     */
    @Override
    public int edit(ZqczXmRyLsjl zqczXmRyLsjl, String username) {
        if (zqczXmRyLsjl.getXmryid() == null) {
            throw new BusinessException("项目人员ID不能为空");
        }
        zqczXmRyLsjl.setUpdate_by(username);
        return zqczXmRyLsjlMapper.edit(zqczXmRyLsjl);
    }

    /**
     * 现场抽取情况统计
     */
    @Override
    public List<XccjqktjVo> spotCheckStatus(ZqczXmRyLsjlQuery zqczXmRyLsjlQuery) {
        if (zqczXmRyLsjlQuery.getJcrwid() == null) {
            throw new BusinessException("基层任务ID不能为空");
        }

        List<ZqczRyxxVo> ryxxList = zqczRyxxMapper.list(ZqczRyxxQuery.builder().jcrwid(zqczXmRyLsjlQuery.getJcrwid()).build());
        if (Optional.ofNullable(ryxxList).orElse(Collections.emptyList()).isEmpty()) {
            throw new BusinessException("未录入在岗人员信息");
        }

        List<XccjqktjVo> xccjqktjVoList = new ArrayList<>();

        List<ZqczXmRyLsjlVo> dataList = list(zqczXmRyLsjlQuery);
        for (ZqczXmRyLsjlVo zqczXmRyLsjlVo : dataList) {
            String rymcStr = zqczXmRyLsjlVo.getRymc();
            List<String> rymcList = Arrays.asList(rymcStr.split(","));
            for (String rymc : rymcList) {
                List<ZqczRyxxVo> filterList = ryxxList.stream().filter(item -> rymc.equals(item.getRymc())).collect(Collectors.toList());
                if (Optional.ofNullable(filterList).orElse(Collections.emptyList()).isEmpty()) {
                    continue;
                }
                ZqczRyxxVo zqczRyxxVo = filterList.get(0);
                xccjqktjVoList.add(new XccjqktjVo(zqczRyxxVo, zqczXmRyLsjlVo));
            }

        }
        return xccjqktjVoList;
    }

    /**
     * 现场抽取情况表导出
     */
    @Override
    public void spotCheckStatusExport(HttpServletResponse response, ZqczXmRyLsjlQuery zqczXmRyLsjlQuery) {
        if (zqczXmRyLsjlQuery.getZsrwid() == null) {
            throw new BusinessException("直属任务ID不能为空");
        }
        if (zqczXmRyLsjlQuery.getJcrwid() == null) {
            throw new BusinessException("基层任务ID不能为空");
        }
        // 查询直属任务
        ZqczZsrwVo zqczZsrwVo = zqczZsrwMapper.info(zqczXmRyLsjlQuery.getZsrwid());
        if (zqczZsrwVo == null) {
            throw new BusinessException("暂无查询到关联的直属任务");
        }

        // 设置导出表格的标题
        List<String> tableTitle = Arrays.asList("序号", "基层单位", "类别", "姓名", "参与项目类型", "参与项目名称", "练兵奖品", "签字", "备注");

        // 拼接导出数据
        List<XccjqktjVo> dataList = spotCheckStatus(zqczXmRyLsjlQuery);
        List<Object[]> finalList = new ArrayList<>();
        for (int i = 0; i < dataList.size(); i++) {
            List<Object> rowList = new ArrayList<>();
            rowList.add(i + 1);  // 序号
            rowList.add(dataList.get(i).getJcdwmc());  // 基层单位

            String ryfl = dataList.get(i).getRyfl();
            String flmc = ZqczRyflEnum.getNameByCode(ryfl);
            rowList.add(flmc);  // 类别

            rowList.add(dataList.get(i).getRymc());  // 姓名

            String xmlb = dataList.get(i).getXmlb();
            String lbmc = ZqczXmlbEnum.getNameByCode(xmlb);
            rowList.add(lbmc);  // 参与项目类型

            rowList.add(dataList.get(i).getXmmc());  // 参与项目名称
            rowList.add("");  // 练兵奖品
            rowList.add("");  // 签字
            rowList.add("");  // 备注
            finalList.add(rowList.toArray());
        }
        // 最终二维数组
        Object[][] finalArray = finalList.toArray(new Object[0][]);

        Workbook workbook = new XSSFWorkbook();
        try {
            Sheet sheet = workbook.createSheet();  // 创建标签页
            createMainTitle(workbook, sheet, tableTitle.size() - 1);
            createSubTitle(workbook, sheet, zqczZsrwVo.getDeptName());
            createTableTitle(workbook, sheet, tableTitle);
            writeTableData(workbook, sheet, finalArray, 3);
            setExportResponseHeader(response, String.format("现场抽取情况表_%s.xlsx", new Date().getTime()));
            workbook.write(response.getOutputStream());
        } catch (Exception e) {
            e.printStackTrace();
            try {
                response.setStatus(HttpServletResponse.SC_INTERNAL_SERVER_ERROR);
                response.getWriter().write("导出失败：" + e.getMessage());
            } catch (IOException ex) {
                ex.printStackTrace();
            }
        }
    }

    /**
     * 修改盲抽人员名称
     */
    @Override
    public int editRymc(ZqczXmRyLsjl zqczXmRyLsjl) {
        if (zqczXmRyLsjl.getXmryid() == null) {
            throw new BusinessException("项目人员ID不能为空");
        }
        if (StringUtils.isEmpty(zqczXmRyLsjl.getRymc())) {
            throw new BusinessException("盲抽人员姓名不能为空");
        }

        ZqczXmRyLsjlVo zqczXmRyLsjlVo = zqczXmRyLsjlMapper.info(zqczXmRyLsjl.getXmryid());

        boolean xmztFlag = ZqczXmzyEnum.JISHU_ZHUANYE.getCode().equals(zqczXmRyLsjlVo.getXmzy());
        boolean xmlbFlag = ZqczXmlbEnum.TUANDUI_YINGJI.getCode().equals(zqczXmRyLsjlVo.getXmlb());

        if (xmztFlag && xmlbFlag) {  // 只有是集输专业并且项目类别是团队应急时才能修改
            List<String> oldRymcList = Arrays.asList(zqczXmRyLsjlVo.getRymc().trim().split(","));  // 之前存储的盲抽人员
            List<String> newRymcList = Arrays.stream(zqczXmRyLsjl.getRymc().split(",")).map(String::trim).filter(s -> !s.isEmpty()).collect(Collectors.toList());  // 现在修改的盲抽人员

            List<String> addRymcList = newRymcList.stream().filter(item -> !oldRymcList.contains(item)).collect(Collectors.toList());  // 计算差值
            if (!Optional.ofNullable(addRymcList).orElse(Collections.emptyList()).isEmpty()) {
                List<ZqczRyxxVo> addRyxxList = zqczRyxxMapper.checkRyfl(addRymcList);
                List<String> addRyflList = addRyxxList.stream().map(ZqczRyxxVo::getRyfl).distinct().collect(Collectors.toList());
                String addRyflStr = String.join(",", addRyflList);
                if (!ZqczRyflEnum.BANZHAN.getCode().equals(addRyflStr)) {
                    throw new BusinessException("只能新增班站人员");
                }
            }

            List<String> delRymcList = oldRymcList.stream().filter(item -> !newRymcList.contains(item)).collect(Collectors.toList());  // 计算差值
            if (!Optional.ofNullable(delRymcList).orElse(Collections.emptyList()).isEmpty()) {
                List<ZqczRyxxVo> delRyxxList = zqczRyxxMapper.checkRyfl(delRymcList);
                List<String> delRyflList = delRyxxList.stream().map(ZqczRyxxVo::getRyfl).distinct().collect(Collectors.toList());
                String delRyflStr = String.join(",", delRyflList);
                if (!ZqczRyflEnum.BANZHAN.getCode().equals(delRyflStr)) {
                    throw new BusinessException("只能删除班站人员");
                }
            }

            List<ZqczRyxxVo> newRyxxList = zqczRyxxMapper.checkRyfl(newRymcList);
            List<String> newDwmcList = newRyxxList.stream().map(ZqczRyxxVo::getDwmc).distinct().collect(Collectors.toList());
            String addDwmcStr = String.join(",", newDwmcList);
            return zqczXmRyLsjlMapper.edit(ZqczXmRyLsjl.builder().xmryid(zqczXmRyLsjl.getXmryid()).rymc(String.join(",", newRymcList)).dwmc(addDwmcStr).build());
        } else {
            throw new BusinessException("只有是集输专业并且项目类别是团队应急时才能修改");
        }
    }

    /**
     * 创建大标题
     */
    private void createMainTitle(Workbook workbook, Sheet sheet, Integer lastCol) {
        // ====================== 创建标题样式 ======================
        CellStyle mainTitleStyle = workbook.createCellStyle();
        mainTitleStyle.setAlignment(HorizontalAlignment.CENTER);  // 水平居中
        mainTitleStyle.setVerticalAlignment(VerticalAlignment.CENTER);  // 垂直居中

        Font mainTitleFont = workbook.createFont();  // 设置字体
        mainTitleFont.setBold(true);  // 加粗
        mainTitleFont.setFontHeightInPoints((short) 22);  // 字体大小
        mainTitleFont.setFontName("宋体");  // 字体名称
        mainTitleStyle.setFont(mainTitleFont);

        Row mainTitleRow = sheet.createRow(0);  // 第一行
        mainTitleRow.setHeightInPoints(46);  // 设置行高

        Cell cell = mainTitleRow.createCell(0);  // 创建单元格
        cell.setCellValue("现场抽取情况表");  // 设置单元格的内容
        cell.setCellStyle(mainTitleStyle);  // 设置单元格样式
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, lastCol));  // 单元格合并
    }

    /**
     * 创建副标题
     */
    public void createSubTitle(Workbook workbook, Sheet sheet, String subTitle) {
        // ====================== 创建副标题样式 ======================
        CellStyle subTitleStyle = workbook.createCellStyle();
        subTitleStyle.setAlignment(HorizontalAlignment.LEFT);  // 左对齐
        subTitleStyle.setVerticalAlignment(VerticalAlignment.CENTER);  // 垂直居中

        Font subTitleFont = workbook.createFont();  // 设置字体
        subTitleFont.setBold(true);  // 加粗
        subTitleFont.setFontHeightInPoints((short) 11);  // 字体大小
        subTitleFont.setFontName("宋体");  // 字体名称
        subTitleStyle.setFont(subTitleFont);

        Row subTitleRow = sheet.createRow(1);  // 第二行
        subTitleRow.setHeightInPoints(25);  // 设置行高

        Cell cell = subTitleRow.createCell(0);  // 创建单元格
        cell.setCellValue(String.format("直属单位：%s", subTitle));  // 设置单元格的内容
        cell.setCellStyle(subTitleStyle);  // 设置单元格样式
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 1));  // 单元格合并
    }

    /**
     * 创建表格标题
     */
    public static void createTableTitle(Workbook workbook, Sheet sheet, List<String> tableTitle) {
        CellStyle tableHeaderStyle = workbook.createCellStyle();  // 创建单元格样式类
        tableHeaderStyle.setAlignment(HorizontalAlignment.CENTER);  // 左对齐
        tableHeaderStyle.setVerticalAlignment(VerticalAlignment.CENTER);  // 垂直居中

        tableHeaderStyle.setBorderTop(BorderStyle.THIN);     // 上边框
        tableHeaderStyle.setBorderBottom(BorderStyle.THIN);  // 下边框
        tableHeaderStyle.setBorderLeft(BorderStyle.THIN);    // 左边框
        tableHeaderStyle.setBorderRight(BorderStyle.THIN);   // 右边框

        Font tableHeaderFont = workbook.createFont();  // 设置字体
        tableHeaderFont.setBold(true);  // 加粗
        tableHeaderFont.setFontHeightInPoints((short) 11);  // 字体大小
        tableHeaderFont.setFontName("宋体");  // 字体名称
        tableHeaderStyle.setFont(tableHeaderFont);

        Row tableHeaderRow = sheet.createRow(2);  // 创建第三行
        tableHeaderRow.setHeightInPoints(43);  // 设置行高

        // 循环创建每一列表头
        for (int i = 0; i < tableTitle.size(); i++) {
            // 在当前行创建第 i 列（i 从 0 开始）
            Cell cell = tableHeaderRow.createCell(i);

            // 设置单元格的值为表头文字
            cell.setCellValue(tableTitle.get(i));

            // 将表头样式应用到该单元格
            cell.setCellStyle(tableHeaderStyle);
        }
    }

    /**
     * 设置数据
     */
    public static void writeTableData(Workbook workbook, Sheet sheet, Object[][] finalArray, Integer firstRow) {
        if (finalArray == null || finalArray.length == 0) {
            return;
        }

        // 1. 创建通用基础样式（边框+垂直居中）
        CellStyle baseStyle = workbook.createCellStyle();
        baseStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        baseStyle.setBorderTop(BorderStyle.THIN);
        baseStyle.setBorderBottom(BorderStyle.THIN);
        baseStyle.setBorderLeft(BorderStyle.THIN);
        baseStyle.setBorderRight(BorderStyle.THIN);

        // 2. 普通文本样式（左对齐，继承基础样式）
        CellStyle dataStyle = workbook.createCellStyle();
        dataStyle.cloneStyleFrom(baseStyle); // 继承边框+垂直居中
        dataStyle.setAlignment(HorizontalAlignment.LEFT);

        // 3. 数字千分位样式（右对齐，继承基础样式）
        CellStyle numberStyle = workbook.createCellStyle();
        numberStyle.cloneStyleFrom(baseStyle);
        numberStyle.setAlignment(HorizontalAlignment.RIGHT);
        numberStyle.setDataFormat(workbook.createDataFormat().getFormat("#,##0"));

        for (int i = 0; i < finalArray.length; i++) {
            Row row = sheet.createRow(i + firstRow);
            row.setHeightInPoints(20);  // 设置行高

            for (int j = 0; j < finalArray[i].length; j++) {
                // 创建单元格
                Cell cell = row.createCell(j);
                // 单元格赋值
                Object value = finalArray[i][j];
                if (value instanceof String) {
                    cell.setCellValue((String) value);  // 设置为字符串
                } else if (value instanceof Integer) {
                    int intValue = (Integer) value;
                    cell.setCellValue(intValue);  // 设置为数值
                } else if (value instanceof LocalDateTime) {
                    String timeValue = MtTimeUtil.dateTimeFormatNyr((LocalDateTime) value);
                    cell.setCellValue(timeValue);  // 将日期类型转化为字符串类型
                }
                // 设置单元格样式
                cell.setCellStyle(dataStyle);
            }
        }

        // ====================== 2. 所有数据写完后，统一自动列宽（关键！） ======================
        int colCount = finalArray[0].length;
        for (int j = 0; j < colCount; j++) {
            // 自动计算宽度
            sheet.autoSizeColumn(j);

            // 对中文进行加宽（关键！解决显示不全）
            int width = sheet.getColumnWidth(j) * 14 / 10;

            // 最大宽度限制（Excel 限制 255 个字符宽度）
            int maxWidth = 255 * 256;
            if (width > maxWidth) {
                width = maxWidth;
            }

            sheet.setColumnWidth(j, width);
        }
    }

    /**
     * 设置响应头
     */
    private void setExportResponseHeader(HttpServletResponse response, String fileName) throws IOException {
        // 设置内容类型
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("UTF-8");

        // 处理中文文件名
        String encodedFileName = URLEncoder.encode(fileName, "UTF-8")
                .replaceAll("\\+", "%20");
        response.setHeader("Content-disposition",
                "attachment;filename*=utf-8''" + encodedFileName);

        // 禁用缓存
        response.setHeader("Cache-Control", "no-cache, no-store, must-revalidate");
        response.setHeader("Pragma", "no-cache");
        response.setDateHeader("Expires", 0);
    }

    /**
     * 抽检 个人应急项目（采油）
     *
     * @param zsrwid        直属任务ID
     * @param jcrwid        基层任务ID
     * @param jcdwid        基层单位ID
     * @param ldbzRyxxList  领导班子 人员信息
     * @param ssyzxRyxxList 三室一中心 人员信息
     * @param desigRyxxList 抽取指定班站 人员信息
     * @param gryjXmglList  个人应急项目
     * @return
     */
    private List<ZqczXmRyLsjl> spotCheckCyGryjxm(Long zsrwid, Long jcrwid, Long jcdwid, List<ZqczRyxxVo> ldbzRyxxList, List<ZqczRyxxVo> ssyzxRyxxList, List<ZqczRyxxVo> desigRyxxList, List<ZqczXmglVo> gryjXmglList) {
        List<ZqczXmRyLsjl> dataList = new ArrayList<>();

        ZqczRyxxVo lockldbzRyxxVo = null;  // 锁定 领导班子
        if (!Optional.ofNullable(ldbzRyxxList).orElse(Collections.emptyList()).isEmpty()) {
            List<ZqczRyxxVo> spotLdbzList = MtUtils.getRandomList(ldbzRyxxList, 1);  // 抽取 领导班子 人员信息
            lockldbzRyxxVo = spotLdbzList.get(0);
        }

        List<ZqczRyxxVo> spotLdbzList = new ArrayList<>();  // 锁定 三室一中心
        if (!Optional.ofNullable(ssyzxRyxxList).orElse(Collections.emptyList()).isEmpty()) {
            if (ssyzxRyxxList.size() >= 2) {  // 这里判断主要是为了判断 如果 三室一中心中的人数不够2个人
                spotLdbzList = MtUtils.getRandomList(ssyzxRyxxList, 2);  // 抽取 三室一中心 人员信息
            } else {
                spotLdbzList = MtUtils.getRandomList(ssyzxRyxxList, 1);  // 抽取 三室一中心 人员信息
            }
        }

        ZqczRyxxVo lockbzRyxxVo = null;  // 锁定 班站
        if (!Optional.ofNullable(desigRyxxList).orElse(Collections.emptyList()).isEmpty()) {
            List<ZqczRyxxVo> spotbzList = MtUtils.getRandomList(desigRyxxList, 1);  // 抽取 班站 人员信息
            lockbzRyxxVo = spotbzList.get(0);
        }

        // 需要处理个人应急项目为空的情况
        if (!Optional.ofNullable(gryjXmglList).orElse(Collections.emptyList()).isEmpty()) {
            if (lockldbzRyxxVo != null) {
                ZqczXmglVo lockldbzXmgl = MtUtils.getRandomList(gryjXmglList, 1).get(0);  // 领导班子 锁定项目
                dataList.add(new ZqczXmRyLsjl(zsrwid, jcrwid, jcdwid, lockldbzRyxxVo, lockldbzXmgl));
            }
            if (!Optional.ofNullable(spotLdbzList).orElse(Collections.emptyList()).isEmpty()) {
                for (ZqczRyxxVo zqczRyxxVo : spotLdbzList) {
                    ZqczXmglVo lockssyzxXmgl = MtUtils.getRandomList(gryjXmglList, 1).get(0);  // 三室一中心 锁定项目
                    dataList.add(new ZqczXmRyLsjl(zsrwid, jcrwid, jcdwid, zqczRyxxVo, lockssyzxXmgl));
                }
            }
            if (lockbzRyxxVo != null) {
                ZqczXmglVo lockbzXmgl = MtUtils.getRandomList(gryjXmglList, 1).get(0);  // 班站 锁定项目
                dataList.add(new ZqczXmRyLsjl(zsrwid, jcrwid, jcdwid, lockbzRyxxVo, lockbzXmgl));
            }
        }
        return dataList;
    }

    /**
     * 抽检 标准化操作项目（采油）
     *
     * @param zsrwid        直属任务ID
     * @param jcrwid        基层任务ID
     * @param jcdwid        基层单位ID
     * @param desigRyxxList 抽取指定班站 人员信息
     * @param drsrXmglList  单人/双人操作项目
     * @param tdczXmglList  团队操作项目
     * @return
     */
    private ZqczXmRyLsjl spotCheckCyBzhczxm(Long zsrwid, Long jcrwid, Long jcdwid, List<ZqczRyxxVo> desigRyxxList, List<ZqczXmglVo> drsrXmglList, List<ZqczXmglVo> tdczXmglList) {
        // 合并单人/双人操作项目，团队操作项目
        List<ZqczXmglVo> bzhczXmglList = new ArrayList<>();
        if (!Optional.ofNullable(drsrXmglList).orElse(Collections.emptyList()).isEmpty()) {
            bzhczXmglList.addAll(drsrXmglList);
        }
        if (!Optional.ofNullable(tdczXmglList).orElse(Collections.emptyList()).isEmpty()) {
            bzhczXmglList.addAll(tdczXmglList);
        }
        // 抽检项目
        if (Optional.ofNullable(bzhczXmglList).orElse(Collections.emptyList()).isEmpty()) {
            return null;
        }
        // 判断班站人员信息是否为空
        if (Optional.ofNullable(desigRyxxList).orElse(Collections.emptyList()).isEmpty()) {
            return null;
        }
        ZqczXmglVo bzhczXmglVo = MtUtils.getRandomList(bzhczXmglList, 1).get(0);
        Integer xmrs = bzhczXmglVo.getXmrs();// 获取项目所需要的人数

        // 最终的人员信息列表
        List<ZqczRyxxVo> finalList = desigRyxxList.size() <= xmrs ? desigRyxxList : MtUtils.getRandomList(desigRyxxList, xmrs);

        String dwmc = finalList.stream().map(ZqczRyxxVo::getDwmc).distinct().collect(Collectors.joining(","));
        String rymc = finalList.stream().map(ZqczRyxxVo::getRymc).distinct().collect(Collectors.joining(","));

        ZqczXmRyLsjl xmRyLsjl = new ZqczXmRyLsjl(zsrwid, jcrwid, jcdwid, bzhczXmglVo, dwmc, rymc);
        return xmRyLsjl;
    }

    /**
     * 抽检 团队应急项目（采油）
     *
     * @param zsrwid        直属任务ID
     * @param jcrwid        基层任务ID
     * @param jcdwid        基层单位ID
     * @param ldbzRyxxList  领导班子 人员信息
     * @param ssyzxRyxxList 三室一中心 人员信息
     * @param desigRyxxList 抽取指定班站 人员信息
     * @param tdyjXmglList  团队应急项目
     * @return
     */
    private ZqczXmRyLsjl spotCheckCyTdyjxm(Long zsrwid, Long jcrwid, Long jcdwid, List<ZqczRyxxVo> ldbzRyxxList, List<ZqczRyxxVo> ssyzxRyxxList, List<ZqczRyxxVo> desigRyxxList, List<ZqczXmglVo> tdyjXmglList) {
        // 抽检项目
        if (Optional.ofNullable(tdyjXmglList).orElse(Collections.emptyList()).isEmpty()) {
            return null;
        }
        // 获取项目所需要的人数
        ZqczXmglVo bzhczXmglVo = MtUtils.getRandomList(tdyjXmglList, 1).get(0);
        Integer xmrs = bzhczXmglVo.getXmrs();
        if (xmrs == null) {
            return null;
        }

        List<ZqczRyxxVo> finalList = new ArrayList<>();
        // 始终锁定领导班子（xmrs >= 1）
        if (!Optional.ofNullable(ldbzRyxxList).orElse(Collections.emptyList()).isEmpty()) {
            finalList.add(MtUtils.getRandomList(ldbzRyxxList, 1).get(0));
        }
        // xmrs >= 2 时叠加三室一中心
        if (xmrs >= 2 && !Optional.ofNullable(ssyzxRyxxList).orElse(Collections.emptyList()).isEmpty()) {
            finalList.add(MtUtils.getRandomList(ssyzxRyxxList, 1).get(0));
        }
        // xmrs >= 3 时叠加班站人员
        if (xmrs >= 3 && !Optional.ofNullable(desigRyxxList).orElse(Collections.emptyList()).isEmpty()) {
            int bzNum = xmrs - 2;
            finalList.addAll(MtUtils.getRandomList(desigRyxxList, bzNum));
        }

        String dwmc = finalList.stream().map(ZqczRyxxVo::getDwmc).distinct().collect(Collectors.joining(","));
        String rymc = finalList.stream().map(ZqczRyxxVo::getRymc).distinct().collect(Collectors.joining(","));


        ZqczXmRyLsjl xmRyLsjl = new ZqczXmRyLsjl(zsrwid, jcrwid, jcdwid, bzhczXmglVo, dwmc, rymc);
        return xmRyLsjl;
    }

    /**
     * 抽检 个人应急项目（集输）
     *
     * @param zsrwid        直属任务ID
     * @param jcrwid        基层任务ID
     * @param jcdwid        基层单位ID
     * @param ldbzRyxxList  领导班子 人员信息
     * @param ssyzxRyxxList 三室一中心 人员信息
     * @param desigRyxxList 抽取指定班站 人员信息
     * @param gryjXmglList  个人应急项目
     * @return
     */
    private List<ZqczXmRyLsjl> spotCheckJsGryjxm(Long zsrwid, Long jcrwid, Long jcdwid, List<ZqczRyxxVo> ldbzRyxxList, List<ZqczRyxxVo> ssyzxRyxxList, List<ZqczRyxxVo> desigRyxxList, List<ZqczXmglVo> gryjXmglList) {
        List<ZqczXmRyLsjl> dataList = new ArrayList<>();

        ZqczRyxxVo lockldbzRyxxVo = null;  // 锁定 领导班子
        if (!Optional.ofNullable(ldbzRyxxList).orElse(Collections.emptyList()).isEmpty()) {
            List<ZqczRyxxVo> spotLdbzList = MtUtils.getRandomList(ldbzRyxxList, 1);  // 抽取 领导班子 人员信息
            lockldbzRyxxVo = spotLdbzList.get(0);
        }

        List<ZqczRyxxVo> spotLdbzList = new ArrayList<>();  // 锁定 三室一中心
        if (!Optional.ofNullable(ssyzxRyxxList).orElse(Collections.emptyList()).isEmpty()) {
            if (ssyzxRyxxList.size() >= 2) {  // 这里判断主要是为了判断 如果 三室一中心中的人数不够2个人
                spotLdbzList = MtUtils.getRandomList(ssyzxRyxxList, 2);  // 抽取 三室一中心 人员信息
            } else {
                spotLdbzList = MtUtils.getRandomList(ssyzxRyxxList, 1);  // 抽取 三室一中心 人员信息
            }
        }

        List<ZqczRyxxVo> spotbzList = new ArrayList<>();  // 锁定 班站
        Map<String, List<ZqczRyxxVo>> spotbzGroupMap = desigRyxxList.stream().collect(Collectors.groupingBy(ZqczRyxxVo::getDwmc));  // 根据班站名称分组
        spotbzGroupMap.forEach((key, value) -> {
            if (!Optional.ofNullable(value).orElse(Collections.emptyList()).isEmpty()) {
                spotbzList.add(MtUtils.getRandomList(value, 1).get(0));
            }
        });

        // 需要处理个人应急项目为空的情况
        if (!Optional.ofNullable(gryjXmglList).orElse(Collections.emptyList()).isEmpty()) {
            if (lockldbzRyxxVo != null) {
                ZqczXmglVo lockldbzXmgl = MtUtils.getRandomList(gryjXmglList, 1).get(0);  // 领导班子 锁定项目
                dataList.add(new ZqczXmRyLsjl(zsrwid, jcrwid, jcdwid, lockldbzRyxxVo, lockldbzXmgl));
            }
            if (!Optional.ofNullable(spotLdbzList).orElse(Collections.emptyList()).isEmpty()) {
                for (ZqczRyxxVo zqczRyxxVo : spotLdbzList) {
                    ZqczXmglVo lockssyzxXmgl = MtUtils.getRandomList(gryjXmglList, 1).get(0);  // 三室一中心 锁定项目
                    dataList.add(new ZqczXmRyLsjl(zsrwid, jcrwid, jcdwid, zqczRyxxVo, lockssyzxXmgl));
                }
            }
            if (!Optional.ofNullable(spotbzList).orElse(Collections.emptyList()).isEmpty()) {
                for (ZqczRyxxVo zqczRyxxVo : spotbzList) {
                    ZqczXmglVo lockbzXmgl = MtUtils.getRandomList(gryjXmglList, 1).get(0);  // 班站 锁定项目
                    dataList.add(new ZqczXmRyLsjl(zsrwid, jcrwid, jcdwid, zqczRyxxVo, lockbzXmgl));
                }
            }
        }
        return dataList;
    }

    /**
     * 抽检 标准化操作项目（集输）
     *
     * @param zsrwid        直属任务ID
     * @param jcrwid        基层任务ID
     * @param jcdwid        基层单位ID
     * @param desigRyxxList 抽取指定班站 人员信息
     * @param drsrXmglList  单人/双人操作项目
     * @param tdczXmglList  团队操作项目
     * @return
     */
    private List<ZqczXmRyLsjl> spotCheckJsBzhczxm(Long zsrwid, Long jcrwid, Long jcdwid, List<ZqczRyxxVo> desigRyxxList, List<ZqczXmglVo> drsrXmglList, List<ZqczXmglVo> tdczXmglList) {
        List<ZqczXmRyLsjl> dataList = new ArrayList<>();

        // 合并单人/双人操作项目，团队操作项目
        List<ZqczXmglVo> bzhczXmglList = new ArrayList<>();
        if (!Optional.ofNullable(drsrXmglList).orElse(Collections.emptyList()).isEmpty()) {
            bzhczXmglList.addAll(drsrXmglList);
        }
        if (!Optional.ofNullable(tdczXmglList).orElse(Collections.emptyList()).isEmpty()) {
            bzhczXmglList.addAll(tdczXmglList);
        }

        // 抽检项目
        if (Optional.ofNullable(bzhczXmglList).orElse(Collections.emptyList()).isEmpty()) {
            return dataList;
        }
        // 判断班站人员信息是否为空
        if (Optional.ofNullable(desigRyxxList).orElse(Collections.emptyList()).isEmpty()) {
            return dataList;
        }

        // 抽人
        List<ZqczRyxxVo> spotbzList = new ArrayList<>();  // 锁定 班站
        Map<String, List<ZqczRyxxVo>> spotbzGroupMap = desigRyxxList.stream().collect(Collectors.groupingBy(ZqczRyxxVo::getDwmc));  // 根据班站名称分组
        spotbzGroupMap.forEach((key, value) -> {  // 每一组抽取两个人
            if (!Optional.ofNullable(value).orElse(Collections.emptyList()).isEmpty()) {
                List<ZqczRyxxVo> randomList = MtUtils.getRandomList(value, 2);
                if (!Optional.ofNullable(randomList).orElse(Collections.emptyList()).isEmpty()) {
                    spotbzList.addAll(randomList);
                }
            }
        });

        // 需要处理 标准化操作项目 为空的情况
        if (!Optional.ofNullable(bzhczXmglList).orElse(Collections.emptyList()).isEmpty()) {
            if (!Optional.ofNullable(spotbzList).orElse(Collections.emptyList()).isEmpty()) {
                for (ZqczRyxxVo zqczRyxxVo : spotbzList) {
                    ZqczXmglVo lockbzXmgl = MtUtils.getRandomList(bzhczXmglList, 1).get(0);  // 班站 锁定项目
                    dataList.add(new ZqczXmRyLsjl(zsrwid, jcrwid, jcdwid, zqczRyxxVo, lockbzXmgl));
                }
            }
        }
        return dataList;
    }

    /**
     * 抽检 团队应急项目（集输）
     *
     * @param zsrwid        直属任务ID
     * @param jcrwid        基层任务ID
     * @param jcdwid        基层单位ID
     * @param ldbzRyxxList  领导班子 人员信息
     * @param ssyzxRyxxList 三室一中心 人员信息
     * @param desigRyxxList 抽取指定班站 人员信息
     * @param tdyjXmglList  团队应急项目
     * @return
     */
    private List<ZqczXmRyLsjl> spotCheckJsTdyjxm(Long zsrwid, Long jcrwid, Long jcdwid, List<ZqczRyxxVo> ldbzRyxxList, List<ZqczRyxxVo> ssyzxRyxxList, List<ZqczRyxxVo> desigRyxxList, List<ZqczXmglVo> tdyjXmglList) {
        List<ZqczXmRyLsjl> dataList = new ArrayList<>();

        // 抽检项目
        if (Optional.ofNullable(tdyjXmglList).orElse(Collections.emptyList()).isEmpty()) {
            return null;
        }

        // 查询班站列表
        List<String> spotCheckBzNameList = desigRyxxList.stream().map(ZqczRyxxVo::getDwmc).distinct().collect(Collectors.toList());
        if (Optional.ofNullable(spotCheckBzNameList).orElse(Collections.emptyList()).isEmpty()) {
            return null;
        }

        // 分组
        Map<String, List<ZqczRyxxVo>> spotCheckBzGroupMap = desigRyxxList.stream().collect(Collectors.groupingBy(ZqczRyxxVo::getDwmc));

        for (int i = 0; i < spotCheckBzNameList.size(); i++) {
            ZqczXmglVo spotCheckTdyjXmgl = MtUtils.getRandomList(tdyjXmglList, 1).get(0);  // 当前班站抽取的项目

            String groupField = spotCheckBzNameList.get(i);
            List<ZqczRyxxVo> currentRyxxList = spotCheckBzGroupMap.get(groupField);  // 获取当前选中班站的人员信息
            if (Optional.ofNullable(currentRyxxList).orElse(Collections.emptyList()).isEmpty()) {  // 如果人员信息为空则跳过此次循环
                continue;
            }

            ZqczRyxxVo lockldbzRyxxVo = null;  // 锁定 领导班子 人员信息（抽一个人）
            ZqczRyxxVo lockSpotCheckSsyzxRyxx = null;  // 锁定 三室一中心 人员信息（抽一个人）

            if (i == 0) {  // 第一个班站坐特殊处理
                if (!Optional.ofNullable(ldbzRyxxList).orElse(Collections.emptyList()).isEmpty()) {
                    List<ZqczRyxxVo> spotLdbzList = MtUtils.getRandomList(ldbzRyxxList, 1);  // 抽取 领导班子 人员信息
                    lockldbzRyxxVo = spotLdbzList.get(0);
                }
                if (!Optional.ofNullable(ssyzxRyxxList).orElse(Collections.emptyList()).isEmpty()) {
                    lockSpotCheckSsyzxRyxx = MtUtils.getRandomList(ssyzxRyxxList, 1).get(0);  // 抽取 三室一中心 人员信息
                }
            }

            // 锁定 班站 人员信息（所有人）
            List<String> dwmcList = currentRyxxList.stream().map(ZqczRyxxVo::getDwmc).collect(Collectors.toList());
            List<String> rymcList = currentRyxxList.stream().map(ZqczRyxxVo::getRymc).distinct().collect(Collectors.toList());

            // 拼接数据单位名称、人员名称
            if (lockldbzRyxxVo != null) {
                dwmcList.add(lockldbzRyxxVo.getDwmc());
                rymcList.add(lockldbzRyxxVo.getRymc());
            }
            if (lockSpotCheckSsyzxRyxx != null) {
                dwmcList.add(lockSpotCheckSsyzxRyxx.getDwmc());
                rymcList.add(lockSpotCheckSsyzxRyxx.getRymc());
            }
            String dwmc = dwmcList.stream().distinct().collect(Collectors.joining(","));
            String rymc = rymcList.stream().distinct().collect(Collectors.joining(","));


            ZqczXmRyLsjl spotCheckXmRyLsjl = new ZqczXmRyLsjl(zsrwid, jcrwid, jcdwid, spotCheckTdyjXmgl, dwmc, rymc);
            dataList.add(spotCheckXmRyLsjl);
        }
        return dataList;
    }
}
