commit

许鹏程

2023-10-23 a7b18ead641993787f29cfb3c143a39ef3e60b5e

commit

 product-server-data-center/src/main/java/com/product/data/center/controller/MesExternalController.java

@@ -77,6 +77,13 @@
        }
    }

    @PostMapping("/split-table-data/{version}")
    @ApiVersion(1)
    public String splitTableData(HttpServletRequest request) {
        mesExternalService.splitTableData();
        return OK();
    }

    @PostMapping("rehandle-error/{version}")
    @ApiVersion(1)
    public String rehandleError(HttpServletRequest request) {

 product-server-data-center/src/main/java/com/product/data/center/entity/SNAssociatedItemEntity.java

@@ -14,49 +14,49 @@

    @ExcelProperty("工单")
    private String moNumber;
	

    @ExcelProperty("整机MN")
    private String zj;
	

    @ExcelProperty("内机/外机SN")
    private String pkProductSn;
	
    @ExcelProperty("点击MN")

    @ExcelProperty("电机MN")
    private String dj;
	

    @ExcelProperty("压缩机SN")
    private String ysj;
	

    @ExcelProperty("压缩机MN")
    private String ysjmn;
	

    @ExcelProperty("电控板MN")
    private String dk;
	

    @ExcelProperty("显示板MN")
    private String xsq;
	

    @ExcelProperty("遥控器SN")
    private String ykq;
	

    @ExcelProperty("主板软件")
    private String zbrj;
	

    @ExcelProperty("wifi板SN")
    private String mk;
	

    @ExcelProperty("长尺配管")
    private String ccpg;
	

    @ExcelProperty("新风组件")
    private String xfzj;
	

    @ExcelProperty("安装服务码")
    private String fjd;
	

    @ExcelProperty("线体")
    private String areaName;
	

    @ExcelProperty("下线时间")
    private String inLineTime;

@@ -195,6 +195,6 @@
                + zbrj + ", mk=" + mk + ", ccpg=" + ccpg + ", xfzj=" + xfzj + ", fjd=" + fjd + ", areaName=" + areaName
                + ", inLineTime=" + inLineTime + "]";
    }
	
	


}

 product-server-data-center/src/main/java/com/product/data/center/service/MesExternalService.java

@@ -3,13 +3,13 @@
import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.date.DateTime;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.date.LocalDateTimeUtil;
import cn.hutool.core.thread.ThreadUtil;
import cn.hutool.core.util.NumberUtil;
import cn.hutool.http.HttpRequest;
import cn.hutool.http.HttpResponse;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.google.common.collect.Sets;
import com.product.common.utils.spring.SpringUtils;
import com.product.core.config.CoreConst;
import com.product.core.config.Global;
@@ -43,10 +43,7 @@
import java.sql.SQLException;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.concurrent.Callable;
import java.util.concurrent.CompletionService;
import java.util.concurrent.ExecutionException;
import java.util.concurrent.Future;
import java.util.concurrent.*;
import java.util.stream.Collectors;

