package cn.estsh.i3plus.ext.mes.pcn.apiservice.serviceimpl.busi.step.ep;

import cn.estsh.i3plus.ext.mes.pcn.api.base.ISxWorkOrderExtService;
import cn.estsh.i3plus.ext.mes.pcn.api.busi.IProduceSnExtService;
import cn.estsh.i3plus.ext.mes.pcn.pojo.bean.MesProduceSnExt;
import cn.estsh.i3plus.ext.mes.pcn.pojo.util.MesPcnExtConstWords;
import cn.estsh.i3plus.ext.mes.pcn.pojo.util.MesPcnExtEnumUtil;
import cn.estsh.i3plus.mes.pcn.api.iservice.base.IBaseDataService;
import cn.estsh.i3plus.mes.pcn.api.iservice.busi.IStepCommonMethodService;
import cn.estsh.i3plus.mes.pcn.dao.IDataSourceDao;
import cn.estsh.i3plus.mes.pcn.serviceimpl.base.BaseStepService;
import cn.estsh.i3plus.mes.pcn.serviceimpl.busi.dispatch.processcontrol.PcsCtlConst;
import cn.estsh.i3plus.mes.pcn.util.BsJdbcTemplate;
import cn.estsh.i3plus.mes.pcn.util.BsJdbcTemplateConfig;
import cn.estsh.i3plus.mes.pcn.util.LocaleUtil;
import cn.estsh.i3plus.mes.pcn.websocket.StationWebSocket;
import cn.estsh.i3plus.platform.common.convert.ConvertBean;
import cn.estsh.i3plus.platform.common.util.MesPcnConstWords;
import cn.estsh.i3plus.pojo.base.bean.DdlPackBean;
import cn.estsh.i3plus.pojo.base.enumutil.CommonEnumUtil;
import cn.estsh.i3plus.pojo.base.enumutil.MesPcnEnumUtil;
import cn.estsh.i3plus.pojo.base.tool.DdlPreparedPack;
import cn.estsh.i3plus.pojo.mes.bean.*;
import cn.estsh.i3plus.pojo.mes.model.ProductDataModel;
import cn.estsh.i3plus.pojo.mes.model.StationRequestBean;
import cn.estsh.i3plus.pojo.mes.model.StepResult;
import cn.estsh.i3plus.pojo.mes.repository.MesDataObjectRepository;
import cn.estsh.i3plus.pojo.mes.repository.MesObjectCfgRepository;
import cn.estsh.i3plus.pojo.mes.repository.MesProductDataRepository;
import cn.estsh.impp.framework.boot.util.ImppRedis;
import com.alibaba.fastjson.JSON;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;

import javax.annotation.Resource;
import java.util.*;
import java.util.concurrent.TimeUnit;
import java.util.stream.Collectors;

/**
 * @Author: wangjie
 * @CreateDate: 2020/8/31 16:00
 * @Description: 数据采集:采集PLC生产数据
 **/
@Slf4j
@Service
public class EpCollectDbProductDataStepService extends BaseStepService {

    @Resource(name = MesPcnConstWords.REDIS_RES_PCN)
    private ImppRedis redisMesPcn;

    @Autowired
    private MesObjectCfgRepository objectCfgRepository;

    @Autowired
    private MesProductDataRepository productDataRepository;

    @Autowired
    private BsJdbcTemplateConfig jdbcTemplateConfig;

    @Autowired
    private IStepCommonMethodService stepCommonMethodService;

    @Autowired
    private IDataSourceDao dataSourceDao;

    @Autowired
    private IBaseDataService baseDataService;

    @Autowired
    private MesDataObjectRepository dataObjectRepository;

    @Autowired
    private IProduceSnExtService produceSnExtService;

    @Autowired
    private ISxWorkOrderExtService workOrderExtService;

    //第三方数据库表名
    private static final String OTHER_TABLE_NAME = "OTHER_TABLE_NAME";
    //对象代码-表名称
    private static final String OBJECT_CODE = "OBJECT_CODE";
    //产品条码
    private static final String SERIAL_NUMBER = "SERIAL_NUMBER";
    //排序字段
    private static final String ORDER_FIELD = "ORDER_FIELD";
    //主键字段
    private static final String FIELD_PK = "FIELD_PK";
    //反写字段
    private static final String FEED_FIELD = "FEED_FIELD";
    //反写值
    private static final String FEED_VALUE = "FEED_VALUE";
    //MySQL8.x时区设置
    private static final String TIME_ZONE = "TIME_ZONE";
    //是否选择自增列
    private static final String IS_SELF_ADDITION = "IS_SELF_ADDITION";
    //睡眠时间
    private static final String THREAD_SLEEP_TIME = "THREAD_SLEEP_TIME";
    //查询数量
    private static final String LIMIT_ROWS = "LIMIT_ROWS";
    private static final String LIMIT_ROWS_DEFAULT = "10";

    //自增列
    private static final String SELF_ADDITION = "SELF_ADDITION";
    //读取标志值
    private static final String READ_FLAG_VALUE = "READ_FLAG_VALUE";
    private static final String SELECT = " SELECT ";
    private static final String SELECT_TOP = " SELECT TOP";
    private static final String SELECT_ROWNUM = " SELECT rownum, ";
    private static final String FROM = "FROM";
    private static final String WHERE = " WHERE ";
    private static final String WHERE_ROWNUM_GREATER_THAN = " WHERE rownum> ";
    private static final String AND = " AND ";
    private static final String IS_NOT_NULL = " is not null";
    private static final String NOT_EQ_EMPTY = " !='' ";
    private static final String ORDER_BY = " ORDER BY ";
    private static final String LIMIT = "LIMIT";
    private static final String UPDATE = " UPDATE ";
    private static final String SET = " SET ";
    private static final String IN_LEFT = " in ( ";
    private static final String IN_RIGHT = " ) ";

