package cn.besteam.modules.report.biz;

import cn.besteam.common.constant.DepartmentEnum;
import cn.besteam.common.constant.ErrorCode;
import cn.besteam.common.constant.MedicalWasteEnum;
import cn.besteam.common.exception.BusinessException;
import cn.besteam.common.util.DateUtil;
import cn.besteam.modules.medical.entity.MwCheckoutRecord;
import cn.besteam.modules.medical.entity.MwCollectRecord;
import cn.besteam.modules.medical.service.*;
import cn.besteam.modules.report.dto.ReportDTO;
import cn.besteam.modules.report.vo.*;
import cn.besteam.modules.system.entity.SysDepartment;
import cn.besteam.modules.system.entity.SysDict;
import cn.besteam.modules.system.entity.SysDictItem;
import cn.besteam.modules.system.service.ISysDepartmentService;
import cn.besteam.modules.system.service.ISysDictItemService;
import cn.besteam.modules.system.service.ISysDictService;
import cn.hutool.poi.excel.ExcelWriter;
import com.alibaba.fastjson.JSONArray;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections.CollectionUtils;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;

import static cn.besteam.common.util.DateUtil.*;
import static cn.besteam.common.util.DateUtil.yyyy_MM;

/**
 * 统计报表
 */
@Service
@Slf4j
public class ReportBiz {

    @Autowired
    private IMwCheckoutRecordService mwCheckoutRecordService;

    @Autowired
    private IMwCheckoutRecordItemService mwCheckoutRecordItemService;

    @Autowired
    private ISysDictService sysDictService;

    @Autowired
    private ISysDictItemService sysDictItemService;

    @Autowired
    private ISysDepartmentService sysDepartmentService;

    @Autowired
    private IMwCollectRecordService mwCollectRecordService;

    @Autowired
    private IMwTransitConfigService mwTransitConfigService;

    /**
     * 获取所有医废类型
     *
     * @return
     */
    public List<SysDictItem> getWasteTypeList() {

        SysDict sysDict = sysDictService.getOne(new QueryWrapper<SysDict>().lambda().eq(SysDict::getDictCode, "MEDICAL_WASTE_TYPE"));
        if (sysDict == null) {
            return null;
        }
        //获取字典所有医废类型
        return sysDictItemService.list(new QueryWrapper<SysDictItem>().lambda().eq(SysDictItem::getDictId, sysDict.getId()));
    }
    /**
     * 获取所有医废类型
     *
     * @return
     */
    private List<SysDictItem> getWasteTypeList(String ids) {

        SysDict sysDict = sysDictService.getOne(new QueryWrapper<SysDict>().lambda().eq(SysDict::getDictCode, "MEDICAL_WASTE_TYPE"));
        if (sysDict == null) {
            return null;
        }
        QueryWrapper<SysDictItem> queryWrapper=new QueryWrapper();
        queryWrapper.lambda().eq(SysDictItem::getDictId, sysDict.getId());
        //获取字典所有医废类型
        if(ids != null && !"".equals(ids)){
            String[] ids_=ids.split(",");
            JSONArray parse=new JSONArray();
            for(String i:ids_){
                parse.add(i);
            }
            queryWrapper.lambda().in(SysDictItem::getId,parse);
        }
        return sysDictItemService.list(queryWrapper);
    }
    /**
     * 医疗废物转移联单
     */
    public WasteTransformVO getTransformList(ReportDTO dto) {

        WasteTransformVO vo = new WasteTransformVO();
        List<SysDictItem> wasteTypeList = getWasteTypeList(dto.getDictTypeIds());
        if (CollectionUtils.isEmpty(wasteTypeList)) {
            return vo;
        }
        SysDepartment hospital = sysDepartmentService.getById(dto.getDepartmentId());
        if (hospital == null) {
            return vo;
        }
        vo.setDepartmentName(hospital.getDepartName());
        /*MwTransitConfig mwTransitConfig = mwTransitConfigService.getById(dto.getTransitConfigId());
        if (mwTransitConfig == null) {
            return vo;
        }
        vo.setTransitConfigName(mwTransitConfig.getTransitCompany());*/
        vo.setTransitConfigName(dto.getTransitCompany());
        vo.setLegend(wasteTypeList.stream().map(SysDictItem::getItemText).collect(Collectors.toList()));
        vo.getLegend().add("小计");
        //
        List<MwCheckoutRecord> checkoutRecordList = mwCheckoutRecordService.list(new QueryWrapper<MwCheckoutRecord>()//
                .lambda()//
                .eq(MwCheckoutRecord::getHospitalId, dto.getDepartmentId())//
                //.eq(MwCheckoutRecord::getTransitConfigId, dto.getTransitConfigId())//
                .eq(MwCheckoutRecord::getTransitCompany, dto.getTransitCompany())//
                .between(MwCheckoutRecord::getCreateTime, DateUtil.format(dto.getStartTime(), "yyyy-MM-dd HH:mm:ss"), DateUtil.format(dto.getEndTime(), "yyyy-MM-dd HH:mm:ss"))//
                .orderByAsc(MwCheckoutRecord::getCreateTime));
        if (CollectionUtils.isEmpty(checkoutRecordList)) {
            return vo;
        }
        List<CheckoutItemReportVO> checkoutItemReportList = mwCheckoutRecordItemService.getCheckoutItemReportList(checkoutRecordList.stream().map(MwCheckoutRecord::getId).collect(Collectors.toList()));
        Map<Integer, List<CheckoutItemReportVO>> checkoutRecordItemMap = checkoutItemReportList.stream().collect(Collectors.groupingBy(CheckoutItemReportVO::getCheckoutRecordId));
        //
        List<WasteTransformItemVO> voList = new ArrayList<>();
        for (MwCheckoutRecord mwCheckoutRecord : checkoutRecordList) {
            List<CheckoutItemReportVO> tempList = checkoutRecordItemMap.getOrDefault(mwCheckoutRecord.getId(), new ArrayList<>());
            if(CollectionUtils.isEmpty(tempList)){
                continue;
            }
            WasteTransformItemVO itemVO = WasteTransformItemVO.of(mwCheckoutRecord);
            //
            Long totalCount = 0L;
            BigDecimal totalWeight = BigDecimal.ZERO;
            for (SysDictItem sysDictItem : wasteTypeList) {
                Long count = tempList.stream().filter(e -> e.getWasteType().equals(sysDictItem.getId())).count();
                BigDecimal weight = tempList.stream().filter(e -> e.getWasteType().equals(sysDictItem.getId())).map(CheckoutItemReportVO::getWeight).reduce(BigDecimal::add).orElse(BigDecimal.ZERO).setScale(2, BigDecimal.ROUND_HALF_UP);
                itemVO.getData().add(new BigDecimal(count));
                itemVO.getData().add(weight);
                //
                totalCount = totalCount + count;
                totalWeight = totalWeight.add(weight);
            }
            itemVO.getData().add(new BigDecimal(totalCount));
            itemVO.getData().add(totalWeight);
            voList.add(itemVO);
        }
        //合计
        WasteTransformItemVO totalItemVO = new WasteTransformItemVO();
        totalItemVO.setDate("合计");
        totalItemVO.setKey(-2);
        totalItemVO.setData(new ArrayList<>());
        Long totalCount = 0L;
        BigDecimal totalWeight = BigDecimal.ZERO;
        for (SysDictItem sysDictItem : wasteTypeList) {
            Long count = checkoutItemReportList.stream().filter(e -> e.getWasteType().equals(sysDictItem.getId())).count();
            BigDecimal weight = checkoutItemReportList.stream().filter(e -> e.getWasteType().equals(sysDictItem.getId())).map(CheckoutItemReportVO::getWeight).reduce(BigDecimal::add).orElse(BigDecimal.ZERO).setScale(2, BigDecimal.ROUND_HALF_UP);
            totalItemVO.getData().add(new BigDecimal(count));
            totalItemVO.getData().add(weight);
            //
            totalCount = totalCount + count;
            totalWeight = totalWeight.add(weight);
        }
        totalItemVO.getData().add(new BigDecimal(totalCount));
        totalItemVO.getData().add(totalWeight);
        voList.add(0,totalItemVO);
        vo.setList(voList);
        return vo;
    }

