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

import cn.estsh.i3plus.ext.mes.api.base.IModuleExcelManageService;
import cn.estsh.i3plus.ext.mes.api.base.bu.IBusiConfigService;
import cn.estsh.i3plus.ext.mes.api.busi.bu3.ISxDailyCheckConfirmRecordDataService;
import cn.estsh.i3plus.ext.mes.api.busi.bu3.ISxDailyCheckConfirmRecordService;
import cn.estsh.i3plus.ext.mes.apiservice.dao.bu3.ISxEquipmentCheckDetailDao;
import cn.estsh.i3plus.ext.mes.apiservice.util.FileExtUtil;
import cn.estsh.i3plus.ext.mes.apiservice.util.SxExcelExtUtil;
import cn.estsh.i3plus.ext.mes.pojo.bean.MesBusiConfig;
import cn.estsh.i3plus.ext.mes.pojo.bean.MesDailyCheckConfirmRecord;
import cn.estsh.i3plus.ext.mes.pojo.model.bu3.SxDailyCheckConfirmRecordDataModel;
import cn.estsh.i3plus.ext.mes.pojo.model.bu3.SxDailyCheckConfirmRecordModel;
import cn.estsh.i3plus.ext.mes.pojo.model.bu3.SxDailyCheckEquCheckDataModel;
import cn.estsh.i3plus.ext.mes.pojo.model.bu3.SxDailyCheckShiftCfgModel;
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.TimeTool;
import cn.estsh.i3plus.pojo.base.codemaker.SnowflakeIdMaker;
import cn.estsh.i3plus.pojo.base.enumutil.CommonEnumUtil;
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 com.alibaba.fastjson.JSONObject;
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.util.*;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.stream.Collectors;

@Slf4j
@Service
public class SxDailyCheckConfirmRecordDataEquCheckService implements ISxDailyCheckConfirmRecordDataService {

    @Autowired
    private ISxDailyCheckConfirmRecordService dailyCheckConfirmRecordService;

    @Autowired
    private ISxEquipmentCheckDetailDao equipmentCheckDetailDao;

    @Autowired
    private IBusiConfigService busiConfigService;

    @Autowired
    private IModuleExcelManageService moduleExcelManageService;

    @Autowired
    private SnowflakeIdMaker snowflakeIdMaker;

    @Autowired
    private ImppFileService imppFileService;

    private final static String DAILY_CHECK_TEMPLATE_EQU_CHECK = "DAILY_CHECK_TEMPLATE_EQU_CHECK";

    private final static Integer TEMPLATE_LAST_ROW = 5;

    @Override
    public void check(Object... obj) {
        if (null == obj)
            throw ImppExceptionBuilder.newInstance()
                    .setSystemID(CommonEnumUtil.SOFT_TYPE.MES.getCode())
                    .setErrorCode(ImppExceptionEnum.VARIFY_EXCEPTION.getCode())
                    .setErrorDetail("入参缺失必要参数")
                    .build();
        if (obj.length >= 1 && StringUtils.isEmpty(obj[0]))
            throw ImppExceptionBuilder.newInstance()
                    .setSystemID(CommonEnumUtil.SOFT_TYPE.MES.getCode())
                    .setErrorCode(ImppExceptionEnum.VARIFY_EXCEPTION.getCode())
                    .setErrorDetail("入参缺失必要参数设备代码")
                    .build();
        if (obj.length >= 2 && StringUtils.isEmpty(obj[1]))
            throw ImppExceptionBuilder.newInstance()
                    .setSystemID(CommonEnumUtil.SOFT_TYPE.MES.getCode())
                    .setErrorCode(ImppExceptionEnum.VARIFY_EXCEPTION.getCode())
                    .setErrorDetail("入参缺失必要参数生产线名称")
                    .build();
    }

    @Override
    public List<SxDailyCheckConfirmRecordModel> confirmList(SxDailyCheckConfirmRecordModel data) {
        List<MesDailyCheckConfirmRecord> confirmDbList = dailyCheckConfirmRecordService.confirmList(data);
        if (CollectionUtils.isEmpty(confirmDbList)) return null;
        List<SxDailyCheckConfirmRecordModel> confirmList = new ArrayList<>();
        for (MesDailyCheckConfirmRecord item : confirmDbList) {
            if (null == item) continue;
            SxDailyCheckConfirmRecordModel confirm = new SxDailyCheckConfirmRecordModel();
            BeanUtils.copyProperties(item, confirm);
            confirm.setOrderTypeName(MesExtEnumUtil.MES_OR_ORDER_TYPE.valueOfDescription(data.getOrderType()));
            confirm.setLevelName(confirm.getLevel().equals("10") ? "课长" : "系长");
            confirm.setConfirmDatetimeYmd(confirm.getConfirmDatetime().substring(0, 10));
            confirmList.add(confirm);
        }
        List<SxDailyCheckConfirmRecordModel> kezhangList = confirmList.stream().filter(o -> (null != o &&
                o.getLevel().equals("10"))).sorted(Comparator.comparing(SxDailyCheckConfirmRecordModel::getConfirmDatetime).reversed()).collect(Collectors.toList());
        List<SxDailyCheckConfirmRecordModel> xizhangList = confirmList.stream().filter(o -> (null != o &&
                o.getLevel().equals("20"))).sorted(Comparator.comparing(SxDailyCheckConfirmRecordModel::getConfirmDatetime)).collect(Collectors.toList());
        confirmList.clear();
        if (!CollectionUtils.isEmpty(kezhangList)) confirmList.add(kezhangList.get(0));
        else confirmList.add(new SxDailyCheckConfirmRecordModel("10", MesExtConstWords.EMPTY, MesExtConstWords.EMPTY));

        if (!CollectionUtils.isEmpty(xizhangList)) confirmList.addAll(xizhangList);
        return confirmList;
    }

