package digital.laboratory.platform.entrustment.service.impl;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.date.LocalDateTimeUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.fastjson.JSONArray;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.CollectionUtils;
import com.baomidou.mybatisplus.core.toolkit.StringUtils;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.baomidou.mybatisplus.extension.conditions.query.LambdaQueryChainWrapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import digital.laboratory.platform.common.core.exception.CheckedException;
import digital.laboratory.platform.common.core.util.R;
import digital.laboratory.platform.common.mybatis.security.service.DLPUser;
import digital.laboratory.platform.common.oss.service.OssFile;
import digital.laboratory.platform.entrustment.convert.DrugLiteConvert;
import digital.laboratory.platform.entrustment.convert.EntrustMaterialCheckoutResultConvert;
import digital.laboratory.platform.entrustment.dto.CheckoutResultExcelDTO;
import digital.laboratory.platform.entrustment.dto.EntrustMaterialCheckoutResultDTO;
import digital.laboratory.platform.entrustment.dto.GenerateQuarterlyReportDTO;
import digital.laboratory.platform.entrustment.dto.ResultExcelDTO;
import digital.laboratory.platform.entrustment.entity.*;
import digital.laboratory.platform.entrustment.enums.EntrustStatusConstants;
import digital.laboratory.platform.entrustment.mapper.EntrustMaterialCheckoutResultMapper;
import digital.laboratory.platform.entrustment.mapper.EntrustmentIdentificationMaterialMapper;
import digital.laboratory.platform.entrustment.query.EntrustMaterialCheckoutResultQuery;
import digital.laboratory.platform.entrustment.service.*;
import digital.laboratory.platform.entrustment.vo.DetectionRateVO;
import digital.laboratory.platform.entrustment.vo.EntrustMaterialCheckoutResultVO;
import digital.laboratory.platform.entrustment.vo.EntrustmentIdentificationMaterialVO;
import digital.laboratory.platform.entrustment.vo.SuspectDetectionVO;
import digital.laboratory.platform.sys.entity.Area;
import digital.laboratory.platform.sys.entity.DrugLite;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.beans.BeanUtils;
import org.springframework.stereotype.Service;
import springfox.documentation.spring.web.json.Json;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.BufferedOutputStream;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.net.URLEncoder;
import java.sql.*;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.time.Month;
import java.time.format.DateTimeFormatter;
import java.time.temporal.TemporalAdjusters;
import java.util.*;
import java.util.function.Function;
import java.util.stream.Collectors;

/**
 * @author ChenJiangBao
 * @description 针对表【b_entrust_material_checkout_result(委托检材--检出定性定量结果信息)】的数据库操作Service实现
 * @createDate 2024-12-30 16:16:25
 */