    //生产线及工位的名称缓存
    private static final String CENTER_AND_CELL_NAME = "CENTER_AND_CELL_NAME:";
    //生产线及工位的名称缓存
    private static final String REPAIR_ORDER_CENTER = "REPAIR_ORDER_CENTER:";

    @Override
    public StepResult execute(StationRequestBean reqBean) {
        log.info("数据采集生产线[{}]工位[{}]:ws请求参数 [{}]", reqBean.getWorkCenterCode(), reqBean.getWorkCellCode(), reqBean);

        Map<String, MesProdRouteOptParam> codeValueMap = stepCommonMethodService.getStepCfgInfo(reqBean);
        log.info("数据采集生产线[{}]工位[{}]:工步参数 {}", reqBean.getWorkCenterCode(), reqBean.getWorkCellCode(), codeValueMap);

        checkStepParams(codeValueMap, reqBean);

        String objectCode = codeValueMap.get(OBJECT_CODE).getParamValue();
        String[] snArr = codeValueMap.get(SERIAL_NUMBER).getParamValue().split(MesPcnExtConstWords.COMMA);

        MesDatasource db = dataSourceDao.getDataSourceByObjectCode(objectCode, reqBean.getOrganizeCode());
        log.info("数据采集生产线[{}]工位[{}]:db:{}", reqBean.getWorkCenterCode(), reqBean.getWorkCellCode(), db);
        if (db == null) {
            String err = String.format(LocaleUtil.transferLanguage(reqBean.getUserInfo(),
                    "数据采集生产线[%s]工位[%s]:未根据对象代码找到相应数据源,请确认对象代码是否配置正确"), reqBean.getWorkCenterCode(), reqBean.getWorkCellCode());
            sendMessage(reqBean, null, err, MesPcnEnumUtil.STATION_BUSI_TYPE.MESSAGE, MesPcnEnumUtil.STATION_DATA_TYPE.EXP_TEXT);
            return StepResult.getNonComplete(err);
        }

        MesDataObject dataObject = findDataObjByDsObjCode(reqBean, objectCode, db);
        if (dataObject == null) {
            log.info("数据采集生产线[{}]工位[{}]:未查询到[MesDataObject]信息", reqBean.getWorkCenterCode(), reqBean.getWorkCellCode());
            String err = String.format(LocaleUtil.transferLanguage(reqBean.getUserInfo(),
                    "数据采集生产线[%s]工位[%s]:未找到数据对象"), reqBean.getWorkCenterCode(), reqBean.getWorkCellCode());
            sendMessage(reqBean, null, err, MesPcnEnumUtil.STATION_BUSI_TYPE.MESSAGE, MesPcnEnumUtil.STATION_DATA_TYPE.EXP_TEXT);
            return StepResult.getNonComplete(err);
        }

        List<MesObjectCfg> objectCfgList = findObjectCfgsByObjCode(reqBean, objectCode);
        if (CollectionUtils.isEmpty(objectCfgList)) {
            log.info("数据采集生产线[{}]工位[{}]:未查询到[MesObjectCfg]信息", reqBean.getWorkCenterCode(), reqBean.getWorkCellCode());
            String err = String.format(LocaleUtil.transferLanguage(reqBean.getUserInfo(),
                    "数据采集生产线[%s]工位[%s]:数据库配置参数不存在"), reqBean.getWorkCenterCode(), reqBean.getWorkCellCode());
            sendMessage(reqBean, null, err, MesPcnEnumUtil.STATION_BUSI_TYPE.MESSAGE, MesPcnEnumUtil.STATION_DATA_TYPE.EXP_TEXT);
            return StepResult.getNonComplete(err);
        }

        boolean isSelfAddition = false;
        if (Integer.parseInt(codeValueMap.get(IS_SELF_ADDITION).getParamValue()) == CommonEnumUtil.TRUE_OR_FALSE.TRUE.getValue()) {
            isSelfAddition = true;
        }
        //获取关键字段map
        Map<String, String> fieldPkMap = getPkFieldMap(reqBean, dataObject, isSelfAddition);

        String timeZone = null;
        if (codeValueMap.get(TIME_ZONE) != null && !StringUtils.isEmpty(codeValueMap.get(TIME_ZONE).getParamValue())) {
            timeZone = codeValueMap.get(TIME_ZONE).getParamValue();
        }

        BsJdbcTemplate bj = jdbcTemplateConfig.getJdbcTemplate(db, timeZone);
        if (bj == null) {
            log.info("数据采集生产线[{}]工位[{}]:获取数据源[BsJdbcTemplate]失败", reqBean.getWorkCenterCode(), reqBean.getWorkCellCode());
            String err = String.format(LocaleUtil.transferLanguage(reqBean.getUserInfo(),
                    "数据采集生产线[%s]工位[%s]:获取数据源失败"), reqBean.getWorkCenterCode(), reqBean.getWorkCellCode());
            sendMessage(reqBean, null, err, MesPcnEnumUtil.STATION_BUSI_TYPE.MESSAGE, MesPcnEnumUtil.STATION_DATA_TYPE.EXP_TEXT);
            return StepResult.getNonComplete(err);
        }

        log.info("数据采集生产线[{}]工位[{}]:创建数据库连接中", reqBean.getWorkCenterCode(), reqBean.getWorkCellCode());
        boolean isConnected = jdbcTemplateConfig.checkDbConn(db);
        if (!isConnected) {
            log.info("数据采集生产线[{}]工位[{}]:创建数据库连接失败", reqBean.getWorkCenterCode(), reqBean.getWorkCellCode());
            String err = String.format(LocaleUtil.transferLanguage(reqBean.getUserInfo(),
                    "数据采集生产线[%s]工位[%s]:连接数据库失败,请检查连接数据库配置"), reqBean.getWorkCenterCode(), reqBean.getWorkCellCode());
            sendMessage(reqBean, null, err, MesPcnEnumUtil.STATION_BUSI_TYPE.MESSAGE, MesPcnEnumUtil.STATION_DATA_TYPE.EXP_TEXT);
            return StepResult.getNonComplete(err);
        }

        log.info("数据采集生产线[{}]工位[{}]:创建数据库连接成功,即将执行数据采集", reqBean.getWorkCenterCode(), reqBean.getWorkCellCode());
        List<Map<String, Object>> mapList = pollingDb(reqBean, bj, codeValueMap, objectCfgList, db, dataObject, snArr);

        if (!getCenterRunningStatus(reqBean)) {
            log.info("数据采集生产线[{}]工位[{}]:工位停止监控", reqBean.getWorkCenterCode(), reqBean.getWorkCellCode());
            String err = String.format(LocaleUtil.transferLanguage(reqBean.getUserInfo(),
                    "数据采集生产线[%s]工位[%s]:工位停止监控"), reqBean.getWorkCenterCode(), reqBean.getWorkCellCode());
            StationWebSocket.sendMessage(reqBean, err, MesPcnEnumUtil.STATION_BUSI_TYPE.MESSAGE, MesPcnEnumUtil.STATION_DATA_TYPE.EXP_TEXT);
            return StepResult.getNonComplete(err);
        }

        if (CollectionUtils.isEmpty(mapList)) {
            log.info("数据采集生产线[{}]工位[{}]:执行数据采集完成,数据量[0],execute结束", reqBean.getWorkCenterCode(), reqBean.getWorkCellCode());
            return StepResult.getSuccessComplete();
        }

        MesProductData productData = getBasicProductData(reqBean, db, dataObject);
        List<MesProductData> resultList = new ArrayList<>();
        for (Map<String, Object> map : mapList) {
            MesProductData pd = new MesProductData();
            BeanUtils.copyProperties(productData, pd);
            packDbResult(map, objectCfgList, assembleMidDbSn(map, snArr), pd, dataObject.getSelfAddition());
            resultList.add(pd);
        }

        saveDbResult(reqBean, resultList, codeValueMap.get(MesPcnExtConstWords.IS_RFID_CELL).getParamValue().equals(CommonEnumUtil.TRUE_OR_FALSE.TRUE.getValueStr()));

        if (isSelfAddition) {
            updateMidDbBySelfAddition(reqBean, mapList, dataObject);
        } else {
            updateMidDbByReadTag(fieldPkMap, bj, objectCode, mapList, reqBean);
        }

        log.info("数据采集生产线[{}]工位[{}]:执行数据采集完成,数据量[{}],execute结束", reqBean.getWorkCenterCode(), reqBean.getWorkCellCode(), mapList.size());
        return StepResult.getSuccessComplete();
    }