    @Override
    public SxDailyCheckConfirmRecordDataModel cellFieldMap(SxDailyCheckConfirmRecordDataModel data) {
        data.addCellField("index", "序号").addCellField("daily", "周期")
                .addCellField("content1", "每日点检项目").addCellField("content2", "点检时间");
        for (int i = 1; i <= 31; i ++) {
            data.addCellField(MesExtConstWords.DAY + i, String.valueOf(i));
        }
        return data;
    }

    @Override
    public SxDailyCheckConfirmRecordDataModel query(SxDailyCheckConfirmRecordDataModel data) {
        log.info("MES日常点检报表确认记录报表 --- QUERY START --- EQU CHECK --- THREAD:[{}]", Thread.currentThread().getName());

        this.check(data.getEquNo(), data.getWorkCenterName());

        this.cellFieldMap(data);

        SxDailyCheckShiftCfgModel shiftCfg = getShiftCfg(data);

        if (null == shiftCfg)  return data;

        List<SxDailyCheckEquCheckDataModel> dataList = dataList(data, shiftCfg);

        if (CollectionUtils.isEmpty(dataList)) return data;

        data.confirmList(this.confirmList(packConfirmRecordParam(data)));

        rowDataMap(data, dataList);

        log.info("MES日常点检报表确认记录报表 --- QUERY SUCCESS --- EQU CHECK --- THREAD:[{}]", Thread.currentThread().getName());

        return data;
    }

    private SxDailyCheckShiftCfgModel getShiftCfg(SxDailyCheckConfirmRecordDataModel data) {
        MesBusiConfig busiConfig = busiConfigService.getBusiConfigByCfgCode(data.getOrganizeCode(), MesExtConstWords.DAILY_CHECK_SHIFT_TIME_CFG);
        if (null == busiConfig || StringUtils.isEmpty(busiConfig.getCfgValue())) return null;
        List<SxDailyCheckShiftCfgModel> shiftCfgList = JSONObject.parseArray(busiConfig.getCfgValue(), SxDailyCheckShiftCfgModel.class);
        Optional<SxDailyCheckShiftCfgModel> shiftCfgOp = CollectionUtils.isEmpty(shiftCfgList) ? null :
                shiftCfgList.stream().filter(o -> (null != o && !StringUtils.isEmpty(o.getShiftCode()) && o.getShiftCode().equals(data.getShiftCode()))).findFirst();
        if (null == shiftCfgOp || !shiftCfgOp.isPresent()) return null;
        return shiftCfgOp.get();
    }

    private List<SxDailyCheckEquCheckDataModel> dataList(SxDailyCheckConfirmRecordDataModel data, SxDailyCheckShiftCfgModel shiftCfg) {
        data.setWorkTimeStart(data.getWorkTime() + MesExtConstWords.DATE_DD_01);
        data.setWorkTimeEnd(TimeTool.timeCalc(TimeTool.stringParseToDate(data.getWorkTime(), MesExtConstWords.DATE_FORMAT_YM), Calendar.MONTH, 1, MesExtConstWords.DATE_FORMAT_YM) + MesExtConstWords.DATE_DD_01);
        List<SxDailyCheckEquCheckDataModel> resultList = getResultList(data);
        if (CollectionUtils.isEmpty(resultList)) return null;
        for (SxDailyCheckEquCheckDataModel result : resultList) {
            if (null == result) continue;
            if (!StringUtils.isEmpty(result.getSpotCheckTime())) packResultByShiftCfg(result, shiftCfg);
            if (StringUtils.isEmpty(data.getEquName())) data.equName(result.getEquName());
        }
        resultList = CollectionUtils.isEmpty(resultList) ? null :
                resultList.stream().filter(o -> (null != o && !StringUtils.isEmpty(o.getCheckType()) && !StringUtils.isEmpty(o.getProjectContent()))).collect(Collectors.toList());
        return resultList;
    }

    private List<SxDailyCheckEquCheckDataModel> getResultList(SxDailyCheckConfirmRecordDataModel data) {
        SxDailyCheckEquCheckDataModel param = packParam(data);
        List<SxDailyCheckEquCheckDataModel> resultList1 = equipmentCheckDetailDao.queryDailyCheckEquipmentCheckDetailList(param.orderStatus(MesExtEnumUtil.MES_ORJ_ORDER_STATUS.CHECK_COMPLETE.getValue()));
        List<SxDailyCheckEquCheckDataModel> resultList2 = equipmentCheckDetailDao.queryDailyCheckEquipmentCheckDetailList(param.orderStatus(MesExtEnumUtil.MES_ORJ_ORDER_STATUS.CLOSE.getValue()));
        if (CollectionUtils.isEmpty(resultList1) && CollectionUtils.isEmpty(resultList2)) return null;
        List<SxDailyCheckEquCheckDataModel> resultList = new ArrayList<>();
        if (!CollectionUtils.isEmpty(resultList1)) resultList.addAll(resultList1);
        if (!CollectionUtils.isEmpty(resultList2)) resultList.addAll(resultList2);
        return resultList;
    }

