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.MesModuleFileImportLot;
import cn.estsh.i3plus.ext.mes.pojo.bean.MesPartExt;
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.MesModuleFileImportLotRepository;
import cn.estsh.i3plus.ext.mes.pojo.repository.MesPartExtRepository;
import cn.estsh.i3plus.ext.mes.pojo.util.ConvertBeanExt;
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.platform.common.exception.ImppExceptionEnum;
import cn.estsh.i3plus.platform.common.tool.MathOperation;
import cn.estsh.i3plus.pojo.base.enumutil.CommonEnumUtil;
import cn.estsh.i3plus.pojo.base.enumutil.MesEnumUtil;
import cn.estsh.i3plus.pojo.mes.bean.MesPartCategory;
import cn.estsh.i3plus.pojo.mes.repository.MesPartCategoryRepository;
import cn.estsh.i3plus.pojo.mes.repository.MesPartProduceTypeRepository;
import cn.estsh.impp.framework.boot.exception.ImppExceptionBuilder;
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.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
 * @author yumingxing
 * @version 1.0
 * @date 2021/4/13 13:09
 **/
@Service
public class EpPartExtExcelService implements IExcelImportExtService {

    @Autowired
    private MesPartCategoryRepository partCategoryRepository;

    @Autowired
    private MesPartExtRepository partExtRepository;

    @Autowired
    private MesPartProduceTypeRepository partProduceTypeRepository;

    @Autowired
    private MesModuleFileImportLotRepository moduleFileImportLotRepository;

    @Autowired
    private CommonExtService commonExtService;


    @Override
    public ExcelImportResultExtModel insertDataByExcel(Workbook workbook, String organizeCode, String userName) {
        ExcelImportResultExtModel excelImportResultModel = this.sheetExtractMesPart(workbook.getSheetAt(0), organizeCode, userName);
        this.insertExcelMesPart(excelImportResultModel);
        return excelImportResultModel;
    }