/**
@@ -69,6 +66,78 @@
            this.commonService = SpringBeanUtil.getBean(CommonService.class);
        }
        return commonService;
    }

    public static void main(String[] args) {
        Date parse = DateUtil.parse("2023-10-23 18:22:50", "yyyy-MM-dd HH:mm:ss");
        Date parse1 = DateUtil.parse("2023-10-23 18:24:11", "yyyy-MM-dd HH:mm:ss");
        Date parse2 = DateUtil.parse("2023-10-23 18:24:21", "yyyy-MM-dd HH:mm:ss");

        Set<Date> set = Sets.newHashSet(parse, parse1, parse2);

        List<Date> sets = CollectionUtil.toList(set.toArray(new Date[0]));

        Optional<Date> max = sets.stream().max(Comparator.comparing((a) -> a.getTime()));
        System.out.println(max.get());

    }


    public void splitTableData() {
        FieldSetEntity reportDbConfig = getBaseDao().getFieldSetByFilter(CmnConst.PRODUCT_SYS_DATA_SYNC_MANAGER, "SYNC_NAME=?", new Object[]{"报表数据库"}, false);
        if (FieldSetEntity.isEmpty(reportDbConfig)) {
            throw new BaseException(ErrorCode.REPORT_DB_CONFIG_FAIL);
        }
        DataBaseEntity dbe = new DataBaseEntity(reportDbConfig);
        Dao reportDao = dbe.getDao();
        Set<String> trackingTableSet = QuerySqlParseUtil.getAllTableName(reportDao, dbe.getDbName(), "da_t_wip_tracking");

        for (String tableName : trackingTableSet) {
            //获取年份从表名最后一个 ä¸‹åˆ’线开始截取
            String year = tableName.substring(tableName.lastIndexOf("_") + 1);
            if (StringUtils.equalsAny(year, "2017", "2018") || year.length() > 4) {
                continue;
            }
            //获取表前缀
            String tablePrefix = tableName.substring(0, tableName.lastIndexOf("_"));
            ExecutorService executorService = Executors.newFixedThreadPool(12);
            for (int i = 1; i <= 12; i++) {
                if ("2023".equals(year) && i > 9) {
                    break;
                }
                final int finalI = i;
                executorService.submit(() -> {
                    Dao currentDao = dbe.newDao();
                    //获取当前月份 ä»¥MM格式化
                    String month = String.format("%02d", finalI);
                    //检查月份对应的表是否存在
                    String monthTableName = tablePrefix + "_" + year + month;
                    Set<String> allTableName = QuerySqlParseUtil.getAllTableName(reportDao, dbe.getDbName(), tablePrefix + "_" + year + month);
                    if (allTableName.size() == 0 || !allTableName.contains(monthTableName)) {
                        //根据原始表结构创建新表
                        String sql = "create table " + monthTableName + " like " + tableName;
                        currentDao.executeSql(sql);
                        SpringMVCContextHolder.getSystemLogger().info("创建表：" + monthTableName);
                    }
                    String sql = "INSERT INTO " + monthTableName + " SELECT * FROM " + tableName + " WHERE MONTH(update_date)=" + finalI;
                    currentDao.executeSql(sql);
                    currentDao.closeConnection();
                });

            }
            executorService.shutdown();
            while (true) {
                try {
                    if (executorService.awaitTermination(5, TimeUnit.SECONDS)) break;
                    Thread.sleep(5000);
                    SpringMVCContextHolder.getSystemLogger().info("线程等待中...");
                } catch (InterruptedException e) {
                    e.printStackTrace();
                }

            }
        }
        reportDao.closeConnection();
    }

    /**
@@ -130,6 +199,7 @@
            success = true;
            SpringMVCContextHolder.getSystemLogger().info("回写机号：" + serialNumber + "成功");
        } catch (Exception e) {
            e.printStackTrace();
            SpringMVCContextHolder.getSystemLogger().error("回写机号：" + serialNumber + "失败");
            SpringMVCContextHolder.getSystemLogger().error(e);
            throw new BaseException(ErrorCode.INSERT_DATA_FAIL);
@@ -161,6 +231,7 @@
        }
    }


    /**
     * æ’入数据到主库
     *
@@ -176,7 +247,16 @@
            if (DataTableEntity.isEmpty(masterDataTable)) {
                continue;
            }
            Object[] objects = masterDataTable.getData().stream().map(item -> item.getString(historyEntity.getPrimaryField())).toArray();
            Object[] objects = masterDataTable.getData().stream().map(item -> {
                String primaryValue = item.getString(historyEntity.getPrimaryField());
                //判断是否为数值且包含小数点 å°æ•°ç‚¹åŽé¢æ˜¯å¦å…¨æ˜¯0
                if (NumberUtil.isNumber(primaryValue) && primaryValue.contains(".") && primaryValue.substring(primaryValue.
                        indexOf(".") + 1).matches("^0*$")) {
                    //返回整数字符串
                    return primaryValue.substring(0, primaryValue.indexOf("."));
                }
                return primaryValue;
            }).toArray();
            //查询主库数据是否存在
            DataTableEntity list = dao.getList(historyEntity.getTableName(),
                    BaseUtil.buildQuestionMarkFilter(historyEntity.getPrimaryField(), objects.length, true),
@@ -234,7 +314,16 @@
                    //查询已存在的数据
                    DataTableEntity list = dao.getList(historyEntity.getTableName(),
                            BaseUtil.buildQuestionMarkFilter(historyEntity.getPrimaryField(), value.size(), true),
                            value.stream().map(item -> item.getString(historyEntity.getPrimaryField())).toArray());
                            value.stream().map(item -> {
                                String primaryValue = item.getString(historyEntity.getPrimaryField());
                                //判断是否为数值且包含小数点 å°æ•°ç‚¹åŽé¢æ˜¯å¦å…¨æ˜¯0
                                if (NumberUtil.isNumber(primaryValue) && primaryValue.contains(".") && primaryValue.substring(primaryValue.
                                        indexOf(".") + 1).matches("^0*$")) {
                                    //返回整数字符串
                                    return primaryValue.substring(0, primaryValue.indexOf("."));
                                }
                                return primaryValue;
                            }).toArray());
                    List<String> existIds = DataTableEntity.isEmpty(list) ? null : list.getData().stream().map(item -> item.getString(historyEntity.getPrimaryField())).collect(Collectors.toList());
                    for (FieldSetEntity fieldSetEntity : value) {
                        String primaryValue = fieldSetEntity.getString(historyEntity.getPrimaryField());
@@ -298,11 +387,41 @@
                }
            }
        }
        if (CmnConst.T_WIP_TRACKING.equalsIgnoreCase(targetTableName) && dt.getRows() > 1) {
            List<FieldSetEntity> data = dt.getData();
            FieldSetEntity newData = null;
            for (int i = 0; i < data.size(); i++) {
                FieldSetEntity fieldSetEntity = data.get(i);
                if ("ch-kt".equals(fieldSetEntity.getValue("pre_master_key"))) {
                    data.remove(i);
                    dt.removeFieldSetEntity(i);
                    break;
                } else if (newData == null) {
                    newData = fieldSetEntity;
                    continue;
                }

                Date date = fieldSetEntity.getDate(historyEntity.getTimeField());
                if (date.getTime() > newData.getDate(historyEntity.getTimeField()).getTime()) {
                    newData = fieldSetEntity;
                }
            }
            if (newData != null) {
                dt = new DataTableEntity();
                dt.addFieldSetEntity(newData);
            }
            //在list中取出时间最近的数据
            Optional<FieldSetEntity> max = data.stream().max(Comparator.comparing((a) -> a.getDate(historyEntity.getTimeField()).getTime()));

        }
        DataTableEntity subData = dt.clones();
        DataTableEntity masterData = dt.clones();
        for (int i = 0; i < subData.getRows(); i++) {
            FieldSetEntity fs = subData.getFieldSetEntity(i);
            String preMasterKey = fs.getString("pre_master_key");
            if (historyEntity.getPrimaryField().equals(preMasterKey)) {
                preMasterKey = null;
            }
            if ("ch-kt".equals(fs.getString("source_info")) && StringUtils.isEmpty(preMasterKey)) {
                preMasterKey = fs.getString(historyEntity.getPrimaryField());
            }
@@ -313,6 +432,11 @@
            fs.remove("~table_name~");
            masterData.getFieldSetEntity(i).remove("~table_name~");
            masterData.setFieldValue(i, "pre_master_key", null);
            if (StringUtils.isEmpty(historyEntity.getPrimaryField())) {
                subData.removeFieldSetEntity(i);
                masterData.removeFieldSetEntity(i);
                i--;
            }
        }
        historyEntity.setArchivedDataTable(dt);
        historyEntity.setMasterDataTable(masterData);
@@ -329,17 +453,12 @@
        CompletionService<DataTableEntity> objectCompletionService = ThreadUtil.newCompletionService();
        //多线程查询单张表，等待所有线程查询完毕
        for (String tableName : tableArray) {
            objectCompletionService.submit(() -> dao.getList("select a.*,'" + tableName + "' as '~table_name~'  from " + tableName + "a where " + filterFieldName + " = ?", new Object[]{serialNumber}));
            objectCompletionService.submit(() -> dao.getList("select a.*,'" + tableName + "' as '~table_name~'  from " + tableName + " a where " + filterFieldName + " = ?", new Object[]{serialNumber}));
        }
        DataTableEntity data = new DataTableEntity();
        Future<DataTableEntity> take = objectCompletionService.take();
        for (int i = 0; i < tableArray.length; i++) {
            DataTableEntity dataTableEntity = take.get();
            DataTableEntity dataTableEntity = objectCompletionService.take().get();
            BaseUtil.dataTableMerge(data, dataTableEntity);
        }

        if (DataTableEntity.isEmpty(data) && !"product_sn".equals(filterFieldName)) {
            throw new BaseException(errorCodes[1]);
        }
        return data;
    }

 product-server-data-center/src/main/java/com/product/data/center/service/MesExternalService1.java

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,977 @@
//package com.product.data.center.service;
//
//import cn.hutool.core.collection.CollectionUtil;
//import cn.hutool.core.date.DateTime;
//import cn.hutool.core.date.DateUtil;
//import cn.hutool.core.thread.ThreadUtil;
//import cn.hutool.core.util.NumberUtil;
//import cn.hutool.http.HttpRequest;
//import cn.hutool.http.HttpResponse;
//import com.alibaba.fastjson.JSON;
//import com.alibaba.fastjson.JSONObject;
//import com.product.common.utils.spring.SpringUtils;
//import com.product.core.config.CoreConst;
//import com.product.core.config.Global;
//import com.product.core.entity.DataTableEntity;
//import com.product.core.entity.FieldSetEntity;
//import com.product.core.exception.BaseException;
//import com.product.core.service.support.AbstractBaseService;
//import com.product.core.sign.SignUtil;
//import com.product.core.spring.context.SpringBeanUtil;
//import com.product.core.spring.context.SpringMVCContextHolder;
//import com.product.core.transfer.Transactional;
//import com.product.core.util.JsonUtil;
//import com.product.data.center.config.CmnConst;
//import com.product.data.center.config.ErrorCode;
//import com.product.data.center.entity.HistoryEntity;
//import com.product.data.center.service.ide.IMesExternalService;
//import com.product.data.center.utils.QuerySqlParseUtil;
//import com.product.datasource.dao.Dao;
//import com.product.datasource.dao.impl.OracleDaoImpl;
//import com.product.datasource.entity.DataBaseEntity;
//import com.product.quartz.service.IRemoteService;
//import com.product.util.BaseUtil;
//import oracle.jdbc.internal.OracleTypes;
//import org.apache.commons.lang3.StringUtils;
//import org.springframework.beans.factory.annotation.Value;
//import org.springframework.stereotype.Service;
//
//import javax.servlet.http.HttpServletRequest;
//import java.sql.CallableStatement;
//import java.sql.Connection;
//import java.sql.SQLException;
//import java.util.*;
//import java.util.concurrent.CompletionService;
//import java.util.concurrent.ExecutionException;
//import java.util.concurrent.Future;
//import java.util.stream.Collectors;
//
///**
// * @Author cheng
// * @Date 2022/12/16 13:20
// * @Desc MES外部接口
// */
//@Service
//public class MesExternalService1 extends AbstractBaseService implements IMesExternalService, IRemoteService, com.product.data.service.impl.IRemoteService {
//
//    @Value("${data.system.name}")
//    private String dataSystemName;
//
//    private CommonService commonService = null;
//
//    private FieldSetEntity collectLogCache = null;
//
//    public CommonService getCommonService() {
//        if (this.commonService == null) {
//            this.commonService = SpringBeanUtil.getBean(CommonService.class);
//        }
//        return commonService;
//    }
//
//    /**
//     * èŽ·å–历史数据
//     */
//    public void getHistoryData(FieldSetEntity fse) throws BaseException, ExecutionException, InterruptedException {
//
//        //机号
//        String serialNumber = fse.getString("serial_number");
//        SpringMVCContextHolder.getSystemLogger().info("准备回写机号数据：" + serialNumber);
//        if (StringUtils.isEmpty(serialNumber)) {
//            throw new BaseException(ErrorCode.SERIAL_NUMBER_IS_NULL);
//        }
//
//        FieldSetEntity fs = getBaseDao().getFieldSetEntityByFilter(CmnConst.PRODUCT_SYS_DATA_SYNC_MES, "LENGTH(data_source)>1  limit 1", null, false);
//        if (FieldSetEntity.isEmpty(fs)) {
//            throw new BaseException(ErrorCode.MO_NUMBER_SYNC_DATASOURCE_ERROR);
//        }
//        FieldSetEntity reportDbConfig = getBaseDao().getFieldSetByFilter(CmnConst.PRODUCT_SYS_DATA_SYNC_MANAGER, "SYNC_NAME=?", new Object[]{"报表数据库"}, false);
//        if (FieldSetEntity.isEmpty(reportDbConfig)) {
//            throw new BaseException(ErrorCode.REPORT_DB_CONFIG_FAIL);
//        }
//        DataTableEntity dt = getBaseDao().listTable(CmnConst.PRODUCT_SYS_DATA_COLLECT, "upper(source_table) in ('T_WIP_TRACKING','T_WIP_PRODUCT_KEYP','T_WIP_DETAIL','T_PM_PRODUCT_SN') and data_source in (select  uuid from product_sys_data_sync_manager)", new Object[]{}, new Object[]{"uuid,id,data_source,source_table"});
//
//        Map<String, List<FieldSetEntity>> groupByCollectId = dt.getData().stream().collect(Collectors.groupingBy(item -> item.getString("id")));
//        Map<String, List<FieldSetEntity>> groupBySourceTable = dt.getData().stream().collect(Collectors.groupingBy(item -> item.getString("source_table")));
//
//        DataBaseEntity dbe = new DataBaseEntity(reportDbConfig);
//        Dao reportDao = dbe.getDao();
//        String reportDbName = dbe.getDbName();
//        Set<String> trackingTableSet = QuerySqlParseUtil.getAllTableName(reportDao, reportDbName, "da_t_wip_tracking");
//        HistoryEntity trackingData = historyBeforeDispose(getData(reportDao, trackingTableSet, "serial_number",
//                serialNumber, new ErrorCode[]{ErrorCode.TRACKING_TABLE_NOT_EXISTS, ErrorCode.NOT_FOUND_SERIAL_NUMBER}), CmnConst.T_WIP_TRACKING);
//
//        Set<String> keypTableSet = QuerySqlParseUtil.getAllTableName(reportDao, reportDbName, "da_t_wip_product_keyp");
//        HistoryEntity keypData = historyBeforeDispose(getData(reportDao, keypTableSet, "pk_product_sn", serialNumber, new ErrorCode[]{ErrorCode.KEYP_TABLE_NOT_EXISTS, ErrorCode.KEYP_DATA_NOT_FOUND}), CmnConst.T_WIP_PRODUCT_KEYP);
//
//        Set<String> detailTableSet = QuerySqlParseUtil.getAllTableName(reportDao, reportDbName, "da_t_wip_detail");
//        HistoryEntity detailData = historyBeforeDispose(getData(reportDao, detailTableSet, "serial_number", serialNumber, new ErrorCode[]{ErrorCode.DETAIL_TABLE_NOT_EXISTS, ErrorCode.DETAIL_DATA_NOT_FOUND}), CmnConst.T_WIP_DETAIL);
//
////        Set<String> productSnTableSet = QuerySqlParseUtil.getAllTableName(reportDao, reportDbName, "da_t_pm_product_sn");
////        HistoryEntity productSnData = historyBeforeDispose(getData(reportDao, productSnTableSet, "product_sn", serialNumber, new ErrorCode[]{ErrorCode.PRODUCT_SN_TABLE_NOT_EXISTS, ErrorCode.PRODUCT_SN_DATA_NOT_FOUND}), CmnConst.T_PM_PRODUCT_SN);
//
//
//        //主库数据源配置
//        String masterDataSource = fs.getString("data_source");
//        dbe = new DataBaseEntity(masterDataSource);
//        Map<String, Dao> groupDao = new HashMap<>();
//        Dao dao = dbe.getDao();
//        boolean success = false;
//        try {
//            Connection connection = dao.getConnection();
//            connection.setAutoCommit(false);
//            HistoryEntity[] historyEntities = {trackingData, keypData, detailData};
//            insertMasterTableData(dao, historyEntities);
//            insertSubTableData(groupDao, groupByCollectId, groupBySourceTable, historyEntities);
//            connection.commit();
//            batchCommit(groupDao);
//            success = true;
//            SpringMVCContextHolder.getSystemLogger().info("回写机号：" + serialNumber + "成功");
//        } catch (Exception e) {
//            SpringMVCContextHolder.getSystemLogger().error("回写机号：" + serialNumber + "失败");
//            SpringMVCContextHolder.getSystemLogger().error(e);
//            throw new BaseException(ErrorCode.INSERT_DATA_FAIL);
//        } finally {
//            try {
//                if (!success && !dao.getConnection().getAutoCommit()) {
//                    dao.getConnection().rollback();
//                }
//                dao.closeConnection();
//                for (Dao value : groupDao.values()) {
//                    try (Connection connection = value.getConnection()) {
//                        if (!connection.getAutoCommit()) {
//                            connection.rollback();
//                        }
//                    }
//                    value.closeConnection();
//
//                }
//            } catch (Exception e) {
//                e.printStackTrace();
//                SpringMVCContextHolder.getSystemLogger().error(e);
//            }
//        }
//    }
//
//    public void batchCommit(Map<String, Dao> groupDao) throws SQLException {
//        for (Dao value : groupDao.values()) {
//            value.getConnection().commit();
//        }
//    }
//
//    /**
//     * æ’入数据到主库
//     *
//     * @param dao
//     * @param historyEntities
//     */
//    public void insertMasterTableData(Dao dao, HistoryEntity[] historyEntities) {
//        for (HistoryEntity historyEntity : historyEntities) {
//            if (historyEntity == null) {
//                continue;
//            }
//            DataTableEntity masterDataTable = historyEntity.getMasterDataTable();
//            if (DataTableEntity.isEmpty(masterDataTable)) {
//                continue;
//            }
//            Object[] objects = masterDataTable.getData().stream().map(item -> item.getString(historyEntity.getPrimaryField())).toArray();
//            //查询主库数据是否存在
//            DataTableEntity list = dao.getList(historyEntity.getTableName(),
//                    BaseUtil.buildQuestionMarkFilter(historyEntity.getPrimaryField(), objects.length, true),
//                    new String[]{historyEntity.getPrimaryField()}, objects);
//            List<String> existsPrimaryValues = null;
//            if (!DataTableEntity.isEmpty(list)) {
//                existsPrimaryValues = list.getData().stream().map(item -> item.getString(historyEntity.getPrimaryField())).collect(Collectors.toList());
//            }
//            for (int i = 0; i < masterDataTable.getRows(); i++) {
//                String primaryValue = masterDataTable.getString(i, historyEntity.getPrimaryField());
//                if (existsPrimaryValues != null && existsPrimaryValues.contains(primaryValue)) {
//                    //数据存在跳过该数据
//                    continue;
//                }
//                FieldSetEntity fieldSetEntity = masterDataTable.getFieldSetEntity(i);
//                fieldSetEntity.getMeta().setTableName(new Object[]{historyEntity.getTableName()});
//                fieldSetEntity.remove("~table_name~");
//                dao.add(fieldSetEntity);
//            }
//        }
//    }
//
//
//    /**
//     * æ’入子库表数据
//     *
//     * @param groupDao           å­åº“dao
//     * @param groupByCollectId   é‡‡é›†é…ç½®æŒ‰é‡‡é›†id分组
//     * @param groupBySourceTable é‡‡é›†é…ç½®æŒ‰è¡¨ååˆ†ç»„
//     * @param historyEntities    åŽ†å²æ•°æ®
//     */
//    public void insertSubTableData(Map<String, Dao> groupDao, Map<String, List<FieldSetEntity>> groupByCollectId,
//                                   Map<String, List<FieldSetEntity>> groupBySourceTable, HistoryEntity[] historyEntities) throws Exception {
//        for (HistoryEntity historyEntity : historyEntities) {
//            if (historyEntity == null) {
//                continue;
//            }
//            Map<String, List<FieldSetEntity>> groupData = historyEntity.getGroupData();
//            if (groupData == null || groupData.isEmpty()) {
//                continue;
//            }
//            for (Map.Entry<String, List<FieldSetEntity>> entry : groupData.entrySet()) {
//                List<Dao> daoList = new ArrayList<>();
//                if ("ch-kt".equals(entry.getKey())) {
//                    List<FieldSetEntity> fieldSetEntityList = groupBySourceTable.get(historyEntity.getTableName().toLowerCase());
//                    Set<String> dataSourceSet = fieldSetEntityList.stream().map(item -> item.getString("data_source")).collect(Collectors.toSet());
//                    for (String sourceUuid : dataSourceSet) {
//                        daoList.add(getDao(groupDao, sourceUuid));
//                    }
//                } else {
//                    daoList.add(getDao(groupDao, groupByCollectId.get(entry.getKey()).get(0).getString("data_source")));
//                }
//                for (Dao dao : daoList) {
//                    List<FieldSetEntity> value = entry.getValue();
//                    //查询已存在的数据
//                    DataTableEntity list = dao.getList(historyEntity.getTableName(),
//                            BaseUtil.buildQuestionMarkFilter(historyEntity.getPrimaryField(), value.size(), true),
//                            value.stream().map(item -> item.getString(historyEntity.getPrimaryField())).toArray());
//                    List<String> existIds = DataTableEntity.isEmpty(list) ? null : list.getData().stream().map(item -> item.getString(historyEntity.getPrimaryField())).collect(Collectors.toList());
//                    for (FieldSetEntity fieldSetEntity : value) {
//                        String primaryValue = fieldSetEntity.getString(historyEntity.getPrimaryField());
//                        if (existIds != null && existIds.contains(NumberUtil.parseNumber(primaryValue).toString())) {
//                            continue;
//                        }
//                        fieldSetEntity.getMeta().setTableName(new Object[]{historyEntity.getTableName()});
//                        fieldSetEntity.remove("~table_name~");
//                        dao.add(fieldSetEntity);
//                    }
//                }
//            }
//        }
//    }
//
//    public Dao getDao(Map<String, Dao> groupDao, String sourceUuid) throws Exception {
//        Dao dao = groupDao.get(sourceUuid);
//        if (null == dao) {
//            DataBaseEntity dbe = new DataBaseEntity(sourceUuid);
//            dao = dbe.getDao();
//            dao.getConnection().setAutoCommit(false);
//            groupDao.put(sourceUuid, dao);
//        }
//        return dao;
//    }
//
//    public HistoryEntity historyBeforeDispose(DataTableEntity dt, String targetTableName) {
//        if (DataTableEntity.isEmpty(dt)) {
//            return null;
//        }
//        HistoryEntity historyEntity = new HistoryEntity();
//        historyEntity.setMoNumberField("mo_number");
//        if (CmnConst.T_WIP_TRACKING.equalsIgnoreCase(targetTableName)) {
//            historyEntity.setPrimaryField("wip_id");
//            historyEntity.setTimeField("update_date");
//        } else if (CmnConst.T_WIP_PRODUCT_KEYP.equalsIgnoreCase(targetTableName)) {
//            historyEntity.setPrimaryField("pk_id");
//            historyEntity.setTimeField("pk_loadtime");
//
//            historyEntity.setMoNumberField("pk_mo");
//        } else if (CmnConst.T_WIP_DETAIL.equalsIgnoreCase(targetTableName)) {
//            historyEntity.setPrimaryField("wip_detail_id");
//            historyEntity.setTimeField("update_date");
//        } else if (CmnConst.T_PM_PRODUCT_SN.equalsIgnoreCase(targetTableName)) {
//            historyEntity.setPrimaryField("row_id");
//            historyEntity.setTimeField("update_date");
//        } else {
//            return null;
//        }
//        historyEntity.setTableName(targetTableName.toUpperCase());
//        Date now = new Date();
//        for (int i = 0; i < dt.getRows(); i++) {
//            Map<Object, Object> values = dt.getFieldSetEntity(i).getValues();
//            //遍历map中的value是否为数字，如果是数字判断小数点后面是否有值，如果没有值则转换为整数
//            for (Map.Entry<Object, Object> entry : values.entrySet()) {
//                if (entry.getValue() instanceof Number) {
//                    Number number = (Number) entry.getValue();
//                    if (number.doubleValue() == number.intValue()) {
//                        entry.setValue(number.intValue());
//                    }
//                }
//            }
//        }
//        DataTableEntity subData = dt.clones();
//        DataTableEntity masterData = dt.clones();
//        for (int i = 0; i < subData.getRows(); i++) {
//            FieldSetEntity fs = subData.getFieldSetEntity(i);
//            String preMasterKey = fs.getString("pre_master_key");
//            if ("ch-kt".equals(fs.getString("source_info")) && StringUtils.isEmpty(preMasterKey)) {
//                preMasterKey = fs.getString(historyEntity.getPrimaryField());
//            }
//            fs.setValue(historyEntity.getPrimaryField(), preMasterKey);
//            fs.setValue("pre_master_key", preMasterKey);
//            masterData.setFieldValue(i, historyEntity.getTimeField(), now);
//            fs.setValue(historyEntity.getTimeField(), now);
//            fs.remove("~table_name~");
//            masterData.getFieldSetEntity(i).remove("~table_name~");
//            masterData.setFieldValue(i, "pre_master_key", null);
//        }
//        historyEntity.setArchivedDataTable(dt);
//        historyEntity.setMasterDataTable(masterData);
//        historyEntity.setSubDataTable(subData);
//        return historyEntity;
//    }
//
//    public DataTableEntity getData(Dao dao, Set<String> tableSet, String filterFieldName, String
//            serialNumber, ErrorCode[] errorCodes) throws InterruptedException, ExecutionException {
//        if (CollectionUtil.isEmpty(tableSet)) {
//            throw new BaseException(errorCodes[0]);
//        }
//        String[] tableArray = tableSet.toArray(new String[]{});
//        CompletionService<DataTableEntity> objectCompletionService = ThreadUtil.newCompletionService();
//        //多线程查询单张表，等待所有线程查询完毕
//        for (String tableName : tableArray) {
//            objectCompletionService.submit(() -> dao.getList("select a.*,'" + tableName + "' as '~table_name~'  from " + tableName + "a where " + filterFieldName + " = ?", new Object[]{serialNumber}));
//        }
//        DataTableEntity data = new DataTableEntity();
//        Future<DataTableEntity> take = objectCompletionService.take();
//        for (int i = 0; i < tableArray.length; i++) {
//            DataTableEntity dataTableEntity = take.get();
//            BaseUtil.dataTableMerge(data, dataTableEntity);
//        }
//
//        if (DataTableEntity.isEmpty(data) && !"product_sn".equals(filterFieldName)) {
//            throw new BaseException(errorCodes[1]);
//        }
//        return data;
//    }
//
//
//    /**
//     * åˆå§‹åŒ–制令单
//     *
//     * @param moNumbers
//     * @throws BaseException
//     */
//    public void initMoBase(String[] moNumbers) throws BaseException {
//        FieldSetEntity fs = getBaseDao().getFieldSetEntityByFilter(CmnConst.PRODUCT_SYS_DATA_SYNC_MES, "LENGTH(data_source)>1  limit 1", null, false);
//        if (FieldSetEntity.isEmpty(fs)) {
//            throw new BaseException(ErrorCode.MO_NUMBER_SYNC_DATASOURCE_ERROR);
//        }
//
//        String[] targetDataSource = fs.getString("target_data_source").split(",");
//
//        Dao[] targetDao = Arrays.stream(targetDataSource).map(item -> new DataBaseEntity(item).getDao()).toArray(Dao[]::new);
//        StringBuilder errorMsg = new StringBuilder();
//        for (Dao dao : targetDao) {
//            for (String moNumber : moNumbers) {
//                synchronized (moNumber.intern()) {
//                    try (Connection connection = dao.getConnection();
//                         CallableStatement callableStatement = connection.prepareCall(
//                                 "{CALL SMT_T_PM_MO_BASE(?,?,?,?,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,?,null,null" +
//                                         ",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null)}")) {
//                        callableStatement.setInt(1, 5);
//                        callableStatement.registerOutParameter(2, OracleTypes.SMALLINT);
//                        callableStatement.registerOutParameter(3, OracleTypes.VARCHAR);
//                        callableStatement.setString(4, moNumber);
//                        callableStatement.setString(5, "1");
//                        callableStatement.execute();
//                        //执行后返回的错误码
//                        int errorCode = callableStatement.getInt(2);
//                        //执行后返回的错误信息
//                        String errorText = callableStatement.getString(3);
//                        if (errorCode != 0) {
//                            //错误的
//                            errorMsg.append("制令单号:").append(moNumber);
//                            errorMsg.append("\nerrorCode：").append(errorCode);
//                            errorMsg.append("\nerrorText:").append(errorText);
//                        }
//                    } catch (Exception e) {
//                        SpringMVCContextHolder.getSystemLogger().error(e);
//                        e.printStackTrace();
//                        errorMsg.append("制令单号:").append(moNumber);
//                        errorMsg.append("\n执行时未知错误：").append(e.getMessage());
//                    }
//                }
//            }
//        }
//        if (errorMsg.length() > 0) {
//            throw new BaseException(ErrorCode.SUB_MO_BASE_INI_FAIL.getValue(), ErrorCode.SUB_MO_BASE_INI_FAIL.getText() + "。\n" + errorMsg);
//        }
//    }
//
//    /**
//     * @param moNumbers åˆ¶ä»¤å•å·,多个逗号分隔
//     * @param type      æ“ä½œç±»åž‹ 1 åˆ›å»ºåˆ¶ä»¤å• 2 æ›´æ–°åˆ¶ä»¤å•ä¸­çš„指定字段
//     * @throws BaseException
//     */
//    public void updateMoBase(String[] moNumbers, int type) throws BaseException {
//        FieldSetEntity fs = getBaseDao().getFieldSetEntityByFilter(CmnConst.PRODUCT_SYS_DATA_SYNC_MES, "LENGTH(data_source)>1  limit 1", null, false);
//        if (FieldSetEntity.isEmpty(fs)) {
//            throw new BaseException(ErrorCode.MO_NUMBER_SYNC_DATASOURCE_ERROR);
//        }
//        String dataSource = fs.getString("data_source");
//        String[] targetDataSource = fs.getString("target_data_source").split(",");
//        StringBuilder errorMessage = new StringBuilder();
//        DataBaseEntity dbe = new DataBaseEntity(dataSource);
//        for (int i = 0; i < moNumbers.length; i++) {
//            String moNumber = moNumbers[i];
//            try {
//                synchronized (moNumber.intern()) {
//                    Dao dao = dbe.getDao();
//                    FieldSetEntity one = dao.getOne("T_PM_MO_BASE", "MO_NUMBER=?", new Object[]{moNumber});
//                    if (FieldSetEntity.isEmpty(one)) {
//                        throw new BaseException(ErrorCode.MO_NUMBER_RECORD_SELECT_EMPTY);
//                    }
//                    one.setTableName("T_PM_MO_BASE");
//                    dao.closeConnection();
//                    Dao[] targetDao = Arrays.stream(targetDataSource).map(item -> new DataBaseEntity(item).getDao()).toArray(Dao[]::new);
//                    StringBuilder errorMsg = new StringBuilder();
//                    try {
//                        if (1 == type) {
//                            createMoBase(errorMsg, one, targetDao);
//                        } else if (2 == type) {
//                            updateMoBase(errorMsg, one, targetDao);
//                        }
//                        if (errorMsg.length() > 0) {
//                            //有错误
//                            throw new BaseException(ErrorCode.SUB_MO_BASE_CREATE_OR_UPDATE_FAIL.getValue(), ErrorCode.SUB_MO_BASE_CREATE_OR_UPDATE_FAIL.getText() + "。\n" + errorMsg);
//                        }
//                    } finally {
//                        for (Dao dao1 : targetDao) {
//                            dao1.closeConnection();
//                        }
//                    }
//                }
//            } catch (Exception e) {
//                e.printStackTrace();
//                SpringMVCContextHolder.getSystemLogger().error(e);
//                errorMessage.append(e.getMessage()).append("\n");
//            }
//
//        }
//        if (errorMessage.length() > 0) {
//            throw new BaseException(ErrorCode.SUB_MO_BASE_CREATE_OR_UPDATE_FAIL.getValue(), ErrorCode.SUB_MO_BASE_CREATE_OR_UPDATE_FAIL.getText() + "。\n" + errorMessage);
//        }
//    }
//
//    /**
//     * åˆ›å»ºåˆ¶ä»¤å•
//     *
//     * @param errorMsg
//     * @param moBase
//     * @param subDao
//     */
//    private void createMoBase(StringBuilder errorMsg, FieldSetEntity moBase, Dao[] subDao) {
//        String moNumber = moBase.getString("mo_number");
//        for (int i = 0; i < subDao.length; i++) {
//            Dao dao = subDao[i];
//            try {
//                //检查工单表是否存在
//                FieldSetEntity projectBase = dao.getOne("T_PM_PROJECT_BASE", "PROJECT_ID = ? ", new Object[]{moNumber});
//                if (FieldSetEntity.isEmpty(projectBase)) {
//                    throw new BaseException(ErrorCode.SUB_PROJECT_BASE_CAN_NOT_EMPTY);
//                }
//                //检查制令单表是否已存在
//                FieldSetEntity fs = dao.getOne(moBase.getTableName(), "mo_number = ? ", new Object[]{moNumber});
//                if (!FieldSetEntity.isEmpty(fs)) {
//                    //进行更新操作
//                    updateMoBase(errorMsg, moBase, new Dao[]{dao});
//                    continue;
//                }
//                //将 T_PM_MO_BASE.PROJECT_ID æ›´æ”¹ä¸ºå­åº“中对应 T_PM_PROJECT_BASE.PROJECT_BASE_ID
//                moBase.setValue("project_id", projectBase.getString("project_base_id"));
////                moBase.setValue("row_id", -1);
//                dao.add(moBase);
//            } catch (BaseException e) {
//                errorMsg.append("制令单号：[ " + moNumber + " ]IP:[").append(getIp(dao)).
//                        append("]").append(e.getMessageInfo());
//                e.printStackTrace();
//                SpringMVCContextHolder.getSystemLogger().error(e);
//            } catch (Exception e) {
//                errorMsg.append("制令单号：[ " + moNumber + " ]IP:[").append(getIp(dao)).append("]").append("创建制令单失败，未知的错误");
//                e.printStackTrace();
//                SpringMVCContextHolder.getSystemLogger().error(e);
//            }
//        }
//    }
//
//    /**
//     * æ›´æ–°åˆ¶ä»¤å•
//     *
//     * @param errorMsg
//     * @param moBase
//     * @param subDao
//     */
//    private void updateMoBase(StringBuilder errorMsg, FieldSetEntity moBase, Dao[] subDao) {
//        String moNumber = moBase.getString("mo_number");
//        for (int i = 0; i < subDao.length; i++) {
//            Dao dao = subDao[i];
//            try {
//                //检查制令单表是否已存在
//                FieldSetEntity fs = dao.getOne(moBase.getTableName(), "mo_number = ? ", new Object[]{moNumber});
//                if (FieldSetEntity.isEmpty(fs)) {
//                    //进行创建操作
//                    createMoBase(errorMsg, moBase, new Dao[]{dao});
//                    continue;
//                }
//                // å½“子库中制令单 input_qty å­—段为 0 æˆ–者 ä¸º null æ—¶ æ›´æ–°å¤šä¸ªå­—段，否则只更新target_qty å­—段
//                String[] updateField = StringUtils.isEmpty(fs.getString("input_qty")) || "0".equals(fs.getString("input_qty")) ?
//                        new String[]{"target_qty", "owner", "mo_create_date", "mo_schedule_date", "mo_due_date", "areaid", "technicsid", "close_flag", "default_group", "end_group"}
//                        : new String[]{"target_qty"};
//                StringBuilder sql = new StringBuilder();
//                sql.append(" UPDATE T_PM_MO_BASE SET ");
//                Object[] params = new Object[updateField.length + 1];
//                for (int k = 0; k < updateField.length; k++) {
//                    if (k > 0) {
//                        sql.append(",");
//                    }
//                    params[k] = moBase.getObject(updateField[k]);
//                    sql.append(updateField[k].toUpperCase()).append(" = ? ");
//                }
//                params[updateField.length] = moNumber;
//                sql.append(" WHERE MO_NUMBER = ?  ");
//                dao.executeSql(sql.toString(), params);
//            } catch (BaseException e) {
//                errorMsg.append("制令单号：[ " + moNumber + " ]IP:[").append(getIp(dao)).
//                        append("]").append(e.getMessageInfo());
//                e.printStackTrace();
//                SpringMVCContextHolder.getSystemLogger().error(e);
//            } catch (Exception e) {
//                errorMsg.append("制令单号：[ " + moNumber + " ]IP:[").append(getIp(dao)).append("]").append("更新制令单失败，未知的错误");
//                e.printStackTrace();
//                SpringMVCContextHolder.getSystemLogger().error(e);
//            }
//        }
//    }
//
//    private String getIp(Dao dao) {
//        return ((OracleDaoImpl) dao).getDataBaseEntity().getIp();
//    }
//
//    /**
//     * é‡‡é›†é…ç½®ä¿å­˜
//     *
//     * @param request
//     * @throws BaseException
//     */
//    @Override
//    @Transactional
//    public void saveCollectConfig(HttpServletRequest request) throws BaseException {
//        //服务名称
//        String serverName = request.getHeader("server-name");
//        if (StringUtils.isEmpty(serverName)) {
//            throw new BaseException(ErrorCode.SERVER_NAME_CAN_NOT_EMPTY);
//        }
//        FieldSetEntity fse = BaseUtil.getFieldSetEntity(request, CmnConst.PRODUCT_SYS_DATA_COLLECT);
//        //采集来源
//        String sourceInfo = fse.getString("id");
//        if (StringUtils.isEmpty(sourceInfo) || StringUtils.contains(sourceInfo, dataSystemName)) {
//            throw new BaseException(ErrorCode.COLLECT_SOURCE_VALUE);
//        }
//        commonSave(fse);
//    }
//
//    /**
//     * æå–配置保存
//     *
//     * @param fse
//     * @throws BaseException
//     */
//    @Override
//    @Transactional
//    public void saveExtractConfig(FieldSetEntity fse) throws BaseException {
//        String dataSource = fse.getString(CmnConst.TABLE_SYNC_MANAGER);
//        if (!StringUtils.isEmpty(dataSource)) {
//            FieldSetEntity fs = JsonUtil.pareseJsonToFieldSetEntity(dataSource);
//            if (!FieldSetEntity.isEmpty(fs)) {
//                commonSave(fs);
//            }
//        }
//        commonSave(fse);
//        getCommonService().saveDelRecordConfig(2, fse.getUUID());
//    }
//
//    /**
//     * å½’档配置保存
//     *
//     * @param fse
//     * @throws BaseException
//     */
//    @Override
//    @Transactional
//    public void saveArchiveConfig(FieldSetEntity fse) throws BaseException {
//        commonSave(fse);
//    }
//
//    /**
//     * @param fse
//     * @throws BaseException
//     */
//    @Override
//    public void saveCollectLog(FieldSetEntity fse) throws BaseException {
//        this.commonSave(fse);
//    }
//
//    /**
//     * @param fse
//     * @throws BaseException
//     */
//    @Override
//    public void saveExtractLog(FieldSetEntity fse) throws BaseException {
//        this.commonSave(fse);
//    }
//
//    /**
//     * æ•°æ®æºä¿å­˜ä¼šè°ƒç”¨è¯¥æ–¹æ³•
//     *
//     * @param fse
//     * @throws BaseException
//     */
//    @Override
//    public void saveSyncConnectionConfig(FieldSetEntity fse) throws BaseException {
//        //子服务保存数据库连接配置后调用该方法，传输数据到主服务
//        //TODO
//    }
//
//    /**
//     * å®šæ—¶ä»»åŠ¡ç”Ÿæˆæ—¥å¿—会调用该方法
//     *
//     * @param fse
//     * @throws BaseException
//     */
//    @Override
//    public void saveTimeLog(FieldSetEntity fse) throws BaseException {
//        //主服务提取日志保存后调用该方法传输到子服务
//        //TODO
//
//    }
//
//    private void commonSave(FieldSetEntity fse) throws BaseException {
//        String uuid = fse.getUUID();
//        FieldSetEntity fs = getBaseDao().getFieldSetEntity(fse.getTableName(), new String[]{CmnConst.UUID}, uuid, false);
//        Map<String, DataTableEntity> subData = fse.getSubData();
//        if (!CollectionUtil.isEmpty(subData)) {
//            DataTableEntity addDt = new DataTableEntity();
//            DataTableEntity updateDt = new DataTableEntity();
//            for (Map.Entry<String, DataTableEntity> entry : subData.entrySet()) {
//                String tableName = entry.getKey();
//                DataTableEntity value = entry.getValue();
//                if (DataTableEntity.isEmpty(value)) {
//                    continue;
//                }
//                Object[] uuids = value.getUuids();
//                DataTableEntity dt = getBaseDao().listTable(tableName, BaseUtil.buildQuestionMarkFilter(CmnConst.UUID, uuids.length, true), uuids, new String[]{CmnConst.UUID});
//                for (int i = 0; i < value.getRows(); i++) {
//                    uuid = value.getString(i, CmnConst.UUID);
//                    List<FieldSetEntity> fieldSetEntity = dt.getFieldSetEntity(uuid);
//                    if (CollectionUtil.isEmpty(fieldSetEntity)) {
//                        addDt.addFieldSetEntity(value.getFieldSetEntity(i));
//                        value.setFieldValue(i, CoreConst.SYSTEM_DATA_OPERATE_TYPE, "add");
//                    } else {
//                        addDt.addFieldSetEntity(updateDt.getFieldSetEntity(i));
//                        value.setFieldValue(i, CoreConst.SYSTEM_DATA_OPERATE_TYPE, "update");
//                    }
//                }
//                if (!DataTableEntity.isEmpty(addDt)) {
//                    getBaseDao().add(addDt);
//                }
//                if (!DataTableEntity.isEmpty(updateDt)) {
//                    getBaseDao().update(updateDt);
//                }
//            }
//        }
//        if (FieldSetEntity.isEmpty(fs)) {
//            getBaseDao().add(fse, false);
//        } else {
//            getBaseDao().update(fse, false);
//        }
//    }
//
//    /**
//     * è°ƒç”¨è¿œç¨‹ä¸»æœåŠ¡å™¨é‡‡é›†ä¿å­˜
//     *
//     * @param fse
//     * @return
//     * @throws BaseException
//     */
//    public FieldSetEntity remoteSaveCollectConfig(FieldSetEntity fse) throws BaseException {
//        if ("ch-kt".equals(dataSystemName)) {
//            return fse;
//        }
//        FieldSetEntity fs = getBaseDao().getFieldSetByFilter(CmnConst.PRODUCT_MES_SERVER, "server_type=0", null, false);
//        if (FieldSetEntity.isEmpty(fs)) {
//            throw new BaseException(ErrorCode.REMOTE_SERVER_CONFIG_EMPTY);
//        }
//        //服务域名的端口
//        String ipPort = fs.getString("server_url");
//        String serverName = fs.getString("server_name");
//        String serverUrl = ipPort + CmnConst.SAVE_COLLECT_URL;
//        FieldSetEntity res = doPost(serverUrl, serverName, fse);
//        return res;
//    }
//
//    /**
//     * ä¸»æœåŠ¡æå–保存日志后调用该方法传入到子服务保存
//     *
//     * @param fse
//     * @return
//     * @throws BaseException
//     */
//    public void remoteSaveExtractLog(FieldSetEntity fse) throws BaseException {
//        try {
//            if ("ch-kt".equals(dataSystemName)) {
//                //主服务采集日志uuid
//                String preStepUuid = fse.getString(CmnConst.PRE_STEP_UUID);
//                StringBuilder sql = new StringBuilder();
//                sql.append("\nSELECT ");
//                sql.append("\nserver.* ");
//                sql.append("\nFROM ").append(CmnConst.PRODUCT_SYS_DATA_COLLECT).append(" collect ");
//                sql.append("\nJOIN ").append(CmnConst.PRODUCT_SYS_DATA_CENTER_LOG).append(" log ");
//                sql.append("\nON log.config_uuid=collect.uuid and log.type=1 ");
//                sql.append("\nJOIN ").append(CmnConst.PRODUCT_MES_SERVER).append(" server ");
//                sql.append("\nON collect.id like concat(server.server_name,'%') ");
//                sql.append("\nWHERE log.uuid=? and collect.id not like concat(?,'%')  limit 1");
//                FieldSetEntity fs = getBaseDao().getFieldSetBySQL(sql.toString(), new Object[]{preStepUuid, dataSystemName}, false);
//                if (FieldSetEntity.isEmpty(fs)) {
//                    return;
//                }
//                String ipPort = fs.getString("server_url");
//                String serverName = fs.getString("server_name");
//                String serverUrl = ipPort + CmnConst.SAVE_EXTRACT_LOG_URL;
//                doPostAsync(serverUrl, serverName, fse);
//            }
//        } catch (Exception e) {
//            //捕获异常为了使采集定时任务正常运行
//            SpringMVCContextHolder.getSystemLogger().error(e);
//            e.printStackTrace();
//        }
//    }
//
//    /**
//     * è°ƒç”¨è¿œç¨‹ä¸»æœåŠ¡å™¨ä¿å­˜
//     *
//     * @param fse
//     * @return
//     * @throws BaseException
//     */
//    public void remoteSaveExtractConfig(FieldSetEntity fse) throws BaseException {
//        DataTableEntity dt = getRemoteSubServer();
//        if (DataTableEntity.isEmpty(dt)) {
//            return;
//        }
//        FieldSetEntity extractTargetSource = getBaseDao().getFieldSetEntity(CmnConst.TABLE_SYNC_MANAGER, fse.getString("extract_target_source"), false);
//        if (!FieldSetEntity.isEmpty(extractTargetSource)) {
//            extractTargetSource.setValue(CoreConst.SYSTEM_TABLE_NAME_LABEL, CmnConst.TABLE_SYNC_MANAGER);
//            fse.setValue(CmnConst.TABLE_SYNC_MANAGER, BaseUtil.fieldSetEntityToJson(extractTargetSource));
//        }
//        for (int i = 0; i < dt.getRows(); i++) {
//            FieldSetEntity fs = dt.getFieldSetEntity(i);
//            //服务域名的端口
//            String ipPort = fs.getString("server_url");
//            String serverName = fs.getString("server_name");
//            String serverUrl = ipPort + CmnConst.SAVE_EXTRACT_URL;
//            doPost(serverUrl, serverName, fse);
//        }
//    }
//
//    /**
//     * è°ƒç”¨è¿œç¨‹ä¸»æœåŠ¡å™¨ä¿å­˜
//     *
//     * @param fse
//     * @return
//     * @throws BaseException
//     */
//    public void remoteSaveArchiveConfig(FieldSetEntity fse) throws BaseException {
//        DataTableEntity dt = getRemoteSubServer();
//        if (DataTableEntity.isEmpty(dt)) {
//            return;
//        }
//        for (int i = 0; i < dt.getRows(); i++) {
//            FieldSetEntity fs = dt.getFieldSetEntity(i);
//            //服务域名的端口
//            String ipPort = fs.getString("server_url");
//            String serverName = fs.getString("server_name");
//            String serverUrl = ipPort + CmnConst.SAVE_ARCHIVE_URL;
//            doPost(serverUrl, serverName, fse);
//        }
//    }
//
//    /**
//     * è°ƒç”¨è¿œç¨‹ä¸»æœåŠ¡å™¨ä¿å­˜
//     *
//     * @param fse
//     * @return
//     * @throws BaseException
//     */
//    public void remoteSaveCollectLog(FieldSetEntity fse) throws BaseException {
//        if ("ch-kt".equals(dataSystemName) || FieldSetEntity.isEmpty(fse)) {
//            return;
//        }
//        FieldSetEntity fs = getBaseDao().getFieldSetByFilter(CmnConst.PRODUCT_MES_SERVER, "server_type=0", null, false);
//        if (FieldSetEntity.isEmpty(fs)) {
//            throw new BaseException(ErrorCode.REMOTE_SERVER_CONFIG_EMPTY);
//        }
//        //服务域名的端口
//        String ipPort = fs.getString("server_url");
//        String serverName = fs.getString("server_name");
//        String serverUrl = ipPort + CmnConst.SAVE_COLLECT_LOG_URL;
//        doPost(serverUrl, serverName, fse);
//    }
//
//
//    /**
//     * èŽ·å–子服务配置
//     *
//     * @return
//     * @throws BaseException
//     */
//    private DataTableEntity getRemoteSubServer() throws BaseException {
//        return getBaseDao().listTable(CmnConst.PRODUCT_MES_SERVER, "server_type!=0");
//    }
//
//    //    @Async
//    void doPostAsync(String url, String serverName, FieldSetEntity fse) throws BaseException {
//        doPost(url, serverName, fse);
//    }
//
//    public String getDataSystemName() {
//        return dataSystemName;
//    }
//
//    public boolean remoteRehandle(FieldSetEntity fse) {
//        String type = fse.getString(CmnConst.TYPE);//类型
//        String logUuid;
//        if ("2".equals(type)) {
//            logUuid = fse.getString(CmnConst.PRE_STEP_UUID);
//        } else if ("1".equals(type)) {
//            logUuid = fse.getUUID();
//        } else {
//            return false;
//        }
//        FieldSetEntity fs = getBaseDao().getFieldSetByFilter(CmnConst.PRODUCT_SYS_DATA_COLLECT, "uuid =(select config_uuid FROM product_sys_data_center_log where uuid=?)", new Object[]{logUuid}, false);
//        if (!FieldSetEntity.isEmpty(fs)) {
//            //采集id
//            String collectId = fs.getString(CmnConst.ID);
//            if (collectId.indexOf(dataSystemName) == -1) {
//                fs = getBaseDao().getFieldSetByFilter(CmnConst.PRODUCT_MES_SERVER, "server_type=1 and ? like concat(server_name,'%')", new Object[]{collectId}, false);
//                if (!FieldSetEntity.isEmpty(fs)) {
//                    //服务域名的端口
//                    String ipPort = fs.getString("server_url");
//                    String serverName = fs.getString("server_name");
//                    String serverUrl = ipPort + CmnConst.REHANDLE_ERROR_URL;
//                    doPost(serverUrl, serverName, fse);
//                    //标记日志成功
//                    JournalManagerService journalManagerService = SpringUtils.getBean(JournalManagerService.class);
//                    journalManagerService.writeBackReDealResult(fse, true);
//                }
//                return true;
//            }
//        }
//        return false;
//    }
//
//    /**
//     * post è¯·æ±‚
//     *
//     * @param url
//     * @param serverName
//     * @param fse
//     * @throws BaseException
//     */
//    private FieldSetEntity doPost(String url, String serverName, FieldSetEntity fse) throws BaseException {
//        fse.setValue(CoreConst.SYSTEM_TABLE_NAME_LABEL, fse.getTableName());
//        JSONObject requestBody = BaseUtil.fieldSetEntityToJson(fse);
//        String requestData = requestBody.toJSONString();
//        //签名
//        String signature = SignUtil.getHmacSHA1(requestData, Global.getSystemConfig("signature.key", ""));
//        try (HttpResponse response = HttpRequest.post(url)
//                .contentType("application/x-www-form-urlencoded")
//                .header(CoreConst.SYSTEM_LANGUAGE_CODE_, "zh-CN") //语言编码
//                .header(CoreConst.SYSTEM_CLIENT_TYPE_, "Web") //客户端类型
//                .header(CoreConst.SYSTEM_CLIENT_VERSION_, "1.0.0") //客户端版本
//                .header("server-name", serverName) //系统名称
//                .header("signature", signature) //签名
//                .body("formData=" + requestData).execute()) {
//            if (response.getStatus() == 200) {
//                //请求成功
//                String res = response.body();
//                if (!StringUtils.isEmpty(res)) {
//                    JSONObject resBody = JSON.parseObject(res);
//                    if (resBody != null) {
//                        String code = resBody.getString(CoreConst.API_RETURN_KEY_CODE);
//                        if (!CoreConst.API_RETURN_VALUE_CODE_200.equals(code)) {
//                            //服务内部抛出的错误
//                            throw new BaseException("调用接口失败，", resBody.getString(CoreConst.API_RETURN_KEY_MSG));
//                        }
//                        String formData = resBody.getString(CoreConst.API_RETURN_KEY_DATA);
//                        if (StringUtils.isEmpty(formData)) {
//                            return null;
//                        }
//                        FieldSetEntity result = JsonUtil.pareseJsonToFieldSetEntity(formData);
//                        return result;
//                    }
//                }
//            }
//            throw new BaseException(ErrorCode.OPEN_API_REQUEST_FAIL);
//        }
//
//    }
//
//    /**
//     * èŽ·å–采集日志
//     *
//     * @return
//     */
//    public FieldSetEntity getCollectLog() {
//        collectLogCache = null;
//        if (collectLogCache == null) {
//            loadCollectLogCache();
//        }
//
//        return collectLogCache;
//    }
//
//    public void loadCollectLogCache() {
//        StringBuilder sql = new StringBuilder();
//        sql.append("\n with no_extract as ( ");
//        sql.append("\n     SELECT ");
//        sql.append("\n         a.*  ");
//        sql.append("\n     FROM ");
//        sql.append("\n         product_sys_data_center_log A ");
//        sql.append("\n         LEFT JOIN product_sys_data_center_log b ON a.uuid = b.pre_step_uuid  ");
//        sql.append("\n     WHERE ");
//        sql.append("\n         a.type = 1  ");
//        sql.append("\n         and a.result=1 and (a.deal_flag is null or a.deal_flag =0) ");
//        sql.append("\n         AND ( ");
//        sql.append("\n                     b.pre_step_uuid IS NULL  ");
//        sql.append("\n                     OR  ");
//        sql.append("\n                     ( ");
//        sql.append("\n                         b.pre_step_uuid=a.uuid  ");
//        sql.append("\n                         AND  ");
//        sql.append("\n                         (b.result=0 and (b.deal_result = 0 or b.deal_flag is null)) ");
//        sql.append("\n                     ) ");
//        sql.append("\n         ) ");
//        sql.append("\n ), ");
//        sql.append("\n last_run_log as ( ");
//        sql.append("\n select max(created_utc_datetime) last_time,config_uuid from product_sys_data_center_log where type=1 and result=1 and deal_flag=0 GROUP BY config_uuid ");
//        sql.append("\n ) ");
//        sql.append("\n      ");
//        sql.append("\n SELECT ifnull(COUNT(no_extract.uuid),0) unextracted_batch ,ifnull(sum(no_extract.count),0) unextracted_total_count,b.`name`,(select last_time from last_run_log where b.uuid=last_run_log.config_uuid) last_success_time FROM  product_sys_data_collect b  ");
//        sql.append("\n     left join no_extract on no_extract.config_uuid=b.uuid ");
//        sql.append("\n  GROUP BY b.uuid ");
//        sql.append("\n     order by b.`name` ");
//
//        FieldSetEntity fse = new FieldSetEntity();
//        fse.setTableName("temp");
//        fse.setValue("load_log_time", DateTime.now().toString());
//        DataTableEntity dataTableEntity = getBaseDao().listTable(sql.toString(), new Object[]{});
//        if (!DataTableEntity.isEmpty(dataTableEntity)) {
//            fse.setValue("list", BaseUtil.dataTableEntityToJson(dataTableEntity, f -> {
//                JSONObject jsonObject = f[0];
//                Date lastSuccessTime = jsonObject.getDate("last_success_time");
//                if (lastSuccessTime != null) {
//                    jsonObject.put("last_success_time", DateUtil.format(lastSuccessTime, "yyyy-MM-dd HH:mm:ss"));
//                }
//            }));
//        } else {
//            fse.setValue("list", "[]");
//        }
//        this.collectLogCache = fse;
//    }
//}

 product-server-data-center/src/main/java/com/product/data/center/service/ProductionRealTimeInfoService.java