    private SxDailyCheckConfirmRecordModel packConfirmRecordParam(SxDailyCheckConfirmRecordDataModel data) {
        SxDailyCheckConfirmRecordModel param = new SxDailyCheckConfirmRecordModel();
        BeanUtils.copyProperties(data, param);
        return param;
    }

    private SxDailyCheckEquCheckDataModel packParam(SxDailyCheckConfirmRecordDataModel data) {
        SxDailyCheckEquCheckDataModel param = new SxDailyCheckEquCheckDataModel();
        BeanUtils.copyProperties(data, param);
        return param;
    }

    private void packResultByShiftCfg(SxDailyCheckEquCheckDataModel result, SxDailyCheckShiftCfgModel shiftCfg) {
        Integer day = Integer.valueOf(result.getSpotCheckTime().substring(8, 10));
        result.setTimeFlag(CommonEnumUtil.TRUE_OR_FALSE.TRUE.getValueStr());

        if (MesExtEnumUtil.MES_EQUIPMENT_CHECK_TYPE.DAY_CHECK.getValue().equals(result.getCheckType())) {
            String spotCheckTime = result.getSpotCheckTime().substring(11);
            if (checkTime(spotCheckTime, shiftCfg.getMorningStart(), shiftCfg.getMorningEnd())) {
                result.setDateFlag(MesExtConstWords.DAY + day);

            } else if (checkTime(spotCheckTime, shiftCfg.getAfternoonStart(), shiftCfg.getAfternoonEnd())) {
                result.setTimeFlag(CommonEnumUtil.TRUE_OR_FALSE.FALSE.getValueStr());

                if (CommonEnumUtil.TRUE_OR_FALSE.FALSE.getValue() == shiftCfg.getShiftType()) result.setDateFlag(MesExtConstWords.DAY + day);
                else result.setDateFlag(MesExtConstWords.DAY + (day - 1));

            } else result.setDateFlag(MesExtConstWords.DAY);

        } else result.setDateFlag(MesExtConstWords.DAY + day);

        if (!StringUtils.isEmpty(result.getResultValue())) result.setResult(result.getResultValue());
        else if (!StringUtils.isEmpty(result.getCheckResult())) {
            if (!StringUtils.isEmpty(result.getRemark()) && result.getRemark().equals("该项不需要点检") &&
                    (MesExtEnumUtil.EQU_CHECK_RESULT.QUALIFIED.getValue() == result.getCheckResult())) result.setResult("不需点检");
            else result.setResult(MesExtEnumUtil.EQU_CHECK_RESULT.valueOfDescription(result.getCheckResult()));
        }

        if (StringUtils.isEmpty(result.getResult())) result.setResult(MesExtConstWords.EMPTY);
    }

    private Boolean checkTime(String spotCheckTime, String startTime, String endTime) {
        return TimeTool.stringParseToDate(spotCheckTime, MesExtConstWords.TIME_FORMAT).compareTo(TimeTool.stringParseToDate(startTime, MesExtConstWords.TIME_FORMAT)) >= 0 &&
                TimeTool.stringParseToDate(spotCheckTime, MesExtConstWords.TIME_FORMAT).compareTo(TimeTool.stringParseToDate(endTime, MesExtConstWords.TIME_FORMAT)) <= 0;
    }

    private Map<String, List<SxDailyCheckEquCheckDataModel>> dataMapByCheckType(List<SxDailyCheckEquCheckDataModel> dataList) {
        return dataList.stream().filter(o -> null != o).collect(Collectors.groupingBy(SxDailyCheckEquCheckDataModel::getCheckType));
    }

    private Map<String, List<SxDailyCheckEquCheckDataModel>> dataMapByProject(List<SxDailyCheckEquCheckDataModel> dataList) {
        return dataList.stream().filter(o -> null != o).collect(Collectors.groupingBy(SxDailyCheckEquCheckDataModel::getProjectContent));
    }

    private Map<String, List<SxDailyCheckEquCheckDataModel>> dataMapByDateTime(List<SxDailyCheckEquCheckDataModel> dataList) {
        return dataList.stream().filter(o -> (null != o && !StringUtils.isEmpty(o.getDateFlag()) && !StringUtils.isEmpty(o.getTimeFlag())))
                .collect(Collectors.groupingBy(o -> new StringJoiner(MesExtConstWords.AND).add(o.getDateFlag()).add(o.getTimeFlag()).toString()));
    }

