package cn.estsh.i3plus.ext.mes.apiservice.serviceimpl.busi.bu3;

import cn.estsh.i3plus.ext.mes.api.base.IModuleExcelManageService;
import cn.estsh.i3plus.ext.mes.api.base.bu.IBusiConfigService;
import cn.estsh.i3plus.ext.mes.api.busi.bu3.ISxLossTimeMonthlyService;
import cn.estsh.i3plus.ext.mes.apiservice.util.FileExtUtil;
import cn.estsh.i3plus.ext.mes.apiservice.util.SxExcelExtUtil;
import cn.estsh.i3plus.ext.mes.pojo.bean.MesBusiConfig;
import cn.estsh.i3plus.ext.mes.pojo.bean.MesProdDataToStatisticsShiftProd;
import cn.estsh.i3plus.ext.mes.pojo.bean.MesShiftProd;
import cn.estsh.i3plus.ext.mes.pojo.bean.MesShiftProdCenterRecord;
import cn.estsh.i3plus.ext.mes.pojo.model.bu3.SxEChartsReportCommonDataModel;
import cn.estsh.i3plus.ext.mes.pojo.model.bu3.SxLossTimeMonthlyModel;
import cn.estsh.i3plus.ext.mes.pojo.model.bu3.SxShiftProdCenterRecordStatisticsModel;
import cn.estsh.i3plus.ext.mes.pojo.repository.MesProdDataToStatisticsShiftProdRepository;
import cn.estsh.i3plus.ext.mes.pojo.repository.MesShiftProdCenterRecordRepository;
import cn.estsh.i3plus.ext.mes.pojo.repository.MesShiftProdRepository;
import cn.estsh.i3plus.ext.mes.pojo.util.MesExtConstWords;
import cn.estsh.i3plus.ext.mes.pojo.util.MesExtEnumUtil;
import cn.estsh.i3plus.platform.common.exception.ImppExceptionEnum;
import cn.estsh.i3plus.platform.common.tool.MathOperation;
import cn.estsh.i3plus.platform.common.tool.TimeTool;
import cn.estsh.i3plus.pojo.base.bean.DdlPackBean;
import cn.estsh.i3plus.pojo.base.codemaker.SnowflakeIdMaker;
import cn.estsh.i3plus.pojo.base.enumutil.CommonEnumUtil;
import cn.estsh.i3plus.pojo.base.tool.DdlPreparedPack;
import cn.estsh.i3plus.pojo.mes.bean.MesFile;
import cn.estsh.i3plus.pojo.platform.bean.SysFile;
import cn.estsh.impp.framework.boot.exception.ImppBusiException;
import cn.estsh.impp.framework.boot.exception.ImppExceptionBuilder;
import cn.estsh.impp.framework.boot.fileservice.ImppFileService;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.io.FileUtils;
import org.apache.commons.lang3.exception.ExceptionUtils;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.openxml4j.util.ZipSecureFile;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.math.BigDecimal;
import java.text.ParseException;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @author wangjie
 * @version 1.0
 * @date 2021/1/13 9:28
 **/
@Slf4j
@Service
public class SxLossTimeMonthlyService implements ISxLossTimeMonthlyService {

    @Autowired
    private MesShiftProdRepository shiftProdRepository;

    @Autowired
    private MesShiftProdCenterRecordRepository shiftProdCenterRecordRepository;

    @Autowired
    private MesProdDataToStatisticsShiftProdRepository prodDataToStatisticsShiftProdRepository;

    @Autowired
    private IModuleExcelManageService moduleExcelManageService;

    @Autowired
    private SnowflakeIdMaker snowflakeIdMaker;

    @Autowired
    private ImppFileService imppFileService;

    @Autowired
    private IBusiConfigService busiConfigService;

    private final static String SX_LOSS_TIME_MONTHLY_REPORT = "SX_LOSS_TIME_MONTHLY_REPORT";
    private final static Integer TEMPLATE_LAST_ROW = 2;

    @Override
    public SxLossTimeMonthlyModel queryLossTimeMonthly(SxLossTimeMonthlyModel data) {

        sxYearFlag(data);

        Map<String, String> dateMap = getDateMap(data);

        calcYmdTime(data, dateMap);

        calcQueryTime(data);

        Map<String, List<MesShiftProd>> shiftProdMap = getShiftProdMap(data.getOrganizeCode());

        List<SxShiftProdCenterRecordStatisticsModel> recordStatisticsList = getRecordStatisticsList(data, shiftProdMap);

        Map<String, List<SxShiftProdCenterRecordStatisticsModel>> recordStatisticsMap = getRecordStatisticsMap(data, recordStatisticsList);

        basicData(data);

        Map<Integer, List<Map<String, Object>>> statisticsDataMap = getStatisticsDataMap(shiftProdMap, recordStatisticsMap);

        return addAmountData(data, statisticsDataMap);

    }