    /**
     * 科室产废报表
     *
     * @param dto
     */
    public DepartmentReportVO getOfficeReportList(ReportDTO dto) {

        SysDepartment hospital = sysDepartmentService.getById(dto.getDepartmentId());
        if (hospital == null) {
            throw new BusinessException(ErrorCode.ERR_500, "医院不存在");
        }
        if (!hospital.getOrgType().equals(DepartmentEnum.OrgTypeEnum.HOSPITAL.getType())) {
            throw new BusinessException(ErrorCode.ERR_500, "医院不存在");
        }
        DepartmentReportVO vo = new DepartmentReportVO();
        vo.setDepartmentName(hospital.getDepartName());

        // 根据统计分类获取对应的医废类型名称列表
        List<String> wasteTypeStrList = MedicalWasteEnum.WASTE_TYPE_CATEGORY_MAP.get(dto.getWasteType());
        if (CollectionUtils.isEmpty(wasteTypeStrList)) {
            throw new BusinessException(ErrorCode.ERR_500, "无效的统计分类");
        }

        // 获取所有医废类型字典，并根据统计分类过滤
        List<SysDictItem> wasteTypeList = getWasteTypeList();
        if (CollectionUtils.isEmpty(wasteTypeList)) {
            return vo;
        }
        // 过滤字典类型列表，只保留对应分类的字典项
        wasteTypeList = wasteTypeList.stream()
                .filter(item -> wasteTypeStrList.contains(item.getItemText()))
                .collect(Collectors.toList());
        if (CollectionUtils.isEmpty(wasteTypeList)) {
            return vo;
        }

        vo.setLegend(wasteTypeList.stream().map(SysDictItem::getItemText).collect(Collectors.toList()));
        vo.getLegend().add("小计");
        vo.getLegend().add("单日产废床位比");
        //
        LambdaQueryWrapper<SysDepartment> wrapper = new QueryWrapper<SysDepartment>()//
                .lambda()//
                .likeRight(SysDepartment::getTreeCode, hospital.getTreeCode())//
                .eq(SysDepartment::getOrgType, DepartmentEnum.OrgTypeEnum.OFFICE.getType())//
                .eq(SysDepartment::getIsWarehouse, 0);
        if (dto.getOrderType().equals(0)) {
            wrapper.orderByAsc(SysDepartment::getDepartOrder);
        } else {
            wrapper.orderByAsc(SysDepartment::getDepartName);
        }
        List<SysDepartment> officeList = sysDepartmentService.list(wrapper);
        if (CollectionUtils.isEmpty(officeList)) {
            return vo;
        }
        List<MwCollectRecord> mwCollectRecordList = mwCollectRecordService.list(new QueryWrapper<MwCollectRecord>()//
                .lambda()//
                .between(MwCollectRecord::getCreateTime, DateUtil.format(dto.getStartTime(), "yyyy-MM-dd HH:mm:ss"), DateUtil.format(dto.getEndTime(), "yyyy-MM-dd HH:mm:ss"))//
                .in(MwCollectRecord::getDepartmentId, officeList.stream().map(SysDepartment::getId).collect(Collectors.toList()))//
                .in(MwCollectRecord::getWasteTypeStr, wasteTypeStrList)// 根据统计分类过滤医废类型
                .eq(MwCollectRecord::getDelFlag, 0)//
                .eq(MwCollectRecord::getAuditStatus, MedicalWasteEnum.MedicalWasteAuditStatusEnum.PASS.getStatus()));
        Map<Integer, List<MwCollectRecord>> mwCollectRecordMap = mwCollectRecordList.stream().collect(Collectors.groupingBy(MwCollectRecord::getDepartmentId));
        
        // 计算统计天数
        Integer days = calculateDays(dto);
        
        //
        List<DepartmentReportItemVO> voList = buildDepartmentReportItemVOList(wasteTypeList, officeList, mwCollectRecordList, mwCollectRecordMap, days);
        
        // 根据orderType进行排序
        List<DepartmentReportItemVO> finalVoList = new ArrayList<>();
        if (dto.getOrderType().equals(1)) {
            // 根据科室产废总重量进行降序排列（排除合计行）
            List<DepartmentReportItemVO> sortedVoList = voList.stream()
                    .filter(item -> item.getKey() != -2) // 过滤掉合计行
                    .sorted((a, b) -> {
                        // 获取总重量（在data数组的倒数第二个位置）
                        BigDecimal weightA = a.getData().get(a.getData().size() - 2);
                        BigDecimal weightB = b.getData().get(b.getData().size() - 2);
                        return weightB.compareTo(weightA); // 降序排列
                    })
                    .collect(Collectors.toList());
            
            // 将合计行添加到排序后的列表开头
            DepartmentReportItemVO totalItemVO = voList.stream()
                    .filter(item -> item.getKey() == -2)
                    .findFirst()
                    .orElse(null);
            
            if (totalItemVO != null) {
                finalVoList.add(totalItemVO);
            }
            finalVoList.addAll(sortedVoList);
        } else {
            // orderType=0时，保持原有顺序（按部门排序字段）
            finalVoList = voList;
        }
        
        vo.setList(finalVoList);
        return vo;
    }
    /**
     * 通用-科室 、 医院
     *
     * @param wasteTypeList
     * @param officeList
     * @param mwCollectRecordList
     * @param mwCollectRecordMap
     * @return
     */
    private List<DepartmentReportItemVO> buildDepartmentReportItemVOList(List<SysDictItem> wasteTypeList, List<SysDepartment> officeList, List<MwCollectRecord> mwCollectRecordList, Map<Integer, List<MwCollectRecord>> mwCollectRecordMap, Integer days) {

        List<DepartmentReportItemVO> voList = new ArrayList<>();
        for (SysDepartment office : officeList) {
            DepartmentReportItemVO itemVO = new DepartmentReportItemVO();
            itemVO.setKey(office.getId());
            itemVO.setName(office.getDepartName());
            itemVO.setData(new ArrayList<>());
            //
            Long totalCount = 0L;
            BigDecimal totalWeight = BigDecimal.ZERO;
            List<MwCollectRecord> tempList = mwCollectRecordMap.getOrDefault(office.getId(), new ArrayList<>());
            for (SysDictItem sysDictItem : wasteTypeList) {
                Long count = tempList.stream().filter(e -> e.getWasteType().equals(sysDictItem.getId())).count();
                BigDecimal weight = tempList.stream().filter(e -> e.getWasteType().equals(sysDictItem.getId())).map(MwCollectRecord::getWeight).reduce(BigDecimal::add).orElse(BigDecimal.ZERO).setScale(2, BigDecimal.ROUND_HALF_UP);
                itemVO.getData().add(new BigDecimal(count));
                itemVO.getData().add(weight);
                //
                totalCount = totalCount + count;
                totalWeight = totalWeight.add(weight);
            }
            itemVO.getData().add(new BigDecimal(totalCount));
            itemVO.getData().add(totalWeight);
            
            // 计算该科室的有效天数（有产废记录的天数）
            Integer effectiveDays = calculateEffectiveDays(tempList);
            
            // 计算单日产废床位比并添加到data数组（使用有效天数）
            BigDecimal dailyWasteBedRatio = calculateDailyWasteBedRatio(totalWeight, office.getBedCount(), effectiveDays);
            itemVO.getData().add(dailyWasteBedRatio);
            
            voList.add(itemVO);
        }
        //合计
        DepartmentReportItemVO totalItemVO = new DepartmentReportItemVO();
        totalItemVO.setKey(-2);
        totalItemVO.setName("合计");
        totalItemVO.setData(new ArrayList<>());
        Long totalCount = 0L;
        BigDecimal totalWeight = BigDecimal.ZERO;
        Integer totalBedCount = 0;
        for (SysDictItem sysDictItem : wasteTypeList) {
            Long count = mwCollectRecordList.stream().filter(e -> e.getWasteType().equals(sysDictItem.getId())).count();
            BigDecimal weight = mwCollectRecordList.stream().filter(e -> e.getWasteType().equals(sysDictItem.getId())).map(MwCollectRecord::getWeight).reduce(BigDecimal::add).orElse(BigDecimal.ZERO).setScale(2, BigDecimal.ROUND_HALF_UP);
            totalItemVO.getData().add(new BigDecimal(count));
            totalItemVO.getData().add(weight);
            //
            totalCount = totalCount + count;
            totalWeight = totalWeight.add(weight);
        }
        totalItemVO.getData().add(new BigDecimal(totalCount));
        totalItemVO.getData().add(totalWeight);
        
        // 计算合计的单日产废床位比（平均值）并添加到data数组
        BigDecimal totalDailyWasteBedRatio = calculateAverageDailyWasteBedRatio(mwCollectRecordList, officeList, days);
        totalItemVO.getData().add(totalDailyWasteBedRatio);
        
        voList.add(0,totalItemVO);
        return voList;
    }