    private Map<String, String> getPkFieldMap(StationRequestBean reqBean, MesDataObject dataObject, boolean isSelfAddition) {
        Map<String, String> map = null;
        if (!isSelfAddition) {
            String errMsg = "";
            map = new HashMap<>();
            if (StringUtils.isEmpty(dataObject.getFieldPk())) {
                errMsg = LocaleUtil.transferLanguage(reqBean.getUserInfo(), "在反写模式下，需要配置主键");
            } else if (StringUtils.isEmpty(dataObject.getFeedField())) {
                errMsg = LocaleUtil.transferLanguage(reqBean.getUserInfo(), "在反写模式下，需要配置反写列名");
            } else if (StringUtils.isEmpty(dataObject.getFeedValue())) {
                errMsg = LocaleUtil.transferLanguage(reqBean.getUserInfo(), "在反写模式下，需要配置反写值");
            }
            if (!StringUtils.isEmpty(errMsg)) {
                log.info("数据采集生产线[{}]工位[{}]:{}", reqBean.getWorkCenterCode(), reqBean.getWorkCellCode(), errMsg);
                String err = String.format(LocaleUtil.transferLanguage(reqBean.getUserInfo(),
                        "数据采集生产线[%s]工位[%s]:%s"), reqBean.getWorkCenterCode(), reqBean.getWorkCellCode(), errMsg);
                sendMessage(reqBean, null, err, MesPcnEnumUtil.STATION_BUSI_TYPE.MESSAGE, MesPcnEnumUtil.STATION_DATA_TYPE.EXP_TEXT);
                foundExThrow(reqBean, errMsg);
            }
            map.put(FIELD_PK, dataObject.getFieldPk());
            map.put(FEED_FIELD, dataObject.getFeedField());
            map.put(FEED_VALUE, dataObject.getFeedValue());
        }
        return map;
    }

    private void updateMidDbBySelfAddition(StationRequestBean reqBean, List<Map<String, Object>> snMapList, MesDataObject dataObject) {
        //更新最大自增
        OptionalLong sa = snMapList.stream().mapToLong(o -> Long.parseLong(String.valueOf(o.get(dataObject.getSelfAddition())))).max();
        if (sa.isPresent()) {
            dataObject.setSelfAdditionValue(sa.getAsLong());
            ConvertBean.serviceModelUpdate(dataObject, reqBean.getUserInfo());
            dataObjectRepository.save(dataObject);
        }
    }

    private void updateMidDbByReadTag(Map<String, String> fieldPkMap, BsJdbcTemplate bj, String
            objectCode, List<Map<String, Object>> mapList, StationRequestBean reqBean) {
        StringBuilder sb = new StringBuilder(UPDATE)
                .append(objectCode)
                .append(SET)
                .append(fieldPkMap.get(FEED_FIELD))
                .append(MesPcnExtConstWords.ONE_SPACE).append(MesPcnExtConstWords.EQ).append(MesPcnExtConstWords.ONE_SPACE)
                .append(fieldPkMap.get(FEED_VALUE))
                .append(WHERE)
                .append(fieldPkMap.get(FIELD_PK)).append(IN_LEFT);
        StringJoiner stringJoiner = new StringJoiner(MesPcnExtConstWords.COMMA);
        for (Map<String, Object> map : mapList) {
            stringJoiner.add(map.get(fieldPkMap.get(FIELD_PK)).toString());
        }
        sb.append(stringJoiner).append(IN_RIGHT);

        try {
            bj.getJdbcTemplate().update(sb.toString(), new HashMap<>());
        } catch (Exception e) {
            log.info("数据采集生产线[{}]工位[{}]:修改中间库失败,sql:{} , 错误:{}", reqBean.getWorkCenterCode(), reqBean.getWorkCellCode(), sb.toString(), e.getMessage());
            String err = String.format(LocaleUtil.transferLanguage(reqBean.getUserInfo(),
                    "数据采集生产线[%s]工位[%s]:修改中间库失败,sql:%s , 错误:%s"), reqBean.getWorkCenterCode(), reqBean.getWorkCellCode(), sb.toString(), e.getMessage());
            sendMessage(reqBean, null, err, MesPcnEnumUtil.STATION_BUSI_TYPE.MESSAGE, MesPcnEnumUtil.STATION_DATA_TYPE.EXP_TEXT);
            foundExThrow(reqBean, err);
        }
    }


