package org.jeecg.modules.weekly.service.impl;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.enums.WriteDirectionEnum;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.merge.AbstractMergeStrategy;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
import com.alibaba.excel.write.metadata.fill.FillWrapper;
import com.alibaba.fastjson.JSON;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import lombok.Data;
import org.apache.commons.lang3.Range;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jeecg.common.util.DateUtils;
import org.jeecg.common.util.HolidayUtils;
import org.jeecg.modules.weekly.entity.WekEvaluate;
import org.jeecg.modules.weekly.feign.SystemClient;
import org.jeecg.modules.weekly.feign.model.SysUser;
import org.jeecg.modules.weekly.mapper.WekEvaluateMapper;
import org.jeecg.modules.weekly.service.IWekEvaluateService;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;

import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;

import java.io.*;
import java.time.DayOfWeek;
import java.time.LocalDate;
import java.time.temporal.TemporalAdjusters;
import java.util.*;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.concurrent.atomic.AtomicReference;
import java.util.stream.Collectors;

/**
 * @Description: wek_evaluate
 * @Author: jeecg-boot
 * @Date: 2024-01-08
 * @Version: V1.0
 */
@Service
public class WekEvaluateServiceImpl extends ServiceImpl<WekEvaluateMapper, WekEvaluate> implements IWekEvaluateService {
    @Value(value = "${jeecg.path.upload}")
    private String uploadpath;
    @Autowired
    private SystemClient systemClient;


    @Override
    public Map<String, Object> exportEvaluate(String year, String sysOrgCode, String user, Integer quarter) {
        return exportEvaTemplate(year, sysOrgCode, user, quarter);

    }