    private void calcYmdTime(SxLossTimeMonthlyModel data, Map<String, String> dateMap) {

        String temp;
        switch (DIMENSION.getByCode(data.getDimension())) {
            case YEAR:
                if (!data.getSxYearFlag()) {
                    data.setYmdStart(Integer.valueOf(data.getWorkTime()) - 1 + MesExtConstWords.DATE_MM_DD_01_01);
                    data.setYmdEnd(data.getWorkTime() + MesExtConstWords.DATE_MM_DD_12_31);

                    data.curDataTimeStart(data.getWorkTime() + MesExtConstWords.DATE_MM_DD_01_01)
                            .lastDataTimeEnd(Integer.valueOf(data.getWorkTime()) - 1 + MesExtConstWords.DATE_MM_DD_12_31);
                } else {
                    data.setYmdStart(Integer.valueOf(data.getWorkTime()) - 1 + MesExtConstWords.DATE_MM_DD_04_01);
                    data.setYmdEnd(Integer.valueOf(data.getWorkTime()) + 1 + MesExtConstWords.DATE_MM_DD_03_31);

                    data.curDataTimeStart(data.getWorkTime() + MesExtConstWords.DATE_MM_DD_04_01)
                            .lastDataTimeEnd(data.getWorkTime() + MesExtConstWords.DATE_MM_DD_03_31);
                }
                data.lastDataTimeStart(data.getYmdStart()).actualDataTimeStart(data.getCurDataTimeStart());
                break;
            case SEASON:
                if (data.getWorkTime().equals("1Q")) {
                    data.setYmdStart(dateMap.get("0Q").split(MesExtConstWords.AND)[0]);
                } else {
                    data.setYmdStart(dateMap.get("1Q").split(MesExtConstWords.AND)[0]);
                }
                data.setYmdEnd(dateMap.get(data.getWorkTime()).split(MesExtConstWords.AND)[1]);

                temp = Integer.valueOf(data.getWorkTime().substring(0, 1)) - 1 + "Q";
                data.curDataTimeStart(dateMap.get(data.getWorkTime()).split(MesExtConstWords.AND)[0])
                        .lastDataTimeStart(dateMap.get(temp).split(MesExtConstWords.AND)[0]).lastDataTimeEnd(dateMap.get(temp).split(MesExtConstWords.AND)[1])
                        .actualDataTimeStart(dateMap.get("1Q").split(MesExtConstWords.AND)[0]);
                break;
            case MONTH:
                if (!data.getSxYearFlag()) {
                    temp = getYear(data.getWorkTime(), false);
                    data.setYmdStart(getYmdStart(temp, data.getWorkTime(), "-01", true));

                    data.actualDataTimeStart(temp + MesExtConstWords.DATE_MM_DD_01_01);
                } else {
                    temp = getYear(data.getWorkTime(), true);
                    data.setYmdStart(getYmdStart(temp, data.getWorkTime(), "-04", true));

                    data.actualDataTimeStart(temp + MesExtConstWords.DATE_MM_DD_04_01);
                }
                data.setYmdEnd(calcDate(calcMonth(data.getWorkTime(), 1) + MesExtConstWords.DATE_DD_01, -1));

                data.curDataTimeStart(data.getWorkTime() + MesExtConstWords.DATE_DD_01)
                        .lastDataTimeStart(calcMonth(data.getWorkTime(), -1) + MesExtConstWords.DATE_DD_01).lastDataTimeEnd(calcDate(data.getCurDataTimeStart(), -1));
                break;
            default:
                if (!data.getSxYearFlag()) {
                    temp = getYear(data.getWorkTime(), false);
                    data.setYmdStart(getYmdStart(temp, data.getWorkTime(), MesExtConstWords.DATE_MM_DD_01_01, false));

                    data.actualDataTimeStart(temp + MesExtConstWords.DATE_MM_DD_01_01);
                } else {
                    temp = getYear(data.getWorkTime(), true);
                    data.setYmdStart(getYmdStart(temp, data.getWorkTime(), MesExtConstWords.DATE_MM_DD_04_01, false));

                    data.actualDataTimeStart(temp + MesExtConstWords.DATE_MM_DD_04_01);
                }
                data.setYmdEnd(data.getWorkTime());

                data.curDataTimeStart(data.getWorkTime())
                        .lastDataTimeStart(calcDate(data.getWorkTime(), -1)).lastDataTimeEnd(data.getLastDataTimeStart());
                break;
        }

        data.curDataTimeEnd(data.getYmdEnd()).actualDataTimeEnd(data.getCurDataTimeEnd());

    }

    private String getYear(String workTime, Boolean flag) {
        String curYear = workTime.substring(0, 4);
        if (!flag) return curYear;
        if (workTime.substring(0, 7).compareTo(curYear + "-04") >= 0) return curYear;
        return String.valueOf(Integer.parseInt(curYear) - 1);
    }

    private String getYmdStart(String year, String workTime, String appendDate, Boolean flag) {
        String date = year + appendDate;
        return flag ? ((!workTime.equals(date) ? date : calcMonth(date, -1)) + MesExtConstWords.DATE_DD_01) : (!workTime.equals(date) ? date : calcDate(date, -1));
    }

    private void calcQueryTime(SxEChartsReportCommonDataModel data) {
        String curMonth = TimeTool.getToday().substring(0, 7);
        String curMonthBegin = curMonth + MesExtConstWords.DATE_DD_01;

        switch (DIMENSION.getByCode(data.getDimension())) {
            case YEAR:
            case SEASON:
            case MONTH:
                if (curMonthBegin.compareTo(data.getYmdStart()) <= 0) {
                    data.setWorkTimeYmdStart(data.getYmdStart());
                    data.setWorkTimeYmdEnd(data.getYmdEnd());
                } else if (curMonthBegin.compareTo(data.getYmdEnd()) > 0) {
                    data.setStatisticsYmStart(data.getYmdStart().substring(0, 7));
                    data.setStatisticsYmEnd(data.getYmdEnd().substring(0, 7));
                } else {
                    data.setStatisticsYmStart(data.getYmdStart().substring(0, 7));
                    data.setStatisticsYmEnd(calcMonth(curMonth, -1));
                    data.setWorkTimeYmdStart(curMonthBegin);
                    data.setWorkTimeYmdEnd(data.getYmdEnd());
                }
                break;
            default:
                if (curMonthBegin.compareTo(data.getYmdStart()) <= 0) {
                    data.setWorkTimeYmdStart(data.getYmdStart());
                    data.setWorkTimeYmdEnd(data.getYmdEnd());
                } else if (curMonthBegin.compareTo(data.getYmdEnd()) > 0) {
                    data.setStatisticsYmdStart(data.getYmdStart());
                    data.setStatisticsYmdEnd(data.getYmdEnd());
                } else {
                    data.setStatisticsYmdStart(data.getYmdStart());
                    data.setStatisticsYmdEnd(calcDate(curMonthBegin, -1));
                    data.setWorkTimeYmdStart(curMonthBegin);
                    data.setWorkTimeYmdEnd(data.getYmdEnd());
                }
                break;
        }
    }

    private String calcMonth(String month, int amount) {
        return TimeTool.timeCalc(TimeTool.stringParseToDate(month, MesExtConstWords.DATE_FORMAT_YM), Calendar.MONTH, amount, MesExtConstWords.DATE_FORMAT_YM);
    }

    private String calcDate(String date, int amount) {
        return TimeTool.timeCalc(TimeTool.stringParseToDate(date, MesExtConstWords.DATE_FORMAT), Calendar.DATE, amount, MesExtConstWords.DATE_FORMAT);
    }

    private void basicData(SxLossTimeMonthlyModel data) {
        data.addTableName(MesExtConstWords.INDEX, "序号").addTableName(MesExtConstWords.PROD_TYPE_NAME, "损失区分").addTableName(MesExtConstWords.PROD_NAME, "项目")
                .addTableName("planRp", "计划").addTableName("lastRp", "前回").addTableName("curRp", "今回")
                .addTableName("lastSubRp", "前回差").addTableName("bpSubRp", "BP差").addTableName(MesExtConstWords.RESULT, "判定")
                .addTableName("amountPlanRp", "累计计划").addTableName("amountActualRp", "累计实绩").addTableName("amountDiffRp", "累计差异").addTableName("amountResult", "判定");
        switch (DIMENSION.getByCode(data.getDimension())) {
            case YEAR:
                data.title(data.getWorkTime() + "年" + data.getCategoryName() + "非作业时间");
                break;
            case SEASON:
                data.title(data.getYear() + "年" + data.getWorkTime().substring(0, 1) + "季度" + data.getCategoryName() + "非作业时间");
                break;
            case MONTH:
                String[] timeM = data.getWorkTime().split(MesExtConstWords.E_LINE);
                data.title(timeM[0] + "年" + Integer.valueOf(timeM[1]) + "月" + data.getCategoryName() + "非作业时间");
                break;
            default:
                String[] timeD = data.getWorkTime().split(MesExtConstWords.E_LINE);
                data.title(timeD[0] + "年" + Integer.valueOf(timeD[1]) + "月" + Integer.valueOf(timeD[2]) + "日" + data.getCategoryName() + "非作业时间");
                break;
        }
    }

