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

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.*;
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.*;
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.platform.common.convert.ConvertBean;
import cn.estsh.i3plus.platform.common.tool.MathOperation;
import cn.estsh.i3plus.platform.common.tool.TimeTool;
import cn.estsh.i3plus.pojo.base.bean.DdlPackBean;
import cn.estsh.i3plus.pojo.base.enumutil.CommonEnumUtil;
import cn.estsh.i3plus.pojo.base.tool.DdlPreparedPack;
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.BeanUtils;
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.function.Function;
import java.util.stream.Collectors;

/**
 * @Description : 解绑条码绑定导入
 * @Reference :
 * @Author : wangjie
 * @CreateDate : 2019-04-23
 * @Modify:
 **/
@Service
public class SxWorkOrderBindUnbindSnExcelService implements IExcelImportExtService {

    @Autowired
    private MesProduceSnExtRepository produceSnExtRepository;

    @Autowired
    private MesWorkOrderExtRepository workOrderExtRepository;

    @Autowired
    private MesUnbindProduceSnRepository unBindProduceSnRepository;

    @Autowired
    private MesUnbindProduceSnTravelRepository unBindProduceSnTravelRepository;

    @Autowired
    private CommonExtService commonExtService;

    /**
     * SOP导入
     * @param workbook
     * @param organizeCode
     * @param userName
     * @return
     */
    @Override
    public ExcelImportResultExtModel insertDataByExcel(Workbook workbook, String organizeCode, String userName) {
        //读取表格
        ExcelImportResultExtModel excelImportResultExtModel = this.sheetExtractUnbindSnBind(workbook.getSheetAt(0), organizeCode, userName);
        return excelImportResultExtModel;
    }

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

        Map<String, MesWorkOrderExt> workOrderMapByOrderNo = new HashMap<>();
        // 工单待绑定条码数量
        Map<String, Double> boundProdSnQtyMapByOrderNo = new HashMap<>();
        // 工单已绑定数量
        //Map<String, List<MesProduceSnExt>> produceSnMapByOrderNo = new HashMap<>();
        Map<String, Integer> existProduceSnMap = new HashMap<>();
        List<MesProduceSnExt> produceSnDbList = new ArrayList<>();
        List<MesUnbindProduceSn> unBindProduceSnDbList = new ArrayList<>();
        List<MesUnbindProduceSnTravel> unBindProduceSnTravelList = new ArrayList<>();

        // 根据物料编码分组解绑条码集合
        Map<String, Map<String, MesUnbindProduceSn>> unbindProduceSnMapByPartNo = new HashMap<>();

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

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

        String nowTime = TimeTool.getNowTime(MesExtConstWords.DATE_FORMAT_YM);

        //从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 workOrderNo = row.getCell(0, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).getStringCellValue().trim();
                //本体条码
                String produceSn = null;
                try {
                    row.getCell(1).setCellType(CellType.STRING);
                    produceSn = row.getCell(1, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).getStringCellValue().trim();
                } catch (Exception e) {
                }

                MesWorkOrderExt workOrderExt = null;