    private Map<String, Object> exportEvaTemplate(String year, String sysOrgCode, String queryUser, Integer quarter) {
        Map<String, Object> result = new HashMap<>();

        String templateFileName = uploadpath + File.separator + "exportTemplate" + File.separator + "WeeklyEva.xlsx";
        //TODO  3D事业部使用特定的模板
        if(queryUser == null && sysOrgCode!=null && sysOrgCode.equals("A04A02A01A10")){
            templateFileName =  uploadpath + File.separator + "exportTemplate" + File.separator + "WeeklyEvaCustom.xlsx";
        }
        String filePath = "export" + File.separator + "绩效汇总-" + DateUtils.date2Str(new Date(), DateUtils.yyyymmddhhmmss.get()) + ".xlsx";
        String fileName = uploadpath + File.separator + filePath;
        Integer yearNum = Integer.parseInt(year);
        Map<String, Object> res = createStatisticsYearTitle(yearNum,quarter);
        List<Map<String, Object>> colmuns = new ArrayList<>();
        if (res.containsKey("colmuns")) {
            colmuns = (List<Map<String, Object>>) res.get("colmuns");
        }
        Map<String, Object> config = new HashMap<>();
        if (res.containsKey("config")) {
            config = (Map<String, Object>) res.get("config");
        }
        TreeMap<Integer, Integer> mergeSeason = new TreeMap<>();
        if (res.containsKey("mergeSeason")) {
            mergeSeason = (TreeMap<Integer, Integer>) res.get("mergeSeason");
        }
        TreeMap<Integer, Integer> mergeMonth = new TreeMap<>();
        if (res.containsKey("mergeMonth")) {
            mergeMonth = (TreeMap<Integer, Integer>) res.get("mergeMonth");
        }
        List<Integer> mergeWeek = new ArrayList<>();
        if (res.containsKey("mergeWeek")) {
            mergeWeek = (List<Integer>) res.get("mergeWeek");
        }


        File templateFile = new File(templateFileName);
        try {
            FileInputStream fileInputStream = new FileInputStream(templateFile);
            ByteArrayOutputStream bos = new ByteArrayOutputStream();
            //原模板只有一个sheet，通过poi复制出需要的sheet个数的模板
            XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream);
            //设置模板的第一个sheet的名称
//        workbook.setSheetName(1, basisinfo.get("sheet").toString());
            //写到流里
            workbook.write(bos);
            byte[] bArray = bos.toByteArray();
            InputStream is = new ByteArrayInputStream(bArray);
            //输出文件路径
            ExcelWriter excelWriter = EasyExcel.write(fileName)
                    .registerWriteHandler(new MyHandler(mergeSeason, mergeMonth, mergeWeek))
                    .withTemplate(is).build();
            WriteSheet writeSheet = EasyExcel.writerSheet().build();


            FillConfig horConfig = FillConfig.builder().direction(WriteDirectionEnum.HORIZONTAL).build();
            horConfig.setForceNewRow(true);
            excelWriter.fill(new FillWrapper("colmuns", colmuns), horConfig, writeSheet);


            //构建模版的 序号 部门 项目 职务
            Map<String, Object> basInfo = new HashMap<>();
            basInfo.put("no", "{data.no}");
            basInfo.put("depart", "{data.depart}");
            basInfo.put("name", "{data.name}");
            basInfo.put("post", "{data.post}");
            basInfo.put("year", year);
            excelWriter.fill(basInfo, writeSheet);
            // 关闭流
            excelWriter.finish();

            Map<String, Object> weeklyEvaData = createWeeklyEvaData(colmuns, year, sysOrgCode, queryUser);
            List<Map<String, Object>> data = (List<Map<String, Object>>) weeklyEvaData.get("hz");
            List<Map<String, Object>> subList = (List<Map<String, Object>>) weeklyEvaData.get("gr");
            List<Map<String, Object>> monList = (List<Map<String, Object>>) weeklyEvaData.get("mon");

            //TODO  3D事业部使用特定的模板
            if(queryUser == null && sysOrgCode!=null && sysOrgCode.equals("A04A02A01A10")){
                exportEvaDataCustom(data, subList,monList, fileName);
            }else {
                exportEvaData(data, subList,monList, fileName);
            }


            result.put("size", data == null ? 0 : data.size());
            result.put("filePath", filePath);

        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

    private String exportEvaData(List<Map<String, Object>> data, List<Map<String, Object>> subList, List<Map<String, Object>> monList ,String templateFileName) {
        File templateFile = new File(templateFileName);
        try {
            FileInputStream fileInputStream = new FileInputStream(templateFile);
            ByteArrayOutputStream bos = new ByteArrayOutputStream();
            //原模板只有一个sheet，通过poi复制出需要的sheet个数的模板
            XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream);
            //设置模板的第一个sheet的名称
//        workbook.setSheetName(1, basisinfo.get("sheet").toString());
            //写到流里
            workbook.write(bos);
            byte[] bArray = bos.toByteArray();
            InputStream is = new ByteArrayInputStream(bArray);
            //输出文件路径
            ExcelWriter excelWriter = EasyExcel.write(templateFileName)
                    .withTemplate(is).build();
            WriteSheet writeSheet = EasyExcel.writerSheet().build();

            FillConfig fillConfig = new FillConfig();
            fillConfig.setForceNewRow(true);
            excelWriter.fill(new FillWrapper("data", data), fillConfig, writeSheet);
            // 关闭流
            excelWriter.finish();
            //exportMon(monList, templateFileName);
            exportSub(subList,monList, templateFileName);


        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
        return templateFileName;
    }
    private String exportEvaDataCustom(List<Map<String, Object>> data, List<Map<String, Object>> subList, List<Map<String, Object>> monList ,String templateFileName) {
        File templateFile = new File(templateFileName);
        try {
            FileInputStream fileInputStream = new FileInputStream(templateFile);
            ByteArrayOutputStream bos = new ByteArrayOutputStream();
            //原模板只有一个sheet，通过poi复制出需要的sheet个数的模板
            XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream);
            //设置模板的第一个sheet的名称
//        workbook.setSheetName(1, basisinfo.get("sheet").toString());
            //写到流里
            workbook.write(bos);
            byte[] bArray = bos.toByteArray();
            InputStream is = new ByteArrayInputStream(bArray);
            //输出文件路径
            ExcelWriter excelWriter = EasyExcel.write(templateFileName)
                    .withTemplate(is).build();
            WriteSheet writeSheet = EasyExcel.writerSheet().build();

            FillConfig fillConfig = new FillConfig();
            fillConfig.setForceNewRow(true);
            excelWriter.fill(new FillWrapper("data", data), fillConfig, writeSheet);
            // 关闭流
            excelWriter.finish();
            exportMonCustom(monList, templateFileName);
            exportSubCustom(subList,monList, templateFileName);


        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
        return templateFileName;
    }

    /**
     * 导出个人部分
     *
     * @param subList
     * @param templateFileName
     */
    private void exportSub(List<Map<String, Object>> subList,List<Map<String, Object>> monList, String templateFileName) {
        File templateFile = new File(templateFileName);
        try {
            if (subList == null || subList.size() < 1) {
                return;
            }
            FileInputStream fileInputStream = new FileInputStream(templateFile);
            ByteArrayOutputStream bos = new ByteArrayOutputStream();
            //原模板只有一个sheet，通过poi复制出需要的sheet个数的模板
            XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream);
            //设置模板的第一个sheet的名称
            Map<String, Object> first = subList.get(0);


            int index = workbook.getSheetIndex("个人绩效考核汇总表");
           /* int remove = workbook.getSheetIndex("绩效分析报告");
            if(monList.size()<1){
                workbook.removeSheetAt(remove);
                index-=1;

            }*/

            workbook.setSheetName(index, first.get("name").toString());
            for (int i = 0; i < subList.size(); i++) {
                if (i == 0) continue;
                Map<String, Object> sub = subList.get(i);
                workbook.cloneSheet(index, sub.get("name").toString());

            }
            //写到流里
            workbook.write(bos);
            byte[] bArray = bos.toByteArray();
            InputStream is = new ByteArrayInputStream(bArray);
            //输出文件路径
            ExcelWriter excelWriter = EasyExcel.write(templateFileName)
                    .withTemplate(is).build();

            for (int i = 0; i < subList.size(); i++) {
                Map<String, Object> sub = subList.get(i);
                WriteSheet writeSheet = EasyExcel.writerSheet(sub.get("name").toString())
                        .build();
                excelWriter.fill(sub, writeSheet);
            }
            // 关闭流
            excelWriter.finish();


        } catch (Exception e) {
            e.printStackTrace();

        }

    }
    private void exportMon(List<Map<String, Object>> monList, String templateFileName) {
        File templateFile = new File(templateFileName);
        try {
            if (monList == null || monList.size() < 1) {
                return;
            }
            FileInputStream fileInputStream = new FileInputStream(templateFile);
            ByteArrayOutputStream bos = new ByteArrayOutputStream();
            //原模板只有一个sheet，通过poi复制出需要的sheet个数的模板
            XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream);
            //设置模板的第一个sheet的名称
            Map<String, Object> first = monList.get(0);
            int sheetIndex = workbook.getSheetIndex("绩效分析报告");
            workbook.setSheetName(sheetIndex, first.get("name").toString());
            for (int i = 0; i < monList.size(); i++) {
                if (i == 0) continue;
                Map<String, Object> sub = monList.get(i);
                workbook.cloneSheet(sheetIndex, sub.get("name").toString());

            }
            //写到流里
            workbook.write(bos);
            byte[] bArray = bos.toByteArray();
            InputStream is = new ByteArrayInputStream(bArray);
            //输出文件路径
            ExcelWriter excelWriter = EasyExcel.write(templateFileName)
                    .withTemplate(is).build();

            for (int i = 0; i < monList.size(); i++) {
                Map<String, Object> mon = monList.get(i);
                WriteSheet writeSheet = EasyExcel.writerSheet(mon.get("name").toString())
                        .build();
                excelWriter.fill(mon, writeSheet);
            }
            // 关闭流
            excelWriter.finish();


        } catch (Exception e) {
            e.printStackTrace();

        }

    }


    /**
     * 导出个人部分
     *
     * @param subList
     * @param templateFileName
     */
    private void exportSubCustom(List<Map<String, Object>> subList,List<Map<String, Object>> monList, String templateFileName) {
        File templateFile = new File(templateFileName);
        try {
            if (subList == null || subList.size() < 1) {
                return;
            }
            FileInputStream fileInputStream = new FileInputStream(templateFile);
            ByteArrayOutputStream bos = new ByteArrayOutputStream();
            //原模板只有一个sheet，通过poi复制出需要的sheet个数的模板
            XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream);
            //设置模板的第一个sheet的名称
            Map<String, Object> first = subList.get(0);



       /*     int remove = workbook.getSheetIndex("绩效分析报告");
            if(monList.size()<1){
                workbook.removeSheetAt(remove);
                index-=1;

            }*/
            int index = workbook.getSheetIndex("个人绩效考核汇总表");
            workbook.setSheetName(index, first.get("name").toString());
            for (int i = 0; i < subList.size(); i++) {
                if (i == 0) continue;
                Map<String, Object> sub = subList.get(i);
                workbook.cloneSheet(index, sub.get("name").toString());

            }
            //写到流里
            workbook.write(bos);
            byte[] bArray = bos.toByteArray();
            InputStream is = new ByteArrayInputStream(bArray);
            //输出文件路径
            ExcelWriter excelWriter = EasyExcel.write(templateFileName)
                    .withTemplate(is).build();

            for (int i = 0; i < subList.size(); i++) {
                Map<String, Object> sub = subList.get(i);
                WriteSheet writeSheet = EasyExcel.writerSheet(sub.get("name").toString())
                        .build();
                excelWriter.fill(sub, writeSheet);
            }
            // 关闭流
            excelWriter.finish();


        } catch (Exception e) {
            e.printStackTrace();

        }

    }
    private void exportMonCustom(List<Map<String, Object>> monList, String templateFileName) {
        File templateFile = new File(templateFileName);
        try {
            if (monList == null || monList.size() < 1) {
                return;
            }
            FileInputStream fileInputStream = new FileInputStream(templateFile);
            ByteArrayOutputStream bos = new ByteArrayOutputStream();
            //原模板只有一个sheet，通过poi复制出需要的sheet个数的模板
            XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream);
            //设置模板的第一个sheet的名称
         /*   Map<String, Object> first = monList.get(0);
            int sheetIndex = workbook.getSheetIndex("绩效分析报告");
            workbook.setSheetName(sheetIndex, first.get("name").toString());
            for (int i = 0; i < monList.size(); i++) {
                if (i == 0) continue;
                Map<String, Object> sub = monList.get(i);
                workbook.cloneSheet(sheetIndex, sub.get("name").toString());

            }*/
            String names = monList.stream()
                    .map(map -> map.get("name")) // 获取指定字段的值
                    .filter(Objects::nonNull) // 过滤掉为null的值
                    .map(Object::toString) // 将字段值转换为字符串
                    .collect(Collectors.joining(", ")); // 使用逗号拼接所有字符串

            for (int i = 1; i <=12 ; i++) {
                if(!names.contains(i+"月绩效分析报告")){
                    int sheetIndex = workbook.getSheetIndex(i + "月绩效分析报告");
                    workbook.removeSheetAt(sheetIndex);
                }
            }


            //写到流里
            workbook.write(bos);
            byte[] bArray = bos.toByteArray();
            InputStream is = new ByteArrayInputStream(bArray);
            //输出文件路径
            ExcelWriter excelWriter = EasyExcel.write(templateFileName)
                    .withTemplate(is).build();

            for (int i = 0; i < monList.size(); i++) {
                Map<String, Object> mon = monList.get(i);
                WriteSheet writeSheet = EasyExcel.writerSheet(mon.get("name").toString())
                        .build();
                excelWriter.fill(mon, writeSheet);
            }
            // 关闭流
            excelWriter.finish();


        } catch (Exception e) {
            e.printStackTrace();

        }

    }


    /**
     * 创建绩效数据
     * tips：此处生成key参照weeklyEva模版
     *
     * @param colmuns
     * @return
     */
    private Map<String, Object> createWeeklyEvaData(List<Map<String, Object>> colmuns, String year, String sysOrgCode, String queryUser) {
        Map<String, Object> root = new HashMap<>();
        List<Map<String, Object>> res = new ArrayList<>();
        List<Map<String, Object>> subList = new ArrayList<>();
        List<Map<String, Object>> monthAnalysList = new ArrayList<>();
        List<SysUser> queryUserList = new ArrayList<>();
        if (StringUtils.isNotEmpty(sysOrgCode)) {
            //过滤选择的部门，选择的用户
            List<SysUser> departUserList = systemClient.queryUserByDepCode(sysOrgCode).getResult();
            if (departUserList.size() > 0) {
                queryUserList.addAll(departUserList);
            }
        }
        String userNames = "";
        if (queryUserList.size() > 0) {
            String collect = queryUserList.stream().map(SysUser::getUsername)
                    .collect(Collectors.joining(","));
            userNames = collect;
        }
        if (StringUtils.isNotEmpty(queryUser)) {
            if (StringUtils.isNotEmpty(userNames)) {
                userNames += ",";
            }
            userNames += queryUser;
        }


        //查询考评记录
        QueryWrapper<WekEvaluate> wekEvaluateQueryWrapper = new QueryWrapper<>();
        wekEvaluateQueryWrapper.lambda().eq(WekEvaluate::getYear, year);
        //有查询条件
        if (StringUtils.isNotEmpty(userNames)) {
            wekEvaluateQueryWrapper.lambda().in(WekEvaluate::getZp, Arrays.asList(userNames.split(",")));
        }
        List<WekEvaluate> wekEvaluateList = this.list(wekEvaluateQueryWrapper);

        if (wekEvaluateList.isEmpty()) {
            return root;
        }


        String zpUsersStr = wekEvaluateList.stream().map(WekEvaluate::getZp)
                .collect(Collectors.joining(","));
        List<SysUser> zpUserList = systemClient.queryByNames(zpUsersStr).getResult();

        Map<String, SysUser> userMap = zpUserList.stream()
                .collect(Collectors.toMap(SysUser::getUsername, sysuser -> sysuser));

        //按照个人分组组装数据
        Map<String, List<WekEvaluate>> wekEvaluategroup = wekEvaluateList.stream().filter(item->StringUtils.isNotEmpty(item.getZp())).collect(Collectors.groupingBy(item -> item.getZp()));

        //TODO  没有考评数据周的数据默认补充 C
        patcEvaData(year,wekEvaluategroup, userMap);


        //月绩效分析报告start  TODO //此报表只有视觉研发部才有；必要条件1-视觉研发部(A04A02A01A10)  2-配置部门小组
        if(queryUser == null && sysOrgCode!=null && sysOrgCode.equals("A04A02A01A10")){
            List<Map<String, Object>> monthList = monthAnalys(wekEvaluategroup, userMap);
            monthAnalysList.addAll(monthList);
        }
        //月绩效分析报告end

        AtomicInteger index = new AtomicInteger(1); //数据行数
        wekEvaluategroup.forEach((user, evaluateList) -> {

            Map<String, Object> item = new HashMap<>();
            Map<String, Object> sub = new HashMap<>();
            //汇总表
            item.put("no", index.get()); //序号
            item.put("name", evaluateList.get(0).getZpName()); //姓名
            item.put("depart", userMap.get(user).getOrgCodeTxt());
            item.put("post", userMap.get(user).getPost());
            //个人表
            sub.put("name", evaluateList.get(0).getZpName()); //姓名
            sub.put("year", evaluateList.get(0).getYear()); //年


            //个人周报数据再按月分组
            Map<Integer, List<WekEvaluate>> monEvaMap = evaluateList.stream().collect(Collectors.groupingBy(month -> month.getMonth()));

            TreeMap<Integer, List<Double>> seasonKpScoreMap = new TreeMap<>();
            TreeMap<Integer, List<Double>> seasonZpScoreMap = new TreeMap<>();
            for (int i = 1; i <= 4; i++) {
                seasonKpScoreMap.put(i, new ArrayList<>());
                seasonZpScoreMap.put(i, new ArrayList<>());
            }

            monEvaMap.forEach((mon, monList) -> {
                //考评分
                List<Integer> monthZpScoreList = new ArrayList<>();
                List<Integer> monthKpScoreList = new ArrayList<>();
                //个人周报数据再按周分组
                Map<Integer, List<WekEvaluate>> weekEvaMap = monList.stream().collect(Collectors.groupingBy(week -> week.getWeek()));
                weekEvaMap.forEach((w, wekList) -> {

                    WekEvaluate eva = wekList.get(0);
                    if (eva == null) return;

                    //TODO 重要 此处是year_week 要转成month_week
                    String weekToDayEndStr = DateUtils.weekToDayEndStr(eva.getYear(), eva.getWeek(), "yyyy-MM-dd");
                    Integer weekInMonth = DateUtils.getWeekInMonth(weekToDayEndStr);
                    if (weekInMonth == null) return;

                    String zpPlan = eva.getZpPlan();
                    String zpAmount = eva.getZpAmount();
                    String zpEffe = eva.getZpEffe();
                    String zpQuality = eva.getZpQuality();
                    if (StringUtils.isNotEmpty(zpPlan) && StringUtils.isNotEmpty(zpAmount) && StringUtils.isNotEmpty(zpEffe) && StringUtils.isNotEmpty(zpQuality)) {
                        int score = calcEvaScore(zpPlan, zpAmount, zpEffe, zpQuality);
                        //注意 此处生成key要和excel生成head中colnum一致
                        item.put("w" + eva.getYearWeek() + "_1", score);

                        //个人绩效汇总生成colmun
                        sub.put("m" + mon + "w" + weekInMonth + "_zp1", calScore(zpPlan, 1));
                        sub.put("m" + mon + "w" + weekInMonth + "_zp2", calScore(zpAmount, 1));
                        sub.put("m" + mon + "w" + weekInMonth + "_zp3", calScore(zpEffe, 1));
                        sub.put("m" + mon + "w" + weekInMonth + "_zp4", calScore(zpQuality, 2));

                        monthZpScoreList.add(score);

                    }

                    String kpPlan = eva.getKpPlan();
                    String kpAmount = eva.getKpAmount();
                    String kpEffe = eva.getKpEffe();
                    String kpQuality = eva.getKpQuality();
                    if (StringUtils.isNotEmpty(kpPlan) && StringUtils.isNotEmpty(kpAmount) && StringUtils.isNotEmpty(kpEffe) && StringUtils.isNotEmpty(kpQuality)) {
                        int score = calcEvaScore(kpPlan, kpAmount, kpEffe, kpQuality);
                        //注意 此处生成key要和excel生成head中colnum一致
                        item.put("w" + eva.getYearWeek() + "_2", score);

                        //个人绩效汇总生成colmun
                        sub.put("m" + mon + "w" + weekInMonth + "_kp1", calScore(kpPlan, 1));
                        sub.put("m" + mon + "w" + weekInMonth + "_kp2", calScore(kpAmount, 1));
                        sub.put("m" + mon + "w" + weekInMonth + "_kp3", calScore(kpEffe, 1));
                        sub.put("m" + mon + "w" + weekInMonth + "_kp4", calScore(kpQuality, 2));

                        monthKpScoreList.add(score);
                    }


                });
                //获取考评附加分 TODO
                Optional<WekEvaluate> max = monList.stream().filter(score -> score.getKpExtScore() != null).max(Comparator.comparingDouble(WekEvaluate::getKpExtScore));

                //考核月平均  考评
                double kpScore = monthKpScoreList.stream().mapToDouble(Integer::intValue)
                        .average()
                        .orElse(0);
                String skpScore = String.format("%.1f", kpScore);
                double avg = Double.parseDouble(skpScore);

                if (max.isPresent() && avg > 0) {
                    avg = avg + max.get().getKpExtScore().intValue();

                    sub.put("m" + mon + "kp_ext_score", max.get().getKpExtScore().intValue());
                    sub.put("m" + mon + "kp_ext_desc", max.get().getKpExtDesc());
                }
                if (avg == 0) {
                    item.put("m" + mon + "_1", "");
                    sub.put("m" + mon + "avg_kp", "");
                } else {
                    item.put("m" + mon + "_1", avg);
                    sub.put("m" + mon + "avg_kp", avg);
                }
                //自评
                double zpScore = monthZpScoreList.stream().mapToInt(Integer::intValue)
                        .average()
                        .orElse(0);

                String szpScore = String.format("%.1f", zpScore);
                double zpAvg = Double.parseDouble(szpScore);

                if (zpAvg == 0) {
                    sub.put("m" + mon + "avg_zp", "");
                } else {
                    sub.put("m" + mon + "avg_zp", zpAvg);
                }

                //个人绩效
                //绩效有4项 计划及达成 、工作完成量、工作完成效率、工作完成质量
                for (int i = 1; i <= 4; i++) {
                    List<Integer> subZpList = new ArrayList<>();
                    List<Integer> subKpList = new ArrayList<>();
                    //一个月最多5周
                    for (int j = 1; j <= 5; j++) {
                        //自评
                        if (sub.containsKey("m" + mon + "w" + j + "_zp" + i)) {
                            Integer zp = (Integer) sub.get("m" + mon + "w" + j + "_zp" + i);
                            subZpList.add(zp);
                        }
                        //考评
                        if (sub.containsKey("m" + mon + "w" + j + "_kp" + i)) {
                            Integer kp = (Integer) sub.get("m" + mon + "w" + j + "_kp" + i);
                            subKpList.add(kp);
                        }
                    }
                    //一个月的各项自评分平均值 TODO是否保留小数点
                    Double zp = subZpList.stream().mapToDouble(Integer::intValue)
                            .average()
                            .orElse(0);

                    Double kp = subKpList.stream().mapToDouble(Integer::intValue)
                            .average()
                            .orElse(0);
                    String szp = String.format("%.1f", zp);
                    String skp = String.format("%.1f", kp);
                    zp = Double.parseDouble(szp);
                    kp = Double.parseDouble(skp);

                    if (zp > 0) {
                        sub.put("m" + mon + "avg" + "_zp" + i, zp);
                    } else {
                        sub.put("m" + mon + "avg" + "_zp" + i, "");
                    }

                    if (kp > 0) {
                        sub.put("m" + mon + "avg" + "_kp" + i, kp);
                    } else {
                        sub.put("m" + mon + "avg" + "_kp" + i, "");
                    }
                }


                //月绩效等级
                String monLevel = "C";
                String monPay = "100%";
                if (avg == 0) {
                    monLevel = "";
                    monPay = "";
                } else if (avg >= 95) {
                    monPay = "140%";
                    monLevel = "A";
                } else if (avg <= 95 && avg >= 85) {
                    monLevel = "B";
                    monPay = "110%";
                } else if (avg <= 84 && avg >= 75) {
                    monLevel = "C";
                    monPay = "100%";
                } else if (avg <= 74 && avg >= 60) {
                    monLevel = "D";
                    monPay = "90%";
                } else if (avg <= 59) {
                    monLevel = "E";
                    monPay = "不合格";
                }
                item.put("m" + mon + "_2", monLevel);
                item.put("m" + mon + "_3", monPay);

                //根据季添加考评分
                if (mon <= 3) {
                    seasonZpScoreMap.get(1).add(zpAvg);
                    seasonKpScoreMap.get(1).add(avg);

                    Optional<WekEvaluate> firstEva = monList.stream().filter(i -> i.getKpName() != null).max((i1, i2) -> i1.getKpDate().compareTo(i2.getKpDate()));
                    if (firstEva.isPresent() && StringUtils.isNotEmpty(firstEva.get().getKpName())) {
                        sub.put("s1_kpName", firstEva.get().getKpName());
                    }
                    if (firstEva.isPresent() && firstEva.get().getKpDate() != null) {
                        sub.put("s1_kpDate", DateUtils.date2Str(firstEva.get().getKpDate(), DateUtils.date_sdf.get()));
                    }

                } else if (mon >= 4 && mon <= 6) {
                    seasonZpScoreMap.get(2).add(zpAvg);
                    seasonKpScoreMap.get(2).add(avg);

                    Optional<WekEvaluate> firstEva = monList.stream().filter(i -> i.getKpName() != null).max((i1, i2) -> i1.getKpDate().compareTo(i2.getKpDate()));
                    if (firstEva.isPresent() && StringUtils.isNotEmpty(firstEva.get().getKpName())) {
                        sub.put("s2_kpName", firstEva.get().getKpName());
                    }
                    if (firstEva.isPresent() && firstEva.get().getKpDate() != null) {
                        sub.put("s2_kpDate", DateUtils.date2Str(firstEva.get().getKpDate(), DateUtils.date_sdf.get()));
                    }


                } else if (mon >= 7 && mon <= 9) {
                    seasonZpScoreMap.get(3).add(zpAvg);
                    seasonKpScoreMap.get(3).add(avg);

                    Optional<WekEvaluate> firstEva = monList.stream().filter(i -> i.getKpName() != null).max((i1, i2) -> i1.getKpDate().compareTo(i2.getKpDate()));
                    if (firstEva.isPresent() && StringUtils.isNotEmpty(firstEva.get().getKpName())) {
                        sub.put("s3_kpName", firstEva.get().getKpName());
                    }
                    if (firstEva.isPresent() && firstEva.get().getKpDate() != null) {
                        sub.put("s3_kpDate", DateUtils.date2Str(firstEva.get().getKpDate(), DateUtils.date_sdf.get()));
                    }

                } else if (mon >= 10 && mon <= 12) {
                    seasonZpScoreMap.get(4).add(zpAvg);
                    seasonKpScoreMap.get(4).add(avg);

                    Optional<WekEvaluate> firstEva = monList.stream().filter(i -> i.getKpName() != null).max((i1, i2) -> i1.getKpDate().compareTo(i2.getKpDate()));
                    if (firstEva.isPresent() && StringUtils.isNotEmpty(firstEva.get().getKpName())) {
                        sub.put("s4_kpName", firstEva.get().getKpName());
                    }
                    if (firstEva.isPresent() && firstEva.get().getKpDate() != null) {
                        sub.put("s4_kpDate", DateUtils.date2Str(firstEva.get().getKpDate(), DateUtils.date_sdf.get()));
                    }

                }


            });

            seasonKpScoreMap.forEach((season, seasonScoreList) -> {
                //季平均
                double score = seasonScoreList.stream().mapToDouble(Double::doubleValue)
                        .average()
                        .orElse(0);
                String savg =  String.format("%.1f",score);
                double avg =  Double.parseDouble(savg);
                if (score == 0) {
                    item.put("s" + season + "_1", "");
                    sub.put("s" + season + "avg_kp", "");

                } else {
                    item.put("s" + season + "_1", avg);
                    sub.put("s" + season + "avg_kp", avg);
                }

                //季绩效等级
                String seasonLevel = "C";
                String seasonPay = "100%";
                if (score == 0) {
                    seasonLevel = "";
                    seasonPay = "";
                } else if (score >= 95) {
                    seasonLevel = "A";
                    seasonPay = "140%";
                } else if (score <= 95 && score >= 85) {
                    seasonLevel = "B";
                    seasonPay = "110%";
                } else if (score <= 84 && score >= 75) {
                    seasonLevel = "C";
                    seasonPay = "100%";
                } else if (score <= 74 && score >= 60) {
                    seasonLevel = "D";
                    seasonPay = "90%";
                } else if (score <= 59) {
                    seasonLevel = "E";
                    seasonPay = "不合格";
                }
                item.put("s" + season + "_2", seasonLevel);
                item.put("s" + season + "_3", seasonPay);
                sub.put("s" + season + "level", seasonLevel);
                sub.put("s" + season + "pay", seasonPay);


            });

            seasonZpScoreMap.forEach((season, seasonScoreList) -> {
                //季平均
                double score = seasonScoreList.stream().mapToDouble(Double::doubleValue)
                        .average()
                        .orElse(0);
                String savg = String.format("%.1f", score);
                double avg = Double.parseDouble(savg);

                if (avg == 0) {
                    sub.put("s" + season + "avg_zp", "");

                } else {
                    sub.put("s" + season + "avg_zp", avg);
                }


            });


            res.add(item);
            subList.add(sub);
            index.getAndIncrement();
        });


        //对不存在的数据添加空数据，避免导出excel不渲染
        for (int i = 0; i < res.size(); i++) {
            Map<String, Object> d = res.get(i);
            for (int j = 0; j < colmuns.size(); j++) {
                Map<String, Object> c = colmuns.get(j);
                String column = c.get("column").toString().replace("{data.", "").replace("}", "");
                String colStr = "";
                for (String key : d.keySet()) {
                    colStr += key;
                    colStr += ",";
                }
                if (!colStr.contains(column)) {
                    res.get(i).put(column, "");
                }
            }

        }
        //汇总数据
        root.put("hz", res);
        //个人数据
        root.put("gr", subList);

        //月度绩效分析报告
        root.put("mon", monthAnalysList);


        return root;
    }

    /**
     * 补齐没有考评的绩效数据，默认C绩效
     *
     * @param wekEvaluategroup
     */
    private void patcEvaData(String yearstr,Map<String, List<WekEvaluate>> wekEvaluategroup, Map<String, SysUser> userMap) {
        //1.获取今天是今年的多少周
        int curWeekInYear = DateUtils.getCurWeek();
        int curYear = DateUtils.getCurYear();
        int year = Integer.parseInt(yearstr);
        if(year!=curYear) curWeekInYear = 52;
        int finalCurWeekInYear = curWeekInYear;
        wekEvaluategroup.forEach((user, evaList) -> {
            Map<Integer, WekEvaluate> userEvaMap = evaList.stream()
                    .collect(Collectors.toMap(WekEvaluate::getWeek, evaluate -> evaluate));
            Map<Integer, Integer> holidayWeek = HolidayUtils.getHolidayWeek(year);

            for (int i = 1; i <= finalCurWeekInYear; i++) {
                //TODO 判断是否法定节假日  一周都是法定节假日的情况下，不计算绩效
                if(holidayWeek.containsKey(i)) continue;
                if (!userEvaMap.containsKey(i)) {
                    WekEvaluate e = new WekEvaluate();
                    e.setZpPlan("C");
                    e.setZpAmount("C");
                    e.setZpEffe("C");
                    e.setZpQuality("C");
                    e.setKpPlan("C");
                    e.setKpAmount("C");
                    e.setKpEffe("C");
                    e.setKpQuality("C");
                    e.setYear(year);
                    e.setMonth(DateUtils.yearweekInMonth(year,i));
                    e.setWeek(i);
                    e.setZp(user);
                    e.setZpName(userMap.get(user).getRealname());
                    e.setYearWeek(year * 100 + i);
                    evaList.add(e);

                    //检测评分是否遗漏，遗漏默认填充C
                } else {
                    WekEvaluate wek = userEvaMap.get(i);
                    if (StringUtils.isEmpty(wek.getZpPlan())) wek.setZpPlan("C");
                    if (StringUtils.isEmpty(wek.getZpAmount())) wek.setZpAmount("C");
                    if (StringUtils.isEmpty(wek.getZpEffe())) wek.setZpEffe("C");
                    if (StringUtils.isEmpty(wek.getZpQuality())) wek.setZpQuality("C");


                    if (StringUtils.isEmpty(wek.getKpPlan())) wek.setKpPlan("C");
                    if (StringUtils.isEmpty(wek.getKpAmount())) wek.setKpAmount("C");
                    if (StringUtils.isEmpty(wek.getKpEffe())) wek.setKpEffe("C");
                    if (StringUtils.isEmpty(wek.getKpQuality())) wek.setKpQuality("C");

                    //替换元素
                    evaList.replaceAll(item -> item.getId() != null && item.getId().equals(wek.getId()) ? wek : item);

                }

            }

            wekEvaluategroup.put(user, evaList);


        });


    }


    /**
     * 组装月绩效分析报告  sheet2
     */
    private  List<Map<String, Object>> monthAnalys(Map<String, List<WekEvaluate>> wekEvaluategroup,Map<String, SysUser> userMap) {
        List<Map<String, Object>> res = new ArrayList<>();
        List<String> levList = Arrays.asList("A", "B", "C", "D", "E");
        List<WekEvaluate> allData = new ArrayList<>();
        Map<String, List<WekEvaluate>> wekGroup = deepCopyMap(wekEvaluategroup);
        //查询用户的小组信息
        wekGroup.forEach((user, evaluateList) -> {
            evaluateList.forEach(item->{
                 if(userMap.containsKey(item.getZp())){
                     item.setTeamName(userMap.get(item.getZp()).getTeamName());
                 }
            });
            evaluateList = evaluateList.stream().filter(i->i.getTeamName()!=null).collect(Collectors.toList());
            allData.addAll(evaluateList);
        });
        //个人周报数据按月分组
        Map<Integer, List<WekEvaluate>> monEvaMap = allData.stream().collect(Collectors.groupingBy(month -> month.getMonth()));
        monEvaMap.forEach((mon, monList) -> {

            Map<String, Object> uMap = new HashMap<>();
            uMap.put("name", mon  + "月绩效分析报告");
            Map<String, Integer> cMap = new HashMap<>();
            //1.先把abcd转成对应分数  TODO 查询部门小组并进行分组
            monList = monList.stream().map(item -> {
                int kpPlan = calScore(item.getKpPlan(), 1);
                item.setKpPlan(kpPlan + "");
                int kpAmount = calScore(item.getKpAmount(), 1);
                item.setKpAmount(kpAmount + "");
                int kpEffe = calScore(item.getKpEffe(), 1);
                item.setKpEffe(kpEffe + "");
                int kpQuality = calScore(item.getKpQuality(), 2);
                item.setKpQuality(kpQuality + "");
                return item;
            }).collect(Collectors.toList());
            //数据按人分组
            Map<String, List<WekEvaluate>> userEvaMap = monList.stream().collect(Collectors.groupingBy(month -> month.getZp()));
            userEvaMap.forEach((user, evaList) -> {

                uMap.put(user + "_0", evaList.get(0).getZpName());
                List<Map<String, Object>> kpMapList = evaList.stream().map(i -> {
                    Map<String, Object> item = new HashMap<>();
                    item.put("kpPlan", i.getKpPlan());
                    item.put("kpAmount", i.getKpAmount());
                    item.put("kpEffe", i.getKpEffe());
                    item.put("kpQuality", i.getKpQuality());
                    item.put("team", i.getTeamName());
                    return item;
                }).collect(Collectors.toList());
                //计算一个月各项考评分平均值
                double kpPlan = kpMapList.stream().mapToDouble(map -> Double.parseDouble(map.get("kpPlan").toString())).average().orElse(0.0);
                double kpAmount = kpMapList.stream().mapToDouble(map ->Double.parseDouble( map.get("kpAmount").toString())).average().orElse(0.0);
                double kpEffe = kpMapList.stream().mapToDouble(map -> Double.parseDouble(map.get("kpEffe").toString())).average().orElse(0.0);
                double kpQuality = kpMapList.stream().mapToDouble(map -> Double.parseDouble(map.get("kpQuality").toString())).average().orElse(0.0);
                String skpPlan = String.format("%.1f", kpPlan);
                kpPlan = Double.parseDouble(skpPlan);
                String skpAmount = String.format("%.1f", kpAmount);
                kpAmount = Double.parseDouble(skpAmount);
                String skpEffe = String.format("%.1f", kpEffe);
                kpEffe = Double.parseDouble(skpEffe);
                String skpQuality = String.format("%.1f", kpQuality);
                kpQuality = Double.parseDouble(skpQuality);
                uMap.put(user + "_1", kpPlan);
                uMap.put(user + "_2", kpAmount);
                uMap.put(user + "_3",  kpEffe);
                uMap.put(user + "_4",  kpQuality);
                double total = kpPlan + kpAmount + kpEffe + kpQuality;
                String stotal = String.format("%.1f", total);
                total = Double.parseDouble(stotal);
                uMap.put(user + "_5", total);
                String level = scoreCalcLevel(total);
                uMap.put(user + "_6", level);




                for (int i = 0; i <levList.size() ; i++) {
                      String s = levList.get(i);
                      if(level.equals(s)){
                          if(cMap.containsKey(s))cMap.put(s,cMap.get(s)+1);
                          else cMap.put(s,1);
                      }
                }

                //按小组统计
                Map<String, List<Map<String, Object>>> teamGroupMap = kpMapList.stream()
                        .collect(Collectors.groupingBy(map -> map.get("team").toString()));

                AtomicReference<String> team = new AtomicReference<>("team1");
                teamGroupMap.forEach((group,list)->{
                    double tkpPlan = list.stream().mapToDouble(map ->Double.parseDouble( map.get("kpPlan").toString())).average().orElse(0.0);
                    double tkpAmount = list.stream().mapToDouble(map ->Double.parseDouble( map.get("kpAmount").toString())).average().orElse(0.0);
                    double tkpEffe = list.stream().mapToDouble(map ->Double.parseDouble( map.get("kpEffe").toString())).average().orElse(0.0);
                    double tkpQuality = list.stream().mapToDouble(map ->Double.parseDouble( map.get("kpQuality").toString())).average().orElse(0.0);


                    if(group.equals("ARM软件")){
                        team.set("team1");
                    }else if(group.equals("上位机")){
                        team.set("team2");
                    }else if(group.equals("硬件")){
                        team.set("team3");
                    }else if(group.equals("产品")){
                        team.set("team4");
                    }else if(group.equals("营销")){
                        team.set("team5");
                    }

                    String stkpPlan = String.format("%.1f", tkpPlan);
                    tkpPlan = Double.parseDouble(stkpPlan);

                    String stkpAmount = String.format("%.1f", tkpAmount);
                    tkpAmount = Double.parseDouble(stkpAmount);

                    String stkpEffe = String.format("%.1f", tkpEffe);
                    tkpEffe = Double.parseDouble(stkpEffe);

                    String stkpQuality = String.format("%.1f", tkpQuality);
                    tkpQuality = Double.parseDouble(stkpQuality);

                    uMap.put( team.get() +"_1", tkpPlan);
                    uMap.put( team.get() +"_2",  tkpAmount);
                    uMap.put( team.get() +"_3", tkpEffe);
                    uMap.put( team.get() +"_4", tkpQuality);
                    double ttotal = tkpPlan + tkpAmount + tkpEffe + tkpQuality;

                    String sttotal = String.format("%.1f", ttotal);
                    ttotal = Double.parseDouble(sttotal);
                    uMap.put( team.get() +"_5",  ttotal);
                });
            });
            StringBuilder info = new StringBuilder();
            cMap.forEach((k,v)->{
                info.append( k +"共" + v +"人,");
            });
            levList.forEach(level->{
                if (cMap.containsKey(level)){
                    uMap.put("count"+level,cMap.get(level));
                }else {
                    uMap.put("count"+level,0);
                }
            });
            // 查找最后一个逗号的位置
            int lastCommaIndex = info.lastIndexOf(",");

            if (lastCommaIndex >= 0) {
                // 构建新的字符串，将最后一个逗号替换为空字符串
                String output = info.substring(0, lastCommaIndex) + info.substring(lastCommaIndex + 1);
                uMap.put("info",output);
            }
            res.add(uMap);


        });
         return res;

    }

    private String scoreCalcLevel(Double score) {
        if (score >= 95) {
            return "A";
        } else if (score >= 85) {
            return "B";
        } else if (score >= 75) {
            return "C";
        } else if (score >= 60) {
            return "D";
        }
        return "E";


    }


    // 进行深拷贝
    public Map<String, List<WekEvaluate>> deepCopyMap(Map<String, List<WekEvaluate>> originalMap) {
        Map<String, List<WekEvaluate>> copiedMap = new HashMap<>();

        for (Map.Entry<String, List<WekEvaluate>> entry : originalMap.entrySet()) {
            String key = entry.getKey();
            List<WekEvaluate> originalList = entry.getValue();
            List<WekEvaluate> copiedList = new ArrayList<>(originalList.size());

            // 深拷贝列表中的每个元素
            for (WekEvaluate wekEvaluate : originalList) {
                WekEvaluate copiedWekEvaluate = new WekEvaluate(); // 假设 WekEvaluat
                BeanUtils.copyProperties(wekEvaluate, copiedWekEvaluate);// e 类有复制构造函数
                copiedList.add(copiedWekEvaluate);
            }

            copiedMap.put(key, copiedList);
        }

        return copiedMap;
    }


    /**
     * 计算一周考评分
     *
     * @param a
     * @param b
     * @param c
     * @param d
     * @return
     */
    private int calcEvaScore(String a, String b, String c, String d) {
        int i = calScore(a, 1);
        int i1 = calScore(b, 1);
        int i2 = calScore(c, 1);
        int i3 = calScore(d, 2);
        return i + i1 + i2 + i3;

    }

    /**
     * 计算每项考评分
     *
     * @param kp
     * @param type
     * @return
     */
    private int calScore(String kp, int type) {
        int score = 0;
        switch (kp) {
            case "A":
                score = 19 * type;
                break;
            case "B":
                score = 17 * type;
                break;
            case "C":
                score = 15 * type;
                break;
            case "D":
                score = 12 * type;
                break;
            case "E":
                score = 5 * type;
                break;
        }

        return score;

    }

    /**
     * @param quarter 季度 0-全年 1-2-3-4
     * @return
     */
    public  Map<String, Object> createStatisticsYearTitle(Integer year, Integer quarter) {
        Map<String, Object> root = new HashMap<>();
        //title
        List<Map<String, Object>> res = new ArrayList<>();
        //周号对应colmun
        Map<Integer, String> map = new HashMap<>();
        //需要合并的行或列信息
        TreeMap<Integer, Integer> mergeSeason = new TreeMap<>();
        TreeMap<Integer, Integer> mergeMonth = new TreeMap<>();
        //当前年
        int curYear =  year;
        //根据 当前季度  得到月份区间
        Range<Integer> monRange = Range.between(quarter * 3 - 2, quarter * 3 == 0 ? 12 : quarter * 3);
        //统计一年有多少周
        int yearWeekCount = 0;
        //int sort = 0;
        //季
        int seasonCount = 1;

        LocalDate firstSundayOfYear = getFirstSundayOfYear(year);


        //一年12个月
        for (int i = 1; i <= 12; i++) {


            LocalDate date = LocalDate.of(curYear, i, 1);
            //获取当月第一天是星期几
            int week = date.getDayOfWeek().getValue();
            //这个月最后一天
            date = date.with(TemporalAdjusters.lastDayOfMonth());
            //获取这天是这个月的第几天
            int dayOfMonth = date.getDayOfMonth();
            //统计一个月有多少周 （TODO ：此处分周逻辑保持和周报获取周逻辑一致，定义周日在哪个月则设定本周属于哪个月）
            // int monthWeekCount = 0;



            for (int j = 1; j <= dayOfMonth; j++) {
                LocalDate everyDay = LocalDate.of(curYear, i, j);
                int d = everyDay.getDayOfWeek().getValue();
                //System.err.println("日期：" + everyDay + "-星期：" + d);
                if (d % 7 == 0) {
                    //判断当年第一天是否周一，不是则第一周不计数
                    LocalDate first = LocalDate.of(year,1,1);
                    int fd = first.getDayOfWeek().getValue();
                    //TODO 特殊处理，一年第一个月的第一周必须是一个完整的周一到周日
                    if(fd!=1 &&  firstSundayOfYear.isEqual(everyDay)){
                        continue;
                    }
                    // monthWeekCount++;
                    yearWeekCount++;
                    //System.err.println("周:"+yearWeekCount);
                    //System.err.println("日期:"+everyDay.toString());
                    //sort++;
                    String weekToDayStartStr = DateUtils.weekToDayStartStr(year,yearWeekCount);
                    String weekToDayEndStr = DateUtils.weekToDayEndStr(year,yearWeekCount);
                    String endstr = DateUtils.weekToDayEndStr(year,yearWeekCount,"yyyy-MM-dd");
                    Integer weekInMonth = DateUtils.getWeekInMonth(endstr);
                    Integer curMonth = DateUtils.yearweekInMonth(year, yearWeekCount);
                    //System.out.println(i + "月：" + monthWeekCount + "周-No:" + yearWeekCount + "------" + weekToDayStartStr + "==" + weekToDayEndStr);
                    //添加每个月的周
                    Map<String, Object> weekMap = new HashMap<>();
                    weekMap.put("title", curMonth + "月第" + weekInMonth + "周");
                    weekMap.put("month", curMonth + "月");
                    weekMap.put("week", "第" + weekInMonth + "周");
                    weekMap.put("season", seasonCount + "季度");
                    weekMap.put("column", "{data.w" + (curYear * 100 + yearWeekCount) + "_1}");
                    weekMap.put("wstart", weekToDayStartStr);
                    weekMap.put("wend", weekToDayEndStr);
                    weekMap.put("wno", yearWeekCount);
                    weekMap.put("type", "自评");
                    weekMap.put("sort", seasonCount*10000 + curMonth *100 + weekInMonth *10 +1);
                    //weekMap.put("curWeek", DateUtils.getCurWeek());
                    //只添加当前季度数据
                    if (monRange.contains(i)) {
                        res.add(weekMap);
                    }

                    //System.err.println(weekMap);


                    //sort++;
                    Map<String, Object> weekMap2 = new HashMap<>();
                    weekMap2.put("title", curMonth + "月第" + weekInMonth + "周");
                    weekMap2.put("month", curMonth + "月");
                    weekMap2.put("week", "第" + weekInMonth + "周");
                    weekMap2.put("season", seasonCount + "季度");
                    weekMap2.put("column", "{data.w" + (curYear * 100 + yearWeekCount) + "_2}");
                    weekMap2.put("wstart", weekToDayStartStr);
                    weekMap2.put("wend", weekToDayEndStr);
                    weekMap2.put("wno", yearWeekCount);
                    weekMap2.put("type", "考评");
                    weekMap2.put("sort", seasonCount*10000 + curMonth *100 + weekInMonth *10 +2);
                    //只添加当前季度数据
                    if (monRange.contains(i)) {
                        res.add(weekMap2);
                    }
                    map.put(yearWeekCount, "m" + i + "w" + weekInMonth);

                }
            }

            //sort++;
            //每个月加考评平均分
            //月度
            Map<String, Object> mon1 = new HashMap<>();
            mon1.put("season", seasonCount + "季度");
            mon1.put("week", "月度");
            mon1.put("month", i + "月绩效");
            mon1.put("column", "{data.m" + i + "_1}");
            mon1.put("type", "月度");
            mon1.put("sort", seasonCount*10000 + i *100 + 90);
            //只添加当前季度数据
            if (monRange.contains(i)) {
                res.add(mon1);
            }

            //sort++;
            //绩效等级
            Map<String, Object> mon2 = new HashMap<>();
            mon2.put("season", seasonCount + "季度");
            mon2.put("week", "绩效等级");
            mon2.put("month", i + "月绩效");
            mon2.put("column", "{data.m" + i + "_2}");
            mon2.put("type", "绩效等级");
            mon2.put("sort", seasonCount*10000 + i *100 + 91);
            //只添加当前季度数据
            if (monRange.contains(i)) {
                res.add(mon2);
            }

            //sort++;
            //发放比例
            Map<String, Object> mon3 = new HashMap<>();
            mon3.put("season", seasonCount + "季度");
            mon3.put("week", "发放比例");
            mon3.put("month", i + "月绩效");
            mon3.put("column", "{data.m" + i + "_3}");
            mon3.put("type", "发放比例");
            mon3.put("sort", seasonCount*10000 + i *100 + 92);
            //只添加当前季度数据
            if (monRange.contains(i)) {
                res.add(mon3);
            }
            //季
            if (i % 3 == 0) {

                //sort++;
                //发放比例
                Map<String, Object> s1 = new HashMap<>();
                s1.put("season", seasonCount + "季度");
                s1.put("week", "季度");
                s1.put("month", seasonCount + "季度绩效");
                s1.put("column", "{data.s" + seasonCount + "_1}");
                s1.put("type", "季度");
                s1.put("sort", seasonCount*10000 + 9000);
                //只添加当前季度数据
                if (quarter == 0 || seasonCount == quarter) {
                    res.add(s1);
                }

                //sort++;
                //发放比例
                Map<String, Object> s2 = new HashMap<>();
                s2.put("season", seasonCount + "季度");
                s2.put("week", "绩效等级");
                s2.put("month", seasonCount + "季度绩效");
                s2.put("column", "{data.s" + seasonCount + "_2}");
                s2.put("type", "绩效等级");
                s2.put("sort", seasonCount*10000 + 9100);
                //只添加当前季度数据
                if (quarter == 0 || seasonCount == quarter) {
                    res.add(s2);
                }

                //sort++;
                //发放比例
                Map<String, Object> s3 = new HashMap<>();
                s3.put("season", seasonCount + "季度");
                s3.put("week", "发放比例");
                s3.put("month", seasonCount + "季度绩效");
                s3.put("column", "{data.s" + seasonCount + "_3}");
                s3.put("type", "发放比例");
                s3.put("sort", seasonCount*10000 + 9200);
                //只添加当前季度数据
                if (quarter == 0 || seasonCount == quarter) {
                    res.add(s3);
                }
                seasonCount++;
            }
        }
        root.put("colmuns", res);
        root.put("config", map);
        //System.err.println("-------------------------------------");
        //System.err.println(JSON.toJSON(res));

        //计算合并数据
        //1.季度
        Map<Object, List<Map<String, Object>>> seasonMap = res.stream().collect(Collectors.groupingBy(item -> item.get("season")));
        seasonMap.forEach((season, list) -> {
            //季度
            Integer s = Integer.parseInt(season.toString().replaceAll("\\D+", ""));
            int size = list.size();
            mergeSeason.put(s, size);

        });
        //2.月
        List<MergeBean> monMergeList = new ArrayList<>();
        List<MergeBean> wekMergeList = new ArrayList<>();
        AtomicReference<Integer> monMergeIndex = new AtomicReference<>(1);
        res.forEach(item -> {
            String month = (String) item.get("month");
            MergeBean bean = new MergeBean();
            bean.setCol(month);
            bean.setSort(monMergeIndex.get());
            monMergeIndex.getAndSet(monMergeIndex.get() + 1);
            monMergeList.add(bean);

            String week = (String) item.get("week");
            MergeBean bean2 = new MergeBean();
            bean2.setCol(week);
            bean2.setSort(monMergeIndex.get());
            monMergeIndex.getAndSet(monMergeIndex.get() + 1);
            wekMergeList.add(bean2);

        });
        Map<String, List<MergeBean>> monGroupList = monMergeList.stream().collect(Collectors.groupingBy(
                item -> item.getCol(),
                LinkedHashMap::new,
                Collectors.toList()));

        List<Integer> mergeWeek = new ArrayList<>();
        LinkedHashMap<String, List<Map<String, Object>>> collect = res.stream().collect(Collectors.groupingBy(item -> item.get("month").toString(), LinkedHashMap::new, Collectors.toList()));
        collect.forEach((m, list) -> {
            LinkedHashMap<String, List<Map<String, Object>>> week = list.stream().collect(Collectors.groupingBy(item -> item.get("week").toString(), LinkedHashMap::new, Collectors.toList()));
            week.forEach((w, weekList) -> {
                mergeWeek.add(weekList.size());
            });
        });


        monGroupList.forEach((key, list) -> {
            mergeMonth.put(mergeMonth.size(), list.size());
        });
        root.put("mergeSeason", mergeSeason);
        root.put("mergeMonth", mergeMonth);
        root.put("mergeWeek", mergeWeek);
        return root;
    }

    public  LocalDate getFirstSundayOfYear(int year) {
        LocalDate date = LocalDate.of(year, 1, 1);

        // 找到给定年份中第一个周日的日期
        while (date.getDayOfWeek() != DayOfWeek.SUNDAY) {
            date = date.plusDays(1);
        }

        return date;
    }

    @Data
    public class MergeBean {
        private String col;
        private String alias;
        private Integer count;
        private Integer sort;
    }


    class MyHandler extends AbstractMergeStrategy {
        private Map<Integer, Integer> mergeSeason;
        private Map<Integer, Integer> mergeMonth;
        private List<Integer> mergeWeek;

        public MyHandler(Map<Integer, Integer> mergeSeason, Map<Integer, Integer> mergeMonth, List<Integer> mergeWeek) {
            this.mergeSeason = mergeSeason;
            this.mergeMonth = mergeMonth;
            this.mergeWeek = mergeWeek;
        }


        @Override
        protected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {
            //合并单元格，根据模板手动计算合并单元格坐标
            Integer seasonStartRow = 1;
            AtomicReference<Integer> seasonStartCol = new AtomicReference<>(4);
            AtomicReference<Integer> seasonEndCol = new AtomicReference<>(0);
            mergeSeason.forEach((type, value) -> {
                //1.合并四季的title
                seasonEndCol.set(seasonStartCol.get() + (value - 1));
                if (cell.getRowIndex() == seasonStartRow && cell.getColumnIndex() == seasonStartCol.get()) {
                    CellRangeAddress cellAddresses = new CellRangeAddress(seasonStartRow, seasonStartRow, seasonStartCol.get(), seasonEndCol.get());
                    sheet.addMergedRegionUnsafe(cellAddresses);
                }
                seasonStartCol.set(seasonStartCol.get() + (value - 1) + 1);
            });
            Integer monthStartRow = 2;
            AtomicReference<Integer> monthStartCol = new AtomicReference<>(4);
            AtomicReference<Integer> monthEndCol = new AtomicReference<>(0);
            mergeMonth.forEach((type, value) -> {
                //1.合并月的title
                monthEndCol.set(monthStartCol.get() + (value - 1));
                if (cell.getRowIndex() == monthStartRow && cell.getColumnIndex() == monthStartCol.get()) {
                    CellRangeAddress cellAddresses = new CellRangeAddress(monthStartRow, monthStartRow, monthStartCol.get(), monthEndCol.get());
                    sheet.addMergedRegionUnsafe(cellAddresses);
                }
                monthStartCol.set(monthStartCol.get() + (value - 1) + 1);
            });


            Integer weekStartRow = 3;
            AtomicReference<Integer> weekStartCol = new AtomicReference<>(4);
            AtomicReference<Integer> weekEndCol = new AtomicReference<>(0);
            mergeWeek.forEach((value) -> {
                //1.合并周的title
                weekEndCol.set(weekStartCol.get() + (value - 1));
                if (cell.getRowIndex() == weekStartRow && cell.getColumnIndex() == weekStartCol.get()) {
                    if (value == 1) {
                        CellRangeAddress cellAddresses = new CellRangeAddress(weekStartRow, weekStartRow + 1, weekStartCol.get(), weekEndCol.get());
                        sheet.addMergedRegionUnsafe(cellAddresses);
                    } else {
                        CellRangeAddress cellAddresses = new CellRangeAddress(weekStartRow, weekStartRow, weekStartCol.get(), weekEndCol.get());
                        sheet.addMergedRegionUnsafe(cellAddresses);
                    }
                }
                weekStartCol.set(weekStartCol.get() + (value - 1) + 1);
            });

            if (relativeRowIndex == null || relativeRowIndex == 0) {
                return;
            }


        }
    }
}