@@ -41,577 +41,574 @@
public class ProductionRealTimeInfoService extends AbstractBaseService {


    public void getReportExcel(HttpServletRequest request, HttpServletResponse response) throws BaseException, IOException {
        FieldSetEntity fse = BaseUtil.getFieldSetEntity(request);
        DataTableEntity manufacturingCommandSheet = this.getManufacturingCommandSheet(fse);
        if (DataTableEntity.isEmpty(manufacturingCommandSheet)) {
            throw new BaseException(ErrorCode.NOT_EXPORT_DATA);
        }
        response.setContentType("multipart/form-data");
        response.setCharacterEncoding("utf-8");
        // è¿™é‡ŒURLEncoder.encode可以防止中文乱码 å½“然和easyexcel没有关系
        String fileName = URLEncoder.encode("制令单生产信息" + DateUtil.format(new Date(), "yyyyMMddHHmmss") + ".xlsx", "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName);
        List<ManufacturingOrderEntity> manufacturingOrderEntityList = new ArrayList<>();
        for (int i = 0; i < manufacturingCommandSheet.getRows(); i++) {
            FieldSetEntity fieldSetEntity = manufacturingCommandSheet.getFieldSetEntity(i);
            Map<Object, Object> values = fieldSetEntity.getValues();
            JSONObject jsonObject = new JSONObject((Map) values);
    public void getReportExcel(HttpServletRequest request, HttpServletResponse response) throws BaseException, IOException {
        FieldSetEntity fse = BaseUtil.getFieldSetEntity(request);
        DataTableEntity manufacturingCommandSheet = this.getManufacturingCommandSheet(fse);
        if (DataTableEntity.isEmpty(manufacturingCommandSheet)) {
            throw new BaseException(ErrorCode.NOT_EXPORT_DATA);
        }
        response.setContentType("multipart/form-data");
        response.setCharacterEncoding("utf-8");
        // è¿™é‡ŒURLEncoder.encode可以防止中文乱码 å½“然和easyexcel没有关系
        String fileName = URLEncoder.encode("制令单生产信息" + DateUtil.format(new Date(), "yyyyMMddHHmmss") + ".xlsx", "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName);
        List<ManufacturingOrderEntity> manufacturingOrderEntityList = new ArrayList<>();
        for (int i = 0; i < manufacturingCommandSheet.getRows(); i++) {
            FieldSetEntity fieldSetEntity = manufacturingCommandSheet.getFieldSetEntity(i);
            Map<Object, Object> values = fieldSetEntity.getValues();
            JSONObject jsonObject = new JSONObject((Map) values);

            ManufacturingOrderEntity manufacturingOrderEntity = jsonObject.toJavaObject(ManufacturingOrderEntity.class);
            manufacturingOrderEntityList.add(manufacturingOrderEntity);
        }
        ServletOutputStream outputStream = response.getOutputStream();
        ExcelWriter excelWriter = EasyExcel.write(outputStream, ManufacturingOrderEntity.class).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build();
        try {
            // åŽ»è°ƒç”¨å†™å…¥,这里我调用了五次，实际使用时根据数据库分页的总的页数来。这里最终会写到5个sheet里面
            // æ¯æ¬¡éƒ½è¦åˆ›å»ºwriteSheet è¿™é‡Œæ³¨æ„å¿…须指定sheetNo è€Œä¸”sheetName必须不一样
            WriteSheet writeSheet = EasyExcel.writerSheet(1, "制令单信息").build();
            // åˆ†é¡µåŽ»æ•°æ®åº“查询数据 è¿™é‡Œå¯ä»¥åŽ»æ•°æ®åº“查询每一页的数据
            excelWriter.write(manufacturingOrderEntityList, writeSheet);
            ManufacturingOrderEntity manufacturingOrderEntity = jsonObject.toJavaObject(ManufacturingOrderEntity.class);
            manufacturingOrderEntityList.add(manufacturingOrderEntity);
        }
        ServletOutputStream outputStream = response.getOutputStream();
        ExcelWriter excelWriter = EasyExcel.write(outputStream, ManufacturingOrderEntity.class).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build();
        try {
            // åŽ»è°ƒç”¨å†™å…¥,这里我调用了五次，实际使用时根据数据库分页的总的页数来。这里最终会写到5个sheet里面
            // æ¯æ¬¡éƒ½è¦åˆ›å»ºwriteSheet è¿™é‡Œæ³¨æ„å¿…须指定sheetNo è€Œä¸”sheetName必须不一样
            WriteSheet writeSheet = EasyExcel.writerSheet(1, "制令单信息").build();
            // åˆ†é¡µåŽ»æ•°æ®åº“查询数据 è¿™é‡Œå¯ä»¥åŽ»æ•°æ®åº“查询每一页的数据
            excelWriter.write(manufacturingOrderEntityList, writeSheet);
//        writeSheet = EasyExcel.writerSheet(2, "制令单信息").build();
//        excelWriter.write(manufacturingOrderEntityList, writeSheet);
//        writeSheet = EasyExcel.writerSheet(3, "制令单信息").build();
//        excelWriter.write(manufacturingOrderEntityList, writeSheet);
            String process = fse.getString("process");
            String process = fse.getString("process");

            String[] moNumbers = manufacturingCommandSheet.getData().stream().map(item -> item.getString("mo_number")).toArray(String[]::new);
            for (int i = 1; i <= moNumbers.length; i++) {
                String moNumber = moNumbers[i - 1];
                FieldSetEntity ff = new FieldSetEntity();
                ff.setTableName("temp");
                ff.setValue("process", process);
                ff.setValue("mo_number", moNumber);
                DataTableEntity processSheet = getProcessSheet(ff);
                if (DataTableEntity.isEmpty(processSheet)) {
                    continue;
                }
                fileName = moNumber + "的生产明细";
                writeSheet = EasyExcel.writerSheet(i + 1, fileName).head(ProductionDetailEntity.class).build();
                List<ProductionDetailEntity> productionDetailEntities = new ArrayList<>();
                for (int k = 0; k < processSheet.getRows(); k++) {
                    JSONObject jsonObject = new JSONObject((Map) processSheet.getFieldSetEntity(k).getValues());
                    ProductionDetailEntity productionDetail = jsonObject.toJavaObject(ProductionDetailEntity.class);
                    productionDetailEntities.add(productionDetail);
                }
                excelWriter.write(productionDetailEntities, writeSheet);
            }
        } catch (Exception e) {
            throw e;
        } finally {
            excelWriter.finish();
            outputStream.flush();
            outputStream.close();
        }
    }
            String[] moNumbers = manufacturingCommandSheet.getData().stream().map(item -> item.getString("mo_number")).toArray(String[]::new);
            for (int i = 1; i <= moNumbers.length; i++) {
                String moNumber = moNumbers[i - 1];
                FieldSetEntity ff = new FieldSetEntity();
                ff.setTableName("temp");
                ff.setValue("process", process);
                ff.setValue("mo_number", moNumber);
                DataTableEntity processSheet = getProcessSheet(ff);
                if (DataTableEntity.isEmpty(processSheet)) {
                    continue;
                }
                fileName = moNumber + "的生产明细";
                writeSheet = EasyExcel.writerSheet(i + 1, fileName).head(ProductionDetailEntity.class).build();
                List<ProductionDetailEntity> productionDetailEntities = new ArrayList<>();
                for (int k = 0; k < processSheet.getRows(); k++) {
                    JSONObject jsonObject = new JSONObject((Map) processSheet.getFieldSetEntity(k).getValues());
                    ProductionDetailEntity productionDetail = jsonObject.toJavaObject(ProductionDetailEntity.class);
                    productionDetailEntities.add(productionDetail);
                }
                excelWriter.write(productionDetailEntities, writeSheet);
            }
        } catch (Exception e) {
            throw e;
        } finally {
            excelWriter.finish();
            outputStream.flush();
            outputStream.close();
        }
    }

    /**
     * å·¥åºåˆ—表获取
     *
     * @return
     */
    public DataTableEntity getProcessList() {
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT ");
        sql.append(" group_next ");
        sql.append(" FROM da_t_co_route_control where state_flag <> '2' AND group_next <> '0' AND isrotegroup = 'Y' ");
        sql.append(" GROUP BY group_next ");
        return getBaseDao().listTable(sql.toString(), new Object[]{});
    }
    /**
     * å·¥åºåˆ—表获取
     *
     * @return
     */
    public DataTableEntity getProcessList() {
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT ");
        sql.append(" group_next ");
        sql.append(" FROM da_t_co_route_control where state_flag <> '2' AND group_next <> '0' AND isrotegroup = 'Y' ");
        sql.append(" GROUP BY group_next ");
        return getBaseDao().listTable(sql.toString(), new Object[]{});
    }

    // T_CO_ROUTE_CONTROL T_PM_MO_BASE T_CO_CUSTOMER T_CO_AREA T_PM_PROJECT_BASE T_CO_ITEM T_WIP_STATION_REC
    //   T_PM_PRODUCT T_WIP_TRACKING T_CO_EMP_DESC  T_SMO_TECHNICS
    //  T_WIP_ERROR_SCRAP T_WIP_DETAIL
    // T_CO_ROUTE_CONTROL T_PM_MO_BASE T_CO_CUSTOMER T_CO_AREA T_PM_PROJECT_BASE T_CO_ITEM T_WIP_STATION_REC
    //   T_PM_PRODUCT T_WIP_TRACKING T_CO_EMP_DESC  T_SMO_TECHNICS
    //  T_WIP_ERROR_SCRAP T_WIP_DETAIL

    public void test() {
        DataBaseEntity dataBaseEntity = new DataBaseEntity("ef7b1348-b2ec-40d1-a0b8-76cd29b99a1232d");
        Dao dao = dataBaseEntity.getDao();
        double ceil = Math.ceil(48548455 / 500);
        try {
            Connection connection = dao.getConnection();
            PreparedStatement pst = connection.prepareStatement("\n" +
                    "\n" +
                    " update T_PM_PRODUCT_SN SET CREATE_DATE=to_date((select '20'||(select trunc(dbms_random.value(17,22)) from dual)||'-'||(select trunc(dbms_random.value(1,10)) from dual)||'-'||(select trunc(dbms_random.value(1,28)) from dual)||' 00:00:00'\n" +
                    "\t  from dual),'yyyy-mm-dd hh24:mi:ss')\n" +
                    "\t where product_sn in (\n" +
                    "\t\tSELECT PRODUCT_SN from T_PM_PRODUCT_SN where CREATE_DATE=TO_DATE('2022-11-17 16:30:28','yyyy-mm-dd hh24:mi:ss') and rownum<=500\n" +
                    "\t )");
            for (int i = 0; i < ceil; i++) {
    public void test() {
        DataBaseEntity dataBaseEntity = new DataBaseEntity("ef7b1348-b2ec-40d1-a0b8-76cd29b99a1232d");
        Dao dao = dataBaseEntity.getDao();
        double ceil = Math.ceil(48548455 / 500);
        try {
            Connection connection = dao.getConnection();
            PreparedStatement pst = connection.prepareStatement("\n" +
                    "\n" +
                    " update T_PM_PRODUCT_SN SET CREATE_DATE=to_date((select '20'||(select trunc(dbms_random.value(17,22)) from dual)||'-'||(select trunc(dbms_random.value(1,10)) from dual)||'-'||(select trunc(dbms_random.value(1,28)) from dual)||' 00:00:00'\n" +
                    "\t  from dual),'yyyy-mm-dd hh24:mi:ss')\n" +
                    "\t where product_sn in (\n" +
                    "\t\tSELECT PRODUCT_SN from T_PM_PRODUCT_SN where CREATE_DATE=TO_DATE('2022-11-17 16:30:28','yyyy-mm-dd hh24:mi:ss') and rownum<=500\n" +
                    "\t )");
            for (int i = 0; i < ceil; i++) {

                pst.execute();
                System.out.println("剩余--" + (ceil - i + 1));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
                pst.execute();
                System.out.println("剩余--" + (ceil - i + 1));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * èŽ·å–制令单信息
     *
     * @param fse
     * @return
     * @throws BaseException
     */
    public DataTableEntity getManufacturingCommandSheet(FieldSetEntity fse) throws BaseException {
        Integer cpage = fse.getInteger("cpage");
        if (cpage == null || cpage <= 0) {
            cpage = 1;
        }
        Integer pageSize = fse.getInteger("page_size");
        if (pageSize == null || pageSize <= 0) {
            pageSize = Integer.MAX_VALUE;
        }
        //近期
        String queryTime = fse.getString("query_time");
        String timeValue = fse.getString("time_value");
        String areaName = fse.getString("areaname");
        String productMaterial = fse.getString("product_material");
        String moNumber = fse.getString("mo_number");
    /**
     * èŽ·å–制令单信息
     *
     * @param fse
     * @return
     * @throws BaseException
     */
    public DataTableEntity getManufacturingCommandSheet(FieldSetEntity fse) throws BaseException {
        Integer cpage = fse.getInteger("cpage");
        if (cpage == null || cpage <= 0) {
            cpage = 1;
        }
        Integer pageSize = fse.getInteger("page_size");
        if (pageSize == null || pageSize <= 0) {
            pageSize = Integer.MAX_VALUE;
        }
        //近期
        String queryTime = fse.getString("query_time");
        String timeValue = fse.getString("time_value");
        String areaName = fse.getString("areaname");
        String productMaterial = fse.getString("product_material");
        String moNumber = fse.getString("mo_number");
//        String projectId = fse.getString("project_id");
        String closeFlag = fse.getString("close_flag");
        String timeFilter = fse.getString("time_filter");
        String timeField = fse.getString("timeField");
        if (StringUtils.isAllEmpty(areaName, productMaterial, moNumber, productMaterial)
                && ((!"1".equals(timeFilter) || StringUtils.isEmpty(timeValue)) && !"1".equals(queryTime))) {
        String closeFlag = fse.getString("close_flag");
        String timeFilter = fse.getString("time_filter");
        String timeField = fse.getString("timeField");
        if (StringUtils.isAllEmpty(areaName, productMaterial, moNumber, productMaterial)
                && ((!"1".equals(timeFilter) || StringUtils.isEmpty(timeValue)) && !"1".equals(queryTime))) {

            throw new BaseException(ErrorCode.REPORT_SELECT_TIME_FILTER_CANNOT_EMPTY);
        }
        StringBuilder sql = new StringBuilder(128);
        sql.append("\n select distinct ");
        sql.append("\n p.project_id as project_name , ");//--工单号,
        sql.append("\n p.destroy_no , ");//--订单号,
        sql.append("\n p.product_material , ");//--机种,
        sql.append("\n m.co_item_name as product_name , ");//--品名,
        sql.append("\n m.co_item_spec as product_standard , ");//--规格,
            throw new BaseException(ErrorCode.REPORT_SELECT_TIME_FILTER_CANNOT_EMPTY);
        }
        StringBuilder sql = new StringBuilder(128);
        sql.append("\n select distinct ");
        sql.append("\n p.project_id as project_name , ");//--工单号,
        sql.append("\n p.destroy_no , ");//--订单号,
        sql.append("\n p.product_material , ");//--机种,
        sql.append("\n m.co_item_name as product_name , ");//--品名,
        sql.append("\n m.co_item_spec as product_standard , ");//--规格,
//        sql.append("\n c.customer , ");//--客户名称,
        sql.append("\n ifnull(p.product_count,0) product_count ,");//--工单数量,
        sql.append("\n t.mo_number , ");//--制令单号,
        sql.append("\n case ");
        sql.append("\n when process_face = 'a' then '单面' ");
        sql.append("\n when process_face = 's' then '正面' ");
        sql.append("\n when process_face = 'c' then '反面' ");
        sql.append("\n else '' end process_face , ");//--加工面,
        sql.append("\n case ");
        sql.append("\n when master_flag='Y' then '是' ");
        sql.append("\n else '否' end  master_flag , ");//--产出标识,
        sql.append("\n a.areaname , ");//--区域名称,
        sql.append("\n t.target_qty , ");//--目标数量,
        sql.append("\n t.input_qty ,");//--投入数量,
        sql.append("\n t.output_qty , ");//--产出数量,
        sql.append("\n t.input_qty-t.output_qty wip_qty , ");//--在制品数量,
        sql.append("\n t.total_scrap_qty , ");//--作废数量,
        sql.append("\n ifnull(t.tp_number,0) tp_number , ");//--单片点数,
        sql.append("\n ifnull(t.output_qty *t.tp_number,0) totoaltp_number,  ");//--总点数,
        sql.append("\n t.mo_create_date , ");//--开立时间,
        sql.append("\n t.mo_start_date , ");//--实际投入时间,
        sql.append("\n t.mo_due_date , ");//--预关结时间,
        sql.append("\n t.mo_close_date  ");//--关结时间
        sql.append("\n from ");
        sql.append("\n [={da_t_pm_mo_base}{areaid,project_id,master_flag,process_face,target_qty,input_qty,output_qty,total_scrap_qty");
        sql.append(",tp_number,mo_create_date,mo_start_date,mo_due_date,mo_close_date");
        sql.append(",close_flag,mo_number}");
        boolean isF = false;
        if ("1".equals(queryTime) && (StringUtils.isEmpty(timeValue) || !"1".equals(timeFilter))) {
            //没有时间条件且勾选了只查看近两年的数据
//            DateTime offset = DateUtil.offset(new Date(), DateField.YEAR, -2);
//            sql.append(" {where  ( mo_create_date >= str_to_date('" + DateUtil.format(offset, "yyyy-MM-dd") + "','%Y-%m-%d') )}");
//            filterParams.add(DateUtil.format(offset, "yyyy-MM-dd"));
        } else if (!StringUtils.isEmpty(timeValue) && "1".equals(timeFilter)) {
            //有时间条件根据用户选择的时间过滤
            String[] times = timeValue.split(",");
            String startTime = times[0];
            String endTime = times[1];
            sql.append("  {WHERE");
            if ("production_time".equals(timeField)) {
                //特殊： ç”Ÿäº§æ—¶é—´
                sql.append(" (mo_number in (SELECT mo_number from [={da_t_wip_station_rec}{mo_number}");
                sql.append("{where create_date >=str_to_date('" + startTime + "','%Y-%m-%d %H:%i:%s') and create_date<=str_to_date('" + endTime + "','%Y-%m-%d %H:%i:%s')}=] ");
                sql.append("  ) ");
            } else {
                //时间条件
                sql.append("\n (").append(timeField).append(">= str_to_date('" + startTime + "','%Y-%m-%d %H:%i:%s')");
                sql.append("\n and ").append(timeField).append("<=str_to_date('" + endTime + "','%Y-%m-%d %H:%i:%s') )");
            }
            sql.append("}");
        }
        sql.append("=] t ");
        sql.append("\n ifnull(p.product_count,0) product_count ,");//--工单数量,
        sql.append("\n t.mo_number , ");//--制令单号,
        sql.append("\n case ");
        sql.append("\n when process_face = 'a' then '单面' ");
        sql.append("\n when process_face = 's' then '正面' ");
        sql.append("\n when process_face = 'c' then '反面' ");
        sql.append("\n else '' end process_face , ");//--加工面,
        sql.append("\n case ");
        sql.append("\n when master_flag='Y' then '是' ");
        sql.append("\n else '否' end  master_flag , ");//--产出标识,
        sql.append("\n a.areaname , ");//--区域名称,
        sql.append("\n t.target_qty , ");//--目标数量,
        sql.append("\n t.input_qty ,");//--投入数量,
        sql.append("\n t.output_qty , ");//--产出数量,
        sql.append("\n t.input_qty-t.output_qty wip_qty , ");//--在制品数量,
        sql.append("\n t.total_scrap_qty , ");//--作废数量,
        sql.append("\n ifnull(t.tp_number,0) tp_number , ");//--单片点数,
        sql.append("\n ifnull(t.output_qty *t.tp_number,0) totoaltp_number,  ");//--总点数,
        sql.append("\n t.mo_create_date , ");//--开立时间,
        sql.append("\n t.mo_start_date , ");//--实际投入时间,
        sql.append("\n t.mo_due_date , ");//--预关结时间,
        sql.append("\n t.mo_close_date  ");//--关结时间
        sql.append("\n from ");
        sql.append("\n [={da_t_pm_mo_base}{areaid,project_id,master_flag,process_face,target_qty,input_qty,output_qty,total_scrap_qty");
        sql.append(",tp_number,mo_create_date,mo_start_date,mo_due_date,mo_close_date");
        sql.append(",close_flag,mo_number}");
        boolean isF = false;
//        if ("1".equals(queryTime) && (StringUtils.isEmpty(timeValue) || !"1".equals(timeFilter))) {
//            //没有时间条件且勾选了只查看近两年的数据
////            DateTime offset = DateUtil.offset(new Date(), DateField.YEAR, -2);
////            sql.append(" {where  ( mo_create_date >= str_to_date('" + DateUtil.format(offset, "yyyy-MM-dd") + "','%Y-%m-%d') )}");
////            filterParams.add(DateUtil.format(offset, "yyyy-MM-dd"));
//        } else
        if (!StringUtils.isEmpty(timeValue) && "1".equals(timeFilter)) {
            //有时间条件根据用户选择的时间过滤
            String[] times = timeValue.split(",");
            String startTime = times[0];
            String endTime = times[1];
//                sql.append(" (mo_number in (SELECT mo_number from [={da_t_wip_station_rec}{mo_number}");
//                sql.append("{where create_date >=str_to_date('" + startTime + "','%Y-%m-%d %H:%i:%s') and create_date<=str_to_date('" + endTime + "','%Y-%m-%d %H:%i:%s')}=] ");
//                sql.append("  ) ");
            //时间条件
            if (!"production_time".equals(timeField)) {
                sql.append("  {WHERE");
                sql.append("\n (").append(timeField).append(">= str_to_date('" + startTime + "','%Y-%m-%d %H:%i:%s')");
                sql.append("\n and ").append(timeField).append("<=str_to_date('" + endTime + "','%Y-%m-%d %H:%i:%s') )");

            }
            sql.append("}=] t");
            if ("production_time".equals(timeField)) {
                //特殊： ç”Ÿäº§æ—¶é—´
                sql.append(" JOIN  [={da_t_wip_station_rec}{mo_number}{where  create_date >=str_to_date('" + startTime + "','%Y-%m-%d %H:%i:%s') and create_date<=str_to_date('" + endTime + "','%Y-%m-%d %H:%i:%s')}=] rec ON rec.mo_number = t.mo_number ");
            }
        } else {
            sql.append("=] t ");
        }

//        sql.append("\n left join [=da_t_co_customer=] c on t.cust_code = c.cust_code ");
        sql.append("\n left join da_t_co_area  a on t.areaid = a.areaid ");
        sql.append("\n left join [={da_t_pm_project_base}{project_base_id,product_material");
        sql.append(",project_id,destroy_no,product_count");
        sql.append("}=] p on p.project_base_id = t.project_id ");
        sql.append("\n left join [={da_t_co_item}{co_item_code,co_item_name,co_item_spec}=] m on p.product_material = m.co_item_code ");
        sql.append("\n left join da_t_co_area  a on t.areaid = a.areaid ");
        sql.append("\n left join [={da_t_pm_project_base}{project_base_id,product_material");
        sql.append(",project_id,destroy_no,product_count");
        sql.append("}=] p on p.project_base_id = t.project_id ");
        sql.append("\n left join [={da_t_co_item}{co_item_code,co_item_name,co_item_spec}=] m on p.product_material = m.co_item_code ");
        boolean isFilter = false;
        StringBuilder sqlFilter = new StringBuilder();
        List<Object> filterParams = new ArrayList<>();

        boolean isFilter = false;
        StringBuilder sqlFilter = new StringBuilder();
        List<Object> filterParams = new ArrayList<>();
        isFilter = appendLikeFilter(areaName, "a.areaname", sqlFilter, filterParams, isFilter);
        isFilter = appendLikeFilter(productMaterial, "p.product_material", false, sqlFilter, filterParams, isFilter);
        isFilter = appendLikeFilter(moNumber, "t.mo_number", sqlFilter, filterParams, isFilter);
        if (!"-1".equals(closeFlag)) {
            isFilter = appendLikeFilter(closeFlag, "t.close_flag", false, sqlFilter, filterParams, isFilter);
        }

        isFilter = appendLikeFilter(areaName, "a.areaname", sqlFilter, filterParams, isFilter);
        isFilter = appendLikeFilter(productMaterial, "p.product_material", false, sqlFilter, filterParams, isFilter);
        isFilter = appendLikeFilter(moNumber, "t.mo_number", sqlFilter, filterParams, isFilter);
        if (!"-1".equals(closeFlag)) {
            isFilter = appendLikeFilter(closeFlag, "t.close_flag", false, sqlFilter, filterParams, isFilter);
        }
        if (isFilter) {
            sql.append(" where (").append(sqlFilter).append(") ");
        }
        sql.append(" order by mo_create_date");
        System.out.println(sql);
        String parseSql = QuerySqlParseUtil.parseSplitTableSql(getBaseDao(), sql.toString(), 2);
        DataTableEntity dataTableEntity = getBaseDao().listTable(parseSql, filterParams.toArray(), pageSize, cpage);

        if (isFilter) {
            sql.append(" where (").append(sqlFilter).append(") ");
        }
        sql.append(" order by mo_create_date");
        String parseSql = QuerySqlParseUtil.parseSplitTableSql(getBaseDao(), sql.toString(), 2);
        DataTableEntity dataTableEntity = getBaseDao().listTable(parseSql, filterParams.toArray(), pageSize, cpage);
        return dataTableEntity;
    }

        return dataTableEntity;
    }
    public DataTableEntity getProductSheet(FieldSetEntity fse) throws BaseException {
        String trackingTable = "da_t_wip_tracking";
        String groupNext = fse.getString("group_next");
        String moNumber = fse.getString("mo_number");
        StringBuilder sql = new StringBuilder(64);
        sql.append("select number_type from [={" + trackingTable + "}{number_type,mo_number}{ where mo_number='" + moNumber + "' and number_type!=0}=] ");
        String parseSql = QuerySqlParseUtil.parseSplitTableSql(getBaseDao(), sql.toString());
        FieldSetEntity fs = getBaseDao().getFieldSetEntityBySQL(parseSql, new Object[]{}, false);
        if (fs != null) {
            List<Object> params = new ArrayList<>();

    public DataTableEntity getProductSheet(FieldSetEntity fse) throws BaseException {
        String bak = fse.getString("~bak~");
        String trackingTable = "da_t_wip_tracking";
        if ("1".equals(bak)) {
            trackingTable = "da_t_wip_tracking_bak";
        }
        String groupNext = fse.getString("group_next");
        String moNumber = fse.getString("mo_number");
        StringBuilder sql = new StringBuilder(64);
        sql.append("select number_type from [={" + trackingTable + "}{number_type,mo_number}{ where mo_number='" + moNumber + "' and number_type!=0}=] ");
        String parseSql = QuerySqlParseUtil.parseSplitTableSql(getBaseDao(), sql.toString());
        FieldSetEntity fs = getBaseDao().getFieldSetEntityBySQL(parseSql, new Object[]{}, false);
        if (fs != null) {
            List<Object> params = new ArrayList<>();
            Integer numberType = NumberUtil.isNumber(fs.getString("number_type")) ? NumberUtil.parseInt(fs.getString("number_type")) : 0;
            if (numberType == null || numberType < 1) {
                return null;
            }
            sql.setLength(0);

            Integer numberType = NumberUtil.isNumber(fs.getString("number_type")) ? NumberUtil.parseInt(fs.getString("number_type")) : 0;
            if (numberType == null || numberType < 1) {
                return null;
            }
            sql.setLength(0);

            String scrap = "报废";
            String lend = "借出";
            if (1 == numberType && !"流程结束".equals(groupNext)) {
                sql.append("\n select t.lot_number,t.in_station_time,t.serial_number, ");
                sql.append("\n (case t.group_name when 'n/a' then ' ' else ");
                sql.append("\n (case t.error_flag when '0' then 'ok' when '1' then 'ng' when '3' then ? when '4' then ? else 'ng' end ) end) error_flag_text, ");
                sql.append("\n t.error_flag, ");
                sql.append("\n t.mo_number,t.group_name,t.item_count,t.repair,e.emp_name,t.container_sn, ");
                sql.append("\n (select sum(count) from [={da_t_pm_product_sn}{COUNT(1) count,product_sn }{GROUP BY product_sn}=] where product_sn=t.serial_number) product_count, ");
                sql.append("\n t.wait_scrap_qty,t.depanel_flag ");
                sql.append("\n from [={" + trackingTable + " d1}{emp_no,number_type,mo_number,display_group,item_count,routeendflage,depanel_flag,wait_scrap_qty");
                sql.append("lot_number,in_station_time,serial_number,group_name,error_flag,group_name,repair,container_sn,in_station_time");
                sql.append("}{where mo_number='" + moNumber + "' and t.number_type='" + numberType + "' and display_group='" + groupNext + "' and t.item_count!=0 and routeendflage<>'Y'}=] t ");
                sql.append("\n left join da_t_co_emp_desc e ");
                sql.append("\n on t.emp_no=e.emp_no ");
            String scrap = "报废";
            String lend = "借出";
            if (1 == numberType && !"流程结束".equals(groupNext)) {
                sql.append("\n select t.lot_number,t.in_station_time,t.serial_number, ");
                sql.append("\n (case t.group_name when 'n/a' then ' ' else ");
                sql.append("\n (case t.error_flag when '0' then 'ok' when '1' then 'ng' when '3' then ? when '4' then ? else 'ng' end ) end) error_flag_text, ");
                sql.append("\n t.error_flag, ");
                sql.append("\n t.mo_number,t.group_name,t.item_count,t.repair,e.emp_name,t.container_sn, ");
                sql.append("\n (select sum(count) from [={da_t_pm_product_sn}{COUNT(1) count,product_sn }{GROUP BY product_sn}=] where product_sn=t.serial_number) product_count, ");
                sql.append("\n t.wait_scrap_qty,t.depanel_flag ");
                sql.append("\n from [={" + trackingTable + " d1}{emp_no,number_type,mo_number,display_group,item_count,routeendflage,depanel_flag,wait_scrap_qty");
                sql.append("lot_number,in_station_time,serial_number,group_name,error_flag,group_name,repair,container_sn,in_station_time");
                sql.append("}{where mo_number='" + moNumber + "' and t.number_type='" + numberType + "' and display_group='" + groupNext + "' and t.item_count!=0 and routeendflage<>'Y'}=] t ");
                sql.append("\n left join da_t_co_emp_desc e ");
                sql.append("\n on t.emp_no=e.emp_no ");
//                sql.append("\n where t.number_type=? and t.mo_number=? and t.display_group=? ");
//                sql.append("\n and t.item_count!=0 and routeendflage<>'Y' ");
                sql.append("\n union all ");
                sql.append("\n select t.lot_number,t.in_station_time,t.serial_number, ");
                sql.append("\n (case t.group_name when 'n/a' then ' ' else ");
                sql.append("\n (case t.error_flag when '0' then 'ok' when '1' then 'ng' when '3' then ? when '4' then ? else 'ng' end ) end) error_flag_text, ");
                sql.append("\n t.error_flag, ");
                sql.append("\n t.mo_number,t.group_name,t.item_count,t.repair,e.emp_name,t.container_sn, ");
                sql.append("\n (select sum(count) from [={da_t_pm_product_sn}{COUNT(1) count,product_sn }{GROUP BY product_sn}=] where product_sn=t.serial_number) product_count, ");
                sql.append("\n t.wait_scrap_qty,t.depanel_flag ");
                sql.append("\n from [={" + trackingTable + " d2}{");
                sql.append("lot_number,in_station_time,serial_number,group_name,error_flag,group_name,repair,container_sn");
                sql.append(",number_type,mo_number,item_count,routeendflage,in_station_time,depanel_flag,wait_scrap_qty,serial_number,emp_name}");
                sql.append("{where number_type='" + numberType + "' and mo_number='" + moNumber + "' and group_name='" + groupNext + "' ");
                sql.append(" and item_count!=0 and routeendflage<>'Y' and error_flag='1'} ");
                sql.append("=] t ");
                sql.append("\n left join da_t_co_emp_desc e ");
                sql.append("\n on t.emp_no=e.emp_no ");
                sql.append("\n order by error_flag asc,in_station_time asc ");
            } else if (2 == numberType && "流程结束".equals(groupNext)) {
                sql.append("\n select t.lot_number,t.in_station_time,t.serial_number, ");
                sql.append("\n (case t.group_name when 'n/a' then ' ' else ");
                sql.append("\n (case t.error_flag when '0' then 'ok' when '1' then 'ng' when '3' then ? when '4' then ? else 'ng' end ) end) error_flag_text, ");
                sql.append("\n t.error_flag, ");
                sql.append("\n t.mo_number,t.group_name,t.item_count,t.repair,e.emp_name,t.container_sn, ");
                sql.append("\n ifnull(c.product_count,0) product_count, ");
                sql.append("\n t.wait_scrap_qty,t.depanel_flag ");
                sql.append("\n from [={" + trackingTable + "}");
                sql.append(" {lot_number,in_station_time,serial_number,group_name,error_flag,mo_number,group_name,item_count,repair");
                sql.append(",container_sn,serial_number,wait_scrap_qty,depanel_flag,number_type,emp_no}");
                sql.append("\n union all ");
                sql.append("\n select t.lot_number,t.in_station_time,t.serial_number, ");
                sql.append("\n (case t.group_name when 'n/a' then ' ' else ");
                sql.append("\n (case t.error_flag when '0' then 'ok' when '1' then 'ng' when '3' then ? when '4' then ? else 'ng' end ) end) error_flag_text, ");
                sql.append("\n t.error_flag, ");
                sql.append("\n t.mo_number,t.group_name,t.item_count,t.repair,e.emp_name,t.container_sn, ");
                sql.append("\n (select sum(count) from [={da_t_pm_product_sn}{COUNT(1) count,product_sn }{GROUP BY product_sn}=] where product_sn=t.serial_number) product_count, ");
                sql.append("\n t.wait_scrap_qty,t.depanel_flag ");
                sql.append("\n from [={" + trackingTable + " d2}{");
                sql.append("lot_number,in_station_time,serial_number,group_name,error_flag,group_name,repair,container_sn");
                sql.append(",number_type,mo_number,item_count,routeendflage,in_station_time,depanel_flag,wait_scrap_qty,serial_number,emp_name}");
                sql.append("{where number_type='" + numberType + "' and mo_number='" + moNumber + "' and group_name='" + groupNext + "' ");
                sql.append(" and item_count!=0 and routeendflage<>'Y' and error_flag='1'} ");
                sql.append("=] t ");
                sql.append("\n left join da_t_co_emp_desc e ");
                sql.append("\n on t.emp_no=e.emp_no ");
                sql.append("\n order by error_flag asc,in_station_time asc ");
            } else if (2 == numberType && "流程结束".equals(groupNext)) {
                sql.append("\n select t.lot_number,t.in_station_time,t.serial_number, ");
                sql.append("\n (case t.group_name when 'n/a' then ' ' else ");
                sql.append("\n (case t.error_flag when '0' then 'ok' when '1' then 'ng' when '3' then ? when '4' then ? else 'ng' end ) end) error_flag_text, ");
                sql.append("\n t.error_flag, ");
                sql.append("\n t.mo_number,t.group_name,t.item_count,t.repair,e.emp_name,t.container_sn, ");
                sql.append("\n ifnull(c.product_count,0) product_count, ");
                sql.append("\n t.wait_scrap_qty,t.depanel_flag ");
                sql.append("\n from [={" + trackingTable + "}");
                sql.append(" {lot_number,in_station_time,serial_number,group_name,error_flag,mo_number,group_name,item_count,repair");
                sql.append(",container_sn,serial_number,wait_scrap_qty,depanel_flag,number_type,emp_no}");

                sql.append("\n  {where number_type='" + numberType + "'  and mo_number='" + moNumber + "' and routeendflage='Y' ");
                sql.append("}=] t ");
                sql.append("\n left join ");
                sql.append("\n da_t_co_emp_desc e ");
                sql.append("\n on t.emp_no=e.emp_no ");
                sql.append("\n LEFT JOIN ");
                sql.append("\n(SELECT SUM(COUNT) product_count,product_sn  FROM [={da_t_pm_product_sn}{COUNT(1) count,product_sn}{WHERE product_sn in (select serial_number from " + trackingTable + ") group by product_sn }=] group by product_sn  ) C ");
                sql.append("\n ON t.serial_number=c.product_sn ");
                sql.append("\n order by error_flag asc,in_station_time asc ");
                sql.append("\n  {where number_type='" + numberType + "'  and mo_number='" + moNumber + "' and routeendflage='Y' ");
                sql.append("}=] t ");
                sql.append("\n left join ");
                sql.append("\n da_t_co_emp_desc e ");
                sql.append("\n on t.emp_no=e.emp_no ");
                sql.append("\n LEFT JOIN ");
                sql.append("\n(SELECT SUM(COUNT) product_count,product_sn  FROM [={da_t_pm_product_sn}{COUNT(1) count,product_sn}{WHERE product_sn in (select serial_number from " + trackingTable + ") group by product_sn }=] group by product_sn  ) C ");
                sql.append("\n ON t.serial_number=c.product_sn ");
                sql.append("\n order by error_flag asc,in_station_time asc ");

            } else if (2 == numberType && !"流程结束".equals(groupNext)) {
                sql.append("\n select t.serial_number,t.in_station_time, ");
                sql.append("\n (case t.group_name when 'n/a' then ' ' else ");
                sql.append("\n (case t.error_flag when '0' then 'ok' when '1' then 'ng' when '3' then ? when '4' then ? else 'ng' end ) end) error_flag_text, ");
                sql.append("\n t.error_flag, ");
                sql.append("\n t.mo_number,t.group_name,t.item_count,t.repair,e.emp_name,t.container_sn, ");
                sql.append("\n t.wait_scrap_qty,t.depanel_flag ");
                sql.append("\n from [={" + trackingTable + " d1}{serial_number,in_station_time,group_name,error_flag,mo_number,item_count,repair,container_sn,wait_scrap_qty,depanel_flag");
                sql.append(",emp_no,number_type,display_group,routeendflage}");
                sql.append("{ WHERE number_type='" + numberType + "' and display_group='" + groupNext + "' and  mo_number='" + moNumber + "' and  routeendflage<>'Y' }");
                sql.append("=] t ");
                sql.append("\n left join ");
                sql.append("\n da_t_co_emp_desc e ");
                sql.append("\n on t.emp_no=e.emp_no ");
            } else if (2 == numberType && !"流程结束".equals(groupNext)) {
                sql.append("\n select t.serial_number,t.in_station_time, ");
                sql.append("\n (case t.group_name when 'n/a' then ' ' else ");
                sql.append("\n (case t.error_flag when '0' then 'ok' when '1' then 'ng' when '3' then ? when '4' then ? else 'ng' end ) end) error_flag_text, ");
                sql.append("\n t.error_flag, ");
                sql.append("\n t.mo_number,t.group_name,t.item_count,t.repair,e.emp_name,t.container_sn, ");
                sql.append("\n t.wait_scrap_qty,t.depanel_flag ");
                sql.append("\n from [={" + trackingTable + " d1}{serial_number,in_station_time,group_name,error_flag,mo_number,item_count,repair,container_sn,wait_scrap_qty,depanel_flag");
                sql.append(",emp_no,number_type,display_group,routeendflage}");
                sql.append("{ WHERE number_type='" + numberType + "' and display_group='" + groupNext + "' and  mo_number='" + moNumber + "' and  routeendflage<>'Y' }");
                sql.append("=] t ");
                sql.append("\n left join ");
                sql.append("\n da_t_co_emp_desc e ");
                sql.append("\n on t.emp_no=e.emp_no ");
//                sql.append("\n where t.number_type=? ");
//                sql.append("\n and t.mo_number=? and t.display_group=? ");
//                sql.append("\n and routeendflage<>'Y' ");
                sql.append("\n union all ");
                sql.append("\n select t.serial_number,t.in_station_time, ");
                sql.append("\n (case t.group_name when 'n/a' then ' ' else ");
                sql.append("\n (case t.error_flag when '0' then 'ok' when '1' then 'ng' when '3' then ? when '4' then ? else 'ng' end ) end) error_flag_text, ");
                sql.append("\n t.error_flag, ");
                sql.append("\n t.mo_number,t.group_name,t.item_count,t.repair,e.emp_name,t.container_sn, ");
                sql.append("\n t.wait_scrap_qty,t.depanel_flag ");
                sql.append("\n from [={" + trackingTable + " d2}{serial_number,in_station_time,group_name,error_flag,mo_number,item_count,repair,container_sn,wait_scrap_qty,depanel_flag");
                sql.append(",emp_no,number_type,display_group,routeendflage}{ where  number_type='" + numberType + "' and  mo_number='" + moNumber + "' and group_name='" + groupNext + "'");
                sql.append(" and routeendflage<>'Y' and error_flag='1'}");
                sql.append("=] t");
                sql.append("\n left join ");
                sql.append("\n da_t_co_emp_desc e ");
                sql.append("\n on t.emp_no=e.emp_no ");
                sql.append("\n union all ");
                sql.append("\n select t.serial_number,t.in_station_time, ");
                sql.append("\n (case t.group_name when 'n/a' then ' ' else ");
                sql.append("\n (case t.error_flag when '0' then 'ok' when '1' then 'ng' when '3' then ? when '4' then ? else 'ng' end ) end) error_flag_text, ");
                sql.append("\n t.error_flag, ");
                sql.append("\n t.mo_number,t.group_name,t.item_count,t.repair,e.emp_name,t.container_sn, ");
                sql.append("\n t.wait_scrap_qty,t.depanel_flag ");
                sql.append("\n from [={" + trackingTable + " d2}{serial_number,in_station_time,group_name,error_flag,mo_number,item_count,repair,container_sn,wait_scrap_qty,depanel_flag");
                sql.append(",emp_no,number_type,display_group,routeendflage}{ where  number_type='" + numberType + "' and  mo_number='" + moNumber + "' and group_name='" + groupNext + "'");
                sql.append(" and routeendflage<>'Y' and error_flag='1'}");
                sql.append("=] t");
                sql.append("\n left join ");
                sql.append("\n da_t_co_emp_desc e ");
                sql.append("\n on t.emp_no=e.emp_no ");
//                sql.append("\n where t.number_type=? ");
//                sql.append("\n and t.mo_number=? and t.group_name=? ");
//                sql.append("\n and routeendflage<>'Y' and error_flag='1' ");
                sql.append("\n order by error_flag asc,in_station_time asc ");
                sql.append("\n order by error_flag asc,in_station_time asc ");

            } else if (1 == numberType && "流程结束".equals(groupNext)) {
                sql.append("\n select t.serial_number,t.in_station_time, ");
                sql.append("\n (case t.group_name when 'n/a' then ' ' else ");
                sql.append("\n (case t.error_flag when '0' then 'ok' when '1' then 'ng' when '3' then ? when '4' then ? else 'ng' end ) end) error_flag_text, ");
                sql.append("\n t.error_flag, ");
                sql.append("\n t.mo_number,t.group_name,t.item_count,t.repair,e.emp_name,t.container_sn, ");
                sql.append("\n t.wait_scrap_qty,t.depanel_flag ");
                sql.append("\n from [={" + trackingTable + "}");
                sql.append("{serial_number,in_station_time,group_name,error_flag,mo_number,item_count,repair,emp_name,container_sn,");
                sql.append("wait_scrap_qty,depanel_flag,emp_no,number_type}{where number_type='" + numberType + "' and mo_number='" + moNumber + "' and routeendflage='Y'}");
                sql.append("=] t ");
                sql.append("\n left join ");
                sql.append("\n da_t_co_emp_desc e on t.emp_no=e.emp_no ");
            } else if (1 == numberType && "流程结束".equals(groupNext)) {
                sql.append("\n select t.serial_number,t.in_station_time, ");
                sql.append("\n (case t.group_name when 'n/a' then ' ' else ");
                sql.append("\n (case t.error_flag when '0' then 'ok' when '1' then 'ng' when '3' then ? when '4' then ? else 'ng' end ) end) error_flag_text, ");
                sql.append("\n t.error_flag, ");
                sql.append("\n t.mo_number,t.group_name,t.item_count,t.repair,e.emp_name,t.container_sn, ");
                sql.append("\n t.wait_scrap_qty,t.depanel_flag ");
                sql.append("\n from [={" + trackingTable + "}");
                sql.append("{serial_number,in_station_time,group_name,error_flag,mo_number,item_count,repair,emp_name,container_sn,");
                sql.append("wait_scrap_qty,depanel_flag,emp_no,number_type}{where number_type='" + numberType + "' and mo_number='" + moNumber + "' and routeendflage='Y'}");
                sql.append("=] t ");
                sql.append("\n left join ");
                sql.append("\n da_t_co_emp_desc e on t.emp_no=e.emp_no ");
//                sql.append("\n where t.number_type=? ");
//                sql.append("\n and t.mo_number=? and routeendflage='Y' ");
                sql.append("\n order by error_flag asc,in_station_time asc ");
                sql.append("\n order by error_flag asc,in_station_time asc ");

            } else {
                return null;
            }
            if (!"流程结束".equals(groupNext)) {
                params.add(scrap);
                params.add(lend);
            } else {
                return null;
            }
            if (!"流程结束".equals(groupNext)) {
                params.add(scrap);
                params.add(lend);
//                params.add(numberType);
//                params.add(moNumber);
//                params.add(groupNext);
                params.add(scrap);
                params.add(lend);
                params.add(scrap);
                params.add(lend);
//                params.add(numberType);
//                params.add(moNumber);
//                params.add(groupNext);
            } else {
                params.add(scrap);
                params.add(lend);
            } else {
                params.add(scrap);
                params.add(lend);
//                params.add(moNumber);
//                params.add(numberType);
            }
            System.out.println(sql);
            String parseSql1 = QuerySqlParseUtil.parseSplitTableSql(getBaseDao(), sql.toString(), 2);
            return getBaseDao().listTable(parseSql1, params.toArray());
        }
        return null;
    }
            }
            System.out.println(sql);
            String parseSql1 = QuerySqlParseUtil.parseSplitTableSql(getBaseDao(), sql.toString(), 2);
            return getBaseDao().listTable(parseSql1, params.toArray());
        }
        return null;
    }

    /**
     * å·¥åºæŠ¥è¡¨
     *
     * @param fse
     * @return
     * @throws BaseException
     */
    public DataTableEntity getProcessSheet(FieldSetEntity fse) throws BaseException {
        String bak = fse.getString("~bak~");
        String detailTable = "da_t_wip_detail";
        String trackTable = "da_t_wip_tracking";
        if ("1".equals(bak)) {
            detailTable = "da_t_wip_detail_bak";
            trackTable = "da_t_wip_tracking_bak";
        }
        //制令单号
        String moNumber = fse.getString("mo_number");
        //工序
        String process = fse.getString("process");
        StringBuilder sql = new StringBuilder(128);
        sql.append("\n select ");
        sql.append("\n * ");
        sql.append("\n from ");
        sql.append("\n ( ");
        sql.append("\n select ");
        sql.append("\n c.* ");
        sql.append("\n from ");
        sql.append("\n ( ");
        sql.append("\n select ");
        sql.append("\n a.*, ");
        sql.append("\n ifnull( c1.wait_scrap_qty_all, 0 ) wait_scrap_qty_all, ");
        sql.append("\n ifnull( l.pass_itemcount, 0 ) pass_itemcount, ");
        sql.append("\n ifnull( k.second_passcount, 0 ) second_passcount, ");
        sql.append("\n ifnull( m.use_itemcount, 0 ) use_itemcount, ");
        sql.append("\n ifnull( n.error_itemcount, 0 ) error_itemcount ");
        sql.append("\n from ");
        sql.append("\n ( ");
        sql.append("\n select ");
        sql.append("\n v.route_code, ");
        sql.append("\n v.group_next, ");
        sql.append("\n min( v.step_sequence ) as step_sequence, ");
        sql.append("\n min( state_flag ) as state_flag, ");
        sql.append("\n v.mo_number ");
        sql.append("\n from ");
        sql.append("\n ( ");
        sql.append("\n select ");
        sql.append("\n a.route_groupid, ");
        sql.append("\n a.route_code, ");
        sql.append("\n a.group_code, ");
        sql.append("\n a.group_name, ");
        sql.append("\n a.group_next, ");
        sql.append("\n a.state_flag, ");
        sql.append("\n a.step_sequence, ");
        sql.append("\n a.ismustpass, ");
        sql.append("\n a.isrotegroup, ");
        sql.append("\n a.ms_code, ");
        sql.append("\n a.route_inout_flag, ");
        sql.append("\n a.route_cycle_time, ");
        sql.append("\n b.technicsid, ");
        sql.append("\n c.mo_number ");
        sql.append("\n from ");
        sql.append("\n da_t_co_route_control a ");
        sql.append("\n left join da_t_smo_technics b on a.route_code = b.route_code ");
        sql.append("\n join [={da_t_pm_mo_base b1}{technicsid,mo_number}{ where mo_number = '" + moNumber + "'  }=] c on b.technicsid = c.technicsid ");
        sql.append("\n ) v ");
        sql.append("\n left join da_t_co_group g on v.group_next = g.group_name ");
        sql.append("\n where ");
    /**
     * å·¥åºæŠ¥è¡¨
     *
     * @param fse
     * @return
     * @throws BaseException
     */
    public DataTableEntity getProcessSheet(FieldSetEntity fse) throws BaseException {
        String detailTable = "da_t_wip_detail";
        String trackTable = "da_t_wip_tracking";
        //制令单号
        String moNumber = fse.getString("mo_number");
        //工序
        String process = fse.getString("process");
        StringBuilder sql = new StringBuilder(128);
        sql.append("\n select ");
        sql.append("\n * ");
        sql.append("\n from ");
        sql.append("\n ( ");
        sql.append("\n select ");
        sql.append("\n c.* ");
        sql.append("\n from ");
        sql.append("\n ( ");
        sql.append("\n select ");
        sql.append("\n a.*, ");
        sql.append("\n ifnull( c1.wait_scrap_qty_all, 0 ) wait_scrap_qty_all, ");
        sql.append("\n ifnull( l.pass_itemcount, 0 ) pass_itemcount, ");
        sql.append("\n ifnull( k.second_passcount, 0 ) second_passcount, ");
        sql.append("\n ifnull( m.use_itemcount, 0 ) use_itemcount, ");
        sql.append("\n ifnull( n.error_itemcount, 0 ) error_itemcount ");
        sql.append("\n from ");
        sql.append("\n ( ");
        sql.append("\n select ");
        sql.append("\n v.route_code, ");
        sql.append("\n v.group_next, ");
        sql.append("\n min( v.step_sequence ) as step_sequence, ");
        sql.append("\n min( state_flag ) as state_flag, ");
        sql.append("\n v.mo_number ");
        sql.append("\n from ");
        sql.append("\n ( ");
        sql.append("\n select ");
        sql.append("\n a.route_groupid, ");
        sql.append("\n a.route_code, ");
        sql.append("\n a.group_code, ");
        sql.append("\n a.group_name, ");
        sql.append("\n a.group_next, ");
        sql.append("\n a.state_flag, ");
        sql.append("\n a.step_sequence, ");
        sql.append("\n a.ismustpass, ");
        sql.append("\n a.isrotegroup, ");
        sql.append("\n a.ms_code, ");
        sql.append("\n a.route_inout_flag, ");
        sql.append("\n a.route_cycle_time, ");
        sql.append("\n b.technicsid, ");
        sql.append("\n c.mo_number ");
        sql.append("\n from ");
        sql.append("\n da_t_co_route_control a ");
        sql.append("\n left join da_t_smo_technics b on a.route_code = b.route_code ");
        sql.append("\n join [={da_t_pm_mo_base b1}{technicsid,mo_number}{ where mo_number = '" + moNumber + "'  }=] c on b.technicsid = c.technicsid ");
        sql.append("\n ) v ");
        sql.append("\n left join da_t_co_group g on v.group_next = g.group_name ");
        sql.append("\n where ");
//        sql.append("\n v.mo_number = '"+moNumber+"' ");
        sql.append("\n  v.state_flag <> '2' ");
        sql.append("\n and v.group_next <> '0' ");
        sql.append("\n and v.isrotegroup = 'Y' ");
        sql.append("\n group by ");
        sql.append("\n v.route_code, ");
        sql.append("\n v.group_next, ");
        sql.append("\n v.mo_number ");
        sql.append("\n order by ");
        sql.append("\n v.route_code, ");
        sql.append("\n v.group_next, ");
        sql.append("\n v.mo_number ");
        sql.append("\n ) a ");
        sql.append("\n left join ( select scrap_process, mo_number, sum( ng_count ) wait_scrap_qty_all from da_t_wip_error_scrap where mo_number = '" + moNumber + "' group by scrap_process, mo_number ) c1 on a.group_next = c1.scrap_process ");
        sql.append("\n and a.mo_number = c1.mo_number ");
        sql.append("\n left join ( ");
        sql.append("\n select ");
        sql.append("\n count( distinct a.serial_number ) pass_itemcount, ");
        sql.append("\n a.group_name, ");
        sql.append("\n a.mo_number ");
        sql.append("\n from ");
        sql.append("\n [={" + detailTable + " d1}{serial_number,group_name,mo_number,reflux_flag}{ where mo_number='" + moNumber + "' and reflux_flag='N' }=] a ");
        sql.append("\n  v.state_flag <> '2' ");
        sql.append("\n and v.group_next <> '0' ");
        sql.append("\n and v.isrotegroup = 'Y' ");
        sql.append("\n group by ");
        sql.append("\n v.route_code, ");
        sql.append("\n v.group_next, ");
        sql.append("\n v.mo_number ");
        sql.append("\n order by ");
        sql.append("\n v.route_code, ");
        sql.append("\n v.group_next, ");
        sql.append("\n v.mo_number ");
        sql.append("\n ) a ");
        sql.append("\n left join ( select scrap_process, mo_number, sum( ng_count ) wait_scrap_qty_all from da_t_wip_error_scrap where mo_number = '" + moNumber + "' group by scrap_process, mo_number ) c1 on a.group_next = c1.scrap_process ");
        sql.append("\n and a.mo_number = c1.mo_number ");
        sql.append("\n left join ( ");
        sql.append("\n select ");
        sql.append("\n count( distinct a.serial_number ) pass_itemcount, ");
        sql.append("\n a.group_name, ");
        sql.append("\n a.mo_number ");
        sql.append("\n from ");
        sql.append("\n [={" + detailTable + " d1}{serial_number,group_name,mo_number,reflux_flag}{ where mo_number='" + moNumber + "' and reflux_flag='N' }=] a ");
//        sql.append("\n where ");
//        sql.append("\n a.mo_number = '"+moNumber+"' ");
//        sql.append("\n and a.reflux_flag = 'N' ");
        sql.append("\n group by ");
        sql.append("\n group_name, ");
        sql.append("\n mo_number ");
        sql.append("\n ) l on a.mo_number = l.mo_number ");
        sql.append("\n and a.group_next = l.group_name ");
        sql.append("\n left join ( ");
        sql.append("\n select ");
        sql.append("\n sum( item_count ) second_passcount, ");
        sql.append("\n group_name, ");
        sql.append("\n mo_number ");
        sql.append("\n from ");
        sql.append("\n ( ");
        sql.append("\n select ");
        sql.append("\n a.group_name, ");
        sql.append("\n a.mo_number, ");
        sql.append("\n a.serial_number, ");
        sql.append("\n min( item_count ) item_count ");
        sql.append("\n from ");
        sql.append("\n [={" + detailTable + " d2}{group_name,mo_number,serial_number,item_count,reflux_flag}{where mo_number='" + moNumber + "' and reflux_flag='Y'}=] a ");
        sql.append("\n group by ");
        sql.append("\n group_name, ");
        sql.append("\n mo_number ");
        sql.append("\n ) l on a.mo_number = l.mo_number ");
        sql.append("\n and a.group_next = l.group_name ");
        sql.append("\n left join ( ");
        sql.append("\n select ");
        sql.append("\n sum( item_count ) second_passcount, ");
        sql.append("\n group_name, ");
        sql.append("\n mo_number ");
        sql.append("\n from ");
        sql.append("\n ( ");
        sql.append("\n select ");
        sql.append("\n a.group_name, ");
        sql.append("\n a.mo_number, ");
        sql.append("\n a.serial_number, ");
        sql.append("\n min( item_count ) item_count ");
        sql.append("\n from ");
        sql.append("\n [={" + detailTable + " d2}{group_name,mo_number,serial_number,item_count,reflux_flag}{where mo_number='" + moNumber + "' and reflux_flag='Y'}=] a ");
//        sql.append("\n where ");
//        sql.append("\n a.mo_number = '"+moNumber+"' ");
//        sql.append("\n and reflux_flag = 'Y' ");
        sql.append("\n group by ");
        sql.append("\n a.group_name, ");
        sql.append("\n a.mo_number, ");
        sql.append("\n a.serial_number ");
        sql.append("\n ) tttt ");
        sql.append("\n group by ");
        sql.append("\n group_name, ");
        sql.append("\n mo_number ");
        sql.append("\n ) k on a.mo_number = k.mo_number ");
        sql.append("\n and a.group_next = k.group_name ");
        sql.append("\n left join ( ");
        sql.append("\n select ");
        sql.append("\n display_group, ");
        sql.append("\n mo_number, ");
        sql.append("\n sum( item_count ) use_itemcount ");
        sql.append("\n from ");
        sql.append("\n [={"+trackTable+" t1}{display_group,mo_number,item_count,routeendflage,depanel_flag}{ where mo_number='" + moNumber + "'  and routeendflage <> 'Y'  and depanel_flag <> 'Y' }=] ");
        sql.append("\n group by ");
        sql.append("\n a.group_name, ");
        sql.append("\n a.mo_number, ");
        sql.append("\n a.serial_number ");
        sql.append("\n ) tttt ");
        sql.append("\n group by ");
        sql.append("\n group_name, ");
        sql.append("\n mo_number ");
        sql.append("\n ) k on a.mo_number = k.mo_number ");
        sql.append("\n and a.group_next = k.group_name ");
        sql.append("\n left join ( ");
        sql.append("\n select ");
        sql.append("\n display_group, ");
        sql.append("\n mo_number, ");
        sql.append("\n sum( item_count ) use_itemcount ");
        sql.append("\n from ");
        sql.append("\n [={" + trackTable + " t1}{display_group,mo_number,item_count,routeendflage,depanel_flag}{ where mo_number='" + moNumber + "'  and routeendflage <> 'Y'  and depanel_flag <> 'Y' }=] ");
//        sql.append("\n where ");
//        sql.append("\n mo_number = '"+moNumber+"' ");
//        sql.append("\n and routeendflage <> 'Y' ");
//        sql.append("\n and depanel_flag <> 'Y' ");
        sql.append("\n group by ");
        sql.append("\n display_group, ");
        sql.append("\n mo_number ");
        sql.append("\n ) m on a.group_next = m.display_group ");
        sql.append("\n and a.mo_number = m.mo_number ");
        sql.append("\n left join ( select group_name, mo_number, sum( repair ) error_itemcount from [={"+trackTable+" t2}{group_name,mo_number,repair,error_flag}{where mo_number = '" + moNumber + "' and error_flag = '1'}=]  group by group_name, mo_number ) n on a.group_next = n.group_name ");
        sql.append("\n and a.mo_number = n.mo_number ");
        sql.append("\n ) c union all ");
        sql.append("\n select ");
        sql.append("\n 0 route_code, ");
        sql.append("\n '流程结束' group_next, ");
        sql.append("\n 150 step_sequence, ");
        sql.append("\n 5 state_flag, ");
        sql.append("\n '" + moNumber + "' mo_number, ");
        sql.append("\n ifnull( ( select sum( wait_scrap_qty ) from [={"+trackTable+" t3}{wait_scrap_qty,mo_number}{ where mo_number = '" + moNumber + "' }=] as tt ), 0 ) wait_scrap_qty_all, ");
        sql.append("\n 0 pass_itemcount, ");
        sql.append("\n 0 second_passcount, ");
        sql.append("\n ifnull( ( select sum( item_count ) from [={"+trackTable+" t4}{item_count,mo_number,routeendflage}{where mo_number = '" + moNumber + "' and routeendflage = 'Y'}=] as ccc  ), 0 ) use_itemcount, ");
        sql.append("\n 0 error_itemcount ");
        sql.append("\n from ");
        sql.append("\n dual ");
        sql.append("\n ) a ");
        sql.append("\n group by ");
        sql.append("\n display_group, ");
        sql.append("\n mo_number ");
        sql.append("\n ) m on a.group_next = m.display_group ");
        sql.append("\n and a.mo_number = m.mo_number ");
        sql.append("\n left join ( select group_name, mo_number, sum( repair ) error_itemcount from [={" + trackTable + " t2}{group_name,mo_number,repair,error_flag}{where mo_number = '" + moNumber + "' and error_flag = '1'}=]  group by group_name, mo_number ) n on a.group_next = n.group_name ");
        sql.append("\n and a.mo_number = n.mo_number ");
        sql.append("\n ) c union all ");
        sql.append("\n select ");
        sql.append("\n 0 route_code, ");
        sql.append("\n '流程结束' group_next, ");
        sql.append("\n 150 step_sequence, ");
        sql.append("\n 5 state_flag, ");
        sql.append("\n '" + moNumber + "' mo_number, ");
        sql.append("\n ifnull( ( select sum( wait_scrap_qty ) from [={" + trackTable + " t3}{wait_scrap_qty,mo_number}{ where mo_number = '" + moNumber + "' }=] as tt ), 0 ) wait_scrap_qty_all, ");
        sql.append("\n 0 pass_itemcount, ");
        sql.append("\n 0 second_passcount, ");
        sql.append("\n ifnull( ( select sum( item_count ) from [={" + trackTable + " t4}{item_count,mo_number,routeendflage}{where mo_number = '" + moNumber + "' and routeendflage = 'Y'}=] as ccc  ), 0 ) use_itemcount, ");
        sql.append("\n 0 error_itemcount ");
        sql.append("\n from ");
        sql.append("\n dual ");
        sql.append("\n ) a ");

        if (!StringUtils.isEmpty(process)) {
            String[] processArray = process.split(",");
            sql.append("\n where ").append(BaseUtil.buildQuestionMarkFilter("group_next", processArray, true));
        }
        sql.append("\n ORDER BY ");
        sql.append("\n state_flag ASC, ");
        sql.append("\n step_sequence ASC ");
        if (!StringUtils.isEmpty(process)) {
            String[] processArray = process.split(",");
            sql.append("\n where ").append(BaseUtil.buildQuestionMarkFilter("group_next", processArray, true));
        }
        sql.append("\n ORDER BY ");
        sql.append("\n state_flag ASC, ");
        sql.append("\n step_sequence ASC ");

        String parseSql = QuerySqlParseUtil.parseSplitTableSql(getBaseDao(), sql.toString(), 2);
        String parseSql = QuerySqlParseUtil.parseSplitTableSql(getBaseDao(), sql.toString(), 2);

        return getBaseDao().listTable(parseSql, new Object[]{});
    }
        return getBaseDao().listTable(parseSql, new Object[]{});
    }

    private boolean appendLikeFilter(String value, String field, StringBuilder sqlFilter, List<Object> filterParams, boolean isFilter) {
        return appendLikeFilter(value, field, true, sqlFilter, filterParams, isFilter);
    }
    private boolean appendLikeFilter(String value, String field, StringBuilder sqlFilter, List<Object> filterParams, boolean isFilter) {
        return appendLikeFilter(value, field, true, sqlFilter, filterParams, isFilter);
    }

    private boolean appendLikeFilter(String value, String field, boolean like, StringBuilder sqlFilter, List<Object> filterParams, boolean isFilter) {
        if (!StringUtils.isEmpty(value)) {
            if (isFilter) {
                sqlFilter.append(" AND \n");
            }
            sqlFilter.append("\n ( ").append(field);
            if (like) {
                sqlFilter.append(" like concat(?,'%') ");
            } else {
                sqlFilter.append("= ?");
            }
            sqlFilter.append(" ) ");
            filterParams.add(value);
            isFilter = true;
        }
        return isFilter;
    }
    private boolean appendLikeFilter(String value, String field, boolean like, StringBuilder sqlFilter, List<Object> filterParams, boolean isFilter) {
        if (!StringUtils.isEmpty(value)) {
            if (isFilter) {
                sqlFilter.append(" AND \n");
            }
            sqlFilter.append("\n ( ").append(field);
            if (like) {
                sqlFilter.append(" like concat(?,'%') ");
            } else {
                sqlFilter.append("= ?");
            }
            sqlFilter.append(" ) ");
            filterParams.add(value);
            isFilter = true;
        }
        return isFilter;
    }

}

 product-server-data-center/src/main/java/com/product/data/center/service/SNAssociatedItemService.java

@@ -2,15 +2,14 @@

import java.io.IOException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.text.DateFormat;
import java.util.*;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import cn.hutool.core.date.DateTime;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

@@ -31,50 +30,50 @@
import cn.hutool.json.JSONObject;

/**
 *     SN关联物料
 * @author 86151
 * SN关联物料
 *
 * @author 86151
 */
@Component
public class SNAssociatedItemService extends AbstractBaseService{
	
public class SNAssociatedItemService extends AbstractBaseService {


    @Autowired
    BaseDao baseDao;
	

    /**
     *     è¿‡ç«™ä¿¡æ¯
     * è¿‡ç«™ä¿¡æ¯
     *
     * @param fse
     * @return
     */
    public JSONObject listSNItemInfo(FieldSetEntity fse) {
		
        JSONObject jsonReturn=new JSONObject(); 
		
        String isBakTable = fse.getString(CmnConst.IS_BAK_TABLE);    //是否切表
        Integer isRecentData = fse.getInteger(CmnConst.IS_RECENT_DATA);//是否近期数据
        Integer pageSize = fse.getInteger(CmnConst.PAGESIZE);    //页数大小
        Integer cpage = fse.getInteger(CmnConst.CPAGE);            //目标页
	

        JSONObject jsonReturn = new JSONObject();

        String isBakTable = fse.getString(CmnConst.IS_BAK_TABLE);    //是否切表
        boolean isRecentData = fse.getBoolean(CmnConst.IS_RECENT_DATA) == true;//是否近期数据
        Integer pageSize = fse.getInteger(CmnConst.PAGESIZE);    //页数大小
        Integer cpage = fse.getInteger(CmnConst.CPAGE);            //目标页

        //创建tracking表SQL，并获取过滤的数据
        String parseTrackingSql=createTrackingSql(fse, isBakTable);
        DataTableEntity dtTracking= baseDao.listTable(parseTrackingSql, new Object[] {}, pageSize, cpage);
        String parseTrackingSql = createTrackingSql(fse, isBakTable, isRecentData);
        DataTableEntity dtTracking = baseDao.listTable(parseTrackingSql, new Object[]{}, pageSize, cpage);
        if (!BaseUtil.dataTableIsEmpty(dtTracking)) {
			

            //获取总条数
//            String totalTrackingSql="SELECT COUNT(*)count_total FROM ("+parseTrackingSql+")t";
//            FieldSetEntity fseTotalTracking=baseDao.getFieldSetBySQL(totalTrackingSql, new Object[] {}, false);
//            jsonReturn.set("total", fseTotalTracking.getInteger("count_total"));
            int totalTrackingSql=dtTracking.getSqle().getTotalCount();
            int totalTrackingSql = dtTracking.getSqle().getTotalCount();
            jsonReturn.set("total", totalTrackingSql);
			

            //通过tracking过滤keyp
            //Method1:使用OR查询
            Object [] serialNumbers = dtTracking.getUuids();
            String keypSql = createKeypSql(serialNumbers, isBakTable);
            System.out.println(keypSql);
            DataTableEntity dtKeyp=baseDao.listTable(keypSql, new Object[] {});
			
            Object[] serialNumbers = dtTracking.getUuids();
            String keypSql = createKeypSql(serialNumbers, isBakTable, isRecentData);
            DataTableEntity dtKeyp = baseDao.listTable(keypSql, new Object[]{});

            if (!BaseUtil.dataTableIsEmpty(dtKeyp)) {
                //将tracking表的数据转为(key，value)形式存储
                JSONObject json = convertDataTableToJson(dtTracking);
@@ -82,97 +81,91 @@
                jsonReturn.set("keyp_info", BaseUtil.dataTableEntityToJson(dtKeyp));
                return jsonReturn;
            }
			

            //Method2:使用左关联查询（考虑到SQL的长度标准，建议不使用）
			
        }else {

        } else {
            jsonReturn.set("total", 0);
        }
        return null;
    }

	

    /**
     *     æ ¹æ®è¿‡æ»¤æ¡ä»¶è§£æžtacking的SQL
     * æ ¹æ®tacking表的SN解析keyp表
     *
     * @param serialNumbers
     * @param isBakTable    æ˜¯å¦BAK表
     * @return
     */
    public String createKeypSql(Object[] serialNumbers, String isBakTable, boolean isRecentData) {
        String keypFilter = BaseUtil.buildQuestionMarkFilter(CmnConst.PK_PRODUCT_SN, serialNumbers, true);

        StringBuilder keypSql = new StringBuilder(2056);
        keypSql.append(" SELECT ")
                .append(" t.pk_product_sn AS pk_product_sn,b1.zj,c.dj,b.ysj, ")
                .append(" d1.ysjmn,d.dk,e1.xsq,x1.ykq,c1.xfzj, ")
                .append(" REGEXP_SUBSTR(d.dk, '[^|]+', 1, 5) zbrj, ")
                .append(" e.mk,f.fjd,h.ccpg  ")
                .append(" FROM(SELECT DISTINCT pk_product_sn FROM [={");
        keypSql.append("da_t_wip_product_keyp");
        keypSql.append("}{pk_product_sn,pk_keyp_sn,pk_item_source_type}{")
                .append(" WHERE instr( pk_mo,'DK' )=0 AND ")
                .append(keypFilter)
                .append("}=]) t ")
                .append(" LEFT JOIN(SELECT u.pk_product_sn, u.pk_keyp_sn ysj FROM ").append("da_t_wip_product_keyp").append(" u WHERE u.pk_item_source_type= 'YSJ' AND u.pk_keyp_sn NOT LIKE 'R_%') b ON t.pk_product_sn = b.pk_product_sn ")
                .append(" LEFT JOIN(SELECT v.pk_product_sn, v.pk_keyp_sn dj FROM ").append("da_t_wip_product_keyp").append(" v WHERE v.pk_item_source_type= 'DJ' AND v.pk_keyp_sn NOT LIKE 'R_%') c ON t.pk_product_sn = c.pk_product_sn ")
                .append(" LEFT JOIN(SELECT w.pk_product_sn, w.pk_keyp_sn dk FROM ").append("da_t_wip_product_keyp").append(" w WHERE w.pk_item_source_type= 'DK'  AND w.pk_keyp_sn NOT LIKE 'R_%') d ON t.pk_product_sn = d.pk_product_sn ")
                .append(" LEFT JOIN(SELECT x.pk_product_sn, x.pk_keyp_sn mk FROM ").append("da_t_wip_product_keyp").append(" x WHERE x.pk_item_source_type= 'MK' AND x.pk_keyp_sn NOT LIKE 'R_%') e ON t.pk_product_sn = e.pk_product_sn ")
                .append(" LEFT JOIN(SELECT y.pk_product_sn, y.pk_keyp_sn fjd FROM ").append("da_t_wip_product_keyp").append(" y WHERE y.pk_item_source_type= 'FJD' AND y.pk_keyp_sn NOT LIKE 'R_%') f ON t.pk_product_sn = f.pk_product_sn ")
                .append(" LEFT JOIN(SELECT z.pk_product_sn, z.pk_keyp_sn zj FROM ").append("da_t_wip_product_keyp").append(" z WHERE  (z.pk_item_source_type= 'ZJNJMN' or z.pk_item_source_type= 'ZJWJMN') AND z.pk_keyp_sn NOT LIKE 'R_%') b1 ON  t.pk_product_sn = b1.pk_product_sn ")
                .append(" LEFT JOIN(SELECT v1.pk_product_sn, v1.pk_keyp_sn ysjmn FROM ").append("da_t_wip_product_keyp").append(" v1 WHERE v1.pk_item_source_type= 'YSJMN' AND v1.pk_keyp_sn NOT LIKE 'R_%') d1 ON t.pk_product_sn = d1.pk_product_sn ")
                .append(" LEFT JOIN(SELECT w1.pk_product_sn, w1.pk_keyp_sn xsq FROM ").append("da_t_wip_product_keyp").append(" w1 WHERE w1.pk_item_source_type= 'XSQ' AND w1.pk_keyp_sn NOT LIKE 'R_%') e1 ON t.pk_product_sn = e1.pk_product_sn ")
                .append(" LEFT JOIN(SELECT x1.pk_product_sn, x1.pk_keyp_sn ykq FROM ").append("da_t_wip_product_keyp").append(" x1 WHERE x1.pk_item_source_type= 'YKQ' AND x1.pk_keyp_sn NOT LIKE 'R_%') x1 ON t.pk_product_sn = x1.pk_product_sn ")
                .append(" LEFT JOIN(SELECT x1.pk_product_sn, x1.pk_keyp_sn xfzj FROM ").append("da_t_wip_product_keyp").append(" x1 WHERE x1.pk_item_source_type= 'XFZJ' AND x1.pk_keyp_sn NOT LIKE 'R_%') c1 ON t.pk_product_sn = c1.pk_product_sn ")
                .append(" LEFT JOIN (SELECT x1.pk_product_sn, x1.pk_keyp_sn ccpg FROM ").append("da_t_wip_product_keyp").append(" x1 WHERE x1.pk_item_source_type = 'CCPG' AND x1.pk_keyp_sn NOT LIKE 'R_%') h ON t.pk_product_sn = h.pk_product_sn ");

        String parseKeypSql = QuerySqlParseUtil.parseSplitTableSql(getBaseDao(), keypSql.toString(), isRecentData ? 2 : 0, false);
        return parseKeypSql;
    }

    /**
     * æ ¹æ®è¿‡æ»¤æ¡ä»¶è§£æžtacking的SQL
     *
     * @param fse
     * @param isBakTable æ˜¯å¦BAK表
     * @return
     */
    public String createTrackingSql(FieldSetEntity fse, String isBakTable) {
		
        StringBuilder trackingSql=new StringBuilder(256);
    public String createTrackingSql(FieldSetEntity fse, String isBakTable, boolean isRecentData) {

        StringBuilder trackingSql = new StringBuilder(256);
        trackingSql.append("SELECT a.mo_number AS moNumber,a.in_line_time AS inLineTime,a.area_name AS areaName,a.serial_number AS uuid,a.technicsid ")
        .append(" FROM [={");
        if ("1".equals(isBakTable)) {
            trackingSql.append("da_t_wip_tracking_bak");
        }else {
            trackingSql.append("da_t_wip_tracking");
        }
        trackingSql.append("}{mo_number,in_line_time,area_name,serial_number,technicsid}{")
        .append(parseCondition(fse,0))
        .append("}=] a")
        .append(" JOIN da_t_smo_technics h ON a.technicsid =h.technicsid ")
        .append(parseCondition(fse,1));
		
                .append(" FROM [={da_t_wip_tracking}");
        trackingSql.append("{mo_number,in_line_time,area_name,serial_number,technicsid}{")
                .append(parseCondition(fse, 0))
                .append("}=] a")
                .append(" JOIN da_t_smo_technics h ON a.technicsid =h.technicsid ")
                .append(parseCondition(fse, 1));

        //解析SQL
        String parseTrackingSql= QuerySqlParseUtil.parseSplitTableSql(getBaseDao(), trackingSql.toString(), 2, false);
        String parseTrackingSql = QuerySqlParseUtil.parseSplitTableSql(getBaseDao(), trackingSql.toString(), isRecentData ? 2 : 0, false);
        return parseTrackingSql;
    }
	

    /**
     *     æ ¹æ®tacking表的SN解析keyp表
     * @param serialNumbers
     * @param isBakTable æ˜¯å¦BAK表
     * @return
     */
    public String createKeypSql(Object[] serialNumbers, String isBakTable) {
        String keypFilter = BaseUtil.buildQuestionMarkFilter(CmnConst.PK_PRODUCT_SN, serialNumbers, true);
		
        StringBuilder keypSql = new StringBuilder(2056);
        keypSql.append(" SELECT ")
        .append(" t.pk_product_sn AS pkProductSn,b1.zj,c.dj,b.ysj, ")
        .append(" d1.ysjmn,d.dk,e1.xsq,x1.ykq,c1.xfzj, ")
        .append(" REGEXP_SUBSTR(d.dk, '[^|]+', 1, 5) zbrj, ")
        .append(" e.mk,f.fjd,h.ccpg  ")
        .append(" FROM(SELECT DISTINCT pk_product_sn FROM [={");
        if ("1".equals(isBakTable)) {
            keypSql.append("da_t_wip_product_keyp_bak");
        }else {
            keypSql.append("da_t_wip_product_keyp");
        }
        keypSql.append("}{pk_product_sn,pk_keyp_sn,pk_item_source_type}{")
        .append(" WHERE instr( pk_mo,'DK' )=0 AND ")
        .append(keypFilter)
        .append("}=]) t ")
        .append(" LEFT JOIN(SELECT u.pk_product_sn, u.pk_keyp_sn ysj FROM ").append("1".equals(isBakTable)?"da_t_wip_product_keyp_bak":"da_t_wip_product_keyp").append(" u WHERE u.pk_item_source_type= 'YSJ' AND u.pk_keyp_sn NOT LIKE 'R_%') b ON t.pk_product_sn = b.pk_product_sn ")
        .append(" LEFT JOIN(SELECT v.pk_product_sn, v.pk_keyp_sn dj FROM ").append("1".equals(isBakTable)?"da_t_wip_product_keyp_bak":"da_t_wip_product_keyp").append(" v WHERE v.pk_item_source_type= 'DJ' AND v.pk_keyp_sn NOT LIKE 'R_%') c ON t.pk_product_sn = c.pk_product_sn ")
        .append(" LEFT JOIN(SELECT w.pk_product_sn, w.pk_keyp_sn dk FROM ").append("1".equals(isBakTable)?"da_t_wip_product_keyp_bak":"da_t_wip_product_keyp").append(" w WHERE w.pk_item_source_type= 'DK'  AND w.pk_keyp_sn NOT LIKE 'R_%') d ON t.pk_product_sn = d.pk_product_sn ")
        .append(" LEFT JOIN(SELECT x.pk_product_sn, x.pk_keyp_sn mk FROM ").append("1".equals(isBakTable)?"da_t_wip_product_keyp_bak":"da_t_wip_product_keyp").append(" x WHERE x.pk_item_source_type= 'MK' AND x.pk_keyp_sn NOT LIKE 'R_%') e ON t.pk_product_sn = e.pk_product_sn ")
        .append(" LEFT JOIN(SELECT y.pk_product_sn, y.pk_keyp_sn fjd FROM ").append("1".equals(isBakTable)?"da_t_wip_product_keyp_bak":"da_t_wip_product_keyp").append(" y WHERE y.pk_item_source_type= 'FJD' AND y.pk_keyp_sn NOT LIKE 'R_%') f ON t.pk_product_sn = f.pk_product_sn ")
        .append(" LEFT JOIN(SELECT z.pk_product_sn, z.pk_keyp_sn zj FROM ").append("1".equals(isBakTable)?"da_t_wip_product_keyp_bak":"da_t_wip_product_keyp").append(" z WHERE  (z.pk_item_source_type= 'ZJNJMN' or z.pk_item_source_type= 'ZJWJMN') AND z.pk_keyp_sn NOT LIKE 'R_%') b1 ON  t.pk_product_sn = b1.pk_product_sn ")
        .append(" LEFT JOIN(SELECT v1.pk_product_sn, v1.pk_keyp_sn ysjmn FROM ").append("1".equals(isBakTable)?"da_t_wip_product_keyp_bak":"da_t_wip_product_keyp").append(" v1 WHERE v1.pk_item_source_type= 'YSJMN' AND v1.pk_keyp_sn NOT LIKE 'R_%') d1 ON t.pk_product_sn = d1.pk_product_sn ")
        .append(" LEFT JOIN(SELECT w1.pk_product_sn, w1.pk_keyp_sn xsq FROM ").append("1".equals(isBakTable)?"da_t_wip_product_keyp_bak":"da_t_wip_product_keyp").append(" w1 WHERE w1.pk_item_source_type= 'XSQ' AND w1.pk_keyp_sn NOT LIKE 'R_%') e1 ON t.pk_product_sn = e1.pk_product_sn ")
        .append(" LEFT JOIN(SELECT x1.pk_product_sn, x1.pk_keyp_sn ykq FROM ").append("1".equals(isBakTable)?"da_t_wip_product_keyp_bak":"da_t_wip_product_keyp").append(" x1 WHERE x1.pk_item_source_type= 'YKQ' AND x1.pk_keyp_sn NOT LIKE 'R_%') x1 ON t.pk_product_sn = x1.pk_product_sn ")
        .append(" LEFT JOIN(SELECT x1.pk_product_sn, x1.pk_keyp_sn xfzj FROM ").append("1".equals(isBakTable)?"da_t_wip_product_keyp_bak":"da_t_wip_product_keyp").append(" x1 WHERE x1.pk_item_source_type= 'XFZJ' AND x1.pk_keyp_sn NOT LIKE 'R_%') c1 ON t.pk_product_sn = c1.pk_product_sn ")
        .append(" LEFT JOIN (SELECT x1.pk_product_sn, x1.pk_keyp_sn ccpg FROM ").append("1".equals(isBakTable)?"da_t_wip_product_keyp_bak":"da_t_wip_product_keyp").append(" x1 WHERE x1.pk_item_source_type = 'CCPG' AND x1.pk_keyp_sn NOT LIKE 'R_%') h ON t.pk_product_sn = h.pk_product_sn ");
		
        String parseKeypSql= QuerySqlParseUtil.parseSplitTableSql(getBaseDao(), keypSql.toString(), 2, false);
        return parseKeypSql;
    }
	
    /**
     *     tracking信息转化
     * tracking信息转化
     *
     * @param dtTracking
     * @return
     */
    public JSONObject convertDataTableToJson(DataTableEntity dtTracking) {
        JSONObject json=new JSONObject();
        for(int i=0; i<dtTracking.getRows();i++) {
            String serialNumber=dtTracking.getFieldSetEntity(i).getString(CmnConst.UUID);
            String areaName=dtTracking.getFieldSetEntity(i).getString(CmnConst.AREANAME);
            String moNumber=dtTracking.getFieldSetEntity(i).getString(CmnConst.MONUMBER);
            String inLineTime=dtTracking.getFieldSetEntity(i).getString(CmnConst.INLINETIME);
            List<String> trackInfo=new ArrayList<>();
        JSONObject json = new JSONObject();
        for (int i = 0; i < dtTracking.getRows(); i++) {
            String serialNumber = dtTracking.getFieldSetEntity(i).getString(CmnConst.UUID);
            String areaName = dtTracking.getFieldSetEntity(i).getString(CmnConst.AREANAME);
            String moNumber = dtTracking.getFieldSetEntity(i).getString(CmnConst.MONUMBER);
            String inLineTime = dtTracking.getFieldSetEntity(i).getString(CmnConst.INLINETIME);
            List<String> trackInfo = new ArrayList<>();
            trackInfo.add(areaName);
            trackInfo.add(moNumber);
            trackInfo.add(inLineTime);
@@ -180,108 +173,122 @@
        }
        return json;
    }
	

    /**
     *     å°†tracking信息添加至keyp
     * å°†tracking信息添加至keyp
     *
     * @param json
     * @param dtKeyp
     */
    public void addKeypInfo(JSONObject json, DataTableEntity dtKeyp) {
        for (int i = 0; i < dtKeyp.getRows(); i++) {
            FieldSetEntity fseKeyp=dtKeyp.getFieldSetEntity(i);
            String pkProductSN=fseKeyp.getString(CmnConst.PKPRODUCTSN);
            FieldSetEntity fseKeyp = dtKeyp.getFieldSetEntity(i);
            String pkProductSN = fseKeyp.getString("pk_product_sn");
            fseKeyp.setValue(CmnConst.PKPRODUCTSN, pkProductSN);
            @SuppressWarnings("unchecked")
            List<String> trackInfo = (List<String>) json.get(pkProductSN);
            if (trackInfo.size()==3) {
                fseKeyp.setValue(CmnConst.AREANAME, trackInfo.get(0));
                fseKeyp.setValue(CmnConst.MONUMBER, trackInfo.get(1));
                fseKeyp.setValue(CmnConst.INLINETIME, trackInfo.get(2));
            if (trackInfo.size() == 3) {
                fseKeyp.setValue("area_name", trackInfo.get(0));
                fseKeyp.setValue("mo_number", trackInfo.get(1));
                fseKeyp.setValue("in_line_time", trackInfo.get(2));
            }
        }
    }
	

    /**
     *     è§£æžæŠ¥è¡¨è¿‡æ»¤æ¡ä»¶
     * è§£æžæŠ¥è¡¨è¿‡æ»¤æ¡ä»¶
     *
     * @param fse
     * @return
     */
     public String parseCondition(FieldSetEntity fse ,int type) {
 		
         StringBuilder sqlFilter=new StringBuilder(128);
 		
         if (type==1) {
             String poka_yoke = fse.getString(CmnConst.POKA_YOKE);    //防呆规则（小米，非小米）
             if (!BaseUtil.strIsNull(poka_yoke)) {
                 if ("0".equals(poka_yoke)) {
                     sqlFilter.append(" WHERE h.poka_yoke=1 ");
                 }else {
                     sqlFilter.append(" WHERE h.poka_yoke!=1 ");
                 }
             }
        }else {
            sqlFilter.append(" WHERE scrap_flag IS NULL ");
            String moNumber = fse.getString(CmnConst.MO_NUMBER);    //工单号
            String factory = fse.getString(CmnConst.FACTORY);        //厂别
            String areasn = fse.getString(CmnConst.AREA_SN);        //线体
            String startTime = fse.getString(CmnConst.START_TIME);    //开始时间
            String endTime = fse.getString(CmnConst.END_TIME);        //结束时间
			
            if(!BaseUtil.strIsNull(moNumber)) {
                sqlFilter.append(" AND mo_number = '")
                .append(moNumber)
                .append("'");
    public String parseCondition(FieldSetEntity fse, int type) {

        StringBuilder sqlFilter = new StringBuilder(128);

        if (type == 1) {
            String poka_yoke = fse.getString(CmnConst.POKA_YOKE);    //防呆规则（小米，非小米）
            if (!BaseUtil.strIsNull(poka_yoke)) {
                if ("0".equals(poka_yoke)) {
                    sqlFilter.append(" WHERE h.poka_yoke=1 ");
                } else {
                    sqlFilter.append(" WHERE h.poka_yoke!=1 ");
                }
            }
			
        } else {
            sqlFilter.append(" WHERE scrap_flag IS NULL ");
            String moNumber = fse.getString(CmnConst.MO_NUMBER);    //工单号
            String factory = fse.getString(CmnConst.FACTORY);        //厂别
            String areasn = fse.getString(CmnConst.AREA_SN);        //线体
            Date startTime = fse.getDate(CmnConst.START_TIME);    //开始时间
            Date endTime = fse.getDate(CmnConst.END_TIME);        //结束时间

            if (!BaseUtil.strIsNull(moNumber)) {
                sqlFilter.append(" AND mo_number = '")
                        .append(moNumber)
                        .append("'");
            }

//            if (!BaseUtil.strIsNull(factory)) {
//                sqlFilter.append("");
//            }
			
            if(!BaseUtil.strIsNull(areasn)) {
                String [] areasns=areasn.split(",");
                if (areasns.length>1) {

            if (!BaseUtil.strIsNull(areasn)) {
                String[] areasns = areasn.split(",");
                if (areasns.length > 1) {
                    sqlFilter.append(" AND ");
                    sqlFilter.append(BaseUtil.buildQuestionMarkFilter(CmnConst.AREA_SN, areasns, true));
                }else {
                } else {
                    sqlFilter.append(" AND area_sn = '")
                    .append(areasn)
                    .append("'");
                            .append(areasn)
                            .append("'");
                }
            }
			
            if(!BaseUtil.strIsNull(startTime)) {
                sqlFilter.append(" AND in_station_time >STR_TO_DATE('")
                .append(startTime)
                .append("','%Y-%m-%d %H:%i:%s')");

            if (startTime != null) {
                sqlFilter.append(" AND in_station_time >=STR_TO_DATE('")
                        .append(DateUtil.format(startTime, "yyyy-MM-dd HH:mm:ss"))
                        .append("','%Y-%m-%d %H:%i:%s')");
            }
			
            if (!BaseUtil.strIsNull(endTime)) {
                sqlFilter.append(" AND in_station_time <STR_TO_DATE('")
                .append(endTime)
                .append("','%Y-%m-%d %H:%i:%s')");

            if (endTime != null) {
                //判断结束时间是否为当天的开始时间 00:00:00
                DateTime dateTime = new DateTime(endTime);
                Calendar calendar = dateTime.toCalendar();
                if (calendar.get(Calendar.HOUR_OF_DAY) == 0 && calendar.get(Calendar.MINUTE) == 0 && calendar.get(Calendar.SECOND) == 0) {
                    //设置为23:59:59
                    calendar.set(Calendar.HOUR_OF_DAY, 23);
                    calendar.set(Calendar.MINUTE, 59);
                    calendar.set(Calendar.SECOND, 59);
                    endTime = calendar.getTime();
                }
                sqlFilter.append(" AND in_station_time <=STR_TO_DATE('")
                        .append(DateUtil.format(endTime, "yyyy-MM-dd HH:mm:ss"))
                        .append("','%Y-%m-%d %H:%i:%s')");
            }
        }
		
        return sqlFilter.toString(); 

        return sqlFilter.toString();
    }
 	
     /**
      *     ç”ŸæˆEXCEL信息
      * @param request
      * @param response
      * @throws IOException
      */
     public void getReportExcel(HttpServletRequest request, HttpServletResponse response) throws IOException {

    /**
     * ç”ŸæˆEXCEL信息
     *
     * @param request
     * @param response
     * @throws IOException
     */
    public void getReportExcel(HttpServletRequest request, HttpServletResponse response) throws IOException {
        //获取机号查询信息
        FieldSetEntity fse = BaseUtil.getFieldSetEntity(request);
        DataTableEntity workCenterOperationInfomationSheet = this.dtSNItemInfo(fse);
		

        //初始EXCEL信息
        response.setContentType("multipart/form-data");
        response.setCharacterEncoding("utf-8");
        //这里URLEncoder.encode可以防止中文乱码
        String fileName = URLEncoder.encode("SN关联物料" + DateUtil.format(new Date(), "yyyyMMddHHmmss") + ".xlsx", "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName);
		
        response.setCharacterEncoding("utf-8");
        //这里URLEncoder.encode可以防止中文乱码
        String fileName = URLEncoder.encode("SN关联物料" + DateUtil.format(new Date(), "yyyyMMddHHmmss") + ".xlsx", "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName);

        List<SNAssociatedItemEntity> traceSNItemEntityList = new ArrayList<>();
        for (int i = 0; i < workCenterOperationInfomationSheet.getRows(); i++) {
            FieldSetEntity fieldSetEntity = workCenterOperationInfomationSheet.getFieldSetEntity(i);
@@ -294,49 +301,50 @@
        ExcelWriter excelWriter = EasyExcel.write(outputStream, SNAssociatedItemEntity.class).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build();
        try {
            WriteSheet writeSheet = EasyExcel.writerSheet(1, "data").build();
            excelWriter.write(traceSNItemEntityList, writeSheet);
            excelWriter.write(traceSNItemEntityList, writeSheet);
        } catch (Exception e) {
            throw e;
        } finally {
            excelWriter.finish();
        }
    }
 	
     /**
     *     è¿‡ç«™ä¿¡æ¯

    /**
     * è¿‡ç«™ä¿¡æ¯
     *
     * @param fse
     * @return
     */
    public DataTableEntity dtSNItemInfo(FieldSetEntity fse) {
		

        String isBakTable = fse.getString(CmnConst.IS_BAK_TABLE);
        Integer isRecentData = fse.getInteger(CmnConst.IS_RECENT_DATA);//是否近期数据
        Integer pageSize = fse.getInteger(CmnConst.PAGESIZE);    //页数大小
        Integer cpage = fse.getInteger(CmnConst.CPAGE);            //目标页
	
        boolean isRecentData = fse.getBoolean(CmnConst.IS_RECENT_DATA) == true;//是否近期数据
        Integer pageSize = fse.getInteger(CmnConst.PAGESIZE);    //页数大小
        Integer cpage = fse.getInteger(CmnConst.CPAGE);            //目标页

        //创建tracking表SQL，并获取过滤的数据
        String parseTrackingSql=createTrackingSql(fse, isBakTable);
        DataTableEntity dtTracking= baseDao.listTable(parseTrackingSql, new Object[] {}, pageSize, cpage);
        String parseTrackingSql = createTrackingSql(fse, isBakTable, isRecentData);
        DataTableEntity dtTracking = baseDao.listTable(parseTrackingSql, new Object[]{}, pageSize, cpage);
        if (!BaseUtil.dataTableIsEmpty(dtTracking)) {
			

            //获取总条数
            String totalTrackingSql="SELECT COUNT(*)count_total FROM ("+parseTrackingSql+")t";
            FieldSetEntity fseTotalTracking=baseDao.getFieldSetBySQL(totalTrackingSql, new Object[] {}, false);
			
            String totalTrackingSql = "SELECT COUNT(*)count_total FROM (" + parseTrackingSql + ")t";
//            FieldSetEntity fseTotalTracking = baseDao.getFieldSetBySQL(totalTrackingSql, new Object[]{}, false);

            //通过tracking过滤keyp
            //Method1:使用OR查询
            Object [] serialNumbers = dtTracking.getUuids();
            String keypSql = createKeypSql(serialNumbers, isBakTable);
            Object[] serialNumbers = dtTracking.getUuids();
            String keypSql = createKeypSql(serialNumbers, isBakTable, isRecentData);
            System.out.println(keypSql);
            DataTableEntity dtKeyp=baseDao.listTable(keypSql, new Object[] {});
			
            DataTableEntity dtKeyp = baseDao.listTable(keypSql, new Object[]{});

            if (!BaseUtil.dataTableIsEmpty(dtKeyp)) {
                //将tracking表的数据转为(key，value)形式存储
                JSONObject json = convertDataTableToJson(dtTracking);
                addKeypInfo(json, dtKeyp);
                return dtKeyp;
            }
        }			
        }
        return null;
    }
}

 product-server-data-center/src/main/java/com/product/data/center/service/TraceToSourceService.java

@@ -35,49 +35,46 @@


/**
 *     SN追溯查询
 * @author du
 * SN追溯查询
 *
 * @author du
 */
@Service
public class TraceToSourceService extends AbstractBaseService{
public class TraceToSourceService extends AbstractBaseService {

    @Autowired
    BaseDao baseDao;

    /**
     *    ç‰©æ–™SN反追溯历史查询
     * @param querySNSN    ä¸»ä»¶SN
     * ç‰©æ–™SN反追溯历史查询
     *
     * @param querySNSN  ä¸»ä»¶SN
     * @param isBakTable æ˜¯å¦BAK表
     * @return
     */
    public JSONObject antitraceReport(String querySN, String isBakTable) {
        JSONObject jsonInfo=new JSONObject();
        JSONObject jsonInfo = new JSONObject();

        //查询关键件，获取机号SN
        String currentSql = "";
        if ("1".equals(isBakTable)) {
            currentSql = "SELECT DISTINCT pk_product_sn FROM [={da_t_wip_product_keyp_bak}{}{WHERE pk_keyp_sn = '"+querySN+"' }=]";
        }else {
            currentSql = "SELECT DISTINCT pk_product_sn FROM [={da_t_wip_product_keyp}{}{WHERE pk_keyp_sn = '"+querySN+"' }=]";
        }
				
        String currentSql = "SELECT DISTINCT pk_product_sn FROM [={da_t_wip_product_keyp}{}{WHERE pk_keyp_sn = '" + querySN + "' }=]";

        String parseSql = QuerySqlParseUtil.parseSplitTableSql(getBaseDao(), currentSql);
        FieldSetEntity fse=baseDao.getFieldSetBySQL(parseSql, new Object[] {}, false);
        if (fse!=null) {
            jsonInfo=traceReport(fse.getString(CmnConst.PK_PRODUCT_SN), isBakTable);
        FieldSetEntity fse = baseDao.getFieldSetBySQL(parseSql, new Object[]{}, false);
        if (fse != null) {
            jsonInfo = traceReport(fse.getString(CmnConst.PK_PRODUCT_SN), isBakTable);
        }
        return jsonInfo;
    }

    /**
     *    SN追溯历史查询
     * @param æœºå™¨SN    æœºå™¨åºåˆ—号
     * SN追溯历史查询
     *
     * @param æœºå™¨SN       æœºå™¨åºåˆ—号
     * @param isBakTable æ˜¯å¦BAK表
     * @return
     */
    public JSONObject traceReport(String querySN, String isBakTable) {
        JSONObject jsonInfo=new JSONObject();
        JSONObject jsonInfo = new JSONObject();

        jsonInfo.set("orderInfo", BaseUtil.fieldSetEntityToJson(getManufacturingOrderInformation(querySN, isBakTable)));
        jsonInfo.set("materialInfo", BaseUtil.dataTableEntityToJson(getWorkCenterOperationInfomation(querySN, isBakTable)));
@@ -89,7 +86,8 @@
    }

    /**
     *     èŽ·å–制令单信息
     * èŽ·å–制令单信息
     *
     * @param querySN
     * @param isBakTable æ˜¯å¦BAK表
     * @return
@@ -98,33 +96,29 @@
        //订单信息
        StringBuilder sb1 = new StringBuilder(1024);
        sb1.append("SELECT ")
        .append(" t.mo_number,t.model_name,t.area_name,t.container_sn, ")
        .append(" t.next_station,t.station_name,t.display_group, ")
        .append(" t.old_begin_time,t.old_end_time,w.pi_sn, ")
        .append(" DATE_FORMAT(p.mo_start_date, '%Y-%m-%d %H:%i:%s') mo_start_date, ")
        .append(" DATE_FORMAT(t.in_station_time, '%Y-%m-%d %H:%i:%s') in_station_time, ")
        .append(" (CASE t.group_name WHEN 'N/A' THEN t.next_station ELSE t.group_name END) group_name, ")
        .append(" (CASE t.error_flag WHEN '0' THEN '正常' WHEN '1' THEN '不良' WHEN '3' THEN '报废' ELSE 'N/A' END) error_flag ")
        .append("")
        .append(" FROM [=da_t_pm_mo_base=] p,[={");
        if ("1".equals(isBakTable)) {
            sb1.append("da_t_wip_tracking_bak");
        }else {
            sb1.append("da_t_wip_tracking");
        }
		
                .append(" t.mo_number,t.model_name,t.area_name,t.container_sn, ")
                .append(" t.next_station,t.station_name,t.display_group, ")
                .append(" t.old_begin_time,t.old_end_time,w.pi_sn, ")
                .append(" DATE_FORMAT(p.mo_start_date, '%Y-%m-%d %H:%i:%s') mo_start_date, ")
                .append(" DATE_FORMAT(t.in_station_time, '%Y-%m-%d %H:%i:%s') in_station_time, ")
                .append(" (CASE t.group_name WHEN 'N/A' THEN t.next_station ELSE t.group_name END) group_name, ")
                .append(" (CASE t.error_flag WHEN '0' THEN '正常' WHEN '1' THEN '不良' WHEN '3' THEN '报废' ELSE 'N/A' END) error_flag ")
                .append("")
                .append(" FROM [=da_t_pm_mo_base=] p,[={");
        sb1.append("da_t_wip_tracking");

        sb1.append("}{}{WHERE serial_number = '")
        .append(querySN)
        .append("'}=] t ")
        .append(" LEFT JOIN [={da_t_wip_packaging_infor}{}{WHERE pi_product_sn='") 
        .append(querySN)
        .append("'}=] w ON t.serial_number = w.pi_product_sn ")
        .append(" WHERE p.mo_number = t.mo_number AND t.serial_number = ?");
                .append(querySN)
                .append("'}=] t ")
                .append(" LEFT JOIN [={da_t_wip_packaging_infor}{}{WHERE pi_product_sn='")
                .append(querySN)
                .append("'}=] w ON t.serial_number = w.pi_product_sn ")
                .append(" WHERE p.mo_number = t.mo_number AND t.serial_number = ?");

        //报表解析SQL，生成WITH
        String finalSql = QuerySqlParseUtil.parseSplitTableSql(getBaseDao(), sb1.toString());
        FieldSetEntity orderInfo=baseDao.getFieldSetEntityBySQL(finalSql, new Object[] {querySN} , false);
        if (orderInfo!=null) {
        FieldSetEntity orderInfo = baseDao.getFieldSetEntityBySQL(finalSql, new Object[]{querySN}, false);
        if (orderInfo != null) {
            orderInfo.setValue("outPack", this.getOutermostContainer(querySN));
        }
        return orderInfo;
@@ -132,7 +126,8 @@


    /**
     *     èŽ·å–最外层容器SN
     * èŽ·å–最外层容器SN
     *
     * @param querySN
     * @return
     */
@@ -140,12 +135,12 @@
        //单独查询包装信息
        StringBuilder sb = new StringBuilder();
        sb.append("SELECT pi_sn,pi_pack_level,pi_child_sn")
        .append(" FROM [={da_t_wip_packaging_infor}{pi_sn,ROUND(pi_pack_level)pi_pack_level,pi_child_sn}{WHERE pi_product_sn='")
        .append(querySN)
        .append("'}=] ");
                .append(" FROM [={da_t_wip_packaging_infor}{pi_sn,ROUND(pi_pack_level)pi_pack_level,pi_child_sn}{WHERE pi_product_sn='")
                .append(querySN)
                .append("'}=] ");
//        .append(" WHERE pi_product_sn=? ");
        String finalSql = QuerySqlParseUtil.parseSplitTableSql(getBaseDao(), sb.toString());
        DataTableEntity dtPackingInfo=baseDao.listTable(finalSql, new Object[] {});
        DataTableEntity dtPackingInfo = baseDao.listTable(finalSql, new Object[]{});
        if (!BaseUtil.dataTableIsEmpty(dtPackingInfo)) {

            //目标SN集合（索引等于深度，索引对应值等于同深度的任意一个SN，即索引最大的SN为目标SN）
@@ -153,18 +148,18 @@
            for (int i = 0; i < dtPackingInfo.getRows(); i++) {
                FieldSetEntity fseBaseData = dtPackingInfo.getFieldSetEntity(i);
                //cheng 2022å¹´12月29日14:16:52
                int level= NumberUtil.parseInt(fseBaseData.getString("pi_pack_level"));
                String ID=fseBaseData.getString("pi_sn");
                while (listLevel.size()<level) {
                int level = NumberUtil.parseInt(fseBaseData.getString("pi_pack_level"));
                String ID = fseBaseData.getString("pi_sn");
                while (listLevel.size() < level) {
                    listLevel.add(new HashMap<>());
                }
                listLevel.get(level-1).put(ID, fseBaseData);
                listLevel.get(level - 1).put(ID, fseBaseData);
            }

            //双循环遍历去掉非上下级的包装数据
            for (int i = 1; i < listLevel.size(); i++) {
                Map<String, FieldSetEntity> mapPreCommonLevel=listLevel.get(i-1);
                Map<String, FieldSetEntity> mapCommonLevel=listLevel.get(i);
                Map<String, FieldSetEntity> mapPreCommonLevel = listLevel.get(i - 1);
                Map<String, FieldSetEntity> mapCommonLevel = listLevel.get(i);
                for (Map.Entry<String, FieldSetEntity> entry : mapCommonLevel.entrySet()) {
                    String key = entry.getKey();
                    FieldSetEntity val = entry.getValue();
@@ -173,14 +168,14 @@
                    }
                }
                if (mapCommonLevel.isEmpty()) {
                    listLevel.remove(listLevel.subList(i, listLevel.size()-1));
                    listLevel.remove(listLevel.subList(i, listLevel.size() - 1));
                }
            }

            //返回最外层包装条码
            for (Map.Entry<String, FieldSetEntity> entry : listLevel.get(listLevel.size()-1).entrySet()) {
                String key=entry.getKey();
                if (key!=null) {
            for (Map.Entry<String, FieldSetEntity> entry : listLevel.get(listLevel.size() - 1).entrySet()) {
                String key = entry.getKey();
                if (key != null) {
                    return key;
                }
            }
@@ -190,7 +185,8 @@


    /**
     *     èŽ·å–工作中心物料作业信息
     * èŽ·å–工作中心物料作业信息
     *
     * @param querySN
     * @param isBakTable æ˜¯å¦BAK表
     * @return
@@ -199,33 +195,30 @@
        //物料作业信息
        StringBuilder sb2 = new StringBuilder(1024);
        sb2.append("SELECT ")
        .append(" DISTINCT pk_station_name AS stationName, ")
        .append(" l.pk_product_lot AS lot_number,l.pk_item_code AS keyPartNo, ")
        .append(" c.co_item_name AS assistantToolName,c.co_item_spec AS itemSpec, ")
        .append(" pk_keyp_sn AS itemSN,pk_keyp_sn_qty AS itemCount, ")
        .append(" DATE_FORMAT(pk_loadtime,'%Y-%m-%d %H:%i:%s') AS loadTime, ")
        .append(" (SELECT d.emp_name FROM da_t_co_emp_desc d WHERE d.emp_no=l.pk_empno)empName, ")
        .append(" (CASE 0 WHEN 0 THEN '上料' WHEN '1' THEN '下料' WHEN '2' THEN '换料' WHEN '3' THEN '异常换料' ELSE '' END) actionType ")
        .append(" FROM [={");
        if ("1".equals(isBakTable)) {
            sb2.append("da_t_wip_product_keyp_bak");
        }else {
            sb2.append("da_t_wip_product_keyp");
        }
                .append(" DISTINCT pk_station_name AS stationName, ")
                .append(" l.pk_product_lot AS lot_number,l.pk_item_code AS keyPartNo, ")
                .append(" c.co_item_name AS assistantToolName,c.co_item_spec AS itemSpec, ")
                .append(" pk_keyp_sn AS itemSN,pk_keyp_sn_qty AS itemCount, ")
                .append(" DATE_FORMAT(pk_loadtime,'%Y-%m-%d %H:%i:%s') AS loadTime, ")
                .append(" (SELECT d.emp_name FROM da_t_co_emp_desc d WHERE d.emp_no=l.pk_empno)empName, ")
                .append(" (CASE 0 WHEN 0 THEN '上料' WHEN '1' THEN '下料' WHEN '2' THEN '换料' WHEN '3' THEN '异常换料' ELSE '' END) actionType ")
                .append(" FROM [={");
        sb2.append("da_t_wip_product_keyp");
        sb2.append("}{}{ WHERE pk_replace_flag='N' AND pk_product_sn='")
        .append(querySN)
        .append("'}=] l ")
        .append(" LEFT JOIN [=da_t_co_item=] c ON c.co_item_code=l.pk_item_code ")
        .append(" WHERE l.pk_replace_flag='N' AND l.pk_product_sn=? ")
        .append(" ORDER BY loadtime ");
                .append(querySN)
                .append("'}=] l ")
                .append(" LEFT JOIN [=da_t_co_item=] c ON c.co_item_code=l.pk_item_code ")
                .append(" WHERE l.pk_replace_flag='N' AND l.pk_product_sn=? ")
                .append(" ORDER BY loadtime ");
        //报表解析SQL，生成WITH
        String finalSql2 = QuerySqlParseUtil.parseSplitTableSql(getBaseDao(), sb2.toString());
        DataTableEntity materialInfo=baseDao.listTable(finalSql2, new Object[] {querySN});
        DataTableEntity materialInfo = baseDao.listTable(finalSql2, new Object[]{querySN});
        return materialInfo;
    }

    /**
     *     èŽ·å–历史过站信息
     * èŽ·å–历史过站信息
     *
     * @param querySN
     * @param isBakTable æ˜¯å¦BAK表
     * @return
@@ -234,41 +227,38 @@
        //过站信息
        StringBuilder sb3 = new StringBuilder();
        sb3.append(" SELECT ")
        .append(" t.mo_number,t.lot_number,t.model_name,t.area_name, ")
        .append(" t.group_name,t.next_station,t.station_name, ")
        .append(" t.customer_no,t.emp_no,c.emp_name,t.item_count,t.serial_number, ")
        .append(" DATE_FORMAT(t.in_station_time, '%Y-%m-%d %H:%i:%s') AS in_station_time, ")
        .append(" (CASE t.error_flag WHEN '0' THEN '正常' WHEN '1' THEN '不良' WHEN '3' THEN '报废' ELSE 'N/A' END) error_flag ")
        .append(" FROM [={");
        if ("1".equals(isBakTable)) {
            sb3.append("da_t_wip_detail_bak");
        }else {
            sb3.append("da_t_wip_detail");
        }
                .append(" t.mo_number,t.lot_number,t.model_name,t.area_name, ")
                .append(" t.group_name,t.next_station,t.station_name, ")
                .append(" t.customer_no,t.emp_no,c.emp_name,t.item_count,t.serial_number, ")
                .append(" DATE_FORMAT(t.in_station_time, '%Y-%m-%d %H:%i:%s') AS in_station_time, ")
                .append(" (CASE t.error_flag WHEN '0' THEN '正常' WHEN '1' THEN '不良' WHEN '3' THEN '报废' ELSE 'N/A' END) error_flag ")
                .append(" FROM [={");
        sb3.append("da_t_wip_detail");
        sb3.append("}{}{")
        .append(" WHERE serial_number = ( ")
        .append(" SELECT '")
        .append(querySN)
        .append("' old_sn ")
        .append(" UNION ")
        .append(" SELECT DISTINCT old_sn  ")
        .append(" FROM da_t_co_sn_relation ")
        .append(" WHERE new_sn = '")
        .append(querySN)
        .append("' ")
        .append(" ) ")
        .append("}=] t ")
        .append(" LEFT JOIN da_t_co_emp_desc c ON t.emp_no = c.emp_no ")
        .append(" ORDER BY t.in_station_time ");
                .append(" WHERE serial_number = ( ")
                .append(" SELECT '")
                .append(querySN)
                .append("' old_sn ")
                .append(" UNION ")
                .append(" SELECT DISTINCT old_sn  ")
                .append(" FROM da_t_co_sn_relation ")
                .append(" WHERE new_sn = '")
                .append(querySN)
                .append("' ")
                .append(" ) ")
                .append("}=] t ")
                .append(" LEFT JOIN da_t_co_emp_desc c ON t.emp_no = c.emp_no ")
                .append(" ORDER BY t.in_station_time ");

        //报表解析SQL，生成WITH
        String finalSql3 = QuerySqlParseUtil.parseSplitTableSql(getBaseDao(), sb3.toString());
        DataTableEntity crossInfo=baseDao.listTable(finalSql3, new Object[] {});
        DataTableEntity crossInfo = baseDao.listTable(finalSql3, new Object[]{});
        return crossInfo;
    }

    /**
     *     èŽ·å–历史维修记录
     * èŽ·å–历史维修记录
     *
     * @param querySN
     * @return
     */
@@ -276,47 +266,48 @@
        //维修记录
        StringBuilder sb4 = new StringBuilder();
        sb4.append(" SELECT ")
        .append(" c.error_desc,d.reason_desc,m.repair_moth, ")
        .append(" e.test_code,e.ng_count,e.test_point, ")
        .append(" r.reason_code,r.duty_type,r.repair_station, ")
        .append(" r.repair_group,r.error_item,r.item_desc, ")
        .append(" r.date_code,r.repair_desc,r.repairer, ")
        .append(" r.repair_time,s.emp_name,t.duty_desc ")
        .append(" FROM [=da_t_wip_repair=] r ")
        .append(" LEFT JOIN [={da_t_wip_error}{test_code,ng_count,test_point,ng_record_id,serial_number}{WHERE serial_number='")
        .append(querySN)
        .append("'}=] e ON e.ng_record_id = r.ng_record_id ")
        .append(" LEFT JOIN da_t_co_error_code c ON e.test_code = c.error_code ")
        .append(" LEFT JOIN da_t_co_repair_moth m ON r.repair_month_code = m.repair_code ")
        .append(" LEFT JOIN da_t_co_reason_code d ON r.reason_code = d.reason_code ")
        .append(" LEFT JOIN da_t_co_duty t ON r.duty_type = t.duty_type ")
        .append(" LEFT JOIN da_t_co_emp_desc s ON r.repairer = s.emp_no ")
        .append(" WHERE e.serial_number= ?");
                .append(" c.error_desc,d.reason_desc,m.repair_moth, ")
                .append(" e.test_code,e.ng_count,e.test_point, ")
                .append(" r.reason_code,r.duty_type,r.repair_station, ")
                .append(" r.repair_group,r.error_item,r.item_desc, ")
                .append(" r.date_code,r.repair_desc,r.repairer, ")
                .append(" r.repair_time,s.emp_name,t.duty_desc ")
                .append(" FROM [=da_t_wip_repair=] r ")
                .append(" LEFT JOIN [={da_t_wip_error}{test_code,ng_count,test_point,ng_record_id,serial_number}{WHERE serial_number='")
                .append(querySN)
                .append("'}=] e ON e.ng_record_id = r.ng_record_id ")
                .append(" LEFT JOIN da_t_co_error_code c ON e.test_code = c.error_code ")
                .append(" LEFT JOIN da_t_co_repair_moth m ON r.repair_month_code = m.repair_code ")
                .append(" LEFT JOIN da_t_co_reason_code d ON r.reason_code = d.reason_code ")
                .append(" LEFT JOIN da_t_co_duty t ON r.duty_type = t.duty_type ")
                .append(" LEFT JOIN da_t_co_emp_desc s ON r.repairer = s.emp_no ")
                .append(" WHERE e.serial_number= ?");

        //报表解析SQL，生成WITH
        String finalSql4 = QuerySqlParseUtil.parseSplitTableSql(getBaseDao(), sb4.toString());
        DataTableEntity repairInfo=baseDao.listTable(finalSql4, new Object[] {querySN});
        DataTableEntity repairInfo = baseDao.listTable(finalSql4, new Object[]{querySN});
        return repairInfo;
    }

    /**
     *     ç”ŸæˆEXCEL数据
     * ç”ŸæˆEXCEL数据
     *
     * @param request
     * @param response
     * @throws IOException 
     * @throws IOException
     */
    public void getReportExcel(HttpServletRequest request, HttpServletResponse response) throws IOException {
        //获取机号查询信息
        FieldSetEntity fse = BaseUtil.getFieldSetEntity(request);
        DataTableEntity workCenterOperationInfomationSheet = this.getWorkCenterOperationInfomation(fse.getString(CmnConst.SERIAL_NUMBER), fse.getString(CmnConst.IS_BAK_TABLE));
		

        //初始EXCEL信息
        response.setContentType("multipart/form-data");
        response.setCharacterEncoding("utf-8");
        //这里URLEncoder.encode可以防止中文乱码
        String fileName = URLEncoder.encode("SN历史追溯" + DateUtil.format(new Date(), "yyyyMMddHHmmss") + ".xlsx", "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName);
		
        response.setCharacterEncoding("utf-8");
        //这里URLEncoder.encode可以防止中文乱码
        String fileName = URLEncoder.encode("SN历史追溯" + DateUtil.format(new Date(), "yyyyMMddHHmmss") + ".xlsx", "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName);

        List<TraceSNItemEntity> traceSNItemEntityList = new ArrayList<>();
        for (int i = 0; i < workCenterOperationInfomationSheet.getRows(); i++) {
            FieldSetEntity fieldSetEntity = workCenterOperationInfomationSheet.getFieldSetEntity(i);
@@ -329,7 +320,7 @@
        ExcelWriter excelWriter = EasyExcel.write(outputStream, TraceSNItemEntity.class).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build();
        try {
            WriteSheet writeSheet = EasyExcel.writerSheet(1, "物料作业信息").build();
            excelWriter.write(traceSNItemEntityList, writeSheet);
            excelWriter.write(traceSNItemEntityList, writeSheet);
        } catch (Exception e) {
            throw e;
        } finally {

 product-server-web/lib/product-server-core-1.0.0-releases.jar

Binary files differ

 product-server-web/resources/LicenseKey.dat

@@ -1 +1 @@
01BC8AA83EB75F3C5839DC34763D25F9D4D7FBE3E961E786868CEC17D5F70B3AAF8239EC0E6754FA3A085623FAC377E3ACB89C05E0F2369E682EB5881EF53A954E6B6E917A8CA6708C379DA4090E4E655E2C546FC3FC644A4BADB3359AFE99E8A889304A8155AEB75382886DC57D7A63796F92E21337F8E2842F2CF17A5BB62C1A856ED7AEE240FEA701250B93D8403C7F7EA3581674312703FFD3953564AB0F9B00A5F9F08DC5EC628BEB28348C02A8C5A7B5788F2CD34C4757ADD7F5223C46384B9F6A7C541AD86558D4937138AD973655178E6CFA438F09BD89489B7FA4D6DD2920ECB70ABD68148F451457536976F5414DE01F0D0E7CEA698A627163D3095B54815CC86C8B9DCE2EB3602BB1586ECBD959D2DBFFF4163734D296FC84B2D0ED10777F225FB2F854B674A6F34E28F36E2F3A4035C20690AD5AF663138301456E3CEA67E17FECEA06309BC40BD6734A563736D2E3EA9DBC9DCA7B4AE23BD8C77E98F44943CC3C12ECA786DAF17BACE8567D4C901608BFAF520C052FF71DDEE8DD268EC0DAF9DCE22435FB828644CB17380A3FF81E6FA2B68B49559D93D8BA869E2D14E374B9CFB9E80C3097A9B60CBD55E7B495163D5DAE0A55888814F148ED1EAF1B9DF0B639568374FEA4298860562870523730EA0518BD5DF6E9796FFB32D4019A616A60E217C74AD441A6270DBB649BB56EE06F56CCCDB40A1EC774A921
7373F609D2DCF63105362A4556F3DBD03DDE005CE3839FE2A1D4AB8895082B9F7F2B031AEB44487E0CF23020A8DD4ACC7CC7BEBB55AADBB2411DE029662CF6B08EA69D97D0284FC0ABC6C02D9EA17A2EC33300DD5B6135DCE70B7EB3B37B7600C13EEC15A033E5D9437680A4ED29851BA3C7679EF8E3B3C3847F5DB74B653D2B796A087D1FA30D733682F393E31E05E06A241230AE9FCFE6E34219F57C272CA9C9F0E52A66744A2D8117F8718385612183BD0164E2B448069D9814704BA67F8565F27D0CA38A290AD162721E7FAD62DE3210BED393072644FE40B200B69A97D89184CE5F636AA949E7AFF39BAD0FB851E2BFAF32ADEA42CC37FAABAD1D972C6086C7F28F21A916E6F00CA3544C99FAC2B4DECD74B9D8A4E80CF7510C7A46B7CDD078DF9142713F03B30CE81D8861F5CD951E3A2B3A024E08ECE6918910FF2029B96CE9BC55CECD331DBD75EEE04BD985F27AF348FA79FA1EBF33942F29C52B10395E294F65FA2149B10458EE1C246CA6A0359442D610188B98C0E927FD6A6DF7

 product-server-web/resources/license001.dat

Binary files differ

 product-server-web/src/main/java/com/product/Application.java

@@ -37,7 +37,7 @@
            SpringApplication.run(Application.class, args);
            boolean cacheFlat = DataPoolCacheImpl.getInstance().cacheALLData();
            boolean codeFlat = DataPoolCacheImpl.getInstance().initCodeManager();
            boolean serialFlat = DataPoolCacheImpl.getInstance().initSerialNumberManager();
//            boolean serialFlat = DataPoolCacheImpl.getInstance().initSerialNumberManager();
            RegistValidate.getInstance().registValidateParameter();
            boolean connFlat = DataSourceManager.getInstance().initClientConnectionPool();
            InitialLoad.runInitialization();
@@ -46,7 +46,7 @@
            Global.setPropertyValue("jdk.home", System.getProperties().getProperty("java.home"));
            SpringMVCContextHolder.getSystemLogger().info("root.path:" + rootPath);
            SpringMVCContextHolder.getSystemLogger().info("jdk.home:" + System.getProperties().getProperty("java.home"));
            if (cacheFlat && codeFlat && serialFlat && connFlat) {
            if (cacheFlat && codeFlat && connFlat) {
                SpringMVCContextHolder.getSystemLogger().info("Successful System Startup ! ");
            } else {
                System.err.println("System startup failed, Please contact the administrator!");

 product-server-web/src/main/resources/application-dev.properties

@@ -15,7 +15,7 @@
#\u662F\u5426\u542F\u7528\u63A5\u53E3\u7B7E\u540D\u8BA4\u8BC1
signature.isEnable=true
#\u5728\u542F\u7528\u63A5\u53E3\u7B7E\u540D\u8BA4\u8BC1\u65F6\uFF0C\u6392\u9664\u6307\u5B9A\u63A5\u53E3\u65E0\u9700\u7B7E\u540D\u8BA4\u8BC1
signature.exclude.path=/api/rsa/getKey/v1,/api/token/refresh/v1,/api/mobile/network/check/v1,/api/system/config/info/v1,/api/fileManager/get-static-file,/api/mes/sync-data/v1,/static/**,/api/open-mes/update-mo-base/v1,/api/open-mes/get-history-data/v1,/api/open-mes/get-collect-log/v1
signature.exclude.path=/api/rsa/getKey/v1,/api/token/refresh/v1,/api/mobile/network/check/v1,/api/system/config/info/v1,/api/fileManager/get-static-file,/api/mes/sync-data/v1,/static/**,/api/open-mes/update-mo-base/v1,/api/open-mes/get-history-data/v1,/api/open-mes/get-collect-log/v1,/api/open-mes/split-table-data/v1
#\u7B7E\u540D\u8BA4\u8BC1Key
signature.key=299cb5bb4c9040a29c58304c25001d72
#\u63A5\u53E3\u7B7E\u540D\u8BA4\u8BC1\u6709\u6548\u671F\uFF0C\u5355\u4F4D\u79D2,0\u8868\u793A\u4E0D\u9A8C\u8BC1
@@ -83,9 +83,9 @@
# \u6D4B\u8BD5\u670D\u52A1\u5668
#data.source.url=jdbc:mysql://10.4.69.68:3306/ch_kt_mes_apply?autoReconnect=true&useUnicode=true&characterEncoding=utf8&useNewIO=true&useSSL=false&serverTimezone=Asia/Shanghai
# \u6B63\u5F0F\u670D\u52A1\u5668-\u91C7\u96C6
data.source.url=jdbc:mysql://10.13.1.39:3306/ch_kt_mes_apply?autoReconnect=true&useUnicode=true&characterEncoding=utf8&useNewIO=true&useSSL=false&serverTimezone=Asia/Shanghai
#data.source.url=jdbc:mysql://10.13.1.39:3306/ch_kt_mes_apply?autoReconnect=true&useUnicode=true&characterEncoding=utf8&useNewIO=true&useSSL=false&serverTimezone=Asia/Shanghai
# \u6B63\u5F0F\u670D\u52A1\u5668-\u62A5\u8868
#data.source.url=jdbc:mysql://10.13.1.39:3307/ch_kt_mes_apply?autoReconnect=true&useUnicode=true&characterEncoding=utf8&useNewIO=true&useSSL=false&serverTimezone=Asia/Shanghai
data.source.url=jdbc:mysql://10.13.1.39:3306/ch_kt_mes_apply?autoReconnect=true&useUnicode=true&characterEncoding=utf8&useNewIO=true&useSSL=false&serverTimezone=Asia/Shanghai
data.source.user=root
#data.source.password=root123
#data.source.password=a123

 product-server-web/src/main/resources/application-prod.properties

@@ -1,6 +1,8 @@
#======================================#
#=============\u7CFB\u7EDF\u53C2\u6570 \u76F8\u5173\u914D\u7F6E==============#
#======================================#
#数据系统名称用于采集 åç§°å€¼åªèƒ½åŒ…含英文和 "-"
data.system.name=ch-kt
#\u4E0A\u4F20\u6587\u4EF6\u8DEF\u5F84,\u7EDD\u5BF9
#\u542F\u7528\u65E5\u5FD7\u6253\u5370
logger.isEnable=true
@@ -74,9 +76,9 @@
data.source.type=mysql
data.source.driver=com.mysql.jdbc.Driver
##################local###############################
data.source.url=jdbc:mysql://127.0.0.1:3306/lx_product_db?autoReconnect=true&useUnicode=true&characterEncoding=utf8&useNewIO=true&useSSL=false&serverTimezone=Asia/Shanghai
data.source.url=jdbc:mysql://10.13.1.39:3307/ch_kt_mes_apply?autoReconnect=true&useUnicode=true&characterEncoding=utf8&useNewIO=true&useSSL=false&serverTimezone=Asia/Shanghai
data.source.user=root
data.source.password=root123
data.source.password=CHKTADMIN
#-------------------------druid\u8FDE\u63A5\u6C60\u7684\u914D\u7F6E\u4FE1\u606F------------------------
# \u521D\u59CB\u5316\u5927\u5C0F\uFF0C\u6700\u5C0F\uFF0C\u6700\u5927
druid.datasource.initialSize=5
@@ -88,7 +90,7 @@
druid.datasource.maxWait=60000
# \u914D\u7F6E\u95F4\u9694\u591A\u4E45\u624D\u8FDB\u884C\u4E00\u6B21\u68C0\u6D4B\uFF0C\u68C0\u6D4B\u9700\u8981\u5173\u95ED\u7684\u7A7A\u95F2\u8FDE\u63A5\uFF0C\u5355\u4F4D\u662F\u6BEB\u79D2  10\u5206\u949F\u5C0F\u65F6\u68C0\u67E5
druid.datasource.timeBetweenEvictionRunsMillis=600000
# \u914D\u7F6E\u4E00\u4E2A\u8FDE\u63A5\u5728\u6C60\u4E2D\u6700\u5C0F\u751F\u5B58\u7684\u65F6\u95F4\uFF0C\u5355\u4F4D\u662F\u6BEB\u79D2 \u8FDE\u63A5\u4FDD\u6301\u7A7A\u95F2\u800C\u4E0D\u88AB\u9A71\u9010\u7684\u6700\u5C0F\u65F6\u95F4 
# \u914D\u7F6E\u4E00\u4E2A\u8FDE\u63A5\u5728\u6C60\u4E2D\u6700\u5C0F\u751F\u5B58\u7684\u65F6\u95F4\uFF0C\u5355\u4F4D\u662F\u6BEB\u79D2 \u8FDE\u63A5\u4FDD\u6301\u7A7A\u95F2\u800C\u4E0D\u88AB\u9A71\u9010\u7684\u6700\u5C0F\u65F6\u95F4
druid.datasource.minEvictableIdleTimeMillis=1800000
#\u7528\u6765\u68C0\u6D4B\u8FDE\u63A5\u662F\u5426\u6709\u6548\u7684sql\uFF0C\u8981\u6C42\u662F\u4E00\u4E2A\u67E5\u8BE2\u8BED\u53E5
druid.datasource.validationQuery=SELECT 1 
@@ -118,7 +120,7 @@
#========== Server settings ===========#
#======================================#
server.servlet.context-path=/lx
server.port=9998
server.port=9999
#HTTP encoding
spring.servlet.multipart.enabled=false
spring.http.encoding.force=true
@@ -215,7 +217,7 @@
source_domain=https://www.baidu.com
default_domain=https://lx.blob.core.windows.net
# \u7B49\u4E8Etrue \u5B9A\u65F6\u4EFB\u52A1\u624D\u80FD\u542F\u52A8
org.quartz.task.isEnable=true
org.quartz.task.isEnable=false
#==============================================================
#Configure Main Scheduler Properties
#==============================================================
@@ -235,7 +237,7 @@
#\u662F\u5426\u52A0\u5165\u96C6\u7FA4
##spring.quartz.properties.org.quartz.jobStore.isClustered = true
#\u8C03\u5EA6\u5B9E\u4F8B\u5931\u6548\u7684\u68C0\u67E5\u65F6\u95F4\u95F4\u9694
##spring.quartz.properties.org.quartz.jobStore.clusterCheckinInterval = 1000  
##spring.quartz.properties.org.quartz.jobStore.clusterCheckinInterval = 1000
##spring.quartz.properties.org.quartz.jobStore.maxMisfiresToHandleAtATime = 20
# \u4FE1\u606F\u4FDD\u5B58\u65F6\u95F4 \u9ED8\u8BA4\u503C60\u79D2
##spring.quartz.properties.org.quartz.jobStore.misfireThreshold = 60000
@@ -279,4 +281,4 @@
#\u624B\u673A\u7AEF   \u524D\u7AEF\u670D\u52A1\u5730\u5740
web.server.url=
#\u662F\u5426\u542F\u7528\u5F3A\u5236\u9A8C\u8BC1\u767B\u5F55APP\u7684mac
mobile.mac.valid.isEnable=false
mobile.mac.valid.isEnable=false

 resources/LicenseKey.dat

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1 @@
765F1622DAEEB425045CAFA1545A6874332D8A7EE7F3587DA9645C9B9265C9547E5B5060EE4F70F60D064222A282FC7777D0F53750264B4942D65D176E58795C83401C1BD80B932DA12493F898FD6F6CC37F50CA5F543603ED6B4229BEBCA889C64FA4BDA7F7226546A53583EE651D26A270CD31F2235B898FB4B1E7469EF82C78AFF86F18B075713B64C13EEC4536EB6DDC0EF9AB8280CAE3752F317099E9A7C1AF508F6AA26D0E807A023385E916B084777CD5EAC8FCA89AFD5F4749B6EC8C6977311EA003E212D6AE396B7A66D3AA3C3EA57D9AA6A74EFD564337B752D837969554716558C03FEFADCDF6A6B759A6E9D86332A78F0A7E30A899E51E8141B683A39ED426712403FCC1D1C24B86ED5BBCD7426DB0AB6CAD0D5A32817ADC01F1D713D92B4FCE4094B66994498B650698960EC7B93EDDD860EB136C7516105499B43CED2F5C45529B1EBD0A3CE54EEC97FC6651E8F907F626B250FC2626ADDF88394847CA62F84626BA753435178F973EABFD2F85D84D998099C97332F46E3551