                //校验工单是否为空
                if (StringUtils.isEmpty(workOrderNo)) {
                    errorNum ++;
                    cellNum += "A;";
                    errorInfo += "第A列数据必填;";
                } else {
                    //校验工单是否存在，如果不存在则从数据库获取，如果存在则从集合中获取
                    if (!workOrderMapByOrderNo.containsKey(workOrderNo)) {

                        workOrderExt = workOrderExtRepository.getByProperty(
                                new String[]{MesExtConstWords.ORGANIZE_CODE, MesExtConstWords.IS_DELETED, MesExtConstWords.IS_VALID, MesExtConstWords.ORDER_NO},
                                new Object[]{organizeCode, CommonEnumUtil.TRUE_OR_FALSE.FALSE.getValue(), CommonEnumUtil.TRUE_OR_FALSE.TRUE.getValue(), workOrderNo});

                        if (workOrderExt == null) {
                            errorNum ++;
                            cellNum += "A;";
                            errorInfo += "第A列数据不存在;";
                        } else if (workOrderExt.getWorkOrderStatus() != MesExtEnumUtil.WORK_ORDER_STATUS.CREATE.getValue()) {
                            errorNum ++;
                            cellNum += "A;";
                            errorInfo += "第A列数据当前状态为【"+ MesExtEnumUtil.WORK_ORDER_STATUS.valueOfDescription(workOrderExt.getWorkOrderStatus()) +"】，不为【创建】状态;";
                        } else if (workOrderExt.getPlanQty() != 0) {
                            errorNum ++;
                            cellNum += "A;";
                            errorInfo += "第A列数据当前存在改造数量;";
                        } else if (workOrderExt.getWorkOrderType() != MesExtEnumUtil.WORK_ORDER_TYPE.STANDARD_ORDER.getValue()) {
                            errorNum ++;
                            cellNum += "A;";
                            errorInfo += "第A列数据不为正常工单;";
                        } else {

                            List<MesProduceSnExt> produceSnExtList = produceSnExtRepository.findByProperty(
                                    new String[]{MesExtConstWords.ORGANIZE_CODE, MesExtConstWords.IS_DELETED, MesExtConstWords.IS_VALID, MesExtConstWords.WORK_ORDER_NO},
                                    new Object[]{organizeCode, CommonEnumUtil.TRUE_OR_FALSE.FALSE.getValue(), CommonEnumUtil.TRUE_OR_FALSE.TRUE.getValue(), workOrderNo});

                            Double boundSnQty = MathOperation.sub(MathOperation.sub(workOrderExt.getQty(), workOrderExt.getPlanQty()), produceSnExtList.size());

                            if (boundSnQty <= 0) {
                                errorNum ++;
                                cellNum += "A;";
                                errorInfo += "第A列工单：" + workOrderNo + "已绑定足额条码，无需再绑定;";
                            } else {
                                workOrderMapByOrderNo.put(workOrderNo, workOrderExt);
                                boundProdSnQtyMapByOrderNo.put(workOrderNo, boundSnQty);
                            }
                        }

                    } else {
                        workOrderExt = workOrderMapByOrderNo.get(workOrderNo);
                    }
                }

                MesUnbindProduceSn unbindProduceSn = null;

                MesProduceSnExt produceSnExt = null;

