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

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.MesExteriorScratchProofCell;
import cn.estsh.i3plus.ext.mes.pojo.bean.MesModuleFileImportLot;
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.MesExteriorScratchProofCellRepository;
import cn.estsh.i3plus.ext.mes.pojo.repository.MesModuleFileImportLotRepository;
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.exception.ImppExceptionEnum;
import cn.estsh.i3plus.pojo.base.enumutil.CommonEnumUtil;
import cn.estsh.i3plus.pojo.mes.bean.MesWorkCell;
import cn.estsh.i3plus.pojo.mes.repository.MesWorkCellRepository;
import cn.estsh.i3plus.pojo.mes.repository.MesWorkCenterRepository;
import cn.estsh.impp.framework.boot.exception.ImppExceptionBuilder;
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.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
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 ExteriorScratchProofCellExcelService implements IExcelImportExtService {

    @Autowired
    private MesExteriorScratchProofCellRepository exteriorScratchProofCellRepository;

    @Autowired
    private MesWorkCenterRepository workCenterRepository;

    @Autowired
    private MesWorkCellRepository workCellRepository;

    @Autowired
    private MesModuleFileImportLotRepository moduleFileImportLotRepository;

    @Autowired
    private CommonExtService commonExtService;

    /**
     * 外观防划伤工位导入
     * @param workbook
     * @param organizeCode
     * @param userName
     * @return
     */
    @Override
    public ExcelImportResultExtModel insertDataByExcel(Workbook workbook, String organizeCode, String userName) {
        //读取表格
        ExcelImportResultExtModel excelImportResultExtModel = this.sheetExtractEsopExt(workbook.getSheetAt(0), organizeCode, userName);
        //数据入库
        this.insertExcelMesExteriorScratchProofCell(excelImportResultExtModel);
        return excelImportResultExtModel;
    }

    /**
     * 外观防划伤工位导入-读取表格
     * @param sheetAt
     * @param organizeCode
     * @param userName
     * @return
     */
    private ExcelImportResultExtModel sheetExtractEsopExt(Sheet sheetAt, String organizeCode, String userName) {
        //从0行开始读取
        int totalNumberOfRows = sheetAt.getLastRowNum() + 1;

        //批次号
        String lotNo = getCellData(getExcelRow(sheetAt, 3), 1, "B", "批次号", 3, true, "checkLotNoMethod", organizeCode);

        //MesExteriorScratchProofCell集合
        List<MesExteriorScratchProofCell> exteriorScratchProofCellList = new ArrayList<>();

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

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

        //用于EXCEL表中的生产线代码 + 工位代码 唯一校验，key：workCenterCode&workCellCode，value：row
        Map<String, String> existMap = new HashMap<>();

        //查询工位表
        List<MesWorkCell> workCells = workCellRepository.findByProperty(new String[]{MesExtConstWords.ORGANIZE_CODE, MesExtConstWords.IS_VALID, MesExtConstWords.IS_DELETED},
                new Object[]{organizeCode, CommonEnumUtil.IS_VAILD.VAILD.getValue(), CommonEnumUtil.TRUE_OR_FALSE.FALSE.getValue()});
        Map<String, List<MesWorkCell>> workCellMap = workCells.stream().filter(workCell -> null != workCell)
                .collect(Collectors.groupingBy(workCell -> (workCell.getWorkCenterCode() + "&" + workCell.getWorkCellCode())));

        // 查询生产线表
        Map<String, Object> workCenterCodeEntityMap;
        try {
            workCenterCodeEntityMap = MesCommonUtil.getCodeEntityMap(workCenterRepository, MesExtConstWords.WORK_CENTER_CODE, organizeCode, "生产线表");
        } catch (Exception e) {
            workCenterCodeEntityMap = null;
        }

        List<MesExteriorScratchProofCell> exteriorScratchProofCells = exteriorScratchProofCellRepository.findByProperty(
                new String[]{MesExtConstWords.ORGANIZE_CODE, MesExtConstWords.IS_DELETED},
                new Object[]{organizeCode, CommonEnumUtil.TRUE_OR_FALSE.FALSE.getValue()});
        Map<String, List<MesExteriorScratchProofCell>> exteriorScratchProofCellMap = CollectionUtils.isEmpty(exteriorScratchProofCells) ? null :
                exteriorScratchProofCells.stream().filter(o -> null != o).collect(Collectors.groupingBy(o -> (o.getWorkCenterCode() + "&" + o.getWorkCellCode())));

        //从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 workCenterCode = row.getCell(0, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).getStringCellValue().trim();

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

                //文件原名
                String fileOriginName = null;
                try {
                    row.getCell(6).setCellType(CellType.STRING);
                    fileOriginName = row.getCell(6, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).getStringCellValue().trim();
                } catch (Exception e) {
                }

                //校验生产线代码
                if (CollectionUtils.isEmpty(workCenterCodeEntityMap) ||
                        (!CollectionUtils.isEmpty(workCenterCodeEntityMap) && !workCenterCodeEntityMap.containsKey(workCenterCode))) {
                    errorNum ++;
                    cellNum += "A;";
                    errorInfo += "第A列数据无效;";
                }

                //校验工位代码
                if (StringUtils.isEmpty(workCellCode)) {
                    errorNum ++;
                    cellNum += "B;";
                    errorInfo += "第B列数据必填;";
                } else {
                    if (StringUtils.isEmpty(errorInfo) && (CollectionUtils.isEmpty(workCellMap) ||
                            (!CollectionUtils.isEmpty(workCellMap) && !workCellMap.containsKey(workCenterCode + "&" + workCellCode)))) {
                        errorNum ++;
                        cellNum += "B;";
                        errorInfo += "第B列数据无效;";
                    }
                }

                //校验生产线代码+工位代码不重复
                boolean isExist = false;
                if (!existMap.containsKey(workCenterCode + "&" + workCellCode)) {
                    existMap.put(workCenterCode + "&" + workCellCode, String.valueOf(rowNum));
                    //校验 生产线代码+工位代码 是否已经存在数据库
                    if (StringUtils.isEmpty(errorInfo) && !CollectionUtils.isEmpty(exteriorScratchProofCellMap) && exteriorScratchProofCellMap.containsKey(workCenterCode + "&" + workCellCode)) {
                        isExist = true;
                    }
                } else {
                    errorNum ++;
                    cellNum += "A+B;";
                    errorInfo += "第A+B列数据跟第" + existMap.get(workCenterCode + "&" + workCellCode) + "行的第A+B列数据重复;";
                }

                //校验文件原名
                if (!StringUtils.isEmpty(fileOriginName)) {
                    if (!fileOriginName.contains(".")) {
                        errorNum ++;
                        cellNum += "C;";
                        errorInfo += "第C列数据无效;";
                    } else {
                        String fileType = fileOriginName.substring(fileOriginName.lastIndexOf("."));
                        if (StringUtils.isEmpty(fileType) || (!fileType.equals(".jpg") && !fileType.equals(".jpeg") && !fileType.equals(".png"))) {
                            errorNum ++;
                            cellNum += "C;";
                            errorInfo += "第C列数据无效;";
                        }
                    }
                }

                //没有错误的时候，封装MesExteriorScratchProofCell
                if (errorNum == 0) {
                    MesExteriorScratchProofCell exteriorScratchProofCell;
                    if (isExist) {
                        exteriorScratchProofCell = (MesExteriorScratchProofCell) ((List) exteriorScratchProofCellMap.get(workCenterCode + "&" + workCellCode)).get(0);
                        ConvertBean.serviceModelUpdate(exteriorScratchProofCell, userName);
                    } else {
                        exteriorScratchProofCell = new MesExteriorScratchProofCell();
                        ConvertBean.serviceModelInitialize(exteriorScratchProofCell, userName);
                    }

                    exteriorScratchProofCell.setWorkCenterCode(workCenterCode);
                    exteriorScratchProofCell.setWorkCellCode(workCellCode);
                    exteriorScratchProofCell.setFileOriginName(fileOriginName);
                    exteriorScratchProofCell.setLotNo(lotNo);
                    exteriorScratchProofCell.setOrganizeCode(organizeCode);
                    ConvertBean.serviceModelInitialize(exteriorScratchProofCell, userName);

                    exteriorScratchProofCellList.add(exteriorScratchProofCell);

                    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 : exteriorScratchProofCellList);
        return excelImportResultExtModel;
    }

    /**
     * 外观防划伤工位导入-数据入库
     * @param excelImportResultExtModel
     */
    private void insertExcelMesExteriorScratchProofCell(ExcelImportResultExtModel excelImportResultExtModel) {
        //导入数据
        if (null != excelImportResultExtModel) {
            List<MesExteriorScratchProofCell> excelList = excelImportResultExtModel.getExcelList();
            if (!CollectionUtils.isEmpty(excelList)) {
                for (MesExteriorScratchProofCell exteriorScratchProofCell : excelList) {
                    if(StringUtils.isEmpty(exteriorScratchProofCell.getId())){
                        exteriorScratchProofCellRepository.insert(exteriorScratchProofCell);
                    }else{
                        exteriorScratchProofCellRepository.save(exteriorScratchProofCell);
                    }
                }
                MesModuleFileImportLot lot = new MesModuleFileImportLot();
                lot.setLotNo(excelList.get(0).getLotNo());
                lot.setModule(MesExtEnumUtil.BATCH_FILE_IMPORT_MODULE.EXTERIOR_SCRATCH_PROOF_CELL.getValue());
                lot.setOrganizeCode(excelList.get(0).getOrganizeCode());
                ConvertBean.serviceModelInitialize(lot, excelList.get(0).getCreateUser());
                moduleFileImportLotRepository.insert(lot);
            }
        }
    }

    /**
     * 获取EXCEL的行数据
     * @param sheetAt
     * @param i
     * @return
     */
    private Row getExcelRow(Sheet sheetAt, int i) {
        if (i == 0) {
            return null;
        }
        Row row = sheetAt.getRow(sheetAt.getFirstRowNum() + (i - 1));
        if (null == row) {
            throw ImppExceptionBuilder.newInstance()
                    .setSystemID(CommonEnumUtil.SOFT_TYPE.MES.getCode())
                    .setErrorCode(ImppExceptionEnum.VARIFY_EXCEPTION.getCode())
                    .setErrorDetail("EXCEL表中第%s行为空", i)
                    .build();
        }
        return row;
    }

    /**
     * 获取当前行每列的数据
     * @param row
     * @param cell
     * @param cellLetter
     * @param cell
     * @param fieldName
     * @param checkNullFlag
     * @return
     */
    private String getCellData(Row row, int cell, String cellLetter, String fieldName, int rowNum, boolean checkNullFlag, String methodStr, Object... obj) {
        String data = null;
        try {
            row.getCell(cell).setCellType(CellType.STRING);
            data = row.getCell(cell, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).getStringCellValue().trim();
        } catch (Exception e) {
        }

        checkDataNull(checkNullFlag, data, rowNum, cellLetter, fieldName);

        if (!StringUtils.isEmpty(methodStr)) {
            Method method;
            try {
                method = this.getClass().getDeclaredMethod(methodStr, String.class, int.class, String.class, String.class, Object[].class);
                method.setAccessible(true);
                Object result = method.invoke(this, data, rowNum, cellLetter, fieldName, obj);
                data = !StringUtils.isEmpty(result) ? result.toString() : data;
            } catch (Exception e) {
                InvocationTargetException targetEx = (InvocationTargetException) e;
                Throwable trowEx = targetEx.getTargetException();
                throw ImppExceptionBuilder.newInstance()
                        .setSystemID(CommonEnumUtil.SOFT_TYPE.MES.getCode())
                        .setErrorCode(ImppExceptionEnum.VARIFY_EXCEPTION.getCode())
                        .setErrorDetail(trowEx.getMessage())
                        .build();
            }
        }
        return data;
    }

    /**
     * 数据判空
     * @param checkNullFlag
     * @param data
     * @param rowNum
     * @param cellLetter
     * @param fieldName
     */
    private void checkDataNull(boolean checkNullFlag, String data, int rowNum, String cellLetter, String fieldName) {
        if (checkNullFlag && StringUtils.isEmpty(data)) {
            throw ImppExceptionBuilder.newInstance()
                    .setSystemID(CommonEnumUtil.SOFT_TYPE.MES.getCode())
                    .setErrorCode(ImppExceptionEnum.VARIFY_EXCEPTION.getCode())
                    .setErrorDetail("EXCEL表中第%s行%s列%s为空", rowNum, cellLetter, fieldName)
                    .build();
        }
    }

    /**
     * 批次号
     * @param data
     * @param rowNum
     * @param cellLetter
     * @param fieldName
     * @param obj -> organizeCode, lotNo, module
     * @return
     */
    private String checkLotNoMethod(String data, int rowNum, String cellLetter, String fieldName, Object... obj){
        if (!StringUtils.isEmpty(data)) {
            if (data.length() > 20) {
                throw ImppExceptionBuilder.newInstance()
                        .setSystemID(CommonEnumUtil.SOFT_TYPE.MES.getCode())
                        .setErrorCode(ImppExceptionEnum.VARIFY_EXCEPTION.getCode())
                        .setErrorDetail("EXCEL表中第%s行%s列%长度超过20个字符", rowNum, cellLetter, fieldName)
                        .build();
            }
            MesModuleFileImportLot lot = moduleFileImportLotRepository.getByProperty(
                    new String[]{MesExtConstWords.ORGANIZE_CODE, MesExtConstWords.IS_DELETED, MesExtConstWords.IS_VALID, MesExtConstWords.LOT_NO, MesExtConstWords.MODULE},
                    new Object[]{obj[0].toString(), CommonEnumUtil.TRUE_OR_FALSE.FALSE.getValue(), CommonEnumUtil.IS_VAILD.VAILD.getValue(), data, MesExtEnumUtil.BATCH_FILE_IMPORT_MODULE.EXTERIOR_SCRATCH_PROOF_CELL.getValue()});
            if (null != lot) {
                throw ImppExceptionBuilder.newInstance()
                        .setSystemID(CommonEnumUtil.SOFT_TYPE.MES.getCode())
                        .setErrorCode(ImppExceptionEnum.VARIFY_EXCEPTION.getCode())
                        .setErrorDetail("EXCEL表中第%s行%s列%s已存在数据库", rowNum, cellLetter, fieldName)
                        .build();
            }
        }
        return data;
    }
}
