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.IProdOrgExtService;
import cn.estsh.i3plus.ext.mes.api.base.bu.IBusiConfigService;
import cn.estsh.i3plus.ext.mes.api.base.bu3.ISxCareerCorrelationCfgService;
import cn.estsh.i3plus.ext.mes.api.base.bu3.ISxOfficialLossDetailRecordService;
import cn.estsh.i3plus.ext.mes.api.base.bu3.ISxPartCategoryPlanRateService;
import cn.estsh.i3plus.ext.mes.api.busi.bu3.ISxOfficialLossAnalysisReportService;
import cn.estsh.i3plus.ext.mes.apiservice.dao.IPartExtDao;
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.*;
import cn.estsh.i3plus.ext.mes.pojo.model.bu3.*;
import cn.estsh.i3plus.ext.mes.pojo.repository.*;
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.openxml4j.util.ZipSecureFile;
import org.apache.poi.ss.usermodel.Row;
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 SxOfficialLossAnalysisReportService implements ISxOfficialLossAnalysisReportService {

    @Autowired
    private IBusiConfigService busiConfigService;

    @Autowired
    private ISxPartCategoryPlanRateService partCategoryPlanRateService;

    @Autowired
    private MesWorkCenterExtRepository workCenterExtRepository;

    @Autowired
    private MesShiftProdRepository shiftProdRepository;

    @Autowired
    private IProdOrgExtService prodOrgExtService;

    @Autowired
    private ISxCareerCorrelationCfgService careerCorrelationCfgService;

    @Autowired
    private MesShiftProdCenterRecordRepository shiftProdCenterRecordRepository;

    @Autowired
    private MesProdDataToStatisticsShiftProdRepository prodDataToStatisticsShiftProdRepository;

    @Autowired
    private MesOutPutStatisticsRepository outPutStatisticsRepository;

    @Autowired
    private MesProdDataToStatisticsOutPutRepository prodDataToStatisticsOutPutRepository;

    @Autowired
    private IPartExtDao partExtDao;

    @Autowired
    private ISxOfficialLossDetailRecordService officialLossDetailRecordService;

    @Autowired
    private IModuleExcelManageService moduleExcelManageService;

    @Autowired
    private SnowflakeIdMaker snowflakeIdMaker;

    @Autowired
    private ImppFileService imppFileService;

    private final static String SX_CAREER_OFFICIAL_LOSS_ANALYSIS_REPORT = "SX_CAREER_OFFICIAL_LOSS_ANALYSIS_REPORT";
    private final static String SX_OFFICIAL_LOSS_TREND_CHART_REPORT = "SX_OFFICIAL_LOSS_TREND_CHART_REPORT";

    @Override
    public SxOfficialLossAnalysisReportModel queryCareerOfficialLossAnalysis(SxOfficialLossAnalysisReportModel data) {

        handleMultipleChoice(data);

        Map<String, String> groupObjMap = DIMENSION.CAREER.code == data.getDimension() ? data.getCareerMap() : data.getCategoryMap();

        List<MesWorkCenterExt> workCenterExtList = getWorkCenterExtList(data);

        Map<String, MesWorkCenterExt> centerMap = getCenterMap(workCenterExtList);

        calcYmdTime(data);

        calcQueryTime(data);

        Map<String, String> careerPersonnelCostMap = StringUtils.isEmpty(data.getWorkTimeYmdStart()) ? null : prodOrgExtService.getCareerPersonnelCostMap(data.getOrganizeCode());

        Map<String, Double> careerCorrelationCfgMap = getCareerCorrelationCfgMap(data, true);

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

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

        List<SxProdDataToStatisticsOutPutModel> outPutStatisticsList = getOutPutStatisticsList(data, centerMap, careerPersonnelCostMap);

        List<SxOfficialLossDetailRecordModel> officialLossDetailRecordList = getOfficialLossDetailRecordList(data, centerMap);

        handleTableName(data);

        handleTableData(data, groupObjMap, careerCorrelationCfgMap, careerPersonnelCostMap, shiftProdMap, workCenterExtList,
                getRecordStatisticsMapByDimension(recordStatisticsList, data.getDimension()), getOutPutStatisticsMapByDimension(outPutStatisticsList, data.getDimension()), getOfficialLossDetailRecordMapByDimension(officialLossDetailRecordList, data.getDimension()));

        return data;
    }

    private void handleMultipleChoice(SxOfficialLossAnalysisReportModel data) {
        Map<String, String> careerMap = !StringUtils.isEmpty(data.getCareerCodeArr()) ? getMultipleChoiceMap(data.getCareerCodeArr(), data.getCareerNameArr()) : null;
        Map<String, String> categoryMap = !StringUtils.isEmpty(data.getCategoryCodeArr()) ? getMultipleChoiceMap(data.getCategoryCodeArr(), data.getCategoryNameArr()) : null;
        if (DIMENSION.CAREER.code == data.getDimension() && CollectionUtils.isEmpty(careerMap)) careerMap = busiConfigService.getConfigMap(data.getOrganizeCode(), MesExtConstWords.SX_CAREER_CFG);;
        if (DIMENSION.CATEGORY.code == data.getDimension() && CollectionUtils.isEmpty(categoryMap)) categoryMap = partCategoryPlanRateService.getCategoryMap(data.getOrganizeCode());
        data.careerMap(careerMap).categoryMap(categoryMap).workCenterCodeList(handleMultipleChoice(data.getWorkCenterCodeArr()));
    }

    private List<String> handleMultipleChoice(String param) {
        return StringUtils.isEmpty(param) ? null : Arrays.asList(param.split(MesExtConstWords.COMMA));
    }

    private Map<String, String> getMultipleChoiceMap(String codeArr, String nameArr) {
        Map<String, String> careerMap = new LinkedHashMap<>();
        List<String> codes = handleMultipleChoice(codeArr);
        List<String> names = handleMultipleChoice(nameArr);
        for (int i = 0; i < codes.size(); i ++) {
            careerMap.put(codes.get(i), names.get(i));
        }
        return careerMap;
    }

    private List<MesWorkCenterExt> getWorkCenterExtList(SxOfficialLossAnalysisReportModel data) {
        DdlPackBean packBean = DdlPackBean.getDdlPackBean(data.getOrganizeCode());
        DdlPreparedPack.getStringEqualPack(data.getIsDont(), MesExtConstWords.IS_DONT, packBean);
        DdlPreparedPack.getStringEqualPack(data.getDontClass(), MesExtConstWords.DONT_CLASS, packBean);
        List<MesWorkCenterExt> workCenterExtList = workCenterExtRepository.findByHqlWhere(packBean);
        if (CollectionUtils.isEmpty(data.getWorkCenterCodeList())) return workCenterExtList;
        return CollectionUtils.isEmpty(workCenterExtList) ? null :
                workCenterExtList.stream().filter(o -> (null != o && data.getWorkCenterCodeList().contains(o.getWorkCenterCode()))).collect(Collectors.toList());
    }

    private Map<String, MesWorkCenterExt> getCenterMap(List<MesWorkCenterExt> workCenterExtList) {
        return CollectionUtils.isEmpty(workCenterExtList) ? null :
                workCenterExtList.stream().filter(o -> null != o).collect(Collectors.toMap(MesWorkCenterExt::getWorkCenterCode, o -> o));
    }

    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) {
        if (CollectionUtils.isEmpty(shiftProdMap)) return null;
        String key = new StringJoiner(MesExtConstWords.AND).add(prodType.toString()).add(prodCode).toString();
        return shiftProdMap.containsKey(key) ? shiftProdMap.get(key).get(0).getProdName() : null;
    }

    private void calcYmdTime(SxOfficialLossAnalysisReportModel data) {
        data.setYmdStart(data.getWorkTimeStart() + MesExtConstWords.DATE_DD_01);
        data.setYmdEnd(getMonthLastDay(data.getWorkTimeEnd()));
    }

    private String getMonthLastDay(String month) {
        return TimeTool.timeCalc(
                TimeTool.stringParseToDate(
                        (TimeTool.timeCalc(TimeTool.stringParseToDate(month, MesExtConstWords.DATE_FORMAT_YM), Calendar.MONTH, 1, MesExtConstWords.DATE_FORMAT_YM) + MesExtConstWords.DATE_DD_01),
                        MesExtConstWords.DATE_FORMAT),
                Calendar.DATE, -1, MesExtConstWords.DATE_FORMAT);
    }

    private void calcQueryTime(SxOfficialLossAnalysisReportModel data) {
        data.curMonth(TimeTool.getToday().substring(0, 7));
        String curMonthBegin = data.getCurMonth() + MesExtConstWords.DATE_DD_01;
        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(TimeTool.timeCalc(TimeTool.stringParseToDate(data.getCurMonth(), MesExtConstWords.DATE_FORMAT_YM), Calendar.MONTH, -1, MesExtConstWords.DATE_FORMAT_YM));
            data.setWorkTimeYmdStart(curMonthBegin);
            data.setWorkTimeYmdEnd(data.getYmdEnd());
        }
    }

    private Map<String, Double> getCareerCorrelationCfgMap(SxOfficialLossAnalysisReportModel data, Boolean flag) {
        List<MesCareerCorrelationCfg> careerCorrelationCfgList1 = careerCorrelationCfgService.getCareerCorrelationCfgListOnlyByDataType(data.getOrganizeCode(), MesExtEnumUtil.CAREER_CORRELATION_CFG_DATA_TYPE.PROD_AMOUNT.getValue(), data.getWorkTimeStart(), data.getWorkTimeEnd());
        List<MesCareerCorrelationCfg> careerCorrelationCfgList2 = careerCorrelationCfgService.getCareerCorrelationCfgListOnlyByDataType(data.getOrganizeCode(), MesExtEnumUtil.CAREER_CORRELATION_CFG_DATA_TYPE.PLAN_LOSS.getValue(), data.getWorkTimeStart(), data.getWorkTimeEnd());
        if (CollectionUtils.isEmpty(careerCorrelationCfgList1) && CollectionUtils.isEmpty(careerCorrelationCfgList2)) return null;
        List<MesCareerCorrelationCfg> careerCorrelationCfgList = new ArrayList<>();
        if (!CollectionUtils.isEmpty(careerCorrelationCfgList1)) careerCorrelationCfgList.addAll(careerCorrelationCfgList1);
        if (!CollectionUtils.isEmpty(careerCorrelationCfgList2)) careerCorrelationCfgList.addAll(careerCorrelationCfgList2);
        if (!CollectionUtils.isEmpty(data.getCareerMap())) {
            careerCorrelationCfgList = CollectionUtils.isEmpty(careerCorrelationCfgList) ? null :
                    careerCorrelationCfgList.stream().filter(o -> (null != o && !StringUtils.isEmpty(o.getCareerCode()) && !StringUtils.isEmpty(o.getCategoryCode()) && data.getCareerMap().containsKey(o.getCareerCode()))).distinct().collect(
                            Collectors.collectingAndThen(Collectors.toCollection(() -> new TreeSet<>(Comparator.comparing(o -> new StringJoiner(MesExtConstWords.AND).add(o.getCareerCode()).add(o.getCategoryCode()).add(o.getDataType()).add(o.getDataKey()).toString()))), ArrayList::new)
                    );
        }
        if (!CollectionUtils.isEmpty(data.getCategoryMap()))
            careerCorrelationCfgList = CollectionUtils.isEmpty(careerCorrelationCfgList) ? null :
                    careerCorrelationCfgList.stream().filter(o -> (null != o && !StringUtils.isEmpty(o.getCareerCode()) && !StringUtils.isEmpty(o.getCategoryCode()) && data.getCategoryMap().containsKey(o.getCategoryCode()))).distinct().collect(
                            Collectors.collectingAndThen(Collectors.toCollection(() -> new TreeSet<>(Comparator.comparing(o -> new StringJoiner(MesExtConstWords.AND).add(o.getCategoryCode()).add(o.getDataType()).add(o.getDataKey()).toString()))), ArrayList::new)
                    );

        if (flag) {
            if (DIMENSION.CAREER.code == data.getDimension())
                return CollectionUtils.isEmpty(careerCorrelationCfgList) ? null :
                        careerCorrelationCfgList.stream().collect(Collectors.groupingBy(o -> new StringJoiner(MesExtConstWords.AND).add(o.getCareerCode()).add(o.getDataType()).add(o.getDataKey()).toString(), Collectors.collectingAndThen(Collectors.summarizingDouble(o -> new Double(o.getCfgValue())), DoubleSummaryStatistics::getSum)));
            else
                return CollectionUtils.isEmpty(careerCorrelationCfgList) ? null :
                        careerCorrelationCfgList.stream().filter(o -> null != o).collect(Collectors.toMap(o -> new StringJoiner(MesExtConstWords.AND).add(o.getCategoryCode()).add(o.getDataType()).add(o.getDataKey()).toString(), o -> new Double(o.getCfgValue())));
        } else
            return CollectionUtils.isEmpty(careerCorrelationCfgList) ? null :
                    careerCorrelationCfgList.stream().collect(Collectors.groupingBy(o -> new StringJoiner(MesExtConstWords.AND).add(o.getDataType()).add(o.getDataKey()).toString(), Collectors.collectingAndThen(Collectors.summarizingDouble(o -> new Double(o.getCfgValue())), DoubleSummaryStatistics::getSum)));
    }

    private List<SxOfficialLossDetailRecordModel> getOfficialLossDetailRecordList(SxOfficialLossAnalysisReportModel data, Map<String, MesWorkCenterExt> centerMap) {
        List<SxOfficialLossDetailRecordModel> officialLossDetailRecordList = officialLossDetailRecordService.getOfficialLossDetailRecordList(new SxOfficialLossDetailRecordModel(data.getOrganizeCode(), data.getWorkTimeStart(), data.getWorkTimeEnd()));
        officialLossDetailRecordList = CollectionUtils.isEmpty(officialLossDetailRecordList) ? officialLossDetailRecordList :
                officialLossDetailRecordList.stream().filter(o -> (null != o && centerMap.containsKey(o.getWorkCenterCode()))).collect(Collectors.toList());
        officialLossDetailRecordList = (CollectionUtils.isEmpty(data.getCareerMap()) || CollectionUtils.isEmpty(officialLossDetailRecordList)) ? officialLossDetailRecordList :
                officialLossDetailRecordList.stream().filter(o -> (null != o && !StringUtils.isEmpty(o.getCareerCode()) && data.getCareerMap().containsKey(o.getCareerCode()))).collect(Collectors.toList());
        officialLossDetailRecordList = (CollectionUtils.isEmpty(data.getCategoryMap()) || CollectionUtils.isEmpty(officialLossDetailRecordList)) ? officialLossDetailRecordList :
                officialLossDetailRecordList.stream().filter(o -> (null != o && !StringUtils.isEmpty(o.getCategoryCode()) && data.getCategoryMap().containsKey(o.getCategoryCode()))).collect(Collectors.toList());
        return officialLossDetailRecordList;
    }

    private List<SxShiftProdCenterRecordStatisticsModel> getRecordStatisticsList(SxOfficialLossAnalysisReportModel data, Map<String, MesWorkCenterExt> centerMap, Map<String, String> careerPersonnelCostMap, Map<String, List<MesShiftProd>> shiftProdMap) {
        List<SxShiftProdCenterRecordStatisticsModel> recordStatisticsList = new ArrayList<>();
        if (!StringUtils.isEmpty(data.getWorkTimeYmdStart())) {
            List<SxShiftProdCenterRecordStatisticsModel> resultList = getShiftProdCenterRecordList(data, data.getWorkTimeYmdStart(), data.getWorkTimeYmdEnd(), careerPersonnelCostMap, 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);
        }
        recordStatisticsList = CollectionUtils.isEmpty(recordStatisticsList) ? recordStatisticsList :
                recordStatisticsList.stream().filter(o -> (null != o && centerMap.containsKey(o.getWorkCenterCode()))).collect(Collectors.toList());
        recordStatisticsList = (CollectionUtils.isEmpty(data.getCareerMap()) || CollectionUtils.isEmpty(recordStatisticsList)) ? recordStatisticsList :
                recordStatisticsList.stream().filter(o -> (null != o && !StringUtils.isEmpty(o.getCareerCode()) && data.getCareerMap().containsKey(o.getCareerCode()))).collect(Collectors.toList());
        recordStatisticsList = (CollectionUtils.isEmpty(data.getCategoryMap()) || CollectionUtils.isEmpty(recordStatisticsList)) ? recordStatisticsList :
                recordStatisticsList.stream().filter(o -> (null != o && !StringUtils.isEmpty(o.getCategoryCode()) && data.getCategoryMap().containsKey(o.getCategoryCode()))).collect(Collectors.toList());
        return recordStatisticsList;
    }

    private List<SxProdDataToStatisticsOutPutModel> getOutPutStatisticsList(SxOfficialLossAnalysisReportModel data, Map<String, MesWorkCenterExt> centerMap, Map<String, String> careerPersonnelCostMap) {
        List<SxProdDataToStatisticsOutPutModel> outPutStatisticsList = new ArrayList<>();
        if (!StringUtils.isEmpty(data.getWorkTimeYmdStart())) {
            List<SxProdDataToStatisticsOutPutModel> resultList = getOutPutStatisticsRecordList(data, data.getWorkTimeYmdStart(), data.getWorkTimeYmdEnd(), careerPersonnelCostMap);
            if (!CollectionUtils.isEmpty(resultList)) outPutStatisticsList.addAll(resultList);
        }
        if (!StringUtils.isEmpty(data.getStatisticsYmStart())) {
            List<SxProdDataToStatisticsOutPutModel> resultList = getProdDataOutPutStatisticsList(data, data.getStatisticsYmStart(), data.getStatisticsYmEnd());
            if (!CollectionUtils.isEmpty(resultList)) outPutStatisticsList.addAll(resultList);
        }
        outPutStatisticsList = CollectionUtils.isEmpty(outPutStatisticsList) ? outPutStatisticsList :
                outPutStatisticsList.stream().filter(o -> (null != o && centerMap.containsKey(o.getWorkCenterCode()))).collect(Collectors.toList());
        outPutStatisticsList = (CollectionUtils.isEmpty(data.getCareerMap()) || CollectionUtils.isEmpty(outPutStatisticsList)) ? outPutStatisticsList :
                outPutStatisticsList.stream().filter(o -> (null != o && !StringUtils.isEmpty(o.getCareerCode()) && data.getCareerMap().containsKey(o.getCareerCode()))).collect(Collectors.toList());
        outPutStatisticsList = (CollectionUtils.isEmpty(data.getCategoryMap()) || CollectionUtils.isEmpty(outPutStatisticsList)) ? outPutStatisticsList :
                outPutStatisticsList.stream().filter(o -> (null != o && !StringUtils.isEmpty(o.getCategoryCode()) && data.getCategoryMap().containsKey(o.getCategoryCode()))).collect(Collectors.toList());
        return outPutStatisticsList;
    }

    private List<SxShiftProdCenterRecordStatisticsModel> getShiftProdCenterRecordList(SxEChartsReportCommonDataModel data, String workTimeStart, String workTimeEnd, Map<String, String> careerPersonnelCostMap, Map<String, List<MesShiftProd>> shiftProdMap) {
        DdlPackBean packBean = DdlPackBean.getDdlPackBean(data.getOrganizeCode());
        DdlPreparedPack.timeBuilder(workTimeStart, workTimeEnd, MesExtConstWords.WORK_TIME, packBean, false);
        List<MesShiftProdCenterRecord> shiftProdCenterRecordList = shiftProdCenterRecordRepository.findByHqlWhere(packBean);
        if (CollectionUtils.isEmpty(shiftProdCenterRecordList)) return null;
        List<SxShiftProdCenterRecordStatisticsModel> resultList = new ArrayList<>();
        for (MesShiftProdCenterRecord item : shiftProdCenterRecordList) {
            if (null == item || StringUtils.isEmpty(item.getProdType())) continue;
            SxShiftProdCenterRecordStatisticsModel result = new SxShiftProdCenterRecordStatisticsModel();
            BeanUtils.copyProperties(item, result);
            result.setProdName(getProdName(shiftProdMap, result.getProdType(), result.getProdCode()));
            result.setGroupInfo(result.getWorkTime().substring(0, 7));
            try {
                result.setStatistics(MathOperation.mul(new Double(TimeTool.getSecoundsBetweenTime(2, item.getStartTime(), item.getEndTime())), item.getWorkerQty()));
            } catch (ParseException e) {
                result.setStatistics(new Double(0));
            }
            result.setPersonnelCost((!StringUtils.isEmpty(result.getCareerCode()) && !CollectionUtils.isEmpty(careerPersonnelCostMap) && careerPersonnelCostMap.containsKey(result.getCareerCode())) ? careerPersonnelCostMap.get(result.getCareerCode()) : MesExtConstWords.ZERO_STR);
            resultList.add(result);
        }
        return resultList;
    }

    private List<SxProdDataToStatisticsOutPutModel> getOutPutStatisticsRecordList(SxEChartsReportCommonDataModel data, String workTimeStart, String workTimeEnd, Map<String, String> careerPersonnelCostMap) {
        DdlPackBean packBean = DdlPackBean.getDdlPackBean(data.getOrganizeCode());
        DdlPreparedPack.getNumEqualPack(MesExtEnumUtil.WORK_ORDER_TYPE.STANDARD_ORDER.getValue(), MesExtConstWords.WORK_ORDER_TYPE, packBean);
        DdlPreparedPack.timeBuilder(workTimeStart, workTimeEnd, MesExtConstWords.WORK_TIME, packBean, false);
        List<MesOutPutStatistics> outPutStatisticsList = outPutStatisticsRepository.findByHqlWhere(packBean);
        if (CollectionUtils.isEmpty(outPutStatisticsList)) return null;

        Map<String, Double> partStandardWorkMap = partExtDao.queryPartExtStandardWorkMap(data.getOrganizeCode());

        List<SxProdDataToStatisticsOutPutModel> resultList = new ArrayList<>();
        for (MesOutPutStatistics item : outPutStatisticsList) {
            if (null == item) continue;
            SxProdDataToStatisticsOutPutModel result = new SxProdDataToStatisticsOutPutModel();
            BeanUtils.copyProperties(item, result);
            result.setGroupInfo(result.getWorkTime().substring(0, 7));
            result.setStandardWork((!CollectionUtils.isEmpty(partStandardWorkMap) && partStandardWorkMap.containsKey(result.getPartNo())) ? partStandardWorkMap.get(result.getPartNo()) : new Double(0));
            result.setStatistics(MathOperation.mul(getUnRemakeQty(item.getQty(), item.getRemakeQty()), result.getStandardWork()));
            result.setPersonnelCost((!StringUtils.isEmpty(result.getCareerCode()) && !CollectionUtils.isEmpty(careerPersonnelCostMap) && careerPersonnelCostMap.containsKey(result.getCareerCode())) ? careerPersonnelCostMap.get(result.getCareerCode()) : MesExtConstWords.ZERO_STR);
            resultList.add(result);
        }
        return resultList;
    }

    private Double getUnRemakeQty(Double qty, Double remakeQty) {
        if (StringUtils.isEmpty(remakeQty) || MathOperation.compareTo(remakeQty, new Double(0)) == 0) return qty;
        return MathOperation.sub(qty, remakeQty);
    }

    private List<SxShiftProdCenterRecordStatisticsModel> getShiftProdCenterRecordStatisticsList(SxEChartsReportCommonDataModel data, String workTimeStart, String workTimeEnd, Map<String, List<MesShiftProd>> shiftProdMap) {
        DdlPackBean packBean = DdlPackBean.getDdlPackBean(data.getOrganizeCode());
        DdlPreparedPack.timeBuilder(workTimeStart, workTimeEnd, MesExtConstWords.WORK_TIME, packBean, false);
        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);
        List<MesProdDataToStatisticsShiftProd> prodDataToStatisticsShiftProdList = prodDataToStatisticsShiftProdRepository.findByHqlWhere(packBean);
        if (CollectionUtils.isEmpty(prodDataToStatisticsShiftProdList)) return null;
        List<SxShiftProdCenterRecordStatisticsModel> resultList = new ArrayList<>();
        for (MesProdDataToStatisticsShiftProd item : prodDataToStatisticsShiftProdList) {
            if (null == item || StringUtils.isEmpty(item.getProdType())) continue;
            SxShiftProdCenterRecordStatisticsModel result = new SxShiftProdCenterRecordStatisticsModel();
            BeanUtils.copyProperties(item, result);
            result.setProdName(getProdName(shiftProdMap, result.getProdType(), result.getProdCode()));
            result.setGroupInfo(result.getWorkTime());
            resultList.add(result);
        }
        return resultList;
    }

    private List<SxProdDataToStatisticsOutPutModel> getProdDataOutPutStatisticsList(SxEChartsReportCommonDataModel data, String workTimeStart, String workTimeEnd) {
        DdlPackBean packBean = DdlPackBean.getDdlPackBean(data.getOrganizeCode());
        DdlPreparedPack.getNumEqualPack(MesExtEnumUtil.WORK_ORDER_TYPE.STANDARD_ORDER.getValue(), MesExtConstWords.WORK_ORDER_TYPE, packBean);
        DdlPreparedPack.getStringEqualPack(data.getWorkCenterCode(), MesExtConstWords.WORK_CENTER_CODE, packBean);
        DdlPreparedPack.getStringEqualPack(MesExtConstWords.MONTH, MesExtConstWords.DATA_TYPE, packBean);
        DdlPreparedPack.getStringEqualPack(MesExtEnumUtil.JOB_STATISTICS_PROD_DATA_KEY.OUT_PUT_MONTH_KEY1.getValue(), MesExtConstWords.DATA_KEY, packBean);
        DdlPreparedPack.timeBuilder(workTimeStart, workTimeEnd, MesExtConstWords.WORK_TIME, packBean, false);
        List<MesProdDataToStatisticsOutPut> prodDataToStatisticsOutPutList = prodDataToStatisticsOutPutRepository.findByHqlWhere(packBean);
        if (CollectionUtils.isEmpty(prodDataToStatisticsOutPutList)) return null;
        List<SxProdDataToStatisticsOutPutModel> resultList = new ArrayList<>();
        for (MesProdDataToStatisticsOutPut item : prodDataToStatisticsOutPutList) {
            if (null == item) continue;
            SxProdDataToStatisticsOutPutModel result = new SxProdDataToStatisticsOutPutModel();
            BeanUtils.copyProperties(item, result);
            result.setGroupInfo(result.getWorkTime());
            resultList.add(result);
        }
        return resultList;
    }

    private void handleTableName(SxOfficialLossAnalysisReportModel data) {
        data.title(String.format("%s~%s工场损失明细", data.getWorkTimeStart(), data.getWorkTimeEnd()))
                .addTableName("content2", MesExtConstWords.EMPTY).addTableName("content3", "项目类别").addCellName("项目类别");
        String startMonth = data.getWorkTimeStart();
        for (int i = 1; i <= 12; i ++) {
            data.addTableName(startMonth);
            startMonth = TimeTool.timeCalc(TimeTool.stringParseToDate(startMonth, MesExtConstWords.DATE_FORMAT_YM), Calendar.MONTH, 1, MesExtConstWords.DATE_FORMAT_YM);
            if (startMonth.compareTo(data.getWorkTimeEnd()) > 0) break;
        }
        data.addTableName("year", "年度").addCellField("year").addCellName("年度");
    }

    private void handleTableData(SxOfficialLossAnalysisReportModel data,
                                 Map<String, String> groupObjMap,
                                 Map<String, Double> careerCorrelationCfgMap,
                                 Map<String, String> careerPersonnelCostMap,
                                 Map<String, List<MesShiftProd>> shiftProdMap,
                                 List<MesWorkCenterExt> workCenterExtList,
                                 Map<String, List<SxShiftProdCenterRecordStatisticsModel>> recordStatisticsMapByDimension,
                                 Map<String, List<SxProdDataToStatisticsOutPutModel>> outPutStatisticsMapByDimension,
                                 Map<String, List<SxOfficialLossDetailRecordModel>> officialLossDetailRecordMapByDimension) {

        for (Map.Entry<String, String> entry : groupObjMap.entrySet()) {

            String careerCode = DIMENSION.CAREER.code == data.getDimension() ? entry.getKey() : getCareerCodeByCategoryCode(entry.getKey(), workCenterExtList);

            List<SxShiftProdCenterRecordStatisticsModel> recordStatisticsListByGroupObj = getRecordStatisticsListByGroupObj(recordStatisticsMapByDimension, entry.getKey());

            Map<String, List<SxShiftProdCenterRecordStatisticsModel>> recordStatisticsMapByDate = getRecordStatisticsMapByDate(recordStatisticsListByGroupObj);

            List<SxShiftProdCenterRecordStatisticsModel> recordStatisticsListByGroupObj4Stop = getShiftProdDataListByFlag(recordStatisticsListByGroupObj, false);

            Map<String, List<SxShiftProdCenterRecordStatisticsModel>> recordStatisticsMapByDate4Stop = getRecordStatisticsMapByDate(recordStatisticsListByGroupObj4Stop);

            Map<String, List<SxProdDataToStatisticsOutPutModel>> outPutStatisticsMapByDate = getOutPutStatisticsMapByDate(outPutStatisticsMapByDimension, entry.getKey());

            List<SxOfficialLossDetailRecordModel> officialLossDetailRecordListByGroupObj = getOfficialLossDetailRecordListByGroupObj(officialLossDetailRecordMapByDimension, entry.getKey());

            SxOfficialLossAnalysisReportModel analysisData = new SxOfficialLossAnalysisReportModel();

            analysisData.groupObjName(entry.getValue()).addTableName("content1", entry.getValue()).addTableName(data.getTableNameList());

            Map<String, String> tableData2ProdAmount = addTableData2ProdAmount(data, analysisData, entry.getKey(), careerCorrelationCfgMap);

            Map<String, String> tableData2PersonnelCost = addTableData2PersonnelCost(data, analysisData,
                    recordStatisticsMapByDate, outPutStatisticsMapByDate, (CollectionUtils.isEmpty(careerPersonnelCostMap) || StringUtils.isEmpty(careerCode)) ? null : careerPersonnelCostMap.get(careerCode));

            Map<String, String> tableData2PLossRate = addTableData2LossRate(data, analysisData);

            addTableData2ProdEnergyRateLoss(data, analysisData, recordStatisticsMapByDate, outPutStatisticsMapByDate, tableData2PersonnelCost);

            addTableData2Record(data, analysisData, shiftProdMap, recordStatisticsListByGroupObj4Stop);

            addTableData2RecordStatistics(data, analysisData, recordStatisticsMapByDate4Stop, tableData2PersonnelCost);

            addTableData2OfficialLoss(data, analysisData, officialLossDetailRecordListByGroupObj);

            addTableData2OfficialLossStatistics(data, analysisData, officialLossDetailRecordListByGroupObj);

            Map<String, String> tableData2FinalStatistics = addTableData2FinalStatistics(data, analysisData);

            addTableData2LossRate(data, analysisData, tableData2PLossRate, tableData2FinalStatistics, tableData2ProdAmount);

            data.addAnalysisData(analysisData);
        }
    }

    private String getCareerCodeByCategoryCode(String categoryCode, List<MesWorkCenterExt> workCenterExtList) {
        try {
            return workCenterExtList.stream().filter(o -> (null != o && !StringUtils.isEmpty(o.getCareerCode()) && !StringUtils.isEmpty(o.getCategoryCode()) && o.getCategoryCode().equals(categoryCode))).findFirst().get().getCareerCode();
        } catch (Exception e) {
            return null;
        }
    }

    private Map<String, List<SxShiftProdCenterRecordStatisticsModel>> getRecordStatisticsMapByProdCode(List<SxShiftProdCenterRecordStatisticsModel> recordStatisticsList) {
        return CollectionUtils.isEmpty(recordStatisticsList) ? null : recordStatisticsList.stream().filter(o -> null != o).collect(Collectors.groupingBy(SxShiftProdCenterRecordStatisticsModel::getProdCode));
    }

    private Map<String, List<SxShiftProdCenterRecordStatisticsModel>> getRecordStatisticsMapByDimension(List<SxShiftProdCenterRecordStatisticsModel> recordStatisticsList, Integer dimension) {
        if (CollectionUtils.isEmpty(recordStatisticsList)) return null;
        return DIMENSION.CAREER.code == dimension
                ? recordStatisticsList.stream().filter(o -> null != o).collect(Collectors.groupingBy(SxShiftProdCenterRecordStatisticsModel::getCareerCode))
                : recordStatisticsList.stream().filter(o -> null != o).collect(Collectors.groupingBy(SxShiftProdCenterRecordStatisticsModel::getCategoryCode));
    }

    private List<SxShiftProdCenterRecordStatisticsModel> getRecordStatisticsListByGroupObj(Map<String, List<SxShiftProdCenterRecordStatisticsModel>> recordStatisticsMapByDimension, String groupObj) {
        return (CollectionUtils.isEmpty(recordStatisticsMapByDimension) || !recordStatisticsMapByDimension.containsKey(groupObj)) ? null : recordStatisticsMapByDimension.get(groupObj);
    }

    private Map<String, List<SxShiftProdCenterRecordStatisticsModel>> getRecordStatisticsMapByDate(List<SxShiftProdCenterRecordStatisticsModel> recordStatisticsList) {
        return CollectionUtils.isEmpty(recordStatisticsList) ? null : recordStatisticsList.stream().filter(o -> null != o).collect(Collectors.groupingBy(SxShiftProdCenterRecordStatisticsModel::getGroupInfo));
    }

    private Map<String, List<SxProdDataToStatisticsOutPutModel>> getOutPutStatisticsMapByDimension(List<SxProdDataToStatisticsOutPutModel> outPutStatisticsList, Integer dimension) {
        if (CollectionUtils.isEmpty(outPutStatisticsList)) return null;
        return DIMENSION.CAREER.code == dimension
                ? outPutStatisticsList.stream().filter(o -> null != o).collect(Collectors.groupingBy(SxProdDataToStatisticsOutPutModel::getCareerCode))
                : outPutStatisticsList.stream().filter(o -> null != o).collect(Collectors.groupingBy(SxProdDataToStatisticsOutPutModel::getCategoryCode));
    }

    private Map<String, List<SxProdDataToStatisticsOutPutModel>> getOutPutStatisticsMapByDate(List<SxProdDataToStatisticsOutPutModel> outPutStatisticsList) {
        return CollectionUtils.isEmpty(outPutStatisticsList) ? null : outPutStatisticsList.stream().filter(o -> null != o).collect(Collectors.groupingBy(SxProdDataToStatisticsOutPutModel::getGroupInfo));
    }

    private Map<String, List<SxProdDataToStatisticsOutPutModel>> getOutPutStatisticsMapByDate(Map<String, List<SxProdDataToStatisticsOutPutModel>> outPutStatisticsMapByDimension, String groupObj) {
        return (CollectionUtils.isEmpty(outPutStatisticsMapByDimension) || !outPutStatisticsMapByDimension.containsKey(groupObj)) ? null : getOutPutStatisticsMapByDate(outPutStatisticsMapByDimension.get(groupObj));
    }

    private Map<String, List<SxOfficialLossDetailRecordModel>> getOfficialLossDetailRecordMapByDimension(List<SxOfficialLossDetailRecordModel> officialLossDetailRecordList, Integer dimension) {
        if (CollectionUtils.isEmpty(officialLossDetailRecordList)) return null;
        return DIMENSION.CAREER.code == dimension
                ? officialLossDetailRecordList.stream().filter(o -> null != o).collect(Collectors.groupingBy(SxOfficialLossDetailRecordModel::getCareerCode))
                : officialLossDetailRecordList.stream().filter(o -> null != o).collect(Collectors.groupingBy(SxOfficialLossDetailRecordModel::getCategoryCode));
    }

    private List<SxOfficialLossDetailRecordModel> getOfficialLossDetailRecordListByGroupObj(Map<String, List<SxOfficialLossDetailRecordModel>> officialLossDetailRecordMapByDimension, String groupObj) {
        return (CollectionUtils.isEmpty(officialLossDetailRecordMapByDimension) || !officialLossDetailRecordMapByDimension.containsKey(groupObj)) ? null : officialLossDetailRecordMapByDimension.get(groupObj);
    }

    private Map<String, List<SxOfficialLossDetailRecordModel>> getOfficialLossDetailRecordMapByType(List<SxOfficialLossDetailRecordModel> officialLossDetailRecordListByGroupObj) {
        return CollectionUtils.isEmpty(officialLossDetailRecordListByGroupObj) ? null :
                officialLossDetailRecordListByGroupObj.stream().filter(o -> (null != o && !StringUtils.isEmpty(o.getLossTypeName()))).collect(Collectors.groupingBy(SxOfficialLossDetailRecordModel::getLossTypeName));
    }

    private Map<String, List<SxOfficialLossDetailRecordModel>> getOfficialLossDetailRecordMapByItem(List<SxOfficialLossDetailRecordModel> officialLossDetailRecordList) {
        return CollectionUtils.isEmpty(officialLossDetailRecordList) ? null :
                officialLossDetailRecordList.stream().filter(o -> (null != o && !StringUtils.isEmpty(o.getLossItemName()))).collect(Collectors.groupingBy(SxOfficialLossDetailRecordModel::getLossItemName));
    }

    private Map<String, List<SxOfficialLossDetailRecordModel>> getOfficialLossDetailRecordMapByDate(List<SxOfficialLossDetailRecordModel> officialLossDetailRecordList) {
        return CollectionUtils.isEmpty(officialLossDetailRecordList) ? null :
                officialLossDetailRecordList.stream().filter(o -> (null != o && !StringUtils.isEmpty(o.getLossDate()) &&
                        !StringUtils.isEmpty(o.getLossTypeName()) && !StringUtils.isEmpty(o.getLossItemName()))).collect(Collectors.groupingBy(SxOfficialLossDetailRecordModel::getLossDate));
    }

    private Map<String, String> getTableDataMap(String content1, String content2, String content3) {
        Map<String, String> tableData = new HashMap<>();
        tableData.put("content1", StringUtils.isEmpty(content1) ? MesExtConstWords.EMPTY : content1);
        tableData.put("content2", StringUtils.isEmpty(content2) ? MesExtConstWords.EMPTY : content2);
        tableData.put("content3", content3);
        return tableData;
    }

    private Map<String, String> getTableDataMap(String content2, String content3) {
        Map<String, String> tableData = new HashMap<>();
        tableData.put("content1", "工场\n损失");
        tableData.put("content2", StringUtils.isEmpty(content2) ? MesExtConstWords.EMPTY : content2);
        tableData.put("content3", content3);
        return tableData;
    }

    private Map<String, String> addTableData2ProdAmount(SxOfficialLossAnalysisReportModel data, SxOfficialLossAnalysisReportModel analysisData,
                                                        String groupObj, Map<String, Double> careerCorrelationCfgMap) {
        Map<String, String> tableData = getTableDataMap(null, null, "生产金额(千元)");
        Double prodAmount = new Double(0);
        for (Map.Entry<String, String> entry : data.getMonthMap().entrySet()) {
            if (null == entry) continue;
            String key = new StringJoiner(MesExtConstWords.AND).add(groupObj).add(MesExtEnumUtil.CAREER_CORRELATION_CFG_DATA_TYPE.PROD_AMOUNT.getValue()).add(entry.getValue()).toString();
            BigDecimal prodAmountStr = formatRoundHalfUp((!CollectionUtils.isEmpty(careerCorrelationCfgMap) && careerCorrelationCfgMap.containsKey(key)) ? careerCorrelationCfgMap.get(key) : new Double(MesExtConstWords.ZERO_STR), 3);
            tableData.put(entry.getKey(), prodAmountStr.toString());
            prodAmount = MathOperation.add(prodAmount, prodAmountStr.doubleValue());
        }
        tableData.put("year", formatRoundHalfUp(prodAmount, 3).toString());
        analysisData.addTableData(tableData);
        return tableData;
    }

    private Map<String, String> addTableData2PersonnelCost(SxOfficialLossAnalysisReportModel data, SxOfficialLossAnalysisReportModel analysisData,
                                                           Map<String, List<SxShiftProdCenterRecordStatisticsModel>> recordStatisticsMapByDate,
                                                           Map<String, List<SxProdDataToStatisticsOutPutModel>> outPutStatisticsMapByDate,
                                                           String careerPersonnelCost) {
        Map<String, String> tableData = getTableDataMap(null, null, "人件费(元/min)");
        for (Map.Entry<String, String> entry : data.getMonthMap().entrySet()) {
            if (null == entry) continue;
            String personnelCost = (CollectionUtils.isEmpty(recordStatisticsMapByDate) || !recordStatisticsMapByDate.containsKey(entry.getValue())) ? null : recordStatisticsMapByDate.get(entry.getValue()).get(0).getPersonnelCost();
            if (StringUtils.isEmpty(personnelCost)) personnelCost = (CollectionUtils.isEmpty(outPutStatisticsMapByDate) || !outPutStatisticsMapByDate.containsKey(entry.getValue())) ? null : outPutStatisticsMapByDate.get(entry.getValue()).get(0).getPersonnelCost();
            if (StringUtils.isEmpty(personnelCost) && entry.getValue().compareTo(data.getCurMonth()) >= 0) personnelCost = careerPersonnelCost;
            tableData.put(entry.getKey(), formatRoundHalfUp(new Double(!StringUtils.isEmpty(personnelCost) ? personnelCost : MesExtConstWords.ZERO_STR), 3).toString());
        }
        tableData.put("year", formatRoundHalfUp(new Double(MesExtConstWords.ZERO_STR), 3).toString());
        analysisData.addTableData(tableData);
        return tableData;
    }

    private void addTableData2ProdEnergyRateLoss(SxOfficialLossAnalysisReportModel data, SxOfficialLossAnalysisReportModel analysisData,
                                                 Map<String, List<SxShiftProdCenterRecordStatisticsModel>> recordStatisticsMapByDate,
                                                 Map<String, List<SxProdDataToStatisticsOutPutModel>> outPutStatisticsMapByDate,
                                                 Map<String, String> tableData2PersonnelCost) {
        Map<String, String> tableData2Time = getTableDataMap("作业能率损失", "实际分钟");
        Map<String, String> tableData2Amount = getTableDataMap("作业能率损失", "实际金额(千元)");
        Double timeStatistics = new Double(0);
        Double amountStatistics = new Double(0);
        for (Map.Entry<String, String> entry : data.getMonthMap().entrySet()) {
            if (null == entry) continue;
            List<SxShiftProdCenterRecordStatisticsModel> recordStatisticsList = CollectionUtils.isEmpty(recordStatisticsMapByDate) ? null : recordStatisticsMapByDate.get(entry.getValue());
            List<SxProdDataToStatisticsOutPutModel> outPutStatisticsList = CollectionUtils.isEmpty(outPutStatisticsMapByDate) ? null : outPutStatisticsMapByDate.get(entry.getValue());
            Double serviceTime = getShiftProdDataListStatistics(getShiftProdDataListByFlag(recordStatisticsList, true));
            Double nonWorkingTime = getShiftProdDataListStatistics(getShiftProdDataListByFlag(recordStatisticsList, false));
            Double standardTime = getOutPutDataListStatistics(outPutStatisticsList);
            BigDecimal realTime = formatRoundHalfUp(MathOperation.sub(MathOperation.sub(serviceTime, nonWorkingTime), standardTime), 3);
            BigDecimal realAmount = divFormatRoundUp(MathOperation.mul(realTime.doubleValue(), new Double(tableData2PersonnelCost.get(entry.getKey()))), new Double(1000), 3);
            tableData2Time.put(entry.getKey(), realTime.toString());
            tableData2Amount.put(entry.getKey(), realAmount.toString());
            timeStatistics = MathOperation.add(timeStatistics, realTime.doubleValue());
            amountStatistics = MathOperation.add(amountStatistics, realAmount.doubleValue());
        }
        tableData2Time.put("year", formatRoundHalfUp(timeStatistics, 3).toString());
        tableData2Amount.put("year", formatRoundHalfUp(amountStatistics, 3).toString());
        tableData2Amount.put("statisticsFlag", CommonEnumUtil.TRUE_OR_FALSE.TRUE.getValueStr());
        analysisData.addTableData(tableData2Time).addTableData(tableData2Amount);
    }

    private List<SxShiftProdCenterRecordStatisticsModel> getShiftProdDataListByFlag(List<SxShiftProdCenterRecordStatisticsModel> dataList, Boolean flag) {
        if (CollectionUtils.isEmpty(dataList)) return null;
        if (!flag) return dataList.stream().filter(o -> (null != o && (
                MesExtEnumUtil.SHIFT_PROD_TYPE.NORMAL_STOP_CENTER.getValue() == o.getProdType() || MesExtEnumUtil.SHIFT_PROD_TYPE.UNNORMAL_STOP_CENTER.getValue() == o.getProdType()
        ))).collect(Collectors.toList());
        else return dataList.stream().filter(o -> (null != o && (
                MesExtEnumUtil.SHIFT_PROD_TYPE.NORMAL_PROD.getValue() == o.getProdType() || MesExtEnumUtil.SHIFT_PROD_TYPE.UNNORMAL_PROD.getValue() == o.getProdType()
        ))).collect(Collectors.toList());
    }

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

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

    private void addTableData2Record(SxOfficialLossAnalysisReportModel data, SxOfficialLossAnalysisReportModel analysisData,
                                     Map<String, List<MesShiftProd>> shiftProdMap, List<SxShiftProdCenterRecordStatisticsModel> recordStatisticsListByGroupObj4Stop) {
        Map<String, List<SxShiftProdCenterRecordStatisticsModel>> recordStatisticsMapByProdCode = getRecordStatisticsMapByProdCode(recordStatisticsListByGroupObj4Stop);
        if (CollectionUtils.isEmpty(recordStatisticsMapByProdCode)) return;
        List<Map<String, String>> tableDataList = new LinkedList<>();
        for (Map.Entry<String, List<SxShiftProdCenterRecordStatisticsModel>> entryOuter : recordStatisticsMapByProdCode.entrySet()) {
            if (null == entryOuter) continue;
            Double timeStatistics = new Double(0);
            SxShiftProdCenterRecordStatisticsModel item = entryOuter.getValue().get(0);
            Map<String, String> tableData2Record = getTableDataMap(null, getProdName(shiftProdMap, item.getProdType(), item.getProdCode()));
            Map<String, List<SxShiftProdCenterRecordStatisticsModel>> recordStatisticsMapByDate = getRecordStatisticsMapByDate(entryOuter.getValue());
            for (Map.Entry<String, String> entry : data.getMonthMap().entrySet()) {
                if (null == entry) continue;
                List<SxShiftProdCenterRecordStatisticsModel> recordStatisticsList = recordStatisticsMapByDate.get(entry.getValue());
                BigDecimal realTime = formatRoundHalfUp(getShiftProdDataListStatistics(recordStatisticsList), 3);
                tableData2Record.put(entry.getKey(), realTime.toString());
                timeStatistics = MathOperation.add(timeStatistics, realTime.doubleValue());
            }
            tableData2Record.put("year", formatRoundHalfUp(timeStatistics, 3).toString());

            tableDataList.add(tableData2Record);
        }
        tableDataList = tableDataList.stream().sorted(Comparator.comparing(SxOfficialLossAnalysisReportService::comparingDouble).reversed()).collect(Collectors.toList());
        analysisData.addTableDataList(tableDataList);
    }

    private static Double comparingDouble(Map<String, String> map) {
        return Double.valueOf(map.get("year"));
    }

    private void addTableData2RecordStatistics(SxOfficialLossAnalysisReportModel data, SxOfficialLossAnalysisReportModel analysisData,
                                               Map<String, List<SxShiftProdCenterRecordStatisticsModel>> recordStatisticsMapByDate4Stop, Map<String, String> tableData2PersonnelCost) {
        if (CollectionUtils.isEmpty(recordStatisticsMapByDate4Stop)) return;
        Map<String, String> tableData2Time = getTableDataMap("小计", "实际分钟");
        Map<String, String> tableData2Amount = getTableDataMap("小计", "实际金额(千元)");
        Double timeStatistics = new Double(0);
        Double amountStatistics = new Double(0);
        for (Map.Entry<String, String> entry : data.getMonthMap().entrySet()) {
            if (null == entry) continue;
            List<SxShiftProdCenterRecordStatisticsModel> recordStatisticsList = recordStatisticsMapByDate4Stop.get(entry.getValue());
            BigDecimal realTime = formatRoundHalfUp(getShiftProdDataListStatistics(recordStatisticsList), 3);
            BigDecimal realAmount = divFormatRoundUp(MathOperation.mul(realTime.doubleValue(), new Double(tableData2PersonnelCost.get(entry.getKey()))), new Double(1000), 3);
            tableData2Time.put(entry.getKey(), realTime.toString());
            tableData2Amount.put(entry.getKey(), realAmount.toString());
            timeStatistics = MathOperation.add(timeStatistics, realTime.doubleValue());
            amountStatistics = MathOperation.add(amountStatistics, realAmount.doubleValue());
        }
        tableData2Time.put("year", formatRoundHalfUp(timeStatistics, 3).toString());
        tableData2Amount.put("year", formatRoundHalfUp(amountStatistics, 3).toString());
        tableData2Amount.put("statisticsFlag", CommonEnumUtil.TRUE_OR_FALSE.TRUE.getValueStr());
        analysisData.addTableData(tableData2Time).addTableData(tableData2Amount);
    }

    private void addTableData2OfficialLoss(SxOfficialLossAnalysisReportModel data, SxOfficialLossAnalysisReportModel analysisData,
                                           List<SxOfficialLossDetailRecordModel> officialLossDetailRecordListByGroupObj) {
        Map<String, List<SxOfficialLossDetailRecordModel>> officialLossDetailRecordMapByType = getOfficialLossDetailRecordMapByType(officialLossDetailRecordListByGroupObj);
        if (CollectionUtils.isEmpty(officialLossDetailRecordMapByType)) return;

        for(Map.Entry<String, List<SxOfficialLossDetailRecordModel>> entryOuter : officialLossDetailRecordMapByType.entrySet()) {
            if (null == entryOuter) continue;
            Map<String, List<SxOfficialLossDetailRecordModel>> officialLossDetailRecordMapByItem = getOfficialLossDetailRecordMapByItem(entryOuter.getValue());
            if (CollectionUtils.isEmpty(officialLossDetailRecordMapByItem)) continue;
            List<Map<String, String>> tableDataList = null;
            for(Map.Entry<String, List<SxOfficialLossDetailRecordModel>> entryInner : officialLossDetailRecordMapByItem.entrySet()) {
                if (null == entryInner) continue;
                Map<String, List<SxOfficialLossDetailRecordModel>> officialLossDetailRecordMapByDate = getOfficialLossDetailRecordMapByDate(entryInner.getValue());
                if (CollectionUtils.isEmpty(officialLossDetailRecordMapByDate)) continue;
                if (CollectionUtils.isEmpty(tableDataList)) tableDataList = new LinkedList<>();
                Map<String, String> tableData2OfficialLoss = getTableDataMap(entryOuter.getKey(), entryInner.getKey() + "(元)");
                Double amountStatistics = new Double(0);
                for (Map.Entry<String, String> entry : data.getMonthMap().entrySet()) {
                    if (null == entry) continue;
                    BigDecimal amount = formatRoundHalfUp(getOfficialLossDetailRecordListStatistics(officialLossDetailRecordMapByDate.get(entry.getValue())), 3);
                    tableData2OfficialLoss.put(entry.getKey(), amount.toString());
                    amountStatistics = MathOperation.add(amountStatistics, amount.doubleValue());
                }
                tableData2OfficialLoss.put("year", formatRoundHalfUp(amountStatistics, 3).toString());
                tableDataList.add(tableData2OfficialLoss);
            }
            if (!CollectionUtils.isEmpty(tableDataList)) tableDataList = tableDataList.stream().sorted(Comparator.comparing(SxOfficialLossAnalysisReportService::comparingDouble).reversed()).collect(Collectors.toList());
            analysisData.addTableDataList(tableDataList);
        }
    }

    private void addTableData2OfficialLossStatistics(SxOfficialLossAnalysisReportModel data, SxOfficialLossAnalysisReportModel analysisData, List<SxOfficialLossDetailRecordModel> officialLossDetailRecordListByGroupObj) {
        Map<String, List<SxOfficialLossDetailRecordModel>> officialLossDetailRecordMapByDate = getOfficialLossDetailRecordMapByDate(officialLossDetailRecordListByGroupObj);
        if (CollectionUtils.isEmpty(officialLossDetailRecordMapByDate)) return;
        Map<String, String> tableData2Amount = getTableDataMap("小计", "实际金额(千元)");
        Double amountStatistics = new Double(0);
        for (Map.Entry<String, String> entry : data.getMonthMap().entrySet()) {
            if (null == entry) continue;
            BigDecimal amount = divFormatRoundUp(getOfficialLossDetailRecordListStatistics(officialLossDetailRecordMapByDate.get(entry.getValue())), new Double(1000), 3);
            tableData2Amount.put(entry.getKey(), amount.toString());
            amountStatistics = MathOperation.add(amountStatistics, amount.doubleValue());
        }
        tableData2Amount.put("year", formatRoundHalfUp(amountStatistics, 3).toString());
        tableData2Amount.put("statisticsFlag", CommonEnumUtil.TRUE_OR_FALSE.TRUE.getValueStr());
        analysisData.addTableData(tableData2Amount);
    }

    private Double getOfficialLossDetailRecordListStatistics(List<SxOfficialLossDetailRecordModel> officialLossDetailRecordList) {
        return CollectionUtils.isEmpty(officialLossDetailRecordList) ? new Double(0) :
                officialLossDetailRecordList.stream().filter(o -> null != o).mapToDouble(SxOfficialLossDetailRecordModel::getAmount).sum();
    }

    private Map<String, String> addTableData2FinalStatistics(SxOfficialLossAnalysisReportModel data, SxOfficialLossAnalysisReportModel analysisData) {
        List<Map<String, String>> tableDataList = analysisData.getTableDataList().stream().filter(o -> (null != o && !StringUtils.isEmpty(o.get("statisticsFlag")))).collect(Collectors.toList());
        Map<String, String> tableData2Amount = getTableDataMap(null, "总计(千元)");
        Double amountStatistics = new Double(0);
        for (Map.Entry<String, String> entry : data.getMonthMap().entrySet()) {
            if (null == entry) continue;
            Double amount = new Double(0);
            for (Map<String, String> tableData : tableDataList) {
                if (null == tableData) continue;
                amount = MathOperation.add(amount, Double.valueOf(tableData.get(entry.getKey())));
            }
            tableData2Amount.put(entry.getKey(), formatRoundHalfUp(amount, 3).toString());
            amountStatistics = MathOperation.add(amountStatistics, amount);
        }
        tableData2Amount.put("year", formatRoundHalfUp(amountStatistics, 3).toString());
        analysisData.addTableData(tableData2Amount);
        return tableData2Amount;
    }

    private Map<String, String> addTableData2LossRate(SxOfficialLossAnalysisReportModel data, SxOfficialLossAnalysisReportModel analysisData, Map<String, String>... tableDataArr) {
        if (null == tableDataArr || tableDataArr.length == 0) {
            Map<String, String> tableData = getTableDataMap(null, null, "损失比例");
            analysisData.addTableData(tableData);
            return tableData;
        }

        Map<String, String> tableData2PLossRate = tableDataArr[0];
        Map<String, String> tableData2FinalStatistics = tableDataArr[1];
        Map<String, String> tableData2ProdAmount = tableDataArr[2];
        for (Map.Entry<String, String> entry : data.getMonthMap().entrySet()) {
            if (null == entry) continue;
            tableData2PLossRate.put(entry.getKey(), getLossRate(tableData2FinalStatistics, tableData2ProdAmount, entry.getKey()));
        }
        tableData2PLossRate.put("year", getLossRate(tableData2FinalStatistics, tableData2ProdAmount, "year"));
        return tableData2PLossRate;
    }

    private String getLossRate(Map<String, String> tableData2FinalStatistics, Map<String, String> tableData2ProdAmount, String key) {
        return getBigDecimalPercent(divFormatRoundUp(MathOperation.mul(Double.valueOf(tableData2FinalStatistics.get(key)), new Double(100)), Double.valueOf(tableData2ProdAmount.get(key)), 2));
    }

    private String getBigDecimalPercent(BigDecimal b) {
        return b + MesExtConstWords.PERCENT;
    }

    private BigDecimal divFormatRoundUp(Double d1, Double d2, int scale) {
        return formatRoundHalfUp((MathOperation.compareTo(d1, new Double(0)) == 0 || MathOperation.compareTo(d2, new Double(0)) == 0) ? null : MathOperation.div(d1, d2), scale);
    }

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

    @Override
    public void doDownLoadCareerOfficialLossAnalysis(HttpServletResponse resp, SxOfficialLossAnalysisReportModel data) {

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

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

        try {

            queryCareerOfficialLossAnalysis(data);

            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_CAREER_OFFICIAL_LOSS_ANALYSIS_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);

            handleCareerOfficialLossAnalysisDownLoadData(workbook, sheet, data);

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

            //根据模板写入文件
            excelFile= File.createTempFile("工场损失明细", 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, data.getTitle());
            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:[{}]", data.getTitle(), file.getFilePath(), Thread.currentThread().getName());

            resp.setCharacterEncoding("UTF-8");
            resp.setHeader("Content-Disposition", "attachment;filename=" + java.net.URLEncoder.encode(data.getTitle(), "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("创建文件异常" + data.getTitle() + ".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 handleCareerOfficialLossAnalysisDownLoadData(Workbook workbook, Sheet sheet, SxOfficialLossAnalysisReportModel data) {

        handle2RowBorderAndColumnWidth(sheet, data.getMonthMap().size());

        handle2RowCellName(sheet, data.getCellNameList());

        handleTitleRowRegion(sheet, data.getMonthMap().size());

        handleRowsBorderCopy(workbook, sheet, data.getAnalysisDataList());

        handleRowsData(sheet, data);

    }

    private void handle2RowBorderAndColumnWidth(Sheet sheet, int size) {
        Row row1 = sheet.getRow(1);
        Row row2 = sheet.getRow(2);
        for (int i = 0; i < 12 - size; i ++) {
            row1.removeCell(row1.getCell(16 - i));
            row2.removeCell(row2.getCell(16 - i));
        }
        sheet.setColumnWidth(1, 10 * 256);
        sheet.setColumnWidth(2, 25 * 256);
        sheet.setColumnWidth(3, 40 * 256);
        for (int i = 0; i <= size; i ++) {
            sheet.setColumnWidth(4 + i, 20 * 256);
        }
    }

    private void handle2RowCellName(Sheet sheet, List<String> cellNameList) {
        Row row1 = sheet.getRow(1);
        Integer[] cellNums = new Integer[cellNameList.size()];
        cellNums[0] = 2;
        for (int i = 1; i < cellNameList.size(); i ++) {
            cellNums[i] = 3 + i;
        }
        SxExcelExtUtil.setCellValue(row1, cellNums, cellNameList.toArray(new String[cellNameList.size()]));
    }

    private void handleTitleRowRegion(Sheet sheet, int size) {
        SxExcelExtUtil.addMergedRegionUnsafe(sheet, 0, 0, 1, 4 + size);
    }

    private void handleRowsBorderCopy(Workbook workbook, Sheet sheet, List<SxOfficialLossAnalysisReportModel> analysisDataList) {

        Integer lastRow = 2;

        for (int i = 0; i < analysisDataList.size(); i ++) {

            SxExcelExtUtil.copyRow(workbook, sheet, 2, lastRow + 1, analysisDataList.get(i).getTableDataList().size() - 1, true, null);

            if (i + 1 == analysisDataList.size()) break;

            lastRow += analysisDataList.get(i).getTableDataList().size() + 2;

            SxExcelExtUtil.copyRow(workbook, sheet, 1, lastRow - 1, 1, true, null);
            SxExcelExtUtil.copyRow(workbook, sheet, 2, lastRow, 1, true, null);
        }
    }

    private void handleRowsData(Sheet sheet, SxOfficialLossAnalysisReportModel data) {

        Integer[] cellNums = getRowsDataCellNums(data.getTableNameList().size());

        Integer lastRow = 2;

        for (SxOfficialLossAnalysisReportModel analysisData : data.getAnalysisDataList()) {
            if (null == analysisData) continue;

            SxExcelExtUtil.setCellValue(sheet.getRow(lastRow - 1), new Integer[]{1}, new String[]{analysisData.getGroupObjName()});

            String content1 = MesExtConstWords.EMPTY;
            Integer index1 = lastRow;
            String content2 = MesExtConstWords.EMPTY;
            Integer index2 = lastRow;

            for (int i = 0; i < analysisData.getTableDataList().size(); i ++) {

                String curContent1 = getContent(analysisData.getTableDataList(), i, "content1");
                String curContent2 = getContent(analysisData.getTableDataList(), i, "content2");
                String curContent3 = getContent(analysisData.getTableDataList(), i, "content3");

                String[] cellValues = getRowsDataCellValues(data.getCellFieldList(), analysisData.getTableDataList().get(i), curContent1, curContent2, curContent3);

                SxExcelExtUtil.setCellValue(sheet.getRow(lastRow), cellNums, cellValues);

                if (StringUtils.isEmpty(curContent1) && StringUtils.isEmpty(curContent2)) SxExcelExtUtil.addMergedRegionUnsafe(sheet, lastRow, lastRow, 1, 3);
                else if (!StringUtils.isEmpty(curContent1) && StringUtils.isEmpty(curContent2)) SxExcelExtUtil.addMergedRegionUnsafe(sheet, lastRow, lastRow, 2, 3);

                if (!content1.equals(curContent1)) {
                    content1 = curContent1;
                    index1 = lastRow;
                }

                if (!content2.equals(curContent2)) {
                    content2 = curContent2;
                    index2 = lastRow;
                }

                if (!StringUtils.isEmpty(curContent1) && (i == analysisData.getTableDataList().size() - 1 || !curContent1.equals(getContent(analysisData.getTableDataList(), i + 1, "content1")))) {
                    SxExcelExtUtil.addMergedRegionUnsafe(sheet, index1, lastRow, 1, 1);
                    sheet.getRow(index1).getCell(1).getCellStyle().setWrapText(true);
                }

                if (!StringUtils.isEmpty(curContent2) && (i == analysisData.getTableDataList().size() - 1 || !curContent2.equals(getContent(analysisData.getTableDataList(), i + 1, "content2"))))
                    SxExcelExtUtil.addMergedRegionUnsafe(sheet, index2, lastRow, 2, 2);

                lastRow ++;
            }


            lastRow += 2;

        }
    }

    private Integer[] getRowsDataCellNums(int size) {
        Integer[] cellNums = new Integer[size + 1];
        for (int i = 0; i <= size; i ++) {
            cellNums[i] = 1 + i;
        }
        return cellNums;
    }

    private String[] getRowsDataCellValues(List<String> cellFieldList, Map<String, String> tableData, String curContent1, String curContent2, String curContent3) {
        String[] cellValues = new String[cellFieldList.size() + 3];
        List<String> valueList = new ArrayList<>();
        if (!StringUtils.isEmpty(curContent1)) valueList.add(curContent1);
        if (!StringUtils.isEmpty(curContent2)) valueList.add(curContent2);
        if (!StringUtils.isEmpty(curContent3)) valueList.add(curContent3);
        cellValues[0] = valueList.get(0);
        cellValues[1] = valueList.size() > 1 ? valueList.get(1) : MesExtConstWords.EMPTY;
        cellValues[2] = valueList.size() > 2 ? valueList.get(2) : MesExtConstWords.EMPTY;
        for (int i = 0; i < cellFieldList.size(); i ++) {
            cellValues[3 + i] = tableData.get(cellFieldList.get(i));
        }
        return cellValues;
    }

    private String getContent(List<Map<String, String>> tableDataList, int index, String content) {
        return StringUtils.isEmpty(tableDataList.get(index).get(content)) ? MesExtConstWords.EMPTY : tableDataList.get(index).get(content);
    }

    @Override
    public SxOfficialLossAnalysisReportModel queryOfficialLossTrendChart(SxOfficialLossAnalysisReportModel data) {

        SxOfficialLossAnalysisReportModel data2LastYear = new SxOfficialLossAnalysisReportModel(data).workTimeStart(getLastYearWorkTime(data.getWorkTimeStart())).workTimeEnd(getLastYearWorkTime(data.getWorkTimeEnd()));

        handleMultipleChoice(data);

        List<MesWorkCenterExt> workCenterExtList = getWorkCenterExtList(data);

        Map<String, MesWorkCenterExt> centerMap = getCenterMap(workCenterExtList);

        calcYmdTime(data);

        calcQueryTime(data);

        Map<String, String> careerPersonnelCostMap = StringUtils.isEmpty(data.getWorkTimeYmdStart()) ? null : prodOrgExtService.getCareerPersonnelCostMap(data.getOrganizeCode());

        Map<String, Double> careerCorrelationCfgMap = getCareerCorrelationCfgMap(data, false);

        List<SxShiftProdCenterRecordStatisticsModel> recordStatisticsList = getRecordStatisticsList(data, centerMap, careerPersonnelCostMap, null);

        List<SxProdDataToStatisticsOutPutModel> outPutStatisticsList = getOutPutStatisticsList(data, centerMap, careerPersonnelCostMap);

        List<SxOfficialLossDetailRecordModel> officialLossDetailRecordList = getOfficialLossDetailRecordList(data, centerMap);

        data2LastYear.careerMap(data.getCareerMap()).workCenterCodeList(data.getWorkCenterCodeList()).categoryMap(data.getCategoryMap());

        calcYmdTime(data2LastYear);

        calcQueryTime(data2LastYear);

        Map<String, Double> careerCorrelationCfgMap2LastYear = getCareerCorrelationCfgMap(data2LastYear, false);

        List<SxShiftProdCenterRecordStatisticsModel> recordStatisticsList2LastYear = getRecordStatisticsList(data2LastYear, centerMap, careerPersonnelCostMap, null);

        List<SxProdDataToStatisticsOutPutModel> outPutStatisticsList2LastYear = getOutPutStatisticsList(data2LastYear, centerMap, careerPersonnelCostMap);

        List<SxOfficialLossDetailRecordModel> officialLossDetailRecordList2LastYear = getOfficialLossDetailRecordList(data2LastYear, centerMap);

        handleTableName2TrendChart(data, data2LastYear);

        Map<String, String> groupObjMap = DIMENSION.CAREER.code == data.getDimension() ? data.getCareerMap() : data.getCategoryMap();

        handleTableData2TrendChart(data, careerCorrelationCfgMap, recordStatisticsList, outPutStatisticsList, officialLossDetailRecordList,
                data2LastYear, careerCorrelationCfgMap2LastYear, recordStatisticsList2LastYear, outPutStatisticsList2LastYear, officialLossDetailRecordList2LastYear, careerPersonnelCostMap, groupObjMap, workCenterExtList);

        return data;
    }

    private String getLastYearWorkTime(String workTime) {
        String year = workTime.substring(0, 4);
        String withoutYear = workTime.substring(4);
        return Integer.valueOf(year) - 1 + withoutYear;
    }

    private void handleTableName2TrendChart(SxOfficialLossAnalysisReportModel data, SxOfficialLossAnalysisReportModel data2LastYear) {
        data.title(String.format("%s~%s工场损失推移管理表", data.getWorkTimeStart(), data.getWorkTimeEnd()));
        String startMonth = data.getWorkTimeStart();
        String startMonth2LastYear = data2LastYear.getWorkTimeStart();
        for (int i = 1; i <= 12; i ++) {
            data.addTableName(startMonth);
            data2LastYear.addTableName(startMonth2LastYear);
            startMonth = TimeTool.timeCalc(TimeTool.stringParseToDate(startMonth, MesExtConstWords.DATE_FORMAT_YM), Calendar.MONTH, 1, MesExtConstWords.DATE_FORMAT_YM);
            startMonth2LastYear = TimeTool.timeCalc(TimeTool.stringParseToDate(startMonth2LastYear, MesExtConstWords.DATE_FORMAT_YM), Calendar.MONTH, 1, MesExtConstWords.DATE_FORMAT_YM);
            if (startMonth.compareTo(data.getWorkTimeEnd()) > 0) break;
        }
        data.addTableName("year", "年度").addCellField("year").addCellName("年度");
    }

    private void handleTableData2TrendChart(SxOfficialLossAnalysisReportModel data,
                                            Map<String, Double> careerCorrelationCfgMap,
                                            List<SxShiftProdCenterRecordStatisticsModel> recordStatisticsList,
                                            List<SxProdDataToStatisticsOutPutModel> outPutStatisticsList,
                                            List<SxOfficialLossDetailRecordModel> officialLossDetailRecordList,
                                            SxOfficialLossAnalysisReportModel data2LastYear,
                                            Map<String, Double> careerCorrelationCfgMap2LastYear,
                                            List<SxShiftProdCenterRecordStatisticsModel> recordStatisticsList2LastYear,
                                            List<SxProdDataToStatisticsOutPutModel> outPutStatisticsList2LastYear,
                                            List<SxOfficialLossDetailRecordModel> officialLossDetailRecordList2LastYear,
                                            Map<String, String> careerPersonnelCostMap,
                                            Map<String, String> groupObjMap,
                                            List<MesWorkCenterExt> workCenterExtList) {

        Map<String, List<SxShiftProdCenterRecordStatisticsModel>> recordStatisticsMapByDate = getRecordStatisticsMapByDate(recordStatisticsList);
        Map<String, List<SxProdDataToStatisticsOutPutModel>> outPutStatisticsMapByDate = getOutPutStatisticsMapByDate(outPutStatisticsList);
        Map<String, List<SxOfficialLossDetailRecordModel>> officialLossDetailRecordMapByDate = getOfficialLossDetailRecordMapByDate(officialLossDetailRecordList);

        Map<String, List<SxShiftProdCenterRecordStatisticsModel>> recordStatisticsMapByDate2LastYear = getRecordStatisticsMapByDate(recordStatisticsList2LastYear);
        Map<String, List<SxProdDataToStatisticsOutPutModel>> outPutStatisticsMapByDate2LastYear = getOutPutStatisticsMapByDate(outPutStatisticsList2LastYear);
        Map<String, List<SxOfficialLossDetailRecordModel>> officialLossDetailRecordMapByDate2LastYear = getOfficialLossDetailRecordMapByDate(officialLossDetailRecordList2LastYear);

        Map<String, String> tableData2ProdAmount = addTableData2TrendChartProdAmount(data, careerCorrelationCfgMap);

        Map<String, String> tableData2ProdAmount2LastYear = addTableData2TrendChartProdAmount(data2LastYear, careerCorrelationCfgMap2LastYear);

        Map<String, String> tableData2LossRate = addTableData2TrendChartStatistics(data, groupObjMap, workCenterExtList, careerPersonnelCostMap, recordStatisticsMapByDate, outPutStatisticsMapByDate, officialLossDetailRecordMapByDate, tableData2ProdAmount);

        Map<String, String> tableData2LossRate2LastYear = addTableData2TrendChartStatistics(data2LastYear, groupObjMap, workCenterExtList, careerPersonnelCostMap, recordStatisticsMapByDate2LastYear, outPutStatisticsMapByDate2LastYear, officialLossDetailRecordMapByDate2LastYear, tableData2ProdAmount2LastYear);

        Map<String, String> tableData2FinalStatistics = data.getTableDataList().get(data.getTableDataList().size() - 1);

        Map<String, String> tableData2FinalStatistics2LastYear = data2LastYear.getTableDataList().get(data2LastYear.getTableDataList().size() - 1);

        data.addTableData(tableData2LossRate).addTableData(tableData2LossRate2LastYear);

        addLineChartDataList(data, tableData2LossRate, tableData2LossRate2LastYear);

        addTableData2Bp(data, careerCorrelationCfgMap, tableData2FinalStatistics);

        data.addTableData(tableData2FinalStatistics2LastYear);

        addTableData2LastYearRate(data, tableData2FinalStatistics, tableData2FinalStatistics2LastYear);

    }

    private Map<String, String> addTableData2TrendChartProdAmount(SxOfficialLossAnalysisReportModel data, Map<String, Double> careerCorrelationCfgMap) {
        Map<String, String> tableData = new HashMap<>();
        Double prodAmount = new Double(0);
        for (Map.Entry<String, String> entry : data.getMonthMap().entrySet()) {
            if (null == entry) continue;
            String key = new StringJoiner(MesExtConstWords.AND).add(MesExtEnumUtil.CAREER_CORRELATION_CFG_DATA_TYPE.PROD_AMOUNT.getValue()).add(entry.getValue()).toString();
            BigDecimal prodAmountBd = formatRoundHalfUp((!CollectionUtils.isEmpty(careerCorrelationCfgMap) && careerCorrelationCfgMap.containsKey(key)) ? careerCorrelationCfgMap.get(key) : new Double(MesExtConstWords.ZERO_STR), 3);
            tableData.put(entry.getKey(), prodAmountBd.toString());
            prodAmount = MathOperation.add(prodAmount, prodAmountBd.doubleValue());
        }
        tableData.put("year", formatRoundHalfUp(prodAmount, 3).toString());
        data.addTableData(tableData);
        return tableData;
    }

    private Map<String, String> addTableData2TrendChartStatistics(SxOfficialLossAnalysisReportModel data, Map<String, String> groupObjMap,
                                                                  List<MesWorkCenterExt> workCenterExtList, Map<String, String> careerPersonnelCostMap,
                                                                  Map<String, List<SxShiftProdCenterRecordStatisticsModel>> recordStatisticsMapByDate,
                                                                  Map<String, List<SxProdDataToStatisticsOutPutModel>> outPutStatisticsMapByDate,
                                                                  Map<String, List<SxOfficialLossDetailRecordModel>> officialLossDetailRecordMapByDate,
                                                                  Map<String, String> tableData2ProdAmount) {

        Map<String, String> tableData2ProdEnergyRateLoss = new HashMap<>();
        Map<String, String> tableData2NonWorking = new HashMap<>();
        Map<String, String> tableData2OfficialLoss = new HashMap<>();
        Map<String, String> tableData2FinalStatistics = new HashMap<>();
        Map<String, String> tableData2LossRate = new HashMap<>();

        Double prodEnergyRateLossStatistics = new Double(0);
        Double nonWorkingStatistics = new Double(0);
        Double officialLossStatistics = new Double(0);
        Double finalLossStatistics = new Double(0);

        for (Map.Entry<String, String> entryOuter : data.getMonthMap().entrySet()) {
            if (null == entryOuter) continue;

            Map<String, List<SxShiftProdCenterRecordStatisticsModel>> recordStatisticsMapByDimension = CollectionUtils.isEmpty(recordStatisticsMapByDate) ? null : getRecordStatisticsMapByDimension(recordStatisticsMapByDate.get(entryOuter.getValue()), data.getDimension());
            Map<String, List<SxProdDataToStatisticsOutPutModel>> outPutStatisticsMapByDimension = CollectionUtils.isEmpty(outPutStatisticsMapByDate) ? null : getOutPutStatisticsMapByDimension(outPutStatisticsMapByDate.get(entryOuter.getValue()), data.getDimension());

            List<SxOfficialLossDetailRecordModel> officialLossDetailRecordList = CollectionUtils.isEmpty(officialLossDetailRecordMapByDate) ? null : officialLossDetailRecordMapByDate.get(entryOuter.getValue());

            Double prodEnergyRateLossStatistics2Career = new Double(0);
            Double nonWorkingStatistics2Career = new Double(0);

            for (Map.Entry<String, String> entry : groupObjMap.entrySet()) {
                if (null == entry) continue;
                String careerCode = DIMENSION.CAREER.code == data.getDimension() ? entry.getKey() : getCareerCodeByCategoryCode(entry.getKey(), workCenterExtList);

                List<SxShiftProdCenterRecordStatisticsModel> recordStatisticsList = (CollectionUtils.isEmpty(recordStatisticsMapByDimension) || !recordStatisticsMapByDimension.containsKey(entry.getKey())) ? null : recordStatisticsMapByDimension.get(entry.getKey());
                List<SxProdDataToStatisticsOutPutModel> outPutStatisticsList = (CollectionUtils.isEmpty(outPutStatisticsMapByDimension) || !outPutStatisticsMapByDimension.containsKey(entry.getKey())) ? null : outPutStatisticsMapByDimension.get(entry.getKey());
                String personnelCost = CollectionUtils.isEmpty(recordStatisticsList) ? null : recordStatisticsList.get(0).getPersonnelCost();
                if (StringUtils.isEmpty(personnelCost)) personnelCost = CollectionUtils.isEmpty(outPutStatisticsList) ? null : outPutStatisticsList.get(0).getPersonnelCost();
                if (StringUtils.isEmpty(personnelCost) && entry.getValue().compareTo(data.getCurMonth()) >= 0) personnelCost = (CollectionUtils.isEmpty(careerPersonnelCostMap) || StringUtils.isEmpty(careerCode)) ? null : careerPersonnelCostMap.get(careerCode);
                Double personnelCostD = !StringUtils.isEmpty(personnelCost) ? new Double(personnelCost) : new Double(0);

                Double serviceTime = getShiftProdDataListStatistics(getShiftProdDataListByFlag(recordStatisticsList, true));
                Double nonWorkingTime = getShiftProdDataListStatistics(getShiftProdDataListByFlag(recordStatisticsList, false));
                Double standardTime = getOutPutDataListStatistics(outPutStatisticsList);
                Double realTime = MathOperation.sub(MathOperation.sub(serviceTime, nonWorkingTime), standardTime);
                Double realAmount = MathOperation.div(MathOperation.mul(realTime, personnelCostD), new Double(1000));
                prodEnergyRateLossStatistics2Career = MathOperation.add(prodEnergyRateLossStatistics2Career, realAmount);

                Double realAmount2 = MathOperation.div(MathOperation.mul(nonWorkingTime, personnelCostD), new Double(1000));
                nonWorkingStatistics2Career = MathOperation.add(nonWorkingStatistics2Career, realAmount2);
            }

            BigDecimal prodEnergyRateLossStatistics2CareerBd = formatRoundHalfUp(prodEnergyRateLossStatistics2Career, 3);
            BigDecimal nonWorkingStatistics2CareerBd = formatRoundHalfUp(nonWorkingStatistics2Career, 3);
            BigDecimal officialLossAmount = divFormatRoundUp(getOfficialLossDetailRecordListStatistics(officialLossDetailRecordList), new Double(1000), 3);
            BigDecimal monthLossStatistics = formatRoundHalfUp(MathOperation.add(MathOperation.add(prodEnergyRateLossStatistics2CareerBd.doubleValue(), nonWorkingStatistics2CareerBd.doubleValue()), officialLossAmount.doubleValue()), 3);

            tableData2ProdEnergyRateLoss.put(entryOuter.getKey(), prodEnergyRateLossStatistics2CareerBd.toString());
            tableData2NonWorking.put(entryOuter.getKey(), nonWorkingStatistics2CareerBd.toString());
            tableData2OfficialLoss.put(entryOuter.getKey(), officialLossAmount.toString());
            tableData2FinalStatistics.put(entryOuter.getKey(), monthLossStatistics.toString());
            tableData2LossRate.put(entryOuter.getKey(), getBigDecimalPercent(divFormatRoundUp(MathOperation.mul(monthLossStatistics.doubleValue(), new Double(100)), new Double(tableData2ProdAmount.get(entryOuter.getKey())), 2)));

            prodEnergyRateLossStatistics = MathOperation.add(prodEnergyRateLossStatistics, prodEnergyRateLossStatistics2CareerBd.doubleValue());
            nonWorkingStatistics = MathOperation.add(nonWorkingStatistics, nonWorkingStatistics2CareerBd.doubleValue());
            officialLossStatistics = MathOperation.add(officialLossStatistics, officialLossAmount.doubleValue());
            finalLossStatistics = MathOperation.add(finalLossStatistics, monthLossStatistics.doubleValue());

            data.addBarChartData(Integer.valueOf(entryOuter.getValue().substring(5, 7)) + "月", prodEnergyRateLossStatistics2CareerBd, nonWorkingStatistics2CareerBd, officialLossAmount);

        }

        tableData2ProdEnergyRateLoss.put("year", formatRoundHalfUp(prodEnergyRateLossStatistics, 3).toString());
        tableData2NonWorking.put("year", formatRoundHalfUp(nonWorkingStatistics, 3).toString());
        tableData2OfficialLoss.put("year", formatRoundHalfUp(officialLossStatistics, 3).toString());
        tableData2FinalStatistics.put("year", formatRoundHalfUp(finalLossStatistics, 3).toString());
        tableData2LossRate.put("year", getBigDecimalPercent(divFormatRoundUp(MathOperation.mul(finalLossStatistics, new Double(100)), new Double(tableData2ProdAmount.get("year")), 2)));

        data.addTableData(tableData2ProdEnergyRateLoss).addTableData(tableData2NonWorking).addTableData(tableData2OfficialLoss).addTableData(tableData2FinalStatistics);

        return tableData2LossRate;

    }

    private void addLineChartDataList(SxOfficialLossAnalysisReportModel data, Map<String, String> tableData2LossRate, Map<String, String> tableData2LossRate2LastYear) {
        for (Map.Entry<String, String> entryOuter : data.getMonthMap().entrySet()) {
            String lossRate = tableData2LossRate.get(entryOuter.getKey());
            String lossRate2LastYear = tableData2LossRate2LastYear.get(entryOuter.getKey());
            data.addLineChartData(
                    Integer.valueOf(entryOuter.getValue().substring(5, 7)) + "月",
                    formatRoundHalfUp(new Double(lossRate.substring(0, lossRate.length() - 1)), 2),
                    formatRoundHalfUp(new Double(lossRate2LastYear.substring(0, lossRate2LastYear.length() - 1)), 2)
            );
        }
    }

    private void addTableData2Bp(SxOfficialLossAnalysisReportModel data, Map<String, Double> careerCorrelationCfgMap, Map<String, String> tableData2FinalStatistics) {
        Map<String, String> tableData2Bp = new HashMap<>();
        Map<String, String> tableData2BpRate = new HashMap<>();
        Double bpStatistics = new Double(0);
        for (Map.Entry<String, String> entry : data.getMonthMap().entrySet()) {
            if (null == entry) continue;
            String key = new StringJoiner(MesExtConstWords.AND).add(MesExtEnumUtil.CAREER_CORRELATION_CFG_DATA_TYPE.PLAN_LOSS.getValue()).add(entry.getValue()).toString();
            BigDecimal bpAmountBd = formatRoundHalfUp((!CollectionUtils.isEmpty(careerCorrelationCfgMap) && careerCorrelationCfgMap.containsKey(key)) ? careerCorrelationCfgMap.get(key) : new Double(MesExtConstWords.ZERO_STR), 3);
            tableData2Bp.put(entry.getKey(), bpAmountBd.toString());
            tableData2BpRate.put(entry.getKey(), getBigDecimalPercent(divFormatRoundUp(MathOperation.mul(new Double(tableData2FinalStatistics.get(entry.getKey())), new Double(100)), bpAmountBd.doubleValue(), 2)));
            bpStatistics = MathOperation.add(bpStatistics, bpAmountBd.doubleValue());
        }
        BigDecimal bpStatisticsBd = formatRoundHalfUp(bpStatistics, 3);
        tableData2Bp.put("year", bpStatisticsBd.toString());
        tableData2BpRate.put("year", getBigDecimalPercent(divFormatRoundUp(MathOperation.mul(new Double(tableData2FinalStatistics.get("year")), new Double(100)), bpStatisticsBd.doubleValue(), 2)));
        data.addTableData(tableData2Bp).addTableData(tableData2BpRate);
    }

    private void addTableData2LastYearRate(SxOfficialLossAnalysisReportModel data, Map<String, String> tableData2FinalStatistics, Map<String, String> tableData2FinalStatistics2LastYear) {
        Map<String, String> tableData2LastYearRate = new HashMap<>();
        for (Map.Entry<String, String> entry : data.getMonthMap().entrySet()) {
            if (null == entry) continue;
            tableData2LastYearRate.put(entry.getKey(), getBigDecimalPercent(divFormatRoundUp(MathOperation.mul(new Double(tableData2FinalStatistics.get(entry.getKey())), new Double(100)), new Double(tableData2FinalStatistics2LastYear.get(entry.getKey())), 2)));
        }
        tableData2LastYearRate.put("year", getBigDecimalPercent(divFormatRoundUp(MathOperation.mul(new Double(tableData2FinalStatistics.get("year")), new Double(100)), new Double(tableData2FinalStatistics2LastYear.get("year")), 2)));
        data.addTableData(tableData2LastYearRate);
    }

    @Override
    public void doDownLoadOfficialLossTrendChart(HttpServletResponse resp, SxOfficialLossAnalysisReportModel data) {

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

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

        try {

            queryOfficialLossTrendChart(data);

            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_OFFICIAL_LOSS_TREND_CHART_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);

            handleOfficialLossTrendChartDownLoadData(sheet, data);

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

            //根据模板写入文件
            excelFile= File.createTempFile("工场损失推移管理表", 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, data.getTitle());
            String curYear = data.getCurMonth().substring(0, 4);
            excelDataMap.put("curYear", curYear.substring(2));
            excelDataMap.put("lastYear", String.valueOf(Integer.parseInt(curYear) - 1).substring(2));
            excelWriter.fill(excelDataMap, writeSheet);
            excelWriter.fill(data.getTableDataList(), writeSheet);

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

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

            log.info("MES工场损失推移管理报表 --- DOWNLOAD SUCCESS --- [{}]URL:[{}] --- THREAD:[{}]", data.getTitle(), file.getFilePath(), Thread.currentThread().getName());

            resp.setCharacterEncoding("UTF-8");
            resp.setHeader("Content-Disposition", "attachment;filename=" + java.net.URLEncoder.encode(data.getTitle(), "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("创建文件异常" + data.getTitle() + ".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 handleOfficialLossTrendChartDownLoadData(Sheet sheet, SxOfficialLossAnalysisReportModel data) {

        handle2RowBorderAndColumnWidth2TrendChart(sheet, data.getMonthMap().size());

        handleTitleRowRegion2TrendChart(sheet, data.getMonthMap().size());

        handleRowsDataReflex2TrendChart(sheet, data);

    }

    private void handle2RowBorderAndColumnWidth2TrendChart(Sheet sheet, int size) {
        for (int i = 1; i <= 12; i ++) {
            Row row = sheet.getRow(i);
            for (int j = 0; j < 12 - size; j ++) {
                row.removeCell(row.getCell(15 - j));
            }
        }
        for (int i = 0; i <= size; i ++) {
            sheet.setColumnWidth(3 + i, 20 * 256);
        }
    }

    private void handleTitleRowRegion2TrendChart(Sheet sheet, int size) {
        SxExcelExtUtil.addMergedRegionUnsafe(sheet, 0, 0, 1, 3 + size);
    }

    private void handleRowsDataReflex2TrendChart(Sheet sheet, SxOfficialLossAnalysisReportModel data) {

        Integer[] cellNums = handle2RowCellName2TrendChart(sheet, data.getCellNameList());

        String[] cellValues = getRowsDataCellValue2TrendChart(data.getCellFieldList());

        SxExcelExtUtil.setCellValue(sheet.getRow(2), cellNums, cellValues);

    }

    private Integer[] handle2RowCellName2TrendChart(Sheet sheet, List<String> cellNameList) {
        Row row1 = sheet.getRow(1);
        Integer[] cellNums = new Integer[cellNameList.size()];
        for (int i = 0; i < cellNameList.size(); i ++) {
            cellNums[i] = 3 + i;
        }
        SxExcelExtUtil.setCellValue(row1, cellNums, cellNameList.toArray(new String[cellNameList.size()]));
        return cellNums;
    }

    private String[] getRowsDataCellValue2TrendChart(List<String> cellFieldList) {
        String[] cellValues = new String[cellFieldList.size() + 1];
        for (int i = 0; i < cellFieldList.size(); i ++) {
            cellValues[i] = "{." + cellFieldList.get(i) + "}";
        }
        return cellValues;
    }

    enum DIMENSION {

        CAREER(10, "事业别"),
        CATEGORY(20, "产品别");

        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;
        }

    }

}
