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

import cn.estsh.i3plus.ext.mes.api.base.IExcelImportExtService;
import cn.estsh.i3plus.ext.mes.api.base.bu.IMesTravelMenuFieldConfigService;
import cn.estsh.i3plus.ext.mes.api.base.bu.IPartDataExtService;
import cn.estsh.i3plus.ext.mes.apiservice.serviceimpl.base.CommonExtService;
import cn.estsh.i3plus.ext.mes.apiservice.util.BusiExtUtil;
import cn.estsh.i3plus.ext.mes.pojo.bean.MesTravelMenuFieldConfig;
import cn.estsh.i3plus.ext.mes.pojo.bean.SxMesDataTravel;
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.MesPartExtRepository;
import cn.estsh.i3plus.ext.mes.pojo.repository.SxMesDataTravelRepository;
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.convert.ConvertBean;
import cn.estsh.i3plus.pojo.base.enumutil.CommonEnumUtil;
import cn.estsh.i3plus.pojo.mes.bean.MesKpsnRule;
import cn.estsh.i3plus.pojo.mes.bean.MesPart;
import cn.estsh.i3plus.pojo.mes.repository.MesKpsnRuleRepository;
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.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

/**
 * @Description : 关键件条码校验规则导入，支持编辑
 * @Reference :
 * @Author : wangjie
 * @CreateDate : 2019-04-23
 * @Modify:
 **/
@Service
public class SxKpsnRuleExcelService implements IExcelImportExtService {

    @Autowired
    private MesPartExtRepository partExtRepository;

    @Autowired
    private MesKpsnRuleRepository kpsnRuleReposity;

    @Autowired
    private CommonExtService commonExtService;

    @Autowired
    private IMesTravelMenuFieldConfigService travelMenuFieldConfigService;

    @Autowired
    private SxMesDataTravelRepository dataTravelRepository;

    @Autowired
    private IPartDataExtService partService;

    /**
     * 关键件条码校验规则导入
     * @param workbook
     * @param organizeCode
     * @param userName
     * @return
     */
    @Override
    public ExcelImportResultExtModel insertDataByExcel(Workbook workbook, String organizeCode, String userName) {
        //读取表格
        ExcelImportResultExtModel excelImportResultExtModel = this.sheetExtractMesKpsnRule(workbook.getSheetAt(0), organizeCode, userName);
        //数据入库
        this.insertExcelMesKpsnRule(excelImportResultExtModel);
        return excelImportResultExtModel;
    }