    public DepartmentReportVO getOfficeReportList1(ReportDTO dto) {

        SysDepartment hospital = sysDepartmentService.getById(dto.getDepartmentId());
        if (hospital == null) {
            throw new BusinessException(ErrorCode.ERR_500, "科室不存在");
        }
        if (!hospital.getOrgType().equals(DepartmentEnum.OrgTypeEnum.OFFICE.getType())) {
            throw new BusinessException(ErrorCode.ERR_500, "科室不存在");
        }
        DepartmentReportVO vo = new DepartmentReportVO();
        vo.setDepartmentName(hospital.getDepartName());
        hospital = sysDepartmentService.getById(hospital.getParentId());
        if(hospital.getOrgType().equals(DepartmentEnum.OrgTypeEnum.DEPARTMENT.getType())){
            hospital = sysDepartmentService.getById(hospital.getParentId());
        }
        if(hospital.getOrgType().equals(DepartmentEnum.OrgTypeEnum.HOSPITAL.getType())){
            vo.setCompanyName(hospital.getDepartName());
            hospital = sysDepartmentService.getById(hospital.getParentId());
            vo.setCountyName(hospital.getDepartName());
            hospital = sysDepartmentService.getById(hospital.getParentId());
            vo.setCityName(hospital.getDepartName());
        }

        // 根据统计分类获取对应的医废类型名称列表
        List<String> wasteTypeStrList = MedicalWasteEnum.WASTE_TYPE_CATEGORY_MAP.get(dto.getWasteType());
        if (CollectionUtils.isEmpty(wasteTypeStrList)) {
            throw new BusinessException(ErrorCode.ERR_500, "无效的统计分类");
        }

        if(dto.getStyleTpl()!=null && dto.getStyleTpl()==2){
            dto.setDictTypeIds("1,2,3,4,5");
        }
        List<SysDictItem> wasteTypeList = getWasteTypeList(dto.getDictTypeIds());
        if (CollectionUtils.isEmpty(wasteTypeList)) {
            return vo;
        }
        // 过滤字典类型列表，只保留对应分类的字典项
        wasteTypeList = wasteTypeList.stream()
                .filter(item -> wasteTypeStrList.contains(item.getItemText()))
                .collect(Collectors.toList());
        if (CollectionUtils.isEmpty(wasteTypeList)) {
            return vo;
        }

        vo.setLegend(wasteTypeList.stream().map(SysDictItem::getItemText).collect(Collectors.toList()));
        vo.getLegend().add("小计");
        if(dto.getEndTime().getTime()>new Date().getTime()){
            dto.setEndTime(new Date());
        }
        //
        List<MwCollectRecord> mwCollectRecordList = mwCollectRecordService.list(new QueryWrapper<MwCollectRecord>()//
                .lambda()//
                .between(MwCollectRecord::getCreateTime, DateUtil.format(dto.getStartTime(), "yyyy-MM-dd HH:mm:ss"), DateUtil.format(dto.getEndTime(), "yyyy-MM-dd HH:mm:ss"))//
                .eq(MwCollectRecord::getDepartmentId, dto.getDepartmentId())//
                .in(MwCollectRecord::getWasteTypeStr, wasteTypeStrList)// 根据统计分类过滤医废类型
                .eq(MwCollectRecord::getDelFlag, 0)//
                .eq(MwCollectRecord::getAuditStatus, MedicalWasteEnum.MedicalWasteAuditStatusEnum.PASS.getStatus()));
        for(MwCollectRecord mwCollectRecord:mwCollectRecordList){
            mwCollectRecord.setCreateTimes(DateUtil.format(mwCollectRecord.getCreateTime(), "yyyy-MM-dd"));
        }
        Map<String, List<MwCollectRecord>> mwCollectRecordMap = mwCollectRecordList.stream().collect(Collectors.groupingBy(MwCollectRecord::getCreateTimes));
        //
        List<DepartmentReportItemVO> voList = buildDepartmentReportItemVOList1(wasteTypeList, mwCollectRecordList, mwCollectRecordMap,dto);
        vo.setList(voList);
        return vo;
    }
    /**
     * 通用-科室 、 医院
     *
     * @param wasteTypeList
     * @param mwCollectRecordList
     * @param mwCollectRecordMap
     * @return
     */
    private List<DepartmentReportItemVO> buildDepartmentReportItemVOList1(List<SysDictItem> wasteTypeList, List<MwCollectRecord> mwCollectRecordList, Map<String, List<MwCollectRecord>> mwCollectRecordMap,ReportDTO dto) {
        Integer dayNum = (int)((dto.getEndTime().getTime()-dto.getStartTime().getTime())/(24*60*60*1000L));
        List<DepartmentReportItemVO> voList = new ArrayList<>();
        //
        MwCollectRecord mwCollectRecord = mwCollectRecordService.getOne(new QueryWrapper<MwCollectRecord>()//
                .lambda()//
                .eq(MwCollectRecord::getDepartmentId, dto.getDepartmentId())//
                .eq(MwCollectRecord::getDelFlag, 0)//
                .eq(MwCollectRecord::getAuditStatus, MedicalWasteEnum.MedicalWasteAuditStatusEnum.PASS.getStatus()).last("order by id desc limit 1"));
        for (int i=0;i<=dayNum;i++) {
            DepartmentReportItemVO itemVO = new DepartmentReportItemVO();
            itemVO.setKey(i);
            itemVO.setName(DateUtil.format(new Date(dto.getStartTime().getTime()+24*60*60*1000L*i), "yyyy-MM-dd"));
            itemVO.setData(new ArrayList<>());
            //
            Long totalCount = 0L;
            BigDecimal totalWeight = BigDecimal.ZERO;
            List<MwCollectRecord> tempList = mwCollectRecordMap.getOrDefault(itemVO.getName(), new ArrayList<>());
            if(mwCollectRecord!=null){
                itemVO.setDepartmentUserName(mwCollectRecord.getDepartmentUserName());
                itemVO.setCreator(mwCollectRecord.getCreator());
            }

            for (SysDictItem sysDictItem : wasteTypeList) {
                Long count = tempList.stream().filter(e -> e.getWasteType().equals(sysDictItem.getId())).count();
                BigDecimal weight = tempList.stream().filter(e -> e.getWasteType().equals(sysDictItem.getId())).map(MwCollectRecord::getWeight).reduce(BigDecimal::add).orElse(BigDecimal.ZERO).setScale(2, BigDecimal.ROUND_HALF_UP);
                itemVO.getData().add(new BigDecimal(count));
                itemVO.getData().add(weight);
                //
                totalCount = totalCount + count;
                totalWeight = totalWeight.add(weight);
            }
            itemVO.getData().add(new BigDecimal(totalCount));
            itemVO.getData().add(totalWeight);
            voList.add(itemVO);
        }
        //合计
        DepartmentReportItemVO totalItemVO = new DepartmentReportItemVO();
        totalItemVO.setKey(-2);
        totalItemVO.setName("合计");
        totalItemVO.setData(new ArrayList<>());
        Long totalCount = 0L;
        BigDecimal totalWeight = BigDecimal.ZERO;
        for (SysDictItem sysDictItem : wasteTypeList) {
            Long count = mwCollectRecordList.stream().filter(e -> e.getWasteType().equals(sysDictItem.getId())).count();
            BigDecimal weight = mwCollectRecordList.stream().filter(e -> e.getWasteType().equals(sysDictItem.getId())).map(MwCollectRecord::getWeight).reduce(BigDecimal::add).orElse(BigDecimal.ZERO).setScale(2, BigDecimal.ROUND_HALF_UP);
            totalItemVO.getData().add(new BigDecimal(count));
            totalItemVO.getData().add(weight);
            //
            totalCount = totalCount + count;
            totalWeight = totalWeight.add(weight);
        }
        totalItemVO.getData().add(new BigDecimal(totalCount));
        totalItemVO.getData().add(totalWeight);
        voList.add(0,totalItemVO);
        return voList;
    }

    /**
     * 医院产废报表
     *
     * @param dto
     * @return
     */
    public DepartmentReportVO getHospitalReportList(ReportDTO dto) {

        SysDepartment department = sysDepartmentService.getById(dto.getDepartmentId());
        if (department == null) {
            throw new BusinessException(ErrorCode.ERR_500, "区域不存在");
        }
        if (!department.getOrgType().equals(DepartmentEnum.OrgTypeEnum.AREA.getType())) {
            throw new BusinessException(ErrorCode.ERR_500, "区域不存在");
        }
        DepartmentReportVO vo = new DepartmentReportVO();
        vo.setDepartmentName(department.getDepartName());
        List<SysDictItem> wasteTypeList = getWasteTypeList();
        if (CollectionUtils.isEmpty(wasteTypeList)) {
            return vo;
        }
        vo.setLegend(wasteTypeList.stream().map(SysDictItem::getItemText).collect(Collectors.toList()));
        vo.getLegend().add("小计");
        //
        List<SysDepartment> hospitalList = sysDepartmentService.list(new QueryWrapper<SysDepartment>()//
                .lambda()//
                .likeRight(SysDepartment::getTreeCode, department.getTreeCode())//
                .eq(SysDepartment::getOrgType, DepartmentEnum.OrgTypeEnum.HOSPITAL.getType()));//
        if (CollectionUtils.isEmpty(hospitalList)) {
            return vo;
        }
        List<MwCollectRecord> mwCollectRecordList = mwCollectRecordService.list(new QueryWrapper<MwCollectRecord>()//
                .lambda()//
                .in(MwCollectRecord::getHospitalId, hospitalList.stream().map(SysDepartment::getId).collect(Collectors.toList()))//
                .between(MwCollectRecord::getCreateTime, DateUtil.format(dto.getStartTime(), "yyyy-MM-dd HH:mm:ss"), DateUtil.format(dto.getEndTime(), "yyyy-MM-dd HH:mm:ss"))//
                .eq(MwCollectRecord::getDelFlag, 0)//
                .eq(MwCollectRecord::getAuditStatus, MedicalWasteEnum.MedicalWasteAuditStatusEnum.PASS.getStatus()));
        Map<Integer, List<MwCollectRecord>> mwCollectRecordMap = mwCollectRecordList.stream().collect(Collectors.groupingBy(MwCollectRecord::getHospitalId));
        //
        // 计算统计天数
        Integer days = calculateDays(dto);
        List<DepartmentReportItemVO> voList = buildDepartmentReportItemVOList(wasteTypeList, hospitalList, mwCollectRecordList, mwCollectRecordMap, days);
        vo.setList(voList);
        return vo;
    }