    private void saveDbResult(StationRequestBean reqBean, List<MesProductData> resultList, Boolean isRfidCellFlag) {
        Map<String, MesProduceSnExt> snMap = null;
        if (!isRfidCellFlag) {
            List<String> serialNumberList = (resultList.stream().filter(o -> (null != o && !StringUtils.isEmpty(o.getSerialNumber())))
                    .map(MesProductData::getSerialNumber).collect(Collectors.toList())).stream().filter(o -> !StringUtils.isEmpty(o)).distinct().collect(Collectors.toList());
            if (!CollectionUtils.isEmpty(serialNumberList)) {
                for (String serialNumber : serialNumberList) {
                    if (StringUtils.isEmpty(serialNumber)) {
                        continue;
                    }
                    MesProduceSnExt produceSnExt = produceSnExtService.getProduceSnExtBySn(reqBean.getOrganizeCode(), serialNumber);
                    if (null != produceSnExt) {
                        if (CollectionUtils.isEmpty(snMap)) {
                            snMap = new HashMap<>();
                        }
                        snMap.put(serialNumber, produceSnExt);

                    }
                }
            }
        }
        for (MesProductData productData : resultList) {
            try {
                if (isRfidCellFlag) {
                    //RFID工位清除条码
                    productData.setSerialNumber(null);
                    productData.setProductSn(null);
                    //暂时不同步到MES,Pcn生产数据RFID匹配定时任务执行后再同步,避免MES库生产数据表索引被消耗
                    productData.setSystemSyncStatus(MesPcnExtEnumUtil.IF_SYNC_STATUS.ALREADY_SYNC.getValue());
                } else {
                    //非RFID工位写入工单及物料信息
                    MesProduceSnExt produceSnExt = (!CollectionUtils.isEmpty(snMap) && snMap.containsKey(productData.getSerialNumber())) ? snMap.get(productData.getSerialNumber()) : null;
                    if (null != produceSnExt) {
                        if (!produceSnExt.getWorkOrderNo().equals(produceSnExt.getLastWorkOrderNo())) {
                            //标记返工工单匹配值
                            productData.setGroupNo(new StringJoiner(MesPcnExtConstWords.AND).add(productData.getGroupNo()).add(produceSnExt.getLastWorkOrderNo()).toString());
                            productData.setWorkCenterCode(doGetRepairOrderCenter(reqBean.getOrganizeCode(), produceSnExt.getLastWorkOrderNo()));
                        } else {
                            productData.setWorkCenterCode(produceSnExt.getWorkCenterCode());
                        }
                        productData.setWorkCenterName(doGetCenterOrCellName(reqBean.getOrganizeCode(), productData.getWorkCenterCode(), null));
                        productData.setWorkCellName(doGetCenterOrCellName(reqBean.getOrganizeCode(), productData.getWorkCenterCode(), productData.getWorkCellCode()));
                        productData.setOrderNo(produceSnExt.getLastWorkOrderNo());
                        productData.setPartNo(produceSnExt.getPartNo());
                        productData.setPartDesc(produceSnExt.getPartNameRdd());
                        //非RFID工位的采集数据可直接同步到MES
                        productData.setSystemSyncStatus(MesPcnExtEnumUtil.IF_SYNC_STATUS.NO_SYNC.getValue());
                    } else {
                        //暂时不同步到MES
                        productData.setSystemSyncStatus(MesPcnExtEnumUtil.IF_SYNC_STATUS.ALREADY_SYNC.getValue());
                    }
                }
                productDataRepository.insert(productData);
            } catch (Exception e) {
                log.info("数据采集生产线[{}]工位[{}]:采集数据[{}]入库异常:{}", reqBean.getWorkCenterCode(), reqBean.getWorkCellCode(), productData, e.getMessage());
                String err = String.format(LocaleUtil.transferLanguage(reqBean.getUserInfo(),
                        "数据采集生产线[%s]工位[%s]:采集数据[%s]入库异常:%s"), reqBean.getWorkCenterCode(), reqBean.getWorkCellCode(), productData, e.getMessage());
                sendMessage(reqBean, null, err, MesPcnEnumUtil.STATION_BUSI_TYPE.MESSAGE, MesPcnEnumUtil.STATION_DATA_TYPE.EXP_TEXT);
                foundExThrow(reqBean, err);
            }
        }
    }

    private String doGetRepairOrderCenter(String organizeCode, String workOrderNo) {
        String key = MesPcnExtConstWords.PREFIX_PCS_CTL + REPAIR_ORDER_CENTER + organizeCode;
        String workCenterCode = (String) redisMesPcn.getHash(key, workOrderNo);
        if (StringUtils.isEmpty(workCenterCode)) {
            MesWorkOrder workOrder = workOrderExtService.getWorkOrderByOrderNo(organizeCode, workOrderNo);
            if (null != workOrder) {
                workCenterCode = workOrder.getWorkCenterCode();
                redisMesPcn.putHash(key, workOrderNo, workCenterCode, MesPcnEnumUtil.EXPIRE_TIME.ONE_HOUR.getValue());
            }
        }
        return workCenterCode;
    }