                if (errorNum == 0) {
                    //校验播放时长
                    if (StringUtils.isEmpty(produceSn)) {
                        errorNum ++;
                        cellNum += "B;";
                        errorInfo += "第B列数据必填;";
                    } else if (existProduceSnMap.containsKey(produceSn)) {
                        errorNum ++;
                        cellNum += "B;";
                        errorInfo += "第B列数据与 "+ existProduceSnMap.get(produceSn) +"行B列重复;";
                    } else {

                        Map<String, MesUnbindProduceSn> unbindProduceSnMapBySn;

                        if (unbindProduceSnMapByPartNo.containsKey(workOrderExt.getPartNo())) {
                            unbindProduceSnMapBySn = unbindProduceSnMapByPartNo.get(workOrderExt.getPartNo());

                            if (unbindProduceSnMapBySn.containsKey(produceSn)) {
                                unbindProduceSn = unbindProduceSnMapBySn.get(produceSn);

                                if (unbindProduceSn.getBindStatus() == MesExtEnumUtil.BIND_STATUS.BINGDING.getValue()) {
                                    errorNum ++;
                                    cellNum += "B;";
                                    errorInfo += "第B列数据已绑定工单："+ unbindProduceSn.getWorkOrderNo() +";";
                                } else {
                                    Double boundSnQty = boundProdSnQtyMapByOrderNo.get(workOrderNo);
                                    boundSnQty--;
                                    // 判断该行条码是否已超工单计划数量
                                    if (boundSnQty < 0 ) {
                                        errorNum ++;
                                        cellNum += "B;";
                                        errorInfo += "第B列数据已超工单计划数量，请重新调整导入;";
                                    } else {

                                        produceSnExt = produceSnExtRepository.getByProperty(
                                                new String[]{MesExtConstWords.ORGANIZE_CODE, MesExtConstWords.IS_DELETED, MesExtConstWords.IS_VALID, MesExtConstWords.SERIAL_NUMBER},
                                                new Object[]{organizeCode, CommonEnumUtil.TRUE_OR_FALSE.FALSE.getValue(), CommonEnumUtil.TRUE_OR_FALSE.TRUE.getValue(), produceSn});

                                        if (produceSnExt == null) {
                                            errorNum ++;
                                            cellNum += "B;";
                                            errorInfo += "第B列数据本体条码已不存在;";
                                        }

                                        boundProdSnQtyMapByOrderNo.put(workOrderNo, boundSnQty);
                                    }
                                }


                            } else {
                                errorNum ++;
                                cellNum += "B;";
                                errorInfo += "第B列数据无效;";
                            }

                        } else {
                            List<MesUnbindProduceSn> unbindProduceSnList = getMesUnbindProduceSns(organizeCode, nowTime, workOrderExt);
                            if (CollectionUtils.isEmpty(unbindProduceSnList)) {
                                errorNum ++;
                                cellNum += "B;";
                                errorInfo += "第B列数据不存在;";
                            } else {
                                unbindProduceSnMapBySn = unbindProduceSnList.stream().collect(Collectors.toMap( MesUnbindProduceSn::getSerialNumber, Function.identity(), (x, y) -> y));
                                if (unbindProduceSnMapBySn.containsKey(produceSn)) {
                                    unbindProduceSnMapByPartNo.put(workOrderExt.getPartNo(), unbindProduceSnMapBySn);
                                    unbindProduceSn = unbindProduceSnMapBySn.get(produceSn);
                                    if (unbindProduceSnMapBySn.containsKey(produceSn)) {
                                        if (unbindProduceSn.getBindStatus() == MesExtEnumUtil.BIND_STATUS.BINGDING.getValue()) {
                                            errorNum ++;
                                            cellNum += "B;";
                                            errorInfo += "第B列数据已绑定工单："+ unbindProduceSn.getWorkOrderNo() +";";
                                        } else {
                                            Double boundSnQty = boundProdSnQtyMapByOrderNo.get(workOrderNo);
                                            boundSnQty--;
                                            if (boundSnQty < 0 ) {
                                                errorNum ++;
                                                cellNum += "B;";
                                                errorInfo += "第B列数据已超工单计划数量，请重新调整导入;";
                                            } else {

                                                produceSnExt = produceSnExtRepository.getByProperty(
                                                        new String[]{MesExtConstWords.ORGANIZE_CODE, MesExtConstWords.IS_DELETED, MesExtConstWords.IS_VALID, MesExtConstWords.SERIAL_NUMBER},
                                                        new Object[]{organizeCode, CommonEnumUtil.TRUE_OR_FALSE.FALSE.getValue(), CommonEnumUtil.TRUE_OR_FALSE.TRUE.getValue(), produceSn});

                                                if (produceSnExt == null) {
                                                    errorNum ++;
                                                    cellNum += "B;";
                                                    errorInfo += "第B列数据本体条码已不存在;";
                                                }

                                                boundProdSnQtyMapByOrderNo.put(workOrderNo, boundSnQty);

                                            }
                                        }
                                    } else {
                                        errorNum ++;
                                        cellNum += "B;";
                                        errorInfo += "第B列数据不存在;";
                                    }

                                } else {
                                    errorNum ++;
                                    cellNum += "B;";
                                    errorInfo += "第B列数据不存在;";
                                }
                            }
                        }
                    }
                }

