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.model.ExcelImportErrorExtModel;
import cn.estsh.i3plus.ext.mes.pojo.model.ExcelImportResultExtModel;
import cn.estsh.i3plus.ext.mes.pojo.repository.MesNumberRuleExtRepository;
import cn.estsh.i3plus.ext.mes.pojo.repository.MesPartExtRepository;
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.pojo.base.enumutil.CommonEnumUtil;
import cn.estsh.i3plus.pojo.base.enumutil.MesEnumUtil;
import cn.estsh.i3plus.pojo.mes.bean.MesPartCategory;
import cn.estsh.i3plus.pojo.mes.bean.MesProductEncodeCfg;
import cn.estsh.i3plus.pojo.mes.repository.MesPartCategoryRepository;
import cn.estsh.i3plus.pojo.mes.repository.MesPartProduceTypeRepository;
import cn.estsh.i3plus.pojo.mes.repository.MesProductEncodeCfgRepository;
import cn.estsh.i3plus.pojo.mes.repository.MesWorkCenterRepository;
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\11\5 11:02
 * @Modify:
 **/
@Service
public class ProductEncodeCfgExtExcelService implements IExcelImportExtService {

    @Autowired
    private MesProductEncodeCfgRepository productEncodeCfgRepository;

    @Autowired
    private MesPartExtRepository partExtRepository;

    @Autowired
    private MesWorkCenterRepository workCenterRepository;

    @Autowired
    private MesPartProduceTypeRepository partProduceTypeRepository;

    @Autowired
    private MesPartCategoryRepository partCategoryRepository;

    @Autowired
    private MesNumberRuleExtRepository numberRuleExtRepository;

    @Autowired
    private CommonExtService commonExtService;

    /**
     * 产品编码配置导入
     * @param workbook
     * @param organizeCode
     * @param userName
     * @return
     */
    @Override
    public ExcelImportResultExtModel insertDataByExcel(Workbook workbook, String organizeCode, String userName) {
        //读取表格
        ExcelImportResultExtModel excelImportResultExtModel = this.sheetExtractMesProductEncodeCfg(workbook.getSheetAt(0), organizeCode, userName);
        //数据入库
        this.insertExcelMesProductEncodeCfg(excelImportResultExtModel);
        return excelImportResultExtModel;
    }

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

        //MesProductEncodeCfg集合
        List<MesProductEncodeCfg> productEncodeCfgList = new ArrayList<>();

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

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

        //用于EXCEL表中的 编码类型+匹配类型+匹配值 唯一校验，key：codeType&matchType&matchValue，value：row
        Map<String,String> codeTypeAndMatchTypeAndMatchValueMap = new HashMap<>();

        //查询产品编码配置表
        List<MesProductEncodeCfg> mesProductEncodeCfgList = productEncodeCfgRepository.findByProperty(
                new String[]{MesExtConstWords.ORGANIZE_CODE, MesExtConstWords.IS_DELETED},
                new Object[]{organizeCode, CommonEnumUtil.TRUE_OR_FALSE.FALSE.getValue()});
        Map<String, List<MesProductEncodeCfg>> mesProductEncodeCfgMap = mesProductEncodeCfgList.stream()
                .filter(mesProductEncodeCfg -> null != mesProductEncodeCfg).collect(Collectors.groupingBy(mesProductEncodeCfg ->
                        (mesProductEncodeCfg.getCodeType() + "&" + mesProductEncodeCfg.getMatchType() + "&" + mesProductEncodeCfg.getMatchValue())));

        // 查询物料表
        Map<String, Object> partCodeEntityMap;
        try {
            partCodeEntityMap = MesCommonUtil.getCodeEntityMap(partExtRepository, MesExtConstWords.PART_NO, organizeCode, "物料表");
        } catch (Exception e) {
            partCodeEntityMap = null;
        }

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

        //查询生产类型表
        Map<String, Object> pptCodeEntityMap;
        try {
            pptCodeEntityMap = MesCommonUtil.getCodeEntityNotOrgMap(partProduceTypeRepository, MesExtConstWords.PPT_CODE, organizeCode, "生产类型表");
        } catch (Exception e) {
            pptCodeEntityMap = null;
        }