    private Map<String, List<SxShiftProdCenterRecordStatisticsModel>> getRecordStatisticsMap(SxLossTimeMonthlyModel data, List<SxShiftProdCenterRecordStatisticsModel> recordStatisticsList) {
        if (CollectionUtils.isEmpty(recordStatisticsList)) return null;

        Map<String, List<SxShiftProdCenterRecordStatisticsModel>> recordStatisticsMap = new HashMap<>();
        List<SxShiftProdCenterRecordStatisticsModel> curDataList = new ArrayList<>();
        List<SxShiftProdCenterRecordStatisticsModel> lastDataList = new ArrayList<>();
        List<SxShiftProdCenterRecordStatisticsModel> actualDataList = new ArrayList<>();

        for (SxShiftProdCenterRecordStatisticsModel record : recordStatisticsList) {
            if (null == record) continue;
            if (record.getGroupInfo().compareTo(data.getCurDataTimeStart()) >= 0 && record.getGroupInfo().compareTo(data.getCurDataTimeEnd()) <= 0) curDataList.add(record);
            if (record.getGroupInfo().compareTo(data.getLastDataTimeStart()) >= 0 && record.getGroupInfo().compareTo(data.getLastDataTimeEnd()) <= 0) lastDataList.add(record);
            if (record.getGroupInfo().compareTo(data.getActualDataTimeStart()) >= 0 && record.getGroupInfo().compareTo(data.getActualDataTimeEnd()) <= 0) actualDataList.add(record);
        }
        if (!CollectionUtils.isEmpty(curDataList)) recordStatisticsMap.put("curDataList", curDataList);
        if (!CollectionUtils.isEmpty(lastDataList)) recordStatisticsMap.put("lastDataList", lastDataList);
        if (!CollectionUtils.isEmpty(actualDataList)) recordStatisticsMap.put("actualDataList", actualDataList);

        return recordStatisticsMap;
    }

    private List<SxShiftProdCenterRecordStatisticsModel> getRecordStatisticsList(SxLossTimeMonthlyModel data, Map<String, List<MesShiftProd>> shiftProdMap) {
        List<SxShiftProdCenterRecordStatisticsModel> recordStatisticsList = new ArrayList<>();
        if (!StringUtils.isEmpty(data.getWorkTimeYmdStart())) {
            List<SxShiftProdCenterRecordStatisticsModel> resultList = getShiftProdCenterRecordList(data, data.getWorkTimeYmdStart(), data.getWorkTimeYmdEnd(), shiftProdMap);
            if (!CollectionUtils.isEmpty(resultList)) recordStatisticsList.addAll(resultList);
        }
        if (!StringUtils.isEmpty(data.getStatisticsYmStart())) {
            List<SxShiftProdCenterRecordStatisticsModel> resultList = getShiftProdCenterRecordStatisticsList(data, data.getStatisticsYmStart(), data.getStatisticsYmEnd(), shiftProdMap);
            if (!CollectionUtils.isEmpty(resultList)) recordStatisticsList.addAll(resultList);
        }
        if (!StringUtils.isEmpty(data.getStatisticsYmdStart())) {
            List<SxShiftProdCenterRecordStatisticsModel> resultList = getShiftProdCenterRecordStatisticsList(data, data.getStatisticsYmdStart(), data.getStatisticsYmdEnd(), shiftProdMap);
            if (!CollectionUtils.isEmpty(resultList)) recordStatisticsList.addAll(resultList);
        }
        return recordStatisticsList;
    }

    private List<SxShiftProdCenterRecordStatisticsModel> getShiftProdCenterRecordList(SxLossTimeMonthlyModel data, String workTimeStart, String workTimeEnd, Map<String, List<MesShiftProd>> shiftProdMap) {
        DdlPackBean packBean = DdlPackBean.getDdlPackBean(data.getOrganizeCode());
        DdlPreparedPack.getStringEqualPack(data.getCategoryCode(), MesExtConstWords.CATEGORY_CODE, packBean);
        DdlPreparedPack.getStringEqualPack(data.getCareerCode(), MesExtConstWords.CAREER_CODE, packBean);
        DdlPreparedPack.getNumEqualPack(data.getProdType(), MesExtConstWords.PROD_TYPE, packBean);
        DdlPreparedPack.getNumEqualPack(data.getProdCategory(), MesExtConstWords.PROD_CATEGORY, packBean);
        DdlPreparedPack.timeBuilder(workTimeStart, workTimeEnd, MesExtConstWords.WORK_TIME, packBean, false);
        List<MesShiftProdCenterRecord> shiftProdCenterRecordList = shiftProdCenterRecordRepository.findByHqlWhere(packBean);
        if (CollectionUtils.isEmpty(shiftProdCenterRecordList)) return null;
        if (!StringUtils.isEmpty(data.getWorkCenterCodeArr())) {
            List<String> workCenterCodeList = Arrays.asList(data.getWorkCenterCodeArr().split(MesExtConstWords.COMMA));
            shiftProdCenterRecordList = shiftProdCenterRecordList.stream().filter(o -> (null != o && !StringUtils.isEmpty(o.getWorkCenterCode()) && workCenterCodeList.contains(o.getWorkCenterCode()))).collect(Collectors.toList());
        }
        if (CollectionUtils.isEmpty(shiftProdCenterRecordList)) return null;
        List<SxShiftProdCenterRecordStatisticsModel> resultList = new ArrayList<>();
        for (MesShiftProdCenterRecord item : shiftProdCenterRecordList) {
            if (null == item) continue;
            SxShiftProdCenterRecordStatisticsModel result = new SxShiftProdCenterRecordStatisticsModel();
            BeanUtils.copyProperties(item, result);
            result.setGroupInfo(result.getWorkTime());
            result.setProdName(getProdName(shiftProdMap, result.getProdType(), result.getProdCode()));
            try {
                result.setStatistics(MathOperation.mul(new Double(TimeTool.getSecoundsBetweenTime(2, item.getStartTime(), item.getEndTime())), item.getWorkerQty()));
            } catch (ParseException e) {
                result.setStatistics(new Double(0));
            }
            resultList.add(result);
        }
        return resultList;
    }

