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.api.base.bu.IBusiConfigService;
import cn.estsh.i3plus.ext.mes.apiservice.serviceimpl.base.CommonExtService;
import cn.estsh.i3plus.ext.mes.pojo.bean.MesPartCategoryPlanRate;
import cn.estsh.i3plus.ext.mes.pojo.bean.MesWorkCenterExt;
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.MesPartCategoryPlanRateRepository;
import cn.estsh.i3plus.ext.mes.pojo.repository.MesWorkCenterExtRepository;
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 cn.estsh.i3plus.pojo.mes.bean.MesArea;
import cn.estsh.i3plus.pojo.mes.repository.MesAreaRepository;
import org.apache.poi.ss.usermodel.DataFormatter;
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 SxWorkCenterExtExcelService implements IExcelImportExtService {

    @Autowired
    private MesWorkCenterExtRepository workCenterExtRepository;

    @Autowired
    private MesAreaRepository areaRepository;

    @Autowired
    private MesPartCategoryPlanRateRepository partCategoryPlanRateRepository;

    @Autowired
    private IBusiConfigService busiConfigService;

    @Autowired
    private CommonExtService commonExtService;

    /**
     * 生产线导入
     * @param workbook
     * @param organizeCode
     * @param userName
     * @return
     */
    @Override
    public ExcelImportResultExtModel insertDataByExcel(Workbook workbook, String organizeCode, String userName) {
        //读取表格
        ExcelImportResultExtModel excelImportResultExtModel = this.sheetExtractMesWorkCenterExt(workbook.getSheetAt(0), organizeCode, userName);
        //数据入库
        this.insertExcelMesWorkCenterExt(excelImportResultExtModel);
        return excelImportResultExtModel;
    }

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

        //MesWorkCenterExt集合
        List<MesWorkCenterExt> workCenterExtList = new ArrayList<>();

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

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

        Map<String, String> existMap = new HashMap<>();

        List<MesArea> areaDbList = areaRepository.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<MesArea>> areaDbMap = CollectionUtils.isEmpty(areaDbList) ? null :
                areaDbList.stream().filter(o -> null != o).collect(Collectors.groupingBy(MesArea::getAreaCode));

        List<MesPartCategoryPlanRate> partCategoryPlanRateDbList = partCategoryPlanRateRepository.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<MesPartCategoryPlanRate>> partCategoryPlanRateDbMap = CollectionUtils.isEmpty(partCategoryPlanRateDbList) ? null :
                partCategoryPlanRateDbList.stream().filter(o -> (null != o && !StringUtils.isEmpty(o.getCategoryName()))).collect(Collectors.groupingBy(MesPartCategoryPlanRate::getCategoryName));

        Map<String, String> careerMap = busiConfigService.getConfigMap(organizeCode, MesExtConstWords.SX_CAREER_CFG);

        List<MesWorkCenterExt> workCenterExtDbList = workCenterExtRepository.findByProperty(
                new String[]{MesExtConstWords.ORGANIZE_CODE, MesExtConstWords.IS_DELETED}, new Object[]{organizeCode, CommonEnumUtil.TRUE_OR_FALSE.FALSE.getValue()});
        Map<String, List<MesWorkCenterExt>> workCenterExtDbMap = CollectionUtils.isEmpty(workCenterExtDbList) ? null :
                workCenterExtDbList.stream().filter(o -> null != o).collect(Collectors.groupingBy(MesWorkCenterExt::getWorkCenterCode));

        //从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 areaCode;
                try {
                    areaCode = new DataFormatter().formatCellValue(row.getCell(0, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK));
                } catch (Exception e) {
                    continue;
                }

                //生产线代码
                String workCenterCode = null;
                try {
                    workCenterCode = new DataFormatter().formatCellValue(row.getCell(1, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK));
                } catch (Exception e) {
                }

                //生产线名称
                String workCenterName = null;
                try {
                    workCenterName = new DataFormatter().formatCellValue(row.getCell(2, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK));
                } catch (Exception e) {
                }

                //ERP工作中心
                String erpWorkCenter = null;
                try {
                    erpWorkCenter = new DataFormatter().formatCellValue(row.getCell(3, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK));
                } catch (Exception e) {
                }

                //ERP生产版本
                String workVer = null;
                try {
                    workVer = new DataFormatter().formatCellValue(row.getCell(4, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK));
                } catch (Exception e) {
                }

                //条码生成模式
                String generateType = null;
                try {
                    generateType = new DataFormatter().formatCellValue(row.getCell(5, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK));
                } catch (Exception e) {
                }

                //工位监控模式
                String monitorType = null;
                try {
                    monitorType = new DataFormatter().formatCellValue(row.getCell(6, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK));
                } catch (Exception e) {
                }

                //产线分类
                String lineType = null;
                try {
                    lineType = new DataFormatter().formatCellValue(row.getCell(7, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK));
                } catch (Exception e) {
                }

                //非作业停机目标
                String noWorkTarget = null;
                try {
                    noWorkTarget = new DataFormatter().formatCellValue(row.getCell(8, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK));
                } catch (Exception e) {
                }

                //系别
                String isDont = null;
                try {
                    isDont = new DataFormatter().formatCellValue(row.getCell(9, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK));
                } catch (Exception e) {
                }

                //班别
                String dontClass = null;
                try {
                    dontClass = new DataFormatter().formatCellValue(row.getCell(10, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK));
                } catch (Exception e) {
                }

                //人件费
                String personnelCost = null;
                try {
                    personnelCost = new DataFormatter().formatCellValue(row.getCell(11, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK));
                } catch (Exception e) {
                }

                //产品别
                String categoryCode = null;
                try {
                    categoryCode = new DataFormatter().formatCellValue(row.getCell(12, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK));
                } catch (Exception e) {
                }

                //产线描述
                String workCenterDesc = null;
                try {
                    workCenterDesc = new DataFormatter().formatCellValue(row.getCell(13, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK));
                } catch (Exception e) {
                }

                //事业别
                String careerCode = null;
                try {
                    careerCode = new DataFormatter().formatCellValue(row.getCell(14, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK));
                } catch (Exception e) {
                }

                //验证区域代码
                if (CollectionUtils.isEmpty(areaDbMap) || !areaDbMap.containsKey(areaCode)) {
                    errorNum ++;
                    cellNum += "A;";
                    errorInfo += "第A列数据无效;";
                }

                //验证生产线代码
                if (StringUtils.isEmpty(workCenterCode)) {
                    errorNum ++;
                    cellNum += "B;";
                    errorInfo += "第B列数据必填;";
                }

                //验证生产线名称
                if (StringUtils.isEmpty(workCenterName)) {
                    errorNum ++;
                    cellNum += "C;";
                    errorInfo += "第C列数据必填;";
                }

                //验证条码生成模式
                if (StringUtils.isEmpty(generateType)) {
                    errorNum ++;
                    cellNum += "F;";
                    errorInfo += "第F列数据必填;";
                } else {
                    if (StringUtils.isEmpty(MesExtEnumUtil.WORK_CENTER_GENERATE_TYPE.descriptionOfValue(generateType))) {
                        errorNum ++;
                        cellNum += "F;";
                        errorInfo += "第F列数据无效;";
                    }
                }

                //验证工位监控模式
                if (!StringUtils.isEmpty(monitorType) && StringUtils.isEmpty(MesEnumUtil.WORK_CENTER_MONITOR_TYPE.descriptionOfValue(monitorType))) {
                    errorNum ++;
                    cellNum += "G;";
                    errorInfo += "第G列数据无效;";
                }

                //验证产线分类
                if (StringUtils.isEmpty(lineType)) {
                    errorNum ++;
                    cellNum += "H;";
                    errorInfo += "第H列数据必填;";
                } else {
                    if (StringUtils.isEmpty(MesExtEnumUtil.LINE_TYPE.descriptionOfValue(lineType))) {
                        errorNum ++;
                        cellNum += "H;";
                        errorInfo += "第H列数据无效;";
                    }
                }

                //验证人件费
                if (StringUtils.isEmpty(personnelCost)) {
                    errorNum ++;
                    cellNum += "L;";
                    errorInfo += "第L列数据必填;";
                } else {
                    try {
                        if (MathOperation.compareTo(new Double(personnelCost), new Double(0)) < 0) {
                            errorNum++;
                            cellNum += "L;";
                            errorInfo += "第L列数据无效,必须是正数,最多三位小数;";
                        }
                        String[] personnelCostArr = personnelCost.split(MesExtConstWords.DECIMAL_POINT);
                        if (null != personnelCostArr && personnelCostArr.length == 2 && personnelCostArr[1].length() > 3) {
                            errorNum++;
                            cellNum += "L;";
                            errorInfo += "第L列数据无效,必须是正数,最多三位小数;";
                        }
                    } catch (NumberFormatException e) {
                        errorNum++;
                        cellNum += "L;";
                        errorInfo += "第L列数据无效,必须是正数,最多三位小数;";
                    }
                }

                //验证产品别
                if (StringUtils.isEmpty(categoryCode)) {
                    errorNum ++;
                    cellNum += "M;";
                    errorInfo += "第M列数据必填;";
                } else {
                    if (CollectionUtils.isEmpty(partCategoryPlanRateDbMap) || !partCategoryPlanRateDbMap.containsKey(categoryCode)) {
                        errorNum ++;
                        cellNum += "M;";
                        errorInfo += "第M列数据无效;";
                    }
                }

                //验证事业别
                if (!StringUtils.isEmpty(careerCode) && (CollectionUtils.isEmpty(careerMap) || !careerMap.containsKey(careerCode))) {
                    errorNum ++;
                    cellNum += "O;";
                    errorInfo += "第O列数据无效;";
                }

                Boolean isExist = false;
                if (StringUtils.isEmpty(errorInfo)) {
                    if (!existMap.containsKey(workCenterCode)) {
                        existMap.put(workCenterCode, String.valueOf(rowNum));
                        //校验是否已经存在数据库
                        if (!CollectionUtils.isEmpty(workCenterExtDbMap) && workCenterExtDbMap.containsKey(workCenterCode)) isExist = true;
                    } else {
                        errorNum ++;
                        cellNum += "B;";
                        errorInfo += "第B列数据跟第" + existMap.get(workCenterCode) + "行的数据重复;";
                    }
                }

                //没有错误的时候，封装MesWorkCenterExt
                if (errorNum == 0) {
                    MesWorkCenterExt workCenterExt;
                    if (isExist) {
                        workCenterExt = workCenterExtDbMap.get(workCenterCode).get(0);
                        workCenterExt.setIsValid(CommonEnumUtil.IS_VAILD.VAILD.getValue());
                        ConvertBean.serviceModelUpdate(workCenterExt, userName);
                    } else {
                        workCenterExt = new MesWorkCenterExt();
                        ConvertBean.serviceModelInitialize(workCenterExt, userName);
                    }

                    workCenterExt.setAreaCode(areaCode);
                    workCenterExt.setWorkCenterCode(workCenterCode);
                    workCenterExt.setWorkCenterName(workCenterName);
                    workCenterExt.setErpWorkCenter(erpWorkCenter);
                    workCenterExt.setWorkVer(workVer);
                    workCenterExt.setGenerateType(MesExtEnumUtil.WORK_CENTER_GENERATE_TYPE.descriptionOfValue(generateType));
                    workCenterExt.setMonitorType(MesEnumUtil.WORK_CENTER_MONITOR_TYPE.descriptionOfValue(monitorType));
                    workCenterExt.setLineType(MesExtEnumUtil.LINE_TYPE.descriptionOfValueStr(lineType));
                    workCenterExt.setNoWorkTarget(noWorkTarget);
                    workCenterExt.setIsDont(isDont);
                    workCenterExt.setDontClass(dontClass);
                    workCenterExt.setPersonnelCost(personnelCost);
                    workCenterExt.setCategoryCode(partCategoryPlanRateDbMap.get(categoryCode).get(0).getCategoryCode());
                    workCenterExt.setWorkCenterDesc(workCenterDesc);
                    workCenterExt.setCareerCode(careerCode);
                    workCenterExt.setOrganizeCode(organizeCode);

                    workCenterExtList.add(workCenterExt);

                    successRowNum ++;
                } 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 : workCenterExtList);
        return excelImportResultModel;
    }

    /**
     * 生产线导入-数据入库
     * @param excelImportResultExtModel
     */
    private void insertExcelMesWorkCenterExt(ExcelImportResultExtModel excelImportResultExtModel) {
        //导入数据
        if (null != excelImportResultExtModel) {
            List<MesWorkCenterExt> excelList = excelImportResultExtModel.getExcelList();
            if (!CollectionUtils.isEmpty(excelList)) {
                for (MesWorkCenterExt workCenterExt : excelList) {
                    if (StringUtils.isEmpty(workCenterExt.getId())) {
                        workCenterExtRepository.insert(workCenterExt);
                    } else {
                        workCenterExtRepository.save(workCenterExt);
                    }
                }
            }
        }
    }
}