        //查询物料分类表
        List<MesPartCategory> partCategoryList = partCategoryRepository.findByProperty(
                new String[]{MesExtConstWords.ORGANIZE_CODE, MesExtConstWords.IS_DELETED},
                new Object[]{organizeCode, CommonEnumUtil.TRUE_OR_FALSE.FALSE.getValue()});
        Map<String, List<MesPartCategory>> categoryCodeEntityMap = CollectionUtils.isEmpty(partCategoryList) ? null :
                partCategoryList.stream().filter(o -> (null != o && !StringUtils.isEmpty(o.getCategoryCode()) && !StringUtils.isEmpty(o.getCategoryType())))
                        .collect(Collectors.groupingBy(o -> (o.getCategoryCode() + "&" + o.getCategoryType())));

        //查询条码生成规则表
        Map<String, Object> ruleCodeEntityMap;
        try {
            ruleCodeEntityMap = MesCommonUtil.getCodeEntityMap(numberRuleExtRepository, MesExtConstWords.RULE_CODE, organizeCode, "条码生成规则表");
        } catch (Exception e) {
            ruleCodeEntityMap = null;
        }

        //从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 codeType = row.getCell(0, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).getStringCellValue().trim();
                //匹配类型
                String matchType = null;
                try {
                    row.getCell(1).setCellType(CellType.STRING);
                    matchType = row.getCell(1, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).getStringCellValue().trim();
                } catch (Exception e) {
                }
                //匹配值
                String matchValue = null;
                try {
                    row.getCell(2).setCellType(CellType.STRING);
                    matchValue = row.getCell(2, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).getStringCellValue().trim();
                } catch (Exception e) {
                }
                //编码规则代码
                String ruleCode = null;
                try {
                    row.getCell(3).setCellType(CellType.STRING);
                    ruleCode = row.getCell(3, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).getStringCellValue().trim();
                } catch (Exception e) {
                }

                //校验编码类型
                if (StringUtils.isEmpty(MesExtEnumUtil.ENCODE_CODE_TYPE.descriptionOfValue(codeType))) {
                    errorNum ++;
                    cellNum += "A;";
                    errorInfo += "第A列数据无效;";
                }
                //校验匹配类型
                if (StringUtils.isEmpty(matchType)) {
                    errorNum ++;
                    cellNum += "B;";
                    errorInfo += "第B列数据必填;";
                } else if (!StringUtils.isEmpty(matchType) && StringUtils.isEmpty(MesExtEnumUtil.ENCODE_MATCH_TYPE.descriptionOfValue(matchType))) {
                    errorNum ++;
                    cellNum += "B;";
                    errorInfo += "第B列数据无效;";
                }
                //校验匹配值
                if (StringUtils.isEmpty(matchValue)) {
                    errorNum ++;
                    cellNum += "C;";
                    errorInfo += "第C列数据必填;";
                } else if (!StringUtils.isEmpty(matchValue) && !StringUtils.isEmpty(matchType) && !StringUtils.isEmpty(MesExtEnumUtil.ENCODE_MATCH_TYPE.descriptionOfValue(matchType))) {
                   if ((matchType.equals(MesExtEnumUtil.ENCODE_MATCH_TYPE.ORGANIZATION.getDescription()) && !matchValue.equals(organizeCode))
                           || (matchType.equals(MesExtEnumUtil.ENCODE_MATCH_TYPE.PART.getDescription()) &&
                                (CollectionUtils.isEmpty(partCodeEntityMap) || (!CollectionUtils.isEmpty(partCodeEntityMap) && !partCodeEntityMap.containsKey(matchValue))))
                           || (matchType.equals(MesEnumUtil.MES_MATCH_TYPE.WORK_CENTER.getDescription()) &&
                                (CollectionUtils.isEmpty(workCenterCodeEntityMap) || (!CollectionUtils.isEmpty(workCenterCodeEntityMap) && !workCenterCodeEntityMap.containsKey(matchValue))))
                           || (matchType.equals(MesExtEnumUtil.ENCODE_MATCH_TYPE.PPT_CODE.getDescription()) &&
                                (CollectionUtils.isEmpty(pptCodeEntityMap) || (!CollectionUtils.isEmpty(pptCodeEntityMap) && !pptCodeEntityMap.containsKey(matchValue))))
                           || (matchType.equals(MesExtEnumUtil.ENCODE_MATCH_TYPE.CATEGORY_CODE1.getDescription()) &&
                                (CollectionUtils.isEmpty(categoryCodeEntityMap) || (!CollectionUtils.isEmpty(categoryCodeEntityMap) &&
                                        !categoryCodeEntityMap.containsKey(matchValue + "&" + MesEnumUtil.PART_CATEGORY_TYPE.CATEGORY_ONE.getValue()))))
                           || (matchType.equals(MesExtEnumUtil.ENCODE_MATCH_TYPE.CATEGORY_CODE2.getDescription()) &&
                                (CollectionUtils.isEmpty(categoryCodeEntityMap) || (!CollectionUtils.isEmpty(categoryCodeEntityMap) &&
                                        !categoryCodeEntityMap.containsKey(matchValue + "&" + MesEnumUtil.PART_CATEGORY_TYPE.CATEGORY_TWO.getValue()))))
                           || (matchType.equals(MesExtEnumUtil.ENCODE_MATCH_TYPE.CATEGORY_CODE3.getDescription()) &&
                                (CollectionUtils.isEmpty(categoryCodeEntityMap) || (!CollectionUtils.isEmpty(categoryCodeEntityMap) &&
                                        !categoryCodeEntityMap.containsKey(matchValue + "&" + MesEnumUtil.PART_CATEGORY_TYPE.CATEGORY_THREE.getValue()))))) {
                       errorNum ++;
                       cellNum += "C;";
                       errorInfo += "第C列数据无效;";
                   }
                }