    private SxDailyCheckConfirmRecordDataModel rowDataMap(SxDailyCheckConfirmRecordDataModel data, List<SxDailyCheckEquCheckDataModel> dataList) {
        List<SxDailyCheckEquCheckDataModel> methodList = new LinkedList<>();

        List<LinkedHashMap<String, String>> rowDataList = new LinkedList<>();

        Map<String, List<SxDailyCheckEquCheckDataModel>> dataMap = dataMapByCheckType(dataList);

        List<String> checkTypeList = getCheckTypeList();

        Integer num = 1;

        for (String checkType : checkTypeList) {
            if (StringUtils.isEmpty(checkType)) continue;

            List<SxDailyCheckEquCheckDataModel> checkTypeDataList = dataMap.get(checkType);

            if (CollectionUtils.isEmpty(checkTypeDataList)) continue;

            Map<String, List<SxDailyCheckEquCheckDataModel>> projectMap = dataMapByProject(checkTypeDataList);

            projectMap = sortMapByKey(projectMap);

            for (Map.Entry<String, List<SxDailyCheckEquCheckDataModel>> entry : projectMap.entrySet()) {
                if (null == entry) continue;

                SxDailyCheckEquCheckDataModel itemData = entry.getValue().get(0);
                methodList.add(itemData);

                Map<String, List<SxDailyCheckEquCheckDataModel>> dateTimeMap = dataMapByDateTime(entry.getValue());

                rowDataList.add(rowDataResult(dateTimeMap, itemData, CommonEnumUtil.TRUE_OR_FALSE.TRUE.getValueStr(), num));
                if (MesExtEnumUtil.MES_EQUIPMENT_CHECK_TYPE.DAY_CHECK.getValue().equals(checkType)) {
                    rowDataList.add(rowDataResult(dateTimeMap, itemData, CommonEnumUtil.TRUE_OR_FALSE.FALSE.getValueStr(), num));
                }
                num ++;
            }

            Map<String, List<SxDailyCheckEquCheckDataModel>> dateTimeMap = dataMapByDateTime(checkTypeDataList);

            rowDataList.add(rowDataOperater(dateTimeMap, checkType, CommonEnumUtil.TRUE_OR_FALSE.TRUE.getValueStr()));
            if (MesExtEnumUtil.MES_EQUIPMENT_CHECK_TYPE.DAY_CHECK.getValue().equals(checkType)) {
                rowDataList.add(rowDataOperater(dateTimeMap, checkType, CommonEnumUtil.TRUE_OR_FALSE.FALSE.getValueStr()));
                rowDataList.add(rowDataLeader(dateTimeMap, checkType, CommonEnumUtil.TRUE_OR_FALSE.TRUE.getValueStr()));
                rowDataList.add(rowDataLeader(dateTimeMap, checkType, CommonEnumUtil.TRUE_OR_FALSE.FALSE.getValueStr()));
            }
        }

        rowDataList.add(rowDataMethodTitle());

        num = 1;
        for (SxDailyCheckEquCheckDataModel methodData : methodList) {
            if (null == methodData) continue;
            rowDataList.add(rowDataMethod(methodData, num));

            num ++;
        }

        return data.rowDataList(rowDataList);
    }

    private LinkedHashMap<String, String> rowDataResult(Map<String, List<SxDailyCheckEquCheckDataModel>> dateTimeMap, SxDailyCheckEquCheckDataModel itemData, String timeFlag, Integer num) {
        LinkedHashMap<String, String> rowData = new LinkedHashMap<>();
        rowData.put("showFlag", CommonEnumUtil.TRUE_OR_FALSE.TRUE.getValueStr());
        rowData.put("dataFlag", CommonEnumUtil.TRUE_OR_FALSE.TRUE.getValueStr());
        rowData.put("index", num.toString());
        rowData.put("daily", MesExtEnumUtil.MES_EQUIPMENT_CHECK_TYPE.valueOfAbbr(itemData.getCheckType()));
        rowData.put("checkType", itemData.getCheckType());
        rowData.put("content1", itemData.getProjectContent());
        putContent2ByCheckType(rowData, itemData.getCheckType(), timeFlag, true);

        String dateFlag;
        for (int i = 1; i <= 31; i ++) {
            dateFlag = MesExtConstWords.DAY + i;
            SxDailyCheckEquCheckDataModel data = dataByDateTime(dateTimeMap, dateFlag, timeFlag);
            rowData.put(dateFlag, null != data ? data.getResult() : MesExtConstWords.EMPTY);
        }
        return rowData;
    }

    private LinkedHashMap<String, String> rowDataOperater(Map<String, List<SxDailyCheckEquCheckDataModel>> dateTimeMap, String checkType, String timeFlag) {
        LinkedHashMap<String, String> rowData = new LinkedHashMap<>();
        rowData.put("showFlag", CommonEnumUtil.TRUE_OR_FALSE.TRUE.getValueStr());
        rowData.put("dataFlag", CommonEnumUtil.TRUE_OR_FALSE.TRUE.getValueStr());
        rowData.put("checkType", checkType);
        rowData.put("content1", "点检者");
        putContent2ByCheckType(rowData, checkType, timeFlag, false);

        String dateFlag;
        for (int i = 1; i <= 31; i ++) {
            dateFlag = MesExtConstWords.DAY + i;
            SxDailyCheckEquCheckDataModel data = dataByDateTime(dateTimeMap, dateFlag, timeFlag);
            rowData.put(dateFlag, null != data ? data.getSpotCheckUser() : MesExtConstWords.EMPTY);
        }
        return rowData;
    }