                //没有错误的时候，封装MesUnbindProduceSn
                if (errorNum == 0) {

                    // 记录已录入条码
                    existProduceSnMap.put(produceSn, rowNum);

                    produceSnExt.setWorkOrderNo(workOrderNo);
                    produceSnExt.setLastWorkOrderNo(workOrderNo);
                    produceSnExt.setSystemSyncStatus(MesExtEnumUtil.IF_SYNC_STATUS.NO_SYNC.getValue());
                    ConvertBean.serviceModelUpdate(produceSnExt, userName);
                    new ConvertBeanExt(produceSnExt).convertBean(produceSnExt);
                    produceSnDbList.add(produceSnExt);

                    unbindProduceSn.setWorkOrderNo(workOrderNo);
                    unbindProduceSn.setBindStatus(MesExtEnumUtil.BIND_STATUS.BINGDING.getValue());
                    unbindProduceSn.setWorkCenterCode(workOrderExt.getWorkCenterCode());
                    unbindProduceSn.setStartTime(workOrderExt.getStartTime());
                    unbindProduceSn.setSystemSyncStatus(MesExtEnumUtil.IF_SYNC_STATUS.NO_SYNC.getValue());
                    ConvertBean.serviceModelUpdate(unbindProduceSn, userName);
                    unBindProduceSnDbList.add(unbindProduceSn);
                    MesUnbindProduceSnTravel unbindProduceSnTravel = new MesUnbindProduceSnTravel();
                    BeanUtils.copyProperties(unbindProduceSn, unbindProduceSnTravel, MesExtConstWords.ID, MesExtConstWords.CREATE_USER, MesExtConstWords.CREATE_DATE_TIME, MesExtConstWords.MODIFY_USER, MesExtConstWords.MODIFY_DATE_TIME);
                    ConvertBean.serviceModelUpdate(unbindProduceSnTravel, userName);
                    unBindProduceSnTravelList.add(unbindProduceSnTravel);
                    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 : unBindProduceSnDbList);

        List<MesWorkOrderExt> workOrderExtList = new ArrayList<>();
        for (Map.Entry<String, Double> workBoundProdSnQty : boundProdSnQtyMapByOrderNo.entrySet()) {
            if (workBoundProdSnQty.getValue() == 0) {
                MesWorkOrderExt workOrderExt = workOrderMapByOrderNo.get(workBoundProdSnQty.getKey());
                workOrderExt.setSnCreateStatus(MesExtEnumUtil.WORK_ORDER_SN_CREATE_STATUS.CREATED.getValue());
                new ConvertBeanExt(workOrderExt).convertBean(workOrderExt);
                workOrderExtList.add(workOrderExt);
            }
        }

        if (failRowNum == 0) {
            insertExcelMesUnbindProduce(unBindProduceSnDbList, unBindProduceSnTravelList, produceSnDbList, workOrderExtList);
        }
        return excelImportResultExtModel;
    }

    private List<MesUnbindProduceSn> getMesUnbindProduceSns(String organizeCode, String nowTime, MesWorkOrderExt workOrderExt) {
        DdlPackBean packBean = DdlPackBean.getDdlPackBean(organizeCode);
        DdlPreparedPack.getStringRightLikerPack(nowTime, MesExtConstWords.START_TIME, packBean);
        DdlPreparedPack.getStringEqualPack(workOrderExt.getPartNo(), MesExtConstWords.PART_NO, packBean);
        return unBindProduceSnRepository.findByHqlWhere(packBean);
    }

    /**
     * 已解绑条码绑定工单导入-数据入库
     * @param unbindProduceSns
     * @param unbindProduceSnTravels
     */
    private void insertExcelMesUnbindProduce(List<MesUnbindProduceSn> unbindProduceSns, List<MesUnbindProduceSnTravel> unbindProduceSnTravels, List<MesProduceSnExt> produceSnExtList, List<MesWorkOrderExt> workOrderExtList) {
        //导入数据
        if (!CollectionUtils.isEmpty(unbindProduceSns)) unBindProduceSnRepository.saveAll(unbindProduceSns);
        if (!CollectionUtils.isEmpty(unbindProduceSnTravels)) unBindProduceSnTravelRepository.saveAll(unbindProduceSnTravels);
        if (!CollectionUtils.isEmpty(produceSnExtList)) produceSnExtRepository.saveAll(produceSnExtList);
        if (!CollectionUtils.isEmpty(workOrderExtList)) workOrderExtRepository.saveAll(workOrderExtList);
    }
}