    public String doGetCenterOrCellName(String organizeCode, String workCenterCode, String workCellCode) {
        if (StringUtils.isEmpty(workCenterCode)) {
            return null;
        }
        String key = MesPcnExtConstWords.PREFIX_PCS_CTL + CENTER_AND_CELL_NAME + organizeCode;
        String item = StringUtils.isEmpty(workCellCode) ? workCenterCode : new StringJoiner(MesPcnExtConstWords.AND).add(workCenterCode).add(workCellCode).toString();
        String name = (String) redisMesPcn.getHash(key, item);
        if (StringUtils.isEmpty(name)) {
            if (StringUtils.isEmpty(workCellCode)) {
                MesWorkCenter workCenter = baseDataService.getMesPcnWorkCenterByCode(workCenterCode, organizeCode);
                if (null != workCenter && !StringUtils.isEmpty(workCenter.getWorkCenterName())) {
                    name = workCenter.getWorkCenterName();
                    redisMesPcn.putHash(key, item, name, MesPcnEnumUtil.EXPIRE_TIME.NEVER.getValue());
                }
            } else {
                MesWorkCell workCell = baseDataService.getWorkCellByCode(workCenterCode, workCellCode, organizeCode);
                if (null != workCell && !StringUtils.isEmpty(workCell.getWorkCellName())) {
                    name = workCell.getWorkCellName();
                    redisMesPcn.putHash(key, item, name, MesPcnEnumUtil.EXPIRE_TIME.NEVER.getValue());
                }
            }
        }
        return name;
    }

    private void packDbResult(Map<String, Object> map, List<MesObjectCfg> objectCfgList, String serialNumber, MesProductData productData, String selfAddition) {
        productData.setGroupNo(serialNumber);
        productData.setSerialNumber(serialNumber);
        productData.setProductSn(serialNumber);
        productData.setId(null);
        productData.setRowNo(String.valueOf(map.get(selfAddition)));

        List<ProductDataModel> productDataModels = new ArrayList<>();
        for (Map.Entry<String, Object> entry : map.entrySet()) {
            Optional<MesObjectCfg> optional = objectCfgList.stream().filter(o -> o.getFieldCode().equals(entry.getKey()) &&
                    o.getIsSave() != null && o.getIsSave() == CommonEnumUtil.TRUE_OR_FALSE.TRUE.getValue()).findFirst();
            if (optional.isPresent()) {
                ProductDataModel productDataModel = new ProductDataModel(
                        String.valueOf(entry.getKey()).replaceAll(MesPcnExtConstWords.ONE_SPACE, MesPcnExtConstWords.EMPTY).trim(),
                        optional.get().getFieldName().replaceAll(MesPcnExtConstWords.ONE_SPACE, MesPcnExtConstWords.EMPTY).trim(),
                        String.valueOf(entry.getValue()).replaceAll(MesPcnExtConstWords.ONE_SPACE, MesPcnExtConstWords.EMPTY).trim());
                productDataModels.add(productDataModel);
            }
        }
        productData.setLineData(JSON.toJSONString(productDataModels));
    }

    private String assembleMidDbSn(Map<String, Object> map, String[] snArr) {
        String serialNumber = "";
        StringBuilder snBuilder = new StringBuilder(serialNumber);
        for (String s : snArr) {
            snBuilder.append(map.get(s) == null ? "" : String.valueOf(map.get(s)).trim());
        }
        serialNumber = snBuilder.toString();
        return serialNumber.toUpperCase();
    }

