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

import cn.estsh.i3plus.ext.mes.api.base.IExcelImportExtService;
import cn.estsh.i3plus.ext.mes.apiservice.dao.IMesInsertBatchDao;
import cn.estsh.i3plus.ext.mes.apiservice.serviceimpl.base.CommonExtService;
import cn.estsh.i3plus.ext.mes.pojo.bean.MesProcessBom;
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.MesProcessBomRepository;
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.util.WmsConstWords;
import cn.estsh.i3plus.pojo.base.enumutil.CommonEnumUtil;
import cn.estsh.i3plus.pojo.mes.bean.MesPart;
import cn.estsh.i3plus.pojo.mes.bean.MesProcess;
import cn.estsh.i3plus.pojo.mes.repository.MesPartRepository;
import cn.estsh.i3plus.pojo.mes.repository.MesProcessRepository;
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.*;
import java.util.stream.Collectors;

/**
 * @Author: xinwang.yi
 * @CreateDate: 2022/09/30 14:22
 * @Description: BU工序BOM项目导入
 **/
@Service
public class BuProcessBomExtExcelService implements IExcelImportExtService {

    @Autowired
    private MesProcessBomRepository mesProcessBomRDao;

    @Autowired
    private MesProcessRepository mesProcessRDao;

    @Autowired
    private MesPartRepository mesPartRDao;

    @Autowired
    private CommonExtService commonExtService;

    @Autowired
    private IMesInsertBatchDao iMesInsertBatchDao;
    /**
     * 工序BOM导入
     *
     * @param workbook
     * @param organizeCode
     * @param userName
     * @return
     */
    @Override
    public ExcelImportResultExtModel insertDataByExcel(Workbook workbook, String organizeCode, String userName) {
        //读取表格
        ExcelImportResultExtModel excelImportResultExtModel = this.sheetExtractMesProcessBom(workbook.getSheetAt(0), organizeCode, userName);
        //数据入库
        this.insertExcelMesProcessBom(excelImportResultExtModel);
        return excelImportResultExtModel;
    }