    private void putContent2ByDayType(LinkedHashMap<String, String> rowData, String timeFlag, Boolean flag) {
        if (CommonEnumUtil.TRUE_OR_FALSE.TRUE.getValueStr().equals(timeFlag)) rowData.put("content2", flag ? "上午首件品" : "上午");
        else rowData.put("content2", flag ? "下午首件品" : "下午");
    }

    private void putContent2ByCheckType(LinkedHashMap<String, String> rowData, String checkType, String timeFlag, Boolean flag) {
        if (MesExtEnumUtil.MES_EQUIPMENT_CHECK_TYPE.DAY_CHECK.getValue().equals(checkType)) {
            putContent2ByDayType(rowData, timeFlag, flag);
        } else {
            rowData.put("content2", "全天");
        }
    }

    private LinkedHashMap<String, String> rowDataLeader(Map<String, List<SxDailyCheckEquCheckDataModel>> dateTimeMap, String checkType, String timeFlag) {
        LinkedHashMap<String, String> rowData = new LinkedHashMap<>();
        rowData.put("showFlag", CommonEnumUtil.TRUE_OR_FALSE.TRUE.getValueStr());
        rowData.put("dataFlag", CommonEnumUtil.TRUE_OR_FALSE.TRUE.getValueStr());
        rowData.put("checkType", checkType);
        rowData.put("content1", "班长（或担当）");
        putContent2ByDayType(rowData, timeFlag, false);

        String dateFlag;
        for (int i = 1; i <= 31; i ++) {
            dateFlag = MesExtConstWords.DAY + i;
            SxDailyCheckEquCheckDataModel data = dataByDateTime4Leader(dateTimeMap, dateFlag, timeFlag);
            rowData.put(dateFlag, null != data ? data.getTeamConfirmUser() : MesExtConstWords.EMPTY);
        }
        return rowData;
    }

    private LinkedHashMap<String, String> rowDataMethodTitle() {
        LinkedHashMap<String, String> rowData = new LinkedHashMap<>();
        rowData.put("showFlag", CommonEnumUtil.TRUE_OR_FALSE.TRUE.getValueStr());
        rowData.put("dataFlag", CommonEnumUtil.TRUE_OR_FALSE.FALSE.getValueStr());
        rowData.put("index", "序号");
        rowData.put("content1", "每日点检项目");
        rowData.put("content2", "点检方法");
        return rowData;
    }

    private LinkedHashMap<String, String> rowDataMethod(SxDailyCheckEquCheckDataModel methodData, Integer num) {
        LinkedHashMap<String, String> rowData = new LinkedHashMap<>();
        rowData.put("showFlag", CommonEnumUtil.TRUE_OR_FALSE.FALSE.getValueStr());
        rowData.put("dataFlag", CommonEnumUtil.TRUE_OR_FALSE.FALSE.getValueStr());
        rowData.put("index", num.toString());
        rowData.put("checkType", methodData.getCheckType());
        rowData.put("content1", methodData.getProjectContent());
        rowData.put("content2", methodData.getCheckMethod());
        return rowData;
    }

    private SxDailyCheckEquCheckDataModel dataByDateTime(Map<String, List<SxDailyCheckEquCheckDataModel>> dateTimeMap, String dateFlag, String timeFlag) {
        List<SxDailyCheckEquCheckDataModel> dataList = dateTimeMap.get(new StringJoiner(MesExtConstWords.AND).add(dateFlag).add(timeFlag).toString());
        dataList = CollectionUtils.isEmpty(dataList) ? null :
                dataList.stream().filter(o -> (null != o && !StringUtils.isEmpty(o.getSpotCheckTime())))
                        .sorted(Comparator.comparing(SxDailyCheckEquCheckDataModel::getSpotCheckTime).reversed()).collect(Collectors.toList());
        return CollectionUtils.isEmpty(dataList) ? null : dataList.get(0);
    }

    private SxDailyCheckEquCheckDataModel dataByDateTime4Leader(Map<String, List<SxDailyCheckEquCheckDataModel>> dateTimeMap, String dateFlag, String timeFlag) {
        List<SxDailyCheckEquCheckDataModel> dataList = dateTimeMap.get(new StringJoiner(MesExtConstWords.AND).add(dateFlag).add(timeFlag).toString());
        dataList = CollectionUtils.isEmpty(dataList) ? null :
                dataList.stream().filter(o -> (null != o && !StringUtils.isEmpty(o.getTeamConfirmTime())))
                        .sorted(Comparator.comparing(SxDailyCheckEquCheckDataModel::getTeamConfirmTime).reversed()).collect(Collectors.toList());
        return CollectionUtils.isEmpty(dataList) ? null : dataList.get(0);
    }

    private List<String> getCheckTypeList() {
        List<String> checkTypeList = new LinkedList<>();
        checkTypeList.add(MesExtEnumUtil.MES_EQUIPMENT_CHECK_TYPE.DAY_CHECK.getValue());
        checkTypeList.add(MesExtEnumUtil.MES_EQUIPMENT_CHECK_TYPE.WEEK_CHECK.getValue());
        checkTypeList.add(MesExtEnumUtil.MES_EQUIPMENT_CHECK_TYPE.MONTH_CHECK.getValue());
        return checkTypeList;
    }