@Service
public class EntrustMaterialCheckoutResultServiceImpl extends ServiceImpl<EntrustMaterialCheckoutResultMapper, EntrustMaterialCheckoutResult>
        implements EntrustMaterialCheckoutResultService {

    @Resource
    private EntrustmentService entrustmentService;

    @Resource
    private EntrustmentIdentificationMaterialService entrustmentIdentificationMaterialService;

    @Resource
    private CommonFeignService commonFeignService;

    @Resource
    private EntrustmentIdentificationMaterialMapper entrustmentIdentificationMaterialMapper;

    @Resource
    private SuspectService suspectService;

    @Resource
    private CaseEventService caseEventService;

    @Resource
    private OssFile ossFile;

    /**
     * 构建委托材料检验结果字符串
     *
     * @param entrustId 委托ID
     * @return 委托材料检验结果字符串
     */
    @Override
    public String buildEntrustMaterialCheckoutResultStr(String entrustId) {
        // 先获取检材列表
//        List<EntrustmentIdentificationMaterial> materialList = entrustmentIdentificationMaterialService.list(Wrappers.<EntrustmentIdentificationMaterial>lambdaQuery().eq(EntrustmentIdentificationMaterial::getEntrustmentId, entrustId));
//        Map<String, EntrustmentIdentificationMaterial> materialMap = materialList.stream().collect(Collectors.toMap(EntrustmentIdentificationMaterial::getId, Function.identity()));
        // 获取检测结果
        List<EntrustMaterialCheckoutResultVO> checkoutResultList = baseMapper.voList(Wrappers.<EntrustMaterialCheckoutResult>query().eq("emr.entrust_id", entrustId));
        if (CollUtil.isEmpty(checkoutResultList)) {
            return "";
        }

        List<EntrustMaterialCheckoutResultVO> materialCheckoutResults = checkoutResultList.stream().filter(o -> StrUtil.isNotBlank(o.getQualitativeResult())).collect(Collectors.toList());
        List<EntrustMaterialCheckoutResultVO> materialNotCheckoutResults = checkoutResultList.stream().filter(o -> StrUtil.isBlank(o.getQualitativeResult())).collect(Collectors.toList());

        List<String> resultStrList = new ArrayList<>();
        // 对检测结果进行分组
        Map<String, List<EntrustMaterialCheckoutResultVO>> checkoutResultGroupMap = materialCheckoutResults.stream().collect(Collectors.groupingBy(item -> DrugLiteConvert.joiningDrugListNameToStr(DrugLiteConvert.getDrugLites(item.getQualitativeResult()), "、")));

        checkoutResultGroupMap.forEach((key, value) -> {
            StringBuilder builder = new StringBuilder();
            builder
                    .append(value.stream().map(item -> item.getOrderNo() + "号检材").sorted().collect(Collectors.joining("、")))
                    .append(" : [")
                    .append(key)
                    .append("]");
            resultStrList.add(builder.toString());
        });
        List<String> sortedResult = resultStrList.stream().sorted().collect(Collectors.toList());
        if (CollUtil.isNotEmpty(materialNotCheckoutResults)) {
            String notCheckoutStr = materialNotCheckoutResults.stream().map(result -> result.getOrderNo() + "号检材").sorted().collect(Collectors.joining("、")) + materialNotCheckoutResults.get(0).getCheckoutRemark();
            sortedResult.add(notCheckoutStr);
        }
        return sortedResult.stream().collect(Collectors.joining("\n"));
    }

    /**
     * 根据DTO保存检材得检出结果信息
     *
     * @param dto 检出DTO
     * @return
     */
    @Override
    public boolean save(EntrustMaterialCheckoutResultDTO dto) {
        Entrustment entrustment = entrustmentService.getById(dto.getEntrustId());
        if (entrustment == null) {
            throw new CheckedException("委托信息不存在！");
        }
        if (!entrustment.getStatus().equals(EntrustStatusConstants.ENTRUST_STATUS_ACCEPTED.getStatus())) {
            throw new CheckedException("该委托信息并未被机构受理！");
        }
        List<String> ids = dto.getIds();
        List<EntrustmentIdentificationMaterial> materialList = entrustmentIdentificationMaterialService.list(Wrappers.<EntrustmentIdentificationMaterial>lambdaQuery().in(EntrustmentIdentificationMaterial::getId, ids));
        Map<String, EntrustmentIdentificationMaterial> identificationMaterialMap = materialList.stream().collect(Collectors.toMap(EntrustmentIdentificationMaterial::getId, Function.identity()));
        boolean success = false;
        for (String id : ids) {
            EntrustmentIdentificationMaterial identificationMaterial = identificationMaterialMap.get(id);
            if (identificationMaterial == null) {
                throw new CheckedException(String.format("id为 [%s] 得检材信息不存在！", id));
            }
            EntrustMaterialCheckoutResult entrustMaterialCheckoutResult = EntrustMaterialCheckoutResultConvert.dtoToEntity(dto);
            entrustMaterialCheckoutResult.setId(id);
            entrustMaterialCheckoutResult.setEntrustId(identificationMaterial.getEntrustmentId());
            if (super.count(Wrappers.<EntrustMaterialCheckoutResult>lambdaQuery().eq(EntrustMaterialCheckoutResult::getId, id)) <= 0) {
//            throw new CheckedException(String.format("检材编号 [%s] 的检材检出结果已经存在！", identificationMaterial.getImNo()));
                success = super.save(entrustMaterialCheckoutResult);
            } else {
                success = super.updateById(entrustMaterialCheckoutResult);
            }
        }
        return success;
    }

    /**
     * 根据委托id导出检出结果excel文件
     *
     * @param entrustIds 委托id列表，包含需要导出的委托的id
     * @param response   HttpServletResponse对象，用于将生成的excel文件写入响应中
     */
    @Override
    public void exportExcel(List<String> entrustIds, HttpServletResponse response) throws IOException {
        // 获取委托信息
        List<Entrustment> entrustmentList = entrustmentService.list(
                Wrappers.<Entrustment>lambdaQuery().in(Entrustment::getId, entrustIds)
        );
        // 获取检材信息， 排序的原因是需要数据是有序的
        List<EntrustmentIdentificationMaterial> materialList = entrustmentIdentificationMaterialService.list(
                Wrappers.<EntrustmentIdentificationMaterial>lambdaQuery()
                        .in(EntrustmentIdentificationMaterial::getEntrustmentId, entrustIds)
                        .orderByAsc(EntrustmentIdentificationMaterial::getEntrustmentId)
                        .orderByAsc(EntrustmentIdentificationMaterial::getOrderNo)
        );
        // 获取检材id,后续根据检材id获取对应的检出结果
        List<String> materialIds = materialList.stream().map(EntrustmentIdentificationMaterial::getId).collect(Collectors.toList());
        // 根据委托id进行分组，防止导出的excel数据被打乱
        Map<String, List<EntrustmentIdentificationMaterial>> materialGroupByEntrustMap = materialList.stream().collect(Collectors.groupingBy(EntrustmentIdentificationMaterial::getEntrustmentId));

        // 获取检出结果
        List<EntrustMaterialCheckoutResult> checkoutResultList = super.list(Wrappers.<EntrustMaterialCheckoutResult>lambdaQuery().in(EntrustMaterialCheckoutResult::getId, materialIds));
        Map<String, EntrustMaterialCheckoutResult> checkoutResultMap = checkoutResultList.stream().collect(Collectors.toMap(EntrustMaterialCheckoutResult::getId, Function.identity()));
        List<CheckoutResultExcelDTO> checkoutResultExcelDTOS = fetchCheckoutResultExcelDTOList(entrustmentList, materialGroupByEntrustMap, checkoutResultMap);

        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        String fileName = URLEncoder.encode("检材检出结果excel表", "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");

        // 不使用默认样式
        // 空的头部样式
//        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
//        headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
//
//        // 空的内容样式
//        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
//
//        // 不使用任何样式
//        HorizontalCellStyleStrategy horizontalCellStyleStrategy =
//                new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
//
//        FastExcel.write(response.getOutputStream(), CheckoutResultExcelDTO.class)
//                .sheet(LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd")))
//                .registerWriteHandler(horizontalCellStyleStrategy)
//                .doWrite(checkoutResultExcelDTOS);
        // 使用poi导出
        buildExcel(response, checkoutResultExcelDTOS);
    }

    /**
     * vo 分页对象
     *
     * @param query
     * @return
     */
    @Override
    public IPage<EntrustMaterialCheckoutResultVO> voiPage(EntrustMaterialCheckoutResultQuery query) {
//        IPage<EntrustMaterialCheckoutResult> page = this.page(
//                new Page<>(query.getCurrent(), query.getSize()),
//                Wrappers.<EntrustMaterialCheckoutResult>lambdaQuery()
//                        .eq(StrUtil.isNotBlank(query.getEntrustId()), EntrustMaterialCheckoutResult::getEntrustId, query.getEntrustId())
//                        .orderByDesc(EntrustMaterialCheckoutResult::getUpdateTime)
//        );
//        IPage<EntrustMaterialCheckoutResultVO> voPage = new Page<>();
//        BeanUtils.copyProperties(page, voPage, "records");
//        List<EntrustMaterialCheckoutResult> records = page.getRecords();
//        if (CollUtil.isNotEmpty(records)) {
//            List<EntrustmentIdentificationMaterial> entrustmentIdentificationMaterials = entrustmentIdentificationMaterialService.list(Wrappers.<EntrustmentIdentificationMaterial>lambdaQuery().in(EntrustmentIdentificationMaterial::getId, records.stream().map(EntrustMaterialCheckoutResult::getId).collect(Collectors.toSet())));
//            Map<String, EntrustmentIdentificationMaterial> materialMap = entrustmentIdentificationMaterials.stream().collect(Collectors.toMap(EntrustmentIdentificationMaterial::getId, Function.identity()));
//            List<EntrustMaterialCheckoutResultVO> entrustMaterialCheckoutResultVOS = records.stream().map(record -> {
//                EntrustMaterialCheckoutResultVO entrustMaterialCheckoutResultVO = EntrustMaterialCheckoutResultConvert.entityToVO(record);
//                entrustMaterialCheckoutResultVO.setName(materialMap.get(record.getId()).getName());
//                return entrustMaterialCheckoutResultVO;
//            }).collect(Collectors.toList());
//            voPage.setRecords(entrustMaterialCheckoutResultVOS);
//        }
        return baseMapper.getEntrustMaterialCheckoutResultVOPage(
                new Page<>(query.getCurrent(), query.getSize()),
                Wrappers.<EntrustMaterialCheckoutResultVO>query()
                        .eq(StrUtil.isNotBlank(query.getEntrustId()), "emr.entrust_id", query.getEntrustId())
                        .orderByAsc("em.order_no")
        );
    }

    /**
     * 构建并输出Excel文件。
     *
     * @param response                HttpServletResponse对象，用于输出Excel文件
     * @param checkoutResultExcelDTOS 需要写入Excel的数据列表
     * @throws IOException 如果在写入Excel文件时发生IO异常
     *                     <p>
     *                     此方法用于将给定的checkoutResultExcelDTOS列表数据写入Excel文件，并输出到HttpServletResponse对象中。
     *                     方法首先创建一个新的XSSFWorkbook对象作为Excel工作簿，并为其创建一个名为当前日期时间的工作表。
     *                     接着，根据buildExcelHead方法返回的表头信息构建Excel的表头行。
     *                     然后，遍历checkoutResultExcelDTOS列表，将每个元素的数据写入Excel的对应行中。
     *                     最后，使用response.getOutputStream()获取输出流，将构建好的Excel工作簿写入该输出流中，实现文件的下载。
     */
    private void buildExcel(HttpServletResponse response, List<CheckoutResultExcelDTO> checkoutResultExcelDTOS) throws IOException {
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet(LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd")));
        // 表头导入
        List<String> head = buildExcelHead();
        // 算上表头的行数
        int rowCount = checkoutResultExcelDTOS.size() + 1;
        for (int i = 0; i < rowCount; i++) {
            if (i == 0) {
                Row row = sheet.createRow(i);
                for (int j = 0; j < head.size(); j++) {
                    row.createCell(j).setCellValue(head.get(j));
                }
            } else {
                // 开始写入具体数据内容
                Row row = sheet.createRow(i);
                CheckoutResultExcelDTO excelDTO = checkoutResultExcelDTOS.get(i - 1);
                row.createCell(0).setCellValue(excelDTO.getOrder());
                row.createCell(1).setCellValue(excelDTO.getDeliverTime());
                row.createCell(2).setCellValue(excelDTO.getClientOrgName());
                row.createCell(3).setCellValue(excelDTO.getProvinceCollectPlace());
                row.createCell(4).setCellValue(excelDTO.getCityCollectPlace());
                row.createCell(5).setCellValue(excelDTO.getAcceptNo());
                row.createCell(6).setCellValue(excelDTO.getOrderNo());
                row.createCell(7).setCellValue(excelDTO.getTypeName());
                row.createCell(8).setCellValue(excelDTO.getColor());
                row.createCell(9).setCellValue(excelDTO.getFormName());
                row.createCell(10).setCellValue(excelDTO.getQualitativeResult());
                row.createCell(11).setCellValue(excelDTO.getQuantitativeResult());
                row.createCell(12).setCellValue(excelDTO.getOtherResult());
                row.createCell(13).setCellValue(excelDTO.getRemark());
            }
        }
        // 输出到流
        workbook.write(response.getOutputStream());
    }

    private List<String> buildExcelHead() {
        List<String> headList = new ArrayList<>();
        headList.add("序号");
        headList.add("送检日期");
        headList.add("送检单位");
        headList.add("检材采集地");
        headList.add("检材采集地");
        headList.add("受理编号");
        headList.add("检材编号");
        headList.add("检材类型");
        headList.add("检材颜色");

        headList.add("检材形态");
        headList.add("定性结果");
        headList.add("定量结果");
        headList.add("其他鉴定结果");
        headList.add("备注");
        return headList;
    }

    /**
     * 根据委托列表、材料映射和检验结果映射生成导出Excel的DTO列表
     *
     * @param entrustList       委托列表
     * @param materialMap       材料映射，键为委托ID，值为对应的材料列表
     * @param checkoutResultMap 检验结果映射，键为委托ID，值为对应的检验结果
     * @return 导出Excel的DTO列表，但此方法实际返回空列表，因为最后返回的是Collections.emptyList()
     */
    private List<CheckoutResultExcelDTO> fetchCheckoutResultExcelDTOList(List<Entrustment> entrustList, Map<String, List<EntrustmentIdentificationMaterial>> materialMap, Map<String, EntrustMaterialCheckoutResult> checkoutResultMap) {
        List<CheckoutResultExcelDTO> checkoutResultExcelDTOList = new ArrayList<>();
        Integer index = 1;
        for (Entrustment entrust : entrustList) {
            List<EntrustmentIdentificationMaterial> materialList = materialMap.get(entrust.getId());
            List<Area> areas = commonFeignService.remoteGetProvinceCityInfo(entrust.getClientOrgId());
            String provinceCollectPlace = areas.get(0).getName();
            String cityCollectPlace = "";
            if (areas.size() > 1) {
                cityCollectPlace = areas.get(1).getName();
            }
            String typeName = getTypeName(entrust);
            if (CollUtil.isNotEmpty(materialList)) {
                for (EntrustmentIdentificationMaterial material : materialList) {
                    CheckoutResultExcelDTO excelDTO = new CheckoutResultExcelDTO();
                    excelDTO.setOrder(index++);
                    excelDTO.setDeliverTime(entrust.getDeliverTime().format(DateTimeFormatter.ofPattern("yyyy/M/d")));
                    excelDTO.setClientOrgName(entrust.getClientOrgName());
                    excelDTO.setProvinceCollectPlace(provinceCollectPlace);
                    excelDTO.setCityCollectPlace(cityCollectPlace);
                    excelDTO.setAcceptNo(entrust.getAcceptNo());
                    excelDTO.setOrderNo(material.getOrderNo());
                    excelDTO.setTypeName(typeName);
                    excelDTO.setColor(material.getColor());
                    excelDTO.setFormName(material.getFormName());
                    EntrustMaterialCheckoutResult entrustMaterialCheckoutResult = checkoutResultMap.get(material.getId());
                    excelDTO.setQualitativeResult(
                            DrugLiteConvert.getDrugLites(entrustMaterialCheckoutResult.getQualitativeResult())
                                    .stream().map(DrugLite::getName).collect(Collectors.joining("、"))
                    );
                    excelDTO.setQuantitativeResult(entrustMaterialCheckoutResult.getQuantitativeResult());
                    excelDTO.setOtherResult(entrustMaterialCheckoutResult.getOtherResult());
                    excelDTO.setRemark(entrustMaterialCheckoutResult.getCheckoutRemark());
                    checkoutResultExcelDTOList.add(excelDTO);
                }
            }
        }
        return checkoutResultExcelDTOList;
    }

    /**
     * 根据委托类型获取委托检材类型的名称
     *
     * @param entrust 委托对象
     * @return 委托类型的名称
     */
    private String getTypeName(Entrustment entrust) {
        String typeName = "";
        if (entrust.getEntrustmentType().equals(0)) {
            typeName = "缴获物";
        } else if (entrust.getEntrustmentType().equals(1)) {
            typeName = "生物样本";
        } else {
            typeName = "其他";
        }
        return typeName;
    }

    /**
     * 获取指定年份的所有毒品类型列表（去重并排序）
     * <p>
     * 该方法通过查询指定年份内所有非空且不为空的定性结果，提取其中的毒品类型信息，
     * 并返回一个去重且按字典序排序的毒品类型列表。
     *
     * @param year  年份，若为 null 则默认使用当前年份
     * @param orgId
     * @return 包含所有毒品类型的列表，列表中的毒品类型名称已去重并排序
     * @see EntrustMaterialCheckoutResult 委托材料检验结果实体类，包含定性结果字段
     * @see DrugLite 药品轻量级实体类，包含药品名称字段
     * @see DrugLiteConvert 工具类，用于将定性结果转换为毒品轻量级实体列表
     */
    @Override
    public List<String> getResultType(Integer year, String orgId) {
        // 根据年份构建查询起始时间
        LocalDateTime startTime = getStartTime(year);
        List<String> entrustIdList = getEntrustIdListByOrgId(orgId);

        // 查询所有非空且不为空的定性结果，且创建时间大于等于起始时间
        List<EntrustMaterialCheckoutResult> list = this.list(Wrappers.<EntrustMaterialCheckoutResult>lambdaQuery()
                .in(CollUtil.isNotEmpty(entrustIdList), EntrustMaterialCheckoutResult::getEntrustId, entrustIdList)
                .ne(EntrustMaterialCheckoutResult::getQualitativeResult, "") // 排除空字符串
                .isNotNull(EntrustMaterialCheckoutResult::getQualitativeResult) // 排除空值
                .ge(EntrustMaterialCheckoutResult::getCreateTime, startTime)); // 创建时间大于等于起始时间

        // 用于存储去重后的毒品类型列表
        ArrayList<String> typeList = new ArrayList<>();

        // 遍历查询结果
        for (EntrustMaterialCheckoutResult result : list) {
            // 将定性结果转换为轻量级实体列表
            List<DrugLite> drugLites = DrugLiteConvert.getDrugLites(result.getQualitativeResult());

            // 遍历药品轻量级实体列表
            for (DrugLite drugLite : drugLites) {
                // 如果毒品类型未添加到列表中，则添加
                if (!typeList.contains(drugLite.getName())) {
                    typeList.add(drugLite.getName());
                }
            }
        }

        // 对列表进行字典序排序
        Collections.sort(typeList);

        // 返回去重并排序后的毒品类型列表
        return typeList;
    }

    // 获取开始时间
    private LocalDateTime getStartTime(Integer year) {
        LocalDateTime startTime = null;
        if (year != null) {
            // 如果年份不为空，使用指定年份的1月1日 00:00:00
            startTime = LocalDateTime.of(year, 1, 1, 0, 0);
        } else {
            // 如果年份为空，默认使用当前年份的1月1日 00:00:00
            startTime = LocalDateTime.of(LocalDateTime.now().getYear(), 1, 1, 0, 0);
        }
        return startTime;
    }

    // 根据机构id获取委托id
    private List<String> getEntrustIdListByOrgId(String orgId) {
        if (StrUtil.isBlank(orgId)) {
            return Collections.emptyList();
        }
        List<Entrustment> entrustmentList = entrustmentService.list(Wrappers.<Entrustment>lambdaQuery().eq(Entrustment::getClientOrgId, orgId));
        return entrustmentList.stream().map(Entrustment::getId).collect(Collectors.toList());
    }


    @Override
    public DetectionRateVO getDetectionRateByMaterial(Integer year, String orgId) {
        // 获取所有毒品类型列表
        List<String> resultType = this.getResultType(year, orgId);
        // 用于存储毒品名称及其检出占比的映射表
        DetectionRateVO vo = new DetectionRateVO();
        ArrayList<String> nameList = new ArrayList<>();
        ArrayList<String> percentList = new ArrayList<>();
        // 根据年份构建查询起始时间
        LocalDateTime startTime = getStartTime(year);
        List<String> entrustIdList = getEntrustIdListByOrgId(orgId);


        // 查询所有非空且不为空的定性结果，且创建时间大于等于起始时间
        List<EntrustmentIdentificationMaterial> materialList = entrustmentIdentificationMaterialService.list(Wrappers.<EntrustmentIdentificationMaterial>lambdaQuery()
                .in(CollUtil.isNotEmpty(entrustIdList), EntrustmentIdentificationMaterial::getEntrustmentId, entrustIdList)
                .ge(EntrustmentIdentificationMaterial::getAcceptTime, startTime)
                .eq(EntrustmentIdentificationMaterial::getAcceptPassed, 1));


        // 如果查询结果为空，直接返回 null
        if (materialList == null || materialList.size() == 0) {
            return null;
        }

        // 获取分母（总结果数）
        double denominator = materialList.size();

        // 遍历每种毒品类型，计算其检出占比
        for (String drugName : resultType) {
            // 查询包含当前毒品名称的定性结果
            List<EntrustMaterialCheckoutResult> list = this.list(Wrappers.<EntrustMaterialCheckoutResult>lambdaQuery()
                    .like(EntrustMaterialCheckoutResult::getQualitativeResult, drugName));

            // 如果查询结果不为空，计算检出占比并存入映射表
            if (list != null && list.size() > 0) {
                double molecule = list.size(); // 分子
                BigDecimal d1 = new BigDecimal(molecule);
                BigDecimal d2 = new BigDecimal(denominator); // 分母
                // 计算百分比（保留2位小数，四舍五入）
                double percentage = d1.divide(d2, 4, BigDecimal.ROUND_HALF_UP)
                        .multiply(new BigDecimal(100))
                        .doubleValue();
                nameList.add(drugName);
                percentList.add(String.format("%.2f%%", percentage));
            }
        }
        vo.setNameList(nameList);
        vo.setPercentList(percentList);
        // 返回包含毒品名称及其检出占比的映射表
        return vo;
    }

    @Override
    public IPage<EntrustmentIdentificationMaterialVO> getMaterialByDetection(String drugName, Integer year, Page page) {

        List<EntrustMaterialCheckoutResult> list = this.list(Wrappers.<EntrustMaterialCheckoutResult>lambdaQuery()
                .like(EntrustMaterialCheckoutResult::getQualitativeResult, drugName));

        ArrayList<String> materialIdList = new ArrayList<>();

        for (EntrustMaterialCheckoutResult result : list) {
            materialIdList.add(result.getId());
        }

        LocalDateTime startTime = this.getStartTime(year);

        Page materialPage = entrustmentIdentificationMaterialService.page(page, Wrappers.<EntrustmentIdentificationMaterial>lambdaQuery()
                .in(EntrustmentIdentificationMaterial::getId, materialIdList)
                .ge(EntrustmentIdentificationMaterial::getAcceptTime, startTime)
                .orderByDesc(EntrustmentIdentificationMaterial::getAcceptTime));
        return materialPage;
    }

    public List<EntrustmentIdentificationMaterialVO> getResultData(ResultExcelDTO excelDTO) {

        LocalDateTime startTime = excelDTO.getStartTime();
        LocalDateTime endTime = excelDTO.getEndTime();
        List<String> oldResults = excelDTO.getOldResult();
        Integer entrustType = excelDTO.getEntrustType();

        // 创建查询条件包装器
        LambdaQueryWrapper<Entrustment> wrapper = new LambdaQueryWrapper<>();

        // 设置时间范围过滤条件
        if (startTime != null && endTime != null) {
            wrapper.between(Entrustment::getAcceptTime, startTime, endTime);

        } else {
            wrapper.ge(Entrustment::getAcceptTime, this.getStartTime(null));
        }
        List<String> allResults = new ArrayList<>();
        for (String oldResult : oldResults) {
            if (StringUtils.isNotBlank(oldResult)) {
                if ("委托".equals(oldResult)) {
                    allResults.add("首次鉴定");
                    allResults.add("补充鉴定");
                    allResults.add("重新鉴定");
                } else {
                    allResults.add(oldResult);
                }
            }
        }

        if (!allResults.isEmpty()) {
            wrapper.in(Entrustment::getOldIdentificationResult, allResults);
        }

        // 查询符合条件的 Entrustment 列表，避免重复查询数据库
        List<Entrustment> entrustmentList = entrustmentService.list(
                wrapper.eq(entrustType != null, Entrustment::getEntrustmentType, entrustType)
        );

        System.out.println("委托的数量:" + entrustmentList.size());
        // 如果无符合条件的委托信息，则返回空列表
        if (CollUtil.isEmpty(entrustmentList)) {
            return Collections.emptyList();
        }

        // 获取符合条件的委托 ID 集合，提高查询效率
        Set<String> entrustIds = entrustmentList.stream()
                .map(Entrustment::getId)
                .collect(Collectors.toSet());

        // 将 Entrustment 列表转换为 Map，提高后续查询速度
        Map<String, Entrustment> entrustMap = entrustmentList.stream()
                .collect(Collectors.toMap(Entrustment::getId, Function.identity()));

        // 查询所有符合条件的 EntrustmentIdentificationMaterial 数据
        List<EntrustmentIdentificationMaterial> materialList = entrustmentIdentificationMaterialService.list(
                Wrappers.<EntrustmentIdentificationMaterial>lambdaQuery()
                        .in(EntrustmentIdentificationMaterial::getEntrustmentId, entrustIds)
        );

        // 若无符合条件的材料，则直接返回空列表
        if (CollUtil.isEmpty(materialList)) {
            return Collections.emptyList();
        }

        // 获取材料 ID 集合，提高查询效率
        Set<String> materialIds = materialList.stream()
                .map(EntrustmentIdentificationMaterial::getId)
                .collect(Collectors.toSet());

        // 查询所有符合条件的检测结果数据
        List<EntrustMaterialCheckoutResult> resultList = this.list(
                Wrappers.<EntrustMaterialCheckoutResult>lambdaQuery()
                        .in(EntrustMaterialCheckoutResult::getId, materialIds)
        );

        Boolean isMetabolite = excelDTO.getIsMetabolite();

        if (isMetabolite) {
            resultList.forEach(result -> {
                if (StringUtils.isNotBlank(result.getMetaboliteResult())) {
                    List<DrugLite> drugLites = DrugLiteConvert.getDrugLites(result.getMetaboliteResult());
                    List<DrugLite> drugLites1 = DrugLiteConvert.getDrugLites(result.getQualitativeResult());
                    drugLites.addAll(drugLites1);
                    result.setQualitativeResult(JSONArray.toJSONString(drugLites));
                }
            });
        }
        // 将检测结果转换为 Map
        Map<String, EntrustMaterialCheckoutResult> resultMap = resultList.stream()
                .collect(Collectors.toMap(EntrustMaterialCheckoutResult::getId, Function.identity()));

        // 构造返回的 VO 列表
        List<EntrustmentIdentificationMaterialVO> voList = materialList.stream().map(material -> {
            EntrustmentIdentificationMaterialVO vo = new EntrustmentIdentificationMaterialVO();
            BeanUtils.copyProperties(material, vo);

            // 设置检测结果
            vo.setDrugLites(Optional.ofNullable(resultMap.get(material.getId()))
                    .map(EntrustMaterialCheckoutResult::getQualitativeResult)
                    .map(DrugLiteConvert::getDrugLites)
                    .orElse(null));

            // 设置委托信息
            Entrustment entrust = entrustMap.get(material.getEntrustmentId());
            if (entrust != null) {
                String oldIdentificationResult = entrust.getOldIdentificationResult();
                vo.setOldIdentificationResult(
                        ("首次鉴定".equals(oldIdentificationResult) || "补充鉴定".equals(oldIdentificationResult) || "重新鉴定".equals(oldIdentificationResult))
                                ? "委托"
                                : oldIdentificationResult
                );
                vo.setOrgName(entrust.getClientOrgName());
            }
            return vo;
        }).collect(Collectors.toList());

        // 按 acceptNo 进行排序并返回结果
        return entrustmentIdentificationMaterialService.sortVoByAcceptNo(voList);
    }


    public Map<String, List<EntrustmentIdentificationMaterialVO>> getResultDataMap(
            ResultExcelDTO excelDTO) {
        LocalDateTime startTime = excelDTO.getStartTime();
        LocalDateTime endTime = excelDTO.getEndTime();
        List<String> oldResults = excelDTO.getOldResult();
        Integer entrustType = excelDTO.getEntrustType();
        boolean isMetabolite = excelDTO.getIsMetabolite();
        try {
            // 1. 构建查询条件
            LambdaQueryWrapper<Entrustment> qw = new LambdaQueryWrapper<>();

            // 时间过滤条件
            if (startTime != null && endTime != null) {
                qw.ge(Entrustment::getAcceptTime, startTime).le(Entrustment::getAcceptTime, endTime);
            } else {
                qw.ge(Entrustment::getAcceptTime, this.getStartTime(null));
            }

            List<String> allResults = new ArrayList<>();
            for (String oldResult : oldResults) {
                if (StringUtils.isNotBlank(oldResult)) {
                    if ("委托".equals(oldResult)) {
                        allResults.add("首次鉴定");
                        allResults.add("补充鉴定");
                        allResults.add("重新鉴定");
                    } else {
                        allResults.add(oldResult);
                    }
                }
            }

            if (!allResults.isEmpty()) {
                qw.in(Entrustment::getOldIdentificationResult, allResults);
            }

            // 2. 查询委托数据
            List<Entrustment> entrusts = entrustmentService.list(qw
                    .eq(Entrustment::getEntrustmentType, entrustType)
                    .ge(Entrustment::getStatus, EntrustStatusConstants.ENTRUST_STATUS_ACCEPTED.getStatus()));

            if (entrusts.isEmpty()) {
                return Collections.emptyMap();
            }

            // 将 Entrustment 转换为 Map，以 ID 为 Key，减少后续查询数据库的次数
            Map<String, Entrustment> entrustMap = entrusts.stream()
                    .collect(Collectors.toMap(Entrustment::getId, Function.identity()));

            Set<String> entrustIdList = entrustMap.keySet();

            // 3. 查询符合条件的检验结果
            List<EntrustMaterialCheckoutResult> newResultList = this.list(Wrappers.<EntrustMaterialCheckoutResult>lambdaQuery()
                    .in(EntrustMaterialCheckoutResult::getEntrustId, entrustIdList)
                    .isNotNull(EntrustMaterialCheckoutResult::getQualitativeResult)
                    .ne(EntrustMaterialCheckoutResult::getQualitativeResult, ""));

            if (newResultList.isEmpty()) {
                return Collections.emptyMap();
            }

            // 4. 提取所有涉及的毒品名称（去重）
            Set<String> drugNames = new HashSet<>();
            for (EntrustMaterialCheckoutResult result : newResultList) {
                List<DrugLite> drugLites = DrugLiteConvert.getDrugLites(result.getQualitativeResult());
                for (DrugLite drugLite : drugLites) {
                    drugNames.add(drugLite.getName());
                }
                if (isMetabolite && StringUtils.isNotBlank(result.getMetaboliteResult())) {
                    List<DrugLite> drugLites1 = DrugLiteConvert.getDrugLites(result.getMetaboliteResult());
                    for (DrugLite drugLite : drugLites1) {
                        if (!drugNames.contains(drugLite.getName())) {
                            drugNames.add(drugLite.getName());
                        }
                    }
                }
            }

            // 5. 处理不同毒品名称的数据
            Map<String, List<EntrustmentIdentificationMaterialVO>> resultMap = new HashMap<>();

            for (String drugName : drugNames) {
                // 5.1 查询所有含有该毒品的检验结果
                List<EntrustMaterialCheckoutResult> resultList = this.list(Wrappers.<EntrustMaterialCheckoutResult>lambdaQuery()
                        .and(queryWrapper -> queryWrapper
                                .like(EntrustMaterialCheckoutResult::getMetaboliteResult, drugName)
                                .or()
                                .like(EntrustMaterialCheckoutResult::getQualitativeResult, drugName))
                        .in(EntrustMaterialCheckoutResult::getEntrustId, entrustIdList));

                if (resultList.isEmpty()) {
                    continue;
                }

                if (isMetabolite) {
                    resultList.forEach(item -> {
                        if (StringUtils.isNotBlank(item.getMetaboliteResult())) {
                            List<DrugLite> drugLites = DrugLiteConvert.getDrugLites(item.getMetaboliteResult());
                            List<DrugLite> drugLites1 = DrugLiteConvert.getDrugLites(item.getQualitativeResult());
                            drugLites.addAll(drugLites1);
                            item.setQualitativeResult(JSONArray.toJSONString(drugLites));
                        }
                    });
                }

                // 转换为 Map，方便后续查找
                Map<String, EntrustMaterialCheckoutResult> checkoutResultMap = resultList.stream()
                        .collect(Collectors.toMap(EntrustMaterialCheckoutResult::getId, Function.identity()));

                // 5.2 获取相关的鉴定材料
                Set<String> materialIdSet = checkoutResultMap.keySet();
                List<EntrustmentIdentificationMaterial> materialList = entrustmentIdentificationMaterialService.list(Wrappers.<EntrustmentIdentificationMaterial>lambdaQuery()
                        .in(EntrustmentIdentificationMaterial::getId, materialIdSet));

                if (materialList.isEmpty()) {
                    continue;
                }

                // 5.3 组装 VO 数据
                List<EntrustmentIdentificationMaterialVO> voList = new ArrayList<>();
                for (EntrustmentIdentificationMaterial material : materialList) {
                    Entrustment entrust = entrustMap.get(material.getEntrustmentId());
                    if (entrust == null) continue;

                    EntrustmentIdentificationMaterialVO vo = new EntrustmentIdentificationMaterialVO();
                    BeanUtils.copyProperties(material, vo);
                    vo.setOrgName(entrust.getClientOrgName());
                    String oldIdentificationResult = entrust.getOldIdentificationResult();
                    if (oldIdentificationResult.equals("首次鉴定") || oldIdentificationResult.equals("补充鉴定") || oldIdentificationResult.equals("重新鉴定")) {
                        vo.setOldIdentificationResult("委托");
                    } else {
                        vo.setOldIdentificationResult(oldIdentificationResult);
                    }
                    EntrustMaterialCheckoutResult checkoutResult = checkoutResultMap.get(vo.getId());
                    if (checkoutResult != null) {
                        vo.setDrugLites(DrugLiteConvert.getDrugLites(checkoutResult.getQualitativeResult()));
                    }

                    voList.add(vo);
                }

                // 5.4 按受理编号排序
                voList.sort(Comparator.comparing(EntrustmentIdentificationMaterialVO::getAcceptNo, (a, b) -> {
                    try {
                        String[] partsA = a.split("-");
                        String[] partsB = b.split("-");
                        if (partsA.length != 3 || partsB.length != 3) return 0;

                        int yearA = Integer.parseInt(partsA[0]), serialA = Integer.parseInt(partsA[1]), subSerialA = Integer.parseInt(partsA[2]);
                        int yearB = Integer.parseInt(partsB[0]), serialB = Integer.parseInt(partsB[1]), subSerialB = Integer.parseInt(partsB[2]);

                        return Comparator.comparingInt((int[] arr) -> arr[0])
                                .thenComparingInt(arr -> arr[1])
                                .thenComparingInt(arr -> arr[2])
                                .compare(new int[]{yearA, serialA, subSerialA}, new int[]{yearB, serialB, subSerialB});
                    } catch (Exception e) {
                        return 0; // 防止解析错误导致异常
                    }
                }));

                resultMap.put(drugName, voList);
            }

            return resultMap;

        } catch (Exception e) {
            log.error("获取结果数据失败: ", e);

            return Collections.emptyMap();
        }
    }

    @Override
    public R getExcelByResult(HttpServletResponse response, ResultExcelDTO excelDTO) throws IOException {
        // 获取查询数据
        List<EntrustmentIdentificationMaterialVO> resultData = this.getResultData(excelDTO);
        Map<String, List<EntrustmentIdentificationMaterialVO>> resultDataMap = this.getResultDataMap(excelDTO);

        LocalDateTime startTime = excelDTO.getStartTime();
        LocalDateTime endTime = excelDTO.getEndTime();
        Integer entrustType = excelDTO.getEntrustType();
        List<String> oldResults = excelDTO.getOldResult();

        if (resultData.isEmpty() || resultDataMap.isEmpty()) {
            return R.failed("没有符合条件的数据!");
        }

        Workbook workbook = new XSSFWorkbook();

        createDataSheet(workbook, entrustType, resultData);
        createStatisticsSheet(workbook, resultDataMap, oldResults, entrustType, startTime, endTime);
        createDrugSheets(workbook, resultDataMap, entrustType);

        workbook.write(response.getOutputStream());
        return R.ok("导出成功!");
    }

    private void createDataSheet(Workbook workbook, Integer entrustType, List<EntrustmentIdentificationMaterialVO> resultData) {
        // 确定表名
        String sheetName = (entrustType == 0) ? "常规毒品" : "生物样本";
        Sheet sheet = workbook.createSheet(sheetName);

        // 定义表头
        String[] inVivoHeaders = {"检材类型", "检材编号", "检材名称", "检材性状", "检材质量", "年龄", "性别", "委托单位", "受理日期", "检出结果"};
        String[] inVitroHeaders = {"检材类型", "检材编号", "检材名称", "检材性状", "检材质量", "委托单位", "受理日期", "检出结果"};
        String[] headers = (entrustType == 0) ? inVitroHeaders : inVivoHeaders;

        // 创建表头行
        Row headerRow = sheet.createRow(0);
        for (int i = 0; i < headers.length; i++) {
            headerRow.createCell(i).setCellValue(headers[i]);
            sheet.setColumnWidth(i, 20 * 256);
        }
        int masculineSize = 0;
        // 填充数据
        for (int i = 0; i < resultData.size(); i++) {
            EntrustmentIdentificationMaterialVO vo = resultData.get(i);
            Row row = sheet.createRow(i + 1);
            fillRowData(row, vo, entrustType);
            if (CollUtil.isNotEmpty(vo.getDrugLites())) {
                masculineSize++;
            }
        }
        Row lastRow = sheet.createRow(resultData.size() + 2);
        lastRow.createCell(0).setCellValue("检材总数量:" + resultData.size());
        lastRow.createCell(1).setCellValue("阳性总数量:" + masculineSize);
        BigDecimal result = new BigDecimal(masculineSize)
                .multiply(new BigDecimal(100))
                .divide(new BigDecimal(resultData.size()), 2, RoundingMode.HALF_UP);
        lastRow.createCell(2).setCellValue("总检出率:" + result.toString() + "%");
    }

    private void createStatisticsSheet(Workbook workbook, Map<String, List<EntrustmentIdentificationMaterialVO>> resultDataMap, List<String> oldResults, Integer entrustType, LocalDateTime startTime, LocalDateTime endTime) {
        Sheet sheet = workbook.createSheet("统计");

        String[] headers = {"化合物种类", "检出数量", "总数量", "检出率"};

        // 创建表头
        Row headerRow = sheet.createRow(0);
        for (int i = 0; i < headers.length; i++) {
            headerRow.createCell(i).setCellValue(headers[i]);
            sheet.setColumnWidth(i, 20 * 256);
        }

        // 获取总数量
        Integer size = this.getSizeMap(entrustType, oldResults, startTime, endTime);

        // 填充统计数据
        int rowIndex = 1;
        for (Map.Entry<String, List<EntrustmentIdentificationMaterialVO>> entry : resultDataMap.entrySet()) {
            String drugName = entry.getKey();
            List<EntrustmentIdentificationMaterialVO> materials = entry.getValue();
            Row row = sheet.createRow(rowIndex++);
            row.createCell(0).setCellValue(drugName);
            row.createCell(1).setCellValue(materials.size());
            row.createCell(2).setCellValue(size);
            row.createCell(3).setCellValue(String.format("%.2f%%", materials.size() * 100.0 / size));
        }
    }

    private void createDrugSheets(Workbook workbook, Map<String, List<EntrustmentIdentificationMaterialVO>> resultDataMap, Integer entrustType) {
        for (Map.Entry<String, List<EntrustmentIdentificationMaterialVO>> entry : resultDataMap.entrySet()) {
            String drugName = entry.getKey().replaceAll("[^\u4e00-\u9fa5a-zA-Z0-9]", "");
            Sheet sheet = workbook.createSheet(drugName);

            // 定义表头
            String[] headers = (entrustType == 0) ? new String[]{"检材类型", "检材编号", "检材名称", "检材性状", "检材质量", "委托单位", "受理日期", "检出结果"}
                    : new String[]{"检材类型", "检材编号", "检材名称", "检材性状", "检材质量", "年龄", "性别", "委托单位", "受理日期", "检出结果"};

            // 创建表头行
            Row headerRow = sheet.createRow(0);
            for (int i = 0; i < headers.length; i++) {
                headerRow.createCell(i).setCellValue(headers[i]);
                sheet.setColumnWidth(i, 20 * 256);
            }

            // 填充数据
            int rowIndex = 1;
            for (EntrustmentIdentificationMaterialVO vo : entry.getValue()) {
                Row row = sheet.createRow(rowIndex++);
                fillRowData(row, vo, entrustType);
            }
        }
    }

    private void fillRowData(Row row, EntrustmentIdentificationMaterialVO vo, Integer entrustType) {
        row.createCell(0).setCellValue(vo.getOldIdentificationResult());
        row.createCell(1).setCellValue(vo.getAcceptNo());
        row.createCell(2).setCellValue(vo.getName());
        row.createCell(3).setCellValue(vo.getFormName());
        row.createCell(4).setCellValue(vo.getQuantity() + vo.getUnit());

        if (entrustType == 1) {
            row.createCell(5).setCellValue(vo.getMaterialAge() != null || vo.getMaterialAge() != 0 ? vo.getMaterialAge().toString() + "岁" : "未知");
            row.createCell(6).setCellValue(StringUtils.isNotBlank(vo.getBiologyGender()) ? vo.getBiologyGender() : "未知");
            row.createCell(7).setCellValue(vo.getOrgName());
            row.createCell(8).setCellValue(LocalDateTimeUtil.format(vo.getAcceptTime(), "yyyy-MM-dd"));
            List<DrugLite> drugLites = vo.getDrugLites();
            if (drugLites != null && !drugLites.isEmpty()) {
                String result = drugLites.stream()
                        .map(DrugLite::getName)
                        .collect(Collectors.joining(","));  // 自动去除最后的逗号
                row.createCell(9).setCellValue(result);
            } else {
                row.createCell(9).setCellValue("未检出");
            }
        } else {
            row.createCell(5).setCellValue(vo.getOrgName());
            row.createCell(6).setCellValue(LocalDateTimeUtil.format(vo.getAcceptTime(), "yyyy-MM-dd"));
            List<DrugLite> drugLites = vo.getDrugLites();
            if (drugLites != null && !drugLites.isEmpty()) {
                String result = drugLites.stream()
                        .map(DrugLite::getName)
                        .collect(Collectors.joining(","));  // 自动去除最后的逗号
                row.createCell(7).setCellValue(result);
            } else {
                row.createCell(7).setCellValue("未检出");
            }
        }
    }


    public Integer getSizeMap(Integer entrustType, List<String> oldResults, LocalDateTime startTime, LocalDateTime endTime) {
        // 构造查询条件，查询 EntrustmentIdentificationMaterial 数据

        LocalDateTime queryStartTime = Optional.ofNullable(startTime).orElseGet(() -> this.getStartTime(null));


        // 统计各类委托的数量
        int entrustSize = 0;
        int preliminaryScreeningSize = 0;
        int myanmarRelatedSize = 0;
        int voluntaryRehabilitationSize = 0;

        // 构造查询条件，查询 Entrustment 数据
        LambdaQueryWrapper<Entrustment> wrapper = new LambdaQueryWrapper<>();
        if (endTime != null) {
            wrapper.between(Entrustment::getAcceptTime, queryStartTime, endTime);
        } else {
            wrapper.ge(Entrustment::getAcceptTime, queryStartTime);
        }

        ArrayList<String> results = new ArrayList<>();
        for (String oldResult : oldResults) {
            if (oldResult.equals("委托")) {
                results.add("首次鉴定");
                results.add("补充鉴定");
                results.add("重新鉴定");
            } else results.add(oldResult);
        }

        // 查询符合条件的 Entrustment 记录
        List<Entrustment> entrusts = entrustmentService.list(wrapper
                .eq(Entrustment::getEntrustmentType, entrustType)
                .ge(Entrustment::getStatus, EntrustStatusConstants.ENTRUST_STATUS_ACCEPTED.getStatus())
                .in(Entrustment::getOldIdentificationResult, results)
        );

        List<String> entrustIds = entrusts.stream().map(Entrustment::getId).collect(Collectors.toList());

        return entrustmentIdentificationMaterialService.lambdaQuery()
                .in(EntrustmentIdentificationMaterial::getEntrustmentId, entrustIds)
                .list()
                .size();
//
//        // 按照 oldIdentificationResult 进行分组
//        Map<String, List<Entrustment>> entrustMap = entrusts.stream()
//                .collect(Collectors.groupingBy(Entrustment::getOldIdentificationResult));
//
//        // 遍历分组数据，计算各类 EntrustmentIdentificationMaterial 的数量
//        for (Map.Entry<String, List<Entrustment>> entry : entrustMap.entrySet()) {
//            String oldIdentificationResult = entry.getKey();
//            List<Entrustment> entrustList = entry.getValue();
//
//            // 获取 Entrustment ID 列表
//            List<String> entIdList = entrustList.stream()
//                    .map(Entrustment::getId)
//                    .collect(Collectors.toList());
//
//            // 查询与这些 Entrustment 关联的 EntrustmentIdentificationMaterial 数量
//            int size = entrustmentIdentificationMaterialService.list(Wrappers.<EntrustmentIdentificationMaterial>lambdaQuery()
//                    .in(EntrustmentIdentificationMaterial::getEntrustmentId, entIdList)).size();
//
//            // 根据 oldIdentificationResult 分类累加数量
//            switch (oldIdentificationResult) {
//                case "初筛":
//                    preliminaryScreeningSize += size;
//                    break;
//                case "涉缅人员":
//                    myanmarRelatedSize += size;
//                    break;
//                case "自愿戒治人员":
//                    voluntaryRehabilitationSize += size;
//                    break;
//                default:
//                    entrustSize += size;
//                    break;
//            }
//        }
//
//        // 组装返回结果
//        Map<String, Integer> sizeMap = new HashMap<>();
//        sizeMap.put("委托", entrustSize);
//        sizeMap.put("初筛", preliminaryScreeningSize);
//        sizeMap.put("涉缅人员", myanmarRelatedSize);
//        sizeMap.put("自愿戒治人员", voluntaryRehabilitationSize);

//        return entrusts.size();
    }

    @Override
    public boolean pushSuspectDetectionResult(String entrustmentId, DLPUser user) {
        // 参数校验
        if (StringUtils.isBlank(entrustmentId)) {
            throw new IllegalArgumentException("entrustmentId不能为空");
        }

        // 获取委托信息
        Entrustment entrustment = entrustmentService.getById(entrustmentId);
        if (entrustment == null) {
            throw new IllegalArgumentException("未找到委托信息：" + entrustmentId);
        }

        // 获取案件信息
        CaseEvent caseEvent = caseEventService.getById(entrustment.getCaseId());
        if (caseEvent == null) {
            throw new IllegalArgumentException("未找到案件信息：" + entrustment.getCaseId());
        }

        // 获取检验结果
        List<EntrustMaterialCheckoutResult> results = this.lambdaQuery()
                .eq(EntrustMaterialCheckoutResult::getEntrustId, entrustmentId)
                .isNotNull(EntrustMaterialCheckoutResult::getQualitativeResult)
                .list();

        if (CollectionUtils.isEmpty(results)) {
            throw new IllegalArgumentException("没有阳性的检验结果：" + entrustmentId + ",无需进行推送!");
        }

        // 获取嫌疑人信息
        List<Suspect> suspects = suspectService.lambdaQuery()
                .eq(Suspect::getEntrustId, entrustmentId)
                .list();

        if (CollectionUtils.isEmpty(suspects)) {
            throw new IllegalArgumentException("未找到嫌疑人信息：" + entrustmentId + ",请先添加嫌疑人信息!");
        }
        // 构造物证材料映射
        Map<String, EntrustmentIdentificationMaterial> materialMap = entrustmentIdentificationMaterialService.lambdaQuery()
                .eq(EntrustmentIdentificationMaterial::getEntrustmentId, entrustmentId)
                .orderByAsc(EntrustmentIdentificationMaterial::getOrderNo)
                .list()
                .stream()
                .collect(Collectors.toMap(EntrustmentIdentificationMaterial::getId, Function.identity(), (a, b) -> a));

        // 构造检验结果VO
        List<EntrustMaterialCheckoutResultVO> resultVOS = results.stream()
                .map(r -> buildResultVO(r, materialMap))
                .sorted(Comparator.comparingInt(EntrustMaterialCheckoutResultVO::getOrderNo))
                .collect(Collectors.toList());


        // 数据推送至目标数据库
        return pushToDatabase(suspects, resultVOS, entrustment, caseEvent, user);
    }

    private EntrustMaterialCheckoutResultVO buildResultVO(EntrustMaterialCheckoutResult result, Map<String, EntrustmentIdentificationMaterial> materialMap) {
        EntrustMaterialCheckoutResultVO vo = new EntrustMaterialCheckoutResultVO();
        BeanUtils.copyProperties(result, vo);

        EntrustmentIdentificationMaterial material = materialMap.get(result.getId());
        if (material != null) {
            vo.setName(material.getName());
            vo.setOrderNo(material.getOrderNo());
            vo.setForm(material.getForm());
            vo.setDrawTime(material.getDrawTime());
            vo.setDrawPlace(material.getDrawPlace());
            vo.setAcceptNo(material.getAcceptNo());
        }
        System.out.println("~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~");
        System.out.println("结果的毒品列表：" + result.getQualitativeResult());

        vo.setQualitativeResult(
                DrugLiteConvert.getDrugLites(result.getQualitativeResult())
                        .stream().map(DrugLite::getName)
                        .collect(Collectors.joining("、"))
        );
        return vo;
    }


    private SuspectDetectionVO buildSuspectVO(Suspect suspect, CaseEvent caseEvent, Entrustment entrustment, List<EntrustMaterialCheckoutResultVO> resultVOS, DLPUser user) {
        SuspectDetectionVO vo = new SuspectDetectionVO();
        BeanUtils.copyProperties(suspect, vo);
        vo.setCaseName(caseEvent.getCaseName());
        vo.setCaseBrief(caseEvent.getCaseBrief());
        vo.setHappenTime(caseEvent.getHappenTime());
        vo.setPushTime(LocalDateTime.now());
        vo.setAcceptNo(entrustment.getAcceptNo());
        vo.setResults(resultVOS);
        return vo;
    }


    /*
    地址：83.103.24.236
    端口：43306
    数据库：znyp
    数据库账号：wsjc
    密码：wsjc9985
     */
    private boolean pushToDatabase(List<Suspect> suspects, List<EntrustMaterialCheckoutResultVO> resultVOS, Entrustment entrustment, CaseEvent caseEvent, DLPUser user) {
        String url = "jdbc:mysql://83.103.24.236:43306/znyp?characterEncoding=utf8&serverTimezone=Asia/Shanghai";
        String username = "wsjc";
        String password = "wsjc9985";

        try (Connection connection = DriverManager.getConnection(url, username, password)) {

            // 推送嫌疑人信息
            String sql1 = "INSERT INTO b_suspect (id, name, id_number, phone_number, sex, age, accept_no, order_no, case_name, case_brief, happen_time, push_time, push_user, accept_time,client_org_name) " +
                    "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) " +
                    "ON DUPLICATE KEY UPDATE name=VALUES(name), id_number=VALUES(id_number), phone_number=VALUES(phone_number), " +
                    "sex=VALUES(sex), age=VALUES(age), accept_no=VALUES(accept_no), order_no=VALUES(order_no), case_name=VALUES(case_name), " +
                    "case_brief=VALUES(case_brief), happen_time=VALUES(happen_time), push_time=VALUES(push_time), push_user=VALUES(push_user), " +
                    "accept_time=VALUES(accept_time) , client_org_name = VALUES(client_org_name)";

            try (PreparedStatement pstmt = connection.prepareStatement(sql1)) {
                for (Suspect s : suspects) {
                    pstmt.setString(1, s.getId());
                    pstmt.setString(2, s.getName());
                    pstmt.setString(3, s.getIdNumber());
                    pstmt.setString(4, s.getPhoneNumber());
                    pstmt.setString(5, s.getSex());
                    pstmt.setInt(6, s.getAge());
                    pstmt.setString(7, entrustment.getAcceptNo());
                    pstmt.setInt(8, s.getOrderNo());
                    pstmt.setString(9, caseEvent.getCaseName());
                    pstmt.setString(10, caseEvent.getCaseBrief());
                    pstmt.setDate(11, java.sql.Date.valueOf(caseEvent.getHappenTime()));
                    pstmt.setTimestamp(12, Timestamp.valueOf(LocalDateTime.now()));
                    pstmt.setString(13, user.getName());
                    System.out.println(entrustment);
                    pstmt.setDate(14, java.sql.Date.valueOf(entrustment.getAcceptTime().toLocalDate()));
                    pstmt.setString(15, entrustment.getClientOrgName());
                    pstmt.addBatch();
                }
                pstmt.executeBatch();
            }

            // 推送检验结果
            String sql2 = "INSERT INTO b_result (id, case_accept_no, name, qualitative_result, quantitative_result, other_result, checkout_remark, order_no, form, draw_time, draw_place) " +
                    "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) " +
                    "ON DUPLICATE KEY UPDATE case_accept_no=VALUES(case_accept_no), name=VALUES(name), qualitative_result=VALUES(qualitative_result), " +
                    "quantitative_result=VALUES(quantitative_result), other_result=VALUES(other_result), checkout_remark=VALUES(checkout_remark), " +
                    "order_no=VALUES(order_no), form=VALUES(form), draw_time=VALUES(draw_time), draw_place=VALUES(draw_place)";

            try (PreparedStatement pstmt2 = connection.prepareStatement(sql2)) {
                for (EntrustMaterialCheckoutResultVO r : resultVOS) {
                    pstmt2.setString(1, r.getId());
                    pstmt2.setString(2, entrustment.getAcceptNo());
                    pstmt2.setString(3, r.getName());
                    pstmt2.setString(4, r.getQualitativeResult());
                    pstmt2.setString(5, r.getQuantitativeResult());
                    pstmt2.setString(6, r.getOtherResult());
                    pstmt2.setString(7, r.getCheckoutRemark());
                    pstmt2.setInt(8, r.getOrderNo());
                    pstmt2.setString(9, r.getForm());
                    pstmt2.setDate(10, java.sql.Date.valueOf(r.getDrawTime().toLocalDate()));
                    pstmt2.setString(11, r.getDrawPlace());
                    pstmt2.addBatch();
                }
                pstmt2.executeBatch();
            }

        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }

        entrustment.setPlatformFlag(true);
        entrustmentService.updateById(entrustment);
        return true;

    }

    /**
     * 推送嫌疑人毒检结果任务
     *
     * @param dlpUser 当前操作用户
     * @return 推送结果
     */
    @Override
    public R pushSuspectDetectionResultTask(DLPUser dlpUser) {
        // 查询所有嫌疑人列表
        List<Suspect> suspectList = suspectService.lambdaQuery()
                .list();

        // 将嫌疑人按委托ID分组
        Map<String, List<Suspect>> susMap = suspectList.stream()
                .collect(Collectors.groupingBy(Suspect::getEntrustId));

        Set<String> entrustSet = susMap.keySet();

        List<Entrustment> entrusts = entrustmentService.lambdaQuery()
                .in(Entrustment::getId, entrustSet)
                .ge(Entrustment::getStatus, EntrustStatusConstants.ENTRUST_STATUS_ACCEPTED.getStatus()) // status >= 9
                .and(qw -> qw
                        .eq(Entrustment::getPlatformFlag, false)
                        .or()
                        .isNull(Entrustment::getPlatformFlag))
                .list();

        // 提取委托ID列表
        List<String> entrustIds = entrusts.stream()
                .map(Entrustment::getId)
                .collect(Collectors.toList());

        // 查询案件信息，并构造以案件ID为键                                                                                                                                                                                                                                 的映射
        Map<String, CaseEvent> caseMap = caseEventService.lambdaQuery()
                .in(CaseEvent::getId, entrusts.stream().map(Entrustment::getCaseId).collect(Collectors.toList()))
                .list()
                .stream()
                .collect(Collectors.toMap(CaseEvent::getId, Function.identity()));

        Map<String, Entrustment> entrustMap = entrusts.stream()
                .collect(Collectors.toMap(Entrustment::getId, Function.identity()));


        // 查询毒检结果：必须存在定性结果
        List<EntrustMaterialCheckoutResult> resultList = this.lambdaQuery()
                .in(EntrustMaterialCheckoutResult::getEntrustId, entrustIds)
                .notLike(EntrustMaterialCheckoutResult::getQualitativeResult, "null")
                .isNotNull(EntrustMaterialCheckoutResult::getQualitativeResult)
                .list();

        if (resultList.isEmpty()) {
            return R.failed("未查询到有定性结果的检材检出结果！");
        }

        // 查询委托检材信息，并按ID去重（按顺序号升序排序）
        Map<String, EntrustmentIdentificationMaterial> materialMap = entrustmentIdentificationMaterialService.lambdaQuery()
                .in(EntrustmentIdentificationMaterial::getEntrustmentId, entrustIds)
                .orderByAsc(EntrustmentIdentificationMaterial::getOrderNo)
                .list()
                .stream()
                .collect(Collectors.toMap(EntrustmentIdentificationMaterial::getId, Function.identity()));

        if (materialMap.isEmpty()) {
            return R.failed("未查询到有检材信息！");
        }
        // 构造结果VO列表
        List<EntrustMaterialCheckoutResultVO> resultVOS = resultList.stream()
                .map(result -> this.buildResultVO(result, materialMap))
                .collect(Collectors.toList());

        // 将VO按委托ID分组
        Map<String, List<EntrustMaterialCheckoutResultVO>> resutlMap = resultVOS.stream()
                .collect(Collectors.groupingBy(EntrustMaterialCheckoutResultVO::getEntrustId));

        Set<String> entrustResultIds = resutlMap.keySet();

        // 按有数据的委托进行推送
        for (String entrustId : entrustResultIds) {
            Entrustment entrust = entrustMap.get(entrustId);
            List<EntrustMaterialCheckoutResultVO> vos = resutlMap.get(entrust.getId());
            List<Suspect> suspects = susMap.get(entrust.getId());
            CaseEvent caseEvent = caseMap.get(entrust.getCaseId());
            boolean ret = this.pushToDatabase(suspects, vos, entrust, caseEvent, dlpUser);
            if (!ret) {
                return R.failed("推送失败");
            }
        }

        return R.ok("推送成功");
    }


    /**
     * 生成指定年份和季度列表的季度统计数据
     *
     * @param year          统计年份
     * @param quarterlyList 季度列表（1~4）
     * @return Map，key为季度号，value为该季度对应的统计数据
     */
    public HashMap<Integer, Map<String, List<Integer>>> generateQuarterlyReportData(int year, List<Integer> quarterlyList) {
        // 先对季度排序，保证顺序一致
        quarterlyList.sort(Comparator.naturalOrder());

        HashMap<Integer, Map<String, List<Integer>>> resultMap = new HashMap<>();

        for (Integer quarter : quarterlyList) {
            // 计算季度起止时间
            LocalDateTime startDateTime = getQuarterStartDateTime(year, quarter);
            LocalDateTime endDateTime = getQuarterEndDateTime(year, quarter);

            System.out.println("开始时间：" + startDateTime);
            System.out.println("结束时间：" + endDateTime);

            // 获取该季度所有委托数据（首次鉴定、补充鉴定、重新鉴定）
            List<Entrustment> entrusts = queryEntrustments(startDateTime, endDateTime, 0,
                    Arrays.asList("首次鉴定", "补充鉴定", "重新鉴定"));

            if (entrusts == null || entrusts.isEmpty()) {
                // 如果无委托则跳过该季度
                continue;
            }

            // 统计该季度普通材料检测情况
            Map<String, List<Integer>> quarterMap = calculateMaterialStats(entrusts);

            // 统计生物样本检测情况（委托类型为1）
            List<Entrustment> inVivoEntrusts = queryEntrustments(startDateTime, endDateTime, 1, null);
            if (inVivoEntrusts != null && !inVivoEntrusts.isEmpty()) {
                quarterMap.put("生物样本", calculateEntrustmentStats(inVivoEntrusts));
            }

            // 统计初筛检测情况（委托类型0，鉴定结果为“初筛”）
            List<Entrustment> initialScreeningEntrusts = queryEntrustments(startDateTime, endDateTime, 0, Collections.singletonList("初筛"));
            if (initialScreeningEntrusts != null && !initialScreeningEntrusts.isEmpty()) {
                quarterMap.put("初筛", calculateEntrustmentStats(initialScreeningEntrusts));
            }
            List<Entrustment> entrustments = entrustmentService.lambdaQuery()
                    .between(Entrustment::getAcceptTime, startDateTime, endDateTime)
                    .ge(Entrustment::getStatus, EntrustStatusConstants.ENTRUST_STATUS_WAITING_CHECK_CLAIM.getStatus())
                    .orderByAsc(Entrustment::getAcceptTime)
                    .list();
            ArrayList<Integer> integers = new ArrayList<>();
            integers.add(Integer.valueOf(entrustments.get(0).getAcceptNo().split("-")[1]));
            integers.add(Integer.valueOf(entrustments.get(entrustments.size() - 1).getAcceptNo().split("-")[1]));
            quarterMap.put("entrustCount", integers);
            resultMap.put(quarter, quarterMap);
        }

        System.out.println(resultMap);
        return resultMap;
    }

    /**
     * 获取指定年份季度开始时间
     */
    private LocalDateTime getQuarterStartDateTime(int year, int quarter) {
        Month startMonth = Month.of((quarter - 1) * 3 + 1);
        return LocalDateTime.of(LocalDate.of(year, startMonth, 1), LocalTime.MIN);
    }

    /**
     * 获取指定年份季度结束时间
     */
    private LocalDateTime getQuarterEndDateTime(int year, int quarter) {
        Month startMonth = Month.of((quarter - 1) * 3 + 1);
        Month endMonth = startMonth.plus(2);
        LocalDate endDate = LocalDate.of(year, endMonth, 1).with(TemporalAdjusters.lastDayOfMonth());
        return LocalDateTime.of(endDate, LocalTime.MAX);
    }

    /**
     * 根据条件查询委托
     *
     * @param start                    开始时间（包含）
     * @param end                      结束时间（包含）
     * @param entrustmentType          委托类型，0或1
     * @param oldIdentificationResults 鉴定结果列表，null表示不筛选此条件
     * @return 符合条件的委托列表
     */
    private List<Entrustment> queryEntrustments(LocalDateTime start, LocalDateTime end, int entrustmentType, List<String> oldIdentificationResults) {
        LambdaQueryChainWrapper<Entrustment> query = entrustmentService.lambdaQuery()
                .between(Entrustment::getAcceptTime, start, end)
                .ge(Entrustment::getStatus, EntrustStatusConstants.ENTRUST_STATUS_ACCEPTED.getStatus())
                .eq(Entrustment::getEntrustmentType, entrustmentType);

        if (oldIdentificationResults != null && !oldIdentificationResults.isEmpty()) {
            query.in(Entrustment::getOldIdentificationResult, oldIdentificationResults);
        }
        return query.list();
    }

    /**
     * 根据委托列表，计算普通材料的统计数据
     *
     * @param entrusts 委托列表
     * @return key为药品名称字符串，value为对应统计值列表（案件总数、检出数、未检出数等）
     */
    private Map<String, List<Integer>> calculateMaterialStats(List<Entrustment> entrusts) {
        Map<String, List<Integer>> quarterMap = new HashMap<>();

        if (entrusts == null || entrusts.isEmpty()) return quarterMap;

        // 获取所有委托ID
        Set<String> entrustIds = entrusts.stream()
                .map(Entrustment::getId)
                .collect(Collectors.toSet());

        if (entrustIds.isEmpty()) return quarterMap;

        // 查询材料列表
        List<EntrustmentIdentificationMaterial> materialList = entrustmentIdentificationMaterialService.lambdaQuery()
                .in(EntrustmentIdentificationMaterial::getEntrustmentId, entrustIds)
                .list();
        if (materialList == null || materialList.isEmpty()) return quarterMap;

        // 获取材料ID集合
        Set<String> materialIds = materialList.stream()
                .map(EntrustmentIdentificationMaterial::getId)
                .collect(Collectors.toSet());
        if (materialIds.isEmpty()) return quarterMap;

        // 查询检测结果列表，排除空或null结果
        List<EntrustMaterialCheckoutResult> resultList = this.lambdaQuery()
                .in(EntrustMaterialCheckoutResult::getId, materialIds)
                .notLike(EntrustMaterialCheckoutResult::getQualitativeResult, "null")
                .isNotNull(EntrustMaterialCheckoutResult::getQualitativeResult)
                .list();

        if (resultList == null) resultList = Collections.emptyList();

        // 按材料ID分组检测结果，方便判断是否检测
        Map<String, List<EntrustMaterialCheckoutResult>> resultMap = resultList.stream()
                .collect(Collectors.groupingBy(EntrustMaterialCheckoutResult::getId));

        // 按候选药品名称分组材料（名称排序后拼接）
        Map<String, List<EntrustmentIdentificationMaterial>> drugGroupMap = materialList.stream()
                .collect(Collectors.groupingBy(item -> {
                    List<String> drugNames = DrugLiteConvert.convertDirtyLiteByJSON(item.getCandidateDrugs())
                            .stream()
                            .map(DrugLite::getName)
                            .sorted()
                            .collect(Collectors.toList());
                    return String.join("、", drugNames);
                }));

        // 遍历药品分组，统计数据
        for (Map.Entry<String, List<EntrustmentIdentificationMaterial>> entry : drugGroupMap.entrySet()) {
            String drugNames = entry.getKey();
            List<EntrustmentIdentificationMaterial> drugMaterials = entry.getValue();

            // 按委托ID分组材料
            Map<String, List<EntrustmentIdentificationMaterial>> entrustToMaterials = drugMaterials.stream()
                    .collect(Collectors.groupingBy(EntrustmentIdentificationMaterial::getEntrustmentId));

            int caseCount = entrustToMaterials.size();  // 案件总数
            int checked = 0, notChecked = 0, partChecked = 0, materialCount = drugMaterials.size(), resultCount = 0;

            // 遍历每个委托的材料，判断检测情况
            for (Map.Entry<String, List<EntrustmentIdentificationMaterial>> e : entrustToMaterials.entrySet()) {
                List<EntrustmentIdentificationMaterial> materials = e.getValue();
                int mCount = materials.size();

                // 材料中有检测结果的数量
                int rCount = (int) materials.stream()
                        .filter(m -> resultMap.containsKey(m.getId()))
                        .count();

                resultCount += rCount;

                if (rCount == 0) {
                    notChecked++;
                } else if (rCount == mCount) {
                    checked++;
                } else {
                    partChecked++;
                }
            }

            int materialNotChecked = materialCount - resultCount;

            // 按顺序添加统计值
            ArrayList<Integer> integers = new ArrayList<>();
            integers.add(caseCount);          // 案件总数
            integers.add(checked);           // 检出数（案件数）
            integers.add(notChecked);        // 未检出数（案件数）
            integers.add(partChecked);       // 部分检出数（案件数）
            integers.add(materialCount);     // 材料总数
            integers.add(resultCount);       // 材料检出数
            integers.add(materialNotChecked);// 材料未检出数

            quarterMap.put(drugNames, integers);
        }

        return quarterMap;
    }

    /**
     * 根据委托列表计算生物样本或初筛的统计数据
     * 该统计逻辑和材料统计类似，但针对委托整体
     *
     * @param entrusts 委托列表
     * @return 统计数据列表，顺序同材料统计
     */
    private List<Integer> calculateEntrustmentStats(List<Entrustment> entrusts) {
        if (entrusts == null || entrusts.isEmpty()) return Collections.emptyList();

        Set<String> entrustIds = entrusts.stream()
                .map(Entrustment::getId)
                .collect(Collectors.toSet());

        if (entrustIds.isEmpty()) return Collections.emptyList();

        // 查询所有相关材料
        List<EntrustmentIdentificationMaterial> materials = entrustmentIdentificationMaterialService.lambdaQuery()
                .in(EntrustmentIdentificationMaterial::getEntrustmentId, entrustIds)
                .list();
        if (materials == null) materials = Collections.emptyList();

        Set<String> materialIds = materials.stream()
                .map(EntrustmentIdentificationMaterial::getId)
                .collect(Collectors.toSet());

        if (materialIds.isEmpty()) return Collections.emptyList();

        // 查询所有检测结果
        List<EntrustMaterialCheckoutResult> results = this.lambdaQuery()
                .in(EntrustMaterialCheckoutResult::getId, materialIds)
                .notLike(EntrustMaterialCheckoutResult::getQualitativeResult, "null")
                .isNotNull(EntrustMaterialCheckoutResult::getQualitativeResult)
                .list();

        if (results == null) results = Collections.emptyList();

        // 按材料ID分组结果
        Map<String, List<EntrustMaterialCheckoutResult>> resultMap = results.stream()
                .collect(Collectors.groupingBy(EntrustMaterialCheckoutResult::getId));

        int caseCount = entrusts.size();
        int materialCount = materials.size();
        int resultCount = results.size();

        int checked = 0, notChecked = 0, partChecked = 0;

        // 按委托ID分组材料，判断检测状态
        Map<String, List<EntrustmentIdentificationMaterial>> entrustToMaterials = materials.stream()
                .collect(Collectors.groupingBy(EntrustmentIdentificationMaterial::getEntrustmentId));

        for (List<EntrustmentIdentificationMaterial> matList : entrustToMaterials.values()) {
            int totalM = matList.size();
            int detected = (int) matList.stream()
                    .filter(m -> resultMap.containsKey(m.getId()))
                    .count();

            if (detected == 0) notChecked++;
            else if (detected == totalM) checked++;
            else partChecked++;
        }

        ArrayList<Integer> statistics = new ArrayList<>();
        statistics.add(caseCount);          // 案件总数
        statistics.add(checked);            // 检出数（案件数）
        statistics.add(notChecked);         // 未检出数（案件数）
        statistics.add(partChecked);        // 部分检出数（案件数）
        statistics.add(materialCount);      // 材料总数
        statistics.add(resultCount);        // 材料检出数
        statistics.add(materialCount - resultCount);// 材料未检出数

        return statistics;
    }


    @Override
    public String generateQuarterlyReportExcel(GenerateQuarterlyReportDTO dto, HttpServletResponse response) throws IOException {
        HashMap<Integer, Map<String, List<Integer>>> map = this.generateQuarterlyReportData(dto.getYear(), dto.getQuarterlyList());
        XSSFWorkbook workbook = new XSSFWorkbook();
        Set<Integer> quarterlySet = map.keySet();
        quarterlySet.stream().sorted(new Comparator<Integer>() {
            @Override
            public int compare(Integer o1, Integer o2) {
                return o1 - o2;
            }
        });
        for (Integer quarterly : quarterlySet) {
            this.generateQuarterlyReportSheet(workbook, dto.getYear(), quarterly, map.get(quarterly));
        }

        ByteArrayOutputStream os = new ByteArrayOutputStream();
        workbook.write(os);
        ByteArrayInputStream is = new ByteArrayInputStream(os.toByteArray());
        String fileName = "";
        if (dto.getQuarterlyList().size() == 1) {
            fileName = dto.getYear() + "年第" + dto.getQuarterlyList().get(0) + "季度统计.xlsx";
        } else {
            fileName = dto.getYear() + "年第" + dto.getQuarterlyList().get(0) + "至" + dto.getQuarterlyList().get(dto.getQuarterlyList().size() - 1) + "季度统计.xlsx";
        }
        boolean ret = ossFile.fileSave("quarterlyReport/" + fileName, is);
        if (ret) {
            return "quarterlyReport/" + fileName;
        } else return null;
    }


    public void generateQuarterlyReportSheet(XSSFWorkbook workbook, Integer year, Integer quarterly, Map<String, List<Integer>> map) {
        String quarterlyName = "";
        switch (quarterly) {
            case 1:
                quarterlyName = "一";
                break;
            case 2:
                quarterlyName = "二";
                break;
            case 3:
                quarterlyName = "三";
                break;
            case 4:
                quarterlyName = "四";
                break;
        }
        XSSFSheet sheet = workbook.createSheet(year + "年第" + quarterlyName + "季度统计");
        int rowIndex = 0;

        // 表头样式（大号黑色字体，居中）
        XSSFCellStyle headStyle = workbook.createCellStyle();
        headStyle.setAlignment(HorizontalAlignment.CENTER);
        headStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        XSSFFont headFont = workbook.createFont();
        headFont.setFontHeightInPoints((short) 16);
        headFont.setFontName("宋体");
        headFont.setColor(IndexedColors.BLACK.getIndex());
        headStyle.setFont(headFont);

        // 创建边框居中样式基础
        XSSFCellStyle baseStyle = workbook.createCellStyle();
        baseStyle.setAlignment(HorizontalAlignment.CENTER);
        baseStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        baseStyle.setBorderTop(BorderStyle.THIN);
        baseStyle.setBorderBottom(BorderStyle.THIN);
        baseStyle.setBorderLeft(BorderStyle.THIN);
        baseStyle.setBorderRight(BorderStyle.THIN);
        baseStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
        baseStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        baseStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        baseStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());

        // 黑色字体样式
        XSSFCellStyle blackStyle = workbook.createCellStyle();
        blackStyle.cloneStyleFrom(baseStyle);
        XSSFFont blackFont = workbook.createFont();
        blackFont.setFontHeightInPoints((short) 12);
        blackFont.setFontName("宋体");
        blackFont.setColor(IndexedColors.BLACK.getIndex());
        blackStyle.setFont(blackFont);

        // 红色字体样式
        XSSFCellStyle redStyle = workbook.createCellStyle();
        redStyle.cloneStyleFrom(baseStyle);
        XSSFFont redFont = workbook.createFont();
        redFont.setFontHeightInPoints((short) 12);
        redFont.setFontName("宋体");
        redFont.setColor(IndexedColors.RED.getIndex());
        redStyle.setFont(redFont);

        List<Integer> integers = map.get("entrustCount");

        String entrustCount = year + "年" + integers.get(0) + "-" + integers.get(1) + "号种类";
        String[] nameRows = {entrustCount, "案件送检登记数", "案件检出数", "案件未检出数", "案件检出及未检出数", "检材送检登记数", "检材检出数", "检材未检出数"};
        map.remove("entrustCount");

        int[] wides = {25, 16, 12, 12, 19, 15, 12, 13};

        // 第一行（合并单元格）
        XSSFRow headRow = sheet.createRow(rowIndex);
        headRow.setHeight((short) (20.25 * 20));
        Cell headCell = headRow.createCell(0);
        headCell.setCellValue(year + "年第" + quarterlyName + "季度");
        headCell.setCellStyle(headStyle);
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, nameRows.length - 1));

        // 第二行 表头列名
        XSSFRow nameRow = sheet.createRow(rowIndex + 1);
        nameRow.setHeight((short) (14.25 * 20));

        for (int i = 0; i < nameRows.length; i++) {
            sheet.setColumnWidth(i, wides[i] * 256);
            Cell cell = nameRow.createCell(i);
            cell.setCellValue(nameRows[i]);
            // 第1列（索引1）和第5列（索引5）用红色字体样式，其他用黑色
            if (i == 1 || i == 5) {
                cell.setCellStyle(redStyle);
            } else {
                cell.setCellStyle(blackStyle);
            }
        }

        // 第三行 空白行
        XSSFRow gapRow = sheet.createRow(rowIndex + 2);
        gapRow.setHeight((short) (14.25 * 20));
        for (int i = 0; i < nameRows.length; i++) {
            Cell cell = gapRow.createCell(i);
            cell.setCellStyle(blackStyle);
        }

        rowIndex = 3;

        // 拆分 entry 顺序
        List<Map.Entry<String, List<Integer>>> normalEntries = new ArrayList<>();
        List<Map.Entry<String, List<Integer>>> specialEntries = new ArrayList<>();

        for (Map.Entry<String, List<Integer>> entry : map.entrySet()) {
            String key = entry.getKey();
            if ("生物样本".equals(key) || "初筛".equals(key)) {
                specialEntries.add(entry);
            } else {
                normalEntries.add(entry);
            }
        }

        // 合并成最终顺序：普通的在前，特殊的在后
        List<Map.Entry<String, List<Integer>>> orderedEntries = new ArrayList<>();
        orderedEntries.addAll(normalEntries);
        orderedEntries.addAll(specialEntries);

        Integer va1 = 0, va2 = 0, va3 = 0, va4 = 0, va5 = 0, va6 = 0, va7 = 0;
        Integer[] ints = {va1, va2, va3, va4, va5, va6, va7};

        for (Map.Entry<String, List<Integer>> entry : orderedEntries) {
            XSSFRow dataRow = sheet.createRow(rowIndex);
            dataRow.setHeight((short) (14.25 * 20));

            for (int i = 0; i < nameRows.length; i++) {
                Cell cell = dataRow.createCell(i);

                if (i == 0) {
                    cell.setCellValue(entry.getKey());
                } else {
                    Integer val = entry.getValue().get(i - 1);
                    if (val != null && val != 0) {
                        cell.setCellValue(val);
                        ints[i - 1] += entry.getValue().get(i - 1);
                    }
                }

                if (i == 1 || i == 5) {
                    cell.setCellStyle(redStyle);
                } else {
                    cell.setCellStyle(blackStyle);
                }


            }

            // 空白行
            XSSFRow emptyRow = sheet.createRow(rowIndex + 1);
            emptyRow.setHeight((short) (14.25 * 20));
            for (int i = 0; i < nameRows.length; i++) {
                Cell cell = emptyRow.createCell(i);
                cell.setCellStyle(blackStyle);
            }

            rowIndex += 2;
        }
        XSSFRow lastRow = sheet.createRow(rowIndex - 1);
        lastRow.setHeight((short) (14.25 * 20));
        for (int i = 0; i < nameRows.length; i++) {
            if (i == 0) {
                Cell cell = lastRow.createCell(i);
                cell.setCellStyle(blackStyle);
            }
            if (i != 0) {
                Cell cell = lastRow.createCell(i);
                cell.setCellValue(ints[i - 1]);
                if (i == 1 || i == 5) {
                    cell.setCellStyle(redStyle);
                } else {
                    cell.setCellStyle(blackStyle);
                }
            }
        }
    }


}