    /**
     * 区域产废报表
     *
     * @param dto
     * @return
     */
    public DepartmentReportVO getRegionReportList(ReportDTO dto) {

        SysDepartment department = sysDepartmentService.getById(dto.getDepartmentId());
        if (department == null) {
            throw new BusinessException(ErrorCode.ERR_500, "区域不存在");
        }
        if (!department.getOrgType().equals(DepartmentEnum.OrgTypeEnum.AREA.getType())) {
            throw new BusinessException(ErrorCode.ERR_500, "区域不存在");
        }
        DepartmentReportVO vo = new DepartmentReportVO();
        vo.setDepartmentName(department.getDepartName());
        List<SysDictItem> wasteTypeList = getWasteTypeList();
        if (CollectionUtils.isEmpty(wasteTypeList)) {
            return vo;
        }
        vo.setLegend(wasteTypeList.stream().map(SysDictItem::getItemText).collect(Collectors.toList()));
        vo.getLegend().add("小计");
        //
        List<SysDepartment> regionList = sysDepartmentService.list(new QueryWrapper<SysDepartment>()//
                .lambda()//
                .eq(SysDepartment::getParentId, department.getId())//
                .eq(SysDepartment::getOrgType, DepartmentEnum.OrgTypeEnum.AREA.getType()));//
        if (CollectionUtils.isEmpty(regionList)) {
            return vo;
        }
        List<MwCollectRecord> totalMwCollectRecordList = new ArrayList<>();
        List<DepartmentReportItemVO> voList = new ArrayList<>();
        for (SysDepartment sysDepartment : regionList) {
            DepartmentReportItemVO itemVO = new DepartmentReportItemVO();
            itemVO.setKey(sysDepartment.getId());
            itemVO.setName(sysDepartment.getDepartName());
            itemVO.setData(new ArrayList<>());
            //获取下级组织
            List<SysDepartment> childrenList = sysDepartmentService.list(new QueryWrapper<SysDepartment>().lambda().likeRight(SysDepartment::getTreeCode, sysDepartment.getTreeCode()));
            List<MwCollectRecord> mwCollectRecordList = new ArrayList<>();
            if (CollectionUtils.isNotEmpty(childrenList)) {
                mwCollectRecordList = mwCollectRecordService.list(new QueryWrapper<MwCollectRecord>().lambda()//
                        .in(MwCollectRecord::getHospitalId, childrenList.stream().map(SysDepartment::getId).collect(Collectors.toList()))//
                        .between(MwCollectRecord::getCreateTime, DateUtil.format(dto.getStartTime(), "yyyy-MM-dd HH:mm:ss"), DateUtil.format(dto.getEndTime(), "yyyy-MM-dd HH:mm:ss"))//
                        .eq(MwCollectRecord::getDelFlag, 0)//
                        .eq(MwCollectRecord::getAuditStatus, MedicalWasteEnum.MedicalWasteAuditStatusEnum.PASS.getStatus()));
                totalMwCollectRecordList.addAll(mwCollectRecordList);
            }
            Long totalCount = 0L;
            BigDecimal totalWeight = BigDecimal.ZERO;
            for (SysDictItem sysDictItem : wasteTypeList) {
                Long count = mwCollectRecordList.stream().filter(e -> e.getWasteType().equals(sysDictItem.getId())).count();
                BigDecimal weight = mwCollectRecordList.stream().filter(e -> e.getWasteType().equals(sysDictItem.getId())).map(MwCollectRecord::getWeight).reduce(BigDecimal::add).orElse(BigDecimal.ZERO).setScale(2, BigDecimal.ROUND_HALF_UP);
                itemVO.getData().add(new BigDecimal(count));
                itemVO.getData().add(weight);
                //
                totalCount = totalCount + count;
                totalWeight = totalWeight.add(weight);
            }
            itemVO.getData().add(new BigDecimal(totalCount));
            itemVO.getData().add(totalWeight);
            voList.add(itemVO);
        }
        //合计
        DepartmentReportItemVO totalItemVO = new DepartmentReportItemVO();
        totalItemVO.setKey(-2);
        totalItemVO.setName("合计");
        totalItemVO.setData(new ArrayList<>());
        Long totalCount = 0L;
        BigDecimal totalWeight = BigDecimal.ZERO;
        for (SysDictItem sysDictItem : wasteTypeList) {
            Long count = totalMwCollectRecordList.stream().filter(e -> e.getWasteType().equals(sysDictItem.getId())).count();
            BigDecimal weight = totalMwCollectRecordList.stream().filter(e -> e.getWasteType().equals(sysDictItem.getId())).map(MwCollectRecord::getWeight).reduce(BigDecimal::add).orElse(BigDecimal.ZERO).setScale(2, BigDecimal.ROUND_HALF_UP);
            totalItemVO.getData().add(new BigDecimal(count));
            totalItemVO.getData().add(weight);
            //
            totalCount = totalCount + count;
            totalWeight = totalWeight.add(weight);
        }
        totalItemVO.getData().add(new BigDecimal(totalCount));
        totalItemVO.getData().add(totalWeight);
        voList.add(0,totalItemVO);
        vo.setList(voList);
        return vo;
    }

    /**
     * 导出报表
     *
     * @param type 1转移联单 2科室产废统计 3医院产废统计 4区域产废统计
     * @param dto
     */
    public void exportReport(HttpServletResponse response, ReportDTO dto, Integer type) throws IOException {

        String fileName = "";
        ExcelWriter writer = null;
        switch (type) {
            case 1:
                fileName = "转移联单";
                writer = new ExcelWriter(exportTransform(dto, fileName), fileName);
                break;
            case 2:
                fileName = "科室产废统计";
                writer = new ExcelWriter(exportDepartmentReport(dto, type, fileName), fileName);
                break;
            case 3:
                fileName = "医院产废统计";
                writer = new ExcelWriter(exportDepartmentReport(dto, type, fileName), fileName);
                break;
            case 4:
                fileName = "区域产废统计";
                writer = new ExcelWriter(exportDepartmentReport(dto, type, fileName), fileName);
                break;
            case 5:
                fileName = "科室产废统计";
                writer = new ExcelWriter(exportDepartmentReport(dto, type, fileName), fileName);
                break;
            case 6:
                fileName = "科室产废统计";
                writer = new ExcelWriter(exportDepartmentReport1(dto, type, fileName), fileName);
                break;
            case 7:
                fileName = "产废月报表";
                XSSFWorkbook workbook7 = exportDepartmentReport(dto, type, fileName);
                writer = new ExcelWriter(workbook7, workbook7.getSheetName(0));
                break;
            case 8:
                fileName = "科室产废统计";
                writer = new ExcelWriter(exportDepartmentReport1(dto, type, fileName), fileName);
                break;
            case 9:
                fileName = "医院产废报表";
                writer = new ExcelWriter(exportDepartmentReport(dto, type, fileName), fileName);
                break;
            default:
                throw new BusinessException(ErrorCode.ERR_500, "请选择报表类型");
        }
        if (writer != null) {
            response.setContentType("application/octet-stream;charset=utf-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xlsx");
            writer.flush(response.getOutputStream(), true);
            writer.close();
        }
    }