    private List<Map<String, Object>> pollingDb(StationRequestBean reqBean,
                                                BsJdbcTemplate bj,
                                                Map<String, MesProdRouteOptParam> codeValueMap,
                                                List<MesObjectCfg> objectCfgList,
                                                MesDatasource db,
                                                MesDataObject dataObject,
                                                String[] snArr) {

        log.info("数据采集生产线[{}]工位[{}]:执行数据采集中,拼接SQL中", reqBean.getWorkCenterCode(), reqBean.getWorkCellCode());
        checkSelectFieldHasSN(reqBean, objectCfgList, snArr);

        StringBuilder sqlBuilder = new StringBuilder();
        sqlBuilder.append(SELECT);
        for (MesObjectCfg mesObjectCfg : objectCfgList) {
            sqlBuilder.append(mesObjectCfg.getFieldCode()).append(MesPcnExtConstWords.COMMA);
        }
        sqlBuilder = new StringBuilder(sqlBuilder.substring(0, sqlBuilder.toString().length() - 1));
        sqlBuilder.append(MesPcnExtConstWords.ONE_SPACE).append(FROM).append(MesPcnExtConstWords.ONE_SPACE)
                .append(codeValueMap.get(OTHER_TABLE_NAME).getParamValue()).append(WHERE);
        if (Integer.parseInt(codeValueMap.get(IS_SELF_ADDITION).getParamValue()) == CommonEnumUtil.TRUE_OR_FALSE.TRUE.getValue()) {

            checkDataObject(reqBean, dataObject.getSelfAddition(), SELF_ADDITION);

            sqlBuilder.append(dataObject.getSelfAddition())
                    .append(MesPcnExtConstWords.GREATER_THAN)
                    .append(StringUtils.isEmpty(dataObject.getSelfAdditionValue()) ? 0 : dataObject.getSelfAdditionValue());
        } else {
            checkDataObject(reqBean, dataObject.getFeedField(), FEED_FIELD);
            checkDataObject(reqBean, dataObject.getReadFlagValue(), READ_FLAG_VALUE);

            sqlBuilder.append(dataObject.getFeedField()).append(MesPcnExtConstWords.EQ).append(dataObject.getReadFlagValue());
        }

        for (String ar : snArr) {
            sqlBuilder.append(AND).append(ar).append(IS_NOT_NULL).append(AND).append(ar).append(NOT_EQ_EMPTY);
        }

        if (codeValueMap.containsKey(ORDER_FIELD) && !StringUtils.isEmpty(codeValueMap.get(ORDER_FIELD))) {
            sqlBuilder.append(ORDER_BY)
                    .append(codeValueMap.get(ORDER_FIELD).getParamValue());
        }

        String modifiedSql = modifySqlByDbType(
                reqBean, sqlBuilder.toString(), db.getDsType(),
                (StringUtils.isEmpty(codeValueMap.get(LIMIT_ROWS)) || StringUtils.isEmpty(codeValueMap.get(LIMIT_ROWS).getParamValue()))
                        ? LIMIT_ROWS_DEFAULT : codeValueMap.get(LIMIT_ROWS).getParamValue());

        List<Map<String, Object>> mapList = null;

        log.info("数据采集生产线[{}]工位[{}]:执行数据采集中,while循环开始", reqBean.getWorkCenterCode(), reqBean.getWorkCellCode());
        while (getCenterRunningStatus(reqBean)) {
            try {
                Long threadSleepTime = 10L;
                if (codeValueMap.containsKey(THREAD_SLEEP_TIME) && !StringUtils.isEmpty(codeValueMap.get(THREAD_SLEEP_TIME).getParamValue())) {
                    threadSleepTime = Long.valueOf(String.valueOf(codeValueMap.get(THREAD_SLEEP_TIME).getParamValue()));
                }
                TimeUnit.MILLISECONDS.sleep(threadSleepTime);

                mapList = bj.getJdbcTemplate().queryForList(modifiedSql, new HashMap<>());
                if (!CollectionUtils.isEmpty(mapList)) {
                    log.info("数据采集生产线[{}]工位[{}]:执行数据采集完成,数据量[{}]", reqBean.getWorkCenterCode(), reqBean.getWorkCellCode(), mapList.size());
                    break;
                }
            } catch (InterruptedException e) {
                log.info("数据采集生产线[{}]工位[{}]:[InterruptedException]:循环读取db 线程睡眠失败", reqBean.getWorkCenterCode(), reqBean.getWorkCellCode());
                Thread.currentThread().interrupt();
            } catch (DataAccessException e) {
                log.info("数据采集生产线[{}]工位[{}]:[DataAccessException]:sql错误:{}", reqBean.getWorkCenterCode(), reqBean.getWorkCellCode(), e.getMessage());
                String err = String.format(LocaleUtil.transferLanguage(reqBean.getUserInfo(),
                        "数据采集生产线[%s]工位[%s]:sql错误:%s"), reqBean.getWorkCenterCode(), reqBean.getWorkCellCode(), e.getMessage());
                sendMessage(reqBean, null, err, MesPcnEnumUtil.STATION_BUSI_TYPE.MESSAGE, MesPcnEnumUtil.STATION_DATA_TYPE.EXP_TEXT);
                foundExThrow(reqBean, err);
            } catch (Exception ex) {
                //出现错误，中断线程
                log.info("数据采集生产线[{}]工位[{}]:[Exception]:{}", reqBean.getWorkCenterCode(), reqBean.getWorkCellCode(), ex.getMessage());
                String err = String.format(LocaleUtil.transferLanguage(reqBean.getUserInfo(),
                        "数据采集生产线[%s]工位[%s]:轮询数据库时发生错误,请联系运维人员."), reqBean.getWorkCenterCode(), reqBean.getWorkCellCode());
                sendMessage(reqBean, null, err + ex.getMessage(), MesPcnEnumUtil.STATION_BUSI_TYPE.MESSAGE, MesPcnEnumUtil.STATION_DATA_TYPE.EXP_TEXT);
                foundExThrow(reqBean, err + ex.getMessage());
            }
        }

        log.info("数据采集生产线[{}]工位[{}]:执行数据采集完成,数据量[{}],while循环结束", reqBean.getWorkCenterCode(), reqBean.getWorkCellCode(), CollectionUtils.isEmpty(mapList) ? 0 : mapList.size());
        return mapList;
    }

    private String modifySqlByDbType(StationRequestBean reqBean, String sql, String dataSourceType, String limitRows) {
        if (CommonEnumUtil.DATA_SOURCE_TYPE.SOURCE_MARIA_DB.name().equals(dataSourceType)) {
            sql = new StringJoiner(MesPcnExtConstWords.ONE_SPACE).add(sql).add(LIMIT).add(limitRows).toString();
        } else if (CommonEnumUtil.DATA_SOURCE_TYPE.SOURCE_SQL_SERVER.name().equals(dataSourceType)) {
            sql = sql.replaceFirst(SELECT, new StringJoiner(MesPcnExtConstWords.ONE_SPACE).add(SELECT_TOP).add(limitRows).toString());
        } else if (CommonEnumUtil.DATA_SOURCE_TYPE.SOURCE_ORACLE.name().equals(dataSourceType)) {
            sql = sql.replaceFirst(SELECT, SELECT_ROWNUM).
                    replaceFirst(WHERE, new StringBuilder().append(WHERE_ROWNUM_GREATER_THAN).append(limitRows).append(AND).toString());
        } else if (CommonEnumUtil.DATA_SOURCE_TYPE.SOURCE_POSTGRE_SQL.name().equals(dataSourceType)) {
            sql = new StringJoiner(MesPcnExtConstWords.ONE_SPACE).add(sql).add(LIMIT).add(limitRows).toString();
        } else {
            log.info("数据采集生产线[{}]工位[{}]:[MesDatasource]信息配置的[数据源类型]异常", reqBean.getWorkCenterCode(), reqBean.getWorkCellCode());
            String err = String.format(LocaleUtil.transferLanguage(reqBean.getUserInfo(), "数据采集生产线[%s]工位[%s]:[MesDatasource]信息配置的[数据源类型]异常"), reqBean.getWorkCenterCode(), reqBean.getWorkCellCode());
            sendMessage(reqBean, null, err, MesPcnEnumUtil.STATION_BUSI_TYPE.MESSAGE, MesPcnEnumUtil.STATION_DATA_TYPE.EXP_TEXT);
            foundExThrow(reqBean, err);
        }
        return sql;
    }