    /**
     * 关键件条码校验规则导入-读取表格
     * @param sheetAt
     * @param organizeCode
     * @param userName
     * @return
     */
    private ExcelImportResultExtModel sheetExtractMesKpsnRule(Sheet sheetAt, String organizeCode, String userName) {
        //从0行开始读取
        int totalNumberOfRows = sheetAt.getLastRowNum() + 1;

        //MesKpsnRule集合
        List<MesKpsnRule> kpsnRuleList = new ArrayList<>();

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

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

        //用于EXCEL表中的物料唯一校验，key：partNo，value：row
        Map<String,String> partNoMap = new HashMap<>();

        //非负整数的正则表达式
        Pattern pattern = Pattern.compile("^[1-9]\\d*|0$");

        //查询关键件条码校验规则表
        List<MesKpsnRule> kpsnRules = kpsnRuleReposity.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<MesKpsnRule>> kpsnRuleDBMap = kpsnRules.stream().filter(o -> (null != o && !StringUtils.isEmpty(o.getPartNo()))).collect(Collectors.groupingBy(MesKpsnRule::getPartNo));


        List<MesTravelMenuFieldConfig> travelMenuFieldConfigList = travelMenuFieldConfigService.getTravelMenuFieldConfigList(organizeCode, MesExtEnumUtil.FIELD_MENU_TYPE.KPSN_RULE.getValue());
        //查询物料表
        Map<String, Object> partNoEntityMap;
        try {
            partNoEntityMap = MesCommonUtil.getCodeEntityMap(partExtRepository, MesExtConstWords.PART_NO, organizeCode, "物料表");
        } catch (Exception e) {
            partNoEntityMap = null;
        }

        //从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 partNo = row.getCell(0, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).getStringCellValue().trim();
                //长度
                String length = null;
                try {
                    row.getCell(1).setCellType(CellType.STRING);
                    length = row.getCell(1, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).getStringCellValue().trim();
                } catch (Exception e) {
                }
                //起始1
                String from1 = null;
                try {
                    row.getCell(2).setCellType(CellType.STRING);
                    from1 = row.getCell(2, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).getStringCellValue().trim();
                } catch (Exception e) {
                }
                //至1
                String to1 = null;
                try {
                    row.getCell(3).setCellType(CellType.STRING);
                    to1 = row.getCell(3, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).getStringCellValue().trim();
                } catch (Exception e) {
                }
                //内容1
                String fix1 = null;
                try {
                    row.getCell(4).setCellType(CellType.STRING);
                    fix1 = row.getCell(4, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).getStringCellValue().trim();
                } catch (Exception e) {
                }
                //起始2
                String from2 = null;
                try {
                    row.getCell(5).setCellType(CellType.STRING);
                    from2 = row.getCell(5, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).getStringCellValue().trim();
                } catch (Exception e) {
                }
                //至2
                String to2 = null;
                try {
                    row.getCell(6).setCellType(CellType.STRING);
                    to2 = row.getCell(6, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).getStringCellValue().trim();
                } catch (Exception e) {
                }
                //内容2
                String fix2 = null;
                try {
                    row.getCell(7).setCellType(CellType.STRING);
                    fix2 = row.getCell(7, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).getStringCellValue().trim();
                } catch (Exception e) {
                }

                //校验长度
                if(StringUtils.isEmpty(length)){
                    errorNum ++;
                    cellNum += "B;";
                    errorInfo += "第B列数必填;";
                }else{
                    //校验长度的有效性
                    if(!pattern.matcher(length).matches() || Integer.valueOf(length) == 0){
                        errorNum ++;
                        cellNum += "B;";
                        errorInfo += "第B列数必须是大于0的正整数;";
                    }else{
                        //长度有效的情况下，校验起始1
                        if(StringUtils.isEmpty(from1)){
                            //校验起始1必填
                            errorNum ++;
                            cellNum += "C;";
                            errorInfo += "第C列数据必填;";
                        }else{
                            //校验起始1数据有效性
                            if(!pattern.matcher(from1).matches() || Integer.valueOf(from1) == 0 || Integer.valueOf(from1) > Integer.valueOf(length)){
                                errorNum ++;
                                cellNum += "C;";
                                errorInfo += "第C列数无效;";
                            }else{
                                //长度和起始1有效的情况下
                                if(StringUtils.isEmpty(to1)){
                                    //校验至1必填
                                    errorNum ++;
                                    cellNum += "D;";
                                    errorInfo += "第D列数据必填;";
                                }else{
                                    //校验至1有效性
                                    if(!pattern.matcher(to1).matches() || Integer.valueOf(to1) < Integer.valueOf(from1) || Integer.valueOf(to1) > Integer.valueOf(length)){
                                        //校验至1必填
                                        errorNum ++;
                                        cellNum += "D;";
                                        errorInfo += "第D列数据无效;";
                                    }
                                }
                            }
                        }
                        //长度有效的情况下，并且起始2和至2都有值的情况下，校验起始2和至2
                        if(!StringUtils.isEmpty(from2) || !StringUtils.isEmpty(to2)){
                            if(!StringUtils.isEmpty(from2) && !StringUtils.isEmpty(to2)){
                                //校验起始2数据有效性
                                if (!pattern.matcher(from2).matches() || Integer.valueOf(from2) == 0 || Integer.valueOf(from2) > Integer.valueOf(length)) {
                                    errorNum++;
                                    cellNum += "F;";
                                    errorInfo += "第F列数无效;";
                                } else {
                                    //长度和起始2有效的情况下
                                    if (StringUtils.isEmpty(to2)) {
                                        //校验至2必填
                                        errorNum++;
                                        cellNum += "G;";
                                        errorInfo += "第G列数据必填;";
                                    } else {
                                        //校验至2有效性
                                        if (!pattern.matcher(to2).matches() || Integer.valueOf(to2) < Integer.valueOf(from2) || Integer.valueOf(to2) > Integer.valueOf(length)) {
                                            //校验至2必填
                                            errorNum++;
                                            cellNum += "G;";
                                            errorInfo += "第G列数据无效;";
                                        }
                                    }
                                }
                            }else{
                                //校验起始2和至2
                                errorNum ++;
                                cellNum += "FG;";
                                errorInfo += "第FG列数据必须同时存在或不存在;";
                            }
                        }
                    }
                }

                //校验内容1
                if(StringUtils.isEmpty(fix1)){
                    errorNum++;
                    cellNum += "E;";
                    errorInfo += "第E列数据必填;";
                }

                boolean isExist = false ;
                //物料唯一校验
                if(!partNoMap.containsKey(partNo)){
                    partNoMap.put(partNo, String.valueOf(rowNum));
                    //校验物料在数据库中是否存在
                    if(StringUtils.isEmpty(errorInfo) && !CollectionUtils.isEmpty(kpsnRuleDBMap) && kpsnRuleDBMap.containsKey(partNo)){
                        isExist = true;
                    }
                } else {
                    errorNum++;
                    cellNum += "A";
                    errorInfo += "第A列数据跟第" + partNoMap.get(partNo) + "行的第A列数据重复;";
                }

                //校验物料号
                if(CollectionUtils.isEmpty(partNoEntityMap) || (!CollectionUtils.isEmpty(partNoEntityMap) && !partNoEntityMap.containsKey(partNo))){
                    errorNum ++;
                    cellNum = "A;" + cellNum;
                    errorInfo = "第A列数据无效;" + errorInfo;
                }
                //新增关键件规则字段变更履历
                //没有错误的时候，封装MesKpsnRule
                if (errorNum == 0) {
                    MesKpsnRule kpsnRule;
                    if(isExist){
                        kpsnRule = kpsnRuleDBMap.get(partNo).get(0);
                        ConvertBean.serviceModelUpdate(kpsnRule,userName);
                        if(!CollectionUtils.isEmpty(travelMenuFieldConfigList)){
                            MesKpsnRule newKpsnRule = new MesKpsnRule();
                            newKpsnRule.setPartNo(partNo);
                            newKpsnRule.setLength(Integer.valueOf(length));
                            newKpsnRule.setFrom1(Integer.valueOf(from1));
                            newKpsnRule.setTo1(Integer.valueOf(to1));
                            newKpsnRule.setFix1(fix1);
                            newKpsnRule.setFrom2(StringUtils.isEmpty(from2)? 0 : Integer.valueOf(from2));
                            newKpsnRule.setTo2(StringUtils.isEmpty(to2)? 0 :Integer.valueOf(to2));
                            newKpsnRule.setFix2(StringUtils.isEmpty(fix2)? "" : fix2);
                            List<String> fields = travelMenuFieldConfigList.stream().map(MesTravelMenuFieldConfig::getFieldCode).distinct().collect(Collectors.toList());
                            Map<String, List<String>> fieldCompareResultMap = BusiExtUtil.checkParamValue(kpsnRule, newKpsnRule, MesKpsnRule.class, fields);
                            for (String field : fieldCompareResultMap.keySet()){
                                List<String> compareResultList = fieldCompareResultMap.get(field);
                                SxMesDataTravel dataTravel = new SxMesDataTravel();
                                dataTravel.setMenuCode(MesExtEnumUtil.FIELD_MENU_TYPE.PROCESS_BOM.getValue());
                                dataTravel.setPartNo(partNo);
                                MesPart mesPart = partService.getMesPart(kpsnRule.getOrganizeCode(), partNo);
                                dataTravel.setPartNameRdd( mesPart != null ? mesPart.getPartName() : "");
                                dataTravel.setFieldCode(field);
                                dataTravel.setBeforeChange(compareResultList.get(0));
                                dataTravel.setAfterChange(compareResultList.get(1));
                                ConvertBean.serviceModelInitialize(dataTravel, AuthUtil.getSessionUser().getUserName());
                                dataTravel.setOrganizeCode(kpsnRule.getOrganizeCode());
                                dataTravelRepository.insert(dataTravel);
                            }
                        }
                    }else{
                        kpsnRule = new MesKpsnRule();
                        ConvertBean.serviceModelInitialize(kpsnRule, userName);
                    }
                    kpsnRule.setPartNo(partNo);
                    kpsnRule.setLength(Integer.valueOf(length));
                    kpsnRule.setFrom1(Integer.valueOf(from1));
                    kpsnRule.setTo1(Integer.valueOf(to1));
                    kpsnRule.setFix1(fix1);
                    kpsnRule.setFrom2(StringUtils.isEmpty(from2)? 0 : Integer.valueOf(from2));
                    kpsnRule.setTo2(StringUtils.isEmpty(to2)? 0 :Integer.valueOf(to2));
                    kpsnRule.setFix2(StringUtils.isEmpty(fix2)? "" : fix2);
                    kpsnRule.setOrganizeCode(organizeCode);

                    kpsnRuleList.add(kpsnRule);

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

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

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

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

    /**
     * 关键件条码校验规则导入-数据入库
     * @param excelImportResultExtModel
     */
    private void insertExcelMesKpsnRule(ExcelImportResultExtModel excelImportResultExtModel) {
        //导入数据
        if (null != excelImportResultExtModel) {
            List<MesKpsnRule> excelList = excelImportResultExtModel.getExcelList();
            if (!CollectionUtils.isEmpty(excelList)) {
                for (MesKpsnRule kpsnRule : excelList) {
                    if(StringUtils.isEmpty(kpsnRule.getId())){
                        kpsnRuleReposity.insert(kpsnRule);
                    }else{
                        kpsnRuleReposity.update(kpsnRule);
                    }
                }
            }
        }
    }
}