    private XSSFCellStyle createDefaultCellStyle(XSSFWorkbook workbook) {

        XSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        XSSFFont font = workbook.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 12);
        cellStyle.setFont(font);
        return cellStyle;
    }

    private XSSFCellStyle createDefaultLeftCellStyle(XSSFWorkbook workbook) {

        XSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setAlignment(HorizontalAlignment.LEFT);
        XSSFFont font = workbook.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 12);
        cellStyle.setFont(font);
        return cellStyle;
    }

    private XSSFCellStyle createDefaultRightCellStyle(XSSFWorkbook workbook) {

        XSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setAlignment(HorizontalAlignment.RIGHT);
        XSSFFont font = workbook.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 12);
        cellStyle.setFont(font);
        return cellStyle;
    }

    /**
     * 导出转移联单
     *
     * @param dto
     * @return
     */
       private XSSFWorkbook exportTransform(ReportDTO dto, String sheetName) {

        WasteTransformVO vo = getTransformList(dto);
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet(sheetName);
        XSSFCellStyle cellStyle = createDefaultCellStyle(workbook);
        XSSFCellStyle leftCellStyle = createDefaultLeftCellStyle(workbook);
        XSSFCellStyle rightCellStyle = createDefaultRightCellStyle(workbook);
        //
        int maxCellSize = 4 + vo.getLegend().size() * 2;
        XSSFRow titleRow = sheet.createRow(0);
        XSSFRow headFirstRow = sheet.createRow(3);
        XSSFRow headSecondRow = sheet.createRow(4);
        XSSFRow departmentRow = sheet.createRow(1);
        XSSFRow transitConfigRow = sheet.createRow(2);
        int legendIndex = 0;
        for (int i = 0; i < maxCellSize; i++) {
            XSSFCell titleRowCell = titleRow.createCell(i);
            titleRowCell.setCellStyle(cellStyle);
            XSSFCell headFirstRowwCell = headFirstRow.createCell(i);
            headFirstRowwCell.setCellStyle(cellStyle);
            XSSFCell headSecondRowCell = headSecondRow.createCell(i);
            headSecondRowCell.setCellStyle(cellStyle);
            XSSFCell departmentRowCell = departmentRow.createCell(i);
            departmentRowCell.setCellStyle(leftCellStyle);
            XSSFCell transitConfigRowCell = transitConfigRow.createCell(i);
            transitConfigRowCell.setCellStyle(leftCellStyle);
            if (i == 0) {
                titleRowCell.setCellValue("医院废弃物转移联单");
                headFirstRowwCell.setCellValue("日期");
                departmentRowCell.setCellValue("医疗卫生机构名称：" + vo.getDepartmentName());
                transitConfigRowCell.setCellValue("医院废弃物运输单位：" + vo.getTransitConfigName());
            } else if (i == maxCellSize - 3) {
                headFirstRowwCell.setCellValue("医疗机构人员签字");
            } else if (i == maxCellSize - 2) {
                headFirstRowwCell.setCellValue("运输人员签字");
            } else if (i == maxCellSize - 1) {
                headFirstRowwCell.setCellValue("交接时间");
                transitConfigRowCell.setCellStyle(rightCellStyle);
                transitConfigRowCell.setCellValue("时间：" + DateUtil.format(dto.getStartTime(), "yyyy年MM月dd日") + "至" + DateUtil.format(dto.getEndTime(), "yyyy年MM月dd日"));
            } else {
                if (i % 2 == 1) {
                    headFirstRowwCell.setCellValue(vo.getLegend().get(legendIndex));
                    legendIndex++;
                    //
                    headSecondRowCell.setCellValue("数量");
                } else {
                    headSecondRowCell.setCellValue("重量(KG)");
                }
            }
        }
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, maxCellSize - 1));
        //
        sheet.addMergedRegion(new CellRangeAddress(3, 4, 0, 0));
        for (int i = 0; i < vo.getLegend().size(); i++) {
            sheet.addMergedRegion(new CellRangeAddress(3, 3, 1 + i * 2, 2 + i * 2));
        }
        sheet.addMergedRegion(new CellRangeAddress(3, 4, 1 + vo.getLegend().size() * 2, 1 + vo.getLegend().size() * 2));
        sheet.addMergedRegion(new CellRangeAddress(3, 4, 2 + vo.getLegend().size() * 2, 2 + vo.getLegend().size() * 2));
        sheet.addMergedRegion(new CellRangeAddress(3, 4, 3 + vo.getLegend().size() * 2, 3 + vo.getLegend().size() * 2));
        //
        if (CollectionUtils.isNotEmpty(vo.getList())) {
            for (int i = 0; i < vo.getList().size(); i++) {
                WasteTransformItemVO itemVO = vo.getList().get(i);
                XSSFRow dataRow = sheet.createRow(i + 5);
                int dataIndex = 0;
                for (int k = 0; k < maxCellSize; k++) {
                    XSSFCell dataRowCell = dataRow.createCell(k);
                    dataRowCell.setCellStyle(cellStyle);
                    if (k == 0) {
                        dataRowCell.setCellValue(itemVO.getDate());
                    } else if (k == maxCellSize - 3) {
                        dataRowCell.setCellValue(itemVO.getDepartmentUserName());
                    } else if (k == maxCellSize - 2) {
                        dataRowCell.setCellValue(itemVO.getEngineDriver());
                    } else if (k == maxCellSize - 1) {
                        dataRowCell.setCellValue(DateUtil.format(itemVO.getCheckoutTime(), "yyyy-MM-dd HH:mm:ss"));
                    } else {
                        dataRowCell.setCellValue(itemVO.getData().get(dataIndex).toString());
                        dataIndex++;
                    }
                }
            }
        }
        sheet.setColumnWidth(0, 6065);
        sheet.setColumnWidth(1 + vo.getLegend().size() * 2, 6065);
        sheet.setColumnWidth(2 + vo.getLegend().size() * 2, 6065);
        sheet.setColumnWidth(3 + vo.getLegend().size() * 2, 6065);
        return workbook;
    }

    /**
     * 导出科室、医院、区域
     *
     * @param dto
     * @return
     */
    private XSSFWorkbook exportDepartmentReport(ReportDTO dto, int type, String sheetName) {

        DepartmentReportVO vo = null;

        XSSFWorkbook workbook = new XSSFWorkbook();
        if(dto.getDepartmentIds()==null || dto.getDepartmentIds().equals("")){
            String cellName = "", title = "";
            if (type == 2) {
                vo = getOfficeReportList(dto);
                cellName = "科室名称";
                title = vo.getDepartmentName() + "医疗废物报表";
            } else if (type == 3) {
                vo = getHospitalReportList(dto);
                cellName = "医院名称";
                title = vo.getDepartmentName() + "医疗废物报表";
            } else if (type == 4) {
                vo = getRegionReportList(dto);
                cellName = "区域名称";
                title = vo.getDepartmentName() + "医疗废物报表";
            }else if (type == 5) {
                vo = getOfficeReportList1(dto);
                cellName = "日期";
                title = vo.getDepartmentName() + "医疗废物报表";
            }else if (type == 7) {
                vo = getOfficeReportList1(dto);
                cellName = "日期";
                title = vo.getDepartmentName() + "产废月报表";
            }else if (type == 9) {
                vo = getMedicalWasteReportList(dto);
                cellName = "日期";
                title = vo.getDepartmentName() + "医院产废报表";
            }
            XSSFSheet sheet = workbook.createSheet(sheetName);
            XSSFCellStyle cellStyle = createDefaultCellStyle(workbook);
            XSSFCellStyle rightCellStyle = createDefaultRightCellStyle(workbook);
            //
            int maxCellSize;
            if (type == 2) {
                maxCellSize = 1 + (vo.getLegend().size() - 1) * 2 + 1; // -1 for 单日产废床位比, +1 for name column
            } else {
                maxCellSize = 1 + vo.getLegend().size() * 2;
            }
            XSSFRow titleRow = sheet.createRow(0);
            XSSFRow dateRow = sheet.createRow(1);
            XSSFRow firstRow = sheet.createRow(2);
            XSSFRow secondRow = sheet.createRow(3);
            int legendIndex = 0;
            for (int i = 0; i < maxCellSize; i++) {
                XSSFCell titleRowCell = titleRow.createCell(i);
                titleRowCell.setCellStyle(cellStyle);
                XSSFCell firstRowCell = firstRow.createCell(i);
                firstRowCell.setCellStyle(cellStyle);
                XSSFCell secondRowCell = secondRow.createCell(i);
                secondRowCell.setCellStyle(cellStyle);
                XSSFCell dateRowCell = dateRow.createCell(i);
                dateRowCell.setCellStyle(rightCellStyle);
                if (i == 0) {
                    titleRowCell.setCellValue(title);
                    firstRowCell.setCellValue(cellName);
                } else {
                    if (i % 2 == 1) {
                        firstRowCell.setCellValue(vo.getLegend().get(legendIndex));
                        legendIndex++;
                        //
                        //科室产废统计逻辑
                        if (type == 2) {
                            if (legendIndex == vo.getLegend().size()) {
                                // 最后一个是单日产废床位比
                                secondRowCell.setCellValue("KG/床位/天");
                            } else {
                                secondRowCell.setCellValue("数量");
                            }
                        } else {
                            secondRowCell.setCellValue("数量");
                        }
                    } else {
                        secondRowCell.setCellValue("重量(KG)");
                    }
                }
                if (i == maxCellSize - 1) {
                    dateRowCell.setCellValue("时间：" + DateUtil.format(dto.getStartTime(), "yyyy年MM月dd日") + "至" + DateUtil.format(dto.getEndTime(), "yyyy年MM月dd日"));
                }
            }
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, maxCellSize - 1));
            sheet.addMergedRegion(new CellRangeAddress(2, 3, 0, 0));
            // 合并医废类型列（除了单日产废床位比）
            if (type == 2) {
                for (int i = 0; i < vo.getLegend().size() - 1; i++) {
                    sheet.addMergedRegion(new CellRangeAddress(2, 2, 1 + i * 2, 2 + i * 2));
                }
                // 合并单日产废床位比列
                sheet.addMergedRegion(new CellRangeAddress(2, 3, maxCellSize - 1, maxCellSize - 1));
            }else {
                for (int i = 0; i < vo.getLegend().size(); i++) {
                    sheet.addMergedRegion(new CellRangeAddress(2, 2, 1 + i * 2, 2 + i * 2));
                }
            }
            //
            if (CollectionUtils.isNotEmpty(vo.getList())) {
                for (int i = 0; i < vo.getList().size(); i++) {
                    DepartmentReportItemVO itemVO = vo.getList().get(i);
                    XSSFRow dataRow = sheet.createRow(i + 4);
                    int dataIndex = 0;
                    for (int k = 0; k < maxCellSize; k++) {
                        XSSFCell dataRowCell = dataRow.createCell(k);
                        dataRowCell.setCellStyle(cellStyle);
                        if (k == 0) {
                            dataRowCell.setCellValue(itemVO.getName());
                        } else {
                            dataRowCell.setCellValue(itemVO.getData().get(dataIndex).toString());
                            dataIndex++;
                        }
                    }
                }
            }
            sheet.setColumnWidth(0, 6065);
        }else{
            String[] ids = dto.getDepartmentIds().split(",");
            for(String id:ids){
                dto.setDepartmentId(Integer.parseInt(id));
                String cellName = "", title = "";
                if (type == 2) {
                    vo = getOfficeReportList(dto);
                    cellName = "科室名称";
                    title = vo.getDepartmentName() + "医疗废物报表";
                } else if (type == 3) {
                    vo = getHospitalReportList(dto);
                    cellName = "医院名称";
                    title = vo.getDepartmentName() + "医疗废物报表";
                } else if (type == 4) {
                    vo = getRegionReportList(dto);
                    cellName = "区域名称";
                    title = vo.getDepartmentName() + "医疗废物报表";
                }else if (type == 5) {
                    vo = getOfficeReportList1(dto);
                    cellName = "日期";
                    title = vo.getDepartmentName() + "医疗废物报表";
                }else if (type == 7) {
                    vo = getOfficeReportList1(dto);
                    cellName = "日期";
                    title = vo.getDepartmentName() + "产废月报表";
                }else if (type == 9) {
                    vo = getMedicalWasteReportList(dto);
                    cellName = "日期";
                    title = vo.getDepartmentName() + "医院产废报表";
                }
                //将departName中的 * / : ? [ \\ ] \ 替换成 -
               String departmentName = vo.getDepartmentName().replaceAll("[*/:?\\[\\\\\\]]", "-");
                XSSFSheet sheet = workbook.createSheet(departmentName+sheetName);
                XSSFCellStyle cellStyle = createDefaultCellStyle(workbook);
                XSSFCellStyle rightCellStyle = createDefaultRightCellStyle(workbook);
                //
                int maxCellSize = 1 + vo.getLegend().size() * 2;
                XSSFRow titleRow = sheet.createRow(0);
                XSSFRow dateRow = sheet.createRow(1);
                XSSFRow firstRow = sheet.createRow(2);
                XSSFRow secondRow = sheet.createRow(3);
                int legendIndex = 0;
                for (int i = 0; i < maxCellSize; i++) {
                    XSSFCell titleRowCell = titleRow.createCell(i);
                    titleRowCell.setCellStyle(cellStyle);
                    XSSFCell firstRowCell = firstRow.createCell(i);
                    firstRowCell.setCellStyle(cellStyle);
                    XSSFCell secondRowCell = secondRow.createCell(i);
                    secondRowCell.setCellStyle(cellStyle);
                    XSSFCell dateRowCell = dateRow.createCell(i);
                    dateRowCell.setCellStyle(rightCellStyle);
                    if (i == 0) {
                        titleRowCell.setCellValue(title);
                        firstRowCell.setCellValue(cellName);
                    } else {
                        if (i % 2 == 1) {
                            firstRowCell.setCellValue(vo.getLegend().get(legendIndex));
                            legendIndex++;
                            //
                            secondRowCell.setCellValue("数量");
                        } else {
                            secondRowCell.setCellValue("重量(KG)");
                        }
                    }
                    if (i == maxCellSize - 1) {
                        dateRowCell.setCellValue("时间：" + DateUtil.format(dto.getStartTime(), "yyyy年MM月dd日") + "至" + DateUtil.format(dto.getEndTime(), "yyyy年MM月dd日"));
                    }
                }
                sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, maxCellSize - 1));
                sheet.addMergedRegion(new CellRangeAddress(2, 3, 0, 0));
                for (int i = 0; i < vo.getLegend().size(); i++) {
                    sheet.addMergedRegion(new CellRangeAddress(2, 2, 1 + i * 2, 2 + i * 2));
                }
                //
                if (CollectionUtils.isNotEmpty(vo.getList())) {
                    for (int i = 0; i < vo.getList().size(); i++) {
                        DepartmentReportItemVO itemVO = vo.getList().get(i);
                        XSSFRow dataRow = sheet.createRow(i + 4);
                        int dataIndex = 0;
                        for (int k = 0; k < maxCellSize; k++) {
                            XSSFCell dataRowCell = dataRow.createCell(k);
                            dataRowCell.setCellStyle(cellStyle);
                            if (k == 0) {
                                dataRowCell.setCellValue(itemVO.getName());
                            } else {
                                dataRowCell.setCellValue(itemVO.getData().get(dataIndex).toString());
                                dataIndex++;
                            }
                        }
                    }
                }
                sheet.setColumnWidth(0, 6065);
            }
        }

        return workbook;
    }


    /**
     * 导出科室、医院、区域
     *
     * @param dto
     * @return
     */
    private XSSFWorkbook exportDepartmentReport1(ReportDTO dto, int type, String sheetName) {

        XSSFWorkbook workbook = new XSSFWorkbook();
        DepartmentReportVO vo = null;
        if(dto.getDepartmentIds()==null || dto.getDepartmentIds().equals("")){
            String cellName = "", title = "";
            if (type == 6) {//邢台市
                dto.setStyleTpl(2);
                vo = getOfficeReportList1(dto);
                cellName = "日期";
                title = "医疗废物电子交接登记表（村级）";
            }
            XSSFSheet sheet = workbook.createSheet(sheetName);
            sheet.getPrintSetup().setLandscape(false);
            sheet.setAutobreaks(true);
            XSSFCellStyle cellStyle = createDefaultCellStyle(workbook);
            cellStyle.setWrapText(true);
            XSSFFont font = workbook.createFont();
            font.setFontName("宋体");
            font.setFontHeightInPoints((short) 11);
            cellStyle.setFont(font);
            //
            int maxCellSize = 3 + vo.getLegend().size();
            XSSFRow titleRow = sheet.createRow(0);
            XSSFRow dateRow = sheet.createRow(1);
            XSSFRow firstRow = sheet.createRow(2);
            XSSFRow secondRow = sheet.createRow(3);
            int legendIndex = 0;
            for (int i = 0; i < maxCellSize; i++) {
                XSSFCell titleRowCell = titleRow.createCell(i);
                titleRowCell.setCellStyle(cellStyle);

                XSSFCell dateRowCell = dateRow.createCell(i);
                dateRowCell.setCellStyle(cellStyle);
                cellStyle.setBorderBottom(BorderStyle.THIN); //下边框

                cellStyle.setBorderLeft(BorderStyle.THIN);//左边框

                cellStyle.setBorderTop(BorderStyle.THIN);//上边框

                cellStyle.setBorderRight(BorderStyle.THIN);//右边框
                XSSFCell firstRowCell = firstRow.createCell(i);
                firstRowCell.setCellStyle(cellStyle);
                XSSFCell secondRowCell = secondRow.createCell(i);
                secondRowCell.setCellStyle(cellStyle);
                if (i == 0) {
                    titleRowCell.setCellValue(title);
                    firstRowCell.setCellValue(cellName);
                    dateRowCell.setCellValue("邢台市"+vo.getCityName()+""+vo.getCountyName()+"  卫生机构名称："+vo.getDepartmentName());
                } else if(i<1 + vo.getLegend().size()){
                    firstRowCell.setCellValue(vo.getLegend().get(legendIndex));
                    legendIndex++;
                    //
                    secondRowCell.setCellValue("重量(KG)");
                }else if(i==1 + vo.getLegend().size()){
                    firstRowCell.setCellValue("医疗机构负责人签名");
                }else if(i==2 + vo.getLegend().size()){
                    firstRowCell.setCellValue("医疗废物收运人员");
                }
            /*if (i == maxCellSize - 1) {
                dateRowCell.setCellValue("时间：" + DateUtil.format(dto.getStartTime(), "yyyy年MM月dd日") + "至" + DateUtil.format(dto.getEndTime(), "yyyy年MM月dd日"));
            }*/

            }
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, maxCellSize - 1));
            sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, maxCellSize - 1));

            sheet.addMergedRegion(new CellRangeAddress(2, 3, 0, 0));
            sheet.addMergedRegion(new CellRangeAddress(2, 3, vo.getLegend().size()+1, vo.getLegend().size()+1));
            sheet.addMergedRegion(new CellRangeAddress(2, 3, vo.getLegend().size()+2, vo.getLegend().size()+2));

            //
            if (CollectionUtils.isNotEmpty(vo.getList())) {
                for (int i = 0; i < vo.getList().size(); i++) {
                    DepartmentReportItemVO itemVO = vo.getList().get(i);
                    XSSFRow dataRow = sheet.createRow(i + 4);
                    int dataIndex = 0;
                    for (int k = 0; k < maxCellSize; k++) {
                        XSSFCell dataRowCell = dataRow.createCell(k);

                        cellStyle.setBorderBottom(BorderStyle.THIN); //下边框

                        cellStyle.setBorderLeft(BorderStyle.THIN);//左边框

                        cellStyle.setBorderTop(BorderStyle.THIN);//上边框

                        cellStyle.setBorderRight(BorderStyle.THIN);//右边框
                        dataRowCell.setCellStyle(cellStyle);
                        if (k == 0) {
                            dataRowCell.setCellValue(itemVO.getName());
                        } else if(k<1 + vo.getLegend().size()){
                            dataRowCell.setCellValue(itemVO.getData().get(2*k-1).toString());
                            dataIndex++;
                        }else if(k==1 + vo.getLegend().size()){
                            dataRowCell.setCellValue(itemVO.getDepartmentUserName());
                        }else{
                            dataRowCell.setCellValue(itemVO.getCreator());
                        }
                    }
                }
            }
            XSSFRow dataRow = sheet.createRow(vo.getList().size()+4);
            XSSFCell dataRowCell = dataRow.createCell(0);
            dataRowCell.setCellValue("医疗废物处置单位：邢台征海环保科技有限公司（盖章生效）   服务投诉电话：0319-7229199");

            dataRow = sheet.createRow(vo.getList().size()+5);
            dataRowCell = dataRow.createCell(0);
            dataRowCell.setCellValue("收运人员签字：                        产废单位负责人签字：       ");
            sheet.setColumnWidth(0, (int)((13 + 0.72) * 256));
            sheet.setColumnWidth(7, 2800);
        }else{
            String[] ids = dto.getDepartmentIds().split(",");
            for(String id:ids){
                dto.setDepartmentId(Integer.parseInt(id));
                String cellName = "", title = "";
                if (type == 8) {
                    dto.setStyleTpl(2);
                    vo = getOfficeReportList1(dto);
                    cellName = "日期";
                    title = "医疗废物电子交接登记表（村级）";
                }
                XSSFSheet sheet = workbook.createSheet(vo.getDepartmentName()+sheetName);
                sheet.getPrintSetup().setLandscape(false);
                XSSFCellStyle cellStyle = createDefaultCellStyle(workbook);
                cellStyle.setWrapText(true);
                XSSFFont font = workbook.createFont();
                font.setFontName("宋体");
                font.setFontHeightInPoints((short) 11);
                cellStyle.setFont(font);
                sheet.setAutobreaks(true);
                //
                int maxCellSize = 3 + vo.getLegend().size();
                XSSFRow titleRow = sheet.createRow(0);
                XSSFRow dateRow = sheet.createRow(1);
                XSSFRow firstRow = sheet.createRow(2);
                XSSFRow secondRow = sheet.createRow(3);
                int legendIndex = 0;
                for (int i = 0; i < maxCellSize; i++) {
                    XSSFCell titleRowCell = titleRow.createCell(i);
                    titleRowCell.setCellStyle(cellStyle);

                    XSSFCell dateRowCell = dateRow.createCell(i);
                    dateRowCell.setCellStyle(cellStyle);
                    cellStyle.setBorderBottom(BorderStyle.THIN); //下边框

                    cellStyle.setBorderLeft(BorderStyle.THIN);//左边框

                    cellStyle.setBorderTop(BorderStyle.THIN);//上边框

                    cellStyle.setBorderRight(BorderStyle.THIN);//右边框
                    XSSFCell firstRowCell = firstRow.createCell(i);
                    firstRowCell.setCellStyle(cellStyle);
                    XSSFCell secondRowCell = secondRow.createCell(i);
                    secondRowCell.setCellStyle(cellStyle);
                    if (i == 0) {
                        titleRowCell.setCellValue(title);
                        firstRowCell.setCellValue(cellName);
                        dateRowCell.setCellValue("邢台市"+vo.getCityName()+""+vo.getCountyName()+"  卫生机构名称："+vo.getDepartmentName());
                    } else if(i<1 + vo.getLegend().size()){
                        firstRowCell.setCellValue(vo.getLegend().get(legendIndex));
                        legendIndex++;
                        //
                        secondRowCell.setCellValue("重量(KG)");
                    }else if(i==1 + vo.getLegend().size()){
                        firstRowCell.setCellValue("医疗机构负责人签名");
                    }else if(i==2 + vo.getLegend().size()){
                        firstRowCell.setCellValue("医疗废物收运人员");
                    }
            /*if (i == maxCellSize - 1) {
                dateRowCell.setCellValue("时间：" + DateUtil.format(dto.getStartTime(), "yyyy年MM月dd日") + "至" + DateUtil.format(dto.getEndTime(), "yyyy年MM月dd日"));
            }*/

                }
                sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, maxCellSize - 1));
                sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, maxCellSize - 1));
                sheet.addMergedRegion(new CellRangeAddress(2, 3, 0, 0));
                sheet.addMergedRegion(new CellRangeAddress(2, 3, vo.getLegend().size()+1, vo.getLegend().size()+1));
                sheet.addMergedRegion(new CellRangeAddress(2, 3, vo.getLegend().size()+2, vo.getLegend().size()+2));
                //
                if (CollectionUtils.isNotEmpty(vo.getList())) {
                    for (int i = 0; i < vo.getList().size(); i++) {
                        DepartmentReportItemVO itemVO = vo.getList().get(i);
                        XSSFRow dataRow = sheet.createRow(i + 4);
                        int dataIndex = 0;
                        for (int k = 0; k < maxCellSize; k++) {
                            XSSFCell dataRowCell = dataRow.createCell(k);
                            cellStyle.setBorderBottom(BorderStyle.THIN); //下边框

                            cellStyle.setBorderLeft(BorderStyle.THIN);//左边框

                            cellStyle.setBorderTop(BorderStyle.THIN);//上边框

                            cellStyle.setBorderRight(BorderStyle.THIN);//右边框
                            dataRowCell.setCellStyle(cellStyle);
                            if (k == 0) {
                                dataRowCell.setCellValue(itemVO.getName());
                            } else if(k<1 + vo.getLegend().size()){
                                dataRowCell.setCellValue(itemVO.getData().get(2*k-1).toString());
                                dataIndex++;
                            }else if(k==1 + vo.getLegend().size()){
                                dataRowCell.setCellValue(itemVO.getDepartmentUserName());
                            }else{
                                dataRowCell.setCellValue(itemVO.getCreator());
                            }
                        }
                    }
                }
                XSSFRow dataRow = sheet.createRow(vo.getList().size()+4);
                XSSFCell dataRowCell = dataRow.createCell(0);
                dataRowCell.setCellValue("医疗废物处置单位：邢台征海环保科技有限公司（盖章生效）   服务投诉电话：0319-7229199");

                dataRow = sheet.createRow(vo.getList().size()+5);
                dataRowCell = dataRow.createCell(0);
                dataRowCell.setCellValue("收运人员签字：                        产废单位负责人签字：       ");
                sheet.setColumnWidth(0, (int)((13 + 0.72) * 256));
                sheet.setColumnWidth(7, 2800);
            }
        }

        return workbook;
    }

    /**
     * 计算有效天数（有产废记录的天数）
     * @param collectRecordList 收集记录列表
     * @return 有效天数
     */
    private Integer calculateEffectiveDays(List<MwCollectRecord> collectRecordList) {
        if (CollectionUtils.isEmpty(collectRecordList)) {
            return 0;
        }
        
        // 按日期分组，统计有产废记录的天数
        return collectRecordList.stream()
                .collect(Collectors.groupingBy(
                    record -> DateUtil.format(record.getCreateTime(), "yyyy-MM-dd"),
                    Collectors.counting()
                ))
                .size();
    }

    /**
     * 计算单日产废床位比
     * @param totalWeight 总重量
     * @param bedCount 床位数
     * @param days 天数
     * @return 单日产废床位比
     */
    private BigDecimal calculateDailyWasteBedRatio(BigDecimal totalWeight, Integer bedCount, Integer days) {
        if (bedCount == null || bedCount == 0 || days == null || days == 0) {
            return BigDecimal.ZERO;
        }
        
        // 计算单日产废床位比 = 总重量 / (床位数 * 天数)
        BigDecimal dailyWeight = totalWeight.divide(new BigDecimal(days), 4, BigDecimal.ROUND_HALF_UP);
        return dailyWeight.divide(new BigDecimal(bedCount), 4, BigDecimal.ROUND_HALF_UP);
    }

    /**
     * 计算平均单日产废床位比
     * 先计算每一天的单日产废床位比，再求平均值
     * @param mwCollectRecordList 收集记录列表
     * @param officeList 科室列表
     * @param days 统计天数
     * @return 平均单日产废床位比
     */
    private BigDecimal calculateAverageDailyWasteBedRatio(List<MwCollectRecord> mwCollectRecordList, List<SysDepartment> officeList, Integer days) {
        if (CollectionUtils.isEmpty(mwCollectRecordList) || CollectionUtils.isEmpty(officeList) || days == null || days == 0) {
            return BigDecimal.ZERO;
        }

        // 按日期分组收集记录
        Map<String, List<MwCollectRecord>> dailyRecords = mwCollectRecordList.stream()
                .collect(Collectors.groupingBy(record -> DateUtil.format(record.getCreateTime(), "yyyy-MM-dd")));

        // 计算每一天的单日产废床位比总和
        BigDecimal dailyRatioSum = BigDecimal.ZERO;
        int validDaysCount = 0;

        for (Map.Entry<String, List<MwCollectRecord>> entry : dailyRecords.entrySet()) {
            String date = entry.getKey();
            List<MwCollectRecord> dayRecords = entry.getValue();
            
            // 计算当天总重量
            BigDecimal dayWeight = dayRecords.stream()
                    .map(MwCollectRecord::getWeight)
                    .reduce(BigDecimal.ZERO, BigDecimal::add);
            
            // 计算当天的总床位数（只计算有产废记录的科室）
            Set<Integer> dayOfficeIds = dayRecords.stream()
                    .map(MwCollectRecord::getDepartmentId)
                    .collect(Collectors.toSet());
            
            Integer dayBedCount = officeList.stream()
                    .filter(office -> dayOfficeIds.contains(office.getId()) && office.getBedCount() != null && office.getBedCount() > 0)
                    .mapToInt(SysDepartment::getBedCount)
                    .sum();
            
            // 计算当天的单日产废床位比
            if (dayBedCount > 0) {
                BigDecimal dayRatio = calculateDailyWasteBedRatio(dayWeight, dayBedCount, 1);
                dailyRatioSum = dailyRatioSum.add(dayRatio);
                validDaysCount++;
            }
        }

        // 计算平均值
        if (validDaysCount > 0) {
            return dailyRatioSum.divide(new BigDecimal(validDaysCount), 4, BigDecimal.ROUND_HALF_UP);
        } else {
            return BigDecimal.ZERO;
        }
    }

    /**
     * 根据统计类型计算天数
     * @param dto 查询参数
     * @return 天数
     */
    private Integer calculateDays(ReportDTO dto) {
        if (dto.getDateType() == null) {
            // 默认按日期范围计算天数
            long diffInMillies = dto.getEndTime().getTime() - dto.getStartTime().getTime();
            return (int) (diffInMillies / (24 * 60 * 60 * 1000)) + 1;
        }
        
        switch (dto.getDateType()) {
            case 1: // 日期
                long diffInMillies = dto.getEndTime().getTime() - dto.getStartTime().getTime();
                return (int) (diffInMillies / (24 * 60 * 60 * 1000)) + 1;
            case 2: // 月度
                return DateUtil.getDaysBetweenDates(dto.getStartTime(), dto.getEndTime());
            case 3: // 季度
                return DateUtil.getDaysBetweenDates(dto.getStartTime(), dto.getEndTime());
            case 4: // 年度
                return DateUtil.getDaysBetweenDates(dto.getStartTime(), dto.getEndTime());
            default:
                long defaultDiffInMillies = dto.getEndTime().getTime() - dto.getStartTime().getTime();
                return (int) (defaultDiffInMillies / (24 * 60 * 60 * 1000)) + 1;
        }
    }

    //医废报表统计
    public DepartmentReportVO getMedicalWasteReportList(ReportDTO dto) {

        SysDepartment hospital = sysDepartmentService.getById(dto.getDepartmentId());
        DepartmentReportVO vo = new DepartmentReportVO();
        vo.setDepartmentName(hospital.getDepartName());

        // 根据统计分类获取对应的医废类型名称列表
        List<String> wasteTypeStrList = MedicalWasteEnum.WASTE_TYPE_CATEGORY_MAP.get(dto.getWasteType());
        if (CollectionUtils.isEmpty(wasteTypeStrList)) {
            throw new BusinessException(ErrorCode.ERR_500, "无效的统计分类");
        }

        List<SysDictItem> wasteTypeList = getWasteTypeList(null);
        if (CollectionUtils.isEmpty(wasteTypeList)) {
            return vo;
        }
        // 过滤字典类型列表，只保留对应分类的字典项
        wasteTypeList = wasteTypeList.stream()
                .filter(item -> wasteTypeStrList.contains(item.getItemText()))
                .collect(Collectors.toList());
        if (CollectionUtils.isEmpty(wasteTypeList)) {
            return vo;
        }

        vo.setLegend(wasteTypeList.stream().map(SysDictItem::getItemText).collect(Collectors.toList()));
        vo.getLegend().add("小计");
        if(dto.getEndTime().getTime()>new Date().getTime()){
            dto.setEndTime(new Date());
        }
        LambdaQueryWrapper<MwCollectRecord> queryWrapper = new QueryWrapper<MwCollectRecord>()//
                .lambda()//
                .between(MwCollectRecord::getCreateTime, DateUtil.format(dto.getStartTime(), "yyyy-MM-dd HH:mm:ss"), DateUtil.format(dto.getEndTime(), "yyyy-MM-dd HH:mm:ss"))//
                .eq(MwCollectRecord::getHospitalId, dto.getDepartmentId())//
                .in(MwCollectRecord::getWasteTypeStr, wasteTypeStrList)// 根据统计分类过滤医废类型
                .eq(MwCollectRecord::getDelFlag, 0)//
                .eq(MwCollectRecord::getAuditStatus, MedicalWasteEnum.MedicalWasteAuditStatusEnum.PASS.getStatus());
        if(dto.getTypeTpl()==1){
            queryWrapper.in(MwCollectRecord::getStatus, 1,2,3);//收集
        }else if(dto.getTypeTpl()==2){
            queryWrapper.in(MwCollectRecord::getStatus, 2,3);//入库
        }else{
            queryWrapper.in(MwCollectRecord::getStatus, 3);//出库
        }
        List<MwCollectRecord> mwCollectRecordList = mwCollectRecordService.list(queryWrapper);
        for(MwCollectRecord mwCollectRecord:mwCollectRecordList){
            mwCollectRecord.setCreateTimes(DateUtil.format(mwCollectRecord.getCreateTime(), "yyyy-MM-dd"));
        }
        //
        List<DepartmentReportItemVO> voList = buildDepartmentStatementItemVOList(wasteTypeList, mwCollectRecordList, dto);
        vo.setList(voList);
        return vo;
    }


    private List<DepartmentReportItemVO> buildDepartmentStatementItemVOList(
            List<SysDictItem> wasteTypeList,
            List<MwCollectRecord> mwCollectRecordList,
            ReportDTO dto) {
        List<DepartmentReportItemVO> voList =  new ArrayList<>();
        //日期数组
        SimpleDateFormat sdf = new SimpleDateFormat(DEFAULT_PATTERN);
        List<String> dateList = DateUtil.getMonthBetween(sdf.format(dto.getStartTime()),sdf.format(dto.getEndTime()),dto.getDateType());
        SimpleDateFormat sdfWayType = new SimpleDateFormat(YYYY_MM_DD);
        if(dto.getDateType() == 4){//年
            sdfWayType = new SimpleDateFormat(yyyy);
        }else if(dto.getDateType() == 2 || dto.getDateType() == 3){//月
            sdfWayType = new SimpleDateFormat(yyyy_MM);
        }
        for(String date : dateList){
            DepartmentReportItemVO itemVO = new DepartmentReportItemVO();
            itemVO.setKey(null);
            itemVO.setName(null);
            itemVO.setName(date);
            itemVO.setData(new ArrayList<>());
            SimpleDateFormat finalSdfWayType = sdfWayType;
            BigDecimal totalCount = BigDecimal.ZERO;
            BigDecimal totalWeight = BigDecimal.ZERO;
            for (SysDictItem sysDictItem : wasteTypeList) {
                BigDecimal weight = mwCollectRecordList.stream().filter(e -> e.getWasteType().equals(sysDictItem.getId())
                        && finalSdfWayType.format(e.getCreateTime()).equals(date)
                ).map(MwCollectRecord::getWeight).reduce(BigDecimal::add).orElse(BigDecimal.ZERO).setScale(2, BigDecimal.ROUND_HALF_UP);
                BigDecimal count = new BigDecimal(mwCollectRecordList.stream().filter(e -> e.getWasteType().equals(sysDictItem.getId())
                        && finalSdfWayType.format(e.getCreateTime()).equals(date)).count());
                ;
                itemVO.getData().add(count);
                itemVO.getData().add(weight);
                totalCount = totalCount .add(count);
                totalWeight = totalWeight.add(weight);
            }
            itemVO.getData().add(totalCount);
            itemVO.getData().add(totalWeight);
            voList.add(itemVO);
        }

        //合计
        DepartmentReportItemVO totalItemVO = new DepartmentReportItemVO();
        totalItemVO.setKey(-2);
        totalItemVO.setName("合计");
        totalItemVO.setData(new ArrayList<>());
        BigDecimal totalCount = BigDecimal.ZERO;
        BigDecimal totalWeight = BigDecimal.ZERO;
        for (SysDictItem sysDictItem : wasteTypeList) {
            BigDecimal count = new BigDecimal(mwCollectRecordList.stream().filter(e -> e.getWasteType().equals(sysDictItem.getId())).count());
            BigDecimal weight = mwCollectRecordList.stream().filter(e -> e.getWasteType().equals(sysDictItem.getId())).map(MwCollectRecord::getWeight).reduce(BigDecimal::add).orElse(BigDecimal.ZERO).setScale(2, BigDecimal.ROUND_HALF_UP);
            totalItemVO.getData().add(count);
            totalItemVO.getData().add(weight);
            //
            totalCount = totalCount .add(count);
            totalWeight = totalWeight.add(weight);
        }
        totalItemVO.getData().add(totalCount);
        totalItemVO.getData().add(totalWeight);
        voList.add(0,totalItemVO);
        return voList;
    }

}