    private List<SxShiftProdCenterRecordStatisticsModel> getShiftProdCenterRecordStatisticsList(SxLossTimeMonthlyModel data, String workTimeStart, String workTimeEnd, Map<String, List<MesShiftProd>> shiftProdMap) {
        DdlPackBean packBean = getRecordStatisticsPackBeanByType(data, workTimeStart, workTimeEnd);
        List<MesProdDataToStatisticsShiftProd> prodDataToStatisticsShiftProdList = prodDataToStatisticsShiftProdRepository.findByHqlWhere(packBean);
        if (CollectionUtils.isEmpty(prodDataToStatisticsShiftProdList)) return null;
        if (!StringUtils.isEmpty(data.getWorkCenterCodeArr())) {
            List<String> workCenterCodeList = Arrays.asList(data.getWorkCenterCodeArr().split(MesExtConstWords.COMMA));
            prodDataToStatisticsShiftProdList = prodDataToStatisticsShiftProdList.stream().filter(o -> (null != o && !StringUtils.isEmpty(o.getWorkCenterCode()) && workCenterCodeList.contains(o.getWorkCenterCode()))).collect(Collectors.toList());
        }
        if (CollectionUtils.isEmpty(prodDataToStatisticsShiftProdList)) return null;
        List<SxShiftProdCenterRecordStatisticsModel> resultList = new ArrayList<>();
        for (MesProdDataToStatisticsShiftProd item : prodDataToStatisticsShiftProdList) {
            if (null == item) continue;
            SxShiftProdCenterRecordStatisticsModel result = new SxShiftProdCenterRecordStatisticsModel();
            BeanUtils.copyProperties(item, result);
            result.setGroupInfo(result.getWorkTime().length() == 7 ? result.getWorkTime() + MesExtConstWords.DATE_MM_DD_01_01 : result.getWorkTime());
            result.setProdName(getProdName(shiftProdMap, result.getProdType(), result.getProdCode()));
            resultList.add(result);
        }
        return resultList;
    }


    private DdlPackBean getRecordStatisticsPackBeanByType(SxLossTimeMonthlyModel data, String workTimeStart, String workTimeEnd) {
        DdlPackBean packBean = getStatisticsPackBeanByType(data, workTimeStart, workTimeEnd);
        if (workTimeStart.length() == 7) {
            DdlPreparedPack.getStringEqualPack(MesExtConstWords.MONTH, MesExtConstWords.DATA_TYPE, packBean);
            DdlPreparedPack.getStringEqualPack(MesExtEnumUtil.JOB_STATISTICS_PROD_DATA_KEY.SHIFT_PROD_MONTH_KEY.getValue(), MesExtConstWords.DATA_KEY, packBean);
        } else {
            DdlPreparedPack.getStringEqualPack(MesExtConstWords.DAY, MesExtConstWords.DATA_TYPE, packBean);
            DdlPreparedPack.getStringEqualPack(MesExtEnumUtil.JOB_STATISTICS_PROD_DATA_KEY.SHIFT_PROD_DAY_KEY.getValue(), MesExtConstWords.DATA_KEY, packBean);
        }
        return packBean;
    }

    private DdlPackBean getStatisticsPackBeanByType(SxLossTimeMonthlyModel data, String workTimeStart, String workTimeEnd) {
        DdlPackBean packBean = DdlPackBean.getDdlPackBean(data.getOrganizeCode());
        DdlPreparedPack.getStringEqualPack(data.getCategoryCode(), MesExtConstWords.CATEGORY_CODE, packBean);
        DdlPreparedPack.getStringEqualPack(data.getCareerCode(), MesExtConstWords.CAREER_CODE, packBean);
        DdlPreparedPack.getNumEqualPack(data.getProdCategory(), MesExtConstWords.PROD_CATEGORY, packBean);
        DdlPreparedPack.getNumEqualPack(data.getProdType(), MesExtConstWords.PROD_TYPE, packBean);
        DdlPreparedPack.timeBuilder(workTimeStart, workTimeEnd, MesExtConstWords.WORK_TIME, packBean, false);
        return packBean;
    }

    private Map<Integer, List<Map<String, Object>>> getStatisticsDataMap(Map<String, List<MesShiftProd>> shiftProdMap, Map<String, List<SxShiftProdCenterRecordStatisticsModel>> recordStatisticsMap) {

        if (CollectionUtils.isEmpty(recordStatisticsMap)) return null;

        List<SxShiftProdCenterRecordStatisticsModel> curDataList = recordStatisticsMap.get("curDataList");
        List<SxShiftProdCenterRecordStatisticsModel> lastDataList = recordStatisticsMap.get("lastDataList");
        List<SxShiftProdCenterRecordStatisticsModel> actualDataList = recordStatisticsMap.get("actualDataList");

        Map<Integer, List<SxShiftProdCenterRecordStatisticsModel>> curDataMapByProdType = getDataMapByProdType(curDataList);
        Map<Integer, List<SxShiftProdCenterRecordStatisticsModel>> lastDataMapByProdType = getDataMapByProdType(lastDataList);
        Map<Integer, List<SxShiftProdCenterRecordStatisticsModel>> actualDataMapByProdType = getDataMapByProdType(actualDataList);

        if (CollectionUtils.isEmpty(curDataMapByProdType) && CollectionUtils.isEmpty(lastDataMapByProdType) && CollectionUtils.isEmpty(actualDataMapByProdType)) return null;

        Double curDataStatistics = getDataListStatistics(getStatisticsDataList(curDataList));
        Double lastDataStatistics = getDataListStatistics(getStatisticsDataList(lastDataList));
        Double actualDataStatistics = getDataListStatistics(getStatisticsDataList(actualDataList));

        Map<Integer, List<Map<String, Object>>> statisticsDataMap = getStatisticsDataMapByProdType(curDataMapByProdType, lastDataMapByProdType, actualDataMapByProdType,
                new HashMap<>(), shiftProdMap, curDataStatistics, lastDataStatistics, actualDataStatistics, MesExtEnumUtil.SHIFT_PROD_TYPE.NORMAL_STOP_CENTER);

        statisticsDataMap = getStatisticsDataMapByProdType(curDataMapByProdType, lastDataMapByProdType, actualDataMapByProdType,
                statisticsDataMap, shiftProdMap, curDataStatistics, lastDataStatistics, actualDataStatistics, MesExtEnumUtil.SHIFT_PROD_TYPE.UNNORMAL_STOP_CENTER);

        return statisticsDataMap;
    }

    private Map<String, List<MesShiftProd>> getShiftProdMap(String organizeCode) {
        List<MesShiftProd> shiftProdList = shiftProdRepository.findByProperty(
                new String[]{MesExtConstWords.ORGANIZE_CODE, MesExtConstWords.IS_DELETED, MesExtConstWords.IS_VALID},
                new Object[]{organizeCode, CommonEnumUtil.TRUE_OR_FALSE.FALSE.getValue(), CommonEnumUtil.IS_VAILD.VAILD.getValue()});
        return CollectionUtils.isEmpty(shiftProdList) ? null :
                shiftProdList.stream().filter(o -> (null != o && !StringUtils.isEmpty(o.getProdCode()) && !StringUtils.isEmpty(o.getProdType())))
                        .collect(Collectors.groupingBy(o -> new StringJoiner(MesExtConstWords.AND).add(o.getProdType().toString()).add(o.getProdCode()).toString()));
    }

    private String getProdName(Map<String, List<MesShiftProd>> shiftProdMap, Integer prodType, String prodCode) {
        String key = new StringJoiner(MesExtConstWords.AND).add(prodType.toString()).add(prodCode).toString();
        return (!CollectionUtils.isEmpty(shiftProdMap) && shiftProdMap.containsKey(key)) ? shiftProdMap.get(key).get(0).getProdName() : null;
    }

