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

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.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.pojo.base.enumutil.CommonEnumUtil;
import cn.estsh.i3plus.pojo.base.enumutil.MesEnumUtil;
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.stream.Collectors;

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

    @Autowired
    private MesPackageExtRepository packageExtRepository;

    @Autowired
    private MesSubassemblyRemadeRecordRepository subassemblyRemadeRecordRepository;

    @Autowired
    private MesPartExtRepository partExtRepository;

    @Autowired
    private MesWorkOrderExtRepository workOrderExtRepository;

    @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, List<String>> packSnByWorkOrderNo = new HashMap<>();
        Map<String, MesPartExt> partMap = new HashMap<>();
        // 工单待绑定条码数量
        Map<String, Double> boundProdSnQtyMapByOrderNo = new HashMap<>();
        // 工单已绑定数量
        Map<String, Integer> existPackageSnMap = new HashMap<>();
        List<MesSubassemblyRemadeRecord> subassemblyRemadeRecordList = new ArrayList<>();

        //成功数量
        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 packageNo = null;
                try {
                    row.getCell(1).setCellType(CellType.STRING);
                    packageNo = 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 {
                    //校验工单是否存在，如果不存在则从数据库获取，如果存在则从集合中获取
                    workOrderExt = getMesWorkOrderExt(organizeCode, workOrderMapByOrderNo, workOrderNo);

                    Double boundSnQty = 0.0;

                    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.REWORK_ORDER.getValue()) {
                        errorNum ++;
                        cellNum += "A;";
                        errorInfo += "第A列数据不为返工工单;";
                    } else {

                        MesPartExt partExt = null;

                        partExt = getMesPartExt(organizeCode, partMap, workOrderExt, partExt);

                        if (partExt == null) {

                            errorNum ++;
                            cellNum += "A;";
                            errorInfo += "第A列数据工单[" + workOrderNo + "]关联物料["+ workOrderExt.getPartNo() +"]不存在;";

                        } else {

                            partMap.put(workOrderExt.getPartNo(), partExt);

                            // 组件
                            if (!StringUtils.isEmpty(partExt.getCategoryCode3()) && partExt.getCategoryCode3().equals("10")) {
                                errorNum ++;
                                cellNum += "A;";
                                errorInfo += "第A列数据工单[" + workOrderNo + "]关联物料["+ workOrderExt.getPartNo() +"]不为组件;";
                            }

                        }

                        boundSnQty = getBoundSnQty(organizeCode, boundProdSnQtyMapByOrderNo, workOrderNo, packSnByWorkOrderNo);

                        if (boundSnQty.compareTo(workOrderExt.getQty()) > 0) {
                            errorNum ++;
                            cellNum += "A;";
                            errorInfo += "第A列工单[" + workOrderNo + "]当前绑定数量["+ boundSnQty +"]大于计划数量["+ workOrderExt.getQty() +"]，无需再绑定;";
                        } else {
                            workOrderMapByOrderNo.put(workOrderNo, workOrderExt);
                            boundProdSnQtyMapByOrderNo.put(workOrderNo, boundSnQty);
                        }
                    }
                }

                MesPackageExt packageExt = null;

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

                        packageExt = packageExtRepository.getByProperty(
                                new String[]{MesExtConstWords.ORGANIZE_CODE, MesExtConstWords.IS_DELETED, MesExtConstWords.IS_VALID, MesExtConstWords.PACKAGENO},
                                new Object[]{organizeCode, CommonEnumUtil.TRUE_OR_FALSE.FALSE.getValue(), CommonEnumUtil.TRUE_OR_FALSE.TRUE.getValue(), packageNo});

                        if (null == packageExt) {
                            errorNum ++;
                            cellNum += "B;";
                            errorInfo += "第B列数据条码[" + packageNo + "]不存在;";
                        } else {
                            if (MesEnumUtil.PACKAGE_IS_SEALED.UNSEALED.getValue() == packageExt.getIsSealed()) {
                                errorNum ++;
                                cellNum += "B;";
                                errorInfo += "第B列数据条码[" + packageNo +"]状态为未封箱";
                            }

                            if (!packageExt.getPartNo().equals(workOrderExt.getPartNo())) {
                                errorNum ++;
                                cellNum += "B;";
                                errorInfo += "工单[" + workOrderNo +"]关联的物料编码["+ workOrderExt.getPartNo() +"]与条码["+ packageNo +"]关联的物料编码["+ packageExt.getPartNo() +"]不一致;";
                            }

                            List<String> packSnList = packSnByWorkOrderNo.get(workOrderNo);
                            if (!CollectionUtils.isEmpty(packSnList) && packSnList.contains(packageNo)) {
                                errorNum ++;
                                cellNum += "B;";
                                errorInfo += "第B列数据条码[" + packageNo +"]已绑定至工单["+ workOrderNo +"],无需再次绑定";
                            } else {
                                if (CollectionUtils.isEmpty(packSnList)) {
                                    packSnList = new ArrayList<>();
                                }

                                Double boundQty = boundProdSnQtyMapByOrderNo.containsKey(workOrderNo) ? 0.0 : boundProdSnQtyMapByOrderNo.get(workOrderNo);

                                Double sumBoundQty = MathOperation.add(boundQty, packageExt.getQty());

                                if (sumBoundQty.compareTo(workOrderExt.getQty()) > 0) {
                                    errorNum ++;
                                    cellNum += "B;";
                                    errorInfo += "第B列数据条码[" + packageNo +"]与工单["+ workOrderNo +"]绑定数量"+ boundQty +"总和为["+ sumBoundQty +"],大于工单计划数量["+ workOrderExt.getQty() +"]无需再次绑定";
                                } else {
                                    packSnList.add(packageNo);
                                    packSnByWorkOrderNo.put(workOrderNo, packSnList);
                                }

                                boundProdSnQtyMapByOrderNo.put(workOrderNo, sumBoundQty);
                            }
                        }
                    }
                }

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

                    // 记录已录入条码
                    existPackageSnMap.put(packageNo, rowNum);

                    MesSubassemblyRemadeRecord record = new MesSubassemblyRemadeRecord();
                    record.setSystemSyncStatus(MesExtEnumUtil.IF_SYNC_STATUS.NO_SYNC.getValue());
                    record.setPackageNo(packageExt.getPackageNo());
                    record.setLotNo(packageExt.getLotNo());
                    record.setQty(0.0);
                    record.setInitWorkOrderNo(packageExt.getLastWorkOrderNo());
                    record.setInitWorkCenterCode(packageExt.getWorkCenterCode());
                    record.setInitQty(packageExt.getQty());
                    record.setWorkOrderNo(workOrderNo);
                    record.setWorkCenterCode(workOrderExt.getWorkCenterCode());
                    record.setPartNo(workOrderExt.getPartNo());
                    record.setPartNameRdd(workOrderExt.getPartNameRdd());
                    record.setOrganizeCode(organizeCode);
                    ConvertBean.serviceModelInitialize(record, userName);

                    subassemblyRemadeRecordList.add(record);
                    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);

        if (failRowNum == 0) {
            insertExcel(subassemblyRemadeRecordList);
        }
        return excelImportResultExtModel;
    }

    private double getBoundSnQty(String organizeCode, Map<String, Double> boundProdSnQtyMapByOrderNo, String workOrderNo, Map<String, List<String>> packSnByWorkOrderNo) {
        if (!boundProdSnQtyMapByOrderNo.containsKey(workOrderNo)) {
            List<MesSubassemblyRemadeRecord> subassemblyRemadeRecordList = subassemblyRemadeRecordRepository.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});

            if (!CollectionUtils.isEmpty(subassemblyRemadeRecordList)) {
                List<String> packSnList = subassemblyRemadeRecordList.stream().map(MesSubassemblyRemadeRecord::getPackageNo).collect(Collectors.toList());
                packSnByWorkOrderNo.put(workOrderNo, packSnList);
            }

            return CollectionUtils.isEmpty(subassemblyRemadeRecordList) ? 0 : subassemblyRemadeRecordList.stream().mapToDouble(MesSubassemblyRemadeRecord::getInitQty).sum();

        } else return boundProdSnQtyMapByOrderNo.get(workOrderNo);
    }

    private MesPartExt getMesPartExt(String organizeCode, Map<String, MesPartExt> partMap, MesWorkOrderExt workOrderExt, MesPartExt partExt) {
        if (workOrderExt != null && !partMap.containsKey(workOrderExt.getPartNo())) {
            partExt = partExtRepository.getByProperty(
                    new String[]{MesExtConstWords.ORGANIZE_CODE, MesExtConstWords.IS_DELETED, MesExtConstWords.IS_VALID, MesExtConstWords.PART_NO},
                    new Object[]{organizeCode, CommonEnumUtil.TRUE_OR_FALSE.FALSE.getValue(), CommonEnumUtil.TRUE_OR_FALSE.TRUE.getValue(), workOrderExt.getPartNo()});
        } else if(workOrderExt != null) {
            partExt = partMap.get(workOrderExt.getPartNo());
        }
        return partExt;
    }

    private MesWorkOrderExt getMesWorkOrderExt(String organizeCode, Map<String, MesWorkOrderExt> workOrderMapByOrderNo, String workOrderNo) {
        MesWorkOrderExt workOrderExt;
        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});

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

    /**
     * 已解绑条码绑定工单导入-数据入库
     * @param subassemblyRemadeRecordList 返工条码

     */
    private void insertExcel(List<MesSubassemblyRemadeRecord> subassemblyRemadeRecordList) {
        //导入数据
        if (!CollectionUtils.isEmpty(subassemblyRemadeRecordList)) subassemblyRemadeRecordRepository.saveAll(subassemblyRemadeRecordList);
    }
}