    /**
     * 工序BOM导入-读取表格
     *
     * @param sheetAt
     * @param organizeCode
     * @param userName
     * @return
     */
    private ExcelImportResultExtModel sheetExtractMesProcessBom(Sheet sheetAt, String organizeCode, String userName) {
        //从0行开始读取
        int totalNumberOfRows = sheetAt.getLastRowNum() + 1;

        //MesProcessBom集合
        List<MesProcessBom> processBomExtList = new ArrayList<>();

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

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

        //用于EXCEL表中的 成品物料编码+原材料物料编码+工序代码 唯一校验
        Map<String, String> processBomByOnlyMap = new HashMap<>();

        //用于EXCEL表中的 成品物料编码+工序代码+序号 唯一校验
        Map<String, String> processBomByOnlyWithSeqMap = new HashMap<>();

        //根据零件号、工序代码、管理项目查询替代料中是否存在数据
        List<MesProcessBom> processBomList = mesProcessBomRDao.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<MesProcessBom>> processBomMap = CollectionUtils.isEmpty(processBomList) ? null :
                processBomList.stream().filter(o -> null != o).collect(Collectors.groupingBy(o -> o.getPartNo() + "&" + o.getItemPartNo() + "&" + o.getProcessCode() + "&" + o.getIsBindKey()));

        // 唯一键 带排序号
        Map<String, List<MesProcessBom>> processBomWithSeqMap = CollectionUtils.isEmpty(processBomList) ? null :
                processBomList.stream().filter(o -> null != o).collect(Collectors.groupingBy(o ->
                        new StringJoiner(MesExtConstWords.AND).add(o.getPartNo()).add(o.getProcessCode()).add(o.getIsBindKey().toString()).add(String.valueOf(o.getSeq())).toString()));

        // 查询物料表
        Map<String, Object> partNoEntityMap;
        try {
            partNoEntityMap = MesCommonUtil.getCodeEntityMap(mesPartRDao, MesExtConstWords.PART_NO, organizeCode, "物料表");
        } catch (Exception e) {
            partNoEntityMap = null;
        }

        // 查询工序表
        Map<String, Object> processCodeEntityMap;
        try {
            processCodeEntityMap = MesCommonUtil.getCodeEntityMap(mesProcessRDao, MesExtConstWords.PROCESS_CODE, organizeCode, "工序表");
        } catch (Exception e) {
            processCodeEntityMap = 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 partNameRdd = null;

                //原材料物料编码
                String itemPartNo = null;
                String itemPartName = null;
                try {
                    row.getCell(1).setCellType(CellType.STRING);
                    itemPartNo = row.getCell(1, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).getStringCellValue().trim();
                } catch (Exception e) {
                }

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

                //数量
                String qty = null;
                try {
                    row.getCell(3).setCellType(CellType.STRING);
                    qty = row.getCell(3, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).getStringCellValue().trim();
                } catch (Exception e) {
                }

                //是否绑定
                String isBindKeyStr = null;
                try {
                    row.getCell(4).setCellType(CellType.STRING);
                    isBindKeyStr = row.getCell(4, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).getStringCellValue().trim();
                } catch (Exception e) {
                }

                //是否可重复
                String isRepeat = null;
                try {
                    row.getCell(5).setCellType(CellType.STRING);
                    isRepeat = row.getCell(5, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).getStringCellValue().trim();
                } catch (Exception e) {
                }

                //是否检查
                String isCheck = null;
                try {
                    row.getCell(6).setCellType(CellType.STRING);
                    isCheck = row.getCell(6, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).getStringCellValue().trim();
                } catch (Exception e) {
                }

                //序号
                String seqStr = null;
                try {
                    row.getCell(7).setCellType(CellType.STRING);
                    seqStr = row.getCell(7, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).getStringCellValue().trim();
                } catch (Exception e) {
                }

                //校验成品物料编码
                if (StringUtils.isEmpty(partNo)) {
                    errorNum++;
                    cellNum += "A;";
                    errorInfo += "第A列数据必填;";
                } else if (!partNoEntityMap.containsKey(partNo)) {
                    errorNum++;
                    cellNum += "A;";
                    errorInfo += "第A列数据无效;";
                } else {
                    partNameRdd = ((MesPart) partNoEntityMap.get(partNo)).getPartName();
                }

                //校验原材料物料编码
                if (StringUtils.isEmpty(itemPartNo)) {
                    errorNum++;
                    cellNum += "B;";
                    errorInfo += "第B列数据必填;";
                } else if (!partNoEntityMap.containsKey(itemPartNo)) {
                    errorNum++;
                    cellNum += "B;";
                    errorInfo += "第B列数据无效;";
                } else {
                    itemPartName = ((MesPart) partNoEntityMap.get(itemPartNo)).getPartName();
                }

                //校验工序编号
                if (StringUtils.isEmpty(processCode)) {
                    errorNum++;
                    cellNum += "C;";
                    errorInfo += "第C列数据必填;";
                } else if (!processCodeEntityMap.containsKey(processCode)) {
                    errorNum++;
                    cellNum += "C;";
                    errorInfo += "第C列数据无效;";
                } else {
                    processName = ((MesProcess) processCodeEntityMap.get(processCode)).getProcessName();
                }

                //校验数量
                if (StringUtils.isEmpty(qty)) {
                    errorNum++;
                    cellNum += "D;";
                    errorInfo += "第D列数据必填;";
                } else {
                    try {
                        Double.parseDouble(qty);
                    } catch (NumberFormatException e) {
                        errorNum++;
                        cellNum += "D;";
                        errorInfo += "第D列数据数字类型错误;";
                    }
                }

                //校验是否绑定
                Integer isBindKey = null;
                if (StringUtils.isEmpty(isBindKeyStr)) {
                    errorNum++;
                    cellNum += "E;";
                    errorInfo += "第E列数据必填;";
                } else if (!StringUtils.isEmpty(isBindKeyStr) && StringUtils.isEmpty(MesExtEnumUtil.IS_BIND_KEY.descriptionOfValue(isBindKeyStr))) {
                    errorNum++;
                    cellNum += "E;";
                    errorInfo += "第E列数据无效;";
                }else{
                    isBindKey = MesExtEnumUtil.IS_BIND_KEY.descriptionOfValue(isBindKeyStr);
                }

                if (!StringUtils.isEmpty(isRepeat)) {
                    if (StringUtils.isEmpty(MesExtEnumUtil.IS_REPEAT.descriptionOfValue(isRepeat))) {
                        errorNum++;
                        cellNum += "F;";
                        errorInfo += "第F列数据无效;";
                    }
                }

                if (!StringUtils.isEmpty(isCheck)) {
                    if (StringUtils.isEmpty(MesExtEnumUtil.IS_CHECK.descriptionOfValue(isCheck))) {
                        errorNum++;
                        cellNum += "G;";
                        errorInfo += "第G列数据无效;";
                    }
                }

                Double seq = null;
                if (!StringUtils.isEmpty(seqStr)) {
                    try {
                        seq = Double.parseDouble(seqStr);
                        if(seq <= 0){
                            errorNum++;
                            cellNum += "H;";
                            errorInfo += "第H列数据【序号】必须为正整数;";
                        }
                    } catch (NumberFormatException e) {
                        errorNum++;
                        cellNum += "H;";
                        errorInfo += "第H列数据无效;";
                    }
                }

                if(isBindKey != null && isBindKey == MesExtEnumUtil.IS_BIND_KEY.YES.getValue() && seq == null){
                    errorNum++;
                    cellNum += "H;";
                    errorInfo += "当绑定关键件维护成是时,第H列数据为必填;";
                }

                if(isBindKey != null && isBindKey == MesExtEnumUtil.IS_BIND_KEY.NO.getValue() && seq != null){
                    errorNum++;
                    cellNum += "H;";
                    errorInfo += "当绑定关键件维护成否时,第H列数据不可填;";
                }

                if(errorNum == 0){
                    //校验唯一性 不重复
                    if (!processBomByOnlyMap.containsKey(new StringJoiner(MesExtConstWords.AND).add(partNo).add(itemPartNo).add(processCode).add(String.valueOf(isBindKey)).toString())) {
                        processBomByOnlyMap.put(new StringJoiner(MesExtConstWords.AND).add(partNo).add(itemPartNo).add(processCode).add(String.valueOf(isBindKey)).toString(), String.valueOf(rowNum));
                        //根据物料号、零件号、工序代码、是否绑定关键件查询替代料中是否存在数据
                        if (StringUtils.isEmpty(errorInfo) && !CollectionUtils.isEmpty(processBomMap) && processBomMap.containsKey(new StringJoiner(MesExtConstWords.AND).add(partNo).add(itemPartNo).add(processCode).add(String.valueOf(isBindKey)).toString())) {
                            errorNum++;
                            cellNum += "A+B+C+E;";
                            errorInfo += "第A+B+C+E列数据已经存在数据库;";
                        }
                    } else {
                        errorNum++;
                        cellNum += "A+B+C+E;";
                        errorInfo += "第A+B+C+E列数据跟第" + processBomByOnlyMap.get(new StringJoiner(MesExtConstWords.AND).add(partNo).add(itemPartNo).add(processCode).add(String.valueOf(isBindKey)).toString()) +
                                "行的第A+B+C+E列数据重复;";
                    }
                    if(seq != null){
                        //校验唯一性 不重复
                        if (!processBomByOnlyWithSeqMap.containsKey(new StringJoiner(MesExtConstWords.AND).add(partNo).add(processCode).add(String.valueOf(isBindKey)).add(String.valueOf(seq)).toString())) {
                            processBomByOnlyWithSeqMap.put(new StringJoiner(MesExtConstWords.AND).add(partNo).add(processCode).add(String.valueOf(isBindKey)).add(String.valueOf(seq)).toString(), String.valueOf(rowNum));
                            //根据物料号、工序代码、是否绑定关键件、序号查询替代料中是否存在数据

                            if (StringUtils.isEmpty(errorInfo) && !CollectionUtils.isEmpty(processBomWithSeqMap) && processBomWithSeqMap.containsKey(new StringJoiner(MesExtConstWords.AND).add(partNo).add(processCode).add(String.valueOf(isBindKey)).add(String.valueOf(seq)).toString())) {
                                errorNum++;
                                cellNum += "A+C+E+H;";
                                errorInfo += "第A+C+E+H列数据已经存在数据库;";
                            }
                        } else {
                            errorNum++;
                            cellNum += "A+C+E+H;";
                            errorInfo += "第A+C+E+H列数据跟第" + processBomByOnlyWithSeqMap.get(new StringJoiner(MesExtConstWords.AND).add(partNo).add(processCode).add(String.valueOf(isBindKey)).add(String.valueOf(seq)).toString()) +
                                    "行的第A+C+E+H列数据重复;";
                        }
                    }
                }

                //没有错误的时候，封装MesProcessBom
                if (errorNum == 0) {
                    MesProcessBom mesProcessBom = new MesProcessBom();
                    mesProcessBom.setPartNo(partNo);
                    mesProcessBom.setPartNameRdd(partNameRdd);
                    mesProcessBom.setItemPartNo(itemPartNo);
                    mesProcessBom.setItemPartName(itemPartName);
                    mesProcessBom.setProcessCode(processCode);
                    mesProcessBom.setProcessName(processName);
                    mesProcessBom.setQty(Double.parseDouble(qty));
                    mesProcessBom.setIsBindKey(isBindKey);
                    mesProcessBom.setIsRepeat(!StringUtils.isEmpty(isRepeat) ? MesExtEnumUtil.IS_REPEAT.descriptionOfValue(isRepeat) : CommonEnumUtil.TRUE_OR_FALSE.FALSE.getValue());
                    mesProcessBom.setIsCheck(!StringUtils.isEmpty(isCheck) ? MesExtEnumUtil.IS_CHECK.descriptionOfValue(isCheck) : CommonEnumUtil.TRUE_OR_FALSE.FALSE.getValue());
                    mesProcessBom.setSeq(seq);
                    mesProcessBom.setOrganizeCode(organizeCode);
                    ConvertBean.serviceModelInitialize(mesProcessBom, userName);
                    processBomExtList.add(mesProcessBom);
                    successRowNum++;
                } else {
                    //封装错误行信息ExcelImportErrorExtModel
                    excelImportErrorExtModels = commonExtService.getExcelImportErrorModels(excelImportErrorExtModels, rowNum, errorNum, cellNum, errorInfo);

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

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

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

    /**
     * 工序BOM导入-数据入库
     *
     * @param excelImportResultExtModel
     */
    private void insertExcelMesProcessBom(ExcelImportResultExtModel excelImportResultExtModel) {
        //导入数据
        if (null != excelImportResultExtModel) {
            List<MesProcessBom> processBomList = excelImportResultExtModel.getExcelList();
            if (!CollectionUtils.isEmpty(processBomList)) {
//                for (MesProcessBom processBom : processBomList) {
//                    mesProcessBomRDao.insert(processBom);
//                }
                //批量导入
                int toNum;
                for (int i = 0, len = (processBomList.size() / WmsConstWords.BASE_NUM + 1); i < len; i++) {
                    toNum = (i + 1) * WmsConstWords.BASE_NUM;
                    if ((i + 1) * WmsConstWords.BASE_NUM > processBomList.size()) {
                        toNum = processBomList.size();
                    }
                    iMesInsertBatchDao.insertMesProcessBomBatch(processBomList.subList(i * WmsConstWords.BASE_NUM, toNum));
                }

            }
        }
    }
}