    private Map<String, List<SxDailyCheckEquCheckDataModel>> sortMapByKey(Map<String, List<SxDailyCheckEquCheckDataModel>> map) {
        Map<String, List<SxDailyCheckEquCheckDataModel>> sortMap = new TreeMap<>(new MapKeyComparator());
        sortMap.putAll(map);
        return sortMap;
    }

    class MapKeyComparator implements Comparator<String>{
        @Override
        public int compare(String str1, String str2) {
            return str1.compareTo(str2);
        }
    }

    @Override
    public void doConfirm(SxDailyCheckConfirmRecordModel data) {

        this.check(data.getEquNo());

        List<MesDailyCheckConfirmRecord> confirmDbList = dailyCheckConfirmRecordService.confirmList(data);

        if (CollectionUtils.isEmpty(confirmDbList) && CollectionUtils.isEmpty(query(packQueryParam(data)).getRowDataList()))
            throw ImppExceptionBuilder.newInstance()
                    .setSystemID(CommonEnumUtil.SOFT_TYPE.MES.getCode())
                    .setErrorCode(ImppExceptionEnum.VARIFY_EXCEPTION.getCode())
                    .setErrorDetail("当前条件查询暂无数据,确认失败")
                    .build();

        if (!StringUtils.isEmpty(data.getTimes()) && !CollectionUtils.isEmpty(confirmDbList) && Integer.valueOf(data.getTimes()).compareTo(confirmDbList.size()) <= 0)
            throw ImppExceptionBuilder.newInstance()
                    .setSystemID(CommonEnumUtil.SOFT_TYPE.MES.getCode())
                    .setErrorCode(ImppExceptionEnum.VARIFY_EXCEPTION.getCode())
                    .setErrorDetail("已经存在%s条%s确认记录,操作失败", confirmDbList.size(), data.getLevel().equals("10") ? "课长" : "系长")
                    .build();

        confirmDbList = CollectionUtils.isEmpty(confirmDbList) ? confirmDbList :
                confirmDbList.stream().filter(o -> null != o).sorted(Comparator.comparing(MesDailyCheckConfirmRecord::getConfirmDatetime).reversed()).collect(Collectors.toList());
        if (!CollectionUtils.isEmpty(confirmDbList) && !StringUtils.isEmpty(data.getIntervalTime()) && !checkIsAllowConfirm(confirmDbList.get(0).getConfirmDatetime(), data.getIntervalTime()))
            throw ImppExceptionBuilder.newInstance()
                    .setSystemID(CommonEnumUtil.SOFT_TYPE.MES.getCode())
                    .setErrorCode(ImppExceptionEnum.VARIFY_EXCEPTION.getCode())
                    .setErrorDetail("继上次%s确认人[%s]确认时间[%s]后未满足间隔时间[%sH],操作失败",
                            data.getLevel().equals("10") ? "课长" : "系长", confirmDbList.get(0).getCreateUser(), confirmDbList.get(0).getConfirmDatetime(), data.getIntervalTime())
                    .build();

        dailyCheckConfirmRecordService.insertConfirm(data);

    }

    private Boolean checkIsAllowConfirm(String confirmDatetime, String intervalTime) {
        if (TimeTool.timeCalc(TimeTool.stringParseToDate(confirmDatetime, MesExtConstWords.DATETIME_FORMAT),
                Calendar.HOUR, Integer.parseInt(intervalTime), MesExtConstWords.DATETIME_FORMAT).compareTo(TimeTool.getNowTime(true)) > 0)
            return false;
        return true;
    }

    private SxDailyCheckConfirmRecordDataModel packQueryParam(SxDailyCheckConfirmRecordModel data) {
        SxDailyCheckConfirmRecordDataModel param = new SxDailyCheckConfirmRecordDataModel();
        BeanUtils.copyProperties(data, param);
        return param;
    }

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

