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.api.busi.jx.IJxPackageExtService;
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.mes.api.iservice.base.IPackageService;
import cn.estsh.i3plus.platform.common.convert.ConvertBean;
import cn.estsh.i3plus.pojo.base.enumutil.CommonEnumUtil;
import cn.estsh.i3plus.pojo.mes.bean.MesPackageDetail;
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.*;
/**
 * @Description : 解绑条码绑定导入
 * @Reference :
 * @Author : wangjie
 * @CreateDate : 2019-04-23
 * @Modify:
 **/
@Service
public class SxReworkOrderBindSnExcelService implements IExcelImportExtService {

    @Autowired
    private MesProduceSnExtRepository produceSnExtRepository;

    @Autowired
    private MesProduceSnRepairRepository produceSnRepairRepository;

    @Autowired
    private MesWorkOrderExtRepository workOrderExtRepository;

    @Autowired
    private MesPackageExtRepository packageExtRepository;

    @Autowired
    private IJxPackageExtService packageExtService;

    @Autowired
    private MesReworkOrderBindSnTravelRepository reworkOrderBindSnTravelRepository;

    @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, String> finalWorkOrderNoBySn = new HashMap<>();
        Map<String, String> finalWorkOrderNoByPackSn = new HashMap<>();
        // 工单待绑定条码数量
        Map<String, Double> boundProdSnQtyMapByOrderNo = new HashMap<>();
        Map<String, Integer> existProduceSnMap = new HashMap<>();
        List<MesProduceSnExt> produceSnDbList = new ArrayList<>();
        List<MesPackageExt> packageExtDbList = new ArrayList<>();
        List<MesProduceSnRepair> produceSnRepairList = new ArrayList<>();
        List<MesReworkOrderBindSnTravel> reworkOrderBindSnTravelList = 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 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 {
                    //校验工单是否存在，如果不存在则从数据库获取，如果存在则从集合中获取
                    workOrderExt = getMesWorkOrderExt(organizeCode, workOrderMapByOrderNo, 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.REWORK_ORDER.getValue()) {
                        errorNum ++;
                        cellNum += "A;";
                        errorInfo += "第A列数据不为返工工单;";
                    } else {

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

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

                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 {

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

                        if (null == produceSnExt) {
                            errorNum ++;
                            cellNum += "B;";
                            errorInfo += "第B列数据条码[" + produceSn + "]不存在;";
                        } else {
                            if (MesExtEnumUtil.PRODUCE_SN_STATUS.OFFLINE.getValue() != produceSnExt.getSnStatus()) {
                                if (MesExtEnumUtil.PRODUCE_SN_STATUS.REPAIR.getValue() == produceSnExt.getSnStatus()) {
                                    errorNum ++;
                                    cellNum += "B;";
                                    errorInfo += "第B列数据条码[" + produceSn +"]状态为返工,已绑定返工工单["+ produceSnExt.getLastWorkOrderNo() + "];";
                                } else {
                                    errorNum ++;
                                    cellNum += "B;";
                                    errorInfo += "第B列数据条码[" + produceSn + "]状态为["+ MesExtEnumUtil.PRODUCE_SN_STATUS.valueOfDescription(produceSnExt.getSnStatus()) + "];";
                                }
                            }

                            if (MesExtEnumUtil.PRODUCE_SN_QC_STATUS.QUALIFIED.getValue() != produceSnExt.getQcStatus()) {
                                errorNum ++;
                                cellNum += "B;";
                                errorInfo += "第B列数据条码[" + produceSn +"]质量状态为["+ MesExtEnumUtil.PRODUCE_SN_QC_STATUS.valueOfDescription(produceSnExt.getQcStatus()) + "];";
                            }

                            if (MesExtEnumUtil.SN_OPERATE_TYPE.REPAIR.getValue() == produceSnExt.getOperateType()) {
                                errorNum ++;
                                cellNum += "B;";
                                errorInfo += "第B列数据条码[" + produceSn +"]操作类型为["+ MesExtEnumUtil.PRODUCE_SN_QC_STATUS.valueOfDescription(produceSnExt.getQcStatus()) + "];";
                            }

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

                            MesProduceSnRepair produceSnRepairBySn = getProduceSnRepairBySn(organizeCode, produceSn);

                            if (produceSnRepairBySn != null) {
                                if (produceSnRepairBySn.getWorkOrderNo().equals(workOrderNo)) {
                                    errorNum ++;
                                    cellNum += "B;";
                                    errorInfo += "条码["+ produceSnExt.getProductSn() +"]已经绑定过返工工单["+ produceSnRepairBySn.getWorkOrderNo() +"];";
                                } else if (StringUtils.isEmpty(produceSnRepairBySn.getOutWorkCenterTime())) {
                                    errorNum ++;
                                    cellNum += "B;";
                                    errorInfo += "条码["+ produceSnExt.getProductSn() +"]已经绑定过其他的返工工单["+ workOrderNo +"]且未下线;";
                                }
                            }
                        }
                    }
                }

                String packageNo = null;
                if (errorNum == 0) {
                    MesPackageDetail packageDetail = packageExtService.getPackageDetailByProductSn(organizeCode, produceSn);
                    if (null != packageDetail) {
                        MesPackageExt packageExt = packageExtService.getPackageByPackNo(organizeCode, packageDetail.getPackageNo());
                        if (null != packageExt) {
                            packageNo = packageExt.getPackageNo();
                        }
                    }
                }

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

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

                    MesProduceSnRepair produceSnRepair = new MesProduceSnRepair();
                    produceSnRepair.setSerialNumber(produceSnExt.getSerialNumber());
                    produceSnRepair.setProductSn(produceSnExt.getProductSn());
                    produceSnRepair.setPartNo(produceSnExt.getPartNo());
                    produceSnRepair.setPartNameRdd(produceSnExt.getPartNameRdd());
                    produceSnRepair.setWorkCenterCode(workOrderExt.getWorkCenterCode());
                    produceSnRepair.setWorkOrderNo(workOrderNo);
                    produceSnRepair.setInitWorkOrderNo(produceSnExt.getLastWorkOrderNo());
                    produceSnRepair.setSnType(MesExtEnumUtil.PRODUCE_SN_TYPE.REWORK_SN.getValue());
                    produceSnRepair.setOrganizeCode(organizeCode);
                    ConvertBean.serviceModelInitialize(produceSnRepair, userName);
                    produceSnRepair.setSystemSyncStatus(MesExtEnumUtil.IF_SYNC_STATUS.NO_SYNC.getValue());

                    MesReworkOrderBindSnTravel reworkOrderBindSnTravel = new MesReworkOrderBindSnTravel();
                    BeanUtils.copyProperties(produceSnRepair, reworkOrderBindSnTravel);
                    reworkOrderBindSnTravel.setBindStatus(MesExtEnumUtil.BIND_STATUS.BINGDING.getValue());
                    reworkOrderBindSnTravelList.add(reworkOrderBindSnTravel);
                    produceSnRepairList.add(produceSnRepair);

                    finalWorkOrderNoBySn.put(produceSnExt.getProductSn(), workOrderNo);
                    if (!StringUtils.isEmpty(packageNo)) finalWorkOrderNoByPackSn.put(packageNo, workOrderNo);


                    produceSnDbList.add(produceSnExt);
                    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);

        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.NO_CREATE.getValue());
                new ConvertBeanExt(workOrderExt).convertBean(workOrderExt);
                workOrderExtList.add(workOrderExt);
            }
        }

        if (failRowNum == 0) {

            for (MesProduceSnExt produceSnExt : produceSnDbList) {
                ConvertBean.serviceModelUpdate(produceSnExt, userName);
                new ConvertBeanExt(produceSnExt).convertBean(produceSnExt);
                produceSnExt.setLastWorkOrderNo(finalWorkOrderNoBySn.get(produceSnExt.getProductSn()));
                produceSnExt.setSnStatus(MesExtEnumUtil.PRODUCE_SN_STATUS.REPAIR.getValue());
                produceSnExt.setRouteCode(null);
                produceSnExt.setProcessCode(null);
                produceSnExt.setWorkCellCode(null);
                produceSnExt.setSystemSyncStatus(MesExtEnumUtil.IF_SYNC_STATUS.NO_SYNC.getValue());
            }

            if (!CollectionUtils.isEmpty(packageExtDbList)) {
                for (MesPackageExt packageExt : packageExtDbList) {
                    packageExt.setLastWorkOrderNo(finalWorkOrderNoByPackSn.get(packageExt.getPackageNo()));
                    ConvertBean.serviceModelUpdate(packageExt, userName);
                    packageExt.setSystemSyncStatus(MesExtEnumUtil.IF_SYNC_STATUS.NO_SYNC.getValue());
                    new ConvertBeanExt(packageExt).convertBean(packageExt);
                }
            }

            insertExcel(produceSnDbList, workOrderExtList, produceSnRepairList, packageExtDbList, reworkOrderBindSnTravelList);
        }
        return excelImportResultExtModel;
    }

    private Double getBoundSnQty(String organizeCode, Map<String, Double> boundProdSnQtyMapByOrderNo, String workOrderNo) {
        if (boundProdSnQtyMapByOrderNo.containsKey(workOrderNo)) {
            return boundProdSnQtyMapByOrderNo.get(workOrderNo);
        } else {
            List<MesProduceSnRepair> produceSnRepairDbList = produceSnRepairRepository.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});

            return CollectionUtils.isEmpty(produceSnRepairDbList) ? 0.0 : produceSnRepairDbList.size();
        }
    }

    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 produceSnExtList 产品条码
     * @param workOrderExtList 工单
     * @param produceSnRepairList 返修条码
     * @param reworkOrderBindSnTravelList 返工工单绑定条码履历
     */
    private void insertExcel(List<MesProduceSnExt> produceSnExtList, List<MesWorkOrderExt> workOrderExtList, List<MesProduceSnRepair> produceSnRepairList, List<MesPackageExt> packageSnExtList, List<MesReworkOrderBindSnTravel> reworkOrderBindSnTravelList) {
        //导入数据
        if (!CollectionUtils.isEmpty(produceSnExtList)) produceSnExtRepository.saveAll(produceSnExtList);
        if (!CollectionUtils.isEmpty(workOrderExtList)) workOrderExtRepository.saveAll(workOrderExtList);
        if (!CollectionUtils.isEmpty(produceSnRepairList)) produceSnRepairRepository.saveAll(produceSnRepairList);
        if (!CollectionUtils.isEmpty(packageSnExtList)) packageExtRepository.saveAll(packageSnExtList);
        if (!CollectionUtils.isEmpty(reworkOrderBindSnTravelList)) reworkOrderBindSnTravelRepository.saveAll(reworkOrderBindSnTravelList);
    }

    private MesProduceSnRepair getProduceSnRepairBySn(String organizeCode, String productSn) {
        if (StringUtils.isEmpty(organizeCode) || StringUtils.isEmpty(productSn)) {
            return null;
        }
        List<MesProduceSnRepair> produceSnRepairList = produceSnRepairRepository.findByProperty(
                new String[]{MesExtConstWords.ORGANIZE_CODE, MesExtConstWords.IS_DELETED, MesExtConstWords.IS_VALID, MesExtConstWords.PRODUCT_SN},
                new Object[]{organizeCode, CommonEnumUtil.TRUE_OR_FALSE.FALSE.getValue(), CommonEnumUtil.IS_VAILD.VAILD.getValue(), productSn},
                " order by createDatetime desc ");
        return !CollectionUtils.isEmpty(produceSnRepairList) ? produceSnRepairList.get(0) : null;
    }
}