    private ExcelImportResultExtModel sheetExtractMesPart(Sheet sheetAt, String organizeCode, String userName) {
        //从0行开始读取
        int totalNumberOfRows = sheetAt.getLastRowNum() + 1;

        //批次号
        String lotNo = getCellData(getExcelRow(sheetAt, 3), 1, "B", "批次号", 3, false, "checkLotNoMethod", organizeCode);

        //MesPartExt集合
        List<MesPartExt> partExtList = new ArrayList<>();

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

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

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

        List<MesPartExt> partList = partExtRepository.findByProperty(
                new String[]{MesExtConstWords.ORGANIZE_CODE, MesExtConstWords.IS_DELETED},
                new Object[]{organizeCode, CommonEnumUtil.TRUE_OR_FALSE.FALSE.getValue()});
        Map<String, List<MesPartExt>> partNoMap = CollectionUtils.isEmpty(partList) ? null :
                partList.stream().filter(o -> null != o).collect(Collectors.groupingBy(MesPartExt::getPartNo));

        List<MesPartCategory> partCategoryList = partCategoryRepository.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<MesPartCategory>> partCategoryMap = CollectionUtils.isEmpty(partCategoryList) ? null :
                partCategoryList.stream().filter(o -> null != o).collect(Collectors.groupingBy(o -> o.getCategoryCode() + "&" + o.getCategoryType()));

        Map partProduceTypeEntityMap;
        try {
            partProduceTypeEntityMap = MesCommonUtil.getCodeEntityMap(partProduceTypeRepository, MesExtConstWords.PPT_CODE, organizeCode, "产品生产类型表");
        } catch (Exception var84) {
            partProduceTypeEntityMap = 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 = ""; //错误信息

                //物料代码
                String partNo = null;
                try {
                    row.getCell(0).setCellType(CellType.STRING);
                    partNo = row.getCell(0, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).getStringCellValue().trim();
                } catch (Exception var88) {
                    continue;
                }

                //物料名称
                String partName = null;
                try {
                    row.getCell(1).setCellType(CellType.STRING);
                    partName = row.getCell(1, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).getStringCellValue().trim();
                } catch (Exception var80) {
                }

                //物料简称
                String briefText = null;
                try {
                    row.getCell(2).setCellType(CellType.STRING);
                    briefText = row.getCell(2, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).getStringCellValue().trim();
                } catch (Exception var79) {
                }

                //单位
                String unit = null;
                try {
                    row.getCell(3).setCellType(CellType.STRING);
                    unit = row.getCell(3, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).getStringCellValue().trim();
                } catch (Exception var77) {
                }

                //物料类型1
                String categoryCode1 = null;
                try {
                    row.getCell(4).setCellType(CellType.STRING);
                    categoryCode1 = row.getCell(4, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).getStringCellValue().trim();
                } catch (Exception var76) {
                }

                //物料类型2
                String categoryCode2 = null;
                try {
                    row.getCell(5).setCellType(CellType.STRING);
                    categoryCode2 = row.getCell(5, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).getStringCellValue().trim();
                } catch (Exception var75) {
                }

                //物料类型3
                String categoryCode3 = null;
                try {
                    row.getCell(6).setCellType(CellType.STRING);
                    categoryCode3 = row.getCell(6, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).getStringCellValue().trim();
                } catch (Exception var74) {
                }

                //产品编码匹配类型
                String processMatchType = null;
                try {
                    row.getCell(7).setCellType(CellType.STRING);
                    processMatchType = row.getCell(7, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).getStringCellValue().trim();
                } catch (Exception var70) {
                }

                //包装编码匹配类型
                String packageMatchType = null;
                try {
                    row.getCell(8).setCellType(CellType.STRING);
                    packageMatchType = row.getCell(8, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).getStringCellValue().trim();
                } catch (Exception var68) {
                }

                //包装数量
                String qty2 = null;
                try {
                    row.getCell(9).setCellType(CellType.STRING);
                    qty2 = row.getCell(9, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).getStringCellValue().trim();
                } catch (Exception var67) {
                }

                //产品生产类型
                String pptCode = null;
                try {
                    row.getCell(10).setCellType(CellType.STRING);
                    pptCode = row.getCell(10, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).getStringCellValue().trim();
                } catch (Exception var66) {
                }

                //颜色代码
                String colorCode = null;
                try {
                    row.getCell(11).setCellType(CellType.STRING);
                    colorCode = row.getCell(11, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).getStringCellValue().trim();
                } catch (Exception var64) {
                }

                //颜色名称
                String colorName = null;
                try {
                    row.getCell(12).setCellType(CellType.STRING);
                    colorName = row.getCell(12, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).getStringCellValue().trim();
                } catch (Exception var63) {
                }

                //销售向
                String salesTo = null;
                try {
                    row.getCell(13).setCellType(CellType.STRING);
                    salesTo = row.getCell(13, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).getStringCellValue().trim();
                } catch (Exception var62) {
                }

                //是否出货检验
                String isOqc = null;
                try {
                    row.getCell(14).setCellType(CellType.STRING);
                    isOqc = row.getCell(14, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).getStringCellValue().trim();
                } catch (Exception var61) {
                }

                //是否生成条码
                String isCreateSn = null;
                try {
                    row.getCell(15).setCellType(CellType.STRING);
                    isCreateSn = row.getCell(15, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).getStringCellValue().trim();
                } catch (Exception var60) {
                }

                //是否PS部件
                String isPSParts = null;
                try {
                    row.getCell(16).setCellType(CellType.STRING);
                    isPSParts = row.getCell(16, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).getStringCellValue().trim();
                } catch (Exception var59) {
                }

                //标准工数(min)
                String standardWork = null;
                try {
                    row.getCell(17).setCellType(CellType.STRING);
                    standardWork = row.getCell(17, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).getStringCellValue().trim();
                } catch (Exception var58) {
                }

                //日文描述
                String japanDesc = null;
                try {
                    row.getCell(18).setCellType(CellType.STRING);
                    japanDesc = row.getCell(18, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).getStringCellValue().trim();
                } catch (Exception var58) {
                }

                //商品码
                String goodsCode = null;
                try {
                    row.getCell(19).setCellType(CellType.STRING);
                    goodsCode = row.getCell(19, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).getStringCellValue().trim();
                } catch (Exception var58) {
                }

                //前缀
                String prefix = null;
                try {
                    row.getCell(20).setCellType(CellType.STRING);
                    prefix = row.getCell(20, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).getStringCellValue().trim();
                } catch (Exception var58) {
                }

                //质保书码
                String qualityAssureCode = null;
                try {
                    row.getCell(21).setCellType(CellType.STRING);
                    qualityAssureCode = row.getCell(21, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).getStringCellValue().trim();
                } catch (Exception var58) {
                }

                //物料识别码
                String identifyCode = null;
                try {
                    row.getCell(22).setCellType(CellType.STRING);
                    identifyCode = row.getCell(22, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).getStringCellValue().trim();
                } catch (Exception var58) {
                }

                //条码末位数
                String snLastCode = null;
                try {
                    row.getCell(23).setCellType(CellType.STRING);
                    snLastCode = row.getCell(23, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).getStringCellValue().trim();
                } catch (Exception var58) {
                }

                //文件原名
                String fileOriginName = null;
                try {
                    row.getCell(24).setCellType(CellType.STRING);
                    fileOriginName = row.getCell(24, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).getStringCellValue().trim();
                } catch (Exception e) {
                }

                //成品批次号长度
                String partLotNoLengthValue = null;
                try {
                    row.getCell(25).setCellType(CellType.STRING);
                    partLotNoLengthValue = row.getCell(25, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).getStringCellValue().trim();
                } catch (Exception e) {
                }

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

                if (StringUtils.isEmpty(partName)) {
                    ++errorNum;
                    cellNum = cellNum + "B;";
                    errorInfo = errorInfo + "第B列数据必填;";
                }

                if (StringUtils.isEmpty(categoryCode1)) {
                    ++errorNum;
                    cellNum = cellNum + "E;";
                    errorInfo = errorInfo + "第E列数据必填;";
                } else if (!StringUtils.isEmpty(categoryCode1) && (CollectionUtils.isEmpty(partCategoryMap) || !CollectionUtils.isEmpty(partCategoryMap) && !partCategoryMap.containsKey(categoryCode1 + "&" + MesEnumUtil.PART_CATEGORY_TYPE.CATEGORY_ONE.getValue()))) {
                    ++errorNum;
                    cellNum = cellNum + "E;";
                    errorInfo = errorInfo + "第E列数据无效;";
                }

                if (!StringUtils.isEmpty(categoryCode2) && (CollectionUtils.isEmpty(partCategoryMap) || !CollectionUtils.isEmpty(partCategoryMap) && !partCategoryMap.containsKey(categoryCode2 + "&" + MesEnumUtil.PART_CATEGORY_TYPE.CATEGORY_TWO.getValue()))) {
                    ++errorNum;
                    cellNum = cellNum + "F;";
                    errorInfo = errorInfo + "第F列数据无效;";
                }

                if (!StringUtils.isEmpty(categoryCode3) && (CollectionUtils.isEmpty(partCategoryMap) || !CollectionUtils.isEmpty(partCategoryMap) && !partCategoryMap.containsKey(categoryCode3 + "&" + MesEnumUtil.PART_CATEGORY_TYPE.CATEGORY_THREE.getValue()))) {
                    ++errorNum;
                    cellNum = cellNum + "G;";
                    errorInfo = errorInfo + "第G列数据无效;";
                }

                if (StringUtils.isEmpty(processMatchType)) {
                    ++errorNum;
                    cellNum = cellNum + "H;";
                    errorInfo = errorInfo + "第H列数据必填;";
                } else {
                    if (StringUtils.isEmpty(MesExtEnumUtil.ENCODE_MATCH_TYPE.descriptionOfValue(processMatchType))) {
                        ++errorNum;
                        cellNum = cellNum + "H;";
                        errorInfo = errorInfo + "第H列数据无效;";
                    }
                }

                if (StringUtils.isEmpty(packageMatchType)) {
                    ++errorNum;
                    cellNum = cellNum + "I;";
                    errorInfo = errorInfo + "第I列数据必填;";
                } else {
                    if (StringUtils.isEmpty(MesExtEnumUtil.ENCODE_MATCH_TYPE.descriptionOfValue(packageMatchType))) {
                        ++errorNum;
                        cellNum = cellNum + "I;";
                        errorInfo = errorInfo + "第I列数据无效;";
                    }
                }

                if (!StringUtils.isEmpty(qty2)) {
                    try {
                        Integer.valueOf(qty2);
                    } catch (NumberFormatException var83) {
                        ++errorNum;
                        cellNum = cellNum + "J;";
                        errorInfo = errorInfo + "第J列数据无效;";
                    }
                }

                if (!StringUtils.isEmpty(pptCode) && (CollectionUtils.isEmpty(partProduceTypeEntityMap) || !CollectionUtils.isEmpty(partProduceTypeEntityMap) && !partProduceTypeEntityMap.containsKey(pptCode))) {
                    ++errorNum;
                    cellNum = cellNum + "K;";
                    errorInfo = errorInfo + "第K列数据无效;";
                }

                if (!StringUtils.isEmpty(salesTo) && StringUtils.isEmpty(MesExtEnumUtil.SALES_TO.descriptionOfValue(salesTo))) {
                    ++errorNum;
                    cellNum = cellNum + "N;";
                    errorInfo = errorInfo + "第N列数据无效;";
                }

                if (StringUtils.isEmpty(isOqc)) {
                    ++errorNum;
                    cellNum = cellNum + "O;";
                    errorInfo = errorInfo + "第O列数据必填;";
                } else {
                    if (StringUtils.isEmpty(CommonEnumUtil.TRUE_OR_FALSE.descriptionOfValue(isOqc))) {
                        ++errorNum;
                        cellNum = cellNum + "O";
                        errorInfo = errorInfo + "第O列数据无效;";
                    }
                }

                if (StringUtils.isEmpty(isCreateSn)) {
                    ++errorNum;
                    cellNum = cellNum + "P;";
                    errorInfo = errorInfo + "第P列数据必填;";
                } else {
                    if (StringUtils.isEmpty(CommonEnumUtil.TRUE_OR_FALSE.descriptionOfValue(isCreateSn))) {
                        ++errorNum;
                        cellNum = cellNum + "P";
                        errorInfo = errorInfo + "第P列数据无效;";
                    }
                }

                if (StringUtils.isEmpty(isPSParts)) {
                    ++errorNum;
                    cellNum = cellNum + "Q;";
                    errorInfo = errorInfo + "第Q列数据必填;";
                } else {
                    if (StringUtils.isEmpty(CommonEnumUtil.TRUE_OR_FALSE.descriptionOfValue(isPSParts))) {
                        ++errorNum;
                        cellNum = cellNum + "Q";
                        errorInfo = errorInfo + "第Q列数据无效;";
                    }
                }

                if (!StringUtils.isEmpty(standardWork)) {
                    try {
                        if (MathOperation.compareTo(Double.valueOf(standardWork), new Double(0)) < 0) {
                            errorNum++;
                            cellNum += "R;";
                            errorInfo += "第R列数据不能小于0;";
                        }
                        String[] standardWorkArr = standardWork.split(MesExtConstWords.DECIMAL_POINT);
                        if (null != standardWorkArr && standardWorkArr.length == 2 && standardWorkArr[1].length() > 2) {
                            errorNum++;
                            cellNum += "R;";
                            errorInfo += "第R列数据无效,最多2位小数;";
                        }
                    } catch (NumberFormatException e) {
                        errorNum ++;
                        cellNum += "R;";
                        errorInfo += "第R列数据无效;";
                    }
                }

                //校验文件原名
                if (!StringUtils.isEmpty(fileOriginName)) {
                    if (!fileOriginName.contains(".")) {
                        errorNum ++;
                        cellNum += "Y;";
                        errorInfo += "第Y列数据无效;";
                    } else {
                        String fileType = fileOriginName.substring(fileOriginName.lastIndexOf("."));
                        if (StringUtils.isEmpty(fileType) || (!fileType.equals(".jpg") && !fileType.equals(".jpeg") && !fileType.equals(".png"))) {
                            errorNum ++;
                            cellNum += "Y;";
                            errorInfo += "第Y列数据无效;";
                        }
                    }
                }
                Integer partLotNoLength = null;
                if(partLotNoLengthValue != null){
                    try{
                        partLotNoLength = Integer.valueOf(partLotNoLengthValue);
                    }catch (Exception e){
                        ++errorNum;
                        cellNum = cellNum + "Z;";
                        errorInfo = errorInfo + "第Z列数据格式校验失败;";
                    }
                }

                if (errorNum == 0) {
                    MesPartExt mesPart;
                    if (isExist) {
                        mesPart = (MesPartExt) ((List) partNoMap.get(partNo)).get(0);
                        ConvertBean.serviceModelUpdate(mesPart, userName);
                    } else {
                        mesPart = new MesPartExt();
                        ConvertBean.serviceModelInitialize(mesPart, userName);
                    }

                    mesPart.setPartNo(partNo);
                    mesPart.setPartName(partName);
                    mesPart.setBriefText(StringUtils.isEmpty(briefText) ? null : briefText);
                    mesPart.setUnit(unit);
                    mesPart.setCategoryCode1(categoryCode1);
                    mesPart.setCategoryCode2(StringUtils.isEmpty(categoryCode2) ? null : categoryCode2);
                    mesPart.setCategoryCode3(StringUtils.isEmpty(categoryCode3) ? null : categoryCode3);
                    mesPart.setProcessMatchType(StringUtils.isEmpty(processMatchType) ? null : MesExtEnumUtil.ENCODE_MATCH_TYPE.descriptionOfValue(processMatchType));
                    mesPart.setPackageMatchType(StringUtils.isEmpty(packageMatchType) ? null : MesExtEnumUtil.ENCODE_MATCH_TYPE.descriptionOfValue(packageMatchType));
                    mesPart.setQty2(StringUtils.isEmpty(qty2) ? null : Double.parseDouble(qty2));
                    mesPart.setPptCode(pptCode);
                    mesPart.setColorCode(colorCode);
                    mesPart.setColorName(colorName);
                    mesPart.setSalesTo(!StringUtils.isEmpty(salesTo) ? String.valueOf(MesExtEnumUtil.SALES_TO.descriptionOfValue(salesTo)) : null);
                    mesPart.setIsOqc(!StringUtils.isEmpty(isOqc) ? CommonEnumUtil.TRUE_OR_FALSE.descriptionOfValue(isOqc) : null);
                    mesPart.setIsCreateSn(!StringUtils.isEmpty(isCreateSn) ? CommonEnumUtil.TRUE_OR_FALSE.descriptionOfValue(isCreateSn) : null);
                    mesPart.setIsPSParts(!StringUtils.isEmpty(isPSParts) ? CommonEnumUtil.TRUE_OR_FALSE.descriptionOfValue(isPSParts) : null);
                    mesPart.setStandardWork(StringUtils.isEmpty(standardWork) ? null : Double.parseDouble(standardWork));
                    mesPart.setJapanDesc(japanDesc);
                    mesPart.setGoodsCode(goodsCode);
                    mesPart.setPrefix(prefix);
                    mesPart.setQualityAssureCode(qualityAssureCode);
                    mesPart.setIdentifyCode(identifyCode);
                    mesPart.setSnLastCode(snLastCode);
                    mesPart.setFileOriginName(fileOriginName);
                    mesPart.setLotNo(lotNo);
                    mesPart.setOrganizeCode(organizeCode);
                    mesPart.setPartNoExt(partNo);
                    mesPart.setPartLotNoLength(partLotNoLength);
                    new ConvertBeanExt(mesPart).convertBean(mesPart);

                    partExtList.add(mesPart);
                    successRowNum = successRowNum + 1;
                } else {
                    //封装错误行信息ExcelImportErrorExtModel
                    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 : partExtList);
        return excelImportResultModel;
    }

    private void insertExcelMesPart(ExcelImportResultExtModel excelImportResultExtModel) {
        if (null != excelImportResultExtModel) {
            List<MesPartExt> excelList = excelImportResultExtModel.getExcelList();
            if (!CollectionUtils.isEmpty(excelList)) {
                for (MesPartExt partExt : excelList) {
                    if(StringUtils.isEmpty(partExt.getId())){
                        partExtRepository.insert(partExt);
                    }else{
                        partExtRepository.save(partExt);
                    }
                }
                MesModuleFileImportLot lot = new MesModuleFileImportLot();
                lot.setLotNo(excelList.get(0).getLotNo());
                lot.setModule(MesExtEnumUtil.BATCH_FILE_IMPORT_MODULE.PART.getValue());
                lot.setOrganizeCode(excelList.get(0).getOrganizeCode());
                ConvertBean.serviceModelInitialize(lot, excelList.get(0).getCreateUser());
                moduleFileImportLotRepository.insert(lot);
            }
        }
    }

    /**
     * 获取EXCEL的行数据
     * @param sheetAt
     * @param i
     * @return
     */
    private Row getExcelRow(Sheet sheetAt, int i) {
        if (i == 0) {
            return null;
        }
        Row row = sheetAt.getRow(sheetAt.getFirstRowNum() + (i - 1));
        if (null == row) {
            throw ImppExceptionBuilder.newInstance()
                    .setSystemID(CommonEnumUtil.SOFT_TYPE.MES.getCode())
                    .setErrorCode(ImppExceptionEnum.VARIFY_EXCEPTION.getCode())
                    .setErrorDetail("EXCEL表中第%s行为空", i)
                    .build();
        }
        return row;
    }

    /**
     * 获取当前行每列的数据
     * @param row
     * @param cell
     * @param cellLetter
     * @param cell
     * @param fieldName
     * @param checkNullFlag
     * @return
     */
    private String getCellData(Row row, int cell, String cellLetter, String fieldName, int rowNum, boolean checkNullFlag, String methodStr, Object... obj) {
        String data = null;
        try {
            row.getCell(cell).setCellType(CellType.STRING);
            data = row.getCell(cell, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).getStringCellValue().trim();
        } catch (Exception e) {
        }

        checkDataNull(checkNullFlag, data, rowNum, cellLetter, fieldName);

        if (!StringUtils.isEmpty(methodStr)) {
            Method method;
            try {
                method = this.getClass().getDeclaredMethod(methodStr, String.class, int.class, String.class, String.class, Object[].class);
                method.setAccessible(true);
                Object result = method.invoke(this, data, rowNum, cellLetter, fieldName, obj);
                data = !StringUtils.isEmpty(result) ? result.toString() : data;
            } catch (Exception e) {
                InvocationTargetException targetEx = (InvocationTargetException) e;
                Throwable trowEx = targetEx.getTargetException();
                throw ImppExceptionBuilder.newInstance()
                        .setSystemID(CommonEnumUtil.SOFT_TYPE.MES.getCode())
                        .setErrorCode(ImppExceptionEnum.VARIFY_EXCEPTION.getCode())
                        .setErrorDetail(trowEx.getMessage())
                        .build();
            }
        }
        return data;
    }

    /**
     * 数据判空
     * @param checkNullFlag
     * @param data
     * @param rowNum
     * @param cellLetter
     * @param fieldName
     */
    private void checkDataNull(boolean checkNullFlag, String data, int rowNum, String cellLetter, String fieldName) {
        if (checkNullFlag && StringUtils.isEmpty(data)) {
            throw ImppExceptionBuilder.newInstance()
                    .setSystemID(CommonEnumUtil.SOFT_TYPE.MES.getCode())
                    .setErrorCode(ImppExceptionEnum.VARIFY_EXCEPTION.getCode())
                    .setErrorDetail("EXCEL表中第%s行%s列%s为空", rowNum, cellLetter, fieldName)
                    .build();
        }
    }

    /**
     * 批次号
     * @param data
     * @param rowNum
     * @param cellLetter
     * @param fieldName
     * @param obj -> organizeCode
     * @return
     */
    private String checkLotNoMethod(String data, int rowNum, String cellLetter, String fieldName, Object... obj){
        if (!StringUtils.isEmpty(data)) {
            if (data.length() > 20) {
                throw ImppExceptionBuilder.newInstance()
                        .setSystemID(CommonEnumUtil.SOFT_TYPE.MES.getCode())
                        .setErrorCode(ImppExceptionEnum.VARIFY_EXCEPTION.getCode())
                        .setErrorDetail("EXCEL表中第%s行%s列%长度超过20个字符", rowNum, cellLetter, fieldName)
                        .build();
            }
            MesModuleFileImportLot lot = moduleFileImportLotRepository.getByProperty(
                    new String[]{MesExtConstWords.ORGANIZE_CODE, MesExtConstWords.IS_DELETED, MesExtConstWords.IS_VALID, MesExtConstWords.LOT_NO, MesExtConstWords.MODULE},
                    new Object[]{obj[0].toString(), CommonEnumUtil.TRUE_OR_FALSE.FALSE.getValue(), CommonEnumUtil.IS_VAILD.VAILD.getValue(), data, MesExtEnumUtil.BATCH_FILE_IMPORT_MODULE.PART.getValue()});
            if (null != lot) {
                throw ImppExceptionBuilder.newInstance()
                        .setSystemID(CommonEnumUtil.SOFT_TYPE.MES.getCode())
                        .setErrorCode(ImppExceptionEnum.VARIFY_EXCEPTION.getCode())
                        .setErrorDetail("EXCEL表中第%s行%s列%s已存在数据库", rowNum, cellLetter, fieldName)
                        .build();
            }
        }
        return data;
    }

}