    @Override
    public void doDownLoad(HttpServletResponse resp, SxDailyCheckConfirmRecordDataModel data) {

        log.info("MES日常点检报表确认记录报表 --- DOWNLOAD START --- EQU CHECK --- THREAD:[{}]", Thread.currentThread().getName());

        this.query(data);

        checkData4DownLoad(data);

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

        List<LinkedHashMap<String, String>> rowDataList4Day = data.getRowDataList().subList(0, getRowDataIndexByFlag(data.getRowDataList(), "content2", "点检方法") - 1);

        List<LinkedHashMap<String, String>> rowDataList4Method = data.getRowDataList().subList(rowDataList4Day.size() + 1, data.getRowDataList().size());

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

        try{

            InputStream inputStream = FileExtUtil.getInputStream(fileDb.getFileUrl());
            if(inputStream == null)
                throw ImppExceptionBuilder.newInstance()
                        .setSystemID(CommonEnumUtil.SOFT_TYPE.MES.getCode())
                        .setErrorCode(ImppExceptionEnum.IO_EXCEPTION_FILE.getCode())
                        .setErrorDetail("导出模板[%s]对应的文件URL[%s]获取文件流异常", DAILY_CHECK_TEMPLATE_EQU_CHECK, 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);

            //生成日期数据行位置
            copyRow4DayData(workbook, sheet, rowDataList4Day);

            //写入确认信息
            setCellValue4ConfirmList(data.getConfirmList(), sheet);

            //写入日期数据
            setCellValue4DayData(workbook, sheet, rowDataList4Day);

            //日期数据结束行号(行号起始是1, 数据起始是0)
            Integer dayDataLastRowNum = TEMPLATE_LAST_ROW + rowDataList4Day.size();

            //复制标题行到日期数据结束行的下一行,写入方法数据的标题
            SxExcelExtUtil.copyRow(workbook, sheet, TEMPLATE_LAST_ROW - 1, dayDataLastRowNum, 1, true, null);
            SxExcelExtUtil.addMergedRegionUnsafe(sheet, dayDataLastRowNum, dayDataLastRowNum, 0, 1);
            SxExcelExtUtil.addMergedRegionUnsafe(sheet, dayDataLastRowNum, dayDataLastRowNum, 3, 34);
            SxExcelExtUtil.setCellValue(sheet.getRow(dayDataLastRowNum), new Integer[]{3}, new String[]{"点检方法"});

            //复制方法标题行至下一行并清空数据
            SxExcelExtUtil.copyRow(workbook, sheet, dayDataLastRowNum, dayDataLastRowNum + 1, 1, true, null);
            Row methodDataRow = sheet.getRow(dayDataLastRowNum + 1);
            SxExcelExtUtil.setCellValue(methodDataRow, new Integer[]{0, 2, 3}, new String[]{MesExtConstWords.EMPTY, MesExtConstWords.EMPTY, MesExtConstWords.EMPTY});
            new SxExcelExtUtil.SetCellStyle(workbook).setRows(methodDataRow).bold(false).execute().clear().setCells(methodDataRow.getCell(3)).alignmentLeftTop().execute();

            //复制方法标题行的下一行至方法数据结束行,用于方法数据的写入
            SxExcelExtUtil.copyRow(workbook, sheet, dayDataLastRowNum + 1, dayDataLastRowNum + 2, rowDataList4Method.size() - 1, true, null);

            //写入列表数据映射字段
            SxExcelExtUtil.setCellValue(methodDataRow, new Integer[]{0, 2, 3}, new String[]{"{.index}", "{.content1}", "{.content2}"});

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

            //获取导出文件名称
            excelName = new StringJoiner(MesExtConstWords.E_UNDERLINE).add("设备日常点检表").add(data.getWorkCenterCode()).add(data.getShiftCode()).add(data.getEquNo()).add(data.getWorkTime()).toString();

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

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

            //数据写入
            Map<String, Object> excelDataMap = new HashMap<>();
            excelDataMap.put(MesExtConstWords.EQU_NO, data.getEquNo());
            excelDataMap.put(MesExtConstWords.EQU_NAME, data.getEquName());
            excelDataMap.put(MesExtConstWords.WORK_CENTER_CODE, data.getWorkCenterCode());
            excelDataMap.put(MesExtConstWords.WORK_CENTER_NAME, data.getWorkCenterName());
            excelDataMap.put(MesExtConstWords.SHIFT_CODE, data.getShiftCode());
            excelDataMap.put("yyyy", data.getWorkTime().split(MesExtConstWords.E_LINE)[0]);
            excelDataMap.put("MM", data.getWorkTime().split(MesExtConstWords.E_LINE)[1]);

            excelWriter.fill(excelDataMap, writeSheet);

            excelWriter.fill(rowDataList4Method, writeSheet);

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

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

            log.info("MES日常点检报表确认记录报表 --- DOWNLOAD SUCCESS --- URL:[{}] --- THREAD:[{}]", file.getFilePath(), Thread.currentThread().getName());

            FileExtUtil.exportFile(resp, excelFile);

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

            log.info("MES日常点检报表确认记录报表 --- DOWNLOAD END --- EQU CHECK --- THREAD:[{}]", Thread.currentThread().getName());
        }
    }

    private Integer getRowDataIndexByFlag(List<LinkedHashMap<String, String>> rowDataList, String field, String value) {
        AtomicInteger index = new AtomicInteger();
        rowDataList.stream().filter(o -> {
            index.getAndIncrement();
            return null != o && o.get(field).equals(value);
        }).findFirst();
        return index.get();
    }

    private void copyRow4DayData(Workbook workbook, Sheet sheet, List<LinkedHashMap<String, String>> rowDataList4Day) {
        List<LinkedHashMap<String, String>> rowDataList4DayCheck = rowDataList4Day.stream().filter(o -> (null != o && MesExtEnumUtil.MES_EQUIPMENT_CHECK_TYPE.DAY_CHECK.getValue().equals(o.get("checkType")))).collect(Collectors.toList());
        //复制标题行的下2行至日期数据结束行,用于day_check类型日期数据的写入
        if (!CollectionUtils.isEmpty(rowDataList4DayCheck)) SxExcelExtUtil.copyRow(workbook, sheet, TEMPLATE_LAST_ROW, TEMPLATE_LAST_ROW + 2, rowDataList4DayCheck.size() - 2, true, null);
        if (!CollectionUtils.isEmpty(rowDataList4DayCheck) && rowDataList4DayCheck.size() == rowDataList4Day.size()) return;

        List<LinkedHashMap<String, String>> rowDataList4Remain = rowDataList4Day.subList(CollectionUtils.isEmpty(rowDataList4DayCheck) ? 0 : rowDataList4DayCheck.size(), rowDataList4Day.size());

        Integer remainIndex;
        if (!CollectionUtils.isEmpty(rowDataList4DayCheck)) {
            SxExcelExtUtil.copyRow(workbook, sheet, TEMPLATE_LAST_ROW, TEMPLATE_LAST_ROW + 2, 2, true, null);
            remainIndex = TEMPLATE_LAST_ROW + rowDataList4DayCheck.size();
        } else {
            remainIndex = TEMPLATE_LAST_ROW;
        }
        new SxExcelExtUtil.SetCellStyle(workbook).setRows(sheet.getRow(remainIndex)).alignmentCenter().borderMedium().execute();

        SxExcelExtUtil.copyRow(workbook, sheet, remainIndex, remainIndex + 1, rowDataList4Remain.size() - 2, true, null);
    }