    private void checkSelectFieldHasSN(StationRequestBean reqBean, List<MesObjectCfg> objectCfgList, String[] snArr) {
        if (!StringUtils.isEmpty(snArr) && snArr.length > 0) {
            List<String> fieldCodes = objectCfgList.stream().
                    filter(o -> o.getIsSave() == CommonEnumUtil.TRUE_OR_FALSE.TRUE.getValue()).
                    map(MesObjectCfg::getFieldCode).
                    collect(Collectors.toList());

            for (int i = 0; i < snArr.length; i++) {
                if (!fieldCodes.contains(snArr[i])) {
                    log.info("数据采集生产线[{}]工位[{}]:产品条码字段[{}]未配置在[MesObjectCfg]信息中", reqBean.getWorkCenterCode(), reqBean.getWorkCellCode(), snArr[i]);
                    String err = String.format(LocaleUtil.transferLanguage(reqBean.getUserInfo(),
                            "数据采集生产线[%s]工位[%s]:产品条码字段[%s]未配置在[MesObjectCfg]信息中"), reqBean.getWorkCenterCode(), reqBean.getWorkCellCode(), snArr[i]);
                    sendMessage(reqBean, null, err, MesPcnEnumUtil.STATION_BUSI_TYPE.MESSAGE, MesPcnEnumUtil.STATION_DATA_TYPE.EXP_TEXT);
                    foundExThrow(reqBean, err);
                }
            }
        } else {
            log.info("数据采集生产线[{}]工位[{}]:工步参数[产品条码]配置异常", reqBean.getWorkCenterCode(), reqBean.getWorkCellCode());
            String err = String.format(LocaleUtil.transferLanguage(reqBean.getUserInfo(), "数据采集生产线[%s]工位[%s]:工步参数[产品条码]配置异常"), reqBean.getWorkCenterCode(), reqBean.getWorkCellCode());
            sendMessage(reqBean, null, err, MesPcnEnumUtil.STATION_BUSI_TYPE.MESSAGE, MesPcnEnumUtil.STATION_DATA_TYPE.EXP_TEXT);
            foundExThrow(reqBean, err);
        }
    }


    private void checkStepParams(Map<String, MesProdRouteOptParam> codeValueMap, StationRequestBean reqBean) {
        if (codeValueMap.get(OBJECT_CODE) == null || StringUtils.isEmpty(codeValueMap.get(OBJECT_CODE).getParamValue())) {
            log.info("数据采集生产线[{}]工位[{}]:工步参数缺失[对象代码]", reqBean.getWorkCenterCode(), reqBean.getWorkCellCode());
            String err = String.format(LocaleUtil.transferLanguage(reqBean.getUserInfo(), "数据采集生产线[%s]工位[%s]:工步参数缺失[对象代码]"), reqBean.getWorkCenterCode(), reqBean.getWorkCellCode());
            sendMessage(reqBean, null, err, MesPcnEnumUtil.STATION_BUSI_TYPE.MESSAGE, MesPcnEnumUtil.STATION_DATA_TYPE.EXP_TEXT);
            foundExThrow(reqBean, err);
        }
        if (codeValueMap.get(SERIAL_NUMBER) == null || StringUtils.isEmpty(codeValueMap.get(SERIAL_NUMBER).getParamValue())) {
            log.info("数据采集生产线[{}]工位[{}]:工步参数缺失[产品条码]", reqBean.getWorkCenterCode(), reqBean.getWorkCellCode());
            String err = String.format(LocaleUtil.transferLanguage(reqBean.getUserInfo(), "数据采集生产线[%s]工位[%s]:工步参数缺失[产品条码]"), reqBean.getWorkCenterCode(), reqBean.getWorkCellCode());
            sendMessage(reqBean, null, err, MesPcnEnumUtil.STATION_BUSI_TYPE.MESSAGE, MesPcnEnumUtil.STATION_DATA_TYPE.EXP_TEXT);
            foundExThrow(reqBean, err);
        }
        if (codeValueMap.get(MesPcnExtConstWords.IS_RFID_CELL) == null || StringUtils.isEmpty(codeValueMap.get(MesPcnExtConstWords.IS_RFID_CELL).getParamValue())) {
            log.info("数据采集生产线[{}]工位[{}]:工步参数缺失[是否属于FRID工位]", reqBean.getWorkCenterCode(), reqBean.getWorkCellCode());
            String err = String.format(LocaleUtil.transferLanguage(reqBean.getUserInfo(), "数据采集生产线[%s]工位[%s]:工步参数缺失[是否属于FRID工位]"), reqBean.getWorkCenterCode(), reqBean.getWorkCellCode());
            sendMessage(reqBean, null, err, MesPcnEnumUtil.STATION_BUSI_TYPE.MESSAGE, MesPcnEnumUtil.STATION_DATA_TYPE.EXP_TEXT);
            foundExThrow(reqBean, err);
        }
        if (codeValueMap.get(IS_SELF_ADDITION) == null || StringUtils.isEmpty(codeValueMap.get(IS_SELF_ADDITION).getParamValue())) {
            log.info("数据采集生产线[{}]工位[{}]:工步参数缺失[是否选择自增列]", reqBean.getWorkCenterCode(), reqBean.getWorkCellCode());
            String err = String.format(LocaleUtil.transferLanguage(reqBean.getUserInfo(), "数据采集生产线[%s]工位[%s]:工步参数缺失[是否选择自增列]"), reqBean.getWorkCenterCode(), reqBean.getWorkCellCode());
            sendMessage(reqBean, null, err, MesPcnEnumUtil.STATION_BUSI_TYPE.MESSAGE, MesPcnEnumUtil.STATION_DATA_TYPE.EXP_TEXT);
            foundExThrow(reqBean, err);
        } else {
            int selfAdd = 0;
            try {
                selfAdd = Integer.parseInt(codeValueMap.get(IS_SELF_ADDITION).getParamValue());
            } catch (NumberFormatException nfe) {
                log.info("数据采集生产线[{}]工位[{}]:工步参数缺失[是否选择自增列]", reqBean.getWorkCenterCode(), reqBean.getWorkCellCode());
                String err = String.format(LocaleUtil.transferLanguage(reqBean.getUserInfo(), "数据采集生产线[%s]工位[%s]:工步参数缺失[是否选择自增列]"), reqBean.getWorkCenterCode(), reqBean.getWorkCellCode());
                sendMessage(reqBean, null, err, MesPcnEnumUtil.STATION_BUSI_TYPE.MESSAGE, MesPcnEnumUtil.STATION_DATA_TYPE.EXP_TEXT);
                foundExThrow(reqBean, err);
            }
            if (selfAdd != CommonEnumUtil.TRUE_OR_FALSE.TRUE.getValue() && selfAdd != CommonEnumUtil.TRUE_OR_FALSE.FALSE.getValue()) {
                log.info("数据采集生产线[{}]工位[{}]:工步参数是否选择自增列的值(枚举值)错误", reqBean.getWorkCenterCode(), reqBean.getWorkCellCode());
                String err = String.format(LocaleUtil.transferLanguage(reqBean.getUserInfo(), "数据采集生产线[%s]工位[%s]:工步参数是否选择自增列的值(枚举值)错误"), reqBean.getWorkCenterCode(), reqBean.getWorkCellCode());
                sendMessage(reqBean, null, err, MesPcnEnumUtil.STATION_BUSI_TYPE.MESSAGE, MesPcnEnumUtil.STATION_DATA_TYPE.EXP_TEXT);
                foundExThrow(reqBean, err);
            }
        }
        if (codeValueMap.get(OTHER_TABLE_NAME) == null || StringUtils.isEmpty(codeValueMap.get(OTHER_TABLE_NAME).getParamValue())) {
            log.info("数据采集生产线[{}]工位[{}]:工步参数缺失[第三方表名]", reqBean.getWorkCenterCode(), reqBean.getWorkCellCode());
            String err = String.format(LocaleUtil.transferLanguage(reqBean.getUserInfo(), "数据采集生产线[%s]工位[%s]:工步参数缺失[第三方表名]"), reqBean.getWorkCenterCode(), reqBean.getWorkCellCode());
            sendMessage(reqBean, null, err, MesPcnEnumUtil.STATION_BUSI_TYPE.MESSAGE, MesPcnEnumUtil.STATION_DATA_TYPE.EXP_TEXT);
            foundExThrow(reqBean, err);
        }
    }