    private Double getDataListStatistics(List<SxShiftProdCenterRecordStatisticsModel> dataList) {
        return CollectionUtils.isEmpty(dataList) ? new Double(0) :
                dataList.stream().filter(o -> null != o).mapToDouble(SxShiftProdCenterRecordStatisticsModel::getStatistics).sum();
    }

    private List<SxShiftProdCenterRecordStatisticsModel> getStatisticsDataList(List<SxShiftProdCenterRecordStatisticsModel> dataList) {
        return CollectionUtils.isEmpty(dataList) ? null :
                dataList.stream().filter(o -> (null != o && !StringUtils.isEmpty(o.getProdCode()) && !StringUtils.isEmpty(o.getProdType()) &&
                        (MesExtEnumUtil.SHIFT_PROD_TYPE.NORMAL_PROD.getValue() == o.getProdType() ||
                                MesExtEnumUtil.SHIFT_PROD_TYPE.UNNORMAL_PROD.getValue() == o.getProdType()))).collect(Collectors.toList());
    }

    private Map<Integer, List<SxShiftProdCenterRecordStatisticsModel>> getDataMapByProdType(List<SxShiftProdCenterRecordStatisticsModel> dataList) {
        return CollectionUtils.isEmpty(dataList) ? null :
                dataList.stream().filter(o -> (null != o && !StringUtils.isEmpty(o.getProdCode()) && !StringUtils.isEmpty(o.getProdType()) &&
                        (MesExtEnumUtil.SHIFT_PROD_TYPE.NORMAL_STOP_CENTER.getValue() == o.getProdType() || MesExtEnumUtil.SHIFT_PROD_TYPE.UNNORMAL_STOP_CENTER.getValue() == o.getProdType())))
                        .collect(Collectors.groupingBy(SxShiftProdCenterRecordStatisticsModel::getProdType));
    }

    private Map<Integer, List<Map<String, Object>>> getStatisticsDataMapByProdType(Map<Integer, List<SxShiftProdCenterRecordStatisticsModel>> curDataMapByProdType,
                                                                                   Map<Integer, List<SxShiftProdCenterRecordStatisticsModel>> lastDataMapByProdType,
                                                                                   Map<Integer, List<SxShiftProdCenterRecordStatisticsModel>> actualDataMapByProdType,
                                                                                   Map<Integer, List<Map<String, Object>>> statisticsDataMap, Map<String, List<MesShiftProd>> shiftProdMap,
                                                                                   Double curDataStatistics, Double lastDataStatistics, Double actualDataStatistics, MesExtEnumUtil.SHIFT_PROD_TYPE prodType) {

        Map<String, List<SxShiftProdCenterRecordStatisticsModel>> curDataMapByProdCode = getDataMapByProdCode(curDataMapByProdType, prodType.getValue());
        Map<String, List<SxShiftProdCenterRecordStatisticsModel>> lastDataMapByProdCode = getDataMapByProdCode(lastDataMapByProdType, prodType.getValue());
        Map<String, List<SxShiftProdCenterRecordStatisticsModel>> actualDataMapByProdCode = getDataMapByProdCode(actualDataMapByProdType, prodType.getValue());

        List<String> prodCodeList = getProdCodeList(curDataMapByProdCode, lastDataMapByProdCode, actualDataMapByProdCode);

        if (CollectionUtils.isEmpty(prodCodeList)) return statisticsDataMap;

        List<Map<String, Object>> statisticsDataList = new ArrayList<>();
        for (String prodCode : prodCodeList) {
            if (StringUtils.isEmpty(prodCode)) continue;

            List<SxShiftProdCenterRecordStatisticsModel> curDataList = CollectionUtils.isEmpty(curDataMapByProdCode) ? null : curDataMapByProdCode.get(prodCode);
            List<SxShiftProdCenterRecordStatisticsModel> lastDataList = CollectionUtils.isEmpty(lastDataMapByProdCode) ? null : lastDataMapByProdCode.get(prodCode);
            List<SxShiftProdCenterRecordStatisticsModel> actualDataList = CollectionUtils.isEmpty(actualDataMapByProdCode) ? null : actualDataMapByProdCode.get(prodCode);

            Map<String, Object> statisticsData = new HashMap<>();
            statisticsData.put(MesExtConstWords.PROD_TYPE, prodType.getValue());
            statisticsData.put("prodTypeName", prodType.getDescription());
            statisticsData.put(MesExtConstWords.PROD_CODE, prodCode);
            statisticsData.put(MesExtConstWords.PROD_NAME, getProdNameByFirstData(curDataList, lastDataList, actualDataList));
            statisticsData.put(MesExtConstWords.PLAN_RATE, format2RoundHalfUp(getPlanRate(shiftProdMap, prodType.getValue(), prodCode)));
            statisticsData.put("planRp", statisticsData.get(MesExtConstWords.PLAN_RATE) + MesExtConstWords.PERCENT);
            statisticsData.put("curRate", divThenMul100Format2RoundHalfUp(getDataListStatistics(curDataList), curDataStatistics));
            statisticsData.put("curRp", statisticsData.get("curRate") + MesExtConstWords.PERCENT);
            statisticsData.put("lastRate", MathOperation.compareTo(lastDataStatistics, new Double(0)) == 0 ?
                    statisticsData.get("curRate") : divThenMul100Format2RoundHalfUp(getDataListStatistics(lastDataList), lastDataStatistics));
            statisticsData.put("lastRp", statisticsData.get("lastRate") + MesExtConstWords.PERCENT);
            statisticsData.put("lastSubRate", sub(statisticsData, "curRate", "lastRate"));
            statisticsData.put("lastSubRp", format2((Double) statisticsData.get("lastSubRate")) + MesExtConstWords.PERCENT);
            statisticsData.put("bpSubRate", sub(statisticsData, "curRate", MesExtConstWords.PLAN_RATE));
            statisticsData.put("bpSubRp", format2((Double) statisticsData.get("bpSubRate")) + MesExtConstWords.PERCENT);
            statisticsData.put(MesExtConstWords.RESULT, getResult((Double) statisticsData.get("bpSubRate")));
            statisticsData.put("amountPlanRp", statisticsData.get("planRp"));
            statisticsData.put("amountActual", divThenMul100Format2RoundHalfUp(getDataListStatistics(actualDataList), actualDataStatistics));
            statisticsData.put("amountActualRp", statisticsData.get("amountActual") + MesExtConstWords.PERCENT);
            statisticsData.put("amountDiff", sub(statisticsData, "amountActual", MesExtConstWords.PLAN_RATE));
            statisticsData.put("amountDiffRp", format2((Double) statisticsData.get("amountDiff")) + MesExtConstWords.PERCENT);
            statisticsData.put("amountResult", getResult((Double) statisticsData.get("amountDiff")));

            statisticsDataList.add(statisticsData);
        }

        statisticsDataMap.put(prodType.getValue(), statisticsDataList);
        return statisticsDataMap;
    }