    private void setCellValue4ConfirmList(List<SxDailyCheckConfirmRecordModel> confirmList, Sheet sheet) {
        if (CollectionUtils.isEmpty(confirmList)) return;
        Row row1 = sheet.getRow(TEMPLATE_LAST_ROW - 3);
        Row row2 = sheet.getRow(TEMPLATE_LAST_ROW - 2);
        SxExcelExtUtil.setCellValue(row1, new Integer[]{23, 25, 27, 29, 31, 33}, new ArrayList<>(confirmList.stream().filter(o -> null != o).map(SxDailyCheckConfirmRecordModel::getCreateUser).collect(Collectors.toList())).toArray(new String[confirmList.size()]));
        SxExcelExtUtil.setCellValue(row2, new Integer[]{23, 25, 27, 29, 31, 33}, new ArrayList<>(confirmList.stream().filter(o -> null != o).map(SxDailyCheckConfirmRecordModel::getConfirmDatetimeYmd).collect(Collectors.toList())).toArray(new String[confirmList.size()]));
    }

    private void setCellValue4DayData(Workbook workbook, Sheet sheet, List<LinkedHashMap<String, String>> rowDataList4Day) {
        Integer rowNum = TEMPLATE_LAST_ROW;
        List<String> keyList = new ArrayList<>();
        for (int i = 0 ; i < rowDataList4Day.size(); i ++) {
            LinkedHashMap<String, String> map = rowDataList4Day.get(i);
            String key = new StringJoiner(MesExtConstWords.AND).add(map.get("index")).add(map.get("daily")).add(map.get("checkType")).add(map.get("content1")).toString();
            if (!keyList.contains(key)) {

                keyList.add(key);

                if (MesExtEnumUtil.MES_EQUIPMENT_CHECK_TYPE.DAY_CHECK.getValue().equals(map.get("checkType"))) {
                    if (!StringUtils.isEmpty(map.get("index"))) {
                        SxExcelExtUtil.addMergedRegionUnsafe(sheet, rowNum, rowNum + 1, 0, 0);
                        SxExcelExtUtil.addMergedRegionUnsafe(sheet, rowNum, rowNum + 1, 1, 1);
                        SxExcelExtUtil.addMergedRegionUnsafe(sheet, rowNum, rowNum + 1, 2, 2);
                        SxExcelExtUtil.setCellValue(sheet.getRow(rowNum), new Integer[]{0, 1, 2}, new String[]{map.get("index"), map.get("daily"), map.get("content1")});
                    } else {
                        SxExcelExtUtil.addMergedRegionUnsafe(sheet, rowNum, rowNum + 1, 0, 2);
                        SxExcelExtUtil.setCellValue(sheet.getRow(rowNum), new Integer[]{0}, new String[]{map.get("content1")});
                    }
                    rowNum = rowNum + 2;
                } else {
                    if (!StringUtils.isEmpty(map.get("index"))) {
                        SxExcelExtUtil.setCellValue(sheet.getRow(rowNum), new Integer[]{0, 1, 2}, new String[]{map.get("index"), map.get("daily"), map.get("content1")});
                    } else {
                        SxExcelExtUtil.addMergedRegionUnsafe(sheet, rowNum, rowNum, 0, 2);
                        SxExcelExtUtil.setCellValue(sheet.getRow(rowNum), new Integer[]{0}, new String[]{map.get("content1")});
                        new SxExcelExtUtil.SetCellStyle(workbook).setRows(sheet.getRow(rowNum)).alignmentCenter().execute();
                    }
                    rowNum = rowNum + 1;
                }
            }

            SxExcelExtUtil.setCellValue(sheet.getRow(TEMPLATE_LAST_ROW + i), getDayDataCellNumArr(), getDayDataCellDataArr(map));
        }
    }

    private Integer[] getDayDataCellNumArr() {
        List<Integer> list = new ArrayList<>();
        for (int i = 3; i < 35; i ++) {
            list.add(i);
        }
        return list.toArray(new Integer[32]);
    }

    private String[] getDayDataCellDataArr(LinkedHashMap<String, String> map) {
        List<String> list = new ArrayList<>();
        for (String field : map.keySet()) {
            if (!CollectionUtils.isEmpty(list) || field.equals("content2")) list.add(map.get(field));
        }
        return list.toArray(new String[32]);
    }

}