    private void checkDataObject(StationRequestBean reqBean, String fieldValue, String fieldName) {
        if (StringUtils.isEmpty(fieldValue)) {
            log.info("数据采集生产线[{}]工位[{}]:[MesDataObject]信息未配置[{}]", reqBean.getWorkCenterCode(), reqBean.getWorkCellCode(), fieldName);
            String err = String.format(LocaleUtil.transferLanguage(reqBean.getUserInfo(), "数据采集生产线[%s]工位[%s]:[MesDataObject]信息未配置[%s]"), reqBean.getWorkCenterCode(), reqBean.getWorkCellCode(), fieldName);
            sendMessage(reqBean, null, err, MesPcnEnumUtil.STATION_BUSI_TYPE.MESSAGE, MesPcnEnumUtil.STATION_DATA_TYPE.EXP_TEXT);
            foundExThrow(reqBean, err);
        }
    }


    private MesDataObject findDataObjByDsObjCode(StationRequestBean reqBean, String objectCode, MesDatasource db) {
        DdlPackBean packBean = DdlPackBean.getDdlPackBean(reqBean.getOrganizeCode());
        DdlPreparedPack.getStringEqualPack(db.getDsCode(), MesPcnExtConstWords.DS_CODE, packBean);
        DdlPreparedPack.getStringEqualPack(objectCode, MesPcnExtConstWords.OBJECT_CODE, packBean);
        return dataObjectRepository.getByProperty(packBean);
    }

    private List<MesObjectCfg> findObjectCfgsByObjCode(StationRequestBean reqBean, String objectCode) {
        DdlPackBean pBean = DdlPackBean.getDdlPackBean(reqBean.getOrganizeCode());
        DdlPreparedPack.getStringEqualPack(objectCode, MesPcnExtConstWords.OBJECT_CODE, pBean);
        return objectCfgRepository.findByHqlWhere(pBean);
    }

    private boolean getCenterRunningStatus(StationRequestBean reqBean) {
        String key = PcsCtlConst.RUNNING_STATUS + reqBean.getWorkCenterCode();
        boolean result = redisMesPcn.hasKey(key);
        if (!result) {
            log.info("数据采集生产线[{}]工位[{}]:RUNNING_STATUS=[{}]", reqBean.getWorkCenterCode(), reqBean.getWorkCellCode(), result);
        }
        return result;
    }

    private MesProductData getBasicProductData(StationRequestBean reqBean, MesDatasource db, MesDataObject dataObject) {
        MesProductData productData = new MesProductData();
        //EP存在同一采集设备不同线的工位共用的情况,所有不能直接给生产线赋值,后面从条码信息中取值,但是已经约定不同的线共用采集设备对应的工位相同,所以这里工位代码可以赋值
        productData.setWorkCellCode(reqBean.getWorkCellCode());

        productData.setEquCode(db.getEquipmentCode());
        MesEquipment eqp = StringUtils.isEmpty(db.getEquipmentCode()) ? null :
                baseDataService.getEqpByCode(db.getEquipmentCode(), reqBean.getOrganizeCode());
        if (eqp != null) {
            productData.setEquName(eqp.getEquipmentName());
        }
        productData.setObjectCode(dataObject.getObjectCode());
        productData.setObjectName(dataObject.getObjectName());
        productData.setOrganizeCode(reqBean.getOrganizeCode());
        ConvertBean.serviceModelInitialize(productData, reqBean.getUserInfo());
        return productData;
    }
}
