package cn.estsh.i3plus.ext.mes.apiservice.serviceimpl.excel.ep;

import cn.estsh.i3plus.ext.mes.api.base.IExcelImportExtService;
import cn.estsh.i3plus.ext.mes.apiservice.serviceimpl.base.CommonExtService;
import cn.estsh.i3plus.ext.mes.pojo.bean.MesEpEquipmentCheck;
import cn.estsh.i3plus.ext.mes.pojo.model.ExcelImportErrorExtModel;
import cn.estsh.i3plus.ext.mes.pojo.model.ExcelImportResultExtModel;
import cn.estsh.i3plus.ext.mes.pojo.repository.MesEpEquipmentCheckRepository;
import cn.estsh.i3plus.ext.mes.pojo.repository.MesWorkCenterExtRepository;
import cn.estsh.i3plus.ext.mes.pojo.util.MesExtConstWords;
import cn.estsh.i3plus.ext.mes.pojo.util.MesExtEnumUtil;
import cn.estsh.i3plus.mes.apiservice.util.MesCommonUtil;
import cn.estsh.i3plus.platform.common.exception.ImppExceptionEnum;
import cn.estsh.i3plus.pojo.base.bean.DdlPackBean;
import cn.estsh.i3plus.pojo.base.enumutil.CommonEnumUtil;
import cn.estsh.i3plus.pojo.mes.bean.MesEquipment;
import cn.estsh.i3plus.pojo.mes.bean.MesWorkCell;
import cn.estsh.i3plus.pojo.mes.repository.MesEquipmentRepository;
import cn.estsh.i3plus.pojo.mes.repository.MesWorkCellRepository;
import cn.estsh.impp.framework.boot.auth.AuthUtil;
import org.apache.poi.ss.usermodel.CellType;
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.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;

import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @PROJECT_NAME: i3plus-mes-panasonic-dev
 * @DESCRIPTION:
 * @USER: xinwang.yi
 * @DATE: 2021-12-13 11:05
 */
@Service
public class SxEpEquipmentCheckExcelService implements IExcelImportExtService {
    @Autowired
    private MesEpEquipmentCheckRepository equipmentCheckRepository;

    @Autowired
    private MesWorkCenterExtRepository workCenterExtRepository;

    @Autowired
    private MesEquipmentRepository equipmentRepository;

    @Autowired
    private MesWorkCellRepository workCellRepository;

    @Autowired
    private CommonExtService commonService;

    private static final String ONE =  "1";
    private static final String TWO =  "2";
    private static final String THREE =  "3";
    private static final String FOUR =  "4";
    private static final String FIVE =  "5";

    /**
     * 设备点检项目导入
     * @param workbook
     * @param organizeCode
     * @param userName
     * @return
     */
    @Override
    public ExcelImportResultExtModel insertDataByExcel(Workbook workbook, String organizeCode, String userName) {
        //读取表格
        ExcelImportResultExtModel excelImportResultModel = this.sheetExtractMesEquipment(workbook.getSheetAt(0), organizeCode, userName);
        //数据入库
        this.insertExcelMesEquipmentCheck(excelImportResultModel);
        return excelImportResultModel;
    }