    private String getProdNameByFirstData(List<SxShiftProdCenterRecordStatisticsModel> curDataList,
                                          List<SxShiftProdCenterRecordStatisticsModel> lastDataList,
                                          List<SxShiftProdCenterRecordStatisticsModel> actualDataList) {
        if (!CollectionUtils.isEmpty(curDataList)) return curDataList.get(0).getProdName();
        if (!CollectionUtils.isEmpty(lastDataList)) return lastDataList.get(0).getProdName();
        if (!CollectionUtils.isEmpty(actualDataList)) return actualDataList.get(0).getProdName();
        return null;
    }

    private Double getPlanRate(Map<String, List<MesShiftProd>> shiftProdMap, Integer prodType, String prodCode) {
        String key = new StringJoiner(MesExtConstWords.AND).add(prodType.toString()).add(prodCode).toString();
        return (!CollectionUtils.isEmpty(shiftProdMap) && shiftProdMap.containsKey(key)) ? shiftProdMap.get(key).get(0).getPlanRate() : null;
    }

    private Double sub(Map<String, Object> statisticsData, String key1, String key2) {
        return MathOperation.sub(getDoubleValue(statisticsData, key1), getDoubleValue(statisticsData, key2));
    }

    private Double getDoubleValue(Map<String, Object> statisticsData, String key) {
        return ((BigDecimal)statisticsData.get(key)).doubleValue();
    }

    private BigDecimal divThenMul100Format2RoundHalfUp(Double d1, Double d2) {
        Double d = (MathOperation.compareTo(d1, new Double(0)) == 0 || MathOperation.compareTo(d2, new Double(0)) == 0)
                ? null : MathOperation.mul(MathOperation.div(d1, d2), new Double(100));
        return format2RoundHalfUp(d);
    }

    private BigDecimal format2RoundHalfUp(Double d) {
        if (StringUtils.isEmpty(d)) d = new Double(0);
        BigDecimal bd = new BigDecimal(d);
        bd = bd.setScale(2, BigDecimal.ROUND_HALF_UP);
        return bd;
    }

    private String format2(Double d) {
        return String.format("%.2f", d);
    }

    private String getResult(Double d) {
        return MathOperation.compareTo(d, new Double(0)) <= 0 ? "O" : "X";
    }

    private Map<String, List<SxShiftProdCenterRecordStatisticsModel>> getDataMapByProdCode(Map<Integer, List<SxShiftProdCenterRecordStatisticsModel>> dataMapByProdType, Integer prodType) {
        List<SxShiftProdCenterRecordStatisticsModel> curDataList = CollectionUtils.isEmpty(dataMapByProdType) ? null : dataMapByProdType.get(prodType);
        return CollectionUtils.isEmpty(curDataList) ? null :
                curDataList.stream().filter(o -> null != o).collect(Collectors.groupingBy(SxShiftProdCenterRecordStatisticsModel::getProdCode));
    }

    private List<String> getProdCodeList(Map<String, List<SxShiftProdCenterRecordStatisticsModel>> curDataMapByProdCode,
                                         Map<String, List<SxShiftProdCenterRecordStatisticsModel>> lastDataMapByProdCode,
                                         Map<String, List<SxShiftProdCenterRecordStatisticsModel>> actualDataMapByProdCode) {
        List<String> prodCodeList = new ArrayList<>();
        if (!CollectionUtils.isEmpty(curDataMapByProdCode)) prodCodeList.addAll(new ArrayList<>(curDataMapByProdCode.keySet()));
        if (!CollectionUtils.isEmpty(lastDataMapByProdCode)) prodCodeList.addAll(new ArrayList<>(lastDataMapByProdCode.keySet()));
        if (!CollectionUtils.isEmpty(actualDataMapByProdCode)) prodCodeList.addAll(new ArrayList<>(actualDataMapByProdCode.keySet()));
        return CollectionUtils.isEmpty(prodCodeList) ? null : prodCodeList.stream().filter(o -> null != o).distinct().collect(Collectors.toList());
    }

    private SxLossTimeMonthlyModel addAmountData(SxLossTimeMonthlyModel data, Map<Integer, List<Map<String, Object>>> statisticsDataMap) {

        if (CollectionUtils.isEmpty(statisticsDataMap)) return null;

        Double planRateAmount = new Double(0);
        Double curRateAmount = new Double(0);
        Double lastRateAmount = new Double(0);
        Double lastSubRateAmount = new Double(0);
        Double bpSubRateAmount = new Double(0);
        Double amountActualAdd = new Double(0);
        Double amountDiffAdd = new Double(0);

        Integer index = 1;
        for (Map.Entry<Integer, List<Map<String, Object>>> entry : statisticsDataMap.entrySet()) {
            if (null == entry) continue;
            List<Map<String, Object>> dataList = entry.getValue();
            Double planRate = dataList.stream().filter(o -> null != o).mapToDouble(o -> ((BigDecimal)o.get(MesExtConstWords.PLAN_RATE)).doubleValue()).sum();
            Double curRate = dataList.stream().filter(o -> null != o).mapToDouble(o -> ((BigDecimal)o.get("curRate")).doubleValue()).sum();
            Double lastRate = dataList.stream().filter(o -> null != o).mapToDouble(o -> ((BigDecimal)o.get("lastRate")).doubleValue()).sum();
            Double lastSubRate = dataList.stream().filter(o -> null != o).mapToDouble(o -> (Double) o.get("lastSubRate")).sum();
            Double bpSubRate = dataList.stream().filter(o -> null != o).mapToDouble(o -> (Double) o.get("bpSubRate")).sum();
            Double amountActual = dataList.stream().filter(o -> null != o).mapToDouble(o -> ((BigDecimal) o.get("amountActual")).doubleValue()).sum();
            Double amountDiff = dataList.stream().filter(o -> null != o).mapToDouble(o -> (Double) o.get("amountDiff")).sum();

            planRateAmount = MathOperation.add(planRateAmount, planRate);
            curRateAmount = MathOperation.add(curRateAmount, curRate);
            lastRateAmount = MathOperation.add(lastRateAmount, lastRate);
            lastSubRateAmount = MathOperation.add(lastSubRateAmount, lastSubRate);
            bpSubRateAmount = MathOperation.add(bpSubRateAmount, bpSubRate);
            amountActualAdd = MathOperation.add(amountActualAdd, amountActual);
            amountDiffAdd = MathOperation.add(amountDiffAdd, amountDiff);

            for (Map<String, Object> dataMap : dataList) {
                if (null == dataMap) continue;
                dataMap.put("index", index);
                index ++;
                data.addTableData(dataMap);
            }

            Map<String, Object> statisticsData = new HashMap<>();
            statisticsData.put("index", index);
            statisticsData.put(MesExtConstWords.PROD_TYPE, entry.getKey());
            statisticsData.put("prodTypeName", MesExtEnumUtil.SHIFT_PROD_TYPE.valueOfDescription(entry.getKey()));
            statisticsData.put(MesExtConstWords.PROD_NAME, "合计");
            putCommonData(statisticsData, planRate, curRate, lastRate, lastSubRate, bpSubRate, amountActual, amountDiff);
            data.addTableData(statisticsData);
            index ++;
        }

        Map<String, Object> statisticsData = new HashMap<>();
        statisticsData.put("index", index);
        statisticsData.put(MesExtConstWords.PROD_NAME, "总和");
        putCommonData(statisticsData, planRateAmount, curRateAmount, lastRateAmount, lastSubRateAmount, bpSubRateAmount, amountActualAdd, amountDiffAdd);
        data.addTableData(statisticsData);

        return data;
    }