                //校验 编码类型+匹配类型+匹配值 不重复
                if (!codeTypeAndMatchTypeAndMatchValueMap.containsKey(codeType + "&" + matchType + "&" + matchValue)) {
                    codeTypeAndMatchTypeAndMatchValueMap.put(codeType + "&" + matchType + "&" + matchValue, String.valueOf(rowNum));
                    //校验是否已经存在数据库
                    if (StringUtils.isEmpty(errorInfo) && !CollectionUtils.isEmpty(mesProductEncodeCfgMap) && mesProductEncodeCfgMap.containsKey(
                            MesExtEnumUtil.ENCODE_CODE_TYPE.descriptionOfValue(codeType) + "&" + MesEnumUtil.MES_MATCH_TYPE.descriptionOfValue(matchType) + "&" + matchValue)) {
                        errorNum ++;
                        cellNum += "ABC;";
                        errorInfo += "第A+B+C列数据已经存在数据库;";
                    }
                } else {
                    errorNum ++;
                    cellNum += "ABC;";
                    errorInfo += "第A+B+C列数据跟第" + codeTypeAndMatchTypeAndMatchValueMap.get(codeType + "&" + matchType + "&" + matchValue) +
                            "行的第A+B+C列数据重复;";
                }

                //校验编码规则代码
                if (StringUtils.isEmpty(ruleCode)) {
                    errorNum ++;
                    cellNum += "D;";
                    errorInfo += "第D列数据必填;";
                } else if (!StringUtils.isEmpty(ruleCode) && (CollectionUtils.isEmpty(ruleCodeEntityMap) ||
                        (!CollectionUtils.isEmpty(ruleCodeEntityMap) && !ruleCodeEntityMap.containsKey(ruleCode)))) {
                    errorNum ++;
                    cellNum += "D;";
                    errorInfo += "第D列数据无效;";
                }

                //没有错误的时候，封装MesProductEncodeCfg
                if (errorNum == 0) {
                    MesProductEncodeCfg productEncodeCfg = new MesProductEncodeCfg();
                    productEncodeCfg.setCodeType(MesExtEnumUtil.ENCODE_CODE_TYPE.descriptionOfValue(codeType));
                    productEncodeCfg.setMatchType(MesExtEnumUtil.ENCODE_MATCH_TYPE.descriptionOfValue(matchType));
                    productEncodeCfg.setMatchValue(matchValue);
                    productEncodeCfg.setRuleCode(ruleCode);
                    productEncodeCfg.setOrganizeCode(organizeCode);
                    ConvertBean.serviceModelInitialize(productEncodeCfg, userName);

                    productEncodeCfgList.add(productEncodeCfg);

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

    /**
     * 产品编码配置导入-数据入库
     * @param excelImportResultExtModel
     */
    private void insertExcelMesProductEncodeCfg(ExcelImportResultExtModel excelImportResultExtModel) {
        //导入数据
        if (null != excelImportResultExtModel) {
            List<MesProductEncodeCfg> excelList = excelImportResultExtModel.getExcelList();
            if (!CollectionUtils.isEmpty(excelList)) {
                for (MesProductEncodeCfg productEncodeCfg : excelList) {
                    productEncodeCfgRepository.insert(productEncodeCfg);
                }
            }
        }
    }
}