    /**
     * 设备点检项目导入-读取表格
     * @param sheetAt
     * @param organizeCode
     * @param userName
     * @return
     */
    private ExcelImportResultExtModel sheetExtractMesEquipment(Sheet sheetAt, String organizeCode, String userName) {
        //从0行开始读取
        int totalNumberOfRows = sheetAt.getLastRowNum() + 1;

        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

        //MesEquipment集合
        List<MesEpEquipmentCheck> mesEquipmentList = new ArrayList<>();

        //成功数量
        Integer successRowNum = 0;
        //失败数量
        Integer failRowNum = 0;
        //错误的行号
        String errorRows = "";

        //错误行信息集合
        List<ExcelImportErrorExtModel> excelImportErrorModels = new ArrayList<>();

        //用于EXCEL表中的工作中心代码+工位代码+设备代码+项目内容+点检方法+类型唯一校验，key：workCenterCode&workCellCode&equNo&projectContent&checkMethod&checkType，value：row
        Map<String, String> existMap = new HashMap<>();

        // 查询产线表
        Map<String, Object> workCenterCodeEntityMap;
        try {
            workCenterCodeEntityMap = MesCommonUtil.getCodeEntityMap(workCenterExtRepository, MesExtConstWords.WORK_CENTER_CODE, organizeCode, "产线表");
        } catch (Exception e) {
            workCenterCodeEntityMap = null;
        }

        // 查询工位表
        List<MesWorkCell> workCellList = workCellRepository.findByProperty(
                new String[]{MesExtConstWords.ORGANIZE_CODE, MesExtConstWords.IS_DELETED},
                new Object[]{organizeCode, CommonEnumUtil.TRUE_OR_FALSE.FALSE.getValue()});
        Map<String, List<MesWorkCell>> workCellCodeEntityMap = CollectionUtils.isEmpty(workCellList) ? null :
                workCellList.stream().filter(o -> null != o).collect(Collectors.groupingBy(o -> o.getWorkCenterCode() + "&" + o.getWorkCellCode()));

        //查询工位设备点检表
        List<MesEpEquipmentCheck> equipmentChecks = equipmentCheckRepository.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()});
        Map<String, List<MesEpEquipmentCheck>> equipmentCheckMap = CollectionUtils.isEmpty(equipmentChecks) ? null :
                equipmentChecks.stream().filter(o -> null != o)
                        .collect(Collectors.groupingBy(o -> new StringJoiner(MesExtConstWords.AND)
                                .add(o.getWorkCenterCode()).add(o.getWorkCellCode()).add(o.getEquNo()).add(o.getProjectContent()).add(o.getCheckMethod()).add(o.getCheckType()).toString()));

        List<MesEquipment> equipmentList = equipmentRepository.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()});
        Map<String, List<MesEquipment>> equipmentMap = CollectionUtils.isEmpty(equipmentList) ? null :
                equipmentList.stream().filter(o -> null != o)
                        .collect(Collectors.groupingBy(MesEquipment::getEquipmentCode));

        //从excel表的第5行数据开始导入，getFirstRowNum是从0行开始读取
        for (int i = (sheetAt.getFirstRowNum() + 4); i < totalNumberOfRows; i ++) {
            Row row = sheetAt.getRow(i);
            //空行跳过
            if (null == row) {
                continue;
            }
            //获取总列数
            Short lastCellNum = row.getLastCellNum();
            if (lastCellNum > 0) {

                int rowNum = i + 1;    //当前行号
                int errorNum = 0;      //错误数量
                String cellNum = "";   //错误列号
                String errorInfo = ""; //错误信息

                //工作中心
                try {
                    //第一列必须有值，否则跳过
                    row.getCell(0).setCellType(CellType.STRING);
                } catch (Exception e) {
                    continue;
                }
                String workCenterCode = row.getCell(0, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).getStringCellValue().trim();

                //工位代码
                String workCellCode = null;
                try {
                    row.getCell(1).setCellType(CellType.STRING);
                    workCellCode = row.getCell(1, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).getStringCellValue().trim();
                } catch (Exception e) {
                }

                //设备编号
                String equNo = null;
                try {
                    row.getCell(2).setCellType(CellType.STRING);
                    equNo = row.getCell(2, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).getStringCellValue().trim();
                } catch (Exception e) {
                }
                //项目内容
                String projectContent = null;
                try {
                    row.getCell(3).setCellType(CellType.STRING);
                    projectContent = row.getCell(3, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).getStringCellValue().trim();
                } catch (Exception e) {
                }

                //点检方法
                String checkMethod = null;
                try {
                    row.getCell(4).setCellType(CellType.STRING);
                    checkMethod = row.getCell(4, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).getStringCellValue().trim();
                } catch (Exception e) {
                }

                //点检周期
                String checkType = null;
                try {
                    row.getCell(5).setCellType(CellType.STRING);
                    checkType = row.getCell(5, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).getStringCellValue().trim();
                } catch (Exception e) {
                }

                //结果类型
                Integer resultType = null;
                try {
                    row.getCell(6).setCellType(CellType.STRING);
                    resultType = Integer.valueOf(row.getCell(6, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).getStringCellValue().trim());
                } catch (Exception e) {
                }

                //标准值
                String standardValue = null;
                try {
                    row.getCell(7).setCellType(CellType.STRING);
                    standardValue = row.getCell(7, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).getStringCellValue().trim();
                } catch (Exception e) {
                }

                //上限值
                String upLimit = null;
                try {
                    row.getCell(8).setCellType(CellType.STRING);
                    upLimit = row.getCell(8, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).getStringCellValue().trim();
                } catch (Exception e) {
                }

                //下限值
                String downLimit = null;
                try {
                    row.getCell(9).setCellType(CellType.STRING);
                    downLimit = row.getCell(9, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).getStringCellValue().trim();
                } catch (Exception e) {
                }

                //校验生产线代码
                if (CollectionUtils.isEmpty(workCenterCodeEntityMap) || !workCenterCodeEntityMap.containsKey(workCenterCode)) {
                    errorNum++;
                    cellNum += "A;";
                    errorInfo += "第A列数据无效;";
                }
                String workCellName = null;
                //校验工位代码
                if (StringUtils.isEmpty(workCellCode)) {
                    errorNum++;
                    cellNum += "B;";
                    errorInfo += "第B列数据必填;";
                } else {
                    if (StringUtils.isEmpty(errorInfo)) {
                        if (CollectionUtils.isEmpty(workCellCodeEntityMap) || !workCellCodeEntityMap.containsKey(workCenterCode + "&" + workCellCode)) {
                            errorNum++;
                            cellNum += "A+B;";
                            errorInfo += "第A+B列数据无效;";
                        } else {
                            workCellName = workCellCodeEntityMap.get(workCenterCode + "&" + workCellCode).get(0).getWorkCellName();
                        }
                    }
                }

                //设备名称
                String equName = null;
                //校验设备编号
                if (StringUtils.isEmpty(equNo)) {
                    errorNum ++;
                    cellNum += "C;";
                    errorInfo += "第C列数据必填;";
                }else if(CollectionUtils.isEmpty(equipmentMap)){
                    errorNum ++;
                    cellNum += "C;";
                    errorInfo += "设备信息没有维护;";
                }else if(!equipmentMap.containsKey(equNo)){
                    errorNum ++;
                    cellNum += "C;";
                    errorInfo += "设备信息中没有维护当前录入的设备代码;";
                }else {
                    equName = equipmentMap.get(equNo).get(0).getEquipmentName();
                }

                //校验项目内容
                if (StringUtils.isEmpty(projectContent)) {
                    errorNum ++;
                    cellNum += "D;";
                    errorInfo += "第D列数据必填;";
                } else {
                    if (projectContent.length() > 255) {
                        errorNum++;
                        cellNum += "D;";
                        errorInfo += "第D列数据长度不能超过255个字符;";
                    }
                }

                //校验点检方法
                if (StringUtils.isEmpty(checkMethod)) {
                    errorNum ++;
                    cellNum += "E;";
                    errorInfo += "第E列数据必填;";
                }

                //校验点检周期
                if (StringUtils.isEmpty(checkType)) {
                    errorNum ++;
                    cellNum += "F;";
                    errorInfo += "第F列数据必填;";
                } else {
                    if (StringUtils.isEmpty(MesExtEnumUtil.MES_EQUIPMENT_CHECK_TYPE.valueOfDescription(checkType))) {
                        errorNum ++;
                        cellNum += "F;";
                        errorInfo += "第F列数据无效;";
                    }
                }

                //校验结果类型
                if (StringUtils.isEmpty(resultType)) {
                    errorNum ++;
                    cellNum += "G;";
                    errorInfo += "第G列数据必填;";
                } else {
                    if (StringUtils.isEmpty(MesExtEnumUtil.EP_EQUIPMENT_CHECK_RESULT_TYPE.valueOfDescription(resultType))) {
                        errorNum ++;
                        cellNum += "G;";
                        errorInfo += "第G列数据无效;";
                    }else if(resultType == (MesExtEnumUtil.EP_EQUIPMENT_CHECK_RESULT_TYPE.COMPARE_TYPE.getValue())){

                        try{
                            //标准值
                            Double standardValueCheck = null;

                            //上限值
                            Double upLimitCheck = null;

                            //下限值
                            Double downLimitCheck = null;
                            if(!StringUtils.isEmpty(standardValue)){
                                standardValueCheck =  Double.valueOf(standardValue);
                                String[] standard = standardValue.split(MesExtConstWords.DECIMAL_POINT);
                                if (null != standard && standard.length == 2 && standard[1].length() > 2) {
                                    errorNum++;
                                    cellNum += "H;";
                                    errorInfo += "第H列数据无效,最多2位小数;";
                                }
                            }

                            if(!StringUtils.isEmpty(upLimit)){
                                upLimitCheck = Double.valueOf(upLimit);
                                String[] standard = upLimit.split(MesExtConstWords.DECIMAL_POINT);
                                if (null != standard && standard.length == 2 && standard[1].length() > 2) {
                                    errorNum++;
                                    cellNum += "I;";
                                    errorInfo += "第I列数据无效,最多2位小数;";
                                }
                            }

                            if(!StringUtils.isEmpty(downLimit)){
                                downLimitCheck =  Double.valueOf(downLimit);
                                String[] standard = downLimit.split(MesExtConstWords.DECIMAL_POINT);
                                if (null != standard && standard.length == 2 && standard[1].length() > 2) {
                                    errorNum++;
                                    cellNum += "J;";
                                    errorInfo += "第J列数据无效,最多2位小数;";
                                }
                            }

                            String computeType;
                            if( downLimitCheck != null && upLimitCheck != null && standardValueCheck != null){
                                computeType = ONE;
                            }else if(downLimitCheck != null && standardValueCheck != null){
                                computeType = TWO;
                            }else if(upLimitCheck != null && standardValueCheck != null){
                                computeType = THREE;
                            }else if(downLimitCheck != null && upLimitCheck != null){
                                computeType = FOUR;
                            }else {
                                computeType = FIVE;
                            }

                            switch (computeType){
                                case ONE:
                                    if(downLimitCheck > standardValueCheck || standardValueCheck > upLimitCheck || downLimitCheck > upLimitCheck){
                                        errorNum++;
                                        cellNum += "H;";
                                        errorInfo += "比值范围输入不合理;";
                                    }
                                    break;
                                case TWO:
                                    if(standardValueCheck < downLimitCheck){
                                        errorNum++;
                                        cellNum += "H;";
                                        errorInfo += "标准值大于上限值;";
                                    }
                                    break;
                                case THREE:
                                    if(standardValueCheck > upLimitCheck){
                                        errorNum++;
                                        cellNum += "H;";
                                        errorInfo += "标准值大于上限值;";
                                    }
                                    break;
                                case FOUR:
                                    if(downLimitCheck > upLimitCheck){
                                        errorNum++;
                                        cellNum += "H;";
                                        errorInfo += "上限值小于下限值;";
                                    }
                            }

                        }catch (Exception e){
                            errorNum++;
                            cellNum += "H;";
                            errorInfo += "不符合结果类型对应的请求参数类型;";
                        }

//                        if(!StringUtils.isEmpty(standardValue)){
//                            try{
//                                Double.valueOf(standardValue);
//                                String[] standardWorkArr = standardValue.split(MesExtConstWords.DECIMAL_POINT);
//                                if (null != standardWorkArr && standardWorkArr.length == 2 && standardWorkArr[1].length() > 2) {
//                                    errorNum++;
//                                    cellNum += "H;";
//                                    errorInfo += "第H列数据无效,最多2位小数;";
//                                }
//                            }catch (Exception e){
//                                errorNum ++;
//                                cellNum += "H;";
//                                errorInfo += "第H列结果类型为比值类型，必须输入数字;";
//                            }
//                        }
//
//                        if(!StringUtils.isEmpty(upLimit)) {
//                            try{
//                                Double.valueOf(upLimit);
//                                String[] standardWorkArr = upLimit.split(MesExtConstWords.DECIMAL_POINT);
//                                if (null != standardWorkArr && standardWorkArr.length == 2 && standardWorkArr[1].length() > 2) {
//                                    errorNum++;
//                                    cellNum += "I;";
//                                    errorInfo += "第I列数据无效,最多2位小数;";
//                                }
//                            }catch (Exception e){
//                                errorNum ++;
//                                cellNum += "I;";
//                                errorInfo += "第I列结果类型为比值类型，必须输入数字;";
//                            }
//                        }
//
//                        if(!StringUtils.isEmpty(downLimit)){
//                            try{
//                                Double.valueOf(downLimit);
//                                String[] standardWorkArr = downLimit.split(MesExtConstWords.DECIMAL_POINT);
//                                if (null != standardWorkArr && standardWorkArr.length == 2 && standardWorkArr[1].length() > 2) {
//                                    errorNum++;
//                                    cellNum += "J;";
//                                    errorInfo += "第J列数据无效,最多2位小数;";
//                                }
//                            }catch (Exception e){
//                                errorNum ++;
//                                cellNum += "J;";
//                                errorInfo += "第J列结果类型为比值类型，必须输入数字;";
//                            }
//                        }
                    }
                }

                //校验工作中心代码+工位代码+项目内容+点检方法+类型不重复
                String key = new StringJoiner(MesExtConstWords.AND).add(workCenterCode).add(workCellCode).add(equNo).add(projectContent).add(checkMethod).add(checkType).toString();
                if (!existMap.containsKey(key)) {
                    existMap.put(key, String.valueOf(rowNum));
                    //校验 工作中心代码+工位代码+设备代码+项目内容+点检方法+类型 是否已经存在数据库
                    if (StringUtils.isEmpty(errorInfo) && !CollectionUtils.isEmpty(equipmentCheckMap) && equipmentCheckMap.containsKey(key)) {
                        errorNum ++;
                        cellNum += "A+B+C+E+F+G;";
                        errorInfo += "第A+B+C+E+F+G列数据已经存在于数据库;";
                    }
                } else {
                    errorNum ++;
                    cellNum += "A+B+C+E+F+G;";
                    errorInfo += "第A+B+C+E+F+G列数据跟第" + existMap.get(key) + "行的第A+B+C+E+F+G列数据重复;";
                }

                //没有错误的时候，封装MesEquipment
                if (errorNum == 0) {
                    MesEpEquipmentCheck equipmentCheck = new MesEpEquipmentCheck();
                    equipmentCheck.setCheckType(checkType);
                    equipmentCheck.setCheckMethod(checkMethod);
                    equipmentCheck.setEquNo(equNo);
                    equipmentCheck.setEquName(equName);
                    equipmentCheck.setWorkCellCode(workCellCode);
                    equipmentCheck.setWorkCellName(workCellName);
                    equipmentCheck.setWorkCenterCode(workCenterCode);
                    equipmentCheck.setProjectContent(projectContent);
                    equipmentCheck.setResultType(resultType);
                    equipmentCheck.setStandardValue(standardValue);
                    equipmentCheck.setDownLimit(downLimit);
                    equipmentCheck.setUpLimit(upLimit);
                    equipmentCheck.setOrganizeCode(organizeCode);
                    equipmentCheck.setSystemSyncStatus(MesExtEnumUtil.IF_SYNC_STATUS.NO_SYNC.getValue());
                    mesEquipmentList.add(equipmentCheck);

                    successRowNum ++;
                } else {
                    //封装错误行信息ExcelImportErrorModel
                    excelImportErrorModels = commonService.getExcelImportErrorModels(excelImportErrorModels, rowNum, errorNum, cellNum, errorInfo);

                    errorRows += rowNum + ";";
                    failRowNum ++;
                }
            }
        }

        //校验EXCEL数据
        commonService.checkExcelData(failRowNum, successRowNum, errorRows);

        //封装返回结果
        ExcelImportResultExtModel excelImportResultModel = commonService.getExcelImportResultModel(failRowNum, successRowNum, excelImportErrorModels, errorRows);
        excelImportResultModel.setExcelList((failRowNum > 0) ? null : mesEquipmentList);
        return excelImportResultModel;
    }

    /**
     * 工位设备点检扩展导入-数据入库
     * @param excelImportResultExtModel
     */
    private void insertExcelMesEquipmentCheck(ExcelImportResultExtModel excelImportResultExtModel) {
        //导入数据
        if (null != excelImportResultExtModel) {
            List<MesEpEquipmentCheck> excelList = excelImportResultExtModel.getExcelList();
            if (!CollectionUtils.isEmpty(excelList)) {
                for (MesEpEquipmentCheck equipmentCheck : excelList) {
                    equipmentCheckRepository.insert(equipmentCheck);
                }
            }
        }
    }
}