    private void putCommonData(Map<String, Object> statisticsData, Double planRate, Double curRate, Double lastRate, Double lastSubRate, Double bpSubRate, Double amountActual, Double amountDiff) {
        statisticsData.put("planRp", format2(planRate) + MesExtConstWords.PERCENT);
        statisticsData.put("curRp", format2(curRate) + MesExtConstWords.PERCENT);
        statisticsData.put("lastRp", format2(lastRate) + MesExtConstWords.PERCENT);
        statisticsData.put("lastSubRp", format2(lastSubRate) + MesExtConstWords.PERCENT);
        statisticsData.put("bpSubRp", format2(bpSubRate) + MesExtConstWords.PERCENT);
        statisticsData.put(MesExtConstWords.RESULT, getResult(bpSubRate));
        statisticsData.put("amountPlanRp", statisticsData.get("planRp"));
        statisticsData.put("amountActualRp", format2(amountActual) + MesExtConstWords.PERCENT);
        statisticsData.put("amountDiffRp", format2(amountDiff) + MesExtConstWords.PERCENT);
        statisticsData.put("amountResult", getResult(amountDiff));
    }

    @Override
    public void doDownLoadLossTimeMonthly(HttpServletResponse resp, SxLossTimeMonthlyModel data) {

        queryLossTimeMonthly(data);

        checkData4DownLoad(data.getTableDataList());

        MesFile fileDb = moduleExcelManageService.getTemplateFile(data.getOrganizeCode(), SX_LOSS_TIME_MONTHLY_REPORT);

        File fileTemplate = null;
        File excelFile = null;
        String excelName = null;
        FileOutputStream fos = null;

        try {
            InputStream inputStream = FileExtUtil.getInputStream(fileDb.getFileUrl());
            if(inputStream == null)
                throw ImppExceptionBuilder.newInstance()
                        .setSystemID(CommonEnumUtil.SOFT_TYPE.MES.getCode())
                        .setErrorCode(ImppExceptionEnum.IO_EXCEPTION_FILE.getCode())
                        .setErrorDetail("导出模板[%s]对应的文件URL[%s]获取文件流异常", SX_LOSS_TIME_MONTHLY_REPORT, fileDb.getFileUrl())
                        .build();

            fileTemplate= File.createTempFile(String.valueOf(snowflakeIdMaker.nextId()), MesExtConstWords.DECIMAL_POINT_STR + fileDb.getFileType().replaceAll(String.valueOf((char)160), MesExtConstWords.EMPTY).trim());
            FileUtils.copyInputStreamToFile(inputStream, fileTemplate);

            Workbook workbook = SxExcelExtUtil.getWorkbook(fileTemplate);

            Sheet sheet = workbook.getSheetAt(MesExtConstWords.ZERO);

            SxExcelExtUtil.copyRow(workbook, sheet, TEMPLATE_LAST_ROW, TEMPLATE_LAST_ROW + 1, data.getTableDataList().size() - 1, true, null);

            setCellValue4ResultList(workbook, sheet, data.getTableDataList());

            fos = new FileOutputStream(fileTemplate);
            workbook.write(fos);
            fos.flush();

            //获取导出文件名称
            excelName = data.getTitle();

            //根据模板写入文件
            excelFile= File.createTempFile(excelName, MesExtConstWords.DECIMAL_POINT_STR + fileDb.getFileType().replaceAll(String.valueOf((char)160), MesExtConstWords.EMPTY).trim());

            OutputStream out = new FileOutputStream(excelFile);
            BufferedOutputStream bos = new BufferedOutputStream(out);
            ZipSecureFile.setMinInflateRatio(-1.0d);
            ExcelWriter excelWriter = EasyExcel.write(bos).withTemplate(fileTemplate).build();
            WriteSheet writeSheet = EasyExcel.writerSheet().build();

            //数据写入
            Map<String, Object> excelDataMap = new HashMap<>();
            excelDataMap.put(MesExtConstWords.TITLE, excelName);
            excelWriter.fill(excelDataMap, writeSheet);

            excelWriter.finish();
            bos.flush();

            SysFile file = imppFileService.upload(excelFile, CommonEnumUtil.SOFT_TYPE.MES.getValue());

            log.info("MES月度非作业汇总损失时间报表 --- DOWNLOAD SUCCESS --- [{}]URL:[{}] --- THREAD:[{}]", excelName, file.getFilePath(), Thread.currentThread().getName());

            resp.setCharacterEncoding("UTF-8");
            resp.setHeader("Content-Disposition", "attachment;filename=" + java.net.URLEncoder.encode(excelName, "UTF-8"));

            FileExtUtil.exportFile(resp, excelFile);

        } catch (IOException e) {
            e.printStackTrace();
            throw ImppExceptionBuilder.newInstance()
                    .setSystemID(CommonEnumUtil.SOFT_TYPE.MES.getCode())
                    .setErrorCode(ImppExceptionEnum.IO_EXCEPTION_FILE.getCode())
                    .setErrorDetail("创建文件异常" + excelName + ".xlsx")
                    .build();
        } catch (ImppBusiException e) {
            e.printStackTrace();
            throw ImppExceptionBuilder.newInstance()
                    .setSystemID(CommonEnumUtil.SOFT_TYPE.MES.getCode())
                    .setErrorCode(ImppExceptionEnum.IO_EXCEPTION_FILE.getCode())
                    .setErrorDetail(ExceptionUtils.getStackTrace(e))
                    .build();
        } catch (Exception e) {
            e.printStackTrace();
            throw ImppExceptionBuilder.newInstance()
                    .setSystemID(CommonEnumUtil.SOFT_TYPE.MES.getCode())
                    .setErrorCode(ImppExceptionEnum.IO_EXCEPTION_FILE.getCode())
                    .setErrorDetail(ExceptionUtils.getStackTrace(e))
                    .build();
        } finally {
            if(fileTemplate != null && fileTemplate.exists()) {
                fileTemplate.delete();
            }
            if(excelFile != null && excelFile.exists()) {
                excelFile.delete();
            }
            if (fos != null){
                try {
                    fos.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }

        }

    }

    private void checkData4DownLoad(List<Map<String, Object>> dataList) {
        if (CollectionUtils.isEmpty(dataList))
            throw ImppExceptionBuilder.newInstance()
                    .setSystemID(CommonEnumUtil.SOFT_TYPE.MES.getCode())
                    .setErrorCode(ImppExceptionEnum.VARIFY_EXCEPTION.getCode())
                    .setErrorDetail("当前条件查询暂无数据,导出失败")
                    .build();
    }

    private void setCellValue4ResultList(Workbook workbook, Sheet sheet, List<Map<String, Object>> dataList) {
        Integer rowNum = TEMPLATE_LAST_ROW;

        Object prodType = dataList.get(0).get(MesExtConstWords.PROD_TYPE);
        Integer index = TEMPLATE_LAST_ROW;
        for (Map<String, Object> result : dataList) {
            if (null == result) continue;
            if (StringUtils.isEmpty(result.get(MesExtConstWords.PROD_TYPE)) || !result.get(MesExtConstWords.PROD_TYPE).equals(prodType)) {
                addMergedRegionUnsafe(sheet, index, rowNum, 1);
                index = rowNum;
                prodType = result.get(MesExtConstWords.PROD_TYPE);
            }

            SxExcelExtUtil.setCellValue(sheet.getRow(rowNum), new Integer[]{1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12},
                    new String[]{result.containsKey("prodTypeName") ? (String) result.get("prodTypeName") : (String) result.get(MesExtConstWords.PROD_NAME),
                            (String) result.get(MesExtConstWords.PROD_NAME), (String) result.get("planRp"), (String) result.get("lastRp"),
                            (String) result.get("curRp"), (String) result.get("lastSubRp"), (String) result.get("bpSubRp"), (String) result.get(MesExtConstWords.RESULT),
                            (String) result.get("amountPlanRp"), (String) result.get("amountActualRp"), (String) result.get("amountDiffRp"), (String) result.get("amountResult")});

            if (StringUtils.isEmpty(result.get(MesExtConstWords.PROD_TYPE)) || StringUtils.isEmpty(result.get(MesExtConstWords.PROD_CODE)))
                new SxExcelExtUtil.SetCellStyle(workbook).setRows(sheet.getRow(rowNum)).bold().execute();

            handleCellStyle((String) result.get(MesExtConstWords.RESULT), workbook, sheet, rowNum, 8);
            handleCellStyle((String) result.get("amountResult"), workbook, sheet, rowNum, 12);

            if (StringUtils.isEmpty(result.get(MesExtConstWords.PROD_TYPE))) {
                SxExcelExtUtil.addMergedRegionUnsafe(sheet, index, index, 1, 2);
            }

            rowNum ++;

        }
    }

    private void handleCellStyle(String result, Workbook workbook, Sheet sheet, Integer rowNum, int cellNum) {
        SxExcelExtUtil.SetCellStyle style = new SxExcelExtUtil.SetCellStyle(workbook).setCells(sheet.getRow(rowNum).getCell(cellNum));
        if (result.equals("X")) style.color(HSSFColor.HSSFColorPredefined.RED.getIndex());
        style.bold().execute();
    }

    private void addMergedRegionUnsafe(Sheet sheet, Integer firstRow, Integer lastRow, int CellCol) {
        SxExcelExtUtil.addMergedRegionUnsafe(sheet, firstRow, lastRow - 1, CellCol, CellCol);
    }

    private void sxYearFlag(SxEChartsReportCommonDataModel data) {
        MesBusiConfig busiConfig = busiConfigService.getBusiConfigByCfgCode(data.getOrganizeCode(), MesExtConstWords.SX_REPORT_IS_PANASONIC_YEAR);
        if (null != busiConfig) data.setSxYearFlag(true);
    }

    private Map<String, String> getDateMap(SxEChartsReportCommonDataModel data) {
        Map<String, String> dateMap = null;
        if (DIMENSION.SEASON.getCode() == data.getDimension()) {
            Integer year;
            if (StringUtils.isEmpty(data.getYear())) data.setYear(Integer.valueOf(TimeTool.getYear()));
            dateMap = new HashMap<>();
            if (!data.getSxYearFlag()) {
                year = data.getYear() - 1;
                dateMap.put("0Q", new StringJoiner(MesExtConstWords.AND).add(year + MesExtConstWords.DATE_MM_DD_10_01).add(year + MesExtConstWords.DATE_MM_DD_12_31).toString());
                dateMap.put("1Q", new StringJoiner(MesExtConstWords.AND).add(data.getYear() + MesExtConstWords.DATE_MM_DD_01_01).add(data.getYear() + MesExtConstWords.DATE_MM_DD_03_31).toString());
                dateMap.put("2Q", new StringJoiner(MesExtConstWords.AND).add(data.getYear() + MesExtConstWords.DATE_MM_DD_04_01).add(data.getYear() + MesExtConstWords.DATE_MM_DD_06_30).toString());
                dateMap.put("3Q", new StringJoiner(MesExtConstWords.AND).add(data.getYear() + MesExtConstWords.DATE_MM_DD_07_01).add(data.getYear() + MesExtConstWords.DATE_MM_DD_09_30).toString());
                dateMap.put("4Q", new StringJoiner(MesExtConstWords.AND).add(data.getYear() + MesExtConstWords.DATE_MM_DD_10_01).add(data.getYear() + MesExtConstWords.DATE_MM_DD_12_31).toString());
            } else {
                year = data.getYear() + 1;
                dateMap.put("0Q", new StringJoiner(MesExtConstWords.AND).add(data.getYear() + MesExtConstWords.DATE_MM_DD_01_01).add(data.getYear() + MesExtConstWords.DATE_MM_DD_03_31).toString());
                dateMap.put("1Q", new StringJoiner(MesExtConstWords.AND).add(data.getYear() + MesExtConstWords.DATE_MM_DD_04_01).add(data.getYear() + MesExtConstWords.DATE_MM_DD_06_30).toString());
                dateMap.put("2Q", new StringJoiner(MesExtConstWords.AND).add(data.getYear() + MesExtConstWords.DATE_MM_DD_07_01).add(data.getYear() + MesExtConstWords.DATE_MM_DD_09_30).toString());
                dateMap.put("3Q", new StringJoiner(MesExtConstWords.AND).add(data.getYear() + MesExtConstWords.DATE_MM_DD_10_01).add(data.getYear() + MesExtConstWords.DATE_MM_DD_12_31).toString());
                dateMap.put("4Q", new StringJoiner(MesExtConstWords.AND).add(year + MesExtConstWords.DATE_MM_DD_01_01).add(year + MesExtConstWords.DATE_MM_DD_03_31).toString());
            }
        }
        return dateMap;
    }

    enum DIMENSION {

        YEAR(10, "年"),
        SEASON(20, "季"),
        MONTH(30, "月"),
        DAY(40, "日");

        private Integer code;
        private String description;

        DIMENSION(Integer code, String description) {
            this.code = code;
            this.description = description;
        }

        public static DIMENSION getByCode(int code) {
            DIMENSION[] var1 = values();
            int var2 = var1.length;

            for (int var3 = 0; var3 < var2; ++var3) {
                DIMENSION dimension = var1[var3];
                if (dimension.getCode() == code) {
                    return dimension;
                }
            }
            return null;
        }

        public int getCode() {
            return code;
        }

        public String getDescription() {
            return description;
        }

        public static String codeOfDescription(Integer code) {
            String tmp = null;
            for (int i = 0; i < values().length; i++) {
                if (values()[i].code == code) {
                    tmp = values()[i].description;
                }
            }
            return tmp;
        }

    }

}
